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

    <select id="findServiceRegionPage" resultType="com.qmth.sop.business.bean.result.ServiceRegionResult">
        SELECT
            tbsr.id AS serviceRegionId,
            tbs.id AS serviceUnitId,
            tbs.name AS serviceUnitName,
            tbsr.region_name AS regionName,
            leader.id AS leadId,
            leader.real_name AS leadName,
            tbs.status AS serviceUnitStatus
        FROM
            t_b_service_region tbsr
                LEFT JOIN
            t_b_service tbs ON tbsr.service_id = tbs.id
                LEFT JOIN
            sys_user leader ON tbsr.lead_id = leader.id
        <where>
            AND tbsr.enable
            AND tbs.enable
            AND tbs.status = 'PUBLISH'
            <if test="serviceUnitId != null">
                AND tbsr.service_id = #{serviceUnitId}
            </if>
            <if test="regionName != null and regionName != ''">
                AND tbsr.region_name LIKE CONCAT('%',#{regionName},'%')
            </if>
            <if test="leadId != null">
                AND tbsr.lead_id = #{leadId}
            </if>
            <if test="city != null and city != ''">
                AND EXISTS( SELECT
                    1
                FROM
                    t_b_service_region_detail tbsrd
                WHERE
                    tbsr.id = tbsrd.service_region_id
                    AND tbsrd.city LIKE CONCAT('%',#{city},'%'))
            </if>
            <if test="serviceStatus != null">
                AND tbs.status != #{serviceStatus}
            </if>
            <if test="dpr != null">
                <if test="!dpr.hasAdmin and !dpr.hasPmo and dpr.hasBusiness">
                    AND tbs.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>
        </where>
        ORDER BY tbsr.update_time DESC
    </select>
</mapper>
