--- a/iro/model/schema.py	Mon Jan 30 06:52:46 2012 +0100
+++ b/iro/model/schema.py	Mon Jan 30 21:02:03 2012 +0100
@@ -7,7 +7,8 @@
 from sqlalchemy import ForeignKey
 from sqlalchemy.orm import relationship, backref, object_session
 
-from sqlalchemy import and_ 
+from sqlalchemy import and_
+import sqlalchemy.sql.functions as func
 
 from ..error import JobNotFound
 
@@ -22,11 +23,27 @@
     offer_name = Column('offer', String(100), ForeignKey('offer.name'), primary_key=True)
     isDefault = Column(Boolean)
     offer = relationship("Offer")
+    user = relationship("User")
 
     def __init__(self, offer, default=False):
         self.offer = offer
         self.isDefault = default
 
+    @property
+    def bill(self):
+        '''getting a list of unbilled messages grouped by Job.info'''
+        query = [ func.count(Message.id).label('anz'),      # anz of messages
+                  func.sum(Message.price).label("price"),   # price of the messages
+                  Job.info.label('info'),                   # info tag
+                  ]
+
+        filters = [ Message.isBilled==False,                # only unbilled messages
+                   Job.user==self.user,                     # only jobs connected to user
+                   Message.offer==self.offer,               # only messages in the right offer
+                   Message.job_hash==Job.hash,              # join Message and Job
+                ]
+        return object_session(self).query(*query).filter(and_(*filters)).group_by(Job.info)
+
 class Offer(Base):
     """Alle Routen über die SMS, Faxe und Mails verschickt werden könnnen. <em>provider</em>, <em>typ</em> und <em>route</em> werden verwendet, um die entsprechenden Zugangsdaten laden zu können."""
     __tablename__ = "offer"
@@ -43,8 +60,8 @@
     recipient = Column(String(100))
     isBilled = Column(Boolean)
     date = Column(DateTime)
-    price = Column(Numeric(8,2))
-    job_hash = Column("job",Integer, ForeignKey('job.hash'))
+    price = Column(Numeric(8,4))
+    job_hash = Column("job", String(40), ForeignKey('job.hash'))
     job = relationship("Job", backref=backref('messages'))
     offer_id = Column("offer",String(100), ForeignKey('offer.name'))
     offer = relationship("Offer", backref=backref('messages'))