<?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.github.wxiaoqi.security.admin.mapper.AppUserDetailMapper">
    <resultMap id="AppUserVoMap" type="com.github.wxiaoqi.security.admin.vo.AppUserVo">
        <result column="id" property="id"/>
        <result column="userid" property="userid"/>
        <result column="im_userid" property="imUserid"/>
        <result column="username" property="username"/>
        <result column="wx_openid" property="wxOpenid"/>
        <result column="unionid" property="unionid"/>
        <result column="openid" property="openid"/>
        <result column="status" property="status"/>
        <result column="id_number" property="idNumber"/>
        <result column="certification_status" property="certificationStatus"/>
        <result column="is_member" property="isMember"/>
        <result column="nickname" property="nickname"/>
        <result column="realname" property="realname"/>
        <result column="headimgurl" property="headimgurl"/>
        <result column="birthday" property="birthday"/>
        <result column="email" property="email"/>
        <result column="sex" property="sex"/>
        <result column="person_sign" property="personSign"/>
        <result column="remark" property="remark"/>
        <result column="channel" property="channel"/>
        <result column="province_code" property="provinceCode"/>
        <result column="city_code" property="cityCode"/>
        <result column="crt_host" property="crtHost"/>
        <result column="upd_host" property="updHost"/>
        <result column="position_id" property="positionId"/>
        <result column="source" property="source"/>
        <result column="code" property="code"/>
        <result column="inviter_account" property="inviterAccount"/>
        <result column="state" property="state"/>
    </resultMap>


    <!--  获取用户信息 -->
    <select id="getUserInfo" resultMap="AppUserVoMap">
    select l.im_userid,l.username,l.wx_openid,l.unionid,l.openid,l.status,l.id_number,l.certification_status,d.*  from app_user_login l
    left join  app_user_detail d
    on d.userid = l.id
     where d.userid = #{userId}  limit 1
  </select>

    <select id="getUserIdByUsername" resultType="java.lang.Integer" parameterType="java.lang.String">
        select d.userid  from app_user_login l
        left join  app_user_detail d
        on d.userid = l.id
        where d.realname like concat("%", #{keywords}, "%") or l.username like concat("%", #{keywords}, "%")
    </select>


    <select id="selectAppUserManage" parameterType="com.github.wxiaoqi.security.admin.dto.AppUserManageDTO"
            resultType="com.github.wxiaoqi.security.admin.vo.AppUserManageVo">
        select
        l.id As userid,
        l.username,
        l.certification_status as certificationStatus,
        l.id_number as idNumber,
        l.createtime,
        l.last_time as lastTime,
        l.status,
        d.channel,
        d.is_member,
        d.realname,
        d.nickname,
        d.source,
        d.province_code as provinceCode,
        d.city_code as cityCode,
        d.sex,
        d.headimgurl,
        d.email,
        m.member_level as memberLevel,
        m.valid_time as validTime,
        m.buy_count as buyCount,
        m.total_number as totalNumber,
        m.rent_free_days as rentFreeDays,
        m.crt_time as timeOfMembership,
        m.recent_recharge as recentRecharge,
        m.name as memberName,
        ul.username as inviter,
        aup.name as `positionName`
        from
        app_user_login l
        inner join
        (select * from app_user_detail where 1=1
        <if test="postionState != null">
            <choose>
                <when test="postionState == 6">
                    and `position_id`=#{postionState}
                </when>
                <otherwise>
                    and <![CDATA[`position_id`<>6]]>
                </otherwise>
            </choose>
        </if>
        ) d
        on
        d.userid = l.id
        left join
        app_user_login ul
        on
        ul.id=d.inviter_account
        left join
        (
        select
        b.*,
        ml.name
        from
        base_user_member b
        left join
        base_user_member_level ml
        on
        ml.level = b.member_level
        ) m
        on
        l.id = m.user_id
        left join
        `app_user_position` as aup
        on aup.id = d.position_id
        where l.isdel = 0
        <if test="mobile !=null and mobile !='' ">
            and l.username like CONCAT('%',#{mobile},'%')
        </if>
        <if test="channel !=null ">
            and d.channel=#{channel}
        </if>
        <if test="memberLevel !=null and memberLevel != -1">
            and m.member_level = #{memberLevel}
        </if>
        <if test="memberLevel == -1">
            and m.member_level in (select level from base_user_member_level)
        </if>

        <if test="registrationTimeBegin !=null  ">
            and l.createtime &gt;= #{registrationTimeBegin}
        </if>
        <if test="registrationTimeEnd !=null ">
            and l.createtime &lt;= #{registrationTimeEnd}
        </if>
        <if test="source !=null  ">
            and d.source = #{source}
        </if>
        <if test="citySet != null ">
            and d.city_code in
            <foreach collection="citySet" item="item" index="index" open="(" separator="," close=")" >
                       #{item}
            </foreach>
        </if>
        order by l.id ASC
    </select>

    <update id="updateUserMemberStatusByUserId">
    update `app_user_detail` set `is_member`={status}  where `userid`=#{userId}
</update>
    <update id="updateUserPositionByUserId">
        update  `app_user_detail` set `position_id`=#{positionId} where `userid`=#{userId}
    </update>
</mapper>