Конструктор отчетов

Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.

Изменения (53)

просмотр истории страницы
h1. Описание конструктора отчетов

*Конструктор отчетов* позволяет делать запросы напрямую к БД. Описание ограничений, возможностей и синтаксиса используемого диалекта SQL Вы можете найти в документации Firebird 2.15.
Конструктор отчетов находится на вкладке "Отчеты" в основном меню биллинга
\\
Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'{code}

h3. Список вариантов из справочников биллинга

*select* - список, построенный на основе модели, подобно работе [API|CarbonBilling:API REST v2.0], параметры содержат модель (обязательно) и фильтры
{code}

h2. Выполнение отчёта из консоли скриптом make_reports.pyc

Для того, чтобы запустить выполнение отчета из консоли, необходимо:
По-умолчанибю информация, выведенная на экран, будет иметь кодировку Windows (windows-1251, cp1251)

У отчета скрипта есть несколько опций и пресетов для отчетов выгрузок в [СОРМ3|Интеграция с СОРМ3]

h3. Дополнительные опции запуска
h3. Пример запуска make_reports с опциями

* Выгрузить отчет #105 в формате, пригодном для [СОРМ от компании "Норси-Транс"|СОРМ3 НОРСИ-ТРАНС Яхонт (архив)] и кодировке UTF-8:
{code}python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -c utf8 -p norsi-trans{code}
* Выгрузить отчет #105, разделяя поля запятыми и экранируя двойными кавычками только поля со спецсимволами:
{code}python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -s ";" -q \" -f{code}

h2. Выполнение отчёта из консоли утилитой sqlexec

Запрос к базе данных можно выполнить передав его утилите sqlexec, например так:

{code:title=Команда}sqlexec "select count(*) from abonents"{code}
{code:title=Вывод}
COUNT
============
14
{code}
Запрос можно написать в несколько строк:
{code:title=Команда}sqlexec "select
count(*)
from
abonents"{code}
Это удобно, если запрос сложный и объёмный. Вывод будет таким же, как у команды выше.

h2. Выполнение запроса для использования в скриптах

Тут возможны два варианта.

h3. С помощью sqlexec

Если отчёт достаточно простой, его можно выполнить с помощью sqlexec. Но нужно учитвать несколько моментов:
* На самом деле утилита работает в контейнере asr_billing
* Вывод лучше отформатировать для удобства написания скриптов и сохранения данных в переменные
* Частично отформатировать Вывод можно средствами SQL:
** либо "*set heading off;*" - уберёт заголовки, это удобно если Вы выгружаете, например, список каких-то позиций (абонентов, финансовых операций) для обработки каждой в отдельности
** либо "*set list on;*" - "перевернёт" данные, отразит их в виде колонок вместо таблицы

Ниже приведён скрипт, в котором показано как работать с выводом в том или ином виде, включая одиночные значения и списки:
{code}
#!/bin/bash

abonent_count=$(chroot /app/asr_billing/ /usr/local/bin/sqlexec "set list; select count(*) from abonents where is_folder=0" | awk '$2{print $2}')
folder_count=$(chroot /app/asr_billing/ /usr/local/bin/sqlexec "set heading off; select count(*) from abonents where is_folder=1" | sed 's/ *//g; /^$/d')

echo "В биллиге сейчас вот столько абонентов, администраторов, и операторов связи: ${abonent_count}"
echo "В биллиге сейчас вот столько папок: ${folder_count}"
echo "Список папок:"
chroot /app/asr_billing/ /usr/local/bin/sqlexec "set heading off; select name from abonents where is_folder=1" | sed 's/ *//g; /^$/d' | \
while read folder; do
echo "* ${folder}"
done
{code}

h3. Если sqlexec не очень подходит, отчёт слишком сложный

Если отчёт слишком сложный и его удобней сохранить в файл, тогда сделайте следующее:
# Убедитесь что запрос в файле заканчивается символом точки с запятой: ";"
# Положите файл где-нибудь внутри контейнера asr_billing
# Выполните запрос командой isql-fb, ключём -i укажите путь к файлу

