Gnucash with MySQL: Creating custom invoices with PDF output and template based email to the customer

No comments

I’ve been using GnuCash for some time and have found it to be a great product. Where GnuCash falls short, in my opinion, is it doesn’t provide for scheduled automatic invoice generation. Additionally creating custom invoices involves learning Scheme which personally I don’t have time to get my head around. So I set out to fix these 2 issues using the tools I know well, namely MySQL, perl, and good old HTML. This post will cover custom invoice generation to html and pdf, and emailing the results to the customer using a template. GnuCash scheduled automatic invoice creation will be covered in another post in the near future.

Firstly let’s set out the baseline required to implement this solution.  I’m currently running GnuCash version 2.4.10. I’d expect this solution will work with any version on GnuCash which supports a MySQL database. This is important because the solution requires GnuCash to be using MySQL and not the standard xml files. This link  http://gnucash.org/docs/v2.4/C/gnucash-guide/basics-files1.html explains how to use an MySQL database. Perl and Python are also required. This solution has been tested on a Linux PC. The solution should work on a Windows PC with minor adjustments.

The Perl script I’ve created requires the following modules HTML::Template and MIME::Lite::TT:HTML. Use the cpan shell to install them. The Python package xhtml2pdf is also required.  It can be downloaded here http://pypi.python.org/pypi/xhtml2pdf/.

The Perl script is called inv2pdf.pl and it takes 5 command line options.

eg. inv2pdf.pl -i000001 -t redirect@home.local -e -v -f/opt/invoices

-i is used to provide the invoice number you wish to generate a custom report for. This option is required.

-v will open the pdf  in a viewer once generated.

-e will email the invoice using the email address of the customer in GnuCash.

-t is used to specify another email address other than the address stored in GnuCash. -t implies -e.

-f is used to specify the location of the templates and the working folder to create the html and pdf files otherwise the current working directory is used.

inv2pdf.pl uses 3 template files invoice.tmpl, invoice.html.tt, and invoice.text.tt.

invoice.tmpl is a standard html file with placeholders for variables populated by perl. See http://search.cpan.org/~wonko/HTML-Template-2.91/lib/HTML/Template.pm for a full description on the variable usage. This file is the html template for the invoice.

invoice.html.tt and invoice.txt.tt are the templates for the email being sent. The email is sent as multipart html and text. Three parameters are passed to the templates:

  • The first name of the customer’s Billing Address Name. If this is blank the Company Name is used.
  • The invoice ID.
  • The due date of the invoice.

Before you use inv2pdf.pl you need to edit the file and modify a few variables to suit your environment.

$dsn = "DBI:mysql:database=gnucash;host=localhost";
$from = 'accounts@yourdomain.com.au';
$user = "dbuser";
$pass = "dbpassword";

$dsn is the connection string for you gnucash database.

$from is the email address to send the emails from.

$user is the database user to use to connect to the gnucash db.

$pass is the password for the database user.

If successful  inv2pdf.pl will created 2 invoice files in the working folder. The naming standard for the files are gen-[invoice number].html and  gen-[invoice number].pdf.  The files mentioned throughout this post are attached below.

The next post in this series will be on scheduling and automatically generating Gnucash invoices.

inv2pdf.pl

#!/usr/bin/perl
# inv2pdf - generate and email pdf GNUCash invoices from HTML template
#    Copyright (C) 2012  Matt Marschall - Red Mars Consulting
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#	Contact: mmarschall@redmars.com.au

use HTML::Template;
use DBI;
use MIME::Lite::TT::HTML;
use Getopt::Std;
use Data::Dumper;
use Cwd;
my %opts;
getopts('evi:t:f:',\%opts);

die "Invoice id missing. use option -i " unless defined $opts{i};
my $folder = $opts{f} || cwd();
my $invoice = HTML::Template->new(filename => $folder.'/invoice.tmpl',
					global_vars => 1);

($csec,$cmin,$chour,$cmday,$cmon,$cyear,$cwday,$cyday,$isdst) = localtime(time);
$cyear += 1900;
$cmon += 1;

