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.
Prices of the Day
11 July, 2005, and in, and in
|3 July, 2005||Initial draft|
|7 July, 2005||Changes from initial implementation|
|9 July, 2005||Revised SQL for effective price|
|10 July, 2005||Final SQL for effective price|
|11 July, 2005||Special Project administrator notifications|
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.
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);
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;
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;
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';
A new web program, pod.cgi, provides access to POD data.
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
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;
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
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';—end—