20120828_332789

-- -------------------------------------------
-- query
-- -------------------------------------------
SELECT
  T151.C1         NUMEROLLAMADA,
  TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T151.C3/86400),'dd/mm/yyyy hh24:mi:ss') FECHA_HORALLAMADA,
  T151.C13        PRIORIDAD, -- esto es un número
  T151.C7         ESTADO, -- esto es un número
  TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T151.C536870953/86400),'dd/mm/yyyy hh24:mi:ss') FECHAHORAENTRADA,
  TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T151.C536870950/86400),'dd/mm/yyyy hh24:mi:ss') FECHACIERRE,
  T151.C536870929 GRUPOSOLUCION,
  ROUND(calcula_horas_servicio(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T151.C536870953/86400),
  TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T151.C536870950/86400),
  TRUNC(HESTADOS.TIEMPOTOTAL/24)),2) TIEMPOTOTAL,
  ROUND(HESTADOS.TIEMPOIMPLANTADOR,2) TIEMPOIMPLANTADOR,
  ROUND(HESTADOS.TIEMPOCAU,2) TIEMPOCAU,
  ROUND(HESTADOS.TIEMPOUSUARIO,2) TIEMPOUSUARIO,
  TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T151.C6/86400),'dd/mm/yyyy hh24:mi:ss') FECHAMODIFICACION,
  T151.C536870941 GRUPO,
  T151.C536870925 CONSEJERIA,
  T151.C8         CENTRO,
  T151.C536870915 DEPARTAMENTO,
  T151.C536870913 AREA,
  T151.C536870931 A1_NIVEL_PRODUCTO,
  T151.C536870932 A2_NIVEL_PRODUCTO,
  T151.C536870933 A3_NIVEL_PRODUCTO,
  --T151.C536870934 A4_NIVEL_PRODUCTO,
  T151.C536871143 A1_NIVEL_OPERACIONAL,
  T151.C536871145 A2_NIVEL_OPERACIONAL,
  T151.C536871099 A3_NIVEL_OPERACIONAL,
  HESTADOS.NUMSEGUIMIENTOS,
  -- QUEJA
  T151.C536871008 NREAPERTURAS,
  -- RECLAMACIÓN
  -- RESPUESTA
  T151.C536871020 N__SOLUCIONES_PARCIALES_
  -- SECUNDARIA
  -- RECLAMACIÓN

FROM
  aradmin.t151 T151,
  (SELECT
               T174.C8 CDINCIDENCIA,
               COUNT(*) NUMSEGUIMIENTOS,
               ROUND(SUM (C536870920)/3600,2) TIEMPOTOTAL,
               calcula_horas_grupo(C8,'DAMADI%',1) TIEMPOIMPLANTADOR,
               calcula_horas_grupo(C8,'%DASC%',1) TIEMPOCAU,
               calcula_horas_grupo(C8,'',0) TIEMPOUSUARIO
               FROM
               aradmin.t174
               GROUP BY T174.C8) HESTADOS

WHERE
 T151.C1 LIKE  '08-16095%'
 AND
 HESTADOS.CDINCIDENCIA =  T151.C1;
/

-- -------------------------------------------
-- calcula_horas_grupo
-- -------------------------------------------
CREATE OR REPLACE FUNCTION calcula_horas_grupo (incidencia VARCHAR2, grupo_estado VARCHAR2, es_grupo NUMBER)
RETURN NUMBER IS

    total_horas_grupo NUMBER;

    CURSOR c1 IS
      SELECT
               calcula_horas_servicio(
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T174.C536870915/86400),
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T174.C536870916/86400),
                   TRUNC((T174.C536870920)/86400)) TIEMPOFUNCION
      FROM ARADMIN.T174
      WHERE C8=incidencia
      AND C536870913 LIKE grupo_estado;

    CURSOR c2  IS
      SELECT
               calcula_horas_servicio(
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T174.C536870915/86400),
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (T174.C536870916/86400),
                   TRUNC((T174.C536870920)/86400)) TIEMPOFUNCION
      FROM ARADMIN.T174
      WHERE C8=incidencia
      AND C536870927 IN ('Pend. Confirmación','Pendiente Usu');

BEGIN

    total_horas_grupo := 0;

    -- valores TRUE (1) y FALSE (0)
    IF (es_grupo=1)
    THEN
        FOR tiempo_grupo_rec IN c1
        LOOP
            total_horas_grupo := total_horas_grupo + tiempo_grupo_rec.TIEMPOFUNCION;
        END LOOP;
    ELSE
        FOR tiempo_grupo_rec IN c2
        LOOP
            total_horas_grupo := total_horas_grupo + tiempo_grupo_rec.TIEMPOFUNCION;
        END LOOP;
    END IF;

    RETURN total_horas_grupo;
