Интеграция с СОРМ 3 Январь компании МФИ СОРМ

Skip to end of metadata
Go to start of metadata
Вы просматриваете старую версию данной страницы. Смотрите текущую версию. Сравнить с текущим  |   просмотр истории страницы
<< Предыдущий Версия 26 Следующий >>
Структура отчёта ABONENTS

Для автоматического выполнения скрипта должна быть создана структура каталогов в контейнере asr_billing следующего формата: /cfg/sorm3/data/done/
Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonents.sql

set heading off;
select distinct '"' || 
A.ID || '";"' || 
A.OPERATOR_ID || '";"' ||
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' ||  '";"' || 
A.CONTRACT_NUMBER  || '";"' || 
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' ' || CAST(lpad(EXTRACT(HOUR FROM a.create_date),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM a.create_date),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM a.create_date)),0),2,'0') AS varchar(2)) || '";"' ||
CAST(lpad(EXTRACT(DAY FROM CURRENT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM CURRENT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM CURRENT_DATE) || ' ' || CAST(lpad(EXTRACT(HOUR FROM CURRENT_TIMESTAMP),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM CURRENT_TIMESTAMP),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM CURRENT_TIMESTAMP)),0),2,'0') AS varchar(2)) || '";"' || 

case when a.company=0 then

'"42";"1";"";"";"";"' || A.NAME || '";"' || coalesce(av22.attribute_value,'') || '";"' || coalesce(av32.attribute_value,'') || '";"0";' || coalesce(av14.attribute_value,'') || '";"' || coalesce(av13.attribute_value,'') || '";"' ||  coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";"";"";"";"";"' || 
case when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then	'1' else '0' end || '";"' || 
CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || '00:00:00"' else 

'"43";"";"";"";"";"";"";"";"";"";"";"";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";"' || A.NAME || '";"' || coalesce(av4.attribute_value,'') || '";"' || coalesce(av29.attribute_value,'') || '";"' || coalesce(av1.attribute_value,'') || '";"' || 
case when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then	'1' else '0' end || '";"' || 
CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || '00:00:00"' 



end 

from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av13 on a.id=av13.ABONENT_ID and av13.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av14 on a.id=av14.ABONENT_ID and av14.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av16 on a.id=av16.ABONENT_ID and av16.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av17 on a.id=av17.ABONENT_ID and av17.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join ATTRIBUTE_VALUES as av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av29 on a.id=av29.ABONENT_ID and av29.ATTRIBUTE_ID=29
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join abonents_block ab on ab.abonent_id=a.id
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.deleted=0	
and a.id is not null;
Структура отчёта ABONENT_ADDRESS

Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonent_address.sql

set heading off;
select distinct
     '"' || A.ID || '";"' ||
      case 
      when a.company=1 then  coalesce(UADR.ATTRIBUTE_VALUE,' ')
      when a.company=0 then coalesce(PPROPRISKA.ATTRIBUTE_VALUE,' ')
      end || '";"' ||
      coalesce(POCHADR.ATTRIBUTE_VALUE,' ') || '";"' ||
      case 
      when a.company=1 then  coalesce(PHADR.ATTRIBUTE_VALUE,' ')
      when a.company=0 then ' '
      end || '";"' ||
      coalesce( h.STREET || ', д. ' ||  h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER,'') || '";"' ||
      '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,'')  || '"'
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;
Структура отчёта ABONENT_IDENT

Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonent_ident.sql

