package com.dacrt.SBIABackend.service; import java.math.BigDecimal; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; 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.ImpactChannelresumeDto; import com.dacrt.SBIABackend.dto.ImpactContentSection5ReportDto; import com.dacrt.SBIABackend.dto.ImpactContentSection6ReportDto; import com.dacrt.SBIABackend.dto.ImpactReportSection5Dto; import com.dacrt.SBIABackend.dto.ImpactReportSection6Dto; import com.dacrt.SBIABackend.dto.ImpactResourceSection6Dto; import com.dacrt.SBIABackend.dto.ImpactStaffSection6Dto; import com.dacrt.SBIABackend.dto.ImpactStaffSections5Dto; import com.dacrt.SBIABackend.dto.ImpactTrendingReportDto; import com.dacrt.SBIABackend.dto.ImpactUnitprioritySection6Dto; import com.dacrt.SBIABackend.dto.LabelValueDtoReport; import com.dacrt.SBIABackend.dto.TypesUnitDto; import com.dacrt.SBIABackend.security.dto.RespuestaDto; @Service @Transactional public class ImpactReportSection6Service { Logger logger = LoggerFactory.getLogger(ImpactReportSection6Service.class); @PersistenceContext private EntityManager entityManager; public ImpactReportSection6Dto getImpactResumeforUnits(int idcampana,String contentin) { ImpactReportSection6Dto detalleImpactReportSection6Dto = new ImpactReportSection6Dto(); ImpactContentSection6ReportDto detalleImpactContentSection6ReportDto = new ImpactContentSection6ReportDto(); ImpactUnitprioritySection6Dto detalleImpactUnitprioritySection6Dto = new ImpactUnitprioritySection6Dto(); ImpactUnitprioritySection6Dto detalleImpactUnitprioritySection6Dto2 = new ImpactUnitprioritySection6Dto(); ImpactStaffSection6Dto detalleImpactStaffSection6Dto = new ImpactStaffSection6Dto(); ImpactResourceSection6Dto detalleImpactResourceSection6Dto = new ImpactResourceSection6Dto(); List listaPriority=new ArrayList(); List listaStaff=new ArrayList(); List listaResources=new ArrayList(); //String fecha2=fecha; Query query; Query queryStaff; Query queryRec; RespuestaDto respuesta = new RespuestaDto("", false); HttpStatus estatus = HttpStatus.FORBIDDEN; try { listaPriority=new ArrayList(); listaStaff=new ArrayList(); listaResources=new ArrayList(); String SentenciaBase = " SELECT " + " u.name AS Unidad, " + " c.id AS campana, " + " MIN(ep.rtoqty) AS rtoHoras, " + " CAST(COUNT(DISTINCT ep.evalprocessid) AS integer) AS NroProcesos, " + " CAST(SUM(DISTINCT ep.PersonasClave) AS integer) AS PersonasClave, " + " CAST(SUM(DISTINCT ep.RecursosClave) AS integer) AS RecursosClave, " + " CAST(SUM(ep.NroProveedores) AS integer) AS NroProveedores, " + " CAST(SUM(ep.RegVitales) AS integer) AS RegVitales, " + " CAST(COALESCE(strat.TotalEstrategias, 0) AS integer) AS NroEstrategias " + " FROM ( " + " SELECT " + " ep.id AS evalprocessid, " + " up.unitid, " + " cu.campaingid, " + " ei.rtoqty, " + " COALESCE(pos.TotalPersonas, 0) AS PersonasClave, " + " COALESCE(res.TotalRecursos, 0) AS RecursosClave, " + " COALESCE(sup.TotalProveedores, 0) AS NroProveedores, " + " COALESCE(rec.TotalRegistrosVitales, 0) AS RegVitales " + " FROM main.evalprocesses ep " + " INNER JOIN main.unitprocesses up ON up.id = ep.unitprocessid " + " INNER JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid " + " INNER JOIN main.campaignunits cu ON cu.id = ep.campaignunitid " + " INNER JOIN main.evalunits eu ON eu.campaingunitid = cu.id " + " LEFT JOIN ( " + " SELECT evalunitid, SUM(qtykey) AS TotalPersonas " + " FROM main.evalunitpos " + " INNER JOIN main.evalunits eu_pos ON eu_pos.id = evalunitid " + " WHERE eu_pos.positionst = 1 " + " GROUP BY evalunitid " + " ) pos ON eu.id = pos.evalunitid " + " LEFT JOIN ( " + " SELECT evalunitid, SUM(qtykey) AS TotalRecursos " + " FROM main.evalunitres " + " INNER JOIN main.evalunits eu_res ON eu_res.id = evalunitid " + " WHERE eu_res.resourcest = 1 " + " GROUP BY evalunitid " + " ) res ON eu.id = res.evalunitid " + " LEFT JOIN ( " + " SELECT ep_sup.evalprocessid, COUNT(ep_sup.id) AS TotalProveedores " + " FROM main.evalprocsupliers ep_sup " + " INNER JOIN main.evalprocesses ep_on_sup ON ep_on_sup.id = ep_sup.evalprocessid " + " WHERE ep_on_sup.suplierst = 1 " + " GROUP BY ep_sup.evalprocessid " + " ) sup ON ep.id = sup.evalprocessid " + " LEFT JOIN ( " + " SELECT ep_rec.evalprocessid, COUNT(ep_rec.id) AS TotalRegistrosVitales " + " FROM main.evalprocrecords ep_rec " + " INNER JOIN main.evalprocesses ep_on_rec ON ep_on_rec.id = ep_rec.evalprocessid " + " WHERE ep_on_rec.recordst = 1 " + " GROUP BY ep_rec.evalprocessid " + " ) rec ON ep.id = rec.evalprocessid " + " WHERE ep.impactst = 1 AND cu.campaingid = " + idcampana + " ) ep " + " INNER JOIN main.units u ON u.id = ep.unitid " + " INNER JOIN main.campaigns c ON c.id = ep.campaingid " + " LEFT JOIN ( " + " SELECT unitid, COUNT(id) AS TotalEstrategias " + " FROM main.strategiesdetunits " + " GROUP BY unitid " + " ) strat ON u.id = strat.unitid " + " GROUP BY " + " u.name, " + " c.id, " + " strat.TotalEstrategias " + " ORDER BY " + " MIN(ep.rtoqty) "; /* String SentenciaBase = " SELECT " + " u.name AS Unidad, " + " c.id AS campana, " + " MIN(ep.rtoqty) AS rtoHoras, " + " CAST(COUNT(DISTINCT ep.evalprocessid) AS integer) AS NroProcesos, " + " CAST(SUM(DISTINCT ep.PersonasClave) AS integer) AS PersonasClave, " + " CAST(SUM(DISTINCT ep.RecursosClave) AS integer) AS RecursosClave, " + " CAST(SUM(ep.NroProveedores) AS integer) AS NroProveedores, " + " CAST(SUM(ep.RegVitales) AS integer) AS RegVitales, " + " CAST(COALESCE(strat.TotalEstrategias, 0) AS integer) AS NroEstrategias " + "FROM ( " + " SELECT " + " ep.id AS evalprocessid, " + " up.unitid, " + " cu.campaingid, " + " ei.rtoqty, " + " COALESCE(pos.TotalPersonas, 0) AS PersonasClave, " + " COALESCE(res.TotalRecursos, 0) AS RecursosClave, " + " COALESCE(sup.TotalProveedores, 0) AS NroProveedores, " + " COALESCE(rec.TotalRegistrosVitales, 0) AS RegVitales " + " FROM main.evalprocesses ep " + " INNER JOIN main.unitprocesses up ON up.id = ep.unitprocessid " + " INNER JOIN main.evalprocimpacts ei ON ep.id = ei.evalprocessid " + " INNER JOIN main.campaignunits cu ON cu.id = ep.campaignunitid " + " INNER JOIN main.evalunits eu ON eu.campaingunitid = cu.id " + " LEFT JOIN ( " + " SELECT evalunitid, SUM(qtykey) AS TotalPersonas " + " FROM main.evalunitpos " + " INNER JOIN main.evalunits eu_pos ON eu_pos.id = evalunitid " + " WHERE eu_pos.positionst = 1 " + " GROUP BY evalunitid " + " ) pos ON eu.id = pos.evalunitid " + " LEFT JOIN ( " + " SELECT evalunitid, SUM(qtykey) AS TotalRecursos " + " FROM main.evalunitres " + " INNER JOIN main.evalunits eu_res ON eu_res.id = evalunitid " + " WHERE eu_res.resourcest = 1 " + " GROUP BY evalunitid " + " ) res ON eu.id = res.evalunitid " + " LEFT JOIN ( " + " SELECT evalprocessid, COUNT(id) AS TotalProveedores FROM main.evalprocsupliers GROUP BY evalprocessid " + " ) sup ON ep.id = sup.evalprocessid " + " LEFT JOIN ( " + " SELECT evalprocessid, COUNT(id) AS TotalRegistrosVitales FROM main.evalprocrecords GROUP BY evalprocessid " + " ) rec ON ep.id = rec.evalprocessid " + " WHERE ep.impactst = 1 AND cu.campaingid = " + idcampana + " ) ep " + " INNER JOIN main.units u ON u.id = ep.unitid " + " INNER JOIN main.campaigns c ON c.id = ep.campaingid " + " LEFT JOIN ( " + " SELECT unitid, COUNT(id) AS TotalEstrategias " + " FROM main.strategiesdetunits " + " GROUP BY unitid " + " ) strat ON u.id = strat.unitid " + " GROUP BY " + " u.name, " + " c.id, " + " strat.TotalEstrategias " + " ORDER BY " + " MIN(ep.rtoqty) " ;*/ query = entityManager.createNativeQuery(SentenciaBase); long cuantosregistro = (long) query.getResultList().size(); List listacompleta = query.getResultList(); for (Object[] fila : listacompleta) { detalleImpactUnitprioritySection6Dto = new ImpactUnitprioritySection6Dto(); detalleImpactUnitprioritySection6Dto.setUnit((String) fila[0]); detalleImpactUnitprioritySection6Dto.setPersons((int) fila[4]); detalleImpactUnitprioritySection6Dto.setProcesses((int) fila[3]); detalleImpactUnitprioritySection6Dto.setResources((int) fila[5]); detalleImpactUnitprioritySection6Dto.setSuppliers((int) fila[6]); Integer RtoInteger = (Integer) fila[2]; String RtoString = RtoInteger.toString() + " min"; /*[{"value":"1","filter":"240","dsc":"#FE861A"}, {"value":"2","filter":"1440","dsc":"#FFC100"}, {"value":"3","filter":"2880","dsc":"#9ACCFF"}, {"value":"4","filter":"9999","dsc":"#74B943"} ]*/ detalleImpactUnitprioritySection6Dto.setRto(RtoString); int franja1=0; int franja2=0; int franja3=0; int franja4=0; String color1 = ""; String color2= ""; String color3 = ""; String color4 = ""; String SetenciaRTOReports= " SELECT " +" MAX(CASE WHEN t.ordinality = 1 THEN elemento ->> 'dsc' END) AS descr1, " +" MAX(CASE WHEN t.ordinality = 1 THEN CAST(elemento ->> 'filter' AS integer) END) AS valor1, " +" MAX(CASE WHEN t.ordinality = 2 THEN elemento ->> 'dsc' END) AS descr2, " +" MAX(CASE WHEN t.ordinality = 2 THEN CAST(elemento ->> 'filter' AS integer) END) AS valor2, " +" MAX(CASE WHEN t.ordinality = 3 THEN elemento ->> 'dsc' END) AS descr3, " +" MAX(CASE WHEN t.ordinality = 3 THEN CAST(elemento ->> 'filter' AS integer) END) AS valor3, " +" MAX(CASE WHEN t.ordinality = 4 THEN elemento ->> 'dsc' END) AS descr4, " +" MAX(CASE WHEN t.ordinality = 4 THEN CAST(elemento ->> 'filter' AS integer) END) AS valor4 " +" FROM " +" main.params p, " +" jsonb_array_elements(CAST(p.value AS jsonb)) WITH ORDINALITY AS t(elemento, ordinality) " +" WHERE " +" p.paramname = 'REPORTS_RTOTEMP' "; Query queryRtoReports = entityManager.createNativeQuery(SetenciaRTOReports); List listaRtoReports= queryRtoReports.getResultList(); for (Object[] types : listaRtoReports) { color1= (String) types[0]; color2= (String) types[2]; color3= (String) types[4]; color4= (String) types[6]; franja1=(int) types[1]; franja2=(int) types[3]; franja3=(int) types[5]; franja4=(int) types[7]; } if ((int) fila[2]<=franja1) { //240 detalleImpactUnitprioritySection6Dto.setTemperature(color1); } int cota1= franja1+1; if ((int) fila[2]>=cota1 && (int) fila[2]<=franja2) { //241 1440 detalleImpactUnitprioritySection6Dto.setTemperature(color2); } int cota2= franja2+1; if ((int) fila[2]>=cota2 && (int) fila[2]<=franja3) { //1441 2880 detalleImpactUnitprioritySection6Dto.setTemperature(color3); } if ((int) fila[2]>franja3) { // 2880 detalleImpactUnitprioritySection6Dto.setTemperature(color4); } //detalleImpactUnitprioritySection6Dto.setTemperature(1); detalleImpactUnitprioritySection6Dto.setVitalregs((int) fila[7]); detalleImpactUnitprioritySection6Dto.setStrategies((int) fila[8]); listaPriority.add(detalleImpactUnitprioritySection6Dto); } String SentenciaBaseStaff = " SELECT " + " u.name AS Unidad, " + " c.id AS campana, " + " cast(COALESCE(pos.qtytotal, 0) as integer) AS StaffTotal, " + " cast(COALESCE(pos.qtyrange1, 0) as integer) AS Dia1, " + " cast(COALESCE(pos.qtyrange2, 0) as integer) AS Dia2, " + " cast(COALESCE(pos.qtyrange3, 0) as integer) AS Dia3, " + " cast(COALESCE(pos.qtyrange4, 0) as integer) AS Dia4, " + " cast(COALESCE(pos.qtyrange5, 0) as integer) AS Dia5, " + " cast(COALESCE(pos.qtyrange6, 0) as integer) AS Dia6, " + " cast(COALESCE(pos.qtyrange7, 0) as integer) AS Dia7, " + " cast(COALESCE(pos.qtyrange8, 0) as integer) AS Dia8, " + " cast(COALESCE(pos.qtyrange9, 0) as integer) AS Dia9, " + " cast(COALESCE(pos.qtyrange10, 0) as integer) AS Dia10, " + " cast(COALESCE(pos.qtyrange11, 0) as integer) AS Dia11 " + " FROM " + " main.evalunits eu " + " INNER JOIN " + " main.campaignunits cu ON cu.id = eu.campaingunitid " + " INNER JOIN " + " main.units u ON u.id = cu.unitid " + " INNER JOIN " + " main.campaigns c ON cu.campaingid = c.id " + " LEFT JOIN ( " + " SELECT " + " evalunitid, " + " SUM(qtytotal) AS qtytotal, " + " SUM(qtyrange1) AS qtyrange1, " + " SUM(qtyrange2) AS qtyrange2, " + " SUM(qtyrange3) AS qtyrange3, " + " SUM(qtyrange4) AS qtyrange4, " + " SUM(qtyrange5) AS qtyrange5, " + " SUM(qtyrange6) AS qtyrange6, " + " SUM(qtyrange7) AS qtyrange7, " + " SUM(qtyrange8) AS qtyrange8, " + " SUM(qtyrange9) AS qtyrange9, " + " SUM(qtyrange10) AS qtyrange10, " + " SUM(qtyrange11) AS qtyrange11 " + " FROM " + " main.evalunitpos " + " GROUP BY " + " evalunitid " + " ) pos ON eu.id = pos.evalunitid " + " WHERE " + " cu.campaingid = " + idcampana + " AND eu.positionst = 1 " + " GROUP BY " + " u.name, c.id, pos.qtytotal, pos.qtyrange1, pos.qtyrange2, pos.qtyrange3, " + " pos.qtyrange4, pos.qtyrange5, pos.qtyrange6, pos.qtyrange7, " + " pos.qtyrange8, pos.qtyrange9, pos.qtyrange10, pos.qtyrange11 " + " ORDER BY " + " u.name " ; queryStaff = entityManager.createNativeQuery(SentenciaBaseStaff); long cuantosregistroStaff = (long) queryStaff.getResultList().size(); List listacompletaStaff = queryStaff.getResultList(); for (Object[] filaStaff : listacompletaStaff) { detalleImpactStaffSection6Dto = new ImpactStaffSection6Dto(); detalleImpactStaffSection6Dto.setUnit((String) filaStaff[0]); detalleImpactStaffSection6Dto.setNormal((int) filaStaff[2]); detalleImpactStaffSection6Dto.setR0_2H((int) filaStaff[3]); detalleImpactStaffSection6Dto.setR2_4H((int) filaStaff[4]); detalleImpactStaffSection6Dto.setR4_8H((int) filaStaff[5]); detalleImpactStaffSection6Dto.setR8_12H((int) filaStaff[6]); detalleImpactStaffSection6Dto.setDay1((int) filaStaff[7]); detalleImpactStaffSection6Dto.setDay2((int) filaStaff[8]); detalleImpactStaffSection6Dto.setDay3((int) filaStaff[9]); detalleImpactStaffSection6Dto.setDay4((int) filaStaff[10]); detalleImpactStaffSection6Dto.setDay5((int) filaStaff[11]); detalleImpactStaffSection6Dto.setDay6((int) filaStaff[12]); detalleImpactStaffSection6Dto.setDay7plus((int) filaStaff[13]); listaStaff.add(detalleImpactStaffSection6Dto); } String SentenciaBaseRes= " SELECT " + " u.name AS Unidad, " + " u.id AS idresources, " + " c.id AS campana, " + " CAST(SUM(COALESCE(pos.qtytotal, 0)) AS integer) AS StaffTotal, " + " CAST(SUM(COALESCE(pos.qtyrange1, 0)) AS integer) AS Dia1, " + " CAST(SUM(COALESCE(pos.qtyrange2, 0)) AS integer) AS Dia2, " + " CAST(SUM(COALESCE(pos.qtyrange3, 0)) AS integer) AS Dia3, " + " CAST(SUM(COALESCE(pos.qtyrange4, 0)) AS integer) AS Dia4, " + " CAST(SUM(COALESCE(pos.qtyrange5, 0)) AS integer) AS Dia5, " + " CAST(SUM(COALESCE(pos.qtyrange6, 0)) AS integer) AS Dia6, " + " CAST(SUM(COALESCE(pos.qtyrange7, 0)) AS integer) AS Dia7, " + " CAST(SUM(COALESCE(pos.qtyrange8, 0)) AS integer) AS Dia8, " + " CAST(SUM(COALESCE(pos.qtyrange9, 0)) AS integer) AS Dia9, " + " CAST(SUM(COALESCE(pos.qtyrange10, 0)) AS integer) AS Dia10, " + " CAST(SUM(COALESCE(pos.qtyrange11, 0)) AS integer) AS Dia11 " + " FROM " + " main.evalunits eu " + " INNER JOIN " + " main.campaignunits cu ON cu.id = eu.campaingunitid " + " INNER JOIN " + " main.campaigns c ON cu.campaingid = c.id " + " LEFT JOIN ( " + " SELECT " + " evalunitid, " + " resourceid, " + " SUM(qtytotal) AS qtytotal, " + " SUM(qtyrange1) AS qtyrange1, " + " SUM(qtyrange2) AS qtyrange2, " + " SUM(qtyrange3) AS qtyrange3, " + " SUM(qtyrange4) AS qtyrange4, " + " SUM(qtyrange5) AS qtyrange5, " + " SUM(qtyrange6) AS qtyrange6, " + " SUM(qtyrange7) AS qtyrange7, " + " SUM(qtyrange8) AS qtyrange8, " + " SUM(qtyrange9) AS qtyrange9, " + " SUM(qtyrange10) AS qtyrange10, " + " SUM(qtyrange11) AS qtyrange11 " + " FROM " + " main.evalunitres " + " INNER JOIN main.evalunits eu_res ON eu_res.id = evalunitid " + " WHERE eu_res.resourcest = 1 " + " GROUP BY " + " evalunitid, resourceid " + " ) pos ON eu.id = pos.evalunitid " + " INNER JOIN " + " main.resources u ON u.id = pos.resourceid " + " WHERE " + " cu.campaingid = " + idcampana + " GROUP BY " + " u.name, " + " u.id, " + " c.id " + " ORDER BY " + " u.name " ; queryRec = entityManager.createNativeQuery(SentenciaBaseRes); long cuantosregistroRec = (long) queryRec.getResultList().size(); List listacompletaRec = queryRec.getResultList(); for (Object[] filaRec : listacompletaRec) { detalleImpactResourceSection6Dto = new ImpactResourceSection6Dto(); detalleImpactResourceSection6Dto.setResource((String) filaRec[0]); detalleImpactResourceSection6Dto.setNormal((int) filaRec[3]); detalleImpactResourceSection6Dto.setR0_2H((int) filaRec[4]); detalleImpactResourceSection6Dto.setR2_4H((int) filaRec[5]); detalleImpactResourceSection6Dto.setR4_8H((int) filaRec[6]); detalleImpactResourceSection6Dto.setR8_12H((int) filaRec[7]); detalleImpactResourceSection6Dto.setDay1((int) filaRec[8]); detalleImpactResourceSection6Dto.setDay2((int) filaRec[9]); detalleImpactResourceSection6Dto.setDay3((int) filaRec[10]); detalleImpactResourceSection6Dto.setDay4((int) filaRec[11]); detalleImpactResourceSection6Dto.setDay5((int) filaRec[12]); detalleImpactResourceSection6Dto.setDay6((int) filaRec[13]); detalleImpactResourceSection6Dto.setDay7plus((int) filaRec[14]); listaResources.add(detalleImpactResourceSection6Dto); } detalleImpactContentSection6ReportDto.setResources(listaResources); detalleImpactContentSection6ReportDto.setStaff(listaStaff); detalleImpactContentSection6ReportDto.setUnitpriority(listaPriority); detalleImpactReportSection6Dto.setContent(detalleImpactContentSection6ReportDto); detalleImpactReportSection6Dto.setTitle("Anexo Resumen por Unidad"); return detalleImpactReportSection6Dto; } catch (Exception e) { // Manejo de excepciones respuesta = new RespuestaDto("Error interno del servidor", false); estatus = HttpStatus.INTERNAL_SERVER_ERROR; return detalleImpactReportSection6Dto; } finally { if (entityManager != null && entityManager.isOpen()) { entityManager.close(); } } } }