ILIAS  eassessment Revision 61809
 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/HTMLLearningModule/classes/class.ilObjFileBasedLMAccess.php';
343  self::__updateDiskUsageReportOfType(new ilObjFileBasedLMAccess(), 'htlm');
344 
345  require_once 'Modules/MediaCast/classes/class.ilObjMediaCastAccess.php';
346  self::__updateDiskUsageReportOfType(new ilObjMediaCastAccess(), 'mcst');
347 
348  require_once 'Modules/ScormAicc/classes/class.ilObjSAHSLearningModuleAccess.php';
349  self::__updateDiskUsageReportOfType(new ilObjSAHSLearningModuleAccess(), 'sahs');
350 
351  require_once 'Services/Mail/classes/class.ilObjMailAccess.php';
352  self::__updateDiskUsageReportOfUsers(new ilObjMailAccess(), 'mail_attachment');
353 
354  // insert the sum of the disk usage of each user
355  // note: second % is needed to not fail on oracle char field
356  $ilDB->manipulate("INSERT INTO usr_pref ".
357  "(usr_id, keyword, value) ".
358  "SELECT usr_id, 'disk_usage', SUM(value) ".
359  "FROM usr_pref ".
360  "WHERE ".$ilDB->like("keyword", "text", 'disk_usage.%.size%').
361  "GROUP BY usr_id"
362  );
363 
364  // insert last update
365  $ilDB->manipulate("INSERT INTO usr_pref ".
366  "(usr_id, keyword, value) ".
367  "SELECT usr_id, 'disk_usage.last_update', ".$ilDB->now()." ".
368  "FROM usr_data");
369  }
370 
384  private static function __updateDiskUsageReportOfType($a_access_obj, $a_type)
385  {
386  global $ilDB;
387 
388  // get all objects of the desired type which are in the repository
389  // ordered by owner
390  $res = $ilDB->query("SELECT DISTINCT d.obj_id, d.owner ".
391  "FROM object_data d ".
392  "JOIN object_reference r ON d.obj_id=r.obj_id ".
393  "JOIN tree t ON t.child=r.ref_id ".
394  "WHERE d.type = ".$ilDB->quote($a_type, "text")." ".
395  "AND t.tree=1 ".
396  "ORDER BY d.owner"
397  );
398 
399  // for each objects of an owner, count the number of objects and sum up
400  // the size
401  $count = null;
402  $size = null;
403  $owner = null;
404  while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
405  if ($row->owner != $owner) {
406  if ($owner != null) {
407  $ilDB->manipulate("INSERT INTO usr_pref ".
408  "(usr_id, keyword, value) ".
409  "VALUES ".
410  "(".$ilDB->quote($owner,'integer').", ".
411  $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
412  $ilDB->quote($size, 'integer').")");
413 
414  $ilDB->manipulate("INSERT INTO usr_pref ".
415  "(usr_id, keyword, value) ".
416  "VALUES ".
417  "(".$ilDB->quote($owner,'integer').", ".
418  $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
419  $ilDB->quote($count, 'integer').")"
420  );
421 
422  }
423  $owner = $row->owner;
424  $size = 0;
425  $count = 0;
426  }
427  $size += $a_access_obj->_lookupDiskUsage($row->obj_id);
428  $count++;
429  }
430  if ($owner != null) {
431  $ilDB->manipulate("INSERT INTO usr_pref ".
432  "(usr_id, keyword, value) ".
433  "VALUES ".
434  "(".$ilDB->quote($owner,'integer').", ".
435  $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
436  $ilDB->quote($size, 'integer').")");
437 
438  $ilDB->manipulate("INSERT INTO usr_pref ".
439  "(usr_id, keyword, value) ".
440  "VALUES ".
441  "(".$ilDB->quote($owner,'integer').", ".
442  $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
443  $ilDB->quote($count, 'integer').")");
444  }
445 
446  }
447 
461  private static function __updateDiskUsageReportOfUsers($a_access_obj, $a_type)
462  {
463  global $ilDB;
464 
465  // get all users
466  $res = $ilDB->query("SELECT usr_id FROM usr_data");
467 
468  // for each user count the number of objects and sum up the size
469  while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
470  $data = $a_access_obj->_lookupDiskUsageOfUser($row->usr_id);
471 
472  if ($data['size'] != null && $data['count'] != null)
473  {
474  $ilDB->manipulate("INSERT INTO usr_pref ".
475  "(usr_id, keyword, value) ".
476  "VALUES ".
477  "(".$ilDB->quote($row->usr_id,'integer').", ".
478  $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
479  $ilDB->quote($data['size'], 'integer').")");
480  $ilDB->manipulate("INSERT INTO usr_pref ".
481  "(usr_id, keyword, value) ".
482  "VALUES ".
483  "(".$ilDB->quote($row->usr_id,'integer').", ".
484  $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
485  $ilDB->quote($data['count'], 'integer').")");
486  }
487  }
488  }
489 
490  public static function _sendSummaryMails()
491  {
492  global $ilSetting;
493 
494  $lastStart = $ilSetting->get('last_cronjob_disk_quota_sum_start_ts', 0);
495  if( !$lastStart || date('dmY', $lastStart) != date('dmY') )
496  {
497  $ilSetting->set('last_cronjob_disk_quota_sum_start_ts', time());
498 
499  include_once 'Services/Mail/classes/class.ilDiskQuotaSummaryNotification.php';
500  $dqsn = new ilDiskQuotaSummaryNotification();
501  $dqsn->send();
502  }
503  }
504 
510  public static function _sendReminderMails()
511  {
512  global $ilDB;
513 
514  require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
515  $mail = new ilDiskQuotaReminderMail();
516 
517  $res = $ilDB->queryf(
518  "SELECT u.usr_id,u.gender,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
519  "u.time_limit_unlimited, ".$ilDB->fromUnixtime("u.time_limit_from").", ".$ilDB->fromUnixtime("u.time_limit_until").",".
520 
521  // Inactive users get the date 0001-01-01 so that they appear
522  // first when the list is sorted by this field. Users with
523  // unlimited access get the date 9999-12-31 so that they appear
524  // last.
525  "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,".
526 
527  " CASE WHEN ".$ilDB->unixTimestamp()." BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
528  "rq.role_disk_quota, system_role.rol_id role_id, ".
529  "p1.value+0 user_disk_quota,".
530  "p2.value+0 disk_usage, ".
531  "p3.value last_update, ".
532  "p4.value last_reminder, ".
533  "p5.value language, ".
534 
535  // We add 0 to some of the values to convert them into a number.
536  // This is needed for correct sorting.
537  "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 ".
538  "FROM usr_data u ".
539 
540  // Fetch the role with the highest disk quota value.
541  "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
542  "FROM usr_data u ".
543  "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
544  "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s ".
545  "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 ".
546 
547  // Fetch the system role in order to determine whether the user has unlimited disk quota
548  "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".
549 
550  // Fetch the user disk quota from table usr_pref
551  "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota' ".
552 
553  // Fetch the disk usage from table usr_pref
554  "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage' ".
555 
556  // Fetch the last update from table usr_pref
557  "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update' ".
558 
559  // Fetch the date when the last disk quota reminder was sent from table usr_pref
560  "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder' ".
561 
562  // Fetch the language of the user
563  "LEFT JOIN usr_pref p5 ON p5.usr_id=u.usr_id AND p5.keyword = 'language' ".
564 
565  // Fetch only users who have exceeded their quota, and who have
566  // access, and who have not received a reminder in the past seven days
567  'WHERE (p2.value > p1.value AND p2.value > rq.role_disk_quota) '.
568  'AND (u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().' BETWEEN u.time_limit_from AND u.time_limit_until)) '.
569  'AND (p4.value IS NULL OR p4.value < DATE_SUB(NOW(), INTERVAL 7 DAY)) '
570 
571  ,
572  array('integer','integer'),
573  array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
574  );
575 
576  while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
577  $details = self::_lookupDiskUsage($row['usr_id']);
578  $row['disk_usage_details'] = $details['details'];
579 
580  // Send reminder e-mail
581  $mail->setData($row);
582  $mail->send();
583 
584  // Store the date the last reminder was sent in the table usr_pref.
585  if ($row['last_reminder'] != null)
586  {
587  $ilDB->manipulatef("UPDATE usr_pref SET value= ".$ilDB->now()." ".
588  "WHERE usr_id=%s AND keyword = 'disk_quota_last_reminder'"
589  ,
590  array('integer'),
591  array($row['usr_id'])
592  );
593  }
594  else
595  {
596  $ilDB->manipulatef("INSERT INTO usr_pref (usr_id, keyword, value) ".
597  "VALUES (%s, 'disk_quota_last_reminder', ".$ilDB->now().")"
598  ,
599  array('integer'),
600  array($row['usr_id'])
601  );
602  }
603  }
604  }
605 
610  public static function _lookupDiskUsageReportLastUpdate()
611  {
612  global $ilDB;
613 
614  require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
615  $mail = new ilDiskQuotaReminderMail();
616 
617  $res = $ilDB->query("SELECT MAX(value) last_update ".
618  "FROM usr_pref WHERE keyword='disk_usage.last_update'");
619  $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
620  return ($row != null) ? $row['last_update'] : null;
621  }
622 }
623 ?>