|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Изменения (293)
просмотр истории страницы{toc:maxLevel=32} |
|
h21. Описание конструктора отчетов |
|
*Конструктор отчетов* позволяет делать запросы напрямую к БД. Описание ограничений, возможностей и синтаксиса используемого диалекта SQL Вы можете найти в документации Firebird 2.15. |
Конструктор отчетов находится на вкладке "Отчеты" в основном меню биллинга \\ \\ |
!report_builder_1.png|border=1,width=1000! \\ \\ |
\\ \\ !report_builder_1.png|border=1,width=1000! \\ \\ |
Отчеты разбиваются на группы. Не относящиеся ни к одной попадают в группу "Не определено". Группы настраиваются в справочниках: \\ \\ |
!report_builder_1_types.png|border=1,width=1000! \\ \\ |
\\ \\ !report_builder_1_types.png|border=1,width=1000! \\ \\ |
По-умолчанию в биллинге идет более 60 отчетов. Вы так же можете создать свои или воспользоваться одним из примеров из данной статьи. Для добавления отчета нажмите кнопку "Добавить" в конструкторе. Ксли требуется выберите категорию, В поле "SQL запрос" вставьте текст отчета и сохраните. \\ \\ |
\\ |
\\ !report_builder_2.png|border=1! |
Результат выполнения отчета можно отобразить браузере или выгрузить: * В формате DBF |
* В CSV с разделением ";" (точка с запятой) "," (запятая) |
* В Excel (xlsx) |
|
Для выполнения отчета выберите формат отображения и нажмите кнопку "Выполнить запрос"\\ \\ |
\\ |
\\ !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 ':Начало|date$' and ':Конец|date$'{code} ** choices - список |
Прмер: {code}BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'{code} 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. Выполнение отчёта из консоли |
h3. Строка для ввода произвольных данных: |
|
Отсутстие параметров предложет ввести произвольные данные. {code} ':Введите число$' {code} h2. Выполнение отчёта из консоли скриптом make_reports.pyc |
Для того, чтобы запустить выполнение отчета из консоли, необходимо: |
1) # Перейти в контейнер биллинга |
{code}chroot /app/asr_billing/{code} # Запустить скрипт {code}python2.7 /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105{code} Где 105 - id отчёта. По-умолчанибю информация, выведенная на экран, будет иметь кодировку Windows (windows-1251, cp1251) У скрипта есть несколько опций и пресетов для выгрузок в [СОРМ3|Интеграция с СОРМ3] h3. Дополнительные опции запуска || Опция || Полный формат || Пример || Описание || | -c CHARSET | --charset=CHARSET | -c utf8 | Кодировка вывода, по-умолчанию: cp1251 | -p PRESET | --preset=PRESET| -p norsi-trans | Использовать пресет настроек для конкретного СОРМ Доступные пресеты: mfi-soft, vasexperts, norsi-trans, signaltec | | -s SEPARATOR | --separator=SEPARATOR | -s "\|" | Символ разделителя полей, по-умолчанию: точка с запятой ";"| | -f | --safe-separator| \- | Удалять символ разделителя из содержимого полей. Если задан этот флаг, то в каждом поле отчета символ разделителя (из опции --separator) будет заменяться на пробел. Это позволяет гарантировать, что разделитель полей встречается только между полями, но не в самих полях. | | -q QUOTE | --quote=QUOTE | -q \" | Символ экранирования полей, по-умолчанию: нет Чтобы задать двойную кавычку в bash её нужно экранировать: make_reports.pyc --quote=\" Все остальные символы можно передать в двойных кавычках, например: make_reports.pyc --quote="'" | | -b | --double-quote | \- | Дублировать символ экранирования, если он встречается внутри поля | | -w | --wrap | \- | Экранировать только поля, внутри которых встречаются символ разделителя или символ экранирования | h3. Пример запуска make_reports с опциями * Выгрузить отчет #105 в формате, пригодном для [СОРМ от компании "Норси-Транс"|СОРМ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 \" -w{code} * Выгрузить отчет #105, экранируя все поля и удаляя символ разделителя из полей: {code}python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -s ";" -q \" -f{code} h2. Выполнение отчёта из консоли утилитой sqlexec Запрос к базе данных можно выполнить передав его утилите sqlexec, например так: {code:title=Команда}sqlexec "select count(*) from abonents"{code} {code:title=Вывод} COUNT ============ 14 |
{code} |
chroot /app/asr_billing/ |
Запрос можно написать в несколько строк: {code:title=Команда}sqlexec "select count(*) from abonents"{code} Это удобно, если запрос сложный и объёмный. Вывод будет таким же, как у команды выше. h2. Выполнение запроса для использования в скриптах Тут возможны два варианта. h3. С помощью sqlexec Если отчёт достаточно простой, его можно выполнить с помощью sqlexec. Но нужно учитвать несколько моментов: * На самом деле утилита работает в контейнере asr_billing * Вывод лучше отформатировать для удобства написания скриптов и сохранения данных в переменные * Частично отформатировать Вывод можно средствами SQL: ** либо "*set heading off;*" - уберёт заголовки, это удобно если Вы выгружаете, например, список каких-то позиций (абонентов, финансовых операций) для обработки каждой в отдельности ** либо "*set list on;*" - "перевернёт" данные, отразит их в виде колонок вместо таблицы Ниже приведён скрипт, в котором показано как работать с выводом в том или ином виде, включая одиночные значения и списки: |
{code} |
2) Запустить скрипт |
#!/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} |
python /usr/lib/python2.6/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 |
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} |
Где 105 - id отчёта. Информация, выведенная на экран, будет иметь кодировку cp1251 |
Тогда запрос к БД можно выплнить такой командой: {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} Вывод отчёта Вы так же можете форматировать командными утилитами ОС, сохранять в переменные или передавать в циклы для дальнейшей обработки. |
|
h32. Описание полей |
К большенсту полей БД существуют описания. Описание всех полей можно получить выполнив команду *sqlexeс "show comments"*, например: |
... |
COMMENT ON COLUMN TARIF.PROMISE_PAY_ONLY_THIS_MONTH IS Разрешать подключение обещанного платежа только один раз в текущий месяц;{code} |
h2. Примеры # h5. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона |
h2. Особенности SQL в Firebird h3. UNION (UNION ALL) и ORDER BY h6. В Firebird Order By должен быть последним оператором, сортируя всю выборку, например: {code} select col1, col2, col3 from table1 union all select '', 'Итого', sum(col3) from table1 order by 1{code} h6. Как поставить "Итого" в конец строки В примере выше вторая часть запроса выбирает итого по первой, при этом "Итого" окажется первой строкой выборки так как при сортировке пустая строка "выше" любого символа. Чтобы "Итого" переместить в конец списка, можно использовать "невидимый" символ Юникода подобный пробелу, но стоящий в Юникоде ниже всех Кириллицы: {code}union all select ' '{code} Список символов можно посмотреть в [Википедии|https://en.wikipedia.org/wiki/Whitespace_character], в примере использован символ U+2003 "em space" h3. Особенность сортировки числовых значений В примере выше выборка будет отсортирована как текстовая. То есть порядок чисел будет: *1,10,100,2,20,200*. Что бы отсортировать выборку как числа нужно убрать строковые константы в полях и заменить их на значение *null*. Тогда результат будет такой: *1,2,10,20,100,200*. {code} select col1, col2, col3 from table1 union all select null, null, sum(col3) from table1 order by 3 |
{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 |
h3. Ограничения в наименовании столбцов отчета {info}В случае использования кириллицы в названии столбцов отчета важно знать, что поле может содержать не более 18 символов.{info} Если требуется указать более развернутое название - используйте латиницу. Иначе, будет возникать [ошибка|https://docs.carbonsoft.ru/pages/viewpage.action?pageId=155714114#Отладкаотчетов-Привыполненииотчетавозниклаошибка"Произошлаошибкапоадресу] при выполнении отчета. h2. Процедуры Carbon Soft Для работы с базой мы добавили ряд собственных процедур, которые помогут найти нужные данные и верно их отобразить в отчётах в соответствии с архитектурой биллинга. h3. Получение списка вложенных групп и абонентов в дереве групп h4. GLN_RECURSIVE_GROUP_WALK Принимает: GR_ID - ID группы Возвращает: GROUP_ID - ID запрошенной группы и всех её подгрупп h4. GLN_RECURSIVE_ABONENTS_GET Принимает: GROUP_ID - ID группы Возвращает: ABONENT_ID - ID абонентов в указанной группе и всех её подгруппах h4. Примеры запросов {code:title=ID групп} SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(1) |
{code} |
{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} |
... |
or AB.B_OWN != 1 or AB.B_ADMIN != 1 |
or AB.B_SYS != 1)) = 0 |
{code} # h5. Список IP адрес и номер договора всех абонентов. у которых в качестве nas_ip указан адрес 192.168.1.2: {code} |
select |
uf_ip2string(u.ip) as "IP", a.CONTRACT_NUMBER as "Номер договора" |
from users u |
left join abonents a on u.abonent_id=a.id left join nas n on u.nas_id=n.id |
... |
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 TP.ID=':Тариф|select[Tarif]$' order by U.CONTRACT_NUMBER{code} |
# h5. Отчет должники по постоплате с разделением по физическим и юридическим лицам. Отчет содержит: номер договора, ФИО, адрес, дата последнего платежа, сумма долга (рекоммендованный платеж по постоплате) |
{code:lang=sql}select first 1 cast('******************************************' as varchar(100) ) as "№ договора", |
... |
group by users.login, tarif.name {code} |
# h5. Отчёт по скачанному трафику за период (2 вариант). {code:lang=sql} select abonent_id as "ИД абонента", year_number ||'-'|| month_number as "Период", (SUM_BYTE_IN_M /1000000000.00) as "Входящий/гиг", (SUM_BYTE_OUT_M /1000000000.00) as "Исходящий/гиг" from traf_counters where MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date)) 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)) union all select 'Итого', '', sum(SUM_BYTE_IN_M /1000000000.00), sum(SUM_BYTE_OUT_M /1000000000.00) from traf_counters where MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date)) 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)) {code} |
# h5. Отчёт по платежам для групп абонентов. {code:lang=sql} |
... |
and a.parent_id=(':ID папки$') {code} |
# h5. Отчёт для платежной системы "Город", выводящий в строку "ФИО;Адрес;Лицевой счет;Текущий остаток;';;;;'" и выводящий в Шапке отчёта |
#* FILESUM - Сумму всех остатков в данном отчёте #* TYPE 7 |
... |
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} |
... |
from USERS where ((IP >= START_IP and IP <= END_IP) |
or (IP <= START_IP and IP >= END_IP)) |
or ((HOST_IP >= START_IP and HOST_IP <= END_IP) |
or (HOST_IP <= START_IP and HOST_IP >= END_IP)) |
and (select first 1 DELETED from ABONENTS |
... |
# h5. Отчёт по приходам за заданный период времени {code} |
select a.name as "ФИО", |
a.contract_number as "Договор", |
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода", |
AU.USERNAME as "Ответственный", |
fo.OP_DATE as "Дата прихода" |
from abonents as a |
left join finance_operations as fo |
on a.id=fo.abonent_id left join AUTH_USER as AU on AU.id=fo.owner_id |
where OP_TYPE=2 and |
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$') {code} |
... |
# h5. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период. {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} select AB.NAME as "ФИО", |
CONTRACT_NUMBER as "Договор", |
FO.NUMBER as "Номер акта", |
round(FO.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "СУММА", FT.OP_NAME || ', ' || FT.OP_DESCR as "ТИП ДОКУМЕНТА" |
... |
and FO.ABONENT_ID = AB.ID and AB.COMPANY = 1 |
and FO.OP_DATE between cast(':1. Начало|date$' as date) and cast(':2. Конец|date$' as date) |
order by 1 {code} |
... |
A.SMS as "Телефон", h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес", |
av1.attribute_value as "Р/С", |
av2.attribute_value as "ИНН", |
av3.attribute_value as "КПП" from abonents as a left join HOMES as H on H.ID=A.HOME_ID |
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=6 |
left join ATTRIBUTE_VALUES AV2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=4 left join ATTRIBUTE_VALUES AV3 on a.id=av3.ABONENT_ID and av3.ATTRIBUTE_ID=5 |
... |
# h5. Отчёт прибыль по плате за подключение нескольких услуг. {code} |
select |
a.NAME as "ФИО", uu.ABONENT_ID as "ID АБОНЕНТА", |
... |
{code} select distinct a.name as "ФИО", |
b.max_time as "Последнее подключ." from abonents as a |
inner join (select rs.abonent_id, max(rs.start_time) as max_time from radius_sessions rs group by abonent_id) as b on a.id=b.abonent_id where max_time<(':1 Дата|date$') {code} |
... |
abs.CEIL_OUT, uf_ip2string(u.ip) as "user_ip", |
u.ROUTER_VLAN, |
u.GPON_MODEM_PORT, |
u.ROUTER_PORT |
from users as u left join abonents as a on a.id=u.abonent_id |
... |
# h5. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус. {code} |
select |
a.contract_number as "Договор", a.name as "Имя", |
... |
cast(round((cast(aa.ostatok as float)+cast(aa.debit as float)-cast(aa.credit as float))/10000000000,2) as numeric(18,2)) as "Баланс", (select st.name from status st where st.id = os.status) as "Статус" |
from |
abonents a |
left join homes h on h.id = a.home_id join admin_accounts aa on aa.id = a.account_id |
join |
objects_status os on a.id = os.object_id |
where |
a.is_folder = 0 {code} |
# h5. Поиск IP-адреса в истории изменения учетных записей (история выдачи ip-адреса) IP-адреса) |
При каждом изменении учётной записи новое состояние учётной записи записывается в историю. Таблица *users_history*. Отчёт покажет когда абоненту был назначен IP-адрес, а также были ли измененяия по абоненту пока он владел этим адресом. Отчёт не показывает когда IP-адрес был снят с абонента. |
{code} select uf_ip2string(ip) as IP,abonent_id,time_changed,a.name |
from |
users_history uh |
join |
abonents a |
on uh.abonent_id = a.id |
where |
uf_ip2string(ip) = ':Введите ip-адрес$' |
order by time_changed |
{code} # h5. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс" |
... |
left join STATUS S on S.ID = OS.STATUS where (AB.IS_FOLDER = 0 |
or AB.IS_FOLDER is null) and AB.DELETED = 0 |
{code} # h5. Отчет выводит данные по операциям по всем абонентам за выбранный период {code} |
select |
a.contract_number "Договор", a.name "ФИО", |
... |
join fin_types ft on fo.op_type = ft.type_id join auth_user au on fo.owner_id = au.id |
where |
fo.op_date between ':C_даты|date$' and ':По_дату|date$' order by op_date |
... |
# h5. Отчёт, выводящий список незаблокированных абонентов. {code} |
select |
a.name as "ФИО", a.contract_number as "Договор" |
from abonents a |
left join abonents_block ab on ab.abonent_id=a.id where ab.id is null |
... |
and a.is_folder=0 {code} |
# h5. Отчёт по абонентам с блокировкой по отрицательному балансу. |
{code} select |
... |
{code} # h5. Абоненты с учетными записями без mac-адреса. Формат: номер договора, ip учетной записи (если есть) |
{code}select |
a.contract_number as "Номер договора", |
coalesce(uf_ip2string(u.ip), '') as "IP" |
from |
users u |
join |
abonents a |
on u.abonent_id = a.id |
where |
u.phone is null and |
u.deleted = 0 and |
ua.deleted = 0 and |
a.deleted a.is_folder = 0 and |
a.is_folder u.is_template = 0 and |
u.is_template = 0 and |
(u.mac is null or u.mac = ''){code} # h5. Отчёт о списаниях абонентской платы по папкам за определенный период. |
... |
{code} # h5. Отчёт по телефонии. Структура данных информации об Абонентах по маске 7496 |
h6. sql запрос |
{code} |
select distinct |
'K6#' || A.CONTRACT_NUMBER as "Договор", |
A.NAME, |
AV_UR_ADDR.ATTRIBUTE_VALUE as URR_ADDR, |
AV_UR_INN.ATTRIBUTE_VALUE as INN, |
AV_UR_KPP.ATTRIBUTE_VALUE as KPP, |
'2' as c4, |
'2' as c4, c5, |
'2' as c5, |
'1' as c6, |
'643' as c7, |
CAST(lpad(EXTRACT(DAY FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM A.CREATE_DATE) || ' 00:00:00' as CREATE_DATE, |
IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') end_date, |
'1' as c1, |
'46' as c2, |
'0' as c3 |
from abonents A |
left join ATTRIBUTE_VALUES AV_UR_ADDR on AV_UR_ADDR.ABONENT_ID=A.ID and AV_UR_ADDR.ATTRIBUTE_ID=25 |
left join ATTRIBUTE_VALUES AV_UR_INN on AV_UR_INN.ABONENT_ID=A.ID and AV_UR_INN.ATTRIBUTE_ID=4 |
left join ATTRIBUTE_VALUES AV_UR_KPP on AV_UR_KPP.ABONENT_ID=A.ID and AV_UR_KPP.ATTRIBUTE_ID=5 |
left join users on users.abonent_id=A.id left join PHONE_PULL_CHACHE PPC on PPC.id=users.phone |
... |
and a.company<>':Абонент|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$' and (select count(*) from abonents ab where ab.account_id=a.account_id group by ab.account_id)=1 |
UNION ALL |
select distinct |
'K6#' || A.CONTRACT_NUMBER as "Договор", |
A.NAME, |
AV_UR_ADDR.ATTRIBUTE_VALUE as URR_ADDR, |
AV_UR_INN.ATTRIBUTE_VALUE as INN, |
AV_UR_KPP.ATTRIBUTE_VALUE as KPP, |
'2' as c4, |
'2' as c4, c5, |
'2' as c5, |
'1' as c6, |
'643' as c7, |
CAST(lpad(EXTRACT(DAY FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM A.CREATE_DATE) || ' 00:00:00' as CREATE_DATE, |
IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') end_date, |
'1' as c1, |
'46' as c2, |
'0' as c3 |
from abonents A |
left join ATTRIBUTE_VALUES AV_UR_ADDR on AV_UR_ADDR.ABONENT_ID=A.ID and AV_UR_ADDR.ATTRIBUTE_ID=25 |
left join ATTRIBUTE_VALUES AV_UR_INN on AV_UR_INN.ABONENT_ID=A.ID and AV_UR_INN.ATTRIBUTE_ID=4 |
left join ATTRIBUTE_VALUES AV_UR_KPP on AV_UR_KPP.ABONENT_ID=A.ID and AV_UR_KPP.ATTRIBUTE_ID=5 |
left join users on users.abonent_id=A.id left join PHONE_PULL_CHACHE PPC on PPC.id=users.phone |
... |
and a.main=1 {code} |
h6. Шаблон отчёта |
{code} {% extends "form_list.html" %} |
... |
FROM counters{code} # h5. Поиск абонентов со скидкой, с выводом самой скидки, id абонента, id услуги и фио абонента. |
{code}select |
u.discount, |
u.abonent_id, |
u.abonent_id, u.usluga_id, |
u.usluga_id, a.name |
from USERS_USLUGA u join abonents a on a.id=u.abonent_id where |
a.name u.ACTIVATED=1 |
from USERS_USLUGA u join abonents a on a.id=u.abonent_id where u.ACTIVATED=1 |
and u.DELETED=0 |
and u.discount>0 {code} # h5. Отчёт, который выводит списания абонентов с учетом и без учета НДС за выбранный период по каждому абоненту. {code} |
select a.name as "ФИО", |
a.contract_number as "Договор", |
h.CITY as "Город", sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма с ндс ", |
round((sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)))/1.18) as "Сумма без ндс" |
from finance_operations fo left join abonents a on a.id=fo.abonent_id |
... |
Отчёт выводит сверку по потреблению услуги телефонного трафика с разделением по категориям и абонентам. {code} |
select |
vc.name || ' (' || vc.id || ')' as "Категория", |
a.contract_number as "№ Договора абонента", sum(vl.bill_sum)/10000000000.00 as "Сумма", |
... |
join voip_direction vd on vd.id=cd.direction_id and vd.id=vl.direction_id join voip_category vc on cd.category_id=vc.id |
join abonents a on vl.abonent_id=a.id |
where vl.bill_sum>0 and |
... |
# h5. Отчёт по абонентам со статусом, балансом, датой последнего платежа и суммой последнего платежа. {code} |
select |
ab.name as "ФИО", |
ab.contract_number as "Договор", |
s.name as "Статус", |
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID and FO.OP_TYPE=2) as "Дата посл.", (select first 1 round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) from finance_operations FO1 where FO1.ABONENT_ID = AB.ID and FO1.OP_TYPE=2 order by fo1.op_date desc) as "Платеж", (aa.debit+aa.Ostatok-aa.credit)/10000000000.00 as "Баланс" from abonents ab |
left join objects_status os on |
os.object_id=ab.id |
left join status s on |
s.id = os.status |
left join admin_accounts aa on |
aa.id = ab.account_id where extract (DAY from ab.activate_date) >0 {code} |
# h5. Отчёт по телефонии по звонкам, которые были выполнены на конкретный пул телефонных номеров за указанную дату по конкретному абоненту. |
{code} |
select |
vl.id as "ID звонка", s_time as "Начало звонка", |
... |
where CONST_ID = 1) as numeric(18,2)), 2) as "Сумма", chan as "Канал" |
from voip_log VL |
where billed=1 and |
... |
and vl.abonent_id=:1Абонент|select[Abonents,is_folder=0]$ AND (vl.DST between (':4 Начало пула$') and (':5 Конец пула$')) |
{code} |
# h5. Отчёт по RADIUS-сессиям выбранного абонента: начало, конец, длительность, причина завершения (если завершена), IP. |
{code}select START_TIME "Начало", |
END_TIME "Конец", |
case |
when coalesce(end_reason,'')='' then 'Не было Act-Session-Stop' when datediff(hour from start_time to end_TIME)>=1 then datediff(hour from start_time to end_TIME) || ' часов' |
... |
where abonent_id=':Абонент|select[Abonents]$' |
order by |
start_time desc{code} # h5. Отчёт для ФСБ в формате Оператор связи IP-адрес Логин Дата подключения Дата отключения Адрес подключения Абонент Дата рождения Документ (ИНН) Адрес регистрации (юридический) Контактное лицо Контактный номер Дополительня информаци {code} |
select '' from abonents |
UNION |
select distinct '"' || (select name from abonents where abonents.id=a.operator_id) || '";"' || |
... |
left join USERS U on A.ID = U.ABONENT_ID left join HOMES H on A.HOME_ID = H.ID |
left join ATTRIBUTE_VALUES DR on DR.ABONENT_ID=A.ID and DR.ATTRIBUTE_ID=22 |
left join ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25 |
left join ATTRIBUTE_VALUES KLICO on KLICO.ABONENT_ID=A.ID and KLICO.ATTRIBUTE_ID=32 left join ATTRIBUTE_VALUES INN on INN.ABONENT_ID=A.ID and INN.ATTRIBUTE_ID=4 |
... |
{code} select abonent_id, |
count(*) as "Кол-во услуг", |
(select count(*) from users_usluga uu1 where uu1.deleted=0 and uu1.enabled=1 and uu1.abonent_id=users_usluga.abonent_id having count(*)>1) as "Кол-во акт. услуг" from users_usluga where deleted=0 group by abonent_id having count(*)>1 |
... |
select abonent_id,time_changed,a.name |
from |
users_history uh |
join |
abonents a |
on uh.abonent_id = a.id |
where |
phone = (select id from PHONE_PULL_CHACHE where PHONE=':Введите номер$') {code} |
... |
select uf_ip2string(ip) as IP,abonent_id,time_changed,a.name |
from |
users_history uh |
join |
abonents a |
on uh.abonent_id = a.id |
where |
LOGIN_UPPER like upper(':Логин$') {code} |
# h5. Отчёт по услугам IP телевидения с возможностью выбора периода. |
Выводит следующие поля: 1) Имя услуги |
... |
{color:#ff0000}{*}Примечание{*}{color}{color:#000000}*: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".*{color} {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 "Цена услуги", coalesce((select count(distinct abonent_id) from arch_account_stack where BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') and arch_account_stack.usluga_id=u.id),0) as "Кол-во абонентов", coalesce((select count(*) from arch_account_stack where arch_account_stack.usluga_id=u.id and BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by usluga_id),0) as "Дни", (select coalesce((round(sum(counters1.SUMM), 2)),0) from counters counters1 where S_DATE between (':1 Дата|date$') and (':2 Дата|date$') and counters1.usluga_id=u.id)as "Сумма" |
from usluga u |
join counters on counters.usluga_id=u.id where U.SYSTEM_TYPE=7 |
... |
sum(SUMOP) from (select round(sum(counters.SUMM), 2) as SUMOP |
from usluga u |
join counters on counters.usluga_id=u.id where U.SYSTEM_TYPE=7 |
... |
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$')) {code} |
# h5. Отчёт по абонентам, у которых подключены услуги IP телевидения. |
Выводит следующие поля: 1) Имя абонента |
... |
u.name as "Услуга", round(sum(counters.SUMM), 2) as "Сумма" |
from counters |
join usluga u on u.id=counters.usluga_id 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 |
... |
sum(SUMOP) from (select round(sum(counters.SUMM), 2) as SUMOP |
from counters |
join usluga u on u.id=counters.usluga_id 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. |
... |
#* 92 - Id услуги "Междугородная связь" #* 91 - Id услуги "Внутризоновая связь" |
h6. SQL запрос: |
{code} select |
'Cnst1' as "Аг. договор", |
A.CONTRACT_NUMBER as "Аб. договор", |
... |
c.summ<>0 {code} |
h6. Шаблон отчёта: |
{code} {% extends "form_list.html" %} |
... |
{% endblock %} {code} |
# h5. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения. |
{code} select distinct a.name as "ФИО", (select name from status where id = OBJECTS_SUBSTATUS.STATUS_ID) as "Тип подключения" |
from abonents a |
left join OBJECTS_STATUS os on os.OBJECT_ID=a.id left join OBJECTS_SUBSTATUS on OBJECTS_SUBSTATUS.OBJECT_ID=os.id {code} # h5. Отчёт, который выводит все активные RADIUS-сессии. |
{code}select |
a.contract_number as "Номер договора", u.login as "Учетная запись", uf_ip2string(r.ip_address) as "IP", RADIUS_UPDATE as "Последний ACC_UPDATE" |
from |
users_radiusauth r |
join users u on r.user_id=u.id join abonents a on u.abonent_id=a.id where |
join r.logged=1 |
users u on r.user_id=u.id join abonents a on u.abonent_id=a.id where r.logged=1 |
order by |
:1. Группировать по столбцу|choices[1^]Номер договора^[2^]Учетная запись^[3^]IP^[4^]ACC_UPDATE]$ :2. Группировать в порядке|choices[asc^]Возрастания^[desc^]Убывания]${code} # h5. Отчёт по распределению абонентов из определенной группы по тарифам, так же выводится количество в настоящий момент заблокированных и не заблокированных абонентов. |
{code}select |
|
count(*) as "Количество", |
t.name as "Тариф", |
... |
count(*)-(select count(distinct atr.id) from (with recursive tree (id,tarif_id,is_folder) as (select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$') union all select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id,tarif_id from tree where is_folder=0) atr join abonents_block ab on atr.id=ab.abonent_id join tarif tt on atr.tarif_id=tt.id where tt.name=t.name) "Работающие" |
from |
(with recursive tree (id,tarif_id,is_folder) as ( |
select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$') |
union all |
select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) |
select id,tarif_id from tree where is_folder=0) ab join tarif t on ab.tarif_id=t.id |
group by 2 |
group order by |
2 order by |
1 desc{code} |
# h5. Отчёт по поступившим платежам за выбранный период, с возможностью выбора папки для поиска и пользователя,который добавил платеж. |
{code} select AB.CONTRACT_NUMBER as "Номер договора", |
... |
and (AB.DELETED = 0 or AB.DELETED is null) |
|
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, AU.USERNAME, FO.SYSTEM_DATE union |
... |
# h5. Отчёт по абонентам, выводящий следующий данные:"ФИО, договор, текущий баланс,сумму списаний по разовым услугам за период, сумму абон.платы, сумму приходов за период". {code} |
select a.name as "ФИО", |
a.contract_number as "Договор", round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс", ( |
select |
sum(round(users_usluga.SUMM / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) |
from users_usluga |
left join usluga on usluga.id=users_usluga.usluga_id where users_usluga.ABONENT_ID=a.id |
... |
) as "Абон. плата", ( |
select sum(round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) |
from finance_operations fo1 |
where fo1.abonent_id=a.id |
and fo1.op_type=2 and fo1.op_date between (':1 Дата|date$') and (':2 Дата|date$') ) as "Приходы" |
from abonents a |
left join finance_operations fo on fo.abonent_id=a.id left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID where is_folder=0 group by a.name, a.TARIF_ID, (aa.ostatok+aa.debit-aa.credit) ,a.id,a.contract_number |
{code} |
# h5. Отчёт по абонентам с блокировкой по отрицательному балансу с балансом больше 2. {code} |
... |
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.deleted=0 |
and a.deleted=0 ab.B_NEGBAL=1 |
and ab.B_NEGBAL=1 |
and round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)>2 {code} |
# h5. Отчёт по абонентам, у которых дата следующего списания больше, чем указанная. |
{code} |
select |
distinct a.id as id, a.name as "ФИО", uu.next_date as "След. списание", |
a.contract_number as "Договор" |
from abonents a |
left join users_usluga uu on uu.abonent_id=a.id |
where uu.next_date>(':1 Дата|date$') and a.deleted=0 {code} |
... |
ua.name, av.attribute_value |
from |
abonents a |
join |
attribute_values av on a.id=av.abonent_id and av.attribute_value like '%:Значение неквизита$%' |
join |
user_attributes ua on av.attribute_id=ua.attribute_id {code} |
... |
{info}Время в фильтре проверят дату *создания* а не дату *закрытия* заявки {info} {code} |
select |
HDSK.id, |
HDSK.SUBJ as "Тема", |
hs.NAME as "Статус", a.name as "Назначено", ht.NAME as "Тип" |
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 on a.id=hdsk.PERFORMED_WHOM left join auth_user au on au.USERNAME=a.name |
where hdsk.IS_TASK=1 |
and hdsk.STATUS=':Статус|select[HdskStatus]$' and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$' |
... |
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$') union all |
select |
'"Количество"', '', '', '', |
count(*) |
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 on a.id=hdsk.PERFORMED_WHOM left join auth_user au on au.USERNAME=a.name |
where hdsk.IS_TASK=1 |
and hdsk.STATUS=':Статус|select[HdskStatus]$' and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$' |
... |
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$') {code} |
# h5. Отчёт по абонентам с балансом между минимальным и максимальным указанным. |
{code} select distinct |
... |
{code} # h5. Отчет выводящий объёмы трафика за вбыранный период (месяца целиком) по абоненту |
{code}select |
select |
year_number ||'-'|| month_number 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 |
(SUM_BYTE_IN_M/1000000000.00) as "Входящий/гиг", traf_counters |
(SUM_BYTE_OUT_M /1000000000.00) as "Исходящий/гиг" |
from where |
traf_counters where |
Abonent_ID = ':3-Абонент|select[Abonents,is_folder=0]$' and MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date)) 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} |
... |
select distinct usl.name as "Услуга", |
(case when a.A_HOME_NUMBER='' then cast('0' as NUMERIC) |
else cast((a.A_HOME_NUMBER) as NUMERIC) end) as "Квартира", |
( select |
... |
) then 'Должник' else '' end) as "Должник", h.city || ', ' || h.STREET || ', д. ' || h.S_NUMBER as "Адрес", |
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс" |
from ABONENTS A left join USERS U on A.ID = U.ABONENT_ID |
... |
# h5. Отчёт по сумме приходов абонентов, подключенных к оператору связи за период. Вторым столбцом выводится 10% от суммы приходов {code} |
select |
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE from VPN_CONST |
... |
substring(cast(vl.s_time as varchar(32)) from 1 for 19) as "Начало звонка", cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) as "Время разговора", |
coalesce(vd.name,'') as "Название направления", |
coalesce(vl.step_price/10000000000.00,0) as "Стоимость направления", cast(coalesce(vl.bill_sum/10000000000.00,'') as float) as "Стоимость" from voip_log vl |
left join voip_direction vd on vl.direction_id=vd.id |
where vl.s_time between ':1-Начало|date$' and ':2-Конец|date$' and vl.abonent_id = ':Абонент|select[Abonents,is_folder=0]$' union all |
select |
' ', ' ', |
... |
sum(duration), ' ', |
' ', |
sum(cost) from |
... |
cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) as duration, coalesce(vl.step_price/10000000000.00,0) as directioncost, |
round(cast(coalesce(vl.bill_sum/10000000000.00,'') as float),2) as cost |
from voip_log vl |
... |
order by 3 {code} |
Скрипт для получения журнала звонков по всем [архивным базам|CarbonBilling:Настройка периода хранения исторических данных в базе] {code}#!/bin/bash >export.csv >export_raw.csv # Текущая дата curdate=$(date +'%Y-%m-%d %H:%M:%S') # Начало периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00' period_start='2019-01-01 00:00:00' # Конец периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00' period_end='2019-10-01 00:00:00' # Если начало или конец периода не заданы - будет выбрана текущая дата period_start_fixed=${period_start:-$curdate} period_end_fixed=${period_end:-$curdate} # ID абонента abonent_id=10507 # Заголовок csv для удобства работы в электронных таблицах echo "Звонок с номера;Назначение звонка;Название направления;Начало звонка;Окончание звонка;Время разговора;Стоимость направления;Стоимость" > export.csv # Получаем звонки из архивных баз find /var/db/billing/ -iname voip_log.fdb | sort | while read file; do #echo $file >> export_raw.csv # DEBUG sqlexec $file "set heading off; select distinct 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) || ';' || cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) || ';' || coalesce(vl.step_price/10000000000.00,0) || ';' || cast(coalesce(vl.bill_sum/10000000000.00,'') as float) from voip_log vl where vl.s_time between '$period_start' and '$period_end_fixed' and vl.abonent_id=$abonent_id " | sed 's/ *//g; /^$/d' >> export_raw.csv done # Получаем звонки из основной БД #echo '/var/db/billing.gdb' >> export_raw.csv # DEBUG sqlexec /var/db/billing.gdb "set heading off; select distinct vl.src || ';' || vl.dst || ';' || 'direction:' || vl.direction_id || ';' || substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' || cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) || ';' || coalesce(vl.step_price/10000000000.00,0) || ';' || cast(coalesce(vl.bill_sum/10000000000.00,'') as float) from voip_log vl where vl.s_time between '$period_start_fixed' and '$period_end_fixed' and vl.abonent_id=$abonent_id" | sed 's/ *//g; /^$/d' >> export_raw.csv # Получаем из БД названия направлений и отфильтровываем дебаг cat export_raw.csv | grep -vE '^<null>|voip_log.fdb$' | cut -d';' -f 3 | sort | uniq | while IFS=':' read field_name direction_id; do direction_name=$(sqlexec "set heading off; select vd.name from voip_direction vd where vd.id='$direction_id'" | sed 's/ *//g; /^$/d') sed "s/direction:${direction_id}/${direction_name}/g" -i export_raw.csv done # Сортируем на случаей если звонок попал в несколько архивных баз и пишем в выгрузку cat export_raw.csv | grep -v '^<null>' | sort | uniq >> export.csv{code} # h5. Отчёт по абонентам, у которых услуги подключены не 5 числа. Данный отчёт подойдет для отслеживания абонентов со сдвигом даты списания услуг. {code} select a.contract_number as "Договор", a.name as "ФИО", uu.usluga_id as "ID услуги", u.name as "Имя услуги" from users_usluga uu left join usluga u on u.id=uu.usluga_id left join abonents a on a.id=uu.abonent_id where extract(day from cast(uu.create_date as date))!=5 and u.USLUGA_ABON_TYPE_ID=4 and uu.deleted=0 and uu.usluga_id!=-170000 {code} # h5. Отчёт по наличным/безналичным приходам с группировкой по тарифам абонентов за указанный период. {code} select t.name as "Тариф", ( select sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) FROM TARIF t1 LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID WHERE t1.id = t.ID ) as "Абон. плата", ( select distinct count(*) from abonents a1 left join tarif t2 on t2.id=a1.tarif_id left join finance_operations fo1 on fo1.abonent_id=a1.id where t2.id=t.id and fo1.OP_TYPE = 2 and fo1.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$') ) as "Количество", ( select sum(round(fo2.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) from finance_operations fo2 left join abonents a on a.id=fo2.abonent_id where a.tarif_id=t.id and fo2.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$') and fo2.is_cash=1 and fo2.OP_TYPE = 2 )as "Наличные", ( select sum(round(fo3.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) from finance_operations fo3 left join abonents a on a.id=fo3.abonent_id where a.tarif_id=t.id and fo3.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$') and fo3.is_cash=0 and fo3.OP_TYPE = 2 )as "Безналичные", ( select sum(round(fo.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) from finance_operations fo left join abonents a on a.id=fo.abonent_id where a.tarif_id=t.id and fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$') and fo.OP_TYPE = 2 )as "Сумма" from tarif t where t.ARCHIVED=0 union all select first 1 '"Итого"', null, null, (select sum(SUMOP) from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as SUMOP from FINANCE_OPERATIONS FO left join ABONENTS AB on FO.ABONENT_ID = AB.ID where OP_TYPE = 2 and fo.is_cash=1 and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')) and (AB.DELETED = 0 or AB.DELETED is null))), (select sum(SUMOP) from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as SUMOP from FINANCE_OPERATIONS FO left join ABONENTS AB on FO.ABONENT_ID = AB.ID where OP_TYPE = 2 and fo.is_cash=0 and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')) and (AB.DELETED = 0 or AB.DELETED is null))), (select sum(SUMOP) from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as SUMOP from FINANCE_OPERATIONS FO left join ABONENTS AB on FO.ABONENT_ID = AB.ID where OP_TYPE = 2 and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')) and (AB.DELETED = 0 or AB.DELETED is null))) from tarif t {code} # h5. Отчёт для анализа настройки агентской схемы VoIP, отражает текущую настройку транков в операторских услугах телефонии {code}select a.name FIO, coalesce(vt.name,'---') DIRECTION, u.name USLUGA, uu.voip_operator_match_format TRUNK from users_usluga uu join usluga u on uu.usluga_id=u.id join abonents a on uu.abonent_id=a.id left join V_TYPE vt on u.voip_v_type_id=vt.id where uu.deleted=0 and a.deleted=0 and u.system_type=5 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} select distinct a.id, a.name, a.sms, a.contract_number from abonents a left join finance_operations fo on fo.abonent_id=a.id where fo.op_type=2 and not exists(select 1 from finance_operations where OP_DATE between (dateadd (-1 month to date ':Начало|date$')) and current_date and op_type=2 and abonent_id=a.id) and a.deleted=0 and a.is_folder=0 and a.parent_id not in (244,4,2) {code} # h5. Отчёт по объёмам потребленного трафика посуточно и остатка/превышения ежедневных и ежемесячных лимитов (если они есть) за указанный период Для работы отчета необходимо включить опцию "*Сохранять движения всего трафика*" в [настройках оператора связи|CarbonBilling:Глобальные настройки биллинга и оператора] {code}/* 1) Получаем абонента, дни когда был расход и по каким услугам - select from abonents join (select distinct from arch_account_stack join usluga) 2) Присоединяем данные по расходу трафика (вх/исх отдельно) - left join down left join upl 2.1) Берем данные из arch_account_stack, группируя по услугам и датам 2.2) Считаем сумму по трафику за сутки, попутно вычисляя остаток/превышение суточного лимита (если есть) 3) Считаем итого за месяц по каждой услуге из тех же самых данных - union all select... 3.1) в присоединенных данных по трафику добавляем колонку с месячным лимитом, с помощью этого вычисляем остаток/превышение за месяц */ select dates.aasdate "Период", dates.aasusluga "Услуга", coalesce(cast(round(down.mbsum) as varchar(32)),'') "Входящий МБ", down.limitleft as "Лимит ВХ", coalesce(cast(round(upl.mbsum) as varchar(32)),'') "Исходящий МБ", upl.limitleft as "Лимит ИСХ" from abonents a join (select distinct aaasss.abonent_id as abon, aasu.name as aasusluga, cast(cast(aaasss.CHANGE_BALANCE_TIME as date) as varchar(32)) as aasdate from arch_account_stack aaasss join usluga aasu on aaasss.usluga_id=aasu.id where aaasss.abonent_id= ':Abonent_ID$' and aaasss.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59') dates on dates.abon=a.id left join (select aas.abonent_id as abon, cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den, u.name as usl, coalesce(cast(u.max_mb_in_d as varchar(32)) || 'МБ','') as lim, sum(aas.v) as mbsum, coalesce( iif( sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null, 'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ', 'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ' ), '' ) as limitleft from arch_account_stack aas join usluga u on aas.usluga_id=u.id where aas.Abonent_ID = ':Abonent_ID$' and aas.ttype=2 and aas.credit=0 and v_type_id=1 and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59' group by aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d) down on dates.aasdate=down.den and dates.aasusluga=down.usl left join (select aas.abonent_id as abon, cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den, u.name as usl, coalesce(cast(u.max_mb_out_d as varchar(32)) || 'МБ','') as lim, sum(aas.v) as mbsum, coalesce( iif( sum(aas.v)<=u.max_mb_out_d and u.max_mb_out_d is not null, 'Остаток ' || cast(round(u.max_mb_out_d-sum(aas.v)) as varchar(32)) || 'МБ', 'Превышение ' || cast(round(sum(aas.v)-u.max_mb_out_d) as varchar(32)) || 'МБ' ), '' ) as limitleft from arch_account_stack aas join usluga u on aas.usluga_id=u.id where aas.Abonent_ID = ':Abonent_ID$' and aas.ttype=2 and aas.credit=0 and v_type_id=2 and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59' group by aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_out_d) upl on dates.aasdate=upl.den and dates.aasusluga=upl.usl where a.id= ':Abonent_ID$' and (upl.mbsum is not null or down.mbsum is not null) union all select extract(year from cast(dates.aasdate as timestamp)) ||'-'|| extract(month from cast(dates.aasdate as timestamp)), dates.aasusluga, coalesce(cast(round(sum(down.mbsum)) as varchar(32)),''), coalesce( iif( sum(down.mbsum)<=down.limm and down.limm is not null, 'Остаток ' || cast(round(down.limm-sum(down.mbsum)) as varchar(32)) || 'МБ', 'Превышение ' || cast(round(sum(down.mbsum)-down.limm) as varchar(32)) || 'МБ' ), '' ), coalesce(cast(round(sum(upl.mbsum)) as varchar(32)),''), coalesce( iif( sum(upl.mbsum)<=upl.limm and upl.limm is not null, 'Остаток ' || cast(round(upl.limm-sum(upl.mbsum)) as varchar(32)) || 'МБ', 'Превышение ' || cast(round(sum(upl.mbsum)-upl.limm) as varchar(32)) || 'МБ' ), '' ) from abonents a join (select distinct aaasss.abonent_id as abon, aasu.name as aasusluga, cast(cast(aaasss.CHANGE_BALANCE_TIME as date) as varchar(32)) as aasdate from arch_account_stack aaasss join usluga aasu on aaasss.usluga_id=aasu.id where aaasss.abonent_id= ':Abonent_ID$' and aaasss.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59') dates on dates.abon=a.id left join (select aas.abonent_id as abon, cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den, u.name as usl, u.max_mb_in_d as limd, u.max_mb_in_m as limm, sum(aas.v) as mbsum, coalesce( iif( sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null, 'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ', 'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ' ), '' ) as limitleft from arch_account_stack aas join usluga u on aas.usluga_id=u.id where aas.Abonent_ID = ':Abonent_ID$' and aas.ttype=2 and aas.credit=0 and v_type_id=1 and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59' group by aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d,max_mb_in_m) down on dates.aasdate=down.den and dates.aasusluga=down.usl left join (select aas.abonent_id as abon, cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den, u.name as usl, u.max_mb_in_d as limd, u.max_mb_in_m as limm, sum(aas.v) as mbsum, coalesce( iif( sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null, 'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ', 'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ' ), '' ) as limitleft from arch_account_stack aas join usluga u on aas.usluga_id=u.id where aas.Abonent_ID = ':Abonent_ID$' and aas.ttype=2 and aas.credit=0 and v_type_id=2 and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59' group by aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d,max_mb_in_m) upl on dates.aasdate=upl.den and dates.aasusluga=upl.usl where a.id= ':Abonent_ID$' and (upl.mbsum is not null or down.mbsum is not null) group by 1,2,down.limm,upl.limm order by 1,2{code} Шаблон для [размещения в личном кабинете|CarbonBilling:Отчёты в личном кабинете] {code}<form class="form-inline" method="post" action="" role="form"> <?$this->module_token()?> <div class="form-group"> <legend><h2>Трафик за выбранный период</h2></legend> <label for="1-date_start">C:</label><input class="datepicker" id="1-date_start" name="1-date_start|date" type="text" /> <label for="2-date_end">По:</label><input class="datepicker" id="2-date_end" name="2-date_end|date" type="text" /> </div> <br /> <button type="submit" class="btn btn-success">Выполнить</button> </form>{code} # h5. Отчёт по звонкам учтенным для взаиморасчетов между операторами {code}select calls.service as "Услуга", calls.source as "Источник", calls.destination as "Назначение", calls.call_start as "Начало звонка", calls.call_end as "Завершение звонка", calls.s_chan as "Ист. транк", calls.d_chan as "Назн. транк", calls.cost as "Стоимость" from (select aas.id aasid, vl.id vlid, u.name service, aas.credit/10000000000.00 cost, s_time call_start, e_time call_end, src source, dst destination, src_chan s_chan, dst_chan d_chan from voip_log vl join arch_account_stack aas on vl.id=aas.log_id join usluga u on aas.usluga_id=u.id and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$ where aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$' and s_time between ':1-Период начала звонка, от|date$' and ':2-Период начала звонка, до|date$ 23:59:59' ) calls union all select ' ', ' ', 'Итого', cast(':1-Период начала звонка, от|date$' as timestamp), cast(':2-Период начала звонка, до|date$ 23:59:59' as timestamp), ' ', ' ', cast(sum(calls.cost) as varchar(32)) from (select aas.id aasid, vl.id vlid, u.name service, aas.credit/10000000000.00 cost, s_time call_start, e_time call_end, src source, dst destination, src_chan s_chan, dst_chan d_chan from voip_log vl join arch_account_stack aas on vl.id=aas.log_id join usluga u on aas.usluga_id=u.id and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$ where aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$' and s_time between ':1-Период начала звонка, от|date$' and ':2-Период начала звонка, до|date$ 23:59:59' ) calls group by calls.service{code} # h5. Список абонентов, у которых не заведён дом или дом заведён некорректно Отчет {code}select a.id, a.name, a.contract_number, t.name, p.name, a.home_id, iif(a.home_id is not null, 'Улица: ' || coalesce(h.street,''),'Не выбран дом'), iif(a.home_id is not null and (h.city is null or h.city=''), 'Не указан город', 'Не выбран дом') from abonents a join tarif t on a.tarif_id=t.id join abonents p on a.parent_id=p.id left join homes h on a.home_id=h.id where (a.home_id is null or h.city='' or h.city is null) and a.is_folder=0 and a.category_id<2 and a.deleted=0 and a.id>0 and a.parent_id not in (244,1313,16723) order by 7,5{code} Шаблон {code}{% extends "form_list.html" %} {% load field_type %} {% block content %} <form method="POST"> <div id="params"> {% csrf_token %}{% load mathfilters %} <input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' /> {% if form %} <div class="row-fluid"> <div class="span6"> <legend><h2>Заполните поля запроса</h2></legend> {{ form }} </div> </div> {% endif %} <div class="row-fluid"> <div class="down_toolbar"> <button type="submit" value="Выполнить запрос" class="default btn btn-success"> <i class="icon-ok icon-white"></i> Выполнить запрос </button> {% if data %} <div class="btn-group"> <a class="default btn dropdown-toggle" data-toggle="dropdown" href="#"> <i class="icon-print icon-white"></i> Выгрузка <span class="caret"></span> </a> <ul class="dropdown-menu"> <li><button type="submit" name="csv" value="True">CSV</button></li> <li><button type="submit" name="dbf" value="True">DBF</button></li> </ul> </div> <button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button> {% endif %} </div> </div> </div> </form> {% block subcontent %} {% if execute %} <h2>{{inst.name}}</h2> {% if error %} {{ error }}<br/><br/> {% endif %} {% if data %} <table id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="2"> <tr> <th>ФИО</th> <th>Договор</th> <th>Тариф</th> <th>Группа</th> <th>Улица</th> <th>Проблема</th> </tr> {% for row in data %} <tr> <td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td> <td>{{ row.2 }}</td> <td>{{ row.3 }}</td> <td>{{ row.4 }}</td> <td>{% if row.5 %}<a href="/admin/Abonents/Abonents/{{ row.5 }}/" target="_blank">{{ row.6 }}{% else %} --- {% endif %}</a></td> <td>{{ row.7 }}</td> </tr> {% endfor %} </table> {% else %} <h3>Ничего не найдено.</h3> {% endif %} {% endif %} {% endblock %} {% endblock %} {% block js_addon %} <link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" /> <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script> <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script> <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script> <script type="text/javascript" src="/static/js/makedatetime.js"></script> <script type="text/javascript" src="/static/js/print.js"></script> <link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" /> {% endblock %}{code} # h5. Отчет по входящему интернет-трафику юридических лиц за выбранный период месяц Отчет расчитан на использование тарифов типа лестница с некоторым объёмом трафика включенного в абонентскую плату, по исчерпании которого начисляется помегабайтная оплата. По каждому абоненту отражены тарифы назначенные абоненту за период, суммарное потребление трафика по тарифу и отдельно трафик сверх включенного, а так же его стоимость. {code}select aid, ' ', s_id, ' УСЛУГА: ' || serv "Тариф/Услуга", coalesce(price,'') "Цена МБ", '' "Включено МБ", sum(mb_count) "МБ по усл./тарифу", '' "Всего МБ", sum(traf_cost) "Сумма руб." from (select distinct ab.id as aid, ab.name as fio, t.id as tar, t.name as tid, usl.name as serv, usl.id as s_id, tar_cost.in_price as price, uslpreord.mbinc, coalesce(tfc.p_in/10000000000.00,0) as traf_cost, round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count from traf_counters as tfc join abonents ab on tfc.abonent_id=ab.id join users_usluga as uu on tfc.users_usluga_id=uu.id join usluga usl on uu.usluga_id=usl.id left join tarif t on uu.tarif_id=t.id join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc from tarif_users_usluga tuu join usluga tu on tuu.usluga_id=tu.id where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0 group by tuu.tarif_id ) uslpreord on uu.tarif_id=uslpreord.tar left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price from tarif_users_usluga tuu join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0 ) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv where tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and ab.company = 1 and (usl.max_mb_in_m>0 or usl.in_price>0) --and ab.id in (1172,2575) ) where (traf_cost<>0 and price<>0) group by 1,3,4,5,6 union all select aid, ' ', tid, 'ТАРИФ: ' || tar "Тариф", ' ', cast(mbinc as varchar(16)) "Включено МБ", cast(sum(mb_count) as varchar(16)), cast(iif((sum(mb_count)-mbinc)>0, sum(mb_count)-mbinc, null) as varchar(32)) "МБ по услуге", iif(sum(traf_cost)>0,sum(traf_cost), null) "Сумма руб." from (select distinct ab.id as aid, ab.name as fio, t.id as tid, t.name as tar, tar_cost.in_price as price, uslpreord.mbinc, coalesce(tfc.p_in/10000000000.00,0) as traf_cost, round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count from traf_counters as tfc join abonents ab on tfc.abonent_id=ab.id join users_usluga as uu on tfc.users_usluga_id=uu.id join usluga usl on uu.usluga_id=usl.id left join tarif t on uu.tarif_id=t.id join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc from tarif_users_usluga tuu join usluga tu on tuu.usluga_id=tu.id where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0 group by tuu.tarif_id ) uslpreord on uu.tarif_id=uslpreord.tar left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price from tarif_users_usluga tuu join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0 ) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv where tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and ab.company = 1 and (usl.max_mb_in_m>0 or usl.in_price>0) --and ab.id in (1172,2575) ) where (traf_cost<>0 and price<>0) or (traf_cost=0 and price is null) group by 1,2,3,4,mbinc union all select distinct aid, fio, null, ' ', ' ', ' ', ' ', ' ', null from (select distinct ab.id as aid, ab.name as fio, t.id as tid, t.name as tar, tar_cost.in_price as price, uslpreord.mbinc, coalesce(tfc.p_in/10000000000.00,0) as traf_cost, round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count from traf_counters as tfc join abonents ab on tfc.abonent_id=ab.id join users_usluga as uu on tfc.users_usluga_id=uu.id join usluga usl on uu.usluga_id=usl.id left join tarif t on uu.tarif_id=t.id join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc from tarif_users_usluga tuu join usluga tu on tuu.usluga_id=tu.id where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0 group by tuu.tarif_id ) uslpreord on uu.tarif_id=uslpreord.tar left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price from tarif_users_usluga tuu join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0 ) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv where tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and ab.company = 1 and (usl.max_mb_in_m>0 or usl.in_price>0) --and ab.id in (1172,2575) ) order by 1,2,4{code} Шаблон отчета: {code}{% extends "form_list.html" %} {% load field_type %} {% block content %} <form method="POST"> <div id="params"> {% csrf_token %}{% load mathfilters %} <input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' /> {% if form %} <div class="row-fluid"> <div class="span6"> <legend><h2>Заполните поля запроса</h2></legend> {{ form }} </div> </div> {% endif %} <div class="row-fluid"> <div class="down_toolbar"> <button type="submit" value="Выполнить запрос" class="default btn btn-success"> <i class="icon-ok icon-white"></i> Выполнить запрос </button> {% if data %} <div class="btn-group"> <a class="default btn dropdown-toggle" data-toggle="dropdown" href="#"> <i class="icon-print icon-white"></i> Выгрузка <span class="caret"></span> </a> <ul class="dropdown-menu"> <li><button type="submit" name="csv" value="True">CSV</button></li> <li><button type="submit" name="dbf" value="True">DBF</button></li> </ul> </div> <button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button> {% endif %} </div> </div> </div> </form> {% block subcontent %} {% if execute %} <h2>{{inst.name}}</h2> {% if error %} {{ error }}<br/><br/> {% endif %} {% if data %} <table bordercolor="#AAAAAA" id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="3"> <tr> <th>ФИО</th> <th>Тариф</th> <th>Цена за МБ</th> <th>Объем предоплаченного трафика</th> <th>Объем трафика по тарифу/услуге</th> <th>Объем трафика сверх предоплаченного</th> <th>Переплата</th> </tr> {% for row in data %} {% if row.1 != ' ' %} <tr> <td bgcolor="#F5F5F5"> </td> <td bgcolor="#F5F5F5"> </td> <td bgcolor="#F5F5F5"> </td> <td bgcolor="#F5F5F5"> </td> <td bgcolor="#F5F5F5"> </td> <td bgcolor="#F5F5F5"> </td> <td bgcolor="#F5F5F5"> </td> </tr> {% endif %} {% if row.1 != ' ' and row.2 != ' ' %} <tr> <td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td> </tr> {% else %} <tr> <td> </td> <td>{% if row.4 != ' ' %} <a href="/admin/tarifs/Usluga/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% else %} <a href="/admin/tarifs/Tarif/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% endif %}</td> <td style="text-align: right;">{% if row.4 != ' ' %} {{ row.4 }} руб. {% else %} {% endif %}</td> <td style="text-align: right;">{% if row.5 %} {{ row.5|floatformat:2 }} Мб {% else %} {% endif %}</td> <td style="text-align: right;">{% if row.6 %} {{ row.6|floatformat:2 }} Мб {% else %} {% endif %}</td> <td style="text-align: right;">{% if row.7 %} {{ row.7|floatformat:2 }} Мб {% else %} {% endif %}</td> <td style="text-align: right;">{% if row.8 %} {{ row.8|floatformat:2 }} руб. {% else %} {% endif %}</td> </tr> {% endif %} {% endfor %} </table> {% else %} <h3>Ничего не найдено.</h3> {% endif %} {% endif %} {% endblock %} {% endblock %} {% block js_addon %} <link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" /> <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script> <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script> <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script> <script type="text/javascript" src="/static/js/makedatetime.js"></script> <script type="text/javascript" src="/static/js/print.js"></script> <link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" /> {% endblock %}{code} # h5. Цены на направления за период {code} select vd.mask as direction_mask, vd.id as direction_id, vd.name as direction_name, u.id as usluga_id, uv.active_from_date, round(uv.price/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 4) as price from usluga u JOIN usluga_voip uv on u.id=uv.usluga_id join CATEGORY_DIRECTIONS CD ON CD.CATEGORY_ID=uv.VOIPCATEGORY_ID join VOIP_CATEGORY VC ON CD.CATEGORY_ID=VC.ID join voip_direction vd on vd.id = cd.direction_id where (uv.active_from_date between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59') and vd.mask=:Маска направления$ {code} # h5. Отчёт по сумме списаний у абонентов. Списания суммируются из поля "Расход/предоплата" в финансовой информации абонента. {code} select first 1 'Дата начала периода ' || ':1-Начало|date$' as "Договор", 'Дата окончания периода ' || ':2-Конец|date$ 23:59:59' as "ФИО", null as "Сумма" from abonents union all select distinct a.contract_number as "Договор", a.name as "ФИО", sum(round(aas.PRICE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма" from abonents a left join arch_account_stack aas on aas.ABONENT_ID = A.ID where a.deleted=0 and a.id>0 and a.is_folder=0 and a.parent_id!=244 and aas.storno=0 and upper(aas.descr) not like upper('%Сторнирование%') and (BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59') and (select sum(PRICE) from arch_account_stack aas where aas.abonent_id=a.id and (BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'))/ (select CONST_VALUE from VPN_CONST where CONST_ID = 1)>0 group by 1,2 union all select '"Итого"', null, sum(SUMOP) from (select sum(round(aas.PRICE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as SUMOP from abonents a left join arch_account_stack aas on aas.ABONENT_ID = A.ID where a.deleted=0 and a.id>0 and a.is_folder=0 and a.parent_id!=244 and aas.storno=0 and upper(aas.descr) not like upper('%Сторнирование%') and (BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59')) {code} # h5. Отчёт по задолженности абонента за месяц. Выводит баланс на 1 число месяца, платежи за месяц и расход. Необходимо выполнять отчёт с 1 по 31 число уже завершенного месяца. {code} select AB.CONTRACT_NUMBER as "Номер договора", AB.NAME as "ФИО", round(FOact.BALANCE_BUH/10000000000.00,2) as "ОстатокНа1Число", paysum.FOpaysum as "Оплачено", round(sum(aas.price*v)/10000000000.00,2) as "Начислено" from ABONENTS AB left join FINANCE_OPERATIONS FOact on AB.ID = FOact.ABONENT_ID and FOact.OP_TYPE = 1 and (FOact.OP_DATE between ':1_C_даты|date$' and ':2_По_дату|date$ 23:59:59') left join ARCH_ACCOUNT_STACK aas on AB.ID = aas.ABONENT_ID and (aas.BILL_DATE between ':1_C_даты|date$' and ':2_По_дату|date$ 23:59:59') left join (select FOpay.ABONENT_ID as ABpay, round(sum(FOpay.OP_SUMMA)/10000000000.00,2) as FOpaysum from FINANCE_OPERATIONS FOpay where FOpay.OP_TYPE = 2 and FOpay.OP_DATE between ':1_C_даты|date$' and ':2_По_дату|date$ 23:59:59' and FOpay.ABONENT_ID=':3_Абонента$' group by 1 ) as paysum on ab.id = paysum.ABpay where (AB.DELETED = 0 or AB.DELETED is null) and FOact.STORNO=0 and ab.id=':3_Абонента$' group by AB.CONTRACT_NUMBER, AB.NAME,3,4 order by AB.CONTRACT_NUMBER, AB.NAME {code} # h5. Отчёт по абонентам без расхода с июня 2020 года Отчёт нужен чтобы найти абонентов у которых не было расхода с определённого времени (в примере с 06.2020). Список поможет абонентскому отделу принять решения по абонентам, например обзвонить и принять решения - удалить окончательно в корзину с закрытием договора или мотивировать продолжать пользоваться услугами. Основная задача по которой делали отчёт - провести "ревизию" неактивных абонентов и удалить в корзину, чтобы не тратили ограничение лицензии. В крайнем правомстолбце можно посмотреть когда был последний расход. {code} select distinct a.contract_number "Номер договора", a.name "ФИО/Название", coalesce((select max(year_number*100+month_number) from counters where abonent_id=a.id),'никогда не пользовался') "Последний период" from abonents a left join counters c -- соединяем чтобы найти абонентов с потреблением услуг в июне 2020 и позже, чтобы потом отсеить таких абонентов (дальше в where) on c.abonent_id=a.id and c.year_number=2020 and c.month_number>=06 where a.is_folder=0 -- не папки, у них не может быть расхода и в выводе отчёта они нам не нужны and a.deleted=0 -- удалённые вне контекста (они уже удалены и не учитываются в лицензии) and a.id>0 -- исключаем тестовых системных абонентов and coalesce(a.category_id,1)=1 -- считаем только абонентов, не операторо связи and a.parent_id<>'244' -- не учитываем служебную группу and cast(a.create_date_system as date)<cast('2020-07-01' as date) -- созданные в июле безусловно не могут иметь расхода за июнь и раньше, отсеиваем всех кто создан до "контрольного" месяца. and c.id is null -- считаем только те, по кому не нашлось счётчиков потребления услуг позде order by 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 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:Отладка отчетов]" |