WorkFerret/tables/invoice: Difference between revisions
< WorkFerret | tables
Jump to navigation
Jump to search
(Created page with '==About== * '''History''': ** '''2010-01-28''' Adapted from MS Access version * '''Fields''': ** '''InvcSeq''' is used to generate the invoice number. I'm thinking [invc ID [zero…') |
m (6 revisions imported: moving from htyp) |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
== | ==History== | ||
* ''' | * '''2010-01-28''' Adapted from MS Access version | ||
* '''2010-02-05''' Added fields: InvcNum, Notes | |||
* ''' | * '''2010-02-09''' Added field: ID_Debit | ||
* '''2010-02-11''' Added field: WhenEdited | |||
==Fields== | |||
* '''InvcSeq''' is used to generate the invoice number. I'm thinking [invc ID [zero-padded]]-[invc pfx]-[InvcSeq] would be sortable ''and'' would provide a validity check (i.e. nobody could just make one up because of the ID). | |||
** Perhaps this field should be changed to INTEGER. It was VARCHAR because I thought I might use some alpha characters at some point, like "12A". It's not clear if this can even be sorted properly in SQL. If the ability to insert between integers is needed, then maybe it should be a FLOAT and we could do things like "12.1" rather than "12A". For now, we have to use "ORDER BY (InvcSeq+0)" to make sure it sorts properly. | |||
==Future== | |||
* '''WhenPaid''' will be obsolete when Charges/statement functionality is working. It's already kind of useless... | |||
==SQL== | ==SQL== | ||
<mysql>DROP TABLE IF EXISTS `invoice`; | <mysql>DROP TABLE IF EXISTS `invoice`; | ||
| Line 10: | Line 15: | ||
`ID_Proj` INT(4) NOT NULL COMMENT "Project to which this invoice applies", | `ID_Proj` INT(4) NOT NULL COMMENT "Project to which this invoice applies", | ||
`InvcSeq` VARCHAR(7) NOT NULL COMMENT "Invoice sequence number", | `InvcSeq` VARCHAR(7) NOT NULL COMMENT "Invoice sequence number", | ||
`InvcNum` VARCHAR(31) NOT NULL COMMENT "Complete invoice number", | |||
`TotalAmt` DECIMAL(9,2) NOT NULL COMMENT "total amount billed", | `TotalAmt` DECIMAL(9,2) NOT NULL COMMENT "total amount billed", | ||
`ID_Debit` INT(4) DEFAULT NULL COMMENT "ID of session in which invoice total is deducted", | |||
`WhenCreated` DATETIME NOT NULL COMMENT "when the invoice was created", | `WhenCreated` DATETIME NOT NULL COMMENT "when the invoice was created", | ||
`WhenEdited` DATETIME DEFAULT NULL COMMENT "when the record was last modified"; | |||
`WhenSent` DATETIME DEFAULT NULL COMMENT "when the invoice was sent out to the client", | `WhenSent` DATETIME DEFAULT NULL COMMENT "when the invoice was sent out to the client", | ||
`WhenPaid` DATETIME DEFAULT NULL COMMENT "when payment was received on this invoice", | `WhenPaid` DATETIME DEFAULT NULL COMMENT "when payment was received on this invoice", | ||
`WhenVoid` DATETIME DEFAULT NULL COMMENT "when this invoice was cancelled or otherwise nullified", | `WhenVoid` DATETIME DEFAULT NULL COMMENT "when this invoice was cancelled or otherwise nullified", | ||
`Notes` VARCHAR(255) DEFAULT NULL COMMENT "human-entered notes", | |||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) ENGINE=MyISAM;</mysql> | ) ENGINE=MyISAM;</mysql> | ||
Latest revision as of 20:32, 1 May 2022
History
- 2010-01-28 Adapted from MS Access version
- 2010-02-05 Added fields: InvcNum, Notes
- 2010-02-09 Added field: ID_Debit
- 2010-02-11 Added field: WhenEdited
Fields
- InvcSeq is used to generate the invoice number. I'm thinking [invc ID [zero-padded]]-[invc pfx]-[InvcSeq] would be sortable and would provide a validity check (i.e. nobody could just make one up because of the ID).
- Perhaps this field should be changed to INTEGER. It was VARCHAR because I thought I might use some alpha characters at some point, like "12A". It's not clear if this can even be sorted properly in SQL. If the ability to insert between integers is needed, then maybe it should be a FLOAT and we could do things like "12.1" rather than "12A". For now, we have to use "ORDER BY (InvcSeq+0)" to make sure it sorts properly.
Future
- WhenPaid will be obsolete when Charges/statement functionality is working. It's already kind of useless...
SQL
<mysql>DROP TABLE IF EXISTS `invoice`; CREATE TABLE `invoice` (
`ID` INT(4) NOT NULL AUTO_INCREMENT, `ID_Proj` INT(4) NOT NULL COMMENT "Project to which this invoice applies", `InvcSeq` VARCHAR(7) NOT NULL COMMENT "Invoice sequence number", `InvcNum` VARCHAR(31) NOT NULL COMMENT "Complete invoice number", `TotalAmt` DECIMAL(9,2) NOT NULL COMMENT "total amount billed", `ID_Debit` INT(4) DEFAULT NULL COMMENT "ID of session in which invoice total is deducted", `WhenCreated` DATETIME NOT NULL COMMENT "when the invoice was created", `WhenEdited` DATETIME DEFAULT NULL COMMENT "when the record was last modified"; `WhenSent` DATETIME DEFAULT NULL COMMENT "when the invoice was sent out to the client", `WhenPaid` DATETIME DEFAULT NULL COMMENT "when payment was received on this invoice", `WhenVoid` DATETIME DEFAULT NULL COMMENT "when this invoice was cancelled or otherwise nullified", `Notes` VARCHAR(255) DEFAULT NULL COMMENT "human-entered notes", PRIMARY KEY(`ID`)
) ENGINE=MyISAM;</mysql>