set heading off;
select distinct
'"' || A.ID || '";"";"";"";"";"";"";"";"";"";"' || coalesce(u.mac,'') || '";"";"";"' || coalesce(u.login,'') || '";"' || coalesce(A.EMAIL,'') || '";"";"";"";"";"0";"' || coalesce(uf_ip2string(u.ip),'') ||  '";"' ||  ip.MASK || '";"' || uf_ip2string(ip.START_IP) || '";"' || uf_ip2string(ip.END_IP) ||'";"";"";"";"' ||  CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || ' 00:00:00"' || ';"' || IIF(ah.time_del is not null ,  CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') || '";"";"";"";"";""'
from abonents a
left join USERS U on A.ID = U.ABONENT_ID
left join abonents_history ah on ah.abonents_id=a.id
left join ip_pull ip on u.PULL_ID=ip.PULL_ID
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.id is not null;
Структура отчёта ABONENT_SERVICE

Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonent_service.sql

set heading off;
select distinct
'"' || A.ID || '";"' ||
A.OPERATOR_ID || '";"' || 
UU.USLUGA_ID  || '";"' || 
CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE) || ' ' || CAST(lpad(EXTRACT(HOUR FROM UU.ENABLE_DATE),2,'0') AS varchar(2))  || ':' ||  CAST(lpad(EXTRACT(MINUTE FROM UU.ENABLE_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM UU.ENABLE_DATE)),0),2,'0') AS varchar(2))  || '";"' || 
' '  || '";"' || 
' '  || '";"' || 
' '  || '";"' || 
' '  || '";"' 
from abonents a
left join users_usluga UU on uu.abonent_id=a.id
left join usluga u on u.id=uu.usluga_id
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.id is not null
and u.deleted=0
and a.deleted=0
and uu.enabled=1
and uu.deleted=0;
Структура отчёта TRANSFER_REMOVAL_BANK_PAYMENT
set heading off; select
    '"86";"' ||
    coalesce(case
		when fo.operator_name = 'APELSIN' then '1'
		when fo.operator_name = 'CITY_PAY' then '2'
		when fo.operator_name = 'EXPRESS_VOLGA' then '3'
		when fo.operator_name = 'ForwardMobile' then '4'
		when fo.operator_name = 'Gorod' then '5'
		when fo.operator_name = 'JCC' then '6'
		when fo.operator_name = 'Kassa24' then '7'
		when fo.operator_name = 'KAZKOMBANK' then '8'
		when fo.operator_name = 'Krasplat' then '9'
		when fo.operator_name = 'MINBANK' then '10'
		when fo.operator_name = 'MKB_OSMP' then '11'
		when fo.operator_name = 'OSMP_Amigo' then '12'
		when fo.operator_name = 'OSMP_CHECK' then '13'
		when fo.operator_name = 'OSMP_DELTAPAY' then '14'
		when fo.operator_name = 'OSMP_EXPRESS_OPLATA' then '15'
		when fo.operator_name = 'OSMP_INTERPAY' then '16'
		when fo.operator_name = 'OSMP_MOROZOV' then '17'
		when fo.operator_name = 'OSMP_QIWI' then '18'
		when fo.operator_name = 'OSMP-QIWI' then '19'
		when fo.operator_name = 'OSMP_SBRF' then '20'
		when fo.operator_name = 'OSMP_SIPAY' then '21'
		when fo.operator_name = 'PAYANYWAY' then '22'
		when fo.operator_name = 'PAYCOM_UZ' then '23'
		when fo.operator_name = 'Payfon_phone' then '24'
		when fo.operator_name = 'PAYKEEPER' then '25'
		when fo.operator_name = 'PAYMASTER' then '26'
		when fo.operator_name = 'PAYNET_UZ' then '27'
		when fo.operator_name = 'PayPal' then '28'
		when fo.operator_name = 'PayPro' then '29'
		when fo.operator_name = 'QIWI_OSMP' then '30'
		when fo.operator_name = 'QIWI(REST)' then '31'
		when fo.operator_name = 'Qiwiwallet' then '32'
		when fo.operator_name = 'RAPIDA' then '33'
		when fo.operator_name = 'Robokassa' then '34'
		when fo.operator_name = 'SBERBANK_ACQ' then '35'
		when fo.operator_name = 'SBRF' then '36'
		when fo.operator_name = 'SBRF_BS' then '37'
		when fo.operator_name = 'SBRF_KIBERPLAT' then '38'
		when fo.operator_name = 'SFOUR' then '39'
		when fo.operator_name = 'Simfoniya' then '40'
		when fo.operator_name = 'TelePay' then '41'
		when fo.operator_name = 'TINKOFF' then '42'
		when fo.operator_name = 'UBRIR' then '43'
		when fo.operator_name = 'UCS_ACQ' then '44'
		when fo.operator_name = 'Uniteller' then '45'
		when fo.operator_name = 'UnitellerAutopay' then '46'
		when fo.operator_name = 'VISA-MASTERCARD' then '47'
		when fo.operator_name = 'WebRobokassa' then '48'
		when fo.operator_name = 'XPLAT' then '49'
		when fo.operator_name = 'YAPK' then '50'
		when (fo.operator_name = '' or fo.operator_name is null) then '51'
		else '51'
    end,'-')
        || '";"' ||
    iif(fo.operator_name<>'' and fo.operator_name is not null,
        CAST(lpad(EXTRACT(DAY FROM  fo.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  fo.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  fo.OP_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM  fo.OP_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM  fo.OP_DATE),2,'0') AS varchar(2)),
        CAST(lpad(EXTRACT(DAY FROM  fo.SYSTEM_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  fo.system_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  fo.system_date) || ' ' || CAST(lpad(EXTRACT(hour FROM  fo.system_date),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM  fo.system_date),2,'0') AS varchar(2))
    )||':'|| '00";"' ||
    cast(fo.op_summa/10000000000.00 as varchar(40)) || '";"' ||
    cast(fo.op_summa/10000000000.00 as varchar(40)) || '";"";"' ||
    a.sms || '";"' ||
    fo.account_id || '";"' ||
    fo.abonent_id || '";"";"' ||
    a.operator_id || '"'
from
        finance_operations fo
join
        abonents a
        on fo.abonent_id = a.id
where
    fo.abonent_id is not null and
    fo.op_type = 2 and
    fo.storno =  0 and
    a.deleted = 0 and
	a.parent_id not in (244)
order by fo.op_date;

Автоматическая выгрузка

Для автоматического выполнения скрипта должна быть создана структура каталогов в контейнере asr_billing следующего формата: /cfg/sorm3/data/done/

В данном примере используются следующие параметры:
10.30.1.60 - ip адрес ftp сервера
carbon - логин ftp сервера
carbon4 - пароль ftp сервера
Скрипт выгрузки всех 4 отчётов:

#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log
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.sql | sed '/^$/d'| sed 's/  */ /g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/ABONENTS_$file_date.csv
if [ -f /app/asr_billing/cfg/sorm3/data/ABONENTS_$file_date.csv ]
then
	printf 'Метаданные файла\n' >> $LOGFILE 2>&1		
	ls -l /app/asr_billing/cfg/sorm3/data/ABONENTS_$file_date.csv >> $LOGFILE 2>&1
	printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENTS_$file_date.csv | wc -l >> $LOGFILE 2>&1
	cp /app/asr_billing/cfg/sorm3/data/ABONENTS_$file_date.csv /app/asr_billing/cfg/sorm3/data/done/ABONENTS_$file_date.csv
	curl -v -sS --user carbon:carbon4 -T /app/asr_billing/cfg/sorm3/data/ABONENTS_$file_date.csv ftp://10.30.1.60/  >> $LOGFILE 2>&1
	rm -rf /app/asr_billing/cfg/sorm3/data/ABONENTS_$file_date.csv
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' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.csv
if [ -f /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.csv ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
	ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.csv >> $LOGFILE 2>&1
	printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.csv | wc -l >> $LOGFILE 2>&1
	cp /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.csv /app/asr_billing/cfg/sorm3/data/done/ABONENT_IDENT_$file_date.csv
	curl -v -sS --user carbon:carbon4 -T /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.csv ftp://10.30.1.60/  >> $LOGFILE 2>&1
	rm -rf /app/asr_billing/cfg/sorm3/data/ABONENT_IDENT_$file_date.csv
fi

chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/abonent_service.sql | sed '/^$/d'| sed 's/  */ /g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/ABONENT_SERVICE_$file_date.csv
if [ -f /app/asr_billing/cfg/sorm3/data/ABONENT_SERVICE_$file_date.csv ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_SERVICE_$file_date.csv >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_SERVICE_$file_date.csv | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/ABONENT_SERVICE_$file_date.csv /app/asr_billing/cfg/sorm3/data/done/ABONENT_SERVICE_$file_date.csv
        curl -v -sS --user carbon:carbon4 -T /app/asr_billing/cfg/sorm3/data/ABONENT_SERVICE_$file_date.csv ftp://10.30.1.60/  >> $LOGFILE 2>&1
        rm -rf /app/asr_billing/cfg/sorm3/data/ABONENT_SERVICE_$file_date.csv
fi

chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/abonent_address.sql | sed '/^$/d'| sed 's/  */ /g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/ABONENT_ADDRESS_$file_date.csv
if [ -f /app/asr_billing/cfg/sorm3/data/ABONENT_ADDRESS_$file_date.csv ]
then
        printf 'Метаданные файла\n' >> $LOGFILE 2>&1
        ls -l /app/asr_billing/cfg/sorm3/data/ABONENT_ADDRESS_$file_date.csv >> $LOGFILE 2>&1
        printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ABONENT_ADDRESS_$file_date.csv | wc -l >> $LOGFILE 2>&1
        cp /app/asr_billing/cfg/sorm3/data/ABONENT_ADDRESS_$file_date.csv /app/asr_billing/cfg/sorm3/data/done/ABONENT_ADDRESS_$file_date.csv
        curl -v -sS --user carbon:carbon4 -T /app/asr_billing/cfg/sorm3/data/ABONENT_ADDRESS_$file_date.csv ftp://10.30.1.60/  >> $LOGFILE 2>&1
        rm -rf /app/asr_billing/cfg/sorm3/data/ABONENT_ADDRESS_$file_date.csv
fi
Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.