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

    <select id="deviceSupplierCountReport" resultType="com.qmth.sop.business.bean.report.SupplierCountReportBean">
        select ss.name as deviceSupplierName,count(distinct tbdd.id) as count from t_b_service tbs
        join t_b_sop_info tbsi on tbsi.service_id = tbs.id
        join t_b_device_delivery tbdd on tbdd.crm_no = tbsi.crm_no
        join sys_device sd on sd.serial_no = tbdd.serial_no
        join sys_supplier ss on ss.id = sd.supplier_id
        where tbs.id = #{serviceId}
        and ss.`type` = 'DEVICE' and tbdd.status = 'USING'
        and tbs.status <![CDATA[ <> ]]> 'CANCEL'
        group by ss.name
        order by ss.name
    </select>

    <select id="humanSupplierCountReport" resultType="com.qmth.sop.business.bean.report.SupplierCountReportBean">
        select ss.name as humanSupplierName,count(distinct tbuas.user_archives_id) as count from t_b_service tbs
        join t_b_sop_info tbsi on tbsi.service_id = tbs.id
        join t_b_crm_detail tbcd on tbcd.sop_no = tbsi.sop_no
        join t_f_custom_flow_entity tfcfe on tfcfe.code = tbcd.sop_no
        join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        join t_b_user_archives_allocation tbuaa on tbuaa.crm_detail_id = tbcd.id
        join t_b_user_archives_supplier tbuas on tbuas.user_archives_id = tbuaa.archives_id
        join sys_supplier ss on ss.id = tbuas.supplier_id
        where tbs.id = #{serviceId}
        and ss.`type` = 'HUMAN' and tbcd.status = 'PUBLISH'
        and tbs.status <![CDATA[ <> ]]> 'CANCEL'
        and tffa.status <![CDATA[ <> ]]> 'END'
        group by ss.name
        order by ss.name
    </select>

    <select id="areaDingExceptionCountReport" resultType="com.qmth.sop.business.bean.report.DingExceptionCountReportBean">
        select * from(select distinct su.real_name as areaManagerName,sum(case when tbd.sign_in_time is null or tbd.sign_out_time is null then 1 else 0 end) as count
        from t_b_service tbs
        join t_b_crm tbc on tbc.service_id = tbs.id
        join t_b_sop_info tbsi on tbsi.crm_no = tbc.crm_no
        join sys_user su on su.id = tbc.lead_id
        join t_f_custom_flow_entity tfcfe on tfcfe.code = tbsi.sop_no
        join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
        join t_b_ding tbd on tbd.ding_sop_no = tbsi.sop_no
        where tbs.id = #{serviceId}
        and tbs.status <![CDATA[ <> ]]> 'CANCEL'
        and tffa.status <![CDATA[ <> ]]> 'END'
        group by su.real_name) t where t.count > 0
        order by t.areaManagerName
    </select>

    <select id="humanDingExceptionCountReport" resultType="com.qmth.sop.business.bean.report.DingExceptionCountReportBean">
        select * from(select distinct ss.name as humanSupplierName,sum(case when tbd.sign_in_time is null or tbd.sign_out_time is null then 1 else 0 end) as count
          from t_b_service tbs
                   join t_b_crm tbc on tbc.service_id = tbs.id
                   join t_b_sop_info tbsi on tbsi.crm_no = tbc.crm_no
                   join t_f_custom_flow_entity tfcfe on tfcfe.code = tbsi.sop_no
                   join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
                   join t_b_ding tbd on tbd.ding_sop_no = tbsi.sop_no
                   join sys_user su on su.id = tbd.user_id
                   join t_b_user_archives_supplier tbuas on tbuas.user_archives_id = tbd.user_archives_id
                   join sys_supplier ss on ss.id = tbuas.supplier_id
          where tbs.id = #{serviceId}
            and tbs.status <![CDATA[ <> ]]> 'CANCEL'
            and tffa.status <![CDATA[ <> ]]> 'END'
          group by ss.name) t where t.count > 0
        order by t.humanSupplierName
    </select>
</mapper>
