DailyDetailDao.java

package ru.dsi.bgbilling.kernel.discount.bean;

import bitel.billing.server.contract.bean.CostSum;
import bitel.billing.server.contract.bean.ServiceCostCache;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import ru.bitel.bgbilling.common.BGException;
import ru.bitel.bgbilling.kernel.module.common.bean.Service;
import ru.bitel.bgbilling.kernel.module.server.bean.ServiceManager;
import ru.bitel.bgbilling.server.util.DefaultServerSetup;
import ru.bitel.bgbilling.server.util.ServerUtils;
import ru.bitel.common.TimeUtils;
import ru.bitel.common.Utils;

public class DailyDetailDao
{
  private Connection con;
  private DefaultServerSetup setup;
  private String tableNamePrefix = "dsi_daily_detail";
  private static final Object updateMonthDetailMutex = new Object();
  
  public DailyDetailDao(Connection con, DefaultServerSetup setup)
  {
    this.con = con;
    this.setup = setup;
  }
  
  private void setDayDetailAccumulative(int dd, String tableName, Collection<CostSum> amounts, Map<Integer, Map<Integer, BigDecimal>> prevAmounts, String sids, String cids)
    throws SQLException
  {
    if (null == amounts) {
      return;
    }
    List<Integer> sid_list = Utils.toIntegerList(sids);
    List<Integer> cid_list = Utils.toIntegerList(cids);
    
    String query = "INSERT INTO " + tableName + " (dd, cid, sid, summa) values(?,?,?,?) ON DUPLICATE KEY UPDATE summa=?";
    
    PreparedStatement ps = this.con.prepareStatement(query);
    int count = 0;
    for (CostSum cost : amounts) {
      if (((sid_list.contains(Integer.valueOf(cost.sid))) || (sid_list.size() <= 0)) && (
        (cid_list.contains(Integer.valueOf(cost.cid))) || (cid_list.size() <= 0)))
      {
        Map<Integer, BigDecimal> cidAmounts = (Map)prevAmounts.get(Integer.valueOf(cost.cid));
        if (null == cidAmounts)
        {
          cidAmounts = new HashMap();
          prevAmounts.put(Integer.valueOf(cost.cid), cidAmounts);
        }
        BigDecimal summa = (BigDecimal)cidAmounts.get(Integer.valueOf(cost.sid));
        if (null == summa)
        {
          summa = BigDecimal.ZERO;
          cidAmounts.put(Integer.valueOf(cost.sid), summa);
        }
        ps.setInt(1, dd);
        ps.setInt(2, cost.cid);
        ps.setInt(3, cost.sid);
        ps.setBigDecimal(4, cost.cost.subtract(summa));
        ps.setBigDecimal(5, cost.cost.subtract(summa));
        ps.addBatch();
        count++;
        if (count % 1000 == 0)
        {
          ps.executeBatch();
          if (!this.con.getAutoCommit()) {
            this.con.commit();
          }
        }
        cidAmounts.put(Integer.valueOf(cost.sid), cost.cost);
      }
    }
    ps.executeBatch();
    ps.close();
  }
  
  public void updateMonthDetailAccumulativeInit(Calendar month, String sids, String cids)
    throws SQLException
  {
    String tableName = ServerUtils.getMonthTableName(this.tableNamePrefix, month.getTime());
    
    createDetailTable(tableName);
    clearMonthDetail(tableName, sids, cids);
  }
  
  public void updateMonthDetailAccumulativeIter(Calendar month, int dd, Collection<CostSum> amounts, Map<Integer, Map<Integer, BigDecimal>> prevAmounts, String sids, String cids)
    throws SQLException
  {
    if (null == amounts) {
      return;
    }
    synchronized (updateMonthDetailMutex)
    {
      boolean autocommit = this.con.getAutoCommit();
      this.con.setAutoCommit(false);
      String tableName = ServerUtils.getMonthTableName(this.tableNamePrefix, month.getTime());
      setDayDetailAccumulative(dd, tableName, amounts, prevAmounts, sids, cids);
      this.con.commit();
      this.con.setAutoCommit(autocommit);
    }
  }
  
  @Deprecated
  public void updateMonthDetailAccumulative(Calendar month, Map<Integer, Collection<CostSum>> dailyAmounts, String sids, String cids)
    throws SQLException
  {
    Map<Integer, Map<Integer, BigDecimal>> prevAmounts = new HashMap();
    String tableName = ServerUtils.getMonthTableName(this.tableNamePrefix, month.getTime());
    
    createDetailTable(tableName);
    synchronized (updateMonthDetailMutex)
    {
      boolean autocommit = this.con.getAutoCommit();
      this.con.setAutoCommit(false);
      clearMonthDetail(tableName, sids, cids);
      for (int dd = 1; dd <= month.getActualMaximum(5); dd++)
      {
        if (null == dailyAmounts.get(Integer.valueOf(dd))) {
          break;
        }
        setDayDetailAccumulative(dd, tableName, (Collection)dailyAmounts.get(Integer.valueOf(dd)), prevAmounts, sids, cids);
      }
      this.con.commit();
      this.con.setAutoCommit(autocommit);
    }
  }
  
