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

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

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

просмотр истории страницы
...
h1. Примеры отчётов

# h5. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата
h2. Абоненты
{code}
select LOGIN_IN as LOGIN,
USER_NAME_OUT as FIO,
SUMMA_IN as SUMMA,
OPERATOR_DATE_IN as DATA
from PAY_LOG
where MSG_OUT='ACCEPTED' and ACT_IN='PAY' and PAY_OPERATOR='Qiwiwallet'
order by USER_NAME_OUT
{code}
# h5. Общее количество абонентов по группам
{code}
...
where is_folder=1
{code}
# h5. Средняя выручка(в расчёте на одного абонента) за предыдущий и текущий месяц или с возможностью выбора периода
{code}
select
cast(avg(ss) as numeric(18,2)) as "Выручка",
year_number as "Год",
month_number as "Месяц"
from
(select sum(summ) as ss,year_number,month_number from counters where (current_date - cast(year_number || '-' || month_number || '-01' as date) <= :cnt.Количество дней до текущей даты$)
group by ABONENT_ID,year_number,month_number)
group by year_number,month_number
{code}
# h5. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента)
{code}
...
order by A.NAME
{code}
# h5. Отчет по списаниям по абонентам в формате (число, id группы, название группы, сумма и кол-во абонентов, по которым были списания) и последней строкой сумма за день.
{code}
select C.DT,
A.PARENT_ID,
(select NAME
from ABONENTS
where ID = A.PARENT_ID),
sum(C.SUMM),
'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
from ABONENTS
where DELETED = 0 and IS_FOLDER = 0 and PARENT_ID = A.PARENT_ID)
from ABONENTS A
join(select sum(SUMM) as SUMM,
ABONENT_ID,
cast(S_DATE as date) as DT
from COUNTERS
group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3

union

select C.DT,
9999999,
'Сумма',
sum(C.SUMM),
'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
from ABONENTS
where DELETED = 0 and IS_FOLDER = 0)
from ABONENTS A
join(select sum(SUMM) as SUMM,
ABONENT_ID,
cast(S_DATE as date) as DT
from COUNTERS
group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3


order by 1, 2, 3
{code}
# h5. Отчет по должникам для отключения КТВ (Улица, Дом, Подъезд, Квартира, ФИО, Тариф) игнорируя ручной статус "Отключен"
{code}
...
order by 6
{code}
# 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}
select distinct(pay_id_str_in) as "PAY",
CONTRACT_NUMBER_IN as "Договор",Пример
USER_NAME_OUT as "ФИО",
operator_date_in as "Дата",
SUMMA_IN as "Сумма"
from pay_log
where (operator_src_ip='213.208.182.172' or pay_operator='Uniteller')
and act_in='pay'
and operator_date_in between ':C_даты|date$' and ':По_дату|date$'
order by user_name_out
{code}
# h5. Количество услуг из ЛК за месяц по абонентам
{code}
select A.ID,
A.CONTRACT_NUMBER,
A.NAME,
count(*)
from USERS_USLUGA UU
left join ABONENTS A on UU.ABONENT_ID = A.ID
where UU.USLUGA_ID = ':ID услуги$' and UU.IS_ZAKAZANO = 1 and UU.CREATE_DATE between ':C_даты|date$' and ':По_дату|date$'
group by A.ID, A.CONTRACT_NUMBER, A.NAME
having count(*)>1
order by A.NAMEe_in between ':C_даты|date$' and ':По_дату|date$'
{code}
# h5. Прибыль с комиссии за обещанный платеж (все периоды)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.MONTH_NUMBER as "номер месяца",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
union
select '#ИТОГО',
'',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)

order by 1
{code}
# h5. Прибыль с комиссии за обещанный платеж (по периодам)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'

order by 1
{code}
# h5. Просмотр пользователей в онлайне с выводом времени онлайна
{code}
select cast(U.LOGIN as varchar(128)) as "логин",
cast(H.STREET as varchar(128)) as "Улица",
cast(H.S_NUMBER as varchar(128)) as "№ дома",
cast(AB.A_HOME_NUMBER as varchar(128)) as "№ квартиры",
cast(UF_IP2STRING(U.IP) as varchar(128)) as "IP-адрес",
cast((time '00:00:00' + datediff(second, RS.START_TIME, current_timestamp)) as varchar(128)) as "время в онлайне"
from USERS_RADIUSAUTH UR
left join USERS U on UR.USER_ID = U.ID
left join ABONENTS AB on U.ABONENT_ID = AB.ID
left join HOMES H on AB.HOME_ID = H.ID
left join RADIUS_SESSIONS RS on UR.ACCT_SESSION_ID = RS.ACCT_SESSION_ID
where UR.LOGGED = 1
union
select cast('#ИТОГО ПОЛЬЗОВАТЕЛЕЙ В ОНЛАЙНЕ' as varchar(128)) as "логин",
cast('' as varchar(128)) as "Улица",
cast('' as varchar(128)) as "№ дома",
cast('' as varchar(128)) as "№ квартиры",
cast('' as varchar(128)) as "IP-адрес",
cast(count(UR.LOGGED) as varchar(128)) as "время в онлайне"

from USERS_RADIUSAUTH UR
where UR.LOGGED = 1

