<?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="rptZoneHourSqlMapper">
	
	<select id="findEveryHourDataBack" parameterType="map" resultType="com.cloudcross.ssp.model.RptZoneHour">
		select hour,sum(pv) as pv,sum(uv) as uv, sum(impression) as impression, 
		sum(uimpression) as uImpression,sum(uclick) as uClick,
		sum(click) as click, sum(income) as income
		from t_rpt_zone_hour where date = #{startDate}
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>      
        <if test="operatorId!=null">
        	and operator_id = #{operatorId}
        </if>
		group by hour
	</select>
		
		<select id="findEveryHourDataOperator" parameterType="map" resultType="com.cloudcross.ssp.model.RptZoneHour">
		select hour,sum(pv) as pv,sum(uv) as uv, sum(impression) as impression, sum(click) as click,sum(uimpression) as uImpression,
		sum(uclick) as uClick, sum(income) as income
		from t_rpt_zone_hour where operator_id = #{operatorId}
		and date =#{startDate} 
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>  
		group by hour
		</select>
		
		<select id="countByParams" parameterType="map" resultType="int">
		select count(distinct hour) from t_rpt_zone_hour
		where operator_id = #{operatorId}
		and date = #{startDate} 
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>  
		</select>
		
		<select id="findByParams1" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		zone_id as zoneId,date as reportDate,hour, sum(pv) as pv, sum(uv) as uv, sum(impression) as impression,sum(uimpression) as uImpression,
		sum(uclick) as uClick,
		100*sum(impression)/sum(pv) as fillRate,
        sum(click) as click,100* sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
        from t_rpt_zone_hour
		where operator_id = #{operatorId}
		and date =#{startDate} 
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>  
        group by hour
        limit #{pager.offset}, #{pager.limit}
		</select>
		
		<select id="findByParams2" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		zone_id as zoneId,date as reportDate,hour, sum(pv) as pv, sum(uv) as uv, sum(impression) as impression,sum(uimpression) as uImpression,
		sum(uclick) as uClick,
		100*sum(impression)/sum(pv) as fillRate,
        sum(click) as click,100* sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
        from t_rpt_zone_hour
		where operator_id = #{operatorId}
		and date =#{startDate} 
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>  
        group by hour
		</select>
		
		<select id="findSum" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select sum(pv) as pv, sum(uv) as uv, sum(impression) as impression, sum(click) as click, sum(income) as income
		from t_rpt_zone_hour
		where operator_id = #{operatorId}
		and date =#{startDate} 
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>  
	</select>
	
	
	<select id="countByParamsBack" parameterType="map" resultType="int">
		select count(distinct hour) from t_rpt_zone_hour
		where
		date = #{startDate} 
		<if test="operatorId != null">
		 and operator_id = #{operatorId}
		</if>
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>  
	</select>
		
		<select id="findByParamsBack" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
		zone_id as zoneId,date as reportDate,hour,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*sum(impression)/sum(pv) as fillRate,
        sum(click) as click,100* sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone_hour
		<where>
		date = #{startDate}
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		<if test="operatorId != null">
		and operator_id=#{operatorId}
		</if>
		</where>
		group by hour
		limit #{pager.offset}, #{pager.limit}
		</select>
		
		
		<select id="findByParamsBack1" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
		zone_id as zoneId,date as reportDate,hour,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*sum(impression)/sum(pv) as fillRate,
        sum(click) as click,100* sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone_hour
		<where>
		date = #{startDate}
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		<if test="operatorId != null">
		and operator_id=#{operatorId}
		</if>
		</where>
		group by hour
		</select>
		
		<select id="findSumBack" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select sum(pv) as pv, sum(uv) as uv, sum(impression) as impression, sum(click) as click, sum(income) as income
		from t_rpt_zone_hour
		where 
		date =#{startDate} 
		<if test="zoneId!=null">
        	and zone_id = #{zoneId}
        </if>  
		<if test="operatorId != null">
		and operator_id=#{operatorId}
		</if>
		</select>
</mapper>