WorkFerret/tables/session: Difference between revisions
< WorkFerret | tables
Jump to navigation
Jump to search
(ok, that's the rest of the fields) |
m (9 revisions imported: moving from htyp) |
||
| (7 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
* '''History''': | * '''History''': | ||
** '''2010-01-28''' Adapted from MS Access version | ** '''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) | |||
** '''2016-04-12''' Changed both Descr (VARCHAR(128)) and Notes (VARCHAR(255)) to TEXT, because descriptions were getting cut off | |||
* '''Fields''': | * '''Fields''': | ||
** '''TimeAdd''': time added to (finish-start), typically an approximation for when start/finish times aren't known | ** '''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 | ** '''TimeSub''': time subtracted from (finish-start), typically for distractions in the middle of a work session | ||
** '''TimeTotal''' = WhenFinish - WhenStart + TimeAdd - TimeSub | ** '''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== | ==SQL== | ||
<mysql>DROP TABLE IF EXISTS `session`; | <mysql>DROP TABLE IF EXISTS `session`; | ||
| Line 14: | Line 22: | ||
`WhenFinish` DATETIME DEFAULT NULL COMMENT "when Session finished (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", | `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", | `TimeAdd` INT(4) DEFAULT NULL COMMENT "time to add, in minutes", | ||
`TimeSub` INT(4) DEFAULT NULL COMMENT "time to subtract, in minutes", | `TimeSub` INT(4) DEFAULT NULL COMMENT "time to subtract, in minutes", | ||
| Line 19: | Line 28: | ||
`ID_Rate` INT(4) DEFAULT NULL COMMENT "rate ID, for tracking usage -- ignored by billing", | `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", | `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", | `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", | `CostBal` DECIMAL(9,2) DEFAULT NULL COMMENT "cost balance after this line is added in", | ||
`Descr` TEXT DEFAULT NULL COMMENT "session summary, for invoices", | |||
`Descr` | `Notes` TEXT DEFAULT NULL COMMENT "longer notes, for reference", | ||
`Notes` | |||
`WhenEntered` DATETIME NOT NULL COMMENT "when this record was first created", | `WhenEntered` DATETIME NOT NULL COMMENT "when this record was first created", | ||
`WhenEdited` DATETIME DEFAULT NULL COMMENT "when an existing record was modified by hand", | `WhenEdited` DATETIME DEFAULT NULL COMMENT "when an existing record was modified by hand", | ||
`WhenFigured` DATETIME DEFAULT NULL COMMENT "when the balance was last calculated", | `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`) | PRIMARY KEY(`ID`) | ||
) ENGINE=MyISAM;</mysql> | ) ENGINE=MyISAM;</mysql> | ||
Latest revision as of 20:32, 1 May 2022
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)
- 2016-04-12 Changed both Descr (VARCHAR(128)) and Notes (VARCHAR(255)) to TEXT, because descriptions were getting cut off
- 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` TEXT DEFAULT NULL COMMENT "session summary, for invoices", `Notes` TEXT 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>