CRM/Upgrade/Incremental/php/FourTwo.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_FourTwo {
  36   const BATCH_SIZE = 5000;
  37   const SETTINGS_SNIPPET_PATTERN = '/CRM_Core_ClassLoader::singleton\(\)-\>register/';
  38   const SETTINGS_SNIPPET = "\nrequire_once 'CRM/Core/ClassLoader.php';\nCRM_Core_ClassLoader::singleton()->register();\n";
  39
  40   function verifyPreDBstate(&$errors) {
  41     return TRUE;
  42   }
  43
  44   /**
  45    * Compute any messages which should be displayed beforeupgrade
  46    *
  47    * Note: This function is called iteratively for each upcoming
  48    * revision to the database.
  49    *
  50    * @param $postUpgradeMessage string, alterable
  51    * @param $rev string, a version number, e.g. '4.2.alpha1', '4.2.beta3', '4.2.0'
  52    * @return void
  53    */
  54   function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
  55     if ($rev == '4.2.alpha1') {
  56       $tables = array('civicrm_contribution_page','civicrm_event','civicrm_group','civicrm_contact');
  57       if (!CRM_Core_DAO::schemaRequiresRebuilding($tables)){
  58         $errors = ts("The upgrade has identified some schema integrity issues in the database. It seems some of your constraints are missing. You will have to rebuild your schema before re-trying the upgrade. Please refer to %1.", array(1 => CRM_Utils_System::docURL2("Ensuring Schema Integrity on Upgrades", FALSE, "Ensuring Schema Integrity on Upgrades", NULL, NULL, "wiki")));
  59         CRM_Core_Error::fatal($errors);
  60         return FALSE;
  61       }
  62
  63       // CRM-10613, CRM-11120
  64       $query = "
  65 SELECT mp.contribution_id, mp.membership_id, mem.membership_type_id, mem.start_date, mem.end_date, mem.status_id, mem.contact_id
  66 FROM civicrm_membership_payment mp
  67 INNER JOIN ( SELECT cmp.contribution_id
  68               FROM civicrm_membership_payment cmp
  69               LEFT JOIN civicrm_line_item cli ON cmp.contribution_id=cli.entity_id and cli.entity_table = 'civicrm_contribution'
  70               WHERE cli.entity_id IS NULL
  71               GROUP BY cmp.contribution_id
  72               HAVING COUNT(cmp.membership_id) > 1) submp ON submp.contribution_id = mp.contribution_id
  73 INNER JOIN civicrm_membership mem ON mem.id = mp.membership_id
  74 ORDER BY mp.contribution_id, mp.membership_id";
  75       $invalidData = CRM_Core_DAO::executeQuery($query);
  76       if ($invalidData->N) {
  77         $invalidDataMessage = "<br /><strong>" . ts('The upgrade is being aborted due to data integrity issues in your database. There are multiple membership records linked to the same contribution record. This is unexpected, and some of the membership records may be duplicates. The problem record sets are listed below. Refer to <a href="%1">this wiki page for instructions on repairing your database</a> so that you can run the upgrade successfully.
  78         ', array( 1 => 'http://wiki.civicrm.org/confluence/display/CRMDOC42/Repair+database+script+for+4.2+upgrades')) . "</strong>";
  79         $membershipType = CRM_Member_PseudoConstant::membershipType();
  80         $membershipStatus = CRM_Member_PseudoConstant::membershipStatus();
  81         $invalidDataMessage .= "<table border=1><tr><th>Contact-ID</th><th>Contribution-ID</th><th>Membership-ID</th><th>Membership Type</th><th>Start Date</th><th>End Date</th><th>Membership Status</th></tr>";
  82         while ($invalidData->fetch()) {
  83           $invalidDataMessage .= "<tr>";
  84           $invalidDataMessage .= "<td>{$invalidData->contact_id}</td>";
  85           $invalidDataMessage .= "<td>{$invalidData->contribution_id}</td>";
  86           $invalidDataMessage .= "<td>{$invalidData->membership_id}</td>";
  87           $invalidDataMessage .= "<td>" . CRM_Utils_Array::value($invalidData->membership_type_id, $membershipType) . "</td>";
  88           $invalidDataMessage .= "<td>{$invalidData->start_date}</td>";
  89           $invalidDataMessage .= "<td>{$invalidData->end_date}</td>";
  90           $invalidDataMessage .= "<td>" . CRM_Utils_Array::value($invalidData->status_id, $membershipStatus) . "</td>";
  91           $invalidDataMessage .= "</tr>";
  92           }
  93         $invalidDataMessage .= "</table><p>" . ts('If you have reviewed the cleanup script documentation on the wiki and you are ready to run the cleanup now - <a href="%1">click here</a>.', array(1 => CRM_Utils_System::url('civicrm/upgrade/cleanup425', 'reset=1'))) . "</p>";
  94         CRM_Core_Error::fatal($invalidDataMessage);
  95         return FALSE;
  96         }
  97       }
  98
  99     if ($rev == '4.2.beta2') {
 100       // note: error conditions are also checked in upgrade_4_2_beta2()
 101       if (!defined('CIVICRM_SETTINGS_PATH')) {
 102         $preUpgradeMessage .= '<br />' . ts('Could not determine path to civicrm.settings.php. Please manually locate it and add these lines at the bottom: <pre>%1</pre>', array(
 103           1 => self::SETTINGS_SNIPPET
 104         ));
 105       } elseif (preg_match(self::SETTINGS_SNIPPET_PATTERN, file_get_contents(CIVICRM_SETTINGS_PATH))) {
 106         // OK, nothing to do
 107       } elseif (!is_writable(CIVICRM_SETTINGS_PATH)) {
 108         $preUpgradeMessage .= '<br />' . ts('The settings file (%1) must be updated. Please make it writable or manually add these lines:<pre>%2</pre>', array(
 109           1 => CIVICRM_SETTINGS_PATH,
 110           2 => self::SETTINGS_SNIPPET
 111         ));
 112       }
 113     }
 114     if ($rev == '4.2.2' && version_compare($currentVer, '3.3.alpha1') >= 0) {
 115       $query = " SELECT cli.id
 116 FROM `civicrm_line_item` cli
 117 INNER JOIN civicrm_membership_payment cmp ON cmp.contribution_id = cli.entity_id AND cli.entity_table = 'civicrm_contribution'
 118 INNER JOIN civicrm_price_field_value cpfv ON cpfv.id = cli.price_field_value_id
 119 INNER JOIN civicrm_price_field cpf ON cpf.id = cpfv.price_field_id and cpf.id != cli.price_field_id
 120 INNER JOIN civicrm_price_set cps ON cps.id = cpf.price_set_id AND cps.name <>'default_membership_type_amount' ";
 121       $dao = CRM_Core_DAO::executeQuery($query);
 122       if ($dao->N) {
 123         $preUpgradeMessage .= "<br /><strong>". ts('We have identified extraneous data in your database that a previous upgrade likely introduced. We STRONGLY recommend making a backup of your site before continuing. We also STRONGLY suggest fixing this issue with unneeded records BEFORE you upgrade. You can find more information about this issue and the way to fix it by visiting <a href="http://forum.civicrm.org/index.php/topic,26181.0.html">http://forum.civicrm.org/index.php/topic,26181.0.html</a>.') ."</strong>";
 124       }
 125     }
 126
 127     if (version_compare($rev, '4.2.9') >= 0) {
 128       //CRM-11980
 129       $sql = "SELECT id FROM civicrm_option_group WHERE name LIKE 'civicrm_price_field.amount.%' LIMIT 1";
 130       $dao = CRM_Core_DAO::executeQuery($sql);
 131       if ($dao->fetch()) {
 132         $errors = ts("We found unexpected data values from an older version of CiviCRM in your database. The upgrade can not be run until this condition is corrected.<br /><br />Details: One or more rows are present in the civicrm_option_group with name like 'civicrm_price_field.amount.%'. <a href='%1'>Check here for information on diagnosing and correcting this problem.</a>", array(1 => 'http://forum.civicrm.org/index.php/topic,27744.msg118748.html#msg118748'));
 133         CRM_Core_Error::fatal($errors);
 134         return FALSE;
 135   }
 136     }
 137   }
 138
 139   /**
 140    * Compute any messages which should be displayed after upgrade
 141    *
 142    * @param $postUpgradeMessage string, alterable
 143    * @param $rev string, an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs
 144    * @return void
 145    */
 146   function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
 147     if ($rev == '4.2.beta5') {
 148       $config = CRM_Core_Config::singleton();
 149       if (!empty($config->extensionsDir)) {
 150         $postUpgradeMessage .= '<br />' . ts('Please <a href="%1" target="_blank">configure the Extension Resource URL</a>.', array(
 151           1 => CRM_Utils_system::url('civicrm/admin/setting/url', 'reset=1')
 152         ));
 153   }
 154     }
 155     if ($rev == '4.2.7') {
 156       $postUpgradeMessage .= '<br />' . ts('If you have configured a report instance to allow anonymous access, you will need to reset the permission to Everyone for that instance (under the Report Settings pane).');
 157   }
 158   }
 159
 160   function upgrade_4_2_alpha1($rev) {
 161     //checking whether the foreign key exists before dropping it
 162     //drop foreign key queries of CRM-9850
 163     $params = array();
 164     $tables = array('civicrm_contribution_page' =>'FK_civicrm_contribution_page_payment_processor_id',
 165                     'civicrm_event' => 'FK_civicrm_event_payment_processor_id',
 166                     'civicrm_group' => 'FK_civicrm_group_saved_search_id',
 167                     );
 168     foreach($tables as $tableName => $fKey){
 169       $foreignKeyExists = CRM_Core_DAO::checkConstraintExists($tableName,$fKey);
 170       if ($foreignKeyExists){
 171         CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP FOREIGN KEY {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
 172         CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
 173       }
 174     }
 175     // Drop index UI_title for civicrm_price_set
 176     $domain = new CRM_Core_DAO_Domain;
 177     $domain->find(TRUE);
 178     if ($domain->locales) {
 179       $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 180       foreach ($locales as $locale) {
 181         $query = "SHOW KEYS FROM `civicrm_price_set` WHERE key_name = 'UI_title_{$locale}'";
 182         $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 183         if ($dao->N) {
 184           CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_price_set` DROP INDEX `UI_title_{$locale}`", $params, TRUE, NULL, FALSE, FALSE);
 185         }
 186       }
 187     } else {
 188       $query = "SHOW KEYS FROM `civicrm_price_set` WHERE key_name = 'UI_title'";
 189       $dao = CRM_Core_DAO::executeQuery($query);
 190       if ($dao->N) {
 191         CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_price_set` DROP INDEX `UI_title`");
 192       }
 193     }
 194
 195     // Some steps take a long time, so we break them up into separate
 196     // tasks and enqueue them separately.
 197     $this->addTask(ts('Upgrade DB to 4.2.alpha1: SQL'), 'task_4_2_x_runSql', $rev);
 198     $this->addTask(ts('Upgrade DB to 4.2.alpha1: Price Sets'), 'task_4_2_alpha1_createPriceSets', $rev);
 199     self::convertContribution();
 200     $this->addTask(ts('Upgrade DB to 4.2.alpha1: Event Profile'), 'task_4_2_alpha1_eventProfile');
 201   }
 202
 203   function upgrade_4_2_beta2($rev) {
 204     // note: error conditions are also checked in setPreUpgradeMessage()
 205     if (defined('CIVICRM_SETTINGS_PATH')) {
 206       if (!preg_match(self::SETTINGS_SNIPPET_PATTERN, file_get_contents(CIVICRM_SETTINGS_PATH))) {
 207         if (is_writable(CIVICRM_SETTINGS_PATH)) {
 208           file_put_contents(CIVICRM_SETTINGS_PATH, self::SETTINGS_SNIPPET, FILE_APPEND);
 209         }
 210       }
 211     }
 212   }
 213
 214   function upgrade_4_2_beta3($rev) {
 215     $this->addTask(ts('Upgrade DB to 4.2.beta3: SQL'), 'task_4_2_x_runSql', $rev);
 216     $minParticipantId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_participant');
 217     $maxParticipantId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_participant');
 218
 219     for ($startId = $minParticipantId; $startId <= $maxParticipantId; $startId += self::BATCH_SIZE) {
 220       $endId = $startId + self::BATCH_SIZE - 1;
 221       $title = ts('Upgrade DB to 4.2.alpha1: Participant (%1 => %2)', array(1 => $startId, 2 => $endId));
 222       $this->addTask($title, 'task_4_2_alpha1_convertParticipants', $startId, $endId);
 223     }
 224   }
 225
 226   function upgrade_4_2_beta5($rev) {
 227     // CRM-10629 Create a setting for extension URLs
 228     // For some reason, this isn't working when placed in the .sql file
 229     CRM_Core_DAO::executeQuery("
 230       INSERT INTO civicrm_setting(group_name,name,value,domain_id,is_domain)
 231       VALUES ('URL Preferences', 'extensionsURL',NULL,1,1);
 232     ");
 233   }
 234
 235   function upgrade_4_2_0($rev) {
 236     $this->addTask(ts('Upgrade DB to 4.2.0: SQL'), 'task_4_2_x_runSql', $rev);
 237   }
 238
 239   function upgrade_4_2_2($rev) {
 240     $this->addTask(ts('Upgrade DB to 4.2.2: SQL'), 'task_4_2_x_runSql', $rev);
 241     //create line items for memberships and participants for api/import
 242     self::convertContribution();
 243
 244     // CRM-10937 Fix the title on civicrm_dedupe_rule_group
 245     $upgrade = new CRM_Upgrade_Form();
 246     if ($upgrade->multilingual) {
 247       // Check if the 'title' field exists
 248       $query = "SELECT column_name
 249                   FROM information_schema.COLUMNS
 250                  WHERE table_name = 'civicrm_dedupe_rule_group'
 251                    AND table_schema = DATABASE()
 252                    AND column_name = 'title'";
 253
 254       $dao = CRM_Core_DAO::executeQuery($query);
 255
 256       if (!$dao->N) {
 257         $domain = new CRM_Core_DAO_Domain;
 258         $domain->find(TRUE);
 259
 260         if ($domain->locales) {
 261           $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 262           $locale = array_shift($locales);
 263
 264           // Use the first language (they should all have the same value)
 265           CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_dedupe_rule_group` CHANGE `title_{$locale}` `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Label of the rule group'", $params, TRUE, NULL, FALSE, FALSE);
 266
 267           // Drop remaining the column for the remaining languages
 268           foreach ($locales as $locale) {
 269             CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_dedupe_rule_group` DROP `title_{$locale}`", $params, TRUE, NULL, FALSE, FALSE);
 270           }
 271         }
 272       }
 273     }
 274   }
 275
 276   function upgrade_4_2_3($rev) {
 277     $this->addTask(ts('Upgrade DB to 4.2.3: SQL'), 'task_4_2_x_runSql', $rev);
 278     // CRM-10953 Remove duplicate activity type for 'Reminder Sent' which is mistakenly inserted by 4.2.alpha1 upgrade script
 279     $queryMin = "
 280 SELECT coalesce(min(value),0) from civicrm_option_value ov
 281 WHERE ov.option_group_id =
 282   (SELECT id from civicrm_option_group og WHERE og.name = 'activity_type') AND
 283 ov.name = 'Reminder Sent'";
 284
 285     $minReminderSent = CRM_Core_DAO::singleValueQuery($queryMin);
 286
 287     $queryMax = "
 288 SELECT coalesce(max(value),0) from civicrm_option_value ov
 289 WHERE ov.option_group_id =
 290   (SELECT id from civicrm_option_group og WHERE og.name = 'activity_type') AND
 291 ov.name = 'Reminder Sent'";
 292
 293     $maxReminderSent = CRM_Core_DAO::singleValueQuery($queryMax);
 294
 295     // If we have two different values, replace new value with original in any activities
 296     if ($maxReminderSent > $minReminderSent) {
 297       $query = "
 298 UPDATE civicrm_activity
 299 SET activity_type_id = {$minReminderSent}
 300 WHERE activity_type_id = {$maxReminderSent}";
 301
 302       CRM_Core_DAO::executeQuery($query);
 303
 304       // Then delete the newer (duplicate) option_value row
 305       $query = "
 306 DELETE from civicrm_option_value
 307   WHERE option_group_id =
 308     (SELECT id from civicrm_option_group og WHERE og.name = 'activity_type') AND
 309   value = '{$maxReminderSent}'";
 310
 311       CRM_Core_DAO::executeQuery($query);
 312     }
 313   }
 314
 315   function upgrade_4_2_5($rev) {
 316     $this->addTask(ts('Upgrade DB to 4.2.5: SQL'), 'task_4_2_x_runSql', $rev);
 317     //CRM-11077
 318     $sql = " SELECT cpse.entity_id, cpse.price_set_id
 319 FROM `civicrm_price_set_entity` cpse
 320 LEFT JOIN civicrm_price_set cps ON cps.id = cpse.price_set_id
 321 LEFT JOIN civicrm_price_set_entity cpse1 ON cpse1.price_set_id = cpse.price_set_id
 322 WHERE cpse.entity_table = 'civicrm_event'  AND cps.is_quick_config = 1
 323 GROUP BY cpse.id
 324 HAVING COUNT(cpse.price_set_id) > 1 AND MIN(cpse1.id) <> cpse.id ";
 325
 326     $dao = CRM_Core_DAO::executeQuery($sql);
 327     while ($dao->fetch()) {
 328       if ($dao->price_set_id) {
 329         $copyPriceSet = &CRM_Upgrade_Snapshot_V4p2_Price_BAO_Set::copy($dao->price_set_id);
 330         CRM_Upgrade_Snapshot_V4p2_Price_BAO_Set::addTo('civicrm_event', $dao->entity_id, $copyPriceSet->id);
 331       }
 332     }
 333   }
 334
 335   function convertContribution(){
 336     $minContributionId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_contribution');
 337     $maxContributionId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_contribution');
 338     for ($startId = $minContributionId; $startId <= $maxContributionId; $startId += self::BATCH_SIZE) {
 339       $endId = $startId + self::BATCH_SIZE - 1;
 340       $title = ts('Upgrade DB to 4.2.alpha1: Contributions (%1 => %2)', array(1 => $startId, 2 => $endId));
 341       $this->addTask($title, 'task_4_2_alpha1_convertContributions', $startId, $endId);
 342     }
 343     $minParticipantId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(min(id),0) FROM civicrm_participant');
 344     $maxParticipantId = CRM_Core_DAO::singleValueQuery('SELECT coalesce(max(id),0) FROM civicrm_participant');
 345
 346     for ($startId = $minParticipantId; $startId <= $maxParticipantId; $startId += self::BATCH_SIZE) {
 347       $endId = $startId + self::BATCH_SIZE - 1;
 348       $title = ts('Upgrade DB to 4.2.alpha1: Participant (%1 => %2)', array(1 => $startId, 2 => $endId));
 349       $this->addTask($title, 'task_4_2_alpha1_convertParticipants', $startId, $endId);
 350     }
 351   }
 352
 353   /**
 354    * (Queue Task Callback)
 355    *
 356    * Upgrade code to create priceset for contribution pages and events
 357    */
 358   static function task_4_2_alpha1_createPriceSets(CRM_Queue_TaskContext $ctx, $rev) {
 359     $upgrade = new CRM_Upgrade_Form();
 360     $daoName =
 361       array(
 362         'civicrm_contribution_page' =>
 363         array(
 364           'CRM_Contribute_BAO_ContributionPage',
 365           CRM_Core_Component::getComponentID('CiviContribute')
 366         ),
 367         'civicrm_event' =>
 368         array(
 369           'CRM_Event_BAO_Event',
 370           CRM_Core_Component::getComponentID('CiviEvent')
 371         ),
 372       );
 373
 374     // get all option group used for event and contribution page
 375     $query = "
 376 SELECT id, name
 377 FROM   civicrm_option_group
 378 WHERE  name LIKE '%.amount.%' ";
 379     $dao = CRM_Core_DAO::executeQuery($query);
 380     while ($dao->fetch()) {
 381       $addTo = explode('.', $dao->name);
 382       if (CRM_Utils_Array::value(2, $addTo)) {
 383         $options = array ('optionGroup' => $dao->name);
 384         self::createPriceSet($daoName, $addTo, $options);
 385       }
 386       CRM_Core_OptionGroup::deleteAssoc($dao->name);
 387     }
 388
 389     //create pricesets for contribution with only other amount
 390     $query = "
 391 SELECT    ccp.id as contribution_page_id, ccp.is_allow_other_amount, cmb.id as membership_block_id
 392 FROM      civicrm_contribution_page ccp
 393 LEFT JOIN civicrm_membership_block cmb ON  cmb.entity_id = ccp.id AND cmb.entity_table = 'civicrm_contribution_page'
 394 LEFT JOIN civicrm_price_set_entity cpse ON cpse.entity_id = ccp.id and cpse.entity_table = 'civicrm_contribution_page'
 395 WHERE     cpse.price_set_id IS NULL";
 396     $dao = CRM_Core_DAO::executeQuery($query);
 397     $addTo = array('civicrm_contribution_page');
 398     while ($dao->fetch()) {
 399       $addTo[2] = $dao->contribution_page_id;
 400       $options = array ('otherAmount' =>$dao->is_allow_other_amount,
 401                       'membership' => $dao->membership_block_id );
 402       self::createPriceSet($daoName, $addTo, $options);
 403     }
 404
 405     return TRUE;
 406   }
 407
 408   /**
 409    * (Queue Task Callback)
 410    */
 411   static function task_4_2_x_runSql(CRM_Queue_TaskContext $ctx, $rev) {
 412       $upgrade = new CRM_Upgrade_Form();
 413       $upgrade->processSQL($rev);
 414
 415       // now rebuild all the triggers
 416       // CRM-9716
 417       // FIXME // CRM_Core_DAO::triggerRebuild();
 418
 419       return TRUE;
 420   }
 421
 422   /**
 423    *
 424    * Function to create price sets
 425    */
 426   static function createPriceSet($daoName, $addTo,  $options = array()) {
 427     $query = "SELECT title FROM {$addTo[0]} where id =%1";
 428     $setParams['title']  = CRM_Core_DAO::singleValueQuery($query,
 429       array(1 => array($addTo[2], 'Integer'))
 430     );
 431     $pageTitle = strtolower(CRM_Utils_String::munge($setParams['title'], '_', 245));
 432
 433     // an event or contrib page has been deleted but left the option group behind - (this may be fixed in later versions?)
 434     // we should probably delete the option group - but at least early exit here as the code following it does not fatal
 435     // CRM-10298
 436     if ( empty($pageTitle)) {
 437       return;
 438     }
 439
 440     $optionValue = array();
 441     if (CRM_Utils_Array::value('optionGroup', $options)) {
 442       CRM_Core_OptionGroup::getAssoc($options['optionGroup'], $optionValue);
 443       if (empty($optionValue))
 444         return;
 445     }
 446     elseif (!CRM_Utils_Array::value('otherAmount', $options) && !CRM_Utils_Array::value('membership', $options)) {
 447       //CRM-12273
 448       //if options group, otherAmount, membersip is empty then return, contribution should be default price set
 449       return;
 450     }
 451
 452     if (! CRM_Core_DAO::getFieldValue('CRM_Upgrade_Snapshot_V4p2_Price_BAO_Set', $pageTitle, 'id', 'name', true)) {
 453       $setParams['name'] = $pageTitle;
 454     }
 455     else {
 456       $timeSec = explode(".", microtime(true));
 457       $setParams['name'] = $pageTitle . '_' . date('is', $timeSec[0]) . $timeSec[1];
 458     }
 459     $setParams['extends'] = $daoName[$addTo[0]][1];
 460     $setParams['is_quick_config'] = 1;
 461     $priceSet = CRM_Upgrade_Snapshot_V4p2_Price_BAO_Set::create($setParams);
 462     CRM_Upgrade_Snapshot_V4p2_Price_BAO_Set::addTo($addTo[0], $addTo[2], $priceSet->id, 1);
 463
 464     $fieldParams['price_set_id'] = $priceSet->id;
 465     if (CRM_Utils_Array::value('optionGroup', $options)) {
 466       $fieldParams['html_type'] = 'Radio';
 467       $fieldParams['is_required'] = 1;
 468       if ($addTo[0] == 'civicrm_event') {
 469         $query = "SELECT fee_label FROM civicrm_event where id =%1";
 470         $fieldParams['name'] = $fieldParams['label'] = CRM_Core_DAO::singleValueQuery($query,
 471           array(1 => array($addTo[2], 'Integer'))
 472         );
 473         $defaultAmountColumn = 'default_fee_id';
 474       }
 475       else {
 476         $options['membership'] = 1;
 477         $fieldParams['name'] = strtolower(CRM_Utils_String::munge("Contribution Amount", '_', 245));
 478         $fieldParams['label'] = "Contribution Amount";
 479         $defaultAmountColumn = 'default_amount_id';
 480         $options['otherAmount'] = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_ContributionPage', $addTo[2], 'is_allow_other_amount');
 481         if (CRM_Utils_Array::value('otherAmount', $options)) {
 482           $fieldParams['is_required'] = 0;
 483         }
 484       }
 485       $fieldParams['option_label'] = $optionValue['label'];
 486       $fieldParams['option_amount'] = $optionValue['value'];
 487       $fieldParams['option_weight'] = $optionValue['weight'];
 488       $fieldParams['is_quick_config'] = $setParams['is_quick_config'];
 489       if ($defaultAmount = CRM_Core_DAO::getFieldValue($daoName[$addTo[0]][0], $addTo[2], $defaultAmountColumn)) {
 490         $fieldParams['default_option'] = array_search($defaultAmount, $optionValue['amount_id']);
 491       }
 492       $priceField = CRM_Upgrade_Snapshot_V4p2_Price_BAO_Field::create($fieldParams);
 493
 494     }
 495     if (CRM_Utils_Array::value('membership', $options)) {
 496       $dao               = new CRM_Member_DAO_MembershipBlock();
 497       $dao->entity_table = 'civicrm_contribution_page';
 498       $dao->entity_id    = $addTo[2];
 499
 500       if ($dao->find(TRUE)) {
 501         if ($dao->membership_types) {
 502           $fieldParams = array(
 503             'name'               => strtolower(CRM_Utils_String::munge("Membership Amount", '_', 245)),
 504             'label'              => "Membership Amount",
 505             'is_required'        => $dao->is_required,
 506             'is_display_amounts' => $dao->display_min_fee,
 507             'is_active'          => $dao->is_active,
 508             'price_set_id'       => $priceSet->id,
 509             'html_type'          => 'Radio',
 510             'weight'             => 1,
 511           );
 512           $membershipTypes = unserialize($dao->membership_types);
 513           $rowcount = 0;
 514           foreach ($membershipTypes as $membershipType => $autoRenew) {
 515             $membershipTypeDetail = CRM_Member_BAO_MembershipType::getMembershipTypeDetails($membershipType);
 516             $rowcount++;
 517             $fieldParams['option_label'][$rowcount]  = $membershipTypeDetail['name'];
 518             $fieldParams['option_amount'][$rowcount] = $membershipTypeDetail['minimum_fee'];
 519             $fieldParams['option_weight'][$rowcount] = $rowcount;
 520             $fieldParams['membership_type_id'][$rowcount] = $membershipType;
 521             if ($membershipType == $dao->membership_type_default) {
 522               $fieldParams['default_option'] = $rowcount;
 523             }
 524           }
 525           $priceField = CRM_Upgrade_Snapshot_V4p2_Price_BAO_Field::create($fieldParams);
 526
 527           $setParams = array(
 528             'id'                   => $priceSet->id,
 529             'extends'              => CRM_Core_Component::getComponentID('CiviMember'),
 530             'contribution_type_id' => CRM_Core_DAO::getFieldValue($daoName[$addTo[0]][0], $addTo[2], 'contribution_type_id'),
 531           );
 532           CRM_Upgrade_Snapshot_V4p2_Price_BAO_Set::create($setParams);
 533         }
 534       }
 535     }
 536     if (CRM_Utils_Array::value('otherAmount', $options)) {
 537
 538       $fieldParams = array(
 539         'name'               => strtolower(CRM_Utils_String::munge("Other Amount", '_', 245)),
 540         'label'              => "Other Amount",
 541         'is_required'        => 0,
 542         'is_display_amounts' => 0,
 543         'is_active'          => 1,
 544         'price_set_id'       => $priceSet->id,
 545         'html_type'          => 'Text',
 546         'weight'             => 3,
 547       );
 548       $fieldParams['option_label'][1]  = "Other Amount";
 549       $fieldParams['option_amount'][1] = 1;
 550       $fieldParams['option_weight'][1] = 1;
 551       $priceField = CRM_Upgrade_Snapshot_V4p2_Price_BAO_Field::create($fieldParams);
 552     }
 553   }
 554
 555   /**
 556    * (Queue Task Callback)
 557    *
 558    * Find any contribution records and create corresponding line-item
 559    * records.
 560    *
 561    * @param $startId int, the first/lowest contribution ID to convert
 562    * @param $endId int, the last/highest contribution ID to convert
 563    */
 564   static function task_4_2_alpha1_convertContributions(CRM_Queue_TaskContext $ctx, $startId, $endId) {
 565     $upgrade = new CRM_Upgrade_Form();
 566     $query = "
 567  INSERT INTO civicrm_line_item(`entity_table` ,`entity_id` ,`price_field_id` ,`label` , `qty` ,`unit_price` ,`line_total` ,`participant_count` ,`price_field_value_id`)
 568  SELECT 'civicrm_contribution',cc.id, cpf.id as price_field_id, cpfv.label, 1, cc.total_amount, cc.total_amount line_total, 0, cpfv.id as price_field_value
 569  FROM civicrm_membership_payment cmp
 570  LEFT JOIN `civicrm_contribution` cc ON cc.id = cmp.contribution_id
 571  LEFT JOIN civicrm_line_item cli ON cc.id=cli.entity_id and cli.entity_table = 'civicrm_contribution'
 572  LEFT JOIN civicrm_membership cm ON cm.id=cmp.membership_id
 573  LEFT JOIN civicrm_membership_type cmt ON cmt.id = cm.membership_type_id
 574  LEFT JOIN civicrm_price_field cpf ON BINARY cpf.name = cmt.member_of_contact_id
 575  LEFT JOIN civicrm_price_field_value cpfv ON cpfv.membership_type_id = cm.membership_type_id AND cpf.id = cpfv.price_field_id
 576  WHERE (cc.id BETWEEN %1 AND %2) AND cli.entity_id IS NULL ;
 577  ";
 578     $sqlParams = array(
 579       1 => array($startId, 'Integer'),
 580       2 => array($endId, 'Integer'),
 581     );
 582     CRM_Core_DAO::executeQuery($query, $sqlParams);
 583
 584     // create lineitems for contribution done for membership
 585     $sql = "
 586 SELECT    cc.id, cmp.membership_id, cpse.price_set_id, cc.total_amount
 587 FROM      civicrm_contribution cc
 588 LEFT JOIN civicrm_line_item cli ON cc.id=cli.entity_id AND cli.entity_table = 'civicrm_contribution'
 589 LEFT JOIN civicrm_membership_payment cmp ON cc.id = cmp.contribution_id
 590 LEFT JOIN civicrm_participant_payment cpp ON cc.id = cpp.contribution_id
 591 LEFT JOIN civicrm_price_set_entity cpse on cpse.entity_table = 'civicrm_contribution_page' AND cpse.entity_id = cc.contribution_page_id
 592 WHERE     (cc.id BETWEEN %1 AND %2)
 593 AND       cli.entity_id IS NULL AND cc.contribution_page_id IS NOT NULL AND cpp.contribution_id IS NULL
 594 GROUP BY  cc.id
 595 ";
 596     $result = CRM_Core_DAO::executeQuery($sql, $sqlParams);
 597
 598     while ($result->fetch()) {
 599       $sql = "
 600 SELECT    cpf.id, cpfv.id as price_field_value_id, cpfv.label, cpfv.amount, cpfv.count
 601 FROM      civicrm_price_field cpf
 602 LEFT JOIN civicrm_price_field_value cpfv ON cpf.id = cpfv.price_field_id
 603 WHERE     cpf.price_set_id = %1
 604 ";
 605       $lineParams = array(
 606         'entity_table' => 'civicrm_contribution',
 607         'entity_id' => $result->id,
 608       );
 609       if ($result->membership_id) {
 610         $sql .= " AND cpf.name = %2 AND cpfv.membership_type_id = %3 ";
 611         $params = array(
 612           '1' => array($result->price_set_id, 'Integer'),
 613           '2' => array('membership_amount', 'String'),
 614           '3' => array(CRM_Core_DAO::getFieldValue('CRM_Member_DAO_Membership', $result->membership_id, 'membership_type_id'), 'Integer'),
 615         );
 616         $res = CRM_Core_DAO::executeQuery($sql, $params);
 617         if ($res->fetch()) {
 618           $lineParams += array(
 619             'price_field_id' => $res->id,
 620             'label' => $res->label,
 621             'qty' => 1,
 622             'unit_price' => $res->amount,
 623             'line_total' => $res->amount,
 624             'participant_count' => $res->count ? $res->count : 0,
 625             'price_field_value_id' => $res->price_field_value_id,
 626           );
 627         }
 628         else {
 629           $lineParams['price_field_id'] = CRM_Core_DAO::getFieldValue('CRM_Upgrade_Snapshot_V4p2_Price_DAO_Field', $result->price_set_id, 'id', 'price_set_id');
 630           $lineParams['label'] = 'Membership Amount';
 631           $lineParams['qty'] = 1;
 632           $lineParams['unit_price'] = $lineParams['line_total'] = $result->total_amount;
 633           $lineParams['participant_count'] = 0;
 634         }
 635       }
 636       else {
 637         $sql .= "AND cpfv.amount = %2";
 638
 639         //CRM-12273
 640         //check if price_set_id is exist, if not use the default contribution amount
 641         if (isset($result->price_set_id)){
 642           $priceSetId = $result->price_set_id;
 643         }
 644         else{
 645           $defaultPriceSets = CRM_Price_BAO_PriceSet::getDefaultPriceSet();
 646           foreach ($defaultPriceSets as $key => $pSet) {
 647             if ($pSet['name'] == 'contribution_amount'){
 648               $priceSetId = $pSet['setID'];
 649             }
 650           }
 651         }
 652
 653         $params = array(
 654           '1' => array($priceSetId, 'Integer'),
 655           '2' => array($result->total_amount, 'String'),
 656         );
 657         $res = CRM_Core_DAO::executeQuery($sql, $params);
 658         if ($res->fetch()) {
 659           $lineParams += array(
 660             'price_field_id' => $res->id,
 661             'label' => $res->label,
 662             'qty' => 1,
 663             'unit_price' => $res->amount,
 664             'line_total' => $res->amount,
 665             'participant_count' => $res->count ? $res->count : 0,
 666             'price_field_value_id' => $res->price_field_value_id,
 667           );
 668         }
 669         else {
 670           $params = array(
 671             'price_set_id' => $priceSetId,
 672             'name' => 'other_amount',
 673           );
 674           $defaults = array();
 675           CRM_Upgrade_Snapshot_V4p2_Price_BAO_Field::retrieve($params, $defaults);
 676           if (!empty($defaults)) {
 677             $lineParams['price_field_id'] = $defaults['id'];
 678             $lineParams['label'] = $defaults['label'];
 679             $lineParams['price_field_value_id'] =
 680               CRM_Core_DAO::getFieldValue('CRM_Upgrade_Snapshot_V4p2_Price_DAO_FieldValue', $defaults['id'], 'id', 'price_field_id');
 681           }
 682           else {
 683             $lineParams['price_field_id'] =
 684               CRM_Core_DAO::getFieldValue('CRM_Upgrade_Snapshot_V4p2_Price_DAO_Field', $priceSetId, 'id', 'price_set_id');
 685             $lineParams['label'] = 'Contribution Amount';
 686           }
 687           $lineParams['qty'] = 1;
 688           $lineParams['participant_count'] = 0;
 689           $lineParams['unit_price'] = $lineParams['line_total'] = $result->total_amount;
 690         }
 691       }
 692       CRM_Upgrade_Snapshot_V4p2_Price_BAO_LineItem::create($lineParams);
 693     }
 694
 695     return TRUE;
 696   }
 697
 698   /**
 699    * (Queue Task Callback)
 700    *
 701    * Find any participant records and create corresponding line-item
 702    * records.
 703    *
 704    * @param $startId int, the first/lowest participant ID to convert
 705    * @param $endId int, the last/highest participant ID to convert
 706    */
 707   static function task_4_2_alpha1_convertParticipants(CRM_Queue_TaskContext $ctx, $startId, $endId) {
 708     $upgrade = new CRM_Upgrade_Form();
 709     //create lineitems for participant in edge cases using default price set for contribution.
 710     $query = "
 711 SELECT    cp.id as participant_id, cp.fee_amount, cp.fee_level,ce.is_monetary,
 712           cpse.price_set_id, cpf.id as price_field_id, cpfv.id as price_field_value_id
 713 FROM      civicrm_participant cp
 714 LEFT JOIN civicrm_line_item cli ON cli.entity_id=cp.id and cli.entity_table = 'civicrm_participant'
 715 LEFT JOIN civicrm_event ce ON ce.id=cp.event_id
 716 LEFT JOIN civicrm_price_set_entity cpse ON cp.event_id = cpse.entity_id and cpse.entity_table = 'civicrm_event'
 717 LEFT JOIN civicrm_price_field cpf ON cpf.price_set_id = cpse.price_set_id
 718 LEFT JOIN civicrm_price_field_value cpfv ON cpfv.price_field_id = cpf.id AND cpfv.label = cp.fee_level
 719 WHERE     (cp.id BETWEEN %1 AND %2)
 720 AND       cli.entity_id IS NULL AND cp.fee_amount IS NOT NULL";
 721     $sqlParams = array(
 722       1 => array($startId, 'Integer'),
 723       2 => array($endId, 'Integer'),
 724     );
 725     $dao = CRM_Core_DAO::executeQuery($query, $sqlParams);
 726     if ($dao->N) {
 727       $defaultPriceSetId = CRM_Core_DAO::getFieldValue('CRM_Upgrade_Snapshot_V4p2_Price_DAO_Set', 'default_contribution_amount', 'id', 'name');
 728       $priceSets = current(CRM_Upgrade_Snapshot_V4p2_Price_BAO_Set::getSetDetail($defaultPriceSetId));
 729       $fieldID = key($priceSets['fields']);
 730     }
 731
 732     while ($dao->fetch()) {
 733       $lineParams = array(
 734         'entity_table' => 'civicrm_participant',
 735         'entity_id' => $dao->participant_id,
 736         'label' => $dao->fee_level ? $dao->fee_level : ts('Default'),
 737         'qty' => 1,
 738         'unit_price' => $dao->fee_amount,
 739         'line_total' => $dao->fee_amount,
 740         'participant_count' => 1,
 741       );
 742       if ($dao->is_monetary && $dao->price_field_id) {
 743         $lineParams += array(
 744           'price_field_id' => $dao->price_field_id,
 745           'price_field_value_id' => $dao->price_field_value_id,
 746         );
 747         $priceSetId = $dao->price_set_id;
 748       } else {
 749         $lineParams['price_field_id'] = $fieldID;
 750         $priceSetId = $defaultPriceSetId;
 751       }
 752       CRM_Upgrade_Snapshot_V4p2_Price_BAO_LineItem::create($lineParams);
 753     }
 754     return TRUE;
 755   }
 756
 757   /**
 758    * (Queue Task Callback)
 759    *
 760    * Create an event registration profile with a single email field CRM-9587
 761    */
 762   static function task_4_2_alpha1_eventProfile(CRM_Queue_TaskContext $ctx) {
 763     $upgrade = new CRM_Upgrade_Form();
 764     $profileTitle = ts('Your Registration Info');
 765     $sql = "
 766 INSERT INTO civicrm_uf_group
 767   (is_active, group_type, title, help_pre, help_post, limit_listings_group_id, post_URL, add_to_group_id, add_captcha, is_map, is_edit_link, is_uf_link, is_update_dupe, cancel_URL, is_cms_user, notify, is_reserved, name, created_id, created_date, is_proximity_search)
 768 VALUES
 769   (1, 'Individual, Contact', '{$profileTitle}', NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, 0, NULL, 0, 'event_registration', NULL, NULL, 0);
 770 ";
 771     CRM_Core_DAO::executeQuery($sql);
 772
 773     $eventRegistrationId = CRM_Core_DAO::singleValueQuery('SELECT LAST_INSERT_ID()');
 774     $sql = "
 775 INSERT INTO civicrm_uf_field
 776   (uf_group_id, field_name, is_active, is_view, is_required, weight, help_post, help_pre, visibility, in_selector, is_searchable, location_type_id, phone_type_id, label, field_type, is_reserved)
 777 VALUES
 778   ({$eventRegistrationId}, 'email', 1, 0, 1, 1, NULL, NULL, 'User and User Admin Only', 0, 0, NULL, NULL, 'Email Address', 'Contact', 0);
 779 ";
 780     CRM_Core_DAO::executeQuery($sql);
 781
 782     $sql = "SELECT * FROM civicrm_event WHERE is_online_registration = 1;";
 783     $events = CRM_Core_DAO::executeQuery($sql);
 784     while ($events->fetch()) {
 785       // Get next weights for the event registration profile
 786       $nextMainWeight = $nextAdditionalWeight = 1;
 787       $sql = "
 788 SELECT   weight
 789 FROM     civicrm_uf_join
 790 WHERE    entity_id = {$events->id} AND module = 'CiviEvent'
 791 ORDER BY weight DESC LIMIT 1";
 792       $weights        = CRM_Core_DAO::executeQuery($sql);
 793       $weights->fetch();
 794       if (isset($weights->weight)) {
 795         $nextMainWeight += $weights->weight;
 796       }
 797       $sql = "
 798 SELECT   weight
 799 FROM     civicrm_uf_join
 800 WHERE    entity_id = {$events->id} AND module = 'CiviEvent_Additional'
 801 ORDER BY weight DESC LIMIT 1";
 802       $weights = CRM_Core_DAO::executeQuery($sql);
 803       $weights->fetch();
 804       if (isset($weights->weight)) {
 805         $nextAdditionalWeight += $weights->weight;
 806       }
 807       // Add an event registration profile to the event
 808       $sql = "
 809 INSERT INTO civicrm_uf_join
 810   (is_active, module, entity_table, entity_id, weight, uf_group_id)
 811 VALUES
 812   (1, 'CiviEvent', 'civicrm_event', {$events->id}, {$nextMainWeight}, {$eventRegistrationId});
 813 ";
 814       CRM_Core_DAO::executeQuery($sql);
 815       $sql = "
 816 INSERT INTO civicrm_uf_join
 817   (is_active, module, entity_table, entity_id, weight, uf_group_id)
 818 VALUES
 819   (1, 'CiviEvent_Additional', 'civicrm_event', {$events->id}, {$nextAdditionalWeight}, {$eventRegistrationId});";
 820       CRM_Core_DAO::executeQuery($sql);
 821     }
 822     return TRUE;
 823   }
 824
 825   /**
 826    * Syntatic sugar for adding a task which (a) is in this class and (b) has
 827    * a high priority.
 828    *
 829    * After passing the $funcName, you can also pass parameters that will go to
 830    * the function. Note that all params must be serializable.
 831    */
 832   protected function addTask($title, $funcName) {
 833     $queue = CRM_Queue_Service::singleton()->load(array(
 834       'type' => 'Sql',
 835       'name' => CRM_Upgrade_Form::QUEUE_NAME,
 836     ));
 837
 838     $args = func_get_args();
 839     $title = array_shift($args);
 840     $funcName = array_shift($args);
 841     $task = new CRM_Queue_Task(
 842       array(get_class($this), $funcName),
 843       $args,
 844       $title
 845     );
 846     $queue->createItem($task, array('weight' => -1));
 847   }
 848
 849   public static function deleteInvalidPairs() {
 850     require_once 'CRM/Member/PseudoConstant.php';
 851     require_once 'CRM/Contribute/PseudoConstant.php';
 852     $processedRecords = array();
 853
 854     $tempTableName1 = CRM_Core_DAO::createTempTableName();
 855     // 1. collect all duplicates
 856     $sql = "
 857   CREATE TEMPORARY TABLE {$tempTableName1} SELECT mp.id as payment_id, mp.contribution_id, mp.membership_id, mem.membership_type_id, mem.start_date, mem.end_date, mem.status_id, mem.contact_id, con.contribution_status_id
 858   FROM civicrm_membership_payment mp
 859   INNER JOIN ( SELECT cmp.contribution_id
 860                 FROM civicrm_membership_payment cmp
 861                 LEFT JOIN civicrm_line_item cli ON cmp.contribution_id=cli.entity_id and cli.entity_table = 'civicrm_contribution'
 862                 WHERE cli.entity_id IS NULL
 863                 GROUP BY cmp.contribution_id
 864                 HAVING COUNT(cmp.membership_id) > 1) submp ON submp.contribution_id = mp.contribution_id
 865   INNER JOIN civicrm_membership mem ON mem.id = mp.membership_id
 866   INNER JOIN civicrm_contribution con ON con.id = mp.contribution_id
 867   ORDER BY mp.contribution_id, mp.membership_id";
 868     $dao = CRM_Core_DAO::executeQuery($sql);
 869
 870     $tempTableName2 = CRM_Core_DAO::createTempTableName();
 871     // 2. collect all records that are going to be retained
 872     $sql = "
 873   CREATE TEMPORARY TABLE {$tempTableName2}
 874   SELECT MAX(payment_id) as payment_id FROM {$tempTableName1} GROUP BY contribution_id HAVING COUNT(*) > 1";
 875     CRM_Core_DAO::executeQuery($sql);
 876
 877     // 3. do the un-linking
 878     $sql = "
 879   DELETE cmp.*
 880   FROM   civicrm_membership_payment cmp
 881   INNER JOIN $tempTableName1 temp1 ON temp1.payment_id = cmp.id
 882   LEFT JOIN  $tempTableName2 temp2 ON temp1.payment_id = temp2.payment_id
 883   WHERE temp2.payment_id IS NULL";
 884     CRM_Core_DAO::executeQuery($sql);
 885
 886     // 4. show all records that were Processed, i.e Retained vs Un-linked
 887     $sql = "
 888   SELECT temp1.contact_id, temp1.contribution_id, temp1.contribution_status_id, temp1.membership_id, temp1.membership_type_id, temp1.start_date, temp1.end_date, temp1.status_id, temp2.payment_id as retain_id
 889   FROM $tempTableName1 temp1
 890   LEFT JOIN  $tempTableName2 temp2 ON temp1.payment_id = temp2.payment_id";
 891     $dao = CRM_Core_DAO::executeQuery($sql);
 892     if ($dao->N) {
 893       $membershipType   = CRM_Member_PseudoConstant::membershipType();
 894       $membershipStatus = CRM_Member_PseudoConstant::membershipStatus();
 895       $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 896       while ($dao->fetch()) {
 897         $status         = $dao->retain_id ? 'Retained' : 'Un-linked';
 898         $memType        = CRM_Utils_Array::value($dao->membership_type_id, $membershipType);
 899         $memStatus      = CRM_Utils_Array::value($dao->status_id, $membershipStatus);
 900         $contribStatus  = CRM_Utils_Array::value($dao->contribution_status_id, $contributionStatus);
 901         $processedRecords[] = array($dao->contact_id, $dao->contribution_id, $contribStatus, $dao->membership_id,
 902                                     $memType, $dao->start_date, $dao->end_date, $memStatus, $status);
 903 }
 904     }
 905
 906     if ( !empty($processedRecords) ) {
 907       CRM_Core_Error::debug_log_message("deleteInvalidPairs() - The following records have been processed. Membership records with action:");
 908       CRM_Core_Error::debug_log_message( "Contact ID, ContributionID, Contribution Status, MembershipID, Membership Type, Start Date, End Date, Membership Status, Action" );
 909       foreach ( $processedRecords as $record ) {
 910         CRM_Core_Error::debug_log_message(implode(', ', $record));
 911       }
 912     } else {
 913       CRM_Core_Error::debug_log_message("deleteInvalidPairs() - Could not find any records to process.");
 914     }
 915     return $processedRecords;
 916   }
 917
 918 }