Saving a report in HTML format

Until recently, I've never needed to save a report (i.e. create a disk file) in HTML format - or maybe I've always managed to dodge my way around this requirement. Recently, though, I was asked to save a report that I distribute via email in PDF format to save it also as a disk file in HTML. As the report in question is actually an 'HTML document', this is documented as using two extra parameters to the WINHTML program ('-o', presumably meaning 'output' and the output file).

LINK ORDERS TO :$.PAR;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORDNAME = 'KL210001';
:FNAME = '//server/Sharing/AutoReports/$.HTM';
EXECUTE WINHTML '-d', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-o', :FNAME
;
UNLINK AND REMOVE ORDERS;
LINK ORDERS TO :$.PAR;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORDNAME = 'KL210001';
:FNAME = '//server/Sharing/AutoReports/$.PDF';
EXECUTE WINHTML '-d', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-pdf', :FNAME;
SELECT ENTMESSAGE ('$', 'P', 20) INTO :SUBJECT FROM DUMMY;
:GROUP = 'FURNDAILY';
LINK GENERALLOAD TO :$.GEN;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT, TEXT6)
VALUES (1, '1', :SUBJECT, :GROUP);
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (2, '3', :FNAME);
EXECUTE INTERFACE 'TEST_SENDREPORTEMAIL', SQL.TMPFILE, '-L', :$.GEN;
SELECT ATOI (KEY1) INTO :MB
FROM GENERALLOAD
WHERE RECORDTYPE = '1'
AND LOADED = 'Y';
LINK MAILBOX TO :$.MBX;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO MAILBOX
SELECT * FROM MAILBOX ORIG
WHERE MAILBOX = :MB;
EXECUTE SENDMAIL :$.MBX, :XMSG;  /* send the letter! */
UNLINK MAILBOX;
UNLINK GENERALLOAD;
UNLINK ORDERS;

Today I was asked to do something similar, but in this case, the report is created by a normal multi-step procedure (i.e. not an HTML document), and so there is no use of WINHTML. I looked at the documentation for saving a normal report as HTML - this uses WINACTIV.

The :F variable implies that the report will be saved to a file with this name, but this variable is not used! I looked at the SDK version on the Internet and exactly the same code appears. THIS IS A MISTAKE! The correct code is as follows:

:FNAME = '//server/Sharing/AutoReports/$.HTM';
EXECUTE WINACTIV '-R', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-o', :FNAME;  

In other words, in the same way that WINHTML receives an extra two parameters when saving a report to disk, WINACTIV does also, only that this is not documented.

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 &lt; and the right angle bracket as &gt;

Creating Excel files from Priority

A question that I see frequently asks how one can create an Excel file from Priority. There are two different answers to this question - one solution creates CSV files that can be read into Excel with no problem, and the other solution creates XLSM files that are native Excel files. The 'M' in this extension stands for Macro; one can include in these files a macro that executes on the data. Unfortunately, such files are often caught by email anti-virus filters, so sending them to third persons can be problematic.

For the purpose of this blog entry, I assume that there exists a report TEST_OUTPUT2EXCEL. It doesn't matter what the contents of this report are.

CSV method: this is very simple and creates, as expected, a CSV file. This means that the file does not allow any formatting of the contents.

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL.CSV';
EXECUTE WINACTIVF '-x', :XLFILE, '-R', 'TEST_OUTPUT2EXCEL';
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA :XLFILE;

The first line defines a file name and the second line executes the TEST_OUTPUT2EXCEL report and sends the output to the denoted file. As far as I can figure out, the first parameter to WINACTIVF, i.e. '-x', means create an Excel/CSV file; the second parameter is the name of the output file; the third parameter is the report type: R = simple report, P = procedure, and the fourth and final parameter is the name of the report/procedure to be executed. The third line in the above snippet defines an email address and the fourth line shows a simple way to send a file to someone via email. The report itself does not have to be defined in any menu but the person executing the above code has to have permission for the report. This means that the report does not have to appear on a menu if it is executed solely by the task scheduler, but execution by 'normal' users requires the report to appear on a menu.

XLSM method: this is more complicated, naturally. There is a preliminary stage for this method that requires that the report appear on a menu: one has to create an Excel template for the report. One performs a right click on the menu option and chooses 'Create Excel template' then one runs the report. All the required formatting is done on the Excel file that is created, then one saves the file without changing its name, extension or location (the file name will be the name of the report and the extension XLT; the location will be ..\..\system\document). Once the template has been prepared, the report can be removed from the menu if it is not intended to be run manually. The template can be edited at a later stage, but again requires that the report appear on a menu.

The next stage is to find the template's number. There is no simple way of doing this; I opened WINDBI then typed 'SELECT * FROM EXCELTEMPLATES WHERE UDATE > SQL.DATE8 FORMAT'. This query will list all the templates that have been created today - there shouldn't be very many! The value of the EXEC field will be the EXEC number of the report. For the purposes of this blog, the template number will be 207, but I warn that this number has to be found empirically.

