VbzCart/tables/event vc ord hold

About

• Purpose: EventPlex extension for tracking events (usually or always manually-invoked) which change the status of an order
• Related: orders, ord_hold_type

Rules

This is a bit complicated, but not messy.

• A hold event is where a hold-type is invoked or released.
• Each hold event record indicates which type of hold is applicable and whether it was set ("hold invoke event") or released ("hold release event"), and also keeps track of the current hold-state for the order. This provides a log of the order's hold-state over time as well as a quick lookup of the current hold-state so we don't also have to store it in the order record.
• If a hold-release event results in no flags being set, then the order's ID_Hold field can be set to NULL; otherwise it should always point to the most recent hold event record.

History

• 2008-12-27 Extracted from main "tables" page, but no documentation yet
• 2009 Summary: Added isActive but then replaced it with do* fields
• 2017-06-01 Renamed from ord_change to event_vc_ord_change; redesigning as EventPlex extension. Will need to import ord_pull data, if possible.
• 2017-06-02 Renaming from event_vc_ord_change to event_vc_ord_hold
  • I decided "change" was too general, and could be about changing information in the order, or order events like packaging... but instead of "pull" let's call it a "hold", for consistency with field names and to avoid conflict with old "pull" tables/naming. Also, a "hold" is different from a "pull" in that an order can have multiple active "holds" but only one active "pull".
  • Finally figured out that a release is a separate event from an invoke, and adjusted table design accordingly:
    • isRelease field
    • No need for separate NotesPull/NotesFree fields as in ord_pull, nor a WhenFreed timestamp.
  • Made the Notes field larger.
  • Oh hey, this is still MYISAM... Changed to InnoDB.
  • Wrote out as clearly as possible how this is supposed to work, since it basically took me 2-3 hours to work back around to what I had apparently intended earlier (2009?) and explained in at least one place (but not prominently enough for me to notice it until I had almost worked the same thing out again).

Fields

• The do* flag fields (formerly isActive) represent the state of the order after this event, as determined by taking the flags from the previous hold-event and applying the flags belonging to the event-type for this hold-event. This provides a record of the order's actual state over time, negating the need to follow the event history from the beginning in order to determine the order's state at any given time.
• ID_Type -> ord_hold_type.ID

SQL

CREATE TABLE `event_vc_ord_hold` (
  `ID_Event`   INT            NOT NULL AUTO_INCREMENT,
  `ID_Order`   INT            NOT NULL COMMENT "orders.ID",
  `ID_Type`    INT            NOT NULL COMMENT "ord_hold_type.ID",
  `isRelease`  BOOL           NOT NULL COMMENT "TRUE = this is a release, not an invoke",
  `doHoldRstk` TINYINT(1) DEFAULT NULL COMMENT "TRUE = do not base restock request items on this order",
  `doHoldChrg` TINYINT(1) DEFAULT NULL COMMENT "TRUE = do not charge bank card for this order",
  `doContact`  TINYINT(1) DEFAULT NULL COMMENT "TRUE = customer needs to be contacted about this order",
  `doExamine`  TINYINT(1) DEFAULT NULL COMMENT "TRUE = this order needs further examination",
  `Notes`      MEDIUMTEXT DEFAULT NULL COMMENT "human-added notes about the hold event",
  PRIMARY KEY(`ID_Event`)
)
ENGINE = InnoDB;