ALTER TABLE TBCASO_PAINEL
    ADD CALENDARIO_OPERACAO BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8;

COMMENT ON COLUMN TBCASO_PAINEL.CALENDARIO_OPERACAO IS
'Calendario utilizado para definicao de dias e horarios de atendimento das tarefas do caso na tela de atividades do workflow';

/***************************************************************************/

ALTER TABLE TBWORKFLOW
    ADD ESCALA_ID INTEGER;
	
ALTER TABLE TBWORKFLOW_TAREFA
    ADD ESCALA_ID INTEGER;

/***************************************************************************/
/***************************************************************************/

UPDATE TBCASO_PAINEL
	SET CALENDARIO_OPERACAO = '[{"DIA":0,"DESCRICAO":"DOMINGO","STATUS":0,"HORA_01":"07:00:00","HORA_02":"11:50:00","HORA_03":"13:05:00","HORA_04":"16:48:00","HORA_05":"","HORA_06":"","HORA_07":"","HORA_08":"","HORA_09":"","HORA_10":"","HORA_11":"","HORA_12":"","HORA_13":"","HORA_14":"","HORA_15":"","HORA_16":"","HORA_17":"","HORA_18":"","HORA_19":"","HORA_20":""},{"DIA":1,"DESCRICAO":"SEGUNDA","STATUS":1,"HORA_01":"07:00:00","HORA_02":"11:50:00","HORA_03":"13:05:00","HORA_04":"16:48:00","HORA_05":"","HORA_06":"","HORA_07":"","HORA_08":"","HORA_09":"","HORA_10":"","HORA_11":"","HORA_12":"","HORA_13":"","HORA_14":"","HORA_15":"","HORA_16":"","HORA_17":"","HORA_18":"","HORA_19":"","HORA_20":""},{"DIA":2,"DESCRICAO":"TERÇA","STATUS":1,"HORA_01":"07:00:00","HORA_02":"11:50:00","HORA_03":"13:05:00","HORA_04":"16:48:00","HORA_05":"","HORA_06":"","HORA_07":"","HORA_08":"","HORA_09":"","HORA_10":"","HORA_11":"","HORA_12":"","HORA_13":"","HORA_14":"","HORA_15":"","HORA_16":"","HORA_17":"","HORA_18":"","HORA_19":"","HORA_20":""},{"DIA":3,"DESCRICAO":"QUARTA","STATUS":1,"HORA_01":"07:00:00","HORA_02":"11:50:00","HORA_03":"13:05:00","HORA_04":"16:48:00","HORA_05":"","HORA_06":"","HORA_07":"","HORA_08":"","HORA_09":"","HORA_10":"","HORA_11":"","HORA_12":"","HORA_13":"","HORA_14":"","HORA_15":"","HORA_16":"","HORA_17":"","HORA_18":"","HORA_19":"","HORA_20":""},{"DIA":4,"DESCRICAO":"QUINTA","STATUS":1,"HORA_01":"07:00:00","HORA_02":"11:50:00","HORA_03":"13:05:00","HORA_04":"16:48:00","HORA_05":"","HORA_06":"","HORA_07":"","HORA_08":"","HORA_09":"","HORA_10":"","HORA_11":"","HORA_12":"","HORA_13":"","HORA_14":"","HORA_15":"","HORA_16":"","HORA_17":"","HORA_18":"","HORA_19":"","HORA_20":""},{"DIA":5,"DESCRICAO":"SEXTA","STATUS":1,"HORA_01":"07:00:00","HORA_02":"11:50:00","HORA_03":"13:05:00","HORA_04":"16:48:00","HORA_05":"","HORA_06":"","HORA_07":"","HORA_08":"","HORA_09":"","HORA_10":"","HORA_11":"","HORA_12":"","HORA_13":"","HORA_14":"","HORA_15":"","HORA_16":"","HORA_17":"","HORA_18":"","HORA_19":"","HORA_20":""},{"DIA":6,"DESCRICAO":"SÁBADO","STATUS":0,"HORA_01":"07:00:00","HORA_02":"11:50:00","HORA_03":"13:05:00","HORA_04":"16:48:00","HORA_05":"","HORA_06":"","HORA_07":"","HORA_08":"","HORA_09":"","HORA_10":"","HORA_11":"","HORA_12":"","HORA_13":"","HORA_14":"","HORA_15":"","HORA_16":"","HORA_17":"","HORA_18":"","HORA_19":"","HORA_20":""}]'
	WHERE CALENDARIO_OPERACAO IS NULL;

