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

    <select id="findServiceUnitPage" resultType="com.qmth.sop.business.bean.result.ServiceUnitResult">
        SELECT
            tbs.id,
            tbs.name,
            tbs.start_time AS startTime,
            tbs.end_time AS endTime,
            tbs.type,
            tbs.service_lead_id AS serviceLeadId,
            leader.real_name AS serviceLeadName,
            tbs.region_people_count AS regionPeopleCount,
            tbs.region_project_count AS regionProjectCount,
            CONCAT(tbs.region_people_count,
                   ':',
                   tbs.region_project_count) AS rate,
            tbs.status,
            creator.real_name AS creatorName,
            tbs.create_time AS createTime
        FROM
            t_b_service tbs
                LEFT JOIN
            sys_user leader ON tbs.service_lead_id = leader.id
                LEFT JOIN
            sys_user creator ON tbs.create_id = creator.id
        <where>
            AND tbs.enable AND tbs.status != 'CANCEL'
            <if test="type != null">
                AND tbs.type = #{type}
            </if>
            <if test="serviceLeadId != null">
                AND tbs.service_lead_id = #{serviceLeadId}
            </if>
            <if test="status != null">
                AND tbs.status = #{status}
            </if>
            <if test="createStartTime != null">
                AND tbs.create_time >= #{createStartTime}
            </if>
            <if test="createEndTime != null">
                AND  #{createEndTime} >= tbs.create_time
            </if>
            <if test="serviceUnitName != null and serviceUnitName != ''">
                AND tbs.name LIKE CONCAT('%',#{serviceUnitName},'%')
            </if>
        </where>
        ORDER BY tbs.update_time DESC
    </select>

    <select id="queryServiceUnit" resultType="com.qmth.sop.business.entity.TBService">
        SELECT
            tbs.*
        FROM
            t_b_service tbs
        <where>
            <if test="type != null">
                AND tbs.type = #{type}
            </if>
            <if test="name != null and name != ''">
                AND tbs.name LIKE CONCAT('%',#{name},'%')
            </if>
            <if test="statusList != null and statusList != '' and statusList.size > 0">
                AND tbs.status IN
                <foreach collection="statusList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </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.service_id = tbs.id)
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasBusiness">
                                AND tbs.service_lead_id = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionManager">
                                        AND EXISTS(SELECT 1 FROM t_b_crm tbc WHERE tbs.id = tbc.service_id AND tbc.lead_id = #{dpr.requestUserId})
                                    </when>
                                    <otherwise>
                                        <choose>
                                            <when test="dpr.hasRegionCoordinator">
                                                AND EXISTS(SELECT 1 FROM t_b_crm tbc WHERE tbs.id = tbc.service_id 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 tbuaa.service_id = tbs.id AND tbuaa.user_id = #{dpr.requestUserId})
                                                    </when>
                                                </choose>
                                            </otherwise>
                                        </choose>
                                    </otherwise>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY tbs.update_time DESC
    </select>
</mapper>
