Clark's Tech Blog

GrandTotal is my invoicing program of choice. I’ve used quite a few others but this is my favorite. For one the developer is very quick about feedback. For an other, it uses a lot of open standards that makes it easy to expand the program’s weaknesses.

There are a lot of reasons why you’d want to script GrandTotal. (Sadly it doesn’t support Applescript – so there’s a lot more I’d like to script but can’t – but it’s more open than the other programs I’ve tried) One problem I have is that the program doesn’t do a lot of analysis reports. It’s great for generating invoices. Not terribly good for things like Aged Reports or client summation reports.

My first job out of college was doing IT for a multimillion dollar company. (I was the entire IT department and handled more of the accounting than I expected) Half of what I did was hack into their accounting program to create custom data and reports using PowerBuilder, FoxPro, Access and Excel. Effectively that’s what I’m doing here with GrandTotal and Python. But it’s quite easy.

Over the next few posts I’ll show how to figure out the database structure, how to get data from the database, on up to doing some simple reports.

GrandTotal uses SQLite for its databases. Fortunately Python has SQLite access built in. I found it fairly helpful to use the shell version of the SQLite program as well. (There’s a good tutorial here)

The first thing to do is make a copy of the database you’re working with. I created a directory called ~/Scripts/Invoice for my programs. I copied the database with a

cp ~/Library/Application\ Support/GrandTotal/Data.grandtotal/GrandTotal.db ~/Scripts/Invoice

You can do this with the Finder if you’d like, but note that you first have to open the GrandTotal Package with Show Package Contents. It’s one of those bundles from NeXT where a folder appears as a file. It’s honestly just easier with the shell.

Before starting our Python program let’s open the database up to examine it. You’ll have to see the tables in it and their structure before scripting anyway.

sqlite3 ~/Scripts/Invoice/GrandTotal.db

You’ll be in the administrator program where you can enter SQL commands. Start by listing the tables. To make life easier I’ll put what I type in bold and the output in regular.

.table
ZCATEGORY      ZENTRY         ZPAYMENTTYPE   ZTAXGROUP      Z_METADATA
ZCLIENT        ZFOLLOWUPTYPE  ZPREFERENCES   ZTAXRULE       Z_PRIMARYKEY
ZCURRENCY      ZINVOICEITEM   ZSLIP          ZTAXTYPE
ZDESIGN        ZPAYMENT       ZTAX           ZTIMELOGRULE

To see the client info do the following:

select * from ZCLIENT;

For obvious privacy reasons I’ll not show you the output. But you get the idea.

You’ll probably want to know the fields in each table. The schema command does that. It basically lists the commands used to create the table and is very nice for figuring out what is going on.

.schema ZClient

I’ll leave the rest as an exercise to the reader and move on to Python now. I should note a very useful little function from the Python Cookbook. It’s a database pretty printer. It prints not only the output of your SQL select query but headers for each field. Unfortunately the version in the book is kind of dated. (Python Cookbook was a great book, but half the modules it used are depreciated now) In this case the pretty printer crashes on unicode data.

Here’s a modified version. I strongly suggest putting it into your program for debugging use. (Or just for trying to figure out some SQLite table)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

def pp(cursor, rows=None, rowlens=0):
    d = cursor.description
    if not d:
        return "None"
    fields = []
    lengths = []
    rules = []
 
    # if not given rows, get them
    if not rows:
        t = cursor.fetchall()
 
    # go through each field description and save them and their lengths
    for dd in d:   
        l = dd[1]
        if not l:
            l = 12             # or default arg ...
        l = max(l, len(dd[0])) # handle long names
        fields.append(dd[0])
        lengths.append(l)
    for col in range(len(lengths)):
        if rowlens:
            rls = []
            for row in rows:
                if row[col]:
                    if type(row[col]) == unicode or type(row[col]) == str:
                        rls.append(len(row[col]))
                    else:
                        rls.append(len(str(row[col])))
            lengths[col] = max([lengths[col]]+rls)
        rules.append("-"*lengths[col])
    format = " ".join(["%%-%ss" % l for l in lengths])
    result = [format % tuple(fields)]
    result.append(format % tuple(rules))
    for row in rows:
        result.append(format % row)
    return "\n".join(result)

I can’t say I’ve tested it under every circumstance. But it definitely lets you pretty print your data now since most Mac applications use unicode. You use it like this:

1
2
3
4
5
6
7
8
9
10

import sqlite3
 
database = "/Users/me/Scripts/invoices/GrandTotal.db"
 
conn = sqlite3.
cursor = conn.cursor()
cursor.execute('''select Z_PK, ZORGANIZATION, ZFIRSTNAME, ZLASTNAME from ZCLIENT''')
rows = cursor.fetchall()
print pp(cursor, rows, 1)
conn.close()

The third argument, as in the original, just tells whether to format into a block appearance the table. 99% of the time you’ll want to do that. But try leaving that field out to leave it unjustified.

Play around with it. You might actually find it’s easier to figure out the database structure from within Python than with the shell sql program.

Next time I’ll show a small program to access the GrandTotal tables.

Related posts:

1. Scripting GrandTotal Part 2
2. CSS Table Design
3. Proximity Address Search Script Revisited
4. Sending Data to Windows
5. Address Book to Numbers
6. Cleaning iTunes Pt. 3: Album Names