Collaboration diagram for ilMailBoxQuery:

Static Public Member Functions
static	_getMailBoxListData ()
	_getMailBoxListData More...

Static Public Attributes
static	$folderId = -1

static	$userId = -1

static	$limit = 0

static	$offset = 0

static	$orderDirection = ''

static	$orderColumn = ''

static	$filter = array()

static	$filtered_ids = array()

Detailed Description

Mail query class.

Author: Michael Jansen mjans.nosp@m.en@d.nosp@m.ataba.nosp@m.y.de

Version: $Id$

Definition at line 12 of file class.ilMailBoxQuery.php.

Member Function Documentation

◆ _getMailBoxListData()

static ilMailBoxQuery::_getMailBoxListData ( )

static

_getMailBoxListData

public

Returns: array Array of mails

Definition at line 31 of file class.ilMailBoxQuery.php.

References $column, $counter, $DIC, $filter, $key, $orderDirection, $query, $res, $row, and array.

Referenced by ilMailFolderTableGUI\fetchTableData().

     {
         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;
     }