< VbzCart | tables
Revision as of 21:46, 1 September 2013 by Woozle (main doc for CardSafe is now in cust_cards)
- Purpose: Charges to customer bank cards
- 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.
- CardSafe: same as in Template:VbzCart
- 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>
- 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
<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>