<?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.fbsDispatchLists.mapper.FbsDispatchListsMapper">
    <select id="getProjectDelivery" resultType="org.jeecg.modules.fbsDispatchLists.entity.FbsDispatchLists">
        SELECT
            SUM( ps.iQuantity ) AS iQuantity,
            cItemName
        FROM
            DispatchLists ps
            LEFT JOIN DispatchList d ON ps.DLID = d.DLID
        WHERE
            ps.cWhCode = '01'
            AND datediff( MONTH, d.dDate, getdate ( ) ) = 0
        GROUP BY
            ps.cItemName

    </select>

    <select id="getMonthSendByProject" resultType="org.jeecg.modules.fbsDispatchLists.entity.FbsDispatchLists">
       <!-- SELECT
            DispatchList.months AS months,
            count( DispatchList.cItemCode ) AS itemCount
        FROM
            (
        SELECT
            count( ds.cItemCode ) cItemCode,
            substring( d.cDLCode, 5, 2 ) months
        FROM
            DispatchLists ds
            LEFT JOIN DispatchList d ON ds.DLID = d.DLID
        WHERE
            substring( d.cDLCode, 3, 2 ) = substring( CONVERT ( VARCHAR ( 100 ), GETDATE ( ), 112 ), 3, 2 )
            AND d.cSTCode='01'
            AND ds.cItemCode <![CDATA[ <> ]]>  '0002'
            and isnull(d.cMemo,'') <![CDATA[ <> ]]> '%补发货%'
        GROUP BY
            ds.cItemCode,
            substring( d.cDLCode, 5, 2 )
            ) AS DispatchList
        GROUP BY
            DispatchList.months
        ORDER BY
            months -->
        SELECT
        rd.months AS months,
        count( rd.cItemCode ) AS itemCount
        FROM
        (
        SELECT
        count( ds.cItemCode ) cItemCode,
        substring( d.cBusCode, 5, 2 ) months
        FROM
        rdrecords32 ds
        LEFT JOIN rdrecord32 d ON ds.ID = d.ID
        WHERE
        substring( d.cBusCode, 3, 2 ) = substring( CONVERT ( VARCHAR ( 100 ), GETDATE ( ), 112 ), 3, 2 )
        AND d.cSTCode='01'
        AND ds.cItemCode <![CDATA[ <> ]]> '0002'
        and isnull(d.cMemo,'')  <![CDATA[<> ]]> '%补发货%'
        GROUP BY
        ds.cItemCode,
        substring( d.cBusCode, 5, 2 )
        ) AS rd
        GROUP BY
        rd.months
        ORDER BY
        months

    </select>
</mapper>