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

    <select id="findWalletDetailsByUserNameOrPhoneOrsourceType"
            resultType="com.github.wxiaoqi.security.admin.dto.WalletDetailListDTO">
        SELECT
        wd.id,
        wd.source,
        wd.amount,
        wd.cono,
        wd.with_draw_order_no AS `withDrawOrderNo`,
        wd.itype,
        wd.activity_id AS `activityId`,
        wd.activity_name AS `activityName`,
        wd.crt_time AS `crtTime`,
        aul.username AS `phone`,
        aud.nickname,
        aud.realname,
        ausw.price,
        ausw.extract,
        ausw.waiting,
        ausw.title
        FROM
        (select id,user_id,source,amount,`with_draw_order_no`,cono,itype,activity_id,activity_name,`crt_time` FROM
        `my_wallet_detail` WHERE 1=1
        <if test="sourceType != null">
            AND `source`=#{sourceType}
        </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 `wd`
        INNER JOIN (SELECT id,username FROM `app_user_login`<if test="phone != null and phone != ''">
        WHERE `username`=#{phone}
    </if>) AS `aul` ON aul.id = wd.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
         LEFT JOIN (SELECT DISTINCT order_id,id,price,extract,waiting,title FROM `app_user_selling_water`) AS `ausw` ON
         ausw.order_id = wd.cono
        ORDER BY `crtTime` DESC
    </select>

    <select id="findpersonalConsumptionsByUserIds"
            resultType="com.github.wxiaoqi.security.admin.dto.PersonalConsumptionDTO">
        select `user_id` AS `userId`,SUM(amount) AS `totalConsumption` FROM my_wallet_cath
        <if test="userIds != null and userIds.size() != 0">
            WHERE `user_id` IN
            <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
                #{userId}
            </foreach>
        </if>
        GROUP BY user_id;
    </select>
</mapper>