/***************************************************************************/
/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_painel_b01u for tbcaso_painel
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF ( OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC:'|| OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF ( OLD.TITULO <> NEW.TITULO) THEN
    BEGIN
        HISTORICO = HISTORICO||' TITULO:'|| OLD.TITULO||'->'||NEW.TITULO;
    END

    IF ( OLD.MENU_GRUPO <> NEW.MENU_GRUPO) THEN
    BEGIN
        HISTORICO = HISTORICO||' GRUPO:'|| OLD.MENU_GRUPO||'->'||NEW.MENU_GRUPO;
    END

    IF ( OLD.TEMPLATE <> NEW.TEMPLATE) THEN
    BEGIN
        HISTORICO = HISTORICO||' TEMPLATE:'|| OLD.TEMPLATE||'->'||NEW.TEMPLATE;
    END

     IF ( OLD.FRASE <> NEW.FRASE) THEN
    BEGIN
        HISTORICO = HISTORICO||' FRASE:'|| OLD.FRASE||'->'||NEW.FRASE;
    END

     IF ( OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||' STATUS:'|| OLD.STATUS||'->'||NEW.STATUS;
        UPDATE TBMENU SET TIPO = NEW.STATUS WHERE CONTROLE = 99000 + NEW.ID;    -- MODIFICA O TIPO DA TBMENU DE ACORDO COM O STATUS DO PAINEL
    END

     IF ( OLD.CONTATO_CADASTRO <> NEW.CONTATO_CADASTRO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CONTATO CADASTRO:'|| OLD.CONTATO_CADASTRO||'->'||NEW.CONTATO_CADASTRO;
    END

     IF ( OLD.EMAIL <> NEW.EMAIL) THEN
    BEGIN
        HISTORICO = HISTORICO||' EMAIL:'|| OLD.EMAIL||'->'||NEW.EMAIL;
    END

     IF ( OLD.EMAIL_PASS <> NEW.EMAIL_PASS) THEN
    BEGIN
        HISTORICO = HISTORICO||' EMAIL SENHA:'||'SENHA ALTERADA';
    END

     IF ( OLD.EMAIL_SMTP <> NEW.EMAIL_SMTP) THEN
    BEGIN
        HISTORICO = HISTORICO||' EMAIL SMTP:'|| OLD.EMAIL_SMTP||'->'||NEW.EMAIL_SMTP;
    END

     IF ( OLD.PORTA_SMTP <> NEW.PORTA_SMTP) THEN
    BEGIN
        HISTORICO = HISTORICO||' PORTA SMTP:'|| OLD.PORTA_SMTP||'->'||NEW.PORTA_SMTP;
    END

     IF ( OLD.EMAIL_POP <> NEW.EMAIL_POP) THEN
    BEGIN
        HISTORICO = HISTORICO||' EMAIL POP:'|| OLD.EMAIL_POP||'->'||NEW.EMAIL_POP;
    END

     IF ( OLD.PORTA_POP <> NEW.PORTA_POP) THEN
    BEGIN
        HISTORICO = HISTORICO||' PORTA POP:'|| OLD.PORTA_POP||'->'||NEW.PORTA_POP;
    END

     IF ( OLD.AUTENTICACAO <> NEW.AUTENTICACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' AUTENTICACAO:'|| OLD.AUTENTICACAO||'->'||NEW.AUTENTICACAO;
    END

     IF ( OLD.SSL_TLS <> NEW.SSL_TLS) THEN
    BEGIN
        HISTORICO = HISTORICO||' SSL_TLS:'|| OLD.SSL_TLS||'->'||NEW.SSL_TLS;
    END

      IF ( OLD.EMAIL_MONITOR <> NEW.EMAIL_MONITOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' EMAIL MONITOR:'|| OLD.EMAIL_MONITOR||'->'||NEW.EMAIL_MONITOR;
    END

     IF ( OLD.SQL_EMAIL_CONTATO <> NEW.SQL_EMAIL_CONTATO) THEN
    BEGIN
        HISTORICO = HISTORICO||' SQL EMAIL CONTATO:'|| OLD.SQL_EMAIL_CONTATO||'->'||NEW.SQL_EMAIL_CONTATO;
    END

    IF ( OLD.CAMPO_NOME_CONTATO <> NEW.CAMPO_NOME_CONTATO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CAMPO NOME CONTATO:'|| OLD.CAMPO_NOME_CONTATO||'->'||NEW.CAMPO_NOME_CONTATO;
    END

    IF ( OLD.REQUER_DESC_TECNICA <> NEW.REQUER_DESC_TECNICA) THEN
    BEGIN
        HISTORICO = HISTORICO||' REQUER DESC.TECNICA:'|| OLD.REQUER_DESC_TECNICA||'->'||NEW.REQUER_DESC_TECNICA;
    END

    IF ( OLD.REQUER_SOLUCAO_CASO <> NEW.REQUER_SOLUCAO_CASO) THEN
    BEGIN
        HISTORICO = HISTORICO||' REQUER SOLUC.CASO:'|| OLD.REQUER_SOLUCAO_CASO||'->'||NEW.REQUER_SOLUCAO_CASO;
    END

    IF ( OLD.VISUALIZACAO_WORKFOW <> NEW.VISUALIZACAO_WORKFOW) THEN
    BEGIN
        HISTORICO = HISTORICO||' VISUAL.WORKFLOW:'|| OLD.VISUALIZACAO_WORKFOW||'->'||NEW.VISUALIZACAO_WORKFOW;
    END

    IF ( OLD.VISUALIZAR_JANELA_FINALIZACAO <> NEW.VISUALIZAR_JANELA_FINALIZACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' VISUAL.JANELA FINAL:'|| OLD.VISUALIZAR_JANELA_FINALIZACAO||'->'||NEW.VISUALIZAR_JANELA_FINALIZACAO;
    END


    IF ( OLD.CONSULTA_MOTIVO_DESCRICAO <> NEW.CONSULTA_MOTIVO_DESCRICAO ) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC.CONSULTA MOTIVO:'||OLD.CONSULTA_MOTIVO_DESCRICAO||'->'||NEW.CONSULTA_MOTIVO_DESCRICAO;
    END

    IF ( OLD.CONSULTA_TIPO_DESCRICAO <> NEW.CONSULTA_TIPO_DESCRICAO ) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC.CONSULTA TIPO:'||OLD.CONSULTA_TIPO_DESCRICAO||'->'||NEW.CONSULTA_TIPO_DESCRICAO;
    END

    IF ( OLD.CONSULTA_ORIGEM_DESCRICAO <> NEW.CONSULTA_ORIGEM_DESCRICAO ) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC.CONSULTA ORIGEM:'||OLD.CONSULTA_ORIGEM_DESCRICAO||'->'||NEW.CONSULTA_ORIGEM_DESCRICAO;
    END

    IF ( OLD.CALENDARIO_OPERACAO <> NEW.CALENDARIO_OPERACAO ) THEN
    BEGIN
        HISTORICO = HISTORICO||' CALENDARIO DE OPERACAO ALTERADO!';
    END


    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCASO_PAINEL',NEW.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^

SET TERM ; ^

/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_b1u for tbworkflow
active before update position 1
AS
DECLARE VARIABLE OLD_ESCALA VARCHAR(500);
DECLARE VARIABLE NEW_ESCALA VARCHAR(500);
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF (OLD.TITULO <> NEW.TITULO) THEN
    BEGIN
        HISTORICO = HISTORICO||' TITULO:'||OLD.TITULO||'->'||NEW.TITULO;
    END

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
       HISTORICO = HISTORICO||' DESC:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END
    
    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
       HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
    END
    
    IF (OLD.TIPO <> NEW.TIPO) THEN
    BEGIN
       HISTORICO = HISTORICO||' TIPO:'||OLD.TIPO||'->'||NEW.TIPO;
    END

    IF (OLD.ESCALA_ID <> NEW.ESCALA_ID) THEN
    BEGIN
       OLD_ESCALA = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBESCALA A WHERE A.CODIGO = OLD.ESCALA_ID), '');
       NEW_ESCALA = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBESCALA A WHERE A.CODIGO = NEW.ESCALA_ID), '');

       HISTORICO = HISTORICO||' ESCALA:'||OLD_ESCALA||'->'||NEW_ESCALA;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       FN_ELIPSE(:HISTORICO, 500));
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_tarefa_b1u for tbworkflow_tarefa
active before update position 1
AS
DECLARE VARIABLE OLD_ESCALA VARCHAR(500);
DECLARE VARIABLE NEW_ESCALA VARCHAR(500);
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

  HISTORICO = '';

  IF (OLD.TITULO <> NEW.TITULO) THEN
  BEGIN
     HISTORICO = HISTORICO||' TIT.:'||OLD.TITULO||'->'||NEW.TITULO;
  END

  IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
  BEGIN
     HISTORICO = HISTORICO||' DESC.:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
  END
  
  IF (OLD.STATUS <> NEW.STATUS) THEN
  BEGIN
     HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
  END

  IF (OLD.SEQUENCIA <> NEW.SEQUENCIA) THEN
  BEGIN
     HISTORICO = HISTORICO||' SEQ:'||OLD.SEQUENCIA||'->'||NEW.SEQUENCIA;
  END

  IF (OLD.TEMPO_PREVISTO <> NEW.TEMPO_PREVISTO) THEN
  BEGIN
     HISTORICO = HISTORICO||' TEMPO:'||OLD.TEMPO_PREVISTO||'->'||NEW.TEMPO_PREVISTO;
  END

  IF (OLD.PONTO_REPROVACAO <> NEW.PONTO_REPROVACAO) THEN
  BEGIN
     HISTORICO = HISTORICO||' PONTO REPROV:'||OLD.PONTO_REPROVACAO||'->'||NEW.PONTO_REPROVACAO;
  END

  IF (OLD.PONTO_APROVACAO <> NEW.PONTO_APROVACAO) THEN
  BEGIN
     HISTORICO = HISTORICO||' PONTO APROV:'||OLD.PONTO_APROVACAO||'->'||NEW.PONTO_APROVACAO;
  END

  IF (OLD.ESCALA_ID <> NEW.ESCALA_ID) THEN
  BEGIN
     OLD_ESCALA = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBESCALA A WHERE A.CODIGO = OLD.ESCALA_ID), '');
     NEW_ESCALA = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.DESCRICAO FROM TBESCALA A WHERE A.CODIGO = NEW.ESCALA_ID), '');

     HISTORICO = HISTORICO||' ESCALA:'||OLD_ESCALA||'->'||NEW_ESCALA;
  END

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_TAREFA', OLD.ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     FN_ELIPSE(:HISTORICO, 500));
  END
