CRM/Core/BAO/CustomValueTable.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Core_BAO_CustomValueTable {
  36
  37   /**
  38    * @param $customParams
  39    *
  40    * @throws Exception
  41    */
  42   static function create(&$customParams) {
  43     if (empty($customParams) ||
  44       !is_array($customParams)
  45     ) {
  46       return;
  47     }
  48
  49     foreach ($customParams as $tableName => $tables) {
  50       foreach ($tables as $index => $fields) {
  51         $sqlOP      = NULL;
  52         $hookID     = NULL;
  53         $hookOP     = NULL;
  54         $entityID   = NULL;
  55         $isMultiple = FALSE;
  56         $set        = array();
  57         $params     = array();
  58         $count      = 1;
  59         foreach ($fields as $field) {
  60           if (!$sqlOP) {
  61             $entityID   = $field['entity_id'];
  62             $hookID     = $field['custom_group_id'];
  63             $isMultiple = $field['is_multiple'];
  64             if (array_key_exists('id', $field)) {
  65               $sqlOP          = "UPDATE $tableName ";
  66               $where          = " WHERE  id = %{$count}";
  67               $params[$count] = array($field['id'], 'Integer');
  68               $count++;
  69               $hookOP = 'edit';
  70             }
  71             else {
  72               $sqlOP  = "INSERT INTO $tableName ";
  73               $where  = NULL;
  74               $hookOP = 'create';
  75             }
  76           }
  77
  78           // fix the value before we store it
  79           $value = $field['value'];
  80           $type = $field['type'];
  81           switch ($type) {
  82             case 'StateProvince':
  83               $type = 'Integer';
  84               if (is_array($value)) {
  85                 $value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $value) . CRM_Core_DAO::VALUE_SEPARATOR;
  86                 $type = 'String';
  87               }
  88               elseif (!is_numeric($value)) {
  89                 //fix for multi select state, CRM-3437
  90                 $mulValues = explode(',', $value);
  91                 $validStates = array();
  92                 foreach ($mulValues as $key => $stateVal) {
  93                   $states = array();
  94                   $states['state_province'] = trim($stateVal);
  95
  96                   CRM_Utils_Array::lookupValue($states, 'state_province',
  97                     CRM_Core_PseudoConstant::stateProvince(), TRUE
  98                   );
  99                   if (empty($states['state_province_id'])) {
 100                     CRM_Utils_Array::lookupValue($states, 'state_province',
 101                       CRM_Core_PseudoConstant::stateProvinceAbbreviation(), TRUE
 102                     );
 103                   }
 104                   $validStates[] = $states['state_province_id'];
 105                 }
 106                 $value = implode(CRM_Core_DAO::VALUE_SEPARATOR,
 107                   $validStates
 108                 );
 109                 $type = 'String';
 110               }
 111               elseif (!$value) {
 112                 // CRM-3415
 113                 // using type of timestamp allows us to sneak in a null into db
 114                 // gross but effective hack
 115                 $value = NULL;
 116                 $type = 'Timestamp';
 117               }
 118               break;
 119
 120             case 'Country':
 121               $type = 'Integer';
 122               if (is_array($value)) {
 123                 $value = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $value) . CRM_Core_DAO::VALUE_SEPARATOR;
 124                 $type = 'String';
 125               }
 126               elseif (!is_numeric($value)) {
 127                 //fix for multi select country, CRM-3437
 128                 $mulValues = explode(',', $value);
 129                 $validCountries = array();
 130                 foreach ($mulValues as $key => $countryVal) {
 131                   $countries = array();
 132                   $countries['country'] = trim($countryVal);
 133                   CRM_Utils_Array::lookupValue($countries, 'country',
 134                     CRM_Core_PseudoConstant::country(), TRUE
 135                   );
 136                   if (empty($countries['country_id'])) {
 137                     CRM_Utils_Array::lookupValue($countries, 'country',
 138                       CRM_Core_PseudoConstant::countryIsoCode(), TRUE
 139                     );
 140                   }
 141                   $validCountries[] = $countries['country_id'];
 142                 }
 143                 $value = implode(CRM_Core_DAO::VALUE_SEPARATOR,
 144                   $validCountries
 145                 );
 146                 $type = 'String';
 147               }
 148               elseif (!$value) {
 149                 // CRM-3415
 150                 // using type of timestamp allows us to sneak in a null into db
 151                 // gross but effective hack
 152                 $value = NULL;
 153                 $type = 'Timestamp';
 154               }
 155               break;
 156
 157             case 'File':
 158               if (!$field['file_id']) {
 159                 CRM_Core_Error::fatal();
 160               }
 161
 162               // need to add/update civicrm_entity_file
 163               $entityFileDAO = new CRM_Core_DAO_EntityFile();
 164               $entityFileDAO->file_id = $field['file_id'];
 165               $entityFileDAO->find(TRUE);
 166
 167               $entityFileDAO->entity_table = $field['table_name'];
 168               $entityFileDAO->entity_id = $field['entity_id'];
 169               $entityFileDAO->file_id = $field['file_id'];
 170               $entityFileDAO->save();
 171               $entityFileDAO->free();
 172               $value = $field['file_id'];
 173               $type = 'String';
 174               break;
 175
 176             case 'Date':
 177               $value = CRM_Utils_Date::isoToMysql($value);
 178               break;
 179
 180             case 'Int':
 181               if (is_numeric($value)) {
 182                 $type = 'Integer';
 183               }
 184               else {
 185                 $type = 'Timestamp';
 186               }
 187               break;
 188
 189             case 'ContactReference':
 190               if ($value == NULL) {
 191                 $type = 'Timestamp';
 192               }
 193               else {
 194                 $type = 'Integer';
 195               }
 196               break;
 197
 198             case 'RichTextEditor':
 199               $type = 'String';
 200               break;
 201
 202             case 'Boolean':
 203               //fix for CRM-3290
 204               $value = CRM_Utils_String::strtoboolstr($value);
 205               if ($value === FALSE) {
 206                 $type = 'Timestamp';
 207               }
 208               break;
 209
 210             default:
 211               break;
 212           }
 213           $set[$field['column_name']] = "%{$count}";
 214           $params[$count] = array($value, $type);
 215           $count++;
 216         }
 217
 218         if (!empty($set)) {
 219           $setClause = array();
 220           foreach ($set as $n => $v) {
 221             $setClause[] = "$n = $v";
 222           }
 223           $setClause = implode(',', $setClause);
 224           if (!$where) {
 225             // do this only for insert
 226             $set['entity_id'] = "%{$count}";
 227             $params[$count] = array($entityID, 'Integer');
 228             $count++;
 229
 230             $fieldNames  = implode(',', array_keys($set));
 231             $fieldValues = implode(',', array_values($set));
 232             $query       = "$sqlOP ( $fieldNames ) VALUES ( $fieldValues )";
 233             // for multiple values we dont do on duplicate key update
 234             if (!$isMultiple) {
 235               $query .= " ON DUPLICATE KEY UPDATE $setClause";
 236             }
 237           }
 238           else {
 239             $query = "$sqlOP SET $setClause $where";
 240           }
 241           $dao = CRM_Core_DAO::executeQuery($query, $params);
 242
 243           CRM_Utils_Hook::custom($hookOP,
 244             $hookID,
 245             $entityID,
 246             $fields
 247           );
 248         }
 249       }
 250     }
 251   }
 252
 253   /**
 254    * given a field return the mysql data type associated with it
 255    *
 256    * @param string $type the civicrm type string
 257    *
 258    * @param int $maxLength
 259    *
 260    * @return the mysql data store placeholder
 261    * @access public
 262    * @static
 263    */
 264   public static function fieldToSQLType($type, $maxLength = 255) {
 265     if (!isset($maxLength) ||
 266       !is_numeric($maxLength) ||
 267       $maxLength <= 0
 268     ) {
 269       $maxLength = 255;
 270     }
 271
 272     switch ($type) {
 273       case 'String':
 274       case 'Link':
 275         return "varchar($maxLength)";
 276
 277       case 'Boolean':
 278         return 'tinyint';
 279
 280       case 'Int':
 281         return 'int';
 282       // the below three are FK's, and have constraints added to them
 283
 284       case 'ContactReference':
 285       case 'StateProvince':
 286       case 'Country':
 287       case 'File':
 288         return 'int unsigned';
 289
 290       case 'Float':
 291         return 'double';
 292
 293       case 'Money':
 294         return 'decimal(20,2)';
 295
 296       case 'Memo':
 297       case 'RichTextEditor':
 298         return 'text';
 299
 300       case 'Date':
 301         return 'datetime';
 302
 303       default:
 304         CRM_Core_Error::fatal();
 305     }
 306   }
 307
 308   /**
 309    * @param $params
 310    * @param $entityTable
 311    * @param $entityID
 312    */
 313   static function store(&$params, $entityTable, $entityID) {
 314     $cvParams = array();
 315     foreach ($params as $fieldID => $param) {
 316       foreach ($param as $index => $customValue) {
 317         $cvParam = array(
 318           'entity_table' => $entityTable,
 319           'entity_id' => $entityID,
 320           'value' => $customValue['value'],
 321           'type' => $customValue['type'],
 322           'custom_field_id' => $customValue['custom_field_id'],
 323           'custom_group_id' => $customValue['custom_group_id'],
 324           'table_name' => $customValue['table_name'],
 325           'column_name' => $customValue['column_name'],
 326           'is_multiple' => CRM_Utils_Array::value('is_multiple', $customValue),
 327           'file_id' => $customValue['file_id'],
 328         );
 329
 330         // fix Date type to be timestamp, since that is how we store in db
 331         if ($cvParam['type'] == 'Date') {
 332           $cvParam['type'] = 'Timestamp';
 333         }
 334
 335         if (!empty($customValue['id'])) {
 336           $cvParam['id'] = $customValue['id'];
 337         }
 338         if (!array_key_exists($customValue['table_name'], $cvParams)) {
 339           $cvParams[$customValue['table_name']] = array();
 340         }
 341
 342         if (!array_key_exists($index, $cvParams[$customValue['table_name']])) {
 343           $cvParams[$customValue['table_name']][$index] = array();
 344         }
 345
 346         $cvParams[$customValue['table_name']][$index][] = $cvParam;
 347       }
 348     }
 349     if (!empty($cvParams)) {
 350       self::create($cvParams);
 351     }
 352   }
 353
 354   /**
 355    * @param $params
 356    * @param $customFields
 357    * @param $entityTable
 358    * @param $entityID
 359    * @param $customFieldExtends
 360    */
 361   static function postProcess(&$params, &$customFields, $entityTable, $entityID, $customFieldExtends) {
 362     $customData = CRM_Core_BAO_CustomField::postProcess($params,
 363       $customFields,
 364       $entityID,
 365       $customFieldExtends
 366     );
 367
 368     if (!empty($customData)) {
 369       self::store($customData, $entityTable, $entityID);
 370     }
 371   }
 372
 373   /**
 374    * Return an array of all custom values associated with an entity.
 375    *
 376    * @param int $entityID Identification number of the entity
 377    * @param string $entityType Type of entity that the entityID corresponds to, specified
 378    *                                   as a string with format "'<EntityName>'". Comma separated
 379    *                                   list may be used to specify OR matches. Allowable values
 380    *                                   are enumerated types in civicrm_custom_group.extends field.
 381    *                                   Optional. Default value assumes entityID references a
 382    *                                   contact entity.
 383    * @param array $fieldIDs optional list of fieldIDs that we want to retrieve. If this
 384    *                                   is set the entityType is ignored
 385    *
 386    * @param bool $formatMultiRecordField
 387    *
 388    * @return array      $fields        Array of custom values for the entity with key=>value
 389    *                                   pairs specified as civicrm_custom_field.id => custom value.
 390    *                                   Empty array if no custom values found.
 391    * @access public
 392    * @static
 393    */
 394   public static function &getEntityValues($entityID, $entityType = NULL, $fieldIDs = NULL, $formatMultiRecordField = FALSE) {
 395     if (!$entityID) {
 396       // adding this here since an empty contact id could have serious repurcussions
 397       // like looping forever
 398       CRM_Core_Error::fatal('Please file an issue with the backtrace');
 399       return NULL;
 400     }
 401
 402     $cond = array();
 403     if ($entityType) {
 404       $cond[] = "cg.extends IN ( '$entityType' )";
 405     }
 406     if ($fieldIDs &&
 407       is_array($fieldIDs)
 408     ) {
 409       $fieldIDList = implode(',', $fieldIDs);
 410       $cond[] = "cf.id IN ( $fieldIDList )";
 411     }
 412     if (empty($cond)) {
 413       $cond[] = "cg.extends IN ( 'Contact', 'Individual', 'Household', 'Organization' )";
 414     }
 415     $cond = implode(' AND ', $cond);
 416
 417     // first find all the fields that extend this type of entity
 418     $query = "
 419 SELECT cg.table_name,
 420        cg.id as groupID,
 421        cg.is_multiple,
 422        cf.column_name,
 423        cf.id as fieldID,
 424        cf.data_type as fieldDataType
 425 FROM   civicrm_custom_group cg,
 426        civicrm_custom_field cf
 427 WHERE  cf.custom_group_id = cg.id
 428 AND    cg.is_active = 1
 429 AND    cf.is_active = 1
 430 AND    $cond
 431 ";
 432     $dao = CRM_Core_DAO::executeQuery($query);
 433
 434     $select = $fields = $isMultiple = array();
 435
 436     while ($dao->fetch()) {
 437       if (!array_key_exists($dao->table_name, $select)) {
 438         $fields[$dao->table_name] = array();
 439         $select[$dao->table_name] = array();
 440       }
 441       $fields[$dao->table_name][] = $dao->fieldID;
 442       $select[$dao->table_name][] = "{$dao->column_name} AS custom_{$dao->fieldID}";
 443       $isMultiple[$dao->table_name] = $dao->is_multiple ? TRUE : FALSE;
 444       $file[$dao->table_name][$dao->fieldID] = $dao->fieldDataType;
 445     }
 446
 447     $result = array();
 448     foreach ($select as $tableName => $clauses) {
 449       $query = "SELECT id, " . implode(', ', $clauses) . " FROM $tableName WHERE entity_id = $entityID";
 450       $dao = CRM_Core_DAO::executeQuery($query);
 451       while ($dao->fetch()) {
 452         foreach ($fields[$tableName] as $fieldID) {
 453           $fieldName = "custom_{$fieldID}";
 454           if ($isMultiple[$tableName]) {
 455             if ($formatMultiRecordField) {
 456               $result["{$dao->id}"]["{$fieldID}"] = $dao->$fieldName;
 457             } else {
 458               $result["{$fieldID}_{$dao->id}"] = $dao->$fieldName;
 459             }
 460           }
 461           else {
 462             $result[$fieldID] = $dao->$fieldName;
 463           }
 464         }
 465       }
 466     }
 467     return $result;
 468   }
 469
 470   /**
 471    * Function to take in an array of entityID, custom_XXX => value
 472    * and set the value in the appropriate table. Should also be able
 473    * to set the value to null. Follows api parameter/return conventions
 474    *
 475    * @array $params
 476    *
 477    * @param $params
 478    *
 479    * @throws Exception
 480    * @return array
 481    * @static
 482    */
 483   static function setValues(&$params) {
 484
 485     if (!isset($params['entityID']) ||
 486       CRM_Utils_Type::escape($params['entityID'], 'Integer', FALSE) === NULL
 487     ) {
 488       return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
 489     }
 490
 491     // first collect all the id/value pairs. The format is:
 492     // custom_X => value or custom_X_VALUEID => value (for multiple values), VALUEID == -1, -2 etc for new insertions
 493     $values = array();
 494     $fieldValues = array();
 495     foreach ($params as $n => $v) {
 496       if ($customFieldInfo = CRM_Core_BAO_CustomField::getKeyID($n, TRUE)) {
 497         $fieldID = (int ) $customFieldInfo[0];
 498         if (CRM_Utils_Type::escape($fieldID, 'Integer', FALSE) === NULL) {
 499           return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
 500               array(1 => $fieldID)
 501             ));
 502         }
 503         if (!array_key_exists($fieldID, $fieldValues)) {
 504           $fieldValues[$fieldID] = array();
 505         }
 506         $id = -1;
 507         if ($customFieldInfo[1]) {
 508           $id = (int ) $customFieldInfo[1];
 509         }
 510         $fieldValues[$fieldID][] = array(
 511           'value' => $v,
 512           'id' => $id,
 513         );
 514       }
 515     }
 516
 517     $fieldIDList = implode(',', array_keys($fieldValues));
 518
 519     // format it so that we can just use create
 520     $sql = "
 521 SELECT cg.table_name  as table_name ,
 522        cg.id          as cg_id      ,
 523        cg.is_multiple as is_multiple,
 524        cf.column_name as column_name,
 525        cf.id          as cf_id      ,
 526        cf.data_type   as data_type
 527 FROM   civicrm_custom_group cg,
 528        civicrm_custom_field cf
 529 WHERE  cf.custom_group_id = cg.id
 530 AND    cf.id IN ( $fieldIDList )
 531 ";
 532
 533     $dao = CRM_Core_DAO::executeQuery($sql);
 534     $cvParams = array();
 535
 536     while ($dao->fetch()) {
 537       $dataType = $dao->data_type == 'Date' ? 'Timestamp' : $dao->data_type;
 538       foreach ($fieldValues[$dao->cf_id] as $fieldValue) {
 539         // Format null values correctly
 540         if ($fieldValue['value'] === NULL || $fieldValue['value'] === '') {
 541           switch ($dataType) {
 542             case 'String':
 543             case 'Int':
 544             case 'Link':
 545             case 'Boolean':
 546               $fieldValue['value'] = '';
 547               break;
 548
 549             case 'Timestamp':
 550               $fieldValue['value'] = NULL;
 551               break;
 552
 553             case 'StateProvince':
 554             case 'Country':
 555             case 'Money':
 556             case 'Float':
 557               $fieldValue['value'] = (int)0;
 558               break;
 559           }
 560         }
 561         // Ensure that value is of the right data type
 562         elseif (CRM_Utils_Type::escape($fieldValue['value'], $dataType, FALSE) === NULL) {
 563           return CRM_Core_Error::createAPIError(ts('value: %1 is not of the right field data type: %2',
 564               array(
 565                 1 => $fieldValue['value'],
 566                 2 => $dao->data_type
 567               )
 568             ));
 569         }
 570
 571         $cvParam = array(
 572           'entity_id' => $params['entityID'],
 573           'value' => $fieldValue['value'],
 574           'type' => $dataType,
 575           'custom_field_id' => $dao->cf_id,
 576           'custom_group_id' => $dao->cg_id,
 577           'table_name' => $dao->table_name,
 578           'column_name' => $dao->column_name,
 579           'is_multiple' => $dao->is_multiple,
 580         );
 581
 582         if ($cvParam['type'] == 'File') {
 583           $cvParam['file_id'] = $fieldValue['value'];
 584         }
 585
 586         if (!array_key_exists($dao->table_name, $cvParams)) {
 587           $cvParams[$dao->table_name] = array();
 588         }
 589
 590         if (!array_key_exists($fieldValue['id'], $cvParams[$dao->table_name])) {
 591           $cvParams[$dao->table_name][$fieldValue['id']] = array();
 592         }
 593
 594         if ($fieldValue['id'] > 0) {
 595           $cvParam['id'] = $fieldValue['id'];
 596         }
 597         $cvParams[$dao->table_name][$fieldValue['id']][] = $cvParam;
 598       }
 599     }
 600
 601     if (!empty($cvParams)) {
 602       self::create($cvParams);
 603       return array('is_error' => 0, 'result' => 1);
 604     }
 605
 606     return CRM_Core_Error::createAPIError(ts('Unknown error'));
 607   }
 608
 609   /**
 610    * Function to take in an array of entityID, custom_ID
 611    * and gets the value from the appropriate table.
 612    *
 613    * To get the values of custom fields with IDs 13 and 43 for contact ID 1327, use:
 614    * $params = array( 'entityID' => 1327, 'custom_13' => 1, 'custom_43' => 1 );
 615    *
 616    * Entity Type will be infered by the custom fields you request
 617    * Specify $params['entityType'] if you do not supply any custom fields to return
 618    * and entity type is other than Contact
 619    *
 620    * @array $params
 621    *
 622    * @param $params
 623    *
 624    * @throws Exception
 625    * @return array
 626    * @static
 627    */
 628   static function &getValues(&$params) {
 629     if (empty($params)) {
 630       return NULL;
 631     }
 632     if (!isset($params['entityID']) ||
 633       CRM_Utils_Type::escape($params['entityID'],
 634         'Integer', FALSE
 635       ) === NULL
 636     ) {
 637       return CRM_Core_Error::createAPIError(ts('entityID needs to be set and of type Integer'));
 638     }
 639
 640     // first collect all the ids. The format is:
 641     // custom_ID
 642     $fieldIDs = array();
 643     foreach ($params as $n => $v) {
 644       $key = $idx = NULL;
 645       if (substr($n, 0, 7) == 'custom_') {
 646         $idx = substr($n, 7);
 647         if (CRM_Utils_Type::escape($idx, 'Integer', FALSE) === NULL) {
 648           return CRM_Core_Error::createAPIError(ts('field ID needs to be of type Integer for index %1',
 649               array(1 => $idx)
 650             ));
 651         }
 652         $fieldIDs[] = (int ) $idx;
 653       }
 654     }
 655
 656     $default = array('Contact', 'Individual', 'Household', 'Organization');
 657     if (!($type = CRM_Utils_Array::value('entityType', $params)) ||
 658       in_array($params['entityType'], $default)
 659     ) {
 660       $type = NULL;
 661     }
 662     else {
 663       $entities = CRM_Core_SelectValues::customGroupExtends();
 664       if (!array_key_exists($type, $entities)) {
 665         if (in_array($type, $entities)) {
 666           $type = $entities[$type];
 667           if (in_array($type, $default)) {
 668             $type = NULL;
 669           }
 670         }
 671         else {
 672           return CRM_Core_Error::createAPIError(ts('Invalid entity type') . ': "' . $type . '"');
 673         }
 674       }
 675     }
 676
 677     $values = self::getEntityValues($params['entityID'],
 678       $type,
 679       $fieldIDs
 680     );
 681     if (empty($values)) {
 682       // note that this behaviour is undesirable from an API point of view - it should return an empty array
 683       // since this is also called by the merger code & not sure the consequences of changing
 684       // are just handling undoing this in the api layer. ie. converting the error back into a success
 685       $result = array(
 686         'is_error' => 1,
 687         'error_message' => 'No values found for the specified entity ID and custom field(s).',
 688       );
 689       return $result;
 690     }
 691     else {
 692       $result = array(
 693         'is_error' => 0,
 694         'entityID' => $params['entityID'],
 695       );
 696       foreach ($values as $id => $value) {
 697         $result["custom_{$id}"] = $value;
 698       }
 699       return $result;
 700     }
 701   }
 702 }
 703