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

    <select id="areaQualityReport" resultType="com.qmth.sop.business.bean.report.QualityAnalyseReportBean">
        select t.*,
        ROUND(t.influenceDegreeA / sum,2) * 100 as influenceDegreeARate,
        ROUND(t.influenceDegreeB / sum,2) * 100 as influenceDegreeBRate,
        ROUND(t.influenceDegreeC / sum,2) * 100 as influenceDegreeCRate,
        ROUND(t.influenceDegreeD / sum,2) * 100 as influenceDegreeDRate
        from(select distinct su.real_name as name,tbc.lead_id as areaManagerId,
        count(distinct tbqpa.id) as sum,
        SUM(CASE WHEN tbqpa.Influence_degree = 'A' THEN 1 ELSE 0 END) as influenceDegreeA,
        SUM(CASE WHEN tbqpa.Influence_degree = 'B' THEN 1 ELSE 0 END) as influenceDegreeB,
        SUM(CASE WHEN tbqpa.Influence_degree = 'C' THEN 1 ELSE 0 END) as influenceDegreeC,
        SUM(CASE WHEN tbqpa.Influence_degree = 'D' THEN 1 ELSE 0 END) as influenceDegreeD
        from
        t_b_quality_problem_apply tbqpa
        join t_b_service tbs on tbs.id = tbqpa.service_id
        join t_b_crm tbc on tbc.crm_no = tbqpa.crm_no
        join sys_user su on su.id = tbc.lead_id
        join t_f_custom_flow_entity tfcfe on tfcfe.code = tbqpa.problem_no
        join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        where tbqpa.service_id = #{serviceId}
        and tbs.status <![CDATA[ <> ]]> 'CANCEL'
        and tffa.status <![CDATA[ <> ]]> 'END'
        and tbqpa.Influence_degree is not null
        group by su.real_name,tbc.lead_id) t
        order by t.sum desc
    </select>

    <select id="humanSupplierQualityReport" resultType="com.qmth.sop.business.bean.report.QualityAnalyseReportBean">
        select temp.*,
           ROUND(temp.influenceDegreeA / sum,2) * 100 as influenceDegreeARate,
           ROUND(temp.influenceDegreeB / sum,2) * 100 as influenceDegreeBRate,
           ROUND(temp.influenceDegreeC / sum,2) * 100 as influenceDegreeCRate,
           ROUND(temp.influenceDegreeD / sum,2) * 100 as influenceDegreeDRate
        from(select distinct IFNULL(ss.name, '启明') as name,IFNULL(ss.id, -1)as supplierId,
        sum(t.sum) as sum,
        sum(t.influenceDegreeA) as influenceDegreeA,
        sum(t.influenceDegreeB) as influenceDegreeB,
        sum(t.influenceDegreeC) as influenceDegreeC,
        sum(t.influenceDegreeD) as influenceDegreeD from
        (select distinct su.mobile_number as mobileNumber,
        count(distinct tbqpa.id) as sum,
        SUM(case when tbqpa.Influence_degree = 'A' then 1 else 0 end) as influenceDegreeA,
        SUM(case when tbqpa.Influence_degree = 'B' then 1 else 0 end) as influenceDegreeB,
        SUM(case when tbqpa.Influence_degree = 'C' then 1 else 0 end) as influenceDegreeC,
        SUM(case when tbqpa.Influence_degree = 'D' then 1 else 0 end) as influenceDegreeD
        from t_b_quality_problem_apply tbqpa
        join t_b_service tbs on tbs.id = tbqpa.service_id
        join sys_user su on FIND_IN_SET(su.id, tbqpa.user_ids)
        join t_f_custom_flow_entity tfcfe on tfcfe.code = tbqpa.problem_no
        join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        where tbqpa.service_id = #{serviceId}
        and tbs.status <![CDATA[ <> ]]> 'CANCEL'
        and tffa.status <![CDATA[ <> ]]> 'END'
        and tbqpa.Influence_degree is not null
        group by su.mobile_number) t
        left join t_b_user_archives tbua on tbua.mobile_number = t.mobileNumber
        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
        group by ss.name,ss.id) temp
        order by temp.sum desc
    </select>

    <select id="reasonQualityReport" resultType="com.qmth.sop.business.bean.report.QualityAnalyseReportBean">
        select t.* from(select tbqpa.reason,
        count(distinct tbqpa.id) as reasonSum
        from t_b_quality_problem_apply tbqpa
        join t_b_service tbs on tbs.id = tbqpa.service_id
        join t_f_custom_flow_entity tfcfe on tfcfe.code = tbqpa.problem_no
        join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        where tbqpa.service_id = #{serviceId}
        and tbs.status <![CDATA[ <> ]]> 'CANCEL'
        and tffa.status <![CDATA[ <> ]]> 'END'
        and tbqpa.reason is not null
        group by tbqpa.reason) t order by t.reasonSum desc
    </select>

    <sql id="viewListCommon">
        select distinct sc.name as customName,
        tbc.name as projectName,
        tbcd.course_name as courseName,
        tbqpa.Influence_degree as InfluenceDegree,
        su.real_name as areaManagerName,
        (select group_concat(su.real_name) from sys_user su where FIND_IN_SET(su.id, tbqpa.user_ids)) as dutyName,
        tbqpa.summary,
        tbqpa.reason,
        tbqpa.id,
        tbqpa.problem_no as problemNo,
        tbqpa.sop_no as sopNo,
        tfcfe.flow_id as problemNoFlowId,
        tfcfe1.flow_id as sopFlowId,
        IFNULL(ss.name, '启明') as humanSupplierName
        from t_b_quality_problem_apply tbqpa
        left join t_b_service tbs on tbs.id = tbqpa.service_id
        left join t_b_crm tbc on tbc.crm_no = tbqpa.crm_no
        left join sys_custom sc on sc.id = tbc.custom_id
        left join t_b_crm_detail tbcd on tbcd.sop_no = tbqpa.sop_no
        left join sys_user su on su.id = tbc.lead_id
        left join t_f_custom_flow_entity tfcfe on tfcfe.code = tbqpa.problem_no
        left join t_f_custom_flow_entity tfcfe1 on tfcfe1.code = tbqpa.sop_no
        left join sys_user su1 on FIND_IN_SET(su1.id, tbqpa.user_ids)
        left join t_b_user_archives tbua on tbua.mobile_number = su1.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
        <where>
            <if test="serviceId != null and serviceId != ''">
                and tbqpa.service_id = #{serviceId}
            </if>
            <if test="areaManagerId != null and areaManagerId != ''">
                and tbc.lead_id = #{areaManagerId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                <choose>
                    <when test="supplierId != -1">
                        and ss.id = #{supplierId}
                    </when>
                    <otherwise>
                        and ss.id is null
                    </otherwise>
                </choose>
            </if>
            <if test="reason != null and reason != ''">
                and tbqpa.reason = #{reason}
            </if>
            <if test="customName != null and customName != ''">
                and sc.name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="influenceDegree != null and influenceDegree != ''">
                and tbqpa.Influence_degree = #{influenceDegree}
            </if>
        </where>
        order by sc.name,tbqpa.Influence_degree
    </sql>

    <select id="viewList" resultType="com.qmth.sop.business.bean.report.QualityAnalyseReportViewBean">
        <include refid="viewListCommon" />
    </select>

    <select id="viewListExport" resultType="com.qmth.sop.business.bean.report.QualityAnalyseReportViewBean">
        <include refid="viewListCommon" />
    </select>
</mapper>
