FdwService.java
5.17 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
package com.sigem.gis.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class FdwService {
@Autowired
private GeoServerService geoServerService;
@Autowired
@Qualifier("masterJdbcTemplate")
private JdbcTemplate masterJdbcTemplate;
@Autowired
@Qualifier("gisJdbcTemplate")
private JdbcTemplate gisJdbcTemplate;
public void setupFdw(String entidadId) {
String sqlEntidad = "SELECT sigem_site, sigem_dbname, boundno, boundse, latlong, zoom, maxzoom, minzoom FROM public.entidades WHERE entidad = ?";
List<Map<String, Object>> entidades = masterJdbcTemplate.queryForList(sqlEntidad, Integer.parseInt(entidadId));
if (entidades.isEmpty()) throw new RuntimeException("Entidad " + entidadId + " no encontrada.");
Map<String, Object> data = entidades.get(0);
String sigemSite = (String) data.get("sigem_site");
String sigemDbname = (String) data.get("sigem_dbname");
String boundNo = (String) data.get("boundno");
String boundSe = (String) data.get("boundse");
String host = extractParam(sigemSite, "host", "127.0.0.1");
String port = extractParam(sigemSite, "port", "5432");
String user = extractParam(sigemSite, "user", "postgres");
String pass = extractParam(sigemSite, "password", "x25yvaga2017");
String serverName = "srv_mun_" + entidadId;
String schemaName = "fdw_" + entidadId;
try {
gisJdbcTemplate.execute("CREATE EXTENSION IF NOT EXISTS postgres_fdw");
gisJdbcTemplate.execute("DROP SERVER IF EXISTS " + serverName + " CASCADE");
gisJdbcTemplate.execute(String.format("CREATE SERVER %s FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '%s', port '%s', dbname '%s')", serverName, host, port, sigemDbname));
gisJdbcTemplate.execute(String.format("CREATE USER MAPPING FOR sigem_user SERVER %s OPTIONS (user '%s', password '%s')", serverName, user, pass));
gisJdbcTemplate.execute("CREATE SCHEMA IF NOT EXISTS " + schemaName);
gisJdbcTemplate.execute(String.format("IMPORT FOREIGN SCHEMA public LIMIT TO (v_liq_entidad_totalxobjeto, v_liq_entidad_percentiles, usuarios, ventanas_usuario) FROM SERVER %s INTO %s", serverName, schemaName));
// REGLA 23 ACTUALIZADA: Join OBLIGATORIO por CCC = INM_CTACATASTRAL
String tableLotes = "public.e" + entidadId + "_lotes_conccc";
String viewName = "vw_lotes_morosidad_" + entidadId;
String viewWms = "vw_lotes_wms_" + entidadId;
String sqlJoin = String.format("CREATE OR REPLACE VIEW public.%s AS SELECT l.*, m.inm_ficha, m.inm_ctacatastral, m.trb_total_deuda, m.trb_total_pago, m.ultimo_pago FROM %s l LEFT JOIN %s.v_liq_entidad_totalxobjeto m ON l.ccc = m.inm_ctacatastral", viewName, tableLotes, schemaName);
gisJdbcTemplate.execute(sqlJoin);
gisJdbcTemplate.execute(sqlJoin.replace(viewName, viewWms));
try {
geoServerService.publishLayer(viewName, viewName, null, boundNo, boundSe);
enableMvt(viewName);
geoServerService.truncateCache(viewName);
geoServerService.publishLayer(viewWms, viewWms, "morosidad_style_wms", boundNo, boundSe);
geoServerService.truncateCache(viewWms);
} catch (Exception e) { System.err.println("Advertencia GS: " + e.getMessage()); }
} catch (Exception e) { throw new RuntimeException("Error FDW: " + e.getMessage(), e); }
}
private String extractParam(String siteParam, String key, String defaultValue) {
if (siteParam != null && siteParam.contains(key + "=")) {
String[] parts = siteParam.split(key + "=");
if (parts.length > 1) return parts[1].split(" ")[0].trim();
}
return defaultValue;
}
public void enableMvt(String layerName) {
// Hablar con host interno proyecto-geoserver-1
String geoserverUrl = "http://proyecto-geoserver-1:8080/geoserver/gwc/rest/layers/sigem:" + layerName + ".xml";
String auth = "admin:geoserver";
String encodedAuth = java.util.Base64.getEncoder().encodeToString(auth.getBytes());
String xmlBody = "<GeoServerLayer><enabled>true</enabled><mimeFormats><string>application/x-protobuf</string></mimeFormats></GeoServerLayer>";
try {
java.net.URL url = new java.net.URL(geoserverUrl);
java.net.HttpURLConnection conn = (java.net.HttpURLConnection) url.openConnection();
conn.setRequestMethod("POST");
conn.setRequestProperty("Authorization", "Basic " + encodedAuth);
conn.setRequestProperty("Content-Type", "text/xml");
conn.setDoOutput(true);
conn.setConnectTimeout(2000);
try (java.io.OutputStream os = conn.getOutputStream()) { os.write(xmlBody.getBytes()); }
conn.getResponseCode();
} catch (Exception e) { System.err.println("Error MVT: " + e.getMessage()); }
}
}