Open source PHP invoice system, FusionInvoice modifications

This is my contribution to FusionInvoice open source invoice system. More to come later on.

[01] Simplified Chinese language pack
I've tried my best to translate as accurate as I could.  Click here to download.

[02] How to migrate Siwapp to FusionInvoice
Step 01:
Prepare four csv with fixed column names on the first row.  I've included the column names in the SQL statements below for your convenience.  For more info, refer here.

Step 02:
Login to phpmyadmin or other DB admin of Siwapp.  Execute these SQLs for data mapping and copy the records to the csv files.
clients.csv
SELECT `name` AS 'client_name', `invoicing_address` AS 'client_address_1', '' AS 'client_address_2',
'Melbourne' AS 'client_city', 'VIC' AS 'client_state', '3000' AS 'client_zip',
'Australia' AS 'client_country', '' AS 'client_phone', '' AS 'client_fax', '' AS 'client_mobile',
`email` AS 'client_email','' AS 'client_web', 1 AS 'client_active'
FROM `customer`

invoices.csv
SELECT 'xxx@yourdomain.com' as 'user_email', `customer_name` as 'client_name',
DATE_FORMAT( `created_at`, "%Y-%l-%d" ) as 'invoice_date_created',
DATE_FORMAT( `due_date`, "%Y-%l-%d" ) as 'invoice_date_due',
`number` as 'invoice_number', `terms` as 'invoice_terms'
FROM `common`
Note: Change user_email to an existing user in FusionInvoice

invoice_items.csv
SELECT c.`number` as 'invoice_number', c.`value` as 'item_tax_rate',
DATE_FORMAT( c.`created_at`, "%Y-%l-%d" ) as 'item_date_added',
p.`reference` as 'item_name', i.`description` as 'item_description',
FORMAT( i.`quantity`, 2 ) as 'item_quantity',
FORMAT( i.unitary_cost, 2 ) as 'item_price'
FROM `common` c, `item` i
LEFT JOIN product p ON i.product_id = p.id
LEFT JOIN (
  SELECT item_tax.item_id, tax.value
  FROM tax, item_tax
  WHERE item_tax.tax_id = tax.id) c ON c.item_id = i.id
WHERE c.id = i.common_id
Note: Change all NULL values to blank in CSV

payments.csv
SELECT c.`number` as 'invoice_number', 'Cash' as 'payment_method', p.`date` as 'payment_date',
p.`amount` as 'payment_amount', p.`notes` as 'payment_note'
FROM `common` c, `payment` p
WHERE c.id = p.invoice_id

Step 03:
Place the 4 CSV files into FusionInvoice <code>uploads/import</code> folder.

Step 04:
Login to FusionInvoice, click Settings and then Import Data.  Click New and finally Import.

Open source invoice system - Siwapp modifications

I've previously written a post introducing Siwapp. Now, my 'client' has some new feature requests that the current version of Siwapp (v 0.4.1) isn't able to do so. It's time to delve into Symfony Framework again!

:: Customizations
[01] How to show all invoices after searching?
[02] File does not exist on installation.
[03] How to install Siwapp with empty DB password?
[04] Does dompdf support float in CSS?
[05] How to solve text overlapping issue in dompdf?
[06] Why Siwapp description autocomplete is not working?
[07] How to make a custom Siwapp invoice template?
[08] Siwapp round option will truncate values with 0 decimal.

:: Solutions
[01] I found out that there's only one way to display all invoices, i.e. when user initially clicks on the Dashboard or Invoices tab on the top horizontal menu. After clicking Status link to filter search result, user will have to stick with viewing only one type of status. The codes below will add a "Show All" link.

For language translation, remember to add Show All to your preferred language file located in siwapp/apps/siwapp/il8n folder.

[02] See here for detailed solution, summary:
It seemed that there was a problem with the app's .htaccess.

The main problem was here:
RewriteCond %{REQUEST_URI} \..+$
which matches all files with a dot in the filename, but the rule was ment to match files that start with a dot.  The fix:
RewriteCond %{REQUEST_URI} /\..+$

After that fix, the app created errors because of a request for favicon.ico.
The fix for that is simple enough
RewriteRule .+/favicon.ico$ favicon.ico [L]

[03] A problem exists when I try to install Siwapp into my local web server.  It doesn't allow me to put empty password in step 4.   Here's a quick solution:

[04] dompdf doesn't support float css properly, it's still in experimenting stage.  But you can enable it from siwapp/plugins/sfDomPDFPlugin/lib/dompdf/dompdf_config.inc.php
def("DOMPDF_ENABLE_CSS_FLOAT", true);

[05] If you encounter any text overlapping over table in PDF but it shows perfectly fine in print mode, I believe it's a bug in dompdf.  Solution is to avoid assigning "width:100%" to table.
table { width: 99%; }

[06] When you add a new invoice,  you can see the screen as shown below:

Add New Invoice screen (Products module is enabled)

