<?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.yonge.cooleshow.biz.dal.dao.HomeDao">
	<select id="getUserToDoNum" resultType="com.yonge.cooleshow.biz.dal.vo.res.HomeUserToDoNum">
		select
			(select count(1) from teacher_auth_entry_record a
				left join sys_user u on a.user_id_ = u.id_
			 	where u.del_flag_ =0 and a.teacher_auth_status_ = 'DOING') as entryNum,
			(select count(1) from teacher_auth_musician_record a
				left join sys_user u on a.user_id_ = u.id_
			 	where u.del_flag_ =0 and a.teacher_auth_status_ = 'DOING') as musicianNum,
        	(select count(1) from music_sheet a
				left join sys_user u on a.user_id_ = u.id_
			 	where u.del_flag_ =0 and a.del_flag_ = 0 and a.audit_status_ = 'DOING') as musicNum,
        	(select count(1) from video_lesson_auth_record where audit_status_ = 'DOING') as videoCourseNum,
			(select count(1) from teacher_style_video a
				left join sys_user u on a.user_id_ = u.id_
			 	where u.del_flag_ =0 and a.del_flag_ = 0 and a.auth_status_ = 'DOING') as styleNum
        from dual
	</select>

    <select id="totalTeacher" resultType="com.yonge.cooleshow.biz.dal.vo.res.HomeTotalTeacher">
		select
			<if test="timeType != null and timeType == 'MONTH'">
				t.sys_day_ymd_ as timeStr,
			</if>
			<if test="timeType != null and timeType == 'YEAR'">
				t.sys_day_ym_ as timeStr,
			</if>
			sum(a.registerNum) as registerNum,
			sum(b.entryNum) as entryNum,
			sum(c.liveNum) as liveNum,
			sum(d.musicianNum) as musicianNum
		from sys_day t
		left join (
			select
				DATE_FORMAT(t.create_time_,'%Y-%m-%d') as timeStr,
				count(1) as registerNum
			from teacher t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.create_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.create_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.create_time_,'%Y-%m-%d')
		) a on t.sys_day_ymd_ = a.timeStr
		left join (
			select
				DATE_FORMAT(t.create_time_,'%Y-%m-%d') as timeStr,
				count(1) as entryNum
			from teacher t
			<where>
				t.entry_flag_ = 1
				<if test="param.startTime !=null">
					<![CDATA[AND t.entry_auth_date_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.entry_auth_date_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.create_time_,'%Y-%m-%d')
		) b on t.sys_day_ymd_ = b.timeStr
		left join (
			select
				DATE_FORMAT(t.create_time_,'%Y-%m-%d') as timeStr,
				count(1) as liveNum
			from teacher t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.live_date_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.live_date_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.create_time_,'%Y-%m-%d')
		) c on t.sys_day_ymd_ = c.timeStr
		left join (
			select
				DATE_FORMAT(t.create_time_,'%Y-%m-%d') as timeStr,
				count(1) as musicianNum
			from teacher t
			<where>
				t.musician_flag_ = 1
				<if test="param.startTime !=null">
					<![CDATA[AND t.musician_date_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.musician_date_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.create_time_,'%Y-%m-%d')
		) d on t.sys_day_ymd_ = d.timeStr
		<where>
			<if test="param.startTime !=null">
				<![CDATA[AND t.sys_day_ >= #{param.startTime} ]]>
			</if>
			<if test="param.endTime !=null">
				<![CDATA[AND t.sys_day_ < #{param.endTime} ]]>
			</if>
		</where>
		<if test="timeType != null and timeType == 'MONTH'">
			group by t.sys_day_ymd_
		</if>
		<if test="timeType != null and timeType == 'YEAR'">
			group by t.sys_day_ym_
		</if>
		order by t.sys_day_
	</select>

	<select id="totalStudent" resultType="com.yonge.cooleshow.biz.dal.vo.res.HomeTotalStudent">
		select
			<if test="timeType != null and timeType == 'MONTH'">
				t.sys_day_ymd_ as timeStr,
			</if>
			<if test="timeType != null and timeType == 'YEAR'">
				t.sys_day_ym_ as timeStr,
			</if>
			sum(a.registerNum) as registerNum,
			sum(b.vipNum) as vipNum,
			sum(c.practiceNum) as practiceNum,
			sum(d.videoNum) as videoNum,
			sum(e.liveNum) as liveNum,
			sum(f.musicNum) as musicNum,
			sum(g.shoppingNum) as shoppingNum
		from sys_day t
		left join (
			select
				DATE_FORMAT(t.create_time_,'%Y-%m-%d') as timeStr,
				count(1) as registerNum
			from student t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.create_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.create_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.create_time_,'%Y-%m-%d')
		) a on t.sys_day_ymd_ = a.timeStr
		left join (
			select
				DATE_FORMAT(t.first_vip_time_,'%Y-%m-%d') as timeStr,
				count(1) as vipNum
			from student_time t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.first_vip_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.first_vip_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.first_vip_time_,'%Y-%m-%d')
		) b on t.sys_day_ymd_ = b.timeStr
		left join (
			select
				DATE_FORMAT(t.first_practice_time_,'%Y-%m-%d') as timeStr,
				count(1) as practiceNum
			from student_time t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.first_practice_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.first_practice_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.first_practice_time_,'%Y-%m-%d')
		) c on t.sys_day_ymd_ = c.timeStr
		left join (
			select
				DATE_FORMAT(t.first_video_time_,'%Y-%m-%d') as timeStr,
				count(1) as videoNum
			from student_time t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.first_video_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.first_video_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.first_video_time_,'%Y-%m-%d')
		) d on t.sys_day_ymd_ = d.timeStr
		left join (
			select
				DATE_FORMAT(t.first_live_time_,'%Y-%m-%d') as timeStr,
				count(1) as liveNum
			from student_time t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.first_live_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.first_live_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.first_live_time_,'%Y-%m-%d')
		) e on t.sys_day_ymd_ = e.timeStr
		left join (
			select
				DATE_FORMAT(t.first_music_time_,'%Y-%m-%d') as timeStr,
				count(1) as musicNum
			from student_time t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.first_music_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.first_music_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.first_music_time_,'%Y-%m-%d')
		) f on t.sys_day_ymd_ = f.timeStr
		left join (
			select
				DATE_FORMAT(t.first_mall_time_,'%Y-%m-%d') as timeStr,
				count(1) as shoppingNum
			from student_time t
			<where>
				<if test="param.startTime !=null">
					<![CDATA[AND t.first_mall_time_ >= #{param.startTime} ]]>
				</if>
				<if test="param.endTime !=null">
					<![CDATA[AND t.first_mall_time_ < #{param.endTime} ]]>
				</if>
			</where>
			group by DATE_FORMAT(t.first_mall_time_,'%Y-%m-%d')
		) g on t.sys_day_ymd_ = g.timeStr
		<where>
			<if test="param.startTime !=null">
				<![CDATA[AND t.sys_day_ >= #{param.startTime} ]]>
			</if>
			<if test="param.endTime !=null">
				<![CDATA[AND t.sys_day_ < #{param.endTime} ]]>
			</if>
		</where>
		<if test="timeType != null and timeType == 'MONTH'">
			group by t.sys_day_ymd_
		</if>
		<if test="timeType != null and timeType == 'YEAR'">
			group by t.sys_day_ym_
		</if>
		order by t.sys_day_
	</select>
</mapper>