  public void updateMonthDetail(Calendar month, Map<Integer, ServiceCostCache> dailyCostCache, String sids, String cids)
    throws SQLException
  {
    String tableName = ServerUtils.getMonthTableName(this.tableNamePrefix, month.getTime());
    
    createDetailTable(tableName);
    synchronized (updateMonthDetailMutex)
    {
      boolean autocommit = this.con.getAutoCommit();
      this.con.setAutoCommit(false);
      clearMonthDetail(tableName, sids, cids);
      for (int dd = 1; dd <= month.getActualMaximum(5); dd++)
      {
        ServiceCostCache dayCostCache = (ServiceCostCache)dailyCostCache.get(Integer.valueOf(dd));
        if (null != dayCostCache) {
          setDayDetail(dd, tableName, dayCostCache, sids, cids);
        }
      }
      this.con.commit();
      this.con.setAutoCommit(autocommit);
    }
  }
  
  public void updateMonthDetail(Calendar month, Map<Integer, ServiceCostCache> dailyCostCache, int mid, String cids)
    throws SQLException, BGException
  {
    StringBuilder sids = new StringBuilder();
    ServiceManager sm = new ServiceManager(this.con);
    List<Service> services = sm.list(mid);
    for (Service service : services)
    {
      sids.append(service.getId());
      sids.append(",");
    }
    sm.recycle();
    if (sids.length() > 0) {
      sids.setLength(sids.length() - 1);
    }
    updateMonthDetail(month, dailyCostCache, sids.toString(), cids);
  }
  
  private void setDayDetail(int dd, String tableName, ServiceCostCache costCache, String sids, String cids)
    throws SQLException
  {
    if (null == costCache) {
      return;
    }
    List<Integer> sid_list = Utils.toIntegerList(sids);
    List<Integer> cid_list = Utils.toIntegerList(cids);
    
    String query = "INSERT INTO " + tableName + " (dd, cid, sid, summa) values(?,?,?,?) ON DUPLICATE KEY UPDATE summa=?";
    
    PreparedStatement ps = this.con.prepareStatement(query);
    int count = 0;
    Collection<CostSum> amounts = costCache.getAmounts();
    if (null == amounts) {
      return;
    }
    for (CostSum cost : amounts) {
      if (((sid_list.contains(Integer.valueOf(cost.sid))) || (sid_list.size() <= 0)) && (
        (cid_list.contains(Integer.valueOf(cost.cid))) || (cid_list.size() <= 0)))
      {
        ps.setInt(1, dd);
        ps.setInt(2, cost.cid);
        ps.setInt(3, cost.sid);
        ps.setBigDecimal(4, cost.cost);
        ps.setBigDecimal(5, cost.cost);
        ps.addBatch();
        count++;
        if (count % 1000 == 0)
        {
          ps.executeBatch();
          if (!this.con.getAutoCommit()) {
            this.con.commit();
          }
        }
      }
    }
    ps.executeBatch();
    ps.close();
  }
  
  public void updateMonthDetailAccumulative(Calendar month, Map<Integer, Collection<CostSum>> dailyAmounts, int mid, String cids)
    throws SQLException, BGException
  {
    StringBuilder sids = new StringBuilder();
    ServiceManager sm = new ServiceManager(this.con);
    List<Service> services = sm.list(mid);
    for (Service service : services)
    {
      sids.append(service.getId());
      sids.append(",");
    }
    if (sids.length() > 0) {
      sids.setLength(sids.length() - 1);
    }
    updateMonthDetailAccumulative(month, dailyAmounts, sids.toString(), cids);
  }
  
  public BigDecimal getDetailedAccount(int cid, String sids, Calendar day1, Calendar day2)
    throws BGException, SQLException
  {
    if ((TimeUtils.compare(day1, day2, 2) != 0) || (TimeUtils.compare(day1, day2, 1) != 0)) {
      throw new BGException("date1 � date2 ����� � ������ �������: date1=" + TimeUtils.formatDate(day1) + ", date2=" + TimeUtils.formatDate(day2));
    }
    BigDecimal result = BigDecimal.ZERO;
    String tableName = ServerUtils.getMonthTableName(this.tableNamePrefix, day1.getTime());
    if (ServerUtils.tableExists(this.con, tableName))
    {
      String query = "SELECT sum(summa) FROM " + tableName + " WHERE dd>=? and dd<=? and cid=?";
      if (Utils.notBlankString(sids)) {
        query = query + " and sid in (" + sids + ")";
      }
      PreparedStatement ps = this.con.prepareStatement(query);
      ps.setInt(1, day1.get(5));
      ps.setInt(2, day2.get(5));
      ps.setInt(3, cid);
      ResultSet rs = ps.executeQuery();
      if (rs.next()) {
        result = rs.getBigDecimal(1);
      }
      rs.close();
      ps.close();
    }
    return Utils.maskNull(result);
  }
  
  private void clearMonthDetail(String tableName, String sids, String cids)
    throws SQLException
  {
    String query = "DELETE FROM " + tableName + " WHERE sid in (" + sids + ") ";
    if (Utils.notEmptyString(cids)) {
      query = query + " AND cid in (" + cids + ")";
    }
    PreparedStatement ps = this.con.prepareStatement(query);
    ps.executeUpdate();
    ps.close();
  }
  
  private void createDetailTable(String tableName)
  {
    String createQuery = "CREATE TABLE `" + tableName + "` (`dd` int(5) unsigned NOT NULL, `cid` int(11) NOT NULL, `sid` int(11) NOT NULL, `summa` decimal(15,5) NOT NULL, PRIMARY KEY (`dd`,`cid`,`sid`), KEY `cid` (`cid`), KEY `dd` (`dd`) )";
    
    this.setup.checkAndCreatePeriodicTableName(this.con, tableName, createQuery);
  }
}