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||': DOC. '|| NEW_DOCUMENTO ||' 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 ; ^

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

ALTER TABLE TBCONFERENCIA
    ADD HISTORICO_CONF BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8;

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

SET TERM ^ ;

CREATE OR ALTER trigger ttbconferencia_b01iu for tbconferencia
active before insert or update position 9
AS
BEGIN

    IF (INSERTING) THEN
    BEGIN

        NEW.HISTORICO_CONF = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': STATUS '||

            '<strong>'||
            IIF(NEW.STATUS = 0, 'CONFERENCIA NAO REALIZADA',
            IIF(NEW.STATUS = 1, 'CONFERENCIA INICIADA',
            IIF(NEW.STATUS = 2, 'CONFERENCIA FINALIZADA',
            IIF(NEW.STATUS = 3, 'CONFERENCIA FINALIZADA COM DIVERGENCIAS',
            IIF(NEW.STATUS = 4, 'CONFERENCIA FATURADA',
            IIF(NEW.STATUS = 5, 'CONFERENCIA PARCIALMENTE FATURADA', ''))))))
            ||'</strong>'

        ||'';

    END ELSE
    IF (UPDATING) THEN
    BEGIN

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

            NEW.HISTORICO_CONF = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': STATUS ALTERADO! '||

                '<strong>'||
                IIF(OLD.STATUS = 0, 'CONFERENCIA NAO REALIZADA',
                IIF(OLD.STATUS = 1, 'CONFERENCIA INICIADA',
                IIF(OLD.STATUS = 2, 'CONFERENCIA FINALIZADA',
                IIF(OLD.STATUS = 3, 'CONFERENCIA FINALIZADA COM DIVERGENCIAS',
                IIF(OLD.STATUS = 4, 'CONFERENCIA FATURADA',
                IIF(OLD.STATUS = 5, 'CONFERENCIA PARCIALMENTE FATURADA', ''))))))
                ||'</strong>'

            ||' PARA '||

                '<strong>'||
                IIF(NEW.STATUS = 0, 'CONFERENCIA NAO REALIZADA',
                IIF(NEW.STATUS = 1, 'CONFERENCIA INICIADA',
                IIF(NEW.STATUS = 2, 'CONFERENCIA FINALIZADA',
                IIF(NEW.STATUS = 3, 'CONFERENCIA FINALIZADA COM DIVERGENCIAS',
                IIF(NEW.STATUS = 4, 'CONFERENCIA FATURADA',
                IIF(NEW.STATUS = 5, 'CONFERENCIA PARCIALMENTE FATURADA', ''))))))
                ||'</strong>'

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

        END

    END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbconferencia_item_b01iud for tbconferencia_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 ID_CONFERENCIA INTEGER;

DECLARE VARIABLE OLD_PRODUTO VARCHAR(500);
DECLARE VARIABLE NEW_PRODUTO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (INSERTING) THEN
    BEGIN

        HISTORICO_ATUAL = COALESCE((SELECT FIRST 1 C.HISTORICO_CONF FROM TBCONFERENCIA C WHERE C.ID = NEW.CONFERENCIA_ID), '');

        ID_CONFERENCIA = NEW.CONFERENCIA_ID;

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

        OLD_PRODUTO = '';
        NEW_PRODUTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 7, '0')||' - '||A.DESCRICAO FROM TBPRODUTO A WHERE A.CODIGO = NEW.PRODUTO_ID), '');


        HISTORICO = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': ITEM ADICIONADO! PROD.:'|| '<strong>'|| NEW_PRODUTO ||'</strong> | QTD.CONF.: <strong>'|| FN_FORMAT_NUMBER2(NEW.QTD_CONFERIDA, 0) ||'</strong>';

    END ELSE
    IF (UPDATING) THEN
    BEGIN

        HISTORICO_ATUAL = COALESCE((SELECT FIRST 1 C.HISTORICO_CONF FROM TBCONFERENCIA C WHERE C.ID = OLD.CONFERENCIA_ID), '');

        ID_CONFERENCIA = OLD.CONFERENCIA_ID;

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

        OLD_PRODUTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 7, '0')||' - '||A.DESCRICAO FROM TBPRODUTO A WHERE A.CODIGO = OLD.PRODUTO_ID), '');
        NEW_PRODUTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 7, '0')||' - '||A.DESCRICAO FROM TBPRODUTO A WHERE A.CODIGO = NEW.PRODUTO_ID), '');


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

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

        END

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

            HISTORICO = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': ITEM '|| NEW_PRODUTO ||' QTD.CONFERIDA ALTERADA! '||
    
                '<strong>'|| FN_FORMAT_NUMBER2(OLD.QTD_CONFERIDA, 0) ||'</strong>'
    
            ||' PARA '||
    
                '<strong>'|| FN_FORMAT_NUMBER2(NEW.QTD_CONFERIDA, 0) ||'</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 C.HISTORICO_CONF FROM TBCONFERENCIA C WHERE C.ID = OLD.CONFERENCIA_ID), '');

        ID_CONFERENCIA = OLD.CONFERENCIA_ID;

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

        OLD_PRODUTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 7, '0')||' - '||A.DESCRICAO FROM TBPRODUTO A WHERE A.CODIGO = OLD.PRODUTO_ID), '');
        NEW_PRODUTO = '';


        HISTORICO = '['||FORMATDATETIME(CURRENT_TIMESTAMP)||'] - '||CURRENT_USER||': ITEM REMOVIDO! PROD.:'|| '<strong>'|| OLD_PRODUTO ||'</strong> | QTD.CONF.: <strong>'|| FN_FORMAT_NUMBER2(OLD.QTD_CONFERIDA, 0) ||'</strong>';

    END


    IF (HISTORICO <> '') THEN
    BEGIN

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

        UPDATE TBCONFERENCIA T
            SET T.HISTORICO_CONF = :HISTORICO
            WHERE T.ID = :ID_CONFERENCIA;

    END

END
^

SET TERM ; ^

