... {toc}
h1. Выгрузка на СОРМ3
h2. Принцип работы
Для интеграции с СОРМ3 Норси-Транс требуется произвести первичную выгрузку данных, а так же добавить сценарии планировщика задач, которые будут выгружать добавленные и изменённые данные с определённой периодичностью
При изменении списка используемых документов или плана ip-адрессации, измените соответствущие *csv* файлы и повторно выполните скрипт *initial-static-data.sh*. При выгрузке изменённого *pay-types.csv* убедитесь, что не перепутались id платежных систем - в таком случае файл нужно будет исправить вручную.
h1. Отчёты
h2. Abonents.sql
{code}
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}
h2. ABONENT_ADDR.sql
{code}
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}
h2. 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
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;
{code}
h2. Abonent_srv.sql
{code}
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}
h2. Abonent_user.sql
{code}
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
|
h2. Payment.sql
{code}
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 from 14 for (position('.', fo.descr)-14)),'') || ';' || --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
join abonents a 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 -- банковский счет абонента
left join attribute_values b_name on b_acc.attribute_id=7 and b_acc.abonent_id=fo.abonent_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}
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. Regions.sql
{code}
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}
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}
|
