СОРМ Яхонт, версия 2020 (архивная)

{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. Скрипты первоначальной выгрузки данных

Скрипты производят первичное наполнение СОРМ данными, выгружая как динамически формируемую информацию (абоненты, услуги, пополнения балансов и тд), так и статичные данные (платежные системы, план нумерации, виды документов).
Когда все статичные данные будут подготовлены и сохранены на сервере, сохраните скрипты по указанным путям. Далее, разрешите их выполнение и выполните:
{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.1.1
ftplogin=admin
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

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

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'

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'

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.1.1
ftplogin=admin
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'

if [ -s /app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv ]
then
printf 'Метаданные файла\n'

fi


if [ -s /app/asr_billing/cfg/sorm3/data/static-data/pay-types.csv ]
then
printf 'Метаданные файла\n'

fi

if [ -s /app/asr_billing/cfg/sorm3/data/static-data/gates.csv ]
then
printf 'Метаданные файла\n'

fi
{code}

h1. Отчеты


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

*/app/asr_billing/cfg/sorm3/abonents.sql*
{code}set heading off; select distinct '"1";"' ||
coalesce(U.LOGIN,'-') || '";"' ||
iif(coalesce(u.ip,'') <> '', UF_IP2STRING(U.IP), '') || '";"' ||
coalesce(a.email,'-') ||
'";"' ||
coalesce(a.sms,'') ||
'";"' ||
coalesce(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.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";"' ||
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 ||

'";"' ||

||
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,'') || '"', '""')
||
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,'') || '"', '""')
||
coalesce(av25.attribute_value,'')


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

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

coalesce(a.email,'-') ||
'";"' ||
coalesce(a.sms,'') ||
'";"' ||
coalesce(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.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";"' ||
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 ||

'";"' ||

||
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,'') || '"', '""')
||
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,'') || '"', '""')
||
coalesce(av25.attribute_value,'')

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)

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



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

*/app/asr_billing/cfg/sorm3/services.sql*
{code}set heading off;
select distinct
'"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
left join users u on u.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.ENABLE_DATE <= current_timestamp 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.sql*
{code}set heading off;
select distinct
'"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
left join users u on u.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.ENABLE_DATE <= current_timestamp 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)
and uu.id in (select distinct users_usluga_id from users_usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp)
order by A.NAME;{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 distinct
'"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.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))
)||':'|| '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
left join users u on u.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 upper(fo.descr) not like upper('%Сторнирование%')
order by fo.op_date;
{code}

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

*/app/asr_billing/cfg/sorm3/balance-fillup-periodic.sql*
{code}set heading off;
select distinct
'"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.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))
)||':'|| '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
left join users u on u.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 upper(fo.descr) not like upper('%Сторнирование%')
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. Формат справочника ДВО (видов услуг)



*/app/asr_billing/cfg/sorm3/supplement-services.sql*
{code}set heading off;
select
'"1";"' ||
abs(usl.id) || '";"' ||
usl.NAME || '";"' ||
'01.01.2015 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.NAME,'') || '"'
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
'"1";"' ||
abs(usl.id) || '";"' ||
usl.NAME || '";"' ||
'01.01.2015 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.NAME,'') || '"'
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 and
(usl.id in (select usluga_id from usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp))
order by id;
{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.2015";"";"Паспорт РФ"{code}

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

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

*/app/asr_billing/cfg/sorm3/data/static-data/ip-numbering-plan.csv*
{code}"1";"Серая сеть";"10.0.0.0";"8";"01.01.2017 00:00:00";""
"1";"Белая сеть";"8.8.0.0";"16";"01.01.2017 00:00:00";""{code}


h2. Справочник шлюзов
Эти данные строго индивидуальны для каждого оператора связи.
*/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 символ)

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

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


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

Примечания:
* Приход денежных средств в 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}