[ic] Stock Notification System that works - maybe not elegant :)
Russ Mann
interchange-users@interchange.redhat.com
Tue Mar 19 14:48:01 2002
This Stock Notification system works. All the maintainer has to do is
update the inventory of the products and the users will automatically be
sent notification that the product they want is now in stock. It uses 2 IC
pages and one separate perl script, and relies on mysql data. If someone
could write the separate perl script into some sort of Interchange page,
that would probably help for ultimate integration.
-Russ Mann
mysql table named stock_alert:
CREATE TABLE stock_alert (
uid int(11) NOT NULL auto_increment,
sku varchar(20) NOT NULL default '',
price decimal(10,2) NOT NULL default '0.00',
description varchar(255) NOT NULL default '',
author varchar(255) default NULL,
fname varchar(255) default NULL,
lname varchar(255) default NULL,
email varchar(255) NOT NULL default '',
date date NOT NULL default '0000-00-00',
PRIMARY KEY (uid),
UNIQUE KEY uid (uid),
KEY uid_2 (uid)
) TYPE=MyISAM;
stock-alert.html
-----------------------------------------
[comment]
ui_template: Yes
ui_template_name: leftonly
[/comment]
[set component_after][/set]
[set members_only]0[/set]
[set page_banner]Stock Alerting[/set]
[set component_hsize]2[/set]
[set hbanner][/set]
[set component_before][/set]
[set hpromo_type]specials[/set]
[set cross_codes][/set]
[set page_title]Stock Alerting - __COMPANY__[/set]
@_LEFTONLY_TOP_@
<!-- BEGIN CONTENT -->
[if session arg]
[seti code][data session arg][/seti]
[/if]
[if value form_errors]
<font __FFACE__ size="2" color="red">
<b>[value form_errors] [value name=form_errors set=""]</b></font>
<br><br>
<font __FFACE__ size="2">
Please try your request again.
</font>
<p>
[/if]
[seti bo_display][data table=products column=display key='[scratch
code]'][/seti]
[seti bo_description][data table=products column=description key='[scratch
code]'][/seti]
[seti bo_author][data table=products column=author key='[scratch
code]'][/seti]
[seti bo_price][data table=products column=price key='[scratch
code]'][/seti]
[seti bo_retail][data table=products column=retail key='[scratch
code]'][/seti]
<div align="center"><span class="pageheader"><a href="[scratch code]"
class="artheader">[scratch bo_description] [if scratch bo_author]<br> by
[scratch bo_author]</a>[/if]</span></div>
<table border="0" cellspacing="0" cellpadding="3" width="100%">
<tr><td valign="top" align="center">
[page href="[scratch code]" send=1]<img src="/thumbnails/[scratch
bo_display]" border="0" align="left"></a>
<br><u>Current Pricing</u><br>
<strong>Our Price: </strong><font color="#800000">[currency][scratch
bo_price][/currency]!</font><br>
<strong>Retail Price: </strong><strike>[currency][scratch
bo_retail][/currency]</strike><br>
<strong>Your Savings</font>:</strong>
<font color="#FF0000" face="Arial"><em>[currency][calc][scratch
bo_retail]-[scratch bo_price][/calc][/currency]
</td></tr>
<tr><td valign="top">
<p>
Please complete the form below and we will contact you once the item you
have selected becomes
available. This is a complementary feature and you are not in any way
obligated to purchase
the item.
</p>
</td></tr>
<tr><td valign="top">
<p>
You may also pre-order the item. When the item becomes available, you will
be
notified via email and the order will be shipped the same day.
<br>
<center><a href="[area href=order arg="[scratch code]"]">Click Here To
Preorder</a></center>
</p>
</td></tr>
</table>
<div align="center">
<form action="[process-target]" method="post">
<input type="hidden" name="mv_todo" value="return">
<input type="hidden" name="mv_check" value="check_alert_form">
<table width="400" border="1" cellspacing="0" cellpadding="3"
bordercolor="#000000">
<tr>
<td colspan="2" class="bgsidebar"><font __FFACE__><b><font size="1"
color="#FFFFFF">Contact Information</font></b></font></td></tr>
[set check_alert_form]
[if !value email]
form_errors=We must have an email address in order to notify you.
mv_nextpage=@@MV_PAGE@@
[else]
mv_nextpage=stock-alert-added
[/else]
[/if]
[/set]
[set send-alert]1[/set]
<tr><td align="right"><font __FFACE__ size="1">[error name=fname
std_label="First Name" required=1]</font>
</td><td align="left"><b><font __FFACE__ size="1">
<input type="text" name="fname" value="[value fname]" size="40"
maxlength="40">
</font></b></div>
</td></tr>
<tr><td><div align="right"><font __FFACE__ size="1">[error name=lname
std_label=Last required=1]</font>
</td>
<td align="left">
<input type="text" name="lname" value="[value lname]" size="40"
maxlength="40">
</td></tr>
<tr><td align="right"><font __FFACE__ size="1">[error name=email
std_label="Email Address" required=1]</font>
</td><td align="left">
<input type="text" name="email" value="[value email]" size="40"
maxlength="40">
</td></tr>
</table>
<p>
<input type="hidden" name="bo_sku" value="[scratch code]">
<input type="hidden" name="bo_description" value="[scratch bo_description]">
<input type="hidden" name="bo_author" value="[scratch bo_author]">
<input type="hidden" name="bo_price" value="[scratch bo_price]">
<input type="submit" value="Submit Request">
</form>
</div>
<!-- END CONTENT -->
@_LEFTONLY_BOTTOM_@
-----------------------------------------
stock-alert-added.html
-----------------------------------------
[comment]
ui_template: Yes
ui_template_name: leftonly
[/comment]
[set component_after][/set]
[set members_only]0[/set]
[set page_banner]Stock Alerting[/set]
[set component_hsize]2[/set]
[set hbanner][/set]
[set component_before][/set]
[set hpromo_type]specials[/set]
[set cross_codes][/set]
[set page_title]Stock Alert - __COMPANY__[/set]
@_LEFTONLY_TOP_@
<!-- BEGIN CONTENT -->
[if session arg]
[seti code][data session arg][/seti]
[/if]
[seti bo_time][tag time]%Y-%m-%d[/tag][/seti]
[flag type=write tables="stock_alert"]
[perl tables="stock_alert"]
$sql_req = "
INSERT INTO stock_alert SET
sku = '$Values->{bo_sku}',
price = '". $Tag->filter('sql', $Values->{bo_price})."',
description = '". $Tag->filter('sql', $Values->{bo_description})."',
author = '". $Tag->filter('sql', $Values->{bo_author})."',
fname = '". $Tag->filter('sql', $Values->{fname})."',
lname = '". $Tag->filter('sql', $Values->{lname})."',
email = '". $Tag->filter('sql', $Values->{email})."',
date = '$Scratch->{bo_time}'
";
$Db{stock_alert}->query("$sql_req");
return;
[/perl]
<div align="center"><span class="pageheader"><a href="[scratch code]"
class="artheader">[scratch bo_description] [if scratch bo_author]<br> by
[scratch bo_author]</a>[/if]</span></div>
<table cellpadding="8" border="0">
<tbody>
<tr><td align="left" valign="top"><font __FFACE__ size="2">
<b>Your request has been sent sucessfully.</b></font>
<br><br>
<font __FFACE__ size="2">
We will contact you once your selected item becomes available.<br><br>
Thank you for your interest in our products.
</font><p>
Back to <a href="[area href="[scratch code]"]">[value bo_description]</a>
</p></td></tr>
</tbody>
</table>
<p>
[set name=alert_added interpolate=1]
[comment] SEND REPORT OF NEW ALERT ENTRY [/comment]
[email
to="[value email]"
subject="__COMPANY__ - Stock Alert Request"
from="__COMPANY__ <__EMAIL_INFO__>"
reply="__COMPANY__ <__EMAIL_INFO__>"]
Hello and thank you for your interest in __COMPANY__ products.
We have received your in-stock alert notification and we will contact you
once the selected item becomes available.
This a confirmation of your request submission.
Name: [value fname] [value lname]
Email: [value email]
Title: [value bo_description]
Author: [value bo_author]
Current Price: [currency][value bo_price][/currency]
You can find this product here:
http://domain.dom/cgi-bin/catalog/[value bo_sku].html?;;BOSYS
Thank you,
__COMPANY__ Backorder Robot
[/email]
[/set]
<!-- END CONTENT -->
@_LEFTONLY_BOTTOM_@
-----------------------------------------
daily cronned perl script:
#!/usr/bin/perl
use Mysql;
use Date::Manip;
$host = 'localhost';
$database = 'db';
$user = 'user';
$password = 'pass';
$table = 'stock_alert';
$company = 'My Company';
$mailprog = "/usr/sbin/sendmail -t";
$dbh = Mysql->connect($host, $database, $user, $password);
$dbh->selectdb($database);
$sql = "SELECT * FROM $table";
$sth = $dbh->query($sql);
$lines = $sth->numrows;
for ($ng = 0 ;$ng < $lines ;$ng++) {
$sth->dataseek($ng);
%row = $sth->fetchhash;
@hi = keys %row;
foreach $hi(@hi) {
$$hi = $row{$hi};
$$hi =~ s/'/\\'/g;
}
$email =~ s/\@/\\\@/g;
if (!&is_backordered("$sku")) {
&mail_user("$sku","$email","$fname","$lname","$description","$author","$pric
e","$date");
push (@delete_uids, $uid);
}
}
foreach $d_uid (@delete_uids) {
$sql = "DELETE FROM $table WHERE uid = '$d_uid'";
$sth_del = $dbh->query($sql);
}
sub is_backordered {
$sku = $_[0];
if (!$backorder_hash{$sku}) {
if (!$quantity_hash{$sku}) {
my $sql_bo = "SELECT quantity FROM inventory WHERE sku = '$sku'";
my $sth_bo = $dbh->query($sql_bo);
$sth_bo->dataseek(0);
my $row_bo = $sth_bo->fetchrow;
$quantity_hash{$sku} = $row_bo;
if ($quantity_hash{$sku} < 1) {
$backorder_hash{$sku} = "YES";
} else {
$backorder_hash{$sku} = "NO";
}
}
}
my $yesno;
if ($backorder_hash{$sku} eq "NO") {
$yesno = 0;
} else {
$yesno = 1;
}
return ($yesno);
}
sub mail_user {
my ($sku, $email, $fname, $lname, $description, $author, $price, $date) =
@_;
my $url = "http://domain.dom/cgi-bin/catalog/" . $sku . ".html?;;BOSYS";
open (MAIL, "|$mailprog $email") || die "Can't open $mailprog $_";
print MAIL "To: $email\n";
print MAIL "From: webmaster\@domain.dom\n";
print MAIL "Subject: $description is now In Stock!\n\n";
print MAIL <<MAIL_TEXT__;
Hello $fname $lname,
On $date you specified to be notified when
"$description"
by $author
came in stock. Today this title became available.
The price is: $price
You can find this product here:
$url
Thank you,
$company Backorder Robot
MAIL_TEXT__
close(MAIL);
}