Commits

Dan Connolly committed ea23df3

refine budget_sync_accounts

Comments (0)

Files changed (1)

 import csv
 import logging
 
-from sqlalchemy import MetaData, Table, Column
+from sqlalchemy import MetaData, Table, Column, func
 from sqlalchemy.types import String
 from sqlalchemy.engine.url import URL
 
                    Column('t_lo', String(40)),  # date
                    Column('account_type', Name),
                    Column('code', Name),
+                   Column('parent', Name),
                    Column('name', Name),
                    Column('budget', String(20)),  # amount
                    Column('notes', String(200)))
 
 
 def budget_sync_accounts(conn, dry_run=True):
-    missing_acct = conn.execute('''
-select bi.* 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 budget_import bi
- where bi.code > '') bi
+    missing_acct = conn.execute(
+'''
+select bi.parent, a.name, count(*) from
+(select distinct parent from gdocs_budget
+where code > '' and t_lo > '') bi
 left join accounts a
-on a.code=bi.code
+       on a.name=bi.parent
+group by bi.parent, a.name
+having count(*) != 1
+''').fetchall()
+
+    log.debug('parent mismatch: %d', len(missing_acct))
+
+    if missing_acct:
+        log.error('parent mismatch:\n%s',
+                  format_rows(missing_acct))
+        raise IOError
+
+    missing_acct_q = '''
+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
+     , '' as description, 0 as hidden, 0 as placeholder
+from (
+  select distinct account_type, parent, name, code
+  from gdocs_budget
+  where code > '' ) bi
+join accounts ap on ap.name = bi.parent
+join commodities usd on usd.mnemonic = 'USD'
+left join accounts a on a.code = bi.code
 where a.guid is null
-''').fetchall()
+'''
+    missing_acct = conn.execute(missing_acct_q).fetchall()
 
     log.debug('missing_acct: %d', len(missing_acct))
 
     if missing_acct:
-        log.warn('no such account code:\n%s',
-                 format_rows(missing_acct))
+        log.log(logging.ERROR if dry_run else logging.WARN,
+                'no such account code:\n%s',
+                format_rows(missing_acct))
 
-    if dry_run:
-        return
+        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
+'''
+
 
 def format_rows(rows):
     return '\n'.join([str(row) for row in rows])
 
 
 def budget_sync_items(conn):
-    dups = conn.execute('''
-select count(*), budget_name, t_lo, code
-from budget_import
-where code > ''
-group by budget_name, t_lo, code
-having count(*) > 1''').fetchall()
+    key_cols = [BudgetItem.budget_name,
+                BudgetItem.t_lo,
+                BudgetItem.code]
+    dups = conn.execute(
+        BudgetItem.select(key_cols + [func.count()]).
+        group_by(key_cols).having(func.count() > 1)).fetchall()
     log.debug('dups: %d', len(dups))
 
     if dups: