ILIAS  Release_5_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
12 class ilMailBoxQuery
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  {
146  $orderDirection = self::$orderDirection;
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 }