CRM/Mailing/BAO/Query.php

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