213 lines
6.3 KiB
Python
213 lines
6.3 KiB
Python
"""Module containing account related models."""
|
|
|
|
# vim: set tw=80 ts=4 sw=4 sts=4:
|
|
|
|
from . import db
|
|
|
|
from .operations import Operation
|
|
|
|
|
|
# pylint: disable=no-member,invalid-name,too-few-public-methods,no-self-use
|
|
class Account(db.Model):
|
|
"""Model class to handle accounts."""
|
|
id = db.Column(db.Integer, primary_key=True)
|
|
name = db.Column(db.String(200), nullable=False)
|
|
authorized_overdraft = db.Column(db.Integer, nullable=True, default=0)
|
|
|
|
def __init__(self, name, authorized_overdraft):
|
|
self.name = name
|
|
self.authorized_overdraft = authorized_overdraft
|
|
|
|
@classmethod
|
|
def query(cls):
|
|
"""Return a query for this class method."""
|
|
return db.session.query(
|
|
cls
|
|
).order_by(
|
|
cls.name
|
|
)
|
|
|
|
def balances(self):
|
|
"""Return the balances of this account."""
|
|
return db.session.query(
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
), 0
|
|
).label("future"),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).filter(
|
|
Operation.pointed
|
|
), 0
|
|
).label("pointed"),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value,
|
|
).filter(
|
|
Operation.operation_date <= db.func.current_date()
|
|
), 0
|
|
).label("current"),
|
|
).filter(
|
|
Operation.account_id == self.id
|
|
).one()
|
|
|
|
def income(self, begin, end):
|
|
"""Return the income for a specific time period."""
|
|
query = db.session.query(
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).filter(
|
|
db.func.sign(Operation.value) == -1
|
|
), 0
|
|
).label("expenses"),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).filter(
|
|
db.func.sign(Operation.value) == 1
|
|
), 0
|
|
).label("revenues"),
|
|
db.func.coalesce(
|
|
db.func.sum(Operation.value), 0
|
|
).label("income")
|
|
).filter(
|
|
Operation.account_id == self.id,
|
|
)
|
|
|
|
if begin:
|
|
query = query.filter(Operation.operation_date >= str(begin))
|
|
|
|
if end:
|
|
query = query.filter(Operation.operation_date <= str(end))
|
|
|
|
return query.one()
|
|
|
|
def category_incomes(self, begin=None, end=None):
|
|
"""Return a query for categories with expenses, revenues and income for
|
|
this account and an optional specific time period."""
|
|
|
|
query = db.session.query(
|
|
Operation.category.label('category'),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).filter(
|
|
db.func.sign(Operation.value) == -1
|
|
),
|
|
0
|
|
).label("expenses"),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).filter(
|
|
db.func.sign(Operation.value) == 1
|
|
),
|
|
0
|
|
).label("revenues"),
|
|
db.func.sum(Operation.value).label("income")
|
|
).filter(
|
|
Operation.account_id == self.id
|
|
).order_by(
|
|
Operation.category
|
|
).group_by(
|
|
Operation.category
|
|
)
|
|
|
|
if begin:
|
|
query = query.filter(Operation.operation_date >= str(begin))
|
|
|
|
if end:
|
|
query = query.filter(Operation.operation_date <= str(end))
|
|
|
|
return query
|
|
|
|
def daily_balances(self, begin=None, end=None):
|
|
"""Return a query for daily balances with expenses, revenues, income
|
|
and balance per day for this account and an optional specifir range."""
|
|
|
|
#end = end if end else arrow.now().ceil('month').date()
|
|
|
|
query = db.session.query(
|
|
Operation.operation_date,
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).filter(
|
|
db.func.sign(Operation.value) == -1
|
|
).over(
|
|
partition_by=[
|
|
Operation.account_id,
|
|
Operation.operation_date
|
|
],
|
|
),
|
|
0
|
|
).label("expenses"),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).filter(
|
|
db.func.sign(Operation.value) == 1
|
|
).over(
|
|
partition_by=[
|
|
Operation.account_id,
|
|
Operation.operation_date
|
|
],
|
|
),
|
|
0
|
|
).label("revenues"),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).over(
|
|
partition_by=[
|
|
Operation.account_id,
|
|
Operation.operation_date
|
|
],
|
|
)
|
|
).label("income"),
|
|
db.func.coalesce(
|
|
db.func.sum(
|
|
Operation.value
|
|
).over(
|
|
partition_by=Operation.account_id,
|
|
order_by=Operation.operation_date
|
|
)
|
|
).label("balance")
|
|
).distinct(
|
|
).order_by(
|
|
Operation.operation_date
|
|
).filter(
|
|
Operation.account_id == self.id
|
|
)
|
|
|
|
if begin:
|
|
base_query = query.subquery()
|
|
|
|
query = db.session.query(
|
|
base_query
|
|
).filter(
|
|
base_query.c.operation_date >= str(begin)
|
|
).order_by(
|
|
base_query.c.operation_date
|
|
)
|
|
|
|
if end:
|
|
query = query.filter(query.c.operation_date <= str(end))
|
|
|
|
elif end:
|
|
query = query.filter(Operation.operation_date <= str(end))
|
|
|
|
return query
|
|
|
|
@db.validates('authorized_overdraft')
|
|
def validate_authorized_overdraft(self, key, authorized_overdraft):
|
|
"""Validator for authorized_overdraft : must be negative."""
|
|
del key
|
|
|
|
assert authorized_overdraft <= 0
|
|
|
|
return authorized_overdraft
|