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