Commits

Dan Connolly committed a9286cc Merge

merge django work

Comments (0)

Files changed (12)

+ - link from net worth balance to "oh yeah?" info, e.g. register

qfm/media/finance.css

 thead th {vertical-align: bottom; border: 1px solid gray;
   border-width: 0 1px 1px 0;
   white-space: normal;}
+tfoot th {vertical-align: bottom; border: 1px solid gray;
+  border-width: 1px 0px 1px 0px;
+  white-space: normal;}
+tfoot td { border-width: 1px 1px 0px 0px; border: 1px solid gray;
+       font-weight: bold}
 th {border-right: 1px solid gray; border-bottom-style: dotted;
   white-space: nowrap;}
 
-from django.db import models
+from django.db import models, connection
 
 class Account(models.Model):
     id = models.AutoField(primary_key=True)
  
-    name = models.CharField(maxlength=80, null=True)
+    name = models.CharField(max_length=80, null=True)
 
     def __str__(self):
         return self.name
  
     parent = models.ForeignKey('self', null=True)
  
-    kind = models.CharField(maxlength=80, null=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
 
 class Job(models.Model):
     id = models.AutoField(primary_key=True)
  
-    name = models.CharField(maxlength=80, null=True)
+    name = models.CharField(max_length=80, null=True)
 
     def __str__(self):
         return self.name
  
     date = models.DateField()
  
-    payee = models.CharField(maxlength=80, null=True)
+    payee = models.CharField(max_length=80, null=True)
  
-    num = models.CharField(maxlength=80, null=True)
+    num = models.CharField(max_length=80, null=True)
  
-    ty = models.CharField(maxlength=80, null=True)
+    ty = models.CharField(max_length=80, null=True)
  
-    s = models.CharField(maxlength=80, null=True)
+    s = models.CharField(max_length=80, null=True)
  
 
     class Admin:
  
     job = models.ForeignKey(Job, null=True)
  
-    clr = models.CharField(maxlength=80, null=True)
+    clr = models.CharField(max_length=80, null=True)
  
-    memo = models.CharField(maxlength=80, null=True)
+    memo = models.CharField(max_length=80, null=True)
  
-    subtot = models.FloatField(max_digits=10, decimal_places=2)
+    subtot = models.DecimalField(max_digits=10, decimal_places=2)
  
 
     class Admin:
 class Payee(models.Model):
     id = models.AutoField(primary_key=True)
  
-    name = models.CharField(maxlength=80, null=True)
+    name = models.CharField(max_length=80, null=True)
 
     def __str__(self):
         return self.name

qfm/templates/accounts.html

   <link rel="stylesheet" href="{{ MEDIA_URL }}finance.css" />
 </head>
 <body>
-<h1>Asset/Liability Accounts</h1>
-<table cellspacing="0" summary="Account Listing">
-<thead>
-<tr>
-<th>Latest</th>
-<th>Account</th>
-<th>#</th>
-</tr>
-</thead>
-<tbody>
+
+<h1>Personal Finances</h1>
+
+<div><h2>Account Balances</h2>
+
+<form action="networth">
+<div>
+Report: <input name="report" value="Net Worth" />
+Accounts: <select name="accts" multiple="multiple">
 {% for a in accounts %}
-<tr>
-  <td><tt>{{a.modified|date:"M-d-Y"}}</tt></td>
-  <td>{{a.name|escape}}</td>
-  <td class="num">
-    <a href="{%url dm93data.qfm.views.register a.id %}">
-      {{ a.transaction_set.count }}
-    </a>
-  </td>
-</tr>
+<option value="{{a.id}}">{{ a.name }}</option>
 {% endfor %}
-</tbody>
-</table>
+</select>
+
+As of: <input type="text" name="date_end" value="yyyy-mm-dd" />
+<input type="submit" value="Report"/>
+</div>
+</form>
+
+</div>
+
+<hr />
+<div><h2>Income and Expenses</h2>
+
+<form action="expenses">
+<div>
+Report: <input name="report" value="Expenses" />
+Categories: <select name="cat" multiple="multiple">
+{% for c in categories %}
+<option value="{{c.id}}">{{ c.name }}</option>
+{% endfor %}
+</select>
+
+from: <input type="text" name="date_start" value="yyyy-mm-dd" />
+to: <input type="text" name="date_end" value="yyyy-mm-dd" />
+<input type="submit" value="Report"/>
+</div>
+</form>
+
+</div>
+
+<hr />
+<div><h2>Open Register</h2>
+
+<form action="register">
+<div>Account: <select name="acct">
+{% for a in accounts %}
+<option value="{{a.id}}">{{ a.name }}</option>
+{% endfor %}
+</select>
+
+<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>
+</form>
+
+<hr />
+
+<div><h2>Expense Account Report</h2>
+
+<form action="expense">
+<div>Account: <select name="cat">
+{% for c in categories %}
+<option value="{{c.id}}">{{ c.name }}</option>
+{% endfor %}
+</select>
+
+<input type="text" name="date_start" value="yyyy-mm-dd" />
+<input type="text" name="date_end" value="yyyy-mm-dd" />
+<input type="submit" value="Report"/>
+</div>
+</form>
+
+<hr />
+
+<div><h2>Export</h2>
+
+<form action="export">
+<div>
+<input type="text" name="from" value="yyyy-mm-dd" />
+<input type="text" name="to" value="yyyy-mm-dd" />
+<input type="submit" value="Export"/>
+</div>
+</form>
 
 <hr />
 <address>

qfm/templates/base.html

+<!DOCTYPE html>
+<html xmlns="http://www.w3.org/1999/xhtml">
+<head>
+  <title>{% block title %}Personal Finances{% endblock %}</title>
+  <link rel="stylesheet" href="{{ MEDIA_URL }}finance.css" />
+</head>
+<body>
+
+{% block body %}
+This is a base template; there's a bug if you're seeing this text.
+{% endblock %}
+</body>
+</html>

qfm/templates/expenses.html

+{% extends "base.html" %}
+{% load financial %}
+
+{% block title %}{{ report }}{% endblock %}
+
+{% block body %}
+
+<h1>{{ report }}<br />
+{{ date_start }} to {{ date_end }}</h1>
+
+<table cellspacing="0" summary="Matching Transactions">
+<thead>
+<tr>
+<th>Date</th>
+<th>Account</th>
+<th>Payee</th>
+<th>Category</th>
+<th>Memo</th>
+<th>Amount</th>
+</tr>
+</thead>
+<tbody>
+{% for s in splits %}
+<tr>
+  <td>{{s.trx.date}}</td>
+  <td>{{s.trx.acct.name}}</td>
+  <td>{{s.trx.payee}}</td>
+  <td>{{s.acct.name}}</td>
+  <td>{{s.memo}}</td>
+  <td class="num"> {{ s.subtot|currency }}</td>
+</tr>
+{% endfor %}
+<tfoot>
+<tr><th colspan="5">Total:</th><td class="num">{{ total|currency}}</td></tr>
+</tfoot>
+</tbody>
+</table>
+
+{% endblock body %}

qfm/templates/export.tsv

 							
 			BALANCE {{ frm_1|date:"n/j/y" }}				{{ bal_in|currency }}
 							
-{% for t in transactions %}{{ t.date|date:"n/j/y" }}	{{ t.acct.name }}	{{ t.ty|default_if_none:"" }}{{ t.num|default_if_none:"" }}{% ifequal t.s "S" %}        S{% endifequal %}	{{ t.payee|default_if_none:"" }}	{% for s in t.split_set.all %}{%if not forloop.first %}				{% endif %}{{ s.memo|default_if_none:"" }}	{%ifequal s.acct.kind "AL"%}[{{s.acct.name}}]{%endifequal%}{%ifnotequal s.acct.kind "AL"%}{{s.acct.name}}{%endifnotequal%}{#
-%}{%if s.job %}/{{s.job.name}}{%endif%}	{{ s.clr|default_if_none:"" }}	{{ s.subtot|currency }}
+{% for t in transactions %}{{ t.date|date:"n/j/y" }}	{{ t.acct.name }}	{{ t.ty|default_if_none:"" }}{{ t.num|default_if_none:"" }}{% ifequal t.s "S" %}        S{% endifequal %}	{{ t.payee|default_if_none:"" }}	{% for s in t.split_set.all %}{%if not forloop.first %}				{% endif %}{{ s.memo|default_if_none:"" }}	{%ifequal s.acct.kind "AL"%}[{{s.acct.name}}]{%endifequal%}{%ifnotequal s.acct.kind "AL"%}{{s.acct.name}}{%endifnotequal%}{%if s.job %}/{{s.job.name}}{%endif%}	{{ s.clr|default_if_none:"" }}	{{ s.subtot|currency }}
 {% endfor %}{% endfor %}
 			TOTAL {{ frm|date:"n/j/y" }} - {{ to|date:"n/j/y" }}				{{ net|currency }}
 							

qfm/templates/register.html

 <!DOCTYPE html>
 <html xmlns="http://www.w3.org/1999/xhtml">
 <head>
-  <title>{{account.name|escape}} Register</title>
+  <title>{{account.name}} Register</title>
   <link rel="stylesheet" href="{{ MEDIA_URL }}finance.css" />
 
 	<!-- Source file --> 
 
 </head>
 <body>
-<h1>{{account.name|escape}}</h1>
+<h1>{{account.name}}</h1>
+
+{% load financial %}
 
 <table cellspacing="0">
 <thead>
  <tr class="trx">
   <td class="dtstart">{{t.date}}</td>
   <td class="vcard" colspan="2">
-   <strong class="fn">{{t.payee|escape}}</strong>
+   <strong class="fn">{{t.payee}}</strong>
   </td>
   <td>&nbsp;</td>
-  <td class="amt trx">{{t.amount|floatformat:2}}</td>
+  <td class="amt trx">{{t.amount|currency}}</td>
   <td class="amt bal"
-    >{{t.balance|floatformat:2}}</td>
+    >{{t.balance|currency}}</td>
  </tr>
 
 {% for s in t.split_set.all %}
  <tr class="split">
   <td>&nbsp;</td>
   <td class="ref">
-{%ifequal s.acct.kind "AL"%}[{{s.acct.name|escape}}]{%endifequal%}{%ifnotequal s.acct.kind "AL"%}{{s.acct.name|escape}}{%endifnotequal%}{%if s.job %}/{{s.job.name|escape}}{%endif%}</td>
-  <td>{{s.memo|default_if_none:""|escape}}</td>
+{%ifequal s.acct.kind "AL"%}[{{s.acct.name}}]{%endifequal%}{%ifnotequal s.acct.kind "AL"%}{{s.acct.name}}{%endifnotequal%}{%if s.job %}/{{s.job.name}}{%endif%}</td>
+  <td>{{s.memo|default_if_none:""}}</td>
   <td>{{s.clr|default_if_none:"&#160;"}}</td>
   <td class="amt">{{s.subtot|floatformat:2}}</td>
  </tr>
 {% endfor %}
 </table>
 
-
-<form action="{{ account.get_absolute_url }}" method="post">
-<hr /><h3>Transaction form@@</h3>
-<ul>
-  @@ txform.as_ul }}
-</ul>
-<hr />
-</form>
-
 <hr />
 <address>
 qfm by Dan Connolly Oct 2007

