BiTel

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ru.ttk.billing.balance;

import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author lda
 */
public class BalanceULDayDetail {
    public double BalanceBegin = 0d;
    public double BalanceEnd = 0d;
    public HashMap<Integer,Double> HashAmountNpay = new HashMap<Integer, Double>();
    public double getAmountNpay(){
        double res = 0d;
        for (Map.Entry<Integer,Double> npay : HashAmountNpay.entrySet()) {
            res += npay.getValue();
        }
        return res;
    }
    public void addAmountNpay(int sid,double amount){
        Object amounts_object = HashAmountNpay.get(sid);
        double amounts = 0d;
        if (amounts_object != null){
            amounts = (Double)amounts_object + amount;
        }else{
            amounts = amount;
        }
        HashAmountNpay.put(sid, amounts);
    }
    public double AmountOther = 0d;
    public double Payments = 0d;
    public double Charges = 0d;
    public String calcMode = "";
    public BalanceULDayDetail(){
    }
}

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ru.ttk.billing.balance;

import bitel.billing.common.TimeUtils;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author lda
 */
public class BalanceULDetail {
    public int cid;
    //Дата начала детализации баланса
    public Calendar date1;
    //Дата окончания детализации баланса
    public Calendar date2;
    private HashMap<Integer,BalanceULDayDetail> BalanceHashMap = new HashMap<Integer, BalanceULDayDetail>();
    public BalanceULDetail(int cid, Calendar date1, Calendar date2){
        this.cid = cid;
        this.date1 = TimeUtils.clear_HOUR_MIN_MIL_SEC(date1);
        this.date2 = TimeUtils.clear_HOUR_MIN_MIL_SEC(date2);
    }
    public BalanceULDetail(int cid, Date date1, Date date2){
        this.cid = cid;
        this.date1 = TimeUtils.clear_HOUR_MIN_MIL_SEC(TimeUtils.convertDateToCalendar(date1));
        this.date2 = TimeUtils.clear_HOUR_MIN_MIL_SEC(TimeUtils.convertDateToCalendar(date2));
    }
    public void RecalcBalance(double BalanceBegin){
        double dd = Math.pow(10,2);
        int maxDays = TimeUtils.daysDelta(date1, date2);
        double MonBalanceBegin = BalanceBegin;
        HashMap<Integer,Double> MonAmountNpay = new HashMap<Integer, Double>();
        double MonAmountOther = 0d;
        double MonPayments = 0d;
        double MonCharges = 0d;
        double MonBalanceEndLast = BalanceBegin;

        Calendar MonDay;
        for (int i = 0;i<=maxDays;i++){
            BalanceULDayDetail bdd = BalanceHashMap.get(i);
            if (bdd == null){
                bdd = new BalanceULDayDetail();
                BalanceHashMap.put(i,bdd);
            }
            MonDay = (Calendar)date1.clone();
            MonDay.add(Calendar.DAY_OF_YEAR, i);
            if (MonDay.get(Calendar.DAY_OF_MONTH) == 1){
                MonAmountNpay = new HashMap<Integer, Double>();
                MonAmountOther = 0d;
                MonPayments = 0d;
                MonCharges = 0d;
                MonBalanceBegin = Math.round(MonBalanceEndLast * dd)/dd;
            }

            MonPayments += bdd.Payments;
            MonCharges += bdd.Charges;
            MonAmountOther += bdd.AmountOther;

            for (Map.Entry<Integer,Double> npay : bdd.HashAmountNpay.entrySet()) {
                int sid = npay.getKey();
                double Amount = npay.getValue();
                double MonAmount = 0d;
                Object MonAmountObject = MonAmountNpay.get(sid);
                if (MonAmountObject == null){
                    MonAmount = Amount;
                }else{
                    MonAmount = (Double)MonAmountObject + Amount;
                }
                MonAmountNpay.put(sid, MonAmount);
            }
            //Суммируем наработку за месяц по услугам
            double MonSumAmountNpay = 0d;
            for (Map.Entry<Integer,Double> npay : MonAmountNpay.entrySet()) {
                double Amount = npay.getValue();
                MonSumAmountNpay += Math.round(Amount * dd)/dd;
            }
            bdd.BalanceBegin = Math.round(MonBalanceEndLast * dd)/dd;
            MonBalanceEndLast = MonBalanceBegin - MonSumAmountNpay - Math.round(MonAmountOther * dd)/dd + MonPayments - MonCharges;
            bdd.BalanceEnd = Math.round(MonBalanceEndLast * dd)/dd;
        }
        MonAmountNpay.clear();
    }
    public void addAmountNpay(Calendar day,int sid,double amount){
        day = TimeUtils.clear_HOUR_MIN_MIL_SEC(day);
        BalanceULDayDetail bdd = BalanceHashMap.get(TimeUtils.daysDelta(date1, day));
        if (bdd != null){
            bdd.addAmountNpay(sid, amount);
        }else{
            bdd = new BalanceULDayDetail();
            bdd.addAmountNpay(sid, amount);
            BalanceHashMap.put(TimeUtils.daysDelta(date1, day), bdd);
        }
    }
    public void addAmountNpay(Date day,int sid,double amount){
        addAmountNpay(TimeUtils.convertDateToCalendar(day),sid,amount);
    }
    public void addAmountOther(Calendar day,double amount){
        day = TimeUtils.clear_HOUR_MIN_MIL_SEC(day);
        BalanceULDayDetail bdd = BalanceHashMap.get(TimeUtils.daysDelta(date1, day));
        if (bdd != null){
            bdd.AmountOther = bdd.AmountOther + amount;
        }else{
            bdd = new BalanceULDayDetail();
            bdd.AmountOther = amount;
            BalanceHashMap.put(TimeUtils.daysDelta(date1, day), bdd);
        }
    }
    public void addAmountOther(Date day,double amount){
        addAmountOther(TimeUtils.convertDateToCalendar(day),amount);
    }
    public void addPayment(Calendar day,double summa){
        day = TimeUtils.clear_HOUR_MIN_MIL_SEC(day);
        BalanceULDayDetail bdd = BalanceHashMap.get(TimeUtils.daysDelta(date1, day));
        if (bdd != null){
            bdd.Payments = bdd.Payments + summa;
        }else{
            bdd = new BalanceULDayDetail();
            bdd.Payments = summa;
            BalanceHashMap.put(TimeUtils.daysDelta(date1, day), bdd);
        }
    }
    public void addPayment(Date day,double summa){
        addPayment(TimeUtils.convertDateToCalendar(day),summa);
    }
    public void addCharge(Calendar day,double summa){
        day = TimeUtils.clear_HOUR_MIN_MIL_SEC(day);
        BalanceULDayDetail bdd = BalanceHashMap.get(TimeUtils.daysDelta(date1, day));
        if (bdd != null){
            bdd.Charges = bdd.Charges + summa;
        }else{
            bdd = new BalanceULDayDetail();
            bdd.Charges = summa;
            BalanceHashMap.put(TimeUtils.daysDelta(date1, day), bdd);
        }
    }
    public void addCharge(Date day,double summa){
        addCharge(TimeUtils.convertDateToCalendar(day),summa);
    }
    public BalanceULDayDetail getBalanceDetail(Calendar day){
        day = TimeUtils.clear_HOUR_MIN_MIL_SEC(day);
        return BalanceHashMap.get(TimeUtils.daysDelta(date1, day));
    }
    public BalanceULDayDetail getBalanceDetail(Date day){
        return getBalanceDetail(TimeUtils.convertDateToCalendar(day));
    }
    public BalanceULDayDetail getBalanceDetail(int delta){
        return BalanceHashMap.get(delta);
    }
    public void addcalcMode(Calendar day,String calcMode){
        day = TimeUtils.clear_HOUR_MIN_MIL_SEC(day);
        BalanceULDayDetail bdd = BalanceHashMap.get(TimeUtils.daysDelta(date1, day));
        if (bdd != null){
            bdd.calcMode = calcMode;
        }else{
            bdd = new BalanceULDayDetail();
            bdd.calcMode = calcMode;
            BalanceHashMap.put(TimeUtils.daysDelta(date1, day), bdd);
        }
    }

}

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.ttk.billing.npay;

