/******************************************************************************/
/****              Generated by IBExpert 08/07/2022 09:05:50               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBCAIXABANCO_CONCILIACAO_BLOQ;

CREATE TABLE TBCAIXABANCO_CONCILIACAO_BLOQ (
    DATAHORA      TIMESTAMP DEFAULT 'NOW' NOT NULL,
    ID            INTEGER NOT NULL,
    DATA          DATE DEFAULT 'NOW',
    STATUS        VARCHAR(1) DEFAULT '1',
    BANCO_CONTA   INTEGER,
    BANCO_CODIGO  INTEGER,
    USUARIO_ID    INTEGER
);

/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBCAIXABANCO_CONCILIACAO_BLOQ ADD PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBCAIXABANCO_CONC_BLOQ_01I ON TBCAIXABANCO_CONCILIACAO_BLOQ (BANCO_CODIGO);
CREATE INDEX ITBCAIXABANCO_CONC_BLOQ_02I ON TBCAIXABANCO_CONCILIACAO_BLOQ (BANCO_CONTA);
CREATE INDEX ITBCAIXABANCO_CONC_BLOQ_03I ON TBCAIXABANCO_CONCILIACAO_BLOQ (DATA);
CREATE INDEX ITBCAIXABANCO_CONC_BLOQ_04I ON TBCAIXABANCO_CONCILIACAO_BLOQ (USUARIO_ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/


/* Trigger: TTBCAIXABANCO_CONC_HIST_B0I */
CREATE OR ALTER TRIGGER TTBCAIXABANCO_CONC_BLOQ_B0I FOR TBCAIXABANCO_CONCILIACAO_BLOQ
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF(NEW.ID IS NULL) THEN
  BEGIN
    NEW.ID = GEN_ID(GTBCAIXABANCO_CONCILIACAO_BLOQ,1);
  END
  IF(NEW.USUARIO_ID IS NULL)THEN
  BEGIN
     NEW.USUARIO_ID = FN_CURRENT_USER_ID();
  END
END
^


CREATE OR ALTER TRIGGER TTBCAIXABANCO_CONC_BLOQ_B01U FOR TBCAIXABANCO_CONCILIACAO_BLOQ
ACTIVE BEFORE UPDATE POSITION 9
AS
declare variable HISTORICO VARCHAR(8000);
begin
    HISTORICO = '';

    IF(NEW.STATUS <> OLD.STATUS) THEN
    BEGIN
       HISTORICO = HISTORICO||'DATA: '||FORMATDATE(NEW.DATA);
       HISTORICO = HISTORICO||',BCO: '||COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO,4,0)||' - '||B.DESCRICAO FROM TBBANCO B WHERE B.CODIGO = NEW.BANCO_CODIGO),'');
       HISTORICO = HISTORICO||',CONTA: '||NEW.BANCO_CONTA;

       IF(NEW.STATUS = '0')THEN
       BEGIN
            HISTORICO = HISTORICO||' BLOQUEADA';
       END

       IF(NEW.STATUS = '1')THEN
       BEGIN
            HISTORICO = HISTORICO||' DESBLOQUEADA';
       END

       --HISTORICO = HISTORICO||' PELO USUÁRIO: '||COALESCE((SELECT FIRST 1 IIF(CHAR_LENGTH(U.NOME) > 0, U.NOME, U.USUARIO) FROM TBUSUARIO U WHERE U.CODIGO = NEW.USUARIO_ID),'');

    END

    IF(HISTORICO <> '') THEN
    BEGIN
       INSERT INTO TBCAIXABANCO_CONCILIACAO_HIST (
            DATAHORA      /*TIMESTAMP DEFAULT 'NOW' NOT NULL*/,
            ID            /*INTEGER NOT NULL*/,
            DATA          /*DATE DEFAULT 'NOW'*/,
            HISTORICO     /*VARCHAR(1000)*/,
            BANCO_CONTA   /*INTEGER*/,
            BANCO_CODIGO  /*INTEGER*/,
            USUARIO_ID    /*INTEGER*/
       ) VALUES (
            CURRENT_TIMESTAMP,
            NULL,
            NEW.DATA,
            :HISTORICO,
            NEW.BANCO_CONTA,
            NEW.BANCO_CODIGO,
            FN_CURRENT_USER_ID()
       );
    END
END^

SET TERM ; ^