<h1><a href="?pg=<?php echo $SPADE['page']['name']; ?>">Version 2 (V2) Conversion: Overall Step 4</a></h1>
<br />

<?php

if (FALSE == isset($_REQUEST['confirm'])) {

	echo '<a href="index.php?pg=' . $SPADE['page']['name'] . '&confirm=1">Click here to proceed with the PHP conversion (step 1: User eStats).</a><br />';
	echo '<a href="index.php?pg=' . $SPADE['page']['name'] . '&confirm=2">Execute step 2: eStat Entries.</a><br />';
	echo '<a href="index.php?pg=' . $SPADE['page']['name'] . '&confirm=3">Execute step 3: Company / Groups (EHC ONLY).</a><br />';
	echo '<a href="index.php?pg=' . $SPADE['page']['name'] . '&confirm=4">Execute step 4: Competitions.</a><br />';
	echo '<a href="index.php?pg=' . $SPADE['page']['name'] . '&confirm=5">Execute step 5: Fix eStat Entries for Non-Standard Units.</a><br />';
	echo '<a href="index.php?pg=' . $SPADE['page']['name'] . '&confirm=6">Execute step 6: Rebuild eStats From Surveys Taken (EHC ONLY).</a><br />';
	echo '<br />';
	echo 'Make sure that you have executed steps 1 through 3 of the conversion process on a FRESH copy of of the old V1 EHC database.<br />';

	return;
}

define ('LAST_STEP_NUMBER', 6);

/**
 * NOTES:
 
*** Current eStats
*

1  weight  Your current weight  1  lifestyle  yes  2  1119738180  
2  water  amount of water consumed  4  nutrition  yes  16  1119740160  
3  time exercised  amount of time exercised  7  fitness  yes  16  1119740340  
4  distance exercised  amount of distance exercised  9  fitness  yes  16  1119740400  
5  fruits and vegetables  fruit and vegetables eaten  12  nutrition  yes  16  1119740460  
6  sleep  amount of time slept at night  8  lifestyle  yes  16  1119740640  
7  calorie intake  Calories eaten  18  nutrition  yes  2  1122444781  
8  fat intake  Fat in all food eaten  17  nutrition  yes  2  1122444781  
9  carb intake  Carbohydrates in all food eaten  17  nutrition  yes  2  1122444781  

*** Current units
*

1  pounds  1  2  1119738422  
2  kilograms  1  2  1119738444  
4  glasses  1  16  1119742425  
5  ounces  0  16  1119742425  
7  minutes  0  16  1119742490  
8  hours  2  16  1119742490  
9  miles  2  16  1119742515  
10  kilometers  2  16  1119742515  
11  laps  1  16  1119742546  
12  cups  1  16  1119742939  
13  pieces  1  16  1119742953  
16  steps  0  16  1119743102  
17  grams  1  2  1122444589  
18  calories  0  2  1122444589  

 */

echo '<h2>PHP Conversion Step ' . (int)$_REQUEST['confirm'] . '</h2>';

