СОРМ3 НОРСИ-ТРАНС Яхонт (архив)

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

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

просмотр истории страницы
h6.Отчёт по заключенным договорам на оказание услуг связи c использованием только стандартных реквизитов.
{code}
select '' from abonents
UNION
select distinct
'"' || U.LOGIN || '"' || ';' || '"' || 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 || '"' || ';' || '"' || IIF(ab.abonent_id is null , '0', '1') || '"' || ';' || '"' || 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' || '"' || ';' || '""' || ';' || '"' || A.COMPANY || '"' || ';' || '"1";"";"";"";' || '"' || IIF(A.company=0, A.NAME,'') || '"' || ';' || '"' || IIF(A.company=0, coalesce(av1.attribute_value,'') || coalesce(av7.attribute_value,''),'') || '"' || ';' || '"' || '1"' || ';' || '"' || '";"";"";' || '"' || IIF(A.company=0, coalesce(av2.attribute_value,'') || ' ' || coalesce(av3.attribute_value,' ') || ' ' || coalesce(av8.attribute_value,'') || ' ' || coalesce(av9.attribute_value,'') || ' ' || coalesce(av4.attribute_value,''),'') || '"' || ';' || '"' || '1' || '"' || ';"";"";' || '"' || coalesce(av12.attribute_value,'') || '"' || ';' || '"' || coalesce(av6.attribute_value,'') || '"' || ';' || '"' || coalesce(av32.attribute_value,'-') || '"' || ';' || '"' || coalesce(av11.attribute_value,'') || '"' || ';' || '"' || coalesce(av5.attribute_value,'') || '"' || ';' || '"' || coalesce(av66.attribute_value,'') || '"' || ';"1";"";"";"";"";"";"";"";"";"";' || '"' || coalesce(av25.attribute_value, '') || coalesce(av1007.attribute_value, '') || coalesce(av15.attribute_value, '') || '"' || ';"1' || '"' || ';"";"";"";"";"";"";"";"";"";' || '"' || h.CITY || ' ' || h.STREET || ' ' || h.S_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=1010
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av3 on a.id=av3.ABONENT_ID and av3.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=1008
left join ATTRIBUTE_VALUES as av5 on a.id=av5.ABONENT_ID and av5.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av8 on a.id=av8.ABONENT_ID and av8.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av9 on a.id=av9.ABONENT_ID and av9.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av12 on a.id=av12.ABONENT_ID and av12.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join ATTRIBUTE_VALUES as av11 on a.id=av11.ABONENT_ID and av11.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av1007 on a.id=av1007.ABONENT_ID and av1007.ATTRIBUTE_ID=1007
left join ATTRIBUTE_VALUES as av66 on a.id=av66.ABONENT_ID and av66.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join abonents_block ab on ab.abonent_id=a.id
left join abonents_history ah on ah.abonents_id=a.id
left join users_history uh on uh.login=u.login
{warning}Эти схемы больше не поддерживаются, пожалуйста используйте новую: [CarbonBilling:СОРМ3 НОРСИ-ТРАНС Яхонт]{warning}

where A.IS_FOLDER=0
and a.parent_id != 244
and a.deleted=0 and a.id is not Null
{code}
h1. Сайт поставщика

