Collaboration diagram for ilMailBoxQuery:

Static Public Member Functions
static	_getMailBoxListData ()

Static Public Attributes
static int	$folderId = -1

static int	$userId = -1

static int	$limit = 0

static int	$offset = 0

static string	$orderDirection = ''

static string	$orderColumn = ''

static array	$filter = []

static array	$filtered_ids = []

Detailed Description

Mail query class.

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

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

Member Function Documentation

◆ _getMailBoxListData()

static ilMailBoxQuery::_getMailBoxListData ( )

static

Returns: array{set: array[], cnt: int, cnt_unread: int}

Exceptions

Exception

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

References $DIC, ILIAS\LTI\ToolProvider\$key, $orderDirection, $res, ilMail\_getIliasMailerName(), ANONYMOUS_USER_ID, ILIAS\Repository\int(), and ilDBConstants\T_INTEGER.

Referenced by ilMailFolderTableGUI\fetchTableData().

                                                 : array
     {
         global $DIC;
 
         $mails = ['cnt' => 0, 'cnt_unread' => 0, 'set' => []];
 
         $filter = [
             'mail_filter_sender' => 'CONCAT(CONCAT(firstname, lastname), login)',
             'mail_filter_recipients' => 'CONCAT(CONCAT(rcp_to, rcp_cc), rcp_bcc)',
             'mail_filter_subject' => 'm_subject',
             'mail_filter_body' => 'm_message',
             'mail_filter_attach' => '',
         ];
 
         $filter_parts = [];
         if (
             isset(self::$filter['mail_filter']) &&
             is_string(self::$filter['mail_filter']) &&
             self::$filter['mail_filter'] !== ''
         ) {
             foreach ($filter as $key => $column) {
                 if ($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) . ')';
         }
 
         if (isset(self::$filter['mail_filter_only_unread']) && self::$filter['mail_filter_only_unread']) {
             $filter_qry .= ' AND m_status = ' . $DIC->database()->quote('unread', 'text') . ' ';
         }
 
         if (
             isset(self::$filter['mail_filter_only_with_attachments']) &&
             self::$filter['mail_filter_only_with_attachments']
         ) {
             $filter_qry .= ' AND attachments != ' . $DIC->database()->quote(serialize(null), 'text') . ' ';
             $filter_qry .= ' AND attachments != ' . $DIC->database()->quote(serialize([]), 'text') . ' ';
         }
 
         if (isset(self::$filter['mail_filter_only_user_mails']) && self::$filter['mail_filter_only_user_mails']) {
             $filter_qry .= ' AND sender_id != ' . $DIC->database()->quote(ANONYMOUS_USER_ID, ilDBConstants::T_INTEGER) . ' ';
         }
 
         if (isset(self::$filter['period']) && is_array(self::$filter['period'])) {
             $dateFilterParts = [];
 
             if (null !== self::$filter['period']['start']) {
                 $dateFilterParts[] = 'send_time >= ' . $DIC->database()->quote(
                     (new DateTimeImmutable(
                         '@' . self::$filter['period']['start']
                     ))->format('Y-m-d 00:00:00'),
                     'timestamp'
                 );
             }
 
             if (null !== self::$filter['period']['end']) {
                 $dateFilterParts[] = 'send_time <= ' . $DIC->database()->quote(
                     (new DateTimeImmutable(
                         '@' . self::$filter['period']['end']
                     ))->format('Y-m-d 23:59:59'),
                     'timestamp'
                 );
             }
 
             if (count($dateFilterParts) > 0) {
                 $filter_qry .= ' AND (' . implode(' AND ', $dateFilterParts) . ') ';
             }
         }
 
         $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;
 
         if (self::$filtered_ids) {
             $queryCount .= ' AND ' . $DIC->database()->in(
                 'mail_id',
                 self::$filtered_ids,
                 false,
                 'integer'
             ) . ' ';
         }
 
         $queryCount .= ' 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,
             ['integer', 'integer', 'integer', 'integer', 'text'],
             [self::$userId, self::$folderId, self::$userId, self::$folderId, 'unread']
         );
 
         $counter = 0;
         while ($cnt_row = $DIC->database()->fetchAssoc($res)) {
             if ($counter === 0) {
                 $mails['cnt'] = (int) $cnt_row['cnt'];
             } elseif ($counter === 1) {
                 $mails['cnt_unread'] = (int) $cnt_row['cnt'];
             } else {
                 break;
             }
 
             ++$counter;
         }
 
         $sortColumn = '';
         $firstnameSelection = '';
         if (self::$orderColumn === 'from') {
             // Because of the user id of automatically generated mails and ordering issues we have to do some magic
             $firstnameSelection = '
                                 ,(CASE
                                         WHEN (usr_id = ' . ANONYMOUS_USER_ID . ') THEN firstname 
                                         ELSE ' . $DIC->database()->quote(ilMail::_getIliasMailerName(), 'text') . '
                                 END) fname
                         ';
         }
 
         $query = 'SELECT mail.*' . $sortColumn . ' ' . $firstnameSelection . ' 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'
             ) . ' ';
         }
 
         $orderDirection = 'ASC';
         if (in_array(strtolower(self::$orderDirection), ['desc', 'asc'], true)) {
             $orderDirection = self::$orderDirection;
         }
 
         if (self::$orderColumn === 'from') {
             $query .= ' ORDER BY '
                     . ' fname ' . $orderDirection . ', '
                     . ' lastname ' . $orderDirection . ', '
                     . ' login ' . $orderDirection . ', '
                     . ' import_name ' . $orderDirection;
         } elseif (self::$orderColumn !== '') {
             if (
                 !in_array(strtolower(self::$orderColumn), ['m_subject', 'send_time', 'rcp_to'], true) &&
                 !$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,
             ['integer', 'integer'],
             [self::$userId, self::$folderId]
         );
         while ($row = $DIC->database()->fetchAssoc($res)) {
             if (isset($row['attachments'])) {
                 $row['attachments'] = (array) unserialize(
                     stripslashes($row['attachments']),
                     ['allowed_classes' => false]
                 );
             } else {
                 $row['attachments'] = [];
             }
 
             if (isset($row['mail_id'])) {
                 $row['mail_id'] = (int) $row['mail_id'];
             }
 
             if (isset($row['user_id'])) {
                 $row['user_id'] = (int) $row['user_id'];
             }
 
             if (isset($row['folder_id'])) {
                 $row['folder_id'] = (int) $row['folder_id'];
             }
 
             if (isset($row['sender_id'])) {
                 $row['sender_id'] = (int) $row['sender_id'];
             }
 
             $mails['set'][] = $row;
         }
 
         return $mails;
     }