CRM/Upgrade/Incremental/php/FourThree.php

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