@title;noquote@ @signatory;noquote@ @context_bar;noquote@

Setup

Under the Hood

This is provided just for your own information. You may never need to know any of it, but it may prove useful if something goes wrong or if you want to extend the module.

Financial Transactions

A financial transaction is inserted whenever a credit card authorization to charge or refund is made. These transactions may or may not be "marked" to be carried through to fulfillment. The specifics:

When an order is placed, an authorization is done for the full cost of the order. @package_name@ creates one or two new ec_financial_transactions. Two rows are created when the order consists of some items that require shipping while others don't, otherwise @package_name@ creates a single row. The rows each have a unique transaction_id and are tied to the order using the order_id. @package_name@ immediately captures the transaction for the items that do not require shipping. @package_name@ captures the transaction for the items that need shipping when the items ship.

When a shipment is made, if it is a full shipment of the order, the financial transaction is captured using the existing authorization from when the order was first placed. (The field to_be_captured_p of the financial transactions become 't', the to_be_captured_date is inserted, and @package_name@ attempts to capture it.)

However, if only a partial shipment is made, a new authorization has to be made (therefore a new row is inserted into ec_financial_transactions, to_be_captured_p is set to 't', the date is inserted into to_be_captured_date and the system attempts to mark and capture it).

When a refund is made, a row is also inserted into ec_financial_transactions. A refund is only inserted when it needs to be captured, so there is no need to set to_be_captured_p if transaction_type='refund'.

Scheduled procs periodically mark or settle every transaction that needs to be captured.

Gift Certificates

Each customer has a gift certificate balance (it may be $0.00), which you can determine by calling the PL/SQL function ec_gift_certificate_balance. Different chunks of a customer's balance may expire at different times because every gift certificate that is issued has an expiration date.

When the system applies a customer's gift certificate balance to an order, it begins by using the ones that are going to expire earlier and continues chronologically until either the order is completely paid for or until the customer's gift certificates are spent. If only part of a gift certificate is used, the remaining amount can be used later.

If a customer purchases a gift certificate for someone else, the recipient (who may or may not be a registered user of the site) is emailed a claim check that they can use to retrieve the gift certificate and have it placed in their gift certificate balance. Note: "retrieving" a gift certificate is equivalent to inserting the user_id of the owner into ec_gift_certificates. Retrieved gift certificates always belong to registered users because gift certificates can only be retrieved during the course of placing an order, at which time an unregistered user becomes registered.

Site administrators can issue gift certificates to customers at will. In this case, no claim check is generated. The gift certificate is automatically assigned to that user_id.

Order States

Order states are discussed in detail in Operation of the Ecommerce Module. That should be read to understand the concepts of order states and item states and to see the finite state machines involved.

Below is a very boring diagram of what order state the order should be in given the item state of the items in that order. This diagram only covers the order states VOID, PARTIALLY_FULFILLED, FULFILLED, and RETURNED. All other order states are grouped under OTHER. In all other order states, the items are of a uniform item state, so it is either quite obvious what the order state will be or it is completely independent of what the order state will be.

An "X" in a column implies that there is at least one item in that item state --maybe more.

Item StateOrder State
VOID RECEIVED_BACK SHIPPED OTHER
X X X X PARTIALLY_FULFILLED
X X X 0 FULFILLED
X X 0 X PARTIALLY_FULFILLED
X X 0 0 RETURNED
X 0 X X PARTIALLY_FULFILLED
X 0 0 X OTHER
X 0 0 0 VOID
0 X X X PARTIALLY_FULFILLED
0 X X 0 FULFILLED
0 X 0 X PARTIALLY_FULFILLED
0 X 0 0 RETURNED
0 0 X X PARTIALLY_FULFILLED
0 0 0 X OTHER

Shopping Cart Definitions

