CREATE TABLE TBOCORRENCIA_WEB (
    ID INTEGER NOT NULL,
    TIPO_ID INTEGER,
    MOTIVO_ID INTEGER,
    STATUS_ID INTEGER,
    VINCULO_ID INTEGER,
    ASSUNTO VARCHAR(100),
    DESCRICAO BLOB SUB_TYPE 1 SEGMENT SIZE 80);

ALTER TABLE TBOCORRENCIA_WEB
ADD CONSTRAINT PK_TBOCORRENCIA_WEB
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOCORRENCIA_WEB;

create trigger ttbocorrencia_web_b01i for tbocorrencia_web
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web,1);
end;

ALTER TABLE TBOCORRENCIA_WEB
    ADD DATA_ABERTURA TIMESTAMP,
    ADD DATA_FINALIZADO TIMESTAMP;

CREATE TABLE TBOCORRENCIA_WEB_TIPO (
    ID INTEGER NOT NULL,
    STATUS INTEGER,
    DESCRICAO VARCHAR(100));

ALTER TABLE TBOCORRENCIA_WEB_TIPO
ADD CONSTRAINT PK_TBOCORRENCIA_WEB_TIPO
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOCORRENCIA_WEB_TIPO;

create trigger ttbocorrencia_web_tipo_b01i for tbocorrencia_web_tipo
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_tipo,1);
end;

ALTER TABLE TBOCORRENCIA_WEB_TIPO
    ADD TABELA VARCHAR(50),
    ADD SQL_WEB BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    ADD SQL_MOB BLOB SUB_TYPE 1 SEGMENT SIZE 80;

CREATE TABLE TBOCORRENCIA_WEB_STATUS (
    ID INTEGER NOT NULL,
    OCORRENCIA_WEB_TIPO_ID INTEGER,
    STATUS INTEGER,
    DESCRICAO VARCHAR(100),
    COR VARCHAR(20),
    INICIO INTEGER,
    FIM INTEGER);

ALTER TABLE TBOCORRENCIA_WEB_STATUS
ADD CONSTRAINT PK_TBOCORRENCIA_WEB_STATUS
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOCORRENCIA_WEB_STATUS;

create trigger ttbocorrencia_web_status_b01i for tbocorrencia_web_status
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_status,1);
end;

ALTER TABLE TBOCORRENCIA_WEB_TIPO
    ADD EMAIL BLOB SUB_TYPE 1 SEGMENT SIZE 80;

CREATE TABLE TBOCORRENCIA_WEB_FREED (
    ID INTEGER NOT NULL,
    OCORRENCIA_WEB_ID INTEGER,
    DE VARCHAR(300),
    PARA VARCHAR(300),
    EM_COPIA VARCHAR(300),
    EM_COPIA_OCULTA VARCHAR(300),
    DATAHORA TIMESTAMP,
    MENSAGEM BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    ORIGEM INTEGER);

ALTER TABLE TBOCORRENCIA_WEB_FREED
ADD CONSTRAINT PK_TBOCORRENCIA_WEB_FREED
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOCORRENCIA_WEB_FREED;

create trigger ttbocorrencia_web_freed_b01i for tbocorrencia_web_freed
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_freed,1);
end;

ALTER TABLE TBOCORRENCIA_WEB_STATUS
    ADD ABERTURA INTEGER;


CREATE trigger tTBOCORRENCIA_WEB_TIPO_b02i for TBOCORRENCIA_WEB_TIPO
active before insert position 2
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = 'Registro Incluido';

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB_TIPO',new.id, HISTORICO);
    END

END;

CREATE trigger tTBOCORRENCIA_WEB_TIPO_b01u for TBOCORRENCIA_WEB_TIPO
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( old.descricao <> new.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição: '|| old.descricao||' -> '||new.descricao;
    END

    IF ( old.status <> new.status) THEN
    BEGIN
        HISTORICO = HISTORICO||' Status: '|| old.status||' -> '||new.status;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB_TIPO',new.id, HISTORICO);
    END

END;


CREATE trigger tTBOCORRENCIA_WEB_TIPO_b01d for TBOCORRENCIA_WEB_TIPO
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB_TIPO',old.id, HISTORICO);
    END
end;


CREATE OR ALTER trigger ttbocorrencia_web_tipo_b01u for tbocorrencia_web_tipo
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( old.descricao <> new.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição: '|| old.descricao||' -> '||new.descricao;
    END

    IF ( old.status <> new.status) THEN
    BEGIN
        HISTORICO = HISTORICO||' Status: '|| old.status||' -> '||new.status;
    END

    IF ( old.tabela <> new.tabela) THEN
    BEGIN
        HISTORICO = HISTORICO||' Tabela: '|| old.tabela||' -> '||new.tabela;
    END

    IF ( old.email  <> new.email) THEN
    BEGIN
        HISTORICO = HISTORICO||' Email: '|| old.email||' -> '||new.email;
    END

    IF ( old.sql_web <> new.sql_web) THEN
    BEGIN
        HISTORICO = HISTORICO||' sql_web ';
    END

    IF ( old.sql_mob <> new.sql_mob) THEN
    BEGIN
        HISTORICO = HISTORICO||' sql_mob ';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB_TIPO',new.id, HISTORICO);
    END

END;

CREATE TABLE TBOCORRENCIA_WEB_MOTIVO (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(100),
    STATUS INTEGER);

ALTER TABLE TBOCORRENCIA_WEB_MOTIVO
ADD CONSTRAINT PK_TBOCORRENCIA_WEB_MOTIVO
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOCORRENCIA_WEB_MOTIVO;

ALTER TABLE TBOCORRENCIA_WEB_MOTIVO
    ADD OCORRENCIA_WEB_TIPO_ID INTEGER;

ALTER TABLE TBOCORRENCIA_WEB
    ADD STATUS INTEGER DEFAULT 0;

CREATE INDEX TTBOCORRENCIA_WEB_06
ON TBOCORRENCIA_WEB (STATUS_ID,DATA_ABERTURA);

CREATE INDEX TTBOCORRENCIA_WEB_07
ON TBOCORRENCIA_WEB (DATA_FINALIZADO);

CREATE INDEX TTBOCORRENCIA_WEB_05
ON TBOCORRENCIA_WEB (DATA_ABERTURA);

CREATE INDEX TTBOCORRENCIA_WEB_04
ON TBOCORRENCIA_WEB (VINCULO_ID);

CREATE INDEX TTBOCORRENCIA_WEB_03
ON TBOCORRENCIA_WEB (STATUS_ID);

CREATE INDEX TTBOCORRENCIA_WEB_02
ON TBOCORRENCIA_WEB (MOTIVO_ID);

CREATE INDEX TTBOCORRENCIA_WEB_01
ON TBOCORRENCIA_WEB (TIPO_ID);

CREATE TABLE TBOCORRENCIA_WEB_USUARIOS (
    ID INTEGER NOT NULL,
    USUARIO_ID INTEGER,
    OCORRENCIA_WEB_ID INTEGER,
    STATUS_EXCLUSAO INTEGER DEFAULT 0);

ALTER TABLE TBOCORRENCIA_WEB_USUARIOS
ADD CONSTRAINT PK_TBOCORRENCIA_WEB_USUARIOS
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOCORRENCIA_WEB_USUARIOS;

create trigger ttbocorrencia_web_usuarios_b01i for tbocorrencia_web_usuarios
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_usuarios,1);
end;

CREATE INDEX TTBOCORRENCIA_WEB_USUARIOS_3
ON TBOCORRENCIA_WEB_USUARIOS (STATUS_EXCLUSAO);

CREATE INDEX TTBOCORRENCIA_WEB_USUARIOS_02
ON TBOCORRENCIA_WEB_USUARIOS (OCORRENCIA_WEB_ID);

CREATE INDEX TTBOCORRENCIA_WEB_USUARIOS_01
ON TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID);

