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

	<select id="findAllCorrelation" parameterType="HashMap" resultType="com.cloudcross.ssp.model.Correlation">
		select t_adv_agent.operator_id as operatorId
		,t_adv_agent.id as agentId
		,t_adv_agent.name as agentName
		, t_operator.company_name as operatorName
		from t_adv_agent,t_operator 
		where operator_id !=0 and t_operator.id = t_adv_agent.operator_id
		order by  t_adv_agent.id   desc
	limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="countAllCorrelation"  resultType="int">
		select count(t_adv_agent.operator_id)
		from t_adv_agent,t_operator 
		where operator_id !=0 and t_operator.id = t_adv_agent.operator_id
	</select>
	
	
	<select id="findCorrelationByOperatorId"  resultType="com.cloudcross.ssp.model.Correlation" parameterType="Long">
		select t_adv_agent.operator_id as operatorId
		,t_adv_agent.id as agentId
		,t_adv_agent.name as agentName
		, t_operator.company_name 
		from t_adv_agent,t_operator 
		where operator_id = #{operatorId} and t_operator.id = t_adv_agent.operator_id
	</select>
	
	<update id="addCorrelation11" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_account
		set 
		t_account.operator_id= #{operatorId}
		where 
		t_account.agent_id = #{agentId}
	</update>
	
	<update id="addCorrelation12" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_adv_agent a
		set 
		a.operator_id=#{operatorId}
		where 
		a.id = #{agentId} 
	</update>
	
	<update id="addCorrelation13" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_advertiser b
		set 
		b.operator_id=#{operatorId},b.updated = NOW()
		where 
		b.agent_id = #{agentId}
	</update>
	<update id="addCorrelation14" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_order c
		set 
		c.operator_id=#{operatorId},c.updated = NOW()
		where 
		c.agent_id = #{agentId}
	</update>
	<update id="addCorrelation15" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_campaign d
		set 
		d.operator_id=#{operatorId},d.updated = NOW()
		where 
		d.agent_id = #{agentId} 
	</update>
	<update id="addCorrelation16" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_adgroup e
		set 
		e.operator_id=#{operatorId},e.updated = NOW()
		where 
		e.agent_id = #{agentId} 
	</update>
	
		<update id="addCorrelation21" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_adv_banner f
		set 
		f.operator_id=#{operatorId}
		where 
		f.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
		<update id="addCorrelation22" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_adv_location g	
		set 
		g.operator_id=#{operatorId}
		where 
		
		g.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
		

	</update>
		<update id="addCorrelation23" parameterType="com.cloudcross.ssp.model.Correlation">
		update 

		t_rpt_adv_os h
		set 
		h.operator_id=#{operatorId}
		where 
		h.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
		<update id="addCorrelation24" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_adv_place i
		set 
		i.operator_id=#{operatorId}
		where 
		i.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
		<update id="addCorrelation25" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_advertiser k
		set 
		k.operator_id=#{operatorId}
		where 
		k.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
	<update id="addCorrelation26" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_advertiser_hour l
		set 
		l.operator_id=#{operatorId}
		where 
		l.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
	
	<update id="deleteCorrelation11" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_account
		
		set 
		t_account.operator_id= 0
		
		where 
		t_account.agent_id = #{agentId}
		
	</update>
	
	<update id="deleteCorrelation12" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_adv_agent a
		set 
		a.operator_id=0
		where 
		a.id = #{agentId} 
	</update>
	
	<update id="deleteCorrelation13" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_advertiser b
		set 
		
		b.operator_id=0,b.updated = NOW()

		where 
		
		b.agent_id = #{agentId} 

	</update>
	
	<update id="deleteCorrelation14" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		
		t_order c
		
		set 
		
		c.operator_id=0,c.updated = NOW()

		where 
		
		c.agent_id = #{agentId} 
		
	</update>
	
	<update id="deleteCorrelation15" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		
		t_campaign d
		
		set 
		
		d.operator_id=0,d.updated = NOW()

		where 
		
		d.agent_id = #{agentId} 
		
	</update>
	
	<update id="deleteCorrelation16" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_adgroup e
		set 
		e.operator_id=0,e.updated = NOW()
		where 
		e.agent_id = #{agentId} 
	</update>

	<update id="deleteCorrelation21" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_adv_banner f
		
		set 
		f.operator_id=0
		where 
		f.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
		
	</update>
	<update id="deleteCorrelation22" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		
		t_rpt_adv_location g
		set 
		
		g.operator_id=0
		

		where 
		
		g.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
		
	</update>
	<update id="deleteCorrelation23" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_adv_os h
		set 
		h.operator_id=0
		where 
		h.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
	<update id="deleteCorrelation24" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_adv_place i
		set 
		i.operator_id=0
		where 
		i.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
	<update id="deleteCorrelation25" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_advertiser k
		set 
		k.operator_id=0
		where 
		k.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
	<update id="deleteCorrelation26" parameterType="com.cloudcross.ssp.model.Correlation">
		update 
		t_rpt_advertiser_hour l
		set 
		l.operator_id=0
		where 
		l.advertiser_id in (select id from t_advertiser where agent_id = #{agentId})
	</update>
	
</mapper>