class.ilDiskQuotaChecker.php

Go to the documentation of this file.

<?php
/*
        +-----------------------------------------------------------------------------+
        | ILIAS open source                                                           |
        +-----------------------------------------------------------------------------+
        | Copyright (c) 1998-2001 ILIAS open source, University of Cologne            |
        |                                                                             |
        | This program is free software; you can redistribute it and/or               |
        | modify it under the terms of the GNU General Public License                 |
        | as published by the Free Software Foundation; either version 2              |
        | of the License, or (at your option) any later version.                      |
        |                                                                             |
        | This program is distributed in the hope that it will be useful,             |
        | but WITHOUT ANY WARRANTY; without even the implied warranty of              |
        | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               |
        | GNU General Public License for more details.                                |
        |                                                                             |
        | You should have received a copy of the GNU General Public License           |
        | along with this program; if not, write to the Free Software                 |
        | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA. |
        +-----------------------------------------------------------------------------+
*/

class ilDiskQuotaChecker
{
        function ilDiskQuotaChecker()
        {
        }

        public static function _lookupDiskQuota($a_user_id)
        {
                $info = array();

                global $ilDB;

                $res = $ilDB->queryf("SELECT keyword, value ".
                        "FROM usr_pref ".
                        "WHERE usr_id = %s ".
                        "AND keyword IN ('disk_quota', 'disk_quota_last_reminder')",
                array('integer'),
                array($a_user_id));

                while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
                        switch ($row->keyword)
                        {
                                case 'disk_quota' :
                                        $info['user_disk_quota'] = $row->value;
                                        break;
                                case 'disk_quota_last_reminder' :
                                        $info['last_reminder'] = $row->value;
                                        break;
                        }
                }


                // Note: we order by role_id ASC here, in the assumption that
                //       the system role has the lowest ID of all roles.
                //       this way, if a user has the system role, this role
                //       will always returned first.
                $ilDB->setLimit(1);
                $res = $ilDB->queryf("SELECT rd.role_id, rd.disk_quota, od.title ".
                        "FROM rbac_ua ua ".
                        "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent = %s ".
                        "JOIN role_data rd ON ua.rol_id=rd.role_id ".
                        "JOIN object_data od ON od.obj_id=rd.role_id ".
                        "WHERE ua.usr_id = %s ".
                        "ORDER BY disk_quota DESC, role_id ASC",
                array('integer','integer'),
                array(ROLE_FOLDER_ID, $a_user_id));

                $row = $res->fetchRow(DB_FETCHMODE_OBJECT);
                $info['role_id'] = $row->role_id;
                $info['role_title'] = $row->title;

                // Note: Users with the system role have an infinite disk quota
                //       We calculate positive infinity by negating the logarithm of 0.
                $info['role_disk_quota']  = ($row->role_id == SYSTEM_ROLE_ID) ? -log(0) : $row->disk_quota;
                $info['disk_quota'] = max($info['user_disk_quota'], $info['role_disk_quota']);

