def cmd_db_reset(settings):
"""
Drop all tables and recreate schema.
"""
get_session(settings.dbref)
if not settings.yes:
x = raw_input("This will destroy all data? [yN] ")
if not x or x not in 'Yy':
return 1
metadata.drop_all()
cmd_db_init(settings)
def cmd_db_stats(settings):
"""
Print table record stats.
"""
sa = get_session(settings.dbref)
print "Accounts:", sa.query(Account).count()
print "Mutations:", sa.query(Mutation).count()
def cmd_balance_commit(settings):
"""
TODO: Commit current balance, insert corrections where needed.
"""
sa = get_session(settings.dbref)
cmd_balance_verify(settings, sa=sa)
accounts, balance = fetch_expense_balance(settings, sa=sa)
if lib.Prompt.ask("End balance %s, correct?" % balance):
return 0
ia = int(lib.Prompt.raw_input("Which account [0-%i]" % (len(accounts)-1)))
account = accounts[ia]
print ia, account
v = float(lib.Prompt.raw_input("Enter the actual balance"))
correction = v - balance
print 'Correction: ', balance, correction, balance+correction
d = None
while not d:
sd = lib.Prompt.raw_input("Enter a year, month, or full day to enter",
datetime.now().strftime('%Y-%m-%d'))
for fmt in '%Y-%m-%d', '%Y-%m', '%Y':
try:
d = datetime.strptime(sd, fmt)
except:
pass
c = Mutation(
amount=correction,
# XXX circular ledger, isnt really valid..
from_account=account.account_id,
to_account=account.account_id,
category='sb',
day=d.day, month=d.month, year=d.year,
description="Correction/starting balance"
)
print c
if lib.Prompt.ask("Commit?"):
sa.add(c)
sa.commit()
return 0
return 1
def cmd_month(settings):
"""
Print balance change per month.
--first-month --last-month
"""
sa = get_session(settings.dbref)
start = settings.first_month \
if 'first_month' in settings and settings.first_month else datetime.now()
end = settings.last_month \
if 'last_month' in settings and settings.last_month else datetime.now()
print '# Range: ', start.year, start.month, '--', end.year, end.month
balance = 0
last3 = Simplemovingaverage(3)
last6 = Simplemovingaverage(6)
last12 = Simplemovingaverage(12)
last24 = Simplemovingaverage(24)
print '# year, change, amount, balance, avg6, avg12'
for year in range(start.year, end.year+1):
if year == start.year and year == end.year:
months = range(start.month, end.month+1)
elif year == start.year:
months = range(start.month, 13)
elif year == end.year:
months = range(1, end.month+1)
else:
months = range(1, 13)
for month in months:
amount, = sa.query(func.sum(Mutation.amount))\
.filter( Mutation.year == year, Mutation.month == month ).one()
if amount:
balance += amount
avg3 = last3(amount)
avg6 = last6(amount)
avg12 = last12(amount)
avg24 = last24(amount)
print ("%(year)04i-%(month)02i "\
"%(balance)9.2f EUR "\
"%(amount)9.2f "\
"%(avg3)9.2f "\
"%(avg6)9.2f %(avg12)9.2f %(avg24)9.2f" ) % locals()
def cmd_balance_verify(settings, sa=None):
"""
Print balance for expence accounts.
"""
if not sa:
sa = get_session(settings.dbref)
expenses_acc = Account.all((Account.name.like(ACCOUNT_CREDIT+'%'),), sa=sa)
balances = [ sa.query(func.sum(Mutation.amount))\
.filter( Mutation.from_account == acc.account_id )
.one() for acc in expenses_acc ]
for i, (sub, (sub_balance,)) in enumerate(zip(expenses_acc, balances)):
print i, sub.name, sub.iban or sub.nl_number or sub.nl_p_number, sub_balance
return 0
def cmd_mutation_list(settings, opts):
"""
List mutation ID, year, from-/to-account and amount.
"""
sa = get_session(settings.dbref)
if opts['<id>']:
accid = int(opts['<id>'])
for m in sa.query(Mutation).filter(
Mutation.from_account == accid or
Mutation.to_account == accid
).all():
print m.mut_id, m.year, m.from_account, m.to_account, m.amount
else:
for m in sa.query(Mutation).all():
print m.mut_id, m.year, m.from_account, m.to_account, m.amount
return 0
def cmd_db_init(settings):
"""
Initialize if the database file doest not exists,
and update schema.
"""
sa = get_session(settings.dbref)
# XXX: update schema..
metadata.create_all()
accounting_acc = Account(name=ACCOUNT_ACCOUNTING)
accounting_acc.init_defaults()
expenses_acc = Account(name=ACCOUNT_EXPENSES)
expenses_acc.init_defaults()
credit_acc = Account(name=ACCOUNT_CREDIT)
credit_acc.init_defaults()
sa.add(accounting_acc)
sa.add(expenses_acc)
sa.add(credit_acc)
sa.commit()
def cmd_corrections(settings):
sa = get_session(settings.dbref)
for rs in sa.query( Mutation ).filter( Mutation.category == 'sb' ).all():
print rs
def cmd_mutation_import(opts, settings):
"""
Import mutations from CSV, create accounts as needed.
Indx with Year, Month.
"""
sa = get_session(settings.dbref)
if settings.reset or lib.Prompt.ask("Purge mutations?"):
sa.query(Mutation).delete()
log.std("Purged all previous mutations")
assert settings.input_format == 'csv', settings.input_format
cache = confparse.Values(dict(
accounts={}, years={}, months={}
))
for csvfile in opts.args.file:
reader = csv_reader(csvfile, [
'line', 'date', 'accnr', 'amount', 'destacc', 'cat',
'destname', 'descr', 'descr2' ])
for line, date, accnr, amount, destacc, cat, destname, descr, descr2 in reader:
from_account, to_account = None, None
assert accnr, (line, date, amount, cat)
# from_account
if accnr not in cache.accounts:
from_account = Account.for_nr(sa, accnr)
if not from_account:
from_account = Account(name=ACCOUNT_CREDIT+':'+accnr)
from_account.init_defaults()
from_account.set_nr(accnr)
sa.add(from_account)
sa.commit()
cache.accounts[accnr] = from_account
else:
from_account = cache.accounts[accnr]
assert from_account.account_id, (str(from_account), line, accnr, date, amount, cat)
# credit account
if not destacc:
if cat == 'ba':
# payment card checkout
to_account = Account.for_checkout(sa,
ACCOUNT_EXPENSES+':ba:'+descr)
elif cat == 'ga':
# atm withdrawal
to_account = Account.for_withdrawal(sa,
ACCOUNT_EXPENSES+':ga:'+descr)
elif cat == 'db':
# debet interest
to_account = Account.for_name_type(sa, ACCOUNT_ACCOUNTING)
else:
print line, date, accnr, amount, cat, descr, descr2
assert not destname, (cat, destname, cat)
continue
# billing account
elif destacc not in cache.accounts:
to_account = Account.for_nr(sa, destacc)
if not to_account:
to_account = Account(name=ACCOUNT_EXPENSES+':'+destname)
to_account.init_defaults()
to_account.set_nr(destacc)
sa.add(to_account)
sa.commit()
cache.accounts[destacc] = to_account
else:
to_account = cache.accounts[destacc]
# get Year/Month
y, m, d = map(int, ( date[:4], date[4:6], date[6:]))
if y not in cache.years:
pass
mut = Mutation(
from_account=from_account.account_id,
to_account=to_account.account_id,
year=y, month=m, day=d,
amount=amount, description=descr+'\t'+descr2,
category=cat)
sa.add(mut)
sa.commit()
log.std("Import ready")
cmd_balance_commit(settings)
def cmd_account_rm(settings, name):
sa = get_session(settings.dbref)
acc = sa.query(Account).filter(Account.name == name).one()
sa.delete(acc)
sa.commit()
log.std("Dropped account %s", name)
def cmd_account_add(props, settings, name):
sa = get_session(settings.dbref)
acc = Account(name=name)
sa.add(acc)
sa.commit()
log.std("Added account %s", name)
def cmd_account_list(settings):
sa = get_session(settings.dbref)
for acc in sa.query(Account).all():
print "\t".join(map(str,(acc.account_id, acc.name)))
def cmd_account_show(settings):
sa = get_session(settings.dbref)
accid = int(opts['<name>'])
for acc in sa.query(Account).filter(Account.account_id==accid).all():
print "\t".join(map(str,(acc.account_id, acc.name, acc.iban)))
