CRM/Upgrade/Incremental/php/FourThree.php

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