Determining if the ouput of an HTML document is a printer, the screen or email

Due to legal requirements, delivery notes and invoices have a field 'Printed' that determines when the document is printed whether the word 'original' or 'copy' should appear in the output. Displaying the document on the screen always results in 'copy'. A client wanted a similar mechanism but for customer orders - they want to know if  the order has been printed at any stage.

In an HTML document, the first three stages are usually INPUT, HTMLCURSOR and SQLI. In that third stage, the code for determing which text should be added ('original'/'copy') can be seen, but there is nothing in the document that says whether the document is being printed or displayed. Looking though the SDK, there's nothing explicit on this topic, although there is a hint.

Saving a Certified Copy when Printing a Document

You can define a procedure so that, when the document is printed or sent by e-mail (:SENDOPTION = 'PRINT' or 'AMAIL'), a certified copy is saved that can be printed later (using the Create Certified Copy program).

In order to save a copy:

• Include an HTMLEXTFILES step which goes over the attached files.
• In the first INPUT step, set the :HTMLPRINTORIG variable to 1.
• In the SQLI step after the HTMLCURSOR step, set the :SAVECOPY variable to 1.

These variables are updated during runtime, so that if several documents are being printed together, the variables will be set for each individual document, depending on its status.

So it would seem that the undocumented system variable :SENDOPTION is the key to the client's request. I added a little code to the SQLI stage of the procedure WWWSHOWORDER that would save the value of :SENDOPTION in a table of private constants. From this I was able to ascertain that when printing, the value is 'PRINT' and when displaying on the screen, it's 'DISPLAY'. Presumably when sending email the value is 'AMAIL' and for fax (??) it's probably 'FAX', although I don't need to know this.

For my purposes (or for the client), I have to update the private field 'PRINTED' in the ORDERS table if SENDOPTION is not equal to 'DISPLAY'. I think it better to save a username and date/time than just the fact that the order has been printed, but that's only my opinion.

I can't imagine any further use for this undocumented variable, but I vaguely recall that once I would have liked to have known its value, although for what, I do not remember.

An advanced subversion of HTML documents

I've written several times about HTML documents in the past, primarily about what I call 'container documents'. This time around, I am writing about an HTML document in its regular sense: the creation of an invoice document, but of course subverting it to display something for which the HTML document framework was not designed.

The customer sells electricity; they want an invoice to show the lines for each electricity meter contained in the invoice (so far, standard functionality) ... and then they want a separate, additional, page for each meter - very much non-standard. In order to create a page for each meter, the HTMLCURSOR of the procedure should return a meter (i.e. SERNUMBERS.SERN) from those contained in the invoice. But the procedure has to be passed an invoice number (or maybe a range of invoices), so how can one display what would appear to be a standard invoice along with non-standard extras?

'Obviously' (it was far from obvious when I started work on this) the list of invoices passed to the procedure in the PAR parameter has to be somehow saved as something else (I used STACK2), then the PAR parameter has to be repositioned in order to receive a list of meters in each invoice. My original code for this pre-HTMLCURSOR stage was

/* Stage 20: Save the invoices passed as a parameter to this procedure */
:PAR1 = '$';
LINK STACK2 TO :$.ST2;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK INVOICES TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO STACK2 (ELEMENT, TYPE)
SELECT SQL.LINE, IV
FROM INVOICES
WHERE IV > 0;
UNLINK AND REMOVE INVOICES;
:$.IDX = 0;  /* global iterator */
SELECT MAX (ELEMENT) INTO :$.MAX FROM STACK2;
UNLINK STACK2;
SELECT SQL.TMPFILE INTO :$.PAR FROM DUMMY;

The IDX and MAX variables are necessary for looping through the list of invoices, as will be seen in the next stage that begins as follows

/* Stage 30: Set up for HTML document */
:$.HDR = 1;
LINK STACK2 TO :$.ST2;
:$.IDX = :$.IDX + 1;
SELECT TYPE INTO :$.IV
FROM STACK2
WHERE ELEMENT = :$.IDX;

Following this originally was this code

UNLINK AND REMOVE SERNUMBERS;
LINK SERNUMBERS TO :$.PAR;
INSERT INTO SERNUMBERS
SELECT * FROM SERNUMBERS ORIG
WHERE EXISTS (SELECT 1 FROM INVOICEITEMS
WHERE SERNUMBERS.SERNUM = INVOICEITEMS.TEST_SERN
AND SERNUMBERS.SERN > 0
AND INVOICEITEMS.IV = :$.IV);

Then in the next stage, HTMLCURSOR would retrieve a value from :$.PAR, i.e. SERNUMBERS, and the appropriate documents would be created. Except this didn't work!

After some trial and error, I discovered that if the PAR parameter initially contains (in this case) invoices, as defined in the 'table name' of the line for the parameter, then it has to contain invoices all the time. Redefining PAR and linking it to SERNUMBERS doesn't work. So how can I pass a list of devices? By saving SERN and SERNUM (i.e. the A and U keys) of SERNUMBERS in a linked INVOICES table! It doesn't matter that an invoice with this key number may not exist; eventually I will not be accessing an invoice but a device. For the moment, the INVOICES table is simply a database table that fits my requirements, regardless of what its purpose is within Priority. Here is the continuation of stage 30 - still before HTMLCURSOR.

/* It seems that PAR always has to be linked to invoices. So insert
into the linked table values from SERNUMBERS */
UNLINK AND REMOVE INVOICES;
LINK INVOICES TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO INVOICES (IV, IVNUM)  /* SUBVERSION!!! */
SELECT SERN, SERNUM
FROM SERNUMBERS
WHERE EXISTS (SELECT 1 FROM INVOICEITEMS
WHERE SERNUMBERS.SERNUM = INVOICEITEMS.TEST_SERN
AND SERNUMBERS.SERN > 0
AND INVOICEITEMS.IV = :$.IV);

