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

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

Необходимо зайти в веб-интерфейс администратора Carbon Billing 5, раздел Отчеты и выбрать Конструктор отчетов.

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

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

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

Примеры:

Пример 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
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

Пример 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С из биллинга.

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. Список должников (ФИО, дата последнего платежа, Баланс, Тариф, Адрес,Телефон)

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

Пример 31. Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период c 2013.02.17 по 2013.02.18

select U.IDENTIFY AS "FIO",
       U.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) between '2013.02.17' and '2013.02.18')

Пример 32. Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за текущий день

select U.IDENTIFY AS "FIO",
       U.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)

Пример 33. Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период с 2012-09-20 по 2013-03-17  

select U.IDENTIFY AS "FIO",
       U.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 '2012-09-20' and '2013-03-17')

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

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

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

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

Пример 36. Список пользователей, у которых активированы дополнительные услуги за период с 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

Пример 37. Список пользователей, у которых активированы дополнительные услуги на данный момент(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

Пример 38. Отчет по сумме оплат за каждый день, внесенных через менеджер за период с 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

Пример 39. Изменить у абонентов тип авторизации с 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

Пример 40. Получить сумму по 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)

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

Запрос:

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_LAST1 (
    OP_TYPE_IN integer)
returns (
    OP_ID integer,
    USER_ID integer,
    OWNER_ID integer,
    OP_TYPE integer,
    OP_DATE timestamp,
    OP_SUMMA numeric(18,0),
    OP_CARD_ID integer,
    SYSTEM_DATE timestamp,
    NUMBER varchar(10),
    BALANCE_BUH numeric(18,0),
    DESCR varchar(255),
    FINANCE integer,
    ISBUHDOC integer,
    END_USER integer,
    OPERATOR_PAY_ID integer,
    OPERATOR_NAME varchar(32),
    OPERATOR_RESULT integer,
    OPERATOR_PAY_ID_STR varchar(32),
    SYNC1C integer,
    CODE1C varchar(32),
    CURS double precision,
    SUM_IN numeric(18,0))
as
declare variable TMP_OP_ID integer;
begin
    for select max(OP_ID)
        from FINANCE_OPERATIONS
        where OP_TYPE = :OP_TYPE_IN
        group by USER_ID
        into TMP_OP_ID
    do
    begin
        select first 1 OP_ID,
                       USER_ID,
                       OWNER_ID,
                       OP_TYPE,
                       OP_DATE,
                       OP_SUMMA,
                       OP_CARD_ID,
                       SYSTEM_DATE,
                       NUMBER,
                       BALANCE_BUH,
                       DESCR,
                       FINANCE,
                       ISBUHDOC,
                       END_USER,
                       OPERATOR_PAY_ID,
                       OPERATOR_NAME,
                       OPERATOR_RESULT,
                       OPERATOR_PAY_ID_STR,
                       SYNC1C,
                       CODE1C,
                       CURS,
                       SUM_IN
        from FINANCE_OPERATIONS
        where OP_ID = :TMP_OP_ID
        order by OP_ID desc
        into :OP_ID,
             :USER_ID,
             :OWNER_ID,
             :OP_TYPE,
             :OP_DATE,
             :OP_SUMMA,
             :OP_CARD_ID,
             :SYSTEM_DATE,
             :NUMBER,
             :BALANCE_BUH,
             :DESCR,
             :FINANCE,
             :ISBUHDOC,
             :END_USER,
             :OPERATOR_PAY_ID,
             :OPERATOR_NAME,
             :OPERATOR_RESULT,
             :OPERATOR_PAY_ID_STR,
             :SYNC1C,
             :CODE1C,
             :CURS,
             :SUM_IN;
        suspend;
    end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON FINANCE_OPERATIONS TO PROCEDURE REP_FINOPER_LAST;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE REP_FINOPER_LAST TO SYSDBA;

Пример 42. Список абонентов в формате (Номер, Логин, Тарифный план, ФИО, Остаток на начало периода, Платеж через кассира, Расход, Расход по трафику, Остаток на конец периода) за период с 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

