CRM/Upgrade/Incremental/php/FourFour.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  * Upgrade logic for 4.4
  30  */
  31 class CRM_Upgrade_Incremental_php_FourFour extends CRM_Upgrade_Incremental_Base {
  32   const MAX_WORD_REPLACEMENT_SIZE = 255;
  33
  34   /**
  35    * Compute any messages which should be displayed beforeupgrade.
  36    *
  37    * Note: This function is called iteratively for each upcoming
  38    * revision to the database.
  39    *
  40    * @param $preUpgradeMessage
  41    * @param string $rev
  42    *   a version number, e.g. '4.4.alpha1', '4.4.beta3', '4.4.0'.
  43    * @param string $currentVer
  44    */
  45   public function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
  46     if ($rev == '4.4.beta1') {
  47       $apiCalls = self::getConfigArraysAsAPIParams(FALSE);
  48       $oversizedEntries = 0;
  49       foreach ($apiCalls as $params) {
  50         if (!self::isValidWordReplacement($params)) {
  51           $oversizedEntries++;
  52         }
  53       }
  54       if ($oversizedEntries > 0) {
  55         $preUpgradeMessage .= '<br/>' . ts("WARNING: There are %1 word-replacement entries which will not be valid in v4.4+ (eg with over 255 characters). They will be dropped during upgrade. For details, consult the CiviCRM log.", array(
  56             1 => $oversizedEntries,
  57           ));
  58       }
  59     }
  60   }
  61
  62   /**
  63    * Compute any messages which should be displayed after upgrade.
  64    *
  65    * @param string $postUpgradeMessage
  66    *   alterable.
  67    * @param string $rev
  68    *   an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs.
  69    */
  70   public function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
  71     if ($rev == '4.4.1') {
  72       $config = CRM_Core_Config::singleton();
  73       if (!empty($config->useIDS)) {
  74         $postUpgradeMessage .= '<br />' . ts("The setting to skip IDS check has been removed. Your site has this configured in civicrm.settings.php but it will no longer work. Instead, use the new permission 'skip IDS check' to bypass the IDS system.");
  75       }
  76     }
  77     if ($rev == '4.4.3') {
  78       $postUpgradeMessage .= '<br /><br />' . ts('Default versions of the following System Workflow Message Templates have been modified to handle new functionality: <ul><li>Events - Registration Confirmation and Receipt (on-line)</li></ul> If you have modified these templates, please review the new default versions and implement updates as needed to your copies (Administer > Communications > Message Templates > System Workflow Messages).');
  79     }
  80     if ($rev == '4.4.3') {
  81       $query = "SELECT cft.id financial_trxn
  82 FROM civicrm_financial_trxn cft
  83 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.financial_trxn_id = cft.id
  84 LEFT JOIN civicrm_contribution cc ON ceft.entity_id = cc.id
  85 WHERE ceft.entity_table = 'civicrm_contribution' AND cft.payment_instrument_id IS NULL;";
  86       $dao = CRM_Core_DAO::executeQuery($query);
  87       if ($dao->N) {
  88         $postUpgradeMessage .= '<br /><br /><strong>' . ts('Your database contains %1 financial transaction records with no payment instrument (Paid By is empty). If you use the Accounting Batches feature this may result in unbalanced transactions. If you do not use this feature, you can ignore the condition (although you will be required to select a Paid By value for new transactions). <a href="%2" target="_blank">You can review steps to correct transactions with missing payment instruments on the wiki.</a>', array(
  89               1 => $dao->N,
  90               2 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Fixing+Transactions+Missing+a+Payment+Instrument+-+4.4.3+Upgrades',
  91             )) . '</strong>';
  92       }
  93     }
  94     if ($rev == '4.4.6') {
  95       $postUpgradeMessage .= '<br /><br /><strong>' . ts('Your contact image urls have been upgraded. If your contact image urls did not follow the standard format for image Urls they have not been upgraded. Please check the log to see image urls that were not upgraded.');
  96     }
  97   }
  98
  99   /**
 100    * Upgrade 4.4.alpha1.
 101    *
 102    * @param string $rev
 103    *
 104    * @return bool
 105    */
 106   public function upgrade_4_4_alpha1($rev) {
 107     // task to process sql
 108     $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.4.alpha1')), 'runSql', $rev);
 109
 110     // Consolidate activity contacts CRM-12274.
 111     $this->addTask('Consolidate activity contacts', 'activityContacts');
 112
 113     return TRUE;
 114   }
 115
 116   /**
 117    * @param $rev
 118    */
 119   public function upgrade_4_4_beta1($rev) {
 120     $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.4.beta1')), 'runSql', $rev);
 121
 122     // add new 'data' column in civicrm_batch
 123     $query = 'ALTER TABLE civicrm_batch ADD data LONGTEXT NULL COMMENT "cache entered data"';
 124     CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
 125
 126     // check if batch entry data exists in civicrm_cache table
 127     $query = 'SELECT path, data FROM civicrm_cache WHERE group_name = "batch entry"';
 128     $dao = CRM_Core_DAO::executeQuery($query);
 129     while ($dao->fetch()) {
 130       // get batch id $batchId[2]
 131       $batchId = explode('-', $dao->path);
 132       $data = unserialize($dao->data);
 133
 134       // move the data to civicrm_batch table
 135       CRM_Core_DAO::setFieldValue('CRM_Batch_DAO_Batch', $batchId[2], 'data', json_encode(array('values' => $data)));
 136     }
 137
 138     // delete entries from civicrm_cache table
 139     $query = 'DELETE FROM civicrm_cache WHERE group_name = "batch entry"';
 140     CRM_Core_DAO::executeQuery($query);
 141
 142     $this->addTask('Migrate custom word-replacements', 'wordReplacements');
 143   }
 144
 145   /**
 146    * @param $rev
 147    */
 148   public function upgrade_4_4_1($rev) {
 149     $config = CRM_Core_Config::singleton();
 150     // CRM-13327 upgrade handling for the newly added name badges
 151     $ogID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'name_badge', 'id', 'name');
 152     $nameBadges = array_flip(array_values(CRM_Core_BAO_OptionValue::getOptionValuesAssocArrayFromName('name_badge')));
 153     unset($nameBadges['Avery 5395']);
 154     if (!empty($nameBadges)) {
 155       $dimension = '{"paper-size":"a4","orientation":"portrait","font-name":"times","font-size":6,"font-style":"","NX":2,"NY":4,"metric":"mm","lMargin":6,"tMargin":19,"SpaceX":0,"SpaceY":0,"width":100,"height":65,"lPadding":0,"tPadding":0}';
 156       $query = "UPDATE civicrm_option_value
 157         SET value = '{$dimension}'
 158         WHERE option_group_id = %1 AND name = 'Fattorini Name Badge 100x65'";
 159
 160       CRM_Core_DAO::executeQuery($query, array(1 => array($ogID, 'Integer')));
 161     }
 162     else {
 163       $dimensions = array(
 164         1 => '{"paper-size":"a4","orientation":"landscape","font-name":"times","font-size":6,"font-style":"","NX":2,"NY":1,"metric":"mm","lMargin":25,"tMargin":27,"SpaceX":0,"SpaceY":35,"width":106,"height":150,"lPadding":5,"tPadding":5}',
 165         2 => '{"paper-size":"a4","orientation":"portrait","font-name":"times","font-size":6,"font-style":"","NX":2,"NY":4,"metric":"mm","lMargin":6,"tMargin":19,"SpaceX":0,"SpaceY":0,"width":100,"height":65,"lPadding":0,"tPadding":0}',
 166         3 => '{"paper-size":"a4","orientation":"portrait","font-name":"times","font-size":6,"font-style":"","NX":2,"NY":2,"metric":"mm","lMargin":10,"tMargin":28,"SpaceX":0,"SpaceY":0,"width":96,"height":121,"lPadding":5,"tPadding":5}',
 167       );
 168       $insertStatements = array(
 169         1 => "($ogID, %1, '{$dimensions[1]}', %1, NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL, NULL)",
 170         2 => "($ogID, %2, '{$dimensions[2]}', %2, NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL, NULL)",
 171         3 => "($ogID, %3, '{$dimensions[3]}', %3, NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL, NULL)",
 172       );
 173
 174       $queryParams = array(
 175         1 => array('A6 Badge Portrait 150x106', 'String'),
 176         2 => array('Fattorini Name Badge 100x65', 'String'),
 177         3 => array('Hanging Badge 3-3/4" x 4-3"/4', 'String'),
 178       );
 179
 180       foreach ($insertStatements as $values) {
 181         $query = 'INSERT INTO civicrm_option_value (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`) VALUES' . $values;
 182         CRM_Core_DAO::executeQuery($query, $queryParams);
 183       }
 184     }
 185
 186     // CRM-12578 - Prior to this version a CSS file under drupal would disable core css
 187     if (!empty($config->customCSSURL) && strpos($config->userFramework, 'Drupal') === 0) {
 188       // The new setting doesn't exist yet - need to create it first
 189       $sql = '
 190         INSERT INTO civicrm_setting (group_name, name , value , domain_id , is_domain , created_date)
 191         VALUES (%1, %2, %3, %4, %5, now())';
 192       CRM_Core_DAO::executeQuery($sql, array(
 193         1 => array(CRM_Core_BAO_Setting::SYSTEM_PREFERENCES_NAME, 'String'),
 194         2 => array('disable_core_css', 'String'),
 195         3 => array(serialize(1), 'String'),
 196         4 => array(CRM_Core_Config::domainID(), 'Positive'),
 197         5 => array(1, 'Int'),
 198       ));
 199       Civi::service('settings_manager')->flush();
 200     }
 201
 202     // CRM-13701 - Fix $config->timeInputFormat
 203     $sql = "
 204       SELECT time_format
 205       FROM   civicrm_preferences_date
 206       WHERE  time_format IS NOT NULL
 207       AND    time_format <> ''
 208       LIMIT  1
 209     ";
 210     $timeInputFormat = CRM_Core_DAO::singleValueQuery($sql);
 211     if ($timeInputFormat && $timeInputFormat != $config->timeInputFormat) {
 212       $params = array('timeInputFormat' => $timeInputFormat);
 213       CRM_Core_BAO_ConfigSetting::add($params);
 214     }
 215
 216     // CRM-13698 - add 'Available' and 'No-show' activity statuses
 217     $insertStatus = array();
 218     $nsinc = $avinc = $inc = 0;
 219     if (!CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'status_id', 'Available')) {
 220       $insertStatus[] = "(%1, 'Available', %2, 'Available',  NULL, 0, NULL, %3, 0, 0, 1, NULL, NULL)";
 221       $avinc = $inc = 1;
 222     }
 223     if (!CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'status_id', 'No_show')) {
 224       $insertStatus[] = "(%1, 'No-show', %4, 'No_show',  NULL, 0, NULL, %5, 0, 0, 1, NULL, NULL)";
 225       $nsinc = $inc + 1;
 226     }
 227     if (!empty($insertStatus)) {
 228       $acOptionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'activity_status', 'id', 'name');
 229       $maxVal = CRM_Core_DAO::singleValueQuery("SELECT MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = $acOptionGroupID");
 230       $maxWeight = CRM_Core_DAO::singleValueQuery("SELECT MAX(weight) FROM civicrm_option_value WHERE option_group_id = $acOptionGroupID");
 231
 232       $p[1] = array($acOptionGroupID, 'Integer');
 233       if ($avinc) {
 234         $p[2] = array($avinc + $maxVal, 'Integer');
 235         $p[3] = array($avinc + $maxWeight, 'Integer');
 236       }
 237       if ($nsinc) {
 238         $p[4] = array($nsinc + $maxVal, 'Integer');
 239         $p[5] = array($nsinc + $maxWeight, 'Integer');
 240       }
 241       $insertStatus = implode(',', $insertStatus);
 242
 243       $sql = "
 244 INSERT INTO
 245    civicrm_option_value (`option_group_id`, label, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
 246 VALUES {$insertStatus}";
 247       CRM_Core_DAO::executeQuery($sql, $p);
 248     }
 249
 250     $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.4.1')), 'runSql', $rev);
 251     $this->addTask('Patch word-replacement schema', 'wordReplacements_patch', $rev);
 252   }
 253
 254   /**
 255    * @param $rev
 256    *
 257    * @return bool
 258    */
 259   public function upgrade_4_4_4($rev) {
 260     $fkConstraint = array();
 261     if (!CRM_Core_DAO::checkFKConstraintInFormat('civicrm_activity_contact', 'activity_id')) {
 262       $fkConstraint[] = "ADD CONSTRAINT `FK_civicrm_activity_contact_activity_id` FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE";
 263     }
 264     if (!CRM_Core_DAO::checkFKConstraintInFormat('civicrm_activity_contact', 'contact_id')) {
 265       $fkConstraint[] = "ADD CONSTRAINT `FK_civicrm_activity_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
 266 ";
 267     }
 268
 269     if (!empty($fkConstraint)) {
 270       $fkConstraint = implode(',', $fkConstraint);
 271       $sql = "ALTER TABLE `civicrm_activity_contact`
 272 {$fkConstraint}
 273 ";
 274       // CRM-14036 : delete entries of un-mapped contacts
 275       CRM_Core_DAO::executeQuery("DELETE ac FROM civicrm_activity_contact ac
 276 LEFT JOIN civicrm_contact c
 277 ON c.id = ac.contact_id
 278 WHERE c.id IS NULL;
 279 ");
 280       // delete entries of un-mapped activities
 281       CRM_Core_DAO::executeQuery("DELETE ac FROM civicrm_activity_contact ac
 282 LEFT JOIN civicrm_activity a
 283 ON a.id = ac.activity_id
 284 WHERE a.id IS NULL;
 285 ");
 286
 287       CRM_Core_DAO::executeQuery("SET FOREIGN_KEY_CHECKS=0;");
 288       CRM_Core_DAO::executeQuery($sql);
 289       CRM_Core_DAO::executeQuery("SET FOREIGN_KEY_CHECKS=1;");
 290     }
 291
 292     // task to process sql
 293     $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.4.4')), 'runSql', $rev);
 294
 295     // CRM-13892 : add `name` column to dashboard schema
 296     $query = "
 297 ALTER TABLE civicrm_dashboard
 298     ADD name varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Internal name of dashlet.' AFTER domain_id ";
 299     CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
 300
 301     $dashboard = new CRM_Core_DAO_Dashboard();
 302     $dashboard->find();
 303     while ($dashboard->fetch()) {
 304       $urlElements = explode('/', $dashboard->url);
 305       if ($urlElements[1] == 'dashlet') {
 306         $url = explode('&', $urlElements[2]);
 307         $name = $url[0];
 308       }
 309       elseif ($urlElements[1] == 'report') {
 310         $url = explode('&', $urlElements[3]);
 311         $name = 'report/' . $url[0];
 312       }
 313       $values .= "
 314       WHEN {$dashboard->id} THEN '{$name}'
 315       ";
 316     }
 317
 318     $query = "
 319      UPDATE civicrm_dashboard
 320   SET name = CASE id
 321   {$values}
 322   END;
 323     ";
 324     CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
 325
 326     // CRM-13998 : missing alter statements for civicrm_report_instance
 327     $this->addTask(ts('Confirm civicrm_report_instance sql table for upgrades'), 'updateReportInstanceTable');
 328
 329     return TRUE;
 330   }
 331
 332   /**
 333    * @param $rev
 334    */
 335   public function upgrade_4_4_6($rev) {
 336     $sql = "SELECT count(*) AS count FROM INFORMATION_SCHEMA.STATISTICS where " .
 337       "TABLE_SCHEMA = database() AND INDEX_NAME = 'index_image_url' AND TABLE_NAME = 'civicrm_contact';";
 338     $dao = CRM_Core_DAO::executeQuery($sql);
 339     $dao->fetch();
 340     if ($dao->count < 1) {
 341       $sql = "CREATE INDEX index_image_url ON civicrm_contact (image_url);";
 342       $dao = CRM_Core_DAO::executeQuery($sql);
 343     }
 344     $minId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact WHERE image_URL IS NOT NULL');
 345     $maxId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact WHERE image_URL IS NOT NULL');
 346     for ($startId = $minId; $startId <= $maxId; $startId += self::BATCH_SIZE) {
 347       $endId = $startId + self::BATCH_SIZE - 1;
 348       $title = "Upgrade image_urls ($startId => $endId)";
 349       $this->addTask($title, 'upgradeImageUrls', $startId, $endId);
 350     }
 351   }
 352
 353   /**
 354    * Upgrade script for 4.4.7.
 355    *
 356    * @param string $rev
 357    * @param string $originalVer
 358    * @param string $latestVer
 359    */
 360   public function upgrade_4_4_7($rev, $originalVer, $latestVer) {
 361     // For WordPress/Joomla(?), cleanup broken image_URL from 4.4.6 upgrades - https://issues.civicrm.org/jira/browse/CRM-14971
 362     $exBackendUrl = CRM_Utils_System::url('civicrm/contact/imagefile', 'photo=XXX', TRUE); // URL formula from 4.4.6 upgrade
 363     $exFrontendUrl = CRM_Utils_System::url('civicrm/contact/imagefile', 'photo=XXX', TRUE, NULL, TRUE, TRUE);
 364     if ($originalVer == '4.4.6' && $exBackendUrl != $exFrontendUrl) {
 365       $minId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact WHERE image_URL IS NOT NULL');
 366       $maxId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact WHERE image_URL IS NOT NULL');
 367       for ($startId = $minId; $startId <= $maxId; $startId += self::BATCH_SIZE) {
 368         $endId = $startId + self::BATCH_SIZE - 1;
 369         $title = "Upgrade image_urls ($startId => $endId)";
 370         $this->addTask($title, 'cleanupBackendImageUrls', $startId, $endId);
 371       }
 372     }
 373     $this->addTask(ts('Update saved search information'), 'changeSavedSearch');
 374   }
 375
 376   /**
 377    * Upgrade image URLs.
 378    *
 379    * @param \CRM_Queue_TaskContext $ctx
 380    * @param $startId
 381    * @param $endId
 382    *
 383    * @return bool
 384    */
 385   public static function upgradeImageUrls(CRM_Queue_TaskContext $ctx, $startId, $endId) {
 386     $dao = self::findContactImageUrls($startId, $endId);
 387     $failures = array();
 388     $config = CRM_Core_Config::singleton();
 389     while ($dao->fetch()) {
 390       $imageURL = $dao->image_url;
 391       $baseurl = CIVICRM_UF_BASEURL;
 392       //CRM-15897 - gross hack for joomla to remove the administrator/
 393       if ($config->userFramework == 'Joomla') {
 394         $baseurl = str_replace("/administrator/", "/", $baseurl);
 395       }
 396       $baselen = strlen($baseurl);
 397       if (substr($imageURL, 0, $baselen) == $baseurl) {
 398         $photo = basename($dao->image_url);
 399         $fullpath = $config->customFileUploadDir . $photo;
 400         if (file_exists($fullpath)) {
 401           // For anyone who upgraded 4.4.6 release (eg 4.4.0=>4.4.6), the $newImageUrl incorrectly used backend URLs.
 402           // For anyone who skipped 4.4.6 (eg 4.4.0=>4.4.7), the $newImageUrl correctly uses frontend URLs
 403           self::setContactImageUrl($dao->id,
 404             CRM_Utils_System::url('civicrm/contact/imagefile', 'photo=' . $photo, TRUE, NULL, TRUE, TRUE));
 405         }
 406         else {
 407           $failures[$dao->id] = $dao->image_url;
 408         }
 409       }
 410       else {
 411         $failures[$dao->id] = $dao->image_url;
 412       }
 413     }
 414     CRM_Core_Error::debug_var('imageUrlsNotUpgraded', $failures);
 415     return TRUE;
 416   }
 417
 418   /**
 419    * Change saved search.
 420    *
 421    * @param \CRM_Queue_TaskContext $ctx
 422    *
 423    * @return bool
 424    */
 425   public static function changeSavedSearch(CRM_Queue_TaskContext $ctx) {
 426     $membershipStatuses = array_flip(CRM_Member_PseudoConstant::membershipStatus());
 427
 428     $dao = new CRM_Contact_DAO_SavedSearch();
 429     $dao->find();
 430     while ($dao->fetch()) {
 431       $formValues = NULL;
 432       if (!empty($dao->form_values)) {
 433         $formValues = unserialize($dao->form_values);
 434       }
 435       if (!empty($formValues['mapper'])) {
 436         foreach ($formValues['mapper'] as $key => $value) {
 437           foreach ($value as $k => $v) {
 438             if ($v[0] == 'Membership' && in_array($v[1], array('membership_status', 'membership_status_id'))) {
 439               $value = $formValues['value'][$key][$k];
 440               $op = $formValues['operator'][$key][$k];
 441               if ($op == 'IN') {
 442                 $value = trim($value);
 443                 $value = str_replace('(', '', $value);
 444                 $value = str_replace(')', '', $value);
 445
 446                 $v = explode(',', $value);
 447                 $value = array();
 448                 foreach ($v as $k1 => $v2) {
 449                   if (is_numeric($v2)) {
 450                     break 2;
 451                   }
 452                   $value[$k1] = $membershipStatuses[$v2];
 453                 }
 454                 $formValues['value'][$key][$k] = "(" . implode(',', $value) . ")";
 455               }
 456               elseif (in_array($op, array('=', '!='))) {
 457                 if (is_numeric($value)) {
 458                   break;
 459                 }
 460                 $formValues['value'][$key][$k] = $membershipStatuses[$value];
 461               }
 462             }
 463           }
 464         }
 465         $dao->form_values = serialize($formValues);
 466         $dao->save();
 467       }
 468     }
 469
 470     return TRUE;
 471   }
 472
 473   /**
 474    * For WordPress/Joomla(?) sites which upgraded to 4.4.6, find back-end image_URLs
 475    * (e.g. "http://example.com/wp-admin/admin.php?page=CiviCRM&amp;q=civicrm/contact/imagefile&amp;photo=123.jpg")
 476    * and convert them to front-end URLs
 477    * (e.g. "http://example.com/?page=CiviCRM&amp;q=civicrm/contact/imagefile&amp;photo=123.jpg").
 478    *
 479    * @param CRM_Queue_TaskContext $ctx
 480    * @param int $startId
 481    * @param int $endId
 482    * @return bool
 483    */
 484   public static function cleanupBackendImageUrls(CRM_Queue_TaskContext $ctx, $startId, $endId) {
 485     $dao = self::findContactImageUrls($startId, $endId);
 486     while ($dao->fetch()) {
 487       $imageUrl = str_replace('&amp;', '&', $dao->image_url);
 488       if (preg_match(":civicrm/contact/imagefile.*photo=:", $imageUrl)) {
 489         // looks like one of ours
 490         $imageUrlParts = parse_url($imageUrl);
 491         parse_str($imageUrlParts['query'], $imageUrlQuery);
 492         self::setContactImageUrl($dao->id,
 493           CRM_Utils_System::url('civicrm/contact/imagefile', 'photo=' . $imageUrlQuery['photo'], TRUE, NULL, TRUE, TRUE));
 494       }
 495     }
 496     return TRUE;
 497   }
 498
 499   /**
 500    * @param int $startId
 501    * @param int $endId
 502    * @return CRM_Core_DAO
 503    *   columns include "id" and "image_URL"
 504    */
 505   public static function findContactImageUrls($startId, $endId) {
 506     $sql = "
 507 SELECT id, image_url
 508 FROM civicrm_contact
 509 WHERE 1
 510 AND id BETWEEN %1 AND %2
 511 AND image_URL IS NOT NULL
 512 ";
 513
 514     $params = array(
 515       1 => array($startId, 'Integer'),
 516       2 => array($endId, 'Integer'),
 517     );
 518     $dao = CRM_Core_DAO::executeQuery($sql, $params, TRUE, NULL, FALSE, FALSE);
 519     return $dao;
 520   }
 521
 522   /**
 523    * @param int $cid
 524    * @param string $newImageUrl
 525    */
 526   public static function setContactImageUrl($cid, $newImageUrl) {
 527     $sql = 'UPDATE civicrm_contact SET image_url=%1 WHERE id=%2';
 528     $params = array(
 529       1 => array($newImageUrl, 'String'),
 530       2 => array($cid, 'Integer'),
 531     );
 532     $updatedao = CRM_Core_DAO::executeQuery($sql, $params);
 533   }
 534
 535   /**
 536    * Update activity contacts CRM-12274
 537    *
 538    * @param CRM_Queue_TaskContext $ctx
 539    *
 540    * @return bool
 541    *   TRUE for success
 542    */
 543   public static function activityContacts(CRM_Queue_TaskContext $ctx) {
 544     $upgrade = new CRM_Upgrade_Form();
 545
 546     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 547     $ovValue[] = $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 548     $ovValue[] = $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 549     $ovValue[] = $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 550
 551     $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'activity_contacts', 'id', 'name');
 552     if (!empty($ovValue)) {
 553       $ovValues = implode(', ', $ovValue);
 554       $query = "
 555 UPDATE civicrm_option_value
 556 SET    is_reserved = 1
 557 WHERE  option_group_id = {$optionGroupID} AND value IN ($ovValues)";
 558
 559       $dao = CRM_Core_DAO::executeQuery($query);
 560     }
 561
 562     if (!$assigneeID) {
 563       $assigneeID = 1;
 564       $value[] = "({$optionGroupID}, 'Activity Assignees', 1, 'Activity Assignees', 1, 1, 1)";
 565     }
 566     if (!$sourceID) {
 567       $sourceID = 2;
 568       $value[] = "({$optionGroupID}, 'Activity Source', 2, 'Activity Source', 2, 1, 1)";
 569     }
 570     if (!$targetID) {
 571       $targetID = 3;
 572       $value[] = "({$optionGroupID}, 'Activity Targets', 3, 'Activity Targets', 3, 1, 1)";
 573     }
 574
 575     if (!$assigneeID || !$sourceID || !$targetID) {
 576       $insert = "
 577 INSERT INTO civicrm_option_value
 578 (option_group_id, label, value, name, weight, is_reserved, is_active)
 579 VALUES
 580
 581 ";
 582       $values = implode(', ', $value);
 583       $query = $insert . $values;
 584       $dao = CRM_Core_DAO::executeQuery($query);
 585     }
 586
 587     // sometimes an user does not make a clean backup and the above table
 588     // already exists, so lets delete this table - CRM-13665
 589     $query = "DROP TABLE IF EXISTS civicrm_activity_contact";
 590     $dao = CRM_Core_DAO::executeQuery($query);
 591
 592     $query = "
 593 CREATE TABLE IF NOT EXISTS civicrm_activity_contact (
 594   id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Activity contact id',
 595   activity_id int(10) unsigned NOT NULL COMMENT 'Foreign key to the activity for this record.',
 596   contact_id int(10) unsigned NOT NULL COMMENT 'Foreign key to the contact for this record.',
 597   record_type_id int(10) unsigned DEFAULT NULL COMMENT 'The record type id for this row',
 598   PRIMARY KEY (id),
 599   UNIQUE KEY UI_activity_contact (contact_id,activity_id,record_type_id),
 600   KEY FK_civicrm_activity_contact_activity_id (activity_id)
 601 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 602 ";
 603
 604     $dao = CRM_Core_DAO::executeQuery($query);
 605
 606     $query = "
 607 INSERT INTO civicrm_activity_contact (activity_id, contact_id, record_type_id)
 608 SELECT      activity_id, target_contact_id, {$targetID} as record_type_id
 609 FROM        civicrm_activity_target";
 610
 611     $dao = CRM_Core_DAO::executeQuery($query);
 612
 613     $query = "
 614 INSERT INTO civicrm_activity_contact (activity_id, contact_id, record_type_id)
 615 SELECT      activity_id, assignee_contact_id, {$assigneeID} as record_type_id
 616 FROM        civicrm_activity_assignment";
 617     $dao = CRM_Core_DAO::executeQuery($query);
 618
 619     $query = "
 620   INSERT INTO civicrm_activity_contact (activity_id, contact_id, record_type_id)
 621 SELECT      id, source_contact_id, {$sourceID} as record_type_id
 622 FROM        civicrm_activity
 623 WHERE       source_contact_id IS NOT NULL";
 624
 625     $dao = CRM_Core_DAO::executeQuery($query);
 626
 627     $query = "DROP TABLE civicrm_activity_target";
 628     $dao = CRM_Core_DAO::executeQuery($query);
 629
 630     $query = "DROP TABLE civicrm_activity_assignment";
 631     $dao = CRM_Core_DAO::executeQuery($query);
 632
 633     $query = "ALTER  TABLE civicrm_activity
 634      DROP FOREIGN KEY FK_civicrm_activity_source_contact_id";
 635
 636     $dao = CRM_Core_DAO::executeQuery($query);
 637
 638     $query = "ALTER  TABLE civicrm_activity DROP COLUMN source_contact_id";
 639     $dao = CRM_Core_DAO::executeQuery($query);
 640
 641     return TRUE;
 642   }
 643
 644   /**
 645    * Migrate word-replacements from $config to civicrm_word_replacement
 646    *
 647    * @param CRM_Queue_TaskContext $ctx
 648    *
 649    * @return bool
 650    *   TRUE for success
 651    * @see http://issues.civicrm.org/jira/browse/CRM-13187
 652    */
 653   public static function wordReplacements(CRM_Queue_TaskContext $ctx) {
 654     $query = "
 655 CREATE TABLE IF NOT EXISTS `civicrm_word_replacement` (
 656      `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Word replacement ID',
 657      `find_word` varchar(255) COLLATE utf8_bin    COMMENT 'Word which need to be replaced',
 658      `replace_word` varchar(255) COLLATE utf8_bin    COMMENT 'Word which will replace the word in find',
 659      `is_active` tinyint    COMMENT 'Is this entry active?',
 660      `match_type` enum('wildcardMatch', 'exactMatch')   DEFAULT 'wildcardMatch',
 661      `domain_id` int unsigned    COMMENT 'FK to Domain ID. This is for Domain specific word replacement',
 662     PRIMARY KEY ( `id` ),
 663     UNIQUE INDEX `UI_domain_find` (domain_id, find_word),
 664     CONSTRAINT FK_civicrm_word_replacement_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
 665 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 666     ";
 667     $dao = CRM_Core_DAO::executeQuery($query);
 668
 669     self::rebuildWordReplacementTable();
 670     return TRUE;
 671   }
 672
 673   /**
 674    * Fix misconfigured constraints created in 4.4.0. To distinguish the good
 675    * and bad configurations, we change the constraint name from "UI_find"
 676    * (the original name in 4.4.0) to "UI_domain_find" (the new name in
 677    * 4.4.1).
 678    *
 679    * @param CRM_Queue_TaskContext $ctx
 680    * @param $rev
 681    *
 682    * @return bool
 683    *   TRUE for success
 684    * @see http://issues.civicrm.org/jira/browse/CRM-13655
 685    */
 686   public static function wordReplacements_patch(CRM_Queue_TaskContext $ctx, $rev) {
 687     if (CRM_Core_DAO::checkConstraintExists('civicrm_word_replacement', 'UI_find')) {
 688       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement DROP FOREIGN KEY FK_civicrm_word_replacement_domain_id;");
 689       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement DROP KEY FK_civicrm_word_replacement_domain_id;");
 690       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement DROP KEY UI_find;");
 691       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement MODIFY COLUMN `find_word` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Word which need to be replaced';");
 692       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement MODIFY COLUMN `replace_word` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Word which will replace the word in find';");
 693       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement ADD CONSTRAINT UI_domain_find UNIQUE KEY `UI_domain_find` (`domain_id`,`find_word`);");
 694       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement ADD CONSTRAINT FK_civicrm_word_replacement_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`);");
 695     }
 696     return TRUE;
 697   }
 698
 699   /**
 700    * Get all the word-replacements stored in config-arrays
 701    * and convert them to params for the WordReplacement.create API.
 702    *
 703    * Note: This function is duplicated in CRM_Core_BAO_WordReplacement and
 704    * CRM_Upgrade_Incremental_php_FourFour to ensure that the incremental upgrade
 705    * step behaves consistently even as the BAO evolves in future versions.
 706    * However, if there's a bug in here prior to 4.4.0, we should apply the
 707    * bugfix in both places.
 708    *
 709    * @param bool $rebuildEach
 710    *   Whether to perform rebuild after each individual API call.
 711    * @return array
 712    *   Each item is $params for WordReplacement.create
 713    * @see CRM_Core_BAO_WordReplacement::convertConfigArraysToAPIParams
 714    */
 715   public static function getConfigArraysAsAPIParams($rebuildEach) {
 716     $wordReplacementCreateParams = array();
 717     // get all domains
 718     $result = civicrm_api3('domain', 'get', array(
 719       'return' => array('locale_custom_strings'),
 720     ));
 721     if (!empty($result["values"])) {
 722       foreach ($result["values"] as $value) {
 723         $params = array();
 724         $params["domain_id"] = $value["id"];
 725         $params["options"] = array('wp-rebuild' => $rebuildEach);
 726         // unserialize word match string
 727         $localeCustomArray = array();
 728         if (!empty($value["locale_custom_strings"])) {
 729           $localeCustomArray = unserialize($value["locale_custom_strings"]);
 730         }
 731         if (!empty($localeCustomArray)) {
 732           $wordMatchArray = array();
 733           // Traverse Language array
 734           foreach ($localeCustomArray as $localCustomData) {
 735             // Traverse status array "enabled" "disabled"
 736             foreach ($localCustomData as $status => $matchTypes) {
 737               $params["is_active"] = ($status == "enabled") ? TRUE : FALSE;
 738               // Traverse Match Type array "wildcardMatch" "exactMatch"
 739               foreach ($matchTypes as $matchType => $words) {
 740                 $params["match_type"] = $matchType;
 741                 foreach ($words as $word => $replace) {
 742                   $params["find_word"] = $word;
 743                   $params["replace_word"] = $replace;
 744                   $wordReplacementCreateParams[] = $params;
 745                 }
 746               }
 747             }
 748           }
 749         }
 750       }
 751     }
 752     return $wordReplacementCreateParams;
 753   }
 754
 755   /**
 756    * Get all the word-replacements stored in config-arrays
 757    * and write them out as records in civicrm_word_replacement.
 758    *
 759    * Note: This function is duplicated in CRM_Core_BAO_WordReplacement and
 760    * CRM_Upgrade_Incremental_php_FourFour to ensure that the incremental upgrade
 761    * step behaves consistently even as the BAO evolves in future versions.
 762    * However, if there's a bug in here prior to 4.4.0, we should apply the
 763    * bugfix in both places.
 764    */
 765   public static function rebuildWordReplacementTable() {
 766     civicrm_api3('word_replacement', 'replace', array(
 767       'options' => array('match' => array('domain_id', 'find_word')),
 768       'values' => array_filter(self::getConfigArraysAsAPIParams(FALSE), array(__CLASS__, 'isValidWordReplacement')),
 769     ));
 770     CRM_Core_BAO_WordReplacement::rebuild();
 771   }
 772
 773
 774   /**
 775    * CRM-13998 missing alter statements for civicrm_report_instance
 776    */
 777   public function updateReportInstanceTable() {
 778
 779     // add civicrm_report_instance.name
 780
 781     $sql = "SELECT count(*) FROM information_schema.columns "
 782       . "WHERE table_schema = database() AND table_name = 'civicrm_report_instance' AND COLUMN_NAME = 'name' ";
 783
 784     $res = CRM_Core_DAO::singleValueQuery($sql);
 785
 786     if ($res <= 0) {
 787       $sql = "ALTER TABLE civicrm_report_instance ADD `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'when combined with report_id/template uniquely identifies the instance'";
 788       $res = CRM_Core_DAO::executeQuery($sql);
 789     }
 790
 791     // add civicrm_report_instance args
 792
 793     $sql = "SELECT count(*) FROM information_schema.columns WHERE table_schema = database() AND table_name = 'civicrm_report_instance' AND COLUMN_NAME = 'args' ";
 794
 795     $res = CRM_Core_DAO::singleValueQuery($sql);
 796
 797     if ($res <= 0) {
 798       $sql = "ALTER TABLE civicrm_report_instance ADD `args` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'arguments that are passed in the url when invoking the instance'";
 799
 800       $res = CRM_Core_DAO::executeQuery($sql);
 801     }
 802
 803     return TRUE;
 804   }
 805
 806   /**
 807    * @param array $params
 808    * @return bool
 809    *   TRUE if $params is valid
 810    */
 811   public static function isValidWordReplacement($params) {
 812     $result = strlen($params['find_word']) <= self::MAX_WORD_REPLACEMENT_SIZE && strlen($params['replace_word']) <= self::MAX_WORD_REPLACEMENT_SIZE;
 813     if (!$result) {
 814       CRM_Core_Error::debug_var('invalidWordReplacement', $params);
 815     }
 816     return $result;
 817   }
 818
 819 }