CRM/Contact/BAO/SavedSearch.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2020                                |
   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-2020
  32  */
  33
  34 /**
  35  * Business object for Saved searches.
  36  */
  37 class CRM_Contact_BAO_SavedSearch extends CRM_Contact_DAO_SavedSearch {
  38
  39   /**
  40    * Class constructor.
  41    */
  42   public function __construct() {
  43     parent::__construct();
  44   }
  45
  46   /**
  47    * Query the db for all saved searches.
  48    *
  49    * @return array
  50    *   contains the search name as value and and id as key
  51    */
  52   public function getAll() {
  53     $savedSearch = new CRM_Contact_DAO_SavedSearch();
  54     $savedSearch->selectAdd();
  55     $savedSearch->selectAdd('id, name');
  56     $savedSearch->find();
  57     while ($savedSearch->fetch()) {
  58       $aSavedSearch[$savedSearch->id] = $savedSearch->name;
  59     }
  60     return $aSavedSearch;
  61   }
  62
  63   /**
  64    * Retrieve DB object based on input parameters.
  65    *
  66    * It also stores all the retrieved values in the default array.
  67    *
  68    * @param array $params
  69    *   (reference ) an assoc array of name/value pairs.
  70    * @param array $defaults
  71    *   (reference ) an assoc array to hold the flattened values.
  72    *
  73    * @return CRM_Contact_BAO_SavedSearch
  74    */
  75   public static function retrieve(&$params, &$defaults) {
  76     $savedSearch = new CRM_Contact_DAO_SavedSearch();
  77     $savedSearch->copyValues($params);
  78     if ($savedSearch->find(TRUE)) {
  79       CRM_Core_DAO::storeValues($savedSearch, $defaults);
  80       return $savedSearch;
  81     }
  82     return NULL;
  83   }
  84
  85   /**
  86    * Given an id, extract the formValues of the saved search.
  87    *
  88    * @param int $id
  89    *   The id of the saved search.
  90    *
  91    * @return array
  92    *   the values of the posted saved search used as default values in various Search Form
  93    */
  94   public static function getFormValues($id) {
  95     $specialDateFields = [
  96       'event_start_date_low' => 'event_date_low',
  97       'event_end_date_high' => 'event_date_high',
  98     ];
  99
 100     $fv = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'form_values');
 101     $result = NULL;
 102     if ($fv) {
 103       // make sure u unserialize - since it's stored in serialized form
 104       $result = unserialize($fv);
 105     }
 106
 107     $specialFields = ['contact_type', 'group', 'contact_tags', 'member_membership_type_id', 'member_status_id'];
 108     foreach ($result as $element => $value) {
 109       if (CRM_Contact_BAO_Query::isAlreadyProcessedForQueryFormat($value)) {
 110         $id = CRM_Utils_Array::value(0, $value);
 111         $value = CRM_Utils_Array::value(2, $value);
 112         if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 113           $op = key($value);
 114           $value = CRM_Utils_Array::value($op, $value);
 115           if (in_array($op, ['BETWEEN', '>=', '<='])) {
 116             self::decodeRelativeFields($result, $id, $op, $value);
 117             unset($result[$element]);
 118             continue;
 119           }
 120         }
 121         // Check for a date range field, which might be a standard date
 122         // range or a relative date.
 123         if (strpos($id, '_date_low') !== FALSE || strpos($id, '_date_high') !== FALSE) {
 124           $entityName = strstr($id, '_date', TRUE);
 125
 126           // This is the default, for non relative dates. We will overwrite
 127           // it if we determine this is a relative date.
 128           $result[$id] = $value;
 129           $result["{$entityName}_date_relative"] = 0;
 130
 131           if (!empty($result['relative_dates'])) {
 132             if (array_key_exists($entityName, $result['relative_dates'])) {
 133               // We have a match from a regular field.
 134               $result[$id] = NULL;
 135               $result["{$entityName}_date_relative"] = $result['relative_dates'][$entityName];
 136             }
 137             elseif (!empty($specialDateFields[$id])) {
 138               // We may have a match on a special date field.
 139               $entityName = strstr($specialDateFields[$id], '_date', TRUE);
 140               if (array_key_exists($entityName, $result['relative_dates'])) {
 141                 $result[$id] = NULL;
 142                 $result["{$entityName}_relative"] = $result['relative_dates'][$entityName];
 143               }
 144             }
 145           }
 146         }
 147         else {
 148           $result[$id] = $value;
 149         }
 150         unset($result[$element]);
 151         continue;
 152       }
 153       if (!empty($value) && is_array($value)) {
 154         if (in_array($element, $specialFields)) {
 155           // Remove the element to minimise support for legacy formats. It is stored in $value
 156           // so will be re-set with the right name.
 157           unset($result[$element]);
 158           $element = str_replace('member_membership_type_id', 'membership_type_id', $element);
 159           $element = str_replace('member_status_id', 'membership_status_id', $element);
 160           CRM_Contact_BAO_Query::legacyConvertFormValues($element, $value);
 161           $result[$element] = $value;
 162         }
 163         // As per the OK (Operator as Key) value format, value array may contain key
 164         // as an operator so to ensure the default is always set actual value
 165         elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 166           $result[$element] = CRM_Utils_Array::value(key($value), $value);
 167           if (is_string($result[$element])) {
 168             $result[$element] = str_replace("%", '', $result[$element]);
 169           }
 170         }
 171       }
 172       if (substr($element, 0, 7) == 'custom_' &&
 173         (substr($element, -5, 5) == '_from' || substr($element, -3, 3) == '_to')
 174       ) {
 175         // Ensure the _relative field is set if from or to are set to ensure custom date
 176         // fields with 'from' or 'to' values are displayed when the are set in the smart group
 177         // being loaded. (CRM-17116)
 178         if (!isset($result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'])) {
 179           $result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'] = 0;
 180         }
 181       }
 182       // check to see if we need to convert the old privacy array
 183       // CRM-9180
 184       if (!empty($result['privacy'])) {
 185         if (is_array($result['privacy'])) {
 186           $result['privacy_operator'] = 'AND';
 187           $result['privacy_toggle'] = 1;
 188           if (isset($result['privacy']['do_not_toggle'])) {
 189             if ($result['privacy']['do_not_toggle']) {
 190               $result['privacy_toggle'] = 2;
 191             }
 192             unset($result['privacy']['do_not_toggle']);
 193           }
 194
 195           $result['privacy_options'] = [];
 196           foreach ($result['privacy'] as $name => $val) {
 197             if ($val) {
 198               $result['privacy_options'][] = $name;
 199             }
 200           }
 201         }
 202         unset($result['privacy']);
 203       }
 204     }
 205
 206     if ($customSearchClass = CRM_Utils_Array::value('customSearchClass', $result)) {
 207       // check if there is a special function - formatSavedSearchFields defined in the custom search form
 208       if (method_exists($customSearchClass, 'formatSavedSearchFields')) {
 209         $customSearchClass::formatSavedSearchFields($result);
 210       }
 211     }
 212
 213     return $result;
 214   }
 215
 216   /**
 217    * Get search parameters.
 218    *
 219    * @param int $id
 220    *
 221    * @return array
 222    */
 223   public static function getSearchParams($id) {
 224     $fv = self::getFormValues($id);
 225     //check if the saved search has mapping id
 226     if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'mapping_id')) {
 227       return CRM_Core_BAO_Mapping::formattedFields($fv);
 228     }
 229     elseif (!empty($fv['customSearchID'])) {
 230       return $fv;
 231     }
 232     else {
 233       return CRM_Contact_BAO_Query::convertFormValues($fv);
 234     }
 235   }
 236
 237   /**
 238    * Get the where clause for a saved search.
 239    *
 240    * @param int $id
 241    *   Saved search id.
 242    * @param array $tables
 243    *   (reference ) add the tables that are needed for the select clause.
 244    * @param array $whereTables
 245    *   (reference ) add the tables that are needed for the where clause.
 246    *
 247    * @return string
 248    *   the where clause for this saved search
 249    */
 250   public static function whereClause($id, &$tables, &$whereTables) {
 251     $params = self::getSearchParams($id);
 252     if ($params) {
 253       if (!empty($params['customSearchID'])) {
 254         // this has not yet been implemented
 255       }
 256       else {
 257         return CRM_Contact_BAO_Query::getWhereClause($params, NULL, $tables, $whereTables);
 258       }
 259     }
 260     return NULL;
 261   }
 262
 263   /**
 264    * Contact IDS Sql (whatever that means!).
 265    *
 266    * @param int $id
 267    *
 268    * @return string
 269    */
 270   public static function contactIDsSQL($id) {
 271     $params = self::getSearchParams($id);
 272     if ($params && !empty($params['customSearchID'])) {
 273       return CRM_Contact_BAO_SearchCustom::contactIDSQL(NULL, $id);
 274     }
 275     else {
 276       $tables = $whereTables = ['civicrm_contact' => 1];
 277       $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 278       if (!$where) {
 279         $where = '( 1 )';
 280       }
 281       $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 282       return "
 283 SELECT contact_a.id
 284 $from
 285 WHERE  $where";
 286     }
 287   }
 288
 289   /**
 290    * Get from where email (whatever that means!).
 291    *
 292    * @param int $id
 293    *
 294    * @return array
 295    */
 296   public static function fromWhereEmail($id) {
 297     $params = self::getSearchParams($id);
 298
 299     if ($params) {
 300       if (!empty($params['customSearchID'])) {
 301         return CRM_Contact_BAO_SearchCustom::fromWhereEmail(NULL, $id);
 302       }
 303       else {
 304         $tables = $whereTables = ['civicrm_contact' => 1, 'civicrm_email' => 1];
 305         $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 306         $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 307         return [$from, $where];
 308       }
 309     }
 310     else {
 311       // fix for CRM-7240
 312       $from = "
 313 FROM      civicrm_contact contact_a
 314 LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
 315 ";
 316       $where = " ( 1 ) ";
 317       $tables['civicrm_contact'] = $whereTables['civicrm_contact'] = 1;
 318       $tables['civicrm_email'] = $whereTables['civicrm_email'] = 1;
 319       return [$from, $where];
 320     }
 321   }
 322
 323   /**
 324    * Given an id, get the name of the saved search.
 325    *
 326    * @param int $id
 327    *   The id of the saved search.
 328    *
 329    * @param string $value
 330    *
 331    * @return string
 332    *   the name of the saved search
 333    */
 334   public static function getName($id, $value = 'name') {
 335     $group = new CRM_Contact_DAO_Group();
 336     $group->saved_search_id = $id;
 337     if ($group->find(TRUE)) {
 338       return $group->$value;
 339     }
 340     return NULL;
 341   }
 342
 343   /**
 344    * Create a smart group from normalised values.
 345    *
 346    * @param array $params
 347    *
 348    * @return \CRM_Contact_DAO_SavedSearch
 349    */
 350   public static function create(&$params) {
 351     $savedSearch = new CRM_Contact_DAO_SavedSearch();
 352     if (isset($params['formValues']) &&
 353       !empty($params['formValues'])
 354     ) {
 355       $savedSearch->form_values = serialize($params['formValues']);
 356     }
 357     else {
 358       $savedSearch->form_values = NULL;
 359     }
 360
 361     $savedSearch->is_active = CRM_Utils_Array::value('is_active', $params, 1);
 362     $savedSearch->mapping_id = CRM_Utils_Array::value('mapping_id', $params, 'null');
 363     $savedSearch->custom_search_id = CRM_Utils_Array::value('custom_search_id', $params, 'null');
 364     $savedSearch->id = CRM_Utils_Array::value('id', $params, NULL);
 365
 366     $savedSearch->save();
 367
 368     return $savedSearch;
 369   }
 370
 371   /**
 372    * Assign test value.
 373    *
 374    * @param string $fieldName
 375    * @param array $fieldDef
 376    * @param int $counter
 377    */
 378   protected function assignTestValue($fieldName, &$fieldDef, $counter) {
 379     if ($fieldName == 'form_values') {
 380       // A dummy value for form_values.
 381       $this->{$fieldName} = serialize(
 382           ['sort_name' => "SortName{$counter}"]);
 383     }
 384     else {
 385       parent::assignTestValues($fieldName, $fieldDef, $counter);
 386     }
 387   }
 388
 389   /**
 390    * Store search variables in $queryParams which were skipped while processing query params,
 391    * precisely at CRM_Contact_BAO_Query::fixWhereValues(...). But these variable are required in
 392    * building smart group criteria otherwise it will cause issues like CRM-18585,CRM-19571
 393    *
 394    * @param array $queryParams
 395    * @param array $formValues
 396    */
 397   public static function saveSkippedElement(&$queryParams, $formValues) {
 398     // these are elements which are skipped in a smart group criteria
 399     $specialElements = [
 400       'operator',
 401       'component_mode',
 402       'display_relationship_type',
 403       'uf_group_id',
 404     ];
 405     foreach ($specialElements as $element) {
 406       if (!empty($formValues[$element])) {
 407         $queryParams[] = [$element, '=', $formValues[$element], 0, 0];
 408       }
 409     }
 410   }
 411
 412   /**
 413    * Decode relative custom fields (converted by CRM_Contact_BAO_Query->convertCustomRelativeFields(...))
 414    *  into desired formValues
 415    *
 416    * @param array $formValues
 417    * @param string $fieldName
 418    * @param string $op
 419    * @param array|string|int $value
 420    *
 421    * @throws \CiviCRM_API3_Exception
 422    */
 423   public static function decodeRelativeFields(&$formValues, $fieldName, $op, $value) {
 424     // check if its a custom date field, if yes then 'searchDate' format the value
 425     if (CRM_Contact_BAO_Query::isCustomDateField($fieldName)) {
 426       return;
 427     }
 428
 429     switch ($op) {
 430       case 'BETWEEN':
 431         list($formValues[$fieldName . '_from'], $formValues[$fieldName . '_to']) = $value;
 432         break;
 433
 434       case '>=':
 435         $formValues[$fieldName . '_from'] = $value;
 436         break;
 437
 438       case '<=':
 439         $formValues[$fieldName . '_to'] = $value;
 440         break;
 441     }
 442   }
 443
 444 }