CRM/Mailing/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  31  * $Id$
  32  *
  33  */
  34 class CRM_Mailing_BAO_Query {
  35
  36   static $_mailingFields = NULL;
  37
  38   /**
  39    * @return array|null
  40    */
  41   static function &getFields() {
  42     if (!self::$_mailingFields) {
  43       self::$_mailingFields = array();
  44       $_mailingFields['mailing_id'] = array(
  45         'name' => 'mailing_id',
  46         'title' => 'Mailing ID',
  47         'where' => 'civicrm_mailing.id',
  48       );
  49     }
  50     return self::$_mailingFields;
  51   }
  52
  53   /**
  54    * if mailings are involved, add the specific Mailing fields
  55    *
  56    * @param $query
  57    *
  58    * @return void
  59    * @access public
  60    */
  61   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;
  66
  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 ";
  70
  71       $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
  72
  73       // get mailing name
  74       if (!empty($query->_returnProperties['mailing_name'])) {
  75         $query->_select['mailing_name']  = "civicrm_mailing.name as mailing_name";
  76         $query->_element['mailing_name'] = 1;
  77       }
  78
  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;
  83       }
  84
  85       // get mailing status
  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;
  91       }
  92
  93       // get email on hold
  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;
  98       }
  99
 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;
 105       }
 106
 107       // get user opt out
 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;
 111       }
 112
 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;
 119       }
 120
 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;
 124       }
 125     }
 126   }
 127
 128   /**
 129    * @param $query
 130    */
 131   static function where(&$query) {
 132     $grouping = NULL;
 133     foreach (array_keys($query->_params) as $id) {
 134       if (empty($query->_params[$id][0])) {
 135         continue;
 136       }
 137       if (substr($query->_params[$id][0], 0, 8) == 'mailing_') {
 138         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
 139           $query->_useDistinct = TRUE;
 140         }
 141         $grouping = $query->_params[$id][3];
 142         self::whereClauseSingle($query->_params[$id], $query);
 143       }
 144     }
 145   }
 146
 147   /**
 148    * @param $name
 149    * @param $mode
 150    * @param $side
 151    *
 152    * @return null|string
 153    */
 154   static function from($name, $mode, $side) {
 155     $from = NULL;
 156
 157     switch ($name) {
 158       case 'civicrm_mailing_recipients':
 159         $from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id";
 160         break;
 161
 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'";
 167         break;
 168
 169       case 'civicrm_mailing':
 170         $from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id ";
 171         break;
 172
 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 ";
 175         break;
 176
 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";
 185         break;
 186
 187       case 'recipient_email':
 188         $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_id";
 189         break;
 190     }
 191
 192     return $from;
 193   }
 194
 195   /**
 196    * @param $mode
 197    * @param bool $includeCustomFields
 198    *
 199    * @return array|null
 200    */
 201   static function defaultReturnProperties($mode,
 202     $includeCustomFields = TRUE
 203   ) {
 204
 205     $properties = NULL;
 206     if ($mode & CRM_Contact_BAO_Query::MODE_MAILING) {
 207       $properties = array(
 208         'mailing_id' => 1,
 209         'mailing_name' => 1,
 210         'sort_name' => 1,
 211         'email' => 1,
 212         'mailing_subject' => 1,
 213         'email_on_hold' => 1,
 214         'contact_opt_out' => 1,
 215         'mailing_job_status' => 1,
 216         'mailing_job_end_date' => 1,
 217         'contact_type' => 1,
 218         'contact_sub_type' => 1,
 219         'mailing_recipients_id' => 1
 220       );
 221     }
 222     return $properties;
 223   }
 224
 225   /**
 226    * @param $values
 227    * @param $query
 228    */
 229   static function whereClauseSingle(&$values, &$query) {
 230     list($name, $op, $value, $grouping, $wildcard) = $values;
 231
 232     $fields = array();
 233     $fields = self::getFields();
 234
 235     switch ($name) {
 236       case 'mailing_id':
 237         $selectedMailings = array_flip($value);
 238         $value = "(" . implode(',', $value) . ")";
 239         $op = 'IN';
 240         $query->_where[$grouping][] = "civicrm_mailing.id $op $value";
 241
 242         $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
 243         foreach ($selectedMailings as $id => $dnc) {
 244           $selectedMailings[$id] = $mailings[$id];
 245         }
 246         $selectedMailings = implode(' or ', $selectedMailings);
 247
 248         $query->_qill[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
 249         $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 250         $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
 251         return;
 252
 253       case 'mailing_name':
 254         $value = strtolower( addslashes( $value ) );
 255         if ( $wildcard ) {
 256           $value = "%$value%";
 257           $op    = 'LIKE';
 258         }
 259         $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'";
 260         $query->_qill[$grouping][]  = "Mailing Namename $op \"$value\"";
 261         $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 262         $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
 263         return;
 264
 265       case 'mailing_date':
 266       case 'mailing_date_low':
 267       case 'mailing_date_high':
 268         // process to / from date
 269         $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 270         $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 271         $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 272         $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
 273         $query->dateQueryBuilder($values,
 274           'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
 275         );
 276         return;
 277
 278       case 'mailing_delivery_status':
 279         $options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered');
 280
 281         list($name, $op, $value, $grouping, $wildcard) = $values;
 282         if ($value == 'Y') {
 283           self::mailingEventQueryBuilder($query, $values,
 284             'civicrm_mailing_event_delivered',
 285             'mailing_delivery_status',
 286             ts('Mailing Delivery'),
 287             $options
 288           );
 289         }
 290         elseif ($value == 'N') {
 291           $options['Y'] = $options['N'];
 292           $values = array($name, $op, 'Y', $grouping, $wildcard);
 293           self::mailingEventQueryBuilder($query, $values,
 294             'civicrm_mailing_event_bounce',
 295             'mailing_delivery_status',
 296             ts('Mailing Delivery'),
 297             $options
 298           );
 299         }
 300         return;
 301
 302       case 'mailing_bounce_types':
 303         $op = 'IN';
 304         $values = array($name, $op, $value, $grouping, $wildcard);
 305         self::mailingEventQueryBuilder($query, $values,
 306           'civicrm_mailing_event_bounce',
 307           'bounce_type_id',
 308           ts('Bounce type(s)'),
 309           CRM_Core_PseudoConstant::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array('keyColumn' => 'id', 'labelColumn' => 'name'))
 310         );
 311         return;
 312
 313       case 'mailing_open_status':
 314         self::mailingEventQueryBuilder($query, $values,
 315           'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')
 316         );
 317         return;
 318
 319       case 'mailing_click_status':
 320         self::mailingEventQueryBuilder($query, $values,
 321           'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click')
 322         );
 323         return;
 324
 325       case 'mailing_reply_status':
 326         self::mailingEventQueryBuilder($query, $values,
 327           'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply')
 328         );
 329         return;
 330
 331       case 'mailing_optout':
 332         $valueTitle = array(1 => ts('Opt-out Requests'));
 333         // include opt-out events only
 334         $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
 335         self::mailingEventQueryBuilder($query, $values,
 336           'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
 337           ts('Mailing: '), $valueTitle
 338         );
 339         return;
 340
 341       case 'mailing_unsubscribe':
 342         $valueTitle = array(1 => ts('Unsubscribe Requests'));
 343         // exclude opt-out events
 344         $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
 345         self::mailingEventQueryBuilder($query, $values,
 346           'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
 347           ts('Mailing: '), $valueTitle
 348         );
 349         return;
 350
 351       case 'mailing_forward':
 352         $valueTitle = array('Y' => ts('Forwards'));
 353         // since its a checkbox
 354         $values[2] = 'Y';
 355         self::mailingEventQueryBuilder($query, $values,
 356           'civicrm_mailing_event_forward', 'mailing_forward',
 357           ts('Mailing: '), $valueTitle
 358         );
 359         return;
 360
 361       case 'mailing_job_status':
 362         if (!empty($value)) {
 363           if ($value != 'Scheduled' && $value != 'Canceled') {
 364             $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 365           }
 366           $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 367           $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 368           $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
 369
 370           $query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
 371           $query->_qill[$grouping][]  = "Mailing Job Status IS \"$value\"";
 372         }
 373         return;
 374     }
 375   }
 376
 377   /**
 378    * add all the elements shared between Mailing search and advnaced search
 379    *
 380    * @access public
 381    *
 382    * @param $form CRM_Core_Form
 383    * @return void
 384    * @static
 385    */
 386   static function buildSearchForm(&$form) {
 387     // mailing selectors
 388     $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
 389
 390     if (!empty($mailings)) {
 391       $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
 392         array('id' => 'mailing_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
 393       );
 394     }
 395
 396     CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
 397
 398     $mailingJobStatuses = array(
 399       '' => ts('- select -'),
 400       'Complete' => 'Complete',
 401       'Scheduled' => 'Scheduled',
 402       'Running' => 'Running',
 403       'Canceled' => 'Canceled'
 404     );
 405     $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
 406
 407     $mailingBounceTypes = CRM_Core_PseudoConstant::get(
 408       'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
 409       array('keyColumn' => 'id', 'labelColumn' => 'name')
 410     );
 411     $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
 412       array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'class' => 'crm-select2')
 413     );
 414
 415     // event filters
 416     $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered'), array('allowClear' => TRUE));
 417     $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open'), array('allowClear' => TRUE));
 418     $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant::yesNoOptions('click'), array('allowClear' => TRUE));
 419     $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply'), array('allowClear' => TRUE));
 420
 421     $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
 422     $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
 423     $form->add('checkbox', 'mailing_forward', ts('Forwards'));
 424
 425     $form->assign('validCiviMailing', TRUE);
 426   }
 427
 428   /**
 429    * @param $row
 430    * @param $id
 431    */
 432   static function searchAction(&$row, $id) {}
 433
 434   /**
 435    * @param $tables
 436    */
 437   static function tableNames(&$tables) {
 438   }
 439
 440   /**
 441    * Filter query results based on which contacts do (not) have a particular mailing event in their history.
 442    *
 443    * @param $query
 444    * @param $values
 445    * @param $tableName
 446    * @param $fieldName
 447    * @param $fieldTitle
 448    *
 449    * @param $valueTitles
 450    *
 451    * @return void
 452    */
 453   static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
 454     list($name, $op, $value, $grouping, $wildcard) = $values;
 455
 456     if (empty($value) || $value == 'A') {
 457       // don't do any filtering
 458       return;
 459     }
 460
 461     if ($value == 'Y') {
 462       $query->_where[$grouping][] = $tableName . ".id is not null ";
 463     }
 464     elseif ($value == 'N') {
 465       $query->_where[$grouping][] = $tableName . ".id is null ";
 466     }
 467
 468     if (is_array($value)) {
 469       $query->_where[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
 470       $query->_qill[$grouping][] = "$fieldTitle $op ". implode(', ', array_intersect_key($valueTitles, array_flip($value)));
 471     }
 472     else {
 473       $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
 474     }
 475
 476     $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 477     $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 478     $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 479     $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
 480     $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
 481   }
 482 }
 483