VbzCart/tables/cust charges - Revision history

Woozle: main doc for CardSafe is now in cust_cards

2013-09-01T21:46:21Z

main doc for CardSafe is now in cust_cards

← Older revision		Revision as of 21:46, 1 September 2013
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''': ~~human-readable (and searchable) summary of "safe" card data: T-''nnnn'' x''nnnn'' p''postal_code''~~		** '''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.

Woozle: /* SQL */ missing comma

2013-09-01T21:42:18Z

SQL: missing comma

← Older revision		Revision as of 21:42, 1 September 2013
Line 31:		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"		`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format",
	`WhenEntered` DATETIME COMMENT "when charge was entered",		`WhenEntered` DATETIME COMMENT "when charge was entered",
	`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",		`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",

Woozle: simplifying

2013-09-01T20:48:26Z

simplifying

← Older revision		Revision as of 20:48, 1 September 2013
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''': human-readable (and searchable) summary of "safe" card data: T-''nnnn'' x''nnnn'' p''postal_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.		** '''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 15:		Line 16:
	*** 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-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 26:		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",
	~~`CardNumExp` VARCHAR(31) COMMENT "card number and expiration as used for this charge",~~		`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format"
	~~`CardBillAddr` VARCHAR(255) COMMENT "card billing address as used for this charge",~~
	~~`CardCrypt` VARCHAR(255) DEFAULT NULL COMMENT "encrypted CardNumExp + CardBillAddr (use TAB as separator)",~~
	`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info~~: last 4 digits, exp, zipcode..."~~
	~~`Encrypted` VARCHAR(127) COMMENT "DEPRECATED: encrypted CardNumExp~~",
	`WhenEntered` DATETIME COMMENT "when charge was entered",		`WhenEntered` DATETIME COMMENT "when charge was entered",
	`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",		`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",
