<?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.CrmAnalyseMapper">
    <select id="region" resultType="com.qmth.sop.business.bean.result.CrmAnalyseResult">

        SELECT
        <!--        r.id,-->
        rd.province name,
        count(DISTINCT c.id ) count
        FROM
        t_b_crm c
        LEFT JOIN sys_custom cu ON cu.id = c.custom_id
        LEFT JOIN t_b_service s ON c.service_id = s.id
        left join t_b_service_region_detail rd on rd.id=c.region_detail_id
        <where>
            and s.`status` IN ( 'PUBLISH', 'FINISH' ) and c.status!='FINISH' AND c.region_detail_id IS NOT NULL
            <if test="type != null and type != ''">
                and cu.type = #{type}
            </if>
            <!--            <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>
        GROUP BY
        <!--        r.id,-->
        rd.province
        order by count(DISTINCT c.id ) desc
    </select>
    <select id="project" resultType="java.util.Map">

        SELECT
        s.id,
        s.`name`,
        c.crm_no,
        si.sop_no,
        tffa.`status`
        FROM
        t_b_service s
        LEFT JOIN t_b_crm c ON c.service_id = s.id
        LEFT JOIN sys_custom cu ON cu.id = c.custom_id
        LEFT JOIN t_b_sop_info si ON si.crm_no = c.crm_no
        LEFT JOIN t_f_custom_flow_entity tfcfe ON tfcfe.CODE = si.sop_no
        LEFT JOIN t_f_flow_approve tffa ON tffa.flow_id = tfcfe.flow_id

        <where>
            s.`status` IN ( 'PUBLISH', 'FINISH' ) and c.crm_no is not null
            and c.service_id is not null and c.enable=1
<!--            and c.`status`!='UN_PUBLISH'-->
            <!--            <if test="year != null and year != ''">-->
            <!--                and YEAR ( FROM_UNIXTIME( s.start_time / 1000 ))=  #{year}-->
            <!--            </if>-->
            <if test="startTime != null">
                AND s.start_time <![CDATA[ >= ]]> #{startTime}
            </if>
            <if test="endTime != null">
                AND s.start_time <![CDATA[ <= ]]> #{endTime}
            </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>
    </select>

    <select id="supplier" resultType="com.qmth.sop.business.bean.result.CrmAnalyseResult">
        SELECT
        su.id,
        su.`name`,
        count(DISTINCT c.id ) count
        FROM
        t_b_crm c
        LEFT JOIN t_b_service s ON c.service_id = s.id
        left join t_b_service_region r on r.id=c.region_id
        LEFT JOIN sys_user u ON u.id = c.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_supplier su ON su.id = us.supplier_id
        <where>
            <!--            <if test="year != null and year != ''">-->
            <!--                and YEAR ( FROM_UNIXTIME( s.start_time / 1000 ))=  #{year}-->
            <!--            </if>-->
            and su.id is not null
            and s.`status` IN ( 'PUBLISH', 'FINISH' ) and c.service_id is not null and c.enable=1
            <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>
        GROUP BY
        su.id,
        su.`name`

    </select>

    <select id="monthly" resultType="java.util.Map">
        SELECT
        a.year,
        a.month ,
        count(DISTINCT a.id) count
        FROM
        (
        SELECT DISTINCT
        CAST( YEAR ( FROM_UNIXTIME( c.begin_time / 1000 )) AS CHAR ) YEAR,
        MONTH (
        FROM_UNIXTIME( c.begin_time / 1000 )) MONTH,
        c.id
        FROM
        t_b_crm c
        LEFT JOIN t_b_service s ON c.service_id = s.id
        LEFT JOIN sys_custom cu ON cu.id = c.custom_id
        <where>
            and s.`status` IN ( 'PUBLISH', 'FINISH' ) and c.service_id is not null and c.enable=1
            <if test="type != null and type != ''">
                and cu.type = #{type}
            </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>
        ) a
        GROUP BY
        a.YEAR,
        a.MONTH
        ORDER BY
        a.YEAR,
        a.MONTH

    </select>


    <select id="countCustomType" resultType="java.util.Map">
        SELECT
        sum(case when cu.type='OFFICE' then 1 else 0 end) as OFFICE,
        sum(case when cu.type='CLOUD_MARK' then 1 else 0 end) as CLOUD_MARK
        FROM t_b_service s
        LEFT JOIN t_b_crm c ON c.service_id = s.id
        LEFT join sys_custom cu on cu.id=c.custom_id

        <where>

            and s.`status` IN ( 'PUBLISH', 'FINISH' ) and c.service_id is not null and c.enable=1
            <if test="startTime != null">
                AND s.start_time <![CDATA[ >= ]]> #{startTime}
            </if>
            <if test="endTime != null">
                AND s.start_time <![CDATA[ <= ]]> #{endTime}
            </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>
    </select>

    <select id="crmDetail" resultType="com.qmth.sop.business.bean.result.TBCrmResult">
        SELECT
        distinct a.id as crmId,a.crm_no as crmNo,
        a.begin_time as beginTime,
        a.name,
        tbs.id,
        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 t_b_service tbs ON a.service_id = tbs.id
        left join t_b_service_region r on r.id=a.region_id
        left join t_b_service_region_detail rd on rd.id=a.region_detail_id
        LEFT JOIN t_b_product p ON p.id = a.product_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 sys_user lu ON lu.id = a.lead_id
        LEFT JOIN sys_user cu ON cu.id = a.create_id
        LEFT JOIN sys_user cru ON cru.id = a.crm_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 tbs.`status` IN ( 'PUBLISH', 'FINISH' ) and a.service_id is not null and a.enable=1
            <if test="serviceId != null and serviceId != ''">
                and a.crm_no is not null
                and a.service_id = #{serviceId}
            </if>
            <!--            <if test="regionId != null and regionId != ''">-->
            <!--                and r.id = #{regionId}-->
            <!--            </if>-->
            <if test="province != null and province != ''">
