class ReceiptItem(Base):
__tablename__ = 'receiptitem'
id = db.Column(db.Integer, primary_key=True)
receipt_id = db.Column(db.Integer, db.ForeignKey('receipt.id'))
name = db.Column(db.String(100), nullable=False)
amount = db.Column(db.Float(asdecimal=True), nullable=False)
class Base(db.Model):
# @declared_attr
# def __tablename__(cls):
# return cls.__name__.lower()
__abstract__ = True
created_on = db.Column(db.DateTime(), default=db.func.now())
updated_on = db.Column(db.DateTime(), default=db.func.now(),
onupdate=db.func.now())
@declared_attr
def __mapper_args__(cls):
return {
"order_by": cls.created_on.desc()
}
class Balance(OwnedMixin, Base):
__tablename__ = 'balance'
id = db.Column(db.Integer, primary_key=True)
# to_user
# from_user
amount = db.Column(db.Float(asdecimal=True),
nullable=False)
paid = db.Column(db.Boolean, nullable=False, default=balance_paid_default)
receipt_id = db.Column(db.Integer, db.ForeignKey('receipt.id'))
# receipt_name = column_property(
# select(
# [Receipt.name]
# ).select_from(
# Receipt
# ).where(
# Receipt.id == receipt_id
# ).correlate_except(Receipt)
# )
@property
def receipt_name(self):
from .receipt_model import Receipt
receipt_name = select(
[Receipt.name]
).select_from(
Receipt
).where(
Receipt.id == self.receipt_id
).correlate_except(Receipt)
return db.session.scalar(receipt_name)
@property
def is_to_and_from_owner(self):
return self.to_user_id == self.from_user_id
class Payment(RequestMixin, Base):
__tablename__ = 'payment'
id = db.Column(db.Integer, primary_key=True)
# TODO
date = db.Column(db.DateTime(), default=func.now(), nullable=False)
# accepted
# archived
message = db.Column(db.String(MAX_MESSAGE_LENGTH))
# to_user_id
# from_user_id
# to_user
# from_user
amount = db.Column(db.Float(asdecimal=True), nullable=False)
# methods return True if modified, else False
def accept(self):
def callback():
app.logger.debug("PAYMENT ACCEPT")
s = Settlement.get(self.from_user_id, self.to_user_id)
app.logger.debug("add payment settlement %s", s)
s.add_payment(self)
return super().accept(callback=callback)
def reject(self):
def callback():
s = Settlement.get(self.from_user_id, self.to_user_id)
app.logger.debug("remove payment settlement %s", s)
s.remove_payment(self)
return super().reject(callback=callback)
class Friend(RequestMixin, Base):
__tablename__ = 'friend'
id = db.Column(db.Integer, primary_key=True)
# to_user_id
# from_user_id
def accept(self):
def callback():
# add settlement objects for each user in the friendrequest on
# accept
if Settlement.get(self.from_user_id, self.to_user_id) is None:
db.session.add(
Settlement(
left_user_id=self.from_user_id,
right_user_id=self.to_user_id,
paid_amount=0.0,
owed_amount=0.0,
))
return super().accept(callback=callback)
class Receipt(Base):
__tablename__ = 'receipt'
# user
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False, default="New Receipt")
amount = db.Column(db.Float(asdecimal=True), nullable=False, default=0)
date = db.Column(db.Date, default=func.now(), nullable=False)
tax = db.Column(db.Float(asdecimal=True), nullable=False, default=0)
# resolved = db.Column(db.Boolean)
# user
# users
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
balances = relationship("Balance",
backref="receipt",
cascade="all,delete-orphan")
receipt_items = relationship("ReceiptItem",
backref="receipt",
foreign_keys=[ReceiptItem.receipt_id],
cascade="all,delete-orphan")
resolved = column_property(
and_(
exists( # check that balance exists, else not resolved
select([Balance.id]).select_from(Balance).where(
Balance.receipt_id == id, ).correlate_except(Balance)),
~exists( # if bal exist, check that no unpaid balances exist
select([Balance.id]).select_from(Balance).where(
and_(Balance.receipt_id == id,
Balance.paid.is_(False))).correlate_except(Balance))))
class User(Base):
__tablename__ = 'user'
# friend_of
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(100), unique=True, nullable=False)
password = db.Column(db.String(100), nullable=False)
fullname = db.Column(db.String(100), nullable=False)
receipts_owned = relationship("Receipt",
backref="user",
cascade="merge,delete")
receipts_in = relationship("Receipt",
secondary=receipt_association_table,
backref="users")
balances_to_user = relationship("Balance",
foreign_keys=[Balance.to_user_id],
backref="to_user")
balances_from_user = relationship("Balance",
foreign_keys=[Balance.from_user_id],
backref="from_user")
payments_to_user = relationship("Payment",
foreign_keys=[Payment.to_user_id],
backref="to_user")
payments_from_user = relationship("Payment",
foreign_keys=[Payment.from_user_id],
backref="from_user")
settlements_right_user = relationship(
"Settlement",
foreign_keys=[Settlement.right_user_id],
backref="to_user")
settlements_left_user = relationship(
"Settlement", foreign_keys=[Settlement.left_user_id], backref="user")
receipt_items = relationship("ReceiptItem",
secondary=receiptitem_association_table,
backref="users")
friend_to_user = relationship("Friend",
foreign_keys=[Friend.to_user_id],
backref="to_user")
friend_from_user = relationship("Friend",
foreign_keys=[Friend.from_user_id],
backref="from_user")
@property
def settlements(self):
settle_from = db.session.query(
Settlement.left_user_id.label("left_user_id"),
Settlement.right_user_id.label("right_user_id")).filter_by(
left_user_id=self.id)
settle_to = db.session.query(
Settlement.left_user_id.label("left_user_id"),
Settlement.right_user_id.label("right_user_id")).filter_by(
right_user_id=self.id)
result_ids = settle_from.union(settle_to).subquery()
result = db.session.query(Settlement).select_from(result_ids).join(
Settlement,
and_(Settlement.left_user_id == result_ids.c.left_user_id,
Settlement.right_user_id == result_ids.c.right_user_id))
return result
@property
def friends(self):
friends_from = db.session.query(
Friend.to_user_id.label("id")).select_from(Friend).filter_by(
from_user_id=self.id, accepted=True)
friends_to = db.session.query(
Friend.from_user_id.label("id")).select_from(Friend).filter_by(
to_user_id=self.id, accepted=True)
all_friends_ids = friends_from.union(friends_to).subquery()
all_friends = db.session.query(User).select_from(all_friends_ids).join(
User, User.id == all_friends_ids.c.id)
return all_friends
@property
def receipts_owned_unresolved(self):
result = Receipt.query.filter(Receipt.user_id == self.id,
Receipt.resolved.is_(False))
return result
@property
def receipts_owned_resolved(self):
result = Receipt.query.filter(Receipt.user_id == self.id,
Receipt.resolved.is_(True))
return result
@property
def receipts_owed_unresolved(self):
result = Receipt.query.join(receipt_association_table, ).filter(
receipt_association_table.c.left_id == self.id,
Receipt.user_id != self.id, Receipt.resolved.is_(False))
# app.logger.debug("receipts_owed expression running - %s", result)
return result
@property
def receipts_owed_resolved(self):
result = Receipt.query.join(receipt_association_table, ).filter(
receipt_association_table.c.left_id == self.id,
Receipt.user_id != self.id, Receipt.resolved.is_(True))
# app.logger.debug("receipts_owed expression running - %s", result)
return result
@property
def receipts_owed(self):
result = Receipt.query.join(receipt_association_table, ).filter(
receipt_association_table.c.left_id == self.id,
Receipt.user_id != self.id)
# app.logger.debug("receipts_owed expression running - %s", result)
return result
@property
def receipts_resolved(self):
# TODO
result = Receipt.query.join(receipt_association_table, ).filter(
or_(
receipt_association_table.c.left_id == self.id,
receipt_association_table.c.right_id == self.id,
),
# Receipt.user_id != self.id,
Receipt.resolved.is_(True))
return result
class RequestMixin(OwnedMixin, object):
accepted = db.Column(db.Boolean)
archived_from = db.Column(db.Boolean, default=False, nullable=False)
archived_to = db.Column(db.Boolean, default=False, nullable=False)
@classmethod
def get_received(cls, user):
return cls.query.filter_by(
# accepted=None,
to_user_id=user.id,
archived_to=False
)
@classmethod
def get_sent(cls, user):
return cls.query.filter_by(
from_user_id=user.id,
archived_from=False
)
@classmethod
def archive_sent(cls, user):
# archive payments not archived yet which are not in "pending" state
# (null)
cls.query.filter(
cls.from_user_id == user.id,
cls.archived_from.is_(False),
cls.accepted.isnot(None)
).update({"archived_from": True})
cls.query.filter(
cls.to_user_id == user.id,
cls.archived_to.is_(False),
cls.accepted.isnot(None)
).update({"archived_to": True})
db.session.commit()
def accept(self, callback=lambda: None):
app.logger.debug("accepted for %s is %s", self.id, self.accepted)
if self.accepted is False or self.accepted is None:
# False means payment was previously rejected
# None means payment has not been accepted or rejected yet
# rejected or not added yet
# in this case, we add the payment to the settlement
# from user -> to user
# TODO exception id settlement none
callback()
# unarchive
self.archived_from = False
self.archived_to = False
self.accepted = True
return True
return False
def reject(self, callback=lambda: None):
if self.accepted is True:
# True means payment was previously accepted
# if payment was previously accepted, we have to remove payment
# value
# in this case, we remove the previously added payment to the
# settlement from user -> to user
# s = self.from_user.get_settlement_to(self.to_user)
# TODO exception id settlement none
callback()
# unarchive
self.archived_from = False
self.archived_to = False
self.accepted = False
return True
if self.accepted is None:
# None means payment is new
self.accepted = False
return True
return False
def to_user_id(cls):
return db.Column(db.Integer, db.ForeignKey('user.id'))
# from flask import current_app as app
from sqlalchemy.orm import relationship, column_property
from sqlalchemy.sql import func, select, exists, and_
from receipt_split.meta import db
from . import Base, Balance
receiptitem_association_table = db.Table(
'user_receiptitem_association', db.metadata,
db.Column('left_id', db.Integer, db.ForeignKey('user.id')),
db.Column('right_id', db.Integer, db.ForeignKey('receiptitem.id')))
receipt_association_table = db.Table(
'user_receipt_association', db.metadata,
db.Column('left_id', db.Integer, db.ForeignKey('user.id')),
db.Column('right_id', db.Integer, db.ForeignKey('receipt.id')))
class ReceiptItem(Base):
__tablename__ = 'receiptitem'
id = db.Column(db.Integer, primary_key=True)
receipt_id = db.Column(db.Integer, db.ForeignKey('receipt.id'))
name = db.Column(db.String(100), nullable=False)
amount = db.Column(db.Float(asdecimal=True), nullable=False)
class Receipt(Base):
__tablename__ = 'receipt'
class Settlement(Base):
__tablename__ = 'settlement'
__table_args__ = (db.CheckConstraint('left_user_id <> right_user_id'), )
left_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True,
nullable=False)
right_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True,
nullable=False)
# user
# to_user
owed_amount = db.Column(db.Float(asdecimal=True),
nullable=False,
default=0.0)
paid_amount = db.Column(db.Float(asdecimal=True),
nullable=False,
default=0.0)
diff_amount = column_property(owed_amount - paid_amount)
def get_balances_to_pay(self, user_id):
if not is_valid_user(self, user_id):
# if from or to balance user is invalid, then return false
return []
other_user_id = self.get_other_user_id(user_id)
app.logger.debug("Balances get_balances_to_pay other_user_id %s",
other_user_id)
balances = Balance.query.filter_by(from_user_id=user_id,
to_user_id=other_user_id,
paid=False).all()
app.logger.debug("Balances get_balances_to_pay %s", balances)
return balances
def get_balances_owed(self, user_id):
if not is_valid_user(self, user_id):
# if from or to balance user is invalid, then return false
return []
other_user_id = self.get_other_user_id(user_id)
balances = Balance.query.filter_by(from_user_id=other_user_id,
to_user_id=user_id,
paid=False).all()
return balances
# TODO
# https://stackoverflow.com/questions/34057756/how-to-combine-sqlalchemys-hybrid-property-decorator-with-werkzeugs-cached-pr
# def update_settlement(self):
# """should be legacy"""
# # get balance from adding up unpaid balances
# from_balances = db.session.query(
# func.coalesce(
# func.sum(Balance.amount), literal_column("0.0")
# ).label("sum")
# ).filter_by(
# from_user_id=self.left_user_id,
# to_user_id=self.right_user_id,
# paid=False
# ).subquery()
# to_balances = db.session.query(
# func.coalesce(
# func.sum(Balance.amount), literal_column("0.0")
# ).label("sum")
# ).filter_by(
# from_user_id=self.right_user_id,
# to_user_id=self.left_user_id,
# paid=False
# ).subquery()
# result = db.session.query(
# (from_balances.c.sum - to_balances.c.sum).label("result")
# ).scalar()
# app.logger.info("NEW BALANCE AMOUNT %s", result)
# self.owed_amount = result
# app.logger.info("NEW BALANCE owed AMOUNT %s", self.owed_amount)
# # self.owed_amount = s
# # self.balance_amount = s
def get(from_user_id, to_user_id):
app.logger.debug("GET SETTLEMENT %s %s", from_user_id, to_user_id)
s1 = Settlement.query.get({
"left_user_id": from_user_id,
"right_user_id": to_user_id,
})
if s1 is None:
s2 = Settlement.query.get({
"left_user_id": to_user_id,
"right_user_id": from_user_id,
})
if s2 is None:
return None
else:
return s2
else:
return s1
def get_other_user_id(self, user_id):
if user_id == self.left_user_id:
return self.right_user_id
elif user_id == self.right_user_id:
return self.left_user_id
return None
def get_paid_amount(self, user_id):
return get(self, user_id, "paid_amount", Decimal(0.0))
def get_owed_amount(self, user_id):
return get(self, user_id, "owed_amount", Decimal(0.0))
def get_diff_amount(self, user_id):
return get(self, user_id, "diff_amount", Decimal(0.0))
def add_payment(self, payment):
"""
add payment and apply balances if possible.
return true if payment added
"""
if add(self, payment.from_user_id, "paid_amount", payment.amount):
self.pay_balances()
return True
return False
def remove_payment(self, payment):
"""
remove payment and apply balances if possible.
return true if payment removed successfully
"""
if add(self, payment.from_user_id, "paid_amount", -1 * payment.amount):
self.pay_balances()
return True
return False
def apply_balance(self, balance):
"""
applies balance to settlement
return False if did not add balance to settlement, else return True
"""
if balance.paid:
# dont apply if paid
app.logger.debug("APPLY BALANCE balance already paid")
return False
if not is_valid_user(self, balance.from_user_id) or \
not is_valid_user(self, balance.to_user_id):
# if from or to balance user is invalid, then return false
return False
if balance.to_user_id == balance.from_user_id:
return False
# balance: from_user pays to_user
paid_amount = self.get_paid_amount(balance.from_user_id)
app.logger.debug("APPLY BALANCE paid %s bal %s", paid_amount,
balance.amount)
if paid_amount < balance.amount:
# if cannot pay right away for balance
# add to balance owed_amount
add(self, balance.from_user_id, "owed_amount", balance.amount)
return True
# otherwise, subtract from paid_amount for the balance and set balance
# to paid. Do not change owed_amount
add(self, balance.from_user_id, "paid_amount", -1 * balance.amount)
balance.paid = True
app.logger.debug("bal_OK! paid %s, owed %s cur_bal %s",
self.paid_amount, self.owed_amount, balance.amount)
return True
def add_balance_back(self, balance):
"""
reverts added balance from settlement
return False if did not revert to settlement, else delete and return
True
"""
app.logger.debug("ADD_BALANCE_BACK")
app.logger.debug("\tbal_id=%s settlement %s->%s", balance.id,
balance.from_user_id, balance.to_user_id)
if not is_valid_user(self, balance.from_user_id) or \
not is_valid_user(self, balance.to_user_id):
# if from or to balance user is invalid, then return false
return False
if balance.to_user_id == balance.from_user_id:
app.logger.debug("\tDid not add back, as balance is to self")
db.session.delete(balance)
return True
if balance.paid:
app.logger.debug("\tbalance paid, so add back to paid_amount")
add(self, balance.from_user_id, "paid_amount", balance.amount)
else:
# if paid balance, remove from balance in settlement.
add(self, balance.from_user_id, "owed_amount", -1 * balance.amount)
db.session.delete(balance)
return True
def pay_balances(self):
app.logger.debug("PAY_BALANCES")
balances = []
check_user_id = None
app.logger.debug("\t(before) paid_amount=%s balance_amount=%s",
self.paid_amount, self.owed_amount)
if self.paid_amount > 0:
# left user has payment to adjust balances
check_user_id = self.left_user_id
app.logger.debug("\tcheck_user left %s", check_user_id)
elif self.paid_amount < 0:
check_user_id = self.right_user_id
app.logger.debug("\tcheck_user right %s", check_user_id)
if check_user_id is not None:
balances = Balance.query.filter(
Balance.from_user_id == check_user_id,
# balances user must pay
Balance.to_user_id != check_user_id,
# no self addressed
Balance.paid.is_(False) # only get unpaid balances
).order_by(Balance.created_on).all()
else:
app.logger.debug("\tERROR check_user_id is None")
return
for balance in balances:
# balances are all "check_user_id pays to"
app.logger.debug("\tbalance from %s->%s", balance.from_user_id,
balance.to_user_id)
app.logger.debug("\t\tpaid_amount=%s balance_amount=%s",
self.get_paid_amount(check_user_id),
balance.amount)
if self.get_paid_amount(check_user_id) >= balance.amount:
add(self, check_user_id, "paid_amount", -1 * balance.amount)
add(self, check_user_id, "owed_amount", -1 * balance.amount)
balance.paid = True
app.logger.debug("\t(final) paid_amount=%s balance_amount=%s",
self.paid_amount, self.owed_amount)
