<?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="orderSqlMapper">
	
	<sql id="base_column">
		t_order.id as id,
		t_order.advertiser_id as advertiserId,
		t_order.operator_id as operatorId,
		t_order.name as name,
		t_order.budget as budget,
		t_order.comment as comment,
		t_order.status as status,
		t_order.updated as updated
	</sql>
	
	<select id="selectByParams"  parameterType="map" resultType="com.cloudcross.ssp.model.Order">
		select 
		<include refid="base_column" />
	,count(t_campaign.order_id) as campaignNumber from t_order left join t_campaign on t_order.id = t_campaign.order_id 
        and t_campaign.status != -1 and t_campaign.advertiser_id = #{advertiserId}
		where 1=1
		<if test="advMark!=null">
			and t_order.agent_id = #{agentId}
		</if> 
		<if test="advMark==null">
			and t_order.advertiser_id = #{advertiserId}
		</if> 
		and t_order.status != -1
		<if test="value!=null">
			and t_order.name like #{value}
		</if>
		
		<if test="status!=null">	
			and t_order.status = #{status}
		</if>
		group by t_order.id	
		order by t_order.id   desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	  <select id="countByParams" parameterType="java.util.Map" resultType="java.lang.Integer">
		select count(t_order.id)
		from t_order where status != -1
		<if test="advMark!=null">
			and t_order.agent_id = #{agentId}
		</if> 
		<if test="advMark==null">
			and t_order.advertiser_id = #{advertiserId}
		</if> 
		<if test="value!=null">
			and t_order.name like #{value}
		</if>
		<if test="status!=null">	
			and t_order.status = #{status}
		</if>
	</select>
	
	
	
		<select id="selectByParamsOperator"  parameterType="map" resultType="com.cloudcross.ssp.model.Order">
		select 
		<include refid="base_column" />
	,count(t_campaign.order_id) as campaignNumber from t_order left join t_campaign on t_order.id = t_campaign.order_id 
		where
		t_order.advertiser_id = #{advertiserId} 
		and t_order.status != -1
		<if test="key!=null and value!=null">
			<choose>
				<when test="key=='name'">
					and t_order.name like #{value}
				</when>
			</choose>	
		</if>
		
		<if test="status!=null">	
			and t_order.status = #{status}
		</if>
		group by t_order.id	
		order by t_order.id   desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
		<select id="countByParamsOperator" parameterType="map" resultType="int">
		select count(t_order.id)
		from t_order where status != -1
		and t_order.advertiser_id = #{advertiserId} 
		<if test="key!=null and value!=null">
			<choose>
				<when test="key=='name'">
					and t_order.name like #{value}
				</when>
			</choose>	
		</if>
		
		<if test="status!=null">	
			and t_order.status = #{status}
		</if>
		
		 <if test="status!=null">	
			and t_order.status = #{status}
		</if>	
	</select>
	
	<select id="selectByParamsBack"  parameterType="map" resultType="com.cloudcross.ssp.model.Order">
		select 
		<include refid="base_column" />
	,count(t_campaign.order_id) as campaignNumber from t_order left join t_campaign on t_order.id = t_campaign.order_id 
		where
		t_order.advertiser_id = #{advertiserId} 
		and t_order.status != -1
		<if test="value!=null">
			and t_order.name like #{value}
		</if>
		
		<if test="status!=null">	
			and t_order.status = #{status}
		</if>
		group by t_order.id	
		order by t_order.id   desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	 <select id="countByParamsBack" parameterType="map" resultType="int">
		select count(t_order.id)
		from t_order where status != -1
		and t_order.advertiser_id = #{advertiserId} 
		<if test="value!=null">
			and t_order.name like #{value}
		</if>
		
		<if test="status!=null">	
			and t_order.status = #{status}
		</if>
		
		 <if test="status!=null">	
			and t_order.status = #{status}
		</if>	
	</select>
	

	
	 
	
	  <select id="selectByAdvertiserType" parameterType="string" resultType="com.cloudcross.ssp.model.Order">
		select
		<include refid="base_column" />
		from t_order where advertiser_id=#{advertiserType} 
		and status != -1
	</select>
	
	
	<select id="selectByStatus"  parameterType="map" resultType="com.cloudcross.ssp.model.Advertiser">
		select 
		<include refid="base_column" />
		,count(t_campaign.order_id) as campaignNumber from t_order left join t_campaign on t_order.id = t_campaign.order_id 
		 where 1 = 1 
		 and t_order.status = #{status}
		 and t_order.advertiser_id = #{advertiserId}  
		group by t_order.id
		order by t_order.id   desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	

	
	<select id="selectById" parameterType="int" resultType="com.cloudcross.ssp.model.Order">
		select 
		<include refid="base_column" />
		from t_order
		<where>
			id = #{id} and
			status != -1
		</where>
	</select>
	
	<select id="findByAdvertiserId" parameterType="int" resultType="com.cloudcross.ssp.model.Order">
		select 
		<include refid="base_column" />
		from t_order
		<where>
			advertiser_id = #{advertiserId}
			and
			status != -1
		</where>
	</select>
	
	<select id="selectByName" parameterType="String" resultType="long">
		select
		max(id)
		from t_order
		<where>
			t_order.name = #{name} and
			status != -1
		</where>
	</select>
	
	
	<insert id="addOrder" parameterType="com.cloudcross.ssp.model.Order">
		insert into t_order(name,budget,comment,updated,advertiser_id,agent_id,operator_id)
		values(#{name},#{budget},#{comment},#{updated},#{advertiserId},#{agentId},#{operatorId})
	</insert>
	
	<insert id="addOrderBack" parameterType="com.cloudcross.ssp.model.Order">
		insert into t_order(name,budget,comment,updated,advertiser_id)
		values(#{name},#{budget},#{comment},#{updated},#{advertiserId})
	</insert>
	
	<update id="editOrder" parameterType="com.cloudcross.ssp.model.Order">
		update t_order set t_order.name=#{name},t_order.budget=#{budget},t_order.comment=#{comment},
		<if test="status !=null">
			status=#{status},
			</if>
			updated=#{updated} 
		 where t_order.id=#{id}
	</update>
	
	<update id="updateOrderStatus" parameterType="HashMap">
		update t_order 
			set status = #{status},updated = #{modifyDate}
			where t_order.id in
			<foreach item="orderId" collection="orderIdList"
		      open="(" separator="," close=")">
		        #{orderId}
			</foreach>
	</update>
	
	
	
	<!-- 导出Excel用 -->
	<select id="findDeliverEffectAnalysis" parameterType="map" resultType="com.cloudcross.ssp.model.Order">
		select 
		<include refid="base_column" />
	,count(t_campaign.order_id) as campaignNumber from t_order left join t_campaign on t_order.id = t_campaign.order_id 
		where
		t_order.advertiser_id = #{advertiserId} 
		and t_order.status != -1
		<if test="key!=null and value!=null">
			<choose>
				<when test="key=='name'">
					and t_order.name like #{value}
				</when>
			</choose>	
		</if>
		
		<if test="status!=null">	
			and t_order.status = #{status}
		</if>
		group by t_order.id	
		order by t_order.id   desc
	</select>
	
	<select id="selectOrderNameById" parameterType="int" resultType="String">
		select name from t_order where id = #{id}
	</select>
	<update id="updateStatusByAdvertiserId" parameterType="HashMap">
		update t_order
			set status = #{status},updated = NOW()
			where t_order.advertiser_id in
			<foreach item="advertiserId" collection="advertiserIdList"
		      open="(" separator="," close=")">
		        #{advertiserId}
			</foreach>
	</update>
</mapper>
	
