WorkFerret/tables/invoice

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>