VbzCart/tables/orders - Revision history

Woozle at 00:37, 3 July 2019

2019-07-03T00:37:29Z

← Older revision		Revision as of 00:37, 3 July 2019
Line 61:		Line 61:
	** '''WhenUpdated_old''': this was originally supposed to be set if the order was changed before being imported, but it doesn't seem to have been used since 2001-02-09. This information would be better stored in a log file anyhow.		** '''WhenUpdated_old''': this was originally supposed to be set if the order was changed before being imported, but it doesn't seem to have been used since 2001-02-09. This information would be better stored in a log file anyhow.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `orders` (		<source lang=mysql>CREATE TABLE `orders` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`Number` VARCHAR(63) NOT NULL COMMENT "order number for human reference; generated by web site",		`Number` VARCHAR(63) NOT NULL COMMENT "order number for human reference; generated by web site",
Line 89:		Line 89:
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = INNODB;</~~mysql~~>		ENGINE = INNODB;</source>
	===Legacy data===		===Legacy data===
	This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.		This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.
	<mysql>CREATE TABLE `core_orders_legacy` (		<source lang=mysql>CREATE TABLE `core_orders_legacy` (
	`ID_Order` INT NOT NULL,		`ID_Order` INT NOT NULL,
	`WhenClosed_old` DATETIME DEFAULT NULL COMMENT "possibly indicated that import was complete?",		`WhenClosed_old` DATETIME DEFAULT NULL COMMENT "possibly indicated that import was complete?",
Line 107:		Line 107:
	FROM core_orders		FROM core_orders
	WHERE (WhenClosed_old IS NOT NULL) OR (WhenUpdated_old IS NOT NULL);		WHERE (WhenClosed_old IS NOT NULL) OR (WhenUpdated_old IS NOT NULL);
	</~~mysql~~>		</source>

Woozle: WhenHeld

2017-06-03T17:33:49Z

WhenHeld

@@ Line 5: / Line 5: @@
 * '''WhenRevised''' is when the order was last modified ''by the customer''.
 * '''WhenEdited''' is when the order record was last modified ''by anyone'' (auto-update)
 ===Future===
 * The fact that this only records cust_name.ID for buyer and seller, rather than core_cust.ID, makes the code more complicated in certain ways, and may be something to think about when we're remediating this table:
@@ Line 42: / Line 43: @@
 *** Got rid of obsolete '''PassCode''' field.
 *** Renamed '''WhenEdited''' to '''WhenRevised''', and adding a new '''WhenEdited''' that means something slightly different.
 * '''2016-06-23''' Rewriting cart-to-order import, so fixing some timestamps:
 ** '''WhenPorted''' is when the Cart was converted to an Order
 *** (renamed WhenStarted to WhenPorted, because this used to be the definition of WhenStarted)
 ** '''WhenCarted''' is when the ''cart'' was created (i.e. copy Cart.WhenCreated)
 ==Notes==
 * Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
@@ Line 82: / Line 85: @@
    `WhenEdited`          TIMESTAMP NULL ON UPDATE NOW() COMMENT "when order record was last modified by anyone",
    `WhenNeeded`          DATETIME  DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",
    `WhenClosed`          DATETIME  DEFAULT NULL COMMENT "when order was completed (all items shipped, cancelled, or unavailable)",
    PRIMARY KEY(`ID`)
+)
 ENGINE = INNODB;</mysql>
 ===Legacy data===
 This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.

Woozle: WhenStarted->WhenCarted; WhenCreated

2016-06-23T23:50:37Z

WhenStarted->WhenCarted; WhenCreated

← Older revision		Revision as of 23:50, 23 June 2016
Line 45:		Line 45:
	** '''WhenPorted''' is when the Cart was converted to an Order		** '''WhenPorted''' is when the Cart was converted to an Order
	*** (renamed WhenStarted to WhenPorted, because this used to be the definition of WhenStarted)		*** (renamed WhenStarted to WhenPorted, because this used to be the definition of WhenStarted)
	** '''~~WhenStarted~~''' is ~~now~~ when the ''cart'' was created (i.e. copy Cart.WhenCreated)		** '''WhenCarted''' is when the ''cart'' was created (i.e. copy Cart.WhenCreated)
	*** (created as a new field after renaming existing WhenStarted)
	==Notes==		==Notes==
	* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''		* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
Line 76:		Line 75:
	`WebTotal_Ship` DECIMAL(9,2) DEFAULT NULL COMMENT "total quoted for additional charges (S/H, tax)",		`WebTotal_Ship` DECIMAL(9,2) DEFAULT NULL COMMENT "total quoted for additional charges (S/H, tax)",
	`WebTotal_Final` DECIMAL(9,2) DEFAULT NULL COMMENT "final total charge quoted",		`WebTotal_Final` DECIMAL(9,2) DEFAULT NULL COMMENT "final total charge quoted",
	`~~WhenStarted~~` DATETIME DEFAULT NULL COMMENT "when corresponding cart record was created",		`WhenCreated` DATETIME NOT NULL NULL COMMENT "when the order record was first created (blank)",
	`WhenPorted` DATETIME DEFAULT NULL COMMENT "when cart record was converted to an order",		`WhenCarted` DATETIME DEFAULT NULL COMMENT "when corresponding cart record was created",
	`WhenPlaced` DATETIME DEFAULT NULL COMMENT "when customer officially placed the order",		`WhenPorted` DATETIME DEFAULT NULL COMMENT "when cart record was converted to an order",
	`WhenRevised` DATETIME DEFAULT NULL COMMENT "when order was last edited by the customer",		`WhenPlaced` DATETIME DEFAULT NULL COMMENT "when customer officially placed the order",
			`WhenRevised` DATETIME DEFAULT NULL COMMENT "when order was last edited by the customer",
	`WhenEdited` TIMESTAMP NULL ON UPDATE NOW() COMMENT "when order record was last modified by anyone",		`WhenEdited` TIMESTAMP NULL ON UPDATE NOW() COMMENT "when order record was last modified by anyone",
	`WhenNeeded` DATETIME DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",		`WhenNeeded` DATETIME DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",
	`WhenClosed` DATETIME DEFAULT NULL COMMENT "when order was completed -- all items shipped, cancelled, or unavailable",		`WhenClosed` DATETIME DEFAULT NULL COMMENT "when order was completed (all items shipped, cancelled, or unavailable)",
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)

Woozle: WhenPorted

2016-06-23T23:10:28Z

WhenPorted

← Older revision		Revision as of 23:10, 23 June 2016
Line 6:		Line 6:
	* '''WhenEdited''' is when the order record was last modified ''by anyone'' (auto-update)		* '''WhenEdited''' is when the order record was last modified ''by anyone'' (auto-update)
	===Future===		===Future===
	* Get rid of PassCode. It was originally supposed to be a way to let customers log in to manage their orders, but it was never used and doesn't seem like the way to do things.
	* The fact that this only records cust_name.ID for buyer and seller, rather than core_cust.ID, makes the code more complicated in certain ways, and may be something to think about when we're remediating this table:		* The fact that this only records cust_name.ID for buyer and seller, rather than core_cust.ID, makes the code more complicated in certain ways, and may be something to think about when we're remediating this table:
	** pulling up a list of orders for a given customer		** pulling up a list of orders for a given customer
Line 40:		Line 39:
	** This used to be the timestamp for when the order was converted from cart data to order data, but we're doing that automatically (when the customer confirms the order) now and the field is no longer used. We do need a timestamp to indicate that an order has been placed (so don't re-use the order record -- at least, not without asking a human), so that's what this field is going to be now. The meaning is similar enough that it seemed reasonable to use the same field.		** This used to be the timestamp for when the order was converted from cart data to order data, but we're doing that automatically (when the customer confirms the order) now and the field is no longer used. We do need a timestamp to indicate that an order has been placed (so don't re-use the order record -- at least, not without asking a human), so that's what this field is going to be now. The meaning is similar enough that it seemed reasonable to use the same field.
	* '''2016-03-06'''		* '''2016-03-06'''
	** Migrating data from MYISAM engine to INNODB. In the process, ~~let's clean~~ some things up:		** Migrating data from MYISAM engine to INNODB. In the process, cleaned some things up:
	** ~~Get~~ rid of obsolete '''PassCode''' field.		*** Got rid of obsolete '''PassCode''' field.
	** ~~Rename~~ '''WhenEdited''' to '''WhenRevised''', and adding a new '''WhenEdited''' that means something slightly different.		*** Renamed '''WhenEdited''' to '''WhenRevised''', and adding a new '''WhenEdited''' that means something slightly different.
			* '''2016-06-23''' Rewriting cart-to-order import, so fixing some timestamps:
			** '''WhenPorted''' is when the Cart was converted to an Order
			*** (renamed WhenStarted to WhenPorted, because this used to be the definition of WhenStarted)
			** '''WhenStarted''' is now when the ''cart'' was created (i.e. copy Cart.WhenCreated)
			*** (created as a new field after renaming existing WhenStarted)
	==Notes==		==Notes==
	* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''		* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
Line 72:		Line 76:
	`WebTotal_Ship` DECIMAL(9,2) DEFAULT NULL COMMENT "total quoted for additional charges (S/H, tax)",		`WebTotal_Ship` DECIMAL(9,2) DEFAULT NULL COMMENT "total quoted for additional charges (S/H, tax)",
	`WebTotal_Final` DECIMAL(9,2) DEFAULT NULL COMMENT "final total charge quoted",		`WebTotal_Final` DECIMAL(9,2) DEFAULT NULL COMMENT "final total charge quoted",
	`WhenStarted` DATETIME DEFAULT NULL COMMENT "when ~~order~~ record was created",		`WhenStarted` DATETIME DEFAULT NULL COMMENT "when corresponding cart record was created",
			`WhenPorted` DATETIME DEFAULT NULL COMMENT "when cart record was converted to an order",
	`WhenPlaced` DATETIME DEFAULT NULL COMMENT "when customer officially placed the order",		`WhenPlaced` DATETIME DEFAULT NULL COMMENT "when customer officially placed the order",
	`WhenRevised` DATETIME DEFAULT NULL COMMENT "when order was last edited by the customer",		`WhenRevised` DATETIME DEFAULT NULL COMMENT "when order was last edited by the customer",

Woozle: /* Migration */ ...actually could have been done much more easily.

2016-03-07T21:28:08Z

Migration: ...actually could have been done much more easily.

← Older revision		Revision as of 21:28, 7 March 2016
Line 128:		Line 128:
	`WhenNeeded`,		`WhenNeeded`,
	`WhenClosed`		`WhenClosed`
	FROM orders</mysql>Note that "WhenEdited" was copied to "WhenRevised" in the new table, not to "WhenEdited".		FROM orders</mysql>Note that "WhenEdited" was copied to "WhenRevised" in the new table, not to "WhenEdited". (2016-03-07 Update: it turns out that this can be done with a single SQL command, which MySQL Workbench can facilitate via some controls that are normally hidden in a drop-down area. Now I know...)

	===Legacy data===		===Legacy data===
	This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.		This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.

Woozle: revisions & migration

2016-03-06T20:05:31Z

revisions & migration

Show changes

Woozle: WhenPrepped -> WhenPlaced

2015-09-04T16:34:30Z

WhenPrepped -> WhenPlaced

← Older revision		Revision as of 16:34, 4 September 2015
Line 1:		Line 1:
	==About==		==About==
	* '''Fields''':		* '''Fields''':
			** '''WhenPlaced''' indicates when order the order was officially submitted by the customer. If this isn't NULL, then the customer should receive a new order record if they checkout again within the same session (or the code should ask if they're updating an existing order; it should never assume they're doing that).
	** '''WhenClosed''' is set automatically when the order-processing code notices that all items ordered have been dealt with. If an order needs to be closed manually (either because the code for automatic closure did not work or because it had not yet been written), then the order should be Pulled with a pull-type of "Completed". (Should this field be renamed to "WhenComplete"? Or should this field be set whenever an order is Pulled, and cleared whenever the active Pull is cleared? In the latter scenario, need to do something about the orders where this field is set but there is no corresponding Pull.)		** '''WhenClosed''' is set automatically when the order-processing code notices that all items ordered have been dealt with. If an order needs to be closed manually (either because the code for automatic closure did not work or because it had not yet been written), then the order should be Pulled with a pull-type of "Completed". (Should this field be renamed to "WhenComplete"? Or should this field be set whenever an order is Pulled, and cleared whenever the active Pull is cleared? In the latter scenario, need to do something about the orders where this field is set but there is no corresponding Pull.)
	* '''Future''':		* '''Future''':
Line 34:		Line 35:
	** renamed '''ID_ChargeCard''' -> '''ID_BuyerCard'''		** renamed '''ID_ChargeCard''' -> '''ID_BuyerCard'''
	** added '''ID_RecipAddr'''		** added '''ID_RecipAddr'''
			* '''2015-09-04''' Changing '''WhenPrepped''' to '''WhenPlaced'''
			** This used to be the timestamp for when the order was converted from cart data to order data, but we're doing that automatically (when the customer confirms the order) now and the field is no longer used. We do need a timestamp to indicate that an order has been placed (so don't re-use the order record -- at least, not without asking a human), so that's what this field is going to be now. The meaning is similar enough that it seemed reasonable to use the same field.
	==Notes==		==Notes==
	* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''		* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
Line 65:		Line 68:
	`WebTotal_Final` DECIMAL(9,2) DEFAULT NULL COMMENT "final total charge quoted",		`WebTotal_Final` DECIMAL(9,2) DEFAULT NULL COMMENT "final total charge quoted",
	`WhenStarted` DATETIME DEFAULT NULL COMMENT "when order record was created",		`WhenStarted` DATETIME DEFAULT NULL COMMENT "when order record was created",
	`~~WhenPrepped~~` DATETIME DEFAULT NULL COMMENT "when order ~~record was completed (import complete), ready to handle~~",		`WhenPlaced` DATETIME DEFAULT NULL COMMENT "when customer officially placed the order",
	`WhenEdited` DATETIME DEFAULT NULL COMMENT "when order was last edited in-store",		`WhenEdited` DATETIME DEFAULT NULL COMMENT "when order was last edited in-store",
	`WhenNeeded` DATETIME DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",		`WhenNeeded` DATETIME DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",

Woozle: modifications to accommodate pre-existing user data

2014-09-15T01:28:57Z

modifications to accommodate pre-existing user data

← Older revision		Revision as of 01:28, 15 September 2014
Line 31:		Line 31:
	*** adding '''BuyerName''', '''RecipName''', '''RecipAddr'''		*** adding '''BuyerName''', '''RecipName''', '''RecipAddr'''
	*** removing '''ID_NameBuyer'''. '''ID_NameRecip''', '''ID_ContactAddrRecip'''		*** removing '''ID_NameBuyer'''. '''ID_NameRecip''', '''ID_ContactAddrRecip'''
			* '''2014-09-14''' Changes to accommodate user selection of pre-existing data:
			** renamed '''ID_ChargeCard''' -> '''ID_BuyerCard'''
			** added '''ID_RecipAddr'''
	==Notes==		==Notes==
	* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''		* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
Line 56:		Line 59:
	`RecipAddr` VARCHAR(255) DEFAULT NULL COMMENT "shipping address, as used/formatted",		`RecipAddr` VARCHAR(255) DEFAULT NULL COMMENT "shipping address, as used/formatted",
	`PayType` INT DEFAULT NULL COMMENT "aux_pay_types.code -- payment method used for this order",		`PayType` INT DEFAULT NULL COMMENT "aux_pay_types.code -- payment method used for this order",
	`~~ID_ChargeCard~~` INT DEFAULT NULL COMMENT "cust_cards.ID -- bank card which should be debited for this order",		`ID_BuyerCard` INT DEFAULT NULL COMMENT "cust_cards.ID -- bank card which should be debited for this order",
			`ID_RecipAddr` INT DEFAULT NULL COMMENT "cust_addrs.ID -- ship-to address",
	`WebTotal_Merch` DECIMAL(9,2) DEFAULT NULL COMMENT "total amount quoted for merchandise",		`WebTotal_Merch` DECIMAL(9,2) DEFAULT NULL COMMENT "total amount quoted for merchandise",
	`WebTotal_Ship` DECIMAL(9,2) DEFAULT NULL COMMENT "total quoted for additional charges (S/H, tax)",		`WebTotal_Ship` DECIMAL(9,2) DEFAULT NULL COMMENT "total quoted for additional charges (S/H, tax)",
Line 68:		Line 72:
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>
	~~<section end=sql />~~
	==Legacy SQL==		==Legacy SQL==
	This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.		This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.

Woozle: removing 3 fields no longer needed

2013-11-07T13:02:20Z

removing 3 fields no longer needed

← Older revision		Revision as of 13:02, 7 November 2013
Line 8:		Line 8:
	*** providing links from orders to the corresponding customer records		*** providing links from orders to the corresponding customer records
	*** looking up which credit cards are appropriate choices for an order		*** looking up which credit cards are appropriate choices for an order
	** Some fields to rename (wait until after final migration of current order data):		** <s>Some fields to rename (wait until after final migration of current order data):</s> These fields have now been removed:
	*** ID_NameBuyer -> ID_BuyerName		*** <s>ID_NameBuyer -> ID_BuyerName</s>
	*** ID_NameRecip -> ID_RecipName		*** <s>ID_NameRecip -> ID_RecipName</s>
	*** ID_ContactAddrRecip -> ID_RecipAddr		*** <s>ID_ContactAddrRecip -> ID_RecipAddr</s>
	*** <s>ID_ContactPhone -> ID_RecipPhone</s>		*** <s>ID_ContactPhone -> ID_RecipPhone</s>
	*** <s>ID_ContactEmail -> ID_RecipEmail</s>		*** <s>ID_ContactEmail -> ID_RecipEmail</s>
Line 30:		Line 30:
	** going to start recording shipping details-as-used on a per-order basis		** going to start recording shipping details-as-used on a per-order basis
	*** adding '''BuyerName''', '''RecipName''', '''RecipAddr'''		*** adding '''BuyerName''', '''RecipName''', '''RecipAddr'''
			*** removing '''ID_NameBuyer'''. '''ID_NameRecip''', '''ID_ContactAddrRecip'''
	==Notes==		==Notes==
	* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''		* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''

Woozle: Woozle moved page VbzCart/tables/core orders to VbzCart/tables/orders: adding "core_" means that core tables sort to a different place than the other tables which depend on them, which is kind of annoying

2013-11-07T11:52:21Z

Woozle moved page VbzCart/tables/core orders to VbzCart/tables/orders: adding "core_" means that core tables sort to a different place than the other tables which depend on them, which is kind of annoying

← Older revision	Revision as of 11:52, 7 November 2013
(No difference)