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

    <select id="waveMonitorPage" resultType="com.prolog.cs.biz.outbound.model.dto.WaveMonitorPageDTO">
        select * from (
        select
        w.code waveNo,
        w.gmt_create createDate ,
        (select count(bill.id) from wms_wave_bill bill where bill.wave_id = w.id) orderWaveNum ,
        (select count(billd.id) from wms_wave_bill bill,wms_wave_bill_d billd where bill.id = billd.bill_id and bill.wave_id = w.id) numberOfRows ,
        (select count(bill.id) from wms_wave_bill bill where bill.wave_id = w.id and bill.status in ('POSITION_ERROR','WORKSTAGE_ERROR')) pendingOrders ,
        (select count(1) from wms_wave_storage t1 where t1.wave_id = w.id and t1.type='1' and w.status = 'COMPLETED') replenishmentCompleted ,
        (select count(1) from wms_wave_storage t1 where t1.wave_id = w.id and t1.type='1') replenishmentAll ,
        (select count(bill.id) from wms_wave_bill bill where bill.wave_id = w.id and bill.status = 'PICKED') pickingCompleted ,
        (select count(bill.id) from wms_wave_bill bill where bill.wave_id = w.id and bill.status = 'INNER_CHECKED') reviewPackagingCompleted ,
        (select count(bill.id) from wms_wave_bill bill where bill.wave_id = w.id and bill.status = 'OUT_CHECKED') thereView ,
        (select min(t1.gmt_create) from wms_wave_storage t1 where t1.wave_id = w.id) firstStartTime ,
        (select max(t1.gmt_create) from wms_wave_storage t1 where t1.wave_id = w.id) lastTimeout
        from wms_wave w
        where 1=1
        <if test="warehouseId != null and warehouseId !=''">
            and w.warehouse_id = #{warehouseId}
        </if>
        <if test="waveNo != null and waveNo !=''">
            and w.code = #{waveNo}
        </if>
        <if test="waveDate != null and waveDate != ''" >
            and DATE_FORMAT(w.gmt_create,'%Y-%m-%d') <![CDATA[ = ]]> #{waveDate}
        </if>
        ) t
        where t.orderWaveNum > 0 order by t.waveNo desc,t.createDate desc
    </select>
</mapper>