switch ((int)$_REQUEST['confirm']) {
// -------------------------------- USER ESTATS ---------------------------------------------
case '1':

	echo "Creating User eStats (determining which eStats to track) for all users.<br />";
	echo "No error indicates success.<br />";

	// Clear out table first (just in case)
	$Q = "DELETE FROM user_estats";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Set up all users to track weight with 'pounds' as units
	$Q = "INSERT INTO user_estats (userId, estatId, unitId, frequency)
		SELECT u.id AS 'userId', 1 as 'estatId', 1 AS 'unitId', 'daily' as 'frequency'
		FROM _users u";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Set up all users to track time exercised with 'minutes' as units
	$Q = "INSERT INTO user_estats (userId, estatId, unitId, frequency)
		SELECT u.id AS 'userId', 3 as 'estatId', 7 AS 'unitId', 'daily' as 'frequency'
		FROM _users u";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Set up 'distance exercised' for users who had entries (estat 4, 'steps' unit 16, 'miles' unit 9)
	$Q = "INSERT INTO user_estats (userId, estatId, unitId, frequency)
		SELECT us.userId AS 'userId', 4 AS 'estatId', IF(avg(distanceExercised)>100,16,9) AS 'unitId', 'daily' AS 'frequency'
		FROM user_statistics us 
		WHERE us.distanceExercised > 0
		GROUP BY us.userId";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Set up 'water' for users who had entries (estat 2, 'ounces' unit 5, 'glasses' unit 4)
	$Q = "INSERT INTO user_estats (userId, estatId, unitId, frequency)
		SELECT us.userId AS 'userId', 2 AS 'estatId', IF(avg(water)>7,5,4) AS 'unitId', 'daily' AS 'frequency'
		FROM user_statistics us 
		WHERE us.water > 0
		GROUP BY us.userId";
		
	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Set up 'calories' for users who had entries (estat 7, 'calories' unit 18)
	$Q = "INSERT INTO user_estats (userId, estatId, unitId, frequency)
		SELECT us.userId AS 'userId', 7 AS 'estatId', 18 AS 'unitId', 'daily' AS 'frequency'
		FROM user_statistics us 
		WHERE us.calories > 0
		GROUP BY us.userId";
	
	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Set up 'fat' for users who had entries (estat 8, 'grams' unit 17)
	$Q = "INSERT INTO user_estats (userId, estatId, unitId, frequency)		
		SELECT us.userId AS 'userId', 8 AS 'estatId', 17 AS 'unitId', 'daily' AS 'frequency'
		FROM user_statistics us 
		WHERE us.fat > 0
		GROUP BY us.userId";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Set up 'carbs' for users who had entries (estat 9, 'grams' unit 17)
	$Q = "INSERT INTO user_estats (userId, estatId, unitId, frequency)		
		SELECT us.userId AS 'userId', 9 AS 'estatId', 17 AS 'unitId', 'daily' AS 'frequency'
		FROM user_statistics us 
		WHERE us.carbs > 0
		GROUP BY us.userId";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	break;

// -------------------------------- ESTAT ENTRIES ---------------------------------------------
case '2':

	echo "Creating eStat Entries for all users.<br />";
	echo "No error indicates success.<br />";

	// Clear out table first (just in case)
	$Q = "DELETE FROM estat_entries";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Insert 'weight' records
	$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
		SELECT us.userId, 1 AS 'estatId', us.weight AS 'value', us.dateOfEntry, us.userId AS 'createdByUserId', UNIX_TIMESTAMP(NOW()) AS 'dateCreated'
		FROM user_statistics us
		WHERE us.weight > 0";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Insert 'water' records
	$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
		SELECT us.userId, 2 AS 'estatId', us.water AS 'value', us.dateOfEntry, us.userId AS 'createdByUserId', UNIX_TIMESTAMP(NOW()) AS 'dateCreated'
		FROM user_statistics us
		WHERE us.water > 0";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Insert 'time exercised' records
	$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
		SELECT us.userId, 3 AS 'estatId', us.minutesExercise AS 'value', us.dateOfEntry, us.userId AS 'createdByUserId', UNIX_TIMESTAMP(NOW()) AS 'dateCreated'
		FROM user_statistics us
		WHERE us.minutesExercise > 0";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Insert 'distance exercised' records
	$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
		SELECT us.userId, 4 AS 'estatId', us.distanceExercised AS 'value', us.dateOfEntry, us.userId AS 'createdByUserId', UNIX_TIMESTAMP(NOW()) AS 'dateCreated'
		FROM user_statistics us
		WHERE us.distanceExercised > 0";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Insert 'calories' records
	$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
		SELECT us.userId, 7 AS 'estatId', us.calories AS 'value', us.dateOfEntry, us.userId AS 'createdByUserId', UNIX_TIMESTAMP(NOW()) AS 'dateCreated'
		FROM user_statistics us
		WHERE us.calories > 0";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Insert 'fat' records
	$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
		SELECT us.userId, 8 AS 'estatId', us.fat AS 'value', us.dateOfEntry, us.userId AS 'createdByUserId', UNIX_TIMESTAMP(NOW()) AS 'dateCreated'
		FROM user_statistics us
		WHERE us.fat > 0";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	// Insert 'carb' records
	$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
		SELECT us.userId, 9 AS 'estatId', us.carbs AS 'value', us.dateOfEntry, us.userId AS 'createdByUserId', UNIX_TIMESTAMP(NOW()) AS 'dateCreated'
		FROM user_statistics us
		WHERE us.carbs > 0";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError('An error occurred at ' . __FILE__ . ':' . __LINE__ . '.');
		return false;
	}

	break;

