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

	<sql id="base_column_banner">
		 a.id as id,
		 a.banner_template_id as bannerTemplateId,
		 a.order_id as orderId,
		 a.campaign_id as campaignId,
		 a.adgroup_id as adGroupId,
		 a.visit_address as visitAddress,
		a.click_address as clickAddress,
		a.impression_address as impressionAddress,
		a.status as status1,
		a.checked as checked,
		a.updated as updated,
		a.supdated as supdated,
		a.memo as memo
	</sql>
	<sql id="base_column_bannertemplate">
		b.id as bannerTemplateId,
		b.agent_id as agentId,
		b.advertiser_id as advertiserId,
		b.agent_id as agentId,
		b.name as name,
		b.path as path,
		b.path_small as pathSmall,
		b.type as type,
		b.width as width,
		b.height as height,
		b.status as status,
		b.updated as updated	
	</sql>
	
	
	<resultMap type="com.cloudcross.ssp.model.Banner" id="Banner">
	<id property="id" column="id"/>
	  <result property="bannerTemplateId" column="bannerTemplateId"/> 
	   <result property="status" column="status1"/>   
	<result property="orderId" column="orderId"/>
	<result property="campaignId" column="campaignId"/> 
  	<result property="adGroupId" column="adGroupId"/>   
    <result property="visitAddress" column="visitAddress"/> 
    <result property="clickAddress" column="clickAddress"/> 
    <result property="impressionAddress" column="impressionAddress"/>
    <result property="checked" column="checked"/>
    <result property="updated" column="updated"/>
    <result property="supdated" column="supdated"/> 
    <association property="bannerTemplate" javaType="com.cloudcross.ssp.model.BannerTemplate" resultMap="bannerTemplate"/>
</resultMap>  
<resultMap type="com.cloudcross.ssp.model.BannerTemplate" id="bannerTemplate">  
   <id property="id" column="bannerTemplateId"/>  
   <result property="advertiserId" column="advertiserId"/>
   <result property="agentId" column="agentId"/>  
   <result property="name" column="name"/>
    <result property="path" column="path"/> 
    <result property="pathSmall" column="pathSmall"/>
   <result property="type" column="type"/>
   <result property="width" column="width"/> 
   <result property="height" column="height"/> 
   <result property="status" column="status"/>   
   <result property="updated" column="updated"/>     
