import mysql.connector as mariadb
import csv
import ConfigParser
import datetime
def configSectionMap(section):
# Read configuration file
dict1 = {}
options = cfgfile.options(section)
for option in options:
try:
dict1[option] = cfgfile.get(section, option)
if dict1[option] == -1:
print("skip: %s" % option)
except:
print("exception on %s!" % option)
dict1[option] = None
return dict1
def findParent (parent_guid):
# Find the parent account for given GUID reference
if parent_guid is None:
acctname = 'ROOT'
else:
cursor2 = mariadb_connection2.cursor()
cursor2.execute('select name as acctname, parent_guid, guid from accounts where guid = %s', (parent_guid,))
results = cursor2.fetchone()
parent_guid = results[1]
acctname = results[0]
if acctname != 'Root Account':
acctparentname = findParent(parent_guid)
if len(acctparentname) > 0:
acctname = acctparentname + '.' + acctname
else:
acctname = ''
cursor2.close()
return acctname
cfgfile = ConfigParser.ConfigParser()
cfgfile.read('gnucash.ini')
mariadb_connection1 = mariadb.connect(
host = configSectionMap('database')['host'],
user = configSectionMap('database')['user'],
password = configSectionMap('database')['password'],
database = configSectionMap('database')['database']
)
mariadb_connection2 = mariadb.connect(
host = configSectionMap('database')['host'],
user = configSectionMap('database')['user'],
password = configSectionMap('database')['password'],
database = configSectionMap('database')['database']
)
cursor1 = mariadb_connection1.cursor()
if __name__ == "__main__":
# Collect a dictionary of account categories
dictCats = {}
cursor1.execute('select name as acctname, parent_guid, guid from accounts where account_type not in("ROOT")')
for acctname, parent_guid, guid in cursor1:
rootcat = findParent(parent_guid)
if rootcat[5:18] != 'Template Root' and acctname != 'Template Root' and acctname != 'Root Account':
if len(rootcat) > 0:
dictCats[guid] = rootcat + '.' + acctname
else:
dictCats[guid] = acctname
cursor1.execute("""SELECT
accts.guid,
accts.name as acctname,
txns.post_date,
txns.description,
spl.memo,
format(spl.value_num / spl.value_denom, 2) AS dollar_value
FROM
accounts AS accts,
transactions AS txns,
splits AS spl
WHERE
accts.guid = spl.account_guid
AND txns.guid = spl.tx_guid
AND txns.post_date BETWEEN '%s' AND '%s'
AND accts.name NOT IN (%s)
AND txns.description NOT IN (%s)
ORDER by
txns.post_date ASC
;""" % (configSectionMap('run_time')['begin_date']
, configSectionMap('run_time')['end_data']
, configSectionMap('exclude_accounts')['exclude_acct_list']
, configSectionMap('exclude_descriptions')['exclude_txns_descriptions']))
dictValues = {}
dictIndex = 0
for guid, acctname, post_date, description, memo, dollar_value in cursor1:
dictIndex += 1
dictValues[dictIndex] = {'post_date':post_date.strftime('%Y.%m.%d'), 'description':str(description), 'category':str(dictCats[guid]), 'dollar_value':"{0:.2f}".format(float(dollar_value.replace(',', '')))}
sorted_keys = sorted(dictValues, key=lambda x:(dictValues[x]['category'], dictValues[x]['post_date']))
exportcsv1 = open('export_report.csv', 'wb')
exportcsv2 = open('export_data.csv', 'wb')
exportcsv3 = open('export_subtotals.csv', 'wb')
wr1 = csv.writer(exportcsv1, dialect='excel', quoting=csv.QUOTE_ALL)
wr2 = csv.writer(exportcsv2, dialect='excel', quoting=csv.QUOTE_ALL)
wr3 = csv.writer(exportcsv3, dialect='excel', quoting=csv.QUOTE_ALL)
wr3.writerow(['Start Date: %s' % datetime.datetime.strptime(configSectionMap('run_time')['begin_date'], '%Y-%m-%d %H:%M:%S').strftime('%B %d, %Y')])
wr3.writerow(['End Date: %s' % datetime.datetime.strptime(configSectionMap('run_time')['end_data'], '%Y-%m-%d %H:%M:%S').strftime('%B %d, %Y')])
wr3.writerow(['Category', 'Subtotal'])
strCurrentCat = ''
strCurrentCatTotal = 0.00
for k in sorted_keys:
if strCurrentCat != dictValues[k]['category']:
if len(strCurrentCat) > 0:
# This is a category change, so capture the change
# and print the prior category subtotal
wr1.writerow(['', '', 'Subtotal:', "{0:.2f}".format(float(strCurrentCatTotal))])
wr1.writerow(['', '', '', ''])
wr3.writerow([strCurrentCat, "{0:.2f}".format(float(strCurrentCatTotal))])
strCurrentCat = dictValues[k]['category']
strCurrentCatTotal = float(dictValues[k]['dollar_value'])
else:
strCurrentCatTotal = strCurrentCatTotal + float(dictValues[k]['dollar_value'])
wr1.writerow([dictValues[k]['post_date'], dictValues[k]['description'], dictValues[k]['category'], dictValues[k]['dollar_value']])
wr2.writerow([dictValues[k]['post_date'], dictValues[k]['description'], dictValues[k]['category'], dictValues[k]['dollar_value']])
# Print the remaining record in the results buffer
wr1.writerow(['', '', 'Subtotal:', "{0:.2f}".format(float(strCurrentCatTotal))])
mariadb_connection1.close()
print('Script completed.')