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_bounce_types':
 278         $op = 'IN';
 279         $values = array($name, $op, $value, $grouping, $wildcard);
 280         self::mailingEventQueryBuilder($query, $values,
 281           'civicrm_mailing_event_bounce',
 282           'bounce_type_id',
 283           ts('Bounce type(s)'),
 284           CRM_Core_PseudoConstant::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array('keyColumn' => 'id', 'labelColumn' => 'name'))
 285         );
 286         return;
 287
 288       case 'mailing_open_status':
 289         self::mailingEventQueryBuilder($query, $values,
 290           'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')
 291         );
 292         return;
 293
 294       case 'mailing_click_status':
 295         self::mailingEventQueryBuilder($query, $values,
 296           'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click')
 297         );
 298         return;
 299
 300       case 'mailing_reply_status':
 301         self::mailingEventQueryBuilder($query, $values,
 302           'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply')
 303         );
 304         return;
 305
 306       case 'mailing_optout':
 307         $valueTitle = array(1 => ts('Opt-out Requests'));
 308         // include opt-out events only
 309         $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
 310         self::mailingEventQueryBuilder($query, $values,
 311           'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
 312           ts('Mailing: '), $valueTitle
 313         );
 314         return;
 315
 316       case 'mailing_unsubscribe':
 317         $valueTitle = array(1 => ts('Unsubscribe Requests'));
 318         // exclude opt-out events
 319         $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
 320         self::mailingEventQueryBuilder($query, $values,
 321           'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
 322           ts('Mailing: '), $valueTitle
 323         );
 324         return;
 325
 326       case 'mailing_forward':
 327         $valueTitle = array('Y' => ts('Forwards'));
 328         // since its a checkbox
 329         $values[2] = 'Y';
 330         self::mailingEventQueryBuilder($query, $values,
 331           'civicrm_mailing_event_forward', 'mailing_forward',
 332           ts('Mailing: '), $valueTitle
 333         );
 334         return;
 335
 336       case 'mailing_job_status':
 337         if (!empty($value)) {
 338           if ($value != 'Scheduled' && $value != 'Canceled') {
 339             $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 340           }
 341           $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 342           $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 343           $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
 344
 345           $query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
 346           $query->_qill[$grouping][]  = "Mailing Job Status IS \"$value\"";
 347         }
 348         return;
 349     }
 350   }
 351
 352   /**
 353    * add all the elements shared between Mailing search and advnaced search
 354    *
 355    * @access public
 356    *
 357    * @return void
 358    * @static
 359    */
 360   static function buildSearchForm(&$form) {
 361     // mailing selectors
 362     $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
 363
 364     if (!empty($mailings)) {
 365       $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
 366         array('id' => 'mailing_id', 'multiple' => 'multiple', 'title' => ts('- select -'))
 367       );
 368     }
 369
 370     CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
 371
 372     $mailingJobStatuses = array(
 373       '' => ts('- select -'),
 374       'Complete' => 'Complete',
 375       'Scheduled' => 'Scheduled',
 376       'Running' => 'Running',
 377       'Canceled' => 'Canceled'
 378     );
 379     $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
 380
 381     $mailingBounceTypes = CRM_Core_PseudoConstant::get(
 382       'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
 383       array('keyColumn' => 'id', 'labelColumn' => 'name')
 384     );
 385     $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
 386       array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'title' => ts('- select -'))
 387     );
 388
 389     // event filters
 390     $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered'));
 391     $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open'));
 392     $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant::yesNoOptions('click'));
 393     $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply'));
 394
 395     $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
 396     $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
 397     $form->add('checkbox', 'mailing_forward', ts('Forwards'));
 398
 399     $form->assign('validCiviMailing', TRUE);
 400   }
 401
 402   static function searchAction(&$row, $id) {}
 403
 404   static function tableNames(&$tables) {
 405   }
 406
 407   /**
 408    * Filter query results based on which contacts do (not) have a particular mailing event in their history.
 409    *
 410    * @param $query
 411    * @param $values
 412    * @param $tableName
 413    * @param $fieldName
 414    * @param $fieldTitle
 415    *
 416    * @return void
 417    */
 418   static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
 419     list($name, $op, $value, $grouping, $wildcard) = $values;
 420
 421     if (empty($value) || $value == 'A') {
 422       // don't do any filtering
 423       return;
 424     }
 425
 426     if ($value == 'Y') {
 427       $query->_where[$grouping][] = $tableName . ".id is not null ";
 428     }
 429     elseif ($value == 'N') {
 430       $query->_where[$grouping][] = $tableName . ".id is null ";
 431     }
 432
 433     if (is_array($value)) {
 434       $query->_where[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
 435       $query->_qill[$grouping][] = "$fieldTitle $op ". implode(', ', array_intersect_key($valueTitles, array_flip($value)));
 436     }
 437     else {
 438       $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
 439     }
 440
 441     $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 442     $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 443     $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 444     $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
 445     $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
 446   }
 447 }
 448