CRM/Upgrade/Incremental/php/FourTwo.php

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