1. Dan Connolly
  2. quacken

Commits

Dan Connolly  committed 613d5bc

basic transaction search includes account names
- move transaction search query building from views module to models

  • Participants
  • Parent commits ba18a72
  • Branches default

Comments (0)

Files changed (4)

File finjax/finjax/models.py

View file
  • Ignore whitespace
     Column, ForeignKey,
     Integer, String, Boolean,
     Date, Text,
+    and_, or_
     )
 from sqlalchemy import orm
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import scoped_session, sessionmaker
+from sqlalchemy.sql import exists
 from zope.sqlalchemy import ZopeTransactionExtension
 
 SessionMaker = sessionmaker(extension=ZopeTransactionExtension())
     description = Column(String)
     splits = orm.relationship('Split')
 
+    @classmethod
+    def search_query(cls, session, txt):
+        pattern = '%' + txt + '%'
+
+        detail = session.query(Transaction.post_date.label('post_date'),
+                               Split.guid.label('split_guid'),
+                               Split.tx_guid.label('tx_guid'),
+                               Split.account_guid.label('account_guid'),
+                               Account.name.label('account_name'),
+                               Split.memo.label('memo'),
+                               Split.value_num.label('value_num'),
+                               Split.value_denom.label('value_denom')).filter(
+            and_(Split.tx_guid == Transaction.guid,
+                 Split.account_guid == Account.guid))
+                               
+        return detail.filter(
+            or_(Transaction.description.like(pattern),
+                exists([Split.guid],
+                       and_(Split.tx_guid == Transaction.guid,
+                            Split.memo.like(pattern))
+                       ).correlate(Transaction.__table__))
+            ).order_by(Transaction.post_date)
+
 
 class Split(Base, GuidMixin):
     __tablename__ = 'splits'
 
 
 def _fix_date(col, x):
-    if x and col.type.__class__ == Date:
+    if x and col['type'].__class__ == Date:
         return x.isoformat()
     else:
         return x
 
-def jrec(rec, cols):
-    return dict([(c.name, _fix_date(c, getattr(rec, c.name)))
-                 for c in cols])
+def jrec(rec, col_descs):
+    return dict([(c['name'], _fix_date(c, getattr(rec, c['name'])))
+                 for c in col_descs])

File finjax/finjax/static/fin.js

View file
  • Ignore whitespace
 angular.service('Transaction', function($resource) {
     return $resource('../transaction/:guid', {}, {
 	// override the query method to make sure the trailing / is kept
-	query2: {method: 'GET', params: {guid: '-'},
-		 isArray: true, verifyCache: true}
+	query: {method: 'GET', params: {guid: '-'},
+		isArray: true, verifyCache: true}
     });
 });
 
 function TransactionsCtrl(Transaction, $log) {
     var self = this;
 
-    self.matches = [{tx: {post_date: '2012-01-01',
-			  description: 'fun fun'},
-		     split: {memo: 'memo',
-			     amount_num: 200,
-			     amount_denom: 100}}];
+    self.matches = [{post_date: '2012-01-01',
+		     description: 'fun fun',
+		     memo: 'memo',
+		     amount_num: 200,
+		     amount_denom: 100}];
     self.search = function(qtxt) {
-	$log.info('search query text: ' + qtxt);
-	self.matches = Transaction.query2({q: qtxt}, function(res, hdrs) {
-	    $log.info('matches: ' + res.length);
-	});
+	self.matches = Transaction.query({q: qtxt});
     }
 }
 TransactionsCtrl.$inject = ['Transaction', '$log'];

File finjax/finjax/static/index.html

View file
  • Ignore whitespace
       <p><input name="q"/>
       <button ng:click="search(q)">Search</button></p>
       <table>
-	<tr><th>Date</th><th>Description</th><th>Memo</th>
+	<tr><th>Date</th><th>Account</th><th>Description</th><th>Memo</th>
 	<th>Amount</th></tr>
 
-	<tr ng:repeat="m in matches">
-	  <td>{{m.tx.post_date}}</td>
-	  <td>{{m.tx.description}}</td>
-	  <td>{{m.split.memo}}</td>
-	  <td class="money">{{m.split.amount_num / m.split.amount_denom }}</td>
+	<tr ng:repeat="d in matches">
+	  <td>{{d.post_date}}</td>
+	  <td>{{d.account_name}}</td>
+	  <td>{{d.description}}</td>
+	  <td>{{d.memo}}</td>
+	  <td class="money">{{d.value_num / d.value_denom }}</td>
 	</tr>
 
       </table>

File finjax/finjax/views.py

View file
  • Ignore whitespace
 from pyramid.response import Response
 
-from sqlalchemy import or_
 from sqlalchemy.exc import DBAPIError
 
 from .models import (
-    Account, Transaction, Split,
+    Account, Transaction,
     jrec
     )
 
             return Response('missing q param', content_type='text/plain',
                             status_int = 400)
 
+        dbq = Transaction.search_query(self._session, q)
         try:
-            qpattern = '%' + q + '%'
-            matches = self._session.query(Split).join(Transaction).filter(
-                or_(Split.memo.like(qpattern),
-                    Transaction.description.like(qpattern)))[:limit]
+            matches = dbq[:limit]
         except DBAPIError:
             return Response(conn_err_msg,
                             content_type='text/plain', status_int=500)
-        scols = Split.__table__.columns
-        tcols = Transaction.__table__.columns
 
         # todo: return all the splits of the relevant transactions
-        return [{'tx': jrec(split.transaction, tcols),
-                 'split': jrec(split, scols)}
-                  for split in matches]
+        return [jrec(m, dbq.column_descriptions) for m in matches]
 
 
 conn_err_msg = """\