CREATE OR ALTER trigger ttbocorrencia_web_b01i for tbocorrencia_web
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web,1);


  UPDATE OR INSERT INTO TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID, OCORRENCIA_WEB_ID) VALUES (fn_current_user_id(), new.id) MATCHING (USUARIO_ID, OCORRENCIA_WEB_ID);
end;

CREATE OR ALTER trigger ttbocorrencia_web_freed_b01i for tbocorrencia_web_freed
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_freed,1);

    UPDATE OR INSERT INTO TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID, OCORRENCIA_WEB_ID) VALUES (fn_current_user_id(), new.ocorrencia_web_id) MATCHING (USUARIO_ID, OCORRENCIA_WEB_ID);
end;

CREATE OR ALTER trigger ttbocorrencia_web_b02i for tbocorrencia_web
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = 'Registro Incluido';

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.id, HISTORICO);
    END
end;

CREATE OR ALTER trigger ttbocorrencia_web_b01u for tbocorrencia_web
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( old.descricao <> new.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição: '|| old.descricao||' -> '||new.descricao;
    END

    IF ( old.status_id <> new.status_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Status: '|| old.status_id||' -> '||new.status_id;
    END

    IF ( old.tipo_id <> new.tipo_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Tipo: '|| old.tipo_id||' -> '||new.tipo_id;
    END

    IF ( old.motivo_id  <> new.motivo_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Motivo: '|| old.motivo_id||' -> '||new.motivo_id;
    END

    IF ( old.vinculo_id <> new.vinculo_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Vinculo: '|| old.vinculo_id||' -> '||new.vinculo_id;
    END

    IF ( old.assunto <> new.assunto) THEN
    BEGIN
        HISTORICO = HISTORICO||' Vinculo: '|| old.assunto||' -> '||new.assunto;
    END

    IF ( old.descricao <> new.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição';
    END

    IF ( old.data_abertura <> new.data_abertura) THEN
    BEGIN
        HISTORICO = HISTORICO||' Data Aber.: '|| formatdatetime(old.data_abertura)||' -> '||formatdatetime(new.data_abertura);
    END

    IF ( old.data_finalizado <> new.data_finalizado) THEN
    BEGIN
        HISTORICO = HISTORICO||' Data Fin.: '|| formatdatetime(old.data_finalizado)||' -> '||formatdatetime(new.data_finalizado);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.id, HISTORICO);
    END
end;

CREATE OR ALTER trigger ttbocorrencia_web_freed_b02i for tbocorrencia_web_freed
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = new.id||' - Freed Incluido';

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.ocorrencia_web_id, HISTORICO);
    END

END;

CREATE OR ALTER trigger ttbocorrencia_web_b01d for tbocorrencia_web
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',old.id, HISTORICO);
    END
end;


DROP TRIGGER TBOCORRENCIA_WEB_FREED_BD0;

CREATE OR ALTER trigger ttbocorrencia_web_freed_b01d for tbocorrencia_web_freed
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.id > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||old.id||' - Freed Excluido';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',old.ocorrencia_web_id, HISTORICO);
    END
end;


CREATE OR ALTER trigger ttbocorrencia_web_b03u for tbocorrencia_web
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
declare variable tipo_status integer;
BEGIN

    if(old.status_id <> new.status_id)then
    begin
        tipo_status = coalesce((select first 1 iif(t.inicio = 1, 1, iif(t.fim = 1, 2, iif(t.abertura = 1, 3, 0))) from tbocorrencia_web_status t where t.id =  new.status_id),0);
    
        HISTORICO = '';

        if(tipo_status = 1)then
        begin
            HISTORICO = 'Ocorrência iniciada';
            new.data_finalizado = null;
            new.status = 1;
        end

        if(tipo_status = 2)then
        begin
            HISTORICO = 'Ocorrência Finalizada';
            new.data_finalizado = current_timestamp;
            new.status = 1;
        end

        if(tipo_status = 3)then
        begin
            HISTORICO = 'Ocorrência aberta';
            new.data_finalizado = null;
            new.status = 0;
        end
    
        IF (HISTORICO <> '') THEN
        BEGIN
            EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.id, HISTORICO);
        END
    end
end;

CREATE OR ALTER function fn_to_par_json (
    campo varchar(50),
    valor varchar(50))
returns varchar(250)
as
begin
  /* Function Text */
  return '"'||CAMPO||'":"'|| coalesce(VALOR,'')||'"';
end;

CREATE OR ALTER trigger ttbocorrencia_web_b01i for tbocorrencia_web
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web,1);

  new.data_abertura = current_timestamp;

  UPDATE OR INSERT INTO TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID, OCORRENCIA_WEB_ID) VALUES (fn_current_user_id(), new.id) MATCHING (USUARIO_ID, OCORRENCIA_WEB_ID);
end;

CREATE OR ALTER trigger ttbocorrencia_web_b01u for tbocorrencia_web
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( old.descricao <> new.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição';
    END

    IF ( old.status_id <> new.status_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Status: '|| old.status_id||' -> '||new.status_id;
    END

    IF ( old.tipo_id <> new.tipo_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Tipo: '|| old.tipo_id||' -> '||new.tipo_id;
    END

    IF ( old.motivo_id  <> new.motivo_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Motivo: '|| old.motivo_id||' -> '||new.motivo_id;
    END

    IF ( old.vinculo_id <> new.vinculo_id) THEN
    BEGIN
        HISTORICO = HISTORICO||' Vinculo: '|| old.vinculo_id||' -> '||new.vinculo_id;
    END

    IF ( old.assunto <> new.assunto) THEN
    BEGIN
        HISTORICO = HISTORICO||' Vinculo: '|| old.assunto||' -> '||new.assunto;
    END

    IF ( old.descricao <> new.descricao) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descrição';
    END

    IF ( old.data_abertura <> new.data_abertura) THEN
    BEGIN
        HISTORICO = HISTORICO||' Data Aber.: '|| formatdatetime(old.data_abertura)||' -> '||formatdatetime(new.data_abertura);
    END

    IF ( old.data_finalizado <> new.data_finalizado) THEN
    BEGIN
        HISTORICO = HISTORICO||' Data Fin.: '|| formatdatetime(old.data_finalizado)||' -> '||formatdatetime(new.data_finalizado);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.id, HISTORICO);
    END
end;

ALTER TABLE TBOCORRENCIA_WEB_FREED
    ADD USUARIO_ID INTEGER;

ALTER TABLE TBOCORRENCIA_WEB_FREED
    ADD FECHADO INTEGER DEFAULT 0;

ALTER TABLE TBOCORRENCIA_WEB
    ADD FECHADO INTEGER;

CREATE OR ALTER trigger ttbocorrencia_web_b03u for tbocorrencia_web
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
declare variable tipo_status integer;
BEGIN

    if(old.status_id <> new.status_id)then
    begin
        tipo_status = coalesce((select first 1 iif(t.inicio = 1, 1, iif(t.fim = 1, 2, iif(t.abertura = 1, 3, 0))) from tbocorrencia_web_status t where t.id =  new.status_id),0);
    
        HISTORICO = '';

        if(tipo_status = 1)then
        begin
            HISTORICO = 'Ocorrência iniciada';
            new.data_finalizado = null;
            new.fechado = 0;
            new.status = 1;
        end

        if(tipo_status = 2)then
        begin
            HISTORICO = 'Ocorrência Finalizada';
            new.data_finalizado = current_timestamp;
            new.fechado = 1;
            new.status = 1;
        end

        if(tipo_status = 3)then
        begin
            HISTORICO = 'Ocorrência aberta';
            new.data_finalizado = null;
            new.fechado = 0;
            new.status = 0;
        end
    
        IF (HISTORICO <> '') THEN
        BEGIN
            EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.id, HISTORICO);
        END
    end
end;

CREATE OR ALTER trigger ttbocorrencia_web_freed_b01i for tbocorrencia_web_freed
active before insert position 0
as
declare variable tipo integer;
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_freed,1);

    if(new.usuario_id > 0)then
    begin
        UPDATE OR INSERT INTO TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID, OCORRENCIA_WEB_ID) VALUES (new.usuario_id , new.ocorrencia_web_id) MATCHING (USUARIO_ID, OCORRENCIA_WEB_ID);
    
        tipo = coalesce((select first 1 w.tipo_id from tbocorrencia_web w where w.id = new.ocorrencia_web_id),0);
    
        update tbocorrencia_web e
        set e.status_id = coalesce(
            (select first 1 t.id from tbocorrencia_web_status t where t.inicio = 1 and t.status = 1 and t.ocorrencia_web_tipo_id = :tipo order by t.descricao)
        ,0)
        where e.id = new.ocorrencia_web_id;
    end

end;

ALTER TABLE TBOCORRENCIA_WEB
    ADD USUARIO_ID INTEGER;

CREATE OR ALTER trigger ttbocorrencia_web_b02i for tbocorrencia_web
active before insert position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = 'Registro Incluido';

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.id, HISTORICO);
    END

    INSERT INTO TBOCORRENCIA_WEB_FREED (OCORRENCIA_WEB_ID, DE, PARA, EM_COPIA, EM_COPIA_OCULTA, MENSAGEM, DATAHORA, ORIGEM, USUARIO_ID, FECHADO)
                            VALUES (new.id, '', '', '', '', 'Ocorrência aberta', current_timestamp, 1, 0, 0);