The HTMLCURSOR stage is simply 'SELECT IV FROM INVOICES WHERE IV > 0'. The SQLI stage following this begins as follows

/* Stage 50: This turns an 'invoice' number back into a sernumber */
SELECT SERN INTO :$.SRN
FROM SERNUMBERS
WHERE SERN = ATOI (:HTMLVALUE);

Stage 50 contains all the complicated boiler-plate code from the regular procedures, e.g. stage 10 of WWWSHOWCIV. In order to make the entire document display, I had to change certain statements of that boiler-plate code, specifically replacing any usage of ATOI (:HTMLVALUE) with :$.IV - this is what the code expects to receive. In the body of the procedure I am including standard reports such as WWWLOGO and WWWDOCNUM along with special reports of mine. The standard reports receive the :$.IV parameter and the special reports receive :$.SRN. Finally at the end, prior to the closing INPUT stage, there is a small SQLI stage with the following statements

:$.GO = (:$.IDX < :$.MAX ? 30 : 500);
:$.HDR = :$.HDR + 1;

Should there have been more than one invoice in the original list passed to the procedure, then the procedure will loop back to stage 30, where the subverted list of invoices is unlinked and then a new list created. The :$.HDR variable is needed so that the initial summation page can be printed for each invoice in the original invoice list, but not printed for every meter returned by HTMLCURSOR. This is something internal to this procedure and cannot necessarily be generalised.

This actually works! I very much doubt that the customer realises how much subversion is required to supply what is asked for.

Version 23.1 - Problem with private procedures to print purchase orders

We have upgraded to version 23.1 and have run into a problem with private procedures to print purchase orders. Such procedures do not print the totals part of an order. Preparing such a procedure brings up an almost impossible to understand error.

The actual problem is that new parameters have been added to report WWWPORD_3 that prints the totals. All one has to do to correct this annoying error is to add the new parameters as shown below. I've marked the new parameters in yellow.

There is a similar change to report WWWIV_3 that prints totals of invoices; the same new three parameters appear there and so have to be added to all private procedures for printing invoices.

A new problem with 'clever dick' HTML documents: redeclaration of a cursor

