Отчёты для СОРМ 3

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

Выгрузка на СОРМ3

Для автоматизации передачи отчетов на СОРМ3 следует:

  1. Удалить в начале select '' from abonents UNION
  2. Открыть текстовый файл в редакторе и привести к следующему виду set heading off; <ТЕКСТ ЗАПРОСА>;, например:
    set heading off; select * from abonents;
  3. Сохранить в пределах контейнера /app/asr_billing, например /app/asr_billing/root/sorm3.sql
  4. Выполнить отчет следующей командой:
    chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /root/sorm3.sql > /app/asr_billing/root/sorm3-export.csv
  5. Любым удобным способом наладить автоматическое выполнение и передачу результата запроса в СОРМ3 (например, с помощью cron отправлять файл утилитой curl)

Отчеты

1. Отчёт по заключенным договорам на оказание услуг связи c использованием только стандартных реквизитов.

select '' from abonents 
UNION 
select distinct '"' || 
U.LOGIN || '";"' || 
UF_IP2STRING(U.IP) || '";"' || 
U.MAC || '";"' ||  
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || '";"' || 
A.CONTRACT_NUMBER  || '";"' || 
case when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then	'1' else '0' end || '";"' ||  
CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || ' 00:00:00";"' ||  
case 
	when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then	
		(select first 1 CAST(lpad(EXTRACT(DAY FROM abb.b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM abb.b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM abb.b_date) || ' 00:00:00";"' from abonents_block abb where abb.abonent_id = a.id and (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1))
	else 
		'' end ||

'";"' || 
A.COMPANY || '";' || 

case when a.company=0 then

	'"1";"";"";"";"' || 
	A.NAME || '";"' || 
	coalesce(av22.attribute_value,'') || '";"1";"";"";"";"' || 

	iif(
		(coalesce(av14.attribute_value,'') || coalesce(av13.attribute_value,'') || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'')) <> '', iif(coalesce(av14.attribute_value,'') <> '', av14.attribute_value,'') || iif(coalesce(av13.attribute_value,'') <> '', ' ' || av13.attribute_value,'') || iif(coalesce(av16.attribute_value,'') <> '', ' ' || av16.attribute_value,'') || iif(coalesce(av17.attribute_value,'') <> '', ' ' || av17.attribute_value,'') || 
		iif(av15.attribute_value > 0, ' Адрес регистрации: ' || (select IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '')  || IIF(coalesce(h.STREET,'') <> '',  h.STREET || ' ', '') ||  coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' ||  a.A_HOME_NUMBER,'') from homes h where h.id=av15.attribute_value),' Адрес регистрации не заполнен')
	, '-')

	|| '";"1";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";' 

else '"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' end || 

case when a.company = 1 then

	coalesce(av2.attribute_value,'')  || '";"' || coalesce(av4.attribute_value,'')  || '";"' || coalesce(av32.attribute_value,'-') || '";"' || coalesce(av1.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || coalesce(av6.attribute_value,'')  || ';'

else '"";"";"";"";"";"";' end

|| '"1";"";"";"";"";"";"";"";"";"";' || 
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '')  || IIF(coalesce(h.STREET,'') <> '',  h.STREET || ' ', '') ||  coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' ||  a.A_HOME_NUMBER,'')  || '"', '""') || 
';"1";"";"";"";"";"";"";"";"";"";' ||
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '')  || IIF(coalesce(h.STREET,'') <> '',  h.STREET || ' ', '') ||  coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' ||  a.A_HOME_NUMBER,'')  || '"', '""')

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 ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av13 on a.id=av13.ABONENT_ID and av13.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av14 on a.id=av14.ABONENT_ID and av14.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av16 on a.id=av16.ABONENT_ID and av16.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av17 on a.id=av17.ABONENT_ID and av17.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join ATTRIBUTE_VALUES as av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join abonents_block ab on ab.abonent_id=a.id
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.deleted=0

Тот же отчёт, в который попадает только информация, добавленная за последние сутки

select '' from abonents 
UNION 
select distinct '"' || 
U.LOGIN || '";"' || 
UF_IP2STRING(U.IP) || '";"' || 
U.MAC || '";"' ||  
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || '";"' || 
A.CONTRACT_NUMBER  || '";"' || 
case when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then	'1' else '0' end || '";"' ||  
CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || ' 00:00:00";"' ||  
case 
	when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then	
		(select first 1 CAST(lpad(EXTRACT(DAY FROM abb.b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM abb.b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM abb.b_date) || ' 00:00:00";"' from abonents_block abb where abb.abonent_id = a.id and (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1))
	else 
		'' end ||

'";"' || 
A.COMPANY || '";' || 

case when a.company=0 then

	'"1";"";"";"";"' || 
	A.NAME || '";"' || 
	coalesce(av22.attribute_value,'') || '";"1";"";"";"";"' || 

	iif(
		(coalesce(av14.attribute_value,'') || coalesce(av13.attribute_value,'') || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'')) <> '', iif(coalesce(av14.attribute_value,'') <> '', av14.attribute_value,'') || iif(coalesce(av13.attribute_value,'') <> '', ' ' || av13.attribute_value,'') || iif(coalesce(av16.attribute_value,'') <> '', ' ' || av16.attribute_value,'') || iif(coalesce(av17.attribute_value,'') <> '', ' ' || av17.attribute_value,'') || 
		iif(av15.attribute_value > 0, ' Адрес регистрации: ' || (select IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '')  || IIF(coalesce(h.STREET,'') <> '',  h.STREET || ' ', '') ||  coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' ||  a.A_HOME_NUMBER,'') from homes h where h.id=av15.attribute_value),' Адрес регистрации не заполнен')
	, '-')

	|| '";"1";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";' 

else '"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' end || 

case when a.company = 1 then

	coalesce(av2.attribute_value,'')  || '";"' || coalesce(av4.attribute_value,'')  || '";"' || coalesce(av32.attribute_value,'-') || '";"' || coalesce(av1.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || coalesce(av6.attribute_value,'')  || ';'

else '"";"";"";"";"";"";' end

|| '"1";"";"";"";"";"";"";"";"";"";' || 
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '')  || IIF(coalesce(h.STREET,'') <> '',  h.STREET || ' ', '') ||  coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' ||  a.A_HOME_NUMBER,'')  || '"', '""') || 
';"1";"";"";"";"";"";"";"";"";"";' ||
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '')  || IIF(coalesce(h.STREET,'') <> '',  h.STREET || ' ', '') ||  coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_NUMBER, '') <> '', ' ' ||  a.A_HOME_NUMBER,'')  || '"', '""')

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 ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av13 on a.id=av13.ABONENT_ID and av13.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av14 on a.id=av14.ABONENT_ID and av14.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av16 on a.id=av16.ABONENT_ID and av16.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av17 on a.id=av17.ABONENT_ID and av17.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join ATTRIBUTE_VALUES as av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join abonents_block ab on ab.abonent_id=a.id

