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