Например, можно положить скрипт в папку opt, внутри контейнера, с таким содержимым:
{code:title=Файл /app/asr_billing/opt/abonents.sql}
select
count(*)
from
abonents
where
is_folder=0
;
{code}
Тогда запрос к БД можно выплнить такой командой:
{code:title=Команда}chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /opt/abonents.sql{code}
{note}Заметьте, что для "хост" системы, файл находится по пути /app/asr_billing/opt/abonents.sql, но утилита isql-fb находится в контейнере, и для неё нужно указывать "относительный" путь: */opt/abonents.sql*, убрав адрес корневой папки контейнера.
{note}
Вывод отчёта Вы так же можете форматировать командными утилитами ОС, сохранять в переменные или передавать в циклы для дальнейшей обработки.

h2. Описание полей

{code}

h3. Ограничения в наименовании столбцов отчета
{info}В случае использования кириллицы в названии столбцов отчета важно знать, что поле может содержать не более 18 символов.{info}
Если требуется указать более развернутое название - используйте латиницу.
Иначе, будет возникать [ошибка|https://docs.carbonsoft.ru/pages/viewpage.action?pageId=155714114#Отладкаотчетов-Привыполненииотчетавозниклаошибка"Произошлаошибкапоадресу] при выполнении отчета.


h2. Процедуры Carbon Soft

h4. Примеры запросов

{code:title=ID групп}
{code}SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(1)
SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1){code}

h2. Полезные функции в Firebird

h4. iif
Функция *iif* аналогична опратору сравнения if then else.
{code}
IIF(<поисковое условие>, <значение1>, <значение2>)
{code:title=ID абонентов}
SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1)
{code}
Если <поисковое условие> верно то возвращается <значение1> иначе <значение2>.
Удобно использовать, если в выборке встречаются пустые поля. В конструкторе отчётов они выводятся как *none*. Пример:
{code}
select
iif(a.home_id is null, 'НеУказан',h.street || ' ' || h.s_number)
from
abonents a left join homes h on a.home_id=h.id
order by 1
{code}
В примере, вместо слова *None* будет стоять *НеУказан*.

Также можно заменять числовые значения текстом:
{code}
{code:title=Имена абонентов}
select
name, a.name
iif(deleted=1,'Удалён','')
from
abonents
from GLN_RECURSIVE_ABONENTS_GET(1) aids
inner join abonents a on a.id=aids.abonent_id
{code}
Удалённые абонеты будут выведены с пометкой *Удалён*, дейсвующие без отметки.

h1. Примеры отчётов

