root / pykota / trunk / pykota / storages / sql.py @ 2287

Revision 2287, 36.2 kB (checked in by jerome, 19 years ago)

Now stores 'hidden' in the title, filename and options fields instead of the
sentence "Hidden because of privacy concerns". When the history is retrieved,
'hidden' is automatically transformed into this sentence, translated into
the final user's preferred language. This effectively delays the translation
until the message is needed, and allows the database to be consitent when
different users with different locales use the system.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1# PyKota
2# -*- coding: ISO-8859-15 -*-
3#
4# PyKota : Print Quotas for CUPS and LPRng
5#
6# (c) 2003-2004 Jerome Alet <alet@librelogiciel.com>
7# This program is free software; you can redistribute it and/or modify
8# it under the terms of the GNU General Public License as published by
9# the Free Software Foundation; either version 2 of the License, or
10# (at your option) any later version.
11#
12# This program is distributed in the hope that it will be useful,
13# but WITHOUT ANY WARRANTY; without even the implied warranty of
14# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15# GNU General Public License for more details.
16#
17# You should have received a copy of the GNU General Public License
18# along with this program; if not, write to the Free Software
19# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
20#
21# $Id$
22#
23#
24
25from types import StringType
26from pykota.storage import PyKotaStorageError,BaseStorage,StorageObject,StorageUser,StorageGroup,StoragePrinter,StorageJob,StorageLastJob,StorageUserPQuota,StorageGroupPQuota
27
28class SQLStorage :
29    def prepareRawResult(self, result) :
30        """Prepares a raw result by including the headers."""
31        if result.ntuples() > 0 :
32            entries = [result.listfields()]
33            entries.extend(result.getresult())
34            nbfields = len(entries[0])
35            for i in range(1, len(entries)) :
36                fields = list(entries[i])
37                for j in range(nbfields) :
38                    field = fields[j]
39                    if type(field) == StringType :
40                        fields[j] = self.databaseToUserCharset(field) 
41                entries[i] = tuple(fields)   
42            return entries
43       
44    def createFilter(self, only) :   
45        """Returns the appropriate SQL filter."""
46        if only :
47            expressions = []
48            for (k, v) in only.items() :
49                expressions.append("%s=%s" % (k, self.doQuote(self.userCharsetToDatabase(v))))
50            return " AND ".join(expressions)     
51        return ""       
52       
53    def extractPrinters(self, extractonly={}) :
54        """Extracts all printer records."""
55        thefilter = self.createFilter(extractonly)
56        if thefilter :
57            thefilter = "WHERE %s" % thefilter
58        result = self.doRawSearch("SELECT * FROM printers %s ORDER BY id ASC" % thefilter)
59        return self.prepareRawResult(result)
60       
61    def extractUsers(self, extractonly={}) :
62        """Extracts all user records."""
63        thefilter = self.createFilter(extractonly)
64        if thefilter :
65            thefilter = "WHERE %s" % thefilter
66        result = self.doRawSearch("SELECT * FROM users %s ORDER BY id ASC" % thefilter)
67        return self.prepareRawResult(result)
68       
69    def extractGroups(self, extractonly={}) :
70        """Extracts all group records."""
71        thefilter = self.createFilter(extractonly)
72        if thefilter :
73            thefilter = "WHERE %s" % thefilter
74        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) %s GROUP BY groups.id,groups.groupname,groups.limitby ORDER BY groups.id ASC" % thefilter)
75        return self.prepareRawResult(result)
76       
77    def extractPayments(self, extractonly={}) :
78        """Extracts all payment records."""
79        thefilter = self.createFilter(extractonly)
80        if thefilter :
81            thefilter = "AND %s" % thefilter
82        result = self.doRawSearch("SELECT username,payments.* FROM users,payments WHERE users.id=payments.userid %s ORDER BY payments.id ASC" % thefilter)
83        return self.prepareRawResult(result)
84       
85    def extractUpquotas(self, extractonly={}) :
86        """Extracts all userpquota records."""
87        thefilter = self.createFilter(extractonly)
88        if thefilter :
89            thefilter = "AND %s" % thefilter
90        result = self.doRawSearch("SELECT users.username,printers.printername,userpquota.* FROM users,printers,userpquota WHERE users.id=userpquota.userid AND printers.id=userpquota.printerid %s ORDER BY userpquota.id ASC" % thefilter)
91        return self.prepareRawResult(result)
92       
93    def extractGpquotas(self, extractonly={}) :
94        """Extracts all grouppquota records."""
95        thefilter = self.createFilter(extractonly)
96        if thefilter :
97            thefilter = "AND %s" % thefilter
98        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) %s GROUP BY grouppquota.id,grouppquota.groupid,grouppquota.printerid,grouppquota.softlimit,grouppquota.hardlimit,grouppquota.datelimit,groups.groupname,printers.printername ORDER BY grouppquota.id" % thefilter)
99        return self.prepareRawResult(result)
100       
101    def extractUmembers(self, extractonly={}) :
102        """Extracts all user groups members."""
103        thefilter = self.createFilter(extractonly)
104        if thefilter :
105            thefilter = "AND %s" % thefilter
106        result = self.doRawSearch("SELECT groups.groupname, users.username, groupsmembers.* FROM groups,users,groupsmembers WHERE users.id=groupsmembers.userid AND groups.id=groupsmembers.groupid %s ORDER BY groupsmembers.groupid, groupsmembers.userid ASC" % thefilter)
107        return self.prepareRawResult(result)
108       
109    def extractPmembers(self, extractonly={}) :
110        """Extracts all printer groups members."""
111        for (k, v) in extractonly.items() :
112            if k == "pgroupname" :
113                del extractonly[k]
114                extractonly["p1.printername"] = v
115            elif k == "printername" :
116                del extractonly[k]
117                extractonly["p2.printername"] = v
118        thefilter = self.createFilter(extractonly)
119        if thefilter :
120            thefilter = "AND %s" % thefilter
121        result = self.doRawSearch("SELECT p1.printername as pgroupname, p2.printername as printername, printergroupsmembers.* FROM printers p1, printers p2, printergroupsmembers WHERE p1.id=printergroupsmembers.groupid AND p2.id=printergroupsmembers.printerid %s ORDER BY printergroupsmembers.groupid, printergroupsmembers.printerid ASC" % thefilter)
122        return self.prepareRawResult(result)
123       
124    def extractHistory(self, extractonly={}) :
125        """Extracts all jobhistory records."""
126        startdate = extractonly.get("start")
127        enddate = extractonly.get("end")
128        for limit in ("start", "end") :
129            try :
130                del extractonly[limit]
131            except KeyError :   
132                pass
133        thefilter = self.createFilter(extractonly)
134        if thefilter :
135            thefilter = "AND %s" % thefilter
136        (startdate, enddate) = self.cleanDates(startdate, enddate)
137        if startdate and enddate : 
138            thefilter = "%s AND jobdate>=%s AND jobdate<=%s" % (thefilter, self.doQuote(startdate), self.doQuote(enddate))
139        result = self.doRawSearch("SELECT users.username,printers.printername,jobhistory.* FROM users,printers,jobhistory WHERE users.id=jobhistory.userid AND printers.id=jobhistory.printerid %s ORDER BY jobhistory.id ASC" % thefilter)
140        return self.prepareRawResult(result)
141           
142    def getAllUsersNames(self) :   
143        """Extracts all user names."""
144        usernames = []
145        result = self.doSearch("SELECT username FROM users")
146        if result :
147            usernames = [record["username"] for record in result]
148        return usernames
149       
150    def getAllGroupsNames(self) :   
151        """Extracts all group names."""
152        groupnames = []
153        result = self.doSearch("SELECT groupname FROM groups")
154        if result :
155            groupnames = [record["groupname"] for record in result]
156        return groupnames
157       
158    def getUserNbJobsFromHistory(self, user) :
159        """Returns the number of jobs the user has in history."""
160        result = self.doSearch("SELECT COUNT(*) FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident))
161        if result :
162            return result[0]["count"]
163        return 0
164       
165    def getUserFromBackend(self, username) :   
166        """Extracts user information given its name."""
167        user = StorageUser(self, username)
168        result = self.doSearch("SELECT * FROM users WHERE username=%s LIMIT 1" % self.doQuote(username))
169        if result :
170            fields = result[0]
171            user.ident = fields.get("id")
172            user.Name = fields.get("username", username)
173            user.LimitBy = fields.get("limitby") or "quota"
174            user.AccountBalance = fields.get("balance")
175            user.LifeTimePaid = fields.get("lifetimepaid")
176            user.Email = fields.get("email")
177            user.OverCharge = fields.get("overcharge", 1.0)
178            user.Exists = 1
179        return user
180       
181    def getGroupFromBackend(self, groupname) :   
182        """Extracts group information given its name."""
183        group = StorageGroup(self, groupname)
184        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))
185        if result :
186            fields = result[0]
187            group.ident = fields.get("id")
188            group.Name = fields.get("groupname", groupname)
189            group.LimitBy = fields.get("limitby") or "quota"
190            group.AccountBalance = fields.get("balance")
191            group.LifeTimePaid = fields.get("lifetimepaid")
192            group.Exists = 1
193        return group
194       
195    def getPrinterFromBackend(self, printername) :       
196        """Extracts printer information given its name."""
197        printer = StoragePrinter(self, printername)
198        result = self.doSearch("SELECT * FROM printers WHERE printername=%s LIMIT 1" % self.doQuote(printername))
199        if result :
200            fields = result[0]
201            printer.ident = fields.get("id")
202            printer.Name = fields.get("printername", printername)
203            printer.PricePerJob = fields.get("priceperjob") or 0.0
204            printer.PricePerPage = fields.get("priceperpage") or 0.0
205            printer.Description = self.databaseToUserCharset(fields.get("description") or "")
206            printer.Exists = 1
207        return printer   
208       
209    def getUserPQuotaFromBackend(self, user, printer) :       
210        """Extracts a user print quota."""
211        userpquota = StorageUserPQuota(self, user, printer)
212        if printer.Exists and user.Exists :
213            result = self.doSearch("SELECT * FROM userpquota WHERE userid=%s AND printerid=%s" % (self.doQuote(user.ident), self.doQuote(printer.ident)))
214            if result :
215                fields = result[0]
216                userpquota.ident = fields.get("id")
217                userpquota.PageCounter = fields.get("pagecounter")
218                userpquota.LifePageCounter = fields.get("lifepagecounter")
219                userpquota.SoftLimit = fields.get("softlimit")
220                userpquota.HardLimit = fields.get("hardlimit")
221                userpquota.DateLimit = fields.get("datelimit")
222                userpquota.WarnCount = fields.get("warncount")
223                userpquota.Exists = 1
224        return userpquota
225       
226    def getGroupPQuotaFromBackend(self, group, printer) :       
227        """Extracts a group print quota."""
228        grouppquota = StorageGroupPQuota(self, group, printer)
229        if group.Exists :
230            result = self.doSearch("SELECT * FROM grouppquota WHERE groupid=%s AND printerid=%s" % (self.doQuote(group.ident), self.doQuote(printer.ident)))
231            if result :
232                fields = result[0]
233                grouppquota.ident = fields.get("id")
234                grouppquota.SoftLimit = fields.get("softlimit")
235                grouppquota.HardLimit = fields.get("hardlimit")
236                grouppquota.DateLimit = fields.get("datelimit")
237                result = self.doSearch("SELECT SUM(lifepagecounter) AS lifepagecounter, SUM(pagecounter) AS pagecounter FROM userpquota WHERE printerid=%s AND userid IN (SELECT userid FROM groupsmembers WHERE groupid=%s)" % (self.doQuote(printer.ident), self.doQuote(group.ident)))
238                if result :
239                    fields = result[0]
240                    grouppquota.PageCounter = fields.get("pagecounter") or 0
241                    grouppquota.LifePageCounter = fields.get("lifepagecounter") or 0
242                grouppquota.Exists = 1
243        return grouppquota
244       
245    def getPrinterLastJobFromBackend(self, printer) :       
246        """Extracts a printer's last job information."""
247        lastjob = StorageLastJob(self, printer)
248        result = self.doSearch("SELECT jobhistory.id, jobid, userid, username, pagecounter, jobsize, jobprice, filename, title, copies, options, hostname, jobdate, md5sum, pages, billingcode FROM jobhistory, users WHERE printerid=%s AND userid=users.id ORDER BY jobdate DESC LIMIT 1" % self.doQuote(printer.ident))
249        if result :
250            fields = result[0]
251            lastjob.ident = fields.get("id")
252            lastjob.JobId = fields.get("jobid")
253            lastjob.UserName = fields.get("username")
254            lastjob.PrinterPageCounter = fields.get("pagecounter")
255            lastjob.JobSize = fields.get("jobsize")
256            lastjob.JobPrice = fields.get("jobprice")
257            lastjob.JobAction = fields.get("action")
258            lastjob.JobFileName = self.databaseToUserCharset(fields.get("filename") or "") 
259            lastjob.JobTitle = self.databaseToUserCharset(fields.get("title") or "") 
260            lastjob.JobCopies = fields.get("copies")
261            lastjob.JobOptions = self.databaseToUserCharset(fields.get("options") or "") 
262            lastjob.JobDate = fields.get("jobdate")
263            lastjob.JobHostName = fields.get("hostname")
264            lastjob.JobSizeBytes = fields.get("jobsizebytes")
265            lastjob.JobMD5Sum = fields.get("md5sum")
266            lastjob.JobPages = fields.get("pages")
267            lastjob.JobBillingCode = self.databaseToUserCharset(fields.get("billingcode"))
268            if lastjob.JobTitle == lastjob.JobFileName == lastjob.JobOptions == "hidden" :
269                (lastjob.JobTitle, lastjob.JobFileName, lastjob.JobOptions) = (_("Hidden because of privacy concerns"),) * 3
270            lastjob.Exists = 1
271        return lastjob
272           
273    def getGroupMembersFromBackend(self, group) :       
274        """Returns the group's members list."""
275        groupmembers = []
276        result = self.doSearch("SELECT * FROM groupsmembers JOIN users ON groupsmembers.userid=users.id WHERE groupid=%s" % self.doQuote(group.ident))
277        if result :
278            for record in result :
279                user = StorageUser(self, record.get("username"))
280                user.ident = record.get("userid")
281                user.LimitBy = record.get("limitby") or "quota"
282                user.AccountBalance = record.get("balance")
283                user.LifeTimePaid = record.get("lifetimepaid")
284                user.Email = record.get("email")
285                user.OverCharge = record.get("overcharge")
286                user.Exists = 1
287                groupmembers.append(user)
288                self.cacheEntry("USERS", user.Name, user)
289        return groupmembers       
290       
291    def getUserGroupsFromBackend(self, user) :       
292        """Returns the user's groups list."""
293        groups = []
294        result = self.doSearch("SELECT groupname FROM groupsmembers JOIN groups ON groupsmembers.groupid=groups.id WHERE userid=%s" % self.doQuote(user.ident))
295        if result :
296            for record in result :
297                groups.append(self.getGroup(record.get("groupname")))
298        return groups       
299       
300    def getParentPrintersFromBackend(self, printer) :   
301        """Get all the printer groups this printer is a member of."""
302        pgroups = []
303        result = self.doSearch("SELECT groupid,printername FROM printergroupsmembers JOIN printers ON groupid=id WHERE printerid=%s" % self.doQuote(printer.ident))
304        if result :
305            for record in result :
306                if record["groupid"] != printer.ident : # in case of integrity violation
307                    parentprinter = self.getPrinter(record.get("printername"))
308                    if parentprinter.Exists :
309                        pgroups.append(parentprinter)
310        return pgroups
311       
312    def getMatchingPrinters(self, printerpattern) :
313        """Returns the list of all printers for which name matches a certain pattern."""
314        printers = []
315        # We 'could' do a SELECT printername FROM printers WHERE printername LIKE ...
316        # but we don't because other storages semantics may be different, so every
317        # storage should use fnmatch to match patterns and be storage agnostic
318        result = self.doSearch("SELECT * FROM printers")
319        if result :
320            for record in result :
321                if self.tool.matchString(record["printername"], printerpattern.split(",")) :
322                    printer = StoragePrinter(self, record["printername"])
323                    printer.ident = record.get("id")
324                    printer.PricePerJob = record.get("priceperjob") or 0.0
325                    printer.PricePerPage = record.get("priceperpage") or 0.0
326                    printer.Description = self.databaseToUserCharset(record.get("description") or "") 
327                    printer.Exists = 1
328                    printers.append(printer)
329                    self.cacheEntry("PRINTERS", printer.Name, printer)
330        return printers       
331       
332    def getPrinterUsersAndQuotas(self, printer, names=["*"]) :       
333        """Returns the list of users who uses a given printer, along with their quotas."""
334        usersandquotas = []
335        result = self.doSearch("SELECT users.id as uid,username,balance,lifetimepaid,limitby,email,overcharge,userpquota.id,lifepagecounter,pagecounter,softlimit,hardlimit,datelimit,warncount FROM users JOIN userpquota ON users.id=userpquota.userid AND printerid=%s ORDER BY username ASC" % self.doQuote(printer.ident))
336        if result :
337            for record in result :
338                if self.tool.matchString(record.get("username"), names) :
339                    user = StorageUser(self, record.get("username"))
340                    user.ident = record.get("uid")
341                    user.LimitBy = record.get("limitby") or "quota"
342                    user.AccountBalance = record.get("balance")
343                    user.LifeTimePaid = record.get("lifetimepaid")
344                    user.Email = record.get("email") 
345                    user.OverCharge = record.get("overcharge")
346                    user.Exists = 1
347                    userpquota = StorageUserPQuota(self, user, printer)
348                    userpquota.ident = record.get("id")
349                    userpquota.PageCounter = record.get("pagecounter")
350                    userpquota.LifePageCounter = record.get("lifepagecounter")
351                    userpquota.SoftLimit = record.get("softlimit")
352                    userpquota.HardLimit = record.get("hardlimit")
353                    userpquota.DateLimit = record.get("datelimit")
354                    userpquota.WarnCount = record.get("warncount")
355                    userpquota.Exists = 1
356                    usersandquotas.append((user, userpquota))
357                    self.cacheEntry("USERS", user.Name, user)
358                    self.cacheEntry("USERPQUOTAS", "%s@%s" % (user.Name, printer.Name), userpquota)
359        return usersandquotas
360               
361    def getPrinterGroupsAndQuotas(self, printer, names=["*"]) :       
362        """Returns the list of groups which uses a given printer, along with their quotas."""
363        groupsandquotas = []
364        result = self.doSearch("SELECT groupname FROM groups JOIN grouppquota ON groups.id=grouppquota.groupid AND printerid=%s ORDER BY groupname ASC" % self.doQuote(printer.ident))
365        if result :
366            for record in result :
367                if self.tool.matchString(record.get("groupname"), names) :
368                    group = self.getGroup(record.get("groupname"))
369                    grouppquota = self.getGroupPQuota(group, printer)
370                    groupsandquotas.append((group, grouppquota))
371        return groupsandquotas
372       
373    def addPrinter(self, printername) :       
374        """Adds a printer to the quota storage, returns it."""
375        self.doModify("INSERT INTO printers (printername) VALUES (%s)" % self.doQuote(printername))
376        return self.getPrinter(printername)
377       
378    def addUser(self, user) :       
379        """Adds a user to the quota storage, returns its id."""
380        self.doModify("INSERT INTO users (username, limitby, balance, lifetimepaid, email, overcharge) VALUES (%s, %s, %s, %s, %s, %s)" % (self.doQuote(user.Name), self.doQuote(user.LimitBy or 'quota'), self.doQuote(user.AccountBalance or 0.0), self.doQuote(user.LifeTimePaid or 0.0), self.doQuote(user.Email), self.doQuote(user.OverCharge)))
381        return self.getUser(user.Name)
382       
383    def addGroup(self, group) :       
384        """Adds a group to the quota storage, returns its id."""
385        self.doModify("INSERT INTO groups (groupname, limitby) VALUES (%s, %s)" % (self.doQuote(group.Name), self.doQuote(group.LimitBy or "quota")))
386        return self.getGroup(group.Name)
387
388    def addUserToGroup(self, user, group) :   
389        """Adds an user to a group."""
390        result = self.doSearch("SELECT COUNT(*) AS mexists FROM groupsmembers WHERE groupid=%s AND userid=%s" % (self.doQuote(group.ident), self.doQuote(user.ident)))
391        try :
392            mexists = int(result[0].get("mexists"))
393        except (IndexError, TypeError) :   
394            mexists = 0
395        if not mexists :   
396            self.doModify("INSERT INTO groupsmembers (groupid, userid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(user.ident)))
397           
398    def addUserPQuota(self, user, printer) :
399        """Initializes a user print quota on a printer."""
400        self.doModify("INSERT INTO userpquota (userid, printerid) VALUES (%s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident)))
401        return self.getUserPQuota(user, printer)
402       
403    def addGroupPQuota(self, group, printer) :
404        """Initializes a group print quota on a printer."""
405        self.doModify("INSERT INTO grouppquota (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(printer.ident)))
406        return self.getGroupPQuota(group, printer)
407       
408    def writePrinterPrices(self, printer) :   
409        """Write the printer's prices back into the storage."""
410        self.doModify("UPDATE printers SET priceperpage=%s, priceperjob=%s WHERE id=%s" % (self.doQuote(printer.PricePerPage), self.doQuote(printer.PricePerJob), self.doQuote(printer.ident)))
411       
412    def writePrinterDescription(self, printer) :   
413        """Write the printer's description back into the storage."""
414        description = self.userCharsetToDatabase(printer.Description)
415        self.doModify("UPDATE printers SET description=%s WHERE id=%s" % (self.doQuote(description), self.doQuote(printer.ident)))
416       
417    def writeUserOverCharge(self, user, factor) :
418        """Sets the user's overcharging coefficient."""
419        self.doModify("UPDATE users SET overcharge=%s WHERE id=%s" % (self.doQuote(factor), self.doQuote(user.ident)))
420       
421    def writeUserLimitBy(self, user, limitby) :   
422        """Sets the user's limiting factor."""
423        self.doModify("UPDATE users SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(user.ident)))
424       
425    def writeGroupLimitBy(self, group, limitby) :   
426        """Sets the group's limiting factor."""
427        self.doModify("UPDATE groups SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(group.ident)))
428       
429    def writeUserPQuotaDateLimit(self, userpquota, datelimit) :   
430        """Sets the date limit permanently for a user print quota."""
431        self.doModify("UPDATE userpquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(userpquota.ident)))
432           
433    def writeGroupPQuotaDateLimit(self, grouppquota, datelimit) :   
434        """Sets the date limit permanently for a group print quota."""
435        self.doModify("UPDATE grouppquota SET datelimit=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(grouppquota.ident)))
436       
437    def increaseUserPQuotaPagesCounters(self, userpquota, nbpages) :   
438        """Increase page counters for a user print quota."""
439        self.doModify("UPDATE userpquota SET pagecounter=pagecounter + %s,lifepagecounter=lifepagecounter + %s WHERE id=%s" % (self.doQuote(nbpages), self.doQuote(nbpages), self.doQuote(userpquota.ident)))
440       
441    def writeUserPQuotaPagesCounters(self, userpquota, newpagecounter, newlifepagecounter) :   
442        """Sets the new page counters permanently for a user print quota."""
443        self.doModify("UPDATE userpquota SET pagecounter=%s, lifepagecounter=%s, warncount=0, datelimit=NULL WHERE id=%s" % (self.doQuote(newpagecounter), self.doQuote(newlifepagecounter), self.doQuote(userpquota.ident)))
444       
445    def decreaseUserAccountBalance(self, user, amount) :   
446        """Decreases user's account balance from an amount."""
447        self.doModify("UPDATE users SET balance=balance - %s WHERE id=%s" % (self.doQuote(amount), self.doQuote(user.ident)))
448       
449    def writeUserAccountBalance(self, user, newbalance, newlifetimepaid=None) :   
450        """Sets the new account balance and eventually new lifetime paid."""
451        if newlifetimepaid is not None :
452            self.doModify("UPDATE users SET balance=%s, lifetimepaid=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(newlifetimepaid), self.doQuote(user.ident)))
453        else :   
454            self.doModify("UPDATE users SET balance=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(user.ident)))
455           
456    def writeNewPayment(self, user, amount) :       
457        """Adds a new payment to the payments history."""
458        self.doModify("INSERT INTO payments (userid, amount) VALUES (%s, %s)" % (self.doQuote(user.ident), self.doQuote(amount)))
459       
460    def writeLastJobSize(self, lastjob, jobsize, jobprice) :       
461        """Sets the last job's size permanently."""
462        self.doModify("UPDATE jobhistory SET jobsize=%s, jobprice=%s WHERE id=%s" % (self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(lastjob.ident)))
463       
464    def writeJobNew(self, printer, user, jobid, pagecounter, action, jobsize=None, jobprice=None, filename=None, title=None, copies=None, options=None, clienthost=None, jobsizebytes=None, jobmd5sum=None, jobpages=None, jobbilling=None) :
465        """Adds a job in a printer's history."""
466        if self.privacy :   
467            # For legal reasons, we want to hide the title, filename and options
468            title = filename = options = "hidden"
469        filename = self.userCharsetToDatabase(filename)
470        title = self.userCharsetToDatabase(title)
471        options = self.userCharsetToDatabase(options)
472        jobbilling = self.userCharsetToDatabase(jobbilling)
473        if (not self.disablehistory) or (not printer.LastJob.Exists) :
474            if jobsize is not None :
475                self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action, jobsize, jobprice, filename, title, copies, options, hostname, jobsizebytes, md5sum, pages, billingcode) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident), self.doQuote(jobid), self.doQuote(pagecounter), self.doQuote(action), self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(filename), self.doQuote(title), self.doQuote(copies), self.doQuote(options), self.doQuote(clienthost), self.doQuote(jobsizebytes), self.doQuote(jobmd5sum), self.doQuote(jobpages), self.doQuote(jobbilling)))
476            else :   
477                self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action, filename, title, copies, options, hostname, jobsizebytes, md5sum, pages, billingcode) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident), self.doQuote(jobid), self.doQuote(pagecounter), self.doQuote(action), self.doQuote(filename), self.doQuote(title), self.doQuote(copies), self.doQuote(options), self.doQuote(clienthost), self.doQuote(jobsizebytes), self.doQuote(jobmd5sum), self.doQuote(jobpages), self.doQuote(jobbilling)))
478        else :       
479            # here we explicitly want to reset jobsize to NULL if needed
480            self.doModify("UPDATE jobhistory SET userid=%s, jobid=%s, pagecounter=%s, action=%s, jobsize=%s, jobprice=%s, filename=%s, title=%s, copies=%s, options=%s, hostname=%s, jobsizebytes=%s, md5sum=%s, pages=%s, billingcode=%s, jobdate=now() WHERE id=%s" % (self.doQuote(user.ident), self.doQuote(jobid), self.doQuote(pagecounter), self.doQuote(action), self.doQuote(jobsize), self.doQuote(jobprice), self.doQuote(filename), self.doQuote(title), self.doQuote(copies), self.doQuote(options), self.doQuote(clienthost), self.doQuote(jobsizebytes), self.doQuote(jobmd5sum), self.doQuote(jobpages), self.doQuote(jobbilling), self.doQuote(printer.LastJob.ident)))
481           
482    def writeUserPQuotaLimits(self, userpquota, softlimit, hardlimit) :
483        """Sets soft and hard limits for a user quota."""
484        self.doModify("UPDATE userpquota SET softlimit=%s, hardlimit=%s, warncount=0, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(userpquota.ident)))
485       
486    def writeUserPQuotaWarnCount(self, userpquota, warncount) :
487        """Sets the warn counter value for a user quota."""
488        self.doModify("UPDATE userpquota SET warncount=%s WHERE id=%s" % (self.doQuote(warncount), self.doQuote(userpquota.ident)))
489       
490    def increaseUserPQuotaWarnCount(self, userpquota) :
491        """Increases the warn counter value for a user quota."""
492        self.doModify("UPDATE userpquota SET warncount=warncount+1 WHERE id=%s" % self.doQuote(userpquota.ident))
493       
494    def writeGroupPQuotaLimits(self, grouppquota, softlimit, hardlimit) :
495        """Sets soft and hard limits for a group quota on a specific printer."""
496        self.doModify("UPDATE grouppquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(grouppquota.ident)))
497
498    def writePrinterToGroup(self, pgroup, printer) :
499        """Puts a printer into a printer group."""
500        children = []
501        result = self.doSearch("SELECT printerid FROM printergroupsmembers WHERE groupid=%s" % self.doQuote(pgroup.ident))
502        if result :
503            for record in result :
504                children.append(record.get("printerid")) # TODO : put this into the database integrity rules
505        if printer.ident not in children :       
506            self.doModify("INSERT INTO printergroupsmembers (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
507       
508    def removePrinterFromGroup(self, pgroup, printer) :
509        """Removes a printer from a printer group."""
510        self.doModify("DELETE FROM printergroupsmembers WHERE groupid=%s AND printerid=%s" % (self.doQuote(pgroup.ident), self.doQuote(printer.ident)))
511       
512    def retrieveHistory(self, user=None, printer=None, hostname=None, billingcode=None, limit=100, start=None, end=None) :
513        """Retrieves all print jobs for user on printer (or all) between start and end date, limited to first 100 results."""
514        query = "SELECT jobhistory.*,username,printername FROM jobhistory,users,printers WHERE users.id=userid AND printers.id=printerid"
515        where = []
516        if user is not None : # user.ident is None anyway if user doesn't exist
517            where.append("userid=%s" % self.doQuote(user.ident))
518        if printer is not None : # printer.ident is None anyway if printer doesn't exist
519            where.append("printerid=%s" % self.doQuote(printer.ident))
520        if hostname is not None :   
521            where.append("hostname=%s" % self.doQuote(hostname))
522        if billingcode is not None :   
523            where.append("billingcode=%s" % self.doQuote(self.userCharsetToDatabase(billingcode)))
524        if start is not None :   
525            where.append("jobdate>=%s" % self.doQuote(start))
526        if end is not None :   
527            where.append("jobdate<=%s" % self.doQuote(end))
528        if where :   
529            query += " AND %s" % " AND ".join(where)
530        query += " ORDER BY id DESC"
531        if limit :
532            query += " LIMIT %s" % self.doQuote(int(limit))
533        jobs = []   
534        result = self.doSearch(query)   
535        if result :
536            for fields in result :
537                job = StorageJob(self)
538                job.ident = fields.get("id")
539                job.JobId = fields.get("jobid")
540                job.PrinterPageCounter = fields.get("pagecounter")
541                job.JobSize = fields.get("jobsize")
542                job.JobPrice = fields.get("jobprice")
543                job.JobAction = fields.get("action")
544                job.JobFileName = self.databaseToUserCharset(fields.get("filename") or "") 
545                job.JobTitle = self.databaseToUserCharset(fields.get("title") or "") 
546                job.JobCopies = fields.get("copies")
547                job.JobOptions = self.databaseToUserCharset(fields.get("options") or "") 
548                job.JobDate = fields.get("jobdate")
549                job.JobHostName = fields.get("hostname")
550                job.JobSizeBytes = fields.get("jobsizebytes")
551                job.JobMD5Sum = fields.get("md5sum")
552                job.JobPages = fields.get("pages")
553                job.JobBillingCode = self.databaseToUserCharset(fields.get("billingcode"))
554                job.UserName = fields.get("username")
555                job.PrinterName = fields.get("printername")
556                if job.JobTitle == job.JobFileName == job.JobOptions == "hidden" :
557                    (job.JobTitle, job.JobFileName, job.JobOptions) = (_("Hidden because of privacy concerns"),) * 3
558                job.Exists = 1
559                jobs.append(job)
560        return jobs
561       
562    def deleteUser(self, user) :   
563        """Completely deletes an user from the Quota Storage."""
564        # TODO : What should we do if we delete the last person who used a given printer ?
565        # TODO : we can't reassign the last job to the previous one, because next user would be
566        # TODO : incorrectly charged (overcharged).
567        for q in [ 
568                    "DELETE FROM payments WHERE userid=%s" % self.doQuote(user.ident),
569                    "DELETE FROM groupsmembers WHERE userid=%s" % self.doQuote(user.ident),
570                    "DELETE FROM jobhistory WHERE userid=%s" % self.doQuote(user.ident),
571                    "DELETE FROM userpquota WHERE userid=%s" % self.doQuote(user.ident),
572                    "DELETE FROM users WHERE id=%s" % self.doQuote(user.ident),
573                  ] :
574            self.doModify(q)
575       
576    def deleteGroup(self, group) :   
577        """Completely deletes a group from the Quota Storage."""
578        for q in [
579                   "DELETE FROM groupsmembers WHERE groupid=%s" % self.doQuote(group.ident),
580                   "DELETE FROM grouppquota WHERE groupid=%s" % self.doQuote(group.ident),
581                   "DELETE FROM groups WHERE id=%s" % self.doQuote(group.ident),
582                 ] : 
583            self.doModify(q)
584           
585    def deletePrinter(self, printer) :   
586        """Completely deletes a printer from the Quota Storage."""
587        for q in [ 
588                    "DELETE FROM printergroupsmembers WHERE groupid=%s OR printerid=%s" % (self.doQuote(printer.ident), self.doQuote(printer.ident)),
589                    "DELETE FROM jobhistory WHERE printerid=%s" % self.doQuote(printer.ident),
590                    "DELETE FROM grouppquota WHERE printerid=%s" % self.doQuote(printer.ident),
591                    "DELETE FROM userpquota WHERE printerid=%s" % self.doQuote(printer.ident),
592                    "DELETE FROM printers WHERE id=%s" % self.doQuote(printer.ident),
593                  ] :
594            self.doModify(q)
595       
Note: See TracBrowser for help on using the browser.