CRM/Upgrade/Incremental/php/FourThree.php

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