end;


CREATE OR ALTER trigger ttbocorrencia_web_b03u for tbocorrencia_web
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
declare variable tipo_status integer;
BEGIN

    if(old.status_id <> new.status_id)then
    begin
        tipo_status = coalesce((select first 1 iif(t.inicio = 1, 1, iif(t.fim = 1, 2, iif(t.abertura = 1, 3, 0))) from tbocorrencia_web_status t where t.id =  new.status_id),0);
    
        HISTORICO = '';

        if(tipo_status = 1)then
        begin
            HISTORICO = 'Ocorrência iniciada';
            new.data_finalizado = null;
            new.fechado = 0;
            new.status = 1;
        end

        if(tipo_status = 2)then
        begin
            HISTORICO = 'Ocorrência Finalizada';
            new.data_finalizado = current_timestamp;
            new.fechado = 1;
            new.status = 1;

            INSERT INTO TBOCORRENCIA_WEB_FREED (OCORRENCIA_WEB_ID, DE, PARA, EM_COPIA, EM_COPIA_OCULTA, MENSAGEM, DATAHORA, ORIGEM, USUARIO_ID, FECHADO)
                            VALUES (new.id, '', '', '', '', 'Ocorrência fechada', current_timestamp, 1, 0, 0);
        end

        if(tipo_status = 3)then
        begin
            HISTORICO = 'Ocorrência aberta';
            new.data_finalizado = null;
            new.fechado = 0;
            new.status = 0;
        end
    
        IF (HISTORICO <> '') THEN
        BEGIN
            EXECUTE PROCEDURE SPI_HISTORICO('TBOCORRENCIA_WEB',new.id, HISTORICO);
        END
    end
end;

ALTER TABLE TBOCORRENCIA_WEB_FREED
    ADD ASSUNTO VARCHAR(100);

CREATE OR ALTER trigger ttbocorrencia_web_freed_b01i for tbocorrencia_web_freed
active before insert position 0
as
declare variable tipo integer;
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_freed,1);

    if(coalesce(new.usuario_id,0) = 0)then
    begin
       new.usuario_id = fn_current_user_id();
    end

    if(new.usuario_id > 0)then
    begin
        UPDATE OR INSERT INTO TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID, OCORRENCIA_WEB_ID) VALUES (new.usuario_id , new.ocorrencia_web_id) MATCHING (USUARIO_ID, OCORRENCIA_WEB_ID);
    
        tipo = coalesce((select first 1 w.tipo_id from tbocorrencia_web w where w.id = new.ocorrencia_web_id),0);
    
        update tbocorrencia_web e
        set e.status_id = coalesce(
            (select first 1 t.id from tbocorrencia_web_status t where t.inicio = 1 and t.status = 1 and t.ocorrencia_web_tipo_id = :tipo order by t.descricao)
        ,0)
        where e.id = new.ocorrencia_web_id;
    end

end;