</resultMap>  

	
	<delete id="deleteBanner" parameterType="HashMap">
		delete from t_banner where
		id in
		<foreach item="id" collection="idList" open="(" separator=","
			close=")">
			#{id}
		</foreach>
	</delete>
	
	<update id="updateBannerStatus" parameterType="HashMap">
		update t_banner 
		set status=#{status},
		updated = #{updated},
		supdated = #{supdated} 
		where id in
		<foreach item="id" collection="idList" open="(" separator=","
			close=")">
			#{id}
		</foreach>
	</update>
	
	
	<select id="countByParams" parameterType="map" resultType="int">
	select count(a.id) from t_banner a ,
			t_banner_template b 
		<where>
				a.banner_template_id = b.id and a.adgroup_id=#{adGroupId}
				<if test="value !=null">
				and name like #{value}
				</if>
		</where>
		and  a.status != -1
	</select>
	
	<select id="countByAdGroupId" parameterType="long" resultType="int">
		select count(id) from t_banner
		where adgroup_id = #{adGroupId}
	</select>
	
	<select id="findById1" parameterType="long" resultType="com.cloudcross.ssp.model.Banner">
		select
		<include refid="base_column_banner"/>
		from t_banner
		<where>
		adgroup_id = #{adGroupId}
		</where>
	</select>
	
	<select id="findByIdAdvertiser" parameterType="long" resultMap="Banner">
		select  <include refid="base_column_banner"/> ,
		<include refid="base_column_bannertemplate"/>
		from t_banner a 
		LEFT JOIN t_banner_template b 
		on a.banner_template_id=b.id   
		<where>
				a.id=#{id}
		</where>
	</select>
	
	
	
	<select id="findByParams" parameterType="map" resultMap="Banner">  
		select <include refid="base_column_banner"/> ,
		<include refid="base_column_bannertemplate"/> 
		from t_banner a 
		LEFT JOIN t_banner_template b 
		on a.banner_template_id=b.id   
		<where>
				a.adgroup_id=#{adGroupId}
				<if test="value !=null">
				and name like #{value}
				</if>
		</where>
		and  a.status != -1
		order by a.id   desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<insert id="addBanner" parameterType="com.cloudcross.ssp.model.Banner" useGeneratedKeys="true"
		keyProperty="id">
		insert into
		t_banner(banner_template_id,order_id,campaign_id,adgroup_id,visit_address,click_address,impression_address,checked,memo,updated,supdated
		)
		values (
		#{bannerTemplateId},#{orderId},#{campaignId},#{adGroupId},#{visitAddress},#{clickAddress},#{impressionAddress},#{checked},#{memo},#{updated},#{supdated}
		)
	</insert>
	
	<update id="editBanner" parameterType="com.cloudcross.ssp.model.Banner">		 
		update t_banner a,t_banner_template b
		set b.name = #{bannerTemplate.name},
		b.updated = #{bannerTemplate.updated},
		a.visit_address = #{visitAddress},
		a.click_address = #{clickAddress},
		a.impression_address = #{impressionAddress},
		a.checked = 0,
		a.updated = #{updated},
		a.supdated = #{supdated}
		WHERE b.id = a.banner_template_id and a.id = #{id}
	</update>
	
	<update id="updataBanner" parameterType="com.cloudcross.ssp.model.Banner">		 
		update t_banner a,t_banner_template b
		set a.banner_template_id = #{bannerTemplateId},	
		b.name = #{bannerTemplate.name},
		b.updated = #{bannerTemplate.updated},
		a.visit_address = #{visitAddress},
		a.click_address = #{clickAddress},
		a.impression_address = #{impressionAddress},
		a.checked = 0,
		a.updated = #{updated},
		a.supdated = #{supdated}
		WHERE b.id = #{bannerTemplate.id} and a.id = #{id}
	</update>
	
	<update id="updataOperatorBanner" parameterType="HashMap">		 
		update t_banner_operator
		set checked = 0,supdated = #{supdated}
		WHERE banner_id in
		<foreach item="id" collection="idList" open="(" separator=","
			close=")">
			#{id}
		</foreach>
	</update>
