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

    <select id="list" resultType="com.qmth.sop.business.bean.result.ProjectExchangeResult">
        select
            tbpe.id,
            tfcf.type,
            tfcfe.code,
            tbpe.sop_no as sopNo,
            tbpe.crm_no as crmNo,
            tbc.name as crmName,
            tbs.id as serviceId,
            tbs.name as serviceName,
            tbs.type as serviceType,
            sc.id as customId,
            sc.name as customName,
            sc.type as customType,
            tbpe.create_id as createId,
            su.real_name as createRealName,
            tbpe.create_time as createTime,
            tbpe.reason,
            tbpe.`type` as exchangeType,
            IF(tffa.status = 'FINISH','APPROVE','UN_APPROVE') as projectExchangeFlowStatus,
            tffa.setup,
            tfcf.version,
            tfcfe.flow_id as flowId,
            IFNULL(art.NAME_,'--') as taskName,
            art.TASK_DEF_KEY_ as taskDefKey,
            art.ID_ as taskId,
            tfcfe.flow_process_var as flowProcessVar,
            tfcfe.t_f_custom_flow_id as customFlowId,
            tfcf.flow_deployment_id as flowDeploymentId,
            tfcfe.id as customFlowEntityid,
            tffa.update_time as flowTime
--             IFNULL((select group_concat(us1.real_name SEPARATOR ';') from sys_user us1
--              where find_in_set(us1.id, (select tffl.pend_approve_id from t_f_flow_log tffl where tffl.flow_id = tfcfe.flow_id order by tffl.create_time desc limit 1))),'--') as approveUsersName
        from t_b_project_exchange tbpe
                 left join t_f_custom_flow_entity tfcfe on tfcfe.code = tbpe.exchange_no
                 left join t_f_flow_approve tffa on tffa.flow_id = tfcfe.flow_id
                 left join ACT_RU_TASK art on art.PROC_INST_ID_ = tffa.flow_id
                 left join t_f_custom_flow tfcf on tfcf.id = tfcfe.t_f_custom_flow_id
                 left join t_b_crm tbc on tbc.crm_no = tbpe.crm_no
                 left join t_b_service tbs on tbs.id = tbc.service_id
                 left join sys_custom sc on sc.id = tbc.custom_id
                 left join sys_user su ON tbpe.create_id = su.id
        <where> 1 = 1
            and IF(tbs.`status` = 'PUBLISH', 1 = 1, 1 <![CDATA[ <> ]]> 1)
            <if test="serviceId != null and serviceId != ''">
                and tbs.id = #{serviceId}
            </if>
            <if test="createUserId != null and createUserId != ''">
                and tbpe.create_id = #{createUserId}
            </if>
            <if test="type != null and type != ''">
                and tbpe.`type` = #{type}
            </if>
            <if test="customType != null and customType != ''">
                and sc.type = #{customType}
            </if>
            <if test="flowStatus != null and flowStatus != ''">
                <choose>
                    <when test="flowStatus == 'UN_APPROVE'">
                        and tffa.status <![CDATA[ <> ]]> 'FINISH'
                    </when>
                    <otherwise>
                        and tffa.status = 'FINISH'
                    </otherwise>
                </choose>
            </if>
            <if test="customName != null and customName != ''">
                and sc.name like concat('%', #{customName}, '%')
            </if>
            <if test="exchangeNo != null and exchangeNo != ''">
                and tbpe.exchange_no like concat('%', #{exchangeNo}, '%')
            </if>
            <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                and (tbpe.create_time <![CDATA[ >= ]]> #{startTime} or tbpe.create_time <![CDATA[ <= ]]> 1)
            </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 = tbpe.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>
                                        </choose>
                                    </otherwise>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        order by tbpe.exchange_no desc,tbpe.create_time desc
    </select>
</mapper>
