VbzCart/docs/tables/event vc ord hold: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
m (VbzCart/tables/ord events moved to VbzCart/tables/ord event: renaming to singular while it's still easy)
imported>Woozle
(alert fields, SysUser)
Line 8: Line 8:
*** Added '''VbzUser''' and '''Machine''' fields
*** Added '''VbzUser''' and '''Machine''' fields
*** Renamed to singular form
*** Renamed to singular form
** '''2009-10-18'''
*** Replaced '''isActive''' field with '''do*''' fields
*** Added '''SysUser''', for consistency with other event log tables
* '''Fields''':
* '''Fields''':
** '''isActive''' represents the state of the order after this event, as determined by taking the order's previous flags and applying the event type's flags -- if no hold flags are active, then the order is active.
** <s>'''isActive''' represents</s> The '''do*''' flag fields represent the state of the order after this event, as determined by taking the order's previous flags and applying the event type's flags -- if no hold flags are active, then the order is active. 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 find the order's state at any given time.
** '''VbzUser''' will probably just be the username from the wiki, if we will be using the wiki to manage user log-ins and provide access to the ordering system. Not sure how to let wiki authentication work for vbzcart code, unless we move the wiki to the same domain instead of its own subdomain (...or actually have the wiki present vbzcart pages, eventually?).
** '''VbzUser''' will probably just be the username from the wiki, if we will be using the wiki to manage user log-ins and provide access to the ordering system. Not sure how to let wiki authentication work for vbzcart code, unless we move the wiki to the same domain instead of its own subdomain (...or actually have the wiki present vbzcart pages, eventually?).
** '''Machine''' hasn't been rigorously defined yet; it just needs to be some way we can tell what computer was being used (and it does not need to be authenticated; authentication records should be kept elsewhere). Ideally, it should be netname@[ip address], but various coding environments may not support both of those.
** '''Machine''' hasn't been rigorously defined yet; it just needs to be some way we can tell what computer was being used (and it does not need to be authenticated; authentication records should be kept elsewhere). Ideally, it should be netname@[ip address], but various coding environments may not support both of those.
Line 23: Line 26:
==SQL==
==SQL==
<section begin=sql /><mysql>CREATE TABLE `ord_event` (
<section begin=sql /><mysql>CREATE TABLE `ord_event` (
   `ID`        INT NOT NULL AUTO_INCREMENT,
   `ID`             INT       NOT NULL AUTO_INCREMENT,
   `ID_Ord`   INT NOT NULL COMMENT "core_orders.ID",
   `ID_Ord`         INT       NOT NULL COMMENT "core_orders.ID",
   `ID_Type`   INT NOT NULL COMMENT "ord_event_type.ID",
   `ID_Type`       INT       NOT NULL COMMENT "ord_event_type.ID",
   `isActiveBOOL COMMENT "state of order after this event",
   `doHoldRstk` TINYINT(1) DEFAULT NULL COMMENT "TRUE = do not base restock request items on this order",
   `WhenDone`  DATETIME     COMMENT "when the event happened",
  `doHoldChrg` TINYINT(1) DEFAULT NULL COMMENT "TRUE = do not charge bank card for this order",
   `VbzUser`  VARCHAR(127) COMMENT "username of whoever initiated the event",
  `doContactTINYINT(1) DEFAULT NULL COMMENT "TRUE = customer needs to be contacted about this order",
   `Machine`  VARCHAR(63) COMMENT "some way of identifying the user's machine -- network name or IP address",
  `doExamine`  TINYINT(1) DEFAULT NULL COMMENT "TRUE = this order needs further examination",
   `Notes`    VARCHAR(255) COMMENT "human-entered notes, if needed",
   `WhenDone`  DATETIME                 COMMENT "when the event happened",
   `VbzUser`  VARCHAR(127)             COMMENT "VbzCart username of whoever initiated the event, if available",
  `SysUser`  VARCHAR(127)            COMMENT "who logged into the operating system, if available",
   `Machine`  VARCHAR(63)             COMMENT "network name or IP address of client",
   `Notes`    VARCHAR(255)             COMMENT "human-entered notes, if needed",
   PRIMARY KEY(`ID`)
   PRIMARY KEY(`ID`)
)
)
ENGINE = MYISAM;</mysql>
ENGINE = MYISAM;</mysql>
<section end=sql />
<section end=sql />

Revision as of 16:46, 18 October 2009

About

  • Purpose: for tracking all order-related events, especially those affecting the order's status
  • History:
    • 2008-12-27 Extracted from main "tables" page, but no documentation yet
    • 2009-07-12
      • Preliminary documentation
      • Added isActive field (was in Template:Vbzcart, didn't belong there)
      • Added VbzUser and Machine fields
      • Renamed to singular form
    • 2009-10-18
      • Replaced isActive field with do* fields
      • Added SysUser, for consistency with other event log tables
  • Fields:
    • isActive represents The do* flag fields represent the state of the order after this event, as determined by taking the order's previous flags and applying the event type's flags -- if no hold flags are active, then the order is active. 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 find the order's state at any given time.
    • VbzUser will probably just be the username from the wiki, if we will be using the wiki to manage user log-ins and provide access to the ordering system. Not sure how to let wiki authentication work for vbzcart code, unless we move the wiki to the same domain instead of its own subdomain (...or actually have the wiki present vbzcart pages, eventually?).
    • Machine hasn't been rigorously defined yet; it just needs to be some way we can tell what computer was being used (and it does not need to be authenticated; authentication records should be kept elsewhere). Ideally, it should be netname@[ip address], but various coding environments may not support both of those.

backstory

I apparently designed this and its "types" table (Template:Vbzcart) sometime in 2008, but didn't document them; a note on the Template:Vbzcart page dated 2008-12-27 says "Are we actually using this, or is it part of the future online ordering system redesign?"

As of 2009-07, though, it has become clear that a general order log seems necessary for the following reasons:

  • To replace the Template:Vbzcart table
  • To make it unnecessary to track user details inside other order-related records
  • To keep an automatic record of all changes made to an order and any significant processing events

The system implemented here of defining event types which may or may not block certain things seems compatible with this goal, and we mainly just needed to add fields for user-tracking.

SQL

<section begin=sql /><mysql>CREATE TABLE `ord_event` (

 `ID`             INT        NOT NULL AUTO_INCREMENT,
 `ID_Ord`         INT        NOT NULL COMMENT "core_orders.ID",
 `ID_Type`        INT        NOT NULL COMMENT "ord_event_type.ID",
 `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",
 `WhenDone`  DATETIME                 COMMENT "when the event happened",
 `VbzUser`   VARCHAR(127)             COMMENT "VbzCart username of whoever initiated the event, if available",
 `SysUser`   VARCHAR(127)             COMMENT "who logged into the operating system, if available",
 `Machine`   VARCHAR(63)              COMMENT "network name or IP address of client",
 `Notes`     VARCHAR(255)             COMMENT "human-entered notes, if needed",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql> <section end=sql />