GnuCash  4.11-243-g1cac132214+
qof.py
Go to the documentation of this file.
1 #!/usr/bin/env python3
2 
3 
9 
10 import datetime
11 from random import randint
12 from gnucash import Session, SessionOpenMode, Account, Transaction, Split, \
13  GncNumeric, Query
14 from gnucash import QOF_COMPARE_GTE, QOF_COMPARE_GT, QOF_QUERY_AND, \
15  QOF_QUERY_OR, QOF_STRING_MATCH_NORMAL, QOF_COMPARE_CONTAINS, \
16  QOF_NUMERIC_MATCH_ANY
17 from gnucash import gnucash_core_c
18 from gnucash import gnucash_core
19 
20 # We need to tell GnuCash the data format to create the new file as (xml://)
21 uri = "xml:///tmp/qof.gnucash"
22 
23 def createAccounts(book):
24  root_account = book.get_root_account()
25  commodtable = book.get_table()
26  currency = commodtable.lookup("CURRENCY", "EUR")
27  ses.save()
28 
29  print('Create two accounts ("Account A", "Account B")')
30  accountA = Account(book)
31  accountA.SetCommodity(currency)
32  accountA.SetName("Account A")
33  root_account.append_child(accountA)
34 
35  accountB = Account(book)
36  accountB.SetCommodity(currency)
37  accountB.SetName("Account B")
38  root_account.append_child(accountB)
39 
40  #ses.save()
41 
42  return accountA, accountB
43 
44 def createRandomTransactions(book, accountA, accountB):
45  split = Split(book)
46 
47  currency = book.get_table().lookup("CURRENCY", "EUR")
48 
49  print("Create 100 random transactions")
50  for i in range(100):
51 
52  trans = Transaction(book)
53  trans.BeginEdit()
54  trans.SetCurrency(currency)
55  trans.SetDate(randint(1,28), randint(1,12), randint(1900,2000))
56  trans.SetDescription("Transaction "+str(i))
57 
58  value = randint(0,10000)
59 
60  value1 = GncNumeric(value, 100)
61  value2 = GncNumeric(-value, 100)
62 
63  split1 = Split(book)
64  split1.SetValue(value1)
65  split1.SetAccount(accountA)
66  split1.SetMemo("A" + str(i))
67  split1.SetParent(trans)
68 
69  split2 = Split(book)
70  split2.SetValue(value2)
71  split2.SetAccount(accountB)
72  split2.SetMemo("B" + str(i))
73  split2.SetParent(trans)
74 
75  trans.CommitEdit()
76 
77 def query_transactions(book, terms=[]):
78 
79  query = Query()
80  query.search_for('Trans')
81  query.set_book(book)
82 
83  if terms:
84  for term in terms:
85  query.add_term(*term)
86 
87  transactions = []
88 
89  for transaction in query.run():
90  transaction = Transaction(instance=transaction) # ToDo: query.run() should return objects
91  transactions.append(transaction)
92 
93  query.destroy()
94  return transactions
95 
96 def query_splits(book, terms=[]):
97 
98  query = Query()
99  query.search_for('Split')
100  query.set_book(book)
101 
102  if terms:
103  for term in terms:
104  query.add_term(*term)
105 
106  splits = []
107 
108  for split in query.run():
109  split = Split(instance=split) # ToDo: query.run() should return objects
110  splits.append(split)
111 
112  query.destroy()
113  return splits
114 
115 with Session(uri, SessionOpenMode.SESSION_NEW_STORE) as ses:
116 
117  book = ses.get_book()
118  accountA, accountB = createAccounts(book)
119  createRandomTransactions(book, accountA, accountB)
120 
121  # TRANSACTIONS
122  #
123  # get all transactions
124  transactions_all = query_transactions(book)
125  print("Query all: " + str(len(transactions_all)) + " transactions.")
126 
127  # query date
128  threshold = datetime.datetime(1950,1,1)
129  QOF_DATE_MATCH_NORMAL = 2
130  terms = [(['date-posted'], gnucash_core.QueryDatePredicate(QOF_COMPARE_GTE, QOF_DATE_MATCH_NORMAL, threshold), QOF_QUERY_AND)]
131  transactions_2 = query_transactions(book, terms)
132  print("Query transactions with date > 1950: " + str(len(transactions_2)) + " (Should be about 50).")
133 
134  # query description
135  isRegex = False
136  terms = [(['desc'], gnucash_core.QueryStringPredicate(QOF_COMPARE_CONTAINS, "Transaction 5", QOF_STRING_MATCH_NORMAL, isRegex), QOF_QUERY_AND)]
137  transactions_3 = query_transactions(book, terms)
138  print("Query transaction with description containing 'Transaction 5': " + str(len(transactions_3)) + " (Should be 11).")
139 
140  # SPLITS
141  #
142  # query memo
143  isRegex = False
144  terms = [(['memo'], gnucash_core.QueryStringPredicate(QOF_COMPARE_CONTAINS, "A22", QOF_STRING_MATCH_NORMAL, isRegex), QOF_QUERY_AND)]
145  splits_1 = query_splits(book, terms)
146  print("Query splits with memo containing 'A22': " + str(len(splits_1)) + " (Should be 1).")
147 
148  # query description
149  isRegex = False
150  terms = [(['trans', 'desc'], gnucash_core.QueryStringPredicate(QOF_COMPARE_CONTAINS, "Transaction 5", QOF_STRING_MATCH_NORMAL, isRegex), QOF_QUERY_AND)]
151  splits_2 = query_splits(book, terms)
152  print("Query splits with transaction description containing 'Transaction 5': " + str(len(splits_2)) + " (Should be 22).")
153 
154  # query memo and desc
155  isRegex = False
156  terms = [(['memo'], gnucash_core.QueryStringPredicate(QOF_COMPARE_CONTAINS, "A22", QOF_STRING_MATCH_NORMAL, isRegex), QOF_QUERY_AND)]
157  terms += [(['trans', 'desc'], gnucash_core.QueryStringPredicate(QOF_COMPARE_CONTAINS, "Transaction 55", QOF_STRING_MATCH_NORMAL, isRegex), QOF_QUERY_OR)]
158  splits_4 = query_splits(book, terms)
159  print("Query splits with memo containing 'A22' or transaction desc containing 'Transaction 55': " + str(len(splits_4)) + " (Should be 3).")
160 
161  # query split value
162  threshold = GncNumeric(5000, 100)
163  terms = [(["amount"], gnucash_core.QueryNumericPredicate(QOF_COMPARE_GT, QOF_NUMERIC_MATCH_ANY, threshold), QOF_QUERY_AND)]
164  splits_3 = query_splits(book, terms)
165  print("Query splits with amount > " + str(threshold) + ": " + str(len(splits_3)) + " (Should be about 100).")
The primary numeric class for representing amounts and values.
Definition: gnc-numeric.hpp:59