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

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

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

просмотр истории страницы
{toc:maxLevel=32}

h21. Описание конструктора отчетов

*Конструктор отчетов* позволяет делать запросы напрямую к БД. Описание ограничений, возможностей и синтаксиса используемого диалекта SQL Вы можете найти в документации Firebird 2.1.
\\ !report_builder_3.png|border=1!

h3. Параметры полей формы
h2. Фильтры данных (параметры полей формы)

Переменные, отображаемые в виде формы:
В отчётах можно добавить фильтры - выбор информации, например счетов и актов, только за выбранные даты или период дат, отфильтровать по абонентам и тд.

Фильтры подставляют указанные данные в тело отчёта и потом он выполняется.

Фильтры указываются примерно по такой схеме:
{code:lang=sql}':title|type[params]$'{code}
* : - указывает, что это строка с переменной
* title - заголовок, отображаемый на форме
* \| - разделитель заголовка и типа
* type\[params\] - тип с параметрами:
* $ - флаг отмечает обязательную переменную, он обязателен для всех фильтров

Ниже они описаны подробно.

h3. Выбор дат

** date *date* - дата, указывается без параметров, отображает на форме календарь выбора даты

Прмер:
{code}BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'{code}
** choices - список

h3. Выбор периодов

*monthchoice* - отобразит список периодов в формате "ММ.ГГГГ месяц_прописью"

Примеры:
* 12 последних месяцев
{code}':Дата Список месяцев|monthchoice$'{code}
* 3 последних месяца
{code}':Дата Список месяцев|monthchoice|3$'{code}
* min и max по полю period_end_date модели financeoperations (можно ещё фильтры вписать, так же как для опции select)
{code}':Дата Список месяцев|monthchoice|period_end_date|select[financeoperations]$'{code}
* min и max по полю period_end_date модели financeoperations с фильтрами: только не сторнированные акты
{code}':Дата Список месяцев|monthchoice|period_end_date|select[financeoperations,op_type=2,storno=0]$'{code}

h3. Список вариантов

*choices* - список, параметры - это элементы списка

Примеры:
{code:lang=sql}Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$'
owner_id in (:Администратор|choices[33^]root^[1005^]Василий^[1007,1005^]Василий или Михаил]$)
Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'{code}

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

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

Примеры:
{code}a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
h.id = ':Заявка|select[HDSK]$'{code}
** Отсутстие параметров запросит строку:

h3. Строка для ввода произвольных данных:

Отсутстие параметров предложет ввести произвольные данные.

{code}
':Введите число$'
{code}
* $ - флаг отмечает обязательную переменную

h32. Выполнение отчёта из консоли

Для того, чтобы запустить выполнение отчета из консоли, необходимо:
{code}chroot /app/asr_billing/{code}
# Запустить скрипт
{code}python2.7 /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105{code}
Где 105 - id отчёта.
По-умолчанибю информация, выведенная на экран, будет иметь кодировку Windows (windows-1251, cp1251)
У отчета есть несколько опций и пресетов для отчетов [СОРМ3|Интеграция с СОРМ3]

h43. Дополнительные опции запуска
|| Опция || Полный формат || Пример || Описание ||
| -c CHARSET | --charset=CHARSET | -c utf8 | Кодировка вывода, по-умолчанию: cp1251


h43. Пример запуска make_reports с опциями

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

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

К большенсту полей БД существуют описания. Описание всех полей можно получить выполнив команду *sqlexeс "show comments"*, например:
COMMENT ON COLUMN TARIF.PROMISE_PAY_ONLY_THIS_MONTH IS Разрешать подключение обещанного платежа только один раз в текущий месяц;{code}

h32. Особенности SQL в Firebird


h43. UNION (UNION ALL) и ORDER BY

h6. В Firebird Order By должен быть последним оператором, сортируя всю выборку, например:
Список символов можно посмотреть в [Википедии|https://en.wikipedia.org/wiki/Whitespace_character], в примере использован символ U+2003 "em space"

h63. Особенность сортировки числовых значений
В примере выше выборка будет отсортирована как текстовая. То есть порядок чисел будет: *1,10,100,2,20,200*. Что бы отсортировать выборку как числа нужно убрать строковые константы в полях и заменить их на значение *null*. Тогда результат будет такой: *1,2,10,20,100,200*.
{code} select
{code}

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

Для работы с базой мы добавили ряд собственных процедур, которые помогут найти нужные данные и верно их отобразить в отчётах в соответствии с архитектурой биллинга.

h3. Получение списка вложенных групп и абонентов в дереве групп


h4. GLN_RECURSIVE_GROUP_WALK

SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1){code}

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

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

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

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

