VbzCart/docs/tables/orders

About

• 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)
• 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 are actually some additional fields for storing legacy data until we figure out what to do with it:
    • 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

<section begin=sql /><mysql>CREATE TABLE `core_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_Pull`        INT DEFAULT NULL COMMENT "ord_pulls.ID: ID of the currently active Pull for this order (NULL = order is active)",
 `ID_NameBuyer`   INT DEFAULT NULL COMMENT "cust_names.ID -- purchaser, contact for discussing order",
 `ID_NameRecip`   INT DEFAULT NULL COMMENT "cust_names.ID -- shipping destination",
 `ID_ContactAddrRecip` INT DEFAULT NULL COMMENT "cust_addrs.ID -- shipping address to use (NULL = recipient's default address)",
 `ID_ContactPhone`     INT DEFAULT NULL COMMENT "cust_phones.ID -- phone number for this order",
 `ID_ContactEmail`     INT DEFAULT NULL COMMENT "cust_emails.ID -- email address 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 used to pay 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",
 `WhenImported`        DATETIME DEFAULT NULL COMMENT "when entered (downloaded) to database (or entered manually) and ready for processing",
 `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> <section end=sql />