Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
4 | | CiviCRM version 4.3 | | |
5 | +--------------------------------------------------------------------+ | |
6 | | Copyright CiviCRM LLC (c) 2004-2013 | | |
7 | +--------------------------------------------------------------------+ | |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
10 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
11 | | under the terms of the GNU Affero General Public License | | |
12 | | Version 3, 19 November 2007. | | |
13 | | | | |
14 | | CiviCRM is distributed in the hope that it will be useful, but | | |
15 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
16 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
17 | | See the GNU Affero General Public License for more details. | | |
18 | | | | |
19 | | You should have received a copy of the GNU Affero General Public | | |
20 | | License along with this program; if not, contact CiviCRM LLC | | |
21 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
22 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
23 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
24 | +--------------------------------------------------------------------+ | |
25 | */ | |
26 | ||
27 | /** | |
28 | * | |
29 | * @package CRM | |
30 | * @copyright CiviCRM LLC (c) 2004-2013 | |
31 | * $Id$ | |
32 | * | |
33 | */ | |
34 | class CRM_Mailing_BAO_Query { | |
35 | ||
36 | static $_mailingFields = NULL; | |
37 | ||
38 | static function &getFields() { | |
39 | if (!self::$_mailingFields) { | |
40 | self::$_mailingFields = array(); | |
41 | $_mailingFields['mailing_id'] = array( | |
42 | 'name' => 'mailing_id', | |
43 | 'title' => 'Mailing ID', | |
44 | 'where' => 'civicrm_mailing.id', | |
45 | ); | |
46 | } | |
47 | return self::$_mailingFields; | |
48 | } | |
49 | ||
50 | /** | |
51 | * if mailings are involved, add the specific Mailing fields | |
52 | * | |
53 | * @return void | |
54 | * @access public | |
55 | */ | |
56 | static function select(&$query) { | |
57 | // if Mailing mode add mailing id | |
58 | if ($query->_mode & CRM_Contact_BAO_Query::MODE_MAILING) { | |
59 | $query->_select['mailing_id'] = "civicrm_mailing.id as mailing_id"; | |
60 | $query->_element['mailing_id'] = 1; | |
61 | $query->_tables['civicrm_mailing'] = 1; | |
62 | $query->_whereTables['civicrm_mailing'] = 1; | |
63 | } | |
64 | } | |
65 | ||
66 | static function where(&$query) { | |
67 | $grouping = NULL; | |
68 | foreach (array_keys($query->_params) as $id) { | |
69 | if (!CRM_Utils_Array::value(0, $query->_params[$id])) { | |
70 | continue; | |
71 | } | |
72 | if (substr($query->_params[$id][0], 0, 8) == 'mailing_') { | |
73 | if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) { | |
74 | $query->_useDistinct = TRUE; | |
75 | } | |
76 | $grouping = $query->_params[$id][3]; | |
77 | self::whereClauseSingle($query->_params[$id], $query); | |
78 | } | |
79 | } | |
80 | } | |
81 | ||
82 | static function from($name, $mode, $side) { | |
83 | $from = NULL; | |
84 | switch ($name) { | |
85 | case 'civicrm_mailing_event_queue': | |
86 | $from = " $side JOIN civicrm_mailing_event_queue ON civicrm_mailing_event_queue.contact_id = contact_a.id"; | |
87 | break; | |
88 | ||
89 | case 'civicrm_mailing_job': | |
90 | $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.id = civicrm_mailing_event_queue.job_id"; | |
91 | break; | |
92 | ||
93 | case 'civicrm_mailing': | |
94 | $from = " $side JOIN civicrm_mailing on civicrm_mailing.id = civicrm_mailing_job.mailing_id"; | |
95 | break; | |
96 | ||
97 | case 'civicrm_mailing_event_bounce': | |
98 | case 'civicrm_mailing_event_delivered': | |
99 | case 'civicrm_mailing_event_opened': | |
100 | case 'civicrm_mailing_event_reply': | |
101 | case 'civicrm_mailing_event_unsubscribe': | |
102 | case 'civicrm_mailing_event_forward': | |
103 | case 'civicrm_mailing_event_trackable_url_open': | |
104 | $from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id"; | |
105 | break; | |
106 | } | |
107 | ||
108 | return $from; | |
109 | } | |
110 | ||
111 | static function defaultReturnProperties($mode, | |
112 | $includeCustomFields = TRUE | |
113 | ) { | |
114 | ||
115 | $properties = NULL; | |
116 | if ($mode & CRM_Contact_BAO_Query::MODE_MAILING) { | |
117 | $properties = array('mailing_id' => 1); | |
118 | } | |
119 | return $properties; | |
120 | } | |
121 | ||
122 | static function whereClauseSingle(&$values, &$query) { | |
123 | list($name, $op, $value, $grouping, $wildcard) = $values; | |
124 | ||
125 | $fields = array(); | |
126 | $fields = self::getFields(); | |
127 | switch ($name) { | |
128 | case 'mailing_id': | |
129 | $selectedMailings = array_flip($value); | |
130 | ||
131 | $value = "(" . implode(',', $value) . ")"; | |
132 | $op = 'IN'; | |
133 | $query->_where[$grouping][] = "civicrm_mailing.id $op $value"; | |
134 | ||
135 | $mailings = CRM_Mailing_BAO_Mailing::getMailingsList(); | |
136 | foreach ($selectedMailings as $id => $dnc) { | |
137 | $selectedMailings[$id] = $mailings[$id]; | |
138 | } | |
139 | $selectedMailings = implode(' or ', $selectedMailings); | |
140 | ||
141 | $query->_qill[$grouping][] = "Mailing Name $op \"$selectedMailings\""; | |
142 | $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; | |
143 | $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; | |
144 | $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; | |
145 | return; | |
146 | ||
147 | case 'mailing_name': | |
148 | $value = strtolower( addslashes( $value ) ); | |
149 | if ( $wildcard ) { | |
150 | $value = "%$value%"; | |
151 | $op = 'LIKE'; | |
152 | } | |
153 | $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'"; | |
154 | $query->_qill[$grouping][] = "Mailing Name $op \"$value\""; | |
155 | $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; | |
156 | $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; | |
157 | $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; | |
158 | return; | |
159 | ||
160 | case 'mailing_date': | |
161 | case 'mailing_date_low': | |
162 | case 'mailing_date_high': | |
163 | // process to / from date | |
164 | $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; | |
165 | $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; | |
166 | $query->dateQueryBuilder($values, | |
167 | 'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date' | |
168 | ); | |
169 | return; | |
170 | ||
171 | case 'mailing_delivery_status': | |
172 | $options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered'); | |
173 | ||
174 | list($name, $op, $value, $grouping, $wildcard) = $values; | |
175 | if ($value == 'Y') { | |
176 | self::mailingEventQueryBuilder($query, $values, | |
177 | 'civicrm_mailing_event_delivered', | |
178 | 'mailing_delivery_status', | |
179 | ts('Mailing Delivery'), | |
180 | $options | |
181 | ); | |
182 | } | |
183 | elseif ($value == 'N') { | |
184 | $options['Y'] = $options['N']; | |
185 | $values = array($name, $op, 'Y', $grouping, $wildcard); | |
186 | self::mailingEventQueryBuilder($query, $values, | |
187 | 'civicrm_mailing_event_bounce', | |
188 | 'mailing_delivery_status', | |
189 | ts('Mailing Delivery'), | |
190 | $options | |
191 | ); | |
192 | } | |
193 | return; | |
194 | ||
195 | case 'mailing_open_status': | |
196 | self::mailingEventQueryBuilder($query, $values, | |
197 | 'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open') | |
198 | ); | |
199 | return; | |
200 | ||
201 | case 'mailing_click_status': | |
202 | self::mailingEventQueryBuilder($query, $values, | |
203 | 'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click') | |
204 | ); | |
205 | return; | |
206 | ||
207 | case 'mailing_reply_status': | |
208 | self::mailingEventQueryBuilder($query, $values, | |
209 | 'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply') | |
210 | ); | |
211 | return; | |
212 | ||
213 | case 'mailing_optout': | |
214 | $valueTitle = array(1 => ts('Opt-out Requests')); | |
215 | // include opt-out events only | |
216 | $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1"; | |
217 | self::mailingEventQueryBuilder($query, $values, | |
218 | 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe', | |
219 | ts('Mailing: '), $valueTitle | |
220 | ); | |
221 | return; | |
222 | ||
223 | case 'mailing_unsubscribe': | |
224 | $valueTitle = array(1 => ts('Unsubscribe Requests')); | |
225 | // exclude opt-out events | |
226 | $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0"; | |
227 | self::mailingEventQueryBuilder($query, $values, | |
228 | 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe', | |
229 | ts('Mailing: '), $valueTitle | |
230 | ); | |
231 | return; | |
232 | ||
233 | case 'mailing_forward': | |
234 | $valueTitle = array('Y' => ts('Forwards')); | |
235 | // since its a checkbox | |
236 | $values[2] = 'Y'; | |
237 | self::mailingEventQueryBuilder($query, $values, | |
238 | 'civicrm_mailing_event_forward', 'mailing_forward', | |
239 | ts('Mailing: '), $valueTitle | |
240 | ); | |
241 | return; | |
242 | } | |
243 | } | |
244 | ||
245 | /** | |
246 | * add all the elements shared between Mailing search and advnaced search | |
247 | * | |
248 | * @access public | |
249 | * | |
250 | * @return void | |
251 | * @static | |
252 | */ | |
253 | static function buildSearchForm(&$form) { | |
254 | // mailing selectors | |
255 | $mailings = CRM_Mailing_BAO_Mailing::getMailingsList(); | |
256 | ||
257 | if (!empty($mailings)) { | |
258 | $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE, | |
259 | array('id' => 'mailing_id', 'multiple' => 'multiple', 'title' => ts('- select -')) | |
260 | ); | |
261 | } | |
262 | ||
263 | CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE, FALSE); | |
264 | ||
265 | // event filters | |
266 | $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered')); | |
267 | $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')); | |
268 | $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant::yesNoOptions('click')); | |
269 | $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply')); | |
270 | ||
271 | $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests')); | |
272 | $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests')); | |
273 | $form->add('checkbox', 'mailing_forward', ts('Forwards')); | |
274 | ||
275 | $form->assign('validCiviMailing', TRUE); | |
276 | $form->addFormRule(array('CRM_Mailing_BAO_Query', 'formRule'), $form); | |
277 | } | |
278 | ||
279 | /** | |
280 | * global form rule | |
281 | * | |
282 | * @param array $fields the input form values | |
283 | * @param array $files the uploaded files if any | |
284 | * @param array $options additional user data | |
285 | * | |
286 | * @return true if no errors, else array of errors | |
287 | * @access public | |
288 | * @static | |
289 | */ | |
290 | static function formRule($fields, $files, $self) { | |
291 | $errors = array(); | |
292 | // if an event filter is specified, then a mailing selector must also be specified | |
293 | if ((CRM_Utils_Array::value('mailing_delivery_status', $fields) || | |
294 | CRM_Utils_Array::value('mailing_open_status', $fields) || | |
295 | CRM_Utils_Array::value('mailing_click_status', $fields) || | |
296 | CRM_Utils_Array::value('mailing_reply_status', $fields) | |
297 | ) && | |
298 | (!CRM_Utils_Array::value('mailing_id', $fields) && | |
299 | !CRM_Utils_Array::value('mailing_date_low', $fields) && | |
300 | !CRM_Utils_Array::value('mailing_date_high', $fields) | |
301 | ) | |
302 | ) { | |
303 | $errors['mailing_id'] = ts('Must specify mailing name or date'); | |
304 | // Keep search form opened in case of form rule. | |
305 | if (is_a($self, 'CRM_Contact_Form_Search_Advanced') && !isset(CRM_Contact_BAO_Query::$_openedPanes['Mailings'])) { | |
306 | CRM_Contact_BAO_Query::$_openedPanes['Mailings'] = TRUE; | |
307 | $self->assign('openedPanes', CRM_Contact_BAO_Query::$_openedPanes); | |
308 | } | |
309 | } | |
310 | return $errors; | |
311 | } | |
312 | ||
313 | static function addShowHide(&$showHide) { | |
314 | $showHide->addHide('MailingForm'); | |
315 | $showHide->addShow('MailingForm_show'); | |
316 | } | |
317 | ||
318 | static function searchAction(&$row, $id) {} | |
319 | ||
320 | static function tableNames(&$tables) {} | |
321 | ||
322 | /** | |
323 | * Filter query results based on which contacts do (not) have a particular mailing event in their history. | |
324 | * | |
325 | * @param $query | |
326 | * @param $values | |
327 | * @param $tableName | |
328 | * @param $fieldName | |
329 | * @param $fieldTitle | |
330 | * | |
331 | * @return void | |
332 | */ | |
333 | static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) { | |
334 | list($name, $op, $value, $grouping, $wildcard) = $values; | |
335 | ||
336 | if (empty($value) || $value == 'A') { | |
337 | // don't do any filtering | |
338 | return; | |
339 | } | |
340 | ||
341 | if ($value == 'Y') { | |
342 | $query->_where[$grouping][] = $tableName . ".id is not null "; | |
343 | } | |
344 | elseif ($value == 'N') { | |
345 | $query->_where[$grouping][] = $tableName . ".id is null "; | |
346 | } | |
347 | ||
348 | $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value]; | |
349 | $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; | |
350 | $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1; | |
351 | } | |
352 | } | |
353 |