BiTel

SELECT cdr_id, COUNT(id)
FROM log_session_10_201509
GROUP BY cdr_id
HAVING COUNT(id) >1

package ru.skyb.reports;

import ru.bitel.common.Utils;
import ru.bitel.common.model.Period;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Calendar;

import bitel.billing.common.TimeUtils;
import ru.bitel.bgbilling.common.BGException;
import ru.bitel.bgbilling.kernel.tariff.common.bean.TariffPlan;
import bitel.billing.server.admin.reports.BGReportFilter;
import bitel.billing.server.contract.bean.PaymentManager;
import bitel.billing.server.npay.bean.ServiceObject;
import bitel.billing.server.npay.bean.ServiceObjectManager;
import bitel.billing.server.phone.bean.ClientItem;
import bitel.billing.server.phone.bean.ClientItemManager;
import bitel.billing.server.reports.BGCSVReport.ReportResult;
import bitel.billing.server.contract.bean.ContractTariffManager;

public class CustomClientsMarj implements
      bitel.billing.server.reports.BGCSVReport.CSVFillerData {
   @Override
   public void fillReport(Connection con, BGReportFilter filter,
         ReportResult result) throws SQLException, BGException {
      int j = 0;
      int NPAY_MID = 9;
      StringBuilder sb = new StringBuilder ();
      ServiceObjectManager som = new ServiceObjectManager( con, NPAY_MID ) ;
      ServiceObject srv = new ServiceObject();
      BigDecimal allCost = BigDecimal.ZERO;
      BigDecimal allCount = BigDecimal.ZERO;
      BigDecimal allCount_oper = BigDecimal.ZERO;
      ClientItemManager cim = new ClientItemManager(con, 12);
      String text = filter.getStringParam("text");
      int combo = filter.getIntParam("combo");
      Calendar month = filter.getCalendarParam( "month" );
      String yyyy = TimeUtils.format ( month, "yyyy" );
      String mm = TimeUtils.format ( month, "MM" );
      String yyyyMM = yyyy+mm;
      int time = 0;
      java.util.Date date1 = filter.getDateParam("date1");
      java.util.Date date2 = filter.getDateParam("date2");
      String groups = "3"; // коды групп договоров через запятую
      long groupMask = Utils.enumToMask(groups);
      List<Map<String, String>> data = new ArrayList<Map<String, String>>(1000);
      Map<String, String> map = new HashMap<String, String>();
      if (!text.equals("") && !text.isEmpty())
         {
            String query = " SELECT c.id, c.title, c.comment, c.date1, ls.cost, ls.cdr_id, ls.session_time, ls.session_start FROM contract AS c "
                  + " LEFT JOIN log_session_12_"+yyyyMM+" AS ls ON ls.cid = c.id"
                  + " WHERE gr&(" + groupMask + ")>0 AND c.id IN (1758,1846) AND ls.session_time > 2 "
                  //+ " WHERE gr&(" + groupMask + ")>0 AND ls.session_time > 2 "
                  + " AND scid = -1 AND title REGEXP '"+text+"'";
            sb.append(query);
         }
      else
         {
            String query = " SELECT c.id, c.title, c.comment, c.date1, ls.cost, ls.cdr_id, ls.session_time, ls.session_start FROM contract AS c "
                  + " LEFT JOIN log_session_12_"+yyyyMM+" AS ls ON ls.cid = c.id"
                  + " WHERE gr&("+ groupMask + ")>0 AND ls.session_time > 2 AND c.id IN (1758,1846) AND scid = -1 ORDER BY cdr_id";
                  //+ " WHERE gr&("+ groupMask + ")>0 AND ls.session_time > 2 AND scid = -1 ORDER BY cdr_id";
            sb.append(query);
         }
      PreparedStatement ps = con.prepareStatement(sb.toString());
      ResultSet rs = ps.executeQuery();
      while (rs.next())
         {
            Map<String, String> NewMap = new HashMap<String, String>();
            //System.out.println("query " + sb.toString());
            j++;
            //BigDecimal cost_oper = BigDecimal.ZERO;
            BigDecimal cost = BigDecimal.ZERO;
            BigDecimal cost_oper = BigDecimal.ZERO;
            int cid = rs.getInt(1);
            String title = rs.getString(2);
            String comment = rs.getString(3);
            Date dt = rs.getDate(4);
            int cdr_id = rs.getInt(6);
            //BigDecimal cost = rs.getBigDecimal(5);
            time = time + rs.getInt(7);
            NewMap.put( "cid", String.valueOf( cid ) );
            NewMap.put("title", title);
            NewMap.put("comment", comment);
            //map.put("time", String.valueOf(rs.getInt(7)) + " время сессии " + rs.getString(8));
            //map.put("count", String.valueOf(j));
            if (cid != 588)
            {
            System.out.println("cdr_id " + cdr_id + " cdr_id_oper " + cdr_id);
            }
            
            String query = "SELECT cost, session_start FROM log_session_12_"+yyyyMM+" AS ls "
                  + "WHERE cid = 588 AND cdr_id = " + cdr_id;
            PreparedStatement psq = con.prepareStatement(query);
            ResultSet rsq = psq.executeQuery();
               while (rsq.next())
               {

                  cost_oper = rsq.getBigDecimal(1);
                  System.out.println("cost  " + rs.getBigDecimal(5).toString() + " cost_oper " + cost_oper + " cdr_id " + cdr_id + "\n\n");
                  NewMap.put("count", rs.getBigDecimal(5).toString() );
                  NewMap.put("count_oper", cost_oper.toString());
                  NewMap.put("time", rs.getString(8));
                  cost = rs.getBigDecimal(5).subtract(cost_oper);
               }
            NewMap.put("cost", cost.toString());
            allCost = allCost.add(cost);   
            allCount = allCount.add(rs.getBigDecimal(5));
            allCount_oper = allCount_oper.add(cost_oper);
            
            Map<String, String> NewMap2 = new HashMap<String, String>();
            NewMap2.put("count", allCount.toString());
            NewMap2.put("count_oper", allCount_oper.toString());
            data.add(NewMap2);
            
            data.add(NewMap);   
         }
      int Time = time/60;

      //Map<String, String> map = new HashMap<String, String>();
      map.put("cost", allCost.toString());
      map.put("count", allCount.toString());
      map.put("count_oper", allCount_oper.toString());
      map.put("time", String.valueOf(Time) + " минут");
      //map.put("cost", allCost.toString());
      data.add(map);      
      result.setData(data);
   }

}

      ...
      [b]String query = "SELECT cost, session_start FROM log_session_12_"+yyyyMM+" AS ls WHERE cid = 588 AND cdr_id = ?;";[/b]
      [b]PreparedStatement psq = con.prepareStatement(query);[/b]

      PreparedStatement ps = con.prepareStatement(sb.toString());
      ResultSet rs = ps.executeQuery();
      while (rs.next())
         {
            [b]psq.setInt(1, cdr_id);[/b]
            ResultSet rsq = psq.executeQuery();
               while (rsq.next())
               {

                  cost_oper = rsq.getBigDecimal(1);
                  System.out.println("cost  " + rs.getBigDecimal(5).toString() + " cost_oper " + cost_oper + " cdr_id " + cdr_id + "\n\n");
                  NewMap.put("count", rs.getBigDecimal(5).toString() );
                  NewMap.put("count_oper", cost_oper.toString());
                  NewMap.put("time", rs.getString(8));
                  cost = rs.getBigDecimal(5).subtract(cost_oper);
               }
            NewMap.put("cost", cost.toString());
            allCost = allCost.add(cost);   
            allCount = allCount.add(rs.getBigDecimal(5));
            allCount_oper = allCount_oper.add(cost_oper);
           
            Map<String, String> NewMap2 = new HashMap<String, String>();
            NewMap2.put("count", allCount.toString());
            NewMap2.put("count_oper", allCount_oper.toString());
            data.add(NewMap2);
           
            data.add(NewMap);   
         }
      int Time = time/60;

      //Map<String, String> map = new HashMap<String, String>();
      map.put("cost", allCost.toString());
      map.put("count", allCount.toString());
      map.put("count_oper", allCount_oper.toString());
      map.put("time", String.valueOf(Time) + " минут");
      //map.put("cost", allCost.toString());
      data.add(map);     
      result.setData(data);
   }

