CRM/Contact/Form/Search/Builder.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  * This class is for search builder processing.
  20  */
  21 class CRM_Contact_Form_Search_Builder extends CRM_Contact_Form_Search {
  22
  23   /**
  24    * Number of columns in where.
  25    *
  26    * @var int
  27    */
  28   public $_columnCount;
  29
  30   /**
  31    * Number of blocks to be shown.
  32    *
  33    * @var int
  34    */
  35   public $_blockCount;
  36
  37   /**
  38    * Build the form object.
  39    */
  40   public function preProcess() {
  41     $this->set('searchFormName', 'Builder');
  42
  43     $this->set('context', 'builder');
  44     parent::preProcess();
  45
  46     // Get the block count
  47     $this->_blockCount = $this->get('blockCount');
  48     // Initialize new form
  49     if (!$this->_blockCount) {
  50       $this->_blockCount = 4;
  51       $this->set('newBlock', 1);
  52     }
  53
  54     //get the column count
  55     $this->_columnCount = $this->get('columnCount');
  56
  57     for ($i = 1; $i < $this->_blockCount; $i++) {
  58       if (empty($this->_columnCount[$i])) {
  59         $this->_columnCount[$i] = 5;
  60       }
  61     }
  62
  63     $this->_loadedMappingId = $this->get('savedMapping');
  64
  65     if ($this->get('showSearchForm')) {
  66       $this->assign('showSearchForm', TRUE);
  67     }
  68     else {
  69       $this->assign('showSearchForm', FALSE);
  70     }
  71   }
  72
  73   /**
  74    * Build quick form.
  75    */
  76   public function buildQuickForm() {
  77     $fields = self::fields();
  78     $searchByLabelFields = [];
  79     // This array contain list of available fields and their corresponding data type,
  80     //  later assigned as json string, to be used to filter list of mysql operators
  81     $fieldNameTypes = [];
  82     foreach ($fields as $name => $field) {
  83       // Assign date type to respective field name, which will be later used to modify operator list
  84       $fieldNameTypes[$name] = CRM_Utils_Type::typeToString(CRM_Utils_Array::value('type', $field));
  85       // it's necessary to know which of the fields are searchable by label
  86       if (isset($field['searchByLabel']) && $field['searchByLabel']) {
  87         $searchByLabelFields[] = $name;
  88       }
  89     }
  90     // Add javascript
  91     CRM_Core_Resources::singleton()
  92       ->addScriptFile('civicrm', 'templates/CRM/Contact/Form/Search/Builder.js', 1, 'html-header')
  93       ->addSetting([
  94         'searchBuilder' => [
  95           // Index of newly added/expanded block (1-based index)
  96           'newBlock' => $this->get('newBlock'),
  97           'fieldOptions' => self::fieldOptions(),
  98           'searchByLabelFields' => $searchByLabelFields,
  99           'fieldTypes' => $fieldNameTypes,
 100           'generalOperators' => ['' => ts('-operator-')] + CRM_Core_SelectValues::getSearchBuilderOperators(),
 101         ],
 102       ]);
 103     //get the saved search mapping id
 104     $mappingId = NULL;
 105     if ($this->_ssID) {
 106       $mappingId = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $this->_ssID, 'mapping_id');
 107     }
 108
 109     CRM_Core_BAO_Mapping::buildMappingForm($this, $mappingId, $this->_columnCount, $this->_blockCount);
 110
 111     parent::buildQuickForm();
 112   }
 113
 114   /**
 115    * Add local and global form rules.
 116    */
 117   public function addRules() {
 118     $this->addFormRule(['CRM_Contact_Form_Search_Builder', 'formRule'], $this);
 119   }
 120
 121   /**
 122    * Global validation rules for the form.
 123    *
 124    * @param array $values
 125    * @param array $files
 126    * @param CRM_Core_Form $self
 127    *
 128    * @return array
 129    *   list of errors to be posted back to the form
 130    */
 131   public static function formRule($values, $files, $self) {
 132     if (!empty($values['addMore']) || !empty($values['addBlock'])) {
 133       return TRUE;
 134     }
 135     $fields = self::fields();
 136     $fld = CRM_Core_BAO_Mapping::formattedFields($values, TRUE);
 137
 138     $errorMsg = [];
 139     foreach ($fld as $k => $v) {
 140       if (!$v[1]) {
 141         $errorMsg["operator[$v[3]][$v[4]]"] = ts("Please enter the operator.");
 142       }
 143       else {
 144         // CRM-10338
 145         $v[2] = self::checkArrayKeyEmpty($v[2]);
 146
 147         if (in_array($v[1], [
 148           'IS NULL',
 149           'IS NOT NULL',
 150           'IS EMPTY',
 151           'IS NOT EMPTY',
 152         ]) && !empty($v[2])) {
 153           $errorMsg["value[$v[3]][$v[4]]"] = ts('Please clear your value if you want to use %1 operator.', [1 => $v[1]]);
 154         }
 155         elseif (substr($v[0], 0, 7) === 'do_not_' or substr($v[0], 0, 3) === 'is_') {
 156           if (isset($v[2])) {
 157             $v2 = [$v[2]];
 158             if (!isset($v[2])) {
 159               $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a value.");
 160             }
 161
 162             $error = CRM_Utils_Type::validate($v2[0], 'Integer', FALSE);
 163             if ($error != $v2[0]) {
 164               $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a valid value.");
 165             }
 166           }
 167           else {
 168             $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a value.");
 169           }
 170         }
 171         else {
 172           if (substr($v[0], 0, 7) == 'custom_') {
 173             // Get rid of appended location type id
 174             list($fieldKey) = explode('-', $v[0]);
 175             $type = $fields[$fieldKey]['data_type'];
 176
 177             // hack to handle custom data of type state and country
 178             if (in_array($type, [
 179               'Country',
 180               'StateProvince',
 181             ])) {
 182               $type = "Integer";
 183             }
 184           }
 185           else {
 186             $fldName = $v[0];
 187             // FIXME: no idea at this point what to do with this,
 188             // FIXME: but definitely needs fixing.
 189             if (substr($v[0], 0, 13) == 'contribution_') {
 190               $fldName = substr($v[0], 13);
 191             }
 192
 193             $fldValue = $fields[$fldName] ?? NULL;
 194             $fldType = $fldValue['type'] ?? NULL;
 195             $type = CRM_Utils_Type::typeToString($fldType);
 196
 197             if (strstr($v[1], 'IN')) {
 198               if (empty($v[2])) {
 199                 $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a value.");
 200               }
 201             }
 202             // Check Empty values for Integer Or Boolean Or Date type For operators other than IS NULL and IS NOT NULL.
 203             elseif (!in_array($v[1],
 204               ['IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'])
 205             ) {
 206               if ((($type == 'Int' || $type == 'Boolean') && !is_array($v[2]) && !trim($v[2])) && $v[2] != '0') {
 207                 $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a value.");
 208               }
 209               elseif ($type == 'Date' && !trim($v[2])) {
 210                 $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a value.");
 211               }
 212             }
 213           }
 214
 215           if ($type && empty($errorMsg)) {
 216             // check for valid format while using IN Operator
 217             if (strstr($v[1], 'IN')) {
 218               if (!is_array($v[2])) {
 219                 $inVal = trim($v[2]);
 220                 //checking for format to avoid db errors
 221                 if ($type == 'Int') {
 222                   if (!preg_match('/^[A-Za-z0-9\,]+$/', $inVal)) {
 223                     $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter correct Data (in valid format).");
 224                   }
 225                 }
 226                 else {
 227                   if (!preg_match('/^[A-Za-z0-9åäöÅÄÖüÜœŒæÆøØ()\,\s]+$/', $inVal)) {
 228                     $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter correct Data (in valid format).");
 229                   }
 230                 }
 231               }
 232
 233               // Validate each value in parenthesis to avoid db errors
 234               if (empty($errorMsg)) {
 235                 $parenValues = [];
 236                 $parenValues = is_array($v[2]) ? (array_key_exists($v[1], $v[2])) ? $v[2][$v[1]] : $v[2] : explode(',', trim($inVal, "(..)"));
 237                 foreach ($parenValues as $val) {
 238                   if ($type == 'Date' || $type == 'Timestamp') {
 239                     $val = CRM_Utils_Date::processDate($val);
 240                     if ($type == 'Date') {
 241                       $val = substr($val, 0, 8);
 242                     }
 243                   }
 244                   else {
 245                     $val = trim($val);
 246                   }
 247                   if (!$val && $val != '0') {
 248                     $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter the values correctly.");
 249                   }
 250                   if (empty($errorMsg)) {
 251                     $error = CRM_Utils_Type::validate($val, $type, FALSE);
 252                     if ($error != $val) {
 253                       $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a valid value.");
 254                     }
 255                   }
 256                 }
 257               }
 258             }
 259             elseif (trim($v[2])) {
 260               //else check value for rest of the Operators
 261               if ($type == 'Date' || $type == 'Timestamp') {
 262                 $v[2] = CRM_Utils_Date::processDate($v[2]);
 263                 if ($type == 'Date') {
 264                   $v[2] = substr($v[2], 0, 8);
 265                 }
 266               }
 267               $error = CRM_Utils_Type::validate($v[2], $type, FALSE);
 268               if ($error != $v[2]) {
 269                 $errorMsg["value[$v[3]][$v[4]]"] = ts("Please enter a valid value.");
 270               }
 271             }
 272           }
 273         }
 274       }
 275     }
 276
 277     if (!empty($errorMsg)) {
 278       $self->set('showSearchForm', TRUE);
 279       $self->assign('rows', NULL);
 280       return $errorMsg;
 281     }
 282
 283     return TRUE;
 284   }
 285
 286   /**
 287    * Normalise form values.
 288    */
 289   public function normalizeFormValues() {
 290   }
 291
 292   /**
 293    * Convert form values.
 294    *
 295    * @param array $formValues
 296    *
 297    * @return array
 298    */
 299   public function convertFormValues(&$formValues) {
 300     return CRM_Core_BAO_Mapping::formattedFields($formValues);
 301   }
 302
 303   /**
 304    * Get return properties.
 305    *
 306    * @return array
 307    */
 308   public function &returnProperties() {
 309     return CRM_Core_BAO_Mapping::returnProperties($this->_formValues);
 310   }
 311
 312   /**
 313    * Process the uploaded file.
 314    */
 315   public function postProcess() {
 316     $this->set('isAdvanced', '2');
 317     $this->set('isSearchBuilder', '1');
 318     $this->set('showSearchForm', FALSE);
 319
 320     $params = $this->controller->exportValues($this->_name);
 321     if (!empty($params)) {
 322       // Add another block
 323       if (!empty($params['addBlock'])) {
 324         $this->set('newBlock', $this->_blockCount);
 325         $this->_blockCount += 3;
 326         $this->set('blockCount', $this->_blockCount);
 327         $this->set('showSearchForm', TRUE);
 328         return;
 329       }
 330       // Add another field
 331       $addMore = $params['addMore'] ?? NULL;
 332       for ($x = 1; $x <= $this->_blockCount; $x++) {
 333         if (!empty($addMore[$x])) {
 334           $this->set('newBlock', $x);
 335           $this->_columnCount[$x] = $this->_columnCount[$x] + 5;
 336           $this->set('columnCount', $this->_columnCount);
 337           $this->set('showSearchForm', TRUE);
 338           return;
 339         }
 340       }
 341       $this->set('newBlock', NULL);
 342       $checkEmpty = NULL;
 343       foreach ($params['mapper'] as $key => $value) {
 344         foreach ($value as $k => $v) {
 345           if ($v[0]) {
 346             $checkEmpty++;
 347           }
 348         }
 349       }
 350
 351       if (!$checkEmpty) {
 352         $this->set('newBlock', 1);
 353         CRM_Utils_System::redirect(CRM_Utils_System::url('civicrm/contact/search/builder', '_qf_Builder_display=true'));
 354       }
 355     }
 356
 357     // get user submitted values
 358     // get it from controller only if form has been submitted, else preProcess has set this
 359     if (!empty($_POST)) {
 360       $this->_formValues = $this->controller->exportValues($this->_name);
 361
 362       // set the group if group is submitted
 363       if (!empty($this->_formValues['uf_group_id'])) {
 364         $this->set('id', $this->_formValues['uf_group_id']);
 365       }
 366       else {
 367         $this->set('id', '');
 368       }
 369     }
 370
 371     // we dont want to store the sortByCharacter in the formValue, it is more like
 372     // a filter on the result set
 373     // this filter is reset if we click on the search button
 374     if ($this->_sortByCharacter !== NULL && empty($_POST)) {
 375       if (strtolower($this->_sortByCharacter) == 'all') {
 376         $this->_formValues['sortByCharacter'] = NULL;
 377       }
 378       else {
 379         $this->_formValues['sortByCharacter'] = $this->_sortByCharacter;
 380       }
 381     }
 382     else {
 383       $this->_sortByCharacter = NULL;
 384     }
 385
 386     $this->_params = $this->convertFormValues($this->_formValues);
 387     $this->_returnProperties = &$this->returnProperties();
 388
 389     // CRM-10338 check if value is empty array
 390     foreach ($this->_params as $k => $v) {
 391       $this->_params[$k][2] = self::checkArrayKeyEmpty($v[2]);
 392     }
 393
 394     parent::postProcess();
 395   }
 396
 397   /**
 398    * Get fields.
 399    *
 400    * @return array
 401    */
 402   public static function fields() {
 403     $fields = array_merge(
 404       CRM_Contact_BAO_Contact::exportableFields('All', FALSE, TRUE),
 405       CRM_Core_Component::getQueryFields(),
 406       CRM_Contact_BAO_Query_Hook::singleton()->getFields(),
 407       CRM_Activity_BAO_Activity::exportableFields()
 408     );
 409     return $fields;
 410   }
 411
 412   /**
 413    * CRM-9434 Hackish function to fetch fields with options.
 414    *
 415    * FIXME: When our core fields contain reliable metadata this will be much simpler.
 416    * @return array
 417    *   (string => string) key: field_name value: api entity name
 418    *   Note: options are fetched via ajax using the api "getoptions" method
 419    */
 420   public static function fieldOptions() {
 421     // Hack to add options not retrieved by getfields
 422     // This list could go on and on, but it would be better to fix getfields
 423     $options = [
 424       'group' => 'group_contact',
 425       'tag' => 'entity_tag',
 426       'on_hold' => 'yesno',
 427       'is_bulkmail' => 'yesno',
 428       'payment_instrument' => 'contribution',
 429       'membership_status' => 'membership',
 430       'membership_type' => 'membership',
 431       'member_campaign_id' => 'membership',
 432       'member_is_test' => 'yesno',
 433       'member_is_pay_later' => 'yesno',
 434       'is_override' => 'yesno',
 435     ];
 436     $entities = [
 437       'contact',
 438       'address',
 439       'activity',
 440       'participant',
 441       'pledge',
 442       'member',
 443       'contribution',
 444       'case',
 445       'grant',
 446     ];
 447     CRM_Contact_BAO_Query_Hook::singleton()->alterSearchBuilderOptions($entities, $options);
 448     foreach ($entities as $entity) {
 449       $fields = civicrm_api3($entity, 'getfields');
 450       foreach ($fields['values'] as $field => $info) {
 451         if (!empty($info['options']) || !empty($info['pseudoconstant']) || !empty($info['option_group_id'])) {
 452           $options[$field] = $entity;
 453           // Hack for when search field doesn't match db field - e.g. "country" instead of "country_id"
 454           if (substr($field, -3) == '_id') {
 455             $options[substr($field, 0, -3)] = $entity;
 456           }
 457         }
 458         elseif (!empty($info['data_type'])) {
 459           if (in_array($info['data_type'], ['StateProvince', 'Country'])) {
 460             $options[$field] = $entity;
 461           }
 462         }
 463         elseif (in_array(substr($field, 0, 3), [
 464           'is_',
 465           'do_',
 466         ]) || CRM_Utils_Array::value('data_type', $info) == 'Boolean'
 467         ) {
 468           $options[$field] = 'yesno';
 469           if ($entity != 'contact') {
 470             $options[$entity . '_' . $field] = 'yesno';
 471           }
 472         }
 473         elseif (strpos($field, '_is_')) {
 474           $options[$field] = 'yesno';
 475         }
 476       }
 477     }
 478     return $options;
 479   }
 480
 481   /**
 482    * CRM-10338 tags and groups use array keys for selection list.
 483    *
 484    * if using IS NULL/NOT NULL, an array with no array key is created
 485    * convert that to simple NULL so processing can proceed
 486    *
 487    * @param string $val
 488    *
 489    * @return null
 490    */
 491   public static function checkArrayKeyEmpty($val) {
 492     if (is_array($val)) {
 493       $v2empty = TRUE;
 494       foreach ($val as $vk => $vv) {
 495         if (!empty($vk)) {
 496           $v2empty = FALSE;
 497         }
 498       }
 499       if ($v2empty) {
 500         $val = NULL;
 501       }
 502     }
 503     return $val;
 504   }
 505
 506 }