                return $info;
        }

        public static function _lookupDiskUsage($a_user_id)
        {
                $info = array();
                $details = array();

                global $ilDB;

                $res = $ilDB->query("SELECT keyword, value ".
                        "FROM usr_pref ".
                        "WHERE usr_id = ".$ilDB->quote($a_user_id, 'integer')." ".
                        "AND ".$ilDB->like("keyword", "text", 'disk\\_usage%')
                        );

                while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
                        switch ($row->keyword)
                        {
                                case 'disk_usage.last_update' :
                                        $info['last_update'] = $row->value;
                                        break;

                                case 'disk_usage' :
                                        $info['disk_usage'] = $row->value;
                                        break;

                                default :
                                        // The following preg_match is used to extract the type
                                        // and the keys 'count' and 'size' from the keywords:
                                        // disk_usage.type.count
                                        // disk_usage.type.size
                                        $matches = array();
                                        preg_match('/^disk_usage\\.([^.]+)\\.([^.]+)/', $row->keyword, $matches);
                                        $type = $matches[1];
                                        $key = $matches[2];
                                        if ($type)
                                        {
                                                $detail_data = $details[$type];
                                                if ($detail_data == null) 
                                                {
                                                        $detail_data = array('type'=>$type);
                                                }
                                                $detail_data[$key] = $row->value;
                                        }
                                        $details[$type] = $detail_data;
                                        break;
                        }
                }
                $info['details'] = $details;


                //ilDiskQuotaChecker::_updateDiskUsageReport();

                return $info;
        }

        public static function _fetchDiskQuotaReport($a_usage_filter = 3, $a_access_filter = 1,  $a_order_column='disk_usage',$a_order_by='desc')
        {
                $data = array();
                global $ilDB;

                if (! $a_order_column) {
                        $a_order_column = 'disk_usage';
                }

                switch ($a_usage_filter) {
                        case 1: // all users
                                $where_clause = '';
                                break;
                        case 2: // only users who don't use disk space
                                $where_clause = 'WHERE (p2.value IS NULL) ';
                                break;
                        case 3: // only users who use disk space
                        default:
                                $where_clause = 'WHERE (p2.value+0 > 0) ';
                                break;
                        case 4: // only users who have exceeded their disk quota
                                // #8554 / #10301
                                $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 
                                        ((rq.role_disk_quota > p1.value+0 OR p1.value IS NULL) AND p2.value+0 > rq.role_disk_quota)) ';
                                break;
                }
                switch ($a_access_filter) {
                        case 1: // all users
                                $where_clause .= '';
                                break;
                        case 2: // only users who have access
                        default:
                                $where_clause .= ($where_clause == '' ? 'WHERE ' : ' AND ' ).
                                        '(u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().' BETWEEN u.time_limit_from AND u.time_limit_until)) ';
                                break;
                        case 3: // only users who don't have access
                                $where_clause .= ($where_clause == '' ? 'WHERE ' : ' AND ' ).
                                        '(u.active=0 OR (u.time_limit_unlimited IS NULL AND '.$ilDB->unixTimestamp().' NOT BETWEEN u.time_limit_from AND u.time_limit_until)) ';
                                break;
                }

                $res = $ilDB->queryf(
                        "SELECT u.usr_id,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
                                "u.time_limit_unlimited, ".$ilDB->fromUnixtime("u.time_limit_from").", ".$ilDB->fromUnixtime("u.time_limit_until").",".

                                // Inactive users get the date 0001-01-01 so that they appear
                                // first when the list is sorted by this field. Users with
                                // unlimited access get the date 9999-12-31 so that they appear
                                // last.
                                "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,".

                                "CASE WHEN ".$ilDB->unixTimestamp()." BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
                                "rq.role_disk_quota, system_role.rol_id role_id, ".
                                "p1.value+0 user_disk_quota,".
                                "p2.value+0 disk_usage, ".
                                "p3.value last_update, ".
                                "p4.value last_reminder, ".

                                // We add 0 to some of the values to convert them into a number.
                                // This is needed for correct sorting.
                                "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     ".
                        "FROM usr_data u  ".

                        // Fetch the role with the highest disk quota value.
                        "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
                                "FROM usr_data u ".
                                "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
                                "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s  ".
                                "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 ".

                        // Fetch the system role in order to determine whether the user has unlimited disk quota
                        "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".

                        // Fetch the user disk quota from table usr_pref
                        "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota'  ".

                        // Fetch the disk usage from table usr_pref
                        "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage'  ".

                        // Fetch the last update from table usr_pref
                        "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update'  ".

                        // Fetch the date when the last disk quota reminder was sent from table usr_pref
                        "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder'  ".

                        $where_clause.
                        "ORDER BY ".$a_order_column." ".($a_order_by=='asc'?' ASC':' DESC').", ".
                                "lastname, firstname, login"
                        ,
                array('integer','integer'),
                array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
                );
                $previous_usr_id = null;
                while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
                        if ($previous_usr_id != $row['usr_id'])
                        {
                                $data[] = $row;
                                $previous_usr_id = $row['usr_id'];
                        }
                }
                return $data;
        }

        public static function _updateDiskUsageReport()
        {
                global $ilDB;

                // delete old values
                $ilDB->manipulate("DELETE FROM usr_pref ".
                        "WHERE ".$ilDB->like("keyword", "text", 'disk_usage%'));


                require_once 'Modules/File/classes/class.ilObjFileAccess.php';
                self::__updateDiskUsageReportOfType(new ilObjFileAccess(), 'file');

                require_once 'Modules/Forum/classes/class.ilObjForumAccess.php';
                self::__updateDiskUsageReportOfType(new ilObjForumAccess(), 'frm');

                require_once 'Modules/HTMLLearningModule/classes/class.ilObjFileBasedLMAccess.php';
                self::__updateDiskUsageReportOfType(new ilObjFileBasedLMAccess(), 'htlm');

                require_once 'Modules/MediaCast/classes/class.ilObjMediaCastAccess.php';
                self::__updateDiskUsageReportOfType(new ilObjMediaCastAccess(), 'mcst');

                require_once 'Modules/ScormAicc/classes/class.ilObjSAHSLearningModuleAccess.php';
                self::__updateDiskUsageReportOfType(new ilObjSAHSLearningModuleAccess(), 'sahs');

                require_once 'Services/Mail/classes/class.ilObjMailAccess.php';
                self::__updateDiskUsageReportOfUsers(new ilObjMailAccess(), 'mail_attachment');

                // insert the sum of the disk usage of each user
                // note: second % is needed to not fail on oracle char field
                $ilDB->manipulate("INSERT INTO usr_pref ".
                        "(usr_id, keyword, value) ".
                        "SELECT usr_id, 'disk_usage', SUM(value) ".
                        "FROM usr_pref ".
                        "WHERE ".$ilDB->like("keyword", "text", 'disk_usage.%.size%').
                        "GROUP BY usr_id"
                        );

                // insert last update
                $ilDB->manipulate("INSERT INTO usr_pref ".
                        "(usr_id, keyword, value) ".
                        "SELECT usr_id, 'disk_usage.last_update', ".$ilDB->now()." ".
                        "FROM usr_data");
        }

        private static function __updateDiskUsageReportOfType($a_access_obj, $a_type)
        {
                $data = array();
                
                // repository
                $res = self::__getRepositoryObjectsByType($a_type);
                self::__updateDiskUsageReportOfTypeHelper($a_access_obj, $res, $data);
                
                // personal workspace
                $res = self::__getWorkspaceObjectsByType($a_type);
                self::__updateDiskUsageReportOfTypeHelper($a_access_obj, $res, $data);
                
                // saving result to DB
                if($data)
                {
                        foreach($data as $owner => $item)
                        {
                                self::__saveUserData($owner, $a_type, $item["size"], $item["count"]);
                        }
                }
        }
        
        private static function __saveUserData($a_user_id, $a_type, $a_size, $a_count)
        {
                global $ilDB;
                
                if ($a_user_id && $a_size != null && $a_count != null)
                {
                        $ilDB->manipulate("INSERT INTO usr_pref ".
                                "(usr_id, keyword, value) ".
                                "VALUES ".
                                "(".$ilDB->quote($a_user_id,'integer').", ".
                                $ilDB->quote('disk_usage.'.$a_type.'.size').", ".
                                $ilDB->quote($a_size, 'integer').")");

                        $ilDB->manipulate("INSERT INTO usr_pref ".
                                "(usr_id, keyword, value) ".
                                "VALUES ".
                                "(".$ilDB->quote($a_user_id,'integer').", ".
                                $ilDB->quote('disk_usage.'.$a_type.'.count').", ".
                                $ilDB->quote($a_count, 'integer').")"
                                );                              
                }
        }
        
        private static function __updateDiskUsageReportOfTypeHelper($a_access_obj, $a_objects, &$a_result)
        {
                $count = null;
                $size = null;
                $owner = null;
                while ($row = $a_objects->fetchRow(DB_FETCHMODE_OBJECT)) {
                        if ($row->owner != $owner) {
                                if ($owner != null) {                                   
                                        $a_result[$owner]["size"] += $size;
                                        $a_result[$owner]["count"] += $count;                                   
                                }
                                $owner = $row->owner;
                                $size = 0;
                                $count = 0;
                        }
                        $size += $a_access_obj->_lookupDiskUsage($row->obj_id);
                        $count++;
                }
                
                // add last set data
                if ($owner != null) {
                        $a_result[$owner]["size"] += $size;
                        $a_result[$owner]["count"] += $count;   
                }
        }
        
        private static function __getRepositoryObjectsByType($a_type)
        {               
                global $ilDB;
                
                return $ilDB->query("SELECT DISTINCT d.obj_id, d.owner ".
                        "FROM object_data d ".
                        "JOIN object_reference r ON d.obj_id=r.obj_id ".
                        "JOIN tree t ON t.child=r.ref_id ".
                        "WHERE d.type = ".$ilDB->quote($a_type, "text")." ".
                        "AND t.tree=1 ".
                        "ORDER BY d.owner"
                        );              
        }
        
        private static function __getWorkspaceObjectsByType($a_type)
        {               
                global $ilDB;
                
                return $ilDB->query("SELECT DISTINCT d.obj_id, d.owner ".
                        "FROM object_data d ".
                        "JOIN object_reference_ws r ON d.obj_id=r.obj_id ".
                        "JOIN tree_workspace t ON t.child=r.wsp_id ".
                        "WHERE d.type = ".$ilDB->quote($a_type, "text")." ".
                        "AND t.tree=d.owner ".
                        "ORDER BY d.owner"
                        );              
        }

        private static function __updateDiskUsageReportOfUsers($a_access_obj, $a_type)
        {
                global $ilDB;

                // get all users
                $res = $ilDB->query("SELECT usr_id FROM usr_data");

                // for each user count the number of objects and sum up the size
                while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
                        $data = $a_access_obj->_lookupDiskUsageOfUser($row->usr_id);
                        self::__saveUserData($row->usr_id, $a_type, $data["size"], $data["count"]);
                }
        }
        
        public static function _sendSummaryMails()
        {
                global $ilSetting;
                
                $lastStart = $ilSetting->get('last_cronjob_disk_quota_sum_start_ts', 0);
                if( !$lastStart || date('dmY', $lastStart) != date('dmY') )
                {
                        $ilSetting->set('last_cronjob_disk_quota_sum_start_ts', time());
                        
                        include_once 'Services/Mail/classes/class.ilDiskQuotaSummaryNotification.php';
                        $dqsn = new ilDiskQuotaSummaryNotification();
                        $dqsn->send();
                }
        }
        
        public static function _sendReminderMails()
        {
                global $ilDB;

                require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
                $mail = new ilDiskQuotaReminderMail();

                $res = $ilDB->queryf(
                        "SELECT u.usr_id,u.gender,u.firstname,u.lastname,u.login,u.email,u.last_login,u.active,".
                                "u.time_limit_unlimited, ".$ilDB->fromUnixtime("u.time_limit_from").", ".$ilDB->fromUnixtime("u.time_limit_until").",".

                                // Inactive users get the date 0001-01-01 so that they appear
                                // first when the list is sorted by this field. Users with
                                // unlimited access get the date 9999-12-31 so that they appear
                                // last.
                                "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,".

                                " CASE WHEN ".$ilDB->unixTimestamp()." BETWEEN u.time_limit_from AND u.time_limit_until THEN 0 ELSE 1 END expired,".
                                "rq.role_disk_quota, system_role.rol_id role_id, ".
                                "p1.value+0 user_disk_quota,".
                                "p2.value+0 disk_usage, ".
                                "p3.value last_update, ".
                                "p4.value last_reminder, ".
                                "p5.value language, ".

                                // We add 0 to some of the values to convert them into a number.
                                // This is needed for correct sorting.
                                "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     ".
                        "FROM usr_data u  ".

                        // Fetch the role with the highest disk quota value.
                        "JOIN (SELECT u.usr_id usr_id,MAX(rd.disk_quota) role_disk_quota ".
                                "FROM usr_data u ".
                                "JOIN rbac_ua ua ON ua.usr_id=u.usr_id ".
                                "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent=%s  ".
                                "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 ".

                        // Fetch the system role in order to determine whether the user has unlimited disk quota
                        "LEFT JOIN rbac_ua system_role ON system_role.usr_id=u.usr_id AND system_role.rol_id = %s ".

                        // Fetch the user disk quota from table usr_pref
                        "LEFT JOIN usr_pref p1 ON p1.usr_id=u.usr_id AND p1.keyword = 'disk_quota'  ".

                        // Fetch the disk usage from table usr_pref
                        "LEFT JOIN usr_pref p2 ON p2.usr_id=u.usr_id AND p2.keyword = 'disk_usage'  ".

                        // Fetch the last update from table usr_pref
                        "LEFT JOIN usr_pref p3 ON p3.usr_id=u.usr_id AND p3.keyword = 'disk_usage.last_update'  ".

                        // Fetch the date when the last disk quota reminder was sent from table usr_pref
                        "LEFT JOIN usr_pref p4 ON p4.usr_id=u.usr_id AND p4.keyword = 'disk_quota_last_reminder'  ".

                        // Fetch the language of the user
                        "LEFT JOIN usr_pref p5 ON p5.usr_id=u.usr_id AND p5.keyword = 'language'  ".

                        // Fetch only users who have exceeded their quota, and who have
                        // access, and who have not received a reminder in the past seven days
                        // #8554 / #10301
                        'WHERE (((p1.value+0 >= rq.role_disk_quota OR rq.role_disk_quota IS NULL) AND p2.value+0 > p1.value+0) OR 
                                ((rq.role_disk_quota > p1.value+0 OR p1.value IS NULL) AND p2.value+0 > rq.role_disk_quota)) '.
                        'AND (u.active=1 AND (u.time_limit_unlimited = 1 OR '.$ilDB->unixTimestamp().' BETWEEN u.time_limit_from AND u.time_limit_until)) '.
                        'AND (p4.value IS NULL OR p4.value < DATE_SUB(NOW(), INTERVAL 7 DAY)) '

                        ,
                array('integer','integer'),
                array(ROLE_FOLDER_ID, SYSTEM_ROLE_ID)
                );

                while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
                        $details = self::_lookupDiskUsage($row['usr_id']);
                        $row['disk_usage_details'] = $details['details'];

                        // Send reminder e-mail
                        $mail->setData($row);
                        $mail->send();

                        // Store the date the last reminder was sent in the table usr_pref.
                        if ($row['last_reminder'] != null)
                        {
                                $ilDB->manipulatef("UPDATE usr_pref SET value= ".$ilDB->now()." ".
                                        "WHERE usr_id=%s AND keyword = 'disk_quota_last_reminder'"
                                        ,
                                        array('integer'),
                                        array($row['usr_id'])
                                        );
                        }
                        else
                        {
                                $ilDB->manipulatef("INSERT INTO usr_pref (usr_id, keyword, value) ".
                                        "VALUES (%s, 'disk_quota_last_reminder', ".$ilDB->now().")"
                                        ,
                                        array('integer'),
                                        array($row['usr_id'])
                                        );
                        }
                }
        }

        public static function _lookupDiskUsageReportLastUpdate()
        {
                global $ilDB;

                require_once 'Services/Mail/classes/class.ilDiskQuotaReminderMail.php';
                $mail = new ilDiskQuotaReminderMail();

                $res = $ilDB->query("SELECT MAX(value) last_update ".
                        "FROM usr_pref WHERE keyword='disk_usage.last_update'");
                $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
                return ($row != null) ? $row['last_update'] : null;
        }
        
        public static function _lookupPersonalWorkspaceDiskQuota($a_user_id)
        {
                global $ilDB;
                
                $info = array();

                $res = $ilDB->queryf("SELECT keyword, value ".
                        "FROM usr_pref ".
                        "WHERE usr_id = %s ".
                        "AND keyword = %s ",
                array('integer', 'text'),
                array($a_user_id, "wsp_disk_quota"));

                $row = $res->fetchRow(DB_FETCHMODE_OBJECT);
                $info['user_wsp_disk_quota'] = $row->value;
                                

                // Note: we order by role_id ASC here, in the assumption that
                //       the system role has the lowest ID of all roles.
                //       this way, if a user has the system role, this role
                //       will always returned first.
                $ilDB->setLimit(1);
                $res = $ilDB->queryf("SELECT rd.role_id, rd.wsp_disk_quota, od.title ".
                        "FROM rbac_ua ua ".
                        "JOIN rbac_fa fa ON fa.rol_id=ua.rol_id AND fa.parent = %s ".
                        "JOIN role_data rd ON ua.rol_id=rd.role_id ".
                        "JOIN object_data od ON od.obj_id=rd.role_id ".
                        "WHERE ua.usr_id = %s ".
                        "ORDER BY wsp_disk_quota DESC, role_id ASC",
                array('integer','integer'),
                array(ROLE_FOLDER_ID, $a_user_id));

                $row = $res->fetchRow(DB_FETCHMODE_OBJECT);
                $info['role_id'] = $row->role_id;
                $info['role_title'] = $row->title;

                // Note: Users with the system role have an infinite disk quota
                //       We calculate positive infinity by negating the logarithm of 0.
                $info['role_wsp_disk_quota']  = ($row->role_id == SYSTEM_ROLE_ID) ? -log(0) : $row->wsp_disk_quota;
                $info['disk_quota'] = max($info['user_wsp_disk_quota'], $info['role_wsp_disk_quota']);

                return $info;
        }
}
?>