<!-- 根据adgroupId修改banner的supdated -->
	<update id="updateBannerSupdated" parameterType="HashMap">
		update t_banner
			set t_banner.supdated= #{modifyDate}
			where  t_banner.adgroup_id in
			<foreach item="adgroupId" collection="adGroupIdList"
		      open="(" separator="," close=")">
		        #{adgroupId}
			</foreach>
	</update>
	
	<!-- 根据orderId修改banner的supdated -->
	<update id="updateBannerSupdatedByOrderId" parameterType="HashMap">
		update t_banner
			set t_banner.supdated=#{modifyDate}
			where  t_banner.order_id in
			<foreach item="orderId" collection="orderIdList"
		      open="(" separator="," close=")">
		        #{orderId}
			</foreach>
	</update>
	
	<!-- 根据campaignid修改banner的supdated -->
	<update id="updateBannerSupdatedByCampaignId" parameterType="HashMap">
		update t_banner
			set t_banner.supdated=#{modifyDate}
			where  t_banner.campaign_id in
			<foreach item="campaignId" collection="campaignIdList"
		      open="(" separator="," close=")">
		        #{campaignId}
			</foreach>
	</update>
	
	
	<update id="updateStatusByOrderId" parameterType="HashMap">
		update t_banner a,t_banner_template b
		set b.status=#{status},b.updated = #{modifyDate},a.updated = #{modifyDate} 
		where
		a.banner_template_id = b.id and
		a.order_id in
		<foreach item="orderId" collection="orderIdList" open="(" separator="," close=")">
			#{orderId}
		</foreach>
	</update>
	
	<update id="updateStatusByCampaignId" parameterType="HashMap">
		update t_banner a,t_banner_template b
		set b.status=#{status},b.updated = #{modifyDate},a.updated = #{modifyDate} 
		where
		a.banner_template_id = b.id and
		a.campaign_id in
		<foreach item="campaignId" collection="campaignIdList" open="(" separator="," close=")">
			#{campaignId}
		</foreach>
	</update>
	
	<update id="updateStatusByAdGroupId" parameterType="HashMap">
		update t_banner a,t_banner_template b
		set b.status=#{status},b.updated = #{modifyDate},a.updated = #{modifyDate} 
		where
		a.banner_template_id = b.id and
		a.adgroup_id in
		<foreach item="adGroupId" collection="adGroupIdList" open="(" separator="," close=")">
			#{adGroupId}
		</foreach>
	</update>
	
	
	<select id="countByParams1" parameterType="map" resultType="int">
		select count(a.id)
	 from t_banner a LEFT JOIN t_banner_template b on a.banner_template_id=b.id   
					<where>
					 a.status != -1 and b.status != -1
					<if test="orderMark == null">
					and a.order_id = #{orderId}
					</if>
					<if test="campaignMark == null">
					and a.campaign_id = #{campaignId}
					</if>
					<if test="adGroupMark == null">
					and a.adgroup_id = #{adGroupId}
					</if>
					<if test="status!=null">
					 and a.status = #{status} and b.status = #{status}
					</if>
		<if test="bannerTemplateName != null">
			and b.name like #{bannerTemplateName}
		</if>
		<if test="agentId!=null">
			 and b.agent_id = #{agentId}
		</if>
		<if test="agentId == null">
			and b.advertiser_id = #{advertiserId}
		</if>
					</where>
	</select>
	
	<select id="findByParams1" parameterType="map" resultMap="Banner">  
		select a.status as status1,a.id as id,a.banner_template_id as bannerTemplateId,a.order_id as orderId,a.campaign_id as campaignId,a.adgroup_id as adGroupId,a.visit_address as visitAddress,
	a.click_address as clickAddress,a.impression_address as impressionAddress, a.checked as checked,a.updated as updated,b.id as bannerTemplateId,b.agent_id as agentId,b.advertiser_id as advertiserId,b.agent_id as agentId,b.name as name,b.path as path,b.path_small as pathSmall,b.type as type,b.width as width,b.height as height,b.status as status,b.updated as updated from t_banner a LEFT JOIN t_banner_template b on a.banner_template_id=b.id   
					<where>
					 a.status != -1 and b.status != -1
					<if test="orderMark == null">
					and a.order_id = #{orderId}
					</if>
					<if test="campaignMark == null">
					and a.campaign_id = #{campaignId}
					</if>
					<if test="adGroupMark == null">
					and a.adgroup_id = #{adGroupId}
					</if>
					<if test="status!=null">
					 and a.status = #{status} and b.status = #{status}
					</if>
		<if test="bannerTemplateName != null">
			and b.name like #{bannerTemplateName}
		</if>
		<if test="agentId!=null">
			 and b.agent_id = #{agentId}
		</if>
		<if test="agentId == null">
			and b.advertiser_id = #{advertiserId}
		</if>
					</where>
		order by a.id   desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	
	
	
	<select id="findByParams2" parameterType="map" resultMap="Banner">  
		select a.status as status1,a.id as id,a.banner_template_id as bannerTemplateId,a.order_id as orderId,a.campaign_id as campaignId,a.adgroup_id as adGroupId,a.visit_address as visitAddress,
	a.click_address as clickAddress,a.impression_address as impressionAddress, a.checked as checked,a.updated as updated,b.id as bannerTemplateId,b.agent_id as agentId,b.advertiser_id as advertiserId,b.agent_id as agentId,b.name as name,b.path as path,b.path_small as pathSmall,b.type as type,b.width as width,b.height as height,b.status as status,b.updated as updated from t_banner a LEFT JOIN t_banner_template b on a.banner_template_id=b.id   
					<where>
					 a.status != -1 and b.status != -1
					<if test="orderMark == null">
					and a.order_id = #{orderId}
					</if>
					<if test="campaignMark == null">
					and a.campaign_id = #{campaignId}
					</if>
					<if test="adGroupMark == null">
					and a.adgroup_id = #{adGroupId}
					</if>
					<if test="status!=null">
					 and a.status = #{status} and b.status = #{status}
					</if>
		<if test="bannerTemplateName != null">
			and b.name like #{bannerTemplateName}
		</if>
		<if test="agentId!=null">
			 and b.agent_id = #{agentId}
		</if>
		<if test="agentId == null">
			and b.advertiser_id = #{advertiserId}
		</if>
					</where>
		order by a.id   desc
	</select>
	
	<update id="updateOperatorBannerTime" parameterType="HashMap">
			update t_banner_operator 
			set supdated = #{supdated}
			where banner_id in 
			<foreach item="id" collection="idList" open="(" separator=","
			close=")">
			#{id}
			</foreach>
	
	</update>
	
	
	
</mapper>