class.ilMailBoxQuery.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */

class ilMailBoxQuery
{
    public static $folderId = -1;
    public static $userId = -1;
    public static $limit = 0;
    public static $offset = 0;
    public static $orderDirection = '';
    public static $orderColumn = '';
    public static $filter = array();
    public static $filtered_ids = array();

    public static function _getMailBoxListData()
    {
        global $DIC;

        $mails = array('cnt' => 0, 'cnt_unread' => 0, 'set' => array());

        $filter = array(
            'mail_filter_sender'     => 'CONCAT(CONCAT(firstname, lastname), login)',
            'mail_filter_recipients' => ($DIC->database()->getDBType() == 'oracle' ?
                "CONCAT(CONCAT(CAST(rcp_to AS VARCHAR2(4000)), CAST(rcp_cc AS VARCHAR2(4000))), CAST(rcp_bcc AS VARCHAR2(4000))))" :
                "CONCAT(CONCAT(rcp_to, rcp_cc), rcp_bcc)"),
            'mail_filter_subject'    => 'm_subject',
            'mail_filter_body'       => 'm_message',
            'mail_filter_attach'     => ''
        );
        $filter_parts = array();
        if (isset(self::$filter['mail_filter']) && strlen(self::$filter['mail_filter'])) {
            foreach ($filter as  $key => $column) {
                if (strlen($column) && isset(self::$filter[$key]) && (int) self::$filter[$key]) {
                    $filter_parts[] = $DIC->database()->like($column, 'text', '%%' . self::$filter['mail_filter'] . '%%', false);
                }
            }
        }
        $filter_qry = '';
        if ($filter_parts) {
            $filter_qry = 'AND (' . implode(' OR ', $filter_parts) . ')';
        }
        // count query
        $queryCount = 'SELECT COUNT(mail_id) cnt FROM mail '
                    . 'LEFT JOIN usr_data ON usr_id = sender_id '
                    . 'WHERE user_id = %s '
                    . 'AND ((sender_id > 0 AND sender_id IS NOT NULL AND usr_id IS NOT NULL) OR (sender_id = 0 OR sender_id IS NULL)) '
                    . 'AND folder_id = %s '
                    . $filter_qry . ' '
                    . 'UNION ALL '
                    . 'SELECT COUNT(mail_id) cnt FROM mail '
                    . 'LEFT JOIN usr_data ON usr_id = sender_id '
                    . 'WHERE user_id = %s '
                    . 'AND ((sender_id > 0 AND sender_id IS NOT NULL AND usr_id IS NOT NULL) OR (sender_id = 0 OR sender_id IS NULL)) '
                    . 'AND folder_id = %s '
                    . $filter_qry . ' '
                    . 'AND m_status = %s';
        
        if (self::$filtered_ids) {
            $queryCount .= ' AND ' . $DIC->database()->in('mail_id', self::$filtered_ids, false, 'integer') . ' ';
        }
        
        $res = $DIC->database()->queryf(
            $queryCount,
            array('integer', 'integer', 'integer', 'integer', 'text'),
            array(self::$userId, self::$folderId, self::$userId, self::$folderId, 'unread')
        );
        $counter = 0;
        while ($cnt_row = $DIC->database()->fetchAssoc($res)) {
            if ($counter == 0) {
                $mails['cnt'] = $cnt_row['cnt'];
            } elseif ($counter == 1) {
                $mails['cnt_unread'] = $cnt_row['cnt'];
            } else {
                break;
            }
            
            ++$counter;
        }
        
        $sortColumn = '';
        if (self::$orderColumn == 'rcp_to' && $DIC->database()->getDBType() == 'oracle') {
            $sortColumn = ", CAST(rcp_to AS VARCHAR2(4000)) SORTCOL";
        }

        $firstname = '';
        if (self::$orderColumn == 'from') {
            // Because of the user id of automatically generated mails and ordering issues we have to do some magic
            $firstname = '
                                ,(CASE
                                        WHEN (usr_id = ' . ANONYMOUS_USER_ID . ') THEN firstname 
                                        ELSE ' . $DIC->database()->quote(ilMail::_getIliasMailerName(), 'text') . '
                                END) fname
                        ';
        }
        
        // item query
        $query = 'SELECT mail.*' . $sortColumn . ' ' . $firstname . ' FROM mail '
               . 'LEFT JOIN usr_data ON usr_id = sender_id '
               . 'AND ((sender_id > 0 AND sender_id IS NOT NULL AND usr_id IS NOT NULL) OR (sender_id = 0 OR sender_id IS NULL)) '
               . 'WHERE user_id = %s '
               . $filter_qry . ' '
               . 'AND folder_id = %s';

        if (self::$filtered_ids) {
            $query .= ' AND ' . $DIC->database()->in('mail_id', self::$filtered_ids, false, 'integer') . ' ';
        }
        
        // order direction
        $orderDirection = '';
        if (in_array(strtolower(self::$orderDirection), array('desc', 'asc'))) {
            $orderDirection = self::$orderDirection;
        } else {
            $orderDirection = 'ASC';
        }
              
        // order column
        if (self::$orderColumn == 'rcp_to' && $DIC->database()->getDBType() == 'oracle') {
            $query .= ' ORDER BY SORTCOL ' . $orderDirection;
        } elseif (self::$orderColumn == 'from') {
            $query .= ' ORDER BY '
                    . ' fname ' . $orderDirection . ', '
                    . ' lastname ' . $orderDirection . ', '
                    . ' login ' . $orderDirection . ', '
                    . ' import_name ' . $orderDirection;
        } elseif (strlen(self::$orderColumn)) {
            if (!in_array(strtolower(self::$orderColumn), array('m_subject', 'send_time', 'rcp_to')) &&
               !$DIC->database()->tableColumnExists('mail', strtolower(self::$orderColumn))) {
                // @todo: Performance problem...
                self::$orderColumn = 'send_time';
            }
            
            $query .= ' ORDER BY ' . strtolower(self::$orderColumn) . ' ' . $orderDirection;
        } else {
            $query .= ' ORDER BY send_time DESC';
        }

        $DIC->database()->setLimit(self::$limit, self::$offset);
        $res = $DIC->database()->queryF(
            $query,
            array('integer', 'integer'),
            array(self::$userId, self::$folderId)
        );
        while ($row = $DIC->database()->fetchAssoc($res)) {
            $row['attachments'] = unserialize(stripslashes($row['attachments']));
            $row['m_type'] = unserialize(stripslashes($row['m_type']));
            $mails['set'][] = $row;
        }
        
        return $mails;
    }
}