<?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
   aur.user_id,
   aur.bind_time as `leagueTime`,
	 IFNULL(ausww.income,0)  AS `income`
FROM
	`app_user_relation` AS `aur`
	LEFT JOIN (SELECT DISTINCT
	`source_id` AS userId,
	IFNULL(
	(
	( SELECT IFNULL(SUM( commission ),0) FROM `app_user_selling_water` WHERE source_id = ausw.source_id AND `status` = 0 ) - ( SELECT IFNULL(SUM( commission ),0) FROM `app_user_selling_water` WHERE source_id = ausw.source_id AND `status` = 1 )
	),
	0
	) AS `income`
FROM
	`app_user_selling_water` AS ausw) AS ausww ON ausww.userId=aur.user_id
	WHERE aur.parent_id=#{parentId}
	ORDER BY ausww.`income` DESC
    </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>

    <select id="selectSellerWalterByUserIdAndWating" resultType="com.github.wxiaoqi.security.admin.vo.SellingWalletVo">
        <!--
          SELECT
	e.*,
	f.order_no AS `f_order`,
	f.outcommission,
	( IFNULL( e.incommission, 0 ) - IFNULL( f.outcommission, 0 ) ) AS `final_commission` ,
	f.sellerStatus,
	f.cancelTime
FROM
	(
SELECT
	a.*,
	b.incommission
FROM
	( SELECT * FROM `app_user_selling_water` WHERE STATUS = 0 and user_id=313 ) AS `a`
	INNER JOIN ( SELECT order_no, IFNULL( SUM( commission ), 0 ) AS `incommission` FROM `app_user_selling_water` WHERE `status` = 0 and user_id=313 GROUP BY `order_no` ) AS `b` ON a.order_no = b.order_no
	) AS `e`
	LEFT JOIN (
SELECT
	c.order_no,
	d.outcommission,
   1 AS `sellerStatus`,
	 c.crt_time AS `cancelTime`
FROM
	( SELECT * FROM `app_user_selling_water` WHERE STATUS = 1 and user_id=313 ) AS `c`
	INNER JOIN ( SELECT order_no,IFNULL( SUM( commission ), 0 ) AS `outcommission` FROM `app_user_selling_water` WHERE `status` = 1 and user_id=313 GROUP BY `order_no` ) AS `d` ON c.order_no = d.order_no
	) AS `f` ON e.order_no = f.order_no
          -->


        SELECT w.*,o.commission as commission ,o.type AS `sellerStatus` FROM (
        SELECT t.order_no,IFNULL(SUM(t.z),0)-IFNULL(SUM(t.f),0) as commission,IF(SUM(t.f)>0,'1',0)as type FROM (

        SELECT order_no,commission as z,0 as f FROM app_user_selling_water WHERE user_id=#{userId}
        AND `status`=0 and commission>0
        UNION
        SELECT order_no,0 as z,commission as f FROM app_user_selling_water WHERE user_id=#{userId}
        AND `status`=1 and commission>0
        ) as t
        GROUP BY t.order_no
        ) o LEFT JOIN ( SELECT order_no,user_id,source_id,order_type,img,price,title,waiting,`crt_time` FROM
        app_user_selling_water WHERE
        id IN ( SELECT MAX( id ) FROM app_user_selling_water GROUP BY order_no)) w
        ON o.order_no=w.order_no
        where 1=1
        <if test="wating!=null">
            and `waiting`= #{wating} and  <![CDATA[
                o.`type`<>1 ]]>
        </if>
        ORDER BY `crt_time`
    </select>
    <select id="selectSellingWaterPage"
            resultType="com.github.wxiaoqi.security.admin.dto.UserSellingWaterAdminDTO">
        SELECT
        ausw.*,
        aul.username AS `phone`,
        IFNULL(aud.realname,aud.nickname) AS  `sourceName`,
        IFNULL(audd.realname,audd.nickname) AS `userName`
        FROM
        ( SELECT * FROM `app_user_selling_water` WHERE 1 = 1
        <if test="status !=null">
            AND `status`=#{status}
        </if>
        <if test="waiting !=null">
            AND `waiting`=#{waiting}
        </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 `ausw`
        INNER JOIN ( SELECT * FROM `app_user_login`
        <if test="phone!=null and phone!=' '">
            WHERE `username` = #{phone}
        </if>
        ) AS aul ON aul.id = ausw.user_id
        INNER JOIN `app_user_detail` AS aud ON aud.userid = ausw.source_id
        INNER JOIN ( SELECT * FROM `app_user_detail`
        <if test="userName!=null and userName!=' '">
            WHERE `realname` LIKE CONCAT('%',#{userName},'%') OR `nickname` LIKE CONCAT('%',#{userName},'%')
        </if>) AS audd ON audd.userid = ausw.user_id
    </select>
</mapper>