|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Изменения (72)
просмотр истории страницы{toc:maxLevel=23} |
h1. Описание конструктора отчетов |
... |
h1. Примеры отчётов |
# h5. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата |
h3. Абоненты |
{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. Список абонентов, Список абонентов, взявших больше одного обещанного платежа |
{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 |
{code} |
# h5. Отчет по количеству активных абонентов, у которых назначен IP и NAS с id 70 {code}select |
... |
and (current_timestamp - ab.b_date) < 90)) |
group by h.s_number{code} |
# h5. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам. {code}select first 1 (':Начало|date$') as "Период начало", (':Конец|date$') as "Период окончание", |
( {code} |
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' |
# 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 aas.storno=0 tc.currentt=1 |
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик') |
{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 ab.company a.is_folder = 0 |
) as "Абон. плата физ.", |
and a.parent_id != 244 and a.parent_id != 2 |
( {code} |
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 |
# 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 aas.storno=0 a.company=1 |
) as "Абон. плата юр.", |
( {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 = 0 ) as "Приходы физ.", |
# h5. Отчёт прибыль по плате за подключение нескольких услуг. |
( {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 "Приходы юр.", |
( select |
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 "Итого", |
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 |
( |
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 "Подписанные", |
union all |
( |
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 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} |
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} h3. Финансовая информация |
# 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. Отчёт по приходам за заданный период времени {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 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. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%" {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 "Остаток", 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 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. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус. {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} h3. Учётные записи # 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 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} select distinct a.name as "ФИО", b.max_time as "Последнее подключ." from abonents as a 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 round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток", u.login, u.GEN_PWD, u.auth_type, uf_ip2string(s.ip) as "switch_ip", abs.RATE_IN, abs.RATE_OUT, abs.CEIL_IN, abs.CEIL_OUT, uf_ip2string(u.ip) as "user_ip", u.ROUTER_VLAN, u.GPON_MODEM_PORT, u.ROUTER_PORT from users as u left join abonents as a on a.id=u.abonent_id left join admin_accounts as aa on aa.id=a.ACCOUNT_ID left join switch as s on s.id=u.switch_id left join ABONENTS_SPEED as ABS on abs.abonent_id=a.id left join NAS on nas.id=u.nas_id where a.is_folder=0 and a.deleted=0 and uf_ip2string(nas.ip)=(':nas_ip$') {code} # h5. Поиск IP-адреса в истории изменения учетных записей (история выдачи IP-адреса) При каждом изменении учётной записи новое состояние учётной записи записывается в историю. Таблица *users_history*. Отчёт покажет когда абоненту был назначен IP-адрес, а также были ли измененяия по абоненту пока он владел этим адресом. Отчёт не показывает когда IP-адрес был снят с абонента. {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 uf_ip2string(ip) = ':Введите ip-адрес$' order by time_changed {code} # h5. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс" {code} select distinct A.CONTRACT_NUMBER as "Договор", T.name as "Тариф", USL.NAME as "Имя услуги", UU.ACTIVATED 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 left join USERS_USLUGA UU on UU.abonent_id=A.id left join USLUGA USL on USL.ID=UU.USLUGA_ID where A.IS_FOLDER=0 AND A.DELETED=0 order by A.NAME {code} h3. Услуги # h5. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период. {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 "Цена", 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.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) order by 1 {code} h3. Телефония # h5. Отчёт по телефонии. Структура данных информации об Абонентах по маске 7496 h6. sql запрос {code} select distinct 'K6#' || A.CONTRACT_NUMBER as "Договор", A.NAME, AV_UR_ADDR.ATTRIBUTE_VALUE as URR_ADDR, AV_UR_INN.ATTRIBUTE_VALUE as INN, AV_UR_KPP.ATTRIBUTE_VALUE as KPP, '2' as c4, '2' as c5, '1' as c6, '643' as c7, CAST(lpad(EXTRACT(DAY FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM A.CREATE_DATE) || ' 00:00:00' as CREATE_DATE, IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') end_date, '1' as c1, '46' as c2, '0' as c3 from abonents A left join ATTRIBUTE_VALUES AV_UR_ADDR on AV_UR_ADDR.ABONENT_ID=A.ID and AV_UR_ADDR.ATTRIBUTE_ID=25 left join ATTRIBUTE_VALUES AV_UR_INN on AV_UR_INN.ABONENT_ID=A.ID and AV_UR_INN.ATTRIBUTE_ID=4 left join ATTRIBUTE_VALUES AV_UR_KPP on AV_UR_KPP.ABONENT_ID=A.ID and AV_UR_KPP.ATTRIBUTE_ID=5 left join users on users.abonent_id=A.id left join PHONE_PULL_CHACHE PPC on PPC.id=users.phone left join abonents_history ah on ah.abonents_id=a.id where COALESCE(A.IS_FOLDER, 0)=0 and A.CATEGORY_ID=1 and PPC.PHONE like '7496%' and a.company<>':Абонент|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$' and (select count(*) from abonents ab where ab.account_id=a.account_id group by ab.account_id)=1 UNION ALL select distinct 'K6#' || A.CONTRACT_NUMBER as "Договор", A.NAME, AV_UR_ADDR.ATTRIBUTE_VALUE as URR_ADDR, AV_UR_INN.ATTRIBUTE_VALUE as INN, AV_UR_KPP.ATTRIBUTE_VALUE as KPP, '2' as c4, '2' as c5, '1' as c6, '643' as c7, CAST(lpad(EXTRACT(DAY FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM A.CREATE_DATE) || ' 00:00:00' as CREATE_DATE, IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') end_date, '1' as c1, '46' as c2, '0' as c3 from abonents A left join ATTRIBUTE_VALUES AV_UR_ADDR on AV_UR_ADDR.ABONENT_ID=A.ID and AV_UR_ADDR.ATTRIBUTE_ID=25 left join ATTRIBUTE_VALUES AV_UR_INN on AV_UR_INN.ABONENT_ID=A.ID and AV_UR_INN.ATTRIBUTE_ID=4 left join ATTRIBUTE_VALUES AV_UR_KPP on AV_UR_KPP.ABONENT_ID=A.ID and AV_UR_KPP.ATTRIBUTE_ID=5 left join users on users.abonent_id=A.id left join PHONE_PULL_CHACHE PPC on PPC.id=users.phone left join abonents_history ah on ah.abonents_id=a.id where COALESCE(A.IS_FOLDER, 0)=0 and A.CATEGORY_ID=1 and PPC.PHONE like '7496%' and a.company<>':Абонент|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$' and a.main=1 {code} h6. Шаблон отчёта {code} {% 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. Отчёт по телефонии по звонкам, которые были выполнены на конкретный пул телефонных номеров за указанную дату по конкретному абоненту. {code} select vl.id as "ID звонка", s_time as "Начало звонка", e_time as "Конец звонка", (select first 1 a.name from abonents a where id=vl.abonent_id) as "Абонент", src as "Номер абон.", dst as "Исх. номер", duration as "Длит.", (select first 1 d.name from voip_direction d where d.id=vl.DIRECTION_ID) as "Направление", round(cast(BILL_SUM as numeric(18,2)) / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,2)), 2) as "Сумма", chan as "Канал" from voip_log VL where billed=1 and (error_code is null or error_code=0) and cast(e_time as date) between cast(':2 С даты|date$' as date) and cast(':3 По дату(включительно)|date$' as date) and duration > 0 and vl.abonent_id=:1Абонент|select[Abonents,is_folder=0]$ AND (vl.DST between (':4 Начало пула$') and (':5 Конец пула$')) {code} # h5. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 496. В данном примере: #* when c.usluga_id=93 then 10 #* when c.usluga_id=92 then 12 #* when c.usluga_id=91 then 14 #* 93 - Id услуги "Международная связь" #* 92 - Id услуги "Междугородная связь" #* 91 - Id услуги "Внутризоновая связь" h6. SQL запрос: {code} select 'Cnst1' as "Аг. договор", A.CONTRACT_NUMBER as "Аб. договор", fo.number as "Фактура", fo.number as "Акт", fo.period_end_date as "Дата счета", dateadd(-16 day to dateadd(month, 1, fo.period_end_date)) as "Срок оплаты", '0' as "Код валюты", '2' as "Код НДС", case when c.usluga_id=93 then 10 when c.usluga_id=92 then 12 when c.usluga_id=91 then 14 end as "Код услуги", fo.period_end_date as "Дата", cast(c.summ as numeric(18,2)) as "Сумма", cast(c.v as numeric(18,2)) as "Минуты", '46' as "C5", '0' as "C6" from abonents a join counters c on c.abonent_id=a.id and c.closed=1 and c.usluga_id in (91,92,93) and c.act_id is not null join finance_operations fo on fo.op_id = c.act_id where a.deleted=0 and a.is_folder=0 and (extract(year from period_end_date)=(':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and extract(month from period_end_date)=(':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')) and 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. Шаблон отчёта: {code} {% 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. Отчёт для анализа настройки агентской схемы VoIP, отражает текущую настройку транков в операторских услугах телефонии {code}select a.name FIO, coalesce(vt.name,'---') DIRECTION, u.name USLUGA, uu.voip_operator_match_format TRUNK from users_usluga uu join usluga u on uu.usluga_id=u.id join abonents a on uu.abonent_id=a.id left join V_TYPE vt on u.voip_v_type_id=vt.id where uu.deleted=0 and a.deleted=0 and u.system_type=5 order by a.id,vt.name{code} # h5. Отчёт для анализа настройки агентской схемы VoIP, подобный предыдущему, но отражает дополнительные данные и удалённые услуги {code} select distinct a.id "ID", a.name "Оператор", coalesce(u.name,'Нет услуги') "Услуга", 'UU_id=' || uu.id || ', del=' || uu.deleted || ', U_id=' || u.id "Метаданные", coalesce(vt.name,'') "Тип трафика", iif(uu.id is not null,voip_operator_match_format,'отсутствуют') "Транки" from abonents a join users_usluga uu on a.id=uu.abonent_id and voip_operator_match_format<>'' left join usluga u on uu.usluga_id=u.id left join v_type vt on u.voip_v_type_id=vt.id where a.category_id=2 and a.id>2 and uu.deleted=:2-Показывать удалённые?|choices[0^]Нет^[1^]Да]$ order by :1-Сортировка по:|choices[2^]Наименованию^[6^]Транкам^[5^]Типу трафика]$ {code} h3. Прочие отчёты # 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. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number) {code:lang=sql} |
... |
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.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. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)" {code} |
... |
order by NAME {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 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. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%" {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 "Остаток", 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 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. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период. {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 "Цена", 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.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) order by 1 {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. Отчёт выводит абонентов, последнее подключение которых было раньше, чем указанная дата. |
{code} |
select distinct a.name as "ФИО", b.max_time as "Последнее подключ." from abonents as a 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 round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток", u.login, u.GEN_PWD, u.auth_type, uf_ip2string(s.ip) as "switch_ip", abs.RATE_IN, abs.RATE_OUT, abs.CEIL_IN, abs.CEIL_OUT, uf_ip2string(u.ip) as "user_ip", u.ROUTER_VLAN, u.GPON_MODEM_PORT, u.ROUTER_PORT from users as u left join abonents as a on a.id=u.abonent_id left join admin_accounts as aa on aa.id=a.ACCOUNT_ID left join switch as s on s.id=u.switch_id left join ABONENTS_SPEED as ABS on abs.abonent_id=a.id left join NAS on nas.id=u.nas_id where a.is_folder=0 and a.deleted=0 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-адрес был снят с абонента. {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 uf_ip2string(ip) = ':Введите ip-адрес$' order by time_changed {code} # h5. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс" {code} select distinct A.CONTRACT_NUMBER as "Договор", T.name as "Тариф", USL.NAME as "Имя услуги", UU.ACTIVATED 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 left join USERS_USLUGA UU on UU.abonent_id=A.id left join USLUGA USL on USL.ID=UU.USLUGA_ID where A.IS_FOLDER=0 AND A.DELETED=0 order by A.NAME {code} |
# h5. Отчёт по платившим абонентам в формате "Номер договора - ФИО - Группа - Дата и время операции - Описание - Сумма" с выводом итоговой суммы и возможностью выбора платежей по конкретной папке. {code} |
... |
group by a.parent_id {code} |
# h5. Отчёт по телефонии. Структура данных информации об Абонентах по маске 7496 h6. sql запрос {code} select distinct 'K6#' || A.CONTRACT_NUMBER as "Договор", A.NAME, AV_UR_ADDR.ATTRIBUTE_VALUE as URR_ADDR, AV_UR_INN.ATTRIBUTE_VALUE as INN, AV_UR_KPP.ATTRIBUTE_VALUE as KPP, '2' as c4, '2' as c5, '1' as c6, '643' as c7, CAST(lpad(EXTRACT(DAY FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM A.CREATE_DATE) || ' 00:00:00' as CREATE_DATE, IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') end_date, '1' as c1, '46' as c2, '0' as c3 from abonents A left join ATTRIBUTE_VALUES AV_UR_ADDR on AV_UR_ADDR.ABONENT_ID=A.ID and AV_UR_ADDR.ATTRIBUTE_ID=25 left join ATTRIBUTE_VALUES AV_UR_INN on AV_UR_INN.ABONENT_ID=A.ID and AV_UR_INN.ATTRIBUTE_ID=4 left join ATTRIBUTE_VALUES AV_UR_KPP on AV_UR_KPP.ABONENT_ID=A.ID and AV_UR_KPP.ATTRIBUTE_ID=5 left join users on users.abonent_id=A.id left join PHONE_PULL_CHACHE PPC on PPC.id=users.phone left join abonents_history ah on ah.abonents_id=a.id where COALESCE(A.IS_FOLDER, 0)=0 and A.CATEGORY_ID=1 and PPC.PHONE like '7496%' and a.company<>':Абонент|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$' and (select count(*) from abonents ab where ab.account_id=a.account_id group by ab.account_id)=1 UNION ALL select distinct 'K6#' || A.CONTRACT_NUMBER as "Договор", A.NAME, AV_UR_ADDR.ATTRIBUTE_VALUE as URR_ADDR, AV_UR_INN.ATTRIBUTE_VALUE as INN, AV_UR_KPP.ATTRIBUTE_VALUE as KPP, '2' as c4, '2' as c5, '1' as c6, '643' as c7, CAST(lpad(EXTRACT(DAY FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM A.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM A.CREATE_DATE) || ' 00:00:00' as CREATE_DATE, IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') end_date, '1' as c1, '46' as c2, '0' as c3 from abonents A left join ATTRIBUTE_VALUES AV_UR_ADDR on AV_UR_ADDR.ABONENT_ID=A.ID and AV_UR_ADDR.ATTRIBUTE_ID=25 left join ATTRIBUTE_VALUES AV_UR_INN on AV_UR_INN.ABONENT_ID=A.ID and AV_UR_INN.ATTRIBUTE_ID=4 left join ATTRIBUTE_VALUES AV_UR_KPP on AV_UR_KPP.ABONENT_ID=A.ID and AV_UR_KPP.ATTRIBUTE_ID=5 left join users on users.abonent_id=A.id left join PHONE_PULL_CHACHE PPC on PPC.id=users.phone left join abonents_history ah on ah.abonents_id=a.id where COALESCE(A.IS_FOLDER, 0)=0 and A.CATEGORY_ID=1 and PPC.PHONE like '7496%' and a.company<>':Абонент|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$' and a.main=1 {code} h6. Шаблон отчёта {code} {% 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. Потребленный трафик суммарно по всем абонентам с возможностью выбора периода {code}SELECT FIRST 1 |
... |
where extract (DAY from ab.activate_date) >0 {code} |
# h5. Отчёт по телефонии по звонкам, которые были выполнены на конкретный пул телефонных номеров за указанную дату по конкретному абоненту. |
{code} |
select vl.id as "ID звонка", s_time as "Начало звонка", e_time as "Конец звонка", (select first 1 a.name from abonents a where id=vl.abonent_id) as "Абонент", src as "Номер абон.", dst as "Исх. номер", duration as "Длит.", (select first 1 d.name from voip_direction d where d.id=vl.DIRECTION_ID) as "Направление", round(cast(BILL_SUM as numeric(18,2)) / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,2)), 2) as "Сумма", chan as "Канал" from voip_log VL where billed=1 and (error_code is null or error_code=0) and cast(e_time as date) between cast(':2 С даты|date$' as date) and cast(':3 По дату(включительно)|date$' as date) and duration > 0 and vl.abonent_id=:1Абонент|select[Abonents,is_folder=0]$ AND (vl.DST between (':4 Начало пула$') and (':5 Конец пула$')) {code} |
# h5. Отчёт по RADIUS-сессиям выбранного абонента: начало, конец, длительность, причина завершения (если завершена), IP. {code}select |
... |
and counters.YEAR_NUMBER=(':Год$')) {code} |
# h5. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 496. В данном примере: #* when c.usluga_id=93 then 10 #* when c.usluga_id=92 then 12 #* when c.usluga_id=91 then 14 #* 93 - Id услуги "Международная связь" #* 92 - Id услуги "Междугородная связь" #* 91 - Id услуги "Внутризоновая связь" h6. SQL запрос: {code} select 'Cnst1' as "Аг. договор", A.CONTRACT_NUMBER as "Аб. договор", fo.number as "Фактура", fo.number as "Акт", fo.period_end_date as "Дата счета", dateadd(-16 day to dateadd(month, 1, fo.period_end_date)) as "Срок оплаты", '0' as "Код валюты", '2' as "Код НДС", case when c.usluga_id=93 then 10 when c.usluga_id=92 then 12 when c.usluga_id=91 then 14 end as "Код услуги", fo.period_end_date as "Дата", cast(c.summ as numeric(18,2)) as "Сумма", cast(c.v as numeric(18,2)) as "Минуты", '46' as "C5", '0' as "C6" from abonents a join counters c on c.abonent_id=a.id and c.closed=1 and c.usluga_id in (91,92,93) and c.act_id is not null join finance_operations fo on fo.op_id = c.act_id where a.deleted=0 and a.is_folder=0 and (extract(year from period_end_date)=(':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$') and extract(month from period_end_date)=(':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')) and 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. Шаблон отчёта: {code} {% 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. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения. {code} |
... |
from tarif t {code} |
# h5. Отчёт для анализа настройки агентской схемы VoIP, отражает текущую настройку транков в операторских услугах телефонии {code}select a.name FIO, coalesce(vt.name,'---') DIRECTION, u.name USLUGA, uu.voip_operator_match_format TRUNK from users_usluga uu join usluga u on uu.usluga_id=u.id join abonents a on uu.abonent_id=a.id left join V_TYPE vt on u.voip_v_type_id=vt.id where uu.deleted=0 and a.deleted=0 and u.system_type=5 order by a.id,vt.name{code} # h5. Отчёт для анализа настройки агентской схемы VoIP, подобный предыдущему, но отражает дополнительные данные и удалённые услуги {code} select distinct a.id "ID", a.name "Оператор", coalesce(u.name,'Нет услуги') "Услуга", 'UU_id=' || uu.id || ', del=' || uu.deleted || ', U_id=' || u.id "Метаданные", coalesce(vt.name,'') "Тип трафика", iif(uu.id is not null,voip_operator_match_format,'отсутствуют') "Транки" from abonents a join users_usluga uu on a.id=uu.abonent_id and voip_operator_match_format<>'' left join usluga u on uu.usluga_id=u.id left join v_type vt on u.voip_v_type_id=vt.id where a.category_id=2 and a.id>2 and uu.deleted=:2-Показывать удалённые?|choices[0^]Нет^[1^]Да]$ order by :1-Сортировка по:|choices[2^]Наименованию^[6^]Транкам^[5^]Типу трафика]$ {code} |
# h5. Отчёт по неплательщикам за месяц {code} |
... |
order by ir.ip {code} |
|
h1. Решение проблем |
... |