PublisherRevenueSplitCache.scala

package com.yieldmo.common.publisher;

import com.google.common.cache._
import com.yieldmo.common.DBUtil
import java.util.concurrent.TimeUnit
import java.sql.Connection
import anorm._
import anorm.SqlParser._
import resource._

object PublisherRevenueSplitCache {

  // Takes in a new Tuple3(publisherId, advertiserId, eventTime) = tuple2(revenueShareForTime, fee)
  val cache: LoadingCache[(Long,Long,Long), (Double,Double)] =
    CacheBuilder
      .newBuilder
      .maximumSize(10000) // 10k tuples
      .expireAfterAccess(1, TimeUnit.HOURS)
      .build(new CacheLoader[(Long,Long,Long), (Double, Double)] {
        val sql: String = """
SELECT CASE adv.intro_publisher_id
           WHEN p.publisher_id THEN agreement.publisher_sold_rate
           ELSE agreement.standard_rate
       END AS rate,
       processing_fee
FROM yieldmo.placement p
JOIN yieldmo.category_placement cp ON cp.placement_id = p.placement_id
JOIN yieldmo.ad_group_bid agb ON agb.category_id = cp.category_id
JOIN yieldmo.ad_group ag ON ag.ad_group_id = agb.ad_group_id
JOIN yieldmo.campaign c ON c.campaign_id = ag.campaign_id
JOIN yieldmo.advertiser adv ON c.advertiser_id = adv.advertiser_id
JOIN yieldmo.publisher_agreement agreement ON p.publisher_id = agreement.publisher_id
WHERE p.publisher_id = {publisherId}
  AND adv.advertiser_id = {advertiserId}
  AND (({eventTime} BETWEEN agreement.effective_start AND agreement.effective_end)
       AND {eventTime} BETWEEN agreement.effective_start AND agreement.effective_end
       OR agreement.effective_end IS NULL)
"""

        override def load(key: (Long,Long,Long)): (Double, Double) = {
          managed(DBUtil.getYieldmoRODataSourceFactory.getConnection) acquireAndGet { connection =>
            implicit val getConnection = connection
            val result = SQL(sql)
              .on('publisherId -> key._1, 'advertiserId -> key._2, 'eventTime -> key._3)()
              .map(row => row[Double]("rate") -> row[Double]("processing_fee")).toList
            result(0)
          }
        }
      })

}