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

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

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

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

h2. Финансовая информация
...
a.is_folder = 0
{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. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)"
{code}
select NAME,
UF_IP2STRING(START_IP) as "Начальный IP",
UF_IP2STRING(END_IP) as "Конечный IP",
(select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0) as "Занято",
(END_IP - START_IP +1 - (select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0)) as "Свободно"
from IP_PULL
order by NAME
{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
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 "Договор",
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 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}

h2. Учётные записи
# h5. Отчет по свободным IP адресам
...
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}

h2. Блокировки
# h5. Отчет выводящий информацию из карточки абонента с текущим статусом; колонки административной и добровольной блокировками заполняются только в случае активной блокировки; в последней колонке пишется дата, до которой действительна добровольная блокировка
{code}
select distinct AB.CONTRACT_NUMBER,
AB.NAME as "Subscriber",
T.NAME as "Package",
AB.CREATE_DATE as "Creation date",
AB.ACTIVATE_DATE as "Activation date",
SMS as "Phone",
EMAIL as "E-mail",
H.STREET,
H.S_NUMBER,
H.S_LITER,
AB.A_HOME_NUMBER,
S.NAME as "Status",
extract(day from OS.APPLY_DATE)||'.'||extract(month from OS.APPLY_DATE)||'.'||extract(year from OS.APPLY_DATE) as "Change date",
(case when ab.id in (select abonent_id from abonents_block where B_ADMIN = 1) then 'Blocked' else '' end) as "Admin",
(case when ab.id in (select abonent_id from abonents_block where B_OWN = 1) or ab.id in (select abonent_id from abonents_block where B_OWN2 = 1) then 'Blocked' else '' end) as "Own",
coalesce(cast(OWN_DISABLED_END as varchar(100)), '') as "Block end"
from ABONENTS AB
left join HOMES H on H.ID = AB.HOME_ID
left join OBJECTS_STATUS OS on OS.OBJECT_ID = AB.ID
left join TARIF T on AB.TARIF_ID = T.ID
left join STATUS S on S.ID = OS.STATUS
where (AB.IS_FOLDER = 0
or AB.IS_FOLDER is null) and AB.DELETED = 0
{code}
# h5. Отчёт, выводящий список незаблокированных абонентов.
{code}
select
a.name as "ФИО",
a.contract_number as "Договор"
from abonents a
left join abonents_block ab on ab.abonent_id=a.id
where ab.id is null
and a.deleted=0
and a.is_folder=0
{code}
# h5. Отчёт по абонентам с блокировкой по отрицательному балансу.
{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 uu.deleted=0
{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. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения.
{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
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}

h2. Телефония

...
order by :1-Сортировка по:|choices[2^]Наименованию^[6^]Транкам^[5^]Типу трафика]$
{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. История выдачи телефонного номера для 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. Отчёт по звонкам учтенным для взаиморасчетов между операторами
{code}select
calls.service as "Услуга",
calls.source as "Источник",
calls.destination as "Назначение",
calls.call_start as "Начало звонка",
calls.call_end as "Завершение звонка",
calls.s_chan as "Ист. транк",
calls.d_chan as "Назн. транк",
calls.cost as "Стоимость"
from
(select
aas.id aasid,
vl.id vlid,
u.name service,
aas.credit/10000000000.00 cost,
s_time call_start,
e_time call_end,
src source,
dst destination,
src_chan s_chan,
dst_chan d_chan
from
voip_log vl
join arch_account_stack aas
on vl.id=aas.log_id
join usluga u
on aas.usluga_id=u.id
and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$
where
aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$'
and s_time
between ':1-Период начала звонка, от|date$'
and ':2-Период начала звонка, до|date$ 23:59:59'
) calls
union all
select
' ',
' ',
'Итого',
cast(':1-Период начала звонка, от|date$' as timestamp),
cast(':2-Период начала звонка, до|date$ 23:59:59' as timestamp),
' ',
' ',
cast(sum(calls.cost) as varchar(32))
from
(select
aas.id aasid,
vl.id vlid,
u.name service,
aas.credit/10000000000.00 cost,
s_time call_start,
e_time call_end,
src source,
dst destination,
src_chan s_chan,
dst_chan d_chan
from
voip_log vl
join arch_account_stack aas
on vl.id=aas.log_id
join usluga u
on aas.usluga_id=u.id
and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$
where
aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$'
and s_time
between ':1-Период начала звонка, от|date$'
and ':2-Период начала звонка, до|date$ 23:59:59'
) calls
group by
calls.service
{code}
# h5. Цены на направления за период
{code}
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}

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

h2. Интернет трафик
# h5. Отчёт по скачанному трафику за период.
{code:lang=sql}
...
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^]Декабрь]$')
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. Отчет по входящему интернет-трафику юридических лиц за выбранный период месяц
Отчет расчитан на использование тарифов типа лестница с некоторым объёмом трафика включенного в абонентскую плату, по исчерпании которого начисляется помегабайтная оплата.
По каждому абоненту отражены тарифы назначенные абоненту за период, суммарное потребление трафика по тарифу и отдельно трафик сверх включенного, а так же его стоимость.
{code}select
aid,
' ',
s_id,
'   УСЛУГА: ' || serv "Тариф/Услуга",
coalesce(price,'') "Цена МБ",
'' "Включено МБ",
sum(mb_count) "МБ по усл./тарифу",
'' "Всего МБ",
sum(traf_cost) "Сумма руб."
from
(select distinct
ab.id as aid,
ab.name as fio,
t.id as tar,
t.name as tid,
usl.name as serv,
usl.id as s_id,
tar_cost.in_price as price,
uslpreord.mbinc,
coalesce(tfc.p_in/10000000000.00,0) as traf_cost,
round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count

from
traf_counters as tfc
join abonents ab on tfc.abonent_id=ab.id
join users_usluga as uu on tfc.users_usluga_id=uu.id
join usluga usl on uu.usluga_id=usl.id
left join tarif t on uu.tarif_id=t.id
join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id
where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0
group by tuu.tarif_id
) uslpreord on uu.tarif_id=uslpreord.tar
left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0
) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv
where
tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
and ab.company = 1
and (usl.max_mb_in_m>0 or usl.in_price>0)
--and ab.id in (1172,2575)
)
where
(traf_cost<>0 and price<>0)
group by 1,3,4,5,6
union all
select
aid,
' ',
tid,
'ТАРИФ: ' || tar "Тариф",
' ',
cast(mbinc as varchar(16)) "Включено МБ",
cast(sum(mb_count) as varchar(16)),
cast(iif((sum(mb_count)-mbinc)>0, sum(mb_count)-mbinc, null) as varchar(32)) "МБ по услуге",
iif(sum(traf_cost)>0,sum(traf_cost), null) "Сумма руб."
from
(select distinct
ab.id as aid,
ab.name as fio,
t.id as tid,
t.name as tar,
tar_cost.in_price as price,
uslpreord.mbinc,
coalesce(tfc.p_in/10000000000.00,0) as traf_cost,
round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count

from
traf_counters as tfc
join abonents ab on tfc.abonent_id=ab.id
join users_usluga as uu on tfc.users_usluga_id=uu.id
join usluga usl on uu.usluga_id=usl.id
left join tarif t on uu.tarif_id=t.id
join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id
where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0
group by tuu.tarif_id
) uslpreord on uu.tarif_id=uslpreord.tar
left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0
) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv
where
tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
and ab.company = 1
and (usl.max_mb_in_m>0 or usl.in_price>0)
--and ab.id in (1172,2575)
)
where
(traf_cost<>0 and price<>0) or (traf_cost=0 and price is null)
group by 1,2,3,4,mbinc
union all
select distinct
aid,
fio,
null,
'       ',
'       ',
'       ',
'       ',
'       ',
null
from
(select distinct
ab.id as aid,
ab.name as fio,
t.id as tid,
t.name as tar,
tar_cost.in_price as price,
uslpreord.mbinc,
coalesce(tfc.p_in/10000000000.00,0) as traf_cost,
round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count

from
traf_counters as tfc
join abonents ab on tfc.abonent_id=ab.id
join users_usluga as uu on tfc.users_usluga_id=uu.id
join usluga usl on uu.usluga_id=usl.id
left join tarif t on uu.tarif_id=t.id
join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id
where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0
group by tuu.tarif_id
) uslpreord on uu.tarif_id=uslpreord.tar
left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0
) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv
where
tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
and ab.company = 1
and (usl.max_mb_in_m>0 or usl.in_price>0)
--and ab.id in (1172,2575)
)
order by 1,2,4{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 bordercolor="#AAAAAA" id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="3">
<tr>
<th>ФИО</th>
<th>Тариф</th>
<th>Цена за МБ</th>
<th>Объем предоплаченного трафика</th>
<th>Объем трафика по тарифу/услуге</th>
<th>Объем трафика сверх предоплаченного</th>
<th>Переплата</th>
</tr>
{% for row in data %}
{% if row.1 != ' ' %}
<tr>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
</tr>
{% endif %}
{% if row.1 != ' ' and row.2 != ' ' %}
<tr>
<td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
</tr>
{% else %}
<tr>
<td> </td>
<td>{% if row.4 != ' ' %} <a href="/admin/tarifs/Usluga/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% else %} <a href="/admin/tarifs/Tarif/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% endif %}</td>
<td style="text-align: right;">{% if row.4 != ' ' %} {{ row.4 }} руб. {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.5 %} {{ row.5|floatformat:2 }} Мб {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.6 %} {{ row.6|floatformat:2 }} Мб {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.7 %} {{ row.7|floatformat:2 }} Мб {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.8 %} {{ row.8|floatformat:2 }} руб. {% else %} {% endif %}</td>
</tr>
{% endif %}
{% 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
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. Отчёт по объёмам потребленного трафика посуточно и остатка/превышения ежедневных и ежемесячных лимитов (если они есть) за указанный период
Для работы отчета необходимо включить опцию "*Сохранять движения всего трафика*" в [настройках оператора связи|CarbonBilling:Глобальные настройки биллинга и оператора]
{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
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
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)),''),

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}

