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(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 a smart group from normalised values.
 339    *
 340    * @param array $params
 341    *
 342    * @return \CRM_Contact_DAO_SavedSearch
 343    */
 344   public static function create(&$params) {
 345     $savedSearch = new CRM_Contact_DAO_SavedSearch();
 346     $savedSearch->copyValues($params);
 347     $savedSearch->save();
 348
 349     return $savedSearch;
 350   }
 351
 352   /**
 353    * Assign test value.
 354    *
 355    * @param string $fieldName
 356    * @param array $fieldDef
 357    * @param int $counter
 358    */
 359   protected function assignTestValue($fieldName, &$fieldDef, $counter) {
 360     if ($fieldName == 'form_values') {
 361       // A dummy value for form_values.
 362       $this->{$fieldName} = serialize(
 363           ['sort_name' => "SortName{$counter}"]);
 364     }
 365     else {
 366       parent::assignTestValues($fieldName, $fieldDef, $counter);
 367     }
 368   }
 369
 370   /**
 371    * Store search variables in $queryParams which were skipped while processing query params,
 372    * precisely at CRM_Contact_BAO_Query::fixWhereValues(...). But these variable are required in
 373    * building smart group criteria otherwise it will cause issues like CRM-18585,CRM-19571
 374    *
 375    * @param array $queryParams
 376    * @param array $formValues
 377    */
 378   public static function saveSkippedElement(&$queryParams, $formValues) {
 379     // these are elements which are skipped in a smart group criteria
 380     $specialElements = [
 381       'operator',
 382       'component_mode',
 383       'display_relationship_type',
 384       'uf_group_id',
 385     ];
 386     foreach ($specialElements as $element) {
 387       if (!empty($formValues[$element])) {
 388         $queryParams[] = [$element, '=', $formValues[$element], 0, 0];
 389       }
 390     }
 391   }
 392
 393   /**
 394    * Decode relative custom fields (converted by CRM_Contact_BAO_Query->convertCustomRelativeFields(...))
 395    *  into desired formValues
 396    *
 397    * @param array $formValues
 398    * @param string $fieldName
 399    * @param string $op
 400    * @param array|string|int $value
 401    *
 402    * @throws \CiviCRM_API3_Exception
 403    */
 404   public static function decodeRelativeFields(&$formValues, $fieldName, $op, $value) {
 405     // check if its a custom date field, if yes then 'searchDate' format the value
 406     if (CRM_Contact_BAO_Query::isCustomDateField($fieldName)) {
 407       return;
 408     }
 409
 410     switch ($op) {
 411       case 'BETWEEN':
 412         list($formValues[$fieldName . '_from'], $formValues[$fieldName . '_to']) = $value;
 413         break;
 414
 415       case '>=':
 416         $formValues[$fieldName . '_from'] = $value;
 417         break;
 418
 419       case '<=':
 420         $formValues[$fieldName . '_to'] = $value;
 421         break;
 422     }
 423   }
 424
 425 }