GisController.java
4.38 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
99
100
101
102
103
104
package com.sigem.gis.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/api/gis")
public class GisController {
@Autowired
@Qualifier("gisJdbcTemplate")
private JdbcTemplate gisJdbcTemplate;
@Autowired
@Qualifier("masterJdbcTemplate")
private JdbcTemplate masterJdbcTemplate;
@GetMapping("/morosidad/{entidad}/{ccc}")
public ResponseEntity<?> getMorosidad(@PathVariable String entidad, @PathVariable String ccc) {
try {
// Consulta a la vista unificada vw_lotes_morosidad_XXX
String viewName = "public.vw_lotes_morosidad_" + entidad;
String sql = "SELECT ccc, inm_ficha, inm_ctacatastral, trb_total_deuda, trb_total_pago, ultimo_pago " +
"FROM " + viewName + " WHERE ccc = ? AND entidad = ? LIMIT 1";
List<Map<String, Object>> results = gisJdbcTemplate.queryForList(sql, ccc, entidad);
if (results.isEmpty()) {
// Si no hay datos en la vista (quizás lote sin deuda), buscamos solo datos de lote
return ResponseEntity.ok(Map.of("ccc", ccc, "trb_total_deuda", 0, "status", "Sin Deuda"));
}
return ResponseEntity.ok(results.get(0));
} catch (Exception e) {
return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
}
}
@GetMapping("/entidad/{id}/percentiles")
public ResponseEntity<?> getPercentiles(@PathVariable String id) {
try {
String sql = "SELECT * FROM public.vw_percentiles_morosidad_" + id + " WHERE entidad = ? LIMIT 1";
List<Map<String, Object>> results = gisJdbcTemplate.queryForList(sql, id);
if (results.isEmpty()) return ResponseEntity.notFound().build();
return ResponseEntity.ok(results.get(0));
} catch (Exception e) {
return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
}
}
@GetMapping("/entidad/{id}/resumen")
public ResponseEntity<?> getResumen(@PathVariable String id) {
try {
String viewName = "public.vw_lotes_morosidad_" + id;
String sql = "SELECT " +
" COUNT(*) as total_lotes, " +
" COUNT(CASE WHEN trb_total_deuda > 0 THEN 1 END) as lotes_con_deuda " +
"FROM " + viewName + " WHERE entidad = ?";
return ResponseEntity.ok(gisJdbcTemplate.queryForList(sql, id).get(0));
} catch (Exception e) {
return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
}
}
@GetMapping("/entidad/{id}")
public ResponseEntity<?> getEntidadData(@PathVariable String id) {
try {
String sql = "SELECT * FROM public.entidades WHERE activo = TRUE AND entidad = ?";
// Parse int as the FDW might require integer for 'entidad' column
int entidadId = Integer.parseInt(id);
List<Map<String, Object>> results = masterJdbcTemplate.queryForList(sql, entidadId);
if (results.isEmpty()) {
return ResponseEntity.notFound().build();
}
return ResponseEntity.ok(results.get(0));
} catch (NumberFormatException e) {
return ResponseEntity.badRequest().body(Map.of("error", "ID de entidad inválido"));
} catch (Exception e) {
return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
}
}
@GetMapping("/entidad/{id}/estadisticas")
public ResponseEntity<?> getEstadisticas(@PathVariable String id) {
try {
String schemaName = "fdw_" + id;
String sql = "SELECT descripcion, valor FROM " + schemaName + ".estadisticas_datos WHERE entidad = ?";
List<Map<String, Object>> results = gisJdbcTemplate.queryForList(sql, id);
return ResponseEntity.ok(results);
} catch (Exception e) {
e.printStackTrace();
return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
}
}
}