Commits

Dan Connolly committed f01f762

limit register view by starting date

  • Participants
  • Parent commits 8e6cb2f

Comments (0)

Files changed (5)

File qfm/models.py

-from django.db import models
+from django.db import models, connection
 
 class Account(models.Model):
     id = models.AutoField(primary_key=True)
     kind = models.CharField(max_length=80, null=True)
  
 
+    def balance(self, when):
+	"""get account balance as of YYYY-MM-DD
+	"""
+	# based on snippet 242
+	# see also django ticket #3566 re aggregates
+	cursor = connection.cursor()
+	tables = dict([(x._meta.object_name, x._meta.db_table)
+		       for x in Account, Transaction, Split])
+	cmd = """select sum(subtot)
+	         from %(Split)s, %(Transaction)s, %(Account)s
+		 where %(Split)s .trx_id = %(Transaction)s .id
+		 and %(Transaction)s .acct_id = %(Account)s .id
+		 and %(Account)s .id = %%s
+		 and %(Transaction)s . date < %%s
+	""" % tables
+	cursor.execute(cmd, (self.id, when))
+	balrow = cursor.fetchone()
+	return balrow[0] or 0
+
     class Admin:
         pass
 

File qfm/templates/accounts.html

 {% endfor %}
 </ul>
 
+<div><h2>Open Register</h2>
+<form action="register">
+
+<div>Account: <select name="acct">
+{% for a in accounts %}
+<option value="{{a.id}}">{{ a.name|escape }}</option>
+{% endfor %}
+</select>
+</form>
+
+<input type="text" name="date_start" value="yyyy-mm-dd" />
+<input type="text" name="date_end" value="yyyy-mm-dd" />
+<input type="submit" value="Open"/>
+</div>
+
 <div><h2>Colophon: SQL details</h2>
-<p><tt>
-{{queries}}
-</tt>
-</p>
+
+<ul>
+ {% for q in queries %}
+ <li><tt>{{q.sql}}</tt><br />{{q.time}} seconds</li>
+ {% endfor %}
+</ul>
+
 </div>
 
 <hr />

File qfm/templates/register.html

 </table>
 
 
-<form action="{{ account.get_absolute_url }}" method="post">
-<hr /><h3>Transaction form@@</h3>
+<div><h2>Colophon: SQL details</h2>
+
 <ul>
-  @@ txform.as_ul }}
+ {% for q in queries %}
+ <li><tt>{{q.sql}}</tt><br />{{q.time}} seconds</li>
+ {% endfor %}
 </ul>
-<hr />
-</form>
+
+</div>
 
 <hr />
 <address>
 
 urlpatterns = patterns('',
     (r'^$', 'dm93data.qfm.views.accounts'),
-    (r'^register/(?P<acct_id>\d+)', 'dm93data.qfm.views.register'),
+    (r'^register/(?P<acct>\d+)', 'dm93data.qfm.views.register'),
+    (r'^register', 'dm93data.qfm.views.register'),
     (r'^export', 'dm93data.qfm.views.export'),
     (r'^api/categories/', 'dm93data.qfm.views.category_choices'),
 )

File qfm/views.py

 # TODO: Use case: dining calendar. Fun:dining category in hCalendar with times
 
 import datetime
+from decimal import Decimal
 
 from django.shortcuts import render_to_response
-from dm93data.qfm.models import Account, Transaction
 from django.http import HttpResponse
 from django.template import loader, RequestContext
 from django.utils import simplejson
 from django import forms
 from django.core.urlresolvers import reverse
-
 from django.db import connection
 
+from dm93data.qfm.models import Account, Transaction
 from widgets import AutoCompleteWidget
 
 def accounts(request):
         w.lookup_url = reverse('dm93data.qfm.views.category_choices')
         w.schema = '["choices", "name"]' 
 
-def register(request, acct_id):
-    account = Account.objects.get(id=int(acct_id))
-    transactions = account.transaction_set.all()
-    bal = 0.0
+def register(request):
+    acct_id, when = int(request.GET['acct']), asDate(request.GET['date_start'])
+    account = Account.objects.get(id=acct_id)
+    bal = account.balance(when)
+    transactions = account.transaction_set.filter(date__gte = when)
     for t in transactions:
         splits = t.split_set.all()
         amount = sum([s.subtot for s in splits])
         t.amount = amount
         t.balance = bal
 
+    #@@ TODO: 'txform': TransactionForm()
     return render_to_response('register.html',
                               {'account': account,
                                'balance': bal,
                                'transactions': transactions,
-                               'txform': TransactionForm()},
+			       'queries': connection.queries,
+                               },
                               context_instance=media_too(request)
                               )