qfm/templates/report.html

+{% extends "base.html" %}
+{% load financial %}
+
+{% block title %}{{ report }}{% endblock %}
+
+{% block body %}
+
+<h1>{{ report }}<br />
+as of {{ date_end }}</h1>
+
+<table cellspacing="0" summary="Account Balances">
+<thead>
+<tr>
+<th>Account</th>
+<th>Balance</th>
+</tr>
+</thead>
+<tbody>
+{% for a in accounts %}
+<tr>
+  <td>{{a.name}}</td>
+  <td class="num"> {{ a.bal|currency }}</td>
+</tr>
+{% endfor %}
+<tfoot>
+<tr><th>Total:</th><td class="num">{{ total|currency}}</td></tr>
+</tfoot>
+</tbody>
+</table>
+
+{% endblock body %}
 
 urlpatterns = patterns('',
     (r'^$', 'dm93data.qfm.views.accounts'),
-    (r'^register/(?P<acct_id>\d+)', 'dm93data.qfm.views.register'),
+    (r'^networth', 'dm93data.qfm.views.networth'),
+    (r'^expenses', 'qfm.views.expenses'),
+    (r'^register', 'dm93data.qfm.views.register'),
+    (r'^register/(?P<acct>\d+)', 'dm93data.qfm.views.register'),
     (r'^export', 'dm93data.qfm.views.export'),
     (r'^api/categories/', 'dm93data.qfm.views.category_choices'),
 )
 import datetime
 
 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 newforms as forms
