<?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.kxs.user.biz.mapper.KxsUserMapper">

    <resultMap type="com.kxs.user.api.model.KxsUser" id="KxsUserMap">
        <result column="id" property="id"/>
        <result column="is_min_warehouse" property="isMinWarehouse"/>
        <result column="is_merchant_maker" property="isMerchantMaker"/>
        <result column="status" property="status"/>
        <result column="version" property="version"/>
        <result column="real_status" property="realStatus"/>
        <result column="hand_pwd" property="handPwd"/>
        <result column="pay_pwd" property="payPwd"/>
        <result column="user_level" property="userLevel"/>
        <result column="salt" property="salt"/>
        <result column="avatar" property="avatar"/>
        <result column="user_code" property="userCode"/>
        <result column="pid_path" property="pidPath"/>
        <result column="pid" property="pid"/>
        <result column="address" property="address"/>
        <result column="phone" property="phone"/>
        <result column="password" property="password"/>
        <result column="username" property="username"/>
        <result column="remark" property="remark"/>
        <result column="hidden_level" property="hiddenLevel"/>
        <result column="del_flag" property="delFlag"/>
        <result column="update_time" property="updateTime"/>
        <result column="create_time" property="createTime"/>
        <result column="to_merchant_maker_time" property="toMerchantMakerTime"/>
        <result column="this_month_trade" property="thisMonthTrade"/>
    </resultMap>
    <select id="findUserRealBankInfo" resultType="com.kxs.user.api.vo.kxsapp.user.RealUserInfoVO">
        select a.phone, a.user_code, r.real_name, r.id_card, b.bank_name, b.bank_open, b.bank_phone, b.bank_code
        from kxs_user a
        left join kxs_user_real r on a.id = r.user_id
        left join kxs_user_bank b on a.id = b.user_id
        <where>
            a.id = #{userId}
        </where>
    </select>
    <select id="selectMaxLevel" resultType="java.lang.Integer">
        select GREATEST(IFNULL(a.user_level, 0), IFNULL(leader.leader_level, 0), IFNULL(partner.partner_level, 0),
                        IFNULL(preset.preset_level, 0))
        from kxs_user a
                 left join kxs_user_preset_log preset on a.id = preset.user_id and preset.end_time > now()
                 left join kxs_leader leader on a.id = leader.user_id and leader.expired_date > now()
                 left join kxs_partner partner on a.id = partner.user_id and partner.expired_date > now()
        where a.id = #{userId}

    </select>
    <select id="selectMaxBeforeLevel" resultType="java.lang.Integer">
        select GREATEST(IFNULL(a.user_level, 0), IFNULL(preset.preset_level, 0))
        from kxs_user a
                 left join kxs_user_preset_before preset on a.id = preset.user_id and preset.end_time > now()
        where a.id = #{userId}
    </select>
    <select id="getParenUser" resultType="com.kxs.user.api.model.KxsUser">
        select
            *
        from kxs_user
        where del_flag != 1
            and id = (
            select pid from kxs_user where id = #{userId}
                                     )
    </select>
    <select id="myUserList" resultType="com.kxs.user.api.vo.kxsapp.user.UserMyUserListVO">
        SELECT
        ku.id, ku.username,ku.create_time,kur.create_time as real_time, ku.this_month_trade
        FROM
        kxs_user ku
        LEFT JOIN kxs_user_real kur ON kur.user_id = ku.id
        WHERE
        ku.del_flag = 0
        AND ku.real_status = 1
        AND ku.pid = #{param.userId}
        <if test="param.keyword != null and param.keyword != ''">
            AND ku.username = #{param.keyword} or ku.user_code = #{param.keyword}
        </if>
        <if test="param.userLevel != null and param.userLevel != ''">
            AND ku.user_level = #{param.userLevel}
        </if>
        <if test="param.sort != null and param.sort !='' and param.sort == 'amount'">
            ORDER BY ku.this_month_trade DESC
        </if>
        <if test="param.sort != null and param.sort !='' and param.sort == 'time'">
            ORDER BY ku.create_time
        </if>
    </select>
    <select id="searchMyUserList" resultType="com.kxs.user.api.vo.kxsapp.user.SearchMyUserListVO">
        SELECT
        ku.id, ku.username, ku.create_time, ku.avatar, ku.user_code,ku.this_month_trade
        FROM
        kxs_user ku
        WHERE
        ku.del_flag = 0
        AND ku.real_status = 1
        AND ku.pid_path LIKE concat('%,', #{userId}, ',%')
        AND (ku.username = #{keyword} or ku.user_code = #{keyword})
    </select>


</mapper>

