CRM/Upgrade/Incremental/php/FourThree.php

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