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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
#
# 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 postgresqleu.mailqueue.util import send_simple_mail
from postgresqleu.util.time import today_global
from .models import JournalEntry, JournalItem, JournalUrl
from .models import Object, Account, Year
class AccountingException(Exception):
pass
def create_accounting_entry(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 *name* 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
date = today_global()
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 ({0})!".format(r[2]))
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:
# If the year simply doesn't exist, we create one and send an alert about it.
# This will handle the case of automated entries showing up very early in the year when
# nobody has had time to deal with it manually yet.
year = Year(year=date.year, isopen=True)
year.save()
send_simple_mail(
settings.INVOICE_SENDER_EMAIL,
settings.INVOICE_NOTIFICATION_RECEIVER,
"Accounting year {} created".format(year.year),
"""An accounting entry for non-existing year {0} arrived,
so the year was automatically created and the entry added.
If this is in error, you will have to go remove the entry
and the year manually!
""".format(year.year),
)
if not year.isopen:
# If the year exists but is closed, then it's actually an error.
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[:200]).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)
return entry
except Exception as e:
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]
def get_account_choices():
return [(a.num, str(a)) for a in Account.objects.all()]
|
