СОРМ3 Специальные технологии Омега v2.4 (архивная)

Skip to end of metadata
Go to start of metadata

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

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

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

Отчёты

/cfg/sorm3/ABONENTS.sql

set heading off;
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
join usluga us on us.id=uu.usluga_id
where A.IS_FOLDER=0 and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
us.name != '' 
and a.parent_id not in (244)
and a.deleted=0
and a.id is not null
and a.parent_id!=2;

Тот же отчёт за последний день - ABONENTS_PERIODIC.sql

/cfg/sorm3/ABONENTS_PERIODIC.sql

set heading off;
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
join
        (select distinct abonent_id abon
        from users_history
        where time_changed between  (current_timestamp - 1) and current_timestamp
                or abonent_id in (select distinct abonents_id abon from abonents_history  where time_changed between  (current_timestamp - 1) and current_timestamp)
        ) user_periodic on user_periodic.abon=a.id
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
join usluga us on us.id=uu.usluga_id
where A.IS_FOLDER=0 and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
us.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
;

/cfg/sorm3/ABONENT_ADDR.sql

set heading off;
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_ADDR_PERIODIC.sql

/cfg/sorm3/ABONENT_ADDR_PERIODIC.sql

set heading off;
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
join
        (select distinct abonent_id abon
        from users_history
        where time_changed between  (current_timestamp - 1) and current_timestamp
                or abonent_id in (select distinct abonents_id abon from abonents_history  where time_changed between  (current_timestamp - 1) and current_timestamp)
        ) user_periodic on user_periodic.abon=a.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
join
        (select distinct abonent_id abon
        from users_history
        where time_changed between  (current_timestamp - 1) and current_timestamp
                or abonent_id in (select distinct abonents_id abon from abonents_history  where time_changed between  (current_timestamp - 1) and current_timestamp)
        ) user_periodic on user_periodic.abon=a.id
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
join
        (select distinct abonent_id abon
        from users_history
        where time_changed between  (current_timestamp - 1) and current_timestamp
                or abonent_id in (select distinct abonents_id abon from abonents_history  where time_changed between  (current_timestamp - 1) and current_timestamp)
        ) user_periodic on user_periodic.abon=a.id
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;

/cfg/sorm3/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
join usluga us on us.id=uu.usluga_id
where A.IS_FOLDER=0 and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
us.name != '' 
and a.parent_id not in (244)
and a.deleted=0
and a.id>1;

Тот же отчёт за последний день - ABONENT_IDENT_PERIODIC.sql

/cfg/sorm3/ABONENT_IDENT_PERIODIC.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
join
        (select distinct abonent_id abon
        from users_history
        where time_changed between  (current_timestamp - 1) and current_timestamp
                or abonent_id in (select distinct abonents_id abon from abonents_history  where time_changed between  (current_timestamp - 1) and current_timestamp)
        ) user_periodic on user_periodic.abon=a.id
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
join usluga us on us.id=uu.usluga_id
where A.IS_FOLDER=0 and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
us.name != '' 
and a.parent_id not in (244)
and a.deleted=0
and a.id>1;

/cfg/sorm3/ABONENT_SRV.sql

set heading off;
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_SRV_PERIODIC.sql

/cfg/sorm3/ABONENT_SRV_PERIODIC.sql

set heading off;
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
join (select distinct
                users_usluga_id uuid
          from users_usluga_history
          where time_changed between  dateadd (-1 hour to current_timestamp) and current_timestamp)users_usluga_periodic on uu.id=users_usluga_periodic.uuid
left join  Abonents A on UU.ABONENT_ID=A.ID
join usluga us on us.id=uu.usluga_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
     us.name != ''
     and uu.deleted=0;

/cfg/sorm3/ABONENT_USER.sql

set heading off;
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;

Тот же отчёт за последний день - ABONENT_USER_PERIODIC.sql

/cfg/sorm3/ABONENT_USER_PERIODIC.sql

set heading off;
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
join
        (select distinct abonent_id abon
        from users_history
        where time_changed between  (current_timestamp - 1) and current_timestamp
                or abonent_id in (select distinct abonents_id abon from abonents_history  where time_changed between  (current_timestamp - 1) and current_timestamp)
        ) user_periodic on user_periodic.abon=a.id
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;

/cfg/sorm3/PAYMENT.sql

