<?php
global $dbc;
include ('includes/settings.php');
include ('includes/mysql_connect.php');
include ('includes/funcs.php');
$page = $_GET['p'];
// require_once('../FirePHPCore/FirePHP.class.php');
// 	ob_start();

// 	$firephp = FirePHP::getInstance(true);
require("../lib/SpreadsheetExport.php");

// // // Instantiate new spreadsheet
$export = new SpreadsheetExport();
// echo "$page";
switch ($page) 
{
	// users
	case 'u':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string");//ln
		$export->addColumn("string");//un
		$export->addColumn("string"); //sec level
		$export->addColumn("date", 5); //last log
		$export->addColumn("date", 5); //created
		$export->addColumn("string"); //by who
		$export->addColumn("date", 5); //modified
		$export->addColumn("string"); //by who

		$export->filename = "users";

		$export->addRow(array("First Name", "Last Name", "Username", "Security Level", "Last Logged in on", "Date Created", "Created By", "Date Modified", "Modified By"));
		$sql = "SELECT strFirstName, strLastName, strUsername, intSecurityLevel, dateLastLogin, dateEntered, intAddedBy, dateModified, intModifiedBy from tblUsers where ynDeleted = 0";
		$result = getFromDatabase($sql);

		while ($row = mysqli_fetch_array($result))
		{
			$enteredBy = $row['intAddedBy'];
			$modifiedBy = $row['intModifiedBy'];
			$security = $row['intSecurityLevel'];
			if (strcmp($security, '25') == 0)
			{
				$security = "Regular User";
			}
			else
			{
				$security = "Administrator";
			}
			$sqlEntered =  "SELECT strFirstName, strLastName from tblUsers where intUserID = '$enteredBy'";
			$resultEntered = getFromDatabase($sqlEntered);

			while ($rowEB = mysqli_fetch_array($resultEntered))
			{
				$enteredByFName = $rowEB['0'];
				$enteredByLName = $rowEB['1'];

			}
			$sqlModified = "SELECT strFirstName, strLastName from tblUsers where intUserID = '$modifiedBy'";
			$resultModified = getFromDatabase($sqlModified);

			while ($rowEM = mysqli_fetch_array($resultModified))
			{
				$modByFName = $rowEM['0'];
				$modByLName = $rowEM['1'];
				
			}
			$export->addRow(array($row['0'], $row['1'], $row['2'], $security, $row['4'], $row['5'], $enteredByFName . ' ' . $enteredByLName, $row['7'], $modByFName . ' ' . $modByLName));
		}
		mysqli_free_result($result);
		mysqli_free_result($resultEntered);
		mysqli_free_result($resultModified);
		break;
	}
	case 'park':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string"); //ln
		$export->addColumn("string"); //email
		$export->addColumn("string"); //phone
		// $export->addColumn("string"); //optIn
		$export->addColumn("string"); //date
		$export->addColumn("string"); //city
		$export->addColumn("string"); //gender

		$export->filename = "Park Place Entries";
		$export->addRow(array("First Name","Last Name","Email Address","Phone Number","Date Registered","City","Gender"));
		$sql = "SELECT * from tblKiosksParkPlace2015";
		$result = getFromDatabase($sql);
		while ($row = mysqli_fetch_array($result) )
		{
			$c_first_name = $row['strFirstName'];
			$c_last_name = $row['strLastName'];
			$c_email = $row['strEmailAddress'];
			$c_phone = $row['strPhoneNumber'];
			// $c_optIn = $row['optInYN'];
			$c_date = $row['entryDate'];
			$c_city = $row['strCity'];
			$c_gender = $row['genderMF'];
			// $c_deleted = $row['deletedYN'];
			$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_date,$c_city, $c_gender));
			
		}
		mysqli_free_result($result);


		break;
	}
	case 'eatfree':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string"); //ln
		$export->addColumn("string"); //email
		$export->addColumn("string"); //phone
		// $export->addColumn("string"); //optIn
		$export->addColumn("string"); //date
		$export->addColumn("string"); //city
		$export->addColumn("string"); //gender

		$export->filename = "Eat Free For a Year Entries";
		$export->addRow(array("First Name","Last Name","Email Address","Phone Number","Date Registered","City","Postal Code"));
		$sql = "SELECT * from tblKioskParkPlace2017";
		$result = getFromDatabase($sql);
		while ($row = mysqli_fetch_array($result) )
		{
			$c_first_name = $row['strFirstName'];
			$c_last_name = $row['strLastName'];
			$c_email = $row['strEmail'];
			$c_phone = $row['strPhone'];
			// $c_optIn = $row['optInYN'];
			$c_date = $row['dateEntered'];
			$c_city = $row['strCity'];
			$c_gender = $row['strPostalCode'];
			// $c_deleted = $row['deletedYN'];
			$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_date,$c_city, $c_gender));
			
		}
		mysqli_free_result($result);


		break;
	}	
	case 'med':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string"); //ln
		$export->addColumn("string"); //email
		$export->addColumn("string"); //phone
		// $export->addColumn("string"); //optIn
		$export->addColumn("string"); //date
		$export->addColumn("string"); //city
		$export->addColumn("string"); //gender

		$export->filename = "Medicine Hat Entries";
		$export->addRow(array("First Name","Last Name","Email Address","Phone Number","Date Registered","City","Gender"));
		$sql = "SELECT * from tblKiosksMedicineHat2015";
		$result = getFromDatabase($sql);
		while ($row = mysqli_fetch_array($result) )
		{
			$c_first_name = $row['strFirstName'];
			$c_last_name = $row['strLastName'];
			$c_email = $row['strEmailAddress'];
			$c_phone = $row['strPhoneNumber'];
			// $c_optIn = $row['optInYN'];
			$c_date = $row['entryDate'];
			// $c_deleted = $row['deletedYN'];
			$c_city = $row['strCity'];
			$c_gender = $row['genderMF'];
			
			$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_date,$c_city, $c_gender));
			
		}
		mysqli_free_result($result);


		break;
	}
	case 'medOld':
	{

		$export->filename = "Medicine Hat Previous Contest Entries";
		$column_sql = "SHOW COLUMNS FROM tblKioskEntrantsMedicineHat ";
		$column_result = getFromDatabase($column_sql);
		
		$num_of_fields = mysqli_num_rows($column_result);
		
		for ($i=0; $i < $num_of_fields; $i++) 
		{ 
			$export->addColumn("string");
		}

		while ($column_row = mysqli_fetch_array($column_result) )
		{
			$cols .= $column_row[0].", ";
		}
		
		$cols = explode(', ', $cols);

		$export->addRow($cols);

		$data_sql = "SELECT * from tblKioskEntrantsMedicineHat";
		$data_result = getFromDatabase($data_sql);

		while ($data_row = mysqli_fetch_array($data_result) )
		{
			$data_cols = '';
			for ($i=0; $i < $num_of_fields; $i++) 
			{ 
				$data_cols .= $data_row[$i].', ';
			}

			// echo "$data_cols <br>";
			$data_cols = explode(',', $data_cols);
			$export->addRow($data_cols);
		}
		mysqli_free_result($column_result);
		mysqli_free_result($data_result);
		break;
	}
	case 'parkOld':
	{

		$export->filename = "Park Place Previous Contest Entries";
		$column_sql = "SHOW COLUMNS FROM tblKioskEntrantsParkPlace";
		$column_result = getFromDatabase($column_sql);
		
		$num_of_fields = mysqli_num_rows($column_result);
		
		for ($i=0; $i < $num_of_fields; $i++) 
		{ 
			$export->addColumn("string");
		}

		while ($column_row = mysqli_fetch_array($column_result) )
		{
			$cols .= $column_row[0].", ";
		}
		
		$cols = explode(', ', $cols);

		$export->addRow($cols);

		$data_sql = "SELECT * from tblKioskEntrantsParkPlace";
		$data_result = getFromDatabase($data_sql);

		while ($data_row = mysqli_fetch_array($data_result) )
		{
			$data_cols = '';
			for ($i=0; $i < $num_of_fields; $i++) 
			{ 
				$data_cols .= $data_row[$i].', ';
			}

			// echo "$data_cols <br>";
			$data_cols = explode(',', $data_cols);
			$export->addRow($data_cols);
		}
		mysqli_free_result($column_result);
		mysqli_free_result($data_result);
		break;
	}
	case 'kingsway':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string"); //ln
		$export->addColumn("string"); //email
		$export->addColumn("string"); //phone
		$export->addColumn("string"); //address
		$export->addColumn("string"); //city
		$export->addColumn("string"); //province
		$export->addColumn("string"); //postal
		$export->addColumn("string"); //date
		$export->addColumn("string"); //gender
		$export->addColumn("string"); //optIn
		$export->filename = "kingsway Entries";
		$export->addRow(array("First Name","Last Name","Email Address","Phone Number","Address", "City", "Province", "Postal Code", "Date Registered","Gender","Opt In"));

		$sql = "SELECT * from tblKiosksKingsway";
		// $sql = "SELECT * from tblKiosksKingsway inner join tblProvince on tblKiosksKingsway.intProvince = tblProvince.intProvince";
		$result = getFromDatabase($sql);
		
		while ($row = mysqli_fetch_array($result) )
		{
			$c_first_name = $row['strFirstName'];
			$c_last_name = $row['strLastName'];
			$c_email = $row['strEmailAddress'];
			$c_phone = $row['strPhone'];
			$c_address = $row['strAddress'];
			$c_city = $row['strCity'];
			$c_province = $row['strProvince'];
			$c_postal = $row['strPostal'];
			$c_date = $row['entryDate'];
			$c_gender = $row['strGender'];
			$c_optIn = $row['ynOptIn'];

			$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_address, $c_city, $c_province, $c_postal, $c_date, $c_gender, $c_optIn));
		}
		mysqli_free_result($result);
		break;
	}
	case 'fav2016':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string");//ln
		$export->addColumn("string"); //email
		$export->addColumn("string");//phone
		$export->addColumn("string"); //city
		$export->addColumn("string");//date
		$export->addColumn("string"); //bonus code
		$export->filename = "Our-Favorite-Things-2016";

		$export->addRow(array("First Name", "Last Name", "Email", "Phone", "City", "Date Entered", "Bonus Code"));

		$sql = "SELECT strFirstName, strLastName, strCity, strEmail, strPhone, dateEntered, strBonusCode from tblKioskParkPlace2016 left join tblKioskBonusCodesParkPlace2016 on tblKioskParkPlace2016.intBonusCode = tblKioskBonusCodesParkPlace2016.intBonusCodeID";
		$result = getFromDatabase($sql);
		if (mysqli_num_rows($result) == 0)
		{
			// $content = "<p>Sorry no entries found</p>";
			$export->addRow(array('Sorry no entries found', '', '','','','',''));

		}
		else
		{

			while ($row = mysqli_fetch_array($result) )
			{
				$c_first_name = $row['strFirstName'];
				$c_last_name = $row['strLastName'];
				$c_email = $row['strEmail'];
				$c_phone = $row['strPhone'];
				$c_city = $row['strCity'];
				$c_date = $row['dateEntered'];
				$c_bonus_code = $row['strBonusCode'];

				if ($c_bonus_code == '')
				{
					$c_bonus_code = 'No code';
				}


				$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_city, $c_date, $c_bonus_code));

				// $content .= '<tr><td>'.$c_first_name.'</td><td>'.$c_last_name.'</td><td>'.$c_email.'</td><td>'.$c_phone.'</td><td>'.$c_city.'</td><td>'.$c_date.'</td><td>'.$c_bonus_code.'</td></tr>';
			}
		}
		
		mysqli_free_result($result);	

		break;
	}
	case 'park2018':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string");//ln
		$export->addColumn("string"); //email
		$export->addColumn("string");//phone
		$export->addColumn("string"); //city
		$export->addColumn("string"); //postal code
		$export->addColumn("string");//date
		$export->addColumn("string"); //bonus code
		$export->filename = "Park Place 2018";

		$export->addRow(array("First Name", "Last Name", "Email", "Phone", "City", "Postal Code", "Date Entered", "Bonus Code"));

		$sql = "SELECT strFirstName, strLastName, strCity, strPostalCode, strEmail, strPhone, dateEntered, strBonusCode from tblKioskParkPlace2018 left join tblKioskBonusCodesParkPlace2018 on tblKioskParkPlace2018.intBonusCode = tblKioskBonusCodesParkPlace2018.intBonusCodeID";
		$result = getFromDatabase($sql);
		if (mysqli_num_rows($result) == 0)
		{
			// $content = "<p>Sorry no entries found</p>";
			$export->addRow(array('Sorry no entries found', '', '','','','',''));

		}
		else
		{

			while ($row = mysqli_fetch_array($result) )
			{
				$c_first_name = $row['strFirstName'];
				$c_last_name = $row['strLastName'];
				$c_email = $row['strEmail'];
				$c_phone = $row['strPhone'];
				$c_city = $row['strCity'];
				$c_pc = $row['strPostalCode'];
				$c_date = $row['dateEntered'];
				$c_bonus_code = $row['strBonusCode'];

				if ($c_bonus_code == '')
				{
					$c_bonus_code = 'No code';
				}


				$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_city, $c_pc, $c_date, $c_bonus_code));

				// $content .= '<tr><td>'.$c_first_name.'</td><td>'.$c_last_name.'</td><td>'.$c_email.'</td><td>'.$c_phone.'</td><td>'.$c_city.'</td><td>'.$c_date.'</td><td>'.$c_bonus_code.'</td></tr>';
			}
		}
		
		mysqli_free_result($result);	

		break;
	}
	case 'pond2017':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string");//ln
		$export->addColumn("string"); //email
		$export->addColumn("string");//phone
		$export->addColumn("string"); //city
		$export->addColumn("string");//date
		$export->addColumn("string"); //bonus code
		$export->filename = "Shop-Win-Drive";

		$export->addRow(array("First Name", "Last Name", "Email", "Phone", "City", "Date Entered", "Bonus Code"));

		$sql = "SELECT strFirstName, strLastName, strCity, strEmail, strPhone, dateEntered, strBonusCode from tblKioskPeterPond2017 left join tblKioskBonusCodesPeterPond2017 on tblKioskPeterPond2017.intBonusCode = tblKioskBonusCodesPeterPond2017.intBonusCodeID";
		$result = getFromDatabase($sql);
		if (mysqli_num_rows($result) == 0)
		{
			// $content = "<p>Sorry no entries found</p>";
			$export->addRow(array('Sorry no entries found', '', '','','','',''));

		}
		else
		{

			while ($row = mysqli_fetch_array($result) )
			{
				$c_first_name = $row['strFirstName'];
				$c_last_name = $row['strLastName'];
				$c_email = $row['strEmail'];
				$c_phone = $row['strPhone'];
				$c_city = $row['strCity'];
				$c_date = $row['dateEntered'];
				$c_bonus_code = $row['strBonusCode'];

				if ($c_bonus_code == '')
				{
					$c_bonus_code = 'No code';
				}


				$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_city, $c_date, $c_bonus_code));

				// $content .= '<tr><td>'.$c_first_name.'</td><td>'.$c_last_name.'</td><td>'.$c_email.'</td><td>'.$c_phone.'</td><td>'.$c_city.'</td><td>'.$c_date.'</td><td>'.$c_bonus_code.'</td></tr>';
			}
		}
		
		mysqli_free_result($result);	

		break;
	}
	case 'pond2018':
	{
		$export->addColumn("string"); //fn
		$export->addColumn("string");//ln
		$export->addColumn("string"); //email
		$export->addColumn("string");//phone
		$export->addColumn("string"); //city
		//$export->addColumn("string"); //postal code
		$export->addColumn("string");//date
		$export->addColumn("string"); //bonus code
		$export->filename = "Polaris-RZR";

		$export->addRow(array("First Name", "Last Name", "Email", "Phone", "City", "Date Entered", "Bonus Code"));

		$sql = "SELECT strFirstName, strLastName, strCity, strEmail, strPhone, dateEntered, strBonusCode from tblKioskPeterPond2018 left join tblKioskBonusCodesPeterPond2018 on tblKioskPeterPond2018.intBonusCode = tblKioskBonusCodesPeterPond2018.intBonusCodeID";
		$result = getFromDatabase($sql);
		if (mysqli_num_rows($result) == 0)
		{
			// $content = "<p>Sorry no entries found</p>";
			$export->addRow(array('Sorry no entries found', '', '','','','',''));

		}
		else
		{

			while ($row = mysqli_fetch_array($result) )
			{
				$c_first_name = $row['strFirstName'];
				$c_last_name = $row['strLastName'];
				$c_email = $row['strEmail'];
				$c_phone = $row['strPhone'];
				$c_city = $row['strCity'];
				//$c_pcode = $row['strPostalCode'];
				$c_date = $row['dateEntered'];
				$c_bonus_code = $row['strBonusCode'];

				if ($c_bonus_code == '')
				{
					$c_bonus_code = 'No code';
				}


				$export->addRow(array($c_first_name, $c_last_name, $c_email, $c_phone, $c_city, $c_date, $c_bonus_code));

				// $content .= '<tr><td>'.$c_first_name.'</td><td>'.$c_last_name.'</td><td>'.$c_email.'</td><td>'.$c_phone.'</td><td>'.$c_city.'</td><td>'.$c_date.'</td><td>'.$c_bonus_code.'</td></tr>';
			}
		}
		
		mysqli_free_result($result);	

		break;
	}
}

$export->DownloadCSV();