END;

-- -------------------------------------------
-- calcula_horas_servicio
-- -------------------------------------------

CREATE OR REPLACE FUNCTION calcula_horas_servicio(
  finicio   IN   DATE,
  ffin      IN   DATE,
  dias      IN   NUMBER

) RETURN NUMBER IS

  horas_dia_inicio NUMBER;
  horas_dia_fin NUMBER;
  horas_totales NUMBER;
  dia_inicio VARCHAR2(10);
  dia_fin VARCHAR2(10);
  fecha_aux DATE;
  finicio_aux DATE;
  ffin_aux DATE;
  dias_aux NUMBER;
  cadena_hora_fija_inicio VARCHAR2(20);
  cadena_hora_fija_fin VARCHAR2(20);
  cadena_aux VARCHAR2(20);

  hora_fija_inicio CONSTANT VARCHAR2(10) := '09:00';
  hora_fija_fin CONSTANT VARCHAR2(10) := '17:00';

BEGIN
  cadena_hora_fija_inicio:=TO_CHAR(finicio,'dd/mm/yyyy hh24:mi');
  cadena_hora_fija_fin:=TO_CHAR(ffin,'dd/mm/yyyy hh24:mi');

  --Adaptación de horario en la fecha de inicio
  finicio_aux := finicio;
  cadena_aux:=TO_CHAR(finicio,'hh24:mi');
  IF (cadena_aux<hora_fija_inicio)
  THEN
     cadena_hora_fija_inicio := TO_CHAR(finicio,'dd/mm/yyyy') || ' ' || hora_fija_inicio;
     finicio_aux := TO_DATE(cadena_hora_fija_inicio,'dd/mm/yyyy hh24:mi');
  END IF;

  IF (TO_CHAR(finicio,'hh24:mi')>hora_fija_fin)
  THEN
     cadena_hora_fija_fin := TO_CHAR(finicio,'dd/mm/yyyy') || ' ' || hora_fija_fin;
     finicio_aux := TO_DATE(cadena_hora_fija_fin,'dd/mm/yyyy hh24:mi');
  END IF;

  --Adaptación de horario en la fecha de fin
  ffin_aux := ffin;
  IF (TO_CHAR(ffin,'hh24:mi')<hora_fija_inicio)
  THEN
     cadena_hora_fija_inicio := TO_CHAR(ffin,'dd/mm/yyyy') || ' ' || hora_fija_inicio;
     ffin_aux := TO_DATE(cadena_hora_fija_inicio,'dd/mm/yyyy hh24:mi');
  END IF;

  IF (TO_CHAR(ffin,'hh24:mi')>hora_fija_fin)
  THEN
     cadena_hora_fija_fin := TO_CHAR(ffin,'dd/mm/yyyy') || ' ' || hora_fija_fin;
     ffin_aux := TO_DATE(cadena_hora_fija_fin,'dd/mm/yyyy hh24:mi');
  END IF;

-- Aquí empieza el programa
IF (ffin_aux > finicio_aux)
THEN

  -- 1. Para el caso en que la incidencia se resuelva el mismo día
  IF (TO_CHAR(finicio_aux,'dd/mm/yyyy')= TO_CHAR(ffin_aux,'dd/mm/yyyy'))
    THEN
       horas_totales := (ffin_aux-finicio_aux)*24;

       /*if horas_totales > 8
          then horas_totales := 8;
          end if;*/

  ELSE
  -- 2. Para el caso en que la incidencia se resuelva en varios días

      -- 2.1. Calculamos primero las horas del día de inicio o registro de la incidencia
      IF (TO_CHAR(finicio_aux,'hh24:mi')<= hora_fija_inicio)
        THEN
          horas_dia_inicio := 8;

        ELSE
          -- Calculamos las horas del día inicial a partir de la hora de inicio, que será mayor que las '9:00'
          dia_inicio := TO_CHAR(finicio_aux,'dd/mm/yyyy');
          fecha_aux := TO_DATE(dia_inicio||' '||hora_fija_fin,'dd/mm/yyyy hh24:mi:ss');
          horas_dia_inicio := (fecha_aux - finicio_aux)*24;
      END IF;

      -- 2.2. A continuación calculamos las horas del día de resolución de la incidencia
      IF (TO_CHAR(ffin_aux,'hh24:mi')>=hora_fija_fin)
        THEN
          horas_dia_fin := 8;

        ELSE
          -- Calculamos las horas del día inicial a partir de la hora de inicio, que será mayor que las '9:00'
          dia_fin := TO_CHAR(ffin_aux,'dd/mm/yyyy');
          fecha_aux := TO_DATE(dia_fin||' '||hora_fija_inicio,'dd/mm/yyyy hh24:mi:ss');
          horas_dia_fin := (ffin_aux - fecha_aux)*24;
      END IF;

      -- 2.3. Calculamos las horas totales
      dias_aux := dias;
      IF dias_aux>0 AND TO_CHAR(ffin,'hh24:mi')>TO_CHAR(finicio,'hh24:mi')
         THEN dias_aux := dias_aux-1;
      END IF;

      horas_totales := horas_dia_inicio + (dias_aux*8) + horas_dia_fin;


  END IF;

