Commits

Dan Connolly committed 5fb3af5

sync'd accounts and budget items

Comments (0)

Files changed (1)

 import csv
 import logging
 
-from sqlalchemy import MetaData, Table, Column, func
+from sqlalchemy import MetaData, Table, Column, select, func
 from sqlalchemy.types import String
 from sqlalchemy.engine.url import URL
 
 
     budget_load(open_read(gdoc_csv), engine)
 
+    budget_check_dups(engine)
     budget_sync_accounts(engine, dry_run='--accounts' not in argv)
     budget_sync_items(engine)
 
 
 
 def budget_sync_accounts(conn, dry_run=True):
-    missing_acct = conn.execute(
-'''
+    missing_acct = conn.execute('''
 select bi.parent, a.name, count(*) from
 (select distinct parent from gdocs_budget
 where code > '' and t_lo > '') bi
         if dry_run:
             raise IOError
 
-    raise NotImplemented
-
-'''
-insert into accounts
-select replace(uuid(), '-', '') as guid
-     , bi.name, bi.account_type
-     , usd.guid as commodity_guid
-     , usd.fraction as commodity_scu, 0 as non_std_scu
-     , ap.guid as parent_guid
-     , bi.code
-     , bi.notes as description, 0 as hidden, 0 as placeholder
-from (
-  select distinct account_type, parent, name, code
-  from gdocs_budget
-  where code > '' ) bi
-left join accounts a on a.code = bi.code
-left join accounts ap on ap.name = bi.parent
-join commodities usd on usd.mnemonic = 'USD'
-where a.guid is null
-'''
+    conn.execute('insert into accounts ' + missing_acct_q)
 
 
 def format_rows(rows):
     return '\n'.join([str(row) for row in rows])
 
 
-def budget_sync_items(conn):
-    key_cols = [BudgetItem.budget_name,
-                BudgetItem.t_lo,
-                BudgetItem.code]
+def budget_check_dups(conn):
+    key_cols = [BudgetItem.c.budget_name,
+                BudgetItem.c.t_lo,
+                BudgetItem.c.code]
     dups = conn.execute(
-        BudgetItem.select(key_cols + [func.count()]).
-        group_by(key_cols).having(func.count() > 1)).fetchall()
+        select(key_cols + [func.count()]).
+        where(BudgetItem.c.code > '').
+        group_by(*key_cols).having(func.count() > 1)).fetchall()
     log.debug('dups: %d', len(dups))
 
     if dups:
                   format_rows(dups))
         raise IOError
 
-    raise NotImplementedError
+
+def budget_sync_items(conn):
+    # TODO: update, rather than delete all and re-insert
+
+    log.info('deleting budget_amounts...')
+    conn.execute('''
+delete
+-- select *
+from budget_amounts
+where budget_guid in (
+  select b.guid
+  from budgets b
+  where b.name in (
+    select distinct budget_name from gdocs_budget)
+)
+''')
+
+    log.info('inserting budget_amounts ...')
+    conn.execute('''
+insert into budget_amounts
+select -- distinct bi.t_lo, bi.code, bi.name
+       null id,
+         b.guid budget_guid,
+       a.guid account_guid,
+       mod(month(t_lo) - 1, 3) period_num,
+       budget * 100 * (
+         case a.account_type
+           when 'INCOME' then 1
+           when 'EXPENSE' then -1
+           when 'LIABILITY' then -1
+           else 1/0
+         end
+       ) amount_num,
+       100 amount_denom
+from (
+ select budget_name,
+        STR_TO_DATE(bi.t_lo,'%%m/%%d/%%Y') t_lo,
+        account_type, bi.code, parent, bi.name,
+        1 * replace(replace(budget, '$', ''), ',', '') budget
+ from gdocs_budget bi
+ where bi.code > ''
+ ) bi
+join budgets b
+on b.name=bi.budget_name
+join accounts a
+on a.code=bi.code
+
+-- and b.guid is null
+-- order by bi.budget_name, bi.code, t_lo
+''')
 
 
 if __name__ == '__main__':