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

Revision 2342, 39.8 kB (checked in by jerome, 19 years ago)

The pkbcodes command line tool now works fine with the PostgreSQL
backend. The dumpykota command can now dump billing codes too.
Still no code for LDAP though.
NB : a database upgrade is necessary AGAIN !
Severity : no need to play with this until there's LDAP support.

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