show index from log_session_10_201509;
+-----------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                 | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| log_session_10_201509 |          0 | PRIMARY       |            1 | id            | A         |      330444 |     NULL | NULL   |      | BTREE      |         |               |
| log_session_10_201509 |          1 | session_start |            1 | session_start | A         |      330444 |     NULL | NULL   |      | BTREE      |         |               |
| log_session_10_201509 |          1 | log           |            1 | source_id     | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| log_session_10_201509 |          1 | log           |            2 | hour          | A         |         299 |     NULL | NULL   |      | BTREE      |         |               |
| log_session_10_201509 |          1 | cid           |            1 | cid           | A         |        6234 |     NULL | NULL   |      | BTREE      |         |               |
| log_session_10_201509 |          1 | sid           |            1 | sid           | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| log_session_10_201509 |          1 | item_id       |            1 | item_id       | A         |        9179 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

package ru.skyb.reports;

import ru.bitel.common.Utils;
import ru.bitel.common.model.Period;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Calendar;

import bitel.billing.common.TimeUtils;
import ru.bitel.bgbilling.common.BGException;
import ru.bitel.bgbilling.kernel.tariff.common.bean.TariffPlan;
import bitel.billing.server.admin.reports.BGReportFilter;
import bitel.billing.server.contract.bean.PaymentManager;
import bitel.billing.server.npay.bean.ServiceObject;
import bitel.billing.server.npay.bean.ServiceObjectManager;
import bitel.billing.server.phone.bean.ClientItem;
import bitel.billing.server.phone.bean.ClientItemManager;
import bitel.billing.server.reports.BGCSVReport.ReportResult;
import bitel.billing.server.contract.bean.ContractTariffManager;

