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

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

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

просмотр истории страницы
...
and a.parent_id = (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}
...
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}

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

...