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