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

Skip to end of metadata
Go to start of metadata
Вы просматриваете старую версию данной страницы. Смотрите текущую версию. Сравнить с текущим  |   просмотр истории страницы

Для того, чтобы создать отчет, необходимо зайти в веб-интерфейс администратора Carbon Billing 5, раздел Отчеты и выбрать Конструктор отчетов. В открывшемся окне можно будет выбрать одну из вкладок, в которой будут создаваться отчеты. Для создания отчета необходимо выбрать папку и нажать на кнопку Добавить.

Есть готовые отчеты, также можно создать свой отчет.

Отличительная особенность отчета состоит в том, что можно указывать параметры. Если в настройках указать параметр (prm), то при нажатии запустить отчет, можно будет задать параметр. Задать название отчета и текст запроса, который вы можете уточнить у тех.поддержки (должен начинаться со слова "select").

Данные в отчете можно выгрузить в csv.

Параметры полей формы

Переменные, отображаемые в виде формы:

':title|type[params]$'
  • : - указывает, что это строка с переменной
  • title - заголовок, отображаемый на форме
  • | - разделитель заголовка и типа
  • type[params] - тип с параметрами:
    • date - дата, без параметров, отображает на форме календарь выбора даты
      BILL_DATE between ':Начало|date$' and ':Конец|date$'
    • choices - список
      Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$'
      Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'
    • select - список, построенный на основе модели, подобно работе API, параметры содержат модель (обязательно) и фильтры
      a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
      h.id = ':Заявка|select[HDSK]$'
  • $ - флаг отмечает обязательную переменную

Выполнение отчёта из консоли

Для того, чтобы запустить выполнение отчета из консоли, необходимо:
1) Перейти в контейнер биллинга

chroot /app/asr_billing/

2) Запустить скрипт

python /usr/lib/python2.6/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105

Где 105 - id отчёта.
Информация, выведенная на экран, будет иметь кодировку cp1251

Описание полей

К большенсту полей БД существуют описания. Описание всех полей можно получить выполнив команду sqlexeс "show comments", например:

sqlexec "show comments" | head -n 5
COMMENT ON DOMAIN       RAD_ATTRIB IS Radius-attribute;
COMMENT ON    COLUMN    ABONENTS.ID IS №;
COMMENT ON    COLUMN    ABONENTS.NAME IS Название/ФИО;
COMMENT ON    COLUMN    ABONENTS.PARENT_ID IS Группа;
COMMENT ON    COLUMN    ABONENTS.CONTRACT_NUMBER IS Номер договора;

Поля, отраженные в веб-интерфейсе (настройки абонента, тарифов, услуг и тд) как правило имею описание согласно названию. На примере настроек тарифа, вкладка "Опции", параметры Обещанного платежа:

sqlexec "show comments" | grep "Разрешать подключение обещанного платежа только один раз в текущий месяц"
COMMENT ON    COLUMN    TARIF.PROMISE_PAY_ONLY_THIS_MONTH IS Разрешать подключение обещанного платежа только один раз в текущий месяц;