<!--                and r.id is not null-->
                and a.status!='FINISH'
                and rd.province = #{province}
            </if>
            <if test="type != null and type != ''">
                and sc.type = #{type}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and su.id = #{supplierId}
            </if>
            <if test="startTime != null">
                AND tbs.start_time <![CDATA[ >= ]]> #{startTime}
            </if>
            <if test="endTime != null">
                AND tbs.start_time <![CDATA[ <= ]]> #{endTime}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND tbs.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>
            <if test="crmYear != null and crmYear != ''">
                and YEAR (FROM_UNIXTIME( a.begin_time / 1000))= #{crmYear}
            </if>
            <if test="month != null and month != ''">
                and MONTH (FROM_UNIXTIME(a.begin_time / 1000))= #{month}
            </if>
        </where>
        ORDER BY a.crm_no DESC
    </select>

    <select id="projectCount" resultType="java.util.Map">
        SELECT
        sum(case when c.id is not null then 1 else 0 end ) crm
<!--        count(DISTINCT a.id ) person-->
        FROM
        t_b_service s
        LEFT JOIN t_b_crm c ON c.service_id = s.id
<!--        LEFT JOIN t_b_user_archives_allocation a ON a.service_id = s.id-->
        <where>
            and s.`status` IN ( 'PUBLISH', 'FINISH' ) and c.service_id is not null and c.enable=1
            <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>
    </select>
    <select id="findAllocation"
            resultType="java.lang.Integer">
        SELECT
        sum(result.distributed)
        FROM
        (SELECT


        IF(tbc.region_coordinator_id IS NULL, 0, 1) + IFNULL(effectR.reality, 0) + IFNULL(assistantR.reality, 0) AS distributed

        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 (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>
            and tbs.`status` IN ( 'PUBLISH', 'FINISH' ) and tbc.service_id is not null and tbc.enable=1
            <if test="startTime != null">
                AND tbs.start_time >= #{startTime}
            </if>
            <if test="endTime != null">
                AND #{endTime} >= tbs.start_time
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND tbs.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>
        </where>

        ) result

    </select>
</mapper>
