<?php

class RIVERMEDIA extends Controller
{	
	public function __construct()
	{
		parent::__construct();
	}

	public function this_date() {
		date_default_timezone_set('Africa/Johannesburg');
		$dateTime = explode('T', date(DATE_ATOM));
		echo $dateTime[0];
	}

	public function this_time() {
		date_default_timezone_set('Africa/Johannesburg');
		$dateTime = explode('T', date(DATE_ATOM));
		$timeUnFormatted = explode('+', $dateTime[1]);
		$timeFormatted = explode(':', $timeUnFormatted[0]);
		echo $timeFormatted[0].':'.$timeFormatted[1];
	}
	
	public function create_timestamp() {
		date_default_timezone_set('Africa/Johannesburg');
		$dateTime = explode('T', date(DATE_ATOM));
		return $dateTime[0].' '.$dateTime[1];
	}
	
	public function create_guid() {
		function guid() {
			if (function_exists('com_create_guid')){
				return com_create_guid();
			}else{
				mt_srand((double)microtime()*10000);
				$charid = strtoupper(md5(uniqid(rand(), true)));
				$hyphen = chr(45);// "-"
				$uuid = chr(123)// "{"
						.substr($charid, 0, 8).$hyphen
						.substr($charid, 8, 4).$hyphen
						.substr($charid,12, 4).$hyphen
						.substr($charid,16, 4).$hyphen
						.substr($charid,20,12)
						.chr(125);// "}"
				return $uuid;
			}
		};
		$guid = guid();
		return $guid;
	}
	
	public function create_session() {
		if (session_status() == PHP_SESSION_NONE) {
			session_start();
		};
	}
	
	public function login()
	{
		$username = $_POST['username'];
		$password = $_POST['password'];
		$query = $this->db->query("SELECT * FROM tbl_users WHERE username = '".$username."';");
		$this->create_session();
		if ($r = $query->fetch(PDO::FETCH_OBJ))
		{
			if (md5($r->username."_".$password."_".$r->n1."_".$r->n2."_".$r->salt) == $r->md5encrypt) {
				$sessionarray = array($r->username,$r->n1,$r->n2,$r->md5encrypt);
				$_SESSION['login_info'] = $sessionarray;
				$_SESSION['loginstatus'] = "success";
				header('Location: /products');
			}
			else {
				$_SESSION['username'] = $username;
				$_SESSION['loginstatus'] = "fail";
				echo 'fail1';
			};	
		}
		else {
			$_SESSION['loginstatus'] = "fail";
			echo 'fail2';
		};
	}

	public function auth() {
		session_start();
		if (isset($_SESSION['login_info']) && is_array($_SESSION['login_info'])) {
			$username = $_SESSION['login_info'][0];
			$query = $this->db->query("SELECT * FROM tbl_users WHERE username = '".$username."';");

			if ($r = $query->fetch(PDO::FETCH_OBJ)) {
				if ($r->username == $_SESSION['login_info'][0] && $r->n1 == $_SESSION['login_info'][1] && $r->n2 == $_SESSION['login_info'][2] && $r->md5encrypt == $_SESSION['login_info'][3]) {
					$sessionarray = array($r->username,$r->n1,$r->n2,$r->md5encrypt);
					//header('Location: admin');
				}
				else {
					session_destroy();
					header('Location: login');
						die();
				};
			};
		}
		else {
			session_destroy();
			header('Location: login');
			die();
		};
	}

	public function copyright() {
		$thisdate = getdate(date("U"));
		$thisyear = "$thisdate[year]";
		$fromDate = '2016';
		echo '&copy; '.$fromDate;
		if ($thisyear > $fromDate) {
			echo ' - '.$thisyear;
		};
	}

	public function logout() {
		session_start();
		unset($_SESSION['login_info']);
		header('Location: login');
	}
	
	//Catalogue functions
	//MEDIA TYPES
	public function add_media_type() {
		$mediaType = "";
		if (isset($_POST['media_entry'])) {
			$mediaType = $_POST['media_entry'];
		};
		$mediaDescription = "";
		if (isset($_POST['mediaentry_description'])) {
			$mediaDescription = $_POST['mediaentry_description'];
		};
		if (trim($mediaType != "")) {
			$mediaTypeSan = filter_var($mediaType, FILTER_SANITIZE_STRING);
			$mediaDescriptionSan = filter_var($mediaDescription, FILTER_SANITIZE_STRING);
			$query = $this->db->query("SELECT * FROM tbl_media_type WHERE media_type = '".$mediaType."';");
			if ($r = $query->fetch(PDO::FETCH_OBJ)) {
				if ($mediaTypeSan == $r->media_type) {
					$statusMessage = "Media type <b>".$mediaTypeSan."</b> already loaded";
					$_SESSION['statusmessage_fail'] = $statusMessage;
				}
			}
			else {
				$urlSegment = strtoupper(str_replace(" ", "_", $mediaTypeSan));
				$stmt = $this->db->prepare("INSERT INTO tbl_media_type (media_type, url_segment, media_description) VALUES (:media_type, :url_segment, :media_description);");
				$stmt->execute(array( ':media_type' => $mediaTypeSan, ':url_segment' => $urlSegment, ':media_description' => $mediaDescriptionSan));
				$statusMessage = "Media type <b>".$mediaTypeSan."</b> added successfully";
				$_SESSION['statusmessage_success'] = $statusMessage;
			};
		};
	}
	
	public function list_media_types() {
		$rowCounter = 0;
		$query = $this->db->query("SELECT * FROM tbl_media_type ORDER BY media_type ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$rowCounter = $rowCounter + 1;
			echo "<tr id=\"mt_".$r->ID."\" style=\"width:100%!important; border-bottom:1px solid #dddddd;\">";
			echo "  <th scope=\"row\">".$rowCounter."</th>";
			echo "  <td id=\"me_".$r->ID."\">".$r->media_type."</td>";
			echo "  <td>".$r->media_description."</td>";
			echo "  <td>".$r->url_segment."</td>";
			echo "  <td>
			          <ul class=\"nav navbar-right panel_toolbox\" style=\"text-align:center; background-color:transparent!important;\">
					  ";
			if ($r->displayed == "Y") {		    
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;hide\"><i class=\"fa fa-eye\"></i></a></li>";
			}
			else {
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;show\"><i class=\"fa fa-eye-slash\"></i></a></li>";
			};
			echo "			
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Browse\"><i class=\"fa fa-search\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Edit\"><i class=\"fa fa-pencil\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Delete\"><i class=\"fa fa-trash\"></i></a></li>
					    <div class=\"clearfix\"></div>
						<div class=\"display_status\"></div>
					  </ul>
				    </td>";
			echo "</tr>";
		};
		$rowsReturned = $rowCounter." row";
		if ($rowCounter != 1) {
			$rowsReturned .= "s";
		};
		$rowsReturned .= " returned";
		echo "<tr>";
		echo "  <td class=\"rowcounters\">".$rowsReturned."</td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "</tr>";
	}
	
	public function change_media_type_state() {
		$Id = "";
		if (isset($_GET['id'])) {
			$Id = str_replace('mt_', '', $_GET['id']);
		};
		$display = "Y";
		$state = "show";
		if (isset($_GET['state'])) {
			$state = $_GET['state'];
		};
		switch($state) {
			case 'show':
				$display = 'Y';
				break;
			case 'hide':
				$display = 'N';
				break;
			default:
				$display = 'Y';
				break;
		}
		if (is_numeric($Id)) {
			$stmt = $this->db->prepare("UPDATE tbl_media_type SET displayed = :displayed WHERE ID = :ID;");
			$stmt->execute(array( ':displayed' => $display, ':ID' => $Id));
			echo $Id.' '.$state;
		};
	}
	