order by 1
{code}
# h5. Абоненты, которые в начале следующего месяца будут заблокированы (при условии что лимит выставлен отричательным числом, в противном случае минус перед ним нужно убрать)
{code}
...
where uf_ip2string(n.ip)='192.168.1.2' and a.deleted=0
{code}
# h5. Список&nbsp;абонентов, Список абонентов, взявших больше одного обещанного платежа
{code:lang=sql}select distinct AB.NAME as "ФИО",
AB.CONTRACT_NUMBER as "Номер договора",
...
and UUA.DELETED = 0
and ABA.ID = AB.ID) > 1
order by 3{code} 3
# h5. Отчет по абонентам со статусом "подключен"
{code}
# h5. Отчет по количеству активных абонентов, у которых назначен IP и NAS с id 70
{code}select
count(distinct ab.id) as "Кол-во абонентов"
...
ab.is_folder = 0
and exists(select 1 from users where nas_id=70 and abonent_id=ab.id and ip is not null){code}
# h5. Отчет по абонентам со статусом "не подключен" количеству заблокированных абонентов
{code}select
count(distinct ab.id) as "Кол-во абонентов"
...
and
(current_timestamp - ab.b_date) < 90))
group by h.s_number{code}
# h5. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам.
{code}select first 1
(':Начало|date$') as "Период начало",
(':Конец|date$') as "Период окончание",
{code}
# h5. Список пользователей с сортировкой по ФИО (ФИО, логин, IP, Город, Улица, Дом)
{code:lang=sql}
select
A.NAME as FIO,
U.LOGIN as LOGIN,
UF_IP2STRING(U.IP) as IP,
H.city as CITY,
H.street as STREET,
H.s_number as HOME
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
order by A.NAME
{code}
# h5. Отчёт в формате "Логин - Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения". По текущей дате
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
round(SUM_BYTE_IN_A/cast((1048576) as numeric(18,5)), 2) as "В год",
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "В месяц",
round(SUM_BYTE_IN_D/cast((1048576) as numeric(18,5)), 2) as "В день",
abonents.create_date as "Подключен"
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number=extract(month from current_timestamp)
and tc.year_number=extract(year from current_timestamp)
and tc.currentt=1
{code}
# h5. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг.
{code:lang=sql}
select distinct A.CONTRACT_NUMBER as "Договор",
A.NAME as "ФИО",
(H.CITY || ' ' || H.STREET || ' ' || H.S_NUMBER || ' ' || H.S_LITER) as "Дом",
A.SMS as "Телефон",
(AC.OSTATOK + AC.DEBIT - AC.CREDIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Баланс",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
where AB.B_DATE between (':1 Дата|date$') and (':2 Дата|date$')
{code}
# h5. Отчёт по должникам по телефонии в формате " № - Папка - ФИО - Телефонный номер - Дата последней оплаты - Сумма долга
{code:lang=sql}
select
cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as "№",
tmp.*
from (select distinct
(select aa.name from abonents aa where aa.id=a.parent_id) as "Папка",
A.NAME as "ФИО",
PHONE_PULL_CHACHE.Phone as "Телефон",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = A.ID) as "Дата посл.",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join users u on u.abonent_id=A.ID
left join PHONE_PULL_CHACHE on PHONE_PULL_CHACHE.ID=u.PHONE)
as tmp
where rdb$set_context('USER_TRANSACTION', 'row#', coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0)+1) > -1
{code}
# h5. Отчет для вывода рекомендуемого ежемесячно платежа, стоимости тарифа по всем абонентам.
{code}
select
a.CONTRACT_NUMBER as "№",
a.NAME as "ФИО",
t.NAME as "ТАРИФ",
round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "АБОН.ПЛАТА",
round(ac.RECOMEND_PAY_SUM_CACHE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "РЕКОМЕНД.",
st.name as "СТАТУС",
subst.name as "ПОДСТАТУС",
cast(obs.apply_date as date) as "ДАТА"

from ABONENTS a
LEFT JOIN ABONENTS_CACHE ac ON ac.ID=a.ID
LEFT JOIN TARIF t ON a.TARIF_ID=t.ID
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN STATUS subst on subst.id = obs.SUBSTATUS

where obs.OBJECT_NAME='Abonents'
and a.is_folder = 0
and a.parent_id != 244
and a.parent_id != 2
{code}
# h5. Отчёт по платежам через веб - кассу(По операторам)
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$') and AU.USERNAME = ':Администратор$'
{code}
# h5. Поле "Не отключать при превышении порога", ID, ФИО.
{code}
select
a.ID as "ID",
a.NAME as "ФИО",
aa.UNLIMITED as "Не отключать"
from ABONENTS a
left join ADMIN_ACCOUNTS aa on a.ACCOUNT_ID=aa.ID
where a.IS_FOLDER=0
order by a.NAME
{code}
# h5. Учёт заключенных договоров за период
{code}
select
a.CREATE_DATE as "Дата договора",
a.CONTRACT_NUMBER as "№ договора",
t.NAME as "Тариф",
st.NAME as "Cтатус",
a.NAME as "ФИО абонента",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
a.ACTIVATE_DATE as "Дата активации",
n.NAME as "NAS"
from ABONENTS a

LEFT JOIN TARIF t on a.TARIF_ID=t.ID
LEFT JOIN USERS u on u.ABONENT_ID=a.ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN NAS n on u.nas_id=n.ID
left join HOMES h on a.HOME_ID=h.ID
where a.IS_FOLDER=0
and a.CREATE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
{code}
# h5. Отчёт по юридическим лицам, выводящий ФИО Телефон Адрес Р/С ИНН КПП
{code}
select A.NAME as "ФИО",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
av1.attribute_value as "Р/С",
av2.attribute_value as "ИНН",
av3.attribute_value as "КПП"
from abonents as a
left join HOMES as H on H.ID=A.HOME_ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES AV2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES AV3 on a.id=av3.ABONENT_ID and av3.ATTRIBUTE_ID=5
where a.is_folder=0
and a.company=1
{code}
# h5. Отчёт прибыль по плате за подключение нескольких услуг.
{code}
select
a.NAME as "ФИО",
uu.ABONENT_ID as "ID АБОНЕНТА",
cast(uu.CREATE_DATE as date) as "ДАТА АКТИВАЦИИ",
c.SUMM as "Прибыль"
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0

union all

SELECT
cast('' as varchar(32)),
cast('' as varchar(32)),
cast('Итого' as varchar(32)),
ROUND (sum(c.summ))
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0
{code}
# h5. Отчёт по абонентам в формате "ФИО ЛОГИН IP MAC ТЕЛЕФОН АДРЕС ТАРИФ БАЛАНС"
{code:lang=sql}
select distinct
A.NAME as "ФИО",
U.LOGIN as "Логин",
UF_IP2STRING(U.IP) as "IP",
U.MAC as "MAC",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
T.name as "Тариф",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0
order by A.NAME
{code}
# h5. Отчёт в формате "ФИО-номер договора-телефон-адрес"
{code}
select distinct
A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0
order by A.NAME
{code}
# h5. Абоненты с учетными записями без mac-адреса. Формат: номер договора, ip учетной записи (если есть)
{code}select
a.contract_number as "Номер договора",
coalesce(uf_ip2string(u.ip), '') as "IP"
from
users u
join
abonents a
on u.abonent_id = a.id
where
u.phone is null and
u.deleted = 0 and
a.deleted = 0 and
a.is_folder = 0 and
u.is_template = 0 and
(u.mac is null or u.mac = '')
{code}
# h5. Отчёт по абонентам, выводящий следующий данные:"ФИО, договор, текущий баланс,сумму списаний по разовым услугам за период, сумму абон.платы, сумму приходов за период".
{code}
select a.name as "ФИО",
a.contract_number as "Договор",
round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс",
(
select (sum(aas.credit) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and aas.storno=0
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик')
and ab.company = 0
) as "Абон. плата физ.",
select
sum(round(users_usluga.SUMM / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
from users_usluga
left join usluga on usluga.id=users_usluga.usluga_id
where users_usluga.ABONENT_ID=a.id
and usluga.SYSTEM_TYPE=0
and users_usluga.CREATE_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by users_usluga.abonent_id
) as "Разовые услуги",
(
select (sum(aas.credit_adjust) /(select CONST_VALUE from VPN_CONST where CONST_ID = 1))from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик' )
and ab.company = 1
and aas.storno=0
select
sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) as "Абон. плата юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 0
) as "Приходы физ.",
select sum(round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
from finance_operations fo1
where fo1.abonent_id=a.id
and fo1.op_type=2
and fo1.op_date between (':1 Дата|date$') and (':2 Дата|date$')
) as "Приходы"
from abonents a
left join finance_operations fo on fo.abonent_id=a.id
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where is_folder=0
group by a.name, a.TARIF_ID, (aa.ostatok+aa.debit-aa.credit) ,a.id,a.contract_number
( {code}
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 1
) as "Приходы юр.",
# h5. Отчёт по абонентам с блокировкой по отрицательному балансу с балансом больше 2.
( {code}
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
) as "Итого",
( select
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
distinct
A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
usl.NAME as "Услуга",
round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join users_usluga uu on uu.ABONENT_ID=A.ID
left join usluga usl on usl.id=uu.USLUGA_ID
left join abonents_block ab on ab.abonent_id=a.id
where os.status=31 A.IS_FOLDER=0
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Подписанные",
and a.deleted=0
and ab.B_NEGBAL=1
and round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)>2
( {code}
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
# 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 os.status=35
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Расторженные"
from send_type{code}
# h5. Отчет по должникам с возможностью выбора тарифа. В отчете: номер договора, ФИО, телефон абонента, адрес, тариф, статус, дата последнего платежа, баланс.
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}select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
AB.SMS as "Телефон",
TP.NAME as "Тариф",
H.STREET as "Улица",
H.S_NUMBER as "№ дома",
H.S_LITER as "Корпус",
AB.A_HOME_NUMBER as "№ квартиры",
(select max(ffoo.system_date) from finance_operations ffoo where ffoo.abonent_id=ab.id and ffoo.op_type=2 and ffoo.storno=0) as "Посл. платеж",
(AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Баланс",
STATUS.NAME as "Статус",
av1.attribute_value as "s/n"
from USERS U
left join ABONENTS AB on U.ABONENT_ID=AB.ID
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join HOMES H on AB.HOME_ID=H.ID
left join TARIF TP on AB.TARIF_ID = TP.ID
left join OBJECTS_STATUS OS on OS.OBJECT_ID=AB.ID
left join STATUS on OS.STATUS=STATUS.ID
left join ATTRIBUTE_VALUES as av1 on ab.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1007
left join ABONENTS_BLOCK ABL on ABL.ABONENT_ID = AB.ID
where ABL.b_negbal=1
and TP.ID=':Тариф|select[Tarif]$'
order by U.CONTRACT_NUMBER{code}

h2. Финансовая информация

# h5. Отчет должники по постоплате с разделением по физическим и юридическим лицам. Отчет содержит: номер договора, ФИО, адрес, дата последнего платежа, сумма долга (рекоммендованный платеж по постоплате)
{code:lang=sql}select first 1
...

group by
PERIOD.DAT{code}
{code}
# h5. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата
{code}
select LOGIN_IN as LOGIN,
USER_NAME_OUT as FIO,
SUMMA_IN as SUMMA,
OPERATOR_DATE_IN as DATA
from PAY_LOG
where MSG_OUT='ACCEPTED' and ACT_IN='PAY' and PAY_OPERATOR='Qiwiwallet'
order by USER_NAME_OUT
{code}
# h5. Средняя выручка(в расчёте на одного абонента) за предыдущий и текущий месяц или с возможностью выбора периода
{code}
select
cast(avg(ss) as numeric(18,2)) as "Выручка",
year_number as "Год",
month_number as "Месяц"
from
(select sum(summ) as ss,year_number,month_number from counters where (current_date - cast(year_number || '-' || month_number || '-01' as date) <= :cnt.Количество дней до текущей даты$)
group by ABONENT_ID,year_number,month_number)
group by year_number,month_number
{code}
# h5. Отчет по списаниям по абонентам в формате (число, id группы, название группы, сумма и кол-во абонентов, по которым были списания) и последней строкой сумма за день.
{code}
select C.DT,
A.PARENT_ID,
(select NAME
from ABONENTS
where ID = A.PARENT_ID),
sum(C.SUMM),
'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
from ABONENTS
where DELETED = 0 and IS_FOLDER = 0 and PARENT_ID = A.PARENT_ID)
from ABONENTS A
join(select sum(SUMM) as SUMM,
ABONENT_ID,
cast(S_DATE as date) as DT
from COUNTERS
group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3

union

select C.DT,
9999999,
'Сумма',
sum(C.SUMM),
'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
from ABONENTS
where DELETED = 0 and IS_FOLDER = 0)
from ABONENTS A
join(select sum(SUMM) as SUMM,
ABONENT_ID,
cast(S_DATE as date) as DT
from COUNTERS
group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3


order by 1, 2, 3
{code}
# h5. Прибыль с комиссии за обещанный платеж (все периоды)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.MONTH_NUMBER as "номер месяца",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
union
select '#ИТОГО',
'',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)

order by 1
{code}
# h5. Прибыль с комиссии за обещанный платеж (по периодам)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'

order by 1
{code}
# h5. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам.
{code}select first 1
(':Начало|date$') as "Период начало",
(':Конец|date$') as "Период окончание",
(
select (sum(aas.credit) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and aas.storno=0
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик')
and ab.company = 0
) as "Абон. плата физ.",
(
select (sum(aas.credit_adjust) /(select CONST_VALUE from VPN_CONST where CONST_ID = 1))from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик' )
and ab.company = 1
and aas.storno=0
) as "Абон. плата юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 0
) as "Приходы физ.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 1
) as "Приходы юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
) as "Итого",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=31
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Подписанные",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=35
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Расторженные"
from send_type
{code}
# h5. Отчет по должникам с возможностью выбора тарифа. В отчете: номер договора, ФИО, телефон абонента, адрес, тариф, статус, дата последнего платежа, баланс.
{code}select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
AB.SMS as "Телефон",
TP.NAME as "Тариф",
H.STREET as "Улица",
H.S_NUMBER as "№ дома",
H.S_LITER as "Корпус",
AB.A_HOME_NUMBER as "№ квартиры",
(select max(ffoo.system_date) from finance_operations ffoo where ffoo.abonent_id=ab.id and ffoo.op_type=2 and ffoo.storno=0) as "Посл. платеж",
(AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Баланс",
STATUS.NAME as "Статус",
av1.attribute_value as "s/n"
from USERS U
left join ABONENTS AB on U.ABONENT_ID=AB.ID
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join HOMES H on AB.HOME_ID=H.ID
left join TARIF TP on AB.TARIF_ID = TP.ID
left join OBJECTS_STATUS OS on OS.OBJECT_ID=AB.ID
left join STATUS on OS.STATUS=STATUS.ID
left join ATTRIBUTE_VALUES as av1 on ab.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1007
left join ABONENTS_BLOCK ABL on ABL.ABONENT_ID = AB.ID
where ABL.b_negbal=1
and TP.ID=':Тариф|select[Tarif]$'
order by U.CONTRACT_NUMBER{code}
# h5. Отчет "Выставленные счета физическим лицам". В отчете: сумма в счете, ФИО абонента, описание финансовой операции
{code:lang=sql}select ((fo.op_summa)/(select const_value from vpn_const where const_id=1)) as "Сумма",
...
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}{code}
{code}
# h5. Отчёт о динамике приходов и актов за заданный период времени
h6. SQL запрос
...
left join users as u on a.id=u.abonent_id
join ABONENTS_BLOCK as ab on ab.ABONENT_ID=a.ID
where ab.b_own=1{code}
# h5. Список пользователей с сортировкой по ФИО (ФИО, логин, IP, Город, Улица, Дом)
{code:lang=sql}
select
A.NAME as FIO,
U.LOGIN as LOGIN,
UF_IP2STRING(U.IP) as IP,
H.city as CITY,
H.street as STREET,
H.s_number as HOME
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
order by A.NAME
{code}
# h5. Отчёт в формате "Логин - Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения". По текущей дате
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
round(SUM_BYTE_IN_A/cast((1048576) as numeric(18,5)), 2) as "В год",
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "В месяц",
round(SUM_BYTE_IN_D/cast((1048576) as numeric(18,5)), 2) as "В день",
abonents.create_date as "Подключен"
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number=extract(month from current_timestamp)
and tc.year_number=extract(year from current_timestamp)
and tc.currentt=1
{code}
# h5. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг.
{code:lang=sql}
select distinct A.CONTRACT_NUMBER as "Договор",
A.NAME as "ФИО",
(H.CITY || ' ' || H.STREET || ' ' || H.S_NUMBER || ' ' || H.S_LITER) as "Дом",
A.SMS as "Телефон",
(AC.OSTATOK + AC.DEBIT - AC.CREDIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Баланс",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
where AB.B_DATE between (':1 Дата|date$') and (':2 Дата|date$')
{code}
# h5. Отчёт по должникам по телефонии в формате " № - Папка - ФИО - Телефонный номер - Дата последней оплаты - Сумма долга
{code:lang=sql}
select
cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as "№",
tmp.*
from (select distinct
(select aa.name from abonents aa where aa.id=a.parent_id) as "Папка",
A.NAME as "ФИО",
PHONE_PULL_CHACHE.Phone as "Телефон",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = A.ID) as "Дата посл.",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join users u on u.abonent_id=A.ID
left join PHONE_PULL_CHACHE on PHONE_PULL_CHACHE.ID=u.PHONE)
as tmp
where rdb$set_context('USER_TRANSACTION', 'row#', coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0)+1) > -1
{code}
# h5. Отчёт по скачанному трафику за период.
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
sum(round(tc.SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2)) as "Объем исх.",
sum(round(tc.SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2)) as "Объем вх."
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number between (':1 Месяц$') and (':2 Месяц$')
and tc.year_number=(':Год$')
and tc.currentt=1
group by users.login, tarif.name
{code}
# h5. Отчёт по скачанному трафику за период (2 вариант).
{code:lang=sql}
select
abonent_id as "ИД абонента",
year_number ||'-'|| month_number as "Период",
(SUM_BYTE_IN_M /1000000000.00) as "Входящий/гиг",
(SUM_BYTE_OUT_M /1000000000.00) as "Исходящий/гиг"
from
traf_counters
where
MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
union all
select
'Итого',
'',
sum(SUM_BYTE_IN_M /1000000000.00),
sum(SUM_BYTE_OUT_M /1000000000.00)
from
traf_counters
where
MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
{code}
# h5. Отчёт по платежам для групп абонентов.
{code:lang=sql}
...
coalesce(a.company,0) = 0
{code}
# h5. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number)
{code:lang=sql}
{% extends "form_list.html" %}
{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>

{% endfor %}

</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>№</th>
{% for field in field_desc %}
<th>{{ field|get_zero }}</th>
{% endfor %}
</tr>
{% for row in data %}
<tr>
<td>{{ forloop.counter }}</td>
{% for cell in row %}
<td>{{ cell }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>
{% endblock %}
# h5. Отчёт по приходам за заданный период времени
{code}
# h5. Отчёт по определенной услуге. Абонент, логин, баланс, конец действия услуги.
{code:lang=sql}
select USERS.LOGIN as "Логин",
ABONENTS.name as "ФИО",
(ADMIN_ACCOUNTS.OSTATOK + ADMIN_ACCOUNTS.DEBIT - ADMIN_ACCOUNTS.CREDIT)/10000000000 as "Баланс",
USERS_USLUGA.END_TIME as "Конец"
from USERS_USLUGA left join ABONENTS on ABONENTS.ID = USERS_USLUGA.ABONENT_ID
left join ADMIN_ACCOUNTS on ABONENTS.ACCOUNT_ID = ADMIN_ACCOUNTS.ID
left join USERS on ABONENTS.ID = USERS.ABONENT_ID
where USERS_USLUGA.USLUGA_ID = (':ID нужной услуги$')
and USERS_USLUGA.DELETED != 1
order by ABONENTS.NAME
select a.name as "ФИО",
a.contract_number as "Договор",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
AU.USERNAME as "Ответственный",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
on a.id=fo.abonent_id
left join AUTH_USER as AU
on AU.id=fo.owner_id
where OP_TYPE=2 and
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
{code}
# h5. Отчёт по абонентам в формате "ФИО ЛОГИН IP MAC ТЕЛЕФОН АДРЕС ТАРИФ БАЛАНС"
{code:lang=sql}
select distinct
A.NAME as "ФИО",
U.LOGIN as "Логин",
UF_IP2STRING(U.IP) as "IP",
U.MAC as "MAC",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
T.name as "Тариф",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0
order by A.NAME
# h5. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%"
{code}
# h5. Отчёт, выводящий сумму списаний, взятых из выставленных актов, за определенный период по всем абонентам.
{code:lang=sql}
select sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма "
from finance_operations
where op_type=1
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
{code}
# h5. Отчет для вывода рекомендуемого ежемесячно платежа, стоимости тарифа по всем абонентам.
{code}
select
a.CONTRACT_NUMBER as "№",
a.NAME as "ФИО",
t.NAME as "ТАРИФ",
round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "АБОН.ПЛАТА",
round(ac.RECOMEND_PAY_SUM_CACHE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "РЕКОМЕНД.",
st.name as "СТАТУС",
subst.name as "ПОДСТАТУС",
cast(obs.apply_date as date) as "ДАТА"

from ABONENTS a
LEFT JOIN ABONENTS_CACHE ac ON ac.ID=a.ID
LEFT JOIN TARIF t ON a.TARIF_ID=t.ID
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN STATUS subst on subst.id = obs.SUBSTATUS

where obs.OBJECT_NAME='Abonents'
and a.is_folder = 0
and a.parent_id != 244
and a.parent_id != 2
{code}
# h5. Отчёт по платежам через веб - кассу(По операторам)
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.DESCR as "Описание",
round(fo.aa_ostatok/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
FO.op_summa/(select round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$') and AU.USERNAME = ':Администратор$'
where 1=1 and (UPPER(FO.DESCR) LIKE UPPER('%Альфа%'))
and (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$')
{code}
# h5. Поле "Не отключать при превышении порога", ID, ФИО.
# h5. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус.
{code}
select
a.ID as "ID",
a.NAME as "ФИО",
aa.UNLIMITED as "Не отключать"
from ABONENTS a
left join ADMIN_ACCOUNTS aa on a.ACCOUNT_ID=aa.ID
a.contract_number as "Договор",
a.name as "Имя",
('г. ' || h.city || ' ' || h.street || ', д.' || h.s_number || ', кв.' || a.a_home_number) as "Адрес",
(select av.attribute_value from attribute_values av where av.attribute_id = 1 and av.abonent_id = a.id) as "Телефон",
cast(round((cast(aa.ostatok as float)+cast(aa.debit as float)-cast(aa.credit as float))/10000000000,2) as numeric(18,2)) as "Баланс",
(select st.name from status st where st.id = os.status) as "Статус"
from
abonents a
left join
homes h on h.id = a.home_id
join
admin_accounts aa on aa.id = a.account_id
join
objects_status os on a.id = os.object_id
where a.IS_FOLDER=0
order by a.NAME
a.is_folder = 0
{code}
# h5. Учёт заключенных договоров за период
# h5. Отчёт, выводящий сумму списаний, взятых из выставленных актов, за определенный период по всем абонентам.
{code:lang=sql}
select sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма "
from finance_operations
where op_type=1
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
{code}
select
a.CREATE_DATE as "Дата договора",
a.CONTRACT_NUMBER as "№ договора",
t.NAME as "Тариф",
st.NAME as "Cтатус",
a.NAME as "ФИО абонента",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
a.ACTIVATE_DATE as "Дата активации",
n.NAME as "NAS"
from ABONENTS a

LEFT JOIN TARIF t on a.TARIF_ID=t.ID
LEFT JOIN USERS u on u.ABONENT_ID=a.ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN NAS n on u.nas_id=n.ID
left join HOMES h on a.HOME_ID=h.ID
where a.IS_FOLDER=0
and a.CREATE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
{code}
# h5. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)"
{code}
...
order by NAME
{code}
# h5. Отчёт по приходам за заданный период времени
# h5. Отчёт по платившим абонентам в формате "Номер договора - ФИО - Группа - Дата и время операции - Описание - Сумма" с выводом итоговой суммы и возможностью выбора платежей по конкретной папке.
{code}
select AB.CONTRACT_NUMBER as "Номер договора",
AB.NAME as "ФИО",
(select name from abonents where abonents.id=ab.parent_id) as "Группа",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and ab.parent_id=(':ID группы$')
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null)
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, FO.SYSTEM_DATE, AB.PARENT_ID
union all
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and ab.parent_id=(':ID группы$')
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null))
order by 4
{code}
Тот же отчёт по всем абонентам
{code}
select AB.CONTRACT_NUMBER as "Номер договора",
AB.NAME as "ФИО",
(select name from abonents where abonents.id=ab.parent_id) as "Группа",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null)
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, FO.SYSTEM_DATE, AB.PARENT_ID
union
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null))
{code}
# h5. Отчет выводит данные по операциям по всем абонентам за выбранный период
{code}
select
a.contract_number "Договор",
a.name "ФИО",
fo.op_date "Дата",
au.username "Ответстывенный",
ft.op_name || ' № ' || fo.number || ' от ' || extract(day from op_date) || '-' || extract(month from op_date) || '-' || extract(year from op_date) || ' за период ' || extract(day from period_end_date) || '-' || extract(month from period_end_date) || '-' || extract(year from period_end_date) as "Наименование",
descr "Описание",
(case when fo.OPERATION_SIGN <> 0 then cast(fo.OP_SUMMA/10000000000.00*OPERATION_SIGN as varchar(100)) else '' end)
from finance_operations fo
join abonents a on fo.abonent_id = a.id
join fin_types ft on fo.op_type = ft.type_id
join auth_user au on fo.owner_id = au.id
where
fo.op_date between ':C_даты|date$' and ':По_дату|date$'
order by op_date
{code}
# h5. Отчет выводит абонентов не имеющих операции приход до указанной даты. Поиск по конкретной папке.
{code}
select distinct
a.name as "ФИО",
u.login as "LOGIN",
a.contract_number as "№ ДОГОВОРА",
round((aa.ostatok+aa.debit-aa.credit) / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "БАЛАНС"
from abonents a
left join finance_operations fo on fo.abonent_id=a.id
left join users u on u.abonent_id=a.id
left join admin_accounts aa on aa.id=a.account_id
where a.id not in (select fo.abonent_id from finance_operations fo where fo.op_date >= cast(':Дата|date$' as timestamp) and fo.op_type=2)
and a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
and a.is_folder=0
and a.deleted=0
{code}
# h5. Отчёт о списаниях абонентской платы по папкам за определенный период.
{code}
select (select name from abonents a1 where a1.id=a.parent_id), sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма "
from finance_operations fo
left join abonents a on a.id=fo.abonent_id
where op_type=1
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
group by a.parent_id
{code}
# h5. Отчёт, который выводит списания абонентов с учетом и без учета НДС за выбранный период по каждому абоненту.
{code}
select a.name as "ФИО",
a.contract_number as "Договор",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
AU.USERNAME as "Ответственный",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
h.CITY as "Город",
sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма с ндс ",
round((sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)))/1.18) as "Сумма без ндс"
from finance_operations fo
left join abonents a on a.id=fo.abonent_id
left join AUTH_USER as AU
on AU.id=fo.owner_id
where OP_TYPE=2 and
join HOMES h on h.ID = A.HOME_ID
where op_type=1
and a.deleted=0
and a.is_folder=0
fo.OP_DATE and op_date between (':1 Дата|date$') and (':2 Дата|date$')
group by a.id, a.name, a.contract_number, h.city
{code}
# h5. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%"
# h5. Отчёт по всем финансовым операциям по конкретному администратору за выбранный период времени.
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата платежа", операции",
FT.OP_NAME as "Тип операции",
A.name as "ФИО",
A.contract_number as "Договор",
...
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where 1=1 and (UPPER(FO.DESCR) LIKE UPPER('%Альфа%'))
left join FIN_TYPES FT on FT.TYPE_ID=FO.op_type
and (FO.op_type=2) and where (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$')
and FO.owner_id=:Администратор|select[AdminUser]$
{code}
# h5. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период.
# h5. Отчёт по поступившим платежам за выбранный период, с возможностью выбора папки для поиска и пользователя,который добавил платеж.
{code}
select distinct u.name as "Услуга",
select AB.CONTRACT_NUMBER as "Номер договора",
AB.NAME as "ФИО",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
AU.USERNAME as "Добавлен",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
left join AUTH_USER AU on FO.OWNER_ID = AU.ID
where OP_TYPE = 2
and (FO.OP_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0))
and (AB.DELETED = 0
or AB.DELETED is null)

