Commits

Dan Connolly committed 1c16c16

- use real gnucash output for mock/test data
- find root account from `books` table rather than by `account_type`
- include action, quantity_num/denom in Split class
- inject UUIDGen
- work-around sqlite datetime handling issues
- drop and create temporary StmtTrn table

  • Participants
  • Parent commits 3f126c5

Comments (0)

Files changed (4)

File finquick/models.py

 
 ... we can query for accounts as per the usual sqlalchemy orm API.
 
-There is always exactly one root account in a GnuCash book.
-.. todo:: cite GnuCash spec that says so.
+Find the root account of the first book::
+>>> (rg, ) = session.execute('select root_account_guid from books').fetchone()
+>>> rg
+u'4b1541673b2df412263bf6888043a6f8'
+>>> root = session.query(Account).filter(Account.guid == rg).one()
 
->>> root = session.query(Account).filter(Account.account_type == 'ROOT').one()
-
-Our mock data has one top-level bank:
+Our mock data has one bank, under Current Assets, under Assets:
 
 >>> banks = session.query(Account).filter(Account.account_type == 'BANK').all()
 >>> banks
 ... #doctest: +NORMALIZE_WHITESPACE
-[Account(guid=u'a35af99599ef5adbb8e1904b86ae1f26',
- name=u'Bank X', account_type=u'BANK',
- description=u'', hidden=False, placeholder=False,
- parent_guid=u'934e3c4f6aa55a8faedf160686214cc4')]
+[Account(guid=u'b49249296b4e626e15e3e9dc26e134ee',
+ name=u'Checking Account', account_type=u'BANK',
+ description=u'Checking Account', hidden=False,
+ placeholder=False, parent_guid=u'8e14dd0122d7603d321cbdc27ad1a61e')]
 
->>> banks[0].parent is root
+>>> banks[0].parent.parent.parent is root
 True
 
 '''
 from dotdict import dotdict
 
 KSession = injector.Key('Session')
+KUUIDGen = injector.Key('UUIDGen')
 Base = declarative_base()
 
 
         s.configure(bind=engine)
         return s
 
+    @singleton
+    @provides(KUUIDGen)
+    def uuidgen(self):
+        return uuid.uuid4
+
     @classmethod
     def mods(cls):
         '''Instantiate this module and its dependencies.
         >>> [(a.name, a.balance, a.reconciled_balance)
         ...  for a in q.order_by(Account.name).all()]
         ... # doctest: +NORMALIZE_WHITESPACE
