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