<?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.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,
	ausw.order_no,
	ausw.sourceUserName
FROM
	(select id,user_id,source,amount,cono,itype,activity_id,activity_name,`crt_time` FROM `my_wallet_detail` <if test="sourceType != null">
		WHERE `source`=#{sourceType}
	</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,au.id,order_no,IFNULL(d.realname,d.nickname) AS `sourceUserName`,price,extract,waiting,title FROM `app_user_selling_water` AS `au` INNER JOIN `app_user_detail` AS `d` ON d.userid=au.source_id) 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>