Oppa noobie style (õ_ó): April 2013

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.

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:

	/**
	* Step #4: Database
	* The validator of this form checks database connection
	* and creates the database if it doesn\'t exists
	* See lib/validator/dbConnectionValidator.class.php
	* File location: siwapp/apps/installer/modules/static/actions/actions.class.php
	*
	* @param $request
	*/
	public function executeStep4(sfWebRequest $request)
	{
	$this->form = new DatabaseConfigurationForm();

	if ($request->isMethod('post'))
	{
	$params = $request->getParameter('db');
	$this->form->bind($params);
	// CHANGE: Skip form validation to allow empty password (if you setup in XAMPP)
	//if($this->form->isValid())
	//{
	$u = $this->getUser();
	$u->setAttribute('database', $params['database']);
	$u->setAttribute('username', $params['username']);
	$u->setAttribute('password', $params['password']);
	$u->setAttribute('host', $params['host']);
	$u->setAttribute('step', 5);

	$this->redirect($this->next);
	//}
	}
	}

view raw actions.class.php hosted with ❤ by GitHub

[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.

	// File location: siwapp/apps/siwapp/modules/common/templates/_invoiceRow.php

	//connect the selection of a product to update the row item
	$('#".$invoiceItemForm['product_autocomplete']->renderId()."')
	.autocomplete('".$urlAjaxSelectProduct."', jQuery.extend({}, {
	dataType: 'json',
	parse: function(data) {
	var parsed = [];
	for (key in data) {
	parsed[parsed.length] = {
	data: [
	data[key].reference,
	data[key].description,
	data[key].price,
	data[key].id
	],
	value: data[key].reference,
	result: data[key].reference,
	};
	}
	return parsed;
	},
	minChars: 2,
	matchContains: true,
	// START Custom Code ------------------------------------
	formatItem: function(row) {
	return row[0] + ': ' + ((row[1] && row[1].length > 50) ? row[1].substring(0, 50) + '...' : row[1]);
	},
	width: 350,
	// END Custom Code ---------------------------------------
	}))

view raw _invoiceRow.php hosted with ❤ by GitHub

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:

	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
	<html lang="{{lang}}" xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Invoice</title>
	<style type="text/css">
	@page { margin:0.5cm 0.5cm 0.8cm 1.5cm; }
	body { margin:0.8cm auto;font:1em;}
	div { display:block; }
	table { border-collapse:collapse; border-spacing:0; width:99%;}
	table.region td { border:0; }
	table tr th { text-align:center; font-weight:bold; background-color:#eee; }
	table tr th, table tr td { border:1px solid #333; padding: 3px; vertical-align:top; }
	table tr td.hb-l { border-left:0; }
	table tr td.hb-b { border-bottom:0; }
	table tr td.hb-t { border-top:0; }
	h2 { padding:2px 5px 2px 0; margin:0; font-weight:bold; font-size:2.2em; }
	h3 { padding:2px 5px 2px 0; margin:0; font-weight:bold; font-size:1.6em; }
	p { padding:3px 0; margin:0; }

	#hd, #bd, #ft {width:95%; margin:0 auto 20px auto; overflow:auto; }
	#hd .comp_profile img { width:280px; height:58px; }
	#hd .inv_info table tr td { text-align:center; width:120px; border:1px solid #333; }

	#bd .delivery-info { margin-top: 20px; } /* for dompdf, margin-bottom does not work well */
	#bd .delivery-info table { width:85%; margin:0 auto 10px; }
	#bd .delivery-info table th.customer { width: 40%; }
	#bd .product-info table { margin:0 auto 10px; }

	.f-small { font-size:0.7em; }
	.f-medium { font-size:0.9em; }
	table .right { text-align:right; }
	table .center { text-align:center; }
	table .ty { width: 55px; }
	table .sm { width:100px; }
	.section { clear:both; /page-break-before: always;/ }

	/* CSS code for printing */
	@media print {
	body { margin:auto; }
	#hd, #bd, #ft {width:600px; margin:0 auto 20px 0; overflow:auto; }
	.section { page-break-inside:avoid; }
	#bd .delivery-info table { width:100%; margin:0 auto 10px; }
	}
	</style>
	</head>
	<body>
	<div id="hd" class="section">
	<table class="region">
	<tbody>
	<tr>
	<td width="70%">
	<div class="comp_profile">
	{% if settings.company_logo %}
	<img src="{{ settings.company_logo }}" alt="{{ settings.company_name }}" />
	{% else %}
	<h3>{{ settings.company_name }}</h3>
	{% endif %}
	{{settings.company_address\|format}}
	{{settings.company_phone}}
	</div>
	</td>
	<td>
	<div class="inv_info">
	<h2>Tax Invoice</h2>
	<table>
	<thead>
	<tr>
	<th>Date</th>
	<th>Invoice No.</th>
	</tr>
	</thead>
	<tbody>
	<tr>
	<td>{{ invoice.issue_date\|date('d', 'en_AU') }}</td>
	<td>{{ invoice }}</td>
	</tr>
	</tbody>
	</table>
	</div>
	</td>
	</tr>
	</tbody>
	</table>
	</div>

	<div id="bd" class="section">
	<div class="delivery-info">
	<table>
	<thead>
	<tr>
	<th class="customer">Customer</th>
	<th>Invoice To</th>
	<th>Deliver To</th>
	</tr>
	</thead>
	<tbody>
	<tr>
	<td>
	<strong>{{ invoice.customer_name }}</strong><br/>
	{{ invoice.contact_person }}<br/>
	{{ invoice.customer_email }}
	</td>
	<td>
	{{ invoice.invoicing_address\|format }}
	</td>
	<td>
	{{ invoice.shipping_address\|format }}
	</td>
	</tr>
	</tbody>
	</table>
	</div>
	<div class="product-info">
	<table>
	<thead>
	<tr>
	<th class="big">Description</th>
	<th class="sm">Unit Cost</th>
	<th class="ty">Qty</th>
	<th class="ty">Tax</th>
	<th class="sm">Discount</th>
	<th class="sm">Price <span class="f-small">({{ settings.currency }})</span></th>
	</tr>
	</thead>
	<tbody>
	{% for item in invoice.Items %}
	<tr>
	<td>{{ item.description }}</td>
	<td class="center">{{ item.unitary_cost\|round }}</td>
	<td class="center">{{ item.quantity }}</td>
	<td class="center">
	{% for tax in item.Taxes %}
	<span class="tax">{{tax.name}}</span>
	{% endfor %}
	</td>
	<td class="center">{{item.discount_amount\|round}}</td>
	<td class="right">{{item.gross_amount\|round}}</td>
	</tr>
	{% endfor %}
	</tbody>
	<tfoot>
	<tr>
	<td colspan="{% if invoice.discount_amount %}4{% else %}3{% endif %}" class="hb-l hb-b"></td>
	<th class="right">Gross</th>
	<td class="right">{{invoice.base_amount\|round}}</td>
	</tr>
	{% if invoice.discount_amount > 0 %}
	<tr>
	<td colspan="{% if invoice.discount_amount %}4{% else %}3{% endif %}" class="hb-l hb-b hb-t"></td>
	<th class="right">Discount</th>
	<td class="td_global_discount right">{{invoice.discount_amount\|round}}</td>
	</tr>
	{% endif %}
	<tr>
	<td colspan="{% if invoice.discount_amount %}4{% else %}3{% endif %}" class="hb-l hb-b hb-t"></td>
	<th class="right">Subtotal</th>
	<td class="td_subtotal right">{{invoice.net_amount\|round}}</td>
	</tr>
	<tr>
	<td colspan="{% if invoice.discount_amount %}4{% else %}3{% endif %}" class="hb-l hb-b hb-t"></td>
	<th class="right">Tax</th>
	<td class="td_total_taxes right">{{invoice.tax_amount\|round}}</td>
	</tr>
	<tr class="strong">
	<td colspan="{% if invoice.discount_amount %}4{% else %}3{% endif %}" class="hb-l hb-b hb-t"></td>
	<th class="right">Total</th>
	<td class="td_total right">{{invoice.gross_amount\|round}}</td>
	</tr>
	</tfoot>
	</table>
	</div>
	</div>

	<div id="ft" class="section">
	<h3>Terms & conditions</h3>
	<div class="terms">
	{% if invoice.terms != '' %}
	{{ invoice.terms\|format }}
	{% elseif settings.legal_terms != '' %}
	{{ settings.legal_terms\|format }}
	{% endif %}

	{% if invoice.due_date %}
	<p>Please pay the remaining amount before {{ invoice.due_date\|date('D', 'en_AU') }}. Thank you.</p>
	{% endif %}
	</div>
	</div>
	</body>
	</html>

view raw my-invoice-template.html hosted with ❤ by GitHub

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'

	// File location: siwapp/lib/templating/Common_Twig_Extension.php

	function common_twig_extension_round($amount, $decimals = 2)
	{
	//return round($amount, $decimals);
	return number_format(round($amount, $decimals), $decimals, '.', '');
	}

view raw Common_Twig_Extension.php hosted with ❤ by GitHub

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	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

Oppa noobie style (õ_ó)

Open source PHP invoice system, FusionInvoice modifications

Open source invoice system - Siwapp modifications

Save electricity with LED lightings

Labels

Blog Archive

Press F1 for Help

About Me