Difference between revisions of "VbzCart/tables/cust charges"
Jump to navigation
Jump to search
(encryption) |
(main doc for CardSafe is now in cust_cards) |
||
| (3 intermediate revisions by the same user not shown) | |||
| Line 5: | Line 5: | ||
** 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. | ** 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''': | * '''Fields''': | ||
| + | ** '''CardSafe''': same as in {{VbzCart|table|cust_cards}} | ||
** '''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. | ** '''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. | ** '''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. | ||
| Line 14: | Line 15: | ||
** '''2013-08-15''' modifying to meet [[VbzCart/security/customer|customer security requirements]] | ** '''2013-08-15''' modifying to meet [[VbzCart/security/customer|customer security requirements]] | ||
*** adding "Encrypted" field for now just in case someone decides to break in before I've fully implemented security | *** 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 {{vbzcart|table|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== | ==SQL== | ||
<mysql>CREATE TABLE `cust_charges` ( | <mysql>CREATE TABLE `cust_charges` ( | ||
| Line 25: | Line 31: | ||
`AmtShip` DECIMAL(9,2) COMMENT "amount for shipping", | `AmtShip` DECIMAL(9,2) COMMENT "amount for shipping", | ||
`AmtTax` DECIMAL(9,2) COMMENT "amount for tax", | `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", | |
| − | `WhenEntered` DATETIME COMMENT "when charge was entered", | + | `WhenHeldUp` DATETIME COMMENT "when this charge was suspended (do not process)", |
| − | `WhenXmitted` DATETIME COMMENT "when processed via credit card service provider", | + | `isSuccess` BOOL DEFAULT FALSE COMMENT "did the charge go through?", |
| − | `WhenHeldUp` DATETIME COMMENT "when this charge was suspended (do not process)", | + | `isTest` BOOL DEFAULT FALSE COMMENT "YES = this is a test charge, to be voided if successful", |
| − | `isSuccess` BOOL DEFAULT FALSE COMMENT "did the charge go through?", | + | `Confirmation` VARCHAR(31) DEFAULT NULL COMMENT "confirmation code returned by card service", |
| − | `isTest` BOOL DEFAULT FALSE COMMENT "YES = this is a test charge, to be voided if successful", | + | `AVSRespCode` VARCHAR(1) DEFAULT NULL COMMENT "Address Verification System response code", |
| − | `Confirmation` VARCHAR(31) DEFAULT NULL COMMENT "confirmation code returned by card service", | + | `AVSAuthChar` VARCHAR(1) DEFAULT NULL COMMENT "'Auth. Char. Ind.' in PTC", |
| − | `AVSRespCode` VARCHAR(1) DEFAULT NULL COMMENT "Address Verification System response code", | + | `WhenDecided` DATETIME DEFAULT NULL COMMENT "when we decided whether or not to accept this charge", |
| − | `AVSAuthChar` VARCHAR(1) DEFAULT NULL COMMENT "'Auth. Char. Ind.' in PTC", | + | `isAccepted` BOOL DEFAULT FALSE COMMENT "NO = we decided not to accept the charge; it should be voided", |
| − | `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", | `WhenVoided` DATETIME DEFAULT NULL COMMENT "when the charge was voided", | ||
| − | `Notes` VARCHAR(255) COMMENT " | + | `Notes` VARCHAR(255) COMMENT "manually-entered notes on this charge", |
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) | ) | ||
ENGINE = MYISAM;</mysql> | ENGINE = MYISAM;</mysql> | ||
Latest revision as of 21:46, 1 September 2013
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>