CRM/Contact/BAO/SavedSearch.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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-2019
  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       'case_from_start_date_low' => 'case_from_date_low',
  99       'case_from_start_date_high' => 'case_from_date_high',
 100       'case_to_end_date_low' => 'case_to_date_low',
 101       'case_to_end_date_high' => 'case_to_date_high',
 102     ];
 103
 104     $fv = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'form_values');
 105     $result = NULL;
 106     if ($fv) {
 107       // make sure u unserialize - since it's stored in serialized form
 108       $result = unserialize($fv);
 109     }
 110
 111     $specialFields = ['contact_type', 'group', 'contact_tags', 'member_membership_type_id', 'member_status_id'];
 112     foreach ($result as $element => $value) {
 113       if (CRM_Contact_BAO_Query::isAlreadyProcessedForQueryFormat($value)) {
 114         $id = CRM_Utils_Array::value(0, $value);
 115         $value = CRM_Utils_Array::value(2, $value);
 116         if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 117           $op = key($value);
 118           $value = CRM_Utils_Array::value($op, $value);
 119           if (in_array($op, ['BETWEEN', '>=', '<='])) {
 120             self::decodeRelativeFields($result, $id, $op, $value);
 121             unset($result[$element]);
 122             continue;
 123           }
 124         }
 125         // Check for a date range field, which might be a standard date
 126         // range or a relative date.
 127         if (strpos($id, '_date_low') !== FALSE || strpos($id, '_date_high') !== FALSE) {
 128           $entityName = strstr($id, '_date', TRUE);
 129
 130           // This is the default, for non relative dates. We will overwrite
 131           // it if we determine this is a relative date.
 132           $result[$id] = $value;
 133           $result["{$entityName}_date_relative"] = 0;
 134
 135           if (!empty($result['relative_dates'])) {
 136             if (array_key_exists($entityName, $result['relative_dates'])) {
 137               // We have a match from a regular field.
 138               $result[$id] = NULL;
 139               $result["{$entityName}_date_relative"] = $result['relative_dates'][$entityName];
 140             }
 141             elseif (!empty($specialDateFields[$id])) {
 142               // We may have a match on a special date field.
 143               $entityName = strstr($specialDateFields[$id], '_date', TRUE);
 144               if (array_key_exists($entityName, $result['relative_dates'])) {
 145                 $result[$id] = NULL;
 146                 $result["{$entityName}_relative"] = $result['relative_dates'][$entityName];
 147               }
 148             }
 149           }
 150         }
 151         else {
 152           $result[$id] = $value;
 153         }
 154         unset($result[$element]);
 155         continue;
 156       }
 157       if (!empty($value) && is_array($value)) {
 158         if (in_array($element, $specialFields)) {
 159           // Remove the element to minimise support for legacy formats. It is stored in $value
 160           // so will be re-set with the right name.
 161           unset($result[$element]);
 162           $element = str_replace('member_membership_type_id', 'membership_type_id', $element);
 163           $element = str_replace('member_status_id', 'membership_status_id', $element);
 164           CRM_Contact_BAO_Query::legacyConvertFormValues($element, $value);
 165           $result[$element] = $value;
 166         }
 167         // As per the OK (Operator as Key) value format, value array may contain key
 168         // as an operator so to ensure the default is always set actual value
 169         elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 170           $result[$element] = CRM_Utils_Array::value(key($value), $value);
 171           if (is_string($result[$element])) {
 172             $result[$element] = str_replace("%", '', $result[$element]);
 173           }
 174         }
 175       }
 176       if (substr($element, 0, 7) == 'custom_' &&
 177         (substr($element, -5, 5) == '_from' || substr($element, -3, 3) == '_to')
 178       ) {
 179         // Ensure the _relative field is set if from or to are set to ensure custom date
 180         // fields with 'from' or 'to' values are displayed when the are set in the smart group
 181         // being loaded. (CRM-17116)
 182         if (!isset($result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'])) {
 183           $result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'] = 0;
 184         }
 185       }
 186       // check to see if we need to convert the old privacy array
 187       // CRM-9180
 188       if (!empty($result['privacy'])) {
 189         if (is_array($result['privacy'])) {
 190           $result['privacy_operator'] = 'AND';
 191           $result['privacy_toggle'] = 1;
 192           if (isset($result['privacy']['do_not_toggle'])) {
 193             if ($result['privacy']['do_not_toggle']) {
 194               $result['privacy_toggle'] = 2;
 195             }
 196             unset($result['privacy']['do_not_toggle']);
 197           }
 198
 199           $result['privacy_options'] = [];
 200           foreach ($result['privacy'] as $name => $val) {
 201             if ($val) {
 202               $result['privacy_options'][] = $name;
 203             }
 204           }
 205         }
 206         unset($result['privacy']);
 207       }
 208     }
 209
 210     if ($customSearchClass = CRM_Utils_Array::value('customSearchClass', $result)) {
 211       // check if there is a special function - formatSavedSearchFields defined in the custom search form
 212       if (method_exists($customSearchClass, 'formatSavedSearchFields')) {
 213         $customSearchClass::formatSavedSearchFields($result);
 214       }
 215     }
 216
 217     return $result;
 218   }
 219
 220   /**
 221    * Get search parameters.
 222    *
 223    * @param int $id
 224    *
 225    * @return array
 226    */
 227   public static function getSearchParams($id) {
 228     $fv = self::getFormValues($id);
 229     //check if the saved search has mapping id
 230     if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'mapping_id')) {
 231       return CRM_Core_BAO_Mapping::formattedFields($fv);
 232     }
 233     elseif (!empty($fv['customSearchID'])) {
 234       return $fv;
 235     }
 236     else {
 237       return CRM_Contact_BAO_Query::convertFormValues($fv);
 238     }
 239   }
 240
 241   /**
 242    * Get the where clause for a saved search.
 243    *
 244    * @param int $id
 245    *   Saved search id.
 246    * @param array $tables
 247    *   (reference ) add the tables that are needed for the select clause.
 248    * @param array $whereTables
 249    *   (reference ) add the tables that are needed for the where clause.
 250    *
 251    * @return string
 252    *   the where clause for this saved search
 253    */
 254   public static function whereClause($id, &$tables, &$whereTables) {
 255     $params = self::getSearchParams($id);
 256     if ($params) {
 257       if (!empty($params['customSearchID'])) {
 258         // this has not yet been implemented
 259       }
 260       else {
 261         return CRM_Contact_BAO_Query::getWhereClause($params, NULL, $tables, $whereTables);
 262       }
 263     }
 264     return NULL;
 265   }
 266
 267   /**
 268    * Contact IDS Sql (whatever that means!).
 269    *
 270    * @param int $id
 271    *
 272    * @return string
 273    */
 274   public static function contactIDsSQL($id) {
 275     $params = self::getSearchParams($id);
 276     if ($params && !empty($params['customSearchID'])) {
 277       return CRM_Contact_BAO_SearchCustom::contactIDSQL(NULL, $id);
 278     }
 279     else {
 280       $tables = $whereTables = ['civicrm_contact' => 1];
 281       $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 282       if (!$where) {
 283         $where = '( 1 )';
 284       }
 285       $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 286       return "
 287 SELECT contact_a.id
 288 $from
 289 WHERE  $where";
 290     }
 291   }
 292
 293   /**
 294    * Get from where email (whatever that means!).
 295    *
 296    * @param int $id
 297    *
 298    * @return array
 299    */
 300   public static function fromWhereEmail($id) {
 301     $params = self::getSearchParams($id);
 302
 303     if ($params) {
 304       if (!empty($params['customSearchID'])) {
 305         return CRM_Contact_BAO_SearchCustom::fromWhereEmail(NULL, $id);
 306       }
 307       else {
 308         $tables = $whereTables = ['civicrm_contact' => 1, 'civicrm_email' => 1];
 309         $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 310         $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 311         return [$from, $where];
 312       }
 313     }
 314     else {
 315       // fix for CRM-7240
 316       $from = "
 317 FROM      civicrm_contact contact_a
 318 LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
 319 ";
 320       $where = " ( 1 ) ";
 321       $tables['civicrm_contact'] = $whereTables['civicrm_contact'] = 1;
 322       $tables['civicrm_email'] = $whereTables['civicrm_email'] = 1;
 323       return [$from, $where];
 324     }
 325   }
 326
 327   /**
 328    * Given a saved search compute the clause and the tables and store it for future use.
 329    */
 330   public function buildClause() {
 331     $fv = unserialize($this->form_values);
 332
 333     if ($this->mapping_id) {
 334       $params = CRM_Core_BAO_Mapping::formattedFields($fv);
 335     }
 336     else {
 337       $params = CRM_Contact_BAO_Query::convertFormValues($fv);
 338     }
 339
 340     if (!empty($params)) {
 341       $tables = $whereTables = [];
 342       $this->where_clause = CRM_Contact_BAO_Query::getWhereClause($params, NULL, $tables, $whereTables);
 343       if (!empty($tables)) {
 344         $this->select_tables = serialize($tables);
 345       }
 346       if (!empty($whereTables)) {
 347         $this->where_tables = serialize($whereTables);
 348       }
 349     }
 350   }
 351
 352   /**
 353    * Save the search.
 354    *
 355    * @param bool $hook
 356    */
 357   public function save($hook = TRUE) {
 358     // first build the computed fields
 359     $this->buildClause();
 360
 361     parent::save($hook);
 362   }
 363
 364   /**
 365    * Given an id, get the name of the saved search.
 366    *
 367    * @param int $id
 368    *   The id of the saved search.
 369    *
 370    * @param string $value
 371    *
 372    * @return string
 373    *   the name of the saved search
 374    */
 375   public static function getName($id, $value = 'name') {
 376     $group = new CRM_Contact_DAO_Group();
 377     $group->saved_search_id = $id;
 378     if ($group->find(TRUE)) {
 379       return $group->$value;
 380     }
 381     return NULL;
 382   }
 383
 384   /**
 385    * Create a smart group from normalised values.
 386    *
 387    * @param array $params
 388    *
 389    * @return \CRM_Contact_DAO_SavedSearch
 390    */
 391   public static function create(&$params) {
 392     $savedSearch = new CRM_Contact_DAO_SavedSearch();
 393     if (isset($params['formValues']) &&
 394       !empty($params['formValues'])
 395     ) {
 396       $savedSearch->form_values = serialize($params['formValues']);
 397     }
 398     else {
 399       $savedSearch->form_values = NULL;
 400     }
 401
 402     $savedSearch->is_active = CRM_Utils_Array::value('is_active', $params, 1);
 403     $savedSearch->mapping_id = CRM_Utils_Array::value('mapping_id', $params, 'null');
 404     $savedSearch->custom_search_id = CRM_Utils_Array::value('custom_search_id', $params, 'null');
 405     $savedSearch->id = CRM_Utils_Array::value('id', $params, NULL);
 406
 407     $savedSearch->save();
 408
 409     return $savedSearch;
 410   }
 411
 412   /**
 413    * Assign test value.
 414    *
 415    * @param string $fieldName
 416    * @param array $fieldDef
 417    * @param int $counter
 418    */
 419   protected function assignTestValue($fieldName, &$fieldDef, $counter) {
 420     if ($fieldName == 'form_values') {
 421       // A dummy value for form_values.
 422       $this->{$fieldName} = serialize(
 423           ['sort_name' => "SortName{$counter}"]);
 424     }
 425     else {
 426       parent::assignTestValues($fieldName, $fieldDef, $counter);
 427     }
 428   }
 429
 430   /**
 431    * Store relative dates in separate array format
 432    *
 433    * @param array $queryParams
 434    * @param array $formValues
 435    */
 436   public static function saveRelativeDates(&$queryParams, $formValues) {
 437     // This is required only until all fields are converted to datepicker fields as the new format is truer to the
 438     // form format and simply saves (e.g) custom_3_relative => "this.year"
 439     $relativeDates = ['relative_dates' => []];
 440     $specialDateFields = ['event_relative', 'case_from_relative', 'case_to_relative', 'participant_relative'];
 441     foreach ($formValues as $id => $value) {
 442       if ((preg_match('/_date$/', $id) || in_array($id, $specialDateFields)) && !empty($value)) {
 443         $entityName = strstr($id, '_date', TRUE);
 444         if (empty($entityName)) {
 445           $entityName = strstr($id, '_relative', TRUE);
 446         }
 447         $relativeDates['relative_dates'][$entityName] = $value;
 448       }
 449     }
 450     // merge with original queryParams if relative date value(s) found
 451     if (count($relativeDates['relative_dates'])) {
 452       $queryParams = array_merge($queryParams, $relativeDates);
 453     }
 454   }
 455
 456   /**
 457    * Store search variables in $queryParams which were skipped while processing query params,
 458    * precisely at CRM_Contact_BAO_Query::fixWhereValues(...). But these variable are required in
 459    * building smart group criteria otherwise it will cause issues like CRM-18585,CRM-19571
 460    *
 461    * @param array $queryParams
 462    * @param array $formValues
 463    */
 464   public static function saveSkippedElement(&$queryParams, $formValues) {
 465     // these are elements which are skipped in a smart group criteria
 466     $specialElements = [
 467       'operator',
 468       'component_mode',
 469       'display_relationship_type',
 470       'uf_group_id',
 471     ];
 472     foreach ($specialElements as $element) {
 473       if (!empty($formValues[$element])) {
 474         $queryParams[] = [$element, '=', $formValues[$element], 0, 0];
 475       }
 476     }
 477   }
 478
 479   /**
 480    * Decode relative custom fields (converted by CRM_Contact_BAO_Query->convertCustomRelativeFields(...))
 481    *  into desired formValues
 482    *
 483    * @param array $formValues
 484    * @param string $fieldName
 485    * @param string $op
 486    * @param array|string|int $value
 487    */
 488   public static function decodeRelativeFields(&$formValues, $fieldName, $op, $value) {
 489     // check if its a custom date field, if yes then 'searchDate' format the value
 490     $isCustomDateField = CRM_Contact_BAO_Query::isCustomDateField($fieldName);
 491
 492     // select date range as default
 493     if ($isCustomDateField) {
 494       if (array_key_exists('relative_dates', $formValues) && array_key_exists($fieldName, $formValues['relative_dates'])) {
 495         $formValues[$fieldName . '_relative'] = $formValues['relative_dates'][$fieldName];
 496       }
 497       else {
 498         $formValues[$fieldName . '_relative'] = 0;
 499       }
 500     }
 501     switch ($op) {
 502       case 'BETWEEN':
 503         if ($isCustomDateField) {
 504           list($formValues[$fieldName . '_from'], $formValues[$fieldName . '_from_time']) = CRM_Utils_Date::setDateDefaults($value[0], 'searchDate');
 505           list($formValues[$fieldName . '_to'], $formValues[$fieldName . '_to_time']) = CRM_Utils_Date::setDateDefaults($value[1], 'searchDate');
 506         }
 507         else {
 508           list($formValues[$fieldName . '_from'], $formValues[$fieldName . '_to']) = $value;
 509         }
 510         break;
 511
 512       case '>=':
 513         if ($isCustomDateField) {
 514           list($formValues[$fieldName . '_from'], $formValues[$fieldName . '_from_time']) = CRM_Utils_Date::setDateDefaults($value, 'searchDate');
 515         }
 516         else {
 517           $formValues[$fieldName . '_from'] = $value;
 518         }
 519         break;
 520
 521       case '<=':
 522         if ($isCustomDateField) {
 523           list($formValues[$fieldName . '_to'], $formValues[$fieldName . '_to_time']) = CRM_Utils_Date::setDateDefaults($value, 'searchDate');
 524         }
 525         else {
 526           $formValues[$fieldName . '_to'] = $value;
 527         }
 528         break;
 529     }
 530   }
 531
 532 }