h2. Выгрузка на СОРМ3 Для автоматизации передачи отчетов на СОРМ3 следует: # Удалить в начале *select '' from abonents UNION* # Открыть текстовый файл в редакторе и привести к следующему виду *set heading off; <ТЕКСТ ЗАПРОСА>;*, например: {code}set heading off; select * from abonents;{code} # Сохранить в пределах контейнера /app/asr_billing, например */app/asr_billing/root/sorm3.sql* # Выполнить отчет следующей командой: {code}chroot /app/asr_billing isql-fb 169.254.30.50:/var/db/billing.gdb -p servicem -u SYSDBA -i /root/sorm3.sql > /app/asr_billing/root/sorm3-export.csv{code} # Любым удобным способом наладить автоматическое выполнение и передачу результата запроса в СОРМ3 (например, с помощью cron отправлять файл утилитой curl) h2. Отчеты
|
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 || '"' || ';' || '"' || CASE
|
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 || '";"' || CASE
|
WHEN AB.id is NULL THEN '0' WHEN AB.B_ADMIN=1 THEN '1'
|
... WHEN AB.B_OWN=1 THEN '1'
|
WHEN AB.B_SYS=1 THEN '1' WHEN AB.B_NEGBAL=1 and (select count(*) from abonents_block where abonent_id=a.id)=1 THEN '0'
|
end || '"' || ';' || '"' ||
|
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' || '"' || ';"' || CASE
|
WHEN AB.id is NULL THEN '' WHEN AB.B_ADMIN=1 THEN (select CAST(lpad(EXTRACT(DAY FROM b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM b_date) || ' 00:00:00' from abonents_block where B_ADMIN=1 and abonent_id=a.id)
|
... WHEN AB.B_OWN=1 THEN (select CAST(lpad(EXTRACT(DAY FROM b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM b_date) || ' 00:00:00' from abonents_block where B_OWN=1 and abonent_id=a.id)
|
WHEN AB.B_SYS=1 THEN (select CAST(lpad(EXTRACT(DAY FROM b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM b_date) || ' 00:00:00' from abonents_block where B_SYS=1 and abonent_id=a.id) WHEN AB.B_NEGBAL=1 and (select count(*) from abonents_block where abonent_id=a.id)=1 THEN ''
|
end|| '"' || ';' || '"' || A.COMPANY || '"' || ';' || '"1";"";"";"";' || '"' || IIF(A.company=0, A.NAME,'') || '"' || ';' || '"' || IIF(A.company=0, coalesce(av1.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(av15.attribute_value, '') || '"' || ';"1' || '"' || ';"";"";"";"";"";"";"";"";"";' || '"' || h.CITY || ' ' || h.STREET || ' ' || h.S_NUMBER || ' ' || a.A_HOME_NUMBER ||'"'
|
end || '";' || '"' || A.COMPANY || '"' || ';' || case when a.company=0 then '"1";"";"";"";"' || A.NAME || '";"' || coalesce(av22.attribute_value,'') || '";"1";"";"";"";"' || iif((coalesce(av14.attribute_value,'') || coalesce(av13.attribute_value,'') || coalesce(av16.attribute_value,'') || coalesce(av17.attribute_value,'')) <> '', iif(coalesce(av14.attribute_value,'') <> '', av14.attribute_value,'') || iif(coalesce(av13.attribute_value,'') <> '', ' ' || av13.attribute_value,'') || iif(coalesce(av16.attribute_value,'') <> '', ' ' || av16.attribute_value,'') || iif(coalesce(av17.attribute_value,'') <> '', ' ' || av17.attribute_value,'') , '-') || '";"1";"' || coalesce(av7.attribute_value,'') || '";"' || coalesce(av6.attribute_value,'') || '";' else '"";"";"";"";"";"";"";"";"";"";"";"";"";"";"' end || case when a.company = 1 then coalesce(av2.attribute_value,'') || '"' || ';' || '"' || coalesce(av4.attribute_value,'') || '"' || ';' || '"' || coalesce(av32.attribute_value,'-') || '"' || ';' || '"' || coalesce(av1.attribute_value,'') || '"' || ';' || '"' || coalesce(av7.attribute_value,'') || '"' || ';' || '"' || coalesce(av6.attribute_value,'') || coalesce(av6.attribute_value,'') || ';' else '"";"";"";"";"";"";' end || '"1";"";"";"";"";"";"";"";"";"";' || IIF(a.home_id > 0, '"' || IIF(coalesce(h.CITY, '') <> '', h.CITY || ' ', '') || IIF(coalesce(h.STREET,'') <> '', h.STREET || ' ', '') || coalesce(h.S_NUMBER,'') || IIF(coalesce(h.S_LITER,'') <> '', '/' || h.S_LITER, '') || IIF(coalesce(a.A_HOME_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=22 av1.ATTRIBUTE_ID=1
|
left join ATTRIBUTE_VALUES as av2 on a.id=av2.ABONENT_ID and av2.ATTRIBUTE_ID=14 av2.ATTRIBUTE_ID=2
|
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=17 av4.ATTRIBUTE_ID=4
|
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=46
|
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 av66 on a.id=av66.ABONENT_ID and av66.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 av32 on a.id=av32.ABONENT_ID and av32.ATTRIBUTE_ID=32
|
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
|
where A.IS_FOLDER=0
|
and a.parent_id != 244 and a.deleted=0 and a.id is not Null
|
and a.parent_id not in (244) and a.deleted=0
|
{code}
|
... Тот же отчёт, в который попадает только информация, добавленная за последние сутки {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 || '"' || ';' || '"' || CASE WHEN AB.id is NULL THEN '0' WHEN AB.B_ADMIN=1 THEN '1' WHEN AB.B_OWN=1 THEN '1' WHEN AB.B_SYS=1 THEN '1' WHEN AB.B_NEGBAL=1 and (select count(*) from abonents_block where abonent_id=a.id)=1 THEN '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' || '"' || ';' || CASE WHEN AB.id is NULL THEN '' WHEN AB.B_ADMIN=1 THEN (select CAST(lpad(EXTRACT(DAY FROM b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM b_date) || ' 00:00:00' from abonents_block where B_ADMIN=1 and abonent_id=a.id) WHEN AB.B_OWN=1 THEN (select CAST(lpad(EXTRACT(DAY FROM b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM b_date) || ' 00:00:00' from abonents_block where B_OWN=1 and abonent_id=a.id) WHEN AB.B_SYS=1 THEN (select CAST(lpad(EXTRACT(DAY FROM b_date),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM b_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM b_date) || ' 00:00:00' from abonents_block where B_SYS=1 and abonent_id=a.id) WHEN AB.B_NEGBAL=1 and (select count(*) from abonents_block where abonent_id=a.id)=1 THEN '' end|| '"' || ';' || '"' || 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 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 {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}
|