[billreminder] Added new db entities file based on sqlalchemy; commiting initial db layer wiring.

[Og B. Maciel <ogmaciel src gnome org>]

commit b65bbbaf594cc2c4e1e480140d297cec5de1c79e
Author: Og B. Maciel <ogmaciel gnome org>
Date:   Mon Sep 28 23:10:58 2009 -0400

    Added new db entities file based on sqlalchemy; commiting initial db layer wiring.

 src/db/entities.py |   86 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/lib/actions.py |   81 +++++++++++++++++++++++++++++++++++++++++++++++++
 src/lib/dal.py     |   58 +++++++++++++++++++++++++++++++++++
 3 files changed, 225 insertions(+), 0 deletions(-)
---
diff --git a/src/db/entities.py b/src/db/entities.py
new file mode 100644
index 0000000..75962ee
--- /dev/null
+++ b/src/db/entities.py
@@ -0,0 +1,86 @@
+from sqlalchemy import Column, Integer, String, Numeric, Text, Date, Boolean, ForeignKey
+from sqlalchemy.orm import relation, backref
+from sqlalchemy.ext.declarative import declarative_base
+
+Base = declarative_base()
+
+class Category(Base):
+    __tablename__ = 'categories'
+
+    id = Column(Integer, primary_key=True)
+    name = Column(String(length=255, convert_unicode=True))
+    color = Column(String(length=6, convert_unicode=True))
+    bill_id = Column(Integer, ForeignKey('bills.id'))
+
+    def __init__(self, name, color=None):
+        self.name = name
+        if color:
+            self.color = color
+
+    def __repr__(self):
+        return self.name
+
+class Bill(Base):
+    __tablename__ = 'bills'
+
+    id = Column(Integer, primary_key=True)
+    payee = Column(String(length=255, convert_unicode=True))
+    amount = Column(Numeric)
+    dueDate = Column(Date)
+    notes = Column(Text(convert_unicode=True))
+    paid = Column(Boolean)
+    repeats = Column(Boolean)
+
+    category = relation(Category, backref=backref('bills', order_by=id))
+
+    def __init__(self, payee, amount, dueDate, notes=None, paid=False, repeats=False):
+        self.payee = payee
+        self.amount=amount
+        self.dueDate = dueDate
+        if notes:
+            self.notes = notes
+        self.paid = paid
+        self.repeats = repeats
+
+    def __repr__(self):
+        return self.payee
+
+if __name__ == 'main':
+    from sqlalchemy.orm import sessionmaker
+    from sqlalchemy import create_engine
+    from sqlalchemy.orm import sessionmaker
+    from datetime import date
+
+    engine = create_engine('sqlite:///:memory:', echo=True)
+    Session = sessionmaker(bind=engine)
+
+    # Creates all database tables
+    Bill.metadata.create_all(engine)
+
+    dt = date.today()
+
+    # Create a new Bill record
+    ht = Bill('Harris Teeter', 123.94, dt)
+    # Create a new Category record
+    food = Category('Groceries')
+    # Add category to bill
+    ht.category.append(food)
+
+    # Session to talk to the database
+    session = Session()
+    # Add new record to database
+    session.add(ht)
+    # Commit it
+    session.commit()
+
+    # Get all Bill records
+    for instance in session.query(Bill).order_by(Bill.dueDate):
+        print "Pay %s the amount of %s on %s" % (instance.payee, instance.amount, instance.dueDate)
+
+    # Get all Categories records
+    for instance in session.query(Category).all():
+        print "Category: %s" % instance
+
+    # Get all Bills with a category of 'Groceries'
+    for instance in session.query(Bill).filter(Bill.category.contains(food)):
+        print instance
diff --git a/src/lib/actions.py b/src/lib/actions.py
new file mode 100644
index 0000000..9b15498
--- /dev/null
+++ b/src/lib/actions.py
@@ -0,0 +1,81 @@
+# -*- coding: utf-8 -*-
+
+__all__ = ['Actions']
+
+import sys
+
+import dal
+import time
+import datetime
+from db.entities import Bill, Category
+from lib import common, scheduler
+from lib.utils import force_string
+from lib.utils import verify_dbus_service
+
+class Actions(object):
+
+    def __init__(self, databaselayer=None):
+        if not databaselayer:
+            databaselayer = dal.DAL()
+
+        self.dal = databaselayer
+
+    def get_interval_bills(self, status, start, end):
+        """
+        """
+
+        records = []
+
+        try:
+            session = self.dal.Session()
+            records = session.query(Bill).filter(Bill.dueDate >= start).filter(Bill.dueDate <= end).all()
+        except Exception, e:
+            print str(e)
+            pass
+        finally:
+            session.close()
+
+        return records
+
+    def get_monthly_totals(self, status, month, year):
+        """
+        Return a list of categories and totals for the given month
+        """
+
+        records = 0.00
+
+        firstOfMonth = scheduler.first_of_month(month, year)
+        lastOfMonth = scheduler.last_of_month(month, year)
+
+        try:
+            session = self.dal.Session()
+            # records is a tuple of type Decimal
+            records = session.query(func.sum(Bill.amount)).filter(Bill.dueDate >= dt).filter(Bill.dueDate <= to).one()[0]
+            records = float(records)
+        except Exception, e:
+            print str(e)
+            pass
+        finally:
+            session.close()
+
+        return records
+
+    def get_monthly_bills(self, status, month, year):
+        """
+        Return a list of all bills for the given month with STATUS
+        """
+
+        records = []
+
+        firstOfMonth = scheduler.first_of_month(month, year)
+        lastOfMonth = scheduler.last_of_month(month, year)
+
+        try:
+            session = self.dal.Session()
+            records = session.query(Bill).filter(Bill.dueDate >= firstOfMonth).filter(Bill.dueDate <= lastOfMonth).all()
+        except Exception, e:
+            print str(e)
+        finally:
+            session.close()
+
+        return records
diff --git a/src/lib/dal.py b/src/lib/dal.py
new file mode 100644
index 0000000..d9bfb2c
--- /dev/null
+++ b/src/lib/dal.py
@@ -0,0 +1,58 @@
+# -*- coding: utf-8 -*-
+
+import os
+import sys
+
+from sqlalchemy.orm import sessionmaker
+from sqlalchemy import create_engine
+from sqlalchemy.orm import sessionmaker
+
+from db.entities import Bill, Category
+
+from lib.common import DB_NAME, APPNAME
+
+from xdg.BaseDirectory import *
+
+class DAL(object):
+
+    def __init__(self):
+        data_dir = os.path.join(xdg_data_home, APPNAME)
+        if not os.path.isdir(data_dir):
+            os.mkdir(data_dir)
+
+        self.engine = create_engine('sqlite:///%s' % os.path.join(data_dir, DB_NAME))
+        self.Session = sessionmaker(bind=self.engine)
+
+        # Creates all database tables
+        Bill.metadata.create_all(self.engine)
+
+    def add(self, dbobjects):
+        if not isinstance(dbobjects, list):
+            dbobjects = [dbobjects]
+
+        session = self.Session()
+
+        try:
+            session.add_all(dbobjects)
+            session.commit()
+        except Exception, e:
+            session.rollback()
+            print str(e)
+        finally:
+            session.close()
+
+    def delete(self, dbobjects):
+        if not isinstance(dbobjects, list):
+            dbobjects = [dbobjects]
+
+        session = self.Session()
+
+        try:
+            for dbo in dbobjects:
+                session.delete(dbo)
+            session.commit()
+        except Exception, e:
+            session.rollback()
+            print str(e)
+        finally:
+            session.close()