def monthly(from_date, to_date):
from_date = from_date.replace(day=1)
to_date = to_date.replace(day=1) + monthdelta.MonthDelta(1)
months = []
tmpdate = datetime.date(from_date.year, from_date.month, from_date.day)
while tmpdate < to_date:
months.append(datetime.date(tmpdate.year, tmpdate.month, 1))
tmpdate += monthdelta.MonthDelta(1)
cats = db.session.query(Category).all()
catids = dict([(c.id, c.name) for c in cats])
keys = [m.strftime('%Y-%m') for m in months]
ts = db.session.query(Transaction)\
.filter(Transaction.bdate>=from_date, Transaction.bdate<to_date)
cat_keys = {}
results = {}
for t in ts:
if t.category.id not in cat_keys:
cat_keys[t.category.id] = [t.category.id]
tmp = t.category.parent
while tmp.name != 'top':
cat_keys[t.category.id].append(tmp.id)
tmp = tmp.parent
cat_keys[t.category.id].append(tmp.id)
for cid in cat_keys[t.category.id]:
results[cid] = results.get(cid) or dict(
[(k, []) for k in keys + ['yearly']])
if t.yearly:
results[cid]['yearly'].append(t.amount)
else:
results[cid][t.bdate.strftime('%Y-%m')].append(t.amount)
table = {
'headings': ['category', 'average'] + keys[::-1] + ['yearly', 'total'],
'rows': []
}
top = db.session.query(Category).filter(
Category.name == 'transactions').first()
for cat in [top] + allChildren(top):
cols = [(sum(results.get(cat.id, {
k: []
}).get(k, []), 0.0), k) for k in keys[::-1] + ['yearly']]
table['rows'].append({
'category':
cat,
'data':
cols[:-1],
'yearly':
cols[-1][0],
'total':
sum([c[0] for c in cols]),
'average':
"%.2f" % (sum([c[0] for c in cols[:-1]]) / (len(cols) - 1)),
})
return table
def transactions(category_id=1):
return db.session.query(Transaction).join(Category).filter(
Category.id.in_(
[category_id] + [c.id for c in allChildren(category_id)]
),
Transaction.date>=datetime.date(2016, 1, 1),
).order_by(Transaction.date).all()
def monthly_breakdown(from_date, to_date):
from_date = from_date.replace(day=1)
to_date = to_date.replace(day=1) + relativedelta(months=1)
months = []
tmpdate = datetime.date(from_date.year, from_date.month, from_date.day)
while tmpdate < to_date:
months.append(datetime.date(tmpdate.year, tmpdate.month, 1))
tmpdate += relativedelta(months=1)
cats = db.session.query(Category).all()
catids = dict([(c.id, c.name) for c in cats])
keys = [m.strftime('%Y-%m') for m in months]
ts = db.session.query(Transaction)\
.filter(Transaction.date>=from_date, Transaction.date<to_date)
cat_keys = {}
results = {}
for t in ts:
if t.category.id not in cat_keys:
cat_keys[t.category.id] = [t.category.id]
tmp = t.category.parent
while tmp.name != 'top':
cat_keys[t.category.id].append(tmp.id)
tmp = tmp.parent
cat_keys[t.category.id].append(tmp.id)
for cid in cat_keys[t.category.id]:
results[cid] = results.get(cid) or dict([(k, []) for k in keys + ['yearly']])
if t.yearly:
results[cid]['yearly'].append(t.amount)
else:
results[cid][t.date.strftime('%Y-%m')].append(t.amount)
table = {'headings': ['category', 'average'] + keys[::-1] + ['yearly', 'total'], 'rows': []}
top = db.session.query(Category).filter(Category.name=='transactions').first()
for cat in [top]+allChildren(top):
cols = [(sum(results.get(cat.id, {k: []}).get(k, []), 0.0), k) for k in keys[::-1] + ['yearly']]
table['rows'].append({
'category': {
'id': cat.id,
'name': cat.name,
'parent_id': (None if not cat.parent else cat.parent.id),
},
'data': cols[:-1],
'yearly': cols[-1][0],
'total': sum([c[0] for c in cols]),
'average': (sum([c[0] for c in cols[:-1]]) / (len(cols)-1)),
})
return table
def transactions(category_id=None, name=None):
form = TransactionsForm()
form.category.choices = categoriesSelectBox()
if form.validate_on_submit():
category_id = form.category.data
session['startdate'] = str(form.startdate.data)
session['enddate'] = str(form.enddate.data)
elif category_id:
form.category.data = category_id
if 'startdate' in session:
form.startdate.data = datetime.date(
*map(int, session['startdate'].split('-')))
if 'enddate' in session:
form.enddate.data = datetime.date(
*map(int, session['enddate'].split('-')))
q = db.session.query(Transaction)
if form.category.data:
c = db.session.query(Category).filter(
Category.id == form.category.data).first()
children = [c] + allChildren(c)
q = q.join(Category).filter(
Category.id.in_([child.id for child in children]))
if name:
name = unquote(name)
q = q.filter(Transaction.name == name)
q = q.filter(
Transaction.bdate >= form.startdate.data,
Transaction.bdate <= form.enddate.data,
).order_by(desc(Transaction.bdate))
graph = collections.defaultdict(float)
for tran in q:
if not tran.yearly:
graph[tran.bdate.replace(day=1)] += tran.amount
return render_template(
'transactions.html',
form=form,
transactions=[q],
title='Transactions',
graph=collections.OrderedDict([(k, abs(v))
for k, v in sorted(graph.items())]),
)
def tritransactions(category_id, month):
yyyy, mm = map(int, month.split('-'))
date = datetime.date(yyyy, mm, 1) - MonthDelta(1)
cat_ids = [category_id] + \
[c.id for c in allChildren(db.session.query(Category).filter(Category.id==category_id).first())]
tables = []
for _ in range(3):
tables.append( db.session.query(Transaction).join(Category)\
.filter(Category.id.in_(cat_ids),
Transaction.bdate>=date,
Transaction.bdate<date + MonthDelta(1),
).order_by(Transaction.bdate).all()
)
date += MonthDelta(1)
return render_template('transactions.html', form=None, transactions=tables)
def test_one(self):
top = db.session.query(Category).filter(Category.name == 'top').first()
assert top.name == 'top'
a = db.session.query(Category).filter(Category.name == 'a').first()
assert a.parent.name == 'top'
assert a.depth == 1
assert set([
db.session.query(Category).filter(Category.id==c.id).first().name
for c in allChildren(a)]
), \
set(['a1', 'a1i', 'a1ii', 'a2'])
'''
top (8.0)
A [1.0] (7.0)
a1 [1.0, 1.0] (3.0)
a1i
a1ii [1.0]
a2
a3 [3.0]
B [] (1.0)
b1 [1.0]
'''
exp = [
['category', '2015-05', 'Average'],
['top', 8.0, 8.0],
['a', 7.0, 7.0],
['a1', 3.0, 3.0],
['a1i', 0.0, 0.0],
['a1ii', 1.0, 1.0],
['a2', 0.0, 0.0],
['a3', 3.0, 3.0],
['b', 1.0, 1.0],
['b1', 1.0, 1.0],
]
self.assertEqual(exp, monthly(date(2015, 5, 1), date(2015, 5, 25)))
