def balance_json(account_id):
balance_json = (select([
Transaction.valid_on,
func.sum(
Split.amount).over(order_by=Transaction.valid_on).label("balance")
]).select_from(
Join(Split, Transaction,
Split.transaction_id == Transaction.id)).where(
Split.account_id == account_id))
res = session.execute(json_agg_core(balance_json)).first()[0]
return jsonify(items=res)
def comments(self):
# The relationship here is not a classical many-to-many because the association
# table has only one foreign key and not two as usual. Therefore, I have
# to specify a secondary join. It is view only because I don't think SQLA
# will be able to handle the cascade updates easily.
return relationship(
Comment,
secondary=Join(
CommentLocation, Comment,
Comment.location_id == CommentLocation.comment_location_id),
order_by=Comment.creation,
viewonly=True)
def balance_json(account_id):
invert = request.args.get('invert', 'False') == 'True'
sum_exp = func.sum(Split.amount).over(order_by=Transaction.valid_on)
if invert:
sum_exp = -sum_exp
balance_json = (select(
[Transaction.valid_on, sum_exp.label("balance")]).select_from(
Join(Split, Transaction,
Split.transaction_id == Transaction.id)).where(
Split.account_id == account_id))
res = session.execute(json_agg_core(balance_json)).first()[0]
return jsonify(items=res)
def transactions_all_json():
lower = request.args.get('after', "")
upper = request.args.get('before', "")
filter = request.args.get('filter', "nonuser")
if filter == "nonuser":
non_user_transactions = (select([Split.transaction_id]).select_from(
Join(Split,
User, (User.account_id == Split.account_id),
isouter=True)).group_by(Split.transaction_id).having(
func.bool_and(User.id == None)).alias("nut"))
tid = literal_column("nut.transaction_id")
transactions = non_user_transactions.join(Transaction,
Transaction.id == tid)
else:
transactions = Transaction.__table__
q = (select([
Transaction.id, Transaction.valid_on, Split.account_id, Account.type,
Split.amount
]).select_from(
transactions.join(Split, Split.transaction_id == Transaction.id).join(
Account, Account.id == Split.account_id)))
try:
datetime.strptime(lower, "%Y-%m-%d").date()
except ValueError:
not lower or abort(422)
else:
q = q.where(Transaction.valid_on >= lower)
try:
datetime.strptime(upper, "%Y-%m-%d").date()
except ValueError:
not upper or abort(422)
else:
q = q.where(Transaction.valid_on <= upper)
res = session.execute(json_agg_core(q)).fetchone()[0] or []
return jsonify(items=res)