ALTER TABLE TMPPROGRAMACAO_REGISTRO
    ADD PROGRAMACAO_ID INTEGER;

SET TERM ^ ;

CREATE OR ALTER procedure SPC_ANALISE_TEMPO_ESTACAO (
    IGP_ID integer,
    IUP_ID integer = 0,
    IESTACAO integer = 0,
    DATA date = current_date,
    TURNO integer = 0)
returns (
    DISPONIVEL integer,
    TRABALHADO integer,
    PARADO integer)
as
declare variable GP_ID integer;
declare variable UP_ID integer;
declare variable DATA1 timestamp;
declare variable DATA2 timestamp;
declare variable DATAHORA timestamp;
declare variable FLAG integer;
declare variable ESTACAO integer;
declare variable IDATA1 timestamp;
declare variable IDATA2 timestamp;
begin

  delete from TMPPROGRAMACAO_REGISTRO;

  if(coalesce(TURNO,0) = 0)then
  begin
      select
        (cast(:DATA as date)+min(p.hora_inicio)) as DATA1,
         dateadd(day, 1,  dateadd(minute, -1, cast(:DATA as date)+min(p.hora_inicio))) as DATA2
      from tbturno_producao p where p.dia_semana = extract(weekday from cast(:DATA as date))
      into :IDATA1, :IDATA2;
  end else
  begin
      select first 1
        (cast(:DATA as date)+min(p.hora_inicio)) as DATA1,
        iif(p.hora_inicio > p.hora_fim,  dateadd(day, 1,  cast(:DATA as date)+min(p.hora_fim)), cast(:DATA as date)+min(p.hora_fim)) as DATA2
      from tbturno_producao p where p.dia_semana = extract(weekday from cast(:DATA as date))
      and p.codigo = :TURNO
      group by p.hora_inicio,p.hora_fim
      into :IDATA1, :IDATA2;
  end

  IDATA2 = iif(DATA = current_date, current_timestamp, IDATA2);

  for
    select
        gp_id,
        up_id,
        estacao,
        datahora1,
        datahora2
    from(
        select
            gp_id,
            up_id,
            estacao,
            datahora1,
            datahora2,
            cast(datahora1 as date) as D1,
            cast(datahora2 as date) as D2
        from(
        
            SELECT
                x.programacao_id,
                lead(x.status) over (partition by x.programacao_id order by x.programacao_id,x.datahora ascending ) STATUS2,
                lead(x.datahora) over (partition by x.programacao_id order by x.programacao_id,x.datahora ascending ) datahora2,
                x.status as status1,
                x.datahora as datahora1,
                u.up_id,
                u.estacao as estacao,
                u.gp_id
            FROM tbprogramacao_registro x,
                 tbprogramacao u
            WHERE x.status in (0,1,2)
            and x.requisicao_id = 0
            and u.id = x.programacao_id
            and u.gp_id = :IGP_ID
            and u.up_id = :IUP_ID
            and u.estacao = :IESTACAO
            and x.datahora between dateadd(day, -2, :IDATA1) and dateadd(day, 2, :IDATA2)
            and coalesce(u.ferramenta_id,0) = 0
            
            order by x.programacao_id, x.datahora ascending
        
        ) y where (y.STATUS1 = 0 and (y.STATUS2 = 1 or y.STATUS2 = 2))
    
    )

  into
    :GP_ID,
    :UP_ID,
    :ESTACAO,
    :DATA1,
    :DATA2
  do begin

    insert into TMPPROGRAMACAO_REGISTRO (
        GP_ID,
        UP_ID,
        ESTACAO,
        DATA_INICIAL,
        DATA_FINAL
    ) values (
        :GP_ID,
        :UP_ID,
        :ESTACAO,
        FN_DATA_EXATA(:DATA1,1),
        FN_DATA_EXATA(:DATA2,2)
    );

  end

  DISPONIVEL = 0;
  TRABALHADO = 0;
  PARADO     = 0;

  for
    select
        FN_DATA_EXATA(DATAHORA)
    from spc_calendario_estacao(:IUP_ID,:IESTACAO,:IDATA1,:IDATA2)
    where PRODUTIVO = '1'
  into
    DATAHORA
  do begin
     DISPONIVEL = DISPONIVEL + 1;

     FLAG = coalesce((select first 1 1 from TMPPROGRAMACAO_REGISTRO f where :DATAHORA between f.data_inicial and f.data_final),0);

     if(FLAG = 1)then
     begin
        TRABALHADO = TRABALHADO + 1;
     end else
     begin
        PARADO = PARADO + 1;
     end
  end

  suspend;
