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 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 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 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 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()
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
AccountModel.reconciliations = db.relationship(
"ReconciliationModel", cascade="delete", lazy="select"
)
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()
LedgerModel.budgets = db.relationship("BudgetModel",
cascade="delete",
lazy="select")
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)
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()
LedgerModel.account_categories = db.relationship(
"AccountCategoryModel", cascade="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)
name = db.Column(db.String(128), nullable=False)
@classmethod
def find_by_ledger_id(cls,
ledger_id: int) -> List["EnvelopeCategoryModel"]:
""" Queries for envelope 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 envelopes """
if len(self.envelopes) > 0:
raise Conflict("Category contains envelopes")
super().delete()
LedgerModel.envelope_categories = db.relationship("EnvelopeCategoryModel",
cascade="delete")
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",
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")
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")
# All checks OK
LedgerModel.transactions = db.relationship("TransactionModel",
cascade="delete",
lazy="select")
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)
