/******************************************************************************/
/****              Generated by IBExpert 25/10/2022 10:10:36               ****/
/******************************************************************************/

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



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


CREATE GENERATOR GTBPERFIL_NF_USUARIOS;

CREATE TABLE TBPERFIL_NF_USUARIOS (
    PERFIL                         VARCHAR(10),
    USUARIO_ID                     INTEGER,
    ID                             INTEGER NOT NULL
);




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

ALTER TABLE TBPERFIL_NF_USUARIOS ADD PRIMARY KEY (ID);


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

CREATE INDEX ITBPERFIL_NF_USUARIOS_01 ON TBPERFIL_NF_USUARIOS (USUARIO_ID);
CREATE INDEX ITBPERFIL_NF_USUARIOS_02 ON TBPERFIL_NF_USUARIOS (PERFIL);


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



SET TERM ^ ;



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



/* Trigger: TTBPERFIL_NF_USUARIOS_B01D */
CREATE OR ALTER TRIGGER TTBPERFIL_NF_USUARIOS_B01D FOR TBPERFIL_NF_USUARIOS
ACTIVE BEFORE DELETE POSITION 9
AS
declare variable historico VARCHAR(4000);
declare variable descricao_tipo VARCHAR(400);   
declare variable codigo_perfil integer;
declare variable descricao_perfil VARCHAR(1000);
begin
   HISTORICO        = '';
   codigo_perfil    = COALESCE((select first 1 z.CONTROLE from tbperfil z where z.id = OLD.perfil and z.TABELA = 'NF'),0);
   descricao_perfil = COALESCE((select first 1 z.DESCRICAO from tbperfil z where z.id = OLD.perfil and z.TABELA = 'NF'),'');
   if(OLD.USUARIO_ID > 0) then
   begin
       HISTORICO = HISTORICO||' USUARIO:'||FN_LPAD(OLD.USUARIO_ID,4,0)||' EXCLUIDO.';
   end

   IF(HISTORICO <> '') THEN
   BEGIN
       EXECUTE PROCEDURE SPI_HISTORICO('TBPERFIL', CODIGO_PERFIL, HISTORICO);
   END
end
^


/* Trigger: TTBPERFIL_NF_USUARIOS_B01U */
CREATE OR ALTER TRIGGER TTBPERFIL_NF_USUARIOS_B01U FOR TBPERFIL_NF_USUARIOS
ACTIVE BEFORE UPDATE POSITION 9
AS
DECLARE VARIABLE CODIGO_PERFIL INTEGER;
DECLARE VARIABLE USUARIO       VARCHAR(8000);
DECLARE VARIABLE HISTORICO     VARCHAR(8000);
BEGIN

   HISTORICO = '';
   IF (HISTORICO <> '') THEN
   BEGIN
       USUARIO       = COALESCE((SELECT FIRST 1 B.USUARIO FROM TBUSUARIO B WHERE B.CODIGO = NEW.USUARIO_ID),'');
       CODIGO_PERFIL = COALESCE((SELECT FIRST 1 Z.CONTROLE FROM TBPERFIL Z WHERE Z.ID = NEW.PERFIL AND Z.TABELA = 'NF'),0);
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
      'TBPERFIL', :CODIGO_PERFIL,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       'USUARIO '||:USUARIO||' ALTERADO '||FN_ELIPSES(:HISTORICO,500));
   END
END
^

/* Trigger: TTBPERFIL_NF_USUARIOS_B02I */
CREATE OR ALTER TRIGGER TTBPERFIL_NF_USUARIOS_B02I FOR TBPERFIL_NF_USUARIOS
ACTIVE BEFORE INSERT POSITION 1
AS
declare variable USUARIO VARCHAR(1000);
begin
   USUARIO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO,4,0)||' - '||iif(char_length(coalesce(a.nome,'')) > 0, a.nome,A.USUARIO)
                FROM TBUSUARIO A, TBPERFIL_NF_USUARIOS B
                WHERE B.USUARIO_ID = A.CODIGO 
                  AND A.CODIGO     = NEW.USUARIO_ID
                  AND B.PERFIL     = NEW.PERFIL
                  and B.ID        <> NEW.ID),'');

   IF(USUARIO <> '')THEN
   BEGIN
      EXCEPTION "..."' USUÁRIO '||:USUARIO||' JÁ CADASTRADO PARA O PERFIL SELECIONADO, OPERAÇÃO CANCELADA!';
   END
end
^


/* Trigger: TTBPERFIL_NF_USUARIOS_B03I */
CREATE OR ALTER TRIGGER TTBPERFIL_NF_USUARIOS_B03I FOR TBPERFIL_NF_USUARIOS
ACTIVE BEFORE INSERT POSITION 9
AS
declare variable historico VARCHAR(4000);
declare variable descricao_tipo VARCHAR(400);   
declare variable codigo_perfil integer;
declare variable descricao_perfil VARCHAR(1000);
begin
   HISTORICO        = '';
--   descricao_tipo   = COALESCE((SELECT case
--                           WHEN NEW.TIPO = 1 THEN ' INCLUIDO NA NOTIFICAÇÃO COMERCIAL'
--                           WHEN NEW.TIPO = 2 THEN ' INCLUIDO NA NOTIFICAÇÃO FINANCEIRO'
--                           WHEN NEW.TIPO = 3 THEN ' PERMISSÃO DE CONFIRMAÇÃO COMERCIAL INCLUIDA'
--                           WHEN NEW.TIPO = 4 THEN ' PERMISSÃO DE CONFIRMAÇÃO FINANCEIRO INCLUIDA'
--                           ELSE ''
--                           END AS DESCRICAO_TIPO FROM RDB$DATABASE),'');
   codigo_perfil    = COALESCE((select first 1 z.CONTROLE from tbperfil z where z.id = new.perfil and z.TABELA = 'NF'),0);
   descricao_perfil = COALESCE((select first 1 z.DESCRICAO from tbperfil z where z.id = new.perfil and z.TABELA = 'NF'),'');
   if(new.USUARIO_ID > 0) then
   begin
       HISTORICO = HISTORICO||' USUARIO:'||FN_LPAD(NEW.USUARIO_ID,4,0)||' INCLUIDO';
   end

   IF(HISTORICO <> '') THEN
   BEGIN
       EXECUTE PROCEDURE SPI_HISTORICO('TBPERFIL', codigo_perfil, HISTORICO);
   END
end
^


/* Trigger: TTBPERFIL_NF_USUARIOS_B0I */
CREATE OR ALTER TRIGGER TTBPERFIL_NF_USUARIOS_B0I FOR TBPERFIL_NF_USUARIOS
ACTIVE BEFORE INSERT POSITION 2
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
  BEGIN
    NEW.ID = GEN_ID(GTBPERFIL_NF_USUARIOS,1);
  END
END
^

/* Trigger: TTBPERFIL_PED_USUARIOS_B987IUD 
CREATE OR ALTER TRIGGER TTBPERFIL_NF_USUARIOS_B987IUD FOR TBPERFIL_NF_USUARIOS
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 987
AS
begin
    if(inserting)then execute procedure spi_integracao_mobile('TBPERFIL_NF_USUARIOS',new.id,'I');
    if(updating )then execute procedure spi_integracao_mobile('TBPERFIL_NF_USUARIOS',new.id,'A');
    if(deleting )then execute procedure spi_integracao_mobile('TBPERFIL_NF_USUARIOS',old.id,'D');
end
^*/

SET TERM ; ^