end
^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure SPC_TEMPO_ESTACAO_JUST_DET (
    IGP_ID integer,
    IUP_ID integer = 0,
    IESTACAO integer = 0,
    DATA date = current_date,
    TURNO integer = 0)
returns (
    PARADO numeric(15,2),
    JUSTIFICATIVA integer,
    PROGRAMACAO_ID integer,
    OPERADOR_ID integer,
    REQUISICAO_ID integer,
    DATA_INICIO timestamp,
    DATA_FIM timestamp,
    DATA_CALENDARIO timestamp)
as
declare variable GP_ID integer;
declare variable UP_ID integer;
declare variable DATA1 timestamp;
declare variable DATA2 timestamp;
declare variable DATAHORA timestamp;
declare variable FLAG1 integer;
declare variable ESTACAO integer;
declare variable IDATA1 timestamp;
declare variable IDATA2 timestamp;
declare variable JUSTIFICATIVA_ID integer;
declare variable FLAG2 integer;
declare variable OLD_PROGRAMACAO_ID integer;
declare variable CONT integer;
declare variable OLD_DATAHORA timestamp;
declare variable NEXT_DATAHORA timestamp;
begin

  delete from TMPPROGRAMACAO_REGISTRO;
  delete from tmpprogramacao_registro_just;

  if(coalesce(TURNO,0) = 0)then
  begin
      select
        (cast(:DATA as date)+min(p.hora_inicio)) as DATA1,
         dateadd(day, 1,  dateadd(minute, -1, cast(:DATA as date)+min(p.hora_inicio))) as DATA2
      from tbturno_producao p where p.dia_semana = extract(weekday from cast(:DATA as date))
      into :IDATA1, :IDATA2;
  end else
  begin
      select first 1
        (cast(:DATA as date)+min(p.hora_inicio)) as DATA1,
        iif(p.hora_inicio > p.hora_fim,  dateadd(day, 1,  cast(:DATA as date)+min(p.hora_fim)), cast(:DATA as date)+min(p.hora_fim)) as DATA2
      from tbturno_producao p where p.dia_semana = extract(weekday from cast(:DATA as date))
      and p.codigo = :TURNO
      group by p.hora_inicio,p.hora_fim
      into :IDATA1, :IDATA2;
  end

  IDATA2 = iif(DATA = current_date, current_timestamp, IDATA2);

  for
    select
        PROGRAMACAO_ID,
        gp_id,
        up_id,
        estacao,
        datahora1,
        datahora2
    from(
        select
            PROGRAMACAO_ID,
            gp_id,
            up_id,
            estacao,
            datahora1,
            datahora2,
            cast(datahora1 as date) as D1,
            cast(datahora2 as date) as D2
        from(
            SELECT
                x.programacao_id,
                lead(x.status) over (partition by x.programacao_id order by x.programacao_id,x.datahora ascending ) STATUS2,
                lead(x.datahora) over (partition by x.programacao_id order by x.programacao_id,x.datahora ascending ) datahora2,
                x.status as status1,
                x.datahora as datahora1,
                u.up_id,
                u.estacao as estacao,
                u.gp_id
            FROM tbprogramacao_registro x,
                 tbprogramacao u
            WHERE x.status in (0,1,2)
            and x.requisicao_id = 0
            and u.id = x.programacao_id
            and u.gp_id = :IGP_ID
            and u.up_id = :IUP_ID
            and u.estacao = :IESTACAO
            and x.datahora between dateadd(day, -2, :IDATA1) and dateadd(day, 2, :IDATA2)
            and coalesce(u.ferramenta_id,0) = 0
            
            order by x.programacao_id, x.datahora ascending
        
        ) y where (y.STATUS1 = 0 and (y.STATUS2 = 1 or y.STATUS2 = 2))
    )
  into
    :PROGRAMACAO_ID,
    :GP_ID,
    :UP_ID,
    :ESTACAO,
    :DATA1,
    :DATA2
  do begin

    insert into TMPPROGRAMACAO_REGISTRO (
        GP_ID,
        UP_ID,
        ESTACAO,
        DATA_INICIAL,
        DATA_FINAL,
        PROGRAMACAO_ID
    ) values (
        :GP_ID,
        :UP_ID,
        :ESTACAO,
        FN_DATA_EXATA(:DATA1,1),
        FN_DATA_EXATA(:DATA2,2),
        :PROGRAMACAO_ID
    );

  end

  for
    
        select
            programacao_id,
            operador_id,
            requisicao_id,
            gp_id,
            up_id,
            estacao,
            datahora1,
            datahora2,
            justificativa_id
        from(
        
            SELECT
                x.programacao_id,
                x.operador_id,
                x.requisicao_id,
                lead(x.status) over (partition by x.programacao_id order by x.programacao_id,x.datahora ascending ) STATUS2,
                lead(x.datahora) over (partition by x.programacao_id order by x.programacao_id,x.datahora ascending ) datahora2,
                x.status as status1,
                x.datahora as datahora1,
                u.up_id,
                u.estacao as estacao,
                u.gp_id,
                x.justificativa_id

            FROM tbprogramacao_registro x,
                 tbprogramacao u
            WHERE x.status in (0,1)
            and x.requisicao_id = 0
            and u.id = x.programacao_id
            and u.gp_id = :IGP_ID
            and u.up_id = :IUP_ID
            and u.estacao = :IESTACAO
            and x.datahora between dateadd(day, -2, :IDATA1) and dateadd(day, 2, :IDATA2)
            and coalesce(u.ferramenta_id,0) = 0
            
            order by x.programacao_id, x.datahora ascending
        
        ) y where (y.STATUS1 = 1 and y.STATUS2 = 0)-- and justificativa_id > 0

  into
    :programacao_id,
    :operador_id,
    :requisicao_id,
    :GP_ID,
    :UP_ID,
    :ESTACAO,
    :DATA1,
    :DATA2,
    :JUSTIFICATIVA_ID
  do begin

    insert into tmpprogramacao_registro_just (
        programacao_id,
        operador_id,
        requisicao_id,
        GP_ID,
        UP_ID,
        ESTACAO,
        DATA_INICIAL,
        DATA_FINAL,
        JUSTIFICATIVA_ID
    ) values (
        :programacao_id,
        :operador_id,
        :requisicao_id,
        :GP_ID,
        :UP_ID,
        :ESTACAO,
        FN_DATA_EXATA(:DATA1,2),
        FN_DATA_EXATA(:DATA2,1),
        :JUSTIFICATIVA_ID
    );

  end

  OLD_PROGRAMACAO_ID = 1;
  CONT = 0;

  for
    SELECT
        DATAHORA,
        lead(DATAHORA) over()
    FROM(
        select
            FN_DATA_EXATA(DATAHORA) as DATAHORA
        from spc_calendario_estacao(:IUP_ID,:IESTACAO,:IDATA1,:IDATA2)
        where PRODUTIVO = '1'
    )
  into
    DATAHORA,
    NEXT_DATAHORA
  do begin

     programacao_id = 0;
     operador_id = 0;
     FLAG2 = 0;

     DATA_INICIO = null;
     DATA_FIM = null;

     FLAG1 = coalesce((select first 1
        1 as FLAG1
     from TMPPROGRAMACAO_REGISTRO f
     where :DATAHORA between f.data_inicial and f.data_final),0);

     select first 1
        f.justificativa_id,
        f.programacao_id,
        f.operador_id,
        f.data_inicial,
        f.data_final
     from TMPPROGRAMACAO_REGISTRO_JUST f
     where :DATAHORA between f.data_inicial and f.data_final
     into FLAG2, programacao_id, operador_id, DATA_INICIO, DATA_FIM;

     if(programacao_id = 0)then
     begin
        DATA_INICIO = DATAHORA;
        DATA_FIM    = DATAHORA;
     end

     if(FLAG1 = 0)then
     begin
        JUSTIFICATIVA = FLAG2;
        PARADO = 1;

        if(programacao_id = 0)then
        begin
            
             if( DATAHORA > dateadd(minute, 1, OLD_DATAHORA))then
             begin
                CONT = CONT + 1;
             end

            programacao_id = CONT;
        end

        DATA_CALENDARIO = DATAHORA;

        OLD_DATAHORA = DATAHORA;
        suspend;
     end

     if(FLAG1 = 0 and programacao_id > 0)then
     begin
        OLD_PROGRAMACAO_ID = programacao_id;
     end else
     begin
        OLD_PROGRAMACAO_ID = 0;
     end


  end


end
^

SET TERM ; ^