h2. CRM и Helpdesk
# 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}

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

# h5. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number)
{code:lang=sql}
...
{% endblock %}
{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. Отчёт по абонентам в формате "ФИО ЛОГИН 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: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. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)"
{code}
select NAME,
UF_IP2STRING(START_IP) as "Начальный IP",
UF_IP2STRING(END_IP) as "Конечный IP",
(select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0) as "Занято",
(END_IP - START_IP +1 - (select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0)) as "Свободно"
from IP_PULL
order by NAME
{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
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 distinct AB.CONTRACT_NUMBER,
AB.NAME as "Subscriber",
T.NAME as "Package",
AB.CREATE_DATE as "Creation date",
AB.ACTIVATE_DATE as "Activation date",
SMS as "Phone",
EMAIL as "E-mail",
H.STREET,
H.S_NUMBER,
H.S_LITER,
AB.A_HOME_NUMBER,
S.NAME as "Status",
extract(day from OS.APPLY_DATE)||'.'||extract(month from OS.APPLY_DATE)||'.'||extract(year from OS.APPLY_DATE) as "Change date",
(case when ab.id in (select abonent_id from abonents_block where B_ADMIN = 1) then 'Blocked' else '' end) as "Admin",
(case when ab.id in (select abonent_id from abonents_block where B_OWN = 1) or ab.id in (select abonent_id from abonents_block where B_OWN2 = 1) then 'Blocked' else '' end) as "Own",
coalesce(cast(OWN_DISABLED_END as varchar(100)), '') as "Block end"
from ABONENTS AB
left join HOMES H on H.ID = AB.HOME_ID
left join OBJECTS_STATUS OS on OS.OBJECT_ID = AB.ID
left join TARIF T on AB.TARIF_ID = T.ID
left join STATUS S on S.ID = OS.STATUS
where (AB.IS_FOLDER = 0
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}
select
a.name as "ФИО",
a.contract_number as "Договор"
from abonents a
left join abonents_block ab on ab.abonent_id=a.id
where ab.id is null
and a.deleted=0
and a.is_folder=0
{code}
# h5. Отчёт по абонентам с блокировкой по отрицательному балансу.
{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 uu.deleted=0
{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 (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 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}
...
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. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения.
{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}
...
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}
...
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}
...
and a.parent_id not in (244,4,2)
{code}
# h5. Отчёт по объёмам потребленного трафика посуточно и остатка/превышения ежедневных и ежемесячных лимитов (если они есть) за указанный период
Для работы отчета необходимо включить опцию "*Сохранять движения всего трафика*" в [настройках оператора связи|CarbonBilling:Глобальные настройки биллинга и оператора]
{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
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
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)),''),

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.service as "Услуга",
calls.source as "Источник",
calls.destination as "Назначение",
calls.call_start as "Начало звонка",
calls.call_end as "Завершение звонка",
calls.s_chan as "Ист. транк",
calls.d_chan as "Назн. транк",
calls.cost as "Стоимость"
from
(select
aas.id aasid,
vl.id vlid,
u.name service,
aas.credit/10000000000.00 cost,
s_time call_start,
e_time call_end,
src source,
dst destination,
src_chan s_chan,
dst_chan d_chan
from
voip_log vl
join arch_account_stack aas
on vl.id=aas.log_id
join usluga u
on aas.usluga_id=u.id
and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$
where
aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$'
and s_time
between ':1-Период начала звонка, от|date$'
and ':2-Период начала звонка, до|date$ 23:59:59'
) calls
union all
select
' ',
' ',
'Итого',
cast(':1-Период начала звонка, от|date$' as timestamp),
cast(':2-Период начала звонка, до|date$ 23:59:59' as timestamp),
' ',
' ',
cast(sum(calls.cost) as varchar(32))
from
(select
aas.id aasid,
vl.id vlid,
u.name service,
aas.credit/10000000000.00 cost,
s_time call_start,
e_time call_end,
src source,
dst destination,
src_chan s_chan,
dst_chan d_chan
from
voip_log vl
join arch_account_stack aas
on vl.id=aas.log_id
join usluga u
on aas.usluga_id=u.id
and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$
where
aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$'
and s_time
between ':1-Период начала звонка, от|date$'
and ':2-Период начала звонка, до|date$ 23:59:59'
) calls
group by
calls.service{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}select
aid,
' ',
s_id,
'   УСЛУГА: ' || serv "Тариф/Услуга",
coalesce(price,'') "Цена МБ",
'' "Включено МБ",
sum(mb_count) "МБ по усл./тарифу",
'' "Всего МБ",
sum(traf_cost) "Сумма руб."
from
(select distinct
ab.id as aid,
ab.name as fio,
t.id as tar,
t.name as tid,
usl.name as serv,
usl.id as s_id,
tar_cost.in_price as price,
uslpreord.mbinc,
coalesce(tfc.p_in/10000000000.00,0) as traf_cost,
round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count

from
traf_counters as tfc
join abonents ab on tfc.abonent_id=ab.id
join users_usluga as uu on tfc.users_usluga_id=uu.id
join usluga usl on uu.usluga_id=usl.id
left join tarif t on uu.tarif_id=t.id
join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id
where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0
group by tuu.tarif_id
) uslpreord on uu.tarif_id=uslpreord.tar
left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0
) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv
where
tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
and ab.company = 1
and (usl.max_mb_in_m>0 or usl.in_price>0)
--and ab.id in (1172,2575)
)
where
(traf_cost<>0 and price<>0)
group by 1,3,4,5,6
union all
select
aid,
' ',
tid,
'ТАРИФ: ' || tar "Тариф",
' ',
cast(mbinc as varchar(16)) "Включено МБ",
cast(sum(mb_count) as varchar(16)),
cast(iif((sum(mb_count)-mbinc)>0, sum(mb_count)-mbinc, null) as varchar(32)) "МБ по услуге",
iif(sum(traf_cost)>0,sum(traf_cost), null) "Сумма руб."
from
(select distinct
ab.id as aid,
ab.name as fio,
t.id as tid,
t.name as tar,
tar_cost.in_price as price,
uslpreord.mbinc,
coalesce(tfc.p_in/10000000000.00,0) as traf_cost,
round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count

from
traf_counters as tfc
join abonents ab on tfc.abonent_id=ab.id
join users_usluga as uu on tfc.users_usluga_id=uu.id
join usluga usl on uu.usluga_id=usl.id
left join tarif t on uu.tarif_id=t.id
join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id
where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0
group by tuu.tarif_id
) uslpreord on uu.tarif_id=uslpreord.tar
left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0
) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv
where
tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
and ab.company = 1
and (usl.max_mb_in_m>0 or usl.in_price>0)
--and ab.id in (1172,2575)
)
where
(traf_cost<>0 and price<>0) or (traf_cost=0 and price is null)
group by 1,2,3,4,mbinc
union all
select distinct
aid,
fio,
null,
'       ',
'       ',
'       ',
'       ',
'       ',
null
from
(select distinct
ab.id as aid,
ab.name as fio,
t.id as tid,
t.name as tar,
tar_cost.in_price as price,
uslpreord.mbinc,
coalesce(tfc.p_in/10000000000.00,0) as traf_cost,
round(tfc.v_in / cast((1048576) as numeric(18,5)), 2) as mb_count

from
traf_counters as tfc
join abonents ab on tfc.abonent_id=ab.id
join users_usluga as uu on tfc.users_usluga_id=uu.id
join usluga usl on uu.usluga_id=usl.id
left join tarif t on uu.tarif_id=t.id
join (select tuu.tarif_id as tar, sum(tu.max_mb_in_m) as mbinc
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id
where tu.max_mb_in_m is not null and tu.max_mb_in_m<>0
group by tuu.tarif_id
) uslpreord on uu.tarif_id=uslpreord.tar
left join (select distinct tuu.tarif_id as tar, tu.id as serv, tu.in_price/10000000000.00 as in_price
from tarif_users_usluga tuu
join usluga tu on tuu.usluga_id=tu.id and tu.in_price<>0
) tar_cost on uu.tarif_id=tar_cost.tar and uu.usluga_id=tar_cost.serv
where
tfc.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
and tfc.year_number = (':Год|choices[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
and ab.company = 1
and (usl.max_mb_in_m>0 or usl.in_price>0)
--and ab.id in (1172,2575)
)
order by 1,2,4{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 bordercolor="#AAAAAA" id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="3">
<tr>
<th>ФИО</th>
<th>Тариф</th>
<th>Цена за МБ</th>
<th>Объем предоплаченного трафика</th>
<th>Объем трафика по тарифу/услуге</th>
<th>Объем трафика сверх предоплаченного</th>
<th>Переплата</th>
</tr>
{% for row in data %}
{% if row.1 != ' ' %}
<tr>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
<td bgcolor="#F5F5F5"> </td>
</tr>
{% endif %}
{% if row.1 != ' ' and row.2 != ' ' %}
<tr>
<td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
</tr>
{% else %}
<tr>
<td> </td>
<td>{% if row.4 != ' ' %} <a href="/admin/tarifs/Usluga/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% else %} <a href="/admin/tarifs/Tarif/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% endif %}</td>
<td style="text-align: right;">{% if row.4 != ' ' %} {{ row.4 }} руб. {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.5 %} {{ row.5|floatformat:2 }} Мб {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.6 %} {{ row.6|floatformat:2 }} Мб {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.7 %} {{ row.7|floatformat:2 }} Мб {% else %} {% endif %}</td>
<td style="text-align: right;">{% if row.8 %} {{ row.8|floatformat:2 }} руб. {% else %} {% endif %}</td>
</tr>
{% endif %}
{% 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
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. Отчёт по сумме списаний у абонентов. Списания суммируются из поля "Расход/предоплата" в финансовой информации абонента.
{code}
...