ALTER TABLE TBCHEQUE
    ADD HISTORICO_SIT BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8;

/****************************************************************************************/
/****************************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcheque_b02iu for tbcheque
active before insert or update position 6
AS
BEGIN

    IF (INSERTING) THEN
    BEGIN

        NEW.HISTORICO_SIT = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': SITUACAO '||

            '<strong>'||
            IIF(NEW.SITUACAO = 0, 'PENDENTE',
            IIF(NEW.SITUACAO = 3, 'PARCELAS BAIXADAS',
            IIF(NEW.SITUACAO = 6, 'CHEQUE DISPONIVEL',
            IIF(NEW.SITUACAO = 1 OR NEW.SITUACAO = 4, 'CHEQUE BAIXADO',
            IIF(NEW.SITUACAO = 2 OR NEW.SITUACAO = 5, 'CHEQUE DEVOLVIDO',
            IIF(NEW.SITUACAO = 7 OR NEW.SITUACAO = 8, 'CHEQUE REEMBOLSADO', ''))))))
            ||'</strong>'

        ||'';

--        EXCEPTION "..." 'INC - HIST: '||COALESCE(NEW.HISTORICO_SIT, '');

    END ELSE
    IF (UPDATING) THEN
    BEGIN

        IF (OLD.SITUACAO IS DISTINCT FROM NEW.SITUACAO) THEN
        BEGIN

            NEW.HISTORICO_SIT = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': SITUACAO ALTERADA! '||

                '<strong>'||
                IIF(OLD.SITUACAO = 0, 'PENDENTE',
                IIF(OLD.SITUACAO = 3, 'PARCELAS BAIXADAS',
                IIF(OLD.SITUACAO = 6, 'CHEQUE DISPONIVEL',
                IIF(OLD.SITUACAO = 1 OR OLD.SITUACAO = 4, 'CHEQUE BAIXADO',
                IIF(OLD.SITUACAO = 2 OR OLD.SITUACAO = 5, 'CHEQUE DEVOLVIDO',
                IIF(OLD.SITUACAO = 7 OR OLD.SITUACAO = 8, 'CHEQUE REEMBOLSADO', ''))))))
                ||'</strong>'

            ||' PARA '||

                '<strong>'||
                IIF(NEW.SITUACAO = 0, 'PENDENTE',
                IIF(NEW.SITUACAO = 3, 'PARCELAS BAIXADAS',
                IIF(NEW.SITUACAO = 6, 'CHEQUE DISPONIVEL',
                IIF(NEW.SITUACAO = 1 OR NEW.SITUACAO = 4, 'CHEQUE BAIXADO',
                IIF(NEW.SITUACAO = 2 OR NEW.SITUACAO = 5, 'CHEQUE DEVOLVIDO',
                IIF(NEW.SITUACAO = 7 OR NEW.SITUACAO = 8, 'CHEQUE REEMBOLSADO', ''))))))
                ||'</strong>'

            ||'<br>'|| ASCII_CHAR(13) || ASCII_CHAR(10) ||''|| COALESCE(OLD.HISTORICO_SIT, '') ||'';

--             EXCEPTION "..." 'UPD ENTR - HIST: '||COALESCE(NEW.HISTORICO_SIT, '');

        END

--        EXCEPTION "..." 'UPD SAID - HIST: '||COALESCE(NEW.HISTORICO_SIT, '');

    END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbcheque_item_b1iud for tbcheque_item
active before insert or update or delete position 9
AS
DECLARE VARIABLE HISTORICO_ATUAL BLOB SUB_TYPE 1 SEGMENT SIZE 80;

DECLARE VARIABLE HISTORICO BLOB SUB_TYPE 1 SEGMENT SIZE 80;
DECLARE VARIABLE TIPO_CHEQUE CHAR(1);
DECLARE VARIABLE ID_CHEQUE INTEGER;

DECLARE VARIABLE OLD_DOCUMENTO VARCHAR(500);
DECLARE VARIABLE NEW_DOCUMENTO VARCHAR(500);
BEGIN

    HISTORICO = '';


    IF (INSERTING) THEN
    BEGIN

        HISTORICO_ATUAL = COALESCE((SELECT FIRST 1 D.HISTORICO_SIT FROM TBCHEQUE D WHERE D.ID = NEW.CHEQUE_ID), '');

        TIPO_CHEQUE = COALESCE((SELECT FIRST 1 D.TIPO FROM TBCHEQUE D WHERE D.ID = NEW.CHEQUE_ID), '');
        ID_CHEQUE = NEW.CHEQUE_ID;

        /**********************************************************************************************************/

        OLD_DOCUMENTO = '';
        NEW_DOCUMENTO = COALESCE(IIF(TIPO_CHEQUE = 'D', (SELECT FIRST 1 FN_LPAD(A.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(A.SERIE, 3, '0')||'-'||FN_LPAD(A.PARCELA, 3, '0') FROM TBNFS_PARCELAMENTO A WHERE A.CONTROLE = NEW.DOCUMENTO_ID), (SELECT FIRST 1 FN_LPAD(B.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(B.SERIE, 3, '0')||'-'||FN_LPAD(B.PARCELA, 3, '0') FROM TBNFE_PARCELAMENTO B WHERE B.CONTROLE = NEW.DOCUMENTO_ID)), '');

        
        HISTORICO = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': DOCUMENTO ADICIONADO! DOC.:'|| '<strong>'|| NEW_DOCUMENTO ||'</strong> | VALOR: <strong>R$ '|| FN_FORMAT_NUMBER2(NEW.VALOR, 2) ||'</strong>';

    END ELSE
    IF (UPDATING) THEN
    BEGIN

        HISTORICO_ATUAL = COALESCE((SELECT FIRST 1 D.HISTORICO_SIT FROM TBCHEQUE D WHERE D.ID = OLD.CHEQUE_ID), '');

        TIPO_CHEQUE = COALESCE((SELECT FIRST 1 D.TIPO FROM TBCHEQUE D WHERE D.ID = OLD.CHEQUE_ID), '');
        ID_CHEQUE = OLD.CHEQUE_ID;

        /**********************************************************************************************************/

        OLD_DOCUMENTO = COALESCE(IIF(TIPO_CHEQUE = 'D', (SELECT FIRST 1 FN_LPAD(A.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(A.SERIE, 3, '0')||'-'||FN_LPAD(A.PARCELA, 3, '0') FROM TBNFS_PARCELAMENTO A WHERE A.CONTROLE = OLD.DOCUMENTO_ID), (SELECT FIRST 1 FN_LPAD(B.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(B.SERIE, 3, '0')||'-'||FN_LPAD(B.PARCELA, 3, '0') FROM TBNFE_PARCELAMENTO B WHERE B.CONTROLE = OLD.DOCUMENTO_ID)), '');
        NEW_DOCUMENTO = COALESCE(IIF(TIPO_CHEQUE = 'D', (SELECT FIRST 1 FN_LPAD(A.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(A.SERIE, 3, '0')||'-'||FN_LPAD(A.PARCELA, 3, '0') FROM TBNFS_PARCELAMENTO A WHERE A.CONTROLE = NEW.DOCUMENTO_ID), (SELECT FIRST 1 FN_LPAD(B.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(B.SERIE, 3, '0')||'-'||FN_LPAD(B.PARCELA, 3, '0') FROM TBNFE_PARCELAMENTO B WHERE B.CONTROLE = NEW.DOCUMENTO_ID)), '');


        IF (OLD.DOCUMENTO_ID IS DISTINCT FROM NEW.DOCUMENTO_ID) THEN
        BEGIN

            HISTORICO = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': DOCUMENTO ALTERADO! '||
    
                '<strong>'|| OLD_DOCUMENTO ||'</strong>'
    
            ||' PARA '||
    
                '<strong>'|| NEW_DOCUMENTO ||'</strong>'
    
            ||''|| IIF( COALESCE(HISTORICO, '') <> '', '<br>'|| ASCII_CHAR(13) || ASCII_CHAR(10), '' ) ||''|| COALESCE(HISTORICO, '') ||'';

        END

        IF (OLD.VALOR IS DISTINCT FROM NEW.VALOR) THEN
        BEGIN

            HISTORICO = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': VALOR ALTERADO! '||
    
                '<strong>R$ '|| FN_FORMAT_NUMBER2(OLD.VALOR, 2) ||'</strong>'
    
            ||' PARA '||
    
                '<strong>R$ '|| FN_FORMAT_NUMBER2(NEW.VALOR, 2) ||'</strong>'
    
            ||''|| IIF( COALESCE(HISTORICO, '') <> '', '<br>'|| ASCII_CHAR(13) || ASCII_CHAR(10), '' ) ||''|| COALESCE(HISTORICO, '') ||'';

        END

    END ELSE
    IF (DELETING) THEN
    BEGIN

        HISTORICO_ATUAL = COALESCE((SELECT FIRST 1 D.HISTORICO_SIT FROM TBCHEQUE D WHERE D.ID = OLD.CHEQUE_ID), '');

        TIPO_CHEQUE = COALESCE((SELECT FIRST 1 D.TIPO FROM TBCHEQUE D WHERE D.ID = OLD.CHEQUE_ID), '');
        ID_CHEQUE = OLD.CHEQUE_ID;

        /**********************************************************************************************************/
        
        OLD_DOCUMENTO = COALESCE(IIF(TIPO_CHEQUE = 'D', (SELECT FIRST 1 FN_LPAD(A.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(A.SERIE, 3, '0')||'-'||FN_LPAD(A.PARCELA, 3, '0') FROM TBNFS_PARCELAMENTO A WHERE A.CONTROLE = OLD.DOCUMENTO_ID), (SELECT FIRST 1 FN_LPAD(B.NUMERO_NOTAFISCAL, 6, '0')||'/'||FN_LPAD(B.SERIE, 3, '0')||'-'||FN_LPAD(B.PARCELA, 3, '0') FROM TBNFE_PARCELAMENTO B WHERE B.CONTROLE = OLD.DOCUMENTO_ID)), '');
        NEW_DOCUMENTO = '';


        HISTORICO = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': DOCUMENTO REMOVIDO! DOC.:'|| '<strong>'|| OLD_DOCUMENTO ||'</strong> | VALOR: <strong>R$ '|| FN_FORMAT_NUMBER2(OLD.VALOR, 2) ||'</strong>';

    END


    IF (HISTORICO <> '') THEN
    BEGIN

        HISTORICO = HISTORICO ||'<br>'|| ASCII_CHAR(13) || ASCII_CHAR(10) ||''|| COALESCE(HISTORICO_ATUAL, '') ||'';

--		  EXCEPTION "..." 'ITEM - HIST: '||COALESCE(HISTORICO, '');

        UPDATE TBCHEQUE T
            SET T.HISTORICO_SIT = :HISTORICO
            WHERE T.ID = :ID_CHEQUE;

    END

END
^

SET TERM ; ^

