<?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.ss.user.mapper.SmUserMapper">

    <resultMap type="SmUserVO" id="SmUserResult" autoMapping="true">
        <result property="balance"    column="balance"  typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"   />
        <result property="rechargeAmount"    column="recharge_amount"  typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"  />
        <result property="withDrawlAmount"    column="with_drawl_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"    />
        <result property="totalIncome"    column="total_income" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"    />
    </resultMap>

    <sql id="selectSmUserVo">
        select
            su.user_id,
            su.user_name,
            su.nick_name,
            su.email,
            su.phonenumber,
            su.birthday,
            su.sex,
            su.avatar,
            su.password,
            su.balance,
            su.pay_password,
            su.status,
            su.del_flag,
            su.login_ip,
            su.login_date,
            su.create_by,
            su.create_time,
            su.update_by,
            su.update_time,
            su.remark,
            su.wx_open_id,
            su.delete_time,
            su.is_mch,
            su.service_rate,
            su.device_admin,
            su.service_type,
            su.withdraw_service_type,
            su.withdraw_service_rate,
            su.real_name,
            su.real_id_card,
            su.real_phone,
            su.is_real,
            su.limit_withdraw,
            su.limit_withdraw_reason,
            su.limit_refund,
            su.limit_refund_reason,
            su.read_mch_licence,
            su.risk_count,
            su.limit_withdraw_time,
            su.agent_service_rate,
            su.arrival_delay,
            su.type,
            su.limit_refund_time,
            su.show_bill_mobile,
            su.show_bill_mobile_price,
            su.agent_device_service,
            su.agent_allow_mch_switch,
            if(su.is_real, su.real_name, su.user_name) as real_or_user_name,
            (select sum(stb.money) from sm_transaction_bill stb where stb.user_id = su.user_id and stb.type = '1' and stb.status = '2') as recharge_amount,
            (select sum(stb.arrival_amount) from sm_transaction_bill stb where stb.user_id = su.user_id and stb.type = '2' and stb.status = '14') as with_drawl_amount,
            (select sum(sb.payed_amount) from ss_bonus sb where sb.arrival_id = su.user_id and sb.arrival_type in ('2', '3')) as total_income
        from sm_user su
    </sql>

    <sql id="searchCondition">
        <if test="userName != null  and userName != ''"> and su.user_name like concat('%', #{userName}, '%')</if>
        <if test="nickName != null  and nickName != ''"> and su.nick_name like concat('%', #{nickName}, '%')</if>
        <if test="phonenumber != null  and phonenumber != ''"> and su.phonenumber like concat('%', #{phonenumber}, '%')</if>
        <if test="eqPhonenumber != null  and eqPhonenumber != ''"> and su.phonenumber = #{eqPhonenumber}</if>
        <if test="wxOpenId != null  and wxOpenId != ''"> and su.wx_open_id = #{wxOpenId}</if>
        <if test="sex != null  and sex != ''"> and su.sex = #{sex}</if>
        <if test="type != null  and type != ''"> and su.type = #{type}</if>
        <if test="status != null  and status != ''"> and su.status = #{status}</if>
        <if test="isMch != null"> and su.is_mch = #{isMch}</if>
        <if test="deleteDate != null"> and date(su.delete_time) = #{deleteDate}</if>
        <if test="createDate != null"> and date(su.create_time) = #{createDate}</if>
        <if test="userId != null"> and su.user_id = #{userId}</if>
        <if test="delFlag == null"> and su.del_flag = '0'</if>
        <if test="delFlag != null"> and su.del_flag = #{delFlag}</if>
        <if test="deviceAdmin != null"> and su.device_admin = #{deviceAdmin}</if>
        <if test="remark != null and remark != ''"> and su.remark like concat('%', #{remark}, '%')</if>
        <if test="withdrawServiceType != null  and withdrawServiceType != ''"> and withdraw_service_type = #{withdrawServiceType}</if>
        <if test="serviceType != null  and serviceType != ''"> and service_type = #{serviceType}</if>
        <if test="limitWithdraw != null "> and limit_withdraw = #{limitWithdraw}</if>
        <if test="limitRefund != null "> and limit_refund = #{limitRefund}</if>
        <if test="readMchLicence != null "> and read_mch_licence = #{readMchLicence}</if>
        <if test="showBillMobile != null "> and show_bill_mobile = #{showBillMobile}</if>
        <if test="excludeUserId != null">and su.user_id != #{excludeUserId}</if>
        <if test="agentAllowMchSwitch != null">and su.agent_allow_mch_switch != #{agentAllowMchSwitch}</if>
        <if test="realOrUserName != null and realOrUserName != ''">and if(su.is_real, su.real_name, su.user_name) like concat('%', #{realOrUserName}, '%')</if>
        <if test="tenantDeviceId != null">
            and su.user_id in (
               select sdt.tenant_id
               from sm_device_tenant sdt
               where sdt.device_id = #{tenantDeviceId}
            )
        </if>
        <if test="userIds != null and userIds.size() > 0">
            and su.user_id in
            <foreach collection="userIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </sql>

    <select id="selectSmUserList" parameterType="SmUserQuery" resultMap="SmUserResult">
        <include refid="selectSmUserVo"/>
        <where>
            <include refid="searchCondition"/>
            and su.del_flag = '0'
        </where>
    </select>

    <select id="selectSmUserByUserId" parameterType="Long" resultMap="SmUserResult">
        <include refid="selectSmUserVo"/>
        where su.user_id = #{userId} and su.del_flag = '0'
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(su.user_id)
        from sm_user su
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectSmUserByWxOpenId" resultMap="SmUserResult">
        <include refid="selectSmUserVo"/>
        where su.wx_open_id = #{openId} and su.del_flag = '0' limit 1
    </select>

    <select id="selectSimpleById" resultMap="SmUserResult">
        select *
        from sm_user
        where user_id = #{userId} and del_flag = '0'
    </select>

    <select id="selectBalanceCount" resultType="java.math.BigDecimal">
        select sum(su.balance)
        from sm_user su
        <where>
            <include refid="searchCondition"/>
            and su.del_flag = '0'
        </where>
    </select>

    <insert id="insertSmUser" parameterType="SmUser" useGeneratedKeys="true" keyProperty="userId">
        <selectKey resultType="Long" order="AFTER" keyProperty="userId">
            select LAST_INSERT_ID()
        </selectKey>
        insert into sm_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userName != null and userName != ''">user_name,</if>
            <if test="nickName != null">nick_name,</if>
            <if test="email != null">email,</if>
            <if test="phonenumber != null">phonenumber,</if>
            <if test="birthday != null">birthday,</if>
            <if test="sex != null">sex,</if>
            <if test="avatar != null">avatar,</if>
            <if test="password != null">`password`,</if>
            <if test="balance != null">balance,</if>
            <if test="payPassword != null">pay_password,</if>
            <if test="status != null">`status`,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="loginIp != null">login_ip,</if>
            <if test="loginDate != null">login_date,</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="remark != null">remark,</if>
            <if test="wxOpenId != null">wx_open_id,</if>
            <if test="deleteTime != null">delete_time,</if>
            <if test="isMch != null">is_mch,</if>
            <if test="serviceRate != null">service_rate,</if>
            <if test="deviceAdmin != null">device_admin,</if>
            <if test="serviceType != null">service_type,</if>
            <if test="withdrawServiceType != null">withdraw_service_type,</if>
            <if test="withdrawServiceRate != null">withdraw_service_rate,</if>
            <if test="realName != null">real_name,</if>
            <if test="realIdCard != null">real_id_card,</if>
            <if test="realPhone != null">real_phone,</if>
            <if test="isReal != null">is_real,</if>
            <if test="limitWithdraw != null">limit_withdraw,</if>
            <if test="limitWithdrawReason != null">limit_withdraw_reason,</if>
            <if test="limitRefund != null">limit_refund,</if>
            <if test="limitRefundReason != null">limit_refund_reason,</if>
            <if test="readMchLicence != null">read_mch_licence,</if>
            <if test="riskCount != null">risk_count,</if>
            <if test="limitWithdrawTime != null">limit_withdraw_time,</if>
            <if test="arrivalDelay != null">arrival_delay,</if>
            <if test="agentServiceRate != null">agent_service_rate,</if>
            <if test="type != null">`type`,</if>
            <if test="limitRefundTime != null">limit_refund_time,</if>
            <if test="showBillMobile != null">show_bill_mobile,</if>
            <if test="showBillMobilePrice != null">show_bill_mobile_price,</if>
            <if test="agentDeviceService != null">agent_device_service,</if>
            <if test="agentAllowMchSwitch != null">agent_allow_mch_switch,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userName != null and userName != ''">#{userName},</if>
            <if test="nickName != null">#{nickName},</if>
            <if test="email != null">#{email},</if>
            <if test="phonenumber != null">#{phonenumber},</if>
            <if test="birthday != null">#{birthday},</if>
            <if test="sex != null">#{sex},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="password != null">#{password},</if>
            <if test="balance != null">#{balance},</if>
            <if test="payPassword != null">#{payPassword},</if>
            <if test="status != null">#{status},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="loginIp != null">#{loginIp},</if>
            <if test="loginDate != null">#{loginDate},</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="remark != null">#{remark},</if>
            <if test="wxOpenId != null">#{wxOpenId},</if>
            <if test="deleteTime != null">#{deleteTime},</if>
            <if test="isMch != null">#{isMch},</if>
            <if test="serviceRate != null">#{serviceRate},</if>
            <if test="deviceAdmin != null">#{deviceAdmin},</if>
            <if test="serviceType != null">#{serviceType},</if>
            <if test="withdrawServiceType != null">#{withdrawServiceType},</if>
            <if test="withdrawServiceRate != null">#{withdrawServiceRate},</if>
            <if test="realName != null">#{realName},</if>
            <if test="realIdCard != null">#{realIdCard},</if>
            <if test="realPhone != null">#{realPhone},</if>
            <if test="isReal != null">#{isReal},</if>
            <if test="limitWithdraw != null">#{limitWithdraw},</if>
            <if test="limitWithdrawReason != null">#{limitWithdrawReason},</if>
            <if test="limitRefund != null">#{limitRefund},</if>
            <if test="limitRefundReason != null">#{limitRefundReason},</if>
            <if test="readMchLicence != null">#{readMchLicence},</if>
            <if test="riskCount != null">#{riskCount},</if>
            <if test="limitWithdrawTime != null">#{limitWithdrawTime},</if>
            <if test="arrivalDelay != null">arrival_delay,</if>
            <if test="agentServiceRate != null">#{agentServiceRate},</if>
            <if test="type != null">#{type},</if>
            <if test="limitRefundTime != null">#{limitRefundTime},</if>
            <if test="showBillMobile != null">#{showBillMobile},</if>
            <if test="showBillMobilePrice != null">#{showBillMobilePrice},</if>
            <if test="agentDeviceService != null">#{agentDeviceService},</if>
            <if test="agentAllowMchSwitch != null">#{agentAllowMchSwitch},</if>
         </trim>
    </insert>

    <update id="addRiskCount">
        update sm_user
        set risk_count = risk_count + 1
        where user_id = #{userId} and del_flag = '0'
    </update>

    <update id="addBalance">
        update sm_user
        set balance = balance + #{amount}
        where user_id = #{userId} and del_flag = '0'
    </update>

    <update id="updateSmUser" parameterType="SmUser">
        update sm_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="userName != null and userName != ''">user_name = #{userName},</if>
            <if test="nickName != null">nick_name = #{nickName},</if>
            <if test="email != null">email = #{email},</if>
            <if test="phonenumber != null">phonenumber = #{phonenumber},</if>
            <if test="birthday != null">birthday = #{birthday},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="password != null">`password` = #{password},</if>
            <if test="balance != null">balance = #{balance},</if>
            <if test="payPassword != null">pay_password = #{payPassword},</if>
            <if test="status != null">`status` = #{status},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="loginIp != null">login_ip = #{loginIp},</if>
            <if test="loginDate != null">login_date = #{loginDate},</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="remark != null">remark = #{remark},</if>
            <if test="wxOpenId != null">wx_open_id = #{wxOpenId},</if>
            <if test="deleteTime != null">delete_time = #{deleteTime},</if>
            <if test="isMch != null">is_mch = #{isMch},</if>
            <if test="serviceRate != null">service_rate = #{serviceRate},</if>
            <if test="deviceAdmin != null">device_admin = #{deviceAdmin},</if>
            <if test="serviceType != null">service_type = #{serviceType},</if>
            <if test="withdrawServiceType != null">withdraw_service_type = #{withdrawServiceType},</if>
            <if test="withdrawServiceRate != null">withdraw_service_rate = #{withdrawServiceRate},</if>
            <if test="realName != null">real_name = #{realName},</if>
            <if test="realIdCard != null">real_id_card = #{realIdCard},</if>
            <if test="realPhone != null">real_phone = #{realPhone},</if>
            <if test="isReal != null">is_real = #{isReal},</if>
            <if test="limitWithdraw != null">limit_withdraw = #{limitWithdraw},</if>
            <if test="limitWithdrawReason != null">limit_withdraw_reason = #{limitWithdrawReason},</if>
            <if test="limitRefund != null">limit_refund = #{limitRefund},</if>
            <if test="limitRefundReason != null">limit_refund_reason = #{limitRefundReason},</if>
            <if test="readMchLicence != null">read_mch_licence = #{readMchLicence},</if>
            <if test="riskCount != null">risk_count = #{riskCount},</if>
            <if test="limitWithdrawTime != null">limit_withdraw_time = #{limitWithdrawTime},</if>
            <if test="arrivalDelay != null">arrival_delay = #{arrivalDelay},</if>
            <if test="agentServiceRate != null">agent_service_rate = #{agentServiceRate},</if>
            <if test="type != null">`type` = #{type},</if>
            <if test="limitRefundTime != null">limit_refund_time = #{limitRefundTime},</if>
            <if test="showBillMobile != null">show_bill_mobile = #{showBillMobile},</if>
            <if test="showBillMobilePrice != null">show_bill_mobile_price = #{showBillMobilePrice},</if>
            <if test="agentDeviceService != null">agent_device_service = #{agentDeviceService},</if>
            <if test="agentAllowMchSwitch != null">agent_allow_mch_switch = #{agentAllowMchSwitch},</if>
        </trim>
        where user_id = #{userId}
    </update>

    <update id="logicDelByIds">
        update sm_user
        set del_flag = '2'
        where user_id in
        <foreach item="userId" collection="userIds" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </update>

    <update id="subtractBalance">
        update sm_user
        set balance = balance - #{amount}
        <where>
            user_id = #{userId} and del_flag = '0'
            <if test="check">
                and balance >= #{amount}
            </if>
        </where>
    </update>

    <update id="updateServiceRate">
        update sm_user
        set service_type = #{serviceType},
            service_rate = #{serviceRate},
            withdraw_service_type = #{withdrawServiceType},
            withdraw_service_rate = #{withdrawServiceRate}
        where user_id = #{userId} and del_flag = '0'
    </update>

    <update id="bindMobile">
        update sm_user
        set phonenumber = #{mobile}
        where user_id = #{userId} and (phonenumber is null or phonenumber = '')
    </update>

    <delete id="deleteSmUserByUserId" parameterType="Long">
        delete from sm_user where user_id = #{userId}
    </delete>

    <delete id="deleteSmUserByUserIds" parameterType="String">
        delete from sm_user where user_id in
        <foreach item="userId" collection="array" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </delete>

    <select id="selectDeviceTenant" resultMap="SmUserResult">
        select sdt.*
        from sm_user su
            left join sm_device_tenant sdt on su.user_id = sdt.tenant_id
        where sdt.device_id = #{deviceId} and su.del_flag = '0'
    </select>

    <select id="selectSumOfBalance" resultType="java.math.BigDecimal">
        select sum(su.balance)
        from sm_user su
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectCountByPhone" resultType="java.lang.Integer">
        select count(su.user_id)
        from sm_user su
        where su.phonenumber = #{phonenumber} and su.del_flag != '2'
    </select>
</mapper>
