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: same as in
 * 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: (WhenDecided IS NULL) AND ((WhenXmitted IS NULL) OR isSuccess) AND (WhenVoided IS NULL) AND (WhenHeldUp IS NULL)
 * 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 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
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;