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