Пример 43. Список абонентов в формате (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

Пример 44. Список должников (№ договора, Ф.И.О, Адрес, Телефон, 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

Пример 45. Список абонентов (№ договора, Ф.И.О, Адрес, Телефон, 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

Пример 46. Список новых абонентов за период 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

Пример 47. Список удаленных абонентов (№ договора, Ф.И.О, Адрес, Телефон, 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

Пример 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(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

Пример 49. Список отключенных абонентов, не плативших с 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

Пример 50. Список суммы должников (№ договора, Ф.И.О, Адрес, Телефон, 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

Пример 51. Кол-во абонентов на каждом тарифе (Тариф, количество абонентов) сортировка по названию тарифа

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

Пример 52. Кол-во абонентов на каждой услуге (Тариф, количество абонентов) сортировка по названию услуги

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 "Количество абонентоd"
from usluga us  order by 1

Пример 53. Сумма внесенных средств за указанный период(№ договора, Ф.И.О, Адрес, телефон, 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 '2000-06-01' and '2013-06-30'
  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"

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 '2000-06-01' and '2013-06-30'  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 "Сумма операций"

from FINANCE_OPERATIONS
where op_date between '2000-06-01' and '2013-06-30' and op_type=2
 and not descr like '%Обещанный платеж%'
order by 7

Пример 54. Поиск абонента по 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'

Пример 55. Список должников(ФИО, 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

Пример 56. Список сессий по абоненту , пример по абоненту 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

Пример 57. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона

select UR.user_id as ID,
       U.identify as FIO,
       AV.attribute_value as TELEFON
from users_radiusauth UR
left join USERS U on U.id=UR.user_id
left join ATTRIBUTE_VALUES AV on AV.user_id=UR.user_id and AV.ATTRIBUTE_ID=1
where UF_IP2STRING(UR.NAS_IP_ADDRESS)='172.16.0.6'
order by U.identify

Пример 58. Список абонентов, оплативших через платежную систему 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

Пример 59. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, 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

Пример 60. Список абонентов, не плативших в течение 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
order by U.IDENTIFY

Пример 61. Общее количество абонентов по группам

select
name as "Группа",
(select count(1) from abonents where parent_id=grp.id) as "Количество абонентов"
from abonents grp
where is_folder=1

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

select
cast(avg(ss) as numeric(18,2)) as "Выручка",
year_number  as "Год",
month_number as "Месяц"
from
(select sum(summ) as ss,year_number,month_number from counters where (current_date - cast(year_number || '-' || month_number || '-01' as date) <= :cnt.Количество дней до текущей даты$)
group by ABONENT_ID,year_number,month_number)
group by year_number,month_number

Пример 63. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента)

select A.CONTRACT_NUMBER,
       A.NAME,
       T.NAME,
       ABSP.CEIL_IN,
       (select AV.ATTRIBUTE_VALUE
        from ATTRIBUTE_VALUES AV
        where AV.ATTRIBUTE_ID = 14
              and AV.ABONENT_ID = A.ID),
       (select AV.ATTRIBUTE_VALUE
        from ATTRIBUTE_VALUES AV
        where AV.ATTRIBUTE_ID = 13
              and AV.ABONENT_ID = A.ID),
       (select AV.ATTRIBUTE_VALUE
        from ATTRIBUTE_VALUES AV
        where AV.ATTRIBUTE_ID = 16
              and AV.ABONENT_ID = A.ID),
       (select AV.ATTRIBUTE_VALUE
        from ATTRIBUTE_VALUES AV
        where AV.ATTRIBUTE_ID = 17
              and AV.ABONENT_ID = A.ID),
       H.CITY,
       H.STREET,
       H.S_NUMBER,
       H.S_LITER,
       A.A_HOME_NUMBER,
       A.SMS
from ABONENTS A
left join TARIF T on T.ID = A.TARIF_ID
left join ABONENTS_SPEED ABSP on ABSP.ABONENT_ID = A.ID
left join HOMES H on H.ID = A.HOME_ID
where A.DELETED != 1
      and A.IS_FOLDER != 1
order by A.NAME

Пример 64. Наработка по дням

select C.DT,
       A.PARENT_ID,
       (select NAME
        from ABONENTS
        where ID = A.PARENT_ID),
       sum(C.SUMM),
       'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
                                                                        from ABONENTS
                                                                        where DELETED = 0 and IS_FOLDER = 0 and PARENT_ID = A.PARENT_ID)
from ABONENTS A
join(select sum(SUMM) as SUMM,
            ABONENT_ID,
            cast(S_DATE as date) as DT
     from COUNTERS
     group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3

union

select C.DT,
       9999999,
       'Сумма',
       sum(C.SUMM),
       'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
                                                                        from ABONENTS
                                                                        where DELETED = 0 and IS_FOLDER = 0)
from ABONENTS A
join(select sum(SUMM) as SUMM,
            ABONENT_ID,
            cast(S_DATE as date) as DT
     from COUNTERS
     group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3


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