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

    <select id="findDingSubmitPage" resultType="com.qmth.sop.business.bean.result.DingSubmitResult">
        SELECT
            *
        FROM
            (SELECT
                 tbd.service_id AS serviceUnitId,
                 tbs.name AS serviceUnitName,
                 tbd.sop_no AS sopNo,
                 tbd.crm_no AS crmNo,
                 sc.name AS customName,
                 sc.province AS province,
                 sc.city AS city,
                 tbd.approach_time AS approachTime,
                 tbd.departure_time AS departureTime,
                 tbd.user_id AS userId,
                 tbua.id AS archivesId,
                 tbua.code AS archivesCode,
                 tbua.name AS archivesName,
                 CONCAT(tbua.name, '(', tbua.code, ')') AS archivesInfo,
                 tbd.role_type AS roleType,
                 ss.id AS supplierId,
                 ss.name AS supplierName,
                 tbd.actual_days AS actualDays,
                 tbd.weekdays AS weekdays,
                 tbd.weekends AS weekends,
                 tbd.legal_holidays AS legalHolidays,
                 tbd.work_hours AS workHours,
                 tbd.violation_days AS violationDays,
                 IFNULL(ec.cnt,0) AS exceptionCount,
                 tbds.id AS submitId,
                 IFNULL(tbds.submit_status, 'WILL_SUBMIT') AS submitStatus,
                 tbds.submit_user_id AS submitUserId,
                 su.real_name AS submitUserName,
                 tbds.submit_time AS submitTime,
                 tbs.service_lead_id AS serviceUnitLeaderId,
                 tbc.lead_id AS regionManagerId,
                 tbc.region_coordinator_id AS coordinatorId,
                 tbs.update_time AS serviceUpdateTime,
                 tbs.status AS serviceStatus,
                 tbc.update_time AS crmUpdateTime,
                 tbd.sign_in_exception_count + tbd.sign_out_exception_count AS dingExceptionCount
             FROM
                 (SELECT
                      service_id,
                      sop_no,
                      crm_no,
                      user_archives_id,
                      MAX(user_id) AS user_id,
                      MAX(role_type) AS role_type,
                      MIN(sign_in_time) AS approach_time,
                      MAX(sign_out_time) AS departure_time,
                      SUM(IF((sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS actual_days,
                      SUM(IF(date_type = 'WEEKDAY' AND (sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS weekdays,
                      SUM(IF(date_type = 'WEEKEND' AND (sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS weekends,
                      SUM(IF(date_type = 'LEGAL_HOLIDAYS' AND (sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS legal_holidays,
                      ROUND(SUM(IF(sign_out_time IS NOT NULL AND sign_in_time IS NOT NULL, TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(SUBSTRING(sign_in_time, 1, 10), '%Y-%m-%d %H:%i:%s'), FROM_UNIXTIME(SUBSTRING(sign_out_time, 1, 10), '%Y-%m-%d %H:%i:%s')), 0)) / 60, 1) AS work_hours,
                      SUM(IF((sign_in_time IS NULL OR sign_out_time IS NULL) AND (sign_in_time IS NOT NULL OR sign_out_time IS NOT NULL), 1, 0)) AS violation_days,
                      SUM(IF(sign_in_time IS NULL, 1, 0)) AS sign_in_exception_count,
                      SUM(IF(sign_out_time IS NULL, 1, 0)) AS sign_out_exception_count
                  FROM
                      t_b_ding
                  GROUP BY service_id , sop_no , crm_no , user_archives_id) tbd
                     LEFT JOIN (SELECT
                                    COUNT(*) AS cnt, sop_no, crm_no, create_id
                                FROM
                                    t_b_ding_apply
                                GROUP BY sop_no , crm_no , create_id) sec ON sec.sop_no = tbd.sop_no
                     AND sec.crm_no = tbd.crm_no
                     AND sec.create_id = tbd.user_id
                     LEFT JOIN (SELECT
                                    COUNT(*) AS cnt, sop_no, crm_no, create_id
                                FROM
                                    t_b_ding_apply
                                WHERE approve IS NULL
                                GROUP BY sop_no , crm_no , create_id) ec ON ec.sop_no = tbd.sop_no
                     AND ec.crm_no = tbd.crm_no
                     AND ec.create_id = tbd.user_id
                     LEFT JOIN t_b_ding_submit tbds ON tbd.sop_no = tbds.sop_no
                     AND tbd.user_archives_id = tbds.user_archives_id
                     LEFT JOIN t_b_service tbs ON tbd.service_id = tbs.id AND tbs.enable
                     LEFT JOIN t_b_crm tbc ON tbd.crm_no = tbc.crm_no
                     LEFT JOIN sys_custom sc ON tbc.custom_id = sc.id
                     LEFT JOIN t_b_user_archives tbua ON tbd.user_archives_id = tbua.id
                     LEFT JOIN t_b_user_archives_supplier tbuas ON tbuas.user_archives_id = tbua.id
                     LEFT JOIN sys_supplier ss ON tbuas.supplier_id = ss.id
                     LEFT JOIN sys_user su ON tbds.submit_user_id = su.id
                     LEFT JOIN t_f_custom_flow_entity tfcfe ON tfcfe.code = tbd.sop_no
                     LEFT JOIN t_f_flow_approve tffa ON tffa.flow_id = tfcfe.flow_id
                     ) main
        <where>
            AND main.serviceStatus = 'PUBLISH'
            <if test="serviceUnitId != null">
                AND main.serviceUnitId = #{serviceUnitId}
            </if>
            <if test="dingSubmitStatusList != null and dingSubmitStatusList != '' and dingSubmitStatusList.size > 0">
                AND main.submitStatus IN
                <foreach collection="dingSubmitStatusList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="submitUserId != null">
                AND main.submitUserId = #{serviceUnitId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND main.archivesName LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="supplierId != null">
                AND main.supplierId = #{supplierId}
            </if>
            <if test="customName != null and customName != ''">
                AND main.customName LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                AND main.sopNo = #{sopNo}
            </if>
            <if test="violationHoursLimit != null">
                AND main.violationDays > #{violationHoursLimit}
            </if>
            <if test="dingExceptionLimit != null">
                AND main.dingExceptionCount > #{dingExceptionLimit}
            </if>
            <if test="exceptionLimit != null">
                AND main.exceptionCount > #{exceptionLimit}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND main.serviceUnitLeaderId = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND main.regionManagerId = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND main.coordinatorId = #{dpr.requestUserId}
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY main.serviceUpdateTime DESC,main.crmUpdateTime DESC
    </select>

    <select id="findDingSubmitList" resultType="com.qmth.sop.business.bean.result.DingSubmitResult">
        SELECT
            *
        FROM
            (SELECT
                 tbd.service_id AS serviceUnitId,
                 tbs.name AS serviceUnitName,
                 tbd.sop_no AS sopNo,
                 tbd.crm_no AS crmNo,
                 sc.name AS customName,
                 sc.province AS province,
                 sc.city AS city,
                 tbd.approach_time AS approachTime,
                 tbd.departure_time AS departureTime,
                 tbd.user_id AS userId,
                 tbua.id AS archivesId,
                 tbua.code AS archivesCode,
                 tbua.name AS archivesName,
                 CONCAT(tbua.name, '(', tbua.code, ')') AS archivesInfo,
                 tbd.role_type AS roleType,
                 ss.id AS supplierId,
                 ss.name AS supplierName,
                 tbd.actual_days AS actualDays,
                 tbd.weekdays AS weekdays,
                 tbd.weekends AS weekends,
                 tbd.legal_holidays AS legalHolidays,
                 tbd.work_hours AS workHours,
                 tbd.violation_days AS violationDays,
                 IFNULL(ec.cnt,0) AS exceptionCount,
                 tbds.id AS submitId,
                 IFNULL(tbds.submit_status, 'WILL_SUBMIT') AS submitStatus,
                 tbds.submit_user_id AS submitUserId,
                 su.real_name AS submitUserName,
                 tbds.submit_time AS submitTime,
                 tbs.service_lead_id AS serviceUnitLeaderId,
                 tbc.lead_id AS regionManagerId,
                 tbc.region_coordinator_id AS coordinatorId,
                 tbs.update_time AS serviceUpdateTime,
                 tbs.status AS serviceStatus,
                 tbc.update_time AS crmUpdateTime,
                 tbd.sign_in_exception_count + tbd.sign_out_exception_count AS dingExceptionCount
             FROM
                 (SELECT
                      service_id,
                      sop_no,
                      crm_no,
                      user_archives_id,
                      MAX(user_id) AS user_id,
                      MAX(role_type) AS role_type,
                      MIN(sign_in_time) AS approach_time,
                      MAX(sign_out_time) AS departure_time,
                      SUM(IF((sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS actual_days,
                      SUM(IF(date_type = 'WEEKDAY' AND (sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS weekdays,
                      SUM(IF(date_type = 'WEEKEND' AND (sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS weekends,
                      SUM(IF(date_type = 'LEGAL_HOLIDAYS' AND (sign_in_time IS NOT NULL AND sign_out_time IS NOT NULL), 1, 0)) AS legal_holidays,
                      ROUND(SUM(IF(sign_out_time IS NOT NULL AND sign_in_time IS NOT NULL, TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(SUBSTRING(sign_in_time, 1, 10), '%Y-%m-%d %H:%i:%s'), FROM_UNIXTIME(SUBSTRING(sign_out_time, 1, 10), '%Y-%m-%d %H:%i:%s')), 0)) / 60, 1) AS work_hours,
                      SUM(IF((sign_in_time IS NULL OR sign_out_time IS NULL) AND (sign_in_time IS NOT NULL OR sign_out_time IS NOT NULL), 1, 0)) AS violation_days,
                      SUM(IF(sign_in_time IS NULL, 1, 0)) AS sign_in_exception_count,
                      SUM(IF(sign_out_time IS NULL, 1, 0)) AS sign_out_exception_count
                  FROM
                      t_b_ding
                  GROUP BY service_id , sop_no , crm_no , user_archives_id) tbd
                     LEFT JOIN (SELECT
                                    COUNT(*) AS cnt, sop_no, crm_no, create_id
                                FROM
                                    t_b_ding_apply
                                GROUP BY sop_no , crm_no , create_id) sec ON sec.sop_no = tbd.sop_no
                     AND sec.crm_no = tbd.crm_no
                     AND sec.create_id = tbd.user_id
                     LEFT JOIN (SELECT
                                    COUNT(*) AS cnt, sop_no, crm_no, create_id
                                FROM
                                    t_b_ding_apply
                                WHERE approve IS NULL
                                GROUP BY sop_no , crm_no , create_id) ec ON ec.sop_no = tbd.sop_no
                     AND ec.crm_no = tbd.crm_no
                     AND ec.create_id = tbd.user_id
                     LEFT JOIN t_b_ding_submit tbds ON tbd.sop_no = tbds.sop_no
                     AND tbd.user_archives_id = tbds.user_archives_id
                     LEFT JOIN t_b_service tbs ON tbd.service_id = tbs.id AND tbs.enable
                     LEFT JOIN t_b_crm tbc ON tbd.crm_no = tbc.crm_no
                     LEFT JOIN sys_custom sc ON tbc.custom_id = sc.id
                     LEFT JOIN t_b_user_archives tbua ON tbd.user_archives_id = tbua.id
                     LEFT JOIN t_b_user_archives_supplier tbuas ON tbuas.user_archives_id = tbua.id
                     LEFT JOIN sys_supplier ss ON tbuas.supplier_id = ss.id
                     LEFT JOIN sys_user su ON tbds.submit_user_id = su.id
                     LEFT JOIN t_f_custom_flow_entity tfcfe ON tfcfe.code = tbd.sop_no
                     LEFT JOIN t_f_flow_approve tffa ON tffa.flow_id = tfcfe.flow_id
                ) main
        <where>
            AND main.serviceStatus = 'PUBLISH'
            <if test="serviceUnitId != null">
                AND main.serviceUnitId = #{serviceUnitId}
            </if>
            <if test="dingSubmitStatusList != null and dingSubmitStatusList != '' and dingSubmitStatusList.size > 0">
                AND main.submitStatus IN
                <foreach collection="dingSubmitStatusList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="submitUserId != null">
                AND main.submitUserId = #{serviceUnitId}
            </if>
            <if test="archivesId != null">
                AND main.archivesId = #{archivesId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND main.archivesName LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="supplierId != null">
                AND main.supplierId = #{supplierId}
            </if>
            <if test="customName != null and customName != ''">
                AND main.customName LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                AND main.sopNo = #{sopNo}
            </if>
            <if test="violationHoursLimit != null">
                AND main.violationDays > #{violationHoursLimit}
            </if>
            <if test="dingExceptionLimit != null">
                AND main.dingExceptionCount > #{dingExceptionLimit}
            </if>
            <if test="exceptionLimit != null">
                AND main.exceptionCount > #{exceptionLimit}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND main.serviceUnitLeaderId = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND main.regionManagerId = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND main.coordinatorId = #{dpr.requestUserId}
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY main.serviceUpdateTime DESC,main.crmUpdateTime DESC
    </select>
</mapper>