END
^

SET TERM ; ^

/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPC_ESCALA_HORARIO_JSON (
    ESCALA_ID integer not null)
returns (
    VALORES blob sub_type 1 segment size 80)
as
BEGIN

    FOR
        SELECT
            '['||LIST( Y.VALOR )||']' AS VALORES
        
        FROM (
        
            SELECT
                X.VALOR
            
            FROM (
                SELECT
                    FN_TO_JSON_V2(
                        FN_TO_PAR_JSON_V3('DIA', T.DIA_SEMANA),
                        FN_TO_PAR_JSON_V3('DESCRICAO',  IIF(T.DIA_SEMANA = 1, 'DOMINGO',
                                                        IIF(T.DIA_SEMANA = 2, 'SEGUNDA',
                                                        IIF(T.DIA_SEMANA = 3, 'TERÇA',
                                                        IIF(T.DIA_SEMANA = 4, 'QUARTA',
                                                        IIF(T.DIA_SEMANA = 5, 'QUINTA',
                                                        IIF(T.DIA_SEMANA = 6, 'SEXTA',
                                                        IIF(T.DIA_SEMANA = 7, 'SÁBADO', ''))))))) ),
                
                        FN_TO_PAR_JSON_V3('HORARIOS', LIST(A.HORA2))
                        ) AS VALOR
                
                FROM TBESCALA_HORARIO T
                INNER JOIN TBRH_HORARIO A ON A.CODIGO = T.HORARIO_CODIGO
                WHERE T.ESCALA_CODIGO = :ESCALA_ID
                AND T.DIA_SEMANA = 1
                
                GROUP BY T.DIA_SEMANA
            ) X
            
            UNION ALL
            
            SELECT
                X.VALOR
            
            FROM (
                SELECT
                    FN_TO_JSON_V2(
                        FN_TO_PAR_JSON_V3('DIA', T.DIA_SEMANA),
                        FN_TO_PAR_JSON_V3('DESCRICAO',  IIF(T.DIA_SEMANA = 1, 'DOMINGO',
                                                        IIF(T.DIA_SEMANA = 2, 'SEGUNDA',
                                                        IIF(T.DIA_SEMANA = 3, 'TERÇA',
                                                        IIF(T.DIA_SEMANA = 4, 'QUARTA',
                                                        IIF(T.DIA_SEMANA = 5, 'QUINTA',
                                                        IIF(T.DIA_SEMANA = 6, 'SEXTA',
                                                        IIF(T.DIA_SEMANA = 7, 'SÁBADO', ''))))))) ),
                
                        FN_TO_PAR_JSON_V3('HORARIOS', LIST(A.HORA2))
                        ) AS VALOR
                
                FROM TBESCALA_HORARIO T
                INNER JOIN TBRH_HORARIO A ON A.CODIGO = T.HORARIO_CODIGO
                WHERE T.ESCALA_CODIGO = :ESCALA_ID
                AND T.DIA_SEMANA = 2
                
                GROUP BY T.DIA_SEMANA
            ) X
            
            UNION ALL
            
            SELECT
                X.VALOR
            
            FROM (
                SELECT
                    FN_TO_JSON_V2(
                        FN_TO_PAR_JSON_V3('DIA', T.DIA_SEMANA),
                        FN_TO_PAR_JSON_V3('DESCRICAO',  IIF(T.DIA_SEMANA = 1, 'DOMINGO',
                                                        IIF(T.DIA_SEMANA = 2, 'SEGUNDA',
                                                        IIF(T.DIA_SEMANA = 3, 'TERÇA',
                                                        IIF(T.DIA_SEMANA = 4, 'QUARTA',
                                                        IIF(T.DIA_SEMANA = 5, 'QUINTA',
                                                        IIF(T.DIA_SEMANA = 6, 'SEXTA',
                                                        IIF(T.DIA_SEMANA = 7, 'SÁBADO', ''))))))) ),
                
                        FN_TO_PAR_JSON_V3('HORARIOS', LIST(A.HORA2))
                        ) AS VALOR
                
                FROM TBESCALA_HORARIO T
                INNER JOIN TBRH_HORARIO A ON A.CODIGO = T.HORARIO_CODIGO
                WHERE T.ESCALA_CODIGO = :ESCALA_ID
                AND T.DIA_SEMANA = 3
                
                GROUP BY T.DIA_SEMANA
            ) X
            
            UNION ALL
            
            SELECT
                X.VALOR
            
            FROM (
                SELECT
                    FN_TO_JSON_V2(
                        FN_TO_PAR_JSON_V3('DIA', T.DIA_SEMANA),
                        FN_TO_PAR_JSON_V3('DESCRICAO',  IIF(T.DIA_SEMANA = 1, 'DOMINGO',
                                                        IIF(T.DIA_SEMANA = 2, 'SEGUNDA',
                                                        IIF(T.DIA_SEMANA = 3, 'TERÇA',
                                                        IIF(T.DIA_SEMANA = 4, 'QUARTA',
                                                        IIF(T.DIA_SEMANA = 5, 'QUINTA',
                                                        IIF(T.DIA_SEMANA = 6, 'SEXTA',
                                                        IIF(T.DIA_SEMANA = 7, 'SÁBADO', ''))))))) ),
                
                        FN_TO_PAR_JSON_V3('HORARIOS', LIST(A.HORA2))
                        ) AS VALOR
                
                FROM TBESCALA_HORARIO T
                INNER JOIN TBRH_HORARIO A ON A.CODIGO = T.HORARIO_CODIGO
                WHERE T.ESCALA_CODIGO = :ESCALA_ID
                AND T.DIA_SEMANA = 4
                
                GROUP BY T.DIA_SEMANA
            ) X
            
            UNION ALL
            
            SELECT
                X.VALOR
            
            FROM (
                SELECT
                    FN_TO_JSON_V2(
                        FN_TO_PAR_JSON_V3('DIA', T.DIA_SEMANA),
                        FN_TO_PAR_JSON_V3('DESCRICAO',  IIF(T.DIA_SEMANA = 1, 'DOMINGO',
                                                        IIF(T.DIA_SEMANA = 2, 'SEGUNDA',
                                                        IIF(T.DIA_SEMANA = 3, 'TERÇA',
                                                        IIF(T.DIA_SEMANA = 4, 'QUARTA',
                                                        IIF(T.DIA_SEMANA = 5, 'QUINTA',
                                                        IIF(T.DIA_SEMANA = 6, 'SEXTA',
                                                        IIF(T.DIA_SEMANA = 7, 'SÁBADO', ''))))))) ),
                
                        FN_TO_PAR_JSON_V3('HORARIOS', LIST(A.HORA2))
                        ) AS VALOR
                
                FROM TBESCALA_HORARIO T
                INNER JOIN TBRH_HORARIO A ON A.CODIGO = T.HORARIO_CODIGO
                WHERE T.ESCALA_CODIGO = :ESCALA_ID
                AND T.DIA_SEMANA = 5
                
                GROUP BY T.DIA_SEMANA
            ) X
            
            UNION ALL
            
            SELECT
                X.VALOR
            
            FROM (
                SELECT
                    FN_TO_JSON_V2(
                        FN_TO_PAR_JSON_V3('DIA', T.DIA_SEMANA),
                        FN_TO_PAR_JSON_V3('DESCRICAO',  IIF(T.DIA_SEMANA = 1, 'DOMINGO',
                                                        IIF(T.DIA_SEMANA = 2, 'SEGUNDA',
                                                        IIF(T.DIA_SEMANA = 3, 'TERÇA',
                                                        IIF(T.DIA_SEMANA = 4, 'QUARTA',
                                                        IIF(T.DIA_SEMANA = 5, 'QUINTA',
                                                        IIF(T.DIA_SEMANA = 6, 'SEXTA',
                                                        IIF(T.DIA_SEMANA = 7, 'SÁBADO', ''))))))) ),
                
                        FN_TO_PAR_JSON_V3('HORARIOS', LIST(A.HORA2))
                        ) AS VALOR
                
                FROM TBESCALA_HORARIO T
                INNER JOIN TBRH_HORARIO A ON A.CODIGO = T.HORARIO_CODIGO
                WHERE T.ESCALA_CODIGO = :ESCALA_ID
                AND T.DIA_SEMANA = 6
                
                GROUP BY T.DIA_SEMANA
            ) X
            
            UNION ALL
            
            SELECT
                X.VALOR
            
            FROM (
                SELECT
                    FN_TO_JSON_V2(
                        FN_TO_PAR_JSON_V3('DIA', T.DIA_SEMANA),
                        FN_TO_PAR_JSON_V3('DESCRICAO',  IIF(T.DIA_SEMANA = 1, 'DOMINGO',
                                                        IIF(T.DIA_SEMANA = 2, 'SEGUNDA',
                                                        IIF(T.DIA_SEMANA = 3, 'TERÇA',
                                                        IIF(T.DIA_SEMANA = 4, 'QUARTA',
                                                        IIF(T.DIA_SEMANA = 5, 'QUINTA',
                                                        IIF(T.DIA_SEMANA = 6, 'SEXTA',
                                                        IIF(T.DIA_SEMANA = 7, 'SÁBADO', ''))))))) ),
                
                        FN_TO_PAR_JSON_V3('HORARIOS', LIST(A.HORA2))
                        ) AS VALOR
                
                FROM TBESCALA_HORARIO T
                INNER JOIN TBRH_HORARIO A ON A.CODIGO = T.HORARIO_CODIGO
                WHERE T.ESCALA_CODIGO = :ESCALA_ID
                AND T.DIA_SEMANA = 7
                
                GROUP BY T.DIA_SEMANA
            ) X
        
        ) Y

    INTO
        :VALORES

    DO
    BEGIN

        SUSPEND;

    END
