5 |
5 |
6 #relationship |
6 #relationship |
7 from sqlalchemy import ForeignKey |
7 from sqlalchemy import ForeignKey |
8 from sqlalchemy.orm import relationship, backref, object_session |
8 from sqlalchemy.orm import relationship, backref, object_session |
9 |
9 |
10 from sqlalchemy import and_ |
10 from sqlalchemy import and_ |
|
11 import sqlalchemy.sql.functions as func |
11 |
12 |
12 from ..error import JobNotFound |
13 from ..error import JobNotFound |
13 |
14 |
14 Base = declarative_base() |
15 Base = declarative_base() |
15 |
16 |
20 __tablename__ = 'userright' |
21 __tablename__ = 'userright' |
21 user_name = Column('user', String(100), ForeignKey('apiuser.name'), primary_key=True) |
22 user_name = Column('user', String(100), ForeignKey('apiuser.name'), primary_key=True) |
22 offer_name = Column('offer', String(100), ForeignKey('offer.name'), primary_key=True) |
23 offer_name = Column('offer', String(100), ForeignKey('offer.name'), primary_key=True) |
23 isDefault = Column(Boolean) |
24 isDefault = Column(Boolean) |
24 offer = relationship("Offer") |
25 offer = relationship("Offer") |
|
26 user = relationship("User") |
25 |
27 |
26 def __init__(self, offer, default=False): |
28 def __init__(self, offer, default=False): |
27 self.offer = offer |
29 self.offer = offer |
28 self.isDefault = default |
30 self.isDefault = default |
|
31 |
|
32 @property |
|
33 def bill(self): |
|
34 '''getting a list of unbilled messages grouped by Job.info''' |
|
35 query = [ func.count(Message.id).label('anz'), # anz of messages |
|
36 func.sum(Message.price).label("price"), # price of the messages |
|
37 Job.info.label('info'), # info tag |
|
38 ] |
|
39 |
|
40 filters = [ Message.isBilled==False, # only unbilled messages |
|
41 Job.user==self.user, # only jobs connected to user |
|
42 Message.offer==self.offer, # only messages in the right offer |
|
43 Message.job_hash==Job.hash, # join Message and Job |
|
44 ] |
|
45 return object_session(self).query(*query).filter(and_(*filters)).group_by(Job.info) |
29 |
46 |
30 class Offer(Base): |
47 class Offer(Base): |
31 """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.""" |
48 """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.""" |
32 __tablename__ = "offer" |
49 __tablename__ = "offer" |
33 name = Column(String(100), primary_key=True) |
50 name = Column(String(100), primary_key=True) |
41 __tablename__ = "message" |
58 __tablename__ = "message" |
42 id = Column(Integer, Sequence('message_id_seq'), primary_key=True) |
59 id = Column(Integer, Sequence('message_id_seq'), primary_key=True) |
43 recipient = Column(String(100)) |
60 recipient = Column(String(100)) |
44 isBilled = Column(Boolean) |
61 isBilled = Column(Boolean) |
45 date = Column(DateTime) |
62 date = Column(DateTime) |
46 price = Column(Numeric(8,2)) |
63 price = Column(Numeric(8,4)) |
47 job_hash = Column("job",Integer, ForeignKey('job.hash')) |
64 job_hash = Column("job", String(40), ForeignKey('job.hash')) |
48 job = relationship("Job", backref=backref('messages')) |
65 job = relationship("Job", backref=backref('messages')) |
49 offer_id = Column("offer",String(100), ForeignKey('offer.name')) |
66 offer_id = Column("offer",String(100), ForeignKey('offer.name')) |
50 offer = relationship("Offer", backref=backref('messages')) |
67 offer = relationship("Offer", backref=backref('messages')) |
51 |
68 |
52 |
69 |