<?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="adReportSqlMapper">
	
	<sql id="base_column">
		id, 
		advertiser_id as advertiserId,
		order_id as orderId,
		campaign_id as campaignId,
		adgroup_id  as adGroupId,
		 banner.id as bannerId,
		t_banner.banner_template_id as bannerTemplateId,
		t_advertiser.name as advertiserName,
		t_order.name as orderName,
		t_campaign.name as campaignName,
		t_adgroup.name  as adGroupName,
		t_banner_template.name as bannerName,
		date as reportDate,
		request,
		impression,
		impressionRate,
		click,
		visit,
		conversion,
		conversionRate,
		clickRate,
		visitRate,
		conversionRate,
		ecpm as eCmp,
		consume,
		os,
		location,
		impressionSum,
		clickSum,
		visitSum,
		conversionSum,
		eCmpSum,
		consumeSum,
		clickRatesum,
		visitRatesum,
		conversionRatesum
		
	</sql>
	<sql id="base_column2">
		id,
		zone_id as zoneId,
		pv,
		uv,
		width,
		height,
		position,
		os,
		device,
		impression,
		place,
		click,
		clickRate,
		eCpm,
		income,
		t_rpt_zone.date as reportDate
	</sql>
	
	<select id="findCountForDeliverEffectAnalysisAdvertiser" parameterType="map" resultType="int">
		select count(1) from (
		select b.name advertiserName,c.name orderName,d.name campaignName,e.name adgroupName,g.name bannerName,a.date reportDate,sum(a.impression) as impression,sum(a.click) as click,100*sum(a.click)/sum(a.impression) as clickRate,sum(a.visit) as visit,100*sum(a.visit)/sum(a.click) as visitRate,sum(a.conversion) as conversion,100*sum(a.conversion)/sum(a.visit) as conversionRate,sum(a.ecpm) as ecmp,sum(a.consume) as consume 
		from t_rpt_advertiser a,t_advertiser b,t_order c,t_campaign d,t_adgroup e,t_banner f,t_banner_template g
		where a.advertiser_id=b.id and a.order_id=c.id and a.campaign_id=d.id and a.adgroup_id=e.id and a.banner_id=f.id and f.banner_template_id=g.id
		<if test="advertiserId != null and advertiserId ">
			and a.advertiser_id = #{advertiserId}
		</if>
		<if test="orderId != null and orderId != ''">
			and a.order_id = #{orderId}
		</if>
		<if test="campaginId != null and campaginId != ''">
			and a.campagin_id = #{campaginId}
		</if>
		<if test="adgroupId != null and adgroupId != ''">
			and a.adgroup_id = #{adgroupId}
		</if>
		<if test="startDate != null and startDate != ''and endDate != nul and endDate != ''">
			and a.date &gt;=#{startDate} and a.date &lt;=#{endDate}
		</if>
		<choose>
			<when test="dimensionStr != null and dimensionStr != ''">
				GROUP BY ${dimensionStr}
			</when>
			<otherwise>
				group by reportDate order by reportDate desc
			</otherwise>
		</choose>
		) t	
	</select>
	
	<!-- 创意报表记录总数 -->
    <select id="findCountForBanner" parameterType="map" resultType="int">
		select count(1) 
		from t_rpt_adv_banner rp      

        <where>      
            <!-- rp.place_id = t_place.id and  rp.advertiser_id = t_advertiser.id  
            and  rp.order_id = t_order.id and rp.campaign_id = t_campaign.id  and rp.adgroup_id =  t_adgroup.id -->
			<if test="advertiserId !=null">
			 and rp.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and rp.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and rp.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rp.adgroup_id=#{adGroupId}
			</if>
		 
			<if test="startDate!=null and endDate!=null"> 
	         and rp.date &gt;= #{startDate} and rp.date &lt;= #{endDate}
			</if>
		
		</where>
	</select>
	
	<!-- 条件查询创意报表 -->

