|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Изменения (79)
просмотр истории страницы{toc:maxLevel=32} |
|
h21. Описание конструктора отчетов |
|
*Конструктор отчетов* позволяет делать запросы напрямую к БД. Описание ограничений, возможностей и синтаксиса используемого диалекта SQL Вы можете найти в документации Firebird 2.15. |
Конструктор отчетов находится на вкладке "Отчеты" в основном меню биллинга \\ |
... |
\\ !report_builder_3.png|border=1! |
h3. Параметры полей формы |
h2. Фильтры данных (параметры полей формы) |
|
Переменные, отображаемые в виде формы: |
В отчётах можно добавить фильтры - выбор информации, например счетов и актов, только за выбранные даты или период дат, отфильтровать по абонентам и тд. Фильтры подставляют указанные данные в тело отчёта и потом он выполняется. Фильтры указываются примерно по такой схеме: |
{code:lang=sql}':title|type[params]$'{code} * : - указывает, что это строка с переменной * title - заголовок, отображаемый на форме * \| - разделитель заголовка и типа |
* type\[params\] - тип с параметрами: |
* $ - флаг отмечает обязательную переменную, он обязателен для всех фильтров Ниже они описаны подробно. h3. Выбор дат |
** date *date* - дата, указывается без параметров, отображает на форме календарь выбора даты |
Прмер: |
{code}BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'{code} |
** choices - список |
h3. Выбор периодов *monthchoice* - отобразит список периодов в формате "ММ.ГГГГ месяц_прописью" Примеры: * 12 последних месяцев {code}':Дата Список месяцев|monthchoice|$'{code} * 3 последних месяца {code}':Дата Список месяцев|monthchoice|3$'{code} * min и max по полю period_end_date модели financeoperations (можно ещё фильтры вписать, так же как для опции select) {code}':Дата Список месяцев|monthchoice|period_end_date|select[financeoperations]$'{code} * min и max по полю period_end_date модели financeoperations с фильтрами: только не сторнированные акты {code}':Дата Список месяцев|monthchoice|period_end_date|select[financeoperations,op_type=2,storno=0]$'{code} h3. Список вариантов *choices* - список, параметры - это элементы списка Примеры: |
{code:lang=sql}Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$' owner_id in (:Администратор|choices[33^]root^[1005^]Василий^[1007,1005^]Василий или Михаил]$) Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'{code} |
h3. Список вариантов из справочников биллинга |
** select *select* - список, построенный на основе модели, подобно работе [API|CarbonBilling:API REST v2.0], параметры содержат модель (обязательно) и фильтры |
Примеры: |
{code}a.parent_id = ':Группа|select[Abonents,is_folder=1]$' h.id = ':Заявка|select[HDSK]$'{code} |
** Отсутстие параметров запросит строку: |
h3. Строка для ввода произвольных данных: Отсутстие параметров предложет ввести произвольные данные. |
{code} ':Введите число$' {code} |
* $ - флаг отмечает обязательную переменную |
|
h32. Выполнение отчёта из консоли скриптом make_reports.pyc |
Для того, чтобы запустить выполнение отчета из консоли, необходимо: |
... |
По-умолчанибю информация, выведенная на экран, будет иметь кодировку Windows (windows-1251, cp1251) |
У отчета скрипта есть несколько опций и пресетов для отчетов выгрузок в [СОРМ3|Интеграция с СОРМ3] |
|
h43. Дополнительные опции запуска |
|| Опция || Полный формат || Пример || Описание || | -c CHARSET | --charset=CHARSET | -c utf8 | Кодировка вывода, по-умолчанию: cp1251 |
... |
|
h43. Пример запуска make_reports с опциями |
|
* Выгрузить отчет #105 в формате, пригодном для [СОРМ от компании "Норси-Транс"|CarbonBilling:Интеграция с СОРМ 3 "Норси-Транс"|СОРМ3 НОРСИ-ТРАНС Яхонт компании НОРСИ-ТРАНС] (архив)] и кодировке UTF-8: |
{code}python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -c utf8 -p norsi-trans{code} * Выгрузить отчет #105, разделяя поля запятыми и экранируя двойными кавычками только поля со спецсимволами: |
... |
{code}python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -s ";" -q \" -f{code} |
h3. Описание полей |
h2. Выполнение отчёта из консоли утилитой sqlexec |
|
Запрос к базе данных можно выполнить передав его утилите sqlexec, например так: {code:title=Команда}sqlexec "select count(*) from abonents"{code} {code:title=Вывод} COUNT ============ 14 {code} Запрос можно написать в несколько строк: {code:title=Команда}sqlexec "select count(*) from abonents"{code} Это удобно, если запрос сложный и объёмный. Вывод будет таким же, как у команды выше. h2. Выполнение запроса для использования в скриптах Тут возможны два варианта. h3. С помощью sqlexec Если отчёт достаточно простой, его можно выполнить с помощью sqlexec. Но нужно учитвать несколько моментов: * На самом деле утилита работает в контейнере asr_billing * Вывод лучше отформатировать для удобства написания скриптов и сохранения данных в переменные * Частично отформатировать Вывод можно средствами SQL: ** либо "*set heading off;*" - уберёт заголовки, это удобно если Вы выгружаете, например, список каких-то позиций (абонентов, финансовых операций) для обработки каждой в отдельности ** либо "*set list on;*" - "перевернёт" данные, отразит их в виде колонок вместо таблицы Ниже приведён скрипт, в котором показано как работать с выводом в том или ином виде, включая одиночные значения и списки: {code} #!/bin/bash abonent_count=$(chroot /app/asr_billing/ /usr/local/bin/sqlexec "set list; select count(*) from abonents where is_folder=0" | awk '$2{print $2}') folder_count=$(chroot /app/asr_billing/ /usr/local/bin/sqlexec "set heading off; select count(*) from abonents where is_folder=1" | sed 's/ *//g; /^$/d') echo "В биллиге сейчас вот столько абонентов, администраторов, и операторов связи: ${abonent_count}" echo "В биллиге сейчас вот столько папок: ${folder_count}" echo "Список папок:" chroot /app/asr_billing/ /usr/local/bin/sqlexec "set heading off; select name from abonents where is_folder=1" | sed 's/ *//g; /^$/d' | \ while read folder; do echo "* ${folder}" done {code} h3. Если sqlexec не очень подходит, отчёт слишком сложный Если отчёт слишком сложный и его удобней сохранить в файл, тогда сделайте следующее: # Убедитесь что запрос в файле заканчивается символом точки с запятой: ";" # Положите файл где-нибудь внутри контейнера asr_billing # Выполните запрос командой isql-fb, ключём -i укажите путь к файлу Например, можно положить скрипт в папку opt, внутри контейнера, с таким содержимым: {code:title=Файл /app/asr_billing/opt/abonents.sql} select count(*) from abonents where is_folder=0 ; {code} Тогда запрос к БД можно выплнить такой командой: {code:title=Команда}chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /opt/abonents.sql{code} {note}Заметьте, что для "хост" системы, файл находится по пути /app/asr_billing/opt/abonents.sql, но утилита isql-fb находится в контейнере, и для неё нужно указывать "относительный" путь: */opt/abonents.sql*, убрав адрес корневой папки контейнера. {note} Вывод отчёта Вы так же можете форматировать командными утилитами ОС, сохранять в переменные или передавать в циклы для дальнейшей обработки. h2. Описание полей |
К большенсту полей БД существуют описания. Описание всех полей можно получить выполнив команду *sqlexeс "show comments"*, например: {code}sqlexec "show comments" | head -n 5 |
... |
COMMENT ON COLUMN TARIF.PROMISE_PAY_ONLY_THIS_MONTH IS Разрешать подключение обещанного платежа только один раз в текущий месяц;{code} |
h32. Особенности SQL в Firebird |
|
h43. UNION (UNION ALL) и ORDER BY |
h6. В Firebird Order By должен быть последним оператором, сортируя всю выборку, например: |
... |
Список символов можно посмотреть в [Википедии|https://en.wikipedia.org/wiki/Whitespace_character], в примере использован символ U+2003 "em space" |
h43. Особенность сортировки числовых значений |
В примере выше выборка будет отсортирована как текстовая. То есть порядок чисел будет: *1,10,100,2,20,200*. Что бы отсортировать выборку как числа нужно убрать строковые константы в полях и заменить их на значение *null*. Тогда результат будет такой: *1,2,10,20,100,200*. {code} select |
... |
{code} |
h3. Полезные функции в Firebird |
h3. Ограничения в наименовании столбцов отчета {info}В случае использования кириллицы в названии столбцов отчета важно знать, что поле может содержать не более 18 символов.{info} Если требуется указать более развернутое название - используйте латиницу. Иначе, будет возникать [ошибка|https://docs.carbonsoft.ru/pages/viewpage.action?pageId=155714114#Отладкаотчетов-Привыполненииотчетавозниклаошибка"Произошлаошибкапоадресу] при выполнении отчета. |
|
h4. iif Функция *iif* аналогична опратору сравнения if then else. {code} IIF(<поисковое условие>, <значение1>, <значение2>) {code} Если <поисковое условие> верно то возвращается <значение1> иначе <значение2>. Удобно использовать, если в выборке встречаются пустые поля. В конструкторе отчётов они выводятся как *none*. Пример: {code} select iif(a.home_id is null, 'НеУказан',h.street || ' ' || h.s_number) from abonents a left join homes h on a.home_id=h.id order by 1 {code} В примере, вместо слова *None* будет стоять *НеУказан*. |
|
Также можно заменять числовые значения текстом: {code} select name, iif(deleted=1,'Удалён','') from abonents {code} Удалённые абонеты будут выведены с пометкой *Удалён*, дейсвующие без отметки. |
h2. Процедуры Carbon Soft |
|
Для работы с базой мы добавили ряд собственных процедур, которые помогут найти нужные данные и верно их отобразить в отчётах в соответствии с архитектурой биллинга. |
h3. Получение списка вложенных групп и абонентов в дереве групп |
|
h4. GLN_RECURSIVE_GROUP_WALK |
... |
h4. Примеры запросов |
{code:title=ID групп} |
{code}SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(1) |
SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1){code} h2. Примеры # h5. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона |
{code} |
select UR.user_id as ID, U.identify as FIO, AV.attribute_value as TELEFON from users_radiusauth UR left join USERS U on U.id=UR.user_id left join ATTRIBUTE_VALUES AV on AV.user_id=UR.user_id and AV.ATTRIBUTE_ID=1 where UF_IP2STRING(UR.NAS_IP_ADDRESS)='172.16.0.6' order by U.identify |
{code:title=ID абонентов} SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1) |
{code} |
{code:title=Имена абонентов} select a.name from GLN_RECURSIVE_ABONENTS_GET(1) aids inner join abonents a on a.id=aids.abonent_id {code} h1. Примеры отчётов |
# h5. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата {code} |
... |
order by USER_NAME_OUT {code} |
# h5. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, IP коммутатор, Номер порта, Номер vlan) с группировкой по номеру коммутатора {code} select u.identify as FIO, sw.name as NAME, uf_ip2string(sw.ip) as SWITCH_IP, sp.num as PORT, sp.vlan as VLAN from switch_ports sp join users u on u.id=sp.user_id join switch sw on sw.id=sp.switch_id order by sp.switch_id {code} |
# h5. Общее количество абонентов по группам {code} |
... |
# h5. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента) {code} |
select A.CONTRACT_NUMBER, |
A.NAME, T.NAME, ABSP.CEIL_IN, (select AV.ATTRIBUTE_VALUE from ATTRIBUTE_VALUES AV where AV.ATTRIBUTE_ID = 14 and AV.ABONENT_ID = A.ID), (select AV.ATTRIBUTE_VALUE from ATTRIBUTE_VALUES AV where AV.ATTRIBUTE_ID = 13 and AV.ABONENT_ID = A.ID), (select AV.ATTRIBUTE_VALUE from ATTRIBUTE_VALUES AV where AV.ATTRIBUTE_ID = 16 and AV.ABONENT_ID = A.ID), (select AV.ATTRIBUTE_VALUE from ATTRIBUTE_VALUES AV where AV.ATTRIBUTE_ID = 17 and AV.ABONENT_ID = A.ID), H.CITY, H.STREET, H.S_NUMBER, H.S_LITER, A.A_HOME_NUMBER, A.SMS |
A.CONTRACT_NUMBER as "Номер договора", A.NAME as "ФИО", SER.ATTRIBUTE_VALUE as "Паспорт серия", NOM.ATTRIBUTE_VALUE as "Паспорт номер", coalesce(PAS1.ATTRIBUTE_VALUE,'') as "Кем выдан", coalesce(PAS2.ATTRIBUTE_VALUE,'') as "Дата выдачи", coalesce(birthday.ATTRIBUTE_VALUE,'') as "Дата рождения", coalesce(H.CITY,'') as "Город", coalesce(H.STREET,'') as "Улица", H.S_NUMBER as "Номер дома", A.A_HOME_NUMBER as "Номер комнаты", A.SMS as "Телефон", EMAIL as "Эл. почта", T.NAME as "Тариф", ABSP.CEIL_IN as "Скорость" |
from ABONENTS A |
left join TARIF T on T.ID = A.TARIF_ID |
left join ABONENTS_SPEED ABSP on ABSP.ABONENT_ID = A.ID |
left join TARIF T on T.ID = A.TARIF_ID |
left join HOMES H on H.ID = A.HOME_ID |
where A.DELETED != 1 and A.IS_FOLDER != 1 |
left join ATTRIBUTE_VALUES SER on SER.ABONENT_ID=A.ID and SER.ATTRIBUTE_ID=14 left join ATTRIBUTE_VALUES NOM on NOM.ABONENT_ID=A.ID and NOM.ATTRIBUTE_ID=13 left join ATTRIBUTE_VALUES PAS1 on PAS1.ABONENT_ID=A.ID and PAS1.ATTRIBUTE_ID=16 left join ATTRIBUTE_VALUES PAS2 on PAS2.ABONENT_ID=A.ID and PAS2.ATTRIBUTE_ID=17 left join ATTRIBUTE_VALUES birthday on birthday.ABONENT_ID=A.ID and birthday.ATTRIBUTE_ID=22 where A.DELETED = 0 and A.IS_FOLDER = 0 and A.ID>0 |
order by A.NAME {code} |
... |
from USERS_USLUGA UU left join ABONENTS AB on UU.ABONENT_ID = AB.ID |
where UU.USLUGA_ID = 1247 |
where UU.USLUGA_ID in (1247, 1248, 1249) |
and UU.DELETED = 0 and (select count(1) |
... |
and is_folder=0 {code} |
Тот же отчет, выводящий абонентскую плату, с возможностью выбора группы {code} select '#FILESUM' || ' ' || round(sum(abon_plata.summa), 2) from abonents as a right join homes as h on a.home_id=h.id left join ( select uu.abonent_id abon_id, sum(iif(coalesce(uu.dinamyc_price,0)=0,u.summa,uu.dinamyc_price))/10000000000.00 summa from users_usluga uu join usluga u on uu.usluga_id=u.id where uu.deleted=0 group by uu.abonent_id ) abon_plata on a.id=abon_plata.abon_id where company=0 and is_folder=0 and a.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Группа|select[Abonents,is_folder=1]$')) UNION ALL select first 1 '#TYPE 7' from users UNION ALL select first 1 '#SERVICE 10240' from users UNION ALL select a.name || ';' || coalesce(h.city,'') || ',' || coalesce(h.street,'') || ',' || coalesce(h.s_number,'') || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||abon_plata.summa|| ';;;;' from abonents as a right join homes as h on a.home_id=h.id left join ( select uu.abonent_id abon_id, sum(iif(coalesce(uu.dinamyc_price,0)=0,u.summa,uu.dinamyc_price))/10000000000.00 summa from users_usluga uu join usluga u on uu.usluga_id=u.id where uu.deleted=0 group by uu.abonent_id ) abon_plata on a.id=abon_plata.abon_id where company=0 and is_folder=0 and a.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Группа|select[Abonents,is_folder=1]$')) {code} Тот же отчёт, выводящий дополнительные поля-константы и номер телефона {code} select first 1 -- #FILESUM 341098.84 - сумма всех платежей '#FILESUM ' || '' || cast(sum(ac.recomend_pay_sum_cache/10000000000.00) as varchar(32)) from abonents a join homes h on a.home_id=h.id join abonents_cache ac on a.id=ac.id union all select first 1 -- #TYPE 7 - константа '#TYPE 7' from abonents a join homes h on a.home_id=h.id join abonents_cache ac on a.id=ac.id union all select first 1 -- #SERVICE 60592 - константа '#SERVICE 60592' from abonents a join homes h on a.home_id=h.id join abonents_cache ac on a.id=ac.id union all select -- 1 поле - ФИО; a.name || ';' || -- 2 поле - Адрес; case when coalesce(h.settlement,'') <> '' then h.settlement -- если есть поселение, берём его else h.city -- иначе город end || ',' || coalesce(h.street,'') || ',' || -- улица case when (coalesce(s_number,'') <> '' and coalesce(s_liter,'') <> '') then s_number || '/' || s_liter -- дом и корпус when coalesce(s_number,'') <> '' then s_number -- дом else '' -- ничего или только корпус end || ',' || coalesce(a.a_home_number,'') || ';' || -- 3 поле - Номер договора; a.contract_number || ';' || -- 4 поле - сумма; cast(ac.recomend_pay_sum_cache/10000000000.00 as varchar(32)) || ';' || -- 5-7 пустые; -- '5;6;7' || ';' || -- DEBUG ';;' || ';' || -- 8 поле - 7705:9120000001:0 (константа:номер телефона:константа) '7705' || ':' || coalesce(a.sms,'') || ':' || '0' from abonents a join homes h on a.home_id=h.id join abonents_cache ac on a.id=ac.id where coalesce(a.company,0) = 0 {code} |
# h5. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number) {code:lang=sql} |
... |
and a.CREATE_DATE between ':1 Дата|date$' and ':2 Дата|date$' {code} |
# h5. Телефония. Количество звонков, ожидающих перерасчет. {code} select count(1) from voip_log where recalc=1 {code} |
# h5. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)" {code} |
... |
select distinct u.name as "Услуга", round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена", |
round(sum(counters.SUMM), 2) as "Сумма" |
from counters c |
left join usluga u on u.id=counters.usluga_id |
where S_DATE between (':1 Дата|date$') and (':2 Дата|date$') |
where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and c.year_number = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) |
group by u.name, u.SUMMA {code} |
... |
{code} # h5. Поиск IP-адреса в истории изменения учетных записей (история выдачи IP-адреса) |
При каждом измение изменении учётной записи новое состояние учётной записи записывается в историю. Таблица *users_history*. Отчёт покажет когда абоненту был назначен IP-адрес, а также были ли измененяия по абоненту пока он владел этим адресом. Отчёт не показывает когда IP-адрес был снят с абонента. |
{code} select |
... |
where uf_ip2string(ip) = ':Введите ip-адрес$' |
order by time_changed |
{code} # h5. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс" |
... |
where U.SYSTEM_TYPE=7 and u.id is not null |
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$') |
and counters.MONTH_NUMBER=(':Месяц$') and counters.YEAR_NUMBER=(':Год$') |
group by counters.abonent_id, u.name, u.SUMMA union all |
... |
where U.SYSTEM_TYPE=7 and u.id is not null |
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$')) |
and counters.MONTH_NUMBER=(':Месяц$') and counters.YEAR_NUMBER=(':Год$')) |
{code} # h5. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 496. |
... |
{code} # h5. Отчет выводящий объёмы трафика за вбыранный период (месяца целиком) по абоненту |
{code}select |
select |
year_number ||'-'|| month_number as "Период", |
(SUM_BYTE_IN_M/1000000000.00) as "Входящий/гиг", (SUM_BYTE_OUT_M /1000000000.00) as "Исходящий/гиг" |
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "Входящий/Mб", round(SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2) as "Исходящий/Mб" |
from traf_counters |
... |
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date)) order by |
YEAR_NUMBER, MONTH_NUMBER{code} |
{code} |
{info}При настройке отчета [для использования в личном кабинете|CarbonBilling:Отчёты в личном кабинете], условие с ID абонента должно быть таким: {code}Abonent_ID = ':Abonent_ID$'{code}{info} |
... |
vl.src || ';' || vl.dst || ';' || |
'direction:' || coalesce(vl.direction_id,0) || ';' || |
substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' || substring(cast(vl.e_time as varchar(32)) from 1 for 19) || ';' || |
... |
order by a.id,vt.name{code} |
# h5. Отчёт для анализа настройки агентской схемы VoIP, подобный предыдущему, но отражает дополнительные данные и удалённые услуги {code} select distinct a.id "ID", a.name "Оператор", coalesce(u.name,'Нет услуги') "Услуга", 'UU_id=' || uu.id || ', del=' || uu.deleted || ', U_id=' || u.id "Метаданные", coalesce(vt.name,'') "Тип трафика", iif(uu.id is not null,voip_operator_match_format,'отсутствуют') "Транки" from abonents a join users_usluga uu on a.id=uu.abonent_id and voip_operator_match_format<>'' left join usluga u on uu.usluga_id=u.id left join v_type vt on u.voip_v_type_id=vt.id where a.category_id=2 and a.id>2 and uu.deleted=:2-Показывать удалённые?|choices[0^]Нет^[1^]Да]$ order by :1-Сортировка по:|choices[2^]Наименованию^[6^]Транкам^[5^]Типу трафика]$ {code} |
# h5. Отчёт по неплательщикам за месяц {code} |
... |
{code} |
# h5. Отчёт по задолженности абонента за месяц. Выводит баланс на 1 число месяца, платежи за месяц и расход. Необходимо выполнять отчёт с 1 по 31 число уже завершенного месяца. |
{code} select AB.CONTRACT_NUMBER as "Номер договора", |
... |
3 desc {code} |
# h5. Отчёт, показывающий абонентов с услугой, у которой вручную переопределена цена. {code} select a.name as "ФИО", a.contract_number as "Договор", round(uu.dinamyc_price / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Динамическая цена", (select usl.name from usluga usl left join users_usluga uu1 on uu1.usluga_id=usl.id where uu1.id=uu.id) as "Услуга" from users_usluga uu left join abonents a on a.id=uu.abonent_id where uu.dinamyc_price!=0 {code} # h5. Отчёт покажет количество аткуальных направлений по услугам подключенным абоненту, и их общее количество {code} select count(distinct cd.id), -- количество направлений в услуге count(distinct uu.id), -- скольким пользователям услуга подключена u.id, -- ID услуги u.name from usluga u join usluga_voip uv -- в таблице хранятся актуальные цены категорий on u.id=uv.usluga_id join category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим" on uv.voipcategory_id=cd.category_id join users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена on u.id=uu.usluga_id and uu.deleted=0 group by u.id, u.name order by 1 {code} # h5. Отчёт покажет количество аткуальных направлений по услугам {code} select count(distinct cd.id) "Кол. направлений", -- количество направлений в услуге cast(u.id as varchar(16)) "ID услуги", u.name "Имя" from usluga u join usluga_voip uv -- в таблице хранятся актуальные цены категорий on u.id=uv.usluga_id join category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим" on uv.voipcategory_id=cd.category_id join users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена on u.id=uu.usluga_id and uu.deleted=0 where uu.abonent_id = ':Абонент|select[Abonents,is_folder=0]$' group by u.id, u.name |
|
h2. Решение проблем |
union |
|
select count(distinct cd.id), -- количество направлений в услугах (суммарно) '', 'Итого' from usluga u join usluga_voip uv -- в таблице хранятся актуальные цены категорий on u.id=uv.usluga_id join category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим" on uv.voipcategory_id=cd.category_id join users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена on u.id=uu.usluga_id and uu.deleted=0 where uu.abonent_id = ':Абонент|select[Abonents,is_folder=0]$' order by 1 {code} # h5. Отчёт по абонентам заключивших договор за период Отчёт покажет абонентов заключивших договор в выбранном периоде, в воде будут номер договора, ФИО, дата договора, текущий тариф и текущий баланс. {code} select a.contract_number "Номер договора", a.name "Название/ФИО", cast(a.create_date as date) "Дата договора", t.name "Тариф", round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) "Текущий баланс" from abonents a join tarif t on a.tarif_id=t.id join admin_accounts aa on a.account_id=aa.id where extract(month from a.CREATE_DATE)=extract(month from cast(':Дата в виде месяца|monthchoice|create_date|select[abonents,id__gt=1000,deleted=0,parent_id__gt=1000,is_folder=0]$' as date)) and extract(year from a.CREATE_DATE)=extract(year from cast(':Дата в виде месяца|monthchoice|create_date|select[abonents,id__gt=1000,deleted=0,parent_id__gt=1000,is_folder=0]$' as date)) and a.id>=1000 and a.deleted=0 and a.parent_id>=1000 and a.is_folder=0 {code} # h5. Отчёт по начислениям по видам услуг за выбранный период Отчёт покажет начисления по видам услуг за выбранный период В case можно добавить собственную логику постороения отчёта в зависимости от натсроек услуг; в примере ниже: #* услуги с типом стандартный будут посчитаны как "разовые" или "прочие" в зависимости от типа списания #* все услути касающиеся интернет-трафика (форсаж, пакет МБ и тд) будут отражены просто как "трафик" {code} select c.year_number*100+c.month_number "Период", case when ut.id=0 and u.usluga_abon_type_id=1 then 'Разовые услуги' --'Стандартный' when ut.id=0 and u.usluga_abon_type_id<>1 then 'Прочие услуги' --'Стандартный' when ut.id=1 then 'Интернет' --'Турбокнопка' when ut.id=3 then 'Интернет' --'Бонусный трафик' when ut.id=4 then 'Интернет' --'Форсаж' when ut.id=2 then 'Прочие услуги' --'Подписка' when ut.id=6 then 'Интернет' --'Пакет МБ' when ut.id=5 then 'IP телефония' when ut.id=7 then 'IP телевидение' when ut.id=8 then 'Абонентская плата' when ut.id=9 then 'Интернет' --'Трафик' when ut.id=10 then 'Скидка\Наценка' when ut.id=11 then 'Обещанный платеж' when ut.id=12 then 'Пакет услуг' when ut.id=13 then 'Системные услуги' when ut.id=14 then 'Прочие услуги' --'Счетчик' end "Тип услуг", round(sum(c.summ),2) "Сумма" from counters c join usluga u on c.usluga_id=u.id join usluga_type ut on u.system_type=ut.id where c.summ<>0 and cast(c.year_number || '-' || c.month_number || '-01' as date) between cast(':1-С периода|monthchoice|6$' as date) and cast(':2-По период|monthchoice|bill_date|select[ArchAccountStack,storno=0]$' as date) group by 1,2 order by :3-Сортировать по|choices[1,2^]Месяцу^[2,1^]Типу услуг]$ {code} # h5. Отчёт по отправленным сообщениям из аудита {code} select a.name as "ФИО", a.contract_number as "Договор", ao.descr as "Описание", ao.OP_TIME as "Дата" from audit_operations ao left join abonents a on a.id=ao.abonent_id where ao.descr like '%Сооб%' and ao.abonent_id is not null and ao.OP_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59' {code} # h5. Вывод абонентов у кого подключен автоплатеж {code} select a.id, a.contract_number as " номер договора", a.name as "ФИО", iif(a.autopay_pay_log_id is not null, 'Включен', ' ') as "автоплатеж" from abonents a where a.is_folder=0 and a.deleted=0 and a.parent_id not in (2,4,244) and a.autopay_pay_log_id is not null {code} # h5. Анализ проводок по абонентам и выявление большого количества записей у абонентов(более 500 записей в месяц, это значение можно менять для отсортировки результатов) в таблице ARCH_ACCOUNT_STACK. {code} select ABONENT_ID, extract(year from CHANGE_BALANCE_TIME), extract(month from CHANGE_BALANCE_TIME), count(*) from ARCH_ACCOUNT_STACK group by 1,2,3 having count(*)>500 {code} # h5. Список задач в HelpDesk и CRM для выгрузки и обработки средствами Excel {code} select HDSK.id "#", coalesce(ht.NAME,'Не указан') as "Типы заявок", HDSK.SUBJ as "Тема", a.name "Абонент", coalesce(a_tel.attribute_value,'') "Телефон", t.name "Тариф", coalesce(h.street,'') || ' ' || coalesce(h.s_number,'') || ' п. ' || coalesce(cast(a.HOME_ENTRANCE as varchar(16)),'') || ' кв. ' || coalesce(cast(a.a_HOME_number as varchar(16)),'') "Адрес", lpad(coalesce(cast(hdsk.HDSK_DATETIME as varchar(32)),''),19) "Дата", hs.NAME as "Статус", lpad(coalesce(cast(hdsk.CLOSED_WHEN as varchar(32)),''),19) "Когда исполнено", hdsk.text "Описание заявки" from HDSK left join HDSK_STATUS hs on hdsk.STATUS=hs.id left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID left join abonents a_adm on a_adm.id=hdsk.PERFORMED_WHOM left join auth_user au on au.USERNAME=a_adm.name left join abonents a on a.id=hdsk.abonent_id left join homes h on a.home_id=h.id left join tarif t on a.home_id=t.id left join attribute_values a_tel on a.id=a_tel.abonent_id and attribute_id=1158 where coalesce(hdsk.IS_TASK,0)<>:Заявки HelpDesk или задачи?|choices[2^]Все^[1^]Заявки HelpDesk^[0^]Задачи]$ and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$ 23:59:59') {code} Тот же отчёт, но в поле "Телефон" перечисленны выданные абоненту телефонные номера {code} select HDSK.id "#", coalesce(ht.NAME,'Не указан') as "Типы заявок", HDSK.SUBJ as "Тема", a.name "Абонент", coalesce(phones_list,'') "Телефон", t.name "Тариф", coalesce(h.street,'') || ' ' || coalesce(h.s_number,'') || ' п. ' || coalesce(cast(a.HOME_ENTRANCE as varchar(16)),'') || ' кв. ' || coalesce(cast(a.a_HOME_number as varchar(16)),'') "Адрес", lpad(coalesce(cast(hdsk.HDSK_DATETIME as varchar(32)),''),19) "Дата", hs.NAME as "Статус", lpad(coalesce(cast(hdsk.CLOSED_WHEN as varchar(32)),''),19) "Когда исполнено", hdsk.text "Описание заявки" from HDSK left join HDSK_STATUS hs on hdsk.STATUS=hs.id left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID left join abonents a_adm on a_adm.id=hdsk.PERFORMED_WHOM left join auth_user au on au.USERNAME=a_adm.name left join abonents a on a.id=hdsk.abonent_id left join homes h on a.home_id=h.id left join tarif t on a.home_id=t.id left join (select list(ppc.phone) phones_list, u.abonent_id from users u join phone_pull_chache ppc on u.phone=ppc.id where u.deleted=0 and u.phone is not null group by u.abonent_id ) phones on a.id=phones.abonent_id where coalesce(hdsk.IS_TASK,0)<>:Заявки HelpDesk или задачи?|choices[2^]Все^[1^]Заявки HelpDesk^[0^]Задачи]$ and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$ 23:59:59') {code} # h5. Потребленный трафик за год с разбивкой по месяцам и возможностью выбора группы абонентов. {code} select distinct c.month_number as "месяц", inp.sum_in as "Входящий", outp.sum_out as "Исходящий" from counters c left join (select cast(sum(v) AS numeric(18,2)) sum_in, month_number mont from counters WHERE YEAR_NUMBER = (':Год|choices[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') AND UNIT_ID = 2 AND V_TYPE_ID = 1 and abonent_id in (SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(':Группа|select[Abonents,is_folder=1]$')) group by mont) inp on c.month_number=inp.mont left join (select cast(sum(v) AS numeric(18,2)) sum_out, month_number mont from counters WHERE YEAR_NUMBER = (':Год|choices[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') AND UNIT_ID = 2 AND V_TYPE_ID = 2 and abonent_id in (SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(':Группа|select[Abonents,is_folder=1]$')) group by mont) outp on c.month_number=outp.mont where c.YEAR_NUMBER = (':Год|choices[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') {code} # h5. Юрлица без ИНН Можно ограничить выборку только выбранной папкой, если все юрлица находятся в ней - в функции GLN_RECURSIVE_ABONENTS_GET укажите ID нужной папки. Для доп. соглашений учитывается ИНН основного договора. {code} select a.id, a.name, a.contract_number from abonents a join (SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1)) as jur_abon_list on a.id=jur_abon_list.abonent_id left join ATTRIBUTE_VALUES as av_inn_abon on a.id=av_inn_abon.ABONENT_ID and av_inn_abon.ATTRIBUTE_ID=4 and coalesce(av_inn_abon.ATTRIBUTE_VALUE,'')<>'' --ИНН left join ATTRIBUTE_VALUES as av_inn_master on a.master_id=av_inn_master.ABONENT_ID and av_inn_master.ATTRIBUTE_ID=4 and coalesce(av_inn_master.ATTRIBUTE_VALUE,'')<>'' --ИНН основного договора, если это доп. соглашение where a.company=1 and av_inn_abon.id is null and av_inn_master.id is null {code} # h5. Отчет формирует информацию за период по следующим полям: входящий баланс абонента, списания за указанный месяц, оплачено, исходящий баланс, НДС 20% от списаний. Если нет необходимости разделять на типы услуг, то можно убрать поля и скорректировать нахождение "Исходящего остатка" {code} select distinct a.contract_number as "№ договора", (select first 1 login from users where users.abonent_id=a.id) as "Логин", a.name as "Название/ФИО", iif(a.company=1, 'Юр. лица','Физ. лица') as "Физ./Юр.", coalesce(tel.attribute_value,'') as "Телефон", a.account_id as "№ лиц.сч", /*Выполянется проверка, какие данные выводить по входящему балансу, в зависимости от даты создания абонента*/ case when a.create_date_system between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) then (select first 1 fost.aa_ostatok/10000000000.00 from finance_operations fost where fost.storno = 0 and fost.abonent_id = a.id and (extract(year from fost.period_end_date) = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and extract(month from fost.period_end_date) = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) order by fost.system_date) when coalesce(foincome2.summa, 0) != 0 then foincome2.summa else round((coalesce(foincome.balance_buh / 10000000000.00, 0.00)), 2) end as "Входящий остаток", round(coalesce(onetime.summa, 0.00), 2) as "Разовые услуги", round(coalesce(constserv.summa, 0.00), 2) as "Периодические услуги", round(coalesce(voiptraf.summa, 0.00),2) as "IP-телефония", iif(a.company=1, round((coalesce(onetime.summa, 0.00) + coalesce(constserv.summa, 0.00) + coalesce(voiptraf.summa, 0.00))/100*20,2),'0.00') as "НДС", round(coalesce(onetime.summa, 0.00) + coalesce(constserv.summa, 0.00) + coalesce(voiptraf.summa, 0.00),2) as "Сумма с налогами", coalesce(debet.summa, 0.00) as "Оплачено", round((coalesce(foincome2.summa, 0.00)) + coalesce(debet.summa, 0.00) - coalesce(onetime.summa, 0.00) - coalesce(constserv.summa, 0.00) - coalesce(voiptraf.summa, 0.00), 2) as "Исходящий остаток" from abonents a join tarif t on a.tarif_id=t.id left join finance_operations foincome on foincome.storno = 0 and a.id = foincome.abonent_id and foincome.op_type = 1 and dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) = cast(foincome.period_end_date as date) /*получаем бух.баланс из акта за предыдущий месяц от искомого*/ left join (select round(fo.balance_buh / 10000000000.00, 2) summa, fo.abonent_id abon from finance_operations fo where fo.storno = 0 and fo.op_type = 1 and ( extract(year from fo.period_end_date) = extract(year from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) and extract(month from fo.period_end_date) = extract(month from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) )) foincome2 on a.id = foincome2.abon /*получаем сумму приходов за искомый месяц*/ left join (select round(sum(fo.op_summa / 10000000000.00), 2) summa, fo.abonent_id abon from finance_operations fo where fo.storno = 0 and fo.op_type = 2 and fo.op_date between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and dateadd(-1 second to dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as timestamp))) group by fo.abonent_id) debet on a.id = debet.abon /*сумма начислений за искомый месяц по разовым услугам*/ left join (select sum(c.summ) summa, c.abonent_id abon from counters c join usluga u on c.usluga_id = u.id where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and c.year_number = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and u.usluga_abon_type_id=1 group by c.abonent_id) onetime on a.id = onetime.abon /*сумма начислений за искомый месяц по периодическим услугам*/ left join (select sum(c.summ) summa, c.abonent_id abon from counters c join usluga u on c.usluga_id = u.id where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and c.year_number = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and u.usluga_abon_type_id<>1 and c.unit_id<>3 group by c.abonent_id) constserv on a.id = constserv.abon /*сумма начислений за искомый месяц по телефонии*/ left join (select sum(c.summ) summa, c.abonent_id abon from counters c join usluga u on c.usluga_id = u.id where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and c.year_number = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and u.usluga_abon_type_id<>1 and c.unit_id=3 group by c.abonent_id) voiptraf on a.id=voiptraf.abon left join attribute_values tel on a.id=tel.abonent_id and tel.attribute_id=1009 where a.is_folder=0 and coalesce(a.company,0)<>:1-Физ./Юр. лица|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$ and a.deleted = 0 and a.id > 0 and a.parent_id not in (2,244) /*Исключить каталог "Операторы" и "Служебная группа"*/ order by 1 {code} h5. Упрощенный вариант "Входящий баланс" - "Оплачено" - "Списано" - "Исходящий баланс" {code} select distinct a.contract_number as "№ договора", (select first 1 login from users where users.abonent_id=a.id) as "Логин", a.name as "Название/ФИО", iif(a.company=1, 'Юр. лица','Физ. лица') as "Физ./Юр.", /*Выполянется проверка, какие данные выводить по входящему балансу, в зависимости от даты создания абонента*/ case when a.create_date_system between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) then (select first 1 fost.aa_ostatok/10000000000.00 from finance_operations fost where fost.storno = 0 and fost.abonent_id = a.id and (extract(year from fost.period_end_date) = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and extract(month from fost.period_end_date) = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) order by fost.system_date) when coalesce(foincome2.summa, 0) != 0 then foincome2.summa else round((coalesce(foincome.balance_buh / 10000000000.00, 0.00)), 2) end as "Входящий остаток", coalesce(debet.summa, 0.00) as "Оплачено", round(coalesce(onetime.summa, 0.00), 2) as "Начислено", round((coalesce(foincome2.summa, 0.00)) + coalesce(debet.summa, 0.00) - coalesce(onetime.summa, 0.00), 2) as "Исходящий остаток" from abonents a join tarif t on a.tarif_id=t.id left join finance_operations foincome on foincome.storno = 0 and a.id = foincome.abonent_id and foincome.op_type = 1 and dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) = cast(foincome.period_end_date as date) /*получаем бух.баланс из акта за предыдущий месяц от искомого*/ left join (select round(fo.balance_buh / 10000000000.00, 2) summa, fo.abonent_id abon from finance_operations fo where fo.storno = 0 and fo.op_type = 1 and ( extract(year from fo.period_end_date) = extract(year from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) and extract(month from fo.period_end_date) = extract(month from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) )) foincome2 on a.id = foincome2.abon /*получаем сумму приходов за искомый месяц*/ left join (select round(sum(fo.op_summa / 10000000000.00), 2) summa, fo.abonent_id abon from finance_operations fo where fo.storno = 0 and fo.op_type = 2 and fo.op_date between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and dateadd(-1 second to dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as timestamp))) group by fo.abonent_id) debet on a.id = debet.abon /*cумма списаний за искомый месяц*/ left join (select sum(c.summ) summa, c.abonent_id abon from counters c join usluga u on c.usluga_id = u.id where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and c.year_number = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) group by c.abonent_id ) onetime on a.id = onetime.abon where a.is_folder=0 and a.deleted = 0 and a.id > 0 and a.parent_id not in (2, 244) {code} # h5. Отчет, выводящий номер договора, ФИО, адрес, баланс, линейку услуг с возможностью выбора линейки услуг {code} select a.contract_number as "Номер договора", a.name as "ФИО", (h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес", (aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс", urt.name as "Линейка услуг" from abonents a left join admin_accounts aa on aa.id = a.account_id left join homes h on h.id = a.home_id join tarif t on t.id=a.tarif_id left join USLUGA_RANGE_TYPES urt on t.USLUGA_RANGE_TYPE_ID=urt.id where a.deleted=0 and a.is_folder=0 and a.parent_id not in (2,244) and urt.id = ':Линейка услуг|select[UslugaRangeTypes]$' {code} # h5. Отчет по абонентам у которых не был выставлен акт за период. {code}select a.id from abonents a where a.is_folder=0 and a.deleted = 0 and a.id > 0 and a.parent_id not in (2, 244) and a.id not in (select abonent_id aid from finance_operations fost where fost.storno = 0 and fost.op_type = 1 and (extract(year from fost.period_end_date) = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and extract(month from fost.period_end_date) = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) order by fost.op_date) {code} # h5. Отчет по radius-атрибутам NAS. {code} select n.name as "NAS", nrp.ATTRIBUTE as "Атрибут", nrp.THEVALUE as "Значение", case when nrp.BALANCE_STATUS_ID=0 then 'В любом случае' when nrp.BALANCE_STATUS_ID=1 then 'При отрицательном балансе' when nrp.BALANCE_STATUS_ID=2 then 'При положительном балансе' else '' end as "Статус баланса", case when nrp.BLOCK_STATUS_ID=0 then 'В любом случае' when nrp.BLOCK_STATUS_ID=1 then 'Заблокирован' when nrp.BLOCK_STATUS_ID=2 then 'Не заблокирован' else '' end as "Статус блокировки" from nas n join NAS_RADIUS_PARAMS nrp on nrp.nas_id=n.id {code} # h5. Список абонентов, у которых есть учетная запись IPTV с выбором даты изменения услуг. {code} select distinct a.id,a.name, us.login --отбираем логин from abonents a join users us on us.abonent_id=a.id and us.nas_id=1126 left join users_usluga uu on a.id=uu.abonent_id join users_usluga_history uuh on uuh.abonent_id=a.id where a.id>0 and a.is_folder=0 and a. parent_id not in (2,244) and uuh.time_changed between (':1 Дата|date$') and (':2 Дата|date$') and uuh.time_del is null {code} # h5. Абоненты-номер договора-статус/подстатус абонента {code} select ab.name as "ФИО", ab.contract_number as "Номер договора", s.name as "Статус" from abonents ab left join objects_status os on os.object_id=ab.id left join status s on s.id = os.status where s.id = ':Статус|select[status]$' and extract (DAY from ab.activate_date) >0 and ab.id>0 and ab.deleted=0 {code} # h5. Список абонентов IPTV с выбором даты блокировки услуги. {code} select distinct a.id, a.name as "Имя абонента", us.id as "Номер услуги в справочнике", us.name as "Название услуги", abh.b_date as "Дата блокировки" from abonents a join users u on u.abonent_id=a.id left join abonents_block_history abh on abh.abonent_id=a.id left join users_usluga uu on abh.abonent_id=uu.abonent_id left join usluga us on us.id=uu.usluga_id where a.id>0 and a.deleted=0 and a.is_folder=0 and a.parent_id not in (2,244) and abh.b_date between (':1 Дата|date$') and (':2 Дата|date$') and abh.time_del is null and us.system_type=7 /*отобрать услуги IPTV по дате блокировки */ and u.nas_id=1111 /*номер NAS IPTV к которому относятся пользователи*/ order by a.id {code} # h5. Неудалённые абоненты интернет по типу авторизации {code} /* Учитываются только: - Неудалённые абоненты; - С адресом IPv4 */ select count(*) "Количество абонентов", case when u.auth_type is null then '---------' when u.auth_type=0 then 'по vpn pptp, pppoe' when u.auth_type=1 then 'по ip static' when u.auth_type=2 then 'по CarbonAgent' when u.auth_type=3 then 'по ip+CarbonAgent' when u.auth_type=4 then 'через веб' when u.auth_type=5 then 'через ip+веб' when u.auth_type=6 then 'любая через RADIUS' when u.auth_type=8 then 'по OPT82' when u.auth_type=9 then 'по ip+radius' end "Тип авторзации" from users u where u.id>0 and u.ip is not null and u.deleted=0 group by u.auth_type {code} # h5. Найти список абонентов, у которых дата начала добровольной блокировки была назначена в прошлом относительно даты включения. В отчете указываем период, в течение которого необходимо проверить события. {code} /*Данные в отчете будут представлены из таблицы Аудит*/ select distinct aid as "ID абонента", ana as "ФИО", opdate as "Дата события", dateblo as "Дата доб.блок." from (select a.id aid, a.name ana, cast(ao.op_time as date) opdate, ao.descr aodes, cast(substring(ao.descr from 69 for 10) as date) dateblo from audit_operations ao left join abonents a on ao.abonent_id=a.id where ao.op_time between ':1.Начало периода|date$' and ':2.Конец периода|date$' and a.id>0 and ao.descr like '%Добровольная блокировка c %' ) where opdate>dateblo{code} # h5. Отчет который выведет расход абонента за указанный период, с итогами по месяцам и по всей выборке {code} select c.YEAR_NUMBER as "Год", c.MONTH_NUMBER as "Месяц", u.name || ' (ID=' || u.id || ')' as "Услуга", round(round(round(sum(c.SUMM),4),3),2) as "Сумма", Null "Итого месяц", Null "Итого отчёт" from counters c join abonents a on c.abonent_id=a.id join usluga u on c.usluga_id=u.id where a.contract_number=':3-Номер договора$' and cast(c.YEAR_NUMBER || '-' || c.MONTH_NUMBER || '-01' as timestamp) between ':1-С какого периода|monthchoice|100$' and ':2-По какой период|monthchoice|100$' group by 1,2,3 union select c.YEAR_NUMBER as "Год", c.MONTH_NUMBER as "Месяц", ' Итого за ' || c.YEAR_NUMBER || '.' || c.MONTH_NUMBER, Null, round(round(round(sum(c.SUMM),4),3),2), Null from counters c join abonents a on c.abonent_id=a.id join usluga u on c.usluga_id=u.id where a.contract_number=':3-Номер договора$' and cast(c.YEAR_NUMBER || '-' || c.MONTH_NUMBER || '-01' as timestamp) between ':1-С какого периода|monthchoice|100$' and ':2-По какой период|monthchoice|100$' group by 1,2,3 union select 9999999999 as "Год", -- 9999999999 чтобы при выгрузке в Excel Год и Месяц в детализации оставались числами и "Итого" был последней строкой 9999999999 as "Месяц", 'Итого за весь период' as "Услуга", Null, Null, round(round(round(sum(c.SUMM),4),3),2) from counters c join abonents a on c.abonent_id=a.id join usluga u on c.usluga_id=u.id where a.contract_number=':3-Номер договора$' and cast(c.YEAR_NUMBER || '-' || c.MONTH_NUMBER || '-01' as timestamp) between ':1-С какого периода|monthchoice|100$' and ':2-По какой период|monthchoice|100$' group by 1,2,3 order by 1,2,3,4 {code} # h5. Отчёт покажет в какие тарифы входят услуги {code} /* Отчёт выведет все услуги, включённые в тарифы: - в левой колонке - услуга; - в правой - тарифы, в которые она входит, через запятую. */ select '(ID=' || usl.id || ')' || usl.name "Услуга", list('(ID=' || t.id || ')' || t.name) "Тарифы" from usluga usl join tarif_users_usluga tuu on usl.id=tuu.usluga_id join tarif t on tuu.tarif_id=t.id group by 1 {code} # h5. Отчет отбирает абонентов по каталогу и текущему статусу {code}select ab.name as "ФИО", ab.contract_number as "Номер договора", s.name as "Статус" from abonents ab left join objects_status os on os.object_id=ab.id left join status s on s.id = os.status where s.id = ':Статус|select[status]$' and extract (DAY from ab.activate_date) >0 and ab.id>0 and ab.deleted=0 and ab.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Абонент|select[abonents,is_folder=1]$' )){code} # h5. Общая статистика по звонкам с разделением на типы трафика и статус: обработанные и не обработанные {code} select calls.per_first_date "Период", sum(in_call) "Входящие", sum(out_call) "Исходящие", sum(tranzit_call) "Транзитные", sum(null_type) "Не определено", sum(not_billed) "Ждут обработку", sum(billed) "Обработаны", count(*) "Всего вызовов" from ( select case when billed=1 then 1 else 0 end billed, case when billed=0 then 1 else 0 end not_billed, case when v_type_id=0 then 1 else 0 end stdt, case when v_type_id=1 then 1 else 0 end in_call, case when v_type_id=2 then 1 else 0 end out_call, case when v_type_id=3 then 1 else 0 end tranzit_call, case when v_type_id is null then 1 else 0 end null_type, cast(s_time-extract(day from s_time)+1 as date) per_first_date from voip_log ) calls group by calls.per_first_date order by 1 {code} # h5. Отчет: выводить ответственного, кто включил опцию "Не отключать при превышении порога". {code} select distinct a.contract_number as "№ договора", a.NAME as "ФИО", aa.UNLIMITED as "Не отключать", coalesce(AU.USERNAME,'') as "Ответственный" from ADMIN_ACCOUNTS aa left join ABONENTS a on a.ACCOUNT_ID=aa.ID left join audit_operations ao on a.id=ao.abonent_id left join AUTH_USER as AU on au.ID=ao.admin_id where a.IS_FOLDER=0 and a.id>0 /* исключить служебные записи*/ and a.parent_id<> 2 /* Исключить каталог "Операторы"*/ and aa.UNLIMITED=1 and a.deleted=0 /* Выводить не удаленных абонентов*/ and ao.descr like '%Не отключать при превышении порога=True%' order by a.NAME {code} # h5. Номер договора абонента и номер договора главного абонента. {code} select a.contract_number as N_cont, a_master.contract_number as N_cont_main from abonents a left join abonents a_master on a.master_id=a_master.id {code} # h5. Отчёт для платёжной системы "Сбербанк" в формате "Номер договора;ФИО;Сумма предоплаты на следующий месяц" {code} -- Чтобы убрать заголовок. В начале файла будут две пустые строки. select first 1 '' from abonents union select distinct -- 1 поле - номер догововра; a.contract_number || ';' || -- 2 поле - ФИО; a.name || ';' || -- 3 поле - Сумма предоплыты на след месяц, при условии что на счету нет денег на след месяц. cast(ac.recomend_pay_sum_cache/10000000000.00 as varchar(32)) from abonents a join homes h on a.home_id=h.id join abonents_cache ac on a.id=ac.id where coalesce(a.company,0) = 0 {code} # h5. Отчёт для платёжной системы "Примбанк" (ПАО АКБ Приморье) в формате "Номер договора|телефон|ФИО|Сумма предоплаты на следующий месяц|Дата, последний день предыдущего месяца|Предыдущий месяц|Вид услуги|" Отчёт строется только для физлиц. В папке физлица абоненты разбиты так же по папкам: DSL, GPON и Смотрёшка. Если у абонента несколько разных услуг (например, Смотрёшка и GPON), в биллинге для него заводятся отдельные абоненты - один в папке "Смотрёшка", другой в папке GPON. Все абоненты находятся только в этих трёх папках, в них нет вложенных папок. {code} select -- 1 поле - № договора a.contract_number || '|' || -- 2 поле - телефон coalesce(a.sms,'') || '|' || -- 3 поле - ФИО a.name || '|' || -- 4 поле - сумма cast(ac.recomend_pay_sum_cache/10000000000.00 as varchar(32)) || '|' || -- 5 поле - дата, всегда последний день предыдущего месяца CAST(lpad(EXTRACT(DAY FROM ld.prev_month_last_date),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM ld.prev_month_last_date),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM ld.prev_month_last_date) || '|' || -- 6 поле - пред месяц EXTRACT(YEAR FROM ld.prev_month_last_date) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM ld.prev_month_last_date),2,'0') AS varchar(2)) || '|' || -- 7 Вид услуги: интернет, Смотрешка и телефон. case when a.parent_id=1369 then 'Интернет|' -- GPON when a.parent_id=1370 then 'Интернет|' -- DSL when a.parent_id=1593 then 'Смотрёшкака|' -- Смотрёшка else 'Услуги связи|' end from abonents a join homes h on a.home_id=h.id join abonents_cache ac on a.id=ac.id join ( SELECT 0 as fake_id, DATEADD ( -1 day TO DATEADD (1 MONTH TO CAST(EXTRACT(YEAR FROM CURRENT_DATE)||'-'||EXTRACT(MONTH FROM CURRENT_DATE)||'-01' AS DATE)) ) as last_date, dateadd ( -1 month to DATEADD (-1 day TO DATEADD (1 MONTH TO CAST(EXTRACT(YEAR FROM CURRENT_DATE)||'-'||EXTRACT(MONTH FROM CURRENT_DATE)||'-01' AS DATE))) ) as prev_month_last_date FROM RDB$DATABASE ) ld on a.id>ld.fake_id join -- Берём абонентов только из папки для физиков (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(1358)) g on g.group_id = a.parent_id where coalesce(a.company,0) = 0 {code} # h5. Отчёт по использованию услуги социального интернета {code} select -- 1. Точное время не записывается в users_usluga, сохраняется только дата, время всегда 00:00:00. -- Можно было бы считать от TIME_DEL, отняв 24 часа, но если услугу отключат раньше, что возможно, -- То дата подключения будет неверной. -- 2. MAX, т.к. если в истории несколько записей (а их должно быть как минимум две - вкл. и выкл.) -- реальное время будет только в первой, в остальных - "00:00:00" max(uu_create.create_date) "Дата подключения", -- Нельзя конвертировать в varchar, т.к. если будут выгружать в Excel, вместо даты будет просто текст -- Это хуже, чем 'Null' при выполнении из интерфейса max(uu_del.time_del) "Дата отключения", uu.id "ID подкл. услуги", a.contract_number "Договор", a.name "ФИО" from users_usluga uu join usluga u on uu.usluga_id = u.id -- У услуг соц. интернета отдельный системный тип ID=15 and u.system_type=15 join users_usluga_history uu_create on uu.id=uu_create.users_usluga_id and uu_create.time_del is null left join users_usluga_history uu_del on uu.id=uu_del.users_usluga_id and uu_del.time_del is not null join abonents a on uu.abonent_id=a.id group by -- 3, 4, 5, 6 3, 4, 5 order by 1 :Сортировка по дате подключения|choices[desc^]Сначала новые^[asc^]Сначала старые]$ {code} # h5. Отчёт по количеству абонентов с доп. соглашениями Отчёт выведет статистику по абонентам и доп. соглашениям. После статистики будет топ. абонентов по количеству доп. соглашений, по-умолчанию 10, но в форме можно выбрать вывести всех. {code} select 'Суммарно', count(*) "Количество" from abonents where is_folder=0 and id>0 and deleted=0 union all select 'Доп.соглашений', count(*) "Количество" from abonents where is_folder=0 and id>0 and deleted=0 and master_id is not null union all select 'Абонентов', count(*) "Количество" from abonents where is_folder=0 and id>0 and deleted=0 and master_id is null union all select 'Абонентов с доп.соглашениями', count(distinct master_id) "Количество" from abonents where is_folder=0 and id>0 and deleted=0 and master_id is not null union all select first 1 '-----------------------', Null from vpn_const union all select first 1 'ТОП 10', Null from vpn_const union all select first 1 '-----------------------', Null from vpn_const union all select first :Сколько вывести абонентов с допниками|choices[10^]Топ 10^[1500000^]Все]$ * -- first 10 * from ( select 'Основной договор: ' || a.contract_number || ', Название-ФИО: ' || a.name contr_name, count(*) cnt from abonents dopnik join abonents a on dopnik.master_id=a.id where dopnik.is_folder=0 and dopnik.id>0 and dopnik.deleted=0 and dopnik.master_id is not null group by a.contract_number, a.name order by 2 desc ) abon where abon.cnt>=1 {code} # h5. Отчёт, выводящий номер договора абонента, название услуги и персональную стоимость услуги для тех абонентов, у которых есть услуги с персональной стоимостью {code} select a.contract_number as "Договор", u.name as "Услуга", round(uu.DINAMYC_PRICE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена" from abonents a join users_usluga uu on a.id=uu.abonent_id join usluga u on u.id=uu.usluga_id where a.is_folder=0 and a.deleted=0 and uu.deleted = 0 and coalesce(uu.DINAMYC_PRICE,0)!=0 {code} # h5. Отчет, выводящий порог отключения абонента, для тех абонентв, у кого порог отключения не равен 0. {code} select a.ID as "ID", a.NAME as "ФИО", aa.LIMIT as "Порог отключения" from ABONENTS a left join ADMIN_ACCOUNTS aa on a.ACCOUNT_ID=aa.ID where a.IS_FOLDER=0 and a.deleted= 0 and aa.LIMIT != 0 {code} # h5. Отчет ID-Название услуги-Тип услуги-Вид сервиса-В архиве(или нет) {code}select u.ID, u.NAME as "Название", ut.name as "Тип", st.name as "Вид сервиса", archived as "В архиве" from usluga u join usluga_type ut on u.system_type=ut.id join service_type st on u.service_type_id=st.id{code} # h5. Отчет по объемам потребленного трафика абонентом. Будет работать лишь после включения опции *["Сохранять движения всего трафика"|https://docs.carbonsoft.ru/pages/viewpage.action?pageId=63242421#Глобальныенастройкибиллингаиоператора-Глобальныенастройкиоператора]* {code} select t1.trafab as "ID абонента", t1.aname1 as "ФИО", t1.ttime as "Дата", t1.uname as "Услуга", t1.inn as "Входящий", t2.out as "Исходящий" from (select distinct aas.abonent_id as trafab, a.name as aname1, cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as ttime, u.name as uname, iif(aas.V_TYPE_ID=1, cast(round(sum(aas.v),2) as varchar(32)),'') as INN from arch_account_stack aas --Трафик считаем по списаниям left join usluga u on aas.USLUGA_ID = u.id --Из услуги берём название left join abonents a on a.id=aas.abonent_id where aas.CHANGE_BALANCE_TIME between ':1.C_даты|date$' and ':2.По_дату|date$' --Используем типы 1 . Входящий and aas.V_TYPE_ID=1 and a.id = ':ФИО|select[Abonents,is_folder=0]$' and aas.storno=0 group by 1,2,3,4,aas.V_TYPE_ID) t1 left join (select distinct aas.abonent_id as trafab, a.id as aname2, cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as ttime, u.name as uname, iif(aas.V_TYPE_ID=2, cast(round(sum(aas.v),2) as varchar(32)),'') as OUT from arch_account_stack aas --Трафик считаем по списаниям left join usluga u on aas.USLUGA_ID = u.id --Из услуги берём название left join abonents a on a.id=aas.abonent_id where aas.CHANGE_BALANCE_TIME between ':1.C_даты|date$' and ':2.По_дату|date$' --Используем типы 2. Исходящий and aas.V_TYPE_ID=2 and a.id = ':ФИО|select[Abonents,is_folder=0]$' and aas.storno=0 group by 1,2,3,4,aas.V_TYPE_ID)t2 on t2.ttime=t1.ttime {code} # h5. Отчет, выводящий суммарный объем входящего и исходящего трафика с начала года по выбранный квартал {code} select distinct 'Объём информации, переданной от/к абонентам, всего, Гб' as "Наименование", sum(round(tc.SUM_BYTE_OUT_M/cast((1073741824) as numeric(18,5)), 2)) + sum(round(tc.SUM_BYTE_IN_M/cast((1073741824) as numeric(18,5)), 2)) as "Величина" from traf_counters tc join (select distinct month_number month_n, case when month_number in (1,2,3) then 1 when month_number in (4,5,6) then 2 when month_number in (7,8,9) then 3 when month_number in (10,11,12) then 4 end kvartal from traf_counters) kv_m on tc.month_number = kv_m.month_n where tc.YEAR_NUMBER = (':Год|choices[2023^]2023^[2022^]2022^[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and kv_m.kvartal <= (':Квартал|choices[1^]1^[2^]2^[3^]3^[4^]4]$') {code} # h5. Отчет, выводящий абонентов с услугами IPTV на определённом NAS. Отчёт выводит порядковый номер, логин, ФИО, название услуги IPTV, цену и списание по услуге за выбранный период {code} SELECT coalesce(rownum,1)+0*x AS "№", login as "Логин", fio as "ФИО", usluga as "Услуга", price as "Цена", credit as "Расход" FROM( SELECT CAST(RDB$GET_CONTEXT('USER_TRANSACTION','ROW')AS INT) rownum, null login, null fio, null usluga, null price, null credit, NULL x FROM rdb$database UNION ALL SELECT (CAST(rdb$get_context('USER_TRANSACTION','row') AS INT)+1) AS rownum, us.login as login, a.name as fio, u.name as "Услуга", round(u.summa / 10000000000.00, 2) as price, round(c.summ,2) as credit, rdb$set_context('USER_TRANSACTION','row', CAST(COALESCE(rdb$get_context('USER_TRANSACTION','row'),0) AS INTEGER)+1) AS x from abonents a join counters c on a.id=c.abonent_id join usluga u on c.usluga_id=u.id left join users us on a.id = us.abonent_id where u.system_type=7 and c.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and c.year_number = (':Год|choices[2025^]2025^[2024^]2024^[2023^]2023^[2022^]2022^[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and us.nas_id=25 and u.summa != 0 ) t WHERE login IS NOT NULL {code} # h5. Отчет выводит абонентов у которых после блокировки по балансу прошло более 2-ух дней, ислючает корзину, служебнуб группу и так же некоторые тарифы. {code} select distinct a.id, a.name, A.CONTRACT_NUMBER as "Договор", T.name as "Тариф", round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс", DATEDIFF(day,ab.B_DATE,current_timestamp) as "Дней блокировки" from ABONENTS A left join USERS U on A.ID = U.ABONENT_ID left join TARIF T on A.TARIF_ID=T.ID left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID left join users_usluga uu on uu.ABONENT_ID=A.ID left join usluga usl on usl.id=uu.USLUGA_ID left join abonents_block ab on ab.abonent_id=a.id where A.IS_FOLDER=0 and a.parent_id not in ( '244', '2', '4' ) and a.deleted=0 and ab.B_NEGBAL=1 and uu.deleted=0 and a.TARIF_ID not in (1175,1186,1188) and DATEDIFF(day,ab.B_DATE,current_timestamp) > '2' order by 2 {code} h1. Решение проблем |
Решение проблем с отчетами и инструкция по методике отладки описаны в статье "[CarbonBilling:Отладка отчетов]" |