Shopping Cart
An IN_BASKET order with the same user_session_id as in the user's cookie.
Saved Cart
An IN_BASKET order with the user_id filled in, no user_session_id filled in, and saved_p='t'
Abandoned Cart
An IN_BASKET order with saved_p='f' and a user_session_id that doesn't correspond to the user_session_id in anyone's cookie (e.g. the user's cookie expired or they turned cookies off). There is no way of determining whether a shopping cart has been abandoned. These are different from expired orders which are automatically put into the order state EXPIRED if they are still IN_BASKET after N days, where N is set in the @package_name@ parameters.)

Credit Card Pre-Checking

Before credit card information is sent out to the payment gateway for authorization, some checking is done by the module to make sure that the credit card number is well-formed (using the procedure ec_creditcard_precheck which can be found in /tcl/ecommerce-credit.tcl). The procedure checks basic characteristics of a credit card number, such as the number of digits, verifies that the card number starts with the right digit for the card type, and if appropriate, does a LUHN-10 check. LUHN-10 is a checksum that determines if the card number is a member of the set of valid credit card numbers.

This procedure only encompasses the three most common credit card types: MasterCard, Visa, and American Express. It can quite easily be extended to include other credit card types. Be sure to examine ec_creditcard_precheck to verify that it does not screen out cards that your merchant system accepts.

Automatic Emails

When you install the system, there are 7 automatic emails included that are sent to customers in common situations (e.g., "Thank you for your order" or "Your order has shipped"). If a site administrator adds a new email template using the admin pages, you will have to create a new procedure that does all the variable substitution, the actual sending out of the email, etc. This should be straight forward. Copy any one of the 7 autoemail procedures in /tcl/ecommerce-email.tcl (except for ec_email_gift_certificate_recipient, which is nonstandard). Then invoke your new procedure anywhere appropriate (e.g. the email that says "Thank you for your order" is invoked by calling ec_email_new_order $order_id after the order has been successfully authorized).

Storage of Credit Card Numbers

Credit card numbers are stored until an order is completely fulfilled. This is done because a new charge might need to be authorized if a partial shipment is made (we are forced to either capture the amount that a charge was authorized for or to capture nothing at all - we cannot capture any amount inbetween; Therefore, we are forced to do a new authorization for each amount we are going to charge the user). A new charge also might need to be authorized if a user has asked the site administrator to add an item to their order.

If you decide to not allow customers to reuse their credit cards, their credit card data is removed periodically (a few times a day) by ec_remove_creditcard_data in /tcl/ecommerce-scheduled-procs.tcl It removes credit card numbers for orders that are FULFILLED, RETURNED, VOID, or EXPIRED.

If you decide to allow customers to reuse their credit cards, their credit card information is stored indefinitely. This is not recommended unless you have top-notch, full-time, security-minded system administrators. Some merchant gateways may have requirements about how credit card data is stored. The credit card numbers are not encrypted in the database because there is not much point in doing so; our software would have to decrypt the numbers anyway in order to pass them off to the payment gateway, so it would be completely trivial for anyone who breaks into the machine to grep for the little bit of code that decrypts them. The ideal thing would be if payment gateways were willing to develop a system that uses PGP so that we could encrypt credit card numbers immediately, store them, and send them to the payment gateway at will. Philip and Alex's Guide to Web Publishing says:

What would plug this last hole is for a payment gateway to give us a public key. We'd encrypt the consumer's card number immediately upon receipt and stuff it into our Oracle database. Then if we needed to retry an authorization, we'd simply send the payment gateway a message with the encrypted card number. They would decrypt the card number with their private key and process the transaction normally. If a cracker broke into our server, the handful of credit card numbers in our database would be unreadable without the payment gateway's private key. The same sort of architecture would let us do reorders or returns six months after an order.

Note 1: The above discussion does not mean that the credit card numbers go over the network unencrypted. Most payment gateways use secure connections.

Note 2: If you want to let your customers reuse their old credit cards, you can reduce some of the risk by manually removing old credit card data once in a while (at least then there will be fewer numbers in your database for the crackers to steal). To clear out the unnecessary credit card data, just run a procedure like ec_remove_creditcard_data (in /tcl/ecommerce-scheduled-procs.tcl). Be sure to bypass the if statement that checks whether SaveCreditCardDataP is 0 or 1.

Price Calculation

The site administrator can give the same product different prices for different classes of users. They can also put products on sale over arbitrary periods of time. Sale prices may be available to all customers or only to ones who have the appropriate offer_code in their URL.

The procedure ec_lowest_price_and_price_name_for_an_item in /tcl/ecommerce-money-computations.tcl determines the lowest price that a given user is entitled to receive based on what user classes they are in and what offer_codes they came to product with. Their offer_codes are stored, along with their user_session_id, in ec_user_session_offer_codes. We store this in the database instead of in cookies, because it is a slightly more efficient method, although either implementation would have worked). One minor complication to this is that if a user saves their shopping cart, we want them to get their special offer price, even though they may be coming back with a different user_session_id; Therefore, upon retrieving saved carts, the offer_codes are inserted again into ec_user_session_offer_codes with the user's current user_session_id. We associate offer_codes with user_session_id as opposed to user_id, because someone with an offer_code should not be prevented from seeing the special offer price if they have not logged in yet).

The above items are just things that I have found myself explaining to others or things that I think will be useful for people extending this module. Obviously the bulk of the module's code has not been discussed here. If you have any questions, please email Eve at eveander@arsdigita.com or Janine at janine@furfly.net