// -------------------------------- COMPANY / GROUPS ---------------------------------------------
case '3':

	echo "Creating groups for all companies and group memberships for all users.<br />";
	echo "No error indicates success.<br />";

	// Create groups for companies
	$Q = "SELECT id, companyName, companyVanity, userId
		FROM companies";

	$companies = $SPADE['db']->query($Q);

	foreach ($companies as $company) {
		$Q = "INSERT INTO _groups (name, description, about, ownerId)
			VALUES ('" . $company['companyVanity'] . "', 
				'" . $company['companyName'] . "', 
				'Company Group Account', 
				" . (int)$company['userId'] . ")";

		$companyGroupId = $SPADE['db']->query($Q);

		if (FALSE == $companyGroupId) {
			logError ('An error occurred while attempting to create a group for company "' . $company['companyName'] . '".');
			continue;
		}

		// Update companies with group IDs
		$Q = "UPDATE companies 
			SET groupId = " . (int)$companyGroupId . "
			WHERE id = " . (int)$company['id'];

		if (FALSE == $SPADE['db']->query($Q)) {
			logError ('An error occurred while attempting to update the company "' . $company['companyName'] . '" with its new group id (' . $companyGroupId . ').');
			continue;
		}

		// Create group memberships for all users
		$Q = "INSERT INTO _group_users (groupId, userId, title)
			SELECT " . (int)$companyGroupId . " AS 'groupId', u.id AS 'userId', 'Employee: " . addslashes($company['companyName']) . "' AS 'title'
			FROM _users u
			WHERE u.companyId = " . (int)$company['id'];

		if (FALSE == $SPADE['db']->query($Q)) {
			logError ('An error occurred while attempting to create group memberships for employees of "' . $company['companyName'] . '". It is possible this company has no registered employees.');
			//continue;
		}

		// Update group_forums table with now corresponding group ID
		$Q = "UPDATE group_forums
			SET groupId = " . (int)$companyGroupId . "
			WHERE groupId = " . (int)$company['id'];

		if (FALSE == $SPADE['db']->query($Q)) {
			logError ('An error occurred while attempting to update the company forums for "' . $company['companyName'] . '". It is possible this company had no registered forums.');
			//continue;
		}
	
		echo 'SUCCESS: ' . $company['id'] . '.) ' . $company['companyName'] . ' -- ' . $company['companyVanity'] . '.ehealthcompete.com<br />';
	}


	break;

