<?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.ruoyi.homepage.mapper.HomePageMapper">


	<select id="statistics" resultType="com.ruoyi.homepage.model.result.StatisticsResult">
		SELECT
			'累计入驻设计师' position_0,
			( SELECT COUNT( 1 ) FROM t_customer t WHERE t.del_flag != 2 ) position_1,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_customer t
				WHERE
					t.del_flag != 2
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )) position_2,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_customer t
				WHERE
					t.del_flag != 2
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 DAY ), '%Y-%m-%d' )) position_3
		UNION ALL
		SELECT
			'累计入驻表现师' position_0,
			( SELECT COUNT( 1 ) FROM t_store t WHERE t.del_flag != 2 ) position_1,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_store t
				WHERE
					t.del_flag != 2
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )) position_2,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_store t
				WHERE
					t.del_flag != 2
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 DAY ), '%Y-%m-%d' )) position_3
		UNION ALL
		SELECT
			'累计入驻销售人员' position_0,
			( SELECT COUNT( 1 ) FROM t_sale t WHERE t.del_flag != 2 ) position_1,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_sale t
				WHERE
					t.del_flag != 2
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )) position_2,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_sale t
				WHERE
					t.del_flag != 2
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 DAY ), '%Y-%m-%d' )) position_3
		UNION ALL
		SELECT
			'累计订单数' position_0,
			( SELECT COUNT( 1 ) FROM t_order t WHERE t.del_flag = 1 AND t.`status` >= 4 ) position_1,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_order t
				WHERE
					t.del_flag = 1
				  AND t.`status` >= 4
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )) position_2,
			(
				SELECT
					COUNT( 1 )
				FROM
					t_order t
				WHERE
					t.del_flag = 1
				  AND t.`status` >= 4
				  AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 DAY ), '%Y-%m-%d' )) position_3
		UNION ALL
		SELECT
			'累计订单金额' position_0,
			IFNULL(( SELECT SUM( t.amount ) FROM t_order t WHERE t.del_flag = 1 AND t.`status` >= 4 ), 0 ) + '元' position_1,
			IFNULL((
					   SELECT
						   SUM( t.amount )
					   FROM
						   t_order t
					   WHERE
						   t.del_flag = 1
						 AND t.`status` >= 4
						 AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )),
				   0
				) position_2,
			IFNULL((
					   SELECT
						   SUM( t.amount )
					   FROM
						   t_order t
					   WHERE
						   t.del_flag = 1
						 AND t.`status` >= 4
						 AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 DAY ), '%Y-%m-%d' )),
				   0
				) position_3

		UNION ALL
		SELECT
			'平台累计服务费' position_0,
			IFNULL(( SELECT SUM( t.service_charge ) FROM t_withdrawal_record t WHERE t.del_flag = 1 AND t.`status` = 2 ), 0 ) + '元' position_1,
			IFNULL((
					   SELECT
						   SUM( t.service_charge )
					   FROM
						   t_withdrawal_record t
					   WHERE
						   t.del_flag = 1
						 AND t.`status` = 2
						 AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )),
				   0
				) position_2,
			IFNULL((
					   SELECT
						   SUM( t.service_charge )
					   FROM
						   t_withdrawal_record t
					   WHERE
						   t.del_flag = 1
						 AND t.`status` = 2
						 AND DATE_FORMAT( t.create_time, '%Y-%m-%d' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 DAY ), '%Y-%m-%d' )),
				   0
				) position_3 UNION ALL
		SELECT
			'累计提现金额' position_0,
			IFNULL((
					   SELECT
						   SUM( t.service_charge + t.price )
					   FROM
						   t_withdrawal_record t
					   WHERE
						   t.del_flag = 1
						 AND t.`status` = 2
				   ),
				   0
				) + '元' position_1,
			IFNULL((
					   SELECT
						   SUM( t.service_charge + t.price )
					   FROM
						   t_withdrawal_record t
					   WHERE
						   t.del_flag = 1
						 AND t.`status` = 2
						 AND DATE_FORMAT( t.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' )),
				   0
				) position_2,
			IFNULL((
					   SELECT
						   SUM( t.service_charge + t.price )
					   FROM
						   t_withdrawal_record t
					   WHERE
						   t.del_flag = 1
						 AND t.`status` = 2
						 AND DATE_FORMAT( t.create_time, '%Y-%m' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 MONTH ), '%Y-%m' )),
				   0
				) position_3 UNION ALL
		SELECT
			'销售员累计佣金' position_0,
			IFNULL(( SELECT SUM( t.income ) FROM t_sale t ), 0 ) + '元' position_1,
			IFNULL((
					   SELECT
						   SUM( t.income )
					   FROM
						   t_sale t
					   WHERE
						   DATE_FORMAT( t.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' )),
				   0
				) position_2,
			IFNULL((
					   SELECT
						   SUM( t.income )
					   FROM
						   t_sale t
					   WHERE
							   DATE_FORMAT( t.create_time, '%Y-%m' ) = DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 MONTH ), '%Y-%m' )),
				   0
				) position_3
	</select>

	<select id="monthOrderAmountChart" resultType="com.ruoyi.homepage.model.result.ChartResult">
		SELECT
			DATE_FORMAT(o.create_time, '%Y-%m-%d') unit,
			IFNULL(SUM(o.amount), 0) amount,
			sum(
					CASE
						WHEN o. STATUS = 1
							OR o. STATUS = 2
								 AND o.source_user_type = 1 THEN
							IFNULL(o.first_price, 0)
						WHEN o. STATUS = 2
							AND o.source_user_type = 2 THEN
							IFNULL(o.first_price, 0)
						ELSE
							IFNULL(o.amount, 0)
						END
			) hasReceiveAmount
		FROM
			t_order o
		WHERE o.first_status = 2 AND
			DATE_FORMAT(o.create_time, '%Y-%m') = #{month}
		GROUP BY
			DATE_FORMAT(o.create_time, '%Y-%m-%d')
		ORDER BY
			DATE_FORMAT(o.create_time, '%Y-%m-%d')
	</select>


	<select id="yearOrderAmountChart" resultType="com.ruoyi.homepage.model.result.ChartResult">
		SELECT
			DATE_FORMAT(o.create_time, '%Y-%m') unit,
			IFNULL(SUM(o.amount), 0) amount,
			sum(
					CASE
						WHEN o. STATUS = 1
							OR o. STATUS = 2
								 AND o.source_user_type = 1 THEN
							IFNULL(o.first_price, 0)
						WHEN o. STATUS = 2
							AND o.source_user_type = 2 THEN
							IFNULL(o.first_price, 0)
						ELSE
							IFNULL(o.amount, 0)
						END
			) hasReceiveAmount
		FROM
			t_order o
		WHERE o.first_status = 2 AND
			DATE_FORMAT(o.create_time, '%Y') = #{year}
		GROUP BY
			DATE_FORMAT(o.create_time, '%Y-%m')
		ORDER BY
			DATE_FORMAT(o.create_time, '%Y-%m')
	</select>


	<select id="monthOrderAmountChartNear" resultType="com.ruoyi.homepage.model.result.ChartResult">
		SELECT
			_unit.unit,
			IFNULL( SUM( o.amount ), 0 ) amount
		FROM
			(
				SELECT
					DATE_FORMAT( now(), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 1 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 2 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 3 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 4 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 5 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 6 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 7 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 8 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 9 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 10 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 11 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 12 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 13 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 14 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 15 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 16 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 17 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 18 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 19 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 20 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 21 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 22 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 23 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 24 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 25 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 26 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 27 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 28 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 29 DAY ), '%Y-%m-%d' ) unit
				FROM
					DUAL
			) _unit
				LEFT JOIN t_order o ON DATE_FORMAT( o.create_time, '%Y-%m-%d' ) = _unit.unit
				AND o.first_status = 2
		GROUP BY
			_unit.unit
		ORDER BY
			STR_TO_DATE( _unit.unit, '%Y-%m-%d' ) ASC
	</select>


	<select id="yearOrderAmountChartNear" resultType="com.ruoyi.homepage.model.result.ChartResult">
		SELECT
			_unit.unit,
			IFNULL( SUM( o.amount ), 0 ) amount
		FROM
			(
				SELECT
					DATE_FORMAT( now(), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 1 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 2 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 3 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 4 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 5 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 6 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 7 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 8 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 9 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 10 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 11 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL UNION ALL
				SELECT
					DATE_FORMAT( date_sub( now(), INTERVAL 12 MONTH ), '%Y-%m' ) unit
				FROM
					DUAL
			) _unit
				LEFT JOIN t_order o ON DATE_FORMAT( o.create_time, '%Y-%m' ) = _unit.unit
				AND o.first_status = 2
		GROUP BY
			_unit.unit
		ORDER BY
			STR_TO_DATE( CONCAT( _unit.unit, '-01' ), '%Y-%m-%d' ) ASC
	</select>

	<select id="rankingCustomerList" resultType="com.ruoyi.homepage.model.result.RankingResult">
		SELECT
			t.nickname,
			t.consumption_amount amount
		FROM
			t_customer t
		WHERE
			t.del_flag = 1
		ORDER BY
			t.consumption_amount DESC,
			t.id
		limit 0,10
	</select>

	<select id="rankingStoreList" resultType="com.ruoyi.homepage.model.result.RankingResult">
		SELECT
			t.nickname,
			t.income amount
		FROM
			t_store t
		WHERE
			t.del_flag = 1
		ORDER BY
			t.income DESC,
			t.id
		limit 0,10
	</select>

	<select id="rankingSaleList" resultType="com.ruoyi.homepage.model.result.RankingResult">
		SELECT
			t.nickname,
			t.income amount
		FROM
			t_sale t
		WHERE
			t.del_flag = 1
		ORDER BY
			t.income DESC,
			t.id
		limit 0,10
	</select>
	<select id="classificationProportion"
			resultType="com.ruoyi.homepage.model.result.ClassificationProportionResult">
		SELECT
		o.works_type_id,
		t2.`name` worksTypeName,
		t3.name parentWorksTypeName,
		sum(o.amount) amount
		FROM
		t_order o
		LEFT JOIN t_works_type t2 ON o.works_type_id = t2.id
		LEFT JOIN t_works_type t3 ON t2.pid = t3.id
		WHERE
		o.first_status = 2
		<if test="req.timeType!=null and req.timeType==1">
			AND DATE_FORMAT(o.create_time, '%Y-%m') = #{req.month}
		</if>
		<if test="req.timeType!=null and req.timeType==2">
			AND DATE_FORMAT(o.create_time, '%Y') = #{req.year}
		</if>
		GROUP BY
		o.works_type_id
		order by o.works_type_id
	</select>

	<select id="newOldCustomers"
			resultType="com.ruoyi.homepage.model.result.NewOldCustomersResult">

		SELECT DISTINCT
		o.customer_id,
		<if test="req.timeType!=null and req.timeType==1">
			CASE
			WHEN
			#{req.month}  > DATE_FORMAT(t2.create_time, '%Y-%m')  then 2
			ELSE
			1
			END isNewCustomer
		</if>
		<if test="req.timeType!=null and req.timeType==2">
			CASE
			WHEN
			#{req.month}  > DATE_FORMAT(t2.create_time, '%Y-%m')  then 2
			ELSE
			1
			END isNewCustomer
		</if>

		FROM
		t_order o
		LEFT JOIN t_customer t2 ON o.customer_id = t2.id
		WHERE
		o.first_status = 2
		AND t2.del_flag != 2
		<if test="req.timeType!=null and req.timeType==1">
			AND DATE_FORMAT(o.create_time, '%Y-%m') = #{req.month}
		</if>
		<if test="req.timeType!=null and req.timeType==2">
			AND DATE_FORMAT(o.create_time, '%Y') = #{req.year}
		</if>
	</select>


</mapper>