Примеры:

  1. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона
    select UR.user_id as ID,
           U.identify as FIO,
           AV.attribute_value as TELEFON
    from users_radiusauth UR
    left join USERS U on U.id=UR.user_id
    left join ATTRIBUTE_VALUES AV on AV.user_id=UR.user_id and AV.ATTRIBUTE_ID=1
    where UF_IP2STRING(UR.NAS_IP_ADDRESS)='172.16.0.6'
    order by U.identify
    
  2. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата
    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
    
  3. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, IP коммутатор, Номер порта, Номер vlan) с группировкой по номеру коммутатора
    select
    u.identify as FIO,
    sw.name as NAME,
    uf_ip2string(sw.ip) as SWITCH_IP,
    sp.num as PORT,
    sp.vlan as VLAN
    from switch_ports sp
    join users u on u.id=sp.user_id
    join switch sw on sw.id=sp.switch_id
    order by sp.switch_id
    
  4. Общее количество абонентов по группам
    select
    name as "Группа",
    (select count(1) from abonents where parent_id=grp.id) as "Количество абонентов"
    from abonents grp
    where is_folder=1
    
  5. Средняя выручка(в расчёте на одного абонента) за предыдущий и текущий месяц или с возможностью выбора периода
    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
    
  6. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента)
    select A.CONTRACT_NUMBER,
           A.NAME,
           T.NAME,
           ABSP.CEIL_IN,
           (select AV.ATTRIBUTE_VALUE
            from ATTRIBUTE_VALUES AV
            where AV.ATTRIBUTE_ID = 14
                  and AV.ABONENT_ID = A.ID),
           (select AV.ATTRIBUTE_VALUE
            from ATTRIBUTE_VALUES AV
            where AV.ATTRIBUTE_ID = 13
                  and AV.ABONENT_ID = A.ID),
           (select AV.ATTRIBUTE_VALUE
            from ATTRIBUTE_VALUES AV
            where AV.ATTRIBUTE_ID = 16
                  and AV.ABONENT_ID = A.ID),
           (select AV.ATTRIBUTE_VALUE
            from ATTRIBUTE_VALUES AV
            where AV.ATTRIBUTE_ID = 17
                  and AV.ABONENT_ID = A.ID),
           H.CITY,
           H.STREET,
           H.S_NUMBER,
           H.S_LITER,
           A.A_HOME_NUMBER,
           A.SMS
    from ABONENTS A
    left join TARIF T on T.ID = A.TARIF_ID
    left join ABONENTS_SPEED ABSP on ABSP.ABONENT_ID = A.ID
    left join HOMES H on H.ID = A.HOME_ID
    where A.DELETED != 1
          and A.IS_FOLDER != 1
    order by A.NAME
    
  7. Отчет по списаниям по абонентам в формате (число, id группы, название группы, сумма и кол-во абонентов, по которым были списания) и последней строкой сумма за день.
    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
    
  8. Отчет по должникам для отключения КТВ (Улица, Дом, Подъезд, Квартира, ФИО, Тариф) игнорируя ручной статус "Отключен"
    select
    h.street as "Улица",
    h.s_number as "Номер дома",
    a.home_entrance as "Номер подъезда",
    a.A_HOME_NUMBER as "Номер квартиры",
    a.name as "ФИО",
    t.name as "Тариф"
    from abonents_block  ab
    left join abonents a on ab.abonent_id=a.id
    left join homes h  on  a.home_id=h.id
    left join tarif t on a.tarif_id=t.id
    left join objects_status os on a.id=os.object_id
    where ab.b_negbal=1 and a.tarif_id in (21,22,23,24) and (os.status<>5 or os.status is null)
    group by 1,2,3,4,5,6
    order by 6
    
  9. Отчет по свободным IP адресам
    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
    
  10. Кто платит через Юнителлер?
    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
    
  11. Количество услуг из ЛК за месяц по абонентам
    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$'
    
  12. Прибыль с комиссии за обещанный платеж (все периоды)
    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
    
  13. Прибыль с комиссии за обещанный платеж (по периодам)
    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
    
  14. Просмотр пользователей в онлайне с выводом времени онлайна
    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
    

    Пример 71. Абоненты, которые в начале следующего месяца будут заблокированы (при условии что лимит выставлен отричательным числом, в противном случае минус перед ним нужно убрать)

select distinct A.CONTRACT_NUMBER as "номер договора",
                A.NAME as "ФИО",
                H.STREET as "Улица",
                H.S_NUMBER as "Номер дома",
                A.HOME_ENTRANCE as "Номер подъезда",
                A.A_HOME_NUMBER as "Номер квартиры",
                A.SMS as "номер для смс",
                T.NAME as "Тариф"
from ABONENTS A
left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID = T.ID
where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE
                                      from VPN_CONST
                                      where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE
                                                                             from VPN_CONST
                                                                             where CONST_ID = 1))
      and A.IS_FOLDER = 0
      and (select count(1)
           from ABONENTS_BLOCK AB
           where AB.ABONENT_ID = A.ID
                 and (AB.B_NEGBAL != 1
                 or AB.B_OWN != 1
                 or AB.B_ADMIN != 1
                 or AB.B_SYS != 1)) = 0
union
select '#ВСЕГО',
       '',
       '',
       '',
       '',
       '',
       '',
       count(A.ID)
from ABONENTS A
left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE
                                      from VPN_CONST
                                      where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE
                                                                             from VPN_CONST
                                                                             where CONST_ID = 1))
      and A.IS_FOLDER = 0
      and (select count(1)
           from ABONENTS_BLOCK AB
           where AB.ABONENT_ID = A.ID
                 and (AB.B_NEGBAL != 1
                 or AB.B_OWN != 1
                 or AB.B_ADMIN != 1
                 or AB.B_SYS != 1)) = 0      

Пример 72. Список IP адрес и номер договора всех абонентов. у которых в качестве nas_ip указан адрес 192.168.1.2:

select 
uf_ip2string(u.ip) as "IP",
a.CONTRACT_NUMBER as "Номер договора"
from users u 
left join abonents a on u.abonent_id=a.id
left join nas n on u.nas_id=n.id
where uf_ip2string(n.ip)='192.168.1.2' and a.deleted=0

Пример 73. Список абонентов, взявших больше одного обещанного платежа:

select distinct AB.NAME as "ФИО",
                AB.CONTRACT_NUMBER as "Номер договора",
                (select count(1)
                 from USERS_USLUGA UUA
                 left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
                 where (UUA.USLUGA_ID = 1247
                       or UUA.USLUGA_ID = 1248
                       or UUA.USLUGA_ID = 1249)
                       and UUA.DELETED = 0
                       and ABA.ID = AB.ID) as "Кол-во обещанных платежей"
from USERS_USLUGA UU
left join ABONENTS AB on UU.ABONENT_ID = AB.ID
where UU.USLUGA_ID = 1247
      and UU.DELETED = 0
      and (select count(1)
           from USERS_USLUGA UUA
           left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
           where (UUA.USLUGA_ID = 1247
                 or UUA.USLUGA_ID = 1248
                 or UUA.USLUGA_ID = 1249)
                 and UUA.DELETED = 0
                 and ABA.ID = AB.ID) > 1
