Интеграция с СОРМ ТехАгрос

set heading off;
select distinct  '100|7| | | | | | | | | | | |',
	case when u.ip is null then  ' | | |  | | | | | | |'
		else  '' || uf_ip2string(u.ip) ||  '|' || u.mask || '| |  | ' || u.mac || '| | | | | |'
	end, a.id,
	case when a.company=0 then '|1| | | |' || a.name || '|' ||coalesce(av22.attribute_value,'')|| '|22|' || coalesce(av14.attribute_value,'') || '|' || coalesce(av13.attribute_value,'') ||'|' || coalesce(av17.attribute_value,'') || '|' || coalesce(av16.attribute_value,'') || '| | | | |'
		else '|2| | | | | | | | | | | | |' || a.name || '| |'
 	end  , 
	case when A.home_id is null  then '1| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
	else	case when  h.country is null  then   '1| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
		else	case when  h.zip_code is null  then   '1| |' || h.country || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
			else	case when  h.region is null  then   '1| |' || h.country || '|' || h.zip_code || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
				else	case when  h.city is null  then   '1| |' || h.country || '|' || h.zip_code  || '|' || h.region || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
					else	case when  h.street is null  then    '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
						else	case when  h.s_number is null  then   '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
							else	case when  h.s_liter is null  then   '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
								else	case when a.a_home_number is null  then   '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '|' || h.s_liter || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
									else  '1| |' || h.country || '|' || h.zip_code || '|' || h.region || '| |' || h.city || '|' || h.street || '|' || h.s_number || '|' || h.s_liter || '|' || a.a_home_number || '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |'
									end
								end
							end	
						end
					end	
				end
			end
		end
	end,
	 a.contract_number ,
	case when a.activate_date is null then '| | | | |' 
		else '| |' ||  EXTRACT(YEAR FROM a.activate_date)||'.' ||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))  || '| | |'
	end ,
 	case when  (ab.b_negbal=1 and (os.status<>5 or os.status is null)) then  '2|'
		else  '1|'
	end,
	' | | | | | | | | | | | | | | | | | '
	





from abonents as a
left join users as u on a.id=u.abonent_id
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 av22 on a.id=av22.ABONENT_ID and av22.ATTRIBUTE_ID=17

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

left join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID
left join objects_status os on a.id=os.object_id

where a.parent_id not in (244)
and a.parent_id not in (25416) 
and a.parent_id not in (25322)
and a.parent_id not in (19902)
and a.parent_id not in (24013)
and a.parent_id not in (23995)
and a.parent_id not in (2)
and a.parent_id not in (8606)
and a.parent_id not in (19892)
and a.parent_id not in (25625)
and a.is_folder=0
and a.deleted=0
and a.id is not null
and u.nas_id not in (1137)
and u.nas_id not in (1138)
and u.nas_id not in (1122)
order by a.ID;

set heading off; select 
    '10|100|8|||||||||||' || a.contract_number || '||||||' ||
    iif(fo.operator_name<>'' and fo.operator_name is not null,
        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) || ' ' || CAST(lpad(EXTRACT(hour FROM  fo.OP_DATE),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM  fo.OP_DATE),2,'0') AS varchar(2)),
        CAST(lpad(EXTRACT(DAY FROM  fo.SYSTEM_DATE),2,'0') AS varchar(2))||'.'|| CAST(lpad(EXTRACT(MONTH FROM  fo.system_date),2,'0') AS varchar(2))||'.'|| EXTRACT(YEAR FROM  fo.system_date) || ' ' || CAST(lpad(EXTRACT(hour FROM  fo.system_date),2,'0') AS varchar(2))||':'|| CAST(lpad(EXTRACT(minute FROM  fo.system_date),2,'0') AS varchar(2))
    )||':'|| '00||' ||
    cast(fo.op_summa/10000000000.00 as varchar(40)) || '|||||||||||' ||
    coalesce(fo.operator_name,'') || '|' || a.contract_number || '|'
    || coalesce((select first 1 uf_ip2string(ip) from users u where u.abonent_id = a.id and u.ip is not null),'') || '|32||||' || a.name || '|||||||||||||||||||||'
from
    finance_operations fo
join
    abonents a
    on fo.abonent_id = a.id
where
    fo.abonent_id is not null and
    fo.op_type = 2 and
    fo.storno =  0 and
    a.deleted = 0 and
    a.parent_id not in (244) and
    ((fo.op_date between  dateadd (-15 minute  to current_timestamp) and current_timestamp) or ((fo.operator_name='' or fo.operator_name is null) and (fo.SYSTEM_DATE between  dateadd (-15 minute to current_timestamp) and current_timestamp)))
order by fo.op_date;

set heading off;
select distinct  ' |' || id || ' |' , uf_ip2string(ip) || ' |' ,' |' ||' |' || STAT_FIRST_AUTH || ' |' ,' |' || name || ' |' ,' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |' ||' |'
from nas
where operator_id=3 or  operator_id=23970
order by ID;