Для интеграции с СОРМ компании ТехАгрос требуется сформировать следующие выгрузки:
-Справочник IP шлюзов Оператора связи, с которых получены записи о соединениях абонентов
-Данные по платежам абонентов Оператора связи
-Данные с планами IP-нумерации сети
-Справочник способов оплаты Оператора связи, с которых получены записи о соединениях абонентов
-Данные по абонентам Оператора связи
h5. Справочник IP шлюзов Оператора связи, с которых получены записи о соединениях абонентов
Для автоматического выполнения скрипта должна быть создана структура каталогов в контейнере asr_billing следующего формата: /cfg/sorm3/data/done/
Пусть до отчёта в контейнере asr_billing h6. /var/cfg/sorm3/tsql/nas.sql
h6. Структура отчёта nas
{code}
set heading off;
select distinct ' |' || id || ' |' , uf_ip2string(ip) || ' |' ,' |' ||' |' || STAT_FIRST_AUTH || ' |' ,' |' || name || ' |' ,' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |'
from nas
where operator_id=3 or operator_id=23970
order by ID;
{code}
h6. Скрипт для выгрузки
{code}
#!/bin/bash
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/GatesRecords.log
file_date=$(date +%Y%m%d_%H%M)
printf '\n\n###################################################\n' >> $LOGFILE 2>&1
date | iconv -f utf8 -t cp1251 >> $LOGFILE 2>&1
GateDate=$(date "+%s")
patchconf="/app/asr_billing/mnt/var/cfg/sorm3/sh/GatesRecords.conf"
count_or=$( sed '1s/.*\=//' $patchconf)
count=$(($count_or+1))
newcount=$(printf "%04d" $count)
sed -i -e "1s/${count_or}/${count}/" $patchconf
telco_id="100"
begin_time="20.08.2015 13:00:00"
addressType="1"
unstruct_info="М.О. Солнечногорский район пгт. Поварово ул. Ленинградская 2а офис 36"
gate_type="aaa(7)"
ftpconf="/app/asr_billing/mnt/var/cfg/sorm3/sh/ftp.conf"
ftpconf="/app/asr_billing/mnt/var/cfg/sorm3/sh/ftp.conf"
ftpip=$( awk -F= 'NR==1 {printf $2}' $ftpconf)
ftplogin=$( awk -F= 'NR==2 {printf $2}' $ftpconf)
ftppass=$( awk -F= 'NR==3 {printf $2}' $ftpconf)
filename=GatesRecords_00330_${GateDate}_${newcount}.txt
function main
{
(chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /var/cfg/sorm3/tsql/nas.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f utf8 -t cp1251 |\
awk -v telco_id=$telco_id -v begin_time="$begin_time" -v addressType="$addressType" -v unstruct_info="$unstruct_info" -v gate_type="$gate_type" -F'|' -vOFS="|" '{$1=telco_id; $6=begin_time; $9=addressType; $10=unstruct_info; $20=gate_type; print $0}' > /app/asr_billing/cfg/sorm3/data/${filename} ) || echo error
if [ -f /app/asr_billing/cfg/sorm3/data/${filename} ]
then
actualsize=$(wc -c < /app/asr_billing/cfg/sorm3/data/${filename})
if [ $actualsize -gt 0 ];
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/${filename} >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/${filename} | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/${filename} /app/asr_billing/cfg/sorm3/data/done/nas/${filename}
curl --upload-file /app/asr_billing/cfg/sorm3/data/${filename} ftp://$ftplogin:$ftppass@$ftpip/ >> $LOGFILE 2>&1 && rm -rf /app/asr_billing/cfg/sorm3/data/${filename}
else
printf 'error' >>$LOGFILE 2>&1 && rm -rf /app/asr_billing/cfg/sorm3/data/${filename}
fi
fi
}
echo "Выгрузка номер $newcount" >> $LOGFILE 2>&1
main 2>&1 | tee -a $LOGFILE
{code}
h6. Структура отчёта ttexpdbusrs_abonents
{code}
set heading off;
select distinct '100|7| | | | | | | | | | | |',
case when u.ip is null then ' | | | | | | | | | |'
else '' || uf_ip2string(u.ip) || '|' || u.mask || '| | | ' || u.mac || '| | | | | |'
end, a.id,
case when a.company=0 then '|1| | | |' || a.name || '|' ||coalesce(av22.attribute_value,'')|| '|22|' || coalesce(av14.attribute_value,'') || '|' || coalesce(av13.attribute_value,'') ||'|' || coalesce(av17.attribute_value,'') || '|' || coalesce(av16.attribute_value,'') || '| | | | |'
else '|2| | | | | | | | | | | | |' || a.name || '| |'
end ,
case when A.home_id is null then '1| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.country is null then '1| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.zip_code is null then '1| |' || h.country || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.region is null then '1| |' || h.country || '|' || h.zip_code || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.city is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.street is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.s_number is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.s_liter is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when a.a_home_number is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '|' || h.s_liter || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '|' || h.s_liter || '|' || a.a_home_number || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
end
end
end
end
end
end
end
end
end,
a.contract_number ,
case when a.activate_date is null then '| | | | |'
else '| |' || EXTRACT(YEAR FROM a.activate_date)||'.' ||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)) || '| | |'
end ,
case when (ab.b_negbal=1 and (os.status<>5 or os.status is null)) then '2|'
else '1|'
end,
' | | | | | | | | | | | | | | | | | '
from abonents as a
left join users as u on a.id=u.abonent_id
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 av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=17
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
left join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID
left join objects_status os on a.id=os.object_id
where a.parent_id not in (244)
and a.parent_id not in (25416)
and a.parent_id not in (25322)
and a.parent_id not in (19902)
and a.parent_id not in (24013)
and a.parent_id not in (23995)
and a.parent_id not in (2)
and a.parent_id not in (8606)
and a.parent_id not in (19892)
and a.parent_id not in (25625)
and a.is_folder=0
and a.deleted=0
and a.id is not null
and u.nas_id not in (1137)
and u.nas_id not in (1138)
and u.nas_id not in (1122)
order by a.ID;
{code}
h6. Структура отчёта payments
{code}
set heading off; select
'10|100|8|||||||||||' || a.contract_number || '||||||' ||
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)) || '|||||||||||' ||
coalesce(fo.operator_name,'') || '|' || a.contract_number || '|'
|| coalesce((select first 1 uf_ip2string(ip) from users u where u.abonent_id = a.id and u.ip is not null),'') || '|32||||' || a.name || '|||||||||||||||||||||'
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) and
((fo.op_date between dateadd (-15 minute to current_timestamp) and current_timestamp) or ((fo.operator_name='' or fo.operator_name is null) and (fo.SYSTEM_DATE between dateadd (-15 minute to current_timestamp) and current_timestamp)))
order by fo.op_date;
{code}
-Справочник IP шлюзов Оператора связи, с которых получены записи о соединениях абонентов
-Данные по платежам абонентов Оператора связи
-Данные с планами IP-нумерации сети
-Справочник способов оплаты Оператора связи, с которых получены записи о соединениях абонентов
-Данные по абонентам Оператора связи
h5. Справочник IP шлюзов Оператора связи, с которых получены записи о соединениях абонентов
Для автоматического выполнения скрипта должна быть создана структура каталогов в контейнере asr_billing следующего формата: /cfg/sorm3/data/done/
Пусть до отчёта в контейнере asr_billing h6. /var/cfg/sorm3/tsql/nas.sql
h6. Структура отчёта nas
{code}
set heading off;
select distinct ' |' || id || ' |' , uf_ip2string(ip) || ' |' ,' |' ||' |' || STAT_FIRST_AUTH || ' |' ,' |' || name || ' |' ,' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |'
from nas
where operator_id=3 or operator_id=23970
order by ID;
{code}
h6. Скрипт для выгрузки
{code}
#!/bin/bash
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/GatesRecords.log
file_date=$(date +%Y%m%d_%H%M)
printf '\n\n###################################################\n' >> $LOGFILE 2>&1
date | iconv -f utf8 -t cp1251 >> $LOGFILE 2>&1
GateDate=$(date "+%s")
patchconf="/app/asr_billing/mnt/var/cfg/sorm3/sh/GatesRecords.conf"
count_or=$( sed '1s/.*\=//' $patchconf)
count=$(($count_or+1))
newcount=$(printf "%04d" $count)
sed -i -e "1s/${count_or}/${count}/" $patchconf
telco_id="100"
begin_time="20.08.2015 13:00:00"
addressType="1"
unstruct_info="М.О. Солнечногорский район пгт. Поварово ул. Ленинградская 2а офис 36"
gate_type="aaa(7)"
ftpconf="/app/asr_billing/mnt/var/cfg/sorm3/sh/ftp.conf"
ftpconf="/app/asr_billing/mnt/var/cfg/sorm3/sh/ftp.conf"
ftpip=$( awk -F= 'NR==1 {printf $2}' $ftpconf)
ftplogin=$( awk -F= 'NR==2 {printf $2}' $ftpconf)
ftppass=$( awk -F= 'NR==3 {printf $2}' $ftpconf)
filename=GatesRecords_00330_${GateDate}_${newcount}.txt
function main
{
(chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /var/cfg/sorm3/tsql/nas.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f utf8 -t cp1251 |\
awk -v telco_id=$telco_id -v begin_time="$begin_time" -v addressType="$addressType" -v unstruct_info="$unstruct_info" -v gate_type="$gate_type" -F'|' -vOFS="|" '{$1=telco_id; $6=begin_time; $9=addressType; $10=unstruct_info; $20=gate_type; print $0}' > /app/asr_billing/cfg/sorm3/data/${filename} ) || echo error
if [ -f /app/asr_billing/cfg/sorm3/data/${filename} ]
then
actualsize=$(wc -c < /app/asr_billing/cfg/sorm3/data/${filename})
if [ $actualsize -gt 0 ];
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/${filename} >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/${filename} | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/${filename} /app/asr_billing/cfg/sorm3/data/done/nas/${filename}
curl --upload-file /app/asr_billing/cfg/sorm3/data/${filename} ftp://$ftplogin:$ftppass@$ftpip/ >> $LOGFILE 2>&1 && rm -rf /app/asr_billing/cfg/sorm3/data/${filename}
else
printf 'error' >>$LOGFILE 2>&1 && rm -rf /app/asr_billing/cfg/sorm3/data/${filename}
fi
fi
}
echo "Выгрузка номер $newcount" >> $LOGFILE 2>&1
main 2>&1 | tee -a $LOGFILE
{code}
h6. Структура отчёта ttexpdbusrs_abonents
{code}
set heading off;
select distinct '100|7| | | | | | | | | | | |',
case when u.ip is null then ' | | | | | | | | | |'
else '' || uf_ip2string(u.ip) || '|' || u.mask || '| | | ' || u.mac || '| | | | | |'
end, a.id,
case when a.company=0 then '|1| | | |' || a.name || '|' ||coalesce(av22.attribute_value,'')|| '|22|' || coalesce(av14.attribute_value,'') || '|' || coalesce(av13.attribute_value,'') ||'|' || coalesce(av17.attribute_value,'') || '|' || coalesce(av16.attribute_value,'') || '| | | | |'
else '|2| | | | | | | | | | | | |' || a.name || '| |'
end ,
case when A.home_id is null then '1| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.country is null then '1| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.zip_code is null then '1| |' || h.country || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.region is null then '1| |' || h.country || '|' || h.zip_code || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.city is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.street is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.s_number is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when h.s_liter is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else case when a.a_home_number is null then '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '|' || h.s_liter || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
else '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '|' || h.s_liter || '|' || a.a_home_number || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
end
end
end
end
end
end
end
end
end,
a.contract_number ,
case when a.activate_date is null then '| | | | |'
else '| |' || EXTRACT(YEAR FROM a.activate_date)||'.' ||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)) || '| | |'
end ,
case when (ab.b_negbal=1 and (os.status<>5 or os.status is null)) then '2|'
else '1|'
end,
' | | | | | | | | | | | | | | | | | '
from abonents as a
left join users as u on a.id=u.abonent_id
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 av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=17
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
left join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID
left join objects_status os on a.id=os.object_id
where a.parent_id not in (244)
and a.parent_id not in (25416)
and a.parent_id not in (25322)
and a.parent_id not in (19902)
and a.parent_id not in (24013)
and a.parent_id not in (23995)
and a.parent_id not in (2)
and a.parent_id not in (8606)
and a.parent_id not in (19892)
and a.parent_id not in (25625)
and a.is_folder=0
and a.deleted=0
and a.id is not null
and u.nas_id not in (1137)
and u.nas_id not in (1138)
and u.nas_id not in (1122)
order by a.ID;
{code}
h6. Структура отчёта payments
{code}
set heading off; select
'10|100|8|||||||||||' || a.contract_number || '||||||' ||
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)) || '|||||||||||' ||
coalesce(fo.operator_name,'') || '|' || a.contract_number || '|'
|| coalesce((select first 1 uf_ip2string(ip) from users u where u.abonent_id = a.id and u.ip is not null),'') || '|32||||' || a.name || '|||||||||||||||||||||'
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) and
((fo.op_date between dateadd (-15 minute to current_timestamp) and current_timestamp) or ((fo.operator_name='' or fo.operator_name is null) and (fo.SYSTEM_DATE between dateadd (-15 minute to current_timestamp) and current_timestamp)))
order by fo.op_date;
{code}