CRM/Upgrade/Incremental/php/FourFour.php

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