Charging the Credit Card

I l @ ve RuBoard

Until now, you've done everything you need to do for the site except actually let a customer pay for a purchase. Some might consider this to be a minor oversight, but it's funny how the folks in accounting get a bit upset if they don't see revenue flowing in. So, to keep them happy, you should probably implement credit card authorization.

As has already happened several times, reconsidering the original design in light of what has been learned during development leads to some redesign. For one thing, the original schema for the table to record orders is needlessly redundant with tables that you already have in the database, and it can be simplified a great deal. You can do this by storing the credit card number, credit card address, and shipping address for the order in the CREDIT_CARD and ADDRESS tables that you already have ”almost.

There is no problem as far as the addresses go because those tables use cross-references between the customer and the shipping address. So, writing new addresses into the database without cross-referenced entries will keep the entries from showing up in the customer's address book. Otherwise, every time an order is saved, a duplicate copy of the shipping and billing addresses would show up in the address book.

Things are a bit more complicated for credit cards. Assuming that you want to use the existing createCreditCard to write a new record for the credit card associated with the order, you'll run into trouble. Because the relationship between credit cards and customers is identified using a key on the credit card that points to the customer ID, you would end up with duplicate credit cards in the wallet if the code just wrote out an exact copy of the credit card record when it stored the order information.

One solution to the credit card duplication is to go back and implement credit cards the same as addresses ”with a cross-reference for the card already in the wallet. This is more work than most people would like to tackle in already running code, if it can be avoided. Thankfully, when you created the CREDIT_CARD table, you didn't make CUSTOMER_KEY a NOT NULL field. This means that if you write out the credit card records for the orders with a NULL in that position, those numbers will never be seen in a wallet but can be referenced by CARD_ID from the ORDERS table.

Remember, the reason you need to record all this information is that you need to keep a snapshot of the billing and shipping addresses and the credit card information as they were on the day the order was taken, even if they are changed in the address book or wallet later. Listing 13.1 shows the revised ORDERS table.

Listing 13.1 Revised Schema for ORDERS

 create table ORDERS (        ORDER_ID           int not null auto_increment,        EMAIL_ADDRESS char(50),        ADDRESS_KEY  int references ADDRESS,        CARD_KEY           int references CREDIT_CARD,        ORDER_DATE      date,        ORDER_SUBTOTAL  float,        ORDER_TAX       float,        ORDER_SHIPPING  float,        ORDER_TOTAL     float,        primary key(ORDER_ID)); 

All the explicit address and credit card fields have been replaced with references to the appropriate tables. The only things explicitly recorded are the various totals and taxes.

With the new schema in place, you can write an order bean to represent the database schema in Java (see Listing 13.2).

