create or replace 
FUNCTION SLA_TICKET
  (P_NRORG IN CONTATO.NRORG%TYPE,
   P_NRSEQCONTATO IN CONTATO.NRSEQCONTATO%TYPE)
RETURN VARCHAR2

IS
   V_RETORNO VARCHAR2(150);
   V_ULTDIAMESANT DATE;
   V_DTPRIMEIRA_RESPOSTA DATE;
   V_CDPRODUTO VARCHAR2(10);
   V_NRPARCNEGOCIO VARCHAR2(21);
   V_DTHRCONTATO DATE;
   V_DTHRCONCCONT DATE;
   V_BUCARSLACLIENTE VARCHAR2(01);
   V_BUCARSLAPADARAO VARCHAR2(01);
   V_NAOEXITESLA VARCHAR2(01);
   V_NRSEQSLA NUMBER;
   V_COUNTSLAPARC NUMBER;
   V_NRPRIORIDADE NUMBER;
   V_NRTEMPORESPREAL NUMBER;
   V_NRTEMPOSOLUREAL NUMBER;
   V_TEMPORESPOSTA NUMBER;
   V_TEMPOSOLUCAO  NUMBER;
   V_RESPOSTA VARCHAR2(10);
   V_SOLUCAO  VARCHAR2(10);
   
BEGIN

   V_RETORNO := 'NULL';

   /* Retorna os dados do Ticket necessários para verifica o SLA */
   BEGIN
      SELECT CDPRODUTO,NRPARCNEGOCIO,DTHRCONTATO, NRPRIORIDADE, DTHRCONCCONT
        INTO V_CDPRODUTO, V_NRPARCNEGOCIO, V_DTHRCONTATO, V_NRPRIORIDADE, V_DTHRCONCCONT
        FROM CONTATO
       WHERE NRORG         = P_NRORG
         AND NRSEQCONTATO  = P_NRSEQCONTATO
         AND IDTIPOCONTATO = 'CHAMADO';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN('TICKET_NAO_ENCONTRADO');
   END;

   /* Busca o SLA utilizado pelo Produto/Cliente do Ticket */
   V_BUCARSLACLIENTE := 'N';
   BEGIN
      SELECT NRSEQSLA
        INTO V_NRSEQSLA
        FROM CRM_SLAPRODPARCNEG
       WHERE NRPARCNEGOCIO    = V_NRPARCNEGOCIO
         AND CDPRODUTO        = V_CDPRODUTO
         AND NRORG            = P_NRORG
         AND IDATIVO          = 'S';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_BUCARSLACLIENTE := 'S';
   END;

   /* Busca o SLA utilizado pelo Cliente do Ticket, quando não identificado por produto */
   V_BUCARSLAPADARAO := 'N';
   IF V_BUCARSLACLIENTE = 'S' THEN
      BEGIN
         /* Busca quantas SLA estão relacionadas ao cliente */
         SELECT COUNT(1)
           INTO V_COUNTSLAPARC
           FROM CRM_SLAPARCNEGOCIO
          WHERE NRORG         = P_NRORG
            AND NRPARCNEGOCIO = V_NRPARCNEGOCIO
            AND IDATIVO       = 'S'
          GROUP BY NRPARCNEGOCIO;
         
         /* Se existir mais de uma SLA para o mesmo cliente, passa para buscar a SLA PADRÃO */
         IF V_COUNTSLAPARC > 1 THEN
            V_BUCARSLAPADARAO := 'S';
         ELSE
            SELECT NRSEQSLA
              INTO V_NRSEQSLA
              FROM CRM_SLAPARCNEGOCIO
             WHERE NRORG         = P_NRORG
               AND NRPARCNEGOCIO = V_NRPARCNEGOCIO
               AND IDATIVO       = 'S';
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_BUCARSLAPADARAO := 'S';
      END;
   END IF;

   IF V_BUCARSLAPADARAO = 'S' THEN
      BEGIN
         SELECT NRSEQSLA
           INTO V_NRSEQSLA
           FROM CRM_SLA
          WHERE NRORG       = P_NRORG
            AND IDSLAPADRAO = 'S'
            AND IDATIVO     = 'S';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NAOEXITESLA := 'S';
      END;
   END IF;

   IF V_NAOEXITESLA = 'S' THEN
      RETURN('RESPSOK'||'-'||'RESOSOK');
   ELSE
      /* Busca a data e hora da primeira resposta do ticket */
      BEGIN
         SELECT MIN(DTHREVOLCONT)
           INTO V_DTPRIMEIRA_RESPOSTA
           FROM EVOLCONT
          WHERE NRSEQCONTATO  = P_NRSEQCONTATO
            AND IDEVOLPUBLICO = 'S'
            AND IDRESPNOTA    = 'R';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_DTPRIMEIRA_RESPOSTA := NULL;
      END;


      /* BUSCA O TEMPO DE RESPOSTA DO TICKET PASSANDO A SLA E A PRIORIDADE DO TICKET */
      BEGIN
         SELECT NRTEMPORESPREAL, NRTEMPOSOLUREAL
           INTO V_NRTEMPORESPREAL, V_NRTEMPOSOLUREAL
           FROM CRM_SLAPRIORIDADE
          WHERE NRORG        = P_NRORG
            AND NRSEQSLA     = V_NRSEQSLA
            AND NRPRIORIDADE = V_NRPRIORIDADE;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            RETURN('RESPSOK'||'-'||'RESOSOK');
      END;

      /* VERIFICA SE O TICKET ESTÁ DENTRO DO TEMPO DE RESPOSTA DA SLA */
      IF V_DTPRIMEIRA_RESPOSTA IS NULL THEN
         V_TEMPORESPOSTA := TRUNC((SYSDATE - V_DTHRCONTATO) * 24 * 60);
      ELSE
         V_TEMPORESPOSTA := TRUNC((V_DTPRIMEIRA_RESPOSTA - V_DTHRCONTATO) * 24 * 60);
      END IF;

      IF V_TEMPORESPOSTA <= V_NRTEMPORESPREAL THEN
          V_RESPOSTA := 'RESPSOK';
      ELSE
          V_RESPOSTA := 'RESPNOK';
      END IF;

      /* VERIFICA SE O TICKET ESTÁ DENTRO DO TEMPO DE SOLUÇÃO DA SLA */
      IF V_DTHRCONCCONT IS NULL THEN
         V_TEMPOSOLUCAO := TRUNC((SYSDATE - V_DTHRCONTATO) * 24 * 60);
      ELSE
         V_TEMPOSOLUCAO := TRUNC((V_DTHRCONCCONT - V_DTHRCONTATO) * 24 * 60);
      END IF;

      IF V_TEMPOSOLUCAO <= V_NRTEMPOSOLUREAL  THEN
          V_SOLUCAO := 'RESOSOK';
      ELSE
          V_SOLUCAO := 'RESONOK';
      END IF;

      V_RETORNO := V_RESPOSTA || '-' || V_SOLUCAO;

      RETURN(V_RETORNO);

   END IF;
