|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Комментарий:
Изменения (14)
просмотр истории страницы... {toc} h1. Выгрузка на СОРМ3 h2. Принцип работы Для интеграции с СОРМ3 Специальные технологии требуется произвести первичную выгрузку данных, а так же добавить сценарии планировщика задач, которые будут выгружать добавленные и изменённые данные с определённой периодичностью При изменении списка используемых документов или плана ip-адрессации, измените соответствущие *csv* файлы и повторно выполните скрипт *initial-static-data.sh*. При выгрузке изменённого *pay-types.csv* убедитесь, что не перепутались id платежных систем - в таком случае файл нужно будет исправить вручную. h1. Отчёты h2. /cfg/sorm3/ABONENTS.sql {code} 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 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; {code} h5. Тот же отчёт за последний день. */cfg/sorm3/ABONENTS_PERIODIC.sql* {code} 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 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 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)); {code} h2. /cfg/sorm3/ABONENT_ADDR.sql {code} 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; {code} h5. Тот же отчёт за последний день */cfg/sorm3/ABONENT_ADDR_PERIODIC.sql* {code} 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 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)) 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 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)) 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 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)); {code} h2. /cfg/sorm3/ABONENT_IDENT.sql {code} 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; {code} h5. Тот же отчёт за последний день */cfg/sorm3/ABONENT_IDENT_PERIODIC.sql* {code} 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 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)); {code} h2. /cfg/sorm3/ABONENT_SRV.sql {code} 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; {code} h5. Тот же отчёт за последний день */cfg/sorm3/ABONENT_SRV_PERIODIC.sql* {code} 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 and uu.id in (select distinct users_usluga_id from users_usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp); {code} h2. /cfg/sorm3/ABONENT_USER.sql {code} 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; {code} h5. Тот же отчёт за последний день */cfg/sorm3/ABONENT_USER_PERIODIC.sql* {code} 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 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)); {code} h2. /cfg/sorm3/PAYMENT.sql {code} 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 'Карта оплаты %', substring(fo.descr replace(substring(left(fo.descr,(position('.', fo.descr))) from 14 for (position('.', fo.descr)-14)),'') 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 finance_operations fo |
| from abonents a |
| join abonents a 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=fo.abonent_id b_acc.abonent_id=a.id -- банковский счет абонента |
| left join attribute_values b_name on b_acc.attribute_id=7 b_name.attribute_id=7 and b_acc.abonent_id=fo.abonent_id b_name.abonent_id=a.id -- наименование банка абонента |
| --left join abonents a_perevod on a_perevod.contract_number=substring(fo.descr from 39 for (position(' (', fo.descr))-39) and fo.descr like '%Перевод от другого абонента через ЛК%' |
| 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; {code} h5. Тот же отчёт за последний час */cfg/sorm3/PAYMENT_PERIODIC.sql* {code} 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 'Карта оплаты %', substring(fo.descr replace(substring(left(fo.descr,(position('.', fo.descr))) from 14 for (position('.', fo.descr)-14)),'') 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 finance_operations fo |
| from abonents a |
| join abonents a 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=fo.abonent_id b_acc.abonent_id=a.id -- банковский счет абонента |
| left join attribute_values b_name on b_acc.attribute_id=7 b_name.attribute_id=7 and b_acc.abonent_id=fo.abonent_id b_name.abonent_id=a.id -- наименование банка абонента |
| --left join abonents a_perevod on a_perevod.contract_number=substring(fo.descr from 39 for (position(' (', fo.descr))-39) and fo.descr like '%Перевод от другого абонента через ЛК%' |
| 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))); {code} h2. Pay_type.txt {code} 1;1;01.01.2017 00:00:00;;APELSIN 1;2;01.01.2017 00:00:00;;CITY_PAY 1;3;01.01.2017 00:00:00;;Comepay 1;4;01.01.2017 00:00:00;;Elexnet 1;5;01.01.2017 00:00:00;;ElexnetOld 1;6;01.01.2017 00:00:00;;EXPRESS_VOLGA 1;7;01.01.2017 00:00:00;;ForwardMobile 1;8;01.01.2017 00:00:00;;Gorod 1;9;01.01.2017 00:00:00;;JCC 1;10;01.01.2017 00:00:00;;Kassa24 1;11;01.01.2017 00:00:00;;KAZKOMBANK 1;12;01.01.2017 00:00:00;;Krasplat 1;13;01.01.2017 00:00:00;;MINBANK 1;14;01.01.2017 00:00:00;;MKB_OSMP 1;15;01.01.2017 00:00:00;;Mobilnik 1;16;01.01.2017 00:00:00;;MONEY_YANDEX 1;17;01.01.2017 00:00:00;;NCC 1;18;01.01.2017 00:00:00;;OSMP_Amigo 1;19;01.01.2017 00:00:00;;OSMP_CHECK 1;20;01.01.2017 00:00:00;;OSMP_DELTAPAY 1;21;01.01.2017 00:00:00;;OSMP_EXPRESS_OPLATA 1;22;01.01.2017 00:00:00;;OSMP_INTERPAY 1;23;01.01.2017 00:00:00;;OSMP_MOROZOV 1;24;01.01.2017 00:00:00;;OSMP_QIWI 1;25;01.01.2017 00:00:00;;OSMP-QIWI 1;26;01.01.2017 00:00:00;;OSMP_SBRF 1;27;01.01.2017 00:00:00;;OSMP_SIPAY 1;28;01.01.2017 00:00:00;;PAYANYWAY 1;29;01.01.2017 00:00:00;;PAYCOM_UZ 1;30;01.01.2017 00:00:00;;Payfon_phone 1;31;01.01.2017 00:00:00;;PAYKEEPER 1;32;01.01.2017 00:00:00;;PAYMASTER 1;33;01.01.2017 00:00:00;;PaymasterAutopay 1;34;01.01.2017 00:00:00;;PAYNET_UZ 1;35;01.01.2017 00:00:00;;PayPal 1;36;01.01.2017 00:00:00;;PayPro 1;37;01.01.2017 00:00:00;;QIWI_OSMP 1;38;01.01.2017 00:00:00;;QIWI(REST) 1;39;01.01.2017 00:00:00;;Qiwiwallet 1;40;01.01.2017 00:00:00;;QuickPay 1;41;01.01.2017 00:00:00;;RAPIDA 1;42;01.01.2017 00:00:00;;Robokassa 1;43;01.01.2017 00:00:00;;RPS 1;44;01.01.2017 00:00:00;;SBERBANK_ACQ 1;45;01.01.2017 00:00:00;;SBRF 1;46;01.01.2017 00:00:00;;SBRF_BS 1;47;01.01.2017 00:00:00;;SBRF_KIBERPLAT 1;48;01.01.2017 00:00:00;;SFOUR 1;49;01.01.2017 00:00:00;;Simfoniya 1;50;01.01.2017 00:00:00;;TelePay 1;51;01.01.2017 00:00:00;;TINKOFF 1;52;01.01.2017 00:00:00;;UBRIR 1;53;01.01.2017 00:00:00;;UCS_ACQ 1;54;01.01.2017 00:00:00;;Unikassa 1;55;01.01.2017 00:00:00;;Uniteller 1;56;01.01.2017 00:00:00;;UnitellerAutopay 1;57;01.01.2017 00:00:00;;VISA-MASTERCARD 1;58;01.01.2017 00:00:00;;webcreds 1;59;01.01.2017 00:00:00;;WebRobokassa 1;60;01.01.2017 00:00:00;;XPLAT 1;61;01.01.2017 00:00:00;;YAPK 1;62;01.01.2017 00:00:00;;Поступление наличных {code} h2. /cfg/sorm3/REGIONS.sql {code} 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; {code} h2. SUPPLEMENTARY_SERVICE.sql {code} 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; {code} h1. Скрипты выгрузки h2. Скрипт первоначальной выгрузки /app/asr_billing/cfg/sorm3/initial-unloaded-data.sh {code} #/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 {code} h2. Скрипт для ежедневной выгрузки /app/asr_billing/cfg/sorm3/day-unload-data.sh {code} #/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 {code} h2. Скрипт ежечасной выгрузки /app/asr_billing/cfg/sorm3/hour-unload-data.sh {code} #/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 {code} h1. Отладка Путь до журнала выгрузки {code}/app/asr_billing/var/log/sorm-upload.log{code} h2. Выгрузка не работает, что можно сделать? h3. Запустите скрипт выгузки с повышенным логированием, например: {code}bash -x /app/asr_billing/cfg/sorm3/initial-unloaded-data.sh{code} Если возникнут какие-то ошибки - вы их увидите и можете попробовать решить используя открытую документацию по bash и SQL в интернете. h3. Пример ошибки: "ошибка синтаксиса: неожиданный конец файла Проблема может быть в том, что файлы сохранены редактором в Windows, с символом "конца строки" и "переноса корретки", а в Linux должен быть только "конец строки" (эта тема хорошо раскрыта [в Википедии |https://ru.wikipedia.org/wiki/Перевод_строки]) Быстро исправить все файлы можно простым скриптом: {code}for file in `ls /app/asr_billing/cfg/sorm3/`; do sed 's/\r$//g' -i /app/asr_billing/cfg/sorm3/$file; done{code} |