+from django import forms
 from django.core.urlresolvers import reverse
+from django.db import connection
 
+from dm93data.qfm.models import Account, Transaction, Split
 from widgets import AutoCompleteWidget
 
 def accounts(request):
-    accounts = Account.objects.filter(kind="AL")
-    for a in accounts:
-        tx = a.transaction_set.latest('date')
-        a.modified = tx.date
-    def byDate(a):
-        return a.modified
-    accounts = sorted(accounts, key=byDate, reverse=True)
+    accounts = Account.objects.filter(kind="AL") \
+	.order_by('name')
+    categories = Account.objects.filter(kind="IE") \
+	.order_by('name')
     return render_to_response('accounts.html',
-                              {'accounts': accounts},
+                              {'accounts': accounts,
+			       'categories': categories,
+			       'queries': connection.queries},
                               context_instance=media_too(request)
                               )
 
 
+def networth(request):
+    de = asDate(request.GET['date_end'])
+    report = request.GET['report']
+    acct_ids = [int(a) for a in request.GET.getlist('accts')]
+    accounts = Account.objects.filter(pk__in = acct_ids) \
+	.order_by('name')
+    tot = 0
+    for a in accounts:
+	b = a.balance(de)
+	a.bal = b
+	tot += b
+    return render_to_response('report.html',
+                              {'date_end': de,
+			       'report': report,
+			       'accounts': accounts,
+			       'total': tot,
+			       'queries': connection.queries,
+                               },
+                              context_instance=media_too(request)
+                              )
+
+def expenses(request):
+    report = request.GET['report']
+    cat_ids = [int(c) for c in request.GET.getlist('cat')]
+    ds, de = asDate(request.GET['date_start']), \
+	asDate(request.GET['date_end'])
+
+    cats = Account.objects.filter(pk__in = cat_ids)
+
+    splits = Split.objects.filter(acct__in = cats,
+				  trx__date__gte = ds,
+				  trx__date__lt = de).order_by('trx__date')
+    tot = sum([s.subtot for s in splits])
+
+    return render_to_response('expenses.html',
+                              {'date_start': ds,
+			       'date_end': de,
+			       'report': report,
+			       'splits': splits,
+			       'total': tot,
+			       'queries': connection.queries,
+                               },
+                              context_instance=media_too(request)
+                              )
+    
 def media_too(request):
     # django 0.96 doesn't yet have django.core.context_processors.media
     # so we do it manually..
         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, ds, de = int(request.GET['acct']), \
+	asDate(request.GET['date_start']), \
+	asDate(request.GET['date_end'])
+    account = Account.objects.get(id=acct_id)
+    bal = account.balance(ds)
+    transactions = account.transaction_set.filter(date__gte = ds,
+						  date__lt = de)
     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)
                               )
 
 # August 27, 2007
 # http://www.djangosnippets.org/snippets/392/
 
-from django import newforms as forms
-from django.newforms.util import smart_unicode
-from django.newforms.widgets import TextInput,flatatt
+from django import forms
+from django.forms.util import smart_unicode
+from django.forms.widgets import TextInput,flatatt
 from django.utils.html import escape
 
 class AutoCompleteField(TextInput):