package com.dacrt.SBIABackend.controler; import java.io.PrintWriter; import java.math.BigDecimal; import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Locale; 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 org.apache.commons.csv.CSVFormat; import org.apache.commons.csv.CSVPrinter; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.http.HttpHeaders; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.CrossOrigin; 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.BcpstrategiesDto; import com.dacrt.SBIABackend.dto.CampaignFormat2Dto; import com.dacrt.SBIABackend.dto.ChannelsAmountDto; import com.dacrt.SBIABackend.dto.ChannelsDtoStatus; import com.dacrt.SBIABackend.dto.ChannelsRankingApplicationsDto; import com.dacrt.SBIABackend.dto.ChannelsRankingProcessesDto; import com.dacrt.SBIABackend.dto.ChannelsRankingRecordDto; import com.dacrt.SBIABackend.dto.ChannelsRankingRtoDto; import com.dacrt.SBIABackend.dto.ChannelsRankingValFormatDto; import com.dacrt.SBIABackend.dto.ChannelsRecordDto; import com.dacrt.SBIABackend.dto.ContentSection1Dto; import com.dacrt.SBIABackend.dto.ContinuityBodySectionDto; import com.dacrt.SBIABackend.dto.ContinuityHeaderDto; import com.dacrt.SBIABackend.dto.ContinuitySection1Dto; import com.dacrt.SBIABackend.dto.ContinuitySection2Dto; import com.dacrt.SBIABackend.dto.ContinuitySection3Dto; import com.dacrt.SBIABackend.dto.DahboardcampaignContentDto; import com.dacrt.SBIABackend.dto.DahboardcampaignHeadersDto; import com.dacrt.SBIABackend.dto.DahboardcampaignSection1Dto; import com.dacrt.SBIABackend.dto.DrpstrategiesDto; import com.dacrt.SBIABackend.dto.FacilitiesListDto; import com.dacrt.SBIABackend.dto.IddscDto; import com.dacrt.SBIABackend.dto.ImpactBodyDto; import com.dacrt.SBIABackend.dto.ImpactContentResponseDto; import com.dacrt.SBIABackend.dto.ImpactContentSection2Dto; import com.dacrt.SBIABackend.dto.ImpactHeaderDto; import com.dacrt.SBIABackend.dto.ImpactReportSection1Dto; import com.dacrt.SBIABackend.dto.ImpactReportSection3Dto; import com.dacrt.SBIABackend.dto.ImpactReportSection4Dto; import com.dacrt.SBIABackend.dto.ImpactReportSection5Dto; import com.dacrt.SBIABackend.dto.ImpactReportSection6Dto; import com.dacrt.SBIABackend.dto.InventoryDto; import com.dacrt.SBIABackend.dto.LabelItemDtoReport; import com.dacrt.SBIABackend.dto.LabelItemDtoReport2; import com.dacrt.SBIABackend.dto.LabelValueDtoReport; import com.dacrt.SBIABackend.dto.LabelValueDtoReport2; import com.dacrt.SBIABackend.dto.LabelValueIntegerDto; import com.dacrt.SBIABackend.dto.OperationlineSinStatusDto; import com.dacrt.SBIABackend.dto.RecordCatalogoResponseDto; import com.dacrt.SBIABackend.dto.RecuperationgapsDto; import com.dacrt.SBIABackend.dto.ReportBIADto; import com.dacrt.SBIABackend.dto.ReportDateDto; import com.dacrt.SBIABackend.dto.ResumenBiaResponseDto; import com.dacrt.SBIABackend.dto.ResumenBiaSection1; import com.dacrt.SBIABackend.dto.ResumenBiaSection2; import com.dacrt.SBIABackend.dto.ResumenBiaSection3; import com.dacrt.SBIABackend.dto.ResumenBiaSection4; import com.dacrt.SBIABackend.dto.ResumenBiaSection5; import com.dacrt.SBIABackend.dto.ResumenBiaSection6; import com.dacrt.SBIABackend.dto.ResumenBiaSection7; import com.dacrt.SBIABackend.dto.ResumenBiaSection8; import com.dacrt.SBIABackend.dto.ResumenBiaSection9; import com.dacrt.SBIABackend.dto.ResumenBiabodyDto; import com.dacrt.SBIABackend.dto.ServiceOffersDescDto; import com.dacrt.SBIABackend.dto.StatusDto; import com.dacrt.SBIABackend.dto.StatusDto2; import com.dacrt.SBIABackend.dto.TypesUnitDto; import com.dacrt.SBIABackend.dto.requestDto.AppcoverageDashRequestDto; import com.dacrt.SBIABackend.dto.requestDto.CoverageAppRequestDto; import com.dacrt.SBIABackend.dto.requestDto.FacilitiesRequestDto; import com.dacrt.SBIABackend.dto.responseDto.ChannelsRankingResponseDto; import com.dacrt.SBIABackend.dto.responseDto.ChannelsResponseDto; import com.dacrt.SBIABackend.dto.responseDto.ContinuityResponseDto; import com.dacrt.SBIABackend.dto.responseDto.DahboardcampaignResponseDto; import com.dacrt.SBIABackend.dto.responseDto.ReportimpactResponseDto; import com.dacrt.SBIABackend.dto.responseDto.UnitsResponseDto; import com.dacrt.SBIABackend.repository.CampaignsRepository; import com.dacrt.SBIABackend.repository.ProcessesRepository; import com.dacrt.SBIABackend.repository.UnitprocessesRepository; import com.dacrt.SBIABackend.repository.UnitsRepository; import com.dacrt.SBIABackend.repository.UserunitsRepository; import com.dacrt.SBIABackend.repository.VicepresidenciesRepository; import com.dacrt.SBIABackend.security.dto.AuditRequestDto; import com.dacrt.SBIABackend.security.dto.ParamsResponseDto; import com.dacrt.SBIABackend.security.dto.PrivilegesAllDto; import com.dacrt.SBIABackend.security.dto.RespuestaDto; import com.dacrt.SBIABackend.security.dto.RespuestaMsgDto; import com.dacrt.SBIABackend.security.dto.UsersListDto; import com.dacrt.SBIABackend.security.entity.Roles; 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.AppteststatusService; import com.dacrt.SBIABackend.service.BcpcoverageService; import com.dacrt.SBIABackend.service.BcpstrategiesService; import com.dacrt.SBIABackend.service.BiaresumeproSection1Service; import com.dacrt.SBIABackend.service.BiaresumeproSection2Service; import com.dacrt.SBIABackend.service.BiaresumeproSection3Service; import com.dacrt.SBIABackend.service.BiaresumeproSection4Service; import com.dacrt.SBIABackend.service.BiaresumeproSection5Service; import com.dacrt.SBIABackend.service.BiaresumeproSection6Service; import com.dacrt.SBIABackend.service.BiaresumeproSection7Service; import com.dacrt.SBIABackend.service.BiaresumeproSection8Service; import com.dacrt.SBIABackend.service.BiaresumeproSection9Service; import com.dacrt.SBIABackend.service.ContinuitySection2Service; import com.dacrt.SBIABackend.service.ContinuitySection3Service; import com.dacrt.SBIABackend.service.DrpcoverageService; import com.dacrt.SBIABackend.service.DrpstrategiesService; import com.dacrt.SBIABackend.service.FacilitiesService; import com.dacrt.SBIABackend.service.ImpactReportSection1Service; import com.dacrt.SBIABackend.service.ImpactReportSection3Service; import com.dacrt.SBIABackend.service.ImpactReportSection4AppsService; import com.dacrt.SBIABackend.service.ImpactReportSection5Service; import com.dacrt.SBIABackend.service.ImpactReportSection6Service; import com.dacrt.SBIABackend.service.InventoryService; import com.dacrt.SBIABackend.service.RecoveryexpectationService; import com.dacrt.SBIABackend.service.RecoveryprocessesService; import com.dacrt.SBIABackend.service.RecuperationgapsService; import com.dacrt.SBIABackend.service.UnitsService; import com.dacrt.SBIABackend.service.VicepresidenciesService; @RestController //@RequestMapping("/units") @CrossOrigin(origins = "*") public class ReportController { @Autowired private ParamsRepository paramsRepository; @Autowired private UsersRepository usersRepository; @Autowired private AuditRepository auditRepository; @Autowired UsersService usersService; @Autowired MenuService menuService; @Autowired SecurityService securityService; @Autowired ParamsService paramsService; @Autowired UnitsService unitsService; @Autowired BiaresumeproSection1Service biaresumeproSection1Service; @Autowired BiaresumeproSection2Service biaresumeproSection2Service; @Autowired BiaresumeproSection3Service biaresumeproSection3Service; @Autowired BiaresumeproSection4Service biaresumeproSection4Service; @Autowired BiaresumeproSection5Service biaresumeproSection5Service; @Autowired BiaresumeproSection6Service biaresumeproSection6Service; @Autowired BiaresumeproSection7Service biaresumeproSection7Service; @Autowired BiaresumeproSection8Service biaresumeproSection8Service; @Autowired BiaresumeproSection9Service biaresumeproSection9Service; @Autowired AppteststatusService appteststatusService; @Autowired RecoveryprocessesService recoveryprocessesService; @Autowired RecoveryexpectationService recoveryexpectationService; @Autowired ImpactReportSection6Service impactReportSection6Service; @Autowired BcpcoverageService bcpcoverageService; @Autowired ImpactReportSection1Service impactReportSection1Service; @Autowired BcpstrategiesService bcpstrategiesService; @Autowired ContinuitySection2Service continuitySection2Service; @Autowired ContinuitySection3Service continuitySection3Service; @Autowired ImpactReportSection5Service impactReportSection5Service; @Autowired CampaignsRepository campaignsRepository; @Autowired DrpcoverageService drpcoverageService; @Autowired DrpstrategiesService drpstrategiesService; @Autowired InventoryService inventoryService; @Autowired RecuperationgapsService recuperationgapsService; @Autowired ImpactReportSection3Service impactReportSection3Service; @Autowired ImpactReportSection4AppsService impactReportSection4AppsService; @Autowired private JdbcTemplate jdbcTemplate; @Value("${customer}") private String client; @PersistenceContext private EntityManager entityManager; @Value("${spring.datasource.url}") private String conexion; @Value("${spring.datasource.username}") private String userbd; @Value("${spring.datasource.password}") private String passbd; @PostMapping("/reports/campaignstatus") public ResponseEntity campaignstatusDash(HttpServletRequest request, @RequestBody AppcoverageDashRequestDto tiposfiltros) throws ParseException { RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; LabelValueIntegerDto detalleLabelValueDtoestado = new LabelValueIntegerDto(); LabelValueIntegerDto detalleLabelValueDtoalcance = new LabelValueIntegerDto(); LabelValueIntegerDto detalleLabelValueDtodistri = new LabelValueIntegerDto(); DahboardcampaignContentDto dahboardcampaignContentDto1 = new DahboardcampaignContentDto(); DahboardcampaignContentDto dahboardcampaignContentDto2 = new DahboardcampaignContentDto(); DahboardcampaignContentDto dahboardcampaignContentDto3 = new DahboardcampaignContentDto(); DahboardcampaignHeadersDto dahboardcampaignHeadersDto = new DahboardcampaignHeadersDto(); DahboardcampaignResponseDto dahboardcampaignResponseDto = new DahboardcampaignResponseDto(); DahboardcampaignSection1Dto dahboardcampaignSection1Dto = new DahboardcampaignSection1Dto(); DahboardcampaignSection1Dto dahboardcampaignSection2Dto = new DahboardcampaignSection1Dto(); DahboardcampaignSection1Dto dahboardcampaignSection3Dto = new DahboardcampaignSection1Dto(); List dahboardcampaignContentDto1List = new ArrayList(); // ParamsDto detalleParams; 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 contentIn = ""; int searchStatus = 0; int facilityIn = 0; int Inunit; int idcampana; String InPeriodo; int RtoMinutes; String fechaComoCadena; Date fecha2 = new Date(); int iduser; 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(); iduser = encontreSessionUsuario.get().getId(); // 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); } Roles roles = encontreSessionUsuario.get().getRolid(); idrol = roles.getId(); int rolisvalid = auditRepository.getCantbyRolAndPrivi(idrol, 560); 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); } Inunit = tiposfiltros.getUnits(); switch (Inunit) { case 0: // viene sin busqueda por el like String mensaje = "ok"; break; case 1: // viene sin busqueda por el like String mensaje2 = "ok"; break; default: String var2 = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var2); respuestaDto= new RespuestaMsgDto("Llamada al servicio malformado- valor no permitido para la Unidad"); estatus=HttpStatus.BAD_REQUEST; return new ResponseEntity(respuestaDto, estatus); } idcampana = tiposfiltros.getCampaignid(); } 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 SentenciaBase1 = ""; String SentenciaBase2 = ""; String SentenciaBase3 = ""; Query query; Query query2; Query query3; String textCammaping1; String textUnits1; String textUnits2; String textUnits3; //idcampana = 12; //iduser = 55; //////////////////////comenzamos a armar la primera parte de la salida con el query//////////////estado de los procesos de la evaluacion if (idcampana==0) { textCammaping1 = " WHERE (null IS NOT NULL AND c.id = null) OR (null IS NULL AND ( "; } else { textCammaping1 = " WHERE ( " + idcampana + " IS NOT NULL AND c.id = " + idcampana + ") OR ( " + idcampana + " IS NULL AND ( "; } if (Inunit==0) { textUnits1 = " AND ( null IS NULL " + " OR ep.unitprocessid IN ( SELECT up.id FROM main.unitprocesses up " + " INNER JOIN main.userunits uu ON up.unitid = uu.unitid " + " WHERE uu.userid = null ) ) GROUP BY estado_evaluacion),"; textUnits2 = " ep.impactst = 1 GROUP BY cu.campaingid ), "; textUnits3 = " ( null IS NULL OR up.unitid IN ( SELECT unitid FROM main.userunits WHERE userid = null ) ) "; } else { textUnits1 = " AND ( " + iduser + " IS NULL " + " OR ep.unitprocessid IN ( SELECT up.id FROM main.unitprocesses up " + " INNER JOIN main.userunits uu ON up.unitid = uu.unitid " + " WHERE uu.userid = " + iduser + " ) ) GROUP BY estado_evaluacion),"; textUnits2 = " ep.impactst = 1 AND uu.userid = " + iduser + " GROUP BY cu.campaingid ),"; textUnits3 = " ( " + iduser + " IS NULL OR up.unitid IN ( SELECT unitid FROM main.userunits WHERE userid = " + iduser + " ) ) "; } SentenciaBase1 = "WITH CampanaSeleccionada AS ( " + " SELECT c.id " + " FROM main.campaigns c "; SentenciaBase1 = SentenciaBase1 + textCammaping1; SentenciaBase1 = SentenciaBase1 + " c.status = 1 OR c.id = ( " + " SELECT id FROM main.campaigns ORDER BY createdat DESC LIMIT 1 ) ))" + " ORDER BY " + " c.status DESC, c.createdat DESC " + " LIMIT 1 " + "), " + "ResultadosBase AS ( " + " SELECT " + " CASE " + " WHEN ep.impactst = 1 AND ep.appst = 1 AND ep.suplierst = 1 AND ep.recordst = 1 AND ep.periodst = 1 AND ep.inputst = 1 AND ep.reportst = 1 THEN 'Completados' " + " WHEN ep.impactst = 0 AND ep.appst = 0 AND ep.suplierst = 0 AND ep.recordst = 0 AND ep.periodst = 0 AND ep.inputst = 0 AND ep.reportst = 0 THEN 'No Iniciados' " + " ELSE 'En Proceso' " + " END AS estado_evaluacion, " + " CAST(COUNT(ep.id) AS INTEGER) AS cantidad_procesos " + " FROM " + " main.evalprocesses ep " + " INNER JOIN " + " main.campaignunits cu ON ep.campaignunitid = cu.id " + " WHERE " + " cu.campaingid IN (SELECT id FROM CampanaSeleccionada) "; SentenciaBase1 = SentenciaBase1 + textUnits1 + " Categorias AS ( " + " SELECT 'Completados' AS estado_evaluacion " + " UNION ALL SELECT 'En Proceso' " + " UNION ALL SELECT 'No Iniciados' " + " ) " + " SELECT " + " c.estado_evaluacion, " + " COALESCE(rb.cantidad_procesos, 0) AS cantidad_procesos " + " FROM " + " Categorias c " + " LEFT JOIN " + " ResultadosBase rb ON c.estado_evaluacion = rb.estado_evaluacion " + " ORDER BY " + " c.estado_evaluacion ASC"; //////////////////////fin de armar la primera parte de la salida con el query//////////////estado de los procesos de la evaluacion asociadas //////////////////////comenzamos a armar la segunda parte de la salida con el query//////////////alcance de la dependencias asociadas SentenciaBase2 = " WITH CampanaSeleccionada AS ( " + " SELECT c.id FROM main.campaigns c "; SentenciaBase2 = SentenciaBase2 + textCammaping1; SentenciaBase2 = SentenciaBase2 + " c.status = 1 OR c.id = ( " + " SELECT id FROM main.campaigns ORDER BY createdat DESC LIMIT 1 " + " ) " + " )) " + " ORDER BY " + " c.status DESC, c.createdat DESC " + " LIMIT 1 " + " ), " + " ResultadosRaw AS ( " + " SELECT " + " cu.campaingid, " + " CAST(COUNT(DISTINCT CASE WHEN ep.appst = 1 AND a.status = 1 THEN eap.applicationid END) AS INTEGER) AS Aplicaciones, " + " CAST(COUNT(DISTINCT CASE WHEN ep.suplierst = 1 AND s.status = 1 THEN esu.supplierid END) AS INTEGER) AS Suppliers, " + " CAST(COUNT(DISTINCT CASE WHEN ep.recordst = 1 THEN ere.name END) AS INTEGER) AS Record " + " FROM main.evalprocesses ep " + " INNER JOIN main.campaignunits cu ON ep.campaignunitid = cu.id " + " INNER JOIN main.unitprocesses up ON ep.unitprocessid = up.id " + " INNER JOIN main.userunits uu ON up.unitid = uu.unitid " + " INNER JOIN CampanaSeleccionada cs ON cu.campaingid = cs.id " + " LEFT JOIN main.evalprocapps eap ON ep.id = eap.evalprocessid " + " LEFT JOIN main.applications a ON eap.applicationid = a.id " + " LEFT JOIN main.evalprocsupliers esu ON ep.id = esu.evalprocessid " + " LEFT JOIN main.suppliers s ON esu.supplierid = s.id " + " LEFT JOIN main.evalprocrecords ere ON ep.id = ere.evalprocessid " + " WHERE "; SentenciaBase2 = SentenciaBase2 + textUnits2; SentenciaBase2 = SentenciaBase2 + " Categorias AS ( " + " SELECT 'Aplicaciones' AS categoria, 1 AS orden " + " UNION ALL " + " SELECT 'Proveedores', 2 " + " UNION ALL " + " SELECT 'Registros Vitales', 3 " + " ) " + " SELECT " + " c.categoria, " + " rr.campaingid, " + " COALESCE(CASE " + " WHEN c.categoria = 'Aplicaciones' THEN rr.Aplicaciones " + " WHEN c.categoria = 'Proveedores' THEN rr.Suppliers " + " WHEN c.categoria = 'Registros Vitales' THEN rr.Record " + " END, " + " 0 " + " ) AS cantidad " + " FROM Categorias c " + " LEFT JOIN ResultadosRaw rr ON 1=1 " + " ORDER BY c.orden "; //////////////////////fin de armar la segunda parte de la salida con el query//////////////alcance de la dependencias asociadas //////////////////////comenzamos a armar la tercera parte de la salida con el query//////////////distribucion rto de los procesos SentenciaBase3 = " WITH CampanaSeleccionada AS ( " + " SELECT c.id FROM main.campaigns c "; SentenciaBase3 = SentenciaBase3 + textCammaping1; SentenciaBase3 = SentenciaBase3 + " c.status = 1 OR c.id = ( SELECT id FROM main.campaigns ORDER BY createdat DESC LIMIT 1 ) )) " + " ORDER BY c.status DESC, c.createdat DESC LIMIT 1 " + " ), " + " RangosDisponibles AS ( " + " SELECT '≤02h' AS rango_rto, 1 AS orden UNION ALL " + " SELECT '04h', 2 UNION ALL " + " SELECT '08h', 3 UNION ALL " + " SELECT '12h', 4 UNION ALL " + " SELECT '24h', 5 UNION ALL " + " SELECT '48h', 6 UNION ALL " + " SELECT '>48h', 7 " + " ), " + " DatosFiltrados AS ( " + " SELECT CASE WHEN ei.rtoqty <= 120 THEN '≤02h' " + " WHEN ei.rtoqty > 120 AND ei.rtoqty <= 240 THEN '04h' " + " WHEN ei.rtoqty > 240 AND ei.rtoqty <= 480 THEN '08h' " + " WHEN ei.rtoqty > 480 AND ei.rtoqty <= 720 THEN '12h' " + " WHEN ei.rtoqty > 720 AND ei.rtoqty <= 1440 THEN '24h' " + " WHEN ei.rtoqty > 1440 AND ei.rtoqty <= 2880 THEN '48h' " + " ELSE '>48h' " + " END AS rango_rto, " + " ep.id AS proceso_id " + " FROM main.evalprocesses ep " + " INNER JOIN main.campaignunits cu ON ep.campaignunitid = cu.id " + " INNER JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid " + " INNER JOIN main.unitprocesses up ON ep.unitprocessid = up.id " + " INNER JOIN CampanaSeleccionada cs ON cu.campaingid = cs.id " + " WHERE " ; SentenciaBase3 = SentenciaBase3 + textUnits3; SentenciaBase3 = SentenciaBase3 + " and ep.impactst = 1 ) " + " SELECT rd.rango_rto, cast(COALESCE(COUNT(df.proceso_id), 0) as integer) AS cantidad_procesos " + " FROM RangosDisponibles rd " + " LEFT JOIN DatosFiltrados df ON rd.rango_rto = df.rango_rto " + " GROUP BY rd.rango_rto, rd.orden " + " ORDER BY rd.orden "; //////////////////////fin de armar la tercera parte de la salida con el query//////////////distribucion rto de los procesos query = entityManager.createNativeQuery(SentenciaBase1); List listacompleta1 = query.getResultList(); query2 = entityManager.createNativeQuery(SentenciaBase2); List listacompleta2 = query2.getResultList(); query3 = entityManager.createNativeQuery(SentenciaBase3); List listacompleta3 = query3.getResultList(); List contentList = new ArrayList<>(); detalleLabelValueDtoestado = new LabelValueIntegerDto(); detalleLabelValueDtoalcance = new LabelValueIntegerDto(); detalleLabelValueDtodistri = new LabelValueIntegerDto(); dahboardcampaignContentDto1 = new DahboardcampaignContentDto(); dahboardcampaignContentDto2 = new DahboardcampaignContentDto(); dahboardcampaignContentDto3 = new DahboardcampaignContentDto(); dahboardcampaignHeadersDto = new DahboardcampaignHeadersDto(); dahboardcampaignResponseDto = new DahboardcampaignResponseDto(); dahboardcampaignSection1Dto = new DahboardcampaignSection1Dto(); List dahboardcampaignSection1DtoList = new ArrayList(); List dahboardcampaignSection2DtoList = new ArrayList(); List dahboardcampaignSection3DtoList = new ArrayList(); List estadoEvaluacionList = new ArrayList<>(); List estadoEvaluacionList2 = new ArrayList<>(); List estadoEvaluacionList3 = new ArrayList<>(); dahboardcampaignContentDto1List = new ArrayList(); int cuantosregistros1 = listacompleta1.size(); int cuantosregistros2 = listacompleta2.size(); int cuantosregistros3 = listacompleta3.size(); if (cuantosregistros1>0) { for (Object[] reg : listacompleta1) { detalleLabelValueDtoestado = new LabelValueIntegerDto(); dahboardcampaignContentDto1 = new DahboardcampaignContentDto(); dahboardcampaignSection1Dto = new DahboardcampaignSection1Dto(); detalleLabelValueDtoestado.setLabel((String) reg[0]) ; detalleLabelValueDtoestado.setValue((int) reg[1]); estadoEvaluacionList.add(detalleLabelValueDtoestado); //dahboardcampaignSection1Dto.setSections(detalleLabelValueDtoestado); //dahboardcampaignSection1DtoList.add(dahboardcampaignSection1Dto); } } dahboardcampaignContentDto1.setName("Estado de Evaluación (procesos)"); dahboardcampaignContentDto1.setSections(estadoEvaluacionList); if (cuantosregistros2>0) { for (Object[] reg2 : listacompleta2) { detalleLabelValueDtoalcance = new LabelValueIntegerDto(); dahboardcampaignSection2Dto = new DahboardcampaignSection1Dto(); detalleLabelValueDtoalcance.setLabel((String) reg2[0]) ; detalleLabelValueDtoalcance.setValue((int) reg2[2]); estadoEvaluacionList2.add(detalleLabelValueDtoalcance); //dahboardcampaignSection2Dto.setSections(detalleLabelValueDtoalcance); //dahboardcampaignSection2DtoList.add(dahboardcampaignSection2Dto); } } dahboardcampaignContentDto2.setName("Alcance de las Dependencias Asociadas"); dahboardcampaignContentDto2.setSections(estadoEvaluacionList2); if (cuantosregistros3>0) { for (Object[] reg3 : listacompleta3) { detalleLabelValueDtodistri = new LabelValueIntegerDto(); dahboardcampaignSection3Dto = new DahboardcampaignSection1Dto(); detalleLabelValueDtodistri.setLabel((String) reg3[0]) ; detalleLabelValueDtodistri.setValue((int) reg3[1]); estadoEvaluacionList3.add(detalleLabelValueDtodistri); // dahboardcampaignSection3Dto.setSections(detalleLabelValueDtodistri); // dahboardcampaignSection3DtoList.add(dahboardcampaignSection3Dto); } } dahboardcampaignContentDto3.setName("Distribución de RTO de los Procesos"); dahboardcampaignContentDto3.setSections(estadoEvaluacionList3); dahboardcampaignHeadersDto.setTitle("Estado de la Campaña"); contentList.add(dahboardcampaignContentDto1); contentList.add(dahboardcampaignContentDto2); contentList.add(dahboardcampaignContentDto3); dahboardcampaignResponseDto.setHeader(dahboardcampaignHeadersDto); dahboardcampaignResponseDto.setContent(contentList); return ResponseEntity.ok(dahboardcampaignResponseDto); } 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); } @PostMapping("/reports/continuity") public ResponseEntity reportConitnuity(HttpServletRequest request, @RequestBody ReportDateDto fechain) throws ParseException { /* Conversión de fecha */ SimpleDateFormat inputFormat = new SimpleDateFormat("yyyyMMdd"); Date fecha = inputFormat.parse(fechain.getDate()); /*********************/ RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; int idrol; String sessionid = request.getHeader("Authorization"); sessionid = sessionid.substring(7); Optional encontreSessionUsuario =usersRepository.getBySessionid(sessionid); 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 { int rolisvalid = 0; Roles roles = encontreSessionUsuario.get().getRolid(); idrol = roles.getId(); rolisvalid = auditRepository.getCantbyRolAndPrivi(encontreSessionUsuario.get().getRolid().getId(), 500); if (rolisvalid == 0) { respuesta.setMsg("No tiene los Privilegios"); estatus = HttpStatus.FORBIDDEN; return new ResponseEntity(respuesta, estatus); } } ContinuityResponseDto continuityResponseDto = new ContinuityResponseDto(); ContinuitySection2Dto detalleContinuitySection2Dto = new ContinuitySection2Dto(); ContinuitySection3Dto detalleContinuitySection3Dto = new ContinuitySection3Dto(); ContinuitySection1Dto detalleContinuitySection1Dto = new ContinuitySection1Dto(); ContentSection1Dto detalleContentSection1Dto = new ContentSection1Dto(); ContinuityHeaderDto detalleContinuityHeaderDto = new ContinuityHeaderDto(); ContinuityBodySectionDto detalleContinuityBodySectionDto = new ContinuityBodySectionDto(); // declaracion de los dto// List listadoappteststatus = new ArrayList(); List listadobcpcoverage = new ArrayList(); List listadobcpstrategies = new ArrayList(); detalleContinuitySection2Dto = new ContinuitySection2Dto(); detalleContinuitySection3Dto = new ContinuitySection3Dto(); detalleContinuitySection1Dto = new ContinuitySection1Dto(); List listadodrpcoverage = new ArrayList(); List listadodrpstrategies = new ArrayList(); List listadoinventory = new ArrayList(); List listadorecuperationgaps = new ArrayList(); // Lamada a los servicios de cada seccion del reporte listadoappteststatus = appteststatusService.getAppeststatus(fechain.getDate()); listadobcpcoverage = bcpcoverageService.getBcpcoverage(fechain.getDate()); listadobcpstrategies = bcpstrategiesService.getBcpstrategies(fechain.getDate()); listadodrpcoverage = drpcoverageService.getDrpcoverage(fechain.getDate()); listadodrpstrategies = drpstrategiesService.getDrpstrategies(fechain.getDate()); listadoinventory = inventoryService.getInventory(fechain.getDate()); listadorecuperationgaps = recuperationgapsService.getRecuperationgaps(fechain.getDate()); // seteando la seccion 1 del reporte de continuidad// detalleContentSection1Dto.setAppteststatus(listadoappteststatus); detalleContentSection1Dto.setBcpcoverage(listadobcpcoverage); detalleContentSection1Dto.setBcpstrategies(listadobcpstrategies); detalleContentSection1Dto.setDrpcoverage(listadodrpcoverage); detalleContentSection1Dto.setDrpstrategies(listadodrpstrategies); detalleContentSection1Dto.setInventory(listadoinventory); detalleContentSection1Dto.setRecuperationgaps(listadorecuperationgaps); // setendo la seccion 2 y 3 del reporte detalleContinuitySection2Dto = continuitySection2Service.getResumeChannels(fechain.getDate()); detalleContinuitySection3Dto = continuitySection3Service.getResumeApps(fechain.getDate()); /// setendo la seccion 2 y 3 del reporte////////////////////////// detalleContinuitySection1Dto.setTitle("Resumen de Estado del BCM"); detalleContinuitySection1Dto.setContent(detalleContentSection1Dto); // seteo el title del reporte completo Date fechaActual = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.forLanguageTag("es")); String fechaFormateada = sdf.format(fechaActual); String Titulo = "Reporte de " + client + " - " + fechaFormateada; detalleContinuityHeaderDto.setTitle(Titulo); ///////////////////////////////////////////////////////////////// // seteo todo el reporte completo continuityResponseDto.setHeader(detalleContinuityHeaderDto); // seteo el body para cada seccion// detalleContinuityBodySectionDto.setSection1(detalleContinuitySection1Dto); detalleContinuityBodySectionDto.setSection2(detalleContinuitySection2Dto); detalleContinuityBodySectionDto.setSection3(detalleContinuitySection3Dto); ///// continuityResponseDto.setBody(detalleContinuityBodySectionDto); return ResponseEntity.ok(continuityResponseDto); } @PostMapping("/reports/impact") public ResponseEntity reportimpact(HttpServletRequest request, @RequestBody ReportDateDto datosin) throws ParseException { /* Conversión de fecha */ SimpleDateFormat inputFormat = new SimpleDateFormat("yyyyMMdd"); //Date fecha = inputFormat.parse(datosin.getDate()); int idrol; int idcampana = datosin.getCampaignid(); String contentin = datosin.getContent(); /*********************/ RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; String sessionid = request.getHeader("Authorization"); sessionid = sessionid.substring(7); Optional encontreSessionUsuario =usersRepository.getBySessionid(sessionid); 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 { int rolisvalid = 0; Roles roles = encontreSessionUsuario.get().getRolid(); idrol = roles.getId(); rolisvalid = auditRepository.getCantbyRolAndPrivi(encontreSessionUsuario.get().getRolid().getId(), 540); if (rolisvalid == 0) { respuesta.setMsg("No tiene los Privilegios"); estatus = HttpStatus.FORBIDDEN; return new ResponseEntity(respuesta, estatus); } } if (campaignsRepository.existsById(idcampana)) { String mensaje = "campana existe"; } else { String var = ""; boolean bloked = false; RespuestaDto respuestaDto = new RespuestaDto(var, bloked); respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Campana no existe"); //Error 400 return new ResponseEntity(respuestaDto, HttpStatus.BAD_REQUEST); } if (contentin !=null) { menuService.iscontentdiffnull(contentin, encontreSessionUsuario.get().getId()); } ImpactContentSection2Dto detalleContentSection2= new ImpactContentSection2Dto(); ImpactContentResponseDto detalleImpactContentResponseDto = new ImpactContentResponseDto(); // declaracion de los dto// //////////////////nuevo reporte/////////// List listadorecoveryprocesses = new ArrayList(); List listadorecoveryexpectation = new ArrayList(); ImpactReportSection1Dto detalleImpactReportSection1 = new ImpactReportSection1Dto(); ImpactReportSection3Dto detalleImpactReportSection3 = new ImpactReportSection3Dto(); ImpactReportSection4Dto detalleImpactReportSection4 = new ImpactReportSection4Dto(); ImpactReportSection5Dto detalleImpactReportSection5Dto = new ImpactReportSection5Dto(); ImpactReportSection6Dto detalleImpactReportSection6Dto = new ImpactReportSection6Dto(); ImpactHeaderDto detalleContinuityHeaderDto = new ImpactHeaderDto(); ImpactBodyDto detalleImpactBodyDto = new ImpactBodyDto(); ReportimpactResponseDto outReportimpactResponseDto = new ReportimpactResponseDto(); // SECCION 1 DEL REPORTE DE IMPACT detalleImpactReportSection1 = impactReportSection1Service.getImpactResumeCampaigns(idcampana, contentin); /////FIN DE LA SECCION 1///////////////// //SECCION 2 DEL REPORTE DE IMPACT listadorecoveryprocesses = recoveryprocessesService.getRecoveryprocesses(idcampana,contentin); listadorecoveryexpectation = recoveryexpectationService.getrecoveryexpectation(idcampana,contentin); detalleContentSection2.setRecoveryexpectation(listadorecoveryexpectation); detalleContentSection2.setRecoveryprocesses(listadorecoveryprocesses); detalleImpactContentResponseDto.setTitle("Resumen de Análisis de Procesos y Unidades"); detalleImpactContentResponseDto.setContent(detalleContentSection2); ///////FIN DE LA SECCION 2/////////////////// // SECCION 3 DEL REPORTE DE IMPACT detalleImpactReportSection3 = impactReportSection3Service.getImpactResumeChannels(idcampana, contentin); /////FIN DE LA SECCION 3///////////////// // SECCION 4 DEL REPORTE DE IMPACT detalleImpactReportSection4 = impactReportSection4AppsService.getImpactResumeChannels(idcampana, contentin); /////FIN DE LA SECCION 4///////////////// // SECCION 5 DEL REPORTE DE IMPACT detalleImpactReportSection5Dto = impactReportSection5Service.getImpactCondPersonalResourcesKey(idcampana, contentin); /////FIN DE LA SECCION 5///////////////// // SECCION 6 DEL REPORTE DE IMPACT detalleImpactReportSection6Dto = impactReportSection6Service.getImpactResumeforUnits(idcampana, contentin); /////FIN DE LA SECCION 6///////////////// //detalleImpactReportSection6Dto = impactReportSection6Service.getImpactResumeforUnits(idcampana, contentin); // seteo el title del reporte completo Date fechaActual = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.forLanguageTag("es")); String fechaFormateada = sdf.format(fechaActual); String Titulo = "Reporte de " + client + " - " + fechaFormateada; //detalleContinuityHeaderDto.setTitle(Titulo); //correccion del encabezado detalleContinuityHeaderDto.setTitle("Análisis de Impacto y Ranking"); detalleContinuityHeaderDto.setSubtitle(Titulo); detalleImpactBodyDto.setSection1(detalleImpactReportSection1); detalleImpactBodyDto.setSection2(detalleImpactContentResponseDto); detalleImpactBodyDto.setSection3(detalleImpactReportSection3); detalleImpactBodyDto.setSection4(detalleImpactReportSection4); detalleImpactBodyDto.setSection5(detalleImpactReportSection5Dto); detalleImpactBodyDto.setSection6(detalleImpactReportSection6Dto); ///seteando la salida principal outReportimpactResponseDto.setHeader(detalleContinuityHeaderDto); outReportimpactResponseDto.setBody(detalleImpactBodyDto); //////////// return ResponseEntity.ok(outReportimpactResponseDto); } @PostMapping("/reports/channelsranking") public ResponseEntity channelsRanking(HttpServletRequest request,HttpServletResponse response,@RequestBody UsersListDto tiposfiltros) throws ParseException { RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; ChannelsResponseDto channelsResponseDto = new ChannelsResponseDto(); ChannelsRankingResponseDto channelsRankingResponseDto = new ChannelsRankingResponseDto(); ChannelsRecordDto channelsRecordDto = new ChannelsRecordDto(); RecordCatalogoResponseDto channelsFormatResponseDto = new RecordCatalogoResponseDto(); CampaignFormat2Dto detalleCompaing2; int formatList = 0; // ParamsDto detalleParams; CampaignFormat2Dto detalleSuppliers; StatusDto2 detalleStatus; StatusDto2 detalleType; StatusDto detalleStatus2; ChannelsAmountDto detalleAmountOP; ChannelsAmountDto detallevolumeOP; OperationlineSinStatusDto detalleChannels; OperationlineSinStatusDto detalleTypeCha; ChannelsRankingValFormatDto detalleWeight; ChannelsRankingValFormatDto detalleAvgtx; ChannelsRankingValFormatDto detalleTotamt; ChannelsRankingValFormatDto detalleCustomer; ChannelsRankingRtoDto detalleRto; IddscDto detalleTypeProcesses; IddscDto detalleOperLineProcesses; ChannelsRankingProcessesDto detalleProcesses; OperationlineSinStatusDto detalleTypeApp; OperationlineSinStatusDto detalleDrptested; ChannelsRankingApplicationsDto detalleApplication; int searchOfferservice; List listasProcesses = new ArrayList<>(); List listasApplication = new ArrayList<>(); List listasChannelsRankingRecordDto = new ArrayList<>(); ChannelsRankingRecordDto detalleChannelsRankingRecordDto; StatusDto2 detallecapacityperiod; ServiceOffersDescDto detalleserviceoffers; List listasTypes = new ArrayList<>(); List listasOfertas = new ArrayList<>(); PrivilegesAllDto detallePrivilege; Long cuantosregistro = (long) 0; String searchChanneltype; List listasPrivelege = new ArrayList<>(); List listasRecord= new ArrayList<>(); TypesUnitDto detalleTypes; TypesUnitDto detallePeriodos; List listasPeriodos = 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 formatIn; 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(); 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); } int rolisvalid = 0; Roles roles = encontreSessionUsuario.get().getRolid(); idrol = roles.getId(); rolisvalid = auditRepository.getCantbyRolAndPrivi(encontreSessionUsuario.get().getRolid().getId(), 540); if (rolisvalid == 0) { respuesta.setMsg("No tiene los Privilegios"); estatus = HttpStatus.FORBIDDEN; return new ResponseEntity(respuesta, estatus); } searchIn = usersService.eliminarAcentosService(tiposfiltros.getFilters().getSearch()); searchChanneltype = tiposfiltros.getFilters().getChanneltypepar(); searchOfferservice = tiposfiltros.getFilters().getServiceofferid(); if (searchChanneltype==null) { searchChanneltype=""; } else { searchChanneltype=searchChanneltype; } orderIn = tiposfiltros.getOrder(); offsetIn = tiposfiltros.getOffset(); numofrecordsIn = tiposfiltros.getNumofrecords(); contentIn = tiposfiltros.getContent(); if (contentIn !=null) { menuService.iscontentdiffnull(contentIn, encontreSessionUsuario.get().getId()); } formatIn = 0; try { formatIn = tiposfiltros.getFormat(); } catch (Exception e) { formatIn = 0; } } 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; SentenciaBase = " WITH CampanasConRanking AS ( " + " SELECT " + " id, " + " ROW_NUMBER() OVER (ORDER BY dateto DESC) AS ranking_campana " + " FROM main.campaigns " + " WHERE DATE_TRUNC('day', dateto) <= CURRENT_DATE " + " ), " + " ProcesosConRTOAgregado AS ( " + " SELECT " + " pa.channelid, " + " cr.ranking_campana, " + " MIN(t1.rtoqty) AS rto, " + " CAST(COUNT(DISTINCT p.id) AS integer) AS total_procesos_por_canal, " + " STRING_AGG(CAST(p.id AS text), ';' ORDER BY p.name ASC) AS idprocesos, " + " STRING_AGG(p.name, ';' ORDER BY p.name ASC) AS procesosacu, " + " STRING_AGG(CAST(p.processtypepar AS text), ';' ORDER BY p.name ASC) AS procesostipo, " + " STRING_AGG(CAST(pr1.descr AS text), ';' ORDER BY p.name ASC) AS tipoproccesDesc, " + " STRING_AGG(CAST(pr1.valor AS text), ';' ORDER BY p.name ASC) AS idproccestype, " + " STRING_AGG(CAST(t1.rtoqty AS text), ';' ORDER BY p.name ASC) AS rtosagrupados, " + " STRING_AGG(CAST(ol.id AS text), ';' ORDER BY p.name ASC) AS idlineop, " + " STRING_AGG(ol.name, ';' ORDER BY p.name ASC) AS lineop, " + " STRING_AGG(CAST(c.id AS text), ';' ORDER BY c.id ASC) AS campid, " + " STRING_AGG(CAST(cu.id AS text), ';' ORDER BY c.id ASC) AS campaignunitid " + " FROM main.processes p " + " INNER JOIN main.processchannels pa ON p.id = pa.processid " + " INNER JOIN main.unitprocesses up ON p.id = up.processid " + " INNER JOIN main.evalprocesses ep ON up.id = ep.unitprocessid " + " INNER JOIN main.campaignunits cu ON ep.campaignunitid = cu.id " + " INNER JOIN main.campaigns c ON cu.campaingid = c.id " + " INNER JOIN main.operationlines ol ON p.operationlineid = ol.id " + " LEFT JOIN ( " + " SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor " + " FROM main.params pr, jsonb_array_elements(CAST(pr.value AS jsonb)) AS elemento " + " WHERE pr.paramname = 'PROCESS_TYPE' " + " ) pr1 ON p.processtypepar = CAST(pr1.valor AS integer) " + " INNER JOIN main.evalprocimpacts t1 ON ep.id = t1.evalprocessid " + " INNER JOIN CampanasConRanking cr ON c.id = cr.id " + " WHERE ep.impactst = 1 " + " GROUP BY pa.channelid, cr.ranking_campana " + " ), " + " AppsRTOsAgregados AS ( " + " SELECT " + " epa.applicationid, " + " cr.ranking_campana, " + " MIN(ei.rtoqty) AS min_rto_app " + " FROM main.evalprocapps epa " + " JOIN main.evalprocesses ep ON epa.evalprocessid = ep.id " + " INNER JOIN main.campaignunits cu ON ep.campaignunitid = cu.id " + " INNER JOIN main.campaigns c ON cu.campaingid = c.id " + " JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid " + " INNER JOIN CampanasConRanking cr ON c.id = cr.id " + " WHERE ep.impactst = 1 " + " GROUP BY epa.applicationid, cr.ranking_campana " + " ), " + " AplicacionesAgregadas AS ( " + " SELECT " + " unnested_channels.channel_id, " + " COALESCE(CAST(COUNT(DISTINCT ap.id) AS integer), 0) AS total_app_por_canal, " + " STRING_AGG(CAST(ap.id AS text), ';' ORDER BY ap.name ASC) AS idapps, " + " STRING_AGG(ap.name, ';' ORDER BY ap.name ASC) AS appsname, " + " STRING_AGG(CAST(prapp.descr AS text), ';' ORDER BY ap.name ASC) AS descTypeApp, " + " STRING_AGG(CAST(prapp.valor AS text), ';' ORDER BY ap.name ASC) AS idtypeApp, " + " STRING_AGG(CAST(prapp2.descr AS text), ';' ORDER BY ap.name ASC) AS descTestedpar, " + " STRING_AGG(CAST(prapp2.valor AS text), ';' ORDER BY ap.name ASC) AS idTestedpar, " + " STRING_AGG(COALESCE(CAST(rtos_agg.min_rto_app AS text), '0'), ';' ORDER BY ap.name ASC) AS rtos_por_app, " + " MIN(rtos_agg.min_rto_app) AS rto_min_app " + " FROM ( " + " SELECT DISTINCT " + " CAST(unnested_id AS integer) AS channel_id, " + " ap.id, " + " ap.name, " + " ap.apptypepar, " + " ap.drptestedpar " + " FROM main.applications ap, " + " unnest(string_to_array(ap.channelids, ',')) AS unnested_id " + " ) AS unnested_channels " + " JOIN main.applications ap ON unnested_channels.id = ap.id " + " LEFT JOIN AppsRTOsAgregados rtos_agg ON ap.id = rtos_agg.applicationid " + " LEFT JOIN ( " + " SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor " + " FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento " + " WHERE p.paramname = 'APPS_TYPE' " + " ) prapp ON ap.apptypepar = prapp.valor " + " LEFT JOIN ( " + " SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor " + " FROM main.params p2, jsonb_array_elements(CAST(p2.value AS jsonb)) AS elemento " + " WHERE p2.paramname = 'APPS_YESNO' " + " ) prapp2 ON ap.drptestedpar = CAST(prapp2.valor AS integer) " + " GROUP BY unnested_channels.channel_id " + " ), " + " ResultadoFinalProcesos AS ( " + " SELECT " + " *, " + " ROW_NUMBER() OVER (PARTITION BY channelid ORDER BY ranking_campana ASC, rto ASC) AS rn " + " FROM ProcesosConRTOAgregado " + " ), " + " ResultadoInicial AS ( " + " SELECT " + " pa.campid, " + " ch.id AS chid, " + " ch.name AS chname, " + " pr.descr AS tipocanal, " + " pr.valor AS idtipocanal, " + " CAST(ch.capacity AS integer) AS rtoti, " + " pa.rto, " + " aa.rto_min_app, " + " pa.total_procesos_por_canal, " + " ROUND(100.0 * ((ch.amountop * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.amountop) FROM main.channels ch1), 0), 1)) + " + " (ch.volumeop * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.volumeop) FROM main.channels ch1), 0), 1)) + " + " (ch.customerqty * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.customerqty) FROM main.channels ch1), 0), 1))) / 3, 2) AS peso, " + " (ROUND(100.0 * ((ch.amountop * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.amountop) FROM main.channels ch1), 0), 1)) + " + " (ch.volumeop * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.volumeop) FROM main.channels ch1), 0), 1)) + " + " (ch.customerqty * 1.0 / COALESCE(NULLIF((SELECT SUM(ch1.customerqty) FROM main.channels ch1), 0), 1))) / 3, 2)) || '%' AS pesoformateado, " + " ch.volumeop AS avgtx, " + " ch.amountop AS totamt, " + " ch.customerqty AS clientes, " + " pa.idlineop, " + " pa.lineop, " + " aa.idapps, " + " aa.appsname, " + " pa.idprocesos, " + " pa.procesosacu, " + " pa.procesostipo, " + " pa.tipoproccesDesc, " + " pa.idproccestype, " + " pa.rtosagrupados, " + " aa.descTypeApp, " + " aa.idtypeApp, " + " aa.rtos_por_app, " + " aa.descTestedpar, " + " aa.idTestedpar, " + " ch.serviceoffers, " + " total_app_por_canal " + " FROM main.channels ch " + " LEFT JOIN ( " + " SELECT elemento ->> 'dsc' AS descr, elemento ->> 'value' AS valor " + " FROM main.params p, jsonb_array_elements(CAST(p.value AS jsonb)) AS elemento " + " WHERE p.paramname = 'CHANNEL_TYPE' " + " ) pr ON ch.channeltypepar = CAST(pr.valor AS integer) " + " LEFT JOIN ResultadoFinalProcesos pa ON ch.id = pa.channelid " + " LEFT JOIN AplicacionesAgregadas aa ON ch.id = aa.channel_id " + " WHERE ch.status = 1 AND pa.rto IS NOT NULL AND pa.rn = 1 " + " ), " + " ResultadoConRanking AS ( " + " SELECT *, ROW_NUMBER() OVER(PARTITION BY chid ORDER BY rto DESC) AS ranking " + " FROM ResultadoInicial " + " ) " + " SELECT " + " chid, chname, tipocanal, idtipocanal, campid, rtoti, total_procesos_por_canal, rto, peso, pesoformateado, avgtx, " + " CASE " + " WHEN avgtx = 0 THEN '0.00' " + " ELSE REPLACE(REPLACE(REPLACE(TO_CHAR(avgtx, 'FM99G999G999G999D00'), ',', '#'), '.', ','), '#', '.') " + " END AS totaloperacionesformat, " + " totamt, " + " CASE " + " WHEN totamt = 0 THEN '0.00' " + " ELSE REPLACE(REPLACE(REPLACE(TO_CHAR(totamt, 'FM99G999G999G999D00'), ',', '#'), '.', ','), '#', '.') " + " END AS totalmontoformat, " + " clientes, " + " REPLACE(TO_CHAR(clientes, 'FM9G999G999'), ',', '.') AS clientesformato, " + " idlineop, lineop, idapps, appsname, descTypeApp, idtypeApp, descTestedpar, " + " idTestedpar, idprocesos, procesosacu, " + " CASE " + " WHEN rto = 0 THEN 0 " + " ELSE ROUND(100.0 * (rto - rtoti) / rto, 2) " + " END AS rtodelta, " + " tipoproccesDesc, idproccestype, rtosagrupados, " + " COALESCE(rto_min_app, 0) AS rto_min_app, " + " rtos_por_app, serviceoffers, total_app_por_canal " + " FROM " + " ResultadoConRanking " + " WHERE ranking = 1 "; // + " ORDER BY " // + " rtopr ASC "; //+ " WHERE ranking = 1 AND rtopr <= 1440 " Query query; String QueryTotal = ""; String name = "u.name"; String status = "u.status"; String lowername = "main.sinacentos(LOWER(chname))"; String lowerdsc = "main.sinacentos(LOWER(u.dsc))"; String lowerdscParams = "main.sinacentos(LOWER(pr.descr))"; String LowerSearch = searchIn.toLowerCase(); // String lowername = "main.sinacentos(LOWER(u.name))"; switch (searchIn) { case "": // viene sin busqueda por el like QueryTotal = SentenciaBase + " AND 1 = 1"; break; default: // viene con el parametro para buscar por el like QueryTotal = SentenciaBase + " AND " + lowername + " LIKE " + "'%" + LowerSearch + "%'"; } switch (searchChanneltype) { case "": // viene sin busqueda por el like QueryTotal = QueryTotal + " AND 1 = 1"; break; default: // viene con el parametro para buscar por el like QueryTotal = QueryTotal + " AND idtipocanal = " + "'" + searchChanneltype + "'"; } switch (searchOfferservice) { case 0: // viene sin busqueda por el like QueryTotal = QueryTotal + " AND 1 = 1"; break; default: // viene con el parametro para buscar por el like // QueryTotal = QueryTotal + " AND idtipocanal = " + "'" + searchChanneltype + "'"; // QueryTotal = QueryTotal + " AND (serviceoffers IS NULL OR CONCAT(',', serviceoffers, ',') LIKE '%," + searchOfferservice + ",%')"; QueryTotal = QueryTotal + " AND (CONCAT(',', serviceoffers, ',') LIKE '%," + searchOfferservice + ",%')"; } // QueryTotal = QueryTotal + groupby; QueryTotal = QueryTotal; query = entityManager.createNativeQuery(QueryTotal); cuantosregistro = (long) query.getResultList().size(); String ordena=""; if (orderIn == 1 || orderIn == 2 || orderIn == 3 || orderIn == 4 || orderIn == 5 || orderIn == 6) { ordena = " ASC"; } else if (orderIn == -1 || orderIn == -2 || orderIn == -3 || orderIn == -4 || orderIn == -5 || orderIn == -6) { ordena = " DESC"; } else { String var2 = ""; boolean bloked = false; RespuestaMsgDto respuestaDto = new RespuestaMsgDto(var2); respuestaDto= new RespuestaMsgDto("Error interno del servidor"); estatus=HttpStatus.INTERNAL_SERVER_ERROR; return new ResponseEntity(respuestaDto, estatus); } int absolutoOrden = Math.abs(orderIn); switch (absolutoOrden) { case 1: //ordena por name ascendente QueryTotal = QueryTotal + " ORDER BY chname " + ordena; break; case 2://ordena por status ascendente QueryTotal = QueryTotal + " ORDER BY idtipocanal" + ordena; break; case 3: //ordena por name ascendente QueryTotal = QueryTotal + " ORDER BY rto " + ordena; break; case 4: //ordena por name ascendente QueryTotal = QueryTotal + " ORDER BY peso " + ordena; break; case 5: //ordena por name ascendente QueryTotal = QueryTotal + " ORDER BY rtoti " + ordena; //total_procesos_por_canal break; case 6: //ordena por status ascendente QueryTotal = QueryTotal + " ORDER BY total_procesos_por_canal " + ordena; break; } query = entityManager.createNativeQuery(QueryTotal); query.setFirstResult(offsetIn); query.setMaxResults(numofrecordsIn); List listacompleta = query.getResultList(); if (formatIn==2 ) { String unit ="CHANNELS"; // response.setContentType("text/csv"); response.setContentType("text/csv; charset=UTF-8"); response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"Ranking_de_Canales.csv\""); try (PrintWriter writer = response.getWriter(); // CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader( "UNIDADES", "PROCESOS" , "IM" , "AT" , "PR" , "RV" , "PC" , "IN" , "RR" ))) { //CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader("CANAL" + ";"+ "TIPO", "PESO", "TX PROMEDIO MES", "MONTO TOTAL PROMEDIO MES", "CLIENTES MENSUAL", "RTO Min", "RTO Ti", "RTO Delta","Procesos","Aplicaciones"))) { CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader("CANAL" + ";"+ "TIPO" + ";"+ "PESO" + ";"+ "TX PROMEDIO MES" + ";"+ "MONTO TOTAL PROMEDIO MES" + ";"+ "CLIENTES MENSUAL" + ";"+ "RTO Min" + ";"+ "RTO Ti" + ";"+ "RTO Delta" + ";"+"Procesos"+ ";"+"Aplicaciones"))) { if (cuantosregistro > 0) { for (Object[] filaformat2 : listacompleta) { String canal=""; String tipo=""; String peso = ""; String tx = ""; String amount = ""; String customer = ""; String rtomin = ""; String rtoti = ""; String rtodelta = ""; String procesos = ""; String apps = ""; canal = (String) filaformat2[1]; canal = canal.replace(',', ' '); //cadenaOriginal.replace(',', ' '); tipo = (String) filaformat2[2]; tipo = tipo.replace(',', ' '); peso = (String) filaformat2[9]; tx = (String) filaformat2[11]; amount = (String) filaformat2[13]; customer = (String) filaformat2[15]; //seteando el rtomin Integer rtominint = (int) filaformat2[7]; String rtominstring = rtominint.toString(); rtomin = rtominstring; //seteando el rtoti Integer rtotiint = (int) filaformat2[5]; String rtotistring = rtotiint.toString(); rtoti = rtotistring; // seteando el rtodelta BigDecimal rtodeltaint = (BigDecimal) filaformat2[26]; String rtodeltastring= rtodeltaint.toString(); rtodelta = rtodeltastring; // seteando el numero de procesos Integer procesosint; try { procesosint = (int) filaformat2[6]; } catch (Exception e) { procesosint = 0; } String procesosstring = procesosint.toString(); procesos = procesosstring; //33 //seteando el numero de apps Integer appsint; try { appsint = (int) filaformat2[33]; } catch (Exception e) { appsint = 0; } String appsstring = appsint.toString(); apps = appsstring; /// terminando de setear los valores String Imprimir = "\"" + canal+ "\"" + ";" + "\""+ tipo +"\"" + ";" + "\"" + peso + "\"" + ";" + "\"" + tx + "\"" + ";" + "\""+ amount + "\"" + ";" + "\"" + customer + "\"" + ";" + "\"" + rtomin + "\"" + ";" + "\"" + rtoti+"\"" + ";" + "\"" + rtodelta + "\"" + ";" + "\"" + procesos + "\"" + ";" + "\"" + apps + "\"" ; csvPrinter.printRecord(Imprimir); } } return new ResponseEntity(HttpStatus.OK); } } if (formatIn==1 || formatIn==0 ) { List listasSuppliers = new ArrayList<>(); detalleSuppliers = new CampaignFormat2Dto(); for (Object[] reg : listacompleta) { detalleSuppliers = new CampaignFormat2Dto(); channelsRecordDto = new ChannelsRecordDto(); //detalleUnits.setId((int) reg[0]); detalleStatus = new StatusDto2(); detalleType = new StatusDto2(); detalleAmountOP = new ChannelsAmountDto(); detallevolumeOP = new ChannelsAmountDto(); detallecapacityperiod = new StatusDto2(); detalleserviceoffers = new ServiceOffersDescDto(); detalleChannels = new OperationlineSinStatusDto(); detalleTypeCha = new OperationlineSinStatusDto(); detalleWeight = new ChannelsRankingValFormatDto(); detalleAvgtx = new ChannelsRankingValFormatDto(); detalleTotamt = new ChannelsRankingValFormatDto(); detalleCustomer = new ChannelsRankingValFormatDto(); detalleRto = new ChannelsRankingRtoDto(); detalleTypeProcesses = new IddscDto(); detalleOperLineProcesses = new IddscDto(); detalleProcesses = new ChannelsRankingProcessesDto(); detalleApplication = new ChannelsRankingApplicationsDto(); detalleTypeApp = new OperationlineSinStatusDto(); detalleDrptested = new OperationlineSinStatusDto(); detalleChannelsRankingRecordDto = new ChannelsRankingRecordDto(); //seteando informacion basica del canal detalleChannels.setId((int) reg[0]); detalleChannels.setName((String) reg[1]); //seteando id y desc del tipo de canal int idtypeCha = Integer.parseInt((String) reg[3]); detalleTypeCha.setId(idtypeCha); detalleTypeCha.setName((String) reg[2]); //seteando el peso del canal detalleWeight.setValue((BigDecimal) reg[8]); detalleWeight.setFormatted((String) reg[9]); //seteando el average de operaciones detalleAvgtx.setValue((BigDecimal) reg[10]); detalleAvgtx.setFormatted((String) reg[11]); //seteando el monto operaciones detalleTotamt.setValue((BigDecimal) reg[12]); detalleTotamt.setFormatted((String) reg[13]); //seteando los clientes detalleCustomer.setValue((BigDecimal) reg[14]); detalleCustomer.setFormatted((String) reg[15]); //seteando los rto BigDecimal rtodeltaint = (BigDecimal) reg[26]; String rtodeltastring= rtodeltaint.toString() + "%"; detalleRto.setMin((int) reg[7]); detalleRto.setTi((int) reg[5]); detalleRto.setDelta(rtodeltastring); String Idprocesos = (String) reg[24]; // AQUI VIENEN LOS ID DE LOS PROCESOS ASOCIADOS AL CHANNELS String NameProceso = (String) reg[25]; // AQUI VIENEN LOS NOMBRES DE LOS PROCESOS ASOCIADOS AL CHANNELS String Rtoprocesos = (String) reg[29]; // AQUI VIENEN LOS RTO DE LOS PROCESOS ASOCIADOS AL CHANNELS String TiposdeProcesos = (String) reg[27]; // AQUI VIENEN LOS TIPOS DE PROCESOS DE LOS PROCESOS ASOCIADOS AL CHANNELS String IdTiposdeProcesos = (String) reg[28]; // AQUI VIENEN LOS ID DE LOS TIPOS DE PROCESOS ASOCIADOS AL CHANNELS String idLineadeProcesos = (String) reg[16]; // AQUI VIENEN LOS ID DE LAS LINEAS DE OPERACION ASOCIADOS AL PROCESO String LineadeProcesos = (String) reg[17]; // AQUI VIENEN LOS NOMBRES DE LAS LINEAS DE OPERACION ASOCIADOS AL PROCESO if (Idprocesos != null && !Idprocesos.isEmpty()) { String[] IdProcesos = Idprocesos.split(";"); String[] NamePro = NameProceso.split(";"); String[] RtoPro = Rtoprocesos.split(";"); String[] TypePro = TiposdeProcesos.split(";"); String[] IdTypePro = IdTiposdeProcesos.split(";"); String[] IdLinePro = idLineadeProcesos.split(";"); String[] LinePro = LineadeProcesos.split(";"); listasProcesses = new ArrayList<>(); //List listasApplication = new ArrayList<>(); int i = 0; listasOfertas = new ArrayList<>(); for (String chaId : IdProcesos) { detalleTypeProcesses = new IddscDto(); detalleOperLineProcesses = new IddscDto(); detalleProcesses = new ChannelsRankingProcessesDto(); String NombrePro = NamePro[i].replace(";", "");; String Rtoprocesses = RtoPro[i]; String TipoPro = TypePro[i].replace(";", "");; String IdTipoPro = IdTypePro[i]; String IdLineaPro = IdLinePro[i]; String LineaPro = LinePro[i].replace(";", "");; detalleProcesses.setId(Integer.parseInt(chaId)); detalleProcesses.setName(NombrePro); detalleProcesses.setRtomin(Integer.parseInt(Rtoprocesses)); //setenado el tipo de proceso de cada uno de los procesos de la lista detalleTypeProcesses.setId(Integer.parseInt(IdTipoPro)); detalleTypeProcesses.setDsc(TipoPro); detalleProcesses.setType(detalleTypeProcesses); //setenado la linea de operaciones de cada uno de los procesos de la lista detalleOperLineProcesses.setId(Integer.parseInt(IdLineaPro)); detalleOperLineProcesses.setDsc(LineaPro); detalleProcesses.setOperationline(detalleOperLineProcesses); //lleno el listado, sí existe una lista de procesos. listasProcesses.add(detalleProcesses); i = i+1; } } else { detalleProcesses = new ChannelsRankingProcessesDto(); } String Idapps = (String) reg[18]; String NameApps = (String) reg[19]; String Rtoapps = (String) reg[31]; String TypeApps = (String) reg[20]; String IdTypesdeApps = (String) reg[21]; String drptested = (String) reg[22]; String iddrptested = (String) reg[23]; if (Idapps != null && !Idapps.isEmpty()) { String[] IdApps = Idapps.split(";"); String[] NombreApps = NameApps.split(";"); String[] RtoApps = Rtoapps.split(";"); String[] TipodeApps = TypeApps.split(";"); String[] IdTiposdeApps = IdTypesdeApps.split(";"); String[] Drptested = drptested.split(";"); String[] idDrptested = iddrptested.split(";"); listasApplication = new ArrayList<>(); int i = 0; listasOfertas = new ArrayList<>(); for (String appId : IdApps) { detalleApplication = new ChannelsRankingApplicationsDto(); detalleTypeApp = new OperationlineSinStatusDto(); detalleDrptested = new OperationlineSinStatusDto(); //TipoOf = TipoOf.replace(" ", "") String Nombapps = NombreApps[i].replace(";", ""); String Rtoapp = RtoApps[i]; String TipoApp = TipodeApps[i].replace(";", "");; String IdTipoApp = IdTiposdeApps[i]; String DrptestedApp = Drptested[i]; String idDrptestedApp = idDrptested[i]; detalleApplication.setId(Integer.parseInt(appId)); detalleApplication.setName(Nombapps); detalleApplication.setRtomin(Integer.parseInt(Rtoapp)); //setenado el tipo de Apps de cada uno de las App de la lista detalleTypeApp.setId(Integer.parseInt(IdTipoApp)); detalleTypeApp.setName(TipoApp); detalleApplication.setType(detalleTypeApp); //seteando el parametro drptested de cada uno de las aplicaciones de la lista detalleDrptested.setId(Integer.parseInt(idDrptestedApp)); detalleDrptested.setName(DrptestedApp); detalleApplication.setDrptested(detalleDrptested); //lleno el listado, sí existe una lista de aplicaciones. listasApplication.add(detalleApplication); i = i+1; } } else { detalleApplication = new ChannelsRankingApplicationsDto(); } detalleChannelsRankingRecordDto.setChannel(detalleChannels); detalleChannelsRankingRecordDto.setType(detalleTypeCha); detalleChannelsRankingRecordDto.setWeight(detalleWeight); detalleChannelsRankingRecordDto.setAvgtx(detalleAvgtx); detalleChannelsRankingRecordDto.setTotamt(detalleTotamt); detalleChannelsRankingRecordDto.setCustomerscnt(detalleCustomer); detalleChannelsRankingRecordDto.setRto(detalleRto); detalleChannelsRankingRecordDto.setProcesses(listasProcesses); detalleChannelsRankingRecordDto.setApplications(listasApplication); listasChannelsRankingRecordDto.add(detalleChannelsRankingRecordDto); // List listasChannelsRankingRecordDto = new ArrayList<>(); // ChannelsRankingRecordDto detalleChannelsRankingRecordDto; } } String SetenciaTypes=""; SetenciaTypes= "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 = 'CHANNEL_TYPE' ORDER BY valor ASC"; Query queryTypes = entityManager.createNativeQuery(SetenciaTypes); List listatipos= queryTypes.getResultList(); for (Object[] types : listatipos) { detalleTypes = new TypesUnitDto(); detalleTypes.setDsc((String) types[0]); detalleTypes.setValue((String) types[1]); listasTypes.add(detalleTypes); } channelsRankingResponseDto.setNumofrecords(cuantosregistro); channelsRankingResponseDto.setSessionvalidthru(fechaComoCadena); channelsRankingResponseDto.setRecords(listasChannelsRankingRecordDto); channelsRankingResponseDto.setChanneltype(listasTypes); return ResponseEntity.ok(channelsRankingResponseDto); }catch (Exception e) { // Manejo de excepciones System.out.println(e); respuesta= new RespuestaDto("Error interno del servidorfff", false); estatus=HttpStatus.INTERNAL_SERVER_ERROR; } finally { if (entityManager != null && entityManager.isOpen()) { entityManager.close(); } } return new ResponseEntity(respuesta, estatus); } @PostMapping("/reports/biaresumepro") public ResponseEntity reportbiaresumepro(HttpServletRequest request, @RequestBody ReportBIADto datosin) throws ParseException { /* Conversión de fecha */ SimpleDateFormat inputFormat = new SimpleDateFormat("yyyyMMdd"); //Date fecha = inputFormat.parse(datosin.getDate()); int idrol; int idcampana = datosin.getCampaignid(); int iduni = datosin.getUnitid(); int idpro = datosin.getProcessid(); String contentin = datosin.getContent(); /*********************/ RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; String sessionid = request.getHeader("Authorization"); sessionid = sessionid.substring(7); Optional encontreSessionUsuario =usersRepository.getBySessionid(sessionid); 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 { int rolisvalid = 0; Roles roles = encontreSessionUsuario.get().getRolid(); idrol = roles.getId(); rolisvalid = auditRepository.getCantbyRolAndPrivi(encontreSessionUsuario.get().getRolid().getId(), 510); if (rolisvalid == 0) { respuesta.setMsg("No tiene los Privilegios"); estatus = HttpStatus.FORBIDDEN; return new ResponseEntity(respuesta, estatus); } } if (campaignsRepository.existsById(idcampana)) { String mensaje = "campana existe"; } else { String var = ""; boolean bloked = false; RespuestaDto respuestaDto = new RespuestaDto(var, bloked); respuestaDto.setBlocked(bloked); respuestaDto.setMsg("Campana no existe"); //Error 400 return new ResponseEntity(respuestaDto, HttpStatus.BAD_REQUEST); } if (contentin !=null) { menuService.iscontentdiffnull(contentin, encontreSessionUsuario.get().getId()); } ImpactContentSection2Dto detalleContentSection2= new ImpactContentSection2Dto(); ImpactContentResponseDto detalleImpactContentResponseDto = new ImpactContentResponseDto(); ResumenBiaResponseDto detalleResumenBiaResponseDto = new ResumenBiaResponseDto(); DahboardcampaignHeadersDto detalleDahboardcampaignHeadersDto = new DahboardcampaignHeadersDto(); DahboardcampaignHeadersDto detalleDahboardcampaignFooterDto = new DahboardcampaignHeadersDto(); ResumenBiabodyDto detalleResumenBiabodyDto = new ResumenBiabodyDto(); ResumenBiaSection1 detalleResumenBiaSection1 = new ResumenBiaSection1(); ResumenBiaSection2 detalleResumenBiaSection2 = new ResumenBiaSection2(); ResumenBiaSection3 detalleResumenBiaSection3 = new ResumenBiaSection3(); ResumenBiaSection4 detalleResumenBiaSection4 = new ResumenBiaSection4(); ResumenBiaSection5 detalleResumenBiaSection5 = new ResumenBiaSection5(); ResumenBiaSection6 detalleResumenBiaSection6 = new ResumenBiaSection6(); ResumenBiaSection7 detalleResumenBiaSection7 = new ResumenBiaSection7(); ResumenBiaSection8 detalleResumenBiaSection8 = new ResumenBiaSection8(); ResumenBiaSection9 detalleResumenBiaSection9 = new ResumenBiaSection9(); detalleResumenBiaSection1 = biaresumeproSection1Service.getResumeBiaProcesos(idcampana,iduni,idpro); detalleResumenBiaSection2 = biaresumeproSection2Service.getResumeBiaImpacts(idcampana,iduni,idpro); detalleResumenBiaSection3 = biaresumeproSection3Service.getResumeBiaPeriodos(idcampana, iduni,idpro); detalleResumenBiaSection4 = biaresumeproSection4Service.getResumeBiaSuppliers(idcampana, iduni, idpro); detalleResumenBiaSection5 = biaresumeproSection5Service.getResumeBiaResources(idcampana, iduni, idpro); detalleResumenBiaSection6 = biaresumeproSection6Service.getResumeBiaPersonal(idcampana, iduni, idpro); detalleResumenBiaSection7 = biaresumeproSection7Service.getResumeBiaApp(idcampana, iduni, idpro); detalleResumenBiaSection8 = biaresumeproSection8Service.getResumeBiaRecord(idcampana, iduni, idpro); detalleResumenBiaSection9 = biaresumeproSection9Service.getResumeBiaRecordVit(idcampana, iduni, idpro); detalleResumenBiabodyDto.setSection1(detalleResumenBiaSection1); detalleResumenBiabodyDto.setSection2(detalleResumenBiaSection2); detalleResumenBiabodyDto.setSection3(detalleResumenBiaSection3); detalleResumenBiabodyDto.setSection4(detalleResumenBiaSection4); detalleResumenBiabodyDto.setSection5(detalleResumenBiaSection5); detalleResumenBiabodyDto.setSection6(detalleResumenBiaSection6); detalleResumenBiabodyDto.setSection7(detalleResumenBiaSection7); detalleResumenBiabodyDto.setSection8(detalleResumenBiaSection8); detalleResumenBiabodyDto.setSection9(detalleResumenBiaSection9); detalleDahboardcampaignHeadersDto.setTitle("Resumen BIA Proceso"); detalleDahboardcampaignFooterDto.setTitle("Duran y Asociados"); detalleResumenBiaResponseDto.setHeader(detalleDahboardcampaignHeadersDto); detalleResumenBiaResponseDto.setBody(detalleResumenBiabodyDto); detalleResumenBiaResponseDto.setFooter(detalleDahboardcampaignFooterDto); return ResponseEntity.ok(detalleResumenBiaResponseDto); } }