Конструктор отчетов. Отчёт

h2. Для создания отчета через конструктор отчетов необходимо:

1. Зайти в Carbon Manager "Отчеты \-> Конструктор отчетов"

!1.png|border=1!

2. Добавить новый отчет кнопкой "+" справа ("-" удаляет выбранный отчет)
3. Задать название отчета и текст запроса, который вы можете уточнить у тех.поддержки (должен начинаться со слова "select").

!Снимок-Отчет.png|border=1!

4. Также есть возможность настроить привилегии доступа для разных типов администраторов.

 Для вывода отчета достаточно выбрать нужный и нажать кнопку "Запросить".  

5. Начиная с версии {color:#000000}{*}3.9.6 484{*}{color} {color:#000000}есть возможность выгрузить полученный отчет в Excel.{color} {color:#ff0000}Работает только под системой Windows\!{color}

{color:#ff0000}{*}Выгрузка может не работать, если имя отчета длиннее 31 символа\!*{color}
 
!werwer.png|border=1!


{color:#e00000}{*}Важно:*{color}{color:#ff0000}* *{color}Если у вас не выводится отчет при нажатии на кнопку запросить(и она впадает при наведении курсора или выводится ошибка "lock conflict on no wait transaction. Your user name and password are not defined. Ask your database administrator to set up a Firebird plugin.") вероятнее всего у вас стоит неверный пароль для пользователя SYSDBA. Паролем для пользователя SYSDBA являются первые 8 символов от пароля локальной консоли (или меньше, если пароль меньше восьми символов)

h2. Также запросы можно выполнять из командной строки.


h3. Выполнение запросов из командной строки

*Пример 1.*    *Список всех доступных телефонов абонентов в формате (порядковый номер, логин абонента, номер телефона)*

{code}
select id, login, SMS from users where DELETED=0 and sms is not null
{code}

*Пример 2.*    *Список ФИО абонентов и их баланс на текущий момент*

{code}
select identify as "FIO", (DEBIT + OSTATOK - CREDIT)/10000000000.00 as "BALANCE" from users
  where id<100000 and deleted=0 and end_user=1
{code}

*Пример 3.&nbsp;&nbsp;&nbsp; Список всех доступных телефонов абонентов, у которых баланс меньше либо равен нулю.*

{code}
select SMS from users where (DEBIT + OSTATOK - CREDIT)<=0 and DELETED=0 and SMS is not null
{code}

*Пример 4.*&nbsp;&nbsp;&nbsp; *Список всех доступных МАС-адресов абонентов, использующих услугу IPTV с известным id.*

{code}
select mac from users
join users_usluga on users_usluga.user_id = users.id
where users_usluga.usluga_id = <id услуги>
and users.mac is not null and users.mac \!= ''
{code}

*Пример отчетов по VOIP.*
{code:lang=sql|title=Журнал определенных звонков за текущий месяц}
select SRC as "Номер абонента",
DST as "Направление звонка",
S_TIME as "Начало звонка",
E_TIME as "Окончание звонка",
DURATION as "Общая длительность",
BILL_SEC as "Платное время",
CHAN,
BILLED as "Обработан",
USER_ID as "Ид пользователя"
from VOIP_LOG
where USER_ID is not null and extract(month from E_TIME) = extract(month from current_timestamp) and extract(year from E_TIME) = extract(year from current_timestamp)
order by E_TIME desc
{code}
{code:lang=sql|title=Журнал неопределенных звонков за текущий месяц}select SRC as "Номер абонента",
DST as "Направление звонка",
S_TIME as "Начало звонка",
E_TIME as "Окончание звонка",
DURATION as "Общая длительность",
BILL_SEC as "Платное время",
CHAN,
BILLED as "Обработан",
USER_ID as "Ид пользователя"
from VOIP_LOG
where USER_ID is null and extract(month from E_TIME) = extract(month from current_timestamp)
and extract(year from E_TIME) = extract(year from current_timestamp)
order by E_TIME desc
{code}
{code:lang=sql|title=VOIP Абоненты со статусом}select U.IDENTIFY as "Наименование",
UU.SIP_ID as "Номер",
U.ENABLED as "1-Включен/0-Выключен"
from USERS_USLUGA UU
left join USERS U on UU.USER_ID = U.ID
where UU.SYSTEM_TYPE = 5
order by U.ID
{code}

*Пример 5. Список ФИО и Адрес всех абонентов. у которых в качестве nas_ip указан адрес 172.16.1.177*:
{code}
select u.identify as "FIO", av.ATTRIBUTE_VALUE as "Address" from users u
left outer join attribute_values av on av.user_id=u.id
left outer join (users_radiusauth ur on ur.user_id=u.id and av.attribute_id=3)
where uf_ip2string(ur.nas_ip_address)='172.16.1.177'
{code}

*Пример 6.* *Список{*}*&nbsp;всех абонентов в формате (id, должность, тариф, ФИО, дата внесения) за&nbsp;*{*}период с 2012-11-01 до 2012-11-30 )*

{code}
select u.id, av.attribute_value, tp.plan_name, u.identify, u.create_date from users
u left outer join attribute_values av on av.user_id=u.id and av.ATTRIBUTE_ID=19
left outer join tarif_plan tp on tp.plan_id=u.TARIFF_ID_CACHE
 where (u.create_date between '2012-11-01' and '2012-11-30')
{code}

*Пример 7.* *Список{*}*&nbsp;абонентов, созданных за* *заданный период&nbsp; в формате (*{*}id, должность, тариф, ФИО, дата создания)&nbsp;* :
{code}
select u.id, av.attribute_value from
users u left outer join attribute_values av on av.user_id=u.id and av.ATTRIBUTE_ID=19
where  (u.create_date between '2012-11-01' and '2012-11-30')
{code}

*Пример 8.&nbsp;*&nbsp;*Список, соответствующих пользователям, кодов 1С из биллинга.*
{code:lang=sql|title=1С коды}select U.ID as "Номер",
U.IDENTIFY as "Наименование",
U.CODE1C "Код синхронизации",
U.SYNC1C as "1-синхронизирован/0-несинхронизирован"
from USERS U
where U.END_USER = 1
{code}

*Пример 9.&nbsp;Список расхода абонентов за заданный период с 01.11.2012 по 25.11.2012 в формате{*}*&nbsp;(id, финансовый договор, ФИО, расход)* {color:#ff0000}(Для закрытого периода){color}
{code}
select U.ID,U.CONTRACT_NUMBER,U.IDENTIFY,sum(FO.OP_SUMMA)/10000000000.00 from
USERS U left join FINANCE_OPERATIONS FO on U.ID = FO.USER_ID
where (FO.OP_TYPE <>2 and FO.OP_TYPE <>3)
and (FO.OP_DATE between '2012-11-01' and '2012-11-30')
group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY
{code}

*Пример 10.&nbsp;Список расхода у не удаленных абонентов за заданный период с 2013-03-01 по 2013-03-31, у которых был трафик в формате {*}*&nbsp;(id, финансовый договор, ФИО, расход)* {color:#ff0000}(Для закрытого периода){color}
{code}
select distinct U.ID,
U.CONTRACT_NUMBER,
U.IDENTIFY,
sum(FO.OP_SUMMA) / 10000000000.00
from USERS U
left join FINANCE_OPERATIONS FO on U.ID = FO.USER_ID
where (FO.OP_TYPE <> 2 and FO.OP_TYPE <> 3) and (FO.OP_DATE between '2013-03-01' and '2013-03-31') and
exists(select first 1 1 from SESSIONS where ID = U.ID) and U.deleted=0
group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY
{code}

*Пример 11.&nbsp;Список расхода абонентов за текущий период в формате (id, финансовый договор, ФИО, расход)*
{code}
select id, contract_number, identify, credit/10000000000.00 from users
where end_user=1 and id<100000
{code}

*Пример 12.*&nbsp; *Список пользователей, подключенных за последний месяц&nbsp;(первый запрос) (период с 2011-11-20 до 2011-12-20 ) и их количества (второй запрос).:*
{code}
SELECT id, identify, login, create_date FROM users
WHERE create_date>'2011-11-20'
AND end_user=1 AND create_date<'2011-12-20'
{code}
{code}
SELECT COUNT(id) AS usercount FROM users
WHERE create_date>'2011-11-20'
AND end_user=1
AND create_date<'2011-12-20'
{code}
Список с выводом адреса
{code}
select U.ID,
U.IDENTIFY,
AV.ATTRIBUTE_VALUE as ADRES,
CREATE_DATE
from USERS U
left join ATTRIBUTE_VALUES AV on AV.USER_ID = U.ID and AV.ATTRIBUTE_ID = 3
where CREATE_DATE > '2013-05-20' and END_USER = 1 and CREATE_DATE < '2013-06-20'
{code}

*Пример 13.*&nbsp;&nbsp;*Перевод пользователей из подсетей 10.20.0.0/16...10.25.0.0/16 на Carbon AS с адресом 172.16.1.15*
{code}
update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.20.%' or uf_ip2string(host_ip) like '10.20.%'
update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.21.%' or uf_ip2string(host_ip) like '10.21.%'
update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.22.%' or uf_ip2string(host_ip) like '10.22.%'
update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.23.%' or uf_ip2string(host_ip) like '10.23.%'
update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.24.%' or uf_ip2string(host_ip) like '10.24.%'
update users set nas_ip_lock=1, nas_ip=uf_string2ip('172.16.1.5') where uf_ip2string(ip) like '10.25.%' or uf_ip2string(host_ip) like '10.25.%'
{code}

*Пример 14.*&nbsp; *Список абонентов с рекомендуемой суммой к оплате*
{code}
select user_id, sum(OP_SUMMA/10000000000) from finaions
where op_date>(current_timestamp-120) and op_type=2 group by user_id
{code}

*Пример 15.*&nbsp; *Список абонентов из заданной подсети 192.168.0.0/16*
{code}
select id, login from users where uf_ip2string(ip) like '192.168.%'
{code}

*Пример 16.*&nbsp; *Список&nbsp;одинаковых ИНН*
{code}
select count(attribute_value), attribute_value from attribute_values where attribute_id=4
group by attribute_value having count(attribute_value)>1
{code}

*Пример 17.* &nbsp;*Список должников (Тариф, ФИО, Телефон, Адрес, Баланс)*
{code}
select (select tp.plan_name from tarif_plan tp where tp.plan_id=users.TARIFF_ID_CACHE) as "TARIF",
        USERS.IDENTIFY as "FIO",
        (select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from attribute_values
         where ATTRIBUTE_VALUES.USER_ID = USERS.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 3) as "ADRES",
        (select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from ATTRIBUTE_VALUES  
         where ATTRIBUTE_VALUES.USER_ID = USERS.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 1) as "TELEFON",
        (DEBIT + OSTATOK - CREDIT)/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "BALANS"
from USERS where OVER_LIMIT_DATE is not null 
{code}

*Пример 18.*&nbsp;&nbsp;*Всех пользователей подсети 10.1.0.0/16 перевести на NAS 172.0.1.1, прикрепив к нему, установить авторизацию по радиус и поменять SNAT адрес на 192.168.5.0.*
{code}
sqlexec "UPDATE users
set 
  snatip=uf_string2ip('192.168.5.0'),
  server=0,
  auth_type_cache=6,
  auth_type=6,
  nas_ip=uf_string2ip('172.0.1.1'),
  nas_ip_lock=1
WHERE
  uf_ip2string(ip) like '10.1.%'"
{code}

*Пример 19.* *Список номинала всех активированных карт за выбранный период*
{code}
select
(card_series.card_value/10000000000) as NOMINAL  ,
cards.*
FROM CARDS
LEFT JOIN CARD_SERIES ON CARDS.SERIES_NO = card_series.SERIES_NO
WHERE
cards.date_used > '2012-12-01 00:00:00'
and cards.date_used < '2013-01-01 00:00:00'
and cards.date_used is not null
{code}

*Пример 20.* *Список итоговой суммы все{*}{color:#333333}{*}х активированных карт за{*}{color} *выбранный период*
{code}
select
SUM(card_series.card_value)/10000000000 as ALL_PERIOD_SUM
FROM CARDS
LEFT JOIN CARD_SERIES ON CARDS.SERIES_NO = card_series.SERIES_NO
WHERE
cards.date_used > '2012-12-01 00:00:00'
and cards.date_used < '2013-01-01 00:00:00'
and cards.date_used is not null
{code}

*Пример 21.*{*}Список (Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по алфавиту*


{code}select 
identify as "FIO",
uf_ip2string(ip) as "IP",
uf_ip2string(nas_ip) as "NAS",
(DEBIT + OSTATOK - CREDIT)/10000000000.00 as "BALANS" 
from users where deleted=0 and end_user=1 order by identify{code}

*Пример 22.*{*}Список (*{*}Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по ip*

{code}select 
identify as "FIO",
uf_ip2string(ip) as "IP",
uf_ip2string(nas_ip) as "NAS",
(DEBIT + OSTATOK - CREDIT)/10000000000.00 as "BALANS" 
from users where deleted=0 and end_user=1 order by IP{code}

*Пример 23.*&nbsp;*Список (*{*}Ф.И.О. , Адрес, телефон для смс) с сортировкой фамилий по алфавиту*

{code}select 
u.identify, 
(select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from attribute_values where ATTRIBUTE_VALUES.USER_ID = U.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 3) as "Address",
u.sms 
from users u where u.deleted=0 and u.end_user=1 order by u.identify{code}

*Пример 24.*&nbsp;*Список (*{*}Номер договора, Ф.И.О. , Баланс, Адрес, Тариф )*


{code}select
u.contract_number,
u.identify,
(DEBIT + OSTATOK - CREDIT)/10000000000.00 as "BALANS",
(select ATTRIBUTE_VALUES.ATTRIBUTE_VALUE from attribute_values where ATTRIBUTE_VALUES.USER_ID = U.ID and ATTRIBUTE_VALUES.ATTRIBUTE_ID = 3) as "Address",
tp.plan_name from users u left outer join tarif_plan tp on tp.plan_id=u.TARIFF_ID_CACHE
{code}

*Пример 25.*&nbsp;*Список ФИО абонентов, у которых запланирован переход в следующем месяце на новый тариф с указанием тарифа и сортировкой по Ф.И.О*

{code}
select u.identify,tp.plan_name from users u
left join tarif_plan tp on u.plan_next_id=tp.plan_id
where u.plan_next_id is not null and u.plan_next_date>current_timestamp
order by u.identify
{code}

*Пример 26.*&nbsp;*Список Ф.И.О. абонентов, у которых взят Отложенный платеж и дата его окончания с сортировкой по Ф.И.О.*

{code}
select u.identify,u.promise_date_end from users u
where u.promise_pay is not null and u.promise_date_end>current_timestamp
order by u.identify
{code}


*Пример 27.*&nbsp;*Список оплат через платежные системы (id, номер договора, Ф.И.О. абонента и внесенная сумма за период с 2013.01.01 с группировкой по группам)*
{code}
select U.ID,
U.CONTRACT_NUMBER,
U.IDENTIFY,
sum(PL.SUMMA_IN)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID
where PL.MSG_OUT = 'ACCEPTED' and PL.OPERATOR_DATE_IN >= '2013.01.01'
group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY
{code}

*Пример 28.*&nbsp;*Список оплат через платежные системы по группам без учета web-интерфейса кассира (id, название группы и внесенная сумма за период с 2013.04.15 до 2013.04.17)*
{code}
select U.PARID,
GU.IDENTIFY,
sum(PL.SUMMA_IN)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID or (PL.USER_ID_IN is null and PL.CONTRACT_NUMBER_IN = U.CONTRACT_NUMBER)
left join USERS GU on U.PARID = GU.ID
where PL.MSG_OUT = 'ACCEPTED' and (PL.OPERATOR_DATE_IN between '2013-04-15' and '2013-04-17') and PL.PAY_OPERATOR <> 'WEB-cash'
group by U.PARID, GU.IDENTIFY
{code}

*Пример 29.*&nbsp;*Список оплат через платежные системы по группам с учетом web-интерфейса кассира (id, название группы и внесенная сумма за период с 2013.04.15 до 2013.04.17)*
{code}
select U.PARID,
GU.IDENTIFY,
sum(PL.SUMMA_IN)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID or (PL.USER_ID_IN is null and PL.CONTRACT_NUMBER_IN = U.CONTRACT_NUMBER)
left join USERS GU on U.PARID = GU.ID
where PL.MSG_OUT = 'ACCEPTED' and (PL.OPERATOR_DATE_IN between '2013-04-15' and '2013-04-17')
group by U.PARID, GU.IDENTIFY
{code}

*Пример 30.*&nbsp;*Список оплат через платежные системы (id, договор, ФИО, название платежной системы, внесенная сумма и дата платежа за период с 2014-01-01 до 2014-02-01)*
{code}
select U.ID,
U.CONTRACT_NUMBER,
U.IDENTIFY,
PL.PAY_OPERATOR,
PL.SUMMA_IN,
PL.OPERATOR_DATE_IN
from PAY_LOG PL
left join USERS U on PL.USER_ID_OUT = U.ID
where PL.MSG_OUT = 'ACCEPTED' and (PL.OPERATOR_DATE_IN between '2014-01-01' and '2014-02-01')
{code}

*Пример 31.*&nbsp;*Список должников (ФИО, дата последнего платежа, Баланс, Тариф{*}*, Адрес,*{*}Телефон)*

{code}
select
U.IDENTIFY as "FIO",
(select max(FO.OP_DATE)
from FINANCE_OPERATIONS FO
where FO.USER_ID = U.ID) "last date",
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "BALANS",
TP.PLAN_NAME as "TARIF",
AV.ATTRIBUTE_VALUE as "ADRES",
AV2.ATTRIBUTE_VALUE as "TELEFON"
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
left join ATTRIBUTE_VALUES AV2 on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 1
where U.OVER_LIMIT_DATE is not null
{code}

*Пример 32.*&nbsp;*Список оплат через менеджер и веб-кассир администратором с id=2792{*}*&nbsp;(ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за текущий день*
{code}
select U.IDENTIFY AS "FIO",
U.login AS "ABONENT_LOGIN",
FO.op_date as "DATE",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "SUMMA",
u2.identify as "OWNER"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where (FO.op_type=2) and (cast(FO.op_date as date)= current_date) and (FO.owner_id<>u.id) and u2.identify is not null and FO.owner_id=2792
{code}

*Пример 33.*&nbsp;*Сумма проведенных оплат через менеджер и веб-кассир администратором с id=2792 за&nbsp;*{*}текущий день*
{code}
select sum(FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1)) as "SUMMA"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where (FO.op_type=2) and (cast(FO.op_date as date)= current_date) and (FO.owner_id<>u.id) and u2.identify is not null and FO.owner_id=2792
{code}

*Пример 34.*&nbsp;*Список оплат через менеджер и веб-кассир администратором с id=2792 &nbsp;(ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 2014-03-18 по 2014-03-19 &nbsp;*
{code}
select U.IDENTIFY AS "FIO",
U.login AS "ABONENT LOGIN",
FO.op_date as "DATE",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "SUMMA",
u2.identify as "OWNER"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where (FO.op_type=2) and (FO.owner_id<>u.id) and (FO.op_date between '2014-03-18' and '2014-03-19') and u2.identify is not null and FO.owner_id=2792
{code}

*Пример 35.*&nbsp;*Список оплат через менеджер (ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 2014-03-18 по 2014-03-19*
_примечание:_ _в отчете считается что комментарий при проведении платежа не изменяется администратором и имеет вид ДД.ММ.ГГГГ-ДД.ММ.ГГГГ_
{code}
select U.IDENTIFY AS "FIO",
U.login AS "ABONENT LOGIN",
FO.op_date as "DATE",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "SUMMA",
u2.identify as "OWNER"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where (FO.op_type=2) and (FO.owner_id<>u.id) and (FO.op_date between '2014-03-18' and '2014-03-19') and u2.identify is not null and FO.descr like '%.%.%-%.%.%'
{code}

*Пример 36.*&nbsp;*Сумма проведенных оплат через менеджер и веб-кассир администратором с id=2792 за период с 2014-03-18 по 2014-03-19 &nbsp;*
{code}
select sum(FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1)) as "SUMMA"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where (FO.op_type=2) and (FO.owner_id<>u.id) and (FO.op_date between '2014-03-01' and '2014-03-20') and u2.identify is not null and FO.owner_id=2792
{code}

*Пример 37. Список логинов и паролей абонентов*

{code}
select id, identify, login, gen_pwd AS "password" from users where DELETED=0 and end_user=1
{code}



*Пример 38. Список пользователей, у которых активированы дополнительные услуги за все время работы (ФИО, название услуги, дата активации)*

{code}
select USERS.IDENTIFY AS "FIO",
USLUGA.NAME AS "USLUGA",
USERS_USLUGA.CREATE_DATE AS "CREATE_DATE"
from USERS, USLUGA, USERS_USLUGA
where USLUGA.ID = USERS_USLUGA.USLUGA_ID and USERS.ID = USERS_USLUGA.USER_ID
{code}

*Пример 39. Список пользователей, у которых активированы дополнительные услуги за период с 2013.03.01 по 2013.04.01 (id, ФИО, название услуги, дата активации)*

{code}
select USERS.ID,
USERS.IDENTIFY,
USLUGA.NAME,
US.CREATE_DATE
from USERS_USLUGA US
left join USERS on US.USER_ID=USERS.ID
left join USLUGA on US.USLUGA_ID=USLUGA.ID
where USERS.ID is not null and
US.CREATE_DATE between '2013.03.01' and '2013.04.01' order by USERS.ID
{code}

*Пример 40. Список пользователей, у которых активированы дополнительные услуги за период с 2013.03.01 по 2013.04.01 (id, ФИО, название услуги, стоимость услуги, дата активации)*
{code}
select USERS.ID,
USERS.IDENTIFY,
USLUGA.NAME,
USLUGA.SUMMA / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as SUMMA,
US.CREATE_DATE
from USERS_USLUGA US
left join USERS on US.USER_ID=USERS.ID
left join USLUGA on US.USLUGA_ID=USLUGA.ID
where USERS.ID is not null and
US.CREATE_DATE between '2013.03.01' and '2013.04.01' order by USERS.ID
{code}

*Пример 41. Список пользователей, у которых активированы дополнительные услуги на данный момент(id, ФИО, название услуги)*
{code}
select USERS.ID,
USERS.IDENTIFY,
USLUGA.NAME
from USERS_USLUGA US
left join USERS on US.USER_ID=USERS.ID
left join USLUGA on US.USLUGA_ID=USLUGA.ID
where USERS.ID is not null and US.activated=1
{code}

*Пример 42. Отчет по сумме оплат за каждый день, внесенных через менеджер за период с 2013.03.01 по 2013.03.25*


В консоли сервера выполните:
1. Открыть базу для редактирования
{code}
sqlexec "alter exception ERR 'Error '"
{code}
2. Добавить в таблицу FINANCE_OPERATIONS поле OP_DATE_DAY
{code}
sqlexec "ALTER TABLE FINANCE_OPERATIONS ADD OP_DATE_DAY DATE"
{code}
3. Закрыть базу для редактирования
{code}
sqlexec "alter exception ERR 'Error'"
{code}

Отчет строится из двух запросов. Запросы необходимо выполнять последовательно.

запрос 1:
{code}
update FINANCE_OPERATIONS set op_date_day = op_date
{code}
запрос 2:
{code}
select
FO.op_date_day ,
SUM(FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) )as "SUMMA"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where (FO.op_type=2) and (FO.op_date_day between '2013.03.01' and '2013.03.25') group by FO.op_date_day order by FO.op_date_day
{code}

*Пример 43. Изменить у абонентов тип авторизации с Radius на ip (адрес NAS - 192.168.0.222)*

{code}
update users set AUTH_TYPE=1, AUTH_TYPE_CACHE=1, NAS_IP_LOCK=1, NAS_IP=uf_string2ip('192.168.0.222') where AUTH_TYPE_CACHE=6
{code}

*Пример 44. Получить сумму по 2 услугам за период по пользователю услуга 10 и 11*
{code}
SELECT SUM(session_credit) FROM sessions WHERE (time_in between '2013-03-01' and '2013-04-01') AND id = 2564 AND (ruleset_no = -10 OR ruleset_no = -11)
{code}

*Пример 45. Список абонентов с последней оплатой (ФИО,Логин,Договор,Дата последнего платежа,Сумма последнего платежа,Текущий баланс)*

Запрос:

{code}
select U.identify,U.login,U.contract_number,RPL.OP_DATE,RPL.OP_SUMMA,RPL.BALANCE_BUH from users U
join REP_FINOPER_LAST(2) RPL on RPL.user_id=u.id
{code}
Предварительно Через [ibexpert|http://docs.carbonsoft.ru/pages/viewpage.action?pageId=3440679] или sqlexec сделать процедуру
{code}
SET TERM ^ ;
create procedure REP_FINOPER_LAST1 (
OP_TYPE_IN integer)
returns (
OP_ID integer,
USER_ID integer,
OWNER_ID integer,
OP_TYPE integer,
OP_DATE timestamp,
OP_SUMMA numeric(18,0),
OP_CARD_ID integer,
SYSTEM_DATE timestamp,
NUMBER varchar(10),
BALANCE_BUH numeric(18,0),
DESCR varchar(255),
FINANCE integer,
ISBUHDOC integer,
END_USER integer,
OPERATOR_PAY_ID integer,
OPERATOR_NAME varchar(32),
OPERATOR_RESULT integer,
OPERATOR_PAY_ID_STR varchar(32),
SYNC1C integer,
CODE1C varchar(32),
CURS double precision,
SUM_IN numeric(18,0))
as
declare variable TMP_OP_ID integer;
begin
for select max(OP_ID)
from FINANCE_OPERATIONS
where OP_TYPE = :OP_TYPE_IN
group by USER_ID
into TMP_OP_ID
do
begin
select first 1 OP_ID,
USER_ID,
OWNER_ID,
OP_TYPE,
OP_DATE,
OP_SUMMA,
OP_CARD_ID,
SYSTEM_DATE,
NUMBER,
BALANCE_BUH,
DESCR,
FINANCE,
ISBUHDOC,
END_USER,
OPERATOR_PAY_ID,
OPERATOR_NAME,
OPERATOR_RESULT,
OPERATOR_PAY_ID_STR,
SYNC1C,
CODE1C,
CURS,
SUM_IN
from FINANCE_OPERATIONS
where OP_ID = :TMP_OP_ID
order by OP_ID desc
into :OP_ID,
:USER_ID,
:OWNER_ID,
:OP_TYPE,
:OP_DATE,
:OP_SUMMA,
:OP_CARD_ID,
:SYSTEM_DATE,
:NUMBER,
:BALANCE_BUH,
:DESCR,
:FINANCE,
:ISBUHDOC,
:END_USER,
:OPERATOR_PAY_ID,
:OPERATOR_NAME,
:OPERATOR_RESULT,
:OPERATOR_PAY_ID_STR,
:SYNC1C,
:CODE1C,
:CURS,
:SUM_IN;
suspend;
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON FINANCE_OPERATIONS TO PROCEDURE REP_FINOPER_LAST;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE REP_FINOPER_LAST TO SYSDBA;
{code}

*Пример 46. Список абонентов в формате (Номер, Логин, Тарифный план, ФИО, Остаток на начало периода, Платеж через кассира, Расход, Расход по трафику, Остаток на конец периода) за период с 2013-05-01 по 2013-05-15* {color:#ff0000}{*}Доступен с версии&nbsp;397_359{*}{color}

{code}
select CR.USER_ID "№",
CR.LOGIN "Логин",
CR.PLAN_NAME "Тарифный план",
CR.USER_NAME "ФИО",
CR.START_BALANCE "Остаток на начало периода",
CR.PAY_SUM "Платеж через кассира",
CR.SUM_CREDIT "Расход",
CR.TRAF_SUM "Расход по трафику",
CR.END_BALANCE "Остаток на конец периода"
from CLN_REPORTS_USERS_INFO('2013-05-01', '2013-05-15') CR
order by CR.LOGIN
{code}

*Пример 47. Список абонентов в формате (ID, ФИО, тарифный_план) у которых за период 2013-04-01 - 2013-05-01 был трафик больше 100Кб*

{code}
select distinct USERS.id, USERS.identify, TARIF_PLAN.plan_name
from SESSIONS
left join USERS on USERS.id = SESSIONS.id
left join TARIF_PLAN on sessions.plan_id = TARIF_PLAN.plan_id
where (SESSIONS.date_in between '2013-04-01' and '2013-05-01') and USERS.id<100000 and SESSIONS.v_in>102400 order by USERS.id
{code}

*Пример 48. Список должников (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по № договора*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
U.CREATE_DATE,
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_Date,
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS,
U.LOGGED as STATUS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
where U.OVER_LIMIT_DATE is not null order by U.CONTRACT_NUMBER
{code}

*Пример 49. Список абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
U.CREATE_DATE,
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_Date,
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS,
U.LOGGED as STATUS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
order by U.IDENTIFY
{code}

*Пример 50. Список новых абонентов за период 2013-03-01 - 2013-03-30 (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по дате подключения.*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
U.CREATE_DATE,
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_PAY_Date,
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS,
U.LOGGED as STATUS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
where U.CREATE_DATE between '2013-03-01' and '2013-03-30' order by U.CREATE_DATE
{code}

*Пример 51. Список удаленных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
U.CREATE_DATE,
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID) as Last_PAY_Date,
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS,
U.LOGGED as STATUS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
where U.DELETED=1 order by U.IDENTIFY
{code}

*Пример 52. Список отключенных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата отключения, Баланс, Статус) сортировка по Ф.И.О.*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
U.CREATE_DATE,
((select max(op_time) from audit_operations where descr like '%Откл%' and op_type=120 and object_id=U.id )) as "Дата отключения",
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS,
U.LOGGED as STATUS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
where U.ENABLED=0 or U.DISABLED_DATE is not null
order by U.IDENTIFY
{code}

*Пример 53. Список заблокированных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Баланс) сортировка по Ф.И.О.*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
where U.ENABLED=0 or OVER_LIMIT_DATE is not null
order by U.IDENTIFY
{code}

*Пример 54. Список отключенных абонентов, не плативших с 2013.04.01 (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата отключения, Баланс, Статус) сортировка по Ф.И.О.*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
U.CREATE_DATE,
((select max(op_time) from audit_operations where descr like '%Откл%' and op_type=120 and object_id=U.id )) as "Дата отключения",
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS,
U.LOGGED as STATUS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
where (U.ENABLED=0 or U.DISABLED_DATE is not null) and U.DISABLED_DATE<'2013.04.01'
order by U.IDENTIFY
{code}
*Пример 55. Список суммы должников (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.*
{code}
SELECT
cast(U.CONTRACT_NUMBER as varchar(32)) as "№ договора",
cast(U.IDENTIFY as varchar(128)) as "Ф.И.О",
cast(AV.ATTRIBUTE_VALUE as varchar(1024)) as "Адрес",
cast(U.SMS as varchar(32)) as "Телефон",
cast(UF_IP2STRING(U.IP) as varchar(16)) as "IP",
cast((U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as varchar(32)) as "Баланс",
cast(cast(FIN.OP_DATE as date) as varchar(32)) as "Дата последней операции",
cast(owner.identify as varchar(128)) as "Кем или чем проведена операция",
cast(U.LOGGED as varchar(32)) as "Статус"

from USERS U
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
left join FINANCE_OPERATIONS FIN ON FIN.op_id = (select max(FO.op_id) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID)
left join USERS owner ON owner.id = FIN.owner_id
where (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) < 0 and u.end_user=1 and u.id<100000 and u.deleted=0
UNION ALL
SELECT
cast('' as varchar(32)) as "№ договора",
cast('' as varchar(128)) as "Ф.И.О",
cast('' as varchar(1024)) as "Адрес",
cast('' as varchar(32)) as "Телефон",
cast('' as varchar(16)) as "IP",
cast('' as varchar(32)) as "Баланс",
cast('' as varchar(32)) as "Дата последней операции",
cast('Всего:' as varchar(128)) as "Кем или чем проведена операция",
cast(sum((U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) as varchar(32)) as "Статус"

from USERS U
where (U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) < 0 and u.end_user=1 and u.id<100000 and u.deleted=0
order by 2
{code}
*Пример 56. Кол-во абонентов на каждом тарифе (Тариф, количество абонентов) сортировка по названию тарифа*
{code}
select
tp.plan_name as "Тарифный план",
(select count(1) from users u where u.tariff_id_cache=tp.plan_id and u.deleted=0 and u.end_user=1 and u.id<100000) as "Количество абонентов"
from
tarif_plan tp order by 1
{code}
*Пример 57. Кол-во абонентов на каждой услуге (Тариф, количество абонентов) сортировка по названию услуги*
{code}
select
us.name as "Услуга",
(select count(1) from users u where u.id in (select user_id from users_usluga uu where uu.usluga_id=us.id) and u.deleted=0 and u.end_user=1 and u.id<100000) as "Количество абонентов"
from usluga us order by 1
{code}
*Пример 58. Сумма внесенных средств за указанный период(№ договора, Ф.И.О, Адрес, телефон, IP, баланс, дата последней операции, кем или чем проведена операция) сортировка по дате последней операции в конце общая сумма всех операций*
{code}
select
cast(U.CONTRACT_NUMBER as varchar(32)) as "№ договора", cast(U.IDENTIFY as varchar(128)) as "Ф.И.О",
cast(AV.ATTRIBUTE_VALUE as varchar(1024)) as "Адрес", cast(U.SMS as varchar(32)) as "Телефон",
cast(UF_IP2STRING(U.IP) as varchar(16)) as "IP", cast((U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as varchar(32)) as "Баланс",
cast(cast(FIN.OP_DATE as date) as varchar(32)) as "Дата последней операции",
cast(OWNER.IDENTIFY as varchar(128)) as "Кем или чем проведена операция",
(select sum(FF.OP_SUMMA)
from FINANCE_OPERATIONS FF
where FF.OP_DATE between '2015-01-01' and '2015-02-20' and
FF.OP_TYPE = 2 and FF.USER_ID = U.ID and not FF.DESCR like '%Обещанный платеж%') / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "op_sum", FIN.DESCR as "Комментарий"
from USERS U
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
left join FINANCE_OPERATIONS FIN on FIN.OP_ID = (select max(FO.OP_ID)
from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID and FO.OP_TYPE = 2 and FO.OP_DATE between '2015-01-01' and '2015-02-20' and not FO.DESCR like '%Обещанный платеж%')
left join USERS OWNER on OWNER.ID = FIN.OWNER_ID where U.DELETED = 0 and U.END_USER = 1 and U.ID < 100000 and FIN.OP_ID is not null
union all
select
cast('' as varchar(32)) as "№ договора", cast('' as varchar(128)) as "Ф.И.О",
cast('' as varchar(1024)) as "Адрес", cast('' as varchar(32)) as "Телефон", cast('' as varchar(16)) as "IP",
cast('' as varchar(32)) as "Баланс", cast('' as varchar(32)) as "Дата последней операции",
cast('Всего:' as varchar(128)) as "Кем или чем проведена операция", sum(OP_SUMMA / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) as "Сумма операций",
cast('' as varchar(255)) as "Комментарий"
from FINANCE_OPERATIONS
where OP_DATE between '2015-01-01' and '2015-02-20' and not DESCR like '%Обещанный платеж%'
order by 7
{code}

*Пример 59. Поиск абонента по MAC (52:54:00:BC:AA:45) (при условии что у абонента стоит статическая привязки) в формате id, ФИО, ip*
{code}
SELECT id, identify, uf_ip2string(ip) from USERS where MAC='52:54:00:BC:AA:45'
{code}

*Пример 60. Список должников(ФИО, IP)*
Сортировка по ФИО
{code}
select identify, uf_ip2string(ip) from users where over_limit_date is not null and deleted=0 and id<100000 and end_user=1 order by 1
{code}
Сортировка по IP
{code}
select identify, uf_ip2string(ip) from users where over_limit_date is not null and deleted=0 and id<100000 and end_user=1 order by 2
{code}

*Пример 61. Список сессий по абоненту , пример по абоненту id 245 (id,IP-адрес,Начало сессии, конец сессии) с сортировкой по дате*
Сортировка по ФИО
{code}
select ID,
UF_IP2STRING(USER_IP) as IP,
TIME_IN as "Начало сесии",
TIME_OUT as "Конец сесии"
from SESSIONS
where ID = 245 and UF_IP2STRING(USER_IP) <> '0.0.0.0'
order by DATE_IN
{code}

*Пример 62. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, логин, фио, номер телефона*
{code}
select UR.user_id as ID,
U.login as LOGIN,
U.identify as FIO,
AV.attribute_value as TELEFON
from users_radiusauth UR
left join USERS U on U.id=UR.user_id
left join ATTRIBUTE_VALUES AV on AV.user_id=UR.user_id and AV.ATTRIBUTE_ID=1
where UF_IP2STRING(UR.NAS_IP_ADDRESS)='172.16.0.6'
order by U.identify
{code}

*Пример 63. Список абонентов, оплативших через платежную систему telepay в формате id, фио, сумма_платежа*
{code}
select U.login,
U.identify as FIO,
PL.SUMMA_IN as SUMMA,
PL.OPERATOR_DATE_IN as DATA
from PAY_LOG PL
left join USERS U on U.id=PL.user_id_out
where PL.MSG_OUT='ACCEPTED' and PL.ACT_IN='PAY' and PL.PAY_OPERATOR='TELEPAY'
order by U.identify
{code}

*Пример 64. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, IP коммутатор, Номер порта, Номер vlan) с группировкой по номеру коммутатора*
{code}
select
u.identify as FIO,
sw.name as NAME,
uf_ip2string(sw.ip) as SWITCH_IP,
sp.num as PORT,
sp.vlan as VLAN
from switch_ports sp
join users u on u.id=sp.user_id
join switch sw on sw.id=sp.switch_id
order by sp.switch_id
{code}
*Пример 65. Список абонентов, не плативших в течение 3-х месяцев (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата блокировки по балансу, Баланс, Статус) сортировка по Ф.И.О.*
{code}
select
U.CONTRACT_NUMBER,
U.IDENTIFY as FIO,
AV.ATTRIBUTE_VALUE as ADRES,
U.SMS as TELEFON,
UF_IP2STRING(U.IP) as IP,
TP.PLAN_NAME as TARIF,
U.CREATE_DATE,
U.over_limit_date as "Дата блокировки",
(U.DEBIT + U.OSTATOK - U.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as BALANS,
U.LOGGED as STATUS
from USERS U
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
left join ATTRIBUTE_VALUES AV on U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3
where U.over_limit_date < (current_timestamp -90) and U.end_user=1 and U.deleted=0
order by U.IDENTIFY
{code}

*Пример 66. Список абонентов в формате (ФИО, NAT, Дата последнего платежа, Кто провел платеж)*
{code}
select
u.identify,
uf_ip2string(u.SNATIP),
cast(FIN.OP_DATE as date) as"Дата платежа",
cast(owner.identify as varchar(128)) as "Кто провел"
from USERS U
left join FINANCE_OPERATIONS FIN ON FIN.op_id = (select max(FO.op_id) from FINANCE_OPERATIONS FO where FO.USER_ID = U.ID AND FO.op_type=2)
left join USERS owner ON owner.id = FIN.owner_id
where u.deleted=0 and u.id<100000 and u.end_user=1
{code}
*Пример 67. Вычислить направление звонка по номеру (351111111 нужно заменить на нужный номер)*

{code}
select first 1 vd.name, vc.NAME, vd.mask
from VOIP_DIRECTION VD
left join VOIP_CATEGORY VC on VD.CATEGORY_ID = VC.ID
where '351111111' like VD.MASK || '%'
order by VD.MASK desc
{code}

*Пример 68. Список абонентов работающих через определенный NAS (ID, ФИО, IP-адрес абонента, IP-адрес NAS сервера)*

{code}
select id,
identify FIO,
uf_ip2string(ip) IP,
uf_ip2string(snatip) sNAT
from users where uf_ip2string(snatip)='ip_адрес_наса' order by id
{code}

*Пример 69. Отчет по voip звонкам за месяц*

{code}
select
users.identify, SRC, sum(BILL_SUM)/10000000000.00
from voip_log left join users on users.id=voip_log.user_id
where
bill_sum is not null
and EXTRACT (year FROM S_TIME)=год
and EXTRACT (month FROM S_TIME)=месяц
group by users.identify, SRC
{code}

*Пример 70. Отчет по приходу абонентов за период с 2013-09-01 по 2013-10-30 в формате (Логин, IP, Баланс, ФИО, Адрес, Телефон, Номер студенческого, Сумма)*

{code}
select U.LOGIN, uf_ip2string(ip), (ostatok+debit-credit)/1000000000 as "Баланс", U.IDENTIFY as "Ф.И.О", AV.ATTRIBUTE_VALUE as "Адрес",
U.SMS as "Телефон",
AV.attribute_value "Номер студенческого",
( select sum(FF.OP_SUMMA)
from FINANCE_OPERATIONS FF
where FF.USER_ID = U.ID and FF.OP_DATE between '2013-09-01' and '2013-10-30' and FF.OP_TYPE = 2
and not FF.DESCR like '%Обещанный платеж%'
) / 10000000000 as "Сумма"

from USERS U
left join ATTRIBUTE_VALUES AV on ( U.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 10008 )
where users.finance_user=1
{code}

*Пример 71. Количество занятых адресов в пуле с номером 7*
{code}
select count(id) from users where (select pull_id from GLN_USR_IP_PULL(users.id))=7
{code}

*Пример 72. Вывод занятых адресов в пуле с номером 7 в формате (id абонента, IP)*
{code}
select id, uf_ip2string(ip) from users where (select pull_id from GLN_USR_IP_PULL(users.id))=7
{code}

*Пример 73. Список должников за месяц с 2013-11-01 по 2013-11-30 в формате ФИО, Логин, Телефон, IP коммутатора, Порт Коммутатора*
{code}
select
u.identify as "ФИО",
u.login as "Login",
u.SMS as "Телефон",
uf_ip2string(sw.IP) as "IP коммутатора",
u.ROUTER_PORT as "Порт Коммутатора"
from users u
left join SWITCH sw
on u.SWITCH_ID=sw.ID
where (u.over_limit_date between '2013-11-01' and '2013-11-30')
{code}

*Пример 74.Список id абонентов, у которых отрицательный баланс был до и по настоящий момент 2013-11-01*
{code}
select id from users where over_limit_date<'2013-11-01'
{code}

*Пример 75.Список абонентов в формате (id, ФИО, Логин, Баланс, Тариф, IP), которые входят в группу 888 (Либо любой номер группы из вашего дерева)*
{code}
select
u.id ID,
u.identify FIO,
u.login LOGIN,
(u.DEBIT + u.OSTATOK - u.CREDIT)/10000000000.00 BALANCE, 
tp.plan_name TARIF, 
uf_ip2string(ip) IP 
from users u 
left outer join tarif_plan tp on tp.plan_id=u.TARIFF_ID_CACHE 
where u.parid=888
{code}

*Пример 76.Список абонентов в формате (id,время начала сессии, время окончания сессии) которые использовали ip 3.3.3.3 в период с 03-11-2013 по 05-11-2013. Для МВД*
{code}
select
id,
time_in,
time_out
from sessions
where (TIME_IN between '2013-11-03' and '2013-11-05') and USER_IP=uf_string2ip('3.3.3.3')
{code}

*Пример 77.Список абонентов в формате (id,время начала сессии, время окончания сессии) которые использовали natip 3.3.3.3 в период с 03-11-2013 по 05-11-2013. Для МВД*
{code}
select distinct
id,
uf_ip2string(USER_IP),
time_in,
time_out
from sessions
where (TIME_IN between '2013-11-03' and '2013-11-05') and NAT_IP=uf_string2ip('3.3.3.3')
{code}

*Пример 78.Список абонентов в формате (id,время начала сессии, время окончания сессии, телефон, ФИО, Логин, Адрес) которые использовали natip 3.3.3.3 в период с 03-11-2013 по 05-11-2013. Для МВД*
{code}
select distinct
s.id,
uf_ip2string(s.user_ip),
s.time_in,
s.time_out,
u.sms,
u.identify,
u.login,
av.ATTRIBUTE_VALUE
from sessions s
left join users u on u.id=s.id
left join attribute_values av on s.id=av.USER_ID
where (s.TIME_IN between '2014-12-28' and '2014-12-30') and uf_ip2string(s.NAT_IP)='3.3.3.3' and (av.ATTRIBUTE_ID=3 or av.ATTRIBUTE_ID is null) order by s.id
{code}

*Пример 79. Список sNAT адресов и пользователей на них*
{code}
select
count(*) as "Users",
uf_ip2string(snatip_cache) as "sNAT"
from users group by uf_ip2string(snatip_cache)
{code}

*Пример 80. Список абонентов, которым меняли дату отключения в период с 2014-06-01 по 2014-07-01 (ID, ФИО, Дата смены, Кто менял, Описание)*
{code}
select U.ID, U.IDENTIFY as FIO , a.op_time,
ow.identify, a.descr
from USERS U
join AUDIT_OPERATIONS A on A.OBJECT_ID = U.ID
join users ow on ow.id = a.owner_id
where a.DESCR like '%ДатаОткл%' and
a.OP_TYPE = 120
and (a.op_time between '2014-06-01' and '2014-07-01')
{code}

*Пример 81. Подсчет количества абонентов, активных(пользующихся интернетом) в сентябре 2014 года (Частое выполнение нагружает сервер)*
{code}
select
count(distinct id)
from sessions
where MONTH_IN='2014-09-01' and id<100000"
{code}

*Пример 82. Объем трафика в разрезе периода (1.10.2014 - 30.10.2014) по тарифам с группировкой*
{code}
select
tp.plan_name as "TARIFf",
sum(s.v_in)/1024/1024 as "V_IN",
sum(s.v_out)/1024/1024 as "V_OUT"
from sessions s join tarif_plan tp
on s.plan_id = tp.plan_id
where s.time_in < '1.10.2014' and s.time_in > '30.10.2014'
group by tp.plan_name
{code}

*Пример 83. Список оплат через менеджер и веб-кассир с комментарием "Бонус" (ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 2014-12-01 по 2014-12-31 &nbsp;*
{code}
select U.IDENTIFY AS "FIO",
U.login AS "ABONENT LOGIN",
FO.op_date as "DATE",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "SUMMA",
u2.identify as "OWNER"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where (FO.op_type=2) and (FO.owner_id<>u.id) and (FO.op_date between '2014-12-01' and '2014-12-31') and u2.identify is not null and (FO.DESCR like '%Бонус%')
{code}

*Пример 84. Список Ф.И.О. абонента, его адреса и номера телефона с сортировкой по улице и номеру дома из поля "Дом"*
{code}
select u.identify,
(select FULL_ADR from CLN_HOME_LIST(0, u.home_id)),
sms
from users u where u.home_id is not null order by 2
{code}
*Пример 85. Список Ф.И.О. абонента, его адреса, номера квартиры и номера телефона с сортировкой по улице и номеру дома из поля "Дом"*
{code}
select u.identify,
(select FULL_ADR from CLN_HOME_LIST(0, u.home_id)),
A_HOME_NUMBER,
sms
from users u where u.home_id is not null order by 2
{code}

*Пример 84. Список абонентов (Номер договора, ФИО абонента, адрес, телефон, дата создания абонента, тарифный план, первая сессия, текущий баланс)*
{code}
select u.contract_number,
u.identify,
av1.attribute_value,
av2.attribute_value,
u.create_date,
tp.plan_name,
(select first 1 s.time_in from sessions s where s.id=u.id),
(DEBIT + OSTATOK - CREDIT)/10000000000.00
from users u
left join attribute_values av1 on av1.user_id=u.id and av1.attribute_id=1
left join attribute_values av2 on av2.user_id=u.id and av2.attribute_id=3
left join TARIF_PLAN TP on U.TARIFF_ID_CACHE = TP.PLAN_ID
where u.id < 10000 and u.deleted = 0 and u.end_user = 1
{code}

*Пример 85. Список абонентов (ФИО абонента, телефон, SMS, адрес) у которых NAS_PORT_ID задан 'abonent_port_id'*
{code}
select U.identify as FIO,
AV1.ATTRIBUTE_VALUE as TEL,
U.sms as SMS,
AV2.ATTRIBUTE_VALUE as ADRES
from users U
left join users_radiusauth UR on U.id=UR.user_id
left join ATTRIBUTE_VALUES AV1 on U.ID = AV1.USER_ID and AV1.ATTRIBUTE_ID = 1
left join ATTRIBUTE_VALUES AV2 on U.ID = AV2.USER_ID and AV2.ATTRIBUTE_ID = 3
where UR.NAS_PORT_ID='abonent_port_id'
{code}


*Пример 86. Список абонентов: №договора, ФИО, адрес подключения, последний тариф, дата подключения (первая сессия), дата закрытия (отключения или удаления)*
Под закрытием (отключением) договора считать галочку "Отключить и запретить вход" или удаление договора.
{code}
select CONTRACT_NUMBER,
IDENTIFY,
(select PLAN_NAME
from TARIF_PLAN
where PLAN_ID = USERS.TARIFF_ID_CACHE),
(select TIME_IN
from REPORT_ GET_FIRST_SESSION_DATE(USERS.ID)),
(select DISABLED_DATE
from REPORT_GET_USER_DISABLED_DATE(USERS.ID))
from USERS
{code}
Уточнение по поводу REPORT_GET_USER_DISABLED_DATE, т.е даты закрытия абонента: в этом месте отчет основывается на неточных данных аудита. Иногда, там не оказывается записи об отключении или удалении.
Там сначала ищется запись о выставлении галочки "отключен" у абонента (тогда дата будет точной), иначе ищется первая запись формата "Услуга не активировалась, т.к USERS.ENABLED=0", т.е дата может отличаться от необходимой от дня до месяца.
Также про deleted.
Если данные в аудите найдены не были - поле будет пустым.


*Пример 87. Количество активных абонентов, общее количество абонентов, количество абонентов, подключенных за указанный период (новые сессии), количество закрытых договоров за указанный период*

{code}
select first 1 (select count(1)
from USERS
where DELETED = 0 and ENABLED = 1 and END_USER = 1),

(select count(1)
from USERS
where END_USER = 1 and DELETED = 0),

(select count(1)
from USERS
where (select TIME_IN
from REPORT_GET_FIRST_SESSION_DATE(USERS.ID)) > '2015-01-01' and (select TIME_IN
from REPORT_GET_FIRST_SESSION_DATE(USERS.ID)) < '2015-02-01'),

(select count(1)
from USERS
where (select DISABLED_DATE
from REPORT_GET_USER_DISABLED_DATE(USERS.ID)) > '2015-01-01' and (select DISABLED_DATE
from REPORT_GET_USER_DISABLED_DATE(USERS.ID)) < '2015-02-01')
from USERS
{code}



*Пример 88. Отчет использования пулов ip адресов*
{code}
select * from REPORT_USR_DYN_IP_RENEW6_DIAG;
{code}
Пояснения полей:
PULL_ID - номер пула
TOTAL - всего адресов в пуле
TOTAL_IN_PULLCACHE - кол-во пред выделенных адресов (далее цифры уже будут зависеть от этого значения)
STEP_1_FREE - кол-во адресов, которые уже выделены абонентам и будут им повторно выделяться
STEP_2_FREE - кол-во адресов, которые не привязаны к абонентам
STEP_3_FREE - кол-во адресов, у которых IP_LOCK is null
STEP_3_FREE_WITHOUT_USED - кол-во адресов, у которых IP_LOCK is null и которые не используются (могут быть забраны у разлогиненого абонента, например)
STEP_4_FREE - кол-во адресов, которые назначены абонентам, но которые можно забрать

*Пример 89. Список финансовых операций, который были проведены задним числом оператором с id=2222 (ФИО, Логин абонента, Дата операции в прошлом, Системная(фактическая) дата, сумма операции, оператор который проводил операцию)*
{code}
select U.identify as "ФИО",
U.login as "Логин клиента",
FO.OP_DATE as "Дата операции",
FO.SYSTEM_DATE as "Системная дата операции",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Сумма",
U2.identify as "Имя администратора"
from finance_operations FO
left join USERS U on FO.user_id = U.ID
left join USERS U2 on FO.owner_id = U2.ID
where
FO.op_type=2 and
cast(FO.OP_DATE as date) < cast(FO.SYSTEM_DATE as date) and
cast(FO.SYSTEM_DATE as date) = current_date and
FO.OWNER_ID = 2222
{code}

*Пример 90. Список id абонентов, с выставленными вручную суммами абон.платы, сумма абон.платы по назначенному тарифу (упрощенная версия, берется только ежемесячная сумма без учета настроек тарифа) и кол-во под-абонентов (актуально для фин.групп)*
{code}
select ID,
ABONENT_PAY / 10000000000.00,
(select ABONENT_PAY
from TARIF_PLAN
where PLAN_ID = USERS.TARIFF_ID_CACHE) / 10000000000.00 as FORCED_PAY_SUM,
(select count(1)
from USERS
where END_USER = 1 and DELETED = 0 and PARID = U.ID) as CHILD_COUNT
from USERS U
where U.ABONENT_PAY is not null
{code}

*Пример 91. Список поступлений абонентов в формате(ФИО, Номер договора, Тарифный план, сумма) за период с 2015-01-01 по 2015-01-31*
{code}
select
u.identify as "ФИО",
u.contract_number as "Договор",
tp.plan_name as "Тариф",
sum(FO.OP_SUMMA)/10000000000.00 as "Расход"
from users u
left join tarif_plan tp on u.TARIFF_ID_CACHE=tp.plan_id
left join finance_operations fo on u.id=fo.USER_ID
where (fo.op_type<>2 and fo.op_type<>3) and (fo.OP_DATE between '2015-01-01' and '2015-07-01')
group by U.IDENTIFY,U.CONTRACT_NUMBER,tp.plan_name
{code}

*Пример 92. Список абонентов в формате ID, ФИО, у которых не заполнен реквизит Паспорт*
{code}
select
u.id,
u.identify
from users u
left join attribute_values av on u.id=av.user_id and av.ATTRIBUTE_ID=14
where av.ATTRIBUTE_VALUE is null and u.id<100000 and u.end_user=1 and u.deleted=0
{code}

*Пример 93. * \*Список итоговой суммы все{*}{color:#333333}{*}х активированных карт по{*}{color} *выбранной группе и времени с учётом включенных папок в папке 656*
{code}
select sum(card_series.card_value)/10000000000 as ALL_PERIOD_SUM FROM CARDS
LEFT JOIN CARD_SERIES ON CARDS.SERIES_NO = card_series.SERIES_NO
left join users on users.id=CARDS.user_id
where users.parid in (select GROUP_ID from GLN_RECURSIVE_GROUP_WALK(656))
and cards.date_used > '2012-12-01 00:00:00'
and cards.date_used < '2013-01-01 00:00:00'
and cards.date_used is not null
{code}

*Пример 94. Дата последнего платежа всех пользователей в группе*
{code}select distinct
users.IDENTIFY as "ФИО",
users.LOGIN as "Логин",
max(FINANCE_OPERATIONS.OP_DATE) as "Дата последнего платежа"
from users
left join FINANCE_OPERATIONS
on users.id=FINANCE_OPERATIONS.USER_ID
where users.parid=1580
and FINANCE_OPERATIONS.OP_TYPE=2
group by users.IDENTIFY, users.LOGIN
{code}

*Пример 95. Отчет для ФСБ. Список пользователей в формате (IP, логин, ФИО, ИНН, Дата рождения, Прописка, Серия паспорта, Номер, Дата выдачи, Кем выдан, Дата подключения, Дата отключения, Номер телефона, Статус)*
{code:lang=sql}
select
uf_ip2string(U.IP) as "IP",
U.LOGIN as "Логин",
U.IDENTIFY as "ФИО",
AV4.ATTRIBUTE_VALUE as "ИНН",
AV3.ATTRIBUTE_VALUE as "Дата рождения",
AV15.ATTRIBUTE_VALUE as "Прописка",
AV14.ATTRIBUTE_VALUE || ' ' ||
AV13.ATTRIBUTE_VALUE || ' ' ||
AV16.ATTRIBUTE_VALUE || ' ' ||
AV17.ATTRIBUTE_VALUE as "Паспортные данные",
U.CREATE_DATE as "Дата подключения",
(select max(ao1.op_time) from audit_operations ao1 where ao1.object_id=u.id and ao1.descr like '%Отключен=1' ) as "Дата отключения",
AV1.ATTRIBUTE_VALUE as "Номер телефона",
U.ENABLED as "Статус (1-подключен,0-отключен)"
from users U
left join attribute_values av1 on U.ID=AV1.user_id and AV1.attribute_id=1
left join attribute_values av4 on U.ID=AV4.user_id and AV4.attribute_id=4
left join attribute_values av13 on U.ID=AV13.user_id and AV13.attribute_id=13
left join attribute_values av14 on U.ID=AV14.user_id and AV14.attribute_id=14
left join attribute_values av15 on U.ID=AV15.user_id and AV15.attribute_id=15
left join attribute_values av16 on U.ID=AV16.user_id and AV16.attribute_id=16
left join attribute_values av17 on U.ID=AV17.user_id and AV17.attribute_id=17
left join attribute_values av3 on U.ID=AV3.user_id and AV3.attribute_id=3
where end_user=1 and u.id<100000
{code}

*Пример 96. Свободные IP из пулов адресов (IP, имя пула, состояние пула - включен(1)/выключен(0))*
{code:lang=sql}
select uf_ip2string(pc.ip) as "IP",
ipp.name as "POOL",
ipp.enabled as "Pool On(1)/Off(0)"
from pull_cache pc
left join ip_pull ipp on ipp.pull_id = pc.pull_id
where pc.user_id is null
order by pc.pull_id, pc.ip
{code}
*Пример 97. Абоненты с не уникальными snat_ip&nbsp;**(ID, SNAT_IP)*
{code:lang=sql}
select id,
uf_ip2string(snatip_cache) as "SNAT_IP"
from users
where uf_ip2string(snatip_cache) in
(select uf_ip2string(snatip_cache) from users group by uf_ip2string(snatip_cache) having count(*)>1)
and server=0
order by snatip_cache desc
{code}

=======
Отчет
Отчеты