Perl Email Order Processing Script - Explanation

This is the script used to automate your website's order processing as described in the chapter on automation. It's run automatically when a new email arrives, and it's output is sent to a log file 'ebot.log'.

ebot.pl uses the following files:

• stdin - standard input - the email arrives here!
• stdout - all the print statements in the program print out to here. When the program is run, stdout is redirected to the file 'ebot.log' using the 'append' redirection operator '>>'.
• count - a count of the number of emails received.
• order.<count> - a log of the individual order.

...and the following database tables:

• orders - new orders are inserted into here.
• products - passwords are retrieved from here.

See the page on automation for general info on automating your order processing.

See the page on Installing scripts for details on customising and installing the scripts.

• The first line tells Unix this is a Perl program. The second is a comment describing the script.
   #!/usr/local/bin/Perl
 # e.bot - V1.8 - TGH 5 July 2001
  
  
• This makes the DBI (DataBase Interface) library of functions available.
   use DBI;
  
  
• Define a variable to count the number of errors that occur while processing the email.
   my $err = 0; # Current error number
  
  
• Define a variable and put the current time in it.
   my $t=gmtime();
  
  
• Print a start-up message with the program name and the time.
   print "\n>> e.bot... ($t) ";
  
  
• Declare a variable to hold the current email count. The count of the number of emails received is held in a file called 'count', not in the database. This is in case the database isn't working for any reason - we'd still get a count - which is our order number.
   my $cnt;
  
  
• Open the count file for reading and writing. The file descriptor 'CNT' is later used by other function to access the file.
   open (CNT, '+</home/timh/cgi-bin/count');
  
  
• Get the current number from the count file into the variable '$cnt'
   read (CNT, $cnt, 10);
  
  
• Set the file pointer back to the start of the file (like rewinding a tape!)
   seek (CNT, 0, 0);
  
  
• Write the new count (old count incremented by 1) into the count file.
   print CNT ++($cnt);
  
  
• Close the count file. Print the current count.
   close (CNT);
 print "ORDER: $cnt\n";
  
  
• Open a new file for writing the details of this order into. The file is called 'order.<count>
   open (OUT, ">/home/timh/orders/order.$cnt");
  
  
