Merge pull request #11017 from seamuslee001/CRM-21212
[civicrm-core.git] / CRM / Mailing / BAO / Query.php
1 <?php
2 /*
3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2017 |
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 and the CiviCRM Licensing Exception. |
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 and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
26 */
27
28 /**
29 *
30 * @package CRM
31 * @copyright CiviCRM LLC (c) 2004-2017
32 */
33 class CRM_Mailing_BAO_Query {
34
35 static $_mailingFields = NULL;
36
37 /**
38 * @return array|null
39 */
40 public static function &getFields() {
41 if (!self::$_mailingFields) {
42 self::$_mailingFields = array();
43 $_mailingFields['mailing_id'] = array(
44 'name' => 'mailing_id',
45 'title' => ts('Mailing ID'),
46 'where' => 'civicrm_mailing.id',
47 );
48 }
49 return self::$_mailingFields;
50 }
51
52 /**
53 * If mailings are involved, add the specific Mailing fields
54 *
55 * @param $query
56 */
57 public static function select(&$query) {
58 // if Mailing mode add mailing id
59 if ($query->_mode & CRM_Contact_BAO_Query::MODE_MAILING) {
60 $query->_select['mailing_id'] = "civicrm_mailing.id as mailing_id";
61 $query->_element['mailing_id'] = 1;
62
63 // base table is contact, so join recipients to it
64 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients']
65 = " INNER JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id ";
66
67 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
68
69 // get mailing name
70 if (!empty($query->_returnProperties['mailing_name'])) {
71 $query->_select['mailing_name'] = "civicrm_mailing.name as mailing_name";
72 $query->_element['mailing_name'] = 1;
73 }
74
75 // get mailing subject
76 if (!empty($query->_returnProperties['mailing_subject'])) {
77 $query->_select['mailing_subject'] = "civicrm_mailing.subject as mailing_subject";
78 $query->_element['mailing_subject'] = 1;
79 }
80
81 // get mailing status
82 if (!empty($query->_returnProperties['mailing_job_status'])) {
83 $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job']
84 = " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
85 $query->_select['mailing_job_status'] = "civicrm_mailing_job.status as mailing_job_status";
86 $query->_element['mailing_job_status'] = 1;
87 }
88
89 // get email on hold
90 if (!empty($query->_returnProperties['email_on_hold'])) {
91 $query->_select['email_on_hold'] = "recipient_email.on_hold as email_on_hold";
92 $query->_element['email_on_hold'] = 1;
93 $query->_tables['recipient_email'] = $query->_whereTables['recipient_email'] = 1;
94 }
95
96 // get recipient email
97 if (!empty($query->_returnProperties['email'])) {
98 $query->_select['email'] = "recipient_email.email as email";
99 $query->_element['email'] = 1;
100 $query->_tables['recipient_email'] = $query->_whereTables['recipient_email'] = 1;
101 }
102
103 // get user opt out
104 if (!empty($query->_returnProperties['contact_opt_out'])) {
105 $query->_select['contact_opt_out'] = "contact_a.is_opt_out as contact_opt_out";
106 $query->_element['contact_opt_out'] = 1;
107 }
108
109 // mailing job end date / completed date
110 if (!empty($query->_returnProperties['mailing_job_end_date'])) {
111 $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job']
112 = " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
113 $query->_select['mailing_job_end_date'] = "civicrm_mailing_job.end_date as mailing_job_end_date";
114 $query->_element['mailing_job_end_date'] = 1;
115 }
116
117 if (!empty($query->_returnProperties['mailing_recipients_id'])) {
118 $query->_select['mailing_recipients_id'] = " civicrm_mailing_recipients.id as mailing_recipients_id";
119 $query->_element['mailing_recipients_id'] = 1;
120 }
121 }
122
123 if (CRM_Utils_Array::value('mailing_campaign_id', $query->_returnProperties)) {
124 $query->_select['mailing_campaign_id'] = 'civicrm_mailing.campaign_id as mailing_campaign_id';
125 $query->_element['mailing_campaign_id'] = 1;
126 $query->_tables['civicrm_campaign'] = 1;
127 }
128 }
129
130 /**
131 * @param $query
132 */
133 public static function where(&$query) {
134 $grouping = NULL;
135 foreach (array_keys($query->_params) as $id) {
136 if (empty($query->_params[$id][0])) {
137 continue;
138 }
139 if (substr($query->_params[$id][0], 0, 8) == 'mailing_') {
140 if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
141 $query->_useDistinct = TRUE;
142 }
143 $grouping = $query->_params[$id][3];
144 self::whereClauseSingle($query->_params[$id], $query);
145 }
146 }
147 }
148
149 /**
150 * @param string $name
151 * @param $mode
152 * @param $side
153 *
154 * @return null|string
155 */
156 public static function from($name, $mode, $side) {
157 $from = NULL;
158
159 switch ($name) {
160 case 'civicrm_mailing_recipients':
161 $from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id";
162 break;
163
164 case 'civicrm_mailing_event_queue':
165 // this is tightly binded so as to do a check WRT actual job recipients ('child' type jobs)
166 $from = " INNER JOIN civicrm_mailing_event_queue ON
167 civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
168 AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'";
169 break;
170
171 case 'civicrm_mailing':
172 $from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id ";
173 break;
174
175 case 'civicrm_mailing_job':
176 $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.is_test != 1 ";
177 break;
178
179 case 'civicrm_mailing_event_bounce':
180 case 'civicrm_mailing_event_delivered':
181 case 'civicrm_mailing_event_opened':
182 case 'civicrm_mailing_event_reply':
183 case 'civicrm_mailing_event_unsubscribe':
184 case 'civicrm_mailing_event_forward':
185 case 'civicrm_mailing_event_trackable_url_open':
186 $from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id";
187 break;
188
189 case 'recipient_email':
190 $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_id";
191 break;
192
193 case 'civicrm_campaign':
194 $from = " $side JOIN civicrm_campaign ON civicrm_campaign.id = civicrm_mailing.campaign_id";
195 break;
196 }
197
198 return $from;
199 }
200
201 /**
202 * @param $mode
203 * @param bool $includeCustomFields
204 *
205 * @return array|null
206 */
207 public static function defaultReturnProperties(
208 $mode,
209 $includeCustomFields = TRUE
210 ) {
211
212 $properties = NULL;
213 if ($mode & CRM_Contact_BAO_Query::MODE_MAILING) {
214 $properties = array(
215 'mailing_id' => 1,
216 'mailing_campaign_id' => 1,
217 'mailing_name' => 1,
218 'sort_name' => 1,
219 'email' => 1,
220 'mailing_subject' => 1,
221 'email_on_hold' => 1,
222 'contact_opt_out' => 1,
223 'mailing_job_status' => 1,
224 'mailing_job_end_date' => 1,
225 'contact_type' => 1,
226 'contact_sub_type' => 1,
227 'mailing_recipients_id' => 1,
228 );
229 }
230 return $properties;
231 }
232
233 /**
234 * @param $values
235 * @param $query
236 */
237 public static function whereClauseSingle(&$values, &$query) {
238 list($name, $op, $value, $grouping, $wildcard) = $values;
239
240 $fields = array();
241 $fields = self::getFields();
242
243 switch ($name) {
244 case 'mailing_id':
245 $selectedMailings = array_flip($value);
246 $value = "(" . implode(',', $value) . ")";
247 $op = 'IN';
248 $query->_where[$grouping][] = "civicrm_mailing.id $op $value";
249
250 $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
251 foreach ($selectedMailings as $id => $dnc) {
252 $selectedMailings[$id] = $mailings[$id];
253 }
254 $selectedMailings = implode(' or ', $selectedMailings);
255
256 $query->_qill[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
257 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
258 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
259 return;
260
261 case 'mailing_name':
262 $value = strtolower(addslashes($value));
263 if ($wildcard) {
264 $value = "%$value%";
265 $op = 'LIKE';
266 }
267
268 // LOWER in query below roughly translates to 'hurt my database without deriving any benefit' See CRM-19811.
269 $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'";
270 $query->_qill[$grouping][] = "Mailing Namename $op \"$value\"";
271 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
272 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
273 return;
274
275 case 'mailing_date':
276 case 'mailing_date_low':
277 case 'mailing_date_high':
278 // process to / from date
279 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
280 $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
281 $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
282 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
283 $query->dateQueryBuilder($values,
284 'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
285 );
286 return;
287
288 case 'mailing_delivery_status':
289 $options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered');
290
291 list($name, $op, $value, $grouping, $wildcard) = $values;
292 if ($value == 'Y') {
293 self::mailingEventQueryBuilder($query, $values,
294 'civicrm_mailing_event_delivered',
295 'mailing_delivery_status',
296 ts('Mailing Delivery'),
297 $options
298 );
299 }
300 elseif ($value == 'N') {
301 $options['Y'] = $options['N'];
302 $values = array($name, $op, 'Y', $grouping, $wildcard);
303 self::mailingEventQueryBuilder($query, $values,
304 'civicrm_mailing_event_bounce',
305 'mailing_delivery_status',
306 ts('Mailing Delivery'),
307 $options
308 );
309 }
310 return;
311
312 case 'mailing_bounce_types':
313 $op = 'IN';
314 $values = array($name, $op, $value, $grouping, $wildcard);
315 self::mailingEventQueryBuilder($query, $values,
316 'civicrm_mailing_event_bounce',
317 'bounce_type_id',
318 ts('Bounce type(s)'),
319 CRM_Core_PseudoConstant::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array(
320 'keyColumn' => 'id',
321 'labelColumn' => 'name',
322 ))
323 );
324 return;
325
326 case 'mailing_open_status':
327 self::mailingEventQueryBuilder($query, $values,
328 'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')
329 );
330 return;
331
332 case 'mailing_click_status':
333 self::mailingEventQueryBuilder($query, $values,
334 'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click')
335 );
336 return;
337
338 case 'mailing_reply_status':
339 self::mailingEventQueryBuilder($query, $values,
340 'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply')
341 );
342 return;
343
344 case 'mailing_optout':
345 $valueTitle = array(1 => ts('Opt-out Requests'));
346 // include opt-out events only
347 $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
348 self::mailingEventQueryBuilder($query, $values,
349 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
350 ts('Mailing: '), $valueTitle
351 );
352 return;
353
354 case 'mailing_unsubscribe':
355 $valueTitle = array(1 => ts('Unsubscribe Requests'));
356 // exclude opt-out events
357 $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
358 self::mailingEventQueryBuilder($query, $values,
359 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
360 ts('Mailing: '), $valueTitle
361 );
362 return;
363
364 case 'mailing_forward':
365 $valueTitle = array('Y' => ts('Forwards'));
366 // since its a checkbox
367 $values[2] = 'Y';
368 self::mailingEventQueryBuilder($query, $values,
369 'civicrm_mailing_event_forward', 'mailing_forward',
370 ts('Mailing: '), $valueTitle
371 );
372 return;
373
374 case 'mailing_job_status':
375 if (!empty($value)) {
376 if ($value != 'Scheduled' && $value != 'Canceled') {
377 $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
378 }
379 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
380 $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
381 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
382
383 $query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
384 $query->_qill[$grouping][] = "Mailing Job Status IS \"$value\"";
385 }
386 return;
387
388 case 'mailing_campaign_id':
389 $name = 'campaign_id';
390 $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_mailing.$name", $op, $value, 'Integer');
391 list($op, $value) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Mailing_DAO_Mailing', $name, $value, $op);
392 $query->_qill[$grouping][] = ts('Campaign %1 %2', array(1 => $op, 2 => $value));
393 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
394 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
395 return;
396 }
397 }
398
399 /**
400 * Add all the elements shared between Mailing search and advnaced search.
401 *
402 *
403 * @param CRM_Core_Form $form
404 */
405 public static function buildSearchForm(&$form) {
406 // mailing selectors
407 $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
408
409 if (!empty($mailings)) {
410 $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
411 array('id' => 'mailing_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
412 );
413 }
414
415 CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
416 $form->addElement('hidden', 'mailing_date_range_error');
417 $form->addFormRule(array('CRM_Mailing_BAO_Query', 'formRule'), $form);
418
419 $mailingJobStatuses = array(
420 '' => ts('- select -'),
421 'Complete' => 'Complete',
422 'Scheduled' => 'Scheduled',
423 'Running' => 'Running',
424 'Canceled' => 'Canceled',
425 );
426 $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
427
428 $mailingBounceTypes = CRM_Core_PseudoConstant::get(
429 'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
430 array('keyColumn' => 'id', 'labelColumn' => 'name')
431 );
432 $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
433 array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'class' => 'crm-select2')
434 );
435
436 // event filters
437 $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered'), array('allowClear' => TRUE));
438 $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open'), array('allowClear' => TRUE));
439 $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant::yesNoOptions('click'), array('allowClear' => TRUE));
440 $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply'), array('allowClear' => TRUE));
441
442 $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
443 $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
444 $form->add('checkbox', 'mailing_forward', ts('Forwards'));
445 // Campaign select field
446 CRM_Campaign_BAO_Campaign::addCampaignInComponentSearch($form, 'mailing_campaign_id');
447
448 $form->assign('validCiviMailing', TRUE);
449 }
450
451 /**
452 * @param $row
453 * @param int $id
454 */
455 public static function searchAction(&$row, $id) {
456 }
457
458 /**
459 * @param $tables
460 */
461 public static function tableNames(&$tables) {
462 }
463
464 /**
465 * Filter query results based on which contacts do (not) have a particular mailing event in their history.
466 *
467 * @param $query
468 * @param $values
469 * @param string $tableName
470 * @param string $fieldName
471 * @param $fieldTitle
472 *
473 * @param $valueTitles
474 */
475 public static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
476 list($name, $op, $value, $grouping, $wildcard) = $values;
477
478 if (empty($value) || $value == 'A') {
479 // don't do any filtering
480 return;
481 }
482
483 if ($value == 'Y') {
484 $query->_where[$grouping][] = $tableName . ".id is not null ";
485 }
486 elseif ($value == 'N') {
487 $query->_where[$grouping][] = $tableName . ".id is null ";
488 }
489
490 if (is_array($value)) {
491 $query->_where[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
492 $query->_qill[$grouping][] = "$fieldTitle $op " . implode(', ', array_intersect_key($valueTitles, array_flip($value)));
493 }
494 else {
495 $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
496 }
497
498 $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
499 $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
500 $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
501 $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
502 $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
503 }
504
505 /**
506 * Check if the values in the date range are in correct chronological order.
507 *
508 * @param array $fields
509 * @param array $files
510 * @param CRM_Core_Form $form
511 *
512 * @return bool|array
513 */
514 public static function formRule($fields, $files, $form) {
515 $errors = array();
516
517 if (empty($fields['mailing_date_high']) || empty($fields['mailing_date_low'])) {
518 return TRUE;
519 }
520
521 CRM_Utils_Rule::validDateRange($fields, 'mailing_date', $errors, ts('Mailing Date'));
522
523 return empty($errors) ? TRUE : $errors;
524 }
525
526 }