END;
/


create or replace FUNCTION SLA_VENCE_HOJE
  (P_NRORG IN CONTATO.NRORG%TYPE,
   P_NRSEQCONTATO IN CONTATO.NRSEQCONTATO%TYPE)
RETURN VARCHAR2

IS
   V_RETORNO VARCHAR2(150);
   V_ULTDIAMESANT DATE;
   V_DTPRIMEIRA_RESPOSTA DATE;
   V_CDPRODUTO VARCHAR2(10);
   V_NRPARCNEGOCIO VARCHAR2(21);
   V_DTHRCONTATO DATE;
   V_DTHRCONCCONT DATE;
   V_BUCARSLACLIENTE VARCHAR2(01);
   V_BUCARSLAPADARAO VARCHAR2(01);
   V_NAOEXITESLA VARCHAR2(01);
   V_NRSEQSLA NUMBER;
   V_NRPRIORIDADE NUMBER;
   V_NRTEMPORESPREAL NUMBER;
   V_NRTEMPOSOLUREAL NUMBER;
   V_TEMPORESPOSTA NUMBER;
   V_TEMPOSOLUCAO  NUMBER;
   V_RESPOSTA VARCHAR2(20);
   V_SOLUCAO  VARCHAR2(20);
   V_NUMERO_RESPSOK NUMBER;
   V_NUMERO_RESPNOK NUMBER;

