СОРМ3 МФИ СОФТ Январь (архив)

Ключ
Эта строка удалена.
Это слово было удалено. Это слово было добавлено.
Эта строка добавлена.

Изменения (11)

просмотр истории страницы
h6. 1) Структура отчёта ABONENTS
Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonents.sql
{code}
set heading off;
select distinct '"' ||
A.ID || '";"' ||
A.OPERATOR_ID || '";"' ||
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 || '";"' ||
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)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM a.create_date)),0),2,'0') AS varchar(2)) || '";"' ||
CAST(lpad(EXTRACT(DAY FROM CURRENT_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM CURRENT_DATE),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM CURRENT_DATE) || ' ' || CAST(lpad(EXTRACT(HOUR FROM CURRENT_TIMESTAMP),2,'0') AS varchar(2)) || ':' || CAST(lpad(EXTRACT(MINUTE FROM CURRENT_TIMESTAMP),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM CURRENT_TIMESTAMP)),0),2,'0') AS varchar(2)) || '";"' ||
{warning}Эти схемы больше не поддерживаются, пожалуйста используйте новую: [CarbonBilling:СОРМ3 МФИ СОФТ Январь]{warning}

case when a.company=0 then
h1. Сайт поставщика

'"42";"1";"";"";"";"' || A.NAME || '";"' || coalesce(av22.attribute_value,'') || '";"' || coalesce(av32.attribute_value,'') || '";"0";' || coalesce(av14.attribute_value,'') || '";"' || coalesce(av13.attribute_value,'') || '";"' || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'') || '";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";"";"";"";"";"' ||
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"' else
[МФИ СОФТ АПК ИС БД ОРМ «Январь»|https://www.mfisoft.ru/solutions/yanvar/]

'"43";"";"";"";"";"";"";"";"";"";"";"";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";"' || A.NAME || '";"' || coalesce(av4.attribute_value,'') || '";"' || coalesce(av29.attribute_value,'') || '";"' || coalesce(av1.attribute_value,'') || '";"' ||
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"'
h1. Как интегрировать

На текущий момент есть несколько версий интеграции:


end

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 av29 on a.id=av29.ABONENT_ID and av29.ATTRIBUTE_ID=29
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join abonents_block ab on ab.abonent_id=a.id
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.deleted=0
and a.id is not null;
{code}
h6. 2) Структура отчёта ABONENT_ADDRESS
Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonent_address.sql
{code}
set heading off;
select distinct
'"' || A.ID || '";"' ||
case
when a.company=1 then coalesce(UADR.ATTRIBUTE_VALUE,' ')
when a.company=0 then coalesce(PPROPRISKA.ATTRIBUTE_VALUE,' ')
end || '";"' ||
coalesce(POCHADR.ATTRIBUTE_VALUE,' ') || '";"' ||
case
when a.company=1 then coalesce(PHADR.ATTRIBUTE_VALUE,' ')
when a.company=0 then ' '
end || '";"' ||
coalesce( h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER,'') || '";"' ||
'0' || '";"' ||
coalesce(H.ZIP_CODE,'') || '";"' ||
coalesce(H.COUNTRY,'') || '";"' ||
coalesce(H.REGION,'') || '";"' ||
' ' || '";"' ||
coalesce(H.city,'') || '";"' ||
coalesce(H.street,'') || '";"' ||
coalesce(H.s_number,'') || '";"' ||
coalesce(H.S_LITER,'') || '";"' ||
coalesce(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 ATTRIBUTE_VALUES UADR on UADR.ABONENT_ID=A.ID and UADR.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES PPROPRISKA on PPROPRISKA.ABONENT_ID=A.ID and PPROPRISKA.ATTRIBUTE_ID=15
left join ATTRIBUTE_VALUES POCHADR on POCHADR.ABONENT_ID=A.ID and POCHADR.ATTRIBUTE_ID=27
left join ATTRIBUTE_VALUES PHADR on PHADR.ABONENT_ID=A.ID and PHADR.ATTRIBUTE_ID=26
where a.deleted=0;
{code}
h6. 3) Структура отчёта ABONENT_IDENT
Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonent_ident.sql
{code}
set heading off;
select distinct
'"' || A.ID || '";"";"";"";"";"";"";"";"";"";"' || coalesce(u.mac,'') || '";"";"";"' || coalesce(u.login,'') || '";"' || coalesce(A.EMAIL,'') || '";"";"";"";"";"0";"' || coalesce(uf_ip2string(u.ip),'') || '";"' || ip.MASK || '";"' || uf_ip2string(ip.START_IP) || '";"' || uf_ip2string(ip.END_IP) ||'";"";"";"";"' || 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"' || ';"' || IIF(ah.time_del is not null , CAST(lpad(EXTRACT(DAY FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM AH.TIME_DEL),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM AH.TIME_DEL) || ' 00:00:00' ,'') || '";"";"";"";"";""'
from abonents a
left join USERS U on A.ID = U.ABONENT_ID
left join abonents_history ah on ah.abonents_id=a.id
left join ip_pull ip on u.PULL_ID=ip.PULL_ID
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.id is not null;
{code}
h6. 4) Структура отчёта ABONENT_SERVICE
Пусть до отчёта в контейнере asr_billing
/cfg/sorm3/abonent_service.sql
{code}
set heading off;
select distinct
'"' || A.ID || '";"' ||
A.OPERATOR_ID || '";"' ||
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) || ' ' || CAST(lpad(EXTRACT(HOUR FROM UU.ENABLE_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(EXTRACT(MINUTE FROM UU.ENABLE_DATE),2,'0') AS varchar(2)) || ':' || CAST(lpad(round((EXTRACT(SECOND FROM UU.ENABLE_DATE)),0),2,'0') AS varchar(2)) || '";"' ||
' ' || '";"' ||
' ' || '";"' ||
' ' || '";"' ||
' ' || '";"'
from abonents a
left join users_usluga UU on uu.abonent_id=a.id
left join usluga u on u.id=uu.usluga_id
where A.IS_FOLDER=0
and a.parent_id not in (244)
and a.id is not null
and u.deleted=0
and a.deleted=0
and uu.enabled=1
and uu.deleted=0;
{code}
{pagetree:root=СОРМ3 МФИ СОФТ Январь (архив)}