<?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.member.mapper.MemberUserMapper">

    <resultMap type="MemberUser" id="MemberUserResult">
        <result property="id" column="id"/>
        <result property="userType" column="user_type"/>
        <result property="identityType" column="identity_type"/>
        <result property="userId" column="user_id"/>
        <result property="tel" column="tel"/>
        <result property="tremaining" column="tremaining"/>
        <result property="ttotalNum" column="ttotal_num"/>
        <result property="tfansNum" column="tfans_num"/>
        <result property="topenDisturb" column="topen_disturb"/>
        <result property="trecipientName" column="trecipient_name"/>
        <result property="trecipientImage" column="trecipient_image"/>
        <result property="bpoints" column="bpoints"/>
        <result property="creator" column="creator"/>
        <result property="createTime" column="create_time"/>
        <result property="updater" column="updater"/>
        <result property="updateTime" column="update_time"/>
        <result property="delFlag" column="del_flag"/>
    </resultMap>

    <sql id="selectMemberUserVo">
        select id,
               user_type,
               identity_type,
               user_id,
               tel,
               remaining,
               ttotal_num,
               tfans_num,
               topen_disturb,
               trecipient_name,
               trecipient_image,
               bpoints,
               creator,
               create_time,
               updater,
               update_time,
               del_flag
        from dl_member_user
    </sql>

    <select id="queryListPage" resultType="com.ruoyi.member.vo.MemberUserVO">
        select main.id AS id,
        main.user_type AS userType,
        main.identity_type AS identityType,
        main.user_id AS userId,
        main.tel AS tel,
        main.tremaining AS tremaining,
        main.ttotal_num AS ttotalNum,
        main.tfans_num AS tfansNum,
        main.topen_disturb AS topenDisturb,
        main.trecipient_name AS trecipientName,
        main.trecipient_image AS trecipientImage,
        main.bpoints AS bpoints,
        su.nick_name AS nickName,
        su.status AS status,
        su.avatar AS avatar,
        su.phonenumber AS phonenumber,
        su_temp.nick_name AS inviteName,
        GROUP_CONCAT(mc.card_name SEPARATOR ', ') AS memberCardName
        from dl_member_user main
        LEFT JOIN sys_user su ON main.user_id = su.user_id AND su.del_flag = 0
        LEFT JOIN sys_user su_temp ON su.invite_id = su_temp.user_id AND su_temp.del_flag = 0
        LEFT JOIN dl_member_card mc
            ON main.user_id = mc.user_id
                   AND mc.start_date &lt;= CURDATE()
                   AND mc.end_date &gt;= CURDATE()
                   AND mc.del_flag = 0
                   AND mc.user_type = #{entity.userType}
        <where>
            main.del_flag = 0
            <if test="entity.userType != null  and entity.userType != ''">
            and main.user_type = #{entity.userType}
            </if>
            <if test="entity.identityType != null  and entity.identityType != ''">and main.identity_type =
                #{entity.identityType}
            </if>
            <if test="entity.nickName != null and entity.nickName != '' ">and su.nick_name like concat('%',#{entity.nickName},'%') </if>
            <if test="entity.userId != null "> and su.invite_id = #{entity.userId}</if>
            <if test="entity.tel != null and entity.tel !='' "> and main.tel like CONCAT('%',#{entity.tel},'%') </if>
        </where>
        GROUP BY main.user_id
        ORDER BY mc.create_time desc
    </select>
    <select id="queryByUserId" resultType="com.ruoyi.member.vo.MemberUserVO">
        select main.id AS id,
        main.user_type AS userType,
        main.identity_type AS identityType,
        sdd.dict_label AS identityName,
        main.user_id AS userId,
        main.tel AS tel,
        main.tremaining AS tremaining,
        main.ttotal_num AS ttotalNum,
        main.tfans_num AS tfansNum,
        main.topen_disturb AS topenDisturb,
        main.trecipient_name AS trecipientName,
        main.trecipient_image AS trecipientImage,
        main.bpoints AS bpoints,
        main.send_report_num as sendReportNum,
        main.is_get_gift as isGetGift,
        su.nick_name AS nickName,
        su.status AS status,
        su.avatar AS avatar,
        su.user_name AS userName,
        su.phonenumber AS phonenumber,

        GROUP_CONCAT(mc.card_name SEPARATOR ', ') AS memberCardName
        from dl_member_user main
        LEFT JOIN sys_user su ON main.user_id = su.user_id AND su.del_flag = 0
            left join (select dict_value,dict_label from sys_dict_data where dict_type = 'dl_identity_type') sdd on main.identity_type = sdd.dict_value
        LEFT JOIN dl_member_card mc ON
            main.user_id = mc.user_id
                AND mc.start_date &lt;= CURDATE()
                AND mc.end_date &gt;= CURDATE()
                AND mc.del_flag = 0
                AND mc.user_type = #{userType}
        where
            main.del_flag = 0
            and main.user_id = #{userId}
        and main.user_type = #{userType}
        GROUP BY main.user_id
    </select>


</mapper>