Listing 13.2 Order.java

 package com.bfg.product; import java.util.Vector; import java.util.HashMap; import java.util.Iterator; import java.text.NumberFormat; import java.sql.*; import org.apache.log4j.Category; import org.apache.turbine.services.db.TurbineDB; import org.apache.turbine.util.db.pool.DBConnection; import com.bfg.product.Product; import java.util.ResourceBundle; import com.bfg.customer.*; import com.bfg.product.*; import com.bfg.exceptions.*; import com.bfg.cart.*; import javax.naming.NamingException; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingEnumeration; import javax.naming.directory.InitialDirContext; import javax.mail.internet.InternetAddress; import javax.mail.internet.MimeMessage; import javax.mail.internet.AddressException; import javax.mail.Message; import javax.mail.Session; import javax.mail.MessagingException; import javax.mail.Transport; import javax.servlet.http.*; import com.braju.format.*; public class Order {     private static ResourceBundle sql_bundle =       ResourceBundle.getBundle("com.bfg.product.SQLQueries");     private static ResourceBundle email_bundle =       ResourceBundle.getBundle("com.bfg.emailProperties");     static Category cat = Category.getInstance(Order.class);     protected Vector items = new Vector();     public Vector getItems() {       return items;     }     public void setItems(Vector it) {       items = it;     }     protected Customer customer;     public Customer getCustomer() {       return customer;     }     public void setCustomer(Customer c) {       customer = c;     }     protected Address orderaddr;     public Address getAddress() {       return orderaddr;     }     public void setAddress(Address addr) {       orderaddr = addr;     }     protected CreditCard ordercard;     public CreditCard getCreditCard() {       return ordercard;     }     public void setCreditCard(CreditCard cc) {       ordercard = cc;     }     protected int orderNumber;     public int getOrderNumber() {       return orderNumber;     }     public void setOrderNumber(int n) {       orderNumber = n;     }     protected Date orderDate;     public Date getOrderDate() {       return orderDate;     }     public void setOrderDate(Date d) {       orderDate = d;     }     protected double orderSubtotal;     public double getOrderSubtotal() {       return orderSubtotal;     }     public String getOrderSubtotalString() {       NumberFormat nf = NumberFormat.getCurrencyInstance();       return nf.format(getOrderSubtotal());     }     public void setOrderSubtotal(double t) {       orderSubtotal = t;     }     protected double orderTax;     public double getOrderTax () {       return orderTax;     }     public String getOrderTaxString() {       NumberFormat nf = NumberFormat.getCurrencyInstance();       return nf.format(getOrderTax());     }     public void setOrderTax (double v) {       orderTax = v;     }     protected double orderShipping;     public double getOrderShipping () {       return orderShipping;     }     public String getOrderShippingString() {       NumberFormat nf = NumberFormat.getCurrencyInstance();       return nf.format(getOrderShipping());     }     public void setOrderShipping (double v) {       orderShipping = v;     }     protected double orderTotal;     public double getOrderTotal () {       return orderTotal;     }     public String getOrderTotalString() {       NumberFormat nf = NumberFormat.getCurrencyInstance();       return nf.format(getOrderTotal());     }     public void setOrderTotal (double v) {       orderTotal = v;     }     public Order() {      super();     }     public Order(Customer cust, Address ad, CreditCard cc, Cart cart) {      super();      setAddress(ad);      setCreditCard(cc);      setCustomer(cust);      Iterator item_it = cart.getItems();      while (item_it.hasNext()) {          CartItem item = (CartItem) item_it.next();          items.add(item);      }      setOrderSubtotal(cart.getTotal());      setOrderTotal(cart.getGrandTotal(ad));      setOrderShipping(cart.getShipping(ad));      setOrderTax(cart.getTax(ad));     }     public void recordOrder()      throws Exception {      DBConnection dbConn = null;      try         {             dbConn = TurbineDB.getConnection();             if (dbConn == null) {                 cat.error("Can't get database connection");                 throw new Exception();             }             Address ad = (Address) getAddress().clone();             CreditCard cc = (CreditCard) getCreditCard().clone();             cc.setAddress((Address) cc.getAddress().clone());             ad.createAddress();             cc.getAddress().createAddress();             cc.setCustomer(null);             cc.createCreditCard();             PreparedStatement pstmt =                 dbConn.prepareStatement(sql_bundle.getString("createOrder"));             pstmt.setString(1, getCustomer().getEmail());             pstmt.setInt(2, ad.getAddressID());             pstmt.setInt(3, cc.getCardID());             pstmt.setDouble(4, getOrderSubtotal());             pstmt.setDouble(5, getOrderTax());             pstmt.setDouble(6, getOrderShipping());             pstmt.setDouble(7, getOrderTotal());             pstmt.executeUpdate();             pstmt.close();             pstmt =                 dbConn.prepareStatement(sql_bundle.getString("getOrderID"));             ResultSet rs = pstmt.executeQuery();             if (rs.next()) {                 setOrderNumber(rs.getInt(1));             } else {                 cat.error("Couldn't find record for new Order");                 throw new Exception();             }             rs.close();             pstmt.close();             Iterator items = getItems().iterator();             pstmt =                 dbConn.prepareStatement(sql_bundle.getString("addOrderItem"));             while (items.hasNext()) {                 CartItem item = (CartItem) items.next();                 pstmt.setInt(1, getOrderNumber());                 pstmt.setString(2, item.getProduct().getISBN());                 pstmt.setString(3, item.getProduct().getTitle());                  pstmt.setInt(4, item.getQuantity());                   pstmt.setDouble(5, item.getProduct().getPrice());                   pstmt.setDouble(6, item.getLineItemPrice());                   pstmt.executeUpdate();             }             pstmt.close();             setOrderDate(new java.sql.Date(new java.util.Date().getTime()));         }     catch (Exception e)             {                cat.error("Error during createOrder", e);                throw new CustomerActivityException();             }     finally         {           try              {                  TurbineDB.releaseConnection(dbConn);              }          catch (Exception e)              {                  cat.error("Error during release connection", e);              }           }     }     public static Order findOrder(int orderNumber)      throws Exception {      Order order = null;      DBConnection dbConn = null;      try          {              dbConn = TurbineDB.getConnection();              if (dbConn == null) {                  cat.error("Can't get database connection");                  throw new Exception();              }              PreparedStatement pstmt =                  dbConn.prepareStatement(sql_bundle.getString("findOrder"));              pstmt.setInt(1, orderNumber);              ResultSet rs = pstmt.executeQuery();              if (!rs.next()) {                  rs.close();                  pstmt.close();                  return null;              }              order = new Order();              order.setOrderNumber(rs.getInt("ORDER_ID"));              order.setAddress(Address.findAddress(rs.getInt("ADDRESS_KEY")));              order.setCreditCard(CreditCard.findCreditCard(rs.getInt("CARD_KEY")));              order.setOrderDate(rs.getDate("ORDER_DATE"));              order.setOrderSubtotal(rs.getDouble("ORDER_SUBTOTAL"));              order.setOrderTax(rs.getDouble("ORDER_TAX"));              order.setOrderShipping(rs.getDouble("ORDER_SHIPPING"));              order.setOrderTotal(rs.getDouble("ORDER_TOTAL")); order.setCustomer(Customer.findCustomer(rs.getString("EMAIL_ADDRESS")));               rs.close();               pstmt.close();               pstmt =                   dbConn.prepareStatement(sql_bundle.getString("getOrderItems"));               pstmt.setInt(1, orderNumber);               rs = pstmt.executeQuery();               while (rs.next()) {                   CartItem item = new CartItem();                   Product product = new Product();                   item.setProduct(product);                   item.setQuantity(rs.getInt("QUANTITY"));                   product.setISBN(rs.getString("PRODUCT_ISBN"));                   product.setTitle(rs.getString("PRODUCT_TITLE"));                   product.setPrice(rs.getDouble("UNIT_PRICE"));                   double totalPrice = rs.getDouble("TOTAL_PRICE");                   if (totalPrice == 0F) {                       item.setPromotionItem(true);                   }                   order.getItems().add(item);               }               rs.close();               pstmt.close();           }       catch (Exception e)               {                   cat.error("Error during createOrder", e);                   throw new CustomerActivityException();               }       finally           {             try                 {                     TurbineDB.releaseConnection(dbConn);                 }             catch (Exception e)                 {                     cat.error("Error during release connection", e);                 }           }      return order;     }     public void emailReceipt(String email)      throws NamingException, AddressException, MessagingException {      Context initCtx = new InitialContext();      Context envCtx = (Context) initCtx.lookup("java:comp/env");      Session session = (Session) envCtx.lookup("mail/session");      Message message = new MimeMessage(session);      message.setFrom(new InternetAddress(email_bundle.getString("fromAddr")));      InternetAddress to[] = new InternetAddress[1];      to[0] = new InternetAddress(email);      message.setRecipients(Message.RecipientType.TO, to);      message.setSubject("Receipt for order " + getOrderNumber());      StringBuffer contents = new StringBuffer();      contents.append("Thank you for shopping at Books for Geeks.\n\n");      contents.append("Here is the receipt for your order " + getOrderNumber() +                      " placed on " + getOrderDate() + "\n\n");      contents.append("CODE        TITLE                           QUANT  PRICE  TOTAL\n");      contents.append(  "================================================================\n");      Iterator items = getItems().iterator();      while (items.hasNext()) {          CartItem it = (CartItem) items.next();          Parameters p = new Parameters();          p.add(it.getProduct().getISBN());          p.add(it.getProduct().getTitle());          p.add(it.getQuantity());          p.add(it.getProduct().getPrice());          p.add(it.getLineItemPrice());          contents.append(Format.sprintf("%-10.10s  %-30.30s  %2d     $%5.2f $%5.2f\n",  p));      }      message.setContent(contents.toString(), "text/plain");      Transport.send(message);     } } 