-        [(u'Bank X', Decimal('-250.0000000000'), None),
+        [(u'Assets', None, None),
+         (u'Checking Account', Decimal('-370.0000000000'), None),
+         (u'Current Assets', None, None),
+         (u'Equity', None, None),
+         (u'Expenses', Decimal('370.0000000000'), None),
+         (u'Income', None, None),
+         (u'Opening Balances', None, None),
          (u'Root Account', None, None),
-         (u'Utilities', Decimal('250.0000000000'), None)]
+         (u'Template Root', None, None)]
+
         '''
 
         sum_value = func.sum(Split.value_num / Split.value_denom,
 
         return sq
 
-GNC_DATETIME_RE = re.compile("(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})")
-GNC_DateTime = DATETIME().with_variant(
-    sqlite.DATETIME(
-        storage_format='%04d%02d%02d%02d%02d%02d%0d',
-        regexp=GNC_DATETIME_RE
-        ), 'sqlite')
+GNC_DATETIME_RE = re.compile(r"(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})")
+#GNC_DateTime = sqlite.DATETIME(
+#        storage_format='%04d%02d%02d%02d%02d%02d%0d',
+#        regexp=GNC_DATETIME_RE
+#        )
+_SDT = sqlite.DATETIME(
+    storage_format='%04d%02d%02d%02d%02d%02d%0d',
+    regexp=GNC_DATETIME_RE
+    )
+# KLUDGE! adapt() seems to rely on attribute names
+_SDT.regexp = GNC_DATETIME_RE
+_SDT.storage_format = '%04d%02d%02d%02d%02d%02d%0d'
+GNC_DateTime = DATETIME().with_variant(_SDT, 'sqlite')
 
 
 def _test_gnc_datetime():
 
         >>> rows = q.all()
         >>> [(r.account_name, r.value_num / r.value_denom) for r in rows]
-        [(u'Bank X', -250), (u'Utilities', 250)]
+        [(u'Checking Account', -250), (u'Expenses', 250)]
         >>> len(set([row.tx_guid for row in rows]))
         1
 
         Or by amount:
         >>> amt_q = Transaction.search_query(session, amount=250)
         >>> [(r.account_name, r.value_num / r.value_denom) for r in amt_q.all()]
-        [(u'Bank X', -250), (u'Utilities', 250)]
+        [(u'Checking Account', -250), (u'Expenses', 250)]
 
 
         Or by account:
-        >>> amt_q = Transaction.search_query(session, account='Utilities')
+        >>> amt_q = Transaction.search_query(session, account='Expenses')
         >>> [(r.account_name, r.value_num / r.value_denom) for r in amt_q.all()]
-        [(u'Bank X', -250), (u'Utilities', 250)]
+        ... #doctest: +NORMALIZE_WHITESPACE
+        [(u'Checking Account', -60), (u'Expenses', 60),
+         (u'Checking Account', -60), (u'Expenses', 60),
+         (u'Checking Account', -250), (u'Expenses', 250)]
 
         Or by all of the above:
         >>> amt_q = Transaction.search_query(session,
         ...                                  txt='Electric',
         ...                                  amount=250,
-        ...                                  account='Utilities')
+        ...                                  account='Expenses')
         >>> [(r.account_name, r.value_num / r.value_denom) for r in amt_q.all()]
-        [(u'Bank X', -250), (u'Utilities', 250)]
+        [(u'Checking Account', -250), (u'Expenses', 250)]
         '''
 
         detail = session.query(Transaction.post_date.label('post_date'),
     account_guid = Column(String, ForeignKey('accounts.guid'))
     account = orm.relationship('Account')
     memo = Column(String)
-    #action = Column(String)
+    action = Column(String)
     reconcile_state = Column(String)
     reconcile_date = Column(GNC_DateTime)
     value_num = Column(Integer)
     value_denom = Column(Integer)
     # TODO: derive value, a decimal
-    #quantity_num = Column(Integer)
-    #quantity_denom = Column(Integer)
+    quantity_num = Column(Integer)
+    quantity_denom = Column(Integer)
     #lot_guid = Column(String)
 
 
 
 
 class Mock(injector.Module):
-    # TODO: use GnuCash to make a small data set and use CSV files.
-    accounts = (dotdict(name='Root Account', account_type='ROOT', parent=None),
-                dotdict(name='Bank X', account_type='BANK',
-                        parent='Root Account'),
-                dotdict(name='Utilities', account_type='EXPENSE',
-                        parent='Root Account'))
-
-    transactions = [dotdict(post_date=datetime.datetime(2001, 01, 01, 1, 2, 3),
-                            description='Electric company',
-                            guid=_n2g('Electric company'))]
-
-    splits = [dotdict(tx_guid=_n2g('Electric company'),
-                      account_guid=_n2g('Bank X'),
-                      memo='',
-                      guid=_n2g(''),
-                      value_num=-25000,
-                      value_denom=100),
-              dotdict(tx_guid=_n2g('Electric company'),
-                      account_guid=_n2g('Utilities'),
-                      memo='lots o killowatt hours',
-                      guid=_n2g('lots o killowatt hours'),
-                      value_num=25000,
-                      value_denom=100)]
-
     @classmethod
     def make(cls, what):
         mods = [cls()] + DBConfig.mods()
         self.bootstrap(engine)
         return engine
 
+    sql='test/fin1.sql'
     def bootstrap(self, engine):
-        Base.metadata.create_all(engine)
-        engine.execute(Account.__table__.insert(),
-                       self.mock_accounts())
-        engine.execute(Transaction.__table__.insert(), self.transactions)
-        engine.execute(Split.__table__.insert(), self.splits)
-
-    def mock_accounts(self):
-        return [dict(name=acct.name,
-                     guid=_n2g(acct.name),
-                     account_type=acct.account_type,
-                     parent_guid=_n2g(acct.parent) if acct.parent else None,
-                     description='',
-                     placeholder=False,
-                     hidden=False)
-                for acct in self.accounts]
+        import pkg_resources
+        for line in pkg_resources.resource_stream(__name__, self.sql):
+            stmt = line.strip().strip(';')
+            if stmt == 'COMMIT':
+                continue  # avoid 'no transaction is active'
+            engine.execute(stmt)

File finquick/ofxin.py

     >>> transactions = list(transactions_g)
 
     Choose the destination account; note splits before import:
+    >>> models.Mock.sql='test/fin1_init.sql'
     >>> (session, i) = models.Mock.make([models.KSession, Importer])
     >>> from models import Account, Split
     >>> bank = session.query(Account).\
-    ...        filter(Account.name == 'Bank X').one()
+    ...        filter(Account.name == 'Checking Account').one()
     >>> [split.value_num
     ...  for split in session.query(Split).filter(Split.account == bank)]
-    [-25000]
+    []
 
     This simple import uses one transfer account for all new transactions:
     >>> exp = session.query(Account).\
-    ...        filter(Account.name == 'Utilities').one()
+    ...        filter(Account.name == 'Expenses').one()
 
     Insert the transactions into a temporary table and match them
     against existing splits/transactions:
     >>> i.run(bank, exp, 'currency_guid@@')
     >>> [split.value_num
     ...  for split in session.query(Split).filter(Split.account == bank)]
-    [-25000, 6000, 6000]
+    [-6000, -6000]
 
     Import is idempotent; doing it again has no effect:
     >>> i.prepare(summary, transactions, bank)
     >>> i.run(bank, exp, 'currency_guid@@')
     >>> [split.value_num
     ...  for split in session.query(Split).filter(Split.account == bank)]
-    [-25000, 6000, 6000]
+    [-6000, -6000]
 
     '''
     @inject(datasource=models.KSession,
         '''
         session = self._ds()  # hmm... instantiate this?
         tst = StmtTrn.__table__
-        session.execute(tst.delete())
+        tst.drop(bind=session.bind, checkfirst=True)
+        tst.create(bind=session.bind)
         session.execute(tst.insert(),
                         list(transactions))
         ta = models.Account.__table__
                               tx_guid=tx.guid,
                               account_guid=acct.guid,
                               memo=ofxtx.memo,
-                              reconcile_state='n',
-                              value_num=ofxtx.trnamt,
-                              value_denom=100)
+                              action='',
+                              reconcile_state='c',
+                              value_num=-ofxtx.trnamt,  # depends on tx type?
+                              value_denom=100,
+                              quantity_num=-ofxtx.trnamt,  # depends on tx type?
+                              quantity_denom=100)
 
             s2 = models.Split(guid=fmt(self._uuidgen()),
                               tx_guid=tx.guid,
                               account_guid=txfr.guid,
+                              memo='',
+                              action='',
                               reconcile_state='n',
-                              value_num=-ofxtx.trnamt,
-                              value_denom=100)
+                              value_num=ofxtx.trnamt,
+                              value_denom=100,
+                              quantity_num=ofxtx.trnamt,
+                              quantity_denom=100)
             online_id = models.TextSlot(obj_guid=s1.guid,
                                         name='online_id',
                                         string_val=ofxtx.fitid)
             yield n, v
 
     @classmethod
