CRM/Contact/BAO/SavedSearch.php

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