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

    <select id="progress" resultType="java.util.Map">
        SELECT
        DISTINCT cast(r.id as char) as region_id,
        r.region_name,
        cast(su.id as char) as supplier_id,
        su.`name`,
        tbsi.sop_no,
        tffa.status,
        tffa.setup
        FROM t_b_sop_info tbsi
--         LEFT JOIN t_b_sop_info_detail sd ON sd.sop_info_id = tbsi.id
--         LEFT JOIN sys_user su2 on su2.id = sd.engineer_user_id
        LEFT JOIN sys_custom sc ON sc.id = tbsi.custom_id
        LEFT JOIN t_b_user_archives_allocation tbuaa on tbuaa.crm_no = tbsi.crm_no
        LEFT JOIN t_f_custom_flow_entity tfcfe ON tfcfe.CODE = tbsi.sop_no
        LEFT JOIN t_f_flow_approve tffa ON tffa.flow_id = tfcfe.flow_id
        LEFT JOIN t_b_crm c on c.crm_no = tbsi.crm_no
        LEFT JOIN t_b_service_region r on r.id = c.region_id
        LEFT JOIN t_b_user_archives ua ON ua.mobile_number = su2.mobile_number
        LEFT JOIN t_b_user_archives_supplier tbuas on tbuas.user_archives_id = ua.id
        LEFT JOIN sys_supplier su on su.id = tbuas.supplier_id
        <where> 1 = 1 and tffa.status != 'END'
            <if test="serviceId != null and serviceId != ''">
                and tbsi.service_id= #{serviceId}
            </if>
        </where>
    </select>

    <select id="detail" resultType="java.util.Map">
        select DISTINCT
        cast(tbsi.id as char) as id,
        cast(tbs.id as char) as serviceId,
        tbs.name as serviceName,
        tbsi.sop_no as sopNo,
        tbsi.crm_no as crmNo,
        tbc.begin_time as beginTime,
        cast(tbc.crm_user_id as char) as customManagerId,
        su1.real_name as customManagerName,
        sc.type as customManagerType,
        IF(sc.type = 'OFFICE','教务处','研究生') as customManagerTypeStr,
        sc.name as customName,
        tbc.name as crmName,
        cast(tbp.id as char) as productId,
        tbp.name as productName,
        tbc.exam_start_time as examStartTime,
        tbc.exam_end_time as examEndTime,
        cast(tfcfe.flow_id as char) as flowId,
        cast(tfcfe.create_id as char) as flowCreateId,
        su2.real_name as flowCreateName,
        tffa.create_time as flowCreateTime,
        tffa.update_time as flowUpdateTime,
        tffa.status,
        CASE
        WHEN tffa.status = 'FINISH' THEN '已完成'
        WHEN tffa.status = 'END' THEN '已作废'
        ELSE '进行中'
        END as statusStr,
        tfcf.type,
        IF(tfcf.type = 'OFFICE_SOP_FLOW','教务处SOP','研究生SOP') as typeStr,
        tfcf.version,
        cast(tfcf.flow_deployment_id as char) as flowDeploymentId,
        IFNULL(art.NAME_,'--') as taskName,
        art.TASK_DEF_KEY_ as taskDefKey
        FROM t_b_sop_info tbsi
        LEFT JOIN t_b_service tbs ON tbs.id = tbsi.service_id
        LEFT JOIN sys_custom sc ON sc.id = tbsi.custom_id
        LEFT JOIN t_b_user_archives_allocation tbuaa ON tbuaa.crm_no = tbsi.crm_no
        LEFT JOIN t_f_custom_flow_entity tfcfe ON tfcfe.CODE = tbsi.sop_no
        LEFT JOIN t_f_custom_flow tfcf ON tfcf.id = tfcfe.t_f_custom_flow_id
        LEFT JOIN t_f_flow_approve tffa ON tffa.flow_id = tfcfe.flow_id
        LEFT JOIN ACT_RU_TASK art ON art.PROC_INST_ID_ = tffa.flow_id
        LEFT JOIN act_ru_identitylink ari ON ari.TASK_ID_ = art.ID_
        LEFT JOIN t_b_crm tbc ON tbc.crm_no = tbsi.crm_no
        LEFT JOIN t_b_service_region r ON r.id = tbc.region_id
        LEFT JOIN t_b_user_archives ua ON ua.id = tbuaa.archives_id
        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_product tbp ON tbp.id = tbc.product_id
        LEFT JOIN sys_user su1 ON su1.id = tbc.crm_user_id
        LEFT JOIN sys_user su2 ON su2.id = tfcfe.create_id
        <where>
            and tffa.status != 'END'
            <if test="serviceId != null and serviceId != ''">
                and tbsi.service_id = #{serviceId}
            </if>
            <if test="regionId != null and regionId != ''">
                and r.id = #{regionId}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
        </where>
    </select>

    <select id="actualProgress" resultType="java.util.Map">
        SELECT
        DATE_FORMAT(
        FROM_UNIXTIME(
        ( CASE WHEN d.sign_in_time IS NOT NULL THEN d.sign_in_time WHEN d.sign_out_time IS NOT NULL THEN d.sign_out_time
        ELSE NULL END )/ 1000
        ),
        '%Y-%m-%d'
        ) datetime,
        count(DISTINCT d.user_id ) count
        FROM
        t_b_ding d
        <where>
            and (d.sign_in_time is not null or d.sign_out_time is not null)
            <if test="serviceId != null and serviceId != ''">
                and d.service_id= #{serviceId}
            </if>
        </where>
        GROUP BY
        datetime
    </select>
</mapper>
