Programming Pitfalls in Priority: Sending email with data in the body of the letter

The standard method of sending reports via email causes the report to be sent as an attachment to the letter. Someone, though, wanted to see how to include data in the body of the email. Following is an example of what I call masochistic programming, as it is very tedious. Basically, one has to combine data from Priority along with HTML coding; I imagine that the skill of hand-written HTML code has long disappeared, and even if it were still extant, the majority of Priority programmers have never come across it.

OK: down to work. The following example will include some data from a customer order, whose internal number can be found in the variable :$.ORD. First off, one has to link a few tables, then insert what will be the header to the letter.


LINK GENERALLOAD TO :$.GEN;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK MAILBOX TO :$.MBX;
ERRMSG 1 WHERE :RETVAL <= 0;
:LINE = 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT, TEXT2)
SELECT :LINE, '1', ORDNAME, :GROUP
FROM ORDERS
WHERE ORD = :$.ORD;

That wasn't too difficult, was it? But now things are going to be a bit harder. The words or codes enclosed by angle brackets are HTML tags - these are what accomplish the required formatting.


:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', '<HTML><BODY><dir=ltr><P>);
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', STRCAT (
'Customer number: ', CUSTOMERS.CUSTNAME,
' Customer name: ', CUSTOMERS.CUSTDES,
' Order date: ', DTOA (ORDERS.CURDATE, 'DD/MM/YY'), '<BR>');
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', STRCAT ('<TABLE>',
'<tr><th>Line</th><th>Part number</th>',
'<th>Part description</th><th>Quantity</th></tr>');
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE + SQL.LINE, '2', STRCAT (
'<tr><td>', ITOA (SQL.LINE), '</td><td>',
PART.PARTNAME, '</td><td>', PART.PARTDES, '</td><td>',
RTOA (ORDERITEMS.QUANT, 2), '</td></tr>')
FROM ORDERITEMS, PART
WHERE ORDERITEMS.PART = PART.PART
AND ORDERITEMS.ORD = :$.ORD;
SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', '</TABLE></BODY></HTML>');

Now that the data is in the load table, it can be sent. First, one has to create a letter, then send it. The code below assumes that there is only one letter to be sent.

:EFILE = 'C:/TMP/2.TXT';
/* one has to define a suitable interface */
EXECUTE INTERFACE 'TEST_SENDMAIL', :EFILE, '-L', :$.GEN;  
SELECT MESSAGE INTO :PAR1 FROM ERRMSGS
WHERE USER = SQL.USER AND TYPE = 'i';
ERRMSG 99 WHERE :RETVAL > 0;
:MB = 0;
SELECT ATOI (KEY1) INTO :MB FROM GENERALLOAD
WHERE LINE = 1;
INSERT INTO MAILBOX
SELECT * FROM MAILBOX ORIG
WHERE MAILBOX = :MB;
:XMSG = '';
EXECUTE SENDMAIL :$.MBX, :XMSG;  /* send it */
UNLINK MAILBOX;
UNLINK GENERALLOAD;

Wishing you the best of luck! I should explain at least one line of cryptic HTML - the one that starts '<tr><th>Line</th>'. <tr> means 'start a new line', <th> means a header column, Line is the name of the column, and the concluding </th> means that this is end of the column name. Then follows the same combination (without <tr>), terminating in </tr>, that means 'end of table line' (the mnemonics are: tr = table row, th = table header, td = table data).

I have finally discovered how to enclose (apparent) HTML codes in a blog post: the left angle bracket should be written in the HTML source as < and the right angle bracket as >

Programming Pitfalls in Priority

Sunday, 22 January 2023

Sending email with data in the body of the letter

No comments:

Post a Comment