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

    <select id="findCrmAllocationPage"
            resultType="com.qmth.sop.business.bean.result.UserArchivesAllocationResult">
        SELECT
            *,
            (result.effectQuota + result.assistantQuota + result.coordinatorQuota) AS quota,
            (result.coordinatorUnDistributed + result.effectUnDistributed + result.assistantUnDistributed) AS unDistributed
        FROM
            (SELECT
                 tbs.id AS serviceUnitId,
                 tbs.name AS serviceUnitName,
                 tbc.id AS crmId,
                 tbc.crm_no AS crmNo,
                 su.id AS customId,
                 su.name AS customName,
                 su.type AS customType,
                 su.province AS province,
                 su.city AS city,
                 su.area AS area,
                 sl.id AS levelId,
                 sl.level AS level,
                 1 AS coordinatorQuota,
                 IFNULL(effectQ.quota, 0) AS effectQuota,
                 IFNULL(assistantQ.quota, 0) AS assistantQuota,
                 IF(tbc.region_coordinator_id IS NULL, 0, 1) AS coordinatorDistributed,
                 IFNULL(effectR.reality, 0) AS effectDistributed,
                 IFNULL(assistantR.reality, 0) AS assistantDistributed,
                 IF(tbc.region_coordinator_id IS NULL, 0, 1) + IFNULL(effectR.reality, 0) + IFNULL(assistantR.reality, 0) AS distributed,
                 IF(tbc.region_coordinator_id IS NULL, 1, 0) AS coordinatorUnDistributed,
                 IF(IFNULL(effectQ.quota, 0) - IFNULL(effectR.reality, 0) &lt; 0, 0, IFNULL(effectQ.quota, 0) - IFNULL(effectR.reality, 0)) AS effectUnDistributed,
                 IF(IFNULL(assistantQ.quota, 0) - IFNULL(assistantR.reality, 0) &lt; 0, 0, IFNULL(assistantQ.quota, 0) - IFNULL(assistantR.reality, 0)) AS assistantUnDistributed,
                 tbc.status AS crmStatus,
                 tbs.service_lead_id AS serviceUnitLeaderId,
                 tbc.region_id AS regionId,
                 tbc.lead_id AS regionManagerId,
                 rm.real_name AS regionManagerName,
                 pu.real_name AS publishUserName,
                 tbc.publish_time AS publishTime,
                 IF(tbc.lead_id IS NOT NULL AND tbc.lead_id = tbc.region_coordinator_id, 1, 0) AS concurrently
             FROM
                 t_b_crm tbc
                     LEFT JOIN t_b_service tbs ON tbc.service_id = tbs.id
                     LEFT JOIN sys_custom su ON tbc.custom_id = su.id
                     LEFT JOIN sys_level sl ON su.level_id = sl.id
                     LEFT JOIN sys_user pu ON tbc.publish_user_id = pu.id
                     LEFT JOIN sys_user rm ON tbc.lead_id = rm.id
                     LEFT JOIN (SELECT
                                    slr.quota, slr.level_id
                                FROM
                                    sys_level_role slr
                                        LEFT JOIN sys_role sr ON sr.id = slr.role_id
                                WHERE
                                    sr.type = 'EFFECT_ENGINEER') effectQ ON effectQ.level_id = sl.id
                     LEFT JOIN (SELECT
                                    slr.quota, slr.level_id
                                FROM
                                    sys_level_role slr
                                        LEFT JOIN sys_role sr ON sr.id = slr.role_id
                                WHERE
                                    sr.type = 'ASSISTANT_ENGINEER') assistantQ ON assistantQ.level_id = sl.id
                     LEFT JOIN (SELECT
                                    tbuaa.crm_no, COUNT(*) AS reality
                                FROM
                                    t_b_user_archives_allocation tbuaa
                                        LEFT JOIN sys_role sr ON sr.id = tbuaa.role_id
                                WHERE
                                    sr.type = 'EFFECT_ENGINEER'
                                GROUP BY tbuaa.crm_no) effectR ON effectR.crm_no = tbc.crm_no
                     LEFT JOIN (SELECT
                                    tbuaa.crm_no, COUNT(*) AS reality
                                FROM
                                    t_b_user_archives_allocation tbuaa
                                        LEFT JOIN sys_role sr ON sr.id = tbuaa.role_id
                                WHERE
                                    sr.type = 'ASSISTANT_ENGINEER'
                                GROUP BY tbuaa.crm_no) assistantR ON assistantR.crm_no = tbc.crm_no
            WHERE tbs.status = 'PUBLISH') result
        <where>
            <if test="serviceUnitId != null">
                AND result.serviceUnitId = #{serviceUnitId}
            </if>
            <if test="province != null and province != ''">
                AND result.province LIKE CONCAT('%',#{province},'%')
            </if>
            <if test="city != null and city != ''">
                AND result.city LIKE CONCAT('%',#{city},'%')
            </if>
            <if test="area != null and area != ''">
                AND result.area LIKE CONCAT('%',#{area},'%')
            </if>
            <if test="customName != null and customName != ''">
                AND result.customName LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="gap != null">
                AND (result.coordinatorUnDistributed + result.effectUnDistributed + result.assistantUnDistributed) &lt;= #{gap}
            </if>
            <if test="regionId != null">
                AND result.regionId = #{regionId}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND result.serviceUnitLeaderId = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <if test="dpr.hasRegionManager">
                            AND result.regionManagerId = #{dpr.requestUserId}
                        </if>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY result.serviceUnitId DESC, result.crmId DESC
    </select>
    <select id="findCrmAllocationSubTotal"
            resultType="com.qmth.sop.business.bean.result.UserArchivesAllocationResult">
        SELECT
            *,
            (result.effectQuota + result.assistantQuota + result.coordinatorQuota) AS quota,
            (result.coordinatorUnDistributed + result.effectUnDistributed + result.assistantUnDistributed) AS unDistributed
        FROM
            (SELECT
                tbs.id AS serviceUnitId,
                tbs.name AS serviceUnitName,
                tbc.id AS crmId,
                tbc.crm_no AS crmNo,
                su.id AS customId,
                su.name AS customName,
                su.type AS customType,
                su.province AS province,
                su.city AS city,
                su.area AS area,
                sl.id AS levelId,
                sl.level AS level,
                1 AS coordinatorQuota,
                IFNULL(effectQ.quota, 0) AS effectQuota,
                IFNULL(assistantQ.quota, 0) AS assistantQuota,
                IF(tbc.region_coordinator_id IS NULL, 0, 1) AS coordinatorDistributed,
                IFNULL(effectR.reality, 0) AS effectDistributed,
                IFNULL(assistantR.reality, 0) AS assistantDistributed,
                1 + IFNULL(effectQ.quota, 0) + IFNULL(assistantQ.quota, 0) AS quota,
                IF(tbc.region_coordinator_id IS NULL, 0, 1) + IFNULL(effectR.reality, 0) + IFNULL(assistantR.reality, 0) AS distributed,
                IF(tbc.region_coordinator_id IS NULL, 1, 0) AS coordinatorUnDistributed,
                IF(IFNULL(effectQ.quota, 0) - IFNULL(effectR.reality, 0) &lt; 0, 0, IFNULL(effectQ.quota, 0) - IFNULL(effectR.reality, 0)) AS effectUnDistributed,
                IF(IFNULL(assistantQ.quota, 0) - IFNULL(assistantR.reality, 0) &lt; 0, 0, IFNULL(assistantQ.quota, 0) - IFNULL(assistantR.reality, 0)) AS assistantUnDistributed,
                tbc.status AS crmStatus,
                tbs.service_lead_id AS serviceUnitLeaderId,
                tbc.region_id AS regionId,
                tbc.lead_id AS regionManagerId,
                rm.real_name AS regionManagerName,
                pu.real_name AS publishUserName,
                tbc.publish_time AS publishTime
             FROM
                 t_b_crm tbc
                     LEFT JOIN t_b_service tbs ON tbc.service_id = tbs.id
                     LEFT JOIN sys_custom su ON tbc.custom_id = su.id
                     LEFT JOIN sys_level sl ON su.level_id = sl.id
                     LEFT JOIN sys_user pu ON tbc.publish_user_id = pu.id
                     LEFT JOIN sys_user rm ON tbc.lead_id = rm.id
                     LEFT JOIN (SELECT
                                    slr.quota, slr.level_id
                                FROM
                                    sys_level_role slr
                                        LEFT JOIN sys_role sr ON sr.id = slr.role_id
                                WHERE
                                    sr.type = 'EFFECT_ENGINEER') effectQ ON effectQ.level_id = sl.id
                     LEFT JOIN (SELECT
                                    slr.quota, slr.level_id
                                FROM
                                    sys_level_role slr
                                        LEFT JOIN sys_role sr ON sr.id = slr.role_id
                                WHERE
                                    sr.type = 'ASSISTANT_ENGINEER') assistantQ ON assistantQ.level_id = sl.id
                     LEFT JOIN (SELECT
                                    tbuaa.crm_no, COUNT(*) AS reality
                                FROM
                                    t_b_user_archives_allocation tbuaa
                                        LEFT JOIN sys_role sr ON sr.id = tbuaa.role_id
                                WHERE
                                    sr.type = 'EFFECT_ENGINEER'
                                GROUP BY tbuaa.crm_no) effectR ON effectR.crm_no = tbc.crm_no
                     LEFT JOIN (SELECT
                                    tbuaa.crm_no, COUNT(*) AS reality
                                FROM
                                    t_b_user_archives_allocation tbuaa
                                        LEFT JOIN sys_role sr ON sr.id = tbuaa.role_id
                                WHERE
                                    sr.type = 'ASSISTANT_ENGINEER'
                                GROUP BY tbuaa.crm_no) assistantR ON assistantR.crm_no = tbc.crm_no
             WHERE tbs.status = 'PUBLISH') result
        <where>
            <if test="serviceUnitId != null">
                AND result.serviceUnitId = #{serviceUnitId}
            </if>
            <if test="province != null and province != ''">
                AND result.province LIKE CONCAT('%',#{province},'%')
            </if>
            <if test="city != null and city != ''">
                AND result.city LIKE CONCAT('%',#{city},'%')
            </if>
            <if test="area != null and area != ''">
                AND result.area LIKE CONCAT('%',#{area},'%')
            </if>
            <if test="customName != null and customName != ''">
                AND result.customName LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="gap != null">
                AND (result.coordinatorUnDistributed + result.effectUnDistributed + result.assistantUnDistributed) &lt;= #{gap}
            </if>
            <if test="crmIdList != null and crmIdList.size > 0">
                AND result.crmId IN(
                <foreach collection="crmIdList" item="val" separator=",">
                    #{val}
                </foreach>
                )
            </if>
            <if test="crmStatus != null">
                AND result.crmStatus = #{crmStatus}
            </if>
            <if test="regionId != null">
                AND result.regionId = #{regionId}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND result.serviceUnitLeaderId = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <if test="dpr.hasRegionManager">
                            AND result.regionManagerId = #{dpr.requestUserId}
                        </if>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY result.serviceUnitId DESC, result.crmId DESC
    </select>
    <select id="findAllocationByCrmNo"
            resultType="com.qmth.sop.business.bean.result.CrmArchivesAllocationResult">
        SELECT
            sr.type AS type,
            tbua.id AS archivesId,
            tbua.name AS archiverName,
            tbuas.supplier_id AS supplierId,
            ss.name AS supplierName,
            tbua.province AS archivesProvince,
            tbua.city AS archivesCity,
            su.id AS userId
        FROM
            t_b_user_archives_allocation tbuaa
                LEFT JOIN
            sys_role sr ON tbuaa.role_id = sr.id
                LEFT JOIN
            t_b_user_archives tbua ON tbuaa.archives_id = tbua.id
                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
            sys_user su ON su.mobile_number = tbua.mobile_number
        <where>
            <if test="crmNo != null and crmNo != ''">
                AND tbuaa.crm_no = #{crmNo}
            </if>
        </where>
        ORDER BY sr.type
    </select>
</mapper>
