def reports(request): try: year = int(request.matchdict['year']) except KeyError: # no matchdict year = datetime.datetime.now().year session = DBSession() if session.query(Transaction.date).\ filter_by(year=year - 1).first() is None: previous_year = None else: previous_year = year - 1 if session.query(Transaction.date).\ filter_by(year=year + 1).first() is None: next_year = None else: next_year = year + 1 lines = session.query(Transaction).\ filter_by(year=year, composite=False).all() report = _calculate_report(lines) _update_report_with_fixed_assets(report, year, session) _update_report_with_vat(report, lines) report['vat_installments'] = accounting.calculate_vat_installments( session.query(Transaction), year) # FIXME: move these to separate function(s)? report['clients'] = ((party, accounting.Price(net_amount)) for party, net_amount in session.execute( 'SELECT party, SUM(net_amount) AS sum ' 'FROM transactions ' 'WHERE category=%d ' 'AND year=%d ' 'GROUP BY party ' 'ORDER BY sum DESC' % (CATEGORY_INCOME_MISC, year))) report['providers'] = ((party, accounting.Price(net_amount)) for party, net_amount, in session.execute( 'SELECT party, SUM(net_amount) AS sum ' 'FROM transactions ' 'WHERE category NOT IN (%s, %s) ' 'AND composite = 0 ' 'AND part_of IS NULL ' 'AND year=%d ' 'GROUP BY party ' 'ORDER BY sum DESC' % ( CATEGORY_INCOME_MISC, CATEGORY_REMUNERATION, year))) report['remuneration'] = [(date, accounting.Price(net_amount)) for date, net_amount in session.query( Transaction.date, Transaction.net_amount).\ filter_by(category=CATEGORY_REMUNERATION, year=year).\ order_by(Transaction.date)] report['total_remuneration'] = sum( r[1] for r in report['remuneration']) bindings = {'api': TemplateAPI(request, 'reports'), 'year': year, 'previous_year': previous_year, 'next_year': next_year, 'report': report} return render_to_response('templates/reports.pt', bindings)