Тот же отчёт, в который попадает только информация, добавленная за последние сутки
{code}
select '' from abonents
UNION
select distinct
'"' || U.LOGIN || '"' || ';' || '"' || 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 || '"' || ';' || '"' || IIF(ab.abonent_id is null , '0', '1') || '"' || ';' || '"' || 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' || '"' || ';' || '""' || ';' || '"' || A.COMPANY || '"' || ';' || '"1";"";"";"";' || '"' || IIF(A.company=0, A.NAME,'') || '"' || ';' || '"' || IIF(A.company=0, coalesce(av1.attribute_value,'') || coalesce(av7.attribute_value,''),'') || '"' || ';' || '"' || '1"' || ';' || '"' || '";"";"";' || '"' || IIF(A.company=0, coalesce(av2.attribute_value,'') || ' ' || coalesce(av3.attribute_value,' ') || ' ' || coalesce(av8.attribute_value,'') || ' ' || coalesce(av9.attribute_value,'') || ' ' || coalesce(av4.attribute_value,''),'') || '"' || ';' || '"' || '1' || '"' || ';"";"";' || '"' || coalesce(av12.attribute_value,'') || '"' || ';' || '"' || coalesce(av6.attribute_value,'') || '"' || ';' || '"' || coalesce(av32.attribute_value,'-') || '"' || ';' || '"' || coalesce(av11.attribute_value,'') || '"' || ';' || '"' || coalesce(av5.attribute_value,'') || '"' || ';' || '"' || coalesce(av66.attribute_value,'') || '"' || ';"1";"";"";"";"";"";"";"";"";"";' || '"' || coalesce(av25.attribute_value, '') || coalesce(av1007.attribute_value, '') || coalesce(av15.attribute_value, '') || '"' || ';"1' || '"' || ';"";"";"";"";"";"";"";"";"";' || '"' || h.CITY || ' ' || h.STREET || ' ' || h.S_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=1010
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=14
left join ATTRIBUTE_VALUES as av3 on a.id=av3.ABONENT_ID and av3.ATTRIBUTE_ID=13
left join ATTRIBUTE_VALUES as av4 on a.id=av4.ABONENT_ID and av4.ATTRIBUTE_ID=1008
left join ATTRIBUTE_VALUES as av5 on a.id=av5.ABONENT_ID and av5.ATTRIBUTE_ID=7
left join ATTRIBUTE_VALUES as av6 on a.id=av6.ABONENT_ID and av6.ATTRIBUTE_ID=4
left join ATTRIBUTE_VALUES as av7 on a.id=av7.ABONENT_ID and av7.ATTRIBUTE_ID=22
left join ATTRIBUTE_VALUES as av8 on a.id=av8.ABONENT_ID and av8.ATTRIBUTE_ID=16
left join ATTRIBUTE_VALUES as av9 on a.id=av9.ABONENT_ID and av9.ATTRIBUTE_ID=17
left join ATTRIBUTE_VALUES as av12 on a.id=av12.ABONENT_ID and av12.ATTRIBUTE_ID=2
left join ATTRIBUTE_VALUES as av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
left join ATTRIBUTE_VALUES as av11 on a.id=av11.ABONENT_ID and av11.ATTRIBUTE_ID=1
left join ATTRIBUTE_VALUES as av25 on a.id=av25.ABONENT_ID and av25.ATTRIBUTE_ID=25
left join ATTRIBUTE_VALUES as av1007 on a.id=av1007.ABONENT_ID and av1007.ATTRIBUTE_ID=1007
left join ATTRIBUTE_VALUES as av66 on a.id=av66.ABONENT_ID and av66.ATTRIBUTE_ID=6
left join ATTRIBUTE_VALUES as av15 on a.id=av15.ABONENT_ID and av15.ATTRIBUTE_ID=15
left join abonents_block ab on ab.abonent_id=a.id
left join abonents_history ah on ah.abonents_id=a.id
left join users_history uh on uh.login=u.login
[НОРСИ-ТРАНС ИС СОРМ "Яхонт-ПД"|https://norsi-trans.ru/catalog/yahont-sorm3/]

where A.IS_FOLDER=0
and ah.time_changed between (current_timestamp - 1) and current_timestamp
or uh.time_changed between (current_timestamp - 1) and current_timestamp
and a.parent_id != 244
and a.deleted=0 and a.id is not Null
h1. Как интегрировать
{code}
На текущий момент есть несколько версий интеграции:

{pagetree:root=СОРМ3 НОРСИ-ТРАНС Яхонт (архив)}