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     // task to process sql
 143     $this->addTask(ts('Upgrade DB to 4.3.alpha1: SQL'), 'task_4_3_x_runSql', $rev);
 144
 145     //CRM-11636
 146     $this->addTask(ts('Populate financial type values for price records'), 'assignFinancialTypeToPriceRecords');
 147     //CRM-11514 create financial records for contributions
 148     $this->addTask(ts('Create financial records for contributions'), 'createFinancialRecords');
 149
 150     $minId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contact');
 151     $maxId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contact');
 152     for ($startId = $minId; $startId <= $maxId; $startId += self::BATCH_SIZE) {
 153       $endId = $startId + self::BATCH_SIZE - 1;
 154       $title = ts('Upgrade timestamps (%1 => %2)', array(
 155                  1 => $startId,
 156                  2 => $endId,
 157                ));
 158       $this->addTask($title, 'convertTimestamps', $startId, $endId);
 159     }
 160
 161     // CRM-10893
 162     // fix WP access control
 163     $config = CRM_Core_Config::singleton( );
 164     if ($config->userFramework == 'WordPress') {
 165       civicrm_wp_set_capabilities( );
 166     }
 167
 168     // Update phones CRM-11292.
 169     $this->addTask(ts('Upgrade Phone Numbers'), 'phoneNumeric');
 170
 171     return TRUE;
 172   }
 173
 174   function upgrade_4_3_alpha2($rev) {
 175     //CRM-11847
 176     $isColumnPresent = CRM_Core_DAO::checkFieldExists('civicrm_dedupe_rule_group', 'is_default');
 177     if ($isColumnPresent) {
 178       CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_dedupe_rule_group DROP COLUMN is_default');
 179     }
 180     $this->addTask(ts('Upgrade DB to 4.3.alpha2: SQL'), 'task_4_3_x_runSql', $rev);
 181   }
 182
 183   function upgrade_4_3_alpha3($rev) {
 184     $this->addTask(ts('Upgrade DB to 4.3.alpha3: SQL'), 'task_4_3_x_runSql', $rev);
 185   }
 186
 187   function upgrade_4_3_beta2($rev) {
 188     $this->addTask(ts('Upgrade DB to 4.3.beta2: SQL'), 'task_4_3_x_runSql', $rev);
 189     // CRM-12002
 190     if (
 191       CRM_Core_DAO::checkTableExists('log_civicrm_line_item') &&
 192       CRM_Core_DAO::checkFieldExists('log_civicrm_line_item', 'label')
 193     ) {
 194       CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_line_item` CHANGE `label` `label` VARCHAR(255) NULL DEFAULT NULL');
 195     }
 196   }
 197
 198   function upgrade_4_3_beta3($rev) {
 199     $this->addTask(ts('Upgrade DB to 4.3.beta3: SQL'), 'task_4_3_x_runSql', $rev);
 200     // CRM-12065
 201     $query = "SELECT id, form_values FROM civicrm_report_instance WHERE form_values LIKE '%contribution_type%'";
 202     $this->addTask('Replace contribution_type to financial_type in table civicrm_report_instance', 'replaceContributionTypeId', $query, 'reportInstance');
 203     $query = "SELECT * FROM civicrm_saved_search WHERE form_values LIKE '%contribution_type%'";
 204     $this->addTask('Replace contribution_type to financial_type in table civicrm_saved_search', 'replaceContributionTypeId', $query, 'savedSearch');
 205   }
 206
 207   function upgrade_4_3_beta4($rev) {
 208     $this->addTask(ts('Upgrade DB to 4.3.beta4: SQL'), 'task_4_3_x_runSql', $rev);
 209   }
 210
 211   //CRM-11636
 212   function assignFinancialTypeToPriceRecords() {
 213     $upgrade = new CRM_Upgrade_Form();
 214     //here we update price set entries
 215     $sqlFinancialIds = "SELECT id, name FROM civicrm_financial_type
 216  WHERE name IN ('Donation', 'Event Fee', 'Member Dues');";
 217     $daoFinancialIds = CRM_Core_DAO::executeQuery($sqlFinancialIds);
 218     while($daoFinancialIds->fetch()) {
 219       $financialIds[$daoFinancialIds->name] = $daoFinancialIds->id;
 220     }
 221     $sqlPriceSetUpdate = "UPDATE civicrm_price_set ps
 222   SET ps.financial_type_id = CASE
 223     WHEN ps.extends LIKE '%1%' THEN {$financialIds['Event Fee']}
 224     WHEN ps.extends LIKE '2' THEN {$financialIds['Donation']}
 225     WHEN ps.extends LIKE '3' THEN {$financialIds['Member Dues']}
 226   END
 227   WHERE financial_type_id IS NULL";
 228     CRM_Core_DAO::executeQuery($sqlPriceSetUpdate);
 229
 230     //here we update price field value rows
 231     $sqlPriceFieldValueUpdate = "UPDATE civicrm_price_field_value pfv
 232  LEFT JOIN civicrm_membership_type mt ON (pfv.membership_type_id = mt.id)
 233  INNER JOIN civicrm_price_field pf ON (pfv.price_field_id = pf.id)
 234  INNER JOIN civicrm_price_set ps ON (pf.price_set_id = ps.id)
 235  SET pfv.financial_type_id = CASE
 236    WHEN pfv.membership_type_id IS NOT NULL THEN mt.financial_type_id
 237    WHEN pfv.membership_type_id IS NULL THEN ps.financial_type_id
 238  END";
 239     CRM_Core_DAO::executeQuery($sqlPriceFieldValueUpdate);
 240
 241     return TRUE;
 242   }
 243
 244   static function _checkAndMigrateDefaultFinancialTypes() {
 245     $modifiedDefaults = FALSE;
 246     //insert types if not exists
 247     $sqlFetchTypes = "SELECT id, name FROM civicrm_contribution_type
 248   WHERE name IN ('Donation', 'Event Fee', 'Member Dues') AND is_active =1;";
 249     $daoFetchTypes = CRM_Core_DAO::executeQuery($sqlFetchTypes);
 250
 251     if ($daoFetchTypes->N < 3) {
 252       $modifiedDefaults = TRUE;
 253       $insertStatments = array (
 254         'Donation' => "('Donation', 0, 1, 1)",
 255         'Member' => "('Member Dues', 0, 1, 1)",
 256         'Event Fee' => "('Event Fee', 0, 1, 0)",
 257       );
 258       foreach ($insertStatments as $values) {
 259         $query = "INSERT INTO  civicrm_contribution_type  (name, is_reserved, is_active, is_deductible)
 260           VALUES $values
 261           ON DUPLICATE KEY UPDATE  is_active = 1;";
 262         CRM_Core_DAO::executeQuery($query);
 263       }
 264     }
 265     return $modifiedDefaults;
 266   }
 267
 268   function createFinancialRecords() {
 269     $upgrade = new CRM_Upgrade_Form();
 270
 271     // update civicrm_entity_financial_trxn.amount = civicrm_financial_trxn.total_amount
 272     $query = "UPDATE civicrm_entity_financial_trxn ceft
 273       LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
 274       SET ceft.amount = total_amount
 275       WHERE cft.net_amount IS NOT NULL AND ceft.entity_table = 'civicrm_contribution';";
 276     CRM_Core_DAO::executeQuery($query);
 277
 278     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
 279     $completedStatus = array_search('Completed', $contributionStatus);
 280     $pendingStatus = array_search('Pending', $contributionStatus);
 281     $cancelledStatus = array_search('Cancelled', $contributionStatus);
 282     $queryParams = array(
 283       1 => array($completedStatus, 'Integer'),
 284       2 => array($pendingStatus, 'Integer'),
 285       3 => array($cancelledStatus, 'Integer')
 286     );
 287
 288     $accountType = key(CRM_Core_PseudoConstant::accountOptionValues('financial_account_type', NULL, " AND v.name = 'Asset' "));
 289     $financialAccountId =
 290       CRM_Core_DAO::singleValueQuery("SELECT id FROM civicrm_financial_account WHERE is_default = 1 AND financial_account_type_id = {$accountType}");
 291
 292     $accountRelationsips = CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL);
 293
 294     $accountsReceivableAccount = array_search('Accounts Receivable Account is', $accountRelationsips);
 295     $incomeAccountIs = array_search('Income Account is', $accountRelationsips);
 296     $assetAccountIs = array_search('Asset Account is', $accountRelationsips);
 297     $expenseAccountIs = array_search('Expense Account is', $accountRelationsips);
 298
 299     $financialItemStatus = CRM_Core_PseudoConstant::accountOptionValues('financial_item_status');
 300     $unpaidStatus = array_search('Unpaid', $financialItemStatus);
 301     $paidStatus = array_search('Paid', $financialItemStatus);
 302
 303     $validCurrencyCodes = CRM_Core_PseudoConstant::currencyCode();
 304     $validCurrencyCodes = implode("','", $validCurrencyCodes);
 305     $config = CRM_Core_Config::singleton();
 306     $defaultCurrency = $config->defaultCurrency;
 307     $now = date( 'YmdHis' );
 308
 309     //adding financial_trxn records and entity_financial_trxn records related to contribution
 310     //Add temp column for easy entry in entity_financial_trxn
 311     $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN contribution_id INT DEFAULT NULL";
 312     CRM_Core_DAO::executeQuery($sql);
 313
 314     //pending pay later status handling
 315     $sql = "
 316 INSERT INTO civicrm_financial_trxn
 317        (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id,
 318        check_number, to_financial_account_id, from_financial_account_id, trxn_date)
 319
 320 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}')
 321         as currency, con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id,
 322         con.check_number, efa.financial_account_id as to_financial_account_id, NULL as from_financial_account_id,
 323         REPLACE(REPLACE(REPLACE(
 324           CASE
 325             WHEN con.receive_date IS NOT NULL THEN
 326                 con.receive_date
 327             WHEN con.receipt_date IS NOT NULL THEN
 328                 con.receipt_date
 329             ELSE
 330                 {$now}
 331           END
 332         , '-', ''), ':', ''), ' ', '') as trxn_date
 333 FROM  civicrm_contribution con
 334       LEFT JOIN civicrm_entity_financial_account efa
 335              ON (con.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
 336                  AND efa.account_relationship = {$accountsReceivableAccount})
 337 WHERE con.is_pay_later = 1 AND con.contribution_status_id = {$pendingStatus}";
 338     CRM_Core_DAO::executeQuery($sql);
 339
 340     //create a temp table to hold financial account id related to payment instruments
 341     $tempTableName1 = CRM_Core_DAO::createTempTableName();
 342
 343     $sql =  "CREATE TEMPORARY TABLE {$tempTableName1}
 344
 345 SELECT ceft.financial_account_id financial_account_id, cov.value as instrument_id
 346 FROM civicrm_entity_financial_account ceft
 347      INNER JOIN civicrm_option_value cov ON cov.id = ceft.entity_id AND ceft.entity_table = 'civicrm_option_value'
 348      INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
 349 WHERE cog.name = 'payment_instrument'";
 350     CRM_Core_DAO::executeQuery($sql);
 351
 352     //create temp table to process completed / cancelled contribution
 353     $tempTableName2 = CRM_Core_DAO::createTempTableName();
 354     $sql = "CREATE TEMPORARY TABLE {$tempTableName2}
 355
 356 SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('{$validCurrencyCodes}'), con.currency, '{$defaultCurrency}') as currency,
 357        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,
 358        REPLACE(REPLACE(REPLACE(
 359           CASE
 360             WHEN con.receive_date IS NOT NULL THEN
 361                 con.receive_date
 362             WHEN con.receipt_date IS NOT NULL THEN
 363                 con.receipt_date
 364             ELSE
 365                 {$now}
 366           END
 367        , '-', ''), ':', ''), ' ', '') as trxn_date,
 368        CASE
 369          WHEN con.payment_instrument_id IS NULL THEN
 370               {$financialAccountId}
 371          WHEN con.payment_instrument_id IS NOT NULL THEN
 372               tpi.financial_account_id
 373        END as to_financial_account_id,
 374        IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
 375 FROM   civicrm_contribution con
 376        LEFT JOIN civicrm_entity_financial_trxn eft
 377               ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
 378        LEFT JOIN {$tempTableName1} tpi
 379               ON con.payment_instrument_id = tpi.instrument_id
 380 WHERE con.contribution_status_id IN ({$completedStatus}, {$cancelledStatus})";
 381     CRM_Core_DAO::executeQuery($sql);
 382
 383     //handling for completed contribution and cancelled contribution
 384     //insertion of new records
 385     $sql = "
 386 INSERT INTO civicrm_financial_trxn
 387             (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
 388             to_financial_account_id, from_financial_account_id, trxn_date)
 389 SELECT   tempI.contribution_id, tempI.payment_instrument_id, tempI.currency, tempI.total_amount, tempI.net_amount,
 390          tempI.fee_amount,    tempI.trxn_id,   tempI.contribution_status_id, tempI.check_number,
 391          tempI.to_financial_account_id, tempI.from_financial_account_id, tempI.trxn_date
 392 FROM {$tempTableName2} tempI
 393 WHERE tempI.action = 'insert';";
 394     CRM_Core_DAO::executeQuery($sql);
 395
 396     //update of existing records
 397     $sql = "
 398 UPDATE civicrm_financial_trxn ft
 399        INNER JOIN {$tempTableName2} tempU
 400                ON (tempU.action != 'insert' AND ft.id = tempU.action)
 401 SET   ft.from_financial_account_id  = NULL,
 402       ft.to_financial_account_id    = tempU.to_financial_account_id,
 403       ft.status_id                  = tempU.contribution_status_id,
 404       ft.payment_instrument_id      = tempU.payment_instrument_id,
 405       ft.check_number               = tempU.check_number,
 406       ft.contribution_id            = tempU.contribution_id;";
 407     CRM_Core_DAO::executeQuery($sql);
 408
 409     //insert the -ve transaction rows for cancelled contributions
 410     $sql = "
 411 INSERT INTO civicrm_financial_trxn
 412             (contribution_id, payment_instrument_id, currency, total_amount,     net_amount, fee_amount, trxn_id, status_id,
 413             check_number,    to_financial_account_id, from_financial_account_id, trxn_date)
 414 SELECT ft.contribution_id, ft.payment_instrument_id, ft.currency, -ft.total_amount, ft.net_amount, ft.fee_amount, ft.trxn_id,
 415        ft.status_id, ft.check_number, ft.to_financial_account_id, ft.from_financial_account_id, ft.trxn_date
 416 FROM   civicrm_financial_trxn ft
 417 WHERE  ft.status_id = {$cancelledStatus};";
 418     CRM_Core_DAO::executeQuery($sql);
 419
 420     //inserting entity financial trxn entries if its not present in entity_financial_trxn for completed and pending contribution statuses
 421     //this also handles +ve and -ve both transaction entries for a cancelled contribution
 422     $sql = "
 423 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
 424 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount as amount
 425 FROM   civicrm_financial_trxn ft
 426 WHERE  contribution_id IS NOT NULL AND
 427        ft.id NOT IN (SELECT financial_trxn_id
 428                      FROM civicrm_entity_financial_trxn
 429                      WHERE entity_table = 'civicrm_contribution'
 430                      AND entity_id      = ft.contribution_id)";
 431     CRM_Core_DAO::executeQuery($sql);
 432     //end of adding financial_trxn records and entity_financial_trxn records related to contribution
 433
 434     //update all linked line_item rows
 435     // set line_item.financial_type_id = contribution.financial_type_id if contribution page id is null and not participant line item
 436     // set line_item.financial_type_id = price_field_value.financial_type_id if contribution page id is set and not participant line item
 437     // set line_item.financial_type_id = event.financial_type_id if its participant line item and line_item.price_field_value_id is null
 438     // 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
 439     $updateLineItemSql = "
 440 UPDATE civicrm_line_item li
 441        LEFT JOIN civicrm_contribution con
 442               ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
 443        LEFT JOIN civicrm_price_field_value cpfv
 444               ON li.price_field_value_id = cpfv.id
 445        LEFT JOIN civicrm_participant cp
 446               ON (li.entity_id = cp.id AND li.entity_table = 'civicrm_participant')
 447        LEFT JOIN civicrm_event ce
 448               ON ce.id = cp.event_id
 449 SET    li.financial_type_id = CASE
 450          WHEN (con.contribution_page_id IS NULL || li.price_field_value_id IS NULL) AND cp.id IS NULL THEN
 451            con.financial_type_id
 452          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
 453            cpfv.financial_type_id
 454          WHEN cp.id IS NOT NULL AND  li.price_field_value_id IS NULL THEN
 455            ce.financial_type_id
 456        END";
 457     CRM_Core_DAO::executeQuery($updateLineItemSql, $queryParams);
 458
 459     //add the financial_item entries
 460     //add a temp column so that inserting entity_financial_trxn entries gets easy
 461     $sql = "ALTER TABLE civicrm_financial_item ADD COLUMN f_trxn_id INT DEFAULT NULL";
 462     CRM_Core_DAO::executeQuery($sql);
 463
 464     //add financial_item entries for contribution  completed / pending pay later / cancelled
 465     $contributionlineItemSql = "
 466 INSERT INTO civicrm_financial_item
 467            (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 468
 469 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
 470        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',
 471        li.id as line_item_id, li.label as line_item_label,
 472        IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id, efa.financial_account_id as financial_account_id,
 473        ft.id as f_trxn_id
 474 FROM  civicrm_line_item li
 475       INNER JOIN civicrm_contribution con
 476               ON (li.entity_id = con.id AND li.entity_table = 'civicrm_contribution')
 477       INNER JOIN civicrm_financial_trxn ft
 478               ON (con.id = ft.contribution_id)
 479       LEFT  JOIN civicrm_entity_financial_account efa
 480               ON (li.financial_type_id = efa.entity_id AND efa.entity_table = 'civicrm_financial_type'
 481                   AND efa.account_relationship = {$incomeAccountIs})
 482 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
 483     CRM_Core_DAO::executeQuery($contributionlineItemSql, $queryParams);
 484
 485     //add financial_item entries for event
 486     $participantLineItemSql = "
 487 INSERT INTO civicrm_financial_item
 488            (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 489
 490 SELECT REPLACE(REPLACE(REPLACE(ft.trxn_date, '-', ''), ':', ''), ' ', ''), con.contact_id,
 491        IF(ft.total_amount < 0 AND con.contribution_status_id = %3, -li.line_total, li.line_total) as line_total,
 492          con.currency, 'civicrm_line_item', li.id as line_item_id, li.label as line_item_label,
 493        IF(con.contribution_status_id = {$pendingStatus}, {$unpaidStatus}, {$paidStatus}) as status_id,
 494           efa.financial_account_id as financial_account_id, ft.id as f_trxn_id
 495 FROM  civicrm_line_item li
 496       INNER JOIN civicrm_participant par
 497               ON (li.entity_id = par.id AND li.entity_table = 'civicrm_participant')
 498       INNER JOIN civicrm_participant_payment pp
 499               ON (pp.participant_id = par.id)
 500       INNER JOIN civicrm_contribution con
 501               ON (pp.contribution_id = con.id)
 502       INNER JOIN civicrm_financial_trxn ft
 503               ON (con.id = ft.contribution_id)
 504       LEFT  JOIN civicrm_entity_financial_account efa
 505               ON (li.financial_type_id = efa.entity_id AND
 506                   efa.entity_table = 'civicrm_financial_type' AND efa.account_relationship = {$incomeAccountIs})
 507 WHERE con.contribution_status_id IN (%1, %3) OR (con.is_pay_later = 1 AND con.contribution_status_id = %2)";
 508     CRM_Core_DAO::executeQuery($participantLineItemSql, $queryParams);
 509
 510     //fee handling for contributions
 511     //insert fee entries in financial_trxn for contributions
 512     $sql = "ALTER TABLE civicrm_financial_trxn ADD COLUMN is_fee TINYINT DEFAULT NULL";
 513     CRM_Core_DAO::executeQuery($sql);
 514
 515     $sql = "
 516 INSERT INTO civicrm_financial_trxn
 517             (contribution_id, payment_instrument_id, currency, total_amount, net_amount, fee_amount, trxn_id, status_id, check_number,
 518              to_financial_account_id, from_financial_account_id, trxn_date, payment_processor_id, is_fee)
 519
 520 SELECT con.id, ft.payment_instrument_id, ft.currency, ft.fee_amount, NULL, NULL, ft.trxn_id, %1 as status_id,
 521        ft.check_number, efaFT.financial_account_id as to_financial_account_id, CASE
 522          WHEN efaPP.financial_account_id IS NOT NULL THEN
 523               efaPP.financial_account_id
 524          WHEN tpi.financial_account_id   IS NOT NULL THEN
 525               tpi.financial_account_id
 526          ELSE
 527               {$financialAccountId}
 528        END    as from_financial_account_id, ft.trxn_date, ft.payment_processor_id, 1 as is_fee
 529 FROM   civicrm_contribution con
 530        INNER JOIN civicrm_financial_trxn ft
 531                ON (ft.contribution_id = con.id)
 532        LEFT  JOIN civicrm_entity_financial_account efaFT
 533                ON (con.financial_type_id = efaFT.entity_id AND efaFT.entity_table = 'civicrm_financial_type'
 534                    AND efaFT.account_relationship = {$expenseAccountIs})
 535        LEFT  JOIN civicrm_entity_financial_account efaPP
 536                ON (ft.payment_processor_id = efaPP.entity_id AND efaPP.entity_table = 'civicrm_payment_processor'
 537                    AND efaPP.account_relationship = {$assetAccountIs})
 538        LEFT  JOIN {$tempTableName1} tpi
 539                ON ft.payment_instrument_id = tpi.instrument_id
 540 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))
 541 GROUP  BY con.id";
 542     CRM_Core_DAO::executeQuery($sql, $queryParams);
 543
 544     //link financial_trxn to contribution
 545     $sql = "
 546 INSERT INTO civicrm_entity_financial_trxn
 547             (entity_table, entity_id, financial_trxn_id, amount)
 548 SELECT 'civicrm_contribution', ft.contribution_id, ft.id, ft.total_amount
 549 FROM   civicrm_financial_trxn ft
 550 WHERE ft.is_fee = 1";
 551     CRM_Core_DAO::executeQuery($sql);
 552
 553     //add fee related entries to financial item table
 554     $domainId = CRM_Core_Config::domainID();
 555     $domainContactId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Domain', $domainId, 'contact_id');
 556     $sql = "
 557 INSERT INTO civicrm_financial_item
 558             (transaction_date, contact_id, amount, currency, entity_table, entity_id, description, status_id, financial_account_id, f_trxn_id)
 559 SELECT ft.trxn_date, {$domainContactId} as contact_id, ft.total_amount, ft.currency, 'civicrm_financial_trxn', ft.id,
 560        'Fee', {$paidStatus} as status_id, ft.to_financial_account_id as financial_account_id, ft.id as f_trxn_id
 561 FROM   civicrm_financial_trxn ft
 562 WHERE  ft.is_fee = 1;";
 563     CRM_Core_DAO::executeQuery($sql);
 564
 565     //add entries to entity_financial_trxn table
 566     $sql = "
 567 INSERT INTO civicrm_entity_financial_trxn (entity_table, entity_id, financial_trxn_id, amount)
 568 SELECT 'civicrm_financial_item' as entity_table, fi.id as entity_id, fi.f_trxn_id as financial_trxn_id, fi.amount
 569 FROM   civicrm_financial_item fi";
 570     CRM_Core_DAO::executeQuery($sql);
 571
 572     //drop the temparory columns
 573     $sql = "ALTER TABLE civicrm_financial_trxn
 574                   DROP COLUMN contribution_id,
 575                   DROP COLUMN  is_fee;";
 576     CRM_Core_DAO::executeQuery($sql);
 577
 578     $sql = "ALTER TABLE civicrm_financial_item DROP f_trxn_id";
 579     CRM_Core_DAO::executeQuery($sql);
 580
 581     return TRUE;
 582   }
 583
 584   function createDomainContacts() {
 585     $domainParams = $context = array();
 586     $query = "
 587 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',
 588  ADD CONSTRAINT `FK_civicrm_domain_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`);";
 589     CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 590
 591     $query = 'SELECT cd.id, cd.name, ce.email FROM `civicrm_domain` cd
 592 LEFT JOIN civicrm_loc_block clb ON clb.id = cd. loc_block_id
 593 LEFT JOIN civicrm_email ce ON ce.id = clb.email_id ;' ;
 594     $dao = CRM_Core_DAO::executeQuery($query);
 595     while($dao->fetch()) {
 596       $params = array(
 597         'sort_name' => $dao->name,
 598         'display_name' => $dao->name,
 599         'legal_name' => $dao->name,
 600         'organization_name' => $dao->name,
 601         'contact_type' => 'Organization'
 602       );
 603       $query = "SELECT cc.id FROM `civicrm_contact` cc
 604 LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id
 605 WHERE cc.contact_type = 'Organization' AND cc.organization_name = '{$dao->name}' ";
 606       if ($dao->email) {
 607         $query .= " AND ce.email = '{$dao->email}' ";
 608       }
 609       $contactID = CRM_Core_DAO::singleValueQuery($query);
 610       $context[1] = $dao->name;
 611       if (empty($contactID)) {
 612         $contact = CRM_Contact_BAO_Contact::add($params);
 613         $contactID = $contact->id;
 614         $context[0] = 'added';
 615       }
 616       else {
 617         $context[0] = 'merged';
 618       }
 619       $domainParams['contact_id'] = $contactID;
 620       CRM_Core_BAO_Domain::edit($domainParams, $dao->id);
 621     }
 622     return $context;
 623   }
 624
 625   function task_4_3_alpha1_checkDBConstraints() {
 626     //checking whether the foreign key exists before dropping it CRM-11260
 627     $config = CRM_Core_Config::singleton();
 628     $dbUf = DB::parseDSN($config->dsn);
 629     $params = array();
 630     $tables = array(
 631       'autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'),
 632       'to_account_id' =>  array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'),
 633       'from_account_id' => array('tableName' =>  'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'),
 634       'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'),
 635     );
 636     $query = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 637 WHERE table_name = 'civicrm_contribution_recur'
 638 AND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'
 639 AND TABLE_SCHEMA = '{$dbUf['database']}'";
 640
 641     $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 642     foreach($tables as $columnName => $value){
 643       if ($value['tableName'] == 'civicrm_membership_type' || $value['tableName'] == 'civicrm_contribution_recur') {
 644         $foreignKeyExists = CRM_Core_DAO::checkConstraintExists($value['tableName'], $value['fkey']);
 645         $fKey = $value['fkey'];
 646       } else {
 647         $foreignKeyExists = CRM_Core_DAO::checkFKConstraintInFormat($value['tableName'], $columnName);
 648         $fKey = "`FK_{$value['tableName']}_{$columnName}`";
 649       }
 650       if ($foreignKeyExists || $value['tableName'] == 'civicrm_financial_trxn') {
 651         if ($value['tableName'] != 'civicrm_contribution_recur' || ($value['tableName'] == 'civicrm_contribution_recur' && $dao->N)) {
 652           $constraintName  = $foreignKeyExists ? $fKey : $value['constraintName'];
 653           CRM_Core_DAO::executeQuery("ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}", $params, TRUE, NULL, FALSE, FALSE);
 654         }
 655         CRM_Core_DAO::executeQuery("ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
 656       }
 657     }
 658     // check if column contact_id is present or not in civicrm_financial_account
 659     $fieldExists = CRM_Core_DAO::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
 660     if (!$fieldExists) {
 661       $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);";
 662       CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 663     }
 664   }
 665
 666   /**
 667    * Read creation and modification times from civicrm_log; add
 668    * them to civicrm_contact.
 669    */
 670   function convertTimestamps(CRM_Queue_TaskContext $ctx, $startId, $endId) {
 671     $sql = "
 672       SELECT entity_id, min(modified_date) AS created, max(modified_date) AS modified
 673       FROM civicrm_log
 674       WHERE entity_table = 'civicrm_contact'
 675       AND entity_id BETWEEN %1 AND %2
 676       GROUP BY entity_id
 677     ";
 678     $params = array(
 679       1 => array($startId, 'Integer'),
 680       2 => array($endId, 'Integer'),
 681     );
 682     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 683     while ($dao->fetch()) {
 684       // FIXME civicrm_log.modified_date is DATETIME; civicrm_contact.modified_date is TIMESTAMP
 685       CRM_Core_DAO::executeQuery(
 686         'UPDATE civicrm_contact SET created_date = %1, modified_date = %2 WHERE id = %3',
 687         array(
 688           1 => array($dao->created, 'String'),
 689           2 => array($dao->modified, 'String'),
 690           3 => array($dao->entity_id, 'Integer'),
 691         )
 692       );
 693     }
 694
 695     return TRUE;
 696   }
 697
 698   /**
 699    * replace contribution_type to financial_type in table
 700    * civicrm_saved_search and Structure civicrm_report_instance
 701    */
 702   function replaceContributionTypeId(CRM_Queue_TaskContext $ctx, $query, $table) {
 703     $dao = CRM_Core_DAO::executeQuery($query);
 704     while ($dao->fetch()) {
 705       $formValues = unserialize($dao->form_values);
 706       foreach (array('contribution_type_id_op', 'contribution_type_id_value', 'contribution_type_id') as $value) {
 707         if (array_key_exists($value, $formValues)) {
 708           $key = preg_replace('/contribution/', 'financial', $value);
 709           $formValues[$key] = $formValues[$value];
 710           unset($formValues[$value]);
 711         }
 712       }
 713       if ($table != 'savedSearch') {
 714         foreach (array('fields', 'group_bys') as $value) {
 715           if (array_key_exists($value, $formValues)) {
 716             if (array_key_exists('contribution_type_id', $formValues[$value])) {
 717               $formValues[$value]['financial_type_id'] = $formValues[$value]['contribution_type_id'];
 718               unset($formValues[$value]['contribution_type_id']);
 719             }
 720             else if (array_key_exists('contribution_type', $formValues[$value])) {
 721               $formValues[$value]['financial_type'] = $formValues[$value]['contribution_type'];
 722               unset($formValues[$value]['contribution_type']);
 723             }
 724           }
 725         }
 726         if (array_key_exists('order_bys', $formValues)) {
 727           foreach ($formValues['order_bys'] as $key => $values) {
 728             if (preg_grep('/contribution_type/', $values)) {
 729               $formValues['order_bys'][$key]['column'] = preg_replace('/contribution_type/', 'financial_type', $values['column']);
 730             }
 731           }
 732         }
 733       }
 734
 735       if ($table == 'savedSearch') {
 736         $saveDao = new CRM_Contact_DAO_SavedSearch();
 737       }
 738       else {
 739         $saveDao = new CRM_Report_DAO_Instance();
 740       }
 741       $saveDao->id = $dao->id;
 742
 743       if ($table == 'savedSearch') {
 744         if (array_key_exists('mapper', $formValues)) {
 745           foreach ($formValues['mapper'] as $key => $values) {
 746             foreach ($values as $k => $v) {
 747               if (preg_grep('/contribution_/', $v)) {
 748                 $formValues['mapper'][$key][$k] = preg_replace('/contribution_type/', 'financial_type', $v);
 749               }
 750             }
 751           }
 752         }
 753         foreach (array('select_tables', 'where_tables') as $value) {
 754           if (preg_match('/contribution_type/', $dao->$value)) {
 755             $tempValue = unserialize($dao->$value);
 756             if (array_key_exists('civicrm_contribution_type', $tempValue)) {
 757               $tempValue['civicrm_financial_type'] = $tempValue['civicrm_contribution_type'];
 758               unset($tempValue['civicrm_contribution_type']);
 759             }
 760             $saveDao->$value = serialize($tempValue);
 761           }
 762         }
 763         if (preg_match('/contribution_type/', $dao->where_clause)) {
 764           $saveDao->where_clause = preg_replace('/contribution_type/', 'financial_type', $dao->where_clause);
 765         }
 766       }
 767       $saveDao->form_values = serialize($formValues);
 768
 769       $saveDao->save();
 770     }
 771     return TRUE;
 772   }
 773
 774   /**
 775    * Update phones CRM-11292
 776    *
 777    * @return bool TRUE for success
 778    */
 779   static function phoneNumeric(CRM_Queue_TaskContext $ctx) {
 780     CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::DROP_STRIP_FUNCTION_43);
 781     CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::CREATE_STRIP_FUNCTION_43);
 782     CRM_Core_DAO::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
 783     return TRUE;
 784   }
 785
 786   /**
 787    * (Queue Task Callback)
 788    */
 789   static function task_4_3_x_runSql(CRM_Queue_TaskContext $ctx, $rev) {
 790     $upgrade = new CRM_Upgrade_Form();
 791     $upgrade->processSQL($rev);
 792
 793     return TRUE;
 794   }
 795
 796   /**
 797    * Syntatic sugar for adding a task which (a) is in this class and (b) has
 798    * a high priority.
 799    *
 800    * After passing the $funcName, you can also pass parameters that will go to
 801    * the function. Note that all params must be serializable.
 802    */
 803   protected function addTask($title, $funcName) {
 804     $queue = CRM_Queue_Service::singleton()->load(array(
 805       'type' => 'Sql',
 806       'name' => CRM_Upgrade_Form::QUEUE_NAME,
 807     ));
 808
 809     $args = func_get_args();
 810     $title = array_shift($args);
 811     $funcName = array_shift($args);
 812     $task = new CRM_Queue_Task(
 813       array(get_class($this), $funcName),
 814       $args,
 815       $title
 816     );
 817     $queue->createItem($task, array('weight' => -1));
 818   }
 819 }