ILIAS  Release_4_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilDiskQuotaChecker.php
Go to the documentation of this file.
1 <?php
2 /*
3  +-----------------------------------------------------------------------------+
4  | ILIAS open source |
5  +-----------------------------------------------------------------------------+
6  | Copyright (c) 1998-2001 ILIAS open source, University of Cologne |
7  | |
8  | This program is free software; you can redistribute it and/or |
9  | modify it under the terms of the GNU General Public License |
10  | as published by the Free Software Foundation; either version 2 |
11  | of the License, or (at your option) any later version. |
12  | |
13  | This program is distributed in the hope that it will be useful, |
14  | but WITHOUT ANY WARRANTY; without even the implied warranty of |
15  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
16  | GNU General Public License for more details. |
17  | |
18  | You should have received a copy of the GNU General Public License |
19  | along with this program; if not, write to the Free Software |
20  | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. |
21  +-----------------------------------------------------------------------------+
22 */
23 
33 class ilDiskQuotaChecker
34 {
35  function ilDiskQuotaChecker()
36  {
37  }
38 
70  public static function _lookupDiskQuota($a_user_id)
71  {
72  $info = array();
73 
74  global $ilDB;
75 
76  $res = $ilDB->queryf("SELECT keyword, value ".
77  "FROM usr_pref ".
78  "WHERE usr_id = %s ".
79  "AND keyword IN ('disk_quota', 'disk_quota_last_reminder')",
80  array('integer'),
81  array($a_user_id));
82 
83  while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
84  switch ($row->keyword)
85  {
86  case 'disk_quota' :
87  $info['user_disk_quota'] = $row->value;
88  break;
89  case 'disk_quota_last_reminder' :
90  $info['last_reminder'] = $row->value;
91  break;
92  }
93  }
94 
95 
96  // Note: we order by role_id ASC here, in the assumption that
97  // the system role has the lowest ID of all roles.
98  // this way, if a user has the system role, this role
99  // will always returned first.
100  $ilDB->setLimit(1);
101  $res = $ilDB->queryf("SELECT rd.role_id, rd.disk_quota, od.title ".
102  "FROM rbac_ua ua ".
103  "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent = %s ".
104  "JOIN role_data rd ON ua.rol_id=rd.role_id ".
105  "JOIN object_data od ON od.obj_id=rd.role_id ".
106  "WHERE ua.usr_id = %s ".
107  "ORDER BY disk_quota DESC, role_id ASC",
108  array('integer','integer'),
109  array(ROLE_FOLDER_ID, $a_user_id));
110 
111  $row = $res->fetchRow(DB_FETCHMODE_OBJECT);
112  $info['role_id'] = $row->role_id;
113  $info['role_title'] = $row->title;
114 
115  // Note: Users with the system role have an infinite disk quota
116  // We calculate positive infinity by negating the logarithm of 0.
117  $info['role_disk_quota'] = ($row->role_id == SYSTEM_ROLE_ID) ? -log(0) : $row->disk_quota;
118  $info['disk_quota'] = max($info['user_disk_quota'], $info['role_disk_quota']);
119 
120  return $info;
121  }
122 
140  public static function _lookupDiskUsage($a_user_id)
141  {
142  $info = array();
143  $details = array();
144 
145  global $ilDB;
146 
147  $res = $ilDB->query("SELECT keyword, value ".
148  "FROM usr_pref ".
149  "WHERE usr_id = ".$ilDB->quote($a_user_id, 'integer')." ".
150  "AND ".$ilDB->like("keyword", "text", 'disk\\_usage%')
151  );
152 
153  while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
154  switch ($row->keyword)
155  {
156  case 'disk_usage.last_update' :
157  $info['last_update'] = $row->value;
158  break;
159 
160  case 'disk_usage' :
161  $info['disk_usage'] = $row->value;
162  break;
163 
164  default :
165  // The following preg_match is used to extract the type
166  // and the keys 'count' and 'size' from the keywords:
167  // disk_usage.type.count
168  // disk_usage.type.size
169  $matches = array();
170  preg_match('/^disk_usage\\.([^.]+)\\.([^.]+)/', $row->keyword, $matches);
171  $type = $matches[1];
172  $key = $matches[2];
173  if ($type)
174  {
175  $detail_data = $details[$type];
176  if ($detail_data == null)
177  {
178  $detail_data = array('type'=>$type);
179  }
180  $detail_data[$key] = $row->value;
181  }
182  $details[$type] = $detail_data;
183  break;
184  }
185  }
186  $info['details'] = $details;
187 
188 
189  //ilDiskQuotaChecker::_updateDiskUsageReport();
190 
191  return $info;
192  }
193 
219  public static function _fetchDiskQuotaReport($a_usage_filter = 3, $a_access_filter = 1, $a_order_column='disk_usage',$a_order_by='desc')
220  {
221  $data = array();
222  global $ilDB;
223 
224  if (! $a_order_column) {
225  $a_order_column = 'disk_usage';
226  }
227 
228  switch ($a_usage_filter) {
229  case 1: // all users
230  $where_clause = '';
231  break;
232  case 2: // only users who don't use disk space
233  $where_clause = 'WHERE (p2.value IS NULL) ';
234  break;
235  case 3: // only users who use disk space
236  default:
237  $where_clause = 'WHERE (p2.value > 0) ';
238  break;
239  case 4: // only users who have exceeded their disk quota
240  $where_clause = 'WHERE (p2.value > p1.value AND p2.value > rq.role_disk_quota) ';
241  break;
242  }
243  switch ($a_access_filter) {
244  case 1: // all users
245  $where_clause .= '';
246  break;
247  case 2: // only users who have access
248  default:
249  $where_clause .= ($where_clause == '' ? 'WHERE ' : ' AND ' ).
250  '(u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().' BETWEEN u.time_limit_from AND u.time_limit_until)) ';
251  break;
252  case 3: // only users who don't have access
253  $where_clause .= ($where_clause == '' ? 'WHERE ' : ' AND ' ).
254  '(u.active=0 OR (u.time_limit_unlimited IS NULL AND '.$ilDB->unixTimestamp().' NOT BETWEEN u.time_limit_from AND u.time_limit_until)) ';
255  break;
256  }
257 
258  $res = $ilDB->queryf(
259  "SELECT u.usr_id,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
260  "u.time_limit_unlimited, ".$ilDB->fromUnixtime("u.time_limit_from").", ".$ilDB->fromUnixtime("u.time_limit_until").",".
261 
262  // Inactive users get the date 0001-01-01 so that they appear
263  // first when the list is sorted by this field. Users with
264  // unlimited access get the date 9999-12-31 so that they appear
265  // last.
266  "CASE WHEN u.active = 0 THEN '0001-01-01' ELSE CASE WHEN u.time_limit_unlimited=1 THEN '9999-12-31' ELSE ".$ilDB->fromUnixtime("u.time_limit_until")." END END access_until,".
267 
268  "CASE WHEN ".$ilDB->unixTimestamp()." BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
269  "rq.role_disk_quota, system_role.rol_id role_id, ".
270  "p1.value+0 user_disk_quota,".
271  "p2.value+0 disk_usage, ".
272  "p3.value last_update, ".
273  "p4.value last_reminder, ".
274 
275  // We add 0 to some of the values to convert them into a number.
276  // This is needed for correct sorting.
277  "CASE WHEN rq.role_disk_quota+0>p1.value+0 OR p1.value IS NULL THEN rq.role_disk_quota+0 ELSE p1.value+0 END disk_quota ".
278  "FROM usr_data u ".
279 
280  // Fetch the role with the highest disk quota value.
281  "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
282  "FROM usr_data u ".
283  "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
284  "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s ".
285  "JOIN role_data rd ON rd.role_id=ua.rol_id WHERE u.usr_id=ua.usr_id GROUP BY u.usr_id) rq ON rq.usr_id=u.usr_id ".
286 
287  // Fetch the system role in order to determine whether the user has unlimited disk quota
288  "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".
289 
290  // Fetch the user disk quota from table usr_pref
291  "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota' ".
292 
293  // Fetch the disk usage from table usr_pref
294  "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage' ".
295 
296  // Fetch the last update from table usr_pref
297  "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update' ".
298 
299  // Fetch the date when the last disk quota reminder was sent from table usr_pref
300  "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder' ".
301 
302  $where_clause.
303  "ORDER BY ".$a_order_column." ".($a_order_by=='asc'?' ASC':' DESC').", ".
304  "lastname, firstname, login"
305  ,
306  array('integer','integer'),
307  array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
308  );
309  $previous_usr_id = null;
310  while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
311  if ($previous_usr_id != $row['usr_id'])
312  {
313  $data[] = $row;
314  $previous_usr_id = $row['usr_id'];
315  }
316  }
317  return $data;
318  }
319 
330  public static function _updateDiskUsageReport()
331  {
332  global $ilDB;
333 
334  // delete old values
335  $ilDB->manipulate("DELETE FROM usr_pref ".
336  "WHERE ".$ilDB->like("keyword", "text", 'disk_usage%'));
337 
338 
339  require_once 'Modules/File/classes/class.ilObjFileAccess.php';
340  self::__updateDiskUsageReportOfType(new ilObjFileAccess(), 'file');
341 
342  require_once 'Modules/Forum/classes/class.ilObjForumAccess.php';
343  self::__updateDiskUsageReportOfType(new ilObjForumAccess(), 'frm');
344 
345  require_once 'Modules/HTMLLearningModule/classes/class.ilObjFileBasedLMAccess.php';
346  self::__updateDiskUsageReportOfType(new ilObjFileBasedLMAccess(), 'htlm');
347 
348  require_once 'Modules/MediaCast/classes/class.ilObjMediaCastAccess.php';
349  self::__updateDiskUsageReportOfType(new ilObjMediaCastAccess(), 'mcst');
350 
351  require_once 'Modules/ScormAicc/classes/class.ilObjSAHSLearningModuleAccess.php';
352  self::__updateDiskUsageReportOfType(new ilObjSAHSLearningModuleAccess(), 'sahs');
353 
354  require_once 'Services/Mail/classes/class.ilObjMailAccess.php';
355  self::__updateDiskUsageReportOfUsers(new ilObjMailAccess(), 'mail_attachment');
356 
357  // insert the sum of the disk usage of each user
358  // note: second % is needed to not fail on oracle char field
359  $ilDB->manipulate("INSERT INTO usr_pref ".
360  "(usr_id, keyword, value) ".
361  "SELECT usr_id, 'disk_usage', SUM(value) ".
362  "FROM usr_pref ".
363  "WHERE ".$ilDB->like("keyword", "text", 'disk_usage.%.size%').
364  "GROUP BY usr_id"
365  );
366 
367  // insert last update
368  $ilDB->manipulate("INSERT INTO usr_pref ".
369  "(usr_id, keyword, value) ".
370  "SELECT usr_id, 'disk_usage.last_update', ".$ilDB->now()." ".
371  "FROM usr_data");
372  }
373 
387  private static function __updateDiskUsageReportOfType($a_access_obj, $a_type)
388  {
389  global $ilDB;
390 
391  // get all objects of the desired type which are in the repository
392  // ordered by owner
393  $res = $ilDB->query("SELECT DISTINCT d.obj_id, d.owner ".
394  "FROM object_data d ".
395  "JOIN object_reference r ON d.obj_id=r.obj_id ".
396  "JOIN tree t ON t.child=r.ref_id ".
397  "WHERE d.type = ".$ilDB->quote($a_type, "text")." ".
398  "AND t.tree=1 ".
399  "ORDER BY d.owner"
400  );
401 
402  // for each objects of an owner, count the number of objects and sum up
403  // the size
404  $count = null;
405  $size = null;
406  $owner = null;
407  while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
408  if ($row->owner != $owner) {
409  if ($owner != null) {
410  $ilDB->manipulate("INSERT INTO usr_pref ".
411  "(usr_id, keyword, value) ".
412  "VALUES ".
413  "(".$ilDB->quote($owner,'integer').", ".
414  $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
415  $ilDB->quote($size, 'integer').")");
416 
417  $ilDB->manipulate("INSERT INTO usr_pref ".
418  "(usr_id, keyword, value) ".
419  "VALUES ".
420  "(".$ilDB->quote($owner,'integer').", ".
421  $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
422  $ilDB->quote($count, 'integer').")"
423  );
424 
425  }
426  $owner = $row->owner;
427  $size = 0;
428  $count = 0;
429  }
430  $size += $a_access_obj->_lookupDiskUsage($row->obj_id);
431  $count++;
432  }
433  if ($owner != null) {
434  $ilDB->manipulate("INSERT INTO usr_pref ".
435  "(usr_id, keyword, value) ".
436  "VALUES ".
437  "(".$ilDB->quote($owner,'integer').", ".
438  $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
439  $ilDB->quote($size, 'integer').")");
440 
441  $ilDB->manipulate("INSERT INTO usr_pref ".
442  "(usr_id, keyword, value) ".
443  "VALUES ".
444  "(".$ilDB->quote($owner,'integer').", ".
445  $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
446  $ilDB->quote($count, 'integer').")");
447  }
448 
449  }
450 
464  private static function __updateDiskUsageReportOfUsers($a_access_obj, $a_type)
465  {
466  global $ilDB;
467 
468  // get all users
469  $res = $ilDB->query("SELECT usr_id FROM usr_data");
470 
471  // for each user count the number of objects and sum up the size
472  while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
473  $data = $a_access_obj->_lookupDiskUsageOfUser($row->usr_id);
474 
475  if ($data['size'] != null && $data['count'] != null)
476  {
477  $ilDB->manipulate("INSERT INTO usr_pref ".
478  "(usr_id, keyword, value) ".
479  "VALUES ".
480  "(".$ilDB->quote($row->usr_id,'integer').", ".
481  $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
482  $ilDB->quote($data['size'], 'integer').")");
483  $ilDB->manipulate("INSERT INTO usr_pref ".
484  "(usr_id, keyword, value) ".
485  "VALUES ".
486  "(".$ilDB->quote($row->usr_id,'integer').", ".
487  $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
488  $ilDB->quote($data['count'], 'integer').")");
489  }
490  }
491  }
497  public static function _sendReminderMails()
498  {
499  global $ilDB;
500 
501  require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
502  $mail = new ilDiskQuotaReminderMail();
503 
504  $res = $ilDB->queryf(
505  "SELECT u.usr_id,u.gender,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
506  "u.time_limit_unlimited, ".$ilDB->fromUnixtime("u.time_limit_from").", ".$ilDB->fromUnixtime("u.time_limit_until").",".
507 
508  // Inactive users get the date 0001-01-01 so that they appear
509  // first when the list is sorted by this field. Users with
510  // unlimited access get the date 9999-12-31 so that they appear
511  // last.
512  "CASE WHEN u.active = 0 THEN '0001-01-01' ELSE CASE WHEN u.time_limit_unlimited=1 THEN '9999-12-31' ELSE ".$ilDB->fromUnixtime("u.time_limit_until")." END END access_until,".
513 
514  " CASE WHEN ".$ilDB->unixTimestamp()." BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
515  "rq.role_disk_quota, system_role.rol_id role_id, ".
516  "p1.value+0 user_disk_quota,".
517  "p2.value+0 disk_usage, ".
518  "p3.value last_update, ".
519  "p4.value last_reminder, ".
520  "p5.value language, ".
521 
522  // We add 0 to some of the values to convert them into a number.
523  // This is needed for correct sorting.
524  "CASE WHEN rq.role_disk_quota+0>p1.value+0 OR p1.value IS NULL THEN rq.role_disk_quota+0 ELSE p1.value+0 END disk_quota ".
525  "FROM usr_data u ".
526 
527  // Fetch the role with the highest disk quota value.
528  "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
529  "FROM usr_data u ".
530  "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
531  "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s ".
532  "JOIN role_data rd ON rd.role_id=ua.rol_id WHERE u.usr_id=ua.usr_id GROUP BY u.usr_id) rq ON rq.usr_id=u.usr_id ".
533 
534  // Fetch the system role in order to determine whether the user has unlimited disk quota
535  "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".
536 
537  // Fetch the user disk quota from table usr_pref
538  "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota' ".
539 
540  // Fetch the disk usage from table usr_pref
541  "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage' ".
542 
543  // Fetch the last update from table usr_pref
544  "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update' ".
545 
546  // Fetch the date when the last disk quota reminder was sent from table usr_pref
547  "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder' ".
548 
549  // Fetch the language of the user
550  "LEFT JOIN usr_pref p5 ON p5.usr_id=u.usr_id AND p5.keyword = 'language' ".
551 
552  // Fetch only users who have exceeded their quota, and who have
553  // access, and who have not received a reminder in the past seven days
554  'WHERE (p2.value > p1.value AND p2.value > rq.role_disk_quota) '.
555  'AND (u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().' BETWEEN u.time_limit_from AND u.time_limit_until)) '.
556  'AND (p4.value IS NULL OR p4.value < DATE_SUB(NOW(), INTERVAL 7 DAY)) '
557 
558  ,
559  array('integer','integer'),
560  array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
561  );
562 
563  while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
564  $details = self::_lookupDiskUsage($row['usr_id']);
565  $row['disk_usage_details'] = $details['details'];
566 
567  // Send reminder e-mail
568  $mail->setData($row);
569  $mail->send();
570 
571  // Store the date the last reminder was sent in the table usr_pref.
572  if ($row['last_reminder'] != null)
573  {
574  $ilDB->manipulatef("UPDATE usr_pref SET value= ".$ilDB->now()." ".
575  "WHERE usr_id=%s AND keyword = 'disk_quota_last_reminder'"
576  ,
577  array('integer'),
578  array($row['usr_id'])
579  );
580  }
581  else
582  {
583  $ilDB->manipulatef("INSERT INTO usr_pref (usr_id, keyword, value) ".
584  "VALUES (%s, 'disk_quota_last_reminder', ".$ilDB->now().")"
585  ,
586  array('integer'),
587  array($row['usr_id'])
588  );
589  }
590  }
591  }
592 
597  public static function _lookupDiskUsageReportLastUpdate()
598  {
599  global $ilDB;
600 
601  require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
602  $mail = new ilDiskQuotaReminderMail();
603 
604  $res = $ilDB->query("SELECT MAX(value) last_update ".
605  "FROM usr_pref WHERE keyword='disk_usage.last_update'");
606  $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
607  return ($row != null) ? $row['last_update'] : null;
608  }
609 }
610 ?>