def refresh(self, concurrently=False):
"""Refreshes the current materialized view"""
if not self.materialized:
raise ValueError("Cannot refresh a non-materialized view")
_con = 'CONCURRENTLY ' if concurrently else ''
session.execute('REFRESH MATERIALIZED VIEW ' + _con + self.name)
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 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 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)
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)