// -------------------------------- COMPETITIONS ---------------------------------------------
case '4':

	echo "Converting competition data.<br />";
	echo "No error indicates success.<br />";

	$scoringMethods = array(
		'Weight Lost' => array( 'estatId' => 1, 'basedOn' => 'actual change', 'polarity' => 'negative' ),
		'Percentage Weight Lost' => array( 'estatId' => 1, 'basedOn' => 'percent change', 'polarity' => 'negative' ),
		'Minutes Exercise' => array( 'estatId' => 3, 'basedOn' => 'total', 'polarity' => 'positive' ),
		'Distance Exercised' => array( 'estatId' => 4, 'basedOn' => 'total', 'polarity' => 'positive' ),
		'Weight Gained' => array( 'estatId' => 1, 'basedOn' => 'actual change', 'polarity' => 'positive')
	);

	/**		
	1  weight  Your current weight  1  lifestyle  yes  2  1119738180  
	2  water  amount of water consumed  4  nutrition  yes  16  1119740160  
	3  time exercised  amount of time exercised  7  fitness  yes  16  1119740340  
	4  distance exercised  amount of distance exercised  9  fitness  yes  16  1119740400  
	5  fruits and vegetables  fruit and vegetables eaten  12  nutrition  yes  16  1119740460  
	6  sleep  amount of time slept at night  8  lifestyle  yes  16  1119740640  
	7  calorie intake  Calories eaten  18  nutrition  yes  2  1122444781  
	8  fat intake  Fat in all food eaten  17  nutrition  yes  2  1122444781  
	9  carb intake  Carbohydrates in all food eaten  17  nutrition  yes  2  1122444781  
	**/


	$Q = "SELECT * FROM competitions_old";

	$competitions = $SPADE['db']->query($Q);

	// Copy main competition data over...
	// Done in SQL step 1.

	foreach ($competitions as $comp) {
		// ----- CREATE COMPETITION PARAMETERS ----- //
		$currentParameters = array();
		$currentParameters[] = $comp['scoringMethod'];		

		// Create competition parameters
		$Q = "INSERT INTO competition_parameters (competitionId, estatId, basedOn, polarity, displayOrder)
			VALUES (" . (int)$comp['id'] . ",
				" . (int)$scoringMethods[$comp['scoringMethod']]['estatId'] . ",
				'" . $scoringMethods[$comp['scoringMethod']]['basedOn'] . "',
				'" . $scoringMethods[$comp['scoringMethod']]['polarity'] . "',
				0)";

		if (FALSE == ($parameterId = $SPADE['db']->query($Q))) {
			logError ('Unable to create (primary) competition parameter for competition ID ' . $comp['id'] . '.');
			continue;
		}

		// Add parameter 
		if ($comp['tieBreakerMethod'] && !in_array($comp['tieBreakerMethod'], $currentParameters)) {
			$currentParameters[] = $comp['tieBreakerMethod'];

			$Q = "INSERT INTO competition_parameters (competitionId, estatId, basedOn, polarity, displayOrder)
				VALUES (" . (int)$comp['id'] . ",
					" . (int)$scoringMethods[$comp['tieBreakerMethod']]['estatId'] . ",
					'" . $scoringMethods[$comp['tieBreakerMethod']]['basedOn'] . "',
					'" . $scoringMethods[$comp['tieBreakerMethod']]['polarity'] . "',
					1)";
	
			if (FALSE == $SPADE['db']->query($Q)) {
				logError ('Unable to create (primary) competition parameter for competition ID ' . $comp['id'] . '.');
				continue;
			}
		}

		if ($comp['weeklyWinnersMethod1'] && !in_array($comp['weeklyWinnersMethod1'], $currentParameters)) {
			$currentParameters[] = $comp['weeklyWinnersMethod1'];

			$Q = "INSERT INTO competition_parameters (competitionId, estatId, basedOn, polarity, displayOrder)
				VALUES (" . (int)$comp['id'] . ",
					" . (int)$scoringMethods[$comp['weeklyWinnersMethod1']]['estatId'] . ",
					'" . $scoringMethods[$comp['weeklyWinnersMethod1']]['basedOn'] . "',
					'" . $scoringMethods[$comp['weeklyWinnersMethod1']]['polarity'] . "',
					2)";
	
			if (FALSE == $SPADE['db']->query($Q)) {
				logError ('Unable to create (primary) competition parameter for competition ID ' . $comp['id'] . '.');
				continue;
			}
		}

		if ($comp['weeklyWinnersMethod2'] && !in_array($comp['weeklyWinnersMethod2'], $currentParameters)) {
			$currentParameters[] = $comp['weeklyWinnersMethod2'];

			$Q = "INSERT INTO competition_parameters (competitionId, estatId, basedOn, polarity, displayOrder)
				VALUES (" . (int)$comp['id'] . ",
					" . (int)$scoringMethods[$comp['weeklyWinnersMethod2']]['estatId'] . ",
					'" . $scoringMethods[$comp['weeklyWinnersMethod2']]['basedOn'] . "',
					'" . $scoringMethods[$comp['weeklyWinnersMethod2']]['polarity'] . "',
					3)";
	
			if (FALSE == $SPADE['db']->query($Q)) {
				logError ('Unable to create (primary) competition parameter for competition ID ' . $comp['id'] . '.');
				continue;
			}
		}


		// ----- CREATE COMPETITION PRIZES ----- //
		$Q = "INSERT INTO competition_prizes (
				competitionId, title, description, maxWinners, 
				frequency, andOr, dateStart, dateEnd )
			VALUES (" . (int)$comp['id'] . ",
				'Competition Prize',
				'" . addslashes($comp['potDescription']) . "',
				1,
				'once',
				'and',
				" . (int)$comp['startDate'] . ",
				" . (int)$comp['endDate'] . ")";

		if (FALSE == ($prizeId = $SPADE['db']->query($Q))) {
			logError ('Unable to create a competition prize for competition ID ' . (int)$comp['id'] . '.');
			continue;
		}

		
		// ----- CREATE COMPETITION PRIZE PARAMETERS ----- //
		$Q = "INSERT INTO competition_prize_parameters (
				competitionPrizeId, competitionParameterId)
			VALUES (" . (int)$prizeId . ",
				" . (int)$parameterId . ")";

		if (FALSE == $SPADE['db']->query($Q)) {
			logError ('Unable to create a competition prize parameter for competition ID ' . (int)$comp['id'] . '.');
			continue;
		}
	}

	// ----- CREATE INVOICES ----- //
	$Q = "SELECT cp.userId, u.firstName, u.lastName, c.companyName, u.address1, u.address2,
			u.city, u.state, u.country, u.zip, u.phone1, u.email,
			cp.amount, cp.dateDonated, cp.paymentMethod, cp.entryFee,
			cp.compId, comp.title
		FROM competition_pot cp
		INNER JOIN _users u ON (cp.userId = u.id)
		LEFT JOIN competitions comp ON (cp.compId = comp.id)
		LEFT JOIN companies c ON (u.companyId = c.id)";

	$paymentDetails = $SPADE['db']->query($Q);

	// Continue if no entry fees were paid
	if (FALSE == is_array($paymentDetails) || 0 == sizeof($paymentDetails)) {
		continue;
	}

	foreach ($paymentDetails as $payment) {
		$Q = "INSERT INTO invoices (
				userId, firstName, lastName, company,
				address1, address2, city, state, country, zip, phone, email,
				dateDue, amountDue)
			VALUES (
				" . (int)$payment['userId'] . ",
				'" . addslashes($payment['firstName']) . "',
				'" . addslashes($payment['lastName']) . "',
				'" . addslashes($payment['companyName']) . "',
				'" . addslashes($payment['address1']) . "',
				'" . addslashes($payment['address2']) . "',
				'" . addslashes($payment['city']) . "',
				'" . addslashes($payment['state']) . "',
				'" . addslashes($payment['country']) . "', 
				'" . $payment['zip'] . "',
				'" . $payment['phone1'] . "',
				'" . addslashes($payment['email']) . "',
				" . (int)$payment['dateDonated'] . ",
				'" . $payment['entryFee'] . "')";

		if (FALSE == ($invoiceId = $SPADE['db']->query($Q))) {
			logError ('Could not create an invoice for competition payment for comp ID ' . $payment['compId'] . ' for ' . $payment['firstName'] . ' ' . $payment['lastName'] . '.');
			continue;
		}

		$Q = "INSERT INTO competition_entry_fees (
				competitionId, userId, amount, invoiceId)
			VALUES (
				'" . $payment['compId'] . "',
				" . (int)$payment['userId'] . ",
				'" . $payment['amount'] . "',
				" . (int)$invoiceId . ")";

		if (FALSE == $SPADE['db']->query($Q)) {
			logError ('Unable to save the competition entry fee for comp ID ' . $payment['compId'] . ' for ' . $payment['firstName'] . ' ' . $payment['lastName'] . '.');
			continue;
		}

		$Q = "INSERT INTO invoice_line_items (
				invoiceNumber, description, price, quantity)
			VALUES (
				" . (int)$invoiceId . ",
				'Competition entry fee, competition #" . $payment['compId'] . "',
				'" . $payment['entryFee'] . "', 
				1)";

		if (FALSE == $SPADE['db']->query($Q)) {
			logError ('Could not create an invoice line item for comp ID ' . $payment['compId'] . ' for ' . $payment['firstName'] . ' ' . $payment['lastName'] . '.');
		}

		$Q = "INSERT INTO invoice_payments (
				invoiceNumber, dateOfPayment, amountPaid, paymentMethod)
			VALUES (
				" . (int)$invoiceId . ",
				" . (int)$payment['dateDonated'] . ",
				'" . $payment['amount'] . "',
				'" . $payment['paymentMethod'] . "')";

		if (FALSE == $SPADE['db']->query($Q)) {
			logError ('Could not create an invoice payment for comp ID ' . $payment['compId'] . ' for ' . $payment['firstName' ] . ' ' . $payment['lastName'] . '.');
			continue;
		}
	}

	break;

