<?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="org.jeecg.ctop.finance.policy.mapper.CwjsSaleAssistantInfoMapper">


    <!-- 根据用户部门查看该部门下的人员-->
    <select id="getSaleListByUserDepart" resultType="org.jeecg.ctop.finance.policy.entity.CwjsSaleAssistantInfo">
        SELECT
        us.id as saleId,
        us.realname as saleName,
        uc.nick_name as nickName
        FROM
            sys_user us
        LEFT JOIN user_company uc on us.id = uc.user_id
        WHERE
        us.STATUS = 1
        AND us.del_flag = 0
        AND us.org_code IN (
            SELECT
                u.org_code
            FROM
                sys_user u,
                sys_depart d
            WHERE
                u.org_code = d.org_code
              AND d.del_flag = 0
            AND u.status = 1
            AND u.del_flag = 0
              <choose>
                <when test="userId !=null and userId != ''">
                    AND u.id = #{userId}
                </when>
                <otherwise>
                    AND ( d.depart_name LIKE '%销售%' OR d.depart_name LIKE '%渠道%' )
                </otherwise>
              </choose>
            GROUP BY
                d.id
        )
        ORDER BY
        us.create_time DESC;
    </select>

<!--查询对应销售 -->
    <select id="getCwSaleNameList" resultType="org.jeecg.ctop.finance.policy.entity.CwjsSaleAssistantInfo">
        SELECT
            u.id AS sale_id,
            u.realname AS sale_name,
        uc.nick_name as nickName
        FROM
            sys_user u
        LEFT JOIN user_company uc on u.id = uc.user_id
        WHERE
                u.id IN ( SELECT s.sale_id FROM ctop_cwjs_sale_assistant_info s WHERE s.del_flag = 0
                            <if test="userId !=null and userId != ''">
                                AND s.sale_id = #{userId}
                            </if>
                        )
        <if test="userId !=null and userId != ''">
            OR u.id = #{userId}
        </if>
        GROUP BY u.create_time;


    </select>
<!--查询销售 -->
    <select id="selectCwSaleName" resultType="org.jeecg.ctop.finance.policy.entity.CwjsSaleAssistantInfo">
        SELECT
            u.sale_id AS sale_id,
            u.sale_name AS sale_name
        FROM
            ctop_cwjs_sale_assistant_info u
            where u.sale_id = #{userId}
        and del_flag = 0
        GROUP BY u.id;
    </select>


    <!--查询对应销售助理 -->
    <select id="getCwAssistantNameList" resultType="org.jeecg.ctop.finance.policy.entity.CwjsSaleAssistantInfo">
        SELECT
            u.id AS sale_id,
            u.realname as sale_name,
        uc.nick_name as nickName
        FROM
            sys_user u
        LEFT JOIN user_company uc on u.id = uc.user_id
        WHERE
                u.id IN ( SELECT s.sale_id FROM ctop_cwjs_sale_assistant_info s WHERE s.del_flag = 0
                            <if test="userId !=null and userId != ''">
                                AND s.assistant_id = #{userId}
                            </if>
                        )
        <if test="userId !=null and userId != ''">
            OR u.id = #{userId}
        </if>
        GROUP BY u.create_time;
    </select>

    <!-- -->
    <select id="getAssistantList" resultType="org.jeecg.ctop.finance.policy.entity.CwjsSaleAssistantInfo">
        SELECT
        u.id AS sale_id,
        u.realname as sale_name,
        uc.nick_name as nickName
        FROM
        sys_user u
        LEFT JOIN user_company uc on u.id = uc.user_id
        WHERE
        u.id IN (
        SELECT
        s.sale_id
        FROM
        ctop_cwjs_sale_assistant_info s
        WHERE
        s.del_flag = 0
        <if test="userId !=null and userId != ''">
            AND s.sale_id = #{userId} OR s.assistant_id = #{userId}
        </if>
        UNION
        SELECT
        s.assistant_id
        FROM
        ctop_cwjs_sale_assistant_info s
        WHERE
        s.del_flag = 0
        <if test="userId !=null and userId != ''">
            AND s.sale_id = #{userId} OR s.assistant_id = #{userId}
        </if>
            )
    </select>


    <!--查询对应销售助理 政策 -->
    <select id="getCwAssistantNameListPolicy" resultType="java.lang.String">
        SELECT
            u.id AS sale_id
        FROM
            sys_user u
        LEFT JOIN user_company uc on u.id = uc.user_id
        WHERE
                u.id IN ( SELECT s.sale_id FROM ctop_cwjs_sale_assistant_info s WHERE s.del_flag = 0
                            <if test="userId !=null and userId != ''">
                                AND s.assistant_id = #{userId}
                            </if>
                        )
        GROUP BY u.create_time;
    </select>



</mapper>