СОРМ3 Специальные технологии

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

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

Принцип работы

Для интеграции с СОРМ3 Норси-Транс требуется произвести первичную выгрузку данных, а так же добавить сценарии планировщика задач, которые будут выгружать добавленные и изменённые данные с определённой периодичностью
При изменении списка используемых документов или плана ip-адрессации, измените соответствущие csv файлы и повторно выполните скрипт initial-static-data.sh. При выгрузке изменённого pay-types.csv убедитесь, что не перепутались id платежных систем - в таком случае файл нужно будет исправить вручную.

Отчёты

Abonents.sql

select first 1 'ID;REGION_ID;CONTRACT_DATE;CONTRACT;ACCOUNT;ACTUAL_FROM;ACTUAL_TO;ABONENT_TYPE;NAME_INFO_TYPE;FAMILY_NAME;GIVEN_NAME;INITIAL_NAME;UNSTRUCT_NAME;BIRTH_DATE;IDENT_CARD_TYPE_ID;IDENT_CARD_TYPE;IDENT_CARD_SERIAL;IDENT_CARD_NUMBER;IDENT_CARD_DESCRIPTION;IDENT_CARD_UNSTRUCT;BANK;BANK_ACCOUNT;FULL_NAME;INN;CONTACT;PHONE_FAX;STATUS;ATTACH;DETACH;NETWORK_TYPE;RECORD_ACTION;INTERNAL_ID1'
from ABONENTS
union all

select distinct 
'' || ';' ||
a.operator_id || ';' ||
SUBSTRING((coalesce(cast(a.create_date as varchar(32)),'')) FROM 1 FOR 19)|| ';' ||
A.CONTRACT_NUMBER  || ';' ||
a.account_id || ';' ||
EXTRACT(YEAR FROM  current_timestamp) ||'-'|| CAST(lpad(EXTRACT(MONTH FROM  current_timestamp),2,'0') AS varchar(2)) ||'-'|| CAST(lpad(EXTRACT(DAY FROM  current_timestamp),2,'0') AS varchar(2)) || ' ' || CAST(lpad(EXTRACT(hour FROM  current_timestamp),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM  current_timestamp),2,'0') AS varchar(2)) || ':00' ||  ';' ||/*actual_from*/
EXTRACT(YEAR FROM  DATEADD(DAY, +1, current_timestamp)) ||'-'|| CAST(lpad(EXTRACT(MONTH FROM  DATEADD(DAY, +1, current_timestamp)),2,'0') AS varchar(2)) ||'-'|| CAST(lpad(EXTRACT(DAY FROM  DATEADD(DAY, +1, current_timestamp)),2,'0') AS varchar(2)) || ' ' || CAST(lpad(EXTRACT(hour FROM  DATEADD(DAY, +1, current_timestamp)),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM  DATEADD(DAY, +1, current_timestamp)),2,'0') AS varchar(2)) || ':00'  || ';' ||/*actual_to*/


case when a.company = 0 then

		'42;1;;;' || ';' ||
		A.NAME || ';' ||
		coalesce(av22.attribute_value,'') ||  ';' ||/*client-birth-date*/ 
		'1;0' ||  ';' ||
		coalesce(av14.attribute_value,'') || ';' ||/*SERIA PASSPORTA*/
		coalesce(av13.attribute_value,'') || ';' ||/*NOMER PASSPORTA*/
		coalesce(av17.attribute_value,'') ||  '' || /*KOGDA VIDAN*/
		coalesce(av16.attribute_value,'') || ';' || /*KEM VIDAN*/
		'' || ';' || /*Unstruct*/
		coalesce(av7.attribute_value,'') || ';' || /*BANK*/
		coalesce(av6.attribute_value,'') || ';' /*rs*/
		
		else '43;;;;;;;;;;;;;;;' end ||
			
case when a.company = 1 then
		
		A.NAME || ';' ||
		coalesce(av4.attribute_value,'') || ';' || /*org-inn*/
		coalesce(av32.attribute_value,'') || ';' ||/*contact_lico*/
		coalesce(a.sms,'') || ';' /*contact_lico*/

		else ';;;;' end ||

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 || ';' ||

SUBSTRING((coalesce(cast(UU.ENABLE_DATE as varchar(32)),'')) FROM 1 FOR 19) || ';' ||
case when coalesce(uu.ACTIVATED,'')=1 then ';'
else
SUBSTRING((coalesce(cast(UU.END_TIME as varchar(32)),'')) FROM 1 FOR 19)
 || ';' 

end ||

'4;1;' || a.id



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  USERS_USLUGA UU on UU.ABONENT_ID=A.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
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
UU.USLUGA_ID not in (select id from usluga where name = '') 
and a.parent_id not in (244)
and a.deleted=0
and a.id is not null
and a.parent_id!=1362
and a.parent_id!=2

ABONENT_ADDR.sql

