<?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.CrmProgressMonitorMapper">

    <select id="listSop" resultType="com.qmth.sop.business.bean.result.CrmProgressSopResult">
        SELECT DISTINCT
        sc.NAME AS customName,
        tbc.NAME AS crmName,
        tbcd.course_name AS courseName,
        IFNULL( art.NAME_, '已完成' ) AS taskName,
        tbc.lead_id leadId,
        su3.real_name AS leadName,
        su3.mobile_number leadMobile,
        tbsi.sop_no AS sopNo,
        tbcd.id AS crmDetailId
        FROM
        t_b_sop_info tbsi
        LEFT JOIN t_b_crm tbc ON tbc.crm_no = tbsi.crm_no
        LEFT JOIN t_b_service tbs ON tbs.id = tbsi.service_id
        LEFT JOIN sys_custom sc ON sc.id = tbsi.custom_id
        LEFT JOIN sys_user su1 ON su1.id = tbc.crm_user_id
        LEFT JOIN t_f_custom_flow_entity tfcfe ON tfcfe.CODE = tbsi.sop_no
        LEFT JOIN t_f_flow_approve tffa ON tffa.flow_id = tfcfe.flow_id
        LEFT JOIN ACT_RU_TASK art ON art.PROC_INST_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_detail tbcd ON tbcd.sop_no = tbsi.sop_no
        LEFT JOIN sys_user su3 ON su3.id = tbc.lead_id

        <where>
            <if test="serviceUnitId != null">
                AND tbsi.service_id = #{serviceUnitId}
            </if>
            and IF(tbs.`status` = 'PUBLISH', tffa.status in ('START', 'DRAFT', 'AUDITING', 'REJECT', 'CANCEL', 'FINISH'), 1 <![CDATA[ <> ]]> 1)
        </where>
    </select>


    <select id="listSopByRoleType" resultType="com.qmth.sop.business.bean.result.CrmProgressSopResult">
        SELECT DISTINCT sc.NAME AS customName,
        tbc.NAME AS crmName,
        tbcd.course_name AS courseName,
        IFNULL(art.NAME_, '已完成') AS taskName,
        tbc.lead_id leadId,
        su3.real_name AS leadName,
        su3.mobile_number leadMobile,
        tbsi.sop_no AS sopNo,
        tbcd.id AS crmDetailId,
        ss.name supplierName,
        ss.id supplierId,
        tbusaa.user_id userId,
        syu.real_name realName
        FROM t_b_sop_info tbsi
        LEFT JOIN t_b_crm tbc ON tbc.crm_no = tbsi.crm_no
        LEFT JOIN t_b_service tbs ON tbs.id = tbsi.service_id
        LEFT JOIN sys_custom sc ON sc.id = tbsi.custom_id
        LEFT JOIN sys_user su1 ON su1.id = tbc.crm_user_id
        LEFT JOIN t_f_custom_flow_entity tfcfe ON tfcfe.CODE = tbsi.sop_no
        LEFT JOIN t_f_flow_approve tffa ON tffa.flow_id = tfcfe.flow_id
        LEFT JOIN ACT_RU_TASK art ON art.PROC_INST_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_detail tbcd ON tbcd.sop_no = tbsi.sop_no
        LEFT JOIN sys_user su3 ON su3.id = tbc.lead_id
        LEFT JOIN t_b_user_archives_allocation tbusaa on tbusaa.crm_detail_id = tbcd.id
        LEFT JOIN t_b_user_archives_supplier tbusas on tbusas.user_archives_id = tbusaa.archives_id
        LEFT JOIN sys_supplier ss on ss.id = tbusas.supplier_id
        LEFT JOIN sys_user syu on syu.id = tbusaa.user_id

        <where>
            <if test="serviceUnitId != null">
                AND tbsi.service_id = #{serviceUnitId}
            </if>
            <if test="sopRoleType != null">
                AND tbusaa.sop_role_type = #{sopRoleType}
            </if>
            and IF(tbs.`status` = 'PUBLISH', tffa.status in ('START', 'DRAFT', 'AUDITING', 'REJECT', 'CANCEL', 'FINISH'), 1 <![CDATA[ <> ]]> 1)
        </where>
    </select>
</mapper>