	public function edit_media_type() {
		$Id = "";
		$isNew = $_GET['isnew'];
		if (isset($_GET['id'])) {
			$Id = str_replace('mt_', '', $_GET['id']);
		};
		$mediaType = "";
		$mediaDescription = "";
		if (isset($_GET['media_entry'])) {
			$mediaType = filter_var($_GET['media_entry'], FILTER_SANITIZE_STRING);
		};
		if (isset($_GET['media_description'])) {
			$mediaDescription = filter_var($_GET['media_description'], FILTER_SANITIZE_STRING);
		};
		$statusMessage = "";
		if (is_numeric($Id) && $mediaType !== "") {
			$stmt = $this->db->prepare("UPDATE tbl_media_type SET media_type = :media_type, media_description = :media_description, url_segment = :url_segment WHERE ID = :ID;");
			$stmt->execute(array( ':media_type' => $mediaType, ':media_description' => $mediaDescription, ':url_segment' => strtoupper(str_replace(" ", "_", $mediaType)), ':ID' => $Id));
			$statusMessage = "<div class=\"status_success\">&nbsp;&nbsp;&nbsp;<i class=\"fa fa-check\"></i>&nbsp;Record updated</div>";
		}
		else {
			if ($isNew == 'n') {
				$statusMessage = "<div class=\"status_fail\">&nbsp;&nbsp;&nbsp;<i class=\"fa fa-times\"></i>&nbsp;MISSING INPUT: record not updated!</div>";
			};
		};
		$query = $this->db->query("SELECT * FROM tbl_media_type WHERE ID = '".$Id."';");
		if ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$_SESSION['edit_media_type'][0] = $r->media_type;
			$_SESSION['edit_media_type'][1] = $r->media_description;
			$_SESSION['edit_media_type'][2] = $statusMessage;
		};
	}
	
	public function delete_media_entry() {
		$Id = "";
		$entryType = "";
		$strippedId = "";
		$productCount = 0;
		if (isset($_GET['id'])) {
			$Id = $_GET['id'];
			if (substr($Id, 0, 3) === "mt_") {
				$entryType = "media_type";
			};
			if (substr($Id, 0, 3) === "mo_") {
				$entryType = "media_origin";
			};
			$strippedId = explode("_", $Id);
		};
		$query = $this->db->query("SELECT COUNT(*) AS record_count FROM tbl_products WHERE ".$entryType." = '".$strippedId[1]."';");
		if ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$productCount = $r->record_count;
			if ($productCount > 0) {
				echo 'Fail';
			}
			else {
				$stmt = $this->db->prepare("DELETE FROM tbl_".$entryType." WHERE ID = :ID;");
				$stmt->execute(array( ':ID' => $strippedId[1]));
				$stmt = $this->db->prepare("UPDATE tbl_products SET ".$entryType." = '0' WHERE ".$entryType." = :ID;");
				$stmt->execute(array( ':ID' => $strippedId[1]));
			};
		};
	}
	
	//MEDIA ORIGINS
	public function selectfield_origin_type() {
		$selected = "";
		if (isset($_SESSION['edit_media_origin'])) {
			$selected = $_SESSION['edit_media_origin'][3];
		};
		$arrSelected = "";
		if (trim($selected !== "")) {
			$arrSelected = explode(",", $selected);
		};
		$query = $this->db->query("SELECT * FROM tbl_origin_types ORDER BY ID ASC;");
		$checkboxCounter = 0;
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			echo '
			  <div>
				<div class="checkbox checkbox-primary">
				  <input id="';
			if (isset($_SESSION['edit_media_origin'])) {
				echo 'edit_';
			};
			echo  'input-'.$checkboxCounter.'" type="checkbox" value="'.$r->origin_type.'"';
			if (is_array($arrSelected)) {
				foreach ($arrSelected as $value) {
					if ($value === $r->origin_type) {
						echo ' checked';
					};
				};
			};
			echo	'>';
			echo  '<label for="';
			if (isset($_SESSION['edit_media_origin'])) {
				echo 'edit_';
			};
			echo 'input-'.$checkboxCounter.'">'.strtoupper($r->origin_type).'</label>
				</div>
			  </div>
			';
			$checkboxCounter = $checkboxCounter + 1;
		};
	}	
	
	public function add_media_origin() {
		$mediaOrigin = "";
		if (isset($_POST['media_entry'])) {
			$mediaOrigin = $_POST['media_entry'];
		};
		$arrOriginType = "";
		if (isset($_POST['arr_origin_type'])) {
			$arrOriginType = filter_var($_POST['arr_origin_type'], FILTER_SANITIZE_STRING);
		};
		$mediaDescription = "";
		if (isset($_POST['mediaentry_description'])) {
			$mediaDescription = $_POST['mediaentry_description'];
		};
		if (trim($mediaOrigin != "")) {
			$mediaOriginSan = filter_var($mediaOrigin, FILTER_SANITIZE_STRING);
			$mediaDescriptionSan = filter_var($mediaDescription, FILTER_SANITIZE_STRING);
			$query = $this->db->query("SELECT * FROM tbl_media_origin WHERE media_origin = '".$mediaOrigin."';");
			if ($r = $query->fetch(PDO::FETCH_OBJ)) {
				if ($mediaOriginSan == $r->media_origin) {
					$statusMessage = "Media origin <b>".$mediaOriginSan."</b> already loaded";
					$_SESSION['statusmessage_fail'] = $statusMessage;
				}
			}
			else {
				$urlSegment = strtoupper(str_replace(" ", "_", $mediaOriginSan));
				$stmt = $this->db->prepare("INSERT INTO tbl_media_origin (media_origin, arr_tag_origin_type, url_segment, media_description) VALUES (:media_origin, :arr_tag_origin_type, :url_segment, :media_description);");
				$stmt->execute(array( ':media_origin' => $mediaOriginSan, ':arr_tag_origin_type' => $arrOriginType, ':url_segment' => $urlSegment, ':media_description' => $mediaDescriptionSan));
				$statusMessage = "Media origin <b>".$mediaOriginSan."</b> added successfully";
				$_SESSION['statusmessage_success'] = $statusMessage;
			};
		};
	}
	
	public function list_media_origins() {
		$rowCounter = 0;
		$query = $this->db->query("SELECT * FROM tbl_media_origin ORDER BY media_origin ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$rowCounter = $rowCounter + 1;
			echo "<tr id=\"mo_".$r->ID."\" style=\"width:100%!important; border-bottom:1px solid #dddddd;\">";
			echo "  <th scope=\"row\">".$rowCounter."</th>";
			echo "  <td id=\"me_".$r->ID."\">".$r->media_origin."</td>";
			echo "  <td>".$r->media_description."</td>";
			echo "  <td>".$r->url_segment."</td>";
			echo "  <td>
			          <ul class=\"nav navbar-right panel_toolbox\" style=\"text-align:center; background-color:transparent!important;\">
					  ";
			if ($r->displayed == "Y") {		    
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;hide\"><i class=\"fa fa-eye\"></i></a></li>";
			}
			else {
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;show\"><i class=\"fa fa-eye-slash\"></i></a></li>";
			};
			echo "			
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Browse\"><i class=\"fa fa-search\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Edit\"><i class=\"fa fa-pencil\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Delete\"><i class=\"fa fa-trash\"></i></a></li>
						<div class=\"display_status\"></div>
						<div class=\"clearfix\"></div>
					  </ul>
				    </td>";
			echo "</tr>";
		};
		$rowsReturned = $rowCounter." row";
		if ($rowCounter != 1) {
			$rowsReturned .= "s";
		};
		$rowsReturned .= " returned";
		echo "<tr>";
		echo "  <td class=\"rowcounters\">".$rowsReturned."</td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "</tr>";
	}
	
	public function change_media_origin_state() {
		$Id = "";
		if (isset($_GET['id'])) {
			$Id = str_replace('mo_', '', $_GET['id']);
		};
		$display = "Y";
		$state = "show";
		if (isset($_GET['state'])) {
			$state = $_GET['state'];
		};
		switch($state) {
			case 'show':
				$display = 'Y';
				break;
			case 'hide':
				$display = 'N';
				break;
			default:
				$display = 'Y';
				break;
		}
		if (is_numeric($Id)) {
			$stmt = $this->db->prepare("UPDATE tbl_media_origin SET displayed = :displayed WHERE ID = :ID;");
			$stmt->execute(array( ':displayed' => $display, ':ID' => $Id));
		};
	}
	
	public function edit_media_origin() {
		$Id = "";
		$isNew = $_GET['isnew'];
		if (isset($_GET['id'])) {
			$Id = str_replace('mo_', '', $_GET['id']);
		};
		$mediaOrigin = "";
		$mediaDescription = "";
		if (isset($_GET['media_entry'])) {
			$mediaOrigin = filter_var($_GET['media_entry'], FILTER_SANITIZE_STRING);
		};
		$arrOriginType = "";
		if (isset($_GET['arr_origin_type'])) {
			$arrOriginType = filter_var($_GET['arr_origin_type'], FILTER_SANITIZE_STRING);
		};
		if (isset($_GET['media_description'])) {
			$mediaDescription = filter_var($_GET['media_description'], FILTER_SANITIZE_STRING);
		};
		$statusMessage = "";
		if (is_numeric($Id) && $mediaOrigin !== "") {
			$stmt = $this->db->prepare("UPDATE tbl_media_origin SET media_origin = :media_origin, arr_tag_origin_type = :arr_tag_origin_type, media_description = :media_description, url_segment = :url_segment WHERE ID = :ID;");
			$stmt->execute(array(':media_origin' => $mediaOrigin, ':arr_tag_origin_type' => $arrOriginType, ':media_description' => $mediaDescription, ':url_segment' => strtoupper(str_replace(" ", "_", $mediaOrigin)), ':ID' => $Id));
			$statusMessage = "<div class=\"status_success\">&nbsp;&nbsp;&nbsp;<i class=\"fa fa-check\"></i>&nbsp;Record updated</div>";
		}
		else {
			if ($isNew == 'n') {
				$statusMessage = "<div class=\"status_fail\">&nbsp;&nbsp;&nbsp;<i class=\"fa fa-times\"></i>&nbsp;MISSING INPUT: record not updated!</div>";
			};
		};
		$query = $this->db->query("SELECT * FROM tbl_media_origin WHERE ID = '".$Id."';");
		if ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$_SESSION['edit_media_origin'][0] = $r->media_origin;
			$_SESSION['edit_media_origin'][1] = $r->media_description;
			$_SESSION['edit_media_origin'][2] = $statusMessage;
			$_SESSION['edit_media_origin'][3] = $r->arr_tag_origin_type;
		};
	}

