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

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

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

просмотр истории страницы
h6. 1. Отчёт по заключенным договорам на оказание услуг связи 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}
h6. 2. Активированные пользователями дополнительные услуги (услуги, не включенные в тариф) services без учета системных обещанных платежей. Формат "логин";"номер договора";"идентификатор услуги";"дата и время подключения услуги";"дата и время отключения";""
На текущий момент есть несколько версий интеграции:
{code}
select distinct
'"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || 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' || '"' || ';' || '"'|| 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) || ' ' || EXTRACT(HOUR FROM UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""'
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 ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join USERS_USLUGA_HISTORY UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and UU.ENABLE_DATE is not null
and UU.TARIF_ID is null
and UU.USLUGA_ID!=-4
and UU.USLUGA_ID!=-3
and A.PARENT_ID!=244
and A.DELETED=0
order by A.NAME
{code}
Тот же отчёт, который выводит только услуги подключенные за последний час
{code}
select distinct
'"' || U.LOGIN || '"' || ';' || '"' || A.CONTRACT_NUMBER || '"' || ';'|| '"' || 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' || '"' || ';' || '"'|| 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) || ' ' || EXTRACT(HOUR FROM UU.END_TIME) || ':' || '00:00' ), '') || '"' || ';""'
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 ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join USERS_USLUGA_HISTORY UU on UU.ABONENT_ID=A.ID
where A.IS_FOLDER=0
and UU.ENABLE_DATE is not null
and UU.TARIF_ID is null
and UU.USLUGA_ID!=-4
and UU.USLUGA_ID!=-3
and A.PARENT_ID!=244
and A.DELETED=0
and UU.TIME_CHANGED between (current_timestamp - 1/24) and current_timestamp
order by A.NAME
{code}
h6. 3. Пополнение баланса личного счета абонента обобщенная информация. Отчёт выводит информацию в виде "1";"идентификатор способа оплаты";"номер договора ";"статический IP-адрес";"дата и время пополнения баланса";"сумма";""
В данном отчёте идентификаторы способа оплаты зависят от описания платежа:
Описание Gorod - 1
Описание Paymaser - 2
Остальное - 3
{code}
select
'"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME)
WHEN 'GOROD' THEN '1'
WHEN 'PAYMASTER' THEN '2'
ELSE '3'
END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || 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) || ' 00:00:00' || '"' || ';' || '"' || round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '"' || ';' || '""'
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
left join users u on u.abonent_id=a.id
where fo.OP_TYPE=2
and A.PARENT_ID!=244
and A.DELETED=0
{code}
Тот же отчёт, который выводит платежи, проводимые за последний час
{code}
select
'"1"' || ';' || '"' || CASE UPPER(FO.OPERATOR_NAME)
WHEN 'GOROD' THEN '1'
WHEN 'PAYMASTER' THEN '2'
ELSE '3'
END || '"' || ';' || '"' || A.contract_number || '"' || ';' || '"' || uf_ip2string(u.ip) || '"' || ';' || '"' || 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) || ' 00:00:00' || '"' || ';' || '"' || round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) || '"' || ';' || '""'
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
left join users u on u.abonent_id=a.id
where fo.OP_TYPE=2
and A.PARENT_ID!=244
and A.DELETED=0
and DATEADD(-1 hour to current_timestamp) - FO.SYSTEM_DATE < 1/24
{code}
h6. 4. Отчёт выводит дополнительные услуги в формате "идентификатор услуги ";"название/обозначение в Вашей системе";"дата создания услуги в биллинге";"";"описание";
{code}
select distinct
'"' || USL.ID || '"' || ';' || '"' || USL.NAME || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)) || ' 00:00:00' || '"' || ';' || '""' || ';' || '"' || USL.NAME || '"'
from USLUGA USL
left join USERS_USLUGA_HISTORY UU on UU.USLUGA_ID=USL.ID
and USL.SYSTEM_TYPE!=8
and USL.SYSTEM_TYPE!=9
and USL.SYSTEM_TYPE!=13
and USL.SYSTEM_TYPE is not null
order by USL.ID
{code}
Тот же отчёт, выводящий услуги, созданные за прошедшие сутки:
{code}
select distinct
'"' || USL.ID || '"' || ';' || '"' || USL.NAME || '"' || ';' || '"' || CAST(lpad(EXTRACT(DAY FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id)) || ' 00:00:00' || '"' || ';' || '""' || ';' || '"' || USL.NAME || '"'
from USLUGA USL
left join USERS_USLUGA_HISTORY UU on UU.USLUGA_ID=USL.ID
left join USLUGA_HISTORY as uh1 on UH1.USLUGA_ID=USL.ID
WHERE
USL.SYSTEM_TYPE!=8
and USL.SYSTEM_TYPE!=9
and USL.SYSTEM_TYPE!=13
and USL.SYSTEM_TYPE is not null
and DATEADD(-1 day to current_timestamp) - (select min(TIME_CHANGED) from USLUGA_HISTORY as UH where uh.USLUGA_ID=usl.id) < 1
order by USL.ID
{code}
{pagetree:root=СОРМ3 НОРСИ-ТРАНС Яхонт (архив)}