# HG changeset patch # User Sandro Knauß # Date 1327953723 -3600 # Node ID 79966b93727408d74332532d05eb53b91eb1faf8 # Parent 1ac2439a68b58dc0e88d988d50e87ab894d4472d made bill (aka statistic) function ready diff -r 1ac2439a68b5 -r 79966b937274 iro/controller/viewinterface.py --- a/iro/controller/viewinterface.py Mon Jan 30 06:52:46 2012 +0100 +++ b/iro/controller/viewinterface.py Mon Jan 30 21:02:03 2012 +0100 @@ -149,16 +149,37 @@ return [u[0] for u in offers] @vUser - def statistic(self, user): - '''Gibt eine Statik zurück über die versendendeten Nachrichten und des Preises. + @dbdefer + def bill(self, session, user): + '''Gibt eine Rechnung zurück über die noch nicht abgerechneten Nachrichten und des Preises. Keywords: apikey[string]: Der API Key Return: - statistic[list]: Eine Liste nach Nachrichtentypen + bills[dict]: Eine Liste nach Nachrichtentypen + bill[route][info].anz: Anzahl der verschickten Nachrichten pro "Infoklasse" + bill[route][info].price: Preis pro "Infoklasse" + bill[route].anz: Anzahl der verschickten Nachrichten pro Route + bill[route].price: Preis für eine Route + total.price: der Gesammtpreis + total.anz: die Gesammtanzahl ''' - return "" + ret={'total':{'price':0, 'anz':0}} + user=session.merge(user) + for route in user.rights: + n=route.offer_name + ret[n]={'price':0, 'anz':0, 'info':{}} + for bill in route.bill: + ret[n]['info'][bill.info]={'price':float(bill.price),'anz':bill.anz} + ret[n]['price'] += bill.price + ret[n]['anz'] += bill.anz + ret['total']['price'] += ret[n]['price'] + ret['total']['anz'] += ret[n]['anz'] + ret[n]['price'] = float(ret[n]['price']) + + ret['total']['price'] = float(ret['total']['price']) + return ret @validate(kwd="recipients",func=vTel) def telnumber(self,recipients): diff -r 1ac2439a68b5 -r 79966b937274 iro/model/schema.py --- 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. provider, typ und route 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')) diff -r 1ac2439a68b5 -r 79966b937274 iro/view/xmlrpc.py --- a/iro/view/xmlrpc.py Mon Jan 30 06:52:46 2012 +0100 +++ b/iro/view/xmlrpc.py Mon Jan 30 21:02:03 2012 +0100 @@ -31,7 +31,7 @@ """Since we override lookupProcedure, its suggested to override listProcedures too. """ - return ['listMethods','status','stop','sms','fax','mail','routes','defaultRoute','statistic','telnumber','email'] + return ['listMethods','status','stop','sms','fax','mail','routes','defaultRoute','bill','telnumber','email'] class XMLRPCInterface(TwistedInterface,xmlrpc.XMLRPC): diff -r 1ac2439a68b5 -r 79966b937274 tests/xmlrpc.py --- a/tests/xmlrpc.py Mon Jan 30 06:52:46 2012 +0100 +++ b/tests/xmlrpc.py Mon Jan 30 21:02:03 2012 +0100 @@ -2,25 +2,26 @@ from sqlalchemy import create_engine, pool import unittest - from tempfile import mkdtemp import shutil +from datetime import datetime + +import time + +from xmlrpclib import Server as xServer, ServerProxy, Fault + from iro.model.utils import WithSession from iro.model import POOL_SIZE as DB_POOL_SIZE -from iro.model.schema import User, Base, Offer, Userright, Job +from iro.model.schema import User, Base, Offer, Userright, Job, Message import iro.model.schema as schema -from ngdatabase.mysql import Server, createConfig, Database - from iro.main import runReactor import iro.error as IroError -import time - -from xmlrpclib import Server as xServer, ServerProxy, Fault +from ngdatabase.mysql import Server, createConfig, Database class SampleDatabase(Database): def createPassword(self): @@ -69,7 +70,7 @@ def testListMethods(self): '''list of all offical Methods, that can be executed''' ret=self.__rpc2().listMethods() - self.failUnlessEqual(ret, ['listMethods', 'status', 'stop', 'sms', 'fax', 'mail', 'routes', 'defaultRoute', 'statistic', 'telnumber','email']) + self.failUnlessEqual(ret, ['listMethods', 'status', 'stop', 'sms', 'fax', 'mail', 'routes', 'defaultRoute', 'bill', 'telnumber','email']) def testStatus(self): ''' test the status function''' @@ -227,8 +228,72 @@ exc = fault.exception self.failUnlessEqual(exc.faultCode, 702) self.failUnlessEqual(exc.faultString, "No valid email: '%s'"%number) + + def testBill(self): + '''test bill function''' + apikey='abcdef123456789' + with WithSession(md.engine, autocommit=True) as session: + u=User(name='test',apikey=apikey) + session.add(u) + self.failUnlessEqual(self.__rpc2().bill(apikey),{'total':{'price':0.0,'anz':0}}) + + with WithSession(md.engine, autocommit=True) as session: + u = session.merge(u) + o = Offer(name='sipgate_basic',provider="sipgate",route="basic",typ="sms") + j = Job(hash='a1',info='i',status='sended') + m = Message(recipient='0123456789', isBilled=False, date=datetime.now() , price=0.30, offer=o, job=j) + u.rights.append(Userright(o)) + u.jobs.append(j) + session.add(m) + + self.failUnlessEqual(self.__rpc2().bill(apikey),{'total':{'price':0.3,'anz':1}, + 'sipgate_basic':{'price':0.3,'anz':1,'info':{'i':{'price':0.3,'anz':1}}}}) + + with WithSession(md.engine, autocommit=True) as session: + j = session.merge(j) + j.messages.append(Message(recipient='0123456789', isBilled=False, date=datetime.now() , price=0.4, offer=o)) + + self.failUnlessEqual(self.__rpc2().bill(apikey),{'total':{'price':0.7,'anz':2}, + 'sipgate_basic':{'price':0.7,'anz':2,'info':{'i':{'price':0.7,'anz':2}}}}) + + with WithSession(md.engine, autocommit=True) as session: + m = session.merge(m) + m.isBilled=True + + self.failUnlessEqual(self.__rpc2().bill(apikey),{'total':{'price':0.4,'anz':1}, + 'sipgate_basic':{'price':0.4,'anz':1,'info':{'i':{'price':0.4,'anz':1}}}}) - + with WithSession(md.engine, autocommit=True) as session: + u = session.merge(u) + j = Job(hash='a2',info='a',status='sended') + j.messages.append(Message(recipient='0123456789', isBilled=False, date=datetime.now(), price=0.4, offer=o)) + u.jobs.append(j) + ret=self.__rpc2().bill(apikey) + self.failUnlessEqual(ret['total'],{'price':0.8,'anz':2}) + self.failUnlessEqual(ret['sipgate_basic'], + {'price':0.8,'anz':2, + 'info':{'i':{'price':0.4,'anz':1}, + 'a':{'price':0.4,'anz':1}, + } + }) + + with WithSession(md.engine, autocommit=True) as session: + u = session.merge(u) + j = Job(hash='a3',info='a',status='sended') + o = Offer(name='sipgate_gold',provider="sipgate",route="gold",typ="sms") + j.messages.append(Message(recipient='0123456789', isBilled=False, date=datetime.now(), price=0.5, offer=o)) + u.rights.append(Userright(offer=o)) + u.jobs.append(j) + + ret=self.__rpc2().bill(apikey) + self.failUnlessEqual(ret['total'],{'price':1.3,'anz':3}) + self.failUnlessEqual(ret['sipgate_gold'], + {'price':0.5,'anz':1, + 'info':{ + 'a':{'price':0.5,'anz':1}, + } + }) + def startReactor(engine): """starts the Rector with a special debug Clild, so that the reactor can be stopped remotly. """ from twisted.internet import reactor @@ -256,7 +321,7 @@ self.server = Server('%s/my.cnf'%self.tdir) self.db = SampleDatabase("test","test",'%s/my.cnf'%self.tdir) self.engine = create_engine('mysql://test:test@localhost/test?unix_socket=%s/socket'%self.tdir, - poolclass = pool.SingletonThreadPool, pool_size=DB_POOL_SIZE, ) + poolclass = pool.SingletonThreadPool, pool_size=DB_POOL_SIZE, )#echo=True) def setUp(self): with open('%s/my.cnf'%self.tdir,'w') as cnf: