|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Изменения (7)
просмотр истории страницыh6. Структура отчёта ttexpdbusrs_abonents |
{toc} |
|
h2. Сайт поставщика [ТехАргос АПК "Вектор-ИС"|https://t-argos.ru/solutions/copm-3/573-vektor-is/] h2. Какие справучники необходимы Для интеграции с СОРМ компании ТехАргос требуется сформировать следующие выгрузки: -Справочник IP шлюзов Оператора связи, с которых получены записи о соединениях абонентов -Данные по платежам абонентов Оператора связи -Данные с планами IP-нумерации сети -Справочник способов оплаты Оператора связи, с которых получены записи о соединениях абонентов -Данные по абонентам Оператора связи Для автоматического выполнения скриптов должна быть создана структура каталогов в контейнере asr_billing следующего формата: /cfg/sorm3/data/done/ h2. Справочник IP шлюзов Оператора связи, с которых получены записи о соединениях абонентов Пусть до отчёта в контейнере asr_billing: /cfg/sorm3/tsql/nas.sql h3. Структура отчёта nas |
{code} set heading off; |
select distinct ' |' || id || ' |' , uf_ip2string(ip) || ' |' ,' |' ||' |' || STAT_FIRST_AUTH || ' |' ,' |' || name || ' |' ,' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' from nas order by ID; {code} h3. Скрипт для выгрузки {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} h2. Данные по платежам абонентов Оператора связи Пусть до отчёта в контейнере asr_billing: /cfg/sorm3/tsql/payments.sql h3. Структура отчёта payments.sql {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} h3. Скрипт для выгрузки {code} #!/bin/bash LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/payments.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/payments.conf" count_or=$( awk -F= 'NR==1 {printf $2}' $patchconf) count=$(($count_or+1)) newcount=$(printf "%04d" $count) sed -i -e "1s/${count_or}/${count}/" $patchconf sql="/app/asr_billing/var/cfg/sorm3/tsql/payments.sql" filename=payments_00104_${GateDate}_${newcount}.txt 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) 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/payments.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f utf8 -t cp1251 > /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/payments/${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} h2. Данные с планами IP-нумерации сети h3. Скрипт для выгрузки {code} #!/bin/sh LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/IpNumeringPlan.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/IpNumeringPlan.conf" count_or=$( awk -F= 'NR==1 {printf $2}' $patchconf) count=$(($count_or+1)) newcount=$(printf "%04d" $count) count=$(($count_or+1)) sed -i -e "1s/${count_or}/${count}/" $patchconf telco_id="100" filename=${newcount}_IpNumeringPlan_${GateDate}.txt 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) function main { (grep -i '^.subnet' /app/collector/mnt/etc/dhcp/dhcpd.conf | awk -v telco_id=$telco_id '{ print $1=telco_id"|"$2"|" $2"|"$4"|"$6"20.05.2016| " }' > /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/IpNumeringPlan/${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} h2. Справочник способов оплаты Оператора связи, с которых получены записи о соединениях абонентов h3. Скрипт для выгрузки {code} #!/bin/sh LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/pay_Types_Records.log file_date=$(date +%Y%m%d_%H%M) date >> $LOGFILE 2>&1 #echo "Выгрузка номер $newcount" >> $LOGFILE 2>&1 GateDate=$(date "+%s") patchconf="/app/asr_billing/mnt/var/cfg/sorm3/sh/pay_Types_Records.conf" count_or=$( sed '1s/.*\=//' $patchconf) count=$(($count_or+1)) #echo count_or=$count_or #echo count=$count newcount=$(printf "%04d" $count) #echo newcount=$newcount sed -i -e "1s/${count_or}/${count}/" $patchconf telco_id="100" filename=pay_Types_Records_00330_${GateDate}_${newcount}.txt 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) function main { echo '100|1|20.05.2016 00:00:00| |Unknown' > /app/asr_billing/cfg/sorm3/data/${filename} echo '100|2|20.05.2016 00:00:00| |WebCash' >> /app/asr_billing/cfg/sorm3/data/${filename} (in=10; for operator in `grep -shir "paywork.*new" /app/asr_fiscal/usr/local/www/htdocs/* | sed 's/ *//g; s/this->//g ;s/k=n/k = n/g; s/,/, /g' | awk '{print $5}' | sed -e "/^$/d; s/);//g; s/'//g" | sort | uniq`; do echo -e $telco_id'|'$in'|'20.05.2016 00:00:00'|' '|'$operator; ((in++)); done >> /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/pay_Types_Records/${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} h2. Данные по абонентам Оператора связи Пусть до отчёта в контейнере asr_billing: /cfg/sorm3/tsql/ttexpdbusrs_abonents.sql h3. Структура отчёта ttexpdbusrs_abonents {code} set heading off; |
select distinct '100|7| | | | | | | | | | | |', case when u.ip is null then ' | | | | | | | | | |' |
... |
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} |
h3. Скрипт для выгрузки {code} #!/bin/bash LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/ttexpdbusrs.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/ttexpdbusrs.conf" count_or=$( awk -F= 'NR==1 {printf $2}' $patchconf) count=$(($count_or+1)) newcount=$(printf "%04d" $count) sed -i -e "1s/${count_or}/${count}/" $patchconf filename=Abonents_00330_${GateDate}_${newcount}.txt 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) function main { (chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/tsql/ttexpdbusrs_abonents.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f utf8 -t cp1251 > /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/ttexpdbusrs/${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} |