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

    <!-- 通用查询映射结果 -->
    <resultMap id="CuMemberTeam" type="com.hzs.common.domain.member.base.CuMemberTeam">
        <id column="PK_ID" property="pkId"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="PK_MEMBER" property="pkMember"/>
        <result column="TEAM_NAME" property="teamName"/>
        <result column="PK_TRANSACTION" property="pkTransaction"/>
    </resultMap>

    <update id="updateSelfCuMemberTeam">
        update cu_member
        set pk_team_code=null
        where pk_id in (
            select t.pk_member
            from cu_member_team t
                     inner join (
                SELECT t.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
            ) s on t.pk_member = s.pk_id
            where t.del_flag = 0)
    </update>

    <update id="updateCuMemberUnderUmbrella">
        merge into cu_member a
            using (
                SELECT t.pk_id
                FROM cu_member t
                WHERE t.del_flag = 0
                    START
                WITH t.pk_parent = #{pkMember}
                CONNECT BY PRIOR
                    t.pk_id = t.pk_parent) b
            on (a.pk_id = b.pk_id)
            when matched then
                update set a.pk_team_code = #{pkId}
    </update>

    <update id="updateCuMemberUnderUmbrellaNull">
        update cu_member
        set pk_team_code = null
        where pk_id in (
            SELECT t.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)
    </update>

    <!-- 查询隶属团队列表-->
    <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 and b.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="pkId != null">
            and a.PK_ID =#{pkId}
        </if>
        ORDER BY a.CREATION_TIME DESC
    </select>

    <select id="queryCuMemberUmbrella" resultMap="CuMemberTeam">
        select t.pk_id, t.pk_member
        from cu_member_team t
                 left join (
            SELECT t.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
        ) s on t.pk_member = s.pk_id
        where t.del_flag = 0
    </select>

</mapper>