CREATE TABLE TBOCORRENCIA_WEB_SQL (
    ID INTEGER NOT NULL,
    OCORRENCIA_WEB_TIPO_ID INTEGER,
	
    SQL_WEB BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    WHERE_WEB1 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
	WHERE_WEB2 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CAMPOS_TABELA_WEB VARCHAR(300),
    CAMPOS_RETORNO_WEB VARCHAR(300),

    SQL_MOB BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    WHERE_MOB1 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
	WHERE_MOB2 BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CAMPOS_TABELA_MOB VARCHAR(300),
    CAMPOS_RETORNO_MOB VARCHAR(300)
    
);

ALTER TABLE TBOCORRENCIA_WEB_SQL
ADD CONSTRAINT PK_TBOCORRENCIA_WEB_SQL
PRIMARY KEY (ID);

CREATE SEQUENCE GTBOCORRENCIA_WEB_SQL;

create trigger ttbocorrencia_web_sql_b01i for tbocorrencia_web_sql
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web_sql,1);
end;

ALTER TABLE TBOCORRENCIA_WEB_SQL
    ADD DESCRICAO_WEB VARCHAR(50),
    ADD DESCRICAO_MOB VARCHAR(50);

ALTER TABLE TBOCORRENCIA_WEB_SQL
    ADD TAMANHO_TABELA INTEGER;

ALTER TABLE TBOCORRENCIA_WEB_SQL
    ADD CAMPOS_SQL_WEB VARCHAR(300),
    ADD CAMPOS_SQL_MOB VARCHAR(300);

CREATE OR ALTER function fn_execute_sql (
    calc blob sub_type 1 segment size 80)
returns blob sub_type 1 segment size 80
as
begin

    EXECUTE STATEMENT 'SELECT FIRST 1 (' || CALC || ') FROM RDB$DATABASE' INTO CALC;

    
    RETURN CALC;
    
    WHEN ANY DO
    BEGIN

    END
end;

create or alter function fn_camel_case (
    palavra varchar(300))
returns varchar(300)
as
declare variable l varchar(300);
declare variable u varchar(5);
begin

  l = lower(:PALAVRA);
  u = upper(substring(:PALAVRA from 1 for 1));

  return u||l;
end;

CREATE OR ALTER trigger ttbregistro_visita_b0i for tbregistro_visita
active before insert position 0
AS
BEGIN
  IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
      NEW.ID = GEN_ID(GTBREGISTRO_VISITA, 1);

  if(coalesce(new.usuario_id, 0) = 0)then
  begin

    if(coalesce(new.dispositivo_id, 0) = 0)then
    begin
        new.usuario_id = coalesce((select first 1 t.usuario_id from TBINTEGRACAO_DISPOSITIVO t where t.id = new.dispositivo_id),0);
    end else
    begin
        new.usuario_id = fn_current_user_id();
    end

  end
END;


CREATE OR ALTER trigger tbocorrencia_web_freed_b03i for tbocorrencia_web_freed
active before insert position 3
AS
declare variable id integer;
declare variable corpo1 blob sub_type 1 segment size 80;
declare variable corpo2 blob sub_type 1 segment size 80;
declare variable corpo3 blob sub_type 1 segment size 80;
declare variable html1 varchar(4000);
declare variable html2 varchar(4000);
declare variable tipo integer;
declare variable linha integer;
declare variable assunto varchar(100);
declare variable descricao blob sub_type 1 segment size 80;
declare variable json blob sub_type 1 segment size 80;
declare variable campos_tabela_web varchar(300);
declare variable campos_sql_web varchar(300);
declare variable descricao_web varchar(300);
declare variable campo varchar(300);
declare variable tipo_descricao varchar(300);
declare variable motivo_descricao varchar(300);
begin

    if(new.origem = 1)then
    begin

        html1 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 13);
        html2 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 14);

        select
            e.tipo_id,
            e.assunto,
            e.descricao,
            fn_execute_sql( replace(s.where_web1,':VINCULO_ID', e.vinculo_id) ) as JSON,
            s.campos_tabela_web,
            s.campos_sql_web,
            s.descricao_web,
            t.descricao,
            m.descricao
        from tbocorrencia_web e,  tbocorrencia_web_sql s, tbocorrencia_web_tipo t, tbocorrencia_web_motivo m
        where e.id = new.ocorrencia_web_id
        and s.ocorrencia_web_tipo_id = e.tipo_id
        and t.id = e.tipo_id
        and m.id = e.motivo_id
        into
            :tipo,
            :assunto,
            :descricao,
            :json,
            :campos_tabela_web,
            :campos_sql_web,
            :descricao_web,
            :tipo_descricao,
            :motivo_descricao;

        corpo1 = '';
        corpo2 = '';
        corpo3 = '';
        
        corpo1 = '<html><head><meta charset="Content-Type: text/html; charset=UTF-8"><style>p {margin: 2px;}</style></head><body>';
        corpo1 = corpo1 ||'<p>'||new.mensagem||'</p>';
        corpo1 = corpo1 || '<br><br>';
        corpo1 = corpo1 || '<p><a href="'||fn_parametro('SISTEMA.URL_SERVER')||'/_26110?TIPO='||:tipo||'&ID='||new.ocorrencia_web_id||'" target="_blank">[ABRIR OCORRÊNCIA NA WEB]</a></p>';
        corpo1 = corpo1 || '<br>';
        corpo1 = corpo1 || html1;
        
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>Ocorrência:</u></p>';
        corpo2 = corpo2||'<p><u>Assunto</u>: '||:assunto||'</p>';
        corpo2 = corpo2||'<p><u>Descrição</u>: '||:descricao||'</p>';
        corpo2 = corpo2||'<br>';
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>'||:descricao_web||'</u></p>';

        for
          select OSPLIT, (row_number() over()) as LINHA from split2(:campos_sql_web,',')
        into
          :campo,
          :linha
        do begin
          corpo2 = corpo2||'<p><u>'|| FN_CAMEL_CASE(fn_array(:campos_tabela_web,:linha,',')) ||'</u>: '||fn_json_v3(:json, :campo)||'</p>';
        end

        corpo3 = corpo3 || html2 || '</body></html>';

        id = Gen_ID(gtbemail,1);

        insert into tbemail (id, status, email, assunto, corpo, usuario_id, cc, cco, painel_id, datahora)
           values (
            :id,
            0,
            new.para,
            :descricao_web ||' [OCORRÊNCIA:'||lpad(new.ocorrencia_web_id,8,'0')||'] - '||FN_CAMEL_CASE(:motivo_descricao),
            :corpo1 || :corpo2 || :corpo3,
            new.usuario_id,
            new.em_copia,
            new.em_copia_oculta,
            new.ocorrencia_web_id,
            current_timestamp
         );
    end
end;

ALTER TABLE TBEMAIL
    ADD DE VARCHAR(300);

