<?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.order_no AS `orderNo`,
        wc.account_number AS `accountNumber`,
        wc.account_name AS `accountName`,
        wc.offline_way AS `offlineWay`
        FROM
        (SELECT * FROM `my_wallet_cath` WHERE 1 = 1
        <if test="state != null">
            AND `stauts`=#{state}
            i
        </if>
        <if test="orderNo !=null and orderNo !=''">
            AND `order_no` = #{orderNo}
        </if>
        <if test="startTime !=null and  endTime !=null">
            AND `crt_time` BETWEEN #{startTime} AND #{endTime}
        </if>
        <if test="startTime !=null and endTime ==null">
            AND  <![CDATA[
               `crt_time` >= #{startTime}
            ]]>
        </if>
        <if test="endTime !=null and startTime ==null ">
            AND <![CDATA[
              `crt_time` <= #{endTime}
            ]]>
        </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>