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