<?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
	( IFNULL(auswu.upIncome,0) - IFNULL(auswd.dowIncome,0) ) AS `income`
FROM
	( SELECT IFNULL(SUM( commission ),0) AS upIncome FROM `app_user_selling_water` WHERE STATUS = 0 AND waiting=1 AND `user_id` = #{userId}  ) AS `auswu`,
	( SELECT IFNULL(SUM( commission ),0) AS `dowIncome` FROM `app_user_selling_water` WHERE STATUS = 1 AND waiting=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--> CASE
        WHEN fcommission>0 THEN
        (CASE
        WHEN  o.commission=0 THEN 1
        ELSE 0 END )
        ELSE 0 END  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 -->
        IFNULL(SUM(t.f), 0) as fcommission
        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` DESC
    </select>
    <select id="selectSellingWaterPage"
            resultType="com.github.wxiaoqi.security.admin.dto.UserSellingWaterAdminDTO">
        SELECT
        ausw.*,
        aul.username AS `phone`,
        (
        CASE
        WHEN aud.realname = "" THEN
        aull.username
        WHEN aud.realname IS NULL THEN
        aull.username
        ELSE
        aud.realname
        END
        )AS  `sourceName`,
        (
        CASE
        WHEN audd.realname = "" THEN
        aul.username
        WHEN audd.realname IS NULL THEN
        aul.username
        ELSE
        audd.realname
        END
        )  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_login` AS `aull` ON aull.id=ausw.source_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
      ORDER BY ausw.crt_time DESC
    </select>

    <select id="statisticsStatffSellerWaterByUserIdsAndTime"
            resultType="com.github.wxiaoqi.security.admin.dto.AppUserSellingWaterDTO">
        SELECT
   `auswu`.user_id, ( IFNULL(auswu.upIncome,0) - IFNULL(auswd.dowIncome,0) ) AS `commission`
   FROM
    ( SELECT `user_id` as `userId`, IFNULL(SUM( commission ),0) AS upIncome FROM `app_user_selling_water` where STATUS = 0 and waiting=1
    AND `crt_time` between #{startTime} and #{endTime}
    <if test="userIds!=null and userIds.size!=0">
        AND `user_id` IN <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
         #{userId}
    </foreach>
    </if> group by user_id) AS `auswu`
     left join
    ( SELECT `user_id`,IFNULL(SUM( commission ),0) AS `dowIncome` FROM `app_user_selling_water` where STATUS = 1 and waiting=1
     and `crt_time` between #{startTime} and #{endTime}
    <if test="userIds!=null and userIds.size!=0">
        AND `user_id` IN <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
        #{userId}
    </foreach>
    </if> group by user_id) AS `auswd`
    on auswd.user_id=auswu.user_id
    </select>
</mapper>