order by 3

Пример 74. Отчет по абонентам со статусом "подключен":

select
    count(distinct ab.id) as "Кол-во абонентов"
from
    abonents ab left join abonents_block abb
    on ab.id = abb.abonent_id
where
    abb.id is null and
    ab.deleted = 0 and
    ab.is_folder = 0
and exists(select 1 from users where nas_id=70 and abonent_id=ab.id and ip is not null)

Пример 75. Отчет по абонентам со статусом "не подключен":

select
    count(distinct ab.id) as "Кол-во абонентов"
from
    abonents ab inner join abonents_block abb
    on ab.id = abb.abonent_id
where
    ab.deleted = 0 and
    ab.is_folder = 0

Пример 76. Отчет по абонентам у которых сегодня списалась абонентская плата:

select
    count(distinct ab.id) as "Кол-во абонентов"
from
    arch_account_stack aas left join abonents ab
    on aas.abonent_id = ab.id
where
    credit > 0 and
    cast(bill_date as date) = current_date

Пример 77. Отчет выводящий абонентов должников и в статусе "отключен" по каждому дому в отдельности:

select CITY,
       STREET,
       SNUMBER,
       AHOMENUMBER,
       FIO,
       PHONE,
       TNAME,
       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 (select H.CITY as "CITY",
             H.STREET as "STREET",
             H.S_NUMBER as "SNUMBER",
             CAST(A.A_HOME_NUMBER AS INTEGER) as "AHOMENUMBER",
             A.NAME as "FIO",
             A.SMS as "PHONE",
             T.NAME as "TNAME",
             A.ACCOUNT_ID as "AAID"
      from ABONENTS A
      left join ABONENTS_BLOCK AB on AB.ABONENT_ID = A.ID
      left join HOMES H on A.HOME_ID = H.ID
      left join TARIF T on A.TARIF_ID = T.ID
      where
            A.DELETED != 1
            and H.STREET like ':Улица$'
            and H.S_NUMBER like ':Дом$'
      group by 1, 2, 3, 4, 5, 6, 7, 8)
left join ADMIN_ACCOUNTS AA on AAID = AA.ID
where round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / 10000000000.00, 2) <= :Баланс ниже$
order by 1, 2, 3, 4, 5

Пример 78. Отчет "абоненты с положительным балансом":

select
a.contract_number as "Номер договора",
a.name "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
inner join admin_accounts aa
on a.account_id = aa.id
left join homes h
on a.home_id = h.id
where
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 > 0
order by (aa.ostatok + aa.debit - aa.credit)/10000000000.00 desc

Пример 79. Отчет "Абоненты по услуге":

select
contract_number as "Номер договора",
name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
uu.enable_date as "Дата подключения",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on  aa.id = a.account_id
left join homes h on h.id = a.home_id
inner join users_usluga uu on uu.abonent_id = a.id
where uu.usluga_id = :ID Услуги$

Пример 80. Отчет "Абоненты по тарифу":

select
a.contract_number as "Номер договора",
a.name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on  aa.id = a.account_id
left join homes h on h.id = a.home_id
where a.tarif_id = :ID Тарифа$

Пример 81. Отчет "Пресса", цель отчета раздать списки почтальонам, кому из абонентов приносить газету.

select
    h.s_number as "Дом",
    list(a.a_home_number) as "Квартиры"
from
    abonents a
        left join abonents_block ab
            on a.id = ab.abonent_id
        inner join homes h
            on a.home_id = h.id
where
    h.street = ':Улица$'
        and
    a.is_folder = 0
        and
    (ab.abonent_id is null
        or
    (ab.b_negbal = 1
        and
    (current_timestamp - ab.b_date) < 90))
