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;
declare variable FERIADO integer;
declare variable PULAR_FERIADO integer;
begin
  CONTADOR = coalesce(CONTADOR,0) + 1;

  DIA_INICIO    = extract(weekday from DATA_HORA_INICIO) + 1;
  DATA_FIM      = DATA_HORA_INICIO;
  SALDO_MINUTOS = MINUTOS;
  DIA_TRATANDO  = DIA_INICIO;
  PULAR_FERIADO = 0;

  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


    if (PULAR_FERIADO > 0 and PULAR_FERIADO = DIA_SEMANA) then
    begin
        
--        exception "..." PULAR_FERIADO;
        PULAR_FERIADO = 0;
        DIA_TRATANDO = DIA_SEMANA;
    end else
    begin


    
         -- se nao for feriado, segue a condicao abaixo
         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
    
            -- condicao que ignora o if para desconsiderar feriados na previsao
        :FERIADO = COALESCE((SELECT FIRST 1 PF.ID FROM TBPONTO_FERIADO PF WHERE PF.DATA = cast(:DATA_FIM as date)), 0);

        if (:FERIADO > 0) then
        begin
    
            DATA_FIM = cast(dateadd(day,1,cast(:DATA_FIM as date)) as timestamp);
            PULAR_FERIADO = DIA_SEMANA;
            DIA_TRATANDO  = DIA_SEMANA;
        end else
        begin

            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
    end
  end

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

    :FERIADO = COALESCE((SELECT FIRST 1 PF.ID FROM TBPONTO_FERIADO PF WHERE PF.DATA = cast(:DATA_FIM as date)), 0);

        if (:FERIADO > 0) then
        begin
            DATA_FIM = cast(dateadd(day,1,cast(:DATA_FIM as date)) as timestamp);
        end

    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';

