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