... *Пример 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. Список всех доступных телефонов абонентов, у которых баланс меньше либо равен нулю.* {code} select SMS from users where (DEBIT + OSTATOK - CREDIT)<=0 and DELETED=0 and SMS is not null {code} *Пример 4.* *Список всех доступных МАС-адресов абонентов, использующих услугу 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.* *Список{*}* всех абонентов в формате (id, должность, тариф, ФИО, дата внесения) за *{*}период с 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.* *Список{*}* абонентов, созданных за* *заданный период в формате (*{*}id, должность, тариф, ФИО, дата создания) * : {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. * *Список, соответствующих пользователям, кодов 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. Список расхода абонентов за заданный период с 01.11.2012 по 25.11.2012 в формате{*}* (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. Список расхода у не удаленных абонентов за заданный период с 2013-03-01 по 2013-03-31, у которых был трафик в формате {*}* (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. Список расхода абонентов за текущий период в формате (id, финансовый договор, ФИО, расход)* {code} select id, contract_number, identify, credit/10000000000.00 from users where end_user=1 and id<100000 {code} *Пример 12.* *Список пользователей, подключенных за последний месяц (первый запрос) (период с 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.* *Перевод пользователей из подсетей 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.* *Список абонентов с рекомендуемой суммой к оплате* {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.* *Список абонентов из заданной подсети 192.168.0.0/16* {code} select id, login from users where uf_ip2string(ip) like '192.168.%' {code} *Пример 16.* *Список одинаковых ИНН* {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.* *Список должников (Тариф, ФИО, Телефон, Адрес, Баланс)* {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.* *Всех пользователей подсети 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.* *Список (*{*}Ф.И.О. , Адрес, телефон для смс) с сортировкой фамилий по алфавиту* {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.* *Список (*{*}Номер договора, Ф.И.О. , Баланс, Адрес, Тариф )* {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.* *Список ФИО абонентов, у которых запланирован переход в следующем месяце на новый тариф с указанием тарифа и сортировкой по Ф.И.О* {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.* *Список Ф.И.О. абонентов, у которых взят Отложенный платеж и дата его окончания с сортировкой по Ф.И.О.* {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.* *Список оплат через платежные системы (id, номер договора, Ф.И.О. абонента и внесенная сумма за период с 2013.01.01 с группировкой по группам)* {code} select U.ID, U.CONTRACT_NUMBER, U.IDENTIFY, sum(PL.SUMMA_IN)
|
... *Пример 28.* *Список оплат через платежные системы по группам без учета 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.* *Список оплат через платежные системы по группам с учетом 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.* *Список оплат через платежные системы (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.* *Список должников (ФИО, дата последнего платежа, Баланс, Тариф{*}*, Адрес,*{*}Телефон)* {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.* *Список оплат через менеджер и веб-кассир администратором с id=2792{*}* (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за текущий день* {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.* *Сумма проведенных оплат через менеджер и веб-кассир администратором с id=2792 за *{*}текущий день* {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.* *Список оплат через менеджер и веб-кассир администратором с id=2792 (ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 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.owner_id=2792 {code} *Пример 35.* *Список оплат через менеджер (ФИО, логин абонента, дата платежа, сумма, Адрес, кто оплатил) за период с 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.* *Сумма проведенных оплат через менеджер и веб-кассир администратором с id=2792 за период с 2014-03-18 по 2014-03-19 * {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)
|
... /* 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}{*}Доступен с версии 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 * {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 "ФИО",
|