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 carried through to fulfillment. The specifics:
When an order is placed, an authorization is done for the full
cost of the order, so a row is inserted into ec_financial_transactions.
This row has a unique transaction_id and it is tied to the order
using the order_id. This isn't captured yet (not until
the items ship).
When a shipment is made, if it's a full shipment of the order, the
financial transaction inserted when the order is first placed
is ready to be captured (to_be_captured_p becomes 't' and the
system attempts to mark and 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' 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 if it is definite that it needs to be captured,
so there is no need to set to_be_captured_p if transaction_type='refund'.
Scheduled procs go around and do the follow-through (making sure everything
is marked/settled) for 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 the soonest and continues
chronologically until either the order is completely paid for or until the
customer's gift certificates run out. 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 (possibly many) item
in that item state.
| Item State | Order 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's 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're still IN_BASKET after N days, where N is set in the .ini file)
Credit Card Pre-Checking
Before credit card information is sent out to CyberCash 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). The procedure checks the length of the credit card number, makes sure
it starts with the right digit for the card type, and does a LUHN-10
check (that's a checksum which can't determine whether the number is a
valid credit card number but which determines whether it's even possible
for it to be a valid credit card number).
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.
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 easy. Just copy any
one of the 7 autoemail procedures in /tcl/ecommerce-email (except
for ec_email_gift_certificate_recipient, which is unusual).
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 can't capture any
amount in between; 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've decided not to 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 (it removes credit card numbers for
orders that are FULFILLED, RETURNED, VOID, or EXPIRED).
If you've decided 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.
The credit card numbers are not encrypted in the database because there
isn't much point in doing so; our software would have to decrypt the
numbers anyway in order to pass them off to CyberCash, 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 CyberCash were willing to develop a system that uses PGP so that we
could encrypt credit card numbers immediately, store them, and send them
to CyberCash at will. Philip and Alex's Guide to Web Publishing says:
What would plug this last hole is for CyberCash 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 CyberCash 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 Cybercash's private key. The same
sort of architecture would let us do reorders or returns six months after an order.
CyberCash has "no plans" to do anything like this.
Note 1: If you or the company you work for are very powerful or
influential, perhaps you can put a little fire under CyberCash's bum
to get them to make that change (Levi's couldn't convince CyberCash
when we were doing a project for them). It's not like it would be
that hard for CyberCash to implement it.
Note 2: The above discussion does not mean that the credit
card numbers go over the network unencrypted. CyberCash's closed-source
software on your machine encrypts the numbers immediately before sending them out.
Note 3: 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) but get rid of 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 determines the lowest price that a given user
is entitled to receive based on what user classes they're 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 decided to store this in
the database instead of in cookies because it was 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 had to associate offer_codes
with user_session_id as opposed to user_id
because someone with an offer_code shouldn't be prevented
from seeing the special offer price if they haven't logged in yet).
