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)