<?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.QualityAnalyseMapper">
    <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="pie" resultType="java.util.Map">
        SELECT
        a.Influence_degree,
        count( a.id ) count
        FROM
        t_b_quality_problem_apply a
        LEFT JOIN t_b_sop_info si ON si.sop_no = a.sop_no
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = si.id
        left join t_b_crm c on c.crm_no=si.crm_no
        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 t_f_custom_flow_entity fe ON fe.`code` = a.problem_no
        LEFT JOIN t_f_flow_approve fa ON fa.flow_id = fe.flow_id
        <where>
            and a.type != 'NO_PROBLEM' AND fa.STATUS = 'FINISH'
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and a.service_id = #{serviceUnitId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
        </where>
        GROUP BY
        a.Influence_degree
    </select>
    <select id="radar" resultType="java.util.Map">
        SELECT
        r.region_name,
        s.NAME supplier,
        a.reason,
        count( a.id ) count
        FROM
        t_b_quality_problem_apply a
        LEFT JOIN t_b_sop_info si ON si.sop_no = a.sop_no
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = si.id
        left join t_b_crm c on c.crm_no=si.crm_no
        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 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 s ON s.id = us.supplier_id
        LEFT JOIN t_f_custom_flow_entity fe ON fe.`code` = a.problem_no
        LEFT JOIN t_f_flow_approve fa ON fa.flow_id = fe.flow_id
        <where>
            and a.type != 'NO_PROBLEM' and fa.STATUS = 'FINISH'
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and a.service_id = #{serviceUnitId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
        </where>
        GROUP BY
        r.region_name,
        s.NAME,
        a.reason
    </select>
    <sql id="query">
        SELECT
        distinct a.*,
        si.type sopType,
        sc.NAME custom,
        sc.type customType,
        fa.STATUS,
        fa.setup,
        fa.create_time submissionTime,
        fa.update_time updateDateTime,
        cu.real_name submitter ,
        fe.flow_id as flowId,
        IFNULL(art.NAME_,'--') as taskName,
        art.TASK_DEF_KEY_ as taskDefKey,
        (select group_concat(us1.real_name SEPARATOR ';') from sys_user us1
        where find_in_set(us1.id, a.user_ids)) user_names,
        (select group_concat(us1.real_name SEPARATOR ';') from sys_user us1
        where find_in_set(us1.id, (select tffl.pend_approve_id from t_f_flow_log tffl where tffl.flow_id = fe.flow_id order by tffl.create_time desc limit 1))) pend_approve_users
        FROM
        t_b_quality_problem_apply a
        LEFT JOIN t_b_sop_info si on si.sop_no = a.sop_no
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = si.id
        left join t_b_crm c on c.crm_no=si.crm_no
        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 t_f_custom_flow_entity fe ON 	fe.`code`= a.problem_no
        LEFT JOIN t_f_flow_approve fa ON fa.flow_id = fe.flow_id
        LEFT JOIN sys_user cu ON cu.id = fa.create_id
        left join ACT_RU_TASK art on art.PROC_INST_ID_ = fa.flow_id
        left join act_ru_identitylink ari on ari.TASK_ID_ = art.ID_
        left join t_b_crm tbc on tbc.crm_no = fe.crm_no
        LEFT JOIN sys_custom sc ON sc.id = tbc.custom_id
        left join t_b_service tbs on tbs.id = tbc.service_id

    </sql>
    <select id="detail" resultType="com.qmth.sop.business.bean.result.TBQualityProblemApplyResult">
        <include refid="query" />
        <where>
                and a.type != 'NO_PROBLEM' AND fa.STATUS = 'FINISH'
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and a.service_id = #{serviceUnitId}
            </if>
            <if test="degree != null and degree != ''">
                and a.Influence_degree = #{degree}
            </if>
        </where>
        ORDER BY a.create_time DESC
    </select>

    <select id="influenceDetail" resultType="com.qmth.sop.business.bean.result.TBQualityProblemApplyResult">
        <include refid="query" />
        <where>
            AND fa.STATUS = 'FINISH'
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and a.service_id = #{serviceUnitId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
            <if test="region_id != null and region_id != ''">
                and c.region_id = #{region_id}
            </if>
            <if test="influenceDegree != null and influenceDegree != ''">
                and a.Influence_degree = #{influenceDegree}
            </if>
            <if test="reason != null and reason != ''">
                and a.reason = #{reason}
            </if>
        </where>
        ORDER BY a.create_time DESC
    </select>


    <select id="progress" resultType="java.lang.Double">
        SELECT
        sum(case when fa.`status`='FINISH' then 1 else 0 end )*100/count( a.id ) count
        FROM
        t_b_quality_problem_apply a
        LEFT JOIN t_f_custom_flow_entity fe ON fe.`code` = a.problem_no
        LEFT JOIN t_f_flow_approve fa ON fa.flow_id = fe.flow_id
        <where>
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and a.service_id = #{serviceUnitId}
            </if>

        </where>

    </select>
    <select id="influence" resultType="java.util.Map">
        SELECT
        a.id,
        a.Influence_degree,
        su.`name` supplier,
        su.id supplier_id,
        r.id region_id,
        r.region_name,
        a.reason
        FROM
        t_b_quality_problem_apply a
        LEFT JOIN t_b_sop_info si ON si.sop_no = a.sop_no
        LEFT JOIN t_f_custom_flow_entity fe ON fe.`code` = a.problem_no
        LEFT JOIN t_f_flow_approve fa ON fa.flow_id = fe.flow_id
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = si.id
        left join t_b_crm c on c.crm_no=si.crm_no
        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 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
        <where>
            AND fa.STATUS = 'FINISH'
            <if test="serviceUnitId != null and serviceUnitId != ''">
                and a.service_id = #{serviceUnitId}
            </if>
        </where>

    </select>
</mapper>
