|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Изменения (23)
просмотр истории страницы{warning}Эта схема больше не поддерживается, пожалуйста используйте новую: [CarbonBilling:СОРМ3 VasExperts СКАТ]{warning} |
{toc} |
h1. Сайт поставщика [VasExperts ИС СОРМ-3|https://vasexperts.ru/products/is-sorm/is-sorm-3/] |
h1. Выгрузка на СОРМ3 |
... |
# Добавьте задания в планировщик задач и перезапустите его. # Проведите первоначальное наполнение данными |
|
h1. Скрипт для планировщика задач Скрипт выгружает изменения по абонентам, услугам и платежам за последний час. |
... |
case when A.DELETED=1 then '1' --текущий статус абонента, справочник, 0 – подключен 1 - отключен-- else '0' end || '";"' || |
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' ' || CAST(lpad(EXTRACT(hour FROM a.create_date),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM a.create_date),2,'0') AS varchar(2)) || ':00' || '";"' || --дата заключения договора-- |
case when A.DELETED=1 then |
case when A.DELETED=1 then |
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM AH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' || from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1) |
else '' end || '";"'|| --дата расторжения договора, дата, формат DD.mm.YYYY HH:MM:SS, для активных абонентов пустая строка-- |
coalesce(A.COMPANY,'0') || '";"' || --тип абонента, справочник, 0 - физическое лицо, 1 - юридическое лицо-- case when coalesce(A.COMPANY,'0')=0 then '1' --тип данных по ФИО, справочник, 0 - структурированные данные, 1 -неструктурированные, обязателен для физических лиц-- |
... |
case when A.DELETED=1 then '1' --текущий статус абонента, справочник, 0 – подключен 1 - отключен-- else '0' end || '";"' || |
CAST(lpad(EXTRACT(DAY FROM a.create_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM a.create_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM a.create_date) || ' ' || CAST(lpad(EXTRACT(hour FROM a.create_date),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM a.create_date),2,'0') AS varchar(2)) || ':00' || '";"' || --дата заключения договора-- |
case when A.DELETED=1 then |
(select first 1 CAST(lpad(EXTRACT(DAY FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM AH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM AH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' || from abonents_history ah where ah.abonents_id = a.id and ah.deleted=1) |
else '' end || '";"'|| --дата расторжения договора, дата, формат DD.mm.YYYY HH:MM:SS, для активных абонентов пустая строка-- |
coalesce(A.COMPANY,'0') || '";"' || --тип абонента, справочник, 0 - физическое лицо, 1 - юридическое лицо-- case when coalesce(A.COMPANY,'0')=0 then '1' --тип данных по ФИО, справочник, 0 - структурированные данные, 1 -неструктурированные, обязателен для физических лиц-- |
... |
{code} h2. 2. Выгрузка номенклатуры услуг оператора |
*/app/asr_billing/cfg/sorm3/service_list.sql * |
{code} set heading off; select |
... |
where u.id>0; {code} |
Тот же отчёт, в который попадает только информация, добавленная за последний час. */app/asr_billing/cfg/sorm3/service_list-periodic.sql* {code} set heading off; select '"' || U.ID || '";"' || --id услуги -- iif (U.NAME='', 'Удаленная услуга', U.NAME) || '";"' || --имя услуги -- coalesce(U.COMMENTS,'') || '";"' || --описание услуги -- coalesce(( select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED) from usluga_history uh where uh.usluga_id = u.id ),'01.01.2018') || '";"' || --дата создания услуги (дата активации услуги)-- case when U.DELETED=1 then coalesce ((select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2)) ||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED) from usluga_history uh where uh.usluga_id = u.id and UH.deleted=1 ),'01.01.2018') else '' end || '";"' || --дата удаления услуги (дата деактивации услуги)-- */ 'Ethernet"' --тип сети from USLUGA U where u.id>0 and (u.id in (select usluga_id from usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp)); {code} |
h2. 3. Выгрузка услуг по абонентам */app/asr_billing/cfg/sorm3/services.sql* |
... |
'";"";"' || '";"' || -- ID лицевого счёта, ID точки подключения, ID договора-- U.NAME || '";"' || --имя услуги -- |
coalesce(CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE),CAST(lpad(EXTRACT(DAY FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.CREATE_DATE)) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.CREATE_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.CREATE_DATE),2,'0') AS varchar(2)) || ':00' || '";"' || --дата создания услуги-- |
case when UU.DELETED=1 then |
coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE)) |
coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UUH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.NEXT_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.NEXT_DATE),2,'0') AS varchar(2)) || ':00' ) |
else ' ' end || '"' --дата удаления услуги-- from USERS_USLUGA UU |
... |
and u.id > 0; {code} |
Тот же отчёт, в который попадает только информация, добавленная за последний час. */app/asr_billing/cfg/sorm3/services-periodic.sql* {code} set heading off; select distinct '"' || U.ID || '";"' || --id услуги -- A.ID || '";"' || --id абонента-- '";"";"' || '";"' || -- ID лицевого счёта, ID точки подключения, ID договора-- U.NAME || '";"' || --имя услуги -- coalesce(CAST(lpad(EXTRACT(DAY FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.ENABLE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.ENABLE_DATE),CAST(lpad(EXTRACT(DAY FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.CREATE_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.CREATE_DATE)) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.CREATE_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.CREATE_DATE),2,'0') AS varchar(2)) || ':00' || '";"' || --дата создания услуги-- case when UU.DELETED=1 then coalesce((select first 1 CAST(lpad(EXTRACT(DAY FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UUH.TIME_CHANGED) || ' ' || CAST(lpad(EXTRACT(hour FROM UUH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UUH.TIME_CHANGED),2,'0') AS varchar(2)) || ':00' from users_usluga_history uuh where uuh.USERS_USLUGA_ID = uu.id and UUH.deleted=1),CAST(lpad(EXTRACT(DAY FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UU.NEXT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UU.NEXT_DATE) || ' ' || CAST(lpad(EXTRACT(hour FROM UU.NEXT_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM UU.NEXT_DATE),2,'0') AS varchar(2)) || ':00' ) else ' ' end || '"' --дата удаления услуги-- from USERS_USLUGA UU join abonents a on a.id=uu.abonent_id join usluga u on uu.usluga_id = u.id where a.is_folder=0 and a.id >0 and a.parent_id not in (2,244) and u.id > 0 and uu.id in (select distinct users_usluga_id from users_usluga_history where time_changed between dateadd (-1 hour to current_timestamp) and current_timestamp); {code} |
h2. 4. Выгрузка платежей абонентов */app/asr_billing/cfg/sorm3/payments.sql* |
... |
and a.id is not null and a.id>0; {code} |
Тот же отчёт, в который попадает только информация, добавленная за последний час. */app/asr_billing/cfg/sorm3/payments-periodic.sql* {code} set heading off; select distinct '"' || A.ID || '";"' || --id абонента -- '";"' || --код абонента, зарезервировано на будущее-- -- fo.OP_DATE || '";"' || --Дата платежа-- CAST(lpad(EXTRACT(DAY FROM fo.OP_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM fo.OP_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM fo.OP_DATE) || 'T' || CAST(lpad(EXTRACT(HOUR FROM fo.OP_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(MINUTE FROM fo.OP_DATE),2,'0') AS varchar(2))||':'|| lpad(CAST(EXTRACT(SECOND FROM fo.OP_DATE) as integer),2,'0') || '";"' || round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '";"' || --Сумма платежа-- iif(fo.OPERATOR_NAME is not null and fo.OPERATOR_NAME<>'',fo.OPERATOR_NAME,'Admin')|| '";"' || coalesce (fo.ADMIN_COMMENT,'') || '"' --раметры платежа, строка, размер 512, дополнительная информация по платежу в неструктурированном виде, при её наличии-- from abonents a left join finance_operations as fo on a.id=fo.abonent_id where OP_TYPE=2 and A.IS_FOLDER=0 and a.parent_id not in (2,244) and a.id is not null and a.id>0 and ((fo.op_date between dateadd (-1 hour to current_timestamp) and current_timestamp) or ((fo.operator_name='' or fo.operator_name is null) and (fo.SYSTEM_DATE between dateadd (-1 hour to current_timestamp) and current_timestamp))); {code} |
h2. 5. Формат выгрузки на удаление абонентов из БД */app/asr_billing/cfg/sorm3/deleted_abonents.sql* |
... |
h2. 6. Выгрузка IP адресов */app/asr_billing/cfg/sorm3/ip.sql* |
В отчете приведен пример интервала белых ip(U.IP between 1541441536 and 1541442047). Вам требуется самостоятельно определить белые ip-адреса с помощью [конвертера|https://www.browserling.com/tools/ip-to-dec] |
{code} set heading off; select distinct '"' || '";"' || --зарезервировано на будущее-- A.ID || '";"' || --id абонента -- uf_ip2string(U.IP) || '";"' || --ip абонента -- |
case when U.IP between 1541441536 and 1541442047 then '1' --Здесь приведен пример интервала белых ip -- |
else '0' end || '";"' || (select first 1 CAST(lpad(EXTRACT(DAY FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM UH.TIME_CHANGED) ||'T'|| CAST(lpad(EXTRACT(HOUR FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(MINUTE FROM UH.TIME_CHANGED),2,'0') AS varchar(2))||':'|| SUBSTRING(uh.time_changed from 18 for 2) from users_history UH where UH.IP=U.IP and UH.USERS_ID=U.ID) || '";"' || |
... |
*/app/asr_billing/cfg/sorm3/ip_numbering.sql* {code} |
..... |
set heading off; select '"' || uf_ip2string(NETWORK_ADDRESS) || '/' || mask || '";"' || name || '-Статические адреса для абонентов' || '";"' || '01.01.2017T00:00:00' || '";""' from ip_pull where pull_id not in (1,2,3,4,5,6,7); |
{code} |
h2. 8. Выгрузка привязки ip адреса к логину |
*/app/asr_billing/cfg/sorm3/fixednetid.sql* {code} |
... |