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

Skip to end of metadata
Go to start of metadata

Описание конструктора отчетов

Конструктор отчетов позволяет делать запросы напрямую к БД. Описание ограничений, возможностей и синтаксиса используемого диалекта SQL Вы можете найти в документации Firebird 2.5.
Конструктор отчетов находится на вкладке "Отчеты" в основном меню биллинга



Отчеты разбиваются на группы. Не относящиеся ни к одной попадают в группу "Не определено". Группы настраиваются в справочниках:



По-умолчанию в биллинге идет более 60 отчетов. Вы так же можете создать свои или воспользоваться одним из примеров из данной статьи. Для добавления отчета нажмите кнопку "Добавить" в конструкторе. Ксли требуется выберите категорию, В поле "SQL запрос" вставьте текст отчета и сохраните.


Результат выполнения отчета можно отобразить браузере или выгрузить:

  • В формате DBF
  • В CSV с разделением "," (запятая)
  • В Excel (xlsx)

Для выполнения отчета выберите формат отображения и нажмите кнопку "Выполнить запрос"

Фильтры данных (параметры полей формы)

В отчётах можно добавить фильтры - выбор информации, например счетов и актов, только за выбранные даты или период дат, отфильтровать по абонентам и тд.

Фильтры подставляют указанные данные в тело отчёта и потом он выполняется.

Фильтры указываются примерно по такой схеме:

':title|type[params]$'
  • : - указывает, что это строка с переменной
  • title - заголовок, отображаемый на форме
  • | - разделитель заголовка и типа
  • type[params] - тип с параметрами
  • $ - флаг отмечает обязательную переменную, он обязателен для всех фильтров

Ниже они описаны подробно.

Выбор дат

date - дата, указывается без параметров, отображает на форме календарь выбора даты

Прмер:

BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'

Выбор периодов

monthchoice - отобразит список периодов в формате "ММ.ГГГГ месяц_прописью"

Примеры:

  • 12 последних месяцев
    ':Дата Список месяцев|monthchoice|$'
  • 3 последних месяца
    ':Дата Список месяцев|monthchoice|3$'
  • min и max по полю period_end_date модели financeoperations (можно ещё фильтры вписать, так же как для опции select)
    ':Дата Список месяцев|monthchoice|period_end_date|select[financeoperations]$'
  • min и max по полю period_end_date модели financeoperations с фильтрами: только не сторнированные акты
    ':Дата Список месяцев|monthchoice|period_end_date|select[financeoperations,op_type=2,storno=0]$'

Список вариантов

choices - список, параметры - это элементы списка

Примеры:

Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$'
owner_id in (:Администратор|choices[33^]root^[1005^]Василий^[1007,1005^]Василий или Михаил]$)
Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'

Список вариантов из справочников биллинга

select - список, построенный на основе модели, подобно работе API, параметры содержат модель (обязательно) и фильтры

Примеры:

a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
h.id = ':Заявка|select[HDSK]$'

Строка для ввода произвольных данных:

Отсутстие параметров предложет ввести произвольные данные.

':Введите число$'

Выполнение отчёта из консоли скриптом make_reports.pyc

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

  1. Перейти в контейнер биллинга
    chroot /app/asr_billing/
  2. Запустить скрипт
    python2.7 /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105

    Где 105 - id отчёта.
    По-умолчанибю информация, выведенная на экран, будет иметь кодировку Windows (windows-1251, cp1251)

У скрипта есть несколько опций и пресетов для выгрузок в СОРМ3

Дополнительные опции запуска

Опция Полный формат Пример Описание
-c CHARSET --charset=CHARSET -c utf8 Кодировка вывода, по-умолчанию: cp1251
-p PRESET --preset=PRESET -p norsi-trans Использовать пресет настроек для конкретного СОРМ
Доступные пресеты: mfi-soft, vasexperts, norsi-trans, signaltec
-s SEPARATOR --separator=SEPARATOR -s "|" Символ разделителя полей, по-умолчанию: точка с запятой ";"
-f --safe-separator - Удалять символ разделителя из содержимого полей.
Если задан этот флаг, то в каждом поле отчета символ разделителя (из опции --separator) будет заменяться на пробел.
Это позволяет гарантировать, что разделитель полей встречается только между полями, но не в самих полях.
-q QUOTE --quote=QUOTE -q \" Символ экранирования полей, по-умолчанию: нет
Чтобы задать двойную кавычку в bash её нужно экранировать: make_reports.pyc --quote=\"
Все остальные символы можно передать в двойных кавычках, например: make_reports.pyc --quote="'"
-b --double-quote - Дублировать символ экранирования, если он встречается внутри поля
-w --wrap - Экранировать только поля, внутри которых встречаются символ разделителя или символ экранирования

Пример запуска make_reports с опциями

  • Выгрузить отчет #105 в формате, пригодном для СОРМ от компании "Норси-Транс" и кодировке UTF-8:
    python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -c utf8 -p norsi-trans
  • Выгрузить отчет #105, разделяя поля запятыми и экранируя двойными кавычками только поля со спецсимволами:
    python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -s "," -q \" -w
  • Выгрузить отчет #105, экранируя все поля и удаляя символ разделителя из полей:
    python /usr/lib/python2.7/site-packages/jobs_worker/jobs_scripts/make_reports.pyc -r 105 -s ";" -q \" -f

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

Запрос к базе данных можно выполнить передав его утилите sqlexec, например так:

Команда
sqlexec "select count(*) from abonents"
Вывод
       COUNT 
============ 
          14 

Запрос можно написать в несколько строк:

Команда
sqlexec "select
count(*)
from
abonents"

Это удобно, если запрос сложный и объёмный. Вывод будет таким же, как у команды выше.

Выполнение запроса для использования в скриптах

Тут возможны два варианта.

С помощью sqlexec

Если отчёт достаточно простой, его можно выполнить с помощью sqlexec. Но нужно учитвать несколько моментов:

  • На самом деле утилита работает в контейнере asr_billing
  • Вывод лучше отформатировать для удобства написания скриптов и сохранения данных в переменные
  • Частично отформатировать Вывод можно средствами SQL:
    • либо "set heading off;" - уберёт заголовки, это удобно если Вы выгружаете, например, список каких-то позиций (абонентов, финансовых операций) для обработки каждой в отдельности
    • либо "set list on;" - "перевернёт" данные, отразит их в виде колонок вместо таблицы

Ниже приведён скрипт, в котором показано как работать с выводом в том или ином виде, включая одиночные значения и списки:

#!/bin/bash

abonent_count=$(chroot /app/asr_billing/ /usr/local/bin/sqlexec "set list; select count(*) from abonents where is_folder=0" | awk '$2{print $2}')
folder_count=$(chroot /app/asr_billing/ /usr/local/bin/sqlexec "set heading off; select count(*) from abonents where is_folder=1" | sed 's/   *//g; /^$/d')

echo "В биллиге сейчас вот столько абонентов, администраторов, и операторов связи: ${abonent_count}"
echo "В биллиге сейчас вот столько папок: ${folder_count}"
echo "Список папок:"
chroot /app/asr_billing/ /usr/local/bin/sqlexec "set heading off; select name from abonents where is_folder=1" | sed 's/   *//g; /^$/d' | \
while read folder; do
    echo "* ${folder}"
done

Если sqlexec не очень подходит, отчёт слишком сложный

Если отчёт слишком сложный и его удобней сохранить в файл, тогда сделайте следующее:

  1. Убедитесь что запрос в файле заканчивается символом точки с запятой: ";"
  2. Положите файл где-нибудь внутри контейнера asr_billing
  3. Выполните запрос командой isql-fb, ключём -i укажите путь к файлу

Например, можно положить скрипт в папку opt, внутри контейнера, с таким содержимым:

Файл /app/asr_billing/opt/abonents.sql
select 
    count(*) 
from 
    abonents 
where 
    is_folder=0
;

Тогда запрос к БД можно выплнить такой командой:

Команда
chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /opt/abonents.sql
Заметьте, что для "хост" системы, файл находится по пути /app/asr_billing/opt/abonents.sql, но утилита isql-fb находится в контейнере, и для неё нужно указывать "относительный" путь: /opt/abonents.sql, убрав адрес корневой папки контейнера.

Вывод отчёта Вы так же можете форматировать командными утилитами ОС, сохранять в переменные или передавать в циклы для дальнейшей обработки.

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

К большенсту полей БД существуют описания. Описание всех полей можно получить выполнив команду 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 Разрешать подключение обещанного платежа только один раз в текущий месяц;

Особенности SQL в Firebird

UNION (UNION ALL) и ORDER BY

В Firebird Order By должен быть последним оператором, сортируя всю выборку, например:
    select
        col1,
        col2,
        col3
    from
        table1
union all
    select
        '',
        'Итого',
        sum(col3)
    from
        table1
order by
    1
Как поставить "Итого" в конец строки

В примере выше вторая часть запроса выбирает итого по первой, при этом "Итого" окажется первой строкой выборки так как при сортировке пустая строка "выше" любого символа.
Чтобы "Итого" переместить в конец списка, можно использовать "невидимый" символ Юникода подобный пробелу, но стоящий в Юникоде ниже всех Кириллицы:

union all
    select
        ' '

Список символов можно посмотреть в Википедии, в примере использован символ U+2003 "em space"

Особенность сортировки числовых значений

В примере выше выборка будет отсортирована как текстовая. То есть порядок чисел будет: 1,10,100,2,20,200. Что бы отсортировать выборку как числа нужно убрать строковые константы в полях и заменить их на значение null. Тогда результат будет такой: 1,2,10,20,100,200.

    select
        col1,
        col2,
        col3
    from
        table1
union all
    select
        null,
        null,
        sum(col3)
    from
        table1
order by 3

Ограничения в наименовании столбцов отчета

В случае использования кириллицы в названии столбцов отчета важно знать, что поле может содержать не более 18 символов.

Если требуется указать более развернутое название - используйте латиницу.
Иначе, будет возникать ошибка при выполнении отчета.

Процедуры Carbon Soft

Для работы с базой мы добавили ряд собственных процедур, которые помогут найти нужные данные и верно их отобразить в отчётах в соответствии с архитектурой биллинга.

Получение списка вложенных групп и абонентов в дереве групп

GLN_RECURSIVE_GROUP_WALK

Принимает: GR_ID - ID группы
Возвращает: GROUP_ID - ID запрошенной группы и всех её подгрупп

GLN_RECURSIVE_ABONENTS_GET

Принимает: GROUP_ID - ID группы
Возвращает: ABONENT_ID - ID абонентов в указанной группе и всех её подгруппах

Примеры запросов

ID групп
SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(1)
ID абонентов
SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1)
Имена абонентов
select
    a.name
from GLN_RECURSIVE_ABONENTS_GET(1) aids
inner join abonents a on a.id=aids.abonent_id

