<?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.FbsArrivalVouchsMapper">

    <select id="getWillArriveList" resultType="org.jeecg.modules.fbsPuArrivalvouch.entity.FbsArrivalVouchs">


        <!--SELECT  CONVERT
            ( VARCHAR ( 30 ), a.dArriveDate, 112 ) arriveDate,
            c.cVenCode cVenCode,
            a.cInvCode materialCode,
            d.cInvName materialName,
            a.iQuantity arriveSum,
            d.cInvStd specs,
            b.cMemo memo,
            b.cDefine12,
            a.cItemName
        FROM
          PO_Podetails a
        LEFT JOIN PO_Pomain b ON a.POID = b.POID
        LEFT JOIN Vendor c ON c.cVenCode = b.cVenCode
        LEFT JOIN Inventory d ON d.cInvCode = a.cInvCode
        WHERE
            a.iQuantity - isnull( a.iReceivedQTY, 0 ) > 0
            AND dArriveDate <![CDATA[ >= ]]>getdate()
            AND dArriveDate <![CDATA[ <= ]]>dateadd(
            DAY,
            3,
            getdate())
        AND d.bPropertyCheck=1
        ORDER BY
            a.dArriveDate sql修改-->

        SELECT
        DATE_FORMAT(a.dArriveDate,'%Y%m%d') arriveDate,
        c.cVenCode cVenCode,
        a.cInvCode materialCode,
        d.cInvName materialName,
        a.iQuantity arriveSum,
        d.cInvStd specs,
        a.cItemName
        FROM
        PO_Podetails a
        LEFT JOIN PO_Pomain b ON a.POID = b.POID
        LEFT JOIN Vendor c ON c.cVenCode = b.cVenCode
        LEFT JOIN Inventory d ON d.cInvCode = a.cInvCode
        WHERE
        a.iQuantity - ifnull( a.iReceivedQTY, 0 ) <![CDATA[ > ]]>0
        AND DATEDIFF(dArriveDate,NOW())>0
        AND
        DATEDIFF(dArriveDate,NOW()) <![CDATA[ <= ]]>3

        AND d.bPropertyCheck=1
        ORDER BY
        a.dArriveDate
    </select>

   <!-- <select id="getWillArriveList" resultType="org.jeecg.modules.fbsPuArrivalvouch.entity.FbsArrivalVouchs">

       select top 5 CONVERT(varchar(100), b.dDate, 23) arriveDate,c.cVenName vendorName,a.cInvCode materialCode,d.cInvName materialName,
          a.fValidQuantity arriveSum,d.cInvStd specs,b.cMemo memo
            from PU_ArrivalVouchs a
            left join PU_ArrivalVouch b on a.id = b.id
            left join Vendor c on c.cVenCode = b.cVenCode
            left join Inventory d on d.cInvCode = a.cInvCode
           where fValidQuantity - fValidInQuan > 0
        order by b.dDate
    </select>-->

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

        <!--select  top 6 CONVERT(varchar(7), b.dDate,23) month, sum(a.fRealQuantity) target, sum(a.fRealQuantity ) fRealQuantity,
        sum(a.fValidQuantity) real,sum(a.fValidInQuan) as fValidInQuan
        from  PU_ArrivalVouchs a
        left join PU_ArrivalVouch b on a.id = b.id
        group by CONVERT(varchar(7), b.dDate,23)
        order by CONVERT(varchar(7), b.dDate,23) desc sql修改-->

        SELECT
            DATE_FORMAT(b.dDate,'%Y-%m') MONTH,
            sum(a.fRealQuantity) target,
            sum(a.fRealQuantity) fRealQuantity,
            sum(a.fValidQuantity) as `REAL`,
            sum(a.fValidInQuan) AS fValidInQuan
        FROM
            PU_ArrivalVouchs a
            LEFT JOIN PU_ArrivalVouch b ON a.id = b.id
        GROUP BY
            DATE_FORMAT(b.dDate,'%Y-%m')
        ORDER BY
            DATE_FORMAT(b.dDate,'%Y-%m') DESC
    </select>
    
    <select id="selectPercentage" resultType="int">
        select IFNULL(id,0) from aa_bank
    </select>
    
</mapper>