CRM/Mailing/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   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       $query->_tables['civicrm_mailing'] = 1;
  62       $query->_whereTables['civicrm_mailing'] = 1;
  63     }
  64   }
  65
  66   static function where(&$query) {
  67     $grouping = NULL;
  68     foreach (array_keys($query->_params) as $id) {
  69       if (!CRM_Utils_Array::value(0, $query->_params[$id])) {
  70         continue;
  71       }
  72       if (substr($query->_params[$id][0], 0, 8) == 'mailing_') {
  73         if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
  74           $query->_useDistinct = TRUE;
  75         }
  76         $grouping = $query->_params[$id][3];
  77         self::whereClauseSingle($query->_params[$id], $query);
  78       }
  79     }
  80   }
  81
  82   static function from($name, $mode, $side) {
  83     $from = NULL;
  84     switch ($name) {
  85       case 'civicrm_mailing_event_queue':
  86         $from = " $side JOIN civicrm_mailing_event_queue ON civicrm_mailing_event_queue.contact_id = contact_a.id";
  87         break;
  88
  89       case 'civicrm_mailing_job':
  90         $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.id = civicrm_mailing_event_queue.job_id";
  91         break;
  92
  93       case 'civicrm_mailing':
  94         $from = " $side JOIN civicrm_mailing on civicrm_mailing.id = civicrm_mailing_job.mailing_id";
  95         break;
  96
  97       case 'civicrm_mailing_event_bounce':
  98       case 'civicrm_mailing_event_delivered':
  99       case 'civicrm_mailing_event_opened':
 100       case 'civicrm_mailing_event_reply':
 101       case 'civicrm_mailing_event_unsubscribe':
 102       case 'civicrm_mailing_event_forward':
 103       case 'civicrm_mailing_event_trackable_url_open':
 104         $from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id";
 105         break;
 106     }
 107
 108     return $from;
 109   }
 110
 111   static function defaultReturnProperties($mode,
 112     $includeCustomFields = TRUE
 113   ) {
 114
 115     $properties = NULL;
 116     if ($mode & CRM_Contact_BAO_Query::MODE_MAILING) {
 117       $properties = array('mailing_id' => 1);
 118     }
 119     return $properties;
 120   }
 121
 122   static function whereClauseSingle(&$values, &$query) {
 123     list($name, $op, $value, $grouping, $wildcard) = $values;
 124
 125     $fields = array();
 126     $fields = self::getFields();
 127     switch ($name) {
 128       case 'mailing_id':
 129         $selectedMailings = array_flip($value);
 130
 131         $value = "(" . implode(',', $value) . ")";
 132         $op = 'IN';
 133         $query->_where[$grouping][] = "civicrm_mailing.id $op $value";
 134
 135         $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
 136         foreach ($selectedMailings as $id => $dnc) {
 137           $selectedMailings[$id] = $mailings[$id];
 138         }
 139         $selectedMailings = implode(' or ', $selectedMailings);
 140
 141         $query->_qill[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
 142         $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 143         $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 144         $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 145         return;
 146
 147       case 'mailing_name':
 148         $value = strtolower( addslashes( $value ) );
 149         if ( $wildcard ) {
 150           $value = "%$value%";
 151           $op    = 'LIKE';
 152         }
 153         $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'";
 154         $query->_qill[$grouping][]  = "Mailing Name $op \"$value\"";
 155         $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 156         $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 157         $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
 158         return;
 159
 160       case 'mailing_date':
 161       case 'mailing_date_low':
 162       case 'mailing_date_high':
 163         // process to / from date
 164         $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 165         $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
 166         $query->dateQueryBuilder($values,
 167           'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
 168         );
 169         return;
 170
 171       case 'mailing_delivery_status':
 172         $options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered');
 173
 174         list($name, $op, $value, $grouping, $wildcard) = $values;
 175         if ($value == 'Y') {
 176           self::mailingEventQueryBuilder($query, $values,
 177             'civicrm_mailing_event_delivered',
 178             'mailing_delivery_status',
 179             ts('Mailing Delivery'),
 180             $options
 181           );
 182         }
 183         elseif ($value == 'N') {
 184           $options['Y'] = $options['N'];
 185           $values = array($name, $op, 'Y', $grouping, $wildcard);
 186           self::mailingEventQueryBuilder($query, $values,
 187             'civicrm_mailing_event_bounce',
 188             'mailing_delivery_status',
 189             ts('Mailing Delivery'),
 190             $options
 191           );
 192         }
 193         return;
 194
 195       case 'mailing_open_status':
 196         self::mailingEventQueryBuilder($query, $values,
 197           'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')
 198         );
 199         return;
 200
 201       case 'mailing_click_status':
 202         self::mailingEventQueryBuilder($query, $values,
 203           'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click')
 204         );
 205         return;
 206
 207       case 'mailing_reply_status':
 208         self::mailingEventQueryBuilder($query, $values,
 209           'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply')
 210         );
 211         return;
 212
 213       case 'mailing_optout':
 214         $valueTitle = array(1 => ts('Opt-out Requests'));
 215         // include opt-out events only
 216         $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
 217         self::mailingEventQueryBuilder($query, $values,
 218           'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
 219           ts('Mailing: '), $valueTitle
 220         );
 221         return;
 222
 223       case 'mailing_unsubscribe':
 224         $valueTitle = array(1 => ts('Unsubscribe Requests'));
 225         // exclude opt-out events
 226         $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
 227         self::mailingEventQueryBuilder($query, $values,
 228           'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
 229           ts('Mailing: '), $valueTitle
 230         );
 231         return;
 232
 233       case 'mailing_forward':
 234         $valueTitle = array('Y' => ts('Forwards'));
 235         // since its a checkbox
 236         $values[2] = 'Y';
 237         self::mailingEventQueryBuilder($query, $values,
 238           'civicrm_mailing_event_forward', 'mailing_forward',
 239           ts('Mailing: '), $valueTitle
 240         );
 241         return;
 242     }
 243   }
 244
 245   /**
 246    * add all the elements shared between Mailing search and advnaced search
 247    *
 248    * @access public
 249    *
 250    * @return void
 251    * @static
 252    */
 253   static function buildSearchForm(&$form) {
 254     // mailing selectors
 255     $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
 256
 257     if (!empty($mailings)) {
 258       $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
 259         array('id' => 'mailing_id', 'multiple' => 'multiple', 'title' => ts('- select -'))
 260       );
 261     }
 262
 263     CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE, FALSE);
 264
 265     // event filters
 266     $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered'));
 267     $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open'));
 268     $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant::yesNoOptions('click'));
 269     $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply'));
 270
 271     $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
 272     $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
 273     $form->add('checkbox', 'mailing_forward', ts('Forwards'));
 274
 275     $form->assign('validCiviMailing', TRUE);
 276     $form->addFormRule(array('CRM_Mailing_BAO_Query', 'formRule'), $form);
 277   }
 278
 279   /**
 280    * global form rule
 281    *
 282    * @param array $fields  the input form values
 283    * @param array $files   the uploaded files if any
 284    * @param array $options additional user data
 285    *
 286    * @return true if no errors, else array of errors
 287    * @access public
 288    * @static
 289    */
 290   static function formRule($fields, $files, $self) {
 291     $errors = array();
 292     // if an event filter is specified, then a mailing selector must also be specified
 293     if ((CRM_Utils_Array::value('mailing_delivery_status', $fields) ||
 294         CRM_Utils_Array::value('mailing_open_status', $fields) ||
 295         CRM_Utils_Array::value('mailing_click_status', $fields) ||
 296         CRM_Utils_Array::value('mailing_reply_status', $fields)
 297       ) &&
 298       (!CRM_Utils_Array::value('mailing_id', $fields) &&
 299         !CRM_Utils_Array::value('mailing_date_low', $fields) &&
 300         !CRM_Utils_Array::value('mailing_date_high', $fields)
 301       )
 302     ) {
 303       $errors['mailing_id'] = ts('Must specify mailing name or date');
 304       // Keep search form opened in case of form rule.
 305       if (is_a($self, 'CRM_Contact_Form_Search_Advanced') && !isset(CRM_Contact_BAO_Query::$_openedPanes['Mailings'])) {
 306         CRM_Contact_BAO_Query::$_openedPanes['Mailings'] = TRUE;
 307         $self->assign('openedPanes', CRM_Contact_BAO_Query::$_openedPanes);
 308       }
 309     }
 310     return $errors;
 311   }
 312
 313   static function addShowHide(&$showHide) {
 314     $showHide->addHide('MailingForm');
 315     $showHide->addShow('MailingForm_show');
 316   }
 317
 318   static function searchAction(&$row, $id) {}
 319
 320   static function tableNames(&$tables) {}
 321
 322   /**
 323    * Filter query results based on which contacts do (not) have a particular mailing event in their history.
 324    *
 325    * @param $query
 326    * @param $values
 327    * @param $tableName
 328    * @param $fieldName
 329    * @param $fieldTitle
 330    *
 331    * @return void
 332    */
 333   static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
 334     list($name, $op, $value, $grouping, $wildcard) = $values;
 335
 336     if (empty($value) || $value == 'A') {
 337       // don't do any filtering
 338       return;
 339     }
 340
 341     if ($value == 'Y') {
 342       $query->_where[$grouping][] = $tableName . ".id is not null ";
 343     }
 344     elseif ($value == 'N') {
 345       $query->_where[$grouping][] = $tableName . ".id is null ";
 346     }
 347
 348     $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
 349     $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
 350     $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
 351   }
 352 }
 353