<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="HuGoods">
<!-- hu_goods -->
<sql id="Goods_Base_Column_List" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Fri Dec 12 11:53:06 CST 2014.
-->
HUGS_ID, HUGS_NAME, HUGS_BRAND, HUGS_VERSION, HUGS_PRODUCT_DATE, HUGS_ASSET_CODE,
HUGS_PRICE, HUGS_DEPT_ID, HUGS_DEPT_NAME, HUGS_USER_ID, HUGS_USER_NAME, HUGS_USE_RESON,
HUGS_USE_STATUS, HUGS_DESCR, HUGS_STATUS, HUGS_CREATOR, HUGS_CREATE_TIME, HUGS_UPDATE,
HUGS_UPDATE_TIME,HUGS_COMPANY
</sql>
<!-- where条件 -->
<sql id="Goods_Where_Clause" >
<dynamic prepend="WHERE HUGS_STATUS = 1">
<isNotEmpty property="HUGS_ASSET_CODE">
AND HUGS_ASSET_CODE LIKE '%$HUGS_ASSET_CODE$%'
</isNotEmpty>
<isNotEmpty property="HUGS_NAME">
AND HUGS_NAME LIKE '%$HUGS_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_DEPT_NAME">
AND HUGS_DEPT_NAME LIKE '%$HUGS_DEPT_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_USER_NAME">
AND HUGS_USER_NAME LIKE '%$HUGS_USER_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_USE_RESON">
AND HUGS_USE_RESON LIKE '%$HUGS_USE_RESON$%'
</isNotEmpty>
<isNotEmpty property="HUGS_COMPANY">
AND HUGS_COMPANY = #HUGS_COMPANY#
</isNotEmpty>
</dynamic>
</sql>
<!-- 物资总数 -->
<select id="getGoodsListCnt" resultClass="java.lang.Integer"
parameterClass="java.util.HashMap">
SELECT COUNT(*) FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
</isParameterPresent>
</select>
<!-- 物资分页集合查询 -->
<select id="getGoodsPageList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT * FROM
(
SELECT
A .*, ROWNUM r
FROM
(
SELECT <include refid="HuGoods.Goods_Base_Column_List" />
FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
) A
WHERE
ROWNUM <![CDATA[ <= ]]> #end#
) B
WHERE r <![CDATA[>]]> #start#
<!-- mysql version -->
<!--
SELECT <include refid="HuGoods.Goods_Base_Column_List" />
FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
LIMIT #start#,#end#
-->
</select>
<!-- 物资EXLS导出数据 -->
<select id="getGoodsEXLSList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT
(
SELECT
WM_CONCAT (
EM.HUGI_DESCR || ' ' ||'变更时间：'||TO_CHAR(EM.HUGI_CHANGE_DATE,'YYYY-MM-DD')
)
FROM
HU_GOODS_ITEM EM
WHERE
EM.HUGI_GOODS_ID = GOODS.HUGS_ID
) AS ITEMS,
GOODS.HUGS_NAME,
GOODS.HUGS_BRAND,
GOODS.HUGS_VERSION,
GOODS.HUGS_PRODUCT_DATE,
GOODS.HUGS_ASSET_CODE,
GOODS.HUGS_PRICE,
GOODS.HUGS_DEPT_NAME,
GOODS.HUGS_USER_NAME,
GOODS.HUGS_USE_RESON,
GOODS.HUGS_DESCR,
GOODS.HUGS_COMPANY,
CASE GOODS.HUGS_USE_STATUS
WHEN 1 THEN
'使用中'
WHEN 2 THEN
'在库'
ELSE
'作废'
END HUGS_USE_STATUS
,CASE GOODS.HUGS_CHECK_STATUS
WHEN 0 THEN
'尚未盘点'
WHEN -1 THEN
'问题物资'
ELSE
'盘点无误'
END HUGS_CHECK_STATUS
FROM
HU_GOODS GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
</isParameterPresent>
ORDER BY
GOODS.HUGS_ID ASC
<!-- mysql version -->
<!--
SELECT
GROUP_CONCAT(
CONCAT(
"变更：",
CONVERT (
IFNULL(EM.HUGI_USER_ID, ""),
CHAR
),
"_",
IFNULL(EM.HUGI_USER_NAME, ""),
"_",
IFNULL(EM.HUGI_DEPT_NAME, ""),
"_",
IFNULL(EM.HUGI_DESCR, "")
)
) AS ITEMS,
GOODS.HUGS_NAME,
GOODS.HUGS_BRAND,
GOODS.HUGS_VERSION,
GOODS.HUGS_PRODUCT_DATE,
GOODS.HUGS_ASSET_CODE,
GOODS.HUGS_PRICE,
GOODS.HUGS_DEPT_NAME,
GOODS.HUGS_USER_NAME,
GOODS.HUGS_USE_RESON,
GOODS.HUGS_DESCR,
CASE GOODS.HUGS_USE_STATUS
WHEN '1' THEN
'使用中'
WHEN '2' THEN
'在库'
ELSE
'作废'
END HUGS_USE_STATUS
FROM
HU_GOODS GOODS
LEFT JOIN HU_GOODS_ITEM EM ON EM.HUGI_GOODS_ID = GOODS.HUGS_ID
GROUP BY
GOODS.HUGS_ID
ORDER BY
GOODS.HUGS_ID ASC
-->
</select>
<!-- 查询物资公司种类 -->
<select id="getGoodsCompany" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
SELECT OG.HUGS_COMPANY FROM HU_GOODS OG
WHERE OG.HUGS_ID IN(
SELECT MAX(GOOD.HUGS_ID) FROM HU_GOODS GOOD
GROUP BY GOOD.HUGS_COMPANY
)
ORDER BY OG.HUGS_ID ASC
</select>
<!-- 添加物资 -->
<insert id="addGoods" parameterClass="java.util.HashMap">
<!-- mysql version -->
<!--
INSERT INTO HU_GOODS (
`HUGS_ID`,
`HUGS_NAME`,
`HUGS_BRAND`,
`HUGS_VERSION`,
`HUGS_PRODUCT_DATE`,
`HUGS_ASSET_CODE`,
`HUGS_PRICE`,
`HUGS_DEPT_ID`,
`HUGS_DEPT_NAME`,
`HUGS_USER_ID`,
`HUGS_USER_NAME`,
`HUGS_USE_RESON`,
`HUGS_USE_STATUS`,
`HUGS_DESCR`,
`HUGS_STATUS`,
`HUGS_CREATOR`,
`HUGS_CREATE_TIME`,
`HUGS_UPDATE`,
`HUGS_UPDATE_TIME`
)
VALUES
(
NULL,
#HUGS_NAME#, #HUGS_BRAND#,
#HUGS_VERSION#,#HUGS_PRODUCT_DATE#,#HUGS_ASSET_CODE#,#HUGS_PRICE#,#HUGS_DEPT_ID#,#HUGS_DEPT_NAME#,#HUGS_USER_ID#,#HUGS_USER_NAME#,#HUGS_USE_RESON#,#HUGS_USE_STATUS#,#HUGS_DESCR#,#HUGS_STATUS#,#HUGS_CREATOR#,SYSDATE(),
#HUGS_UPDATE#,
SYSDATE()
);
<selectKey resultClass="java.lang.Integer" keyProperty="HUGS_ID">
SELECT LAST_INSERT_ID()
</selectKey>
-->
<!-- oracle version -->
<selectKey resultClass="java.lang.Integer" keyProperty="HUGS_ID" type="pre">
SELECT HU_GOODS_SQ.NEXTVAL AS VALUE FROM DUAL
</selectKey>
INSERT INTO HU_GOODS (
HUGS_ID,
HUGS_NAME,
HUGS_BRAND,
HUGS_VERSION,
HUGS_PRODUCT_DATE,
HUGS_ASSET_CODE,
HUGS_PRICE,
HUGS_DEPT_ID,
HUGS_DEPT_NAME,
HUGS_USER_ID,
HUGS_USER_NAME,
HUGS_USE_RESON,
HUGS_USE_STATUS,
HUGS_DESCR,
HUGS_STATUS,
HUGS_CREATOR,
HUGS_CREATE_TIME,
HUGS_UPDATE,
HUGS_UPDATE_TIME,
HUGS_COMPANY
)
VALUES
(
#HUGS_ID#,
#HUGS_NAME#, #HUGS_BRAND#,
#HUGS_VERSION#,#HUGS_PRODUCT_DATE#,#HUGS_ASSET_CODE#,#HUGS_PRICE#,#HUGS_DEPT_ID#,#HUGS_DEPT_NAME#,#HUGS_USER_ID#,#HUGS_USER_NAME#,#HUGS_USE_RESON#,#HUGS_USE_STATUS#,#HUGS_DESCR#,#HUGS_STATUS#,#HUGS_CREATOR#,SYSDATE,
#HUGS_UPDATE#,
SYSDATE,
#HUGS_COMPANY#
)
</insert>
<!-- 更新物资 -->
<update id="updateGoods" parameterClass="java.util.HashMap">
<!-- oracle version -->
UPDATE HU_GOODS SET
HUGS_NAME=#HUGS_NAME#, HUGS_BRAND=#HUGS_BRAND#,
HUGS_VERSION=#HUGS_VERSION#,HUGS_PRODUCT_DATE=#HUGS_PRODUCT_DATE#,HUGS_PRICE=#HUGS_PRICE#,HUGS_DEPT_ID=#HUGS_DEPT_ID#,HUGS_DEPT_NAME=#HUGS_DEPT_NAME#,HUGS_USER_ID=#HUGS_USER_ID#,HUGS_USER_NAME=#HUGS_USER_NAME#,HUGS_USE_RESON=#HUGS_USE_RESON#,HUGS_USE_STATUS=#HUGS_USE_STATUS#,HUGS_DESCR=#HUGS_DESCR#,HUGS_STATUS=#HUGS_STATUS#,HUGS_CREATOR=#HUGS_CREATOR#,HUGS_UPDATE_TIME=SYSDATE
where HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
<!-- mysql version -->
<!--
UPDATE HU_GOODS SET
HUGS_NAME=#HUGS_NAME#, HUGS_BRAND=#HUGS_BRAND#,
HUGS_VERSION=#HUGS_VERSION#,HUGS_PRODUCT_DATE=#HUGS_PRODUCT_DATE#,HUGS_PRICE=#HUGS_PRICE#,HUGS_DEPT_ID=#HUGS_DEPT_ID#,HUGS_DEPT_NAME=#HUGS_DEPT_NAME#,HUGS_USER_ID=#HUGS_USER_ID#,HUGS_USER_NAME=#HUGS_USER_NAME#,HUGS_USE_RESON=#HUGS_USE_RESON#,HUGS_USE_STATUS=#HUGS_USE_STATUS#,HUGS_DESCR=#HUGS_DESCR#,HUGS_STATUS=#HUGS_STATUS#,HUGS_CREATOR=#HUGS_CREATOR#,HUGS_UPDATE_TIME=SYSDATE()
where HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
-->
</update>
<!-- 查询固定资产编码数量 -->
<select id="selectHugsAssetCodeCount" parameterClass="java.util.HashMap"
resultClass="int">
SELECT COUNT(HG.HUGS_ID) AS COUNT FROM HU_GOODS HG WHERE
HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
</select>
<!-- 由固定资产编码查询对应的记录id集合 -->
<select id="selectGoodsIdListByHugsAssetCode" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
SELECT HG.HUGS_ID FROM HU_GOODS HG WHERE
HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
</select>
<!-- ################################################################################# -->
<!-- HU_GOODS_ITEM -->
<!-- 变更项列集合 -->
<sql id="Goods_Item_Base_Column_List" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Fri Dec 12 11:53:06 CST 2014.
-->
HUGI_ID, HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME, HUGI_USER_ID, HUGI_USER_NAME,
HUGI_USE_RESON, HUGI_USE_STATUS, HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME
</sql>
<!-- where条件 -->
<sql id="Goods_Item_Where_Clause" >
<dynamic prepend="WHERE 1=1">
<isNotEmpty property="HUGI_GOODS_ID">
AND HUGI_GOODS_ID = #HUGI_GOODS_ID#
</isNotEmpty>
<isNotEmpty property="HUGI_DEPT_NAME">
AND HUGI_DEPT_NAME LIKE '%$HUGI_DEPT_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGI_USER_NAME">
AND HUGI_USER_NAME LIKE '%$HUGI_USER_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGI_USE_RESON">
AND HUGI_USE_RESON LIKE '%$HUGI_USE_RESON$%'
</isNotEmpty>
</dynamic>
</sql>
<!-- 物资变更项总数 -->
<select id="getGoodsItemListCnt" resultClass="java.lang.Integer"
parameterClass="java.util.HashMap">
SELECT COUNT(*) FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
</isParameterPresent>
</select>
<!-- 物资变更项分页集合查询 -->
<select id="getGoodsItemPageList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT * FROM
(
SELECT
A .*, ROWNUM r
FROM
(
SELECT <include refid="HuGoods.Goods_Item_Base_Column_List" />
FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
) A
WHERE
ROWNUM <![CDATA[ <= ]]> #end#
) B
WHERE r <![CDATA[>]]> #start#
<!-- mysql version -->
<!--
SELECT <include refid="HuGoods.Goods_Item_Base_Column_List" />
FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
LIMIT #start#,#end#
-->
</select>
<!-- 添加记录明细 -->
<insert id="addGoodsItem" parameterClass="java.util.HashMap">
<!-- oracle version -->
<selectKey resultClass="java.lang.Integer" keyProperty="HUGI_ID" type="pre">
SELECT HU_GOODS_ITEM_SQ.NEXTVAL AS VALUE FROM DUAL
</selectKey>
INSERT INTO HU_GOODS_ITEM (HUGI_ID,HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME,
HUGI_USER_ID, HUGI_USER_NAME, HUGI_USE_RESON, HUGI_USE_STATUS,
HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME)
VALUES (#HUGI_ID#,#HUGI_GOODS_ID#, #HUGI_DEPT_ID#, #HUGI_DEPT_NAME#,
#HUGI_USER_ID#, #HUGI_USER_NAME#, #HUGI_USE_RESON#, #HUGI_USE_STATUS#,
#HUGI_DESCR#, #HUGI_STATUS#, #HUGI_CREATOR#, SYSDATE,
#HUGI_UPDATE#, SYSDATE)
<!-- mysql version -->
<!--
INSERT INTO HU_GOODS_ITEM (HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME,
HUGI_USER_ID, HUGI_USER_NAME, HUGI_USE_RESON, HUGI_USE_STATUS,
HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME)
VALUES (#HUGI_GOODS_ID:INTEGER#, #HUGI_DEPT_ID:VARCHAR#, #HUGI_DEPT_NAME:VARCHAR#,
#HUGI_USER_ID:INTEGER#, #HUGI_USER_NAME:VARCHAR#, #HUGI_USE_RESON:VARCHAR#, #HUGI_USE_STATUS:INTEGER#,
#HUGI_DESCR:VARCHAR#, #HUGI_STATUS:INTEGER#, #HUGI_CREATOR:VARCHAR#, SYSDATE(),
#HUGI_UPDATE:VARCHAR#, SYSDATE())
<selectKey resultClass="java.lang.Integer" keyProperty="HUGI_ID">
SELECT LAST_INSERT_ID() AS HUGI_ID
</selectKey>
-->
</insert>
<!-- 删除物资变更明细 -->
<delete id="deleteGoodsItem" parameterClass="java.util.HashMap">
DELETE FROM HU_GOODS_ITEM WHERE HUGI_ID =#HUGI_ID#
</delete>
</sqlMap>
