<?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.MyWalletCathMapper">


    <select id="selectByUserNameOrPhoneOrWithDrawSate" resultType="com.github.wxiaoqi.security.admin.dto.WalletCathListDTO">
        SELECT
        wc.id,
        aud.nickname,
        aud.realname,
        aul.username,
        wc.amount,
        wc.cath_type AS `cathType`,
        wc.stauts,
        wc.crt_time AS `crtTime`,
        wc.finish_time AS `finishTime`,
        wc.cono,
        wc.account_number AS `accountNumber`
        FROM
        (SELECT * FROM `my_wallet_cath` <if test="state != null">
        WHERE `stauts`=#{state}
    </if>) AS `wc`
        INNER JOIN ( SELECT `id`, `username` FROM `app_user_login` <if test="phone != null and phone != ''">
        WHERE `username`=#{phone}
    </if> ) AS `aul` ON aul.id = wc.user_id
        INNER JOIN ( SELECT `userid`, `nickname`, `realname` FROM `app_user_detail` <if
            test="userName != null and userName != ''">
        WHERE nickname =#{userName} OR realname =#{userName}
    </if> ) AS `aud` ON aud.userid = aul.id order by wc.crt_time DESC
    </select>

    <select id="findUserWithDrawingByUserIds" resultType="com.github.wxiaoqi.security.admin.dto.PersonalConsumptionDTO">
        select `user_id` AS `userId`,SUM(amount) AS `totalConsumption` from `my_wallet_cath` where `stauts`=0  AND `user_id` in
         <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
             #{userId}
         </foreach>
        GROUP BY `user_id`
    </select>
    
    <select id="sumCathAmount" resultType="com.github.wxiaoqi.security.admin.dto.WalletCathSumDto">
        select IFNULL(SUM(amount),0) as sumAmount,COUNT(*) as `number` from my_wallet_cath
        <where>
            user_id=#{userId} and stauts in(0,1)
            <choose>
                <when test="type ==1 ">
                    AND date_format(from_unixtime(crt_time/1000),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')
                </when >
                <when test="type==2 ">
                    AND date_format(from_unixtime(crt_time/1000),'%Y-%m') = date_format(now(),'%Y-%m')
                </when >
                <otherwise>
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>