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   public static function getFormValues($id) {
  79     $specialDateFields = [
  80       'event_start_date_low' => 'event_date_low',
  81       'event_end_date_high' => 'event_date_high',
  82     ];
  83
  84     $fv = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'form_values');
  85     $result = NULL;
  86     if ($fv) {
  87       // make sure u CRM_Utils_String::unserialize - since it's stored in serialized form
  88       $result = CRM_Utils_String::unserialize($fv);
  89     }
  90
  91     $specialFields = ['contact_type', 'group', 'contact_tags', 'member_membership_type_id', 'member_status_id'];
  92     foreach ($result as $element => $value) {
  93       if (CRM_Contact_BAO_Query::isAlreadyProcessedForQueryFormat($value)) {
  94         $id = CRM_Utils_Array::value(0, $value);
  95         $value = CRM_Utils_Array::value(2, $value);
  96         if (is_array($value) && in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
  97           $op = key($value);
  98           $value = CRM_Utils_Array::value($op, $value);
  99           if (in_array($op, ['BETWEEN', '>=', '<='])) {
 100             self::decodeRelativeFields($result, $id, $op, $value);
 101             unset($result[$element]);
 102             continue;
 103           }
 104         }
 105         // Check for a date range field, which might be a standard date
 106         // range or a relative date.
 107         if (strpos($id, '_date_low') !== FALSE || strpos($id, '_date_high') !== FALSE) {
 108           $entityName = strstr($id, '_date', TRUE);
 109
 110           // This is the default, for non relative dates. We will overwrite
 111           // it if we determine this is a relative date.
 112           $result[$id] = $value;
 113           $result["{$entityName}_date_relative"] = 0;
 114
 115           if (!empty($result['relative_dates'])) {
 116             if (array_key_exists($entityName, $result['relative_dates'])) {
 117               // We have a match from a regular field.
 118               $result[$id] = NULL;
 119               $result["{$entityName}_date_relative"] = $result['relative_dates'][$entityName];
 120             }
 121             elseif (!empty($specialDateFields[$id])) {
 122               // We may have a match on a special date field.
 123               $entityName = strstr($specialDateFields[$id], '_date', TRUE);
 124               if (array_key_exists($entityName, $result['relative_dates'])) {
 125                 $result[$id] = NULL;
 126                 $result["{$entityName}_relative"] = $result['relative_dates'][$entityName];
 127               }
 128             }
 129           }
 130         }
 131         else {
 132           $result[$id] = $value;
 133         }
 134         unset($result[$element]);
 135         continue;
 136       }
 137       if (!empty($value) && is_array($value)) {
 138         if (in_array($element, $specialFields)) {
 139           // Remove the element to minimise support for legacy formats. It is stored in $value
 140           // so will be re-set with the right name.
 141           unset($result[$element]);
 142           $element = str_replace('member_membership_type_id', 'membership_type_id', $element);
 143           $element = str_replace('member_status_id', 'membership_status_id', $element);
 144           CRM_Contact_BAO_Query::legacyConvertFormValues($element, $value);
 145           $result[$element] = $value;
 146         }
 147         // As per the OK (Operator as Key) value format, value array may contain key
 148         // as an operator so to ensure the default is always set actual value
 149         elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
 150           $result[$element] = CRM_Utils_Array::value(key($value), $value);
 151           if (is_string($result[$element])) {
 152             $result[$element] = str_replace("%", '', $result[$element]);
 153           }
 154         }
 155       }
 156       if (substr($element, 0, 7) == 'custom_' &&
 157         (substr($element, -5, 5) == '_from' || substr($element, -3, 3) == '_to')
 158       ) {
 159         // Ensure the _relative field is set if from or to are set to ensure custom date
 160         // fields with 'from' or 'to' values are displayed when the are set in the smart group
 161         // being loaded. (CRM-17116)
 162         if (!isset($result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'])) {
 163           $result[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_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   public static function getSearchParams($id) {
 208     $fv = self::getFormValues($id);
 209     //check if the saved search has mapping id
 210     if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $id, 'mapping_id')) {
 211       return CRM_Core_BAO_Mapping::formattedFields($fv);
 212     }
 213     elseif (!empty($fv['customSearchID'])) {
 214       return $fv;
 215     }
 216     else {
 217       return CRM_Contact_BAO_Query::convertFormValues($fv);
 218     }
 219   }
 220
 221   /**
 222    * Get the where clause for a saved search.
 223    *
 224    * @param int $id
 225    *   Saved search id.
 226    * @param array $tables
 227    *   (reference ) add the tables that are needed for the select clause.
 228    * @param array $whereTables
 229    *   (reference ) add the tables that are needed for the where clause.
 230    *
 231    * @return string
 232    *   the where clause for this saved search
 233    */
 234   public static function whereClause($id, &$tables, &$whereTables) {
 235     $params = self::getSearchParams($id);
 236     if ($params) {
 237       if (!empty($params['customSearchID'])) {
 238         // this has not yet been implemented
 239       }
 240       else {
 241         return CRM_Contact_BAO_Query::getWhereClause($params, NULL, $tables, $whereTables);
 242       }
 243     }
 244     return NULL;
 245   }
 246
 247   /**
 248    * Contact IDS Sql (whatever that means!).
 249    *
 250    * @param int $id
 251    *
 252    * @return string
 253    */
 254   public static function contactIDsSQL($id) {
 255     $params = self::getSearchParams($id);
 256     if ($params && !empty($params['customSearchID'])) {
 257       return CRM_Contact_BAO_SearchCustom::contactIDSQL(NULL, $id);
 258     }
 259     else {
 260       $tables = $whereTables = ['civicrm_contact' => 1];
 261       $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 262       if (!$where) {
 263         $where = '( 1 )';
 264       }
 265       $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 266       return "
 267 SELECT contact_a.id
 268 $from
 269 WHERE  $where";
 270     }
 271   }
 272
 273   /**
 274    * Get from where email (whatever that means!).
 275    *
 276    * @param int $id
 277    *
 278    * @return array
 279    */
 280   public static function fromWhereEmail($id) {
 281     $params = self::getSearchParams($id);
 282
 283     if ($params) {
 284       if (!empty($params['customSearchID'])) {
 285         return CRM_Contact_BAO_SearchCustom::fromWhereEmail(NULL, $id);
 286       }
 287       else {
 288         $tables = $whereTables = ['civicrm_contact' => 1, 'civicrm_email' => 1];
 289         $where = CRM_Contact_BAO_SavedSearch::whereClause($id, $tables, $whereTables);
 290         $from = CRM_Contact_BAO_Query::fromClause($whereTables);
 291         return [$from, $where];
 292       }
 293     }
 294     else {
 295       // fix for CRM-7240
 296       $from = "
 297 FROM      civicrm_contact contact_a
 298 LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
 299 ";
 300       $where = " ( 1 ) ";
 301       $tables['civicrm_contact'] = $whereTables['civicrm_contact'] = 1;
 302       $tables['civicrm_email'] = $whereTables['civicrm_email'] = 1;
 303       return [$from, $where];
 304     }
 305   }
 306
 307   /**
 308    * Given an id, get the name of the saved search.
 309    *
 310    * @param int $id
 311    *   The id of the saved search.
 312    *
 313    * @param string $value
 314    *
 315    * @return string
 316    *   the name of the saved search
 317    */
 318   public static function getName($id, $value = 'name') {
 319     $group = new CRM_Contact_DAO_Group();
 320     $group->saved_search_id = $id;
 321     if ($group->find(TRUE)) {
 322       return $group->$value;
 323     }
 324     return NULL;
 325   }
 326
 327   /**
 328    * Create a smart group from normalised values.
 329    *
 330    * @param array $params
 331    *
 332    * @return \CRM_Contact_DAO_SavedSearch
 333    */
 334   public static function create(&$params) {
 335     $savedSearch = new CRM_Contact_DAO_SavedSearch();
 336     if (isset($params['formValues']) &&
 337       !empty($params['formValues'])
 338     ) {
 339       $savedSearch->form_values = serialize($params['formValues']);
 340     }
 341     else {
 342       $savedSearch->form_values = NULL;
 343     }
 344
 345     $savedSearch->is_active = CRM_Utils_Array::value('is_active', $params, 1);
 346     $savedSearch->mapping_id = CRM_Utils_Array::value('mapping_id', $params, 'null');
 347     $savedSearch->custom_search_id = CRM_Utils_Array::value('custom_search_id', $params, 'null');
 348     $savedSearch->id = CRM_Utils_Array::value('id', $params, NULL);
 349
 350     $savedSearch->save();
 351
 352     return $savedSearch;
 353   }
 354
 355   /**
 356    * Assign test value.
 357    *
 358    * @param string $fieldName
 359    * @param array $fieldDef
 360    * @param int $counter
 361    */
 362   protected function assignTestValue($fieldName, &$fieldDef, $counter) {
 363     if ($fieldName == 'form_values') {
 364       // A dummy value for form_values.
 365       $this->{$fieldName} = serialize(
 366           ['sort_name' => "SortName{$counter}"]);
 367     }
 368     else {
 369       parent::assignTestValues($fieldName, $fieldDef, $counter);
 370     }
 371   }
 372
 373   /**
 374    * Store search variables in $queryParams which were skipped while processing query params,
 375    * precisely at CRM_Contact_BAO_Query::fixWhereValues(...). But these variable are required in
 376    * building smart group criteria otherwise it will cause issues like CRM-18585,CRM-19571
 377    *
 378    * @param array $queryParams
 379    * @param array $formValues
 380    */
 381   public static function saveSkippedElement(&$queryParams, $formValues) {
 382     // these are elements which are skipped in a smart group criteria
 383     $specialElements = [
 384       'operator',
 385       'component_mode',
 386       'display_relationship_type',
 387       'uf_group_id',
 388     ];
 389     foreach ($specialElements as $element) {
 390       if (!empty($formValues[$element])) {
 391         $queryParams[] = [$element, '=', $formValues[$element], 0, 0];
 392       }
 393     }
 394   }
 395
 396   /**
 397    * Decode relative custom fields (converted by CRM_Contact_BAO_Query->convertCustomRelativeFields(...))
 398    *  into desired formValues
 399    *
 400    * @param array $formValues
 401    * @param string $fieldName
 402    * @param string $op
 403    * @param array|string|int $value
 404    *
 405    * @throws \CiviCRM_API3_Exception
 406    */
 407   public static function decodeRelativeFields(&$formValues, $fieldName, $op, $value) {
 408     // check if its a custom date field, if yes then 'searchDate' format the value
 409     if (CRM_Contact_BAO_Query::isCustomDateField($fieldName)) {
 410       return;
 411     }
 412
 413     switch ($op) {
 414       case 'BETWEEN':
 415         list($formValues[$fieldName . '_from'], $formValues[$fieldName . '_to']) = $value;
 416         break;
 417
 418       case '>=':
 419         $formValues[$fieldName . '_from'] = $value;
 420         break;
 421
 422       case '<=':
 423         $formValues[$fieldName . '_to'] = $value;
 424         break;
 425     }
 426   }
 427
 428 }