Once all the above has been done, the execution is simple:

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL';
EXECUTE WINACTIV '-R', 'TEST_OUTPUT2EXCEL', '-X', :XLFILE, 207; 
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA STRCAT (:XLFILE', '.XLSM');

Here the order of the parameters seems more logical: type of report, name of report, Excel, name of output file (note that one does NOT give an extension) and number of template. This creates the file and adds the extension name; the extension has to be added automatically when sending the file by email, although as I noted at the beginning, such a file is likely to be stopped by an email anti-virus filter. This file will be formatted according to the format of the template file; one can also define a macro within the template file that is supposed to execute automatically when the file is opened (according to this page; I haven't tried this myself and there are likely to be problems executing macros on network files).

Update regarding how to find the template number: the template file will be in directory ..\..\system\document and its name will be something like TEST_OUTPUT2EXCEL_207.xlt, i.e. the name of the procedure or report for which the template was defined, followed by the number of the template. One can take the number directly from here instead of issuing a query via WINDBI.

Appropriating the 'HTML document' framework

It all started when I was asked to prepare a report for the CEO ... well, three reports if you don't mind, and the CEO would like to have them all in one email. My thinking process went something like this: to send the reports in an email, we will have to build a letter and attach the files to the letter (I've done this before), but the only way that I know to save a report automatically as a file (so that it can be attached) is to save it as a pdf file, and the only way that I know to accomplish this is to use an 'HTML document', like a delivery note or an invoice. Anyway, there's no connection between the three reports so there's no way I can define a suitable HTML document.

Or is there? Looking at this problem laterally, an HTML document is built from several sub-reports, all of which display data which is connected to a single item, be it order, delivery note or invoice. But when one looks closely at this, there's no mandatory reason that all the sub-reports have to look at the same item, or even look at it at all. I could pass an item which is guaranteed to exist (like part 000 or customer www) then ignore it.

So I set about writing an HTML document from scratch: this used to be a daunting experience, especially seeing as it is almost totally undocumented and very fragile, but now I've got some experience in this and know that I am capable of the required programming. Speaking abstractly, there are three sections to an HTML document: the first is composed of three (or possibly four) stages, these being INPUT, HTMLCURSOR, possibly HTMLEXTFILES and SQLI. The second section comprises the various reports which need to be displayed, and the final section is another INPUT stage where various parameters are displayed.

The input stage generally has one parameter named PAR that gets linked to a table holding the values which are being passed to the document. The table can have one row or many, depending on how the document is being invoked (one row if invoked from a form, several rows if invoked from a menu). The HTMLCURSOR stage seems like black magic: normally one would visibly link the PAR parameter to a table but here it seems to be done automatically (presumably based on information in the INPUT stage). This stage simply consists of a statement like SELECT ORD FROM ORDERS WHERE ORD > 0; this assumes that PAR consists of a linked table of orders. Ignoring the HTMLEXTFILES stage for the moment (this selects any attachments or 'ext[ernal] files' connected to the given record), the SQLI statement at a minimum seems to be a recapitulation of the HTMLCURSOR stage as it extracts the key field from the current record and saves it as a parameter for the sub-reports.

But nowhere does it say that the sub-reports have to use this key field! Obviously, a normal HTML document would use this key field, but here I am appropriating the framework of the HTML document and I couldn't care less about this key field. It seems that I have to extract it though and even pass it to the sub-reports (where it gets ignored) otherwise the document doesn't display.

The SQLI stage can also issue SQL queries and store the results in linked temporary files for the following sub-reports to use. As two of the three reports which are to be displayed require some data mining, I can do this during the SQLI stage.

Assuming now that I have an HTML document which actually displays the three disparate reports, how do I invoke it to create a PDF file then send an email with the PDF file attached? I've done this bit before: one uses the WINHTML program to create the document and save it as a PDF, then one builds an email and sends it (I wrote about this some years ago)

LINK PART TO :$.PAR;
GOTO 99 WHERE :RETVAL <= 0;
INSERT INTO PART
SELECT * FROM PART ORIG
WHERE PARTNAME = '000';
:FNAME = '../../system/mail/TEST_ALL_IN_ONE.pdf';
EXECUTE WINHTML '-d', 'TEST_WWWALLINONE', 'PART', :$.PAR,
'-pdf', :FNAME;
UNLINK PART;
SELECT ENTMESSAGE ('$', 'P', 10) INTO :SUBJECT FROM DUMMY;
LINK GENERALLOAD TO :$.GEN;
GOTO 99 WHERE :RETVAL <= 0;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT, TEXT2)
VALUES (1, '1', :SUBJECT, 'someone@somewhere.com');
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (2, '2', :FNAME);
EXECUTE INTERFACE 'TEST_SENDMAIL', SQL.TMPFILE, '-L', :$.GEN;
:LETTER = 0;
SELECT ATOI (KEY1) INTO :LETTER
FROM GENERALLOAD
WHERE LOADED = 'Y'
AND RECORDTYPE = '1';
LINK MAILBOX TO :$.MB;
INSERT INTO MAILBOX
SELECT * FROM MAILBOX ORIG
WHERE MAILBOX = :LETTER;
EXECUTE SENDMAIL :$.MB, :XMSG;  /* send it */
UNLINK MAILBOX;
UNLINK GENERALLOAD;
LABEL 99;

I don't imagine that I will frequently use this technique of making a fake HTML document composed of unrelated sub-reports, but it is an interesting technique.