-    def whitebox(cls):
+    def _whitebox(cls):
         '''
         >>> import pkg_resources
         >>> ofxin = pkg_resources.resource_stream(__name__, 'test/test_ofx.ofx')

File finquick/test/fin1.sql

 PRAGMA foreign_keys=OFF;
 BEGIN TRANSACTION;
 CREATE TABLE gnclock ( Hostname varchar(255), PID int );
-INSERT INTO "gnclock" VALUES('pav',13268);
+INSERT INTO "gnclock" VALUES('pav',9256);
 CREATE TABLE versions (table_name text(50) PRIMARY KEY NOT NULL, table_version integer NOT NULL);
 INSERT INTO "versions" VALUES('Gnucash',2041000);
 INSERT INTO "versions" VALUES('Gnucash-Resave',19920);
 CREATE TABLE transactions (guid text(32) PRIMARY KEY NOT NULL, currency_guid text(32) NOT NULL, num text(2048) NOT NULL, post_date text(14), enter_date text(14), description text(2048));
 INSERT INTO "transactions" VALUES('192f9b4f554696782ff2bf51e482e1a1','0236753a3aa761d1a8b7d04b76e8fcae','','20110118022742','20120512170245','90853');
 INSERT INTO "transactions" VALUES('4469fae8a92d093313fa3f156eb130cb','0236753a3aa761d1a8b7d04b76e8fcae','','20110118031215','20120512170245','90853');
+INSERT INTO "transactions" VALUES('d0368c6511f78fb88acc96b6a4770d94','0236753a3aa761d1a8b7d04b76e8fcae','','20110101060000','20120514023734','Electric company');
 CREATE TABLE splits (guid text(32) PRIMARY KEY NOT NULL, tx_guid text(32) NOT NULL, account_guid text(32) NOT NULL, memo text(2048) NOT NULL, action text(2048) NOT NULL, reconcile_state text(1) NOT NULL, reconcile_date text(14), value_num bigint NOT NULL, value_denom bigint NOT NULL, quantity_num bigint NOT NULL, quantity_denom bigint NOT NULL, lot_guid text(32));
-INSERT INTO "splits" VALUES('d0cf8eae96402daced7e4a55870878f4','192f9b4f554696782ff2bf51e482e1a1','b49249296b4e626e15e3e9dc26e134ee','90853','','c','20120512170315',6000,100,6000,100,NULL);
-INSERT INTO "splits" VALUES('32498ba380a05f98abdda143d9be4080','192f9b4f554696782ff2bf51e482e1a1','42be2b8f2f42233383dae2d790f4bfcb','','','n',NULL,-6000,100,-6000,100,NULL);
-INSERT INTO "splits" VALUES('dc227176700e4e46b9bab972831c5112','4469fae8a92d093313fa3f156eb130cb','b49249296b4e626e15e3e9dc26e134ee','90853','','c','20120512170315',6000,100,6000,100,NULL);
-INSERT INTO "splits" VALUES('9fb6cf2159b734ed9e6e227ad031612c','4469fae8a92d093313fa3f156eb130cb','42be2b8f2f42233383dae2d790f4bfcb','','','n',NULL,-6000,100,-6000,100,NULL);
+INSERT INTO "splits" VALUES('d0cf8eae96402daced7e4a55870878f4','192f9b4f554696782ff2bf51e482e1a1','b49249296b4e626e15e3e9dc26e134ee','90853','','c','20120512170315',-6000,100,-6000,100,NULL);
+INSERT INTO "splits" VALUES('32498ba380a05f98abdda143d9be4080','192f9b4f554696782ff2bf51e482e1a1','42be2b8f2f42233383dae2d790f4bfcb','','','n',NULL,6000,100,6000,100,NULL);
+INSERT INTO "splits" VALUES('dc227176700e4e46b9bab972831c5112','4469fae8a92d093313fa3f156eb130cb','b49249296b4e626e15e3e9dc26e134ee','90853','','c','20120512170315',-6000,100,-6000,100,NULL);
+INSERT INTO "splits" VALUES('9fb6cf2159b734ed9e6e227ad031612c','4469fae8a92d093313fa3f156eb130cb','42be2b8f2f42233383dae2d790f4bfcb','','','n',NULL,6000,100,6000,100,NULL);
+INSERT INTO "splits" VALUES('1b194a16d7a8a85acdbe56f5f8007ab4','d0368c6511f78fb88acc96b6a4770d94','b49249296b4e626e15e3e9dc26e134ee','','','n',NULL,-25000,100,-25000,100,NULL);
+INSERT INTO "splits" VALUES('e0eb05a955605ab7c1e3ea3f660cd99d','d0368c6511f78fb88acc96b6a4770d94','42be2b8f2f42233383dae2d790f4bfcb','lots o killowatt hours','','n',NULL,25000,100,25000,100,NULL);
 CREATE TABLE billterms (guid text(32) PRIMARY KEY NOT NULL, name text(2048) NOT NULL, description text(2048) NOT NULL, refcount integer NOT NULL, invisible integer NOT NULL, parent text(32), type text(2048) NOT NULL, duedays integer, discountdays integer, discount_num bigint, discount_denom bigint, cutoff integer);
 CREATE TABLE customers (guid text(32) PRIMARY KEY NOT NULL, name text(2048) NOT NULL, id text(2048) NOT NULL, notes text(2048) NOT NULL, active integer NOT NULL, discount_num bigint NOT NULL, discount_denom bigint NOT NULL, credit_num bigint NOT NULL, credit_denom bigint NOT NULL, currency text(32) NOT NULL, tax_override integer NOT NULL, addr_name text(1024), addr_addr1 text(1024), addr_addr2 text(1024), addr_addr3 text(1024), addr_addr4 text(1024), addr_phone text(128), addr_fax text(128), addr_email text(256), shipaddr_name text(1024), shipaddr_addr1 text(1024), shipaddr_addr2 text(1024), shipaddr_addr3 text(1024), shipaddr_addr4 text(1024), shipaddr_phone text(128), shipaddr_fax text(128), shipaddr_email text(256), terms text(32), tax_included integer, taxtable text(32));
 CREATE TABLE employees (guid text(32) PRIMARY KEY NOT NULL, username text(2048) NOT NULL, id text(2048) NOT NULL, language text(2048) NOT NULL, acl text(2048) NOT NULL, active integer NOT NULL, currency text(32) NOT NULL, ccard_guid text(32), workday_num bigint NOT NULL, workday_denom bigint NOT NULL, rate_num bigint NOT NULL, rate_denom bigint NOT NULL, addr_name text(1024), addr_addr1 text(1024), addr_addr2 text(1024), addr_addr3 text(1024), addr_addr4 text(1024), addr_phone text(128), addr_fax text(128), addr_email text(256));
 INSERT INTO "slots" VALUES(25,'0a26d14ca5d9423ec042743da3d66bc0','import-map/memo/90853',5,0,NULL,0.0,NULL,'42be2b8f2f42233383dae2d790f4bfcb',0,1,NULL);
 INSERT INTO "slots" VALUES(26,'b49249296b4e626e15e3e9dc26e134ee','online_id',4,0,'123456789  123456789',0.0,NULL,NULL,0,1,NULL);
 INSERT INTO "slots" VALUES(27,'192f9b4f554696782ff2bf51e482e1a1','notes',4,0,'OFX ext. info: |Trans type:Generic credit|Payee ID:2',0.0,NULL,NULL,0,1,NULL);
-INSERT INTO "slots" VALUES(28,'d0cf8eae96402daced7e4a55870878f4','online_id',4,0,'2-6',0.0,NULL,NULL,0,1,NULL);
 INSERT INTO "slots" VALUES(29,'4469fae8a92d093313fa3f156eb130cb','notes',4,0,'OFX ext. info: |Trans type:Generic credit|Payee ID:2',0.0,NULL,NULL,0,1,NULL);
-INSERT INTO "slots" VALUES(30,'dc227176700e4e46b9bab972831c5112','online_id',4,0,'2-9',0.0,NULL,NULL,0,1,NULL);
+INSERT INTO "slots" VALUES(31,'d0368c6511f78fb88acc96b6a4770d94','date-posted',10,0,NULL,0.0,NULL,NULL,0,1,'20110101');
+INSERT INTO "slots" VALUES(33,'d0cf8eae96402daced7e4a55870878f4','online_id',4,0,'2-6',0.0,NULL,NULL,0,1,NULL);
+INSERT INTO "slots" VALUES(34,'dc227176700e4e46b9bab972831c5112','online_id',4,0,'2-9',0.0,NULL,NULL,0,1,NULL);
 DELETE FROM sqlite_sequence;
-INSERT INTO "sqlite_sequence" VALUES('slots',30);
+INSERT INTO "sqlite_sequence" VALUES('slots',34);
 CREATE INDEX tx_post_date_index ON transactions (post_date);
 CREATE INDEX splits_tx_guid_index ON splits (tx_guid);
 CREATE INDEX splits_account_guid_index ON splits (account_guid);

File finquick/views.py

 
 class AccountsList(JSONDBView):
     '''
-    Our test data has a ROOT, a BANK, and an EXPENSE:
+    The GnuCash Simple Checking Account suite has accounts of various types:
     >>> obj = AccountsList._test_view()
     >>> [o['account_type'] for o in obj]
-    [u'ROOT', u'BANK', u'EXPENSE']
+    ... #doctest: +NORMALIZE_WHITESPACE
+    [u'ROOT', u'ASSET', u'ASSET', u'BANK',
+     u'INCOME', u'EXPENSE',
+     u'EQUITY', u'EQUITY', u'ROOT']
     '''
     def __call__(self, request):
         try:
     '''
     Our test data has a ROOT, a BANK, and an EXPENSE:
     >>> obj = AccountSummary._test_view()
-    >>> [(o['account_type'], o['balance']) for o in sorted(obj)]
-    [(u'BANK', -250), (u'EXPENSE', 250), (u'ROOT', None)]
+    >>> [(o['account_type'], o['balance']) for o in sorted(obj) if o['balance']]
+    [(u'BANK', '-370.0000000000'), (u'EXPENSE', '370.0000000000')]
     '''
     def __call__(self, request):
         try: