Сделай табличный отчет для бухов
Для версии 6.1 где используется динкод для отчетовcustom_agent_payment.rep.xmlКод:
<?xml version="1.0" encoding="UTF-8"?>
<report title="Агент || Платежи" type="java" dyn_class="ru.ellcom.bgbilling.modules.reports.AgentPayments">
<contracts name="contracts" title="Договор контрагента"/>
<period name1="period1" default1="first_day_of_month" name2="period2" default2="last_day_of_month"/>
<paymentTypes name="paymentTypes" title="Типы платежей"/>
<users name="users" title="Пользователи"/>
<contractGroups name="contractGroups" title="Группы договоров"/>
<hide title="Дополнительные параметры">
<combo name="type" title="Тип клиента">
<item id="-1" title="Все"/>
<item id="0" title="Физ.лицо"/>
<item id="1" title="Юр.лицо"/>
</combo>
<combo name="agent" title="Учитывать договора контрагентов">
<item id="yes" title="Да"/>
<item id="no" title="Нет"/>
<item id="only" title="Только договора контрагентов"/>
</combo>
<checkedList name="withoutcontract" title="Не учитывать договора">
<item id="301" title="Телефонии (301%)"/>
<item id="302" title="SIP телефонии (302%)"/>
<item id="901" title="IP телефонии (901%)"/>
<item id="201" title="Внешние гарант (201%)"/>
<item id="109" title="Служебные (109%)"/>
<item id="101" title="PPPoE (101%)"/>
</checkedList>
</hide>
<hide title="Параметры адреса">
<address cityName="city" streetName="street" title="Адрес"/>
</hide>
<fields>
<item id="cid" title="#cid"/>
<item id="title" title="Договор"/>
<item id="comment" title="Клиент"/>
<item id="fc" title="Юр. статус"/>
<item id="address" title="Адрес"/>
<item id="user" title="Пользователь"/>
<item id="summa" title="Сумма"/>
<item id="paydate" title="Дата платежа"/>
<item id="changedate" title="Дата изменения платежа"/>
<item id="paytype" title="Тип платежа"/>
<item id="paycomment" title="Комментарий платежа"/>
<item id="agentcode" title="Код контрагента в 1С"/>
<item id="contractcode" title="Код договора в 1С"/>
<item id="contract_text" title="Номер договора (параметр)"/>
<item id="att" title="Обслуживающее лицо"/>
<item id="part" title="Контрагент"/>
</fields>
</report>
ru.ellcom.bgbilling.modules.reports.AgentPaymentsКод:
package ru.ellcom.bgbilling.modules.reports;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import ru.bitel.common.Utils;
import bitel.billing.common.TimeUtils;
import bitel.billing.server.admin.reports.BGReportFilter;
import bitel.billing.server.reports.BGCSVReport.ReportResult;
public class AgentPayments
implements bitel.billing.server.reports.BGCSVReport.CSVFillerData
{
@Override
public void fillReport(Connection con, BGReportFilter filter, ReportResult result) throws Exception {
int face = filter.getIntParam("type", -1);
java.sql.Date date1 = TimeUtils.convertCalendarToSqlDate(filter.getCalendarParam("period1"));
java.sql.Date date2 = TimeUtils.convertCalendarToSqlDate(filter.getCalendarParam("period2"));
String payment_types = filter.getStringParam("paymentTypes");
String users = filter.getStringParam("users");
DecimalFormat dfSumm = new DecimalFormat("0.00");
String partner = filter.getStringParam("contracts");
//if(partner == null ){ partner = ""; }
int cityID = filter.getIntParam("cityId");
int streetID = filter.getIntParam("streetId");
String agent = filter.getStringParam("agent");
String cnt = filter.getStringParam("withoutcontract");
int contractGroups = filter.getIntParam("contractGroups");
List<String> cntList = Utils.toList(cnt);
List<Map<String, String>> data = new ArrayList<Map<String, String>>( 1000 );
BigDecimal summ = new BigDecimal (0.00);
String query = "SELECT c.id, c.title, c.comment, address.address, IF(c.fc = 0,'Физическое лицо','Юридическое лицо') AS fc, IFNULL(u.name,'Электронный платеж') AS uname, pay.summa, pay.dt, pay.comment, pay.lm, ptype.title, cpar.val, att.val, con.val, d1c.val, agent.title " +
" FROM contract c" +
" LEFT JOIN contract_payment AS pay ON pay.cid=c.id" +
" LEFT JOIN user AS u ON pay.uid=u.id" +
" LEFT JOIN contract_payment_types AS ptype ON pay.pt=ptype.id" +
" LEFT JOIN contract_parameter_type_2 as address ON c.id=address.cid AND address.pid=25" +
" LEFT JOIN contract_parameter_type_1 as cpar ON c.id=cpar.cid AND cpar.pid=21" +
" LEFT JOIN contract_parameter_type_1 as att ON c.id=att.cid AND att.pid=33" +
" LEFT JOIN contract_parameter_type_1 as con ON c.id=con.cid AND con.pid=12" +
" LEFT JOIN contract_parameter_type_1 as d1c ON c.id=d1c.cid AND d1c.pid=32" +
" LEFT JOIN address_house AS house ON address.hid=house.id" +
" LEFT JOIN address_street AS street ON house.streetid =street.id" +
" LEFT JOIN address_city AS city ON city.id=street.cityid" +
" LEFT JOIN contract_parameter_type_8 as contracts ON contracts.cid=c.id AND contracts.pid = 19 " +
" LEFT JOIN contract AS agent ON contracts.val = agent.id " +
" WHERE pay.dt BETWEEN '" + date1 + "' AND '" + date2 + "'";
if(face >= 0)
{
query += " AND c.fc = " + face;
}
if(Utils.notEmptyString(partner))
{
query += " AND contracts.val IN (" + partner + ") AND contracts.cid NOT IN (" + partner + ")";
}
if(Utils.notEmptyString(payment_types))
{
query += " AND pay.pt IN (" + payment_types + ")";
}
if(Utils.notEmptyString(users))
{
query += " AND u.id IN (" + users + ")";
}
if(agent.equals("no"))
{
query +=" AND c.id NOT IN (SELECT cid FROM contract_parameter_type_8 WHERE val IS NOT NULL AND pid = 19)";
}
if(agent.equals("only"))
{
query +=" AND c.id IN (SELECT cid FROM contract_parameter_type_8 WHERE val IS NOT NULL AND pid = 19)";
}
if(streetID > 0)
{
query += " AND house.streetid = " + streetID;
}
if(cityID > 0)
{
query += " AND city.id = " + cityID;
}
if(!cntList.isEmpty()){
for(Iterator<String> it = cntList.iterator(); it.hasNext();){
query += " AND c.title NOT LIKE '" + it.next() + "%' ";
}
}
if(contractGroups > 0)
{
query += " AND c.gr = " + contractGroups;
}
query += " ORDER BY pay.dt";
PreparedStatement ps = con.prepareStatement( query );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
int cid = rs.getInt( "c.id" );
String title = rs.getString( "c.title" );
String comment = rs.getString( "c.comment" );
String fc = rs.getString("fc");
String payComment = rs.getString( "pay.comment" );
String address = rs.getString( "address.address" );
BigDecimal summa = rs.getBigDecimal("pay.summa");
summ = summ.add(summa);
String user = rs.getString("uname");
String paydate = rs.getString("pay.dt");
String paytype = rs.getString("ptype.title");
String changedate = rs.getString("pay.lm");
String agentcode = rs.getString("cpar.val");
String contractcode = rs.getString("d1c.val");
String att = rs.getString("att.val");
String contract_text = rs.getString("con.val");
String part = rs.getString("agent.title");
HashMap<String, String> map = new HashMap<String, String>();
map.put("cid", String.valueOf(cid));
map.put("title", title);
map.put("comment", comment);
map.put("fc", fc);
map.put("address", address);
map.put("user", user);
map.put("summa", dfSumm.format(summa));
map.put("paydate", paydate);
map.put("changedate", changedate);
map.put("paycomment", payComment);
map.put("paytype", paytype);
map.put("agentcode", agentcode);
map.put("att", att);
map.put("contract_text", contract_text);
map.put("part", part);
map.put("contractcode", contractcode);
data.add(map);
}
rs.close();
ps.close();
HashMap<String, String> map = new HashMap<String, String>();
map.put("comment", "Итого:");
map.put("summa", dfSumm.format(summ));
data.add(map);
result.setData(data);
}
}