import bitel.billing.common.TimeUtils;
import bitel.billing.server.npay.bean.ServiceObject;
import java.lang.ref.SoftReference;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.log4j.Logger;
import ru.bitel.bgbilling.common.BGException;
import ru.bitel.bgbilling.kernel.tariff.tree.server.TariffModuleTree;
import ru.bitel.bgbilling.kernel.tariff.tree.server.TariffModuleTreeSet;
import ru.bitel.bgbilling.kernel.tariff.tree.server.TariffModuleTreeSetDao;
import ru.bitel.bgbilling.modules.npay.tariff.server.CalculateModeTariffTreeNode;
import ru.bitel.bgbilling.modules.npay.tariff.server.NPayTariffContext;
import ru.bitel.bgbilling.modules.npay.tariff.server.NPayTariffRequest;
import ru.bitel.bgbilling.server.util.Setup;
import ru.bitel.common.Preferences;
import ru.bitel.common.Utils;
import ru.bitel.common.model.Period;
import ru.bitel.common.sql.ConnectionSet;
import ru.ttk.billing.TTKConst;
import ru.ttk.billing.balance.BalanceULDetail;

/**
 *
 * @author lda
 */
public class CalculatorUL{
    private Calendar startMonth;
    private Calendar endMonth;
    private Calendar startCalc;
    private Calendar endCalc;
    private int daysInMonth;
    private Calendar StartActiveStatus = null;
    private Setup setup;
    private ConnectionSet connectionSet;
    private Connection con;
    private Connection conSlave;
    private String query_get_abn;
    private int mid = TTKConst.MID_NPAY;
    private String cids = "";
    private NPayTariffContext context;
    private static final Logger log = Logger.getLogger( CalculatorUL.class );
    private HashMap<Integer,BalanceULDetail> HashMapBalance = new HashMap<Integer, BalanceULDetail>();
   