group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, AU.USERNAME, FO.SYSTEM_DATE
union
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (FO.OP_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0))
and (AB.DELETED = 0
or AB.DELETED is null))
{code}
# h5. Отчёт по наличным/безналичным приходам с группировкой по тарифам абонентов за указанный период.
{code}
select
t.name as "Тариф",
(
select
round(u.summa sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена", 2))
round(sum(c.SUMM), 2) as "Сумма"
from counters c
left join usluga u on u.id=c.usluga_id
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

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
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
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. Сформированные акты/счета по юридическим лицам.
# h5. Отчёт по начислениям по видам услуг за выбранный период
Отчёт покажет начисления по видам услуг за выбранный период
В case можно добавить собственную логику постороения отчёта в зависимости от натсроек услуг; в примере ниже:
#* услуги с типом стандартный будут посчитаны как "разовые" или "прочие" в зависимости от типа списания
#* все услути касающиеся интернет-трафика (форсаж, пакет МБ и тд) будут отражены просто как "трафик"
{code}
select AB.NAME as "ФИО",
CONTRACT_NUMBER as "Договор",
FO.NUMBER as "Номер акта",
round(FO.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "СУММА",
FT.OP_NAME || ', ' || FT.OP_DESCR as "ТИП ДОКУМЕНТА"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
left join FIN_TYPES FT on FO.OP_TYPE = FT.TYPE_ID
where (FO.OP_TYPE = 5
or FO.OP_TYPE = 9
or FO.OP_TYPE = 1)
and FO.ABONENT_ID = AB.ID
and AB.COMPANY = 1
and FO.OP_DATE between cast(':1. Начало|date$' as date) and cast(':2. Конец|date$' as date)
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 1
:3-Сортировать по|choices[1,2^]Месяцу^[2,1^]Типу услуг]$
{code}
# h5. Отчёт по юридическим лицам, выводящий ФИО Телефон Адрес Р/С ИНН КПП
# h5. Отчет формирует информацию за период по следующим полям: входящий баланс абонента, списания за указанный месяц, оплачено, исходящий баланс, НДС 20% от списаний.
Если нет необходимости разделять на типы услуг, то можно убрать поля и скорректировать нахождение "Исходящего остатка"
{code}
select A.NAME as "ФИО",
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 "Физ./Юр.",
A.SMS coalesce(tel.attribute_value,'') as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
av1.attribute_value as "Р/С",
av2.attribute_value as "ИНН",
av3.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 as a
left join HOMES as H on H.ID=A.HOME_ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES AV2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES AV3 on a.id=av3.ABONENT_ID and av3.ATTRIBUTE_ID=5
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 a.company=1
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. Отчёт прибыль по плате за подключение нескольких услуг.
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 "Физ./Юр.",
/*Выполянется проверка, какие данные выводить по входящему балансу, в зависимости от даты создания абонента*/
select case
a.NAME as "ФИО",
uu.ABONENT_ID as "ID АБОНЕНТА",
cast(uu.CREATE_DATE as date) as "ДАТА АКТИВАЦИИ",
c.SUMM as "Прибыль"
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0
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)
/*получаем бух.баланс из акта за предыдущий месяц от искомого*/

union all
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

SELECT
cast('' as varchar(32)),
cast('' as varchar(32)),
cast('Итого' as varchar(32)),
ROUND (sum(c.summ))
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0
where
a.is_folder=0
and a.deleted = 0
and a.id > 0
and a.parent_id not in (2, 244)
{code}
# h5. Кто платит через Юнителлер?
{code}
select distinct(pay_id_str_in) as "PAY",
CONTRACT_NUMBER_IN as "Договор",Пример
USER_NAME_OUT as "ФИО",
operator_date_in as "Дата",
SUMMA_IN as "Сумма"
from pay_log
where (operator_src_ip='213.208.182.172' or pay_operator='Uniteller')
and act_in='pay'
and operator_date_in between ':C_даты|date$' and ':По_дату|date$'
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}
select A.ID,
A.CONTRACT_NUMBER,
A.NAME,
count(*)
from USERS_USLUGA UU
left join ABONENTS A on UU.ABONENT_ID = A.ID
where UU.USLUGA_ID = ':ID услуги$' and UU.IS_ZAKAZANO = 1 and UU.CREATE_DATE between ':C_даты|date$' and ':По_дату|date$'
group by A.ID, A.CONTRACT_NUMBER, A.NAME
having count(*)>1
order by A.NAMEe_in between ':C_даты|date$' and ':По_дату|date$'
{code}
# h5. Просмотр пользователей в онлайне с выводом времени онлайна
{code}
select cast(U.LOGIN as varchar(128)) as "логин",
cast(H.STREET as varchar(128)) as "Улица",
cast(H.S_NUMBER as varchar(128)) as "№ дома",
cast(AB.A_HOME_NUMBER as varchar(128)) as "№ квартиры",
cast(UF_IP2STRING(U.IP) as varchar(128)) as "IP-адрес",
cast((time '00:00:00' + datediff(second, RS.START_TIME, current_timestamp)) as varchar(128)) as "время в онлайне"
from USERS_RADIUSAUTH UR
left join USERS U on UR.USER_ID = U.ID
left join ABONENTS AB on U.ABONENT_ID = AB.ID
left join HOMES H on AB.HOME_ID = H.ID
left join RADIUS_SESSIONS RS on UR.ACCT_SESSION_ID = RS.ACCT_SESSION_ID
where UR.LOGGED = 1
union
select cast('#ИТОГО ПОЛЬЗОВАТЕЛЕЙ В ОНЛАЙНЕ' as varchar(128)) as "логин",
cast('' as varchar(128)) as "Улица",
cast('' as varchar(128)) as "№ дома",
cast('' as varchar(128)) as "№ квартиры",
cast('' as varchar(128)) as "IP-адрес",
cast(count(UR.LOGGED) as varchar(128)) as "время в онлайне"

from USERS_RADIUSAUTH UR
where UR.LOGGED = 1

order by 1
{code}
# h5. Отчёт выводит абонентов, последнее подключение которых было раньше, чем указанная дата.
{code}
...
inner join (select rs.abonent_id, max(rs.start_time) as max_time from radius_sessions rs group by abonent_id) as b on a.id=b.abonent_id where max_time<(':1 Дата|date$')
{code}
# h5. Отчёт выводит информацию по абонентам с определенным NAS'ом.
{code}
select distinct
...
and uf_ip2string(nas.ip)=(':nas_ip$')
{code}
# h5. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус.
{code}
select
a.contract_number as "Договор",
a.name as "Имя",
('г. ' || h.city || ' ' || h.street || ', д.' || h.s_number || ', кв.' || a.a_home_number) as "Адрес",
(select av.attribute_value from attribute_values av where av.attribute_id = 1 and av.abonent_id = a.id) as "Телефон",
cast(round((cast(aa.ostatok as float)+cast(aa.debit as float)-cast(aa.credit as float))/10000000000,2) as numeric(18,2)) as "Баланс",
(select st.name from status st where st.id = os.status) as "Статус"
from
abonents a
left join
homes h on h.id = a.home_id
join
admin_accounts aa on aa.id = a.account_id
join
objects_status os on a.id = os.object_id
where
a.is_folder = 0
{code}
# h5. Поиск IP-адреса в истории изменения учетных записей (история выдачи IP-адреса)
При каждом изменении учётной записи новое состояние учётной записи записывается в историю. Таблица *users_history*. Отчёт покажет когда абоненту был назначен IP-адрес, а также были ли измененяия по абоненту пока он владел этим адресом. Отчёт не показывает когда IP-адрес был снят с абонента.
...
order by A.NAME
{code}
# h5. Отчёт по платившим абонентам в формате "Номер договора - ФИО - Группа - Дата и время операции - Описание - Сумма" с выводом итоговой суммы и возможностью выбора платежей по конкретной папке.

h2. Услуги

