|
Page
21 of 22 - Chapter 14
Sweetness Follows - Order Transaction Completed
Successfully
The
transaction has completed successfully, the sun is shining and
MusicMadOnline.com are a few dollars richer. All that needs
doing now is to e-mail the order shipping department telling
them what to send and to whom. We'll also e-mail the customer
just to confirm the order is being sent. The page itself also
displays confirmation and a thank you message.
To
send an e-mail we will use Collaboration Data Objects for Windows
NT Server, which are installed on NT Server when you install
Option Pack 4. You'll need to either have a mail server, such
as Microsoft Exchange, located on your server machine or use
SMTP (Simple Mail Transfer Protocol) to divert messages to a
machine with a mail server. If you don't have a mail server
on your machine, using SMTP's smart host facility enables you
to point to your mail server. Open up the IIS console and check
that SMTP is loaded and running on your server machine. Right
click the SMTP service and choose Properties. Select the Delivery
tab and in the Smart host property enter either your mail server's
IP address or friendly name. Now messages should be routed for
delivery to your mail server.
Our first
goal is to build up the text for the e-mails, retrieving the
information from the database. To do this we need to create
two more stored procedures.
First,
to retrieve the customer details, we need to create a CustomerDetails
stored procedure. We know the OrderId as it's passed from the
proceeding page so we can use that to do a join between the
Orders table and Customer table.
CREATE PROCEDURE [CustomerDetails]
( @OrderId int )
AS
SELECT Title, FirstName, LastName, Email, Street,
City,Locality,PostCode,Country
FROM Orders JOIN Customer ON Customer.CustId = Orders.CustId
WHERE Orders.OrderId = @OrderId |
Our
second stored procedure is OrderDetails, which returns details
of each item the customer ordered.
CREATE PROCEDURE [OrderDetails]
( @OrderId int )
AS
SELECT OrderItem.ItemId, Qty, ArtistName, Title, Price
FROM (OrderItem JOIN Stock ON Stock.ItemId = OrderItem.ItemId)
JOIN _
Artist ON Artist.ArtistId = Stock.ArtistId
WHERE OrderItem.OrderId = @OrderId |
Before
leaving Enterprise Manager remember to give MMCustomer Exec permissions
for both the stored procedures.
Now
to create the AcceptOrder.asp page. We appended the OrderId to
the end of the URL in ProcessOrder.asp when we redirected to this
page. We can now retrieve it using Response.QueryString. This
information is all we need to start pulling back customer and
order information from the database.
Having
created a new ADO.Connection object and opened a connection to
the database we then populate a Recordset using the CustomerDetails
query.
<!--#include file="ServerSideGlobalDef.inc"-->
<%
// Get Order info
var sCustEmail = "Thank you for shopping with MusicMadOnline.com\n\n
_
The following items\n\n";
var sOrderDeptEmail = "Send the following items
- \n\n";
var sAddress = "";
var iTotal = 0;
var iOrderId = Request.QueryString;
var loConn = Server.CreateObject("ADODB.Connection");
var loRS;
// Retrive customer details from database
loConn.Open(sdbConnString);
loRS = loConn.Execute("CustomerDetails " +
iOrderId); |
Now we can retrieve the customer's address and e-mail address
from the database before closing the recordset.
// create customer address
part of e-mail
sAddress = sAddress.concat(loRS("Title"), "
", loRS("FirstName"), _
" ", loRS("LastName"),"\n");
sAddress = sAddress.concat(loRS("Street"),
"\n", loRS("City"), "\n",
_
loRS("Locality"),
"\n");
sAddress = sAddress.concat(loRS("PostCode"),
"\n", loRS("Country"), _
"\n");
sEmail = new String(loRS("Email"));
loRS.Close(); |
Now
we need to build up a list of the items the customer has ordered.
Another recordset is created using the OrderDetails stored procedure
and we then loop though it row by row building up the e-mail's
text: one e-mail for the shipping department and one as confirmation
for the customer.
// Retrive order details
- list of all items to be sent
loRS = loConn.Execute("OrderDetails " + iOrderId);
// create the order detail par of the e-mail
while (!loRS.Eof)
{
sCustEmail = sCustEmail.concat(loRS("Title"),
" by ", _
loRS("ArtistName"), "
Qty ", loRS("Qty"), " @ £", _
loRS("Price"), "\n");
iTotal = iTotal + loRS("Qty") * loRS("Price");
sOrderDeptEmail = sOrderDeptEmail.concat("ItemId
: ", _
loRS("ItemId"), "\nArtist
Name : ", _
loRS("ArtistName"), "\nTitle
: ", loRS("Title"), _
"\nQty : ", loRS("Qty"),
"\n\n");
loRS.MoveNext();
}
loRS.Close();
loConn.Close();
loRS = null;
loConn = null;
// complete e-mail message
sOrderDeptEmail = sOrderDeptEmail.concat _
("\n\nto the address
below\n\n",sAddress);
sCustEmail = sCustEmail.concat("\n\nHave been shipped
to\n\n",
sAddress, "\n\n£",
iTotal + 2.5, _
" will be debited from
your credit card\n");
sCustEmail = sCustEmail.concat("\nIf you have any
queries please _
email us at orders@MusicMadOnline.com");
|
With
the e-mail message created we can now send it using CDONTS's NewMail
object.
First
we create a new NewMail object, simply use NewMail's Send method
to send the e-mail. The Send method has 5 parameters: From E-mail
Address, Send to E-mail Address, Subject, Message Body and importance.
I have left the importance parameter at its default value of normal.
|
