Changeset 1792 for pykota/trunk

Show
Ignore:
Timestamp:
10/10/04 12:12:21 (20 years ago)
Author:
jalet
Message:

Improved SQL queries for groups. Same work has to be done for groups print quotas.

Files:
1 modified

Legend:

Unmodified
Added
Removed
  • pykota/trunk/pykota/storages/sql.py

    r1790 r1792  
    2222# 
    2323# $Log$ 
     24# Revision 1.57  2004/10/10 10:12:21  jalet 
     25# Improved SQL queries for groups. Same work has to be done for groups print quotas. 
     26# 
    2427# Revision 1.56  2004/10/07 21:14:28  jalet 
    2528# Hopefully final fix for data encoding to and from the database 
     
    133136    def extractGroups(self) : 
    134137        """Extracts all group records.""" 
    135         result = self.doRawSearch("SELECT groups.*,sum(balance) AS balance, sum(lifetimepaid) as lifetimepaid FROM groups,users WHERE users.id IN (SELECT userid FROM groupsmembers WHERE groupid=groups.id) GROUP BY groups.id,groups.groupname,groups.limitby ORDER BY groups.id ASC") 
     138        result = self.doRawSearch("SELECT groups.*,COALESCE(SUM(balance), 0) AS balance, COALESCE(SUM(lifetimepaid), 0) as lifetimepaid FROM groups LEFT OUTER JOIN users ON users.id IN (SELECT userid FROM groupsmembers WHERE groupid=groups.id) GROUP BY groups.id,groups.groupname,groups.limitby ORDER BY groups.id ASC") 
    136139        return self.prepareRawResult(result) 
    137140         
     
    148151    def extractGpquotas(self) : 
    149152        """Extracts all grouppquota records.""" 
    150         result = self.doRawSearch("SELECT groups.groupname,printers.printername,grouppquota.*,sum(pagecounter) AS pagecounter,sum(lifepagecounter) AS lifepagecounter FROM groups,printers,grouppquota,userpquota WHERE groups.id=grouppquota.groupid AND printers.id=grouppquota.printerid AND userpquota.printerid=grouppquota.printerid AND userpquota.userid IN (SELECT userid FROM groupsmembers WHERE groupsmembers.groupid=grouppquota.groupid) GROUP BY grouppquota.id,grouppquota.groupid,grouppquota.printerid,grouppquota.softlimit,grouppquota.hardlimit,grouppquota.datelimit,groups.groupname,printers.printername ORDER BY grouppquota.id") 
     153        result = self.doRawSearch("SELECT groups.groupname,printers.printername,grouppquota.*,coalesce(sum(pagecounter), 0) AS pagecounter,coalesce(sum(lifepagecounter), 0) AS lifepagecounter FROM groups,printers,grouppquota,userpquota WHERE groups.id=grouppquota.groupid AND printers.id=grouppquota.printerid AND userpquota.printerid=grouppquota.printerid AND userpquota.userid IN (SELECT userid FROM groupsmembers WHERE groupsmembers.groupid=grouppquota.groupid) GROUP BY grouppquota.id,grouppquota.groupid,grouppquota.printerid,grouppquota.softlimit,grouppquota.hardlimit,grouppquota.datelimit,groups.groupname,printers.printername ORDER BY grouppquota.id") 
    151154        return self.prepareRawResult(result) 
    152155         
     
    200203        """Extracts group information given its name.""" 
    201204        group = StorageGroup(self, groupname) 
    202         result = self.doSearch("SELECT * FROM groups WHERE groupname=%s LIMIT 1" % self.doQuote(groupname)) 
     205        result = self.doSearch("SELECT groups.*,COALESCE(SUM(balance), 0.0) AS balance, COALESCE(SUM(lifetimepaid), 0.0) AS lifetimepaid FROM groups LEFT OUTER JOIN users ON users.id IN (SELECT userid FROM groupsmembers WHERE groupid=groups.id) WHERE groupname=%s GROUP BY groups.id,groups.groupname,groups.limitby LIMIT 1" % self.doQuote(groupname)) 
    203206        if result : 
    204207            fields = result[0] 
     
    206209            group.Name = fields.get("groupname", groupname) 
    207210            group.LimitBy = fields.get("limitby") 
    208             result = self.doSearch("SELECT SUM(balance) AS balance, SUM(lifetimepaid) AS lifetimepaid FROM users WHERE id IN (SELECT userid FROM groupsmembers WHERE groupid=%s)" % self.doQuote(group.ident)) 
    209             if result : 
    210                 fields = result[0] 
    211                 group.AccountBalance = fields.get("balance") or 0.0 
    212                 group.LifeTimePaid = fields.get("lifetimepaid") or 0.0 
     211            group.AccountBalance = fields.get("balance") 
     212            group.LifeTimePaid = fields.get("lifetimepaid") 
    213213            group.Exists = 1 
    214214        return group