CRM/Upgrade/Incremental/php/FourTwo.php

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