# h5. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона
{code}
a.is_folder = 0
{code}
# h5. Поиск IP-адреса в истории изменения учетных записей (история выдачи ip-адреса) IP-адреса)
При каждом измение учётной записи новое состояние учётной записи записывается в историю. Таблица *users_history*. Отчёт покажет когда абоненту был назначен IP-адрес, а также были ли измененяия по абонету пока он владел этим адресом. Отчёт не показывает когда IP-адрес был снят с абонента.
{code}
select
# 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 "ФИО",
(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) 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

h2. Решение проблем
union

select
count(distinct cd.id), -- количество направлений в услугах (суммарно)
'',
'Итого'
from
usluga u
join
usluga_voip uv -- в таблице хранятся актуальные цены категорий
on u.id=uv.usluga_id
join
category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим"
on uv.voipcategory_id=cd.category_id
join
users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена
on u.id=uu.usluga_id and uu.deleted=0
where
uu.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'

order by
1
{code}
# h5. Отчёт по абонентам заключивших договор за период
Отчёт покажет абонентов заключивших договор в выбранном периоде, в воде будут номер договора, ФИО, дата договора, текущий тариф и текущий баланс.
{code}
select
a.contract_number "Номер договора",
a.name "Название/ФИО",
cast(a.create_date as date) "Дата договора",
t.name "Тариф",
round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)), 2) "Текущий баланс"
from
abonents a
join
tarif t
on a.tarif_id=t.id
join
admin_accounts aa
on a.account_id=aa.id
where
extract(month from a.CREATE_DATE)=extract(month from cast(':Дата в виде месяца|monthchoice|create_date|select[abonents,id__gt=1000,deleted=0,parent_id__gt=1000,is_folder=0]$' as date))
and extract(year from a.CREATE_DATE)=extract(year from cast(':Дата в виде месяца|monthchoice|create_date|select[abonents,id__gt=1000,deleted=0,parent_id__gt=1000,is_folder=0]$' as date))
and a.id>=1000
and a.deleted=0
and a.parent_id>=1000
and a.is_folder=0
{code}
# h5. Отчёт по начислениям по видам услуг за выбранный период
Отчёт покажет начисления по видам услуг за выбранный период
В case можно добавить собственную логику постороения отчёта в зависимости от натсроек услуг; в примере ниже:
* услуги с типом стандартный будут посчитаны как "разовые" или "прочие" в зависимости от типа списания
* все услути касающиеся интернет-трафика (форсаж, пакет МБ и тд) будут отражены просто как "трафик"
{code}
select
c.year_number*100+c.month_number "Период",
case
when ut.id=0 and u.usluga_abon_type_id=1 then 'Разовые услуги' --'Стандартный'
when ut.id=0 and u.usluga_abon_type_id<>1 then 'Прочие услуги' --'Стандартный'
when ut.id=1 then 'Интернет' --'Турбокнопка'
when ut.id=3 then 'Интернет' --'Бонусный трафик'
when ut.id=4 then 'Интернет' --'Форсаж'
when ut.id=2 then 'Прочие услуги' --'Подписка'
when ut.id=6 then 'Интернет' --'Пакет МБ'
when ut.id=5 then 'IP телефония'
when ut.id=7 then 'IP телевидение'
when ut.id=8 then 'Абонентская плата'
when ut.id=9 then 'Интернет' --'Трафик'
when ut.id=10 then 'Скидка\Наценка'
when ut.id=11 then 'Обещанный платеж'
when ut.id=12 then 'Пакет услуг'
when ut.id=13 then 'Системные услуги'
when ut.id=14 then 'Прочие услуги' --'Счетчик'
end "Тип услуг",
round(sum(c.summ),2) "Сумма"
from
counters c
join
usluga u
on c.usluga_id=u.id
join
usluga_type ut
on u.system_type=ut.id
where
c.summ<>0
and cast(c.year_number || '-' || c.month_number || '-01' as date)
between cast(':1-С периода|monthchoice|6$' as date)
and cast(':2-По период|monthchoice|bill_date|select[ArchAccountStack,storno=0]$' as date)
group by
1,2
order by
:3-Сортировать по|choices[1,2^]Месяцу^[2,1^]Типу услуг]$
{code}
# h5. Отчёт по отправленным сообщениям из аудита
{code}
select
a.name as "ФИО",
a.contract_number as "Договор",
ao.descr as "Описание",
ao.OP_TIME as "Дата"
from audit_operations ao
left join abonents a on a.id=ao.abonent_id
where ao.descr like '%Сооб%' and ao.abonent_id is not null
and ao.OP_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
{code}
# h5. Вывод абонентов у кого подключен автоплатеж
{code}
select
a.id,
a.contract_number as " номер договора",
a.name as "ФИО",
iif(a.autopay_pay_log_id is not null, 'Включен', ' ') as "автоплатеж"
from abonents a
where
a.is_folder=0
and a.deleted=0
and a.parent_id not in (2,4,244)
and a.autopay_pay_log_id is not null
{code}

h1. Решение проблем

Решение проблем с отчетами и инструкция по методике отладки описаны в статье "[CarbonBilling:Отладка отчетов]"