CRM/Upgrade/Incremental/php/FourThree.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   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
  67       // upgrade pages ... except when we're in Drush.
  68       if (!function_exists('drush_main')) {
  69         theme('item_list', array()); // force-load theme registry
  70         $theme_registry = theme_get_registry();
  71         if (!isset($theme_registry['page']['preprocess functions']) || FALSE === array_search('civicrm_preprocess_page_inject', $theme_registry['page']['preprocess functions'])) {
  72           CRM_Core_Error::fatal('Please reset the Drupal cache (Administer => Site Configuration => Performance => Clear cached data))');
  73         }
  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     if ($rev == '4.3.6') {
 197       $flag = CRM_Core_DAO::singleValueQuery('SELECT count(ccp.id) FROM civicrm_contribution_product ccp
 198 INNER JOIN civicrm_product cp ON ccp.product_id = cp.id
 199 WHERE ccp.financial_type_id IS NULL and cp.cost > 0');
 200       if ($flag) {
 201         $postUpgradeMessage .= '<br />' . ts('Your database contains one or more premiums which have a cost but are not linked to a financial type. If you are exporting transations to an accounting package, this will result in unbalanced transactions. <a href="%1" target="_blank">You can review steps to correct this situation on the wiki.</a>',
 202         array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC/Fixing+Issues+Caused+by+Missing+Cost+of+Goods+Account+-+4.3+Upgrades'));
 203       }
 204     }
 205   }
 206
 207   function upgrade_4_3_alpha1($rev) {
 208     self::task_4_3_alpha1_checkDBConstraints();
 209
 210     // add indexes for civicrm_entity_financial_trxn
 211     // CRM-12141
 212     $this->addTask(ts('Check/Add indexes for civicrm_entity_financial_trxn'), 'task_4_3_x_checkIndexes', $rev);
 213     // task to process sql
 214     $this->addTask(ts('Upgrade DB to 4.3.alpha1: SQL'), 'task_4_3_x_runSql', $rev);
 215
 216     //CRM-11636
 217     $this->addTask(ts('Populate financial type values for price records'), 'assignFinancialTypeToPriceRecords');
 218     //CRM-11514 create financial records for contributions
 219     $this->addTask(ts('Create financial records for contributions'), 'createFinancialRecords');
 220
 221     $minId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
 222     $maxId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
 223     for ($startId = $minId; $startId <= $maxId; $startId += self::BATCH_SIZE) {
 224       $endId = $startId + self::BATCH_SIZE - 1;
 225       $title = ts('Upgrade timestamps (%1 => %2)', array(1 => $startId, 2 => $endId));
 226       $this->addTask($title, 'convertTimestamps', $startId, $endId);
 227     }
 228
 229     // CRM-10893
 230     // fix WP access control
 231     $config = CRM_Core_Config::singleton( );
 232     if ($config->userFramework == 'WordPress') {
 233       civicrm_wp_set_capabilities( );
 234     }
 235
 236     // Update phones CRM-11292.
 237     $this->addTask(ts('Upgrade Phone Numbers'), 'phoneNumeric');
 238
 239     return TRUE;
 240   }
 241
 242   function upgrade_4_3_alpha2($rev) {
 243     //CRM-11847
 244     $isColumnPresent = CRM_Core_DAO::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
 245     if ($isColumnPresent) {
 246       CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
 247     }
 248     $this->addTask(ts('Upgrade DB to 4.3.alpha2: SQL'), 'task_4_3_x_runSql', $rev);
 249   }
 250
 251   function upgrade_4_3_alpha3($rev) {
 252     $this->addTask(ts('Upgrade DB to 4.3.alpha3: SQL'), 'task_4_3_x_runSql', $rev);
 253   }
 254
 255   function upgrade_4_3_beta2($rev) {
 256     $this->addTask(ts('Upgrade DB to 4.3.beta2: SQL'), 'task_4_3_x_runSql', $rev);
 257
 258     // CRM-12002
 259     if (
 260       CRM_Core_DAO::checkTableExists('log_civicrm_line_item') &&
 261       CRM_Core_DAO::checkFieldExists('log_civicrm_line_item', 'label')
 262     ) {
 263       CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
 264     }
 265   }
 266
 267   function upgrade_4_3_beta3($rev) {
 268     $this->addTask(ts('Upgrade DB to 4.3.beta3: SQL'), 'task_4_3_x_runSql', $rev);
 269     // CRM-12065
 270     $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
 271     $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
 272     $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
 273     $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
 274   }
 275
 276   function upgrade_4_3_beta4($rev) {
 277     $this->addTask(ts('Upgrade DB to 4.3.beta4: SQL'), 'task_4_3_x_runSql', $rev);
 278     // add indexes for civicrm_entity_financial_trxn
 279     // CRM-12141
 280     $this->addTask(ts('Check/Add indexes for civicrm_entity_financial_trxn'), 'task_4_3_x_checkIndexes', $rev);
 281   }
 282
 283   function upgrade_4_3_beta5($rev) {
 284     // CRM-12205
 285     if (
 286       CRM_Core_DAO::checkTableExists('log_civicrm_financial_trxn') &&
 287       CRM_Core_DAO::checkFieldExists('log_civicrm_financial_trxn', 'trxn_id')
 288     ) {
 289       CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` VARCHAR(255) NULL DEFAULT NULL');
 290     }
 291     // CRM-12142 - some sites didn't get this column added yet, and sites which installed 4.3 from scratch will already have it
 292     // CRM-12367 - add this column to single lingual sites only
 293     $upgrade = new CRM_Upgrade_Form();
 294     if (!$upgrade->multilingual &&
 295       !CRM_Core_DAO::checkFieldExists('civicrm_premiums', 'premiums_nothankyou_label')
 296     ) {
 297       $query = "
 298 ALTER TABLE civicrm_premiums
 299 ADD COLUMN   premiums_nothankyou_label varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
 300   COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)'
 301 ";
 302       CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
 303     }
 304     $this->addTask(ts('Upgrade DB to 4.3.beta5: SQL'), 'task_4_3_x_runSql', $rev);
 305   }
 306
 307   function upgrade_4_3_4($rev) {
 308     $this->addTask(ts('Upgrade DB to 4.3.4: SQL'), 'task_4_3_x_runSql', $rev);
 309   }
 310
 311   function upgrade_4_3_5($rev) {
 312     // CRM-12156
 313     $config = CRM_Core_Config::singleton();
 314     $dbname  = DB::parseDSN($config->dsn);
 315     $sql = "SELECT DELETE_RULE
 316 FROM information_schema.REFERENTIAL_CONSTRAINTS
 317 WHERE CONSTRAINT_NAME = 'FK_civicrm_financial_item_contact_id'
 318 AND CONSTRAINT_SCHEMA = %1";
 319     $params = array(1 => array($dbname['database'], 'String'));
 320     $onDelete = CRM_Core_DAO::singleValueQuery($sql, $params, TRUE, FALSE);
 321
 322     if ($onDelete != 'CASCADE') {
 323       $query = "ALTER TABLE `civicrm_financial_item`
 324 DROP FOREIGN KEY FK_civicrm_financial_item_contact_id,
 325 DROP INDEX FK_civicrm_financial_item_contact_id;";
 326       CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
 327       $query = "
 328 ALTER TABLE `civicrm_financial_item`
 329 ADD CONSTRAINT `FK_civicrm_financial_item_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
 330 ";
 331       CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
 332     }
 333     $this->addTask(ts('Upgrade DB to 4.3.5: SQL'), 'task_4_3_x_runSql', $rev);
 334   }
 335
 336   function upgrade_4_3_6($rev) {
 337     //CRM-13094
 338     $this->addTask(ts('Add mising contraints'), 'addMissingConstraints', $rev);
 339     //CRM-13088
 340     $this->addTask(ts('Add ON DELETE Options for constraints'), 'task_4_3_x_checkConstraints', $rev);
 341     $this->addTask(ts('Upgrade DB to 4.3.6: SQL'), 'task_4_3_x_runSql', $rev);
 342     // CRM-12844
 343     // update line_item, financial_trxn and financial_item table for recurring contributions
 344     $this->addTask(ts('Update financial_account_id in financial_trxn table'), 'updateFinancialTrxnData', $rev);
 345     $this->addTask(ts('Update Line Item Data'), 'updateLineItemData', $rev);
 346   }
 347
 348   //CRM-11636
 349   function assignFinancialTypeToPriceRecords() {
 350     $upgrade = new CRM_Upgrade_Form();
 351     //here we update price set entries
 352     $sqlFinancialIds = "
 353 SELECT id, LCASE(name) name
 354 FROM   civicrm_financial_type
 355 WHERE name IN ('Donation', 'Event Fee', 'Member Dues');
 356 ";
 357     $daoFinancialIds = CRM_Core_DAO::executeQuery($sqlFinancialIds);
 358     while($daoFinancialIds->fetch()) {
 359       $financialIds[$daoFinancialIds->name] = $daoFinancialIds->id;
 360     }
 361     $sqlPriceSetUpdate = "
 362 UPDATE civicrm_price_set ps
 363 SET    ps.financial_type_id =
 364   CASE
 365     WHEN ps.extends LIKE '%1%' THEN {$financialIds['event fee']}
 366     WHEN ps.extends LIKE '2' THEN {$financialIds['donation']}
 367     WHEN ps.extends LIKE '3' THEN {$financialIds['member dues']}
 368   END
 369 WHERE  financial_type_id IS NULL
 370 ";
 371     CRM_Core_DAO::executeQuery($sqlPriceSetUpdate);
 372
 373     //here we update price field value rows
 374     $sqlPriceFieldValueUpdate = "
 375 UPDATE     civicrm_price_field_value pfv
 376 LEFT JOIN  civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
 377 INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
 378 INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
 379  SET pfv.financial_type_id =
 380    CASE
 381      WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
 382      WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
 383    END
 384 ";
 385     CRM_Core_DAO::executeQuery($sqlPriceFieldValueUpdate);
 386
 387     return TRUE;
 388   }
 389
 390   static function _checkAndMigrateDefaultFinancialTypes() {
 391     $modifiedDefaults = FALSE;
 392     //insert types if not exists
 393     $sqlFetchTypes = "
 394 SELECT id, name
 395 FROM   civicrm_contribution_type
 396 WHERE  name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1
 397 ";
 398     $daoFetchTypes = CRM_Core_DAO::executeQuery($sqlFetchTypes);
 399
 400     if ($daoFetchTypes->N < 3) {
 401       $modifiedDefaults = TRUE;
 402       $insertStatments = array (
 403         'Donation' => "('Donation', 0, 1, 1)",
 404         'Member' => "('Member Dues', 0, 1, 1)",
 405         'Event Fee' => "('Event Fee', 0, 1, 0)",
 406       );
 407       foreach ($insertStatments as $values) {
 408         $query = "
 409 INSERT INTO  civicrm_contribution_type  (name, is_reserved, is_active, is_deductible)
 410 VALUES       $values
 411 ON DUPLICATE KEY UPDATE  is_active = 1
 412 ";
 413         CRM_Core_DAO::executeQuery($query);
 414       }
 415     }
 416     return $modifiedDefaults;
 417   }
 418
 419   function createFinancialRecords() {
 420     $upgrade = new CRM_Upgrade_Form();
 421
 422     // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
 423     $query = "
 424 UPDATE    civicrm_entity_financial_trxn ceft
 425 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
 426 SET       ceft.amount = total_amount
 427 WHERE     cft.net_amount IS NOT NULL
 428 AND       ceft.entity_table = 'civicrm_contribution'
 429 ";
 430     CRM_Core_DAO::executeQuery($query);
 431
 432     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 433     $completedStatus = array_search('Completed', $contributionStatus);
 434     $pendingStatus = array_search('Pending', $contributionStatus);
 435     $cancelledStatus = array_search('Cancelled', $contributionStatus);
 436     $queryParams = array(
 437       1 => array($completedStatus, 'Integer'),
 438       2 => array($pendingStatus, 'Integer'),
 439       3 => array($cancelledStatus, 'Integer')
 440     );
 441
 442     $accountType = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
 443     $query = "
 444 SELECT id
 445 FROM   civicrm_financial_account
 446 WHERE  is_default = 1
 447 AND    financial_account_type_id = {$accountType}
 448 ";
 449     $financialAccountId = CRM_Core_DAO::singleValueQuery($query);
 450
 451     $accountRelationsips = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_EntityFinancialAccount', 'account_relationship');
 452
 453     $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
 454     $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
 455     $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
 456     $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
 457
 458     $financialItemStatus = CRM_Core_PseudoConstant::get('CRM_Financial_DAO_FinancialItem', 'status_id');
 459     $unpaidStatus = array_search('Unpaid', $financialItemStatus);
 460     $paidStatus = array_search('Paid', $financialItemStatus);
 461
 462     $validCurrencyCodes = CRM_Core_PseudoConstant::currencyCode();
 463     $validCurrencyCodes = implode("','", $validCurrencyCodes);
 464     $config = CRM_Core_Config::singleton();
 465     $defaultCurrency = $config->defaultCurrency;
 466     $now = date( 'YmdHis' );
 467
 468     //adding financial_trxn records and entity_financial_trxn records related to contribution
 469     //Add temp column for easy entry in entity_financial_trxn
 470     $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
 471     CRM_Core_DAO::executeQuery($sql);
 472
 473     //pending pay later status handling
 474     $sql = "
 475 INSERT INTO civicrm_financial_trxn
 476        (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
 477        check_number, to_financial_account_id, from_financial_account_id, trxn_date)
 478 SELECT con.id as contribution_id, con.payment_instrument_id,
 479        IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
 480        con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
 481        con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
 482         REPLACE(REPLACE(REPLACE(
 483           CASE
 484             WHEN con.receive_date IS NOT NULL THEN
 485                 con.receive_date
 486             WHEN con.receipt_date IS NOT NULL THEN
 487                 con.receipt_date
 488             ELSE
 489                 {$now}
 490           END
 491         , '-', ''), ':', ''), ' ', '') as trxn_date
 492 FROM  civicrm_contribution con
 493       LEFT JOIN civicrm_entity_financial_account efa
 494              ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
 495                  AND efa.account_relationship = {$accountsReceivableAccount})
 496 WHERE con.is_pay_later = 1
 497 AND   con.contribution_status_id = {$pendingStatus}
 498 ";
 499     CRM_Core_DAO::executeQuery($sql);
 500
 501     //create a temp table to hold financial account id related to payment instruments
 502     $tempTableName1 = CRM_Core_DAO::createTempTableName();
 503
 504     $sql =  "
 505 CREATE TEMPORARY TABLE {$tempTableName1}
 506 SELECT     ceft.financial_account_id financial_account_id, cov.value as instrument_id
 507 FROM       civicrm_entity_financial_account ceft
 508 INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
 509 INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
 510 WHERE      cog.name = 'payment_instrument'
 511 ";
 512     CRM_Core_DAO::executeQuery($sql);
 513
 514     //CRM-12141
 515     $sql = "ALTER TABLE {$tempTableName1} ADD INDEX index_instrument_id (instrument_id(200));";
 516     CRM_Core_DAO::executeQuery($sql);
 517
 518     //create temp table to process completed / cancelled contribution
 519     $tempTableName2 = CRM_Core_DAO::createTempTableName();
 520     $sql = "
 521 CREATE TEMPORARY TABLE {$tempTableName2}
 522 SELECT con.id as contribution_id, con.payment_instrument_id,
 523        IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
 524        con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
 525        con.check_number, NULL as from_financial_account_id,
 526        REPLACE(REPLACE(REPLACE(
 527           CASE
 528             WHEN con.receive_date IS NOT NULL THEN
 529                 con.receive_date
 530             WHEN con.receipt_date IS NOT NULL THEN
 531                 con.receipt_date
 532             ELSE
 533                 {$now}
 534           END
 535        , '-', ''), ':', ''), ' ', '') as trxn_date,
 536        CASE
 537          WHEN con.payment_instrument_id IS NULL THEN
 538               {$financialAccountId}
 539          WHEN con.payment_instrument_id IS NOT NULL THEN
 540               tpi.financial_account_id
 541        END as to_financial_account_id,
 542        IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
 543 FROM      civicrm_contribution con
 544 LEFT JOIN civicrm_entity_financial_trxn eft
 545        ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
 546 LEFT JOIN {$tempTableName1} tpi
 547        ON con.payment_instrument_id = tpi.instrument_id
 548 WHERE     con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})
 549 ";
 550     CRM_Core_DAO::executeQuery($sql);
 551
 552     // CRM-12141
 553     $sql = "ALTER TABLE {$tempTableName2} ADD INDEX index_action (action);";
 554     CRM_Core_DAO::executeQuery($sql);
 555
 556     //handling for completed contribution and cancelled contribution
 557     //insertion of new records
 558     $sql = "
 559 INSERT INTO civicrm_financial_trxn
 560             (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
 561             to_financial_account_id, from_financial_account_id, trxn_date)
 562 SELECT   tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
 563          tempI.fee_amount,    tempI.trxn_id,   tempI.contribution_status_id, tempI.check_number,
 564          tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
 565 FROM {$tempTableName2} tempI
 566 WHERE tempI.action = 'insert'
 567 ";
 568     CRM_Core_DAO::executeQuery($sql);
 569
 570     //update of existing records
 571     $sql = "
 572 UPDATE civicrm_financial_trxn ft
 573        INNER JOIN {$tempTableName2} tempU
 574                ON (tempU.action != 'insert' AND ft.id = tempU.action)
 575 SET   ft.from_financial_account_id  = NULL,
 576       ft.to_financial_account_id    = tempU.to_financial_account_id,
 577       ft.status_id                  = tempU.contribution_status_id,
 578       ft.payment_instrument_id      = tempU.payment_instrument_id,
 579       ft.check_number               = tempU.check_number,
 580       ft.contribution_id            = tempU.contribution_id;";
 581     CRM_Core_DAO::executeQuery($sql);
 582
 583     //insert the -ve transaction rows for cancelled contributions
 584     $sql = "
 585 INSERT INTO civicrm_financial_trxn
 586             (contribution_id, payment_instrument_id, currency, total_amount,     net_amount, fee_amount, trxn_id, status_id,
 587             check_number,    to_financial_account_id, from_financial_account_id, trxn_date)
 588 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
 589        ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
 590 FROM   civicrm_financial_trxn ft
 591 WHERE  ft.status_id = {$cancelledStatus};";
 592     CRM_Core_DAO::executeQuery($sql);
 593
 594     //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
 595     //this also handles +ve and -ve both transaction entries for a cancelled contribution
 596     $sql = "
 597 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
 598 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
 599 FROM   civicrm_financial_trxn ft
 600 WHERE  contribution_id IS NOT NULL AND
 601        ft.id NOT IN (SELECT financial_trxn_id
 602                      FROM civicrm_entity_financial_trxn
 603                      WHERE entity_table = 'civicrm_contribution'
 604                      AND entity_id      = ft.contribution_id)";
 605     CRM_Core_DAO::executeQuery($sql);
 606     //end of adding financial_trxn records and entity_financial_trxn records related to contribution
 607
 608     //update all linked line_item rows
 609     // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
 610     // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
 611     // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
 612     // 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
 613     $updateLineItemSql = "
 614 UPDATE civicrm_line_item li
 615        LEFT JOIN civicrm_contribution con
 616               ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
 617        LEFT JOIN civicrm_price_field_value cpfv
 618               ON li.price_field_value_id = cpfv.id
 619        LEFT JOIN civicrm_participant cp
 620               ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
 621        LEFT JOIN civicrm_event ce
 622               ON ce.id = cp.event_id
 623 SET    li.financial_type_id = CASE
 624          WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
 625            con.financial_type_id
 626          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
 627            cpfv.financial_type_id
 628          WHEN cp.id IS NOT NULL AND  li.price_field_value_id IS NULL THEN
 629            ce.financial_type_id
 630        END";
 631     CRM_Core_DAO::executeQuery($updateLineItemSql, $queryParams);
 632
 633     //add the financial_item entries
 634     //add a temp column so that inserting entity_financial_trxn entries gets easy
 635     $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
 636     CRM_Core_DAO::executeQuery($sql);
 637
 638     //add financial_item entries for contribution  completed / pending pay later / cancelled
 639     $contributionlineItemSql = "
 640 INSERT INTO civicrm_financial_item
 641            (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 642
 643 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
 644        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',
 645        li.id as line_item_id, li.label as line_item_label,
 646        IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
 647        ft.id as f_trxn_id
 648 FROM  civicrm_line_item li
 649       INNER JOIN civicrm_contribution con
 650               ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
 651       INNER JOIN civicrm_financial_trxn ft
 652               ON (con.id = ft.contribution_id)
 653       LEFT  JOIN civicrm_entity_financial_account efa
 654               ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
 655                   AND efa.account_relationship = {$incomeAccountIs})
 656 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
 657     CRM_Core_DAO::executeQuery($contributionlineItemSql, $queryParams);
 658
 659     //add financial_item entries for event
 660     $participantLineItemSql = "
 661 INSERT INTO civicrm_financial_item
 662            (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 663
 664 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
 665        IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
 666          con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
 667        IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
 668           efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
 669 FROM  civicrm_line_item li
 670       INNER JOIN civicrm_participant par
 671               ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
 672       INNER JOIN civicrm_participant_payment pp
 673               ON (pp.participant_id = par.id)
 674       INNER JOIN civicrm_contribution con
 675               ON (pp.contribution_id = con.id)
 676       INNER JOIN civicrm_financial_trxn ft
 677               ON (con.id = ft.contribution_id)
 678       LEFT  JOIN civicrm_entity_financial_account efa
 679               ON (li.financial_type_id = efa.entity_id AND
 680                   efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
 681 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
 682     CRM_Core_DAO::executeQuery($participantLineItemSql, $queryParams);
 683
 684     //fee handling for contributions
 685     //insert fee entries in financial_trxn for contributions
 686     $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
 687     CRM_Core_DAO::executeQuery($sql);
 688
 689     $sql = "
 690 INSERT INTO civicrm_financial_trxn
 691             (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
 692              to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
 693
 694 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
 695        ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
 696          WHEN efaPP.financial_account_id IS NOT NULL THEN
 697               efaPP.financial_account_id
 698          WHEN tpi.financial_account_id   IS NOT NULL THEN
 699               tpi.financial_account_id
 700          ELSE
 701               {$financialAccountId}
 702        END    as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
 703 FROM   civicrm_contribution con
 704        INNER JOIN civicrm_financial_trxn ft
 705                ON (ft.contribution_id = con.id)
 706        LEFT  JOIN civicrm_entity_financial_account efaFT
 707                ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
 708                    AND efaFT.account_relationship = {$expenseAccountIs})
 709        LEFT  JOIN civicrm_entity_financial_account efaPP
 710                ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
 711                    AND efaPP.account_relationship = {$assetAccountIs})
 712        LEFT  JOIN {$tempTableName1} tpi
 713                ON ft.payment_instrument_id = tpi.instrument_id
 714 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))
 715 GROUP  BY con.id";
 716     CRM_Core_DAO::executeQuery($sql, $queryParams);
 717
 718     //link financial_trxn to contribution
 719     $sql = "
 720 INSERT INTO civicrm_entity_financial_trxn
 721             (entity_table, entity_id, financial_trxn_id, amount)
 722 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
 723 FROM   civicrm_financial_trxn ft
 724 WHERE ft.is_fee = 1";
 725     CRM_Core_DAO::executeQuery($sql);
 726
 727     //add fee related entries to financial item table
 728     $domainId = CRM_Core_Config::domainID();
 729     $domainContactId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
 730     $sql = "
 731 INSERT INTO civicrm_financial_item
 732             (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 733 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
 734        'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
 735 FROM   civicrm_financial_trxn ft
 736 WHERE  ft.is_fee = 1;";
 737     CRM_Core_DAO::executeQuery($sql);
 738
 739     //add entries to entity_financial_trxn table
 740     $sql = "
 741 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
 742 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
 743 FROM   civicrm_financial_item fi";
 744     CRM_Core_DAO::executeQuery($sql);
 745
 746     //drop the temparory columns
 747     $sql = "ALTER TABLE civicrm_financial_trxn
 748                   DROP COLUMN contribution_id,
 749                   DROP COLUMN  is_fee;";
 750     CRM_Core_DAO::executeQuery($sql);
 751
 752     $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
 753     CRM_Core_DAO::executeQuery($sql);
 754
 755     return TRUE;
 756   }
 757
 758   function createDomainContacts() {
 759     $domainParams = $context = array();
 760     $query = "
 761 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',
 762  ADD CONSTRAINT FK_civicrm_domain_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);";
 763     CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 764
 765     $query = '
 766 SELECT cd.id, cd.name, ce.email FROM civicrm_domain cd
 767 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
 768 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;
 769 ' ;
 770     $dao = CRM_Core_DAO::executeQuery($query);
 771     while($dao->fetch()) {
 772       $query = "
 773 SELECT    cc.id FROM civicrm_contact cc
 774 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
 775 WHERE     cc.contact_type = 'Organization' AND cc.organization_name = %1
 776 ";
 777       $params = array(1 => array($dao->name, 'String'));
 778       if ($dao->email) {
 779         $query .= " AND ce.email = %2 ";
 780         $params[2] = array($dao->email, 'String');
 781       }
 782       $contactID = CRM_Core_DAO::singleValueQuery($query, $params);
 783       $context[1] = $dao->name;
 784       if (empty($contactID)) {
 785         $params = array(
 786           'sort_name' => $dao->name,
 787           'display_name' => $dao->name,
 788           'legal_name' => $dao->name,
 789           'organization_name' => $dao->name,
 790           'contact_type' => 'Organization'
 791         );
 792         $contact = CRM_Contact_BAO_Contact::add($params);
 793         $contactID = $contact->id;
 794         $context[0] = 'added';
 795       }
 796       else {
 797         $context[0] = 'merged';
 798       }
 799       $domainParams['contact_id'] = $contactID;
 800       CRM_Core_BAO_Domain::edit($domainParams, $dao->id);
 801     }
 802     return $context;
 803   }
 804
 805   function task_4_3_alpha1_checkDBConstraints() {
 806     //checking whether the foreign key exists before dropping it CRM-11260
 807     $config = CRM_Core_Config::singleton();
 808     $dbUf = DB::parseDSN($config->dsn);
 809     $tables = array(
 810       'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
 811       'to_account_id' =>  array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
 812       'from_account_id' => array('tableName' =>  'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
 813       'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
 814     );
 815     $query = "
 816 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 817 WHERE table_name = 'civicrm_contribution_recur'
 818 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
 819 AND TABLE_SCHEMA = %1
 820 ";
 821     $params = array(1 => array($dbUf['database'], 'String'));
 822     $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 823     foreach($tables as $columnName => $value){
 824       if ($value['tableName'] == 'civicrm_membership_type' || $value['tableName'] == 'civicrm_contribution_recur') {
 825         $foreignKeyExists = CRM_Core_DAO::checkConstraintExists($value['tableName'], $value['fkey']);
 826         $fKey = $value['fkey'];
 827       } else {
 828         $foreignKeyExists = CRM_Core_DAO::checkFKConstraintInFormat($value['tableName'], $columnName);
 829         $fKey = "`FK_{$value['tableName']}_{$columnName}`";
 830       }
 831       if ($foreignKeyExists || $value['tableName'] == 'civicrm_financial_trxn') {
 832         if ($value['tableName'] != 'civicrm_contribution_recur' || ($value['tableName'] == 'civicrm_contribution_recur' && $dao->N)) {
 833           $constraintName  = $foreignKeyExists ? $fKey : $value['constraintName'];
 834           $query = "ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}";
 835           CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 836         }
 837         $query = "ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}";
 838         CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 839       }
 840     }
 841     // check if column contact_id is present or not in civicrm_financial_account
 842     $fieldExists = CRM_Core_DAO::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
 843     if (!$fieldExists) {
 844       $query = "
 845 ALTER TABLE civicrm_financial_account
 846   ADD contact_id int(10) unsigned DEFAULT NULL COMMENT 'Version identifier of financial_type' AFTER name,
 847   ADD CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);
 848 ";
 849       CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 850     }
 851   }
 852
 853   /**
 854    * Read creation and modification times from civicrm_log; add
 855    * them to civicrm_contact.
 856    */
 857   function convertTimestamps(CRM_Queue_TaskContext $ctx, $startId, $endId) {
 858     $sql = "
 859       SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
 860       FROM civicrm_log
 861       WHERE entity_table = 'civicrm_contact'
 862       AND entity_id BETWEEN %1 AND %2
 863       GROUP BY entity_id
 864     ";
 865     $params = array(
 866       1 => array($startId, 'Integer'),
 867       2 => array($endId, 'Integer'),
 868     );
 869     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 870     while ($dao->fetch()) {
 871       // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
 872       CRM_Core_DAO::executeQuery(
 873         'UPDATE civicrm_contact SET created_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%1)), modified_date = FROM_UNIXTIME(UNIX_TIMESTAMP(%2)) WHERE id = %3',
 874         array(
 875           1 => array($dao->created, 'String'),
 876           2 => array($dao->modified, 'String'),
 877           3 => array($dao->entity_id, 'Integer'),
 878         )
 879       );
 880     }
 881
 882     return TRUE;
 883   }
 884
 885   /**
 886    * change index and add missing constraints for civicrm_contribution_recur
 887    */
 888   function addMissingConstraints(CRM_Queue_TaskContext $ctx) {
 889     $query = "SHOW KEYS FROM `civicrm_contribution_recur` WHERE key_name = 'UI_contrib_payment_instrument_id'";
 890     $dao = CRM_Core_DAO::executeQuery($query);
 891     if ($dao->N) {
 892       CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contribution_recur DROP INDEX UI_contrib_payment_instrument_id');
 893       CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contribution_recur ADD INDEX UI_contribution_recur_payment_instrument_id (payment_instrument_id)');
 894     }
 895     $constraintArray = array(
 896       'contact_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE ",
 897       'payment_processor_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL ",
 898       'financial_type_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`) ON DELETE SET NULL ",
 899       'campaign_id' => " ADD CONSTRAINT `FK_civicrm_contribution_recur_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL ",
 900     );
 901     $constraint = array();
 902     foreach ($constraintArray as $constraintKey => $value) {
 903       $foreignKeyExists = CRM_Core_DAO::checkFKConstraintInFormat('civicrm_contribution_recur', $constraintKey);
 904       if (!$foreignKeyExists) {
 905         $constraint[] = $value;
 906       }
 907     }
 908     if (!empty($constraint)) {
 909       $query = "ALTER TABLE civicrm_contribution_recur " . implode(' , ', $constraint);
 910       CRM_Core_DAO::executeQuery($query);
 911     }
 912     return TRUE;
 913   }
 914
 915   /**
 916    * Update financial_account_id for bad data in financial_trxn table
 917    * CRM-12844
 918    *
 919    */
 920   function updateFinancialTrxnData(CRM_Queue_TaskContext $ctx) {
 921     $upgrade = new CRM_Upgrade_Form();
 922     $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id, cft.payment_processor_id,
 923 cft.id financial_trxn_id, cfi.entity_table, cft.from_financial_account_id, cft.to_financial_account_id
 924
 925 FROM `civicrm_contribution` cc
 926 LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id
 927 LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
 928 LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id
 929 LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id
 930 WHERE ceft.entity_table = 'civicrm_contribution'  AND cc.contribution_recur_id IS NOT NULL
 931 AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = %1
 932
 933 ORDER BY cft.id ";
 934     $paymentInstrument = CRM_Contribute_PseudoConstant::paymentInstrument('name');
 935     $param = array(1 => array(array_search('Credit Card', $paymentInstrument), 'Integer'));
 936     $dao = CRM_Core_DAO::executeQuery($sql, $param);
 937     $financialTrxn = array();
 938     $subsequentPayments = array();
 939     while ($dao->fetch()) {
 940       if (!array_key_exists($dao->contribution_recur_id, $financialTrxn)) {
 941         $financialTrxn[$dao->contribution_recur_id] = array(
 942            'from_financial_account_id' => $dao->to_financial_account_id,
 943            'payment_processor_id' => $dao->payment_processor_id,
 944            $dao->contribution_id => 1,
 945         );
 946         if (!is_null($dao->from_financial_account_id)) {
 947           $sql = 'UPDATE civicrm_financial_trxn SET from_financial_account_id = NULL WHERE id = %1';
 948           $params = array(1 => array($dao->financial_trxn_id, 'Integer'));
 949           CRM_Core_DAO::executeQuery($sql, $params);
 950         }
 951       }
 952       elseif (!array_key_exists($dao->contribution_id, $financialTrxn[$dao->contribution_recur_id])) {
 953         if (($dao->entity_table == 'civicrm_line_item' && $dao->to_financial_account_id == $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'])
 954           || ($dao->entity_table == 'civicrm_financial_trxn' && $dao->from_financial_account_id == $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'])) {
 955           continue;
 956         }
 957           $subsequentPayments[$dao->contribution_recur_id][$dao->entity_table][] = $dao->financial_trxn_id;
 958       }
 959     }
 960     foreach ($subsequentPayments as $key => $value) {
 961       foreach ($value as $table => $val) {
 962         if ($table == 'civicrm_financial_trxn') {
 963           $field = 'from_financial_account_id';
 964         }
 965         else {
 966           $field = 'to_financial_account_id';
 967         }
 968         $sql = "UPDATE civicrm_financial_trxn SET $field = " . $financialTrxn[$dao->contribution_recur_id]['from_financial_account_id'] . ',
 969 payment_processor_id = ' . $financialTrxn[$dao->contribution_recur_id]['payment_processor_id'] . ' WHERE
 970 id IN (' . implode(',', $val) . ')';
 971         CRM_Core_DAO::executeQuery($sql);
 972       }
 973     }
 974     return TRUE;
 975   }
 976
 977   /**
 978    * Update financial_account_id for bad data in financial_trxn table
 979    * CRM-12844
 980    *
 981    */
 982   function updateLineItemData(CRM_Queue_TaskContext $ctx) {
 983     $sql = "SELECT cc.id contribution_id, cc.contribution_recur_id,
 984 cc.financial_type_id contribution_financial_type,
 985 cli.financial_type_id line_financial_type_id,
 986 cli.price_field_id, cli.price_field_value_id, cli.label, cli.id line_item_id,
 987 cfi.financial_account_id
 988 FROM `civicrm_line_item` cli
 989 LEFT JOIN civicrm_contribution cc ON cc.id =  cli.entity_id
 990 LEFT JOIN civicrm_financial_item cfi ON cfi.entity_id = cli.id
 991 LEFT JOIN civicrm_price_field cpf ON cpf.id = cli.price_field_id
 992 LEFT JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id
 993 LEFT JOIN civicrm_price_field_value cpfv ON cpfv.id = cli.price_field_value_id
 994 WHERE cfi.entity_table = 'civicrm_line_item'
 995 AND cli.entity_table = 'civicrm_contribution'
 996 AND cps.is_quick_config = 1 AND cc.contribution_recur_id IS NOT NULL
 997 ORDER BY cli.id";
 998     $dao = CRM_Core_DAO::executeQuery($sql);
 999     $financialTrxn = $subsequentPayments = array();
1000     while ($dao->fetch()) {
1001       if (!array_key_exists($dao->contribution_recur_id, $financialTrxn)) {
1002         $financialTrxn[$dao->contribution_recur_id] = array(
1003            'price_field_id' => $dao->price_field_id,
1004            'price_field_value_id' => $dao->price_field_value_id,
1005            'label' => strval($dao->label),
1006            'financial_account_id' => $dao->financial_account_id,
1007            $dao->contribution_id => 1,
1008         );
1009       }
1010       else {
1011         if ($dao->price_field_value_id == $financialTrxn[$dao->contribution_recur_id]['price_field_value_id']) {
1012           continue;
1013         }
1014          $subsequentPayments[$dao->contribution_recur_id][] = $dao->line_item_id;
1015       }
1016     }
1017     foreach ($subsequentPayments as $key => $value) {
1018       $sql = "UPDATE civicrm_line_item cli
1019 LEFT JOIN civicrm_financial_item cfi ON cli.id = cfi.entity_id
1020 SET
1021 cli.label = %1,
1022 cli.price_field_id = %2,
1023 cli.price_field_value_id = %3,
1024 cfi.financial_account_id = %4,
1025 cfi.description = %5,
1026 cli.financial_type_id = %6
1027 WHERE cfi.entity_table = 'civicrm_line_item'
1028 AND cli.entity_table = 'civicrm_contribution' AND cli.id IN (" . implode(',', $value). ');';
1029       $params =  array(
1030         1 => array($financialTrxn[$key]['label'], 'String'),
1031         2 => array($financialTrxn[$key]['price_field_id'], 'Integer'),
1032         3 => array($financialTrxn[$key]['price_field_value_id'], 'Integer'),
1033         4 => array($financialTrxn[$key]['financial_account_id'], 'Integer'),
1034         5 => array($financialTrxn[$key]['label'], 'String'),
1035         6 => array($dao->contribution_financial_type, 'Integer'),
1036       );
1037       CRM_Core_DAO::executeQuery($sql, $params);
1038     }
1039     return TRUE;
1040   }
1041
1042   /**
1043    * replace contribution_type to financial_type in table
1044    * civicrm_saved_search and Structure civicrm_report_instance
1045    */
1046   function replaceContributionTypeId(CRM_Queue_TaskContext $ctx, $query, $table) {
1047     $dao = CRM_Core_DAO::executeQuery($query);
1048     while ($dao->fetch()) {
1049       $formValues = unserialize($dao->form_values);
1050       foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
1051         if (array_key_exists($value, $formValues)) {
1052           $key = preg_replace('/contribution/', 'financial', $value);
1053           $formValues[$key] = $formValues[$value];
1054           unset($formValues[$value]);
1055         }
1056       }
1057       if ($table != 'savedSearch') {
1058         foreach (array('fields', 'group_bys') as $value) {
1059           if (array_key_exists($value, $formValues)) {
1060             if (array_key_exists('contribution_type_id', $formValues[$value])) {
1061               $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
1062               unset($formValues[$value]['contribution_type_id']);
1063             }
1064             else if (array_key_exists('contribution_type', $formValues[$value])) {
1065               $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
1066               unset($formValues[$value]['contribution_type']);
1067             }
1068           }
1069         }
1070         if (array_key_exists('order_bys', $formValues)) {
1071           foreach ($formValues['order_bys'] as $key => $values) {
1072             if (preg_grep('/contribution_type/', $values)) {
1073               $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
1074             }
1075           }
1076         }
1077       }
1078
1079       if ($table == 'savedSearch') {
1080         $saveDao = new CRM_Contact_DAO_SavedSearch();
1081       }
1082       else {
1083         $saveDao = new CRM_Report_DAO_ReportInstance();
1084       }
1085       $saveDao->id = $dao->id;
1086
1087       if ($table == 'savedSearch') {
1088         if (array_key_exists('mapper', $formValues)) {
1089           foreach ($formValues['mapper'] as $key => $values) {
1090             foreach ($values as $k => $v) {
1091               if (preg_grep('/contribution_/', $v)) {
1092                 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
1093               }
1094             }
1095           }
1096         }
1097         foreach (array('select_tables', 'where_tables') as $value) {
1098           if (preg_match('/contribution_type/', $dao->$value)) {
1099             $tempValue = unserialize($dao->$value);
1100             if (array_key_exists('civicrm_contribution_type', $tempValue)) {
1101               $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
1102               unset($tempValue['civicrm_contribution_type']);
1103             }
1104             $saveDao->$value = serialize($tempValue);
1105           }
1106         }
1107         if (preg_match('/contribution_type/', $dao->where_clause)) {
1108           $saveDao->where_clause = preg_replace('/contribution_type/', 'financial_type', $dao->where_clause);
1109         }
1110       }
1111       $saveDao->form_values = serialize($formValues);
1112
1113       $saveDao->save();
1114     }
1115     return TRUE;
1116   }
1117
1118   /**
1119    * Add ON DELETE options for constraint if not present
1120    * CRM-13088 && CRM-12156
1121    *
1122    * @return bool TRUE for success
1123    */
1124   function task_4_3_x_checkConstraints(CRM_Queue_TaskContext $ctx) {
1125     $config = CRM_Core_Config::singleton();
1126     $dbname  = DB::parseDSN($config->dsn);
1127     $constraintArray = array(
1128       "'FK_civicrm_financial_account_contact_id'",
1129       "'FK_civicrm_financial_item_contact_id'",
1130       "'FK_civicrm_contribution_recur_financial_type_id'",
1131       "'FK_civicrm_line_item_financial_type_id'",
1132       "'FK_civicrm_product_financial_type_id'",
1133       "'FK_civicrm_premiums_product_financial_type_id'",
1134       "'FK_civicrm_price_field_value_financial_type_id'",
1135       "'FK_civicrm_contribution_product_financial_type_id'",
1136       "'FK_civicrm_price_set_financial_type_id'",
1137       "'FK_civicrm_grant_financial_type_id'",
1138     );
1139
1140     $sql = "SELECT DELETE_RULE, TABLE_NAME, CONSTRAINT_NAME
1141 FROM information_schema.REFERENTIAL_CONSTRAINTS
1142 WHERE CONSTRAINT_NAME IN (" . implode(',', $constraintArray) . ")
1143 AND CONSTRAINT_SCHEMA = %1";
1144     $params = array(1 => array($dbname['database'], 'String'));
1145     $onDelete = CRM_Core_DAO::executeQuery($sql, $params, TRUE, FALSE);
1146     while ($onDelete->fetch()) {
1147       if (($onDelete->TABLE_NAME != 'civicrm_financial_item' && $onDelete->DELETE_RULE != 'SET NULL') ||
1148         ($onDelete->TABLE_NAME == 'civicrm_financial_item' && $onDelete->DELETE_RULE != 'CASCADE')) {
1149         $tableName = 'civicrm_financial_type';
1150         $onDeleteOption = ' SET NULL ';
1151         $columnName = 'financial_type_id';
1152         if (preg_match('/contact_id/', $onDelete->CONSTRAINT_NAME)) {
1153           $tableName = 'civicrm_contact';
1154           $columnName = 'contact_id';
1155           if ($onDelete->TABLE_NAME == 'civicrm_financial_item') {
1156             $onDeleteOption = 'CASCADE';
1157           }
1158         }
1159       }
1160       else {
1161         continue;
1162       }
1163       $query = "ALTER TABLE {$onDelete->TABLE_NAME}
1164         DROP FOREIGN KEY {$onDelete->CONSTRAINT_NAME},
1165         DROP INDEX {$onDelete->CONSTRAINT_NAME};";
1166       CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1167       $query = " ALTER TABLE  {$onDelete->TABLE_NAME}
1168         ADD CONSTRAINT  {$onDelete->CONSTRAINT_NAME} FOREIGN KEY (`" . $columnName . "`) REFERENCES {$tableName} (`id`) ON DELETE {$onDeleteOption};
1169         ";
1170       CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
1171     }
1172     return TRUE;
1173   }
1174
1175   /**
1176    * Check/Add INDEX CRM-12141
1177    *
1178    * @return bool TRUE for success
1179    */
1180   function task_4_3_x_checkIndexes(CRM_Queue_TaskContext $ctx) {
1181     $query = "
1182 SHOW KEYS
1183 FROM civicrm_entity_financial_trxn
1184 WHERE key_name IN ('UI_entity_financial_trxn_entity_table', 'UI_entity_financial_trxn_entity_id')
1185 ";
1186     $dao = CRM_Core_DAO::executeQuery($query);
1187     if (!$dao->N) {
1188       $query = "
1189 ALTER TABLE civicrm_entity_financial_trxn
1190 ADD INDEX UI_entity_financial_trxn_entity_table (entity_table),
1191 ADD INDEX UI_entity_financial_trxn_entity_id (entity_id);
1192 ";
1193       CRM_Core_DAO::executeQuery($query);
1194     }
1195     return TRUE;
1196   }
1197
1198   /**
1199    * Update phones CRM-11292
1200    *
1201    * @return bool TRUE for success
1202    */
1203   static function phoneNumeric(CRM_Queue_TaskContext $ctx) {
1204     CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::DROP_STRIP_FUNCTION_43);
1205     CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::CREATE_STRIP_FUNCTION_43);
1206     CRM_Core_DAO::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
1207     return TRUE;
1208   }
1209
1210   /**
1211    * (Queue Task Callback)
1212    */
1213   static function task_4_3_x_runSql(CRM_Queue_TaskContext $ctx, $rev) {
1214     $upgrade = new CRM_Upgrade_Form();
1215     $upgrade->processSQL($rev);
1216
1217     return TRUE;
1218   }
1219
1220   /**
1221    * Syntatic sugar for adding a task which (a) is in this class and (b) has
1222    * a high priority.
1223    *
1224    * After passing the $funcName, you can also pass parameters that will go to
1225    * the function. Note that all params must be serializable.
1226    */
1227   protected function addTask($title, $funcName) {
1228     $queue = CRM_Queue_Service::singleton()->load(array(
1229       'type' => 'Sql',
1230       'name' => CRM_Upgrade_Form::QUEUE_NAME,
1231     ));
1232
1233     $args = func_get_args();
1234     $title = array_shift($args);
1235     $funcName = array_shift($args);
1236     $task = new CRM_Queue_Task(
1237       array(get_class($this), $funcName),
1238       $args,
1239       $title
1240     );
1241     $queue->createItem($task, array('weight' => -1));
1242   }
1243 }