# h5. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона
{code}
select UR.user_id as ID,
U.identify as FIO,
AV.attribute_value as TELEFON
from users_radiusauth UR
left join USERS U on U.id=UR.user_id
left join ATTRIBUTE_VALUES AV on AV.user_id=UR.user_id and AV.ATTRIBUTE_ID=1
where UF_IP2STRING(UR.NAS_IP_ADDRESS)='172.16.0.6'
order by U.identify
{code}
# h5. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата
{code}
order by USER_NAME_OUT
{code}
# h5. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, IP коммутатор, Номер порта, Номер vlan) с группировкой по номеру коммутатора
{code}
select
u.identify as FIO,
sw.name as NAME,
uf_ip2string(sw.ip) as SWITCH_IP,
sp.num as PORT,
sp.vlan as VLAN
from switch_ports sp
join users u on u.id=sp.user_id
join switch sw on sw.id=sp.switch_id
order by sp.switch_id
{code}
# h5. Общее количество абонентов по группам
{code}
# h5. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента)
{code}
select A.CONTRACT_NUMBER,
A.NAME,
T.NAME,
ABSP.CEIL_IN,
(select AV.ATTRIBUTE_VALUE
from ATTRIBUTE_VALUES AV
where AV.ATTRIBUTE_ID = 14
and AV.ABONENT_ID = A.ID),
(select AV.ATTRIBUTE_VALUE
from ATTRIBUTE_VALUES AV
where AV.ATTRIBUTE_ID = 13
and AV.ABONENT_ID = A.ID),
(select AV.ATTRIBUTE_VALUE
from ATTRIBUTE_VALUES AV
where AV.ATTRIBUTE_ID = 16
and AV.ABONENT_ID = A.ID),
(select AV.ATTRIBUTE_VALUE
from ATTRIBUTE_VALUES AV
where AV.ATTRIBUTE_ID = 17
and AV.ABONENT_ID = A.ID),
H.CITY,
H.STREET,
H.S_NUMBER,
H.S_LITER,
A.A_HOME_NUMBER,
A.SMS
A.CONTRACT_NUMBER as "Номер договора",
A.NAME as "ФИО",
SER.ATTRIBUTE_VALUE as "Паспорт серия",
NOM.ATTRIBUTE_VALUE as "Паспорт номер",
coalesce(PAS1.ATTRIBUTE_VALUE,'') as "Кем выдан",
coalesce(PAS2.ATTRIBUTE_VALUE,'') as "Дата выдачи",
coalesce(birthday.ATTRIBUTE_VALUE,'') as "Дата рождения",
coalesce(H.CITY,'') as "Город",
coalesce(H.STREET,'') as "Улица",
H.S_NUMBER as "Номер дома",
A.A_HOME_NUMBER as "Номер комнаты",
A.SMS as "Телефон",
EMAIL as "Эл. почта",
T.NAME as "Тариф",
ABSP.CEIL_IN as "Скорость"
from ABONENTS A
left join TARIF T on T.ID = A.TARIF_ID
left join ABONENTS_SPEED ABSP on ABSP.ABONENT_ID = A.ID
left join TARIF T on T.ID = A.TARIF_ID
left join HOMES H on H.ID = A.HOME_ID
where A.DELETED != 1
and A.IS_FOLDER != 1
left join ATTRIBUTE_VALUES SER on SER.ABONENT_ID=A.ID and SER.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES NOM on NOM.ABONENT_ID=A.ID and NOM.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES PAS1 on PAS1.ABONENT_ID=A.ID and PAS1.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES PAS2 on PAS2.ABONENT_ID=A.ID and PAS2.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES birthday on birthday.ABONENT_ID=A.ID and birthday.ATTRIBUTE_ID=22
where A.DELETED = 0
and A.IS_FOLDER = 0
and A.ID>0
order by A.NAME
{code}
from USERS_USLUGA UU
left join ABONENTS AB on UU.ABONENT_ID = AB.ID
where UU.USLUGA_ID = 1247
where UU.USLUGA_ID in (1247, 1248, 1249)
and UU.DELETED = 0
and (select count(1)
and is_folder=0
{code}
Тот же отчет, выводящий абонентскую плату, с возможностью выбора группы
{code}
select '#FILESUM' || ' ' || round(sum(abon_plata.summa), 2)
from abonents as a
right join homes as h on a.home_id=h.id
left join
(
select
uu.abonent_id abon_id,
sum(iif(coalesce(uu.dinamyc_price,0)=0,u.summa,uu.dinamyc_price))/10000000000.00 summa
from
users_usluga uu
join
usluga u
on uu.usluga_id=u.id
where
uu.deleted=0
group by
uu.abonent_id
) abon_plata on a.id=abon_plata.abon_id
where company=0
and is_folder=0
and a.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Группа|select[Abonents,is_folder=1]$'))
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || coalesce(h.city,'') || ',' || coalesce(h.street,'') || ',' || coalesce(h.s_number,'') || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||abon_plata.summa|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id

left join
(
select
uu.abonent_id abon_id,
sum(iif(coalesce(uu.dinamyc_price,0)=0,u.summa,uu.dinamyc_price))/10000000000.00 summa
from
users_usluga uu
join
usluga u
on uu.usluga_id=u.id
where
uu.deleted=0
group by
uu.abonent_id
) abon_plata on a.id=abon_plata.abon_id
where company=0
and is_folder=0
and a.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Группа|select[Abonents,is_folder=1]$'))
{code}
Тот же отчёт, выводящий дополнительные поля-константы и номер телефона
{code}
select first 1
-- #FILESUM 341098.84 - сумма всех платежей
'#FILESUM ' || '' ||
cast(sum(ac.recomend_pay_sum_cache/10000000000.00) as varchar(32))
from
abonents a
join
homes h
on a.home_id=h.id
join
abonents_cache ac
on a.id=ac.id

union all

select first 1
-- #TYPE 7 - константа
'#TYPE 7'
from
abonents a
join
homes h
on a.home_id=h.id
join
abonents_cache ac
on a.id=ac.id

union all

select first 1
-- #SERVICE 60592 - константа
'#SERVICE 60592'
from
abonents a
join
homes h
on a.home_id=h.id
join
abonents_cache ac
on a.id=ac.id

union all

select
-- 1 поле - ФИО;
a.name || ';' ||
-- 2 поле - Адрес;
case
when coalesce(h.settlement,'') <> '' then h.settlement -- если есть поселение, берём его
else h.city -- иначе город
end || ',' ||
coalesce(h.street,'') || ',' || -- улица
case
when (coalesce(s_number,'') <> '' and coalesce(s_liter,'') <> '') then s_number || '/' || s_liter -- дом и корпус
when coalesce(s_number,'') <> '' then s_number -- дом
else '' -- ничего или только корпус
end || ',' ||
coalesce(a.a_home_number,'') || ';' ||
-- 3 поле - Номер договора;
a.contract_number || ';' ||
-- 4 поле - сумма;
cast(ac.recomend_pay_sum_cache/10000000000.00 as varchar(32)) || ';' ||
-- 5-7 пустые;
-- '5;6;7' || ';' || -- DEBUG
';;' || ';' ||
-- 8 поле - 7705:9120000001:0 (константа:номер телефона:константа)
'7705' || ':' || coalesce(a.sms,'') || ':' || '0'
from
abonents a
join
homes h
on a.home_id=h.id
join
abonents_cache ac
on a.id=ac.id
where
coalesce(a.company,0) = 0
{code}
# h5. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number)
{code:lang=sql}
and a.CREATE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
{code}
# h5. Телефония. Количество звонков, ожидающих перерасчет.
{code}
select count(1) from voip_log where recalc=1
{code}
# h5. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)"
{code}
select distinct u.name as "Услуга",
round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена",
round(sum(counters.SUMM), 2) as "Сумма"
from counters c
left join usluga u on u.id=counters.usluga_id
where S_DATE between (':1 Дата|date$') and (':2 Дата|date$')
where
c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
and c.year_number = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
group by u.name, u.SUMMA
{code}
{code}
# h5. Поиск IP-адреса в истории изменения учетных записей (история выдачи IP-адреса)
При каждом измение изменении учётной записи новое состояние учётной записи записывается в историю. Таблица *users_history*. Отчёт покажет когда абоненту был назначен IP-адрес, а также были ли измененяия по абоненту пока он владел этим адресом. Отчёт не показывает когда IP-адрес был снят с абонента.
{code}
select
where
uf_ip2string(ip) = ':Введите ip-адрес$'
order by
time_changed
{code}
# h5. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс"
where U.SYSTEM_TYPE=7
and u.id is not null
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and counters.MONTH_NUMBER=(':Месяц$')
and counters.YEAR_NUMBER=(':Год$')
group by counters.abonent_id, u.name, u.SUMMA
union all
where U.SYSTEM_TYPE=7
and u.id is not null
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
and counters.MONTH_NUMBER=(':Месяц$')
and counters.YEAR_NUMBER=(':Год$'))
{code}
# h5. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 496.
{code}
# h5. Отчет выводящий объёмы трафика за вбыранный период (месяца целиком) по абоненту
{code}select

select
year_number ||'-'|| month_number as "Период",
(SUM_BYTE_IN_M/1000000000.00) as "Входящий/гиг",
(SUM_BYTE_OUT_M /1000000000.00) as "Исходящий/гиг"
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "Входящий/Mб",
round(SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2) as "Исходящий/Mб"
from
traf_counters
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
order by
YEAR_NUMBER, MONTH_NUMBER{code}

{code}
{info}При настройке отчета [для использования в личном кабинете|CarbonBilling:Отчёты в личном кабинете], условие с ID абонента должно быть таким:
{code}Abonent_ID = ':Abonent_ID$'{code}{info}
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}
order by a.id
{code}
# h5. Неудалённые абоненты интернет по типу авторизации
{code}
/*
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. Решение проблем