77 $res =
$ilDB->queryf(
"SELECT keyword, value ".
80 "AND keyword IN ('disk_quota', 'disk_quota_last_reminder')",
85 switch (
$row->keyword)
90 case 'disk_quota_last_reminder' :
102 $res =
$ilDB->queryf(
"SELECT rd.role_id, rd.disk_quota, od.title ".
104 "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent = %s ".
105 "JOIN role_data rd ON ua.rol_id=rd.role_id ".
106 "JOIN object_data od ON od.obj_id=rd.role_id ".
107 "WHERE ua.usr_id = %s ".
108 "ORDER BY disk_quota DESC, role_id ASC",
109 array(
'integer',
'integer'),
110 array(ROLE_FOLDER_ID, $a_user_id));
118 $info[
'role_disk_quota'] = (
$row->role_id == SYSTEM_ROLE_ID) ? -log(0) :
$row->disk_quota;
119 $info[
'disk_quota'] = max(
$info[
'user_disk_quota'],
$info[
'role_disk_quota']);
149 $res =
$ilDB->query(
"SELECT keyword, value ".
151 "WHERE usr_id = ".
$ilDB->quote($a_user_id,
'integer').
" ".
152 "AND ".$ilDB->like(
"keyword",
"text",
'disk\\_usage%')
156 switch (
$row->keyword)
158 case 'disk_usage.last_update' :
172 preg_match(
'/^disk_usage\\.([^.]+)\\.([^.]+)/',
$row->keyword, $matches);
177 $detail_data = $details[$type];
178 if ($detail_data ==
null)
180 $detail_data = array(
'type'=>$type);
182 $detail_data[$key] =
$row->value;
184 $details[$type] = $detail_data;
188 $info[
'details'] = $details;
221 public static function _fetchDiskQuotaReport($a_usage_filter = 3, $a_access_filter = 1, $a_order_column=
'disk_usage',$a_order_by=
'desc')
227 if (! $a_order_column) {
228 $a_order_column =
'disk_usage';
231 switch ($a_usage_filter) {
236 $where_clause =
'WHERE (p2.value IS NULL) ';
240 $where_clause =
'WHERE (p2.value+0 > 0) ';
244 $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
245 ((rq.role_disk_quota > p1.value+0 OR p1.value IS NULL) AND p2.value+0 > rq.role_disk_quota)) ';
248 switch ($a_access_filter) {
254 $where_clause .= ($where_clause ==
'' ?
'WHERE ' :
' AND ' ).
255 '(u.active=1 AND (u.time_limit_unlimited = 1 OR '.
$ilDB->unixTimestamp().
' BETWEEN u.time_limit_from AND u.time_limit_until)) ';
258 $where_clause .= ($where_clause ==
'' ?
'WHERE ' :
' AND ' ).
259 '(u.active=0 OR (u.time_limit_unlimited IS NULL AND '.
$ilDB->unixTimestamp().
' NOT BETWEEN u.time_limit_from AND u.time_limit_until)) ';
264 "SELECT u.usr_id,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
265 "u.time_limit_unlimited, ".
$ilDB->fromUnixtime(
"u.time_limit_from").
", ".
$ilDB->fromUnixtime(
"u.time_limit_until").
",".
271 "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,".
273 "CASE WHEN ".$ilDB->unixTimestamp().
" BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
274 "rq.role_disk_quota, system_role.rol_id role_id, ".
275 "p1.value+0 user_disk_quota,".
276 "p2.value+0 disk_usage, ".
277 "p3.value last_update, ".
278 "p4.value last_reminder, ".
282 "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 ".
286 "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
288 "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
289 "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s ".
290 "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 ".
293 "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".
296 "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota' ".
299 "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage' ".
302 "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update' ".
305 "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder' ".
308 "ORDER BY ".$a_order_column.
" ".($a_order_by==
'asc'?
' ASC':
' DESC').
", ".
309 "lastname, firstname, login"
311 array(
'integer',
'integer'),
312 array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
314 $previous_usr_id =
null;
316 if ($previous_usr_id !=
$row[
'usr_id'])
319 $previous_usr_id =
$row[
'usr_id'];
341 $ilDB->manipulate(
"DELETE FROM usr_pref ".
342 "WHERE ".
$ilDB->like(
"keyword",
"text",
'disk_usage%'));
345 require_once
'Modules/File/classes/class.ilObjFileAccess.php';
348 require_once
'Modules/Forum/classes/class.ilObjForumAccess.php';
351 require_once
'Modules/HTMLLearningModule/classes/class.ilObjFileBasedLMAccess.php';
354 require_once
'Modules/MediaCast/classes/class.ilObjMediaCastAccess.php';
357 require_once
'Modules/ScormAicc/classes/class.ilObjSAHSLearningModuleAccess.php';
360 require_once
'Services/Mail/classes/class.ilObjMailAccess.php';
365 $ilDB->manipulate(
"INSERT INTO usr_pref ".
366 "(usr_id, keyword, value) ".
367 "SELECT usr_id, 'disk_usage', SUM(value) ".
369 "WHERE ".
$ilDB->like(
"keyword",
"text",
'disk_usage.%.size%').
374 $ilDB->manipulate(
"INSERT INTO usr_pref ".
375 "(usr_id, keyword, value) ".
376 "SELECT usr_id, 'disk_usage.last_update', ".
$ilDB->now().
" ".
408 foreach(
$data as $owner => $item)
428 if ($a_user_id && $a_size !=
null && $a_count !=
null)
430 $ilDB->manipulate(
"INSERT INTO usr_pref ".
431 "(usr_id, keyword, value) ".
433 "(".
$ilDB->quote($a_user_id,
'integer').
", ".
434 $ilDB->quote(
'disk_usage.'.$a_type.
'.size').
", ".
435 $ilDB->quote($a_size,
'integer').
")");
437 $ilDB->manipulate(
"INSERT INTO usr_pref ".
438 "(usr_id, keyword, value) ".
440 "(".
$ilDB->quote($a_user_id,
'integer').
", ".
441 $ilDB->quote(
'disk_usage.'.$a_type.
'.count').
", ".
442 $ilDB->quote($a_count,
'integer').
")"
461 if (
$row->owner != $owner) {
462 if ($owner !=
null) {
463 $a_result[$owner][
"size"] +=
$size;
464 $a_result[$owner][
"count"] += $count;
466 $owner =
$row->owner;
470 $size += $a_access_obj->_lookupDiskUsage(
$row->obj_id);
475 if ($owner !=
null) {
476 $a_result[$owner][
"size"] +=
$size;
477 $a_result[$owner][
"count"] += $count;
493 return $ilDB->query(
"SELECT DISTINCT d.obj_id, d.owner ".
494 "FROM object_data d ".
495 "JOIN object_reference r ON d.obj_id=r.obj_id ".
496 "JOIN tree t ON t.child=r.ref_id ".
515 return $ilDB->query(
"SELECT DISTINCT d.obj_id, d.owner ".
516 "FROM object_data d ".
517 "JOIN object_reference_ws r ON d.obj_id=r.obj_id ".
518 "JOIN tree_workspace t ON t.child=r.wsp_id ".
520 "AND t.tree=d.owner ".
544 $res =
$ilDB->query(
"SELECT usr_id FROM usr_data");
548 $data = $a_access_obj->_lookupDiskUsageOfUser(
$row->usr_id);
558 $lastStart =
$ilSetting->get(
'last_cronjob_disk_quota_sum_start_ts', 0);
559 if( !$lastStart ||
date(
'dmY', $lastStart) !=
date(
'dmY') )
561 $ilSetting->set(
'last_cronjob_disk_quota_sum_start_ts', time());
563 include_once
'Services/Mail/classes/class.ilDiskQuotaSummaryNotification.php';
579 require_once
'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
583 "SELECT u.usr_id,u.gender,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
584 "u.time_limit_unlimited, ".
$ilDB->fromUnixtime(
"u.time_limit_from").
", ".
$ilDB->fromUnixtime(
"u.time_limit_until").
",".
590 "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,".
592 " CASE WHEN ".$ilDB->unixTimestamp().
" BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
593 "rq.role_disk_quota, system_role.rol_id role_id, ".
594 "p1.value+0 user_disk_quota,".
595 "p2.value+0 disk_usage, ".
596 "p3.value last_update, ".
597 "p4.value last_reminder, ".
598 "p5.value language, ".
602 "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 ".
606 "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
608 "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
609 "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s ".
610 "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 ".
613 "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".
616 "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota' ".
619 "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage' ".
622 "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update' ".
625 "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder' ".
628 "LEFT JOIN usr_pref p5 ON p5.usr_id=u.usr_id AND p5.keyword = 'language' ".
633 'WHERE (((p1.value+0 >= rq.role_disk_quota OR rq.role_disk_quota IS NULL) AND p2.value+0 > p1.value+0) OR
634 ((rq.role_disk_quota > p1.value+0 OR p1.value IS NULL) AND p2.value+0 > rq.role_disk_quota)) '.
635 'AND (u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().
' BETWEEN u.time_limit_from AND u.time_limit_until)) '.
636 'AND (p4.value IS NULL OR p4.value < DATE_SUB(NOW(), INTERVAL 7 DAY)) '
639 array(
'integer',
'integer'),
640 array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
645 $row[
'disk_usage_details'] = $details[
'details'];
648 $mail->setData(
$row);
652 if (
$row[
'last_reminder'] !=
null)
654 $ilDB->manipulatef(
"UPDATE usr_pref SET value= ".
$ilDB->now().
" ".
655 "WHERE usr_id=%s AND keyword = 'disk_quota_last_reminder'"
658 array(
$row[
'usr_id'])
663 $ilDB->manipulatef(
"INSERT INTO usr_pref (usr_id, keyword, value) ".
664 "VALUES (%s, 'disk_quota_last_reminder', ".
$ilDB->now().
")"
667 array(
$row[
'usr_id'])
682 require_once
'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
685 $res =
$ilDB->query(
"SELECT MAX(value) last_update ".
686 "FROM usr_pref WHERE keyword='disk_usage.last_update'");
688 return (
$row !=
null) ?
$row[
'last_update'] :
null;
698 $res =
$ilDB->queryf(
"SELECT keyword, value ".
700 "WHERE usr_id = %s ".
702 array(
'integer',
'text'),
703 array($a_user_id,
"wsp_disk_quota"));
706 $info[
'user_wsp_disk_quota'] =
$row->value;
714 $res =
$ilDB->queryf(
"SELECT rd.role_id, rd.wsp_disk_quota, od.title ".
716 "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent = %s ".
717 "JOIN role_data rd ON ua.rol_id=rd.role_id ".
718 "JOIN object_data od ON od.obj_id=rd.role_id ".
719 "WHERE ua.usr_id = %s ".
720 "ORDER BY wsp_disk_quota DESC, role_id ASC",
721 array(
'integer',
'integer'),
722 array(ROLE_FOLDER_ID, $a_user_id));
730 $info[
'role_wsp_disk_quota'] = (
$row->role_id == SYSTEM_ROLE_ID) ? -log(0) :
$row->wsp_disk_quota;
731 $info[
'disk_quota'] = max(
$info[
'user_wsp_disk_quota'],
$info[
'role_wsp_disk_quota']);
date( 'd-M-Y', $objPHPExcel->getProperties() ->getCreated())
An exception for terminatinating execution or to throw for unit testing.
Class ilDiskQuotaChecker.
static __getRepositoryObjectsByType($a_type)
static __getWorkspaceObjectsByType($a_type)
get all objects of the desired type which are in the personal workspace ordered by owner
static __updateDiskUsageReportOfUsers($a_access_obj, $a_type)
Updates the disk usage report of the specified object type for all user accounts.
static _updateDiskUsageReport()
Updates the disk usage info of all user accounts.
static _lookupDiskUsage($a_user_id)
Gets the disk usage info for the specified user account.
static __updateDiskUsageReportOfType($a_access_obj, $a_type)
Updates the disk usage report of the specified object type for all user accounts.
static _lookupPersonalWorkspaceDiskQuota($a_user_id)
static _sendSummaryMails()
static _lookupDiskQuota($a_user_id)
Gets the disk quota info for the specified user account.
static __saveUserData($a_user_id, $a_type, $a_size, $a_count)
Save disk quota for user.
static __updateDiskUsageReportOfTypeHelper($a_access_obj, $a_objects, &$a_result)
for each objects of an owner, count the number of objects and sum up the size
static _lookupDiskUsageReportLastUpdate()
Returns the SQL datetime of the last update of the disk usage report.
static _fetchDiskQuotaReport($a_usage_filter=3, $a_access_filter=1, $a_order_column='disk_usage', $a_order_by='desc')
Reads disk quota/disk usage report of the user accounts.
static _sendReminderMails()
Sends reminder e-mails to all users who have access and who have exceeded their disk quota and who ha...
Class ilDiskQuotaReminderMail.
Access class for file objects.
Class ilFileBasedLMAccess.
Class ilObjContentObjectAccess.