<?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`
	INNER JOIN (select `id` from `app_user_login` where  isdel=0)as `aul` ON aul.id=aur.user_id
	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 wa.* FROM
        (
        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 ) wa
        where 1=1
        <if test="wating!=null">
            and  wa.waiting= #{wating} and  <![CDATA[
                wa.sellerStatus<>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>

    <select id="getCommisionByOrder" resultType="com.github.wxiaoqi.security.admin.entity.AppUserSellingWater">
        SELECT
        r.user_id as userId,
        SUM(r.zCommission)-SUM(r.fCommission) as commission,
        SUM(r.zCommission) as price
        FROM
        (
        SELECT user_id,commission as zCommission,0 as fCommission,price FROM app_user_selling_water WHERE order_id = #{orderId} and `status`= 0 and waiting = 1
        UNION ALL
        SELECT user_id,0 as zCommission,commission as fCommission,price  FROM app_user_selling_water WHERE order_id = #{orderId} and `status`= 1 and waiting = 1
        ) r
        GROUP BY r.user_id
    </select>

    <select id="findSellingWaterPageb" resultType="com.github.wxiaoqi.security.admin.dto.AppUserSellingWaterDTO">
        SELECT
            wa.user_id AS 'userId',
            SUM(wa.commission) as commission ,
            wa.crt_time as crtTime,
            wa.source_id as sourceId,
            ul.username as sourceName
        FROM
            app_user_selling_water wa
        LEFT JOIN base_order_accept_detailed bo ON wa.accept_id = bo.id
        LEFT JOIN app_user_login ul ON wa.source_id = ul.id
        where wa.waiting = 1
        and wa.user_id = #{userId}
        and bo.division_type = #{divisionType}
        and bo.`status` = 1
        GROUP BY wa.source_id
    </select>
</mapper>