public class CustomClientsMarj implements
      bitel.billing.server.reports.BGCSVReport.CSVFillerData {
   @Override
   public void fillReport(Connection con, BGReportFilter filter,
         ReportResult result) throws SQLException, BGException {
      int j = 0;

      int NPAY_MID = 9;
      StringBuilder sb = new StringBuilder ();
      String TABLE_GROUPED_PHONE_ACCOUNT = "_phone_account_upload_";//Группа для выборки всех договоров и звонков по ним,
                                                      //дальше приджойнить к выборке по операторскому договору
      ServiceObjectManager som = new ServiceObjectManager( con, NPAY_MID ) ;
      ServiceObject srv = new ServiceObject();
      BigDecimal allCost = BigDecimal.ZERO;
      BigDecimal allCount = BigDecimal.ZERO;
      BigDecimal allCount_oper = BigDecimal.ZERO;
      ClientItemManager cim = new ClientItemManager(con, 12);
      String text = filter.getStringParam("text");
      int combo = filter.getIntParam("combo");
      Calendar month = filter.getCalendarParam( "month" );
      String yyyy = TimeUtils.format ( month, "yyyy" );
      String mm = TimeUtils.format ( month, "MM" );
      String yyyyMM = yyyy+mm;
      int time = 0;
      java.util.Date date1 = filter.getDateParam("date1");
      java.util.Date date2 = filter.getDateParam("date2");
      String groups = "3"; // коды групп договоров через запятую
      long groupMask = Utils.enumToMask(groups);
      
      con.createStatement().executeUpdate( "DROP TEMPORARY TABLE IF EXISTS "   + TABLE_GROUPED_PHONE_ACCOUNT);
      //con.createStatement().executeUpdate( "DROP TEMPORARY TABLE IF EXISTS _phone_account_upload_");
      
      
      String query_temp = "CREATE TEMPORARY TABLE " + TABLE_GROUPED_PHONE_ACCOUNT + " ( `id` int(11) NOT NULL, `cdr_id` int(11) NOT NULL, `hour` datetime NOT NULL, " +
      //String query_temp = "CREATE TEMPORARY TABLE _phone_account_upload_ ( `cdr_id` int(11) NOT NULL," +
        " `summa` decimal(10,2) NOT NULL, PRIMARY KEY  (`id` ) ) SELECT id, cdr_id, ROUND(cost,2) AS summa, hour " +
        " FROM log_session_12_"+yyyyMM+" "
      + " WHERE cid IN (580, 581, 588) AND session_time > 2 LIMIT 1000 ";
      PreparedStatement psCreateTempTable = con.prepareStatement(query_temp);
      psCreateTempTable.executeUpdate();
      psCreateTempTable.close();
      
      List<Map<String, String>> data = new ArrayList<Map<String, String>>(1000);
      Map<String, String> map = new HashMap<String, String>();
      if (!text.equals("") && !text.isEmpty())
         {
            String query = " SELECT c.id, c.title, c.comment, ls.cost, ls.session_time, ls.session_start, ROUND(tgpa.summa,2), ls.to_number, ls.cdr_id FROM contract AS c "
                  + " LEFT JOIN log_session_12_"+yyyyMM+" AS ls ON ls.cid = c.id "
                  + " LEFT JOIN " + TABLE_GROUPED_PHONE_ACCOUNT + " AS tgpa ON ls.cdr_id = tgpa.cdr_id AND ls.hour = tgpa.hour "
                  //+ " WHERE gr&(" + groupMask + ")>0 AND c.id IN (1758,1846) AND ls.session_time > 2 "
                  + " WHERE gr&(" + groupMask + ")>0 AND ls.session_time > 2 "
                  //+ " AND scid = -1 AND title REGEXP '"+text+"' AND ls.to_number = 79271716905";
                  + " AND scid = -1 AND title REGEXP '"+text+"' ORDER BY c.id LIMIT 1000";
            sb.append(query);
         }
      else
         {
            String query = " SELECT c.id, c.title, c.comment, ls.cost, ls.session_time, ls.session_start, ROUND(tgpa.summa,2), ls.to_number FROM contract AS c "
                  + " LEFT JOIN log_session_12_"+yyyyMM+" AS ls ON ls.cid = c.id"
                  + " LEFT JOIN " + TABLE_GROUPED_PHONE_ACCOUNT + " AS tgpa ON ls.cdr_id = tgpa.cdr_id AND ls.hour=tgpa.hour"
                  //+ " WHERE gr&("+ groupMask + ")>0 AND ls.session_time > 2 AND c.id IN (1758,1846) AND scid = -1 ORDER BY cdr_id";
                  + " WHERE gr&("+ groupMask + ")>0 AND ls.session_time > 2 AND scid = -1 ORDER BY c.id LIMIT 1000";
            sb.append(query);
         }

      int i = 0;


      PreparedStatement ps = con.prepareStatement(sb.toString());
      ResultSet rs = ps.executeQuery();
      while (rs.next())
         {
            Map<String, String> NewMap = new HashMap<String, String>();
Map<String, String>    NewMap2 = new HashMap<String, String>();
            //System.out.println("query " + sb.toString());
            j++;

            i++;
            //BigDecimal cost_oper = BigDecimal.ZERO;
            BigDecimal cost = BigDecimal.ZERO;
            BigDecimal cost_oper = BigDecimal.ZERO;
            int cid = rs.getInt(1);
            String title = rs.getString(2);
            String comment = rs.getString(3);
            cost = rs.getBigDecimal(4);
            time = time + rs.getInt(5);
            cost_oper = rs.getBigDecimal(7);
            String toNumber = rs.getString(8);
            int cdr_id = rs.getInt(9);
            NewMap.put( "cid", String.valueOf( cid ) );
            NewMap.put("title", title);
            NewMap.put("comment", comment);
            NewMap.put("count", cost.toString() );
            if ( cost_oper != null )
            {
            NewMap.put("count_oper", cost_oper.toString() );
            NewMap.put("time", rs.getString(6));
            NewMap.put("cost", cost.subtract(cost_oper).toString());
            NewMap.put("toNumber", toNumber);
            data.add(NewMap);
            allCost = allCost.add(cost.subtract(cost_oper));   
            allCount = allCount.add(cost);
            allCount_oper = allCount_oper.add(cost_oper);
            System.out.println("cdr_id " + cdr_id + " cdr_id_oper " + cdr_id);
            }
            //map.put("time", String.valueOf(rs.getInt(7)) + " время сессии " + rs.getString(8));
            //map.put("count", String.valueOf(j));
            /*if (cid != 588)
            {
            System.out.println("cdr_id " + cdr_id + " cdr_id_oper " + cdr_id);
            }
            
            String query = "SELECT cost, session_start FROM log_session_12_"+yyyyMM+" AS ls "
                  + "WHERE cid = 588 AND cdr_id = " + cdr_id;
            PreparedStatement psq = con.prepareStatement(query);
            ResultSet rsq = psq.executeQuery();
               while (rsq.next())
               {

                  cost_oper = rsq.getBigDecimal(1);
                  System.out.println("cost  " + rs.getBigDecimal(5).toString() + " cost_oper " + cost_oper + " cdr_id " + cdr_id + "\n\n");
                  NewMap.put("count", rs.getBigDecimal(5).toString() );
                  NewMap.put("count_oper", cost_oper.toString());
                  NewMap.put("time", rs.getString(8));
                  cost = rs.getBigDecimal(5).subtract(cost_oper);
               }
            */
            /*NewMap.put("cost", cost.toString());
            allCost = allCost.add(cost);   
            allCount = allCount.add(rs.getBigDecimal(5));
            allCount_oper = allCount_oper.add(cost_oper);
               */

            //NewMap2.put("count_oper", allCount_oper.toString());
            NewMap2.put("count", String.valueOf( 5 ));
            data.add(NewMap2);
            //data.add(NewMap);
         }
                     
      
      


      int Time = time/60;

      //map = new HashMap<String, String>();
      map.put("cost", allCost.toString());
      map.put("count", allCount.toString());
      map.put("count_oper", allCount_oper.toString());
      map.put("time", String.valueOf(Time) + " минут");
      //map.put("cost", allCost.toString());
      data.add(map);      
      result.setData(data);
   }

}