# h5. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период.
{code}
select AB.CONTRACT_NUMBER as "Номер договора",
AB.NAME as "ФИО",
(select name from abonents where abonents.id=ab.parent_id) as "Группа",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and ab.parent_id=(':ID группы$')
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null)
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, FO.SYSTEM_DATE, AB.PARENT_ID
union all
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and ab.parent_id=(':ID группы$')
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null))
order by 4
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(c.SUMM), 2) as "Сумма"
from counters c
left join usluga u on u.id=c.usluga_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 u.name, u.SUMMA
{code}
Тот же отчёт по всем абонентам
# h5. Сформированные акты/счета по юридическим лицам.
{code}
select AB.CONTRACT_NUMBER as "Номер договора",
AB.NAME as "ФИО",
(select name from abonents where abonents.id=ab.parent_id) as "Группа",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
select AB.NAME as "ФИО",
CONTRACT_NUMBER as "Договор",
FO.NUMBER as "Номер акта",
round(FO.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "СУММА",
from VPN_CONST FT.OP_NAME || ', ' || FT.OP_DESCR as "ТИП ДОКУМЕНТА"
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
left join FIN_TYPES FT on FO.OP_TYPE = FT.TYPE_ID
where (FO.OP_TYPE = 5
or FO.OP_TYPE = 9
or FO.OP_TYPE = 1)
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$') FO.ABONENT_ID = AB.ID
and (AB.DELETED AB.COMPANY = 0 1
or AB.DELETED is null)
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, FO.SYSTEM_DATE, AB.PARENT_ID
union
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null))
and FO.OP_DATE between cast(':1. Начало|date$' as date) and cast(':2. Конец|date$' as date)
order by 1
{code}
# h5. Отчёт по определенной услуге. Абонент, логин, баланс, конец действия услуги.
{code:lang=sql}
select USERS.LOGIN as "Логин",
ABONENTS.name as "ФИО",
(ADMIN_ACCOUNTS.OSTATOK + ADMIN_ACCOUNTS.DEBIT - ADMIN_ACCOUNTS.CREDIT)/10000000000 as "Баланс",
USERS_USLUGA.END_TIME as "Конец"
from USERS_USLUGA left join ABONENTS on ABONENTS.ID = USERS_USLUGA.ABONENT_ID
left join ADMIN_ACCOUNTS on ABONENTS.ACCOUNT_ID = ADMIN_ACCOUNTS.ID
left join USERS on ABONENTS.ID = USERS.ABONENT_ID
where USERS_USLUGA.USLUGA_ID = (':ID нужной услуги$')
and USERS_USLUGA.DELETED != 1
order by ABONENTS.NAME
{code}
# h5. Поиск абонентов со скидкой, с выводом самой скидки, id абонента, id услуги и фио абонента.
{code}select
u.discount,
u.abonent_id,
u.usluga_id,
a.name
from USERS_USLUGA u
join abonents a on a.id=u.abonent_id
where
u.ACTIVATED=1
and u.DELETED=0
and u.discount>0
{code}
# 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. Блокировки
# h5. Отчет выводящий информацию из карточки абонента с текущим статусом; колонки административной и добровольной блокировками заполняются только в случае активной блокировки; в последней колонке пишется дата, до которой действительна добровольная блокировка
{code}
...
or AB.IS_FOLDER is null) and AB.DELETED = 0
{code}
# h5. Отчет выводит данные по операциям по всем абонентам за выбранный период
{code}
select
a.contract_number "Договор",
a.name "ФИО",
fo.op_date "Дата",
au.username "Ответстывенный",
ft.op_name || ' № ' || fo.number || ' от ' || extract(day from op_date) || '-' || extract(month from op_date) || '-' || extract(year from op_date) || ' за период ' || extract(day from period_end_date) || '-' || extract(month from period_end_date) || '-' || extract(year from period_end_date) as "Наименование",
descr "Описание",
(case when fo.OPERATION_SIGN <> 0 then cast(fo.OP_SUMMA/10000000000.00*OPERATION_SIGN as varchar(100)) else '' end)
from finance_operations fo
join abonents a on fo.abonent_id = a.id
join fin_types ft on fo.op_type = ft.type_id
join auth_user au on fo.owner_id = au.id
where
fo.op_date between ':C_даты|date$' and ':По_дату|date$'
order by op_date
{code}
# h5. Отчет выводит абонентов не имеющих операции приход до указанной даты. Поиск по конкретной папке.
{code}
select distinct
a.name as "ФИО",
u.login as "LOGIN",
a.contract_number as "№ ДОГОВОРА",
round((aa.ostatok+aa.debit-aa.credit) / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "БАЛАНС"
from abonents a
left join finance_operations fo on fo.abonent_id=a.id
left join users u on u.abonent_id=a.id
left join admin_accounts aa on aa.id=a.account_id
where a.id not in (select fo.abonent_id from finance_operations fo where fo.op_date >= cast(':Дата|date$' as timestamp) and fo.op_type=2)
and a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
and a.is_folder=0
and a.deleted=0
{code}
# h5. Отчёт, выводящий список незаблокированных абонентов.
{code}
...
and uu.deleted=0
{code}
# h5. Отчёт в формате "ФИО-номер договора-телефон-адрес"
# h5. Отчёт по услугам IP телевидения с возможностью выбора периода.
Выводит следующие поля:
1) Имя услуги
2) Цену услуги
3) Количество абонентов, у которых подключена конкретная услуга в выбранный период
4) Количество оплаченных дней за указанный период
5) Сумму, списанную по каждой услуге за указанный период
6) Итоговую сумму по всем услугам за указанный период
{color:#ff0000}{*}Примечание{*}{color}{color:#000000}*: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".*{color}
{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 "Цена услуги",
coalesce((select count(distinct abonent_id) from arch_account_stack where BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') and arch_account_stack.usluga_id=u.id),0) as "Кол-во абонентов",
coalesce((select count(*) from arch_account_stack where arch_account_stack.usluga_id=u.id and BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by usluga_id),0) as "Дни",
(select coalesce((round(sum(counters1.SUMM), 2)),0) from counters counters1 where S_DATE between (':1 Дата|date$') and (':2 Дата|date$') and counters1.usluga_id=u.id)as "Сумма"
from usluga u
join counters on counters.usluga_id=u.id
where U.SYSTEM_TYPE=7
and u.id is not null
group by u.name, u.SUMMA, u.id
union all
select '"Итого"',
A.NAME as "ФИО", null,
A.CONTRACT_NUMBER as "Номер договора", null,
A.SMS as "Телефон", null,
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
sum(SUMOP)
from (select round(sum(counters.SUMM), 2) as SUMOP
from usluga u
join counters on counters.usluga_id=u.id
where A.IS_FOLDER=0 U.SYSTEM_TYPE=7
order by A.NAME
and u.id is not null
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
{code}
# h5. Абоненты с учетными записями без mac-адреса. Формат: номер договора, ip учетной записи (если есть)
# h5. Отчёт по абонентам, у которых подключены услуги IP телевидения.
Выводит следующие поля:
1) Имя абонента
2) Цена услуги
3) Название услуги
4) Сумму, списанную по конкретному абоненту за услугу за указанный период
5) Итоговую сумму по всем абонентам за услуги IP телевидения за указанный период
{color:#ff0000}{*}Примечание{*}{color}{color:#000000}*: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".*{color}
{code}
select distinct
(select name from abonents a where a.id=counters.abonent_id) as "Абонент",
round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена услуги",
u.name as "Услуга",
round(sum(counters.SUMM), 2) as "Сумма"
from counters
join usluga u on u.id=counters.usluga_id
where U.SYSTEM_TYPE=7
and u.id is not null
and counters.MONTH_NUMBER=(':Месяц$')
and counters.YEAR_NUMBER=(':Год$')
group by counters.abonent_id, u.name, u.SUMMA
union all
select '"Итого"',
null,
null,
sum(SUMOP)
from (select round(sum(counters.SUMM), 2) as SUMOP
from counters
join usluga u on u.id=counters.usluga_id
where U.SYSTEM_TYPE=7
and u.id is not null
and counters.MONTH_NUMBER=(':Месяц$')
and counters.YEAR_NUMBER=(':Год$'))
{code}
# h5. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения.
{code}
select distinct
a.name as "ФИО",
(select name from status where id = OBJECTS_SUBSTATUS.STATUS_ID) as "Тип подключения"
from abonents a
left join OBJECTS_STATUS os on os.OBJECT_ID=a.id
left join OBJECTS_SUBSTATUS on OBJECTS_SUBSTATUS.OBJECT_ID=os.id
{code}
# h5. Отчёт, который выводит все активные RADIUS-сессии.
{code}select
a.contract_number as "Номер договора",
coalesce(uf_ip2string(u.ip), '') as "IP"
a.contract_number as "Номер договора",
u.login as "Учетная запись",
uf_ip2string(r.ip_address) as "IP",
RADIUS_UPDATE as "Последний ACC_UPDATE"
from
users u
join
abonents a
on u.abonent_id = a.id
users_radiusauth r
join
users u
on
r.user_id=u.id
join
abonents a
on
u.abonent_id=a.id
where
u.phone is null and
u.deleted = 0 and
a.deleted = 0 and
a.is_folder = 0 and
u.is_template = 0 and
(u.mac is null or u.mac = ''){code}
# h5. Отчёт о списаниях абонентской платы по папкам за определенный период.
r.logged=1
order by
:1. Группировать по столбцу|choices[1^]Номер договора^[2^]Учетная запись^[3^]IP^[4^]ACC_UPDATE]$ :2. Группировать в порядке|choices[asc^]Возрастания^[desc^]Убывания]$
{code}
select (select name from abonents a1 where a1.id=a.parent_id), sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма "
from finance_operations fo
left join abonents a on a.id=fo.abonent_id
where op_type=1
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
group by a.parent_id
# h5. Отчёт по абонентам, у которых дата следующего списания больше, чем указанная.
{code}
select
distinct a.id as id,
a.name as "ФИО",
uu.next_date as "След. списание",
a.contract_number as "Договор"
from abonents a
left join users_usluga uu on uu.abonent_id=a.id
where uu.next_date>(':1 Дата|date$') and a.deleted=0
{code}
# h5. Отчет для поиска по реквизитам
Отчет ориентирован только на [реквизиты текстового типа|Реквизиты].
При использовании вместе с шаблоном отчета имя абонента будет преобразовано в гиперссылку на его карточку в БД
h6. Текст запроса
{code}
select
a.id,
a.name,
a.contract_number,
ua.name,
av.attribute_value
from
abonents a
join
attribute_values av on a.id=av.abonent_id and av.attribute_value like '%:Значение реквизита$%'
join
user_attributes ua on av.attribute_id=ua.attribute_id
{code}
h6. Шаблон отчета
{code}{% extends "form_list.html" %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}{% load mathfilters %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><h2>Заполните поля запроса</h2></legend>
{{ form }}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<div class="btn-group">
<a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
<i class="icon-print icon-white"></i>
Выгрузка
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><button type="submit" name="csv" value="True">CSV</button></li>
<li><button type="submit" name="dbf" value="True">DBF</button></li>
</ul>
</div>
<button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{inst.name}}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" style="text-align: center;" cellpadding="5">
<tr>
<th>ФИО/Название</th>
<th>Договор</th>
<th>Реквизит</th>
<th>Значение</th>
</tr>
{% for row in data %}
<tr align="left" cellpadding="10">
<td><a href="/admin/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
<td>{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td>{{ row.4 }}</td>
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}
{code}
# h5. Найти список абонентов, у которых дата начала добровольной блокировки была назначена в прошлом относительно даты включения.
В отчете указываем период, в течение которого необходимо проверить события.
{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. Телефония

# h5. Отчёт по телефонии. Структура данных информации об Абонентах по маске 7496
h6. sql запрос
...
{% endblock %}
{code}
# h5. Потребленный трафик суммарно по всем абонентам с возможностью выбора периода
{code}SELECT FIRST 1
(SELECT cast(sum(v) AS numeric(18,2)) AS "Входящий"
FROM counters
WHERE YEAR_NUMBER = (':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
AND MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
AND UNIT_ID = 2
AND V_TYPE_ID = 1),
(SELECT cast(sum(v) AS numeric(18,2)) AS "Исходящий"
FROM counters
WHERE YEAR_NUMBER = (':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
AND MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
AND UNIT_ID = 2
AND V_TYPE_ID = 2)
FROM counters{code}
# h5. Поиск абонентов со скидкой, с выводом самой скидки, id абонента, id услуги и фио абонента.
{code}select
u.discount,
u.abonent_id,
u.usluga_id,
a.name
from USERS_USLUGA u
join abonents a on a.id=u.abonent_id
where
u.ACTIVATED=1
and u.DELETED=0
and u.discount>0
{code}
# h5. Отчёт, который выводит списания абонентов с учетом и без учета НДС за выбранный период по каждому абоненту.
{code}
select a.name as "ФИО",
a.contract_number as "Договор",
h.CITY as "Город",
sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма с ндс ",
round((sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)))/1.18) as "Сумма без ндс"
from finance_operations fo
left join abonents a on a.id=fo.abonent_id
join HOMES h on h.ID = A.HOME_ID
where op_type=1
and a.deleted=0
and a.is_folder=0
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
group by a.id, a.name, a.contract_number, h.city
{code}
# h5. Отчёт по всем финансовым операциям по конкретному администратору за выбранный период времени.
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата операции",
FT.OP_NAME as "Тип операции",
A.name as "ФИО",
A.contract_number as "Договор",
FO.DESCR as "Описание",
round(fo.aa_ostatok/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
left join FIN_TYPES FT on FT.TYPE_ID=FO.op_type
where (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$')
and FO.owner_id=:Администратор|select[AdminUser]$
{code}
# h5. Отчёт по исходящему телефонному трафику за выбранный период
Отчёт выводит сверку по потреблению услуги телефонного трафика с разделением по категориям и абонентам.
{code}
select
vc.name || ' (' || vc.id || ')' as "Категория",
a.contract_number as "№ Договора абонента",
sum(vl.bill_sum)/10000000000.00 as "Сумма",
cast(sum(vl.bill_sec_round) as numeric(18,2))/60 as "Длительность",
a.name as "Название/ФИО"
from
voip_log vl
join usluga u on vl.usluga_id=u.id
join usluga_voip uv on u.id=uv.usluga_id
join category_directions cd on uv.voipcategory_id=cd.category_id
join voip_direction vd on vd.id=cd.direction_id and vd.id=vl.direction_id
join voip_category vc on cd.category_id=vc.id
join abonents a on vl.abonent_id=a.id
where
vl.bill_sum>0 and
vl.s_time between ':Начало|date$' and ':Конец|date$' and
vl.V_TYPE_ID = 2 and
vl.error_code is null
group by 1, a.contract_number, a.name
order by a.contract_number
{code}
# h5. Отчёт по абонентам со статусом, балансом, датой последнего платежа и суммой последнего платежа.
{code}
select
ab.name as "ФИО",
ab.contract_number as "Договор",
s.name as "Статус",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID and FO.OP_TYPE=2) as "Дата посл.",
(select first 1 round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) from finance_operations FO1 where FO1.ABONENT_ID = AB.ID and FO1.OP_TYPE=2 order by fo1.op_date desc) as "Платеж",
(aa.debit+aa.Ostatok-aa.credit)/10000000000.00 as "Баланс"
from abonents ab
left join objects_status os on
os.object_id=ab.id
left join status s on
s.id = os.status
left join admin_accounts aa on
aa.id = ab.account_id
where extract (DAY from ab.activate_date) >0
{code}
# h5. Отчёт по телефонии по звонкам, которые были выполнены на конкретный пул телефонных номеров за указанную дату по конкретному абоненту.
{code}
...
AND (vl.DST between (':4 Начало пула$') and (':5 Конец пула$'))
{code}
# h5. Отчёт по RADIUS-сессиям выбранного абонента: начало, конец, длительность, причина завершения (если завершена), IP.
{code}select
START_TIME "Начало",
END_TIME "Конец",
case
when coalesce(end_reason,'')='' then 'Не было Act-Session-Stop'
when datediff(hour from start_time to end_TIME)>=1 then datediff(hour from start_time to end_TIME) || ' часов'
when datediff(minute from start_time to end_TIME)<1 then 'Меньше минуты'
when datediff(hour from start_time to end_TIME)<1 then datediff(minute from start_time to end_TIME) || ' минут'
end as "Длительность",
coalesce(END_REASON,'Отсутствует') as "Причина",
uf_ip2string(ip) as "IP"
from
radius_sessions
where
abonent_id=':Абонент|select[Abonents]$'
order by
start_time desc{code}
# h5. Отчёт для ФСБ в формате Оператор связи IP-адрес Логин Дата подключения Дата отключения Адрес подключения Абонент Дата рождения Документ (ИНН) Адрес регистрации (юридический) Контактное лицо Контактный номер Дополительня информаци
{code}
select '' from abonents
UNION
select distinct
'"' || (select name from abonents where abonents.id=a.operator_id) || '";"' ||
uf_ip2string(u.ip) || '";"' ||
u.login || '";"' ||
a.activate_date || '";"' ||
coalesce(a.disabled_date,'') || '";"' ||
coalesce( h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER,'') || '";"' ||
' ' || '";"' ||
a.name || '";"' ||
coalesce(DR.ATTRIBUTE_VALUE,' ') || '";"' ||
coalesce(INN.ATTRIBUTE_VALUE,' ') || '";"' ||
coalesce(UADR.ATTRIBUTE_VALUE,' ') || '";"' ||
coalesce(KLICO.ATTRIBUTE_VALUE,' ') || '";"' ||
a.sms || '";"' ||
' ' || '"'
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join ATTRIBUTE_VALUES DR on DR.ABONENT_ID=A.ID and DR.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES KLICO on KLICO.ABONENT_ID=A.ID and KLICO.ATTRIBUTE_ID=32
left join ATTRIBUTE_VALUES INN on INN.ABONENT_ID=A.ID and INN.ATTRIBUTE_ID=4
{code}
# h5. Отчёт по абонентам, у которых количество услуг больше 1, показывающий количество подключенных услуг и количество активных услуг
{code}
select abonent_id,
count(*) as "Кол-во услуг",
(select count(*) from users_usluga uu1 where uu1.deleted=0 and uu1.enabled=1 and uu1.abonent_id=users_usluga.abonent_id having count(*)>1) as "Кол-во акт. услуг"
from users_usluga where deleted=0 group by abonent_id having count(*)>1
{code}
# h5. История выдачи телефонного номера для voip.
{code}
select
abonent_id,time_changed,a.name
from
users_history uh
join
abonents a
on uh.abonent_id = a.id
where
phone = (select id from PHONE_PULL_CHACHE where PHONE=':Введите номер$')
{code}
# h5. История использования логина.
{code}
select
uf_ip2string(ip) as IP,abonent_id,time_changed,a.name
from
users_history uh
join
abonents a
on uh.abonent_id = a.id
where
LOGIN_UPPER like upper(':Логин$')
{code}
# h5. Отчёт по услугам IP телевидения с возможностью выбора периода.
Выводит следующие поля:
1) Имя услуги
2) Цену услуги
3) Количество абонентов, у которых подключена конкретная услуга в выбранный период
4) Количество оплаченных дней за указанный период
5) Сумму, списанную по каждой услуге за указанный период
6) Итоговую сумму по всем услугам за указанный период
{color:#ff0000}{*}Примечание{*}{color}{color:#000000}*: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".*{color}
{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 "Цена услуги",
coalesce((select count(distinct abonent_id) from arch_account_stack where BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') and arch_account_stack.usluga_id=u.id),0) as "Кол-во абонентов",
coalesce((select count(*) from arch_account_stack where arch_account_stack.usluga_id=u.id and BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by usluga_id),0) as "Дни",
(select coalesce((round(sum(counters1.SUMM), 2)),0) from counters counters1 where S_DATE between (':1 Дата|date$') and (':2 Дата|date$') and counters1.usluga_id=u.id)as "Сумма"
from usluga u
join counters on counters.usluga_id=u.id
where U.SYSTEM_TYPE=7
and u.id is not null
group by u.name, u.SUMMA, u.id
union all
select '"Итого"',
null,
null,
null,
sum(SUMOP)
from (select round(sum(counters.SUMM), 2) as SUMOP
from usluga u
join counters on counters.usluga_id=u.id
where U.SYSTEM_TYPE=7
and u.id is not null
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
{code}
# h5. Отчёт по абонентам, у которых подключены услуги IP телевидения.
Выводит следующие поля:
1) Имя абонента
2) Цена услуги
3) Название услуги
4) Сумму, списанную по конкретному абоненту за услугу за указанный период
5) Итоговую сумму по всем абонентам за услуги IP телевидения за указанный период
{color:#ff0000}{*}Примечание{*}{color}{color:#000000}*: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".*{color}
{code}
select distinct
(select name from abonents a where a.id=counters.abonent_id) as "Абонент",
round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена услуги",
u.name as "Услуга",
round(sum(counters.SUMM), 2) as "Сумма"
from counters
join usluga u on u.id=counters.usluga_id
where U.SYSTEM_TYPE=7
and u.id is not null
and counters.MONTH_NUMBER=(':Месяц$')
and counters.YEAR_NUMBER=(':Год$')
group by counters.abonent_id, u.name, u.SUMMA
union all
select '"Итого"',
null,
null,
sum(SUMOP)
from (select round(sum(counters.SUMM), 2) as SUMOP
from counters
join usluga u on u.id=counters.usluga_id
where U.SYSTEM_TYPE=7
and u.id is not null
and counters.MONTH_NUMBER=(':Месяц$')
and counters.YEAR_NUMBER=(':Год$'))
{code}
# h5. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 496.
В данном примере:
...
exists(select first 1 1 from users u join phone_pull_chache ppc on ppc.id=u.phone where ppc.phone like '7496%' and u.abonent_id=a.id) and
c.summ<>0
--Получаем только счётчики с типом минуты
and c.unit_id=3
order by 2,9
{code}
h6. Шаблон отчёта:
...
{% endblock %}
{code}
# h5. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения.
{code}
select distinct
a.name as "ФИО",
(select name from status where id = OBJECTS_SUBSTATUS.STATUS_ID) as "Тип подключения"
from abonents a
left join OBJECTS_STATUS os on os.OBJECT_ID=a.id
left join OBJECTS_SUBSTATUS on OBJECTS_SUBSTATUS.OBJECT_ID=os.id
{code}
# h5. Отчёт, который выводит все активные RADIUS-сессии.
{code}select
a.contract_number as "Номер договора",
u.login as "Учетная запись",
uf_ip2string(r.ip_address) as "IP",
RADIUS_UPDATE as "Последний ACC_UPDATE"
from
users_radiusauth r
join
users u
on
r.user_id=u.id
join
abonents a
on
u.abonent_id=a.id
where
r.logged=1
order by
:1. Группировать по столбцу|choices[1^]Номер договора^[2^]Учетная запись^[3^]IP^[4^]ACC_UPDATE]$ :2. Группировать в порядке|choices[asc^]Возрастания^[desc^]Убывания]${code}
# h5. Отчёт по распределению абонентов из определенной группы по тарифам, так же выводится количество в настоящий момент заблокированных и не заблокированных абонентов.
{code}select

