Saturday, February 15, 2020

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 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 stage 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 which 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.

No comments: