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

    <select id="findRegionDetailByServiceUnitIdAndCrmNo"
            resultType="com.qmth.sop.business.bean.result.ServiceRegionDetailResult">
        SELECT
            tbsrd.id AS serviceRegionDetailId,
            tbsrd.country AS county,
            tbsrd.province AS province,
            tbsrd.city AS city,
            tbsrd.area AS area,
            tbsrd.address AS address,
            tbsr.id AS serviceRegionId,
            tbsr.lead_id AS leadId,
            su.real_name AS leadName,
            tbsr.region_name AS regionName
        FROM
            t_b_service_region_detail tbsrd
                LEFT JOIN
            t_b_service_region tbsr ON tbsrd.service_region_id = tbsr.id
                LEFT JOIN
            t_b_service tbs ON tbsr.service_id = tbs.id AND tbs.enable
                AND tbs.type != 'CANCEL'
                LEFT JOIN
            sys_user su ON tbsr.lead_id = su.id
        <where>
            <if test="crmNo != null and crmNo != '' and serviceUnitId != null">
                AND EXISTS( SELECT
                    1
                FROM
                (
                SELECT
                    tbc.crm_no,tbc.service_id, sc.province, sc.city
                FROM
                    t_b_crm tbc
                LEFT JOIN sys_custom sc ON tbc.custom_id = sc.id) crmInfo
                WHERE
                    crmInfo.service_id = tbs.id
                    AND crmInfo.province = tbsrd.province
                    AND crmInfo.city = tbsrd.city
                    AND crmInfo.service_id = #{serviceUnitId}
                    AND crmInfo.crm_no = #{crmNo})
            </if>
        </where>
    </select>
</mapper>