group by h.s_number
Пример 82. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам.
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
Пример 83. Отчет по должникам с возможностью выбора тарифа. В отчете: номер договора, ФИО, телефон абонента, адрес, тариф, статус, дата последнего платежа, баланс.
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
Пример 84. Отчет должники по постоплате с разделением по физическим и юридическим лицам. Отчет содержит: номер договора, ФИО, адрес, дата последнего платежа, сумма долга (рекоммендованный платеж по постоплате). 
select first 1
     cast('******************************************' as varchar(100) ) as "№ договора",
     cast('*****ФИЗИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "Наименование",
     cast('******************************************' as varchar(100)) as "Адрес" ,
     cast('******************************************' as varchar(100) ) as "Посл. платеж",
     cast('*****************************' as varchar(100)) as "Сумма долга"
     from send_type
union all

select
     AB.CONTRACT_NUMBER as "№ договора",
     AB.NAME as "ФИО",
     H.STREET || ', д. ' ||  H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER  as "Адрес",
     (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
    (select  round(
                           sum(
                                  cast(
                                         (CNT.SUMM) as float
                                         )
                                   )
                            , 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
             where cnt.month_number=extract(month FROM cast('NOW' as date))-1
             and cnt.year_number=extract(year FROM cast('NOW' as date))
             and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0
and AB.COMPANY = 0
and  (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0


union all

select first 1
     cast('******************************************' as varchar(100) ) as "№ договора",
     cast('*****ЮРИДИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "ФИО",
     cast('******************************************' as varchar(100)) as "Адрес" ,
     cast('******************************************' as varchar(100) ) as "Посл. платеж",
     cast('*****************************' as varchar(100)) as "Баланс"
     from send_type
union all

select
     AB.CONTRACT_NUMBER as "№ договора",
     AB.NAME as "ФИО",
     H.STREET || ', д. ' ||  H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER  as "Адрес",
     (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
    (select  round(
                           sum(
                                  cast(
                                         (CNT.SUMM) as float
                                         )
                                   )
                            , 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
             where cnt.month_number=extract(month FROM cast('NOW' as date))-1
             and cnt.year_number=extract(year FROM cast('NOW' as date))
             and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0
and AB.COMPANY = 1
and  (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0
Пример 85. Отчет по абонентам, у которых должна произойти смена тарифа.  
select A.ID as "ID абонента",
          A.NAME as "ФИО",
          A.TARIF_ID as "ID тарифа",
          T.NAME as "Тариф",
          A.TARIF_NEXT_ID as "ID нового тарифа"
       from ABONENTS A
       left join TARIF T on A.TARIF_ID=T.ID
where A.TARIF_NEXT_DATE between ':День смены тарифа|date$' and ':День смены тарифа|date$ 23:59:59 '
Пример 86. Сумма платежей через платежные системы "Жилищное управление", "Yandex" за каждый день в периоде между Датой1 и Датой2. 
with PERIOD as (select
         distinct cast(PLS.DATE_CREATE as date) as DAT
    from PAY_LOG PLS
where PLS.DATE_CREATE between ':Дата1|date$' and ':Дата2|date$ 23:59:59')

select
    PERIOD.DAT as "Date",
    (
    	select  COALESCE(sum(P.SUMMA_IN),0)
		from PAY_LOG P
		where P.MSG_OUT containing 'ACCEPTED'
		        and P.ACT_IN containing 'PAY'
		        and P.PAY_OPERATOR containing upper('Жилищное упр')
		        and P.DATE_CREATE containing DAT
    )  as "Жилищное упр",
    (
    	select  COALESCE(sum(P.SUMMA_IN),0)
		from PAY_LOG P
		where P.MSG_OUT containing 'ACCEPTED'
		        and P.ACT_IN containing 'PAY'
		        and P.PAY_OPERATOR containing upper('Yandex.Kassa')
		        and P.DATE_CREATE containing DAT
    )  as "Яндекс"
from
    PAY_LOG PL
        inner join PERIOD
            on PL.DATE_CREATE containing DAT
where PL.MSG_OUT containing 'ACCEPTED'
        and PL.ACT_IN containing 'PAY'

group by
    PERIOD.DAT
Пример 87. Отчет "Выставленные счета физическим лицам". В отчете: сумма в счете, ФИО абонента, описание финансовой операции.
select ((fo.op_summa)/(select const_value from vpn_const where const_id=1)) as "Сумма",
           ab.name as "ФИО",
           ft.op_name || ', ' || ft.op_descr
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=0
           and fo.op_date between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
Пример 88. "Отчёт о превышении лимита предоплаченного трафика по юридическим лицам с возможностью выбора месяца - года"
SQL запрос
select distinct
ab.name,
tarif.name,
usluga.max_mb_in_m,
round(usluga.in_price  / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2),
round(traf_counters.v_in / cast((1048576) as numeric(18,5)), 2),
round(traf_counters.v_out / cast((1048576) as numeric(18,5)), 2),
usluga.name,
usluga.id,
tarif.id,
ab.id
from abonents as ab
left join tarif on ab.tarif_id=tarif.id
join tarif_users_usluga as tuu on tuu.tarif_id = tarif.id
join usluga on tuu.usluga_id=usluga.id
join users_usluga as uu on uu.abonent_id = ab.id and uu.usluga_Id = usluga.id and uu.tarif_id = tarif.id
join traf_counters on ab.id=traf_counters.abonent_id
where traf_counters.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and traf_counters.year_number LIKE upper(':Год$')  and ab.company = 1
Шаблон отчёта
{% 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;">
                <tr>
                   <th>ФИО</th>
                   <th>Тариф</th>
                   <th>Услуга</th>
                   <th>Объем предоплаченного трафика</th>
                   <th>Цена за МБ</th>
                   <th>Объем использованного входящего</th>
                   <th>Объем использованного исходящего</th>
                   <th>Объем трафика сверх предоплаченного входящего</th>
                </tr>
                {% for row in data %}
                       {% if row.2 and row.2 < row.4 %}
                    <tr>
                       <td><a href="/admin/Abonents/Abonents/{{ row.9 }}/" target="_blank">{{ row.0 }}</td>
                       <td><a href="/admin/tarifs/Tarif/{{ row.8 }}/" target="_blank">{{ row.1 }}</a></td>
                       <td><a href="/admin/tarifs/Usluga/{{ row.7 }}/" target="_blank">{{ row.6 }}</a></td>
                       <td>{% if row.2 %} {{ row.2|floatformat:2 }} Мб {% else %} --- {% endif %}</td>
                       <td>{{ row.3 }} руб.</td>
                       <td>{{ row.4|floatformat:2 }} Мб</td>
                       <td>{{ row.5|floatformat:2 }} Мб</td>
                       <td>{{ row.4|sub:row.2 }} Мб</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 %}
Пример 89. "Отчёт о динамике приходов и актов за заданный период времени"
SQL запрос
select
fo.SYSTEM_DATE as "Дата",
u.login as "Логин",
a.contract_number as "Номер договора",
a.name as "ФИО",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
round(fo.balance_buh / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс",
a.tarif_id as "ID тарифа",
t.name,
ft.op_name
from abonents as a join users as u on a.id=u.abonent_id
join finance_operations as fo on fo.ABONENT_ID=a.id
join fin_types as ft on fo.op_type = ft.type_id
join tarif as t on t.id = a.tarif_id
where
fo.SYSTEM_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and
fo.op_type in (1,2)
Шаблон отчёта
{% 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>
<th>ЛОГИН</th>
<th>НОМЕР ДОГОВОРА</th>
<th>ФИО</th>
<th>СУММА</th>
<th>ТИП</th>
<th>БАЛАНС</th>
<th>ТАРИФ</th>
                    </tr>
                    {% for row in data %}
                        <tr>
                                <td class="date">{{ row.0|date:"d.m.Y G:i:s" }}</td>
                                <td>{{ row.1 }}</td>
                                <td class="contract">{{ row.2 }}</td>
                                <td>{{ row.3 }}</td>
                                <td class="fio">{{ row.4 }}</td>
                                <td>{{ row.8 }}</td>
                                <td>{{ row.5 }}</td>
<td><a href="/admin/tarifs/Tarif/{{ row.6 }}/" target="_blank">{{ row.7 }}</a></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 %}
Пример 90. "Отчёт по пользователям с добровольной блокировкой"
SQL запрос
select a.name as "ФИО",
a.CONTRACT_NUMBER as "Номер договора",
uf_ip2string(ip) as "IP",
a.own_disabled_start as "Дата начала",
a.own_disabled_end as "Дата конца"
from abonents as a
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
Пример 91. "Список пользователей с сортировкой по ФИО (ФИО, логин, IP, Город, Улица, Дом)"
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
Пример 92. Отчёт в формате "Логин- Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения. По текущей дате"
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
Пример 93. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг".
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$')
Пример 94. Отчёт по должникам по телефонии в формате " № - Папка - ФИО - Телефонный номер - Дата последней оплаты - Сумма долга"
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
Пример 95. Отчёт по скачанному трафику за период.
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
Пример 96. Отчёт по платежам для групп абонентов.
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 "Сумма прихода",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and a.parent_id=(':ID папки$')
Пример 97. Отчёт для платежной системы "Город", выводящий в строку "ФИО;Адрес;Лицевой счет;Текущий остаток;';;;;'" и выводящий в Шапке отчёта :

#FILESUM - Сумму всех остатков в данном отчёте
#TYPE 7
#SERVICE 10240
по всем физ.лицам

select '#FILESUM' || ' ' || round(sum(aa.ostatok / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number  || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round(aa.ostatok / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0

Тот же отчет, выводящий текущий баланс.

select '#FILESUM' || ' ' || round(sum((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
and parent_id not in ( '244', '2', '4' , '1499')
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number  || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
Пример 98. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number)
{% 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 %}
Пример 99. Отчёт по определенной услуге. Абонент, логин, баланс, конец действия услуги.
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
Пример 100. Отчёт по абонентам в формате "ФИО ЛОГИН IP MAC ТЕЛЕФОН АДРЕС ТАРИФ БАЛАНС"
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
Пример 101. Отчёт, выводящий сумму списаний, взятых из выставленных актов, за определенный период по всем абонентам.
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$')
Пример 102. Отчет для вывода рекомендуемого ежемесячно платежа, стоимости тарифа по всем абонентам.
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
Пример 103. Отчёт по платежам через веб - кассу(По операторам)
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 = ':Администратор$'
Пример 104. Поле "Не отключать при превышении порога", ID, ФИО.
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
Пример 105. Учёт заключенных договоров за период
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$'

Пример 106. Телефония. Количество звонков, ожидающих перерасчет.

select count(1) from voip_log where recalc=1
Пример 107. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)"
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
Пример 108. Отчёт по приходам за заданный период времени
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$')
Пример 109. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%"
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$')
Пример 110. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период.
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(counters.SUMM), 2) as "Сумма"
from counters 
left join usluga u on u.id=counters.usluga_id
where S_DATE between (':1 Дата|date$') and (':2 Дата|date$')
group by u.name, u.SUMMA
Пример 111. Сформированные акты/счета по юридическим лицам.
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
Пример 112. Отчёт по юридическим лицам, выводящий ФИО Телефон Адрес Р/С ИНН КПП
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
Пример 113. Отчёт прибыль по плате за подключение нескольких услуг.
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
Пример 114. Отчёт выводит абонентов, последнее подключение которых было раньше, чем указанная дата.
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$')
Пример 115. Отчёт выводит информацию по абонентам с определенным NAS'ом.
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$')
Пример 116. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус.
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
Пример 117. Поиск IP-адреса в истории изменения учетных записей (история выдачи ip-адреса)
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-адрес$'
Пример 118. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс"
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
Пример 119. Отчёт по платившим абонентам в формате "Номер договора - ФИО - Группа - Дата и время операции - Описание - Сумма" с выводом итоговой суммы и возможностью выбора платежей по конкретной папке.
select AB.CONTRACT_NUMBER as "Номер договора",
       AB.NAME as "ФИО",
       (select name from abonents where abonents.id=ab.parent_id) as "Группа",
       FO.SYSTEM_DATE as "Дата",
       FO.DESCR as "Описание",
       cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                     from VPN_CONST
                                                     where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
      and ab.parent_id=(':ID группы$')
      and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
      and (AB.DELETED = 0
      or AB.DELETED is null)
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, FO.SYSTEM_DATE, AB.PARENT_ID
union all
select '"Итого"',
       null,
       null,
       null,
       null,
       sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                           from VPN_CONST
                                                           where CONST_ID = 1) as SUMOP
      from FINANCE_OPERATIONS FO
      left join ABONENTS AB on FO.ABONENT_ID = AB.ID
      where OP_TYPE = 2
            and ab.parent_id=(':ID группы$')
            and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
            and (AB.DELETED = 0
            or AB.DELETED is null))
order by 4

Тот же отчёт по всем абонентам

select 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))
Пример 120. Отчёт для СОРМ3. Активированные пользователями дополнительные услуги (услуги, не включенные в тариф) services без учета системных обещанных платежей. Формат "логин";"номер договора";"идентификатор услуги";"дата и время подключения услуги";"дата и время отключения";""
select distinct
     '"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || UU.USLUGA_ID || '"' || ';'|| '"' || CAST(lpad(EXTRACT(DAY FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.ENABLE_DATE) || ' 00:00:00' || '"' || ';' || '"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.END_TIME) || ' ' || EXTRACT(HOUR FROM  UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""'
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_HISTORY UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and UU.ENABLE_DATE is not null
and UU.TARIF_ID is null
and UU.USLUGA_ID!=-4
and UU.USLUGA_ID!=-3
and A.PARENT_ID!=244
and A.DELETED=0
order by A.NAME

Тот же отчёт, который выводит только услуги подключенные за последний час

select distinct
     '"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || UU.USLUGA_ID || '"' || ';'|| '"' || CAST(lpad(EXTRACT(DAY FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.ENABLE_DATE) || ' 00:00:00' || '"' || ';' || '"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.END_TIME) || ' ' || EXTRACT(HOUR FROM  UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""'
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_HISTORY UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and UU.ENABLE_DATE is not null
and UU.TARIF_ID is null
and UU.USLUGA_ID!=-4
and UU.USLUGA_ID!=-3
and A.PARENT_ID!=244
and A.DELETED=0
and UU.TIME_CHANGED between  (current_timestamp - 1/24) and current_timestamp
order by A.NAME
Отчёт 121. Отчёт для СОРМ3. Пополнение баланса личного счета абонента обобщенная информация. Отчёт выводит информацию в виде "1";"идентификатор способа оплаты";"номер договора ";"статический IP-адрес";"дата и время пополнения баланса";"сумма";""

В данном отчёте идентификаторы способа оплаты зависят от описания платежа:
Описание Gorod - 1
Описание Paymaser - 2
Остальное - 3

select 
      '"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME)
 WHEN 'GOROD' THEN '1'
 WHEN 'PAYMASTER' THEN '2'
 ELSE '3'
 END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM FO.OP_DATE) || ' 00:00:00' || '"' || ';' || '"' || round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '"' || ';' || '""'
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
left join users u on u.abonent_id=a.id
where fo.OP_TYPE=2
and A.PARENT_ID!=244
and A.DELETED=0

Тот же отчёт, который выводит платежи, проводимые за последний час

select 
      '"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME)
 WHEN 'GOROD' THEN '1'
 WHEN 'PAYMASTER' THEN '2'
 ELSE '3'
 END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM FO.OP_DATE) || ' 00:00:00' || '"' || ';' || '"' || round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '"' || ';' || '""'
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
left join users u on u.abonent_id=a.id
where fo.OP_TYPE=2
and A.PARENT_ID!=244
and A.DELETED=0
and DATEADD(-1 hour to current_timestamp) - FO.SYSTEM_DATE < 1/24
Пример 122. Отчёт для СОРМ3. Отчёт выводит дополнительные услуги в формате "идентификатор услуги ";"название/обозначение в Вашей системе";"дата создания услуги в биллинге";"";"описание";

Отчёт

select distinct
   '"' || USL.ID || '"' ||  ';' || '"' || USL.NAME || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM  (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)) || ' 00:00:00' || '"' || ';' || '""' || ';' || '"' || USL.NAME || '"'
from USLUGA USL
left join  USERS_USLUGA_HISTORY UU on UU.USLUGA_ID=USL.ID
and USL.SYSTEM_TYPE!=8
and USL.SYSTEM_TYPE!=9
and USL.SYSTEM_TYPE!=13
and USL.SYSTEM_TYPE is not null
order by USL.ID

Шаблон отчёта

{% 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>
                        {% for field in field_desc %}
                            <th>{{ field|get_zero }}</th>
                        {% endfor %}
                    </tr>
                    {% for row in data %}
                        <tr>
                            {% for cell in row %}
                               {% if cell %}
                                <td>{{ cell }}</td>
                                {% endif %}
                            {% 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 %}

Тот же отчёт, выводящий услуги, созданные за прошедшие сутки:

select distinct
   '"' || USL.ID || '"' ||  ';' || '"' || USL.NAME || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM  (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)) || ' 00:00:00' || '"' || ';' || '""' || ';' || '"' || USL.NAME || '"'
from USLUGA USL
left join  USERS_USLUGA_HISTORY UU on UU.USLUGA_ID=USL.ID
left join USLUGA_HISTORY as uh1 on UH1.USLUGA_ID=USL.ID
WHERE
USL.SYSTEM_TYPE!=8
and USL.SYSTEM_TYPE!=9
and USL.SYSTEM_TYPE!=13
and USL.SYSTEM_TYPE is not null
and DATEADD(-1 day to current_timestamp) - (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id) < 1
order by USL.ID
Пример 123. Отчет выводящий информацию из карточки абонента с текущим статусом; колонки административной и добровольной блокировками заполняются только в случае активной блокировки; в последней колонке пишется дата, до которой действительна добровольная блокировка
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 
Пример 124. Отчет выводит данные по операциям по всем абонентам за выбранный период
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
Пример 125. Отчет выводит абонентов не имеющих операции приход до указанной даты. Поиск по конкретной папке.
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
Пример 126. Отчёт, выводящий список незаблокированных абонентов.
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
Пример 127. Отчёт по абонентам с блокировкой по отрицательному балансу.
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
Пример 128. Отчёт в формате "ФИО-номер договора-телефон-адрес"
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
Пример 129. Абоненты с учетными записями без mac-адреса. Формат: номер договора, ip учетной записи (если есть)
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 = '')
Пример 130. Отчёт о списаниях абонентской платы по папкам за определенный период.
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
Пример 131. Отчёт по телефонии. Структура данных информации об Абонентах по маске 7496
sql запрос
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
Шаблон отчёта
{% 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 %}
Пример 132. Потребленный трафик суммарно по всем абонентам с возможностью выбора периода
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
Пример 133. Поиск абонентов со скидкой, с выводом самой скидки, id абонента, id услуги и фио абонента.
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
Пример 134. Отчёт, который выводит списания абонентов с учетом и без учета НДС за выбранный период по каждому абоненту.
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
Пример 135. Отчёт по всем финансовым операциям по конкретному администратору за выбранный период времени.
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]$
Пример 136. Отчёт по исходящему телефонному трафику за выбранный период

Отчёт выводит сверку по потреблению услуги телефонного трафика с разделением по категориям и абонентам.

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
Пример 137. Отчёт по абонентам со статусом, балансом, датой последнего платежа и суммой последнего платежа.
select 
		ab.name as "ФИО", 
		ab.contract_number as "Договор", 
		s.name as "Статус", 
                (select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID and FO.OP_TYPE=2) as "Дата посл.",
       (select first 1 round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) from finance_operations FO1 where FO1.ABONENT_ID = AB.ID and FO1.OP_TYPE=2 order by fo1.op_date desc) as "Платеж",
		(aa.debit+aa.Ostatok-aa.credit)/10000000000.00 as "Баланс"
from abonents ab
left join objects_status os on 
		os.object_id=ab.id
left join status  s on 
		s.id = os.status
left join admin_accounts aa on 
		aa.id = ab.account_id
where  extract (DAY from ab.activate_date) >0
Пример 138. Отчёт по телефонии по звонкам, которые были выполнены на конкретный пул телефонных номеров за указанную дату по конкретному абоненту.
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 Конец пула$'))
Пример 139. Отчёт по RADIUS-сессиям выбранного абонента: начало, конец, длительность, причина завершения (если завершена), IP.
select
	START_TIME "Начало",
	END_TIME "Конец", 
	case 
		when coalesce(end_reason,'')='' then 'Не было Act-Session-Stop'
		when datediff(hour from start_time to end_TIME)>=1 then datediff(hour from start_time to end_TIME) || ' часов'
        when datediff(minute from start_time to end_TIME)<1 then 'Меньше минуты'
		when datediff(hour from start_time to end_TIME)<1 then datediff(minute from start_time to end_TIME) || ' минут'
	end as "Длительность",
	coalesce(END_REASON,'Отсутствует') as "Причина",
	uf_ip2string(ip) as "IP"
from
	radius_sessions
where
	abonent_id=':Абонент|select[Abonents]$'
order by 
	start_time desc
Пример 141. Отчёт для ФСБ в формате Оператор связи IP-адрес Логин Дата подключения Дата отключения Адрес подключения Абонент Дата рождения Документ (ИНН) Адрес регистрации (юридический) Контактное лицо Контактный номер Дополительня информаци
select '' from abonents 
UNION 
select distinct
     '"' || (select name from abonents where abonents.id=a.operator_id) || '";"' ||
     uf_ip2string(u.ip) || '";"' ||
     u.login || '";"' ||
     a.activate_date || '";"' ||
     coalesce(a.disabled_date,'') || '";"' ||
     coalesce( h.STREET || ', д. ' ||  h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER,'') || '";"' ||
     ' ' || '";"' ||
     a.name || '";"' ||
     coalesce(DR.ATTRIBUTE_VALUE,' ') || '";"' ||
     coalesce(INN.ATTRIBUTE_VALUE,' ') || '";"' ||
     coalesce(UADR.ATTRIBUTE_VALUE,' ') || '";"' ||
     coalesce(KLICO.ATTRIBUTE_VALUE,' ') || '";"' ||
     a.sms || '";"' ||
     ' '  || '"'
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join ATTRIBUTE_VALUES DR on DR.ABONENT_ID=A.ID and DR.ATTRIBUTE_ID=22 
left join ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25 
left join ATTRIBUTE_VALUES KLICO on KLICO.ABONENT_ID=A.ID and KLICO.ATTRIBUTE_ID=32
left join ATTRIBUTE_VALUES INN on INN.ABONENT_ID=A.ID and INN.ATTRIBUTE_ID=4
Пример 142. Отчёт по абонентам, у которых количество услуг больше 1, показывающий количество подключенных услуг и количество активных услуг
select abonent_id,
count(*) as "Кол-во услуг", 
(select count(*) from users_usluga uu1 where uu1.deleted=0 and uu1.enabled=1 and uu1.abonent_id=users_usluga.abonent_id having count(*)>1) as "Кол-во акт. услуг"
from users_usluga where deleted=0 group by abonent_id having count(*)>1
Пример 143. История выдачи телефонного номера для voip.
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=':Введите номер$')
Пример 144. История использования логина.
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(':Логин$')
Пример 145. Отчёт по услугам IP телевидения с возможностью выбора периода.

Выводит следующие поля:
1) Имя услуги
2) Цену услуги
3) Количество абонентов, у которых подключена конкретная услуга на текущий момент (в момент выполнения отчета)
4) Количество оплаченных дней за указанный период
5) Сумму, списанную по каждой услуге за указанный период
6) Итоговую сумму по всем услугам за указанный период
Примечание: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".

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 "Цена услуги",
(select count(*) from abonents a left join users_usluga uu on uu.abonent_id=a.id where uu.usluga_id=u.id) as "Кол-во абонентов",
(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) as "Дни",
round(sum(counters.SUMM), 2) as "Сумма"
from counters 
join usluga u on u.id=counters.usluga_id
and U.SYSTEM_TYPE=7
and u.id is not null
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$')
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 counters 
join usluga u on u.id=counters.usluga_id
and U.SYSTEM_TYPE=7
and u.id is not null
and S_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
Пример 146. Отчёт по абонентам, у которых подключены услуги IP телевидения.

