{warning}Эта схема больше не поддерживается, пожалуйста используйте новую: [CarbonBilling:СОРМ3 VasExperts СКАТ]{warning}
{toc}
h1. Сайт поставщика
[VasExperts ИС СОРМ-3|https://vasexperts.ru/products/is-sorm/is-sorm-3/]
h1. Выгрузка на СОРМ3
h2. Принцип работы
Для интеграции с СОРМ3 VasExperts требуется произвести первичную выгрузку данных, а так же добавить сценарии планировщика задач, которые будут выгружать добавленные и изменённые данные с определённой периодичностью: один раз в час.
Для автоматизации передачи отчетов на СОРМ3 сделайте следующее:
# Создайте папку *sorm3* и необходимые подпапки, в них будут храниться отчеты для выгрузки, а так же выгруженные файлы:
{code}
mkdir -p /app/asr_billing/cfg/sorm3/
mkdir -p /app/asr_billing/cfg/sorm3/conf/
mkdir -p /app/asr_billing/cfg/sorm3/data/
mkdir -p /app/asr_billing/cfg/sorm3/done/
mkdir -p /app/asr_billing/cfg/sorm3/Log/
{code}
# Создайте файл *ftp.conf* c данными для подключения к FTP-серверу СОРМ в директории */app/asr_billing/cfg/sorm3/conf/*. Пример содержимого файла */app/asr_billing/cfg/sorm3/conf/ftp.conf*:
{code}
ftpip=192.168.1.10
ftplogin=sorm
ftppass=sormpass
{code}
# Добавьте все отчеты, получающие данные из биллинга, а также скрипты для первоначальной выгрузки данных в папку */app/asr_billing/cfg/sorm3/*. Листинги отчетов и пути приведены далее в статье. Должна получиться следующая структура каталога:
{code}
/app/asr_billing/cfg/sorm3/conf
/app/asr_billing/cfg/sorm3/data
/app/asr_billing/cfg/sorm3/deleted_abonents.sh
/app/asr_billing/cfg/sorm3/deleted_abonents.sql
/app/asr_billing/cfg/sorm3/done
/app/asr_billing/cfg/sorm3/fixednetid.sql
/app/asr_billing/cfg/sorm3/fixednetid.sh
/app/asr_billing/cfg/sorm3/ip_numbering.sh
/app/asr_billing/cfg/sorm3/ip_numbering.sql
/app/asr_billing/cfg/sorm3/ip.sh
/app/asr_billing/cfg/sorm3/ip.sql
/app/asr_billing/cfg/sorm3/Log
/app/asr_billing/cfg/sorm3/payments-periodic.sql
/app/asr_billing/cfg/sorm3/payments.sh
/app/asr_billing/cfg/sorm3/payments.sql
/app/asr_billing/cfg/sorm3/service_list-periodic.sql
/app/asr_billing/cfg/sorm3/service_list.sh
/app/asr_billing/cfg/sorm3/service_list.sql
/app/asr_billing/cfg/sorm3/services-periodic.sql
/app/asr_billing/cfg/sorm3/services.sh
/app/asr_billing/cfg/sorm3/services.sql
/app/asr_billing/cfg/sorm3/subscribers_v2-periodic.sql
/app/asr_billing/cfg/sorm3/subscribers_v2.sh
/app/asr_billing/cfg/sorm3/subscribers_v2.sql
/app/asr_billing/cfg/sorm3/upload_all.sh
{code}
# Добавьте задания в планировщик задач и перезапустите его.
# Проведите первоначальное наполнение данными
h1. Скрипт для планировщика задач
Скрипт выгружает изменения по абонентам, услугам и платежам за последний час.
*/etc/cron.hourly/vasexperts_sorm_hourly.sh*
{code}
#/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/payments-periodic.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/payments_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
fi
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/services-periodic.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/services_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/services_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/services_$file_date.txt /app/asr_billing/cfg/sorm3/done/services_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
fi
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/service_list-periodic.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/service_list_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
fi
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/subscribers_v2-periodic.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt /app/asr_billing/cfg/sorm3/done/subscribers_v2_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
fi
{code}
После сохранения скриптов, установите на обоих права на выполнение и перезапустите планировщик:
{code}
chmod a+x /etc/cron.hourly/vasexperts_sorm_hourly.sh
/etc/init.d/crond restart
{code}
h1. Скрипты для первоначальной выгрузки данных
Скрипты производят первичное наполнение СОРМ данными, выгружая как динамически формируемую информацию (абоненты, услуги, пополнения балансов и тд), так и статичные данные (платежные системы, план нумерации, виды документов).
Когда все статичные данные будут подготовлены и сохранены на сервере, сохраните скрипты по указанным путям. Далее, разрешите их выполнение и выполните скрипт */app/asr_billing/cfg/sorm3/upload_all.sh*
{code}
chmod a+x /app/asr_billing/cfg/sorm3/deleted_abonents.sh
chmod a+x /app/asr_billing/cfg/sorm3/fixednetid.sh
chmod a+x /app/asr_billing/cfg/sorm3/ip_numbering.sh
chmod a+x /app/asr_billing/cfg/sorm3/ip.sh
chmod a+x /app/asr_billing/cfg/sorm3/payments.sh
chmod a+x /app/asr_billing/cfg/sorm3/service_list.sh
chmod a+x /app/asr_billing/cfg/sorm3/services.sh
chmod a+x /app/asr_billing/cfg/sorm3/subscribers_v2.sh
chmod a+x /app/asr_billing/cfg/sorm3/upload_all.sh
bash /app/asr_billing/cfg/sorm3/upload_all.sh
{code}
*/app/asr_billing/cfg/sorm3/deleted_abonents.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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' > /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/deleted_abonents.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt /app/asr_billing/cfg/sorm3/done/deleted_abonents_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/fixednetid.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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' > /app/asr_billing/cfg/sorm3/data/fixednetid_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/fixednetid.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/fixednetid.txt
#chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/fixednetid.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/fixednetid.txt
if [ -f /app/asr_billing/cfg/sorm3/data/fixednetid.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/fixednetid_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/fixednetid.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/fixednetid.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/fixednetid.txt /app/asr_billing/cfg/sorm3/done/fixednetid_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/fixednetid.txt ftp://$ftpip/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/fixednetid.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/ip_numbering.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/ip_numbering.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ip_numbering.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt /app/asr_billing/cfg/sorm3/done/ip_numbering_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt ftp://$ftpip/ISP/dict/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/ip.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/ip_nets_v1.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ip.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/ip_nets_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/payments.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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' > /app/asr_billing/cfg/sorm3/data/subscribers_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/payments.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/payments_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/service_list.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/service_list.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/service_list.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/service_list_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/services.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/services.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/services.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/services_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/services_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/services_$file_date.txt /app/asr_billing/cfg/sorm3/done/services_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/subscribers_v2.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/subscribers_v2.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/subscribers_v2.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
#chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/subscribers_v2.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt /app/asr_billing/cfg/sorm3/done/subscribers_v2_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/upload_all.sh*
{code}
#!/bin/bash
/app/asr_billing/cfg/sorm3/subscribers_v2.sh
/app/asr_billing/cfg/sorm3/deleted_abonents.sh
/app/asr_billing/cfg/sorm3/payments.sh
/app/asr_billing/cfg/sorm3/services.sh
/app/asr_billing/cfg/sorm3/ip.sh
/app/asr_billing/cfg/sorm3/ip_numbering.sh
/app/asr_billing/cfg/sorm3/service_list.sh
/app/asr_billing/cfg/sorm3/fixednetid.sh
{code}
h1. Отчеты
h2. 1. Плоский формат выгрузки абонентских данных
*/app/asr_billing/cfg/sorm3/subscribers_v2.sql*
{code}
set heading off;select distinct '"0";"' || --стандарт связи абонента--
A.ID || '";"' || --id абонента --
A.ID || '";"' || --логин--
A.CONTRACT_NUMBER || '";"' || --номер договора--
case when A.DELETED=1 then '1' --текущий статус абонента, справочник, 0 – подключен 1 - отключен--
else '0' end || '";"' ||
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)) || ':00' || '";"' || --дата заключения договора--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM AH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' || from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '";"'|| --дата расторжения договора, дата, формат DD.mm.YYYY HH:MM:SS, для активных абонентов пустая строка--
coalesce(A.COMPANY,'0') || '";"' || --тип абонента, справочник, 0 - физическое лицо, 1 - юридическое лицо--
case when coalesce(A.COMPANY,'0')=0 then '1' --тип данных по ФИО, справочник, 0 - структурированные данные, 1 -неструктурированные, обязателен для физических лиц--
else ' ' end || '";"' ||
'' || '";"' ||
'' || '";"' ||
'' || '";"' ||
case when A.COMPANY=0 then A.NAME || '";"' ||--неструктурированное ФИО--
--iif (position('.19' in av1020.attribute_value) !=0, coalesce(left(replace(replace (replace(av1020.attribute_value, '«', ''),'»',''), ' ', ''),10),''), '') --дата рождения --
iif (coalesce(av22.attribute_value,'')='','',av22.attribute_value) --дата рождения
else '";"' end || '";"' ||
--паспортные данные --
case when A.COMPANY=0 then
'0' || '";"' ||
coalesce(av14.attribute_value, '') || '";"' || --паспорт серия
coalesce(av13.attribute_value, '') || '";"' || --паспорт номер
--coalesce(av17.attribute_value, '') || '";"' || --когда выдан
iif (av17.attribute_value like '%-%',substring(av17.attribute_value from 9 for 2) || '.' || substring (av17.attribute_value from 6 for 2) || '.' || substring (av17.attribute_value from 1 for 4) ,coalesce(av17.attribute_value, '')) || '";"' || --паспорт когда выдан--
iif (coalesce(av16.attribute_value, '') <> '', av16.attribute_value || ' ', '')|| '";"' || --кем выдан
''|| '";"' || --неструктурированные паспортные данные, зарезервировано на будущее - пустая строка--
'Паспорт РФ' || '";"' || --тип документа--
coalesce(av7.attribute_value,'') || '";"' ||--банк абонента--
coalesce(av6.attribute_value,'') --р/с абонента --
else '";"";"";"";"";"";"";"";"' end || '";"' ||
case when A.COMPANY=1 then A.NAME || '";"' || --название ЮР лица --
coalesce(av4.attribute_value,'') || '";"' || '";"' || '";"' ||coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') --ИНН,контакты,банк,р/с юр.лица --
else '";"";"";"";"";"' end || '";"' ||
case when a.company=1 then
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av25.attribute_value,'') --адрес юр лица--
else iif(coalesce(av15.attribute_value,'') <> '' and char_length(av15.attribute_value) between 4 and 5, '0' || '";"' || (select IIF(coalesce(h.ZIP_CODE, '') <> '', h.zip_code, '') || '";"'
|| IIF(coalesce(h.country, '') <> '', h.country, 'Россия') || '";"' || iif(coalesce(h.region_type_id, '') <> '',htn_region.SHORT, '') || '";"' || IIF(coalesce(h.REGION, '') <> '', h.REGION, '')
|| '";"' || iif(coalesce(h.DISTRICT_type_id, '') <> '',htn_district.SHORT, '') || '";"' || IIF(coalesce(h.DISTRICT, '') <> '', h.DISTRICT, '') || '";"'
|| iif(coalesce(h.city_type_id, '') <> '',htn_city.SHORT, 'г') || '";"' || IIF(coalesce(h.CITY, '') <> '', h.CITY, '') || '";"'
|| iif(coalesce(h.street_type_id, '') <> '',htn_street.SHORT, 'ул')|| '";"' || IIF(coalesce(h.STREET,'') <> '', h.STREET, '') || '";"'
|| coalesce(h.S_NUMBER,'') || '";"' || IIF(coalesce(h.S_LITER,'') <> '',h.S_LITER, '') || '";"'
|| '";"' || IIF(coalesce(a.A_HOME_NUMBER, '') <> '',a.A_HOME_NUMBER,'') || '";"' from homes h
left join home_type_name htn_region on h.REGION_TYPE_ID=htn_region.id
left join home_type_name htn_district on h.DISTRICT_TYPE_ID=htn_district.id
left join home_type_name htn_city on h.CITY_TYPE_ID =htn_city.id
left join home_type_name htn_street on h.STREET_TYPE_ID =htn_street.id
where h.id=av15.attribute_value),'0";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' || coalesce(av26.attribute_value,''))
end || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av26.attribute_value,'') || --адрес установки оборудования --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --почтовый адрес абонента --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --адрес доставки счета --
'";"' ||
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) || 'T'
|| 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))||':'|| SUBSTRING(a.create_date from 18 for 2)|| '";"' || --дата начала интервала, на котором актуальна информация--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'||
EXTRACT(YEAR FROM AH.TIME_CHANGED) || 'T00:00:00' from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '"' --дата расторжения договора, дата, формат DD.mm.YYYY, для активных абонентов пустая строка--
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
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 av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
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=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av26 on a.id=av26.ABONENT_ID and av26.ATTRIBUTE_ID=26
left join ATTRIBUTE_VALUES as av27 on a.id=av27.ABONENT_ID and av27.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES as av34 on a.id=av34.ABONENT_ID and av34.ATTRIBUTE_ID=34
left join ATTRIBUTE_VALUES as av1011 on a.id=av1011.ABONENT_ID and av1011.ATTRIBUTE_ID=1011
left join ATTRIBUTE_VALUES as av1019 on a.id=av1019.ABONENT_ID and av1019.ATTRIBUTE_ID=1019
left join ATTRIBUTE_VALUES as av1020 on a.id=av1020.ABONENT_ID and av1020.ATTRIBUTE_ID=1020
where A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id >0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/subscribers_v2-periodic.sql*
{code}
set heading off;select distinct '"0";"' || --стандарт связи абонента--
A.ID || '";"' || --id абонента --
A.ID || '";"' || --логин--
A.CONTRACT_NUMBER || '";"' || --номер договора--
case when A.DELETED=1 then '1' --текущий статус абонента, справочник, 0 – подключен 1 - отключен--
else '0' end || '";"' ||
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)) || ':00' || '";"' || --дата заключения договора--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM AH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' || from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '";"'|| --дата расторжения договора, дата, формат DD.mm.YYYY HH:MM:SS, для активных абонентов пустая строка--
coalesce(A.COMPANY,'0') || '";"' || --тип абонента, справочник, 0 - физическое лицо, 1 - юридическое лицо--
case when coalesce(A.COMPANY,'0')=0 then '1' --тип данных по ФИО, справочник, 0 - структурированные данные, 1 -неструктурированные, обязателен для физических лиц--
else ' ' end || '";"' ||
'' || '";"' ||
'' || '";"' ||
'' || '";"' ||
case when A.COMPANY=0 then A.NAME || '";"' ||--неструктурированное ФИО--
--iif (position('.19' in av1020.attribute_value) !=0, coalesce(left(replace(replace (replace(av1020.attribute_value, '«', ''),'»',''), ' ', ''),10),''), '') --дата рождения --
iif (coalesce(av22.attribute_value,'')='','',av22.attribute_value) --дата рождения
else '";"' end || '";"' ||
--паспортные данные --
case when A.COMPANY=0 then
'0' || '";"' ||
coalesce(av14.attribute_value, '') || '";"' || --паспорт серия
coalesce(av13.attribute_value, '') || '";"' || --паспорт номер
--coalesce(av17.attribute_value, '') || '";"' || --когда выдан
iif (av17.attribute_value like '%-%',substring(av17.attribute_value from 9 for 2) || '.' || substring (av17.attribute_value from 6 for 2) || '.' || substring (av17.attribute_value from 1 for 4) ,coalesce(av17.attribute_value, '')) || '";"' || --паспорт когда выдан--
iif (coalesce(av16.attribute_value, '') <> '', av16.attribute_value || ' ', '')|| '";"' || --кем выдан
''|| '";"' || --неструктурированные паспортные данные, зарезервировано на будущее - пустая строка--
'Паспорт РФ' || '";"' || --тип документа--
coalesce(av7.attribute_value,'') || '";"' ||--банк абонента--
coalesce(av6.attribute_value,'') --р/с абонента --
else '";"";"";"";"";"";"";"";"' end || '";"' ||
case when A.COMPANY=1 then A.NAME || '";"' || --название ЮР лица --
coalesce(av4.attribute_value,'') || '";"' || '";"' || '";"' ||coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') --ИНН,контакты,банк,р/с юр.лица --
else '";"";"";"";"";"' end || '";"' ||
case when a.company=1 then
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av25.attribute_value,'') --адрес юр лица--
else iif(coalesce(av15.attribute_value,'') <> '' and char_length(av15.attribute_value) between 4 and 5, '0' || '";"' || (select IIF(coalesce(h.ZIP_CODE, '') <> '', h.zip_code, '') || '";"'
|| IIF(coalesce(h.country, '') <> '', h.country, 'Россия') || '";"' || iif(coalesce(h.region_type_id, '') <> '',htn_region.SHORT, '') || '";"' || IIF(coalesce(h.REGION, '') <> '', h.REGION, '')
|| '";"' || iif(coalesce(h.DISTRICT_type_id, '') <> '',htn_district.SHORT, '') || '";"' || IIF(coalesce(h.DISTRICT, '') <> '', h.DISTRICT, '') || '";"'
|| iif(coalesce(h.city_type_id, '') <> '',htn_city.SHORT, 'г') || '";"' || IIF(coalesce(h.CITY, '') <> '', h.CITY, '') || '";"'
|| iif(coalesce(h.street_type_id, '') <> '',htn_street.SHORT, 'ул')|| '";"' || IIF(coalesce(h.STREET,'') <> '', h.STREET, '') || '";"'
|| coalesce(h.S_NUMBER,'') || '";"' || IIF(coalesce(h.S_LITER,'') <> '',h.S_LITER, '') || '";"'
|| '";"' || IIF(coalesce(a.A_HOME_NUMBER, '') <> '',a.A_HOME_NUMBER,'') || '";"' from homes h
left join home_type_name htn_region on h.REGION_TYPE_ID=htn_region.id
left join home_type_name htn_district on h.DISTRICT_TYPE_ID=htn_district.id
left join home_type_name htn_city on h.CITY_TYPE_ID =htn_city.id
left join home_type_name htn_street on h.STREET_TYPE_ID =htn_street.id
where h.id=av15.attribute_value),'0";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' || coalesce(av26.attribute_value,''))
end || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av26.attribute_value,'') || --адрес установки оборудования --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --почтовый адрес абонента --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --адрес доставки счета --
'";"' ||
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) || 'T'
|| 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))||':'|| SUBSTRING(a.create_date from 18 for 2)|| '";"' || --дата начала интервала, на котором актуальна информация--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'||
EXTRACT(YEAR FROM AH.TIME_CHANGED) || 'T00:00:00' from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '"' --дата расторжения договора, дата, формат DD.mm.YYYY, для активных абонентов пустая строка--
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
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 av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
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=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av26 on a.id=av26.ABONENT_ID and av26.ATTRIBUTE_ID=26
left join ATTRIBUTE_VALUES as av27 on a.id=av27.ABONENT_ID and av27.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES as av34 on a.id=av34.ABONENT_ID and av34.ATTRIBUTE_ID=34
left join ATTRIBUTE_VALUES as av1011 on a.id=av1011.ABONENT_ID and av1011.ATTRIBUTE_ID=1011
left join ATTRIBUTE_VALUES as av1019 on a.id=av1019.ABONENT_ID and av1019.ATTRIBUTE_ID=1019
left join ATTRIBUTE_VALUES as av1020 on a.id=av1020.ABONENT_ID and av1020.ATTRIBUTE_ID=1020
where A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id >0
and (u.login in (select uh.login from users_history uh where uh.time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp)
or a.id in (select ah.abonents_id from abonents_history ah where ah.time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp));
{code}
h2. 2. Выгрузка номенклатуры услуг оператора
*/app/asr_billing/cfg/sorm3/service_list.sql*
{code}
set heading off; select
'"' || U.ID || '";"' || --id услуги --
iif (U.NAME='', 'Удаленная услуга', U.NAME) || '";"' || --имя услуги --
coalesce(U.COMMENTS,'') || '";"' || --описание услуги --
coalesce((
select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
),'01.01.2018') || '";"' || --дата создания услуги (дата активации услуги)--
case
when U.DELETED=1
then coalesce ((select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
and UH.deleted=1
),'01.01.2018')
else ''
end || '";"' || --дата удаления услуги (дата деактивации услуги)-- */
'Ethernet"' --тип сети
from
USLUGA U
where u.id>0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/service_list-periodic.sql*
{code}
set heading off; select
'"' || U.ID || '";"' || --id услуги --
iif (U.NAME='', 'Удаленная услуга', U.NAME) || '";"' || --имя услуги --
coalesce(U.COMMENTS,'') || '";"' || --описание услуги --
coalesce((
select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
),'01.01.2018') || '";"' || --дата создания услуги (дата активации услуги)--
case
when U.DELETED=1
then coalesce ((select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
and UH.deleted=1
),'01.01.2018')
else ''
end || '";"' || --дата удаления услуги (дата деактивации услуги)-- */
'Ethernet"' --тип сети
from
USLUGA U
where u.id>0
and (u.id in (select usluga_id from usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp));
{code}
h2. 3. Выгрузка услуг по абонентам
*/app/asr_billing/cfg/sorm3/services.sql*
{code}
set heading off; select distinct
'"' || U.ID || '";"' || --id услуги --
A.ID || '";"' || --id абонента--
'";"";"' || '";"' || -- ID лицевого счёта, ID точки подключения, ID договора--
U.NAME || '";"' || --имя услуги --
coalesce(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(DAY FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.CREATE_DATE)) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.CREATE_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.CREATE_DATE),2,'0') AS varchar(2)) || ':00' || '";"' || --дата создания услуги--
case when UU.DELETED=1 then
coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UUH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.NEXT_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.NEXT_DATE),2,'0') AS varchar(2)) || ':00' )
else ' ' end || '"' --дата удаления услуги--
from USERS_USLUGA UU
join abonents a on a.id=uu.abonent_id
join usluga u on uu.usluga_id = u.id
where a.is_folder=0
and a.id >0
and a.parent_id not in (2,244)
and u.id > 0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/services-periodic.sql*
{code}
set heading off; select distinct
'"' || U.ID || '";"' || --id услуги --
A.ID || '";"' || --id абонента--
'";"";"' || '";"' || -- ID лицевого счёта, ID точки подключения, ID договора--
U.NAME || '";"' || --имя услуги --
coalesce(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(DAY FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.CREATE_DATE)) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.CREATE_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.CREATE_DATE),2,'0') AS varchar(2)) || ':00' || '";"' || --дата создания услуги--
case when UU.DELETED=1 then
coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UUH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.NEXT_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.NEXT_DATE),2,'0') AS varchar(2)) || ':00' )
else ' ' end || '"' --дата удаления услуги--
from USERS_USLUGA UU
join abonents a on a.id=uu.abonent_id
join usluga u on uu.usluga_id = u.id
where a.is_folder=0
and a.id >0
and a.parent_id not in (2,244)
and u.id > 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. 4. Выгрузка платежей абонентов
*/app/asr_billing/cfg/sorm3/payments.sql*
{code}
set heading off; select distinct
'"' || A.ID || '";"' || --id абонента --
'";"' || --код абонента, зарезервировано на будущее--
-- fo.OP_DATE || '";"' || --Дата платежа--
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) || 'T' ||
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))||':'|| lpad(CAST(EXTRACT(SECOND FROM fo.OP_DATE) as integer),2,'0') || '";"' ||
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '";"' || --Сумма платежа--
iif(fo.OPERATOR_NAME is not null and fo.OPERATOR_NAME<>'',fo.OPERATOR_NAME,'Admin')|| '";"' ||
coalesce (fo.ADMIN_COMMENT,'') || '"' --раметры платежа, строка, размер 512, дополнительная информация по платежу в неструктурированном виде, при её наличии--
from abonents a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null and a.id>0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/payments-periodic.sql*
{code}
set heading off; select distinct
'"' || A.ID || '";"' || --id абонента --
'";"' || --код абонента, зарезервировано на будущее--
-- fo.OP_DATE || '";"' || --Дата платежа--
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) || 'T' ||
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))||':'|| lpad(CAST(EXTRACT(SECOND FROM fo.OP_DATE) as integer),2,'0') || '";"' ||
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '";"' || --Сумма платежа--
iif(fo.OPERATOR_NAME is not null and fo.OPERATOR_NAME<>'',fo.OPERATOR_NAME,'Admin')|| '";"' ||
coalesce (fo.ADMIN_COMMENT,'') || '"' --раметры платежа, строка, размер 512, дополнительная информация по платежу в неструктурированном виде, при её наличии--
from abonents a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id>0
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. 5. Формат выгрузки на удаление абонентов из БД
*/app/asr_billing/cfg/sorm3/deleted_abonents.sql*
{code}
set heading off;
select '"' ||
id || '";""'
from abonents
where
is_folder=0
and deleted=1;
{code}
h2. 6. Выгрузка IP адресов
*/app/asr_billing/cfg/sorm3/ip.sql*
В отчете приведен пример интервала белых ip(U.IP between 1541441536 and 1541442047). Вам требуется самостоятельно определить белые ip-адреса с помощью [конвертера|https://www.browserling.com/tools/ip-to-dec]
{code}
set heading off; select distinct '"' || '";"' || --зарезервировано на будущее--
A.ID || '";"' || --id абонента --
uf_ip2string(U.IP) || '";"' || --ip абонента --
case when U.IP between 1541441536 and 1541442047 then '1' --Здесь приведен пример интервала белых ip --
else '0' end || '";"' ||
(select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED) ||'T'|| CAST(lpad(EXTRACT(HOUR FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(MINUTE FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| SUBSTRING(uh.time_changed from 18 for 2) from users_history UH where UH.IP=U.IP and UH.USERS_ID=U.ID) || '";"' ||
''|| '";"' ||
U.MAC ||
'"'
from abonents A
join USERS U on U.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id > 0
and U.IP != 0;
{code}
h2. 7. Выгрузка данных по IP-нумерации оператора
*/app/asr_billing/cfg/sorm3/ip_numbering.sql*
{code}
set heading off; select '"' ||
uf_ip2string(NETWORK_ADDRESS) || '/' ||
mask || '";"' ||
name || '-Статические адреса для абонентов' || '";"' ||
'01.01.2017T00:00:00' || '";""'
from ip_pull
where pull_id not in (1,2,3,4,5,6,7);
{code}
h2. 8. Выгрузка привязки ip адреса к логину
*/app/asr_billing/cfg/sorm3/fixednetid.sql*
{code}
set heading off; select
u.abonent_id || ';' ||
uf_ip2string(u.ip) || '/' || coalesce(mask,'32')
from users u
right join abonents a on a.id=u.abonent_id and a.deleted=0
where
u.ip is not null
and u.abonent_id>=1000;
{code}
{toc}
h1. Сайт поставщика
[VasExperts ИС СОРМ-3|https://vasexperts.ru/products/is-sorm/is-sorm-3/]
h1. Выгрузка на СОРМ3
h2. Принцип работы
Для интеграции с СОРМ3 VasExperts требуется произвести первичную выгрузку данных, а так же добавить сценарии планировщика задач, которые будут выгружать добавленные и изменённые данные с определённой периодичностью: один раз в час.
Для автоматизации передачи отчетов на СОРМ3 сделайте следующее:
# Создайте папку *sorm3* и необходимые подпапки, в них будут храниться отчеты для выгрузки, а так же выгруженные файлы:
{code}
mkdir -p /app/asr_billing/cfg/sorm3/
mkdir -p /app/asr_billing/cfg/sorm3/conf/
mkdir -p /app/asr_billing/cfg/sorm3/data/
mkdir -p /app/asr_billing/cfg/sorm3/done/
mkdir -p /app/asr_billing/cfg/sorm3/Log/
{code}
# Создайте файл *ftp.conf* c данными для подключения к FTP-серверу СОРМ в директории */app/asr_billing/cfg/sorm3/conf/*. Пример содержимого файла */app/asr_billing/cfg/sorm3/conf/ftp.conf*:
{code}
ftpip=192.168.1.10
ftplogin=sorm
ftppass=sormpass
{code}
# Добавьте все отчеты, получающие данные из биллинга, а также скрипты для первоначальной выгрузки данных в папку */app/asr_billing/cfg/sorm3/*. Листинги отчетов и пути приведены далее в статье. Должна получиться следующая структура каталога:
{code}
/app/asr_billing/cfg/sorm3/conf
/app/asr_billing/cfg/sorm3/data
/app/asr_billing/cfg/sorm3/deleted_abonents.sh
/app/asr_billing/cfg/sorm3/deleted_abonents.sql
/app/asr_billing/cfg/sorm3/done
/app/asr_billing/cfg/sorm3/fixednetid.sql
/app/asr_billing/cfg/sorm3/fixednetid.sh
/app/asr_billing/cfg/sorm3/ip_numbering.sh
/app/asr_billing/cfg/sorm3/ip_numbering.sql
/app/asr_billing/cfg/sorm3/ip.sh
/app/asr_billing/cfg/sorm3/ip.sql
/app/asr_billing/cfg/sorm3/Log
/app/asr_billing/cfg/sorm3/payments-periodic.sql
/app/asr_billing/cfg/sorm3/payments.sh
/app/asr_billing/cfg/sorm3/payments.sql
/app/asr_billing/cfg/sorm3/service_list-periodic.sql
/app/asr_billing/cfg/sorm3/service_list.sh
/app/asr_billing/cfg/sorm3/service_list.sql
/app/asr_billing/cfg/sorm3/services-periodic.sql
/app/asr_billing/cfg/sorm3/services.sh
/app/asr_billing/cfg/sorm3/services.sql
/app/asr_billing/cfg/sorm3/subscribers_v2-periodic.sql
/app/asr_billing/cfg/sorm3/subscribers_v2.sh
/app/asr_billing/cfg/sorm3/subscribers_v2.sql
/app/asr_billing/cfg/sorm3/upload_all.sh
{code}
# Добавьте задания в планировщик задач и перезапустите его.
# Проведите первоначальное наполнение данными
h1. Скрипт для планировщика задач
Скрипт выгружает изменения по абонентам, услугам и платежам за последний час.
*/etc/cron.hourly/vasexperts_sorm_hourly.sh*
{code}
#/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/payments-periodic.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/payments_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
fi
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/services-periodic.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/services_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/services_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/services_$file_date.txt /app/asr_billing/cfg/sorm3/done/services_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
fi
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/service_list-periodic.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/service_list_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
fi
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/subscribers_v2-periodic.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt /app/asr_billing/cfg/sorm3/done/subscribers_v2_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
fi
{code}
После сохранения скриптов, установите на обоих права на выполнение и перезапустите планировщик:
{code}
chmod a+x /etc/cron.hourly/vasexperts_sorm_hourly.sh
/etc/init.d/crond restart
{code}
h1. Скрипты для первоначальной выгрузки данных
Скрипты производят первичное наполнение СОРМ данными, выгружая как динамически формируемую информацию (абоненты, услуги, пополнения балансов и тд), так и статичные данные (платежные системы, план нумерации, виды документов).
Когда все статичные данные будут подготовлены и сохранены на сервере, сохраните скрипты по указанным путям. Далее, разрешите их выполнение и выполните скрипт */app/asr_billing/cfg/sorm3/upload_all.sh*
{code}
chmod a+x /app/asr_billing/cfg/sorm3/deleted_abonents.sh
chmod a+x /app/asr_billing/cfg/sorm3/fixednetid.sh
chmod a+x /app/asr_billing/cfg/sorm3/ip_numbering.sh
chmod a+x /app/asr_billing/cfg/sorm3/ip.sh
chmod a+x /app/asr_billing/cfg/sorm3/payments.sh
chmod a+x /app/asr_billing/cfg/sorm3/service_list.sh
chmod a+x /app/asr_billing/cfg/sorm3/services.sh
chmod a+x /app/asr_billing/cfg/sorm3/subscribers_v2.sh
chmod a+x /app/asr_billing/cfg/sorm3/upload_all.sh
bash /app/asr_billing/cfg/sorm3/upload_all.sh
{code}
*/app/asr_billing/cfg/sorm3/deleted_abonents.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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' > /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/deleted_abonents.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt /app/asr_billing/cfg/sorm3/done/deleted_abonents_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/deleted_abonents_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/fixednetid.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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' > /app/asr_billing/cfg/sorm3/data/fixednetid_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/fixednetid.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/fixednetid.txt
#chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/fixednetid.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/fixednetid.txt
if [ -f /app/asr_billing/cfg/sorm3/data/fixednetid.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/fixednetid_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/fixednetid.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/fixednetid.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/fixednetid.txt /app/asr_billing/cfg/sorm3/done/fixednetid_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/fixednetid.txt ftp://$ftpip/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/fixednetid.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/ip_numbering.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/ip_numbering.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ip_numbering.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt /app/asr_billing/cfg/sorm3/done/ip_numbering_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt ftp://$ftpip/ISP/dict/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/ip_numbering_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/ip.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/ip_nets_v1.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/ip.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/ip_nets_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/ip_nets_v1_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/payments.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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' > /app/asr_billing/cfg/sorm3/data/subscribers_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/payments.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/payments_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/payments_v1_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/service_list.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/service_list.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/service_list.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt /app/asr_billing/cfg/sorm3/done/service_list_v1_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/service_list_v1_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/services.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/abonents.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/services.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/service_list_$file_date.txt
chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/services.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/services_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/services_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/services_$file_date.txt /app/asr_billing/cfg/sorm3/done/services_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/services_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/services_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/subscribers_v2.sh*
{code}
#!/bin/bash
source /app/asr_billing/cfg/sorm3/conf/ftp.conf
LOGFILE=/app/asr_billing/mnt/var/cfg/sorm3/Log/subscribers_v2.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/subscribers_v2.sql | sed '/^$/d'| sed 's/ */ /g' > /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
#chroot /app/asr_billing/ isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/subscribers_v2.sql | sed '/^$/d'| sed 's/ */ /g' | iconv -f cp1251 -t utf8 > /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
if [ -f /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ]
then
# sed -i '1d' /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt /app/asr_billing/cfg/sorm3/done/subscribers_v2_$file_date.txt
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt ftp://$ftpip/ISP/abonents/ >> $LOGFILE 2>&1
rm -rf /app/asr_billing/cfg/sorm3/data/subscribers_v2_$file_date.txt
fi
{code}
*/app/asr_billing/cfg/sorm3/upload_all.sh*
{code}
#!/bin/bash
/app/asr_billing/cfg/sorm3/subscribers_v2.sh
/app/asr_billing/cfg/sorm3/deleted_abonents.sh
/app/asr_billing/cfg/sorm3/payments.sh
/app/asr_billing/cfg/sorm3/services.sh
/app/asr_billing/cfg/sorm3/ip.sh
/app/asr_billing/cfg/sorm3/ip_numbering.sh
/app/asr_billing/cfg/sorm3/service_list.sh
/app/asr_billing/cfg/sorm3/fixednetid.sh
{code}
h1. Отчеты
h2. 1. Плоский формат выгрузки абонентских данных
*/app/asr_billing/cfg/sorm3/subscribers_v2.sql*
{code}
set heading off;select distinct '"0";"' || --стандарт связи абонента--
A.ID || '";"' || --id абонента --
A.ID || '";"' || --логин--
A.CONTRACT_NUMBER || '";"' || --номер договора--
case when A.DELETED=1 then '1' --текущий статус абонента, справочник, 0 – подключен 1 - отключен--
else '0' end || '";"' ||
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)) || ':00' || '";"' || --дата заключения договора--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM AH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' || from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '";"'|| --дата расторжения договора, дата, формат DD.mm.YYYY HH:MM:SS, для активных абонентов пустая строка--
coalesce(A.COMPANY,'0') || '";"' || --тип абонента, справочник, 0 - физическое лицо, 1 - юридическое лицо--
case when coalesce(A.COMPANY,'0')=0 then '1' --тип данных по ФИО, справочник, 0 - структурированные данные, 1 -неструктурированные, обязателен для физических лиц--
else ' ' end || '";"' ||
'' || '";"' ||
'' || '";"' ||
'' || '";"' ||
case when A.COMPANY=0 then A.NAME || '";"' ||--неструктурированное ФИО--
--iif (position('.19' in av1020.attribute_value) !=0, coalesce(left(replace(replace (replace(av1020.attribute_value, '«', ''),'»',''), ' ', ''),10),''), '') --дата рождения --
iif (coalesce(av22.attribute_value,'')='','',av22.attribute_value) --дата рождения
else '";"' end || '";"' ||
--паспортные данные --
case when A.COMPANY=0 then
'0' || '";"' ||
coalesce(av14.attribute_value, '') || '";"' || --паспорт серия
coalesce(av13.attribute_value, '') || '";"' || --паспорт номер
--coalesce(av17.attribute_value, '') || '";"' || --когда выдан
iif (av17.attribute_value like '%-%',substring(av17.attribute_value from 9 for 2) || '.' || substring (av17.attribute_value from 6 for 2) || '.' || substring (av17.attribute_value from 1 for 4) ,coalesce(av17.attribute_value, '')) || '";"' || --паспорт когда выдан--
iif (coalesce(av16.attribute_value, '') <> '', av16.attribute_value || ' ', '')|| '";"' || --кем выдан
''|| '";"' || --неструктурированные паспортные данные, зарезервировано на будущее - пустая строка--
'Паспорт РФ' || '";"' || --тип документа--
coalesce(av7.attribute_value,'') || '";"' ||--банк абонента--
coalesce(av6.attribute_value,'') --р/с абонента --
else '";"";"";"";"";"";"";"";"' end || '";"' ||
case when A.COMPANY=1 then A.NAME || '";"' || --название ЮР лица --
coalesce(av4.attribute_value,'') || '";"' || '";"' || '";"' ||coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') --ИНН,контакты,банк,р/с юр.лица --
else '";"";"";"";"";"' end || '";"' ||
case when a.company=1 then
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av25.attribute_value,'') --адрес юр лица--
else iif(coalesce(av15.attribute_value,'') <> '' and char_length(av15.attribute_value) between 4 and 5, '0' || '";"' || (select IIF(coalesce(h.ZIP_CODE, '') <> '', h.zip_code, '') || '";"'
|| IIF(coalesce(h.country, '') <> '', h.country, 'Россия') || '";"' || iif(coalesce(h.region_type_id, '') <> '',htn_region.SHORT, '') || '";"' || IIF(coalesce(h.REGION, '') <> '', h.REGION, '')
|| '";"' || iif(coalesce(h.DISTRICT_type_id, '') <> '',htn_district.SHORT, '') || '";"' || IIF(coalesce(h.DISTRICT, '') <> '', h.DISTRICT, '') || '";"'
|| iif(coalesce(h.city_type_id, '') <> '',htn_city.SHORT, 'г') || '";"' || IIF(coalesce(h.CITY, '') <> '', h.CITY, '') || '";"'
|| iif(coalesce(h.street_type_id, '') <> '',htn_street.SHORT, 'ул')|| '";"' || IIF(coalesce(h.STREET,'') <> '', h.STREET, '') || '";"'
|| coalesce(h.S_NUMBER,'') || '";"' || IIF(coalesce(h.S_LITER,'') <> '',h.S_LITER, '') || '";"'
|| '";"' || IIF(coalesce(a.A_HOME_NUMBER, '') <> '',a.A_HOME_NUMBER,'') || '";"' from homes h
left join home_type_name htn_region on h.REGION_TYPE_ID=htn_region.id
left join home_type_name htn_district on h.DISTRICT_TYPE_ID=htn_district.id
left join home_type_name htn_city on h.CITY_TYPE_ID =htn_city.id
left join home_type_name htn_street on h.STREET_TYPE_ID =htn_street.id
where h.id=av15.attribute_value),'0";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' || coalesce(av26.attribute_value,''))
end || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av26.attribute_value,'') || --адрес установки оборудования --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --почтовый адрес абонента --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --адрес доставки счета --
'";"' ||
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) || 'T'
|| 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))||':'|| SUBSTRING(a.create_date from 18 for 2)|| '";"' || --дата начала интервала, на котором актуальна информация--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'||
EXTRACT(YEAR FROM AH.TIME_CHANGED) || 'T00:00:00' from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '"' --дата расторжения договора, дата, формат DD.mm.YYYY, для активных абонентов пустая строка--
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
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 av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
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=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av26 on a.id=av26.ABONENT_ID and av26.ATTRIBUTE_ID=26
left join ATTRIBUTE_VALUES as av27 on a.id=av27.ABONENT_ID and av27.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES as av34 on a.id=av34.ABONENT_ID and av34.ATTRIBUTE_ID=34
left join ATTRIBUTE_VALUES as av1011 on a.id=av1011.ABONENT_ID and av1011.ATTRIBUTE_ID=1011
left join ATTRIBUTE_VALUES as av1019 on a.id=av1019.ABONENT_ID and av1019.ATTRIBUTE_ID=1019
left join ATTRIBUTE_VALUES as av1020 on a.id=av1020.ABONENT_ID and av1020.ATTRIBUTE_ID=1020
where A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id >0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/subscribers_v2-periodic.sql*
{code}
set heading off;select distinct '"0";"' || --стандарт связи абонента--
A.ID || '";"' || --id абонента --
A.ID || '";"' || --логин--
A.CONTRACT_NUMBER || '";"' || --номер договора--
case when A.DELETED=1 then '1' --текущий статус абонента, справочник, 0 – подключен 1 - отключен--
else '0' end || '";"' ||
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)) || ':00' || '";"' || --дата заключения договора--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM AH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' || from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '";"'|| --дата расторжения договора, дата, формат DD.mm.YYYY HH:MM:SS, для активных абонентов пустая строка--
coalesce(A.COMPANY,'0') || '";"' || --тип абонента, справочник, 0 - физическое лицо, 1 - юридическое лицо--
case when coalesce(A.COMPANY,'0')=0 then '1' --тип данных по ФИО, справочник, 0 - структурированные данные, 1 -неструктурированные, обязателен для физических лиц--
else ' ' end || '";"' ||
'' || '";"' ||
'' || '";"' ||
'' || '";"' ||
case when A.COMPANY=0 then A.NAME || '";"' ||--неструктурированное ФИО--
--iif (position('.19' in av1020.attribute_value) !=0, coalesce(left(replace(replace (replace(av1020.attribute_value, '«', ''),'»',''), ' ', ''),10),''), '') --дата рождения --
iif (coalesce(av22.attribute_value,'')='','',av22.attribute_value) --дата рождения
else '";"' end || '";"' ||
--паспортные данные --
case when A.COMPANY=0 then
'0' || '";"' ||
coalesce(av14.attribute_value, '') || '";"' || --паспорт серия
coalesce(av13.attribute_value, '') || '";"' || --паспорт номер
--coalesce(av17.attribute_value, '') || '";"' || --когда выдан
iif (av17.attribute_value like '%-%',substring(av17.attribute_value from 9 for 2) || '.' || substring (av17.attribute_value from 6 for 2) || '.' || substring (av17.attribute_value from 1 for 4) ,coalesce(av17.attribute_value, '')) || '";"' || --паспорт когда выдан--
iif (coalesce(av16.attribute_value, '') <> '', av16.attribute_value || ' ', '')|| '";"' || --кем выдан
''|| '";"' || --неструктурированные паспортные данные, зарезервировано на будущее - пустая строка--
'Паспорт РФ' || '";"' || --тип документа--
coalesce(av7.attribute_value,'') || '";"' ||--банк абонента--
coalesce(av6.attribute_value,'') --р/с абонента --
else '";"";"";"";"";"";"";"";"' end || '";"' ||
case when A.COMPANY=1 then A.NAME || '";"' || --название ЮР лица --
coalesce(av4.attribute_value,'') || '";"' || '";"' || '";"' ||coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') --ИНН,контакты,банк,р/с юр.лица --
else '";"";"";"";"";"' end || '";"' ||
case when a.company=1 then
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av25.attribute_value,'') --адрес юр лица--
else iif(coalesce(av15.attribute_value,'') <> '' and char_length(av15.attribute_value) between 4 and 5, '0' || '";"' || (select IIF(coalesce(h.ZIP_CODE, '') <> '', h.zip_code, '') || '";"'
|| IIF(coalesce(h.country, '') <> '', h.country, 'Россия') || '";"' || iif(coalesce(h.region_type_id, '') <> '',htn_region.SHORT, '') || '";"' || IIF(coalesce(h.REGION, '') <> '', h.REGION, '')
|| '";"' || iif(coalesce(h.DISTRICT_type_id, '') <> '',htn_district.SHORT, '') || '";"' || IIF(coalesce(h.DISTRICT, '') <> '', h.DISTRICT, '') || '";"'
|| iif(coalesce(h.city_type_id, '') <> '',htn_city.SHORT, 'г') || '";"' || IIF(coalesce(h.CITY, '') <> '', h.CITY, '') || '";"'
|| iif(coalesce(h.street_type_id, '') <> '',htn_street.SHORT, 'ул')|| '";"' || IIF(coalesce(h.STREET,'') <> '', h.STREET, '') || '";"'
|| coalesce(h.S_NUMBER,'') || '";"' || IIF(coalesce(h.S_LITER,'') <> '',h.S_LITER, '') || '";"'
|| '";"' || IIF(coalesce(a.A_HOME_NUMBER, '') <> '',a.A_HOME_NUMBER,'') || '";"' from homes h
left join home_type_name htn_region on h.REGION_TYPE_ID=htn_region.id
left join home_type_name htn_district on h.DISTRICT_TYPE_ID=htn_district.id
left join home_type_name htn_city on h.CITY_TYPE_ID =htn_city.id
left join home_type_name htn_street on h.STREET_TYPE_ID =htn_street.id
where h.id=av15.attribute_value),'0";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' || coalesce(av26.attribute_value,''))
end || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av26.attribute_value,'') || --адрес установки оборудования --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --почтовый адрес абонента --
'' || '";"' ||
'1' || '";"' || --тип данных адреса абонента--
'' || '";"' || --индекс--
'' || '";"' || --страна--
'' || '";"' || --тип области--
'' || '";"' || --область--
'' || '";"' || --тип района--
'' || '";"' || --район--
'' || '";"' || --тип города--
'' || '";"' || --город--
'' || '";"' || --тип улицы--
'' || '";"' || --улица--
'' || '";"' || --номер дома--
'' || '";"' || --корпус--
'' || '";"' || --тип квартиры--
'' || '";"' || --квартира--
coalesce(av27.attribute_value,'') || --адрес доставки счета --
'";"' ||
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) || 'T'
|| 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))||':'|| SUBSTRING(a.create_date from 18 for 2)|| '";"' || --дата начала интервала, на котором актуальна информация--
case when A.DELETED=1 then
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'||
EXTRACT(YEAR FROM AH.TIME_CHANGED) || 'T00:00:00' from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1)
else '' end || '"' --дата расторжения договора, дата, формат DD.mm.YYYY, для активных абонентов пустая строка--
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
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 av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
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=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av26 on a.id=av26.ABONENT_ID and av26.ATTRIBUTE_ID=26
left join ATTRIBUTE_VALUES as av27 on a.id=av27.ABONENT_ID and av27.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES as av34 on a.id=av34.ABONENT_ID and av34.ATTRIBUTE_ID=34
left join ATTRIBUTE_VALUES as av1011 on a.id=av1011.ABONENT_ID and av1011.ATTRIBUTE_ID=1011
left join ATTRIBUTE_VALUES as av1019 on a.id=av1019.ABONENT_ID and av1019.ATTRIBUTE_ID=1019
left join ATTRIBUTE_VALUES as av1020 on a.id=av1020.ABONENT_ID and av1020.ATTRIBUTE_ID=1020
where A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id >0
and (u.login in (select uh.login from users_history uh where uh.time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp)
or a.id in (select ah.abonents_id from abonents_history ah where ah.time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp));
{code}
h2. 2. Выгрузка номенклатуры услуг оператора
*/app/asr_billing/cfg/sorm3/service_list.sql*
{code}
set heading off; select
'"' || U.ID || '";"' || --id услуги --
iif (U.NAME='', 'Удаленная услуга', U.NAME) || '";"' || --имя услуги --
coalesce(U.COMMENTS,'') || '";"' || --описание услуги --
coalesce((
select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
),'01.01.2018') || '";"' || --дата создания услуги (дата активации услуги)--
case
when U.DELETED=1
then coalesce ((select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
and UH.deleted=1
),'01.01.2018')
else ''
end || '";"' || --дата удаления услуги (дата деактивации услуги)-- */
'Ethernet"' --тип сети
from
USLUGA U
where u.id>0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/service_list-periodic.sql*
{code}
set heading off; select
'"' || U.ID || '";"' || --id услуги --
iif (U.NAME='', 'Удаленная услуга', U.NAME) || '";"' || --имя услуги --
coalesce(U.COMMENTS,'') || '";"' || --описание услуги --
coalesce((
select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
),'01.01.2018') || '";"' || --дата создания услуги (дата активации услуги)--
case
when U.DELETED=1
then coalesce ((select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED)
from
usluga_history uh
where
uh.usluga_id = u.id
and UH.deleted=1
),'01.01.2018')
else ''
end || '";"' || --дата удаления услуги (дата деактивации услуги)-- */
'Ethernet"' --тип сети
from
USLUGA U
where u.id>0
and (u.id in (select usluga_id from usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp));
{code}
h2. 3. Выгрузка услуг по абонентам
*/app/asr_billing/cfg/sorm3/services.sql*
{code}
set heading off; select distinct
'"' || U.ID || '";"' || --id услуги --
A.ID || '";"' || --id абонента--
'";"";"' || '";"' || -- ID лицевого счёта, ID точки подключения, ID договора--
U.NAME || '";"' || --имя услуги --
coalesce(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(DAY FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.CREATE_DATE)) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.CREATE_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.CREATE_DATE),2,'0') AS varchar(2)) || ':00' || '";"' || --дата создания услуги--
case when UU.DELETED=1 then
coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UUH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.NEXT_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.NEXT_DATE),2,'0') AS varchar(2)) || ':00' )
else ' ' end || '"' --дата удаления услуги--
from USERS_USLUGA UU
join abonents a on a.id=uu.abonent_id
join usluga u on uu.usluga_id = u.id
where a.is_folder=0
and a.id >0
and a.parent_id not in (2,244)
and u.id > 0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/services-periodic.sql*
{code}
set heading off; select distinct
'"' || U.ID || '";"' || --id услуги --
A.ID || '";"' || --id абонента--
'";"";"' || '";"' || -- ID лицевого счёта, ID точки подключения, ID договора--
U.NAME || '";"' || --имя услуги --
coalesce(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(DAY FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.CREATE_DATE)) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.CREATE_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.CREATE_DATE),2,'0') AS varchar(2)) || ':00' || '";"' || --дата создания услуги--
case when UU.DELETED=1 then
coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UUH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.NEXT_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.NEXT_DATE),2,'0') AS varchar(2)) || ':00' )
else ' ' end || '"' --дата удаления услуги--
from USERS_USLUGA UU
join abonents a on a.id=uu.abonent_id
join usluga u on uu.usluga_id = u.id
where a.is_folder=0
and a.id >0
and a.parent_id not in (2,244)
and u.id > 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. 4. Выгрузка платежей абонентов
*/app/asr_billing/cfg/sorm3/payments.sql*
{code}
set heading off; select distinct
'"' || A.ID || '";"' || --id абонента --
'";"' || --код абонента, зарезервировано на будущее--
-- fo.OP_DATE || '";"' || --Дата платежа--
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) || 'T' ||
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))||':'|| lpad(CAST(EXTRACT(SECOND FROM fo.OP_DATE) as integer),2,'0') || '";"' ||
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '";"' || --Сумма платежа--
iif(fo.OPERATOR_NAME is not null and fo.OPERATOR_NAME<>'',fo.OPERATOR_NAME,'Admin')|| '";"' ||
coalesce (fo.ADMIN_COMMENT,'') || '"' --раметры платежа, строка, размер 512, дополнительная информация по платежу в неструктурированном виде, при её наличии--
from abonents a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null and a.id>0;
{code}
Тот же отчёт, в который попадает только информация, добавленная за последний час.
*/app/asr_billing/cfg/sorm3/payments-periodic.sql*
{code}
set heading off; select distinct
'"' || A.ID || '";"' || --id абонента --
'";"' || --код абонента, зарезервировано на будущее--
-- fo.OP_DATE || '";"' || --Дата платежа--
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) || 'T' ||
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))||':'|| lpad(CAST(EXTRACT(SECOND FROM fo.OP_DATE) as integer),2,'0') || '";"' ||
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '";"' || --Сумма платежа--
iif(fo.OPERATOR_NAME is not null and fo.OPERATOR_NAME<>'',fo.OPERATOR_NAME,'Admin')|| '";"' ||
coalesce (fo.ADMIN_COMMENT,'') || '"' --раметры платежа, строка, размер 512, дополнительная информация по платежу в неструктурированном виде, при её наличии--
from abonents a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id>0
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. 5. Формат выгрузки на удаление абонентов из БД
*/app/asr_billing/cfg/sorm3/deleted_abonents.sql*
{code}
set heading off;
select '"' ||
id || '";""'
from abonents
where
is_folder=0
and deleted=1;
{code}
h2. 6. Выгрузка IP адресов
*/app/asr_billing/cfg/sorm3/ip.sql*
В отчете приведен пример интервала белых ip(U.IP between 1541441536 and 1541442047). Вам требуется самостоятельно определить белые ip-адреса с помощью [конвертера|https://www.browserling.com/tools/ip-to-dec]
{code}
set heading off; select distinct '"' || '";"' || --зарезервировано на будущее--
A.ID || '";"' || --id абонента --
uf_ip2string(U.IP) || '";"' || --ip абонента --
case when U.IP between 1541441536 and 1541442047 then '1' --Здесь приведен пример интервала белых ip --
else '0' end || '";"' ||
(select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED) ||'T'|| CAST(lpad(EXTRACT(HOUR FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(MINUTE FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| SUBSTRING(uh.time_changed from 18 for 2) from users_history UH where UH.IP=U.IP and UH.USERS_ID=U.ID) || '";"' ||
''|| '";"' ||
U.MAC ||
'"'
from abonents A
join USERS U on U.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and a.parent_id not in (2,244)
and a.id is not null
and a.id > 0
and U.IP != 0;
{code}
h2. 7. Выгрузка данных по IP-нумерации оператора
*/app/asr_billing/cfg/sorm3/ip_numbering.sql*
{code}
set heading off; select '"' ||
uf_ip2string(NETWORK_ADDRESS) || '/' ||
mask || '";"' ||
name || '-Статические адреса для абонентов' || '";"' ||
'01.01.2017T00:00:00' || '";""'
from ip_pull
where pull_id not in (1,2,3,4,5,6,7);
{code}
h2. 8. Выгрузка привязки ip адреса к логину
*/app/asr_billing/cfg/sorm3/fixednetid.sql*
{code}
set heading off; select
u.abonent_id || ';' ||
uf_ip2string(u.ip) || '/' || coalesce(mask,'32')
from users u
right join abonents a on a.id=u.abonent_id and a.deleted=0
where
u.ip is not null
and u.abonent_id>=1000;
{code}