<?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.yiqi.order.mapper.OrderClothItemMapper">

    <resultMap type="OrderClothItem" id="OrderClothItemResult">
        <result property="id" column="id"/>
        <result property="orderId" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="washCode" column="wash_code"/>
        <result property="appUserId" column="app_user_id"/>
        <result property="orgId" column="org_id"/>
        <result property="sourceType" column="source_type"/>
        <result property="factoryId" column="factory_id"/>
        <result property="flowStatus" column="flow_status"/>
        <result property="clothItemId" column="cloth_item_id"/>
        <result property="clothItemName" column="cloth_item_name"/>
        <result property="clothBrandId" column="cloth_brand_id"/>
        <result property="clothBrandName" column="cloth_brand_name"/>
        <result property="clothSpeedId" column="cloth_speed_id"/>
        <result property="clothSpeedName" column="cloth_speed_name"/>
        <result property="clothMultiple" column="cloth_multiple"/>
        <result property="clothWashDayNum" column="cloth_wash_day_num"/>
        <result property="price" column="price"/>
        <result property="specialPrice" column="special_price"/>
        <result property="totalPrice" column="total_price"/>
        <result property="clothWashModeId" column="cloth_wash_mode_id"/>
        <result property="clothWashModeName" column="cloth_wash_mode_name"/>
        <result property="pics" column="pics"/>
        <result property="clothProblem" column="cloth_problem"/>
        <result property="clothHangerId" column="cloth_hanger_id"/>
        <result property="clothHangerCode" column="cloth_hanger_code"/>
        <result property="isHedging" column="is_hedging"/>
        <result property="hedgingPrice" column="hedging_price"/>
        <result property="sendClothTime" column="send_cloth_time"/>
        <result property="createById" column="create_by_id"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateById" column="update_by_id"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
        <result property="isCustomPrice" column="is_custom_price"/>
    </resultMap>


    <sql id="selectOrderClothItemVo">
        select * from order_cloth_item
    </sql>

    <select id="selectOrderClothItemList" parameterType="OrderClothItem" resultMap="OrderClothItemResult">
        <include refid="selectOrderClothItemVo"/>
        <where>
            <if test="orderId != null ">
                and order_id = #{orderId}
            </if>
            <if test="orderNo != null  and orderNo != ''">
                and order_no = #{orderNo}
            </if>
            <if test="washCode != null  and washCode != ''">
                and wash_code = #{washCode}
            </if>
            <if test="appUserId != null ">
                and app_user_id = #{appUserId}
            </if>
            <if test="orgId != null ">
                and org_id = #{orgId}
            </if>
            <if test="sourceType != null  and sourceType != ''">
                and source_type = #{sourceType}
            </if>
            <if test="factoryId != null ">
                and factory_id = #{factoryId}
            </if>
            <if test="flowStatus != null  and flowStatus != ''">
                and flow_status = #{flowStatus}
            </if>
            <if test="clothItemId != null ">
                and cloth_item_id = #{clothItemId}
            </if>
            <if test="clothItemName != null  and clothItemName != ''">
                and cloth_item_name like concat('%', #{clothItemName}, '%')
            </if>
            <if test="clothBrandId != null ">
                and cloth_brand_id = #{clothBrandId}
            </if>
            <if test="clothBrandName != null  and clothBrandName != ''">
                and cloth_brand_name like concat('%', #{clothBrandName}, '%')
            </if>
            <if test="clothSpeedId != null ">
                and cloth_speed_id = #{clothSpeedId}
            </if>
            <if test="clothSpeedName != null  and clothSpeedName != ''">
                and cloth_speed_name like concat('%', #{clothSpeedName}, '%')
            </if>
            <if test="clothMultiple != null ">
                and cloth_multiple = #{clothMultiple}
            </if>
            <if test="clothWashDayNum != null ">
                and cloth_wash_day_num = #{clothWashDayNum}
            </if>
            <if test="price != null ">
                and price = #{price}
            </if>
            <if test="specialPrice != null ">
                and special_price = #{specialPrice}
            </if>
            <if test="totalPrice != null ">
                and total_price = #{totalPrice}
            </if>
            <if test="clothWashModeId != null ">
                and cloth_wash_mode_id = #{clothWashModeId}
            </if>
            <if test="clothWashModeName != null  and clothWashModeName != ''">
                and cloth_wash_mode_name like concat('%', #{clothWashModeName}, '%')
            </if>
            <if test="pics != null  and pics != ''">
                and pics = #{pics}
            </if>
            <if test="clothProblem != null  and clothProblem != ''">
                and cloth_problem = #{clothProblem}
            </if>
            <if test="clothHangerId != null ">
                and cloth_hanger_id = #{clothHangerId}
            </if>
            <if test="clothHangerCode != null  and clothHangerCode != ''">
                and cloth_hanger_code = #{clothHangerCode}
            </if>
            <if test="isHedging != null  and isHedging != ''">
                and is_hedging = #{isHedging}
            </if>
            <if test="hedgingPrice != null ">
                and hedging_price = #{hedgingPrice}
            </if>
            <if test="sendClothTime != null ">
                and send_cloth_time = #{sendClothTime}
            </if>
            <if test="createTime != null ">
                and create_time = #{createTime}
            </if>
            <if test="isCustomPrice != null  and isCustomPrice != ''">
                and is_custom_price = #{isCustomPrice}
            </if>
        </where>
    </select>

    <select id="selectOrderClothItemById" parameterType="Long"
            resultMap="OrderClothItemResult">
        <include refid="selectOrderClothItemVo"/>
        where id = #{id}
    </select>

    <insert id="insertOrderClothItem" parameterType="OrderClothItem" useGeneratedKeys="true"
            keyProperty="id">
        insert into order_cloth_item
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="orderId != null">order_id,
            </if>
            <if test="orderNo != null">order_no,
            </if>
            <if test="washCode != null">wash_code,
            </if>
            <if test="appUserId != null">app_user_id,
            </if>
            <if test="orgId != null">org_id,
            </if>
            <if test="sourceType != null">source_type,
            </if>
            <if test="factoryId != null">factory_id,
            </if>
            <if test="flowStatus != null">flow_status,
            </if>
            <if test="clothItemId != null">cloth_item_id,
            </if>
            <if test="clothItemName != null">cloth_item_name,
            </if>
            <if test="clothBrandId != null">cloth_brand_id,
            </if>
            <if test="clothBrandName != null">cloth_brand_name,
            </if>
            <if test="clothSpeedId != null">cloth_speed_id,
            </if>
            <if test="clothSpeedName != null">cloth_speed_name,
            </if>
            <if test="clothMultiple != null">cloth_multiple,
            </if>
            <if test="clothWashDayNum != null">cloth_wash_day_num,
            </if>
            <if test="price != null">price,
            </if>
            <if test="specialPrice != null">special_price,
            </if>
            <if test="totalPrice != null">total_price,
            </if>
            <if test="clothWashModeId != null">cloth_wash_mode_id,
            </if>
            <if test="clothWashModeName != null">cloth_wash_mode_name,
            </if>
            <if test="pics != null">pics,
            </if>
            <if test="clothProblem != null">cloth_problem,
            </if>
            <if test="clothHangerId != null">cloth_hanger_id,
            </if>
            <if test="clothHangerCode != null">cloth_hanger_code,
            </if>
            <if test="isHedging != null">is_hedging,
            </if>
            <if test="hedgingPrice != null">hedging_price,
            </if>
            <if test="sendClothTime != null">send_cloth_time,
            </if>
            <if test="createById != null">create_by_id,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateById != null">update_by_id,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
            <if test="remark != null">remark,
            </if>
            <if test="isCustomPrice != null">is_custom_price,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="orderId != null">#{orderId},
            </if>
            <if test="orderNo != null">#{orderNo},
            </if>
            <if test="washCode != null">#{washCode},
            </if>
            <if test="appUserId != null">#{appUserId},
            </if>
            <if test="orgId != null">#{orgId},
            </if>
            <if test="sourceType != null">#{sourceType},
            </if>
            <if test="factoryId != null">#{factoryId},
            </if>
            <if test="flowStatus != null">#{flowStatus},
            </if>
            <if test="clothItemId != null">#{clothItemId},
            </if>
            <if test="clothItemName != null">#{clothItemName},
            </if>
            <if test="clothBrandId != null">#{clothBrandId},
            </if>
            <if test="clothBrandName != null">#{clothBrandName},
            </if>
            <if test="clothSpeedId != null">#{clothSpeedId},
            </if>
            <if test="clothSpeedName != null">#{clothSpeedName},
            </if>
            <if test="clothMultiple != null">#{clothMultiple},
            </if>
            <if test="clothWashDayNum != null">#{clothWashDayNum},
            </if>
            <if test="price != null">#{price},
            </if>
            <if test="specialPrice != null">#{specialPrice},
            </if>
            <if test="totalPrice != null">#{totalPrice},
            </if>
            <if test="clothWashModeId != null">#{clothWashModeId},
            </if>
            <if test="clothWashModeName != null">#{clothWashModeName},
            </if>
            <if test="pics != null">#{pics},
            </if>
            <if test="clothProblem != null">#{clothProblem},
            </if>
            <if test="clothHangerId != null">#{clothHangerId},
            </if>
            <if test="clothHangerCode != null">#{clothHangerCode},
            </if>
            <if test="isHedging != null">#{isHedging},
            </if>
            <if test="hedgingPrice != null">#{hedgingPrice},
            </if>
            <if test="sendClothTime != null">#{sendClothTime},
            </if>
            <if test="createById != null">#{createById},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateById != null">#{updateById},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
            <if test="remark != null">#{remark},
            </if>
            <if test="isCustomPrice != null">#{isCustomPrice},
            </if>
        </trim>
    </insert>

    <update id="updateOrderClothItem" parameterType="OrderClothItem">
        update order_cloth_item
        <trim prefix="SET" suffixOverrides=",">
            <if test="orderId != null">order_id =
                #{orderId},
            </if>
            <if test="orderNo != null">order_no =
                #{orderNo},
            </if>
            <if test="washCode != null">wash_code =
                #{washCode},
            </if>
            <if test="appUserId != null">app_user_id =
                #{appUserId},
            </if>
            <if test="orgId != null">org_id =
                #{orgId},
            </if>
            <if test="sourceType != null">source_type =
                #{sourceType},
            </if>
            <if test="factoryId != null">factory_id =
                #{factoryId},
            </if>
            <if test="flowStatus != null">flow_status =
                #{flowStatus},
            </if>
            <if test="clothItemId != null">cloth_item_id =
                #{clothItemId},
            </if>
            <if test="clothItemName != null">cloth_item_name =
                #{clothItemName},
            </if>
            <if test="clothBrandId != null">cloth_brand_id =
                #{clothBrandId},
            </if>
            <if test="clothBrandName != null">cloth_brand_name =
                #{clothBrandName},
            </if>
            <if test="clothSpeedId != null">cloth_speed_id =
                #{clothSpeedId},
            </if>
            <if test="clothSpeedName != null">cloth_speed_name =
                #{clothSpeedName},
            </if>
            <if test="clothMultiple != null">cloth_multiple =
                #{clothMultiple},
            </if>
            <if test="clothWashDayNum != null">cloth_wash_day_num =
                #{clothWashDayNum},
            </if>
            <if test="price != null">price =
                #{price},
            </if>
            <if test="specialPrice != null">special_price =
                #{specialPrice},
            </if>
            <if test="totalPrice != null">total_price =
                #{totalPrice},
            </if>
            <if test="clothWashModeId != null">cloth_wash_mode_id =
                #{clothWashModeId},
            </if>
            <if test="clothWashModeName != null">cloth_wash_mode_name =
                #{clothWashModeName},
            </if>
            <if test="pics != null">pics =
                #{pics},
            </if>
            <if test="clothProblem != null">cloth_problem =
                #{clothProblem},
            </if>
            <if test="clothHangerId != null">cloth_hanger_id =
                #{clothHangerId},
            </if>
            <if test="clothHangerCode != null">cloth_hanger_code =
                #{clothHangerCode},
            </if>
            <if test="isHedging != null">is_hedging =
                #{isHedging},
            </if>
            <if test="hedgingPrice != null">hedging_price =
                #{hedgingPrice},
            </if>
            <if test="sendClothTime != null">send_cloth_time =
                #{sendClothTime},
            </if>
            <if test="createById != null">create_by_id =
                #{createById},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateById != null">update_by_id =
                #{updateById},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
            <if test="remark != null">remark =
                #{remark},
            </if>
            <if test="isCustomPrice != null">is_custom_price =
                #{isCustomPrice},
            </if>
        </trim>
        where id = #{id}
    </update>
    <update id="reFlowCloth">
        update order_cloth_item
        set flow_status = '0', repeat_count = repeat_count + 1, cloth_hanger_code = null ,cloth_hanger_id = null
        where id = #{orderClothItemId}
</update>

    <delete id="deleteOrderClothItemById" parameterType="Long">
            delete from order_cloth_item where id = #{id}
        </delete>

    <delete id="deleteOrderClothItemByIds" parameterType="String">
        delete from order_cloth_item where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="getUnFinishedOrderClothCountGroupByStatus" resultType="com.yiqi.order.domain.vo.OrderClothItemCountStatisticVO">
        select all_count.count as all_count,washing_count.count as washing_count
        ,hanger_count.count as hanger_count,timeout_count.count as timeout_count
        ,not_take_count.count as not_take_count from
        (select count(1) as count from order_cloth_item
        where org_id=#{orgId} and source_type=#{sourceType}
        and flow_status in ('0','1','2','3','4','100','101'))  as all_count,
        (select count(1) as count from order_cloth_item
        where org_id=#{orgId} and source_type=#{sourceType}
        and flow_status in ('0','1','2','3','100','101'))  as washing_count,
        (select count(1) as count from order_cloth_item
        where org_id=#{orgId} and source_type=#{sourceType}
        and flow_status='04')  as hanger_count,
        (select count(1) as count from order_cloth_item
        where org_id=#{orgId} and source_type=#{sourceType}
        and flow_status in ('0','1','2','3','4','100','101')
        and plan_send_cloth_time&lt;now()) as timeout_count,
        (select count(1) as count from order_cloth_item
        where org_id=#{orgId} and source_type=#{sourceType}
        and flow_status='4'
        and plan_send_cloth_time&lt;now()) as not_take_count
    </select>

    <select id="getTimeoutOrderClothCountGroupByStatus"
            resultType="com.yiqi.order.domain.vo.OrderClothItemTimeoutCountStatisticVO">
        select three_day_timeout_count.count as three_day_timeout_count,
        five_day_timeout_count.count as five_day_timeout_count from
        (select count(1) as count from order_cloth_item
        where org_id=#{orgId} and source_type=#{sourceType}
        and flow_status in ('0','1','2','3','4','100','101')
        and plan_send_cloth_time&lt;#{threeTimeoutDay} and plan_send_cloth_time&gt;=#{fiveTimeoutDay}) as three_day_timeout_count,
        (select count(1) as count from order_cloth_item
        where org_id=#{orgId} and source_type=#{sourceType}
        and flow_status in ('0','1','2','3','4','100','101')
        and plan_send_cloth_time&lt;#{fiveTimeoutDay}) as five_day_timeout_count
    </select>
    <select id="listFinancialStatisticsByFactoryWithToday"
            resultType="com.yiqi.order.domain.vo.FactoryFinancialClothDayVO">
        select oci.org_id as orgId, ss.name as storeName , sum(oci.total_price) as totalPrice, count(1) as countCloth ,
               sum(oci.factory_settlement_amount) as totalStatementPrice , oci.factory_settlement_rate as statementRate
        from order_cloth_item oci
        left join sys_store ss on oci.org_id = ss.id
        where  oci.factory_id = #{factoryId} and oci.flow_status != '102' and oci.source_type = '02' and oci.in_factory_time is not null
        <if test="factoryOrderFinancialDayQueryDTO.beginCreateTimeToInFactory != null and factoryOrderFinancialDayQueryDTO.beginCreateTimeToInFactory != ''"><!-- 开始时间检索 -->
            and date_format(oci.in_factory_time,'%y%m%d') &gt;= date_format(#{factoryOrderFinancialDayQueryDTO.beginCreateTimeToInFactory},'%y%m%d')
        </if>
        <if test="factoryOrderFinancialDayQueryDTO.endCreateTimeToInFactory != null and factoryOrderFinancialDayQueryDTO.endCreateTimeToInFactory != ''"><!-- 结束时间检索 -->
            and date_format(oci.in_factory_time,'%y%m%d') &lt;= date_format(#{factoryOrderFinancialDayQueryDTO.endCreateTimeToInFactory},'%y%m%d')
        </if>
        <if test="factoryOrderFinancialDayQueryDTO.orgId != null and factoryOrderFinancialDayQueryDTO.orgId != ''">
            and oci.org_id = #{factoryOrderFinancialDayQueryDTO.orgId}
        </if>
        GROUP BY oci.org_id
    </select>
    <select id="listFinancialStatisticsByFactoryOperate"
            resultType="com.yiqi.order.domain.vo.FactoryStatisticsListQueryVO">
        select oci.org_id as orgId, ss.name as storeName ,
               sum(oci.total_price) as totalPrice,
                sum(oci.factory_settlement_amount) as totalStatementPrice ,
                oci.factory_settlement_rate as statementRate,
               count(oci.wash_code) as countTotalCloth,
               count(ocif.wash_code) as countFinishCloth,
               count(ociw.wash_code) as countWashCloth,
               count(ocic.wash_code) as countCheckCloth

        from order_cloth_item oci
        left join order_cloth_item ocif on oci.wash_code = ocif.wash_code and ocif.flow_status in ('4','5')
        left join order_cloth_item ociw on oci.wash_code = ociw.wash_code and ociw.flow_status in ('0','1','100')
        left join order_cloth_item ocic on oci.wash_code = ocic.wash_code and ocic.flow_status in ('2','3')
        left join sys_store ss on oci.org_id = ss.id
        where  oci.factory_id = #{factoryId} and oci.flow_status != '102' and oci.source_type = '02'
        <if test="factoryStatisticsQueryDTO.beginCreateTime != null and factoryStatisticsQueryDTO.beginCreateTime != ''"><!-- 开始时间检索 -->
            and date_format(oci.create_time,'%y%m%d') &gt;= date_format(#{factoryStatisticsQueryDTO.beginCreateTime},'%y%m%d')
        </if>
        <if test="factoryStatisticsQueryDTO.endCreateTime != null and factoryStatisticsQueryDTO.endCreateTime != ''"><!-- 结束时间检索 -->
            and date_format(oci.create_time,'%y%m%d') &lt;= date_format(#{factoryStatisticsQueryDTO.endCreateTime},'%y%m%d')
        </if>
        <if test="factoryStatisticsQueryDTO.orgId != null and factoryStatisticsQueryDTO.orgId != ''">
            and oci.org_id = #{factoryStatisticsQueryDTO.orgId}
        </if>
        GROUP BY oci.org_id
    </select>
    <select id="listFinancialStatisticsByFactoryOperateDetail"
            resultType="com.yiqi.order.domain.vo.FactoryStatisticsDetailQueryVO">
        select oci.org_id as orgId,oci.wash_code as washCode,  ss.name as storeName ,
               oci.cloth_item_name AS clothItemName, oci.cloth_speed_name as clothSpeedName ,
               oci.flow_status as flowStatus, oci.in_factory_time as inFactoryTime,
               oci.total_price as totalPrice ,
                sum(oci.factory_settlement_amount) as totalStatementPrice ,
                oci.factory_settlement_rate as statementRate,
               GROUP_CONCAT(occ.cloth_color_name) as clothColorName
        from order_cloth_item oci
                 LEFT JOIN order_cloth_color occ on oci.order_id = occ.order_id and oci.id = occ.order_cloth_id
                 left join sys_store ss on oci.org_id = ss.id
        where oci.factory_id = #{factoryId} and oci.flow_status != '102' and oci.source_type = '02'
        <if test="factoryStatisticsDetailQueryDTO.beginCreateTime != null and factoryStatisticsDetailQueryDTO.beginCreateTime != ''"><!-- 开始时间检索 -->
            and date_format(oci.in_factory_time,'%y%m%d') &gt;= date_format(#{factoryStatisticsDetailQueryDTO.beginCreateTime},'%y%m%d')
        </if>
        <if test="factoryStatisticsDetailQueryDTO.endCreateTime != null and factoryStatisticsDetailQueryDTO.endCreateTime != ''"><!-- 结束时间检索 -->
            and date_format(oci.in_factory_time,'%y%m%d') &lt;= date_format(#{factoryStatisticsDetailQueryDTO.endCreateTime},'%y%m%d')
        </if>
        <if test="factoryStatisticsDetailQueryDTO.orgId != null and factoryStatisticsDetailQueryDTO.orgId != ''">
            and oci.org_id = #{factoryStatisticsDetailQueryDTO.orgId}
        </if>
        GROUP BY oci.wash_code
        ORDER BY oci.create_time DESC
        limit #{factoryStatisticsDetailQueryDTO.startRow},#{factoryStatisticsDetailQueryDTO.pageSize}
    </select>
    <select id="countFinancialStatisticsByFactoryOperateDetail" resultType="java.lang.Integer">
        select  count(distinct(oci.wash_code))
        from order_cloth_item oci
        LEFT JOIN order_cloth_color occ on oci.order_id = occ.order_id and oci.id = occ.order_cloth_id
        left join sys_store ss on oci.org_id = ss.id
        where oci.factory_id = #{factoryId} and oci.flow_status != '102' and oci.source_type = '02'
        <if test="factoryStatisticsDetailQueryDTO.beginCreateTime != null and factoryStatisticsDetailQueryDTO.beginCreateTime != ''"><!-- 开始时间检索 -->
            and date_format(oci.in_factory_time,'%y%m%d') &gt;= date_format(#{factoryStatisticsDetailQueryDTO.beginCreateTime},'%y%m%d')
        </if>
        <if test="factoryStatisticsDetailQueryDTO.endCreateTime != null and factoryStatisticsDetailQueryDTO.endCreateTime != ''"><!-- 结束时间检索 -->
            and date_format(oci.in_factory_time,'%y%m%d') &lt;= date_format(#{factoryStatisticsDetailQueryDTO.endCreateTime},'%y%m%d')
        </if>
        <if test="factoryStatisticsDetailQueryDTO.orgId != null and factoryStatisticsDetailQueryDTO.orgId != ''">
            and oci.org_id = #{factoryStatisticsDetailQueryDTO.orgId}
        </if>
    </select>
</mapper>