// -------------------------------- FIX ESTAT ENTRIES FOR NON-STANDARD UNITS ---------------------------------------------
case '5':

	echo "Converting eStats for non-standard units.<br />";
	echo "No error indicates success.<br />";

	// Get userId's of users who have non-standard units (ounces = unitId 5) for water entries.
	$Q = "SELECT u.id
		FROM user_estats ue
		INNER JOIN _users u ON (ue.userId = u.id)
		WHERE ue.estatId = 2
		AND ue.unitId = 5";

	$userIds = $SPADE['db']->query($Q, DB_LIST);
	$userIds = implode(',', $userIds);

	$Q = "UPDATE estat_entries 
		SET value = (value * 0.125)
		WHERE estatId = 2
		AND userId IN (" . $userIds . ")";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError ('An error occurred while attempting to update and convert water entries from ounces to glasses.');
		break;
	}

	// Get userId's of users who have non-standard units (steps = unitId 16) for distance exercised entries.
	$Q = "SELECT u.id
		FROM user_estats ue
		INNER JOIN _users u ON (ue.userId = u.id)
		WHERE ue.estatId = 4
		AND ue.unitId = 16";

	$userIds = $SPADE['db']->query($Q, DB_LIST);
	$userIds = implode(',', $userIds);

	$Q = "UPDATE estat_entries 
		SET value = (value * 0.0005)
		WHERE estatId = 4
		AND userId IN (" . $userIds . ")";

	if (FALSE == $SPADE['db']->query($Q)) {
		logError ('An error occurred while attempting to update and convert distance exercised entries from steps to miles.');
		break;
	}


	break;

