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

    <select id="listDelayWarn" resultType="com.qmth.sop.business.bean.result.SopWarnMonitorDetailResult">
        SELECT
        tbdw.crm_no AS crmNo,
        tbdw.sop_no AS sopNo,
        tbdw.field_obj AS fieldObj,
        tbc.lead_id AS leadId,
        su.real_name AS leadName
        FROM
        t_b_delay_warn tbdw
        LEFT JOIN t_b_crm tbc ON tbdw.crm_no = tbc.crm_no
        INNER JOIN t_b_crm_detail tbcd on tbcd.crm_no = tbdw.crm_no and tbcd.sop_no = tbdw.sop_no
        LEFT JOIN sys_user su ON su.id = tbc.lead_id
        WHERE
        tbdw.service_id = #{serviceUnitId}
        <if test="warnFieldList != null and warnFieldList.size() > 0">
            AND tbdw.field_obj IN
            <foreach collection="warnFieldList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="listDelayWarnByUserRole" resultType="com.qmth.sop.business.bean.result.SopWarnMonitorDetailResult">
        SELECT  tbdw.crm_no AS crmNo,
        tbdw.sop_no AS sopNo,
        tbdw.field_obj AS fieldObj,
        ss.id AS supplierId,
        ss.NAME AS supplierName,
        tbaa.user_id AS coordinatorId,
        su.real_name AS coordinatorName
        FROM t_b_delay_warn tbdw
        LEFT JOIN t_b_crm tbc ON tbc.crm_no = tbdw.crm_no
        LEFT JOIN t_b_crm_detail tbcd ON tbcd.crm_no = tbc.crm_no AND tbcd.sop_no = tbdw.sop_no
        LEFT JOIN t_b_user_archives_allocation tbaa ON tbaa.crm_detail_id = tbcd.id AND tbdw.user_id = tbaa.user_id
        LEFT JOIN t_b_user_archives_supplier tbusa ON tbusa.user_archives_id = tbaa.archives_id
        LEFT JOIN sys_supplier ss ON ss.id = tbusa.supplier_id
        LEFT JOIN sys_user su ON su.id = tbaa.user_id
        WHERE tbdw.service_id = #{serviceUnitId}
        <if test="sopRoleType != null">
            AND tbaa.sop_role_type = #{sopRoleType}
        </if>
        <if test="warnFieldList != null and warnFieldList.size() > 0">
            AND tbdw.field_obj IN
            <foreach collection="warnFieldList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>
</mapper>
