ILIAS  release_4-3 Revision
 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 > 0) ';
238  break;
239  case 4: // only users who have exceeded their disk quota
240  // #8554 / #10301
241  $where_clause = 'WHERE (((p1.value+0 > rq.role_disk_quota OR rq.role_disk_quota IS NULL) AND p2.value+0 > p1.value+0) OR
242  ((rq.role_disk_quota > p1.value+0 OR p1.value IS NULL) AND p2.value+0 > rq.role_disk_quota)) ';
243  break;
244  }
245  switch ($a_access_filter) {
246  case 1: // all users
247  $where_clause .= '';
248  break;
249  case 2: // only users who have access
250  default:
251  $where_clause .= ($where_clause == '' ? 'WHERE ' : ' AND ' ).
252  '(u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().' BETWEEN u.time_limit_from AND u.time_limit_until)) ';
253  break;
254  case 3: // only users who don't have access
255  $where_clause .= ($where_clause == '' ? 'WHERE ' : ' AND ' ).
256  '(u.active=0 OR (u.time_limit_unlimited IS NULL AND '.$ilDB->unixTimestamp().' NOT BETWEEN u.time_limit_from AND u.time_limit_until)) ';
257  break;
258  }
259 
260  $res = $ilDB->queryf(
261  "SELECT u.usr_id,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
262  "u.time_limit_unlimited, ".$ilDB->fromUnixtime("u.time_limit_from").", ".$ilDB->fromUnixtime("u.time_limit_until").",".
263 
264  // Inactive users get the date 0001-01-01 so that they appear
265  // first when the list is sorted by this field. Users with
266  // unlimited access get the date 9999-12-31 so that they appear
267  // last.
268  "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,".
269 
270  "CASE WHEN ".$ilDB->unixTimestamp()." BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
271  "rq.role_disk_quota, system_role.rol_id role_id, ".
272  "p1.value+0 user_disk_quota,".
273  "p2.value+0 disk_usage, ".
274  "p3.value last_update, ".
275  "p4.value last_reminder, ".
276 
277  // We add 0 to some of the values to convert them into a number.
278  // This is needed for correct sorting.
279  "CASE WHEN rq.role_disk_quota>p1.value+0 OR p1.value IS NULL THEN rq.role_disk_quota ELSE p1.value+0 END disk_quota ".
280  "FROM usr_data u ".
281 
282  // Fetch the role with the highest disk quota value.
283  "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
284  "FROM usr_data u ".
285  "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
286  "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s ".
287  "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 ".
288 
289  // Fetch the system role in order to determine whether the user has unlimited disk quota
290  "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".
291 
292  // Fetch the user disk quota from table usr_pref
293  "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota' ".
294 
295  // Fetch the disk usage from table usr_pref
296  "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage' ".
297 
298  // Fetch the last update from table usr_pref
299  "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update' ".
300 
301  // Fetch the date when the last disk quota reminder was sent from table usr_pref
302  "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder' ".
303 
304  $where_clause.
305  "ORDER BY ".$a_order_column." ".($a_order_by=='asc'?' ASC':' DESC').", ".
306  "lastname, firstname, login"
307  ,
308  array('integer','integer'),
309  array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
310  );
311  $previous_usr_id = null;
312  while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
313  if ($previous_usr_id != $row['usr_id'])
314  {
315  $data[] = $row;
316  $previous_usr_id = $row['usr_id'];
317  }
318  }
319  return $data;
320  }
321 
332  public static function _updateDiskUsageReport()
333  {
334  global $ilDB;
335 
336  // delete old values
337  $ilDB->manipulate("DELETE FROM usr_pref ".
338  "WHERE ".$ilDB->like("keyword", "text", 'disk_usage%'));
339 
340 
341  require_once 'Modules/File/classes/class.ilObjFileAccess.php';
342  self::__updateDiskUsageReportOfType(new ilObjFileAccess(), 'file');
343 
344  require_once 'Modules/Forum/classes/class.ilObjForumAccess.php';
345  self::__updateDiskUsageReportOfType(new ilObjForumAccess(), 'frm');
346 
347  require_once 'Modules/HTMLLearningModule/classes/class.ilObjFileBasedLMAccess.php';
348  self::__updateDiskUsageReportOfType(new ilObjFileBasedLMAccess(), 'htlm');
349 
350  require_once 'Modules/MediaCast/classes/class.ilObjMediaCastAccess.php';
351  self::__updateDiskUsageReportOfType(new ilObjMediaCastAccess(), 'mcst');
352 
353  require_once 'Modules/ScormAicc/classes/class.ilObjSAHSLearningModuleAccess.php';
354  self::__updateDiskUsageReportOfType(new ilObjSAHSLearningModuleAccess(), 'sahs');
355 
356  require_once 'Services/Mail/classes/class.ilObjMailAccess.php';
357  self::__updateDiskUsageReportOfUsers(new ilObjMailAccess(), 'mail_attachment');
358 
359  // insert the sum of the disk usage of each user
360  // note: second % is needed to not fail on oracle char field
361  $ilDB->manipulate("INSERT INTO usr_pref ".
362  "(usr_id, keyword, value) ".
363  "SELECT usr_id, 'disk_usage', SUM(value) ".
364  "FROM usr_pref ".
365  "WHERE ".$ilDB->like("keyword", "text", 'disk_usage.%.size%').
366  "GROUP BY usr_id"
367  );
368 
369  // insert last update
370  $ilDB->manipulate("INSERT INTO usr_pref ".
371  "(usr_id, keyword, value) ".
372  "SELECT usr_id, 'disk_usage.last_update', ".$ilDB->now()." ".
373  "FROM usr_data");
374  }
375 
389  private static function __updateDiskUsageReportOfType($a_access_obj, $a_type)
390  {
391  $data = array();
392 
393  // repository
394  $res = self::__getRepositoryObjectsByType($a_type);
395  self::__updateDiskUsageReportOfTypeHelper($a_access_obj, $res, $data);
396 
397  // personal workspace
398  $res = self::__getWorkspaceObjectsByType($a_type);
399  self::__updateDiskUsageReportOfTypeHelper($a_access_obj, $res, $data);
400 
401  // saving result to DB
402  if($data)
403  {
404  foreach($data as $owner => $item)
405  {
406  self::__saveUserData($owner, $a_type, $item["size"], $item["count"]);
407  }
408  }
409  }
410 
419  private static function __saveUserData($a_user_id, $a_type, $a_size, $a_count)
420  {
421  global $ilDB;
422 
423  if ($a_user_id && $a_size != null && $a_count != null)
424  {
425  $ilDB->manipulate("INSERT INTO usr_pref ".
426  "(usr_id, keyword, value) ".
427  "VALUES ".
428  "(".$ilDB->quote($a_user_id,'integer').", ".
429  $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
430  $ilDB->quote($a_size, 'integer').")");
431 
432  $ilDB->manipulate("INSERT INTO usr_pref ".
433  "(usr_id, keyword, value) ".
434  "VALUES ".
435  "(".$ilDB->quote($a_user_id,'integer').", ".
436  $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
437  $ilDB->quote($a_count, 'integer').")"
438  );
439  }
440  }
441 
450  private static function __updateDiskUsageReportOfTypeHelper($a_access_obj, $a_objects, &$a_result)
451  {
452  $count = null;
453  $size = null;
454  $owner = null;
455  while ($row = $a_objects->fetchRow(DB_FETCHMODE_OBJECT)) {
456  if ($row->owner != $owner) {
457  if ($owner != null) {
458  $a_result[$owner]["size"] += $size;
459  $a_result[$owner]["count"] += $count;
460  }
461  $owner = $row->owner;
462  $size = 0;
463  $count = 0;
464  }
465  $size += $a_access_obj->_lookupDiskUsage($row->obj_id);
466  $count++;
467  }
468 
469  // add last set data
470  if ($owner != null) {
471  $a_result[$owner]["size"] += $size;
472  $a_result[$owner]["count"] += $count;
473  }
474  }
475 
483  private static function __getRepositoryObjectsByType($a_type)
484  {
485  global $ilDB;
486 
487  return $ilDB->query("SELECT DISTINCT d.obj_id, d.owner ".
488  "FROM object_data d ".
489  "JOIN object_reference r ON d.obj_id=r.obj_id ".
490  "JOIN tree t ON t.child=r.ref_id ".
491  "WHERE d.type = ".$ilDB->quote($a_type, "text")." ".
492  "AND t.tree=1 ".
493  "ORDER BY d.owner"
494  );
495  }
496 
504  private static function __getWorkspaceObjectsByType($a_type)
505  {
506  global $ilDB;
507 
508  return $ilDB->query("SELECT DISTINCT d.obj_id, d.owner ".
509  "FROM object_data d ".
510  "JOIN object_reference_ws r ON d.obj_id=r.obj_id ".
511  "JOIN tree_workspace t ON t.child=r.wsp_id ".
512  "WHERE d.type = ".$ilDB->quote($a_type, "text")." ".
513  "AND t.tree=d.owner ".
514  "ORDER BY d.owner"
515  );
516  }
517 
531  private static function __updateDiskUsageReportOfUsers($a_access_obj, $a_type)
532  {
533  global $ilDB;
534 
535  // get all users
536  $res = $ilDB->query("SELECT usr_id FROM usr_data");
537 
538  // for each user count the number of objects and sum up the size
539  while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
540  $data = $a_access_obj->_lookupDiskUsageOfUser($row->usr_id);
541  self::__saveUserData($row->usr_id, $a_type, $data["size"], $data["count"]);
542  }
543  }
544 
545  public static function _sendSummaryMails()
546  {
547  global $ilSetting;
548 
549  $lastStart = $ilSetting->get('last_cronjob_disk_quota_sum_start_ts', 0);
550  if( !$lastStart || date('dmY', $lastStart) != date('dmY') )
551  {
552  $ilSetting->set('last_cronjob_disk_quota_sum_start_ts', time());
553 
554  include_once 'Services/Mail/classes/class.ilDiskQuotaSummaryNotification.php';
555  $dqsn = new ilDiskQuotaSummaryNotification();
556  $dqsn->send();
557  }
558  }
559 
565  public static function _sendReminderMails()
566  {
567  global $ilDB;
568 
569  require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
570  $mail = new ilDiskQuotaReminderMail();
571 
572  $res = $ilDB->queryf(
573  "SELECT u.usr_id,u.gender,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
574  "u.time_limit_unlimited, ".$ilDB->fromUnixtime("u.time_limit_from").", ".$ilDB->fromUnixtime("u.time_limit_until").",".
575 
576  // Inactive users get the date 0001-01-01 so that they appear
577  // first when the list is sorted by this field. Users with
578  // unlimited access get the date 9999-12-31 so that they appear
579  // last.
580  "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,".
581 
582  " CASE WHEN ".$ilDB->unixTimestamp()." BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
583  "rq.role_disk_quota, system_role.rol_id role_id, ".
584  "p1.value+0 user_disk_quota,".
585  "p2.value+0 disk_usage, ".
586  "p3.value last_update, ".
587  "p4.value last_reminder, ".
588  "p5.value language, ".
589 
590  // We add 0 to some of the values to convert them into a number.
591  // This is needed for correct sorting.
592  "CASE WHEN rq.role_disk_quota>p1.value+0 OR p1.value IS NULL THEN rq.role_disk_quota ELSE p1.value+0 END disk_quota ".
593  "FROM usr_data u ".
594 
595  // Fetch the role with the highest disk quota value.
596  "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
597  "FROM usr_data u ".
598  "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
599  "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s ".
600  "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 ".
601 
602  // Fetch the system role in order to determine whether the user has unlimited disk quota
603  "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".
604 
605  // Fetch the user disk quota from table usr_pref
606  "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota' ".
607 
608  // Fetch the disk usage from table usr_pref
609  "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage' ".
610 
611  // Fetch the last update from table usr_pref
612  "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update' ".
613 
614  // Fetch the date when the last disk quota reminder was sent from table usr_pref
615  "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder' ".
616 
617  // Fetch the language of the user
618  "LEFT JOIN usr_pref p5 ON p5.usr_id=u.usr_id AND p5.keyword = 'language' ".
619 
620  // Fetch only users who have exceeded their quota, and who have
621  // access, and who have not received a reminder in the past seven days
622  // #8554 / #10301
623  'WHERE (((p1.value+0 > rq.role_disk_quota OR rq.role_disk_quota IS NULL) AND p2.value+0 > p1.value+0) OR
624  ((rq.role_disk_quota > p1.value+0 OR p1.value IS NULL) AND p2.value+0 > rq.role_disk_quota)) '.
625  'AND (u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().' BETWEEN u.time_limit_from AND u.time_limit_until)) '.
626  'AND (p4.value IS NULL OR p4.value < DATE_SUB(NOW(), INTERVAL 7 DAY)) '
627 
628  ,
629  array('integer','integer'),
630  array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
631  );
632 
633  while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
634  $details = self::_lookupDiskUsage($row['usr_id']);
635  $row['disk_usage_details'] = $details['details'];
636 
637  // Send reminder e-mail
638  $mail->setData($row);
639  $mail->send();
640 
641  // Store the date the last reminder was sent in the table usr_pref.
642  if ($row['last_reminder'] != null)
643  {
644  $ilDB->manipulatef("UPDATE usr_pref SET value= ".$ilDB->now()." ".
645  "WHERE usr_id=%s AND keyword = 'disk_quota_last_reminder'"
646  ,
647  array('integer'),
648  array($row['usr_id'])
649  );
650  }
651  else
652  {
653  $ilDB->manipulatef("INSERT INTO usr_pref (usr_id, keyword, value) ".
654  "VALUES (%s, 'disk_quota_last_reminder', ".$ilDB->now().")"
655  ,
656  array('integer'),
657  array($row['usr_id'])
658  );
659  }
660  }
661  }
662 
667  public static function _lookupDiskUsageReportLastUpdate()
668  {
669  global $ilDB;
670 
671  require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
672  $mail = new ilDiskQuotaReminderMail();
673 
674  $res = $ilDB->query("SELECT MAX(value) last_update ".
675  "FROM usr_pref WHERE keyword='disk_usage.last_update'");
676  $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
677  return ($row != null) ? $row['last_update'] : null;
678  }
679 }
680 ?>