3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2019
33 class CRM_Mailing_BAO_Query
{
36 * Get fields for the mailing & mailing job entity.
40 public static function &getFields() {
41 $mailingFields = CRM_Mailing_BAO_Mailing
::fields();
42 $mailingJobFields = CRM_Mailing_BAO_MailingJob
::fields();
44 // In general it's good to return as many fields as could possibly be searched, but
45 // with the limitation that if the fields do not have unique names they might
46 // clobber other fields :-(
48 'mailing_id' => $mailingFields['id'],
49 'mailing_job_start_date' => $mailingJobFields['mailing_job_start_date'],
55 * If mailings are involved, add the specific Mailing fields
59 public static function select(&$query) {
60 // if Mailing mode add mailing id
61 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_MAILING
) {
62 $query->_select
['mailing_id'] = "civicrm_mailing.id as mailing_id";
63 $query->_element
['mailing_id'] = 1;
65 // base table is contact, so join recipients to it
66 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients']
67 = " INNER JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id ";
69 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
72 if (!empty($query->_returnProperties
['mailing_name'])) {
73 $query->_select
['mailing_name'] = "civicrm_mailing.name as mailing_name";
74 $query->_element
['mailing_name'] = 1;
77 // get mailing subject
78 if (!empty($query->_returnProperties
['mailing_subject'])) {
79 $query->_select
['mailing_subject'] = "civicrm_mailing.subject as mailing_subject";
80 $query->_element
['mailing_subject'] = 1;
84 if (!empty($query->_returnProperties
['mailing_job_status'])) {
85 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job']
86 = " 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 ";
87 $query->_select
['mailing_job_status'] = "civicrm_mailing_job.status as mailing_job_status";
88 $query->_element
['mailing_job_status'] = 1;
92 if (!empty($query->_returnProperties
['email_on_hold'])) {
93 $query->_select
['email_on_hold'] = "recipient_email.on_hold as email_on_hold";
94 $query->_element
['email_on_hold'] = 1;
95 $query->_tables
['recipient_email'] = $query->_whereTables
['recipient_email'] = 1;
98 // get recipient email
99 if (!empty($query->_returnProperties
['email'])) {
100 $query->_select
['email'] = "recipient_email.email as email";
101 $query->_element
['email'] = 1;
102 $query->_tables
['recipient_email'] = $query->_whereTables
['recipient_email'] = 1;
106 if (!empty($query->_returnProperties
['contact_opt_out'])) {
107 $query->_select
['contact_opt_out'] = "contact_a.is_opt_out as contact_opt_out";
108 $query->_element
['contact_opt_out'] = 1;
111 // mailing job end date / completed date
112 if (!empty($query->_returnProperties
['mailing_job_end_date'])) {
113 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job']
114 = " 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 ";
115 $query->_select
['mailing_job_end_date'] = "civicrm_mailing_job.end_date as mailing_job_end_date";
116 $query->_element
['mailing_job_end_date'] = 1;
119 if (!empty($query->_returnProperties
['mailing_recipients_id'])) {
120 $query->_select
['mailing_recipients_id'] = " civicrm_mailing_recipients.id as mailing_recipients_id";
121 $query->_element
['mailing_recipients_id'] = 1;
125 if (CRM_Utils_Array
::value('mailing_campaign_id', $query->_returnProperties
)) {
126 $query->_select
['mailing_campaign_id'] = 'civicrm_mailing.campaign_id as mailing_campaign_id';
127 $query->_element
['mailing_campaign_id'] = 1;
128 $query->_tables
['civicrm_campaign'] = 1;
133 * Get the metadata for fields to be included on the mailing search form.
135 * @throws \CiviCRM_API3_Exception
137 * @todo ideally this would be a trait included on the mailing search & advanced search
138 * rather than a static function.
140 public static function getSearchFieldMetadata() {
142 $metadata = civicrm_api3('Mailing', 'getfields', [])['values'];
143 $metadata = array_merge($metadata, civicrm_api3('MailingJob', 'getfields', [])['values']);
144 return array_intersect_key($metadata, array_flip($fields));
150 public static function where(&$query) {
152 foreach (array_keys($query->_params
) as $id) {
153 if (empty($query->_params
[$id][0])) {
156 if (substr($query->_params
[$id][0], 0, 8) == 'mailing_') {
157 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
158 $query->_useDistinct
= TRUE;
160 $grouping = $query->_params
[$id][3];
161 self
::whereClauseSingle($query->_params
[$id], $query);
167 * @param string $name
171 * @return null|string
173 public static function from($name, $mode, $side) {
177 case 'civicrm_mailing_recipients':
178 $from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id";
181 case 'civicrm_mailing_event_queue':
182 // this is tightly binded so as to do a check WRT actual job recipients ('child' type jobs)
183 $from = " INNER JOIN civicrm_mailing_event_queue ON
184 civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
185 AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'";
188 case 'civicrm_mailing':
189 $from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id ";
192 case 'civicrm_mailing_job':
193 $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.is_test != 1 ";
196 case 'civicrm_mailing_event_bounce':
197 case 'civicrm_mailing_event_delivered':
198 case 'civicrm_mailing_event_opened':
199 case 'civicrm_mailing_event_reply':
200 case 'civicrm_mailing_event_unsubscribe':
201 case 'civicrm_mailing_event_forward':
202 case 'civicrm_mailing_event_trackable_url_open':
203 $from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id";
206 case 'recipient_email':
207 $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_id";
210 case 'civicrm_campaign':
211 $from = " $side JOIN civicrm_campaign ON civicrm_campaign.id = civicrm_mailing.campaign_id";
220 * @param bool $includeCustomFields
224 public static function defaultReturnProperties(
226 $includeCustomFields = TRUE
230 if ($mode & CRM_Contact_BAO_Query
::MODE_MAILING
) {
233 'mailing_campaign_id' => 1,
237 'mailing_subject' => 1,
238 'email_on_hold' => 1,
239 'contact_opt_out' => 1,
240 'mailing_job_status' => 1,
241 'mailing_job_end_date' => 1,
243 'contact_sub_type' => 1,
244 'mailing_recipients_id' => 1,
254 public static function whereClauseSingle(&$values, &$query) {
255 list($name, $op, $value, $grouping, $wildcard) = $values;
259 $selectedMailings = array_flip($value);
260 $value = "(" . implode(',', $value) . ")";
262 $query->_where
[$grouping][] = "civicrm_mailing.id $op $value";
264 $mailings = CRM_Mailing_BAO_Mailing
::getMailingsList();
265 foreach ($selectedMailings as $id => $dnc) {
266 $selectedMailings[$id] = $mailings[$id];
268 $selectedMailings = implode(' or ', $selectedMailings);
270 $query->_qill
[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
271 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
272 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
276 $value = addslashes($value);
282 $query->_where
[$grouping][] = "civicrm_mailing.name $op '$value'";
283 $query->_qill
[$grouping][] = "Mailing Namename $op \"$value\"";
284 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
285 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
289 case 'mailing_date_low':
290 case 'mailing_date_high':
291 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
292 $query->dateQueryBuilder($values,
293 'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
297 case 'mailing_delivery_status':
298 $options = CRM_Mailing_PseudoConstant
::yesNoOptions('delivered');
300 list($name, $op, $value, $grouping, $wildcard) = $values;
302 self
::mailingEventQueryBuilder($query, $values,
303 'civicrm_mailing_event_delivered',
304 'mailing_delivery_status',
305 ts('Mailing Delivery'),
309 elseif ($value == 'N') {
310 $options['Y'] = $options['N'];
311 $values = [$name, $op, 'Y', $grouping, $wildcard];
312 self
::mailingEventQueryBuilder($query, $values,
313 'civicrm_mailing_event_bounce',
314 'mailing_delivery_status',
315 ts('Mailing Delivery'),
321 case 'mailing_bounce_types':
323 $values = [$name, $op, $value, $grouping, $wildcard];
324 self
::mailingEventQueryBuilder($query, $values,
325 'civicrm_mailing_event_bounce',
327 ts('Bounce type(s)'),
328 CRM_Core_PseudoConstant
::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', [
330 'labelColumn' => 'name',
335 case 'mailing_open_status':
336 self
::mailingEventQueryBuilder($query, $values,
337 'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant
::yesNoOptions('open')
341 case 'mailing_click_status':
342 self
::mailingEventQueryBuilder($query, $values,
343 'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant
::yesNoOptions('click')
347 case 'mailing_reply_status':
348 self
::mailingEventQueryBuilder($query, $values,
349 'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant
::yesNoOptions('reply')
353 case 'mailing_optout':
354 $valueTitle = [1 => ts('Opt-out Requests')];
355 // include opt-out events only
356 $query->_where
[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
357 self
::mailingEventQueryBuilder($query, $values,
358 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
359 ts('Mailing: '), $valueTitle
363 case 'mailing_unsubscribe':
364 $valueTitle = [1 => ts('Unsubscribe Requests')];
365 // exclude opt-out events
366 $query->_where
[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
367 self
::mailingEventQueryBuilder($query, $values,
368 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
369 ts('Mailing: '), $valueTitle
373 case 'mailing_forward':
374 $valueTitle = ['Y' => ts('Forwards')];
375 // since its a checkbox
377 self
::mailingEventQueryBuilder($query, $values,
378 'civicrm_mailing_event_forward', 'mailing_forward',
379 ts('Mailing: '), $valueTitle
383 case 'mailing_job_status':
384 if (!empty($value)) {
385 if ($value != 'Scheduled' && $value != 'Canceled') {
386 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
388 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
389 $query->_where
[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
390 $query->_qill
[$grouping][] = "Mailing Job Status IS \"$value\"";
394 case 'mailing_campaign_id':
395 $name = 'campaign_id';
396 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_mailing.$name", $op, $value, 'Integer');
397 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Mailing_DAO_Mailing', $name, $value, $op);
398 $query->_qill
[$grouping][] = ts('Campaign %1 %2', [1 => $op, 2 => $value]);
399 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
400 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
406 * Add all the elements shared between Mailing search and advnaced search.
408 * @param \CRM_Mailing_Form_Search $form
410 * @throws \CiviCRM_API3_Exception
412 public static function buildSearchForm(&$form) {
413 $form->addSearchFieldMetadata(['Mailing' => self
::getSearchFieldMetadata()]);
414 $form->addFormFieldsFromMetadata();
417 $mailings = CRM_Mailing_BAO_Mailing
::getMailingsList();
419 if (!empty($mailings)) {
420 $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
421 ['id' => 'mailing_id', 'multiple' => 'multiple', 'class' => 'crm-select2']
425 CRM_Core_Form_Date
::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
426 $form->addElement('hidden', 'mailing_date_range_error');
427 $form->addFormRule(['CRM_Mailing_BAO_Query', 'formRule'], $form);
429 $mailingJobStatuses = [
430 '' => ts('- select -'),
431 'Complete' => 'Complete',
432 'Scheduled' => 'Scheduled',
433 'Running' => 'Running',
434 'Canceled' => 'Canceled',
436 $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
438 $mailingBounceTypes = CRM_Core_PseudoConstant
::get(
439 'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
440 ['keyColumn' => 'id', 'labelColumn' => 'name']
442 $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
443 ['id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'class' => 'crm-select2']
447 $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant
::yesNoOptions('delivered'), ['allowClear' => TRUE]);
448 $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant
::yesNoOptions('open'), ['allowClear' => TRUE]);
449 $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant
::yesNoOptions('click'), ['allowClear' => TRUE]);
450 $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant
::yesNoOptions('reply'), ['allowClear' => TRUE]);
452 $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
453 $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
454 $form->add('checkbox', 'mailing_forward', ts('Forwards'));
455 // Campaign select field
456 CRM_Campaign_BAO_Campaign
::addCampaignInComponentSearch($form, 'mailing_campaign_id');
458 $form->assign('validCiviMailing', TRUE);
465 public static function searchAction(&$row, $id) {
471 public static function tableNames(&$tables) {
472 if (isset($tables['civicrm_mailing_job'])) {
473 $tables['civicrm_mailing'] = $tables['civicrm_mailing'] ??
1;
474 $tables['civicrm_mailing_recipients'] = $tables['civicrm_mailing_recipients'] ??
1;
479 * Filter query results based on which contacts do (not) have a particular mailing event in their history.
483 * @param string $tableName
484 * @param string $fieldName
487 * @param $valueTitles
489 public static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
490 list($name, $op, $value, $grouping, $wildcard) = $values;
492 if (empty($value) ||
$value == 'A') {
493 // don't do any filtering
498 $query->_where
[$grouping][] = $tableName . ".id is not null ";
500 elseif ($value == 'N') {
501 $query->_where
[$grouping][] = $tableName . ".id is null ";
504 if (is_array($value)) {
505 $query->_where
[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
506 $query->_qill
[$grouping][] = "$fieldTitle $op " . implode(', ', array_intersect_key($valueTitles, array_flip($value)));
509 $query->_qill
[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
512 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
513 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
514 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
515 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
516 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;
520 * Check if the values in the date range are in correct chronological order.
522 * @param array $fields
523 * @param array $files
524 * @param CRM_Core_Form $form
528 public static function formRule($fields, $files, $form) {
531 if (empty($fields['mailing_date_high']) ||
empty($fields['mailing_date_low'])) {
535 CRM_Utils_Rule
::validDateRange($fields, 'mailing_date', $errors, ts('Mailing Date'));
537 return empty($errors) ?
TRUE : $errors;