<?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.OmsDoBillDMapper">
    <update id="updateAttrstr3">
        UPDATE oms_do_bill_d SET attr_str3 = COALESCE(attr_str3, 0) + 1
        where lineid in
        <foreach collection="lineids" close=")" index="index" item="item" open="(" separator=",">
            #{item}
        </foreach>
    </update>
    <update id="updateAttrstr4">
        UPDATE oms_do_bill_d SET attr_str4 = COALESCE(attr_str4, 0) + 1
        where lineid in
        <foreach collection="lineids" close=")" index="index" item="item" open="(" separator=",">
            #{item}
        </foreach>
    </update>

    <select id="queryQnSalesData" resultType="com.prolog.cs.biz.outbound.model.vo.PrintQnSaleVO">
        select
        t1.locator_id as locId, -- 货位
        t1.itemname as itemName, -- 商品名称
        t1.item_specs as itemSpecs, -- 商品规格
        t3.ITEM_HEALTH_TYPE as itemHealthType, -- 剂型
        t1.pack_units as packUnits, -- 包装单位
        t1.item_qty as itemQty, -- 数量
        t1.price, -- 单价
        t1.price * t1.item_qty as actualQty, -- 实收金额
        t1.amt, -- 金额
        t1.discount_price as discountPrice, -- 折扣单价
        t1.discount_price * t1.item_qty as discountQty, -- 折扣
        t1.discount_amt as discountAmt, -- 折扣金额
        CONCAT(ifnull(t1.batch_no,''),'/',ifnull(t1.pdate_from,''),'/',ifnull(t1.valid_until,'')) as batchNo, -- 批号/生产日期/有效期至
        CONCAT(ifnull(t1.item_pcs,''),'/',IFNULL(t4.PIECE_BARCODE,''),'/',IFNULL(t4.CASE_BARCODE,'')) as itemPcs, -- 件数/最小包装/最大包装
        CONCAT(ifnull(t1.approval_number,''),'/',ifnull(t3.ITEM_HEALTH_REGISTER_NBR,''),'/',ifnull(t3.ITEM_HEALTH_RECORD_NBR,'')) as num, -- 批准文号/注册证号/备案号
        t2.SHELF_LIFE_DAYS as shelfLifeDays, -- 保质期
        CONCAT(ifnull(t1.manufacturer,''),'/',ifnull(t3.ITEM_HEALTH_LISTED_LICENSOR,'')) as manufacturer, -- 生产厂家/上市许可持有人
        t1.STOCKSTATUS as stockStatus, -- 库存状态质检状态
        t1.production_place as productionPlace, -- 产地
        CONCAT(ifnull(t3.ITEM_HEALTH_PRODUCE_LICENSE,''),'/',ifnull(t3.ITEM_HEALTH_RECORD_NBR,'')) as license, -- 生产企业许可证号/备案号
        t2.STORE_TEMPERATURE as storeTemperature -- 存储条件

        from oms_do_bill_d t1
        left join upcloud_base_item.base_mtl_item_wms t2 on t1.itemid = t2.ITEM_ID
        left join upcloud_base_item.base_mtl_item_health t3 on t1.itemid = t3.ITEM_ID
        left join upcloud_base_item.base_mtl_item_mst t4 on t1.itemid = t4.ITEM_ID
        where t1.billid = #{billId}

    </select>

    <select id="queryYmSalesData" resultType="map">
        select
        t1.itemname as itemName,-- 商品名称
        t1.item_specs as itemSpecs,-- 商品规格
        t3.ITEM_HEALTH_TYPE as itemHealthType,-- 剂型
        t1.pack_units as packUnits,-- 包装单位
        t1.item_qty as itemQty, -- 数量
        t1.price, -- 单价
        t1.amt, -- 金额
        CONCAT(ifnull(t1.batch_no,''),'/',ifnull(t1.pdate_from,''),'/',ifnull(t1.valid_until,'')) as bpv, -- 批号/生产日期/有效期至
        t7.barcode as barCode, -- 条码
        t7.checker_name as checkerName, -- 复核人
        t1.item_pcs as itemPcs, -- 件数
        t1.approval_number as approvalNumber, -- 批准文号
        CONCAT(ifnull(t1.manufacturer,''),'/',ifnull(t3.ITEM_HEALTH_LISTED_LICENSOR,'')) as mi, -- 生产厂家/上市许可持有人
        t1.STOCKSTATUS as stockStatus, -- 库存状态/质检状态
        t1.manufacturer, -- 生产厂家
        CONCAT(ifnull(t3.ITEM_HEALTH_REGISTER_NBR,''),'/',ifnull(t3.ITEM_HEALTH_RECORD_NBR,'')) as num, -- 注册证号/备案号
        CONCAT(ifnull(t3.ITEM_HEALTH_PRODUCE_LICENSE,''),'/',ifnull(t3.ITEM_HEALTH_RECORD_NBR,'')) as license, -- 生产企业许可证号/备案号
        t2.STORE_TEMPERATURE as storeTemperature, -- 存储条件
        CONCAT(ifnull(t1.batch_no,''),'/',ifnull(t1.pdate_from,'')) as bp, -- 批号/生产日期
        CONCAT(ifnull(t1.item_pcs,''),'/',IFNULL(t4.PIECE_BARCODE,''),'/',IFNULL(t4.CASE_BARCODE,'')) as pcspc, -- 件数/最小包装/最大包装
        t2.SHELF_LIFE_DAYS as shelfLifeDays, -- 保质期
        t3.ITEM_HEALTH_CATEGORY as itemCateGory -- 商品大类
        from oms_do_bill_d t1
        left join upcloud_base_item.base_mtl_item_wms t2 on t1.itemid = t2.ITEM_ID
        left join upcloud_base_item.base_mtl_item_health t3 on t1.itemid = t3.ITEM_ID
        left join upcloud_base_item.base_mtl_item_mst t4 on t1.itemid = t4.ITEM_ID
        left join wms_pick_task_d t5 on t1.lineid = t5.src_line_id
        left join wms_barcode_d t6 on t5.pick_task_d_id = t6.wms_pick_task_d_id
        left join wms_barcode_h t7 on t6.barcode_id = t7.id
        where t1.billid = #{billId}
    </select>

    <select id="queryLogisticData" resultType="map">
        select
        t1.itemname as itemName,
        t1.item_specs as itemSpecs,
        t3.ITEM_HEALTH_TYPE as itemHealthType,
        t1.pack_units as packUnits,
        t1.item_qty as itemQty,
        t1.batch_no as batchNo,
        t1.item_volume as itemVolume,
        t1.item_pcs as itemPcs,
        t1.manufacturer,
        t1.item_volume * t1.item_qty as volume,
        -- 收货情况
        t1.amt,
        t1.item_weight * t1.item_qty as weight
        from oms_do_bill_d t1
        left join upcloud_base_item.base_mtl_item_wms t2 on t1.itemid = t2.ITEM_ID
        left join upcloud_base_item.base_mtl_item_health t3 on t1.itemid = t3.ITEM_ID
        left join upcloud_base_item.base_mtl_item_mst t4 on t1.itemid = t4.ITEM_ID
        where t1.billid = #{billId}
    </select>
    <select id="findPrintAgainSY" resultType="com.prolog.cs.biz.outbound.model.SYZJPrintAgain">
        select
            d.lineid as lineId,
            d.itemid as itemId,
            d.itemname as itemName,
            d.lot_no as lotNo,
            d.billno as billNo,
            d.itemcode as itemCode,
            d.createdate as billDate,
            d.attr_str3 as count
        from oms_do_bill_d d,
             oms_do_bill_h h
        where d.billid = h.billid
        and h.attr_str3 = "1"
    </select>

    <select id="findPrintAgainZJ" resultType="com.prolog.cs.biz.outbound.model.SYZJPrintAgain">
        select
            d.lineid as lineId,
            d.itemid as itemId,
            d.itemname as itemName,
            d.lot_no as lotNo,
            d.billno as billNo,
            d.itemcode as itemCode,
            d.createdate as billDate,
            d.attr_str4 as count
        from oms_do_bill_d d,
            oms_do_bill_h h
        where d.billid = h.billid
          and h.attr_str4 = "1"
    </select>
    <select id="getPreReplenishPageList" resultType="com.prolog.cs.biz.outbound.model.dto.PreReplenishDto">
        with inBhPreStock as (
        select ITEM_ID ,LOCATOR_ID ,BATCH_ID ,sum(quantity) as inBhPreStockQty
        from upcloud_base_inventory_qingniu.wms_inv_stock_pre where IN_OUT_FLAG  = '1' and BILL_TYPE = 'REPLENISH'
        group by ITEM_ID ,LOCATOR_ID ,BATCH_ID
        ),
        outStock as (
        select ITEM_ID ,LOCATOR_ID ,BATCH_ID ,sum(quantity) as outPreStockQty
        from upcloud_base_inventory_qingniu.wms_inv_stock_pre where IN_OUT_FLAG  = '0'
        group by ITEM_ID ,LOCATOR_ID ,BATCH_ID
        ),
        lhstock as
         (
         select
            wisl.ITEM_ID as itemId,
            wisl.BATCH_ID as batchId,
            wibn.MANUFACTURER_LOT_NUM as lotNo,
            sum(wisl.quantity - wisl.on_lock_quantity- ifnull(outStock.outPreStockQty, 0) + ifnull(inBhPreStock.inBhPreStockQty, 0)) as lhStcokQty
        from
            upcloud_base_inventory_qingniu.wms_inv_stock_lot wisl
        join upcloud_base_warehouse_qingniu.base_wms_wh_locator bwwl on
            wisl.LOCATOR_ID = bwwl.WAREHOUSE_LOCATOR_ID
        join upcloud_base_inventory_qingniu.wms_inv_batch_numbers wibn on
            wibn.BATCH_ID = wisl.BATCH_ID
        left join outStock on
            wisl.ITEM_ID = outStock.ITEM_ID
            and wisl.LOCATOR_ID = outStock.LOCATOR_ID
            and wisl.BATCH_ID = outStock.BATCH_ID
        left join inBhPreStock on
            wisl.ITEM_ID = inBhPreStock.ITEM_ID
            and wisl.LOCATOR_ID = inBhPreStock.LOCATOR_ID
            and wisl.BATCH_ID = inBhPreStock.BATCH_ID
        where
            bwwl.AREA_CATEGORY = 'AGVL'
            and wisl.quantity >= 0
            and wisl.SUB_STATUS = 1
            and wisl.STATUS = 1
        group by
            wisl.ITEM_ID,
            wisl.BATCH_ID,
            wibn.MANUFACTURER_LOT_NUM
        having
            lhStcokQty > 0 )
            ,
            noBatchNoLhstock as (
            select lhstock.itemId,sum(lhstock.lhStcokQty) as noBatchNolhStcokQty from lhstock group by lhstock.itemId
            ),
            batchNoLhstock as (
            select lhstock.itemId,lhstock.batchId,lhstock.lotNo,sum(lhstock.lhStcokQty) as batchNolhStcokQty from lhstock group by lhstock.itemId,lhstock.batchId,lhstock.lotNo
            ),
        zjstock as (
            select
            wisl.ITEM_ID as itemId,
            wisl.BATCH_ID as batchId,
            wibn.MANUFACTURER_LOT_NUM as lotNo,
            sum(wisl.quantity - wisl.on_lock_quantity- ifnull(outStock.outPreStockQty,0)) as zjStcokQty
        from
            upcloud_base_inventory_qingniu.wms_inv_stock_lot wisl
        join upcloud_base_warehouse_qingniu.base_wms_wh_locator bwwl on
            wisl.LOCATOR_ID = bwwl.WAREHOUSE_LOCATOR_ID
        join upcloud_base_inventory_qingniu.wms_inv_batch_numbers wibn on
            wibn.BATCH_ID = wisl.BATCH_ID
        left join outStock on
            wisl.ITEM_ID = outStock.ITEM_ID
            and wisl.LOCATOR_ID = outStock.LOCATOR_ID
            and wisl.BATCH_ID = outStock.BATCH_ID
        where
            bwwl.AREA_CATEGORY in ('MJK', 'ZJK', 'AGVZ', 'AGVSP', 'AGVQX', 'AGVZY')
            and wisl.quantity > 0
            and wisl.SUB_STATUS = 1
            and wisl.STATUS = 1
        group by
            wisl.ID ,
            wisl.ITEM_ID,
            wisl.BATCH_ID,
            wibn.MANUFACTURER_LOT_NUM
        having
            zjStcokQty > 0
            ),

        noBatchzjStcok as (
        select zjstock.itemId,sum(zjstock.zjStcokQty) as noBatchZjStcokQty  from zjstock group by zjstock.itemId
        ),
        batchzjStcok as (
        select zjstock.itemId,zjstock.batchId,zjstock.lotNo,sum(zjstock.zjStcokQty) as batchZjStcokQty  from zjstock
        group by zjstock.itemId,zjstock.batchId,zjstock.lotNo
        ),
            bill as (
            select
                    p_enterpriseid as enterpriseId,
                    consignorid as consignorId,
                    consignorname as consignorName,
                    branchid as branchId,
                    branchcode as branchCode,
                    branchname as branchName,
                    itemid as itemId,
                    itemcode as itemCode,
                    itemname as itemName,
                    sum(item_odd) as itemOdd ,
                    type_code as typeCode,
                    type_name as typeName,
                    pack_type_num as conversionRate,
                    attr_str5 as uom,
                    lot_no as batchNo
                from
                    oms_do_bill_d odbd
                where 1=1
                    and item_odd is not null and item_odd > 0
                    and odbd.billid not in (select wwb.src_id from wms_wave_bill wwb where wwb.status in ('DISTRIBUTED','PRE_POSITION','POSITIONED','COMPLETED')
                    and wwb.src_id is not null and wwb.src_id != '')
                    and odbd.lineid not in (select src_line_id from wms_pick_task_d where src_line_id is not null and src_line_id != '')
                    and odbd.billid in (select billid from oms_do_bill_h omsbill where omsbill.IS_CANCELLED IS null and (omsbill.CON_CHANGE_STATUS is null or omsbill.CON_CHANGE_STATUS= '0') )

                group by
                    p_enterpriseid ,
                    consignorid ,
                    consignorname ,
                    branchid ,
                    branchcode ,
                    branchname ,
                    itemid ,
                    itemcode ,
                    itemname,
                    type_code ,
                    type_name ,
                    pack_type_num ,
                    attr_str5,
                    lot_no
	)
    select bill.*, batchNoLhstock.batchNolhStcokQty as lhStockQty,bill.itemOdd - ifnull(batchNoLhstock.batchNolhStcokQty,0) as shouldQty,
        ceil((bill.itemOdd - ifnull(batchNoLhstock.batchNolhStcokQty,0))/bill.conversionRate) as shouldPcs,
        ifnull(batchzjStcok.batchZjStcokQty, 0) as allowableQty,
        ifnull(batchzjStcok.batchZjStcokQty, 0) / bill.conversionRate as allowablePcs
        from bill
        left join batchNoLhstock  on bill.itemId = batchNoLhstock.itemId and bill.batchNo = batchNoLhstock.lotNo
        join batchzjStcok on bill.itemId = batchzjStcok.itemId and bill.batchNo = batchzjStcok.lotNo
        where bill.batchNo is not null and bill.batchNo != '' and bill.itemOdd >  ifnull(batchNoLhstock.batchNolhStcokQty,0)
        group by bill.itemId,bill.batchNo
        union all
        select bill.*, noBatchNoLhstock.noBatchNolhStcokQty as lhStockQty,bill.itemOdd - ifnull(noBatchNoLhstock.noBatchNolhStcokQty,0) as shouldQty,
        ceil((bill.itemOdd - ifnull(noBatchNoLhstock.noBatchNolhStcokQty,0))/bill.conversionRate) as shouldPcs,
        ifnull(noBatchzjStcok.noBatchZjStcokQty, 0) as allowableQty,
        ifnull(noBatchzjStcok.noBatchZjStcokQty, 0) / bill.conversionRate as allowablePcs
        from bill
        left join noBatchNoLhstock  on bill.itemId = noBatchNoLhstock.itemId
        join noBatchzjStcok on bill.itemId = noBatchzjStcok.itemId
        where (bill.batchNo is null or bill.batchNo = '') and bill.itemOdd >  ifnull(noBatchNoLhstock.noBatchNolhStcokQty,0)
        group by bill.itemId
    </select>

</mapper>