Интеграция с СОРМ 3 Яхонт компании НОРСИ-ТРАНС

{toc}

{tip}*Время выполнения инструкции*: 20-40 минут, основное время занимает настройка, сама выгрузка должна укладываться в 10 минут, точное время зависит от размера базы{tip}
h1. Выгрузка на СОРМ3

h2. Принцип работы

Для интеграции с СОРМ3 Норси-Транс требуется произвести первичную выгрузку данных, а так же добавить сценарии планировщика задач, которые будут выгружать добавленные и изменённые данные с определённой периодичностью: один - раз в сутки (данные из карточек абонентов), и один - раз в час (все прочие данные).
При изменении списка используемых документов, платежных систем или плана ip-адрессации, измените соответствущие *csv* файлы и повторно выполните скрипт *initial-static-data.sh*. При выгрузке изменённого *pay-types.csv* убедитесь, что не перепутались id платежных систем - в таком случае файл нужно будет исправить вручную.

Для автоматизации передачи отчетов на СОРМ3 сделайте следующее:
# Создайте папку *sorm3* и необходимые подпапки, в них будут храниться отчеты для выгрузки, а так же выгруженные файлы:
{code}mkdir -p /app/asr_billing/cfg/sorm3/data/done
mkdir -p /app/asr_billing/cfg/sorm3/data/static-data{code}
# Добавьте все отчеты, получающие данные из биллинга, в папку */app/asr_billing/cfg/sorm3/*. Листинги отчетов и пути приведены далее в статье. Должна получиться следующая структура каталога:
{code}/app/asr_billing/cfg/sorm3/abonents-periodic.sql
/app/asr_billing/cfg/sorm3/abonents.sql
/app/asr_billing/cfg/sorm3/balance-fillup-periodic.sql
/app/asr_billing/cfg/sorm3/balance-fillup.sql
/app/asr_billing/cfg/sorm3/data
/app/asr_billing/cfg/sorm3/initial-static-data.sh
/app/asr_billing/cfg/sorm3/initial-unloaded-data.sh
/app/asr_billing/cfg/sorm3/services-periodic-id.sql
/app/asr_billing/cfg/sorm3/services.sh
/app/asr_billing/cfg/sorm3/services.sql
/app/asr_billing/cfg/sorm3/supplement-services-periodic.sql
/app/asr_billing/cfg/sorm3/supplement-services.sql{code}
# Добавьте отчеты со статичными данными в папку */app/asr_billing/cfg/sorm3/data/static-data*: справочник типов документов, план адресации и список платежных систем
# Добавьте задания в планировщик задач и перезапустите его.
# Проведите первоначальное наполнение данными

Примечания:
* Данные, выгруженные по расписанию, сохраняются в каталоге */app/asr_billing/cfg/sorm3/data/done*
* Путь к лог-файлу выгрузки */app/asr_billing/var/log/sorm-upload.log*

h2. Скрипты для планировщика задач

Первый скрипт выгружает изменение по абонентам, измененным за последние сутки, второй - все остальное, раз в час.
В переменные ftpip, ftplogin и ftppass нужно подставить данные доступа к Вашем СОРМ.

*/etc/cron.daily/40_sorm_daily.sh*
{code}#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log