Выводит следующие поля:
1) Имя абонента
2) Цена услуги
3) Название услуги
4) Сумму, списанную по конкретному абоненту за услугу за указанный период
5) Итоговую сумму по всем абонентам за услуги IP телевидения за указанный период
Примечание: В отчёт попадут только те услуги, у которых выбран тип: "IP телевидение".

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 S_DATE between (':1 Дата|date$') and (':2 Дата|date$')
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 S_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
Пример 147. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 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 услуги "Внутризоновая связь"

SQL запрос:
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
Шаблон отчёта:
{% 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 %}
Пример 148. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения.
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
Пример 149. Отчёт, который выводит все активные RADIUS-сессии.
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^]Убывания]$
Пример 150. Отчёт по распределению абонентов из определенной группы по тарифам, так же выводится количество в настоящий момент заблокированных и не заблокированных абонентов.
select 

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

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

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

from 
    (with recursive tree (id,tarif_id,is_folder) as (
        select id,tarif_id,is_folder from abonents where id in (':Группа|choices[1^]Все^[244^]Служебная группа^[4^]Корзина]$') 
        union all 
        select a.id,a.tarif_id,is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) 
    select id,tarif_id from tree where is_folder=0) ab
join
    tarif t on ab.tarif_id=t.id
group by 
    2  
order by 
    1 desc
Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.