Commits

Dan Connolly committed 068cea5

budget subtotal report

Comments (0)

Files changed (1)

 
 import csv
 import logging
+from pprint import pformat
 
 from sqlalchemy import (MetaData, Table, Column,
                         select, func, text)
 
     budget.check_dups()
     budget.sync_accounts(dry_run='--accounts' not in argv)
-    budget.sync_items()
+    if '--subtot' in argv:
+        budget.compare_subtots()
+    else:
+        budget.sync_items()
 
 
 GnuCashAux = MetaData()
                       format_rows(dups))
             raise IOError
 
+    subtot_q = '''select gcb.*, gdb.subtot,
+      case when gcb.subtot = gdb.subtot then '' else 'MISMATCH' end ok
+    from
+    (select b.name budget_name, a.account_type, p.name parent,
+    sum(amount_num / 100.0) subtot
+    from budgets b
+    join budget_amounts ba on ba.budget_guid = b.guid
+    join accounts a on a.guid = ba.account_guid
+    join accounts p on a.parent_guid = p.guid
+    where b.name in (%(budget_name)s)
+    group by b.name, a.account_type, p.name) gcb
+    join (
+      select budget_name, account_type, parent,
+      sum(amount_num / 100.0) subtot
+      from gdocs_budget bi
+      where bi.code > ''
+      and budget_name in (%(budget_name)s)
+      group by budget_name, account_type, parent
+    ) gdb
+      on gdb.budget_name = gcb.budget_name
+      and gdb.account_type = gcb.account_type
+      and gdb.parent = gcb.parent
+    '''
+
+    def compare_subtots(self):
+        conn = self._engine.connect()
+        ans = conn.execute(self.subtot_q, budget_name='2013 Q2')
+        log.info('subtots:\n%s', pformat(ans.fetchall()))
+
     def sync_items(self):
         # TODO: update, rather than delete all and re-insert
         conn = self._engine.connect()