Вы просматриваете старую версию данной страницы. Смотрите текущую версию.
Сравнить с текущим |
просмотр истории страницы
Структура отчёта ttexpdbusrs_abonents
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;