|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Изменения (500)
просмотр истории страницыДля того, чтобы создать отчет, необходимо зайти в веб-интерфейс администратора Carbon Billing 5, раздел *Отчеты* и выбрать *Конструктор отчетов*. В открывшемся окне можно будет выбрать одну из вкладок, в которой будут создаваться отчеты. Для создания отчета необходимо выбрать папку и нажать на кнопку *Добавить*. |
{toc:maxLevel=2} |
|
!report1.png|border=1! |
h1. Описание конструктора отчетов |
|
Есть готовые отчеты, также можно создать свой отчет. |
*Конструктор отчетов* позволяет делать запросы напрямую к БД. Описание ограничений, возможностей и синтаксиса используемого диалекта SQL Вы можете найти в документации Firebird 2.5. Конструктор отчетов находится на вкладке "Отчеты" в основном меню биллинга \\ \\ !report_builder_1.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) |
|
Отличительная особенность отчета состоит в том, что можно указывать параметры. Если в настройках указать параметр (prm), то при нажатии запустить отчет, можно будет задать параметр. Задать название отчета и текст запроса, который вы можете уточнить у тех.поддержки (должен начинаться со слова "select"). !жур2.JPG|border=1! Данные в отчете можно выгрузить в csv. !жур3.JPG|border=1! |
Для выполнения отчета выберите формат отображения и нажмите кнопку "Выполнить запрос" \\ \\ !report_builder_3.png|border=1! |
|
h3. Параметры полей формы |
h2. Фильтры данных (параметры полей формы) |
|
Переменные, отображаемые в виде формы: |
В отчётах можно добавить фильтры - выбор информации, например счетов и актов, только за выбранные даты или период дат, отфильтровать по абонентам и тд. Фильтры подставляют указанные данные в тело отчёта и потом он выполняется. Фильтры указываются примерно по такой схеме: |
{code:lang=sql}':title|type[params]$'{code} * : - указывает, что это строка с переменной * title - заголовок, отображаемый на форме * \| - разделитель заголовка и типа |
* type\[params\] - тип с параметрами: |
** date - дата, без параметров, отображает на форме календарь выбора даты {code}BILL_DATE between ':Начало|date$' and ':Конец|date$'{code} ** choices - список {code:lang=sql}Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$' Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'{code} ** select - список, построенный на основе модели, подобно работе [API|CarbonBilling:API REST v2.0], параметры содержат модель (обязательно) и фильтры {code}a.parent_id = ':Группа|select[Abonents,is_folder=1]$' h.id = ':Заявка|select[HDSK]$'{code} * $ - флаг отмечает обязательную переменную |
* $ - флаг отмечает обязательную переменную, он обязателен для всех фильтров |
|
h3. Выполнение отчёта из консоли |
Ниже они описаны подробно. |
|
Для того, чтобы запустить выполнение отчета из консоли, необходимо: 1) Перейти в контейнер биллинга {code} chroot /app/asr_billing/ {code} 2) Запустить скрипт {code} python /usr/lib/python2.6/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 {code} Где 105 - id отчёта. Информация, выведенная на экран, будет иметь кодировку cp1251 |
h3. Выбор дат |
|
h3. Описание полей |
*date* - дата, указывается без параметров, отображает на форме календарь выбора даты |
|
К большенсту полей БД существуют описания. Описание всех полей можно получить выполнив команду *sqlexeс "show comments"*, например: {code}sqlexec "show comments" | head -n 5 COMMENT ON DOMAIN RAD_ATTRIB IS Radius-attribute; COMMENT ON COLUMN ABONENTS.ID IS №; COMMENT ON COLUMN ABONENTS.NAME IS Название/ФИО; COMMENT ON COLUMN ABONENTS.PARENT_ID IS Группа; COMMENT ON COLUMN ABONENTS.CONTRACT_NUMBER IS Номер договора;{code} Поля, отраженные в веб-интерфейсе (настройки абонента, тарифов, услуг и тд) как правило имею описание согласно названию. На примере настроек тарифа, вкладка "Опции", параметры Обещанного платежа: {code}sqlexec "show comments" | grep "Разрешать подключение обещанного платежа только один раз в текущий месяц" COMMENT ON COLUMN TARIF.PROMISE_PAY_ONLY_THIS_MONTH IS Разрешать подключение обещанного платежа только один раз в текущий месяц;{code} |
Прмер: {code}BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'{code} |
|
h2. {color:#000000}{*}Примеры:*{color} |
h3. Выбор периодов |
|
*Пример 1.* *Список всех доступных телефонов абонентов в формате (порядковый номер, логин абонента, номер телефона)* |
*monthchoice* - отобразит список периодов в формате "ММ.ГГГГ месяц_прописью" |
|
{code} Примеры: |
select id, login, SMS from users where DELETED=0 and sms is not null {code} |
* 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} |
|
*Пример 2.* *Список ФИО абонентов и их баланс на текущий момент* |
h3. Список вариантов |
|
{code} select ab.name as "Ф.И.О.", (ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс" from abonents ab left join admin_accounts ac on ab.account_id = ac.id left join users u on u.abonent_id = ab.id where ab.deleted<>1 and ab.is_folder<>1 and u.id is not null order by ab.name {code} |
*choices* - список, параметры - это элементы списка |
|
*Пример 3. Список всех доступных телефонов абонентов, у которых баланс меньше либо равен нулю.* |
Примеры: {code:lang=sql}Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$' owner_id in (:Администратор|choices[33^]root^[1005^]Василий^[1007,1005^]Василий или Михаил]$) Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'{code} |
|
{code} select SMS from users where (DEBIT + OSTATOK - CREDIT)<=0 and DELETED=0 and SMS is not null {code} |
h3. Список вариантов из справочников биллинга |
|
*Пример 4.* *Список всех доступных МАС-адресов абонентов, использующих услугу IPTV с известным id.* |
*select* - список, построенный на основе модели, подобно работе [API|CarbonBilling:API REST v2.0], параметры содержат модель (обязательно) и фильтры |
|
{code} Примеры: |
select mac from users join users_usluga on users_usluga.user_id = users.id where users_usluga.usluga_id = <id услуги> and users.mac is not null and users.mac \!= '' {code} |
{code}a.parent_id = ':Группа|select[Abonents,is_folder=1]$' h.id = ':Заявка|select[HDSK]$'{code} |
|
*Пример отчетов по VOIP.* {code:lang=sql|title=Журнал определенных звонков за текущий месяц} select SRC as "Номер абонента", DST as "Направление звонка", S_TIME as "Начало звонка", E_TIME as "Окончание звонка", DURATION as "Общая длительность", BILL_SEC as "Платное время", CHAN, BILLED as "Обработан", USER_ID as "Ид пользователя" from VOIP_LOG where USER_ID is not null and extract(month from E_TIME) = extract(month from current_timestamp) and extract(year from E_TIME) = extract(year from current_timestamp) order by E_TIME desc {code} {code:lang=sql|title=Журнал неопределенных звонков за текущий месяц}select SRC as "Номер абонента", DST as "Направление звонка", S_TIME as "Начало звонка", E_TIME as "Окончание звонка", DURATION as "Общая длительность", BILL_SEC as "Платное время", CHAN, BILLED as "Обработан", USER_ID as "Ид пользователя" from VOIP_LOG where USER_ID is null and extract(month from E_TIME) = extract(month from current_timestamp) and extract(year from E_TIME) = extract(year from current_timestamp) order by E_TIME desc {code} {code:lang=sql|title=VOIP Абоненты со статусом}select U.IDENTIFY as "Наименование", UU.SIP_ID as "Номер", U.ENABLED as "1-Включен/0-Выключен" from USERS_USLUGA UU left join USERS U on UU.USER_ID = U.ID where UU.SYSTEM_TYPE = 5 order by U.ID {code} |
h3. Строка для ввода произвольных данных: |
|
*Пример 5. Список ФИО и Адрес всех абонентов. у которых в качестве nas_ip указан адрес 172.16.1.177*: {code} select u.identify as "FIO", av.ATTRIBUTE_VALUE as "Address" from users u left outer join attribute_values av on av.user_id=u.id left outer join (users_radiusauth ur on ur.user_id=u.id and av.attribute_id=3) where uf_ip2string(ur.nas_ip_address)='172.16.1.177' {code} |
Отсутстие параметров предложет ввести произвольные данные. |
|
*Пример 6.* *Список{*}* всех абонентов в формате (id, должность, тариф, ФИО, дата внесения) за *{*}период с 2012-11-01 до 2012-11-30 )* |
{code} |
select u.id, av.attribute_value, tp.plan_name, u.identify, u.create_date from users u left outer join attribute_values av on av.user_id=u.id and av.ATTRIBUTE_ID=19 left outer join tarif_plan tp on tp.plan_id=u.TARIFF_ID_CACHE where (u.create_date between '2012-11-01' and '2012-11-30') |
':Введите число$' |
{code} |
*Пример 7.* *Список{*}* абонентов, созданных за* *заданный период в формате (*{*}id, должность, тариф, ФИО, дата создания) * : {code} select u.id, av.attribute_value from users u left outer join (attribute_values av on av.user_id=u.id and av.ATTRIBUTE_ID=19) where (u.create_date between '2012-11-01' and '2012-11-30') {code} |
h2. Выполнение отчёта из консоли скриптом make_reports.pyc |
|
*Пример 8. * *Список, соответствующих пользователям, кодов 1С из биллинга.* {code:lang=sql|title=1С коды}select U.ID as "Номер", U.IDENTIFY as "Наименование", U.CODE1C "Код синхронизации", U.SYNC1C as "1-синхронизирован/0-несинхронизирован" from USERS U where U.END_USER = 1 {code} |
Для того, чтобы запустить выполнение отчета из консоли, необходимо: # Перейти в контейнер биллинга {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) |
|
*Пример 9. Список расхода абонентов за заданный период с 01.11.2012 по 25.11.2012 в формате{*}* (id, финансовый договор, ФИО, расход)* {color:#ff0000}(Для закрытого периода){color} {code} select U.ID,U.CONTRACT_NUMBER,U.IDENTIFY,sum(FO.OP_SUMMA)/10000000000.00 from USERS U left join FINANCE_OPERATIONS FO on U.ID = FO.USER_ID where (FO.OP_TYPE <>2 and FO.OP_TYPE <>3) and (FO.OP_DATE between '2012-11-01' and '2012-11-30') group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY {code} |
У скрипта есть несколько опций и пресетов для выгрузок в [СОРМ3|Интеграция с СОРМ3] |
|
*Пример 10. Список расхода у не удаленных абонентов за заданный период с 2013-03-01 по 2013-03-31, у которых был трафик в формате {*}* (id, финансовый договор, ФИО, расход)* {color:#ff0000}(Для закрытого периода){color} {code} select distinct U.ID, U.CONTRACT_NUMBER, U.IDENTIFY, sum(FO.OP_SUMMA) / 10000000000.00 from USERS U left join FINANCE_OPERATIONS FO on U.ID = FO.USER_ID where (FO.OP_TYPE <> 2 and FO.OP_TYPE <> 3) and (FO.OP_DATE between '2013-03-01' and '2013-03-31') and exists(select first 1 1 from SESSIONS where ID = U.ID) and U.deleted=0 group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY {code} |
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 | \- | Экранировать только поля, внутри которых встречаются символ разделителя или символ экранирования | |
|
*Пример 11. Список расхода абонентов за текущий период в формате (id, финансовый договор, ФИО, расход)* {code} select id, contract_number, identify, credit/10000000000.00 from users where end_user=1 and id<100000 {code} |
|
*Пример 12.* *Список пользователей, подключенных за последний месяц (первый запрос) (период с 2011-11-20 до 2011-12-20 ) и их количества (второй запрос).:* |
h3. Пример запуска make_reports с опциями |
{code} |
SELECT id, identify, login, create_date FROM users WHERE create_date>'2011-11-20' AND end_user=1 AND create_date<'2011-12-20' {code} {code} SELECT COUNT(id) AS usercount FROM users WHERE create_date>'2011-11-20' AND end_user=1 AND create_date<'2011-12-20' {code} Список с выводом адреса {code} select U.ID, U.IDENTIFY, AV.ATTRIBUTE_VALUE as ADRES, CREATE_DATE from USERS U left join ATTRIBUTE_VALUES AV on AV.USER_ID = U.ID and AV.ATTRIBUTE_ID = 3 where CREATE_DATE > '2013-05-20' and END_USER = 1 and CREATE_DATE < '2013-06-20' {code} |
* Выгрузить отчет #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} |
|
*Пример 13.* *Перевод пользователей из подсетей 10.20.0.0/16...10.25.0.0/16 на Carbon AS с адресом 172.16.1.15* {code} update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.20.%' or uf_ip2string(host_ip) like '10.20.%' update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.21.%' or uf_ip2string(host_ip) like '10.21.%' update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.22.%' or uf_ip2string(host_ip) like '10.22.%' update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.23.%' or uf_ip2string(host_ip) like '10.23.%' update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.24.%' or uf_ip2string(host_ip) like '10.24.%' update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.25.%' or uf_ip2string(host_ip) like '10.25.%' {code} |
h2. Выполнение отчёта из консоли утилитой sqlexec |
|
*Пример 14.* *Список абонентов с рекомендуемой суммой к оплате* {code} select user_id, sum(OP_SUMMA/10000000000) from finaions where op_date>(current_timestamp-120) and op_type=2 group by user_id {code} |
Запрос к базе данных можно выполнить передав его утилите sqlexec, например так: |
|
*Пример 15.* *Список абонентов из заданной подсети 192.168.0.0/16* |
{code:title=Команда}sqlexec "select count(*) from abonents"{code} {code:title=Вывод} COUNT ============ 14 |
{code} |
select id, login from users where uf_ip2string(ip) like '192.168.%' |
Запрос можно написать в несколько строк: {code:title=Команда}sqlexec "select |
{code} count(*) |
from abonents"{code} Это удобно, если запрос сложный и объёмный. Вывод будет таким же, как у команды выше. |
|
*Пример 16.* *Список одинаковых ИНН* {code} select count(attribute_value), attribute_value from attribute_values where attribute_id=4 group by attribute_value having count(attribute_value)>1 {code} |
h2. Выполнение запроса для использования в скриптах |
|
*Пример 17.* *Список должников (Тариф, ФИО, Телефон, Адрес, Баланс)* {code} select (select tp.plan_name from tarif_plan tp where tp.plan_id=users.TARIFF_ID_CACHE) as "TARIF", USERS.IDENTIFY as "FIO", (select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from attribute_values where ATTRIBUTE_VALUES.USER_ID = USERS.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 3) as "ADRES", (select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from ATTRIBUTE_VALUES where ATTRIBUTE_VALUES.USER_ID = USERS.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 1) as "TELEFON", (DEBIT + OSTATOK - CREDIT)/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "BALANS" from USERS where OVER_LIMIT_DATE is not null {code} |
Тут возможны два варианта. |
|
*Пример 18.* *Всех пользователей подсети 10.1.0.0/16 перевести на NAS 172.0.1.1, прикрепив к нему, установить авторизацию по радиус и поменять SNAT адрес на 192.168.5.0.* {code} sqlexec "UPDATE users set snatip=uf_string2ip('192.168.5.0'), server=0, auth_type_cache=6, auth_type=6, nas_ip=uf_string2ip('172.0.1.1'), nas_ip_lock=1 WHERE uf_ip2string(ip) like '10.1.%'" {code} |
h3. С помощью sqlexec |
|
*Пример 19.* *Список номинала всех активированных карт за выбранный период* {code} select (card_series.card_value/10000000000) as NOMINAL , cards.* FROM CARDS LEFT JOIN CARD_SERIES ON CARDS.SERIES_NO = card_series.SERIES_NO WHERE cards.date_used > '2012-12-01 00:00:00' and cards.date_used < '2013-01-01 00:00:00' and cards.date_used is not null {code} |
Если отчёт достаточно простой, его можно выполнить с помощью sqlexec. Но нужно учитвать несколько моментов: * На самом деле утилита работает в контейнере asr_billing * Вывод лучше отформатировать для удобства написания скриптов и сохранения данных в переменные * Частично отформатировать Вывод можно средствами SQL: ** либо "*set heading off;*" - уберёт заголовки, это удобно если Вы выгружаете, например, список каких-то позиций (абонентов, финансовых операций) для обработки каждой в отдельности ** либо "*set list on;*" - "перевернёт" данные, отразит их в виде колонок вместо таблицы |
|
*Пример 20.* *Список итоговой суммы все{*}{color:#333333}{*}х активированных карт за{*}{color} *выбранный период* |
Ниже приведён скрипт, в котором показано как работать с выводом в том или ином виде, включая одиночные значения и списки: |
{code} |
select #!/bin/bash |
SUM(card_series.card_value)/10000000000 as ALL_PERIOD_SUM FROM CARDS LEFT JOIN CARD_SERIES ON CARDS.SERIES_NO = card_series.SERIES_NO WHERE cards.date_used > '2012-12-01 00:00:00' and cards.date_used < '2013-01-01 00:00:00' and cards.date_used is not null {code} |
|
*Пример 21.*{*}Список (Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по алфавиту* |
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}select ab.name as "FIO", uf_ip2string(u.ip) as "IP", uf_ip2string(u.nas_ip) as "NAS", (ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс" from users u left join abonents ab on u.abonent_id = ab.id left join admin_accounts ac on ab.account_id = ac.id where ab.deleted<>1 and ab.is_folder<>1 and u.id is not null order by ab.name{code} *Пример 22.*{*}Список (*{*}Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по ip* {code}select select ab.name as "FIO", uf_ip2string(u.ip) as "IP", uf_ip2string(u.nas_ip) as "NAS", (ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс" from users u left join abonents ab on u.abonent_id = ab.id left join admin_accounts ac on ab.account_id = ac.id where ab.deleted<>1 and ab.is_folder<>1 and u.id is not null order by u.ip{code} *Пример 23.* *Список (*{*}Ф.И.О. , Адрес, телефон для смс) с сортировкой фамилий по алфавиту* {code}select u.identify, (select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from attribute_values where ATTRIBUTE_VALUES.USER_ID = U.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 3) as "Address", u.sms from users u where u.deleted=0 and u.end_user=1 order by u.identify{code} *Пример 24.* *Список (*{*}Номер договора, Ф.И.О. , Баланс, Адрес, Тариф )* {code}select u.contract_number, u.identify, (DEBIT + OSTATOK - CREDIT)/10000000000.00 as "BALANS", (select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from attribute_values where ATTRIBUTE_VALUES.USER_ID = U.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 3) as "Address", tp.plan_name from users u left outer join tarif_plan tp on tp.plan_id=u.TARIFF_ID_CACHE |
{code} |
*Пример 25.* *Список ФИО абонентов, у которых запланирован переход в следующем месяце на новый тариф с указанием тарифа и сортировкой по Ф.И.О* |
h3. Если sqlexec не очень подходит, отчёт слишком сложный |
|
{code} select u.identify,tp.plan_name from users u left join tarif_plan tp on u.plan_next_id=tp.plan_id where u.plan_next_id is not null and u.plan_next_date>current_timestamp order by u.identify {code} |
Если отчёт слишком сложный и его удобней сохранить в файл, тогда сделайте следующее: # Убедитесь что запрос в файле заканчивается символом точки с запятой: ";" # Положите файл где-нибудь внутри контейнера asr_billing # Выполните запрос командой isql-fb, ключём -i укажите путь к файлу |
|
*Пример 26.* *Список Ф.И.О. абонентов, у которых взят Отложенный платеж и дата его окончания с сортировкой по Ф.И.О.* |
Например, можно положить скрипт в папку opt, внутри контейнера, с таким содержимым: {code:title=Файл /app/asr_billing/opt/abonents.sql} |
select |
count(*) from abonents where is_folder=0 ; |
{code} |
select u.identify,u.promise_date_end from users u where u.promise_pay is not null and u.promise_date_end>current_timestamp order by u.identify |
Тогда запрос к БД можно выплнить такой командой: {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*, убрав адрес корневой папки контейнера. |
{code} {note} |
Вывод отчёта Вы так же можете форматировать командными утилитами ОС, сохранять в переменные или передавать в циклы для дальнейшей обработки. |
|
h2. Описание полей |
|
*Пример 27.* *Список оплат через платежные системы (id, номер договора, Ф.И.О. абонента и внесенная сумма за период с 2013.01.01 с группировкой по группам)* {code} select U.ID, U.CONTRACT_NUMBER, U.IDENTIFY, sum(PL.SUMMA_IN) from PAY_LOG PL left join USERS U on PL.USER_ID_IN = U.ID where PL.MSG_OUT = 'ACCEPTED' and PL.OPERATOR_DATE_IN >= '2013.01.01' group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY {code} |
К большенсту полей БД существуют описания. Описание всех полей можно получить выполнив команду *sqlexeс "show comments"*, например: {code}sqlexec "show comments" | head -n 5 COMMENT ON DOMAIN RAD_ATTRIB IS Radius-attribute; COMMENT ON COLUMN ABONENTS.ID IS №; COMMENT ON COLUMN ABONENTS.NAME IS Название/ФИО; COMMENT ON COLUMN ABONENTS.PARENT_ID IS Группа; COMMENT ON COLUMN ABONENTS.CONTRACT_NUMBER IS Номер договора;{code} Поля, отраженные в веб-интерфейсе (настройки абонента, тарифов, услуг и тд) как правило имею описание согласно названию. На примере настроек тарифа, вкладка "Опции", параметры Обещанного платежа: {code}sqlexec "show comments" | grep "Разрешать подключение обещанного платежа только один раз в текущий месяц" COMMENT ON COLUMN TARIF.PROMISE_PAY_ONLY_THIS_MONTH IS Разрешать подключение обещанного платежа только один раз в текущий месяц;{code} |
|
*Пример 28.* *Список оплат через платежные системы по группам без учета web-интерфейса кассира (id, название группы и внесенная сумма за период с 2013.04.15 до 2013.04.17)* {code} select U.PARID, GU.IDENTIFY, sum(PL.SUMMA_IN) from PAY_LOG PL left join USERS U on PL.USER_ID_IN = U.ID or (PL.USER_ID_IN is null and PL.CONTRACT_NUMBER_IN = U.CONTRACT_NUMBER) left join USERS GU on U.PARID = GU.ID where PL.MSG_OUT = 'ACCEPTED' and (PL.OPERATOR_DATE_IN between '2013-04-15' and '2013-04-17') and PL.PAY_OPERATOR <> 'WEB-cash' group by U.PARID, GU.IDENTIFY {code} |
h2. Особенности SQL в Firebird |
|
*Пример 29.* *Список оплат через платежные системы по группам с учетом web-интерфейса кассира (id, название группы и внесенная сумма за период с 2013.04.15 до 2013.04.17)* {code} select U.PARID, GU.IDENTIFY, sum(PL.SUMMA_IN) from PAY_LOG PL left join USERS U on PL.USER_ID_IN = U.ID or (PL.USER_ID_IN is null and PL.CONTRACT_NUMBER_IN = U.CONTRACT_NUMBER) left join USERS GU on U.PARID = GU.ID where PL.MSG_OUT = 'ACCEPTED' and (PL.OPERATOR_DATE_IN between '2013-04-15' and '2013-04-17') group by U.PARID, GU.IDENTIFY {code} |
|
*Пример 30.* *Список должников (ФИО, дата последнего платежа, Баланс, Тариф{*}*, Адрес,*{*}Телефон)* |
h3. UNION (UNION ALL) и ORDER BY |
|
{code} select U.IDENTIFY as "FIO", (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) "last date", (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "BALANS", TP.PLAN_NAME as "TARIF", AV.ATTRIBUTE_VALUE as "ADRES", AV2.ATTRIBUTE_VALUE as "TELEFON" from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 left join ATTRIBUTE_VALUES AV2 on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 1 where U.OVER_LIMIT_DATE is not null {code} |
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" |
|
*Пример 31.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период c 2013.02.17 по 2013.02.18* |
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 U.IDENTIFY AS "FIO", U.login, FO.op_date as "DATE", FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "SUMMA", u2.identify as "OWNER" from finance_operations FO left join USERS U on FO.user_id = U.ID left join USERS U2 on FO.owner_id = U2.ID where (FO.op_type=2) and (cast(FO.op_date as date) between '2013.02.17' and '2013.02.18') {code} |
|
*Пример 32.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за текущий день* {code} select U.IDENTIFY AS "FIO", U.login, FO.op_date as "DATE", FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "SUMMA", u2.identify as "OWNER" from finance_operations FO left join USERS U on FO.user_id = U.ID left join USERS U2 on FO.owner_id = U2.ID where (FO.op_type=2) and (cast(FO.op_date as date)= current_date) and (FO.owner_id<>u.id) {code} |
h3. Ограничения в наименовании столбцов отчета {info}В случае использования кириллицы в названии столбцов отчета важно знать, что поле может содержать не более 18 символов.{info} Если требуется указать более развернутое название - используйте латиницу. Иначе, будет возникать [ошибка|https://docs.carbonsoft.ru/pages/viewpage.action?pageId=155714114#Отладкаотчетов-Привыполненииотчетавозниклаошибка"Произошлаошибкапоадресу] при выполнении отчета. |
|
*Пример 33.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период с 2012-09-20 по 2013-03-17 * {code} select U.IDENTIFY AS "FIO", U.login, FO.op_date as "DATE", FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "SUMMA", u2.identify as "OWNER" from finance_operations FO left join USERS U on FO.user_id = U.ID left join USERS U2 on FO.owner_id = U2.ID where (FO.op_type=2) and (FO.owner_id<>u.id) and (FO.op_date between '2012-09-20' and '2013-03-17') {code} |
|
*Пример 34. Список логинов и паролей абонентов* |
h2. Процедуры Carbon Soft |
|
{code} select id, identify, login, gen_pwd AS "password" from users where DELETED=0 and end_user=1 {code} |
Для работы с базой мы добавили ряд собственных процедур, которые помогут найти нужные данные и верно их отобразить в отчётах в соответствии с архитектурой биллинга. |
|
h3. Получение списка вложенных групп и абонентов в дереве групп |
|
h4. GLN_RECURSIVE_GROUP_WALK |
|
*Пример 35. Список пользователей, у которых активированы дополнительные услуги за все время работы (ФИО, название услуги, дата активации)* |
Принимает: GR_ID - ID группы Возвращает: GROUP_ID - ID запрошенной группы и всех её подгрупп |
|
{code} select USERS.IDENTIFY AS "FIO", USLUGA.NAME AS "USLUGA", USERS_USLUGA.CREATE_DATE AS "CREATE_DATE" from USERS, USLUGA, USERS_USLUGA where USLUGA.ID = USERS_USLUGA.USLUGA_ID and USERS.ID = USERS_USLUGA.USER_ID {code} |
h4. GLN_RECURSIVE_ABONENTS_GET |
|
*Пример 36. Список пользователей, у которых активированы дополнительные услуги за период с 2013.03.01 по 2013.04.01 (id, ФИО, название услуги, дата активации)* |
Принимает: GROUP_ID - ID группы Возвращает: ABONENT_ID - ID абонентов в указанной группе и всех её подгруппах |
|
{code} select USERS.ID, USERS.IDENTIFY, USLUGA.NAME, US.CREATE_DATE from USERS_USLUGA US left join USERS on US.USER_ID=USERS.ID left join USLUGA on US.USLUGA_ID=USLUGA.ID where USERS.ID is not null and US.CREATE_DATE between '2013.03.01' and '2013.04.01' order by USERS.ID {code} |
h4. Примеры запросов |
|
*Пример 37. Список пользователей, у которых активированы дополнительные услуги на данный момент(id, ФИО, название услуги)* |
{code:title=ID групп} SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(1) |
{code} |
select USERS.ID, USERS.IDENTIFY, USLUGA.NAME from USERS_USLUGA US left join USERS on US.USER_ID=USERS.ID left join USLUGA on US.USLUGA_ID=USLUGA.ID where USERS.ID is not null and US.activated=1 |
{code:title=ID абонентов} SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1) |
{code} |
*Пример 38. Отчет по сумме оплат за каждый день, внесенных через менеджер за период с 2013.03.01 по 2013.03.25* В консоли сервера выполните: 1. Открыть базу для редактирования {code} sqlexec "alter exception ERR 'Error '" {code} 2. Добавить в таблицу FINANCE_OPERATIONS поле OP_DATE_DAY {code} sqlexec "ALTER TABLE FINANCE_OPERATIONS ADD OP_DATE_DAY DATE" {code} 3. Закрыть базу для редактирования {code} sqlexec "alter exception ERR 'Error'" {code} Отчет строится из двух запросов. Запросы необходимо выполнять последовательно. запрос 1: {code} update FINANCE_OPERATIONS set op_date_day = op_date {code} запрос 2: {code} |
{code:title=Имена абонентов} |
select |
FO.op_date_day , a.name |
SUM(FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) )as "SUMMA" from finance_operations FO left join USERS U on FO.user_id = U.ID left join USERS U2 on FO.owner_id = U2.ID where (FO.op_type=2) and (FO.op_date_day between '2013.03.01' and '2013.03.25') group by FO.op_date_day order by FO.op_date_day |
from GLN_RECURSIVE_ABONENTS_GET(1) aids inner join abonents a on a.id=aids.abonent_id |
{code} |
*Пример 39. Изменить у абонентов тип авторизации с Radius на ip (адрес NAS - 192.168.0.222)* |
h1. Примеры отчётов |
|
# h5. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата |
{code} |
update users set AUTH_TYPE=1, AUTH_TYPE_CACHE=1, NAS_IP_LOCK=1, NAS_IP=uf_string2ip('192.168.0.222') where AUTH_TYPE_CACHE=6 {code} *Пример 40. Получить сумму по 2 услугам за период по пользователю услуга 10 и 11* {code} SELECT SUM(session_credit) FROM sessions WHERE (time_in between '2013-03-01' and '2013-04-01') AND id = 2564 AND (ruleset_no = -10 OR ruleset_no = -11) {code} *Пример 41. Список должников с последней оплатой (ФИО,Логин,Договор,Дата последнего платежа,Сумма последнего платежа,Текущий баланс)* Запрос: {code} select U.identify,U.login,U.contract_number,RPL.OP_DATE,RPL.OP_SUMMA,RPL.BALANCE_BUH from users U join REP_FINOPER_LAST(2) RPL on RPL.user_id=u.id {code} Предварительно Через [ibexpert|http://docs.carbonsoft.ru/pages/viewpage.action?pageId=3440679] или sqlexec сделать процедуру {code} SET TERM ^ ; create procedure REP_FINOPER_LAST1 ( OP_TYPE_IN integer) returns ( OP_ID integer, USER_ID integer, OWNER_ID integer, OP_TYPE integer, OP_DATE timestamp, OP_SUMMA numeric(18,0), OP_CARD_ID integer, SYSTEM_DATE timestamp, NUMBER varchar(10), BALANCE_BUH numeric(18,0), DESCR varchar(255), FINANCE integer, ISBUHDOC integer, END_USER integer, OPERATOR_PAY_ID integer, OPERATOR_NAME varchar(32), OPERATOR_RESULT integer, OPERATOR_PAY_ID_STR varchar(32), SYNC1C integer, CODE1C varchar(32), CURS double precision, SUM_IN numeric(18,0)) as declare variable TMP_OP_ID integer; begin for select max(OP_ID) from FINANCE_OPERATIONS where OP_TYPE = :OP_TYPE_IN group by USER_ID into TMP_OP_ID do begin select first 1 OP_ID, USER_ID, OWNER_ID, OP_TYPE, OP_DATE, OP_SUMMA, OP_CARD_ID, SYSTEM_DATE, NUMBER, BALANCE_BUH, DESCR, FINANCE, ISBUHDOC, END_USER, OPERATOR_PAY_ID, OPERATOR_NAME, OPERATOR_RESULT, OPERATOR_PAY_ID_STR, SYNC1C, CODE1C, CURS, SUM_IN from FINANCE_OPERATIONS where OP_ID = :TMP_OP_ID order by OP_ID desc into :OP_ID, :USER_ID, :OWNER_ID, :OP_TYPE, :OP_DATE, :OP_SUMMA, :OP_CARD_ID, :SYSTEM_DATE, :NUMBER, :BALANCE_BUH, :DESCR, :FINANCE, :ISBUHDOC, :END_USER, :OPERATOR_PAY_ID, :OPERATOR_NAME, :OPERATOR_RESULT, :OPERATOR_PAY_ID_STR, :SYNC1C, :CODE1C, :CURS, :SUM_IN; suspend; end end^ SET TERM ; ^ /* Following GRANT statetements are generated automatically */ GRANT SELECT ON FINANCE_OPERATIONS TO PROCEDURE REP_FINOPER_LAST; /* Existing privileges on this procedure */ GRANT EXECUTE ON PROCEDURE REP_FINOPER_LAST TO SYSDBA; {code} *Пример 42. Список абонентов в формате (Номер, Логин, Тарифный план, ФИО, Остаток на начало периода, Платеж через кассира, Расход, Расход по трафику, Остаток на конец периода) за период с 2013-05-01 по 2013-05-15* {color:#ff0000}{*}Доступен с версии 397_359{*}{color} {code} select CR.USER_ID "№", CR.LOGIN "Логин", CR.PLAN_NAME "Тарифный план", CR.USER_NAME "ФИО", CR.START_BALANCE "Остаток на начало периода", CR.PAY_SUM "Платеж через кассира", CR.SUM_CREDIT "Расход", CR.TRAF_SUM "Расход по трафику", CR.END_BALANCE "Остаток на конец периода" from CLN_REPORTS_USERS_INFO('2013-05-01', '2013-05-15') CR order by CR.LOGIN {code} *Пример 43. Список абонентов в формате (ID, ФИО, тарифный_план) у которых за период 2013-04-01 - 2013-05-01 был трафик больше 100Кб* {code} select distinct USERS.id, USERS.identify, TARIF_PLAN.plan_name from SESSIONS left join USERS on USERS.id = SESSIONS.id left join TARIF_PLAN on sessions.plan_id = TARIF_PLAN.plan_id where (SESSIONS.date_in between '2013-04-01' and '2013-05-01') and USERS.id<100000 and SESSIONS.v_in>102400 order by USERS.id {code} *Пример 44. Список должников (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по № договора* {code} select U.CONTRACT_NUMBER, U.IDENTIFY as FIO, AV.ATTRIBUTE_VALUE as ADRES, U.SMS as TELEFON, UF_IP2STRING(U.IP) as IP, TP.PLAN_NAME as TARIF, U.CREATE_DATE, (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_Date, (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS, U.LOGGED as STATUS from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 where U.OVER_LIMIT_DATE is not null order by U.CONTRACT_NUMBER {code} *Пример 45. Список абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.* {code} select U.CONTRACT_NUMBER, U.IDENTIFY as FIO, AV.ATTRIBUTE_VALUE as ADRES, U.SMS as TELEFON, UF_IP2STRING(U.IP) as IP, TP.PLAN_NAME as TARIF, U.CREATE_DATE, (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_Date, (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS, U.LOGGED as STATUS from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 order by U.IDENTIFY {code} *Пример 46. Список новых абонентов за период 2013-03-01 - 2013-03-30 (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по дате подключения.* {code} select U.CONTRACT_NUMBER, U.IDENTIFY as FIO, AV.ATTRIBUTE_VALUE as ADRES, U.SMS as TELEFON, UF_IP2STRING(U.IP) as IP, TP.PLAN_NAME as TARIF, U.CREATE_DATE, (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_PAY_Date, (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS, U.LOGGED as STATUS from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 where U.CREATE_DATE between '2013-03-01' and '2013-03-30' order by U.CREATE_DATE {code} *Пример 47. Список удаленных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.* {code} select U.CONTRACT_NUMBER, U.IDENTIFY as FIO, AV.ATTRIBUTE_VALUE as ADRES, U.SMS as TELEFON, UF_IP2STRING(U.IP) as IP, TP.PLAN_NAME as TARIF, U.CREATE_DATE, (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_PAY_Date, (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS, U.LOGGED as STATUS from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 where U.DELETED=1 order by U.IDENTIFY {code} *Пример 48. Список отключенных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата отключения, Баланс, Статус) сортировка по Ф.И.О.* {code} select U.CONTRACT_NUMBER, U.IDENTIFY as FIO, AV.ATTRIBUTE_VALUE as ADRES, U.SMS as TELEFON, UF_IP2STRING(U.IP) as IP, TP.PLAN_NAME as TARIF, U.CREATE_DATE, ((select max(op_time) from audit_operations where descr like '%Откл%' and op_type=120 and object_id=U.id )) as "Дата отключения", (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS, U.LOGGED as STATUS from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 where U.ENABLED=0 or U.DISABLED_DATE is not null order by U.IDENTIFY {code} *Пример 49. Список отключенных абонентов, не плативших с 2013.04.01 (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата отключения, Баланс, Статус) сортировка по Ф.И.О.* {code} select U.CONTRACT_NUMBER, U.IDENTIFY as FIO, AV.ATTRIBUTE_VALUE as ADRES, U.SMS as TELEFON, UF_IP2STRING(U.IP) as IP, TP.PLAN_NAME as TARIF, U.CREATE_DATE, ((select max(op_time) from audit_operations where descr like '%Откл%' and op_type=120 and object_id=U.id )) as "Дата отключения", (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS, U.LOGGED as STATUS from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 where (U.ENABLED=0 or U.DISABLED_DATE is not null) and U.DISABLED_DATE<'2013.04.01' order by U.IDENTIFY {code} *Пример 50. Список суммы должников (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.* {code} SELECT cast(U.CONTRACT_NUMBER as varchar(32)) as "№ договора", cast(U.IDENTIFY as varchar(128)) as "Ф.И.О", cast(AV.ATTRIBUTE_VALUE as varchar(1024)) as "Адрес", cast(U.SMS as varchar(32)) as "Телефон", cast(UF_IP2STRING(U.IP) as varchar(16)) as "IP", cast((U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as varchar(32)) as "Баланс", cast(cast(FIN.OP_DATE as date) as varchar(32)) as "Дата последней операции", cast(owner.identify as varchar(128)) as "Кем или чем проведена операция", cast(U.LOGGED as varchar(32)) as "Статус" from USERS U left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 left join FINANCE_OPERATIONS FIN ON FIN.op_id = (select max(FO.op_id) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) left join USERS owner ON owner.id = FIN.owner_id where (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) < 0 and u.end_user=1 and u.id<100000 and u.deleted=0 UNION ALL SELECT cast('' as varchar(32)) as "№ договора", cast('' as varchar(128)) as "Ф.И.О", cast('' as varchar(1024)) as "Адрес", cast('' as varchar(32)) as "Телефон", cast('' as varchar(16)) as "IP", cast('' as varchar(32)) as "Баланс", cast('' as varchar(32)) as "Дата последней операции", cast('Всего:' as varchar(128)) as "Кем или чем проведена операция", cast(sum((U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) as varchar(32)) as "Статус" from USERS U where (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) < 0 and u.end_user=1 and u.id<100000 and u.deleted=0 order by 2 {code} *Пример 51. Кол-во абонентов на каждом тарифе (Тариф, количество абонентов) сортировка по названию тарифа* {code} select tp.plan_name as "Тарифный план", (select count(1) from users u where u.tariff_id_cache=tp.plan_id and u.deleted=0 and u.end_user=1 and u.id<100000) as "Количество абонентов" from tarif_plan tp order by 1 {code} *Пример 52. Кол-во абонентов на каждой услуге (Тариф, количество абонентов) сортировка по названию услуги* {code} select us.name as "Услуга", (select count(1) from users u where u.id in (select user_id from users_usluga uu where uu.usluga_id=us.id) and u.deleted=0 and u.end_user=1 and u.id<100000) as "Количество абонентоd" from usluga us order by 1 {code} *Пример 53. Сумма внесенных средств за указанный период(№ договора, Ф.И.О, Адрес, телефон, IP, баланс, дата последней операции, кем или чем проведена операция) сортировка по дате последней операции в конце общая сумма всех операций* {code} SELECT cast(U.CONTRACT_NUMBER as varchar(32)) as "№ договора", cast(U.IDENTIFY as varchar(128)) as "Ф.И.О", cast(AV.ATTRIBUTE_VALUE as varchar(1024)) as "Адрес", cast(U.SMS as varchar(32)) as "Телефон", cast(UF_IP2STRING(U.IP) as varchar(16)) as "IP", cast((U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as varchar(32)) as "Баланс", cast(cast(FIN.OP_DATE as date) as varchar(32)) as "Дата последней операции", cast(owner.identify as varchar(128)) as "Кем или чем проведена операция", (select sum(ff.op_summa) from FINANCE_OPERATIONS ff where ff.op_date between '2000-06-01' and '2013-06-30' and ff.op_type=2 and ff.user_id=U.id and not ff.descr like '%Обещанный платеж%')/ (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "op_sum" from USERS U left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 left join FINANCE_OPERATIONS FIN ON FIN.op_id = (select max(FO.op_id) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID AND FO.op_type=2 AND FO.op_date between '2000-06-01' and '2013-06-30' and not FO.descr like '%Обещанный платеж%') left join USERS owner ON owner.id = FIN.owner_id where U.deleted = 0 AND U.end_user=1 AND u.id<100000 and FIN.op_id is not null UNION ALL SELECT cast('' as varchar(32)) as "№ договора", cast('' as varchar(128)) as "Ф.И.О", cast('' as varchar(1024)) as "Адрес", cast('' as varchar(32)) as "Телефон", cast('' as varchar(16)) as "IP", cast('' as varchar(32)) as "Баланс", cast('' as varchar(32)) as "Дата последней операции", cast('Всего:' as varchar(128)) as "Кем или чем проведена операция", sum(op_summa / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) as "Сумма операций" from FINANCE_OPERATIONS where op_date between '2000-06-01' and '2013-06-30' and op_type=2 and not descr like '%Обещанный платеж%' order by 7 {code} *Пример 54. Поиск абонента по MAC (52:54:00:BC:AA:45) (при условии что у абонента стоит статическая привязки) в формате id, ФИО, ip* {code} SELECT id, identify, uf_ip2string(ip) from USERS where MAC='52:54:00:BC:AA:45' {code} *Пример 55. Список должников(ФИО, IP)* Сортировка по ФИО {code} select identify, uf_ip2string(ip) from users where over_limit_date is not null and deleted=0 and id<100000 and end_user=1 order by 1 {code} Сортировка по IP {code} select identify, uf_ip2string(ip) from users where over_limit_date is not null and deleted=0 and id<100000 and end_user=1 order by 2 {code} *Пример 56. Список сессий по абоненту , пример по абоненту id 245 (id,IP-адрес,Начало сессии, конец сессии) с сортировкой по дате* Сортировка по ФИО {code} select ID, UF_IP2STRING(USER_IP) as IP, TIME_IN as "Начало сесии", TIME_OUT as "Конец сесии" from SESSIONS where ID = 245 and UF_IP2STRING(USER_IP) <> '0.0.0.0' order by DATE_IN {code} *Пример 57. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона* {code} select UR.user_id as ID, U.identify as FIO, AV.attribute_value as TELEFON from users_radiusauth UR left join USERS U on U.id=UR.user_id left join ATTRIBUTE_VALUES AV on AV.user_id=UR.user_id and AV.ATTRIBUTE_ID=1 where UF_IP2STRING(UR.NAS_IP_ADDRESS)='172.16.0.6' order by U.identify {code} *Пример 58. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата* {code} |
select LOGIN_IN as LOGIN, USER_NAME_OUT as FIO, |
... |
order by USER_NAME_OUT {code} |
*Пример 59. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, IP коммутатор, Номер порта, Номер vlan) с группировкой по номеру коммутатора* |
# h5. Общее количество абонентов по группам |
{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} *Пример 60. Список абонентов, не плативших в течение 3-х месяцев (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата блокировки по балансу, Баланс, Статус) сортировка по Ф.И.О.* {code} select U.CONTRACT_NUMBER, U.IDENTIFY as FIO, AV.ATTRIBUTE_VALUE as ADRES, U.SMS as TELEFON, UF_IP2STRING(U.IP) as IP, TP.PLAN_NAME as TARIF, U.CREATE_DATE, U.over_limit_date as "Дата блокировки", (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS, U.LOGGED as STATUS from USERS U left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3 where U.over_limit_date < (current_timestamp -90) and U.end_user=1 order by U.IDENTIFY {code} *Пример 61. Общее количество абонентов по группам* {code} select |
name as "Группа", (select count(1) from abonents where parent_id=grp.id) as "Количество абонентов" |
... |
where is_folder=1 {code} |
|
*Пример 62. # h5. Средняя выручка(в расчёте на одного абонента) за предыдущий и текущий месяц или с возможностью выбора периода* |
{code} select |
... |
group by year_number,month_number {code} |
|
*Пример 63. # 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} |
|
*Пример 64. # h5. Отчет по списаниям по абонентам в формате (число, id группы, название группы, сумма и кол-во абонентов, по которым были списания) и последней строкой сумма за день.* |
{code} select C.DT, |
... |
order by 1, 2, 3 {code} |
|
*Пример 65. # h5. Отчет по должникам для отключения КТВ (Улица, Дом, Подъезд, Квартира, ФИО, Тариф) игнорируя ручной статус "Отключен"* |
{code} select |
... |
order by 6 {code} |
*Пример 66. Отчет по свободным IP адресам* |
# h5. Отчет по свободным IP адресам |
{code} select uf_ip2string(pc.ip) as "IP адрес", |
... |
order by pc.pull_id, pc.ip {code} |
*Пример 67. Кто платит через Юнителлер?* |
# h5. Кто платит через Юнителлер? |
{code} select distinct(pay_id_str_in) as "PAY", |
CONTRACT_NUMBER_IN as "Договор",Пример |
USER_NAME_OUT as "ФИО", operator_date_in as "Дата", |
... |
order by user_name_out {code} |
*Пример 68. Количество услуг из ЛК за месяц по абонентам* |
# h5. Количество услуг из ЛК за месяц по абонентам |
{code} select A.ID, |
... |
order by A.NAMEe_in between ':C_даты|date$' and ':По_дату|date$' {code} |
\*Пример 69. # h5. Прибыль с комиссии за обещанный платеж (все периоды) |
{code} select A.NAME as "ФИО", |
... |
order by 1 |
{code}\*Пример 69. Прибыль с комиссии за обещанный платеж (по периодам) |
{code} |
# h5. Прибыль с комиссии за обещанный платеж (по периодам) {code} |
select A.NAME as "ФИО", A.CONTRACT_NUMBER as "Номер договора", |
... |
order by 1 {code} |
|
\*Пример 70. # h5. Просмотр пользователей в онлайне с выводом времени онлайна |
{code} select cast(U.LOGIN as varchar(128)) as "логин", |
... |
order by 1 {code} |
*Пример 71.* # h5. Абоненты, которые в начале следующего месяца будут заблокированы (при условии что лимит выставлен отричательным числом, в противном случае минус перед ним нужно убрать) |
|
{code} select distinct A.CONTRACT_NUMBER as "номер договора", |
A.NAME as "ФИО", H.STREET as "Улица", H.S_NUMBER as "Номер дома", A.HOME_ENTRANCE as "Номер подъезда", A.A_HOME_NUMBER as "Номер квартиры", A.SMS as "номер для смс", T.NAME as "Тариф" |
A.NAME as "ФИО", H.STREET as "Улица", H.S_NUMBER as "Номер дома", A.HOME_ENTRANCE as "Номер подъезда", A.A_HOME_NUMBER as "Номер квартиры", A.SMS as "номер для смс", T.NAME as "Тариф" |
from ABONENTS A left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID |
... |
left join TARIF T on A.TARIF_ID = T.ID where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE |
from VPN_CONST where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) and A.IS_FOLDER = 0 and (select count(1) from ABONENTS_BLOCK AB where AB.ABONENT_ID = A.ID and (AB.B_NEGBAL != 1 or AB.B_OWN != 1 or AB.B_ADMIN != 1 or AB.B_SYS != 1)) = 0 |
from VPN_CONST where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) and A.IS_FOLDER = 0 and (select count(1) from ABONENTS_BLOCK AB where AB.ABONENT_ID = A.ID and (AB.B_NEGBAL != 1 or AB.B_OWN != 1 or AB.B_ADMIN != 1 or AB.B_SYS != 1)) = 0 |
union select '#ВСЕГО', |
'', '', '', '', '', '', count(A.ID) |
'', '', '', '', '', '', count(A.ID) |
from ABONENTS A left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE |
from VPN_CONST where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) and A.IS_FOLDER = 0 and (select count(1) from ABONENTS_BLOCK AB where AB.ABONENT_ID = A.ID and (AB.B_NEGBAL != 1 or AB.B_OWN != 1 or AB.B_ADMIN != 1 or AB.B_SYS != 1)) = 0 {code}{*}Пример 72. Список IP адрес и номер договора всех абонентов. у которых в качестве nas_ip указан адрес 192.168.1.2*: |
from VPN_CONST where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) and A.IS_FOLDER = 0 and (select count(1) |
from ABONENTS_BLOCK AB |
where AB.ABONENT_ID = A.ID and (AB.B_NEGBAL != 1 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: |
select {code} |
select |
uf_ip2string(u.ip) as "IP", a.CONTRACT_NUMBER as "Номер договора" |
from users u u |
left join abonents a on u.abonent_id=a.id left join nas n on u.nas_id=n.id where uf_ip2string(n.ip)='192.168.1.2' and a.deleted=0 |
{code}{*}Пример 73. Список абонентов, взявших больше одного обещанного платежа:* |
{code} # h5. Список абонентов, взявших больше одного обещанного платежа |
{code:lang=sql}select distinct AB.NAME as "ФИО", AB.CONTRACT_NUMBER as "Номер договора", |
... |
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 UUA.DELETED = 0 and ABA.ID = AB.ID) > 1 |
order by 3{code}{*}Пример 74. *{color:#000000}{*}Отчет по абонентам со статусом "подключен":*{color} |
order by 3{code} # h5. Отчет по абонентам со статусом "подключен" |
{code}select count(distinct ab.id) as "Кол-во абонентов" |
... |
ab.is_folder = 0 and exists(select 1 from users where nas_id=70 and abonent_id=ab.id and ip is not null){code} |
*Пример 75. Отчет по абонентам со статусом "не подключен":* |
# h5. Отчет по абонентам со статусом "не подключен" |
{code}select count(distinct ab.id) as "Кол-во абонентов" |
... |
where ab.deleted = 0 and |
ab.is_folder = 0{code}{*}Пример 76. Отчет по абонентам у которых сегодня списалась абонентская плата:* |
ab.is_folder = 0{code} # h5. Отчет по абонентам у которых сегодня списалась абонентская плата |
{code}select count(distinct ab.id) as "Кол-во абонентов" |
... |
where credit > 0 and |
cast(bill_date as date) = current_date{code}{*}Пример 77. *{color:#000000}{*}Отчет выводящий абонентов должников и в статусе "отключен" по каждому дому в отдельности:*{color} {code}select CITY, |
cast(bill_date as date) = current_date{code} # h5. Отчет выводящий абонентов должников и в статусе "отключен" по каждому дому в отдельности: {code}select CITY as "Город", |
STREET as "Улица", |
SNUMBER as "Дом", |
AHOMENUMBER as "Квартира", |
FIO as "ФИО", |
PHONE as "Телефон", |
TNAME as "Тариф", |
round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / cast((select CONST_VALUE from VPN_CONST |
... |
where A.DELETED != 1 |
and H.STREET like ':Улица$' and H.S_NUMBER like ':Дом$' |
and H.id=':Дом|select[homes]$' |
group by 1, 2, 3, 4, 5, 6, 7, 8) left join ADMIN_ACCOUNTS AA on AAID = AA.ID where round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / 10000000000.00, 2) <= :Баланс ниже$ |
order by 1, 2, 3, 4, 5{code}{*}Пример 78. О{*}{color:#000000}{*}тчет "абоненты с положительным балансом":*{color} 5 |
{code} |
|
# h5. Отчет "абоненты с положительным балансом" |
{code}select a.contract_number as "Номер договора", |
... |
where (aa.ostatok + aa.debit - aa.credit)/10000000000.00 > 0 |
order by (aa.ostatok + aa.debit - aa.credit)/10000000000.00 desc{code}{*}Пример 79. Отчет "Абоненты по услуге":* |
# h5. Отчет "Абоненты по услуге" |
{code}select contract_number as "Номер договора", |
... |
left join homes h on h.id = a.home_id inner join users_usluga uu on uu.abonent_id = a.id |
where uu.usluga_id = :ID Услуги${code}{*}Пример 80. Отчет "Абоненты по тарифу":* |
where uu.usluga_id = ':Услуга|select[Usluga]$' {code} # h5. Отчет "Абоненты по тарифу" |
{code}select a.contract_number as "Номер договора", |
... |
left join homes h on h.id = a.home_id where a.tarif_id = :ID Тарифа$ |
{code}{*}Пример 81. Отчет "Пресса", *{color:#000000}{*}цель отчета раздать списки почтальонам, кому из абонентов приносить газету.*{color} |
{code} # h5. Отчет "Пресса", цель отчета раздать списки почтальонам, кому из абонентов приносить газету. |
{code}select h.s_number as "Дом", |
... |
(current_timestamp - ab.b_date) < 90)) group by h.s_number{code} |
h6. |
h6. Пример 82. # h5. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам. |
h6. |
{code}select first 1 (':Начало|date$') as "Период начало", |
... |
) as "Расторженные" from send_type{code} |
|
h6. {color:#000000}{*}Пример 83. # h5. Отчет по должникам с возможностью выбора тарифа. В отчете: номер договора, ФИО, телефон абонента, адрес, тариф, статус, дата последнего платежа, баланс.*{color} |
h6. h5. |
{code}select AB.CONTRACT_NUMBER as "№ договора", |
... |
and TP.ID=':Тариф|select[Tarif]$' order by U.CONTRACT_NUMBER{code} |
|
h6. Пример 84. # h5. Отчет должники по постоплате с разделением по физическим и юридическим лицам. Отчет содержит: номер договора, ФИО, адрес, дата последнего платежа, сумма долга (рекоммендованный платеж по постоплате). |
h6. |
{code:lang=sql}select first 1 cast('******************************************' as varchar(100) ) as "№ договора", |
... |
and AB.COMPANY = 1 and (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0{code} |
h6. Пример 85. Отчет по абонентам, у которых должна произойти смена тарифа. h6. |
# h5. Отчет по абонентам, у которых должна произойти смена тарифа |
{code:lang=sql}select A.ID as "ID абонента", A.NAME as "ФИО", |
... |
left join TARIF T on A.TARIF_ID=T.ID where A.TARIF_NEXT_DATE between ':День смены тарифа|date$' and ':День смены тарифа|date$ 23:59:59 '{code} |
|
h6. Пример 86. # h5. Сумма платежей через платежные системы "Жилищное управление", "Yandex" за каждый день в периоде между Датой1 и Датой2. |
h6. |
{code:lang=sql}with PERIOD as (select distinct cast(PLS.DATE_CREATE as date) as DAT |
... |
group by PERIOD.DAT{code} |
|
h6. Пример 87. # h5. Отчет "Выставленные счета физическим лицам". В отчете: сумма в счете, ФИО абонента, описание финансовой операции. |
h6. |
{code:lang=sql}select ((fo.op_summa)/(select const_value from vpn_const where const_id=1)) as "Сумма", ab.name as "ФИО", |
... |
and ab.company=0 and fo.op_date between cast(':Начало|date$' as date) and cast(':Конец|date$' as date){code} |
|
h6. Пример 88. "Отчёт # h5. Отчёт о превышении лимита предоплаченного трафика по юридическим лицам с возможностью выбора месяца - года" |
|
h6. SQL запрос |
h6. |
{code:lang=sql}select distinct ab.name, |
... |
join traf_counters on ab.id=traf_counters.abonent_id where traf_counters.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and traf_counters.year_number LIKE upper(':Год$') and ab.company = 1{code} |
|
h6. Шаблон отчёта {code:lang=sql}{% extends "form_list.html" %} |
... |
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" /> {% endblock %}{code} |
h6. Пример 89. "Отчёт о динамике приходов и актов за заданный период времени" |
# h5. Отчёт о динамике приходов и актов за заданный период времени |
h6. SQL запрос |
h6. |
{code:lang=sql}select fo.SYSTEM_DATE as "Дата", |
... |
and fo.op_type in (1,2){code} |
|
h6. Шаблон отчёта {code:lang=sql}{% extends "form_list.html" %} |
... |
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/> {% endblock %}{code} |
h6. Пример 90. "Отчёт по пользователям с добровольной блокировкой" |
# h5. Отчёт по пользователям с добровольной блокировкой |
h6. SQL запрос |
h6. |
{code:lang=sql}select a.name as "ФИО", a.CONTRACT_NUMBER as "Номер договора", |
... |
join ABONENTS_BLOCK as ab on ab.ABONENT_ID=a.ID where ab.b_own=1{code} |
h6. Пример 91. "Список пользователей с сортировкой по ФИО (ФИО, логин, IP, Город, Улица, Дом)" |
# h5. Список пользователей с сортировкой по ФИО (ФИО, логин, IP, Город, Улица, Дом) |
{code:lang=sql} select |
... |
order by A.NAME {code} |
h6. Пример 92. Отчёт в формате "Логин\- Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения. По текущей дате" |
# h5. Отчёт в формате "Логин - Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения". По текущей дате |
{code:lang=sql} select distinct |
... |
and tc.currentt=1 {code} |
h6. Пример 93. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг". |
# h5. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг. |
{code:lang=sql} select distinct A.CONTRACT_NUMBER as "Договор", |
... |
where AB.B_DATE between (':1 Дата|date$') and (':2 Дата|date$') {code} |
|
h6. Пример 94. # h5. Отчёт по должникам по телефонии в формате " № - Папка - ФИО - Телефонный номер - Дата последней оплаты - Сумма долга" |
{code:lang=sql} select |
... |
where rdb$set_context('USER_TRANSACTION', 'row#', coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0)+1) > -1 {code} |
|
h6. Пример 95. # h5. Отчёт по скачанному трафику за период. |
{code:lang=sql} select distinct |
... |
group by users.login, tarif.name {code} |
h6. Пример 96. Отчёт по платежам для групп абонентов. |
# 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} |
select a.name as "ФИО", a.contract_number as "Договор", |
... |
and a.parent_id=(':ID папки$') {code} |
|
h6. Пример 97. # h5. Отчёт для платежной системы "Город", выводящий в строку "ФИО;Адрес;Лицевой счет;Текущий остаток;';;;;'" и выводящий в Шапке отчёта : |
|
\#FILESUM #* FILESUM - Сумму всех остатков в данном отчёте |
\#TYPE 7 \#SERVICE 10240 |
#* TYPE 7 #* SERVICE 10240 |
по всем физ.лицам {code:lang=sql} |
... |
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} |
h6. # h5. Пример 98. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number) |
{code:lang=sql} {% extends "form_list.html" %} |
... |
{% endblock %} {code} |
|
h6. Пример 99. # h5. Отчёт по определенной услуге. Абонент, логин, баланс, конец действия услуги. |
{code:lang=sql} select USERS.LOGIN as "Логин", |
... |
order by ABONENTS.NAME {code} |
|
h6. Пример 100. # h5. Отчёт по абонентам в формате "ФИО ЛОГИН IP MAC ТЕЛЕФОН АДРЕС ТАРИФ БАЛАНС" |
{code:lang=sql} select distinct |
... |
order by A.NAME {code} |
|
h6. Пример 101. # h5. Отчёт, выводящий сумму списаний, взятых из выставленных актов, за определенный период по всем абонентам. |
{code:lang=sql} select sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма " |
... |
and op_date between (':1 Дата|date$') and (':2 Дата|date$') {code} |
|
h6. Пример 102. # h5. Отчет для вывода рекомендуемого ежемесячно платежа, стоимости тарифа по всем абонентам. |
{code} select |
... |
and a.parent_id != 2 {code} |
|
h6. Пример 103. # h5. Отчёт по платежам через веб - кассу(По операторам) |
{code} select AU.USERNAME as "Администратор", |
... |
where (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$') and AU.USERNAME = ':Администратор$' {code} |
|
h6. Пример 104. # h5. Поле "Не отключать при превышении порога", ID, ФИО. |
{code} select |
... |
order by a.NAME {code} |
|
h6. Пример 105. # h5. Учёт заключенных договоров за период |
{code} select |
... |
where a.IS_FOLDER=0 and a.CREATE_DATE between ':1 Дата|date$' and ':2 Дата|date$' |
{code}{*}Пример 106. Телефония. Количество звонков, ожидающих перерасчет.* |
{code} |
select count(1) from voip_log where recalc=1 |
# h5. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)" |
{code} |
h6. Пример 107. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)" {code} |
select NAME, UF_IP2STRING(START_IP) as "Начальный IP", |
... |
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 |
... |
order by NAME {code} |
h6. Пример 108. # 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} |
h6. Пример 109. # h5. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%" |
{code} select AU.USERNAME as "Администратор", |
... |
and (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$') {code} |
h6. Пример 110. # 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} |
h6. Пример 111. # h5. Сформированные акты/счета по юридическим лицам. |
{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} |
h6. Пример 112. # h5. Отчёт по юридическим лицам, выводящий ФИО Телефон Адрес Р/С ИНН КПП |
{code} select A.NAME as "ФИО", 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 |
... |
and a.company=1 {code} |
h6. Пример 113. # h5. Отчёт прибыль по плате за подключение нескольких услуг. |
{code} |
select |
a.NAME as "ФИО", uu.ABONENT_ID as "ID АБОНЕНТА", |
... |
and c.SUMM>0 {code} |
h6. Пример 114. # h5. Отчёт выводит абонентов, последнее подключение которых было раньше, чем указанная дата. |
{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} |
h6. Пример 115. # h5. Отчёт выводит информацию по абонентам с определенным NAS'ом. |
{code} select distinct |
... |
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 |
... |
and uf_ip2string(nas.ip)=(':nas_ip$') {code} |
|
h6. Пример 116. # 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} |
h6. Пример 117. Поиск IP-адреса в истории изменения учетных записей (история выдачи ip-адреса) |
# h5. Поиск 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} |
|
h6. Пример 118. # h5. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс" |
{code} select distinct |
... |
order by A.NAME {code} |
h6. Пример 119. # h5. Отчёт по платившим абонентам в формате "Номер договора - ФИО - Группа - Дата и время операции - Описание - Сумма" с выводом итоговой суммы и возможностью выбора платежей по конкретной папке. |
{code} select AB.CONTRACT_NUMBER as "Номер договора", |
... |
or AB.DELETED is null)) {code} |
h6. Пример 120. Отчёт для СОРМ3. Активированные пользователями дополнительные услуги (услуги, не включенные в тариф) services без учета системных обещанных платежей. Формат "логин";"номер договора";"идентификатор услуги";"дата и время подключения услуги";"дата и время отключения";"" |
# h5. Отчет выводящий информацию из карточки абонента с текущим статусом; колонки административной и добровольной блокировками заполняются только в случае активной блокировки; в последней колонке пишется дата, до которой действительна добровольная блокировка |
{code} |
select distinct '"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || UU.USLUGA_ID || '"' || ';'|| '"' || CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE) || ' 00:00:00' || '"' || ';' || '"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.END_TIME) || ' ' || EXTRACT(HOUR FROM UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""' from ABONENTS A left join USERS U on A.ID = U.ABONENT_ID left join HOMES H on A.HOME_ID = H.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_HISTORY UU on UU.ABONENT_ID=A.ID where A.IS_FOLDER=0 and UU.ENABLE_DATE is not null and UU.TARIF_ID is null and UU.USLUGA_ID!=-4 and UU.USLUGA_ID!=-3 and A.PARENT_ID!=244 and A.DELETED=0 order by A.NAME {code} Тот же отчёт, который выводит только услуги подключенные за последний час {code} select distinct '"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || UU.USLUGA_ID || '"' || ';'|| '"' || CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE) || ' 00:00:00' || '"' || ';' || '"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.END_TIME) || ' ' || EXTRACT(HOUR FROM UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""' from ABONENTS A left join USERS U on A.ID = U.ABONENT_ID left join HOMES H on A.HOME_ID = H.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_HISTORY UU on UU.ABONENT_ID=A.ID where A.IS_FOLDER=0 and UU.ENABLE_DATE is not null and UU.TARIF_ID is null and UU.USLUGA_ID!=-4 and UU.USLUGA_ID!=-3 and A.PARENT_ID!=244 and A.DELETED=0 and UU.TIME_CHANGED between (current_timestamp - 1/24) and current_timestamp order by A.NAME {code} h6. Отчёт 121. Отчёт для СОРМ3. Пополнение баланса личного счета абонента обобщенная информация. Отчёт выводит информацию в виде "1";"идентификатор способа оплаты";"номер договора ";"статический IP-адрес";"дата и время пополнения баланса";"сумма";"" В данном отчёте идентификаторы способа оплаты зависят от описания платежа: Описание Gorod - 1 Описание Paymaser - 2 Остальное - 3 {code} select '"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME) WHEN 'GOROD' THEN '1' WHEN 'PAYMASTER' THEN '2' ELSE '3' END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM FO.OP_DATE) || ' 00:00:00' || '"' || ';' || '"' || 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 AUTH_USER AU on FO.owner_id = AU.ID left join Abonents a on A.id=FO.abonent_id left join users u on u.abonent_id=a.id where fo.OP_TYPE=2 and A.PARENT_ID!=244 and A.DELETED=0 {code} Тот же отчёт, который выводит платежи, проводимые за последний час {Code} select '"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME) WHEN 'GOROD' THEN '1' WHEN 'PAYMASTER' THEN '2' ELSE '3' END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM FO.OP_DATE) || ' 00:00:00' || '"' || ';' || '"' || 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 AUTH_USER AU on FO.owner_id = AU.ID left join Abonents a on A.id=FO.abonent_id left join users u on u.abonent_id=a.id where fo.OP_TYPE=2 and A.PARENT_ID!=244 and A.DELETED=0 and DATEADD(-1 hour to current_timestamp) - FO.SYSTEM_DATE < 1/24 {Code} h6. Пример 122. Отчёт для СОРМ3. Отчёт выводит дополнительные услуги в формате "идентификатор услуги ";"название/обозначение в Вашей системе";"дата создания услуги в биллинге";"";"описание"; Отчёт {code} select distinct '"' || USL.ID || '"' || ';' || '"' || USL.NAME || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)) || ' 00:00:00' || '"' || ';' || '""' || ';' || '"' || USL.NAME || '"' from USLUGA USL left join USERS_USLUGA_HISTORY UU on UU.USLUGA_ID=USL.ID and USL.SYSTEM_TYPE!=8 and USL.SYSTEM_TYPE!=9 and USL.SYSTEM_TYPE!=13 and USL.SYSTEM_TYPE is not null order by USL.ID {code} Шаблон отчёта {code} {% extends "form_list.html" %} {% block addonhead %} <style> .btn-container { padding: 10px; } </style> <script> $(function () { {# debugger;#} var $select = $('select'); {# $select.wrap('<div class="span12"></div>');#} $select.select2({ containerCssClass : "span6" }); }) </script> {% endblock %} {% load field_type %} {% block content %} <form method="POST"> <div id="params"> {% csrf_token %} <input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/> {% if form %} <div class="row-fluid"> <div class="span6"> <legend><b>Заполните поля запроса</b></legend> {% for field in form %} <div class="control-group clearfix"> {{ field.label_tag }} <div class="controls"> {{ field }} </div> </div> {% endfor %} </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 %} <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" width="100%"> <tr> {% for field in field_desc %} <th>{{ field|get_zero }}</th> {% endfor %} </tr> {% for row in data %} <tr> {% for cell in row %} {% if cell %} <td>{{ cell }}</td> {% endif %} {% endfor %} </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} Тот же отчёт, выводящий услуги, созданные за прошедшие сутки: {code} select distinct '"' || USL.ID || '"' || ';' || '"' || USL.NAME || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)) || ' 00:00:00' || '"' || ';' || '""' || ';' || '"' || USL.NAME || '"' from USLUGA USL left join USERS_USLUGA_HISTORY UU on UU.USLUGA_ID=USL.ID left join USLUGA_HISTORY as uh1 on UH1.USLUGA_ID=USL.ID WHERE USL.SYSTEM_TYPE!=8 and USL.SYSTEM_TYPE!=9 and USL.SYSTEM_TYPE!=13 and USL.SYSTEM_TYPE is not null and DATEADD(-1 day to current_timestamp) - (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id) < 1 order by USL.ID {code} h6. Пример 123. Отчет выводящий информацию из карточки абонента с текущим статусом; колонки административной и добровольной блокировками заполняются только в случае активной блокировки; в последней колонке пишется дата, до которой действительна добровольная блокировка {code} |
select distinct AB.CONTRACT_NUMBER, AB.NAME as "Subscriber", |
... |
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} |
|
h6. Пример 124. # 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 {code} |
|
h6.Пример 125. # h5. Отчет выводит абонентов не имеющих операции приход до указанной даты. Поиск по конкретной папке. |
{code} select distinct |
... |
and a.deleted=0 {code} |
h6. Пример 126. # 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} |
h6. Пример 127. # h5. Отчёт по абонентам с блокировкой по отрицательному балансу. |
{code} select |
... |
and uu.deleted=0 {code} |
h6. Пример 128. # h5. Отчёт в формате "ФИО-номер договора-телефон-адрес" |
{code} select distinct |
... |
order by A.NAME {code} |
h6. Пример 129. # 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} |
h6. Пример 130. # h5. Отчёт о списаниях абонентской платы по папкам за определенный период. |
{code} select (select name from abonents a1 where a1.id=a.parent_id), sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма " |
... |
group by a.parent_id {code} |
h6. Пример 131. # 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" %} |
... |
{% endblock %} {code} |
h6. Пример 132. # h5. Потребленный трафик суммарно по всем абонентам с возможностью выбора периода |
{code}SELECT FIRST 1 (SELECT cast(sum(v) AS numeric(18,2)) AS "Входящий" |
... |
AND V_TYPE_ID = 2) FROM counters{code} |
# h5. Поиск абонентов со скидкой, с выводом самой скидки, id абонента, id услуги и фио абонента. |
{code}select |
h6. Пример 133. Поиск абонентов со скидкой, с выводом самой скидки, 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} |
h6. Пример 134. # 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 |
... |
group by a.id, a.name, a.contract_number, h.city {code} |
h6. Пример 135. # h5. Отчёт по всем финансовым операциям по конкретному администратору за выбранный период времени. |
{code} select AU.USERNAME as "Администратор", |
... |
and FO.owner_id=:Администратор|select[AdminUser]$ {code} |
|
h6. Пример 136. # h5. Отчёт по исходящему телефонному трафику за выбранный период |
Отчёт выводит сверку по потреблению услуги телефонного трафика с разделением по категориям и абонентам. {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 |
... |
order by a.contract_number {code} |
h6. Пример 137. # 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} |
h6. Пример 138. # 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} |
|
h6. Пример 139. # 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} |
h6. Пример 140. Отчёт заблокированным абонентам: ФИО абонента, дата последней оплаты. {code}select abonname as "ФИО", iif(lastpay is null, 'Никогда не платил',lastpay) as "Последняя оплата" from (select a.name as abonname, max(fo.op_date) as lastpay from abonents a left join finance_operations fo on a.id=fo.abonent_id and fo.op_type=2 where a.is_folder=0 and a.id>4 and a.deleted=0 and a.parent_id<>244 group by a.name) where lastpay<=dateadd(month, -2, current_timestamp) or lastpay is null order by lastpay, abonname{code} |
h6. Пример 141. # 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} |
h6. Пример 142. # h5. Отчёт по абонентам, у которых количество услуг больше 1, показывающий количество подключенных услуг и количество активных услуг |
{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 {code} |
h6. Пример 143. # h5. История выдачи телефонного номера для voip. |
{code} 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} |
h6. Пример 144. # h5. История использования логина. |
{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} |
|
h6. Пример 145. # h5. Отчёт по услугам IP телевидения с возможностью выбора периода. |
Выводит следующие поля: 1) Имя услуги 2) Цену услуги |
3) Количество абонентов, у которых подключена конкретная услуга на текущий момент (в момент выполнения отчета) в выбранный период |
4) Количество оплаченных дней за указанный период 5) Сумму, списанную по каждой услуге за указанный период |
... |
{color:#ff0000}{*}Примечание{*}{color}{color:#000000}*: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".*{color} {code} |
select distinct |
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 "Цена услуги", |
(select count(*) from abonents a left join users_usluga uu on uu.abonent_id=a.id where uu.usluga_id=u.id) 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 "Дни", |
round(sum(counters.SUMM), 2) as "Сумма" from counters join usluga u on u.id=counters.usluga_id |
(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 |
and where U.SYSTEM_TYPE=7 |
and u.id is not null |
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$') |
group by u.name, u.SUMMA, u.id union all |
... |
sum(SUMOP) from (select round(sum(counters.SUMM), 2) as SUMOP |
from counters join usluga u on u.id=counters.usluga_id |
from usluga u join counters on counters.usluga_id=u.id |
and where U.SYSTEM_TYPE=7 |
and u.id is not null and S_DATE between (':1 Дата|date$') and (':2 Дата|date$')) {code} |
|
h6. Пример 146. # h5. Отчёт по абонентам, у которых подключены услуги IP телевидения. |
Выводит следующие поля: |
1) Имя абонента |
2) Название Цена услуги |
3) Сумму, списанную за конкретного абонента по услуге |
3) Название услуги 4) Сумму, списанную по конкретному абоненту за услугу за указанный период |
45) Итоговую сумму по всем абонентам за услуги IP телевидения за указанный период |
{color:#ff0000}{*}Примечание{*}{color}{color:#000000}*: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".*{color} |
{code} select distinct (select name from abonents a where a.id=counters.abonent_id) as "Абонент", |
round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена услуги", |
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 |
group by counters.abonent_id, u.name |
and counters.MONTH_NUMBER=(':Месяц$') and counters.YEAR_NUMBER=(':Год$') group by counters.abonent_id, u.name, u.SUMMA |
union all select '"Итого"', null, |
null, |
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 counters.MONTH_NUMBER=(':Месяц$') and counters.YEAR_NUMBER=(':Год$')) |
{code} |
# h5. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 496. В данном примере: #* when c.usluga_id=93 then 10 #* when c.usluga_id=92 then 12 #* when c.usluga_id=91 then 14 #* 93 - Id услуги "Международная связь" #* 92 - Id услуги "Междугородная связь" #* 91 - Id услуги "Внутризоновая связь" h6. SQL запрос: {code} select 'Cnst1' as "Аг. договор", A.CONTRACT_NUMBER as "Аб. договор", fo.number as "Фактура", fo.number as "Акт", fo.period_end_date as "Дата счета", dateadd(-16 day to dateadd(month, 1, fo.period_end_date)) as "Срок оплаты", '0' as "Код валюты", '2' as "Код НДС", case when c.usluga_id=93 then 10 when c.usluga_id=92 then 12 when c.usluga_id=91 then 14 end as "Код услуги", fo.period_end_date as "Дата", cast(c.summ as numeric(18,2)) as "Сумма", cast(c.v as numeric(18,2)) as "Минуты", '46' as "C5", '0' as "C6" from abonents a join counters c on c.abonent_id=a.id and c.closed=1 and c.usluga_id in (91,92,93) and c.act_id is not null join finance_operations fo on fo.op_id = c.act_id where a.deleted=0 and a.is_folder=0 and (extract(year from period_end_date)=(':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and extract(month from period_end_date)=(':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')) and exists(select first 1 1 from users u join phone_pull_chache ppc on ppc.id=u.phone where ppc.phone like '7496%' and u.abonent_id=a.id) and c.summ<>0 {code} h6. Шаблон отчёта: {code} {% extends "form_list.html" %} {% block addonhead %} <style> .btn-container { padding: 10px; } </style> <script> $(function () { {# debugger;#} var $select = $('select'); {# $select.wrap('<div class="span12"></div>');#} $select.select2({ containerCssClass : "span6" }); }) </script> {% endblock %} {% load field_type %} {% block content %} <form method="POST"> <div id="params"> {% csrf_token %} <input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/> {% if form %} <div class="row-fluid"> <div class="span6"> <legend><b>Заполните поля запроса</b></legend> {% for field in form %} <div class="control-group clearfix"> {{ field.label_tag }} <div class="controls"> {{ field }} </div> </div> {% endfor %} </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 %} <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" width="100%"> <tr> <th>№</th> {% for field in field_desc %} <th>{{ field|get_zero }}</th> {% endfor %} </tr> {% for row in data %} <tr> <td>{{ forloop.counter }}</td> {% for cell in row %} <td>{{ cell }}</td> {% endfor %} </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 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 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 "Тариф", (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) 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 order by 1 desc{code} # h5. Отчёт по поступившим платежам за выбранный период, с возможностью выбора папки для поиска и пользователя,который добавил платеж. {code} select AB.CONTRACT_NUMBER as "Номер договора", AB.NAME as "ФИО", FO.SYSTEM_DATE as "Дата", FO.DESCR as "Описание", AU.USERNAME as "Добавлен", cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Сумма" from FINANCE_OPERATIONS FO left join ABONENTS AB on FO.ABONENT_ID = AB.ID left join AUTH_USER AU on FO.OWNER_ID = AU.ID where OP_TYPE = 2 and (FO.OP_DATE between ':1.C_даты|date$' and ':2.По_дату|date$') and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0)) and (AB.DELETED = 0 or AB.DELETED is null) group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, AU.USERNAME, FO.SYSTEM_DATE union select '"Итого"', null, null, null, null, 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.C_даты|date$' and ':2.По_дату|date$') and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0)) and (AB.DELETED = 0 or AB.DELETED is null)) {code} # 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 and usluga.SYSTEM_TYPE=0 and users_usluga.CREATE_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by users_usluga.abonent_id ) as "Разовые услуги", ( select sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) FROM TARIF t LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID WHERE a.TARIF_ID = t.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} select distinct A.CONTRACT_NUMBER as "Договор", T.name as "Тариф", usl.NAME as "Услуга", round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) 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.deleted=0 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} # h5. Отчет для поиска по реквизитам Отчет ориентирован только на [реквизиты текстового типа|Реквизиты]. При использовании вместе с шаблоном отчета имя абонента будет преобразовано в гиперссылку на его карточку в БД h6. Текст запроса {code} select a.id, a.name, a.contract_number, 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} h6. Шаблон отчета {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: center;" cellpadding="5"> <tr> <th>ФИО/Название</th> <th>Договор</th> <th>Реквизит</th> <th>Значение</th> </tr> {% for row in data %} <tr align="left" cellpadding="10"> <td><a href="/admin/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td> <td>{{ row.2 }}</td> <td>{{ row.3 }}</td> <td>{{ row.4 }}</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. Отчет по выполненным задачам CRM с возможностью выбора периода, статуса, типа, ответственного за задачу. В конце выводится общее число задач. {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.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$' 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.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$' and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$') {code} # h5. Отчёт по абонентам с балансом между минимальным и максимальным указанным. {code} select distinct A.CONTRACT_NUMBER as "Договор", T.name as "Тариф", round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс", A.name 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 where A.IS_FOLDER=0 and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)<(':1.Максимум$') and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)>(':2.Минимум$') AND A.DELETED=0 and a.is_folder=0 order by A.NAME {code} # h5. Отчет выводящий объёмы трафика за вбыранный период (месяца целиком) по абоненту {code} 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 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} {info}При настройке отчета [для использования в личном кабинете|CarbonBilling:Отчёты в личном кабинете], условие с ID абонента должно быть таким: {code}Abonent_ID = ':Abonent_ID$'{code}{info} # h5. Отчёт по абонентам, подключенным по адресу. В поле "Должник" выводится информация "Должник" в том случае, если у абонента баланс меньше, чем минус 3 абонентские платы. {code} 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 sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) FROM TARIF t LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID WHERE a.TARIF_ID = t.ID ) as "Абон. плата", ( select sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2)) FROM TARIF t LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID WHERE a.TARIF_ID = t.ID ) as "3 АБ", (case when (round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)) < ( select sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2)) FROM TARIF t LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID WHERE a.TARIF_ID = t.ID ) 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 left join TARIF T on A.TARIF_ID=T.ID left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID left join homes h on h.id=a.home_id left join abonents_block ab on ab.abonent_id=a.id LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID left join usluga usl on usl.id=tus.usluga_id where A.IS_FOLDER=0 and H.id=':Дом|select[homes]$' AND A.DELETED=0 order by 2 {code} # h5. Отчёт по сумме приходов абонентов, подключенных к оператору связи за период. Вторым столбцом выводится 10% от суммы приходов {code} select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Сумма приходов", (cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1))*0.1 as "10%" from finance_operations fo left join abonents a on a.id=fo.abonent_id where a.OPERATOR_ID = ':Оператор|select[Abonents,category_id=2]$' and op_type=2 and op_date between (':1 Дата|date$') and (':2 Дата|date$') group by a.operator_id {code} # h5. Детализация звонков по абоненту {info}НДС посчитан только для юридических лиц{info} {code} select distinct vl.src as "Звонок с номера", vl.dst as "Назначение звонка", 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 ( select distinct vl.src as source, vl.dst as dest, substring(cast(vl.s_time as varchar(32)) from 1 for 19) as stime, 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 where vl.s_time between ':1-Начало|date$' and ':2-Конец|date$' and vl.abonent_id = ':Абонент|select[Abonents,is_folder=0]$' ) 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:Отладка отчетов]" |