CRM/Upgrade/Incremental/php/FourThree.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.3                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007.                                       |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License along with this program; if not, contact CiviCRM LLC       |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Upgrade_Incremental_php_FourThree {
  36   const BATCH_SIZE = 5000;
  37
  38   function verifyPreDBstate(&$errors) {
  39     return TRUE;
  40   }
  41
  42   /**
  43    * Compute any messages which should be displayed beforeupgrade
  44    *
  45    * Note: This function is called iteratively for each upcoming
  46    * revision to the database.
  47    *
  48    * @param $postUpgradeMessage string, alterable
  49    * @param $rev string, a version number, e.g. '4.3.alpha1', '4.3.beta3', '4.3.0'
  50    * @return void
  51    */
  52   function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
  53     if ($rev == '4.3.beta3') {
  54       //CRM-12084
  55       //sql for checking orphaned contribution records
  56       $sql = "SELECT COUNT(ct.id) FROM civicrm_contribution ct LEFT JOIN civicrm_contact c ON ct.contact_id = c.id WHERE c.id IS NULL";
  57       $count = CRM_Core_DAO::singleValueQuery($sql, array(), TRUE, FALSE);
  58
  59       if ($count > 0) {
  60         $error = ts("There is a data integrity issue with this CiviCRM database. It contains %1 contribution records which are linked to contact records that have been deleted. You will need to correct this manually before you can run the upgrade. Use the following MySQL query to identify the problem records: %2 These records will need to be deleted or linked to an existing contact record.", array(1 => $count, 2 => '<em>SELECT ct.* FROM civicrm_contribution ct LEFT JOIN civicrm_contact c ON ct.contact_id = c.id WHERE c.id IS NULL;</em>'));
  61         CRM_Core_Error::fatal($error);
  62         return FALSE;
  63       }
  64     }
  65     if ($rev == '4.3.beta4' && CRM_Utils_Constant::value('CIVICRM_UF', FALSE) == 'Drupal6') {
  66       // CRM-11823 - Make sure the D6 HTML HEAD technique will work on upgrade pages
  67       theme('item_list', array()); // force-load theme registry
  68       $theme_registry = theme_get_registry();
  69       if (
  70         !isset($theme_registry['page']['preprocess functions']) ||
  71         FALSE === array_search('civicrm_preprocess_page_inject', $theme_registry['page']['preprocess functions'])
  72       ) {
  73         CRM_Core_Error::fatal('Please reset the Drupal cache (Administer => Site Configuration => Performance => Clear cached data))');
  74       }
  75     }
  76   }
  77
  78   /**
  79    * Compute any messages which should be displayed after upgrade
  80    *
  81    * @param $postUpgradeMessage string, alterable
  82    * @param $rev string, an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs
  83    * @return void
  84    */
  85   function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
  86     if ($rev == '4.3.alpha1') {
  87       // check if CiviMember component is enabled
  88       $config = CRM_Core_Config::singleton();
  89       if (in_array('CiviMember', $config->enableComponents)) {
  90         $postUpgradeMessage .= '<br />' . ts('Membership renewal reminders must now be configured using the Schedule Reminders feature, which supports multiple renewal reminders  (Administer > Communications > Schedule Reminders). The Update Membership Statuses scheduled job will no longer send membershp renewal reminders. You can use your existing renewal reminder message template(s) with the Schedule Reminders feature.');
  91         $postUpgradeMessage .= '<br />' . ts('The Set Membership Reminder Dates scheduled job has been deleted since membership reminder dates stored in the membership table are no longer in use.');
  92       }
  93
  94       //CRM-11636
  95       //here we do the financial type check and migration
  96       $isDefaultsModified = self::_checkAndMigrateDefaultFinancialTypes();
  97       if($isDefaultsModified) {
  98         $postUpgradeMessage .= '<br />' . ts('Please review all price set financial type assignments.');
  99       }
 100       list($context, $orgName) = self::createDomainContacts();
 101       if ($context == 'added') {
 102         $postUpgradeMessage .= '<br />' . ts("A new organization contact has been added as the default domain contact using the information from your Organization Address and Contact Info settings: '%1'.", array(1 => $orgName));
 103       }
 104       elseif ($context == 'merged') {
 105         $postUpgradeMessage .= '<br />' . ts("The existing organization contact record for '%1' has been marked as the default domain contact, and has been updated with information from your Organization Address and Contact Info settings.", array(1 => $orgName));
 106       }
 107
 108       $providerExists = CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_sms_provider LIMIT 1");
 109       if ($providerExists) {
 110         $postUpgradeMessage .= '<br />' . ts('SMS providers were found to setup. Please note Clickatell / Twilio are now shipped as extensions and will require installing them to continue working. Extension could be downloaded and installed from <a href="%1">github</a>.', array(1 => 'https://github.com/civicrm/civicrm-core/tree/master/tools/extensions'));
 111       }
 112     }
 113
 114     if ($rev == '4.3.alpha2') {
 115       $sql = "
 116 SELECT   title, id
 117 FROM     civicrm_action_schedule
 118 WHERE    entity_value = '' OR entity_value IS NULL
 119 ";
 120
 121       $dao = CRM_Core_DAO::executeQuery($sql);
 122       $reminder = array();
 123       $list = '';
 124       while ($dao->fetch()) {
 125           $reminder[$dao->id] = $dao->title;
 126           $list .= "<li>{$dao->title}</li>";
 127       }
 128       if (!empty($reminder)) {
 129         $list = "<br /><ul>" . $list . "</ul>";
 130         $postUpgradeMessage .=  '<br />' .ts("Scheduled Reminders must be linked to one or more 'entities' (Events, Event Templates, Activity Types, Membership Types). The following reminders are not configured properly and will not be run. Please review them and update or delete them: %1", array(1 => $list));
 131       }
 132     }
 133     if ($rev == '4.3.beta2') {
 134       $postUpgradeMessage .= '<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><li>Events - Registration Confirmation and Receipt (off-line)</li><li>Pledges - Acknowledgement</li><li>Pledges - Payment Reminder</li><li>Contributions - Receipt (off-line)</li><li>Contributions - Receipt (on-line)</li><li>Memberships - Signup and Renewal Receipts (off-line)</li><li>Memberships - Receipt (on-line)</li><li>Personal Campaign Pages - Admin Notification</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).');
 135     }
 136
 137     if ($rev == '4.3.beta5') {
 138       $postUpgradeMessage .= '<br />' . ts("If you are interested in trying out the new Accounting Integration features, please review user permissions and assign the new 'manual batch' permissions as appropriate.");
 139
 140       // CRM-12155
 141       $query = "
 142 SELECT    ceft.id FROM `civicrm_financial_trxn` cft
 143 LEFT JOIN civicrm_entity_financial_trxn ceft
 144   ON ceft.financial_trxn_id = cft.id AND ceft.entity_table = 'civicrm_contribution'
 145 LEFT JOIN civicrm_contribution cc
 146   ON cc.id = ceft.entity_id AND ceft.entity_table = 'civicrm_contribution'
 147 WHERE cc.id IS NULL
 148 ";
 149
 150       $dao = CRM_Core_DAO::executeQuery($query);
 151       $isOrphanData = TRUE;
 152       if (!$dao->N) {
 153         $query = "
 154 SELECT    cli.id FROM civicrm_line_item cli
 155 LEFT JOIN civicrm_contribution cc ON cli.entity_id = cc.id AND cli.entity_table = 'civicrm_contribution'
 156 LEFT JOIN civicrm_participant cp ON cli.entity_id = cp.id AND cli.entity_table = 'civicrm_participant'
 157 WHERE CASE WHEN cli.entity_table = 'civicrm_contribution'
 158   THEN cc.id IS NULL
 159   ELSE  cp.id IS NULL
 160 END
 161 ";
 162         $dao = CRM_Core_DAO::executeQuery($query);
 163         if (!$dao->N) {
 164           $revPattern = '/^((\d{1,2})\.\d{1,2})\.(\d{1,2}|\w{4,7})?$/i';
 165           preg_match($revPattern, $currentVer, $version);
 166           if ($version[1] >= 4.3) {
 167             $query = "
 168 SELECT    cfi.id
 169 FROM      civicrm_financial_item cfi
 170 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_table = 'civicrm_financial_item' and cfi.id = ceft.entity_id
 171 WHERE     ceft.entity_id IS NULL;
 172 ";
 173             $dao = CRM_Core_DAO::executeQuery($query);
 174             if (!$dao->N) {
 175               $isOrphanData = FALSE;
 176             }
 177           }
 178           else {
 179             $isOrphanData = FALSE;
 180           }
 181         }
 182       }
 183
 184       if ($isOrphanData) {
 185         $postUpgradeMessage .= "</br> <strong>" . ts('Your database contains extraneous financial records related to deleted contacts and contributions. These records should not affect the site and will not appear in reports, search results or exports. However you may wish to clean them up. Refer to <a href="%1" target="_blank">this wiki page for details</a>.
 186         ', array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Clean+up+extraneous+financial+data+-+4.3+upgrades')) . "</strong>";
 187       }
 188     }
 189     if ($rev == '4.3.4') {
 190       $postUpgradeMessage .= '<br />' . ts('System Administrator Alert: If you are running scheduled jobs using CLI.php, you will need to reconfigure cron tasks to include a password. Scheduled jobs will no longer run if the password is not provided (<a href="%1" target="_blank">learn more</a>).',
 191       array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Managing+Scheduled+Jobs'));
 192     }
 193     if ($rev == '4.3.5') {
 194       $postUpgradeMessage .= '<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><li>Events - Registration Confirmation and Receipt (off-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).');
 195     }
 196
 197   }
 198
 199   function upgrade_4_3_alpha1($rev) {
 200     self::task_4_3_alpha1_checkDBConstraints();
 201
 202     // add indexes for civicrm_entity_financial_trxn
 203     // CRM-12141
 204     $this->addTask(ts('Check/Add indexes for civicrm_entity_financial_trxn'), 'task_4_3_x_checkIndexes', $rev);
 205     // task to process sql
 206     $this->addTask(ts('Upgrade DB to 4.3.alpha1: SQL'), 'task_4_3_x_runSql', $rev);
 207
 208     //CRM-11636
 209     $this->addTask(ts('Populate financial type values for price records'), 'assignFinancialTypeToPriceRecords');
 210     //CRM-11514 create financial records for contributions
 211     $this->addTask(ts('Create financial records for contributions'), 'createFinancialRecords');
 212
 213     $minId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
 214     $maxId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
 215     for ($startId = $minId; $startId <= $maxId; $startId += self::BATCH_SIZE) {
 216       $endId = $startId + self::BATCH_SIZE - 1;
 217       $title = ts('Upgrade timestamps (%1 => %2)', array(1 => $startId, 2 => $endId));
 218       $this->addTask($title, 'convertTimestamps', $startId, $endId);
 219     }
 220
 221     // CRM-10893
 222     // fix WP access control
 223     $config = CRM_Core_Config::singleton( );
 224     if ($config->userFramework == 'WordPress') {
 225       civicrm_wp_set_capabilities( );
 226     }
 227
 228     // Update phones CRM-11292.
 229     $this->addTask(ts('Upgrade Phone Numbers'), 'phoneNumeric');
 230
 231     return TRUE;
 232   }
 233
 234   function upgrade_4_3_alpha2($rev) {
 235     //CRM-11847
 236     $isColumnPresent = CRM_Core_DAO::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
 237     if ($isColumnPresent) {
 238       CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
 239     }
 240     $this->addTask(ts('Upgrade DB to 4.3.alpha2: SQL'), 'task_4_3_x_runSql', $rev);
 241   }
 242
 243   function upgrade_4_3_alpha3($rev) {
 244     $this->addTask(ts('Upgrade DB to 4.3.alpha3: SQL'), 'task_4_3_x_runSql', $rev);
 245   }
 246
 247   function upgrade_4_3_beta2($rev) {
 248     $this->addTask(ts('Upgrade DB to 4.3.beta2: SQL'), 'task_4_3_x_runSql', $rev);
 249
 250     // CRM-12002
 251     if (
 252       CRM_Core_DAO::checkTableExists('log_civicrm_line_item') &&
 253       CRM_Core_DAO::checkFieldExists('log_civicrm_line_item', 'label')
 254     ) {
 255       CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
 256     }
 257   }
 258
 259   function upgrade_4_3_beta3($rev) {
 260     $this->addTask(ts('Upgrade DB to 4.3.beta3: SQL'), 'task_4_3_x_runSql', $rev);
 261     // CRM-12065
 262     $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
 263     $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
 264     $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
 265     $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
 266   }
 267
 268   function upgrade_4_3_beta4($rev) {
 269     $this->addTask(ts('Upgrade DB to 4.3.beta4: SQL'), 'task_4_3_x_runSql', $rev);
 270     // add indexes for civicrm_entity_financial_trxn
 271     // CRM-12141
 272     $this->addTask(ts('Check/Add indexes for civicrm_entity_financial_trxn'), 'task_4_3_x_checkIndexes', $rev);
 273   }
 274
 275   function upgrade_4_3_beta5($rev) {
 276     // CRM-12205
 277     if (
 278       CRM_Core_DAO::checkTableExists('log_civicrm_financial_trxn') &&
 279       CRM_Core_DAO::checkFieldExists('log_civicrm_financial_trxn', 'trxn_id')
 280     ) {
 281       CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` VARCHAR(255) NULL DEFAULT NULL');
 282     }
 283     // CRM-12142 - some sites didn't get this column added yet, and sites which installed 4.3 from scratch will already have it
 284     // CRM-12367 - add this column to single lingual sites only
 285     $upgrade = new CRM_Upgrade_Form();
 286     if (!$upgrade->multilingual &&
 287       !CRM_Core_DAO::checkFieldExists('civicrm_premiums', 'premiums_nothankyou_label')
 288     ) {
 289       $query = "
 290 ALTER TABLE civicrm_premiums
 291 ADD COLUMN   premiums_nothankyou_label varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
 292   COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)'
 293 ";
 294       CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
 295     }
 296     $this->addTask(ts('Upgrade DB to 4.3.beta5: SQL'), 'task_4_3_x_runSql', $rev);
 297   }
 298
 299   function upgrade_4_3_4($rev) {
 300     $this->addTask(ts('Upgrade DB to 4.3.4: SQL'), 'task_4_3_x_runSql', $rev);
 301   }
 302
 303   //CRM-11636
 304   function assignFinancialTypeToPriceRecords() {
 305     $upgrade = new CRM_Upgrade_Form();
 306     //here we update price set entries
 307     $sqlFinancialIds = "
 308 SELECT id, name
 309 FROM   civicrm_financial_type
 310 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');
 311 ";
 312     $daoFinancialIds = CRM_Core_DAO::executeQuery($sqlFinancialIds);
 313     while($daoFinancialIds->fetch()) {
 314       $financialIds[$daoFinancialIds->name] = $daoFinancialIds->id;
 315     }
 316     $sqlPriceSetUpdate = "
 317 UPDATE civicrm_price_set ps
 318 SET    ps.financial_type_id =
 319   CASE
 320     WHEN ps.extends LIKE '%1%' THEN {$financialIds['Event Fee']}
 321     WHEN ps.extends LIKE '2' THEN {$financialIds['Donation']}
 322     WHEN ps.extends LIKE '3' THEN {$financialIds['Member Dues']}
 323   END
 324 WHERE  financial_type_id IS NULL
 325 ";
 326     CRM_Core_DAO::executeQuery($sqlPriceSetUpdate);
 327
 328     //here we update price field value rows
 329     $sqlPriceFieldValueUpdate = "
 330 UPDATE     civicrm_price_field_value pfv
 331 LEFT JOIN  civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
 332 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
 333 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
 334  SET pfv.financial_type_id =
 335    CASE
 336      WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
 337      WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
 338    END
 339 ";
 340     CRM_Core_DAO::executeQuery($sqlPriceFieldValueUpdate);
 341
 342     return TRUE;
 343   }
 344
 345   static function _checkAndMigrateDefaultFinancialTypes() {
 346     $modifiedDefaults = FALSE;
 347     //insert types if not exists
 348     $sqlFetchTypes = "
 349 SELECT id, name
 350 FROM   civicrm_contribution_type
 351 WHERE  name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1
 352 ";
 353     $daoFetchTypes = CRM_Core_DAO::executeQuery($sqlFetchTypes);
 354
 355     if ($daoFetchTypes->N < 3) {
 356       $modifiedDefaults = TRUE;
 357       $insertStatments = array (
 358         'Donation' => "('Donation', 0, 1, 1)",
 359         'Member' => "('Member Dues', 0, 1, 1)",
 360         'Event Fee' => "('Event Fee', 0, 1, 0)",
 361       );
 362       foreach ($insertStatments as $values) {
 363         $query = "
 364 INSERT INTO  civicrm_contribution_type  (name, is_reserved, is_active, is_deductible)
 365 VALUES       $values
 366 ON DUPLICATE KEY UPDATE  is_active = 1
 367 ";
 368         CRM_Core_DAO::executeQuery($query);
 369       }
 370     }
 371     return $modifiedDefaults;
 372   }
 373
 374   function createFinancialRecords() {
 375     $upgrade = new CRM_Upgrade_Form();
 376
 377     // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
 378     $query = "
 379 UPDATE    civicrm_entity_financial_trxn ceft
 380 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
 381 SET       ceft.amount = total_amount
 382 WHERE     cft.net_amount IS NOT NULL
 383 AND       ceft.entity_table = 'civicrm_contribution'
 384 ";
 385     CRM_Core_DAO::executeQuery($query);
 386
 387     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 388     $completedStatus = array_search('Completed', $contributionStatus);
 389     $pendingStatus = array_search('Pending', $contributionStatus);
 390     $cancelledStatus = array_search('Cancelled', $contributionStatus);
 391     $queryParams = array(
 392       1 => array($completedStatus, 'Integer'),
 393       2 => array($pendingStatus, 'Integer'),
 394       3 => array($cancelledStatus, 'Integer')
 395     );
 396
 397     $accountType = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
 398     $query = "
 399 SELECT id
 400 FROM   civicrm_financial_account
 401 WHERE  is_default = 1
 402 AND    financial_account_type_id = {$accountType}
 403 ";
 404     $financialAccountId = CRM_Core_DAO::singleValueQuery($query);
 405
 406     $accountRelationsips = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL);
 407
 408     $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
 409     $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
 410     $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
 411     $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
 412
 413     $financialItemStatus = CRM_Core_PseudoConstant::accountOptionValues('financial_item_status');
 414     $unpaidStatus = array_search('Unpaid', $financialItemStatus);
 415     $paidStatus = array_search('Paid', $financialItemStatus);
 416
 417     $validCurrencyCodes = CRM_Core_PseudoConstant::currencyCode();
 418     $validCurrencyCodes = implode("','", $validCurrencyCodes);
 419     $config = CRM_Core_Config::singleton();
 420     $defaultCurrency = $config->defaultCurrency;
 421     $now = date( 'YmdHis' );
 422
 423     //adding financial_trxn records and entity_financial_trxn records related to contribution
 424     //Add temp column for easy entry in entity_financial_trxn
 425     $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
 426     CRM_Core_DAO::executeQuery($sql);
 427
 428     //pending pay later status handling
 429     $sql = "
 430 INSERT INTO civicrm_financial_trxn
 431        (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
 432        check_number, to_financial_account_id, from_financial_account_id, trxn_date)
 433 SELECT con.id as contribution_id, con.payment_instrument_id,
 434        IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
 435        con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
 436        con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
 437         REPLACE(REPLACE(REPLACE(
 438           CASE
 439             WHEN con.receive_date IS NOT NULL THEN
 440                 con.receive_date
 441             WHEN con.receipt_date IS NOT NULL THEN
 442                 con.receipt_date
 443             ELSE
 444                 {$now}
 445           END
 446         , '-', ''), ':', ''), ' ', '') as trxn_date
 447 FROM  civicrm_contribution con
 448       LEFT JOIN civicrm_entity_financial_account efa
 449              ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
 450                  AND efa.account_relationship = {$accountsReceivableAccount})
 451 WHERE con.is_pay_later = 1
 452 AND   con.contribution_status_id = {$pendingStatus}
 453 ";
 454     CRM_Core_DAO::executeQuery($sql);
 455
 456     //create a temp table to hold financial account id related to payment instruments
 457     $tempTableName1 = CRM_Core_DAO::createTempTableName();
 458
 459     $sql =  "
 460 CREATE TEMPORARY TABLE {$tempTableName1}
 461 SELECT     ceft.financial_account_id financial_account_id, cov.value as instrument_id
 462 FROM       civicrm_entity_financial_account ceft
 463 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
 464 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
 465 WHERE      cog.name = 'payment_instrument'
 466 ";
 467     CRM_Core_DAO::executeQuery($sql);
 468
 469     //CRM-12141
 470     $sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id(200));";
 471     CRM_Core_DAO::executeQuery($sql);
 472
 473     //create temp table to process completed / cancelled contribution
 474     $tempTableName2 = CRM_Core_DAO::createTempTableName();
 475     $sql = "
 476 CREATE TEMPORARY TABLE {$tempTableName2}
 477 SELECT con.id as contribution_id, con.payment_instrument_id,
 478        IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
 479        con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
 480        con.check_number, NULL as from_financial_account_id,
 481        REPLACE(REPLACE(REPLACE(
 482           CASE
 483             WHEN con.receive_date IS NOT NULL THEN
 484                 con.receive_date
 485             WHEN con.receipt_date IS NOT NULL THEN
 486                 con.receipt_date
 487             ELSE
 488                 {$now}
 489           END
 490        , '-', ''), ':', ''), ' ', '') as trxn_date,
 491        CASE
 492          WHEN con.payment_instrument_id IS NULL THEN
 493               {$financialAccountId}
 494          WHEN con.payment_instrument_id IS NOT NULL THEN
 495               tpi.financial_account_id
 496        END as to_financial_account_id,
 497        IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
 498 FROM      civicrm_contribution con
 499 LEFT JOIN civicrm_entity_financial_trxn eft
 500        ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
 501 LEFT JOIN {$tempTableName1} tpi
 502        ON con.payment_instrument_id = tpi.instrument_id
 503 WHERE     con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})
 504 ";
 505     CRM_Core_DAO::executeQuery($sql);
 506
 507     // CRM-12141
 508     $sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);";
 509     CRM_Core_DAO::executeQuery($sql);
 510
 511     //handling for completed contribution and cancelled contribution
 512     //insertion of new records
 513     $sql = "
 514 INSERT INTO civicrm_financial_trxn
 515             (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
 516             to_financial_account_id, from_financial_account_id, trxn_date)
 517 SELECT   tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
 518          tempI.fee_amount,    tempI.trxn_id,   tempI.contribution_status_id, tempI.check_number,
 519          tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
 520 FROM {$tempTableName2} tempI
 521 WHERE tempI.action = 'insert'
 522 ";
 523     CRM_Core_DAO::executeQuery($sql);
 524
 525     //update of existing records
 526     $sql = "
 527 UPDATE civicrm_financial_trxn ft
 528        INNER JOIN {$tempTableName2} tempU
 529                ON (tempU.action != 'insert' AND ft.id = tempU.action)
 530 SET   ft.from_financial_account_id  = NULL,
 531       ft.to_financial_account_id    = tempU.to_financial_account_id,
 532       ft.status_id                  = tempU.contribution_status_id,
 533       ft.payment_instrument_id      = tempU.payment_instrument_id,
 534       ft.check_number               = tempU.check_number,
 535       ft.contribution_id            = tempU.contribution_id;";
 536     CRM_Core_DAO::executeQuery($sql);
 537
 538     //insert the -ve transaction rows for cancelled contributions
 539     $sql = "
 540 INSERT INTO civicrm_financial_trxn
 541             (contribution_id, payment_instrument_id, currency, total_amount,     net_amount, fee_amount, trxn_id, status_id,
 542             check_number,    to_financial_account_id, from_financial_account_id, trxn_date)
 543 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
 544        ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
 545 FROM   civicrm_financial_trxn ft
 546 WHERE  ft.status_id = {$cancelledStatus};";
 547     CRM_Core_DAO::executeQuery($sql);
 548
 549     //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
 550     //this also handles +ve and -ve both transaction entries for a cancelled contribution
 551     $sql = "
 552 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
 553 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
 554 FROM   civicrm_financial_trxn ft
 555 WHERE  contribution_id IS NOT NULL AND
 556        ft.id NOT IN (SELECT financial_trxn_id
 557                      FROM civicrm_entity_financial_trxn
 558                      WHERE entity_table = 'civicrm_contribution'
 559                      AND entity_id      = ft.contribution_id)";
 560     CRM_Core_DAO::executeQuery($sql);
 561     //end of adding financial_trxn records and entity_financial_trxn records related to contribution
 562
 563     //update all linked line_item rows
 564     // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
 565     // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
 566     // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
 567     // set line_item.financial_type_id = price_field_value.financial_type_id if its participant line item and line_item.price_field_value_id is set
 568     $updateLineItemSql = "
 569 UPDATE civicrm_line_item li
 570        LEFT JOIN civicrm_contribution con
 571               ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
 572        LEFT JOIN civicrm_price_field_value cpfv
 573               ON li.price_field_value_id = cpfv.id
 574        LEFT JOIN civicrm_participant cp
 575               ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
 576        LEFT JOIN civicrm_event ce
 577               ON ce.id = cp.event_id
 578 SET    li.financial_type_id = CASE
 579          WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
 580            con.financial_type_id
 581          WHEN (con.contribution_page_id IS NOT NULL AND cp.id IS NULL) || (cp.id IS NOT NULL AND  li.price_field_value_id IS NOT NULL) THEN
 582            cpfv.financial_type_id
 583          WHEN cp.id IS NOT NULL AND  li.price_field_value_id IS NULL THEN
 584            ce.financial_type_id
 585        END";
 586     CRM_Core_DAO::executeQuery($updateLineItemSql, $queryParams);
 587
 588     //add the financial_item entries
 589     //add a temp column so that inserting entity_financial_trxn entries gets easy
 590     $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
 591     CRM_Core_DAO::executeQuery($sql);
 592
 593     //add financial_item entries for contribution  completed / pending pay later / cancelled
 594     $contributionlineItemSql = "
 595 INSERT INTO civicrm_financial_item
 596            (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 597
 598 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
 599        IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total, con.currency, 'civicrm_line_item',
 600        li.id as line_item_id, li.label as line_item_label,
 601        IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
 602        ft.id as f_trxn_id
 603 FROM  civicrm_line_item li
 604       INNER JOIN civicrm_contribution con
 605               ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
 606       INNER JOIN civicrm_financial_trxn ft
 607               ON (con.id = ft.contribution_id)
 608       LEFT  JOIN civicrm_entity_financial_account efa
 609               ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
 610                   AND efa.account_relationship = {$incomeAccountIs})
 611 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
 612     CRM_Core_DAO::executeQuery($contributionlineItemSql, $queryParams);
 613
 614     //add financial_item entries for event
 615     $participantLineItemSql = "
 616 INSERT INTO civicrm_financial_item
 617            (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 618
 619 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
 620        IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
 621          con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
 622        IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
 623           efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
 624 FROM  civicrm_line_item li
 625       INNER JOIN civicrm_participant par
 626               ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
 627       INNER JOIN civicrm_participant_payment pp
 628               ON (pp.participant_id = par.id)
 629       INNER JOIN civicrm_contribution con
 630               ON (pp.contribution_id = con.id)
 631       INNER JOIN civicrm_financial_trxn ft
 632               ON (con.id = ft.contribution_id)
 633       LEFT  JOIN civicrm_entity_financial_account efa
 634               ON (li.financial_type_id = efa.entity_id AND
 635                   efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
 636 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
 637     CRM_Core_DAO::executeQuery($participantLineItemSql, $queryParams);
 638
 639     //fee handling for contributions
 640     //insert fee entries in financial_trxn for contributions
 641     $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
 642     CRM_Core_DAO::executeQuery($sql);
 643
 644     $sql = "
 645 INSERT INTO civicrm_financial_trxn
 646             (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
 647              to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
 648
 649 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
 650        ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
 651          WHEN efaPP.financial_account_id IS NOT NULL THEN
 652               efaPP.financial_account_id
 653          WHEN tpi.financial_account_id   IS NOT NULL THEN
 654               tpi.financial_account_id
 655          ELSE
 656               {$financialAccountId}
 657        END    as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
 658 FROM   civicrm_contribution con
 659        INNER JOIN civicrm_financial_trxn ft
 660                ON (ft.contribution_id = con.id)
 661        LEFT  JOIN civicrm_entity_financial_account efaFT
 662                ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
 663                    AND efaFT.account_relationship = {$expenseAccountIs})
 664        LEFT  JOIN civicrm_entity_financial_account efaPP
 665                ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
 666                    AND efaPP.account_relationship = {$assetAccountIs})
 667        LEFT  JOIN {$tempTableName1} tpi
 668                ON ft.payment_instrument_id = tpi.instrument_id
 669 WHERE  ft.fee_amount IS NOT NULL AND ft.fee_amount != 0 AND (con.contribution_status_id IN (%1, %3) OR (con.contribution_status_id =%2 AND con.is_pay_later = 1))
 670 GROUP  BY con.id";
 671     CRM_Core_DAO::executeQuery($sql, $queryParams);
 672
 673     //link financial_trxn to contribution
 674     $sql = "
 675 INSERT INTO civicrm_entity_financial_trxn
 676             (entity_table, entity_id, financial_trxn_id, amount)
 677 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
 678 FROM   civicrm_financial_trxn ft
 679 WHERE ft.is_fee = 1";
 680     CRM_Core_DAO::executeQuery($sql);
 681
 682     //add fee related entries to financial item table
 683     $domainId = CRM_Core_Config::domainID();
 684     $domainContactId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
 685     $sql = "
 686 INSERT INTO civicrm_financial_item
 687             (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 688 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
 689        'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
 690 FROM   civicrm_financial_trxn ft
 691 WHERE  ft.is_fee = 1;";
 692     CRM_Core_DAO::executeQuery($sql);
 693
 694     //add entries to entity_financial_trxn table
 695     $sql = "
 696 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
 697 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
 698 FROM   civicrm_financial_item fi";
 699     CRM_Core_DAO::executeQuery($sql);
 700
 701     //drop the temparory columns
 702     $sql = "ALTER TABLE civicrm_financial_trxn
 703                   DROP COLUMN contribution_id,
 704                   DROP COLUMN  is_fee;";
 705     CRM_Core_DAO::executeQuery($sql);
 706
 707     $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
 708     CRM_Core_DAO::executeQuery($sql);
 709
 710     return TRUE;
 711   }
 712
 713   function createDomainContacts() {
 714     $domainParams = $context = array();
 715     $query = "
 716 ALTER TABLE civicrm_domain ADD contact_id INT( 10 ) UNSIGNED NULL DEFAULT NULL COMMENT 'FK to Contact ID. This is specifically not an FK to avoid circular constraints',
 717  ADD CONSTRAINT FK_civicrm_domain_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);";
 718     CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 719
 720     $query = '
 721 SELECT cd.id, cd.name, ce.email FROM civicrm_domain cd
 722 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
 723 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;
 724 ' ;
 725     $dao = CRM_Core_DAO::executeQuery($query);
 726     while($dao->fetch()) {
 727       $query = "
 728 SELECT    cc.id FROM civicrm_contact cc
 729 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
 730 WHERE     cc.contact_type = 'Organization' AND cc.organization_name = %1
 731 ";
 732       $params = array(1 => array($dao->name, 'String'));
 733       if ($dao->email) {
 734         $query .= " AND ce.email = %2 ";
 735         $params[2] = array($dao->email, 'String');
 736       }
 737       $contactID = CRM_Core_DAO::singleValueQuery($query, $params);
 738       $context[1] = $dao->name;
 739       if (empty($contactID)) {
 740         $params = array(
 741           'sort_name' => $dao->name,
 742           'display_name' => $dao->name,
 743           'legal_name' => $dao->name,
 744           'organization_name' => $dao->name,
 745           'contact_type' => 'Organization'
 746         );
 747         $contact = CRM_Contact_BAO_Contact::add($params);
 748         $contactID = $contact->id;
 749         $context[0] = 'added';
 750       }
 751       else {
 752         $context[0] = 'merged';
 753       }
 754       $domainParams['contact_id'] = $contactID;
 755       CRM_Core_BAO_Domain::edit($domainParams, $dao->id);
 756     }
 757     return $context;
 758   }
 759
 760   function task_4_3_alpha1_checkDBConstraints() {
 761     //checking whether the foreign key exists before dropping it CRM-11260
 762     $config = CRM_Core_Config::singleton();
 763     $dbUf = DB::parseDSN($config->dsn);
 764     $tables = array(
 765       'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
 766       'to_account_id' =>  array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
 767       'from_account_id' => array('tableName' =>  'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
 768       'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
 769     );
 770     $query = "
 771 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 772 WHERE table_name = 'civicrm_contribution_recur'
 773 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
 774 AND TABLE_SCHEMA = %1
 775 ";
 776     $params = array(1 => array($dbUf['database'], 'String'));
 777     $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 778     foreach($tables as $columnName => $value){
 779       if ($value['tableName'] == 'civicrm_membership_type' || $value['tableName'] == 'civicrm_contribution_recur') {
 780         $foreignKeyExists = CRM_Core_DAO::checkConstraintExists($value['tableName'], $value['fkey']);
 781         $fKey = $value['fkey'];
 782       } else {
 783         $foreignKeyExists = CRM_Core_DAO::checkFKConstraintInFormat($value['tableName'], $columnName);
 784         $fKey = "`FK_{$value['tableName']}_{$columnName}`";
 785       }
 786       if ($foreignKeyExists || $value['tableName'] == 'civicrm_financial_trxn') {
 787         if ($value['tableName'] != 'civicrm_contribution_recur' || ($value['tableName'] == 'civicrm_contribution_recur' && $dao->N)) {
 788           $constraintName  = $foreignKeyExists ? $fKey : $value['constraintName'];
 789           $query = "ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}";
 790           CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 791         }
 792         $query = "ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}";
 793         CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 794       }
 795     }
 796     // check if column contact_id is present or not in civicrm_financial_account
 797     $fieldExists = CRM_Core_DAO::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
 798     if (!$fieldExists) {
 799       $query = "
 800 ALTER TABLE civicrm_financial_account
 801   ADD contact_id int(10) unsigned DEFAULT NULL COMMENT 'Version identifier of financial_type' AFTER name,
 802   ADD CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);
 803 ";
 804       CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 805     }
 806   }
 807
 808   /**
 809    * Read creation and modification times from civicrm_log; add
 810    * them to civicrm_contact.
 811    */
 812   function convertTimestamps(CRM_Queue_TaskContext $ctx, $startId, $endId) {
 813     $sql = "
 814       SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
 815       FROM civicrm_log
 816       WHERE entity_table = 'civicrm_contact'
 817       AND entity_id BETWEEN %1 AND %2
 818       GROUP BY entity_id
 819     ";
 820     $params = array(
 821       1 => array($startId, 'Integer'),
 822       2 => array($endId, 'Integer'),
 823     );
 824     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 825     while ($dao->fetch()) {
 826       // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
 827       CRM_Core_DAO::executeQuery(
 828         'UPDATE civicrm_contact SET created_date = %1, modified_date = %2 WHERE id = %3',
 829         array(
 830           1 => array($dao->created, 'String'),
 831           2 => array($dao->modified, 'String'),
 832           3 => array($dao->entity_id, 'Integer'),
 833         )
 834       );
 835     }
 836
 837     return TRUE;
 838   }
 839
 840   /**
 841    * replace contribution_type to financial_type in table
 842    * civicrm_saved_search and Structure civicrm_report_instance
 843    */
 844   function replaceContributionTypeId(CRM_Queue_TaskContext $ctx, $query, $table) {
 845     $dao = CRM_Core_DAO::executeQuery($query);
 846     while ($dao->fetch()) {
 847       $formValues = unserialize($dao->form_values);
 848       foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
 849         if (array_key_exists($value, $formValues)) {
 850           $key = preg_replace('/contribution/', 'financial', $value);
 851           $formValues[$key] = $formValues[$value];
 852           unset($formValues[$value]);
 853         }
 854       }
 855       if ($table != 'savedSearch') {
 856         foreach (array('fields', 'group_bys') as $value) {
 857           if (array_key_exists($value, $formValues)) {
 858             if (array_key_exists('contribution_type_id', $formValues[$value])) {
 859               $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
 860               unset($formValues[$value]['contribution_type_id']);
 861             }
 862             else if (array_key_exists('contribution_type', $formValues[$value])) {
 863               $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
 864               unset($formValues[$value]['contribution_type']);
 865             }
 866           }
 867         }
 868         if (array_key_exists('order_bys', $formValues)) {
 869           foreach ($formValues['order_bys'] as $key => $values) {
 870             if (preg_grep('/contribution_type/', $values)) {
 871               $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
 872             }
 873           }
 874         }
 875       }
 876
 877       if ($table == 'savedSearch') {
 878         $saveDao = new CRM_Contact_DAO_SavedSearch();
 879       }
 880       else {
 881         $saveDao = new CRM_Report_DAO_Instance();
 882       }
 883       $saveDao->id = $dao->id;
 884
 885       if ($table == 'savedSearch') {
 886         if (array_key_exists('mapper', $formValues)) {
 887           foreach ($formValues['mapper'] as $key => $values) {
 888             foreach ($values as $k => $v) {
 889               if (preg_grep('/contribution_/', $v)) {
 890                 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
 891               }
 892             }
 893           }
 894         }
 895         foreach (array('select_tables', 'where_tables') as $value) {
 896           if (preg_match('/contribution_type/', $dao->$value)) {
 897             $tempValue = unserialize($dao->$value);
 898             if (array_key_exists('civicrm_contribution_type', $tempValue)) {
 899               $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
 900               unset($tempValue['civicrm_contribution_type']);
 901             }
 902             $saveDao->$value = serialize($tempValue);
 903           }
 904         }
 905         if (preg_match('/contribution_type/', $dao->where_clause)) {
 906           $saveDao->where_clause = preg_replace('/contribution_type/', 'financial_type', $dao->where_clause);
 907         }
 908       }
 909       $saveDao->form_values = serialize($formValues);
 910
 911       $saveDao->save();
 912     }
 913     return TRUE;
 914   }
 915
 916   /**
 917    * Check/Add INDEX CRM-12141
 918    *
 919    * @return bool TRUE for success
 920    */
 921   function task_4_3_x_checkIndexes(CRM_Queue_TaskContext $ctx) {
 922     $query = "
 923 SHOW KEYS
 924 FROM civicrm_entity_financial_trxn
 925 WHERE key_name IN ('UI_entity_financial_trxn_entity_table', 'UI_entity_financial_trxn_entity_id')
 926 ";
 927     $dao = CRM_Core_DAO::executeQuery($query);
 928     if (!$dao->N) {
 929       $query = "
 930 ALTER TABLE civicrm_entity_financial_trxn
 931 ADD INDEX UI_entity_financial_trxn_entity_table (entity_table),
 932 ADD INDEX UI_entity_financial_trxn_entity_id (entity_id);
 933 ";
 934       CRM_Core_DAO::executeQuery($query);
 935     }
 936     return TRUE;
 937   }
 938
 939   /**
 940    * Update phones CRM-11292
 941    *
 942    * @return bool TRUE for success
 943    */
 944   static function phoneNumeric(CRM_Queue_TaskContext $ctx) {
 945     CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::DROP_STRIP_FUNCTION_43);
 946     CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::CREATE_STRIP_FUNCTION_43);
 947     CRM_Core_DAO::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
 948     return TRUE;
 949   }
 950
 951   /**
 952    * (Queue Task Callback)
 953    */
 954   static function task_4_3_x_runSql(CRM_Queue_TaskContext $ctx, $rev) {
 955     $upgrade = new CRM_Upgrade_Form();
 956     $upgrade->processSQL($rev);
 957
 958     return TRUE;
 959   }
 960
 961   /**
 962    * Syntatic sugar for adding a task which (a) is in this class and (b) has
 963    * a high priority.
 964    *
 965    * After passing the $funcName, you can also pass parameters that will go to
 966    * the function. Note that all params must be serializable.
 967    */
 968   protected function addTask($title, $funcName) {
 969     $queue = CRM_Queue_Service::singleton()->load(array(
 970       'type' => 'Sql',
 971       'name' => CRM_Upgrade_Form::QUEUE_NAME,
 972     ));
 973
 974     $args = func_get_args();
 975     $title = array_shift($args);
 976     $funcName = array_shift($args);
 977     $task = new CRM_Queue_Task(
 978       array(get_class($this), $funcName),
 979       $args,
 980       $title
 981     );
 982     $queue->createItem($task, array('weight' => -1));
 983   }
 984 }