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

    <select id="findServiceScopePage" resultType="com.qmth.sop.business.bean.result.CrmServiceResult">
        SELECT
            tbc.service_id AS serviceUnitId,
            IFNULL(tbs.name, '--') AS serviceUnitName,
            tbc.id AS crmId,
            tbc.crm_no AS crmNo,
            tbc.custom_id AS customId,
            sc.name AS customName,
            sc.type AS productType,
            sc.province,
            sc.city,
            sc.area,
            sl.level,
            tbs.status AS serviceUnitStatus
        FROM
            t_b_crm tbc
                LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id
                LEFT JOIN
            sys_custom sc ON tbc.custom_id = sc.id
                LEFT JOIN
            sys_level sl ON sc.level_id = sl.id
        <where>
            AND tbc.enable
            AND tbc.service_id IS NOT null
            AND tbs.enable AND tbs.status = 'PUBLISH'
            <if test="serviceUnitId != null">
                AND tbc.service_id = #{serviceUnitId}
            </if>
            <if test="city != null and city != ''">
                AND sc.city LIKE CONCAT('%',#{city},'%')
            </if>
            <if test="productType != null">
                AND sc.type = #{productType}
            </if>
            <if test="customName != null and customName != ''">
                AND sc.name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="bindStatus != null">
                <choose>
                    <when test="bindStatus">
                        AND tbc.service_id IS NOT NULL
                    </when>
                    <otherwise>
                        AND tbc.service_id IS NULL
                    </otherwise>
                </choose>
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND tbs.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>
        </where>
        ORDER BY tbc.update_time DESC
    </select>

    <select id="findServiceScopeList" resultType="com.qmth.sop.business.bean.result.CrmServiceResult">
        SELECT
            tbc.service_id AS serviceUnitId,
            IFNULL(tbs.name, '--') AS serviceUnitName,
            tbc.id AS crmId,
            tbc.crm_no AS crmNo,
            tbc.custom_id AS customId,
            sc.name AS customName,
            sc.type AS productType,
            sc.province,
            sc.city,
            sc.area,
            sl.level
        FROM
            t_b_crm tbc
        LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id
        LEFT JOIN
            sys_custom sc ON tbc.custom_id = sc.id
        LEFT JOIN
            sys_level sl ON sc.level_id = sl.id
        <where>
            AND tbc.enable
            AND tbs.enable
            AND tbs.status = 'PUBLISH'
            <if test="serviceUnitId != null">
                AND tbc.service_id = #{serviceUnitId}
            </if>
            <if test="city != null and city != ''">
                AND sc.city LIKE CONCAT('%',#{city},'%')
            </if>
            <if test="productType != null">
                AND sc.type = #{productType}
            </if>
            <if test="customName != null and customName != ''">
                AND sc.name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="bindStatus != null">
                <choose>
                    <when test="bindStatus">
                        AND tbc.service_id IS NOT NULL
                    </when>
                    <otherwise>
                        AND tbc.service_id IS NULL
                    </otherwise>
                </choose>
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <if test="dpr.hasBusiness">
                    AND tbs.service_lead_id = #{dpr.requestUserId}
                </if>
            </if>
        </where>
        ORDER BY tbc.update_time DESC
    </select>

    <select id="findUnbindOrderPage" resultType="com.qmth.sop.business.bean.result.CrmServiceResult">
        SELECT
            tbc.id AS crmId,
            tbc.crm_no AS crmNo,
            tbc.begin_time AS beginTime,
            tbc.crm_user_id AS crmUserId,
            crmuser.real_name AS crmUserName,
            tbc.custom_id AS customId,
            sc.type AS productType,
            sc.name AS customName,
            tbc.product_id AS productId,
            tbp.name AS productName,
            tbc.exam_start_time AS examStartTime,
            tbc.exam_end_time AS examEndTime,
            tbc.create_id AS creatorId,
            tbc.create_time AS createTime,
            creator.real_name AS creatorName
        FROM
            t_b_crm tbc
                LEFT JOIN
            sys_user crmuser ON tbc.crm_user_id = crmuser.id
                LEFT JOIN
            sys_custom sc ON tbc.custom_id = sc.id
                LEFT JOIN
            t_b_product tbp ON tbc.product_id = tbp.id
                LEFT JOIN
            sys_user creator ON tbc.create_id = creator.id
        <where>
            AND tbc.enable
            AND tbc.service_id IS NULL
            <if test="crmUserId != null">
                AND tbc.crm_user_id = #{crmUserId}
            </if>
            <if test="productType != null">
                AND sc.type = #{productType}
            </if>
            <if test="customName != null and customName != ''">
                AND sc.name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="crmNo != null and crmNo != ''">
                AND tbc.crm_no LIKE CONCAT('%',#{crmNo},'%')
            </if>
            <if test="startTime != null">
                AND tbc.begin_time >= #{startTime}
            </if>
            <if test="endTime != null">
                AND #{endTime} >= tbc.begin_time
            </if>
        </where>
        ORDER BY tbc.update_time DESC
    </select>

    <select id="findOrderListByServiceIds" resultType="com.qmth.sop.business.bean.result.CrmServiceResult">
        SELECT
            tbc.id AS crmId,
            tbc.crm_no AS crmNo,
            tbc.custom_id AS customId,
            sc.type AS productType,
            sc.name AS customName,
            sc.province,
            sc.city,
            tbc.service_id AS serviceUnitId
        FROM
            t_b_crm tbc
                LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id AND tbs.enable
                LEFT JOIN
            sys_custom sc ON tbc.custom_id = sc.id
        <where>
            AND tbc.enable
            <if test="serviceIdList != null and serviceIdList.size > 0">
                AND tbc.service_id IN
                <foreach collection="serviceIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>


    <select id="query" resultType="com.qmth.sop.business.bean.result.TBCrmResult">
        SELECT
        a.*,
        tbs.name service,
        cru.real_name crm_user_name,
        sc.NAME custom,
        sc.type custom_type,
        p.name product,
        lu.real_name leadName,
        cu.real_name createName,
        tbs.status serviceUnitStatus,
        tbs.type as type
        FROM
        t_b_crm a
        LEFT JOIN sys_user cru ON cru.id = a.crm_user_id
        LEFT JOIN sys_user lu ON lu.id = a.lead_id
        LEFT JOIN sys_user cu ON cu.id = a.create_id
        LEFT JOIN t_b_product p ON p.id = a.product_id
        LEFT JOIN t_b_service tbs ON a.service_id = tbs.id
        LEFT JOIN sys_custom sc ON sc.id = a.custom_id
        <where>
            AND a.enable=1
            AND (tbs.status <![CDATA[ <> ]]> 'FINISH' or tbs.status is null)
            <if test="serviceId != null and serviceId != ''">
                and a.service_id = #{serviceId}
            </if>
            <if test="isBind != null and !isBind">
                and a.service_id IS NULL
            </if>
            <if test="leadId != null and leadId != ''">
                and a.lead_id = #{leadId}
            </if>
            <if test="crmUserId != null and crmUserId != ''">
                and a.crm_user_id = #{crmUserId}
            </if>
            <if test="type != null and type != ''">
                and sc.type = #{type}
            </if>
            <if test="custom != null and custom != ''">
                and sc.name like concat('%', #{custom} , '%')
            </if>
            <if test="crmNo != null and crmNo != ''">
                and a.crm_no like concat('%', #{crmNo} , '%')
            </if>
            <if test="startTime != null and startTime != '' ">
                <![CDATA[
               and a.begin_time >= #{startTime}
               ]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[
                and a.begin_time <= #{endTime}
                ]]>
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo and !dpr.hasBusiness">
                <if test="dpr.hasRegionManager">
                    AND a.lead_id = #{dpr.requestUserId}
                </if>
            </if>
        </where>


        ORDER BY
        a.update_time DESC
    </select>
    <select id="findCrmProjectBySopNo" resultType="com.qmth.sop.business.bean.result.CrmProjectResult">
        SELECT
            tbc.crm_no AS crmNo,
            tbc.name AS crmName,
            tbc.begin_time AS crmBeginTime,
            sc.type AS customType,
            sc.id AS customId,
            sc.name AS customName,
            customManager.id AS customManagerId,
            customManager.real_name AS customManagerName,
            tbc.exam_start_time AS examStartTime,
            tbc.exam_end_time AS examEndTime,
            tbp.id AS productId,
            tbp.name AS productName,
            tbs.id AS serviceUnitId,
            tbs.name AS serviceUnitName,
            regionManager.id AS regionManagerId,
            regionManager.real_name AS regionManagerName,
            regionCoordinator.id AS regionCoordinatorId,
            regionCoordinator.real_name AS regionCoordinatorName,
            sc.peoper_day as peoperDay,
            tbc.process_var as processVar
        FROM
            t_f_custom_flow_entity tfcfe
                LEFT JOIN
            t_b_crm tbc ON tfcfe.crm_no = tbc.crm_no
                LEFT JOIN
            sys_custom sc ON tbc.custom_id = sc.id
                LEFT JOIN
            t_b_product tbp ON tbc.product_id = tbp.id
                LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id
                LEFT JOIN
            sys_user regionManager ON tbc.lead_id = regionManager.id
                LEFT JOIN
            sys_user regionCoordinator ON tbc.region_coordinator_id = regionCoordinator.id
                LEFT JOIN
            sys_user customManager ON tbc.crm_user_id = customManager.id
        <where>
            <if test="sopNo != null and sopNo != ''">
                AND tfcfe.code = #{sopNo}
            </if>
        </where>
    </select>

    <select id="findCrmProjectByCrmNo" resultType="com.qmth.sop.business.bean.result.CrmProjectResult">
        SELECT
            tbc.crm_no AS crmNo,
            tbc.name AS crmName,
            tbc.begin_time AS crmBeginTime,
            sc.type AS customType,
            sc.id AS customId,
            sc.name AS customName,
            sc.province AS customProvince,
            sc.city AS customCity,
            sc.area AS customArea,
            sc.address AS customAddress,
            sl.level AS level,
            customManager.id AS customManagerId,
            customManager.real_name AS customManagerName,
            tbc.exam_start_time AS examStartTime,
            tbc.exam_end_time AS examEndTime,
            tbp.id AS productId,
            tbp.name AS productName,
            tbs.id AS serviceUnitId,
            tbs.name AS serviceUnitName,
            regionManager.id AS regionManagerId,
            regionManager.real_name AS regionManagerName,
            sc.peoper_day as peoperDay,
            tbc.process_var as processVar
        FROM
            t_b_crm tbc
                LEFT JOIN
            sys_custom sc ON tbc.custom_id = sc.id
                LEFT JOIN
            sys_level sl ON sc.level_id = sl.id
                LEFT JOIN
            t_b_product tbp ON tbc.product_id = tbp.id
                LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id
                LEFT JOIN
            sys_user regionManager ON tbc.lead_id = regionManager.id
                LEFT JOIN
            sys_user customManager ON tbc.crm_user_id = customManager.id
        <where>
            <if test="crmNo != null and crmNo != ''">
                AND tbc.crm_no = #{crmNo}
            </if>
        </where>
    </select>

    <select id="findSopCrmInfoBySopNo" resultType="com.qmth.sop.business.bean.dto.SopCrmInfo">
        SELECT
            tfcfe.code AS sopNo,
            tfcfe.flow_id AS flowId,
            tbc.crm_no AS crmNo,
            tbs.id AS serviceUnitId
        FROM
            t_f_custom_flow_entity tfcfe
                LEFT JOIN
            t_b_crm tbc ON tfcfe.crm_no = tbc.crm_no
                LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id
        <where>
            <if test="sopNo != null and sopNo != ''">
                AND tfcfe.code = #{sopNo}
            </if>
        </where>
    </select>
    <select id="findOccupiedCrm" resultType="com.qmth.sop.business.entity.TBCrm">
        SELECT
            tbc.*
        FROM
            t_b_user_archives_allocation tbuaa
                LEFT JOIN
            t_b_crm tbc ON tbuaa.crm_no = tbc.crm_no
        <where>
            <if test="userId != null">
                AND tbuaa.user_id = #{userId}
            </if>
            <if test="userArchivesId != null">
                AND tbuaa.archives_id = #{userArchivesId}
            </if>
            AND tbc.status != 'FINISH'
        </where>
    </select>
    <select id="findByTBCrmListByServiceUnitIdAndAddress" resultType="com.qmth.sop.business.entity.TBCrm">
        SELECT
            tbc.*
        FROM
            t_b_crm tbc
                LEFT JOIN
            sys_custom sc ON tbc.custom_id = sc.id
        <where>
            <if test="serviceUnitId != null">
                AND tbc.service_id = #{serviceUnitId}
            </if>
            <if test="province != null and province != ''">
                AND sc.province = #{province}
            </if>
            <if test="city != null and city != ''">
                AND sc.city = #{city}
            </if>
        </where>
    </select>
    <select id="count" resultType="java.lang.Integer">
        SELECT
        count(*)
        FROM
        t_b_crm a
        LEFT JOIN sys_user cru ON cru.id = a.crm_user_id
        LEFT JOIN sys_user lu ON lu.id = a.lead_id
        LEFT JOIN sys_user cu ON cu.id = a.create_id
        LEFT JOIN t_b_product p ON p.id = a.product_id
        LEFT JOIN t_b_service tbs ON a.service_id = tbs.id
        LEFT JOIN sys_custom sc ON sc.id = a.custom_id
        <where>
            AND a.enable=1
            AND tbs.status != 'FINISH'
            <if test="serviceId != null and serviceId != ''">
                and a.service_id = #{serviceId}
            </if>
            <if test="isBind != null and !isBind">
                and a.service_id IS NULL
            </if>
            <if test="leadId != null and leadId != ''">
                and a.lead_id = #{leadId}
            </if>
            <if test="crmUserId != null and crmUserId != ''">
                and a.crm_user_id = #{crmUserId}
            </if>
            <if test="type != null and type != ''">
                and sc.type = #{type}
            </if>
            <if test="custom != null and custom != ''">
                and sc.name like concat('%', #{custom} , '%')
            </if>
            <if test="crmNo != null and crmNo != ''">
                and a.crm_no like concat('%', #{crmNo} , '%')
            </if>
            <if test="startTime != null and startTime != '' ">
                <![CDATA[
               and a.begin_time >= #{startTime}
               ]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[
                and a.begin_time <= #{endTime}
                ]]>
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo and !dpr.hasBusiness">
                <if test="dpr.hasRegionManager">
                    AND a.lead_id = #{dpr.requestUserId}
                </if>
            </if>
        </where>
    </select>
    <select id="findAllCrm" resultType="com.qmth.sop.business.entity.TBCrm">
        SELECT
        distinct tbc.*
        FROM
        t_b_user_archives_allocation tbuaa
        LEFT JOIN
        t_b_crm tbc ON tbuaa.crm_no = tbc.crm_no
        <where>
            AND tbc.status != 'UN_PUBLISH'
            <if test="userId != null">
                AND tbuaa.user_id = #{userId}
            </if>
            <if test="userArchivesId != null">
                AND tbuaa.archives_id = #{userArchivesId}
            </if>
        </where>
    </select>
    <select id="listCrmCustomByServiceId" resultType="com.qmth.sop.business.bean.dto.CrmCustomImportDto" parameterType="java.lang.Long">
        SELECT
            tbc.crm_no AS crmNo,
            sc.NAME customName,
            scr.consignee,
            scr.consignee_phone AS consigneePhone,
            scr.consignee_address AS address
        FROM
            t_b_crm tbc
                LEFT JOIN sys_custom sc ON tbc.custom_id = sc.id
                LEFT JOIN sys_custom_receiver scr ON trim( scr.NAME )= trim( sc.NAME ) AND sc.type=scr.type
        WHERE
            service_id = #{serviceUnitId}
        ORDER BY
            tbc.update_time DESC
    </select>


</mapper>
