Отчёты для СОРМ 3

Skip to end of metadata
Go to start of metadata
Вы просматриваете старую версию данной страницы. Смотрите текущую версию. Сравнить с текущим  |   просмотр истории страницы
Версия 1 Следующий >>
Отчёт по заключенным договорам на оказание услуг связи
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

where A.IS_FOLDER=0
and a.parent_id != 244 
and a.deleted=0 and a.id is not Null

Тот же отчёт, в который попадает только информация, добавленная за последние сутки

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

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
Введите метки, чтобы добавить к этой странице:
Please wait 
Ищите метку? просто начните печатать.