CREATE OR ALTER trigger tbocorrencia_web_freed_b03i for tbocorrencia_web_freed
active before insert position 3
AS
declare variable id integer;
declare variable corpo1 blob sub_type 1 segment size 80;
declare variable corpo2 blob sub_type 1 segment size 80;
declare variable corpo3 blob sub_type 1 segment size 80;
declare variable html1 varchar(4000);
declare variable html2 varchar(4000);
declare variable tipo integer;
declare variable linha integer;
declare variable assunto varchar(100);
declare variable descricao blob sub_type 1 segment size 80;
declare variable json blob sub_type 1 segment size 80;
declare variable campos_tabela_web varchar(300);
declare variable campos_sql_web varchar(300);
declare variable descricao_web varchar(300);
declare variable campo varchar(300);
declare variable tipo_descricao varchar(300);
declare variable motivo_descricao varchar(300);
declare variable email varchar(300);
begin

    if(new.origem = 1 and new.usuario_id > 0)then
    begin

        html1 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 13);
        html2 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 14);

        select
            e.tipo_id,
            e.assunto,
            e.descricao,
            fn_execute_sql( replace(s.where_web1,':VINCULO_ID', e.vinculo_id) ) as JSON,
            s.campos_tabela_web,
            s.campos_sql_web,
            s.descricao_web,
            t.descricao,
            m.descricao,
            t.email
        from tbocorrencia_web e,  tbocorrencia_web_sql s, tbocorrencia_web_tipo t, tbocorrencia_web_motivo m
        where e.id = new.ocorrencia_web_id
        and s.ocorrencia_web_tipo_id = e.tipo_id
        and t.id = e.tipo_id
        and m.id = e.motivo_id
        into
            :tipo,
            :assunto,
            :descricao,
            :json,
            :campos_tabela_web,
            :campos_sql_web,
            :descricao_web,
            :tipo_descricao,
            :motivo_descricao,
            :email;

        corpo1 = '';
        corpo2 = '';
        corpo3 = '';
        
        corpo1 = '<html><head><meta charset="Content-Type: text/html; charset=UTF-8"><style>p {margin: 2px;}</style></head><body>';
        corpo1 = corpo1 ||'<p>'||new.mensagem||'</p>';
        corpo1 = corpo1 || '<br><br>';
        corpo1 = corpo1 || '<p><a href="'||fn_parametro('SISTEMA.URL_SERVER')||'/_26110?TIPO='||:tipo||'&ID='||new.ocorrencia_web_id||'" target="_blank">[ABRIR OCORRÊNCIA NA WEB]</a></p>';
        corpo1 = corpo1 || '<br>';
        corpo1 = corpo1 || html1;
        
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>Ocorrência:</u></p>';
        corpo2 = corpo2||'<p><u>Assunto</u>: '||:assunto||'</p>';
        corpo2 = corpo2||'<p><u>Descrição</u>: '||:descricao||'</p>';
        corpo2 = corpo2||'<br>';
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>'||:descricao_web||'</u></p>';

        for
          select OSPLIT, (row_number() over()) as LINHA from split2(:campos_sql_web,',')
        into
          :campo,
          :linha
        do begin
          corpo2 = corpo2||'<p><u>'|| FN_CAMEL_CASE(fn_array(:campos_tabela_web,:linha,',')) ||'</u>: '||fn_json_v3(:json, :campo)||'</p>';
        end

        corpo3 = corpo3 || html2 || '</body></html>';

        id = Gen_ID(gtbemail,1);

        insert into tbemail (id, status, email, assunto, corpo, usuario_id, cc, cco, painel_id, datahora, de)
           values (
            :id,
            0,
            new.para,
            :descricao_web ||' [OCORRÊNCIA:'||lpad(new.ocorrencia_web_id,8,'0')||'] - '||FN_CAMEL_CASE(:motivo_descricao),
            :corpo1 || :corpo2 || :corpo3,
            new.usuario_id,
            new.em_copia,
            new.em_copia_oculta,
            0,
            current_timestamp,
            :email
         );
    end
end;

CREATE OR ALTER procedure sp_notificacao_ocorrencia (
    titulo varchar(50),
    mensagem blob sub_type 1 segment size 80,
    ocorrencia_id integer)
returns (
    usuario_id integer)
as
begin

  for
     select
        u.usuario_id
     from tbocorrencia_web_usuarios u
     where u.ocorrencia_web_id = :ocorrencia_id
     and u.status_exclusao = 0
  into
    :USUARIO_ID
  do begin

        INSERT INTO TBNOTIFICACAO (TIPO, USUARIO_ID, TODOS, MENU_ID, TITULO, LEITURA, ENVIO, DATA_HORA, EMITENTE, AGENDAMENTO, EXECUTADO, TABELA, TABELA_ID, MENSAGEM)
        VALUES (
            6,
            :USUARIO_ID,
            0,
            0,
            :TITULO,
            0,
            0,
            current_timestamp,
            0,
            current_timestamp,
            0,
            'TBOCORRENCIA_WEB',
            :OCORRENCIA_ID,
            :MENSAGEM
        );

    suspend;
  end

end;

CREATE OR ALTER trigger ttbocorrencia_web_b01i for tbocorrencia_web
active before insert position 0
as
declare variable usuario_admin integer;
declare variable email varchar(100);
begin

  if (new.id is null) then
    new.id = gen_id(gtbocorrencia_web,1);

  new.data_abertura = current_timestamp;

  UPDATE OR INSERT INTO TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID, OCORRENCIA_WEB_ID) VALUES (fn_current_user_id(), new.id) MATCHING (USUARIO_ID, OCORRENCIA_WEB_ID);

  for
     select
         OSPLIT
     from split2(coalesce((select t.email from tbocorrencia_web_tipo t where t.id = new.id),''),',')
  into
     :email
  do begin
     usuario_admin = coalesce((select first 1 u.codigo from tbusuario u where trim(u.email) = trim(:email)),0);

     if(usuario_admin > 0)then
     begin
        UPDATE OR INSERT INTO TBOCORRENCIA_WEB_USUARIOS (USUARIO_ID, OCORRENCIA_WEB_ID) VALUES (:usuario_admin, new.id) MATCHING (USUARIO_ID, OCORRENCIA_WEB_ID);
     end
  end

end;

COMMENT ON TRIGGER TTBOCORRENCIA_WEB_B01I IS 'gera id e adiciona usuarios envolvidos nas ocorrencias';


CREATE trigger ttbocorrencia_web_tipo_biud for tbocorrencia_web_tipo
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_TIPO',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_TIPO',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_TIPO',old.id,'D');
end;

CREATE trigger ttbocorrencia_web_usuarios_biud for tbocorrencia_web_usuarios
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_USUARIOS',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_USUARIOS',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_USUARIOS',old.id,'D');
end;

CREATE trigger ttbocorrencia_web_status_biud for tbocorrencia_web_status
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_STATUS',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_STATUS',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_STATUS',old.id,'D');
end;

