Выгрузка на СОРМ3

Отчеты

1. Данные об абонентах физических и юридических лицах по договорам на оказание услуг передачи данных.
2. Данные о подключенных абонентам видах услуг связи
3. Пополнение баланса личного счета абонента обобщенная информация.
4. Формат справочника ДВО (видов услуг)

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

Справочник типов документов, удостоверяющих личность
Справочник IP-плана адресации
Справочник шлюзов
Справочник способов оплаты (пополнения баланса)

Время выполнения инструкции: 20-40 минут, основное время занимает настройка, сама выгрузка должна укладываться в 10 минут, точное время зависит от размера базы

Выгрузка на СОРМ3

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

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

Для автоматизации передачи отчетов на СОРМ3 сделайте следующее:

Создайте папку sorm3 и необходимые подпапки, в них будут храниться отчеты для выгрузки, а так же выгруженные файлы:
```
mkdir -p /app/asr_billing/cfg/sorm3/data/done
mkdir -p /app/asr_billing/cfg/sorm3/data/static-data
```

Добавьте все отчеты, получающие данные из биллинга, в папку /app/asr_billing/cfg/sorm3/. Листинги отчетов и пути приведены далее в статье. Должна получиться следующая структура каталога:

/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

Добавьте отчеты со статичными данными в папку /app/asr_billing/cfg/sorm3/data/static-data: справочник типов документов, план адресации и список платежных систем
Добавьте задания в планировщик задач и перезапустите его.
Проведите первоначальное наполнение данными

Примечания:

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

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

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

/etc/cron.daily/40_sorm_daily.sh

#/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

/etc/cron.hourly/40_sorm_hourly.sh

#/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

После сохранения скриптов, установите на обоих права на выполнение и перезапустите планировщик:

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

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

Скрипты производят первичное наполнение СОРМ данными, выгружая как динамически формируемую информацию (абоненты, услуги, пополнения балансов и тд), так и статичные данные (платежные системы, план нумерации, виды документов).
Когда все статичные данные будут подготовлены и сохранены на сервере, сохраните скрипты по указанным путям. Далее, разрешите их выполнение и выполните:

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

/app/asr_billing/cfg/sorm3/initial-unloaded-data.sh

#/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

/app/asr_billing/cfg/sorm3/initial-static-data.sh

#/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

Отчеты

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

/app/asr_billing/cfg/sorm3/abonents.sql

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;

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

/app/asr_billing/cfg/sorm3/abonents-periodic.sql

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;

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

В отчет попадают только услуги не входящие в тариф

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

/app/asr_billing/cfg/sorm3/services.sql

set heading off; select 
'"1";"' ||
'-' || '";"' ||
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' ||
'";"'||
case when uu.ACTIVATED=1 then '";""'
else
	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) || ' ' || '00' || ':' || '00:00' ), '') || '";""'
end
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;

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

/app/asr_billing/cfg/sorm3/services-periodic-id.sql

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;

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

/app/asr_billing/cfg/sorm3/services.sh

#!/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

Добавление прав на выполнение скрипта:

chmod a+x /app/asr_billing/cfg/sorm3/services.sh

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

Отчёт выводит информацию в виде "1";"идентификатор способа оплаты";"номер договора ";"статический IP-адрес";"дата и время пополнения баланса";"сумма";""
Идентификаторы перечислены в "Справочнике способов оплаты (пополнения баланса)" (далее в статье) и учитывает все интегрированные в Carbon Billing платежные системы в стабильной ветке на 09.06.2017. Если на момент интеграции используемая Вами платежная система отсутствует с правочнике и не учитывается в отчете, обратитесь в техпожжержку по вопросы актуализации отчета.

Набор платежных систем доступных для интеграции с биллингом постоянно увеличивается. Перечень с актуальным списком платежных систем для case можно получить выполнив следующий скрипт:

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'"

/app/asr_billing/cfg/sorm3/balance-fillup.sql

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;

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

/app/asr_billing/cfg/sorm3/balance-fillup-periodic.sql

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;

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

В отчет попадают только услуги не входящие в тарифы

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

/app/asr_billing/cfg/sorm3/supplement-services.sql

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;

Тот же отчёт, выводящий услуги, созданные за прошедшие сутки:

/app/asr_billing/cfg/sorm3/supplement-services-periodic.sql

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));

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

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

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

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

/app/asr_billing/cfg/sorm3/data/static-data/doc-types.csv

"1";"1";"01.01.2017";"";"Паспорт РФ"

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

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

/app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv

"1";"Серая сеть";"10.0.0.0";"8";"01.01.2017 00:00:00";""
"1";"Белая сеть";"8.8.0.0";"16";"01.01.2017 00:00:00";""

Справочник шлюзов

Эти данные строго индивидуальны для каждого оператора связи.
/app/asr_billing/cfg/sorm3/data/static-data/gates.csv

Формат:
идентификатор филиала (число, 0.. 65535)
IP-адрес шлюза (либо V4 либо v6) (строка) (формат согласно правилу кодирования)
дата начала действия шлюза (дата) (формат согласно правилу кодирования)
дата завершения действия шлюза (в случае работы оборудования по настоящее время - пустое значение) (формат согласно правилу кодирования)
описание шлюза (строка, 1..256)
адрес установки шлюза: страна (строка, 1..128)
область (строка, 1..128)
район (строка, 1..128)
город/поселок/деревня/аул (строка, 1..128)
улица (строка, 1..128)
номер дома, строения (строка, 1..128)
тип шлюза (число , строго 1 символ)

Пример:

"1";"192.168.1.1";"01.01.2017 00:00:00";"";"NAS на улице Ленина";"РФ";"Московская область";"";"Москва";"Ленина";"10";"7"

Список типов шлюзов:
тип шлюза (число)
для SGSN – 0;
для GGSN – 1;
для SMSC – 2;
для GMSC – 3;
для HSS – 4;
для ТФоП-шлюза – 5;
для VoIP-шлюза – 6;
для ААА – 7;
для NAT – 8.

Справочник способов оплаты (пополнения баланса)

Примечания:

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

Эта информация необходима для отчета о пополнении баланса, в которм требуется указать численный идентификатор типа оплаты. В справочнике способов оплаты будут представлены все возможные идентификаторы платежных систем. Последний идентификатор - пополнение наличными. В отчете о пополнении баланса все, что не подходит под тот или иной шаблон, будет учтено как пополнение наличными.

При заполнении описания подобно предустановленном идентификатору платежной системы, платеж НЕ будет считаться проведенным через эту платежную систему, в отчете будет использовано иное поле однозначно идентифицируеще платежную систему и недоступное для ручного изменения.
Во избежание возможной путаницы, инструктируйте сотрудников абонентского отдела что без необходимости так делать не следует

При необходимости, список можно сократить в соответствии с используемыми платежными системами, но рекомендуется выгрузить его целиком.
Перечень с актуальным списком платежных систем для csv можно получить выполнив следующий скрипт:

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\"';'\"\"';'\"Поступление наличных\"

При повторной выгрузке pay-types.csv убедитесь, что не перепутались id платежных систем - в таком случае файл нужно будет исправить вручную.

/app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv

"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";"";"Поступление наличных"

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