BEGIN

   V_RETORNO := 'NULL';
   V_NUMERO_RESPSOK := 1;
   V_NUMERO_RESPNOK := 1;

   /* Retorna os dados do Ticket necessários para verifica o SLA */
   BEGIN
      SELECT CDPRODUTO,NRPARCNEGOCIO,DTHRCONTATO, NRPRIORIDADE, DTHRCONCCONT
        INTO V_CDPRODUTO, V_NRPARCNEGOCIO, V_DTHRCONTATO, V_NRPRIORIDADE, V_DTHRCONCCONT
        FROM CONTATO
       WHERE NRORG         = P_NRORG
         AND NRSEQCONTATO  = P_NRSEQCONTATO
         AND IDTIPOCONTATO = 'CHAMADO';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN('TICKET_NAO_ENCONTRADO');
   END;

   /* Busca o SLA utilizado pelo Produto/Cliente do Ticket */
   V_BUCARSLACLIENTE := 'N';
   BEGIN
      SELECT NRSEQSLA
        INTO V_NRSEQSLA
        FROM CRM_SLAPRODPARCNEG
       WHERE NRPARCNEGOCIO    = V_NRPARCNEGOCIO
         AND CDPRODUTO        = V_CDPRODUTO
         AND NRORG            = P_NRORG
         AND IDATIVO          = 'S';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_BUCARSLACLIENTE := 'S';
   END;

   /* Busca o SLA utilizado pelo Cliente do Ticket */
   V_BUCARSLAPADARAO := 'N';
   IF V_BUCARSLACLIENTE = 'S' THEN
      BEGIN
         SELECT NRSEQSLA
           INTO V_NRSEQSLA
           FROM CRM_SLAPARCNEGOCIO
          WHERE NRORG         = P_NRORG
            AND NRPARCNEGOCIO = V_NRPARCNEGOCIO
            AND IDATIVO       = 'S';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_BUCARSLAPADARAO := 'S';
      END;
   END IF;

   IF V_BUCARSLAPADARAO = 'S' THEN
      BEGIN
         SELECT NRSEQSLA
           INTO V_NRSEQSLA
           FROM CRM_SLA
          WHERE NRORG       = P_NRORG
            AND IDSLAPADRAO = 'S'
            AND IDATIVO     = 'S';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NAOEXITESLA := 'S';
      END;
   END IF;

   IF V_NAOEXITESLA = 'S' THEN
      RETURN('RESPSOK'||'-'||'RESOSOK');
   ELSE
      /* Busca a data e hora da primeira resposta do ticket */
      BEGIN
         SELECT MIN(DTHREVOLCONT)
           INTO V_DTPRIMEIRA_RESPOSTA
           FROM EVOLCONT
          WHERE NRSEQCONTATO  = P_NRSEQCONTATO
            AND IDEVOLPUBLICO = 'S'
            AND IDRESPNOTA    = 'R';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_DTPRIMEIRA_RESPOSTA := NULL;
      END;


      /* BUSCA O TEMPO DE RESPOSTA DO TICKET PASSANDO A SLA E A PRIORIDADE DO TICKET */
      BEGIN
         SELECT NRTEMPORESPREAL, NRTEMPOSOLUREAL
           INTO V_NRTEMPORESPREAL, V_NRTEMPOSOLUREAL
           FROM CRM_SLAPRIORIDADE
          WHERE NRORG        = P_NRORG
            AND NRSEQSLA     = V_NRSEQSLA
            AND NRPRIORIDADE = V_NRPRIORIDADE;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            RETURN('RESPOK'||'-'||'RESOOK');
      END;

      IF TRUNC(V_DTHRCONTATO + (V_NRTEMPORESPREAL/24/60)) = TRUNC(SYSDATE) THEN
         V_RESPOSTA := 'RESP_SVENCE_HOJE';
      ELSE
         V_RESPOSTA := 'RESP_NVENCE_HOJE';
      END IF;

      IF TRUNC(V_DTHRCONTATO + (V_NRTEMPOSOLUREAL/24/60)) = TRUNC(SYSDATE) THEN
         V_SOLUCAO := 'SOLU_SVENCE_HOJE';
      ELSE
         V_SOLUCAO := 'SOLU_NVENCE_HOJE';
      END IF;


      V_RETORNO := V_RESPOSTA || '-' || V_SOLUCAO;

      RETURN(V_RETORNO);

   END IF;
END;
/



create or replace FUNCTION TEMPO_MEDIO_SLA
  (P_NRORG IN CONTATO.NRORG%TYPE,
   P_NRSEQCONTATO IN CONTATO.NRSEQCONTATO%TYPE,
   P_TNRSEQCONTATO IN VARCHAR2,
   P_CDOPERADOR IN VARCHAR2)
RETURN VARCHAR2

IS
   V_RETORNO1 NUMBER;
   V_RETORNO2 NUMBER;
   V_RETORNO3 NUMBER;
   V_RETORNO3_AUX NUMBER;
   V_RETORNO4 NUMBER;
   V_RETORNO_GERAL VARCHAR2(200);
   V_ULTDIAMESANT DATE;
   V_DTPRIMEIRA_RESPOSTA DATE;
   V_NUMERO_TICKETS NUMBER;
   V_NUMERO_EVOLUCOES NUMBER;
   V_NUMERO_TICKETS_RESOLVIDOS NUMBER;
   
