SET TERM ^ ;

CREATE OR ALTER trigger ttbregistro_setup_02 for tbregistro_setup
active before update position 0
AS
begin

  if(new.setup_id = 3 and new.datahora_fim is not null)then
  begin

    update tbregistro_setup s set
    s.datahora_fim = COALESCE(dateadd(minute,(select first 1 p.tempo_setup_ferramenta from  tbprogramacao p where p.tabela_id = s.talao_id),s.datahora_inicio),CURRENT_TIMESTAMP)
    where s.talao_id = new.talao_id
    and s.requisicao_id = new.requisicao_id
    and s.setup_id = 1;

    update tbregistro_setup s set
    s.datahora_fim = COALESCE(dateadd(minute,(select first 1 p.tempo_setup_aquecimento from  tbprogramacao p where p.tabela_id = s.talao_id),s.datahora_inicio),CURRENT_TIMESTAMP)
    where s.talao_id = new.talao_id
    and s.requisicao_id = new.requisicao_id
    and s.setup_id = 2;

  end

end
^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER trigger ttbregistro_setup_a01u for tbregistro_setup
active after update position 0
AS
begin
  --corrigir setups errados com o tempo padrao
  if((extract(year from new.datahora_inicio) <> extract(year from new.datahora_fim)) and  (new.datahora_inicio is not null))then
  begin

     if(new.setup_id = 1)then
     begin
        update tbregistro_setup s set
        s.datahora_inicio = current_timestamp,
        s.datahora_fim = dateadd(minute,(select first 1 p.tempo_setup_ferramenta from  tbprogramacao p where p.tabela_id = s.talao_id),current_timestamp)
        where s.talao_id = new.talao_id
        and s.setup_id = 1;
     end

     if(new.setup_id = 2)then
     begin
        update tbregistro_setup s set
        s.datahora_inicio = current_timestamp,
        s.datahora_fim = dateadd(minute,(select first 1 p.tempo_setup_aquecimento from  tbprogramacao p where p.tabela_id = s.talao_id),current_timestamp)
        where s.talao_id = new.talao_id
        and s.requisicao_id = new.requisicao_id
        and s.setup_id = 2;
     end

     if(new.setup_id = 3)then
     begin
        update tbregistro_setup s set
        s.datahora_inicio = current_timestamp,
        s.datahora_fim = dateadd(minute,(select first 1 p.tempo_setup_aprovacao from  tbprogramacao p where p.tabela_id = s.talao_id),current_timestamp)
        where s.talao_id = new.talao_id
        and s.requisicao_id = new.requisicao_id
        and s.setup_id = 3;
     end

  end
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure AJUSTAR_SETUP_TALAO (
    I_TALAO_ID integer,
    I_SETUP_1 integer,
    I_SETUP_2 integer,
    I_SETUP_3 integer,
    I_DATA timestamp,
    I_REQUISICAO integer,
    I_REQUISICAO_ID integer)
