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

    <select id="query" resultType="com.qmth.sop.business.bean.result.TBQualityProblemApplyResult">
        SELECT
        distinct a.*,
        si.type sopType,
        sc.NAME custom,
        sc.type customType,
        fa.STATUS,
        fa.setup,
        fa.create_time submissionTime,
        fa.update_time updateDateTime,
        cu.real_name submitter,
        fe.flow_id as flowId,
        IFNULL(art.NAME_,'--') as taskName,
        art.TASK_DEF_KEY_ as taskDefKey,
        (select group_concat(cast(art.ID_ as char)) from ACT_RU_TASK art where art.PROC_INST_ID_ = fa.flow_id) as taskIds
        FROM
        t_b_quality_problem_apply a
        LEFT JOIN t_b_sop_info si on si.sop_no = a.sop_no
        LEFT JOIN sys_custom sc ON sc.id = si.custom_id
        LEFT JOIN t_f_custom_flow_entity fe ON 	fe.`code`= a.problem_no
        LEFT JOIN t_f_flow_approve fa ON fa.flow_id = fe.flow_id
        LEFT JOIN sys_user cu ON cu.id = fa.create_id
        left join ACT_RU_TASK art on art.PROC_INST_ID_ = fa.flow_id
        left join act_ru_identitylink ari on ari.TASK_ID_ = art.ID_
        left join t_b_crm tbc on tbc.crm_no = fe.crm_no
        left join t_b_service tbs on tbs.id = tbc.service_id
        <where>
            <if test="serviceId != null and serviceId != ''">
                and a.service_id = #{serviceId}
            </if>
            <if test="userId != null and userId != ''">
<!--                and a.user_ids = #{userId}-->
                and LOCATE(#{userId},a.user_ids)
            </if>
            <if test="type != null and type != ''">
                and a.type = #{type}
            </if>
            <if test="reason != null and reason != ''">
                and a.reason = #{reason}
            </if>
            <if test="degree != null and degree != ''">
                and a.influence_degree = #{degree}
            </if>
            <if test="custom != null and custom != ''">
                and sc.name like concat('%', #{custom} , '%')
            </if>
            <if test="problemNo != null and problemNo != ''">
                and a.problem_no like concat('%', #{problemNo} , '%')
            </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 tbs.service_lead_id = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionManager">
                                        AND tbc.lead_id = #{dpr.requestUserId}
                                    </when>
                                    <otherwise>
                                        <choose>
                                            <when test="dpr.hasRegionCoordinator">
                                                AND tbc.region_coordinator_id = #{dpr.requestUserId}
                                            </when>
                                            <otherwise>
                                                <choose>
                                                    <when test="dpr.hasEffectEngineer or dpr.hasAssistantEngineer">
                                                        AND EXISTS(SELECT 1 FROM t_b_user_archives_allocation tbuaa WHERE tbc.crm_no = tbuaa.crm_no AND tbuaa.user_id = #{dpr.requestUserId})
                                                    </when>
                                                </choose>
                                            </otherwise>
                                        </choose>
                                    </otherwise>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY a.problem_no desc,a.update_time desc
    </select>

    <select id="userList" resultType="com.qmth.sop.business.entity.SysUser">
        SELECT DISTINCT
        us1.*
        FROM
        sys_user us1,
        t_b_quality_problem_apply a
        WHERE
        find_in_set( us1.id, a.user_ids )
    </select>
</mapper>