<?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="cn.iocoder.yudao.module.member.dal.mysql.group.MemberGroupMapper">

    <!--
        一般情况下，尽可能使用 Mapper 进行 CRUD 增删改查即可。
        无法满足的场景，例如说多表关联查询，才使用 XML 编写 SQL。
        代码生成器暂时只生成 Mapper XML 文件本身，更多推荐 MybatisX 快速开发插件来生成查询。
        文档可见：https://www.iocoder.cn/MyBatis/x-plugins/
     -->
    <insert id="addMember">
        INSERT INTO member_group_member (group_id, member_id)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{groupId}, #{item})
        </foreach>
    </insert>

    <select id="getMemberByGroupId" resultType="cn.iocoder.yudao.module.member.dal.dataobject.user.MemberUserDO">
        select mu.*
        from  member_group_member mgm
             left join member_user mu on mgm.member_id = mu.id
        where mgm.group_id = #{groupId}
          and mu.status = 0
        <if test="nickname != null and nickname !=''">
            and mu.nickname like concat('%',#{nickname},'%')
        </if>
        <if test="mobile != null and mobile !=''">
            and mu.mobile like concat('%',#{mobile},'%')
        </if>
        order by mgm.create_time desc
        limit #{limitOne},#{pageSize}
    </select>

    <select id="getByGroupId" resultType="cn.iocoder.yudao.module.member.dal.dataobject.user.MemberUserDO">
        select mu.*
        from  member_group_member mgm
        left join member_user mu on mgm.member_id = mu.id
        where mgm.group_id = #{groupId}
        and mu.status = 0
    </select>

    <delete id="deleteMember">
        delete from member_group_member
        where group_id = #{groupId} and member_id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="memberCount" resultType="Long">
        select count(*)
        from  member_group_member mgm
                  left join member_user mu on mgm.member_id = mu.id
        where mgm.group_id = #{groupId}
          and mu.status = 0
        <if test="nickname != null and nickname !=''">
            and mu.nickname like concat('%',#{nickname},'%')
        </if>
        order by mgm.create_time desc
    </select>

    <select id="getMemberList" resultType="Long">
        select member_id from member_group_member where group_id = #{groupId}
    </select>

    <select id="getAllMember" resultType="Long">
        select member_id from member_group_member
    </select>

    <select id="getGroupName" resultType="string">
        SELECT name FROM member_group WHERE
        id = (select group_id from member_group_member where member_id = #{userId} limit 1 )
    </select>
    <select id="selectAllUserGroupName"  resultType="java.util.Map">
        select member_id,name from member_group_member mm
        left join member_group mg on mm.group_id = mg.id
    </select>

    <select id="getGroupList" resultType="cn.iocoder.yudao.module.member.controller.admin.group.vo.MemberGroupUserVo">
        SELECT mg.name,mm.member_id FROM member_group_member mm left join member_group mg on  mm.group_id = mg.id
         where mm.member_id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="getCarteenId" resultType="long">
        SELECT carteen_id FROM member_group WHERE
            id in (select group_id from member_group_member where member_id = #{userId})
    </select>

</mapper>