<?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.fbsPuArrivalvouch.mapper.FbsPuArrivalvouchMapper">

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

        <!-- SELECT b.cVenCode '供应商编码',c.cVenName '供应商名称',a.cInvCode '物料编码',d.cInvName '物料名称',d.cInvStd '规格',
                 iQuantity - fInspectQuantity '待检数量',b.dDate '到货日期'-->
       <!-- SELECT b.cVenCode '供应商编码',c.cVenName vendorName,a.cInvCode materialCode,d.cInvName materialName,d.cInvStd specs,
        a.iQuantity - a.fInspectQuantity arriveSum,CONVERT(varchar(30),b.dDate,23) arriveDate
        FROM
        PU_ArrivalVouchs a
        left join PU_ArrivalVouch b on a.id = b.id
        left join Vendor c on b.cVenCode = c.cVenCode
        left join Inventory d on a.cInvCode = d.cInvCode
        where  (a.iQuantity - a.fInspectQuantity) &gt; 0
        order by b.dDate desc-->

        <!--

        SELECT
        pp.cVenCode vendorName,
        pps.cInvCode materialCode,
        i.cInvName materialName,
        CONVERT ( VARCHAR ( 30 ), pps.dArriveDate, 112 ) arriveDate,
        CONVERT ( VARCHAR ( 30 ), pp.dPODate, 112 ) dPODate,
        pps.iArrQTY arriveSum,
        sum( puas.fInspectQuantity ) AS fInspectQuantity,
        pps.freceivedqty
        FROM
        PO_Podetails pps
        LEFT JOIN PO_Pomain pp ON pps.POID = pp.POID
        LEFT JOIN Inventory i ON pps.cInvCode = i.cInvCode
        LEFT JOIN PU_ArrivalVouchs puas ON pps.ID = puas.iPOsID
        WHERE
        pps.iArrQTY - pps.freceivedqty > 0
        GROUP BY
        pp.cVenCode,
        pps.cInvCode,
        i.cInvName,
        pps.dArriveDate,
        pp.dPODate,
        pps.iArrQTY,
        pps.freceivedqty
        ORDER BY
        pp.dPODate DESC
        -->
        SELECT
        pp.cVenCode vendorName,
        pps.cInvCode materialCode,
        i.cInvName materialName,
        CONVERT ( VARCHAR ( 30 ), pps.dArriveDate, 112 ) arriveDate,
        CONVERT ( VARCHAR ( 30 ), pp.dPODate, 112 ) dPODate,
        pps.iQuantity iuantity,
        pps.iArrQTY iarrQTY,
        pps.freceivedqty
        FROM
        PO_Podetails pps
        LEFT JOIN PO_Pomain pp ON pps.POID = pp.POID
        LEFT JOIN Inventory i ON pps.cInvCode = i.cInvCode
        WHERE
        pps.iQuantity - pps.freceivedqty > 0
        ORDER BY
        pp.dPODate DESC

    </select>

    <select id="getCheckSum" resultType="int">

       <!-- SELECT count(1)
        FROM
        PU_ArrivalVouchs a
        left join PU_ArrivalVouch b on a.id = b.id
        left join Vendor c on b.cVenCode = c.cVenCode
        left join Inventory d on a.cInvCode = d.cInvCode
        where  (a.iQuantity - a.fInspectQuantity) &lt;= 0 -->
        SELECT
        count(pps.POID)
        FROM
        PO_Podetails pps
        LEFT JOIN PO_Pomain pp ON pps.POID= pp.POID
        LEFT JOIN Inventory i ON pps.cInvCode= i.cInvCode
        WHERE
        pps.iQuantity- pps.freceivedqty <![CDATA[ <= ]]> 0
        AND DateDiff(year, GetDate(), pp.dPODate ) = 0
    </select>


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

        <!-- SELECT b.cVenCode '供应商编码',c.cVenName '供应商名称',a.cInvCode '物料编码',d.cInvName '物料名称',d.cInvStd '规格',
                 fValidQuantity - fRealQuantity '已到货未入库数据',b.dportdate '到货日期'-->
        SELECT b.cVenCode cVenCode,c.cVenName vendorName,a.cInvCode materialCode,d.cInvName materialName,d.cInvStd specs,
        (a.fRealQuantity - a.fValidInQuan) arriveSum,CONVERT(varchar(30),b.dDate ,112) arriveDate,a.cItemName cItemName
        FROM
        PU_ArrivalVouchs a
        left join PU_ArrivalVouch b on a.id = b.id
        left join Vendor c on b.cVenCode = c.cVenCode
        left join Inventory d on a.cInvCode = d.cInvCode
        where
        <!--(a.fValidQuantity - a.fValidInQuan) &gt; 0-->
        (a.fRealQuantity - a.fValidInQuan) &gt; 0
        and d.bPropertyCheck=1
        order by b.dDate desc

    </select>

    <select id="getInSum" resultType="int">
       SELECT count(1)
        FROM
        PU_ArrivalVouchs a
        left join PU_ArrivalVouch b on a.id = b.id
        left join Vendor c on b.cVenCode = c.cVenCode
        left join Inventory d on a.cInvCode = d.cInvCode
        where  (a.fValidQuantity - a.fValidInQuan) &lt;= 0

    </select>

    <select id="getPassRateByVendor" resultType="org.jeecg.modules.fbsPuArrivalvouch.entity.FbsPuArrivalvouch">
        SELECT
            top 10
        <!--v.cVenName,-->
            p.cVenCode,
            round( CAST (( SUM ( pa.fValidQuantity ) * 1.0 / SUM ( pa.iQuantity )) AS FLOAT ), 0 ) * 100 AS passRate
        FROM
            PU_ArrivalVouchs pa
            LEFT JOIN PU_ArrivalVouch p ON pa.ID= p.ID
            LEFT JOIN Vendor v ON p.cVenCode= v.cVenCode
        WHERE
            datediff(
            MONTH,
            p.dDate,
            getdate()) = 0
        GROUP BY
            p.cVenCode
        <!--,v.cVenName-->
    </select>

    <select id="getCheckRateByBatchno" resultType="org.jeecg.modules.fbsPuArrivalvouch.entity.FbsPuArrivalvouch">
        SELECT

            <!--v.cVenName,-->
            p.cVenCode,
            SUM (ISNULL(pa.fValidQuantity,0)) fvalidquantity,
            SUM (ISNULL(pa.iQuantity,0)) iquantity,
            round( CAST (( SUM ( pa.fValidQuantity ) * 1.0 / SUM ( pa.iQuantity )) AS FLOAT ), 0 ) * 100 AS passRate
        FROM
            PU_ArrivalVouchs pa
            LEFT JOIN PU_ArrivalVouch p ON pa.ID= p.ID
            LEFT JOIN Vendor v ON p.cVenCode= v.cVenCode
        WHERE
            pa.bInspect=1
            and
            datediff(
            MONTH,
            p.dDate,
            getdate()) = 0
            AND v.cMemo like '%合格供方%'
        GROUP BY
            p.cVenCode
            <!--,v.cVenName-->
    </select>

    <select id="getTodayArriveSum" resultType="java.lang.Integer">
        SELECT
            count(pa.Autoid)
        FROM
        PU_ArrivalVouchs pa
        LEFT JOIN PU_ArrivalVouch p ON pa.ID= p.ID
        left join Inventory d on pa.cInvCode = d.cInvCode
        WHERE
        DateDiff(
        dd,
        p.dDate,
        getdate()) =0
        and d.bPropertyCheck=1
    </select>

    <select id="getWillArriveSum" resultType="java.lang.Integer">
       <!-- SELECT
        count(pa.Autoid)
        FROM
        PU_ArrivalVouchs pa
        LEFT JOIN PU_ArrivalVouch p ON pa.ID= p.ID
        WHERE
        p.dDate <![CDATA[ >= ]]> getdate()
        AND p.dDate <![CDATA[ <= ]]> dateadd(
        DAY,
        3,
        getdate()) -->
        SELECT
        count(a.ID)
        FROM
        PO_Podetails a
        LEFT JOIN Inventory d ON d.cInvCode = a.cInvCode
        WHERE
        a.iQuantity - isnull( a.iReceivedQTY, 0 ) > 0
        AND a.dArriveDate <![CDATA[ >= ]]>getdate()
        AND a.dArriveDate <![CDATA[ <= ]]> dateadd(
        DAY,
        3,
        getdate())
        AND d.bPropertyCheck=1
    </select>

    <select id="getThisMonthArriveSum" resultType="java.lang.Integer">
        SELECT
                count(pa.Autoid)
        FROM
        PU_ArrivalVouchs pa
        LEFT JOIN PU_ArrivalVouch p ON pa.ID= p.ID
        left join Inventory d on pa.cInvCode = d.cInvCode
        WHERE
            datediff(
            MONTH,
            p.dDate,
            getdate()) =0
            AND d.bPropertyCheck=1
    </select>

    <select id="getNotInSum" resultType="java.lang.Integer">
        SELECT
        count(pa.Autoid)
        FROM
        PU_ArrivalVouchs pa
        LEFT JOIN PU_ArrivalVouch p ON pa.ID= p.ID
        left join Inventory d on pa.cInvCode = d.cInvCode
        WHERE
        (pa.fRealQuantity - pa.fValidInQuan) &gt; 0
        AND d.bPropertyCheck=1
        <!--(pa.iQuantity-pa.fRealQuantity)>0 -->
	    <!--and
        datediff(
        MONTH,
        p.dDate,
        getdate()) =0-->

    </select>

    <select id="getQualityUnpassList" resultType="java.util.HashMap">
        <!--SELECT
            pp.cPOID cPOID,
            iArrQTY iArrQTY,
            qq.CPOCODE cpocode,
            CONVERT ( VARCHAR ( 30 ), qq.DDATE, 23 ) ddate,
            qq.CVENCODE cvencode,
            qq.CINVCODE cinvcode,
            qq.cInvName cinvName,
            qq.FCHKQUANTITY fchkquantity,
            qq.FCHKINVALIDQTY fchkinvalidqty
        FROM
            (
        SELECT
            pp.cPOID,
            sum( iArrQTY ) iArrQTY
        FROM
            PO_Pomain pp
            LEFT JOIN PO_Podetails pps ON pp.POID = pps.POID
        GROUP BY
            pp.cPOID
            ) pp
            LEFT JOIN (
        SELECT
            q.CPOCODE,
            q.DDATE,
            q.CVENCODE,
            q.CINVCODE,
            i.cInvName,
            sum( qs.FCHKQUANTITY ) FCHKQUANTITY,
            sum( qs.FCHKINVALIDQTY ) FCHKINVALIDQTY
        FROM
            QMCHECKVOUCHER q
            LEFT JOIN QMCHECKVOUCHERS qs ON qs.ID = q.ID
            LEFT JOIN Inventory i ON q.CINVCODE = i.cInvCode
        GROUP BY
            q.CPOCODE,
            q.DDATE,
            q.CVENCODE,
            q.CINVCODE,
            i.cInvName
            ) qq ON pp.cPOID = qq.CPOCODE-->


        <!--SELECT CONVERT
        ( VARCHAR ( 30 ), q.DDATE, 23 ) ddate,
        q.CVENCODE cvencode,
        q.CINVCODE cinvcode,
        i.cInvName cinvName,
        sum( q.FDTQUANTITY ) fdtquantity,
        sum( q.FDISQUANTITY ) fdisquantity,
        sum( q.FQUANTITY ) fquantity
        FROM
        QMCHECKVOUCHER q
        LEFT JOIN QMCHECKVOUCHERS qs ON qs.ID = q.ID
        LEFT JOIN Inventory i ON q.CINVCODE = i.cInvCode
        GROUP BY
        CONVERT ( VARCHAR ( 30 ), q.DDATE, 23 ),
        q.CVENCODE,
        q.CINVCODE,
        i.cInvName-->

        SELECT CONVERT
        ( VARCHAR ( 30 ), q.DDATE, 23 ) ddate,
        q.CVENCODE cvencode,
        q.CINVCODE cinvcode,
        i.cInvName cinvName,
        q.FDTQUANTITY fdtquantity,
        q.FDISBREAKQUANTITY fdisquantity,
        q.FQUANTITY fquantity,
        Q.CCHECKCODE as ccheckcode,
        q.CINSPECTCODE as cinspectcode
        FROM
        QMCHECKVOUCHER q
        LEFT JOIN Inventory i ON q.CINVCODE = i.cInvCode

        where datediff(m,CONVERT ( VARCHAR ( 30 ), q.DDATE, 23 ),getdate()) <![CDATA[< ]]>3
        and q.FDISQUANTITY is not null
        AND q.FDISBREAKQUANTITY >0
        ORDER BY q.DDATE desc

            </select>

</mapper>