• Begin a 'while' loop: Loop while there's still data on <stdin> (Unix's standard input file). This reads each line from stdin and stores in in the default variable ('$_')
   while (<STDIN>) {
  
  
• Ignore lines that only contain spaces (blank lines).
    next if /^\s*$/;
  
  
• Print the current line out to the order log file. $_ is the default variable.
    print OUT $_;
  
  
• Print the current line out to the program log file (ebot.log). When you give no arguments to the print function it prints the default variable to the default output - stdout (Unix's standard output file).
    print;
  
  
• Remove the 'newline' character from the end of the line.
    chomp;
  
  
• If the current line contains the string 'Order Number', then the textthat follows is the order number from the credit card processor. Save the order number in an appropriately named variable.
    if (/Order Number: /) { $order_id = $'; }
  
  
• Repeat the above for all the data fields we want to extract from the email...
    if (/Product ID: /) { $product_id = $'; }
  if (/Name: /) { $name = $'; }
  if (/Address1: /) { $addr1 = $'; }
  if (/Address2: /) { $addr2 = $'; }
  if (/City: /) { $city = $'; }
  if (/State: /) { $state = $'; }
  if (/Zip: /) { $zip = $'; }
  if (/Country: /) { $country = $'; }
  if (/Phone: /) { $phone = $'; }
  if (/^E-Mail: /) { $email_addr = $'; }
  if (/Subscribe E-Mail: /) { $subscribe = $'; }
  if (/Grand Total: /) { $total = $'; }
  } # ...end of 'while' loop
  
  
• Remove any spaces from the beginning or end of all the values.
   for ($order_id) { s/^\s+//; s/\s+$//; }
 for ($product_id) { s/^\s+//; s/\s+$//; }
 for ($email_addr) { s/^\s+//; s/\s+$//; }
 for ($total) { s/^\s+//; s/\s+$//; }
 for ($name) { s/^\s+//; s/\s+$//; }
 for ($addr1) { s/^\s+//; s/\s+$//; }
 for ($addr2) { s/^\s+//; s/\s+$//; }
 for ($city) { s/^\s+//; s/\s+$//; }
 for ($state) { s/^\s+//; s/\s+$//; }
 for ($zip) { s/^\s+//; s/\s+$//; }
 for ($country) { s/^\s+//; s/\s+$//; }
 for ($phone) { s/^\s+//; s/\s+$//; }
 for ($subscribe) { s/^\s+//; s/\s+$//; }
  
  
• If certain essential fields are missing - it's an error! Add some text describing the error to the $err_txt variable, and increment the count of errors each time.
   if (!$order_id) { $err_txt[$err++]="No order id\n"; }
 if (!$product_id) { $err_txt[$err++]="No product id\n"; }
 if (!$email_addr) { $err_txt[$err++]="No email address\n"; }
 if (!$total) { $err_txt[$err++]="No total price\n"; }
  
  
• Connect to the database.
   $dbh = DBI->connect("DBI:mysql:timh:localhost:3306", 'timh', 'proper001');
  
  
• Create a SQL insert statement to insert the new order data into the orders table...
   $statement = "insert into orders (counter, order_id, pid, name, addr1, addr2, city, state, zip, country, phone, email, subscribe, price)";
  
  
• ...It's a big statement, so it's split into 2 lines for legibility. The .= operator appends the string to what's in the variable. The '"' characters are escaped this means that a special escape character is put in front of them to remove their special meaning... Normally the '"' character would end the string, but we actually want the string to contain them. The escape character can be user defined in Perl, by default it's the backslash '\'.
   $statement .= "values ($cnt, \"$order_id\", \"$product_id\", \"$name\", \"$addr1\", \"$addr2\", \"$city\", \"$state\", \"$zip\", \"$country\", \"$phone\", \"$email_addr\", \"$subscribe\", \"$total\")";
  
  
• Prepare the execute the SQL. Print the number of rows affected (should always be 1, or 0 on failure).
   $sth = $dbh->prepare($statement) or print "Can't prepare the SQL\n";
 $rv = $sth->execute or print "Can't execute the query: $sth->errstr";
 print $rv, " rows inserted\n";
  
  
• Create a SQL statement to get the password for the product that was purchased. Prepare and execute the query.
   $statement = "select password from products where pid=\"$product_id\"";
 $sth = $dbh->prepare($statement) or print "Can't prepare the SQL\n";
 $rv = $sth->execute or print "Can't execute the query: $sth->errstr";
  
  
• If the query returns no rows, the product_id is unknown, add an error to the '$err_txt' array.
   if ($rv == '0') { $err_txt[$err++]="Product not found\n"; }
  
  
• Get the result of the query into the array '@row'.
   @row = $sth->fetchrow_array;
  
  
• Drop the statement handle - we don't need it anymore and you have to do it before closing the database!
   $sth->finish;
  
  
• If there were errors, and you have the customer's address, email them listing and apologising for the problem...
   if ($err) {
  if ($email_addr) {
   &send_mail ($email_addr, "Error in Order!", "Dear Customer,\n The following errors occurred while processing your order:\n@err_txt\n\nA human will process your order within the next few days. I apologise for the delay.\n\nThanks\n\n");    }
  
  
• Print the list of errors to stdout (ebot.log).
    print "$err errors!: \n@err_txt";
  }
  
  
• If everything was OK - send the password to the customer...
   else { &send_mail($email_addr, "Your Book Password...", "Dear Customer,\n Thankyou for your order.\n\nThe password for the item you ordered is: $row[0]\n\nIf you have any problems, please contact us: \n\nsales\@yourdomain.com"); }
  
  
• Disconeect from the DB.
   $dbh->disconnect;
  
  
  
• This is the 'send_mail' subroutine that's used above. It uses the unix program 'sendmail'. If your webhost uses another mail program, such as 'qmail', you may have to change this line. Consult their documentation. This works fine with phpwebhosting.
  
  There are 3 parameters that need to be passed to 'send_mail', they are accessed as $_[0] to $_[2] within the subroutine itself:
  
  
  1. email address to send the mail to. ($_[0])
  2. the subject of the mail. ($_[1])
  3. The text of the mail. ($_[2])
  
   # Send Mail Subroutine... -------------
 sub send_mail {
  
  
• Open the program 'sendmail' This is just like opening a file, except you use the pipe symbol '|'
   open(MAIL, "|/usr/lib/sendmail -t") or die ("FATAL: Failed open mail!");
  
  
• Send all text until 'EOM' to the mail program. the '\n' character means 'newline'.
   print MAIL <<"EOM";
 To: $_[0]
 Subject: $_[1]
 From: Your Name <yourname\@yourdomain.com>
 Sender: e\-bot\@yourdomain.com
 Reply-To: yourname\@yourdomain.com\n
 $_[2]
 \n.\n
 EOM
 
  
  
• Close the sendmail program.
   close (MAIL); }