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

    <sql id="sopSql">
        SELECT DISTINCT
        sc.NAME AS customName,
        tbc.NAME AS crmName,
        tbcd.course_name AS courseName,
        IFNULL( art.NAME_, '已完成' ) AS taskName,
        tbc.lead_id AS leadId,
        su3.real_name AS leadName,
        su3.mobile_number leadMobile,
        tbsi.sop_no AS sopNo,
        tbcd.id AS crmDetailId,
        IF ( sc.type = 'OFFICE', '教务处', '研究生' ) AS customManagerTypeStr,
        cast( tfcfe.flow_id AS CHAR ) AS flowId,
        <if test="type != null and type != ''">
            tbdw.field_obj AS fieldObj,
            tbdw.id warnId,
        </if>
        tbsi.update_time AS updateTime
        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_delay_warn tbdw on tbdw.crm_no=tbc.crm_no and tbdw.sop_no=tbcd.sop_no
        <where>
            <if test="serviceUnitId != null">
                AND tbsi.service_id=#{serviceUnitId}
            </if>
            <if test="crmUserId != null">
                AND tbc.crm_user_id=#{crmUserId}
            </if>
            <if test="customName != null and customName != ''">
                AND sc.name=#{customName}
            </if>
            <if test="leadId != null">
                AND tbc.lead_id=#{leadId}
            </if>
            <if test="province != null and province != '' ">
                AND sc.province=#{province}
            </if>
            <!-- 预警字段 -->
            <if test="type != null and type != '' ">
                AND tbdw.field_obj is not null
            </if>
            <if test="fieldObj != null and fieldObj != '' ">
                AND tbdw.field_obj = #{fieldObj}
            </if>

            <if test="process != null">
                <choose>
                    <!--非已完成状态-->
                    <when test="prList != null and prList.size > 0">
                        AND art.NAME_ in
                        <foreach collection="prList" item="item" index="index" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </when>
                    <!-- 已完成状态-->
                    <otherwise>
                        AND art.NAME_ is null
                    </otherwise>
                </choose>
            </if>
            and IF(tbs.`status` = 'PUBLISH', tffa.status in ('START', 'DRAFT', 'AUDITING', 'REJECT', 'CANCEL', 'FINISH'), 1 <![CDATA[ <> ]]> 1)
        </where>
        ORDER BY tbsi.update_time DESC,tbsi.sop_no DESC
    </sql>

    <sql id="sopByUserAllocationSql">
        SELECT DISTINCT
        sc.NAME AS customName,
        tbc.NAME AS crmName,
        tbcd.course_name AS courseName,
        IFNULL( art.NAME_, '已完成' ) AS taskName,
        tbc.lead_id AS leadId,
        su3.real_name AS leadName,
        su3.mobile_number leadMobile,
        tbsi.sop_no AS sopNo,
        tbcd.id AS crmDetailId,
        IF ( sc.type = 'OFFICE', '教务处', '研究生' ) AS customManagerTypeStr,
        cast( tfcfe.flow_id AS CHAR ) AS flowId,
        <if test="type != null and type != ''">
            tbdw.field_obj AS fieldObj,
            tbdw.id warnId,
        </if>
        tbsi.update_time AS updateTime
        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
        LEFT JOIN t_b_delay_warn tbdw on tbdw.crm_no=tbc.crm_no and tbdw.sop_no=tbcd.sop_no
        <where>
            <if test="serviceUnitId != null">
                AND tbsi.service_id=#{serviceUnitId}
            </if>
            <if test="crmUserId != null">
                AND tbc.crm_user_id=#{crmUserId}
            </if>
            <if test="customName != null and customName != ''">
                AND sc.name=#{customName}
            </if>
            <if test="leadId != null">
                AND tbc.lead_id=#{leadId}
            </if>
            <if test="province != null and province != '' ">
                AND sc.province=#{province}
            </if>
            <if test="supplierId != null">
                AND ss.id = #{supplierId}
            </if>
            <if test="coordinatorId != null">
                AND tbusaa.user_id = #{coordinatorId}
            </if>
            <if test="roleType != null">
                AND tbusaa.sop_role_type = #{roleType}
            </if>
            <!-- 预警字段 -->
            <if test="type != null and type != '' ">
                AND tbdw.field_obj is not null
            </if>
            <if test="fieldObj != null and fieldObj != '' ">
                AND tbdw.field_obj = #{fieldObj}
            </if>

            <if test="process != null">
                <choose>
                    <!--非已完成状态-->
                    <when test="prList != null and prList.size > 0">
                        AND art.NAME_ in
                        <foreach collection="prList" item="item" index="index" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </when>
                    <!-- 已完成状态-->
                    <otherwise>
                        AND art.NAME_ is null
                    </otherwise>
                </choose>
            </if>
            and IF(tbs.`status` = 'PUBLISH', tffa.status in ('START', 'DRAFT', 'AUDITING', 'REJECT', 'CANCEL', 'FINISH'), 1 <![CDATA[ <> ]]> 1)
        </where>
        ORDER BY tbsi.update_time DESC,tbsi.sop_no DESC
    </sql>


    <select id="countLead" resultType="java.lang.Integer" parameterType="java.lang.Long">
        select count(distinct lead_id)
        from t_b_crm
        where service_id = #{serviceUnitId}
    </select>

    <select id="countTotalPerson" resultType="java.lang.Integer" parameterType="java.lang.Long">
        select count(distinct user_id)
        from t_b_user_archives_allocation
        where service_id = #{serviceUnitId}
    </select>

    <select id="countScanner" resultType="java.lang.Integer">
        SELECT
        count( 1 )
        FROM
        t_b_device_delivery tdd,
        t_b_crm tbc
        WHERE
        tdd.crm_no = tbc.crm_no
        AND tbc.service_id = #{serviceUnitId}
        AND tdd.delivery_type = #{out}
        <if test="deliveryStatusList != null and deliveryStatusList.size() > 0">
            AND tdd.STATUS IN
            <foreach collection="deliveryStatusList" item="item" open="(" close=")" index="index" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="listSopProgress" resultType="com.qmth.sop.business.bean.result.ServiceUnitProgressResult" parameterType="java.lang.Long">
        SELECT DISTINCT IFNULL(art.NAME_, '已完成') AS taskName,
                        tbsi.sop_no                 AS sopNo,
                        sc.province                 AS provinceName
        FROM t_b_sop_info tbsi
                 INNER JOIN t_b_crm tbc ON tbc.crm_no = tbsi.crm_no
                 INNER JOIN t_b_service tbs ON tbs.id = tbsi.service_id
                 INNER JOIN t_f_custom_flow_entity tfcfe ON tfcfe.CODE = tbsi.sop_no
                 INNER 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
                 INNER JOIN t_f_custom_flow tfcf ON tfcf.id = tfcfe.t_f_custom_flow_id
                 INNER JOIN t_b_crm_detail tbcd ON tbcd.sop_no = tbsi.sop_no
                 INNER JOIN sys_custom sc on sc.id = tbc.custom_id
        WHERE tbsi.service_id = #{serviceUnitId}
    </select>

    <select id="listProvinceCrmNum" resultType="com.qmth.sop.business.bean.result.ServiceUnitProvinceResult" parameterType="java.lang.Long">
        SELECT sc.province,
               count(1) AS provinceCrmNum
        FROM t_b_crm tbc
                 INNER JOIN sys_custom sc ON tbc.custom_id = sc.id
        WHERE tbc.service_id = #{serviceUnitId}
        GROUP BY sc.province
    </select>


    <select id="pageSop" resultType="com.qmth.sop.business.bean.result.ProjectMonitorResult">
        <include refid="sopSql"/>
    </select>

    <select id="pageSopByUserAllocation" resultType="com.qmth.sop.business.bean.result.ProjectMonitorResult">
        <include refid="sopByUserAllocationSql"/>
    </select>


    <select id="listSop" resultType="com.qmth.sop.business.bean.result.ProjectMonitorResult">
        <include refid="sopSql"/>
    </select>

    <select id="listSopByUserAllocation" resultType="com.qmth.sop.business.bean.result.ProjectMonitorResult">
        <include refid="sopByUserAllocationSql"/>
    </select>


</mapper>
