At the start of the year I wrote a custom invoicing system for a client, using PHP and Smarty (hope to convert to use Twig at some point). The client has since come back to me to built in extra functionality – namely a reporting section. I thought I’d share my solution to the issue I was faced with.
The client required some fairly involved reporting facilities, here is a sample of some of the requirements from the client:
- Aggregated report, split between two dates of our choosing, with totals split by either month, day or week (we want to choose)
- A list of invoices for a day of our choosing with aggregated totals of for the day selected
- Annual invoicing report – we want to show a summary of the total for the items sold, the total vat charged and summaries for average invoice values
- Invoices totals grouped by customer
There were several more, but they were all similar to the above.
Firstly, I’ll explain the database setup, for the invoicing, of the current system I built. It was basically two tables – one for the invoivce header and one table for the invoices items – nothing un typical there at all.
My first point of call to integrate the reporting was to use what I had already. This proved qwuite complicated as it involved fecthing large sets of data to calculate the totals required by the reports – this was also pretty slow when running the more intensive reports over a longer date range. It would get even more complicated and database intensive when I wanted totals for the other reports.
After some consideration, my solution to this was simple. I decided to add a few new columns to my invoice header table – “invoice_total”, “invoice_sub_total”, “invoice_vat” and “total_invoice_items”. For the reporting side of things, this made the whole process much easier. For example:
To get an aggreate total figure invoiced between two dates, I required a single sql statement, that required no joins, or loops in PHP:
SELECT SUM(`invoice_total`) As total_invoiced FROM `invoice_header` WHERE (`invoice_issue_date` >= :myDate AND `invoice_issue_date`
Equally as simple, to get a monthly total between two dates, grouped by month, the sql statement was just as simple:
SELECT SUM(`invoice_total`) As total_invoiced FROM `invoice_header` WHERE (`invoice_issue_date` >= :myDate AND `invoice_issue_date`
Getting average invoice sub total between two dates:
SELECT AVG(`invoice_sub_total`) As average_invoice_subtotal FROM `invoice_header` WHERE (`invoice_issue_date` >= :myDate AND `invoice_issue_date`
The latter made the actual PHP code required to produce the required reports very straightforward – before I would have required joins to caluclate the totals and multiple sql queries – that could have got quite messy. I also gained a few added benefits of storing the totals within my header table:
- On the print invoice screen, there was need to calculate anything, as I simply read the totals from the datbase
- On the invoice listing screens, I could now display an invoice total in a seperate column, as this was again, simply a database value – before I would have needed to calculate this total via the invoice items table
However, this simplicity in one area, came at a cost … unfortunately. As all report data was now based from totals in the invoice header table, this figure needed to be correct at all times. I needed to amend the add and edit invoice functions. When adding an invoice, I needed another loop to calculate the aggregate totals to insert into the database. When editing the invoice, I needed to recalculate these totals each time the invoice was edited (in case a row was deleted for example). Whilst the latter added a little more work when adding/editing invoices, I feel that this is well worth it due to simplicity when dealing with the reporting side.
