<?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.fbsMomOrder.mapper.FbsMomOrderdetailMapper">
    <select id="getFbsMomOrderdetailSum" resultType="java.util.HashMap">
        SELECT
            sum( MOD.QualifiedInQty ) AS '已完成',
            sum( MOD.Qty ) - sum( MOD.QualifiedInQty ) AS '未完成'
        FROM
            mom_orderdetail
            MOD LEFT JOIN mom_order mo ON MOD.MoId = mo.MoId
        WHERE
            substring( mo.MoCode, 3, 4 ) = substring( CONVERT ( VARCHAR ( 100 ), GETDATE ( ), 112 ), 3, 4 )

    </select>

    <select id="getQualifiedInQty" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
        i.cInvName ,
        sum(mo.QualifiedInQty) as qualifiedinqty
        FROM
        mom_orderdetail mo
        LEFT JOIN Inventory i ON mo.InvCode = i.cInvCode
        GROUP BY
        i.cInvName
    </select>

    <select id="getQualifiedInQtyWeek" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
       <!-- SELECT

            i.cInvName,
            sum( QualifiedInQty ) as QualifiedInQty
        FROM
            mom_orderdetail mo
            LEFT JOIN Inventory i ON mo.InvCode = i.cInvCode
            left join mom_morder mm on mo.modid=mm.modid
        WHERE
            datediff( WEEK, mm.DueDate, getdate ( ) ) = 0
        GROUP BY
            i.cInvName-->
        SELECT
            i.cInvName,
            sum( iQuantity ) AS QualifiedInQty
        FROM
          rdrecords10 mo
        LEFT JOIN Inventory i ON mo.cInvCode = i.cInvCode
        LEFT JOIN rdrecord10 mm ON mo.ID = mm.ID
        WHERE
            mm.cWhCode = '01'
            AND datediff( WEEK, mm.dDate, getdate ( ) ) = 0
        GROUP BY
            i.cInvName
    </select>

    <select id="getMonthList" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
            m.MoId,
            m.MoCode,
            i.cInvCode,
            i.cInvStd,
            i.cInvName,
            i.cInvDefine13,
            mo.Qty,
            mo.QualifiedInQty,
            m.Define12
        FROM
            mom_orderdetail mo
            LEFT JOIN mom_order m ON mo.MoId= m.MoId
            LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
            left join mom_morder mm on mo.MoDId=mm.MoDId
        WHERE
            <!--datediff(
            MONTH,
            mm.StartDate,
            getdate()) =0-->
            substring( m.MoCode, 3, 4 ) = substring( CONVERT ( VARCHAR ( 100 ), GETDATE ( ), 112 ), 3, 4 )
        ORDER BY
            m.createtime DESC
    </select>


    <select id="getMonthListStatre" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
        m.MoId,
        m.MoCode,
        i.cInvCode,
        i.cInvStd,
        i.cInvName,
        i.cInvDefine13,
        mo.Qty,
        mo.QualifiedInQty,
        m.Define12
        FROM
        mom_orderdetail mo
        LEFT JOIN mom_order m ON mo.MoId= m.MoId
        LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        left join mom_morder mm on mo.MoDId=mm.MoDId
        WHERE

        <!--datediff(
        MONTH,
        mm.StartDate,
        getdate()) =0-->
        mo.Status = 3
        <if test="fbsMomOrderdetail.mocode!=null and fbsMomOrderdetail.mocode!=''">
            AND m.MoCode=#{fbsMomOrderdetail.mocode}
        </if>
        ORDER BY
        m.createtime DESC
    </select>

    <select id="getMonthListStatreReviewed" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
        m.MoId,
        m.MoCode,
        i.cInvCode,
        i.cInvStd,
        i.cInvName,
        i.cInvDefine13,
        mo.Qty,
        mo.QualifiedInQty,
        m.Define12,
        mo.Status
        FROM
        mom_orderdetail mo
        LEFT JOIN mom_order m ON mo.MoId= m.MoId
        LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        left join mom_morder mm on mo.MoDId=mm.MoDId
        WHERE
        <!--datediff(
        MONTH,
        mm.StartDate,
        getdate()) =0-->
        <!--substring(m.MoCode, 3, 4)=SUBSTRING ( CONVERT ( VARCHAR ( 100 ), GETDATE( ), 112 ), 3, 4 )-->

        (

            <if test="fbsMomOrderdetail.yearMonth!=null and fbsMomOrderdetail.yearMonth!=''">
                SUBSTRING ( CONVERT ( VARCHAR ( 100 ), mo.CloseTime, 112 ), 3, 4 )=#{fbsMomOrderdetail.yearMonth}
                OR
                substring(m.MoCode, 3, 4)=#{fbsMomOrderdetail.yearMonth}
            </if>
            <if test="fbsMomOrderdetail.yearMonth==null or fbsMomOrderdetail.yearMonth==''">
                datediff(
                MONTH,
                mo.CloseTime,
                getdate()) =0
                OR
                substring(m.MoCode, 3, 4)=SUBSTRING ( CONVERT ( VARCHAR ( 100 ), GETDATE( ), 112 ), 3, 4 )
            </if>
        )
        AND mo.Status = 4
        <if test="fbsMomOrderdetail.mocode!=null and fbsMomOrderdetail.mocode!=''">
            AND m.MoCode=#{fbsMomOrderdetail.mocode}
        </if>

        ORDER BY
        m.createtime DESC
    </select>

    <select id="getMonthListNotStatre" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
        m.MoId,
        m.MoCode,
        i.cInvCode,
        i.cInvStd,
        i.cInvName,
        i.cInvDefine13,
        mo.Qty,
        mo.QualifiedInQty,
        m.Define12
        FROM
        mom_orderdetail mo
        LEFT JOIN mom_order m ON mo.MoId= m.MoId
        LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        left join mom_morder mm on mo.MoDId=mm.MoDId
        WHERE

        <!--datediff(
        MONTH,
        mm.StartDate,
        getdate()) =0-->
        substring(m.MoCode, 3, 4)=#{month}
        ORDER BY
        m.createtime DESC
    </select>

    <select id="getMonthListGroupDefine12" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
        Round(SUM( (mo.Qty*isnull(i.cInvDefine13,0))  ),2) cInvDefine13,
            SUM ( mo.Qty ) Qty,
            SUM ( mo.QualifiedInQty ) QualifiedInQty,
            m.Define12
        FROM
            mom_orderdetail mo
            LEFT JOIN mom_order m ON mo.MoId= m.MoId
            LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        WHERE
            SUBSTRING ( m.MoCode, 3, 4 ) = SUBSTRING ( CONVERT ( VARCHAR ( 100 ), GETDATE( ), 112 ), 3, 4 )
            AND m.Define12 IS NOT NULL
        GROUP BY
            m.Define12
    </select>

    <select id="getDayList" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
            m.MoCode,
            i.cInvCode,
            i.cInvStd,
            i.cInvName,
            mo.Qty,
            mo.QualifiedInQty
        FROM
            mom_orderdetail mo
        LEFT JOIN mom_order m ON mo.MoId= m.MoId
        LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        left join mom_morder mm on mo.MoDId=mm.MoDId
        WHERE
            DateDiff(
            dd,
            mm.StartDate,
            getdate()) =0
    </select>

    <select id="getListByOrderSheetCurrentStock" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT SUM
            ( iQuantity ) as materialPreparationTotal,
            cInvCode
        FROM
            CurrentStock
        WHERE
            cWhCode in ('02','10')
        GROUP BY
            cInvCode
    </select>


    <select id="getListByOrderSheetMomMoallocate" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        <!--
        SELECT
            SUM ( mm.Qty ) as materialPreparationNumber,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd
        FROM
            mom_moallocate mm
            LEFT JOIN mom_orderdetail mo ON mm.MoDId= mo.MoDId
            left join mom_order m on mo.MoId=m.MoId
            left join Inventory i on mo.InvCode=i.cInvCode
        GROUP BY
            mo.MoId,mo.InvCode,m.MoCode,i.cinvname,i.cinvcode,i.cinvstd -->
        SELECT SUM
            ( mm.Qty ) AS Qtys,
            sum(mm.IssQty) as IssQty,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd
        FROM
          mom_moallocate mm
        LEFT JOIN mom_orderdetail mo ON mm.MoDId= mo.MoDId
        LEFT JOIN mom_order m ON mo.MoId= m.MoId
        LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        WHERE mo.RelsUser is NOT NULL
        AND SUBSTRING ( m.MoCode, 3, 4 ) = SUBSTRING ( CONVERT ( VARCHAR ( 100 ), GETDATE( ), 112 ), 3, 4 )
        GROUP BY
            mo.MoId,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd
       <!-- HAVING
            SUM( mm.Qty )-SUM(mm.IssQty)>0 -->
    </select>

    <select id="getListByOrderSheetEnough" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT SUM
            ( mm.Qty ) AS Qtys,
            sum(mm.IssQty) as IssQty,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd
        FROM
          mom_moallocate mm
        LEFT JOIN mom_orderdetail mo ON mm.MoDId= mo.MoDId
        LEFT JOIN mom_order m ON mo.MoId= m.MoId
            LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        WHERE SUBSTRING ( m.MoCode, 3, 4 ) = SUBSTRING ( CONVERT ( VARCHAR ( 100 ), GETDATE( ), 112 ), 3, 4 )
        GROUP BY
            mo.MoId,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd
        HAVING
            SUM( mm.Qty )-SUM(mm.IssQty) <![CDATA[ <=]]>  0
    </select>

    <select id="getListByOrderSheetNotEnough" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
         SELECT
            mm.Qty AS Qtys,
            mm.IssQty as IssQty,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd,
            moi.cinvname mocinvname
        FROM
          mom_moallocate mm
        LEFT JOIN mom_orderdetail mo ON mm.MoDId= mo.MoDId
        LEFT JOIN mom_order m ON mo.MoId= m.MoId
        LEFT JOIN Inventory i ON mm.InvCode= i.cInvCode
        LEFT JOIN Inventory moi on moi.cInvCode=mo.InvCode
				where mm.Qty-mm.IssQty>0 and mo.RelsUser is NOT NULL
				AND SUBSTRING ( m.MoCode, 3, 4 ) = SUBSTRING ( CONVERT ( VARCHAR ( 100 ), GETDATE( ), 112 ), 3, 4 )
				ORDER BY m.MoCode
    </select>




    <select id="getApplyList" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
       <!-- SELECT SUM
            ( mm.RequisitionQty ) - SUM ( RequisitionIssQty ) AS unfinishedQuantity,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd
        FROM
            mom_moallocate mm
            LEFT JOIN mom_orderdetail mo ON mm.MoDId= mo.MoDId
            LEFT JOIN mom_order m ON mo.MoId= m.MoId
            LEFT JOIN Inventory i ON mo.InvCode= i.cInvCode
        GROUP BY
            mo.MoId,
            mo.InvCode,
            m.MoCode,
            i.cinvname,
            i.cinvcode,
            i.cinvstd
        HAVING
            SUM ( mm.RequisitionQty )-sum(RequisitionIssQty)>0 -->
        SELECT
        mas.cInvCode,
        i.cInvName,
        ma.cMaker,
        (mas.iQuantity - mas.fOutQuantity) as unfinishedQuantity,
        d.cDepName,
        ma.dDate
        FROM
        MaterialAppVouchs mas
        LEFT JOIN MaterialAppVouch ma ON mas.ID= ma.ID
        LEFT JOIN Department d ON ma.cDepCode= d.cDepCode
        LEFT JOIN Inventory i ON mas.cInvCode= i.cInvCode
        WHERE
        isnull(mas.iQuantity,0) - isnull(mas.fOutQuantity,0) >0
        AND mas.cBCloser is NULL
        AND datediff( MONTH, ma.dDate, getdate ( ) ) = 0
    </select>

    <select id="getApplyCompleteSum" resultType="java.lang.Integer">
        SELECT
        count(mas.ID)
        FROM
        MaterialAppVouchs mas
        LEFT JOIN MaterialAppVouch ma ON mas.ID= ma.ID
        LEFT JOIN Department d ON ma.cDepCode= d.cDepCode
        LEFT JOIN Inventory i ON mas.cInvCode= i.cInvCode
        WHERE
        mas.iQuantity - mas.fOutQuantity <![CDATA[ <=]]>0
    </select>

    <select id="getLatelyDayMomOrderList" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
            mo.MoCode,
            mod.QualifiedInQty,
            mo.CreateTime
        FROM
          mom_order mo
        LEFT JOIN mom_orderdetail MOD ON mo.MoId = MOD.MoId
        WHERE
            DateDiff( dd, mo.CreateTime, getdate ( ) ) <![CDATA[ <=]]> 90
    </select>

    <select id="getMomOrderDetailByMoCode" resultType="org.jeecg.modules.fbsMomOrder.entity.FbsMomOrderdetail">
        SELECT
            *
        FROM
            mom_order mo
            LEFT JOIN mom_orderdetail MOD ON mo.MoId = MOD.MoId
        WHERE
            mo.MoCode = #{moCode}
    </select>
</mapper>