ftpip=192.168.0.1
ftplogin=user
ftppass=password

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-periodic.sql | sed '/^$/d'| sed 's/ *//g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv
if [ -s /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv /app/asr_billing/cfg/sorm3/data/done/abonents-periodic.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv ftp://$ftpip/abonents/abonents/ >> $LOGFILE 2>&1
>| /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv
printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/abonents-periodic.csv >> $LOGFILE 2>&1
fi{code}

*/etc/cron.hourly/40_sorm_hourly.sh*
{code}#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log

ftpip=192.168.0.1
ftplogin=user
ftppass=password

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/balance-fillup-periodic.sql | sed '/^$/d'| sed 's/ *//g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv
if [ -s /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv /app/asr_billing/cfg/sorm3/data/done/balance-fillup-periodic.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv ftp://$ftpip/payments/balance-fillup/ >> $LOGFILE 2>&1
>| /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv
printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/balance-fillup-periodic.csv >> $LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/supplement-services-periodic.sql | sed '/^$/d'| sed 's/ *//g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv
if [ -s /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv ]
then
printf 'Метаданные файла\n'
ls -l /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv
printf 'Количество строк в файле: ' && cat /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv | wc -l
cp /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv /app/asr_billing/cfg/sorm3/data/done/supplement-services-periodic.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv ftp://$ftpip/dictionaries/supplement-services/
>| /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv
printf 'Проверим что файл очищен\n'
ls -l /app/asr_billing/cfg/sorm3/data/supplement-services-periodic.csv
fi

chroot /app/asr_billing bash /cfg/sorm3/services.sh
if [ -s /app/asr_billing/cfg/sorm3/data/services-periodic.csv ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/services-periodic.csv >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/services-periodic.csv | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/services-periodic.csv /app/asr_billing/cfg/sorm3/data/done/services-periodic.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/services-periodic.csv ftp://$ftpip/services/services/ >> $LOGFILE 2>&1
>| /app/asr_billing/cfg/sorm3/data/services-periodic.csv
>| /app/asr_billing/cfg/sorm3/data/services-periodic-temp.csv
printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/services-periodic.csv >> $LOGFILE 2>&1
fi

exit 0{code}
После сохранения скриптов, установите на обоих права на выполнение и перезапустите планировщик:
{code}
chmod a+x /etc/cron.hourly/40_sorm_hourly.sh
chmod a+x /etc/cron.daily/40_sorm_daily.sh
/etc/init.d/crond restart
{code}

h2. Скрипты первоначальной выгрузки данных

Скрипты производят первичное наполнение СОРМ данными, выгружая как динамически формируемую информацию (абоненты, услуги, пополнения балансов и тд), так и статичные данные (платежные системы, план нумерации, виды документов).
Когда все статичные данные будут подготовлены и сохранены на сервере, сохраните скрипты по указанным путям. Далее, разрешите их выполнение и выполните:
{code}
chmod a+x /app/asr_billing/cfg/sorm3/initial-static-data.sh
chmod a+x /app/asr_billing/cfg/sorm3/initial-unloaded-data.sh
bash /app/asr_billing/cfg/sorm3/initial-static-data.sh
bash /app/asr_billing/cfg/sorm3/initial-unloaded-data.sh{code}

*/app/asr_billing/cfg/sorm3/initial-unloaded-data.sh*
{code}#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log

ftpip=192.168.0.1
ftplogin=user
ftppass=password

printf '\n\n' >> $LOGFILE 2>&1
date >> $LOGFILE 2>&1

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/abonents.sql | sed '/^$/d'| sed 's/ *//g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/abonents.csv
if [ -s /app/asr_billing/cfg/sorm3/data/abonents.csv ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/abonents.csv >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/abonents.csv | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/abonents.csv /app/asr_billing/cfg/sorm3/data/done/abonents.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/abonents.csv ftp://$ftpip/abonents/abonents/ >> $LOGFILE 2>&1
>| /app/asr_billing/cfg/sorm3/data/abonents.csv
printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/abonents.csv >> $LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/balance-fillup.sql | sed '/^$/d'| sed 's/ *//g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/balance-fillup.csv
if [ -s /app/asr_billing/cfg/sorm3/data/balance-fillup.csv ]
then
printf 'Метаданные файла\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/balance-fillup.csv >> $LOGFILE 2>&1
printf 'Количество строк в файле: ' >> $LOGFILE 2>&1 && cat /app/asr_billing/cfg/sorm3/data/balance-fillup.csv | wc -l >> $LOGFILE 2>&1
cp /app/asr_billing/cfg/sorm3/data/balance-fillup.csv /app/asr_billing/cfg/sorm3/data/done/balance-fillup.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/balance-fillup.csv ftp://$ftpip/payments/balance-fillup/ >> $LOGFILE 2>&1
>| /app/asr_billing/cfg/sorm3/data/balance-fillup.csv
printf 'Проверим что файл очищен\n' >> $LOGFILE 2>&1
ls -l /app/asr_billing/cfg/sorm3/data/balance-fillup.csv >> $LOGFILE 2>&1
fi

chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/supplement-services.sql | sed '/^$/d'| sed 's/ *//g' | iconv -f utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/supplement-services.csv
if [ -s /app/asr_billing/cfg/sorm3/data/supplement-services.csv ]
then
printf 'Метаданные файла\n'
ls -l /app/asr_billing/cfg/sorm3/data/supplement-services.csv
printf 'Количество строк в файле: ' && cat /app/asr_billing/cfg/sorm3/data/supplement-services.csv | wc -l
cp /app/asr_billing/cfg/sorm3/data/supplement-services.csv /app/asr_billing/cfg/sorm3/data/done/supplement-services.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/supplement-services.csv ftp://$ftpip/dictionaries/supplement-services/
>| /app/asr_billing/cfg/sorm3/data/supplement-services.csv
printf 'Проверим что файл очищен\n'
ls -l /app/asr_billing/cfg/sorm3/data/supplement-services.csv
fi

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 utf8 -t cp1251 > /app/asr_billing/cfg/sorm3/data/services.csv
if [ -s /app/asr_billing/cfg/sorm3/data/services.csv ]
then
printf 'Метаданные файла\n'
ls -l /app/asr_billing/cfg/sorm3/data/services.csv
printf 'Количество строк в файле: ' && cat /app/asr_billing/cfg/sorm3/data/services.csv | wc -l
cp /app/asr_billing/cfg/sorm3/data/services.csv /app/asr_billing/cfg/sorm3/data/done/services.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/services.csv ftp://$ftpip/abonents/services/
>| /app/asr_billing/cfg/sorm3/data/services.csv
printf 'Проверим что файл очищен\n'
ls -l /app/asr_billing/cfg/sorm3/data/services.csv
fi
{code}

*/app/asr_billing/cfg/sorm3/initial-static-data.sh*
{code}#/bin/bash

LOGFILE=/app/asr_billing/var/log/sorm-upload.log

ftpip=192.168.0.1
ftplogin=user
ftppass=password

printf '\n\n' >> $LOGFILE 2>&1
date >> $LOGFILE 2>&1

if [ -s /app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv ]
then
printf 'Метаданные файла\n'
ls -l /app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv
printf 'Количество строк в файле: ' && cat /app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv | wc -l
cp /app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv /app/asr_billing/cfg/sorm3/data/done/services.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv ftp://$ftpip/dictionaries/supplement-services/
>| /app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv
printf 'Проверим что файл очищен\n'
ls -l /app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv
fi

if [ -s /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv ]
then
printf 'Метаданные файла\n'
ls -l /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv
printf 'Количество строк в файле: ' && cat /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv | wc -l
cp /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv /app/asr_billing/cfg/sorm3/data/done/services.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv ftp://$ftpip/dictionaries/supplement-services/
>| /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv
printf 'Проверим что файл очищен\n'
ls -l /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv
fi

if [ -s /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv ]
then
printf 'Метаданные файла\n'
ls -l /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv
printf 'Количество строк в файле: ' && cat /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv | wc -l
cp /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv /app/asr_billing/cfg/sorm3/data/done/services.csv-`date +"%Y%m%d_%H%M%S"`
curl -v -sS --user $ftplogin:$ftppass -T /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv ftp://$ftpip/dictionaries/supplement-services/
>| /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv
printf 'Проверим что файл очищен\n'
ls -l /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv
fi{code}

h1. Отчеты


h2. 1. Данные об абонентах физических и юридических лицах по договорам на оказание услуг передачи данных.

*/app/asr_billing/cfg/sorm3/abonents.sql*
{code}set heading off; select distinct '"' ||
U.LOGIN || '";"' ||
iif(coalesce(u.ip,'') <> '', UF_IP2STRING(U.IP), '') || '";"' ||
U.MAC || '";"' ||
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || '";"' ||
A.CONTRACT_NUMBER || '";"' ||
case when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then '1' else '0' end || '";"' ||
CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || ' 00:00:00";"' ||
case
when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then
(select first 1 CAST(lpad(EXTRACT(DAY FROM abb.b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM abb.b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM abb.b_date) || ' 00:00:00' from abonents_block abb where abb.abonent_id = a.id and (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1))
else
'' end ||

'";"' ||
A.COMPANY || '";' ||

case when a.company=0 then

'"1";"";"";"";"' ||
A.NAME || '";"' ||
coalesce(av22.attribute_value,'') || '";"1";"";"";"";"' ||

iif(
(coalesce(av14.attribute_value,'') || coalesce(av13.attribute_value,'') || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'')) <> '', iif(coalesce(av14.attribute_value,'') <> '', av14.attribute_value,'') || iif(coalesce(av13.attribute_value,'') <> '', ' ' || av13.attribute_value,'') || iif(coalesce(av16.attribute_value,'') <> '', ' ' || av16.attribute_value,'') || iif(coalesce(av17.attribute_value,'') <> '', ' ' || av17.attribute_value,'')
, '-')

|| '";"1";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' end ||

case when a.company = 1 then

coalesce(av2.attribute_value,'') || '";"' || coalesce(av4.attribute_value,'') || '";"' || coalesce(av32.attribute_value,'-') || '";"' || coalesce(av1.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";' end

|| '"1";"";"";"";"";"";"";"";"";"";"' ||
IIF(a.company = 0, iif(coalesce(av15.attribute_value,'') <> '', (select IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || 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 where h.id=av15.attribute_value),''), coalesce(av25.attribute_value,'')) ||
'";"1";"";"";"";"";"";"";"";"";"";' ||
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || 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 ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av13 on a.id=av13.ABONENT_ID and av13.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av14 on a.id=av14.ABONENT_ID and av14.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av16 on a.id=av16.ABONENT_ID and av16.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av17 on a.id=av17.ABONENT_ID and av17.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join ATTRIBUTE_VALUES as av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join abonents_block ab on ab.abonent_id=a.id
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.deleted=0
and a.id is not null;
{code}

Тот же отчёт, в который попадает только информация, добавленная за последние сутки

*/app/asr_billing/cfg/sorm3/abonents-periodic.sql*
{code}set heading off; select distinct '"' ||
U.LOGIN || '";"' ||
iif(coalesce(u.ip,'') <> '', UF_IP2STRING(U.IP), '') || '";"' ||
U.MAC || '";"' ||
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' 00:00:00' || '";"' ||
A.CONTRACT_NUMBER || '";"' ||
case when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then '1' else '0' end || '";"' ||
CAST(lpad(EXTRACT(DAY FROM a.activate_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.activate_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.activate_date) || ' 00:00:00";"' ||
case
when a.id in (select distinct abonent_id from abonents_block where (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1)) then
(select first 1 CAST(lpad(EXTRACT(DAY FROM abb.b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM abb.b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM abb.b_date) || ' 00:00:00' from abonents_block abb where abb.abonent_id = a.id and (b_own = 1 or b_admin = 1 or b_sys = 1 or b_own2 = 1))
else
'' end ||

'";"' ||
A.COMPANY || '";' ||

case when a.company=0 then

'"1";"";"";"";"' ||
A.NAME || '";"' ||
coalesce(av22.attribute_value,'') || '";"1";"";"";"";"' ||

iif(
(coalesce(av14.attribute_value,'') || coalesce(av13.attribute_value,'') || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'')) <> '', iif(coalesce(av14.attribute_value,'') <> '', av14.attribute_value,'') || iif(coalesce(av13.attribute_value,'') <> '', ' ' || av13.attribute_value,'') || iif(coalesce(av16.attribute_value,'') <> '', ' ' || av16.attribute_value,'') || iif(coalesce(av17.attribute_value,'') <> '', ' ' || av17.attribute_value,'')
, '-')

|| '";"1";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' end ||

case when a.company = 1 then

coalesce(av2.attribute_value,'') || '";"' || coalesce(av4.attribute_value,'') || '";"' || coalesce(av32.attribute_value,'-') || '";"' || coalesce(av1.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";'

else '"";"";"";"";"";"";' end

|| '"1";"";"";"";"";"";"";"";"";"";"' ||
IIF(a.company = 0, iif(coalesce(av15.attribute_value,'') <> '', (select IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || 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 where h.id=av15.attribute_value),''), coalesce(av25.attribute_value,'')) ||
'";"1";"";"";"";"";"";"";"";"";"";' ||
IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || 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 ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ATTRIBUTE_VALUES as av1 on a.id=av1.ABONENT_ID and av1.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av13 on a.id=av13.ABONENT_ID and av13.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av14 on a.id=av14.ABONENT_ID and av14.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av16 on a.id=av16.ABONENT_ID and av16.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av17 on a.id=av17.ABONENT_ID and av17.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join ATTRIBUTE_VALUES as av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join abonents_block ab on ab.abonent_id=a.id
where A.IS_FOLDER=0
and (u.login in (select uh.login from users_history uh where uh.time_changed between (current_timestamp - 1) and current_timestamp)
or a.id in (select ah.abonents_id from abonents_history ah where ah.time_changed between (current_timestamp - 1) and current_timestamp))
and a.parent_id not in (244)
and a.deleted=0
and a.id is not null;{code}

h2. 2. Данные о подключенных абонентам видах услуг связи

{info}В отчет попадают только услуги не входящие в [тариф|CarbonBilling:Тарифы]{info}

Формат "логин";"номер договора";"идентификатор услуги";"дата и время подключения услуги";"дата и время отключения";""

*/app/asr_billing/cfg/sorm3/services.sql*
{code}set heading off; select distinct
'"' ||
(select first 1 U.LOGIN from users u where u.abonent_id = a.id) || '";"' ||
A.CONTRACT_NUMBER || '";"' ||
abs(UU.USLUGA_ID) || '";"' ||
CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE) || ' 00:00:00' || '";"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.END_TIME) || ' ' || EXTRACT(HOUR FROM UU.END_TIME) || ':' || '00:00' ), '') || '";""'
from ABONENTS A
left join USERS_USLUGA UU on UU.ABONENT_ID=A.ID
where
A.IS_FOLDER=0 and
A.PARENT_ID!=244 and
A.DELETED=0 and
UU.TARIF_ID is null and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
UU.USLUGA_ID not in (select id from usluga where name = '') and
a.parent_id not in (244)
order by A.NAME;

{code}
Тот же отчёт, который выводит только услуги подключенные за последний час

*/app/asr_billing/cfg/sorm3/services-periodic-id.sql*
{code}set heading off; select distinct
'"' ||
(select first 1 U.LOGIN from users u where u.abonent_id = a.id) || '";"' ||
A.CONTRACT_NUMBER || '";"' ||
abs(UU.USLUGA_ID) || '";"' ||
CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE) || ' 00:00:00' || '";"'|| coalesce((CAST(lpad(EXTRACT(DAY FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.END_TIME),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.END_TIME) || ' ' || EXTRACT(HOUR FROM UU.END_TIME) || ':' || '00:00' ), '') || '";""'
from ABONENTS A
left join USERS_USLUGA UU on UU.ABONENT_ID=A.ID and uu.id=replace-uuid
where
A.IS_FOLDER=0 and
A.PARENT_ID!=244 and
A.DELETED=0 and
UU.TARIF_ID is null and
UU.ENABLE_DATE is not null and
UU.USLUGA_ID not in (-3,-4) and
UU.USLUGA_ID not in (select id from usluga where name = '') and
a.parent_id not in (244)
order by A.NAME;{code}

Скрипт для заполнения csv

*/app/asr_billing/cfg/sorm3/services.sh*
{code}#!/bin/bash

set -eux
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/app/base/usr/local/bin:/root/bin

for uuid in $(sqlexec "set heading off; select distinct users_usluga_id from users_usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp" | egrep -v "^$" | sed 's/^\s*//g')
do
cat /cfg/sorm3/services-periodic-id.sql > /cfg/sorm3/services-periodic-temp.sql
sed -i "s/replace-uuid/${uuid}/g" /cfg/sorm3/services-periodic-temp.sql
isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /cfg/sorm3/services-periodic-temp.sql >> /cfg/sorm3/data/services-periodic-temp.csv
cat /cfg/sorm3/data/services-periodic-temp.csv | sed '/^$/d' | sed 's/ */ /g' | iconv -f utf8 -t cp1251 > /cfg/sorm3/data/services-periodic.csv
done
{code}
Добавление прав на выполнение скрипта:
{code}chmod a+x /app/asr_billing/cfg/sorm3/services.sh{code}

h2. 3. Пополнение баланса личного счета абонента обобщенная информация.

Отчёт выводит информацию в виде "1";"идентификатор способа оплаты";"номер договора ";"статический IP-адрес";"дата и время пополнения баланса";"сумма";""
Идентификаторы перечислены в "Справочнике способов оплаты (пополнения баланса)" (далее в статье) и учитывает все интегрированные в Carbon Billing платежные системы в стабильной ветке на 09.06.2017. Если на момент интеграции используемая Вами платежная система отсутствует с правочнике и не учитывается в отчете, обратитесь в техпожжержку по вопросы актуализации отчета.
{info}Набор платежных систем доступных для интеграции с биллингом постоянно увеличивается. Перечень с актуальным списком платежных систем для *case* можно получить выполнив следующий скрипт:
{code}in=1; for operator in `grep -shir "paywork.*new" /app/asr_fiscal/usr/local/www/htdocs/* | sed 's/ *//g; s/this->//g ;s/k=n/k = n/g; s/,/, /g' | awk '{print $5}' | sed -e "/^$/d; s/);//g; s/'//g" | sort | uniq`; do echo -e "\t\twhen fo.operator_name = '$operator' then '$in'"; ((in++)); done; echo -e "\t\twhen fo.operator_name is null then '$in'"{code}{info}

*/app/asr_billing/cfg/sorm3/balance-fillup.sql*
{code}set heading off; select
'"1";"' ||
coalesce(case
when fo.operator_name = 'APELSIN' then '1'
when fo.operator_name = 'CITY_PAY' then '2'
when fo.operator_name = 'Comepay' then '3'
when fo.operator_name = 'Elexnet' then '4'
when fo.operator_name = 'ElexnetOld' then '5'
when fo.operator_name = 'EXPRESS_VOLGA' then '6'
when fo.operator_name = 'ForwardMobile' then '7'
when fo.operator_name = 'Gorod' then '8'
when fo.operator_name = 'JCC' then '9'
when fo.operator_name = 'Kassa24' then '10'
when fo.operator_name = 'KAZKOMBANK' then '11'
when fo.operator_name = 'Krasplat' then '12'
when fo.operator_name = 'MINBANK' then '13'
when fo.operator_name = 'MKB_OSMP' then '14'
when fo.operator_name = 'Mobilnik' then '15'
when fo.operator_name = 'MONEY_YANDEX' then '16'
when fo.operator_name = 'NCC' then '17'
when fo.operator_name = 'OSMP_Amigo' then '18'
when fo.operator_name = 'OSMP_CHECK' then '19'
when fo.operator_name = 'OSMP_DELTAPAY' then '20'
when fo.operator_name = 'OSMP_EXPRESS_OPLATA' then '21'
when fo.operator_name = 'OSMP_INTERPAY' then '22'
when fo.operator_name = 'OSMP_MOROZOV' then '23'
when fo.operator_name = 'OSMP_QIWI' then '24'
when fo.operator_name = 'OSMP-QIWI' then '25'
when fo.operator_name = 'OSMP_SBRF' then '26'
when fo.operator_name = 'OSMP_SIPAY' then '27'
when fo.operator_name = 'PAYANYWAY' then '28'
when fo.operator_name = 'PAYCOM_UZ' then '29'
when fo.operator_name = 'Payfon_phone' then '30'
when fo.operator_name = 'PAYKEEPER' then '31'
when fo.operator_name = 'PAYMASTER' then '32'
when fo.operator_name = 'PaymasterAutopay' then '33'
when fo.operator_name = 'PAYNET_UZ' then '34'
when fo.operator_name = 'PayPal' then '35'
when fo.operator_name = 'PayPro' then '36'
when fo.operator_name = 'QIWI_OSMP' then '37'
when fo.operator_name = 'QIWI(REST)' then '38'
when fo.operator_name = 'Qiwiwallet' then '39'
when fo.operator_name = 'QuickPay' then '40'
when fo.operator_name = 'RAPIDA' then '41'
when fo.operator_name = 'Robokassa' then '42'
when fo.operator_name = 'RPS' then '43'
when fo.operator_name = 'SBERBANK_ACQ' then '44'
when fo.operator_name = 'SBRF' then '45'
when fo.operator_name = 'SBRF_BS' then '46'
when fo.operator_name = 'SBRF_KIBERPLAT' then '47'
when fo.operator_name = 'SFOUR' then '48'
when fo.operator_name = 'Simfoniya' then '49'
when fo.operator_name = 'TelePay' then '50'
when fo.operator_name = 'TINKOFF' then '51'
when fo.operator_name = 'UBRIR' then '52'
when fo.operator_name = 'UCS_ACQ' then '53'
when fo.operator_name = 'Unikassa' then '54'
when fo.operator_name = 'Uniteller' then '55'
when fo.operator_name = 'UnitellerAutopay' then '56'
when fo.operator_name = 'VISA-MASTERCARD' then '57'
when fo.operator_name = 'webcreds' then '58'
when fo.operator_name = 'WebRobokassa' then '59'
when fo.operator_name = 'XPLAT' then '60'
when fo.operator_name = 'YAPK' then '61'
when fo.operator_name is null then '62'
else '62'

end,'-')
|| '";"' ||
a.contract_number || '";"' ||
coalesce((select first 1 uf_ip2string(ip) from users u where u.abonent_id = a.id and u.ip is not null),'') || '";"' ||
iif(fo.operator_name<>'' and fo.operator_name is not null,
CAST(lpad(EXTRACT(DAY FROM fo.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM fo.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM fo.OP_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM fo.OP_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM fo.OP_DATE),2,'0') AS varchar(2)),
CAST(lpad(EXTRACT(DAY FROM fo.SYSTEM_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM fo.system_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM fo.system_date) || ' ' || CAST(lpad(EXTRACT(hour FROM fo.system_date),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM fo.system_date),2,'0') AS varchar(2))
)||':'|| '00";"' ||
cast(fo.op_summa/10000000000.00 as varchar(40)) || '";"' ||
coalesce(fo.DESCR,'') || '"'
from
finance_operations fo
join
abonents a
on fo.abonent_id = a.id
where
fo.abonent_id is not null and
fo.op_type = 2 and
fo.storno = 0 and
a.deleted = 0 and
a.parent_id not in (244)
order by fo.op_date;{code}

Тот же отчет, выводящий пополнения баланса за последний час

*/app/asr_billing/cfg/sorm3/balance-fillup-periodic.sql*
{code}set heading off; select
'"1";"' ||
coalesce(case
when fo.operator_name = 'APELSIN' then '1'
when fo.operator_name = 'CITY_PAY' then '2'
when fo.operator_name = 'Comepay' then '3'
when fo.operator_name = 'Elexnet' then '4'
when fo.operator_name = 'ElexnetOld' then '5'
when fo.operator_name = 'EXPRESS_VOLGA' then '6'
when fo.operator_name = 'ForwardMobile' then '7'
when fo.operator_name = 'Gorod' then '8'
when fo.operator_name = 'JCC' then '9'
when fo.operator_name = 'Kassa24' then '10'
when fo.operator_name = 'KAZKOMBANK' then '11'
when fo.operator_name = 'Krasplat' then '12'
when fo.operator_name = 'MINBANK' then '13'
when fo.operator_name = 'MKB_OSMP' then '14'
when fo.operator_name = 'Mobilnik' then '15'
when fo.operator_name = 'MONEY_YANDEX' then '16'
when fo.operator_name = 'NCC' then '17'
when fo.operator_name = 'OSMP_Amigo' then '18'
when fo.operator_name = 'OSMP_CHECK' then '19'
when fo.operator_name = 'OSMP_DELTAPAY' then '20'
when fo.operator_name = 'OSMP_EXPRESS_OPLATA' then '21'
when fo.operator_name = 'OSMP_INTERPAY' then '22'
when fo.operator_name = 'OSMP_MOROZOV' then '23'
when fo.operator_name = 'OSMP_QIWI' then '24'
when fo.operator_name = 'OSMP-QIWI' then '25'
when fo.operator_name = 'OSMP_SBRF' then '26'
when fo.operator_name = 'OSMP_SIPAY' then '27'
when fo.operator_name = 'PAYANYWAY' then '28'
when fo.operator_name = 'PAYCOM_UZ' then '29'
when fo.operator_name = 'Payfon_phone' then '30'
when fo.operator_name = 'PAYKEEPER' then '31'
when fo.operator_name = 'PAYMASTER' then '32'
when fo.operator_name = 'PaymasterAutopay' then '33'
when fo.operator_name = 'PAYNET_UZ' then '34'
when fo.operator_name = 'PayPal' then '35'
when fo.operator_name = 'PayPro' then '36'
when fo.operator_name = 'QIWI_OSMP' then '37'
when fo.operator_name = 'QIWI(REST)' then '38'
when fo.operator_name = 'Qiwiwallet' then '39'
when fo.operator_name = 'QuickPay' then '40'
when fo.operator_name = 'RAPIDA' then '41'
when fo.operator_name = 'Robokassa' then '42'
when fo.operator_name = 'RPS' then '43'
when fo.operator_name = 'SBERBANK_ACQ' then '44'
when fo.operator_name = 'SBRF' then '45'
when fo.operator_name = 'SBRF_BS' then '46'
when fo.operator_name = 'SBRF_KIBERPLAT' then '47'
when fo.operator_name = 'SFOUR' then '48'
when fo.operator_name = 'Simfoniya' then '49'
when fo.operator_name = 'TelePay' then '50'
when fo.operator_name = 'TINKOFF' then '51'
when fo.operator_name = 'UBRIR' then '52'
when fo.operator_name = 'UCS_ACQ' then '53'
when fo.operator_name = 'Unikassa' then '54'
when fo.operator_name = 'Uniteller' then '55'
when fo.operator_name = 'UnitellerAutopay' then '56'
when fo.operator_name = 'VISA-MASTERCARD' then '57'
when fo.operator_name = 'webcreds' then '58'
when fo.operator_name = 'WebRobokassa' then '59'
when fo.operator_name = 'XPLAT' then '60'
when fo.operator_name = 'YAPK' then '61'
when fo.operator_name is null then '62'
else '62'
end,'-')
|| '";"' ||
a.contract_number || '";"' ||
coalesce((select first 1 uf_ip2string(ip) from users u where u.abonent_id = a.id and u.ip is not null),'') || '";"' ||
iif(fo.operator_name<>'' and fo.operator_name is not null,
CAST(lpad(EXTRACT(DAY FROM fo.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM fo.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM fo.OP_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM fo.OP_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM fo.OP_DATE),2,'0') AS varchar(2)),
CAST(lpad(EXTRACT(DAY FROM fo.SYSTEM_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM fo.system_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM fo.system_date) || ' ' || CAST(lpad(EXTRACT(hour FROM fo.system_date),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM fo.system_date),2,'0') AS varchar(2))
)||':'|| '00";"' ||
cast(fo.op_summa/10000000000.00 as varchar(40)) || '";"' ||
coalesce(fo.DESCR,'') || '"'
from
finance_operations fo
join
abonents a
on fo.abonent_id = a.id
where
fo.abonent_id is not null and
fo.op_type = 2 and
fo.storno = 0 and
a.deleted = 0 and
a.parent_id not in (244) and
((fo.op_date between dateadd (-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)))
order by fo.op_date;{code}

h2. 4. Формат справочника ДВО (видов услуг)

{info}В отчет попадают только услуги не входящие в [тарифы|CarbonBilling:Тарифы]{info}

Формат "идентификатор услуги ";"название/обозначение в Вашей системе";"дата создания услуги в биллинге";"";"описание";

*/app/asr_billing/cfg/sorm3/supplement-services.sql*
{code}set heading off;
select
'"1";"' ||
abs(usl.id) || '";"' ||
usl.NAME || '";"' ||
coalesce((select first 1
cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' ||
cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' ||
extract(year from time_changed) from usluga_history where usluga_id = usl.id),'') ||
' 00:00:00";"' ||
iif(usl.deleted > 0,
(select first 1
cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' ||
cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' ||
extract(year from time_changed) from usluga_history where usluga_id = usl.id order by time_changed desc) ||
' 00:00:00";"',
'";"') ||
coalesce(usl.comments,'') || '"'
from
usluga usl
where
usl.system_type != 13 and
usl.id!=170000 and
usl.id in (select usluga_id from usluga_history union distinct select usluga_id from users_usluga where deleted = 0 and abonent_id is not null) and
(usl.name is not null and usl.name <> '')
and usl.id>0
order by id;
{code}
Тот же отчёт, выводящий услуги, созданные за прошедшие сутки:

*/app/asr_billing/cfg/sorm3/supplement-services-periodic.sql*
{code}set heading off;
select
'"' ||
abs(usl.id) || '";"' ||
usl.NAME || '";"' ||
(select first 1
cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' ||
cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' ||
extract(year from time_changed) from usluga_history where usluga_id = usl.id) ||
' 00:00:00";"' ||
iif(usl.deleted > 0,
(select first 1
cast(lpad(extract(day from time_changed),2,'0') as varchar(2)) || '.' ||
cast(lpad(extract(month from time_changed),2,'0') as varchar(2)) || '.' ||
extract(year from time_changed) from usluga_history where usluga_id = usl.id order by time_changed desc) ||
' 00:00:00";"',
'";"') ||
coalesce(usl.comments,'') || '"'
from
usluga usl
where
usl.system_type != 13 and
(id in (select usluga_id from usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp));
{code}

h1. Статичные данные для справочников

Некоторые данные, выгружаемые на СОРМ, невозможно получить по средством системы отчетов по по той или иной причине. Где-то это несоотвествие логики БД СОРМ3 и БД Carbon Billing. Так же часть данных статична и создавать для этого отчет нецелесообразно. Некоторые данные, как например план адресации или список шлюзов, могут в биллинге вообще не заполняться.

h2. Справочник типов документов, удостоверяющих личность

В Carbon Billing 5 нет как такового справочника документов удостоверяющих личность. Для этого существуют [реквизиты|Реквизиты].
Предустановленные реквизиты есть только для одного типа - Паспорт гражданина РФ. Ниже приведен пример содержимого csv с типами документов:

*app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv*
{code}"1";"1";"01.01.2017";"";"Паспорт РФ"{code}

h2. Справочник IP-плана адресации

Эти данные строго индивидуальны для каждого оператора связи. Теоретический, если справочник IP-пулов совпадает с планом адрессации, можно составить отчет (в даном примере не рассматривается)

Примечания:
* Приход денежных средств в Carbon Billing - это тип финансовой операции.
* Финансовые операции хранятся в таблице FINANCE_OPERATIONS, Приход - в поле OP_TYPE, значение = 2 (целочисленное).
* При проведении платежа через платежную систему, поле DESCR заполняются соответствующим идентификатором, представляющим из себя мнемоническое обозначение оператора (название).
* При проведении платежа через веб-кассу или интерфейс администратора биллинга, DESCR доступно для свободного редактирования.

Эта информация необходима для отчета о пополнении баланса, в которм требуется указать численный идентификатор типа оплаты. В справочнике способов оплаты будут представлены все возможные идентификаторы платежных систем. Последний идентификатор - пополнение наличными. В отчете о пополнении баланса все, что не подходит под тот или иной шаблон, будет учтено как пополнение наличными.
{warning}При заполнении описания подобно предустановленном идентификатору платежной системы, платеж НЕ будет считаться проведенным через эту платежную систему, в отчете будет использовано иное поле однозначно идентифицируеще платежную систему и недоступное для ручного изменения.
*Во избежание возможной путаницы, инструктируйте сотрудников абонентского отдела что без необходимости так делать не следует{*}{warning}
При необходимости, список можно сократить в соответствии с используемыми платежными системами, но рекомендуется выгрузить его целиком.
Перечень с актуальным списком платежных систем для csv можно получить выполнив следующий скрипт:
{code}in=1; for operator in `grep -shir "paywork.*new" /app/asr_fiscal/usr/local/www/htdocs/* | sed 's/ *//g; s/this->//g ;s/k=n/k = n/g; s/,/, /g' | awk '{print $5}' | sed -e "/^$/d; s/);//g; s/'//g" | sort | uniq`; do echo -e \"$in\"';'\"01.01.2017 00:00:00\"';'\"\"';'\"$operator\"; ((in++)); done; echo -e \"$in\"';'\"01.01.2017 00:00:00\"';'\"\"';'\"Поступление наличных\"{code}
{info}При повторной выгрузке *pay-types.csv* убедитесь, что не перепутались id платежных систем - в таком случае файл нужно будет исправить вручную.{info}
*app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv*
{code}"1";"1";"01.01.2017 00:00:00";"";"APELSIN"
"1";"2";"01.01.2017 00:00:00";"";"CITY_PAY"
"1";"3";"01.01.2017 00:00:00";"";"Comepay"
"1";"4";"01.01.2017 00:00:00";"";"Elexnet"
"1";"5";"01.01.2017 00:00:00";"";"ElexnetOld"
"1";"6";"01.01.2017 00:00:00";"";"EXPRESS_VOLGA"
"1";"7";"01.01.2017 00:00:00";"";"ForwardMobile"
"1";"8";"01.01.2017 00:00:00";"";"Gorod"
"1";"9";"01.01.2017 00:00:00";"";"JCC"
"1";"10";"01.01.2017 00:00:00";"";"Kassa24"
"1";"11";"01.01.2017 00:00:00";"";"KAZKOMBANK"
"1";"12";"01.01.2017 00:00:00";"";"Krasplat"
"1";"13";"01.01.2017 00:00:00";"";"MINBANK"
"1";"14";"01.01.2017 00:00:00";"";"MKB_OSMP"
"1";"15";"01.01.2017 00:00:00";"";"Mobilnik"
"1";"16";"01.01.2017 00:00:00";"";"MONEY_YANDEX"
"1";"17";"01.01.2017 00:00:00";"";"NCC"
"1";"18";"01.01.2017 00:00:00";"";"OSMP_Amigo"
"1";"19";"01.01.2017 00:00:00";"";"OSMP_CHECK"
"1";"20";"01.01.2017 00:00:00";"";"OSMP_DELTAPAY"
"1";"21";"01.01.2017 00:00:00";"";"OSMP_EXPRESS_OPLATA"
"1";"22";"01.01.2017 00:00:00";"";"OSMP_INTERPAY"
"1";"23";"01.01.2017 00:00:00";"";"OSMP_MOROZOV"
"1";"24";"01.01.2017 00:00:00";"";"OSMP_QIWI"
"1";"25";"01.01.2017 00:00:00";"";"OSMP-QIWI"
"1";"26";"01.01.2017 00:00:00";"";"OSMP_SBRF"
"1";"27";"01.01.2017 00:00:00";"";"OSMP_SIPAY"
"1";"28";"01.01.2017 00:00:00";"";"PAYANYWAY"
"1";"29";"01.01.2017 00:00:00";"";"PAYCOM_UZ"
"1";"30";"01.01.2017 00:00:00";"";"Payfon_phone"
"1";"31";"01.01.2017 00:00:00";"";"PAYKEEPER"
"1";"32";"01.01.2017 00:00:00";"";"PAYMASTER"
"1";"33";"01.01.2017 00:00:00";"";"PaymasterAutopay"
"1";"34";"01.01.2017 00:00:00";"";"PAYNET_UZ"
"1";"35";"01.01.2017 00:00:00";"";"PayPal"
"1";"36";"01.01.2017 00:00:00";"";"PayPro"
"1";"37";"01.01.2017 00:00:00";"";"QIWI_OSMP"
"1";"38";"01.01.2017 00:00:00";"";"QIWI(REST)"
"1";"39";"01.01.2017 00:00:00";"";"Qiwiwallet"
"1";"40";"01.01.2017 00:00:00";"";"QuickPay"
"1";"41";"01.01.2017 00:00:00";"";"RAPIDA"
"1";"42";"01.01.2017 00:00:00";"";"Robokassa"
"1";"43";"01.01.2017 00:00:00";"";"RPS"
"1";"44";"01.01.2017 00:00:00";"";"SBERBANK_ACQ"
"1";"45";"01.01.2017 00:00:00";"";"SBRF"
"1";"46";"01.01.2017 00:00:00";"";"SBRF_BS"
"1";"47";"01.01.2017 00:00:00";"";"SBRF_KIBERPLAT"
"1";"48";"01.01.2017 00:00:00";"";"SFOUR"
"1";"49";"01.01.2017 00:00:00";"";"Simfoniya"
"1";"50";"01.01.2017 00:00:00";"";"TelePay"
"1";"51";"01.01.2017 00:00:00";"";"TINKOFF"
"1";"52";"01.01.2017 00:00:00";"";"UBRIR"
"1";"53";"01.01.2017 00:00:00";"";"UCS_ACQ"
"1";"54";"01.01.2017 00:00:00";"";"Unikassa"
"1";"55";"01.01.2017 00:00:00";"";"Uniteller"
"1";"56";"01.01.2017 00:00:00";"";"UnitellerAutopay"
"1";"57";"01.01.2017 00:00:00";"";"VISA-MASTERCARD"
"1";"58";"01.01.2017 00:00:00";"";"webcreds"
"1";"59";"01.01.2017 00:00:00";"";"WebRobokassa"
"1";"60";"01.01.2017 00:00:00";"";"XPLAT"
"1";"61";"01.01.2017 00:00:00";"";"YAPK"
"1";"62";"01.01.2017 00:00:00";"";"Поступление наличных"
{code}