Clark's Tech Blog

OK, last time I introduced how to access the database of GrandTotal. Half the fun is figuring out how the author put together his database. I have to say that some of it isn’t how I’d have done it, but thus far it hasn’t been too hard to figure out. The author put in his invoice item tables rows that identify an invoice as a whole and then other rows that are the items in the invoice. I think that would have been cleaner and easier to have two separate tables. It’d certainly have made the SQL easier.

To demonstrate getting data out I wrote a small program that goes through all the clients, finds the invoices for each, and then searches through the items in each invoice summing up the invoice total.

Now this wasn’t an exercise. I actually had a need to do this but exclude certain types of fees. My invoices contain the items I ship and then shipping charges. However if I’m trying to figure out profits I can’t include shipping since I don’t make any money on shipping. You might not have this particular need, but I think it demonstrates the principle of how to utilize a script in a helpful way.

If you are going to use this as a template, I’d suggest adding in the pretty printer from the prior day as well. It’s invaluable for debugging.

Now these functions get tasked to other uses at times. Following my scripting principles I’ve made them a bit broader than I need for this particular application.

(Note you may have to scroll to the right to see some of the lines)

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

#!/usr/bin/python
 
import sqlite3
 
database = "/Users/me/Scripts/invoices/GrandTotal.db"
 
# Gets the list of client numbers, company name, and contact name
 
def getclients(cursor):
    cursor.execute('''select Z_PK, ZORGANIZATION, ZFIRSTNAME, ZLASTNAME from ZCLIENT''')
    clients =  []
    for row in cursor:
        clients.append(row)
    #print clients
    return clients
 
# Gets the particular invoice items for a particular invoice id
# Returns the invoice total
 
def getinvoiceitems( cursor, id):
    t = (id,)
 
    cursor.execute("select Z_PK, ZNAME, ZUID, ZRATE, 1- 0.01 * ZDISCOUNT, ZQUANTITY  from ZINVOICEITEM where ZPARENTURI = ?" , t)
    items = []
    total = 0
    for row in cursor:
        #print row
        if row[1] != u'Shipping':
            items.append(row)
 
            # price * 1-.01*discount * quantity
            total = total + row[3]*row[4]*row[5]
 
    #print "    TOTAL: " + str(total)
    return total
 
# Gets all the invoices associated with each client
# Prints a report of each client
 
def getmasterinvoice( cursor, clients ):
 
    for cl in clients:
        clientid = cl[0]
        scid = str(cl[0])
        company = ""  if cl[1] == None else cl[1]
        name = "" if cl[2] == None else cl[2]
        name = name if cl[3] == None else name + " " + cl[3]
 
        #if clientid != 48: continue
 
        print 
        print scid + "  " + company + "   " + name
        print "-----------------------------------------------------------------------"
 
        t = (clientid,)
        cursor.execute("select Z_PK, ZUID, ZCLIENT from ZINVOICEITEM where ZCLIENT = ?", t)
        invoices = cursor.fetchall()
        total=0
        for i in invoices:
            total = total + getinvoiceitems(cursor, i[1])
        print "Client Total: " + str(total)
 
def main():
    conn = sqlite3.connect(database)
 
    cursor = conn.cursor()
 
    clients = getclients( cursor )
    getmasterinvoice( cursor, clients )
 
    conn.close()
 
if __name__ == '__main__':
	main()

A few comments.

First note the single line if statements on lines 45-47. Those are new with Python 2.5 and I really, really like them for when your checking if some field is None. (Primarily when concatenating strings) If you aren’t using them you might consider starting.

The first thing we do is get a list of clients and their unique ids. We then get all the “master invoices” associated with each client. This is where the database structure is a bit annoying since the master invoice and the invoice items are all in the same table. (I’m sure there was some programming reason this was done though) Once you have the master invoice identifier (the ZUID) you then look up the other items. (Where ZUID of the master is equal to ZPARENTURI)

In my case I simply do a check and only update the total if the item name is not Shipping. You should hopefully be able to see how you cold make this much more complicated.

The only tricky part is how each function is nested in an other. You might be tempted to simply call the narrower function within the innerloop (line 60 for instance) rather than calling fetchall() from the cursor (line 57). The thing to remember is that if you reuse a Cursor (use a new execute command) then you lose its prior state. Generally it’s easier to just get all the data at once. The databases are fairly small and memory is cheap now.

Related posts:

1. Scripting GrandTotal Part 1
2. Count Your iPhone App Usage