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

    <select id="selectSopDynamicExist" resultType="java.lang.Integer">
        SELECT count(1) FROM information_schema.tables WHERE table_schema = #{db} AND table_name = #{tableName}
    </select>

    <insert id="saveDynamicSop">
        INSERT INTO ${tableName}
        (${fieldName})
        VALUES(${fieldValue})
    </insert>

    <update id="updateDynamicSop">
        update ${tableName}
        set ${fieldValue}
        <where> 1 = 1
            <if test="flowDeploymentId != null and flowDeploymentId != ''">
                and flow_deployment_id = #{flowDeploymentId}
            </if>
            <if test="flowId != null and flowId != ''">
                and flow_id = #{flowId}
            </if>
        </where>
    </update>

    <select id="selectDynamicSop" resultType="java.lang.Integer">
        select count(1) from ${tableName}
        <where> 1 = 1
            <if test="flowDeploymentId != null and flowDeploymentId != ''">
                and flow_deployment_id = #{flowDeploymentId}
            </if>
            <if test="flowId != null and flowId != ''">
                and flow_id = #{flowId}
            </if>
        </where>
    </select>

    <select id="selectDynamicSopAll" resultType="java.util.Map">
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = #{dbName} and TABLE_NAME = #{tableName}
    </select>

    <select id="list" resultType="java.util.Map">
        select distinct cast(tbsi.id as char) as id,
                <if test="defaultFieldName != null and defaultFieldName != ''">
                    ${defaultFieldName},
                </if>
               cast(tbs.id as char) as serviceId,
               tbsi.update_time as updateTime,
               cast(su1.id as char) as customManagerId,
               IF(sc.type = 'OFFICE','教务处','研究生') as customManagerTypeStr,
               cast(tbp.id as char) as productId,
               cast(tfcfe.flow_id as char) as flowId,
               cast(tfcfe.create_id as char) as flowCreateId,
               tffa.update_time as flowUpdateTime,
               CASE
                WHEN tffa.status = 'FINISH' THEN '已完成'
                WHEN tffa.status = 'END' THEN '已作废'
                ELSE '进行中'
               END as statusStr,
               tfcf.type,
               IF(tfcf.type = 'OFFICE_SOP_FLOW','教务处SOP','研究生SOP') as typeStr,
               tfcf.version,
               cast(tfcf.flow_deployment_id as char) as flowDeploymentId,
               art.TASK_DEF_KEY_ as taskDefKey,
               cast(tbcd.id as char) as crmDetailId,
               if((select count(1) from t_b_ding tbd where tbd.sop_no = tbsi.sop_no and tbd.ding_exception = 1) > 0,true,false) as dingException
                <if test="fieldName != null and fieldName != ''">
                    ,${fieldName}
                </if>
                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_b_product tbp on tbp.id = tbc.product_id
                 left join t_f_custom_flow_entity tfcfe on tfcfe.code = tbsi.sop_no
                 left join sys_user su2 on su2.id = tfcfe.create_id
                 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 act_ru_identitylink ari on ari.TASK_ID_ = art.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
                 <if test="tableName != null and tableName != ''">
                     left join ${tableName} t on t.flow_deployment_id = tfcf.flow_deployment_id and t.code = tfcfe.code
                 </if>
        <where> 1 = 1
            <if test="type != null and type != ''">
                and tfcf.type = #{type}
            </if>
            <if test="serviceId != null and serviceId != ''">
                and tbs.id = #{serviceId}
            </if>
            <if test="fieldValue != null and fieldValue != ''">
                ${fieldValue}
            </if>
            <if test="leadId != null and leadId != ''">
                and tbc.lead_id = #{leadId}
            </if>
            <if test="crmUserId != null and crmUserId != ''">
                and tbc.crm_user_id = #{crmUserId}
            </if>
            <if test="customName != null and customName != ''">
                and sc.name like concat('%', #{customName} , '%')
            </if>
            <if test="crmName != null and crmName != ''">
                and tbc.name like concat('%', #{crmName} , '%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                and tbsi.sop_no like concat('%', #{sopNo} , '%')
            </if>
            <if test="dingPlan != null and dingPlan != ''">
                <choose>
                    <when test="dingPlan == 'false'">
                        and (select count(1) from t_b_ding tbd where tbd.sop_no = tbsi.sop_no and tbd.ding_exception = 1) > 0
                    </when>
                    <otherwise>
                        and (select count(1) from t_b_ding tbd where tbd.sop_no = tbsi.sop_no and tbd.ding_exception = 0) <![CDATA[ > ]]> 0
                    </otherwise>
                </choose>
            </if>
            <if test="taskKey != null and taskKey != ''">
                <choose>
                    <when test="taskKey == '已完成'">
                        and tffa.status IN ('FINISH')
                    </when>
                    <otherwise>
                        and art.NAME_ = #{taskKey}
                    </otherwise>
                </choose>
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasThirdPmo">
                        AND EXISTS (
                        SELECT
                        1
                        FROM
                        t_b_user_archives_allocation tbuaa
                        LEFT JOIN t_b_user_archives_supplier tbuas ON tbuaa.archives_id = tbuas.user_archives_id
                        WHERE
                        tbuas.supplier_id = #{dpr.supplierId} AND tbuaa.crm_no = tbsi.crm_no)
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasBusiness">
                                AND (tbs.service_lead_id = #{dpr.requestUserId} or EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbuaa.crm_detail_id = tbcd.id AND tbuaa.user_id = #{dpr.requestUserId}))
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionManager">
                                        AND (tbc.lead_id = #{dpr.requestUserId} or EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbuaa.crm_detail_id = tbcd.id AND tbuaa.user_id = #{dpr.requestUserId}))
                                    </when>
                                    <otherwise>
                                        <choose>
                                            <when test="dpr.hasAccountManager">
                                                AND (tbc.crm_user_id = #{dpr.requestUserId} or EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbuaa.crm_detail_id = tbcd.id AND tbuaa.user_id = #{dpr.requestUserId}))
                                            </when>
                                            <otherwise>
                                                AND EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbuaa.crm_detail_id = tbcd.id AND tbuaa.user_id = #{dpr.requestUserId})
                                            </otherwise>
                                        </choose>
                                    </otherwise>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
                and IF(tbs.`status` = 'PUBLISH', tffa.status in ('START', 'DRAFT', 'AUDITING', 'REJECT', 'CANCEL', 'FINISH'), 1 <![CDATA[ <> ]]> 1)
         </where>
         <choose>
             <when test="fieldOrder != null and fieldOrder != ''">
                 order by ${fieldOrder}
             </when>
             <otherwise>
                 ${defaultFieldOrder}
             </otherwise>
         </choose>
    </select>

    <select id="findFlowPassageByCrmNo" resultType="java.lang.String">
        select distinct t.sop_no from
        (
            select
                tbsi.sop_no,
                tbsi.status,
                DATEDIFF(DATE_FORMAT(FROM_UNIXTIME(tffa.update_time / 1000), '%Y-%m-%d'), CURDATE()) as datediff
            from
                t_b_sop_info tbsi
            join t_f_custom_flow_entity tfcfe on tfcfe.code = tbsi.sop_no
            join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        <where> 1 = 1
            <if test="crmNo != null and crmNo != ''">
                and tbsi.crm_no = #{crmNo}
            </if>
        </where>) t
        join t_f_custom_flow_entity tfcfe on tfcfe.code = t.sop_no
        join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        join t_b_crm_detail tbcd on tbcd.sop_no = tfcfe.code
        join t_b_user_archives_allocation tbuaa on tbuaa.crm_detail_id = tbcd.id
        <where> 1 = 1
            <if test="userId != null and userId != ''">
                and tbuaa.user_id = #{userId}
            </if>
                and tbuaa.sop_role_type in ('ENGINEER','PROJECT_MANAGER') and (tffa.status in ('START', 'DRAFT', 'AUDITING', 'REJECT', 'CANCEL') or (tffa.status = 'FINISH' and (t.datediff <![CDATA[ >= ]]> -1 and t.datediff <![CDATA[ <= ]]> 0)))
        </where>
   </select>

    <select id="findFlowByServiceId" resultType="com.qmth.sop.business.bean.result.SopInfoResult">
        select t.* from(SELECT
        distinct tbsi.id,
        tbsi.sop_no as sopNo,
        tbsi.crm_no as crmNo,
        tbc.name as crmName,
        sc.name as customName,
        sc.type as customType,
        sc.id as customId,
        tbsi.service_id as serviceId,
        tbcd.course_name as courseName,
        tbs.name as serviceName
        FROM  t_b_sop_info tbsi
        left join t_b_crm tbc on tbc.crm_no = tbsi.crm_no
        left join sys_custom sc on sc.id = tbsi.custom_id
        left join t_b_service tbs on tbs.id = tbsi.service_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 t_b_crm_detail tbcd on tbsi.sop_no = tbcd.sop_no
        left join t_b_user_archives_allocation tbuaa on tbuaa.crm_detail_id = tbcd.id
        <where>
            <if test="serviceId != null and serviceId != ''">
               and tbuaa.service_id = #{serviceId}
            </if>
            <if test="userId != null and userId != ''">
                and tbuaa.user_id = #{userId}
            </if>
            and tbuaa.sop_role_type in ('ENGINEER','PROJECT_MANAGER','REGION_COORDINATOR')
            and tffa.status not in('END')
        </where>
        union
        SELECT  tbsi.id,
        tbsi.sop_no as sopNo,
        tbsi.crm_no as crmNo,
        tbc.name as crmName,
        sc.name as customName,
        sc.type as customType,
        sc.id as customId,
        tbsi.service_id as serviceId,
        tbcd.course_name as courseName,
        tbs.name as serviceName
        from t_b_sop_info tbsi
        left join t_b_crm tbc on tbc.crm_no = tbsi.crm_no
        left join sys_custom sc on sc.id = tbsi.custom_id
        left join t_b_service tbs on tbs.id = tbsi.service_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 t_b_crm_detail tbcd on tbsi.sop_no = tbcd.sop_no
        left join t_b_user_archives_allocation_log tbuaal on tbuaal.crm_detail_id = tbcd.id
        WHERE exists (
        SELECT tbuaa.crm_detail_id FROM t_b_user_archives_allocation tbuaa
        left join t_b_crm_detail tbcd on tbcd.id = tbuaa.crm_detail_id
        left join t_f_custom_flow_entity tfcfe on tfcfe.code = tbcd.sop_no
        left join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        <where>
            <if test="serviceId != null and serviceId != ''">
                and tbuaa.service_id = #{serviceId}
            </if>
            <if test="userId != null and userId != ''">
                and tbuaa.user_id = #{userId}
            </if>
            and tbuaa.sop_role_type in ('ENGINEER','PROJECT_MANAGER','REGION_COORDINATOR')
            and tffa.status not in('END')
            and tbuaal.crm_detail_id = tbuaa.crm_detail_id
        </where>
        )) t order by t.sopNo
    </select>

    <select id="userList" resultType="com.qmth.sop.business.entity.SysUser">
        SELECT * FROM sys_user  WHERE
                id IN (
                select tbc.lead_id from t_b_sop_info tbsi join t_b_crm tbc on tbc.crm_no = tbsi.crm_no where tbsi.id = #{id}
                UNION
                select tbuaa.user_id  from t_b_sop_info tbsi
                                               join t_b_crm_detail tbcd on tbcd.sop_no = tbsi.sop_no
                                               join t_b_user_archives_allocation tbuaa on tbuaa.crm_detail_id = tbcd.id
                where tbsi.id = #{id} and tbuaa.sop_role_type in ('REGION_COORDINATOR','ENGINEER','PROJECT_MANAGER')
            )
    </select>

    <select id="pageProjectMonitor" resultType="com.qmth.sop.business.bean.result.ProjectMonitorResult">
        SELECT DISTINCT
            sc.NAME AS customName,
            tbc.NAME AS crmName,
            tbcd.course_name AS courseName,
            IFNULL( art.NAME_, '已完成' ) AS taskName,
            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,
            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
        <where>
            <if test="serviceUnitId != null">
                AND tbsi.service_id=#{serviceUnitId}
            </if>
            <if test="customerId != null">
                AND tbsi.custom_id=#{customerId}
            </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>

            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo and !dpr.hasQA">
                <choose>
                    <when test="dpr.hasThirdPmo">
                        AND EXISTS (
                        SELECT
                        1
                        FROM
                        t_b_user_archives_allocation tbuaa
                        LEFT JOIN t_b_user_archives_supplier tbuas ON tbuaa.archives_id = tbuas.user_archives_id
                        WHERE
                        tbuas.supplier_id = #{dpr.supplierId} AND tbuaa.crm_no = tbsi.crm_no)
                    </when>
                    <otherwise>
                        <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.hasProjectManager">
                                                AND EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbuaa.crm_detail_id = tbcd.id AND tbuaa.user_id = #{dpr.requestUserId} and tbuaa.sop_role_type = 'PROJECT_MANAGER')
                                            </when>
                                            <otherwise>
                                                <choose>
                                                    <when test="dpr.hasRegionCoordinator">
                                                        AND EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbuaa.crm_detail_id = tbcd.id AND tbuaa.user_id = #{dpr.requestUserId} and tbuaa.sop_role_type = 'REGION_COORDINATOR')
                                                    </when>
                                                    <otherwise>
                                                        <choose>
                                                            <when test="!dpr.hasAccountManager and (dpr.hasEffectEngineer or dpr.hasAssistantEngineer)">
                                                                AND EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbuaa.crm_detail_id = tbcd.id AND tbuaa.user_id = #{dpr.requestUserId} and tbuaa.sop_role_type = 'ENGINEER')
                                                            </when>
                                                        </choose>
                                                        <choose>
                                                            <when test="dpr.hasAccountManager">
                                                                AND tbc.crm_user_id = #{dpr.requestUserId}
                                                            </when>
                                                        </choose>
                                                    </otherwise>
                                                </choose>
                                            </otherwise>
                                        </choose>
                                    </otherwise>
                                </choose>
                            </otherwise>
                        </choose>
                    </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
    </select>
</mapper>
