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

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

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

просмотр истории страницы
...
and round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)>2
{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}

h2. Финансовая информация
...
or AB.DELETED is null))
{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. Отчёт по неплательщикам за месяц
{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. Отчёт по сумме списаний у абонентов. Списания суммируются из поля "Расход/предоплата" в финансовой информации абонента.
{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

h2. Учётные записи
# h5. Отчет по свободным IP адресам
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}
select uf_ip2string(pc.ip) as "IP адрес",
ipp.name as "Pull",
(case when ipp.enabled <> 0 THEN 'Да' ELSE 'Нет' END) as "Включен"
from pull_cache pc
left join ip_pull ipp on ipp.pull_id = pc.pull_id
# 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. Отчёт по начислениям по видам услуг за выбранный период
Отчёт покажет начисления по видам услуг за выбранный период
В 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. Отчет формирует информацию за период по следующим полям: входящий баланс абонента, списания за указанный месяц, оплачено, исходящий баланс, НДС 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
pc.user_id is null
order by pc.pull_id, pc.ip
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}
...
order by user_name_out
{code}

h2. Учётные записи
# h5. Отчет по свободным IP адресам
{code}
select uf_ip2string(pc.ip) as "IP адрес",
ipp.name as "Pull",
(case when ipp.enabled <> 0 THEN 'Да' ELSE 'Нет' END) as "Включен"
from pull_cache pc
left join ip_pull ipp on ipp.pull_id = pc.pull_id
where
pc.user_id is null
order by pc.pull_id, pc.ip
{code}
# h5. Количество услуг из ЛК за месяц по абонентам
{code}
...
and u.discount>0
{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
-- 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}

h2. Блокировки
...
{% endblock %}
{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}

h2. Телефония
...
and vd.mask=:Маска направления$
{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}
# 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
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}

h2. Интернет трафик
# h5. Отчёт по скачанному трафику за период.
...
</form>
{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. Отчет, выводящий суммарный объем входящего и исходящего трафика с начала года по выбранный квартал
{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. Отчет по объемам потребленного трафика абонентом.
Будет работать лишь после включения опции *["Сохранять движения всего трафика"|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}

h2. CRM и Helpdesk
# h5. Отчет по выполненным задачам CRM с возможностью выбора периода, статуса, типа, ответственного за задачу. В конце выводится общее число задач.
...
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$')
{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}

h2. История измения абонентов

# h5. Отчёт покажет абонентов удалённых начиная с даты *2025-01-01* и находившихся в папке с *ID* 11413
{code}
select
ah.abonents_id,
ah.contract_number,
lpad(EXTRACT(DAY FROM ah.TIME_DEL),2,'0') ||'-'|| lpad(EXTRACT(MONTH from ah.TIME_DEL),2,'0') ||'-'|| EXTRACT(YEAR FROM ah.TIME_DEL)
from abonents_history ah
where
ah.TIME_DEL > '2025-01-01'
and ah.parent_id=11413
{code}

h2. Прочие отчёты

...
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
...
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. Отчёт по неплательщикам за месяц
{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. Список абонентов, у которых не заведён дом или дом заведён некорректно
Отчет
...
{% endblock %}
{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}
...
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 нужной папки.
...
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}
...
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}
...
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}
...
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, но в форме можно выбрать вывести всех.
...
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}
...