<?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.zfire.mall.manager.commonmapper.stock.ReservedMapper">

    <select id="list" resultType="com.zfire.mall.manager.bean.reserved.ReservedBean">
        SELECT
            ro.*,
            roi.id as itemId,
            roi.reserved_id,
            roi.order_id,
            roi.main_order_id,
            roi.reserved_num,
            roi.reserved_remark,
            roi.order_type,
            roi.material_id,
            roi.status,
            roi.order_time,
            roi.customer_id,
            roi.customer_name,
            roi.customer_number,
            roi.material_code,
            roi.material_name,
            roi.specification,
            roi.unit,
            roi.qty,
            roi.create_by,
            roi.create_time,
            roi.update_time,
            roi.update_by,
            roi.closure_by,
            roi.closure_time,
            roi.material_old_number,
               roi.old_num,
        roi.correspond_name,
        roi.correspond_id,
        ROW_NUMBER() OVER (PARTITION BY ro.id  order by ro.create_time,roi.id desc) AS frequency
        FROM
        reserved_order ro
        JOIN reserved_order_item roi ON ro.id = roi.reserved_id
        join admin_user_stock aus on aus.correspond_id = roi.correspond_id
        where 1=1
        <if test="adminUserId != null and adminUserId != ''">
            and aus.admin_user_id = #{adminUserId}
        </if>
        <if test="id != null and id != ''">
            and ro.id = #{id}
        </if>
        <if test="customerName != null and customerName != ''">
            and roi.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="createBy != null and createBy != ''">
            and ro.create_by like concat('%',#{createBy},'%')
        </if>
        <if test="updateBy != null and updateBy != ''">
            and ro.update_by like concat('%',#{updateBy},'%')
        </if>
        <if test="orderId != null and orderId != ''">
            and roi.main_order_id like concat('%',#{orderId},'%')
        </if>
        <if test="specification != null and specification != ''">
            and roi.specification like concat('%',#{specification},'%')
        </if>
        <if test="startTime != null and startTime != ''">
            and ro.order_time between #{startTime} and #{endTime}
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and roi.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialCode != null and materialCode != ''">
            and roi.material_code like concat('%',#{materialCode},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and roi.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="customerId != null and customerId != ''">
            and roi.customer_id = #{customerId}
        </if>
          <if test="status != null">
            and roi.status = #{status}
        </if>
        <if test="correspondId != null and correspondId != ''">
            and roi.correspond_id = #{correspondId}
        </if>
<!--        <if test="serviceIds != null and serviceIds.size > 0">
            and roi.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and roi.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>-->
        order by ro.create_time,roi.id desc
    </select>


    <resultMap id="BaseResultMap" type="com.zfire.mall.manager.bean.reserved.ReservedDetail">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="order_time" jdbcType="TIMESTAMP" property="orderTime"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <collection property="reservedOrderItems" ofType="com.zfire.mall.manager.plus.entity.ReservedOrderItem" select="order" column="id">
            <id column="id" jdbcType="VARCHAR" property="id"/>
            <result column="reserved_id" jdbcType="VARCHAR" property="reservedId"/>
            <result column="order_id" jdbcType="VARCHAR" property="orderId"/>
            <result column="reserved_num" jdbcType="INTEGER" property="reservedNum"/>
            <result column="reserved_remark" jdbcType="VARCHAR" property="reservedRemark"/>
            <result column="material_id" jdbcType="VARCHAR" property="materialId"/>
            <result column="status" jdbcType="TINYINT" property="status"/>
            <result column="customer_id" jdbcType="VARCHAR" property="customerId"/>
            <result column="customer_name" jdbcType="VARCHAR" property="customerName"/>
            <result column="material_code" jdbcType="VARCHAR" property="materialCode"/>
            <result column="material_name" jdbcType="VARCHAR" property="materialName"/>
            <result column="specification" jdbcType="VARCHAR" property="specification"/>
            <result column="unit" jdbcType="VARCHAR" property="unit"/>
            <result column="qty" jdbcType="INTEGER" property="qty"/>
            <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
            <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
            <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
            <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
            <result column="closure_by" jdbcType="VARCHAR" property="closureBy"/>
            <result column="closure_time" jdbcType="TIMESTAMP" property="closureTime"/>
            <result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber"/>
        </collection>
    </resultMap>


    <select id="detail" resultMap="BaseResultMap">
        SELECT
        *
        FROM
        reserved_order ro
        where 1=1
        <if test="id != null and id != ''">
            and ro.id = #{id}
        </if>
    </select>


    <select id="order" resultType="com.zfire.mall.manager.plus.entity.ReservedOrderItem">
        SELECT
        *
        FROM
        reserved_order_item
        WHERE
        reserved_id = #{id}
    </select>

    <select id="listOrder" resultType="com.zfire.mall.manager.bean.reserved.ReservedOrderBean">
        select
        *
        from
        (
        SELECT
        cptr.service_id,
        eo.customer_id,
        eo.customer_name,
        eo.customer_number,
        eo.order_date as order_time,
        eo.engin_order_type as order_type,
        eo.engin_order_no as main_order_id,
        eoi.id as order_id,
        eoi.sale_type_id,
        eoi.sale_type_name,
        eoi.material_id,
        eoi.material_number as materialCode,
        eoi.material_name,
        eoi.material_old_number,
        eoi.specification,
        eoi.unit,
        eoi.qty,
        (eoi.refundable_qty - IFNULL(eoi.withhold,0) - IFNULL(sum(roi.reserved_num),0) ) as refundable_qty,
        eoi.retired_qty,
        eoi.service_name,
        eoi.k3_category_id
        FROM
        engin_order eo
        JOIN engin_order_item eoi ON eo.engin_order_id = eoi.parent_id
        join customer_product_type_rela cptr on eoi.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN reserved_order_item roi on roi.order_id = eoi.id and roi.`status` = 1
        WHERE
        eo.examine_status = 'OK'
        and ((eoi.direct_transfer_status = 1 and eoi.is_direct_transfer = 1) or eoi.is_direct_transfer = 0)
        GROUP BY eoi.id having  refundable_qty > 0
        UNION
        SELECT
        cptr.service_id,
        a.customer_id,
        a.customer_name,
        a.customer_number,
        a.the_time as order_time,
        ( CASE WHEN a.type = 1 THEN 'RETAIL' WHEN a.type = 2 THEN 'RETAIL_POLICY' END ) order_type,
        a.id AS main_order_id,
        b.id AS order_id,
        b.sale_type_id,
        b.sale_type_name,
        b.material_id,
        b.material_code,
        b.material_name,
        b.material_old_number,
        b.specification,
        b.unit,
        b.qty,
        (b.refundable_qty - IFNULL(b.withhold,0) - IFNULL(sum(roi.reserved_num),0) ) as refundable_qty,
        b.retired_qty,
        b.service_name,
        b.k3_category_id
        FROM
        retail_order a
        JOIN retail_order_item b ON a.id = b.retail_order_id
        join customer_product_type_rela cptr on b.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN reserved_order_item roi on roi.order_id = b.id and roi.`status` = 1
        WHERE
        a.examine_status = 'OK'
        and ((b.direct_transfer_status = 1 and b.is_direct_transfer = 1) or b.is_direct_transfer = 0)
        GROUP BY b.id having  refundable_qty > 0
        ) s where 1=1

        <if test="materialOldNumber != null and materialOldNumber != ''">
            and s.material_old_number like concat('%',#{materialOldNumber},'%')
        </if>
        <if test="customerName != null and customerName != ''">
            and s.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="orderId != null and orderId != ''">
            and s.main_order_id like concat('%',#{orderId},'%')
        </if>
        <if test="specification != null and specification != ''">
            and s.specification like concat('%',#{specification},'%')
        </if>

        <if test="orderType != null and orderType != '' and orderType != 'PERMU'">
            and s.order_type = #{orderType}
        </if>
        <if test="orderType != null and orderType != '' and orderType == 'PERMU'">
            and s.order_type in ('PERMU_RETAIL','PERMU_RETAIL_POLICY','PERMU_TRADE','PERMU_HOME')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and s.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="materialCode != null and materialCode != ''">
            and s.materialCode like concat('%',#{materialCode},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and s.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="customerId != null and customerId != ''">
            and s.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and s.service_id in
            <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
            and s.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by s.order_time desc
    </select>


    <select id="getAutomaticRetailOrder" resultType="com.zfire.mall.manager.plus.entity.RetailOrderItem">
        SELECT
            roi.*
        FROM
        retail_order ro
        LEFT JOIN retail_order_item roi ON ro.id = roi.retail_order_id
        WHERE
        1 = 1
        AND ro.examine_status = 'OK'
        AND (( roi.direct_transfer_status = 1 AND roi.is_direct_transfer = 1 ) OR roi.is_direct_transfer = 0 )
        AND ro.the_time &lt; #{date}
        and (SELECT count(1) FROM reserved_order_item roit where roit.order_id = roi.id) &lt; 1
        GROUP BY roi.id
    </select>


    <select id="getDisplaceOrder" resultType="com.zfire.mall.manager.plus.entity.DisplaceOrderItem">
        SELECT
        roi.*
        FROM
        displace_order ro
        LEFT JOIN displace_order_item roi ON ro.displace_order_id = roi.parent_id
        WHERE
        1 = 1
        AND ro.examine_status = 'OK'
        AND ro.order_date &lt; #{number}
        and (SELECT count(1) FROM reserved_order_item roit where roit.order_id = roi.id) &lt; 1
        GROUP BY roi.id
    </select>

    <select id="getEnginOrderItem" resultType="com.zfire.mall.manager.plus.entity.EnginOrderItem">
        SELECT
        roi.*
        FROM
        engin_order ro
        LEFT JOIN engin_order_item roi ON ro.engin_order_id = roi.parent_id
        WHERE
        1 = 1
        AND ro.examine_status = 'OK'
        AND ro.order_date &lt; #{number}
        and (SELECT count(1) FROM reserved_order_item roit where roit.order_id = roi.id) &lt; 1
        GROUP BY roi.id
    </select>

    <select id="listStock" resultType="com.zfire.mall.manager.bean.k3.StockPositionStock">
        SELECT
        sc.*
        FROM
        stock_correspond sc

        join admin_user_stock aus on sc.id = aus.correspond_id
        join admin_user au on au.admin_user_id = aus.admin_user_id
        where 1=1
        <if test="customerId != null and customerId != ''">
            and au.customer_id =#{customerId} and au.is_master = 1
        </if>

        <if test="name != null and name != ''">
            and sc.name like  CONCAT('%', #{name},'%')
        </if>
        <if test="stockName != null and stockName != ''">
            and kds.name like  CONCAT('%', #{stockName},'%')
        </if>

        group by sc.id
        order by sc.create_time desc
    </select>

    <select id="listStockPosition" resultType="com.zfire.mall.manager.bean.k3.StockPositionStock">
        SELECT
        sc.*
        FROM
        stock_correspond sc

        join admin_user_stock aus on sc.id = aus.correspond_id
        join admin_user au on au.admin_user_id = aus.admin_user_id
        where 1=1
        <if test="customerId != null and customerId != ''">
            and au.customer_id =#{customerId} and au.is_master = 1
        </if>
        group by sc.id
        order by sc.create_time desc
    </select>
    <select id="listOrderReserved" resultType="com.zfire.mall.manager.bean.reserved.ReservedOrderBean">
         select
        *
        from
        (
        SELECT
        cptr.service_id,
        eo.customer_id,
        eo.customer_name,
        eo.customer_number,
        eo.order_date as order_time,
        eo.engin_order_type as order_type,
        eo.engin_order_no as main_order_id,
        eoi.id as order_id,
        eoi.sale_type_id,
        eoi.sale_type_name,
        eoi.material_id,
        eoi.material_number as materialCode,
        eoi.material_name,
        eoi.material_old_number,
        eoi.specification,
        eoi.unit,
        eoi.qty,
        (eoi.refundable_qty - IFNULL(eoi.withhold,0) - IFNULL(sum(roi.reserved_num),0) ) as refundable_qty,
        eoi.retired_qty,
        eoi.service_name,
        eoi.k3_category_id,
        eo.admin_websit_id
        FROM
        engin_order eo
        JOIN engin_order_item eoi ON eo.engin_order_id = eoi.parent_id
        join customer_product_type_rela cptr on eoi.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN reserved_order_item roi on roi.order_id = eoi.id and roi.`status` = 1
        WHERE
        eo.examine_status = 'OK'
        and ((eoi.direct_transfer_status = 1 and eoi.is_direct_transfer = 1) or eoi.is_direct_transfer = 0)
        AND eo.order_date &lt; #{today}
        <if test="type != null and type.size > 0">
            and eo.admin_websit_id in
            <foreach item="item" index="index" collection="type" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY eoi.id HAVING refundable_qty > 0
        UNION
        SELECT
        cptr.service_id,
        a.customer_id,
        a.customer_name,
        a.customer_number,
        a.the_time as order_time,
        ( CASE WHEN a.type = 1 THEN 'RETAIL' WHEN a.type = 2 THEN 'RETAIL_POLICY' END ) order_type,
        a.id AS main_order_id,
        b.id AS order_id,
        b.sale_type_id,
        b.sale_type_name,
        b.material_id,
        b.material_code,
        b.material_name,
        b.material_old_number,
        b.specification,
        b.unit,
        b.qty,
        (b.refundable_qty - IFNULL(b.withhold,0) - IFNULL(sum(roi.reserved_num),0) ) as refundable_qty,
        b.retired_qty,
        b.service_name,
        b.k3_category_id,
        a.admin_websit_id
        FROM
        retail_order a
        JOIN retail_order_item b ON a.id = b.retail_order_id
        join customer_product_type_rela cptr on b.customer_wallet_id = cptr.customer_wallet_id
        LEFT JOIN reserved_order_item roi on roi.order_id = b.id and roi.`status` = 1
        WHERE
        a.examine_status = 'OK'
        and ((b.direct_transfer_status = 1 and b.is_direct_transfer = 1) or b.is_direct_transfer = 0)
        AND a.the_time &lt; #{today}
        <if test="type != null and type.size > 0">
            and a.admin_websit_id in
            <foreach item="item" index="index" collection="type" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY b.id HAVING refundable_qty > 0
        ) s where 1=1
    </select>
    <select id="listV2" resultType="com.zfire.mall.manager.bean.reserved.ReservedBeanVO">
        SELECT
        ro.*,
        roi.id as itemId,
        roi.reserved_id,
        roi.order_id,
        roi.main_order_id,
        roi.reserved_num,
        roi.reserved_remark,
        roi.order_type,
        roi.material_id,
        roi.status,
        roi.customer_id,
        roi.customer_name,
        roi.customer_number,
        roi.material_code,
        roi.material_name,
        roi.specification,
        roi.unit,
        roi.qty,
        roi.closure_by,
        roi.closure_time,
        roi.material_old_number,
        roi.old_num,
        roi.correspond_name,
        roi.correspond_id,
        ROW_NUMBER() OVER (PARTITION BY ro.id  order by ro.create_time,roi.id desc) AS frequency
        FROM
        reserved_order ro
        JOIN reserved_order_item roi ON ro.id = roi.reserved_id
        ${ex.query}
        <if test="customerId != null and customerId != ''">
            and roi.customer_id = #{customerId}
        </if>
        group by roi.id
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by ro.create_time desc
        </if>

        ${ex.orderBy}
    </select>
    <select id="listCountV2" resultType="java.lang.Integer">
        SELECT
        count(distinct roi.id)
        FROM
        reserved_order ro
        JOIN reserved_order_item roi ON ro.id = roi.reserved_id
        ${ex.query}
        <if test="customerId != null and customerId != ''">
            and roi.customer_id = #{customerId}
        </if>
    </select>

</mapper>