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

    <select id="query" resultType="com.qmth.sop.business.bean.result.TBViolationResult">
        SELECT
        a.*,
        s.NAME service,
        u.real_name user_name,
        u.mobile_number,
        sc.NAME custom,
        sc.type custom_type,
        c.NAME crm_name,
        cu.real_name create_name
        FROM
        t_b_violation a
        LEFT JOIN sys_user u ON u.id = a.user_id
        LEFT JOIN sys_user cu ON cu.id = a.create_id
        LEFT JOIN t_b_crm c ON c.crm_no = a.crm_no
        LEFT JOIN sys_custom sc ON sc.id = c.custom_id
        LEFT JOIN t_b_service s ON s.id = a.service_id
        <where> 1 = 1
            and IF(s.`status` = 'PUBLISH', 1 = 1, 1 <![CDATA[ <> ]]> 1)
            <if test="userId != null and userId != ''">
                and a.user_id = #{userId}
            </if>
            <if test="createId != null and createId != ''">
                and a.create_id = #{createId}
            </if>
            <if test="type != null and type != ''">
                and a.type = #{type}
            </if>
            <if test="status != null and status != ''">
                and a.status = #{status}
            </if>
            <if test="serviceId != null and serviceId != ''">
                and a.service_id = #{serviceId}
            </if>
            <if test="custom != null and custom != ''">
                and sc.name like concat('%', #{custom} , '%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                and a.sop_no like concat('%', #{sopNo} , '%')
            </if>
            <if test="startTime != null and startTime != '' ">
                <![CDATA[
               and a.create_time >= #{startTime}
               ]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[
                and a.create_time <= #{endTime}
                ]]>
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasThirdPmo">
                        AND EXISTS (
                        SELECT
                        1
                        FROM
                        t_b_user_archives_allocation tbuaa
                        LEFT JOIN t_b_user_archives_supplier tbuas ON tbuaa.archives_id = tbuas.user_archives_id
                        WHERE
                        tbuas.supplier_id = #{dpr.supplierId} AND tbuaa.crm_no = a.crm_no)
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasBusiness">
                                AND s.service_lead_id = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionManager">
                                        AND c.lead_id = #{dpr.requestUserId}
                                    </when>
                                    <otherwise>
                                        <choose>
                                            <when test="dpr.hasRegionCoordinator">
                                                AND c.region_coordinator_id = #{dpr.requestUserId}
                                            </when>
                                        </choose>
                                    </otherwise>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY a.code DESC,a.update_time desc
    </select>

    <select id="getByCode" resultType="com.qmth.sop.business.bean.result.TBViolationResult">
        SELECT
        a.*,
        s.NAME service,
        u.real_name user_name,
        u.mobile_number,
        sc.NAME custom,
        sc.type custom_type,
        c.NAME crm_name,
        cu.real_name create_name
        FROM
        t_b_violation a
        LEFT JOIN sys_user u ON u.id = a.user_id
        LEFT JOIN sys_user cu ON cu.id = a.create_id
        LEFT JOIN t_b_crm c ON c.crm_no = a.crm_no
        LEFT JOIN sys_custom sc ON sc.id = c.custom_id
        LEFT JOIN t_b_service s ON s.id = a.service_id
        <where>
            <if test="code != null and code != ''">
                and a.code = #{code}
            </if>
        </where>
        ORDER BY a.create_time DESC
    </select>
</mapper>