//EVENTS
	public function add_event() {
		$event = "";
		if (isset($_POST['media_event'])) {
			$event = $_POST['media_event'];
		};
		$eventYear = "";
		if (isset($_POST['event_year'])) {
			$eventYear = $_POST['event_year'];
		};
		if (trim($event != "")) {
			$eventSan = filter_var($event, FILTER_SANITIZE_STRING);
			$eventYearSan = filter_var($eventYear, FILTER_SANITIZE_STRING);
			$query = $this->db->query("SELECT * FROM tbl_event WHERE event_name = '".$eventSan."' AND event_year = '".$eventYearSan."';");
			if ($r = $query->fetch(PDO::FETCH_OBJ)) {
				if ($eventSan == $r->event_name) {
					$statusMessage = "Event <b>".$eventSan." ".$eventYearSan."</b> already loaded";
					$_SESSION['statusmessage_fail'] = $statusMessage;
				}
			}
			else {
				$urlSegment = strtoupper(str_replace(" ", "_", $eventSan));
				$stmt = $this->db->prepare("INSERT INTO tbl_event (event_name, event_year, url_segment) VALUES (:event_name, :event_year, :url_segment);");
				$stmt->execute(array( ':event_name' => $eventSan, ':event_year' => $eventYearSan, ':url_segment' => $urlSegment));
				$statusMessage = "Event <b>".$eventSan." ".$eventYearSan."</b> added successfully";
				$_SESSION['statusmessage_success'] = $statusMessage;
			};
		};
	}
	
	public function list_events() {
		$rowCounter = 0;
		$query = $this->db->query("SELECT * FROM tbl_event ORDER BY event_name ASC, event_year ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$rowCounter = $rowCounter + 1;
			echo "<tr id=\"ev_".$r->ID."\" style=\"width:100%!important; border-bottom:1px solid #dddddd;\">";
			echo "  <th scope=\"row\">".$rowCounter."</th>";
			echo "  <td id=\"me_".$r->ID."\">".$r->event_name."</td>";
			echo "  <td>".$r->event_year."</td>";
			echo "  <td>".$r->url_segment."</td>";
			echo "  <td>
			          <ul class=\"nav navbar-right panel_toolbox\" style=\"text-align:center; background-color:transparent!important;\">
					  ";
			if ($r->displayed == "Y") {		    
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;hide\"><i class=\"fa fa-eye\"></i></a></li>";
			}
			else {
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;show\"><i class=\"fa fa-eye-slash\"></i></a></li>";
			};
			echo "			
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Browse\"><i class=\"fa fa-search\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Edit\"><i class=\"fa fa-pencil\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Delete\"><i class=\"fa fa-trash\"></i></a></li>
						<div class=\"display_status\"></div>
						<div class=\"clearfix\"></div>
					  </ul>
				    </td>";
			echo "</tr>";
		};
		$rowsReturned = $rowCounter." row";
		if ($rowCounter != 1) {
			$rowsReturned .= "s";
		};
		$rowsReturned .= " returned";
		echo "<tr>";
		echo "  <td class=\"rowcounters\">".$rowsReturned."</td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "</tr>";
	}

	public function change_event_state() {
		$Id = "";
		if (isset($_GET['id'])) {
			$Id = str_replace('ev_', '', $_GET['id']);
		};
		$display = "Y";
		$state = "show";
		if (isset($_GET['state'])) {
			$state = $_GET['state'];
		};
		switch($state) {
			case 'show':
				$display = 'Y';
				break;
			case 'hide':
				$display = 'N';
				break;
			default:
				$display = 'Y';
				break;
		}
		if (is_numeric($Id)) {
			$stmt = $this->db->prepare("UPDATE tbl_event SET displayed = :displayed WHERE ID = :ID;");
			$stmt->execute(array( ':displayed' => $display, ':ID' => $Id));
		};
	}
	
	public function edit_event() {
		$Id = "";
		$isNew = $_GET['isnew'];
		if (isset($_GET['id'])) {
			$Id = str_replace('ev_', '', $_GET['id']);
		};
		$event = "";
		$eventYear = "";
		if (isset($_GET['media_event'])) {
			$event = filter_var($_GET['media_event'], FILTER_SANITIZE_STRING);
		};
		if (isset($_GET['event_year'])) {
			$eventYear = filter_var($_GET['event_year'], FILTER_SANITIZE_STRING);
		};
		$statusMessage = "";
		if (is_numeric($Id) && $event !== "") {
			$stmt = $this->db->prepare("UPDATE tbl_event SET event_name = :event_name, event_year = :event_year, url_segment = :url_segment WHERE ID = :ID;");
			$stmt->execute(array( ':event_name' => $event, ':event_year' => $eventYear, ':url_segment' => $eventYear."_".strtoupper(str_replace(" ", "_", $event)), ':ID' => $Id));
			$statusMessage = "<div class=\"status_success\">&nbsp;&nbsp;&nbsp;<i class=\"fa fa-check\"></i>&nbsp;Record updated</div>";
		}
		else {
			if ($isNew == 'n') {
				$statusMessage = "<div class=\"status_fail\">&nbsp;&nbsp;&nbsp;<i class=\"fa fa-times\"></i>&nbsp;MISSING INPUT: record not updated!</div>";
			};
		};
		$query = $this->db->query("SELECT * FROM tbl_event WHERE ID = '".$Id."';");
		if ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$_SESSION['edit_event'][0] = $r->event_name;
			$_SESSION['edit_event'][1] = $r->event_year;
			$_SESSION['edit_event'][2] = $statusMessage;
		};
	}

	public function delete_event() {
		$Id = "";
		$strippedId = "";
		$productCount = 0;
		if (isset($_GET['id'])) {
			$Id = $_GET['id'];
			$strippedId = explode("_", $Id);
		};
		$query = $this->db->query("SELECT COUNT(*) AS record_count FROM tbl_products WHERE media_event = '".$strippedId[1]."';");
		if ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$productCount = $r->record_count;
			if ($productCount > 0) {
				echo 'Fail';
			}
			else {
				$stmt = $this->db->prepare("DELETE FROM tbl_event WHERE ID = :ID;");
				$stmt->execute(array( ':ID' => $strippedId[1]));
				$stmt = $this->db->prepare("UPDATE tbl_products SET media_event = '0' WHERE media_event = :ID;");
				$stmt->execute(array( ':ID' => $strippedId[1]));
			};
		};
	}	

	//PRODUCTS
	public function add_product() {
		$productQuantity = 0;
		$product = "";
		$productId = "";
		if (isset($_POST['product_name'])) {
			$product = filter_var($_POST['product_name'], FILTER_SANITIZE_STRING);
		};
		$productDescription = "";
		if (isset($_POST['product_description'])) {
			$productDescription = filter_var($_POST['product_description'], FILTER_SANITIZE_STRING);
		};
		$mediaType = "";
		if (isset($_POST['media_type'])) {
			$mediaType = $_POST['media_type'];
		};
		$mediaOrigin = "";
		if (isset($_POST['media_origin'])) {
			$mediaOrigin = $_POST['media_origin'];
		};
		$mediaEvent = "";
		if (isset($_POST['media_event'])) {
			$mediaEvent = $_POST['media_event'];
		};
		$arrPhysicalMedium = "";
		if (isset($_POST['physical_medium'])) {
			$arrPhysicalMedium = $_POST['physical_medium'];
		};
		$reference = "";
		if (isset($_POST['reference'])) {
			$reference = filter_var($_POST['reference'], FILTER_SANITIZE_STRING);
		};
		$priceExVat = "0";
		if (isset($_POST['price_ex_vat'])) {
			$priceExVat = filter_var($_POST['price_ex_vat'], FILTER_SANITIZE_STRING);
		};
		$priceIncVat = "0";
		if (isset($_POST['price_inc_vat'])) {
			$priceIncVat = filter_var($_POST['price_inc_vat'], FILTER_SANITIZE_STRING);
		};
		$width = "0";
		if (isset($_POST['width'])) {
			$width = filter_var($_POST['width'], FILTER_SANITIZE_STRING);
		};
		$height = "0";
		if (isset($_POST['height'])) {
			$height = filter_var($_POST['height'], FILTER_SANITIZE_STRING);
		};
		$depth = "0";
		if (isset($_POST['depth'])) {
			$depth = filter_var($_POST['depth'], FILTER_SANITIZE_STRING);
		};
		$weight = "0";
		if (isset($_POST['weight'])) {
			$weight = filter_var($_POST['weight'], FILTER_SANITIZE_STRING);
		};
		$kids = "N";
		if (isset($_POST['kids'])) {
			$kids = filter_var($_POST['kids'], FILTER_SANITIZE_STRING);
		};
		$dynamis = "N";
		if (isset($_POST['dynamis'])) {
			$dynamis = filter_var($_POST['dynamis'], FILTER_SANITIZE_STRING);
		};
		$externalText = "";
		if (isset($_POST['external_url_text'])) {
			$externalText = filter_var($_POST['external_url_text'], FILTER_SANITIZE_STRING);
		};
		$externalUrl = "";
		if (isset($_POST['external_url'])) {
			$externalUrl = $_POST['external_url'];
			if (filter_var($externalUrl, FILTER_VALIDATE_URL) === false) {
				$externalUrl = "";
				$externalText = "";
			};
		};
		$quantity = 0;
		if (isset($_POST['quantity'])) {
			$quantity = filter_var($_POST['quantity'], FILTER_SANITIZE_STRING);
		};
		$thisdate = explode('+', $this->create_timestamp());
		$thisTimestamp = $thisdate[0];
		$guid = "";
		if (trim($product) !== "") {
			$guid = $this->create_guid();
			$insertQuery = "INSERT INTO tbl_products (
							product_name,
							product_description,
							product_quantity,
							media_type,
							media_origin,
							media_event,
							tag_array_physical_medium,
							reference, 
							kids,
							dynamis,
							external_url_label,
							external_url,
							shipping_width,
							shipping_height,
							shipping_depth,
							shipping_weight,
							created,
							string_id)
							VALUES (
							:product_name,
							:product_description,
							:product_quantity,
							:media_type,
							:media_origin,
							:media_event,
							:tag_array_physical_medium,
							:reference, 
							:kids,
							:dynamis,
							:external_url_label,
							:external_url,
							:shipping_width,
							:shipping_height,
							:shipping_depth,
							:shipping_weight, 
							:created, 
							:string_id);
			";
			$stmt = $this->db->prepare($insertQuery);
			$stmt->execute(array(':product_name' => $product, 
								 ':product_description' => $productDescription, 
								 ':product_quantity' => $productQuantity,
								 ':media_type' => $mediaType,
								 ':media_origin' => $mediaOrigin,
								 ':media_event' => $mediaEvent,
								 ':tag_array_physical_medium' => $arrPhysicalMedium,
								 ':reference' => $reference,
								 ':kids' => $kids,
								 ':dynamis' => $dynamis,
								 ':external_url_label' => $externalText,
								 ':external_url' => $externalUrl,
								 ':shipping_width' => $width,
								 ':shipping_height' => $height,
								 ':shipping_depth' => $depth,
								 ':shipping_weight' => $weight,
								 ':created' => $thisTimestamp,
								 ':string_id' => $guid
								 )
							);
			$query = $this->db->query("SELECT LAST_INSERT_ID() AS product_id;");	
			if ($r = $query->fetch(PDO::FETCH_OBJ)) {
				$productId = $r->product_id;
				$stmt = $this->db->prepare("INSERT INTO tbl_price (product_id, price_ex_vat, activate) VALUES (:product_id, :price_ex_vat, :activate);");
				$stmt->execute(array( ':product_id' => $productId, ':price_ex_vat' => $priceExVat, ':activate' => 'Y'));
				$stmt = $this->db->prepare("INSERT INTO tbl_product_quantities (product_id, quantity) VALUES (:product_id, :quantity);");
				$stmt->execute(array(':product_id' => $productId, ':quantity' => $quantity));
			};
			echo '<div id="product_id" class="hidden">'.$productId.'</div>';				
			$statusMessage = "product <b>".$product."</b> added successfully";
			$_SESSION['statusmessage_success'] = $statusMessage;
		};
	}
	
	public function edit_product() {
		$isNew = "y";
		if (isset($_GET['isnew'])) {
			$isNew = $_GET['isnew'];
		};
		$id = "";
		if (isset($_GET['id'])) {
			$id = $_GET['id'];
		};
		
		if (is_numeric($id)) {
			$query = $this->db->query("SELECT * FROM tbl_products WHERE ID = '".$id."';");
			if ($r = $query->fetch(PDO::FETCH_OBJ)) {
				$_SESSION['edit_product'][0] = $r->ID;
				$_SESSION['edit_product'][1] = $r->product_name;
				$_SESSION['edit_product'][2] = $r->product_description;
				$_SESSION['edit_product'][3] = $r->media_type;
				$_SESSION['edit_product'][4] = $r->media_origin;
				$_SESSION['edit_product'][5] = $r->media_event;
				$_SESSION['edit_product'][6] = $r->tag_array_physical_medium;
				$_SESSION['edit_product'][7] = $r->shipping_width;
				$_SESSION['edit_product'][8] = $r->shipping_height;
				$_SESSION['edit_product'][9] = $r->shipping_depth;
				$_SESSION['edit_product'][10] = $r->shipping_weight;
				$_SESSION['edit_product'][11] = $r->kids;
				$_SESSION['edit_product'][12] = $r->string_id;
				$_SESSION['edit_product'][13] = $r->reference;
				$_SESSION['edit_product'][14] = $r->dynamis;
				$_SESSION['edit_product'][15] = $r->external_url_label;
				$_SESSION['edit_product'][16] = $r->external_url;
				$imageName = $r->string_id;
				$imageName = str_replace('{', '', $r->string_id);
				$imageName = str_replace('}', '', $imageName);
				if (file_exists("public/media/product_images/working_image_".strtolower($imageName).".jpg")) {
					unlink("public/media/product_images/working_image_".strtolower($imageName).".jpg");
				};
			};
		};
	}
	
	public function update_product() {
		if (isset($_POST['id'])) {
			$id = $_POST['id'];
		};
		$productName = "";
		if (isset($_POST['product_name'])) {
			$productName = filter_var($_POST['product_name'], FILTER_SANITIZE_STRING);
		};
		$productDescription = "";
		if (isset($_POST['product_description'])) {
			$productDescription = filter_var($_POST['product_description'], FILTER_SANITIZE_STRING);
		};
		$mediaType = "";
		if (isset($_POST['media_type'])) {
			$mediaType = $_POST['media_type'];
		};
		$mediaOrigin = "";
		if (isset($_POST['media_origin'])) {
			$mediaOrigin = $_POST['media_origin'];
		};
		$mediaEvent = "";
		if (isset($_POST['media_event'])) {
			$mediaEvent = $_POST['media_event'];
		};
		$arrPhysicalMedia = "";
		if (isset($_POST['physical_medium'])) {
			$arrPhysicalMedia = $_POST['physical_medium'];
		};
		$reference = "";
		if (isset($_POST['reference'])) {
			$reference = filter_var($_POST['reference'], FILTER_SANITIZE_STRING);
		};
		$width = "0.00";
		if (isset($_POST['shipping_width'])) {
			$width = $_POST['shipping_width'];
		};
		$height = "0.00";
		if (isset($_POST['shipping_height'])) {
			$height = $_POST['shipping_height'];
		};
		$depth = "0.00";
		if (isset($_POST['shipping_depth'])) {
			$depth = $_POST['shipping_depth'];
		};
		$weight = "0.00";
		if (isset($_POST['shipping_weight'])) {
			$weight = $_POST['shipping_weight'];
		};
		$kidsItem = "N";
		if (isset($_POST['kids_item'])) {
			$kidsItem = $_POST['kids_item'];
		};
		
		if (trim($productName) != "" ) {
			$updateQuery = "UPDATE tbl_products SET 
			                product_name = :product_name, 
							product_description = :product_description, 
							media_type = :media_type, 
							media_origin = :media_origin, 
							media_event = :media_event, 
							tag_array_physical_medium = :tag_array_physical_medium, 
							reference = :reference, 
							kids = :kids, 
							shipping_width = :shipping_width, 
							shipping_height = :shipping_height, 
							shipping_depth = :shipping_depth, 
							shipping_weight = :shipping_weight  
							WHERE ID = :ID;
							";
			$stmt = $this->db->prepare($updateQuery);
			$stmt->execute(array(':product_name' => $productName,
				':product_description' => $productDescription,
				':media_type' => $mediaType,
				':media_origin' => $mediaOrigin,
				':media_event' => $mediaEvent,
				':tag_array_physical_medium' => $arrPhysicalMedia,
				':reference' => $reference, 
				':kids' => $kidsItem,
				':shipping_width' => $width,
				':shipping_height' => $height,
				':shipping_depth' => $depth,
				':shipping_weight' => $weight,
				':ID' => intval(trim($id))));
		};
	}
	
	public function list_products() {
		$rowCounter = 0;
		$query = $this->db->query("SELECT ID, product_name, CONCAT(LEFT(product_description, 30), '...') AS description, displayed FROM tbl_products ORDER BY product_name ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$quantity = 0;
			$queryInner = $this->db->query("SELECT quantity FROM tbl_product_quantities WHERE product_id = '".$r->ID."' ORDER BY ID DESC LIMIT 0,1;");
			while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
				$quantity = $rInner->quantity;
			};
			$rowCounter = $rowCounter + 1;
			echo "<tr id=\"pr_".$r->ID."\" style=\"width:100%!important; border-bottom:1px solid #dddddd;\">";
			echo "  <th scope=\"row\">".$rowCounter."</th>";
			echo "  <td id=\"me_".$r->ID."\">".$r->product_name."</td>";
			echo "  <td>".$r->description."</td>";
			echo "  <td class='pull-right'>".number_format($quantity)."</td>";
			echo "  <td>
			          <ul class=\"nav navbar-right panel_toolbox\" style=\"text-align:center; background-color:transparent!important;\">
					  ";
			if ($r->displayed == "Y") {		    
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;hide\"><i class=\"fa fa-eye\"></i></a></li>";
			}
			else {
					echo "	<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Click&nbsp;to&nbsp;show\"><i class=\"fa fa-eye-slash\"></i></a></li>";
			};
			echo "			
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Update&nbsp;Quantity\nView&nbsp;History\"><i class=\"fa fa-cubes\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Edit\"><i class=\"fa fa-pencil\"></i></a></li>
						<li><a href=\"javascript:void(0);\" data-toggle=\"tooltip\" title=\"Delete\"><i class=\"fa fa-trash\"></i></a></li>
						<div class=\"display_status\"></div>
						<div class=\"clearfix\"></div>
					  </ul>
				    </td>";
			echo "</tr>";
		};
		$rowsReturned = $rowCounter." row";
		if ($rowCounter != 1) {
			$rowsReturned .= "s";
		};
		$rowsReturned .= " returned";
		echo "<tr>";
		echo "  <td class=\"rowcounters\">".$rowsReturned."</td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "  <td></td>";
		echo "</tr>";
	}
	
	public function change_product_state() {
		$Id = "";
		if (isset($_GET['id'])) {
			$Id = str_replace('pr_', '', $_GET['id']);
		};
		$display = "Y";
		$state = "show";
		if (isset($_GET['state'])) {
			$state = $_GET['state'];
		};
		switch($state) {
			case 'show':
				$display = 'Y';
				break;
			case 'hide':
				$display = 'N';
				break;
			default:
				$display = 'Y';
				break;
		}
		if (is_numeric($Id)) {
			$stmt = $this->db->prepare("UPDATE tbl_products SET displayed = :displayed WHERE ID = :ID;");
			$stmt->execute(array( ':displayed' => $display, ':ID' => $Id));
			echo $Id.' '.$state;
		};
	}

	public function list_quantities() {
		$rowCounter = 0;
		if (isset($_GET['product_id']))	{
			$productId = $_GET['product_id'];
			$query = $this->db->query("SELECT quantity, transaction_type, transaction_timestamp FROM tbl_product_quantities WHERE product_id = '".$productId."' ORDER BY ID DESC, transaction_timestamp ASC LIMIT 0,5;");
			while ($r = $query->fetch(PDO::FETCH_OBJ)) {
				$rowCounter = $rowCounter + 1;
				echo '<tr style="width:100%!important; border-bottom:1px solid #dddddd;">';
				echo '  <th scope="row">'.$rowCounter.'</th>';
				echo '  <td class="pull-right" style="border-top:none;">'.number_format($r->quantity).'</td>';
				echo '  <td>'.$r->transaction_type.'</td>';
				echo '  <td>'.$r->transaction_timestamp.'</td>';
				echo '</tr>';
			};
		};
	}

	public function update_quantity() {
		$productId = "";
		if (isset($_GET['product_id'])) {
			$productId = $_GET['product_id'];
		};
		$orderId = 0;
		if (isset($_GET['order_id'])) {
			$orderId = $_GET['order_id'];
		};
		$transactionType = 3;
		if (isset($_GET['transaction_type']) && is_numeric($_GET['transaction_type'])) {
			$transactionType = $_GET['transaction_type'];
			switch($transactionType) {
				case 0:
					$transactionType = "INITIAL";
					break;
				case 1:
					$transactionType = "ORDER";
					break;
				case 2:
					$transactionType = "REVERSAL";
					break;
				case 3:
					$transactionType = "CAPTURE";
					break;
				default:
					$transactionType = "INITIAL";
					break;
			};
		};
		$quantity = 0;
		if (isset($_GET['quantity'])) {
			$quantity = $_GET['quantity'];
		};
		if (is_numeric($productId) && $transactionType !== "") {
			$stmt = $this->db->prepare("INSERT INTO tbl_product_quantities (product_id, order_id, quantity, transaction_type) VALUES (:product_id, :order_id, :quantity, :transaction_type);");
			$stmt->execute(array(':product_id' => $productId, ':order_id' => $orderId, ':quantity' => $quantity, ':transaction_type' => $transactionType));
		};
		echo 'updated';
	}

	public function get_vat() {
		$query = $this->db->query("SELECT * FROM tbl_vat;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			return $r->vat_rate;
		};
	}
	
	public function selectfield_media_type() {
		$selected = "";
		if (isset($_SESSION['edit_product'])) {
			$selected = $_SESSION['edit_product'][3];
		};
		$query = $this->db->query("SELECT * FROM tbl_media_type WHERE displayed = 'Y' ORDER BY media_type ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			echo '<option value="'.$r->ID.'"';
			if ($r->ID === $selected) {
				echo ' selected';
			};
			echo '>'.$r->media_type.'</option>';
		};
	}

	public function selectfield_media_origin() {
		$selected = "";
		if (isset($_SESSION['edit_product'])) {
			$selected = $_SESSION['edit_product'][4];
		};
		$query = $this->db->query("SELECT * FROM tbl_media_origin WHERE displayed = 'Y' ORDER BY media_origin ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			echo '<option value="'.$r->ID.'"';
			if ($r->ID === $selected) {
				echo ' selected';
			};
			echo '>'.$r->media_origin.'</option>';
		};
	}

	public function selectfield_event() {
		$selected = "";
		if (isset($_SESSION['edit_product'])) {
			$selected = $_SESSION['edit_product'][5];
		};
		$query = $this->db->query("SELECT * FROM tbl_event WHERE displayed = 'Y' ORDER BY event_name ASC, event_year DESC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			echo '<option value="'.$r->ID.'"';
			if ($r->ID === $selected) {
				echo ' selected';
			};
			echo '>'.$r->event_year.' | '.$r->event_name.'</option>';
		};
	}
	
	public function selectfield_physical_medium() {
		$selected = "";
		if (isset($_SESSION['edit_product'])) {
			$selected = $_SESSION['edit_product'][6];
		};
		$arrSelected = "";
		if (trim($selected !== "")) {
			$arrSelected = explode(",", $selected);
		};
		$query = $this->db->query("SELECT * FROM tbl_physical_medium ORDER BY medium_name ASC;");
		$checkboxCounter = 0;
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			echo '
			  <div>
				<div class="checkbox checkbox-primary">
				  <input id="';
			if (isset($_SESSION['edit_product'])) {
				echo 'edit_';
			};
			echo  'input-'.$checkboxCounter.'" type="checkbox" value="'.$r->url_segment.'"';
			if (is_array($arrSelected)) {
				foreach ($arrSelected as $value) {
					if ($value === $r->url_segment) {
						echo ' checked';
					};
				};
			};
			echo	'>';
			echo  '<label for="';
			if (isset($_SESSION['edit_product'])) {
				echo 'edit_';
			};
			echo 'input-'.$checkboxCounter.'">'.strtoupper($r->medium_name).'</label>
				</div>
			  </div>
			';
			$checkboxCounter = $checkboxCounter + 1;
		};
	}
	
	public function show_price() {
		$productId = "";
		$vatRate = $this->get_vat();
		if (isset($_SESSION['edit_product'])) {
			$productId = $_SESSION['edit_product'][0];
		};
		if (is_numeric($productId)) {
			$query = $this->db->query("SELECT * FROM tbl_price WHERE product_id = '".$productId."' ORDER BY created DESC LIMIT 0,1;");
			while ($r = $query->fetch(PDO::FETCH_OBJ)) {
				return $r->price_ex_vat."|".substr($r->created, 0, -3);
			};
		};
	}
	
	public function add_price() {
		$productId = "";
		if (isset($_GET['product_id'])) {
			$productId = $_GET['product_id'];
		};
		$newPrice = $_GET['new_price_ex_vat'];
		if (is_numeric($productId)) {
			$stmt = $this->db->prepare("INSERT INTO tbl_price (product_id, price_ex_vat) VALUES (:product_id, :price_ex_vat);");
			$stmt->execute(array(':product_id' => $productId, ':price_ex_vat' => $newPrice));
			echo 'New price applied<div class="hidden" id="newpricetimestamp">'.$this->get_price_timestamp($productId).'</div>';
		};
	}
	
	public function get_price_timestamp($productId) {
		if (is_numeric($productId)) {
			$query = $this->db->query("SELECT created FROM tbl_price WHERE product_id = '".$productId."' ORDER BY created DESC LIMIT 0,1;");
			while ($r = $query->fetch(PDO::FETCH_OBJ)) {
				return substr($r->created, 0, -3);
			};
		};
	}
	
	public function image_upload() {
		$guid = "";
		$imageName = "";
		if (isset($_GET['guid'])) {
			$guid = $_GET['guid'];
			$imageName = str_replace("}", "", str_replace("{", '', strtolower($guid)));
		};
		$thumbExists = "N";
		if (file_exists("public/media/product_images/tn_".$guid.".jpg")) {
			list($tnWidth, $tnHeight) = getimagesize("public/media/product_images/tn_".$guid.".jpg");
			$thumbExists = "Y";
		};
		
	}
	
	public function upload_image() {
		if ($_FILES["file"]["error"] > 0)
		  {
		  echo "Error: " . $_FILES["file"]["error"] . "<br>";
		  }
		else
		  {
		  echo "Upload: " . $_FILES["file"]["name"] . "<br>";
		  echo "Type: " . $_FILES["file"]["type"] . "<br>";
		  echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
		  echo "Stored in: " . $_FILES["file"]["tmp_name"];
		  };
	}
	
	public function upload() {
		$status = "success";
		$target = "public/media/product_images/"; 
		$recordId = strtolower($_GET['upload_guid']);
		
		$target = $target . basename($_FILES['image_upload']['name']);
		$ok=1; 
		if(move_uploaded_file($_FILES['image_upload']['tmp_name'], "public/media/product_images/".strtolower($recordId).".jpg")) {
			chmod(getcwd()."/public/media/product_images/".strtolower($recordId).".jpg", 0777);
			$status = "success";
		}
		else {
		 $status = "fail";
		};
		$originalImage = strtolower($recordId).".jpg";
		$workingImage = "working_image_".strtolower($recordId).".jpg";
		//$largeImg = "lg_".strtolower($recordId).".jpg";
		//$mediumImg = "me_".strtolower($recordId).".jpg";
		//$smallImg = "sm_".strtolower($recordId).".jpg";
		//$thumbnail = "tn_".strtolower($recordId).".jpg";
		
		include_once('includes/resize_image.php');
		
		//resize_image("public/media/product_images/".$originalImage, "public/media/product_images/".$largeImg, 1000, false);
		resize_image("public/media/product_images/".$originalImage, "public/media/product_images/".$workingImage, 800, false);
		//resize_image("public/media/product_images/".$originalImage, "public/media/product_images/".$smallImg, 500, false);
		//resize_image("public/media/product_images/".$originalImage, "public/media/product_images/".$thumbnail, 232, false);
		chmod(getcwd()."/public/media/product_images/working_image_".strtolower($recordId).".jpg", 0777);
		unlink("public/media/product_images/".$originalImage);
		
		//$q = "UPDATE tbl_item SET blur_img = 'N' WHERE guid = '".$recordId."';";
		//mysqli_query($dbc, $q);
		
		header('Location: image_upload?guid='.strtolower($recordId).'&edit=y');
	}
	
	public function crop_image() {
		// Create a blank image
		//echo $_GET['image_name'];
		$fileName = explode("?", strtolower($_GET['image_name']));
		$ini_filename = $fileName[0].".jpg";
		$imageName = strtolower(str_replace("public/media/product_images/working_image_", "", $ini_filename));
		
		$mediumImg = "me_".$imageName;
		$smallImg = "sm_".$imageName;
		$thumbnail = "tn_".$imageName;
		
		$im = imagecreatefromjpeg($ini_filename);
		$ini_size = getimagesize($ini_filename);
		$ini_x_size = $ini_size[0];
		$ini_y_size = $ini_size[1];
		$xOffset = $_GET['x1'];
		$yOffset = $_GET['y1'];
		$selectionWidth = $_GET['x2'];
		$selectionHeight = $_GET['y2'];
		//the minimum of xlength and ylength to crop.
		$crop_measure = min($ini_x_size, $ini_y_size);
		
		// Set the content type header - in this case image/jpeg
		//header('Content-Type: image/jpeg');
		
		$to_crop_array = array('x' => $xOffset , 'y' => $yOffset, 'width' => $selectionWidth, 'height' => $selectionHeight);
		$thumb_im = imagecrop($im, $to_crop_array);
		
		include_once('includes/resize_image.php');
		
		//echo getcwd()."/public/media/product_images/working_image_".$imageName;
		chmod(getcwd()."/public/media/product_images/working_image_".$imageName, 0777);
		//chown(getcwd()."/public/media/product_images/working_image_".$imageName, 'user');
		//chgrp(getcwd()."/public/media/product_images/working_image_".$imageName, 'user');
		clearstatcache();
		imagejpeg($thumb_im, getcwd()."/public/media/product_images/working_image_".$imageName, 100);
		
		resize_image(getcwd()."/public/media/product_images/working_image_".$imageName, getcwd()."/public/media/product_images/".$mediumImg, 600, false);
		resize_image(getcwd()."/public/media/product_images/working_image_".$imageName, getcwd()."/public/media/product_images/".$smallImg, 400, false);
		resize_image(getcwd()."/public/media/product_images/working_image_".$imageName, getcwd()."/public/media/product_images/".$thumbnail, 330, false);
		unlink("public/media/product_images/working_image_".$imageName);
	    echo '<div class="hidden" id="img_proc_msg">Success</div>';
	}
	/*
	public function add_shipping_rate() {
		$isNew = "y";
		if (isset($_REQUEST['isnew'])) {
			$isNew = $_REQUEST['isnew'];
		};
		$newRate = "0.00";
		if (isset($_POST['new_rate'])) {
			$newRate = $_POST['new_rate'];
		};
		
		if ($newRate !== 0.00 && $isNew !== "y") {
			$stmt = $this->db->prepare("INSERT INTO tbl_shipping_rate_per_kg (price_per_kg) VALUES (:price_per_kg);");
			$stmt->execute(array(':price_per_kg' => $newRate));
			echo '<div class="hidden" id="newshippingratetimestamp">'.$this->get_shippingrate_timestamp().'</div>';
		};
	}
	*/
	public function add_shipping_rate() {
		$isNew = "y";
		if (isset($_REQUEST['isnew'])) {
			$isNew = $_REQUEST['isnew'];
		};
		$firstKg = "0.00";
		if (isset($_POST['first_kg'])) {
			$firstKg = filter_var($_POST['first_kg'], FILTER_SANITIZE_STRING);
		};
		$perAdditionalKg = "0.00";
		if (isset($_POST['per_additional_kg'])) {
			$perAdditionalKg = filter_var($_POST['per_additional_kg'], FILTER_SANITIZE_STRING);
		};
		if ($firstKg !== 0.00 && $perAdditionalKg !== 0.00 && $isNew !== "y") {
			$stmt = $this->db->prepare("INSERT INTO tbl_shipping (first_kg, per_additional_kg) VALUES (:first_kg, :per_additional_kg);");
			$stmt->execute(array(':first_kg' => $firstKg, ':per_additional_kg' => $perAdditionalKg));
			echo '<div class="hidden" id="newshippingratetimestamp">'.$this->get_shippingrate_timestamp().'</div>';
		};
	}
