|
Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.
|
Изменения (20)
просмотр истории страницы| {warning}Эта схема больше не поддерживается, пожалуйста используйте новую: [CarbonBilling:СОРМ3 VasExperts СКАТ]{warning} |
| {toc} |
| h1. Сайт поставщика [VasExperts ИС СОРМ-3|https://vasexperts.ru/products/is-sorm/is-sorm-3/] |
| h1. Выгрузка на СОРМ3 |
... |
| 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 -неструктурированные, обязателен для физических лиц-- |
... |
| '";"";"' || '";"' || -- 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 |
... |
| '";"";"' || '";"' || -- 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 |
... |
| 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 адреса к логину |
... |