set heading off;
select first 1 'REGION_ID;PAYMENT_TYPE;PAY_TYPE_ID;PAYMENT_DATE;AMOUNT;AMOUNT_CURRENCY;PHONE_NUMBER;ACCOUNT;ABONENT_ID;BANK_ACCOUNT;BANK_NAME;EXPRESS_CARD_NUMBER;TERMINAL_ID;TERMINAL_NUMBER;LATITUDE;LONGITUDE;PROJECTION_TYPE;CENTER_ID;DONATED_PHONE_NUMBER;DONATED_ACCOUNT;DONATED_INTERNAL_ID1;DONATED_INTERNAL_ID2;CARD_NUMBER;PAY_PARAMS;PERSON_RECIEVED;BANK_DIVISION_NAME;BANK_CARD_ID;ADDRESS_TYPE_ID;ADDRESS_TYPE;ZIP;COUNTRY;REGION;ZONE;CITY;STREET;BUILDING;BUILD_SECT;APARTMENT;UNSTRUCT_INFO;RECORD_ACTION'
FROM abonents
union all
select  
    --fo.op_id || ';' || fo.abonent_id || ';' || -- для дебага
    a.operator_id|| ';' || --region_id
    case 
        when (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%') then '80' --80 - через банковский перевод
        when (fo.descr like 'Карта оплаты %') then '81'  --81 - через карты экспресс-оплаты
        when (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) like '%OSMP%') then '82' --82 - через терминалы моментальных платежей
        when (fo.is_cash=1 and fo.descr containing 'Вебкасса. Пользователь: ') then '83' --83 - через центры обслуживания клиентов
        when fo.descr like 'Перевод от другого абонента через ЛК%' then '84' --84 - посредством снятия денег со счета другого абонента
        else '86' --86 - если невозможно определить тип платежа, но является пополнением баланса
    end || ';' || -- payment_type
    '1' || ';' || -- pay_type_id - для связи с отчетом pay_type
    SUBSTRING((coalesce(cast(fo.system_date as varchar(32)),'')) FROM 1 FOR 19) || ';' || --payment_date
    cast(fo.op_summa/10000000000.00 as varchar(32)) || ';' || --amount
    cast((fo.op_summa*fo.curs)/10000000000.00 as varchar(32)) || ';' || --amount_CURRENCY
    coalesce(a.sms,'') || ';' || --phone_number
    fo.account_id || ';' || --account
    fo.abonent_id || ';;' || --internal_id1 internal_id2 (пустой)
    iif(fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%',coalesce(b_acc.attribute_value,''),'') || ';' || --bank_account
    iif(fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%',coalesce(b_name.attribute_value,''),'') || ';' ||--bank_name
    iif(fo.descr like 'Карта оплаты %', replace(substring(left(fo.descr,(position('.', fo.descr))) from 14 for 20),'.',''),'') || ';' || --express_card_number
    ';;;;' || --terminal_id--terminal_number --latitude --longitude --projection_type 
    a.operator_id || ';' || --center_id идентификатор центра обслуживания клиентов
    coalesce(a_don.sms,'') || ';' || --donated_phone_number
    coalesce(a_don.account_id,'') || ';' || --donated_account
    coalesce(a_don.id,'') || ';;' || --donated_internal_id1 --donated_internal_id2
    ';;;;;' || --card_number --pay_params --person_recieved --bank_division_name --bank_card_id
    iif(
        (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%') 
        or (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) like '%OSMP%')
        or (fo.is_cash=1 and fo.descr containing 'Вебкасса. Пользователь: '),
            '0;0',
            ';'
    ) || ';' || --address_type_id --address_type если --payment_type in (80,82,83)
    coalesce(h.zip_code,'') || ';' || --zip
    coalesce(h.country,'') || ';' ||--country
    coalesce(h.region,'') || ';' ||--region
    coalesce(h.district,'') || ';' ||--zone
    coalesce(h.city,'') || ';' ||--city
    coalesce(h.street,'') || ';' ||--street
    coalesce(h.s_number,'') || ';' ||--building
    coalesce(h.s_liter,'') || ';' ||--build_sect
    coalesce(a.a_home_number,'') || ';' || --apartment
    '' || ';' || --unstruct_info 
    '1'
from abonents a 
    join finance_operations fo on fo.abonent_id=a.id -- чтобы узнать абонента
        left join homes h on a.home_id=h.id -- получаем адрес абонента для --address_type_id --address_type
    left join attribute_values b_acc on b_acc.attribute_id=6 and b_acc.abonent_id=a.id -- банковский счет абонента
    left join attribute_values b_name on b_name.attribute_id=7 and b_name.abonent_id=a.id -- наименование банка абонента
    left join finance_operations fo_don on fo.related_fin_op_id=fo_don.op_id and fo.descr like '%Перевод от другого абонента через ЛК%' -- для --payment_type=86 (переводов денег между абонентами)
        left join abonents a_don on fo_don.abonent_id=a_don.id -- для payment_type=86 (переводов денег между абонентами)
where 
    fo.op_type=2
    and A.IS_FOLDER=0
    and a.parent_id not in (244)
    and a.deleted=0
    and a.id>1;

Тот же отчёт за последний день - PAYMENT_PERIODIC.sql

/cfg/sorm3/PAYMENT_PERIODIC.sql

set heading off;
select first 1 'REGION_ID;PAYMENT_TYPE;PAY_TYPE_ID;PAYMENT_DATE;AMOUNT;AMOUNT_CURRENCY;PHONE_NUMBER;ACCOUNT;ABONENT_ID;BANK_ACCOUNT;BANK_NAME;EXPRESS_CARD_NUMBER;TERMINAL_ID;TERMINAL_NUMBER;LATITUDE;LONGITUDE;PROJECTION_TYPE;CENTER_ID;DONATED_PHONE_NUMBER;DONATED_ACCOUNT;DONATED_INTERNAL_ID1;DONATED_INTERNAL_ID2;CARD_NUMBER;PAY_PARAMS;PERSON_RECIEVED;BANK_DIVISION_NAME;BANK_CARD_ID;ADDRESS_TYPE_ID;ADDRESS_TYPE;ZIP;COUNTRY;REGION;ZONE;CITY;STREET;BUILDING;BUILD_SECT;APARTMENT;UNSTRUCT_INFO;RECORD_ACTION'

FROM abonents
union all
select  
    --fo.op_id || ';' || fo.abonent_id || ';' || -- для дебага
    a.operator_id|| ';' || --region_id
    case 
        when (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%') then '80' --80 - через банковский перевод
        when (fo.descr like 'Карта оплаты %') then '81'  --81 - через карты экспресс-оплаты
        when (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) like '%OSMP%') then '82' --82 - через терминалы моментальных платежей
        when (fo.is_cash=1 and fo.descr containing 'Вебкасса. Пользователь: ') then '83' --83 - через центры обслуживания клиентов
        when fo.descr like 'Перевод от другого абонента через ЛК%' then '84' --84 - посредством снятия денег со счета другого абонента
        else '86' --86 - если невозможно определить тип платежа, но является пополнением баланса
    end || ';' || -- payment_type
    '1' || ';' || -- pay_type_id - для связи с отчетом pay_type
    SUBSTRING((coalesce(cast(fo.system_date as varchar(32)),'')) FROM 1 FOR 19) || ';' || --payment_date
    cast(fo.op_summa/10000000000.00 as varchar(32)) || ';' || --amount
    cast((fo.op_summa*fo.curs)/10000000000.00 as varchar(32)) || ';' || --amount_CURRENCY
    coalesce(a.sms,'') || ';' || --phone_number
    fo.account_id || ';' || --account
    fo.abonent_id || ';;' || --internal_id1 internal_id2 (пустой)
    iif(fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%',coalesce(b_acc.attribute_value,''),'') || ';' || --bank_account
    iif(fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%',coalesce(b_name.attribute_value,''),'') || ';' ||--bank_name
    iif(fo.descr like 'Карта оплаты %', replace(substring(left(fo.descr,(position('.', fo.descr))) from 14 for 20),'.',''),'') || ';' || --express_card_number
    ';;;;' || --terminal_id--terminal_number --latitude --longitude --projection_type 
    a.operator_id || ';' || --center_id идентификатор центра обслуживания клиентов
    coalesce(a_don.sms,'') || ';' || --donated_phone_number
    coalesce(a_don.account_id,'') || ';' || --donated_account
    coalesce(a_don.id,'') || ';;' || --donated_internal_id1 --donated_internal_id2
    ';;;;;' || --card_number --pay_params --person_recieved --bank_division_name --bank_card_id
    iif(
        (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) not like '%OSMP%') 
        or (fo.operator_id is not null and fo.operator_id<>'' and upper(fo.operator_id) like '%OSMP%')
        or (fo.is_cash=1 and fo.descr containing 'Вебкасса. Пользователь: '),
            '0;0',
            ';'
    ) || ';' || --address_type_id --address_type если --payment_type in (80,82,83)
    coalesce(h.zip_code,'') || ';' || --zip
    coalesce(h.country,'') || ';' ||--country
    coalesce(h.region,'') || ';' ||--region
    coalesce(h.district,'') || ';' ||--zone
    coalesce(h.city,'') || ';' ||--city
    coalesce(h.street,'') || ';' ||--street
    coalesce(h.s_number,'') || ';' ||--building
    coalesce(h.s_liter,'') || ';' ||--build_sect
    coalesce(a.a_home_number,'') || ';' || --apartment
    '' || ';' || --unstruct_info 
    '1'
from abonents a 
    join finance_operations fo on fo.abonent_id=a.id -- чтобы узнать абонента
        left join homes h on a.home_id=h.id -- получаем адрес абонента для --address_type_id --address_type
    left join attribute_values b_acc on b_acc.attribute_id=6 and b_acc.abonent_id=a.id -- банковский счет абонента
    left join attribute_values b_name on b_name.attribute_id=7 and b_name.abonent_id=a.id -- наименование банка абонента
    left join finance_operations fo_don on fo.related_fin_op_id=fo_don.op_id and fo.descr like '%Перевод от другого абонента через ЛК%' -- для --payment_type=86 (переводов денег между абонентами)
        left join abonents a_don on fo_don.abonent_id=a_don.id -- для payment_type=86 (переводов денег между абонентами)
where 
    fo.op_type=2
    and A.IS_FOLDER=0
    and a.parent_id not in (244)
    and a.deleted=0
    and a.id>1
    and
    ((fo.op_date between  dateadd (-1 hour to current_timestamp) and current_timestamp) or ((fo.operator_name='' or fo.operator_name is null) and (fo.SYSTEM_DATE between  dateadd (-1 hour to current_timestamp) and current_timestamp)));

/cfg/sorm3/SUPPLEMENTARY_SERVICE.sql

set heading off;
select first 1 'ID;MNEMONIC;BEGIN_TIME;END_TIME;DESCRIPTION;REGION_ID'
from abonents
union all
select
abs(usl.id) || ';' ||
usl.NAME || ';' ||
coalesce((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,'') || ';' ||
coalesce(usl.OPERATOR_ID,'')

	from
	usluga usl
	where
	usl.system_type != 13 and
	usl.id!=170000 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
	(usl.name is not null and usl.name <> '')
	and usl.id>0;

Тот же отчёт за последний день - SUPPLEMENTARY_SERVICE_PERIODIC.sql

/cfg/sorm3/SUPPLEMENTARY_SERVICE_PERIODIC.sql

set heading off; select first 1 'ID;MNEMONIC;BEGIN_TIME;END_TIME;DESCRIPTION;REGION_ID'
from abonents
union all
select
abs(usl.id) || ';' ||
usl.NAME || ';' ||
coalesce((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,'') || ';' ||
coalesce(usl.OPERATOR_ID,'')

        from
        usluga usl
        join (select distinct usluga_id uid
                from usluga_history
                where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp) usluga_periodic on usl.id=usluga_periodic.uid
        where
        usl.system_type != 13 and
        usl.id!=170000 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
        (usl.name is not null and usl.name <> '')
        and usl.id>0;

/cfg/sorm3/REGIONS.sql

set heading off;
select first 1 'id;begin_time;end_time;description;MCC;MNC'
from abonents
union all
select 
a.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*/
a.name ||  ';' ||
'' ||  ';' ||
''
from abonents a
where a.CATEGORY_ID=2
and a.is_folder=0
and a.deleted=0;

/cfg/sorm3/IP_GATEWAY.sql

-- Отключает заголовки на каждые 10 строк, 
-- они добавляются отдельным запросом в формате CSV
set heading off;
-- Запрос добавляет заголовок
select first 1 
'GATE_ID;IP_TYPE;IPV4;IPV6;IP_PORT;REGION_ID'
from nas 
union all
-- Запрос выгружает NAS internet 
select 
    nas.id || ';' || /* gate_id */
    '0' || ';' ||  /* ip_type, для IPv4 = 0 */
    UF_IP2HEX(ip) || ';' || /* ipv4 */
    '' || ';' || /* ipv6 */
    '0000' || ';' || /* ip_port */
    '3' /* region_id */
from 
    nas
join 
    nas_type
    on nas.nas_type = nas_type.id
where 
    nas.id >= 1000 /* Исключаем системные и тестовые NAS */
    and nas_type.object_type = 1 /* только NAS internet */
;

/cfg/sorm3/IP_PLAN.sql

-- Отключает заголовки на каждые 10 строк, 
-- они добавляются отдельным запросом в формате CSV
set heading off;
-- Запрос добавляет заголовок
select first 1 
'DESCRIPTION;IP_TYPE;IPV4;IPV6;IP_MASK_TYPE;IPV4_MASK;IPV6_MASK;BEGIN_TIME;END_TIME;REGION_ID'
from nas 
union all
-- Запрос выгружает план IP адресации
select
    ip_pull.name || ';' || /* DESCRIPTION */
    '0' || ';' || /* ip_type, для IPv4 = 0 */
    uf_ip2hex(ip_pull.network_address) || ';' || /* IPV4 */
    '' || ';' || /* IPV6 */
    '0' || ';' || /* ip_mask_type, для IPv4 = 0 */
    case 
        when coalesce(ip_pull.mask,24) = 0 then upper('00000000')
        when coalesce(ip_pull.mask,24) = 1 then upper('80000000')
        when coalesce(ip_pull.mask,24) = 2 then upper('c0000000')
        when coalesce(ip_pull.mask,24) = 3 then upper('e0000000')
        when coalesce(ip_pull.mask,24) = 4 then upper('f0000000')
        when coalesce(ip_pull.mask,24) = 5 then upper('f8000000')
        when coalesce(ip_pull.mask,24) = 6 then upper('fc000000')
        when coalesce(ip_pull.mask,24) = 7 then upper('fe000000')
        when coalesce(ip_pull.mask,24) = 8 then upper('ff000000')
        when coalesce(ip_pull.mask,24) = 9 then upper('ff800000')
        when coalesce(ip_pull.mask,24) = 10 then upper('ffc00000')
        when coalesce(ip_pull.mask,24) = 11 then upper('ffe00000')
        when coalesce(ip_pull.mask,24) = 12 then upper('fff00000')
        when coalesce(ip_pull.mask,24) = 13 then upper('fff80000')
        when coalesce(ip_pull.mask,24) = 14 then upper('fffc0000')
        when coalesce(ip_pull.mask,24) = 15 then upper('fffe0000')
        when coalesce(ip_pull.mask,24) = 16 then upper('ffff0000')
        when coalesce(ip_pull.mask,24) = 17 then upper('ffff8000')
        when coalesce(ip_pull.mask,24) = 18 then upper('ffffc000')
        when coalesce(ip_pull.mask,24) = 19 then upper('ffffe000')
        when coalesce(ip_pull.mask,24) = 20 then upper('fffff000')
        when coalesce(ip_pull.mask,24) = 21 then upper('fffff800')
        when coalesce(ip_pull.mask,24) = 22 then upper('fffffc00')
        when coalesce(ip_pull.mask,24) = 23 then upper('fffffe00')
        when coalesce(ip_pull.mask,24) = 24 then upper('ffffff00')
        when coalesce(ip_pull.mask,24) = 25 then upper('ffffff80')
        when coalesce(ip_pull.mask,24) = 26 then upper('ffffffc0')
        when coalesce(ip_pull.mask,24) = 27 then upper('ffffffe0')
        when coalesce(ip_pull.mask,24) = 28 then upper('fffffff0')
        when coalesce(ip_pull.mask,24) = 29 then upper('fffffff8')
        when coalesce(ip_pull.mask,24) = 30 then upper('fffffffc')
        when coalesce(ip_pull.mask,24) = 31 then upper('fffffffe')
        when coalesce(ip_pull.mask,24) = 32 then upper('ffffffff')
        else 'ffffff00'
    end || ';' || /* IPV4_MASK */
    '' || ';' || /* IPV6_MASK */
    '1970-01-01 00:00:00' || ';' || /* BEGIN_TIME */
    '2030-01-01 23:59:59' || ';' || /* END_TIME */
    coalesce(ip_pull.operator_id,3) /* REGION_ID */
from 
    ip_pull
where 
    ip_pull.pull_id >= 1000 /* Отсекаем служебные пулы: DNS-сервера, IP биллинга и т.д. */
;

Pay_type.txt

ID;BEGIN_TIME;END_TIME;DESCRIPTION;REGION_ID
1;01.01.2017 00:00:00;;APELSIN;3
2;01.01.2017 00:00:00;;CITY_PAY;3
3;01.01.2017 00:00:00;;Comepay;3
4;01.01.2017 00:00:00;;Elexnet;3
5;01.01.2017 00:00:00;;ElexnetOld;3
6;01.01.2017 00:00:00;;EXPRESS_VOLGA;3
7;01.01.2017 00:00:00;;ForwardMobile;3
8;01.01.2017 00:00:00;;Gorod;3
9;01.01.2017 00:00:00;;JCC;3
10;01.01.2017 00:00:00;;Kassa24;3
11;01.01.2017 00:00:00;;KAZKOMBANK;3
12;01.01.2017 00:00:00;;Krasplat;3
13;01.01.2017 00:00:00;;MINBANK;3
14;01.01.2017 00:00:00;;MKB_OSMP;3
15;01.01.2017 00:00:00;;Mobilnik;3
16;01.01.2017 00:00:00;;MONEY_YANDEX;3
17;01.01.2017 00:00:00;;NCC;3
18;01.01.2017 00:00:00;;OSMP_Amigo;3
19;01.01.2017 00:00:00;;OSMP_CHECK;3
20;01.01.2017 00:00:00;;OSMP_DELTAPAY;3
21;01.01.2017 00:00:00;;OSMP_EXPRESS_OPLATA;3
22;01.01.2017 00:00:00;;OSMP_INTERPAY;3
23;01.01.2017 00:00:00;;OSMP_MOROZOV;3
24;01.01.2017 00:00:00;;OSMP_QIWI;3
25;01.01.2017 00:00:00;;OSMP-QIWI;3
26;01.01.2017 00:00:00;;OSMP_SBRF;3
27;01.01.2017 00:00:00;;OSMP_SIPAY;3
28;01.01.2017 00:00:00;;PAYANYWAY;3
29;01.01.2017 00:00:00;;PAYCOM_UZ;3
30;01.01.2017 00:00:00;;Payfon_phone;3
31;01.01.2017 00:00:00;;PAYKEEPER;3
32;01.01.2017 00:00:00;;PAYMASTER;3
33;01.01.2017 00:00:00;;PaymasterAutopay;3
34;01.01.2017 00:00:00;;PAYNET_UZ;3
35;01.01.2017 00:00:00;;PayPal;3
36;01.01.2017 00:00:00;;PayPro;3
37;01.01.2017 00:00:00;;QIWI_OSMP;3
38;01.01.2017 00:00:00;;QIWI(REST);3
39;01.01.2017 00:00:00;;Qiwiwallet;3
40;01.01.2017 00:00:00;;QuickPay;3
41;01.01.2017 00:00:00;;RAPIDA;3
42;01.01.2017 00:00:00;;Robokassa;3
43;01.01.2017 00:00:00;;RPS;3
44;01.01.2017 00:00:00;;SBERBANK_ACQ;3
45;01.01.2017 00:00:00;;SBRF;3
46;01.01.2017 00:00:00;;SBRF_BS;3
47;01.01.2017 00:00:00;;SBRF_KIBERPLAT;3
48;01.01.2017 00:00:00;;SFOUR;3
49;01.01.2017 00:00:00;;Simfoniya;3
50;01.01.2017 00:00:00;;TelePay;3
51;01.01.2017 00:00:00;;TINKOFF;3
52;01.01.2017 00:00:00;;UBRIR;3
53;01.01.2017 00:00:00;;UCS_ACQ;3
54;01.01.2017 00:00:00;;Unikassa;3
55;01.01.2017 00:00:00;;Uniteller;3
56;01.01.2017 00:00:00;;UnitellerAutopay;3
57;01.01.2017 00:00:00;;VISA-MASTERCARD;3
58;01.01.2017 00:00:00;;webcreds;3
59;01.01.2017 00:00:00;;WebRobokassa;3
60;01.01.2017 00:00:00;;XPLAT;3
61;01.01.2017 00:00:00;;YAPK;3
62;01.01.2017 00:00:00;;Поступление наличных;3

DOC_TYPE.txt

DOC_TYPE_ID;BEGIN_TIME;END_TIME;DESCRIPTION;REGION_ID
1;1970-01-01 00:00:00;2030-01-01 23:59:59;Паспорт гражданина РФ;3
2;1970-01-01 00:00:00;2030-01-01 23:59:59;Военный билет;3
3;1970-01-01 00:00:00;2030-01-01 23:59:59;Временное удостоверение личности гражданина РФ;3
4;1970-01-01 00:00:00;2030-01-01 23:59:59;Иностранный паспорт;3
5;1970-01-01 00:00:00;2030-01-01 23:59:59;Паспорт гражданина СССР;3
6;1970-01-01 00:00:00;2030-01-01 23:59:59;Свидетельство о регистрации ходатайства иммигранта;3
7;1970-01-01 00:00:00;2030-01-01 23:59:59;Свидетельство о рождении;3
8;1970-01-01 00:00:00;2030-01-01 23:59:59;Справка об освобождении из мест лишения свободы;3
9;1970-01-01 00:00:00;2030-01-01 23:59:59;Удостоверение беженца в РФ;3
10;1970-01-01 00:00:00;2030-01-01 23:59:59;Удостоверение личности офицера;3
11;1970-01-01 00:00:00;2030-01-01 23:59:59;Вид на жительство в РФ иностранных граждан;3
12;1970-01-01 00:00:00;2030-01-01 23:59:59;Загранпаспорт гражданина СССР;3
13;1970-01-01 00:00:00;2030-01-01 23:59:59;Загранпаспорт гражданина РФ;3
14;1970-01-01 00:00:00;2030-01-01 23:59:59;Паспорт Минморфлота;3
15;1970-01-01 00:00:00;2030-01-01 23:59:59;Дипломатический паспорт гражданина РФ;3
16;1970-01-01 00:00:00;2030-01-01 23:59:59;Удостоверение личности;3
17;1970-01-01 00:00:00;2030-01-01 23:59:59;Свидетельство о рождении выданное уполномоченным органом иностранного государства;3
18;1970-01-01 00:00:00;2030-01-01 23:59:59;Паспорт моряка;3
19;1970-01-01 00:00:00;2030-01-01 23:59:59;Иные документы выданные МВД;3
20;1970-01-01 00:00:00;2030-01-01 23:59:59;Неизвестно;3

Скрипты выгрузки

Скрипт первоначальной выгрузки /app/asr_billing/cfg/sorm3/initial-unloaded-data.sh

#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log

ftpip=10.10.8.6
ftplogin=login
ftppass=password
ftpdirectory=/
file_date=$(date +%Y%m%d_%H%M)
printf '\n\n' >>$LOGFILE 2>&1
date >>$LOGFILE 2>&1

chroot /app/asr_billing python2.7 /usr/lib/python2.7/site-packages/python_tools/sorm_report.py abonents 1 /cfg/sorm3/data/ABONENT_$file_date.txt >>$LOGFILE 2>&1
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/ABONENT.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ABONENT_IDENT.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_IDENT.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/ABONENT_IDENT.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ABONENT_SRV.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_SRV.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/ABONENT_SRV.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ABONENT_ADDR.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_ADDR.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/ABONENT_ADDR.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ABONENT_USER.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_USER.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/ABONENT_USER.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/PAYMENT.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/PAYMENT.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/PAYMENT.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/REGIONS.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/REGIONS_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/REGIONS_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/REGIONS_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/REGIONS_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/REGIONS_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/REGIONS.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/REGIONS_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/REGIONS.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/REGIONS_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/SUPPLEMENTARY_SERVICE.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/SUPPLEMENTARY_SERVICE.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/IP_GATEWAY.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/IP_GATEWAY_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/IP_GATEWAY_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/IP_GATEWAY_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/IP_GATEWAY_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/IP_GATEWAY_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/IP_GATEWAY.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/IP_GATEWAY_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/IP_GATEWAY.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/IP_GATEWAY_$file_date.txt >>$LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/IP_PLAN.sql | sed '/^$/d' | sed 's/   *//g' >/app/asr_billing/cfg/sorm3/data/IP_PLAN_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/IP_PLAN_$file_date.txt ]; then
    printf 'Метаданные файла\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/IP_PLAN_$file_date.txt >>$LOGFILE 2>&1
    printf 'Количество строк в файле: ' >>$LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/IP_PLAN_$file_date.txt | wc -l >>$LOGFILE 2>&1
    cp /app/asr_billing/cfg/sorm3/data/IP_PLAN_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/IP_PLAN.txt-$(date +"%Y%m%d_%H%M%S")
    curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/IP_PLAN_$file_date.txt ftp://${ftpip}${ftpdirectory} >>$LOGFILE 2>&1
    >|/app/asr_billing/cfg/sorm3/data/IP_PLAN.txt
    printf 'Проверим что файл очищен\n' >>$LOGFILE 2>&1
    ls -l /app/asr_billing/cfg/sorm3/data/IP_PLAN_$file_date.txt >>$LOGFILE 2>&1
fi

Скрипт для ежедневной выгрузки /app/asr_billing/cfg/sorm3/day-unload-data.sh

#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log

ftpip=10.10.8.6
ftplogin=login
ftppass=password
ftpdirectory=/
file_date=$(date +%Y%m%d_%H%M)
printf '\n\n' >> $LOGFILE 2>&1
date >> $LOGFILE 2>&1




chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i  /cfg/sorm3/ABONENTS_PERIODIC.sql | sed '/^$/d'| sed 's/   *//g'  > /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT.txt-`date +"%Y%m%d_%H%M%S"`
        curl -v -sS --user $ftplogin:$ftppass -T  /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt ftp://${ftpip}${ftpdirectory} >> $LOGFILE 2>&1
        >| /app/asr_billing/cfg/sorm3/data/ABONENT.txt
        printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_$file_date.txt >> $LOGFILE 2>&1
fi





chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i  /cfg/sorm3/ABONENT_IDENT_PERIODIC.sql | sed '/^$/d'| sed 's/   *//g'  > /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_IDENT.txt-`date +"%Y%m%d_%H%M%S"`
        curl -v -sS --user $ftplogin:$ftppass -T  /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt ftp://${ftpip}${ftpdirectory} >> $LOGFILE 2>&1
        >| /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT.txt
        printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.txt >> $LOGFILE 2>&1
fi




chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i  /cfg/sorm3/ABONENT_SRV_PERIODIC.sql | sed '/^$/d'| sed 's/   *//g'  > /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_SRV.txt-`date +"%Y%m%d_%H%M%S"`
        curl -v -sS --user $ftplogin:$ftppass -T  /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt ftp://${ftpip}${ftpdirectory} >> $LOGFILE 2>&1
        >| /app/asr_billing/cfg/sorm3/data/ABONENT_SRV.txt
        printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_SRV_$file_date.txt >> $LOGFILE 2>&1
fi





chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i  /cfg/sorm3/ABONENT_ADDR_PERIODIC.sql | sed '/^$/d'| sed 's/   *//g'  > /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_ADDR.txt-`date +"%Y%m%d_%H%M%S"`
        curl -v -sS --user $ftplogin:$ftppass -T  /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt ftp://${ftpip}${ftpdirectory} >> $LOGFILE 2>&1
        >| /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR.txt
        printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_ADDR_$file_date.txt >> $LOGFILE 2>&1
fi





chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i  /cfg/sorm3/ABONENT_USER_PERIODIC.sql | sed '/^$/d'| sed 's/   *//g'  > /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/ABONENT_USER.txt-`date +"%Y%m%d_%H%M%S"`
        curl -v -sS --user $ftplogin:$ftppass -T  /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt ftp://${ftpip}${ftpdirectory} >> $LOGFILE 2>&1
        >| /app/asr_billing/cfg/sorm3/data/ABONENT_USER.txt
        printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_USER_$file_date.txt >> $LOGFILE 2>&1
fi





chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i  /cfg/sorm3/SUPPLEMENTARY_SERVICE_PERIODIC.sql | sed '/^$/d'| sed 's/   *//g'  > /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/SUPPLEMENTARY_SERVICE.txt-`date +"%Y%m%d_%H%M%S"`
        curl -v -sS --user $ftplogin:$ftppass -T  /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt ftp://${ftpip}${ftpdirectory} >> $LOGFILE 2>&1
        >| /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE.txt
        printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/SUPPLEMENTARY_SERVICE_$file_date.txt >> $LOGFILE 2>&1
fi

Скрипт ежечасной выгрузки /app/asr_billing/cfg/sorm3/hour-unload-data.sh

#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log

ftpip=10.10.8.6
ftplogin=login
ftppass=password
ftpdirectory=/
file_date=$(date +%Y%m%d_%H%M)
printf '\n\n' >> $LOGFILE 2>&1
date >> $LOGFILE 2>&1




chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i  /cfg/sorm3/PAYMENT_PERIODIC.sql | sed '/^$/d'| sed 's/   *//g'  > /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt
if [ -s /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt /app/asr_billing/cfg/sorm3/data/done/PAYMENT.txt-`date +"%Y%m%d_%H%M%S"`
        curl -v -sS --user $ftplogin:$ftppass -T  /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt ftp://${ftpip}${ftpdirectory} >> $LOGFILE 2>&1
        >| /app/asr_billing/cfg/sorm3/data/PAYMENT.txt
        printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/PAYMENT_$file_date.txt >> $LOGFILE 2>&1
fi

Отладка

Путь до журнала выгрузки

/app/asr_billing/var/log/sorm-upload.log

Выгрузка не работает, что можно сделать?

Запустите скрипт выгузки с повышенным логированием, например:

bash -x /app/asr_billing/cfg/sorm3/initial-unloaded-data.sh

Если возникнут какие-то ошибки - вы их увидите и можете попробовать решить используя открытую документацию по bash и SQL в интернете.

Пример ошибки: "ошибка синтаксиса: неожиданный конец файла

Проблема может быть в том, что файлы сохранены редактором в Windows, с символом "конца строки" и "переноса корретки", а в Linux должен быть только "конец строки" (эта тема хорошо раскрыта в Википедии )

Быстро исправить все файлы можно простым скриптом:

for file in `ls /app/asr_billing/cfg/sorm3/`; do sed 's/\r$//g' -i /app/asr_billing/cfg/sorm3/$file; done
Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.