From fc2b648989cd5afaff3c563dea739e90c19be331 Mon Sep 17 00:00:00 2001
From: René 'Necoro' Neumann <necoro@necoro.net>
Date: Tue, 4 May 2010 16:07:19 +0200
Subject: First working 'Show'

---
 controller.py       | 38 +++++++++++++++++++++++++--
 model.py            | 74 +++++++++++++++++++++++++++++++++++++++++++++++++++--
 templates/404.mako  |  4 +++
 templates/page.mako |  9 ++++---
 templates/show.mako | 25 ++++++++++++++++++
 5 files changed, 142 insertions(+), 8 deletions(-)
 create mode 100644 templates/show.mako

diff --git a/controller.py b/controller.py
index 69eaf76..0957692 100644
--- a/controller.py
+++ b/controller.py
@@ -1,13 +1,47 @@
 from __future__ import with_statement
 
 import web
+
+from model import *
 from helper import appdir
 from renderer import render
 
+import datetime
+from sqlalchemy import sql
+
 class Show:
     def GET(self, year = '', month = ''):
-        if year: return "Show %s/%s" % (year, month)
-        else: return "Show current"
+        if year:
+            return self.render([self.calc(year, month)])
+        else:
+            d = datetime.date.today()
+            
+            first = self.calc(d.year, d.month)
+            if d.month == 1:
+                second = self.calc(d.year - 1, 12)
+            else:
+                second = self.calc(d.year, d.month - 1)
+
+            return self.render([first, second])
+
+    def calc(self, year, month):
+
+        ssum = sql.functions.sum(SingleExpense.expense)
+        csum = sql.functions.sum(ConstExpense.expense)
+
+        query = SingleExpense.of_month(month, year).\
+                group_by(SingleExpense.category_id).\
+                values(SingleExpense.category_id, ssum)
+
+        exps = [CatExpense(Category.query.get(c), s) for c,s in query]
+
+        consts = ConstExpense.of_month(month, year).value(csum)
+        if consts is None: consts = 0
+
+        return MonthExpense(datetime.date(int(year), int(month), 1), consts, exps)
+
+    def render(self, exps):
+        return render("show", exps = exps)
 
 class Add:
     def GET(self):
diff --git a/model.py b/model.py
index 02323b9..d37bf62 100644
--- a/model.py
+++ b/model.py
@@ -1,12 +1,24 @@
 import elixir
 from elixir import Field, ManyToOne, OneToMany, OneToOne, ColumnProperty, using_options, using_options_defaults
 from sqlalchemy import types as T
+from sqlalchemy import sql
 
 from functools import partial
+from collections import namedtuple
+
+__all__ = ["Category", "SingleExpense", "ConstExpense", "CatExpense", "MonthExpense", "session"]
+
+#
+# DB Setup
+#
 
 elixir.metadata.bind = "sqlite:///test.sqlite"
 elixir.metadata.bind.echo = True
 
+#
+# Global definitions
+#
+
 ReqField = partial(Field, required = True)
 
 class Entity (elixir.Entity):
@@ -14,6 +26,10 @@ class Entity (elixir.Entity):
 
     using_options_defaults(shortnames = True)
 
+#
+# Database Entities
+#
+
 class Category (Entity):
     
     name = Field(T.String(50), unique = True)
@@ -36,16 +52,70 @@ class Expense (Entity):
 
 class SingleExpense (Expense):
     date = ReqField(T.Date)
+    
+    year = ColumnProperty(lambda c: sql.extract('year', c.date))
+    month = ColumnProperty(lambda c: sql.extract('month', c.date))
+
+    @classmethod
+    def of_month (cls, month, year):
+        comp = sql.and_(
+                cls.month == month,
+                cls.year == year)
+
+        return cls.query.filter(comp)
 
 class ConstExpense (Expense):
     months  = ReqField(T.Integer)
     start   = ReqField(T.Date)
     end     = ReqField(T.Date)
 
-    monthly = ColumnProperty(lambda c: c.expense / c.months)
-
     next = OneToOne('ConstExpense', inverse = 'prev')
     prev = ManyToOne('ConstExpense')
+    
+    monthly = ColumnProperty(lambda c: c.expense / c.months, deferred = True)
+    
+    start_year = ColumnProperty(lambda c: sql.extract('year', c.start))
+    start_month = ColumnProperty(lambda c: sql.extract('month', c.start))
+    
+    end_year = ColumnProperty(lambda c: sql.extract('year', c.end))
+    end_month = ColumnProperty(lambda c: sql.extract('month', c.end))
+
+    @classmethod
+    def of_month (cls, month, year):
+        c1 = sql.or_(
+                    cls.start_year < year,
+                    sql.and_(
+                        cls.start_year == year,
+                        cls.start_month <= month
+                    ))
+
+        c2 = sql.or_(
+                    cls.end_year > year,
+                    sql.and_(
+                        cls.end_year == year,
+                        cls.end_month >= month
+                    ))
+
+        return cls.query.filter(sql.and_(c1,c2))
+
+#
+# Work entities (not stored in DB)
+#
+CatExpense = namedtuple('CatExpense', 'cat expense')
+
+class MonthExpense (namedtuple('MonthExpense', 'date const catexps')):
+    __slots__ = ()
+    
+    @property
+    def sum (self):
+        return self.const + sum(x.expense for x in self.catexps)
+
+    def __str__ (self):
+        return '<MonthExpense of "%s": %s>' % (self.date, self.sum)
+
+#
+# Rest
+#
 
 elixir.setup_all()
 
diff --git a/templates/404.mako b/templates/404.mako
index aeb95e3..c47aee3 100644
--- a/templates/404.mako
+++ b/templates/404.mako
@@ -5,3 +5,7 @@
 <p>
 Sorry - the requested page <i>${page}</i> cannot be found.
 </p>
+
+<%def name="heading()">
+    404 -- Page not found!
+</%def>
diff --git a/templates/page.mako b/templates/page.mako
index cc9911f..8e30cf3 100644
--- a/templates/page.mako
+++ b/templates/page.mako
@@ -1,9 +1,10 @@
-<%!
-    title = ""
-%>
 <%inherit file="/root.mako" />
 
 ## content
 
-<h1 class="title">${self.attr.title}</h1>
+<h1 class="title">${self.heading()}</h1>
 ${next.body()}
+
+## functions
+<%def name="heading()">
+</%def>
diff --git a/templates/show.mako b/templates/show.mako
new file mode 100644
index 0000000..270477a
--- /dev/null
+++ b/templates/show.mako
@@ -0,0 +1,25 @@
+<%inherit file="/page.mako" />
+
+% for e in exps:
+    % if len(exps) > 1:
+        <h2>${get_d(e)}</h2>
+    % endif
+    % for c in e.catexps:
+        <strong>${c.cat.name}</strong>    ${c.expense}<br>
+    % endfor
+    <strong>Constant:</strong> ${e.const}<br>
+    <strong>In Summa:</strong> ${e.sum}<br><br>
+% endfor
+
+<%def name="heading()">
+    % if len(exps) > 1:
+        Current expenses
+    % else:
+        Expenses for ${get_d(exps[0])}
+    % endif
+
+</%def>
+
+<%def name="get_d(e)">
+    ${e.date.year}/${e.date.month}
+</%def>
-- 
cgit v1.2.3-70-g09d2