package com.dacrt.SBIABackend.controler; import java.io.PrintWriter; import java.io.UnsupportedEncodingException; import java.math.BigDecimal; import java.math.BigInteger; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Optional; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query; import javax.persistence.TypedQuery; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.transaction.Transactional; import javax.validation.Valid; import org.json.JSONObject; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.CrossOrigin; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RestController; import com.dacrt.SBIABackend.dto.AppTypeDto; import com.dacrt.SBIABackend.dto.ApplicationDto; import com.dacrt.SBIABackend.dto.ApplicationDto2; import com.dacrt.SBIABackend.dto.ApplicationResponseDto2; import com.dacrt.SBIABackend.dto.CampaignFormat2Dto; import com.dacrt.SBIABackend.dto.ChannelDto; import com.dacrt.SBIABackend.dto.ChannelDto2; import com.dacrt.SBIABackend.dto.DrptestDto; import com.dacrt.SBIABackend.dto.FacilityDto2; import com.dacrt.SBIABackend.dto.InstrumentsDto2; import com.dacrt.SBIABackend.dto.ItemDto; import com.dacrt.SBIABackend.dto.PeriodParResponseDto; import com.dacrt.SBIABackend.dto.RecordCatalogoResponseDto; import com.dacrt.SBIABackend.dto.RisckElementColorsDto; import com.dacrt.SBIABackend.dto.RiskElementMainDto; import com.dacrt.SBIABackend.dto.RiskFactorMainDto; import com.dacrt.SBIABackend.dto.RiskFactorsElementDto; import com.dacrt.SBIABackend.dto.RiskFactorsEntryRecordDto; import com.dacrt.SBIABackend.dto.RiskFactorsEntryRecordDto2; import com.dacrt.SBIABackend.dto.RiskFactorsRecordDto; import com.dacrt.SBIABackend.dto.RiskFollowCloseDto; import com.dacrt.SBIABackend.dto.RiskFollowOpenDto; import com.dacrt.SBIABackend.dto.RiskFollowactionsDto; import com.dacrt.SBIABackend.dto.RiskScalesDto; import com.dacrt.SBIABackend.dto.RiskfollowupsDto; import com.dacrt.SBIABackend.dto.RiskfollowupsRecordsDto; import com.dacrt.SBIABackend.dto.RiskfollowupsResponseDto; import com.dacrt.SBIABackend.dto.RiskfollowupsSearchDto; import com.dacrt.SBIABackend.dto.ScalesDto; import com.dacrt.SBIABackend.dto.SceneryDto; import com.dacrt.SBIABackend.dto.SceneryDto2; import com.dacrt.SBIABackend.dto.ServiceOffersDescDto; import com.dacrt.SBIABackend.dto.SeverityDto; import com.dacrt.SBIABackend.dto.StateDto; import com.dacrt.SBIABackend.dto.StatusDto; import com.dacrt.SBIABackend.dto.StatusDto2; import com.dacrt.SBIABackend.dto.StatusDto5; import com.dacrt.SBIABackend.dto.TypesUnitDto; import com.dacrt.SBIABackend.dto.scalesDtoAll; import com.dacrt.SBIABackend.dto.requestDto.ChannelsRequestDto; import com.dacrt.SBIABackend.dto.requestDto.RiskFactorElementDto; import com.dacrt.SBIABackend.dto.requestDto.RiskFactorRequestDto; import com.dacrt.SBIABackend.dto.responseDto.ApplicationResponseDto; import com.dacrt.SBIABackend.dto.responseDto.ChannelsResponseDto; import com.dacrt.SBIABackend.dto.responseDto.EntryFollowEvalDto; import com.dacrt.SBIABackend.dto.responseDto.RickEntryRespondeDto; import com.dacrt.SBIABackend.dto.responseDto.RiskCategoryResponseDto; import com.dacrt.SBIABackend.dto.responseDto.RiskFactorsResponseDto; import com.dacrt.SBIABackend.repository.ChannelsRepository; import com.dacrt.SBIABackend.repository.FacilitiesRepository; import com.dacrt.SBIABackend.repository.InstrumentriskfactorsRepository; import com.dacrt.SBIABackend.repository.RiskevaluationsRepository; import com.dacrt.SBIABackend.repository.RiskfactorelementsRepository; import com.dacrt.SBIABackend.repository.RiskfactorsRepository; import com.dacrt.SBIABackend.repository.SuppliersRepository; import com.dacrt.SBIABackend.security.dto.AuditRequestDto; import com.dacrt.SBIABackend.security.dto.ParamsDto; import com.dacrt.SBIABackend.security.dto.ParamsResponseDto; import com.dacrt.SBIABackend.security.dto.PrivilegesAllDto; import com.dacrt.SBIABackend.security.dto.PrivilegesDto; import com.dacrt.SBIABackend.security.dto.RespuestaDto; import com.dacrt.SBIABackend.security.dto.RespuestaMsgDto; import com.dacrt.SBIABackend.security.dto.RespuestaValueDto; import com.dacrt.SBIABackend.security.dto.RoleDto; import com.dacrt.SBIABackend.security.dto.UsersListDto; import com.dacrt.SBIABackend.security.entity.Params; import com.dacrt.SBIABackend.security.entity.Roles; import com.dacrt.SBIABackend.entity.Channels; import com.dacrt.SBIABackend.entity.Riskfactorelements; import com.dacrt.SBIABackend.entity.Riskfactors; import com.dacrt.SBIABackend.security.entity.Users; import com.dacrt.SBIABackend.security.repository.AuditRepository; import com.dacrt.SBIABackend.security.repository.ParamsRepository; import com.dacrt.SBIABackend.security.repository.RolesPrivilegesRepository; import com.dacrt.SBIABackend.security.repository.UsersRepository; import com.dacrt.SBIABackend.security.service.MenuService; import com.dacrt.SBIABackend.security.service.ParamsService; import com.dacrt.SBIABackend.security.service.SecurityService; import com.dacrt.SBIABackend.security.service.UsersService; import com.dacrt.SBIABackend.service.ChannelsService; import com.dacrt.SBIABackend.service.RiskfactorelementsService; import com.dacrt.SBIABackend.service.RiskfactorsService; import com.dacrt.SBIABackend.utils.HttpReqRespUtils; @Valid @RestController //@RequestMapping("/units") @CrossOrigin(origins = "*") public class RiskfollowupsController { @Autowired private ParamsRepository paramsRepository; @Autowired private UsersRepository usersRepository; @Autowired private AuditRepository auditRepository; @Autowired private ChannelsRepository channelsRepository; @Autowired private RolesPrivilegesRepository rolesPrivilegesRepository; @Autowired UsersService usersService; @Autowired ParamsService paramsService; @Autowired ChannelsService channelsService; @Autowired SecurityService securityService; @Autowired MenuService menuService; @PersistenceContext private EntityManager entityManager; @Autowired SuppliersRepository suppliersRepository; @Autowired RiskfactorsRepository riskfactorsRepository; @Autowired FacilitiesRepository facilitiesRepository; @PostMapping("/riskfollowups") public ResponseEntity riskfollowups(HttpServletRequest request, @RequestBody RiskfollowupsSearchDto tiposfiltros) throws ParseException { RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; ApplicationResponseDto applicationResponseDto = new ApplicationResponseDto(); ApplicationResponseDto2 applicationResponseDto2 = new ApplicationResponseDto2(); RecordCatalogoResponseDto appFormatResponseDto = new RecordCatalogoResponseDto(); // ParamsDto detalleParams; ApplicationDto detalleApplication; ApplicationDto2 detalleApplication2; StatusDto detalleStatus; PrivilegesAllDto detallePrivilege; FacilityDto2 detallefacility; Long cuantosregistro = (long) 0; CampaignFormat2Dto detalleCompaing2; List listasPrivelege = new ArrayList<>(); String sessionid = request.getHeader("Authorization"); Date fecha = new Date(); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String dataFormattata = formatter.format(fecha); Date fechaDate = formatter.parse(dataFormattata); AuditRequestDto auditDto = new AuditRequestDto(); int idrol; String searchIn = ""; String drptestyesnoparSearch = ""; String apptypeSearch = ""; String contentIn = ""; int serviceofferidIn = 0; int searchStatus = 0; int sceneryid = 0; String from=""; String to=""; int facilityIn = 0; int formatList = 0; String fechaComoCadena; int orderIn = 0; int offsetIn = 0; int numofrecordsIn = 0; Date fecha2 = new Date(); if (sessionid == null) { String var = ""; boolean bloked = false; RespuestaDto respuestaDto = new RespuestaDto(var, bloked); respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Sesión expirada o inválida"); // Error 400 return new ResponseEntity(respuestaDto, HttpStatus.BAD_REQUEST); } else { sessionid = sessionid.substring(7); Optional encontreSessionUsuario = usersRepository.getBySessionid(sessionid); if (encontreSessionUsuario.isPresent()) { Date FechaReg = encontreSessionUsuario.get().getValidthru(); // fechaComoCadena = securityService.consultarSessionActiva(FechaReg,fecha2); fechaComoCadena = securityService.consultarSessionActiva(FechaReg, fecha2, encontreSessionUsuario.get().getId()); if (fechaComoCadena == "") { String var = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var); // respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Sesión expirada o inválida"); return new ResponseEntity(respuestaDto, HttpStatus.UNAUTHORIZED); } // Este proceso permite obtener un listado de los seguimientos. (Priv 440) Roles roles = encontreSessionUsuario.get().getRolid(); idrol = roles.getId(); int rolisvalid = auditRepository.getCantbyRolAndPrivi(idrol, 827); if (rolisvalid == 0) { String var = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var); // respuestaDto.setBlocked(bloked); respuestaDto.setMsg("No tiene los Privilegios"); return new ResponseEntity(respuestaDto, HttpStatus.FORBIDDEN); } /* * if (formatList != 2) { if (rolisvalid == 0) { * * String var = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new * RespuestaMsgDto(var); // respuestaDto.setBlocked(bloked); * respuestaDto.setMsg("No tiene los Privilegios"); return new * ResponseEntity(respuestaDto, HttpStatus.FORBIDDEN); * * } } */ searchIn = tiposfiltros.getFilters().getSearch(); searchStatus = tiposfiltros.getFilters().getStatus(); sceneryid = tiposfiltros.getFilters().getSceneryid(); from = tiposfiltros.getFilters().getDates().getFrom(); to = tiposfiltros.getFilters().getDates().getTo(); orderIn = tiposfiltros.getOrder(); offsetIn = tiposfiltros.getOffset(); contentIn = tiposfiltros.getContent(); numofrecordsIn = tiposfiltros.getNumofrecords(); String Salida = usersService.verificarCaracteresValidosConRegex(searchIn); if (Salida == "NOOK") { String var = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var); // respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Caracteres no permitidos en la busqueda"); return new ResponseEntity(respuestaDto, HttpStatus.BAD_REQUEST); } if (contentIn != null) { menuService.iscontentdiffnull(contentIn, encontreSessionUsuario.get().getId()); } } else { String var = ""; boolean bloked = false; RespuestaDto respuestaDto = new RespuestaDto(var, bloked); respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Sesión expirada o inválida"); // Error 400 return new ResponseEntity(respuestaDto, HttpStatus.UNAUTHORIZED); } } try { String SentenciaBase = ""; String status=""; switch (searchStatus) { case 0: status = " WHERE TRUE= TRUE "; break; case 1: status = " WHERE notassigned>0 "; break; case 2: status = " WHERE assigned>0 "; break; case 3: status = " WHERE inprocess>0 "; break; case 4: status = " WHERE forreview>0 "; break; case 5: status = " WHERE closed>0 "; break; } SentenciaBase = "WITH status_counts AS ( " + " SELECT " + " re.id as evaluation_id, " + " COUNT(rfe.id) FILTER (WHERE rf.id IS NULL) AS notassigned, " + " COUNT(rf.id) FILTER (WHERE rf.id IS NOT NULL AND ra.id IS NULL) AS assigned, " + " " + " COUNT(ra.id) FILTER (WHERE ra.progress < 100) AS inprocess, " + " " + " COUNT(ra.id) FILTER (WHERE ra.progress = 100 AND rf.mitigation IS NULL) AS forreview, " + " " + " COUNT(rf.id) FILTER (WHERE rf.mitigation IS NOT NULL) AS closed, " + " MAX(rf.modifiedat) as ultima_fecha " + " FROM main.riskevaluations re " + " LEFT JOIN main.riskevalfactors rfact ON re.id = rfact.riskevaluationid " + " LEFT JOIN main.riskevalfactorelements rfe ON rfact.id = rfe.riskevalfactorid " + " LEFT JOIN main.riskfollowups rf ON rfe.id = rf.riskevalfactorelementid " + " LEFT JOIN main.riskfollowupactions ra ON rf.id = ra.riskfollowupid " + " GROUP BY re.id " + ") " + "SELECT " + " re.id, " + " re.instrumentid, " + " re.instrumentname, " + " re.scenery, " + " re.scenerydsc, " + " re.relateditem, " + " re.relateditemdsc, " + " re.probability * re.impact AS severidad_inicial, " + " re.remedyprob * re.remedyimpact AS severidad_final, " + " sc.ultima_fecha, " + " COALESCE(sc.notassigned, 0) as notassigned, " + " COALESCE(sc.assigned, 0) as assigned, " + " COALESCE(sc.inprocess, 0) as inprocess, " + " COALESCE(sc.forreview, 0) as forreview, " + " COALESCE(sc.closed, 0) as closed " + "FROM main.riskevaluations re " + "LEFT JOIN status_counts sc ON re.id = sc.evaluation_id " + status; String QueryTotal = ""; String instrumento = "re.instrumentname"; String escenario = "re.scenerydsc"; String item = "re.relateditemdsc"; String lowerinstrumento = "LOWER(re.instrumentname)"; String lowerescenario = "LOWER(re.scenerydsc)"; String loweritem = "LOWER(re.relateditemdsc)"; String LowerSearch = searchIn.toLowerCase(); switch (searchIn) { case "": QueryTotal = SentenciaBase + " AND TRUE = TRUE"; break; default: // viene con el parametro para buscar por el like QueryTotal = SentenciaBase + " AND (" + lowerinstrumento + " LIKE " + "'%" + LowerSearch + "%'" + " OR " + lowerescenario + " LIKE " + "'%" + LowerSearch + "%'" + loweritem + " LIKE " + "'%" + LowerSearch + "%'" + ")"; } switch (sceneryid) { case 0: // Busca por cualquier escenario QueryTotal = QueryTotal; break; default: // viene con el parametro para buscar por el like QueryTotal = QueryTotal + " AND " + " re.scenery " + " = " + sceneryid; break; } LocalDate fechaDesde= LocalDate.now(); LocalDate fechaHasta= LocalDate.now();; if (from!="") fechaDesde = LocalDate.parse(from); if (to!="") fechaHasta = LocalDate.parse(to); String buscarFecha=""; if (from!="" && to=="") buscarFecha=" AND CAST(sc.ultima_fecha AS date) >= "+fechaDesde + "'"; if (from=="" && to!="") buscarFecha=" AND CAST(sc.ultima_fecha AS date) <= '"+fechaHasta + "'"; if (from!="" && to!="") buscarFecha = " AND CAST(sc.ultima_fecha AS date) >= '" + fechaDesde + "' AND CAST(sc.ultima_fecha AS date) <= '" + fechaHasta + "'"; QueryTotal=QueryTotal+buscarFecha; String ordena = ""; if (orderIn == 1 || orderIn == 2 || orderIn == 3 || orderIn == 4 || orderIn == 5 || orderIn == 6 || orderIn == 7 || orderIn == 8 || orderIn == 9 || orderIn == 10 || orderIn == 11 || orderIn == 12 || orderIn == 99) { ordena = " ASC"; } else if (orderIn == -1 || orderIn == -2 || orderIn == -3 || orderIn == -4 || orderIn == -5 || orderIn == -6 || orderIn == -7 || orderIn == -8 || orderIn == -9 || orderIn == -10 || orderIn == -11 || orderIn == -12) { ordena = " DESC"; } else { String var2 = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var2); respuestaDto = new RespuestaMsgDto("Llamada al servicio malformado"); estatus = HttpStatus.BAD_REQUEST; return new ResponseEntity(respuestaDto, estatus); } int absolutoOrden = Math.abs(orderIn); switch (absolutoOrden) { case 1: // 1=escenario+instrumento QueryTotal = QueryTotal + " ORDER BY " + escenario + " " + ordena + ", " +instrumento + " " + ordena; break; case 2:// 2=instrumento QueryTotal = QueryTotal + " ORDER BY " + instrumento + " " + ordena; break; case 3:// 3=item, QueryTotal = QueryTotal + " ORDER BY " + item + " " + ordena; break; case 4:// 4=Sev Inicial QueryTotal = QueryTotal + " ORDER BY " + " severidad_inicial " + ordena; break; case 5:// 5=Sev Final QueryTotal = QueryTotal + " ORDER BY " + " severidad_final " + ordena; break; case 6:// 6=Fecha, QueryTotal = QueryTotal + " ORDER BY " + " sc.ultima_fecha " + ordena; break; case 7:// 7=Atenuador, QueryTotal = QueryTotal + " ORDER BY " + " notassigned " + ordena; break; case 8:// 8=sin asignar, QueryTotal = QueryTotal + " ORDER BY " + " notassigned " + ordena; break; case 9:// 9=asignado, QueryTotal = QueryTotal + " ORDER BY " + " assigned " + ordena; break; case 10:// 10=en proceso, QueryTotal = QueryTotal + " ORDER BY " + " inprocess " + ordena; break; case 11:// 11=por revisar, QueryTotal = QueryTotal + " ORDER BY " + " forreview " + ordena; break; case 12:// 12=cerrados, QueryTotal = QueryTotal + " ORDER BY " + " closed " + ordena; break; case 99: // 99=id (negativos ordenan descendentemente) QueryTotal = QueryTotal + " ORDER BY " + " re.id " + ordena; break; default: String var2 = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var2); respuestaDto = new RespuestaMsgDto("Llamada al servicio malformado"); estatus = HttpStatus.BAD_REQUEST; return new ResponseEntity(respuestaDto, estatus); } RiskfollowupsResponseDto riskfollowupsResponseDto = new RiskfollowupsResponseDto(); RiskfollowupsRecordsDto riskfollowupsRecordsDto = new RiskfollowupsRecordsDto(); List records = new ArrayList(); List sceneries = new ArrayList();; SceneryDto2 sceneryDto2 = new SceneryDto2(); PrivilegesAllDto privileges = new PrivilegesAllDto(); InstrumentsDto2 instrument = new InstrumentsDto2(); SceneryDto scenery= new SceneryDto(); ItemDto item2= new ItemDto(); SeverityDto severity= new SeverityDto(); String date= ""; StatusDto5 status2= new StatusDto5(); Query query; Query query2; List resultadosTypes; List resultadosTypes2; String QueryTypes = "SELECT CAST(elem AS TEXT) " + "FROM main.params pa, " + " jsonb_array_elements(CAST(pa.value AS jsonb)) AS elem " + " WHERE pa.paramname = 'RISK_SCENERIES' "; query2 = entityManager.createNativeQuery(QueryTypes); resultadosTypes = query2.getResultList(); JSONObject jsonObject; String jsonStringTipo = ""; if (resultadosTypes.size() > 0) { for (Object tipos : resultadosTypes) { jsonStringTipo = (String) tipos; // Recupera el JSON como String jsonObject = new JSONObject(jsonStringTipo); // Convierte String a JSONObject // Extrae los valores del JSONObject String dsc = jsonObject.getString("dsc"); String value = jsonObject.getString("value"); sceneryDto2.setDsc(dsc); sceneryDto2.setValue(value); sceneries.add(sceneryDto2); sceneryDto2 = new SceneryDto2(); } } System.out.println(QueryTotal); query = entityManager.createNativeQuery(QueryTotal); cuantosregistro = (long) query.getResultList().size(); query.setFirstResult(offsetIn); query.setMaxResults(numofrecordsIn); List listacompleta = query.getResultList(); for (Object[] fila : listacompleta) { riskfollowupsRecordsDto.setId((int) fila[0]); instrument.setId((int) fila[1]); instrument.setName((String) fila[2]); riskfollowupsRecordsDto.setInstrument(instrument); scenery.setId((int) fila[3]); scenery.setName((String) fila[4]); riskfollowupsRecordsDto.setScenery(scenery); item2.setId((int) fila[5]); item2.setName((String) fila[6]); riskfollowupsRecordsDto.setItem(item2); riskfollowupsRecordsDto.setDate((String) fila[9]); severity.setFinals((BigDecimal) fila[8]); severity.setInitial((BigDecimal) fila[7]); riskfollowupsRecordsDto.setSeverity(severity); status2.setAssigned((BigInteger) fila[11]); status2.setClosed((BigInteger) fila[14]); status2.setForreview((BigInteger) fila[13]); status2.setInprocess((BigInteger) fila[12]); status2.setNotassigned((BigInteger) fila[10]); riskfollowupsRecordsDto.setStatus(status2); records.add(riskfollowupsRecordsDto); instrument = new InstrumentsDto2(); scenery= new SceneryDto(); item2= new ItemDto(); severity= new SeverityDto(); date= ""; status2= new StatusDto5(); } privileges = new PrivilegesAllDto(); boolean tieneView = rolesPrivilegesRepository.existsByRolidAndPrivilegeid(idrol, 827); boolean tieneUpdate = rolesPrivilegesRepository.existsByRolidAndPrivilegeid(idrol, 828); privileges.setView(tieneView); privileges.setUpdate(tieneUpdate); riskfollowupsResponseDto.setNumofrecords(cuantosregistro); riskfollowupsResponseDto.setSessionvalidthru(fechaComoCadena); riskfollowupsResponseDto.setRecords(records); riskfollowupsResponseDto.setSceneries(sceneries); riskfollowupsResponseDto.setPrivileges(privileges); return ResponseEntity.ok(riskfollowupsResponseDto); } catch (Exception e) { // Manejo de excepciones respuesta = new RespuestaDto("Error interno del servidor" + e.getMessage(), false); estatus = HttpStatus.INTERNAL_SERVER_ERROR; } finally { if (entityManager != null && entityManager.isOpen()) { entityManager.close(); } } return new ResponseEntity(respuesta, estatus); } @GetMapping("/riskfollowups/{riskevaluationid}") public ResponseEntity getoneriskfactors(HttpServletRequest request, @PathVariable("riskevaluationid") final Integer riskevaluationid) throws ParseException { int idrol; String searchIn = ""; String contentIn = ""; // String searchModule = ""; int searchStatus = 0; int typeIn=0; String fechaComoCadena; int orderIn = 0; int offsetIn = 0; int numofrecordsIn = 0; Date fecha2 = new Date(); Long cuantosregistro = (long) 0; RickEntryRespondeDto detalleRickEntryRespondeDto=null; RiskFactorsRecordDto detalleRiskFactorsRecordDto; List listasRiskFactorsRecordDto= new ArrayList<>(); List listasTypes = new ArrayList<>(); List listasRecordRisk= new ArrayList<>(); TypesUnitDto detalleTypes; RiskScalesDto detalleTypes2; TypesUnitDto detallePeriodos; List listasPeriodos = new ArrayList<>(); List scalespro = new ArrayList<>(); List scales = new ArrayList<>(); String sessionid = request.getHeader("Authorization"); RiskFactorsEntryRecordDto riskRecordDto = new RiskFactorsEntryRecordDto(); Date fecha = new Date(); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String dataFormattata = formatter.format(fecha); Date fechaDate = formatter.parse(dataFormattata); AuditRequestDto auditDto=new AuditRequestDto(); if (sessionid==null) { String var = ""; boolean bloked = false; RespuestaDto respuestaDto = new RespuestaDto(var, bloked); respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Sesión expirada o inválida"); //Error 400 return new ResponseEntity(respuestaDto, HttpStatus.BAD_REQUEST); } else { sessionid = sessionid.substring(7); Optional encontreSessionUsuario =usersRepository.getBySessionid(sessionid); // String usuarioIN = encontreSessionUsuario.get().getUsr(); // int position = usuarioIN.indexOf('('); if (encontreSessionUsuario.isPresent()) { Date FechaReg = encontreSessionUsuario.get().getValidthru(); //fechaComoCadena = securityService.consultarSessionActiva(FechaReg,fecha2); fechaComoCadena = securityService.consultarSessionActiva(FechaReg,fecha2,encontreSessionUsuario.get().getId()); if (fechaComoCadena=="") { String var = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var); //respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Sesión expirada o inválida"); return new ResponseEntity(respuestaDto, HttpStatus.UNAUTHORIZED); } // Este proceso permite obtener un listado de los proveedores. (Priv 440) Roles roles = encontreSessionUsuario.get().getRolid(); idrol = roles.getId(); int rolisvalid = auditRepository.getCantbyRolAndPrivi(idrol, 827); if (rolisvalid==0) { String var = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var); //respuestaDto.setBlocked(bloked); respuestaDto.setMsg("No tiene los Privilegios"); return new ResponseEntity(respuestaDto, HttpStatus.FORBIDDEN); } if (contentIn !=null) { menuService.iscontentdiffnull(contentIn, encontreSessionUsuario.get().getId()); } } else { String var = ""; boolean bloked = false; RespuestaDto respuestaDto = new RespuestaDto(var, bloked); respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Sesión expirada o inválida"); //Error 400 return new ResponseEntity(respuestaDto, HttpStatus.UNAUTHORIZED); } } try { // ... (Tu código de validación de sesión y privilegios aquí) ... String SentenciaBase; // SentenciaBase = "SELECT new com.dacrt.SBIABackend.dto.ChannelsDtoStatus (u.id,u.name,u.status,CASE WHEN u.status = 1 then 'Activo' ELSE 'Inactivo' END AS estatus) FROM Channels u"; SentenciaBase = " SELECT " + " e.id as ideva, " + " i.id as idisnt, " + " i.name as nombinst, " + " e.scenery, " + " e.scenerydsc, " + " e.relateditem, " + " e.relateditemdsc, " + " e.impact, " + " e.probability, " //+ " -- Formateo de fecha a Texto YYYY-MM-DD" + " TO_CHAR(e.enddate, 'YYYY-MM-DD') AS enddate, " + " re.id AS factor_id ," + " re.riskfactornam, " + " rele.id AS ideleme, " + " rele.riskfactorelementdsc AS nameleme, rele.impact as impac_ele, rele.probability as prob_ele," + " ups.id AS idfollow, " + " ups.name AS namefollow, " + " ups.responsable, " + " TO_CHAR(ups.estfinishdate, 'YYYY-MM-DD') AS estfinishdate, " + " TO_CHAR(ups.createdat, 'YYYY-MM-DD') AS createdat, " + " TO_CHAR(ups.finishdate, 'YYYY-MM-DD') AS finishdate, " //+ " -- Campos Agrupados" + " STRING_AGG(TO_CHAR(a.date, 'YYYY-MM-DD'), ' | ' ORDER BY a.date) AS fechas_acciones, " + " STRING_AGG(a.action, ' | ' ORDER BY a.date) AS resumen_acciones, " + " STRING_AGG(CAST(a.progress AS TEXT), ' | ' ORDER BY a.date) AS progresos_acciones, " //+ " -- Lógica de Estatus" + " CASE " + " WHEN ups.id IS NULL THEN 'Sin asignar' " + " WHEN ups.mitigation IS NOT NULL THEN 'Cerrado' " + " WHEN COUNT(a.id) = 0 THEN 'Asignado' " + " WHEN MAX(a.progress) = 100 AND ups.mitigation IS NULL THEN 'Por revisar' " + " ELSE 'En Proceso' " + " END AS status, " + " ups.mitigation as idmitig, " + " (SELECT elemento ->> 'dsc' " + " FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento " + " WHERE p.paramname = 'RISK_MITIGATION' " + " AND (elemento ->> 'value') = CAST(ups.mitigation AS TEXT) LIMIT 1) as namemitig " + " FROM main.riskevaluations e " + " JOIN main.instruments i ON i.id = e.instrumentid " + " JOIN main.riskevalfactors re ON re.riskevaluationid = e.id " + " JOIN main.riskevalfactorelements rele ON rele.riskevalfactorid = re.id " + " LEFT JOIN main.riskfollowups ups ON ups.riskevalfactorelementid = rele.id " + " LEFT JOIN main.riskfollowupactions a ON a.riskfollowupid = ups.id " + " WHERE e.id = " + riskevaluationid ; String groupby = " GROUP BY " + " e.id,i.id,i.name,e.scenery,e.scenerydsc,e.relateditem,e.relateditemdsc,re.id, re.riskfactornam,rele.impact, rele.probability, " + " e.impact,e.probability,e.enddate," + " rele.id, rele.riskfactorelementdsc, " + " ups.id, ups.name, ups.responsable, ups.estfinishdate,ups.createdat, ups.finishdate, ups.mitigation"; Query query; String queryTotal = SentenciaBase + groupby; // El query que construimos query = entityManager.createNativeQuery(queryTotal); List listacompleta = query.getResultList(); if (listacompleta.isEmpty()) { return ResponseEntity.notFound().build(); } EntryFollowEvalDto entry = new EntryFollowEvalDto(); Map factorsMap = new LinkedHashMap<>(); for (Object[] reg : listacompleta) { // 1. Llenar datos de la Evaluación (Solo la primera vez) if (entry.getId() == null) { entry.setId((Integer) reg[0]); entry.setInstrument(new StateDto((Integer) reg[1], (String) reg[2])); entry.setScenery(new StateDto((Integer) reg[3], (String) reg[4])); entry.setElement(new StateDto((Integer) reg[5], (String) reg[6])); entry.setImpact(reg[7] != null ? new BigDecimal(reg[7].toString()) : BigDecimal.ZERO); entry.setProbability(reg[8] != null ? new BigDecimal(reg[8].toString()) : BigDecimal.ZERO); entry.setDate((String) reg[20]); entry.setCreationdate((String) reg[18]); // createdat de ups o e entry.setColor("#FFFFFF"); } // 2. Agrupar por Factores Integer factorId = (Integer) reg[10]; RiskFactorMainDto factor = factorsMap.computeIfAbsent(factorId, k -> { RiskFactorMainDto f = new RiskFactorMainDto(); f.setId(factorId); f.setName((String) reg[11]); f.setElements(new ArrayList<>()); return f; }); // 3. Crear el Elemento RiskElementMainDto element = new RiskElementMainDto(); element.setId((Integer) reg[12]); element.setName((String) reg[13]); element.setImpact((BigDecimal) reg[14]); element.setProbability((BigDecimal) reg[15]); element.setStatus((String) reg[25]); // El CASE status del query element.setColors(new RisckElementColorsDto("#FFFFFF", "#FFFFFF", "#FFFFFF")); // 4. Llenar Followups (si existen) if (reg[16] != null) { // idfollow RiskfollowupsDto follow = new RiskfollowupsDto(); // Open section follow.setOpen(new RiskFollowOpenDto((String) reg[17], (String) reg[18], (String) reg[19])); // Actions section (Parsear las strings concatenadas con " | ") List actionsList = new ArrayList<>(); if (reg[22] != null) { String[] dates = reg[22].toString().split(" \\| "); String[] descs = reg[23].toString().split(" \\| "); String[] progs = reg[24].toString().split(" \\| "); for (int i = 0; i < dates.length; i++) { actionsList.add(new RiskFollowactionsDto(dates[i], descs[i], Integer.parseInt(progs[i].trim()))); } } follow.setActions(actionsList); // Close section StateDto mitigation = new StateDto(); mitigation.setId(reg[26] != null ? Integer.parseInt(reg[26].toString()) : 0); // Ajustar índice según tu query mitigation.setName(reg[27] != null ? (String) (reg[27]) : ""); // Ajustar índice según tu query // mitigation.setName((String) reg[27]); // Nombre de mitigación follow.setClose(new RiskFollowCloseDto(mitigation, (String) reg[18], (String) reg[21])); element.setFollowups(follow); } factor.getElements().add(element); } entry.setRiskfactors(new ArrayList<>(factorsMap.values())); /////MITIGACION String SetenciaSceneries=""; SetenciaSceneries= "SELECT elemento ->> 'dsc' AS descr,elemento ->> 'value' AS valor " + " FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento WHERE paramname = 'RISK_MITIGATION' ORDER BY valor ASC"; Query queryTypes = entityManager.createNativeQuery(SetenciaSceneries); List listasceneries= queryTypes.getResultList(); for (Object[] types : listasceneries) { detalleTypes = new TypesUnitDto(); detalleTypes.setDsc((String) types[0]); detalleTypes.setValue((String) types[1]); listasTypes.add(detalleTypes); } detalleRickEntryRespondeDto = new RickEntryRespondeDto(); // detalleRickEntryRespondeDto.setEntry(riskRecordDto); // detalleRickEntryRespondeDto.setSceneries(listasTypes); ////////////////TERMINO DE RECORRER EL PARAMETRO DE MITIGACIONES //// AQUI COMIENZO PARA BUSCAR EL PARAMETRO DE ESCALAS//////////// //scalesDtoAll detalleImpact; scalesDtoAll detalleImpact = new scalesDtoAll(); String SetenciascalesImpact = ""; SetenciascalesImpact = " SELECT elemento ->> 'dsc' AS descr," + " elemento ->> 'value' AS valor," + " elemento ->> 'filter' AS lng " + " FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento " + " WHERE paramname = 'RISK_SCALEIMPACT' ORDER BY valor ASC "; Query queryScales = entityManager.createNativeQuery(SetenciascalesImpact); List listascales = queryScales.getResultList(); ScalesDto scal; ScalesDto scal2; for (Object[] scales2 : listascales) { scal = new ScalesDto(); scal.setDsc(scales2[0] != null ? scales2[0].toString() : ""); scal.setValue(scales2[1] != null ? scales2[1].toString() : ""); scal.setLng(scales2[2] != null ? scales2[2].toString() : ""); scales.add(scal); } detalleImpact.setImpact(scales); String SetenciascalesProb = ""; SetenciascalesProb = " SELECT elemento ->> 'dsc' AS descr," + " elemento ->> 'value' AS valor," + " elemento ->> 'filter' AS lng " + " FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento " + " WHERE paramname = 'RISK_SCALEPROB' ORDER BY valor ASC "; Query querySetenciascalesProb = entityManager.createNativeQuery(SetenciascalesProb); List listaProb= querySetenciascalesProb.getResultList(); ScalesDto pro; for (Object[] scales2 : listaProb) { scal2 = new ScalesDto(); scal2.setDsc(scales2[0] != null ? scales2[0].toString() : ""); scal2.setValue(scales2[1] != null ? scales2[1].toString() : ""); scal2.setLng(scales2[2] != null ? scales2[2].toString() : ""); scalespro.add(scal2); } detalleImpact.setProbability(scalespro); // 5. Construir respuesta final Map response = new HashMap<>(); response.put("entry", entry); response.put("mitigation", listasTypes); // La que ya tienes response.put("scales", detalleImpact); // La que ya tienes return ResponseEntity.ok(response); } catch (Exception e) { return new ResponseEntity<>(new RespuestaDto("Error: " + e.getMessage(), false), HttpStatus.INTERNAL_SERVER_ERROR); } finally { if (entityManager != null && entityManager.isOpen()) { entityManager.close(); } } } }