Line 42:		Line 43:
	`isAccepted` BOOL DEFAULT FALSE COMMENT "NO = we decided not to accept the charge; it should be voided",		`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 "~~human~~-entered notes on this charge",		`Notes` VARCHAR(255) COMMENT "manually-entered notes on this charge",
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>

Woozle: new fields to handle encryption properly

2013-08-23T17:53:26Z

new fields to handle encryption properly

← Older revision		Revision as of 17:53, 23 August 2013
Line 14:		Line 14:
	** '''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
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cust_charges` (		<mysql>CREATE TABLE `cust_charges` (
Line 25:		Line 26:
	`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",
	`CardNumExp` VARCHAR(31) COMMENT "card number and expiration as used for this charge",		`CardNumExp` VARCHAR(31) COMMENT "card number and expiration as used for this charge",
	`CardBillAddr` VARCHAR(255) COMMENT "card billing address as used for this charge",		`CardBillAddr` VARCHAR(255) COMMENT "card billing address as used for this charge",
	`Encrypted` VARCHAR(127) COMMENT "encrypted CardNumExp",		`CardCrypt` VARCHAR(255) DEFAULT NULL COMMENT "encrypted CardNumExp + CardBillAddr (use TAB as separator)",
	`WhenEntered` DATETIME COMMENT "when charge was entered",		`CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info: last 4 digits, exp, zipcode..."
	`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",		`Encrypted` VARCHAR(127) COMMENT "DEPRECATED: encrypted CardNumExp",
	`WhenHeldUp` DATETIME COMMENT "when this charge was suspended (do not process)",		`WhenEntered` DATETIME COMMENT "when charge was entered",
	`isSuccess` BOOL DEFAULT FALSE COMMENT "did the charge go through?",		`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",
	`isTest` BOOL DEFAULT FALSE COMMENT "YES = this is a test charge, to be voided if successful",		`WhenHeldUp` DATETIME COMMENT "when this charge was suspended (do not process)",
	`Confirmation` VARCHAR(31) DEFAULT NULL COMMENT "confirmation code returned by card service",		`isSuccess` BOOL DEFAULT FALSE COMMENT "did the charge go through?",
	`AVSRespCode` VARCHAR(1) DEFAULT NULL COMMENT "Address Verification System response code",		`isTest` BOOL DEFAULT FALSE COMMENT "YES = this is a test charge, to be voided if successful",
	`AVSAuthChar` VARCHAR(1) DEFAULT NULL COMMENT "'Auth. Char. Ind.' in PTC",		`Confirmation` VARCHAR(31) DEFAULT NULL COMMENT "confirmation code returned by card service",
	`WhenDecided` DATETIME DEFAULT NULL COMMENT "when we decided whether or not to accept this charge",		`AVSRespCode` VARCHAR(1) DEFAULT NULL COMMENT "Address Verification System response code",
	`isAccepted` BOOL DEFAULT FALSE COMMENT "NO = we decided not to accept the charge; it should be voided",		`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",		`WhenVoided` DATETIME DEFAULT NULL COMMENT "when the charge was voided",
	`Notes` VARCHAR(255) COMMENT "human-entered notes on this charge",		`Notes` VARCHAR(255) COMMENT "human-entered notes on this charge",

Woozle: encryption

2013-08-16T01:12:52Z

encryption

← Older revision		Revision as of 01:12, 16 August 2013
Line 1:		Line 1:
	==About==		==About==
			* '''Purpose''': Charges to customer bank cards
	* '''Notes''':		* '''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.		** 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.
Line 11:		Line 12:
	* '''History''':		* '''History''':
	** '''2009-07-09''' moved to separate wiki page; no design changes		** '''2009-07-09''' moved to separate wiki page; no design changes
			** '''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
	==SQL==		==SQL==
	~~Charges to customer bank cards~~		<mysql>CREATE TABLE `cust_charges` (
	~~<section begin=sql />~~<mysql>CREATE TABLE `cust_charges` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`ID_Batch` INT DEFAULT NULL COMMENT "batch within which this payment was processed",		`ID_Batch` INT DEFAULT NULL COMMENT "batch within which this payment was processed",
Line 25:		Line 27:
	`CardNumExp` VARCHAR(31) COMMENT "card number and expiration as used for this charge",		`CardNumExp` VARCHAR(31) COMMENT "card number and expiration as used for this charge",
	`CardBillAddr` VARCHAR(255) COMMENT "card billing address as used for this charge",		`CardBillAddr` VARCHAR(255) COMMENT "card billing address as used for this charge",
			`Encrypted` VARCHAR(127) COMMENT "encrypted CardNumExp",
	`WhenEntered` DATETIME COMMENT "when charge was entered",		`WhenEntered` DATETIME COMMENT "when charge was entered",
	`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",		`WhenXmitted` DATETIME COMMENT "when processed via credit card service provider",
Line 40:		Line 43:
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>
	~~<section end=sql />~~

Woozle: how to filter for charges needing processing

2009-10-13T23:34:37Z

how to filter for charges needing processing

← Older revision		Revision as of 23:34, 13 October 2009
Line 8:		Line 8:
	** '''WhenDecided''' and '''isAccepted''' was '''WhenAuthorized''' and '''WhenRefused''' in Access		** '''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.		** '''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''':		* '''History''':
	** '''2009-07-09''' moved to separate wiki page; no design changes		** '''2009-07-09''' moved to separate wiki page; no design changes

Woozle: moved from "customer tables" page

2009-07-09T19:36:41Z

moved from "customer tables" page

New page

==About==
* '''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''':
** '''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.
* '''History''':
** '''2009-07-09''' moved to separate wiki page; no design changes
==SQL==
Charges to customer bank cards
<section begin=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",
`CardNumExp` VARCHAR(31) COMMENT "card number and expiration as used for this charge",
`CardBillAddr` VARCHAR(255) COMMENT "card billing address as used for this charge",
`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 "human-entered notes on this charge",
PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>
<section end=sql />