<?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.buy507.mall.dao.DmsQueryDao">
	
	<!-- 查询今日订单 -->
	<select id="selectTodayOrder" resultMap="com.buy507.mall.mapper.OmsOrderMapper.BaseResultMap">
		SELECT * FROM oms_order o WHERE TO_DAYS(o.create_time) = TO_DAYS(NOW())
	</select>

	<!-- 查询昨日销售总额 -->
	<select id="selectYesterdaySellAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(o.total_amount), 0) FROM oms_order o WHERE TO_DAYS(NOW()) - TO_DAYS(o.create_time) = 1 AND o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询本周订单数量 -->
	<select id="selectCurrentWeekOrderCount" resultType="java.lang.Integer">
		SELECT COUNT(*) FROM oms_order o WHERE YEARWEEK(DATE_FORMAT(o.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) AND o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询本周销售总额 -->
	<select id="selectCurrentWeekSellAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(o.total_amount), 0) FROM oms_order o WHERE YEARWEEK(DATE_FORMAT(o.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) AND o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询本月订单数量 -->
	<select id="selectCurrentMonthOrderCount" resultType="java.lang.Integer">
		SELECT COUNT(*) FROM oms_order o WHERE DATE_FORMAT(o.create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') AND o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询本月销售总额 -->
	<select id="selectCurrentMonthSellAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(o.total_amount), 0) FROM oms_order o WHERE DATE_FORMAT(o.create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') AND o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询本年订单数量 -->
	<select id="selectCurrentYearOrderCount" resultType="java.lang.Integer">
		SELECT COUNT(*) FROM oms_order o WHERE YEAR(o.create_time) = YEAR(NOW()) AND o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询本年销售总额 -->
	<select id="selectCurrentYearSellAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(o.total_amount), 0) FROM oms_order o WHERE YEAR(o.create_time) = YEAR(NOW()) AND o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询销售总金额 -->
	<select id="selectAllSellAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(o.total_amount), 0) FROM oms_order o WHERE o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	
	<!-- 查询积分支付总金额-->
	<select id="selectBalancePayemntAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(o.integration_amount), 0) FROM oms_order o WHERE o.payment_time is not null AND o.status in (1, 2, 3)
	</select>
	
	<!-- 查询订单图表数据 -->
	<select id="selectOrderChart" resultType="com.buy507.mall.dto.OrderChartResult">
		SELECT 
			DATE_FORMAT(o.create_time, '%Y-%m-%d') AS orderDate,
			COUNT(*) AS orderCount,
			SUM(o.total_amount) AS orderAmount,
			SUM(o.integration_amount) AS balanceAmount
		FROM oms_order o
		WHERE <![CDATA[   DATE_FORMAT(o.create_time, '%Y-%m-%d') >= DATE_FORMAT(#{startTime}, '%Y-%m-%d')  ]]>
		AND	  <![CDATA[   DATE_FORMAT(o.create_time, '%Y-%m-%d') <= DATE_FORMAT(#{endTime}, '%Y-%m-%d')   ]]>
		AND o.payment_time IS NOT NULL AND o.status IN (1, 2, 3)
		GROUP BY orderDate
		ORDER BY orderDate ASC
	</select>
	
	<!-- 查询提现图表数据 -->
	<select id="selectWithdrawChart" resultType="com.buy507.mall.dto.WithdrawChartResult">
		SELECT 
			DATE_FORMAT(mwa.create_time, '%Y-%m-%d') AS withdrawDate,
			COUNT(*) AS withdrawCount,
			SUM(mwa.value) AS withdrawAmount
		FROM dms_member_withdraw_apply mwa
		WHERE <![CDATA[   DATE_FORMAT(mwa.create_time, '%Y-%m-%d') >= DATE_FORMAT(#{startTime}, '%Y-%m-%d')  ]]>
		AND	  <![CDATA[   DATE_FORMAT(mwa.create_time, '%Y-%m-%d') <= DATE_FORMAT(#{endTime}, '%Y-%m-%d')   ]]>
		AND mwa.payment_status = 1
		GROUP BY withdrawDate
		ORDER BY withdrawDate ASC
	</select>
	
	<!-- 查询本周提现数量 -->
	<select id="selectCurrentWeekWithdrawCount" resultType="java.lang.Integer">
		SELECT COUNT(*) FROM dms_member_withdraw_apply mwa WHERE YEARWEEK(DATE_FORMAT(mwa.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) AND mwa.payment_status = 1
	</select>
	
	<!-- 查询本周提现总额 -->
	<select id="selectCurrentWeekWithdrawAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(mwa.value), 0) FROM dms_member_withdraw_apply mwa WHERE YEARWEEK(DATE_FORMAT(mwa.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) AND mwa.payment_status = 1
	</select>
	
	<!-- 查询本月提现数量 -->
	<select id="selectCurrentMonthWithdrawCount" resultType="java.lang.Integer">
		SELECT COUNT(*) FROM dms_member_withdraw_apply mwa WHERE DATE_FORMAT(mwa.create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') AND mwa.payment_status = 1
	</select>
	
	<!-- 查询本月提现总额 -->
	<select id="selectCurrentMonthWithdrawAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(mwa.value), 0) FROM dms_member_withdraw_apply mwa WHERE DATE_FORMAT(mwa.create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') AND mwa.payment_status = 1
	</select>
	
	<!-- 查询本年提现数量 -->
	<select id="selectCurrentYearWithdrawCount" resultType="java.lang.Integer">
		SELECT COUNT(*) FROM dms_member_withdraw_apply mwa WHERE YEAR(mwa.create_time) = YEAR(NOW()) AND mwa.payment_status = 1
	</select>
	
	<!-- 查询本年提现总额 -->
	<select id="selectCurrentYearWithdrawAmount" resultType="java.math.BigDecimal">
		SELECT IFNULL(SUM(mwa.value), 0) FROM dms_member_withdraw_apply mwa WHERE YEAR(mwa.create_time) = YEAR(NOW()) AND mwa.payment_status = 1
	</select>
	
	<!-- 查询会员团队信息 -->
    <select id="selectMemberTeamInfo" resultType="com.buy507.mall.dto.MemberTeamInfoResult">

        SELECT m.id,
        	   m.member_level as memberLevel,
        	   m.nickname as nickname,
        	   m.phone as phone,
        	   m.gender as gender,
        	   m.store_status as storeStatus,
        	   (SELECT IFNULL(SUM(o.total_amount), 0) FROM oms_order o WHERE m.id = o.member_id AND o.payment_time is not null AND o.status in (1, 2, 3)) as personalConsume,
        	   ttc.value as teamConsume,
        	   im.nickname as inviterName,
        	   im.id as inviterId,
        	   m.create_time as createTime
          FROM
            ums_member m 
     LEFT JOIN dms_team_level tl ON m.id = tl.member_id
     LEFT JOIN dms_team_total_consume ttc ON m.id = ttc.member_id
     LEFT JOIN ums_member im ON im.id = tl.parent_id
         WHERE
       		1=1
       	<if test="nickname!=null">
        	AND m.nickname like "%"#{nickname}"%"
        </if>
        <if test="memberLevel!=null">
        	AND m.member_level = #{memberLevel}
        </if>
        <if test="storeStatus!=null">
        	AND m.store_status = #{storeStatus}
        </if>
		<if test="inviterName!=null">
        	AND im.nickname like "%"#{inviterName}"%"
        </if>
        <if test="inviterId!=null">
        	AND im.id = #{inviterId}
        </if>
	    
	    ORDER BY m.create_time DESC
	    
    </select>
    
    
    <!-- 查询会员购物信息 -->
	<select id="selectMemberShoppingInfo" resultType="com.buy507.mall.dto.MemberShoppingInfoResult">
		SELECT 
			m.real_name as name,
			m.nickname as nickname,
			m.phone,
			oi.product_name as productName,
			oi.product_quantity as quantity,
			oi.product_price as price,
			(oi.product_quantity * oi.product_price) as totalPrice,
			o.create_time as createTime
		FROM oms_order_item oi
		LEFT JOIN oms_order o ON oi.order_id = o.id
		LEFT JOIN ums_member m ON o.member_id = m.id
		WHERE o.payment_time is not null AND o.status in (1, 2, 3)
		<if test="startTime!=null">
        	AND	  <![CDATA[   DATE_FORMAT(o.create_time, '%Y-%m-%d') >= DATE_FORMAT(#{startTime}, '%Y-%m-%d')  ]]>
        </if>
        <if test="endTime!=null">
        	AND	  <![CDATA[   DATE_FORMAT(o.create_time, '%Y-%m-%d') <= DATE_FORMAT(#{endTime}, '%Y-%m-%d')   ]]>
        </if>
		ORDER BY o.create_time DESC
	</select>
    
    
</mapper>