$dsn = "DBI:mysql:database=gnucash;host=localhost";
$from = 'avalid@from.address';
$user = "dbuser";
$pass = "dbpass";

my $dbh = DBI->connect($dsn,
                         $user, $pass,
                         {'RaiseError' => 1});

$query = "select i.guid as guid,i.id as id,i.notes as notes,DATE_FORMAT(i.date_posted,'%e/%c/%Y') as date_posted,c.name as cname,c.addr_name as caddr_name,c.addr_addr1 as caddr1,c.addr_addr2 as caddr2,";
$query .= "c.addr_addr3 as caddr3,c.addr_addr4 as caddr4,c.addr_email as caddr_email,cast(s.value_num / s.value_denom as decimal(10,2)) as amount,DATE_FORMAT(timespec_val,'%e/%c/%Y') as datedue,";
$query .= "b.description as terms from invoices i ";
$query .= "inner join customers c on c.guid = i.owner_guid inner join splits s on i.post_txn = s.tx_guid and i.post_acc = s.account_guid ";
$query .= "inner join slots sl on i.post_txn = sl.obj_guid inner join billterms b on i.terms = b.guid";
$query .= " where sl.name='trans-date-due' and i.id=?";
my $sth = $dbh->prepare($query);
print "Looking up invoice [$opts{i}] \n";		
$sth->execute($opts{i}) or die $sth->errstr;
while (my $ref = $sth->fetchrow_hashref()) {
	print "Processing invoice [$opts{i}] \n";
	$invoice->param(CustomerName => $ref->{'cname'});
	$invoice->param(CustomerAddrName => $ref->{'caddr_name'});
	$invoice->param(CustomerAddr1 => $ref->{'caddr1'});
	$invoice->param(CustomerAddr2 => $ref->{'caddr2'});
	$invoice->param(CustomerAddr3 => $ref->{'caddr3'});
	$invoice->param(CustomerAddr4 => $ref->{'caddr4'});
	$invoice->param(InvoiceNum => $ref->{'id'});
	$invoice->param(InvoicePostDate => $ref->{'date_posted'});
	$invoice->param(InvoiceDueDate => $ref->{'datedue'});
	$invoice->param(InvoiceNotes => $ref->{'notes'});
	$invoice->param(TermDescription => $ref->{'terms'});
	
	$enquery = "select description as EntryDescription,action as EntryAction,cast(quantity_num/quantity_denom as decimal (10,2)) as EntryQuantity,cast(i_price_num/i_price_denom as decimal(10,2)) as EntryAmount,cast((i_price_num/i_price_denom) * (quantity_num/quantity_denom) * ((i_discount_num/i_discount_denom))/100 as decimal(18,2)) as EntryDiscount,cast((i_price_num/i_price_denom) * (quantity_num/quantity_denom) * coalesce((100-(i_discount_num/i_discount_denom))/100,1) as decimal(10,2)) as EntryTotal from entries ";
	$enquery .= "where invoice = ? order by date_entered";
	my $esth = $dbh->prepare($enquery);
	$esth->execute( $ref->{'guid'}) or die $esth->errstr;
	my @loop_data = ();
	while (my $eref = $esth->fetchrow_hashref()) {
		push(@loop_data, $eref);
		$invoice->param(ShowDiscount => 1) if $eref->{'EntryDiscount'} > 0;
	}
	$esth->finish();
	$invoice->param(Entries => \@loop_data);
	$invoice->param(TotalDue => $ref->{'amount'});
	open(my $fh, ">", $folder."/gen-".$invoice->param('InvoiceNum').".html") 
	or die "cannot open file: $!";
	$invoice->output(print_to => $fh);
	@args = ("/usr/local/bin/pisa", $folder."/gen-".$invoice->param('InvoiceNum').".html", $folder."/gen-".$invoice->param('InvoiceNum').".pdf");
	system(@args) == 0 or die "system @args failed: $?";
	if (defined $opts{v}){
		print "Opening viewer for invoice [$opts{i}]\n";
		@args = ("xdg-open",$folder."/gen-".$invoice->param('InvoiceNum').".pdf");
		system(@args) == 0 or die "system @args failed: $?" ;
	}
	if (defined $opts{e} or defined $opts{t}) {
		$email = $opts{t} || $ref->{'caddr_email'};
		if (length($email)>0){
			print "Emailing invoice [$opts{i}] to $email\n";
			my %sendparams;
			$ref->{'caddr_name'} =~ /(\w*)/;
			$sendparams{who} = $1 || $ref->{'cname'};
			$sendparams{invoice_id} = $ref->{'id'};
			$sendparams{duedate} = $ref->{'datedue'};
			my %options;
			$options{INCLUDE_PATH} = $folder;
			my $body = MIME::Lite::TT::HTML->new(
				Type =>'multipart/alternative',
				Template => {
        	                        html    => 'invoice.html.tt',
					text	=> 'invoice.txt.tt'
                        	},
			 	TmplOptions => \%options,
	                	TmplParams => \%sendparams
			);
			my $msg = MIME::Lite->new(
			From => $from,
			To => $email,
			Subject => $nomail.'New Invoice for payment: '.$invoice->param('InvoiceNum').'. Payment Due Date: '.$ref->{'datedue'},
			Type => 'multipart/mixed'
			);
			$msg->attach($body);
			$msg->attach(  Type	=>  'application/pdf',
                	Id	=> 'invoice.pdf',
			Path        =>  $folder.'/gen-'.$opts{i}.'.pdf',
        	        Filename    =>  'Invoice'.$invoice->param('InvoiceNum').'.pdf',
                	Disposition =>  'attachment'
	 		); 
			$msg->send;	
		} else {
			print "Unable to send email for invoice [$opts{i}]. Email address missing\n";
		}
	}
}
$sth->finish();
$dbh->disconnect();

