class TransactionCategory(db.Model):
transaction_id = db.Column(db.ForeignKey('transaction.id',
ondelete='CASCADE'),
primary_key=True)
category_id = db.Column(db.ForeignKey('category.id', ondelete='CASCADE'),
primary_key=True)
transaction_amount = db.Column(db.Numeric(15, 3), nullable=False)
category_amount = db.Column(db.Numeric(15, 3), nullable=False)
category = db.relationship('Category')
transaction = db.relationship('Transaction',
backref=db.backref(
'transaction_categories',
cascade="all, delete-orphan"))
def as_dict(self, username):
data = self.category.as_dict(username,
parent=False,
children=False,
balance=False)
data['transaction_amount'] = self.transaction_amount
data['category_amount'] = self.category_amount
return data
def as_tuple(self):
return (self.category_id, {
'transaction_amount': self.transaction_amount,
'category_amount': self.category_amount
})
class Currency(db.Model):
id = db.Column(db.Integer, primary_key=True)
owner_username = db.Column(db.String(50),
db.ForeignKey('user.username', use_alter=True,
onupdate='CASCADE',
ondelete='CASCADE',
name='fk_owner'))
isocode = db.Column(db.String(5), nullable=False)
symbol = db.Column(db.String(5), nullable=False)
name = db.Column(db.String(50), nullable=False)
rate = db.Column(db.Numeric(16, 4))
def __unicode__(self):
return u'Currency name "{0}", isocode "{1}", symbol "{2}", rate "{3}"'.format(
self.name, self.isocode, self.symbol, self.rate
)
def as_dict(self):
info = {
'isocode': self.isocode,
'symbol': self.symbol,
'name': self.name
}
if self.owner_username:
info['owner'] = self.owner_username
if self.rate is not None:
info['rate'] = self.rate
return info
class Transaction(db.Model):
id = db.Column(db.Integer, primary_key=True)
owner_username = db.Column(db.ForeignKey('user.username',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
description = db.Column(db.String(200), nullable=False)
original_description = db.Column(db.String(200), nullable=False)
amount = db.Column(db.Numeric(15, 3), nullable=False)
currency_id = db.Column(db.ForeignKey('currency.id', ondelete='CASCADE'),
nullable=False)
date = db.Column(db.Date, nullable=False)
currency = db.relationship('Currency')
def as_dict(self, username):
return {
'id': self.id,
'description': self.description,
'original_description': self.original_description,
'amount': self.amount,
'currency': self.currency.isocode,
'date': self.date.strftime('%Y-%m-%d'),
'accounts': [ta.as_dict(username) \
for ta in self.transaction_accounts],
'categories': [tc.as_dict(username) \
for tc in self.transaction_categories]
}
class Account(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
currency_id = db.Column(db.ForeignKey('currency.id', ondelete='CASCADE'))
start_balance = db.Column(db.Numeric(15, 3), nullable=False)
currency = db.relationship('Currency')
def __unicode__(self):
return u'Account id {0}, name "{1}", currency "{2}", start balance {3}'.format(
self.id, self.name, self.currency.isocode, self.start_balance)
def balance(self, username):
"""
Return a list :
[ <balance in account currency>, <balance in preferred currency> ]
"""
balance = db.session.query(db.func.sum(
TransactionAccount.amount)).filter(
TransactionAccount.account_id == self.id).one()[0]
if balance:
balances = [self.start_balance + balance]
else:
balances = [self.start_balance]
balances.append(
helpers.rate(
username, self.currency.isocode,
coremodels.User.query.options(
db.joinedload(coremodels.User.preferred_currency)).get(
username).preferred_currency.isocode) * balances[0])
return balances
def transactions_count(self):
return db.session.query(
db.func.count(TransactionAccount.transaction_id)).filter(
TransactionAccount.account_id == self.id).one()[0] or 0
def as_dict(self, username, short=False):
if short:
return {
'id': self.id,
'name': self.name,
'currency': self.currency.isocode
}
else:
balances = self.balance(username)
return {
'id': self.id,
'name': self.name,
'currency': self.currency.isocode,
'start_balance': self.start_balance,
'balance': balances[0],
'balance_preferred': balances[1],
'transactions_count': self.transactions_count()
}
class TransactionAccount(db.Model):
transaction_id = db.Column(db.ForeignKey('transaction.id',
ondelete='CASCADE'),
primary_key=True)
account_id = db.Column(db.ForeignKey('account.id', ondelete='CASCADE'),
primary_key=True)
amount = db.Column(db.Numeric(15, 3), nullable=False)
verified = db.Column(db.Boolean, nullable=False, default=False)
account = db.relationship('Account')
transaction = db.relationship('Transaction',
backref=db.backref(
'transaction_accounts',
cascade="all, delete-orphan"))
def as_dict(self, username):
data = self.account.as_dict(username, short=True)
data['amount'] = self.amount
data['verified'] = self.verified
return data
def as_tuple(self):
return (self.account_id, self.amount)