CRM/Upgrade/Incremental/php/FourFour.php

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