invoice.html.tt

<html>
<head>
<style type="text/css">
body {font-family:Verdana, Arial,Sans-serif;
	font-size:10pt;
}
</style>
</head>
<body>
Hi [% who %],<br/>
<p>
Please find attached Invoice number [% invoice_id %]. 
We would appreciate payment by [% duedate %].
</p>
<p>
ACME Co thanks you for your business. Please direct
any queries to Joe Bloggs on xxxxxxxx or reply to this email.
</p>
<p>
Regards,<br/><br/>
<span style="font-weight:Bold;Color:rgb(31,73,125);">Accounts</span><br/>
<div style="Color:rgb(31,73,125);font-size:8pt;">ACME Co<br/>
1 About Way, Somewhere<br/>
Phone: xxxxxxxx<br/>
Email: reply@acme.co.local<br/>
Web: www.acme.local</div>
</body>
</html>

invoice.txt.tt

Hi [% who %],

Please find attached Invoice number [% invoice_id %]. 
We would appreciate payment by [% duedate %].

ACME Co thanks you for your business. Please direct
any queries to Joe Bloggs on xxxxxxxx or reply to this email.


Regards,
Accounts
ACME Co
1 About Way, Somewhere
Phone: xxxxxxxx
Email: reply@acme.co.local
Web: www.acme.local

