﻿#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys
from datetime import datetime, timedelta
#import codecs
#codecs.register(lambda name: codecs.lookup('utf-8') if name == 'cp65001' else None)

def out ( unicodeobj ): 
  sys.stdout.write ( unicodeobj.encode('utf-8') )
  sys.stdout.write ( '\t' )

gctimeformat = '%Y%m%d%H%M%S'
  
#print sys.getdefaultencoding()
#print sys.getfilesystemencoding()
#print "Encoding is", sys.stdout.encoding
#out ( sys.argv[1].decode(sys.getfilesystemencoding()) )

con = lite.connect ( sys.argv[1].decode(sys.getfilesystemencoding()) )

with con:
  
  cur = con.cursor()
  
  # time range
  cur.execute ( "select min(post_date), max(post_date) from transactions" );
  x = cur.fetchone();
  #dt = datetime.strptime ( x[0], gctimeformat );
  dt = datetime.strptime ( '20150101000000', gctimeformat );
  verystart = dt - timedelta(dt.weekday())
  #dt = datetime.strptime ( x[1], gctimeformat );
  dt = datetime.now()
  veryend = dt - timedelta(dt.weekday()) + timedelta(7)
  #print ( verystart, veryend );
  
  # all expense accounts
  cur.execute ( "select name, parent_guid, a.guid, c.mnemonic from accounts a join commodities c on a.commodity_guid=c.guid where account_type='EXPENSE' and placeholder=0" );
  res = cur.fetchall()
  accs = []
  for x in res:
    name = []
    parent = x[1]
    acc = { 'guid': x[2], 'currency': x[3] }
    cparentacc = con.cursor()
    while parent != None:
      name.append ( x[0] )
      cparentacc.execute ( "select name, parent_guid from accounts where guid=?", (parent,) )
      x = cparentacc.fetchone()
      parent = x[1]
    acc['name'] = [i for i in reversed(name)]
    #out ( ':'.join(acc['name']) )
    accs.append(acc)
    
  # now accs contains list of expense accounts we are interested in 
  
  # print table header
  out ( 'Account' )
  out ( 'Currency' )
  weekst = verystart
  while weekst < veryend:
    out ( weekst.date().isoformat() )
    weekst += timedelta(7)
  print
    
  # get weekly history for every expense account
  for acc in accs:
    weekst = verystart
    out ( ':'.join(acc['name']) )
    out ( acc['currency'] )
    while weekst < veryend:
      weeklo = weekst
      weekst += timedelta(7)
      cur.execute ( "select sum(cast(quantity_num as numeric(10,2))/100.0) from transactions t join splits s on s.tx_guid=t.guid "
        "where s.account_guid=? and t.post_date>=? and t.post_date<?"
        "and reconcile_state in ('y','c','n')", (acc['guid'],weeklo.strftime(gctimeformat),weekst.strftime(gctimeformat)) )
      x = cur.fetchone()
      if x[0] == None:
        newbalance = 0
      else:
        newbalance = x[0]
      out ( str(newbalance) )
    print
      
@@ -91,16 +91,22 @@
 e import
+ date,
  datetim
@@ -118,16 +118,16 @@
 medelta%0A
-
 #import 
@@ -855,17 +855,23 @@
 format )
-;
+.date()
 %0A  verys
@@ -971,16 +971,79 @@
 date
-time.now
+.today()%0A  budgetSince = dt - timedelta(dt.weekday())%0A  dt = date.today
 ()%0A 
@@ -1091,24 +1091,26 @@
  timedelta(7
+*4
 )%0A  #print (
@@ -1128,16 +1128,29 @@
  veryend
+, budgetSince
  );%0A  %0A 
@@ -2016,15 +2016,8 @@
 kst.
-date().
 isof
@@ -2023,16 +2023,203 @@
 format()
+ )%0A    weekst += timedelta(7)%0A  print%0A  out ('')%0A  out ('')%0A  weekst = verystart%0A  while weekst %3C veryend:%0A    if weekst %3C budgetSince:%0A      out ( 'fact' )%0A    else:%0A      out ( 'budget'
  )%0A    w

Determine intervals for budgeting