END
^

SET TERM ; ^

/***************************************************************************/
/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER function FN_CALC_FIM_ESCALA (
    ESCALA_ID integer,
    MINUTOS integer,
    DATA_HORA_INICIO timestamp,
    CONTADOR integer = 0)
returns timestamp
AS
declare variable DIA_SEMANA varchar(10);
declare variable HORA1 integer;
declare variable HORA2 integer;
declare variable QTD_MINUTOS integer;
declare variable DIA_INICIO integer;
declare variable DATA_FIM timestamp;
declare variable SALDO_MINUTOS integer;
declare variable MINUTOS_CONTAR integer;
declare variable ENCONTRADO integer;
declare variable TDATA_FIM timestamp;
declare variable DIA_TRATANDO integer;
declare variable GASTO integer;
begin
  CONTADOR = coalesce(CONTADOR,0) + 1;

  DIA_INICIO    = extract(weekday from DATA_HORA_INICIO);
  DATA_FIM      = DATA_HORA_INICIO;
  SALDO_MINUTOS = MINUTOS;
  DIA_INICIO    = iif(DIA_INICIO = 0, 7, DIA_INICIO);
  DIA_TRATANDO  = DIA_INICIO;

  for
    select
        dia_semana, hora1, hora2, hora2 - hora1 as MINUTOS
    from(
        select
           h.*, o.hora2 as hora1, lead(o.hora2) over (order by h.dia_semana, h.sequencia) as hora2, mod(h.sequencia,2) as PAR
        from tbescala a, tbescala_horario h, tbrh_horario o
        where a.codigo = :ESCALA_ID
        and h.escala_codigo = a.codigo
        and o.codigo = h.horario_codigo
        and h.dia_semana >= :DIA_INICIO
        and iif(h.dia_semana = 1, a.dia1 = '1',
            iif(h.dia_semana = 2, a.dia2 = '1',
            iif(h.dia_semana = 3, a.dia3 = '1',
            iif(h.dia_semana = 4, a.dia4 = '1',
            iif(h.dia_semana = 5, a.dia5 = '1',
            iif(h.dia_semana = 6, a.dia6 = '1',
            iif(h.dia_semana = 7, a.dia7 = '1',false)))))))
        order by h.dia_semana, h.sequencia
    ) where PAR = 1
  into
    DIA_SEMANA,
    HORA1,
    HORA2,
    QTD_MINUTOS
  do begin

    -- condicao que ignora o if para desconsiderar feriados ()
    -- fazer AQUI e incrementar a data no campo DATA_FIM

     if(SALDO_MINUTOS > 0)then
     begin

        if(QTD_MINUTOS > SALDO_MINUTOS)then
        begin
            MINUTOS_CONTAR = SALDO_MINUTOS;
        end else
        begin
            MINUTOS_CONTAR = QTD_MINUTOS;
        end

        if(DIA_TRATANDO < DIA_SEMANA and SALDO_MINUTOS > 0)then
        begin
            --exception "..." SALDO_MINUTOS||' - '||DATA_FIM||' - '||DIA_TRATANDO||'-'||DIA_SEMANA||'-'||HORA1;
            DATA_FIM = cast(dateadd(day,1,cast(:DATA_FIM as date)) as timestamp);
        end

        for
            select
                max(DATA_FIM) as DATA_FIM,
                max(CORIDOS) as GASTO
            from(
                select
                    g.*,
                    row_number() over(order by g.id) as CORIDOS
                from(
                    select
                        m.*, b.*, dateadd(minute, m.id, cast(cast(data as date) as timestamp)) as DATA_FIM
                    from tbminuto m, (select
                      cast(:DATA_FIM as timestamp) as data,
                      cast(:HORA1 as integer) as minuto1,
                      cast(:HORA2 as integer) as minuto2,
                      cast(:MINUTOS_CONTAR as integer) as qtd_minuto
                    from rdb$database) b
                    
                    where m.id between minuto1 and  minuto2
                    order by m.id
                ) g where DATA_FIM >= data
            )  where CORIDOS <= qtd_minuto
        into
            TDATA_FIM,
            GASTO
        do begin
            if(TDATA_FIM is not null)then
            begin
                DATA_FIM = TDATA_FIM;
                SALDO_MINUTOS = SALDO_MINUTOS - GASTO;
            end
        end

        DIA_TRATANDO = DIA_SEMANA;
     end

  end

  if(SALDO_MINUTOS > 0 and CONTADOR < 10)then
  begin
    DATA_FIM = cast(dateadd(day,1,cast(:DATA_FIM as date)) as timestamp);
    DATA_FIM = FN_CALC_FIM_ESCALA(:ESCALA_ID, :SALDO_MINUTOS, :DATA_FIM, :CONTADOR);
  end

  return DATA_FIM;
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure SPC_PREVISAO_FIM_CASO (
    IPAINEL_ID integer not null,
    ICASO_ID integer not null,
    ITIPO integer = 0,
    ITAREFA_ID integer = 0)
