VbzCart/docs/tables/orders: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
(significant revision in progress)
m (Woozle moved page VbzCart/VbzCart/tables/orders to VbzCart/docs/tables/orders without leaving a redirect: part 4/5)
 
(10 intermediate revisions by one other user not shown)
Line 1: Line 1:
==About==
==About==
* '''Fields''':
===Fields===
** '''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.)
* '''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).
* '''Future''':
* '''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.)
** 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.
* '''WhenRevised''' is when the order was last modified ''by the customer''.
** 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:
* '''WhenEdited''' is when the order record was last modified ''by anyone'' (auto-update)
*** pulling up a list of orders for a given customer
* '''WhenHeld''' is set as long as there's an active hold on the order; if NULL, then none are set
*** providing links from orders to the corresponding customer records
===Future===
*** looking up which credit cards are appropriate choices for an order
* 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:
** Some fields to rename (wait until after final migration of current order data):
** pulling up a list of orders for a given customer
*** ID_NameBuyer -> ID_BuyerName
** providing links from orders to the corresponding customer records
*** ID_NameRecip -> ID_RecipName
** looking up which credit cards are appropriate choices for an order
*** ID_ContactAddrRecip -> ID_RecipAddr
* <s>Some fields to rename (wait until after final migration of current order data):</s> These fields have now been removed:
*** <s>ID_ContactPhone -> ID_RecipPhone</s>
** <s>ID_NameBuyer -> ID_BuyerName</s>
*** <s>ID_ContactEmail -> ID_RecipEmail</s>
** <s>ID_NameRecip -> ID_RecipName</s>
** <s>We should probably add email and phone fields for the buyer too, since they might conceivably want to be able to receive order confirmations for different orders at different email addresses... ask Board of Advisors about this.</s> See history, 2009-10-29; also see note about using core_custs.ID instead of cust_name.ID. 2009-11-28: decided to eventually replace ID_NameBuyer and ID_NameRecip with ID_Buyer and ID_Recip -- see History.
** <s>ID_ContactAddrRecip -> ID_RecipAddr</s>
** <s>ID_ContactPhone -> ID_RecipPhone</s>
** <s>ID_ContactEmail -> ID_RecipEmail</s>
* <s>We should probably add email and phone fields for the buyer too, since they might conceivably want to be able to receive order confirmations for different orders at different email addresses... ask Board of Advisors about this.</s> See history, 2009-10-29; also see note about using core_custs.ID instead of cust_name.ID. 2009-11-28: decided to eventually replace ID_NameBuyer and ID_NameRecip with ID_Buyer and ID_Recip -- see History.
==History==
==History==
* '''2008-11-20''' Changing field names:
* '''2008-11-20''' Changing field names:
Line 30: Line 33:
** 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'''
* '''2014-09-14''' Changes to accommodate user selection of pre-existing data:
** renamed '''ID_ChargeCard''' -> '''ID_BuyerCard'''
** 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.
* '''2016-03-06'''
** Migrating data from MYISAM engine to INNODB. In the process, cleaned some things up:
*** Got rid of obsolete '''PassCode''' field.
*** Renamed '''WhenEdited''' to '''WhenRevised''', and adding a new '''WhenEdited''' that means something slightly different.
*** See {{l/sub|migration}} for more.
* '''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)
* '''2017-06-03''' replacing '''ID_Pull''' with '''WhenHeld''' (order-pull system substantially reworked)
==Notes==
==Notes==
* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
Line 42: 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",
   `SortPfx`        VARCHAR(7)  DEFAULT NULL COMMENT "sorting prefix -- earlier prefix schemes sort wrong, and this overrides that",
   `SortPfx`        VARCHAR(7)  DEFAULT NULL COMMENT "sorting prefix -- earlier prefix schemes sort wrong, and this overrides that",
  `PassCode`      VARCHAR(15)  DEFAULT NULL COMMENT "order's security passcode - useful if user has not created an account",
   `ID_Cart`                INT DEFAULT NULL COMMENT "ID of cart used to create this order",
   `ID_Cart`                INT DEFAULT NULL COMMENT "ID of cart used to create this order",
   `ID_Pull`                INT DEFAULT NULL COMMENT "ord_pulls.ID: ID of the currently active Pull for this order (NULL = order is active)",
   `ID_Pull`                INT DEFAULT NULL COMMENT "ord_pulls.ID: ID of the currently active Pull for this order (NULL = order is active)",
Line 55: Line 73:
   `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)",
   `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",
   `WhenCreated`        DATETIME NOT NULL NULL COMMENT "when the order record was first created (blank)",
   `WhenPrepped`         DATETIME DEFAULT NULL COMMENT "when order record was completed (import complete), ready to handle",
   `WhenCarted`         DATETIME DEFAULT NULL COMMENT "when corresponding cart record was created",
   `WhenEdited`          DATETIME DEFAULT NULL COMMENT "when order was last edited in-store",
  `WhenPorted`          DATETIME  DEFAULT NULL COMMENT "when cart record was converted to an order",
   `WhenNeeded`          DATETIME DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",
   `WhenPlaced`          DATETIME DEFAULT NULL COMMENT "when customer officially placed the order",
   `WhenClosed`          DATETIME DEFAULT NULL COMMENT "when order was completed -- all items shipped, cancelled, or unavailable",
  `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",
   `WhenNeeded`          DATETIME DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",
  `WhenHeld`            DATETIME  DEFAULT NULL COMMENT "most recent timestamp of an order hold (NULL = none active)",
   `WhenClosed`          DATETIME DEFAULT NULL COMMENT "when order was completed (all items shipped, cancelled, or unavailable)",
   PRIMARY KEY(`ID`)
   PRIMARY KEY(`ID`)
)
)
ENGINE = MYISAM;</mysql>
ENGINE = INNODB;</source>
<section end=sql />
===Legacy data===
==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.
<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 85: 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>

Latest revision as of 01:57, 25 February 2024

About

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.)
  • WhenRevised is when the order was last modified by the customer.
  • WhenEdited is when the order record was last modified by anyone (auto-update)
  • WhenHeld is set as long as there's an active hold on the order; if NULL, then none are set

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:
    • pulling up a list of orders for a given customer
    • providing links from orders to the corresponding customer records
    • looking up which credit cards are appropriate choices for an order
  • Some fields to rename (wait until after final migration of current order data): These fields have now been removed:
    • ID_NameBuyer -> ID_BuyerName
    • ID_NameRecip -> ID_RecipName
    • ID_ContactAddrRecip -> ID_RecipAddr
    • ID_ContactPhone -> ID_RecipPhone
    • ID_ContactEmail -> ID_RecipEmail
  • We should probably add email and phone fields for the buyer too, since they might conceivably want to be able to receive order confirmations for different orders at different email addresses... ask Board of Advisors about this. See history, 2009-10-29; also see note about using core_custs.ID instead of cust_name.ID. 2009-11-28: decided to eventually replace ID_NameBuyer and ID_NameRecip with ID_Buyer and ID_Recip -- see History.

History

  • 2008-11-20 Changing field names:
    • WhenOpened -> WhenImported (this won't be set once order data is kept live)
    • WhenClosed -> WhenClosed_old (we want "WhenClosed" to be a timestamp for when the order was finished, and we don't know what this legacy field was actually for)
    • WhenUpdated -> WhenUpdated_old (no longer being used)
  • 2009-10-04 WhenClosed_old and WhenUpdated_old fields removed (from Rizzo version of db); data moved to core_orders_legacy -- now we can finally upload the latest data to the main server
  • 2009-10-19 added ID_Cart field
  • 2009-10-22
    • renamed WhenImported to WhenPrepped, changed definition slightly
  • 2009-10-29 We never used the ID_ContactPhone and ID_ContactEmail fields, so they are being removed until we find is some compelling design reason to start using them. Board of advisors says different phone/email for different orders doesn't really make sense; should always use whatever phone/email is current.
  • 2009-11-28 added ID_Buyer and ID_Recip to point directly to Template:Vbzcart; this will eventually replace ID_NameBuyer and ID_NameRecip.
  • 2013-11-07 significant revision now in progress
    • renamed from core_orders to orders
    • going to start recording shipping details-as-used on a per-order basis
      • adding BuyerName, RecipName, RecipAddr
      • 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
  • 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.
  • 2016-03-06
    • Migrating data from MYISAM engine to INNODB. In the process, cleaned some things up:
      • Got rid of obsolete PassCode field.
      • Renamed WhenEdited to WhenRevised, and adding a new WhenEdited that means something slightly different.
      • See migration for more.
  • 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)
  • 2017-06-03 replacing ID_Pull with WhenHeld (order-pull system substantially reworked)

Notes

  • Normal sorting order is: CONCAT(IFNULL(SortPfx,''),Number)
    • The fields have to be concatenated, else the non-NULL SortPfx records always get sorted after the rest. "IFNULL(SortPfx,Number),Number" also works; not sure which is computationally cheaper.
  • The customer contact information schema needs to be significantly reworked:
    • Orders should save a string of the actual name used
    • There should be some kind of "customer" record which contains the current defaults, as well as alternatives, for names & contact info
  • There should eventually be an event log for orders so a complete activity history can be pulled up, which may render some of the When* fields obsolete.
  • If not dealing with legacy data, WhenStarted should be NOT NULL.
  • As implemented for vbz, there were originally some additional fields for storing legacy data; these were eventually discarded when the data didn't seem to be relevant:
    • WhenClosed_old: it's not clear what this field actually did
    • 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

CREATE TABLE `orders` (
  `ID`             INT NOT NULL AUTO_INCREMENT,
  `Number`         VARCHAR(63)      NOT NULL COMMENT "order number for human reference; generated by web site",
  `SortPfx`        VARCHAR(7)   DEFAULT NULL COMMENT "sorting prefix -- earlier prefix schemes sort wrong, and this overrides that",
  `ID_Cart`                 INT DEFAULT NULL COMMENT "ID of cart used to create this order",
  `ID_Pull`                 INT DEFAULT NULL COMMENT "ord_pulls.ID: ID of the currently active Pull for this order (NULL = order is active)",
  `ID_Buyer`                INT DEFAULT NULL COMMENT "core_custs.ID -- purchaser, contact for discussing order",
  `ID_Recip`                INT DEFAULT NULL COMMENT "core_custs.ID -- shipping destination",
  `BuyerName`      VARCHAR(127) DEFAULT NULL COMMENT "name of purchaser, contact for discussing order",
  `RecipName`      VARCHAR(127) DEFAULT NULL COMMENT "name of recipient, for shipping address",
  `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",
  `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_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",
  `WhenCreated`         DATETIME NOT NULL NULL COMMENT "when the order record was first created (blank)",
  `WhenCarted`          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",
  `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",
  `WhenNeeded`          DATETIME  DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",
  `WhenHeld`            DATETIME  DEFAULT NULL COMMENT "most recent timestamp of an order hold (NULL = none active)",
  `WhenClosed`          DATETIME  DEFAULT NULL COMMENT "when order was completed (all items shipped, cancelled, or unavailable)",
  PRIMARY KEY(`ID`)
)
ENGINE = INNODB;

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.

CREATE TABLE `core_orders_legacy` (
  `ID_Order`        INT NOT NULL,
  `WhenClosed_old`  DATETIME DEFAULT NULL COMMENT "possibly indicated that import was complete?",
  `WhenUpdated_old` DATETIME DEFAULT NULL COMMENT "was only set in 8 records; maybe indicates admin revision during import?",
  PRIMARY KEY(`ID_Order`)
)
ENGINE = MYISAM;

INSERT INTO `core_orders_legacy` (ID_Order, WhenClosed_old, WhenUpdated_old)
SELECT
  ID,
  WhenClosed_old,
  WhenUpdated_old
FROM core_orders
WHERE (WhenClosed_old IS NOT NULL) OR (WhenUpdated_old IS NOT NULL);