where A.IS_FOLDER=0
and (u.login in (select uh.login from users_history uh where uh.time_changed between  (current_timestamp - 1) and current_timestamp) 
or a.id in (select ah.abonents_id from abonents_history ah where ah.time_changed between  (current_timestamp - 1) and current_timestamp))
and a.parent_id not in (244)
and a.deleted=0	
and a.id not Null

2. Активированные пользователями дополнительные услуги (услуги, не включенные в тариф) services без учета системных обещанных платежей.

Формат "логин";"номер договора";"идентификатор услуги";"дата и время подключения услуги";"дата и время отключения";""

select distinct
     '"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || UU.USLUGA_ID || '"' || ';'|| '"' || CAST(lpad(EXTRACT(DAY FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.ENABLE_DATE) || ' 00:00:00' || '"' || ';' || '"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.END_TIME) || ' ' || EXTRACT(HOUR FROM  UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""'
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_HISTORY UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and UU.ENABLE_DATE is not null
and UU.TARIF_ID is null
and UU.USLUGA_ID!=-4
and UU.USLUGA_ID!=-3
and A.PARENT_ID!=244
and A.DELETED=0
order by A.NAME

Тот же отчёт, который выводит только услуги подключенные за последний час

select distinct
     '"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || UU.USLUGA_ID || '"' || ';'|| '"' || CAST(lpad(EXTRACT(DAY FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.ENABLE_DATE) || ' 00:00:00' || '"' || ';' || '"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  UU.END_TIME) || ' ' || EXTRACT(HOUR FROM  UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""'
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_HISTORY UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and UU.ENABLE_DATE is not null
and UU.TARIF_ID is null
and UU.USLUGA_ID!=-4
and UU.USLUGA_ID!=-3
and A.PARENT_ID!=244
and A.DELETED=0
and UU.TIME_CHANGED between  (current_timestamp - 1/24) and current_timestamp
order by A.NAME

3. Пополнение баланса личного счета абонента обобщенная информация.

Отчёт выводит информацию в виде "1";"идентификатор способа оплаты";"номер договора ";"статический IP-адрес";"дата и время пополнения баланса";"сумма";""
В данном отчёте идентификаторы способа оплаты зависят от описания платежа:
Описание Gorod - 1
Описание Paymaser - 2
Остальное - 3

select 
      '"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME)
 WHEN 'GOROD' THEN '1'
 WHEN 'PAYMASTER' THEN '2'
 ELSE '3'
 END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM FO.OP_DATE) || ' 00:00:00' || '"' || ';' || '"' || round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '"' || ';' || '""'
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
left join users u on u.abonent_id=a.id
where fo.OP_TYPE=2
and A.PARENT_ID!=244
and A.DELETED=0

