<?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.qmth.sop.business.mapper.TBDingApplyMapper">

    <select id="flowTaskUnDoneList" resultType="com.qmth.sop.business.bean.result.DingApplyUnDoneResult">
        <include refid="flowTaskUnDoneListHeader"/>
        <include refid="flowTaskUnDoneListFoot" /> order by tfcfe.code desc,tbda.create_time desc
    </select>

    <sql id="flowTaskUnDoneListHeader">
        select
            distinct tfcf.type,
                     tfcfe.code,
                     tffa.status,
                     tffa.setup,
                     tfcf.version,
                     tfcfe.obj_id as objId,
                     tfcfe.crm_no as crmNo,
                     tbda.sop_no as sopNo,
                     tbs.id as serviceId,
                     tbs.name as serviceName,
                     tbs.type as serviceType,
                     sc.id as customId,
                     sc.type as customType,
                     sc.name as customName,
                     tfcfe.create_id as createId,
                     su.real_name as createRealName,
                     tfcfe.flow_id as flowId,
                     tfcfe.flow_process_var as flowProcessVar,
                     tfcfe.t_f_custom_flow_id as customFlowId,
                     tfcf.flow_deployment_id as flowDeploymentId,
                     tfcfe.id as customFlowEntityid,
                     tbda.`type` as dingExceptionType,
                     tbd.sign_date as exceptionTime,
                     tbda.apply_time as applyTime,
                     tbda.reason,
                     tbda.attachment_paths as attachmentPaths,
                     tbda.create_time as createTime,
                     ss.name as supplierName,
                     ss.code as supplierCode,
                     tbs.service_lead_id as serviceUnitLeaderId,
                     tbc.lead_id as regionManagerId,
                     tbc.region_coordinator_id as coordinatorId,
                     IFNULL(art.NAME_,'--') as taskName,
                     art.TASK_DEF_KEY_ as taskDefKey
        from
            t_b_ding_apply tbda
                left join t_f_custom_flow_entity tfcfe on
                tfcfe.code = tbda.ding_exception_no
                left join t_f_flow_approve tffa on
                tfcfe.flow_id = tffa.flow_id
                left join t_f_custom_flow tfcf on
                tfcf.id = tfcfe.t_f_custom_flow_id
                left join t_b_crm tbc on
                tbc.service_id = tbda.service_id
                left join t_b_service tbs on
                tbs.id = tbc.service_id
                left join sys_custom sc on
                sc.id = tbc.custom_id
                left join t_b_ding tbd on
                tbd.id = tbda.ding_id
                left join sys_user su on
                tbda.create_id = su.id
                left join t_b_user_archives tbua on
                tbua.mobile_number = su.mobile_number
                left join t_b_user_archives_supplier tbuas on
                tbuas.user_archives_id = tbua.id
                left join sys_supplier ss on
                ss.id = tbuas.supplier_id
                left join ACT_RU_TASK art on art.PROC_INST_ID_ = tffa.flow_id
                left join act_ru_identitylink ari on ari.TASK_ID_ = art.ID_
    </sql>

    <sql id="flowTaskUnDoneListFoot">
        <where>
            and tfcf.type = 'DING_EXCEPTION_FLOW'
        <if test="serviceId != null and serviceId != ''">
            and tbs.id = #{serviceId}
        </if>
        <if test="name != null and name != ''">
            and su.real_name like concat('%', #{name}, '%')
        </if>
        <if test="supplierId != null and supplierId != ''">
            and ss.id = #{supplierId}
        </if>
        <if test="customName != null and customName != ''">
            and sc.name like concat('%', #{customName}, '%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and (tbda.apply_time <![CDATA[ >= ]]> #{startTime} and tbda.apply_time <![CDATA[ <= ]]> #{endTime})
        </if>
        <if test="applyStartTime != null and applyStartTime != '' and applyEndTime != null and applyEndTime != ''">
            and (tbda.create_time <![CDATA[ >= ]]> #{applyStartTime} and tbda.create_time <![CDATA[ <= ]]> #{applyEndTime})
        </if>
        <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
            <choose>
                <when test="dpr.hasBusiness">
                    AND tbs.service_lead_id = #{dpr.requestUserId}
                </when>
                <otherwise>
                    <choose>
                        <when test="dpr.hasRegionManager">
                            AND tbc.lead_id = #{dpr.requestUserId}
                        </when>
                        <otherwise>
                            <choose>
                                <when test="dpr.hasRegionCoordinator">
                                    AND tbc.region_coordinator_id = #{dpr.requestUserId}
                                </when>
                            </choose>
                        </otherwise>
                    </choose>
                </otherwise>
            </choose>
        </if>
            and tffa.status in ('START','DRAFT','AUDITING','REJECT','CANCEL')
            and tbs.status = 'PUBLISH'
        </where>
    </sql>

    <select id="flowTaskDoneList" resultType="com.qmth.sop.business.bean.result.DingApplyDoneResult">
        select
            distinct tfcf.type,
            tfcfe.code,
            tffa.status,
            tffa.setup,
            tfcf.version,
            tfcfe.obj_id as objId,
            tfcfe.crm_no as crmNo,
            tbda.sop_no as sopNo,
            tfcfe.flow_id as flowId,
            tbs.id as serviceId,
            tbs.name as serviceName,
            tbs.type as serviceType,
            sc.id as customId,
            sc.type as customType,
            sc.name as customName,
            tfcfe.create_id as createId,
            su.real_name as createRealName,
            tfcfe.flow_process_var as flowProcessVar,
            tfcfe.t_f_custom_flow_id as customFlowId,
            tfcf.flow_deployment_id as flowDeploymentId,
            tfcfe.id as customFlowEntityid,
            tbda.`type` as dingExceptionType,
            tbd.sign_date as exceptionTime,
            tbda.apply_time as applyTime,
            tbda.create_time as createTime,
            tffa.update_time as flowTime,
            tbda.reason,
            tbda.attachment_paths as attachmentPaths,
            (
            select
            CONCAT(tffl.approve_remark, ';' , tffl.create_time)
            from
            t_f_flow_log tffl
            where
            tffl.flow_id = tfcfe.flow_id
            order by
            tffl.create_time desc
            limit 1) as approveInfo,
            ss.id as supplierId,
            tbd.id as dingId,
            ss.name as supplierName,
            ss.code as supplierCode,
            tbda.approve as dingExceptionApprove,
            tbs.service_lead_id as serviceUnitLeaderId,
            tbc.lead_id as regionManagerId,
            tbc.region_coordinator_id as coordinatorId
            from
            t_b_ding_apply tbda
            left join t_f_custom_flow_entity tfcfe on
            tbda.ding_exception_no = tfcfe.code
            left join t_f_flow_approve tffa on
            tfcfe.flow_id = tffa.flow_id
            left join t_f_custom_flow tfcf on
            tfcf.id = tfcfe.t_f_custom_flow_id
            left join t_b_crm tbc on
            tbc.service_id = tbda.service_id
            left join t_b_service tbs on
            tbs.id = tbc.service_id
            left join sys_custom sc on
            sc.id = tbc.custom_id
            left join t_b_ding tbd on
            tbd.id = tbda.ding_id
            left join sys_user su on
            tbda.create_id = su.id
            left join t_b_user_archives tbua on
            tbua.mobile_number = su.mobile_number
            left join t_b_user_archives_supplier tbuas on
            tbuas.user_archives_id = tbua.id
            left join sys_supplier ss on
            ss.id = tbuas.supplier_id
        <where> 1 = 1 and tfcf.type = 'DING_EXCEPTION_FLOW'
            and tffa.status in ('AUDITING','REJECT','FINISH')
            and tbs.status = 'PUBLISH'
            <!--and aht.ASSIGNEE_ = #{dpr.requestUserId}-->
            <if test="serviceId != null and serviceId != ''">
                and tbs.id = #{serviceId}
            </if>
            <if test="name != null and name != ''">
                and su.real_name like concat('%', #{name}, '%')
            </if>
            <if test="supplierId != null and supplierId != ''">
                and ss.id = #{supplierId}
            </if>
            <if test="dingExceptionApprove != null and dingExceptionApprove != ''">
                and tbda.approve = #{dingExceptionApprove}
            </if>
            <if test="customName != null and customName != ''">
                and sc.name like concat('%', #{customName}, '%')
            </if>
            <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                and (tbda.apply_time <![CDATA[ >= ]]> #{startTime} and tbda.apply_time <![CDATA[ <= ]]> #{endTime})
            </if>
            <if test="applyStartTime != null and applyStartTime != '' and applyEndTime != null and applyEndTime != ''">
                and (tbda.create_time <![CDATA[ >= ]]> #{applyStartTime} and tbda.create_time <![CDATA[ <= ]]> #{applyEndTime})
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND tbc.lead_id = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND tbc.region_coordinator_id = #{dpr.requestUserId}
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
            order by tfcfe.code desc,tbda.create_time desc
    </select>
</mapper>
