<?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.SopAnalyseMapper">
    <select id="list" resultType="com.qmth.sop.business.entity.TBService">
        select * from t_b_service s
        <where>
            and s.status = '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 >= #{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="overview" resultType="java.util.Map">
        SELECT
        r.region_name,
        u.real_name,
        su.NAME supplier,
        c.crm_no,
        c.name,
        c.`status`,
        s.sop_no,
        tffa.status sopStatus,
        d.id did,
        d.`status` dstatus,
        case when d.`status`='CLOSE' then ((d.close_time - d.warn_time)/60/1000) else null end ddiff,
        v.id vid,
        v.`status` vstatus,
        case when v.`status`='CLOSE' then ((v.close_time - v.create_time)/60/1000) else null end vdiff
        FROM
        t_b_sop_info s
        left join t_b_crm c on c.crm_no=s.crm_no
        left join t_b_service_region r on r.id=c.region_id
        LEFT join t_f_custom_flow_entity tfcfe on tfcfe.code = s.sop_no
        LEFT join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = s.id
        <if test="type != null and type != ''">
            <choose>
                <when test="type == 'CRM' or type == 'SUPPLIER'">
                    LEFT JOIN sys_user u ON u.id = c.region_coordinator_id
                </when>
                <otherwise>
                    LEFT JOIN sys_user u ON u.id = c.lead_id
                </otherwise>
            </choose>
        </if>
        LEFT JOIN t_b_user_archives ua ON ua.mobile_number = u.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
        left join t_b_delay_warn d on d.sop_no=s.sop_no
        left join t_b_violation v on v.sop_no=s.sop_no
        <where>

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

        </where>
    </select>
    <select id="trend" resultType="java.util.Map">
        SELECT
        a.click_date,
        b.supplier,
        ifnull( b.count, 0 ) AS count
        FROM
        (
        SELECT
        curdate() AS click_date UNION ALL
        SELECT
        date_sub( curdate(), INTERVAL 1 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate(), INTERVAL 2 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate(), INTERVAL 3 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate(), INTERVAL 4 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate(), INTERVAL 5 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate(), INTERVAL 6 DAY ) AS click_date
        ) a
        LEFT JOIN (
        SELECT
        su.NAME supplier,
        DATE_FORMAT(
        FROM_UNIXTIME( ( CASE WHEN d.create_time IS NOT NULL THEN d.create_time WHEN v.create_time IS NOT NULL THEN v.create_time ELSE NULL END )/ 1000 ),
        '%Y-%m-%d'
        ) dattime,
        count( DISTINCT d.id )+ count( DISTINCT v.id ) count
        FROM
        t_b_sop_info s
        left join t_b_crm c on c.crm_no=s.crm_no
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = s.id
        LEFT JOIN sys_user u ON u.id = c.region_coordinator_id
        LEFT JOIN t_b_user_archives ua ON ua.mobile_number = u.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
        LEFT JOIN t_b_delay_warn d ON d.sop_no = s.sop_no
        LEFT JOIN t_b_violation v ON v.sop_no = s.sop_no
        <where>
            and (d.id IS NOT NULL OR v.id IS NOT NULL)
            <if test="serviceId != null and serviceId != ''">
                and  s.service_id= #{serviceId}
            </if>

        </where>
        GROUP BY
        su.NAME,
        dattime
        ) b ON a.click_date = b.dattime
    </select>

    <select id="attendance" resultType="java.util.Map">
        SELECT
        r.region_name,
        u.real_name,
        su.NAME supplier,
        c.crm_no,
        c.name,
        c.`status`,
        s.sop_no,
        d.id,
        du.real_name dname
        FROM
        t_b_sop_info s
        left join t_b_crm c on c.crm_no=s.crm_no
        left join t_b_service_region r on r.id=c.region_id
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = s.id
        <if test="type != null and type != ''">
            <choose>
                <when test="type == 'CRM' or type == 'SUPPLIER'">
                    LEFT JOIN sys_user u ON u.id = c.region_coordinator_id
                </when>
                <otherwise>
                    LEFT JOIN sys_user u ON u.id = c.lead_id
                </otherwise>
            </choose>
        </if>
        LEFT JOIN t_b_user_archives ua ON ua.mobile_number = u.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
        left join t_b_ding_apply d on d.sop_no=s.sop_no
        left join sys_user du on du.id=d.create_id
        <where>
            <if test="serviceId != null and serviceId != ''">
                and  s.service_id= #{serviceId}
            </if>
        </where>
    </select>

    <select id="findSopMonitorListByServiceId"
            resultType="com.qmth.sop.business.bean.result.analyze.sopWarn.SopMonitor">
        SELECT
            sopMonitor.sop_monitor_type AS sopMonitorType,
            sopMonitor.crm_no AS crmNo,
            sopMonitor.sop_no AS sopNo,
            sopMonitor.service_id AS serviceId,
            sopMonitor.user_id AS userId,
            sopMonitor.finish_status AS finishStatus,
            sopMonitor.start_time AS startTime,
            sopMonitor.close_time AS closeTime,
            tbc.region_id AS regionId,
            tbsr.region_name AS regionName,
            IFNULL(ss.id,0) AS supplierId,
            IFNULL(ss.name,'大区经理兼任') AS supplierName
        FROM
            (SELECT
                 'DELAY_WARN' AS sop_monitor_type,
                 crm_no,
                 sop_no,
                 service_id,
                 create_id AS user_id,
                 IF(status = 'CLOSE', TRUE, FALSE) AS finish_status,
                 warn_time AS start_time,
                 close_time AS close_time
             FROM
                 t_b_delay_warn
             UNION ALL SELECT
                 'VIOLATION' AS sop_monitor_type,
                 crm_no,
                 sop_no,
                 service_id,
                 create_id AS user_id,
                 IF(status = 'CLOSE', TRUE, FALSE) AS finish_status,
                 create_time AS start_time,
                 close_time AS close_time
             FROM
                 t_b_violation) sopMonitor
                LEFT JOIN
            t_b_crm tbc ON tbc.crm_no = sopMonitor.crm_no
                LEFT JOIN
            t_b_service_region tbsr ON tbc.region_id = tbsr.id
                LEFT JOIN
            sys_user su ON sopMonitor.user_id = su.id
                LEFT JOIN
            t_b_user_archives tbua ON tbua.mobile_number = su.mobile_number
                LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
                LEFT JOIN
            sys_supplier ss ON tbuas.supplier_id = ss.id
        <where>
            <if test="serviceId != null">
                AND sopMonitor.service_id = #{serviceId}
            </if>
        </where>
    </select>
    <select id="findCrmSupplierRelationByServiceId"
            resultType="com.qmth.sop.business.bean.result.analyze.sopWarn.CrmSupplierRelation">
        SELECT
            cu.service_id AS serviceId,
            cu.crm_no AS crmNo,
            cu.user_id AS userId,
            IFNULL(ss.id, 0) AS supplierId,
            IFNULL(ss.name, '大区经理兼任') AS supplierName
        FROM
            (SELECT
                 service_id, crm_no, user_id
             FROM
                 t_b_user_archives_allocation
             UNION ALL SELECT
                           service_id, crm_no, region_coordinator_id AS user_id
             FROM
                 t_b_crm) cu
                LEFT JOIN
            sys_user su ON cu.user_id = su.id
                LEFT JOIN
            t_b_user_archives tbua ON su.mobile_number = tbua.mobile_number
                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
            t_b_crm tbc ON tbc.crm_no = cu.crm_no
        <where>
            <if test="serviceId != null">
                AND cu.service_id = #{serviceId}
            </if>
        </where>
    </select>
</mapper>
