First working 'Show'

1 files changed, 72 insertions, 2 deletions

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()