<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.fbsInventory.mapper.FbsInventoryMapper">
    <select id="getInventoryGetByOrderId" resultType="org.jeecg.modules.fbsInventory.entity.FbsInventory" >
        SELECT
        i.cInvDefine13,i.cInvCode as cinvcode,i.cInvName as cinvname,m.CostItemName,
        <!--Convert(decimal(18,2),m.Qty) as qty,-->
        round(m.Qty,2) as qty,
        m.SortSeq as sortSeq,
        m.SAPStatus as SAPStatus
        FROM
        mom_orderdetail m
        LEFT JOIN Inventory i ON m.InvCode = i.cInvCode
        WHERE
        m.MoId = #{moId}
    </select>

    <select id="getStockList" resultType="java.util.HashMap">

        select <!--top 20--> a.iQuantity realStockSum,b.iSafeNum safeStockSum ,b.cInvCode materialCode,b.cInvName materialName,cInvStd specs from

        (select cInvCode,sum(iQuantity) as iQuantity  from CurrentStock group by cInvCode) a

        left join Inventory b on a.cInvCode = b.cInvCode
        where b.iSafeNum is not null

    </select>

    <select id="getAllStockList" resultType="java.util.HashMap">

        select a.iQuantity realStockSum,b.cInvName materialName,b.cInvCode cInvCode from
        (select cInvCode,sum(iQuantity) as iQuantity  from CurrentStock group by cInvCode) a
        left join Inventory b on a.cInvCode = b.cInvCode
        where b.cInvCCode='0101' and a.iQuantity>0
       <!-- SELECT
          cInvName materialName,
          SUM ( iSafeNum ) realStockSum
        FROM
          Inventory
        where cInvCCode='0101'
        GROUP BY
          cInvCode,
          cInvName -->

    </select>

    <select id="getByInvCode" resultType="java.lang.String">
        select cInvDefine13 from Inventory where cInvCode=#{invCode}
    </select>

    <update id="updateCInvDefine13">
        UPDATE Inventory
            set cInvDefine13=#{standardWorkingHoursZ}
        where
            cInvCode=#{invCode}
    </update>

    <select id="getDrawingCode" resultType="org.jeecg.modules.fbsInventory.entity.FbsInventory">
        SELECT
            i.cInvCode,
            i.cInvDefine6
        FROM
            bom_opcomponent bop
            LEFT JOIN bas_part basp ON bop.ComponentId = basp.PartId
            LEFT JOIN Inventory i ON basp.InvCode = i.cInvCode
            left join mom_orderdetail momo on momo.BomId=bop.BomId
            left join mom_order mo ON mo.MoId = momo.MoId
        WHERE
            mo.MoCode = #{moCode}
            AND i.cInvCCode = '020206'
    </select>

    <select id="getInventory" resultType="org.jeecg.modules.fbsInventory.entity.FbsInventory">
        select * from Inventory where cInvCode=#{cInvCode}
    </select>

    <update id="updateCInvDefine13ByCInvCode">
        update inventory set cInvDefine13=#{cInvDefine13} where cInvCode=#{cInvCode}
    </update>

    <!-- 获取SAP数据新增存货 -->
    <insert id="addSAPInventory" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into inventory (id,cInvCode,cInvName,iSafeNum,cInvCCode,cInvStd,bPropertyCheck,cInvCName,isSerialNumber,create_time,pull_date) values
            (
            #{item.id},
            #{item.cInvCode},
            #{item.cInvName},
            #{item.iSafeNum},
            #{item.cInvCCode},
            #{item.cInvStd},
            #{item.bPropertyCheck},
            #{item.cInvCName},
            #{item.isSerialNumber},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除存货 -->
    <delete id="deleteSAPInventory" parameterType="java.util.List">
        delete from inventory where cInvCode in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.cInvCode}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增库存存量 -->
    <insert id="addSAPCurrentStock" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into currentstock (id,cInvCode,iQuantity,cWhCode,cWhName,WBS,cVenCode,create_time,pull_date) values
            (
            #{item.id},
            #{item.cInvCode},
            #{item.iQuantity},
            #{item.cWhCode},
            #{item.cWhName},
            #{item.WBS},
            #{item.cVenCode},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除库存存量 -->
    <delete id="deleteSAPCurrentStock">
        delete from currentstock
    </delete>



    <!-- 获取SAP数据新增产成品入库主表 -->
    <insert id="addSAPRdrecord10" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into rdrecord10 (ID,cWhCode,cWhName,dDate,create_time,pull_date) values
            (
            #{item.ID},
            #{item.cWhCode},
            #{item.cWhName},
            #{item.dDate},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除产成品入库主表 -->
    <delete id="deleteSAPRdrecord10" parameterType="java.util.List">
        delete from rdrecord10 where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增产成品入库子表 -->
    <insert id="addSAPRdrecords10" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into rdrecords10 (ID,cInvCode,cBatch,iQuantity,create_time,pull_date) values
            (
            #{item.ID},
            #{item.cInvCode},
            #{item.cBatch},
            #{item.iQuantity},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除产成品入库子表 -->
    <delete id="deleteSAPRdrecords10" parameterType="java.util.List">
        delete from rdrecords10 where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增供应商 -->
    <insert id="addSAPVendor" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into Vendor (id,cVenCode,cMemo,cVenName,create_time,pull_date) values
            (
            #{item.id},
            #{item.cVenCode},
            #{item.cMemo},
            #{item.cVenName},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 删除存在的供应商 -->
    <delete id="deleteSAPVendor" parameterType="java.util.List">
        delete from Vendor where cVenCode in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.cVenCode}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增采购订单主表 -->
    <insert id="addSAPPoPomain" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into po_pomain (id,dPODate,cVenCode,POID,create_time,pull_date) values
            (
            #{item.id},
            #{item.dPODate},
            #{item.cVenCode},
            #{item.POID},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除采购订单主表 -->
    <delete id="deleteSAPPoPomain" parameterType="java.util.List">
        delete from po_pomain where POID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.POID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增采购订单子表 -->
    <insert id="addSAPPoPodetails" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into po_podetails (ID,dArriveDate,cInvCode,iQuantity,cItemName,POID,iReceivedQTY,iArrQTY,freceivedqty,SAPID,create_time,pull_date) values
            (
            #{item.ID},
            #{item.dArriveDate},
            #{item.cInvCode},
            #{item.iQuantity},
            #{item.cItemName},
            #{item.POID},
            #{item.iReceivedQTY},
            #{item.iArrQTY},
            #{item.freceivedqty},
            #{item.SAPID},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除采购订单子表 -->
    <delete id="deleteSAPPoPodetails" parameterType="java.util.List">
        delete from po_podetails where POID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.POID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增采购到货主表 -->
    <insert id="addSAPPuArrivalvouch" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into pu_arrivalvouch (ID,dDate,cVenCode,idd,create_time,pull_date) values
            (
            #{item.ID},
            #{item.dDate},
            #{item.cVenCode},
            #{item.idd},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除采购到货主表 -->
    <delete id="deleteSAPPuArrivalvouch" parameterType="java.util.List">
        delete from pu_arrivalvouch where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增采购到货子表 -->
    <insert id="addSAPPuArrivalvouchs" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into pu_arrivalvouchs (ID,fRealQuantity,fValidQuantity,fValidInQuan,cInvCode,cItemName,iQuantity,bInspect,Autoid,SAPAutoid,create_time,pull_date) values
            (
            #{item.ID},
            #{item.fRealQuantity},
            #{item.fValidQuantity},
            #{item.fValidInQuan},
            #{item.cInvCode},
            #{item.cItemName},
            #{item.iQuantity},
            #{item.bInspect},
            #{item.Autoid},
            #{item.SAPAutoid},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除采购到货子表 -->
    <delete id="deleteSAPPuArrivalvouchs" parameterType="java.util.List">
        delete from pu_arrivalvouchs where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增领料单主表 -->
    <insert id="addSAPMaterialappvouch" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into materialappvouch (ID,idd,cMaker,dDate,businessType,cDepCode,cDepName,create_time,pull_date) values
            (
            #{item.ID},
            #{item.idd},
            #{item.cMaker},
            #{item.dDate},
            #{item.businessType},
            #{item.cDepCode},
            #{item.cDepName},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除领料单主表 -->
    <delete id="deleteSAPMaterialappvouch" parameterType="java.util.List">
        delete from materialappvouch where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增领料单子表 -->
    <insert id="addSAPMaterialappvouchs" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into materialappvouchs (ID,idd,cInvCode,iQuantity,fOutQuantity,cBCloser,dArriveDate,create_time,pull_date) values
            (
            #{item.ID},
            #{item.idd},
            #{item.cInvCode},
            #{item.iQuantity},
            #{item.fOutQuantity},
            #{item.cBCloser},
            #{item.dArriveDate},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除领料单子表 -->
    <delete id="deleteSAPMaterialappvouchs" parameterType="java.util.List">
        delete from materialappvouchs where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增检验信息 -->
    <insert id="addSAPQmcheckvoucher" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into qmcheckvoucher (id,DDATE,CVENCODE,CINVCODE,FDTQUANTITY,FDISBREAKQUANTITY,FQUANTITY,CINSPECTCODE,FDISQUANTITY,CCHECKCODE,create_time,pull_date) values
            (
            #{item.id},
            #{item.DDATE},
            #{item.CVENCODE},
            #{item.CINVCODE},
            #{item.FDTQUANTITY},
            #{item.FDISBREAKQUANTITY},
            #{item.FQUANTITY},
            #{item.CINSPECTCODE},
            #{item.FDISQUANTITY},
            #{item.CCHECKCODE},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除检验单 -->
    <delete id="deleteSAPQmcheckvoucher" parameterType="java.util.List">
        delete from qmcheckvoucher where CINSPECTCODE in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.CINSPECTCODE}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增销售出库单主表 -->
    <insert id="addSAPRdrecord32" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into rdrecord32 (idd,ID,cBusCode,cSTCode,type,cMemo,dDate,postingDate,deliveryStatus,create_time,pull_date) values
            (
            #{item.idd},
            #{item.ID},
            #{item.cBusCode},
            #{item.cSTCode},
            #{item.type},
            #{item.cMemo},
            #{item.dDate},
            #{item.postingDate},
            #{item.deliveryStatus},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除销售出库单主表 -->
    <delete id="deleteSAPRdrecord32" parameterType="java.util.List">
        delete from rdrecord32 where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增销售出库单子表 -->
    <insert id="addSAPRdrecords32" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into rdrecords32 (idd,ID,cItemCode,iQuantity,cInvCode,rowCode,cWhCode,cWhName,create_time,pull_date) values
            (
            #{item.idd},
            #{item.ID},
            #{item.cItemCode},
            #{item.iQuantity},
            #{item.cInvCode},
            #{item.rowCode},
            #{item.cWhCode},
            #{item.cWhName},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除销售出库单子表 -->
    <delete id="deleteSAPRdrecords32" parameterType="java.util.List">
        delete from rdrecords32 where ID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.ID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增发退货单主表 -->
    <insert id="addSAPDispatchList" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into dispatchlist (idd,DLID,dDate,cVerifier,create_time,pull_date) values
            (
            #{item.idd},
            #{item.DLID},
            #{item.dDate},
            #{item.cVerifier},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除发退货单主表 -->
    <delete id="deleteSAPDispatchList" parameterType="java.util.List">
        delete from dispatchlist where DLID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.DLID}
        </foreach>
    </delete>

    <!-- 获取SAP数据新增发退货单子表 -->
    <insert id="addSAPDispatchLists" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into dispatchlists (idd,DLID,cWhCode,iQuantity,cItemCode,cItemName,fOutQuantity,cWhName,cInvCode,create_time,pull_date) values
            (
            #{item.idd},
            #{item.DLID},
            #{item.cWhCode},
            #{item.iQuantity},
            #{item.cItemCode},
            #{item.cItemName},
            #{item.fOutQuantity},
            #{item.cWhName},
            #{item.cInvCode},
            #{item.create_time},
            #{item.pull_date}
            )
        </foreach>
    </insert>

    <!-- 获取SAP数据删除销售出库单子表 -->
    <delete id="deleteSAPDispatchLists" parameterType="java.util.List">
        delete from dispatchlists where DLID in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.DLID}
        </foreach>
    </delete>

    <!--新增存货-->
    <insert id="addInventory" parameterType="java.util.Map">
        INSERT INTO inventory ( id, cInvCode,cInvName,cInvDefine13,iSafeNum,cInvCCode,cInvCName )
                       VALUES
                       ( #{inventory.id}, #{inventory.cInvCode},#{inventory.cInvName},#{inventory.cInvDefine13},#{inventory.iSafeNum},#{inventory.cInvCCode},#{inventory.cInvCName} )
    </insert>
</mapper>