September 13, 2018
Many years ago, I wrote two brief notes expressing the advantages of writing email using HTML. I recently realised that neither note was indexed by major search engines, and so I have written this third document, incorporating the earlier ones, and linked it to my Web so that I (and you) can find it easily.
The notes were contraversial; some people, especially computer experts of a similar age as myself, decry HTML in email, often citing the bloat that accompanies HTML. That claim has some merit, and I agree that using pretty, graphical borders and background images in email adds nothing of substance and plenty to the amount of data that has to be transmitted, however, I remain convinced that the advantages are substantial.
Perhaps, the biggest benefit, is that HTML is almost universal. It makes your document look pretty on vastly different size screens, without you having to do any special work.
The two notes that I wrote showed actual examples of email that I had sent.
They were technical proposals, the sort of document that you might write using a good word processor,
complete with multi-level headings, samples of computer code, and tables of data.
They were also examples of the sort of document that technical people often try to express using just
normal ASCII (plain text
), for example, by indicating italics and bold by surrounding text
with slashes (/
) and asterisks (*
).
Certainly, it's readily apparent what people mean when they do that, but it looks ugly and cumbersome.
It becomes especially ugly when people try to use plain text to draw a table or diagram.
The boxes around cells, formed using hyphens, plus-signs and vertical bars, take up an innordinate amount
of room on screen and also increase the the size of the message quite significantly, which is ironic if
you consider that the argument against HTML is that it increases the size of the message.
Consider the following two tables:
| Cost Price | SRP | |
|---|---|---|
| Low | High | |
| $5.80 | $7.00 | $9.95 |
| $7.01 | $9.60 | $12.95 |
| $9.61 | $11.20 | $14.95 |
| $11.21 | $13.50 | $17.95 |
| $13.51 | $14.90 | $19.95 |
| $14.91 | $15.65 | $22.95 |
| $15.66 | $18.50 | $24.95 |
| $18.51 | $20.42 | $29.95 |
| $20.43 | $23.83 | $34.95 |
ASCII Table
+-------------+------+
| *Cost Price*| |
+------+------+ *SRP*|
| *Low*|*High*| |
+------+------+------+
| $5.80| $7.00| $9.95|
| $7.01| $9.60|$12.95|
| $9.61|$11.20|$14.95|
|$11.21|$13.50|$17.95|
|$13.51|$14.90|$19.95|
|$14.91|$15.65|$22.95|
|$15.66|$18.50|$24.95|
|$18.51|$20.42|$29.95|
|$20.43|$23.83|$34.95|
+------+------+------+
Both contain the same data, they look similar, but the HTML table looks neater.
Finally, I particularly want to show that HTML is surprisingly readable. The following two sections show, first, one of my original notes, and second, the HTML coding for that note. In fact, the HTML coding also includes CSS, which is used to style the document, by setting font sizes, colours and so forth. That's not strictly HTML, but it's very closely associated.
None of this is hard to learn. It's as easy as using a word processor.
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=ISO-8859-1">
<title>POD Detailed Design</title>
<style type="text/css">
/* the default border works around a positioning bug in Firefox */
* { margin: 0; padding: 0; border: 1px dotted white; }
BODY { font-family: sans-serif; font-size: 10pt; color: black; background-color: white; }
H1 { margin: 1em 0 .5em; font-size: 1.5em }
H2 { margin: .5em 0; font-style: italic; font-size: 1.3em }
PRE { font-family: sans-serif; font-size: 80%; background-color: silver; padding: 1cm; margin: 1cm; }
P { margin: .5em 0; text-align: justify; }
.logo {
float: right;
font-family: serif;
text-align: center;
border: 3pt none #00C; border-style: solid none;
padding: 1ex 1em;
}
.logo1 { font-size: 150% }
.logo2 { font-style: italic; font-size: 90% }
.version {
color: gray;
margin-bottom: 1cm;
font-size: 80%;
border: 1px solid gray;
}
.cover { margin: 2cm 0; }
.title { font-size: 360%; }
.sub-title { font-size: 240%; padding-top: .5em }
.author, .date { font-size: 180%; padding-top: 1em }
</style>
</head>
<body bgcolor="#ffffff" text="#000000">
<p>I'm becoming quite enamored with HTML. It lets you express yourself
bloody well better than plain text, and it near as makes no difference
to being a universal, platform-agnostic format. I thought I'd show you
what we could look like when we send email, by forwarding this
real-world example: At 8K it's a bargain; the same order of size as a
plain text rendition and it looks great and prints well, too.<br>
</p>
<div class="logo"><span class="logo1">DNA Pty Ltd</span><br>
<span class="logo2">Programming Center</span>
</div>
<p>
</p>
<div class="cover">
<p class="title">Prices of the Day</p>
<p class="sub-title">Detailed Design</p>
<p class="author">David Newall</p>
<p class="date">11 July, 2005, and in, and in</p>
</div>
<table class="version">
<tbody>
<tr>
<th>Date</th>
<th>Changes </th>
</tr>
<tr>
<td>3 July, 2005</td>
<td>Initial draft </td>
</tr>
<tr>
<td>7 July, 2005</td>
<td>Changes from initial implementation </td>
</tr>
<tr>
<td>9 July, 2005</td>
<td>Revised SQL for effective price </td>
</tr>
<tr>
<td>10 July, 2005</td>
<td>Final SQL for effective price </td>
</tr>
<tr>
<td>11 July, 2005</td>
<td>Special Project administrator notifications </td>
</tr>
</tbody>
</table>
<h1>Introduction</h1>
<p>This document describes the implementation of <i>Price of the Day</i>.</p>
<p>
POD includes a new database table to record historic prices for each
stock line, and
a Web program to search the table.
You can search by date and apn, and be shown the price in effect on
that day; and
you can view a list of all changes to Special Project prices since any
date in the past.
The latter function is restricted to users from Special Project
Participating stores.
A store is defined as "Participating" if it shares its POS code with
another,
the other having "VC" for the first two letters.
</p>
<p>
Emails are sent to Special Project Administrators when Special Project
stock changes price.
In turn, Special Project Administrators can send email, via the system,
to Participating
Store Administrators to alert them to the price changes.
</p>
<h1>Database Changes</h1>
<h2>Price Change Table</h2>
<p>
The <tt>price_change</tt> table records the (new) price whenever it is
changed.
The price is recorded as NULL when a stock item is deleted.
</p>
<pre>CREATE TABLE price_change (
apn CHARACTER(15) NOT NULL,
effective TIMESTAMP NOT NULL,
price NUMERIC(9,2),
srp NUMERIC(9,2),
UNIQUE (apn, effective)
);
</pre>
<pre>CREATE OR REPLACE FUNCTION insert_price_change() RETURNS trigger AS '
DECLARE
bool boolean;
BEGIN
IF tg_op = ''DELETE'' THEN
INSERT INTO price_change(apn, effective, price, srp)
VALUES (old.barcode, CURRENT_TIMESTAMP, NULL, NULL);
RETURN old;
END IF;
IF tg_op = ''INSERT'' THEN
bool := TRUE;
ELSIF
new.srp IS NULL != old.srp IS NULL OR new.srp != old.srp OR
new.price IS NULL != old.price IS NULL OR new.price != old.price
THEN
bool := TRUE;
END IF;
IF bool THEN
INSERT INTO price_change(apn, effective, price, srp)
VALUES (new.barcode, CURRENT_TIMESTAMP, new.price, new.srp);
END IF;
RETURN new;
END
' LANGUAGE plpgsql;
CREATE TRIGGER insert_price_change
AFTER INSERT OR UPDATE OR DELETE ON stock
FOR EACH ROW EXECUTE PROCEDURE insert_price_change();
</pre>
<pre>INSERT INTO price_change
SELECT barcode, current_date, price, srp
FROM stock
WHERE barcode IS NOT NULL;
</pre>
<p>A new index is created for stock barcode, because we join it to
price_change on apn.</p>
<pre>CREATE INDEX stock_barcode ON stock(barcode);
</pre>
<h2>Special Project Administrator, and in</h2>
<p>
A new access right, Special Project Administrator, has been added to
the customer table.
Users with this right will be granted special rights and
responsibilities.
Special Project Administrators currently ensure that Franchisee and
Participating stores action
price changes promptly.
</p>
<pre>ALTER TABLE customers ADD COLUMN conifer_admin CHARACTER(1);
ALTER TABLE customers ALTER COLUMN conifer_admin SET DEFAULT 'F';
UPDATE customers SET conifer_admin = 'F';
ALTER TABLE customers ALTER COLUMN conifer_admin SET NOT NULL;
</pre>
<h2>Last Price Check</h2>
<p>
The date of the last price check will be recorded for each store,
providing a default
date for next time prices are checked, and permitting the Special
Project Administrator to monitor
the frequency of store price checks.
</p>
<pre>ALTER TABLE stores ADD COLUMN last_price_check TIMESTAMP;
</pre>
<h2>Email Notifications</h2>
<p>
User email address and notification preferences are captured using a
new User Preferences page.
Users set their preferences using the new web program, <tt>preferences.cgi</tt>.
</p>
<pre>ALTER TABLE customers ADD COLUMN email_address VARCHAR(80);
ALTER TABLE customers ADD COLUMN mail_price_changes CHARACTER(1);
UPDATE customers SET mail_price_changes = 'F';
</pre>
<h1>New Price of Day Program</h1>
<p>A new web program, <tt>pod.cgi</tt>, provides access to POD data.</p>
<h2>Prices of the Day</h2>
<p>The price of a title, as at any date in the past, is displayed after
the following invocation:</p>
<pre>pod.cgi sid=sessionid apn=barcode date=yyyy-mm-dd
</pre>
<h2>List of Price Changes</h2>
<p>
Participating stores may display a list of all Special Project price
changes since any arbitrary date in the past.
Each price change is flagged as permanent or temporary.
</p>
<pre>pod sid=sessionid since=yyyy-mm-dd
</pre>
<p>The following SQL returns a list of prices changed since any
previous effective date:</p>
<pre>SELECT
supplier, apn, title,
s.price AS cur_price, s.srp AS cur_srp,
e.price AS old_price, e.srp AS old_srp
FROM (
SELECT DISTINCT ON (apn) *
FROM price_change
WHERE effective < date '$latest_release_date'
ORDER BY apn, effective DESC) as p
LEFT JOIN stock s ON (apn = barcode)
LEFT JOIN supplier USING (supplier_code)
WHERE (
p.srp IS NULL != s.srp IS NULL OR p.srp != s.srp OR
p.price IS NULL != s.price IS NULL OR p.price != s.price)
ORDER BY supplier_code;
</pre>
<h2>Price Changes</h2>
<p>
Email is sent to Special Project Administrators every time Supplier
publishes price changes for Special Project stock.
Special Project Administrators must notify Participating stores of
these changes.
This can be done as follows, which causes email to be sent to
Participating Store
Administrators, according to their user preferences.
</p>
<pre>pod.cgi sid=sessionid notify
</pre>
<h2>Monitoring Price Checks</h2>
<p>
The system records the time that each store displays a list of price
changes, both so
a useful default date can be provided for the list of changes, and to
notify Special Project
Administrators of stores that have not checked for recent price
changes.
Special Project Administrators can generate a list of stores which have
failed to check for
price changes during the last three days.
</p>
<pre>pod.cgi sid=sessionid checksince=days
</pre>
<pre>SELECT poscust, last_price_check, name, contact
FROM stores
WHERE last_price_check < CURRENT_TIMESTAMP - interval '3 days';
</pre>
<font size="1">—end—
</font>
</body>
</html>