BEGIN

   V_RETORNO1 := 0;
   V_RETORNO2 := 0;
   V_RETORNO3 := 0;
   V_RETORNO3_AUX := 0;   
   V_RETORNO4 := 0;

   V_NUMERO_TICKETS := 0;
   V_NUMERO_EVOLUCOES := 0;
   V_NUMERO_TICKETS_RESOLVIDOS := 0;
   V_RETORNO3_AUX := 0;

   /* Retorna os dados do Ticket necessários para verifica o SLA */
   FOR C_TICKET IN(SELECT NRSEQCONTATO,CDPRODUTO,NRPARCNEGOCIO,DTHRCONTATO, NRPRIORIDADE, DTHRCONCCONT
                     FROM CONTATO
                    WHERE INSTR(P_CDOPERADOR,CDOPERCONT) > 0  
                      AND NRORG         = P_NRORG
                      AND IDTIPOCONTATO = 'CHAMADO'
                      AND IDCANCELADO   = 'N'
                      AND (NRSEQCONTATO = P_NRSEQCONTATO OR P_TNRSEQCONTATO = 'T')) LOOP

      V_NUMERO_TICKETS := V_NUMERO_TICKETS + 1;

      /* Busca a data e hora da primeira resposta do ticket */
      BEGIN
         SELECT MIN(DTHREVOLCONT)
           INTO V_DTPRIMEIRA_RESPOSTA
           FROM EVOLCONT
          WHERE NRSEQCONTATO  = C_TICKET.NRSEQCONTATO
            AND IDEVOLPUBLICO = 'S'
            AND IDRESPNOTA    = 'R';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_DTPRIMEIRA_RESPOSTA := NULL;
      END;

      IF V_DTPRIMEIRA_RESPOSTA IS NULL THEN
         V_RETORNO1 := V_RETORNO1 + 0;
      ELSE
         V_RETORNO1 := V_RETORNO1 + ((V_DTPRIMEIRA_RESPOSTA - C_TICKET.DTHRCONTATO) * 24 * 60);
      END IF;


      /* Busca o tempo total de todas as resostas dadas de todos os ticket do operador passado */
      V_NUMERO_EVOLUCOES := 0;
      FOR C_TICKET_EVOL IN(SELECT DTHREVOLCONT
                             FROM EVOLCONT
                            WHERE NRSEQCONTATO  = C_TICKET.NRSEQCONTATO
                              AND IDEVOLPUBLICO = 'S'
                              AND IDRESPNOTA    = 'R') LOOP

         V_NUMERO_EVOLUCOES := V_NUMERO_EVOLUCOES + 1;

         IF C_TICKET_EVOL.DTHREVOLCONT IS NULL THEN
            V_RETORNO2 := V_RETORNO2 + 0;
         ELSE
            V_RETORNO2 := V_RETORNO2 + (C_TICKET_EVOL.DTHREVOLCONT - C_TICKET.DTHRCONTATO) * 24 * 60;
         END IF;

      END LOOP;
      IF V_NUMERO_EVOLUCOES > 0 THEN
         V_RETORNO3_AUX := V_RETORNO2 / V_NUMERO_EVOLUCOES;
      END IF;
      V_RETORNO3 := V_RETORNO3 + V_RETORNO3_AUX;

   END LOOP;

   /* Calcula o tempo médio de resolução, buscando apenas tickets resolvidos / encerrados */
   V_NUMERO_TICKETS_RESOLVIDOS := 0;
   FOR C_TICKET_RESO IN(SELECT NRSEQCONTATO,DTHRCONTATO, DTHRCONCCONT
                          FROM CONTATO
                         WHERE INSTR(P_CDOPERADOR,CDOPERCONT) > 0
                           AND NRORG         = P_NRORG
                           AND NRSEQSITUACAO IN (3,4)
                           AND IDTIPOCONTATO = 'CHAMADO'
                           AND IDCANCELADO   = 'N'
                           AND (NRSEQCONTATO = P_NRSEQCONTATO OR P_TNRSEQCONTATO = 'T')) LOOP


      V_NUMERO_TICKETS_RESOLVIDOS := V_NUMERO_TICKETS_RESOLVIDOS + 1;

      IF C_TICKET_RESO.DTHRCONCCONT IS NULL THEN
         V_RETORNO4 := V_RETORNO4 + 0;
      ELSE
         V_RETORNO4 := V_RETORNO4 + (C_TICKET_RESO.DTHRCONCCONT - C_TICKET_RESO.DTHRCONTATO) * 24 * 60;
      END IF;


   END LOOP;

   IF V_NUMERO_TICKETS = 0 THEN
      V_NUMERO_TICKETS := 1;
   END IF;

   IF V_NUMERO_TICKETS_RESOLVIDOS = 0 THEN
      V_NUMERO_TICKETS_RESOLVIDOS := 1;
   END IF;

   V_RETORNO_GERAL :=  TO_CHAR(V_RETORNO1/V_NUMERO_TICKETS) || '_' || TO_CHAR(V_RETORNO3/V_NUMERO_TICKETS) || '_' || TO_CHAR(V_RETORNO4/V_NUMERO_TICKETS_RESOLVIDOS);


   RETURN(V_RETORNO_GERAL);

END; 