Конструктор отчетов. Отчёт

h2. Для создания отчета через конструктор отчетов необходимо:

1. Зайти в АСР менеджере "Отчеты \-> Конструктор отчетов"

!1.png|border=1!

2. Добавить новый отчет кнопкой "+" справа ("-" удаляет выбранный отчет)
3. Задать название отчета и текст запроса, который вы можете уточнить у тех.поддержки (должен начинаться со слова "select").

!Снимок-Отчет.png|border=1!

4. Также есть возможность настроить привилегии доступа для разных типов администраторов.

 Для вывода отчета достаточно выбрать нужный и нажать кнопку "Запросить".  

5. Начиная с версии {color:#000000}{*}3.9.6 484{*}{color} {color:#000000}есть возможность выгрузить полученный отчет в Excel.{color} {color:#ff0000}Работает только под системой Windows\!{color}

{color:#ff0000}{*}Выгрузка может не работать, если имя отчета длиннее 31 символа\!*{color}
 
!werwer.png|border=1!


{color:#e00000}{*}Важно:*{color}{color:#ff0000}* *{color}Если у вас не выводится отчет при нажатии на кнопку запросить(и она впадает при наведении курсора) вероятнее всего у вас стоит неверный пароль для пользователя SYSDBA. Паролем для пользователя SYSDBA являются первые 8 символов от пароля локальной консоли (или меньше, если пароль меньше восьми символов)

h2. Также запросы можно выполнять из командной строки.


h3. Выполнение запросов из командной строки


Из командной сторки запросы выполняются при помощи утилиты sqlexec таким образомsqlexec "текст запроса"


h6. Дополнительные моменты

Вывод на экран в виде спискаsqlexec "set list on ; текст запроса"

Вывод на экран русских символов - добавьте в конец строки.\| iconv \-f cp1251

Запись в файл, для того чтобы забрать его по WinSCP / SCP позже:> /tmp/file

h2. Примеры:

{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

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)

U.ENABLED as "1-Включен/0-Выключен"
from USERS_USLUGA UU
left join USERS U on UU.USER_ID = U.ID

{code:lang=sql|title=1С коды}select U.ID as "Номер",
U.IDENTIFY as "Наименование",
U.CODE1C "Код синхронизации",
U.SYNC1C as "1-синхронизирован/0-несинхронизирован"

where (FO.OP_TYPE <>2 and FO.OP_TYPE <>3)

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

AV.ATTRIBUTE_VALUE as ADRES,
CREATE_DATE
from USERS U

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.%'

        (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",

  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

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'

FROM CARDS
LEFT JOIN CARD_SERIES ON CARDS.SERIES_NO = card_series.SERIES_NO
WHERE
cards.date_used > '2012-12-01 00:00:00'

(DEBIT + OSTATOK - CREDIT)/10000000000.00 as "BALANS",

U.IDENTIFY,
sum(PL.SUMMA_IN)
from PAY_LOG PL
left join USERS U on PL.USER_ID_IN = U.ID

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

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

(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

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

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

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

USERS_USLUGA.CREATE_DATE AS "CREATE_DATE"

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

USLUGA.NAME
from USERS_USLUGA US
left join USERS on US.USER_ID=USERS.ID

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

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;

CR.PLAN_NAME "Тарифный план",
CR.USER_NAME "ФИО",
CR.START_BALANCE "Остаток на начало периода",
CR.PAY_SUM "Платеж через кассира",
CR.SUM_CREDIT "Расход",
CR.TRAF_SUM "Расход по трафику",
CR.END_BALANCE "Остаток на конец периода"

left join USERS on USERS.id = SESSIONS.id

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

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

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

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

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

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

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

(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 "Количество абонентов"

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

TIME_IN as "Начало сесии",
TIME_OUT as "Конец сесии"
from SESSIONS

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

PL.SUMMA_IN as SUMMA,
PL.OPERATOR_DATE_IN as DATA
from PAY_LOG PL
left join USERS U on U.id=PL.user_id_out

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

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

Отчеты