<select id="findBannerList" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	              bt.name as bannerName,
	              rp.date as reportDate,
                  rp.impression,rp.click,rp.visit,rp.conversion,
              100*rp.impression/(select sum(t_rpt_adv_banner.impression) from t_rpt_adv_banner      
             where  1=1    
			<if test="advertiserId !=null">
			 and t_rpt_adv_banner.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and t_rpt_adv_banner.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and t_rpt_adv_banner.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_adv_banner.adgroup_id=#{adGroupId}
			</if>
		 
	  		 <if test="startDate!=null and endDate!=null"> 
            and t_rpt_adv_banner.date &gt;= #{startDate} and #{endDate} &gt;=t_rpt_adv_banner.date
		        </if>
	) as impressionRate,100*rp.click/rp.impression as clickRate ,
	100*visit/rp.click as visitRate ,100*rp.conversion/visit as
	conversionRate,ecpm as eCmp,consume
	from t_rpt_adv_banner rp,
	t_banner_template bt
              <where>      
            rp.banner_id = bt.id
			<if test="advertiserId !=null">
			 and rp.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and rp.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and rp.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rp.adgroup_id=#{adGroupId}
			</if>
		 
	  		 <if test="startDate!=null and endDate!=null"> 
            and rp.date &gt;= #{startDate} and #{endDate} &gt;=rp.date
		        </if>
		     
		  </where> 
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<!-- 创意报表导出 -->
	<select id="exportBannerList" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	              bt.name as bannerName,
	              rp.date as reportDate,
                  rp.impression,rp.click,rp.visit,rp.conversion,
              100*rp.impression/(select sum(t_rpt_adv_banner.impression) from t_rpt_adv_banner      
             where  1=1    
			<if test="advertiserId !=null">
			 and t_rpt_adv_banner.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and t_rpt_adv_banner.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and t_rpt_adv_banner.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_adv_banner.adgroup_id=#{adGroupId}
			</if>
		 
	  		 <if test="startDate!=null and endDate!=null"> 
            and t_rpt_adv_banner.date &gt;= #{startDate} and #{endDate} &gt;=t_rpt_adv_banner.date
		        </if>
	) as impressionRate,100*rp.click/rp.impression as clickRate ,
	100*visit/rp.click as visitRate ,100*rp.conversion/visit as
	conversionRate,ecpm as eCmp,consume
	from t_rpt_adv_banner rp,
	t_banner_template bt
              <where>      
            rp.banner_id = bt.id
			<if test="advertiserId !=null">
			 and rp.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and rp.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and rp.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rp.adgroup_id=#{adGroupId}
			</if>
		 
	  		 <if test="startDate!=null and endDate!=null"> 
            and rp.date &gt;= #{startDate} and #{endDate} &gt;=rp.date
		        </if>
		     
		  </where> 
	</select>
	
	<select id="findCountForDeliverEffectAnalysisBack" parameterType="map" resultType="int">
		select count(1) 
		from t_rpt_advertiser ad,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template
           
        <where>
	 	    ad.advertiser_id=t_advertiser.id  and  ad.order_id = t_order.id  and ad.campaign_id= t_campaign.id   
	 	    and  ad.adgroup_id=t_adgroup.id
            and  ad.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id
	 	   
			<if test="advertiserId !=null">
			 and ad.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and ad.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and ad.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and ad.adgroup_id=#{adGroupId}
			</if>
		 
			<if test="startDate!=null and endDate!=null"> 
	            and ad.date &gt;= #{startDate} and #{endDate} &gt;=ad.date
			</if>
		
		</where>
	</select>
		<select id="findCountForDeliverEffectAnalysisOperator" parameterType="map" resultType="int">
		select count(date) from ((
		select distinct date 
		from t_rpt_zone  
		<where>
		operator_id = #{operatorId} and date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		</where>
		) as a)
	</select>
	
		<select id="findCountForLocationAdvertiser" parameterType="map" resultType="int">
		select count(location) from ( select location
		from t_rpt_adv_location a, t_advertiser b
       	<where>	
       	a.advertiser_id = b.id and a.date &gt;= #{startDate} and #{endDate} &gt;=a.date
			<if test="advertiserId ==null">
			 and b.agent_id=#{agentId}
			</if>
			<if test="advertiserId != null">
			and a.advertiser_id = #{advertiserId}	
			</if>
			<if test="orderId !=null">
			 and a.order_id=#{orderId}
			</if>
		     <if test="campaignId !=null">
			 and a.campaign_id=#{campaignId}
			</if>
			<if test="adGroupId !=null">
			 and a.adgroup_id=#{adGroupId}
			</if>
		</where>
		group by a.location
		) as c
	</select>
	
		<select id="findCountForLocationBack" parameterType="map" resultType="int">
		select count(1) 
		from t_rpt_adv_location rl, t_advertiser, t_order, t_campaign, t_adgroup
        
       	<where>	
            rl.advertiser_id = t_advertiser.id  and  rl.order_id =  t_order.id and rl.campaign_id = t_campaign.id  and rl.adgroup_id =  t_adgroup.id
	 	   	
			<if test="advertiserId !=null">
			 and rl.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and rl.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and rl.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rl.adgroup_id=#{adGroupId}
			</if>
		 
			<if test="startDate!=null and endDate!=null"> 
	            and rl.date &gt;= #{startDate} and #{endDate} &gt;=rl.date
			</if>
		
		</where>
	</select>
	
	<select id="findDeliverEffectAnalysis" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		zone_id as zoneId,date as reportDate,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 
		<where>
		operator_id = #{operatorId} and date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		</where>
		group by date
		limit #{pager.offset}, #{pager.limit}
	</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 
		<where>
		operator_id = #{operatorId}
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		<if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
		</if>
		</where>
	</select>
	
    <select id="findCountForOs" parameterType="map" resultType="int">
		select count(1) 
		from t_rpt_adv_os ros, t_advertiser, t_order, t_campaign, t_adgroup        
        <where>	          
           ros.advertiser_id = t_advertiser.id  and  ros.order_id =  t_order.id and ros.campaign_id = t_campaign.id  and ros.adgroup_id =  t_adgroup.id
	 	   
			<if test="advertiserId !=null">
			 and ros.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and ros.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and ros.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and ros.adgroup_id=#{adGroupId}
			</if>
		 
			<if test="startDate!=null and endDate!=null"> 
	            and ros.date &gt;= #{startDate} and #{endDate} &gt;=ros.date
			</if>
		
		</where>
	</select>
	
		<select id="findDeliverEffectAnalysis1" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		zone_id as zoneId,date as reportDate,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 
		<where>
		operator_id = #{operatorId} and date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		</where>
		group by date
	</select>
	
    <select id="findCountForScene" parameterType="map" resultType="int">
		select count(1) 
		from t_rpt_adv_place rp, t_advertiser, t_order, t_campaign, t_adgroup , t_place      

        <where>      
            rp.place_id = t_place.id and  rp.advertiser_id = t_advertiser.id  
            and  rp.order_id = t_order.id and rp.campaign_id = t_campaign.id  and rp.adgroup_id =  t_adgroup.id
			<if test="advertiserId !=null">
			 and rp.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and rp.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and rp.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rp.adgroup_id=#{adGroupId}
			</if>
		 
			<if test="startDate!=null and endDate!=null"> 
	         and rp.date &gt;= #{startDate} and rp.date &lt;= #{endDate}
			</if>
		
		</where>
	</select>
	
	<select id="countBySize" parameterType="map" resultType="int">
		 select count(height) from ((select distinct width,height from t_rpt_zone_size where 
		  date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		 ) as a )
	</select>
	
	 <select id="findByTotalReportAdvertiser" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select b.name advertiserName,c.name orderName,d.name campaignName,e.name adgroupName,g.name bannerName,a.date reportDate,sum(a.impression) as impression,sum(a.click) as click,100*sum(a.click)/sum(a.impression) as clickRate,sum(a.visit) as visit,100*sum(a.visit)/sum(a.click) as visitRate,sum(a.conversion) conversion,100*sum(a.conversion)/sum(a.visit) as conversionRate,sum(a.ecpm) ecmp,sum(a.consume) as consume 
		from t_rpt_advertiser a,t_advertiser b,t_order c,t_campaign d,t_adgroup e,t_banner f,t_banner_template g
		where a.advertiser_id=b.id and a.order_id=c.id and a.campaign_id=d.id and a.adgroup_id=e.id and a.banner_id=f.id and f.banner_template_id=g.id
		<if test="advertiserId != null and advertiserId ">
			and a.advertiser_id = #{advertiserId}
		</if>
		<if test="orderId != null and orderId != ''">
			and a.order_id = #{orderId}
		</if>
		<if test="campaginId != null and campaginId != ''">
			and a.campagin_id = #{campaginId}
		</if>
		<if test="adgroupId != null and adgroupId != ''">
			and a.adgroup_id = #{adgroupId}
		</if>
		<if test="startDate != null and startDate != ''and endDate != nul and endDate != ''">
			and a.date &gt;=#{startDate} and a.date &lt;=#{endDate}
		</if>
		<choose>
			<when test="dimensionStr != null and dimensionStr != ''">
				GROUP BY ${dimensionStr}
			</when>
			<otherwise>
				group by reportDate
			</otherwise>
		</choose>		
		limit #{pager.offset}, #{pager.limit}
	</select>
	
