<?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="financeSqlMapper">
	
	<sql id="base_column">
		id,
		account_id as accountId,
		act_time as actTime,
		num,
	    t_advertiser.name as advertiserName,                    
		memo,
		agent_id as agentId,
		advertiser_id as advertiserId,
		charge,
		allocate,
		consume,
		balance
	</sql>
	
	<select id="findByParams"  parameterType="map" resultType="com.cloudcross.ssp.model.Finance">
	   select advertiserId , advertiserName, (ifnull(sum(allocate), 0) - ifnull(sum(consume), 0)) as adBalance, 
       ifnull(sum(allocate), 0) as sumAllocation, max(actTime) as upDated from
      (select a.agent_id as agentId,a.id as advertiserId, a.name as advertiserName, b.allocate as allocate, b.consume as consume, b.act_time
      as actTime
       from t_advertiser as a left join t_adv_balance as b 
      	on a.id = b.advertiser_id) aa
       where 
       <if test="agentId != null">
            agentId = #{agentId}
       </if>
			<if test="advertiserId != null">
				and advertiserId = #{advertiserId}
			</if>
			group by advertiserId
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByParams1" parameterType="map" resultType="com.cloudcross.ssp.model.Finance">
		select advertiserId , advertiserName, (ifnull(sum(allocate), 0) - ifnull(sum(consume), 0)) as adBalance, 
       ifnull(sum(allocate), 0) as sumAllocation, max(actTime) as upDated from
      (select a.agent_id as agentId,a.id as advertiserId, a.name as advertiserName, b.allocate as allocate, b.consume as consume, b.act_time
      as actTime
       from t_advertiser as a left join t_adv_balance as b 
      	on a.id = b.advertiser_id) aa
       where 
       <if test="agentId != null">
            agentId = #{agentId}
       </if>
			<if test="advertiserId != null">
				and advertiserId = #{advertiserId}
			</if>
			group by advertiserId
	</select>
	
	<select id="countByParams" parameterType="map" resultType="int">
		select count(id) from t_advertiser
		<where>
		1 = 1
			<if test="agentId != null">
			and	agent_id = #{agentId}
			</if>
			<if test="advertiserId != null">
			and	id = #{advertiserId}
			</if>
		</where>
		
	</select>
	
	<!-- 最主要的是下面的balance，此时的balance对应为广告主的余额 -->
	<select id="findById" parameterType="long" resultType="com.cloudcross.ssp.model.Finance">
		select 
		id,
		account_id as accountId,
		act_time as actTime,
		num,                  
		memo,
		agent_id as agentId,
		advertiser_id as advertiserId,
		charge,
		allocate,
		consume,
		balance
		from t_adv_balance
		where id = #{id}
	</select>
	
	<insert id="editFinance"  parameterType="com.cloudcross.ssp.model.Finance">
          insert into t_adv_balance
          (account_id,agent_id,advertiser_id,charge,allocate,consume,memo,num,balance,act_time)
          values
          (#{accountId},#{agentId},#{advertiserId},0,#{allocate},0,#{memo},#{num},#{balance}+#{allocate},NOW())                    
	</insert>
	
	<insert id="addFinance"  parameterType="com.cloudcross.ssp.model.Finance">
	      insert into t_adv_balance
          (account_id,agent_id,advertiser_id,allocate,balance,act_time)
          values
          (#{accountId},#{agentId},0,#{allocate},#{agentBalance}-#{allocate},NOW())    	
	</insert>
	
	<select id="findByBalance"  parameterType="map" resultType="com.cloudcross.ssp.model.Finance" >
	     select a.balance as agentBalance from t_adv_balance a 
	     where a.agent_id = #{agentId} and a.advertiser_id = 0
	     and a.account_id = #{accountId}
	     order by a.act_time desc limit 1
	</select>

</mapper>