CREATE trigger ttbocorrencia_web_sql_biud for tbocorrencia_web_sql
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_SQL',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_SQL',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_SQL',old.id,'D');
end;

CREATE trigger ttbocorrencia_web_motivo_biud for tbocorrencia_web_motivo
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_MOTIVO',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_MOTIVO',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_MOTIVO',old.id,'D');
end;

CREATE trigger ttbocorrencia_web_freed_biud for tbocorrencia_web_freed
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_FREED',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_FREED',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB_FREED',old.id,'D');
end;

CREATE trigger ttbocorrencia_web_biud999 for tbocorrencia_web
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBOCORRENCIA_WEB',old.id,'D');
end;

ALTER TABLE TBOCORRENCIA_WEB_SQL
    ADD USUARIOS_SQL BLOB SUB_TYPE 1 SEGMENT SIZE 80;

ALTER TABLE TBSUPERVISOR
    ADD USUARIO_ID INTEGER;

CREATE trigger ttbusuario_a01iud for tbusuario
active after insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBUSUARIO',new.codigo,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBUSUARIO',new.codigo,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBUSUARIO',old.codigo,'D');
end;

CREATE trigger ttbusuario_representante_b01iud for tbusuario_representante
active before insert or update or delete position 999
AS
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBUSUARIO_REPRESENTANTE',new.usuario_codigo,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBUSUARIO_REPRESENTANTE',new.usuario_codigo,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBUSUARIO_REPRESENTANTE',old.usuario_codigo,'D');
end;

CREATE trigger ttbrepresentante_b01iud for tbrepresentante
active before insert or update or delete position 999
as
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBREPRESENTANTE',new.codigo,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBREPRESENTANTE',new.codigo,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBREPRESENTANTE',old.codigo,'D');
end;

CREATE trigger ttbsupervisor_b01iud for tbsupervisor
active before insert or update or delete position 0
as
begin
  if(inserting)then execute procedure spi_integracao_mobile('TBSUPERVISOR',new.id,'I');
  if(updating )then execute procedure spi_integracao_mobile('TBSUPERVISOR',new.id,'A');
  if(deleting )then execute procedure spi_integracao_mobile('TBSUPERVISOR',old.id,'D');
end;

ALTER TABLE TBOCORRENCIA_WEB_SQL
    ADD USUARIOS_MOB BLOB SUB_TYPE 1 SEGMENT SIZE 80;

ALTER TABLE TBOCORRENCIA_WEB
    ADD DISPOSITIVO_ID INTEGER,
    ADD TEMP_ID INTEGER;
	
ALTER TABLE TBOCORRENCIA_WEB_USUARIOS
    ADD DISPOSITIVO_ID INTEGER,
    ADD TEMP_ID INTEGER;
	
ALTER TABLE TBOCORRENCIA_WEB_FREED
    ADD DISPOSITIVO_ID INTEGER,
    ADD TEMP_ID INTEGER;


CREATE OR ALTER function fn_json_v3 (
    json blob sub_type 1 segment size 80,
    campo varchar(50))
returns varchar(100)
as
declare variable retorno blob sub_type 1 segment size 80;
declare variable pos integer;
begin
    RETORNO = '';

    POS = POSITION ('"'||:CAMPO||'":', :JSON, 1);
    if(POS > 0)then
    begin
        RETORNO = SUBSTRING (:JSON FROM (:POS + CHAR_LENGTH ('"'||:CAMPO||'":')) FOR CHAR_LENGTH (:JSON) - 0 + 1);
        POS = POSITION ('"', :RETORNO, 2);
        RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
    end

    RETORNO = replace(RETORNO,'"','');
    RETORNO = replace(RETORNO,'"','');

    return replace(RETORNO,'"','');
end;

CREATE OR ALTER procedure sp_notificacao_ocorrencia (
    titulo varchar(50),
    mensagem blob sub_type 1 segment size 80,
    ocorrencia_id integer,
    usuario_ignorar integer)
returns (
    usuario_id integer)
as
begin

  for
     select distinct
        u.usuario_id
     from tbocorrencia_web_usuarios u
     where u.ocorrencia_web_id = :ocorrencia_id
     and u.status_exclusao = 0
  into
    :USUARIO_ID
  do begin

     --if(:USUARIO_IGNORAR <> :USUARIO_ID)then
     --begin
        INSERT INTO TBNOTIFICACAO (TIPO, USUARIO_ID, TODOS, MENU_ID, TITULO, LEITURA, ENVIO, DATA_HORA, EMITENTE, AGENDAMENTO, EXECUTADO, TABELA, TABELA_ID, MENSAGEM)
        VALUES (
            6,
            :USUARIO_ID,
            0,
            0,
            :TITULO,
            0,
            0,
            current_timestamp,
            0,
            current_timestamp,
            0,
            'TBOCORRENCIA_WEB',
            :OCORRENCIA_ID,
            :MENSAGEM
        );

      suspend;
     --end
  end

end;

CREATE OR ALTER function fn_camel_case (
    palavra varchar(300))
returns varchar(300)
as
declare variable l varchar(300);
declare variable u varchar(5);
begin

  l = lower(substring(:PALAVRA from 2 for char_length(:PALAVRA)));
  u = upper(substring(:PALAVRA from 1 for 1));

  return u||l;
end;



