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


    <select id="accquireIncomeByMemberIds" resultType="com.github.wxiaoqi.security.admin.bo.UserIncomeBo">
        SELECT DISTINCT
        `source_id`as userId,
        IFNULL((( SELECT SUM(commission) FROM `app_user_selling_water` WHERE source_id=ausw.source_id AND `status` = 0 AND
        `waiting` = 1 )
        - ( SELECT SUM(commission) FROM `app_user_selling_water` WHERE source_id=ausw.source_id AND `status` = 1 AND
        `waiting` = 1 )),0) as `income`
        FROM
        `app_user_selling_water` as ausw
        WHERE
        source_id in
        <foreach collection="memberIds" item="memberId" open="(" close=")" separator=",">
            #{memberId}
        </foreach>
    </select>

    <select id="selectTotalIncomeByUserId" resultType="java.math.BigDecimal">
        SELECT
	( auswu.upIncome - auswd.dowIncome ) AS `income`
FROM
	( SELECT IFNULL(SUM( commission ),0) AS upIncome FROM `app_user_selling_water` WHERE STATUS = 0 AND `user_id` = #{userId}  ) AS `auswu`,
	( SELECT IFNULL(SUM( commission ),0) AS `dowIncome` FROM `app_user_selling_water` WHERE STATUS = 1 AND `user_id` = #{userId}  ) AS `auswd`;
    </select>
</mapper>