<?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.shkj.wms.mapper.BusinStockInvMapper">

    <select id="queryList" resultType="com.shkj.wms.vo.BusinStockInvVo"
            parameterType="com.shkj.wms.bo.BusinStockInvQueryBo">
        SELECT
        s.*, a.area_code,
        a.area_name,
        m.mold_name,
        m.model,
        l.location_code,
        l.tunnel,
        m.type,
        l.location_name
        FROM
        busin_stock_inv s
        JOIN base_location l ON l.id = s.location_id
        JOIN base_area a ON s.area_id = a.id
        join base_mold m on m.id = s.mold_id

        WHERE
        s.inv_status = #{invStatus}
        <if test="invWay != null and invWay != ''">
            and s.inv_way = #{invWay}
        </if>
        <if test="invOrderNo != null and invOrderNo != ''">
            and s.inv_order_no like concat('%', #{invOrderNo},'%')
        </if>
        <if test="haveDifference != null and haveDifference == '1'">
            and s.difference_qty > 0
        </if>

        <if test="moldBarcode != null and moldBarcode != ''">
            and m.mold_no like concat('%', #{moldBarcode},'%')
        </if>

        <if test="model != null and model != ''">
            and m.model like concat('%', #{model},'%')
        </if>

        <if test="moldName != null and moldName != ''">
            and m.mold_name like concat('%', #{moldName},'%')
        </if>

        <if test="invSDate != null and invSDate != ''">
            and Date(s.create_time) >=#{invSDate}
        </if>

        <if test="invEDate != null and invEDate != ''">
            and Date(s.create_time) &lt;=#{invEDate}
        </if>

    </select>

    <select id="getBusinStockInvList" parameterType="com.shkj.wms.bo.BusinStockInvQueryBo" resultType="com.shkj.wms.vo.BusinStockInvExeVo">

        select  inv_order_no,inv_status,inv_way,inv_type,s.create_by,s.create_time,sum(qty) moldQty ,exec_time,
        c.container_name,c.container_type,l.location_name
        from busin_stock_inv s
        inner join base_container c on s.container_id=c.id
        inner join base_location l on s.location_id=l.id
        <where>
            <if test="invWay != null and invWay != ''">
                and s.inv_way = #{invWay}
            </if>
            <if test="invOrderNo != null and invOrderNo != ''">
                and s.inv_order_no like concat('%', #{invOrderNo},'%')
            </if>

            <if test="invType != null and invType != ''">
                and s.inv_type =#{invType}
            </if>

            <if test="invStatus != null and invStatus != ''">
                and s.inv_status =#{invStatus}
            </if>

            <if test="invSDate != null and invSDate != ''">
                and Date(s.create_time) >=#{invSDate}
            </if>

            <if test="invEDate != null and invEDate != ''">
                and Date(s.create_time) &lt;=#{invEDate}
            </if>

            <if test="containerCode != null and containerCode != ''">
                and c.container_code =#{containerCode}
            </if>
        </where>

        group by inv_order_no,inv_status,inv_way,inv_type,create_by,create_time,exec_time,end_time,
        c.container_name,c.container_type,l.location_name
        order by s.create_time desc

    </select>


    <select id="queryInvMold" resultType="com.shkj.wms.vo.StockLocationGoodsAreaStorageVo"
            parameterType="com.shkj.wms.bo.BusinStockInvGoodsQueryBo">
        SELECT DISTINCT
        s.*,
        a.area_code,
        a.area_name,
        bs.storage_code,
        bs.storage_name,
        l.tunnel ,
        l.location_name,
        mold.model,
        mold.mold_name,
        mold.type
        FROM
        sys_stock_detail s
        JOIN base_location l ON l.id = s.location_id
        JOIN base_area a ON s.area_id = a.id
        JOIN base_storage bs ON s.storage_id = bs.id
        JOIN base_mold mold on s.mold_id=mold.id

        WHERE
        1 = 1
        AND s.container_id != 0
        and IFNULL(mold_id,'') !=''

        <if test="storageId != null and storageId != ''">
            and s.storage_id = #{storageId}
        </if>
        <if test="areaId != null and areaId != ''">
            AND s.area_id = #{areaId}
        </if>
        <if test="tunnel != null and tunnel != ''">
            AND l.tunnel = #{tunnel}
        </if>
        <if test="locationId != null and locationId != ''">
            AND s.location_id = #{locationId}
        </if>
        <if test="layer !=null and layer !='' ">
            and l.layer=#{layer}
        </if>
        <if test="invType != null and invType == 'DP'">
            AND EXISTS (
            SELECT
            plan.id
            FROM
            busin_out_in_plan  plan
            WHERE
            plan.location_id = s.location_id
            AND plan.mold_barcode = s.barcode
            <if test="startTime != null">
                AND plan.create_time &gt; #{startTime}
            </if>
            <if test="endTime != null">
                AND plan.create_time &lt; #{endTime}
            </if>
            limit 1)
        </if>

    </select>

    <select id="queryInvLocation" resultType="com.shkj.wms.vo.StockLocationInvVo" parameterType="com.shkj.wms.bo.BusinStockInvGoodsQueryBo">
        SELECT
            count( mold_id ) model_qty,
            a.area_code,
            a.area_name,
            l.location_name,
            c.container_name,
            c.container_type,
            c.container_code,
        max(s.in_time ) in_time,
        max(inv.inv_time) inv_time,
            s.location_id,
            s.container_id
        FROM
            sys_stock_detail s
                JOIN base_location l ON l.id = s.location_id
                JOIN base_area a ON s.area_id = a.id
                JOIN base_container c ON c.id = s.container_id
                LEFT JOIN ( SELECT location_id, max( IFNULL( end_time, exec_time )) inv_time
                            FROM busin_stock_inv WHERE inv_status = 'end'
                            group by location_id) inv ON inv.location_id = s.location_id

        <where>
            not EXISTS  ( SELECT plan.id FROM busin_out_in_plan plan WHERE plan.location_id = s.location_id
            and  plan_status not in('2','3')
            LIMIT 1 )
            and not exists (select inv.id from busin_stock_inv inv where inv.location_id=s.location_id
            and inv_status not in ('end','cancel') limit 1)
        <if test="areaId != null and areaId != ''">
            AND s.area_id = #{areaId}
        </if>
        <if test="locationId != null and locationId != ''">
            AND s.location_id = #{locationId}
        </if>
        <if test="layer !=null and layer !='' ">
            and l.layer=#{layer}
        </if>
        <if test="invType != null and invType == 'DP'">
            AND EXISTS (
            SELECT
            plan.id
            FROM
            busin_out_in_plan  plan
            WHERE
            plan.location_id = s.location_id
            <if test="startTime != null">
                AND plan.create_time &gt; #{startTime}
            </if>
            <if test="endTime != null">
                AND plan.create_time &lt; #{endTime}
            </if>
            limit 1)
        </if>
        </where>
        GROUP BY
            a.area_code,
            a.area_name,
            l.location_name,
            c.container_name,
            c.container_type,
            c.container_code,
        s.location_id,
        s.container_id
    </select>
    <select id="pdaInvByContainerCode" resultType="com.shkj.wms.vo.BusinStockInvVo"
            parameterType="com.shkj.wms.bo.PdaInvByContainerCodeBo">
        SELECT
            s.*,
            c.container_code,
            m.mold_name,
            m.model,
            m.height,
            m.width,
            m.length,
            m.type,
            m.model,
            d.qty AS actualQty,
            c.container_name
        FROM
                busin_stock_inv s
            JOIN base_container c ON c.id = s.container_id
           left join base_mold m on m.id = s.mold_id
           left join sys_stock_detail d on d.location_id = s.location_id and d.barcode=s.mold_barcode

            <where>
                <if test="invStatus !=null and invStatus != '' ">
                    s.inv_status = #{invStatus}
                </if>
                <if test="containerCode != null and containerCode != ''">
                    AND c.container_code like concat('%', #{containerCode}, '%')
                </if>

                <if test="invOrderNo != null and invOrderNo != ''">
                    AND s.inv_order_no =#{invOrderNo}
                </if>


            </where>
    </select>
    <select id="getInvById" resultType="com.shkj.wms.vo.BusinStockInvVo" parameterType="java.lang.Long">
         SELECT
                s.*, d.qty as actualQty, d.out_qty
            FROM
                busin_stock_inv s
            join sys_stock_detail d on d.location_id = s.location_id and d.barcode=s.mold_barcode
            WHERE s.id = #{id}
    </select>
</mapper>