CREATE OR ALTER trigger ttbocorrencia_web_a01i for tbocorrencia_web
active after insert position 0
AS
declare variable id integer;
declare variable corpo1 blob sub_type 1 segment size 80;
declare variable corpo2 blob sub_type 1 segment size 80;
declare variable corpo3 blob sub_type 1 segment size 80;
declare variable html1 varchar(4000);
declare variable html2 varchar(4000);
declare variable tipo integer;
declare variable linha integer;
declare variable usuario_id integer;
declare variable assunto varchar(100);
declare variable descricao blob sub_type 1 segment size 80;
declare variable json blob sub_type 1 segment size 80;
declare variable campos_tabela_web varchar(300);
declare variable campos_sql_web varchar(300);
declare variable descricao_web varchar(300);
declare variable campo varchar(300);
declare variable tipo_descricao varchar(300);
declare variable motivo_descricao varchar(300);
declare variable de varchar(300);
declare variable para varchar(3000);
declare variable em_copia varchar(3000);
declare variable em_copia_oculta varchar(3000);
begin

    if(new.id > 0)then
    begin

        :para            = lower(coalesce((select list(u.email,', ') as email from tbocorrencia_web_usuarios g, tbusuario u where u.codigo = g.usuario_id and g.ocorrencia_web_id = new.id and coalesce(u.email,'') <> '' /*and g.usuario_id <> new.usuario_id*/),''));
        :em_copia        = '';
        :em_copia_oculta = '';

        html1 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 13);
        html2 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 14);

        select
            e.tipo_id,
            e.assunto,
            e.descricao,
            fn_execute_sql( replace(s.where_web1,':VINCULO_ID', e.vinculo_id) ) as JSON,
            s.campos_tabela_web,
            s.campos_sql_web,
            s.descricao_web,
            t.descricao,
            m.descricao,
            t.email
        from tbocorrencia_web e,  tbocorrencia_web_sql s, tbocorrencia_web_tipo t, tbocorrencia_web_motivo m
        where e.id = new.id
        and s.ocorrencia_web_tipo_id = e.tipo_id
        and t.id = e.tipo_id
        and m.id = e.motivo_id
        into
            :tipo,
            :assunto,
            :descricao,
            :json,
            :campos_tabela_web,
            :campos_sql_web,
            :descricao_web,
            :tipo_descricao,
            :motivo_descricao,
            :de;

        corpo1 = '';
        corpo2 = '';
        corpo3 = '';
        
        corpo1 = '<html><head><meta charset="Content-Type: text/html; charset=UTF-8"><style>p {margin: 2px;}</style></head><body>';
        corpo1 = corpo1 ||'<p> Foi registrada uma nova ocorrência e você esta envolvido.</p>';
        corpo1 = corpo1 || '<br><br>';
        corpo1 = corpo1 || '<p><a href="'||fn_parametro('SISTEMA.URL_SERVER')||'/_26110?TIPO='||:tipo||'&ID='||new.id||'" target="_blank">[ABRIR OCORRÊNCIA NA WEB]</a></p>';
        corpo1 = corpo1 || '<br>';
        corpo1 = corpo1 || html1;
        
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>Ocorrência:</u></p>';
        corpo2 = corpo2||'<p><u>Assunto</u>: '||:assunto||'</p>';
        corpo2 = corpo2||'<p><u>Descrição</u>: '||:descricao||'</p>';
        corpo2 = corpo2||'<br>';
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>'||:descricao_web||'</u></p>';

        for
          select OSPLIT, (row_number() over()) as LINHA from split2(:campos_sql_web,',')
        into
          :campo,
          :linha
        do begin
          corpo2 = corpo2||'<p><u>'|| FN_CAMEL_CASE(fn_array(:campos_tabela_web,:linha,',')) ||'</u>: '||fn_json_v3(:json, :campo)||'</p>';
        end

        corpo3 = corpo3 || html2 || '</body></html>';

        id = Gen_ID(gtbemail,1);

        for
            select usuario_id from
            SP_NOTIFICACAO_OCORRENCIA(
                :descricao_web||' - Nova Ocorrência aberta ('||new.id||')',
                'Uma nova ocorrência foi aberta e você esta envolvido.<br><a href="'||fn_parametro('SISTEMA.URL_SERVER')||'/_26110?TIPO='||:tipo||'&ID='||new.id||'" target="_blank">[ABRIR OCORRÊNCIA]</a></p>',
                new.id,
                new.usuario_id
            )
        into
         :usuario_id
        do begin

       end

       if(coalesce(:corpo1 || :corpo2 || :corpo3, '') <> '' and char_length(:para) > 0)then
       begin
        insert into tbemail (id, status, email, assunto, corpo, usuario_id, cc, cco, painel_id, datahora, de)
           values (
            :id,
            0,
            :para,
            :descricao_web ||' [OCORRÊNCIA:'||lpad(new.id,8,'0')||'] - '||FN_CAMEL_CASE(:motivo_descricao),
            :corpo1 || :corpo2 || :corpo3,
            0,
            :em_copia,
            :em_copia_oculta,
            0,
            current_timestamp,
            lower(fn_parametro('SISTEMA.EMAIL_OCORRENCIA'))
         );
       end
    end
end;


CREATE OR ALTER trigger tbocorrencia_web_freed_b03i for tbocorrencia_web_freed
active before insert position 3
AS
declare variable id integer;
declare variable corpo1 blob sub_type 1 segment size 80;
declare variable corpo2 blob sub_type 1 segment size 80;
declare variable corpo3 blob sub_type 1 segment size 80;
declare variable html1 varchar(4000);
declare variable html2 varchar(4000);
declare variable tipo integer;
declare variable linha integer;
declare variable assunto varchar(100);
declare variable descricao blob sub_type 1 segment size 80;
declare variable json blob sub_type 1 segment size 80;
declare variable campos_tabela_web varchar(300);
declare variable campos_sql_web varchar(300);
declare variable descricao_web varchar(300);
declare variable campo varchar(300);
declare variable tipo_descricao varchar(300);
declare variable motivo_descricao varchar(300);
declare variable email varchar(300);
begin

    if(new.origem = 1 and new.usuario_id > 0)then
    begin

        html1 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 13);
        html2 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 14);

        select
            e.tipo_id,
            e.assunto,
            e.descricao,
            fn_execute_sql( replace(s.where_web1,':VINCULO_ID', e.vinculo_id) ) as JSON,
            s.campos_tabela_web,
            s.campos_sql_web,
            s.descricao_web,
            t.descricao,
            m.descricao,
            t.email
        from tbocorrencia_web e,  tbocorrencia_web_sql s, tbocorrencia_web_tipo t, tbocorrencia_web_motivo m
        where e.id = new.ocorrencia_web_id
        and s.ocorrencia_web_tipo_id = e.tipo_id
        and t.id = e.tipo_id
        and m.id = e.motivo_id
        into
            :tipo,
            :assunto,
            :descricao,
            :json,
            :campos_tabela_web,
            :campos_sql_web,
            :descricao_web,
            :tipo_descricao,
            :motivo_descricao,
            :email;

        corpo1 = '';
        corpo2 = '';
        corpo3 = '';
        
        corpo1 = '<html><head><meta charset="Content-Type: text/html; charset=UTF-8"><style>p {margin: 2px;}</style></head><body>';
        corpo1 = corpo1 ||'<p>'||new.mensagem||'</p>';
        corpo1 = corpo1 || '<br><br>';
        corpo1 = corpo1 || '<p><a href="'||fn_parametro('SISTEMA.URL_SERVER')||'/_26110?TIPO='||:tipo||'&ID='||new.ocorrencia_web_id||'" target="_blank">[ABRIR OCORRÊNCIA NA WEB]</a></p>';
        corpo1 = corpo1 || '<br>';
        corpo1 = corpo1 || html1;
        
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>Ocorrência:</u></p>';
        corpo2 = corpo2||'<p><u>Assunto</u>: '||:assunto||'</p>';
        corpo2 = corpo2||'<p><u>Descrição</u>: '||:descricao||'</p>';
        corpo2 = corpo2||'<br>';
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>'||:descricao_web||'</u></p>';

        for
          select OSPLIT, (row_number() over()) as LINHA from split2(:campos_sql_web,',')
        into
          :campo,
          :linha
        do begin
          corpo2 = corpo2||'<p><u>'|| FN_CAMEL_CASE(fn_array(:campos_tabela_web,:linha,',')) ||'</u>: '||fn_json_v3(:json, :campo)||'</p>';
        end

        corpo3 = corpo3 || html2 || '</body></html>';

        id = Gen_ID(gtbemail,1);

        if(coalesce(:corpo1 || :corpo2 || :corpo3, '') <> '' and char_length(new.para) > 0)then
        begin

            insert into tbemail (id, status, email, assunto, corpo, usuario_id, cc, cco, painel_id, datahora, de)
               values (
                :id,
                0,
                new.para,
                :descricao_web ||' [OCORRÊNCIA:'||lpad(new.ocorrencia_web_id,8,'0')||'] - '||FN_CAMEL_CASE(:motivo_descricao),
                :corpo1 || :corpo2 || :corpo3,
                new.usuario_id,
                new.em_copia,
                new.em_copia_oculta,
                0,
                current_timestamp,
                :email
             );
       end

    end
