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