Тот же отчёт, который выводит платежи, проводимые за последний час

select 
      '"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME)
 WHEN 'GOROD' THEN '1'
 WHEN 'PAYMASTER' THEN '2'
 ELSE '3'
 END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM FO.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM FO.OP_DATE) || ' 00:00:00' || '"' || ';' || '"' || round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '"' || ';' || '""'
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
left join users u on u.abonent_id=a.id
where fo.OP_TYPE=2
and A.PARENT_ID!=244
and A.DELETED=0
and DATEADD(-1 hour to current_timestamp) - FO.SYSTEM_DATE < 1/24

4. Отчёт выводит дополнительные услуги

Формат "идентификатор услуги ";"название/обозначение в Вашей системе";"дата создания услуги в биллинге";"";"описание";

set heading off;
select 
	'"' || 
	usl.id || '";"' ||
	usl.NAME || '";"' ||
	(select first 1 
		cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' || 
		cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' || 
		extract(year from time_changed) from usluga_history where usluga_id = usl.id) ||
		' 00:00:00";"' ||
	iif(usl.deleted > 0,
		(select first 1 
			cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' || 
			cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' || 
			extract(year from time_changed) from usluga_history where usluga_id = usl.id order by time_changed desc) ||
			' 00:00:00";"',
		'";"') ||
	coalesce(usl.comments,'') || '"'
from 
	usluga usl
where
	usl.system_type != 13 and
	usl.id in (select usluga_id from usluga_history union distinct select usluga_id from users_usluga where deleted = 0 and abonent_id is not null) and
	(id in (select usluga_id from usluga_history where time_changed between  (current_timestamp - 1) and current_timestamp))
order by id;

Тот же отчёт, выводящий услуги, созданные за прошедшие сутки:

set heading off;
select 
	'"' || 
	usl.id || '";"' ||
	usl.NAME || '";"' ||
	(select first 1 
		cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' || 
		cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' || 
		extract(year from time_changed) from usluga_history where usluga_id = usl.id) ||
		' 00:00:00";"' ||
	iif(usl.deleted > 0,
		(select first 1 
			cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' || 
			cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' || 
			extract(year from time_changed) from usluga_history where usluga_id = usl.id order by time_changed desc) ||
			' 00:00:00";"',
		'";"') ||
	coalesce(usl.comments,'') || '"'
from 
	usluga usl
where
	usl.system_type != 13 and
	(id in (select usluga_id from usluga_history where time_changed between  (current_timestamp - 1) and current_timestamp));
Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.