select first 1 'ABONENT_ID;REGION_ID;ADDRESS_TYPE_ID;ADDRESS_TYPE;ZIP;COUNTRY;REGION;ZONE;CITY;STREET;BUILDING;BUILD_SECT;APARTMENT;UNSTRUCT_INFO;BEGIN_TIME;END_TIME;RECORD_ACTION;INTERNAL_ID1;INTERNAL_ID2'
from USERS U

union all
select distinct
     '' || ';' ||
     '1' || ';' ||
     '3' || ';' ||
     '0' || ';' ||
     coalesce(H.ZIP_CODE,'') || ';' ||
     coalesce(H.COUNTRY,'') || ';' ||
     coalesce(H.REGION,'') || ';' ||
     '' || ';' ||
     coalesce(H.city,'') || ';' ||
     coalesce(H.street,'') || ';' ||
     coalesce(H.s_number,'') || ';' ||
     coalesce(H.S_LITER,'') || ';' ||
     coalesce(A.A_HOME_NUMBER,'')  || ';' || 
     '' || ';' ||
     SUBSTRING((coalesce(cast(current_timestamp as varchar(32)),'')) FROM 1 FOR 19) || ';' ||  /*6*/    
     SUBSTRING((coalesce(cast((DATEADD(DAY, +1, current_timestamp)) as varchar(32)),'')) FROM 1 FOR 19) || ';' || /*7*/
     '1' || ';' ||
     a.id || ';' ||
     coalesce(u.id,'')

from USERS U
left join ABONENTS A on A.ID = U.ABONENT_ID
left join HOMES H on U.HOME_ID = H.ID
left join ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25 
left join ATTRIBUTE_VALUES PPROPRISKA on PPROPRISKA.ABONENT_ID=A.ID and PPROPRISKA.ATTRIBUTE_ID=15 
left join ATTRIBUTE_VALUES POCHADR on POCHADR.ABONENT_ID=A.ID and POCHADR.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES PHADR on PHADR.ABONENT_ID=A.ID and PHADR.ATTRIBUTE_ID=26 
where a.deleted=0
and a.parent_id not in (244)
and a.deleted=0
and a.id>1
and A.IS_FOLDER=0 

union all

select distinct
     '' || ';' ||
     '1' || ';' ||
     '0' || ';' ||
     '0' || ';' ||
     coalesce(H.ZIP_CODE,'') || ';' ||
     coalesce(H.COUNTRY,'') || ';' ||
     coalesce(H.REGION,'') || ';' ||
     '' || ';' ||
     coalesce(H.city,'') || ';' ||
     coalesce(H.street,'') || ';' ||
     coalesce(H.s_number,'') || ';' ||
     coalesce(H.S_LITER,'') || ';' ||
     coalesce(A.A_HOME_NUMBER,'')  || ';' || 
     '' || ';' ||
     SUBSTRING((coalesce(cast(current_timestamp as varchar(32)),'')) FROM 1 FOR 19) || ';' ||  /*6*/    
     SUBSTRING((coalesce(cast((DATEADD(DAY, +1, current_timestamp)) as varchar(32)),'')) FROM 1 FOR 19) || ';' || /*7*/
     '1' || ';' ||
     a.id || ';' ||
     coalesce((select first 1 coalesce(id,'') from users where users.abonent_id=a.id),'')

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 ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25 
left join ATTRIBUTE_VALUES PPROPRISKA on PPROPRISKA.ABONENT_ID=A.ID and PPROPRISKA.ATTRIBUTE_ID=15 
left join ATTRIBUTE_VALUES POCHADR on POCHADR.ABONENT_ID=A.ID and POCHADR.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES PHADR on PHADR.ABONENT_ID=A.ID and PHADR.ATTRIBUTE_ID=26 
where a.deleted=0
and a.parent_id not in (244)
and a.deleted=0
and a.id>1
and A.IS_FOLDER=0 

union all

select distinct
     '' || ';' ||
     '1' || ';' ||
     '2' || ';' ||
     '0' || ';' ||
     coalesce(H.ZIP_CODE,'') || ';' ||
     coalesce(H.COUNTRY,'') || ';' ||
     coalesce(H.REGION,'') || ';' ||
     '' || ';' ||
     coalesce(H.city,'') || ';' ||
     coalesce(H.street,'') || ';' ||
     coalesce(H.s_number,'') || ';' ||
     coalesce(H.S_LITER,'') || ';' ||
     coalesce(A.A_HOME_NUMBER,'')  || ';' || 
     '' || ';' ||
     SUBSTRING((coalesce(cast(current_timestamp as varchar(32)),'')) FROM 1 FOR 19) || ';' ||  /*6*/    
     SUBSTRING((coalesce(cast((DATEADD(DAY, +1, current_timestamp)) as varchar(32)),'')) FROM 1 FOR 19) || ';' || /*7*/
     '1' || ';' ||
     a.id || ';' ||
     coalesce((select first 1 coalesce(id,'') from users where users.abonent_id=a.id),'')

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 ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25 
left join ATTRIBUTE_VALUES PPROPRISKA on PPROPRISKA.ABONENT_ID=A.ID and PPROPRISKA.ATTRIBUTE_ID=15 
left join ATTRIBUTE_VALUES POCHADR on POCHADR.ABONENT_ID=A.ID and POCHADR.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES PHADR on PHADR.ABONENT_ID=A.ID and PHADR.ATTRIBUTE_ID=26 
where a.deleted=0
and a.parent_id not in (244)
and a.deleted=0
and a.id>1
and A.IS_FOLDER=0 

