[ic] In Stock Notification

Bill Randle billr@exgate.tek.com
Thu, 4 Jan 2001 09:26:19 -0800


Doug, et. al.,

I finally got time to follow up on my previous cron suggestion with
some more details.

This method has three pieces, although the second and third could be
combined into a single page.

(1) a crontab entry for the 'interchange' user that runs periodically
    (pick your time interval). For example, to run once a day at 4pm:
	16 * * * * /usr/bin/GET \
		http://mysite.com/cgi-bin/myshop/cron/pending >> \
		/tmp/pending_log

(2) create a file "pending.html" and put in an accessable location
    (in this case, a "cron" directory underneath the "pages" dir).
    This file is just a wrapper for including the file that does the
    "real" work. If you want multiple checks or updates to happen,
    you can add additional [include] tags for them. For example:
	>>>> pending.html <<<<
	[include cron/check_pending]

(3) create the "check_pending" script, which lives in a different "cron"
    directory, this one at the same level as the "pages" dir. I.e.:
		interchange
		     |
		-----|--------- ...
		|	   |
	       cron       pages
			    |
			------------ ...
			|	|
		      cron    special

    You can use pretty much any directory structure you want - this just
    happens to be what I have.

    In "check_pending" you might have something like this:

[tag export inventory][/tag]
[tag export pending][/tag]
[mvasp tables="inventory pending"]
<%
	no strict;
	$Document->hot(1);
	$CGI::cookie = 'CRON';
	$Scratch->{mv_no_session_id} = $Scratch->{mv_no_count} = 1;
	HTML("\nEntering pending/inventory update");
	# open 'pending' db and grab entries for users that haven't been
	# notified their requested item is now in stock
	my $dbh = $Sql{pending};
	my $sth = $dbh->prepare(q{ SELECT * FROM pending WHERE notified = '0'})
	    or return error_message("can't open pending database");
	my $rc = $sth->execute()
	    or return HTML("\ncan't open pending database");
	my $hash;
	while($hash = $sth->fetchrow_hashref()) {
		# $hash has all column values:
		#     code username email initial_date notify_date skus
notified
		# "skus" is an array of out of stock items

		# foreach sku, check the current inventory quantity and if > 0,
		# send the user email (email address could from the 'pending'
db,
		# or be looked up in the 'userdb', given the username).

		# if any notification emails were sent for this user then:
		#  (1) update the "notified" and "notify_date" fields of
		#      the 'pending' db.
		#  (2) if this user still has outstanding requests, then
		#      (a) either update the "skus" field of the existing
		#	   record, deleting the items that are now in stock, or
		#      (b) close the existing record and create a new record
		#	   with those item numbers still out of stock
	}
%>
[/mvasp]

Flushing out the "check_pending" script is left as an exercise to the
implementor. :-)

Finally, get and install 'GET', if you don't already have it. 'GET' is part
of the Perl LWP library.

As a final note, I'm sure there are other variations or implementations
possible. This is just an outline to get you going. Also note that this
is being used with minivend 4.04. It should be completely portable to
Interchange, but won't work with minivend 3.x without some changes.

	-Bill