    public CalculatorUL(Setup setup, ConnectionSet connectionSet) {
        this.setup = setup;
        this.connectionSet = connectionSet;
        this.con = connectionSet.getConnection();
        this.conSlave = connectionSet.getSlaveConnection();
    }
    public void setStartActiveStatus(Calendar StartActiveStatus) {
        this.StartActiveStatus = StartActiveStatus;
    }       
    public BalanceULDetail FillAmounts (int cid, Calendar date1, Calendar date2){
        cids = String.valueOf(cid);
        startCalc = TimeUtils.getStartMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date1) );
        endCalc = TimeUtils.getEndMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date2) );
        query_get_abn = "SELECT cid, sid, date1, date2, col, emid, eid " +
            "FROM npay_service_object_" + mid + " " +
            "WHERE (date1 IS NULL OR date1<=?) AND (date2 IS NULL OR date2>=?) ";
        query_get_abn += " AND cid IN (" + cid + ")";
        query_get_abn += " ORDER BY cid, sid";

        startMonth = (Calendar)startCalc.clone();
        while(TimeUtils.dateBeforeOrEq(startMonth, endCalc)){
            endMonth = TimeUtils.getEndMonth((Calendar)startMonth.clone());
            daysInMonth = startMonth.getActualMaximum( Calendar.DAY_OF_MONTH );
           
            getAmounts();
           
            startMonth.set(Calendar.DAY_OF_MONTH, 1);
            startMonth.add(Calendar.MONTH, 1);                   
        }
        return HashMapBalance.get(cid);
    }
    public HashMap<Integer, BalanceULDetail> FillAmountsForDailyCheckTask(String cids, Calendar date1, Calendar date2){
        this.cids = cids;
        startCalc = TimeUtils.getStartMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date1) );
        endCalc = TimeUtils.getEndMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date2) );
       
        query_get_abn = "SELECT npay_service.cid, npay_service.sid, npay_service.date1, npay_service.date2, npay_service.col, npay_service.emid, npay_service.eid " +
            "FROM npay_service_object_" + mid + " AS npay_service" +
            " INNER JOIN contract_script " +
            " ON (npay_service.cid=contract_script.cid AND contract_script.script_id='1') " +
            " INNER JOIN contract "+
            " ON (npay_service.cid=contract.id AND contract.status='0') "+
            "WHERE (npay_service.date1 IS NULL OR npay_service.date1<=?) AND (npay_service.date2 IS NULL OR npay_service.date2>=?) ";
        if( Utils.notBlankString( cids ) ){
            query_get_abn += " AND npay_service.cid IN (" + cids + ")";
        }
        query_get_abn += " ORDER BY npay_service.cid, npay_service.sid";

        startMonth = (Calendar)startCalc.clone();
        while(TimeUtils.dateBeforeOrEq(startMonth, endCalc)){
            endMonth = TimeUtils.getEndMonth((Calendar)startMonth.clone());
            daysInMonth = startMonth.getActualMaximum( Calendar.DAY_OF_MONTH );
           
            getAmounts();
           
            startMonth.set(Calendar.DAY_OF_MONTH, 1);
            startMonth.add(Calendar.MONTH, 1);                   
        }       
        return HashMapBalance;
    }
    private void getAmounts(){
        try {       
            TariffModuleTreeSetDao tariffModuleTreeSetDao = new TariffModuleTreeSetDao( con );
            Preferences moduleSetup = setup.getModuleSetup( mid );
           
            context  = new NPayTariffContext( moduleSetup, con, mid, startMonth, endMonth, endMonth, cids, null );
            if (StartActiveStatus != null){
                context.setPrecalcOpenPeriod( new Period( TimeUtils.convertCalendarToDate( StartActiveStatus ), null ) );
            }
           
            PreparedStatement ps = conSlave.prepareStatement( query_get_abn );
            ps.setDate( 1, TimeUtils.convertCalendarToSqlDate( endMonth ) );
            ps.setDate( 2, TimeUtils.convertCalendarToSqlDate( startMonth ) );
            ResultSet rs = ps.executeQuery();
            while( rs.next() ){
                ServiceObject serviceObject = new ServiceObject();
                serviceObject.setContractId( rs.getInt( 1 ) );
                serviceObject.setServiceId( rs.getInt( 2 ) );
                serviceObject.setDate1( rs.getDate( 3 ) );
                serviceObject.setDate2( rs.getDate( 4 ) );
                serviceObject.setCol( rs.getInt( 5 ) );
                serviceObject.setEntityMid( rs.getInt( 6 ) );
                serviceObject.setEntityId( rs.getInt( 7 ) );
               
                processServiceObject(serviceObject, tariffModuleTreeSetDao );
            }
            ps.close();
            tariffModuleTreeSetDao.freeResources();
        }catch( Exception e ){
            e.printStackTrace();
        }
    }
    private void processServiceObject(ServiceObject serviceObject, TariffModuleTreeSetDao tariffModuleTreeSetDao ){
        final int cid = serviceObject.getContractId();
        BalanceULDetail bd = HashMapBalance.get(cid);
        if (bd == null){
            bd = new BalanceULDetail(cid, startCalc, endCalc);
            HashMapBalance.put(cid, bd);
        }
        final int sid = serviceObject.getServiceId();
        try{
            final Date serviceStartDate = serviceObject.getDate1();
            final Date serviceEndDate = serviceObject.getDate2();
            final int emid = serviceObject.getEntityMid();
            final int eid = serviceObject.getEntityId();

            final Calendar serviceStart = TimeUtils.convertDateToCalendar( serviceObject.getDate1() );
            final Calendar serviceEnd = TimeUtils.convertDateToCalendar( serviceObject.getDate2() );
            int col = serviceObject.getCol();

            final Calendar serviceDate1 = TimeUtils.convertDateToCalendar( trimByMonthStart( serviceStartDate ) );
            final Calendar serviceDate2 = TimeUtils.convertDateToCalendar( trimByMonthEnd( serviceEndDate ) );
           
            TariffModuleTreeSet tts = getTreeSet( cid, emid, eid, tariffModuleTreeSetDao);
            for( TariffModuleTreeSet.Entry tariffPeriod : tts.entries() ){
                Calendar tariffFrom = tariffPeriod.getCalendarFrom();
                Calendar tariffTo = tariffPeriod.getCalendarTo();

//                log.info("tariffFrom="+TimeUtils.convertCalendarToDateString(tariffFrom));
//                log.info("tariffTo="+TimeUtils.convertCalendarToDateString(tariffTo));
               
                Calendar date1 = (Calendar)serviceDate1.clone();
                Calendar date2 = (Calendar)serviceDate2.clone();

                // если тариф начинает действовать после окончания периода услуги
                if( tariffFrom != null && TimeUtils.dateBefore( date2, tariffFrom ) ){
                    continue;
                }

                // тариф начал работать позже начала периода услуги
                if( tariffFrom != null && TimeUtils.dateBefore( date1, tariffFrom ) ){
                    date1 = tariffFrom;
                }

                // тариф кончил работать раньше окончания периода услаги
                if( tariffTo != null && TimeUtils.dateBefore( tariffTo, date2 ) ){
                    if( TimeUtils.dateBeforeOrEq( date1, tariffTo ) ){
                        date2 = tariffTo;
                    }else{
                        continue;
                    }
                }

                NPayTariffRequest req = new NPayTariffRequest( mid, cid, sid );
                req.setInfoEnable( true );
                req.setConnectionSet( connectionSet );
                req.setAccountingMonthDays( daysInMonth );
                req.setTime( date1 );
               
                req.setServiceCost( sid, Collections.singletonList( new NPayTariffRequest.NPayServiceCost( sid, serviceStart, serviceEnd, date1, date2, col ) ) );

                final TariffModuleTree tree = tariffPeriod.getTree();
               
                tree.execute( req, context );
               
                if( req.isAccepted() ){
                    for( NPayTariffRequest.NPayServiceCost serviceCost : req.getServiceCostList() ){
                        if (req.getCalculateModeLog() == CalculateModeTariffTreeNode.MODE_DAY){
                            for (Map.Entry<Integer, BigDecimal> detail : req.getDayDetailLog().entrySet()) {
                                Calendar day = (Calendar)serviceCost.date1.clone();
                                day.set(Calendar.DAY_OF_MONTH, detail.getKey());
                                HashMapBalance.get(cid).addAmountNpay(day, sid, detail.getValue().doubleValue());
                            }
                        }else if (req.getCalculateModeLog() == CalculateModeTariffTreeNode.MODE_MONTH){
                            HashMapBalance.get(cid).addAmountNpay(startMonth, sid, serviceCost.cost.doubleValue());
                        }
                    }
                }
               
            }
        }catch( Exception e ){
            e.printStackTrace();
        }
    }
    private Date trimByMonthEnd( Date date){
        if( date == null || TimeUtils.dateBefore( endMonth.getTime(), date ) ){
            date = ((Calendar)endMonth.clone()).getTime();
        }
        return date;
    }

    private Date trimByMonthStart( Date date1){
        if( date1 == null || TimeUtils.dateBefore( date1, startMonth.getTime() ) ){
            date1 = ((Calendar)startMonth.clone()).getTime();
        }
        return date1;
    }
    private Map<Integer, SoftReference<Map<Integer, Map<Integer, TariffModuleTreeSet>>>> treeSetMap = new HashMap<Integer, SoftReference<Map<Integer, Map<Integer, TariffModuleTreeSet>>>>( 1024 );
    private TariffModuleTreeSet getTreeSet( int cid, int emid, int eid, TariffModuleTreeSetDao tariffModuleTreeSetDao)
            throws BGException{
        SoftReference<Map<Integer, Map<Integer, TariffModuleTreeSet>>> emidMapRef = treeSetMap.get( cid );
        Map<Integer, Map<Integer, TariffModuleTreeSet>> emidMap = emidMapRef != null ? emidMapRef.get() : null;
        if( emidMap == null ){
            emidMap = new HashMap<Integer, Map<Integer, TariffModuleTreeSet>>( 8 );
            treeSetMap.put( cid, new SoftReference<Map<Integer, Map<Integer, TariffModuleTreeSet>>>( emidMap ) );
        }

        Map<Integer, TariffModuleTreeSet> eidMap = emidMap.get( emid );
        if( eidMap == null ){
            emidMap.put( emid, eidMap = new HashMap<Integer, TariffModuleTreeSet>( 8 ) );
        }

        TariffModuleTreeSet result = eidMap.get( eid );
        if( result == null ){
            result = tariffModuleTreeSetDao.getRealtimeTariffTreeSet( cid, startMonth.getTime(), "npay", mid, emid, eid );
        }

        return result;
       
    }
   
}

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ru.ttk.billing.balance;

