ALTER TABLE TBREMESSA_SETOR
    ADD CALCULAR_DATA_PROD INTEGER DEFAULT 0;

SET TERM ^ ;

CREATE OR ALTER trigger ttbremessa_setor_b1000u for tbremessa_setor
active before update position 1000
AS
declare variable gp_id integer;
begin
  if(new.situacao is distinct from old.situacao)then
  begin
    if(new.situacao = 1 and new.esteira_producao = '1')then
    begin

        gp_id = coalesce((select first 1 a.gp_id from  TBHORARIO_GP a where a.gp_id = (select first 1 r.gp_id from tbremessa r where r.numero = new.remessa)),0);

        if(gp_id > 0)then
        begin
            new.data_producao = coalesce((
                    select first 1
                        iif(current_time < a.hora_1,
                            dateadd(day, a.add_dias_ini, dateadd(day, a.add_dias_fim * -1, current_date)),
                            current_date
                        )
                    from TBHORARIO_GP a
                    where a.gp_id = (select first 1 r.gp_id from tbremessa r where r.numero = new.remessa)
                    and iif(a.hora_1 < a.hora_2,
                            current_time between a.hora_1 and a.hora_2,
                            current_time between a.hora_1 and '23:59:59' or current_time between '00:00' and a.hora_2
                    )
                ), current_date);
        end else
        begin
            new.data_producao = coalesce((
                    select first 1
                        iif(current_time < a.hora_1,
                            dateadd(day, a.add_dias_ini, dateadd(day, a.add_dias_fim * -1, current_date)),
                            current_date
                        )
                    from TBHORARIO a
                    where iif(a.hora_1 < a.hora_2,
                            current_time between a.hora_1 and a.hora_2,
                            current_time between a.hora_1 and '23:59:59' or current_time between '00:00' and a.hora_2
                    )
                ), current_date);
        end

        update tbpedido_item_processado p set
            p.data_producao_etapa = new.data_producao,
            p.hora_producao_etapa = current_timestamp
        where p.remessa = new.remessa
        and p.controle = new.talao_id;
    end

    if(new.situacao = 0 and new.esteira_producao = '1')then
    begin
        update tbpedido_item_processado p set
            p.data_producao_etapa = null,
            p.hora_producao_etapa = null
        where p.remessa = new.remessa
        and p.controle = new.talao_id;
    end

  end
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger tbremessa_setor_bu999 for tbremessa_setor
active before update position 999
AS
declare variable gp_id integer;
begin
  if(new.calcular_data_prod = 1)then
  begin
        gp_id = coalesce((select first 1 a.gp_id from  TBHORARIO_GP a where a.gp_id = (select first 1 r.gp_id from tbremessa r where r.numero = new.remessa)),0);

        if(gp_id > 0)then
        begin

            new.data_producao = coalesce((
                    select first 1
                        iif(cast( new.data_hora as time) < a.hora_1,
                            dateadd(day, a.add_dias_ini, dateadd(day, a.add_dias_fim * -1, new.data_hora)),
                            new.data_hora
                        )
                    from TBHORARIO_GP a
                    where a.gp_id = (select first 1 r.gp_id from tbremessa r where r.numero = new.remessa)
                    and iif(a.hora_1 < a.hora_2,
                            cast(new.data_hora as time) between a.hora_1 and a.hora_2,
                            cast(new.data_hora as time) between a.hora_1 and '23:59:59' or cast(new.data_hora as time) between '00:00' and a.hora_2
                    )
                ), new.data_hora);
        end else
        begin
            new.data_producao = coalesce((
                    select first 1
                        iif(cast( new.data_hora as time) < a.hora_1,
                            dateadd(day, a.add_dias_ini, dateadd(day, a.add_dias_fim * -1, new.data_hora)),
                            new.data_hora
                        )
                    from TBHORARIO a
                    where iif(a.hora_1 < a.hora_2,
                            cast(new.data_hora as time) between a.hora_1 and a.hora_2,
                            cast(new.data_hora as time) between a.hora_1 and '23:59:59' or cast(new.data_hora as time) between '00:00' and a.hora_2
                    )
                ), new.data_hora);
        end

        update tbpedido_item_processado p set
            p.data_producao_etapa = new.data_producao,
            p.hora_producao_etapa = new.data_hora
        where p.remessa = new.remessa
        and p.controle = new.talao_id;
  end
end
^

SET TERM ; ^

