1. Dan Connolly
  2. quacken

Commits

Dan Connolly  committed 955de3f

budget loads into gnucash DB
- not sure how much of these money calculations were necessary; deleting
and re-creating the budget seems to have fixed a problem

  • Participants
  • Parent commits 3945105
  • Branches default

Comments (0)

Files changed (1)

File budget_sync.py

View file
  • Ignore whitespace
 import csv
 import logging
 
+import sqlalchemy
+from sqlalchemy import Column
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.types import Integer, String, DECIMAL
+
+Base = declarative_base()
+Session = sqlalchemy.orm.sessionmaker()
+
 log = logging.getLogger(__name__)
 
-MONEY_DENOM = 100
+Money = DECIMAL(precision=8, scale=2)
+Name = String(80)
 
 
 def main(argv):
     logging.basicConfig(level=logging.DEBUG)
-    infn = argv[1]
+    infn, engine_url = argv[1:3]
 
-    def log_row(r):
-        log.debug('insert: %s', r)
-
-    budget_load(open(infn), log_row)
+    engine = budget_setup(engine_url)
+    def add_row(r):
+        engine.execute(BudgetItem.__table__.insert(), r)
+    budget_load(open(infn), add_row)
 
 
 def budget_load(infp, insert_fn,
         subcat = row[id_col - 1]
         cat = row[id_col - 2]
 
-        monthly_num = money(row[monthly_col])
+        monthly_money = money(row[monthly_col])
         for p in range(0, periods):
-            amt = monthly_num + (money(row[period0_col + p])
-                                 if period0_col + p < len(row)
-                                 else 0)
+            amt = add_money(monthly_money, (money(row[period0_col + p])
+                                            if period0_col + p < len(row)
+                                            else (0, 1)))
 
-            if amt > 0:
-                insert_fn((None, budget_name, cat, subcat, acct_id, p,
-                           amt, MONEY_DENOM))
+            if amt[0] > 0:
+                insert_fn(dict(id=None,
+                               budget=budget_name,
+                               cat=cat, subcat=subcat, acct_id=acct_id,
+                               period=p,
+                               amount_num=amt[0], amount_denom=amt[1]))
+
+
+def budget_setup(engine_url):
+    engine = sqlalchemy.create_engine(engine_url)
+    Base.metadata.drop_all(engine)
+    Base.metadata.create_all(engine)
+    return engine
+
+
+class BudgetItem(Base):
+    __tablename__ = 'gdocs_budget'
+    id = Column(Integer, primary_key=True)
+    budget = Column(Name)
+    cat = Column(Name)
+    subcat = Column(Name)
+    acct_id = Column(Integer)
+    period = Column(Integer)
+    amount_num = Column(Integer)
+    amount_denom = Column(Integer)
 
 
 def money(txt):
     '''
     >>> money('$8,454.00')
-    845400
+    (8454, 1)
+    >>> money('60')
+    (60, 1)
+    >>> money('256.69')
+    (25669, 100)
     '''
-    amt = 0
     try:
-        amt = int(txt.replace('$', '').replace(',', '').replace('.', ''))
+        amt = int(float(txt.replace('$', '').replace(',', '')) * 100)
     except ValueError:
-        pass
-    return amt
+        return 0, 1
+
+    if amt % 100 == 0:
+        return amt / 100, 1
+    else:
+        return amt, 100
+
+def add_money((xn, xd), (yn, yd)):
+    if xd == yd:
+        return xn + yn, xd
+    else:
+        n = xn * (100 / xd) + yn * (100 / yd)
+        if n % 100 == 0:
+            return n / 100, 1
+        else:
+            return n, 100
 
 
 if __name__ == '__main__':