<?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="cn.ezhizao.project.business.workOrder.mapper.BizWorkOrderMapper">

    <resultMap type="cn.ezhizao.project.business.workOrder.domain.BizWorkOrder" id="BizWorkOrderResult">
        <id column="id" property="id"/>
        <id column="contract_id" property="contractId"/>

        <association property="record" column="id" select="getRecord"
                     javaType="cn.ezhizao.project.business.workOrder.domain.BizWorkOrderRecord"/>

        <association property="arriveAmount" column="contract_id" select="getReceive"
                     javaType="java.math.BigDecimal"/>

        <association property="onWorkDetail" column="id" select="getOnWorkDetail"
                     javaType="java.lang.String"/>
    </resultMap>

    <resultMap id="RecordResult" type="cn.ezhizao.project.business.workOrder.domain.BizWorkOrderRecord">
        <id property="id" column="id"/>
    </resultMap>

    <select id="getRecord" resultMap="RecordResult">
        select t1.*, t2.nick_name as handlerName, t3.nick_name as recorderName, t4.create_time as entrustTime
        from biz_work_order_record t1
                 left join sys_user t2 on t1.handler_id = t2.user_id
                 left join sys_user t3 on t1.recorder_id = t3.user_id
                 left join biz_entrust t4 on t1.work_order_id = t4.work_order_id AND t4.deleted = 0
        where t1.deleted = 0
          and t1.from_id = 0
          and t1.work_order_id = #{id}
    </select>
    <select id="getReceive" resultType="java.math.BigDecimal">
        select ifnull(sum(arrive_amount),0) as arriveAmount from biz_collection
        where contract_id = #{contract_id}
        and status = 1
        group by contract_id
    </select>
    <select id="getOnWorkDetail" resultType="java.lang.String">
        select CONCAT(DATE_FORMAT(record_date,'%m.%d'),'---',record_detail) as record_detail from biz_oncework_detail where work_order_id = #{id} and deleted = 0 order by record_date desc limit 1
    </select>
    <select id="getList" parameterType="BizWorkOrder" resultMap="BizWorkOrderResult">
        SELECT CONCAT(t1.remark, t3.name, t4.name) AS contact_info,if(t7.status is null,0,t7.`status`),t1.*, t2.name as
        company_name, t2.social_credit_code,
        t3.name as task_type_name,
        t4.name as task_type_detail_name, t5.nick_name as
        service_name,t6.alter_type,t6.address_style,t6.fiction_address_id,t6.province as inputProvince,t6.city as
        inputCity,t6.district as inputDistrict,t10.account_name
        FROM biz_work_order t1
        left join biz_company t2 on t2.id = t1.company_id
        left join biz_task_type t3 on t3.id = t1.task_type_id
        left join biz_task_type_detail t4 on t4.id = t1.task_type_detail_id
        left join sys_user t5 on t5.user_id = t1.service_id
        LEFT JOIN biz_archive_input_detail t6 ON t6.id = t1.contract_detail_id
        left join biz_work_order_record t7 on t7.work_order_id = t1.id
        left join sys_user t8 on t7.handler_id = t8.user_id
        left join biz_entrust t9 on t9.work_order_id = t1.id and t9.deleted = 0
        left join biz_tenant t10 on t10.id = t9.from_tenant_id
        LEFT JOIN biz_archive_input t11 ON t11.id = t1.contract_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.deleted = 0 and
            (t11.dissolution is null or t11.dissolution = 0)
            <if test="id != null ">AND t1.id = #{id}</if>
            <if test="tenantId != null ">
                AND exists (select 1 from biz_entrust eb
                where eb.deleted = 0
                and (eb.to_tenant_id = #{tenantId} or eb.from_tenant_id = #{tenantId})
                and eb.work_order_id = t1.id
                )
            </if>
            <if test="formTenantId != null">

                and t9.from_tenant_id = #{formTenantId}
            </if>
            <if test="taskTypeName != null">
                AND (CONCAT( t3.name,"-", t4.name) LIKE concat('%', #{taskTypeName}, '%') OR t1.remark LIKE concat('%',#{taskTypeName}, '%')
                OR CONCAT(t3.name,"-",t6.alter_type,"-",t4.name) LIKE concat('%', #{taskTypeName}, '%'))
            </if>
            <if test="handlerName != null ">
                AND t8.nick_name like concat('%', #{handlerName}, '%')
            </if>
            <if test="recordStatus != null ">
                AND (IF(t7.STATUS IS NULL, 0, t7.`status`) = #{recordStatus} )
            </if>
            <if test="taskTypeId != null ">AND t1.task_type_id = #{taskTypeId}</if>
            <if test="companyId != null ">AND t1.company_id = #{companyId}</if>
            <if test="type != null ">AND t1.type = #{type}</if>
            <if test="principal != null and principal != ''">
                AND (t2.leader_id=#{principal} OR t2.adviser_id=#{principal})
            </if>
            <if test="noContract != null ">AND t1.no_contract = #{noContract}</if>
            <if test="workOrderNo != null  and workOrderNo != ''">AND t1.work_order_no = #{workOrderNo}</if>
            <if test="startMonth != null ">AND t1.start_month = #{startMonth}</if>
            <if test="endMonth != null ">AND t1.end_month = #{endMonth}</if>
            <if test="monthNum != null ">AND t1.month_num = #{monthNum}</if>
            <if test="isStop != null ">AND t1.is_stop = #{isStop}</if>
            <if test="resultStatus != null">AND t7.result_status = #{resultStatus}</if>
            <if test="entrustTimeFilter != null">
                and t7.deleted = 0
                AND (
                DATE_FORMAT(t9.create_time,'%Y-%m-%d') &gt;= #{entrustTimeFilter[0]}
                AND
                DATE_FORMAT(t9.create_time,'%Y-%m-%d') &lt;= #{entrustTimeFilter[1]}
                )
            </if>
            <if test="handlerTimeFilter != null">
                AND (
                DATE_FORMAT(t7.handler_time,'%Y-%m-%d') &gt;= #{handlerTimeFilter[0]}
                AND
                DATE_FORMAT(t7.handler_time,'%Y-%m-%d') &lt;= #{handlerTimeFilter[1]}
                )
            </if>
            <if test="finishTimeFilter != null">
                AND (
                DATE_FORMAT(t7.finish_time,'%Y-%m-%d') &gt;= #{finishTimeFilter[0]}
                AND
                DATE_FORMAT(t7.finish_time,'%Y-%m-%d') &lt;= #{finishTimeFilter[1]}
                )
            </if>
            <if test="companyName != null">AND t2.name like concat('%', #{companyName}, '%')</if>
        </trim>
    </select>

    <delete id="physicalDelete">
        DELETE FROM biz_work_order
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            <if test="id != null">
                id = #{id} AND
            </if>
            <!-- 删除条件为其他外键可以在这里加 -->
        </trim>
    </delete>
    <insert id="insertTasks">
        <foreach collection="tableValues" item="item" index="index" separator=";">
            insert into ${item.tableName} (
            id,
            company_id,
            work_order_id,
            tenant_id,
            create_time,
            creator_id
            ) select
            #{item.id},
            #{item.workOrderId},
            company_id,
            tenant_id,
            sysdate(),
            #{item.creatorId}
            from biz_work_order where id = #{item.workOrderId}
            and not exists (select 1 from ${item.tableName} where ${item.tableName}.company_id =
            biz_work_order.company_id and is_stop = 0)
        </foreach>
    </insert>

    <update id="stopTasks">
        <foreach collection="tableValues" item="item" index="index" separator=";">
            update ${item.tableName} set is_stop = 1 where work_order_id = #{item.workOrderId}
        </foreach>
    </update>

</mapper>
