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'

Save electricity with LED lightings

Left: LED unit without downlight cap; Middle: 12W LED with cap; Right: Halogen 50W bulb

Although Melbourne is crowned as one of "the most liveable city in the world" in recent years, the ever increasing living cost is somehow killing the low income or big families.  It's never to late to do your  part in putting resources in good use and save the earth.

Well, I shall cut down electricity usage by substituting all 50W Halogen downlights to 12W LEDs (4 LEDs in each bulb).  There're a few base standards in the market, namely GU10, MR16 (GU5.3) and E27.

1. Make sure the current transformer supports the LED unit, normally it's 12V
2. Refer to the table below to see which LED unit to buy in order to achieve the same brightness
3. Get the measurements right before ordering
4. Order LED lightings
   
   Product Spec:
   LEDs quantity: 4 pieces
   LED Source: High Power CREE LED Chips Dimmable
   LED Power:4x3W 12W
   Power consumption: 12W
   Input voltages: AC85V-265V AC12V
   
   Base:MR16 (Other options: E27 MR16 B22 GU10 E14 GU5.3)
   Material of shell:Aluminum Alloy
   Size:55mm x 50mm
   Weight:57g
   
   Lumens:650-750LM(approx)
   Reflctor efficiency:90%
   Light source:4pcs 3Watt High Power LED Chips
   Body temperature:<60 Degree Celsius
   LED work temperature:0-65 Degrees Celsius
   Equivalent to 60W Halogen Bulb
   Approvals: CE, RoHS,CCC,FCC certificate
   Shape: Round
   Average rated lifespan: 50,0000 hours under normal usage
5. Replace halogen bulb for LED ones, test it.  If LED unit flickers, check the pin connection or transformer

Below is the table to compare between incandescent and LED to emit the same amount of light.

Lumens (lm)	Incandescent (W)	LED (W)
2600	150	25 - 28
1600	100	16 - 20
1100	75	9 - 13
800	60	8 - 12
450	40	6 - 9

Color temperature table

Color Temperature	Value	Areas
Warm White (yellowish glow)	2700K - 3500K	Lounge, Bedroom
Pure White	3500K - 5500K	For your own preference if you find cool white is too bright
Cool White	5500K - 7000K	Kitchen, Office, Bathroom, Laundry

This handy calculator will give you a rough idea on how much you can save.