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)