<?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>

    <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>


    <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 '供应商编码',c.cVenName vendorName,a.cInvCode materialCode,d.cInvName materialName,d.cInvStd specs,
        a.fValidQuantity - a.fValidInQuan 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.fValidQuantity - a.fValidInQuan) &gt; 0
        order by b.dportdate 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,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
            top 10
            v.cVenName,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
        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
        WHERE
        DateDiff(
        dd,
        p.dDate,
        getdate()) =0
    </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(ID)
        FROM
        PO_Podetails
        WHERE
        iQuantity - isnull( iReceivedQTY, 0 ) > 0
        AND dArriveDate <![CDATA[ >= ]]>getdate()
        AND dArriveDate <![CDATA[ <= ]]> dateadd(
        DAY,
        3,
        getdate())
    </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
        WHERE
            datediff(
            MONTH,
            p.dDate,
            getdate()) =0
    </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
        WHERE
        (pa.iQuantity-pa.fRealQuantity)>0
	    and
        datediff(
        MONTH,
        p.dDate,
        getdate()) =0

    </select>



</mapper>