/*	
	public function get_shippingrate_timestamp() {
		$query = $this->db->query("SELECT created FROM tbl_shipping_rate_per_kg ORDER BY created DESC LIMIT 0,1;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			return substr($r->created, 0, -3);
		};
	}
*/
	public function get_shippingrate_timestamp() {
		$query = $this->db->query("SELECT created FROM tbl_shipping ORDER BY created DESC LIMIT 0,1;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			return substr($r->created, 0, -3);
		};
	}
/*	
	public function shippingrate_history() {
		$recordCounter = 0;
		$query = $this->db->query("SELECT created, price_per_kg FROM tbl_shipping_rate_per_kg ORDER BY created DESC LIMIT 0,5;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$recordCounter++;
			echo '<div';
			if ($recordCounter === 1) {
				echo ' style="font-weight:bold;"';
			};
			echo '><div class="col-lg-7 col-md-7 col-sm-7 col-xs-7">'.substr($r->created, 0, -3).'</div><div style="text-align:right;" class="col-lg-5 col-md-5 col-sm-5 col-xs-5">'.$r->price_per_kg.'</div><div class="clearfix"></div></div>';
		};
	}
*/
	public function shippingrate_history() {
		$recordCounter = 0;
		$query = $this->db->query("SELECT created, first_kg, per_additional_kg FROM tbl_shipping ORDER BY created DESC LIMIT 0,5;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$recordCounter++;
			/*
			echo '<div';
			if ($recordCounter === 1) {
				echo ' style="font-weight:bold;"';
			};
			echo '><div class="col-lg-5 col-md-5 col-sm-5 col-xs-5">'.substr($r->created, 0, -3).'</div><div style="text-align:right;" class="col-lg-4 col-md-4 col-sm-4 col-xs-4">'.$r->first_kg.'</div><div class="clearfix"></div></div>';
			*/
			echo '
			<tr';
			if ($recordCounter === 1) {
				echo ' style="font-weight:bold;"';
			};
			echo '>
			  <td>'.substr($r->created, 0, -3).'</td>
			  <td><div class="pull-right">'.number_format($r->first_kg, 2).'</div></td>
			  <td><div class="pull-right">'.number_format($r->per_additional_kg, 2).'</div></td>
			</tr>
			';
			
		};
	}
	
	public function delete_product() {
		$Id = "";
		$stringId = "";
		$imageMe = "";
		$imageSm = "";
		$imageTn = "";
		$ordersExist = false;
		if (isset($_GET['id'])) {
			if (is_numeric($_GET['id'])) {
				$Id = $_GET['id'];
				//Check for orders on this product before attempting to delete
				$query = $this->db->query("SELECT COUNT(*) AS order_count FROM tbl_order_lines WHERE product_id = '".$Id."';");
				while ($r = $query->fetch(PDO::FETCH_OBJ)) {
					if ($r->order_count > 0) {
						$ordersExist = true;
					};
				};
				if ($ordersExist === false) {
					$query = $this->db->query("SELECT string_id FROM tbl_products WHERE ID = '".$Id."';");
					while ($r = $query->fetch(PDO::FETCH_OBJ)) {
						$stringId = $r->string_id;
						$imageMe = "public/media/product_images/me_".strtolower(str_replace("}", "", str_replace("{", "", $stringId))).".jpg";
						$imageSm = "public/media/product_images/sm_".strtolower(str_replace("}", "", str_replace("{", "", $stringId))).".jpg";
						$imageTn = "public/media/product_images/tn_".strtolower(str_replace("}", "", str_replace("{", "", $stringId))).".jpg";
						if (file_exists($imageMe)) {
							unlink($imageMe);
						};
						if (file_exists($imageSm)) {
							unlink($imageSm);
						};
						if (file_exists($imageTn)) {
							unlink($imageTn);
						};
					};
					$stmt = $this->db->prepare("DELETE FROM tbl_products WHERE ID = :ID;");
					$stmt->execute(array(':ID' => $Id));
				};
			};
		};
	}
	
	public function list_order_states() {
		$arrOrderStates = "";
		$query = $this->db->query("SELECT ID, state FROM tbl_order_states ORDER BY ID ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			if (trim($arrOrderStates) !== "") {
				$arrOrderStates = $arrOrderStates.",";
			};
			$arrOrderStates = $arrOrderStates.$r->ID."|".$r->state;
		};
		return $arrOrderStates;
	}
	
	public function alert_orders() {
		$counter = 0;
		$query = $this->db->query("SELECT COUNT(*) AS record_count FROM tbl_orders WHERE read_by_admin = 'N' AND order_state = 'ORDER';");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$counter = $r->record_count;
		};
		if ($counter > 0) {
			echo $counter;
		};
	}
	
	public function alert_users() {
		$counter = 0;
		$query = $this->db->query("SELECT COUNT(*) AS record_count FROM tbl_customers WHERE read_by_admin = 'N';");	
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$counter = $r->record_count;
		};
		if ($counter > 0) {
			echo $counter;
		};
	}
	
	public function show_new_users() {
		$counter = 0;
		$query = $this->db->query("SELECT UPPER(CONCAT_WS(' ', title, firstname, lastname)) AS customer_name, phone, email FROM tbl_customers WHERE read_by_admin = 'N' ORDER BY ID DESC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$counter = $counter + 1;
			echo '
				<div>'.$r->customer_name.'<br />'.$r->phone.' '.$r->email.'</div><br />
			';
		};
		if ($counter === 0) {
			echo '<div>No new users have registered</div>';
		};
		$this->db->query("UPDATE tbl_customers SET read_by_admin = 'Y';");
	}
	
	public function insert_latest_orders() { //Insert new orders at top of list in real time
		$rowCounter = 0;
		$latestId = 0;
		$currentState = "";
		$arrOrderStates = explode(",", $this->list_order_states());
		//$arrPaidStates = array_diff($arrOrderStates, ['1|AWAITING CHEQUE PAYMENT', '8|PAYMENT ERROR', '10|AWAITING EFT PAYMENT', '11|AWAITING PAYPAL PAYMENT']);
		$arrPaidStates = $arrOrderStates;
		if (isset($_GET['latest_id'])) {
			$latestId = filter_var($_GET['latest_id'], FILTER_SANITIZE_STRING);
		};
		$q = "
		SELECT LPAD(tbl_orders.ID, 6, '0') as order_id, 
		tbl_orders.created AS created,
		tbl_orders.customer_order_number AS customer_order_number,
		tbl_orders.guid AS guid,
		tbl_orders.price_inc_vat AS price_inc_vat, 
		tbl_orders.read_by_admin AS read_by_admin, 
		CONCAT_WS(' ', tbl_customers.title, tbl_customers.firstname, tbl_customers.lastname) AS customer_name
		FROM tbl_orders, tbl_customers
		WHERE tbl_customers.ID = tbl_orders.customer_id
		AND tbl_orders.order_state = 'ORDER'
		AND tbl_orders.ID > '".$latestId."' 
		ORDER BY tbl_orders.ID ASC LIMIT 0,1;
		";	
		$query = $this->db->query($q);
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$queryInner = $this->db->query("SELECT state_id, created FROM tbl_order_progress WHERE order_id = '".(int)$r->order_id."' ORDER BY ID DESC LIMIT 0,1;");
			while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
				$currentState = $rInner->state_id;
			};
			echo '<tr id="or_'.$r->order_id.'" style="width:100%!important; border-bottom:1px solid #dddddd;"';
			if ($r->read_by_admin === 'N') {
				echo ' class="bold pitch_black"';
			};
			echo '>';
			echo '  <td>'.$r->created.'</td>';
			echo '  <td><div class="pull-right">'.$r->order_id.'</div></td>';
			echo '  <td><div class="pull-right">'.strtoupper($r->customer_order_number).'</div></td>';
			echo '  <td>'.strtoupper($r->customer_name).'</td>';
			echo '  <td><div class="pull-right">'.number_format($r->price_inc_vat, 2).'</div></td>';
			echo '	<td>';
			$stateRecord = "";
			$stateId = "";
			$state = "";
			echo '		<input type="hidden" name="" id="" value="" />';
			echo '		<span>';
			echo '			<select name="os_'.$r->order_id.'" id="os_'.$r->order_id.'" class="state_change">';
			if ($currentState === '2') {
				foreach($arrPaidStates as $key => $value) {
					$stateRecord = explode("|", $value);
					$stateId = $stateRecord[0];
					$state = $stateRecord[1];
					echo '				<option value="'.$stateId.'"';
					if ($stateId === $currentState) {
						echo ' selected';
					};
					echo '>'.$state.'</option>';
				};
			};
			
			if ($currentState === '10') {
				//$arrEftStates = array_diff($arrOrderStates, ['1|AWAITING CHEQUE PAYMENT', '8|PAYMENT ERROR', '11|AWAITING PAYPAL PAYMENT']);
				$arrEftStates = $arrOrderStates;
				foreach($arrEftStates as $key => $value) {
					$stateRecord = explode("|", $value);
					$stateId = $stateRecord[0];
					$state = $stateRecord[1];
					echo '				<option value="'.$stateId.'"';
					if ($stateId === $currentState) {
						echo ' selected';
					};
					echo '>'.$state.'</option>';
				};
			};
			
			if ($currentState !== '2' && $currentState !== '10') {
				//$arrOtherStates = array_diff($arrOrderStates, ['1|AWAITING CHEQUE PAYMENT', '2|PAYMENT ACCEPTED', '8|PAYMENT ERROR', '10|AWAITING EFT PAYMENT', '11|AWAITING PAYPAL PAYMENT']);
				$arrOtherStates = $arrOrderStates;
				foreach($arrOtherStates as $key => $value) {
					$stateRecord = explode("|", $value);
					$stateId = $stateRecord[0];
					$state = $stateRecord[1];
					echo '				<option value="'.$stateId.'"';
					if ($stateId === $currentState) {
						echo ' selected';
					};
					echo '>'.$state.'</option>';
				};
			};
			echo '			</select>';
			echo '		</span>';
			echo '		<a href="javascript:void(0);" id="osbtn_'.$r->order_id.'" class="btn btn-xs btn-primary state_buttons disabled"> Apply </a>';
			echo '		<input type="hidden" name="cs_'.$r->order_id.'" id="cs_'.$r->order_id.'" value="'.$currentState.'" />';
			echo '		<div id="ajax_state_change_'.$r->order_id.'" class="hidden"></div>';
			echo '		<input type="hidden" name="latest_inserted" id="latest_inserted" value="'.$r->order_id.'" />';
			echo '	</td>';
			echo '	<td style="text-align:center;"><a href="javascript:void(0);" class="vo_lnk" id="vo_'.$r->order_id.'" data-toggle="tooltip" title="View&nbsp;order"><i class="fa fa-eye"></i></a></td>';
			echo '</tr>';
		};
	}
	
	public function list_orders() {
		$rowCounter = 0;
		$latestId = 0;
		$currentState = "";
		$arrOrderStates = explode(",", $this->list_order_states());
		//$arrPaidStates = array_diff($arrOrderStates, ['1|AWAITING CHEQUE PAYMENT', '8|PAYMENT ERROR', '10|AWAITING EFT PAYMENT', '11|AWAITING PAYPAL PAYMENT']);
		$arrPaidStates = $arrOrderStates;
		$q = "SELECT LPAD(tbl_orders.ID, 6, '0') as order_id, 
			  tbl_orders.created AS created,
			  tbl_orders.customer_order_number AS customer_order_number,
			  tbl_orders.guid AS guid,
			  tbl_orders.price_inc_vat AS price_inc_vat, 
			  tbl_orders.read_by_admin AS read_by_admin, 
			  CONCAT_WS(' ', tbl_customers.title, tbl_customers.firstname, tbl_customers.lastname) AS customer_name
			  FROM tbl_orders, tbl_customers
			  WHERE tbl_customers.ID = tbl_orders.customer_id
			  AND tbl_orders.order_state = 'ORDER'
			  ORDER BY tbl_orders.ID DESC;
		";
		$query = $this->db->query($q);
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$rowCounter = $rowCounter + 1;
			if ($rowCounter === 1) {
				$latestId = (int)$r->order_id;
			};
			$queryInner = $this->db->query("SELECT state_id, created FROM tbl_order_progress WHERE order_id = '".(int)$r->order_id."' ORDER BY ID DESC LIMIT 0,1;");
			while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
				$currentState = $rInner->state_id;
			};
			echo '<tr id="or_'.$r->order_id.'" style="width:100%!important; border-bottom:1px solid #dddddd;"';
			if ($r->read_by_admin === 'N') {
				echo ' class="bold pitch_black"';
			};
			echo '>';
			echo '  <td>'.$r->created.'</td>';
			echo '  <td><div class="pull-right">'.$r->order_id.'</div></td>';
			echo '  <td><div class="pull-right">'.strtoupper($r->customer_order_number).'</div></td>';
			echo '  <td>'.strtoupper($r->customer_name).'</td>';
			echo '  <td><div class="pull-right">'.number_format($r->price_inc_vat, 2).'</div></td>';
			echo '	<td>';
			$stateRecord = "";
			$stateId = "";
			$state = "";
			echo '		<input type="hidden" name="" id="" value="" />';
			echo '		<span>';
			echo '			<select name="os_'.$r->order_id.'" id="os_'.$r->order_id.'" class="state_change">';
			if ($currentState === '2') {
				foreach($arrPaidStates as $key => $value) {
					$stateRecord = explode("|", $value);
					$stateId = $stateRecord[0];
					$state = $stateRecord[1];
					echo '				<option value="'.$stateId.'"';
					if ($stateId === $currentState) {
						echo ' selected';
					};
					echo '>'.$state.'</option>';
				};
			};
			
			if ($currentState === '10') {
				//$arrEftStates = array_diff($arrOrderStates, ['1|AWAITING CHEQUE PAYMENT', '8|PAYMENT ERROR', '11|AWAITING PAYPAL PAYMENT']);
				$arrEftStates = $arrOrderStates;
				foreach($arrEftStates as $key => $value) {
					$stateRecord = explode("|", $value);
					$stateId = $stateRecord[0];
					$state = $stateRecord[1];
					echo '				<option value="'.$stateId.'"';
					if ($stateId === $currentState) {
						echo ' selected';
					};
					echo '>'.$state.'</option>';
				};
			};
			
			if ($currentState !== '2' && $currentState !== '10') {
				//$arrOtherStates = array_diff($arrOrderStates, ['1|AWAITING CHEQUE PAYMENT', '2|PAYMENT ACCEPTED', '8|PAYMENT ERROR', '10|AWAITING EFT PAYMENT', '11|AWAITING PAYPAL PAYMENT']);
				$arrOtherStates = $arrOrderStates;
				foreach($arrOtherStates as $key => $value) {
					$stateRecord = explode("|", $value);
					$stateId = $stateRecord[0];
					$state = $stateRecord[1];
					echo '				<option value="'.$stateId.'"';
					if ($stateId === $currentState) {
						echo ' selected';
					};
					echo '>'.$state.'</option>';
				};
			};
			echo '			</select>';
			echo '		</span>';
			echo '		<a href="javascript:void(0);" id="osbtn_'.$r->order_id.'" class="btn btn-xs btn-primary state_buttons disabled"> Apply </a>';
			echo '		<input type="hidden" name="cs_'.$r->order_id.'" id="cs_'.$r->order_id.'" value="'.$currentState.'" />';
			echo '		<div id="ajax_state_change_'.$r->order_id.'" class="hidden"></div>';
			echo '	</td>';
			echo '	<td style="text-align:center;"><a href="javascript:void(0);" class="vo_lnk" id="vo_'.$r->order_id.'" data-toggle="tooltip" title="View&nbsp;order"><i class="fa fa-eye"></i></a></td>';
			echo '</tr>';
		};
		$rowsReturned = '<span id="order_rowcount_value">'.$rowCounter.'</span> row';
		if ($rowCounter != 1) {
			$rowsReturned .= 's';
		};
		$rowsReturned .= ' returned';
		echo '<tr>';
		echo '  <td class="rowcounters" id="order_row_counter">'.$rowsReturned.'</td>';
		echo '  <td></td>';
		echo '  <td></td>';
		echo '  <td></td>';
		echo '  <td></td>';
		echo '  <td><input type="hidden" name="latest_id" id="latest_id" value="'.$latestId.'" /></td>';
		echo '</tr>';
	}
	
	public function change_order_state() {
		require_once('includes/phpMailer/class.phpmailer.php');
		$orderId = $_GET['order_id'];
		$stateId = $_GET['state_id'];	
		$stmt = $this->db->prepare("INSERT INTO tbl_order_progress (order_id, state_id) VALUES (:order_id, :state_id);");
		$stmt->execute(array(':order_id' => $orderId, ':state_id' => $stateId));
		$emailAddress = "";
		$customerName = "";
		$state = "";
		$q = "
		SELECT LPAD(tbl_orders.ID, 6, '0') AS order_id, 
		tbl_orders.customer_id AS customer_id,
		CONCAT_WS(' ', tbl_customers.title, tbl_customers.firstname, tbl_customers.lastname) AS customer_name,
		tbl_customers.email AS email, 
		tbl_order_states.state AS state
		FROM tbl_orders, tbl_order_states, tbl_customers
		WHERE tbl_orders.ID = '".$orderId."'
		AND tbl_customers.ID = tbl_orders.customer_id 
		AND tbl_order_states.ID = '".$stateId."'
		;";
		$query = $this->db->query($q);
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$emailAddress = $r->email;
			$customerName = $r->customer_name;
			$state = $r->state;
			$orderId = $r->order_id;
			$email = new PHPMailer();
			$email->From      = 'bookshop@myriver.com';
			$email->FromName  = 'River Media';
			$email->Subject   = 'Order #'.$orderId.' progress';
			$email->Body      = 'Order [#'.$orderId.'] for '.strtoupper($customerName).'<br /><br />Status has been updated to "'.$state.'"<br /><br />Regards,<br /><br />The River Media team';
			$email->IsHTML(true);
			$email->AddAddress($emailAddress);
			$cwd = getcwd();
			$email->Send();	
		};
		$stmt = $this->db->prepare("UPDATE tbl_orders SET read_by_admin = 'Y' WHERE ID = :ID;");
		$stmt->execute(array(':ID' => (int)$orderId));
		echo $stateId."_success";
	}
	
	public function view_order_details() {
		$orderId = "";
		if (isset($_GET['order_id'])) {
			$orderId = $_GET['order_id'];
		};
		$orderNumber = "";
		$created = "";
		$customerId = "";
		$customerName = "";
		$email = "";
		$phone = "";
		$event = "";
		//Order headers
		$q = "
		SELECT tbl_orders.customer_id AS customer_id,
		LPAD(tbl_orders.ID, 6, '0') AS order_id,
		tbl_orders.created AS created,
		tbl_orders.price_inc_vat AS price_inc_vat, 
		UPPER(CONCAT_WS(' ', tbl_customers.title, tbl_customers.firstname, tbl_customers.lastname)) AS customer_name, 
		LOWER(tbl_customers.email) AS email, 
		tbl_customers.phone AS phone
		FROM tbl_orders, tbl_customers 
		WHERE tbl_orders.ID = '".$orderId."'
		AND tbl_customers.ID = tbl_orders.customer_id;
		";
		$query = $this->db->query($q);
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$orderNumber = $r->order_id;
			$created = $r->created;
			$price = 'R'.number_format($r->price_inc_vat, 2);
			$customerId = $r->customer_id;
			$customerName = $r->customer_name;
			$email = $r->email;
			$phone = $r->phone;
		};
		echo '<input type="hidden" name="vo_order_id" id="vo_order_id" value="'.$orderNumber.'" />';
		echo '<input type="hidden" name="vo_created" id="vo_created" value="'.$created.'" />';
		echo '<input type="hidden" name="vo_price" id="vo_price" value="'.$price.'" />';
		echo '<input type="hidden" name="vo_customer_name" id="vo_customer_name" value="'.$customerName.'" />';
		echo '<input type="hidden" name="vo_email" id="vo_email" value="'.$email.'" />';
		echo '<input type="hidden" name="vo_phone" id="vo_phone" value="'.$phone.'" />';
		echo '<h5 style="font-weight:bold;">CUSTOMER DETAILS</h5>';
		echo '<div style="border-bottom:1px dashed #dddddd; padding-bottom:7px; margin-bottom:7px;" class="col-md-12">';
		echo '<div class="col-md-6">';
		echo $customerName.'<br />EMAIL: '.$email.'<br />PHONE: '.$phone;
		echo '</div>';
		$stmt = $this->db->prepare("UPDATE tbl_orders SET read_by_admin = 'Y' WHERE ID = :ID;");
		$stmt->execute(array(':ID' => (int)$orderNumber));
		
		//Delivery address
		echo '<div class="col-md-6 pull-right">';
		echo 'DELIVER TO<br />';
		$query = $this->db->query("SELECT line1, line2, line3, line4, city, postal_code FROM tbl_delivery_address WHERE customer_id = '".$customerId."';");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			foreach($r as $key => $value) {
				if (trim($value) !== '') {
					echo $value.'<br />';
				};
			};
		};
		echo '</div>';
		echo '<div class="clearfix"></div>';
		echo '</div>';
		//Order state
		echo '<div style="border-bottom:1px dashed #dddddd; padding-bottom:7px; margin-bottom:7px;">';
		echo '<h5 style="font-weight:bold;">ORDER STATE</h5>';
		$q = "
		SELECT tbl_order_states.state AS state,
		tbl_order_progress.state_id AS state_id,
		tbl_order_progress.created AS created
		FROM tbl_order_states, tbl_order_progress 
		WHERE tbl_order_states.ID = tbl_order_progress.state_id 
		AND tbl_order_progress.order_id = '".$orderId."'  
		ORDER BY tbl_order_progress.ID ASC;
		";
		$query = $this->db->query($q);
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			echo '<div class="state_history">';
			echo '	<div class="col-md-3">'.$r->created.'</div>';
			echo '	<div class="col-md-1"> <i class="fa fa-arrow-right" aria-hidden="true"></i></div>';
			echo '	<div class="col-md-7">'.$r->state.'</div>';
			echo '	<div class="clearfix"></div>';
			echo '</div>';
		};
		echo '</div>';
		//Order lines
		echo '<h5 style="font-weight:bold;">ORDER DETAILS</h5>';
		echo '
			<table class="table table-hover">
			  <thead>
				<tr>
				  <th>Product name</th>
				  <th>By</th>
				  <th><div class="pull-right">Quantity</div></th>
				</tr>
			  </thead>
			  <tbody>		
		';
		$q = "
		SELECT tbl_products.product_name AS product_name,
		tbl_products.media_event AS event_id, 
		tbl_order_lines.product_id AS product_id,
		tbl_order_lines.order_id AS order_id,
		tbl_order_lines.quantity AS quantity,
		tbl_media_origin.media_origin AS media_origin
		FROM tbl_products, tbl_order_lines, tbl_media_origin  
		WHERE tbl_products.ID = tbl_order_lines.product_id 
		AND tbl_products.media_origin = tbl_media_origin.ID  
		AND tbl_order_lines.order_id = '".$orderId."' 
		ORDER BY tbl_order_lines.ID ASC;
		";
		$query = $this->db->query($q);
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$queryInner = $this->db->query("SELECT CONCAT_WS(' ', tbl_event.event_year, tbl_event.event_name) AS media_event FROM tbl_event WHERE ID = '".$r->event_id."';");
			while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
				$event = $rInner->media_event.': ';
			};
			echo '	
			<tr>
				<td>'.$event.$r->product_name.'</td>
			';
			echo '	
				<td>'.$r->media_origin.'</td>
			';
			echo '	
				<td><div class="pull-right">'.$r->quantity.'</div></td>
			</tr>
			';
			$event = '';
		};
		echo '	</tbody>';
		echo '</table>';
		echo '<div class="pull-right">Total (Inc Shipping &amp; VAT): <b class="green" style="font-size:16pt;">'.$price.'</b></div>';
		echo '<div class="clearfix"></div>';
	}
	
	public function list_customers() {
		$rowCounter = 0;
		$lastLogin = "";
		$query = $this->db->query("SELECT ID, title, firstname, lastname, email, phone, enabled FROM tbl_customers ORDER BY lastname ASC;");
		while ($r = $query->fetch(PDO::FETCH_OBJ)) {
			$rowCounter = $rowCounter + 1;
			$queryInner = $this->db->query("SELECT login FROM tbl_customer_logs WHERE customer_id = '".$r->ID."' ORDER BY ID DESC LIMIT 0,1;");
			while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
				$lastLogin = $rInner->login;
			};
			echo '<tr id="cu_'.$r->ID.'" style="width:100%!important; border-bottom:1px solid #dddddd;">';
			echo '  <td>'.strtoupper($r->title).'</td>';
			echo '  <td>'.strtoupper($r->firstname).'</td>';
			echo '  <td>'.strtoupper($r->lastname).'</td>';
			echo '  <td>'.strtolower($r->email).'</td>';
			echo '	<td>'.$r->phone.'</td>';
			echo '	<td class="text-center"><div class="checkbox checkbox-primary">';
			echo '		<input name="culog_'.$r->ID.'" id="culog_'.$r->ID.'" class="login_allowed" type="checkbox" value="Y"'; 
			if ($r->enabled === 'Y') {
				echo ' checked';
			};
			echo '><label for="culog_'.$r->ID.'">&nbsp;</label>';
			echo '	</div>
					<div class="hidden" id="ajax_login_allow_'.$r->ID.'"></div>
					</td>';
			echo '  <td class="pull-right">
			          <ul class="nav navbar-right panel_toolbox pull-right" style="text-align:center; background-color:transparent!important;">
					  ';
			echo '			
						<li><a href="javascript:void(0);" data-toggle="tooltip" title="Order&nbsp;history"><i class="fa fa-history"></i></a></li>
						<li><a href="javascript:void(0);" data-toggle="tooltip" title="View&nbsp;details"><i class="fa fa-eye"></i></a></li>
						<div class="display_status"></div>
						<div class="clearfix"></div>
					  </ul>
				    </td>';
			echo '</tr>';
			$lastLogin = "";
		};
		$rowsReturned = $rowCounter." row";
		if ($rowCounter != 1) {
			$rowsReturned .= "s";
		};
		$rowsReturned .= " returned";
		echo '<tr>';
		echo '  <td class="rowcounters">'.$rowsReturned.'</td>';
		echo '  <td></td>';
		echo '  <td></td>';
		echo '  <td></td>';
		echo '</tr>';
	}
	
	public function change_login_ability() {
		$customerId = "";
		if (isset($_GET['customer_id'])) {
			$customerId = filter_var($_GET['customer_id'], FILTER_SANITIZE_STRING);
		};
		$loginAllowed = 'Y';
		if (isset($_GET['login_allowed'])) {
			$loginAllowed = filter_var($_GET['login_allowed'], FILTER_SANITIZE_STRING);
		};
		if ($loginAllowed !== 'Y' && $loginAllowed !== 'N') {
			$loginAllowed = 'Y';
		};
		$stmt = $this->db->prepare("UPDATE tbl_customers SET enabled = :enabled WHERE ID = :ID;");
		$stmt->execute(array(':enabled' => $loginAllowed, ':ID' => $customerId));
	}
	
	public function view_customer_details() {
		$customerId = "";
		$customerName = "";
		$email = "";
		$phone = "";
		$DOB = "";
		$enabled = "";
		$deliveryAddress = "";
		$ipAddress = "";
		$login = "";
		if (isset($_GET['customer_id'])) {
			$customerId = $_GET['customer_id'];
			$q = "
			SELECT UPPER(CONCAT_WS(' ', tbl_customers.title, tbl_customers.firstname, tbl_customers.lastname)) AS customer_name, 
			tbl_customers.email AS email,
			tbl_customers.phone AS phone, 
			tbl_customers.DOB AS DOB, 
			tbl_customers.enabled AS enabled, 
			tbl_delivery_address.line1 AS line1, 
			tbl_delivery_address.line2 AS line2, 
			tbl_delivery_address.line3 AS line3, 
			tbl_delivery_address.line4 AS line4, 
			tbl_delivery_address.city AS city, 
			tbl_delivery_address.postal_code AS postal_code 
			FROM tbl_customers, tbl_delivery_address 
			WHERE tbl_customers.ID = '".$customerId."' 
			AND tbl_delivery_address.customer_id = tbl_customers.ID;
			";
			$query = $this->db->query($q);
			while ($r = $query->fetch(PDO::FETCH_OBJ)) {
				$queryInner = $this->db->query("SELECT ip_address, login FROM tbl_customer_logs WHERE customer_id = '".$customerId."' ORDER BY ID DESC LIMIT 0,1;");
				while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
					$ipAddress = $rInner->ip_address;
					$login = $rInner->login;
				};
				$customerName = $r->customer_name;
				$email = $r->email;
				$phone = $r->phone;
				$DOB = $r->DOB;
				$enabled = $r->enabled;
				if (trim($r->line1) !== '') {
					$deliveryAddress = $deliveryAddress.$r->line1.'<br />';
				};
				if (trim($r->line2) !== '') {
					$deliveryAddress = $deliveryAddress.$r->line2.'<br />';
				};
				if (trim($r->line3) !== '') {
					$deliveryAddress = $deliveryAddress.$r->line3.'<br />';
				};
				if (trim($r->line4) !== '') {
					$deliveryAddress = $deliveryAddress.$r->line4.'<br />';
				};
				if (trim($r->city) !== '') {
					$deliveryAddress = $deliveryAddress.$r->city.'<br />';
				};
				if (trim($r->postal_code) !== '') {
					$deliveryAddress = $deliveryAddress.$r->postal_code;
				};
			};
			echo '<div class="row">';
			echo '  <div class="col-md-6">';
			echo '	<h5><b>CUSTOMER DETAILS</b></h5>';
			echo '	<i class="fa fa-user blue" aria-hidden="true"></i>&nbsp;'.$customerName.'<br />';
			echo '	<i class="fa fa-envelope blue" aria-hidden="true"></i>&nbsp;<a href="mailto:'.$email.'">'.$email.'</a><br />';
			echo '	<i class="fa fa-phone blue" aria-hidden="true"></i>&nbsp;<span id="customer_phone">'.$phone.'</span><br />';
			echo '	<i class="fa fa-birthday-cake blue" aria-hidden="true"></i>&nbsp;'.$DOB.'<br /><br />';
			echo '	<h5><b>LAST LOGIN</b></h5>';
			echo '	<i class="fa fa-sign-in blue" aria-hidden="true"></i>&nbsp;';
			if (trim($login !== '' && trim($ipAddress) !== '')) {
				echo $login.' from IP '.$ipAddress.'<br />';
			}
			else {
				echo 'Customer has never logged in<br />';
			};
			if ($enabled === 'Y') {
				echo '<i class="fa fa-check green" aria-hidden="true"></i>&nbsp;Login is ENABLED';
			}
			else {
				echo '<i class="fa fa-close red" aria-hidden="true"></i>&nbsp;Login is DISABLED';
			};
			echo ' for this account<br />';
			echo '	</div>';
			echo '  <div class="col-md-6">';
			echo '	<h5><b>DELIVERY ADDRESS</b></h5>';
			echo '	<div>'.$deliveryAddress.'</div>';
			echo '	</div>';
			echo '	<div class="clearfix"></div>';
			echo '</div>';
		};
	}
	
	public function customer_order_history() {
		$customerId = "";
		$customerName = "";
		$email = "";
		$phone = "";
		if (isset($_GET['customer_id'])) {
			$customerId = $_GET['customer_id'];
			$q = "
			SELECT UPPER(CONCAT_WS(' ', title, firstname, lastname)) AS customer_name, 
			LOWER(email) AS email, phone 
			FROM tbl_customers 
			WHERE ID = '".$customerId."';
			";
			$query = $this->db->query($q);
			while ($r = $query->fetch(PDO::FETCH_OBJ)) {
				$customerName = $r->customer_name;
				$email = $r->email;
				$phone = $r->phone;
			};
			echo '<div class="row">';
			echo '<input type="hidden" name="h_customer_name" id="h_customer_name" value="'.$customerName.'" />';
			echo '<h5>&nbsp;&nbsp;<i class="fa fa-envelope blue"></i> <a href="mailto:'.$email.'">'.$email.'</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i class="fa fa-phone blue"></i> <span id="customer_phone">'.$phone.'</span></h5>';
			echo '</div>';
			echo '<div class="clearfix"></div>';
			echo '<div class="row">';
			echo '
                <table class="table table-hover">
                  <thead>
                    <tr>
                      <th>Order #</th>
					  <th><div class="pull-right">Items</div></th>
                      <th><div class="pull-right">Customer Ref</div></th>
                      <th>Created</th>
                      <th><div class="pull-right">Price Inc</div></th>
                    </tr>
                  </thead>
				  <tbody id="table_customer_orders">
			';
			$query = $this->db->query("SELECT LPAD(ID, 6, '0') AS order_id, UPPER(customer_order_number) AS customer_order_number, created, price_inc_vat FROM tbl_orders WHERE customer_id = '".$customerId."' AND order_state = 'ORDER' ORDER BY ID DESC;");
			$recordCounter = 0;
			while ($r = $query->fetch(PDO::FETCH_OBJ)) {
				$recordCounter = $recordCounter + 1;
				$sum = 0;
				$queryInner = $this->db->query("SELECT COUNT(*) AS line_count FROM tbl_order_lines WHERE order_id = '".(int)$r->order_id."';");
				while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
					$sum = $rInner->line_count;
				};
				echo '<tr class="customer_orders" id="co_'.$r->order_id.'">';
				echo '	<td id="co_id_'.(int)$r->order_id.'" class="co_id_cells">'.$r->order_id.'</td>';
				echo '	<td><div class="pull-right">'.$sum.'</div></td>';
				echo '	<td><div class="pull-right">'.$r->customer_order_number.'</div></td>';
				echo '	<td>'.$r->created.'</td>';
				echo '	<td><div class="pull-right">'.number_format($r->price_inc_vat, 2).'</div></td>';
				echo '</tr>';
			};
			echo '<tr>
				    <td colspan="4" class="green">'.$recordCounter.' Record';
			if ($recordCounter !== 1) {
				echo 's';
			};
			echo '	</td>
				  </tr>';
			echo '</tbody>
				  </table>';
			echo '</div>';
		}
		else {
			echo 'Missing customer ID';	
		};
	}
	
	public function customer_order_lines() {
		$event = "";
		$orderId = "";
		if (isset($_GET['order_id'])) {
			$orderId = $_GET['order_id'];
			echo '
				<div class="pull-right"><a href="javascript:void(0);" id="close_'.$orderId.'"><i class="fa fa-close black"></i></a></div>
				<div class="black"><b>ORDER DETAILS</b></div>
                <table class="table table-hover black">
                  <thead>
                    <tr>
                      <th>Product name</th>
					  <th>By</th>
                      <th><div class="pull-right">Quantity</div></th>
                    </tr>
                  </thead>
				  <tbody id="table_customer_order_lines">
			';
			$q = "
			SELECT tbl_products.product_name AS product_name,
			tbl_products.media_event AS event_id, 
			tbl_order_lines.product_id AS product_id,
			tbl_order_lines.order_id AS order_id,
			tbl_order_lines.quantity AS quantity,
			tbl_media_origin.media_origin AS media_origin
			FROM tbl_products, tbl_order_lines, tbl_media_origin  
			WHERE tbl_products.ID = tbl_order_lines.product_id 
			AND tbl_products.media_origin = tbl_media_origin.ID  
			AND tbl_order_lines.order_id = '".$orderId."' 
			ORDER BY tbl_order_lines.ID ASC;
			";
			$query = $this->db->query($q);
			while ($r = $query->fetch(PDO::FETCH_OBJ)) {
				$queryInner = $this->db->query("SELECT CONCAT_WS(' ', tbl_event.event_year, tbl_event.event_name) AS media_event FROM tbl_event WHERE ID = '".$r->event_id."';");
				while ($rInner = $queryInner->fetch(PDO::FETCH_OBJ)) {
					$event = $rInner->media_event.': ';
				};
				echo '	
				<tr>
					<td>'.$event.$r->product_name.'</td>
				';
				echo '	
					<td>'.$r->media_origin.'</td>
				';
				echo '	
					<td><div class="pull-right">'.$r->quantity.'</div></td>
				</tr>
				';
				$event = '';
			};
			echo '</tbody></table>';
		}
		else {
			echo 'Missing Order ID';	
		};
	}
}