// -------------------------------- CORRECT / REBUILD estat_entries FROM company_survey_estats ---------------------------------------------
case '6':

	echo "Rebuilding estat_entries from company_survey_estats<br />";
	echo "No error indicates success.<br />";

	$Q = "SELECT *
		FROM company_survey_estats";

	// Temp...
	$Q .= " WHERE surveyId = 34";

	$companySurveyEstats = $SPADE['db']->query($Q);

	foreach ($companySurveyEstats as $cse) {

		$whereAddition = '';

		if ($cse['dateStart']) {
			$whereAddition .= ' AND st.dateTaken > ' . $cse['dateStart'];
		}

		if ($cse['dateEnd']) {
			$whereAddition .= ' AND st.dateTaken < ' . $cse['dateEnd'];
		}

		$Q = "SELECT ee.id
			FROM surveys_taken st
			INNER JOIN surveys s ON (st.surveyId = s.id)
			INNER JOIN estat_entries ee ON (ee.userId = st.userId AND ee.estatId = " . $cse['estatId'] . " AND ee.dateOfEntry = st.dateTaken)
			WHERE s.id = " . $cse['surveyId'] . "
			AND (s.companyId = " . $cse['companyId'] . " OR s.companyId = 0)" . $whereAddition;
		
		display($Q);

		$entryIds = trim(implode(',', $SPADE['db']->query($Q, DB_LIST)));

		if ($entryIds) {
			$Q = "DELETE FROM estat_entries WHERE id IN (" . $entryIds . ")";
	
			display($Q);
	
			$SPADE['db']->query($Q);
		}

		$Q = "SELECT st.*
			FROM surveys_taken st
			LEFT JOIN surveys s ON (st.surveyId = s.id)
			WHERE s.id = " . $cse['surveyId'] . "
			AND (s.companyId = " . $cse['companyId'] . " OR s.companyId = 0)" . $whereAddition;

		display($Q);

		$surveysTaken = $SPADE['db']->query($Q);

		if (is_array($surveysTaken) AND 0 < sizeof($surveysTaken)) {
			foreach ($surveysTaken as $st) {
				$Q = "INSERT INTO estat_entries (userId, estatId, value, dateOfEntry, createdByUserId, dateCreated)
					VALUES (
						" . $st['userId'] . ",
						" . $cse['estatId'] . ",
						" . $st['pointsEarned'] . ",
						" . $st['dateTaken'] . ",
						" . $SPADE['user']->id . ",
						" . time() . ")";

				display($Q);

				$SPADE['db']->query($Q);
			}
		}
	}



	break;

default:
	logError( 'Unknown confirm step number: ' . $_REQUEST['confirm'] );
	return;
}


if ($_REQUEST['confirm'] <= LAST_STEP_NUMBER) {
	echo '<br /><br /><a href="index.php?pg=' . $SPADE['page']['name'] . '&confirm=' . ((int)$_REQUEST['confirm'] + 1) . '">Execute the next step of the PHP conversion process.</a>';
}


?>