Для сверки наработки клиентов с LifeStreamTV пользуемся этим отчетом (дин. класс). Может быть, кому пригодится.
Для работы скрипта необходимо добавить атрибут для продуктов TV: Процент агентского вознаграждения (Справочники -> Атрибуты)
Версия биллинга: 7.1
Класс отчета
Код:
package ru.xxx.bgbilling.reports.dyn;
import bitel.billing.server.admin.reports.BGReportFilter;
import bitel.billing.server.reports.BGCSVReport.*;
import bitel.billing.server.reports.BGCSVReport.ReportResult;
import ru.bitel.bgbilling.server.util.ModuleSetup;
import ru.bitel.common.Utils;
import java.sql.Connection;
import java.text.DecimalFormat;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
/**
* @author alex
* Агентский отчет по наработке TV за месяц.
* Тарифы определяются исходя из посуточных начислений из таблицы tv_account_detail_*.
* Процент агентского вознаграждения находится в атрибутах продукта
*/
public class tv_lfstrm_2
implements CSVFillerData
{
private static final int TV_MODULE_ID = 25;
private static final int CONTRACT_EMAIL_PARAMETER_ID = 11;
private static final int TV_PRODUCT_ATTR_MARGIN_PERCENT_ID = 5; // атрибут продукта: процент агентского вознаграждения
private static final String TV_CONTRACT_GROUPS = "27,29"; // группы субдоговоров: ТВ
@Override
public void fillReport( Connection con, BGReportFilter filter, ReportResult result )
throws Exception
{
String month=filter.getStringParam("month");
int agentId = filter.getIntParam( "agents" ); // Контрагент. Если не выбран, равен 0.
DecimalFormat df = new DecimalFormat();
df.setMaximumFractionDigits(2);
df.setMinimumFractionDigits(2);
df.setGroupingUsed(false);
String prefix = new ModuleSetup( con, TV_MODULE_ID).get("account.login.prefix", "");
String qSel = "SELECT cid,"
+ " supercontract,"
+ " login,"
+ " accEmail,"
+ " loginRegFrom,"
+ " clientName,"
+ " activationTime,"
+ " deactivationTime,"
+ " amountDays *24 AS amountHours,"
+ " productId,"
+ " productName,"
+ " COALESCE(ROUND( abonpay / amountDays * DATE_FORMAT( LAST_DAY('" + month.substring(6, 10) + "-" + month.substring(3, 5) + "-01'), '%e'), 2), -1) AS tariff,"
+ " COALESCE(ROUND(abonpay, 2), -1) AS round_abonpay,"
+ " COALESCE( margin_percent / 100, -1) AS margin_rate,"
+ " COALESCE(ROUND( abonpay * margin_percent / 100, 2), -1) AS margin"
+ " FROM ("
+ " SELECT con.id AS cid,"
+ " scon.title AS supercontract,"
+ " tva.login AS login,"
+ " tmail.email AS accEmail,"
+ " tva.DateFrom AS loginRegFrom,"
+ " con.comment AS clientName,"
+ " ip.ActivationTime AS activationTime,"
+ " ip.DeactivationTime AS deactivationTime,"
+ " COUNT(tvad.day) AS amountDays,"
+ " ips.identifier AS productId,"
+ " ips.title AS productName,"
+ " SUM(tvad.account) AS abonpay,"
+ " attr.value AS margin_percent"
+ " FROM contract AS con"
+ " JOIN contract AS scon ON con.scid = scon.id"
+ " JOIN contract_parameter_type_7 AS cpt7 ON cpt7.cid = con.id"
+ " JOIN tv_account_detail_" + TV_MODULE_ID + "_" + month.substring(6, 10) + month.substring(3, 5) + " AS tvad ON con.id = tvad.contractId"
+ " LEFT JOIN inv_product AS ip ON tvad.productId = ip.id"
+ " LEFT JOIN inv_product_spec AS ips ON ip.productspecid = ips.id"
+ " LEFT JOIN tv_account_" + TV_MODULE_ID + " AS tva ON tva.id = tvad.accountId"
+ " LEFT JOIN contract_parameter_type_3 AS tmail ON con.id = tmail.cid AND tmail.pid = " + CONTRACT_EMAIL_PARAMETER_ID
+ " JOIN entity_attr_text AS attr on attr.entityId = ips.entityId AND attr.entitySpecAttrId = " + TV_PRODUCT_ATTR_MARGIN_PERCENT_ID
+ " WHERE con.gr & ? > 0 AND( ? OR cpt7.val = ?)"
+ " GROUP BY con.id, tvad.productId, tvad.account"
+ " ) AS t1";
List<Map<String, String>> data = new ArrayList<Map<String, String>>(600);
int countRows = 0;
BigDecimal tariff;
BigDecimal abonpay;
BigDecimal margin;
BigDecimal paySum = BigDecimal.ZERO;
BigDecimal marginSum = BigDecimal.ZERO;
try( PreparedStatement psSel = con.prepareStatement( qSel)){
psSel.setLong(1, Utils.enumToMask(TV_CONTRACT_GROUPS));
psSel.setBoolean(2, agentId == 0);
psSel.setInt(3, agentId);
ResultSet rsSel = psSel.executeQuery();
while( rsSel.next()){
Map<String, String> map = new HashMap<String, String>();
map.put("npp", String.valueOf( countRows + 1));
map.put("cid", rsSel.getString("cid"));
// map.put("supercontract", rsSel.getString("supercontract"));
map.put("login", prefix + rsSel.getString("login"));
map.put("accEmail", rsSel.getString("accEmail"));
map.put("phone", " ");
map.put("loginRegFrom", rsSel.getString("loginRegFrom"));
map.put("clientName", rsSel.getString("clientName"));
map.put("loginDateFrom", " ");
map.put("serviceDuration", " ");
map.put("activationTime", rsSel.getString("activationTime"));
map.put("deactivationTime", rsSel.getString("deactivationTime"));
map.put("amountHours", rsSel.getString("amountHours"));
map.put("productId", rsSel.getString("productId"));
map.put("productName", rsSel.getString("productName"));
tariff = rsSel.getBigDecimal("tariff");
map.put("productTariff", df.format(tariff));
map.put("marginRate", df.format(rsSel.getBigDecimal("margin_rate")));
abonpay = rsSel.getBigDecimal("round_abonpay");
map.put("abonpay", df.format(abonpay));
paySum = paySum.add(abonpay);
margin = rsSel.getBigDecimal("margin");
map.put("margin", df.format(margin));
marginSum = marginSum.add( margin);
data.add(map);
countRows++;
}
}
// Summary
Map<String, String> map3 = new HashMap<String, String>();
map3.put("login", "ИТОГО");
map3.put("abonpay", df.format( paySum));
map3.put("margin", df.format( marginSum));
data.add(map3);
result.setData(data);
}
}
Описатель отчета
Код:
<?xml version="1.0" encoding="UTF-8"?>
<report title="Агентский отчет по наработке клиентов TV (с НДС)" dyn_class="ru.xxx.bgbilling.reports.dyn.tv_lfstrm_2" type="java">
<!-- http://docs.bitel.ru/pages/viewpage.action?pageId=1607043 -->
<month name="month" title="Месяц" />
<combo name="agents" title="Контрагент" sql="SELECT id,title FROM contract_parameter_type_7_values WHERE pid=59 ORDER BY title">
<item id="0" title="-" />
</combo>
<fields>
<item id="npp" title="№" />
<item id="cid" title="#cid" />
<!-- <item id="supercontract" title="Договор" /> -->
<item id="login" title="Логин" />
<item id="accEmail" title="Email" />
<item id="phone" title="Телефон"></item>
<item id="loginRegFrom" title="Дата регистрации" />
<item id="clientName" title="ФИО" />
<item id="loginDateFrom" title="Дата активации" />
<item id="serviceDuration" title="Длит.предоставл.услуги" />
<item id="activationTime" title="Дата активации подписки" />
<item id="deactivationTime" title="Дата закрытия подписки" />
<item id="amountHours" title="Длит. предоставления услуги" />
<item id="productId" title="Идент.подписки" />
<item id="productName" title="Название подписки" />
<item id="productTariff" title="Стоимость подписки" />
<item id="marginRate" title="Размер Агентского вознаграждения" />
<item id="abonpay" title="Начисленная абонентская плата с НДС" />
<item id="margin" title="Начисленное агентское вознаграждение с НДС" />
</fields>
</report>