VbzCart/tables/cust charges

About

• Purpose: Charges to customer bank cards
• Notes:
  • Batches are needed for batch-mode card processing. This was useful when credit cards were debited using PTC, which dialed in the charges using a modem (and hence you wanted to do as much offline as possible), but it's not necessary for PayPal. Will migrate later if it seems useful. Non-batch-mode charges can leave ID_Batch set to NULL.
  • I've decided that the field Resolved in the MS Access version should be a separate table listing unresolved charge records; this shouldn't be something that needs to happen when code is working properly.
• Fields:
  • CardSafe: human-readable (and searchable) summary of "safe" card data: T-nnnn xnnnn ppostal_code
  • AmtSold and AmtShip are necessary for PayPal Virtual Terminal, which wants to know these amounts separately. Presumably other card processing systems may also want this information.
  • AVSAuthChar: it's not clear whether any software besides PTC returns this code. It's also not clear what it means. Only Visa cards ever seem to return this code.
  • WhenDecided and isAccepted was WhenAuthorized and WhenRefused in Access
  • WhenHeldUp is specifically for old orders where the data which would complete the charge record is missing, and I don't want to fill it in with bogus data just to get it out of the queue. I can imagine that it might be useful for suspending charges due to technical difficulties, but I can't think of a scenario in which some other solution wouldn't be more appropriate. Still: set the timestamp in this field (and make a note!) if you need to pull a charge out of the queue without completing it.
  • To view only charges which need further processing, filter for: <mysql>(WhenDecided IS NULL) AND ((WhenXmitted IS NULL) OR isSuccess) AND (WhenVoided IS NULL) AND (WhenHeldUp IS NULL)</mysql>
• History:
  • 2009-07-09 moved to separate wiki page; no design changes
  • 2013-08-15 modifying to meet customer security requirements
    • adding "Encrypted" field for now just in case someone decides to break in before I've fully implemented security
  • 2013-08-23 New fields to handle encryption properly: CardCrypt,CardSafe; Encrypted is now DEPRECATED
  • 2013-09-01 Decided it's silly to duplicate the card information here, especially if we have to encrypt it.
    • It's enough to point back to the proper card record in cust_cards and keep only the "safe" info here.
    • The "safe" info can be formatted to be searchable without needing to be rigorously parsable.
    • Removing CardCrypt, CardNumExp, CardBillAddr, and of course Encrypted

SQL

<mysql>CREATE TABLE `cust_charges` (

 `ID`         INT NOT NULL AUTO_INCREMENT,
 `ID_Batch`   INT DEFAULT NULL COMMENT "batch within which this payment was processed",
 `ID_Order`   INT COMMENT "core_orders.ID of order for which this charge is being made",
 `ID_Card`    INT COMMENT "cust_cards.ID of charge card debited",
 `ID_Trxact`  INT COMMENT "cust_trxacts.ID of transaction generated by this charge",
 `AmtTrx`     DECIMAL(9,2) COMMENT "amount transacted (debited or refunded; negative number = refund)",
 `AmtSold`    DECIMAL(9,2) COMMENT "amount for items sold, before shipping/tax",
 `AmtShip`    DECIMAL(9,2) COMMENT "amount for shipping",
 `AmtTax`     DECIMAL(9,2) COMMENT "amount for tax",
 `CardSafe`     VARCHAR(127)     NOT NULL  COMMENT "unencrypted 'safe' card info in human-readable format",
 `WhenEntered`  DATETIME                   COMMENT "when charge was entered",
 `WhenXmitted`  DATETIME                   COMMENT "when processed via credit card service provider",
 `WhenHeldUp`   DATETIME                   COMMENT "when this charge was suspended (do not process)",
 `isSuccess`    BOOL         DEFAULT FALSE COMMENT "did the charge go through?",
 `isTest`       BOOL         DEFAULT FALSE COMMENT "YES = this is a test charge, to be voided if successful",
 `Confirmation` VARCHAR(31)  DEFAULT NULL  COMMENT "confirmation code returned by card service",
 `AVSRespCode`  VARCHAR(1)   DEFAULT NULL  COMMENT "Address Verification System response code",
 `AVSAuthChar`  VARCHAR(1)   DEFAULT NULL  COMMENT "'Auth. Char. Ind.' in PTC",
 `WhenDecided`  DATETIME     DEFAULT NULL  COMMENT "when we decided whether or not to accept this charge",
 `isAccepted`   BOOL         DEFAULT FALSE COMMENT "NO = we decided not to accept the charge; it should be voided",
 `WhenVoided`   DATETIME DEFAULT NULL COMMENT "when the charge was voided",
 `Notes`        VARCHAR(255) COMMENT "manually-entered notes on this charge",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>