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)))