AutoComplete feature is available on Product and Description columns but they search in different tables.  Product column will search in 'product' table as expected but Description column will do a search in 'item' table (which is used to store products that have been added to existing invoices) instead.

User can type Product Reference or Product Description in the Product column, autocomplete will then display a maximum of 10 matching product reference.  It's not user-friendly if I put product code as Product Reference.  Here's a small hack to show Product Reference and Product Description in the autocomplete dropdown.

The result:

Custom Autocomplete text. If it's more than 40 characters, truncate it and append '...'

[07] The default invoice template doesn't look nice but it gave me some ideas on how to work on templates.
- Original invoice template link
- Siwapp template documentation
Below is my customized invoice template:

Custom template print view

[08] This is one quirk that caught me out of the blue, I was expecting a quick fix when I'm editing the invoice template.  Instead of showing the currency name beside every number in PDF, I just want to show it in Price label.  So, I change '|currency' to '|round' (default precision is 2).  I noticed:
'9' expects to be '9.00', result: '9'
'9.5' expects to be '9.50', result '9.5'
'10.55' expects to be '10.55', result '10.55'

How to cache/refresh static resources

They're a couple of ways to cache static resources, e.g. css, javascript and images.  You can specify the default cache values in .htaccess file (as shown below in ".htaccess_original"). After some tweaking effort, I managed to integrate this feature into CodeIgniter. The main issue of this solution would be retrieving the last modified date of each file.

Server Side
Server & Client sides
If there're css or javascript files that you need to load dynamically, then you would need to use another approach. For CodeIgniter, I set a config item, called "code_revision" which store the latest version number of your source project. PHP and Javascript will be referring to this variable for consistency. References:

• Automatically version your CSS and Javascript files
• Things You Should Do Soon: Static Resources
• Revving Filenames: Don't use querystring
• Stackoverflow: REGEX capture filename without file extension

Open source invoice software [UPDATE]

On a weekend, I was asked to develop a "simple" software to generate invoice, a sample document is given as reference.  First thing that comes out from my mind is to setup a template in Office Excel, that would be easy and fast.  But the idea gets turned off immediately because invoice number needs to be auto-incremented.

A simple google search leads me to Siwapp Invoice System and Simple Invoices.  I spent some time playing with the demos, here's my opinion.

Siwapp v 0.4.1
	PROS
	Easy to setup in XAMPP (Apache web server, PHP, MySQL bundle on local machine)
	Clean and pretty interface
	Built on Symphony which is similar to CodeIgniter and PHP5.3, easy for programmers to do customisations
	Able to set up recurring invoices
	Provide wiki and community forum to look for solutions
	Able to create invoice templates
	Able to add extra modules, e.g. Customers, Estimates and Products
	CONS
	Beta version, undergoing very slow development phase
	Unable to set up database in XAMPP if password field is empty unless you apply a small hack as shown here

Simple Invoice v 2011.1
	PROS
	Easy to setup in XAMPP
	Provide several types of reports
	Able to create database backup files
	Offer more configurations
	Supports PayPal and Eway Merchant
	CONS
	UI needs to be improved, should not have fix width

FusionInvoice v1.2.4 beta
	PROS
	Build on CodeIgniter PHP MVC framework.
	Simple, clean UI from Twitter bootscrap
	Ongoing development and bug fixing by the creator
	Same and more functionalities as Siwapp
	Calendar view
	Able to import .csv files, see documentation.
	Report generating
	CONS
	Cannot change invoice template from UI
	Limited search criterias
	Cannot do batch actions on invoices
	Pagination is not done in Ajax

Update:
I've done some modifications to Siwapp, check out my other post for more info.  I stopped halfway through implementing "batch invoice status update" feature.  I might continue to develop it until I'm confident to release the codes.

On the other hand, I came across FusionInvoice early today.  After playing the demo and comparing it to Siwapp, I would say it's a decent software and worth checking out the code structure.

References:

• Four Open Source Invoicing Apps Worth Checking Out
• Windows Grep: Graphical text search and replace tool for Win32 (no expiry date applied without registration)

How to implement achievement system in CodeIgniter

In this post, I'm going to implement a reward system using PHP, similar to the current console generation's game achievements. Certain user interactions which satisfy the game's predefined set of requirements will trigger an event of showing the reward. E.g. Survive a fall from 400m will unlock the achievement of "Die Hard".

How to implement this feature in PHP? Since I've been working on CodeIgniter MVC, I shall introduce you a library, simply called as "CodeIgniter Events" by Eric Barnes and Dan Horrigan.

Steps

1. Download the latest CodeIgniter here, version 2.1.3 as of writing 
2. Download CodeIgniter Events library here
3. Add the autoload line in config/autoload.php and put the events.php in application/libraries folder

Tasks

Whenever user types more than 5 characters into a text field and clicks 'Send' button, it will trigger the event class and show the achievement text.

Solution