3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
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-2015
35 class CRM_Mailing_BAO_Query
{
37 static $_mailingFields = NULL;
42 public static function &getFields() {
43 if (!self
::$_mailingFields) {
44 self
::$_mailingFields = array();
45 $_mailingFields['mailing_id'] = array(
46 'name' => 'mailing_id',
47 'title' => 'Mailing ID',
48 'where' => 'civicrm_mailing.id',
51 return self
::$_mailingFields;
55 * If mailings are involved, add the specific Mailing fields
61 public static function select(&$query) {
62 // if Mailing mode add mailing id
63 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_MAILING
) {
64 $query->_select
['mailing_id'] = "civicrm_mailing.id as mailing_id";
65 $query->_element
['mailing_id'] = 1;
67 // base table is contact, so join recipients to it
68 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients']
69 = " INNER JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id ";
71 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
74 if (!empty($query->_returnProperties
['mailing_name'])) {
75 $query->_select
['mailing_name'] = "civicrm_mailing.name as mailing_name";
76 $query->_element
['mailing_name'] = 1;
79 // get mailing subject
80 if (!empty($query->_returnProperties
['mailing_subject'])) {
81 $query->_select
['mailing_subject'] = "civicrm_mailing.subject as mailing_subject";
82 $query->_element
['mailing_subject'] = 1;
86 if (!empty($query->_returnProperties
['mailing_job_status'])) {
87 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job']
88 = " 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 ";
89 $query->_select
['mailing_job_status'] = "civicrm_mailing_job.status as mailing_job_status";
90 $query->_element
['mailing_job_status'] = 1;
94 if (!empty($query->_returnProperties
['email_on_hold'])) {
95 $query->_select
['email_on_hold'] = "recipient_email.on_hold as email_on_hold";
96 $query->_element
['email_on_hold'] = 1;
97 $query->_tables
['recipient_email'] = $query->_whereTables
['recipient_email'] = 1;
100 // get recipient email
101 if (!empty($query->_returnProperties
['email'])) {
102 $query->_select
['email'] = "recipient_email.email as email";
103 $query->_element
['email'] = 1;
104 $query->_tables
['recipient_email'] = $query->_whereTables
['recipient_email'] = 1;
108 if (!empty($query->_returnProperties
['contact_opt_out'])) {
109 $query->_select
['contact_opt_out'] = "contact_a.is_opt_out as contact_opt_out";
110 $query->_element
['contact_opt_out'] = 1;
113 // mailing job end date / completed date
114 if (!empty($query->_returnProperties
['mailing_job_end_date'])) {
115 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job']
116 = " 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 ";
117 $query->_select
['mailing_job_end_date'] = "civicrm_mailing_job.end_date as mailing_job_end_date";
118 $query->_element
['mailing_job_end_date'] = 1;
121 if (!empty($query->_returnProperties
['mailing_recipients_id'])) {
122 $query->_select
['mailing_recipients_id'] = " civicrm_mailing_recipients.id as mailing_recipients_id";
123 $query->_element
['mailing_recipients_id'] = 1;
131 public static function where(&$query) {
133 foreach (array_keys($query->_params
) as $id) {
134 if (empty($query->_params
[$id][0])) {
137 if (substr($query->_params
[$id][0], 0, 8) == 'mailing_') {
138 if ($query->_mode
== CRM_Contact_BAO_QUERY
::MODE_CONTACTS
) {
139 $query->_useDistinct
= TRUE;
141 $grouping = $query->_params
[$id][3];
142 self
::whereClauseSingle($query->_params
[$id], $query);
148 * @param string $name
152 * @return null|string
154 public static function from($name, $mode, $side) {
158 case 'civicrm_mailing_recipients':
159 $from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id";
162 case 'civicrm_mailing_event_queue':
163 // this is tightly binded so as to do a check WRT actual job recipients ('child' type jobs)
164 $from = " INNER JOIN civicrm_mailing_event_queue ON
165 civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
166 AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'";
169 case 'civicrm_mailing':
170 $from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id ";
173 case 'civicrm_mailing_job':
174 $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.is_test != 1 ";
177 case 'civicrm_mailing_event_bounce':
178 case 'civicrm_mailing_event_delivered':
179 case 'civicrm_mailing_event_opened':
180 case 'civicrm_mailing_event_reply':
181 case 'civicrm_mailing_event_unsubscribe':
182 case 'civicrm_mailing_event_forward':
183 case 'civicrm_mailing_event_trackable_url_open':
184 $from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id";
187 case 'recipient_email':
188 $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_id";
197 * @param bool $includeCustomFields
201 public static function defaultReturnProperties(
203 $includeCustomFields = TRUE
207 if ($mode & CRM_Contact_BAO_Query
::MODE_MAILING
) {
213 'mailing_subject' => 1,
214 'email_on_hold' => 1,
215 'contact_opt_out' => 1,
216 'mailing_job_status' => 1,
217 'mailing_job_end_date' => 1,
219 'contact_sub_type' => 1,
220 'mailing_recipients_id' => 1,
230 public static function whereClauseSingle(&$values, &$query) {
231 list($name, $op, $value, $grouping, $wildcard) = $values;
234 $fields = self
::getFields();
238 $selectedMailings = array_flip($value);
239 $value = "(" . implode(',', $value) . ")";
241 $query->_where
[$grouping][] = "civicrm_mailing.id $op $value";
243 $mailings = CRM_Mailing_BAO_Mailing
::getMailingsList();
244 foreach ($selectedMailings as $id => $dnc) {
245 $selectedMailings[$id] = $mailings[$id];
247 $selectedMailings = implode(' or ', $selectedMailings);
249 $query->_qill
[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
250 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
251 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
255 $value = strtolower(addslashes($value));
260 $query->_where
[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'";
261 $query->_qill
[$grouping][] = "Mailing Namename $op \"$value\"";
262 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
263 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
267 case 'mailing_date_low':
268 case 'mailing_date_high':
269 // process to / from date
270 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
271 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
272 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
273 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
274 $query->dateQueryBuilder($values,
275 'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
279 case 'mailing_delivery_status':
280 $options = CRM_Mailing_PseudoConstant
::yesNoOptions('delivered');
282 list($name, $op, $value, $grouping, $wildcard) = $values;
284 self
::mailingEventQueryBuilder($query, $values,
285 'civicrm_mailing_event_delivered',
286 'mailing_delivery_status',
287 ts('Mailing Delivery'),
291 elseif ($value == 'N') {
292 $options['Y'] = $options['N'];
293 $values = array($name, $op, 'Y', $grouping, $wildcard);
294 self
::mailingEventQueryBuilder($query, $values,
295 'civicrm_mailing_event_bounce',
296 'mailing_delivery_status',
297 ts('Mailing Delivery'),
303 case 'mailing_bounce_types':
305 $values = array($name, $op, $value, $grouping, $wildcard);
306 self
::mailingEventQueryBuilder($query, $values,
307 'civicrm_mailing_event_bounce',
309 ts('Bounce type(s)'),
310 CRM_Core_PseudoConstant
::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array(
312 'labelColumn' => 'name',
317 case 'mailing_open_status':
318 self
::mailingEventQueryBuilder($query, $values,
319 'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant
::yesNoOptions('open')
323 case 'mailing_click_status':
324 self
::mailingEventQueryBuilder($query, $values,
325 'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant
::yesNoOptions('click')
329 case 'mailing_reply_status':
330 self
::mailingEventQueryBuilder($query, $values,
331 'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant
::yesNoOptions('reply')
335 case 'mailing_optout':
336 $valueTitle = array(1 => ts('Opt-out Requests'));
337 // include opt-out events only
338 $query->_where
[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
339 self
::mailingEventQueryBuilder($query, $values,
340 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
341 ts('Mailing: '), $valueTitle
345 case 'mailing_unsubscribe':
346 $valueTitle = array(1 => ts('Unsubscribe Requests'));
347 // exclude opt-out events
348 $query->_where
[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
349 self
::mailingEventQueryBuilder($query, $values,
350 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
351 ts('Mailing: '), $valueTitle
355 case 'mailing_forward':
356 $valueTitle = array('Y' => ts('Forwards'));
357 // since its a checkbox
359 self
::mailingEventQueryBuilder($query, $values,
360 'civicrm_mailing_event_forward', 'mailing_forward',
361 ts('Mailing: '), $valueTitle
365 case 'mailing_job_status':
366 if (!empty($value)) {
367 if ($value != 'Scheduled' && $value != 'Canceled') {
368 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
370 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
371 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
372 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
374 $query->_where
[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
375 $query->_qill
[$grouping][] = "Mailing Job Status IS \"$value\"";
382 * Add all the elements shared between Mailing search and advnaced search.
385 * @param CRM_Core_Form $form
388 public static function buildSearchForm(&$form) {
390 $mailings = CRM_Mailing_BAO_Mailing
::getMailingsList();
392 if (!empty($mailings)) {
393 $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
394 array('id' => 'mailing_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
398 CRM_Core_Form_Date
::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
400 $mailingJobStatuses = array(
401 '' => ts('- select -'),
402 'Complete' => 'Complete',
403 'Scheduled' => 'Scheduled',
404 'Running' => 'Running',
405 'Canceled' => 'Canceled',
407 $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
409 $mailingBounceTypes = CRM_Core_PseudoConstant
::get(
410 'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
411 array('keyColumn' => 'id', 'labelColumn' => 'name')
413 $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
414 array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'class' => 'crm-select2')
418 $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant
::yesNoOptions('delivered'), array('allowClear' => TRUE));
419 $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant
::yesNoOptions('open'), array('allowClear' => TRUE));
420 $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant
::yesNoOptions('click'), array('allowClear' => TRUE));
421 $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant
::yesNoOptions('reply'), array('allowClear' => TRUE));
423 $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
424 $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
425 $form->add('checkbox', 'mailing_forward', ts('Forwards'));
427 $form->assign('validCiviMailing', TRUE);
434 public static function searchAction(&$row, $id) {
440 public static function tableNames(&$tables) {
444 * Filter query results based on which contacts do (not) have a particular mailing event in their history.
448 * @param string $tableName
449 * @param string $fieldName
452 * @param $valueTitles
456 public static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
457 list($name, $op, $value, $grouping, $wildcard) = $values;
459 if (empty($value) ||
$value == 'A') {
460 // don't do any filtering
465 $query->_where
[$grouping][] = $tableName . ".id is not null ";
467 elseif ($value == 'N') {
468 $query->_where
[$grouping][] = $tableName . ".id is null ";
471 if (is_array($value)) {
472 $query->_where
[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
473 $query->_qill
[$grouping][] = "$fieldTitle $op " . implode(', ', array_intersect_key($valueTitles, array_flip($value)));
476 $query->_qill
[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
479 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
480 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
481 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
482 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
483 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;