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