<?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(QualifiedInQty) as '已完成',sum(Qty)-sum(QualifiedInQty) as '未完成' from mom_orderdetail
    </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
            top 10
            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>

    <select id="getMonthList" 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(
            MONTH,
            mm.StartDate,
            getdate()) =0
    </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
        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
        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="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>
</mapper>