abonent_ident.sql

set heading off;
select first 1 'ABONENT_ID;REGION_ID;IDENT_TYPE;PHONE;INTERNAL_NUMBER;IMSI;IMEI;ICC;MIN;ESN;EQUIPMENT_TYPE;MAC;VPI;VCI;LOGIN;E_MAIL;PIN;USER_DOMAIN;RESERVED;ORIGINATOR_NAME;IP_TYPE;IPV4;IPV6;IPV4_MASK;IPV6_MASK;BEGIN_TIME;END_TIME;LINE_OBJECT;LINE_CROSS;LINE_BLOCK;LINE_PAIR;LINE_RESERVED;LOC_TYPE;LOC_LAC;LOC_CELL;LOC_TA;LOC_CELL_WIRELESS;LOC_MAC;LOC_LATITUDE;LOC_LONGITUDE;LOC_PROJECTION_TYPE;RECORD_ACTION;INTERNAL_ID1;INTERNAL_ID2'
from ABONENTS
union all
select distinct
'' || ';' ||
a.operator_id ||  ';' ||
'5' ||  ';' ||
''  ||  ';' ||
';;;;;' || ';' ||
 '0' || ';' ||
coalesce(u.mac,'')  || ';' ||
 '' || ';' ||
  '' || ';' ||
coalesce(u.login,'')   || ';' ||
coalesce(a.EMAIL,'') || ';' ||
';;;' || ';' ||
'0' || ';' ||
UF_IP2HEX(ip) || ';' ||
''  ||  ';' ||
coalesce(u.mask,'') ||  ';' ||
''  ||  ';' ||
SUBSTRING((coalesce(cast(UU.ENABLE_DATE as varchar(32)),'')) FROM 1 FOR 19) || ';' ||
case when coalesce(uu.ACTIVATED,'')=1 then ';'
else
SUBSTRING((coalesce(cast(UU.END_TIME as varchar(32)),'')) FROM 1 FOR 19)
 || ';' 

end || 
';;;;;;;;;;;;;' ||  ';'  || 
'1' ||  ';'  || 
a.id ||  ';'  || 
coalesce(u.id,'')



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 /*INN*/
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6 /*r/s*/
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7 /*bank*/
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
left join  USERS_USLUGA UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0 and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
UU.USLUGA_ID not in (select id from usluga where name = '') 
and a.parent_id not in (244)
and a.deleted=0
and a.id>1;

Abonent_srv.sql

select first 1 'ABONENT_ID;REGION_ID;ID;BEGIN_TIME;END_TIME;PARAMETER;SRV_CONTRACT;RECORD_ACTION'
from Abonents
union all
select distinct
     uu.abonent_id || ';' ||
     '1' ||';' ||
     uu.usluga_id || ';' ||
     SUBSTRING((coalesce(cast(UU.ENABLE_DATE as varchar(32)),'')) FROM 1 FOR 19) || ';' ||
     case when coalesce(uu.ACTIVATED,'')=1 then ';'
     else
     SUBSTRING((coalesce(cast(UU.END_TIME as varchar(32)),'')) FROM 1 FOR 19)
     || ';' 
     end || 
     '' || ';' ||
     a.CONTRACT_NUMBER || ';' ||
     '1'

from USERS_USLUGA UU
left join  Abonents A on UU.ABONENT_ID=A.ID
where
     A.IS_FOLDER=0 and
     A.PARENT_ID!=244 and
     A.DELETED=0 and
     UU.ENABLE_DATE is not null and
     UU.USLUGA_ID not in (-3,-4) and
     UU.USLUGA_ID not in (select id from usluga where name = '') 
     and uu.deleted=0

Abonent_user.sql

select first 1 'ABONENT_ID;REGION_ID;USER_NUMBER;USER_NAME;RECORD_ACTION'
from ABONENTS
union all
select distinct
a.id || ';' ||
'1' ||  ';' ||
a.contract_number  ||  ';' ||
a.name ||  ';' ||
'1'

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 /*INN*/
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6 /*r/s*/
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7 /*bank*/
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
left join  USERS_USLUGA UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0 and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
UU.USLUGA_ID not in (select id from usluga where name = '') 
and a.parent_id not in (244)
and a.deleted=0
and a.id>1
and a.company=1
Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.