Two bundles are needed here: the SQL bundle for your various queries and the e-mail bundle last used for the lost password code so that order confirmations can be sent.

Two versions of the constructor are provided: one used internally here and a helper version that takes a customer, address, credit card, and cart and then copies all the values to local properties.

There are a few things to note about the recordOrder method (which writes the order to the database). First, the code starts by cloning the addresses and credit card information. This is because you want it to pass them to the create methods to put new ones in the database; you wouldn't want the method to somehow damage the existing ones in the wallet and address book as a side effect.

After recordOrder writes the main ORDERS record, it records all the individual items in ORDER_ITEM. Remember, the whole purpose of this exercise is to preserve order details from changes in the underlying product or customer information, such as a change of address or product price. The ORDERS table records the exact order information as it was entered on the day the order was placed.

findOrder is the mirror of recordOrder . The only thing of note here is that you want to properly preserve the promotion flags. This is done by checking the line item price of the items to see if they are 0. If so, the code sets the promotion flag. This makes them render correctly when displayed in order history.

After the site has recorded the order, you'll want it to e-mail out a receipt. This code works much the same as the lost password code until you get into the body of the message.

One of the things that I miss most about C when I work in Java is sprintf . It's so handy for getting lines of text lined up, especially when doing things like plain-text receipts. Along with atoi , it's the thing I spend the most time coding around. Luckily, there are now public sprintf clones available so that I don't have to do without its capabilities. In this case, I'm using a version written by a programmer named Henrik Bengtsson, which can be obtained from http://www.braju.com/. Because it comes as a ZIP file, you must unzip it to a directory and then JAR it back up in a .jar file; then you can put it in your \TOMCAT\LIB directory and take advantage of it.