count(*) as "Количество",
t.name as "Тариф",

(select count(distinct atr.id) from (with recursive tree (id,tarif_id,is_folder) as (select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$') union all select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id,tarif_id from tree where is_folder=0) atr join abonents_block ab on atr.id=ab.abonent_id join tarif tt on atr.tarif_id=tt.id where tt.name=t.name) as "Заблокированные",

count(*)-(select count(distinct atr.id) from (with recursive tree (id,tarif_id,is_folder) as (select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$') union all select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id,tarif_id from tree where is_folder=0) atr join abonents_block ab on atr.id=ab.abonent_id join tarif tt on atr.tarif_id=tt.id where tt.name=t.name) "Работающие"

from
(with recursive tree (id,tarif_id,is_folder) as (
select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$')
union all
select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0)
select id,tarif_id from tree where is_folder=0) ab
join
tarif t on ab.tarif_id=t.id
group by
2
order by
1 desc{code}
# h5. Отчёт по поступившим платежам за выбранный период, с возможностью выбора папки для поиска и пользователя,который добавил платеж.
{code}
select AB.CONTRACT_NUMBER as "Номер договора",
AB.NAME as "ФИО",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
AU.USERNAME as "Добавлен",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
left join AUTH_USER AU on FO.OWNER_ID = AU.ID
where OP_TYPE = 2
and (FO.OP_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0))
and (AB.DELETED = 0
or AB.DELETED is null)

group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, AU.USERNAME, FO.SYSTEM_DATE
union
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (FO.OP_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0))
and (AB.DELETED = 0
or AB.DELETED is null))
{code}
# h5. Отчёт по абонентам, выводящий следующий данные:"ФИО, договор, текущий баланс,сумму списаний по разовым услугам за период, сумму абон.платы, сумму приходов за период".
{code}
select a.name as "ФИО",
a.contract_number as "Договор",
round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс",
(
select
sum(round(users_usluga.SUMM / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
from users_usluga
left join usluga on usluga.id=users_usluga.usluga_id
where users_usluga.ABONENT_ID=a.id
and usluga.SYSTEM_TYPE=0
and users_usluga.CREATE_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by users_usluga.abonent_id
) as "Разовые услуги",
(
select
sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) as "Абон. плата",
(
select sum(round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
from finance_operations fo1
where fo1.abonent_id=a.id
and fo1.op_type=2
and fo1.op_date between (':1 Дата|date$') and (':2 Дата|date$')
) as "Приходы"
from abonents a
left join finance_operations fo on fo.abonent_id=a.id
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where is_folder=0
group by a.name, a.TARIF_ID, (aa.ostatok+aa.debit-aa.credit) ,a.id,a.contract_number
{code}
# h5. Отчёт по абонентам с блокировкой по отрицательному балансу с балансом больше 2.
{code}
select
distinct
A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
usl.NAME as "Услуга",
round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join users_usluga uu on uu.ABONENT_ID=A.ID
left join usluga usl on usl.id=uu.USLUGA_ID
left join abonents_block ab on ab.abonent_id=a.id
where A.IS_FOLDER=0
and a.deleted=0
and ab.B_NEGBAL=1
and round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)>2
{code}
# h5. Отчёт по абонентам, у которых дата следующего списания больше, чем указанная.
{code}
select
distinct a.id as id,
a.name as "ФИО",
uu.next_date as "След. списание",
a.contract_number as "Договор"
from abonents a
left join users_usluga uu on uu.abonent_id=a.id
where uu.next_date>(':1 Дата|date$') and a.deleted=0
{code}
# h5. Отчет для поиска по реквизитам
Отчет ориентирован только на [реквизиты текстового типа|Реквизиты].
При использовании вместе с шаблоном отчета имя абонента будет преобразовано в гиперссылку на его карточку в БД
h6. Текст запроса
{code}
select
a.id,
a.name,
a.contract_number,
ua.name,
av.attribute_value
from
abonents a
join
attribute_values av on a.id=av.abonent_id and av.attribute_value like '%:Значение реквизита$%'
join
user_attributes ua on av.attribute_id=ua.attribute_id
{code}
h6. Шаблон отчета
{code}{% extends "form_list.html" %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}{% load mathfilters %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><h2>Заполните поля запроса</h2></legend>
{{ form }}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<div class="btn-group">
<a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
<i class="icon-print icon-white"></i>
Выгрузка
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><button type="submit" name="csv" value="True">CSV</button></li>
<li><button type="submit" name="dbf" value="True">DBF</button></li>
</ul>
</div>
<button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{inst.name}}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" style="text-align: center;" cellpadding="5">
<tr>
<th>ФИО/Название</th>
<th>Договор</th>
<th>Реквизит</th>
<th>Значение</th>
</tr>
{% for row in data %}
<tr align="left" cellpadding="10">
<td><a href="/admin/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
<td>{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td>{{ row.4 }}</td>
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}{code}
# h5. Отчет по выполненным задачам CRM с возможностью выбора периода, статуса, типа, ответственного за задачу. В конце выводится общее число задач.
{info}Время в фильтре проверят дату *создания* а не дату *закрытия* заявки {info}
{code}
select
HDSK.id,
HDSK.SUBJ as "Тема",
hs.NAME as "Статус",
a.name as "Назначено",
ht.NAME as "Тип"
from HDSK
left join HDSK_STATUS hs on hdsk.STATUS=hs.id
left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
left join abonents a on a.id=hdsk.PERFORMED_WHOM
left join auth_user au on au.USERNAME=a.name
where hdsk.IS_TASK=1
and hdsk.STATUS=':Статус|select[HdskStatus]$'
and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$'
and hdsk.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$'
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$')
union all
select
'"Количество"',
'',
'',
'',
count(*)
from HDSK
left join HDSK_STATUS hs on hdsk.STATUS=hs.id
left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
left join abonents a on a.id=hdsk.PERFORMED_WHOM
left join auth_user au on au.USERNAME=a.name
where hdsk.IS_TASK=1
and hdsk.STATUS=':Статус|select[HdskStatus]$'
and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$'
and hdsk.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$'
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$')
{code}
# h5. Отчёт по абонентам с балансом между минимальным и максимальным указанным.
{code}
select distinct
A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс",
A.name as "ФИО"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0
and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)<(':1.Максимум$')
and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)>(':2.Минимум$')
AND A.DELETED=0
and a.is_folder=0
order by A.NAME
{code}
# h5. Отчет выводящий объёмы трафика за вбыранный период (месяца целиком) по абоненту
{code}

select
year_number ||'-'|| month_number as "Период",
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "Входящий/Mб",
round(SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2) as "Исходящий/Mб"
from
traf_counters
where
Abonent_ID = ':3-Абонент|select[Abonents,is_folder=0]$'
and MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
order by
YEAR_NUMBER, MONTH_NUMBER

{code}
{info}При настройке отчета [для использования в личном кабинете|CarbonBilling:Отчёты в личном кабинете], условие с ID абонента должно быть таким:
{code}Abonent_ID = ':Abonent_ID$'{code}{info}
# h5. Отчёт по абонентам, подключенным по адресу. В поле "Должник" выводится информация "Должник" в том случае, если у абонента баланс меньше, чем минус 3 абонентские платы.
{code}
select distinct
usl.name as "Услуга",
(case
when a.A_HOME_NUMBER='' then cast('0' as NUMERIC)
else cast((a.A_HOME_NUMBER) as NUMERIC) end) as "Квартира",
(
select
sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) as "Абон. плата",
(
select
sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) as "3 АБ",
(case when (round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)) < (
select
sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) then 'Должник' else '' end) as "Должник",
h.city || ', ' || h.STREET || ', д. ' || h.S_NUMBER as "Адрес",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join homes h on h.id=a.home_id
left join abonents_block ab on ab.abonent_id=a.id
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
left join usluga usl on usl.id=tus.usluga_id
where A.IS_FOLDER=0
and H.id=':Дом|select[homes]$'
AND A.DELETED=0
order by 2
{code}
# h5. Отчёт по сумме приходов абонентов, подключенных к оператору связи за период. Вторым столбцом выводится 10% от суммы приходов
{code}
select
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма приходов",
(cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1))*0.1 as "10%"
from finance_operations fo
left join abonents a on a.id=fo.abonent_id
where a.OPERATOR_ID = ':Оператор|select[Abonents,category_id=2]$'
and op_type=2
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
group by a.operator_id
{code}
# h5. Детализация звонков по абоненту
{info}НДС посчитан только для юридических лиц{info}
{code}
select distinct
vl.src as "Звонок с номера",
vl.dst as "Назначение звонка",
substring(cast(vl.s_time as varchar(32)) from 1 for 19) as "Начало звонка",
cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) as "Время разговора",
coalesce(vd.name,'') as "Название направления",
coalesce(vl.step_price/10000000000.00,0) as "Стоимость направления",
cast(coalesce(vl.bill_sum/10000000000.00,'') as float) as "Стоимость"
from
voip_log vl
left join voip_direction vd on vl.direction_id=vd.id
where
vl.s_time between ':1-Начало|date$' and ':2-Конец|date$' and
vl.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
union all
select
' ',
' ',
'Итого:',
sum(duration),
' ',
' ',
sum(cost)
from
(
select distinct
vl.src as source,
vl.dst as dest,
substring(cast(vl.s_time as varchar(32)) from 1 for 19) as stime,
cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) as duration,
coalesce(vl.step_price/10000000000.00,0) as directioncost,
round(cast(coalesce(vl.bill_sum/10000000000.00,'') as float),2) as cost
from
voip_log vl
where
vl.s_time between ':1-Начало|date$' and ':2-Конец|date$' and
vl.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
)
order by 3
{code}
Скрипт для получения журнала звонков по всем [архивным базам|CarbonBilling:Настройка периода хранения исторических данных в базе]
{code}#!/bin/bash

