<?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.microservices.zone.member.mapper.ZoneMemberMapper">

    <resultMap type="com.microservices.zone.member.domain.ZoneMember" id="ZoneMemberResult">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="name" column="name"/>
        <result property="imageUrl" column="image_url"/>
        <result property="introduction" column="introduction"/>
        <result property="memberTypeId" column="member_type_id"/>
        <result property="memberLevelId" column="member_level_id"/>
        <result property="isAudit" column="is_audit"/>
        <result property="isHomepage" column="is_homepage"/>
        <result property="deptId" column="dept_id"/>
        <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="zoneId" column="zone_id"/>
        <result property="remark" column="remark"/>
        <result property="auditTime" column="audit_time"/>
        <result property="sort" column="sort"/>
        <association property="sysDept" column="dept_id" javaType="com.microservices.system.api.domain.SysDept"
                     resultMap="deptResult"/>
        <association property="sysUser" column="user_id" javaType="com.microservices.system.api.domain.SysUser"
                     resultMap="userResult"/>
        <association property="zoneMemberLevel" column="member_level_id"
                     javaType="com.microservices.zone.member.domain.ZoneMemberLevel"
                     resultMap="levelResult"/>
    </resultMap>

    <resultMap id="deptResult" type="com.microservices.system.api.domain.SysDept">
        <id property="deptId" column="deptId"/>
        <result property="parentId" column="parent_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="ancestors" column="ancestors"/>
        <result property="orderNum" column="order_num"/>
        <result property="leader" column="leader"/>
        <result property="status" column="dept_status"/>
    </resultMap>

    <resultMap id="userResult" type="com.microservices.system.api.domain.SysUser">
        <id property="userId" column="userId"/>
        <result property="gitlinkUserId" column="gitlink_user_id"/>
        <result property="userName" column="user_name"/>
        <result property="nickName" column="nick_name"/>
        <result property="email" column="email"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="sex" column="sex"/>
        <result property="avatar" column="avatar"/>
        <result property="delFlag" column="userDelFlag"/>
        <result property="status" column="status"/>
    </resultMap>

    <resultMap id="levelResult" type="com.microservices.zone.member.domain.ZoneMemberLevel">
        <id property="id" column="member_level_id"/>
        <result property="name" column="levelName"/>
    </resultMap>

    <sql id="selectZoneMemberAliasVo">
        select zm.id,
               zm.user_id,
               zm.name,
               zm.image_url,
               zm.introduction,
               zm.member_type_id,
               zm.member_level_id,
               zm.is_audit,
               zm.is_homepage,
               zm.dept_id,
               zm.del_flag,
               zm.create_by,
               zm.create_time,
               zm.update_by,
               zm.update_time,
               zm.zone_id,
               zm.sort,
               zm.remark,
               zm.audit_time,
               zml.name   as levelName,
               zml.sort   as levelSort,
               zmt.name   as typeName,
               zmt.sort   as typeSort,
               u.user_id  as userId,
               u.gitlink_user_id,
               u.user_name,
               u.nick_name,
               u.email,
               u.avatar,
               u.phonenumber,
               u.sex,
               u.status,
               u.del_flag as userDelFlag,
               d.dept_id  as deptId,
               d.parent_id,
               d.ancestors,
               d.dept_name,
               d.order_num,
               d.leader,
               d.status   as dept_status
        from zone_member as zm
                 left join sys_dept d on zm.dept_id = d.dept_id
                 left join sys_user u on u.user_id = zm.user_id
                 left join zone_member_level zml on zml.id = zm.member_level_id
                 left join zone_member_type zmt on zmt.id = zm.member_type_id
    </sql>

    <sql id="selectZoneMemberVo">
        select id,
               user_id,
               name,
               sort,
               image_url,
               introduction,
               member_type_id,
               member_level_id,
               is_audit,
               is_homepage,
               dept_id,
               del_flag,
               create_by,
               create_time,
               update_by,
               update_time,
               zone_id,
               audit_time,
               remark
        from zone_member
    </sql>

    <select id="selectZoneMemberList" parameterType="com.microservices.zone.member.domain.ZoneMember"
            resultMap="ZoneMemberResult">
        <include refid="selectZoneMemberAliasVo"/>
        <include refid="selectListWhere"/>
        ORDER BY
        zmt.sort ASC,
        zml.sort ASC,
        zm.sort ASC
    </select>

    <select id="selectZoneMemberById" parameterType="Long" resultMap="ZoneMemberResult">
        <include refid="selectZoneMemberAliasVo"/>
        where zm.id = #{id}
    </select>
    <select id="selectZoneMemberByZoneIdAndUserId" parameterType="Long" resultMap="ZoneMemberResult">
        <include refid="selectZoneMemberVo"/>
        where user_id = #{userId}
        and zone_id=#{zoneId}
        and is_audit &lt;&gt; 2
    </select>
    <sql id="selectListWhere">
        <where>
            <if test="memberTypeId != null  and memberTypeId != ''">and zm.member_type_id = #{memberTypeId}</if>
            <if test="memberLevelId != null ">and zm.member_level_id = #{memberLevelId}</if>
            <if test="isAudit != null  and isAudit != ''">and zm.is_audit = #{isAudit}</if>
            <if test="isHomepage != null  and isHomepage != ''">and is_homepage = #{isHomepage}</if>
            <if test="zoneId != null ">and zm.zone_id = #{zoneId}</if>
            <if test="username != null  and username != '' ">and u.user_name like concat('%', #{username}, '%')</if>
            <if test="name != null  and name != '' ">
                <choose>
                    <when test='name.contains("_")'>
                        <bind name="contentTmp" value='name.replaceAll("_","/_")'/>
                        and zm.name like concat('%', #{ contentTmp }, '%') escape '/'
                    </when>
                    <otherwise>
                        and zm.name like concat('%', #{name}, '%')
                    </otherwise>
                </choose>
            </if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}

        </where>
    </sql>
    <select id="selectZoneMemberListCount" resultType="java.lang.Integer">
        select count(0)
        from zone_member as zm
        left join sys_dept d on zm.dept_id = d.dept_id
        left join sys_user u on u.user_id = zm.user_id
        left join zone_member_level zml on zml.id = zm.member_level_id
        left join zone_member_type zmt on zmt.id = zm.member_type_id
        <include refid="selectListWhere"/>
    </select>
    <select id="selectAuditZoneMemberList" parameterType="com.microservices.zone.member.domain.ZoneMember"
            resultMap="ZoneMemberResult">
        <include refid="selectZoneMemberAliasVo"/>
        <include refid="selectListWhere"/>
        <if test="isAudit != null  and isAudit != '' ">
            <choose>
                <when test='isAudit == 0'>
                    order by zm.create_time desc
                </when>
                <otherwise>
                    order by zm.audit_time desc
                </otherwise>
            </choose>
        </if>
    </select>
    <select id="selectAuditZoneMemberCount" resultType="java.lang.Integer">
        select count(0)
        from zone_member as zm
        left join sys_dept d on zm.dept_id = d.dept_id
        left join sys_user u on u.user_id = zm.user_id
        left join zone_member_level zml on zml.id = zm.member_level_id
        left join zone_member_type zmt on zmt.id = zm.member_type_id
        <include refid="selectListWhere"/>
    </select>
    <select id="selectMaxSortByZoneId" resultType="java.lang.Integer">
        select MAX(zm.sort)
        from zone_member as zm
        where zm.zone_id = #{zoneId}
          and zm.is_audit = #{isAudit}
          and zm.member_type_id = #{memberTypeId}
          and zm.member_level_id = #{memberLevelId}
    </select>
    <select id="selectCountByZoneId" resultType="java.lang.Integer">
        select count(0)
        from zone_member as zm
        where zm.zone_id = #{zoneId}
          and zm.is_audit = #{isAudit}
          and zm.member_type_id = #{memberTypeId}
          and zm.member_level_id = #{memberLevelId}
    </select>
    <select id="selectZoneMemberByOrderAndZoneId" resultMap="ZoneMemberResult">
        <include refid="selectZoneMemberVo"/>
        where zone_id=#{zoneMember.zoneId}
        and is_audit=#{zoneMember.isAudit}
        and member_type_id = #{zoneMember.memberTypeId}
        and member_level_id = #{zoneMember.memberLevelId}
        <if test="!isContainsSelf">
            and id &lt;&gt; #{zoneMember.id}
        </if>
        ORDER BY sort ASC
        LIMIT ${order-1},1
    </select>
    <select id="selectMinSortByZoneId" resultType="java.lang.Integer">
        select MIN(zm.sort)
        from zone_member as zm
        where zm.zone_id = #{zoneId}
          and zm.is_audit = #{isAudit}
          and zm.member_type_id = #{memberTypeId}
          and zm.member_level_id = #{memberLevelId}
    </select>
    <select id="checkUsersIsMemberInDept" resultType="java.lang.Long">
        select user_id from zone_member
        where dept_id = #{deptId}
        and user_id in
        <foreach item="userId" collection="userIds" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </select>
    <select id="checkUserIsMemberInDepts" resultType="java.lang.Long">
        select dept_id from zone_member
        where user_id = #{userId}
        and dept_id in
        <foreach item="deptId" collection="deptIds" open="(" separator="," close=")">
            #{deptId}
        </foreach>
    </select>

    <insert id="insertZoneMember" parameterType="com.microservices.zone.member.domain.ZoneMember"
            useGeneratedKeys="true"
            keyProperty="id">
        insert into zone_member
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null and userId != ''">user_id,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="imageUrl != null and imageUrl != ''">image_url,</if>
            <if test="introduction != null and introduction != ''">introduction,</if>
            <if test="memberTypeId != null and memberTypeId != ''">member_type_id,</if>
            <if test="memberLevelId != null">member_level_id,</if>
            <if test="isAudit != null">is_audit,</if>
            <if test="deptId != null">dept_id,</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="auditTime != null">audit_time,</if>
            <if test="zoneId != null">zone_id,</if>
            <if test="remark != null">remark,</if>
            <if test="sort != null">sort,</if>
            <if test="isHomepage != null and isHomepage != ''">is_homepage,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null and userId != ''">#{userId},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="imageUrl != null and imageUrl != ''">#{imageUrl},</if>
            <if test="introduction != null and introduction != ''">#{introduction},</if>
            <if test="memberTypeId != null and memberTypeId != ''">#{memberTypeId},</if>
            <if test="memberLevelId != null">#{memberLevelId},</if>
            <if test="isAudit != null">#{isAudit},</if>
            <if test="deptId != null">#{deptId},</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="auditTime != null">#{auditTime},</if>
            <if test="zoneId != null">#{zoneId},</if>
            <if test="remark != null">#{remark},</if>
            <if test="sort != null">#{sort},</if>
            <if test="isHomepage != null and isHomepage != ''">#{isHomepage},</if>
        </trim>
    </insert>

    <update id="updateZoneMember" parameterType="com.microservices.zone.member.domain.ZoneMember">
        update zone_member
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null and userId != ''">user_id = #{userId},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="imageUrl != null and imageUrl != ''">image_url = #{imageUrl},</if>
            <if test="introduction != null">introduction = #{introduction},</if>
            <if test="memberTypeId != null and memberTypeId != ''">member_type_id = #{memberTypeId},</if>
            <if test="memberLevelId != null">member_level_id = #{memberLevelId},</if>
            <if test="isAudit != null">is_audit = #{isAudit},</if>
            <if test="deptId != null">dept_id = #{deptId},</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="auditTime != null">audit_time = #{auditTime},</if>
            <if test="zoneId != null">zone_id = #{zoneId},</if>
            <if test="sort != null">sort = #{sort},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateZoneMemberHomePageShow">
        update zone_member
        set is_homepage = #{isHomepage}
        where id = #{id}
    </update>
    <update id="batchUpdateSortIncreased">
        update zone_member
        set sort=sort + 1
        where sort &gt;= #{setSort}
          and zone_id = #{zoneMember.zoneId}
          and is_audit = #{zoneMember.isAudit}
          and member_type_id = #{zoneMember.memberTypeId}
          and member_level_id = #{zoneMember.memberLevelId}
    </update>

    <delete id="deleteZoneMemberById" parameterType="Long">
        delete
        from zone_member
        where id = #{id}
    </delete>

    <delete id="deleteZoneMemberByIds" parameterType="String">
        delete from zone_member where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <delete id="deleteZoneMemberByZoneId">
        delete
        from zone_member
        where zone_id = #{zoneId}
    </delete>
</mapper>