VbzCart/tables/orders

About

• 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.)
• 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:
    • 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 core_custs; 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

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

<mysql>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",
 `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_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_ChargeCard`       INT DEFAULT NULL COMMENT "cust_cards.ID -- bank card which should be debited for this order",
 `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",
 `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",
 `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)",
 `WhenClosed`          DATETIME DEFAULT NULL COMMENT "when order was completed -- all items shipped, cancelled, or unavailable",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>

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. <mysql>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); </mysql>