ELSE
 horas_totales := 0;
END IF;

  RETURN horas_totales;
END;




-- -------------------------------------------
-- query INCIDENCIAS
-- -------------------------------------------

SELECT i.numerollamada,
       TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (i.fecha_horallamada/86400),'dd/mm/yyyy hh24:mi:ss'),
       i.prioridad,
       i.estado,
       TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (i.fechahoraentrada/86400),'dd/mm/yyyy hh24:mi:ss'),
       TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (i.fechacierre/86400),'dd/mm/yyyy hh24:mi:ss'),
       i.gruposolucion,
       ROUND(calcula_horas_servicio(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (i.fechahoraentrada/86400),
       TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (i.fechacierre/86400),
       TRUNC(hestados.tiempototal/24)),2) tiempototal,
       ROUND(hestados.tiempoimplantador,2) tiempoimplantador,
       ROUND(hestados.tiempocau,2) tiempocau,
       ROUND(hestados.tiempousuario,2) tiempousuario,
       TO_CHAR(TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (i.fechamodificacion/86400),'dd/mm/yyyy hh24:mi:ss') fechamodificacion,
       i.grupo,
       i.consejeria,
       i.centro,
       i.departamento,
       i.area,
       i.a1_nivel_producto,
       i.a2_nivel_producto,
       i.a3_nivel_producto,
       --i.a4_nivel_producto,
       i.operacional_nivel_1 a1_nivel_operacional,
       i.operacional_nivel_2 a2_nivel_operacional,
       i.operacional_nivel_3 a3_nivel_operacional,
       --i.a3_nivel_operacional,
       hestados.numseguimientos,
       -- queja
       i.nreaperturas,
       -- reclamación
       -- respuesta
       i.numsolucionesparciales
       -- secundaria
       -- reclamación
  FROM incidencias i,
       (SELECT h.idincidencia cdincidencia,
               COUNT(*) numseguimientos,
               ROUND(SUM (h.totalsg)/3600,2) tiempototal,
               calcula_horas_grupo(idincidencia,'damadi%',1) tiempoimplantador,
               calcula_horas_grupo(idincidencia,'%dasc%',1) tiempocau,
               calcula_horas_grupo(idincidencia,'',0) tiempousuario
          FROM h_estados h
         GROUP BY idincidencia) hestados
 WHERE i.numerollamada=hestados.cdincidencia
   AND i.numerollamada LIKE '12-32252%';

-- -------------------------------------------
-- calcula_horas_grupo
-- -------------------------------------------

CREATE OR REPLACE FUNCTION calcula_horas_grupo (incidencia VARCHAR2, grupo_estado VARCHAR2, es_grupo NUMBER)
RETURN NUMBER IS

    total_horas_grupo NUMBER;

    CURSOR c1 IS
      SELECT
               calcula_horas_servicio(
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (h.fechainicio/86400),
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (h.fechafin/86400),
                   TRUNC((h.totalsg)/86400)) TIEMPOFUNCION
      FROM h_estados h
      WHERE h.idincidencia=incidencia
      AND h.grupo LIKE grupo_estado;

    CURSOR c2  IS
      SELECT
               calcula_horas_servicio(
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (h.fechainicio/86400),
                   TO_DATE('01/01/1970','dd/mm/yyyy hh24:mi:ss')+ (h.fechafin/86400),
                   TRUNC((h.totalsg)/86400)) TIEMPOFUNCION
      FROM h_estados h
      WHERE h.idincidencia=incidencia
      AND h.estado IN ('Pend. Confirmación','Pendiente Usu');

BEGIN

    total_horas_grupo := 0;

    -- valores true (1) y false (0)
    IF (es_grupo=1)
    THEN
        FOR tiempo_grupo_rec IN c1
        LOOP
            total_horas_grupo := total_horas_grupo + tiempo_grupo_rec.tiempofuncion;
        END LOOP;
    ELSE
        FOR tiempo_grupo_rec IN c2
        LOOP
            total_horas_grupo := total_horas_grupo + tiempo_grupo_rec.tiempofuncion;
        END LOOP;
    END IF;

    RETURN total_horas_grupo;

END;


-- fin -----------------------------------------------------------------------------------------------------