<?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.hzs.member.base.mapper.CuMemberTeamMapper">

    <!-- 查询隶属团队列表-->
    <select id="queryCuMemberTeam" parameterType="com.hzs.common.domain.member.ext.CuMemberTeamExt"
            resultType="com.hzs.common.domain.member.ext.CuMemberTeamExt">
        select
        a.PK_ID,
        a.PK_MEMBER,
        a.TEAM_NAME,
        a.PK_TRANSACTION,
        a.DEL_FLAG,
        a.PK_COUNTRY,
        a.CREATION_TIME,
        a.MODIFIED_TIME,
        a.PK_CREATOR,
        a.PK_MODIFIED,
        a.TEAM_CODE,
        a.PK_BD_VERTEX,
        b.MEMBER_CODE as memberCode,
        b.MEMBER_NAME as memberName,
        c.VERTEX_NAME as vertexName
        from CU_MEMBER_TEAM a
        left JOIN CU_MEMBER b on a.PK_MEMBER = b.PK_ID
        left JOIN BD_VERTEX c on c.PK_ID = b.PK_VERTEX
        where a.del_flag = 0
        <if test="pkMember != null and pkMember != ''">
            and a.pk_member = #{pkMember}
        </if>
        <if test="memberCode != null and memberCode != ''">
            and b.member_code like #{memberCode} || '%'
        </if>
        <if test="memberName != null and memberName != ''">
            and b.MEMBER_NAME like #{memberName} || '%'
        </if>
        <if test="vertexName != null and vertexName != ''">
            and c.VERTEX_NAME like #{vertexName} || '%'
        </if>
        <if test="teamName != null and teamName != ''">
            and a.TEAM_NAME like #{teamName} || '%'
        </if>
        <if test="teamCode != null and teamCode != ''">
            and a.TEAM_CODE like #{teamCode} || '%'
        </if>
        <if test="pkBdVertex != null">
            and a.PK_BD_VERTEX = #{pkBdVertex}
        </if>
        <if test="pkBdVertexList != null and pkBdVertexList.size>0">
            and a.PK_BD_VERTEX in
            <foreach collection="pkBdVertexList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="pkCountry != null">
            and a.PK_COUNTRY = #{pkCountry}
        </if>
        <if test="systemType != null">
            and a.SYSTEM_TYPE = #{systemType}
        </if>
        <if test="pkId != null">
            and a.PK_ID = #{pkId}
        </if>
        ORDER BY a.CREATION_TIME DESC
    </select>

    <!-- 更新团队为空 -->
    <update id="updateTeamNullById">
        update cu_member
        set pk_team_code = null
        where pk_team_code = #{pkTeamCode}
    </update>

    <!-- 更新伞下团队 -->
    <update id="updateTeamById">
        update cu_member
        set pk_team_code = #{pkTeamCode}
        where pk_id in (
            SELECT pk_id
            FROM cu_member t
            WHERE t.del_flag = 0
        START WITH t.pk_id = #{pkMember}
        CONNECT BY PRIOR t.pk_id = t.pk_parent)
               and pk_vertex = #{pkVertex}
    </update>

    <!-- 更新团队（新） -->
    <update id="updateTeamNew">
        update cu_member
        set pk_team_code = #{pkTeam}
        where pk_id in (SELECT cm.pk_id
                        FROM cu_member cm
                        WHERE cm.del_flag = 0
        START WITH cm.pk_id = #{pkMember}
        CONNECT BY PRIOR cm.pk_id = cm.pk_parent
               and cm.pk_vertex = #{pkVertex}
               and cm.pk_id not in
                 (select cmt.pk_member
            from cu_member_team cmt
            where cmt.del_flag = 0
               and cmt.pk_country = #{pkCountry}
               and cmt.pk_member != #{pkMember}))
    </update>

</mapper>
