CRM/Contact/BAO/SavedSearch.php

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