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.

Introduction

This document describes the implementation of Price of the Day.

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.

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.

Database Changes

Price Change Table

The price_change table records the (new) price whenever it is changed.  The price is recorded as NULL when a stock item is deleted.

CREATE TABLE price_change (
	apn CHARACTER(15) NOT NULL,
	effective TIMESTAMP NOT NULL,
	price NUMERIC(9,2),
	srp NUMERIC(9,2),
	UNIQUE (apn, effective)
);

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();

INSERT INTO price_change
	SELECT barcode, current_date, price, srp
	FROM stock
	WHERE barcode IS NOT NULL;

A new index is created for stock barcode, because we join it to price_change on apn.

CREATE INDEX stock_barcode ON stock(barcode);

Special Project Administrator, and in

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.

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;

Last Price Check

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.

ALTER TABLE stores ADD COLUMN last_price_check TIMESTAMP;

Email Notifications

User email address and notification preferences are captured using a new User Preferences page.  Users set their preferences using the new web program, preferences.cgi.

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';

New Price of Day Program

A new web program, pod.cgi, provides access to POD data.

Prices of the Day

The price of a title, as at any date in the past, is displayed after the following invocation:

pod.cgi sid=sessionid apn=barcode date=yyyy-mm-dd

List of Price Changes

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.

pod sid=sessionid since=yyyy-mm-dd

The following SQL returns a list of prices changed since any previous effective date:

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;

Price Changes

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.

pod.cgi sid=sessionid notify

Monitoring Price Checks

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.

pod.cgi sid=sessionid checksince=days

SELECT poscust, last_price_check, name, contact
FROM stores
WHERE last_price_check < CURRENT_TIMESTAMP - interval '3 days';