import bitel.billing.common.TimeUtils;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import ru.bitel.bgbilling.kernel.container.managed.ServerContext;
import ru.bitel.bgbilling.server.util.Setup;
import ru.bitel.common.sql.ConnectionSet;
import ru.bitel.common.worker.ThreadContext;
import ru.ttk.billing.npay.CalculatorUL;

/**
 *
 * @author lda
 */
public class BalanceULDetailManager {
    private Setup setup;
    private ConnectionSet connectionSet;
    public Connection con;
    public Connection conSlave;
    private Calendar StartActiveStatus = null;
    public BalanceULDetailManager(Setup setup, ConnectionSet connectionSet){
        this.setup = setup;
        this.connectionSet = connectionSet;
        this.con = connectionSet.getConnection();
        this.conSlave = connectionSet.getSlaveConnection();
    }
    public BalanceULDetailManager(){
    }
    public void setStartActiveStatus(Calendar StartActiveStatus) {
        this.StartActiveStatus = StartActiveStatus;
    }   
    public void setStartActiveStatus(GregorianCalendar StartActiveStatus) {
        this.StartActiveStatus = (Calendar) StartActiveStatus;
    }   
    public BalanceULDetail getDetailForCid(int cid, Calendar date1, Calendar date2){
        boolean createConnectionSet = false;
        if (connectionSet == null){
            setup = Setup.getSetup();
            ServerContext threadContext = ThreadContext.get();
            if( threadContext != null){
                connectionSet = threadContext.getConnectionSet();
            }else{
                connectionSet = ConnectionSet.newInstance( setup, true );
            }
            createConnectionSet = true;
            this.con = connectionSet.getConnection();
            this.conSlave = connectionSet.getSlaveConnection();
        }
        date1 = TimeUtils.getStartMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date1) );
        date2 = TimeUtils.getEndMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date2) );
       
        Calendar StartDate = Calendar.getInstance();
        StartDate = TimeUtils.clear_HOUR_MIN_MIL_SEC(StartDate);
        StartDate.set(Calendar.DAY_OF_MONTH, 1);
        if (TimeUtils.dateBefore(StartDate, date1)){
            date1 = StartDate;
        }
        CalculatorUL calc = new CalculatorUL(setup, connectionSet);
        calc.setStartActiveStatus(StartActiveStatus);
        BalanceULDetail bd = calc.FillAmounts(cid, date1, date2);
        if (bd == null){
            bd = new BalanceULDetail(cid, date1, date2);
        }
        FillPaymentsForCid(bd);
        FillChargesForCid(bd);
        FillAmountOtherForCid(bd);
        bd.RecalcBalance(getStartBalanceForCid(bd));
        if( createConnectionSet ){
            connectionSet.recycle();
        }       
        return bd;
    }
    public HashMap<Integer,BalanceULDetail> getDetailForDailyCheckTask(String cids, Calendar date1, Calendar date2){
        date1 = TimeUtils.getStartMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date1) );
        date2 = TimeUtils.getEndMonth( TimeUtils.clear_HOUR_MIN_MIL_SEC(date2) );
       
        CalculatorUL calc = new CalculatorUL(setup, connectionSet);
        HashMap<Integer,BalanceULDetail> HashMapBalance = calc.FillAmountsForDailyCheckTask(cids,date1, date2);
        FillPayments(HashMapBalance, date1, date2);
        FillCharges(HashMapBalance, date1, date2);
        FillAmountOther(HashMapBalance, date1, date2);

        for ( Map.Entry<Integer,BalanceULDetail> entry : HashMapBalance.entrySet()){
            double StartBalance = getStartBalanceForCid(entry.getValue());
            entry.getValue().RecalcBalance(StartBalance);
        }
        return HashMapBalance;
    }

    private void FillPaymentsForCid(BalanceULDetail bd){
        try {
            String query = "SELECT dt,sum(summa) FROM contract_payment WHERE cid=? AND dt>=? AND dt<=? GROUP BY 1";
            PreparedStatement ps = conSlave.prepareStatement(query);
            ps.setInt( 1, bd.cid );
            ps.setDate( 2, TimeUtils.convertCalendarToSqlDate( bd.date1 ) );
            ps.setDate( 3, TimeUtils.convertCalendarToSqlDate( bd.date2 ) );
            ResultSet rs = ps.executeQuery();
            while( rs.next() )
            {
                bd.addPayment(TimeUtils.convertSqlDateToCalendar(rs.getDate(1)), rs.getDouble(2));
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(BalanceULDetailManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    private void FillPayments(HashMap<Integer,BalanceULDetail> HashMapBalance, Calendar date1, Calendar date2){
        try {
            String query = "SELECT cid, dt, sum(summa) "
                    + "FROM contract_payment "
                    + "WHERE dt>=? AND dt<=? "
                    + "GROUP BY 1,2";
            PreparedStatement ps = conSlave.prepareStatement(query);
            ps.setDate( 1, TimeUtils.convertCalendarToSqlDate( date1 ) );
            ps.setDate( 2, TimeUtils.convertCalendarToSqlDate( date2 ) );
            ResultSet rs = ps.executeQuery();
            while( rs.next() )
            {
                int cid = rs.getInt(1);
                Date SQLDate = rs.getDate(2);
                double summa = rs.getDouble(3);
                BalanceULDetail bd = HashMapBalance.get(cid);
                if (bd != null){
                    bd.addPayment(TimeUtils.convertSqlDateToCalendar(SQLDate), summa);
                }
            }
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private void FillChargesForCid(BalanceULDetail bd){
        try {
            String query = "SELECT dt,sum(summa) FROM contract_charge WHERE cid=? AND dt>=? AND dt<=? GROUP BY 1";
            PreparedStatement ps = conSlave.prepareStatement(query);
            ps.setInt( 1, bd.cid );
            ps.setDate( 2, TimeUtils.convertCalendarToSqlDate( bd.date1 ) );
            ps.setDate( 3, TimeUtils.convertCalendarToSqlDate( bd.date2 ) );
            ResultSet rs = ps.executeQuery();
            while( rs.next() )
            {
                bd.addCharge(TimeUtils.convertSqlDateToCalendar(rs.getDate(1)), rs.getDouble(2));
            }
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private void FillCharges(HashMap<Integer,BalanceULDetail> HashMapBalance, Calendar date1, Calendar date2){
        try {
            String query = "SELECT cid, dt, sum(summa) "
                    + "FROM contract_charge "
                    + "WHERE dt>=? AND dt<=? GROUP BY 1,2";
            PreparedStatement ps = conSlave.prepareStatement(query);
            ps.setDate( 1, TimeUtils.convertCalendarToSqlDate( date1 ) );
            ps.setDate( 2, TimeUtils.convertCalendarToSqlDate( date2 ) );
            ResultSet rs = ps.executeQuery();
            while( rs.next() )
            {
                int cid = rs.getInt(1);
                Date SQLDate = rs.getDate(2);
                double summa = rs.getDouble(3);
                BalanceULDetail bd = HashMapBalance.get(cid);
                if (bd != null){
                    bd.addCharge(TimeUtils.convertSqlDateToCalendar(SQLDate), summa);
                }
            }
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private void FillAmountOtherForCid(BalanceULDetail bd){
        Calendar StartPerion = (Calendar) bd.date1.clone();
        StartPerion.set(Calendar.DAY_OF_MONTH, 1);
        while(TimeUtils.dateBeforeOrEq(StartPerion, bd.date2)){
            try {
                String query = "SELECT sum(contract_account.summa) FROM contract_account "
                        + "INNER JOIN service "
                        + "ON (contract_account.sid=service.id AND service.mid!='7') "
                        + "WHERE contract_account.cid=? AND contract_account.yy=? AND contract_account.mm=?";
                PreparedStatement ps = conSlave.prepareStatement(query);
                ps.setInt( 1, bd.cid );
                ps.setInt( 2, StartPerion.get(Calendar.YEAR) );
                ps.setInt( 3, StartPerion.get(Calendar.MONTH) + 1);
                ResultSet rs = ps.executeQuery();
                while( rs.next() )
                {
                    bd.addAmountOther(StartPerion, rs.getDouble(1));
                }
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            StartPerion.set(Calendar.DAY_OF_MONTH, 1);
            StartPerion.add(Calendar.MONTH, 1);
        }
    }
    private void FillAmountOther(HashMap<Integer,BalanceULDetail> HashMapBalance, Calendar date1, Calendar date2){
        Calendar StartPerion = (Calendar) date1.clone();
        StartPerion.set(Calendar.DAY_OF_MONTH, 1);
        while(TimeUtils.dateBeforeOrEq(StartPerion, date2)){
            try {
                String query = "SELECT contract_account.cid, sum(contract_account.summa) "
                        + "FROM contract_account "
                        + "INNER JOIN service "
                        + "ON (contract_account.sid=service.id AND service.mid!='7') "
                        + "WHERE contract_account.yy=? AND contract_account.mm=? GROUP BY 1";
                PreparedStatement ps = conSlave.prepareStatement(query);
                ps.setInt( 1, StartPerion.get(Calendar.YEAR) );
                ps.setInt( 2, StartPerion.get(Calendar.MONTH) + 1);
                ResultSet rs = ps.executeQuery();
                while( rs.next() )
                {
                    int cid = rs.getInt(1);
                    double summa = rs.getDouble(2);
                    BalanceULDetail bd = HashMapBalance.get(cid);
                    if (bd != null){
                        bd.addAmountOther(StartPerion, summa);
                    }
                }
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            StartPerion.set(Calendar.DAY_OF_MONTH, 1);
            StartPerion.add(Calendar.MONTH, 1);
        }
    }
    private double getStartBalanceForCid(BalanceULDetail bd){
        double StartBalance = 0d;
        try {
            String query = "SELECT summa1 FROM contract_balance WHERE cid=? AND yy=? AND mm=?";
            PreparedStatement ps = conSlave.prepareStatement(query);
            ps.setInt( 1, bd.cid );
            ps.setInt( 2, bd.date1.get(Calendar.YEAR) );
            ps.setInt( 3, bd.date1.get(Calendar.MONTH) + 1);
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
                StartBalance = rs.getDouble(1);
                ps.close();
            }else{
                ps.close();
                query = "SELECT summa1+summa2-summa3-summa4 FROM contract_balance WHERE cid=? AND ((yy*12) + mm)<=((?*12) + ?)" +
                " ORDER BY yy DESC, mm DESC LIMIT 1";
                ps = conSlave.prepareStatement(query);
                ps.setInt( 1, bd.cid );
                ps.setInt( 2, bd.date1.get(Calendar.YEAR) );
                ps.setInt( 3, bd.date1.get(Calendar.MONTH) + 1);
                rs = ps.executeQuery();
                if (rs.next()){
                    StartBalance = rs.getDouble(1);
                }
                ps.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return StartBalance;
    }
}

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.ttk.billing.event.GlobalScript;

import bitel.billing.common.TimeUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.HashMap;
import ru.bitel.bgbilling.kernel.script.server.dev.GlobalScriptBase;
import ru.bitel.bgbilling.server.util.Setup;
import ru.bitel.common.sql.ConnectionSet;
import ru.ttk.billing.balance.BalanceULDetail;
import ru.ttk.billing.balance.BalanceULDetailManager;

/**
 *
 * @author lda
 */
public class createSMS extends GlobalScriptBase{
    private Setup setup;
    private ConnectionSet connectionSet;
    private Connection con;
   
    @Override
    public void execute( Setup setup, ConnectionSet connectionSet )
            throws Exception{
        this.setup = setup;
        this.connectionSet = connectionSet;
        this.con = connectionSet.getConnection();
        Calendar dt = TimeUtils.clear_HOUR_MIN_MIL_SEC(Calendar.getInstance());
   Calendar StartDate = (Calendar)dt.clone();
   Calendar BlockedDate = (Calendar)dt.clone();
   Calendar ActiveDate = (Calendar)dt.clone();
   ActiveDate.add(Calendar.DAY_OF_YEAR, 6);
   BlockedDate.add(Calendar.DAY_OF_YEAR, 7);
        BalanceULDetailManager BDM = new BalanceULDetailManager(setup, connectionSet);
        HashMap<Integer,BalanceULDetail> HashMapBalance = BDM.getDetailForDailyCheckTask("",StartDate, BlockedDate);
       
        try {
            String query = "SELECT phone.cid, phone.val, contract.gr, account.val, contract.pfid "+
                    "FROM contract_script "+
                    "  INNER JOIN contract_parameter_type_1 phone "+
                    "  ON (contract_script.cid=phone.cid AND phone.pid='121') "+
                    "  INNER JOIN contract_parameter_type_1 account "+
                    "  ON (contract_script.cid=account.cid AND account.pid='137') "+
                    "  INNER JOIN contract "+
                    "  ON (contract_script.cid=contract.id) "+
                    "WHERE contract_script.script_id='1' AND contract_script.date2 is NULL "+
                    "AND contract.status='0'";
            PreparedStatement ps = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
      int cid = rs.getInt(1);
                BalanceULDetail bd = HashMapBalance.get(cid);
                if (bd != null){
                    if (bd.getBalanceDetail(BlockedDate).BalanceEnd < 0 && bd.getBalanceDetail(ActiveDate).BalanceEnd >= 0){
                        //Этот абонент будет заблокирован через 7 дней
                    }
                }
            }
            ps.close();
        }catch( Exception e ){
            e.printStackTrace();
        }

       
    }   
}

import java.util.*;
import java.text.*;
import java.math.*;
import java.util.regex.*;
import bitel.billing.common.*;
import ru.ttk.billing.balance.*;
public void fillReport( con, filter, bitel.billing.server.reports.BGCSVReport.ReportResult result )
{
    data = new ArrayList( 100000 );
    cids = filter.getStringParam( "contracts" );
    if (cids == null || cids.equals("") || !cids.matches("\\d+")){
        return;
    };
    cid = Integer.parseInt(cids);
    active_date = TimeUtils.convertDateToCalendar(filter.getDateParam( "active_date" ));
    date1 = filter.getCalendarParam( "date1" );
    date2 = filter.getCalendarParam( "date2" );
    if (date1 == null || date2 == null){
        return;
    };
    Calendar day = (Calendar) date1.clone();
    bdm = new BalanceULDetailManager();
    bdm.setStartActiveStatus(active_date);
    bd = bdm.getDetailForCid(cid,date1,date2);
    while(TimeUtils.dateBeforeOrEq( day, date2 )){
        dd = Math.pow(10,2);
        bdd = bd.getBalanceDetail(day);
        map = new HashMap();
        map.put( "day",TimeUtils.convertCalendarToDateString(day) );
        if (bdd != null){
            map.put( "begin",(Math.round(bdd.BalanceBegin * dd)/dd).toString() );
            map.put( "payment",(Math.round(bdd.Payments * dd)/dd).toString() );
            map.put( "npay",(Math.round(bdd.getAmountNpay() * dd)/dd).toString() );
            map.put( "other",(Math.round(bdd.AmountOther * dd)/dd).toString() );
            map.put( "сharge",(Math.round(bdd.Charges * dd)/dd).toString() );
            map.put( "end",(Math.round(bdd.BalanceEnd * dd)/dd).toString() );
        };
        data.add( map );
        day.add(Calendar.DAY_OF_YEAR, 1);
    }

  result.setData( data );
};

<?xml version="1.0" encoding="windows-1251"?>
<report title="Детализация баланса" type="java">
    <contracts name="contracts"/>
    <period name1="date1" name2="date2"/>
    <date title="Дата активации" name="active_date"/>
    <fields>
        <item id="day" title="День"/>
        <item id="begin" title="На начало"/>
        <item id="payment" title="Платежи"/>
        <item id="npay" title="Абонплата"/>
        <item id="other" title="Другие наработки"/>
        <item id="сharge" title="Расход"/>
        <item id="end" title="На конец"/>
    </fields>
</report>