def trial_balance(currency, groupby, period):
if groupby == None:
period = datetime.now()
lastday = calendar.monthrange(period.year, period.month)[1]
year = period.year
month = period.month
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0).strftime('%Y-%m-%d')
period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999).strftime('%Y-%m-%d')
return redirect(
url_for('bookkeeping.trial_balance',
currency='Satoshis',
groupby='Monthly',
period='Current'))
if groupby == 'Daily':
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.order_by( \
func.date_part('year', models.LedgerEntries.date).desc(), \
func.date_part('month', models.LedgerEntries.date).desc(), \
func.date_part('day', models.LedgerEntries.date).desc()) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.limit(7)
periods = sorted([date(int(period[0]), int(period[1]), int(period[2])) for period in periods])
if period == 'Current':
period = periods[-1]
else:
period = datetime.strptime(period, "%Y-%m-%d")
period_beg = datetime(period.year, period.month, period.day, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, period.day, 23, 59, 59, 999999)
periods = sorted([period.strftime("%Y-%m-%d") for period in periods])
period = period.strftime("%Y-%m-%d")
elif groupby == 'Weekly':
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('week', models.LedgerEntries.date)) \
.order_by( \
func.date_part('year', models.LedgerEntries.date).desc(), \
func.date_part('week', models.LedgerEntries.date).desc()) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('week', models.LedgerEntries.date)) \
.limit(7)
periods = sorted([Week(int(period[0]), int(period[1])).monday() for period in periods])
if period == 'Current':
period = periods[-1]
else:
period = period.split('-')
period = Week(int(period[0]), int(period[1])+1).monday()
period_beg = period - timedelta(days = period.weekday())
period_end = period_beg + timedelta(days = 6)
period_beg = datetime(period_beg.year, period_beg.month, period_beg.day, 0, 0, 0, 0)
period_end = datetime(period_end.year, period_end.month, period_end.day, 23, 59, 59, 999999)
periods = sorted([period.strftime("%Y-%W") for period in periods])
period = period.strftime("%Y-%W")
elif groupby == 'Monthly':
if period == 'Current':
period = datetime.now()
else:
period = datetime.strptime(period, "%Y-%m")
lastday = calendar.monthrange(period.year, period.month)[1]
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = period = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
periods = sorted([period.strftime("%Y-%m") for period in periods])
period = period.strftime("%Y-%m")
elif groupby == 'Annual':
if period == 'Current':
period = datetime.now()
else:
period = datetime.strptime(period, "%Y")
period_beg = datetime(period.year, 1, 1, 0, 0, 0, 0)
period_end = datetime(period.year, 12, 31, 23, 59, 59, 999999)
periods = db.session \
.query(func.date_part('year', models.LedgerEntries.date)) \
.group_by(func.date_part('year', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), 12, 31) for period in periods])
periods = sorted([period.strftime("%Y") for period in periods])
period = period.strftime("%Y")
subaccounts = db.session \
.query( \
models.LedgerEntries.ledger, \
func.sum(models.LedgerEntries.debit), \
func.sum(models.LedgerEntries.credit)) \
.filter(models.LedgerEntries.currency==currency) \
.filter( models.LedgerEntries.date.between(period_beg, period_end)) \
.group_by(models.LedgerEntries.ledger) \
.all()
totalDebits = 0
totalCredits = 0
for subaccount in subaccounts:
totalDebits += subaccount[1]
totalCredits += subaccount[2]
return render_template('trial_balance.html',
groupby=groupby,
currency=currency,
periods=periods,
period=period,
period_beg=period_beg,
period_end=period_end,
totalDebits=totalDebits,
totalCredits=totalCredits,
subaccounts=subaccounts)