Примеры отчётов

  1. Список абонентов, оплативших через платежную систему "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
    
  2. Общее количество абонентов по группам
    select
    name as "Группа",
    (select count(1) from abonents where parent_id=grp.id) as "Количество абонентов"
    from abonents grp
    where is_folder=1
    
  3. Средняя выручка(в расчёте на одного абонента) за предыдущий и текущий месяц или с возможностью выбора периода
    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
    
  4. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента)
    select
    A.CONTRACT_NUMBER as "Номер договора",
    A.NAME as "ФИО",
    SER.ATTRIBUTE_VALUE as "Паспорт серия",
    NOM.ATTRIBUTE_VALUE as "Паспорт номер",
    coalesce(PAS1.ATTRIBUTE_VALUE,'') as "Кем выдан",
    coalesce(PAS2.ATTRIBUTE_VALUE,'') as "Дата выдачи",
    coalesce(birthday.ATTRIBUTE_VALUE,'') as "Дата рождения",
    coalesce(H.CITY,'') as "Город",
    coalesce(H.STREET,'') as "Улица",
    H.S_NUMBER as "Номер дома",
    A.A_HOME_NUMBER as "Номер комнаты",
    A.SMS as "Телефон",
    EMAIL as "Эл. почта",
    T.NAME as "Тариф",
    ABSP.CEIL_IN as "Скорость"
    from ABONENTS A
    left join ABONENTS_SPEED ABSP on ABSP.ABONENT_ID = A.ID
    left join TARIF T on T.ID = A.TARIF_ID
    left join HOMES H on H.ID = A.HOME_ID
    left join ATTRIBUTE_VALUES SER on SER.ABONENT_ID=A.ID and SER.ATTRIBUTE_ID=14
    left join ATTRIBUTE_VALUES NOM on NOM.ABONENT_ID=A.ID and NOM.ATTRIBUTE_ID=13
    left join ATTRIBUTE_VALUES PAS1 on PAS1.ABONENT_ID=A.ID and PAS1.ATTRIBUTE_ID=16
    left join ATTRIBUTE_VALUES PAS2 on PAS2.ABONENT_ID=A.ID and PAS2.ATTRIBUTE_ID=17
    left join ATTRIBUTE_VALUES birthday on birthday.ABONENT_ID=A.ID and birthday.ATTRIBUTE_ID=22
    where A.DELETED = 0
    and A.IS_FOLDER = 0
    and A.ID>0
    order by A.NAME
    
  5. Отчет по списаниям по абонентам в формате (число, 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
    
  6. Отчет по должникам для отключения КТВ (Улица, Дом, Подъезд, Квартира, ФИО, Тариф) игнорируя ручной статус "Отключен"
    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
    
  7. Отчет по свободным 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
    
  8. Кто платит через Юнителлер?
    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
    
  9. Количество услуг из ЛК за месяц по абонентам
    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$'
    
  10. Прибыль с комиссии за обещанный платеж (все периоды)
    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
    
  11. Прибыль с комиссии за обещанный платеж (по периодам)
    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
    
  12. Просмотр пользователей в онлайне с выводом времени онлайна
    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
    
  13. Абоненты, которые в начале следующего месяца будут заблокированы (при условии что лимит выставлен отричательным числом, в противном случае минус перед ним нужно убрать)
    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
    
  14. Список 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
    
  15. Список абонентов, взявших больше одного обещанного платежа
    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 in (1247, 1248, 1249)
          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
  16. Отчет по абонентам со статусом "подключен"
    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)
  17. Отчет по абонентам со статусом "не подключен"
    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
  18. Отчет по абонентам у которых сегодня списалась абонентская плата
    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
  19. Отчет выводящий абонентов должников и в статусе "отключен" по каждому дому в отдельности:
    select CITY as "Город",
           STREET as "Улица",
           SNUMBER as "Дом",
           AHOMENUMBER as "Квартира",
           FIO as "ФИО",
           PHONE as "Телефон",
           TNAME 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 (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.id=':Дом|select[homes]$'
          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
    
  20. Отчет "абоненты с положительным балансом"
    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
  21. Отчет "Абоненты по услуге"
    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 = ':Услуга|select[Usluga]$' 
  22. Отчет "Абоненты по тарифу"
    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 Тарифа$
    
  23. Отчет "Пресса", цель отчета раздать списки почтальонам, кому из абонентов приносить газету.
    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
  24. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам.
    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
  25. Отчет по должникам с возможностью выбора тарифа. В отчете: номер договора, ФИО, телефон абонента, адрес, тариф, статус, дата последнего платежа, баланс.
    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
  26. Отчет должники по постоплате с разделением по физическим и юридическим лицам. Отчет содержит: номер договора, ФИО, адрес, дата последнего платежа, сумма долга (рекоммендованный платеж по постоплате)
    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
  27. Отчет по абонентам, у которых должна произойти смена тарифа
    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 '
  28. Сумма платежей через платежные системы "Жилищное управление", "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
  29. Отчет "Выставленные счета физическим лицам". В отчете: сумма в счете, ФИО абонента, описание финансовой операции
    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)
  30. Отчёт о превышении лимита предоплаченного трафика по юридическим лицам с возможностью выбора месяца - года
    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 %}
  31. Отчёт о динамике приходов и актов за заданный период времени
    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 %}
  32. Отчёт по пользователям с добровольной блокировкой
    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
  33. Список пользователей с сортировкой по ФИО (ФИО, логин, 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
    
  34. Отчёт в формате "Логин - Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения". По текущей дате
    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
    
  35. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг.
    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$')
    
  36. Отчёт по должникам по телефонии в формате " № - Папка - ФИО - Телефонный номер - Дата последней оплаты - Сумма долга
    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
    
  37. Отчёт по скачанному трафику за период.
    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
    
  38. Отчёт по скачанному трафику за период (2 вариант).
    select
        abonent_id as "ИД абонента",
        year_number ||'-'|| month_number as "Период",
        (SUM_BYTE_IN_M /1000000000.00) as "Входящий/гиг",
        (SUM_BYTE_OUT_M /1000000000.00) as "Исходящий/гиг"
    from
        traf_counters
    where
        MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
        and YEAR_NUMBER  between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
    union all
    select
     'Итого',
     '',
     sum(SUM_BYTE_IN_M /1000000000.00),
     sum(SUM_BYTE_OUT_M /1000000000.00)
    from
        traf_counters
    where
        MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
        and YEAR_NUMBER  between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
    
  39. Отчёт по платежам для групп абонентов.
    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 папки$')
    
  40. Отчёт для платежной системы "Город", выводящий в строку "ФИО;Адрес;Лицевой счет;Текущий остаток;';;;;'" и выводящий в Шапке отчёта
    • 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
      

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

      select '#FILESUM' || ' ' || round(sum(abon_plata.summa), 2)
      from abonents as a
      right join homes as h on a.home_id=h.id
      left join 
          (
              select 
                  uu.abonent_id abon_id,
                  sum(iif(coalesce(uu.dinamyc_price,0)=0,u.summa,uu.dinamyc_price))/10000000000.00 summa
              from 
                  users_usluga uu
              join 
                  usluga u 
                  on uu.usluga_id=u.id
              where
                  uu.deleted=0
              group by 
                  uu.abonent_id
          ) abon_plata on a.id=abon_plata.abon_id 
      where company=0
      and is_folder=0
      and a.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Группа|select[Abonents,is_folder=1]$'))
      UNION ALL
      select first 1 '#TYPE 7' from users
      UNION ALL
      select first 1 '#SERVICE 10240' from users
      UNION ALL
      select a.name || ';' || coalesce(h.city,'') || ',' || coalesce(h.street,'') || ',' || coalesce(h.s_number,'')  || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||abon_plata.summa|| ';;;;'
      from abonents as a
      right join homes as h on a.home_id=h.id
      
      left join 
          (
              select 
                  uu.abonent_id abon_id,
                  sum(iif(coalesce(uu.dinamyc_price,0)=0,u.summa,uu.dinamyc_price))/10000000000.00 summa
              from 
                  users_usluga uu
              join 
                  usluga u 
                  on uu.usluga_id=u.id
              where
                  uu.deleted=0
              group by 
                  uu.abonent_id
          ) abon_plata on a.id=abon_plata.abon_id 
      where company=0
      and is_folder=0
      and a.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Группа|select[Abonents,is_folder=1]$'))
      

      Тот же отчёт, выводящий дополнительные поля-константы и номер телефона

      select first 1
      -- #FILESUM 341098.84 - сумма всех платежей
          '#FILESUM ' || '' ||
          cast(sum(ac.recomend_pay_sum_cache/10000000000.00) as varchar(32))
      from 
          abonents a
      join
          homes h
          on a.home_id=h.id
      join
          abonents_cache ac
          on a.id=ac.id
      
      union all
      
      select first 1
      -- #TYPE 7 - константа
          '#TYPE 7'
      from 
          abonents a
      join
          homes h
          on a.home_id=h.id
      join
          abonents_cache ac
          on a.id=ac.id
      
      union all
      
      select first 1
      -- #SERVICE 60592 - константа
          '#SERVICE 60592'
      from 
          abonents a
      join
          homes h
          on a.home_id=h.id
      join
          abonents_cache ac
          on a.id=ac.id
      
      union all
      
      select
      -- 1 поле - ФИО; 
          a.name || ';' ||
      -- 2 поле - Адрес; 
          case
              when coalesce(h.settlement,'') <> '' then h.settlement -- если есть поселение, берём его
              else h.city -- иначе город
          end || ',' ||
          coalesce(h.street,'') || ',' || -- улица 
          case
              when (coalesce(s_number,'') <> '' and coalesce(s_liter,'') <> '') then s_number || '/' || s_liter -- дом и корпус
              when coalesce(s_number,'') <> '' then s_number -- дом
              else '' -- ничего или только корпус
          end || ',' ||
          coalesce(a.a_home_number,'') || ';' ||
      -- 3 поле - Номер договора; 
          a.contract_number || ';' ||
      -- 4 поле - сумма;
          cast(ac.recomend_pay_sum_cache/10000000000.00 as varchar(32)) || ';' ||
      -- 5-7 пустые; 
          -- '5;6;7' || ';' || -- DEBUG
          ';;' || ';' ||
      -- 8 поле - 7705:9120000001:0 (константа:номер телефона:константа)
          '7705' || ':' || coalesce(a.sms,'') || ':' || '0'
      from 
          abonents a
      join
          homes h
          on a.home_id=h.id
      join
          abonents_cache ac
          on a.id=ac.id
      where
          coalesce(a.company,0) = 0
      
  41. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (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 %}
    
  42. Отчёт по определенной услуге. Абонент, логин, баланс, конец действия услуги.
    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
    
  43. Отчёт по абонентам в формате "ФИО ЛОГИН 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
    
  44. Отчёт, выводящий сумму списаний, взятых из выставленных актов, за определенный период по всем абонентам.
    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$')
    
  45. Отчет для вывода рекомендуемого ежемесячно платежа, стоимости тарифа по всем абонентам.
    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
    
  46. Отчёт по платежам через веб - кассу(По операторам)
    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 = ':Администратор$'
    
  47. Поле "Не отключать при превышении порога", 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
    
  48. Учёт заключенных договоров за период
    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$'
    
  49. Отчёт по пулам 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
    
  50. Отчёт по приходам за заданный период времени
    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$')
    
  51. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%"
    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$')
    
  52. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период.
    select distinct u.name as "Услуга",
    round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена",
    round(sum(c.SUMM), 2) as "Сумма"
    from counters c
    left join usluga u on u.id=c.usluga_id
    where 
    c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
    and c.year_number  = extract(year  from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
    group by u.name, u.SUMMA
    
  53. Сформированные акты/счета по юридическим лицам.
    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
    
  54. Отчёт по юридическим лицам, выводящий ФИО Телефон Адрес Р/С ИНН КПП
    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
    
  55. Отчёт прибыль по плате за подключение нескольких услуг.
    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
    
  56. Отчёт выводит абонентов, последнее подключение которых было раньше, чем указанная дата.
    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$')
    
  57. Отчёт выводит информацию по абонентам с определенным 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$')
    
  58. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус.
    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
    
  59. Поиск IP-адреса в истории изменения учетных записей (история выдачи IP-адреса)

    При каждом изменении учётной записи новое состояние учётной записи записывается в историю. Таблица users_history. Отчёт покажет когда абоненту был назначен 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-адрес$'
    order by
        time_changed
    
  60. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс"
    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
    
  61. Отчёт по платившим абонентам в формате "Номер договора - ФИО - Группа - Дата и время операции - Описание - Сумма" с выводом итоговой суммы и возможностью выбора платежей по конкретной папке.
    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))
    
  62. Отчет выводящий информацию из карточки абонента с текущим статусом; колонки административной и добровольной блокировками заполняются только в случае активной блокировки; в последней колонке пишется дата, до которой действительна добровольная блокировка
    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
    
  63. Отчет выводит данные по операциям по всем абонентам за выбранный период
    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
    
  64. Отчет выводит абонентов не имеющих операции приход до указанной даты. Поиск по конкретной папке.
    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
    
  65. Отчёт, выводящий список незаблокированных абонентов.
    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
    
  66. Отчёт по абонентам с блокировкой по отрицательному балансу.
    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
    
  67. Отчёт в формате "ФИО-номер договора-телефон-адрес"
    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
    
  68. Абоненты с учетными записями без 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 = '')
  69. Отчёт о списаниях абонентской платы по папкам за определенный период.
    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
    
  70. Отчёт по телефонии. Структура данных информации об Абонентах по маске 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 %}
    
  71. Потребленный трафик суммарно по всем абонентам с возможностью выбора периода
    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
  72. Поиск абонентов со скидкой, с выводом самой скидки, 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
    
  73. Отчёт, который выводит списания абонентов с учетом и без учета НДС за выбранный период по каждому абоненту.
    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
    
  74. Отчёт по всем финансовым операциям по конкретному администратору за выбранный период времени.
    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]$
    
  75. Отчёт по исходящему телефонному трафику за выбранный период

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

    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
    
  76. Отчёт по абонентам со статусом, балансом, датой последнего платежа и суммой последнего платежа.
    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
    
  77. Отчёт по телефонии по звонкам, которые были выполнены на конкретный пул телефонных номеров за указанную дату по конкретному абоненту.
    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 Конец пула$'))
    
  78. Отчёт по 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
  79. Отчёт для ФСБ в формате Оператор связи 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
    
  80. Отчёт по абонентам, у которых количество услуг больше 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
    
  81. История выдачи телефонного номера для 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=':Введите номер$')
    
  82. История использования логина.
    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(':Логин$')
    
  83. Отчёт по услугам 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 "Цена услуги",
    coalesce((select count(distinct abonent_id) from arch_account_stack where BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') and arch_account_stack.usluga_id=u.id),0) as "Кол-во абонентов",
    coalesce((select count(*) from arch_account_stack where arch_account_stack.usluga_id=u.id and BILL_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by usluga_id),0) as "Дни",
    (select coalesce((round(sum(counters1.SUMM), 2)),0) from counters counters1 where S_DATE between (':1 Дата|date$') and (':2 Дата|date$') and counters1.usluga_id=u.id)as "Сумма"
    from usluga u
    join counters on counters.usluga_id=u.id
    where U.SYSTEM_TYPE=7
    and u.id is not null
    group by u.name, u.SUMMA, u.id
    union all
    select '"Итого"',
           null,
           null,
           null,
           sum(SUMOP)
    from (select round(sum(counters.SUMM), 2)  as SUMOP
    from usluga u
    join counters on counters.usluga_id=u.id
    where U.SYSTEM_TYPE=7
    and u.id is not null
    and S_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
    
  84. Отчёт по абонентам, у которых подключены услуги 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 counters.MONTH_NUMBER=(':Месяц$')
    and counters.YEAR_NUMBER=(':Год$')
    group by counters.abonent_id, u.name, u.SUMMA
    union all
    select '"Итого"',
           null,
           null,
           sum(SUMOP)
    from (select round(sum(counters.SUMM), 2)  as SUMOP
    from counters
    join usluga u on u.id=counters.usluga_id
    where U.SYSTEM_TYPE=7
    and u.id is not null
    and counters.MONTH_NUMBER=(':Месяц$')
    and counters.YEAR_NUMBER=(':Год$'))
    
  85. Отчёт по телефонии. Структура данных о выставленных счетах фактуры по абонентам с маской телефона 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 %}
      
  86. Отчёт, который выводит всех абонентов с активными подстатусами типов подключения.
    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
    
  87. Отчёт, который выводит все активные 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^]Убывания]$
  88. Отчёт по распределению абонентов из определенной группы по тарифам, так же выводится количество в настоящий момент заблокированных и не заблокированных абонентов.
    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
  89. Отчёт по поступившим платежам за выбранный период, с возможностью выбора папки для поиска и пользователя,который добавил платеж.
    select AB.CONTRACT_NUMBER as "Номер договора",
           AB.NAME as "ФИО",
           FO.SYSTEM_DATE as "Дата",
           FO.DESCR as "Описание",
           AU.USERNAME as "Добавлен",
           cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                         from VPN_CONST
                                                         where CONST_ID = 1) as "Сумма"
            from FINANCE_OPERATIONS FO
    left join ABONENTS AB on FO.ABONENT_ID = AB.ID
    left join AUTH_USER AU on FO.OWNER_ID = AU.ID
    where OP_TYPE = 2
          and (FO.OP_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
    and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0))
          and (AB.DELETED = 0
          or AB.DELETED is null)
    
    group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, AU.USERNAME, FO.SYSTEM_DATE
    union
    select '"Итого"',
           null,
           null,
           null,
           null,
           sum(SUMOP)
    from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                               from VPN_CONST
                                                               where CONST_ID = 1) as SUMOP
          from FINANCE_OPERATIONS FO
          left join ABONENTS AB on FO.ABONENT_ID = AB.ID
          where OP_TYPE = 2
                and (FO.OP_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
    and AB.id in (select id from (with recursive tree (id,is_folder) as (select id,is_folder from abonents where id = ':Папка|select[Abonents,is_folder=1]$' union all select a.id,a.is_folder from abonents a join tree on a.parent_id=tree.id where a.deleted=0) select id from tree where is_folder=0))
                and (AB.DELETED = 0
                or AB.DELETED is null))
    
  90. Отчёт по абонентам, выводящий следующий данные:"ФИО, договор, текущий баланс,сумму списаний по разовым услугам за период, сумму абон.платы, сумму приходов за период".
    select a.name as "ФИО",
    a.contract_number as "Договор",
    round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс",
    (
    	select
    	sum(round(users_usluga.SUMM / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	from users_usluga
    	left join usluga on usluga.id=users_usluga.usluga_id
    	where users_usluga.ABONENT_ID=a.id
    	and usluga.SYSTEM_TYPE=0
    	and users_usluga.CREATE_DATE between (':1 Дата|date$') and (':2 Дата|date$') group by users_usluga.abonent_id
    ) as "Разовые услуги",
    (
    	select
    	sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	FROM TARIF t
    	LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
    	LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
    	WHERE a.TARIF_ID = t.ID
    ) as "Абон. плата",
    (
    	select sum(round(fo1.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	from finance_operations fo1
    	where fo1.abonent_id=a.id
    	and fo1.op_type=2
    	and fo1.op_date between (':1 Дата|date$') and (':2 Дата|date$')
    ) as "Приходы"
    from abonents a
    left join finance_operations fo on fo.abonent_id=a.id
    left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
    where is_folder=0
    group by a.name, a.TARIF_ID, (aa.ostatok+aa.debit-aa.credit) ,a.id,a.contract_number
    
  91. Отчёт по абонентам с блокировкой по отрицательному балансу с балансом больше 2.
    select
         distinct
         A.CONTRACT_NUMBER as "Договор",
         T.name as "Тариф",
         usl.NAME as "Услуга",
         round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс"
    from ABONENTS A
    left join USERS U on A.ID = U.ABONENT_ID
    left join TARIF T on A.TARIF_ID=T.ID
    left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
    left join users_usluga uu on uu.ABONENT_ID=A.ID
    left join usluga usl on usl.id=uu.USLUGA_ID
    left join abonents_block ab on ab.abonent_id=a.id
    where A.IS_FOLDER=0
    and a.deleted=0
    and ab.B_NEGBAL=1
    and round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)>2
    
  92. Отчёт по абонентам, у которых дата следующего списания больше, чем указанная.
    select
    distinct a.id as id,
    a.name as "ФИО",
    uu.next_date as "След. списание",
    a.contract_number as "Договор"
    from abonents a
    left join users_usluga uu on uu.abonent_id=a.id
    where uu.next_date>(':1 Дата|date$') and a.deleted=0
    
  93. Отчет для поиска по реквизитам

    Отчет ориентирован только на реквизиты текстового типа.
    При использовании вместе с шаблоном отчета имя абонента будет преобразовано в гиперссылку на его карточку в БД

    Текст запроса
    select
        a.id,
        a.name,
        a.contract_number,
        ua.name,
        av.attribute_value
    from
        abonents a
    join
        attribute_values av on a.id=av.abonent_id and av.attribute_value like '%:Значение реквизита$%'
    join
        user_attributes ua on av.attribute_id=ua.attribute_id
    
    Шаблон отчета
    {% extends "form_list.html" %}
    {% load field_type %}
    {% block content %}
            <form method="POST">
        <div id="params">
            {% csrf_token %}{% load mathfilters %}
            <input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
            {% if form %}
                <div class="row-fluid">
                    <div class="span6">
                        <legend><h2>Заполните поля запроса</h2></legend>
                        {{ form }}
                    </div>
                </div>
            {% endif %}
            <div class="row-fluid">
                <div class="down_toolbar">
                    <button type="submit" value="Выполнить запрос" class="default btn btn-success">
                        <i class="icon-ok icon-white"></i> Выполнить запрос
                    </button>
                    {% if data %}
                        <div class="btn-group">
                            <a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
                                <i class="icon-print icon-white"></i>
                                Выгрузка
                                <span class="caret"></span>
                            </a>
                            <ul class="dropdown-menu">
                                <li><button type="submit" name="csv" value="True">CSV</button></li>
                                <li><button type="submit" name="dbf" value="True">DBF</button></li>
                            </ul>
                        </div>
                        <button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
                    {% endif %}
                </div>
            </div>
            </div>
        </form>
        {% block subcontent %}
         {% if execute %}
            <h2>{{inst.name}}</h2>
            {% if error  %}
                {{ error }}<br/><br/>
            {% endif %}
    
            {% if data %}
    
                <table id="print" class="mysqldata" border="1" style="text-align: center;" cellpadding="5">
                    <tr>
                       <th>ФИО/Название</th>
                       <th>Договор</th>
                       <th>Реквизит</th>
                       <th>Значение</th>
                    </tr>
                    {% for row in data %}
                        <tr align="left" cellpadding="10">
                           <td><a href="/admin/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
                           <td>{{ row.2 }}</td>
                           <td>{{ row.3 }}</td>
                           <td>{{ row.4 }}</td>
                        </tr>
                    {% endfor %}
                </table>
            {% else %}
                <h3>Ничего не найдено.</h3>
            {% endif %}
         {% endif %}
        {% endblock %}
    {% endblock %}
    {% block js_addon %}
    <link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
    <script type="text/javascript" src="/static/js/makedatetime.js"></script>
    <script type="text/javascript" src="/static/js/print.js"></script>
    <link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
    {% endblock %}
  94. Отчет по выполненным задачам CRM с возможностью выбора периода, статуса, типа, ответственного за задачу. В конце выводится общее число задач.
    Время в фильтре проверят дату создания а не дату закрытия заявки
    select
    HDSK.id,
    HDSK.SUBJ as "Тема",
    hs.NAME as "Статус",
    a.name as "Назначено",
    ht.NAME as "Тип"
    from HDSK
    left join HDSK_STATUS hs on hdsk.STATUS=hs.id
    left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
    left join abonents a on a.id=hdsk.PERFORMED_WHOM
    left join auth_user au on au.USERNAME=a.name
    where hdsk.IS_TASK=1
    and hdsk.STATUS=':Статус|select[HdskStatus]$'
    and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$'
    and hdsk.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$'
    and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$')
    union all
    select
    '"Количество"',
    '',
    '',
    '',
    count(*)
    from HDSK
    left join HDSK_STATUS hs on hdsk.STATUS=hs.id
    left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
    left join abonents a on a.id=hdsk.PERFORMED_WHOM
    left join auth_user au on au.USERNAME=a.name
    where hdsk.IS_TASK=1
    and hdsk.STATUS=':Статус|select[HdskStatus]$'
    and hdsk.HDSK_TYPE_ID=':Тип|select[HdskType]$'
    and hdsk.PERFORMED_WHOM=':Назначено|select[Abonents,parent_id=244]$'
    and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$')
    
  95. Отчёт по абонентам с балансом между минимальным и максимальным указанным.
    select distinct
         A.CONTRACT_NUMBER as "Договор",
         T.name as "Тариф",
    round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс",
         A.name as "ФИО"
    from ABONENTS A
    left join USERS U on A.ID = U.ABONENT_ID
    left join TARIF T on A.TARIF_ID=T.ID
    left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
    where A.IS_FOLDER=0
    and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)<(':1.Максимум$')
    and round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)>(':2.Минимум$')
    AND A.DELETED=0
    and a.is_folder=0
    order by A.NAME
    
  96. Отчет выводящий объёмы трафика за вбыранный период (месяца целиком) по абоненту
    
    select
        year_number ||'-'|| month_number as "Период",
    round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "Входящий/Mб",
    round(SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2) as "Исходящий/Mб"
    from
        traf_counters
    where
        Abonent_ID =  ':3-Абонент|select[Abonents,is_folder=0]$'
        and MONTH_NUMBER between extract(month from cast(':1-date_start|date$' as date)) and extract(month from cast(':2-date_end|date$' as date))
        and YEAR_NUMBER  between extract(year from cast(':1-date_start|date$' as date)) and extract(year from cast(':2-date_end|date$' as date))
    order by
        YEAR_NUMBER, MONTH_NUMBER
    
    
    При настройке отчета для использования в личном кабинете, условие с ID абонента должно быть таким:
    Abonent_ID =  ':Abonent_ID$'
  97. Отчёт по абонентам, подключенным по адресу. В поле "Должник" выводится информация "Должник" в том случае, если у абонента баланс меньше, чем минус 3 абонентские платы.
    select distinct
          usl.name as "Услуга",
                (case
          when a.A_HOME_NUMBER='' then cast('0' as NUMERIC)
          else cast((a.A_HOME_NUMBER) as NUMERIC) end) as "Квартира",
          (
    	select
    	sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	FROM TARIF t
    	LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
    	LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
    	WHERE a.TARIF_ID = t.ID
    ) as "Абон. плата",
     (
    	select
    	sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2))
    	FROM TARIF t
    	LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
    	LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
    	WHERE a.TARIF_ID = t.ID
    ) as "3 АБ",
          (case when (round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)) < (
    	select
    	sum(round(u.SUMMA / cast((-3333333333) as numeric(18,5)), 2))
    	FROM TARIF t
    	LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
    	LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
    	WHERE a.TARIF_ID = t.ID
    ) then 'Должник' else '' end) as "Должник",
          h.city || ', ' || h.STREET || ', д. ' ||  h.S_NUMBER as "Адрес",
    round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
    from ABONENTS A
    left join USERS U on A.ID = U.ABONENT_ID
    left join TARIF T on A.TARIF_ID=T.ID
    left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
    left join homes h on h.id=a.home_id
    left join abonents_block ab on ab.abonent_id=a.id
    LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
    left join usluga usl on usl.id=tus.usluga_id
    where A.IS_FOLDER=0
    and H.id=':Дом|select[homes]$'
    AND A.DELETED=0
    order by 2
    
  98. Отчёт по сумме приходов абонентов, подключенных к оператору связи за период. Вторым столбцом выводится 10% от суммы приходов
    select
    cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                         from VPN_CONST
                                                         where CONST_ID = 1) as "Сумма приходов",
    (cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                         from VPN_CONST
                                                         where CONST_ID = 1))*0.1 as "10%"
    from finance_operations fo
    left join abonents a on a.id=fo.abonent_id
    where a.OPERATOR_ID = ':Оператор|select[Abonents,category_id=2]$'
    and op_type=2
    and op_date between (':1 Дата|date$') and (':2 Дата|date$')
    group by a.operator_id
    
  99. Детализация звонков по абоненту
    НДС посчитан только для юридических лиц
    select distinct
        vl.src as "Звонок с номера",
        vl.dst as "Назначение звонка",
        substring(cast(vl.s_time as varchar(32)) from 1 for 19) as "Начало звонка",
        cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'),  coalesce(vl.bill_sec_round/60,'0')) as float) as "Время разговора",
        coalesce(vd.name,'') as "Название направления",
        coalesce(vl.step_price/10000000000.00,0) as "Стоимость направления",
        cast(coalesce(vl.bill_sum/10000000000.00,'') as float) as "Стоимость"
    from
        voip_log vl
        left join voip_direction vd on vl.direction_id=vd.id
    where
        vl.s_time between ':1-Начало|date$' and ':2-Конец|date$' and
        vl.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
    union all
    select
        '    ',
        '    ',
        'Итого:',
        sum(duration),
        '    ',
        '    ',
        sum(cost)
    from
        (
        select distinct
            vl.src as source,
            vl.dst as dest,
            substring(cast(vl.s_time as varchar(32)) from 1 for 19) as stime,
            cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'),  coalesce(vl.bill_sec_round/60,'0')) as float) as duration,
            coalesce(vl.step_price/10000000000.00,0) as directioncost,
            round(cast(coalesce(vl.bill_sum/10000000000.00,'') as float),2) as cost
        from
            voip_log vl
        where
            vl.s_time between ':1-Начало|date$' and ':2-Конец|date$' and
            vl.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
        )
    order by 3
    

    Скрипт для получения журнала звонков по всем архивным базам

    #!/bin/bash
    
    >export.csv
    >export_raw.csv
    
    # Текущая дата
    curdate=$(date +'%Y-%m-%d %H:%M:%S')
    
    # Начало периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00'
    period_start='2019-01-01 00:00:00'
    # Конец периода в формате '%Y-%m-%d %H:%M:%S', например '2019-01-01 00:00:00'
    period_end='2019-10-01 00:00:00'
    
    # Если начало или конец периода не заданы - будет выбрана текущая дата
    period_start_fixed=${period_start:-$curdate}
    period_end_fixed=${period_end:-$curdate}
    
    # ID абонента
    abonent_id=10507
    
    # Заголовок csv для удобства работы в электронных таблицах
    echo "Звонок с номера;Назначение звонка;Название направления;Начало звонка;Окончание звонка;Время разговора;Стоимость направления;Стоимость" > export.csv
    
    # Получаем звонки из архивных баз
    find /var/db/billing/ -iname voip_log.fdb | sort | while read file; do
        #echo $file >> export_raw.csv # DEBUG
        sqlexec $file "set heading off; select distinct
                vl.src || ';' ||
                vl.dst || ';' ||
                'direction:'  || coalesce(vl.direction_id,0) || ';' ||
                substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' ||
                substring(cast(vl.e_time as varchar(32)) from 1 for 19) || ';' ||
                cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'),  coalesce(vl.bill_sec_round/60,'0')) as float) || ';' ||
                coalesce(vl.step_price/10000000000.00,0) || ';' ||
                cast(coalesce(vl.bill_sum/10000000000.00,'') as float)
            from
                voip_log vl
            where
                vl.s_time between '$period_start' and '$period_end_fixed'
                and vl.abonent_id=$abonent_id " | sed 's/   *//g; /^$/d' >> export_raw.csv
    done
    
    # Получаем звонки из основной БД
    #echo '/var/db/billing.gdb' >> export_raw.csv # DEBUG
    sqlexec /var/db/billing.gdb "set heading off; select distinct
            vl.src || ';' ||
            vl.dst || ';' ||
            'direction:'  || vl.direction_id || ';' ||
            substring(cast(vl.s_time as varchar(32)) from 1 for 19) || ';' ||
            cast(iif(vl.step is not null and vl.step>0, coalesce(vl.bill_sec_round/vl.step,'0'),  coalesce(vl.bill_sec_round/60,'0')) as float) || ';' ||
            coalesce(vl.step_price/10000000000.00,0) || ';' ||
            cast(coalesce(vl.bill_sum/10000000000.00,'') as float)
        from
            voip_log vl
        where
            vl.s_time between '$period_start_fixed' and '$period_end_fixed'
            and vl.abonent_id=$abonent_id" | sed 's/   *//g; /^$/d' >> export_raw.csv
    
    # Получаем из БД названия направлений и отфильтровываем дебаг
    cat export_raw.csv | grep -vE '^<null>|voip_log.fdb$' | cut -d';' -f 3 | sort | uniq | while IFS=':' read field_name direction_id; do
        direction_name=$(sqlexec "set heading off; select vd.name from voip_direction vd where vd.id='$direction_id'" | sed 's/   *//g; /^$/d')
        sed "s/direction:${direction_id}/${direction_name}/g" -i export_raw.csv
    done
    
    # Сортируем на случаей если звонок попал в несколько архивных баз и пишем в выгрузку
    cat export_raw.csv | grep -v '^<null>' | sort | uniq >> export.csv
  100. Отчёт по абонентам, у которых услуги подключены не 5 числа. Данный отчёт подойдет для отслеживания абонентов со сдвигом даты списания услуг.
    select
    a.contract_number as "Договор",
    a.name as "ФИО",
    uu.usluga_id as "ID услуги",
    u.name as "Имя услуги"
    from users_usluga uu
    left join usluga u on u.id=uu.usluga_id
    left join abonents a on a.id=uu.abonent_id
    where extract(day from cast(uu.create_date as date))!=5
    and u.USLUGA_ABON_TYPE_ID=4
    and uu.deleted=0
    and uu.usluga_id!=-170000
    
  101. Отчёт по наличным/безналичным приходам с группировкой по тарифам абонентов за указанный период.
    select
    t.name as "Тариф",
    (
    	select
    	sum(round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	FROM TARIF t1
    	LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
    	LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
    	WHERE t1.id = t.ID
    ) as "Абон. плата",
    (
    	select distinct
    	count(*)
    	from abonents a1
    	left join tarif t2 on t2.id=a1.tarif_id
    	left join finance_operations fo1 on fo1.abonent_id=a1.id
    	where t2.id=t.id
    	and fo1.OP_TYPE = 2
    	and fo1.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
    ) as "Количество",
    (
    	select
    	sum(round(fo2.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	from finance_operations fo2
    	left join abonents a on a.id=fo2.abonent_id
    	where a.tarif_id=t.id
    	and fo2.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
    	and fo2.is_cash=1
    	and fo2.OP_TYPE = 2
    )as "Наличные",
    (
    	select
    	sum(round(fo3.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	from finance_operations fo3
    	left join abonents a on a.id=fo3.abonent_id
    	where a.tarif_id=t.id
    	and fo3.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
    	and fo3.is_cash=0
    	and fo3.OP_TYPE = 2
    )as "Безналичные",
    (
    	select
    	sum(round(fo.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2))
    	from finance_operations fo
    	left join abonents a on a.id=fo.abonent_id
    	where a.tarif_id=t.id
    	and fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
    	and fo.OP_TYPE = 2
    )as "Сумма"
    from tarif t
    where t.ARCHIVED=0
    union all
    select first 1 '"Итого"',
           null,
           null,
           (select sum(SUMOP)
    from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                               from VPN_CONST
                                                               where CONST_ID = 1) as SUMOP
          from FINANCE_OPERATIONS FO
          left join ABONENTS AB on FO.ABONENT_ID = AB.ID
          where OP_TYPE = 2
          		and fo.is_cash=1
                and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
                and (AB.DELETED = 0
                or AB.DELETED is null))),
            (select sum(SUMOP)
    from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                               from VPN_CONST
                                                               where CONST_ID = 1) as SUMOP
          from FINANCE_OPERATIONS FO
          left join ABONENTS AB on FO.ABONENT_ID = AB.ID
          where OP_TYPE = 2
          		and fo.is_cash=0
                and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
                and (AB.DELETED = 0
                or AB.DELETED is null))),
           (select sum(SUMOP)
    from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
                                                               from VPN_CONST
                                                               where CONST_ID = 1) as SUMOP
          from FINANCE_OPERATIONS FO
          left join ABONENTS AB on FO.ABONENT_ID = AB.ID
          where OP_TYPE = 2
                and (fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$'))
                and (AB.DELETED = 0
                or AB.DELETED is null)))
    from tarif t
    
  102. Отчёт для анализа настройки агентской схемы VoIP, отражает текущую настройку транков в операторских услугах телефонии
    select
        a.name FIO,
        coalesce(vt.name,'---') DIRECTION,
        u.name USLUGA,
        uu.voip_operator_match_format TRUNK
    from
        users_usluga uu
    join
        usluga u on uu.usluga_id=u.id
    join
        abonents a on uu.abonent_id=a.id
    left join
        V_TYPE vt on u.voip_v_type_id=vt.id
    where
        uu.deleted=0
        and a.deleted=0
        and u.system_type=5
    order by
        a.id,vt.name
  103. Отчёт для анализа настройки агентской схемы VoIP, подобный предыдущему, но отражает дополнительные данные и удалённые услуги
    select distinct 
        a.id "ID",
        a.name "Оператор",
        coalesce(u.name,'Нет услуги') "Услуга",
        'UU_id=' || uu.id || ', del=' || uu.deleted || ', U_id=' || u.id "Метаданные",
        coalesce(vt.name,'') "Тип трафика",
        iif(uu.id is not null,voip_operator_match_format,'отсутствуют') "Транки"
    from 
        abonents a 
        join users_usluga uu 
            on a.id=uu.abonent_id 
            and voip_operator_match_format<>'' 
        left join usluga u 
            on uu.usluga_id=u.id 
        left join v_type vt 
            on u.voip_v_type_id=vt.id 
    where 
        a.category_id=2 
        and a.id>2 
        and uu.deleted=:2-Показывать удалённые?|choices[0^]Нет^[1^]Да]$
    
    order by :1-Сортировка по:|choices[2^]Наименованию^[6^]Транкам^[5^]Типу трафика]$
    
  104. Отчёт по неплательщикам за месяц
    select distinct
    a.id,
    a.name,
    a.sms,
    a.contract_number
    from abonents a
    left join finance_operations fo on fo.abonent_id=a.id
    where fo.op_type=2
    and not exists(select 1 from finance_operations where OP_DATE between (dateadd (-1 month to date ':Начало|date$')) and current_date and op_type=2 and abonent_id=a.id)
    and a.deleted=0
    and a.is_folder=0
    and a.parent_id not in (244,4,2)
    
  105. Отчёт по объёмам потребленного трафика посуточно и остатка/превышения ежедневных и ежемесячных лимитов (если они есть) за указанный период

    Для работы отчета необходимо включить опцию "Сохранять движения всего трафика" в настройках оператора связи

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

    Шаблон для размещения в личном кабинете

    <form class="form-inline" method="post" action="" role="form">
        <?$this->module_token()?>
        <div class="form-group">
            <legend><h2>Трафик за выбранный период</h2></legend>
            <label for="1-date_start">C:</label><input class="datepicker" id="1-date_start" name="1-date_start|date" type="text" />
            <label for="2-date_end">По:</label><input class="datepicker" id="2-date_end" name="2-date_end|date" type="text" />
        </div>
        <br />
        <button type="submit" class="btn btn-success">Выполнить</button>
    </form>
  106. Отчёт по звонкам учтенным для взаиморасчетов между операторами
    select
        calls.service as "Услуга",
        calls.source as "Источник",
        calls.destination as "Назначение",
        calls.call_start as "Начало звонка",
        calls.call_end as "Завершение звонка",
        calls.s_chan as "Ист. транк",
        calls.d_chan as "Назн. транк",
        calls.cost as "Стоимость"
    from
        (select
            aas.id aasid,
            vl.id vlid,
            u.name service,
            aas.credit/10000000000.00 cost,
            s_time call_start,
            e_time call_end,
            src source,
            dst destination,
            src_chan s_chan,
            dst_chan d_chan
        from
            voip_log vl
            join arch_account_stack aas
                on vl.id=aas.log_id
            join usluga u
                on aas.usluga_id=u.id
                and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$
        where
            aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$'
            and s_time
                between ':1-Период начала звонка, от|date$'
                and ':2-Период начала звонка, до|date$ 23:59:59'
        ) calls
    union all
    select
        ' ',
        ' ',
        'Итого',
        cast(':1-Период начала звонка, от|date$' as timestamp),
        cast(':2-Период начала звонка, до|date$ 23:59:59' as timestamp),
        ' ',
        ' ',
        cast(sum(calls.cost) as varchar(32))
    from
        (select
            aas.id aasid,
            vl.id vlid,
            u.name service,
            aas.credit/10000000000.00 cost,
            s_time call_start,
            e_time call_end,
            src source,
            dst destination,
            src_chan s_chan,
            dst_chan d_chan
        from
            voip_log vl
            join arch_account_stack aas
                on vl.id=aas.log_id
            join usluga u
                on aas.usluga_id=u.id
                and vl.v_type_id=:Направление|choices[1^]Входящий^[2^]Исходящий^[3^]Транзит]$
        where
            aas.abonent_id = ':Группа|select[Abonents,is_folder=0,category_id=2,deleted=0]$'
            and s_time
                between ':1-Период начала звонка, от|date$'
                and ':2-Период начала звонка, до|date$ 23:59:59'
        ) calls
    group by
        calls.service
  107. Список абонентов, у которых не заведён дом или дом заведён некорректно

    Отчет

    select
        a.id,
        a.name,
        a.contract_number,
        t.name,
        p.name,
        a.home_id,
        iif(a.home_id is not null, 'Улица: ' || coalesce(h.street,''),'Не выбран дом'),
        iif(a.home_id is not null and (h.city is null or h.city=''), 'Не указан город', 'Не выбран дом')
    from
        abonents a
        join tarif t on a.tarif_id=t.id
        join abonents p on a.parent_id=p.id
        left join homes h on a.home_id=h.id
    where
        (a.home_id is null or h.city='' or h.city is null)
        and a.is_folder=0
        and a.category_id<2
        and a.deleted=0
        and a.id>0
        and a.parent_id not in (244,1313,16723)
    order by
        7,5

    Шаблон

    {% extends "form_list.html" %}
    {% load field_type %}
    {% block content %}
            <form method="POST">
        <div id="params">
            {% csrf_token %}{% load mathfilters %}
            <input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
            {% if form %}
                <div class="row-fluid">
                    <div class="span6">
                        <legend><h2>Заполните поля запроса</h2></legend>
                        {{ form }}
                    </div>
                </div>
            {% endif %}
            <div class="row-fluid">
                <div class="down_toolbar">
                    <button type="submit" value="Выполнить запрос" class="default btn btn-success">
                        <i class="icon-ok icon-white"></i> Выполнить запрос
                    </button>
                    {% if data %}
                        <div class="btn-group">
                            <a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
                                <i class="icon-print icon-white"></i>
                                Выгрузка
                                <span class="caret"></span>
                            </a>
                            <ul class="dropdown-menu">
                                <li><button type="submit" name="csv" value="True">CSV</button></li>
                                <li><button type="submit" name="dbf" value="True">DBF</button></li>
                            </ul>
                        </div>
                        <button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
                    {% endif %}
                </div>
            </div>
            </div>
        </form>
        {% block subcontent %}
         {% if execute %}
            <h2>{{inst.name}}</h2>
            {% if error  %}
                {{ error }}<br/><br/>
            {% endif %}
    
            {% if data %}
    
            <table id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="2">
                <tr>
                   <th>ФИО</th>
                   <th>Договор</th>
                   <th>Тариф</th>
                   <th>Группа</th>
                   <th>Улица</th>
                   <th>Проблема</th>
                </tr>
                {% for row in data %}
                    <tr>
                       <td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
                       <td>{{ row.2 }}</td>
                       <td>{{ row.3 }}</td>
                       <td>{{ row.4 }}</td>
                       <td>{% if row.5 %}<a href="/admin/Abonents/Abonents/{{ row.5 }}/" target="_blank">{{ row.6 }}{% else %} --- {% endif %}</a></td>
                       <td>{{ row.7 }}</td>
                    </tr>
                {% endfor %}
            </table>
            {% else %}
                <h3>Ничего не найдено.</h3>
            {% endif %}
         {% endif %}
        {% endblock %}
    {% endblock %}
    {% block js_addon %}
    <link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
    <script type="text/javascript" src="/static/js/makedatetime.js"></script>
    <script type="text/javascript" src="/static/js/print.js"></script>
    <link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
    {% endblock %}
  108. Отчет по входящему интернет-трафику юридических лиц за выбранный период месяц

    Отчет расчитан на использование тарифов типа лестница с некоторым объёмом трафика включенного в абонентскую плату, по исчерпании которого начисляется помегабайтная оплата.
    По каждому абоненту отражены тарифы назначенные абоненту за период, суммарное потребление трафика по тарифу и отдельно трафик сверх включенного, а так же его стоимость.

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

    Шаблон отчета:

    {% extends "form_list.html" %}
    {% load field_type %}
    {% block content %}
    <form method="POST">
        <div id="params">
            {% csrf_token %}{% load mathfilters %}
            <input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
            {% if form %}
                <div class="row-fluid">
                    <div class="span6">
                        <legend><h2>Заполните поля запроса</h2></legend>
                        {{ form }}
                    </div>
                </div>
            {% endif %}
            <div class="row-fluid">
                <div class="down_toolbar">
                    <button type="submit" value="Выполнить запрос" class="default btn btn-success">
                        <i class="icon-ok icon-white"></i> Выполнить запрос
                    </button>
                    {% if data %}
                        <div class="btn-group">
                            <a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
                                <i class="icon-print icon-white"></i>
                                Выгрузка
                                <span class="caret"></span>
                            </a>
                            <ul class="dropdown-menu">
                                <li><button type="submit" name="csv" value="True">CSV</button></li>
                                <li><button type="submit" name="dbf" value="True">DBF</button></li>
                            </ul>
                        </div>
                        <button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
                    {% endif %}
                </div>
            </div>
            </div>
        </form>
        {% block subcontent %}
         {% if execute %}
            <h2>{{inst.name}}</h2>
            {% if error  %}
                {{ error }}<br/><br/>
            {% endif %}
    
            {% if data %}
                <table bordercolor="#AAAAAA" id="print" class="mysqldata" border="1" style="text-align: left;" cellpadding="3">
                    <tr>
                       <th>ФИО</th>
                       <th>Тариф</th>
                       <th>Цена за МБ</th>
                       <th>Объем предоплаченного трафика</th>
                       <th>Объем трафика по тарифу/услуге</th>
                       <th>Объем трафика сверх предоплаченного</th>
                       <th>Переплата</th>
                    </tr>
                    {% for row in data %}
                        {% if row.1 != ' ' %}
                        <tr>
                           <td bgcolor="#F5F5F5"> </td>
                           <td bgcolor="#F5F5F5"> </td>
                           <td bgcolor="#F5F5F5"> </td>
                           <td bgcolor="#F5F5F5"> </td>
                           <td bgcolor="#F5F5F5"> </td>
                           <td bgcolor="#F5F5F5"> </td>
                           <td bgcolor="#F5F5F5"> </td>
                        </tr>
                        {% endif %}
                        {% if row.1 != ' ' and row.2 != ' ' %}
                            <tr>
                                <td><a href="/admin/Abonents/Abonents/{{ row.0 }}/" target="_blank">{{ row.1 }}</a></td>
                            </tr>
                         {% else %}
                            <tr>
                                <td> </td>
                                <td>{% if row.4 != ' ' %} <a href="/admin/tarifs/Usluga/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% else %} <a href="/admin/tarifs/Tarif/{{ row.2 }}/" target="_blank">{{ row.3 }}</a> {% endif %}</td>
                                <td style="text-align: right;">{% if row.4 != ' ' %} {{ row.4 }} руб. {% else %}   {% endif %}</td>
                                <td style="text-align: right;">{% if row.5 %} {{ row.5|floatformat:2 }} Мб {% else %}   {% endif %}</td>
                                <td style="text-align: right;">{% if row.6 %} {{ row.6|floatformat:2 }} Мб {% else %}   {% endif %}</td>
                                <td style="text-align: right;">{% if row.7 %} {{ row.7|floatformat:2 }} Мб {% else %}   {% endif %}</td>
                                <td style="text-align: right;">{% if row.8 %} {{ row.8|floatformat:2 }} руб. {% else %}   {% endif %}</td>
                            </tr>
                        {% endif %}
                    {% endfor %}
                </table>
            {% else %}
                <h3>Ничего не найдено.</h3>
            {% endif %}
         {% endif %}
        {% endblock %}
    {% endblock %}
    {% block js_addon %}
    <link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
    <script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
    <script type="text/javascript" src="/static/js/makedatetime.js"></script>
    <script type="text/javascript" src="/static/js/print.js"></script>
    <link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
    {% endblock %}
  109. Цены на направления за период
    select
    vd.mask as direction_mask,
    vd.id as direction_id,
    vd.name as direction_name,
    u.id as usluga_id,
    uv.active_from_date,
    round(uv.price/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 4) as price
    from usluga u
    JOIN usluga_voip uv on u.id=uv.usluga_id
    join CATEGORY_DIRECTIONS CD ON CD.CATEGORY_ID=uv.VOIPCATEGORY_ID
    join VOIP_CATEGORY VC ON CD.CATEGORY_ID=VC.ID
    join voip_direction vd on vd.id = cd.direction_id
    where (uv.active_from_date between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59')
    and vd.mask=:Маска направления$
    
  110. Отчёт по сумме списаний у абонентов. Списания суммируются из поля "Расход/предоплата" в финансовой информации абонента.
    select first 1
    'Дата начала периода ' || ':1-Начало|date$' as "Договор",
    'Дата окончания периода ' || ':2-Конец|date$ 23:59:59' as "ФИО",
    null as "Сумма"
    from abonents
    union all
    select distinct
    a.contract_number as "Договор",
    a.name as "ФИО",
    sum(round(aas.PRICE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма"
    from abonents a
    left join arch_account_stack aas on aas.ABONENT_ID = A.ID
    where a.deleted=0
    and a.id>0
    and a.is_folder=0
    and a.parent_id!=244
    and aas.storno=0
    and upper(aas.descr) not like upper('%Сторнирование%')
    and (BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59')
    and (select sum(PRICE) from arch_account_stack aas where aas.abonent_id=a.id and (BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'))/ (select CONST_VALUE
                                                               from VPN_CONST
                                                               where CONST_ID = 1)>0
    group by 1,2
    union all
    
    select '"Итого"',
           null,
           sum(SUMOP)
    from (select sum(round(aas.PRICE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as SUMOP
          from abonents a 
          left join arch_account_stack aas on aas.ABONENT_ID = A.ID
          where a.deleted=0
    and a.id>0
    and a.is_folder=0
    and a.parent_id!=244
    and aas.storno=0
    and upper(aas.descr) not like upper('%Сторнирование%')
    and (BILL_DATE between ':1-Начало|date$' and ':2-Конец|date$ 23:59:59'))
    
    
    
    
  111. Отчёт по задолженности абонента за месяц. Выводит баланс на 1 число месяца, платежи за месяц и расход. Необходимо выполнять отчёт с 1 по 31 число уже завершенного месяца.
    select AB.CONTRACT_NUMBER as "Номер договора",
           AB.NAME as "ФИО",
           round(FOact.BALANCE_BUH/10000000000.00,2) as "ОстатокНа1Число",
           paysum.FOpaysum as "Оплачено",
           round(sum(aas.price*v)/10000000000.00,2) as "Начислено"
    from ABONENTS AB
    left join FINANCE_OPERATIONS FOact on AB.ID = FOact.ABONENT_ID and FOact.OP_TYPE = 1 and (FOact.OP_DATE between ':1_C_даты|date$' and ':2_По_дату|date$ 23:59:59')
    left join ARCH_ACCOUNT_STACK aas on AB.ID = aas.ABONENT_ID and (aas.BILL_DATE between ':1_C_даты|date$' and ':2_По_дату|date$ 23:59:59')
    left join 
        (select FOpay.ABONENT_ID as ABpay,
            round(sum(FOpay.OP_SUMMA)/10000000000.00,2) as FOpaysum
            from FINANCE_OPERATIONS FOpay
            where
                FOpay.OP_TYPE = 2
                and FOpay.OP_DATE between ':1_C_даты|date$' and ':2_По_дату|date$ 23:59:59'
                and FOpay.ABONENT_ID=':3_Абонента$'
        group by 1
        ) as paysum on ab.id = paysum.ABpay
    where (AB.DELETED = 0
          or AB.DELETED is null)
          and FOact.STORNO=0
          and ab.id=':3_Абонента$'
    group by AB.CONTRACT_NUMBER, AB.NAME,3,4
    order by AB.CONTRACT_NUMBER, AB.NAME
    
  112. Отчёт по абонентам без расхода с июня 2020 года

    Отчёт нужен чтобы найти абонентов у которых не было расхода с определённого времени (в примере с 06.2020). Список поможет абонентскому отделу принять решения по абонентам, например обзвонить и принять решения - удалить окончательно в корзину с закрытием договора или мотивировать продолжать пользоваться услугами.
    Основная задача по которой делали отчёт - провести "ревизию" неактивных абонентов и удалить в корзину, чтобы не тратили ограничение лицензии.
    В крайнем правомстолбце можно посмотреть когда был последний расход.

    select distinct 
        a.contract_number "Номер договора",
        a.name "ФИО/Название",
        coalesce((select max(year_number*100+month_number) from counters where abonent_id=a.id),'никогда не пользовался') "Последний период"
    from 
        abonents a
    left join
        counters c -- соединяем чтобы найти абонентов с потреблением услуг в июне 2020 и позже, чтобы потом отсеить таких абонентов (дальше в where)
        on c.abonent_id=a.id
        and c.year_number=2020
        and c.month_number>=06
    where 
        a.is_folder=0 -- не папки, у них не может быть расхода и в выводе отчёта они нам не нужны
        and a.deleted=0 -- удалённые вне контекста (они уже удалены и не учитываются в лицензии)
        and a.id>0 -- исключаем тестовых системных абонентов 
        and coalesce(a.category_id,1)=1 -- считаем только абонентов, не операторо связи
        and a.parent_id<>'244' -- не учитываем служебную группу
        and cast(a.create_date_system as date)<cast('2020-07-01' as date) -- созданные в июле безусловно не могут иметь расхода за июнь и раньше, отсеиваем всех кто создан до "контрольного" месяца. 
        and c.id is null -- считаем только те, по кому не нашлось счётчиков потребления услуг позде
    order by 
        3 desc 
    
  113. Отчёт, показывающий абонентов с услугой, у которой вручную переопределена цена.
    select 
    a.name as "ФИО",
    a.contract_number as "Договор",
    round(uu.dinamyc_price / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Динамическая цена",
    (select usl.name from usluga usl left join users_usluga uu1 on uu1.usluga_id=usl.id where uu1.id=uu.id) as "Услуга"
    from users_usluga uu 
    left join abonents a on a.id=uu.abonent_id 
    where uu.dinamyc_price!=0 
    
  114. Отчёт покажет количество аткуальных направлений по услугам подключенным абоненту, и их общее количество
    select 
        count(distinct cd.id), -- количество направлений в услуге
        count(distinct uu.id), -- скольким пользователям услуга подключена
        u.id, -- ID услуги
        u.name 
    from 
        usluga u 
    join 
        usluga_voip uv -- в таблице хранятся актуальные цены категорий 
        on u.id=uv.usluga_id  
    join 
        category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим"
        on uv.voipcategory_id=cd.category_id 
    join
        users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена
        on u.id=uu.usluga_id and uu.deleted=0
    group by 
        u.id,
        u.name 
    order by 
        1
    
  115. Отчёт покажет количество аткуальных направлений по услугам
    select 
        count(distinct cd.id) "Кол. направлений", -- количество направлений в услуге
        cast(u.id as varchar(16)) "ID услуги", 
        u.name "Имя"
    from 
        usluga u 
    join 
        usluga_voip uv -- в таблице хранятся актуальные цены категорий 
        on u.id=uv.usluga_id  
    join 
        category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим"
        on uv.voipcategory_id=cd.category_id 
    join
        users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена
        on u.id=uu.usluga_id and uu.deleted=0
    where 
        uu.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
    group by 
        u.id,
        u.name 
    
    union
    
    select 
        count(distinct cd.id), -- количество направлений в услугах (суммарно)
        '',
        'Итого'
    from 
        usluga u 
    join 
        usluga_voip uv -- в таблице хранятся актуальные цены категорий 
        on u.id=uv.usluga_id  
    join 
        category_directions cd -- таблица для соединения категорий с направлениями по принципу "многие ко многим"
        on uv.voipcategory_id=cd.category_id 
    join
        users_usluga uu -- таблица с подключенными услугами абонентов чтобы посчитать скольким абонентам услуга подключена
        on u.id=uu.usluga_id and uu.deleted=0
    where 
        uu.abonent_id = ':Абонент|select[Abonents,is_folder=0]$'
    
    order by 
        1
    
  116. Отчёт по абонентам заключивших договор за период

    Отчёт покажет абонентов заключивших договор в выбранном периоде, в воде будут номер договора, ФИО, дата договора, текущий тариф и текущий баланс.

    select 
        a.contract_number "Номер договора",
        a.name "Название/ФИО",
        cast(a.create_date as date) "Дата договора",
        t.name "Тариф",
        round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / cast((select CONST_VALUE
                                                             from VPN_CONST
                                                             where CONST_ID = 1) as numeric(18,5)), 2) "Текущий баланс"
    from 
        abonents a 
    join 
        tarif t 
        on a.tarif_id=t.id 
    join 
        admin_accounts aa 
        on a.account_id=aa.id
    where 
        extract(month from a.CREATE_DATE)=extract(month from cast(':Дата в виде месяца|monthchoice|create_date|select[abonents,id__gt=1000,deleted=0,parent_id__gt=1000,is_folder=0]$' as date))
        and extract(year from a.CREATE_DATE)=extract(year from cast(':Дата в виде месяца|monthchoice|create_date|select[abonents,id__gt=1000,deleted=0,parent_id__gt=1000,is_folder=0]$' as date))
        and a.id>=1000
        and a.deleted=0
        and a.parent_id>=1000
        and a.is_folder=0
    
  117. Отчёт по начислениям по видам услуг за выбранный период

    Отчёт покажет начисления по видам услуг за выбранный период
    В case можно добавить собственную логику постороения отчёта в зависимости от натсроек услуг; в примере ниже:

    • услуги с типом стандартный будут посчитаны как "разовые" или "прочие" в зависимости от типа списания
    • все услути касающиеся интернет-трафика (форсаж, пакет МБ и тд) будут отражены просто как "трафик"
      select 
          c.year_number*100+c.month_number "Период",
          case 
              when ut.id=0 and u.usluga_abon_type_id=1 then 'Разовые услуги' --'Стандартный'
              when ut.id=0 and u.usluga_abon_type_id<>1 then 'Прочие услуги' --'Стандартный'
              when ut.id=1 then 'Интернет' --'Турбокнопка'
              when ut.id=3 then 'Интернет' --'Бонусный трафик'
              when ut.id=4 then 'Интернет' --'Форсаж'
              when ut.id=2 then 'Прочие услуги' --'Подписка'
              when ut.id=6 then 'Интернет' --'Пакет МБ'
              when ut.id=5 then  'IP телефония'
              when ut.id=7 then  'IP телевидение'
              when ut.id=8 then  'Абонентская плата'
              when ut.id=9 then 'Интернет' --'Трафик'
              when ut.id=10 then  'Скидка\Наценка'
              when ut.id=11 then  'Обещанный платеж'
              when ut.id=12 then  'Пакет услуг'
              when ut.id=13 then  'Системные услуги'
              when ut.id=14 then 'Прочие услуги' --'Счетчик'
          end "Тип услуг",
          round(sum(c.summ),2) "Сумма"
      from 
          counters c 
      join 
          usluga u 
          on c.usluga_id=u.id 
      join 
          usluga_type ut 
          on u.system_type=ut.id 
      where 
          c.summ<>0
          and cast(c.year_number || '-' || c.month_number || '-01' as date) 
              between cast(':1-С периода|monthchoice|6$' as date) 
              and cast(':2-По период|monthchoice|bill_date|select[ArchAccountStack,storno=0]$' as date)
      group by 
          1,2
      order by 
          :3-Сортировать по|choices[1,2^]Месяцу^[2,1^]Типу услуг]$
      
  118. Отчёт по отправленным сообщениям из аудита
    select
    a.name as "ФИО",
    a.contract_number as "Договор",
    ao.descr as "Описание",
    ao.OP_TIME as "Дата"
    from audit_operations ao
    left join abonents a on a.id=ao.abonent_id
    where  ao.descr like '%Сооб%'  and ao.abonent_id is not null 
    and ao.OP_TIME  between ':1-date_start|date$' and ':2-date_end|date$ 23:59:59'
    
  119. Вывод абонентов у кого подключен автоплатеж
    select 
    a.id, 
    a.contract_number as " номер договора", 
    a.name as "ФИО", 
    iif(a.autopay_pay_log_id is not null, 'Включен', ' ') as "автоплатеж"
    from abonents a
    where 
    a.is_folder=0 
    and a.deleted=0 
    and a.parent_id not in (2,4,244)
    and a.autopay_pay_log_id  is not null
    
  120. Анализ проводок по абонентам и выявление большого количества записей у абонентов(более 500 записей в месяц, это значение можно менять для отсортировки результатов) в таблице ARCH_ACCOUNT_STACK.
    select 
    ABONENT_ID,
    extract(year from CHANGE_BALANCE_TIME),
    extract(month from CHANGE_BALANCE_TIME),
    count(*) 
    from ARCH_ACCOUNT_STACK
    group by 1,2,3  
    having count(*)>500
    
  121. Список задач в HelpDesk и CRM для выгрузки и обработки средствами Excel
    select
        HDSK.id "#",
        coalesce(ht.NAME,'Не указан') as "Типы заявок",
        HDSK.SUBJ as "Тема",
        a.name "Абонент",
        coalesce(a_tel.attribute_value,'') "Телефон",
        t.name "Тариф",
        coalesce(h.street,'') || ' ' || coalesce(h.s_number,'')  || ' п. ' || coalesce(cast(a.HOME_ENTRANCE as varchar(16)),'') || ' кв. ' || coalesce(cast(a.a_HOME_number as varchar(16)),'') "Адрес",
        lpad(coalesce(cast(hdsk.HDSK_DATETIME as varchar(32)),''),19) "Дата",
        hs.NAME as "Статус",
        lpad(coalesce(cast(hdsk.CLOSED_WHEN as varchar(32)),''),19) "Когда исполнено",
        hdsk.text "Описание заявки"
    from HDSK
    left join HDSK_STATUS hs on hdsk.STATUS=hs.id
    left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
    left join abonents a_adm on a_adm.id=hdsk.PERFORMED_WHOM
        left join auth_user au on au.USERNAME=a_adm.name
    left join abonents a on a.id=hdsk.abonent_id
        left join homes h on a.home_id=h.id
        left join tarif t on a.home_id=t.id
        left join attribute_values a_tel on a.id=a_tel.abonent_id and attribute_id=1158
    where 
        coalesce(hdsk.IS_TASK,0)<>:Заявки HelpDesk или задачи?|choices[2^]Все^[1^]Заявки HelpDesk^[0^]Задачи]$
        and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$ 23:59:59')
    

    Тот же отчёт, но в поле "Телефон" перечисленны выданные абоненту телефонные номера

    select
        HDSK.id "#",
        coalesce(ht.NAME,'Не указан') as "Типы заявок",
        HDSK.SUBJ as "Тема",
        a.name "Абонент",
        coalesce(phones_list,'') "Телефон",
        t.name "Тариф",
        coalesce(h.street,'') || ' ' || coalesce(h.s_number,'')  || ' п. ' || coalesce(cast(a.HOME_ENTRANCE as varchar(16)),'') || ' кв. ' || coalesce(cast(a.a_HOME_number as varchar(16)),'') "Адрес",
        lpad(coalesce(cast(hdsk.HDSK_DATETIME as varchar(32)),''),19) "Дата",
        hs.NAME as "Статус",
        lpad(coalesce(cast(hdsk.CLOSED_WHEN as varchar(32)),''),19) "Когда исполнено",
        hdsk.text "Описание заявки"
    from HDSK
    left join HDSK_STATUS hs on hdsk.STATUS=hs.id
    left join HDSK_TYPE HT on HT.id=HDSK.HDSK_TYPE_ID
    left join abonents a_adm on a_adm.id=hdsk.PERFORMED_WHOM
        left join auth_user au on au.USERNAME=a_adm.name
    left join abonents a on a.id=hdsk.abonent_id
        left join homes h on a.home_id=h.id
        left join tarif t on a.home_id=t.id
        left join (select 
                    list(ppc.phone) phones_list,
                    u.abonent_id
                from users u 
                join phone_pull_chache ppc on u.phone=ppc.id 
                where u.deleted=0 and u.phone is not null
                group by u.abonent_id
            ) phones on a.id=phones.abonent_id
    where 
        coalesce(hdsk.IS_TASK,0)<>:Заявки HelpDesk или задачи?|choices[2^]Все^[1^]Заявки HelpDesk^[0^]Задачи]$
        and HDSK.HDSK_DATETIME between (':1 Дата|date$') and (':2 Дата|date$ 23:59:59')
    
  122. Потребленный трафик за год с разбивкой по месяцам и возможностью выбора группы абонентов.
    select 
    distinct c.month_number as "месяц",
    inp.sum_in as "Входящий",
    outp.sum_out as "Исходящий"
    from counters c
    
    left join (select cast(sum(v) AS numeric(18,2)) sum_in,
    month_number mont
    from counters 
    
    WHERE YEAR_NUMBER = (':Год|choices[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
           AND UNIT_ID = 2
         AND V_TYPE_ID = 1
    	 and abonent_id in (SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(':Группа|select[Abonents,is_folder=1]$'))
    	 group by mont) inp on c.month_number=inp.mont
    	 
    left join (select cast(sum(v) AS numeric(18,2)) sum_out,
    month_number mont
    from counters 
    
    WHERE YEAR_NUMBER = (':Год|choices[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
           AND UNIT_ID = 2
         AND V_TYPE_ID = 2
    	 and abonent_id in (SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(':Группа|select[Abonents,is_folder=1]$'))
    	 group by mont) outp on c.month_number=outp.mont
    where c.YEAR_NUMBER = (':Год|choices[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
    
  123. Юрлица без ИНН

    Можно ограничить выборку только выбранной папкой, если все юрлица находятся в ней - в функции GLN_RECURSIVE_ABONENTS_GET укажите ID нужной папки.
    Для доп. соглашений учитывается ИНН основного договора.

    select 
        a.id,
        a.name,
        a.contract_number
    from 
    abonents a 
    join 
        (SELECT abonent_id FROM GLN_RECURSIVE_ABONENTS_GET(1)) as jur_abon_list 
        on a.id=jur_abon_list.abonent_id
    left join 
        ATTRIBUTE_VALUES as av_inn_abon 
        on a.id=av_inn_abon.ABONENT_ID 
        and av_inn_abon.ATTRIBUTE_ID=4 
        and coalesce(av_inn_abon.ATTRIBUTE_VALUE,'')<>'' --ИНН
    left join 
        ATTRIBUTE_VALUES as av_inn_master 
        on a.master_id=av_inn_master.ABONENT_ID 
        and av_inn_master.ATTRIBUTE_ID=4 
        and coalesce(av_inn_master.ATTRIBUTE_VALUE,'')<>'' --ИНН основного договора, если это доп. соглашение
    where 
        a.company=1
        and av_inn_abon.id is null
        and av_inn_master.id is null
    
  124. Отчет формирует информацию за период по следующим полям: входящий баланс абонента, списания за указанный месяц, оплачено, исходящий баланс, НДС 20% от списаний.

    Если нет необходимости разделять на типы услуг, то можно убрать поля и скорректировать нахождение "Исходящего остатка"

    select  distinct 
    a.contract_number as "№ договора",
    (select first 1 login from users where users.abonent_id=a.id) as "Логин",
    a.name as "Название/ФИО",
    iif(a.company=1, 'Юр. лица','Физ. лица') as "Физ./Юр.",
    coalesce(tel.attribute_value,'') as "Телефон",
    a.account_id as "№ лиц.сч",
    
    /*Выполянется проверка, какие данные выводить по входящему балансу, в зависимости от даты создания абонента*/
    case
      when a.create_date_system between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and 
                     dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
        then 
            (select first 1 fost.aa_ostatok/10000000000.00
                from finance_operations fost
                where fost.storno = 0
                  and fost.abonent_id = a.id
                  and (extract(year from fost.period_end_date) = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and
                      extract(month from fost.period_end_date) = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)))
                order by fost.system_date)
      
      when coalesce(foincome2.summa, 0) != 0
           then  
           foincome2.summa   
    else round((coalesce(foincome.balance_buh / 10000000000.00, 0.00)), 2) 
    end                             as "Входящий остаток",
    round(coalesce(onetime.summa, 0.00), 2)                    as "Разовые услуги",
    round(coalesce(constserv.summa, 0.00), 2)                    as "Периодические услуги",
    round(coalesce(voiptraf.summa, 0.00),2) as "IP-телефония",
    iif(a.company=1, round((coalesce(onetime.summa, 0.00) + coalesce(constserv.summa, 0.00) + coalesce(voiptraf.summa, 0.00))/100*20,2),'0.00') as "НДС",
    round(coalesce(onetime.summa, 0.00) + coalesce(constserv.summa, 0.00) + coalesce(voiptraf.summa, 0.00),2) as "Сумма с налогами",
    coalesce(debet.summa, 0.00)     as "Оплачено",
    round((coalesce(foincome2.summa, 0.00)) + coalesce(debet.summa, 0.00) - coalesce(onetime.summa, 0.00) - coalesce(constserv.summa, 0.00) - coalesce(voiptraf.summa, 0.00), 2) as "Исходящий остаток"
       
    from abonents a
    join tarif t on a.tarif_id=t.id
    left join finance_operations foincome on foincome.storno = 0 and a.id = foincome.abonent_id and foincome.op_type = 1 and
                                        dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) = cast(foincome.period_end_date as date)
                        /*получаем бух.баланс из акта за предыдущий месяц от искомого*/
    
                           left join (select round(fo.balance_buh / 10000000000.00, 2) summa, fo.abonent_id abon
                      from finance_operations fo
                      where fo.storno = 0 and fo.op_type = 1
                      and (
                            extract(year from fo.period_end_date)  = extract(year  from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) and
                            extract(month from fo.period_end_date) = extract(month from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)))
                        )) foincome2 on a.id = foincome2.abon
                        
             /*получаем сумму приходов за искомый месяц*/
    left join (select round(sum(fo.op_summa / 10000000000.00), 2) summa,
                             fo.abonent_id                            abon
                      from finance_operations fo
                      where fo.storno = 0
                        and fo.op_type = 2
                        and fo.op_date between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and dateadd(-1 second to dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as timestamp)))
                      group by fo.abonent_id) debet on a.id = debet.abon
                      
                      /*сумма начислений за искомый месяц по разовым услугам*/
    left join (select sum(c.summ)  summa, c.abonent_id abon
                      from counters c
                      join usluga u on c.usluga_id = u.id
                      where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                        and c.year_number  = extract(year  from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                      and u.usluga_abon_type_id=1
                      group by c.abonent_id) onetime on a.id = onetime.abon
                      
                      /*сумма начислений за искомый месяц по периодическим услугам*/
    left join (select sum(c.summ)  summa, c.abonent_id abon
                      from counters c
                      join usluga u on c.usluga_id = u.id
                      where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                        and c.year_number  = extract(year  from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                      and u.usluga_abon_type_id<>1 
                      and  c.unit_id<>3
                      group by c.abonent_id) constserv on a.id = constserv.abon
                      
                      /*сумма начислений за искомый месяц по телефонии*/
    left join (select sum(c.summ)  summa, c.abonent_id abon
                      from counters c
                      join usluga u on c.usluga_id = u.id
                      where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                        and c.year_number  = extract(year  from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                      and u.usluga_abon_type_id<>1 
                      and c.unit_id=3
                      group by c.abonent_id) voiptraf on a.id=voiptraf.abon  
                      
    left join
        attribute_values tel on a.id=tel.abonent_id and tel.attribute_id=1009
                    
    where
    a.is_folder=0 and
    coalesce(a.company,0)<>:1-Физ./Юр. лица|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$
    and a.deleted = 0
    and a.id > 0
    and a.parent_id not in (2,244) /*Исключить каталог "Операторы" и "Служебная группа"*/
    order by
        1
    
    Упрощенный вариант "Входящий баланс" - "Оплачено" - "Списано" - "Исходящий баланс"
    select  distinct 
    a.contract_number as "№ договора",
    (select first 1 login from users where users.abonent_id=a.id) as "Логин",
    a.name as "Название/ФИО",
    iif(a.company=1, 'Юр. лица','Физ. лица') as "Физ./Юр.",
    /*Выполянется проверка, какие данные выводить по входящему балансу, в зависимости от даты создания абонента*/
    case
      when a.create_date_system between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and 
              dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
        then 
            (select first 1 fost.aa_ostatok/10000000000.00
                from finance_operations fost
                where fost.storno = 0
                  and fost.abonent_id = a.id
                  and (extract(year from fost.period_end_date) = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and
                       extract(month from fost.period_end_date) = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)))
                order by fost.system_date)
      
      when coalesce(foincome2.summa, 0) != 0
           then  
           foincome2.summa   
    else round((coalesce(foincome.balance_buh / 10000000000.00, 0.00)), 2) 
    end                             as "Входящий остаток",
    coalesce(debet.summa, 0.00)     as "Оплачено",
    round(coalesce(onetime.summa, 0.00), 2)                    as "Начислено",
    round((coalesce(foincome2.summa, 0.00)) + coalesce(debet.summa, 0.00) - coalesce(onetime.summa, 0.00), 2)     as "Исходящий остаток"
    from abonents a
    join tarif t on a.tarif_id=t.id
    left join finance_operations foincome on foincome.storno = 0 and a.id = foincome.abonent_id and foincome.op_type = 1 and
                                        dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) = cast(foincome.period_end_date as date)
                           /*получаем бух.баланс из акта за предыдущий месяц от искомого*/
    
                           left join (select round(fo.balance_buh / 10000000000.00, 2) summa, fo.abonent_id abon
                      from finance_operations fo
                      where fo.storno = 0 and fo.op_type = 1
                      and (
                            extract(year from fo.period_end_date)  = extract(year  from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))) and
                            extract(month from fo.period_end_date) = extract(month from dateadd(-1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)))
                        )) foincome2 on a.id = foincome2.abon
                        
             /*получаем сумму приходов за искомый месяц*/
    left join (select round(sum(fo.op_summa / 10000000000.00), 2) summa,
                             fo.abonent_id                            abon
                      from finance_operations fo
                      where fo.storno = 0
                        and fo.op_type = 2
                        and fo.op_date between cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date) and dateadd(-1 second to dateadd(1 month to cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as timestamp)))
                      group by fo.abonent_id) debet on a.id = debet.abon
    /*cумма списаний  за искомый месяц*/
    left join (select sum(c.summ)  summa, c.abonent_id abon
                      from counters c
                      join usluga u on c.usluga_id = u.id
                      where c.month_number = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                        and c.year_number  = extract(year  from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date))
                      group by c.abonent_id
             ) onetime on a.id = onetime.abon
    
                      
    where
    a.is_folder=0
    and a.deleted = 0
    and a.id > 0
    and  a.parent_id not in (2, 244)
    
  125. Отчет, выводящий номер договора, ФИО, адрес, баланс, линейку услуг с возможностью выбора линейки услуг
    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 "Баланс",
    urt.name 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
    join tarif t on t.id=a.tarif_id
    left join USLUGA_RANGE_TYPES urt on t.USLUGA_RANGE_TYPE_ID=urt.id
    where a.deleted=0 
    and a.is_folder=0
    and a.parent_id not in (2,244)
    and urt.id = ':Линейка услуг|select[UslugaRangeTypes]$'
    
  126. Отчет по абонентам у которых не был выставлен акт за период.
    select  a.id
    from abonents a 
    where  a.is_folder=0
    and a.deleted = 0
    and a.id > 0
    and  a.parent_id not in (2, 244)
    and a.id not in (select abonent_id aid
                from finance_operations fost
                where fost.storno = 0
                  and fost.op_type = 1
                  and (extract(year from fost.period_end_date) = extract(year from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)) and
                      extract(month from fost.period_end_date) = extract(month from cast(':Расчетный период|monthchoice|period_end_date|select[financeoperations]$' as date)))
                order by fost.op_date)
    
    
  127. Отчет по radius-атрибутам NAS.
    select 
    n.name as "NAS", 
    nrp.ATTRIBUTE as "Атрибут", 
    nrp.THEVALUE as "Значение",
    case when nrp.BALANCE_STATUS_ID=0 then 'В любом случае'
    when nrp.BALANCE_STATUS_ID=1 then 'При отрицательном балансе'
    when nrp.BALANCE_STATUS_ID=2 then 'При положительном балансе' 
    else ''
    end as "Статус баланса",
    case when nrp.BLOCK_STATUS_ID=0 then 'В любом случае'
    when nrp.BLOCK_STATUS_ID=1 then 'Заблокирован'
    when nrp.BLOCK_STATUS_ID=2 then 'Не заблокирован' 
    else ''
    end as "Статус блокировки"
    from nas n 
    join NAS_RADIUS_PARAMS nrp on nrp.nas_id=n.id
    
  128. Список абонентов, у которых есть учетная запись IPTV с выбором даты изменения услуг.
    select distinct  a.id,a.name, us.login --отбираем логин
    from abonents a 
    join users us on us.abonent_id=a.id and us.nas_id=1126
    left join users_usluga uu on a.id=uu.abonent_id
    join users_usluga_history uuh on uuh.abonent_id=a.id
    where 
    a.id>0 
    and a.is_folder=0
    and a. parent_id not in (2,244)
    and uuh.time_changed between (':1 Дата|date$') and (':2 Дата|date$')
    and uuh.time_del is null
    
  129. Абоненты-номер договора-статус/подстатус абонента
     
    select ab.name as "ФИО", ab.contract_number as "Номер договора", s.name as "Статус"
    from   abonents ab
    left join objects_status os on os.object_id=ab.id
    left join status  s on s.id = os.status
    where 
    s.id = ':Статус|select[status]$' 
    and extract (DAY from ab.activate_date) >0 
    and ab.id>0 
    and ab.deleted=0
    
  130. Список абонентов IPTV с выбором даты блокировки услуги.
    select  distinct a.id, 
    a.name as "Имя абонента", 
    us.id as "Номер услуги в справочнике", 
    us.name as "Название услуги", 
    abh.b_date as "Дата блокировки"
    from abonents a 
    join users u on u.abonent_id=a.id
    left join abonents_block_history abh on abh.abonent_id=a.id
    left join users_usluga uu on abh.abonent_id=uu.abonent_id
    left join usluga us on us.id=uu.usluga_id
    where 
    a.id>0 
    and a.deleted=0
    and a.is_folder=0
    and a.parent_id not in (2,244) 
    and  abh.b_date between (':1 Дата|date$') and (':2 Дата|date$') and  abh.time_del is null and us.system_type=7 /*отобрать услуги IPTV по дате блокировки */
    and u.nas_id=1111 /*номер NAS IPTV к которому относятся пользователи*/
    order by a.id
    
  131. Неудалённые абоненты интернет по типу авторизации
    /*
    Учитываются только:
    - Неудалённые абоненты;
    - С адресом IPv4
    */
    select 
        count(*) "Количество абонентов",
        case
            when u.auth_type is null then '---------'
            when u.auth_type=0 then 'по vpn pptp, pppoe'
            when u.auth_type=1 then 'по ip static'
            when u.auth_type=2 then 'по CarbonAgent'
            when u.auth_type=3 then 'по ip+CarbonAgent'
            when u.auth_type=4 then 'через веб'
            when u.auth_type=5 then 'через ip+веб'
            when u.auth_type=6 then 'любая через RADIUS'
            when u.auth_type=8 then 'по OPT82'
            when u.auth_type=9 then 'по ip+radius'
        end "Тип авторзации"
    from 
        users u 
    where 
        u.id>0
        and u.ip is not null 
        and u.deleted=0
    group by 
        u.auth_type
    
  132. Найти список абонентов, у которых дата начала добровольной блокировки была назначена в прошлом относительно даты включения.

    В отчете указываем период, в течение которого необходимо проверить события.

    /*Данные в отчете будут представлены из таблицы Аудит*/
    select distinct aid as "ID абонента", ana as "ФИО", opdate as "Дата события", dateblo as "Дата доб.блок."
    from 
    (select a.id aid, a.name ana, cast(ao.op_time as date) opdate, ao.descr aodes, cast(substring(ao.descr from 69 for 10) as date) dateblo
    from audit_operations ao
    left join abonents a on ao.abonent_id=a.id
    where ao.op_time  between ':1.Начало периода|date$' and ':2.Конец периода|date$'  
    and a.id>0 and ao.descr like '%Добровольная блокировка c %'
    )
    where opdate>dateblo
  133. Отчет который выведет расход абонента за указанный период, с итогами по месяцам и по всей выборке
    select
        c.YEAR_NUMBER as "Год",
        c.MONTH_NUMBER as "Месяц",
        u.name || ' (ID=' || u.id || ')' as "Услуга",
        round(round(round(sum(c.SUMM),4),3),2) as "Сумма",
        Null "Итого месяц",
        Null "Итого отчёт"
    from
        counters c 
    join 
        abonents a 
        on c.abonent_id=a.id
    join 
        usluga u 
        on c.usluga_id=u.id 
    where 
        a.contract_number=':3-Номер договора$'
        and cast(c.YEAR_NUMBER || '-' || c.MONTH_NUMBER || '-01' as timestamp) between ':1-С какого периода|monthchoice|100$' and ':2-По какой период|monthchoice|100$'
    group by 
        1,2,3
    
    union
    
    select
        c.YEAR_NUMBER as "Год",
        c.MONTH_NUMBER as "Месяц",
        ' Итого за ' || c.YEAR_NUMBER || '.' || c.MONTH_NUMBER,
        Null,
        round(round(round(sum(c.SUMM),4),3),2),
        Null
    from
        counters c 
    join 
        abonents a 
        on c.abonent_id=a.id
    join 
        usluga u 
        on c.usluga_id=u.id 
    where 
        a.contract_number=':3-Номер договора$'
        and cast(c.YEAR_NUMBER || '-' || c.MONTH_NUMBER || '-01' as timestamp) between ':1-С какого периода|monthchoice|100$' and ':2-По какой период|monthchoice|100$'
    group by 
        1,2,3
    
    union 
    
    select
        9999999999 as "Год", -- 9999999999 чтобы при выгрузке в Excel Год и Месяц в детализации оставались числами и "Итого" был последней строкой
        9999999999 as "Месяц",
        'Итого за весь  период' as "Услуга",
        Null,
        Null,
        round(round(round(sum(c.SUMM),4),3),2)
    from
        counters c 
    join 
        abonents a 
        on c.abonent_id=a.id
    join 
        usluga u 
        on c.usluga_id=u.id 
    where 
        a.contract_number=':3-Номер договора$'
        and cast(c.YEAR_NUMBER || '-' || c.MONTH_NUMBER || '-01' as timestamp) between ':1-С какого периода|monthchoice|100$' and ':2-По какой период|monthchoice|100$'
    group by 
        1,2,3
    
    order by 
        1,2,3,4
    
  134. Отчёт покажет в какие тарифы входят услуги
    /* 
    
    Отчёт выведет все услуги, включённые в тарифы:
    - в левой колонке - услуга;
    - в правой - тарифы, в которые она входит, через запятую.
    
    */
    
    select 
        '(ID=' || usl.id || ')' || usl.name "Услуга",
        list('(ID=' || t.id || ')' || t.name) "Тарифы"
    from 
        usluga usl 
    join 
        tarif_users_usluga tuu 
        on usl.id=tuu.usluga_id
        join 
            tarif t 
            on tuu.tarif_id=t.id 
    group by 
        1
    
  135. Отчет отбирает абонентов по каталогу и текущему статусу
    select ab.name as "ФИО", ab.contract_number as "Номер договора", s.name as "Статус"
    from   abonents ab
    left join objects_status os on os.object_id=ab.id
    left join status  s on s.id = os.status
    where 
    s.id = ':Статус|select[status]$' 
    and extract (DAY from ab.activate_date) >0 
    and ab.id>0 
    and ab.deleted=0
    and ab.parent_id in (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(':Абонент|select[abonents,is_folder=1]$' ))
  136. Общая статистика по звонкам с разделением на типы трафика и статус: обработанные и не обработанные
    select 
        calls.per_first_date "Период",
        sum(in_call) "Входящие",
        sum(out_call) "Исходящие",
        sum(tranzit_call) "Транзитные",
        sum(null_type) "Не определено",
        sum(not_billed) "Ждут обработку",
        sum(billed) "Обработаны",
        count(*) "Всего вызовов"
    from 
        (
        select
            case when billed=1 then 1 else 0 end billed,
            case when billed=0 then 1 else 0 end not_billed,
            case when v_type_id=0 then 1 else 0 end stdt,
            case when v_type_id=1 then 1 else 0 end in_call,
            case when v_type_id=2 then 1 else 0 end out_call,
            case when v_type_id=3 then 1 else 0 end tranzit_call,
            case when v_type_id is null then 1 else 0 end null_type,
            cast(s_time-extract(day from s_time)+1 as date) per_first_date
        from 
            voip_log
        ) calls
    group by 
        calls.per_first_date
    order by 
        1
    
  137. Отчет: выводить ответственного, кто включил опцию "Не отключать при превышении порога".
     select distinct
    a.contract_number as "№ договора",
    a.NAME as "ФИО",
    aa.UNLIMITED as "Не отключать",
    coalesce(AU.USERNAME,'') as "Ответственный"
    from ADMIN_ACCOUNTS aa
    left join ABONENTS a on a.ACCOUNT_ID=aa.ID
    left join audit_operations ao on a.id=ao.abonent_id
    left join AUTH_USER as AU on au.ID=ao.admin_id
    where a.IS_FOLDER=0
    and a.id>0 /* исключить служебные записи*/
    and a.parent_id<> 2 /* Исключить каталог "Операторы"*/
    and aa.UNLIMITED=1 
    and a.deleted=0  /* Выводить не удаленных абонентов*/
    and ao.descr like '%Не отключать при превышении порога=True%'
    order by a.NAME
    
  138. Номер договора абонента и номер договора главного абонента.
    select
        a.contract_number as N_cont,
        a_master.contract_number as N_cont_main
    from abonents a left join abonents a_master on a.master_id=a_master.id
    
  139. Отчёт для платёжной системы "Сбербанк" в формате "Номер договора;ФИО;Сумма предоплаты на следующий месяц"
    -- Чтобы убрать заголовок. В начале файла будут две пустые строки.
    select first 1 ''
    from abonents
    
    union
    
    select distinct
    -- 1 поле - номер догововра; 
        a.contract_number || ';' ||
    -- 2 поле - ФИО; 
        a.name || ';' ||
    -- 3 поле - Сумма предоплыты на след месяц, при условии что на счету нет денег на след месяц.
        cast(ac.recomend_pay_sum_cache/10000000000.00 as varchar(32))
    from 
        abonents a
    join
        homes h
        on a.home_id=h.id
    join
        abonents_cache ac
        on a.id=ac.id
    where
        coalesce(a.company,0) = 0
    
  140. Отчёт для платёжной системы "Примбанк" (ПАО АКБ Приморье) в формате "Номер договора|телефон|ФИО|Сумма предоплаты на следующий месяц|Дата, последний день предыдущего месяца|Предыдущий месяц|Вид услуги|"

    Отчёт строется только для физлиц. В папке физлица абоненты разбиты так же по папкам: DSL, GPON и Смотрёшка.
    Если у абонента несколько разных услуг (например, Смотрёшка и GPON), в биллинге для него заводятся отдельные абоненты - один в папке "Смотрёшка", другой в папке GPON.
    Все абоненты находятся только в этих трёх папках, в них нет вложенных папок.

    select
    -- 1 поле - № договора
        a.contract_number || '|' ||
    -- 2 поле - телефон
        coalesce(a.sms,'') || '|' ||
    -- 3 поле - ФИО
        a.name || '|' ||
    -- 4 поле - сумма
        cast(ac.recomend_pay_sum_cache/10000000000.00 as varchar(32)) || '|' ||
    -- 5 поле - дата, всегда последний день предыдущего месяца
        CAST(lpad(EXTRACT(DAY FROM ld.prev_month_last_date),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM ld.prev_month_last_date),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM ld.prev_month_last_date) || '|' ||
    -- 6 поле - пред месяц
        EXTRACT(YEAR FROM ld.prev_month_last_date) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM ld.prev_month_last_date),2,'0') AS varchar(2)) || '|' ||
    -- 7 Вид услуги: интернет, Смотрешка и телефон.
        case
            when a.parent_id=1369 then 'Интернет|' -- GPON
            when a.parent_id=1370 then 'Интернет|' -- DSL
            when a.parent_id=1593 then 'Смотрёшкака|' -- Смотрёшка
            else 'Услуги связи|'
        end
    from 
        abonents a
    join
        homes h
        on a.home_id=h.id
    join
        abonents_cache ac
        on a.id=ac.id
    join
        (
            SELECT 
                0 as fake_id,
                DATEADD (
                    -1 day TO DATEADD (1 MONTH TO CAST(EXTRACT(YEAR FROM
                    CURRENT_DATE)||'-'||EXTRACT(MONTH FROM CURRENT_DATE)||'-01' AS DATE))
                ) as last_date,
                dateadd (
                    -1 month to DATEADD (-1 day TO DATEADD (1 MONTH TO CAST(EXTRACT(YEAR FROM
                    CURRENT_DATE)||'-'||EXTRACT(MONTH FROM CURRENT_DATE)||'-01' AS DATE)))
                ) as prev_month_last_date
            FROM RDB$DATABASE
        ) ld
        on a.id>ld.fake_id
    join
        -- Берём абонентов только из папки для физиков
        (SELECT group_id FROM GLN_RECURSIVE_GROUP_WALK(1358)) g
        on g.group_id  = a.parent_id
    where
        coalesce(a.company,0) = 0
    
  141. Отчёт по использованию услуги социального интернета
    select 
        -- 1. Точное время не записывается в users_usluga, сохраняется только дата, время всегда 00:00:00.
        --    Можно было бы считать от TIME_DEL, отняв 24 часа, но если услугу отключат раньше, что возможно, 
        --    То дата подключения будет неверной.
        -- 2. MAX, т.к. если в истории несколько записей (а их должно быть как минимум две - вкл. и выкл.)
        --    реальное время будет только в первой, в остальных - "00:00:00"
        max(uu_create.create_date) "Дата подключения",
        -- Нельзя конвертировать в varchar, т.к. если будут выгружать в Excel, вместо даты будет просто текст
        -- Это хуже, чем 'Null' при выполнении из интерфейса
        max(uu_del.time_del)  "Дата отключения",
        uu.id "ID подкл. услуги",
        a.contract_number "Договор",
        a.name "ФИО"
    from 
        users_usluga uu
    join
        usluga u
        on uu.usluga_id = u.id
        -- У услуг соц. интернета отдельный системный тип ID=15
        and u.system_type=15
    join 
        users_usluga_history uu_create
        on uu.id=uu_create.users_usluga_id
        and uu_create.time_del is null
    left join 
        users_usluga_history uu_del
        on uu.id=uu_del.users_usluga_id
        and uu_del.time_del is not null
    join
        abonents a on uu.abonent_id=a.id
    group by
        -- 3, 4, 5, 6
        3, 4, 5
    order by 
        1 :Сортировка по дате подключения|choices[desc^]Сначала новые^[asc^]Сначала старые]$
    
  142. Отчёт по количеству абонентов с доп. соглашениями

    Отчёт выведет статистику по абонентам и доп. соглашениям. После статистики будет топ. абонентов по количеству доп. соглашений, по-умолчанию 10, но в форме можно выбрать вывести всех.

    select 'Суммарно', count(*) "Количество" from abonents where is_folder=0 and id>0 and deleted=0
    
    union all
    
    select 'Доп.соглашений', count(*) "Количество" from abonents where is_folder=0 and id>0 and deleted=0 and master_id is not null
    
    union all
    
    select 'Абонентов', count(*) "Количество" from abonents where is_folder=0 and id>0 and deleted=0 and master_id is null
    
    union all
    
    select 'Абонентов с доп.соглашениями', count(distinct master_id) "Количество" from abonents where is_folder=0 and id>0 and deleted=0 and master_id is not null
    
    union all
    
    select first 1 '-----------------------', Null from vpn_const
    
    union all
    
    select first 1 'ТОП 10', Null from vpn_const
    
    union all
    
    select first 1 '-----------------------', Null from vpn_const
    
    union all
    
    select 
        first :Сколько вывести абонентов с допниками|choices[10^]Топ 10^[1500000^]Все]$ * 
        -- first 10 * 
    from (
        select 
            'Основной договор: ' || a.contract_number || ', Название-ФИО: ' || a.name contr_name, 
            count(*) cnt
        from 
            abonents dopnik 
        join 
            abonents a 
            on dopnik.master_id=a.id 
        where 
            dopnik.is_folder=0 
            and dopnik.id>0 
            and dopnik.deleted=0 
            and dopnik.master_id is not null 
        group by 
            a.contract_number, 
            a.name
        order by 2 desc
    ) abon
    where 
        abon.cnt>=1
    
  143. Отчёт, выводящий номер договора абонента, название услуги и персональную стоимость услуги для тех абонентов, у которых есть услуги с персональной стоимостью
    select a.contract_number as "Договор",
    u.name as "Услуга",
    round(uu.DINAMYC_PRICE  / cast((select CONST_VALUE
                                                             from VPN_CONST
                                                             where CONST_ID = 1) as numeric(18,5)), 2) as "Цена"
    from abonents a 
    join users_usluga uu on a.id=uu.abonent_id
    join usluga u on u.id=uu.usluga_id
    where a.is_folder=0
    and a.deleted=0
    and uu.deleted = 0
    and coalesce(uu.DINAMYC_PRICE,0)!=0
    
  144. Отчет, выводящий порог отключения абонента, для тех абонентв, у кого порог отключения не равен 0.
    select
    a.ID as "ID",
    a.NAME as "ФИО",
    aa.LIMIT as "Порог отключения"
    from ABONENTS a
    left join ADMIN_ACCOUNTS aa on a.ACCOUNT_ID=aa.ID
    where a.IS_FOLDER=0
    and a.deleted= 0
    and aa.LIMIT != 0
    
  145. Отчет ID-Название услуги-Тип услуги-Вид сервиса-В архиве(или нет)
    select
    u.ID,
    u.NAME as "Название",
    ut.name as "Тип",
    st.name as "Вид сервиса",
    archived as "В архиве"
    from usluga u
    join usluga_type ut on u.system_type=ut.id
    join service_type st on u.service_type_id=st.id
  146. Отчет по объемам потребленного трафика абонентом.

    Будет работать лишь после включения опции "Сохранять движения всего трафика"

    select t1.trafab as "ID абонента",
    t1.aname1 as "ФИО",
    t1.ttime as "Дата",
    t1.uname as "Услуга",
    t1.inn as "Входящий",
    t2.out as "Исходящий"
    
    from
    
     (select distinct aas.abonent_id as trafab,
                a.name as aname1,    
                cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as ttime,
                u.name as uname,
                iif(aas.V_TYPE_ID=1, cast(round(sum(aas.v),2) as varchar(32)),'') as INN
                from arch_account_stack aas                --Трафик считаем по списаниям
                left join usluga u on aas.USLUGA_ID = u.id --Из услуги берём название
                left join abonents a on a.id=aas.abonent_id
                where
                aas.CHANGE_BALANCE_TIME between ':1.C_даты|date$' and ':2.По_дату|date$'
                --Используем типы 1 . Входящий 
                and aas.V_TYPE_ID=1             
                and a.id = ':ФИО|select[Abonents,is_folder=0]$'
                and aas.storno=0
                group by 1,2,3,4,aas.V_TYPE_ID) t1
    left join (select distinct aas.abonent_id as trafab,
                a.id as aname2,
                cast(cast(aas.CHANGE_BALANCE_TIME as date) as varchar(32)) as ttime,
                u.name as uname,
               iif(aas.V_TYPE_ID=2, cast(round(sum(aas.v),2) as varchar(32)),'') as OUT
                from arch_account_stack aas                --Трафик считаем по списаниям
                left join usluga u on aas.USLUGA_ID = u.id --Из услуги берём название
                left join abonents a on a.id=aas.abonent_id
                where
                aas.CHANGE_BALANCE_TIME between ':1.C_даты|date$' and ':2.По_дату|date$'
                --Используем типы  2. Исходящий 
                and aas.V_TYPE_ID=2 
                and a.id = ':ФИО|select[Abonents,is_folder=0]$'
                and aas.storno=0
    
                group by 1,2,3,4,aas.V_TYPE_ID)t2 on t2.ttime=t1.ttime
    
  147. Отчет, выводящий суммарный объем входящего и исходящего трафика с начала года по выбранный квартал
    select distinct
    'Объём информации, переданной от/к абонентам, всего, Гб' as "Наименование",
    sum(round(tc.SUM_BYTE_OUT_M/cast((1073741824) as numeric(18,5)), 2)) +
    sum(round(tc.SUM_BYTE_IN_M/cast((1073741824) as numeric(18,5)), 2)) as "Величина"
    from traf_counters tc
    join 
    	(select distinct
    		month_number month_n,
    		case when month_number in (1,2,3)
    			then 1
    			when month_number in (4,5,6)
    			then 2
    			when month_number in (7,8,9)
    			then 3
    			when month_number in (10,11,12)
    			then 4
    		end kvartal
    	from traf_counters) kv_m on tc.month_number = kv_m.month_n
    
    where
    tc.YEAR_NUMBER = (':Год|choices[2023^]2023^[2022^]2022^[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
    and kv_m.kvartal <= (':Квартал|choices[1^]1^[2^]2^[3^]3^[4^]4]$')
    
  148. Отчет, выводящий абонентов с услугами IPTV на определённом NAS. Отчёт выводит порядковый номер, логин, ФИО, название услуги IPTV, цену и списание по услуге за выбранный период
    SELECT coalesce(rownum,1)+0*x AS "№",
    login as "Логин",
    fio as "ФИО", 
    usluga as "Услуга", 
    price as "Цена", 
    credit as "Расход"
    FROM(
    	SELECT
    	CAST(RDB$GET_CONTEXT('USER_TRANSACTION','ROW')AS INT) rownum,
    	null login,
    	null fio,
    	null usluga,
    	null price,
    	null credit,
    	NULL x	
    	FROM rdb$database
    	UNION ALL
     
    	SELECT 
        (CAST(rdb$get_context('USER_TRANSACTION','row') AS INT)+1) AS rownum,	
    	us.login as login,
    			a.name as fio,
    			u.name as "Услуга",
    			round(u.summa / 10000000000.00, 2) as price,
    			round(c.summ,2) as credit,
    			rdb$set_context('USER_TRANSACTION','row', CAST(COALESCE(rdb$get_context('USER_TRANSACTION','row'),0) AS INTEGER)+1) AS x
    		from abonents a 
    		join counters c on a.id=c.abonent_id
    		join usluga u on c.usluga_id=u.id
    		left join users us on a.id = us.abonent_id
    		where 
    			u.system_type=7
    			and c.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
    			and c.year_number = (':Год|choices[2025^]2025^[2024^]2024^[2023^]2023^[2022^]2022^[2021^]2021^[2020^]2020^[2019^]2019^[2018^]2018^[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
    			and us.nas_id=25
    			and u.summa != 0
    	) t
    	
    	WHERE login IS NOT NULL
    
  149. Отчет выводит абонентов у которых после блокировки по балансу прошло более 2-ух дней, ислючает корзину, служебнуб группу и так же некоторые тарифы.
    select
         distinct
         a.id,
         a.name,
         A.CONTRACT_NUMBER as "Договор",
         T.name as "Тариф",
         round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс",
    	 DATEDIFF(day,ab.B_DATE,current_timestamp)  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.parent_id not in ( '244', '2', '4' )
    and a.deleted=0
    and ab.B_NEGBAL=1
    and uu.deleted=0
    and a.TARIF_ID not in (1175,1186,1188)
    and DATEDIFF(day,ab.B_DATE,current_timestamp) > '2'
    order by 2
    

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

Решение проблем с отчетами и инструкция по методике отладки описаны в статье "Отладка отчетов"

Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.