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_BAO_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()
 212       ->setCheckPermissions(FALSE)
 213       ->addWhere('id', '=', $id)
 214       ->execute()
 215       ->first();
 216     if ($savedSearch['api_entity']) {
 217       return $savedSearch;
 218     }
 219     $fv = self::getFormValues($id);
 220     //check if the saved search has mapping id
 221     if ($savedSearch['mapping_id']) {
 222       return CRM_Core_BAO_Mapping::formattedFields($fv);
 223     }
 224     elseif (!empty($fv['customSearchID'])) {
 225       return $fv;
 226     }
 227     else {
 228       return CRM_Contact_BAO_Query::convertFormValues($fv);
 229     }
 230   }
 231
 232   /**
 233    * Get the where clause for a saved search.
 234    *
 235    * @param int $id
 236    *   Saved search id.
 237    * @param array $tables
 238    *   (reference ) add the tables that are needed for the select clause.
 239    * @param array $whereTables
 240    *   (reference ) add the tables that are needed for the where clause.
 241    *
 242    * @return string
 243    *   the where clause for this saved search
 244    */
 245   public static function whereClause($id, &$tables, &$whereTables) {
 246     $params = self::getSearchParams($id);
 247     if ($params) {
 248       if (!empty($params['customSearchID'])) {
 249         // this has not yet been implemented
 250       }
 251       else {
 252         return CRM_Contact_BAO_Query::getWhereClause($params, NULL, $tables, $whereTables);
 253       }
 254     }
 255     return NULL;
 256   }
 257
 258   /**
 259    * Contact IDS Sql (whatever that means!).
 260    *
 261    * @param int $id
 262    *
 263    * @return string
 264    */
 265   public static function contactIDsSQL($id) {
 266     $params = self::getSearchParams($id);
 267     if ($params && !empty($params['customSearchID'])) {
 268       return CRM_Contact_BAO_SearchCustom::contactIDSQL(NULL, $id);
 269     }
 270     else {
 271       $tables = $whereTables = ['civicrm_contact' => 1];
 272       $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 273       if (!$where) {
 274         $where = '( 1 )';
 275       }
 276       $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 277       return "
 278 SELECT contact_a.id
 279 $from
 280 WHERE  $where";
 281     }
 282   }
 283
 284   /**
 285    * Get from where email (whatever that means!).
 286    *
 287    * @param int $id
 288    *
 289    * @return array
 290    */
 291   public static function fromWhereEmail($id) {
 292     $params = self::getSearchParams($id);
 293
 294     if ($params) {
 295       if (!empty($params['customSearchID'])) {
 296         return CRM_Contact_BAO_SearchCustom::fromWhereEmail(NULL, $id);
 297       }
 298       else {
 299         $tables = $whereTables = ['civicrm_contact' => 1, 'civicrm_email' => 1];
 300         $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 301         $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 302         return [$from, $where];
 303       }
 304     }
 305     else {
 306       // fix for CRM-7240
 307       $from = "
 308 FROM      civicrm_contact contact_a
 309 LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
 310 ";
 311       $where = " ( 1 ) ";
 312       $tables['civicrm_contact'] = $whereTables['civicrm_contact'] = 1;
 313       $tables['civicrm_email'] = $whereTables['civicrm_email'] = 1;
 314       return [$from, $where];
 315     }
 316   }
 317
 318   /**
 319    * Given an id, get the name of the saved search.
 320    *
 321    * @param int $id
 322    *   The id of the saved search.
 323    *
 324    * @param string $value
 325    *
 326    * @return string
 327    *   the name of the saved search
 328    */
 329   public static function getName($id, $value = 'name') {
 330     $group = new CRM_Contact_DAO_Group();
 331     $group->saved_search_id = $id;
 332     if ($group->find(TRUE)) {
 333       return $group->$value;
 334     }
 335     return NULL;
 336   }
 337
 338   /**
 339    * Create a smart group from normalised values.
 340    *
 341    * @param array $params
 342    *
 343    * @return \CRM_Contact_DAO_SavedSearch
 344    */
 345   public static function create(&$params) {
 346     $savedSearch = new CRM_Contact_DAO_SavedSearch();
 347     $savedSearch->copyValues($params);
 348     $savedSearch->save();
 349
 350     return $savedSearch;
 351   }
 352
 353   /**
 354    * Assign test value.
 355    *
 356    * @param string $fieldName
 357    * @param array $fieldDef
 358    * @param int $counter
 359    */
 360   protected function assignTestValue($fieldName, &$fieldDef, $counter) {
 361     if ($fieldName == 'form_values') {
 362       // A dummy value for form_values.
 363       $this->{$fieldName} = serialize(
 364           ['sort_name' => "SortName{$counter}"]);
 365     }
 366     else {
 367       parent::assignTestValues($fieldName, $fieldDef, $counter);
 368     }
 369   }
 370
 371   /**
 372    * Store search variables in $queryParams which were skipped while processing query params,
 373    * precisely at CRM_Contact_BAO_Query::fixWhereValues(...). But these variable are required in
 374    * building smart group criteria otherwise it will cause issues like CRM-18585,CRM-19571
 375    *
 376    * @param array $queryParams
 377    * @param array $formValues
 378    */
 379   public static function saveSkippedElement(&$queryParams, $formValues) {
 380     // these are elements which are skipped in a smart group criteria
 381     $specialElements = [
 382       'operator',
 383       'component_mode',
 384       'display_relationship_type',
 385       'uf_group_id',
 386     ];
 387     foreach ($specialElements as $element) {
 388       if (!empty($formValues[$element])) {
 389         $queryParams[] = [$element, '=', $formValues[$element], 0, 0];
 390       }
 391     }
 392   }
 393
 394   /**
 395    * Decode relative custom fields (converted by CRM_Contact_BAO_Query->convertCustomRelativeFields(...))
 396    *  into desired formValues
 397    *
 398    * @param array $formValues
 399    * @param string $fieldName
 400    * @param string $op
 401    * @param array|string|int $value
 402    *
 403    * @throws \CiviCRM_API3_Exception
 404    */
 405   public static function decodeRelativeFields(&$formValues, $fieldName, $op, $value) {
 406     // check if its a custom date field, if yes then 'searchDate' format the value
 407     if (CRM_Contact_BAO_Query::isCustomDateField($fieldName)) {
 408       return;
 409     }
 410
 411     switch ($op) {
 412       case 'BETWEEN':
 413         list($formValues[$fieldName . '_from'], $formValues[$fieldName . '_to']) = $value;
 414         break;
 415
 416       case '>=':
 417         $formValues[$fieldName . '_from'] = $value;
 418         break;
 419
 420       case '<=':
 421         $formValues[$fieldName . '_to'] = $value;
 422         break;
 423     }
 424   }
 425
 426 }