ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.ilMailBoxQuery.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3
13{
14 public static $folderId = -1;
15 public static $userId = -1;
16 public static $limit = 0;
17 public static $offset = 0;
18 public static $orderDirection = '';
19 public static $orderColumn = '';
20 public static $filter = array();
21 public static $filtered_ids = array();
22
31 public static function _getMailBoxListData()
32 {
36 global $ilDB;
37
38 // initialize array
39 $mails = array('cnt' => 0, 'cnt_unread' => 0, 'set' => array());
40
41 $filter = array(
42 'mail_filter_sender' => 'CONCAT(CONCAT(firstname, lastname), login)',
43 'mail_filter_recipients' => ($ilDB->getDBType() == 'oracle' ?
44 "CONCAT(CONCAT(CAST(rcp_to AS VARCHAR2(4000)), CAST(rcp_cc AS VARCHAR2(4000))), CAST(rcp_bcc AS VARCHAR2(4000))))" :
45 "CONCAT(CONCAT(rcp_to, rcp_cc), rcp_bcc)"),
46 'mail_filter_subject' => 'm_subject',
47 'mail_filter_body' => 'm_message',
48 'mail_filter_attach' => ''
49 );
50 $filter_parts = array();
51 if(isset(self::$filter['mail_filter']) && strlen(self::$filter['mail_filter']))
52 {
53 foreach($filter as $key => $column)
54 {
55 if(strlen($column) && isset(self::$filter[$key]) && (int)self::$filter[$key])
56 {
57 $filter_parts[] = $ilDB->like($column, 'text', '%%'.self::$filter['mail_filter'].'%%', false);
58 }
59 }
60 }
61 $filter_qry = '';
62 if($filter_parts)
63 {
64 $filter_qry = 'AND ('.implode(' OR ', $filter_parts).')';
65 }
66 // count query
67 $queryCount = 'SELECT COUNT(mail_id) cnt FROM mail '
68 . 'LEFT JOIN usr_data ON usr_id = sender_id '
69 . 'WHERE user_id = %s '
70 . '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)) '
71 . 'AND folder_id = %s '
72 . $filter_qry.' '
73 . 'UNION ALL '
74 . 'SELECT COUNT(mail_id) cnt FROM mail '
75 . 'LEFT JOIN usr_data ON usr_id = sender_id '
76 . 'WHERE user_id = %s '
77 . '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)) '
78 . 'AND folder_id = %s '
79 . $filter_qry.' '
80 . 'AND m_status = %s';
81
82 if(self::$filtered_ids)
83 {
84 $queryCount .= ' AND '.$ilDB->in('mail_id', self::$filtered_ids, false, 'integer').' ';
85 }
86
87 $res = $ilDB->queryf(
88 $queryCount,
89 array('integer', 'integer', 'integer', 'integer', 'text'),
90 array(self::$userId, self::$folderId, self::$userId, self::$folderId, 'unread')
91 );
92 $counter = 0;
93 while($cnt_row = $ilDB->fetchAssoc($res))
94 {
95 if($counter == 0)
96 {
97 $mails['cnt'] = $cnt_row['cnt'];
98 }
99 else if($counter == 1)
100 {
101 $mails['cnt_unread'] = $cnt_row['cnt'];
102 }
103 else
104 {
105 break;
106 }
107
108 ++$counter;
109 }
110
111 $sortColumn = '';
112 if(self::$orderColumn == 'rcp_to' && $ilDB->getDBType() == 'oracle')
113 {
114 $sortColumn = ", CAST(rcp_to AS VARCHAR2(4000)) SORTCOL";
115 }
116
117 $firstname = '';
118 if(self::$orderColumn == 'from')
119 {
120 // Because of the user id of automatically generated mails and ordering issues we have to do some magic
121 $firstname = '
122 ,(CASE
123 WHEN (usr_id = '.ANONYMOUS_USER_ID.') THEN firstname
124 ELSE '.$ilDB->quote(ilMail::_getIliasMailerName(), 'text').'
125 END) fname
126 ';
127 }
128
129 // item query
130 $query = 'SELECT mail.*'.$sortColumn.' '.$firstname.' FROM mail '
131 . 'LEFT JOIN usr_data ON usr_id = sender_id '
132 . '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)) '
133 . 'WHERE user_id = %s '
134 . $filter_qry.' '
135 . 'AND folder_id = %s';
136
137 if(self::$filtered_ids)
138 {
139 $query .= ' AND '.$ilDB->in('mail_id', self::$filtered_ids, false, 'integer').' ';
140 }
141
142 // order direction
143 $orderDirection = '';
144 if(in_array(strtolower(self::$orderDirection), array('desc', 'asc')))
145 {
147 }
148 else
149 {
150 $orderDirection = 'ASC';
151 }
152
153 // order column
154 if(self::$orderColumn == 'rcp_to' && $ilDB->getDBType() == 'oracle')
155 {
156 $query .= ' ORDER BY SORTCOL '.$orderDirection;
157 }
158 else if(self::$orderColumn == 'from')
159 {
160 $query .= ' ORDER BY '
161 . ' fname '.$orderDirection.', '
162 . ' lastname '.$orderDirection.', '
163 . ' login '.$orderDirection.', '
164 . ' import_name '.$orderDirection;
165 }
166 else if(strlen(self::$orderColumn))
167 {
168 if(!in_array(strtolower(self::$orderColumn), array('m_subject', 'send_time', 'rcp_to')) &&
169 !$ilDB->tableColumnExists('mail', strtolower(self::$orderColumn)))
170 {
171 // @todo: Performance problem...
172 self::$orderColumn = 'send_time';
173 }
174
175 $query .= ' ORDER BY '.strtolower(self::$orderColumn).' '.$orderDirection;
176 }
177 else
178 {
179 $query .= ' ORDER BY send_time DESC';
180 }
181
182 $ilDB->setLimit(self::$limit, self::$offset);
183 $res = $ilDB->queryF(
184 $query,
185 array('integer', 'integer'),
186 array(self::$userId, self::$folderId)
187 );
188 while($row = $ilDB->fetchAssoc($res))
189 {
190 $row['attachments'] = unserialize(stripslashes($row['attachments']));
191 $row['m_type'] = unserialize(stripslashes($row['m_type']));
192 $mails['set'][] = $row;
193 }
194
195 return $mails;
196 }
197}
Mail query class.
global $ilDB