end;

ALTER TABLE TBOCORRENCIA_WEB
    ADD EMAIL_ABERTURA BLOB SUB_TYPE 1 SEGMENT SIZE 80;

CREATE OR ALTER trigger ttbocorrencia_web_a01i for tbocorrencia_web
active after insert position 0
AS
declare variable id integer;
declare variable corpo1 blob sub_type 1 segment size 80;
declare variable corpo2 blob sub_type 1 segment size 80;
declare variable corpo3 blob sub_type 1 segment size 80;
declare variable html1 varchar(4000);
declare variable html2 varchar(4000);
declare variable tipo integer;
declare variable linha integer;
declare variable usuario_id integer;
declare variable assunto varchar(100);
declare variable descricao blob sub_type 1 segment size 80;
declare variable json blob sub_type 1 segment size 80;
declare variable campos_tabela_web varchar(300);
declare variable campos_sql_web varchar(300);
declare variable descricao_web varchar(300);
declare variable campo varchar(300);
declare variable tipo_descricao varchar(300);
declare variable motivo_descricao varchar(300);
declare variable de varchar(300);
declare variable para varchar(3000);
declare variable em_copia varchar(3000);
declare variable em_copia_oculta varchar(3000);
begin

    if(new.id > 0)then
    begin

        :para            = lower(coalesce((select list(u.email,', ') as email from tbocorrencia_web_usuarios g, tbusuario u where u.codigo = g.usuario_id and g.ocorrencia_web_id = new.id and coalesce(u.email,'') <> '' /*and g.usuario_id <> new.usuario_id*/),''));
        :em_copia        = '';
        :em_copia_oculta = '';

        if(char_length(coalesce(new.EMAIL_ABERTURA,'')) > 0)then
        begin
          :para = coalesce(new.EMAIL_ABERTURA,'');
        end

        html1 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 13);
        html2 = (SELECT first 1 p.conteudo FROM TBHTML_ELEMENTO p where p.id = 14);

        select
            e.tipo_id,
            e.assunto,
            e.descricao,
            fn_execute_sql( replace(s.where_web1,':VINCULO_ID', e.vinculo_id) ) as JSON,
            s.campos_tabela_web,
            s.campos_sql_web,
            s.descricao_web,
            t.descricao,
            m.descricao,
            t.email
        from tbocorrencia_web e,  tbocorrencia_web_sql s, tbocorrencia_web_tipo t, tbocorrencia_web_motivo m
        where e.id = new.id
        and s.ocorrencia_web_tipo_id = e.tipo_id
        and t.id = e.tipo_id
        and m.id = e.motivo_id
        into
            :tipo,
            :assunto,
            :descricao,
            :json,
            :campos_tabela_web,
            :campos_sql_web,
            :descricao_web,
            :tipo_descricao,
            :motivo_descricao,
            :de;

        corpo1 = '';
        corpo2 = '';
        corpo3 = '';
        
        corpo1 = '<html><head><meta charset="Content-Type: text/html; charset=UTF-8"><style>p {margin: 2px;}</style></head><body>';
        corpo1 = corpo1 ||'<p> Foi registrada uma nova ocorrência e você esta envolvido.</p>';
        corpo1 = corpo1 || '<br><br>';
        corpo1 = corpo1 || '<p><a href="'||fn_parametro('SISTEMA.URL_SERVER')||'/_26110?TIPO='||:tipo||'&ID='||new.id||'" target="_blank">[ABRIR OCORRÊNCIA NA WEB]</a></p>';
        corpo1 = corpo1 || '<br>';
        corpo1 = corpo1 || html1;
        
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>Ocorrência:</u></p>';
        corpo2 = corpo2||'<p><u>Assunto</u>: '||:assunto||'</p>';
        corpo2 = corpo2||'<p><u>Descrição</u>: '||:descricao||'</p>';
        corpo2 = corpo2||'<br>';
        corpo2 = corpo2||'<p style="font-weight: bold;"><u>'||:descricao_web||'</u></p>';

        for
          select OSPLIT, (row_number() over()) as LINHA from split2(:campos_sql_web,',')
        into
          :campo,
          :linha
        do begin
          corpo2 = corpo2||'<p><u>'|| FN_CAMEL_CASE(fn_array(:campos_tabela_web,:linha,',')) ||'</u>: '||fn_json_v3(:json, :campo)||'</p>';
        end

        corpo3 = corpo3 || html2 || '</body></html>';

        id = Gen_ID(gtbemail,1);

        for
            select usuario_id from
            SP_NOTIFICACAO_OCORRENCIA(
                :descricao_web||' - Nova Ocorrência aberta ('||new.id||')',
                'Uma nova ocorrência foi aberta e você esta envolvido.<br><a href="'||fn_parametro('SISTEMA.URL_SERVER')||'/_26110?TIPO='||:tipo||'&ID='||new.id||'" target="_blank">[ABRIR OCORRÊNCIA]</a></p>',
                new.id,
                new.usuario_id
            )
        into
         :usuario_id
        do begin

       end

       if(coalesce(:corpo1 || :corpo2 || :corpo3, '') <> '' and char_length(:para) > 0)then
       begin
        insert into tbemail (id, status, email, assunto, corpo, usuario_id, cc, cco, painel_id, datahora, de)
           values (
            :id,
            0,
            :para,
            :descricao_web ||' [OCORRÊNCIA:'||lpad(new.id,8,'0')||'] - '||FN_CAMEL_CASE(:motivo_descricao),
            :corpo1 || :corpo2 || :corpo3,
            0,
            :em_copia,
            :em_copia_oculta,
            0,
            current_timestamp,
            lower(fn_parametro('SISTEMA.EMAIL_OCORRENCIA'))
         );
       end
    end
end;