<select id="findBySize" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	    id,date as reportDate,width ,height,100*sum(pv)/(select sum(pv) from t_rpt_zone_size 
	    where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		) as viewRate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*impression/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_size 
		where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		group by width,height
		order by impression desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	 <select id="findByTotalReportBack" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		t_advertiser.name as advertiserName,  t_order.name as orderName, t_campaign.name as campaignName, t_adgroup.name  as adGroupName, t_banner_template.name as bannerName, 
        
         ad.date as reportDate,     
        (select sum(t_rpt_advertiser.impression) from  t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as impressionSum,ad.impression,ad.click,visit,ad.conversion, 
        100*ad.click/ad.impression as clickRate ,100*visit/ad.click as visitRate ,100*ad.conversion/visit as conversionRate ,ecpm as eCmp,ad.consume,
		(select sum(t_rpt_advertiser.click) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as clickSum, (select sum(t_rpt_advertiser.visit) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as visitSum,
        (select sum(t_rpt_advertiser.conversion) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as conversionSum, 
		(select sum(t_rpt_advertiser.ecpm) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as eCmpSum, (select sum(t_rpt_advertiser.consume) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as consumeSum, 
        100*(select sum(t_rpt_advertiser.click) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>)/(select sum(t_rpt_advertiser.impression) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as clickRatesum, 
		100*(select sum(t_rpt_advertiser.visit) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>)/(select sum(t_rpt_advertiser.click) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as visitRatesum,
		100*(select sum(t_rpt_advertiser.conversion) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>)/(select sum(t_rpt_advertiser.visit) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as conversionRatesum
		
		from t_rpt_advertiser ad,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template
           
           
           <where>
	 	    ad.advertiser_id=t_advertiser.id  and  ad.order_id = t_order.id  and ad.campaign_id= t_campaign.id   
	 	    and  ad.adgroup_id=t_adgroup.id
            and  ad.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id

			<if test="advertiserId !=null">
			 and ad.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and ad.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and ad.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and ad.adgroup_id=#{adGroupId}
			</if>
		 
	
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>
	  
		  </where> 
		
		  limit #{pager.offset}, #{pager.limit}
	</select>
	
	<!-- 系统报表 -->
	<select id="countBySystem" parameterType="map" resultType="int">
         select count(os) from ((select distinct os from t_rpt_zone_os where 
		  date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		  ) as a)
	</select>
	
		<select id="findByPosition" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	    id,date as reportDate,position,100*sum(pv)/(select sum(pv) from t_rpt_zone_position where 
		 date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		 ) as viewRate,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_position
		where 
		 date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		group by position 
		order by impression desc 
	</select>
	
	<select id="findBySize1" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	    id,date as reportDate,width ,height,100*sum(pv)/(select sum(pv) from t_rpt_zone_size 
	    where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		) as viewRate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*impression/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_size 
		where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		group by width,height
		order by impression desc
	</select>
	
	<select id="findByTotalReport2" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		t_advertiser.name as advertiserName,  t_order.name as orderName, t_campaign.name as campaignName, t_adgroup.name  as adGroupName, t_banner_template.name as bannerName, 
        
         ad.date as reportDate,   ad.hour as hour,  
        (select sum(impression) from t_rpt_advertiser_hour) as impressionSum,ad.impression,ad.click,visit,ad.conversion, 
        100*ad.click/ad.impression as clickRate ,100*visit/ad.click as visitRate ,100*ad.conversion/visit as conversionRate ,ecpm as eCmp,ad.consume,
		(select sum(click) from t_rpt_advertiser_hour) as clickSum, (select sum(visit) from t_rpt_advertiser_hour) as visitSum,
        (select sum(conversion) from t_rpt_advertiser_hour) as conversionSum, 
		(select sum(ecpm) from t_rpt_advertiser_hour) as eCmpSum, (select sum(consume) from t_rpt_advertiser_hour) as consumeSum, 
        100*(select sum(click) from t_rpt_advertiser_hour)/(select sum(impression) from t_rpt_advertiser_hour) as clickRatesum, 
		100*(select sum(visit) from t_rpt_advertiser_hour)/(select sum(click) from t_rpt_advertiser_hour) as visitRatesum,
		100*(select sum(conversion) from t_rpt_advertiser_hour)/(select sum(visit) from t_rpt_advertiser_hour) as conversionRatesum
		
		from t_rpt_advertiser_hour ad,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template
           
           
           <where>
	 	    ad.advertiser_id=t_advertiser.id  and  ad.order_id = t_order.id  and ad.campaign_id= t_campaign.id   
	 	    and  ad.adgroup_id=t_adgroup.id
            and  ad.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id

			<if test="advertiserId !=null">
			 and ad.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and ad.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and ad.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and ad.adgroup_id=#{adGroupId}
			</if>
		 
		 <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
		</if>
		 
		  </where> 
		
		  limit #{pager.offset}, #{pager.limit}
	</select>
	
		<select id="findBySystem" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
	   select 
	    os as system,100*(sum(pv)/(select sum(pv) from t_rpt_zone_os
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		   )) as viewRate,
		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_os 
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		group by os
		order by impression desc
	</select>
	
	<select id="findByLocationAdvertiser" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select t_location.cn_city as location, 100 * sum(impression) /(select sum(impression) from t_rpt_adv_location a1,t_advertiser b1	
		<where>	
       	a1.advertiser_id = b1.id and a1.date &gt;= #{startDate} and #{endDate} &gt;=a1.date
			<if test="advertiserId ==null">
			 and b1.agent_id=#{agentId}
			</if>
			<if test="advertiserId != null">
			and a1.advertiser_id = #{advertiserId}	
			</if>
			<if test="orderId !=null">
			 and a1.order_id=#{orderId}
			</if>
		     <if test="campaignId !=null">
			 and a1.campaign_id=#{campaignId}
			</if>
			<if test="adGroupId !=null">
			 and a1.adgroup_id=#{adGroupId}
			</if>
		</where>
		) as impressionRate, sum(impression) as impression, sum(click) as click, 100 * sum(click) / sum(impression) as clickRate,
		sum(visit) as visit, 100 * sum(visit) / sum(click) as visitRate, 100 * sum(conversion) / sum(visit) as conversionRate,
		sum(conversion) as conversion, sum(ecpm) as eCmp, sum(consume) as consume from t_rpt_adv_location a, t_advertiser b,t_location
		 	<where>	
       	a.advertiser_id = b.id and a.location = t_location.location and a.date &gt;= #{startDate} and #{endDate} &gt;=a.date
			<if test="advertiserId ==null">
			 and b.agent_id=#{agentId}
			</if>
			<if test="advertiserId != null">
			and a.advertiser_id = #{advertiserId}	
			</if>
			<if test="orderId !=null">
			 and a.order_id=#{orderId}
			</if>
		     <if test="campaignId !=null">
			 and a.campaign_id=#{campaignId}
			</if>
			<if test="adGroupId !=null">
			 and a.adgroup_id=#{adGroupId}
			</if>
		</where>
		group by a.location
		limit #{pager.offset}, #{pager.limit}
	   </select>
	   
		<select id="findBySystem1" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
	    select 
	    os as system,100*(sum(pv)/(select sum(pv) from t_rpt_zone_os
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		   )) as viewRate,
		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_os 
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		group by os
		order by impression desc
	</select>
	
	<select id="findByLocationBack" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		t_advertiser.name as advertiserName,  t_order.name as orderName, t_campaign.name as campaignName, t_adgroup.name  as adGroupName,

               rl.date as reportDate,
        rl.impression,rl.click,visit,rl.conversion,location,
        100*rl.impression/(select sum(t_rpt_adv_location.impression) from t_rpt_adv_location , t_advertiser, t_order, t_campaign, t_adgroup
        
           where	
            t_rpt_adv_location.advertiser_id = t_advertiser.id  and  t_rpt_adv_location.order_id =  t_order.id and t_rpt_adv_location.campaign_id = t_campaign.id  and t_rpt_adv_location.adgroup_id =  t_adgroup.id
         	   
			<if test="advertiserId !=null">
			 and t_rpt_adv_location.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and t_rpt_adv_location.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and t_rpt_adv_location.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_adv_location.adgroup_id=#{adGroupId}
			</if>
		
	  	 <if test="startDate!=null and endDate!=null"> 
            and t_rpt_adv_location.date &gt;= #{startDate} and #{endDate} &gt;=t_rpt_adv_location.date
		 </if>) as impressionRate,100*rl.click/rl.impression as clickRate ,
        100*visit/rl.click as visitRate ,100*rl.conversion/visit as conversionRate,ecpm as eCmp,consume   
        from t_rpt_adv_location rl, t_advertiser, t_order, t_campaign, t_adgroup
        
           <where>	
            rl.advertiser_id = t_advertiser.id  and  rl.order_id =  t_order.id and rl.campaign_id = t_campaign.id  and rl.adgroup_id =  t_adgroup.id
         	   
			<if test="advertiserId !=null">
			 and rl.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and rl.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and rl.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rl.adgroup_id=#{adGroupId}
			</if>
		
	  	 <if test="startDate!=null and endDate!=null"> 
            and rl.date &gt;= #{startDate} and #{endDate} &gt;=rl.date
		      </if>
	    
		    </where> 
		    
		limit #{pager.offset}, #{pager.limit}
	   </select>
	
	<select id="findByOs" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		t_advertiser.name as advertiserName,  t_order.name as orderName, t_campaign.name as campaignName, t_adgroup.name  as adGroupName,

              ros.date as reportDate,
        ros.impression,ros.click,visit,ros.conversion,ros.os,
        100*ros.impression/(select sum(t_rpt_adv_os.impression) from t_rpt_adv_os, t_advertiser, t_order, t_campaign, t_adgroup        


            where	          
         
           t_rpt_adv_os.advertiser_id = t_advertiser.id  and  t_rpt_adv_os.order_id =  t_order.id and t_rpt_adv_os.campaign_id = t_campaign.id  and t_rpt_adv_os.adgroup_id =  t_adgroup.id
		     	   
			<if test="advertiserId !=null">
			 and t_rpt_adv_os.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and t_rpt_adv_os.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and t_rpt_adv_os.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_adv_os.adgroup_id=#{adGroupId}
			</if>
			
	 		<if test="startDate!=null and endDate!=null"> 
            and t_rpt_adv_os.date &gt;= #{startDate} and #{endDate} &gt;=t_rpt_adv_os.date
		     </if>
	) as impressionRate,100*ros.click/ros.impression as clickRate ,
        100*visit/ros.click as visitRate ,100*ros.conversion/visit as conversionRate,ecpm as eCmp,consume   
        from t_rpt_adv_os ros, t_advertiser, t_order, t_campaign, t_adgroup        


            <where>	          
         
           ros.advertiser_id = t_advertiser.id  and  ros.order_id =  t_order.id and ros.campaign_id = t_campaign.id  and ros.adgroup_id =  t_adgroup.id
		     	   
			<if test="advertiserId !=null">
			 and ros.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and ros.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and ros.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and ros.adgroup_id=#{adGroupId}
			</if>
			
	 		<if test="startDate!=null and endDate!=null"> 
            and ros.date &gt;= #{startDate} and #{endDate} &gt;=ros.date
		     </if>
	
		   </where> 

		limit #{pager.offset}, #{pager.limit}
	</select>
	
		<select id="countByAllAreal" parameterType="map" resultType="int">
		select count(cn) from ((select distinct cn from t_rpt_zone_location a,t_location b 
  		where
		a.location = b.location and date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		) as t)
		
	</select>
	
		<select id="findByAllAreal" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		 select 
	    a.id,a.date as reportDate,b.cn,b.cn_city,100*sum(a.pv)/(select sum(pv) from t_rpt_zone_location where date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		) as viewRate,
	    sum(a.pv) as pv,sum(a.uv) as uv,sum(a.impression) as impression,
	    sum(uimpression) as uImpression, sum(uclick) as uClick,100*sum(a.impression)/sum(a.pv) as fillRate,sum(a.click) as click,100*sum(a.click)/sum(a.impression) as clickRate,sum(a.eCpm) as eCpm,sum(a.income) as income
		from t_rpt_zone_location a,t_location b
	 	where
		a.location = b.location  and a.date &gt;= #{startDate} and #{endDate} &gt;=a.date
		    and operator_id=#{operatorId}
		group by b.cn 
		order by b.cn 
		limit #{pager.offset}, #{pager.limit}
	</select>
	
<select id="findByDevice" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		
		select 
	    id,date as reportDate,device,100*sum(pv)/
	    (select sum(pv) from t_rpt_zone_device where operator_id = #{operatorId} and date &gt;= #{startDate} and #{endDate} &gt;=date) as viewRate,
	    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_device
		where operator_id = #{operatorId} and date &gt;= #{startDate} and #{endDate} &gt;=date
		group by device 
		order by impression desc
	</select>
	
	
	 <select id="findByPlaceName" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	             t_place.name as placeName,	t_advertiser.name as advertiserName,  t_order.name as orderName, 
	             t_campaign.name as campaignName, t_adgroup.name as adGroupName,
	             
	              rp.date as reportDate,
                  rp.impression,rp.click,visit,rp.conversion,
              100*rp.impression/(select sum(t_rpt_adv_place.impression) from t_rpt_adv_place , t_advertiser, t_order, t_campaign, t_adgroup , t_place      


             where      
            t_rpt_adv_place.place_id = t_place.id and  t_rpt_adv_place.advertiser_id = t_advertiser.id  
            and  t_rpt_adv_place.order_id = t_order.id and t_rpt_adv_place.campaign_id = t_campaign.id  and t_rpt_adv_place.adgroup_id =  t_adgroup.id
	   
			<if test="advertiserId !=null">
			 and t_rpt_adv_place.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and t_rpt_adv_place.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and t_rpt_adv_place.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_adv_place.adgroup_id=#{adGroupId}
			</if>
		 
	  		 <if test="startDate!=null and endDate!=null"> 
            and t_rpt_adv_place.date &gt;= #{startDate} and #{endDate} &gt;=t_rpt_adv_place.date
		        </if>) as impressionRate,100*rp.click/rp.impression as clickRate ,
              100*visit/rp.click as visitRate ,100*rp.conversion/visit as conversionRate,ecpm as eCmp,consume   
            from t_rpt_adv_place rp, t_advertiser, t_order, t_campaign, t_adgroup , t_place      


              <where>      
            rp.place_id = t_place.id and  rp.advertiser_id = t_advertiser.id  
            and  rp.order_id = t_order.id and rp.campaign_id = t_campaign.id  and rp.adgroup_id =  t_adgroup.id
	   
			<if test="advertiserId !=null">
			 and rp.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and rp.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and rp.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rp.adgroup_id=#{adGroupId}
			</if>
		 
	  		 <if test="startDate!=null and endDate!=null"> 
            and rp.date &gt;= #{startDate} and #{endDate} &gt;=rp.date
		        </if>
		     
		  </where> 
		limit #{pager.offset}, #{pager.limit}
	</select>
	

		<select id="findByArealCity" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		 select 
	    b.cn,b.cn_city,100*sum(a.pv)/(select sum(pv) from t_rpt_zone_location where date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		) as viewRate,
	    sum(a.pv) as pv,sum(a.uv) as uv,sum(a.impression),
	    sum(uimpression) as uImpression, sum(uclick) as uClick,
	    100*sum(a.impression)/sum(a.pv) as fillRate,sum(a.click),100*sum(a.click)/sum(a.impression) as clickRate,sum(a.eCpm) as eCpm,sum(a.income) as income
		from t_rpt_zone_location a,t_location b
		where
		a.location=b.location and b.cn=#{cn} and date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
       group by b.cn_city
	</select>
	
	<!--<select id="findByOrderName" parameterType="map" resultType="DeliverAnalysis">
		select 
		t_order.name as orderName,impression,click,visit,conversion,click/impression as clickRate ,visit/click as visitRate ,conversion/visit as conversionRate,ecpm,income
		from t_rpt_daily
		left join t_order
		ON t_rpt_daily.order_id=t_order.id
		order by order_id desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByCampaignName" parameterType="map" resultType="DeliverAnalysis">
		select 
		t_campaign.name as campaignName,impression,click,visit,conversion,click/impression as clickRate ,visit/click as visitRate ,conversion/visit as conversionRate ,ecpm,income
		from t_rpt_daily
		left join t_campaign
		ON t_rpt_daily.campaign_id=t_campaign.id
		order by campaign_id desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByAdGroupName" parameterType="map" resultType="DeliverAnalysis">
		select 
		t_adgroup.name as adGroupName,impression,click,visit,conversion,click/impression as clickRate ,visit/click as visitRate ,conversion/visit as conversionRate ,ecpm,income
		from t_rpt_daily
		left join t_adgroup
		ON t_rpt_daily.adgroup_id=t_adgroup.id
		order by adgroup_id desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByBannerName" parameterType="map" resultType="DeliverAnalysis">
		select 
		t_banner.name as bannerName,impression,click,visit,conversion,click/impression as clickRate ,visit/click as visitRate ,conversion/visit as conversionRate ,eCmp,income
		from t_rpt_daily
		left join t_banner
		ON t_rpt_daily.banner_id=t_banner.id
		order by banner_id desc
		limit #{pager.offset}, #{pager.limit}
	</select>  -->
	
	
	
	
	<select id="findByAdvertiser" parameterType="map" resultType="map">
		select ad.name as advertiserName,date as reportDate,sum(a.consume) as totalConsume,sum(a.impression) as totalImpression,
sum(click) as totalClick, if(sum(a.impression) > 0,sum(a.click)/sum(a.impression),0)
		as clickRate from t_rpt_advertiser as a left join t_advertiser ad on a.advertiser_id=ad.id
		where a.advertiser_id=#{advertiserId}
		and a.date >= #{startDate} and a.date &lt;=#{endDate}
		group by date(a.date)
	</select>
	
	<select id="findByAdvertiser3" parameterType="map" resultType="map">
		select ad.name as advertiserName,date as reportDate,sum(a.consume) as totalConsume,sum(a.impression) as totalImpression,
sum(click) as totalClick, if(sum(a.impression) > 0,sum(a.click)/sum(a.impression),0)
		as clickRate from t_rpt_advertiser as a left join t_advertiser ad on a.advertiser_id=ad.id
		where a.advertiser_id=#{advertiserId}
		and a.date >= #{startDate} and a.date &lt;=#{endDate}
		group by date(a.date)
		limit #{pager.offset}, #{pager.limit} 
	</select>
	
	<select id="findByAreal" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	    b.cn,b.cn_city,100*sum(a.pv)/(select sum(pv) from t_rpt_zone_location where date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
		) as viewRate,
	    sum(a.pv) as pv,sum(a.uv) as uv,sum(a.impression),
	    sum(uimpression) as uImpression, sum(uclick) as uClick,
	    100*sum(a.impression)/sum(a.pv) as fillRate,sum(a.click),100*sum(a.click)/sum(a.impression) as clickRate,sum(a.eCpm) as eCpm,sum(a.income) as income
		from t_rpt_zone_location a,t_location b
		where
		a.location=b.location and b.cn=#{cn} and date &gt;= #{startDate} and #{endDate} &gt;=date
		    and operator_id=#{operatorId}
       group by b.cn_city
	</select>
	
	<select id="findByAdvertiser1" parameterType="map" resultType="map">
		select ad.name as advertiserName,date as reportDate, a.hour as hour,sum(a.consume) as totalConsume,sum(a.impression) as totalImpression,
sum(click) as totalClick, if(sum(a.impression) > 0,sum(a.click)/sum(a.impression),0)
		as clickRate from t_rpt_advertiser_hour as a left join t_advertiser ad on a.advertiser_id=ad.id
		where a.advertiser_id=#{advertiserId} and a.date = #{startDate}
		group by a.hour
	</select>
	
	<select id="countByPlace" parameterType="map" resultType="int">
		select count(place_id) from ((select distinct place_id from t_rpt_zone_place where 
		operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as a) 
	</select>
	
	
	<select id="findByAdvertiser2" parameterType="map" resultType="map">
		select ad.name as advertiserName,date as reportDate, a.hour as hour,sum(a.consume) as totalConsume,sum(a.impression) as totalImpression,
sum(click) as totalClick, if(sum(a.impression) > 0,sum(a.click)/sum(a.impression),0)
		as clickRate from t_rpt_advertiser_hour as a left join t_advertiser ad on a.advertiser_id=ad.id
		where a.advertiser_id=#{advertiserId} and a.date = #{startDate}
		group by a.hour
		limit #{pager.offset}, #{pager.limit} 
	</select>
	
	
		<select id="findByPlace" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select t_place.name as place,sum(p.pv) as pv, sum(p.uv) as uv, sum(p.impression) as impression, 
		sum(uimpression) as uImpression,
		sum(uclick) as uClick,sum(p.click) as click, sum(p.ecpm) as eCpm,
    sum(p.income) as income, 100 * sum(p.pv) / (select sum(pv) from t_rpt_zone_place where operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as viewRate, 
    100 * sum(p.impression) / sum(p.pv) as fillRate, 100 * sum(p.click) / sum(p.impression) as clickRate
    from ((select distinct place_id from t_rpt_zone_place where operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as a ), 
    t_rpt_zone_place as p, t_place where a.place_id = p.place_id and a.place_id = t_place.id
    and operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}
    group by p.place_id
    order by p.impression desc
	limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findCountForChart" parameterType="map" resultType="int">
	select count(date) from((select distinct date from t_rpt_advertiser where advertiser_id=#{advertiserId}
		and date >= #{startDate} and date &lt;=#{endDate}) as a)
	</select>
	
		<select id="findByPlace1" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select t_place.name as place,sum(p.pv) as pv, sum(p.uv) as uv, sum(p.impression) as impression, 
		sum(uimpression) as uImpression,
		sum(uclick) as uClick,sum(p.click) as click, sum(p.ecpm) as eCpm,
    sum(p.income) as income, 100 * sum(p.pv) / (select sum(pv) from t_rpt_zone_place where operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as viewRate, 
    100 * sum(p.impression) / sum(p.pv) as fillRate, 100 * sum(p.click) / sum(p.impression) as clickRate
    from ((select distinct place_id from t_rpt_zone_place where operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as a ), 
    t_rpt_zone_place as p, t_place where a.place_id = p.place_id and a.place_id = t_place.id
    and operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}
    group by p.place_id
     order by p.impression desc
	</select>
	
	<select id="findCountForChart1" parameterType="map" resultType="int">
		select count(hour) from ((select distinct hour from t_rpt_advertiser_hour where advertiser_id=#{advertiserId} and date = #{startDate}) as a) 
	</select>
	
	<select id="findTotalImpressionAndClickByAdvertiserId" parameterType="map" resultType="map">
		select sum(impression) as totalImpression, sum(click) as totalClick
		from t_rpt_advertiser 
		where advertiser_id = #{advertiserId} and date = #{date}
	</select>
	
	<select id="countByApmac" parameterType="map" resultType="int">
			select count(apmac) from ((select distinct apmac from t_rpt_zone_ap where 
		operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as a) 
	</select>
	
	<select id="findTotalImpressionAndClickByAgentId" parameterType="map" resultType="map">
		select sum(a.impression) as totalImpression, sum(a.click) as totalClick
		from t_rpt_advertiser  a,t_advertiser b
		where b.id = a.advertiser_id and b.agent_id = #{agentId}
		and a.date = #{date}
	
	</select>
	
	<!--数据报表 导出excel -->
	<select id="findByTotalForExcelAdvertiser" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select b.name advertiserName,c.name orderName,d.name campaignName,e.name adgroupName,g.name bannerName,a.date reportDate,sum(a.impression) as impression,sum(a.click) as click,100*sum(a.click)/sum(a.impression) as clickRate,sum(a.visit) as visit,100*sum(a.visit)/sum(a.click) as visitRate,sum(a.conversion) conversion,100*sum(a.conversion)/sum(a.visit) as conversionRate,sum(a.ecpm) ecmp,sum(a.consume) as consume 
		from t_rpt_advertiser a,t_advertiser b,t_order c,t_campaign d,t_adgroup e,t_banner f,t_banner_template g
		where a.advertiser_id=b.id and a.order_id=c.id and a.campaign_id=d.id and a.adgroup_id=e.id and a.banner_id=f.id and f.banner_template_id=g.id
		<if test="advertiserId != null and advertiserId ">
			and a.advertiser_id = #{advertiserId}
		</if>
		<if test="orderId != null and orderId != ''">
			and a.order_id = #{orderId}
		</if>
		<if test="campaginId != null and campaginId != ''">
			and a.campagin_id = #{campaginId}
		</if>
		<if test="adgroupId != null and adgroupId != ''">
			and a.adgroup_id = #{adgroupId}
		</if>
		<if test="startDate != null and startDate != ''and endDate != nul and endDate != ''">
			and a.date &gt;=#{startDate} and a.date &lt;=#{endDate}
		</if>
		<choose>
			<when test="dimensionStr != null and dimensionStr != ''">
				GROUP BY ${dimensionStr}
			</when>
			<otherwise>
				group by reportDate 
			</otherwise>
		</choose>		
	</select>
	
	<select id="findByApmac" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select apmac, 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(ecpm) as eCpm,
    sum(income) as income, 100 * sum(pv) / (select sum(pv) from t_rpt_zone_ap where operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as viewRate, 
    100 * sum(impression) / sum(pv) as fillRate, 100 * sum(click) / sum(impression) as clickRate
    from  t_rpt_zone_ap  where 
    operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}
    group by apmac
    order by impression desc
	limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByLocationForExcel" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select t_location.cn_city as location, 100 * sum(impression) /(select sum(impression) from t_rpt_adv_location a1,t_advertiser b1	
		<where>	
       	a1.advertiser_id = b1.id and a1.date &gt;= #{startDate} and #{endDate} &gt;=a1.date
			<if test="advertiserId ==null">
			 and b1.agent_id=#{agentId}
			</if>
			<if test="advertiserId != null">
			and a1.advertiser_id = #{advertiserId}	
			</if>
			<if test="orderId !=null">
			 and a1.order_id=#{orderId}
			</if>
		     <if test="campaignId !=null">
			 and a1.campaign_id=#{campaignId}
			</if>
			<if test="adGroupId !=null">
			 and a1.adgroup_id=#{adGroupId}
			</if>
		</where>
		) as impressionRate, sum(impression) as impression, sum(click) as click, 100 * sum(click) / sum(impression) as clickRate,
		sum(visit) as visit, 100 * sum(visit) / sum(click) as visitRate, 100 * sum(conversion) / sum(visit) as conversionRate,
		sum(conversion) as conversion, sum(ecpm) as eCmp, sum(consume) as consume from t_rpt_adv_location a, t_advertiser b,t_location
		 	<where>	
       	a.advertiser_id = b.id and a.location = t_location.location and a.date &gt;= #{startDate} and #{endDate} &gt;=a.date
			<if test="advertiserId ==null">
			 and b.agent_id=#{agentId}
			</if>
			<if test="advertiserId != null">
			and a.advertiser_id = #{advertiserId}	
			</if>
			<if test="orderId !=null">
			 and a.order_id=#{orderId}
			</if>
		     <if test="campaignId !=null">
			 and a.campaign_id=#{campaignId}
			</if>
			<if test="adGroupId !=null">
			 and a.adgroup_id=#{adGroupId}
			</if>
		</where>
		group by a.location
	   </select>
	
		<select id="findByApmac1" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
			select apmac, 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(ecpm) as eCpm,
    sum(income) as income, 100 * sum(pv) / (select sum(pv) from t_rpt_zone_ap where operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}) as viewRate, 
    100 * sum(impression) / sum(pv) as fillRate, 100 * sum(click) / sum(impression) as clickRate
    from  t_rpt_zone_ap  where 
    operator_id = #{operatorId} and date &gt;= #{startDate} and date &lt;= #{endDate}
    group by apmac
    order by impression desc
	</select>
	
	<!-- 导出excel -->
	<select id="findByTotalForExcelBack" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		t_advertiser.name as advertiserName,  t_order.name as orderName, t_campaign.name as campaignName, t_adgroup.name  as adGroupName, t_banner_template.name as bannerName, 
        
         ad.date as reportDate,     
        (select sum(t_rpt_advertiser.impression) from  t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as impressionSum,ad.impression,ad.click,visit,ad.conversion, 
        100*ad.click/ad.impression as clickRate ,100*visit/ad.click as visitRate ,100*ad.conversion/visit as conversionRate ,ecpm as eCmp,ad.consume,
		(select sum(t_rpt_advertiser.click) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as clickSum, (select sum(t_rpt_advertiser.visit) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as visitSum,
        (select sum(t_rpt_advertiser.conversion) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as conversionSum, 
		(select sum(t_rpt_advertiser.ecpm) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as eCmpSum, (select sum(t_rpt_advertiser.consume) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as consumeSum, 
        100*(select sum(t_rpt_advertiser.click) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>)/(select sum(t_rpt_advertiser.impression) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as clickRatesum, 
		100*(select sum(t_rpt_advertiser.visit) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>)/(select sum(t_rpt_advertiser.click) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as visitRatesum,
		100*(select sum(t_rpt_advertiser.conversion) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>)/(select sum(t_rpt_advertiser.visit) from t_rpt_advertiser ,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template where  t_rpt_advertiser.advertiser_id=t_advertiser.id  and  t_rpt_advertiser.order_id = t_order.id  and t_rpt_advertiser.campaign_id= t_campaign.id   
	 	    and  t_rpt_advertiser.adgroup_id=t_adgroup.id
            and  t_rpt_advertiser.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id 

			<if test="advertiserId !=null">
			 and t_rpt_advertiser.advertiser_id=#{advertiserId}
			</if>
			<if test="orderId !=null">
			 and t_rpt_advertiser.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and t_rpt_advertiser.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and t_rpt_advertiser.adgroup_id=#{adGroupId}
			</if>
		 
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>) as conversionRatesum
		
		from t_rpt_advertiser ad,t_advertiser,t_order, t_campaign, t_banner, t_adgroup, t_banner_template
           
           
           <where>
	 	    ad.advertiser_id=t_advertiser.id  and  ad.order_id = t_order.id  and ad.campaign_id= t_campaign.id   
	 	    and  ad.adgroup_id=t_adgroup.id
            and  ad.banner_id=t_banner.id 
            and  t_banner.banner_template_id = t_banner_template.id

			<if test="advertiserId !=null">
			 and ad.advertiser_id=#{advertiserId}
			</if>
			
			<if test="orderId !=null">
			 and ad.order_id=#{orderId}
			</if>
			  
		     <if test="campaignId !=null">
			 and ad.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and ad.adgroup_id=#{adGroupId}
			</if>
		 
	
	    <if test="startDate!=null and endDate!=null"> 
            and date &gt;= #{startDate} and #{endDate} &gt;=date
	    </if>
	  
		  </where> 
		
	</select>
	
	<select id="findAllArea" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	    a.id,a.date as reportDate,b.cn,b.cn_city,100*sum(a.pv)/(select sum(pv) from t_rpt_zone_location where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>) as viewRate,
	    sum(a.pv) as pv,sum(a.uv) as uv,sum(a.impression) as impression,
	    sum(uimpression) as uImpression, sum(uclick) as uClick,
	    100*sum(a.impression)/sum(a.pv) as fillRate,sum(a.click) as click,100*sum(a.click)/sum(a.impression) as clickRate,sum(a.eCpm) as eCpm,sum(a.income) as income
		from t_rpt_zone_location a,t_location b
	 	where
		a.location = b.location  and a.date &gt;= #{startDate} and #{endDate} &gt;=a.date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>
		group by b.cn 
		order by b.cn
	</select>
	
	<select id="findByOsForExcel" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
		t_advertiser.name as advertiserName,  t_order.name as orderName, t_campaign.name as campaignName, t_adgroup.name  as adGroupName,

              ros.date as reportDate,
        ros.impression,ros.click,visit,ros.conversion,os,
        100*ros.impression/(select sum(impression) from t_rpt_adv_os) as impressionRate,100*ros.click/ros.impression as clickRate ,
        100*visit/ros.click as visitRate ,100*ros.conversion/visit as conversionRate,ecpm as eCmp,consume   
        from t_rpt_adv_os ros, t_advertiser, t_order, t_campaign, t_adgroup        


            <where>	          
         
           ros.advertiser_id = t_advertiser.id  and  ros.order_id =  t_order.id and ros.campaign_id = t_campaign.id  and ros.adgroup_id =  t_adgroup.id
		     	   
			<if test="advertiserId !=null">
			 and ros.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and ros.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and ros.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and ros.adgroup_id=#{adGroupId}
			</if>
			
	 		<if test="startDate!=null and endDate!=null"> 
            and ros.date &gt;= #{startDate} and #{endDate} &gt;=ros.date
		     </if>
	
		   </where> 

	</select>
	
	 <select id="findByPlaceNameForExcel" parameterType="map" resultType="com.cloudcross.ssp.model.DeliverAnalysis">
		select 
	             t_place.name as placeName,	t_advertiser.name as advertiserName,  t_order.name as orderName, 
	             t_campaign.name as campaignName, t_adgroup.name as adGroupName,
	             
	              rp.date as reportDate,
                  rp.impression,rp.click,visit,rp.conversion,
              100*rp.impression/(select sum(impression) from t_rpt_adv_place) as impressionRate,100*rp.click/rp.impression as clickRate ,
              100*visit/rp.click as visitRate ,100*rp.conversion/visit as conversionRate,ecpm as eCmp,consume   
            from t_rpt_adv_place rp, t_advertiser, t_order, t_campaign, t_adgroup , t_place      


              <where>      
            rp.place_id = t_place.id and  rp.advertiser_id = t_advertiser.id  
            and  rp.order_id = t_order.id and rp.campaign_id = t_campaign.id  and rp.adgroup_id =  t_adgroup.id
	   
			<if test="advertiserId !=null">
			 and rp.advertiser_id=#{advertiserId}
			</if> 
			
			<if test="orderId !=null">
			 and rp.order_id=#{orderId}
			</if>
		     
		     <if test="campaignId !=null">
			 and rp.campaign_id=#{campaignId}
			</if>
			
			<if test="adGroupId !=null">
			 and rp.adgroup_id=#{adGroupId}
			</if>
		 
	  		 <if test="startDate!=null and endDate!=null"> 
            and rp.date &gt;= #{startDate} and #{endDate} &gt;=rp.date
		        </if>
		     
		  </where> 
	</select>
	
</mapper>