<?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="com.prolog.cs.biz.outbound.dao.InnerCheckHMapper">


    <select id="checkLclDetail" resultType="com.prolog.cs.biz.outbound.model.vo.check.LclCheckResultVO">
        WITH x AS (
            SELECT
            barcodeH.BILL_GROUP_ID,
            barcodeD.ITEM_ID,
            barcodeD.CONTAINER_CODE,
            sum(barcoded.qty) lcl_qty
        FROM
             WMS_BARCODE_D barcodeD
        LEFT JOIN WMS_BARCODE_H barcodeH ON barcodeH.id = barcodeD.BARCODE_ID
        WHERE
         barcodeH.BILL_GROUP_ID = #{billGroupId} and barcodeD.CONTAINER_CODE = #{containerCode}
        GROUP BY
         barcodeH.BILL_GROUP_ID,
         barcodeD.ITEM_ID,
         barcodeD.CONTAINER_CODE
        ),
        b AS (
            SELECT
                ITEM_ID,
                sum( PICK_QTY ) PICK_QTY,
                sum( CHECK_QTY ) CHECK_QTY,
                sum(coalesce(red_qty,0)) red_qty,
                BILL_GROUP_ID
            FROM
                (
                SELECT
                s.ITEM_ID,
                innercheckD.PICK_QTY PICK_QTY,
                innercheckD.CHECK_QTY CHECK_QTY,
                (select sum(coalesce(QTY,0)) red_qty from WMS_SALE_RED_RECORD r where r.BILL_ID=s.BILL_ID and r.BILL_D_ID=s.BILL_D_ID
                                                                      and r.type=4 and r.PICK_TASK_D_ID=innercheckD.PICK_TASK_D_ID) red_qty,
                h.BILL_GROUP_ID
                FROM
                wms_pick_task_d innercheckD
                LEFT JOIN wms_wave_storage s ON innercheckD.WAVE_STORAGE_ID = s.ID
                LEFT JOIN wms_pick_task_h h ON innercheckD.pick_task_id = h.pick_task_id
                WHERE h.BILL_GROUP_ID = #{billGroupId}
                and h.container_code = #{containerCode}
                ) temp
            GROUP BY
                temp.BILL_GROUP_ID,
                temp.ITEM_ID)
        SELECT
            DISTINCT
            b.ITEM_ID,
            b.pick_qty,
            (x.lcl_qty - b.pick_qty + b.red_qty) diff_qty,
            barcodeH.LCL_NO,
            x.bill_group_id
        FROM  b
        LEFT JOIN x ON x.BILL_GROUP_ID = b.bill_group_id  AND x.ITEM_ID = b.ITEM_ID
        LEFT JOIN WMS_BARCODE_H barcodeH ON barcodeH.BILL_GROUP_ID = x.bill_group_id
        WHERE
            b.pick_qty - b.red_qty != x.lcl_qty and x.container_code =  #{containerCode}
    </select>

</mapper>