>export.csv
>export_raw.csv

# Текущая дата
curdate=$(date +'%Y-%m-%d %H:%M:%S')

# Начало периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00'
period_start='2019-01-01 00:00:00'
# Конец периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00'
period_end='2019-10-01 00:00:00'

# Если начало или конец периода не заданы - будет выбрана текущая дата
period_start_fixed=${period_start:-$curdate}
period_end_fixed=${period_end:-$curdate}

# ID абонента
abonent_id=10507

# Заголовок csv для удобства работы в электронных таблицах
echo "Звонок с номера;Назначение звонка;Название направления;Начало звонка;Окончание звонка;Время разговора;Стоимость направления;Стоимость" > export.csv

# Получаем звонки из архивных баз
find /var/db/billing/ -iname voip_log.fdb | sort | while read file; do
#echo $file >> export_raw.csv # DEBUG
sqlexec $file "set heading off; select distinct
vl.src || ';' ||
vl.dst || ';' ||
'direction:' || coalesce(vl.direction_id,0) || ';' ||
substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' ||
substring(cast(vl.e_time as varchar(32)) from 1 for 19) || ';' ||
cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) || ';' ||
coalesce(vl.step_price/10000000000.00,0) || ';' ||
cast(coalesce(vl.bill_sum/10000000000.00,'') as float)
from
voip_log vl
where
vl.s_time between '$period_start' and '$period_end_fixed'
and vl.abonent_id=$abonent_id " | sed 's/ *//g; /^$/d' >> export_raw.csv
done

# Получаем звонки из основной БД
#echo '/var/db/billing.gdb' >> export_raw.csv # DEBUG
sqlexec /var/db/billing.gdb "set heading off; select distinct
vl.src || ';' ||
vl.dst || ';' ||
'direction:' || vl.direction_id || ';' ||
substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' ||
cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) || ';' ||
coalesce(vl.step_price/10000000000.00,0) || ';' ||
cast(coalesce(vl.bill_sum/10000000000.00,'') as float)
from
voip_log vl
where
vl.s_time between '$period_start_fixed' and '$period_end_fixed'
and vl.abonent_id=$abonent_id" | sed 's/ *//g; /^$/d' >> export_raw.csv

# Получаем из БД названия направлений и отфильтровываем дебаг
cat export_raw.csv | grep -vE '^<null>|voip_log.fdb$' | cut -d';' -f 3 | sort | uniq | while IFS=':' read field_name direction_id; do
direction_name=$(sqlexec "set heading off; select vd.name from voip_direction vd where vd.id='$direction_id'" | sed 's/ *//g; /^$/d')
sed "s/direction:${direction_id}/${direction_name}/g" -i export_raw.csv
done