One caution regarding this library: Unlike some freeware products, Bengtsson has released the sprintf code for free use only for noncommercial applications. If you were going to deploy a real e-commerce site using it, you would have to contact him to arrange licensing. Typically, for a small library like this, the cost runs less than $100, but you should double-check before you become too dependant on it.

It works almost the same as the C version, except that instead of passing the arguments as a variable argument list to sprintf itself, you build a parameter list with the arguments and hand it in as a single argument. Bengtsson's Web site gives some good examples of how to use the products, but it is pretty clear cut, as emailReceipt shows.

You might recall that recordOrder requires Address and CreditCard to support the clone operation.

To use the clone operation with an object, the class must implement the Cloneable interface. Normally, you simply add implements Cloneable to the class definition, but, unfortunately , the clone method is protected by default, so you need to wrap it in a public method if you want other classes to be able to take advantage of it. Listing 13.3 shows how to do it for the Address class.

Listing 13.3 Additions to Address.java

 public class Address implements Cloneable { . . .     public Object clone() throws java.lang.CloneNotSupportedException {      return super.clone();     } 

We also need to implement clone for the CreditCard class. In addition, you need to make a small change in createCreditCard to allow the customer to be null (for the credit cards written by the order). If you don't, you will get an exception when the method tries to do getCustomer.getCustomerId() . Listing 13.4 shows the changes.

Listing 13.4 Additions to CreditCard.java

 public class CreditCard implements Cloneable { . . .               if (getCustomer() == null) {                   pstmt.setNull(1, java.sql.Types.INTEGER);               } else {                   pstmt.setInt(1, getCustomer().getCustomerId());               } . . .     public Object clone() throws java.lang.CloneNotSupportedException {      return super.clone();     } 

A few things need to be added to the cart (see Listing 13.5). One is a stub credit card authorization method, which, for your purposes, will always return true . You also need a method to clear the cart after the order is recorded.

Listing 13.5 Additions to Cart.java

 import com.bfg.customer.CreditCard; . . .     public boolean authorizeCharge(CreditCard cc) {      return true;     }     public void clear() {      contents.clear();     } 

Finally, you need the new SQL statements added to the SQLQueries properties file (see Listing 13.6).

Listing 13.6 New SQLQueries Entries

 createOrder=INSERT INTO ORDERS (EMAIL_ADDRESS, ADDRESS_KEY, CARD_KEY, ORDER_SUBTO TAL, \                                 ORDER_TAX, ORDER_SHIPPING, ORDER_TOTAL, ORDER_DATE)  VALUES \                                (?, ?, ?, ?, ?, ?, ?, NOW()); getOrderID=SELECT LAST_INSERT_ID() addOrderItem=INSERT INTO ORDER_ITEM (ORDER_ID, PRODUCT_ISBN, PRODUCT_TITLE, \                                      QUANTITY, UNIT_PRICE, TOTAL_PRICE) VALUES \                                      (?, ?, ?, ?, ?, ?) findOrder=SELECT * FROM ORDERS WHERE ORDER_ID=? getOrderItems=SELECT * FROM ORDER_ITEM WHERE ORDER_ID=? 

I l @ ve RuBoard