<?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
            sta.service_id AS serviceUnitId,
            tbs.name AS serviceUnitName,
            sta.sop_no AS sopNo,
            sta.crm_no AS crmNo,
            sta.custom_name AS customName,
            sta.province AS province,
            sta.city AS city,
            sta.user_id AS userId,
            sta.user_archives_id AS archivesId,
            sta.user_archives_code AS archivesCode,
            sta.user_archives_name AS archivesName,
            CONCAT(sta.user_archives_name,
                   '(',
                   sta.user_archives_code,
                   ')') AS archivesInfo,
            sta.scan_start_time AS scanStartTime,
            sta.scan_end_time AS scanEndTime,
            sta.mark_paper_start_time AS markPaperStartTime,
            sta.mark_paper_end_time AS markPaperEndTime,
            sta.role_name AS sopRoleTypeStr,
            sta.supplier_name AS supplierName,
            sta.actual_days AS actualDays,
            sta.weekdays AS weekdays,
            sta.weekends AS weekends,
            sta.legal_holidays AS legalHolidays,
            sta.violation_days AS violationDays,
            sta.work_hours AS workHours,
            sta.ding_exception_count AS dingExceptionCount,
            sta.remain_count AS remainCount,
            sta.exception_count AS exceptionCount,
            IFNULL(tbds.submit_status, 'WILL_SUBMIT') AS submitStatus,
            tbds.id AS submitId,
            tbds.submit_user_id AS submitUserId,
            su.real_name AS submitUserName,
            tbds.submit_time AS submitTime,
            tbs.update_time AS serviceUpdateTime,
            tbc.update_time AS crmUpateTime
        FROM
            t_b_ding_statistic sta
                LEFT JOIN
            t_b_service tbs ON sta.service_id = tbs.id
                LEFT JOIN
            t_b_crm tbc ON sta.crm_no = tbc.crm_no
                LEFT JOIN
            t_b_ding_submit tbds
                ON sta.service_id = tbds.service_id
                AND sta.sop_no = tbds.sop_no
                AND sta.user_archives_id = tbds.user_archives_id
                LEFT JOIN
            sys_user su ON tbds.submit_user_id = su.id
        <where>
            AND tbs.status = 'PUBLISH' AND sta.sop_no IS NOT NULL
            <if test="serviceUnitId != null">
                AND sta.service_id = #{serviceUnitId}
            </if>
            <if test="dingSubmitStatusList != null and dingSubmitStatusList != '' and dingSubmitStatusList.size > 0">
                AND IFNULL(tbds.submit_status, 'WILL_SUBMIT') IN
                <foreach collection="dingSubmitStatusList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="submitUserId != null">
                AND tbds.submit_user_id = #{serviceUnitId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND sta.user_archives_name LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="supplierId != null">
                AND sta.supplier_id = #{supplierId}
            </if>
            <if test="customName != null and customName != ''">
                AND sta.custom_name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                AND sta.sop_no = #{sopNo}
            </if>
            <if test="violationHoursLimit != null">
                AND sta.violation_days > #{violationHoursLimit}
            </if>
            <if test="dingExceptionLimit != null">
                AND sta.ding_exception_count > #{dingExceptionLimit}
            </if>
            <if test="exceptionLimit != null">
                AND sta.exception_count > #{exceptionLimit}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND tbc.lead_id = #{dpr.requestUserId}
                            </when>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        UNION ALL
        SELECT
            sta.service_id AS serviceUnitId,
            tbs.name AS serviceUnitName,
            sta.sop_no AS sopNo,
            sta.crm_no AS crmNo,
            sta.custom_name AS customName,
            sta.province AS province,
            sta.city AS city,
            sta.user_id AS userId,
            sta.user_archives_id AS archivesId,
            sta.user_archives_code AS archivesCode,
            sta.user_archives_name AS archivesName,
            CONCAT(sta.user_archives_name,
            '(',
            sta.user_archives_code,
            ')') AS archivesInfo,
            sta.scan_start_time AS scanStartTime,
            sta.scan_end_time AS scanEndTime,
            sta.mark_paper_start_time AS markPaperStartTime,
            sta.mark_paper_end_time AS markPaperEndTime,
            sta.role_name AS sopRoleTypeStr,
            sta.supplier_name AS supplierName,
            sta.actual_days AS actualDays,
            sta.weekdays AS weekdays,
            sta.weekends AS weekends,
            sta.legal_holidays AS legalHolidays,
            sta.violation_days AS violationDays,
            sta.work_hours AS workHours,
            sta.ding_exception_count AS dingExceptionCount,
            sta.remain_count AS remainCount,
            sta.exception_count AS exceptionCount,
            IFNULL(tbds.submit_status, 'WILL_SUBMIT') AS submitStatus,
            tbds.id AS submitId,
            tbds.submit_user_id AS submitUserId,
            su.real_name AS submitUserName,
            tbds.submit_time AS submitTime,
            tbs.update_time AS serviceUpdateTime,
            tbc.update_time AS crmUpateTime
        FROM
            t_b_ding_statistic sta
                LEFT JOIN
            t_b_service tbs ON sta.service_id = tbs.id
                LEFT JOIN
            t_b_crm tbc ON sta.crm_no = tbc.crm_no
                LEFT JOIN
            t_b_ding_submit tbds ON sta.service_id = tbds.service_id
                AND tbds.sop_no IS NULL
                AND sta.user_archives_id = tbds.user_archives_id
                LEFT JOIN
            sys_user su ON tbds.submit_user_id = su.id
        <where>
            AND tbs.status = 'PUBLISH' AND sta.sop_no IS NULL
            <if test="serviceUnitId != null">
                AND sta.service_id = #{serviceUnitId}
            </if>
            <if test="dingSubmitStatusList != null and dingSubmitStatusList != '' and dingSubmitStatusList.size > 0">
                AND IFNULL(tbds.submit_status, 'WILL_SUBMIT') IN
                <foreach collection="dingSubmitStatusList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="submitUserId != null">
                AND tbds.submit_user_id = #{serviceUnitId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND sta.user_archives_name LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="supplierId != null">
                AND sta.supplier_id = #{supplierId}
            </if>
            <if test="customName != null and customName != ''">
                AND sta.custom_name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                AND sta.sop_no = #{sopNo}
            </if>
            <if test="violationHoursLimit != null">
                AND sta.violation_days > #{violationHoursLimit}
            </if>
            <if test="dingExceptionLimit != null">
                AND sta.ding_exception_count > #{dingExceptionLimit}
            </if>
            <if test="exceptionLimit != null">
                AND sta.exception_count > #{exceptionLimit}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND tbc.lead_id = #{dpr.requestUserId}
                            </when>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY serviceUpdateTime DESC , crmUpateTime DESC
    </select>

    <select id="findDingSubmitList" resultType="com.qmth.sop.business.bean.result.DingSubmitResult">
        SELECT
            sta.service_id AS serviceUnitId,
            tbs.name AS serviceUnitName,
            sta.sop_no AS sopNo,
            sta.crm_no AS crmNo,
            sta.custom_name AS customName,
            sta.province AS province,
            sta.city AS city,
            sta.user_id AS userId,
            sta.user_archives_id AS archivesId,
            sta.user_archives_code AS archivesCode,
            sta.user_archives_name AS archivesName,
            CONCAT(sta.user_archives_name,
                   '(',
                   sta.user_archives_code,
                   ')') AS archivesInfo,
            sta.scan_start_time AS scanStartTime,
            sta.scan_end_time AS scanEndTime,
            sta.mark_paper_start_time AS markPaperStartTime,
            sta.mark_paper_end_time AS markPaperEndTime,
            sta.role_name AS sopRoleTypeStr,
            sta.supplier_name AS supplierName,
            sta.actual_days AS actualDays,
            sta.weekdays AS weekdays,
            sta.weekends AS weekends,
            sta.legal_holidays AS legalHolidays,
            sta.violation_days AS violationDays,
            sta.work_hours AS workHours,
            sta.ding_exception_count AS dingExceptionCount,
            sta.remain_count AS remainCount,
            sta.exception_count AS exceptionCount,
            IFNULL(tbds.submit_status, 'WILL_SUBMIT') AS submitStatus,
            tbds.id AS submitId,
            tbds.submit_user_id AS submitUserId,
            su.real_name AS submitUserName,
            tbds.submit_time AS submitTime,
            tbs.update_time AS serviceUpdateTime,
            tbc.update_time AS crmUpateTime
        FROM
            t_b_ding_statistic sta
                LEFT JOIN
            t_b_service tbs ON sta.service_id = tbs.id
                LEFT JOIN
            t_b_crm tbc ON sta.crm_no = tbc.crm_no
                LEFT JOIN
            t_b_ding_submit tbds ON sta.service_id = tbds.service_id
                AND sta.sop_no = tbds.sop_no
                AND sta.user_archives_id = tbds.user_archives_id
                LEFT JOIN
            sys_user su ON tbds.submit_user_id = su.id
        <where>
            AND tbs.status = 'PUBLISH' AND sta.sop_no IS NOT NULL
            <if test="serviceUnitId != null">
                AND sta.service_id = #{serviceUnitId}
            </if>
            <if test="dingSubmitStatusList != null and dingSubmitStatusList != '' and dingSubmitStatusList.size > 0">
                AND IFNULL(tbds.submit_status, 'WILL_SUBMIT') IN
                <foreach collection="dingSubmitStatusList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="submitUserId != null">
                AND tbds.submit_user_id = #{serviceUnitId}
            </if>
            <if test="archivesId != null">
                AND sta.user_archives_id = #{archivesId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND sta.user_archives_name LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="supplierId != null">
                AND sta.supplier_id = #{supplierId}
            </if>
            <if test="customName != null and customName != ''">
                AND sta.custom_name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                AND sta.sop_no = #{sopNo}
            </if>
            <if test="violationHoursLimit != null">
                AND sta.violation_days > #{violationHoursLimit}
            </if>
            <if test="dingExceptionLimit != null">
                AND sta.ding_exception_count > #{dingExceptionLimit}
            </if>
            <if test="exceptionLimit != null">
                AND sta.exception_count > #{exceptionLimit}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND tbc.lead_id = #{dpr.requestUserId}
                            </when>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        UNION ALL
        SELECT
            sta.service_id AS serviceUnitId,
            tbs.name AS serviceUnitName,
            sta.sop_no AS sopNo,
            sta.crm_no AS crmNo,
            sta.custom_name AS customName,
            sta.province AS province,
            sta.city AS city,
            sta.user_id AS userId,
            sta.user_archives_id AS archivesId,
            sta.user_archives_code AS archivesCode,
            sta.user_archives_name AS archivesName,
            CONCAT(sta.user_archives_name,
            '(',
            sta.user_archives_code,
            ')') AS archivesInfo,
            sta.scan_start_time AS scanStartTime,
            sta.scan_end_time AS scanEndTime,
            sta.mark_paper_start_time AS markPaperStartTime,
            sta.mark_paper_end_time AS markPaperEndTime,
            sta.role_name AS sopRoleTypeStr,
            sta.supplier_name AS supplierName,
            sta.actual_days AS actualDays,
            sta.weekdays AS weekdays,
            sta.weekends AS weekends,
            sta.legal_holidays AS legalHolidays,
            sta.violation_days AS violationDays,
            sta.work_hours AS workHours,
            sta.ding_exception_count AS dingExceptionCount,
            sta.remain_count AS remainCount,
            sta.exception_count AS exceptionCount,
            IFNULL(tbds.submit_status, 'WILL_SUBMIT') AS submitStatus,
            tbds.id AS submitId,
            tbds.submit_user_id AS submitUserId,
            su.real_name AS submitUserName,
            tbds.submit_time AS submitTime,
            tbs.update_time AS serviceUpdateTime,
            tbc.update_time AS crmUpateTime
        FROM
            t_b_ding_statistic sta
                LEFT JOIN
            t_b_service tbs ON sta.service_id = tbs.id
                LEFT JOIN
            t_b_crm tbc ON sta.crm_no = tbc.crm_no
                LEFT JOIN
            t_b_ding_submit tbds ON sta.service_id = tbds.service_id
                AND tbds.sop_no IS NULL
                AND sta.user_archives_id = tbds.user_archives_id
                LEFT JOIN
            sys_user su ON tbds.submit_user_id = su.id
        <where>
            AND tbs.status = 'PUBLISH' AND sta.sop_no IS NULL
            <if test="serviceUnitId != null">
                AND sta.service_id = #{serviceUnitId}
            </if>
            <if test="dingSubmitStatusList != null and dingSubmitStatusList != '' and dingSubmitStatusList.size > 0">
                AND IFNULL(tbds.submit_status, 'WILL_SUBMIT') IN
                <foreach collection="dingSubmitStatusList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="submitUserId != null">
                AND tbds.submit_user_id = #{serviceUnitId}
            </if>
            <if test="archivesId != null">
                AND sta.user_archives_id = #{archivesId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND sta.user_archives_name LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="supplierId != null">
                AND sta.supplier_id = #{supplierId}
            </if>
            <if test="customName != null and customName != ''">
                AND sta.custom_name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                AND sta.sop_no = #{sopNo}
            </if>
            <if test="violationHoursLimit != null">
                AND sta.violation_days > #{violationHoursLimit}
            </if>
            <if test="dingExceptionLimit != null">
                AND sta.ding_exception_count > #{dingExceptionLimit}
            </if>
            <if test="exceptionLimit != null">
                AND sta.exception_count > #{exceptionLimit}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND tbc.lead_id = #{dpr.requestUserId}
                            </when>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY serviceUpdateTime DESC , crmUpateTime DESC
    </select>
</mapper>