SET TERM ^ ;

CREATE OR ALTER function FN_TIMESTAMP_TO_STRING (
    CAMPO timestamp)
returns varchar(16)
AS
DECLARE VARIABLE DATA_RETURN VARCHAR(16);
BEGIN
    IF (FN_IS_EMPTY(CAMPO) = '') THEN
    BEGIN
        DATA_RETURN = '';
    END ELSE
    BEGIN
        DATA_RETURN = LPAD(EXTRACT(DAY    FROM CAMPO), 2, '0')||'/'||
                      LPAD(EXTRACT(MONTH  FROM CAMPO), 2, '0')||'/'||
                      LPAD(EXTRACT(YEAR   FROM CAMPO), 4, '0')||' '||
                      LPAD(EXTRACT(HOUR   FROM CAMPO), 2, '0')||':'||
                      LPAD(EXTRACT(MINUTE FROM CAMPO), 2, '0');
    END

    RETURN DATA_RETURN;
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbconferencia_b01u for tbconferencia
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);

DECLARE VARIABLE HISTORICO_ID INTEGER;
DECLARE VARIABLE OLD_STATUS VARCHAR(500);
DECLARE VARIABLE NEW_STATUS VARCHAR(500);
DECLARE VARIABLE OLD_USUARIO VARCHAR(500);
DECLARE VARIABLE NEW_USUARIO VARCHAR(500);
BEGIN

    :HISTORICO = '';

    OLD_STATUS  = IIF(OLD.STATUS = '0', 'NAO REALIZADA', IIF(OLD.STATUS = '1', 'INICIADA', IIF(OLD.STATUS = '2', 'FINALIZADA OK', IIF(OLD.STATUS = '3', 'FINALIZADA COM DIVERGÊNCIAS', IIF(OLD.STATUS = '4', 'FATURADA', '')))));
    NEW_STATUS  = IIF(NEW.STATUS = '0', 'NAO REALIZADA', IIF(NEW.STATUS = '1', 'INICIADA', IIF(NEW.STATUS = '2', 'FINALIZADA OK', IIF(NEW.STATUS = '3', 'FINALIZADA COM DIVERGÊNCIAS', IIF(NEW.STATUS = '4', 'FATURADA', '')))));
    OLD_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = OLD.USUARIO_ID), '');
    NEW_USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.USUARIO FROM TBUSUARIO A WHERE A.CODIGO = NEW.USUARIO_ID), '');

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

    IF (OLD.TABELA_ID <> NEW.TABELA_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' CODIGO:'||FN_LPAD(OLD.TABELA_ID, 8, '0')||'->'||FN_LPAD(NEW.TABELA_ID, 8, '0');
    END

    IF ((OLD.DATAHORA IS NULL AND NEW.DATAHORA IS NOT NULL) OR (OLD.DATAHORA IS NOT NULL AND NEW.DATAHORA IS NULL) OR (OLD.DATAHORA <> NEW.DATAHORA)) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.HORA INI:'||FN_TIMESTAMP_TO_STRING(OLD.DATAHORA)||'->'||FN_TIMESTAMP_TO_STRING(NEW.DATAHORA);
    END

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' USUARIO:'||OLD_USUARIO||'->'||NEW_USUARIO;
    END

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

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

    IF ((OLD.DATAHORA_FIM IS NULL AND NEW.DATAHORA_FIM IS NOT NULL) OR (OLD.DATAHORA_FIM IS NOT NULL AND NEW.DATAHORA_FIM IS NULL) OR (OLD.DATAHORA_FIM <> NEW.DATAHORA_FIM)) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.HORA FIM:'||FN_TIMESTAMP_TO_STRING(OLD.DATAHORA_FIM)||'->'||FN_TIMESTAMP_TO_STRING(NEW.DATAHORA_FIM);
    END

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

    -- GRAVAR HISTORICO
    IF (HISTORICO <> '') THEN
    BEGIN
        IF (OLD.TABELA = 'NFE') THEN
        BEGIN
          :HISTORICO_ID = '01'||OLD.TABELA_ID;
        END ELSE IF (OLD.TABELA = 'NFS') THEN
        BEGIN
          :HISTORICO_ID = '02'||OLD.TABELA_ID;
        END ELSE IF (OLD.TABELA = 'PED') THEN
        BEGIN
          :HISTORICO_ID = '03'||OLD.TABELA_ID;
        END
    
        HISTORICO = 'CONFERENCIA ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA', :HISTORICO_ID, FN_ELIPSE(:HISTORICO, 500));
    END
END
^

SET TERM ; ^