returns (
    DATA timestamp)
as
declare variable SEQUENCIA_TAREFA integer;
declare variable MINUTOS integer;
declare variable ESCALA_ID integer;
BEGIN
    :ITIPO = COALESCE(:ITIPO, 0);
    :ITAREFA_ID = COALESCE(:ITAREFA_ID, 0);
    IF (:ITAREFA_ID > 0) THEN
    BEGIN
        :ITIPO = 0;
    END


    :DATA = COALESCE((SELECT FIRST 1 T.DATAHORA_REGISTRO FROM TBCASO_REGISTRO T WHERE T.ID = :ICASO_ID), NULL);
    :SEQUENCIA_TAREFA = COALESCE((SELECT FIRST 1 A.SEQUENCIA FROM TBWORKFLOW_ITEM_TAREFA A WHERE A.ID = :ITAREFA_ID), 1);

    FOR
        SELECT
            CAST(B.TEMPO_PREVISTO AS INTEGER) AS MINUTOS,
            B.ESCALA_ID

        FROM TBWORKFLOW_ITEM_TAREFA A, TBWORKFLOW_TAREFA B
        WHERE A.WORKFLOW_ID = B.WORKFLOW_ID
        AND A.WORKFLOW_TAREFA_MODELO_ID = B.ID
        AND A.WORKFLOW_ITEM_ID = COALESCE((SELECT FIRST 1
                                                X2.WORKFLOW_ITEM_ID
                                            FROM TBCASO_REGISTRO T, TBCASO_REGISTRO_WORKFLOWS X2
                                            WHERE T.ID = X2.REGISTRO_ID
                                            AND T.PAINEL_ID = X2.PAINEL_ID
                                            AND T.ID = :ICASO_ID
                                            AND T.PAINEL_ID = :IPAINEL_ID

                                            ORDER BY X2.ORDEM), 0)

        AND IIF( :ITIPO = 1, A.STATUS_CONCLUSAO <> 3, TRUE )
        AND IIF( :ITIPO = 1, A.STATUS_CONCLUSAO <> 4, TRUE )
        AND IIF( :ITAREFA_ID > 0, A.SEQUENCIA <= :SEQUENCIA_TAREFA, TRUE )

        ORDER BY A.SEQUENCIA

    INTO
        :MINUTOS,
        :ESCALA_ID

    DO
    BEGIN

        :DATA = FN_CALC_FIM_ESCALA(:ESCALA_ID, :MINUTOS, :DATA);

    END

    SUSPEND;
END
^

SET TERM ; ^

COMMENT ON PARAMETER SPC_PREVISAO_FIM_CASO.ITIPO IS
'0 - Tempo Total; 1 - Tempo Previsto Restante;';

COMMENT ON PARAMETER SPC_PREVISAO_FIM_CASO.ITAREFA_ID IS
'Se definido, retorna somente a data final da tarefa';

