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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
#
# These are the internal API entrypoints for the accounting system.
#
from django.db import connection, transaction
from django.db.models import Max
from django.conf import settings
from decimal import Decimal
from models import JournalEntry, JournalItem, JournalUrl
from models import Object, Account, Year
class AccountingException(Exception):
pass
def create_accounting_entry(date,
items,
leaveopen=False,
urllist=None):
# items must be an array of tuples in the format:
# (accountnumber, descriptiontext, amount, objectname)
# Positive amounts indicate debit, negative amounts indicate credit.
# objects are referenced by *named* and looked up internally here.
# Entries must be balanced unless leaveopen is set to True
# Any urls listed in urllist must exist and be correct, no verification
# is done.
if not settings.ENABLE_AUTO_ACCOUNTING:
return
sid = transaction.savepoint()
try:
# Start by some simple validation
for r in items:
if r[2] == 0:
raise AccountingException("Submitted accounting journal entry has a zero sum entry!")
if Decimal(r[2]).as_tuple().exponent < -2:
raise AccountingException("Submitted accounting journal entry has items that are not rounded off to two decimal points!")
debitsum = sum([r[2] for r in items if r[2] > 0])
creditsum = -sum([r[2] for r in items if r[2] < 0])
if debitsum != creditsum and not leaveopen:
raise AccountingException("Submitted accounting journal entry is not balanced!")
try:
year = Year.objects.get(year=date.year)
except Year.DoesNotExist:
raise AccountingException("Year %s does not exist in the accounting system!" % date.year)
if not year.isopen:
raise AccountingException("Year %s is not open for new entries!" % date.year)
seq = JournalEntry.objects.filter(year=year).aggregate(Max('seq'))['seq__max']
if seq is None: seq = 0
# We assume the rest is correct and start building the db entries,
# since we'll just roll it back if referenced data is missing.
entry = JournalEntry(year=year, seq=seq + 1, date=date, closed=False)
entry.save()
for accountnum, description, amount, objectname in items:
try:
account = Account.objects.get(num=accountnum)
if objectname:
obj = Object.objects.get(name=objectname)
else:
obj = None
JournalItem(journal=entry,
account=account,
amount=amount,
object=obj,
description=description).save()
except Account.DoesNotExist:
raise AccountingException("Account %s does not exist!" % accountnum)
except Object.DoesNotExist:
raise AccountingException("Object %s does not exist!" % objectname)
# If there are any URLs to attach, do so now
if urllist:
for url in urllist:
JournalUrl(journal=entry, url=url).save()
# All items saved correct. Close the entry if we have to. We verified
# above that it's valid...
if not leaveopen:
entry.closed = True
entry.save()
# Ok, it seems it worked...
transaction.savepoint_commit(sid)
except:
transaction.savepoint_rollback(sid)
raise
def get_latest_account_balance(accountid):
# Start from the year with the first incoming balance (meaning that the previous year
# was closed and it was transferred) and sum up all accounting entries for the specified
# account since then. We intentionally include open items, so we can track pending transfers
# between the banks.
cursor = connection.cursor()
cursor.execute("""WITH incoming_balance(incoming_year, incoming_amount) AS (
SELECT year_id, amount FROM accounting_incomingbalance WHERE account_id=%(account)s
UNION ALL VALUES (0,0)
ORDER BY year_id DESC LIMIT 1
)
SELECT sum(amount)+COALESCE((SELECT incoming_amount FROM incoming_balance),0)
FROM accounting_journalitem ji INNER JOIN accounting_journalentry je ON ji.journal_id=je.id
WHERE account_id=%(account)s
AND je.year_id >= (SELECT incoming_year FROM incoming_balance)""",
{
'account': accountid,
})
return cursor.fetchall()[0][0]
|
