CRM/Mailing/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Mailing_BAO_Query {
  36
  37   static $_mailingFields = NULL;
  38
  39   /**
  40    * @return array|null
  41    */
  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',
  49       );
  50     }
  51     return self::$_mailingFields;
  52   }
  53
  54   /**
  55    * If mailings are involved, add the specific Mailing fields
  56    *
  57    * @param $query
  58    *
  59    * @return void
  60    */
  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;
  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   public 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 string $name
 149    * @param $mode
 150    * @param $side
 151    *
 152    * @return null|string
 153    */
 154   public 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   public static function defaultReturnProperties(
 202     $mode,
 203     $includeCustomFields = TRUE
 204   ) {
 205
 206     $properties = NULL;
 207     if ($mode & CRM_Contact_BAO_Query::MODE_MAILING) {
 208       $properties = array(
 209         'mailing_id' => 1,
 210         'mailing_name' => 1,
 211         'sort_name' => 1,
 212         'email' => 1,
 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,
 218         'contact_type' => 1,
 219         'contact_sub_type' => 1,
 220         'mailing_recipients_id' => 1,
 221       );
 222     }
 223     return $properties;
 224   }
 225
 226   /**
 227    * @param $values
 228    * @param $query
 229    */
 230   public static function whereClauseSingle(&$values, &$query) {
 231     list($name, $op, $value, $grouping, $wildcard) = $values;
 232
 233     $fields = array();
 234     $fields = self::getFields();
 235
 236     switch ($name) {
 237       case 'mailing_id':
 238         $selectedMailings = array_flip($value);
 239         $value = "(" . implode(',', $value) . ")";
 240         $op = 'IN';
 241         $query->_where[$grouping][] = "civicrm_mailing.id $op $value";
 242
 243         $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
 244         foreach ($selectedMailings as $id => $dnc) {
 245           $selectedMailings[$id] = $mailings[$id];
 246         }
 247         $selectedMailings = implode(' or ', $selectedMailings);
 248
 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;
 252         return;
 253
 254       case 'mailing_name':
 255         $value = strtolower(addslashes($value));
 256         if ($wildcard) {
 257           $value = "%$value%";
 258           $op = 'LIKE';
 259         }
 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;
 264         return;
 265
 266       case 'mailing_date':
 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'
 276         );
 277         return;
 278
 279       case 'mailing_delivery_status':
 280         $options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered');
 281
 282         list($name, $op, $value, $grouping, $wildcard) = $values;
 283         if ($value == 'Y') {
 284           self::mailingEventQueryBuilder($query, $values,
 285             'civicrm_mailing_event_delivered',
 286             'mailing_delivery_status',
 287             ts('Mailing Delivery'),
 288             $options
 289           );
 290         }
 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'),
 298             $options
 299           );
 300         }
 301         return;
 302
 303       case 'mailing_bounce_types':
 304         $op = 'IN';
 305         $values = array($name, $op, $value, $grouping, $wildcard);
 306         self::mailingEventQueryBuilder($query, $values,
 307           'civicrm_mailing_event_bounce',
 308           'bounce_type_id',
 309           ts('Bounce type(s)'),
 310           CRM_Core_PseudoConstant::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array(
 311               'keyColumn' => 'id',
 312               'labelColumn' => 'name',
 313             ))
 314         );
 315         return;
 316
 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')
 320         );
 321         return;
 322
 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')
 326         );
 327         return;
 328
 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')
 332         );
 333         return;
 334
 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
 342         );
 343         return;
 344
 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
 352         );
 353         return;
 354
 355       case 'mailing_forward':
 356         $valueTitle = array('Y' => ts('Forwards'));
 357         // since its a checkbox
 358         $values[2] = 'Y';
 359         self::mailingEventQueryBuilder($query, $values,
 360           'civicrm_mailing_event_forward', 'mailing_forward',
 361           ts('Mailing: '), $valueTitle
 362         );
 363         return;
 364
 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;
 369           }
 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;
 373
 374           $query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
 375           $query->_qill[$grouping][] = "Mailing Job Status IS \"$value\"";
 376         }
 377         return;
 378     }
 379   }
 380
 381   /**
 382    * Add all the elements shared between Mailing search and advnaced search.
 383    *
 384    *
 385    * @param CRM_Core_Form $form
 386    * @return void
 387    */
 388   public static function buildSearchForm(&$form) {
 389     // mailing selectors
 390     $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
 391
 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')
 395       );
 396     }
 397
 398     CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
 399
 400     $mailingJobStatuses = array(
 401       '' => ts('- select -'),
 402       'Complete' => 'Complete',
 403       'Scheduled' => 'Scheduled',
 404       'Running' => 'Running',
 405       'Canceled' => 'Canceled',
 406     );
 407     $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
 408
 409     $mailingBounceTypes = CRM_Core_PseudoConstant::get(
 410       'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
 411       array('keyColumn' => 'id', 'labelColumn' => 'name')
 412     );
 413     $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
 414       array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'class' => 'crm-select2')
 415     );
 416
 417     // event filters
 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));
 422
 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'));
 426
 427     $form->assign('validCiviMailing', TRUE);
 428   }
 429
 430   /**
 431    * @param $row
 432    * @param int $id
 433    */
 434   public static function searchAction(&$row, $id) {
 435   }
 436
 437   /**
 438    * @param $tables
 439    */
 440   public static function tableNames(&$tables) {
 441   }
 442
 443   /**
 444    * Filter query results based on which contacts do (not) have a particular mailing event in their history.
 445    *
 446    * @param $query
 447    * @param $values
 448    * @param string $tableName
 449    * @param string $fieldName
 450    * @param $fieldTitle
 451    *
 452    * @param $valueTitles
 453    *
 454    * @return void
 455    */
 456   public static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
 457     list($name, $op, $value, $grouping, $wildcard) = $values;
 458
 459     if (empty($value) || $value == 'A') {
 460       // don't do any filtering
 461       return;
 462     }
 463
 464     if ($value == 'Y') {
 465       $query->_where[$grouping][] = $tableName . ".id is not null ";
 466     }
 467     elseif ($value == 'N') {
 468       $query->_where[$grouping][] = $tableName . ".id is null ";
 469     }
 470
 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)));
 474     }
 475     else {
 476       $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
 477     }
 478
 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;
 484   }
 485
 486 }