SET TERM ^ ;

CREATE OR ALTER procedure SPI_NOTIFICAR (
    HORA timestamp,
    USUARIO_ID varchar(500),
    ASSUNTO varchar(500),
    CORPO blob sub_type 1 segment size 80,
    EMAIL integer = 1,
    NOTIFICACAO integer = 1)
as
declare variable EMAIL_ID integer;
declare variable DESTINATARIO varchar(100);
BEGIN

    DESTINATARIO = (SELECT FIRST 1 U.EMAIL FROM TBUSUARIO U WHERE U.CODIGO = :USUARIO_ID);

    IF ( NOTIFICACAO = 1 ) THEN
    BEGIN

        INSERT INTO TBNOTIFICACAO (TIPO, USUARIO_ID, TODOS, MENU_ID, TITULO, LEITURA, ENVIO, DATA_HORA, EMITENTE, AGENDAMENTO, EXECUTADO, TABELA, TABELA_ID, MENSAGEM)
           VALUES (
            1,
            :USUARIO_ID,
            0,
            0,
            COALESCE(:ASSUNTO,'NOVO EMAIL'),
            0,
            0,
            :HORA,
            :USUARIO_ID,
            :HORA,
            0,
            '',
            0,
            COALESCE(:CORPO,'')
        );

    END

    IF ( EMAIL = 1 and coalesce(DESTINATARIO, '') is distinct from '') THEN
    BEGIN

        EMAIL_ID = (SELECT GEN_ID(GTBEMAIL,1) FROM RDB$DATABASE);

        INSERT INTO TBEMAIL (
            ID,
            EMAIL,
            USUARIO_ID,
            ASSUNTO,
            CORPO,
            STATUS,
            DATAHORA
        ) VALUES (
            :EMAIL_ID,
            :DESTINATARIO,
            0,
            COALESCE(:ASSUNTO,'NOVO EMAIL'),
            COALESCE(:CORPO,''),
            0,
            :HORA
        );
    END
END
^

SET TERM ; ^

