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