class BudgetAnnualExpenseModel(db.Model, AuditModel, CrudModel):
""" Budget annual expense model """
__tablename__ = "budget_annual_expense"
id = db.Column(db.Integer, primary_key=True)
budget_id = db.Column(db.Integer,
db.ForeignKey("budget.id"),
nullable=False)
envelope_id = db.Column(db.Integer,
db.ForeignKey("envelope.id"),
nullable=False)
name = db.Column(db.String(128), nullable=False)
amount = db.Column(db.Integer, nullable=False)
details = db.relationship(
"BudgetAnnualExpenseDetailModel",
cascade="delete",
order_by="asc(BudgetAnnualExpenseDetailModel.period)",
)
@classmethod
def find_by_budget_id(cls,
budget_id: int) -> List["BudgetAnnualExpenseModel"]:
""" Queries for annual expenses under the given budget ID """
return cls.query.filter_by(budget_id=budget_id).all()
class EnvelopeModel(db.Model, AuditModel, CrudModel):
""" Envelope model """
__tablename__ = "envelope"
id = db.Column(db.Integer, primary_key=True)
category_id = db.Column(db.Integer,
db.ForeignKey("envelope_category.id"),
nullable=False)
transactions = db.relationship("EnvelopeTransactionModel", lazy="select")
periodic_expenses = db.relationship("BudgetPeriodicExpenseModel",
lazy="select")
annual_expenses = db.relationship("BudgetAnnualExpenseModel",
lazy="select")
name = db.Column(db.String(128), nullable=False)
archived = db.Column(db.Boolean, nullable=False)
external_id = db.Column(db.String(256), nullable=False)
def delete(self):
""" Deletes the envelope if it does not have any associated transactions """
if len(self.transactions) > 0:
raise Conflict("Envelope is referenced by transactions")
if len(self.periodic_expenses) > 0:
raise Conflict(
"Envelope is referenced by budgeted periodic expenses")
if len(self.annual_expenses) > 0:
raise Conflict(
"Envelope is referenced by budgeted annual expenses")
super().delete()
class LedgerModel(db.Model, AuditModel, CrudModel):
""" Ledger model """
__tablename__ = "ledger"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=False)
currency = db.Column(db.Integer, nullable=False)
class BudgetAnnualExpenseDetailModel(db.Model):
""" Budget annual expense detail model """
__tablename__ = "budget_annual_expense_detail"
id = db.Column(db.Integer, primary_key=True)
annual_budget_id = db.Column(db.Integer,
db.ForeignKey("budget_annual_expense.id"),
nullable=False)
name = db.Column(db.String(128), nullable=False)
amount = db.Column(db.Integer, nullable=False)
period = db.Column(db.Integer, nullable=False)
class ActiveBudgetModel(db.Model, AuditModel, CrudModel):
""" Active budget model """
__tablename__ = "active_budget"
id = db.Column(db.Integer, primary_key=True)
ledger_id = db.Column(db.Integer,
db.ForeignKey("ledger.id"),
nullable=False)
budget_id = db.Column(db.Integer,
db.ForeignKey("budget.id"),
nullable=False)
year = db.Column(db.Integer, nullable=False)
@classmethod
def find_by_id(cls, active_budget_id: int) -> "ActiveBudgetModel":
""" Queries for an active budget with the given ID """
active_budget = (cls.query.filter_by(
id=active_budget_id).join(BudgetModel).add_columns(
cls.id,
cls.budget_id,
cls.year,
cls.created,
cls.last_updated,
BudgetModel.name,
).first())
if not active_budget:
raise NotFound()
return active_budget
@classmethod
def find_by_ledger_id(cls, ledger_id: int) -> List["ActiveBudgetModel"]:
""" Queries for active budgets under the given ledger ID """
return (cls.query.filter_by(
ledger_id=ledger_id).join(BudgetModel).add_columns(
cls.id,
cls.budget_id,
cls.year,
cls.created,
cls.last_updated,
BudgetModel.name,
).order_by(cls.year.desc()).all())
@classmethod
def find_by_year(cls, ledger_id: int,
year: int) -> Optional["ActiveBudgetModel"]:
""" Queries for an active budget for the given year """
return cls.query.filter_by(ledger_id=ledger_id).filter_by(
year=year).first()
class BudgetPeriodicIncomeModel(db.Model, AuditModel, CrudModel):
""" Budget periodic income model """
__tablename__ = "budget_periodic_income"
id = db.Column(db.Integer, primary_key=True)
budget_id = db.Column(db.Integer,
db.ForeignKey("budget.id"),
nullable=False)
name = db.Column(db.String(128), nullable=False)
amount = db.Column(db.Integer, nullable=False)
@classmethod
def find_by_budget_id(cls,
budget_id: int) -> List["BudgetPeriodicIncomeModel"]:
""" Queries for periodic incomes under the given budget ID """
return cls.query.filter_by(budget_id=budget_id).all()
class AccountCategoryModel(db.Model, AuditModel, CrudModel):
""" Account category model """
__tablename__ = "account_category"
id = db.Column(db.Integer, primary_key=True)
ledger_id = db.Column(db.Integer, db.ForeignKey("ledger.id"), nullable=False)
accounts = db.relationship("AccountModel", cascade="delete")
name = db.Column(db.String(128), nullable=False)
@classmethod
def find_by_ledger_id(cls, ledger_id: int) -> List["AccountCategoryModel"]:
""" Queries for account categories under the given ledger ID """
return cls.query.filter_by(ledger_id=ledger_id).all()
def delete(self):
""" Deletes the category if it does not contain any child accounts """
if len(self.accounts) > 0:
raise Conflict("Category contains accounts")
super().delete()
class ReconciliationModel(db.Model, AuditModel, CrudModel):
""" Reconciliation model """
__tablename__ = "reconciliation"
id = db.Column(db.Integer, primary_key=True)
account_id = db.Column(db.Integer, db.ForeignKey("account.id"), nullable=False)
beginning_balance = db.Column(db.Integer, nullable=False)
beginning_date = db.Column(db.Date, nullable=False)
ending_balance = db.Column(db.Integer, nullable=False)
ending_date = db.Column(db.Date, nullable=False)
transactions = db.relationship("AccountTransactionModel", lazy="select")
@classmethod
def find_by_account_id(cls, account_id: int, page: int = 1, size: int = 20):
""" Queries for reonciliations under the given account ID """
return (
cls.query.filter_by(account_id=account_id)
.order_by(cls.ending_date.desc())
.paginate(page, size)
)
@classmethod
def find_last_by_account_id(cls, account_id: int):
""" Queries for the last reonciliation under the given account ID """
reconciliation = (
cls.query.filter_by(account_id=account_id)
.order_by(cls.ending_date.desc())
.limit(1)
.one_or_none()
)
if not reconciliation:
raise NotFound()
return reconciliation
class BudgetModel(db.Model, AuditModel, CrudModel):
""" Budget model """
__tablename__ = "budget"
id = db.Column(db.Integer, primary_key=True)
ledger_id = db.Column(db.Integer,
db.ForeignKey("ledger.id"),
nullable=False)
name = db.Column(db.String(128), nullable=False)
periods = db.Column(db.Integer, nullable=False)
@classmethod
def find_by_ledger_id(cls, ledger_id: int) -> List["BudgetModel"]:
""" Queries for budgets under the given ledger ID """
return cls.query.filter_by(ledger_id=ledger_id).all()
def delete(self):
""" Deletes the budget if it is not an active budget for any year """
if ActiveBudgetModel.query.filter_by(budget_id=self.id).count():
raise Conflict("Budget is an active budget")
super().delete()
class AccountModel(db.Model, AuditModel, CrudModel):
""" Account model """
__tablename__ = "account"
id = db.Column(db.Integer, primary_key=True)
category_id = db.Column(
db.Integer, db.ForeignKey("account_category.id"), nullable=False
)
transactions = db.relationship("AccountTransactionModel", lazy="select")
name = db.Column(db.String(128), nullable=False)
institution = db.Column(db.String(256), nullable=False)
account_number = db.Column(db.String(256), nullable=False)
archived = db.Column(db.Boolean, nullable=False)
external_id = db.Column(db.String(256), nullable=False)
def delete(self):
""" Deletes the account if it does not have any associated transactions """
if len(self.transactions) > 0:
raise Conflict("Account is referenced by transactions")
super().delete()
class AuditModel:
""" Auditable model """
created = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, nullable=False)
class TransactionModel(db.Model, AuditModel, CrudModel):
""" Transaction model """
__tablename__ = "transaction"
id = db.Column(db.Integer, primary_key=True)
ledger_id = db.Column(db.Integer,
db.ForeignKey("ledger.id"),
nullable=False)
account_transactions = db.relationship(
"AccountTransactionModel", cascade="save-update,delete,delete-orphan")
envelope_transactions = db.relationship(
"EnvelopeTransactionModel", cascade="save-update,delete,delete-orphan")
transaction_type = db.Column(db.Enum(TransactionType), nullable=False)
recorded_date = db.Column(db.Date, nullable=False)
payee = db.Column(db.String(256), nullable=False)
def _check_type(self, allowed_types: List["TransactionType"],
default_type: "TransactionType"):
"""
Sets the transaction type to the default type if it is undefined, or validates that
the type is one of the allowed types.
"""
if not self.transaction_type:
self.transaction_type = default_type
elif self.transaction_type not in allowed_types:
raise ValidationError(
f"Invalid type for {default_type.name} transactions")
def validate(self):
"""
Verifies that the transaction satisfies all integrity constraints, including:
- zero-sum difference between account and envelope transactions
- transaction type matches account and envelope transactions
"""
# pylint: disable=not-an-iterable
account_sum = sum([trn.amount for trn in self.account_transactions], 0)
envelope_sum = sum([trn.amount for trn in self.envelope_transactions],
0)
has_account_trns = len(self.account_transactions) != 0
has_envelope_trns = len(self.envelope_transactions) != 0
if account_sum - envelope_sum != 0:
raise ValidationError("Transaction entries are unbalanced")
if account_sum > 0: # is an increase
self._check_type(TransactionType.incomes(), TransactionType.income)
elif account_sum < 0: # is a decrease
self._check_type(TransactionType.expenses(),
TransactionType.expense)
elif (has_account_trns and
not has_envelope_trns): # zero-sum, only account transactions
self._check_type(TransactionType.transfers(),
TransactionType.transfer)
elif (has_envelope_trns and
not has_account_trns): # zero-sum, only envelope transactions
self._check_type(TransactionType.allocations(),
TransactionType.allocation)
elif (has_account_trns and has_envelope_trns
): # zero-sum, account and envelope transactions
raise ValidationError(
"Cannot transfer account and envelope balances in same transaction",
)
else: # zero-sum, no transactions at all
raise ValidationError("Missing account or envelope transactions")
class EnvelopeTransactionModel(db.Model):
""" Envelope transaction model """
__tablename__ = "envelope_transaction"
id = db.Column(db.Integer, primary_key=True)
transaction_id = db.Column(db.Integer,
db.ForeignKey("transaction.id"),
nullable=False)
envelope_id = db.Column(db.Integer,
db.ForeignKey("envelope.id"),
nullable=False)
amount = db.Column(db.Integer, nullable=False)
memo = db.Column(db.String(256), nullable=False)
@classmethod
def get_history(
cls: Type["EnvelopeTransactionModel"],
envelope_id: int,
page: int = 1,
size: int = 20,
):
""" Gets ordered transaction history for a single envelope. """
# Join envelope transactions and transactions so we can sort by date
# and include payee, date, type.
# Use a postgres window function to calculate the running balance.
sql = text(
"SELECT "
f"{cls.__tablename__}.id as id, "
f"{cls.__tablename__}.amount as amount, "
f"{cls.__tablename__}.memo as memo, "
f"{cls.__tablename__}.transaction_id as transaction_id, "
f"{TransactionModel.__tablename__}.transaction_type as transaction_type, "
f"{TransactionModel.__tablename__}.recorded_date as recorded_date, "
f"{TransactionModel.__tablename__}.payee as payee, "
f"sum({cls.__tablename__}.amount) over "
f" (partition by {cls.__tablename__}.envelope_id "
f" ORDER BY {TransactionModel.__tablename__}.recorded_date, "
f" {cls.__tablename__}.id"
f") AS balance "
f"FROM {cls.__tablename__}, {TransactionModel.__tablename__} "
f"WHERE {cls.__tablename__}.transaction_id = {TransactionModel.__tablename__}.id "
f"AND {cls.__tablename__}.envelope_id = :envelope_id "
f"ORDER BY {TransactionModel.__tablename__}.recorded_date DESC, "
f"{cls.__tablename__}.id DESC "
"LIMIT :size OFFSET :page")
transactions = db.session.execute(sql, {
"envelope_id": envelope_id,
"page": ((page - 1) * size),
"size": size
})
total = cls.query.filter_by(envelope_id=envelope_id).count()
return Pagination(cls.query, page, size, total, transactions)
# pylint: disable=too-many-arguments
@classmethod
def search(
cls: Type["EnvelopeTransactionModel"],
page: int = 1,
size: int = 20,
amount: Optional[Dict[str, Any]] = None,
envelope_id: Optional[Dict[str, Any]] = None,
memo: Optional[Dict[str, Any]] = None,
payee: Optional[Dict[str, Any]] = None,
recorded_date: Optional[Dict[str, Any]] = None,
transaction_type: Optional[Dict[str, Any]] = None,
):
""" Searches for envelope transactions """
query = cls.query.join(TransactionModel).add_columns(
cls.id,
cls.amount,
cls.envelope_id,
cls.memo,
TransactionModel.id.label("transaction_id"),
TransactionModel.transaction_type,
TransactionModel.recorded_date,
TransactionModel.payee,
)
if amount:
query = filter_ops.filter_comp(query, cls.amount, **amount)
if envelope_id:
query = filter_ops.filter_in(query, cls.envelope_id, **envelope_id)
if memo:
query = filter_ops.filter_str(query, cls.memo, **memo)
if payee:
query = filter_ops.filter_str(query, TransactionModel.payee,
**payee)
if recorded_date:
query = filter_ops.filter_comp(query,
TransactionModel.recorded_date,
**recorded_date)
if transaction_type:
query = filter_ops.filter_in(query,
TransactionModel.transaction_type,
**transaction_type)
return query.order_by(TransactionModel.recorded_date.desc()).paginate(
page, size)
class AccountTransactionModel(db.Model):
""" Account transaction model """
__tablename__ = "account_transaction"
id = db.Column(db.Integer, primary_key=True)
transaction_id = db.Column(db.Integer,
db.ForeignKey("transaction.id"),
nullable=False)
account_id = db.Column(db.Integer,
db.ForeignKey("account.id"),
nullable=False)
amount = db.Column(db.Integer, nullable=False)
memo = db.Column(db.String(256), nullable=False)
cleared = db.Column(db.Boolean, nullable=False)
reconciliation_id = db.Column(db.Integer,
db.ForeignKey("reconciliation.id"),
nullable=True)
@classmethod
def update_reconciliation_id(
cls: Type["AccountTransactionModel"],
transaction_ids: List[int],
reconciliation_id: int,
):
""" Updates the specified transactions to reference the given reconciliation """
count = cls.query.filter(cls.id.in_(transaction_ids)).update(
{
cls.reconciliation_id: reconciliation_id,
cls.cleared: True
},
synchronize_session=False,
)
if count != len(transaction_ids):
raise NotFound("Account transaction not found")
@classmethod
def remove_reconciliation_id(cls: Type["AccountTransactionModel"],
reconciliation_id: int):
""" Removes any references to the given reconciliation ID from all transactions """
cls.query.filter_by(reconciliation_id=reconciliation_id).update(
{"reconciliation_id": None}, synchronize_session=False)
@classmethod
def get_history(
cls: Type["AccountTransactionModel"],
account_id: int,
page: int = 1,
size: int = 20,
):
""" Gets ordered transaction history for a single account. """
# Join account transactions and transactions so we can sort by date
# and include payee, date, type.
# Use a postgres window function to calculate the running balance.
sql = text(
"SELECT "
f"{cls.__tablename__}.id as id, "
f"{cls.__tablename__}.amount as amount, "
f"{cls.__tablename__}.memo as memo, "
f"{cls.__tablename__}.cleared as cleared, "
f"{cls.__tablename__}.reconciliation_id as reconciliation_id, "
f"{cls.__tablename__}.transaction_id as transaction_id, "
f"{TransactionModel.__tablename__}.transaction_type as transaction_type, "
f"{TransactionModel.__tablename__}.recorded_date as recorded_date, "
f"{TransactionModel.__tablename__}.payee as payee, "
f"sum({cls.__tablename__}.amount) over "
f" (partition by {cls.__tablename__}.account_id "
f" ORDER BY {TransactionModel.__tablename__}.recorded_date, "
f" {cls.__tablename__}.id"
f") AS balance "
f"FROM {cls.__tablename__}, {TransactionModel.__tablename__} "
f"WHERE {cls.__tablename__}.transaction_id = {TransactionModel.__tablename__}.id "
f"AND {cls.__tablename__}.account_id = :account_id "
f"ORDER BY {TransactionModel.__tablename__}.recorded_date DESC, "
f"{cls.__tablename__}.id DESC "
"LIMIT :size OFFSET :page")
transactions = db.session.execute(sql, {
"account_id": account_id,
"page": ((page - 1) * size),
"size": size
})
total = cls.query.filter_by(account_id=account_id).count()
return Pagination(cls.query, page, size, total, transactions)
# pylint: disable=too-many-arguments
@classmethod
def search(
cls: Type["AccountTransactionModel"],
page: int = 1,
size: int = 20,
account_id: Optional[Dict[str, Any]] = None,
amount: Optional[Dict[str, Any]] = None,
cleared: Optional[Dict[str, Any]] = None,
memo: Optional[Dict[str, Any]] = None,
payee: Optional[Dict[str, Any]] = None,
reconciliation_id: Optional[Dict[str, Any]] = None,
recorded_date: Optional[Dict[str, Any]] = None,
transaction_type: Optional[Dict[str, Any]] = None,
):
""" Searches for account transactions """
query = cls.query.join(TransactionModel).add_columns(
cls.id,
cls.account_id,
cls.amount,
cls.cleared,
cls.memo,
cls.reconciliation_id,
TransactionModel.id.label("transaction_id"),
TransactionModel.transaction_type,
TransactionModel.recorded_date,
TransactionModel.payee,
)
if account_id:
query = filter_ops.filter_in(query, cls.account_id, **account_id)
if amount:
query = filter_ops.filter_comp(query, cls.amount, **amount)
if cleared:
query = filter_ops.filter_bool(query, cls.cleared, **cleared)
if memo:
query = filter_ops.filter_str(query, cls.memo, **memo)
if payee:
query = filter_ops.filter_str(query, TransactionModel.payee,
**payee)
if reconciliation_id:
query = filter_ops.filter_in(query, cls.reconciliation_id,
**reconciliation_id)
if recorded_date:
query = filter_ops.filter_comp(query,
TransactionModel.recorded_date,
**recorded_date)
if transaction_type:
query = filter_ops.filter_in(query,
TransactionModel.transaction_type,
**transaction_type)
return query.order_by(TransactionModel.recorded_date.desc()).paginate(
page, size)
