package com.dacrt.SBIABackend.service; import java.math.BigDecimal; import java.math.BigInteger; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.NoResultException; import javax.persistence.PersistenceContext; import javax.persistence.PersistenceException; import javax.persistence.Query; import javax.transaction.Transactional; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.HttpStatus; import org.springframework.stereotype.Service; import com.dacrt.SBIABackend.dto.ImpactAppsresumeDto; import com.dacrt.SBIABackend.dto.ImpactContentAppReportDto; import com.dacrt.SBIABackend.dto.ImpactContentSection5ReportDto; import com.dacrt.SBIABackend.dto.ImpactReportScoperesumeDto; import com.dacrt.SBIABackend.dto.ImpactReportSection4Dto; import com.dacrt.SBIABackend.dto.ImpactReportSection5Dto; import com.dacrt.SBIABackend.dto.ImpactStaffSections5Dto; import com.dacrt.SBIABackend.dto.ImpactTrendingReportDto; import com.dacrt.SBIABackend.dto.LabelValueDtoReport; import com.dacrt.SBIABackend.security.dto.RespuestaDto; @Service @Transactional public class ImpactReportSection5Service { Logger logger = LoggerFactory.getLogger(ImpactReportSection5Service.class); @PersistenceContext private EntityManager entityManager; public ImpactReportSection5Dto getImpactCondPersonalResourcesKey(int idcampana,String contentin) { List listaImpactAppsresumeDto=new ArrayList(); ImpactReportSection5Dto detalleImpactReportSection5Dto = new ImpactReportSection5Dto(); ImpactContentSection5ReportDto detalleImpactContentSection5ReportDto= new ImpactContentSection5ReportDto(); ImpactStaffSections5Dto detalleImpactStaffSections5Dtostaff = new ImpactStaffSections5Dto(); ImpactStaffSections5Dto detalleImpactStaffSections5Dtostaff2 = new ImpactStaffSections5Dto(); ImpactStaffSections5Dto detalleImpactDto2Resources = new ImpactStaffSections5Dto(); ImpactStaffSections5Dto detalleImpactDto2Resources2 = new ImpactStaffSections5Dto(); ImpactTrendingReportDto detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); ImpactTrendingReportDto detalleImpactTrendingReportDto2= new ImpactTrendingReportDto(); LabelValueDtoReport detalleLabelValueDtoReport = new LabelValueDtoReport(); LabelValueDtoReport detalleLabelValueDtoReport2 = new LabelValueDtoReport(); LabelValueDtoReport detalleLabelValueDtoReportRes = new LabelValueDtoReport(); LabelValueDtoReport detalleLabelValueDtoReportRes2 = new LabelValueDtoReport(); List ListLabelValueDtoReport2=new ArrayList(); List ListLabelValueDtoReportResources=new ArrayList(); List ListImpactTrendingReportDto=new ArrayList(); //String fecha2=fecha; Query query; RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; long cuantosregistro; try { try { String SentenciaBase = "SELECT " + " '0-2 Hrs' AS name1, " + " COALESCE(SUM(ep.qtyrange1), 0) AS qtyrange1_sum, " + " COALESCE((SUM(ep.qtyrange1) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange1_percentage, " + " '2-4 Hrs' AS name2, " + " COALESCE(SUM(ep.qtyrange2), 0) AS qtyrange2_sum, " + " COALESCE((SUM(ep.qtyrange2) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange2_percentage, " + " '4-8 Hrs' AS name3, " + " COALESCE(SUM(ep.qtyrange3), 0) AS qtyrange3_sum, " + " COALESCE((SUM(ep.qtyrange3) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange3_percentage, " + " '8-12 Hrs' AS name4, " + " COALESCE(SUM(ep.qtyrange4), 0) AS qtyrange4_sum, " + " COALESCE((SUM(ep.qtyrange4) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange4_percentage, " + " 'Día 1' AS name5, " + " COALESCE(SUM(ep.qtyrange5), 0) AS qtyrange5_sum, " + " COALESCE((SUM(ep.qtyrange5) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange5_percentage, " + " 'Día 2' AS name6, " + " COALESCE(SUM(ep.qtyrange6), 0) AS qtyrange6_sum, " + " COALESCE((SUM(ep.qtyrange6) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange6_percentage, " + " 'Día 3' AS name7, " + " COALESCE(SUM(ep.qtyrange7), 0) AS qtyrange7_sum, " + " COALESCE((SUM(ep.qtyrange7) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange7_percentage, " + " 'Día 4' AS name8, " + " COALESCE(SUM(ep.qtyrange8), 0) AS qtyrange8_sum, " + " COALESCE((SUM(ep.qtyrange8) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange8_percentage, " + " 'Día 5' AS name9, " + " COALESCE(SUM(ep.qtyrange9), 0) AS qtyrange9_sum, " + " COALESCE((SUM(ep.qtyrange9) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange9_percentage, " + " 'Día 6' AS name10, " + " COALESCE(SUM(ep.qtyrange10), 0) AS qtyrange10_sum, " + " COALESCE((SUM(ep.qtyrange10) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange10_percentage, " + " 'Día 7' AS name11, " + " COALESCE(SUM(ep.qtyrange11), 0) AS qtyrange11_sum, " + " COALESCE((SUM(ep.qtyrange11) * 100.0) / NULLIF(SUM(ep.qtytotal), 0), 0) AS qtyrange11_percentage " + "FROM main.evalunits eu " + "INNER JOIN main.evalunitpos ep ON eu.id = ep.evalunitid AND eu.positionst = 1 " + "INNER JOIN main.campaignunits cu ON cu.id = eu.campaingunitid " + "INNER JOIN main.campaigns c ON c.id = cu.campaingid AND c.id = :idcampana"; query = entityManager.createNativeQuery(SentenciaBase); query.setParameter("idcampana", idcampana); //List listacompleta = query.getResultList(); Object[] fila = (Object[]) query.getSingleResult(); detalleImpactTrendingReportDto.setLabel((String) fila[0]); BigDecimal bigDecimaltrendig = (BigDecimal) fila[2]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[1]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[3]); bigDecimaltrendig = (BigDecimal) fila[5]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[4]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[6]); bigDecimaltrendig = (BigDecimal) fila[8]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[7]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[9]); bigDecimaltrendig = (BigDecimal) fila[11]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[10]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[12]); bigDecimaltrendig = (BigDecimal) fila[14]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[13]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[15]); bigDecimaltrendig = (BigDecimal) fila[17]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[16]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[18]); bigDecimaltrendig = (BigDecimal) fila[20]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[19]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[21]); bigDecimaltrendig = (BigDecimal) fila[23]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[22]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[24]); bigDecimaltrendig = (BigDecimal) fila[26]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[25]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[27]); bigDecimaltrendig = (BigDecimal) fila[29]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[28]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); detalleImpactTrendingReportDto = new ImpactTrendingReportDto(); detalleImpactTrendingReportDto.setLabel((String) fila[30]); bigDecimaltrendig = (BigDecimal) fila[32]; detalleImpactTrendingReportDto.setPercent(bigDecimaltrendig); detalleImpactTrendingReportDto.setValue((BigInteger)fila[31]); ListImpactTrendingReportDto.add(detalleImpactTrendingReportDto); // Tu lógica para procesar 'fila' } catch (NoResultException e) { // Escenario esperado si no hay datos para la campaña respuesta = new RespuestaDto("Error interno del servidor "+e.getMessage(), false); estatus = HttpStatus.INTERNAL_SERVER_ERROR; return detalleImpactReportSection5Dto; } catch (PersistenceException e) { // Si hay otro error de base de datos (por ejemplo, un typo), este catch lo atrapará System.err.println("Error de persistencia: " + e.getMessage()); // Re-lanza la excepción o maneja el error de forma apropiada para que Spring pueda hacer el rollback y tú puedas ver la causa raíz throw e; } String SentenciaBase2="WITH RankedUnits AS ( " + " SELECT " + " u.name AS unit_name, " + " SUM(eur.qtykey) AS key_personnel_count " + " FROM " + " main.units u " + " INNER JOIN " + " main.campaignunits cu ON u.id = cu.unitid " + " INNER JOIN " + " main.evalunits eu ON cu.id = eu.campaingunitid AND eu.positionst = 1" + " INNER JOIN " + " main.evalunitpos eur ON eu.id = eur.evalunitid " + " WHERE " + " cu.campaingid = :idcampana " + " GROUP BY " + " u.name " + " HAVING SUM(eur.qtykey) > 0 " + "), " + "FinalRanks AS ( " + " SELECT " + " unit_name, " + " key_personnel_count, " + " ROW_NUMBER() OVER (ORDER BY key_personnel_count DESC) AS rn " + " FROM " + " RankedUnits " + "), " + "GroupedResults AS ( " + " SELECT " + " CASE " + " WHEN fr.rn <= 5 THEN fr.unit_name " + " ELSE 'Unidades Adicionales' " + " END AS group_name, " + " fr.key_personnel_count, " + " fr.rn " + " FROM " + " FinalRanks fr " + ") " + "SELECT " + " CASE " + " WHEN gr.group_name != 'Unidades Adicionales' THEN gr.group_name " + " ELSE 'Más ' || CAST(COUNT(gr.rn) AS VARCHAR) || ' Unidades Adicionales' " + " END AS unit, " + " SUM(gr.key_personnel_count) AS total_key_personnel " + "FROM " + " GroupedResults gr " + "GROUP BY " + " gr.group_name " + "ORDER BY " + " CASE " + " WHEN gr.group_name = 'Unidades Adicionales' THEN -1 " + " ELSE SUM(gr.key_personnel_count) " + " END DESC"; query = entityManager.createNativeQuery(SentenciaBase2); query.setParameter("idcampana", idcampana); cuantosregistro = (long) query.getResultList().size(); List listacompleta2 = query.getResultList(); List units = new ArrayList(); String nombre=""; for (Object[] fila : listacompleta2) { nombre=(String)fila[0]; units.add(nombre); } /*List units = Arrays.asList( "Préstamos", "Créditos Corporativos - Zona Libre", "Gerencia Regional de Segmentos Especializados (A)" );*/ String SentenciaBase5="SELECT SUM(eur.qtytotal) AS cantidad_plantilla, " + " SUM(eur.qtykey) AS cantidad_clave, " + " round(CAST(SUM(eur.qtykey) AS NUMERIC) / CAST(SUM(eur.qtytotal) AS NUMERIC),2) *100 AS porcentaje_clave, " + " 100 - (round(CAST(SUM(eur.qtykey) AS NUMERIC) / CAST(SUM(eur.qtytotal) AS NUMERIC),2) *100) AS porcentaje_total " + " FROM " + "main.evalunits eu " + "INNER JOIN " + "main.evalunitpos eur ON eu.id = eur.evalunitid AND eu.positionst = 1 " + " INNER JOIN " + "main.campaignunits cu1 ON cu1.id = eu.campaingunitid " + "WHERE " + " cu1.campaingid = :idcampana "; query = entityManager.createNativeQuery(SentenciaBase5); query.setParameter("idcampana", idcampana); cuantosregistro = (long) query.getResultList().size(); Object[] fila = (Object[]) query.getSingleResult(); java.math.BigDecimal valor1 = (java.math.BigDecimal)fila[2]; java.math.BigDecimal valor2 = (java.math.BigDecimal)fila[3]; if (valor1==null) valor1=new BigDecimal(0); if (valor2==null) valor2=new BigDecimal(0); detalleLabelValueDtoReportRes.setLabel("Total Plantilla"); detalleLabelValueDtoReportRes.setValue(valor1); detalleLabelValueDtoReportRes2.setLabel("Total Cargos Clave"); detalleLabelValueDtoReportRes2.setValue(valor2); ListLabelValueDtoReport2.add(detalleLabelValueDtoReportRes); ListLabelValueDtoReport2.add(detalleLabelValueDtoReportRes2); detalleImpactStaffSections5Dtostaff.setChart(ListLabelValueDtoReport2); detalleImpactStaffSections5Dtostaff.setUnits(units); String SentenciaBase3="WITH RankedUnits AS ( " + " SELECT " + " u.name AS unit_name, " + " SUM(eur.qtykey) AS key_personnel_count " + " FROM " + " main.units u " + " INNER JOIN " + " main.campaignunits cu ON u.id = cu.unitid " + " INNER JOIN " + " main.evalunits eu ON cu.id = eu.campaingunitid AND eu.resourcest = 1" + " INNER JOIN " + " main.evalunitres eur ON eu.id = eur.evalunitid " + " WHERE " + " cu.campaingid = :idcampana " + " GROUP BY " + " u.name " + " HAVING SUM(eur.qtykey) > 0 " + "), " + "FinalRanks AS ( " + " SELECT " + " unit_name, " + " key_personnel_count, " + " ROW_NUMBER() OVER (ORDER BY key_personnel_count DESC) AS rn " + " FROM " + " RankedUnits " + "), " + "GroupedResults AS ( " + " SELECT " + " CASE " + " WHEN fr.rn <= 5 THEN fr.unit_name " + " ELSE 'Unidades Adicionales' " + " END AS group_name, " + " fr.key_personnel_count, " + " fr.rn " + " FROM " + " FinalRanks fr " + ") " + "SELECT " + " CASE " + " WHEN gr.group_name != 'Unidades Adicionales' THEN gr.group_name " + " ELSE 'Más ' || CAST(COUNT(gr.rn) AS VARCHAR) || ' Unidades Adicionales' " + " END AS unit, " + " SUM(gr.key_personnel_count) AS total_key_personnel " + "FROM " + " GroupedResults gr " + "GROUP BY " + " gr.group_name " + "ORDER BY " + " CASE " + " WHEN gr.group_name = 'Unidades Adicionales' THEN -1 " + " ELSE SUM(gr.key_personnel_count) " + " END DESC"; query = entityManager.createNativeQuery(SentenciaBase3); query.setParameter("idcampana", idcampana); cuantosregistro = (long) query.getResultList().size(); List listacompleta3 = query.getResultList(); List units2 = new ArrayList(); String nombre2=""; for (Object[] fila2 : listacompleta3) { nombre2=(String)fila2[0]; units2.add(nombre2); } ///////seteando los valores del resources///////// /*query = entityManager.createNativeQuery(SentenciaBase2); query.setParameter("idcampana", idcampana); cuantosregistro = (long) query.getResultList().size(); List listacompleta4 = query.getResultList(); for (Object[] fila : listacompleta4) { detalleLabelValueDtoReportRes.setLabel ("Total BBC"); detalleLabelValueDtoReportRes.setValue(25); ListLabelValueDtoReportResources.add(detalleLabelValueDtoReportRes); }*/ String SentenciaBase4="SELECT SUM(eur.qtytotal) AS cantidad_plantilla, " + " SUM(eur.qtykey) AS cantidad_clave, " + " round(CAST(SUM(eur.qtykey) AS NUMERIC) / CAST(SUM(eur.qtytotal) AS NUMERIC),2) *100 AS porcentaje_clave, " + " 100 - (round(CAST(SUM(eur.qtykey) AS NUMERIC) / CAST(SUM(eur.qtytotal) AS NUMERIC),2) *100) AS porcentaje_total " + " FROM " + " main.evalunits eu " + " INNER JOIN " + " main.evalunitres eur ON eu.id = eur.evalunitid AND eu.resourcest = 1 " + " INNER JOIN " + " main.campaignunits cu1 ON cu1.id = eu.campaingunitid " + " WHERE " + " cu1.campaingid = :idcampana "; query = entityManager.createNativeQuery(SentenciaBase4); query.setParameter("idcampana", idcampana); cuantosregistro = (long) query.getResultList().size(); Object[] fila3 = (Object[]) query.getSingleResult(); valor1 = (BigDecimal)fila3[2]; valor2 = (BigDecimal)fila3[3]; if (valor1==null) valor1=new BigDecimal(0); if (valor2==null) valor2=new BigDecimal(0); detalleLabelValueDtoReportRes = new LabelValueDtoReport(); detalleLabelValueDtoReportRes2 = new LabelValueDtoReport(); detalleLabelValueDtoReportRes.setLabel("Total General"); detalleLabelValueDtoReportRes.setValue(valor1); detalleLabelValueDtoReportRes2.setLabel("Total Recursos Clave"); detalleLabelValueDtoReportRes2.setValue(valor2); ListLabelValueDtoReportResources.add(detalleLabelValueDtoReportRes); ListLabelValueDtoReportResources.add(detalleLabelValueDtoReportRes2); /*List units2 = Arrays.asList( "Préstamos2", "Créditos Corporativos - Zona Libre2", "Gerencia Regional de Segmentos Especializados2 (A)" );*/ detalleImpactDto2Resources.setChart(ListLabelValueDtoReportResources); detalleImpactDto2Resources.setUnits(units2); ////////////////////////////////////////////////// detalleImpactContentSection5ReportDto.setResources(detalleImpactDto2Resources); detalleImpactContentSection5ReportDto.setStaff(detalleImpactStaffSections5Dtostaff); detalleImpactContentSection5ReportDto.setTrending(ListImpactTrendingReportDto); detalleImpactReportSection5Dto.setContent(detalleImpactContentSection5ReportDto); detalleImpactReportSection5Dto.setTitle("Condición de personal y recursos claves"); return detalleImpactReportSection5Dto; } catch (Exception e) { // Manejo de excepciones respuesta = new RespuestaDto("Error interno del servidor "+e.getMessage(), false); estatus = HttpStatus.INTERNAL_SERVER_ERROR; return detalleImpactReportSection5Dto; } finally { if (entityManager != null && entityManager.isOpen()) { entityManager.close(); } } } }