<?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="StatisticsDayMapper">

        <resultMap id="StatisticsDayResult" type="StatisticsDay">
            <result property="id" column="ID"/>
            <result property="statisticsTime" column="STATISTICS_TIME"/>
            <result property="loginNum" column="LOGIN_NUM"/>
            <result property="createTime" column="CREATE_TIME"/>
            <result property="registeredNum" column="REGISTERED_NUM"/>
			<result property="videoViewingNum" column="VIDEO_VIEWING_NUM"/>
			<result property="dailyUserNumber" column="DAILY_USER_NUMBER"/>
			<result property="dailyCourseNumber" column="DAILY_COURSE_NUMBER"/>
        </resultMap>

        <sql id="edu_statistics_day_columns">
            edu_statistics_day.id,
            edu_statistics_day.statistics_time,
            edu_statistics_day.login_num,
            edu_statistics_day.create_time,
            edu_statistics_day.registered_num,
            edu_statistics_day.video_viewing_num,
            edu_statistics_day.daily_user_number,
            edu_statistics_day.daily_course_number
        </sql>
		 <sql id="edu_statistics_day_columns_notnull">
            IFNULL(edu_statistics_day.id,0) as id,
            IFNULL(edu_statistics_day.statistics_time,'') as statistics_time,
            IFNULL(edu_statistics_day.login_num,0) as login_num,
            IFNULL(edu_statistics_day.create_time,'') as create_time,
            IFNULL(edu_statistics_day.registered_num,0) as registered_num,
            IFNULL(edu_statistics_day.video_viewing_num,0) as video_viewing_num,
            IFNULL(edu_statistics_day.daily_user_number,0) as daily_user_number,
            IFNULL(edu_statistics_day.daily_course_number,0) as daily_course_number
        </sql>
        <sql id="edu_statistics_day_columns_sum">
        	IFNULL(sum(edu_statistics_day.id),0) as id,
            '' as statistics_time,
            IFNULL(sum(edu_statistics_day.login_num),0) as login_num,
            '' as create_time,
            IFNULL(sum(edu_statistics_day.registered_num),0) as registered_num,
            IFNULL(sum(edu_statistics_day.video_viewing_num),0) as video_viewing_num,
            IFNULL(sum(edu_statistics_day.daily_user_number),0) as daily_user_number,
            IFNULL(sum(edu_statistics_day.daily_course_number),0) as daily_course_number
        </sql>
        <sql id="edu_statistics_day_properties">
            #{id},
            #{statisticsTime},
            #{loginNum},
            #{createTime}
        </sql>

        <!-- 活跃人数 -->
		<sql id="edu_statistics_loginNum">
			select IfNULL(count(DISTINCT ul.user_id),0) from edu_user_login_log as ul WHERE DATE_FORMAT(ul.login_time,'%Y-%m-%d')=DATE_FORMAT(#{value},'%Y-%m-%d')
		</sql>
		
		<!-- 注册人数 -->
		<sql id="edu_statistics_registeredNum">
			select IfNULL(count(DISTINCT ur.USER_ID),0) from edu_user as ur WHERE DATE_FORMAT(ur.CREATE_TIME,'%Y-%m-%d')=DATE_FORMAT(#{value},'%Y-%m-%d')
		</sql>
		
		<!-- 添加网站统计web（天） -->
        <insert id="createStatisticsDay" parameterType="Date"  >
            insert INTO edu_statistics_day (<include refid="edu_statistics_day_columns"/>) VALUES (0,DATE_FORMAT(#{value},'%Y-%m-%d'),
			(<include refid="edu_statistics_loginNum"/>),
			now(),(<include refid="edu_statistics_registeredNum"/>),
			(select IfNULL(count(1),0) from edu_course_studyhistory where date_format(edu_course_studyhistory.UPDATE_TIME,'%Y-%m-%d')=date_format(#{value},'%Y-%m-%d')),
			(select IfNULL(count(1),0) from edu_user where edu_user.IS_AVALIBLE=1),
			(select IfNULL(count(1),0) from edu_course where edu_course.IS_AVALIABLE=1))
        </insert>
        
        <!-- 按年查询网站统计 -->
        <select id="getStatisticsByYear" resultMap="StatisticsDayResult" parameterType="Map">
            select
			year.m,
			<include refid="edu_statistics_day_columns_notnull"/>
			FROM
			(SELECT 12 as m UNION ALL
			SELECT 11 UNION ALL
			SELECT 10 UNION ALL
			SELECT 09 UNION ALL
			SELECT 08 UNION ALL
			SELECT 07 UNION ALL
			SELECT 06 UNION ALL
			SELECT 05 UNION ALL
			SELECT 04 UNION ALL
			SELECT 03 UNION ALL
			SELECT 02 UNION ALL
			SELECT 01) as year
			LEFT JOIN
			(
				SELECT
				DATE_FORMAT(edu_statistics_day.statistics_time, '%m') as m,
	            <include refid="edu_statistics_day_columns_sum"/>
	            from edu_statistics_day
	            WHERE DATE_FORMAT(edu_statistics_day.statistics_time, '%Y')=#{nowYear}
	            GROUP BY m
	        ) AS edu_statistics_day
			on year.m = edu_statistics_day.m
        </select>
        <!-- 按月查询网站统计 -->
        <select id="getStatisticsByMonth" resultMap="StatisticsDayResult" parameterType="Map">
	        SELECT
			DATE_FORMAT(day.d, '%d') as d,
			 <include refid="edu_statistics_day_columns_notnull"/>
			FROM
			(SELECT
			(#{nowMonth} - INTERVAL DAY(#{nowMonth}) DAY) + INTERVAL s DAY as d
			FROM
			(SELECT 31 AS s UNION ALL
				SELECT 30 UNION ALL
				SELECT 29 UNION ALL
				SELECT 28 UNION ALL
				SELECT 27 UNION ALL
				SELECT 26 UNION ALL
				SELECT 25 UNION ALL
				SELECT 24 UNION ALL
				SELECT 23 UNION ALL
				SELECT 22 UNION ALL
				SELECT 21 UNION ALL
				SELECT 20 UNION ALL
				SELECT 19 UNION ALL
				SELECT 18 UNION ALL
				SELECT 17 UNION ALL
				SELECT 16 UNION ALL
				SELECT 15 UNION ALL
				SELECT 14 UNION ALL
				SELECT 13 UNION ALL
				SELECT 12 UNION ALL
				SELECT 11 UNION ALL
				SELECT 10 UNION ALL
				SELECT 09 UNION ALL
				SELECT 08 UNION ALL
				SELECT 07 UNION ALL
				SELECT 06 UNION ALL
				SELECT 05 UNION ALL
				SELECT 04 UNION ALL
				SELECT 03 UNION ALL
				SELECT 02 UNION ALL
				SELECT 01 
			) m
			WHERE <![CDATA[s<=day(LAST_DAY(#{nowMonth})]]>)) 
			AS day
			LEFT JOIN
			(
				SELECT
				DATE_FORMAT(edu_statistics_day.statistics_time, '%Y-%m-%d') AS d,
	            <include refid="edu_statistics_day_columns"/>
	            from edu_statistics_day
	            WHERE DATE_FORMAT(edu_statistics_day.statistics_time, '%Y-%m')=#{nowYear}
			) AS edu_statistics_day
			ON day.d = edu_statistics_day.d
		</select>
		
		<!-- 查询网站总统计,不分时间 -->
        <select id="getStatisticsSumMsg" resultMap="StatisticsDayResult">
	        select 
	 			<include refid="edu_statistics_day_columns_sum"/>
			from edu_statistics_day
		</select>
		<!-- 动态查询最近的活跃度 -->
		<select id="getStatisticThirty" resultMap="StatisticsDayResult" parameterType="int">
			select 
	 			<include refid="edu_statistics_day_columns"/>
			from edu_statistics_day
			order by edu_statistics_day.statistics_time DESC
			limit 0,#{value }
		</select>
		<!-- 查询时间段的统计集合 -->
		 <select id="getStatisticsByDate" resultMap="StatisticsDayResult" parameterType="map">
	 		select 
 			<include refid="edu_statistics_day_columns"/>
			from edu_statistics_day
			<where>
				<if test="startTime!=null and startTime!=''">
					and <![CDATA[edu_statistics_day.statistics_time>=#{startTime}]]>
				</if>
				<if test="endTime!=null and endTime!=''">
					and <![CDATA[edu_statistics_day.statistics_time<=#{endTime}]]>
				</if>
			</where>
		 </select>
		 <!-- 日起段删除统计 -->
		 <delete id="delStatisticsByDate" parameterType="map">
		 	delete from edu_statistics_day 
		 	<where>
				<if test="startTime!=null and startTime!=''">
					and <![CDATA[edu_statistics_day.statistics_time>=#{startTime}]]>
				</if>
				<if test="endTime!=null and endTime!=''">
					and <![CDATA[edu_statistics_day.statistics_time<=#{endTime}]]>
				</if>
			</where>
		 </delete>
		 <!-- 删除指定天统计 -->
		 <delete id="delStatisticsDay" parameterType="Date">
		 	delete from edu_statistics_day 
		 	WHERE DATE_FORMAT(edu_statistics_day.statistics_time,'%Y-%m-%d')=DATE_FORMAT(#{value},'%Y-%m-%d')
		 </delete>
	<!-- 今天的登录人数-->
	<select id="statistics_loginNumToday" parameterType="date" resultType="int">
		<include refid="edu_statistics_loginNum"></include>
	</select>
	<!-- 今天的注册人数-->
	<select id="statistics_registeredNumToday" parameterType="date" resultType="int">
		<include refid="edu_statistics_registeredNum"></include>
	</select>

	<!-- 查询网站统计数据 -->
	<select id="getStatisticsDayList" parameterType="hashMap" resultMap="StatisticsDayResult">
		select <include refid="edu_statistics_day_columns"/>
		from edu_statistics_day
		<where>
			<if test="startDate !='' and startDate !=null and endDate !=null and endDate !='' ">
				and edu_statistics_day.STATISTICS_TIME BETWEEN #{startDate} and #{endDate}
			</if>
		</where>
	</select>

	<!-- 网校总用户数 -->
	<select id="getEudUserCount" resultType="int">
		select count(1) from edu_user where edu_user.IS_AVALIBLE=1
	</select>

	<!-- 网校课程数 -->
	<select id="getEudCouresCount" resultType="int">
		select count(1) from edu_course where edu_course.IS_AVALIABLE=1
	</select>
</mapper>