111 | | def doParseResult(self, result) : |
112 | | """Returns the result as a list of Python mappings.""" |
113 | | if (result is not None) and (result.ntuples() > 0) : |
114 | | return result.dictresult() |
115 | | |
| 133 | def getUser(self, username) : |
| 134 | """Extracts user information given its name.""" |
| 135 | user = StorageUser(self, username) |
| 136 | result = self.doSearch("SELECT * FROM users WHERE username=%s LIMIT 1" % self.doQuote(username)) |
| 137 | if result : |
| 138 | fields = result[0] |
| 139 | user.ident = fields.get("id") |
| 140 | user.LimitBy = fields.get("limitby") |
| 141 | user.AccountBalance = fields.get("balance") |
| 142 | user.LifeTimePaid = fields.get("lifetimepaid") |
| 143 | user.Exists = 1 |
| 144 | return user |
| 145 | |
| 146 | def getGroup(self, groupname) : |
| 147 | """Extracts group information given its name.""" |
| 148 | group = StorageGroup(self, groupname) |
| 149 | result = self.doSearch("SELECT * FROM groups WHERE groupname=%s LIMIT 1" % self.doQuote(groupname)) |
| 150 | if result : |
| 151 | fields = result[0] |
| 152 | group.ident = fields.get("id") |
| 153 | group.LimitBy = fields.get("limitby") |
| 154 | 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)) |
| 155 | if result : |
| 156 | fields = result[0] |
| 157 | group.AccountBalance = fields.get("balance") |
| 158 | group.LifeTimePaid = fields.get("lifetimepaid") |
| 159 | group.Exists = 1 |
| 160 | return group |
| 161 | |
| 162 | def getPrinter(self, printername) : |
| 163 | """Extracts printer information given its name.""" |
| 164 | printer = StoragePrinter(self, printername) |
| 165 | result = self.doSearch("SELECT * FROM printers WHERE printername=%s LIMIT 1" % self.doQuote(printername)) |
| 166 | if result : |
| 167 | fields = result[0] |
| 168 | printer.ident = fields.get("id") |
| 169 | printer.PricePerJob = fields.get("priceperjob") |
| 170 | printer.PricePerPage = fields.get("priceperpage") |
| 171 | printer.LastJob = self.getPrinterLastJob(printer) |
| 172 | printer.Exists = 1 |
| 173 | return printer |
| 174 | |
| 175 | def getUserGroups(self, user) : |
| 176 | """Returns the user's groups list.""" |
| 177 | groups = [] |
| 178 | result = self.doSearch("SELECT groupname FROM groupsmembers JOIN groups ON groupsmembers.groupid=groups.id WHERE userid=%s" % self.doQuote(user.ident)) |
| 179 | if result : |
| 180 | for record in result : |
| 181 | groups.append(self.getGroup(record.get("groupname"))) |
| 182 | return groups |
| 183 | |
| 184 | def getGroupMembers(self, group) : |
| 185 | """Returns the group's members list.""" |
| 186 | groupmembers = [] |
| 187 | result = self.doSearch("SELECT * FROM groupsmembers JOIN users ON groupsmembers.userid=users.id WHERE groupid=%s" % self.doQuote(group.ident)) |
| 188 | if result : |
| 189 | for record in result : |
| 190 | user = StorageUser(self, record.get("username")) |
| 191 | user.ident = record.get("userid") |
| 192 | user.LimitBy = record.get("limitby") |
| 193 | user.AccountBalance = record.get("balance") |
| 194 | user.LifeTimePaid = record.get("lifetimepaid") |
| 195 | user.Exists = 1 |
| 196 | groupmembers.append(user) |
| 197 | return groupmembers |
| 198 | |
| 199 | def getUserPQuota(self, user, printer) : |
| 200 | """Extracts a user print quota.""" |
| 201 | userpquota = StorageUserPQuota(self, user, printer) |
| 202 | if user.Exists : |
| 203 | result = self.doSearch("SELECT id, lifepagecounter, pagecounter, softlimit, hardlimit, datelimit FROM userpquota WHERE userid=%s AND printerid=%s" % (self.doQuote(user.ident), self.doQuote(printer.ident))) |
| 204 | if result : |
| 205 | fields = result[0] |
| 206 | userpquota.ident = fields.get("id") |
| 207 | userpquota.PageCounter = fields.get("pagecounter") |
| 208 | userpquota.LifePageCounter = fields.get("lifepagecounter") |
| 209 | userpquota.SoftLimit = fields.get("softlimit") |
| 210 | userpquota.HardLimit = fields.get("hardlimit") |
| 211 | userpquota.DateLimit = fields.get("datelimit") |
| 212 | userpquota.Exists = 1 |
| 213 | return userpquota |
| 214 | |
| 215 | def getGroupPQuota(self, group, printer) : |
| 216 | """Extracts a group print quota.""" |
| 217 | grouppquota = StorageGroupPQuota(self, group, printer) |
| 218 | if group.Exists : |
| 219 | result = self.doSearch("SELECT id, softlimit, hardlimit, datelimit FROM grouppquota WHERE groupid=%s AND printerid=%s" % (self.doQuote(group.ident), self.doQuote(printer.ident))) |
| 220 | if result : |
| 221 | fields = result[0] |
| 222 | grouppquota.ident = fields.get("id") |
| 223 | grouppquota.SoftLimit = fields.get("softlimit") |
| 224 | grouppquota.HardLimit = fields.get("hardlimit") |
| 225 | grouppquota.DateLimit = fields.get("datelimit") |
| 226 | 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))) |
| 227 | if result : |
| 228 | fields = result[0] |
| 229 | grouppquota.PageCounter = fields.get("pagecounter") |
| 230 | grouppquota.LifePageCounter = fields.get("lifepagecounter") |
| 231 | grouppquota.Exists = 1 |
| 232 | return grouppquota |
| 233 | |
| 234 | def getPrinterLastJob(self, printer) : |
| 235 | """Extracts a printer's last job information.""" |
| 236 | lastjob = StorageLastJob(self, printer) |
| 237 | result = self.doSearch("SELECT jobhistory.id, jobid, userid, username, pagecounter, jobsize, jobdate FROM jobhistory, users WHERE printerid=%s AND userid=users.id ORDER BY jobdate DESC LIMIT 1" % self.doQuote(printer.ident)) |
| 238 | if result : |
| 239 | fields = result[0] |
| 240 | lastjob.ident = fields.get("id") |
| 241 | lastjob.JobId = fields.get("jobid") |
| 242 | lastjob.User = self.getUser(fields.get("username")) |
| 243 | lastjob.PrinterPageCounter = fields.get("pagecounter") |
| 244 | lastjob.JobSize = fields.get("jobsize") |
| 245 | lastjob.JobAction = fields.get("action") |
| 246 | lastjob.JobDate = fields.get("jobdate") |
| 247 | lastjob.Exists = 1 |
| 248 | return lastjob |
| 249 | |
122 | | result = self.doQuery("SELECT id, printername FROM printers") |
123 | | result = self.doParseResult(result) |
124 | | if result is not None : |
125 | | for printer in result : |
126 | | if fnmatch.fnmatchcase(printer["printername"], printerpattern) : |
127 | | printerslist.append((printer["id"], printer["printername"])) |
128 | | return printerslist |
129 | | |
130 | | def getPrinterId(self, printername) : |
131 | | """Returns a printerid given a printername.""" |
132 | | result = self.doQuery("SELECT id FROM printers WHERE printername=%s" % self.doQuote(printername)) |
133 | | try : |
134 | | return self.doParseResult(result)[0]["id"] |
135 | | except TypeError : # Not found |
136 | | return |
137 | | |
138 | | def getPrinterPrices(self, printerid) : |
139 | | """Returns a printer prices per page and per job given a printerid.""" |
140 | | result = self.doQuery("SELECT priceperpage, priceperjob FROM printers WHERE id=%s" % self.doQuote(printerid)) |
141 | | try : |
142 | | printerprices = self.doParseResult(result)[0] |
143 | | return (printerprices["priceperpage"], printerprices["priceperjob"]) |
144 | | except TypeError : # Not found |
145 | | return |
146 | | |
147 | | def setPrinterPrices(self, printerid, perpage, perjob) : |
148 | | """Sets prices per job and per page for a given printer.""" |
149 | | self.doQuery("UPDATE printers SET priceperpage=%s, priceperjob=%s WHERE id=%s" % (self.doQuote(perpage), self.doQuote(perjob), self.doQuote(printerid))) |
150 | | |
151 | | def getUserId(self, username) : |
152 | | """Returns a userid given a username.""" |
153 | | result = self.doQuery("SELECT id FROM users WHERE username=%s" % self.doQuote(username)) |
154 | | try : |
155 | | return self.doParseResult(result)[0]["id"] |
156 | | except TypeError : # Not found |
157 | | return |
158 | | |
159 | | def getGroupId(self, groupname) : |
160 | | """Returns a groupid given a grupname.""" |
161 | | result = self.doQuery("SELECT id FROM groups WHERE groupname=%s" % self.doQuote(groupname)) |
162 | | try : |
163 | | return self.doParseResult(result)[0]["id"] |
164 | | except TypeError : # Not found |
165 | | return |
166 | | |
167 | | def getJobHistoryId(self, jobid, userid, printerid) : |
168 | | """Returns the history line's id given a (jobid, userid, printerid).""" |
169 | | result = self.doQuery("SELECT id FROM jobhistory WHERE jobid=%s AND userid=%s AND printerid=%s" % (self.doQuote(jobid), self.doQuote(userid), self.doQuote(printerid))) |
170 | | try : |
171 | | return self.doParseResult(result)[0]["id"] |
172 | | except TypeError : # Not found |
173 | | return |
174 | | |
175 | | def getPrinterUsers(self, printerid) : |
176 | | """Returns the list of userids and usernames which uses a given printer.""" |
177 | | result = self.doQuery("SELECT DISTINCT id, username FROM users WHERE id IN (SELECT userid FROM userpquota WHERE printerid=%s) ORDER BY username" % self.doQuote(printerid)) |
178 | | result = self.doParseResult(result) |
179 | | if result is None : |
180 | | return [] |
181 | | else : |
182 | | return [(record["id"], record["username"]) for record in result] |
183 | | |
184 | | def getPrinterGroups(self, printerid) : |
185 | | """Returns the list of groups which uses a given printer.""" |
186 | | result = self.doQuery("SELECT DISTINCT id, groupname FROM groups WHERE id IN (SELECT groupid FROM grouppquota WHERE printerid=%s)" % self.doQuote(printerid)) |
187 | | result = self.doParseResult(result) |
188 | | if result is None : |
189 | | return [] |
190 | | else : |
191 | | return [(record["id"], record["groupname"]) for record in result] |
192 | | |
193 | | def getGroupMembersNames(self, groupname) : |
194 | | """Returns the list of user's names which are member of this group.""" |
195 | | groupid = self.getGroupId(groupname) |
196 | | if groupid is None : |
197 | | return [] |
198 | | else : |
199 | | result = self.doQuery("SELECT DISTINCT username FROM users WHERE id IN (SELECT userid FROM groupsmembers WHERE groupid=%s)" % self.doQuote(groupid)) |
200 | | return [record["username"] for record in (self.doParseResult(result) or [])] |
201 | | |
202 | | def getUserGroupsNames(self, userid) : |
203 | | """Returns the list of groups' names the user is a member of.""" |
204 | | result = self.doQuery("SELECT DISTINCT groupname FROM groups WHERE id IN (SELECT groupid FROM groupsmembers WHERE userid=%s)" % self.doQuote(userid)) |
205 | | return [record["groupname"] for record in (self.doParseResult(result) or [])] |
| 256 | result = self.doSearch("SELECT * FROM printers") |
| 257 | if result : |
| 258 | for record in result : |
| 259 | if self.tool.matchString(record["printername"], [ printerpattern ]) : |
| 260 | printer = StoragePrinter(self, record["printername"]) |
| 261 | printer.ident = record.get("id") |
| 262 | printer.PricePerJob = record.get("priceperjob") |
| 263 | printer.PricePerPage = record.get("priceperpage") |
| 264 | printer.LastJob = self.getPrinterLastJob(printer) |
| 265 | printer.Exists = 1 |
| 266 | printers.append(printer) |
| 267 | return printers |
| 268 | |
| 269 | def getPrinterUsersAndQuotas(self, printer, names=None) : |
| 270 | """Returns the list of users who uses a given printer, along with their quotas.""" |
| 271 | usersandquotas = [] |
| 272 | result = self.doSearch("SELECT users.id as uid,username,balance,lifetimepaid,limitby,userpquota.id,lifepagecounter,pagecounter,softlimit,hardlimit,datelimit FROM users JOIN userpquota ON users.id=userpquota.userid AND printerid=%s" % self.doQuote(printer.ident)) |
| 273 | if result : |
| 274 | for record in result : |
| 275 | user = StorageUser(self, record.get("username")) |
| 276 | if (names is None) or self.tool.matchString(user.Name, names) : |
| 277 | user.ident = record.get("uid") |
| 278 | user.LimitBy = record.get("limitby") |
| 279 | user.AccountBalance = record.get("balance") |
| 280 | user.LifeTimePaid = record.get("lifetimepaid") |
| 281 | user.Exists = 1 |
| 282 | userpquota = StorageUserPQuota(self, user, printer) |
| 283 | userpquota.ident = record.get("id") |
| 284 | userpquota.PageCounter = record.get("pagecounter") |
| 285 | userpquota.LifePageCounter = record.get("lifepagecounter") |
| 286 | userpquota.SoftLimit = record.get("softlimit") |
| 287 | userpquota.HardLimit = record.get("hardlimit") |
| 288 | userpquota.DateLimit = record.get("datelimit") |
| 289 | userpquota.Exists = 1 |
| 290 | usersandquotas.append((user, userpquota)) |
| 291 | return usersandquotas |
| 292 | |
| 293 | def getPrinterGroupsAndQuotas(self, printer, names=None) : |
| 294 | """Returns the list of groups which uses a given printer, along with their quotas.""" |
| 295 | groupsandquotas = [] |
| 296 | result = self.doSearch("SELECT groupname FROM groups JOIN grouppquota ON groups.id=grouppquota.groupid AND printerid=%s" % self.doQuote(printer.ident)) |
| 297 | if result : |
| 298 | for record in result : |
| 299 | group = self.getGroup(record.get("groupname")) |
| 300 | if (names is None) or self.tool.matchString(group.Name, names) : |
| 301 | grouppquota = self.getGroupPQuota(group, printer) |
| 302 | groupsandquotas.append((group, grouppquota)) |
| 303 | return groupsandquotas |
219 | | self.doQuery("INSERT INTO groups (groupname) VALUES (%s)" % self.doQuote(groupname)) |
220 | | return self.getGroupId(groupname) |
221 | | |
222 | | def addUserPQuota(self, username, printerid) : |
223 | | """Initializes a user print quota on a printer, adds the user to the quota storage if needed.""" |
224 | | userid = self.getUserId(username) |
225 | | if userid is None : |
226 | | userid = self.addUser(username) |
227 | | uqexists = (self.getUserPQuota(userid, printerid) is not None) |
228 | | if not uqexists : |
229 | | self.doQuery("INSERT INTO userpquota (userid, printerid) VALUES (%s, %s)" % (self.doQuote(userid), self.doQuote(printerid))) |
230 | | return (userid, printerid) |
231 | | |
232 | | def addGroupPQuota(self, groupname, printerid) : |
233 | | """Initializes a group print quota on a printer, adds the group to the quota storage if needed.""" |
234 | | groupid = self.getGroupId(groupname) |
235 | | if groupid is None : |
236 | | groupid = self.addGroup(groupname) |
237 | | gqexists = (self.getGroupPQuota(groupid, printerid) is not None) |
238 | | if not gqexists : |
239 | | self.doQuery("INSERT INTO grouppquota (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(groupid), self.doQuote(printerid))) |
240 | | return (groupid, printerid) |
241 | | |
242 | | def increaseUserBalance(self, userid, amount) : |
243 | | """Increases (or decreases) an user's account balance by a given amount.""" |
244 | | self.doQuery("UPDATE users SET balance=balance+(%s), lifetimepaid=lifetimepaid+(%s) WHERE id=%s" % (self.doQuote(amount), self.doQuote(amount), self.doQuote(userid))) |
245 | | |
246 | | def getUserBalance(self, userid) : |
247 | | """Returns the current account balance for a given user.""" |
248 | | result = self.doQuery("SELECT balance, lifetimepaid FROM users WHERE id=%s" % self.doQuote(userid)) |
249 | | try : |
250 | | result = self.doParseResult(result)[0] |
251 | | except TypeError : # Not found |
252 | | return |
253 | | else : |
254 | | return (result["balance"], result["lifetimepaid"]) |
255 | | |
256 | | def getGroupBalance(self, groupid) : |
257 | | """Returns the current account balance for a given group, as the sum of each of its users' account balance.""" |
258 | | result = self.doQuery("SELECT SUM(balance) AS balance, SUM(lifetimepaid) AS lifetimepaid FROM users WHERE id in (SELECT userid FROM groupsmembers WHERE groupid=%s)" % self.doQuote(groupid)) |
259 | | try : |
260 | | result = self.doParseResult(result)[0] |
261 | | except TypeError : # Not found |
262 | | return |
263 | | else : |
264 | | return (result["balance"], result["lifetimepaid"]) |
265 | | |
266 | | def getUserLimitBy(self, userid) : |
267 | | """Returns the way in which user printing is limited.""" |
268 | | result = self.doQuery("SELECT limitby FROM users WHERE id=%s" % self.doQuote(userid)) |
269 | | try : |
270 | | return self.doParseResult(result)[0]["limitby"] |
271 | | except TypeError : # Not found |
272 | | return |
273 | | |
274 | | def getGroupLimitBy(self, groupid) : |
275 | | """Returns the way in which group printing is limited.""" |
276 | | result = self.doQuery("SELECT limitby FROM groups WHERE id=%s" % self.doQuote(groupid)) |
277 | | try : |
278 | | return self.doParseResult(result)[0]["limitby"] |
279 | | except TypeError : # Not found |
280 | | return |
281 | | |
282 | | def setUserBalance(self, userid, balance) : |
283 | | """Sets the account balance for a given user to a fixed value.""" |
284 | | (current, lifetimepaid) = self.getUserBalance(userid) |
285 | | difference = balance - current |
286 | | self.increaseUserBalance(userid, difference) |
287 | | |
288 | | def limitUserBy(self, userid, limitby) : |
289 | | """Limits a given user based either on print quota or on account balance.""" |
290 | | self.doQuery("UPDATE users SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(userid))) |
291 | | |
292 | | def limitGroupBy(self, groupid, limitby) : |
293 | | """Limits a given group based either on print quota or on sum of its users' account balances.""" |
294 | | self.doQuery("UPDATE groups SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(groupid))) |
295 | | |
296 | | def setUserPQuota(self, userid, printerid, softlimit, hardlimit) : |
297 | | """Sets soft and hard limits for a user quota on a specific printer given (userid, printerid).""" |
298 | | self.doQuery("UPDATE userpquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE userid=%s AND printerid=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(userid), self.doQuote(printerid))) |
299 | | |
300 | | def setGroupPQuota(self, groupid, printerid, softlimit, hardlimit) : |
301 | | """Sets soft and hard limits for a group quota on a specific printer given (groupid, printerid).""" |
302 | | self.doQuery("UPDATE grouppquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE groupid=%s AND printerid=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(groupid), self.doQuote(printerid))) |
303 | | |
304 | | def resetUserPQuota(self, userid, printerid) : |
305 | | """Resets the page counter to zero for a user on a printer. Life time page counter is kept unchanged.""" |
306 | | self.doQuery("UPDATE userpquota SET pagecounter=0, datelimit=NULL WHERE userid=%s AND printerid=%s" % (self.doQuote(userid), self.doQuote(printerid))) |
307 | | |
308 | | def resetGroupPQuota(self, groupid, printerid) : |
309 | | """Resets the page counter to zero for a group on a printer. Life time page counter is kept unchanged.""" |
310 | | self.doQuery("UPDATE grouppquota SET pagecounter=0, datelimit=NULL WHERE groupid=%s AND printerid=%s" % (self.doQuote(groupid), self.doQuote(printerid))) |
311 | | |
312 | | def updateUserPQuota(self, userid, printerid, pagecount) : |
313 | | """Updates the used user Quota information given (userid, printerid) and a job size in pages.""" |
314 | | jobprice = self.computePrinterJobPrice(printerid, pagecount) |
315 | | queries = [] |
316 | | queries.append("UPDATE userpquota SET lifepagecounter=lifepagecounter+(%s), pagecounter=pagecounter+(%s) WHERE userid=%s AND printerid=%s" % (self.doQuote(pagecount), self.doQuote(pagecount), self.doQuote(userid), self.doQuote(printerid))) |
317 | | queries.append("UPDATE users SET balance=balance-(%s) WHERE id=%s" % (self.doQuote(jobprice), self.doQuote(userid))) |
318 | | self.doQuery(queries) |
319 | | |
320 | | def getUserPQuota(self, userid, printerid) : |
321 | | """Returns the Print Quota information for a given (userid, printerid).""" |
322 | | result = self.doQuery("SELECT lifepagecounter, pagecounter, softlimit, hardlimit, datelimit FROM userpquota WHERE userid=%s AND printerid=%s" % (self.doQuote(userid), self.doQuote(printerid))) |
323 | | try : |
324 | | return self.doParseResult(result)[0] |
325 | | except TypeError : # Not found |
326 | | return |
327 | | |
328 | | def getGroupPQuota(self, groupid, printerid) : |
329 | | """Returns the Print Quota information for a given (groupid, printerid).""" |
330 | | result = self.doQuery("SELECT softlimit, hardlimit, datelimit FROM grouppquota WHERE groupid=%s AND printerid=%s" % (self.doQuote(groupid), self.doQuote(printerid))) |
331 | | try : |
332 | | grouppquota = self.doParseResult(result)[0] |
333 | | except TypeError : |
334 | | return |
335 | | else : |
336 | | result = self.doQuery("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(printerid), self.doQuote(groupid))) |
337 | | try : |
338 | | result = self.doParseResult(result)[0] |
339 | | except TypeError : # Not found |
340 | | return |
341 | | else : |
342 | | grouppquota.update({"lifepagecounter": result["lifepagecounter"], "pagecounter": result["pagecounter"]}) |
343 | | return grouppquota |
344 | | |
345 | | def setUserDateLimit(self, userid, printerid, datelimit) : |
346 | | """Sets the limit date for a soft limit to become an hard one given (userid, printerid).""" |
347 | | self.doQuery("UPDATE userpquota SET datelimit=%s::TIMESTAMP WHERE userid=%s AND printerid=%s" % (self.doQuote("%04i-%02i-%02i %02i:%02i:%02i" % (datelimit.year, datelimit.month, datelimit.day, datelimit.hour, datelimit.minute, datelimit.second)), self.doQuote(userid), self.doQuote(printerid))) |
348 | | |
349 | | def setGroupDateLimit(self, groupid, printerid, datelimit) : |
350 | | """Sets the limit date for a soft limit to become an hard one given (groupid, printerid).""" |
351 | | self.doQuery("UPDATE grouppquota SET datelimit=%s::TIMESTAMP WHERE groupid=%s AND printerid=%s" % (self.doQuote("%04i-%02i-%02i %02i:%02i:%02i" % (datelimit.year, datelimit.month, datelimit.day, datelimit.hour, datelimit.minute, datelimit.second)), self.doQuote(groupid), self.doQuote(printerid))) |
352 | | |
353 | | def addJobToHistory(self, jobid, userid, printerid, pagecounter, action, jobsize=None) : |
354 | | """Adds a job to the history: (jobid, userid, printerid, last page counter taken from requester).""" |
355 | | self.doQuery("INSERT INTO jobhistory (jobid, userid, printerid, pagecounter, action, jobsize) VALUES (%s, %s, %s, %s, %s, %s)" % (self.doQuote(jobid), self.doQuote(userid), self.doQuote(printerid), self.doQuote(pagecounter), self.doQuote(action), self.doQuote(jobsize))) |
356 | | return self.getJobHistoryId(jobid, userid, printerid) # in case jobid is not sufficient |
357 | | |
358 | | def updateJobSizeInHistory(self, historyid, jobsize) : |
359 | | """Updates a job size in the history given the history line's id.""" |
360 | | self.doQuery("UPDATE jobhistory SET jobsize=%s WHERE id=%s" % (self.doQuote(jobsize), self.doQuote(historyid))) |
361 | | |
362 | | def getPrinterPageCounter(self, printerid) : |
363 | | """Returns the last page counter value for a printer given its id, also returns last username, last jobid and history line id.""" |
364 | | result = self.doQuery("SELECT jobhistory.id, jobid, userid, username, pagecounter, jobsize FROM jobhistory, users WHERE printerid=%s AND userid=users.id ORDER BY jobdate DESC LIMIT 1" % self.doQuote(printerid)) |
365 | | try : |
366 | | return self.doParseResult(result)[0] |
367 | | except TypeError : # Not found |
368 | | return |
369 | | |
370 | | def addUserToGroup(self, userid, groupid) : |
| 317 | self.doModify("INSERT INTO groups (groupname, limitby) VALUES (%s, %s)" % (self.doQuote(group.Name), self.doQuote(group.LimitBy))) |
| 318 | return self.getGroup(group.Name) |
| 319 | |
| 320 | def addUserToGroup(self, user, group) : |
378 | | self.doQuery("INSERT INTO groupsmembers (groupid, userid) VALUES (%s, %s)" % (self.doQuote(groupid), self.doQuote(userid))) |
379 | | |
380 | | def deleteUser(self, userid) : |
| 328 | self.doModify("INSERT INTO groupsmembers (groupid, userid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(user.ident))) |
| 329 | |
| 330 | def addUserPQuota(self, user, printer) : |
| 331 | """Initializes a user print quota on a printer.""" |
| 332 | self.doModify("INSERT INTO userpquota (userid, printerid) VALUES (%s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident))) |
| 333 | return self.getUserPQuota(user, printer) |
| 334 | |
| 335 | def addGroupPQuota(self, group, printer) : |
| 336 | """Initializes a group print quota on a printer.""" |
| 337 | self.doModify("INSERT INTO grouppquota (groupid, printerid) VALUES (%s, %s)" % (self.doQuote(group.ident), self.doQuote(printer.ident))) |
| 338 | return self.getGroupPQuota(group, printer) |
| 339 | |
| 340 | def writePrinterPrices(self, printer) : |
| 341 | """Write the printer's prices back into the storage.""" |
| 342 | self.doModify("UPDATE printers SET priceperpage=%s, priceperjob=%s WHERE printerid=%s" % (self.doQuote(printer.PricePerPage), self.doQuote(printer.PricePerJob), self.doQuote(printer.ident))) |
| 343 | |
| 344 | def writeUserLimitBy(self, user, limitby) : |
| 345 | """Sets the user's limiting factor.""" |
| 346 | self.doModify("UPDATE users SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(user.ident))) |
| 347 | |
| 348 | def writeGroupLimitBy(self, group, limitby) : |
| 349 | """Sets the group's limiting factor.""" |
| 350 | self.doModify("UPDATE groups SET limitby=%s WHERE id=%s" % (self.doQuote(limitby), self.doQuote(group.ident))) |
| 351 | |
| 352 | def writeUserPQuotaDateLimit(self, userpquota, datelimit) : |
| 353 | """Sets the date limit permanently for a user print quota.""" |
| 354 | self.doModify("UPDATE userpquota SET datelimit::TIMESTAMP=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(userpquota.ident))) |
| 355 | |
| 356 | def writeGroupPQuotaDateLimit(self, grouppquota, datelimit) : |
| 357 | """Sets the date limit permanently for a group print quota.""" |
| 358 | self.doModify("UPDATE grouppquota SET datelimit::TIMESTAMP=%s WHERE id=%s" % (self.doQuote(datelimit), self.doQuote(grouppquota.ident))) |
| 359 | |
| 360 | def writeUserPQuotaPagesCounters(self, userpquota, newpagecounter, newlifepagecounter) : |
| 361 | """Sets the new page counters permanently for a user print quota.""" |
| 362 | self.doModify("UPDATE userpquota SET pagecounter=%s,lifepagecounter=%s WHERE id=%s" % (self.doQuote(newpagecounter), self.doQuote(newlifepagecounter), self.doQuote(userpquota.ident))) |
| 363 | |
| 364 | def writeUserAccountBalance(self, user, newbalance, newlifetimepaid=None) : |
| 365 | """Sets the new account balance and eventually new lifetime paid.""" |
| 366 | if newlifetimepaid is not None : |
| 367 | self.doModify("UPDATE users SET balance=%s, lifetimepaid=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(newlifetimepaid), self.doQuote(user.ident))) |
| 368 | else : |
| 369 | self.doModify("UPDATE users SET balance=%s WHERE id=%s" % (self.doQuote(newbalance), self.doQuote(user.ident))) |
| 370 | |
| 371 | def writeLastJobSize(self, lastjob, jobsize) : |
| 372 | """Sets the last job's size permanently.""" |
| 373 | self.doModify("UPDATE jobhistory SET jobsize=%s WHERE id=%s" % (self.doQuote(jobsize), self.doQuote(lastjob.ident))) |
| 374 | |
| 375 | def writeJobNew(self, printer, user, jobid, pagecounter, action, jobsize=None) : |
| 376 | """Adds a job in a printer's history.""" |
| 377 | if jobsize is not None : |
| 378 | self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action, jobsize) VALUES (%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))) |
| 379 | else : |
| 380 | self.doModify("INSERT INTO jobhistory (userid, printerid, jobid, pagecounter, action) VALUES (%s, %s, %s, %s, %s)" % (self.doQuote(user.ident), self.doQuote(printer.ident), self.doQuote(jobid), self.doQuote(pagecounter), self.doQuote(action))) |
| 381 | |
| 382 | def writeUserPQuotaLimits(self, userpquota, softlimit, hardlimit) : |
| 383 | """Sets soft and hard limits for a user quota.""" |
| 384 | self.doModify("UPDATE userpquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(userpquota.ident))) |
| 385 | |
| 386 | def writeGroupPQuotaLimits(self, grouppquota, softlimit, hardlimit) : |
| 387 | """Sets soft and hard limits for a group quota on a specific printer given (groupid, printerid).""" |
| 388 | self.doModify("UPDATE grouppquota SET softlimit=%s, hardlimit=%s, datelimit=NULL WHERE id=%s" % (self.doQuote(softlimit), self.doQuote(hardlimit), self.doQuote(grouppquota.ident))) |
| 389 | |
| 390 | def deleteUser(self, user) : |