Hats off to the GnuCash developers and the Parit Worker Collective for the python bindings to GnuCash. If there is any doubt that free software makes life easier it's being able to write your own accounting import scripts while reading the original source code.
I had to re-compile Debian's version of GnuCash because it doesn't have the python bindings enabled by default.
As root:
apt-get build-dep gnucash
apt-get install python-dev
As a non privileged user:
apt-get source gnucash
cd gnucash-N.N.N # varies depending on your version)
Then, edit debian/rules and add --enable-python-bindings to the dhautoconfigure command. I replaced the line:
--enable-locale-specific-tax
With the lines:
--enable-locale-specific-tax \
--enable-python-bindings
Then, update the change log with:
dch
Built the new package:
dpkg-buildpackage -rfakeroot -uc -b
I then wrote a python module. It logs into the server running our members database, runs a script to export tab delimited lists, and then imports them into our gnucash xml file.
It's far from perfect (I'm fairly new to python). Also - I have sheepishly inserted code that searches for certain values by accessing the gnucash file directly (via python's xml handling modules). I couldn't figure out how to do the search I needed with they available python methods, although it may very well be there and I just couldn't figure it out.
If anyone is interested in using it for a template:
#!/usr/bin/python
import sys
import os
import time
import csv
import warnings
from xml.dom.minidom import parse
from gnucash import Session, GncNumeric
from gnucash.gnucash_business import Customer, Invoice, Entry, BillTerm
from decimal import Decimal
import datetime
import tempfile
import subprocess
def main(argv = None):
if argv is None:
argv = sys.argv
path = os.environ['MFPL_GNUCASH_PATH']
if not os.path.exists(path):
print """Path (%s) does not exist. Please set
MFPL_GNUCASH_PATH environment variable\n""" % path
return 1
if os.path.exists(path + '.LCK'):
print """Lock file exists. Is GNUCash running?\n"""
return 1
command = argv[1]
import_file = None
if 2 in argv:
if argv[2] == '--from-file':
if argv[3]:
if os.path.exists(argv[3]):
file_path = argv[3]
import_file = open(file_path,'r')
else:
print "Can't find file to import: %s" % argv[3]
return 1
else:
print "Please pass path to file if using --from-file"
return 1
else:
print "I didn't understand the argument: %s" %s
# download import file if not provided
if import_file == None:
import_file = get_data(command)
# The file might be gzipped, so
# open and save it
s = get_gnucash_session(path)
s.save()
s.end()
# Wait a second, so we can create a backup
# without a name collision
time.sleep(1)
# Open for real now...
s = get_gnucash_session(path)
xml_dom = parse(path)
if command == "members":
ret = import_members_from_file(s, xml_dom, import_file)
if command == "invoices":
ret = import_invoices_from_file(s, xml_dom, import_file)
s.save()
s.end()
return ret
def get_data(command):
import_file = tempfile.NamedTemporaryFile()
if command == 'members':
remote_cmd = 'acc-member-list'
else:
remote_cmd = 'acc-invoice-list'
args = [ '/usr/bin/ssh', 'mayfirst@hay.mayfirst.org', '/home/mayfirst/bin/' + remote_cmd ]
subprocess.call(args,stdout = import_file)
return import_file
def import_invoices_from_file(s, xml_dom, csvfile):
dialect = 'excel-tab'
csvfile.seek(0)
reader = csv.reader(csvfile, dialect)
nodes = xml_dom.getElementsByTagName('gnc:GncCustomer')
already_entered = 0
entered = 0
for row in reader:
member_id = row[0]
invoice_id = row[1]
invoice_date = row[2]
invoice_amount = row[3]
invoice_desc = row[4]
if not member_id.isdigit():
# header line or not coded to a member?
continue
member_id = int(member_id)
invoice_id = int(invoice_id)
if invoice_exists(xml_dom, invoice_id):
# print "Already entered %s (%s)" % (invoice_date,invoice_id)
already_entered += 1
else:
if not member_exists(xml_dom, "No Name McGoo - fix me" , member_id):
print "Skipping member id %s, invoice id %s - member doesn't exist" % (member_id, invoice_id)
else:
invoice_enter(s, member_id, invoice_id, invoice_date, invoice_amount, invoice_desc )
entered += 1
print "Invoices entered from last two months: %s (%s already existed)" % (entered, already_entered)
return True
def get_guid_from_member_id(nodes,member_id):
member_id = pad_number(member_id)
for node in nodes:
for child in node.childNodes:
if child.nodeName == 'cust:guid':
guid = child.childNodes[0].data
if child.nodeName == 'cust:id':
if member_id == child.childNodes[0].data:
return guid
def import_members_from_file(s, dom, csvfile):
dialect = 'excel-tab'
csvfile.seek(0)
reader = csv.reader(csvfile, dialect)
already_entered = 0
entered = 0
for row in reader:
member = row[0]
member_id = row[1]
if not member_id.isdigit():
# header line?
continue
member_id = int(member_id)
if member_exists(dom, member, member_id):
#print "Already entered %s (%s)" % (member, member_id)
already_entered += 1
else:
member_enter(s, member, member_id )
entered += 1
print "Members entered from last two months: %s (%s already existed)" % (entered, already_entered)
return True
def get_gnucash_session(path):
with warnings.catch_warnings():
warnings.simplefilter("ignore")
return Session(path, is_new = False)
def member_enter(s, member, member_id):
print "Entering %s with id %s" % (member, member_id)
book = s.book
root = book.get_root_account()
commod_table = book.get_table()
USD = commod_table.lookup('CURRENCY', 'USD')
member_id = pad_number(member_id)
new_customer = Customer(book, member_id, USD, member)
def invoice_enter(s, member_id, invoice_id, invoice_date, invoice_amount, invoice_desc):
book = s.book
root = book.get_root_account()
commod_table = book.get_table()
USD = commod_table.lookup('CURRENCY', 'USD')
invoice_id = pad_number(invoice_id)
member_id = pad_number(member_id)
invoice_date_object = datetime.datetime.strptime(invoice_date, "%Y-%m-%d %H:%M:%S")
member = book.CustomerLookupByID(member_id)
assert( member != None )
assert( isinstance(member, Customer) )
assets = root.lookup_by_name("Assets")
recievables = assets.lookup_by_name("Recievables")
income = root.lookup_by_name("Membership Dues")
invoice = Invoice(book, invoice_id, USD, member, invoice_date_object )
invoice_value = gnc_numeric_from_decimal(Decimal(invoice_amount))
invoice_entry = Entry(book, invoice)
invoice_entry.SetDescription(invoice_desc)
invoice_entry.SetQuantity( GncNumeric(1) )
invoice_entry.SetInvAccount(income)
invoice_entry.SetInvPrice(invoice_value)
invoice_entry.SetDate(invoice_date_object)
invoice_entry.SetDateEntered(invoice_date_object)
print "Entering invoice %s %s %s" % (invoice_id, invoice_date, invoice_amount)
invoice.PostToAccount(recievables, invoice_date_object, invoice_date_object, "", True)
def pad_number(num):
return "%(number)06d" % { 'number': num }
def member_exists(dom, member, member_id):
member_id = pad_number(member_id)
for node in dom.getElementsByTagName('gnc:GncCustomer'):
for child in node.childNodes:
if child.nodeName == 'cust:name':
for grandchild in child.childNodes:
if member == grandchild.data:
return True
if child.nodeName == 'cust:id':
for grandchild in child.childNodes:
if member_id == grandchild.data:
return True
return False
def invoice_exists(dom, invoice_id):
invoice_id = pad_number(invoice_id)
for node in dom.getElementsByTagName('gnc:GncInvoice'):
for child in node.childNodes:
if child.nodeName == 'invoice:id':
for grandchild in child.childNodes:
if invoice_id == grandchild.data:
return True
return False
def gnc_numeric_from_decimal(decimal_value):
sign, digits, exponent = decimal_value.as_tuple()
# convert decimal digits to a fractional numerator
# equivlent to
# numerator = int(''.join(digits))
# but without the wated conversion to string and back,
# this is probably the same algorithm int() uses
numerator = 0
TEN = int(Decimal(0).radix()) # this is always 10
numerator_place_value = 1
# add each digit to the final value multiplied by the place value
# from least significant to most sigificant
for i in xrange(len(digits)-1,-1,-1):
numerator += digits[i] * numerator_place_value
numerator_place_value *= TEN
if decimal_value.is_signed():
numerator = -numerator
# if the exponent is negative, we use it to set the denominator
if exponent < 0 :
denominator = TEN ** (-exponent)
# if the exponent isn't negative, we bump up the numerator
# and set the denominator to 1
else:
numerator *= TEN ** exponent
denominator = 1
return GncNumeric(numerator, denominator)
if __name__ == "__main__":
sys.exit(main())