VbzCart/tables/orders

About

• Future:
  • Some fields to rename (wait until after final migration of current order data):
    • 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.
• 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

<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>