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