<?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="advBalanceSqlMapper">
	
	<sql id="base_column">
		t_adv_balance.id as id,
		account_id as accountId,
		agent_id as agentId, 
		advertiser_id as advertiserId,
		act_time as actTime,
		num,
	<!-- 	type, -->
		charge,
		allocate,
		consume,
	    t_advertiser.name as advertiserName,
	    t_agent.name as agentName,                     
		balance,
		memo
	</sql>
	<sql id="base_column2">
	t_adv_balance.id as id,
		t_adv_balance.account_id as accountId,
		t_adv_balance.agent_id as agentId,
		t_adv_balance.advertiser_id as advertiserId,
		t_adv_balance.act_time as actTime,
		t_adv_balance.num as num,
		t_adv_balance.charge as charge,
		t_adv_balance.allocate as allocate,
		t_adv_balance.memo as memo,
		t_adv_balance.consume as consume,
		t_adv_balance.balance as balance,
		t_adv_balance.status as status
	</sql>
	<select id="findAdvBalance" parameterType="int" resultType="com.cloudcross.ssp.model.AdvBalance">
		select * from 	t_adv_balance where id=#{advBalanceId}
	</select>
		
	<select id="selectByParams"  parameterType="map" resultType="com.cloudcross.ssp.model.AdvBalance">
		select <include refid="base_column2" />
		from t_adv_balance
		<where>
		date_format(act_time,'%Y-%c-%d') &gt;= #{startDate} and #{endDate} &gt;=date_format(act_time,'%Y-%c-%d')
			<if test="agentId != null">
			<if test="advertiserId == null">
			and agent_id = #{agentId}
			</if>
			<if test="advertiserId != null">
			and advertiser_id = #{advertiserId} and allocate > 0
			</if>
			</if>
			<if test="agentId == null">
			and agent_id = 0 and advertiser_id = #{advertiserId}
			</if>
			<if test="type == 'charge'">
			 and charge > 0
			</if>
			<if test="type == 'allocate'">
			 and allocate > 0
			</if>
			<if test="type == 'consume'">
			 and consume > 0
			</if>
		</where>
		order by  act_time  desc
		limit #{pager.offset}, #{pager.limit}
	</select>	
	
	<select id="selectByParams1"  parameterType="map" resultType="com.cloudcross.ssp.model.AdvBalance">
		select <include refid="base_column2" />
		from t_adv_balance
		<where>
		date_format(act_time,'%Y-%c-%d') &gt;= #{startDate} and #{endDate} &gt;=date_format(act_time,'%Y-%c-%d')
			<if test="agentId != null">
			<if test="advertiserId == null">
			and agent_id = #{agentId}
			</if>
			<if test="advertiserId != null">
			and advertiser_id = #{advertiserId} and allocate > 0
			</if>
			</if>
			<if test="agentId == null">
			and agent_id = 0 and advertiser_id = #{advertiserId}
			</if>
			<if test="type == 'charge'">
			 and charge > 0
			</if>
			<if test="type == 'allocate'">
			 and allocate > 0
			</if>
			<if test="type == 'consume'">
			 and consume > 0
			</if>
		</where>
		order by  act_time  desc
	</select>	
	
	
    <select id="selectByParams2"  parameterType="map" resultType="com.cloudcross.ssp.model.AdvBalance">
		select 
		<include refid="base_column2" />
		from t_adv_balance
        <where>
		date_format(act_time,'%Y-%c-%d') &gt;= #{startDate} and #{endDate} &gt;=date_format(act_time,'%Y-%c-%d')
			<if test="advertiserId != null">
			and advertiser_id = #{advertiserId} and allocate > 0
			</if>
			<if test="advertiserId == null">
			and agent_id = #{agentId} and allocate > 0
			</if>
		</where>
		order by  act_time desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	 <select id="selectByParams3"  parameterType="map" resultType="com.cloudcross.ssp.model.AdvBalance">
		select 
		<include refid="base_column2" />
		from t_adv_balance
        <where>
		date_format(act_time,'%Y-%c-%d') &gt;= #{startDate} and #{endDate} &gt;=date_format(act_time,'%Y-%c-%d')
			<if test="advertiserId != null">
			and advertiser_id = #{advertiserId} and allocate > 0
			</if>
			<if test="advertiserId == null">
			and agent_id = #{agentId} and allocate > 0
			</if>
		</where>
		order by  act_time desc
	</select>
	
	
	<select id="countByParams" parameterType="map" resultType="int">
		select count(id) from t_adv_balance
		<where>
		date_format(act_time,'%Y-%c-%d') &gt;= #{startDate} and #{endDate} &gt;=date_format(act_time,'%Y-%c-%d')
			<if test="agentId != null">
			<if test="advertiserId == null">
			and agent_id = #{agentId}
			</if>
			<if test="advertiserId != null">
			and advertiser_id = #{advertiserId} and allocate > 0
			</if>
			</if>
			<if test="agentId == null">
			and agent_id = 0 and advertiser_id = #{advertiserId}
			</if>
			<if test="type == 'charge'">
			 and charge > 0
			</if>
			<if test="type == 'allocate'">
			 and allocate > 0
			</if>
			<if test="type == 'consume'">
			 and consume > 0
			</if>
		</where>
	</select>
	
	<select id="countByParams2" parameterType="map" resultType="int">
		select count(id) from t_adv_balance
		<where>
		date_format(act_time,'%Y-%c-%d') &gt;= #{startDate} and #{endDate} &gt;=date_format(act_time,'%Y-%c-%d')
			<if test="advertiserId != null">
			and advertiser_id = #{advertiserId} and allocate > 0
			</if>
			<if test="advertiserId == null">
			and agent_id = #{agentId} and allocate > 0
			</if>
		</where>
	</select>
	
	<select id="selectById" parameterType="int" resultType="com.cloudcross.ssp.model.AdvBalance">
		select 
		<include refid="base_column2" />
		from t_adv_balance
		<where>
			id = #{id} 
		</where>
	</select>


	<select id="countByParamsBack" parameterType="map" resultType="int">
		select count(id) from t_adv_balance
		<where> 
		advertiser_id = 0 and agent_id != 0 and status != -1 and abs(charge) > 0
		<if test="actTime != null">
			and act_time like #{actTime}
		</if>
		</where>
	</select>
	
	<select id="findByParamsBack" parameterType="map" resultType="com.cloudcross.ssp.model.AdvBalance">
		select <include refid="base_column2"/>, name as agentName from t_adv_balance left join
		t_adv_agent on t_adv_balance.agent_id = t_adv_agent.id
		<where> 
		t_adv_balance.advertiser_id = 0 and t_adv_balance.agent_id != 0 and t_adv_balance.status != -1
		and abs(t_adv_balance.charge) > 0
		<if test="actTime != null">
			and act_time like #{actTime}
		</if>
		</where>
		order by act_time desc
		limit #{pager.offset}, #{pager.limit} 
	</select>
	
	<select id="findByParams1Back" parameterType="map" resultType="com.cloudcross.ssp.model.AdvBalance">
	select <include refid="base_column2"/>, name as agentName from t_adv_balance left join
		t_adv_agent on t_adv_balance.agent_id = t_adv_agent.id
		<where> 
		t_adv_balance.advertiser_id = 0 and t_adv_balance.agent_id != 0 and t_adv_balance.status != -1
		<if test="actTime != null">
			and act_time like #{actTime}
		</if>
		</where>
		order by act_time desc
	</select>
	
	<select id="findBalanceByAgentIdBack" parameterType="long" resultType="double">
		select balance from t_adv_balance where agent_id = #{agentId} and status != -1 order by id desc limit 1
	</select>
	
	
	<insert id="addBack" parameterType="com.cloudcross.ssp.model.AdvBalance">
		insert into t_adv_balance(account_id,agent_id,act_time,num,charge,balance) 
		values(#{accountId},#{agentId},#{actTime},#{num},#{charge},#{balance})
	</insert>
	
	<select id="findByIdBack" parameterType="long" resultType="com.cloudcross.ssp.model.AdvBalance">
		select <include refid="base_column2"/> from t_adv_balance
		where id = #{id} and status != -1
	</select>
	
	<update id="updateBalanceByAgentIdBack" parameterType="map">
		  update t_adv_balance set balance = #{balance}
    	  where id in (select * from (select id from t_adv_balance where agent_id = #{agentId} and status != -1 order by id desc limit 1) as a)
	</update>
	
	<delete id="deleteAdvBalanceByIdBack" parameterType="long">
		delete from t_adv_balance where id = #{id}
	</delete>
	
	<select id="findBalanceByAdvertiserId" parameterType="long" resultType="double">
		select balance from t_adv_balance where advertiser_id = #{advertiserId} and status != -1 order by id desc limit 1
	</select>
	
	
	<insert id="insertAgentAllocation" parameterType="map">
		insert into t_adv_balance(account_id, agent_id, act_time, num, allocate, balance) 
		values(#{accountId}, #{agentId}, #{actTime}, #{num}, #{allocate}, #{agentBalance})
	</insert>
	
	<insert id="insertAdvertiserAllocation" parameterType="map">
		insert into t_adv_balance(account_id, advertiser_id, act_time, num, allocate, balance) 
		values(#{accountId}, #{advertiserId}, #{actTime}, #{num}, #{allocate}, #{advertiserBalance})
	</insert>
	
	
	<insert id="backOut" parameterType="map">
	insert into t_adv_balance(account_id, agent_id, act_time, num, charge, balance)
	values(#{accountId}, #{agentId}, #{actTime}, #{num}, #{charge}, #{balance})
	</insert>
</mapper>