The comptroller of a company for whom I do contract work complained that two standalone reports that present similar data have different values. These reports give sales invoice totals converted in dollars; the invoices may be in at least three different currencies, some with and some without VAT. Years ago I developed an algorithm for calculating the value of a single line: multiply the quantity by the price of the line, taking into account both the discount on the line and the invoice level discount. Once this is known, this intermediate value can be converted to dollars by means of a routine that I saved as a global trigger (i.e. as a trigger in the 'func' screen), that checks the currency of the line (I've seen invoices that have different currencies in the lines and in the header) and acts accordingly. It looks something like this:

  GOTO 110 WHERE :$.CURR = -2;   /* dollars */
  SUB 900 WHERE :$.CURR <> -1;   /* convert to NIS */
  SUB 910;                       /* convert to dollars */
  LABEL 110;
  ...
  #INCLUDE func/TEST_CNV2DOLLAR
  

Subroutine 900 converts the given amount of currency X to NIS according to the exchange rate at the invoice date; subroutine 910 converts NIS to $ is similar to subroutine 900 but does the same thing in reverse. 

One of the reports uses this global trigger in a subroutine that iterates over all the lines of a given invoce. The other report used the FNCTRANS.SUM2 field that gives the dollar value of the financial transaction - whilst it might seem that it gives the appropriate value with no work, this is true only of export invoices; Israeli invoices include VAT and the SUM2 field gives the complete total for the invoice. I tried various approaches that would use this field in order to give the dollar value of the invoice but never succeeded (a new approach to this problem has just occurred to me: subtract from the SUM2 field the value of the VAT field in the invoice header divided by the exchange rate of the transaction. If the VAT is zero then SUM2 will be returned). Eventually I converted the local subroutine that uses a cursor to iterate over the invoice lines to a global trigger and saved it in the 'func' screen.

---

This company has a prediliction for what I am now calling 'container documents', aka complicated 'clever dick' HTML documents: the one that I am working on now (and have been doing so for maybe the past two months) has ten or eleven different reports displayed within a single container. Most of the reports are standalone, but it happens that one report uses a total that has been calculated in a previous report in the container. This requires the use of global parameters, e.g. a variable such as :$.AM, whose name is preceeded by a dollar sign. Internal variables appear to have scope that is restricted to the SQLI section in which they appear, although I am not totally convinced about this*. I suppose that I should check this by defining such a variable in one SQLI section of type integer then redefining it as a real in a different section. If this is so, then there's no real need for the use of global parameters, whose name is constricted to three letters.

Several of these reports display data regarding invoices, so it occurred to me that I could use my new global trigger code instead of having several SQLI stages have the same subroutine. I updated the various stages to use the global trigger; the syntax check of the entire container procedure showed no errors but I was unable to run the procedure. This normally means that I've redeclared a cursor in the procedure. I checked all the stages but could see no duplication; I renamed cursors but saw no duplication; I ran the entire procedure source through my cross referencer but still saw no duplication. I tried removing sections of the code but still the problem existed. After about an hour of extreme frustration, I restored previous versions of the sections (making use of the version control system that I developed a few years ago) and decided to leave things as they are.

This morning, in a splendid display of right brain thinking, the reason for the cursor redeclaration suddenly appeared. I was using a global trigger ... that was using a cursor! In other words, stage 89 defined a cursor C6200, stage 99 - by virtue of including the global trigger - also defined cursor C6200, as did stage 109. In other words, the redeclarations arose because of the use of the global trigger that contains a cursor. Ooops.

The idea that I had at the beginning of this blog as to how to calculate the dollar value of an invoice without using a cursor will provide a solution to the redeclaration problem, assuming that the new function provides the correct result.

* After declaring the same variable as a real in one stage and as a string in another stage, I found out that local variables have scope of the entire procedure and not of a SQLI stage. So there's no need to use 'global variables' prefixed with a dollar sign - one can use regular variables to carry values between stages.

COC document (2)

As Maslow's hammer puts it, when the only tool you have is a hammer, every problem looks like a nail.

This happens to me every now and then in Priority, when I choose a complicated solution involving procedures when there exists a simpler method. For example, someone wanted me to send him copies of delivery notes when the status of those notes is changed to 'final'. I wrote something similar to this for the same person, sending a letter when the status of a customer order changes. This involved writing a procedure that sends an email, that is invoked by a trigger. Complicated. I was about to write the same sort of procedure for the delivery notes when I remembered that I could define a simple rule that would fire when a delivery note reaches a given status: it can send email with an attached document. Request solved after a few minutes of thought and one minute of development. As it happens, a few days later, the person making this request asked me to stop sending the documents!

So, again, with the COC document. I couldn't figure out how to have the HTML document work on devices when the imput is customer orders, so I wrote a procedure that would obtain the devices from the order then invoke the HTML document with this list of devices. I was looking at the documentation again when this statement caught my eye:

HTMLCURSOR (Create HTML Document) — Declares the cursor for a document. This step first creates a linked file that holds the records selected in the PAR input parameter.

If the PAR input parameter contains orders, then the internal cursor will obtain an order number from this linked table, but what is important is the value selected in the query - this value will be stored in the internal variable HTMLVALUE and will be a device number.

My original code in the external procedure was as follows

LINK ORDERS TO :$.ORD;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK SERNUMBERS TO :$.SER;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO SERNUMBERS (SERN, SERNUM)
SELECT ORIG.SERN, ORIG.SERNUM
FROM ORDERS, ORDERITEMS, SERNUMBERS ORIG
WHERE ORDERS.ORD = ORDERITEMS.ORD
AND ORDERITEMS.ORDI = ORIG.TEST_ORDI
AND ORIG.SERN > 0
AND ORDERS.ORD > 0;
EXECUTE WINHTML '-d', 'TEST_WWWCOC', 'SERNUMBERS', :$.SER, '-e';
UNLINK ORDERS;
UNLINK SERNUMBERS;
  

My new code in the HTMLCURSOR stage became

SELECT SERNUMBERS.SERN
FROM ORDERS, ORDERITEMS, SERNUMBERS
WHERE ORDERS.ORD = ORDERITEMS.ORD
AND ORDERITEMS.ORDI = SERNUMBERS.TEST_ORDI
AND SERNUMBERS.SERN > 0
AND ORDERS.ORD > 0;
  

ORDERS is automatically linked to the PAR parameter, so this code provides a list of all the devices linked to the lines in the given list of orders. A much simpler solution than my baroque construction!

I wonder now what I had written in this statement in the first version of the document that receives an order number.

Certificate of conformance document

Many companies issue a 'certificate of conformance' (COC) document for the products that they ship. There is a default document in Priority that is based on delivery notes, but of course, the wording in this document is fixed, whereas every company wants different text. One of my customers asked me to create such a document that would be based on shipping documents, so this was a fairly simple exercise in HTML document programming.

My contact person for this customer used to work with another of my customers, and presumably during the course of a conversation comparing notes, he mentioned that I had created such a document for them. So customer #2 wanted a document ... except that they wanted the document to be based on orders with completely different text.

I started work on creating the new document and immediately ran into the first problem: how many documents do they expect to create from one order? One, for the entire order? One per line? One per device connected to each line? It turns out that they want a document for each device, making my work problematic.

An order line can have a quantity greater than one, and so there may be more than one device with its unique serial number connected to the line. In the standard form for shipping documents, the quantity in the line is displayed, along with a separate report (in Priority terms) that prints a list of serial numbers. This is not what the customer wanted.

My problem was that the input to the HTML document would be orders but the cursor would have to run on devices. I tried various approaches but couldn't get the HTML document to work properly (if at all). After writing a letter explaining why I couldn't provide what the customer wanted, the rubber duck to whom I had been explaining the problem spoke up, pointing out that I could write a separate program that would create a linked file of devices, then invoke the COC document with that linked file.

Indeed. This approach works, although I had some problems at the beginning. In the end, I deleted the HTML document that I had created and created a new one by copying an HTML document that I wrote that prints data about devices. I removed most of the subreports of this copied document and restored the few subreports that I had written for the original COC document. This works!

One more problem that took some time to solve: the COC document will be in English, and I added the E flag in the procedure header. But the document created by the calling procedure had some left to right problems. Eventually I remembered that I had written about this problem a few years ago and how to solve it.

Following is the code of the calling procedure (this is helped by the fact that the customer had previously requested the addition of the order line to which the device is connected; this is default behaviour with shipping document lines):

LINK ORDERS TO :$.ORD;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK SERNUMBERS TO :$.SER;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO SERNUMBERS (SERN, SERNUM)
SELECT ORIG.SERN, ORIG.SERNUM
FROM ORDERS, ORDERITEMS, SERNUMBERS ORIG
WHERE ORDERS.ORD = ORDERITEMS.ORD
AND ORDERITEMS.ORDI = ORIG.TEST_ORDI
AND ORIG.SERN > 0
AND ORDERS.ORD > 0;
EXECUTE WINHTML '-d', 'TEST_WWWCOC', 'SERNUMBERS', :$.SER, '-e';
UNLINK ORDERS;
UNLINK SERNUMBERS;
  

Syntax problem in 'clever dick' HTML documents

In this blog,  I showed a way to fool the HTML document generator by passing it one linked file, but then creating a new linked file and inserting into it only the records that one wants. I am in the process of upgrading our Priority version, and the program that checks procedures gave me an error message for this procedure: "Unresolved identifier TEST_WWWSHOWCUSTA.PAR". This is a mystifying message as the procedure works perfectly and of course PAR is a recognised identifier.

I discovered that the error came from this line, the first in the procedure:

:OLDPAR = :$.PAR;

This saves the value of the parameter holding the list of customers (or whatever) in the local variable 'oldpar'; internally the value of the parameter is the name of an external file somewhere on the server and so can be viewed as a regular string. If I commented out this line, the error message would disappear, although of course, the procedure wouldn't work. This led me to believe that the error message was probably a bug in the syntax checker: I was using syntax that the checker didn't recognise.

My colleague Yitzchok suggested an alternative (and a more SQL-like) syntax:

SELECT :$.PAR INTO :OLDPAR FROM DUMMY;

This has the same effect but also passes the syntax check. Once I discovered this, I quickly changed the three or four procedures that I had written using this 'replacing PAR' technique so that they too would not fail the syntax check. 

:HTMLFNCTITLE

The documentation about this variable is as follows: The report title can be changed at runtime in the SQLI step preceding the report step, using the :HTMLFNCTITLE variable. When would one need/desire to use this variable? When one wants the title of a report to contain the value of a variable; for example, 'List of orders entered on 18/01/22' could be achieved by the following means

:PAR1 = DTOA (SQL.DATE8 - 24:00, 'DD/MM/YY');
SELECT ENTMESSAGE ('$', 'P', 10) INTO :HTMLFNCTITLE FROM DUMMY;
  

where message 10 would be 'List of orders entered on <P1>'  (I use this method to get Hebrew strings into a program - it's easier this way than writing naked strings in the program text that get reversed ... or not). As the documentation states, the above should be in an SQL statement prior to the report whose title one is creating.

This technique is especially useful in 'HTML documents', especially here. But should one does use this technique when there are more reports to be displayed after the one whose title is to be changed, then one must insert another SQLI stage after the given report to nullify the value of :HTMLFNCTITLE.

One might be tempted to use :HTMLFNCTITLE in an 'ordinary' procedure consisting of an SQLI step to collate/calculate data, followed by a CHOOSE step then a few reports, where one will be activated according to the value of the CHOOSE step. Don't get me wrong: the technique will still work, provided one inserts an SQLI step before the desired report. 

So where's the pitfall? 'Ordinary' reports created without :HTMLFNCTITLE display their title (this may come from the report's title - which is limited in length - or from the sub-form 'output title) along with the parameters used to create the report. I find this information very useful when debugging reports that other users have created (e.g. answering the question why a given order doesn't appear in a report - because the parameters of the report don't match those of the order).  This information does not appear in a report whose title has been changed by :HTMLFNCTITLE.

So: it's ok to use :HTMLFNCTITLE in an 'HTML document' but not advisable to use it in an ordinary multi-step report.

Conditional display of sub-reports within an HTML document

Scenario: let's assume that for some customers we want to print customer orders using a certain sub-report whereas for most customers we use the default sub-report. How to do this?

The first thing that needs to be done is to add a field to the customers table/form that shows which customers are 'special'. For the purpose of this blog, I'll assume that there is a simple binary choice between 'special' and 'regular', in which case maybe three or four customers are marked as 'special'. In my real world use of this technique, the distinguishing field is in the 'activities' table and there are about nine different values, so obviously an integer field is needed and not a boolean. I'll call this distinguishing field TEST_CASE.

In the HTML procedure, within the first SQLI statement after the HTMLCURSOR stage there will be a line similar to

SELECT ORD INTO :$.ORD
FROM ORDERS
WHERE ORD > 0;

In real life, that statement is much longer, but irrelevant for didactic purposes. Adding our distinguishing variable to the above statement changes it to

SELECT ORDERS.ORD, CUSTOMERS.TEST_CASE INTO :$.ORD, :$.CAS
FROM ORDERS, CUSTOMERS
WHERE ORDERS.CUST = CUSTOMERS.CUST
AND ORDERS.ORD > 0;

where CAS is defined as a single character variable.

Now for the sub-reports. As this document will be based on the existing procedure WWWSHOWORDER, it's best to copy the procedure to TEST_WWWSHOWORDER and copy the sub-report WWWORD_2R to TEST_WWWORD_2R (assuming that this is the sub-report that we want to display conditionally). Running TEST_WWWSHOWORDER at this stage should produce the same output as WWWSHOWORDER. 

The first change that we need to make is to add the parameter CAS to TEST_WWWORD_2R. Then we need to add to this report the table/field combination DUMMY.DUMMY; this field is of course set to be non-displayable. The field's expression is = 1 AND :CAS <> 'Y'  (this is a good reason why we have to copy the standard sub-report because we have to make a change to it. Otherwise any other procedure that includes this sub-report would fail for the special customers). If we now try to print orders for a 'normal' customer and a 'special' customer, the print-out for a 'normal' customer will include the order lines; these will be missing from the 'special' customer, as for this customer, :CAS will be equal to Y.

The next step is to copy TEST_WWWORD_2R to a new report, let's say TEST_WWWORD_2RA. Within this report, we make whatever changes are necessary in order to display the data in the format required for the special customers. After doing so, the DUMMY condition is changed to  = 1 AND :CAS = 'Y'.

TEST_WWWORD_2RA is now added to the TEST_WWWSHOWORDER procedure; it should have three parameters (as does TEST_WWWORD_2R) where the first two are ORD and CAS. We need to make a very important change to the 'output' variable (probably O2): for our special conditional sub-report we need to define a new 'output' variable. Of course, this variable has to be added to the final INPUT stage and the 'create HTML page' activation has to be run. This is the key step to defining a conditional report. Of course, TEST_WWWORD_2RA has to be added to the list of sub-reports for a given format.

What happens now when we run the procedure for an order belonging to a 'normal' customer? CAS will not be equal to Y; TEST_WWWORD_2R will produce output but TEST_WWWORD_2RA will not (the DUMMY condition prevents any data being displayed). If the procedure is run for an order belonging to a 'special' customer? CAS will now be equal to Y; TEST_WWWORD_2R will not produce any output but TEST_WWWORD_2RA will.

It is vitally important that the two equivalent sub-reports have different 'output' variables; if they were the same, no output would appear for the lines. One sub-report wants to produce data whereas the other doesn't, and seeing as they overlay each other (that's the meaning of the same 'output' variable), no output will appear. They need to have different 'output' variables that do not overlay each other; one will produce no output but the other will, and something is guaranteed to appear.

Even more on 'a safer method': possibly the safest method of all

One comment to these blogs basically said that the PAR parameter in the initial INPUT step doesn't have to be the same as the implicit PAR parameter in HTMLCURSOR. Bearing this in mind, I wrote a new procedure that has the following intermediate step between the INPUT and HTMLCURSOR steps.

SELECT :$.PAR INTO :OLDPAR FROM DUMMY;
LINK CUSTOMERS OLD TO :OLDPAR;
SELECT SQL.TMPFILE INTO :$.PAR FROM DUMMY;
LINK CUSTOMERS TO :$.PAR;
DECLARE CUR CURSOR FOR
SELECT CUST, CUSTNAME
FROM CUSTOMERS OLD
WHERE CUST > 0;
OPEN CUR;
GOTO 300 WHERE :RETVAL <= 0;
LABEL 100;
FETCH CUR INTO :CST, :CNAME;
GOTO 200 WHERE :RETVAL <= 0;
LOOP 100 WHERE RSTRIND (:CNAME, 1, 1) <> '0';
INSERT INTO CUSTOMERS (CUST, CUSTNAME)
VALUES (:CST, :CNAME);
LOOP 100;
LABEL 200;
CLOSE CUR;
LABEL 300;
UNLINK CUSTOMERS OLD;
UNLINK CUSTOMERS ;

This actually works! Obviously I'm taking an arbitrary range of customers but one that meets the criterion of having a number that ends in 0 does have orders and the procedure does create a report of orders for this specific customer. In the real program, a series of tests are run, and only customers that pass these tests are inserted into the linked table.

This method is the safest and easiest - no deletions, no external tables and one person can run the report more than once concurrently with different parameters.

One reason why my previous tests using STACK as an intermediate table failed is that the new record has to be inserted with both the A and U keys (cust and custname respectively). STACK only has one field into which I was inserting the A key; looking at this now in retrospect, it's clear that this wasn't sufficiently as every tuple has to have a value for the U key.

Still more on 'a safer method'

Yesterday's blog included this statement HTMLCURSOR then becomes SELECT CUST FROM CUSTOMERS WHERE CUST > 0 AND TEST_WWWFLAG = 'Y'. Problem solved.

Looking at it this morning, this statement is not strictly true: the problem is solved as long as only one person is running the procedure at any given time.

A better way of solving this would be to define a new table, (say) TEST_CUSTUSER that would have two fields CUST and USER that together comprise the table's key. Then instead of writing UPDATE CUSTOMERS SET TEST_WWWFLAG = 'Y', one would write

DELETE FROM TEST_CUSTUSER WHERE USER = SQL.USER;
INSERT INTO TEST_CUSTUSER (CUST, USER)
SELECT CUST, SQL.USER 
FROM CUSTOMERS /* may or may not be linked */
WHERE CUST > 0;

And of course HTMLCURSOR has to be revised to become

SELECT CUST 
FROM TEST_CUSTUSER 
WHERE CUST > 0
AND USER = SQL.USER;

This still is not ideal: whilst two users can now run the procedure simultaneously, a given user cannot run the procedure with different inputs simultaneously. SQL.DATE would not work as the user could invoke the procedure twice with different parameters within the same minute; also SQL.DATE at the beginning of the procedure would probably not be the same as SQL.DATE at the HTMLCURSOR stage. 

A possible solution to this would be to add another field to the table, GUID. At the beginning of the program one would add the statement :$.MYG = SQL.GUID, then add this field to the delete and insert statements. I suspect, though, that HTMLCURSOR might not recognise this global variable ... and indeed it doesn't. Even had this worked, one doesn't need to use the SQL.GUID function (returning a random 32 char string); one could simply define an integer whose maximum value would be established on entering the procedure, then incremented for the current procedure's run. But again, there would be no way of using this field in HTMLCURSOR. Even storing this maximum value in the LASTS table and accessing it from there in HTMLCURSOR wouldn't work - LASTS is not on a per-user basis, and even if it were, the second run might finish before the first run, so the value in LASTS would not be the correct one.

 

Any ideas? 

A safer method of choosing tuples with complex conditions within an HTML document

Continuing this unintended series of blogs about choosing customers for display within an HTML document when the conditions for the inclusion of a customer are complex .... We've seen that deleting records from the linked CUSTOMERS table can be dangerous, because in certain circumstances that table might not be linked.

There was a very interesting comment left on that entry that suggested using a temporary table such as STACK: one could link this table, load it with records from CUSTOMERS, test the values and then delete from the STACK table. It wouldn't matter too much if this table were not linked. Whilst this technique can work in complex procedures, it won't work in an HTML document. The SDK states in the section 'Basic commands' in chapter 5 that HTMLCURSOR creates a linked file that holds the records selected in the PAR input parameter (emphasis mine). I have always wondered how this command 'knew' from which table to select, and now I know. Linking STACK to :$.PAR then inserting records into this linked table from CUSTOMERS didn't work in my tests (the insertion worked fine, but the procedure did not display any documents).

Here is a much safer method, although it requires some preparation. Add a field to the CUSTOMERS table and call it something like TEST_WWWFLAG with type CHAR, length 1. Set the value of this field to 'Y' in the step that checks the suitability of records for inclusion before the cursor iterates over the records. Thus one can simply write UPDATE CUSTOMERS SET TEST_WWWFLAG = 'Y'. As this should be the only place in Priority that accesses this field, it doesn't matter whether the table is linked or unlinked. At the end of the cursor code, replace the infamous DELETE statement with this: UPDATE CUSTOMERS SET TEST_WWWFLAG = '\0' WHERE CUST = :CST. Thus any record that fails all the checks will have this field empty.

HTMLCURSOR then becomes SELECT CUST FROM CUSTOMERS WHERE CUST > 0 AND TEST_WWWFLAG = 'Y'. Problem solved.

If anyone is wondering: fortunately the deletion of customers happened at the end of the day so not many people were inconvenienced. After a series of phone calls, the database administrator was able to restore the missing table. I checked that the data had been restored successfully by accessing customer orders that had been entered that day then checking their status log to see until when there were changes. Even so, there was one check that had not occurred to me: it might have been that a customer had been added after the backup that was restored; in this case, there would be no record in CUSTOMERS but there would be in CUSTOMERSA. A quick check revealed that indeed there was such a record, so I quickly added a new customer; this received the same autoinc key number as the final record in CUSTOMERSA. In the customers screen, I saw that this customer had a linked contact person (!) , causing me to check whether there was a sales opportunity for this unknown customer. Indeed there was, enabling me to see who had opened the customer; from this person I obtained the customer's name. So that episode is safely behind us. 

NFILE: advantages and disadvantages

I am sure that I have written about NFILE at some time but I can't find the reference and so I'll start from the beginning. [Edit: I found the entry which now has the NFILE label]

Let's say that one has a procedure that accepts a list of customers as its input - let's even say that it's the HTML document that I worked on yesterday. When one enters a list of customers, the linked customers table will include only those customers, obviously. When one writes * as the input, all the customers from the unlinked customer table have to be copied into the linked customers table; this takes time! 

Enter NFILE: this was introduced in version 17 IIRC. The documentation says: Select NFILE if the linked file comprises a group of records and you want the link table to remain empty when the user enters * or leaves the field empty. How does one use this? By writing code as follows

LINK CUSTOMERS TO :$.CST;  /* NFILE */
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM CUSTOMERS WHERE CUST > 0;
UNLINK CUSTOMERS;
LABEL 1;

This saves time in copying records from the unlinked table to the linked table. Tables that benefit from this are PART, DOCUMENTS, INVOICES and CUSTOMERS. Maybe a few others.

Now back to the HTML document that I described yesterday. I realised that I could filter unwanted customers using a cursor and a series of expressions such as

GOTO 999 WHERE NOT EXISTS (SELECT 1 FROM BRANCHES
WHERE BRANCH > 0);
LINK CUSTOMERS TO :$.CST;
LINK BRANCHES TO :$.BRN;
DECLARE C15 CURSOR FOR
SELECT CUST FROM CUSTOMERS
WHERE CUST > 0;
OPEN C15;
GOTO 999 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C15 INTO :CST;
GOTO 800 WHERE :RETVAL <= 0;
GOTO 200 WHERE :$.A3 <> 'Y'; /* No need to check price quotes if not wanted */
:HASDATA = 0;
SELECT 1 INTO :HASDATA
FROM CPROF, CPROFA, BRANCHES
WHERE CPROF.PROF = CPROFA.PROF
AND CPROFA.BRANCH = BRANCHES.BRANCH
AND CPROF.CUST = :CST
AND CPROF.PDATE >= :$.DAT;
LOOP 100 WHERE :HASDATA = 1;
....
LABEL 700;
/* If we are here, then the customer has failed all the tests
so delete it from the linked table */
DELETE FROM CUSTOMERS WHERE CUST = :CST;
LOOP 100;
LABEL 800;
...

This code is repeated, once for each type of record - sales opportunities, price quotes, orders and projects. The first two lines check whether any branches were passed as a parameter: there's no point in checking anything if there are no branches. I added all the necessary code, ran the program using one specific branch without designating customers: the procedure took a few minutes to sort itself out then showed the output: as designed, only customers with data from the chosen branch were shown.

I wondered how I could improve the procedure's speed - the obvious step would be to define CUSTOMERS as type NFILE instead of FILE. There would be no need to copy customers to the linked table which would save time. So I added this change, ran the program ... and then realised with a shudder that the DELETION WAS NOT FROM THE LINKED TABLE BUT FROM THE REAL TABLE!!! The first code snippet that I showed unlinks CUSTOMERS; thus the DELETE statement does not work on the linked table but rather the real table.

I reached for the power switch (figuratively) as soon as possible but it was too late. A few minutes later, someone called to ask whether customers had disappeared. My code has managed to delete about 16,000 customers out of 24,000 in a few minutes. Straightaway I phoned the network manager who took Priority off line, and then we turned to our database administrator who started restoring the damaged table.

Bottom line: be wary of NFILE! Normally it can help but sometimes it is dangerous.

How to choose sections within an HTML document

I was recently tasked with creating a document that shows general details about a customer, how many price quotations have been issued to the customer, a list of the price quotations themselves, a list of customer orders, service calls, etc. There is a standard document called WWWSHOWCUST that shows some of this data but for some reason it is laid out in a very unattractive manner, and anyway I wanted to include data that isn't included in the standard report (as well as discarding certain sections).

I started off with a 'simple' HTML document: at the beginning, the document is presented with a list of customers and then one by one, the various data are shown, where each type of datum resides in its own report (that is, one report for sales opportunities, one for price quotations, etc). To complicate matters slightly, I decided to add a parameter at the beginning that defines from which date the data will be displayed (or in other words, don't display price quotations (for example) from before this date. So far, so good.

Then I considered the possibility that a user might not want to see all the data; for example, there is a certain amount of duplication between orders and projects (at least, in the way that we work), so there's little reason to show both. I decided that I would have a series of global variables that could be chosen, and depending on these values, the corresponding report would be shown.

To the left is shown the parameters' screen; the checkboxes are connected to variables :$:A1, :$.A2, etc. 

Today's tip is concerned with how those choices are translated into which reports are shown. Let's say that price quotations will be shown if variable :$.A4 = 'Y'. First of all, this parameter is passed to the report, along with CST, which holds the current customer number. In the report one adds the inscrutable line DUMMY.DUMMY whose expression is = 1 and :A4 = 'Y'. For reasons that have never been explained, every DUMMY.DUMMY expression has to start with = 1. If the second half of the expression is true, the DUMMY.DUMMY will be true and the report will be printed. If not ....

This technique enables the HTML document to have one format, where every user chooses what to display, instead of 7! (5040) different combinations!

A later request, that I was able to serve partially, was to display only data belonging to a given branch. This is not a problem for the reports that display price quotations, etc (one simply passes a parameter containing the branch numbers) but it's a problem for reports that don't display data per branch - our company header and the current customer's data. True, it is possible to define that a customer belongs to a specific branch, in which case there would be no problem (one would add the branch in the HTMLCURSOR stage), but we don't do this as customers are free to order from different branches (this is intended to reduce the number of duplicate customers). At the moment, if one passes to the procedure a range of customers, there will be a header for every customer even if there is no data for that customer. Not good.

The problem is how to filter the data (or weed out unwanted customers) before the procedure gets to the HTMLCURSOR stage. I spent about an hour working on this but didn't resolve the situation to my satisfaction. I can see now that I was filtering after the HTMLCURSOR stage whereas in fact I have to do so before this stage, at the very beginning. Maybe I will be able to find a solution after all.

The Letter Generator and Mail Merge

One of my coworkers requested a few days ago that I create a form letter for a given customer order in which certain data from the order would be displayed. I wasn't initially aware of any possibility of doing so in Priority, but after a quick look through the SDK, I discovered that the possibility not only exists but is seemingly simple.

As usual, it was very difficult to understand anything from the SDK, so what follows is my explanation. The goal is to create a form letter with data fields, like this

Dear <customer name>, <address> <contact's telephone number>

With regard to your order <purchase order number>, we are pleased to inform you that we intend to supply the order on <supply date>.

How does one do this? My explanation is as follows: 

First, one creates a report that contains all the necessary fields for the mail merge. The standard example is a report called CUSTLETTERREP; this report can be used whenever one wants mail merge based on customers. As I needed it for customer orders, I had to create a similar report that is based on ORDERS. One creates what seems to be a standard report, but with no parameters; these are supplied by joining ORDERS.ORD to a field called LETTERSTACK.KEY1. Also, LETTERSTACK.USER should be the current user.

Once this is done, one copies the procedure CUSTLETTER to a new procedure (let's call it TEST_ORDLETTER) that is very similar to an HTML document. There are a few changes that need to be made: 

1. The Input stage should reference ORDERS.ORDNAME, not CUSTOMERS.CUSTNAME
2. HTMLCURSOR now becomes SELECT ORD FROM ORDERS WHERE ORD > 0
3. SQLI (stage 30) now becomes

   
       /************ Get ORD from HTMLVALUE by priform ************/
   :$.ORD = ATOI(:HTMLVALUE);
   :$.CLN = 'ORDERS.ORD';
   INSERT INTO LETTERSTACK (USER, KEY1, KEY2, STATUSTYPE)
   VALUES (SQL.USER, :$.ORD, 0, 'O');
   SELECT ORDNAME INTO :$.NUM
   FROM ORDERS ORIG
   WHERE ORD = :$.ORD;
   

4. The final stage is now TEST_ORDERSREP instead of CUSTLETTERREP

Maybe that sounds very complicated but it's actually reasonably simple.

Then one goes to the 'letter generator' program which is here: CRM > Customer Relations Management > Letter Generator. Here one defines the name of the desired letter and the name of the procedure that will create the letter (in this case, TEST_ORDLETTER). If this is the first time that this procedure has been listed, there will be a pause of a few minutes before one can continue.

In the son form, 'free text', one obviously enters the form letter. I wondered at first how one selects the fields that appear in the letter: look carefully and one will see that there is a new icon that has been added to the icons in the text form. To the right of the picture of a diskette now appears an icon that looks like a rubbish bin (that can't be right!); pressing on it brings up a list of fields that were defined in the TEST_ORDERSREP report. This works in the same way that one can add a field to be displayed in a business rule.

I have designed my procedure to be called as a direct activation from the ORDERS screen but it doesn't have to be. However the procedure is invoked, a list of possible formats is displayed from which one must choose (in the same way that one chooses a format for an HTML document) - the names of these formats are the names of the desired letters than one entered in the letter generator.

Now one can read the documentation in the SDK and make sense of it.

An important definition in 'HTML documents'

I had occasion today to program an 'HTML document' that enables me to document (no pun intended) an interface that reads an external file. A standard report exists that lists the fields defined in the interface but there is no means of extracting the SQL part of the interface, which is the more interesting part that requires documenting. Hence the HTML document.

As usual, I was faced with the problem of how to remove the frame around the document's header. I can never remember how to do this so I am documenting it here. In the report that contains the header, one goes to the 'HTML definitions' son form and looks for the field '# Page Columns'; this will probably be the first field in the third tab. This should be set to 1.

Another field that is useful in this son form is 'Show Title? ' - this should be set to D when one wants the report's title to be displayed and left empty when one doesn't want the title. Normally reports that display a table should have their title displayed, whereas reports that show comments or similar should not have a title.

Beware of the dollar sign!

At a company for which I am doing piecework programming, I was asked to fix a procedure that was giving an error message about being unable to create a file. I looked at the procedure (one which prints invoices); the problematic code was trying to create a string whose contents - basically the invoice number and customer email - would be displayed as a barcode in the final document.

Why is this problematic? For those who don't know, the character set that can be displayed in a barcode is severely limited to upper case characters, digits and a few characters such as * and $, but not @. Email addresses have the '@' character, so the original programmer had to find a way of replacing this with the '$' character.

In order to achieve this, the original programmer used a baroque set up of creating the initial string (with '@'), writing it to a file, then running the FILTER program three times (!) in order to get the file contents (i.e. the string) into the required format; one run replaced @ with $, one run turned the entire string into upper case; I don't know (nor care) what the third run did. This file was then loaded via an interface into a simple table from which the string was extracted and eventually displayed. Breathtaking in its ingenuity but totally misguided. To be fair, the procedure might have been written for a much earlier version of Priority in which certain functionality might be missing.

The actual problem that the client faced was that the intermediate files were being written to the root of disk C: which is of course a no-no. I simply changed the file directory and the procedure started working. But the sheer complication of this procedure irked me and I was sure that I could find a simpler way of creating the required string.

Here is my first attempt:

SELECT TOUPPER (CUSTOMERSA.EMAIL) INTO :EMAIL
FROM CUSTOMERSA, INVOICES
WHERE CUSTOMERSA.CUST = INVOICES.CUST
AND INVOICES.IV = :$.IV;
SELECT STRCAT ('*E', IVNUM, 
STRPIECE (:EMAIL, '@', 1, 1), '$',
STRPIECE (:EMAIL, '@', 2, 1), '*') INTO :$.BC
FROM INVOICES
WHERE IV = :$.IV;

If the invoice number were IV200001 and the email test@microsoft.com, the resulting string would be expected to be '*EIV200001TEST$MICROSOFT.COM*'. Unfortunately this would not be the result. Using the naked dollar sign ('$') causes the preprocessor in the parser to replace this with the name of the procedure, resulting in something like 

'*EIV200001TESTTEST_WWWSHOWCIV2MICROSOFT.COM*'! This is normally a good thing as it enables one to pass the current procedure name to the ENTMESSAGE function as $, without denoting the name - as a result, the same code can be copy/pasted between procedures without problem.

A method of obtaining the naked dollar sign without using the naked dollar sign is required! After some lateral thinking, I came up with the following, but unfortunately it too inserts the first letter of the procedure's name instead of the dollar sign.

SELECT TOUPPER (CUSTOMERSA.EMAIL) INTO :EMAIL
FROM CUSTOMERSA, INVOICES
WHERE CUSTOMERSA.CUST = INVOICES.CUST
AND INVOICES.IV = :$.IV;
:DOLLAR = '1$1';
SELECT STRCAT ('*E', IVNUM, 
STRPIECE (:EMAIL, '@', 1, 1), STRIND (:DOLLAR, 2, 1),
STRPIECE (:EMAIL, '@', 2, 1), '*') INTO :$.BC
FROM INVOICES
WHERE IV = :$.IV;

Incidentally, I don't see the above as being programming in Priority; it's got nothing to do with working with a database engine. Instead, it's more general programming in the context of the Priority programming language, which isn't something that can be easily taught. One has to remember that in a procedure, '$' is going to be expanded into the procedure's name.

I finally figured out how to solve the problem - use the code of currency -2, which is ... $ (at least, in companies where the default currency is NIS; for companies where the default currency is dollars, the number is -1). The below works in a test procedure on my server, but I will have to implement it fully on the client's server in order to be sure.

SELECT TOUPPER (CUSTOMERSA.EMAIL) INTO :EMAIL
FROM CUSTOMERSA, INVOICES
WHERE CUSTOMERSA.CUST = INVOICES.CUST
AND INVOICES.IV = :$.IV;
SELECT STRCAT ('*E', INVOICES.IVNUM, 
STRPIECE (:EMAIL, '@', 1, 1), CURRENCIES.CODE,
STRPIECE (:EMAIL, '@', 2, 1), '*') INTO :$.BC
FROM INVOICES, CURRENCIES
WHERE INVOICES.IV = :$.IV
AND CURRENCIES.CURRENCY = -2;

The above code did not work on the client's server as they have defined the code of currency -2 to be USD. Tired of knocking my head against a brick wall, I swiftly defined a personal table of constants, defined a constant with the value '$' then inserted the appropriate code into the procedure. This finally works!!

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.