<?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.ServiceAnalyseMapper">
    <select id="list" resultType="com.qmth.sop.business.entity.TBService">
        select * from t_b_service s
        <where>
            and s.status in ('PUBLISH','FINISH')

            <!--            <if test="year != null and year != ''">-->
            <!--                and YEAR ( FROM_UNIXTIME( s.start_time / 1000 ))=  #{year}-->
            <!--            </if>-->
            <if test="startTime != null">
                AND s.start_time >= #{startTime}
            </if>
            <if test="endTime != null">
                AND #{endTime} >= s.start_time
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND s.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>
        </where>
        order by s.start_time desc
    </select>

    <select id="equipmentOutboundTotal" resultType="java.util.Map">
        SELECT
            IFNULL(sum(case when io.status = 'NORMAL' then 1 else 0 end),0) deviceSum,
            IFNULL(sum(case when io.type = 'OUT' and io.service_id = #{serviceUnitId} then 1 else 0 end),0) outs
        from t_b_device_in_out io
    </select>

    <select id="projectProgress" resultType="java.util.Map">
        select tfcfe.code as code,tfcfe.crm_no as crmNo,tfcfe.flow_id as flowId,tffa.status as status from t_b_service tbs
        left join t_b_crm tbc ON tbc.service_id = tbs.id
        left join t_f_custom_flow_entity tfcfe on tfcfe.crm_no = tbc.crm_no
        left join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        <where>
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and tbs.id = #{serviceUnitId}
            </if>
            and tfcfe.code like 'SOP%'
        </where>
    </select>

    <select id="projectCount" resultType="java.lang.Integer">
        SELECT
        count(DISTINCT c.id )
        FROM
        t_b_service s
        LEFT JOIN t_b_crm c ON c.service_id = s.id
        <where>

            <if test="serviceUnitId != null and serviceUnitId != ''">
                and s.id = #{serviceUnitId}
            </if>

        </where>
    </select>
    <select id="supplierDistribution" resultType="java.util.Map">
        SELECT
        CONCAT( su.id, '' ) id,
        su.`name`,
        count( DISTINCT c.id ) count
        FROM t_b_service s
        LEFT JOIN t_b_crm c ON s.id = c.service_id
        LEFT JOIN t_b_user_archives_allocation tbuaa on tbuaa.crm_no = c.crm_no
        LEFT JOIN t_b_user_archives_supplier tbuas on tbuas.user_archives_id = tbuaa.archives_id
        LEFT JOIN sys_supplier su on su.id = tbuas.supplier_id
        <where>
                    and su.id IS NOT NULL
                    <if test="serviceUnitId != null and serviceUnitId != ''">
                        and s.id = #{serviceUnitId}
                    </if>
                </where>
        GROUP BY
        su.id,
        su.`name`
    </select>
    <select id="crmDetail" resultType="com.qmth.sop.business.bean.result.TBCrmResult">
        SELECT
        distinct a.*,
        tbs.name service,
        cru.real_name crm_user_name,
        sc.NAME custom,
        sc.type custom_type,
        p.name product,
        lu.real_name leadName,
        cu.real_name createName,
        tbs.status serviceUnitStatus,
        u.real_name regionCoordinator,
        su.name supplier
        FROM
        t_b_crm a
        LEFT JOIN sys_user cru ON cru.id = a.crm_user_id
        LEFT JOIN sys_user lu ON lu.id = a.lead_id
        LEFT JOIN sys_user cu ON cu.id = a.create_id
        LEFT JOIN t_b_product p ON p.id = a.product_id
        LEFT JOIN t_b_service tbs ON a.service_id = tbs.id
        LEFT JOIN sys_custom sc ON sc.id = a.custom_id
        LEFT JOIN sys_user u ON u.id = a.region_coordinator_id
        left join t_b_user_archives_allocation tbuaa on tbuaa.crm_no = a.crm_no
        LEFT JOIN t_b_user_archives ua ON ua.id = tbuaa.archives_id
        LEFT JOIN t_b_user_archives_supplier tbuas on tbuas.user_archives_id = ua.id
        LEFT JOIN sys_supplier su on su.id = tbuas.supplier_id
        <where>
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and a.service_id = #{serviceUnitId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and su.id = #{supplierId}
            </if>
        </where>
        ORDER BY
        a.create_time DESC
    </select>
    <select id="supplierRole" resultType="java.util.Map">
<!--        SELECT-->
<!--        r.NAME,-->
<!--        count( DISTINCT ua.archives_id ) count-->
<!--        FROM-->
<!--        t_b_user_archives_allocation ua-->
<!--        LEFT JOIN sys_role r ON r.id = ua.role_id-->
<!--        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.archives_id-->
<!--        <where>-->
<!--            and us.authentication_valid_time > UNIX_TIMESTAMP()* 1000-->
<!--            <if test="supplierId != null and supplierId != ''">-->
<!--                and us.supplier_id = #{supplierId}-->
<!--            </if>-->
<!--        </where>-->
<!--        GROUP BY-->
<!--        r.NAME-->


        SELECT
        r.NAME,
        count( DISTINCT ua.user_id ) count
        FROM
        t_b_user_archives_allocation ua
        LEFT JOIN sys_role r ON r.id = ua.role_id
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.archives_id
                <where>
                    and us.authentication_valid_time > UNIX_TIMESTAMP()* 1000
                    <if test="supplierId != null and supplierId != ''">
                        and us.supplier_id = #{supplierId}
                    </if>
                    <if test="serviceUnitId != null and serviceUnitId != ''">
                        and ua.service_id = #{serviceUnitId}
                    </if>
                </where>
        GROUP BY
        r.NAME

        UNION all



        SELECT
        '区域协调人' as NAME,
        count( DISTINCT aa.region_user_id ) count

        FROM
        t_b_user_archives_allocation aa
        LEFT JOIN sys_user u ON u.id = aa.region_user_id
        LEFT JOIN t_b_user_archives ua ON u.mobile_number = ua.mobile_number
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.id
        LEFT JOIN sys_supplier su ON su.id = us.supplier_id
        <where>
            and us.authentication_valid_time > UNIX_TIMESTAMP()* 1000
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and aa.service_id = #{serviceUnitId}
            </if>
        </where>
    </select>
    <select id="supplierRegion" resultType="java.util.Map">
        SELECT DISTINCT
        cast(r.id as char) as region_id,
        r.region_name
        FROM
        t_b_crm c
        LEFT JOIN
        t_b_service_region r ON r.id = c.region_id
        LEFT JOIN
        t_b_user_archives_allocation tbuaa on tbuaa.crm_no = c.crm_no
        LEFT JOIN
        t_b_user_archives ua ON ua.id = tbuaa.archives_id
        LEFT JOIN
        t_b_user_archives_supplier us ON us.user_archives_id = ua.id
        LEFT JOIN
        sys_supplier su ON su.id = us.supplier_id
        <where>
            and r.id IS NOT NULL
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and c.service_id = #{serviceUnitId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
        </where>
    </select>

    <select id="findCrmAllocation" 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.lead_id AS regionManagerId,
        rm.real_name AS regionManagerName,
        pu.real_name AS publishUserName,
        tbc.publish_time AS publishTime,
        r.id region_id,
        us.supplier_id
        FROM
        t_b_crm tbc

        LEFT JOIN t_b_service tbs ON tbc.service_id = tbs.id
        LEFT JOIN t_b_service_region r ON tbs.id = r.service_id
        LEFT JOIN sys_user u ON u.id = tbc.region_coordinator_id
        LEFT JOIN t_b_user_archives ua ON u.mobile_number = ua.mobile_number
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.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 ) result
        <where>
            <if test="serviceUnitId != null">
                AND result.serviceUnitId = #{serviceUnitId}
            </if>
            <if test="regionId != null and regionId != ''">
                AND result.region_id =#{regionId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                AND result.supplier_id = #{supplierId}
            </if>
        </where>
        ORDER BY result.serviceUnitId DESC, result.crmId DESC
    </select>

    <select id="device" resultType="java.util.Map">
        SELECT
        su.`name`,
        sum(case when io.type='OUT' then 1 else 0 end )-sum(case when io.type='IN' then 1 else 0 end ) outs
<!--        sum( CASE WHEN d.`status` = 'NORMAL' THEN 1 ELSE 0 END ) count-->
        FROM
        t_b_service s
        LEFT JOIN t_b_device_in_out io ON io.service_id = s.id
        LEFT JOIN sys_device d ON d.serial_no = io.device_serial_no
        LEFT JOIN sys_supplier su ON su.id = d.supplier_id
        <where> 1 = 1
            and io.id IS NOT NULL and su.id is not null
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and s.id = #{serviceUnitId}
            </if>
        </where>
        GROUP BY
        su.`name`
    </select>
    <select id="personnel" resultType="java.util.Map">
        SELECT
        su.`name`,
        r.region_name,
        count( DISTINCT aa.user_id ) count
        FROM
        t_b_user_archives_allocation aa
        LEFT JOIN t_b_service_region r ON aa.service_id = r.service_id
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = aa.archives_id
        LEFT JOIN sys_supplier su ON su.id = us.supplier_id
        <where>
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and aa.service_id = #{serviceUnitId}
            </if>
        </where>
        GROUP BY
        su.`name`,
        r.region_name

        UNION ALL

        SELECT
        su.`name`,
        r.region_name,
        count( DISTINCT aa.region_user_id ) count
        FROM
        t_b_user_archives_allocation aa
        LEFT JOIN sys_user u ON u.id = aa.region_user_id
        LEFT JOIN t_b_user_archives ua ON u.mobile_number = ua.mobile_number
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.id
        LEFT JOIN t_b_service_region r ON aa.service_id = r.service_id
        LEFT JOIN sys_supplier su ON su.id = us.supplier_id
        <where>
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and aa.service_id = #{serviceUnitId}
            </if>
        </where>
        GROUP BY
        su.`name`,
        r.region_name
    </select>

    <select id="findWaitZone" resultType="java.lang.Integer">
        select IFNULL(count(1),0) as c from t_b_crm tbc
        left join sys_custom sc on sc.id = tbc.custom_id
        where exists(
                SELECT tbsr.id FROM t_b_service_region tbsr
                        left join t_b_service_region_detail tbsrd on tbsrd.service_region_id = tbsr.id
                where tbsr.id = #{regionId} and sc.province = tbsrd.province and sc.city = tbsrd.city)
          and tbc.service_id is null and tbc.enable = 1
    </select>
</mapper>
