- <?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>
来源: http://www.phpxs.com/code/1008269/