WorkFerret/tables/session

About

• History:
  • 2010-01-28 Adapted from MS Access version
  • 2010-01-29 Changed ID_Prev to Seq; moved next to Sort
  • 2010-01-30 Added CostAdd field from MS Access version (not sure why it got left out)
  • 2010-02-01 Added ID_InvcLine field for invoice management
  • 2010-02-27 Added ID_Invc so we can generate a list of all Sessions for a given Invoice, including Sessions which don't have a specific Invoice Line (ID_InvcLine=0)
• Fields:
  • TimeAdd: time added to (finish-start), typically an approximation for when start/finish times aren't known
  • TimeSub: time subtracted from (finish-start), typically for distractions in the middle of a work session
  • TimeTotal = WhenFinish - WhenStart + TimeAdd - TimeSub
  • CostAdd is added before calculating CostLine; can be used for flat-rate billing and for deducting invoice amounts
  • CostLine = (TimeTotal * BillRate) + CostAdd
  • Seq: (2010-10-07) The purpose of this field has not been well-defined. I'm thinking that it should reflect the order in which lines were last processed for generating a balance (maybe it should be renamed SeqBal). It should be automatically set based on sorting by (WhenStart, Sort). Reports can use it for sorting so that CostBal actually follows the sequence of calculation.

SQL

<mysql>DROP TABLE IF EXISTS `session`; CREATE TABLE `session` (

 `ID`         INT(4)           NOT NULL AUTO_INCREMENT,
 `ID_Proj`    INT(4)           NOT NULL COMMENT "Project to which this session applies",
 `WhenStart`  DATETIME     DEFAULT NULL COMMENT "when Session started (optional)",
 `WhenFinish` DATETIME     DEFAULT NULL COMMENT "when Session finished (optional)",
 `Sort`       DECIMAL(7,4) DEFAULT NULL COMMENT "when there's no specific time, use this to sort sessions",
 `Seq`        INT(4)           NOT NULL COMMENT "order in which this line was added (within a Project)",
 `TimeAdd`    INT(4)       DEFAULT NULL COMMENT "time to add, in minutes",
 `TimeSub`    INT(4)       DEFAULT NULL COMMENT "time to subtract, in minutes",
 `TimeTotal`  INT(4)       DEFAULT NULL COMMENT "calculated total time for this session",
 `ID_Rate`    INT(4)       DEFAULT NULL COMMENT "rate ID, for tracking usage -- ignored by billing",
 `BillRate`   DECIMAL(9,2) DEFAULT NULL COMMENT "rate, if using time",
 `CostAdd`    DECIMAL(9,2) DEFAULT NULL COMMENT "additional cost (can be used instead of by-rate total)",
 `CostLine`   DECIMAL(9,2)     NOT NULL COMMENT "final cost for this line",
 `CostBal`    DECIMAL(9,2) DEFAULT NULL COMMENT "cost balance after this line is added in",
 `Descr`      VARCHAR(127) DEFAULT NULL COMMENT "session summary, for invoices",
 `Notes`      VARCHAR(255) DEFAULT NULL COMMENT "longer notes, for reference",
 `WhenEntered` DATETIME        NOT NULL COMMENT "when this record was first created",
 `WhenEdited`  DATETIME    DEFAULT NULL COMMENT "when an existing record was modified by hand",
 `WhenFigured` DATETIME    DEFAULT NULL COMMENT "when the balance was last calculated",
 `ID_Invc     INT(4)       DEFAULT NULL COMMENT "invoice to which this session was applied",
 `ID_InvcLine INT(4)       DEFAULT NULL COMMENT "invoice line to which this session was applied",
  PRIMARY KEY(`ID`)

) ENGINE=MyISAM;</mysql>