invoice.tmpl

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<style type="text/css">
h3 { font-family: Ubuntu; font-size: 15pt; font-weight: bold;  }
a { font-family: Ubuntu; font-size: 10pt; font-style: italic;  }
body, p, table, tr, td { text-align: left; font-family: Ubuntu; font-size: 10pt;  }
tr.alternate-row { background: #ffffff }
th.column-heading-left { text-align: left; font-family: Ubuntu; font-size: 10pt;  }
th.column-heading-center { text-align: center; font-family: Ubuntu; font-size: 10pt;  }
th.column-heading-right { text-align: right; font-family: Ubuntu; font-size: 10pt;  }
td.neg { color: red;  }
td.number-cell, td.total-number-cell { text-align: right; white-space: nowrap; }
td.date-cell { white-space: nowrap; }
td.anchor-cell { white-space: nowrap; font-family: Ubuntu; font-size: 10pt;  }
td.number-cell { font-family: Ubuntu; font-size: 10pt;  }
td.number-header { text-align: right; font-family: Ubuntu; font-size: 10pt;  }
td.text-cell { font-family: Ubuntu; font-size: 10pt;  }
td.total-number-cell { font-family: Ubuntu; font-size: 12pt; font-weight: bold;  }
td.total-label-cell { font-family: Ubuntu; font-size: 12pt; font-weight: bold;  }
td.centered-label-cell { text-align: center; font-family: Ubuntu; font-size: 12pt; font-weight: bold;  }
</style><title>
</title></head>

<body bgcolor="#ffffff">
<h3></h3>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
	<tr>
	<td width="20%" rowspan="1" colspan="1"><font size="+2" >ACME Co/font>
	</td>

	<td width="35%" align="center" rowspan="1" colspan="1"></td>

	<td align="right" width="45%" rowspan="1" colspan="1"><font size="+2" >Invoice</font>
	</td>
	</tr>

	<tr>
	<td>1 About Way<br>Somewhere<br>ABN: xx xxx xxx xxx</td>

	<td width="35%" align="center">Phone: xxxxxxxxxx</td>

	<td width="45%" align="right">Web: www.acme.local</td>
	</tr>
</table>
<br />
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top" width="50%">
	<font size="+2" ><TMPL_VAR Name="CustomerName"></font><br/>
	<TMPL_VAR Name="CustomerAddrName"><br/>
	<TMPL_VAR Name="CustomerAddr1"><br/>
	<TMPL_VAR Name="CustomerAddr2"><br/>
	<TMPL_VAR Name="CustomerAddr3"><br/>
	<TMPL_VAR Name="CustomerAddr4"/>
</td>


<td width="50%" align="right" valign="top">
	<table cellpadding="0" cellspacing="0" border="0" width="100%">
	<tr>
	<td width="50%" align="right" valign="top">Invoice #</td>

	<td width="50%" align="right" valign="top"><TMPL_VAR Name="InvoiceNum"></td>
	</tr>

	<tr>
	<td width="50%" align="right" valign="top">Invoice Date: </td>

	<td width="50%" align="right" valign="top"><TMPL_VAR Name="InvoicePostDate"></td>
	</tr>

	<tr>
	<td width="50%" align="right" valign="top">Due Date: </td>

	<td width="50%" align="right" valign="top"><TMPL_VAR Name="InvoiceDueDate"></td>
	</tr>
	</table>
</td>
</tr>
</table>

Terms: <TMPL_VAR Name="TermDescription">
<br />
<table width="100%" border="1" cellspacing="0" cellpadding="4">
<tr>
<th>Description</th>

<th>Charge Type</th>

<th>Quantity</th>

<th>Unit Price</th>
<TMPL_IF ShowDiscount>
<th>Discount</th>
</TMPL_IF>
<th>Total</th>
</tr>
<TMPL_LOOP NAME="Entries">
<tr>
<td width="60%"><TMPL_VAR Name="EntryDescription"></td>

<td><TMPL_VAR Name="EntryAction"></td>

<td class="number-cell" rowspan="1" colspan="1"><TMPL_VAR Name="EntryQuantity"></td>

<td class="number-cell" rowspan="1" colspan="1">$<TMPL_VAR Name="EntryAmount"></td>

<TMPL_IF ShowDiscount>
<td class="number-cell" rowspan="1" colspan="1">$<TMPL_VAR Name="EntryDiscount"></td>
</TMPL_IF>
<td class="number-cell" rowspan="1" colspan="1">$<TMPL_VAR Name="EntryTotal"></td>
</tr>
</TMPL_LOOP>

<tr bgcolor="#ffffff">
<TMPL_IF ShowDiscount>
<td class="total-label-cell" rowspan="1" colspan="5">Amount Due</td>
<TMPL_ELSE>
<td class="total-label-cell" rowspan="1" colspan="4">Amount Due</td>
</TMPL_IF>
<td class="total-number-cell" rowspan="1" colspan="1">$<TMPL_VAR Name="TotalDue"></td>
</tr>
</table>
<br />
<div style="font-size:8pt;"><TMPL_VAR Name="InvoiceNotes"></div>
<br />
<span style="font-size:+2;font-weight:bold;">Payment Details</span>
<br />
<br /></body>
</html>
Share and Enjoy:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Twitter
  • Google Bookmarks

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA

*