CRM/Core/DAO.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  * Base Database Access Object class.
  14  *
  15  * All DAO classes should inherit from this class.
  16  *
  17  * @package CRM
  18  * @copyright CiviCRM LLC https://civicrm.org/licensing
  19  */
  20
  21 if (!defined('DB_DSN_MODE')) {
  22   define('DB_DSN_MODE', 'auto');
  23 }
  24
  25 require_once 'PEAR.php';
  26 require_once 'DB/DataObject.php';
  27
  28 require_once 'CRM/Core/I18n.php';
  29
  30 /**
  31  * Class CRM_Core_DAO
  32  */
  33 class CRM_Core_DAO extends DB_DataObject {
  34
  35   /**
  36    * How many times has this instance been cloned.
  37    *
  38    * @var int
  39    */
  40   protected $resultCopies = 0;
  41
  42   /**
  43    * @var null
  44    * @deprecated
  45    */
  46   public static $_nullObject = NULL;
  47
  48   /**
  49    * Icon associated with this entity.
  50    *
  51    * @var string
  52    */
  53   public static $_icon = NULL;
  54
  55   /**
  56    * Field to show when displaying a record.
  57    *
  58    * @var string
  59    */
  60   public static $_labelField = NULL;
  61
  62   /**
  63    * @var array
  64    * @deprecated
  65    */
  66   public static $_nullArray = [];
  67
  68   public static $_dbColumnValueCache = NULL;
  69   const NOT_NULL = 1, IS_NULL = 2,
  70     DB_DAO_NOTNULL = 128,
  71     VALUE_SEPARATOR = "\ 1",
  72     BULK_INSERT_COUNT = 200,
  73     BULK_INSERT_HIGH_COUNT = 200,
  74     QUERY_FORMAT_WILDCARD = 1,
  75     QUERY_FORMAT_NO_QUOTES = 2,
  76
  77     /**
  78      * Serialized string separated by and bookended with VALUE_SEPARATOR
  79      */
  80     SERIALIZE_SEPARATOR_BOOKEND = 1,
  81     /**
  82      * @deprecated format separated by VALUE_SEPARATOR
  83      */
  84     SERIALIZE_SEPARATOR_TRIMMED = 2,
  85     /**
  86      * Recommended serialization format
  87      */
  88     SERIALIZE_JSON = 3,
  89     /**
  90      * @deprecated format using php serialize()
  91      */
  92     SERIALIZE_PHP = 4,
  93     /**
  94      * Comma separated string, no quotes, no spaces
  95      */
  96     SERIALIZE_COMMA = 5;
  97
  98   /**
  99    * Define entities that shouldn't be created or deleted when creating/ deleting
 100    * test objects - this prevents world regions, countries etc from being added / deleted
 101    * @var array
 102    */
 103   public static $_testEntitiesToSkip = [];
 104   /**
 105    * The factory class for this application.
 106    * @var object
 107    */
 108   public static $_factory = NULL;
 109
 110   public static $_checkedSqlFunctionsExist = FALSE;
 111
 112   /**
 113    * https://issues.civicrm.org/jira/browse/CRM-17748
 114    * internal variable for DAO to hold per-query settings
 115    * @var array
 116    */
 117   protected $_options = [];
 118
 119   /**
 120    * Class constructor.
 121    *
 122    * @return \CRM_Core_DAO
 123    */
 124   public function __construct() {
 125     $this->initialize();
 126     $this->__table = $this->getTableName();
 127   }
 128
 129   /**
 130    * Returns localized title of this entity.
 131    *
 132    * @return string
 133    */
 134   public static function getEntityTitle() {
 135     $className = static::class;
 136     CRM_Core_Error::deprecatedWarning("$className needs to be regenerated. Missing getEntityTitle method.");
 137     return CRM_Core_DAO_AllCoreTables::getBriefName($className);
 138   }
 139
 140   /**
 141    * Returns user-friendly description of this entity.
 142    *
 143    * @return string|null
 144    */
 145   public static function getEntityDescription() {
 146     return NULL;
 147   }
 148
 149   public function __clone() {
 150     if (!empty($this->_DB_resultid)) {
 151       $this->resultCopies++;
 152     }
 153   }
 154
 155   /**
 156    * Class destructor.
 157    */
 158   public function __destruct() {
 159     if ($this->resultCopies === 0) {
 160       $this->free();
 161     }
 162     $this->resultCopies--;
 163   }
 164
 165   /**
 166    * Returns the name of this table
 167    *
 168    * @return string
 169    */
 170   public static function getTableName() {
 171     return self::getLocaleTableName(static::$_tableName ?? NULL);
 172   }
 173
 174   /**
 175    * Returns if this table needs to be logged
 176    *
 177    * @return bool
 178    */
 179   public function getLog() {
 180     return static::$_log ?? FALSE;
 181   }
 182
 183   /**
 184    * Initialize the DAO object.
 185    *
 186    * @param string $dsn
 187    *   The database connection string.
 188    */
 189   public static function init($dsn) {
 190     Civi::$statics[__CLASS__]['init'] = 1;
 191     $options = &PEAR::getStaticProperty('DB_DataObject', 'options');
 192     $dsn = CRM_Utils_SQL::autoSwitchDSN($dsn);
 193     $options['database'] = $dsn;
 194     $options['quote_identifiers'] = TRUE;
 195     if (CRM_Utils_SQL::isSSLDSN($dsn)) {
 196       // There are two different options arrays.
 197       $other_options = &PEAR::getStaticProperty('DB', 'options');
 198       $other_options['ssl'] = TRUE;
 199     }
 200     if (defined('CIVICRM_DAO_DEBUG')) {
 201       self::DebugLevel(CIVICRM_DAO_DEBUG);
 202     }
 203     $factory = new CRM_Contact_DAO_Factory();
 204     CRM_Core_DAO::setFactory($factory);
 205     CRM_Core_DAO::executeQuery('SET NAMES utf8mb4');
 206     CRM_Core_DAO::executeQuery('SET @uniqueID = %1', [1 => [CRM_Utils_Request::id(), 'String']]);
 207   }
 208
 209   /**
 210    * @return DB_common
 211    */
 212   public static function getConnection() {
 213     global $_DB_DATAOBJECT;
 214     $dao = new CRM_Core_DAO();
 215     return $_DB_DATAOBJECT['CONNECTIONS'][$dao->_database_dsn_md5];
 216   }
 217
 218   /**
 219    * Disables usage of the ONLY_FULL_GROUP_BY Mode if necessary
 220    */
 221   public static function disableFullGroupByMode() {
 222     $currentModes = CRM_Utils_SQL::getSqlModes();
 223     if (in_array('ONLY_FULL_GROUP_BY', $currentModes) && CRM_Utils_SQL::isGroupByModeInDefault()) {
 224       $key = array_search('ONLY_FULL_GROUP_BY', $currentModes);
 225       unset($currentModes[$key]);
 226       CRM_Core_DAO::executeQuery("SET SESSION sql_mode = %1", [1 => [implode(',', $currentModes), 'String']]);
 227     }
 228   }
 229
 230   /**
 231    * Re-enables ONLY_FULL_GROUP_BY sql_mode as necessary..
 232    */
 233   public static function reenableFullGroupByMode() {
 234     $currentModes = CRM_Utils_SQL::getSqlModes();
 235     if (!in_array('ONLY_FULL_GROUP_BY', $currentModes) && CRM_Utils_SQL::isGroupByModeInDefault()) {
 236       $currentModes[] = 'ONLY_FULL_GROUP_BY';
 237       CRM_Core_DAO::executeQuery("SET SESSION sql_mode = %1", [1 => [implode(',', $currentModes), 'String']]);
 238     }
 239   }
 240
 241   /**
 242    * @param string $fieldName
 243    * @param $fieldDef
 244    * @param array $params
 245    */
 246   protected function assignTestFK($fieldName, $fieldDef, $params) {
 247     $required = $fieldDef['required'] ?? NULL;
 248     $FKClassName = $fieldDef['FKClassName'] ?? NULL;
 249     $dbName = $fieldDef['name'];
 250     $daoName = str_replace('_BAO_', '_DAO_', get_class($this));
 251
 252     // skip the FK if it is not required
 253     // if it's contact id we should create even if not required
 254     // we'll have a go @ fetching first though
 255     // we WILL create campaigns though for so tests with a campaign pseudoconstant will complete
 256     if ($FKClassName === 'CRM_Campaign_DAO_Campaign' && $daoName != $FKClassName) {
 257       $required = TRUE;
 258     }
 259     if (!$required && $dbName != 'contact_id') {
 260       $fkDAO = new $FKClassName();
 261       if ($fkDAO->find(TRUE)) {
 262         $this->$dbName = $fkDAO->id;
 263       }
 264     }
 265
 266     elseif (in_array($FKClassName, CRM_Core_DAO::$_testEntitiesToSkip)) {
 267       $depObject = new $FKClassName();
 268       $depObject->find(TRUE);
 269       $this->$dbName = $depObject->id;
 270     }
 271     elseif ($daoName == 'CRM_Member_DAO_MembershipType' && $fieldName == 'member_of_contact_id') {
 272       // FIXME: the fields() metadata is not specific enough
 273       $depObject = CRM_Core_DAO::createTestObject($FKClassName, ['contact_type' => 'Organization']);
 274       $this->$dbName = $depObject->id;
 275     }
 276     else {
 277       //if it is required we need to generate the dependency object first
 278       $depObject = CRM_Core_DAO::createTestObject($FKClassName, CRM_Utils_Array::value($dbName, $params, 1));
 279       $this->$dbName = $depObject->id;
 280     }
 281   }
 282
 283   /**
 284    * Generate and assign an arbitrary value to a field of a test object.
 285    *
 286    * @param string $fieldName
 287    * @param array $fieldDef
 288    * @param int $counter
 289    *   The globally-unique ID of the test object.
 290    *
 291    * @throws \CRM_Core_Exception
 292    */
 293   protected function assignTestValue($fieldName, &$fieldDef, $counter) {
 294     $dbName = $fieldDef['name'];
 295     $daoName = get_class($this);
 296     $handled = FALSE;
 297
 298     if (!$handled && $dbName == 'contact_sub_type') {
 299       //coming up with a rule to set this is too complex let's not set it
 300       $handled = TRUE;
 301     }
 302
 303     // Pick an option value if needed
 304     if (!$handled && $fieldDef['type'] !== CRM_Utils_Type::T_BOOLEAN) {
 305       $options = $daoName::buildOptions($dbName, 'create');
 306       if ($options) {
 307         $this->$dbName = key($options);
 308         $handled = TRUE;
 309       }
 310     }
 311
 312     if (!$handled) {
 313       switch ($fieldDef['type']) {
 314         case CRM_Utils_Type::T_INT:
 315         case CRM_Utils_Type::T_FLOAT:
 316         case CRM_Utils_Type::T_MONEY:
 317           if (isset($fieldDef['precision'])) {
 318             // $object->$dbName = CRM_Utils_Number::createRandomDecimal($value['precision']);
 319             $this->$dbName = CRM_Utils_Number::createTruncatedDecimal($counter, $fieldDef['precision']);
 320           }
 321           else {
 322             $this->$dbName = $counter;
 323           }
 324           break;
 325
 326         case CRM_Utils_Type::T_BOOLEAN:
 327           if (isset($fieldDef['default'])) {
 328             $this->$dbName = $fieldDef['default'];
 329           }
 330           elseif ($fieldDef['name'] == 'is_deleted' || $fieldDef['name'] == 'is_test') {
 331             $this->$dbName = 0;
 332           }
 333           else {
 334             $this->$dbName = 1;
 335           }
 336           break;
 337
 338         case CRM_Utils_Type::T_DATE:
 339         case CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME:
 340           $this->$dbName = '19700101';
 341           if ($dbName == 'end_date') {
 342             // put this in the future
 343             $this->$dbName = '20200101';
 344           }
 345           break;
 346
 347         case CRM_Utils_Type::T_TIMESTAMP:
 348           $this->$dbName = '19700201000000';
 349           break;
 350
 351         case CRM_Utils_Type::T_TIME:
 352           throw new CRM_Core_Exception('T_TIME shouldn\'t be used.');
 353
 354         case CRM_Utils_Type::T_CCNUM:
 355           $this->$dbName = '4111 1111 1111 1111';
 356           break;
 357
 358         case CRM_Utils_Type::T_URL:
 359           $this->$dbName = 'http://www.civicrm.org';
 360           break;
 361
 362         case CRM_Utils_Type::T_STRING:
 363         case CRM_Utils_Type::T_BLOB:
 364         case CRM_Utils_Type::T_MEDIUMBLOB:
 365         case CRM_Utils_Type::T_TEXT:
 366         case CRM_Utils_Type::T_LONGTEXT:
 367         case CRM_Utils_Type::T_EMAIL:
 368         default:
 369           // WAS: if (isset($value['enumValues'])) {
 370           // TODO: see if this works with all pseudoconstants
 371           if (isset($fieldDef['pseudoconstant'], $fieldDef['pseudoconstant']['callback'])) {
 372             if (isset($fieldDef['default'])) {
 373               $this->$dbName = $fieldDef['default'];
 374             }
 375             else {
 376               $options = CRM_Core_PseudoConstant::get($daoName, $fieldName);
 377               if (is_array($options)) {
 378                 $this->$dbName = $options[0];
 379               }
 380               else {
 381                 $defaultValues = explode(',', $options);
 382                 $this->$dbName = $defaultValues[0];
 383               }
 384             }
 385           }
 386           else {
 387             $this->$dbName = $dbName . '_' . $counter;
 388             $maxlength = $fieldDef['maxlength'] ?? NULL;
 389             if ($maxlength > 0 && strlen($this->$dbName) > $maxlength) {
 390               $this->$dbName = substr($this->$dbName, 0, $fieldDef['maxlength']);
 391             }
 392           }
 393       }
 394     }
 395   }
 396
 397   /**
 398    * Reset the DAO object.
 399    *
 400    * DAO is kinda crappy in that there is an unwritten rule of one query per DAO.
 401    *
 402    * We attempt to get around this crappy restriction by resetting some of DAO's internal fields. Use this with caution
 403    */
 404   public function reset() {
 405
 406     foreach (array_keys($this->table()) as $field) {
 407       unset($this->$field);
 408     }
 409
 410     /**
 411      * reset the various DB_DAO structures manually
 412      */
 413     $this->_query = [];
 414     $this->whereAdd();
 415     $this->selectAdd();
 416     $this->joinAdd();
 417   }
 418
 419   /**
 420    * @param string $tableName
 421    *
 422    * @return string
 423    */
 424   public static function getLocaleTableName($tableName) {
 425     global $dbLocale;
 426     if ($dbLocale) {
 427       $tables = CRM_Core_I18n_Schema::schemaStructureTables();
 428       if (in_array($tableName, $tables)) {
 429         return $tableName . $dbLocale;
 430       }
 431     }
 432     return $tableName;
 433   }
 434
 435   /**
 436    * Execute a query by the current DAO, localizing it along the way (if needed).
 437    *
 438    * @param string $query
 439    *   The SQL query for execution.
 440    * @param bool $i18nRewrite
 441    *   Whether to rewrite the query.
 442    *
 443    * @return object
 444    *   the current DAO object after the query execution
 445    */
 446   public function query($query, $i18nRewrite = TRUE) {
 447     // rewrite queries that should use $dbLocale-based views for multi-language installs
 448     global $dbLocale, $_DB_DATAOBJECT;
 449
 450     if (empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
 451       // Will force connection to be populated per CRM-20541.
 452       new CRM_Core_DAO();
 453     }
 454
 455     $conn = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
 456     $orig_options = $conn->options;
 457     $this->_setDBOptions($this->_options);
 458
 459     if ($i18nRewrite and $dbLocale) {
 460       $query = CRM_Core_I18n_Schema::rewriteQuery($query);
 461     }
 462
 463     $ret = parent::query($query);
 464
 465     $this->_setDBOptions($orig_options);
 466     return $ret;
 467   }
 468
 469   /**
 470    * Static function to set the factory instance for this class.
 471    *
 472    * @param object $factory
 473    *   The factory application object.
 474    */
 475   public static function setFactory(&$factory) {
 476     self::$_factory = &$factory;
 477   }
 478
 479   /**
 480    * Factory method to instantiate a new object from a table name.
 481    *
 482    * @param string $table
 483    * @return \DataObject|\PEAR_Error
 484    */
 485   public function factory($table = '') {
 486     if (!isset(self::$_factory)) {
 487       return parent::factory($table);
 488     }
 489
 490     return self::$_factory->create($table);
 491   }
 492
 493   /**
 494    * Initialization for all DAO objects. Since we access DB_DO programatically
 495    * we need to set the links manually.
 496    */
 497   public function initialize() {
 498     $this->_connect();
 499     if (empty(Civi::$statics[__CLASS__]['init'])) {
 500       // CRM_Core_DAO::init() must be called before CRM_Core_DAO->initialize().
 501       // This occurs very early in bootstrap - error handlers may not be wired up.
 502       echo "Inconsistent system initialization sequence. Premature access of (" . get_class($this) . ")";
 503       CRM_Utils_System::civiExit();
 504     }
 505   }
 506
 507   /**
 508    * Defines the default key as 'id'.
 509    *
 510    * @return array
 511    */
 512   public function keys() {
 513     static $keys;
 514     if (!isset($keys)) {
 515       $keys = ['id'];
 516     }
 517     return $keys;
 518   }
 519
 520   /**
 521    * Tells DB_DataObject which keys use autoincrement.
 522    * 'id' is autoincrementing by default.
 523    *
 524    *
 525    * @return array
 526    */
 527   public function sequenceKey() {
 528     static $sequenceKeys;
 529     if (!isset($sequenceKeys)) {
 530       $sequenceKeys = ['id', TRUE];
 531     }
 532     return $sequenceKeys;
 533   }
 534
 535   /**
 536    * Returns list of FK relationships.
 537    *
 538    * @return CRM_Core_Reference_Basic[]
 539    */
 540   public static function getReferenceColumns() {
 541     if (!isset(Civi::$statics[static::class]['links'])) {
 542       Civi::$statics[static::class]['links'] = static::createReferenceColumns(static::class);
 543       CRM_Core_DAO_AllCoreTables::invoke(static::class, 'links_callback', Civi::$statics[static::class]['links']);
 544     }
 545     return Civi::$statics[static::class]['links'];
 546   }
 547
 548   /**
 549    * Returns all the column names of this table.
 550    *
 551    *
 552    * @return array
 553    */
 554   public static function &fields() {
 555     $result = NULL;
 556     return $result;
 557   }
 558
 559   /**
 560    * Returns all usable fields, indexed by name.
 561    *
 562    * This function differs from fields() in that it indexes by name rather than unique_name.
 563    *
 564    * It excludes fields not added yet by pending upgrades.
 565    * This avoids problems with trying to SELECT a field that exists in code but has not yet been added to the db.
 566    *
 567    * @param bool $checkPermissions
 568    *   Filter by field permissions.
 569    * @return array
 570    */
 571   public static function getSupportedFields($checkPermissions = FALSE) {
 572     $fields = array_column((array) static::fields(), NULL, 'name');
 573
 574     // Exclude fields yet not added by pending upgrades
 575     $dbVer = \CRM_Core_BAO_Domain::version();
 576     $daoExt = defined(static::class . '::EXT') ? constant(static::class . '::EXT') : NULL;
 577     if ($fields && $daoExt === 'civicrm' && version_compare($dbVer, \CRM_Utils_System::version()) < 0) {
 578       $fields = array_filter($fields, function($field) use ($dbVer) {
 579         $add = $field['add'] ?? '1.0.0';
 580         if (substr_count($add, '.') < 2) {
 581           $add .= '.alpha1';
 582         }
 583         return version_compare($dbVer, $add, '>=');
 584       });
 585     }
 586
 587     // Exclude fields the user does not have permission for
 588     if ($checkPermissions) {
 589       $fields = array_filter($fields, function($field) {
 590         return empty($field['permission']) || CRM_Core_Permission::check($field['permission']);
 591       });
 592     }
 593
 594     return $fields;
 595   }
 596
 597   /**
 598    * Get/set an associative array of table columns
 599    *
 600    * @return array
 601    *   (associative)
 602    */
 603   public function table() {
 604     $fields = $this->fields();
 605
 606     $table = [];
 607     if ($fields) {
 608       foreach ($fields as $name => $value) {
 609         $table[$value['name']] = $value['type'];
 610         if (!empty($value['required'])) {
 611           $table[$value['name']] += self::DB_DAO_NOTNULL;
 612         }
 613       }
 614     }
 615
 616     return $table;
 617   }
 618
 619   /**
 620    * Save DAO object.
 621    *
 622    * @param bool $hook
 623    *
 624    * @return CRM_Core_DAO
 625    */
 626   public function save($hook = TRUE) {
 627     $eventID = uniqid();
 628     if (!empty($this->id)) {
 629       if ($hook) {
 630         $preEvent = new \Civi\Core\DAO\Event\PreUpdate($this);
 631         $preEvent->eventID = $eventID;
 632         \Civi::dispatcher()->dispatch("civi.dao.preUpdate", $preEvent);
 633       }
 634
 635       $result = $this->update();
 636
 637       if ($hook) {
 638         $event = new \Civi\Core\DAO\Event\PostUpdate($this, $result);
 639         $event->eventID = $eventID;
 640         \Civi::dispatcher()->dispatch("civi.dao.postUpdate", $event);
 641       }
 642       $this->clearDbColumnValueCache();
 643     }
 644     else {
 645       if ($hook) {
 646         $preEvent = new \Civi\Core\DAO\Event\PreUpdate($this);
 647         $preEvent->eventID = $eventID;
 648         \Civi::dispatcher()->dispatch("civi.dao.preInsert", $preEvent);
 649       }
 650
 651       $result = $this->insert();
 652
 653       if ($hook) {
 654         $event = new \Civi\Core\DAO\Event\PostUpdate($this, $result);
 655         $event->eventID = $eventID;
 656         \Civi::dispatcher()->dispatch("civi.dao.postInsert", $event);
 657       }
 658     }
 659     $this->free();
 660
 661     if ($hook) {
 662       CRM_Utils_Hook::postSave($this);
 663     }
 664
 665     return $this;
 666   }
 667
 668   /**
 669    * Deletes items from table which match current objects variables.
 670    *
 671    * Returns the true on success
 672    *
 673    * for example
 674    *
 675    * Designed to be extended
 676    *
 677    * $object = new mytable();
 678    * $object->ID=123;
 679    * echo $object->delete(); // builds a conditon
 680    *
 681    * $object = new mytable();
 682    * $object->whereAdd('age > 12');
 683    * $object->limit(1);
 684    * $object->orderBy('age DESC');
 685    * $object->delete(true); // dont use object vars, use the conditions, limit and order.
 686    *
 687    * @param bool $useWhere (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
 688    *             we will build the condition only using the whereAdd's.  Default is to
 689    *             build the condition only using the object parameters.
 690    *
 691    *     * @return mixed Int (No. of rows affected) on success, false on failure, 0 on no data affected
 692    */
 693   public function delete($useWhere = FALSE) {
 694     $preEvent = new \Civi\Core\DAO\Event\PreDelete($this);
 695     \Civi::dispatcher()->dispatch("civi.dao.preDelete", $preEvent);
 696
 697     $result = parent::delete($useWhere);
 698
 699     $event = new \Civi\Core\DAO\Event\PostDelete($this, $result);
 700     \Civi::dispatcher()->dispatch("civi.dao.postDelete", $event);
 701     $this->free();
 702
 703     $this->clearDbColumnValueCache();
 704
 705     return $result;
 706   }
 707
 708   /**
 709    * @param bool $created
 710    */
 711   public function log($created = FALSE) {
 712     static $cid = NULL;
 713
 714     if (!$this->getLog()) {
 715       return;
 716     }
 717
 718     if (!$cid) {
 719       $session = CRM_Core_Session::singleton();
 720       $cid = $session->get('userID');
 721     }
 722
 723     // return is we dont have handle to FK
 724     if (!$cid) {
 725       return;
 726     }
 727
 728     $dao = new CRM_Core_DAO_Log();
 729     $dao->entity_table = $this->getTableName();
 730     $dao->entity_id = $this->id;
 731     $dao->modified_id = $cid;
 732     $dao->modified_date = date("YmdHis");
 733     $dao->insert();
 734   }
 735
 736   /**
 737    * Given an associative array of name/value pairs, extract all the values
 738    * that belong to this object and initialize the object with said values
 739    *
 740    * @param array $params
 741    *   Array of name/value pairs to save.
 742    *
 743    * @return bool
 744    *   Did we copy all null values into the object
 745    */
 746   public function copyValues($params) {
 747     $allNull = TRUE;
 748     foreach ($this->fields() as $uniqueName => $field) {
 749       $dbName = $field['name'];
 750       if (array_key_exists($dbName, $params)) {
 751         $value = $params[$dbName];
 752         $exists = TRUE;
 753       }
 754       elseif (array_key_exists($uniqueName, $params)) {
 755         $value = $params[$uniqueName];
 756         $exists = TRUE;
 757       }
 758       else {
 759         $exists = FALSE;
 760       }
 761
 762       // if there is no value then make the variable NULL
 763       if ($exists) {
 764         if ($value === '') {
 765           $this->$dbName = 'null';
 766         }
 767         elseif (is_array($value) && !empty($field['serialize'])) {
 768           $this->$dbName = CRM_Core_DAO::serializeField($value, $field['serialize']);
 769           $allNull = FALSE;
 770         }
 771         else {
 772           $maxLength = $field['maxlength'] ?? NULL;
 773           if (!is_array($value) && $maxLength && mb_strlen($value) > $maxLength && empty($field['pseudoconstant'])) {
 774             // No ts() since this is a sysadmin-y string not seen by general users.
 775             Civi::log()->warning('A string for field {dbName} has been truncated. The original string was {value}.', ['dbName' => $dbName, 'value' => $value]);
 776             // The string is too long - what to do what to do? Well losing data is generally bad so let's truncate
 777             $value = CRM_Utils_String::ellipsify($value, $maxLength);
 778           }
 779           $this->$dbName = $value;
 780           $allNull = FALSE;
 781         }
 782       }
 783     }
 784     return $allNull;
 785   }
 786
 787   /**
 788    * Store all the values from this object in an associative array
 789    * this is a destructive store, calling function is responsible
 790    * for keeping sanity of id's.
 791    *
 792    * @param object $object
 793    *   The object that we are extracting data from.
 794    * @param array $values
 795    *   (reference ) associative array of name/value pairs.
 796    */
 797   public static function storeValues(&$object, &$values) {
 798     $fields = $object->fields();
 799     foreach ($fields as $name => $value) {
 800       $dbName = $value['name'];
 801       if (isset($object->$dbName) && $object->$dbName !== 'null') {
 802         $values[$dbName] = $object->$dbName;
 803         if ($name != $dbName) {
 804           $values[$name] = $object->$dbName;
 805         }
 806       }
 807     }
 808   }
 809
 810   /**
 811    * Create an attribute for this specific field. We only do this for strings and text
 812    *
 813    * @param array $field
 814    *   The field under task.
 815    *
 816    * @return array|null
 817    *   the attributes for the object
 818    */
 819   public static function makeAttribute($field) {
 820     if ($field) {
 821       if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
 822         $maxLength = $field['maxlength'] ?? NULL;
 823         $size = $field['size'] ?? NULL;
 824         if ($maxLength || $size) {
 825           $attributes = [];
 826           if ($maxLength) {
 827             $attributes['maxlength'] = $maxLength;
 828           }
 829           if ($size) {
 830             $attributes['size'] = $size;
 831           }
 832           return $attributes;
 833         }
 834       }
 835       elseif (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_TEXT) {
 836         $rows = $field['rows'] ?? NULL;
 837         if (!isset($rows)) {
 838           $rows = 2;
 839         }
 840         $cols = $field['cols'] ?? NULL;
 841         if (!isset($cols)) {
 842           $cols = 80;
 843         }
 844
 845         $attributes = [];
 846         $attributes['rows'] = $rows;
 847         $attributes['cols'] = $cols;
 848         return $attributes;
 849       }
 850       elseif (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_INT || CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_FLOAT || CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_MONEY) {
 851         $attributes['size'] = 6;
 852         $attributes['maxlength'] = 14;
 853         return $attributes;
 854       }
 855     }
 856     return NULL;
 857   }
 858
 859   /**
 860    * Get the size and maxLength attributes for this text field.
 861    * (or for all text fields) in the DAO object.
 862    *
 863    * @param string $class
 864    *   Name of DAO class.
 865    * @param string $fieldName
 866    *   Field that i'm interested in or null if.
 867    *                          you want the attributes for all DAO text fields
 868    *
 869    * @return array
 870    *   assoc array of name => attribute pairs
 871    */
 872   public static function getAttribute($class, $fieldName = NULL) {
 873     $object = new $class();
 874     $fields = $object->fields();
 875     if ($fieldName != NULL) {
 876       $field = $fields[$fieldName] ?? NULL;
 877       return self::makeAttribute($field);
 878     }
 879     else {
 880       $attributes = [];
 881       foreach ($fields as $name => $field) {
 882         $attribute = self::makeAttribute($field);
 883         if ($attribute) {
 884           $attributes[$name] = $attribute;
 885         }
 886       }
 887
 888       if (!empty($attributes)) {
 889         return $attributes;
 890       }
 891     }
 892     return NULL;
 893   }
 894
 895   /**
 896    * Create or update a record from supplied params.
 897    *
 898    * If 'id' is supplied, an existing record will be updated
 899    * Otherwise a new record will be created.
 900    *
 901    * @param array $record
 902    *
 903    * @return static
 904    * @throws \CRM_Core_Exception
 905    */
 906   public static function writeRecord(array $record): CRM_Core_DAO {
 907     $hook = empty($record['id']) ? 'create' : 'edit';
 908     $className = CRM_Core_DAO_AllCoreTables::getCanonicalClassName(static::class);
 909     if ($className === 'CRM_Core_DAO') {
 910       throw new CRM_Core_Exception('Function writeRecord must be called on a subclass of CRM_Core_DAO');
 911     }
 912     $entityName = CRM_Core_DAO_AllCoreTables::getBriefName($className);
 913
 914     \CRM_Utils_Hook::pre($hook, $entityName, $record['id'] ?? NULL, $record);
 915     $instance = new $className();
 916     $instance->copyValues($record);
 917     $instance->save();
 918     \CRM_Utils_Hook::post($hook, $entityName, $instance->id, $instance);
 919
 920     return $instance;
 921   }
 922
 923   /**
 924    * Bulk save multiple records
 925    *
 926    * @param array[] $records
 927    * @return static[]
 928    * @throws CRM_Core_Exception
 929    */
 930   public static function writeRecords(array $records) {
 931     $results = [];
 932     foreach ($records as $record) {
 933       $results[] = static::writeRecord($record);
 934     }
 935     return $results;
 936   }
 937
 938   /**
 939    * Delete a record from supplied params.
 940    *
 941    * @param array $record
 942    *   'id' is required.
 943    * @return static
 944    * @throws CRM_Core_Exception
 945    */
 946   public static function deleteRecord(array $record) {
 947     $className = CRM_Core_DAO_AllCoreTables::getCanonicalClassName(static::class);
 948     if ($className === 'CRM_Core_DAO') {
 949       throw new CRM_Core_Exception('Function deleteRecord must be called on a subclass of CRM_Core_DAO');
 950     }
 951     $entityName = CRM_Core_DAO_AllCoreTables::getBriefName($className);
 952     if (empty($record['id'])) {
 953       throw new CRM_Core_Exception("Cannot delete {$entityName} with no id.");
 954     }
 955
 956     CRM_Utils_Hook::pre('delete', $entityName, $record['id'], $record);
 957     $instance = new $className();
 958     $instance->id = $record['id'];
 959     if (!$instance->delete()) {
 960       throw new CRM_Core_Exception("Could not delete {$entityName} id {$record['id']}");
 961     }
 962     CRM_Utils_Hook::post('delete', $entityName, $record['id'], $instance);
 963
 964     return $instance;
 965   }
 966
 967   /**
 968    * Bulk delete multiple records.
 969    *
 970    * @param array[] $records
 971    * @return static[]
 972    * @throws CRM_Core_Exception
 973    */
 974   public static function deleteRecords(array $records) {
 975     $results = [];
 976     foreach ($records as $record) {
 977       $results[] = static::deleteRecord($record);
 978     }
 979     return $results;
 980   }
 981
 982   /**
 983    * Check if there is a record with the same name in the db.
 984    *
 985    * @param string $value
 986    *   The value of the field we are checking.
 987    * @param string $daoName
 988    *   The dao object name.
 989    * @param string $daoID
 990    *   The id of the object being updated. u can change your name.
 991    *                          as long as there is no conflict
 992    * @param string $fieldName
 993    *   The name of the field in the DAO.
 994    *
 995    * @param string $domainID
 996    *   The id of the domain.  Object exists only for the given domain.
 997    *
 998    * @return bool
 999    *   true if object exists
1000    */
1001   public static function objectExists($value, $daoName, $daoID, $fieldName = 'name', $domainID = NULL) {
1002     $object = new $daoName();
1003     $object->$fieldName = $value;
1004     if ($domainID) {
1005       $object->domain_id = $domainID;
1006     }
1007
1008     if ($object->find(TRUE)) {
1009       return $daoID && $object->id == $daoID;
1010     }
1011     else {
1012       return TRUE;
1013     }
1014   }
1015
1016   /**
1017    * Check if there is a given column in a specific table.
1018    *
1019    * @deprecated
1020    * @see CRM_Core_BAO_SchemaHandler::checkIfFieldExists
1021    *
1022    * @param string $tableName
1023    * @param string $columnName
1024    * @param bool $i18nRewrite
1025    *   Whether to rewrite the query on multilingual setups.
1026    *
1027    * @return bool
1028    *   true if exists, else false
1029    */
1030   public static function checkFieldExists($tableName, $columnName, $i18nRewrite = TRUE) {
1031     CRM_Core_Error::deprecatedFunctionWarning('CRM_Core_BAO_SchemaHandler::checkIfFieldExists');
1032     return CRM_Core_BAO_SchemaHandler::checkIfFieldExists($tableName, $columnName, $i18nRewrite);
1033   }
1034
1035   /**
1036    * Scans all the tables using a slow query and table name.
1037    *
1038    * @return array
1039    */
1040   public static function getTableNames() {
1041     $dao = CRM_Core_DAO::executeQuery(
1042       "SELECT TABLE_NAME
1043        FROM information_schema.TABLES
1044        WHERE TABLE_SCHEMA = '" . CRM_Core_DAO::getDatabaseName() . "'
1045          AND TABLE_NAME LIKE 'civicrm_%'
1046          AND TABLE_NAME NOT LIKE 'civicrm_import_job_%'
1047          AND TABLE_NAME NOT LIKE '%_temp%'
1048       ");
1049
1050     while ($dao->fetch()) {
1051       $values[] = $dao->TABLE_NAME;
1052     }
1053     return $values;
1054   }
1055
1056   /**
1057    * @param int $maxTablesToCheck
1058    *
1059    * @return bool
1060    */
1061   public static function isDBMyISAM($maxTablesToCheck = 10) {
1062     return CRM_Core_DAO::singleValueQuery(
1063       "SELECT count(*)
1064        FROM information_schema.TABLES
1065        WHERE ENGINE = 'MyISAM'
1066          AND TABLE_SCHEMA = '" . CRM_Core_DAO::getDatabaseName() . "'
1067          AND TABLE_NAME LIKE 'civicrm_%'
1068          AND TABLE_NAME NOT LIKE 'civicrm_import_job_%'
1069          AND TABLE_NAME NOT LIKE '%_temp%'
1070          AND TABLE_NAME NOT LIKE 'civicrm_tmp_%'
1071       ");
1072   }
1073
1074   /**
1075    * Get the name of the CiviCRM database.
1076    *
1077    * @return string
1078    */
1079   public static function getDatabaseName() {
1080     $daoObj = new CRM_Core_DAO();
1081     return $daoObj->database();
1082   }
1083
1084   /**
1085    * Checks if a constraint exists for a specified table.
1086    *
1087    * @param string $tableName
1088    * @param string $constraint
1089    *
1090    * @return bool
1091    *   true if constraint exists, false otherwise
1092    *
1093    * @throws \CRM_Core_Exception
1094    */
1095   public static function checkConstraintExists($tableName, $constraint) {
1096     static $show = [];
1097
1098     if (!array_key_exists($tableName, $show)) {
1099       $query = "SHOW CREATE TABLE $tableName";
1100       $dao = CRM_Core_DAO::executeQuery($query, [], TRUE, NULL, FALSE, FALSE);
1101
1102       if (!$dao->fetch()) {
1103         throw new CRM_Core_Exception('query failed');
1104       }
1105
1106       $show[$tableName] = $dao->Create_Table;
1107     }
1108
1109     return (bool) preg_match("/\b$constraint\b/i", $show[$tableName]);
1110   }
1111
1112   /**
1113    * Checks if CONSTRAINT keyword exists for a specified table.
1114    *
1115    * @param array $tables
1116    *
1117    * @throws CRM_Core_Exception
1118    *
1119    * @return bool
1120    *   true if CONSTRAINT keyword exists, false otherwise
1121    */
1122   public static function schemaRequiresRebuilding($tables = ["civicrm_contact"]) {
1123     $show = [];
1124     foreach ($tables as $tableName) {
1125       if (!array_key_exists($tableName, $show)) {
1126         $query = "SHOW CREATE TABLE $tableName";
1127         $dao = CRM_Core_DAO::executeQuery($query, [], TRUE, NULL, FALSE, FALSE);
1128
1129         if (!$dao->fetch()) {
1130           throw new CRM_Core_Exception('Show create table failed.');
1131         }
1132
1133         $show[$tableName] = $dao->Create_Table;
1134       }
1135
1136       $result = (bool) preg_match("/\bCONSTRAINT\b\s/i", $show[$tableName]);
1137       if ($result == TRUE) {
1138         continue;
1139       }
1140       else {
1141         return FALSE;
1142       }
1143     }
1144     return TRUE;
1145   }
1146
1147   /**
1148    * Checks if the FK constraint name is in the format 'FK_tableName_columnName'
1149    * for a specified column of a table.
1150    *
1151    * @param string $tableName
1152    * @param string $columnName
1153    *
1154    * @return bool
1155    *   true if in format, false otherwise
1156    *
1157    * @throws \CRM_Core_Exception
1158    */
1159   public static function checkFKConstraintInFormat($tableName, $columnName) {
1160     static $show = [];
1161
1162     if (!array_key_exists($tableName, $show)) {
1163       $query = "SHOW CREATE TABLE $tableName";
1164       $dao = CRM_Core_DAO::executeQuery($query);
1165
1166       if (!$dao->fetch()) {
1167         throw new CRM_Core_Exception('query failed');
1168       }
1169
1170       $show[$tableName] = $dao->Create_Table;
1171     }
1172     $constraint = "`FK_{$tableName}_{$columnName}`";
1173     $pattern = "/\bCONSTRAINT\b\s+%s\s+\bFOREIGN\s+KEY\b\s/i";
1174     return (bool) preg_match(sprintf($pattern, $constraint), $show[$tableName]);
1175   }
1176
1177   /**
1178    * Check whether a specific column in a specific table has always the same value.
1179    *
1180    * @param string $tableName
1181    * @param string $columnName
1182    * @param string $columnValue
1183    *
1184    * @return bool
1185    *   true if the value is always $columnValue, false otherwise
1186    */
1187   public static function checkFieldHasAlwaysValue($tableName, $columnName, $columnValue) {
1188     $query = "SELECT * FROM $tableName WHERE $columnName != '$columnValue'";
1189     $dao = CRM_Core_DAO::executeQuery($query);
1190     $result = $dao->fetch() ? FALSE : TRUE;
1191     return $result;
1192   }
1193
1194   /**
1195    * Check whether a specific column in a specific table is always NULL.
1196    *
1197    * @param string $tableName
1198    * @param string $columnName
1199    *
1200    * @return bool
1201    *   true if if the value is always NULL, false otherwise
1202    */
1203   public static function checkFieldIsAlwaysNull($tableName, $columnName) {
1204     $query = "SELECT * FROM $tableName WHERE $columnName IS NOT NULL";
1205     $dao = CRM_Core_DAO::executeQuery($query);
1206     $result = $dao->fetch() ? FALSE : TRUE;
1207     return $result;
1208   }
1209
1210   /**
1211    * Checks if this DAO's table ought to exist.
1212    *
1213    * If there are pending DB updates, this function compares the CiviCRM version of the table to the current schema version.
1214    *
1215    * @return bool
1216    * @throws CRM_Core_Exception
1217    */
1218   public static function tableHasBeenAdded() {
1219     if (CRM_Utils_System::version() === CRM_Core_BAO_Domain::version()) {
1220       return TRUE;
1221     }
1222     $daoExt = defined(static::class . '::EXT') ? constant(static::class . '::EXT') : NULL;
1223     $daoVersion = defined(static::class . '::TABLE_ADDED') ? constant(static::class . '::TABLE_ADDED') : '1.0';
1224     return !($daoExt === 'civicrm' && version_compare(CRM_Core_BAO_Domain::version(), $daoVersion, '<'));
1225   }
1226
1227   /**
1228    * Check if there is a given table in the database.
1229    *
1230    * @param string $tableName
1231    *
1232    * @return bool
1233    *   true if exists, else false
1234    */
1235   public static function checkTableExists($tableName) {
1236     $query = "
1237 SHOW TABLES
1238 LIKE %1
1239 ";
1240     $params = [1 => [$tableName, 'String']];
1241
1242     $dao = CRM_Core_DAO::executeQuery($query, $params);
1243     return (bool) $dao->fetch();
1244   }
1245
1246   /**
1247    * Check if a given table has data.
1248    *
1249    * @param string $tableName
1250    * @return bool
1251    *   TRUE if $tableName has at least one record.
1252    */
1253   public static function checkTableHasData($tableName) {
1254     $c = CRM_Core_DAO::singleValueQuery(sprintf('SELECT count(*) c FROM `%s`', $tableName));
1255     return $c > 0;
1256   }
1257
1258   /**
1259    * @param $version
1260    * @deprecated
1261    * @return bool
1262    */
1263   public function checkVersion($version) {
1264     CRM_Core_Error::deprecatedFunctionWarning('CRM_Core_BAO_Domain::version');
1265     $dbVersion = CRM_Core_BAO_Domain::version();
1266     return trim($version) == trim($dbVersion);
1267   }
1268
1269   /**
1270    * Find a DAO object for the given ID and return it.
1271    *
1272    * @param int $id
1273    *   Id of the DAO object being searched for.
1274    *
1275    * @return CRM_Core_DAO
1276    *   Object of the type of the class that called this function.
1277    *
1278    * @throws Exception
1279    */
1280   public static function findById($id) {
1281     $object = new static();
1282     $object->id = $id;
1283     if (!$object->find(TRUE)) {
1284       throw new Exception("Unable to find a " . get_called_class() . " with id {$id}.");
1285     }
1286     return $object;
1287   }
1288
1289   /**
1290    * Returns all results as array-encoded records.
1291    *
1292    * @return array
1293    */
1294   public function fetchAll($k = FALSE, $v = FALSE, $method = FALSE) {
1295     $result = [];
1296     while ($this->fetch()) {
1297       $result[] = $this->toArray();
1298     }
1299     return $result;
1300   }
1301
1302   /**
1303    * Return the results as PHP generator.
1304    *
1305    * @param string $type
1306    *   Whether the generator yields 'dao' objects or 'array's.
1307    */
1308   public function fetchGenerator($type = 'dao') {
1309     while ($this->fetch()) {
1310       switch ($type) {
1311         case 'dao':
1312           yield $this;
1313           break;
1314
1315         case 'array':
1316           yield $this->toArray();
1317           break;
1318
1319         default:
1320           throw new \RuntimeException("Invalid record type ($type)");
1321       }
1322     }
1323   }
1324
1325   /**
1326    * Returns a singular value.
1327    *
1328    * @return mixed|NULL
1329    */
1330   public function fetchValue() {
1331     $result = $this->getDatabaseResult();
1332     $row = $result->fetchRow();
1333     $ret = NULL;
1334     if ($row) {
1335       $ret = $row[0];
1336     }
1337     $this->free();
1338     return $ret;
1339   }
1340
1341   /**
1342    * Get all the result records as mapping between columns.
1343    *
1344    * @param string $keyColumn
1345    *   Ex: "name"
1346    * @param string $valueColumn
1347    *   Ex: "label"
1348    * @return array
1349    *   Ex: ["foo" => "The Foo Bar", "baz" => "The Baz Qux"]
1350    */
1351   public function fetchMap($keyColumn, $valueColumn) {
1352     $result = [];
1353     while ($this->fetch()) {
1354       $result[$this->{$keyColumn}] = $this->{$valueColumn};
1355     }
1356     return $result;
1357   }
1358
1359   /**
1360    * Given a DAO name, a column name and a column value, find the record and GET the value of another column in that record
1361    *
1362    * @param string $daoName
1363    *   Name of the DAO (Example: CRM_Contact_DAO_Contact to retrieve value from a contact).
1364    * @param int $searchValue
1365    *   Value of the column you want to search by.
1366    * @param string $returnColumn
1367    *   Name of the column you want to GET the value of.
1368    * @param string $searchColumn
1369    *   Name of the column you want to search by.
1370    * @param bool $force
1371    *   Skip use of the cache.
1372    *
1373    * @return string|int|null
1374    *   Value of $returnColumn in the retrieved record
1375    *
1376    * @throws \CRM_Core_Exception
1377    */
1378   public static function getFieldValue($daoName, $searchValue, $returnColumn = 'name', $searchColumn = 'id', $force = FALSE) {
1379     if (
1380       empty($searchValue) ||
1381       trim(strtolower($searchValue)) == 'null'
1382     ) {
1383       // adding this here since developers forget to check for an id
1384       // or for the 'null' (which is a bad DAO kludge)
1385       // and hence we get the first value in the db
1386       throw new CRM_Core_Exception('getFieldValue failed');
1387     }
1388
1389     self::$_dbColumnValueCache = self::$_dbColumnValueCache ?? [];
1390
1391     while (strpos($daoName, '_BAO_') !== FALSE) {
1392       $daoName = get_parent_class($daoName);
1393     }
1394
1395     if ($force ||
1396       empty(self::$_dbColumnValueCache[$daoName][$searchColumn][$searchValue]) ||
1397       !array_key_exists($returnColumn, self::$_dbColumnValueCache[$daoName][$searchColumn][$searchValue])
1398     ) {
1399       $object = new $daoName();
1400       $object->$searchColumn = $searchValue;
1401       $object->selectAdd();
1402       $object->selectAdd($returnColumn);
1403
1404       $result = NULL;
1405       if ($object->find(TRUE)) {
1406         $result = $object->$returnColumn;
1407       }
1408
1409       self::$_dbColumnValueCache[$daoName][$searchColumn][$searchValue][$returnColumn] = $result;
1410     }
1411     return self::$_dbColumnValueCache[$daoName][$searchColumn][$searchValue][$returnColumn];
1412   }
1413
1414   /**
1415    * Given a DAO name, a column name and a column value, find the record and SET the value of another column in that record
1416    *
1417    * @param string $daoName
1418    *   Name of the DAO (Example: CRM_Contact_DAO_Contact to retrieve value from a contact).
1419    * @param int $searchValue
1420    *   Value of the column you want to search by.
1421    * @param string $setColumn
1422    *   Name of the column you want to SET the value of.
1423    * @param string $setValue
1424    *   SET the setColumn to this value.
1425    * @param string $searchColumn
1426    *   Name of the column you want to search by.
1427    *
1428    * @return bool
1429    *   true if we found and updated the object, else false
1430    */
1431   public static function setFieldValue($daoName, $searchValue, $setColumn, $setValue, $searchColumn = 'id') {
1432     $object = new $daoName();
1433     $object->selectAdd();
1434     $object->selectAdd("$searchColumn, $setColumn");
1435     $object->$searchColumn = $searchValue;
1436     $result = FALSE;
1437     if ($object->find(TRUE)) {
1438       $object->$setColumn = $setValue;
1439       if ($object->save()) {
1440         $result = TRUE;
1441       }
1442     }
1443     $object->free();
1444     return $result;
1445   }
1446
1447   /**
1448    * Get sort string.
1449    *
1450    * @param array|object $sort either array or CRM_Utils_Sort
1451    * @param string $default
1452    *   Default sort value.
1453    *
1454    * @return string
1455    */
1456   public static function getSortString($sort, $default = NULL) {
1457     // check if sort is of type CRM_Utils_Sort
1458     if (is_a($sort, 'CRM_Utils_Sort')) {
1459       return $sort->orderBy();
1460     }
1461
1462     $sortString = '';
1463
1464     // is it an array specified as $field => $sortDirection ?
1465     if ($sort) {
1466       foreach ($sort as $k => $v) {
1467         $sortString .= "$k $v,";
1468       }
1469       return rtrim($sortString, ',');
1470     }
1471     return $default;
1472   }
1473
1474   /**
1475    * Fetch object based on array of properties.
1476    *
1477    * @param string $daoName
1478    *   Name of the dao object.
1479    * @param array $params
1480    *   (reference ) an assoc array of name/value pairs.
1481    * @param array $defaults
1482    *   (reference ) an assoc array to hold the flattened values.
1483    * @param array $returnProperities
1484    *   An assoc array of fields that need to be returned, eg array( 'first_name', 'last_name').
1485    *
1486    * @return object
1487    *   an object of type referenced by daoName
1488    */
1489   public static function commonRetrieve($daoName, &$params, &$defaults, $returnProperities = NULL) {
1490     $object = new $daoName();
1491     $object->copyValues($params);
1492
1493     // return only specific fields if returnproperties are sent
1494     if (!empty($returnProperities)) {
1495       $object->selectAdd();
1496       $object->selectAdd(implode(',', $returnProperities));
1497     }
1498
1499     if ($object->find(TRUE)) {
1500       self::storeValues($object, $defaults);
1501       return $object;
1502     }
1503     return NULL;
1504   }
1505
1506   /**
1507    * Delete the object records that are associated with this contact.
1508    *
1509    * @param string $daoName
1510    *   Name of the dao object.
1511    * @param int $contactId
1512    *   Id of the contact to delete.
1513    */
1514   public static function deleteEntityContact($daoName, $contactId) {
1515     $object = new $daoName();
1516
1517     $object->entity_table = 'civicrm_contact';
1518     $object->entity_id = $contactId;
1519     $object->delete();
1520   }
1521
1522   /**
1523    * Execute an unbuffered query.
1524    *
1525    * This is a wrapper around new functionality exposed with CRM-17748.
1526    *
1527    * @param string $query query to be executed
1528    *
1529    * @param array $params
1530    * @param bool $abort
1531    * @param null $daoName
1532    * @param bool $freeDAO
1533    * @param bool $i18nRewrite
1534    * @param bool $trapException
1535    *
1536    * @return CRM_Core_DAO
1537    *   Object that points to an unbuffered result set
1538    */
1539   public static function executeUnbufferedQuery(
1540     $query,
1541     $params = [],
1542     $abort = TRUE,
1543     $daoName = NULL,
1544     $freeDAO = FALSE,
1545     $i18nRewrite = TRUE,
1546     $trapException = FALSE
1547   ) {
1548
1549     return self::executeQuery(
1550       $query,
1551       $params,
1552       $abort,
1553       $daoName,
1554       $freeDAO,
1555       $i18nRewrite,
1556       $trapException,
1557       ['result_buffering' => 0]
1558     );
1559   }
1560
1561   /**
1562    * Execute a query.
1563    *
1564    * @param string $query
1565    *   Query to be executed.
1566    *
1567    * @param array $params
1568    * @param bool $abort
1569    * @param null $daoName
1570    * @param bool $freeDAO
1571    * @param bool $i18nRewrite
1572    * @param bool $trapException
1573    * @param array $options
1574    *
1575    * @return CRM_Core_DAO|object
1576    *   object that holds the results of the query
1577    *   NB - if this is defined as just returning a DAO phpstorm keeps pointing
1578    *   out all the properties that are not part of the DAO
1579    */
1580   public static function &executeQuery(
1581     $query,
1582     $params = [],
1583     $abort = TRUE,
1584     $daoName = NULL,
1585     $freeDAO = FALSE,
1586     $i18nRewrite = TRUE,
1587     $trapException = FALSE,
1588     $options = []
1589   ) {
1590     $queryStr = self::composeQuery($query, $params, $abort);
1591
1592     if (!$daoName) {
1593       $dao = new CRM_Core_DAO();
1594     }
1595     else {
1596       $dao = new $daoName();
1597     }
1598
1599     if ($trapException) {
1600       CRM_Core_Error::deprecatedFunctionWarning('calling functions should handle exceptions');
1601       $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
1602     }
1603
1604     if ($dao->isValidOption($options)) {
1605       $dao->setOptions($options);
1606     }
1607
1608     $result = $dao->query($queryStr, $i18nRewrite);
1609
1610     // since it is unbuffered, ($dao->N==0) is true.  This blocks the standard fetch() mechanism.
1611     if (CRM_Utils_Array::value('result_buffering', $options) === 0) {
1612       $dao->N = TRUE;
1613     }
1614
1615     if (is_a($result, 'DB_Error')) {
1616       CRM_Core_Error::deprecatedFunctionWarning('calling functions should handle exceptions');
1617       return $result;
1618     }
1619
1620     return $dao;
1621   }
1622
1623   /**
1624    * Wrapper to validate internal DAO options before passing to DB_mysql/DB_Common level
1625    *
1626    * @param array $options
1627    *
1628    * @return bool
1629    *   Provided options are valid
1630    */
1631   public function isValidOption($options) {
1632     $isValid = FALSE;
1633     $validOptions = [
1634       'result_buffering',
1635       'persistent',
1636       'ssl',
1637       'portability',
1638     ];
1639
1640     if (empty($options)) {
1641       return $isValid;
1642     }
1643
1644     foreach (array_keys($options) as $option) {
1645       if (!in_array($option, $validOptions)) {
1646         return FALSE;
1647       }
1648       $isValid = TRUE;
1649     }
1650
1651     return $isValid;
1652   }
1653
1654   /**
1655    * Execute a query and get the single result.
1656    *
1657    * @param string $query
1658    *   Query to be executed.
1659    * @param array $params
1660    * @param bool $abort
1661    * @param bool $i18nRewrite
1662    * @return string|null
1663    *   the result of the query if any
1664    *
1665    */
1666   public static function &singleValueQuery(
1667     $query,
1668     $params = [],
1669     $abort = TRUE,
1670     $i18nRewrite = TRUE
1671   ) {
1672     $queryStr = self::composeQuery($query, $params, $abort);
1673
1674     static $_dao = NULL;
1675
1676     if (!$_dao) {
1677       $_dao = new CRM_Core_DAO();
1678     }
1679
1680     $_dao->query($queryStr, $i18nRewrite);
1681
1682     $result = $_dao->getDatabaseResult();
1683     $ret = NULL;
1684     if ($result) {
1685       $row = $result->fetchRow();
1686       if ($row) {
1687         $ret = $row[0];
1688       }
1689     }
1690     $_dao->free();
1691     return $ret;
1692   }
1693
1694   /**
1695    * Compose the query by merging the parameters into it.
1696    *
1697    * @param string $query
1698    * @param array $params
1699    * @param bool $abort
1700    *
1701    * @return string
1702    * @throws CRM_Core_Exception
1703    */
1704   public static function composeQuery($query, $params = [], $abort = TRUE) {
1705     $tr = [];
1706     foreach ($params as $key => $item) {
1707       if (is_numeric($key)) {
1708         if (CRM_Utils_Type::validate($item[0], $item[1]) !== NULL) {
1709           $item[0] = self::escapeString($item[0]);
1710           if ($item[1] == 'String' ||
1711             $item[1] == 'Memo' ||
1712             $item[1] == 'Link'
1713           ) {
1714             // Support class constants stipulating wildcard characters and/or
1715             // non-quoting of strings. Also support legacy code which may be
1716             // passing in TRUE or 1 for $item[2], which used to indicate the
1717             // use of wildcard characters.
1718             if (!empty($item[2])) {
1719               if ($item[2] & CRM_Core_DAO::QUERY_FORMAT_WILDCARD || $item[2] === TRUE) {
1720                 $item[0] = "'%{$item[0]}%'";
1721               }
1722               elseif (!($item[2] & CRM_Core_DAO::QUERY_FORMAT_NO_QUOTES)) {
1723                 $item[0] = "'{$item[0]}'";
1724               }
1725             }
1726             else {
1727               $item[0] = "'{$item[0]}'";
1728             }
1729           }
1730
1731           if (($item[1] == 'Date' || $item[1] == 'Timestamp') &&
1732             strlen($item[0]) == 0
1733           ) {
1734             $item[0] = 'null';
1735           }
1736
1737           $tr['%' . $key] = $item[0];
1738         }
1739         elseif ($abort) {
1740           throw new CRM_Core_Exception("{$item[0]} is not of type {$item[1]}");
1741         }
1742       }
1743     }
1744
1745     return strtr($query, $tr);
1746   }
1747
1748   /**
1749    * @param null $ids
1750    */
1751   public static function freeResult($ids = NULL) {
1752     global $_DB_DATAOBJECT;
1753
1754     if (!$ids) {
1755       if (!$_DB_DATAOBJECT ||
1756         !isset($_DB_DATAOBJECT['RESULTS'])
1757       ) {
1758         return;
1759       }
1760       $ids = array_keys($_DB_DATAOBJECT['RESULTS']);
1761     }
1762
1763     foreach ($ids as $id) {
1764       if (isset($_DB_DATAOBJECT['RESULTS'][$id])) {
1765         $_DB_DATAOBJECT['RESULTS'][$id]->free();
1766         unset($_DB_DATAOBJECT['RESULTS'][$id]);
1767       }
1768
1769       if (isset($_DB_DATAOBJECT['RESULTFIELDS'][$id])) {
1770         unset($_DB_DATAOBJECT['RESULTFIELDS'][$id]);
1771       }
1772     }
1773   }
1774
1775   /**
1776    * Make a shallow copy of an object and all the fields in the object.
1777    *
1778    * @param string $daoName
1779    *   Name of the dao.
1780    * @param array $criteria
1781    *   Array of all the fields & values.
1782    *   on which basis to copy
1783    * @param array $newData
1784    *   Array of all the fields & values.
1785    *   to be copied besides the other fields
1786    * @param string $fieldsFix
1787    *   Array of fields that you want to prefix/suffix/replace.
1788    * @param string $blockCopyOfDependencies
1789    *   Fields that you want to block from.
1790    *   getting copied
1791    * @param bool $blockCopyofCustomValues
1792    *   Case when you don't want to copy the custom values set in a
1793    *   template as it will override/ignore the submitted custom values
1794    *
1795    * @return CRM_Core_DAO|bool
1796    *   the newly created copy of the object. False if none created.
1797    */
1798   public static function copyGeneric($daoName, $criteria, $newData = NULL, $fieldsFix = NULL, $blockCopyOfDependencies = NULL, $blockCopyofCustomValues = FALSE) {
1799     $object = new $daoName();
1800     $newObject = FALSE;
1801     if (!$newData) {
1802       $object->id = $criteria['id'];
1803     }
1804     else {
1805       foreach ($criteria as $key => $value) {
1806         $object->$key = $value;
1807       }
1808     }
1809
1810     $object->find();
1811     while ($object->fetch()) {
1812
1813       // all the objects except with $blockCopyOfDependencies set
1814       // be copied - addresses #CRM-1962
1815
1816       if ($blockCopyOfDependencies && $object->$blockCopyOfDependencies) {
1817         break;
1818       }
1819
1820       $newObject = new $daoName();
1821
1822       $fields = $object->fields();
1823       $fieldsToPrefix = [];
1824       $fieldsToSuffix = [];
1825       $fieldsToReplace = [];
1826       if (!empty($fieldsFix['prefix'])) {
1827         $fieldsToPrefix = $fieldsFix['prefix'];
1828       }
1829       if (!empty($fieldsFix['suffix'])) {
1830         $fieldsToSuffix = $fieldsFix['suffix'];
1831       }
1832       if (!empty($fieldsFix['replace'])) {
1833         $fieldsToReplace = $fieldsFix['replace'];
1834       }
1835
1836       $localizableFields = FALSE;
1837       foreach ($fields as $name => $value) {
1838         if ($name == 'id' || $value['name'] == 'id') {
1839           // copy everything but the id!
1840           continue;
1841         }
1842
1843         $dbName = $value['name'];
1844         $type = CRM_Utils_Type::typeToString($value['type']);
1845         $newObject->$dbName = $object->$dbName;
1846         if (isset($fieldsToPrefix[$dbName])) {
1847           $newObject->$dbName = $fieldsToPrefix[$dbName] . $newObject->$dbName;
1848         }
1849         if (isset($fieldsToSuffix[$dbName])) {
1850           $newObject->$dbName .= $fieldsToSuffix[$dbName];
1851         }
1852         if (isset($fieldsToReplace[$dbName])) {
1853           $newObject->$dbName = $fieldsToReplace[$dbName];
1854         }
1855
1856         if ($type == 'Timestamp' || $type == 'Date') {
1857           $newObject->$dbName = CRM_Utils_Date::isoToMysql($newObject->$dbName);
1858         }
1859
1860         if (!empty($value['localizable'])) {
1861           $localizableFields = TRUE;
1862         }
1863
1864         if ($newData) {
1865           $newObject->copyValues($newData);
1866         }
1867       }
1868       $newObject->save();
1869
1870       // ensure we copy all localized fields as well
1871       if (CRM_Core_I18n::isMultilingual() && $localizableFields) {
1872         global $dbLocale;
1873         $locales = CRM_Core_I18n::getMultilingual();
1874         $curLocale = CRM_Core_I18n::getLocale();
1875         // loop on other locales
1876         foreach ($locales as $locale) {
1877           if ($locale != $curLocale) {
1878             // setLocale doesn't seems to be reliable to set dbLocale and we only need to change the db locale
1879             $dbLocale = '_' . $locale;
1880             $newObject->copyLocalizable($object->id, $newObject->id, $fieldsToPrefix, $fieldsToSuffix, $fieldsToReplace);
1881           }
1882         }
1883         // restore dbLocale to starting value
1884         $dbLocale = '_' . $curLocale;
1885       }
1886
1887       if (!$blockCopyofCustomValues) {
1888         $newObject->copyCustomFields($object->id, $newObject->id);
1889       }
1890       CRM_Utils_Hook::post('create', CRM_Core_DAO_AllCoreTables::getBriefName($daoName), $newObject->id, $newObject);
1891     }
1892
1893     return $newObject;
1894   }
1895
1896   /**
1897    * Method that copies localizable fields from an old entity to a new one.
1898    *
1899    * Fixes bug dev/core#2479,
1900    * where non current locale fields are copied from current locale losing translation when copying
1901    *
1902    * @param int $entityID
1903    * @param int $newEntityID
1904    * @param array $fieldsToPrefix
1905    * @param array $fieldsToSuffix
1906    * @param array $fieldsToReplace
1907    */
1908   protected function copyLocalizable($entityID, $newEntityID, $fieldsToPrefix, $fieldsToSuffix, $fieldsToReplace) {
1909     $entity = get_class($this);
1910     $object = new $entity();
1911     $object->id = $entityID;
1912     $object->find();
1913
1914     $newObject = new $entity();
1915     $newObject->id = $newEntityID;
1916
1917     $newObject->find();
1918
1919     if ($object->fetch() && $newObject->fetch()) {
1920
1921       $fields = $object->fields();
1922       foreach ($fields as $name => $value) {
1923
1924         if ($name == 'id' || $value['name'] == 'id') {
1925           // copy everything but the id!
1926           continue;
1927         }
1928
1929         // only copy localizable fields
1930         if (!$value['localizable']) {
1931           continue;
1932         }
1933
1934         $dbName = $value['name'];
1935         $type = CRM_Utils_Type::typeToString($value['type']);
1936         $newObject->$dbName = $object->$dbName;
1937         if (isset($fieldsToPrefix[$dbName])) {
1938           $newObject->$dbName = $fieldsToPrefix[$dbName] . $newObject->$dbName;
1939         }
1940         if (isset($fieldsToSuffix[$dbName])) {
1941           $newObject->$dbName .= $fieldsToSuffix[$dbName];
1942         }
1943         if (isset($fieldsToReplace[$dbName])) {
1944           $newObject->$dbName = $fieldsToReplace[$dbName];
1945         }
1946
1947         if ($type == 'Timestamp' || $type == 'Date') {
1948           $newObject->$dbName = CRM_Utils_Date::isoToMysql($newObject->$dbName);
1949         }
1950
1951       }
1952       $newObject->save();
1953
1954     }
1955   }
1956
1957   /**
1958    * Method that copies custom fields values from an old entity to a new one.
1959    *
1960    * Fixes bug CRM-19302,
1961    * where if a custom field of File type was present, left both events using the same file,
1962    * breaking download URL's for the old event.
1963    *
1964    * @todo the goal here is to clean this up so that it works for any entity. Copy Generic already DOES some custom field stuff
1965    * but it seems to be bypassed & perhaps less good than this (or this just duplicates it...)
1966    *
1967    * @param int $entityID
1968    * @param int $newEntityID
1969    */
1970   public function copyCustomFields($entityID, $newEntityID) {
1971     $entity = CRM_Core_DAO_AllCoreTables::getBriefName(get_class($this));
1972     $tableName = CRM_Core_DAO_AllCoreTables::getTableForClass(get_class($this));
1973     // Obtain custom values for old event
1974     $customParams = $htmlType = [];
1975     $customValues = CRM_Core_BAO_CustomValueTable::getEntityValues($entityID, $entity);
1976
1977     // If custom values present, we copy them
1978     if (!empty($customValues)) {
1979       // Get Field ID's and identify File type attributes, to handle file copying.
1980       $fieldIds = implode(', ', array_keys($customValues));
1981       $sql = "SELECT id FROM civicrm_custom_field WHERE html_type = 'File' AND id IN ( {$fieldIds} )";
1982       $result = CRM_Core_DAO::executeQuery($sql);
1983
1984       // Build array of File type fields
1985       while ($result->fetch()) {
1986         $htmlType[] = $result->id;
1987       }
1988
1989       // Build params array of custom values
1990       foreach ($customValues as $field => $value) {
1991         if ($value !== NULL) {
1992           // Handle File type attributes
1993           if (in_array($field, $htmlType)) {
1994             $fileValues = CRM_Core_BAO_File::path($value, $entityID);
1995             $customParams["custom_{$field}_-1"] = [
1996               'name' => CRM_Utils_File::duplicate($fileValues[0]),
1997               'type' => $fileValues[1],
1998             ];
1999           }
2000           // Handle other types
2001           else {
2002             $customParams["custom_{$field}_-1"] = $value;
2003           }
2004         }
2005       }
2006
2007       // Save Custom Fields for new Event
2008       CRM_Core_BAO_CustomValueTable::postProcess($customParams, $tableName, $newEntityID, $entity);
2009     }
2010
2011     // copy activity attachments ( if any )
2012     CRM_Core_BAO_File::copyEntityFile($tableName, $entityID, $tableName, $newEntityID);
2013   }
2014
2015   /**
2016    * Cascade update through related entities.
2017    *
2018    * @param string $daoName
2019    * @param $fromId
2020    * @param $toId
2021    * @param array $newData
2022    *
2023    * @return CRM_Core_DAO|null
2024    */
2025   public static function cascadeUpdate($daoName, $fromId, $toId, $newData = []) {
2026     $object = new $daoName();
2027     $object->id = $fromId;
2028
2029     if ($object->find(TRUE)) {
2030       $newObject = new $daoName();
2031       $newObject->id = $toId;
2032
2033       if ($newObject->find(TRUE)) {
2034         $fields = $object->fields();
2035         foreach ($fields as $name => $value) {
2036           if ($name == 'id' || $value['name'] == 'id') {
2037             // copy everything but the id!
2038             continue;
2039           }
2040
2041           $colName = $value['name'];
2042           $newObject->$colName = $object->$colName;
2043
2044           if (substr($name, -5) == '_date' ||
2045             substr($name, -10) == '_date_time'
2046           ) {
2047             $newObject->$colName = CRM_Utils_Date::isoToMysql($newObject->$colName);
2048           }
2049         }
2050         foreach ($newData as $k => $v) {
2051           $newObject->$k = $v;
2052         }
2053         $newObject->save();
2054         return $newObject;
2055       }
2056     }
2057     return NULL;
2058   }
2059
2060   /**
2061    * Given the component id, compute the contact id
2062    * since its used for things like send email
2063    *
2064    * @param $componentIDs
2065    * @param string $tableName
2066    * @param string $idField
2067    *
2068    * @return array
2069    */
2070   public static function getContactIDsFromComponent($componentIDs, $tableName, $idField = 'id') {
2071     $contactIDs = [];
2072
2073     if (empty($componentIDs)) {
2074       return $contactIDs;
2075     }
2076
2077     $IDs = implode(',', $componentIDs);
2078     $query = "
2079 SELECT contact_id
2080   FROM $tableName
2081  WHERE $idField IN ( $IDs )
2082 ";
2083
2084     $dao = CRM_Core_DAO::executeQuery($query);
2085     while ($dao->fetch()) {
2086       $contactIDs[] = $dao->contact_id;
2087     }
2088     return $contactIDs;
2089   }
2090
2091   /**
2092    * Fetch object based on array of properties.
2093    *
2094    * @param string $daoName
2095    *   Name of the dao object.
2096    * @param string $fieldIdName
2097    * @param int $fieldId
2098    * @param $details
2099    * @param array $returnProperities
2100    *   An assoc array of fields that need to be returned, eg array( 'first_name', 'last_name').
2101    *
2102    * @return object
2103    *   an object of type referenced by daoName
2104    */
2105   public static function commonRetrieveAll($daoName, $fieldIdName, $fieldId, &$details, $returnProperities = NULL) {
2106     require_once str_replace('_', DIRECTORY_SEPARATOR, $daoName) . ".php";
2107     $object = new $daoName();
2108     $object->$fieldIdName = $fieldId;
2109
2110     // return only specific fields if returnproperties are sent
2111     if (!empty($returnProperities)) {
2112       $object->selectAdd();
2113       $object->selectAdd('id');
2114       $object->selectAdd(implode(',', $returnProperities));
2115     }
2116
2117     $object->find();
2118     while ($object->fetch()) {
2119       $defaults = [];
2120       self::storeValues($object, $defaults);
2121       $details[$object->id] = $defaults;
2122     }
2123
2124     return $details;
2125   }
2126
2127   /**
2128    * Drop all CiviCRM tables.
2129    *
2130    * @throws \CRM_Core_Exception
2131    */
2132   public static function dropAllTables() {
2133
2134     // first drop all the custom tables we've created
2135     CRM_Core_BAO_CustomGroup::dropAllTables();
2136
2137     // drop all multilingual views
2138     CRM_Core_I18n_Schema::dropAllViews();
2139
2140     CRM_Utils_File::sourceSQLFile(CIVICRM_DSN,
2141       dirname(__FILE__) . DIRECTORY_SEPARATOR .
2142       '..' . DIRECTORY_SEPARATOR .
2143       '..' . DIRECTORY_SEPARATOR .
2144       'sql' . DIRECTORY_SEPARATOR .
2145       'civicrm_drop.mysql'
2146     );
2147   }
2148
2149   /**
2150    * @param $string
2151    *
2152    * @return string
2153    */
2154   public static function escapeString($string) {
2155     static $_dao = NULL;
2156     if (!$_dao) {
2157       // If this is an atypical case (e.g. preparing .sql file before CiviCRM
2158       // has been installed), then we fallback DB-less str_replace escaping, as
2159       // we can't use mysqli_real_escape_string, as there is no DB connection.
2160       // Note: In typical usage, escapeString() will only check one conditional
2161       // ("if !$_dao") rather than two conditionals ("if !defined(DSN)")
2162       if (!defined('CIVICRM_DSN')) {
2163         // See http://php.net/manual/en/mysqli.real-escape-string.php for the
2164         // list of characters mysqli_real_escape_string escapes.
2165         $search = ["\\", "\x00", "\n", "\r", "'", '"', "\x1a"];
2166         $replace = ["\\\\", "\\0", "\\n", "\\r", "\'", '\"', "\\Z"];
2167         return str_replace($search, $replace, $string);
2168       }
2169       $_dao = new CRM_Core_DAO();
2170     }
2171     return $_dao->escape($string);
2172   }
2173
2174   /**
2175    * Escape a list of strings for use with "WHERE X IN (...)" queries.
2176    *
2177    * @param array $strings
2178    * @param string $default
2179    *   the value to use if $strings has no elements.
2180    * @return string
2181    *   eg "abc","def","ghi"
2182    */
2183   public static function escapeStrings($strings, $default = NULL) {
2184     static $_dao = NULL;
2185     if (!$_dao) {
2186       $_dao = new CRM_Core_DAO();
2187     }
2188
2189     if (empty($strings)) {
2190       return $default;
2191     }
2192
2193     $escapes = array_map([$_dao, 'escape'], $strings);
2194     return '"' . implode('","', $escapes) . '"';
2195   }
2196
2197   /**
2198    * @param $string
2199    *
2200    * @return string
2201    */
2202   public static function escapeWildCardString($string) {
2203     // CRM-9155
2204     // ensure we escape the single characters % and _ which are mysql wild
2205     // card characters and could come in via sortByCharacter
2206     // note that mysql does not escape these characters
2207     if ($string && in_array($string,
2208         ['%', '_', '%%', '_%']
2209       )
2210     ) {
2211       return '\\' . $string;
2212     }
2213
2214     return self::escapeString($string);
2215   }
2216
2217   /**
2218    * Creates a test object, including any required objects it needs via recursion
2219    * createOnly: only create in database, do not store or return the objects (useful for perf testing)
2220    * ONLY USE FOR TESTING
2221    *
2222    * @param string $daoName
2223    * @param array $params
2224    * @param int $numObjects
2225    * @param bool $createOnly
2226    *
2227    * @return object|array|NULL
2228    *   NULL if $createOnly. A single object if $numObjects==1. Otherwise, an array of multiple objects.
2229    */
2230   public static function createTestObject(
2231     $daoName,
2232     $params = [],
2233     $numObjects = 1,
2234     $createOnly = FALSE
2235   ) {
2236     //this is a test function  also backtrace is set for the test suite it sometimes unsets itself
2237     // so we re-set here in case
2238     $config = CRM_Core_Config::singleton();
2239     $config->backtrace = TRUE;
2240
2241     static $counter = 0;
2242     CRM_Core_DAO::$_testEntitiesToSkip = [
2243       'CRM_Core_DAO_Worldregion',
2244       'CRM_Core_DAO_StateProvince',
2245       'CRM_Core_DAO_Country',
2246       'CRM_Core_DAO_Domain',
2247       'CRM_Financial_DAO_FinancialType',
2248       //because valid ones exist & we use pick them due to pseudoconstant can't reliably create & delete these
2249     ];
2250
2251     // Prefer to instantiate BAO's instead of DAO's (when possible)
2252     // so that assignTestValue()/assignTestFK() can be overloaded.
2253     $baoName = str_replace('_DAO_', '_BAO_', $daoName);
2254     if ($baoName === 'CRM_Financial_BAO_FinancialTrxn') {
2255       // OMG OMG OMG this is so incredibly bad. The BAO is insanely named.
2256       // @todo create a new class called what the BAO SHOULD be
2257       // that extends BAO-crazy-name.... migrate.
2258       $baoName = 'CRM_Core_BAO_FinancialTrxn';
2259     }
2260     if (class_exists($baoName)) {
2261       $daoName = $baoName;
2262     }
2263
2264     for ($i = 0; $i < $numObjects; ++$i) {
2265
2266       ++$counter;
2267       /** @var CRM_Core_DAO $object */
2268       $object = new $daoName();
2269
2270       $fields = $object->fields();
2271       foreach ($fields as $fieldName => $fieldDef) {
2272         $dbName = $fieldDef['name'];
2273         $FKClassName = $fieldDef['FKClassName'] ?? NULL;
2274
2275         if (isset($params[$dbName]) && !is_array($params[$dbName])) {
2276           $object->$dbName = $params[$dbName];
2277         }
2278
2279         elseif ($dbName != 'id') {
2280           if ($FKClassName != NULL) {
2281             $object->assignTestFK($fieldName, $fieldDef, $params);
2282             continue;
2283           }
2284           else {
2285             $object->assignTestValue($fieldName, $fieldDef, $counter);
2286           }
2287         }
2288       }
2289
2290       $object->save();
2291
2292       if (!$createOnly) {
2293         $objects[$i] = $object;
2294       }
2295       else {
2296         unset($object);
2297       }
2298     }
2299
2300     if ($createOnly) {
2301       return NULL;
2302     }
2303     elseif ($numObjects == 1) {
2304       return $objects[0];
2305     }
2306     else {
2307       return $objects;
2308     }
2309   }
2310
2311   /**
2312    * Deletes the this object plus any dependent objects that are associated with it.
2313    * ONLY USE FOR TESTING
2314    *
2315    * @param string $daoName
2316    * @param array $params
2317    */
2318   public static function deleteTestObjects($daoName, $params = []) {
2319     //this is a test function  also backtrace is set for the test suite it sometimes unsets itself
2320     // so we re-set here in case
2321     $config = CRM_Core_Config::singleton();
2322     $config->backtrace = TRUE;
2323
2324     $object = new $daoName();
2325     $object->id = $params['id'] ?? NULL;
2326
2327     // array(array(0 => $daoName, 1 => $daoParams))
2328     $deletions = [];
2329     if ($object->find(TRUE)) {
2330
2331       $fields = $object->fields();
2332       foreach ($fields as $name => $value) {
2333
2334         $dbName = $value['name'];
2335
2336         $FKClassName = $value['FKClassName'] ?? NULL;
2337         $required = $value['required'] ?? NULL;
2338         if ($FKClassName != NULL
2339           && $object->$dbName
2340           && !in_array($FKClassName, CRM_Core_DAO::$_testEntitiesToSkip)
2341           && ($required || $dbName == 'contact_id')
2342           //I'm a bit stuck on this one - we might need to change the singleValueAlter so that the entities don't share a contact
2343           // to make this test process pass - line below makes pass for now
2344           && $dbName != 'member_of_contact_id'
2345         ) {
2346           // x
2347           $deletions[] = [$FKClassName, ['id' => $object->$dbName]];
2348         }
2349       }
2350     }
2351
2352     $object->delete();
2353
2354     foreach ($deletions as $deletion) {
2355       CRM_Core_DAO::deleteTestObjects($deletion[0], $deletion[1]);
2356     }
2357   }
2358
2359   /**
2360    * Set defaults when creating new entity.
2361    * (don't call this set defaults as already in use with different signature in some places)
2362    *
2363    * @param array $params
2364    * @param $defaults
2365    */
2366   public static function setCreateDefaults(&$params, $defaults) {
2367     if (!empty($params['id'])) {
2368       return;
2369     }
2370     foreach ($defaults as $key => $value) {
2371       if (!array_key_exists($key, $params) || $params[$key] === NULL) {
2372         $params[$key] = $value;
2373       }
2374     }
2375   }
2376
2377   /**
2378    * @param string $prefix
2379    * @param bool $addRandomString
2380    * @param null $string
2381    *
2382    * @return string
2383    * @deprecated
2384    * @see CRM_Utils_SQL_TempTable
2385    */
2386   public static function createTempTableName($prefix = 'civicrm', $addRandomString = TRUE, $string = NULL) {
2387     CRM_Core_Error::deprecatedFunctionWarning('Use CRM_Utils_SQL_TempTable interface to create temporary tables');
2388     $tableName = $prefix . "_temp";
2389
2390     if ($addRandomString) {
2391       if ($string) {
2392         $tableName .= "_" . $string;
2393       }
2394       else {
2395         $tableName .= "_" . md5(uniqid('', TRUE));
2396       }
2397     }
2398     return $tableName;
2399   }
2400
2401   /**
2402    * @param bool $view
2403    * @param bool $trigger
2404    *
2405    * @return bool
2406    */
2407   public static function checkTriggerViewPermission($view = TRUE, $trigger = TRUE) {
2408     if (\Civi::settings()->get('logging_no_trigger_permission')) {
2409       return TRUE;
2410     }
2411     // test for create view and trigger permissions and if allowed, add the option to go multilingual and logging
2412     $dao = new CRM_Core_DAO();
2413     try {
2414       if ($view) {
2415         $dao->query('CREATE OR REPLACE VIEW civicrm_domain_view AS SELECT * FROM civicrm_domain');
2416         $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
2417       }
2418
2419       if ($trigger) {
2420         $dao->query('CREATE TRIGGER civicrm_domain_trigger BEFORE INSERT ON civicrm_domain FOR EACH ROW BEGIN END');
2421         $dao->query('DROP TRIGGER IF EXISTS civicrm_domain_trigger');
2422       }
2423     }
2424     catch (Exception $e) {
2425       return FALSE;
2426     }
2427
2428     return TRUE;
2429   }
2430
2431   /**
2432    * @param null $message
2433    * @param bool $printDAO
2434    */
2435   public static function debugPrint($message = NULL, $printDAO = TRUE) {
2436     CRM_Utils_System::xMemory("{$message}: ");
2437
2438     if ($printDAO) {
2439       global $_DB_DATAOBJECT;
2440       $q = [];
2441       foreach (array_keys($_DB_DATAOBJECT['RESULTS']) as $id) {
2442         $q[] = $_DB_DATAOBJECT['RESULTS'][$id]->query;
2443       }
2444       CRM_Core_Error::debug('_DB_DATAOBJECT', $q);
2445     }
2446   }
2447
2448   /**
2449    * Build a list of triggers via hook and add them to (err, reconcile them
2450    * with) the database.
2451    *
2452    * @param string $tableName
2453    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
2454    * @param bool $force
2455    * @deprecated
2456    *
2457    * @see CRM-9716
2458    */
2459   public static function triggerRebuild($tableName = NULL, $force = FALSE) {
2460     Civi::service('sql_triggers')->rebuild($tableName, $force);
2461   }
2462
2463   /**
2464    * Because sql functions are sometimes lost, esp during db migration, we check here to avoid numerous support requests
2465    * @see http://issues.civicrm.org/jira/browse/CRM-13822
2466    * TODO: Alternative solutions might be
2467    *  * Stop using functions and find another way to strip numeric characters from phones
2468    *  * Give better error messages (currently a missing fn fatals with "unknown error")
2469    */
2470   public static function checkSqlFunctionsExist() {
2471     if (!self::$_checkedSqlFunctionsExist) {
2472       self::$_checkedSqlFunctionsExist = TRUE;
2473       $dao = CRM_Core_DAO::executeQuery("SHOW function status WHERE db = database() AND name = 'civicrm_strip_non_numeric'");
2474       if (!$dao->fetch()) {
2475         self::triggerRebuild();
2476       }
2477     }
2478   }
2479
2480   /**
2481    * Wrapper function to drop triggers.
2482    *
2483    * @param string $tableName
2484    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
2485    * @deprecated
2486    */
2487   public static function dropTriggers($tableName = NULL) {
2488     Civi::service('sql_triggers')->dropTriggers($tableName);
2489   }
2490
2491   /**
2492    * @param array $info
2493    *   per hook_civicrm_triggerInfo.
2494    * @param string $onlyTableName
2495    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
2496    * @deprecated
2497    */
2498   public static function createTriggers(&$info, $onlyTableName = NULL) {
2499     Civi::service('sql_triggers')->createTriggers($info, $onlyTableName);
2500   }
2501
2502   /**
2503    * Given a list of fields, create a list of references.
2504    *
2505    * @param string $className
2506    *   BAO/DAO class name.
2507    * @return array<CRM_Core_Reference_Interface>
2508    */
2509   public static function createReferenceColumns($className) {
2510     $result = [];
2511     $fields = $className::fields();
2512     foreach ($fields as $field) {
2513       if (isset($field['pseudoconstant'], $field['pseudoconstant']['optionGroupName'])) {
2514         $result[] = new CRM_Core_Reference_OptionValue(
2515           $className::getTableName(),
2516           $field['name'],
2517           'civicrm_option_value',
2518           CRM_Utils_Array::value('keyColumn', $field['pseudoconstant'], 'value'),
2519           $field['pseudoconstant']['optionGroupName']
2520         );
2521       }
2522     }
2523     return $result;
2524   }
2525
2526   /**
2527    * Find all records which refer to this entity.
2528    *
2529    * @return array
2530    *   Array of objects referencing this
2531    */
2532   public function findReferences() {
2533     $links = self::getReferencesToTable(static::getTableName());
2534
2535     $occurrences = [];
2536     foreach ($links as $refSpec) {
2537       /** @var $refSpec CRM_Core_Reference_Interface */
2538       $daoName = CRM_Core_DAO_AllCoreTables::getClassForTable($refSpec->getReferenceTable());
2539       $result = $refSpec->findReferences($this);
2540       if ($result) {
2541         while ($result->fetch()) {
2542           $obj = new $daoName();
2543           $obj->id = $result->id;
2544           $occurrences[] = $obj;
2545         }
2546       }
2547     }
2548
2549     return $occurrences;
2550   }
2551
2552   /**
2553    * @return array
2554    *   each item has keys:
2555    *   - name: string
2556    *   - type: string
2557    *   - count: int
2558    *   - table: string|null SQL table name
2559    *   - key: string|null SQL column name
2560    */
2561   public function getReferenceCounts() {
2562     $links = self::getReferencesToTable(static::getTableName());
2563
2564     $counts = [];
2565     foreach ($links as $refSpec) {
2566       /** @var $refSpec CRM_Core_Reference_Interface */
2567       $count = $refSpec->getReferenceCount($this);
2568       if ($count['count'] != 0) {
2569         $counts[] = $count;
2570       }
2571     }
2572
2573     foreach (CRM_Core_Component::getEnabledComponents() as $component) {
2574       /** @var $component CRM_Core_Component_Info */
2575       $counts = array_merge($counts, $component->getReferenceCounts($this));
2576     }
2577     CRM_Utils_Hook::referenceCounts($this, $counts);
2578
2579     return $counts;
2580   }
2581
2582   /**
2583    * List all tables which have hard foreign keys to this table.
2584    *
2585    * For now, this returns a description of every entity_id/entity_table
2586    * reference.
2587    * TODO: filter dynamic entity references on the $tableName, based on
2588    * schema metadata in dynamicForeignKey which enumerates a restricted
2589    * set of possible entity_table's.
2590    *
2591    * @param string $tableName
2592    *   Table referred to.
2593    *
2594    * @return CRM_Core_Reference_Interface[]
2595    *   structure of table and column, listing every table with a
2596    *   foreign key reference to $tableName, and the column where the key appears.
2597    */
2598   public static function getReferencesToTable($tableName) {
2599     $refsFound = [];
2600     foreach (CRM_Core_DAO_AllCoreTables::getClasses() as $daoClassName) {
2601       $links = $daoClassName::getReferenceColumns();
2602
2603       foreach ($links as $refSpec) {
2604         /** @var $refSpec CRM_Core_Reference_Interface */
2605         if ($refSpec->matchesTargetTable($tableName)) {
2606           $refsFound[] = $refSpec;
2607         }
2608       }
2609     }
2610     return $refsFound;
2611   }
2612
2613   /**
2614    * Get all references to contact table.
2615    *
2616    * This includes core tables, custom group tables, tables added by the merge
2617    * hook and  the entity_tag table.
2618    *
2619    * Refer to CRM-17454 for information on the danger of querying the information
2620    * schema to derive this.
2621    *
2622    * @throws \CiviCRM_API3_Exception
2623    */
2624   public static function getReferencesToContactTable() {
2625     if (isset(\Civi::$statics[__CLASS__]) && isset(\Civi::$statics[__CLASS__]['contact_references'])) {
2626       return \Civi::$statics[__CLASS__]['contact_references'];
2627     }
2628     $contactReferences = [];
2629     $coreReferences = CRM_Core_DAO::getReferencesToTable('civicrm_contact');
2630     foreach ($coreReferences as $coreReference) {
2631       if (
2632         // Exclude option values
2633         !is_a($coreReference, 'CRM_Core_Reference_Dynamic') &&
2634         // Exclude references to other columns
2635         $coreReference->getTargetKey() === 'id'
2636       ) {
2637         $contactReferences[$coreReference->getReferenceTable()][] = $coreReference->getReferenceKey();
2638       }
2639     }
2640     self::appendCustomTablesExtendingContacts($contactReferences);
2641     self::appendCustomContactReferenceFields($contactReferences);
2642     \Civi::$statics[__CLASS__]['contact_references'] = $contactReferences;
2643     return \Civi::$statics[__CLASS__]['contact_references'];
2644   }
2645
2646   /**
2647    * Get all dynamic references to the given table.
2648    *
2649    * @param string $tableName
2650    *
2651    * @return array
2652    */
2653   public static function getDynamicReferencesToTable($tableName) {
2654     if (!isset(\Civi::$statics[__CLASS__]['contact_references_dynamic'][$tableName])) {
2655       \Civi::$statics[__CLASS__]['contact_references_dynamic'][$tableName] = [];
2656       $coreReferences = CRM_Core_DAO::getReferencesToTable($tableName);
2657       foreach ($coreReferences as $coreReference) {
2658         if ($coreReference instanceof \CRM_Core_Reference_Dynamic) {
2659           \Civi::$statics[__CLASS__]['contact_references_dynamic'][$tableName][$coreReference->getReferenceTable()][] = [$coreReference->getReferenceKey(), $coreReference->getTypeColumn()];
2660         }
2661       }
2662     }
2663     return \Civi::$statics[__CLASS__]['contact_references_dynamic'][$tableName];
2664   }
2665
2666   /**
2667    * Add custom tables that extend contacts to the list of contact references.
2668    *
2669    * CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity seems like a safe-ish
2670    * function to be sure all are retrieved & we don't miss subtypes or inactive or multiples
2671    * - the down side is it is not cached.
2672    *
2673    * Further changes should be include tests in the CRM_Core_MergerTest class
2674    * to ensure that disabled, subtype, multiple etc groups are still captured.
2675    *
2676    * @param array $cidRefs
2677    */
2678   public static function appendCustomTablesExtendingContacts(&$cidRefs) {
2679     $customValueTables = CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity('Contact');
2680     $customValueTables->find();
2681     while ($customValueTables->fetch()) {
2682       $cidRefs[$customValueTables->table_name][] = 'entity_id';
2683     }
2684   }
2685
2686   /**
2687    * Add custom ContactReference fields to the list of contact references
2688    *
2689    * This includes active and inactive fields/groups
2690    *
2691    * @param array $cidRefs
2692    *
2693    * @throws \CiviCRM_API3_Exception
2694    */
2695   public static function appendCustomContactReferenceFields(&$cidRefs) {
2696     $fields = civicrm_api3('CustomField', 'get', [
2697       'return'    => ['column_name', 'custom_group_id.table_name'],
2698       'data_type' => 'ContactReference',
2699       'options' => ['limit' => 0],
2700     ])['values'];
2701     foreach ($fields as $field) {
2702       $cidRefs[$field['custom_group_id.table_name']][] = $field['column_name'];
2703     }
2704   }
2705
2706   /**
2707    * Lookup the value of a MySQL global configuration variable.
2708    *
2709    * @param string $name
2710    *   E.g. "thread_stack".
2711    * @param mixed $default
2712    * @return mixed
2713    */
2714   public static function getGlobalSetting($name, $default = NULL) {
2715     // Alternatively, SELECT @@GLOBAL.thread_stack, but
2716     // that has been reported to fail under MySQL 5.0 for OS X
2717     $escapedName = self::escapeString($name);
2718     $dao = CRM_Core_DAO::executeQuery("SHOW VARIABLES LIKE '$escapedName'");
2719     if ($dao->fetch()) {
2720       return $dao->Value;
2721     }
2722     else {
2723       return $default;
2724     }
2725   }
2726
2727   /**
2728    * Update the fields array to also hold keys for pseudoconstant fields that relate to contained fields.
2729    *
2730    * This is relevant where we want to offer both the ID field and the label field
2731    * as an option, e.g. search builder.
2732    *
2733    * It is currently limited for optionGroupName & id+ name+ FK combos for purposes keeping the scope of the
2734    * change small, but is appropriate for other sorts of pseudoconstants.
2735    *
2736    * @param array $fields
2737    */
2738   public static function appendPseudoConstantsToFields(&$fields) {
2739     foreach ($fields as $fieldUniqueName => $field) {
2740       if (!empty($field['pseudoconstant'])) {
2741         $pseudoConstant = $field['pseudoconstant'];
2742         if (!empty($pseudoConstant['optionGroupName'])) {
2743           $fields[$pseudoConstant['optionGroupName']] = [
2744             'title' => CRM_Core_BAO_OptionGroup::getTitleByName($pseudoConstant['optionGroupName']),
2745             'name' => $pseudoConstant['optionGroupName'],
2746             'data_type' => CRM_Utils_Type::T_STRING,
2747             'is_pseudofield_for' => $fieldUniqueName,
2748           ];
2749         }
2750         // We restrict to id + name + FK as we are extending this a bit, but cautiously.
2751         elseif (
2752           !empty($field['FKClassName'])
2753           && CRM_Utils_Array::value('keyColumn', $pseudoConstant) === 'id'
2754           && CRM_Utils_Array::value('labelColumn', $pseudoConstant) === 'name'
2755         ) {
2756           $pseudoFieldName = str_replace('_' . $pseudoConstant['keyColumn'], '', $field['name']);
2757           // This if is just an extra caution when adding change.
2758           if (!isset($fields[$pseudoFieldName])) {
2759             $daoName = $field['FKClassName'];
2760             $fkFields = $daoName::fields();
2761             foreach ($fkFields as $fkField) {
2762               if ($fkField['name'] === $pseudoConstant['labelColumn']) {
2763                 $fields[$pseudoFieldName] = [
2764                   'name' => $pseudoFieldName,
2765                   'is_pseudofield_for' => $field['name'],
2766                   'title' => $fkField['title'],
2767                   'data_type' => $fkField['type'],
2768                   'where' => $field['where'],
2769                 ];
2770               }
2771             }
2772           }
2773         }
2774       }
2775     }
2776   }
2777
2778   /**
2779    * Get options for the called BAO object's field.
2780    *
2781    * This function can be overridden by each BAO to add more logic related to context.
2782    * The overriding function will generally call the lower-level CRM_Core_PseudoConstant::get
2783    *
2784    * @param string $fieldName
2785    * @param string $context
2786    * @see CRM_Core_DAO::buildOptionsContext
2787    * @param array $props
2788    *   Raw field values; whatever is known about this bao object.
2789    *
2790    * Note: $props can contain unsanitized input and should not be passed directly to CRM_Core_PseudoConstant::get
2791    *
2792    * @return array|bool
2793    */
2794   public static function buildOptions($fieldName, $context = NULL, $props = []) {
2795     // If a given bao does not override this function
2796     $baoName = get_called_class();
2797     return CRM_Core_PseudoConstant::get($baoName, $fieldName, [], $context);
2798   }
2799
2800   /**
2801    * Populate option labels for this object's fields.
2802    *
2803    * @throws exception if called directly on the base class
2804    */
2805   public function getOptionLabels() {
2806     $fields = $this->fields();
2807     if ($fields === NULL) {
2808       throw new Exception('Cannot call getOptionLabels on CRM_Core_DAO');
2809     }
2810     foreach ($fields as $field) {
2811       $name = $field['name'] ?? NULL;
2812       if ($name && isset($this->$name)) {
2813         $label = CRM_Core_PseudoConstant::getLabel(get_class($this), $name, $this->$name);
2814         if ($label !== FALSE) {
2815           // Append 'label' onto the field name
2816           $labelName = $name . '_label';
2817           $this->$labelName = $label;
2818         }
2819       }
2820     }
2821   }
2822
2823   /**
2824    * Provides documentation and validation for the buildOptions $context param
2825    *
2826    * @param string $context
2827    *
2828    * @throws CRM_Core_Exception
2829    * @return array
2830    */
2831   public static function buildOptionsContext($context = NULL) {
2832     $contexts = [
2833       'get' => "get: all options are returned, even if they are disabled; labels are translated.",
2834       'create' => "create: options are filtered appropriately for the object being created/updated; labels are translated.",
2835       'search' => "search: searchable options are returned; labels are translated.",
2836       'validate' => "validate: all options are returned, even if they are disabled; machine names are used in place of labels.",
2837       'abbreviate' => "abbreviate: enabled options are returned; labels are replaced with abbreviations.",
2838       'match' => "match: enabled options are returned using machine names as keys; labels are translated.",
2839     ];
2840     // Validation: enforce uniformity of this param
2841     if ($context !== NULL && !isset($contexts[$context])) {
2842       throw new CRM_Core_Exception("'$context' is not a valid context for buildOptions.");
2843     }
2844     return $contexts;
2845   }
2846
2847   /**
2848    * @param string $fieldName
2849    * @return bool|array
2850    */
2851   public function getFieldSpec($fieldName) {
2852     $fields = $this->fields();
2853
2854     // Support "unique names" as well as sql names
2855     $fieldKey = $fieldName;
2856     if (empty($fields[$fieldKey])) {
2857       $fieldKeys = $this->fieldKeys();
2858       $fieldKey = $fieldKeys[$fieldName] ?? NULL;
2859     }
2860     // If neither worked then this field doesn't exist. Return false.
2861     if (empty($fields[$fieldKey])) {
2862       return FALSE;
2863     }
2864     return $fields[$fieldKey];
2865   }
2866
2867   /**
2868    * Get SQL where clause for SQL filter syntax input parameters.
2869    *
2870    * SQL version of api function to assign filters to the DAO based on the syntax
2871    * $field => array('IN' => array(4,6,9))
2872    * OR
2873    * $field => array('LIKE' => array('%me%))
2874    * etc
2875    *
2876    * @param string $fieldName
2877    *   Name of fields.
2878    * @param array $filter
2879    *   filter to be applied indexed by operator.
2880    * @param string $type
2881    *   type of field (not actually used - nor in api @todo ).
2882    * @param string $alias
2883    *   alternative field name ('as') @todo- not actually used.
2884    * @param bool $returnSanitisedArray
2885    *   Return a sanitised array instead of a clause.
2886    *   this is primarily so we can add filters @ the api level to the Query object based fields
2887    *
2888    * @throws Exception
2889    *
2890    * @return NULL|string|array
2891    *   a string is returned if $returnSanitisedArray is not set, otherwise and Array or NULL
2892    *   depending on whether it is supported as yet
2893    */
2894   public static function createSQLFilter($fieldName, $filter, $type = NULL, $alias = NULL, $returnSanitisedArray = FALSE) {
2895     foreach ($filter as $operator => $criteria) {
2896       if (!CRM_Core_BAO_SchemaHandler::databaseSupportsUTF8MB4()) {
2897         foreach ((array) $criteria as $criterion) {
2898           if (!empty($criterion) && !is_numeric($criterion)
2899             // The first 2 criteria are redundant but are added as they
2900             // seem like they would
2901             // be quicker than this 3rd check.
2902             && max(array_map('ord', str_split($criterion))) >= 240) {
2903             // String contains unsupported emojis.
2904             // We return a clause that resolves to false as an emoji string by definition cannot be saved.
2905             // note that if we return just 0 for false if gets lost in empty checks.
2906             // https://stackoverflow.com/questions/16496554/can-php-detect-4-byte-encoded-utf8-chars
2907             return '0 = 1';
2908           }
2909         }
2910       }
2911
2912       if (in_array($operator, self::acceptedSQLOperators(), TRUE)) {
2913         switch ($operator) {
2914           // unary operators
2915           case 'IS NULL':
2916           case 'IS NOT NULL':
2917             if (!$returnSanitisedArray) {
2918               return (sprintf('%s %s', $fieldName, $operator));
2919             }
2920             else {
2921               return (sprintf('%s %s ', $fieldName, $operator));
2922             }
2923             break;
2924
2925           // ternary operators
2926           case 'BETWEEN':
2927           case 'NOT BETWEEN':
2928             if ((empty($criteria[0]) && !in_array($criteria[0], ['0', 0]))|| (empty($criteria[1]) &&  !in_array($criteria[1], ['0', 0]))) {
2929               throw new Exception("invalid criteria for $operator");
2930             }
2931             if (!$returnSanitisedArray) {
2932               return (sprintf('%s ' . $operator . ' "%s" AND "%s"', $fieldName, CRM_Core_DAO::escapeString($criteria[0]), CRM_Core_DAO::escapeString($criteria[1])));
2933             }
2934             else {
2935               // not yet implemented (tests required to implement)
2936               return NULL;
2937             }
2938             break;
2939
2940           // n-ary operators
2941           case 'IN':
2942           case 'NOT IN':
2943             if (empty($criteria)) {
2944               throw new Exception("invalid criteria for $operator");
2945             }
2946             $escapedCriteria = array_map([
2947               'CRM_Core_DAO',
2948               'escapeString',
2949             ], $criteria);
2950             if (!$returnSanitisedArray) {
2951               return (sprintf('%s %s ("%s")', $fieldName, $operator, implode('", "', $escapedCriteria)));
2952             }
2953             return $escapedCriteria;
2954
2955           // binary operators
2956
2957           default:
2958             if (!$returnSanitisedArray) {
2959               return (sprintf('%s %s "%s"', $fieldName, $operator, CRM_Core_DAO::escapeString($criteria)));
2960             }
2961             else {
2962               // not yet implemented (tests required to implement)
2963               return NULL;
2964             }
2965         }
2966       }
2967     }
2968   }
2969
2970   /**
2971    * @see http://issues.civicrm.org/jira/browse/CRM-9150
2972    * support for other syntaxes is discussed in ticket but being put off for now
2973    * @return string[]
2974    */
2975   public static function acceptedSQLOperators() {
2976     return [
2977       '=',
2978       '<=',
2979       '>=',
2980       '>',
2981       '<',
2982       'LIKE',
2983       "<>",
2984       "!=",
2985       "NOT LIKE",
2986       'IN',
2987       'NOT IN',
2988       'BETWEEN',
2989       'NOT BETWEEN',
2990       'IS NOT NULL',
2991       'IS NULL',
2992     ];
2993   }
2994
2995   /**
2996    * SQL has a limit of 64 characters on various names:
2997    * table name, trigger name, column name ...
2998    *
2999    * For custom groups and fields we generated names from user entered input
3000    * which can be longer than this length, this function helps with creating
3001    * strings that meet various criteria.
3002    *
3003    * @param string $string
3004    *   The string to be shortened.
3005    * @param int $length
3006    *   The max length of the string.
3007    *
3008    * @param bool $makeRandom
3009    *
3010    * @return string
3011    */
3012   public static function shortenSQLName($string, $length = 60, $makeRandom = FALSE) {
3013     // early return for strings that meet the requirements
3014     if (strlen($string) <= $length) {
3015       return $string;
3016     }
3017
3018     // easy return for calls that dont need a randomized uniq string
3019     if (!$makeRandom) {
3020       return substr($string, 0, $length);
3021     }
3022
3023     // the string is longer than the length and we need a uniq string
3024     // for the same tablename we need the same uniq string every time
3025     // hence we use md5 on the string, which is not random
3026     // we'll append 8 characters to the end of the tableName
3027     $md5string = substr(md5($string), 0, 8);
3028     return substr($string, 0, $length - 8) . "_{$md5string}";
3029   }
3030
3031   /**
3032    * https://issues.civicrm.org/jira/browse/CRM-17748
3033    * Sets the internal options to be used on a query
3034    *
3035    * @param array $options
3036    *
3037    */
3038   public function setOptions($options) {
3039     if (is_array($options)) {
3040       $this->_options = $options;
3041     }
3042   }
3043
3044   /**
3045    * https://issues.civicrm.org/jira/browse/CRM-17748
3046    * wrapper to pass internal DAO options down to DB_mysql/DB_Common level
3047    *
3048    * @param array $options
3049    *
3050    */
3051   protected function _setDBOptions($options) {
3052     global $_DB_DATAOBJECT;
3053
3054     if (is_array($options) && count($options)) {
3055       $conn = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
3056       foreach ($options as $option_name => $option_value) {
3057         $conn->setOption($option_name, $option_value);
3058       }
3059     }
3060   }
3061
3062   /**
3063    * @deprecated
3064    * @param array $params
3065    */
3066   public function setApiFilter(&$params) {
3067   }
3068
3069   /**
3070    * Generates acl clauses suitable for adding to WHERE or ON when doing an api.get for this entity
3071    *
3072    * Return format is in the form of fieldname => clauses starting with an operator. e.g.:
3073    * ```
3074    *   array(
3075    *     'location_type_id' => array('IS NOT NULL', 'IN (1,2,3)')
3076    *   )
3077    * ```
3078    *
3079    * Note that all array keys must be actual field names in this entity. Use subqueries to filter on other tables e.g. custom values.
3080    *
3081    * @return array
3082    */
3083   public function addSelectWhereClause() {
3084     $clauses = [];
3085     $fields = $this->fields();
3086     foreach ($fields as $fieldName => $field) {
3087       // Clause for contact-related entities like Email, Relationship, etc.
3088       if (strpos($fieldName, 'contact_id') === 0 && CRM_Utils_Array::value('FKClassName', $field) == 'CRM_Contact_DAO_Contact') {
3089         $clauses[$fieldName] = CRM_Utils_SQL::mergeSubquery('Contact');
3090       }
3091       // Clause for an entity_table/entity_id combo
3092       if ($fieldName === 'entity_id' && isset($fields['entity_table'])) {
3093         $relatedClauses = [];
3094         $relatedEntities = $this->buildOptions('entity_table', 'get');
3095         foreach ((array) $relatedEntities as $table => $ent) {
3096           if (!empty($ent)) {
3097             $ent = CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table));
3098             $subquery = CRM_Utils_SQL::mergeSubquery($ent);
3099             if ($subquery) {
3100               $relatedClauses[] = "(entity_table = '$table' AND entity_id " . implode(' AND entity_id ', $subquery) . ")";
3101             }
3102             else {
3103               $relatedClauses[] = "(entity_table = '$table')";
3104             }
3105           }
3106         }
3107         if ($relatedClauses) {
3108           $clauses['id'] = 'IN (SELECT id FROM `' . $this->tableName() . '` WHERE (' . implode(') OR (', $relatedClauses) . '))';
3109         }
3110       }
3111     }
3112     CRM_Utils_Hook::selectWhereClause($this, $clauses);
3113     return $clauses;
3114   }
3115
3116   /**
3117    * This returns the final permissioned query string for this entity
3118    *
3119    * With acls from related entities + additional clauses from hook_civicrm_selectWhereClause
3120    *
3121    * @param string $tableAlias
3122    * @return array
3123    */
3124   public static function getSelectWhereClause($tableAlias = NULL) {
3125     $bao = new static();
3126     if ($tableAlias === NULL) {
3127       $tableAlias = $bao->tableName();
3128     }
3129     $clauses = [];
3130     foreach ((array) $bao->addSelectWhereClause() as $field => $vals) {
3131       $clauses[$field] = NULL;
3132       if ($vals) {
3133         $clauses[$field] = "(`$tableAlias`.`$field` IS NULL OR (`$tableAlias`.`$field` " . implode(" AND `$tableAlias`.`$field` ", (array) $vals) . '))';
3134       }
3135     }
3136     return $clauses;
3137   }
3138
3139   /**
3140    * ensure database name is 'safe', i.e. only contains word characters (includes underscores)
3141    * and dashes, and contains at least one [a-z] case insensitive.
3142    *
3143    * @param $database
3144    *
3145    * @return bool
3146    */
3147   public static function requireSafeDBName($database) {
3148     $matches = [];
3149     preg_match(
3150       "/^[\w\-]*[a-z]+[\w\-]*$/i",
3151       $database,
3152       $matches
3153     );
3154     if (empty($matches)) {
3155       return FALSE;
3156     }
3157     return TRUE;
3158   }
3159
3160   /**
3161    * Transform an array to a serialized string for database storage.
3162    *
3163    * @param array|null $value
3164    * @param int $serializationType
3165    * @return string|null
3166    *
3167    * @throws \Exception
3168    */
3169   public static function serializeField($value, $serializationType) {
3170     if ($value === NULL) {
3171       return NULL;
3172     }
3173     switch ($serializationType) {
3174       case self::SERIALIZE_SEPARATOR_BOOKEND:
3175         return $value === [] ? '' : CRM_Utils_Array::implodePadded($value);
3176
3177       case self::SERIALIZE_SEPARATOR_TRIMMED:
3178         return is_array($value) ? implode(self::VALUE_SEPARATOR, $value) : $value;
3179
3180       case self::SERIALIZE_JSON:
3181         return is_array($value) ? json_encode($value) : $value;
3182
3183       case self::SERIALIZE_PHP:
3184         return is_array($value) ? serialize($value) : $value;
3185
3186       case self::SERIALIZE_COMMA:
3187         return is_array($value) ? implode(',', $value) : $value;
3188
3189       default:
3190         throw new Exception('Unknown serialization method for field.');
3191     }
3192   }
3193
3194   /**
3195    * Transform a serialized string from the database into an array.
3196    *
3197    * @param string|null $value
3198    * @param $serializationType
3199    *
3200    * @return array|null
3201    * @throws CRM_Core_Exception
3202    */
3203   public static function unSerializeField($value, $serializationType) {
3204     if ($value === NULL) {
3205       return NULL;
3206     }
3207     if ($value === '') {
3208       return [];
3209     }
3210     switch ($serializationType) {
3211       case self::SERIALIZE_SEPARATOR_BOOKEND:
3212         return (array) CRM_Utils_Array::explodePadded($value);
3213
3214       case self::SERIALIZE_SEPARATOR_TRIMMED:
3215         return explode(self::VALUE_SEPARATOR, trim($value));
3216
3217       case self::SERIALIZE_JSON:
3218         return strlen($value) ? json_decode($value, TRUE) : [];
3219
3220       case self::SERIALIZE_PHP:
3221         return strlen($value) ? CRM_Utils_String::unserialize($value) : [];
3222
3223       case self::SERIALIZE_COMMA:
3224         return explode(',', trim(str_replace(', ', '', $value)));
3225
3226       default:
3227         throw new CRM_Core_Exception('Unknown serialization method for field.');
3228     }
3229   }
3230
3231   /**
3232    * @return array
3233    */
3234   public static function getEntityRefFilters() {
3235     return [];
3236   }
3237
3238   /**
3239    * Get exportable fields with pseudoconstants rendered as an extra field.
3240    *
3241    * @param string $baoClass
3242    *
3243    * @return array
3244    */
3245   public static function getExportableFieldsWithPseudoConstants($baoClass) {
3246     if (method_exists($baoClass, 'exportableFields')) {
3247       $fields = $baoClass::exportableFields();
3248     }
3249     else {
3250       $fields = $baoClass::export();
3251     }
3252     CRM_Core_DAO::appendPseudoConstantsToFields($fields);
3253     return $fields;
3254   }
3255
3256   /**
3257    * Remove item from static cache during update/delete operations
3258    */
3259   private function clearDbColumnValueCache() {
3260     $daoName = get_class($this);
3261     while (strpos($daoName, '_BAO_') !== FALSE) {
3262       $daoName = get_parent_class($daoName);
3263     }
3264     if (isset($this->id)) {
3265       unset(self::$_dbColumnValueCache[$daoName]['id'][$this->id]);
3266     }
3267     if (isset($this->name)) {
3268       unset(self::$_dbColumnValueCache[$daoName]['name'][$this->name]);
3269     }
3270   }
3271
3272   /**
3273    * Return a mapping from field-name to the corresponding key (as used in fields()).
3274    *
3275    * @return array
3276    *   Array(string $name => string $uniqueName).
3277    */
3278   public static function fieldKeys() {
3279     return array_flip(CRM_Utils_Array::collect('name', static::fields()));
3280   }
3281
3282   /**
3283    * Returns system paths related to this entity (as defined in the xml schema)
3284    *
3285    * @return array
3286    */
3287   public static function getEntityPaths() {
3288     return static::$_paths ?? [];
3289   }
3290
3291 }