<?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 TOP
            5 SUM ( iQuantity ) AS iQuantity,
            cItemName
        FROM
            DispatchLists
        GROUP BY
            cItemName
    </select>

    <select id="getMonthSendByProject" resultType="org.jeecg.modules.fbsDispatchLists.entity.FbsDispatchLists">
        SELECT
            DispatchList.months as months,
            count( DispatchList.cDLCode ) as itemCount
        FROM
            (
        SELECT
            d.cDLCode,
            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 )
        GROUP BY
            d.cDLCode,
            substring( d.cDLCode, 5, 2 )
            ) AS DispatchList
        GROUP BY
            DispatchList.months

        ORDER BY months
    </select>
</mapper>