Avisos Individuais Avançados
Criando Avisos Individuais Avançados
O que estamos chamando por avisos individuais avançados neste artigo são e-mails com várias partes, incluindo tabelas de dados, indicadores, e mensagens entre estes.
Há duas formas de se criar e-mails individuais avançados: simples e complexa.
Vamos começar com a simples
Para criar um aviso individuais simples, vamos retornar um único SELECT com os dados que devem fazer parte do aviso.
Para exemplificar vamos montar um aviso com uma relação simples contendo 4 colunas: Código, Nome, Emitido em e Valor. Essas serão as colunas de dados que constarão no aviso por e-mail:
Para tal criaremos uma stored procedure de aviso individual, que geralmente inicia com VEXTA_INDIVIDUAL_. Neste artigo demos o nome de [VEXTA_INDIVIDUAL_Teste avançado 1], e abaixo apresentamos o retorno do SELECT.
Veja que a diferença para para um aviso individual comum é a presença de campos "especiais", que iniciam com @, e a diferença fundamental é o campo @msg-id. Este campo informa ao serviço de avisos que deverá compor em um único aviso todo conteúdo retornado até que o id da mensagem seja diferente. E cada vez que o serviço de avisos verificar que a coluna @msg-id foi alterada, ele também buscará pelos campos @titulo e @destinatarios. O @título será o assunto do e-mail, e @destinatários deverá conter os e-mails dos destinatários separados com ponto-e-vírgula ( ; ). E como criará um novo aviso quando o id for diferente, o ideal é sempre ordenar o retorno primeiramente pelo id da mensagem, depois por outro critério que se fizer necessário, como por vencimento, ou valor, alfabético, etc.
Então, como podemos ver nas duas imagens de mensagem de e-mail acima, o aviso com o id de mensagem 1 tem 3 linhas na tabela de dados, enquanto o segundo aviso tem duas linhas. Exatamente conforme o retorno do SELECT. Assim como já foi tratado em outro artigo, os campos especiais - que iniciam com @ - não são renderizados na tabela de dados, e servem para indicar um comportamento para o serviço de avisos - como formatação de colunas, mensagem a incluir antes e após os dados, alterar ou excluir um rodapé.
Vamos agora aos avisos avançados complexos
Embora neste artigo chamamos de complexos a montagem da procedure ainda se mantém bastante simples. A diferença é que o e-mail pode conter vários conteúdos diferentes, como indicadores, mensagens e tabelas e dados. Como isso não é possível de se retornar em um único SELECT vamos apelar ao suporte à vários SELECTs do serviço de avisos.
Observe que basicamente o aviso contém a tabela de dados, mas possui também, antes, uma mensagem informando sobre os indicadores, após os indicadores em si, depois mais uma mensagem antes da tabela de dados, uma mensagem após a tabela de dados, e uma mensagem final de agradecimento. Cada um desses itens deve ser retornado separadamente, exceto as mensagens @antes e @apos que, conforme o outro artigo, podem ser retornadas juntamente com a tabela de dados - mas poderiam ser retornadas separadamente.
Os indicadores são retornados no formato NOME, VALOR, e podem conter alguns campos especiais, como @decimal ou @css-Valor, para formatação. Mais detalhes sobre isso podem ser vistos em artigos anteriores.
E as mensagens individuais, como Olá, esses são os seus indicadores e Obrigada! são retornadas separadamente, num SELECT com um único registro e uma única coluna com o nome @mensagem.
Como o serviço de e-mails verifica se @msg-id foi alterado para compor um novo e-mail, e ao mesmo tempo busca pelos campos @titulo e @destinatarios, precisaremos retornar um SELECT apenas com esses dados. E após, retornar SELECTs em sequencia, na ordem que se deseja montar o aviso no e-mail.
Lembrando que, como são avisos individuais, deverá retornar SELECTs com @msg-id, @destinatarios e @titulo a cada novo aviso. Isso pode ser feito facilmente através de cursores. Este exemplo de aviso avançado complexo fizemos um cursor sobre o SELECT do primeiro aviso simples, que vimos no início do artigo. Abaixo vemos como fazer isso facilmente através de cursores.
Usando cursores para montar os avisos avançados complexos
Primeiramente vamos declarar a variável com o ID da mensagem:
DECLARE @msg_id INT
Em seguida declaramos o cursor, usando um SELECT DISTINCT na coluna @msg-id. Desta forma teremos um SELECT que terá apenas dois registros, levando em conta que estamos aplicando na tabela do primeiro exemplo deste artigo. Fazemos isso pois dentro do WHILE vamos listar os dados daquele ID de mensagem, e se não usarmos DISTINCT passaremos três vezes pelo msg-id 1 e duas vezes pela msg-id 2, fazendo com que sejam disparados 5 avisos.
DECLARE curTemp CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT [@msg-id] FROM #RETORNO
OPEN curTemp
FETCH NEXT FROM curTemp INTO @msg_id
Dentro do cursor vamos somar o valor dos títulos e contar quantos títulos existem. Ao fazer isso vamos salvar esses valores em variáveis. Então vamos declará-las antes de começar a percorrer os dados:
DECLARE @fltSoma MONEY, @intCont INT
Agora vamos percorrer os dados, usando um WHILE, e já vamos comentando o que estamos fazendo em cada linha (você pode remover os comentários):
WHILE (@@FETCH_STATUS = 0) BEGIN
-- aqui retornamos o ID da mensagem, o titulo e destinatarios da nossa tabela
-- não esqueça do TOP 1, para que apenas um registro seja retornado
SELECT TOP 1 [@msg-id], [@destinatarios], [@titulo] FROM #RETORNO WHERE [@msg-id] = @msg_id
-- aqui retornamos um único registro, com uma única coluna @mensagem
SELECT 'Olá, esses são os seus indicadores' [@mensagem]
-- somamos o valor dos títulos, e contamos quantos existem com aquele ID de mensagem
SELECT @fltSoma = SUM([Valor]), @intCont = COUNT(0)
FROM #RETORNO WHERE [@msg-id] = @msg_id
-- retornamos os indicadores, um em cada linha, com NOME e VALOR
-- o campo especial @decimal indica para formatar, ou não, como valor decimal
-- o padrão é sempre formatar como decimal, então não é obrigatório retornar essa coluna
SELECT 'Valor vencido' NOME, @fltSoma VALOR, 1 [@indicador], 1 [@decimal]
UNION
SELECT 'Títulos vencidos', @intCont, 1, 0
-- mais uma mensagem
SELECT 'Abaixo a listagem de títulos em aberto' [@mensagem]
-- retornamos aqui a tabela de dados, indicando que não queremos rodapé com contagem de ocorrências
SELECT [Código], [Nome], [Emitido em], [Valor], '~~' [@rodape]
FROM #RETORNO WHERE [@msg-id] = @msg_id
-- mais uma mensagem
SELECT 'Qualquer dúvida ligue para a gente no (47) 3520-8888.' [@mensagem]
-- última mensagem de agradecimeno
SELECT 'Obrigada!' [@mensagem]
-- aqui buscamos o ID da próxima mensagem no cursor
FETCH NEXT FROM curTemp INTO @msg_id
END
E agora desalocamos o cursor. Nunca esqueça de fazer isso, caso contrários os avisos poderão travar.
CLOSE curTemp
DEALLOCATE curTemp
Abaixo o código do cursor sem comentários:
DECLARE @msg_id INT
DECLARE @fltSoma MONEY, @intCont INT
DECLARE curTemp CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT [@msg-id] FROM #RETORNO
OPEN curTemp
FETCH NEXT FROM curTempINTO @msg_id
WHILE (@@FETCH_STATUS = 0) BEGIN
SELECT TOP 1 [@msg-id], [@destinatarios], [@titulo] FROM #RETORNO WHERE [@msg-id] = @msg_id
SELECT 'Olá, esses são os seus indicadores' [@mensagem]
SELECT @fltSoma = SUM([Valor]), @intCont = COUNT(0)FROM #RETORNO WHERE [@msg-id] = @msg_id
SELECT 'Valor vencido' NOME, @fltSoma VALOR, 1 [@indicador], 1 [@decimal]UNIONSELECT 'Títulos vencidos', @intCont, 1, 0
SELECT 'Abaixo a listagem de títulos em aberto' [@mensagem]
SELECT [Código], [Nome], [Emitido em], [Valor], '~~' [@rodape] FROM #RETORNO WHERE [@msg-id] = @msg_id
SELECT 'Qualquer dúvida ligue para a gente no (47) 3520-8888.' [@mensagem]
SELECT 'Obrigada!' [@mensagem]
FETCH NEXT FROM curTemp INTO @msg_id
END
CLOSE curTemp
DEALLOCATE curTemp
Algumas considerações finais
Neste artigo simulamos o ID da mensagem, coluna @msg-id, como um número inteiro, no entanto qualquer valor poderá ser usado. Você poderá usar o próprio e-mail do destinatário como @msg-id. Para isso basta repetir o valor dos destinatários com o nome @msg-id.
Além disso, como citado na primeira parte do artigo, quando criar um aviso simples, ordene sempre pela coluna @msg-id.
Dicas sobre formatação podem ser encontradas no artigo Formatação e Indicadores nos Avisos por E-mail.