Необходимо зайти в веб-интерфейс администратора Carbon Billing 5, раздел *Отчеты* и выбрать *Конструктор отчетов*.
Есть готовые отчеты, также можно создать свой отчет.
Отличительная особенность отчета состоит в том, что можно указывать параметры. Если в настройках указать параметр (prm), то при нажатии запустить отчет, можно будет задать параметр. Задать название отчета и текст запроса, который вы можете уточнить у тех.поддержки (должен начинаться со слова "select").
!жур2.JPG|border=1!
Данные в отчете можно выгрузить в csv.
!жур3.JPG|border=1!
h3. Параметры полей формы
Переменные, отображаемые в виде формы:
{code:lang=sql}':title|type[params]$'{code}
* : - указывает, что это строка с переменной
* title - заголовок, отображаемый на форме
* \| - разделитель заголовка и типа
* type\[params\] - тип с параметрами:
** date - дата, без параметров, отображает на форме календарь выбора даты
{code}BILL_DATE between ':Начало|date$' and ':Конец|date$'{code}
** choices - список
{code:lang=sql}Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$'
Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'{code}
** select - список, построенный на основе модели, подобно работе [API|CarbonBilling:API REST v2.0], параметры содержат модель (обязательно) и фильтры
{code}a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
h.id = ':Заявка|select[HDSK]$'{code}
* $ - флаг отмечает обязательную переменную
h2. {color:#000000}{*}Примеры:*{color}
*Пример 1.* *Список всех доступных телефонов абонентов в формате (порядковый номер, логин абонента, номер телефона)*
{code}
select id, login, SMS from users where DELETED=0 and sms is not null
{code}
*Пример 2.* *Список ФИО абонентов и их баланс на текущий момент*
{code}
select
ab.name as "Ф.И.О.",
(ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс"
from abonents ab
left join admin_accounts ac on ab.account_id = ac.id
left join users u on u.abonent_id = ab.id
where
ab.deleted<>1
and ab.is_folder<>1
and u.id is not null
order by ab.name
{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
ab.name as "FIO",
uf_ip2string(u.ip) as "IP",
uf_ip2string(u.nas_ip) as "NAS",
(ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс"
from users u
left join abonents ab on u.abonent_id = ab.id
left join admin_accounts ac on ab.account_id = ac.id
where
ab.deleted<>1
and ab.is_folder<>1
and u.id is not null
order by ab.name{code}
*Пример 22.*{*}Список (*{*}Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по ip*
{code}select
select
ab.name as "FIO",
uf_ip2string(u.ip) as "IP",
uf_ip2string(u.nas_ip) as "NAS",
(ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс"
from users u
left join abonents ab on u.abonent_id = ab.id
left join admin_accounts ac on ab.account_id = ac.id
where
ab.deleted<>1
and ab.is_folder<>1
and u.id is not null
order by u.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)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID
where PL.MSG_OUT = 'ACCEPTED' and PL.OPERATOR_DATE_IN >= '2013.01.01'
group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY
{code}
*Пример 28.* *Список оплат через платежные системы по группам без учета 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.* *Список должников (ФИО, дата последнего платежа, Баланс, Тариф{*}*, Адрес,*{*}Телефон)*
{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}
*Пример 31.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период c 2013.02.17 по 2013.02.18*
{code}
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')
{code}
*Пример 32.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за текущий день*
{code}
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)
{code}
*Пример 33.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период с 2012-09-20 по 2013-03-17 *
{code}
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')
{code}
*Пример 34. Список логинов и паролей абонентов*
{code}
select id, identify, login, gen_pwd AS "password" from users where DELETED=0 and end_user=1
{code}
*Пример 35. Список пользователей, у которых активированы дополнительные услуги за все время работы (ФИО, название услуги, дата активации)*
{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}
*Пример 36. Список пользователей, у которых активированы дополнительные услуги за период с 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}
*Пример 37. Список пользователей, у которых активированы дополнительные услуги на данный момент(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}
*Пример 38. Отчет по сумме оплат за каждый день, внесенных через менеджер за период с 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}
*Пример 39. Изменить у абонентов тип авторизации с 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}
*Пример 40. Получить сумму по 2 услугам за период по пользователю услуга 10 и 11*
{code}
SELECT SUM(session_credit) FROM sessions WHERE (time_in between '2013-03-01' and '2013-04-01') AND id = 2564 AND (ruleset_no = -10 OR ruleset_no = -11)
{code}
*Пример 41. Список должников с последней оплатой (ФИО,Логин,Договор,Дата последнего платежа,Сумма последнего платежа,Текущий баланс)*
Запрос:
{code}
select U.identify,U.login,U.contract_number,RPL.OP_DATE,RPL.OP_SUMMA,RPL.BALANCE_BUH from users U
join REP_FINOPER_LAST(2) RPL on RPL.user_id=u.id
{code}
Предварительно Через [ibexpert|http://docs.carbonsoft.ru/pages/viewpage.action?pageId=3440679] или sqlexec сделать процедуру
{code}
SET TERM ^ ;
create procedure REP_FINOPER_LAST1 (
OP_TYPE_IN integer)
returns (
OP_ID integer,
USER_ID integer,
OWNER_ID integer,
OP_TYPE integer,
OP_DATE timestamp,
OP_SUMMA numeric(18,0),
OP_CARD_ID integer,
SYSTEM_DATE timestamp,
NUMBER varchar(10),
BALANCE_BUH numeric(18,0),
DESCR varchar(255),
FINANCE integer,
ISBUHDOC integer,
END_USER integer,
OPERATOR_PAY_ID integer,
OPERATOR_NAME varchar(32),
OPERATOR_RESULT integer,
OPERATOR_PAY_ID_STR varchar(32),
SYNC1C integer,
CODE1C varchar(32),
CURS double precision,
SUM_IN numeric(18,0))
as
declare variable TMP_OP_ID integer;
begin
for select max(OP_ID)
from FINANCE_OPERATIONS
where OP_TYPE = :OP_TYPE_IN
group by USER_ID
into TMP_OP_ID
do
begin
select first 1 OP_ID,
USER_ID,
OWNER_ID,
OP_TYPE,
OP_DATE,
OP_SUMMA,
OP_CARD_ID,
SYSTEM_DATE,
NUMBER,
BALANCE_BUH,
DESCR,
FINANCE,
ISBUHDOC,
END_USER,
OPERATOR_PAY_ID,
OPERATOR_NAME,
OPERATOR_RESULT,
OPERATOR_PAY_ID_STR,
SYNC1C,
CODE1C,
CURS,
SUM_IN
from FINANCE_OPERATIONS
where OP_ID = :TMP_OP_ID
order by OP_ID desc
into :OP_ID,
:USER_ID,
:OWNER_ID,
:OP_TYPE,
:OP_DATE,
:OP_SUMMA,
:OP_CARD_ID,
:SYSTEM_DATE,
:NUMBER,
:BALANCE_BUH,
:DESCR,
:FINANCE,
:ISBUHDOC,
:END_USER,
:OPERATOR_PAY_ID,
:OPERATOR_NAME,
:OPERATOR_RESULT,
:OPERATOR_PAY_ID_STR,
:SYNC1C,
:CODE1C,
:CURS,
:SUM_IN;
suspend;
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON FINANCE_OPERATIONS TO PROCEDURE REP_FINOPER_LAST;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE REP_FINOPER_LAST TO SYSDBA;
{code}
*Пример 42. Список абонентов в формате (Номер, Логин, Тарифный план, ФИО, Остаток на начало периода, Платеж через кассира, Расход, Расход по трафику, Остаток на конец периода) за период с 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}
*Пример 43. Список абонентов в формате (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}
*Пример 44. Список должников (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 45. Список абонентов (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 46. Список новых абонентов за период 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}
*Пример 47. Список удаленных абонентов (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 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(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}
*Пример 49. Список отключенных абонентов, не плативших с 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}
*Пример 50. Список суммы должников (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 51. Кол-во абонентов на каждом тарифе (Тариф, количество абонентов) сортировка по названию тарифа*
{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}
*Пример 52. Кол-во абонентов на каждой услуге (Тариф, количество абонентов) сортировка по названию услуги*
{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 "Количество абонентоd"
from usluga us order by 1
{code}
*Пример 53. Сумма внесенных средств за указанный период(№ договора, Ф.И.О, Адрес, телефон, 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 '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
{code}
*Пример 54. Поиск абонента по 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}
*Пример 55. Список должников(ФИО, 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}
*Пример 56. Список сессий по абоненту , пример по абоненту 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}
*Пример 57. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона*
{code}
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
{code}
*Пример 58. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата*
{code}
select LOGIN_IN as LOGIN,
USER_NAME_OUT as FIO,
SUMMA_IN as SUMMA,
OPERATOR_DATE_IN as DATA
from PAY_LOG
where MSG_OUT='ACCEPTED' and ACT_IN='PAY' and PAY_OPERATOR='Qiwiwallet'
order by USER_NAME_OUT
{code}
*Пример 59. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, 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}
*Пример 60. Список абонентов, не плативших в течение 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
order by U.IDENTIFY
{code}
*Пример 61. Общее количество абонентов по группам*
{code}
select
name as "Группа",
(select count(1) from abonents where parent_id=grp.id) as "Количество абонентов"
from abonents grp
where is_folder=1
{code}
*Пример 62. Средняя выручка(в расчёте на одного абонента) за предыдущий и текущий месяц или с возможностью выбора периода*
{code}
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
{code}
*Пример 63. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента)*
{code}
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
{code}
*Пример 64. Отчет по списаниям по абонентам в формате (число, id группы, название группы, сумма и кол-во абонентов, по которым были списания) и последней строкой сумма за день.*
{code}
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
{code}
*Пример 65. Отчет по должникам для отключения КТВ (Улица, Дом, Подъезд, Квартира, ФИО, Тариф) игнорируя ручной статус "Отключен"*
{code}
select
h.street as "Улица",
h.s_number as "Номер дома",
a.home_entrance as "Номер подъезда",
a.A_HOME_NUMBER as "Номер квартиры",
a.name as "ФИО",
t.name as "Тариф"
from abonents_block ab
left join abonents a on ab.abonent_id=a.id
left join homes h on a.home_id=h.id
left join tarif t on a.tarif_id=t.id
left join objects_status os on a.id=os.object_id
where ab.b_negbal=1 and a.tarif_id in (21,22,23,24) and (os.status<>5 or os.status is null)
group by 1,2,3,4,5,6
order by 6
{code}
*Пример 66. Отчет по свободным IP адресам*
{code}
select uf_ip2string(pc.ip) as "IP адрес",
ipp.name as "Pull",
(case when ipp.enabled <> 0 THEN 'Да' ELSE 'Нет' END) as "Включен"
from pull_cache pc
left join ip_pull ipp on ipp.pull_id = pc.pull_id
where
pc.user_id is null
order by pc.pull_id, pc.ip
{code}
*Пример 67. Кто платит через Юнителлер?*
{code}
select distinct(pay_id_str_in) as "PAY",
CONTRACT_NUMBER_IN as "Договор",
USER_NAME_OUT as "ФИО",
operator_date_in as "Дата",
SUMMA_IN as "Сумма"
from pay_log
where (operator_src_ip='213.208.182.172' or pay_operator='Uniteller')
and act_in='pay'
and operator_date_in between ':C_даты|date$' and ':По_дату|date$'
order by user_name_out
{code}
*Пример 68. Количество услуг из ЛК за месяц по абонентам*
{code}
select A.ID,
A.CONTRACT_NUMBER,
A.NAME,
count(*)
from USERS_USLUGA UU
left join ABONENTS A on UU.ABONENT_ID = A.ID
where UU.USLUGA_ID = ':ID услуги$' and UU.IS_ZAKAZANO = 1 and UU.CREATE_DATE between ':C_даты|date$' and ':По_дату|date$'
group by A.ID, A.CONTRACT_NUMBER, A.NAME
having count(*)>1
order by A.NAMEe_in between ':C_даты|date$' and ':По_дату|date$'
{code}
\*Пример 69. Прибыль с комиссии за обещанный платеж (все периоды)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.MONTH_NUMBER as "номер месяца",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
union
select '#ИТОГО',
'',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
order by 1
{code}\*Пример 69. Прибыль с комиссии за обещанный платеж (по периодам)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
order by 1
{code}
\*Пример 70. Просмотр пользователей в онлайне с выводом времени онлайна
{code}
select cast(U.LOGIN as varchar(128)) as "логин",
cast(H.STREET as varchar(128)) as "Улица",
cast(H.S_NUMBER as varchar(128)) as "№ дома",
cast(AB.A_HOME_NUMBER as varchar(128)) as "№ квартиры",
cast(UF_IP2STRING(U.IP) as varchar(128)) as "IP-адрес",
cast((time '00:00:00' + datediff(second, RS.START_TIME, current_timestamp)) as varchar(128)) as "время в онлайне"
from USERS_RADIUSAUTH UR
left join USERS U on UR.USER_ID = U.ID
left join ABONENTS AB on U.ABONENT_ID = AB.ID
left join HOMES H on AB.HOME_ID = H.ID
left join RADIUS_SESSIONS RS on UR.ACCT_SESSION_ID = RS.ACCT_SESSION_ID
where UR.LOGGED = 1
union
select cast('#ИТОГО ПОЛЬЗОВАТЕЛЕЙ В ОНЛАЙНЕ' as varchar(128)) as "логин",
cast('' as varchar(128)) as "Улица",
cast('' as varchar(128)) as "№ дома",
cast('' as varchar(128)) as "№ квартиры",
cast('' as varchar(128)) as "IP-адрес",
cast(count(UR.LOGGED) as varchar(128)) as "время в онлайне"
from USERS_RADIUSAUTH UR
where UR.LOGGED = 1
order by 1
{code}
*Пример 71.* Абоненты, которые в начале следующего месяца будут заблокированы (при условии что лимит выставлен отричательным числом, в противном случае минус перед ним нужно убрать)
{code}
select distinct A.CONTRACT_NUMBER as "номер договора",
A.NAME as "ФИО",
H.STREET as "Улица",
H.S_NUMBER as "Номер дома",
A.HOME_ENTRANCE as "Номер подъезда",
A.A_HOME_NUMBER as "Номер квартиры",
A.SMS as "номер для смс",
T.NAME as "Тариф"
from ABONENTS A
left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID = T.ID
where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1))
and A.IS_FOLDER = 0
and (select count(1)
from ABONENTS_BLOCK AB
where AB.ABONENT_ID = A.ID
and (AB.B_NEGBAL != 1
or AB.B_OWN != 1
or AB.B_ADMIN != 1
or AB.B_SYS != 1)) = 0
union
select '#ВСЕГО',
'',
'',
'',
'',
'',
'',
count(A.ID)
from ABONENTS A
left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1))
and A.IS_FOLDER = 0
and (select count(1)
from ABONENTS_BLOCK AB
where AB.ABONENT_ID = A.ID
and (AB.B_NEGBAL != 1
or AB.B_OWN != 1
or AB.B_ADMIN != 1
or AB.B_SYS != 1)) = 0
{code}{*}Пример 72. Список IP адрес и номер договора всех абонентов. у которых в качестве nas_ip указан адрес 192.168.1.2*:
{code}
select
uf_ip2string(u.ip) as "IP",
a.CONTRACT_NUMBER as "Номер договора"
from users u
left join abonents a on u.abonent_id=a.id
left join nas n on u.nas_id=n.id
where uf_ip2string(n.ip)='192.168.1.2' and a.deleted=0
{code}{*}Пример 73. Список абонентов, взявших больше одного обещанного платежа:*
{code:lang=sql}select distinct AB.NAME as "ФИО",
AB.CONTRACT_NUMBER as "Номер договора",
(select count(1)
from USERS_USLUGA UUA
left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
where (UUA.USLUGA_ID = 1247
or UUA.USLUGA_ID = 1248
or UUA.USLUGA_ID = 1249)
and UUA.DELETED = 0
and ABA.ID = AB.ID) as "Кол-во обещанных платежей"
from USERS_USLUGA UU
left join ABONENTS AB on UU.ABONENT_ID = AB.ID
where UU.USLUGA_ID = 1247
and UU.DELETED = 0
and (select count(1)
from USERS_USLUGA UUA
left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
where (UUA.USLUGA_ID = 1247
or UUA.USLUGA_ID = 1248
or UUA.USLUGA_ID = 1249)
and UUA.DELETED = 0
and ABA.ID = AB.ID) > 1
order by 3{code}{*}Пример 74. *{color:#000000}{*}Отчет по абонентам со статусом "подключен":*{color}
{code}select
count(distinct ab.id) as "Кол-во абонентов"
from
abonents ab left join abonents_block abb
on ab.id = abb.abonent_id
where
abb.id is null and
ab.deleted = 0 and
ab.is_folder = 0
and exists(select 1 from users where nas_id=70 and abonent_id=ab.id and ip is not null){code}
*Пример 75. Отчет по абонентам со статусом "не подключен":*
{code}select
count(distinct ab.id) as "Кол-во абонентов"
from
abonents ab inner join abonents_block abb
on ab.id = abb.abonent_id
where
ab.deleted = 0 and
ab.is_folder = 0{code}{*}Пример 76. Отчет по абонентам у которых сегодня списалась абонентская плата:*
{code}select
count(distinct ab.id) as "Кол-во абонентов"
from
arch_account_stack aas left join abonents ab
on aas.abonent_id = ab.id
where
credit > 0 and
cast(bill_date as date) = current_date{code}{*}Пример 77. *{color:#000000}{*}Отчет выводящий абонентов должников и в статусе "отключен" по каждому дому в отдельности:*{color}
{code}select CITY,
STREET,
SNUMBER,
AHOMENUMBER,
FIO,
PHONE,
TNAME,
round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from (select H.CITY as "CITY",
H.STREET as "STREET",
H.S_NUMBER as "SNUMBER",
CAST(A.A_HOME_NUMBER AS INTEGER) as "AHOMENUMBER",
A.NAME as "FIO",
A.SMS as "PHONE",
T.NAME as "TNAME",
A.ACCOUNT_ID as "AAID"
from ABONENTS A
left join ABONENTS_BLOCK AB on AB.ABONENT_ID = A.ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID = T.ID
where
A.DELETED != 1
and H.STREET like ':Улица$'
and H.S_NUMBER like ':Дом$'
group by 1, 2, 3, 4, 5, 6, 7, 8)
left join ADMIN_ACCOUNTS AA on AAID = AA.ID
where round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / 10000000000.00, 2) <= :Баланс ниже$
order by 1, 2, 3, 4, 5{code}{*}Пример 78. О{*}{color:#000000}{*}тчет "абоненты с положительным балансом":*{color}
{code}select
a.contract_number as "Номер договора",
a.name "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
inner join admin_accounts aa
on a.account_id = aa.id
left join homes h
on a.home_id = h.id
where
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 > 0
order by (aa.ostatok + aa.debit - aa.credit)/10000000000.00 desc{code}{*}Пример 79. Отчет "Абоненты по услуге":*
{code}select
contract_number as "Номер договора",
name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
uu.enable_date as "Дата подключения",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on aa.id = a.account_id
left join homes h on h.id = a.home_id
inner join users_usluga uu on uu.abonent_id = a.id
where uu.usluga_id = :ID Услуги${code}{*}Пример 80. Отчет "Абоненты по тарифу":*
{code}select
a.contract_number as "Номер договора",
a.name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on aa.id = a.account_id
left join homes h on h.id = a.home_id
where a.tarif_id = :ID Тарифа$
{code}{*}Пример 81. Отчет "Пресса", *{color:#000000}{*}цель отчета раздать списки почтальонам, кому из абонентов приносить газету.*{color}
{code}select
h.s_number as "Дом",
list(a.a_home_number) as "Квартиры"
from
abonents a
left join abonents_block ab
on a.id = ab.abonent_id
inner join homes h
on a.home_id = h.id
where
h.street = ':Улица$'
and
a.is_folder = 0
and
(ab.abonent_id is null
or
(ab.b_negbal = 1
and
(current_timestamp - ab.b_date) < 90))
group by h.s_number{code}
h6.
h6. Пример 82. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам.
h6.
{code}select first 1
(':Начало|date$') as "Период начало",
(':Конец|date$') as "Период окончание",
(
select (sum(aas.credit) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and aas.storno=0
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик')
and ab.company = 0
) as "Абон. плата физ.",
(
select (sum(aas.credit_adjust) /(select CONST_VALUE from VPN_CONST where CONST_ID = 1))from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик' )
and ab.company = 1
and aas.storno=0
) as "Абон. плата юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 0
) as "Приходы физ.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 1
) as "Приходы юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
) as "Итого",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=31
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Подписанные",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=35
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Расторженные"
from send_type{code}
h6. {color:#000000}{*}Пример 83. Отчет по должникам с возможностью выбора тарифа. В отчете: номер договора, ФИО, телефон абонента, адрес, тариф, статус, дата последнего платежа, баланс.*{color}
h6.
h5.
{code}select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
AB.SMS as "Телефон",
TP.NAME as "Тариф",
H.STREET as "Улица",
H.S_NUMBER as "№ дома",
H.S_LITER as "Корпус",
AB.A_HOME_NUMBER as "№ квартиры",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Баланс",
STATUS.NAME as "Статус"
from USERS U
left join ABONENTS AB on U.ABONENT_ID=AB.ID
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join HOMES H on AB.HOME_ID=H.ID
left join TARIF TP on AB.TARIF_ID = TP.ID
left join OBJECTS_STATUS OS on OS.OBJECT_ID=AB.ID
left join STATUS on OS.STATUS=STATUS.ID
where U.OVER_LIMIT_DATE is not null
and TP.ID=':Тариф|select[Tarif]$'
order by U.CONTRACT_NUMBER{code}
h6. Пример 84. Отчет должники по постоплате с разделением по физическим и юридическим лицам. Отчет содержит: номер договора, ФИО, адрес, дата последнего платежа, сумма долга (рекоммендованный платеж по постоплате).
h6.
{code:lang=sql}select first 1
cast('******************************************' as varchar(100) ) as "№ договора",
cast('*****ФИЗИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "Наименование",
cast('******************************************' as varchar(100)) as "Адрес" ,
cast('******************************************' as varchar(100) ) as "Посл. платеж",
cast('*****************************' as varchar(100)) as "Сумма долга"
from send_type
union all
select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
H.STREET || ', д. ' || H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER as "Адрес",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(select round(
sum(
cast(
(CNT.SUMM) as float
)
)
, 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
where cnt.month_number=extract(month FROM cast('NOW' as date))-1
and cnt.year_number=extract(year FROM cast('NOW' as date))
and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0
and AB.COMPANY = 0
and (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0
union all
select first 1
cast('******************************************' as varchar(100) ) as "№ договора",
cast('*****ЮРИДИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "ФИО",
cast('******************************************' as varchar(100)) as "Адрес" ,
cast('******************************************' as varchar(100) ) as "Посл. платеж",
cast('*****************************' as varchar(100)) as "Баланс"
from send_type
union all
select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
H.STREET || ', д. ' || H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER as "Адрес",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(select round(
sum(
cast(
(CNT.SUMM) as float
)
)
, 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
where cnt.month_number=extract(month FROM cast('NOW' as date))-1
and cnt.year_number=extract(year FROM cast('NOW' as date))
and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0
and AB.COMPANY = 1
and (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0{code}
h6. Пример 85. Отчет по абонентам, у которых должна произойти смена тарифа.
h6.
{code:lang=sql}select A.ID as "ID абонента",
A.NAME as "ФИО",
A.TARIF_ID as "ID тарифа",
T.NAME as "Тариф",
A.TARIF_NEXT_ID as "ID нового тарифа"
from ABONENTS A
left join TARIF T on A.TARIF_ID=T.ID
where A.TARIF_NEXT_DATE between ':День смены тарифа|date$' and ':День смены тарифа|date$ 23:59:59 '{code}
h6. Пример 86. Сумма платежей через платежные системы "Жилищное управление", "Yandex" за каждый день в периоде между Датой1 и Датой2.
h6.
{code:lang=sql}with PERIOD as (select
distinct cast(PLS.DATE_CREATE as date) as DAT
from PAY_LOG PLS
where PLS.DATE_CREATE between ':Дата1|date$' and ':Дата2|date$ 23:59:59')
select
PERIOD.DAT as "Date",
(
select COALESCE(sum(P.SUMMA_IN),0)
from PAY_LOG P
where P.MSG_OUT containing 'ACCEPTED'
and P.ACT_IN containing 'PAY'
and P.PAY_OPERATOR containing upper('Жилищное упр')
and P.DATE_CREATE containing DAT
) as "Жилищное упр",
(
select COALESCE(sum(P.SUMMA_IN),0)
from PAY_LOG P
where P.MSG_OUT containing 'ACCEPTED'
and P.ACT_IN containing 'PAY'
and P.PAY_OPERATOR containing upper('Yandex.Kassa')
and P.DATE_CREATE containing DAT
) as "Яндекс"
from
PAY_LOG PL
inner join PERIOD
on PL.DATE_CREATE containing DAT
where PL.MSG_OUT containing 'ACCEPTED'
and PL.ACT_IN containing 'PAY'
group by
PERIOD.DAT{code}
h6. Пример 87. Отчет "Выставленные счета физическим лицам". В отчете: сумма в счете, ФИО абонента, описание финансовой операции.
h6.
{code:lang=sql}select ((fo.op_summa)/(select const_value from vpn_const where const_id=1)) as "Сумма",
ab.name as "ФИО",
ft.op_name || ', ' || ft.op_descr
from finance_operations fo
left join abonents ab on fo.abonent_id=ab.id
left join fin_types ft on fo.op_type=ft.type_id
where (fo.op_type=5 or fo.op_type=9 or fo.op_type=1)
and fo.abonent_id=ab.id
and ab.company=0
and fo.op_date between cast(':Начало|date$' as date) and cast(':Конец|date$' as date){code}
h6. Пример 88. "Отчёт о превышении лимита предоплаченного трафика по юридическим лицам с возможностью выбора месяца - года"
h6. SQL запрос
h6.
{code:lang=sql}select distinct
ab.name,
tarif.name,
usluga.max_mb_in_m,
round(usluga.in_price / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2),
round(traf_counters.v_in / cast((1048576) as numeric(18,5)), 2),
round(traf_counters.v_out / cast((1048576) as numeric(18,5)), 2),
usluga.name,
usluga.id,
tarif.id,
ab.id
from abonents as ab
left join tarif on ab.tarif_id=tarif.id
join tarif_users_usluga as tuu on tuu.tarif_id = tarif.id
join usluga on tuu.usluga_id=usluga.id
join users_usluga as uu on uu.abonent_id = ab.id and uu.usluga_Id = usluga.id and uu.tarif_id = tarif.id
join traf_counters on ab.id=traf_counters.abonent_id
where traf_counters.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and traf_counters.year_number LIKE upper(':Год$') and ab.company = 1{code}
h6. Шаблон отчёта
{code:lang=sql}{% extends "form_list.html" %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}{% load mathfilters %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><h2>Заполните поля запроса</h2></legend>
{{ form }}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<div class="btn-group">
<a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
<i class="icon-print icon-white"></i>
Выгрузка
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><button type="submit" name="csv" value="True">CSV</button></li>
<li><button type="submit" name="dbf" value="True">DBF</button></li>
</ul>
</div>
<button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{inst.name}}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}
{% if data %}
<table id="print" class="mysqldata" border="1" style="text-align: center;">
<tr>
<th>ФИО</th>
<th>Тариф</th>
<th>Услуга</th>
<th>Объем предоплаченного трафика</th>
<th>Цена за МБ</th>
<th>Объем использованного входящего</th>
<th>Объем использованного исходящего</th>
<th>Объем трафика сверх предоплаченного входящего</th>
</tr>
{% for row in data %}
{% if row.2 and row.2 < row.4 %}
<tr>
<td><a href="/admin/Abonents/Abonents/{{ row.9 }}/" target="_blank">{{ row.0 }}</td>
<td><a href="/admin/tarifs/Tarif/{{ row.8 }}/" target="_blank">{{ row.1 }}</a></td>
<td><a href="/admin/tarifs/Usluga/{{ row.7 }}/" target="_blank">{{ row.6 }}</a></td>
<td>{% if row.2 %} {{ row.2|floatformat:2 }} Мб {% else %} --- {% endif %}</td>
<td>{{ row.3 }} руб.</td>
<td>{{ row.4|floatformat:2 }} Мб</td>
<td>{{ row.5|floatformat:2 }} Мб</td>
<td>{{ row.4|sub:row.2 }} Мб</td>
</tr>
{% endif %}
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}{code}
h6. Пример 89. "Отчёт о динамике приходов и актов за заданный период времени"
h6. SQL запрос
h6.
{code:lang=sql}select
fo.SYSTEM_DATE as "Дата",
u.login as "Логин",
a.contract_number as "Номер договора",
a.name as "ФИО",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
round(fo.balance_buh / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс",
a.tarif_id as "ID тарифа",
t.name,
ft.op_name
from abonents as a join users as u on a.id=u.abonent_id
join finance_operations as fo on fo.ABONENT_ID=a.id
join fin_types as ft on fo.op_type = ft.type_id
join tarif as t on t.id = a.tarif_id
where
fo.SYSTEM_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and
fo.op_type in (1,2){code}
h6. Шаблон отчёта
{code:lang=sql}{% extends "form_list.html" %}
{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>
{% endfor %}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}
{% if data %}
<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>ДАТА ПРИХОДА</th>
<th>ЛОГИН</th>
<th>НОМЕР ДОГОВОРА</th>
<th>ФИО</th>
<th>СУММА</th>
<th>ТИП</th>
<th>БАЛАНС</th>
<th>ТАРИФ</th>
</tr>
{% for row in data %}
<tr>
<td class="date">{{ row.0|date:"d.m.Y G:i:s" }}</td>
<td>{{ row.1 }}</td>
<td class="contract">{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td class="fio">{{ row.4 }}</td>
<td>{{ row.8 }}</td>
<td>{{ row.5 }}</td>
<td><a href="/admin/tarifs/Tarif/{{ row.6 }}/" target="_blank">{{ row.7 }}</a></td>
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>
{% endblock %}{code}
h6. Пример 90. "Отчёт по пользователям с добровольной блокировкой"
h6. SQL запрос
h6.
{code:lang=sql}select a.name as "ФИО",
a.CONTRACT_NUMBER as "Номер договора",
uf_ip2string(ip) as "IP",
a.own_disabled_start as "Дата начала",
a.own_disabled_end as "Дата конца"
from abonents as a
left join users as u on a.id=u.abonent_id
join ABONENTS_BLOCK as ab on ab.ABONENT_ID=a.ID
where ab.b_own=1{code}
h6. Пример 91. "Список пользователей с сортировкой по ФИО (ФИО, логин, IP, Город, Улица, Дом)"
{code:lang=sql}
select
A.NAME as FIO,
U.LOGIN as LOGIN,
UF_IP2STRING(U.IP) as IP,
H.city as CITY,
H.street as STREET,
H.s_number as HOME
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
order by A.NAME
{code}
h6. Пример 92. Отчёт в формате "Логин\- Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения. По текущей дате"
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
round(SUM_BYTE_IN_A/cast((1048576) as numeric(18,5)), 2) as "В год",
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "В месяц",
round(SUM_BYTE_IN_D/cast((1048576) as numeric(18,5)), 2) as "В день",
abonents.create_date as "Подключен"
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number=extract(month from current_timestamp)
and tc.year_number=extract(year from current_timestamp)
and tc.currentt=1
{code}
h6. Пример 93. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг".
{code:lang=sql}
select distinct A.CONTRACT_NUMBER as "Договор",
A.NAME as "ФИО",
(H.CITY || ' ' || H.STREET || ' ' || H.S_NUMBER || ' ' || H.S_LITER) as "Дом",
A.SMS as "Телефон",
(AC.OSTATOK + AC.DEBIT - AC.CREDIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Баланс",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
where AB.B_DATE between (':1 Дата|date$') and (':2 Дата|date$')
{code}
h6. Пример 94. Отчёт по должникам по телефонии в формате " № - Папка - ФИО - Телефонный номер - Дата последней оплаты - Сумма долга"
{code:lang=sql}
select
cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as "№",
tmp.*
from (select distinct
(select aa.name from abonents aa where aa.id=a.parent_id) as "Папка",
A.NAME as "ФИО",
PHONE_PULL_CHACHE.Phone as "Телефон",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = A.ID) as "Дата посл.",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join users u on u.abonent_id=A.ID
left join PHONE_PULL_CHACHE on PHONE_PULL_CHACHE.ID=u.PHONE)
as tmp
where rdb$set_context('USER_TRANSACTION', 'row#', coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0)+1) > -1
{code}
h6. Пример 95. Отчёт по скачанному трафику за период.
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
sum(round(tc.SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2)) as "Объем исх.",
sum(round(tc.SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2)) as "Объем вх."
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number between (':1 Месяц$') and (':2 Месяц$')
and tc.year_number=(':Год$')
and tc.currentt=1
group by users.login, tarif.name
{code}
h6. Пример 96. Отчёт по платежам для групп абонентов.
{code:lang=sql}
select a.name as "ФИО",
a.contract_number as "Договор",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and a.parent_id=(':ID папки$')
{code}
h6. Пример 97. Отчёт для платежной системы "Город", выводящий в строку "ФИО;Адрес;Лицевой счет;Текущий остаток;';;;;'" и выводящий в Шапке отчёта :
\#FILESUM - Сумму всех остатков в данном отчёте
\#TYPE 7
\#SERVICE 10240
по всем физ.лицам
{code:lang=sql}
select '#FILESUM' || ' ' || round(sum(aa.ostatok / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round(aa.ostatok / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
{code}
Тот же отчет, выводящий текущий баланс.
{code:lang=sql}
select '#FILESUM' || ' ' || round(sum((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
and parent_id not in ( '244', '2', '4' , '1499')
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
{code}
h6. Пример 98. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number)
{code:lang=sql}
{% extends "form_list.html" %}
{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>
{% endfor %}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}
{% if data %}
<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>№</th>
{% for field in field_desc %}
<th>{{ field|get_zero }}</th>
{% endfor %}
</tr>
{% for row in data %}
<tr>
<td>{{ forloop.counter }}</td>
{% for cell in row %}
<td>{{ cell }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>
{% endblock %}
{code}
h6. Пример 99. Отчёт по определенной услуге. Абонент, логин, баланс, конец действия услуги.
{code:lang=sql}
select USERS.LOGIN as "Логин",
ABONENTS.name as "ФИО",
(ADMIN_ACCOUNTS.OSTATOK + ADMIN_ACCOUNTS.DEBIT - ADMIN_ACCOUNTS.CREDIT)/10000000000 as "Баланс",
USERS_USLUGA.END_TIME as "Конец"
from USERS_USLUGA left join ABONENTS on ABONENTS.ID = USERS_USLUGA.ABONENT_ID
left join ADMIN_ACCOUNTS on ABONENTS.ACCOUNT_ID = ADMIN_ACCOUNTS.ID
left join USERS on ABONENTS.ID = USERS.ABONENT_ID
where USERS_USLUGA.USLUGA_ID = (':ID нужной услуги$')
and USERS_USLUGA.DELETED != 1
order by ABONENTS.NAME
{code}
h6. Пример 100. Отчёт по абонентам в формате "ФИО ЛОГИН IP MAC ТЕЛЕФОН АДРЕС ТАРИФ БАЛАНС"
{code:lang=sql}
select distinct
A.NAME as "ФИО",
U.LOGIN as "Логин",
UF_IP2STRING(U.IP) as "IP",
U.MAC as "MAC",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
T.name as "Тариф",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0
order by A.NAME
{code}
h6. Пример 101. Отчёт, выводящий сумму списаний, взятых из выставленных актов, за определенный период по всем абонентам.
{code:lang=sql}
select sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма "
from finance_operations
where op_type=1
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
{code}
h6. Пример 102. Отчет для вывода рекомендуемого ежемесячно платежа, стоимости тарифа по всем абонентам.
{code}
select
a.CONTRACT_NUMBER as "№",
a.NAME as "ФИО",
t.NAME as "ТАРИФ",
round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "АБОН.ПЛАТА",
round(ac.RECOMEND_PAY_SUM_CACHE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "РЕКОМЕНД.",
st.name as "СТАТУС",
subst.name as "ПОДСТАТУС",
cast(obs.apply_date as date) as "ДАТА"
from ABONENTS a
LEFT JOIN ABONENTS_CACHE ac ON ac.ID=a.ID
LEFT JOIN TARIF t ON a.TARIF_ID=t.ID
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN STATUS subst on subst.id = obs.SUBSTATUS
where obs.OBJECT_NAME='Abonents'
and a.is_folder = 0
and a.parent_id != 244
and a.parent_id != 2
{code}
h6. Пример 103. Отчёт по платежам через веб - кассу(По операторам)
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$') and AU.USERNAME = ':Администратор$'
{code}
h6. Пример 104. Поле "Не отключать при превышении порога", ID, ФИО.
{code}
select
a.ID as "ID",
a.NAME as "ФИО",
aa.UNLIMITED as "Не отключать"
from ABONENTS a
left join ADMIN_ACCOUNTS aa on a.ACCOUNT_ID=aa.ID
where a.IS_FOLDER=0
order by a.NAME
{code}
h6. Пример 105. Учёт заключенных договоров за период
{code}
select
a.CREATE_DATE as "Дата договора",
a.CONTRACT_NUMBER as "№ договора",
t.NAME as "Тариф",
st.NAME as "Cтатус",
a.NAME as "ФИО абонента",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
a.ACTIVATE_DATE as "Дата активации",
n.NAME as "NAS"
from ABONENTS a
LEFT JOIN TARIF t on a.TARIF_ID=t.ID
LEFT JOIN USERS u on u.ABONENT_ID=a.ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN NAS n on u.nas_id=n.ID
left join HOMES h on a.HOME_ID=h.ID
where a.IS_FOLDER=0
and a.CREATE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
{code}{*}Пример 106. Телефония. Количество звонков, ожидающих перерасчет.*
{code}
select count(1) from voip_log where recalc=1
{code}
h6. Пример 107. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)"
{code}
select NAME,
UF_IP2STRING(START_IP) as "Начальный IP",
UF_IP2STRING(END_IP) as "Конечный IP",
(select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0) as "Занято",
(END_IP - START_IP - (select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0)) as "Свободно"
from IP_PULL
order by NAME
{code}
h6. Пример 108. Отчёт по приходам за заданный период времени
{code}
select a.name as "ФИО",
a.contract_number as "Договор",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
AU.USERNAME as "Ответственный",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
on a.id=fo.abonent_id
left join AUTH_USER as AU
on AU.id=fo.owner_id
where OP_TYPE=2 and
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
{code}
h6. Пример 109. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%"
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.DESCR as "Описание",
round(fo.aa_ostatok/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where 1=1 and (UPPER(FO.DESCR) LIKE UPPER('%Альфа%'))
and (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$')
{code}
h6. Пример 110. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период.
{code}
select distinct u.name as "Услуга",
round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена",
round(sum(counters.SUMM), 2) as "Сумма"
from counters
left join usluga u on u.id=counters.usluga_id
where S_DATE between (':1 Дата|date$') and (':2 Дата|date$')
group by u.name, u.SUMMA
{code}
h6. Пример 111. Сформированные акты/счета по юридическим лицам.
{code}
select AB.NAME as "ФИО",
CONTRACT_NUMBER as "Договор",
FO.NUMBER as "Номер акта",
round(FO.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "СУММА",
FT.OP_NAME || ', ' || FT.OP_DESCR as "ТИП ДОКУМЕНТА"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
left join FIN_TYPES FT on FO.OP_TYPE = FT.TYPE_ID
where (FO.OP_TYPE = 5
or FO.OP_TYPE = 9
or FO.OP_TYPE = 1)
and FO.ABONENT_ID = AB.ID
and AB.COMPANY = 1
and FO.OP_DATE between cast(':1. Начало|date$' as date) and cast(':2. Конец|date$' as date)
order by 1
{code}
h6. Пример 112. Отчёт по юридическим лицам, выводящий ФИО Телефон Адрес Р/С ИНН КПП
{code}
select A.NAME as "ФИО",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
av1.attribute_value as "Р/С",
av2.attribute_value as "ИНН",
av3.attribute_value as "КПП"
from abonents as a
left join HOMES as H on H.ID=A.HOME_ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES AV2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES AV3 on a.id=av3.ABONENT_ID and av3.ATTRIBUTE_ID=5
where a.is_folder=0
and a.company=1
{code}
h6. Пример 113. Отчёт прибыль по плате за подключение нескольких услуг.
{code}
select
a.NAME as "ФИО",
uu.ABONENT_ID as "ID АБОНЕНТА",
cast(uu.CREATE_DATE as date) as "ДАТА АКТИВАЦИИ",
c.SUMM as "Прибыль"
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0
union all
SELECT
cast('' as varchar(32)),
cast('' as varchar(32)),
cast('Итого' as varchar(32)),
ROUND (sum(c.summ))
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0
{code}
h6. Пример 114. Отчёт выводит абонентов, последнее подключение которых было раньше, чем указанная дата.
{code}
select distinct a.name as "ФИО",
b.max_time as "Последнее подключ." from abonents as a
inner join (select rs.abonent_id, max(rs.start_time) as max_time from radius_sessions rs group by abonent_id) as b on a.id=b.abonent_id where max_time<(':1 Дата|date$')
{code}
h6. Пример 115. Отчёт выводит информацию по абонентам с определенным NAS'ом.
{code}
select distinct
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
u.login,
u.GEN_PWD,
u.auth_type,
uf_ip2string(s.ip) as "switch_ip",
abs.RATE_IN,
abs.RATE_OUT,
abs.CEIL_IN,
abs.CEIL_OUT,
uf_ip2string(u.ip) as "user_ip",
u.ROUTER_VLAN,
u.GPON_MODEM_PORT,
u.ROUTER_PORT
from users as u
left join abonents as a on a.id=u.abonent_id
left join admin_accounts as aa on aa.id=a.ACCOUNT_ID
left join switch as s on s.id=u.switch_id
left join ABONENTS_SPEED as ABS on abs.abonent_id=a.id
left join NAS on nas.id=u.nas_id
where a.is_folder=0
and a.deleted=0
and uf_ip2string(nas.ip)=(':nas_ip$')
{code}
h6. Пример 116. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус.
{code}
select
a.contract_number as "Договор",
a.name as "Имя",
('г. ' || h.city || ' ' || h.street || ', д.' || h.s_number || ', кв.' || a.a_home_number) as "Адрес",
(select av.attribute_value from attribute_values av where av.attribute_id = 1 and av.abonent_id = a.id) as "Телефон",
cast(round((cast(aa.ostatok as float)+cast(aa.debit as float)-cast(aa.credit as float))/10000000000,2) as numeric(18,2)) as "Баланс",
(select st.name from status st where st.id = os.status) as "Статус"
from
abonents a
left join
homes h on h.id = a.home_id
join
admin_accounts aa on aa.id = a.account_id
join
objects_status os on a.id = os.object_id
where
a.is_folder = 0
{code}
h6. Пример 117. Поиск IP-адреса в истории изменения учетных записей (история выдачи ip-адреса)
{code}
select
uf_ip2string(ip) as IP,abonent_id,time_changed,a.name
from
users_history uh
join
abonents a
on uh.abonent_id = a.id
where
uf_ip2string(ip) = ':Введите ip-адрес$'
{code}
h6. Пример 118. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс"
{code}
select distinct
A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
USL.NAME as "Имя",
UU.ACTIVATED as "Активна",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join USERS_USLUGA UU on UU.abonent_id=A.id
left join USLUGA USL on USL.ID=UU.USLUGA_ID
where A.IS_FOLDER=0
AND A.DELETED=0
order by A.NAME
{code}
Есть готовые отчеты, также можно создать свой отчет.
Отличительная особенность отчета состоит в том, что можно указывать параметры. Если в настройках указать параметр (prm), то при нажатии запустить отчет, можно будет задать параметр. Задать название отчета и текст запроса, который вы можете уточнить у тех.поддержки (должен начинаться со слова "select").
!жур2.JPG|border=1!
Данные в отчете можно выгрузить в csv.
!жур3.JPG|border=1!
h3. Параметры полей формы
Переменные, отображаемые в виде формы:
{code:lang=sql}':title|type[params]$'{code}
* : - указывает, что это строка с переменной
* title - заголовок, отображаемый на форме
* \| - разделитель заголовка и типа
* type\[params\] - тип с параметрами:
** date - дата, без параметров, отображает на форме календарь выбора даты
{code}BILL_DATE between ':Начало|date$' and ':Конец|date$'{code}
** choices - список
{code:lang=sql}Homes.City = ':Город|choices[Москва^]Москва^[Волгоград^]Волгоград]$'
Abonents.Id = ':Абонент|choices[35^]Михаил^[40^]Володя]$'{code}
** select - список, построенный на основе модели, подобно работе [API|CarbonBilling:API REST v2.0], параметры содержат модель (обязательно) и фильтры
{code}a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
h.id = ':Заявка|select[HDSK]$'{code}
* $ - флаг отмечает обязательную переменную
h2. {color:#000000}{*}Примеры:*{color}
*Пример 1.* *Список всех доступных телефонов абонентов в формате (порядковый номер, логин абонента, номер телефона)*
{code}
select id, login, SMS from users where DELETED=0 and sms is not null
{code}
*Пример 2.* *Список ФИО абонентов и их баланс на текущий момент*
{code}
select
ab.name as "Ф.И.О.",
(ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс"
from abonents ab
left join admin_accounts ac on ab.account_id = ac.id
left join users u on u.abonent_id = ab.id
where
ab.deleted<>1
and ab.is_folder<>1
and u.id is not null
order by ab.name
{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
ab.name as "FIO",
uf_ip2string(u.ip) as "IP",
uf_ip2string(u.nas_ip) as "NAS",
(ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс"
from users u
left join abonents ab on u.abonent_id = ab.id
left join admin_accounts ac on ab.account_id = ac.id
where
ab.deleted<>1
and ab.is_folder<>1
and u.id is not null
order by ab.name{code}
*Пример 22.*{*}Список (*{*}Ф.И.О. , IP, NAS, баланс) с сортировкой фамилий по ip*
{code}select
select
ab.name as "FIO",
uf_ip2string(u.ip) as "IP",
uf_ip2string(u.nas_ip) as "NAS",
(ac.ostatok+ac.debit-ac.credit)/(select const_value from vpn_const where const_id=1) as "Баланс"
from users u
left join abonents ab on u.abonent_id = ab.id
left join admin_accounts ac on ab.account_id = ac.id
where
ab.deleted<>1
and ab.is_folder<>1
and u.id is not null
order by u.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)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID
where PL.MSG_OUT = 'ACCEPTED' and PL.OPERATOR_DATE_IN >= '2013.01.01'
group by U.ID, U.CONTRACT_NUMBER, U.IDENTIFY
{code}
*Пример 28.* *Список оплат через платежные системы по группам без учета 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.* *Список должников (ФИО, дата последнего платежа, Баланс, Тариф{*}*, Адрес,*{*}Телефон)*
{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}
*Пример 31.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период c 2013.02.17 по 2013.02.18*
{code}
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')
{code}
*Пример 32.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за текущий день*
{code}
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)
{code}
*Пример 33.* *Список оплат через менеджер (ФИО, логин, дата платежа, сумма, Адрес, кто оплатил) за период с 2012-09-20 по 2013-03-17 *
{code}
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')
{code}
*Пример 34. Список логинов и паролей абонентов*
{code}
select id, identify, login, gen_pwd AS "password" from users where DELETED=0 and end_user=1
{code}
*Пример 35. Список пользователей, у которых активированы дополнительные услуги за все время работы (ФИО, название услуги, дата активации)*
{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}
*Пример 36. Список пользователей, у которых активированы дополнительные услуги за период с 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}
*Пример 37. Список пользователей, у которых активированы дополнительные услуги на данный момент(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}
*Пример 38. Отчет по сумме оплат за каждый день, внесенных через менеджер за период с 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}
*Пример 39. Изменить у абонентов тип авторизации с 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}
*Пример 40. Получить сумму по 2 услугам за период по пользователю услуга 10 и 11*
{code}
SELECT SUM(session_credit) FROM sessions WHERE (time_in between '2013-03-01' and '2013-04-01') AND id = 2564 AND (ruleset_no = -10 OR ruleset_no = -11)
{code}
*Пример 41. Список должников с последней оплатой (ФИО,Логин,Договор,Дата последнего платежа,Сумма последнего платежа,Текущий баланс)*
Запрос:
{code}
select U.identify,U.login,U.contract_number,RPL.OP_DATE,RPL.OP_SUMMA,RPL.BALANCE_BUH from users U
join REP_FINOPER_LAST(2) RPL on RPL.user_id=u.id
{code}
Предварительно Через [ibexpert|http://docs.carbonsoft.ru/pages/viewpage.action?pageId=3440679] или sqlexec сделать процедуру
{code}
SET TERM ^ ;
create procedure REP_FINOPER_LAST1 (
OP_TYPE_IN integer)
returns (
OP_ID integer,
USER_ID integer,
OWNER_ID integer,
OP_TYPE integer,
OP_DATE timestamp,
OP_SUMMA numeric(18,0),
OP_CARD_ID integer,
SYSTEM_DATE timestamp,
NUMBER varchar(10),
BALANCE_BUH numeric(18,0),
DESCR varchar(255),
FINANCE integer,
ISBUHDOC integer,
END_USER integer,
OPERATOR_PAY_ID integer,
OPERATOR_NAME varchar(32),
OPERATOR_RESULT integer,
OPERATOR_PAY_ID_STR varchar(32),
SYNC1C integer,
CODE1C varchar(32),
CURS double precision,
SUM_IN numeric(18,0))
as
declare variable TMP_OP_ID integer;
begin
for select max(OP_ID)
from FINANCE_OPERATIONS
where OP_TYPE = :OP_TYPE_IN
group by USER_ID
into TMP_OP_ID
do
begin
select first 1 OP_ID,
USER_ID,
OWNER_ID,
OP_TYPE,
OP_DATE,
OP_SUMMA,
OP_CARD_ID,
SYSTEM_DATE,
NUMBER,
BALANCE_BUH,
DESCR,
FINANCE,
ISBUHDOC,
END_USER,
OPERATOR_PAY_ID,
OPERATOR_NAME,
OPERATOR_RESULT,
OPERATOR_PAY_ID_STR,
SYNC1C,
CODE1C,
CURS,
SUM_IN
from FINANCE_OPERATIONS
where OP_ID = :TMP_OP_ID
order by OP_ID desc
into :OP_ID,
:USER_ID,
:OWNER_ID,
:OP_TYPE,
:OP_DATE,
:OP_SUMMA,
:OP_CARD_ID,
:SYSTEM_DATE,
:NUMBER,
:BALANCE_BUH,
:DESCR,
:FINANCE,
:ISBUHDOC,
:END_USER,
:OPERATOR_PAY_ID,
:OPERATOR_NAME,
:OPERATOR_RESULT,
:OPERATOR_PAY_ID_STR,
:SYNC1C,
:CODE1C,
:CURS,
:SUM_IN;
suspend;
end
end^
SET TERM ; ^
/* Following GRANT statetements are generated automatically */
GRANT SELECT ON FINANCE_OPERATIONS TO PROCEDURE REP_FINOPER_LAST;
/* Existing privileges on this procedure */
GRANT EXECUTE ON PROCEDURE REP_FINOPER_LAST TO SYSDBA;
{code}
*Пример 42. Список абонентов в формате (Номер, Логин, Тарифный план, ФИО, Остаток на начало периода, Платеж через кассира, Расход, Расход по трафику, Остаток на конец периода) за период с 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}
*Пример 43. Список абонентов в формате (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}
*Пример 44. Список должников (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 45. Список абонентов (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 46. Список новых абонентов за период 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}
*Пример 47. Список удаленных абонентов (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 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(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}
*Пример 49. Список отключенных абонентов, не плативших с 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}
*Пример 50. Список суммы должников (№ договора, Ф.И.О, Адрес, Телефон, 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}
*Пример 51. Кол-во абонентов на каждом тарифе (Тариф, количество абонентов) сортировка по названию тарифа*
{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}
*Пример 52. Кол-во абонентов на каждой услуге (Тариф, количество абонентов) сортировка по названию услуги*
{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 "Количество абонентоd"
from usluga us order by 1
{code}
*Пример 53. Сумма внесенных средств за указанный период(№ договора, Ф.И.О, Адрес, телефон, 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 '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
{code}
*Пример 54. Поиск абонента по 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}
*Пример 55. Список должников(ФИО, 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}
*Пример 56. Список сессий по абоненту , пример по абоненту 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}
*Пример 57. Список абонентов, подключенных к NAS с ip 172.16.0.6 в формате id, фио, номер телефона*
{code}
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
{code}
*Пример 58. Список абонентов, оплативших через платежную систему "Qiwiwallet" в формате логин, ФИО, сумма, дата*
{code}
select LOGIN_IN as LOGIN,
USER_NAME_OUT as FIO,
SUMMA_IN as SUMMA,
OPERATOR_DATE_IN as DATA
from PAY_LOG
where MSG_OUT='ACCEPTED' and ACT_IN='PAY' and PAY_OPERATOR='Qiwiwallet'
order by USER_NAME_OUT
{code}
*Пример 59. Список привязки абонентов к порту коммутатора (ФИО, Имя коммутатора, 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}
*Пример 60. Список абонентов, не плативших в течение 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
order by U.IDENTIFY
{code}
*Пример 61. Общее количество абонентов по группам*
{code}
select
name as "Группа",
(select count(1) from abonents where parent_id=grp.id) as "Количество абонентов"
from abonents grp
where is_folder=1
{code}
*Пример 62. Средняя выручка(в расчёте на одного абонента) за предыдущий и текущий месяц или с возможностью выбора периода*
{code}
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
{code}
*Пример 63. Информация об абонентах с реквизитами и скоростями(CEIL_IN) - Номер договора, Имя абонента, Название тарифа, Паспорт серия, Паспорт номер, Кем выдан, Когда выдан, адрес, номер телефона (сортировка по имени абонента)*
{code}
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
{code}
*Пример 64. Отчет по списаниям по абонентам в формате (число, id группы, название группы, сумма и кол-во абонентов, по которым были списания) и последней строкой сумма за день.*
{code}
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
{code}
*Пример 65. Отчет по должникам для отключения КТВ (Улица, Дом, Подъезд, Квартира, ФИО, Тариф) игнорируя ручной статус "Отключен"*
{code}
select
h.street as "Улица",
h.s_number as "Номер дома",
a.home_entrance as "Номер подъезда",
a.A_HOME_NUMBER as "Номер квартиры",
a.name as "ФИО",
t.name as "Тариф"
from abonents_block ab
left join abonents a on ab.abonent_id=a.id
left join homes h on a.home_id=h.id
left join tarif t on a.tarif_id=t.id
left join objects_status os on a.id=os.object_id
where ab.b_negbal=1 and a.tarif_id in (21,22,23,24) and (os.status<>5 or os.status is null)
group by 1,2,3,4,5,6
order by 6
{code}
*Пример 66. Отчет по свободным IP адресам*
{code}
select uf_ip2string(pc.ip) as "IP адрес",
ipp.name as "Pull",
(case when ipp.enabled <> 0 THEN 'Да' ELSE 'Нет' END) as "Включен"
from pull_cache pc
left join ip_pull ipp on ipp.pull_id = pc.pull_id
where
pc.user_id is null
order by pc.pull_id, pc.ip
{code}
*Пример 67. Кто платит через Юнителлер?*
{code}
select distinct(pay_id_str_in) as "PAY",
CONTRACT_NUMBER_IN as "Договор",
USER_NAME_OUT as "ФИО",
operator_date_in as "Дата",
SUMMA_IN as "Сумма"
from pay_log
where (operator_src_ip='213.208.182.172' or pay_operator='Uniteller')
and act_in='pay'
and operator_date_in between ':C_даты|date$' and ':По_дату|date$'
order by user_name_out
{code}
*Пример 68. Количество услуг из ЛК за месяц по абонентам*
{code}
select A.ID,
A.CONTRACT_NUMBER,
A.NAME,
count(*)
from USERS_USLUGA UU
left join ABONENTS A on UU.ABONENT_ID = A.ID
where UU.USLUGA_ID = ':ID услуги$' and UU.IS_ZAKAZANO = 1 and UU.CREATE_DATE between ':C_даты|date$' and ':По_дату|date$'
group by A.ID, A.CONTRACT_NUMBER, A.NAME
having count(*)>1
order by A.NAMEe_in between ':C_даты|date$' and ':По_дату|date$'
{code}
\*Пример 69. Прибыль с комиссии за обещанный платеж (все периоды)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.MONTH_NUMBER as "номер месяца",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
union
select '#ИТОГО',
'',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
order by 1
{code}\*Пример 69. Прибыль с комиссии за обещанный платеж (по периодам)
{code}
select A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
order by 1
{code}
\*Пример 70. Просмотр пользователей в онлайне с выводом времени онлайна
{code}
select cast(U.LOGIN as varchar(128)) as "логин",
cast(H.STREET as varchar(128)) as "Улица",
cast(H.S_NUMBER as varchar(128)) as "№ дома",
cast(AB.A_HOME_NUMBER as varchar(128)) as "№ квартиры",
cast(UF_IP2STRING(U.IP) as varchar(128)) as "IP-адрес",
cast((time '00:00:00' + datediff(second, RS.START_TIME, current_timestamp)) as varchar(128)) as "время в онлайне"
from USERS_RADIUSAUTH UR
left join USERS U on UR.USER_ID = U.ID
left join ABONENTS AB on U.ABONENT_ID = AB.ID
left join HOMES H on AB.HOME_ID = H.ID
left join RADIUS_SESSIONS RS on UR.ACCT_SESSION_ID = RS.ACCT_SESSION_ID
where UR.LOGGED = 1
union
select cast('#ИТОГО ПОЛЬЗОВАТЕЛЕЙ В ОНЛАЙНЕ' as varchar(128)) as "логин",
cast('' as varchar(128)) as "Улица",
cast('' as varchar(128)) as "№ дома",
cast('' as varchar(128)) as "№ квартиры",
cast('' as varchar(128)) as "IP-адрес",
cast(count(UR.LOGGED) as varchar(128)) as "время в онлайне"
from USERS_RADIUSAUTH UR
where UR.LOGGED = 1
order by 1
{code}
*Пример 71.* Абоненты, которые в начале следующего месяца будут заблокированы (при условии что лимит выставлен отричательным числом, в противном случае минус перед ним нужно убрать)
{code}
select distinct A.CONTRACT_NUMBER as "номер договора",
A.NAME as "ФИО",
H.STREET as "Улица",
H.S_NUMBER as "Номер дома",
A.HOME_ENTRANCE as "Номер подъезда",
A.A_HOME_NUMBER as "Номер квартиры",
A.SMS as "номер для смс",
T.NAME as "Тариф"
from ABONENTS A
left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID = T.ID
where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1))
and A.IS_FOLDER = 0
and (select count(1)
from ABONENTS_BLOCK AB
where AB.ABONENT_ID = A.ID
and (AB.B_NEGBAL != 1
or AB.B_OWN != 1
or AB.B_ADMIN != 1
or AB.B_SYS != 1)) = 0
union
select '#ВСЕГО',
'',
'',
'',
'',
'',
'',
count(A.ID)
from ABONENTS A
left join ABONENTS_CACHE ACCH on A.ID = ACCH.ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
where (ACCH.RECOMEND_PAY_SUM_CACHE / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1)) > (-(AC.LIMIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1))
and A.IS_FOLDER = 0
and (select count(1)
from ABONENTS_BLOCK AB
where AB.ABONENT_ID = A.ID
and (AB.B_NEGBAL != 1
or AB.B_OWN != 1
or AB.B_ADMIN != 1
or AB.B_SYS != 1)) = 0
{code}{*}Пример 72. Список IP адрес и номер договора всех абонентов. у которых в качестве nas_ip указан адрес 192.168.1.2*:
{code}
select
uf_ip2string(u.ip) as "IP",
a.CONTRACT_NUMBER as "Номер договора"
from users u
left join abonents a on u.abonent_id=a.id
left join nas n on u.nas_id=n.id
where uf_ip2string(n.ip)='192.168.1.2' and a.deleted=0
{code}{*}Пример 73. Список абонентов, взявших больше одного обещанного платежа:*
{code:lang=sql}select distinct AB.NAME as "ФИО",
AB.CONTRACT_NUMBER as "Номер договора",
(select count(1)
from USERS_USLUGA UUA
left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
where (UUA.USLUGA_ID = 1247
or UUA.USLUGA_ID = 1248
or UUA.USLUGA_ID = 1249)
and UUA.DELETED = 0
and ABA.ID = AB.ID) as "Кол-во обещанных платежей"
from USERS_USLUGA UU
left join ABONENTS AB on UU.ABONENT_ID = AB.ID
where UU.USLUGA_ID = 1247
and UU.DELETED = 0
and (select count(1)
from USERS_USLUGA UUA
left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
where (UUA.USLUGA_ID = 1247
or UUA.USLUGA_ID = 1248
or UUA.USLUGA_ID = 1249)
and UUA.DELETED = 0
and ABA.ID = AB.ID) > 1
order by 3{code}{*}Пример 74. *{color:#000000}{*}Отчет по абонентам со статусом "подключен":*{color}
{code}select
count(distinct ab.id) as "Кол-во абонентов"
from
abonents ab left join abonents_block abb
on ab.id = abb.abonent_id
where
abb.id is null and
ab.deleted = 0 and
ab.is_folder = 0
and exists(select 1 from users where nas_id=70 and abonent_id=ab.id and ip is not null){code}
*Пример 75. Отчет по абонентам со статусом "не подключен":*
{code}select
count(distinct ab.id) as "Кол-во абонентов"
from
abonents ab inner join abonents_block abb
on ab.id = abb.abonent_id
where
ab.deleted = 0 and
ab.is_folder = 0{code}{*}Пример 76. Отчет по абонентам у которых сегодня списалась абонентская плата:*
{code}select
count(distinct ab.id) as "Кол-во абонентов"
from
arch_account_stack aas left join abonents ab
on aas.abonent_id = ab.id
where
credit > 0 and
cast(bill_date as date) = current_date{code}{*}Пример 77. *{color:#000000}{*}Отчет выводящий абонентов должников и в статусе "отключен" по каждому дому в отдельности:*{color}
{code}select CITY,
STREET,
SNUMBER,
AHOMENUMBER,
FIO,
PHONE,
TNAME,
round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from (select H.CITY as "CITY",
H.STREET as "STREET",
H.S_NUMBER as "SNUMBER",
CAST(A.A_HOME_NUMBER AS INTEGER) as "AHOMENUMBER",
A.NAME as "FIO",
A.SMS as "PHONE",
T.NAME as "TNAME",
A.ACCOUNT_ID as "AAID"
from ABONENTS A
left join ABONENTS_BLOCK AB on AB.ABONENT_ID = A.ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID = T.ID
where
A.DELETED != 1
and H.STREET like ':Улица$'
and H.S_NUMBER like ':Дом$'
group by 1, 2, 3, 4, 5, 6, 7, 8)
left join ADMIN_ACCOUNTS AA on AAID = AA.ID
where round((AA.OSTATOK + AA.DEBIT - AA.CREDIT) / 10000000000.00, 2) <= :Баланс ниже$
order by 1, 2, 3, 4, 5{code}{*}Пример 78. О{*}{color:#000000}{*}тчет "абоненты с положительным балансом":*{color}
{code}select
a.contract_number as "Номер договора",
a.name "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
inner join admin_accounts aa
on a.account_id = aa.id
left join homes h
on a.home_id = h.id
where
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 > 0
order by (aa.ostatok + aa.debit - aa.credit)/10000000000.00 desc{code}{*}Пример 79. Отчет "Абоненты по услуге":*
{code}select
contract_number as "Номер договора",
name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
uu.enable_date as "Дата подключения",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on aa.id = a.account_id
left join homes h on h.id = a.home_id
inner join users_usluga uu on uu.abonent_id = a.id
where uu.usluga_id = :ID Услуги${code}{*}Пример 80. Отчет "Абоненты по тарифу":*
{code}select
a.contract_number as "Номер договора",
a.name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on aa.id = a.account_id
left join homes h on h.id = a.home_id
where a.tarif_id = :ID Тарифа$
{code}{*}Пример 81. Отчет "Пресса", *{color:#000000}{*}цель отчета раздать списки почтальонам, кому из абонентов приносить газету.*{color}
{code}select
h.s_number as "Дом",
list(a.a_home_number) as "Квартиры"
from
abonents a
left join abonents_block ab
on a.id = ab.abonent_id
inner join homes h
on a.home_id = h.id
where
h.street = ':Улица$'
and
a.is_folder = 0
and
(ab.abonent_id is null
or
(ab.b_negbal = 1
and
(current_timestamp - ab.b_date) < 90))
group by h.s_number{code}
h6.
h6. Пример 82. Отчет "Интернет", отчет за период по абонентской плате, по приходам физических и юридических лиц, по приходам через платежные системы, расторгнутым и заключенным договорам.
h6.
{code}select first 1
(':Начало|date$') as "Период начало",
(':Конец|date$') as "Период окончание",
(
select (sum(aas.credit) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and aas.storno=0
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик')
and ab.company = 0
) as "Абон. плата физ.",
(
select (sum(aas.credit_adjust) /(select CONST_VALUE from VPN_CONST where CONST_ID = 1))from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик' )
and ab.company = 1
and aas.storno=0
) as "Абон. плата юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 0
) as "Приходы физ.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 1
) as "Приходы юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
) as "Итого",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=31
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Подписанные",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=35
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Расторженные"
from send_type{code}
h6. {color:#000000}{*}Пример 83. Отчет по должникам с возможностью выбора тарифа. В отчете: номер договора, ФИО, телефон абонента, адрес, тариф, статус, дата последнего платежа, баланс.*{color}
h6.
h5.
{code}select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
AB.SMS as "Телефон",
TP.NAME as "Тариф",
H.STREET as "Улица",
H.S_NUMBER as "№ дома",
H.S_LITER as "Корпус",
AB.A_HOME_NUMBER as "№ квартиры",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Баланс",
STATUS.NAME as "Статус"
from USERS U
left join ABONENTS AB on U.ABONENT_ID=AB.ID
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join HOMES H on AB.HOME_ID=H.ID
left join TARIF TP on AB.TARIF_ID = TP.ID
left join OBJECTS_STATUS OS on OS.OBJECT_ID=AB.ID
left join STATUS on OS.STATUS=STATUS.ID
where U.OVER_LIMIT_DATE is not null
and TP.ID=':Тариф|select[Tarif]$'
order by U.CONTRACT_NUMBER{code}
h6. Пример 84. Отчет должники по постоплате с разделением по физическим и юридическим лицам. Отчет содержит: номер договора, ФИО, адрес, дата последнего платежа, сумма долга (рекоммендованный платеж по постоплате).
h6.
{code:lang=sql}select first 1
cast('******************************************' as varchar(100) ) as "№ договора",
cast('*****ФИЗИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "Наименование",
cast('******************************************' as varchar(100)) as "Адрес" ,
cast('******************************************' as varchar(100) ) as "Посл. платеж",
cast('*****************************' as varchar(100)) as "Сумма долга"
from send_type
union all
select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
H.STREET || ', д. ' || H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER as "Адрес",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(select round(
sum(
cast(
(CNT.SUMM) as float
)
)
, 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
where cnt.month_number=extract(month FROM cast('NOW' as date))-1
and cnt.year_number=extract(year FROM cast('NOW' as date))
and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0
and AB.COMPANY = 0
and (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0
union all
select first 1
cast('******************************************' as varchar(100) ) as "№ договора",
cast('*****ЮРИДИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "ФИО",
cast('******************************************' as varchar(100)) as "Адрес" ,
cast('******************************************' as varchar(100) ) as "Посл. платеж",
cast('*****************************' as varchar(100)) as "Баланс"
from send_type
union all
select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
H.STREET || ', д. ' || H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER as "Адрес",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(select round(
sum(
cast(
(CNT.SUMM) as float
)
)
, 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
where cnt.month_number=extract(month FROM cast('NOW' as date))-1
and cnt.year_number=extract(year FROM cast('NOW' as date))
and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0
and AB.COMPANY = 1
and (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0{code}
h6. Пример 85. Отчет по абонентам, у которых должна произойти смена тарифа.
h6.
{code:lang=sql}select A.ID as "ID абонента",
A.NAME as "ФИО",
A.TARIF_ID as "ID тарифа",
T.NAME as "Тариф",
A.TARIF_NEXT_ID as "ID нового тарифа"
from ABONENTS A
left join TARIF T on A.TARIF_ID=T.ID
where A.TARIF_NEXT_DATE between ':День смены тарифа|date$' and ':День смены тарифа|date$ 23:59:59 '{code}
h6. Пример 86. Сумма платежей через платежные системы "Жилищное управление", "Yandex" за каждый день в периоде между Датой1 и Датой2.
h6.
{code:lang=sql}with PERIOD as (select
distinct cast(PLS.DATE_CREATE as date) as DAT
from PAY_LOG PLS
where PLS.DATE_CREATE between ':Дата1|date$' and ':Дата2|date$ 23:59:59')
select
PERIOD.DAT as "Date",
(
select COALESCE(sum(P.SUMMA_IN),0)
from PAY_LOG P
where P.MSG_OUT containing 'ACCEPTED'
and P.ACT_IN containing 'PAY'
and P.PAY_OPERATOR containing upper('Жилищное упр')
and P.DATE_CREATE containing DAT
) as "Жилищное упр",
(
select COALESCE(sum(P.SUMMA_IN),0)
from PAY_LOG P
where P.MSG_OUT containing 'ACCEPTED'
and P.ACT_IN containing 'PAY'
and P.PAY_OPERATOR containing upper('Yandex.Kassa')
and P.DATE_CREATE containing DAT
) as "Яндекс"
from
PAY_LOG PL
inner join PERIOD
on PL.DATE_CREATE containing DAT
where PL.MSG_OUT containing 'ACCEPTED'
and PL.ACT_IN containing 'PAY'
group by
PERIOD.DAT{code}
h6. Пример 87. Отчет "Выставленные счета физическим лицам". В отчете: сумма в счете, ФИО абонента, описание финансовой операции.
h6.
{code:lang=sql}select ((fo.op_summa)/(select const_value from vpn_const where const_id=1)) as "Сумма",
ab.name as "ФИО",
ft.op_name || ', ' || ft.op_descr
from finance_operations fo
left join abonents ab on fo.abonent_id=ab.id
left join fin_types ft on fo.op_type=ft.type_id
where (fo.op_type=5 or fo.op_type=9 or fo.op_type=1)
and fo.abonent_id=ab.id
and ab.company=0
and fo.op_date between cast(':Начало|date$' as date) and cast(':Конец|date$' as date){code}
h6. Пример 88. "Отчёт о превышении лимита предоплаченного трафика по юридическим лицам с возможностью выбора месяца - года"
h6. SQL запрос
h6.
{code:lang=sql}select distinct
ab.name,
tarif.name,
usluga.max_mb_in_m,
round(usluga.in_price / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2),
round(traf_counters.v_in / cast((1048576) as numeric(18,5)), 2),
round(traf_counters.v_out / cast((1048576) as numeric(18,5)), 2),
usluga.name,
usluga.id,
tarif.id,
ab.id
from abonents as ab
left join tarif on ab.tarif_id=tarif.id
join tarif_users_usluga as tuu on tuu.tarif_id = tarif.id
join usluga on tuu.usluga_id=usluga.id
join users_usluga as uu on uu.abonent_id = ab.id and uu.usluga_Id = usluga.id and uu.tarif_id = tarif.id
join traf_counters on ab.id=traf_counters.abonent_id
where traf_counters.MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$') and traf_counters.year_number LIKE upper(':Год$') and ab.company = 1{code}
h6. Шаблон отчёта
{code:lang=sql}{% extends "form_list.html" %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}{% load mathfilters %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><h2>Заполните поля запроса</h2></legend>
{{ form }}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<div class="btn-group">
<a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
<i class="icon-print icon-white"></i>
Выгрузка
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><button type="submit" name="csv" value="True">CSV</button></li>
<li><button type="submit" name="dbf" value="True">DBF</button></li>
</ul>
</div>
<button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{inst.name}}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}
{% if data %}
<table id="print" class="mysqldata" border="1" style="text-align: center;">
<tr>
<th>ФИО</th>
<th>Тариф</th>
<th>Услуга</th>
<th>Объем предоплаченного трафика</th>
<th>Цена за МБ</th>
<th>Объем использованного входящего</th>
<th>Объем использованного исходящего</th>
<th>Объем трафика сверх предоплаченного входящего</th>
</tr>
{% for row in data %}
{% if row.2 and row.2 < row.4 %}
<tr>
<td><a href="/admin/Abonents/Abonents/{{ row.9 }}/" target="_blank">{{ row.0 }}</td>
<td><a href="/admin/tarifs/Tarif/{{ row.8 }}/" target="_blank">{{ row.1 }}</a></td>
<td><a href="/admin/tarifs/Usluga/{{ row.7 }}/" target="_blank">{{ row.6 }}</a></td>
<td>{% if row.2 %} {{ row.2|floatformat:2 }} Мб {% else %} --- {% endif %}</td>
<td>{{ row.3 }} руб.</td>
<td>{{ row.4|floatformat:2 }} Мб</td>
<td>{{ row.5|floatformat:2 }} Мб</td>
<td>{{ row.4|sub:row.2 }} Мб</td>
</tr>
{% endif %}
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet" />
{% endblock %}{code}
h6. Пример 89. "Отчёт о динамике приходов и актов за заданный период времени"
h6. SQL запрос
h6.
{code:lang=sql}select
fo.SYSTEM_DATE as "Дата",
u.login as "Логин",
a.contract_number as "Номер договора",
a.name as "ФИО",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
round(fo.balance_buh / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс",
a.tarif_id as "ID тарифа",
t.name,
ft.op_name
from abonents as a join users as u on a.id=u.abonent_id
join finance_operations as fo on fo.ABONENT_ID=a.id
join fin_types as ft on fo.op_type = ft.type_id
join tarif as t on t.id = a.tarif_id
where
fo.SYSTEM_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and
fo.op_type in (1,2){code}
h6. Шаблон отчёта
{code:lang=sql}{% extends "form_list.html" %}
{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>
{% endfor %}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}
{% if data %}
<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>ДАТА ПРИХОДА</th>
<th>ЛОГИН</th>
<th>НОМЕР ДОГОВОРА</th>
<th>ФИО</th>
<th>СУММА</th>
<th>ТИП</th>
<th>БАЛАНС</th>
<th>ТАРИФ</th>
</tr>
{% for row in data %}
<tr>
<td class="date">{{ row.0|date:"d.m.Y G:i:s" }}</td>
<td>{{ row.1 }}</td>
<td class="contract">{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td class="fio">{{ row.4 }}</td>
<td>{{ row.8 }}</td>
<td>{{ row.5 }}</td>
<td><a href="/admin/tarifs/Tarif/{{ row.6 }}/" target="_blank">{{ row.7 }}</a></td>
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>
{% endblock %}{code}
h6. Пример 90. "Отчёт по пользователям с добровольной блокировкой"
h6. SQL запрос
h6.
{code:lang=sql}select a.name as "ФИО",
a.CONTRACT_NUMBER as "Номер договора",
uf_ip2string(ip) as "IP",
a.own_disabled_start as "Дата начала",
a.own_disabled_end as "Дата конца"
from abonents as a
left join users as u on a.id=u.abonent_id
join ABONENTS_BLOCK as ab on ab.ABONENT_ID=a.ID
where ab.b_own=1{code}
h6. Пример 91. "Список пользователей с сортировкой по ФИО (ФИО, логин, IP, Город, Улица, Дом)"
{code:lang=sql}
select
A.NAME as FIO,
U.LOGIN as LOGIN,
UF_IP2STRING(U.IP) as IP,
H.city as CITY,
H.street as STREET,
H.s_number as HOME
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
order by A.NAME
{code}
h6. Пример 92. Отчёт в формате "Логин\- Тариф - Скачанный трафик в год - Скачанный трафик в месяц - Скачанный трафик в день - Дата подключения. По текущей дате"
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
round(SUM_BYTE_IN_A/cast((1048576) as numeric(18,5)), 2) as "В год",
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "В месяц",
round(SUM_BYTE_IN_D/cast((1048576) as numeric(18,5)), 2) as "В день",
abonents.create_date as "Подключен"
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number=extract(month from current_timestamp)
and tc.year_number=extract(year from current_timestamp)
and tc.currentt=1
{code}
h6. Пример 93. Отчёт по должникам в формате "Договор ФИО Дом Телефон Баланс Долг".
{code:lang=sql}
select distinct A.CONTRACT_NUMBER as "Договор",
A.NAME as "ФИО",
(H.CITY || ' ' || H.STREET || ' ' || H.S_NUMBER || ' ' || H.S_LITER) as "Дом",
A.SMS as "Телефон",
(AC.OSTATOK + AC.DEBIT - AC.CREDIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Баланс",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
where AB.B_DATE between (':1 Дата|date$') and (':2 Дата|date$')
{code}
h6. Пример 94. Отчёт по должникам по телефонии в формате " № - Папка - ФИО - Телефонный номер - Дата последней оплаты - Сумма долга"
{code:lang=sql}
select
cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as "№",
tmp.*
from (select distinct
(select aa.name from abonents aa where aa.id=a.parent_id) as "Папка",
A.NAME as "ФИО",
PHONE_PULL_CHACHE.Phone as "Телефон",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = A.ID) as "Дата посл.",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join users u on u.abonent_id=A.ID
left join PHONE_PULL_CHACHE on PHONE_PULL_CHACHE.ID=u.PHONE)
as tmp
where rdb$set_context('USER_TRANSACTION', 'row#', coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0)+1) > -1
{code}
h6. Пример 95. Отчёт по скачанному трафику за период.
{code:lang=sql}
select distinct
users.login as "Логин",
tarif.name as "Тариф",
sum(round(tc.SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2)) as "Объем исх.",
sum(round(tc.SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2)) as "Объем вх."
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number between (':1 Месяц$') and (':2 Месяц$')
and tc.year_number=(':Год$')
and tc.currentt=1
group by users.login, tarif.name
{code}
h6. Пример 96. Отчёт по платежам для групп абонентов.
{code:lang=sql}
select a.name as "ФИО",
a.contract_number as "Договор",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
and a.parent_id=(':ID папки$')
{code}
h6. Пример 97. Отчёт для платежной системы "Город", выводящий в строку "ФИО;Адрес;Лицевой счет;Текущий остаток;';;;;'" и выводящий в Шапке отчёта :
\#FILESUM - Сумму всех остатков в данном отчёте
\#TYPE 7
\#SERVICE 10240
по всем физ.лицам
{code:lang=sql}
select '#FILESUM' || ' ' || round(sum(aa.ostatok / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round(aa.ostatok / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
{code}
Тот же отчет, выводящий текущий баланс.
{code:lang=sql}
select '#FILESUM' || ' ' || round(sum((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
and parent_id not in ( '244', '2', '4' , '1499')
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
{code}
h6. Пример 98. Пример шаблона, подходящего для любого отчёта, который выводит первым столбцом порядковый номер записи (Row Number)
{code:lang=sql}
{% extends "form_list.html" %}
{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>
{% endfor %}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}
{% if data %}
<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>№</th>
{% for field in field_desc %}
<th>{{ field|get_zero }}</th>
{% endfor %}
</tr>
{% for row in data %}
<tr>
<td>{{ forloop.counter }}</td>
{% for cell in row %}
<td>{{ cell }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>
{% endblock %}
{code}
h6. Пример 99. Отчёт по определенной услуге. Абонент, логин, баланс, конец действия услуги.
{code:lang=sql}
select USERS.LOGIN as "Логин",
ABONENTS.name as "ФИО",
(ADMIN_ACCOUNTS.OSTATOK + ADMIN_ACCOUNTS.DEBIT - ADMIN_ACCOUNTS.CREDIT)/10000000000 as "Баланс",
USERS_USLUGA.END_TIME as "Конец"
from USERS_USLUGA left join ABONENTS on ABONENTS.ID = USERS_USLUGA.ABONENT_ID
left join ADMIN_ACCOUNTS on ABONENTS.ACCOUNT_ID = ADMIN_ACCOUNTS.ID
left join USERS on ABONENTS.ID = USERS.ABONENT_ID
where USERS_USLUGA.USLUGA_ID = (':ID нужной услуги$')
and USERS_USLUGA.DELETED != 1
order by ABONENTS.NAME
{code}
h6. Пример 100. Отчёт по абонентам в формате "ФИО ЛОГИН IP MAC ТЕЛЕФОН АДРЕС ТАРИФ БАЛАНС"
{code:lang=sql}
select distinct
A.NAME as "ФИО",
U.LOGIN as "Логин",
UF_IP2STRING(U.IP) as "IP",
U.MAC as "MAC",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
T.name as "Тариф",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0
order by A.NAME
{code}
h6. Пример 101. Отчёт, выводящий сумму списаний, взятых из выставленных актов, за определенный период по всем абонентам.
{code:lang=sql}
select sum(round(op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2)) as "Сумма "
from finance_operations
where op_type=1
and op_date between (':1 Дата|date$') and (':2 Дата|date$')
{code}
h6. Пример 102. Отчет для вывода рекомендуемого ежемесячно платежа, стоимости тарифа по всем абонентам.
{code}
select
a.CONTRACT_NUMBER as "№",
a.NAME as "ФИО",
t.NAME as "ТАРИФ",
round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "АБОН.ПЛАТА",
round(ac.RECOMEND_PAY_SUM_CACHE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "РЕКОМЕНД.",
st.name as "СТАТУС",
subst.name as "ПОДСТАТУС",
cast(obs.apply_date as date) as "ДАТА"
from ABONENTS a
LEFT JOIN ABONENTS_CACHE ac ON ac.ID=a.ID
LEFT JOIN TARIF t ON a.TARIF_ID=t.ID
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN STATUS subst on subst.id = obs.SUBSTATUS
where obs.OBJECT_NAME='Abonents'
and a.is_folder = 0
and a.parent_id != 244
and a.parent_id != 2
{code}
h6. Пример 103. Отчёт по платежам через веб - кассу(По операторам)
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$') and AU.USERNAME = ':Администратор$'
{code}
h6. Пример 104. Поле "Не отключать при превышении порога", ID, ФИО.
{code}
select
a.ID as "ID",
a.NAME as "ФИО",
aa.UNLIMITED as "Не отключать"
from ABONENTS a
left join ADMIN_ACCOUNTS aa on a.ACCOUNT_ID=aa.ID
where a.IS_FOLDER=0
order by a.NAME
{code}
h6. Пример 105. Учёт заключенных договоров за период
{code}
select
a.CREATE_DATE as "Дата договора",
a.CONTRACT_NUMBER as "№ договора",
t.NAME as "Тариф",
st.NAME as "Cтатус",
a.NAME as "ФИО абонента",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
a.ACTIVATE_DATE as "Дата активации",
n.NAME as "NAS"
from ABONENTS a
LEFT JOIN TARIF t on a.TARIF_ID=t.ID
LEFT JOIN USERS u on u.ABONENT_ID=a.ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN NAS n on u.nas_id=n.ID
left join HOMES h on a.HOME_ID=h.ID
where a.IS_FOLDER=0
and a.CREATE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
{code}{*}Пример 106. Телефония. Количество звонков, ожидающих перерасчет.*
{code}
select count(1) from voip_log where recalc=1
{code}
h6. Пример 107. Отчёт по пулам IP (занято, свободно) с учётом поля "Host IP (только для VPN)"
{code}
select NAME,
UF_IP2STRING(START_IP) as "Начальный IP",
UF_IP2STRING(END_IP) as "Конечный IP",
(select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0) as "Занято",
(END_IP - START_IP - (select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0)) as "Свободно"
from IP_PULL
order by NAME
{code}
h6. Пример 108. Отчёт по приходам за заданный период времени
{code}
select a.name as "ФИО",
a.contract_number as "Договор",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
AU.USERNAME as "Ответственный",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
on a.id=fo.abonent_id
left join AUTH_USER as AU
on AU.id=fo.owner_id
where OP_TYPE=2 and
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')
{code}
h6. Пример 109. Отчёт по платежам, проведенным через Альфа - банк. Выводится информация по приходам, содержащим в поле описания"%Альфа%"
{code}
select AU.USERNAME as "Администратор",
FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.DESCR as "Описание",
round(fo.aa_ostatok/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where 1=1 and (UPPER(FO.DESCR) LIKE UPPER('%Альфа%'))
and (FO.op_type=2) and (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$')
{code}
h6. Пример 110. Отчёт по услугам, выводящий название услуги, стоимость, и количество списанных средств по этой услуге за период.
{code}
select distinct u.name as "Услуга",
round(u.summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Цена",
round(sum(counters.SUMM), 2) as "Сумма"
from counters
left join usluga u on u.id=counters.usluga_id
where S_DATE between (':1 Дата|date$') and (':2 Дата|date$')
group by u.name, u.SUMMA
{code}
h6. Пример 111. Сформированные акты/счета по юридическим лицам.
{code}
select AB.NAME as "ФИО",
CONTRACT_NUMBER as "Договор",
FO.NUMBER as "Номер акта",
round(FO.OP_SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "СУММА",
FT.OP_NAME || ', ' || FT.OP_DESCR as "ТИП ДОКУМЕНТА"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
left join FIN_TYPES FT on FO.OP_TYPE = FT.TYPE_ID
where (FO.OP_TYPE = 5
or FO.OP_TYPE = 9
or FO.OP_TYPE = 1)
and FO.ABONENT_ID = AB.ID
and AB.COMPANY = 1
and FO.OP_DATE between cast(':1. Начало|date$' as date) and cast(':2. Конец|date$' as date)
order by 1
{code}
h6. Пример 112. Отчёт по юридическим лицам, выводящий ФИО Телефон Адрес Р/С ИНН КПП
{code}
select A.NAME as "ФИО",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
av1.attribute_value as "Р/С",
av2.attribute_value as "ИНН",
av3.attribute_value as "КПП"
from abonents as a
left join HOMES as H on H.ID=A.HOME_ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES AV2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES AV3 on a.id=av3.ABONENT_ID and av3.ATTRIBUTE_ID=5
where a.is_folder=0
and a.company=1
{code}
h6. Пример 113. Отчёт прибыль по плате за подключение нескольких услуг.
{code}
select
a.NAME as "ФИО",
uu.ABONENT_ID as "ID АБОНЕНТА",
cast(uu.CREATE_DATE as date) as "ДАТА АКТИВАЦИИ",
c.SUMM as "Прибыль"
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0
union all
SELECT
cast('' as varchar(32)),
cast('' as varchar(32)),
cast('Итого' as varchar(32)),
ROUND (sum(c.summ))
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0
{code}
h6. Пример 114. Отчёт выводит абонентов, последнее подключение которых было раньше, чем указанная дата.
{code}
select distinct a.name as "ФИО",
b.max_time as "Последнее подключ." from abonents as a
inner join (select rs.abonent_id, max(rs.start_time) as max_time from radius_sessions rs group by abonent_id) as b on a.id=b.abonent_id where max_time<(':1 Дата|date$')
{code}
h6. Пример 115. Отчёт выводит информацию по абонентам с определенным NAS'ом.
{code}
select distinct
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
u.login,
u.GEN_PWD,
u.auth_type,
uf_ip2string(s.ip) as "switch_ip",
abs.RATE_IN,
abs.RATE_OUT,
abs.CEIL_IN,
abs.CEIL_OUT,
uf_ip2string(u.ip) as "user_ip",
u.ROUTER_VLAN,
u.GPON_MODEM_PORT,
u.ROUTER_PORT
from users as u
left join abonents as a on a.id=u.abonent_id
left join admin_accounts as aa on aa.id=a.ACCOUNT_ID
left join switch as s on s.id=u.switch_id
left join ABONENTS_SPEED as ABS on abs.abonent_id=a.id
left join NAS on nas.id=u.nas_id
where a.is_folder=0
and a.deleted=0
and uf_ip2string(nas.ip)=(':nas_ip$')
{code}
h6. Пример 116. Отчёт, выводящий Лицевой счет, ФИО, Адрес (из справочника "Дома"), Телефон, Баланс, Статус.
{code}
select
a.contract_number as "Договор",
a.name as "Имя",
('г. ' || h.city || ' ' || h.street || ', д.' || h.s_number || ', кв.' || a.a_home_number) as "Адрес",
(select av.attribute_value from attribute_values av where av.attribute_id = 1 and av.abonent_id = a.id) as "Телефон",
cast(round((cast(aa.ostatok as float)+cast(aa.debit as float)-cast(aa.credit as float))/10000000000,2) as numeric(18,2)) as "Баланс",
(select st.name from status st where st.id = os.status) as "Статус"
from
abonents a
left join
homes h on h.id = a.home_id
join
admin_accounts aa on aa.id = a.account_id
join
objects_status os on a.id = os.object_id
where
a.is_folder = 0
{code}
h6. Пример 117. Поиск IP-адреса в истории изменения учетных записей (история выдачи ip-адреса)
{code}
select
uf_ip2string(ip) as IP,abonent_id,time_changed,a.name
from
users_history uh
join
abonents a
on uh.abonent_id = a.id
where
uf_ip2string(ip) = ':Введите ip-адрес$'
{code}
h6. Пример 118. Отчёт в формате "Договор - Тариф - Имя услуги - Активность услуги - Баланс"
{code}
select distinct
A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
USL.NAME as "Имя",
UU.ACTIVATED as "Активна",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join USERS_USLUGA UU on UU.abonent_id=A.id
left join USLUGA USL on USL.ID=UU.USLUGA_ID
where A.IS_FOLDER=0
AND A.DELETED=0
order by A.NAME
{code}