class CloseDates(db.Model):
""" This is the history of closed accounting periods.
The Close Dates have the following field:
:closing_date: The first date of the new accounting
period
"""
__tablename__ = 'closedats'
closing_date = db.Column(db.DateTime, nullable=False, primary_key=True)
def add(self):
""" Add this date to the session """
db.session.add(self)
class Journals(db.Model):
""" The journal is the way postings are delivered by clients.
The journal consists of an unknown number of postings. It must be at least
two, because the total balance (debit +, credit -) must be zero.
The journal controls the way the postings are grouped, all postings
or none are posted by the system. To that purpose the Journals
have a flag journalstat that tells the system its status.
Journals have the following fields :
:id: the system generated sequence number
:extkey: the key to the callings systems object, this is optional
:journalstat: the status of the journal
:updated_at: The timestamp of the last update
"""
__tablename__ = 'journals'
id = db.Column(db.Integer, db.Sequence('journal_id_seq'), primary_key=True)
extkey = db.Column(db.String(150), nullable=True)
journalpostings = db.relationship('Postings', backref='journal')
journalstat = db.Column(db.String(1), nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
UNPROCESSED = 'U'
PROCESSED = 'P'
FAILED = 'F'
@classmethod
def get_by_id(cls, requested_id):
""" Return the journal row for requested_id
"""
try:
journal = query(Journals).filter_by(id=requested_id).first()
if not journal:
raise NoJournalError('No journal for id ' + str(requested_id))
return journal
except NoResultFound:
raise NoJournalError('No journal for id ' + str(requested_id))
@classmethod
def create_from_dict(cls, journdict):
"""Creates a new journal including posting from
a dictionary created from json
"""
if 'postings' not in journdict['journal']\
or journdict['journal']['postings'] is None:
raise NoPostingInJournal('Empty journal')
newjournal = cls(journalstat=cls.UNPROCESSED,
extkey=journdict['journal']['extkey'])
newjournal.add()
for posting in journdict["journal"]["postings"]:
try:
Postings.create_from_dict(posting, newjournal)
except NoAccountError as exc:
raise InvalidJournalError(str(exc)) from exc
return newjournal
@classmethod
def postings_for_id(cls, journal_id):
""" Assemble the postings in journal with id journal_id
"""
posts = query(Postings).filter_by(journals_id=journal_id).all()
if posts == []:
raise NoJournalError('Journal ' + str(journal_id) +
' does not exist')
return posts
@classmethod
def get_by_key(self, extkey=None):
""" Get the journal data without postings by the supplied extkey
"""
if extkey is None:
raise NoJournalError('An external key is required')
journal = query(Journals).filter_by(extkey=extkey).first()
if not journal:
raise NoJournalError('No journal with key ' + extkey)
return journal
@classmethod
def journals_for_search(cls, search_string=None, page=1, pagelength=25):
""" Return journals that have the search string
in their key. Uphold paging attributes.
"""
if search_string is None or search_string == '':
return JournalList([],
page=page,
pagelength=pagelength,
num_records=0)
if len(search_string) < 3:
raise ShortSearchStringError('Search string ' + search_string +
' too short')
journals = query(Journals).order_by(Journals.extkey.desc())
if search_string:
journals = journals.filter(
Journals.extkey.like('%' + search_string + '%'))
if page > 1:
skip_records = (page - 1) * pagelength
journals = journals.offset(skip_records)
journals = journals.limit(pagelength)
logging.debug('Journals SQL is: :' + str(journals))
journals = journals.all()
q = query(Journals)
if search_string:
q = q.filter(Journals.extkey.like('%' + search_string + '%'))
num_records = q.count()
return JournalList(journals,
page=page,
pagelength=pagelength,
num_records=num_records)
@classmethod
def postings_for_key(self, journal_key):
""" Assemble the posting in the journal by the external key
"""
journal = query(Journals).filter_by(extkey=journal_key).first()
if not journal:
raise NoJournalError('No journal for key ' + str(journal_key))
return journal.journalpostings
@validates('journalstat')
def validate_status(self, id, journalstat):
""" Check if the status is valid
"""
if journalstat not in [self.UNPROCESSED, self.PROCESSED, self.FAILED]:
raise InvalidJournalError('Status ' + str(journalstat) +
' is invalid')
return journalstat
def add(self):
""" Add this journal to the session
Make sure it is timestamped
"""
self.updated_at = datetime.today()
db.session.add(self)
def post_journal(self):
""" Post the posting of this journal to the accounts.
The journal is first checked to balance. If it doesn't
balance, it is marked for being unprocessable.
"""
journal_balance = 0
if self.journalpostings:
firstpostingccy = self.journalpostings[0].currency
for posting in self.journalpostings:
if not posting.currency == firstpostingccy:
continue
if posting.is_debit():
journal_balance += posting.amount
else:
journal_balance -= posting.amount
if not journal_balance == 0:
raise JournalBalanceError('Journal balance = ' +
str(journal_balance))
for posting in self.journalpostings:
try:
posting.apply()
except NoAccountError as exc:
raise InvalidJournalError(str(exc)) from exc
self.journalstat = self.PROCESSED
class Postings(db.Model):
""" The individual postings.
Postings have the following fields :
:id: the system generated sequence number
:account_id: the account the posting is to be applied to
:journals_id: the sequence number of the journal the posting is in
:postmonth: The accounting month the posting was posted in
:currency: The currency of the posting
:amount: the posted amount in the smallest unit
:debcred: if the account is to be debited or credited
:value_date: the date the posting should take effect
:updated_at: The timestamp of the last update
"""
__tablename__ = 'postings'
id = db.Column(db.Integer, db.Sequence('posting_id_seq'), primary_key=True)
accounts_id = db.Column(db.Integer,
db.ForeignKey('accounts.id'),
nullable=False)
journals_id = db.Column(db.Integer,
db.ForeignKey('journals.id'),
nullable=False)
postmonth = db.Column(db.Numeric(precision=6))
currency = db.Column(db.String(3), nullable=False, default='EUR')
amount = db.Column(db.Numeric(precision=14), nullable=False)
debcred = db.Column(db.String(2), nullable=False)
db.CheckConstraint("debcred in ('Db', 'Cr')", name='debcredval'),
value_date = db.Column(db.DateTime, nullable=False)
updated_at = db.Column(db.DateTime, nullable=False)
@classmethod
def create_from_dict(cls, posting, for_journal):
"""Create a posting from a dictionary with the applicable fields.
TODO This routine leaks info of the json to the model. Wants
refactoring!
"""
value_date = datetime(int(posting["valuedate"][0:4]),
int(posting["valuedate"][5:7]),
int(posting["valuedate"][8:10]))
newposting = cls(postmonth=postmonth_for(value_date),
value_date=value_date,
currency=posting["currency"],
amount=posting["amount"],
debcred=posting["debitcredit"])
newposting.accounts_id = newposting._id_for_account(posting["account"])
newposting.journal = for_journal
newposting.add()
for_journal.journalpostings.append(newposting)
return newposting
@classmethod
def postings_for_account(cls, account, pagelength=25, page=1, month=None):
""" This method gets a list of postings for the account passed.
It has a pagelength for the number of postings. -1 is unlimited
(warning: That may return very many postings!
"""
posts = query(Postings).filter_by(accounts_id=account.id)
posts = posts.order_by(Postings.updated_at.desc())
if month:
posts = posts.filter_by(postmonth=Postmonths.internal(month))
if not pagelength == -1:
posts = posts.limit(pagelength)
if page is None:
page = 1
if not page == 1:
posts = posts.offset((page - 1) * pagelength)
posts = posts.all()
num_posts = query(Postings).filter_by(accounts_id=account.id)
num_posts = num_posts.count()
return PostingList(posts,
page=page,
pagelength=pagelength,
num_records=num_posts)
def _id_for_account(self, from_name):
""" Get an ID for an account for which we only have the name
"""
account = Accounts.get_by_name(from_name)
return account.id
@classmethod
def get_by_id(cls, posting_id):
""" Get a posting from its id """
return query(Postings).filter_by(id=posting_id).first()
def add(self):
""" Add this posting to the session
Make sure it is timestamped
"""
self.updated_at = datetime.today()
db.session.add(self)
@validates('debcred')
def validate_debcred(self, id, debitcredit):
""" Check if debit/credit indicator has a valid value
"""
if debitcredit not in ['Db', 'Cr']:
raise InvalidDebitCreditError('Debit credit indicator ' +
debitcredit + 'is invalid')
return debitcredit
def is_debit(self):
return (self.debcred == 'Db')
def is_credit(self):
return (self.debcred == 'Cr')
def apply(self):
""" Apply this posting to its account.
Applying means adjusting the balance with the amount of
the posting
"""
account = Accounts.get_by_id(self.accounts_id)
account.post_amount(self.debcred, self.amount, self.value_date)
class Accounts(db.Model):
""" Accounts models the "immutable" properties of an account
Accounts have the following fields:
:id: a sequence number
:name: the accounts account "number" as the user wants to see it
:role: asset, liability, income, expense
:parent_id: its place in the hierarchy, like an adjacency list
:children: the list of dependents
:balances: the balances for the account
"""
VALID_ROLES = ['I', 'E', 'A', 'L']
ROLE_NAME = {'I': 'Income', 'E': 'Expense', 'A': 'Asset', 'L': 'Liability'}
""" The list of valid roles.
:I: Income
:E: Expense
:A: Asset
:L: Liability
"""
__tablename__ = 'accounts'
id = db.Column(db.Integer, db.Sequence('account_id_seq'), primary_key=True)
name = db.Column(db.String(15), nullable=False, unique=True)
role = db.Column(db.String(1))
parent_id = db.Column(db.Integer, db.ForeignKey('accounts.id'), index=True)
children = db.relationship('Accounts')
balances = db.relationship('Balances', backref='accounts')
updated_at = db.Column(db.DateTime)
__table_args__ = (db.Index('byparent', 'parent_id', 'id'), )
@validates('role')
def validate_role(self, id, role):
if role not in self.VALID_ROLES:
raise ValueError('Account role invalid')
return role
@classmethod
def get_by_id(cls, requested_id):
""" Get an account form the database by id """
try:
account = query(Accounts).filter_by(id=requested_id).first()
if not account:
raise NoAccountError('No account for id ' + str(requested_id))
return account
except NoResultFound:
raise NoAccountError('No account for id ' + str(requested_id))
@classmethod
def get_by_name(cls, requested_name):
""" Get an account from the database by name
The name of an account is pointing to a single account row."""
try:
account = query(Accounts).filter_by(name=requested_name).first()
if not account:
raise NoAccountError('No account for ' + str(requested_name))
return account
except NoResultFound:
raise NoAccountError('No account for ' + str(requested_name))
@classmethod
def create_account(cls,
name=None,
role=None,
parent_name=None,
parent_id=None):
"""Create an account and add it as a child.
This scripts all things to be done for adding an account
while also attaching it in the structure. If either
parent_id or parent_name is filled, the method adds the
account created to the children.
Checks are made:
1. the account to be added doesn't exist
2. the parent account does exist; if not, an exception will be
thrown
"""
if not name:
raise ValueError('name cannot be None')
if cls.account_exists(requested_name=name):
raise AccountAlreadyExistsError('Account with name ' + str(name) +
' already exists')
account = cls(name=name, role=role)
parent = None
if parent_id:
parent = cls.get_by_id(parent_id)
if parent_name and not parent_id:
parent = cls.get_by_name(parent_name)
if parent:
parent.children.append(account)
account.add()
return account
@classmethod
def account_exists(cls, requested_id=None, requested_name=None):
""" Return if an account exists, presented with an ID or
an account name
"""
if requested_id:
return not (query(Accounts).filter_by(id=requested_id).all() == [])
if requested_name:
return not (query(Accounts).filter_by(name=requested_name).all()
== [])
raise NoAccountError('An account id or name is mandatory')
def _balance_for(self):
""" Set up a query for the balance(s) of this account """
return query(Balances).filter_by(accounts=self)
def parentaccount(self):
""" Get the parent of this account as an account """
if hasattr(self, 'parent_account'):
return self.parent_account
else:
parent_accounts = query(Accounts).filter_by(id=self.parent_id)\
.all()
if len(parent_accounts) > 0:
self.parentaccount = parent_accounts[0]
return parent_accounts[0]
else:
return None
def __repr__(self):
displayStr = 'Account(name = {}'.format(self.name, )
if self.role:
displayStr = displayStr + ', role = {}'.format(self.role) + ')'
return displayStr
def add(self):
""" Add this account to the session """
self.updated_at = datetime.today()
db.session.add(self)
def update_role_or_parent(self, new_role=None, new_parent=None):
""" Update the parent and or role attribute.
These are the only attributes that may be updated.
"""
if new_parent:
parent = Accounts.get_by_name(new_parent)
if parent:
self.parent_id = parent.id
self.updated_at = datetime.today()
else:
raise ValueError('Account ' + repr(new_parent) +
' (new parent) does not exist')
if new_role:
self.role = new_role
self.updated_at = datetime.today()
def current_balance(self):
""" Return the last known balance of the account """
balance_last_known = self._balance_for().order_by(Balances.postmonth.desc())\
.all()
if balance_last_known == []:
return 0
return balance_last_known[0].amount
def balance_ultimo(self, postmonth, balance_so_far=0):
""" Return the balance of the account at the end of the postmonth """
balance_requested = self._balance_for().filter(
Balances.postmonth <= postmonth).order_by(
Balances.postmonth.desc()).all()
if balance_requested != []:
balance_so_far += balance_requested[0].amount
for child in self.children:
balance_so_far = child.balance_ultimo(postmonth, balance_so_far)
return balance_so_far
def debit_credit(self):
""" Return a debit/credit indicator for the account.
The indicator is returned from the role.
"""
if self.role == 'A' or self.role == 'E':
return 'Db'
if self.role == 'L' or self.role == 'I':
return 'Cr'
# Come here, unknown role: crash
raise ValueError(f'Unknown role {role} in account {name}')
def is_debit(self):
""" Is this account a debit account? """
return (self.debit_credit() == 'Db')
def is_credit(self):
""" Is this account a credit account? """
return (self.debit_credit() == 'Cr')
def post_amount(self, debit_credit, post_amount, value_date):
"""Post an amount to this account.
This is a transaction script. The script runs as follows:
1. Get the balance row for the postmonth
2. apply the amount (using a function) to this row
3. return the new balance
"""
postmonth = postmonth_for(value_date)
balance_requested = self._balance_for().\
filter_by(postmonth=postmonth).\
order_by(Balances.postmonth.desc()).first()
if balance_requested is None:
balance_requested = Balances(account_id=self.id,
postmonth=postmonth,
amount=0,
value_date=datetime.today())
balance_requested.add()
balance_requested.update_with(debit_credit, post_amount)
return balance_requested.amount
class Postmonths(db.Model):
__tablename__ = 'postmnths'
postmonth = db.Column(db.Integer, primary_key=True)
monthstat = db.Column(db.String(1), nullable=False)
ACTIVE = 'a'
CLOSED = 'c'
def add(self):
""" Add this postmonth to the session
"""
self.updated_at = datetime.today()
db.session.add(self)
def close(self):
""" Close the postmonth for posting
This will make sure that no more postings are made to this
period, like after you have closed the books.
"""
self.monthstat = self.CLOSED
@validates('monthstat')
def validate_monthstat(self, id, monthstat):
if (monthstat != self.ACTIVE) and (monthstat != self.CLOSED):
raise InvalidPostmonthError('Invalid status in postmonth')
return monthstat
@staticmethod
def internal(month_string):
""" Return an internally formatted postmonth for the passed in
edited month string.
The edited string has the format mm-yyyy.
"""
# Check the format
if (not month_string[0:2].isdigit() or not month_string[3:7].isdigit()
or month_string[2:3] != '-' or len(month_string) != 7):
raise InvalidPostmonthError(
'The postmonth {0} could not be converted'.format(
month_string))
month = int(month_string[0:2])
year = int(month_string[3:7])
return 100 * year + month
@staticmethod
def external(postmonth):
""" Return a string for a 6 digit postmonth integer"""
return "{0:02}-{1}".format(postmonth % 100, int(postmonth / 100))
@staticmethod
def list_to_update(postmonths):
""" Return a list of Postmonths instances for the list passed
The list is assumed to consist of tuples where the first element
of the tuple is a postmonth in internal format
"""
def validate_month(postmonth_string):
if hasattr(postmonth_string, 'len') and len(postmonth_string) > 6:
raise InvalidPostmonthError('Postmonth to long')
try:
postmonth = int(postmonth_string)
except ValueError as ve:
raise InvalidPostmonthError('Postmonth must be a number')
_, monthno = divmod(postmonth, postmonth / 100)
if monthno > 12 or monthno == 0:
raise InvalidPostmonthError('Month must be from 1 to 12')
return True
keylist = [x for (x, _) in postmonths if validate_month(x)]
q = query(Postmonths).filter(Postmonths.postmonth.in_(keylist)).all()
if len(q) != len(keylist):
raise InvalidPostmonthError(
'There was an invalid postmonth in the list')
return q
@staticmethod
def update_from_list(postmonths):
""" Update postmonths from a list of changes
The list is assumed to consist of tuples where the first element
of the tuple is a postmonth in internal format and the second is
the desired monthstat (active, closed, ...)
"""
for postmonth in Postmonths.list_to_update(postmonths):
for newdata in postmonths:
if int(newdata[0]) == postmonth.postmonth \
and not newdata[1] == postmonth.monthstat:
postmonth.monthstat = newdata[1]
@staticmethod
def update_from_dict(postmonthdict):
""" Update postmonths for a dict of postmonths and statuses.
Keys are internal postmonth keys (int with form yyyymm) and
a status as value.
"""
postmonthlist = [(k, v) for k, v in postmonthdict.items()]
Postmonths.update_from_list(postmonthlist)
@staticmethod
def get_postmonths_between_dates(from_date, to_date, monthstat=None):
""" Get postmonths between between two dates.
The from_date is included (if it is 2016-01-01 2016-01 is included)
and the to_date is not (if it is 2016-01-01 2016-01 is excluded).
monthstat is none means "don't care",any value is considered to be a
selection criterion.
Although named "date", both dates are datetime instances
"""
from_pm = postmonth_for(from_date)
to_pm = postmonth_for(to_date)
pmlist = query(Postmonths).\
filter(Postmonths.postmonth >= from_pm).\
filter(Postmonths.postmonth < to_pm)
if monthstat:
pmlist = pmlist.filter(Postmonths.monthstat == monthstat)
return pmlist.all()
def status_can_post(self):
""" Returns True if the status of this postmonth
is active, i.e. posting in it is permitted.
"""
return self.monthstat == self.ACTIVE
def str(self):
""" Returns the postmonth key as a formatted string
"""
return "{0:02}-{1}".format(self.postmonth % 100,
int(self.postmonth / 100))
def __repr__(self):
""" Returns the postmonth key as a formatted string
"""
return self.str()
class Balances(db.Model):
"""Balances model the balances at different moments in time
A balance is created for each accounting period of a month.
After the end of the month it retains the ultimo balance
for further reference. During the accounting month it contains the
current balance.
A balance is made upon receiving the first posting of the accounting
month. If a record for an older month is returned, that is the current
balance; no postings for the current month have been received.
Balances have the following fields:
:id: a sequence number
:account_id: the sequence number of the account this is the balance of
:postmonth: the postmonth in the format yyyymm
:currency: the currency code (preferably: use ISO)
:amount: the amount
"""
__tablename__ = 'balances'
id = db.Column(db.Integer, db.Sequence('balance_id_seq'), primary_key=True)
account_id = db.Column(db.Integer,
db.ForeignKey('accounts.id'),
nullable=False)
postmonth = db.Column(db.Numeric(precision=6))
value_date = db.Column(db.DateTime, nullable=False)
amount = db.Column(db.Numeric(precision=14))
updated_at = db.Column(db.DateTime)
__table_args__ = (db.Index('bymonth', 'account_id', 'postmonth'), )
@validates('postmonth')
def validate_postmonth(self, id, postmonth):
""" the post month can only be the current or an existing, active
month
"""
months_db = query(Postmonths).filter_by(postmonth=postmonth).all()
if (months_db != []):
if months_db[0].status_can_post():
return postmonth
raise ValueError('Postmonth not active')
current_postmonth = postmonth_for(date.today())
if postmonth != current_postmonth:
raise ValueError('Post month must exist or be current month')
return postmonth
def add(self):
self.updated_at = datetime.today()
db.session.add(self)
def update(self):
self.updated_at = datetime.today()
db.session.update(self)
def update_with(self, debit_credit, post_amount):
""" Update the balance with the amount to be applied. """
account = Accounts.get_by_id(self.account_id)
if account.debit_credit() == debit_credit:
self.amount += post_amount
else:
self.amount -= post_amount
def __repr__(self):
return 'Balances(amount = {}, postmonth = {}, account {})'.\
format(self.amount, self.postmonth, self.account_id)