Для создания отчета через конструктор отчетов необходимо:
1. Зайти в Carbon Manager "Отчеты -> Конструктор отчетов"
2. Добавить новый отчет кнопкой "+" справа ("-" удаляет выбранный отчет)
3. Задать название отчета и текст запроса, который вы можете уточнить у тех.поддержки (должен начинаться со слова "select").
4. Также есть возможность настроить привилегии доступа для разных типов администраторов.
Для вывода отчета достаточно выбрать нужный и нажать кнопку "Запросить".
5. Начиная с версии 3.9.6 484 есть возможность выгрузить полученный отчет в Excel. Работает только под системой Windows!
Выгрузка может не работать, если имя отчета длиннее 31 символа!
Важно: Если у вас не выводится отчет при нажатии на кнопку запросить(и она впадает при наведении курсора или выводится ошибка "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 символов от пароля локальной консоли (или меньше, если пароль меньше восьми символов)
Также запросы можно выполнять из командной строки.
Выполнение запросов из командной строки
Из командной сторки запросы выполняются при помощи утилиты sqlexec.
Дополнительные моменты
Вывод на экран в виде списка sqlexec "set list on ; текст запроса"
Вывод на экран русских символов - добавьте в конец строки | iconv -f cp1251
Запись в файл, для того чтобы забрать его по WinSCP / SCP позже > /tmp/file
Примеры:
Пример 1. Список всех доступных телефонов абонентов в формате (порядковый номер, логин абонента, номер телефона)
select id, login, SMS from users where DELETED=0 and sms is not null
Пример 2. Список ФИО абонентов и их баланс на текущий момент
select identify as "FIO", (DEBIT + OSTATOK - CREDIT)/10000000000.00 as "BALANCE" from users where id<100000 and deleted=0 and end_user=1
Пример 3. Список всех доступных телефонов абонентов, у которых баланс меньше либо равен нулю.
select SMS from users where (DEBIT + OSTATOK - CREDIT)<=0 and DELETED=0 and SMS is not null
Пример 4. Список всех доступных МАС-адресов абонентов, использующих услугу IPTV с известным id.
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 \!= ''
Пример отчетов по VOIP.
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
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
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
Пример 5. Список ФИО и Адрес всех абонентов. у которых в качестве nas_ip указан адрес 172.16.1.177:
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'
Пример 6. Список всех абонентов в формате (id, должность, тариф, ФИО, дата внесения) за период с 2012-11-01 до 2012-11-30 )
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')
Пример 7. Список абонентов, созданных за заданный период в формате (id, должность, тариф, ФИО, дата создания) :
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')
Пример 8. Список, соответствующих пользователям, кодов 1С из биллинга.
select U.ID as "Номер", U.IDENTIFY as "Наименование", U.CODE1C "Код синхронизации", U.SYNC1C as "1-синхронизирован/0-несинхронизирован" from USERS U where U.END_USER = 1
Пример 9. Список расхода абонентов за заданный период с 01.11.2012 по 25.11.2012 в формате (id, финансовый договор, ФИО, расход) (Для закрытого периода)
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
Пример 10. Список расхода у не удаленных абонентов за заданный период с 2013-03-01 по 2013-03-31, у которых был трафик в формате {} (id, финансовый договор, ФИО, расход) (Для закрытого периода)
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
Пример 11. Список расхода абонентов за текущий период в формате (id, финансовый договор, ФИО, расход)
select id, contract_number, identify, credit/10000000000.00 from users where end_user=1 and id<100000
Пример 12. Список пользователей, подключенных за последний месяц (первый запрос) (период с 2011-11-20 до 2011-12-20 ) и их количества (второй запрос).:
SELECT id, identify, login, create_date FROM users WHERE create_date>'2011-11-20' AND end_user=1 AND create_date<'2011-12-20'
SELECT COUNT(id) AS usercount FROM users WHERE create_date>'2011-11-20' AND end_user=1 AND create_date<'2011-12-20'
Список с выводом адреса
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'
Пример 13. Перевод пользователей из подсетей 10.20.0.0/16...10.25.0.0/16 на Carbon AS с адресом 172.16.1.15
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.%'
Пример 14. Список абонентов с рекомендуемой суммой к оплате
select user_id, sum(OP_SUMMA/10000000000) from finaions where op_date>(current_timestamp-120) and op_type=2 group by user_id
Пример 15. Список абонентов из заданной подсети 192.168.0.0/16
select id, login from users where uf_ip2string(ip) like '192.168.%'
Пример 16. Список одинаковых ИНН
select count(attribute_value), attribute_value from attribute_values where attribute_id=4 group by attribute_value having count(attribute_value)>1
Пример 17. Список должников (Тариф, ФИО, Телефон, Адрес, Баланс)
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
Пример 18. Всех пользователей подсети 10.1.0.0/16 перевести на NAS 172.0.1.1, прикрепив к нему, установить авторизацию по радиус и поменять SNAT адрес на 192.168.5.0.
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.%'"
Пример 19. Список номинала всех активированных карт за выбранный период
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
Пример 20. Список итоговой суммы всех активированных карт за выбранный период
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
Пример 21.Список (Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по алфавиту
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
Пример 22.Список (Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по ip
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
Пример 23. Список (Ф.И.О. , Адрес, телефон для смс) с сортировкой фамилий по алфавиту
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
Пример 24. Список (Номер договора, Ф.И.О. , Баланс, Адрес, Тариф )
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
Пример 25. Список ФИО абонентов, у которых запланирован переход в следующем месяце на новый тариф с указанием тарифа и сортировкой по Ф.И.О
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
Пример 26. Список Ф.И.О. абонентов, у которых взят Отложенный платеж и дата его окончания с сортировкой по Ф.И.О.
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
Пример 27. Список оплат через платежные системы (id, номер договора, Ф.И.О. абонента и внесенная сумма за период с 2013.01.01 с группировкой по группам)
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
Пример 28. Список оплат через платежные системы по группам без учета web-интерфейса кассира (id, название группы и внесенная сумма за период с 2013.04.15 до 2013.04.17)
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
Пример 29. Список оплат через платежные системы по группам с учетом web-интерфейса кассира (id, название группы и внесенная сумма за период с 2013.04.15 до 2013.04.17)
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
Пример 30. Список оплат через платежные системы (id, договор, ФИО, название платежной системы, внесенная сумма и дата платежа за период с 2014-01-01 до 2014-02-01)
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')
Пример 31. Список должников (ФИО, дата последнего платежа, Баланс, Тариф, Адрес,Телефон)
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
Пример 32. Список оплат через менеджер и веб-кассир администратором с id=2792 (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за текущий день
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
Пример 33. Сумма проведенных оплат через менеджер и веб-кассир администратором с id=2792 за текущий день
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
Пример 34. Список оплат через менеджер и веб-кассир администратором с id=2792 (ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 2014-03-18 по 2014-03-19
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
Пример 35. Список оплат через менеджер (ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 2014-03-18 по 2014-03-19
примечание: в отчете считается что комментарий при проведении платежа не изменяется администратором и имеет вид ДД.ММ.ГГГГ-ДД.ММ.ГГГГ
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 '%.%.%-%.%.%'
Пример 36. Сумма проведенных оплат через менеджер и веб-кассир администратором с id=2792 за период с 2014-03-18 по 2014-03-19
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
Пример 37. Список логинов и паролей абонентов
select id, identify, login, gen_pwd AS "password" from users where DELETED=0 and end_user=1
Пример 38. Список пользователей, у которых активированы дополнительные услуги за все время работы (ФИО, название услуги, дата активации)
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
Пример 39. Список пользователей, у которых активированы дополнительные услуги за период с 2013.03.01 по 2013.04.01 (id, ФИО, название услуги, дата активации)
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
Пример 40. Список пользователей, у которых активированы дополнительные услуги за период с 2013.03.01 по 2013.04.01 (id, ФИО, название услуги, стоимость услуги, дата активации)
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
Пример 41. Список пользователей, у которых активированы дополнительные услуги на данный момент(id, ФИО, название услуги)
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
Пример 42. Отчет по сумме оплат за каждый день, внесенных через менеджер за период с 2013.03.01 по 2013.03.25
В консоли сервера выполните:
1. Открыть базу для редактирования
sqlexec "alter exception ERR 'Error '"
2. Добавить в таблицу FINANCE_OPERATIONS поле OP_DATE_DAY
sqlexec "ALTER TABLE FINANCE_OPERATIONS ADD OP_DATE_DAY DATE"
3. Закрыть базу для редактирования
sqlexec "alter exception ERR 'Error'"
Отчет строится из двух запросов. Запросы необходимо выполнять последовательно.
запрос 1:
update FINANCE_OPERATIONS set op_date_day = op_date
запрос 2:
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
Пример 43. Изменить у абонентов тип авторизации с Radius на ip (адрес NAS - 192.168.0.222)
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
Пример 44. Получить сумму по 2 услугам за период по пользователю услуга 10 и 11
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)
Пример 45. Список абонентов с последней оплатой (ФИО,Логин,Договор,Дата последнего платежа,Сумма последнего платежа,Текущий баланс)
Запрос:
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
Предварительно Через ibexpert или sqlexec сделать процедуру
SET TERM ^ ; create procedure REP_FINOPER_LAST ( 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 and DESCR not like '%Обещанный платеж%' 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;
Пример 46. Список абонентов в формате (Номер, Логин, Тарифный план, ФИО, Остаток на начало периода, Платеж через кассира, Расход, Расход по трафику, Остаток на конец периода) за период с 2013-05-01 по 2013-05-15 Доступен с версии 397_359
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
Пример 47. Список абонентов в формате (ID, ФИО, тарифный_план) у которых за период 2013-04-01 - 2013-05-01 был трафик больше 100Кб
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
Пример 48. Список должников (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по № договора
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
Пример 49. Список абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.
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
Пример 50. Список новых абонентов за период 2013-03-01 - 2013-03-30 (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по дате подключения.
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
Пример 51. Список удаленных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.
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
Пример 52. Список отключенных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата отключения, Баланс, Статус) сортировка по Ф.И.О.
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
Пример 53. Список заблокированных абонентов (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Баланс) сортировка по Ф.И.О.
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
Пример 54. Список отключенных абонентов, не плативших с 2013.04.01 (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата отключения, Баланс, Статус) сортировка по Ф.И.О.
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
Пример 55. Список суммы должников (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата последней оплаты, Баланс, Статус) сортировка по Ф.И.О.
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
Пример 56. Кол-во абонентов на каждом тарифе (Тариф, количество абонентов) сортировка по названию тарифа
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
Пример 57. Кол-во абонентов на каждой услуге (Тариф, количество абонентов) сортировка по названию услуги
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
Пример 58. Сумма внесенных средств за указанный период(№ договора, Ф.И.О, Адрес, телефон, IP, баланс, дата последней операции, кем или чем проведена операция) сортировка по дате последней операции в конце общая сумма всех операций
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
Пример 59. Поиск абонента по MAC (52:54:00:BC:AA:45) (при условии что у абонента стоит статическая привязки) в формате id, ФИО, ip
SELECT id, identify, uf_ip2string(ip) from USERS where MAC='52:54:00:BC:AA:45'
Пример 60. Список должников(ФИО, IP)
Сортировка по ФИО
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
Сортировка по IP
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
Пример 61. Список сессий по абоненту , пример по абоненту id 245 (id,IP-адрес,Начало сессии, конец сессии) с сортировкой по дате
Сортировка по ФИО
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
Пример 62. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, логин, фио, номер телефона
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
Пример 63. Список абонентов, оплативших через платежную систему telepay в формате id, фио, сумма_платежа
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
Пример 64. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, IP коммутатор, Номер порта, Номер vlan) с группировкой по номеру коммутатора
select
u.identify as FIO,
sw.name as NAME,
uf_ip2string(sw.ip) as SWITCH_IP,
sp.num as PORT,
sp.vlan as VLAN
from switch_ports sp
join users u on u.id=sp.user_id
join switch sw on sw.id=sp.switch_id
order by sp.switch_id
Пример 65. Список абонентов, не плативших в течение 3-х месяцев (№ договора, Ф.И.О, Адрес, Телефон, IP, Тариф, Дата подключения, Дата блокировки по балансу, Баланс, Статус) сортировка по Ф.И.О.
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
Пример 66. Список абонентов в формате (ФИО, NAT, Дата последнего платежа, Кто провел платеж)
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
Пример 67. Вычислить направление звонка по номеру (351111111 нужно заменить на нужный номер)
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
Пример 68. Список абонентов работающих через определенный NAS (ID, ФИО, IP-адрес абонента, IP-адрес NAS сервера)
select id, identify FIO, uf_ip2string(ip) IP, uf_ip2string(snatip) sNAT from users where uf_ip2string(snatip)='ip_адрес_наса' order by id
Пример 69. Отчет по voip звонкам за месяц
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
Пример 70. Отчет по приходу абонентов за период с 2013-09-01 по 2013-10-30 в формате (Логин, IP, Баланс, ФИО, Адрес, Телефон, Номер студенческого, Сумма)
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
Пример 71. Количество занятых адресов в пуле с номером 7
select count(id) from users where (select pull_id from GLN_USR_IP_PULL(users.id))=7
Пример 72. Вывод занятых адресов в пуле с номером 7 в формате (id абонента, IP)
select id, uf_ip2string(ip) from users where (select pull_id from GLN_USR_IP_PULL(users.id))=7
Пример 73. Список должников за месяц с 2013-11-01 по 2013-11-30 в формате ФИО, Логин, Телефон, IP коммутатора, Порт Коммутатора
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')
Пример 74.Список id абонентов, у которых отрицательный баланс был до и по настоящий момент 2013-11-01
select id from users where over_limit_date<'2013-11-01'
Пример 75.Список абонентов в формате (id, ФИО, Логин, Баланс, Тариф, IP), которые входят в группу 888 (Либо любой номер группы из вашего дерева)
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
Пример 76.Список абонентов в формате (id,время начала сессии, время окончания сессии) которые использовали ip 3.3.3.3 в период с 03-11-2013 по 05-11-2013. Для МВД
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')
Пример 77.Список абонентов в формате (id,время начала сессии, время окончания сессии) которые использовали natip 3.3.3.3 в период с 03-11-2013 по 05-11-2013. Для МВД
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')
Пример 78.Список абонентов в формате (id,время начала сессии, время окончания сессии, телефон, ФИО, Логин, Адрес) которые использовали natip 3.3.3.3 в период с 03-11-2013 по 05-11-2013. Для МВД
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
Пример 79. Список sNAT адресов и пользователей на них
select count(*) as "Users", uf_ip2string(snatip_cache) as "sNAT" from users group by uf_ip2string(snatip_cache)
Пример 80. Список абонентов, которым меняли дату отключения в период с 2014-06-01 по 2014-07-01 (ID, ФИО, Дата смены, Кто менял, Описание)
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')
Пример 81. Подсчет количества абонентов, активных(пользующихся интернетом) в сентябре 2014 года (Частое выполнение нагружает сервер)
select count(distinct id) from sessions where MONTH_IN='2014-09-01' and id<100000"
Пример 82. Объем трафика в разрезе периода (1.10.2014 - 30.10.2014) по тарифам с группировкой
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
Пример 83. Список оплат через менеджер и веб-кассир с комментарием "Бонус" (ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 2014-12-01 по 2014-12-31
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 '%Бонус%')
Пример 84. Список Ф.И.О. абонента, его адреса и номера телефона с сортировкой по улице и номеру дома из поля "Дом"
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
Пример 85. Список Ф.И.О. абонента, его адреса, номера квартиры и номера телефона с сортировкой по улице и номеру дома из поля "Дом"
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
Пример 84. Список абонентов (Номер договора, ФИО абонента, адрес, телефон, дата создания абонента, тарифный план, первая сессия, текущий баланс)
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
Пример 85. Список абонентов (ФИО абонента, телефон, SMS, адрес) у которых NAS_PORT_ID задан 'abonent_port_id'
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'
Пример 86. Список абонентов: №договора, ФИО, адрес подключения, последний тариф, дата подключения (первая сессия), дата закрытия (отключения или удаления)
Под закрытием (отключением) договора считать галочку "Отключить и запретить вход" или удаление договора.
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
Уточнение по поводу REPORT_GET_USER_DISABLED_DATE, т.е даты закрытия абонента: в этом месте отчет основывается на неточных данных аудита. Иногда, там не оказывается записи об отключении или удалении.
Там сначала ищется запись о выставлении галочки "отключен" у абонента (тогда дата будет точной), иначе ищется первая запись формата "Услуга не активировалась, т.к USERS.ENABLED=0", т.е дата может отличаться от необходимой от дня до месяца.
Также про deleted.
Если данные в аудите найдены не были - поле будет пустым.
Пример 87. Количество активных абонентов, общее количество абонентов, количество абонентов, подключенных за указанный период (новые сессии), количество закрытых договоров за указанный период
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
Пример 88. Отчет использования пулов ip адресов
select * from REPORT_USR_DYN_IP_RENEW6_DIAG;
Пояснения полей:
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 (ФИО, Логин абонента, Дата операции в прошлом, Системная(фактическая) дата, сумма операции, оператор который проводил операцию)
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
Пример 90. Список id абонентов, с выставленными вручную суммами абон.платы, сумма абон.платы по назначенному тарифу (упрощенная версия, берется только ежемесячная сумма без учета настроек тарифа) и кол-во под-абонентов (актуально для фин.групп)
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
Пример 91. Список поступлений абонентов в формате(ФИО, Номер договора, Тарифный план, сумма) за период с 2015-01-01 по 2015-01-31
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_DATE between '2015-01-01' and '2015-07-01') group by U.IDENTIFY,U.CONTRACT_NUMBER,tp.plan_name
Пример 92. Список абонентов в формате ID, ФИО, у которых не заполнен реквизит Паспорт
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
Пример 93. * *Список итоговой суммы всех активированных карт по выбранной группе и времени с учётом включенных папок в папке 656
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
Пример 94. Дата последнего платежа всех пользователей в группе
select distinct users.IDENTIFY as "ФИО", users.login as "Логин", users.sms as "Телефон", AV.ATTRIBUTE_VALUE as "Адрес", max(FINANCE_OPERATIONS.OP_DATE) as "Дата последнего платежа" from users left join FINANCE_OPERATIONS on users.id=FINANCE_OPERATIONS.USER_ID and FINANCE_OPERATIONS.OP_TYPE=2 left join ATTRIBUTE_VALUES AV on ( Users.ID = AV.USER_ID and AV.ATTRIBUTE_ID = 3) where users.parid=8 and users.deleted=0 group by users.IDENTIFY, users.sms, users.login,AV.ATTRIBUTE_VALUE
Пример 95. Отчет для ФСБ. Список пользователей в формате (IP, логин, ФИО, ИНН, Дата рождения, Прописка, Серия паспорта, Номер, Дата выдачи, Кем выдан, Дата подключения, Дата отключения, Номер телефона, Статус)
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
Пример 96. Свободные IP из пулов адресов (IP, имя пула, состояние пула - включен(1)/выключен(0))
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
Пример 97. Абоненты с не уникальными snat_ip (ID, SNAT_IP)
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
Пример 98. Списание платы за выбранные по id (ruleset_no) доп услуги за определенный период (ФИО, Услуга, Количество дней, Списано руб.)
select u.identify as "ФИО",us.name as "Услуга",count(ruleset_no) as "Количество дней",sum(s.session_credit)/10000000000 as "Списано, руб." FROM sessions s left join users u on s.id=u.id left join usluga us on -us.id=s.ruleset_no where s.ruleset_no in (-31,-35,-37,-38,-42,-43,-44) and (s.date_in between '2017-02-01' and '2017-02-28') group by s.id,u.identify,us.name order by u.identify
Пример 99. Список подключенных услуг у конкретного абонента с указанным логином (Услуга)
select us.name as "Услуга" from users_usluga uu left join users u on uu.user_id=u.id left join usluga us on uu.USLUGA_ID=us.id where u.login='pupkin'
Пример 100. Список приходов абонентов на конкретном тариф за указанный период
select sum(FO.OP_SUMMA)/10000000000.00 as "Сумма" from finance_operations fo left join users u on u.id=fo.USER_ID where fo.op_type=2 and u.TARIFF_ID_CACHE=37 and OP_DATE between '2016-09-01' and '2016-10-01'
Пример 101. Список приходов абонентов через платежные системы за указанный период с указанием группы абонентов (папки) со всеми подгруппами и с Итоговой суммой (Номер договора, ФИО, Дата последнего платежа, Сумма платежа)
select U.CONTRACT_NUMBER, U.IDENTIFY as "FIO", max(PL.OPERATOR_DATE_IN) as "LAST PAY DATE", 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' and U.parid in (select GROUP_ID from GLN_RECURSIVE_GROUP_WALK(654)) group by U.CONTRACT_NUMBER, U.IDENTIFY union select cast('total' as varchar(32)), cast('' as varchar(128)), cast('' as timestamp), 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' and U.parid in (select GROUP_ID from GLN_RECURSIVE_GROUP_WALK(654))
Пример 102. Отчёт по платежам за выбранный период в формате "id платежа - номер договора - сумма платежа"
select
fo.OP_ID,
u.contract_number,
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_DATE between '2015-01-01' and '2018-07-01')
group by fo.OP_ID,U.CONTRACT_NUMBER
Пример 103. Отчёт который показывает абонентов, у которых финансовых операций с типом расход больше, чем 1 за один и тот же день.
select OP_DATE, user_id, count(*) from finance_operations where op_type=1 group by OP_DATE,user_id having count(*)>1