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