def get_igac_property_record_card_query(names, schema, plot_t_ids, parcel_fmi,
parcel_number, previous_parcel_number):
custom_filter_plots = get_custom_filter_plots(names, schema, plot_t_ids)
custom_filter_parcels = get_custom_filter_parcels(names, schema,
plot_t_ids)
query = """
WITH
_unidad_area_terreno AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename = '{LC_PLOT_T}' AND columnname = '{LC_PLOT_T_PLOT_AREA_F}' LIMIT 1
),
_terrenos_seleccionados AS (
{custom_filter_plots}
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_predios_seleccionados AS (
{custom_filter_parcels}
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_info_predio AS (
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_PARCEL_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Nombre', {LC_PARCEL_T}.{COL_BAUNIT_T_NAME_F}
, 'Departamento', {LC_PARCEL_T}.{LC_PARCEL_T_DEPARTMENT_F}
, 'Municipio', {LC_PARCEL_T}.{LC_PARCEL_T_MUNICIPALITY_F}
, 'NUPRE', {LC_PARCEL_T}.{LC_PARCEL_T_NUPRE_F}
, 'Id operación', {LC_PARCEL_T}.{LC_PARCEL_T_ID_OPERATION_F}
, 'FMI', ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F})
, 'Número predial', {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F}
, 'Número predial anterior', {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F}
, 'Tipo', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_BAUNIT_TYPE_D} WHERE {T_ID_F} = {LC_PARCEL_T}.{LC_PARCEL_T_TYPE_F})
)) ORDER BY {LC_PARCEL_T}.{T_ID_F}) FILTER(WHERE {LC_PARCEL_T}.{T_ID_F} IS NOT NULL) AS _predio_
FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} = {LC_PARCEL_T}.{T_ID_F}
WHERE {LC_PARCEL_T}.{T_ID_F} IN (SELECT * FROM _predios_seleccionados)
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_F} IS NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_UNIT_F} IS NULL
GROUP BY {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F}
),
_info_terreno AS (
SELECT {LC_PLOT_T}.{T_ID_F},
JSON_BUILD_OBJECT('id', {LC_PLOT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT(CONCAT('Área' , (SELECT * FROM _unidad_area_terreno)), {LC_PLOT_T}.{LC_PLOT_T_PLOT_AREA_F},
'{LC_PARCEL_T}', COALESCE(_info_predio._predio_, '[]')
)) AS _terreno_
FROM {schema}.{LC_PLOT_T} LEFT JOIN _info_predio ON _info_predio.{COL_UE_BAUNIT_T_LC_PLOT_F} = {LC_PLOT_T}.{T_ID_F}
WHERE {LC_PLOT_T}.{T_ID_F} IN (SELECT * FROM _terrenos_seleccionados)
ORDER BY {LC_PLOT_T}.{T_ID_F}
)
SELECT JSON_BUILD_OBJECT('{LC_PLOT_T}', COALESCE(JSON_AGG(_info_terreno._terreno_), '[]')) FROM _info_terreno
"""
query = query.format(
**vars(
names), # Custom keys are searched in Table And Field Names object
schema=schema,
custom_filter_plots=custom_filter_plots,
custom_filter_parcels=custom_filter_parcels,
parcel_fmi=parcel_fmi,
parcel_number=parcel_number,
previous_parcel_number=previous_parcel_number)
return query
def get_igac_basic_query(names, schema, plot_t_ids, parcel_fmi, parcel_number,
previous_parcel_number):
custom_filter_plots = get_custom_filter_plots(names, schema, plot_t_ids)
custom_filter_parcels = get_custom_filter_parcels(names, schema,
plot_t_ids)
query = """
WITH
_unidad_area_terreno AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename LIKE '{LC_PLOT_T}' AND columnname LIKE '{LC_PLOT_T_PLOT_AREA_F}' LIMIT 1
),
_unidad_area_construida_uc AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename LIKE '{LC_BUILDING_UNIT_T}' AND columnname LIKE '{LC_BUILDING_UNIT_T_BUILT_AREA_F}' LIMIT 1
),
_terrenos_seleccionados AS (
{custom_filter_plots}
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_predios_seleccionados AS (
{custom_filter_parcels}
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_construcciones_seleccionadas AS (
SELECT {COL_UE_BAUNIT_T_LC_BUILDING_F} FROM {schema}.{COL_UE_BAUNIT_T} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} IN (SELECT _predios_seleccionados.{T_ID_F} FROM _predios_seleccionados WHERE _predios_seleccionados.{T_ID_F} IS NOT NULL) AND {COL_UE_BAUNIT_T_LC_BUILDING_F} IS NOT NULL
),
_unidadesconstruccion_seleccionadas AS (
SELECT {LC_BUILDING_UNIT_T}.{T_ID_F} FROM {schema}.{LC_BUILDING_UNIT_T} WHERE {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_F} IN (SELECT {COL_UE_BAUNIT_T_LC_BUILDING_F} FROM _construcciones_seleccionadas)
),
_uc_extdireccion AS (
SELECT {EXT_ADDRESS_S}.{EXT_ADDRESS_S_LC_BUILDING_UNIT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {EXT_ADDRESS_S}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo dirección', (SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_ADDRESS_TYPE_F}),
'Código postal', {EXT_ADDRESS_S}.{EXT_ADDRESS_S_POSTAL_CODE_F},
'Dirección', trim(concat(COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_MAIN_ROAD_CLASS_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_MAIN_ROAD_CLASS_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_VALUE_MAIN_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_LETTER_MAIN_ROAD_F} || ' ', ''),
COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_CITY_SECTOR_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_CITY_SECTOR_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_VALUE_GENERATOR_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_LETTER_GENERATOR_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_NUMBER_F} || ' ', ''),
COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_PARCEL_SECTOR_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_SECTOR_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_COMPLEMENT_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_NAME_F} || ' ', '')
))))
ORDER BY {EXT_ADDRESS_S}.{T_ID_F}) FILTER(WHERE {EXT_ADDRESS_S}.{T_ID_F} IS NOT NULL) AS {EXT_ADDRESS_S}
FROM {schema}.{EXT_ADDRESS_S} WHERE {EXT_ADDRESS_S_LC_BUILDING_UNIT_F} IN (SELECT * FROM _unidadesconstruccion_seleccionadas)
GROUP BY {EXT_ADDRESS_S}.{EXT_ADDRESS_S_LC_BUILDING_UNIT_F}
),
_info_uc AS (
SELECT {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_BUILDING_UNIT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Número de pisos', {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_TOTAL_FLOORS_F},
'Número de habitaciones', {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_TOTAL_ROOMS_F},
'Número de baños', {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_TOTAL_BATHROOMS_F},
'Número de locales', {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_TOTAL_LOCALS_F},
'Tipo construcción', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_BUILDING_TYPE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_TYPE_F}),
'Tipo unidad de construcción', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_BUILDING_UNIT_TYPE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_UNIT_TYPE_F}),
'Tipo de planta', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_BUILDING_FLOOR_TYPE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_FLOOR_TYPE_F}),
'Tipo dominio', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_DOMAIN_BUILDING_TYPE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_DOMAIN_TYPE_F}),
'Ubicación en el piso', {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_FLOOR_F},
CONCAT('Área construida' , (SELECT * FROM _unidad_area_construida_uc)), {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILT_AREA_F},
'Uso', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_BUILDING_UNIT_USE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_USE_F}),
'{EXT_ADDRESS_S}', COALESCE(_uc_extdireccion.{EXT_ADDRESS_S}, '[]')
)) ORDER BY {LC_BUILDING_UNIT_T}.{T_ID_F}) FILTER(WHERE {LC_BUILDING_UNIT_T}.{T_ID_F} IS NOT NULL) AS _unidadconstruccion_
FROM {schema}.{LC_BUILDING_UNIT_T}
LEFT JOIN _uc_extdireccion ON {LC_BUILDING_UNIT_T}.{T_ID_F} = _uc_extdireccion.{EXT_ADDRESS_S_LC_BUILDING_UNIT_F}
WHERE {LC_BUILDING_UNIT_T}.{T_ID_F} IN (SELECT * FROM _unidadesconstruccion_seleccionadas)
GROUP BY {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_F}
),
_c_extdireccion AS (
SELECT {EXT_ADDRESS_S}.{EXT_ADDRESS_S_LC_BUILDING_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {EXT_ADDRESS_S}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo dirección', (SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_ADDRESS_TYPE_F}),
'Código postal', {EXT_ADDRESS_S}.{EXT_ADDRESS_S_POSTAL_CODE_F},
'Dirección', trim(concat(COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_MAIN_ROAD_CLASS_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_MAIN_ROAD_CLASS_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_VALUE_MAIN_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_LETTER_MAIN_ROAD_F} || ' ', ''),
COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_CITY_SECTOR_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_CITY_SECTOR_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_VALUE_GENERATOR_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_LETTER_GENERATOR_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_NUMBER_F} || ' ', ''),
COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_PARCEL_SECTOR_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_SECTOR_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_COMPLEMENT_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_NAME_F} || ' ', '')
))))
ORDER BY {EXT_ADDRESS_S}.{T_ID_F}) FILTER(WHERE {EXT_ADDRESS_S}.{T_ID_F} IS NOT NULL) AS {EXT_ADDRESS_S}
FROM {schema}.{EXT_ADDRESS_S} WHERE {EXT_ADDRESS_S_LC_BUILDING_F} IN (SELECT * FROM _construcciones_seleccionadas)
GROUP BY {EXT_ADDRESS_S}.{EXT_ADDRESS_S_LC_BUILDING_F}
),
_info_construccion AS (
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_BUILDING_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Área', {LC_BUILDING_T}.{LC_BUILDING_T_BUILDING_AREA_F},
'{EXT_ADDRESS_S}', COALESCE(_c_extdireccion.{EXT_ADDRESS_S}, '[]'),
'{LC_BUILDING_UNIT_T}', COALESCE(_info_uc._unidadconstruccion_, '[]')
)) ORDER BY {LC_BUILDING_T}.{T_ID_F}) FILTER(WHERE {LC_BUILDING_T}.{T_ID_F} IS NOT NULL) AS _construccion_
FROM {schema}.{LC_BUILDING_T} LEFT JOIN _c_extdireccion ON {LC_BUILDING_T}.{T_ID_F} = _c_extdireccion.{EXT_ADDRESS_S_LC_BUILDING_F}
LEFT JOIN _info_uc ON {LC_BUILDING_T}.{T_ID_F} = _info_uc.{LC_BUILDING_UNIT_T_BUILDING_F}
LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_F} = {LC_BUILDING_T}.{T_ID_F}
WHERE {LC_BUILDING_T}.{T_ID_F} IN (SELECT * FROM _construcciones_seleccionadas)
GROUP BY {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F}
),
_info_predio AS (
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_PARCEL_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Nombre', {LC_PARCEL_T}.{COL_BAUNIT_T_NAME_F},
'Departamento', {LC_PARCEL_T}.{LC_PARCEL_T_DEPARTMENT_F},
'Municipio', {LC_PARCEL_T}.{LC_PARCEL_T_MUNICIPALITY_F},
'NUPRE', {LC_PARCEL_T}.{LC_PARCEL_T_NUPRE_F},
'Id operación', {LC_PARCEL_T}.{LC_PARCEL_T_ID_OPERATION_F},
'FMI', ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}),
'Número predial', {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F},
'Número predial anterior', {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F},
'Tipo', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_BAUNIT_TYPE_D} WHERE {T_ID_F} = {LC_PARCEL_T}.{LC_PARCEL_T_TYPE_F}),
'{LC_BUILDING_T}', COALESCE(_info_construccion._construccion_, '[]')
)) ORDER BY {LC_PARCEL_T}.{T_ID_F}) FILTER(WHERE {LC_PARCEL_T}.{T_ID_F} IS NOT NULL) AS _predio_
FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} = {LC_PARCEL_T}.{T_ID_F}
LEFT JOIN _info_construccion ON {LC_PARCEL_T}.{T_ID_F} = _info_construccion.{COL_UE_BAUNIT_T_PARCEL_F}
WHERE {LC_PARCEL_T}.{T_ID_F} IN (SELECT * FROM _predios_seleccionados)
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_F} IS NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_UNIT_F} IS NULL
GROUP BY {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F}
),
_t_extdireccion AS (
SELECT {EXT_ADDRESS_S}.{EXT_ADDRESS_S_LC_PLOT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {EXT_ADDRESS_S}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo dirección', (SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_ADDRESS_TYPE_F}),
'Código postal', {EXT_ADDRESS_S}.{EXT_ADDRESS_S_POSTAL_CODE_F},
'Dirección', trim(concat(COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_MAIN_ROAD_CLASS_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_MAIN_ROAD_CLASS_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_VALUE_MAIN_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_LETTER_MAIN_ROAD_F} || ' ', ''),
COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_CITY_SECTOR_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_CITY_SECTOR_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_VALUE_GENERATOR_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_LETTER_GENERATOR_ROAD_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_NUMBER_F} || ' ', ''),
COALESCE((SELECT {DISPLAY_NAME_F} FROM {schema}.{EXT_ADDRESS_TYPE_PARCEL_SECTOR_D} WHERE {T_ID_F} = {EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_SECTOR_F}) || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_COMPLEMENT_F} || ' ', ''),
COALESCE({EXT_ADDRESS_S}.{EXT_ADDRESS_S_PARCEL_NAME_F} || ' ', '')
))))
ORDER BY {EXT_ADDRESS_S}.{T_ID_F}) FILTER(WHERE {EXT_ADDRESS_S}.{T_ID_F} IS NOT NULL) AS {EXT_ADDRESS_S}
FROM {schema}.{EXT_ADDRESS_S} WHERE {EXT_ADDRESS_S_LC_PLOT_F} IN (SELECT * FROM _terrenos_seleccionados)
GROUP BY {EXT_ADDRESS_S}.{EXT_ADDRESS_S_LC_PLOT_F}
),
_info_terreno AS (
SELECT {LC_PLOT_T}.{T_ID_F},
JSON_BUILD_OBJECT('id', {LC_PLOT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT(CONCAT('Área' , (SELECT * FROM _unidad_area_terreno)), {LC_PLOT_T}.{LC_PLOT_T_PLOT_AREA_F},
'{EXT_ADDRESS_S}', COALESCE(_t_extdireccion.{EXT_ADDRESS_S}, '[]'),
'{LC_PARCEL_T}', COALESCE(_info_predio._predio_, '[]')
)) AS _terreno_
FROM {schema}.{LC_PLOT_T} LEFT JOIN _info_predio ON _info_predio.{COL_UE_BAUNIT_T_LC_PLOT_F} = {LC_PLOT_T}.{T_ID_F}
LEFT JOIN _t_extdireccion ON {LC_PLOT_T}.{T_ID_F} = _t_extdireccion.{EXT_ADDRESS_S_LC_PLOT_F}
WHERE {LC_PLOT_T}.{T_ID_F} IN (SELECT * FROM _terrenos_seleccionados)
ORDER BY {LC_PLOT_T}.{T_ID_F}
)
SELECT JSON_BUILD_OBJECT('{LC_PLOT_T}', COALESCE(JSON_AGG(_info_terreno._terreno_), '[]')) FROM _info_terreno
"""
query = query.format(
**vars(
names), # Custom keys are searched in Table And Field Names object
schema=schema,
custom_filter_plots=custom_filter_plots,
custom_filter_parcels=custom_filter_parcels,
parcel_fmi=parcel_fmi,
parcel_number=parcel_number,
previous_parcel_number=previous_parcel_number)
return query
def get_igac_legal_query(names, schema, plot_t_ids, parcel_fmi, parcel_number,
previous_parcel_number):
custom_filter_plots = get_custom_filter_plots(names, schema, plot_t_ids)
custom_filter_parcels = get_custom_filter_parcels(names, schema,
plot_t_ids)
query = """
WITH
_unidad_area_terreno AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename = '{LC_PLOT_T}' AND columnname = '{LC_PLOT_T_PLOT_AREA_F}' LIMIT 1
),
_terrenos_seleccionados AS (
{custom_filter_plots}
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_predios_seleccionados AS (
{custom_filter_parcels}
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_derechos_seleccionados AS (
SELECT DISTINCT {LC_RIGHT_T}.{T_ID_F} FROM {schema}.{LC_RIGHT_T} WHERE {LC_RIGHT_T}.{COL_BAUNIT_RRR_T_UNIT_F} IN (SELECT * FROM _predios_seleccionados)
),
_derecho_interesados AS (
SELECT DISTINCT {LC_RIGHT_T}.{COL_RRR_PARTY_T_LC_PARTY_F}, {LC_RIGHT_T}.{T_ID_F} FROM {schema}.{LC_RIGHT_T} WHERE {LC_RIGHT_T}.{T_ID_F} IN (SELECT * FROM _derechos_seleccionados) AND {LC_RIGHT_T}.{COL_RRR_PARTY_T_LC_PARTY_F} IS NOT NULL
),
_derecho_agrupacion_interesados AS (
SELECT DISTINCT {LC_RIGHT_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}, {MEMBERS_T}.{MEMBERS_T_PARTY_F}
FROM {schema}.{LC_RIGHT_T} LEFT JOIN {schema}.{MEMBERS_T} ON {LC_RIGHT_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} = {MEMBERS_T}.{MEMBERS_T_GROUP_PARTY_F}
WHERE {LC_RIGHT_T}.{T_ID_F} IN (SELECT * FROM _derechos_seleccionados) AND {LC_RIGHT_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} IS NOT NULL
),
_restricciones_seleccionadas AS (
SELECT DISTINCT {LC_RESTRICTION_T}.{T_ID_F} FROM {schema}.{LC_RESTRICTION_T} WHERE {LC_RESTRICTION_T}.{COL_BAUNIT_RRR_T_UNIT_F} IN (SELECT * FROM _predios_seleccionados)
),
_restriccion_interesados AS (
SELECT DISTINCT {LC_RESTRICTION_T}.{COL_RRR_PARTY_T_LC_PARTY_F}, {LC_RESTRICTION_T}.{T_ID_F} FROM {schema}.{LC_RESTRICTION_T} WHERE {LC_RESTRICTION_T}.{T_ID_F} IN (SELECT * FROM _restricciones_seleccionadas) AND {LC_RESTRICTION_T}.{COL_RRR_PARTY_T_LC_PARTY_F} IS NOT NULL
),
_restriccion_agrupacion_interesados AS (
SELECT DISTINCT {LC_RESTRICTION_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}, {MEMBERS_T}.{MEMBERS_T_PARTY_F}
FROM {schema}.{LC_RESTRICTION_T} LEFT JOIN {schema}.{MEMBERS_T} ON {LC_RESTRICTION_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} = {MEMBERS_T}.{MEMBERS_T_GROUP_PARTY_F}
WHERE {LC_RESTRICTION_T}.{T_ID_F} IN (SELECT * FROM _restricciones_seleccionadas) AND {LC_RESTRICTION_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} IS NOT NULL
),
_info_contacto_interesados_derecho AS (
SELECT {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_CONTACT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Teléfono 1', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_1_F},
'Teléfono 2', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_2_F},
'Domicilio notificación', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_NOTIFICATION_ADDRESS_F},
'Correo electrónico', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_EMAIL_F})) ORDER BY {LC_PARTY_CONTACT_T}.{T_ID_F})
FILTER(WHERE {LC_PARTY_CONTACT_T}.{T_ID_F} IS NOT NULL) AS _interesado_contacto_
FROM {schema}.{LC_PARTY_CONTACT_T}
WHERE {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F} IN (SELECT _derecho_interesados.{COL_RRR_PARTY_T_LC_PARTY_F} FROM _derecho_interesados)
GROUP BY {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F}
),
_info_interesados_derecho AS (
SELECT _derecho_interesados.{T_ID_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_PARTY_TYPE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_TYPE_F}),
{LC_PARTY_DOCUMENT_TYPE_D}.{DISPLAY_NAME_F}, {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_ID_F},
'Nombre', {LC_PARTY_T}.{COL_BAUNIT_T_NAME_F},
CASE WHEN {LC_PARTY_T}.{LC_PARTY_T_TYPE_F} = 9 THEN 'Tipo interesado jurídico' ELSE 'Género' END,
CASE WHEN {LC_PARTY_T}.{LC_PARTY_T_TYPE_F} = 9 THEN (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_PARTY_TYPE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_TYPE_F}) ELSE (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_GENRE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_GENRE_F}) END,
'{LC_PARTY_CONTACT_T}', COALESCE(_info_contacto_interesados_derecho._interesado_contacto_, '[]')))
ORDER BY {LC_PARTY_T}.{T_ID_F}) FILTER (WHERE {LC_PARTY_T}.{T_ID_F} IS NOT NULL) AS _interesado_
FROM _derecho_interesados LEFT JOIN {schema}.{LC_PARTY_T} ON {LC_PARTY_T}.{T_ID_F} = _derecho_interesados.{COL_RRR_PARTY_T_LC_PARTY_F}
LEFT JOIN {schema}.{LC_PARTY_DOCUMENT_TYPE_D} ON {LC_PARTY_DOCUMENT_TYPE_D}.{T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_TYPE_F}
LEFT JOIN _info_contacto_interesados_derecho ON _info_contacto_interesados_derecho.{LC_PARTY_CONTACT_T_LC_PARTY_F} = {LC_PARTY_T}.{T_ID_F}
GROUP BY _derecho_interesados.{T_ID_F}
),
_info_contacto_interesado_agrupacion_interesados_derecho AS (
SELECT {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_CONTACT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Teléfono 1', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_1_F},
'Teléfono 2', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_2_F},
'Domicilio notificación', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_NOTIFICATION_ADDRESS_F},
'Correo electrónico', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_EMAIL_F})) ORDER BY {LC_PARTY_CONTACT_T}.{T_ID_F})
FILTER(WHERE {LC_PARTY_CONTACT_T}.{T_ID_F} IS NOT NULL) AS _interesado_contacto_
FROM {schema}.{LC_PARTY_CONTACT_T} LEFT JOIN _derecho_interesados ON _derecho_interesados.{COL_RRR_PARTY_T_LC_PARTY_F} = {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F}
WHERE {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F} IN (SELECT DISTINCT _derecho_agrupacion_interesados.{MEMBERS_T_PARTY_F} FROM _derecho_agrupacion_interesados)
GROUP BY {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F}
),
_info_interesados_agrupacion_interesados_derecho AS (
SELECT _derecho_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_PARTY_TYPE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_TYPE_F}),
{LC_PARTY_DOCUMENT_TYPE_D}.{DISPLAY_NAME_F}, {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_ID_F},
'Nombre', {LC_PARTY_T}.{COL_BAUNIT_T_NAME_F},
'Género', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_GENRE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_GENRE_F}),
'{LC_PARTY_CONTACT_T}', COALESCE(_info_contacto_interesado_agrupacion_interesados_derecho._interesado_contacto_, '[]'),
'{FRACTION_S}', ROUND(({FRACTION_S}.{FRACTION_S_NUMERATOR_F}::numeric/{FRACTION_S}.{FRACTION_S_DENOMINATOR_F}::numeric)*100,2) ))
ORDER BY {LC_PARTY_T}.{T_ID_F}) FILTER (WHERE {LC_PARTY_T}.{T_ID_F} IS NOT NULL) AS _interesado_
FROM _derecho_agrupacion_interesados LEFT JOIN {schema}.{LC_PARTY_T} ON {LC_PARTY_T}.{T_ID_F} = _derecho_agrupacion_interesados.{MEMBERS_T_PARTY_F}
LEFT JOIN {schema}.{LC_PARTY_DOCUMENT_TYPE_D} ON {LC_PARTY_DOCUMENT_TYPE_D}.{T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_TYPE_F}
LEFT JOIN _info_contacto_interesado_agrupacion_interesados_derecho ON _info_contacto_interesado_agrupacion_interesados_derecho.{LC_PARTY_CONTACT_T_LC_PARTY_F} = {LC_PARTY_T}.{T_ID_F}
LEFT JOIN {schema}.{MEMBERS_T} ON ({MEMBERS_T}.{MEMBERS_T_GROUP_PARTY_F}::text || {MEMBERS_T}.{MEMBERS_T_PARTY_F}::text) = (_derecho_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}::text|| {LC_PARTY_T}.{T_ID_F}::text)
LEFT JOIN {schema}.{FRACTION_S} ON {MEMBERS_T}.{T_ID_F} = {FRACTION_S}.{FRACTION_S_MEMBER_F}
GROUP BY _derecho_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}
),
_info_agrupacion_interesados AS (
SELECT {LC_RIGHT_T}.{T_ID_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_GROUP_PARTY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de agrupación de interesados', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_GROUP_PARTY_TYPE_D} WHERE {T_ID_F} = {LC_GROUP_PARTY_T}.{COL_GROUP_PARTY_T_TYPE_F}),
'Nombre', {LC_GROUP_PARTY_T}.{COL_BAUNIT_T_NAME_F},
'{LC_PARTY_T}', COALESCE(_info_interesados_agrupacion_interesados_derecho._interesado_, '[]')))
ORDER BY {LC_GROUP_PARTY_T}.{T_ID_F}) FILTER (WHERE {LC_GROUP_PARTY_T}.{T_ID_F} IS NOT NULL) AS _agrupacioninteresados_
FROM {schema}.{LC_GROUP_PARTY_T} LEFT JOIN {schema}.{LC_RIGHT_T} ON {LC_GROUP_PARTY_T}.{T_ID_F} = {LC_RIGHT_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}
LEFT JOIN _info_interesados_agrupacion_interesados_derecho ON _info_interesados_agrupacion_interesados_derecho.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} = {LC_GROUP_PARTY_T}.{T_ID_F}
WHERE {LC_GROUP_PARTY_T}.{T_ID_F} IN (SELECT DISTINCT _derecho_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} FROM _derecho_agrupacion_interesados)
AND {LC_RIGHT_T}.{T_ID_F} IN (SELECT _derechos_seleccionados.{T_ID_F} FROM _derechos_seleccionados)
GROUP BY {LC_RIGHT_T}.{T_ID_F}
),
_info_fuentes_administrativas_derecho AS (
SELECT {LC_RIGHT_T}.{T_ID_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de fuente administrativa', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_ADMINISTRATIVE_SOURCE_TYPE_D} WHERE {T_ID_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{LC_ADMINISTRATIVE_SOURCE_T_TYPE_F}),
'Ente emisor', {LC_ADMINISTRATIVE_SOURCE_T}.{LC_ADMINISTRATIVE_SOURCE_T_EMITTING_ENTITY_F},
'Estado disponibilidad', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_AVAILABILITY_TYPE_D} WHERE {T_ID_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{COL_SOURCE_T_AVAILABILITY_STATUS_F}),
'Archivo fuente', {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_DATA_F}))
ORDER BY {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F}) FILTER (WHERE {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F} IS NOT NULL) AS _fuenteadministrativa_
FROM {schema}.{LC_RIGHT_T}
LEFT JOIN {schema}.{COL_RRR_SOURCE_T} ON {LC_RIGHT_T}.{T_ID_F} = {COL_RRR_SOURCE_T}.{COL_RRR_SOURCE_T_LC_RIGHT_F}
LEFT JOIN {schema}.{LC_ADMINISTRATIVE_SOURCE_T} ON {COL_RRR_SOURCE_T}.{COL_RRR_SOURCE_T_SOURCE_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F}
LEFT JOIN {schema}.{EXT_ARCHIVE_S} ON {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_LC_ADMINISTRATIVE_SOURCE_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F}
WHERE {LC_RIGHT_T}.{T_ID_F} IN (SELECT _derechos_seleccionados.{T_ID_F} FROM _derechos_seleccionados)
GROUP BY {LC_RIGHT_T}.{T_ID_F}
),
_info_derecho AS (
SELECT {LC_RIGHT_T}.{COL_BAUNIT_RRR_T_UNIT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_RIGHT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de derecho', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_RIGHT_TYPE_D} WHERE {T_ID_F} = {LC_RIGHT_T}.{LC_RIGHT_T_TYPE_F}),
'Descripción', {LC_RIGHT_T}.{COL_RRR_T_DESCRIPTION_F},
'{LC_ADMINISTRATIVE_SOURCE_T}', COALESCE(_info_fuentes_administrativas_derecho._fuenteadministrativa_, '[]'),
'{LC_PARTY_T}', COALESCE(_info_interesados_derecho._interesado_, '[]'),
'{LC_GROUP_PARTY_T}', COALESCE(_info_agrupacion_interesados._agrupacioninteresados_, '[]')))
ORDER BY {LC_RIGHT_T}.{T_ID_F}) FILTER (WHERE {LC_RIGHT_T}.{T_ID_F} IS NOT NULL) AS _derecho_
FROM {schema}.{LC_RIGHT_T} LEFT JOIN _info_fuentes_administrativas_derecho ON {LC_RIGHT_T}.{T_ID_F} = _info_fuentes_administrativas_derecho.{T_ID_F}
LEFT JOIN _info_interesados_derecho ON {LC_RIGHT_T}.{T_ID_F} = _info_interesados_derecho.{T_ID_F}
LEFT JOIN _info_agrupacion_interesados ON {LC_RIGHT_T}.{T_ID_F} = _info_agrupacion_interesados.{T_ID_F}
WHERE {LC_RIGHT_T}.{T_ID_F} IN (SELECT * FROM _derechos_seleccionados)
GROUP BY {LC_RIGHT_T}.{COL_BAUNIT_RRR_T_UNIT_F}
),
_info_contacto_interesados_restriccion AS (
SELECT {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_CONTACT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Teléfono 1', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_1_F},
'Teléfono 2', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_2_F},
'Domicilio notificación', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_NOTIFICATION_ADDRESS_F},
'Correo electrónico', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_EMAIL_F})) ORDER BY {LC_PARTY_CONTACT_T}.{T_ID_F})
FILTER(WHERE {LC_PARTY_CONTACT_T}.{T_ID_F} IS NOT NULL) AS _interesado_contacto_
FROM {schema}.{LC_PARTY_CONTACT_T}
WHERE {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F} IN (SELECT _restriccion_interesados.{COL_RRR_PARTY_T_LC_PARTY_F} FROM _restriccion_interesados)
GROUP BY {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F}
),
_info_interesados_restriccion AS (
SELECT _restriccion_interesados.{T_ID_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo', {LC_PARTY_T}.{LC_PARTY_T_TYPE_F},
{LC_PARTY_DOCUMENT_TYPE_D}.{DISPLAY_NAME_F}, {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_ID_F},
'Nombre', {LC_PARTY_T}.{COL_BAUNIT_T_NAME_F},
CASE WHEN {LC_PARTY_T}.{LC_PARTY_T_TYPE_F} = (SELECT {T_ID_F} FROM {schema}.{LC_PARTY_TYPE_D} WHERE {ILICODE_F} LIKE '{LC_PARTY_TYPE_D_ILICODE_F_NOT_NATURAL_PARTY_V}') THEN 'Tipo interesado jurídico' ELSE 'Género' END,
CASE WHEN {LC_PARTY_T}.{LC_PARTY_T_TYPE_F} = (SELECT {T_ID_F} FROM {schema}.{LC_PARTY_TYPE_D} WHERE {ILICODE_F} LIKE '{LC_PARTY_TYPE_D_ILICODE_F_NOT_NATURAL_PARTY_V}') THEN (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_PARTY_TYPE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_TYPE_F}) ELSE (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_GENRE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_GENRE_F}) END,
'{LC_PARTY_CONTACT_T}', COALESCE(_info_contacto_interesados_restriccion._interesado_contacto_, '[]')))
ORDER BY {LC_PARTY_T}.{T_ID_F}) FILTER (WHERE {LC_PARTY_T}.{T_ID_F} IS NOT NULL) AS _interesado_
FROM _restriccion_interesados LEFT JOIN {schema}.{LC_PARTY_T} ON {LC_PARTY_T}.{T_ID_F} = _restriccion_interesados.{COL_RRR_PARTY_T_LC_PARTY_F}
LEFT JOIN {schema}.{LC_PARTY_DOCUMENT_TYPE_D} ON {LC_PARTY_DOCUMENT_TYPE_D}.{T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_TYPE_F}
LEFT JOIN _info_contacto_interesados_restriccion ON _info_contacto_interesados_restriccion.{LC_PARTY_CONTACT_T_LC_PARTY_F} = {LC_PARTY_T}.{T_ID_F}
GROUP BY _restriccion_interesados.{T_ID_F}
),
_info_contacto_interesado_agrupacion_interesados_restriccion AS (
SELECT {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_CONTACT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Teléfono 1', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_1_F},
'Teléfono 2', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_TELEPHONE_NUMBER_2_F},
'Domicilio notificación', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_NOTIFICATION_ADDRESS_F},
'Correo electrónico', {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_EMAIL_F})) ORDER BY {LC_PARTY_CONTACT_T}.{T_ID_F})
FILTER(WHERE {LC_PARTY_CONTACT_T}.{T_ID_F} IS NOT NULL) AS _interesado_contacto_
FROM {schema}.{LC_PARTY_CONTACT_T} LEFT JOIN _restriccion_interesados ON _restriccion_interesados.{COL_RRR_PARTY_T_LC_PARTY_F} = {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F}
WHERE {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F} IN (SELECT DISTINCT _restriccion_agrupacion_interesados.{MEMBERS_T_PARTY_F} FROM _restriccion_agrupacion_interesados)
GROUP BY {LC_PARTY_CONTACT_T}.{LC_PARTY_CONTACT_T_LC_PARTY_F}
),
_info_interesados_agrupacion_interesados_restriccion AS (
SELECT _restriccion_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_PARTY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_PARTY_TYPE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_TYPE_F}),
{LC_PARTY_DOCUMENT_TYPE_D}.{DISPLAY_NAME_F}, {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_ID_F},
'Nombre', {LC_PARTY_T}.{COL_BAUNIT_T_NAME_F},
'Género', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_GENRE_D} WHERE {T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_GENRE_F}),
'{LC_PARTY_CONTACT_T}', COALESCE(_info_contacto_interesado_agrupacion_interesados_restriccion._interesado_contacto_, '[]'),
'{FRACTION_S}', ROUND(({FRACTION_S}.{FRACTION_S_NUMERATOR_F}::numeric/{FRACTION_S}.{FRACTION_S_DENOMINATOR_F}::numeric)*100,2) ))
ORDER BY {LC_PARTY_T}.{T_ID_F}) FILTER (WHERE {LC_PARTY_T}.{T_ID_F} IS NOT NULL) AS _interesado_
FROM _restriccion_agrupacion_interesados LEFT JOIN {schema}.{LC_PARTY_T} ON {LC_PARTY_T}.{T_ID_F} = _restriccion_agrupacion_interesados.{MEMBERS_T_PARTY_F}
LEFT JOIN {schema}.{LC_PARTY_DOCUMENT_TYPE_D} ON {LC_PARTY_DOCUMENT_TYPE_D}.{T_ID_F} = {LC_PARTY_T}.{LC_PARTY_T_DOCUMENT_TYPE_F}
LEFT JOIN _info_contacto_interesado_agrupacion_interesados_restriccion ON _info_contacto_interesado_agrupacion_interesados_restriccion.{LC_PARTY_CONTACT_T_LC_PARTY_F} = {LC_PARTY_T}.{T_ID_F}
LEFT JOIN {schema}.{MEMBERS_T} ON ({MEMBERS_T}.{MEMBERS_T_GROUP_PARTY_F}::text || {MEMBERS_T}.{MEMBERS_T_PARTY_F}::text) = (_restriccion_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}::text|| {LC_PARTY_T}.{T_ID_F}::text)
LEFT JOIN {schema}.{FRACTION_S} ON {MEMBERS_T}.{T_ID_F} = {FRACTION_S}.{FRACTION_S_MEMBER_F}
GROUP BY _restriccion_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}
),
_info_agrupacion_interesados_restriccion AS (
SELECT {LC_RESTRICTION_T}.{T_ID_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_GROUP_PARTY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de agrupación de interesados', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_GROUP_PARTY_TYPE_D} WHERE {T_ID_F} = {LC_GROUP_PARTY_T}.{COL_GROUP_PARTY_T_TYPE_F}),
'Nombre', {LC_GROUP_PARTY_T}.{COL_BAUNIT_T_NAME_F},
'{LC_PARTY_T}', COALESCE(_info_interesados_agrupacion_interesados_restriccion._interesado_, '[]')))
ORDER BY {LC_GROUP_PARTY_T}.{T_ID_F}) FILTER (WHERE {LC_GROUP_PARTY_T}.{T_ID_F} IS NOT NULL) AS _agrupacioninteresados_
FROM {schema}.{LC_GROUP_PARTY_T} LEFT JOIN {schema}.{LC_RESTRICTION_T} ON {LC_GROUP_PARTY_T}.{T_ID_F} = {LC_RESTRICTION_T}.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F}
LEFT JOIN _info_interesados_agrupacion_interesados_restriccion ON _info_interesados_agrupacion_interesados_restriccion.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} = {LC_GROUP_PARTY_T}.{T_ID_F}
WHERE {LC_GROUP_PARTY_T}.{T_ID_F} IN (SELECT DISTINCT _restriccion_agrupacion_interesados.{COL_RRR_PARTY_T_LC_GROUP_PARTY_F} FROM _restriccion_agrupacion_interesados)
AND {LC_RESTRICTION_T}.{T_ID_F} IN (SELECT _restricciones_seleccionadas.{T_ID_F} FROM _restricciones_seleccionadas)
GROUP BY {LC_RESTRICTION_T}.{T_ID_F}
),
_info_fuentes_administrativas_restriccion AS (
SELECT {LC_RESTRICTION_T}.{T_ID_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de fuente administrativa', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_ADMINISTRATIVE_SOURCE_TYPE_D} WHERE {T_ID_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{LC_ADMINISTRATIVE_SOURCE_T_TYPE_F}),
'Ente emisor', {LC_ADMINISTRATIVE_SOURCE_T}.{LC_ADMINISTRATIVE_SOURCE_T_EMITTING_ENTITY_F},
'Estado disponibilidad', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_AVAILABILITY_TYPE_D} WHERE {T_ID_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{COL_SOURCE_T_AVAILABILITY_STATUS_F}),
'Archivo fuente', {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_DATA_F}))
ORDER BY {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F}) FILTER (WHERE {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F} IS NOT NULL) AS _fuenteadministrativa_
FROM {schema}.{LC_RESTRICTION_T}
LEFT JOIN {schema}.{COL_RRR_SOURCE_T} ON {LC_RESTRICTION_T}.{T_ID_F} ={COL_RRR_SOURCE_T}.{COL_RRR_SOURCE_T_LC_RESTRICTION_F}
LEFT JOIN {schema}.{LC_ADMINISTRATIVE_SOURCE_T} ON {COL_RRR_SOURCE_T}.{COL_RRR_SOURCE_T_SOURCE_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F}
LEFT JOIN {schema}.{EXT_ARCHIVE_S} ON {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_LC_ADMINISTRATIVE_SOURCE_F} = {LC_ADMINISTRATIVE_SOURCE_T}.{T_ID_F}
WHERE {LC_RESTRICTION_T}.{T_ID_F} IN (SELECT _restricciones_seleccionadas.{T_ID_F} FROM _restricciones_seleccionadas)
GROUP BY {LC_RESTRICTION_T}.{T_ID_F}
),
_info_restriccion AS (
SELECT {LC_RESTRICTION_T}.{COL_BAUNIT_RRR_T_UNIT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_RESTRICTION_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de restricción', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_RESTRICTION_TYPE_D} WHERE {T_ID_F} = {LC_RESTRICTION_T}.{LC_RESTRICTION_T_TYPE_F}),
'Descripción', {LC_RESTRICTION_T}.{COL_RRR_T_DESCRIPTION_F},
'{LC_ADMINISTRATIVE_SOURCE_T}', COALESCE(_info_fuentes_administrativas_restriccion._fuenteadministrativa_, '[]'),
'{LC_PARTY_T}', COALESCE(_info_interesados_restriccion._interesado_, '[]'),
'{LC_GROUP_PARTY_T}', COALESCE(_info_agrupacion_interesados_restriccion._agrupacioninteresados_, '[]')))
ORDER BY {LC_RESTRICTION_T}.{T_ID_F}) FILTER (WHERE {LC_RESTRICTION_T}.{T_ID_F} IS NOT NULL) AS _restriccion_
FROM {schema}.{LC_RESTRICTION_T} LEFT JOIN _info_fuentes_administrativas_restriccion ON {LC_RESTRICTION_T}.{T_ID_F} = _info_fuentes_administrativas_restriccion.{T_ID_F}
LEFT JOIN _info_interesados_restriccion ON {LC_RESTRICTION_T}.{T_ID_F} = _info_interesados_restriccion.{T_ID_F}
LEFT JOIN _info_agrupacion_interesados_restriccion ON {LC_RESTRICTION_T}.{T_ID_F} = _info_agrupacion_interesados_restriccion.{T_ID_F}
WHERE {LC_RESTRICTION_T}.{T_ID_F} IN (SELECT * FROM _restricciones_seleccionadas)
GROUP BY {LC_RESTRICTION_T}.{COL_BAUNIT_RRR_T_UNIT_F}
),
_info_predio AS (
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_PARCEL_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Nombre', {LC_PARCEL_T}.{COL_BAUNIT_T_NAME_F},
'NUPRE', {LC_PARCEL_T}.{LC_PARCEL_T_NUPRE_F},
'Id operación', {LC_PARCEL_T}.{LC_PARCEL_T_ID_OPERATION_F},
'FMI', ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}),
'Número predial', {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F},
'Número predial anterior', {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F},
'{LC_RIGHT_T}', COALESCE(_info_derecho._derecho_, '[]'),
'{LC_RESTRICTION_T}', COALESCE(_info_restriccion._restriccion_, '[]')
)) ORDER BY {LC_PARCEL_T}.{T_ID_F}) FILTER(WHERE {LC_PARCEL_T}.{T_ID_F} IS NOT NULL) AS _predio_
FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} = {LC_PARCEL_T}.{T_ID_F}
LEFT JOIN _info_derecho ON _info_derecho.{COL_BAUNIT_RRR_T_UNIT_F} = {LC_PARCEL_T}.{T_ID_F}
LEFT JOIN _info_restriccion ON _info_restriccion.{COL_BAUNIT_RRR_T_UNIT_F} = {LC_PARCEL_T}.{T_ID_F}
WHERE {LC_PARCEL_T}.{T_ID_F} IN (SELECT * FROM _predios_seleccionados)
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_F} IS NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_UNIT_F} IS NULL
GROUP BY {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F}
),
_info_terreno AS (
SELECT {LC_PLOT_T}.{T_ID_F},
JSON_BUILD_OBJECT('id', {LC_PLOT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT(CONCAT('Área' , (SELECT * FROM _unidad_area_terreno)), {LC_PLOT_T}.{LC_PLOT_T_PLOT_AREA_F},
'{LC_PARCEL_T}', COALESCE(_info_predio._predio_, '[]')
)) AS _terreno_
FROM {schema}.{LC_PLOT_T} LEFT JOIN _info_predio ON {LC_PLOT_T}.{T_ID_F} = _info_predio.{COL_UE_BAUNIT_T_LC_PLOT_F}
WHERE {LC_PLOT_T}.{T_ID_F} IN (SELECT * FROM _terrenos_seleccionados)
ORDER BY {LC_PLOT_T}.{T_ID_F}
)
SELECT JSON_BUILD_OBJECT('{LC_PLOT_T}', COALESCE(JSON_AGG(_info_terreno._terreno_), '[]')) FROM _info_terreno
"""
query = query.format(
**vars(
names), # Custom keys are searched in Table And Field Names object
schema=schema,
custom_filter_plots=custom_filter_plots,
custom_filter_parcels=custom_filter_parcels,
parcel_fmi=parcel_fmi,
parcel_number=parcel_number,
LC_PARTY_TYPE_D_ILICODE_F_NOT_NATURAL_PARTY_V=LADMNames.
LC_PARTY_TYPE_D_ILICODE_F_NOT_NATURAL_PARTY_V,
previous_parcel_number=previous_parcel_number)
return query
def get_igac_physical_query(names, schema, plot_t_ids, parcel_fmi,
parcel_number, previous_parcel_number):
custom_filter_plots = get_custom_filter_plots(names, schema, plot_t_ids)
custom_filter_parcels = get_custom_filter_parcels(names, schema,
plot_t_ids)
query = """
WITH
_unidad_area_terreno AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename = '{LC_PLOT_T}' AND columnname = '{LC_PLOT_T_PLOT_AREA_F}' LIMIT 1
),
_unidad_area_construida_uc AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename = '{LC_BUILDING_UNIT_T}' AND columnname = '{LC_BUILDING_UNIT_T_BUILT_AREA_F}' LIMIT 1
),
_unidad_area_privada_construida_uc AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename = '{LC_BUILDING_UNIT_T}' AND columnname = '{LC_BUILDING_UNIT_T_BUILT_PRIVATE_AREA_F}' LIMIT 1
),
_unidad_longitud_lindero AS (
SELECT ' [' || setting || ']' FROM {schema}.t_ili2db_column_prop WHERE tablename = '{LC_BOUNDARY_T}' AND columnname = '{LC_BOUNDARY_T_LENGTH_F}' LIMIT 1
),
_terrenos_seleccionados AS (
{custom_filter_plots}
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} FROM {schema}.{LC_PARCEL_T} LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {LC_PARCEL_T}.{T_ID_F} = {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL AND CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_predios_seleccionados AS (
{custom_filter_parcels}
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_fmi}' = 'NULL' THEN 1 = 2 ELSE ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}) = '{parcel_fmi}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F} = '{parcel_number}' END
UNION
SELECT {T_ID_F} FROM {schema}.{LC_PARCEL_T} WHERE CASE WHEN '{previous_parcel_number}' = 'NULL' THEN 1 = 2 ELSE {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F} = '{previous_parcel_number}' END
),
_construcciones_seleccionadas AS (
SELECT {COL_UE_BAUNIT_T_LC_BUILDING_F} FROM {schema}.{COL_UE_BAUNIT_T} WHERE {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} IN (SELECT _predios_seleccionados.{T_ID_F} FROM _predios_seleccionados WHERE _predios_seleccionados.{T_ID_F} IS NOT NULL) AND {COL_UE_BAUNIT_T_LC_BUILDING_F} IS NOT NULL
),
_unidadesconstruccion_seleccionadas AS (
SELECT {LC_BUILDING_UNIT_T}.{T_ID_F} FROM {schema}.{LC_BUILDING_UNIT_T} WHERE {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_F} IN (SELECT {COL_UE_BAUNIT_T_LC_BUILDING_F} FROM _construcciones_seleccionadas)
),
_punto_lindero_externos_seleccionados AS (
SELECT DISTINCT {MORE_BFS_T}.{MORE_BFS_T_LC_PLOT_F}, {LC_BOUNDARY_POINT_T}.{T_ID_F}
FROM {schema}.{LC_BOUNDARY_POINT_T} JOIN {schema}.{POINT_BFS_T} ON {LC_BOUNDARY_POINT_T}.{T_ID_F} = {POINT_BFS_T}.{POINT_BFS_T_LC_BOUNDARY_POINT_F}
JOIN {schema}.{LC_BOUNDARY_T} ON {POINT_BFS_T}.{POINT_BFS_T_LC_BOUNDARY_F} = {LC_BOUNDARY_T}.{T_ID_F}
JOIN {schema}.{MORE_BFS_T} ON {LC_BOUNDARY_T}.{T_ID_F} = {MORE_BFS_T}.{MORE_BFS_T_LC_BOUNDARY_F}
WHERE {MORE_BFS_T}.{MORE_BFS_T_LC_PLOT_F} IN (SELECT * FROM _terrenos_seleccionados)
ORDER BY {MORE_BFS_T}.{MORE_BFS_T_LC_PLOT_F}, {LC_BOUNDARY_POINT_T}.{T_ID_F}
),
_punto_lindero_internos_seleccionados AS (
SELECT DISTINCT {LESS_BFS_T}.{LESS_BFS_T_LC_PLOT_F}, {LC_BOUNDARY_POINT_T}.{T_ID_F}
FROM {schema}.{LC_BOUNDARY_POINT_T} JOIN {schema}.{POINT_BFS_T} ON {LC_BOUNDARY_POINT_T}.{T_ID_F} = {POINT_BFS_T}.{POINT_BFS_T_LC_BOUNDARY_POINT_F}
JOIN {schema}.{LC_BOUNDARY_T} ON {POINT_BFS_T}.{POINT_BFS_T_LC_BOUNDARY_F} = {LC_BOUNDARY_T}.{T_ID_F}
JOIN {schema}.{LESS_BFS_T} ON {LC_BOUNDARY_T}.{T_ID_F} = {LESS_BFS_T}.{LESS_BFS_T_LC_BOUNDARY_F}
WHERE {LESS_BFS_T}.{LESS_BFS_T_LC_PLOT_F} IN (SELECT * FROM _terrenos_seleccionados)
ORDER BY {LESS_BFS_T}.{LESS_BFS_T_LC_PLOT_F}, {LC_BOUNDARY_POINT_T}.{T_ID_F}
),
_uc_fuente_espacial AS (
SELECT {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_BUILDING_UNIT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_SPATIAL_SOURCE_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de fuente espacial', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_SPATIAL_SOURCE_TYPE_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SPATIAL_SOURCE_T_TYPE_F}),
'Estado disponibilidad', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_AVAILABILITY_TYPE_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_AVAILABILITY_STATUS_F}),
'Tipo principal', (SELECT {DISPLAY_NAME_F} FROM {schema}.{CI_CODE_PRESENTATION_FORM_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_MAIN_TYPE_F}),
'Fecha documento', {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_DATE_DOCUMENT_F},
'Archivo fuente', {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_DATA_F}))
ORDER BY {LC_SPATIAL_SOURCE_T}.{T_ID_F}) FILTER(WHERE {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_SOURCE_F} IS NOT NULL) AS _fuenteespacial_
FROM {schema}.{COL_UE_SOURCE_T} LEFT JOIN {schema}.{LC_SPATIAL_SOURCE_T} ON {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_SOURCE_F} = {LC_SPATIAL_SOURCE_T}.{T_ID_F}
LEFT JOIN {schema}.{EXT_ARCHIVE_S} ON {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_LC_SPATIAL_SOURCE_F} = {LC_SPATIAL_SOURCE_T}.{T_ID_F}
WHERE {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_BUILDING_UNIT_F} IN (SELECT * FROM _unidadesconstruccion_seleccionadas)
GROUP BY {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_BUILDING_UNIT_F}
),
_info_uc AS (
SELECT {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_BUILDING_UNIT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Número de pisos', {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_TOTAL_FLOORS_F},
'Uso', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_BUILDING_UNIT_USE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_USE_F}),
'Tipo construcción', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_BUILDING_TYPE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_TYPE_F}),
'Tipo unidad de construcción', (SELECT {DISPLAY_NAME_F} FROM {schema}.{LC_BUILDING_UNIT_TYPE_D} WHERE {T_ID_F} = {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_UNIT_TYPE_F}),
CONCAT('Área privada construida' , (SELECT * FROM _unidad_area_privada_construida_uc)), {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILT_PRIVATE_AREA_F},
CONCAT('Área construida' , (SELECT * FROM _unidad_area_construida_uc)), {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILT_AREA_F},
'{LC_SPATIAL_SOURCE_T}', COALESCE(_uc_fuente_espacial._fuenteespacial_, '[]')
)) ORDER BY {LC_BUILDING_UNIT_T}.{T_ID_F}) FILTER(WHERE {LC_BUILDING_UNIT_T}.{T_ID_F} IS NOT NULL) AS _unidadconstruccion_
FROM {schema}.{LC_BUILDING_UNIT_T} LEFT JOIN _uc_fuente_espacial ON {LC_BUILDING_UNIT_T}.{T_ID_F} = _uc_fuente_espacial.{COL_UE_SOURCE_T_LC_BUILDING_UNIT_F}
WHERE {LC_BUILDING_UNIT_T}.{T_ID_F} IN (SELECT * FROM _unidadesconstruccion_seleccionadas)
GROUP BY {LC_BUILDING_UNIT_T}.{LC_BUILDING_UNIT_T_BUILDING_F}
),
_c_fuente_espacial AS (
SELECT {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_BUILDING_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_SPATIAL_SOURCE_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de fuente espacial', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_SPATIAL_SOURCE_TYPE_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SPATIAL_SOURCE_T_TYPE_F}),
'Estado disponibilidad', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_AVAILABILITY_TYPE_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_AVAILABILITY_STATUS_F}),
'Tipo principal', (SELECT {DISPLAY_NAME_F} FROM {schema}.{CI_CODE_PRESENTATION_FORM_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_MAIN_TYPE_F}),
'Fecha documento', {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_DATE_DOCUMENT_F},
'Archivo fuente', {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_DATA_F}))
ORDER BY {LC_SPATIAL_SOURCE_T}.{T_ID_F}) FILTER(WHERE {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_SOURCE_F} IS NOT NULL) AS _fuenteespacial_
FROM {schema}.{COL_UE_SOURCE_T} LEFT JOIN {schema}.{LC_SPATIAL_SOURCE_T} ON {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_SOURCE_F} = {LC_SPATIAL_SOURCE_T}.{T_ID_F}
LEFT JOIN {schema}.{EXT_ARCHIVE_S} ON {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_LC_SPATIAL_SOURCE_F} = {LC_SPATIAL_SOURCE_T}.{T_ID_F}
WHERE {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_BUILDING_F} IN (SELECT * FROM _construcciones_seleccionadas)
GROUP BY {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_BUILDING_F}
),
_info_construccion AS (
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_BUILDING_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Área construcción', {LC_BUILDING_T}.{LC_BUILDING_T_BUILDING_AREA_F},
'Número de pisos', {LC_BUILDING_T}.{LC_BUILDING_T_NUMBER_OF_FLOORS_F},
'{LC_BUILDING_UNIT_T}', COALESCE(_info_uc._unidadconstruccion_, '[]'),
'{LC_SPATIAL_SOURCE_T}', COALESCE(_c_fuente_espacial._fuenteespacial_, '[]')
)) ORDER BY {LC_BUILDING_T}.{T_ID_F}) FILTER(WHERE {LC_BUILDING_T}.{T_ID_F} IS NOT NULL) AS _construccion_
FROM {schema}.{LC_BUILDING_T} LEFT JOIN _c_fuente_espacial ON {LC_BUILDING_T}.{T_ID_F} = _c_fuente_espacial.{COL_UE_SOURCE_T_LC_BUILDING_F}
LEFT JOIN _info_uc ON {LC_BUILDING_T}.{T_ID_F} = _info_uc.{LC_BUILDING_UNIT_T_BUILDING_F}
LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_F} = {LC_BUILDING_T}.{T_ID_F}
WHERE {LC_BUILDING_T}.{T_ID_F} IN (SELECT * FROM _construcciones_seleccionadas)
GROUP BY {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F}
),
_info_predio AS (
SELECT {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F},
JSON_AGG(JSON_BUILD_OBJECT('id', {LC_PARCEL_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Nombre', {LC_PARCEL_T}.{COL_BAUNIT_T_NAME_F},
'NUPRE', {LC_PARCEL_T}.{LC_PARCEL_T_NUPRE_F},
'Id operación', {LC_PARCEL_T}.{LC_PARCEL_T_ID_OPERATION_F},
'FMI', ({LC_PARCEL_T}.{LC_PARCEL_T_ORIP_CODE_F} || '-'|| {LC_PARCEL_T}.{LC_PARCEL_T_FMI_F}),
'Número predial', {LC_PARCEL_T}.{LC_PARCEL_T_PARCEL_NUMBER_F},
'Número predial anterior', {LC_PARCEL_T}.{LC_PARCEL_T_PREVIOUS_PARCEL_NUMBER_F},
'{LC_BUILDING_T}', COALESCE(_info_construccion._construccion_, '[]')
)) ORDER BY {LC_PARCEL_T}.{T_ID_F}) FILTER(WHERE {LC_PARCEL_T}.{T_ID_F} IS NOT NULL) AS _predio_
FROM {schema}.{LC_PARCEL_T} LEFT JOIN _info_construccion ON {LC_PARCEL_T}.{T_ID_F} = _info_construccion.{COL_UE_BAUNIT_T_PARCEL_F}
LEFT JOIN {schema}.{COL_UE_BAUNIT_T} ON {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_PARCEL_F} = _info_construccion.{COL_UE_BAUNIT_T_PARCEL_F}
WHERE {LC_PARCEL_T}.{T_ID_F} = _info_construccion.{COL_UE_BAUNIT_T_PARCEL_F}
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F} IS NOT NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_F} IS NULL
AND {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_BUILDING_UNIT_F} IS NULL
GROUP BY {COL_UE_BAUNIT_T}.{COL_UE_BAUNIT_T_LC_PLOT_F}
),
_t_fuente_espacial AS (
SELECT {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_PLOT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_SPATIAL_SOURCE_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Tipo de fuente espacial', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_SPATIAL_SOURCE_TYPE_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SPATIAL_SOURCE_T_TYPE_F}),
'Estado disponibilidad', (SELECT {DISPLAY_NAME_F} FROM {schema}.{COL_AVAILABILITY_TYPE_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_AVAILABILITY_STATUS_F}),
'Tipo principal', (SELECT {DISPLAY_NAME_F} FROM {schema}.{CI_CODE_PRESENTATION_FORM_D} WHERE {T_ID_F} = {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_MAIN_TYPE_F}),
'Fecha documento', {LC_SPATIAL_SOURCE_T}.{COL_SOURCE_T_DATE_DOCUMENT_F},
'Archivo fuente', {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_DATA_F}))
ORDER BY {LC_SPATIAL_SOURCE_T}.{T_ID_F}) FILTER(WHERE {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_SOURCE_F} IS NOT NULL) AS _fuenteespacial_
FROM {schema}.{COL_UE_SOURCE_T} LEFT JOIN {schema}.{LC_SPATIAL_SOURCE_T} ON {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_SOURCE_F} = {LC_SPATIAL_SOURCE_T}.{T_ID_F}
LEFT JOIN {schema}.{EXT_ARCHIVE_S} ON {EXT_ARCHIVE_S}.{EXT_ARCHIVE_S_LC_SPATIAL_SOURCE_F} = {LC_SPATIAL_SOURCE_T}.{T_ID_F}
WHERE {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_PLOT_F} IN (SELECT * FROM _terrenos_seleccionados)
GROUP BY {COL_UE_SOURCE_T}.{COL_UE_SOURCE_T_LC_PLOT_F}
),
_info_linderos_externos AS (
SELECT {MORE_BFS_T}.{MORE_BFS_T_LC_PLOT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_BOUNDARY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT(CONCAT('Longitud' , (SELECT * FROM _unidad_longitud_lindero)), {LC_BOUNDARY_T}.{LC_BOUNDARY_T_LENGTH_F}))
ORDER BY {LC_BOUNDARY_T}.{T_ID_F}) FILTER(WHERE {LC_BOUNDARY_T}.{T_ID_F} IS NOT NULL) AS _lindero_
FROM {schema}.{LC_BOUNDARY_T} JOIN {schema}.{MORE_BFS_T} ON {LC_BOUNDARY_T}.{T_ID_F} = {MORE_BFS_T}.{MORE_BFS_T_LC_BOUNDARY_F}
WHERE {MORE_BFS_T}.{MORE_BFS_T_LC_PLOT_F} IN (SELECT * FROM _terrenos_seleccionados)
GROUP BY {MORE_BFS_T}.{MORE_BFS_T_LC_PLOT_F}
),
_info_linderos_internos AS (
SELECT {LESS_BFS_T}.{LESS_BFS_T_LC_PLOT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_BOUNDARY_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT(CONCAT('Longitud' , (SELECT * FROM _unidad_longitud_lindero)), {LC_BOUNDARY_T}.{LC_BOUNDARY_T_LENGTH_F}))
ORDER BY {LC_BOUNDARY_T}.{T_ID_F}) FILTER(WHERE {LC_BOUNDARY_T}.{T_ID_F} IS NOT NULL) AS _lindero_
FROM {schema}.{LC_BOUNDARY_T} JOIN {schema}.{LESS_BFS_T} ON {LC_BOUNDARY_T}.{T_ID_F} = {LESS_BFS_T}.{LESS_BFS_T_LC_BOUNDARY_F}
WHERE {LESS_BFS_T}.{LESS_BFS_T_LC_PLOT_F} IN (SELECT * FROM _terrenos_seleccionados)
GROUP BY {LESS_BFS_T}.{LESS_BFS_T_LC_PLOT_F}
),
_info_punto_lindero_externos AS (
SELECT _punto_lindero_externos_seleccionados.{MORE_BFS_T_LC_PLOT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_BOUNDARY_POINT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Nombre', {LC_BOUNDARY_POINT_T}.{LC_BOUNDARY_POINT_T_ID_F},
'Coordenadas', concat(st_x({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F}),
' ', st_y({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F}),
CASE WHEN st_z({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F}) IS NOT NULL THEN concat(' ', st_z({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F})) END))
) ORDER BY {LC_BOUNDARY_POINT_T}.{T_ID_F}) FILTER(WHERE {LC_BOUNDARY_POINT_T}.{T_ID_F} IS NOT NULL) AS _puntolindero_
FROM {schema}.{LC_BOUNDARY_POINT_T} JOIN _punto_lindero_externos_seleccionados ON {LC_BOUNDARY_POINT_T}.{T_ID_F} = _punto_lindero_externos_seleccionados.{T_ID_F}
WHERE _punto_lindero_externos_seleccionados.{MORE_BFS_T_LC_PLOT_F} IS NOT NULL
GROUP BY _punto_lindero_externos_seleccionados.{MORE_BFS_T_LC_PLOT_F}
),
_info_punto_lindero_internos AS (
SELECT _punto_lindero_internos_seleccionados.{LESS_BFS_T_LC_PLOT_F},
JSON_AGG(
JSON_BUILD_OBJECT('id', {LC_BOUNDARY_POINT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT('Nombre', {LC_BOUNDARY_POINT_T}.{LC_BOUNDARY_POINT_T_ID_F},
'Coordenadas', concat(st_x({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F}),
' ', st_y({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F}),
CASE WHEN st_z({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F}) IS NOT NULL THEN concat(' ', st_z({LC_BOUNDARY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F})) END))
) ORDER BY {LC_BOUNDARY_POINT_T}.{T_ID_F}) FILTER(WHERE {LC_BOUNDARY_POINT_T}.{T_ID_F} IS NOT NULL) AS _puntolindero_
FROM {schema}.{LC_BOUNDARY_POINT_T} JOIN _punto_lindero_internos_seleccionados ON {LC_BOUNDARY_POINT_T}.{T_ID_F} = _punto_lindero_internos_seleccionados.{T_ID_F}
WHERE _punto_lindero_internos_seleccionados.{LESS_BFS_T_LC_PLOT_F} IS NOT NULL
GROUP BY _punto_lindero_internos_seleccionados.{LESS_BFS_T_LC_PLOT_F}
),
_info_puntolevantamiento AS (
SELECT _t_id_terreno_,
JSON_AGG(
JSON_BUILD_OBJECT('id', _puntoslevantamiento_seleccionados._t_id_puntolevantamiento_,
'attributes', JSON_BUILD_OBJECT('Coordenadas', concat(st_x(_puntoslevantamiento_seleccionados._geometria_),
' ', st_y(_puntoslevantamiento_seleccionados._geometria_),
CASE WHEN st_z(_puntoslevantamiento_seleccionados._geometria_) IS NOT NULL THEN concat(' ', st_z(_puntoslevantamiento_seleccionados._geometria_)) END)
))
ORDER BY _puntoslevantamiento_seleccionados._t_id_puntolevantamiento_) FILTER(WHERE _puntoslevantamiento_seleccionados._t_id_puntolevantamiento_ IS NOT NULL) AS _puntolevantamiento_
FROM
(
SELECT {LC_SURVEY_POINT_T}.{T_ID_F} AS _t_id_puntolevantamiento_, {LC_SURVEY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F} AS _geometria_, {LC_PLOT_T}.{T_ID_F} AS _t_id_terreno_
FROM {schema}.{LC_PLOT_T}, {schema}.{LC_SURVEY_POINT_T}
WHERE ST_Intersects({LC_PLOT_T}.{LC_PLOT_T_GEOMETRY_F}, {LC_SURVEY_POINT_T}.{COL_POINT_T_ORIGINAL_LOCATION_F}) AND {LC_PLOT_T}.{T_ID_F} IN (SELECT * FROM _terrenos_seleccionados)
) AS _puntoslevantamiento_seleccionados
GROUP BY _t_id_terreno_
),
_info_terreno AS (
SELECT {LC_PLOT_T}.{T_ID_F},
JSON_BUILD_OBJECT('id', {LC_PLOT_T}.{T_ID_F},
'attributes', JSON_BUILD_OBJECT(CONCAT('Área' , (SELECT * FROM _unidad_area_terreno)), {LC_PLOT_T}.{LC_PLOT_T_PLOT_AREA_F},
'{LC_PARCEL_T}', COALESCE(_info_predio._predio_, '[]'),
'{LC_BOUNDARY_T} externos', COALESCE(_info_linderos_externos._lindero_, '[]'),
'{LC_BOUNDARY_POINT_T} externos', COALESCE(_info_punto_lindero_externos._puntolindero_, '[]'),
'{LC_BOUNDARY_T} internos', COALESCE(_info_linderos_internos._lindero_, '[]'),
'{LC_BOUNDARY_POINT_T} internos', COALESCE(_info_punto_lindero_internos._puntolindero_, '[]'),
'{LC_SURVEY_POINT_T}', COALESCE(_info_puntolevantamiento._puntolevantamiento_, '[]'),
'{LC_SPATIAL_SOURCE_T}', COALESCE(_t_fuente_espacial._fuenteespacial_, '[]')
)) AS _terreno_
FROM {schema}.{LC_PLOT_T} LEFT JOIN _info_predio ON _info_predio.{COL_UE_BAUNIT_T_LC_PLOT_F} = {LC_PLOT_T}.{T_ID_F}
LEFT JOIN _t_fuente_espacial ON {LC_PLOT_T}.{T_ID_F} = _t_fuente_espacial.{COL_UE_SOURCE_T_LC_PLOT_F}
LEFT JOIN _info_linderos_externos ON {LC_PLOT_T}.{T_ID_F} = _info_linderos_externos.{MORE_BFS_T_LC_PLOT_F}
LEFT JOIN _info_linderos_internos ON {LC_PLOT_T}.{T_ID_F} = _info_linderos_internos.{LESS_BFS_T_LC_PLOT_F}
LEFT JOIN _info_punto_lindero_externos ON {LC_PLOT_T}.{T_ID_F} = _info_punto_lindero_externos.{MORE_BFS_T_LC_PLOT_F}
LEFT JOIN _info_punto_lindero_internos ON {LC_PLOT_T}.{T_ID_F} = _info_punto_lindero_internos.{LESS_BFS_T_LC_PLOT_F}
LEFT JOIN _info_puntolevantamiento ON {LC_PLOT_T}.{T_ID_F} = _info_puntolevantamiento._t_id_terreno_
WHERE {LC_PLOT_T}.{T_ID_F} IN (SELECT * FROM _terrenos_seleccionados)
ORDER BY {LC_PLOT_T}.{T_ID_F}
)
SELECT JSON_BUILD_OBJECT('{LC_PLOT_T}', COALESCE(JSON_AGG(_info_terreno._terreno_), '[]')) FROM _info_terreno
"""
query = query.format(
**vars(
names), # Custom keys are searched in Table And Field Names object
schema=schema,
custom_filter_plots=custom_filter_plots,
custom_filter_parcels=custom_filter_parcels,
parcel_fmi=parcel_fmi,
parcel_number=parcel_number,
previous_parcel_number=previous_parcel_number)
return query
