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