with
mro_on as (
--все объекты, включенные в реестр на данный момент
select
object_id,
string_agg(registrynumber, E';\n') as registrynumber,
true as fromRegistry
from munregistryobject
where
munregistrytype_id = any(string_to_array('37248950', ', ')::bigint[])
and ato_id = (select head_id from subject where id = 1126886251)
and coalesce(startdate, '31.12.2024'::date)<='31.12.2024'::date
and coalesce(enddate, '31.12.2024'::date)>='31.12.2024'::date
group by object_id --группировка для объединения регистровых номеров
),
mro_off as (
--объекты, исключенные ранее из реестра
select
object_id,
'Исключен из реестра' as registrynumber,
false as fromRegistry
from munregistryobject
where false
and munregistrytype_id = any(string_to_array('37248950', ', ')::bigint[])
and ato_id = (select head_id from subject where id = 1126886251)
and enddate<'31.12.2024'::date
and not exists(select 1 from mro_on where mro_on.object_id = munregistryobject.object_id)
group by object_id --включена группировка, так как исключать могли и несколько раз
),
mro as (
--а теперь все вместе
select
object_id,
registrynumber,
fromRegistry
from mro_on
union all
select
object_id,
registrynumber::text,
fromRegistry
from mro_off
)
select
row_number() over () as rownum, --№ п/п
ot.caption as objecttype, --Вид объекта учета
mro.registrynumber as registrynumber,--Реестровый номер
concat_ws(E'\n',
unh.cadastrenumber,
to_char(unh.cadastreRegistrationDate, 'DD.MM.YYYY')
) as cadastrenumber,--Кадастровый номер объекта учета
unh."name", --Наименование объекта учета
nullif(
concat_ws(
', ',
coalesce(
unh.addresstext,
nullif(
concat_ws(
', ',
ae.addresstext,
unh.addressext
),
''
)
),
'ОКТМО: ' || oktmo.code
),
''
) as address, --Адрес (местоположение) объекта учета
case when mro.fromRegistry
then concat_ws(' / ',
bft_ru_format(unh.area),
bft_ru_format(unh.distance)
)
end as areastr, --Площадь / протяженность объекта учета, кв.м. / п.м.
case when mro.fromRegistry
then null--coalesce(bld.otherparam, inst.otherparam, unf.otherparam)
end as otherparam, --Иные сведения
case when mro.fromRegistry
then hef.inventorynumber
end as inventorynumber, --Инвентарный номер объекта учета
case when mro.fromRegistry
then hef.initialvalue
end as initialvalue, --Балансовая стоимость объекта учета, руб.
case when mro.fromRegistry
then hef.residualvalue
end as residualvalue, -- Остаточная стоимость объекта учета, руб.
case when mro.fromRegistry
then hef.cadastrevalue
end as cadastrevalue, --Кадастровая стоимость объекта учета, руб.
case when mro.fromRegistry
then hef.initialvalue is not null
end as showinitialvalue, --Флаг отображения Балансовая стоимость объекта учета, руб.
case when mro.fromRegistry
then hef.residualvalue is not null
else null
end as showresidualvalue, --Флаг отображения Остаточная стоимость объекта учета, руб.
case when mro.fromRegistry
then hef.cadastrevalue is not null
end as showcadastrevalue, --Флаг отображения Кадастровая стоимость объекта учета, руб.
case when mro.fromRegistry
then currOwn.startdate
else case when currOwn.startdate is not null
then currOwn.startdate
else endown.startdate
end
end as currOwnStartDate, --Дата возникновения права муниципальной собственности на недвижимое имущество
case when mro.fromRegistry
then null
else case when currOwn.startdate is not null
then currOwn.enddate
else endOwn.enddate
end
end as endOwnEndDate, --Дата прекращения права муниципальной собственности на недвижимое имущество
case when mro.fromRegistry
then currOwn.startReason
else case when currOwn.startdate is not null
then currOwn.startReason
else endown.startReason
end
end as currOwnReason, --Реквизиты документов оснований возникновения права муниципальной собственности на недвижимое имущество
case when mro.fromRegistry
then null
else case when currOwn.startdate is not null
then currOwn.endReason
else endOwn.endReason
end
end as endOwnReason, --Реквизиты документов оснований прекращения права муниципальной собственности на недвижимое имущество
case when mro.fromRegistry
then rightowners.rightowner
else null
end as rightowner, --Сведения о правообладателе
case when mro.fromRegistry
then encumbrance.contractnames
else null
end as contractnames, --Установленные в отношении земельного участка ограничения (обременения) (вид, основания и даты возникновения и прекращения, лицо, в пользу которого они установлены)
case when mro.fromRegistry
then currOwn.reginfo
else null
end as ownreginfo, --Сведения о регистрации права собственности
case when mro.fromRegistry
then otherreg.reginfo
else null
end as otherreginfo, --Сведения о регистрации иного вещного права
case when mro.fromRegistry
then coalesce(
bld.realAppointmentStr,
inst.realAppointmentStr,
unf.realAppointmentStr,
hc.realAppointmentStr,
kni.caption
)
else null
end as realappointment,--Назначение объекта учета
case when mro.fromRegistry
then concat_ws(' / ',
nullif(coalesce(bld.floorNumberText, inst.floorNumberText, unf.floorNumberText, hc.floorNumberText), ''),
nullif(coalesce(bld.undergroundFloorNumberText, inst.undergroundFloorNumberText, unf.undergroundFloorNumberText, hc.undergroundFloorNumberText), '')
)
else null
end as floors, --Этажность (подземная этажность) объекта учета
case when mro.fromRegistry
then concat_ws(E'\n', steadinfo.info, enk.name)
else null
end as steadcad, --Сведения о земельном участке, на котором расположен объект учета / об объекте единого недвижимого комплекса
hld.objectImprovements, --Сведения об изменениях объекта учета
case when mro.fromRegistry
then otherright.righttype
else null
end as otherrighttype, --Вид иного вещного права
case when mro.fromRegistry
then otherright.startdate
else null
end as otherrightstart, --Дата возникновения иного вещного права на объект учета
case when mro.fromRegistry
then otherright.startreason
else null
end as otherrightstartreason, --Реквизиты документов-оснований возникновения иного вещного права на объект учета
case when mro.fromRegistry
then otherright.enddate
else null
end as otherrightend, --Дата, основание прекращения иного вещного права на объект учета
case when mro.fromRegistry
then otherright.endreason
else null
end as otherrightendreason,--Реквизиты документов-оснований прекращения иного вещного права на объект учета
----------------------------
--служебные поля
mro.fromRegistry --включен в реестр
from mro
inner join unholding unh on unh.head_id = mro.object_id
left outer join holding hld on hld.id = unh.id
left join building bld on bld.id = unh.id
left outer join installation inst on inst.id = unh.id
left outer join unfinished unf on unf.id = unh.id
left outer join holdingcomplex hc on hc.id = unh.id
inner join objecttype ot on ot.id = unh.reestrobjtype_id
left outer join addresselement ae on
ae.head_id = unh.address_id
and '31.12.2024'::date::date between ae.startdate and coalesce(ae.enddate, 'infinity')
left outer join oktmo on
oktmo.head_id = coalesce(bld.oktmo_id, inst.oktmo_id, unf.oktmo_id)
and '31.12.2024'::date between oktmo.startdate and coalesce(oktmo.enddate, 'infinity')::date
left outer join kni on kni.head_id = hld.kni_id
and '31.12.2024'::date between kni.startdate and coalesce(kni.enddate, 'infinity')::date
--ЗУ, на которых расположено сооружение
left outer join lateral (
select
E'ЗУ:\n' ||
nullif (
string_agg(
concat_ws(E'\n',
'кад. номер ' || coalesce(h.cadastrenumber, '—'),
'площадь ' || coalesce(bft_ru_format(h.area), '-'),
'ОКФС ' || coalesce(rights.okfs, '-')
),
E';\n'
),
''
) as info
from
object_object objobj
inner join unholding h on h.head_id = objobj.secondaryobject_id
left outer join lateral(
select
string_agg(o.caption || ' ('|| o.code || ')', '; ') as okfs
from lawinstanceobj lo
inner join lawinstance l on l.id = lo.lawinstance_id
inner join righttab r on r.id = l.id
inner join okfs o on o.head_id = r.okfs_id
inner join lawinstancetype lt on l.reestrobjtype_id = lt.id
where
lo.object_id = h.head_id
and lt.syscode = 'OWNERSHIP'
and '31.12.2024'::date::date between l.startdate and coalesce(l.enddate, 'infinity')
and '31.12.2024'::date::date between o.startdate and coalesce(o.enddate, 'infinity')
) as rights on true
where
objobj.primaryobject_id = unh.head_id
and '31.12.2024'::date::date between h.startdate and coalesce(h.enddate, 'infinity')
) as steadinfo on true
--Сведения об объекте недвижимого комплекса
left outer join lateral (
select
'ЕНК: ' ||
nullif(
string_agg(u.name, E';\n'),
''
) as name
from complexpair cp
inner join unholding u on u.id = cp.holdingComplex_id
where cp.object_id = unh.head_id
) as enk on true
--Экономические показатели
left outer join lateral(
select
hef.inventorynumber,
hef.initialvalue,
hef.cadastrevalue,
hef.residualvalue
from holdingeconomicfactors hef
where hef.dateactual<='31.12.2024'::date
and hef.holding_id = unh.head_id
order by hef.dateactual desc
limit 1
) as hef on true
--Текущее право собственности
left outer join lateral(
select
string_agg(to_char(law.startdate, 'DD.MM.YYYY'), E';\n' order by startdate) as startdate,
string_agg(to_char(law.enddate, 'DD.MM.YYYY'), E';\n' order by startdate) as enddate,
string_agg(startDocs.docnames, E';\n' order by startdate) as startreason,
string_agg(endDocs.docnames, E';\n' order by startdate) as endreason,
string_agg(
concat_ws(
' ',
lt.caption,
'-',
coalesce('№ ' || nullif(law.gosregnum, '') , 'б/н'),
'от',
coalesce(to_char(law.gosregdate, 'DD.MM.YYYY'), '__.__.______')
),
E';\n' order by law.startdate
) as reginfo
from lawinstanceobj lobj
inner join lawinstance law on law.id = lobj.lawinstance_id
inner join lawinstancesub ls on ls.lawinstance_id = law.id and ls.subject_id = (select head_id from subject where id = 1126886251)
inner join lawinstancetype lt on lt.id = law.reestrobjtype_id
left outer join lateral(
select
string_agg(sd.displayname, E';\n' order by sd.docdate) as docnames
from lawinstancedoc ld
inner join saumidocument sd on sd.head_id = ld.document_id
inner join documentrole dr on dr.head_id = ld.documentrole_id
where
'31.12.2024'::date::date between sd.startdate and coalesce(sd.enddate, 'infinity')
and '31.12.2024'::date::date between dr.startdate and coalesce(dr.enddate, 'infinity')
and dr.caption = 'Основание возникновения права'
and ld.lawinstance_id = law.id
) as startDocs on true
left outer join lateral(
select
string_agg(sd.displayname, E';\n' order by sd.docdate) as docnames
from lawinstancedoc ld
inner join saumidocument sd on sd.head_id = ld.document_id
inner join documentrole dr on dr.head_id = ld.documentrole_id
where
'31.12.2024'::date::date between sd.startdate and coalesce(sd.enddate, 'infinity')
and '31.12.2024'::date::date between dr.startdate and coalesce(dr.enddate, 'infinity')
and dr.caption = 'Основание прекращения права'
and ld.lawinstance_id = law.id
) as endDocs on true
where
'31.12.2024'::date::date between law.startdate and coalesce(law.enddate, 'infinity')
and lt.syscode = 'OWNERSHIP'
and lobj.object_id = unh.head_id
) as currOwn on true
--Последнее завершенное право собственности
left outer join lateral(
select
row_number() over fw as rownum,
first_value(to_char(law.startdate, 'DD.MM.YYYY')) over fw as startdate,
first_value(to_char(law.enddate, 'DD.MM.YYYY')) over fw as enddate,
first_value(startDocs.docnames) over fw as startReason,
first_value(endDocs.docnames) over fw as endReason
from lawinstanceobj lobj
inner join lawinstance law on law.id = lobj.lawinstance_id
inner join lawinstancesub ls on ls.lawinstance_id = law.id and ls.subject_id = (select head_id from subject where id = 1126886251)
inner join lawinstancetype lt on lt.id = law.reestrobjtype_id
left outer join lateral(
select
string_agg(sd.displayname, E';\n' order by sd.docdate) as docnames
from lawinstancedoc ld
inner join saumidocument sd on sd.head_id = ld.document_id
inner join documentrole dr on dr.head_id = ld.documentrole_id
where
'31.12.2024'::date::date between sd.startdate and coalesce(sd.enddate, 'infinity')
and '31.12.2024'::date::date between dr.startdate and coalesce(dr.enddate, 'infinity')
and dr.caption = 'Основание возникновения права'
and ld.lawinstance_id = law.id
) as startDocs on true
left outer join lateral(
select
string_agg(sd.displayname, E';\n' order by sd.docdate) as docnames
from lawinstancedoc ld
inner join saumidocument sd on sd.head_id = ld.document_id
inner join documentrole dr on dr.head_id = ld.documentrole_id
where
'31.12.2024'::date::date between sd.startdate and coalesce(sd.enddate, 'infinity')
and '31.12.2024'::date::date between dr.startdate and coalesce(dr.enddate, 'infinity')
and dr.caption = 'Основание прекращения права'
and ld.lawinstance_id = law.id
) as endDocs on true
where
law.enddate < '31.12.2024'::date
and lt.syscode = 'OWNERSHIP'
and lobj.object_id = unh.head_id
window fw as (partition by lobj.object_id order by law.enddate desc)
) as endOwn on endOwn.rownum=1
--Иные права
left outer join lateral(
select
string_agg(lt.caption, E';\n' order by startdate) as righttype,
string_agg(to_char(law.startdate, 'DD.MM.YYYY'), E';\n' order by startdate) as startdate,
string_agg(to_char(law.enddate, 'DD.MM.YYYY'), E';\n' order by startdate) as enddate,
string_agg(startDocs.docnames, E';\n' order by startdate) as startreason,
string_agg(endDocs.docnames, E';\n' order by startdate) as endreason,
string_agg(
concat_ws(
' ',
lt.caption,
'-',
coalesce('№ ' || nullif(law.gosregnum, '') , 'б/н'),
'от',
coalesce(to_char(law.gosregdate, 'DD.MM.YYYY'), '__.__.______')
),
E';\n' order by law.startdate
) as reginfo
from lawinstanceobj lobj
inner join lawinstance law on law.id = lobj.lawinstance_id
inner join lawinstancetype lt on lt.id = law.reestrobjtype_id
left outer join lateral(
select
string_agg(sd.displayname, E';\n' order by sd.docdate) as docnames
from lawinstancedoc ld
inner join saumidocument sd on sd.head_id = ld.document_id
inner join documentrole dr on dr.head_id = ld.documentrole_id
where
'31.12.2024'::date::date between sd.startdate and coalesce(sd.enddate, 'infinity')
and '31.12.2024'::date::date between dr.startdate and coalesce(dr.enddate, 'infinity')
and dr.caption = 'Основание возникновения права'
and ld.lawinstance_id = law.id
) as startDocs on true
left outer join lateral(
select
string_agg(sd.displayname, E';\n' order by sd.docdate) as docnames
from lawinstancedoc ld
inner join saumidocument sd on sd.head_id = ld.document_id
inner join documentrole dr on dr.head_id = ld.documentrole_id
where
'31.12.2024'::date::date between sd.startdate and coalesce(sd.enddate, 'infinity')
and '31.12.2024'::date::date between dr.startdate and coalesce(dr.enddate, 'infinity')
and dr.caption = 'Основание прекращения права'
and ld.lawinstance_id = law.id
) as endDocs on true
where
'31.12.2024'::date::date between law.startdate and coalesce(law.enddate, 'infinity')
and lt.syscode in ('OPER_UPR', 'HOS_VED', 'IN_KAZNA')
and lobj.object_id = unh.head_id
) as otherRight on true
--Правообладатели
left outer join lateral(
select
string_agg(
case
when lt.syscode='IN_KAZNA' then sub.fullname
else concat_ws(
', ',
sub.fullname,
'ОПФ: '|| opf.code,
'ИНН: '|| l.inn,
'КПП: '|| l.kpp,
'ОГРН: '|| l.ogrn,
'Адрес: '|| bft_address_subject(sub.head_id, '31.12.2024'::date),
'ОКТМО: ' || oktmo.code
)
end,
E';\n' order by law.startdate
) as rightowner
from lawinstanceobj lobj
inner join lawinstance law on
law.id = lobj.lawinstance_id
and '31.12.2024'::date::date between law.startdate and coalesce(law.enddate, 'infinity')
inner join lawinstancetype lt on lt.id = law.reestrobjtype_id
left outer join lawinstancesub ls on ls.lawinstance_id = law.id
inner join subject as sub on
case
when lt.syscode = 'IN_KAZNA' then sub.head_id = law.owner_id
else sub.head_id = ls.subject_id
end
and '31.12.2024'::date::date between sub.startdate and coalesce(sub.enddate, 'infinity')
left outer join legpersubject l on
l.id = sub.id
left outer join oktmo on
oktmo.head_id = l.oktmo_id
and '31.12.2024'::date::date between oktmo.startdate and coalesce(oktmo.enddate, 'infinity')
left outer join opf on
opf.head_id = l.opf_id
and '31.12.2024'::date::date between opf.startdate and coalesce(opf.enddate, 'infinity')
where
lt.syscode in ('OPER_UPR', 'HOS_VED', 'IN_KAZNA')
and lobj.object_id = unh.head_id
) as rightowners on true
--Обремения
left outer join lateral (
select
string_agg(
concat_ws(' ',
lt.caption,
'№',
coalesce(c.docnumber, '_____'),
'от',
coalesce(to_char(c.docdate, 'DD.MM.YYYY'), '__.__.______')
) ||
', ' ||
coalesce(to_char(c.planstartdate, 'DD.MM.YYYY'), '__.__.______') ||
'-' ||
coalesce(to_char(c.planenddate, 'DD.MM.YYYY'), '__.__.______') ||
', '||
sub.description,
E';\n' order by law.startdate
) as contractnames
from lawinstanceobj lobj
inner join lawinstance law on law.id = lobj.lawinstance_id
inner join lawinstancetype lt on lt.id = law.reestrobjtype_id
inner join contract c on c.id = law.id
left outer join lateral (
select
string_agg(
concat_ws(
', ',
sub.fullname,
'ОПФ: '|| opf.code,
'ИНН: '|| l.inn,
'КПП: '|| l.kpp,
'ОГРН: '|| l.ogrn,
'Адрес: '|| bft_address_subject(sub.head_id, '31.12.2024'::date),
'ОКТМО: ' || oktmo.code
),
E';\n' order by law.startdate
) as description
from lawinstancesub ls
inner join subject as sub on
sub.head_id = ls.subject_id
and '31.12.2024'::date::date between sub.startdate and coalesce(sub.enddate, 'infinity')
left outer join legpersubject l on
l.id = sub.id
left outer join oktmo on
oktmo.head_id = l.oktmo_id
and '31.12.2024'::date::date between oktmo.startdate and coalesce(oktmo.enddate, 'infinity')
left outer join opf on
opf.head_id = l.opf_id
and '31.12.2024'::date::date between opf.startdate and coalesce(opf.enddate, 'infinity')
where ls.lawinstance_id = law.id
) as sub on true
where
'31.12.2024'::date::date between law.startdate and coalesce(law.enddate, 'infinity')
and lobj.object_id = unh.head_id
) as encumbrance on true
--Номера гос регистраций 'OPER_UPR', 'HOS_VED', 'IN_KAZNA'
left outer join lateral(
select
string_agg(
concat_ws(
' ',
lt.caption,
'-',
coalesce('№ ' || nullif(law.gosregnum, '') , 'б/н'),
'от',
coalesce(to_char(law.gosregdate, 'DD.MM.YYYY'), '__.__.______')
),
E';\n' order by law.startdate
) as reginfo
from lawinstanceobj lobj
inner join lawinstance law on law.id = lobj.lawinstance_id
inner join lawinstancetype lt on lt.id = law.reestrobjtype_id
where
'31.12.2024'::date::date between law.startdate and coalesce(law.enddate, 'infinity')
and lt.syscode in ('OPER_UPR', 'HOS_VED', 'IN_KAZNA')
and lobj.object_id = unh.head_id
) as otherreg on true
where
'31.12.2024'::date::date between unh.startdate and coalesce(unh.enddate, 'infinity')
and ot.appobjname in ('saumi.building', 'saumi.installation', 'saumi.unfinished', 'saumi.holdingComplex');
Telegram: DB developers channel