<?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.ruoyi.branch.mapper.TBranchMemberMapper">
    
    <resultMap type="TBranchMember" id="TBranchMemberResult">
        <result property="memberId"    column="member_id"    />
        <result property="userId"    column="user_id"    />
        <result property="memberType"    column="member_type"    />
        <result property="birthday"    column="birthday"    />
        <result property="educationLevel"    column="education_level"    />
        <result property="workJoinTime"    column="work_join_time"    />
        <result property="workEntryTime"    column="work_entry_time"    />
        <result property="partyEntryTime"    column="party_entry_time"    />
        <result property="labourEntryTime"    column="labour_entry_time"    />
        <result property="labourLeaveTime"    column="labour_leave_time"    />
        <result property="entryTime"    column="entry_time"    />
        <result property="leaveTime"    column="leave_time"    />
        <result property="isLeague"    column="is_league"    />
        <result property="applyTime"    column="apply_time"    />
        <result property="formCreateTime"    column="form_create_time"    />
        <result property="currentMentor"    column="current_mentor"    />
        <result property="planDevelopDuration"    column="plan_develop_duration"    />
        <result property="isTrained"    column="is_trained"    />
        <result property="remarks"    column="remarks"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="deptId"    column="dept_id"    />
        <result property="nickName"    column="nick_name"    />
        <result property="sex"    column="sex"    />
        <result property="phonenumber"    column="phonenumber"    />
        <result property="ethnic"    column="ethnic"    />
        <result property="staffId"    column="staff_id"    />
        <result property="newDeptId"    column="new_dept_id"    />
        <result property="oldDeptId"    column="old_dept_id"    />
        <result property="apStatus"    column="ap_status"    />
        <collection  property="roles"   javaType="java.util.List" resultMap="RoleResult" />
        <collection  property="posts"   javaType="java.util.List" resultMap="PostResult" />
    </resultMap>

    <resultMap id="RoleResult" type="SysRole">
        <id     property="roleId"       column="role_id"        />
        <result property="roleName"     column="role_name"      />
        <result property="roleKey"      column="role_key"       />
        <result property="roleSort"     column="role_sort"      />
        <result property="dataScope"     column="data_scope"    />
        <result property="status"       column="role_status"    />
    </resultMap>

    <resultMap id="PostResult" type="SysPost">
        <id     property="postId"       column="post_id"        />
        <result property="postCode"     column="post_code"      />
        <result property="postName"      column="post_name"       />
        <result property="postSort"     column="post_sort"      />
        <result property="status"    column="post_status"    />
    </resultMap>

    <sql id="selectTBranchMemberVo">
        select u.member_id, u.new_dept_id, u.old_dept_id, u.ap_status, u.user_id, u.staff_id, u.ethnic, u.member_type, u.birthday, u.education_level, u.work_join_time, u.work_entry_time, u.party_entry_time, u.labour_entry_time, u.labour_leave_time, u.entry_time, u.leave_time, u.is_league, u.apply_time, u.form_create_time, u.current_mentor, u.plan_develop_duration, u.is_trained, u.remarks, u.del_flag, u.create_by, u.create_time, u.update_by, u.update_time, u.dept_id,
        us.NICK_NAME, us.SEX, us.PHONENUMBER,
        r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.status as role_status,
        p.post_id, p.post_code, p.post_name, p.post_sort, p.status as post_status
        from t_branch_member u
            left join sys_dept d on u.dept_id = d.dept_id
            left join SYS_USER us on us.user_id = u.user_id
            left join sys_user_role ur on us.user_id = ur.user_id
            left join sys_role r on r.role_id = ur.role_id
            left join sys_user_post up on us.user_id = up.user_id
            left join sys_post p on p.post_id = up.post_id
    </sql>

    <select id="selectPoliticalBirthdayList" parameterType="TBranchMember" resultMap="TBranchMemberResult">
        <include refid="selectTBranchMemberVo"/>
        where u.DEL_FLAG = 0
        and extract(month from u.PARTY_ENTRY_TIME) = extract(month from sysdate)
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>

    <select id="selectTBranchMemberList" parameterType="TBranchMember" resultMap="TBranchMemberResult">
        <include refid="selectTBranchMemberVo"/>
        <where>  
            <if test="memberId != null "> and u.member_id = #{memberId}</if>
            <if test="userId != null "> and u.user_id = #{userId}</if>
            <if test="memberType != null  and memberType != '' and memberType == 1"> and u.member_type = '1' </if>
            <if test="memberType != null  and memberType != '' and memberType == 2"> and u.member_type in ('2', '3') </if>
            <if test="memberType != null  and memberType != '' and memberType == 3"> and u.member_type = '3' </if>
            <if test="ethnic != null  and ethnic != ''"> and u.ethnic = #{ethnic}</if>
            <if test="staffId != null  and staffId != ''"> and u.staff_id = #{staffId}</if>
            <if test="birthday != null "> and u.birthday = #{birthday}</if>
            <if test="educationLevel != null  and educationLevel != ''"> and u.education_level = #{educationLevel}</if>
            <if test="workJoinTime != null "> and u.work_join_time = #{workJoinTime}</if>
            <if test="workEntryTime != null "> and u.work_entry_time = #{workEntryTime}</if>
            <if test="partyEntryTime != null "> and u.party_entry_time = #{partyEntryTime}</if>
            <if test="labourEntryTime != null "> and u.labour_entry_time = #{labourEntryTime}</if>
            <if test="labourLeaveTime != null "> and u.labour_leave_time = #{labourLeaveTime}</if>
            <if test="entryTime != null "> and u.entry_time = #{entryTime}</if>
            <if test="leaveTime != null "> and u.leave_time = #{leaveTime}</if>
            <if test="isLeague != null  and isLeague != ''"> and u.is_league = #{isLeague}</if>
            <if test="applyTime != null "> and u.apply_time = #{applyTime}</if>
            <if test="formCreateTime != null "> and u.form_create_time = #{formCreateTime}</if>
            <if test="currentMentor != null  and currentMentor != ''"> and u.current_mentor = #{currentMentor}</if>
            <if test="planDevelopDuration != null  and planDevelopDuration != ''"> and u.plan_develop_duration = #{planDevelopDuration}</if>
            <if test="isTrained != null  and isTrained != ''"> and u.is_trained = #{isTrained}</if>
            <if test="remarks != null  and remarks != ''"> and u.remarks like concat(concat('%', #{remarks}), '%')</if>
            <if test="deptId != null "> and u.dept_id = #{deptId}</if>
            <if test="nickName != null  and nickName != ''"> and us.nick_name like concat(concat('%', #{nickName}), '%')</if>
            and u.del_flag = 0
        </where>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>
    
    <select id="selectTBranchMemberByMemberId" parameterType="Long" resultMap="TBranchMemberResult">
        <include refid="selectTBranchMemberVo"/>
        where u.member_id = #{memberId}
        and u.del_flag = 0
    </select>
        
    <insert id="insertTBranchMember" parameterType="TBranchMember">
        <selectKey keyProperty="memberId" resultType="long" order="BEFORE">
            SELECT seq_t_branch_member.NEXTVAL as memberId FROM DUAL
        </selectKey>
        insert into t_branch_member
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="memberId != null">member_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="memberType != null">member_type,</if>
            <if test="ethnic != null">ethnic,</if>
            <if test="staffId != null">staff_id,</if>
            <if test="birthday != null">birthday,</if>
            <if test="educationLevel != null">education_level,</if>
            <if test="workJoinTime != null">work_join_time,</if>
            <if test="workEntryTime != null">work_entry_time,</if>
            <if test="partyEntryTime != null">party_entry_time,</if>
            <if test="labourEntryTime != null">labour_entry_time,</if>
            <if test="labourLeaveTime != null">labour_leave_time,</if>
            <if test="entryTime != null">entry_time,</if>
            <if test="leaveTime != null">leave_time,</if>
            <if test="isLeague != null">is_league,</if>
            <if test="applyTime != null">apply_time,</if>
            <if test="formCreateTime != null">form_create_time,</if>
            <if test="currentMentor != null">current_mentor,</if>
            <if test="planDevelopDuration != null">plan_develop_duration,</if>
            <if test="isTrained != null">is_trained,</if>
            <if test="remarks != null">remarks,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="newDeptId != null">new_dept_id,</if>
            <if test="oldDeptId != null">old_dept_id,</if>
            <if test="apStatus != null">ap_status,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="memberId != null">#{memberId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="memberType != null">#{memberType},</if>
            <if test="ethnic != null">#{ethnic},</if>
            <if test="staffId != null">#{staffId},</if>
            <if test="birthday != null">#{birthday},</if>
            <if test="educationLevel != null">#{educationLevel},</if>
            <if test="workJoinTime != null">#{workJoinTime},</if>
            <if test="workEntryTime != null">#{workEntryTime},</if>
            <if test="partyEntryTime != null">#{partyEntryTime},</if>
            <if test="labourEntryTime != null">#{labourEntryTime},</if>
            <if test="labourLeaveTime != null">#{labourLeaveTime},</if>
            <if test="entryTime != null">#{entryTime},</if>
            <if test="leaveTime != null">#{leaveTime},</if>
            <if test="isLeague != null">#{isLeague},</if>
            <if test="applyTime != null">#{applyTime},</if>
            <if test="formCreateTime != null">#{formCreateTime},</if>
            <if test="currentMentor != null">#{currentMentor},</if>
            <if test="planDevelopDuration != null">#{planDevelopDuration},</if>
            <if test="isTrained != null">#{isTrained},</if>
            <if test="remarks != null">#{remarks},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="newDeptId != null">#{newDeptId},</if>
            <if test="oldDeptId != null">#{oldDeptId},</if>
            <if test="apStatus != null">#{apStatus},</if>
         </trim>
    </insert>

    <update id="updateTBranchMember" parameterType="TBranchMember">
        update t_branch_member
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="memberType != null">member_type = #{memberType},</if>
            <if test="ethnic != null">ethnic = #{ethnic},</if>
            <if test="staffId != null">staff_id = #{staffId},</if>
            <if test="birthday != null">birthday = #{birthday},</if>
            <if test="educationLevel != null">education_level = #{educationLevel},</if>
            <if test="workJoinTime != null">work_join_time = #{workJoinTime},</if>
            <if test="workJoinTime == null">work_join_time = null,</if>
            <if test="workEntryTime != null">work_entry_time = #{workEntryTime},</if>
            <if test="workEntryTime == null">work_entry_time = null,</if>
            <if test="partyEntryTime != null">party_entry_time = #{partyEntryTime},</if>
            <if test="partyEntryTime == null">party_entry_time = null,</if>
            <if test="labourEntryTime != null">labour_entry_time = #{labourEntryTime},</if>
            <if test="labourEntryTime == null">labour_entry_time = null,</if>
            <if test="labourLeaveTime != null">labour_leave_time = #{labourLeaveTime},</if>
            <if test="labourLeaveTime == null">labour_leave_time = null,</if>
            <if test="entryTime != null">entry_time = #{entryTime},</if>
            <if test="entryTime == null">entry_time = null,</if>
            <if test="leaveTime != null">leave_time = #{leaveTime},</if>
            <if test="leaveTime == null">leave_time = null,</if>
            <if test="isLeague != null">is_league = #{isLeague},</if>
            <if test="applyTime != null">apply_time = #{applyTime},</if>
            <if test="applyTime == null">apply_time = null,</if>
            <if test="formCreateTime != null">form_create_time = #{formCreateTime},</if>
            <if test="formCreateTime == null">form_create_time = null,</if>
            <if test="currentMentor != null">current_mentor = #{currentMentor},</if>
            <if test="planDevelopDuration != null">plan_develop_duration = #{planDevelopDuration},</if>
            <if test="isTrained != null">is_trained = #{isTrained},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="apStatus != null">ap_status = #{apStatus},</if>
            <if test="newDeptId != null">new_dept_id = #{newDeptId},</if>
            <if test="newDeptId == null">new_dept_id = null,</if>
            <if test="oldDeptId != null">old_dept_id = #{oldDeptId},</if>
            <if test="oldDeptId == null">old_dept_id = null,</if>
        </trim>
        where member_id = #{memberId}
    </update>

    <update id="deleteTBranchMemberByMemberId" parameterType="Long">
        update t_branch_member set del_flag = 2 where member_id = #{memberId}
    </update>

    <update id="deleteTBranchMemberByMemberIds" parameterType="String">
        update t_branch_member set del_flag = 2 where member_id in
        <foreach item="memberId" collection="array" open="(" separator="," close=")">
            #{memberId}
        </foreach>
    </update>
</mapper>