VbzCart/tables/cust charges
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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 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>
- 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>