# Сортируем на случаей если звонок попал в несколько архивных баз и пишем в выгрузку
cat export_raw.csv | grep -v '^<null>' | sort | uniq >> export.csv{code}
# h5. Отчёт по абонентам, у которых услуги подключены не 5 числа. Данный отчёт подойдет для отслеживания абонентов со сдвигом даты списания услуг.
{code}
select
a.contract_number as "Договор",
a.name as "ФИО",
uu.usluga_id as "ID услуги",
u.name as "Имя услуги"
from users_usluga uu
left join usluga u on u.id=uu.usluga_id
left join abonents a on a.id=uu.abonent_id
where extract(day from cast(uu.create_date as date))!=5
and u.USLUGA_ABON_TYPE_ID=4
and uu.deleted=0
and uu.usluga_id!=-170000
{code}
# h5. Отчёт по наличным/безналичным приходам с группировкой по тарифам абонентов за указанный период.
{code}
select
t.name as "Тариф",
(
select
sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
FROM TARIF t1
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE t1.id = t.ID
) as "Абон. плата",
(
select distinct
count(*)
from abonents a1
left join tarif t2 on t2.id=a1.tarif_id
left join finance_operations fo1 on fo1.abonent_id=a1.id
where t2.id=t.id
and fo1.OP_TYPE = 2
and fo1.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
) as "Количество",
(
select
sum(round(fo2.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
from finance_operations fo2
left join abonents a on a.id=fo2.abonent_id
where a.tarif_id=t.id
and fo2.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and fo2.is_cash=1
and fo2.OP_TYPE = 2
)as "Наличные",
(
select
sum(round(fo3.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
from finance_operations fo3
left join abonents a on a.id=fo3.abonent_id
where a.tarif_id=t.id
and fo3.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and fo3.is_cash=0
and fo3.OP_TYPE = 2
)as "Безналичные",
(
select
sum(round(fo.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
from finance_operations fo
left join abonents a on a.id=fo.abonent_id
where a.tarif_id=t.id
and fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and fo.OP_TYPE = 2
)as "Сумма"
from tarif t
where t.ARCHIVED=0
union all
select first 1 '"Итого"',
null,
null,
(select sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and fo.is_cash=1
and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
and (AB.DELETED = 0
or AB.DELETED is null))),
(select sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and fo.is_cash=0
and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
and (AB.DELETED = 0
or AB.DELETED is null))),
(select sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
and (AB.DELETED = 0
or AB.DELETED is null)))
from tarif t
{code}
# h5. Отчёт для анализа настройки агентской схемы VoIP, отражает текущую настройку транков в операторских услугах телефонии
{code}select
...
order by :1-Сортировка по:|choices[2^]Наименованию^[6^]Транкам^[5^]Типу трафика]$
{code}
# h5. Отчёт по неплательщикам за месяц
# h5. Отчёт по исходящему телефонному трафику за выбранный период
Отчёт выводит сверку по потреблению услуги телефонного трафика с разделением по категориям и абонентам.
{code}
select distinct
vc.name || ' (' || vc.id || ')' as "Категория",
a.contract_number as "№ Договора абонента",
sum(vl.bill_sum)/10000000000.00 as "Сумма",
cast(sum(vl.bill_sec_round) as numeric(18,2))/60 as "Длительность",
a.name as "Название/ФИО"
a.id, from
voip_log vl
join usluga u on vl.usluga_id=u.id
join usluga_voip uv on u.id=uv.usluga_id
join category_directions cd on uv.voipcategory_id=cd.category_id
join voip_direction vd on vd.id=cd.direction_id and vd.id=vl.direction_id
join voip_category vc on cd.category_id=vc.id
join abonents a on vl.abonent_id=a.id
a.name, where
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)
vl.bill_sum>0 and
vl.s_time between ':Начало|date$' and ':Конец|date$' and
vl.V_TYPE_ID = 2 and
vl.error_code is null
group by 1, a.contract_number, a.name
order by a.contract_number
{code}
# h5. Отчёт по объёмам потребленного трафика посуточно и остатка/превышения ежедневных и ежемесячных лимитов (если они есть) за указанный период
Для работы отчета необходимо включить опцию "*Сохранять движения всего трафика*" в [настройках оператора связи|CarbonBilling:Глобальные настройки биллинга и оператора]
# h5. История выдачи телефонного номера для voip.
{code}/*
1) Получаем абонента, дни когда был расход и по каким услугам - select from abonents join (select distinct from arch_account_stack join usluga)
2) Присоединяем данные по расходу трафика (вх/исх отдельно) - left join down left join upl
2.1) Берем данные из arch_account_stack, группируя по услугам и датам
2.2) Считаем сумму по трафику за сутки, попутно вычисляя остаток/превышение суточного лимита (если есть)
3) Считаем итого за месяц по каждой услуге из тех же самых данных - union all select...
3.1) в присоединенных данных по трафику добавляем колонку с месячным лимитом, с помощью этого вычисляем остаток/превышение за месяц
*/ select
select abonent_id,time_changed,a.name
from
users_history uh
join
abonents a
dates.aasdate "Период", on uh.abonent_id = a.id
where
phone = (select id from PHONE_PULL_CHACHE where PHONE=':Введите номер$')
{code}
# h5. История использования логина.
{code}
select
uf_ip2string(ip) as IP,abonent_id,time_changed,a.name
from
users_history uh
join
abonents a
dates.aasusluga "Услуга", on uh.abonent_id = a.id
coalesce(cast(round(down.mbsum) as varchar(32)),'') "Входящий МБ",
where
down.limitleft as "Лимит ВХ", LOGIN_UPPER like upper(':Логин$')
coalesce(cast(round(upl.mbsum) as varchar(32)),'') "Исходящий МБ",
upl.limitleft as "Лимит ИСХ"
from
abonents a
join
(select distinct
aaasss.abonent_id as abon,
aasu.name as aasusluga,
cast(cast(aaasss.CHANGE_BALANCE_TIME as date) as varchar(32)) as aasdate
from
arch_account_stack aaasss
join usluga aasu on aaasss.usluga_id=aasu.id
where
aaasss.abonent_id= ':Abonent_ID$'
and aaasss.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59') dates on dates.abon=a.id
left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
coalesce(cast(u.max_mb_in_d as varchar(32)) || 'МБ','') as lim,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null,
'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=1
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d) down on dates.aasdate=down.den and dates.aasusluga=down.usl
left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
coalesce(cast(u.max_mb_out_d as varchar(32)) || 'МБ','') as lim,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_out_d and u.max_mb_out_d is not null,
'Остаток ' || cast(round(u.max_mb_out_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_out_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=2
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_out_d) upl on dates.aasdate=upl.den and dates.aasusluga=upl.usl
where
a.id= ':Abonent_ID$'
and (upl.mbsum is not null or down.mbsum is not null)
union all
select
extract(year from cast(dates.aasdate as timestamp)) ||'-'|| extract(month from cast(dates.aasdate as timestamp)),
dates.aasusluga,
coalesce(cast(round(sum(down.mbsum)) as varchar(32)),''),
coalesce(
iif(
sum(down.mbsum)<=down.limm and down.limm is not null,
'Остаток ' || cast(round(down.limm-sum(down.mbsum)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(down.mbsum)-down.limm) as varchar(32)) || 'МБ'
),
''
),
coalesce(cast(round(sum(upl.mbsum)) as varchar(32)),''),
{code}
coalesce(
iif(
sum(upl.mbsum)<=upl.limm and upl.limm is not null,
'Остаток ' || cast(round(upl.limm-sum(upl.mbsum)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(upl.mbsum)-upl.limm) as varchar(32)) || 'МБ'
),
''
)
from
abonents a
join
(select distinct
aaasss.abonent_id as abon,
aasu.name as aasusluga,
cast(cast(aaasss.CHANGE_BALANCE_TIME as date) as varchar(32)) as aasdate
from
arch_account_stack aaasss
join usluga aasu on aaasss.usluga_id=aasu.id
where
aaasss.abonent_id= ':Abonent_ID$'
and aaasss.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59') dates on dates.abon=a.id

left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
u.max_mb_in_d as limd,
u.max_mb_in_m as limm,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null,
'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=1
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d,max_mb_in_m) down on dates.aasdate=down.den and dates.aasusluga=down.usl
left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
u.max_mb_in_d as limd,
u.max_mb_in_m as limm,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null,
'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=2
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d,max_mb_in_m) upl on dates.aasdate=upl.den and dates.aasusluga=upl.usl
where
a.id= ':Abonent_ID$'
and (upl.mbsum is not null or down.mbsum is not null)
group by
1,2,down.limm,upl.limm
order by
1,2{code}
Шаблон для [размещения в личном кабинете|CarbonBilling:Отчёты в личном кабинете]
{code}<form class="form-inline" method="post" action="" role="form">
<?$this->module_token()?>
<div class="form-group">
<legend><h2>Трафик за выбранный период</h2></legend>
<label for="1-date_start">C:</label><input class="datepicker" id="1-date_start" name="1-date_start|date" type="text" />
<label for="2-date_end">По:</label><input class="datepicker" id="2-date_end" name="2-date_end|date" type="text" />
</div>
<br />
<button type="submit" class="btn btn-success">Выполнить</button>
</form>{code}
# h5. Отчёт по звонкам учтенным для взаиморасчетов между операторами
{code}select
...
) calls
group by
calls.service{code}
# h5. Список абонентов, у которых не заведён дом или дом заведён некорректно
Отчет {code}
# h5. Цены на направления за период
{code}select
a.id,
a.name,
a.contract_number,
t.name,
p.name,
a.home_id,
iif(a.home_id is not null, 'Улица: ' || coalesce(h.street,''),'Не выбран дом'),
iif(a.home_id is not null and (h.city is null or h.city=''), 'Не указан город', 'Не выбран дом')
select
vd.mask as direction_mask,
vd.id as direction_id,
vd.name as direction_name,
u.id as usluga_id,
uv.active_from_date,
round(uv.price/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 4) as price
from usluga u
JOIN usluga_voip uv on u.id=uv.usluga_id
join CATEGORY_DIRECTIONS CD ON CD.CATEGORY_ID=uv.VOIPCATEGORY_ID
join VOIP_CATEGORY VC ON CD.CATEGORY_ID=VC.ID
join voip_direction vd on vd.id = cd.direction_id
where (uv.active_from_date between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59')
and vd.mask=:Маска направления$
{code}
# h5. Детализация звонков по абоненту
{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
abonents a voip_log vl
join tarif t on a.tarif_id=t.id
join abonents p on a.parent_id=p.id
left join homes h on a.home_id=h.id
left join voip_direction vd on vl.direction_id=vd.id
where
(a.home_id is null or h.city='' or h.city is null)
vl.s_time between ':1-Начало|date$' and ':2-Конец|date$' and
vl.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
union all
select
' ',
' ',
and a.is_folder=0 'Итого:',
and a.category_id<2 sum(duration),
' ',
' ',
and a.deleted=0 sum(cost)
from
and a.id>0 (
and a.parent_id not in (244,1313,16723) 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]$'
order group by
7,5{code} u.id,
Шаблон
{code}{% extends "form_list.html" %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params"> u.name
{% csrf_token %}{% load mathfilters %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><h2>Заполните поля запроса</h2></legend>
{{ form }}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<div class="btn-group">
<a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
<i class="icon-print icon-white"></i>
Выгрузка
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><button type="submit" name="csv" value="True">CSV</button></li>
<li><button type="submit" name="dbf" value="True">DBF</button></li>
</ul>
</div>
<button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{inst.name}}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}
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,
<table id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="2"> cast(s_time-extract(day from s_time)+1 as date) per_first_date
from
<tr> voip_log
<th>ФИО</th>
<th>Договор</th>
<th>Тариф</th>
<th>Группа</th>
<th>Улица</th>
<th>Проблема</th>
</tr>
{% for row in data %}
<tr>
<td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
<td>{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td>{{ row.4 }}</td>
<td>{% if row.5 %}<a href="/admin/Abonents/Abonents/{{ row.5 }}/" target="_blank">{{ row.6 }}{% else %} --- {% endif %}</a></td>
<td>{{ row.7 }}</td>
</tr>
{% endfor %}
) calls
group by
calls.per_first_date
order by
1
{code}

h2. Интернет трафик
# h5. Отчёт по скачанному трафику за период.
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
sum(round(tc.SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2)) as "Объем исх.",
sum(round(tc.SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2)) as "Объем вх."
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number between (':1 Месяц$') and (':2 Месяц$')
and tc.year_number=(':Год$')
and tc.currentt=1
group by users.login, tarif.name
{code}
# h5. Отчёт по скачанному трафику за период (2 вариант).
{code:lang=sql}
select
abonent_id as "ИД абонента",
year_number ||'-'|| month_number as "Период",
(SUM_BYTE_IN_M /1000000000.00) as "Входящий/гиг",
(SUM_BYTE_OUT_M /1000000000.00) as "Исходящий/гиг"
from
traf_counters
where
MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
union all
select
'Итого',
'',
sum(SUM_BYTE_IN_M /1000000000.00),
sum(SUM_BYTE_OUT_M /1000000000.00)
from
traf_counters
where
MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
{code}
# h5. Потребленный трафик суммарно по всем абонентам с возможностью выбора периода
{code}SELECT FIRST 1
(SELECT cast(sum(v) AS numeric(18,2)) AS "Входящий"
FROM counters
WHERE YEAR_NUMBER = (':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
AND MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
</table> AND UNIT_ID = 2
{% else %} AND V_TYPE_ID = 1),
<h3>Ничего не найдено.</h3>
(SELECT cast(sum(v) AS numeric(18,2)) AS "Исходящий"
FROM counters
WHERE YEAR_NUMBER = (':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
AND MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
{% endif %} AND UNIT_ID = 2
{% endif %} AND V_TYPE_ID = 2)
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}{code}
FROM counters
{code}
# h5. Отчет по входящему интернет-трафику юридических лиц за выбранный период месяц
Отчет расчитан на использование тарифов типа лестница с некоторым объёмом трафика включенного в абонентскую плату, по исчерпании которого начисляется помегабайтная оплата.
...
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}{code}
# h5. Цены на направления за период
{code}
# h5. Отчет выводящий объёмы трафика за вбыранный период (месяца целиком) по абоненту
{code}

select
vd.mask as direction_mask,
vd.id as direction_id,
vd.name as direction_name,
u.id as usluga_id,
year_number ||'-'|| month_number as "Период",
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "Входящий/Mб",
round(SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2) as "Исходящий/Mб"
uv.active_from_date, from
round(uv.price/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 4) as price
from usluga u
JOIN usluga_voip uv on u.id=uv.usluga_id
join CATEGORY_DIRECTIONS CD ON CD.CATEGORY_ID=uv.VOIPCATEGORY_ID
join VOIP_CATEGORY VC ON CD.CATEGORY_ID=VC.ID
join voip_direction vd on vd.id = cd.direction_id
where (uv.active_from_date between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59')
and vd.mask=:Маска направления$
traf_counters
where
Abonent_ID = ':3-Абонент|select[Abonents,is_folder=0]$'
and MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
and YEAR_NUMBER between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
order by
YEAR_NUMBER, MONTH_NUMBER

{code}
# h5. Отчёт по сумме списаний у абонентов. Списания суммируются из поля "Расход/предоплата" в финансовой информации абонента.
{info}При настройке отчета [для использования в личном кабинете|CarbonBilling:Отчёты в личном кабинете], условие с ID абонента должно быть таким:
{code}Abonent_ID = ':Abonent_ID$'{code}{info}
# h5. Отчёт по объёмам потребленного трафика посуточно и остатка/превышения ежедневных и ежемесячных лимитов (если они есть) за указанный период
Для работы отчета необходимо включить опцию "*Сохранять движения всего трафика*" в [настройках оператора связи|CarbonBilling:Глобальные настройки биллинга и оператора]
{code}/*
select first 1
'Дата начала периода ' || ':1-Начало|date$' as "Договор",
'Дата окончания периода ' || ':2-Конец|date$ 23:59:59' as "ФИО",
null as "Сумма"
from abonents
1) Получаем абонента, дни когда был расход и по каким услугам - select from abonents join (select distinct from arch_account_stack join usluga)
2) Присоединяем данные по расходу трафика (вх/исх отдельно) - left join down left join upl
2.1) Берем данные из arch_account_stack, группируя по услугам и датам
2.2) Считаем сумму по трафику за сутки, попутно вычисляя остаток/превышение суточного лимита (если есть)
3) Считаем итого за месяц по каждой услуге из тех же самых данных - union all select...
3.1) в присоединенных данных по трафику добавляем колонку с месячным лимитом, с помощью этого вычисляем остаток/превышение за месяц
*/
select
dates.aasdate "Период",
dates.aasusluga "Услуга",
coalesce(cast(round(down.mbsum) as varchar(32)),'') "Входящий МБ",
down.limitleft as "Лимит ВХ",
coalesce(cast(round(upl.mbsum) as varchar(32)),'') "Исходящий МБ",
upl.limitleft as "Лимит ИСХ"
from
abonents a
join
(select distinct
aaasss.abonent_id as abon,
aasu.name as aasusluga,
cast(cast(aaasss.CHANGE_BALANCE_TIME as date) as varchar(32)) as aasdate
from
arch_account_stack aaasss
join usluga aasu on aaasss.usluga_id=aasu.id
where
aaasss.abonent_id= ':Abonent_ID$'
and aaasss.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59') dates on dates.abon=a.id
left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
coalesce(cast(u.max_mb_in_d as varchar(32)) || 'МБ','') as lim,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null,
'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=1
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d) down on dates.aasdate=down.den and dates.aasusluga=down.usl
left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
coalesce(cast(u.max_mb_out_d as varchar(32)) || 'МБ','') as lim,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_out_d and u.max_mb_out_d is not null,
'Остаток ' || cast(round(u.max_mb_out_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_out_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=2
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_out_d) upl on dates.aasdate=upl.den and dates.aasusluga=upl.usl
where
a.id= ':Abonent_ID$'
and (upl.mbsum is not null or down.mbsum is not null)
union all
select 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
extract(year from cast(dates.aasdate as timestamp)) ||'-'|| extract(month from cast(dates.aasdate as timestamp)),
dates.aasusluga,
coalesce(cast(round(sum(down.mbsum)) as varchar(32)),''),
coalesce(
iif(
sum(down.mbsum)<=down.limm and down.limm is not null,
'Остаток ' || cast(round(down.limm-sum(down.mbsum)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(down.mbsum)-down.limm) as varchar(32)) || 'МБ'
),
''
),
coalesce(cast(round(sum(upl.mbsum)) as varchar(32)),''),

select '"Итого"',
coalesce(
iif(
sum(upl.mbsum)<=upl.limm and upl.limm is not null,
'Остаток ' || cast(round(upl.limm-sum(upl.mbsum)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(upl.mbsum)-upl.limm) as varchar(32)) || 'МБ'
),
''
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
from abonents a
join
left join arch_account_stack aas on aas.ABONENT_ID = A.ID (select distinct
aaasss.abonent_id as abon,
aasu.name as aasusluga,
cast(cast(aaasss.CHANGE_BALANCE_TIME as date) as varchar(32)) as aasdate
from
arch_account_stack aaasss
join usluga aasu on aaasss.usluga_id=aasu.id
where 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'))
aaasss.abonent_id= ':Abonent_ID$'
and aaasss.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59') dates on dates.abon=a.id

left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
u.max_mb_in_d as limd,
u.max_mb_in_m as limm,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null,
'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=1
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d,max_mb_in_m) down on dates.aasdate=down.den and dates.aasusluga=down.usl
left join
(select
aas.abonent_id as abon,
cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as den,
u.name as usl,
u.max_mb_in_d as limd,
u.max_mb_in_m as limm,
sum(aas.v) as mbsum,
coalesce(
iif(
sum(aas.v)<=u.max_mb_in_d and u.max_mb_in_d is not null,
'Остаток ' || cast(round(u.max_mb_in_d-sum(aas.v)) as varchar(32)) || 'МБ',
'Превышение ' || cast(round(sum(aas.v)-u.max_mb_in_d) as varchar(32)) || 'МБ'
),
''
) as limitleft
from
arch_account_stack aas
join usluga u on aas.usluga_id=u.id
where
aas.Abonent_ID = ':Abonent_ID$'
and aas.ttype=2
and aas.credit=0
and v_type_id=2
and aas.CHANGE_BALANCE_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
group by
aas.abonent_id,u.name,cast(aas.CHANGE_BALANCE_TIME as date),max_mb_in_d,max_mb_in_m) upl on dates.aasdate=upl.den and dates.aasusluga=upl.usl
where
a.id= ':Abonent_ID$'
and (upl.mbsum is not null or down.mbsum is not null)
group by
1,2,down.limm,upl.limm
order by
1,2{code}
Шаблон для [размещения в личном кабинете|CarbonBilling:Отчёты в личном кабинете]
{code}<form class="form-inline" method="post" action="" role="form">
<?$this->module_token()?>
<div class="form-group">
<legend><h2>Трафик за выбранный период</h2></legend>
<label for="1-date_start">C:</label><input class="datepicker" id="1-date_start" name="1-date_start|date" type="text" />
<label for="2-date_end">По:</label><input class="datepicker" id="2-date_end" name="2-date_end|date" type="text" />
</div>
<br />
<button type="submit" class="btn btn-success">Выполнить</button>
</form>
{code}
# h5. Отчёт по задолженности абонента за месяц. Выводит баланс на 1 число месяца, платежи за месяц и расход. Необходимо выполнять отчёт с 1 по 31 число уже завершенного месяца.
# h5. Потребленный трафик за год с разбивкой по месяцам и возможностью выбора группы абонентов.
{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
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. Отчёт покажет количество аткуальных направлений по услугам подключенным абоненту, и их общее количество
# h5. Отчет, выводящий суммарный объем входящего и исходящего трафика с начала года по выбранный квартал
{code}
select
count(distinct cd.id), -- количество направлений в услуге
count(distinct uu.id), -- скольким пользователям услуга подключена
u.id, -- ID услуги
u.name
from
usluga u
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
usluga_voip uv -- в таблице хранятся актуальные цены категорий
on u.id=uv.usluga_id
(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
join
category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим"
on uv.voipcategory_id=cd.category_id
join where
users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена
on u.id=uu.usluga_id and uu.deleted=0
group by
u.id,
u.name
order by
1
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. Отчёт покажет количество аткуальных направлений по услугам
# h5. Отчет по объемам потребленного трафика абонентом.
Будет работать лишь после включения опции *["Сохранять движения всего трафика"|https://docs.carbonsoft.ru/pages/viewpage.action?pageId=63242421#Глобальныенастройкибиллингаиоператора-Глобальныенастройкиоператора]*
{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
select t1.trafab as "ID абонента",
t1.aname1 as "ФИО",
t1.ttime as "Дата",
t1.uname as "Услуга",
t1.inn as "Входящий",
t2.out as "Исходящий"

union from

select
count(distinct cd.id), -- количество направлений в услугах (суммарно)
'',
'Итого'
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 --Из услуги берём название
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
left join abonents a on a.id=aas.abonent_id
where where
uu.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
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

order by
1
group by 1,2,3,4,aas.V_TYPE_ID)t2 on t2.ttime=t1.ttime
{code}
# h5. Отчёт по абонентам заключивших договор за период
Отчёт покажет абонентов заключивших договор в выбранном периоде, в воде будут номер договора, ФИО, дата договора, текущий тариф и текущий баланс.

h2. CRM и Helpdesk
# h5. Отчет по выполненным задачам CRM с возможностью выбора периода, статуса, типа, ответственного за задачу. В конце выводится общее число задач.
{info}Время в фильтре проверят дату *создания* а не дату *закрытия* заявки {info}
{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
HDSK.id,
HDSK.SUBJ as "Тема",
hs.NAME as "Статус",
a.name as "ФИО", "Назначено",
a.contract_number as "Договор",
ao.descr as "Описание",
ao.OP_TIME as "Дата"
from audit_operations ao
ht.NAME as "Тип"
from HDSK
left join HDSK_STATUS hs on hdsk.STATUS=hs.id
left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
left join abonents a on a.id=ao.abonent_id a.id=hdsk.PERFORMED_WHOM
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'
left join auth_user au on au.USERNAME=a.name
where hdsk.IS_TASK=1
and hdsk.STATUS=':Статус|select[HdskStatus]$'
and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$'
and hdsk.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$'
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$')
union all
select
'"Количество"',
'',
'',
'',
count(*)
from HDSK
left join HDSK_STATUS hs on hdsk.STATUS=hs.id
left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
left join abonents a on a.id=hdsk.PERFORMED_WHOM
left join auth_user au on au.USERNAME=a.name
where hdsk.IS_TASK=1
and hdsk.STATUS=':Статус|select[HdskStatus]$'
and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$'
and hdsk.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$'
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$')
{code}
# h5. Вывод абонентов у кого подключен автоплатеж
{code}
select
a.id,
a.contract_number as " номер договора",
a.name as "ФИО",
iif(a.autopay_pay_log_id is not null, 'Включен', ' ') as "автоплатеж"
from abonents a
where
a.is_folder=0
and a.deleted=0
and a.parent_id not in (2,4,244)
and a.autopay_pay_log_id is not null
{code}
# h5. Анализ проводок по абонентам и выявление большого количества записей у абонентов(более 500 записей в месяц, это значение можно менять для отсортировки результатов) в таблице ARCH_ACCOUNT_STACK.
{code}
select
ABONENT_ID,
extract(year from CHANGE_BALANCE_TIME),
extract(month from CHANGE_BALANCE_TIME),
count(*)
from ARCH_ACCOUNT_STACK
group by 1,2,3
having count(*)>500
{code}
# h5. Список задач в HelpDesk и CRM для выгрузки и обработки средствами Excel
{code}
...
and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$ 23:59:59')
{code}
# h5. Потребленный трафик за год с разбивкой по месяцам и возможностью выбора группы абонентов.

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

# h5. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number)
{code:lang=sql}
{% extends "form_list.html" %}
{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>

{% endfor %}

</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>№</th>
{% for field in field_desc %}
<th>{{ field|get_zero }}</th>
{% endfor %}
</tr>
{% for row in data %}
<tr>
<td>{{ forloop.counter }}</td>
{% for cell in row %}
<td>{{ cell }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>
{% endblock %}
{code}
# h5. Отчёт по абонентам со статусом, балансом, датой последнего платежа и суммой последнего платежа.
select {code}
distinct c.month_number as "месяц",
inp.sum_in as "Входящий",
outp.sum_out as "Исходящий"
from counters c
select
ab.name as "ФИО",
ab.contract_number as "Договор",
s.name as "Статус",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID and FO.OP_TYPE=2) as "Дата посл.",
(select first 1 round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) from finance_operations FO1 where FO1.ABONENT_ID = AB.ID and FO1.OP_TYPE=2 order by fo1.op_date desc) as "Платеж",
(aa.debit+aa.Ostatok-aa.credit)/10000000000.00 as "Баланс"
from abonents ab
left join objects_status os on
os.object_id=ab.id
left join status s on
s.id = os.status
left join admin_accounts aa on
aa.id = ab.account_id
where extract (DAY from ab.activate_date) >0
{code}
# h5. Отчёт по RADIUS-сессиям выбранного абонента: начало, конец, длительность, причина завершения (если завершена), IP.
{code}select
START_TIME "Начало",
END_TIME "Конец",
case
when coalesce(end_reason,'')='' then 'Не было Act-Session-Stop'
when datediff(hour from start_time to end_TIME)>=1 then datediff(hour from start_time to end_TIME) || ' часов'
when datediff(minute from start_time to end_TIME)<1 then 'Меньше минуты'
when datediff(hour from start_time to end_TIME)<1 then datediff(minute from start_time to end_TIME) || ' минут'
end as "Длительность",
coalesce(END_REASON,'Отсутствует') as "Причина",
uf_ip2string(ip) as "IP"
from
radius_sessions
where
abonent_id=':Абонент|select[Abonents]$'
order by
start_time desc{code}
# h5. Отчёт для ФСБ в формате Оператор связи IP-адрес Логин Дата подключения Дата отключения Адрес подключения Абонент Дата рождения Документ (ИНН) Адрес регистрации (юридический) Контактное лицо Контактный номер Дополительня информаци
{code}
select '' from abonents
UNION
select distinct
'"' || (select name from abonents where abonents.id=a.operator_id) || '";"' ||
uf_ip2string(u.ip) || '";"' ||
u.login || '";"' ||
a.activate_date || '";"' ||
coalesce(a.disabled_date,'') || '";"' ||
coalesce( h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER,'') || '";"' ||
' ' || '";"' ||
a.name || '";"' ||
coalesce(DR.ATTRIBUTE_VALUE,' ') || '";"' ||
coalesce(INN.ATTRIBUTE_VALUE,' ') || '";"' ||
coalesce(UADR.ATTRIBUTE_VALUE,' ') || '";"' ||
coalesce(KLICO.ATTRIBUTE_VALUE,' ') || '";"' ||
a.sms || '";"' ||
' ' || '"'
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join ATTRIBUTE_VALUES DR on DR.ABONENT_ID=A.ID and DR.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES KLICO on KLICO.ABONENT_ID=A.ID and KLICO.ATTRIBUTE_ID=32
left join ATTRIBUTE_VALUES INN on INN.ABONENT_ID=A.ID and INN.ATTRIBUTE_ID=4
{code}
# h5. Отчёт по абонентам, у которых количество услуг больше 1, показывающий количество подключенных услуг и количество активных услуг
{code}
select abonent_id,
count(*) as "Кол-во услуг",
(select count(*) from users_usluga uu1 where uu1.deleted=0 and uu1.enabled=1 and uu1.abonent_id=users_usluga.abonent_id having count(*)>1) as "Кол-во акт. услуг"
from users_usluga where deleted=0 group by abonent_id having count(*)>1
{code}
# h5. Отчёт по распределению абонентов из определенной группы по тарифам, так же выводится количество в настоящий момент заблокированных и не заблокированных абонентов.
{code}
select

left join (select cast(sum(v) AS numeric(18,2)) sum_in,
month_number mont
from counters
count(*) as "Количество",
t.name as "Тариф",

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
(select count(distinct atr.id) from (with recursive tree (id,tarif_id,is_folder) as (select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$') union all select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id,tarif_id from tree where is_folder=0) atr join abonents_block ab on atr.id=ab.abonent_id join tarif tt on atr.tarif_id=tt.id where tt.name=t.name) as "Заблокированные",

WHERE YEAR_NUMBER = (':Год|choices[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
count(*)-(select count(distinct atr.id) from (with recursive tree (id,tarif_id,is_folder) as (select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$') union all select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id,tarif_id from tree where is_folder=0) atr join abonents_block ab on atr.id=ab.abonent_id join tarif tt on atr.tarif_id=tt.id where tt.name=t.name) "Работающие"

from
(with recursive tree (id,tarif_id,is_folder) as (
select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$')
AND UNIT_ID = 2 union all
select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0)
select id,tarif_id from tree where is_folder=0) ab
join
tarif t on ab.tarif_id=t.id
group by
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]$')
order by
1 desc
{code}
# h5. Отчёт по абонентам с балансом между минимальным и максимальным указанным.
{code}
select distinct
A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс",
A.name as "ФИО"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0
and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)<(':1.Максимум$')
and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)>(':2.Минимум$')
AND A.DELETED=0
and a.is_folder=0
order by A.NAME
{code}
# h5. Отчёт по абонентам, подключенным по адресу. В поле "Должник" выводится информация "Должник" в том случае, если у абонента баланс меньше, чем минус 3 абонентские платы.
{code}
select distinct
usl.name as "Услуга",
(case
when a.A_HOME_NUMBER='' then cast('0' as NUMERIC)
else cast((a.A_HOME_NUMBER) as NUMERIC) end) as "Квартира",
(
select
sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) as "Абон. плата",
(
select
sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) as "3 АБ",
(case when (round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)) < (
select
sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2))
FROM TARIF t
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
WHERE a.TARIF_ID = t.ID
) then 'Должник' else '' end) as "Должник",
h.city || ', ' || h.STREET || ', д. ' || h.S_NUMBER as "Адрес",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join homes h on h.id=a.home_id
left join abonents_block ab on ab.abonent_id=a.id
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
left join usluga usl on usl.id=tus.usluga_id
where A.IS_FOLDER=0
and H.id=':Дом|select[homes]$'
AND A.DELETED=0
order by 2
{code}
# h5. Отчёт по сумме приходов абонентов, подключенных к оператору связи за период. Вторым столбцом выводится 10% от суммы приходов
{code}
select
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма приходов",
(cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1))*0.1 as "10%"
from finance_operations fo
left join abonents a on a.id=fo.abonent_id
where a.OPERATOR_ID = ':Оператор|select[Abonents,category_id=2]$'
and op_type=2
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
group by a.operator_id
{code}
Скрипт для получения журнала звонков по всем [архивным базам|CarbonBilling:Настройка периода хранения исторических данных в базе]
{code}#!/bin/bash

>export.csv
>export_raw.csv

# Текущая дата
curdate=$(date +'%Y-%m-%d %H:%M:%S')

# Начало периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00'
period_start='2019-01-01 00:00:00'
# Конец периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00'
period_end='2019-10-01 00:00:00'

# Если начало или конец периода не заданы - будет выбрана текущая дата
period_start_fixed=${period_start:-$curdate}
period_end_fixed=${period_end:-$curdate}

# ID абонента
abonent_id=10507

# Заголовок csv для удобства работы в электронных таблицах
echo "Звонок с номера;Назначение звонка;Название направления;Начало звонка;Окончание звонка;Время разговора;Стоимость направления;Стоимость" > export.csv

# Получаем звонки из архивных баз
find /var/db/billing/ -iname voip_log.fdb | sort | while read file; do
#echo $file >> export_raw.csv # DEBUG
sqlexec $file "set heading off; select distinct
vl.src || ';' ||
vl.dst || ';' ||
'direction:' || coalesce(vl.direction_id,0) || ';' ||
substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' ||
substring(cast(vl.e_time as varchar(32)) from 1 for 19) || ';' ||
cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) || ';' ||
coalesce(vl.step_price/10000000000.00,0) || ';' ||
cast(coalesce(vl.bill_sum/10000000000.00,'') as float)
from
voip_log vl
where
vl.s_time between '$period_start' and '$period_end_fixed'
and vl.abonent_id=$abonent_id " | sed 's/ *//g; /^$/d' >> export_raw.csv
done

# Получаем звонки из основной БД
#echo '/var/db/billing.gdb' >> export_raw.csv # DEBUG
sqlexec /var/db/billing.gdb "set heading off; select distinct
vl.src || ';' ||
vl.dst || ';' ||
'direction:' || vl.direction_id || ';' ||
substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' ||
cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'), coalesce(vl.bill_sec_round/60,'0')) as float) || ';' ||
coalesce(vl.step_price/10000000000.00,0) || ';' ||
cast(coalesce(vl.bill_sum/10000000000.00,'') as float)
from
voip_log vl
where
vl.s_time between '$period_start_fixed' and '$period_end_fixed'
and vl.abonent_id=$abonent_id" | sed 's/ *//g; /^$/d' >> export_raw.csv

# Получаем из БД названия направлений и отфильтровываем дебаг
cat export_raw.csv | grep -vE '^<null>|voip_log.fdb$' | cut -d';' -f 3 | sort | uniq | while IFS=':' read field_name direction_id; do
direction_name=$(sqlexec "set heading off; select vd.name from voip_direction vd where vd.id='$direction_id'" | sed 's/ *//g; /^$/d')
sed "s/direction:${direction_id}/${direction_name}/g" -i export_raw.csv
done

# Сортируем на случаей если звонок попал в несколько архивных баз и пишем в выгрузку
cat export_raw.csv | grep -v '^<null>' | sort | uniq >> export.csv{code}
# h5. Отчёт по абонентам, у которых услуги подключены не 5 числа. Данный отчёт подойдет для отслеживания абонентов со сдвигом даты списания услуг.
{code}
select
a.contract_number as "Договор",
a.name as "ФИО",
uu.usluga_id as "ID услуги",
u.name as "Имя услуги"
from users_usluga uu
left join usluga u on u.id=uu.usluga_id
left join abonents a on a.id=uu.abonent_id
where extract(day from cast(uu.create_date as date))!=5
and u.USLUGA_ABON_TYPE_ID=4
and uu.deleted=0
and uu.usluga_id!=-170000
{code}
# h5. Список абонентов, у которых не заведён дом или дом заведён некорректно
Отчет
{code}select
a.id,
a.name,
a.contract_number,
t.name,
p.name,
a.home_id,
iif(a.home_id is not null, 'Улица: ' || coalesce(h.street,''),'Не выбран дом'),
iif(a.home_id is not null and (h.city is null or h.city=''), 'Не указан город', 'Не выбран дом')
from
abonents a
join tarif t on a.tarif_id=t.id
join abonents p on a.parent_id=p.id
left join homes h on a.home_id=h.id
where
(a.home_id is null or h.city='' or h.city is null)
and a.is_folder=0
and a.category_id<2
and a.deleted=0
and a.id>0
and a.parent_id not in (244,1313,16723)
order by
7,5{code}
Шаблон
{code}{% extends "form_list.html" %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}{% load mathfilters %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><h2>Заполните поля запроса</h2></legend>
{{ form }}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<div class="btn-group">
<a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
<i class="icon-print icon-white"></i>
Выгрузка
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><button type="submit" name="csv" value="True">CSV</button></li>
<li><button type="submit" name="dbf" value="True">DBF</button></li>
</ul>
</div>
<button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{inst.name}}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="2">
<tr>
<th>ФИО</th>
<th>Договор</th>
<th>Тариф</th>
<th>Группа</th>
<th>Улица</th>
<th>Проблема</th>
</tr>
{% for row in data %}
<tr>
<td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
<td>{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td>{{ row.4 }}</td>
<td>{% if row.5 %}<a href="/admin/Abonents/Abonents/{{ row.5 }}/" target="_blank">{{ row.6 }}{% else %} --- {% endif %}</a></td>
<td>{{ row.7 }}</td>
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}
{code}
# h5. Отчёт по отправленным сообщениям из аудита
{code}
select
a.name as "ФИО",
a.contract_number as "Договор",
ao.descr as "Описание",
ao.OP_TIME as "Дата"
from audit_operations ao
left join abonents a on a.id=ao.abonent_id
where ao.descr like '%Сооб%' and ao.abonent_id is not null
and ao.OP_TIME between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
{code}
# h5. Вывод абонентов у кого подключен автоплатеж
{code}
select
a.id,
a.contract_number as " номер договора",
a.name as "ФИО",
iif(a.autopay_pay_log_id is not null, 'Включен', ' ') as "автоплатеж"
from abonents a
where
a.is_folder=0
and a.deleted=0
and a.parent_id not in (2,4,244)
and a.autopay_pay_log_id is not null
{code}
# h5. Анализ проводок по абонентам и выявление большого количества записей у абонентов(более 500 записей в месяц, это значение можно менять для отсортировки результатов) в таблице ARCH_ACCOUNT_STACK.
{code}
select
ABONENT_ID,
extract(year from CHANGE_BALANCE_TIME),
extract(month from CHANGE_BALANCE_TIME),
count(*)
from ARCH_ACCOUNT_STACK
group by 1,2,3
having count(*)>500
{code}
# h5. Юрлица без ИНН
Можно ограничить выборку только выбранной папкой, если все юрлица находятся в ней - в функции 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}
...
order by ir.ip
{code}

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

...