as
declare variable V_ID integer;
declare variable V_SETUP_ID integer;
declare variable V_TALAO_ID integer;
declare variable V_DATAHORA_INICIO timestamp;
declare variable V_DATAHORA_FIM timestamp;
declare variable V_TEMPO numeric(18,9);
declare variable V_FIM integer;
declare variable V_DATAHORA_INICIO_A timestamp;
declare variable V_DATAHORA_FIM_A timestamp;
declare variable V_1 integer;
declare variable V_2 integer;
declare variable V_3 integer;
declare variable V_REQUISICAO_ID integer;
begin

    :V_DATAHORA_INICIO_A = null;
    :V_DATAHORA_FIM_A    = null;

    :V_1 = 0;
    :V_2 = 0;
    :V_3 = 0;

  for

    select
    
        n.ID,
        n.SETUP_ID,
        n.TALAO_ID,
        n.DATAHORA_INICIO,
        n.DATAHORA_FIM,
        coalesce(n.datahora_fim - n.DATAHORA_INICIO,0) as TEMPO,
        iif(n.datahora_fim is null,0,1) FIM,
        n.requisicao_id
    
    from TBREGISTRO_SETUP n
    where n.talao_id = :I_TALAO_ID
    AND N.requisicao_id = :I_REQUISICAO_ID

    order by n.setup_id, n.id desc

  into
      :V_ID,
      :V_SETUP_ID,
      :V_TALAO_ID,
      :V_DATAHORA_INICIO,
      :V_DATAHORA_FIM,
      :V_TEMPO,
      :V_FIM,
      :v_requisicao_id
  do
  begin
    
    if(:V_SETUP_ID = 1)then
    begin
        :V_1 = 1;
    end
    if(:V_SETUP_ID = 2)then
    begin
        :V_2 = 1;
    end
    if(:V_SETUP_ID = 3)then
    begin
        :V_3 = 1;
    end

    if(V_DATAHORA_FIM is null)then
    begin

       if(:V_SETUP_ID = 1 and :I_SETUP_1 > 0)then
       begin
            update tbregistro_setup n set n.datahora_fim = (n.datahora_inicio + (:I_SETUP_1 * (1.000000000 / (24 * 60)) ))  where n.talao_id = :i_talao_id and n.requisicao_id = :I_REQUISICAO_ID;
       end

       if(:V_SETUP_ID = 2 and :I_SETUP_2 > 0)then
       begin
            update tbregistro_setup n set n.datahora_fim = (n.datahora_inicio + (:I_SETUP_2 * (1.000000000 / (24 * 60)) ))  where n.talao_id = :i_talao_id and n.requisicao_id = :I_REQUISICAO_ID;
       end

       if(:V_SETUP_ID = 3 and :I_SETUP_3 > 0)then
       begin
            update tbregistro_setup n set n.datahora_fim = (n.datahora_inicio + (:I_SETUP_3 * (1.000000000 / (24 * 60)) ))  where n.talao_id = :i_talao_id and n.requisicao_id = :I_REQUISICAO_ID;
       end

    end

    if(:V_SETUP_ID = 1 and :I_SETUP_1 > 0)then
    begin
        :V_DATAHORA_FIM_A    = (:V_DATAHORA_INICIO + (:I_SETUP_1 * (1.000000000 / (24 * 60)) ));
    END

    if(:V_SETUP_ID = 2 and :I_SETUP_2 > 0)then
    begin
        :V_DATAHORA_FIM_A    = (:V_DATAHORA_INICIO + (:I_SETUP_2 * (1.000000000 / (24 * 60)) ));
    END

    if(:V_SETUP_ID = 3 and :I_SETUP_3 > 0)then
    begin
        :V_DATAHORA_FIM_A    = (:V_DATAHORA_INICIO + (:I_SETUP_3 * (1.000000000 / (24 * 60)) ));
    END

  end
        if(:V_DATAHORA_FIM_A IS NULL)then
        begin
            :V_DATAHORA_FIM_A = I_DATA;
        END

        if((:I_SETUP_1 > 0) and (:V_1 = 0))then
        begin
            INSERT INTO TBREGISTRO_SETUP (SETUP_ID, DATAHORA_INICIO, datahora_fim, TALAO_ID, REQUISICAO, REQUISICAO_ID)
            VALUES (
                1,
                (:V_DATAHORA_FIM_A + (1.000000000 / (24 * 60 * 60))),
                (:V_DATAHORA_FIM_A + (:I_SETUP_1 * (1.000000000 / (24 * 60)) )),
                 :I_TALAO_ID,
                 :I_REQUISICAO,
                 :I_REQUISICAO_ID
            );

            :V_DATAHORA_FIM_A    = (:V_DATAHORA_FIM_A + (:I_SETUP_1 * (1.000000000 / (24 * 60)) ));
        end

        if((:I_SETUP_2 > 0) and (:V_2 = 0))then
        begin
            INSERT INTO TBREGISTRO_SETUP (SETUP_ID, DATAHORA_INICIO, datahora_fim, TALAO_ID, REQUISICAO, REQUISICAO_ID)
            VALUES (
                2,
                (:V_DATAHORA_FIM_A + (1.000000000 / (24 * 60 * 60))),
                (:V_DATAHORA_FIM_A + (:I_SETUP_2 * (1.000000000 / (24 * 60)) )),
                 :I_TALAO_ID,
                 :I_REQUISICAO,
                 :I_REQUISICAO_ID
            );

            :V_DATAHORA_FIM_A    = (:V_DATAHORA_FIM_A + (:I_SETUP_2 * (1.000000000 / (24 * 60)) ));
        end

        if((:I_SETUP_3 > 0) and (:V_3 = 0))then
        begin
            INSERT INTO TBREGISTRO_SETUP (SETUP_ID, DATAHORA_INICIO, datahora_fim, TALAO_ID, REQUISICAO, REQUISICAO_ID)
            VALUES (
                3,
                (:V_DATAHORA_FIM_A + (1.000000000 / (24 * 60 * 60))),
                (:V_DATAHORA_FIM_A + (:I_SETUP_3 * (1.000000000 / (24 * 60)) )),
                 :I_TALAO_ID,
                 :I_REQUISICAO,
                 :I_REQUISICAO_ID
            );

            :V_DATAHORA_FIM_A = (:V_DATAHORA_FIM_A + (:I_SETUP_3 * (1.000000000 / (24 * 60)) ));
        end

end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbregistro_setup_02 for tbregistro_setup
active before update position 0
AS
begin

  if(new.setup_id = 3 and new.datahora_fim is not null)then
  begin

    update tbregistro_setup s set
        s.datahora_fim = COALESCE(dateadd(minute,(select first 1 p.tempo_setup_ferramenta from  tbprogramacao p where p.tabela_id = s.talao_id),s.datahora_inicio),CURRENT_TIMESTAMP)
    where s.talao_id = new.talao_id
    and s.requisicao_id = new.requisicao_id
    and s.setup_id = 1
    and s.datahora_fim is null;

    update tbregistro_setup s set
        s.datahora_fim = COALESCE(dateadd(minute,(select first 1 p.tempo_setup_aquecimento from  tbprogramacao p where p.tabela_id = s.talao_id),s.datahora_inicio),CURRENT_TIMESTAMP)
    where s.talao_id = new.talao_id
    and s.requisicao_id = new.requisicao_id
    and s.setup_id = 2
    and s.datahora_fim is null;

  end

end
^

SET TERM ; ^



