продолжаем =)
не могу догнать как вывести подсчет по каждому договору, по всем сделал, а как по каждому, подскажите плиз
Код:
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);
}
}
запрос выводит несколько договоров, по каждому ещё несколько полей, вот по каждому договору и хочу сделать подсчет
_________________
Код:
Клиент: вер. 6.2.714 / 25.05.2015 17:27:15
os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_45
Сервер: вер. 6.2.881 / 22.05.2015 17:56:55
os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_45
Помощь по администрированию bgbilling в jabber конференции или Группа в telegramСтиль программирования - пьяный мастерстер
Разработка мобильных приложений