<?	
	$mysqli = new mysqli('localhost', 'upstream', 'lSiv74GleNQ', 'upstream');	
		
	if(mysqli_connect_errno()) 
	{ 
	    trigger_error('Fout bij verbinding: '.$mysqli->error); 
	    exit();
	}
?><?
	require_once("Functions.class.php");
	require_once("Artist.class.php");
	require_once("ArtistDownload.class.php");
	require_once("ArtistExhibition.class.php");
	require_once("ArtistSliderImage.class.php");
	require_once("Exhibition.class.php");
	require_once("ExhibitionImage.class.php");
	require_once("ArtFair.class.php");
	require_once("ArtFairImage.class.php");
	require_once("News.class.php");
	require_once("NewsImage.class.php");
	require_once("Settings.class.php");
	require_once("MaillingListUser.class.php");
	require_once("AdminUser.class.php");
	require_once("Staff.class.php");
	
	class Database extends Functions {
	
		public $mysqli;
	
		function __construct($mysqli) {		
			$this->mysqli = $mysqli;
		}
				
		//////////////////////////////WEBSITE//////////////////////////////
	
		public function getPageByName($name) {
			$sql = 'SELECT `page_id` FROM `page` WHERE `page_name` = "'.$name.'";';
			$result = $this->mysqli->query($sql);
			
			if($result->num_rows == 1) {
				$fields = $result->fetch_assoc();
				$page_id = $fields['page_id'];
				return new Page($this->mysqli, $page_id);
			}			
		}
		
		public function getExhibitions($header=false, $upcoming=false) {			
			if($header) {
				$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE `is_header` = "y" AND `show` = "y" ORDER BY `header_position` ASC;';	
			}else if($upcoming) {
				$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE `is_upcoming` = "y" AND `show` = "y" LIMIT 1;';	
			}else{
				$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE `show` = "y";';	
			}
				
			$result = $this->mysqli->query($sql);
			if($header || $upcoming) {
				if($result->num_rows == 0) {
					$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE `show` = "y" LIMIT 1;';
					$result = $this->mysqli->query($sql);
				}
			}
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Exhibition($this->mysqli, $row['exhibition_id']);
			}
			return $array;	
		}
		
		public function getCurrentExhibitions() {
			$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE CURRENT_DATE() >= `start_date` AND CURRENT_DATE() <= `end_date` AND `show` = "y" ORDER BY `start_date` ASC;';
			
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Exhibition($this->mysqli, $row['exhibition_id']);
			}
			return $array;	
		}
		
		public function getUpcomingExhibitions() {
			$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE CURRENT_DATE() < `start_date` AND `show` = "y" ORDER BY `start_date` ASC;';
			
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Exhibition($this->mysqli, $row['exhibition_id']);
			}
			return $array;	
		}
		
		public function getPastExhibitions($year) {
			$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE CURRENT_DATE() > `end_date` AND YEAR(`start_date`) = "'.$year.'" AND `show` = "y" ORDER BY `start_date` DESC;';
			
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Exhibition($this->mysqli, $row['exhibition_id']);
			}
			return $array;	
		}
		
		public function getExhibitionById($exhibition_id) {
			return new Exhibition($this->mysqli, $exhibition_id);
		}
		
		public function getArtFairById($art_fair_id) {
			return new ArtFair($this->mysqli, $art_fair_id);
		}
		
		public function getArtistExhibitionById($artist_exhibition_id) {
			return new ArtistExhibition($this->mysqli, $artist_exhibition_id);
		}
		
		public function removeArtistExhibitionByExhibitionId($exhibition_id='') {
			if(!empty($exhibition_id)) {
				$sql = 'SELECT `artist_exhibition_id` FROM `artist_exhibition` WHERE `exhibition_id` = '.$exhibition_id.';';
				$result = $this->mysqli->query($sql);
				
				if($result->num_rows == 1) {
					$row = $result->fetch_assoc();
					$artist_exhibition = new ArtistExhibition($this->mysqli, $row['artist_exhibition_id']);
					$artist_exhibition->delete();
				}
			}
		}
		
		public function getExhibitionPhotosById($exhibition_id) {
			$sql = 'SELECT * FROM `exhibition_image` WHERE `exhibition_id` = "'.$exhibition_id.'";';
			$result = $this->mysqli->query($sql);
			$array = array();

			while($row = $result->fetch_assoc()) {
				$array[] = new ExhibitionImage($this->mysqli, $row['exhibition_image_id']);
			}
			return $array;
		}
		
		public function getArtFairPhotosById($art_fair_id) {
			$sql = 'SELECT * FROM `art_fair_image` WHERE `art_fair_id` = '.$art_fair_id.';';
			$result = $this->mysqli->query($sql);
			$array = array();

			while($row = $result->fetch_assoc()) {
				array_push($array, new ArtFairImage($this->mysqli, $row['art_fair_image_id']));
			}
			return $array;
		}
		
		public function removeExhibitionPhotosById($exhibition_id) {
			$sql = 'DELETE FROM `exhibition_image` WHERE `exhibition_id` = "'.$exhibition_id.'";';
			$result = $this->mysqli->query($sql);
		}
		
		public function removeSingleExhibitionPhotoById($exhibition_image_id='') {
			if($exhibition_image_id != '') {
				$image = new ExhibitionImage($this->mysqli, $exhibition_image_id);
				unlink('../images/exhibition/495x318/' . $image->image_path);
			
				$sql = $sql = 'DELETE FROM `exhibition_image` WHERE `exhibition_image_id` = "' . $exhibition_image_id . '";';
				$result = $this->mysqli->query($sql);
			}			
		}
		
		public function removeSingleArtFairPhotoById($art_fair_image_id='') {
			if($art_fair_image_id != '') {
				
				$image = new ArtFairImage($this->mysqli, $art_fair_image_id);
				unlink('../images/art_fair/495x318/' . $image->image_path);
				
				$sql = $sql = 'DELETE FROM `art_fair_image` WHERE `art_fair_image_id` = "' . $art_fair_image_id . '";';
				$result = $this->mysqli->query($sql);
			}			
		}
		
		public function removeArtFairPhotosById($art_fair_id) {
			$sql = 'DELETE FROM `art_fair_image` WHERE `art_fair_id` = "'.$art_fair_id.'";';
			$result = $this->mysqli->query($sql);
		}
		
		public function getCurrentArtFairs() {
			$sql = 'SELECT `art_fair_id` FROM `art_fair` WHERE CURRENT_DATE() >= `start_date` AND CURRENT_DATE() <= `end_date` AND `show` = "y" ORDER BY `start_date` ASC;';
			
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new ArtFair($this->mysqli, $row['art_fair_id']);
			}
			return $array;	
		}
		
		public function getUpcomingArtFairs() {
			$sql = 'SELECT `art_fair_id` FROM `art_fair` WHERE CURRENT_DATE() < `start_date` AND `show` = "y" ORDER BY `start_date` ASC;';
			
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new ArtFair($this->mysqli, $row['art_fair_id']);
			}
			return $array;	
		}
		
		public function getPastArtFairs($year) {
			$sql = 'SELECT `art_fair_id` FROM `art_fair` WHERE CURRENT_DATE() > `end_date` AND YEAR(`start_date`) = "'.$year.'" AND `show` = "y" ORDER BY `start_date` DESC;';
			
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new ArtFair($this->mysqli, $row['art_fair_id']);
			}
			return $array;	
		}
		
		public function getArtistById($artist_id='') {
			if(!empty($artist_id)) {
				return new Artist($this->mysqli, $artist_id);
			}
		}
		
		public function getArtists($limit='') {			
			if(!empty($limit)) {
				$sql = 'SELECT `artist_id` FROM `artist` WHERE `guest` = "n" AND `affiliate` = "n" ORDER BY `sort_by` ASC LIMIT '.$limit.';';	
			}else{
				$sql = 'SELECT `artist_id` FROM `artist` WHERE `guest` = "n" AND `affiliate` = "n" ORDER BY `sort_by` ASC;';	
			}
				
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Artist($this->mysqli, $row['artist_id']);
			}
			return $array;	
		}	
		
		public function getArtistsWithGuests($limit='') {			
			if(!empty($limit)) {
				$sql = 'SELECT `artist_id` FROM `artist` ORDER BY `sort_by` ASC LIMIT '.$limit.';';	
			}else{
				$sql = 'SELECT `artist_id` FROM `artist` ORDER BY `sort_by` ASC;';	
			}
				
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Artist($this->mysqli, $row['artist_id']);
			}
			return $array;	
		}	
		
		public function getHighlightedItem() {
			$sql = 'SELECT * FROM `highlighted` WHERE `highlighted_id` = 1';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array['highlighted_id'] = $row['highlighted_id'];
				$array['exhibition_id'] = $row['exhibition_id'];
				$array['art_fair_id'] = $row['art_fair_id'];
				$array['news_id'] = $row['news_id'];
			}
			
			if($array['exhibition_id'] != 0) {
				$item = new Exhibition($this->mysqli, $array['exhibition_id']);
			}elseif($array['art_fair_id'] != 0) {
				$item = new ArtFair($this->mysqli, $array['art_fair_id']);
			}elseif($array['news_id'] != 0) {
				$item = new News($this->mysqli, $array['news_id']);
			}	
			return $item;		
		}
		
		public function getArtistDownloadByArtistId($artist_id) {
			$sql = 'SELECT `artist_download_id` FROM `artist_download` WHERE `artist_id` = "'.$artist_id.'" ORDER BY `position` ASC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new ArtistDownload($this->mysqli, $row['artist_download_id']);
			}
			return $array;	
		}
		
		public function getArtistExhibitionByArtistId($artist_id) {
			$sql = 'SELECT `artist_exhibition_id` FROM `artist_exhibition` WHERE `artist_id` = "'.$artist_id.'" ORDER BY `position` ASC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new ArtistExhibition($this->mysqli, $row['artist_exhibition_id']);				
			}
			return $array;
		}
		
		public function getGuestArtists() {			
			$sql = 'SELECT `artist_id` FROM `artist` WHERE `guest` = "y" ORDER BY `sort_by` ASC;';
				
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Artist($this->mysqli, $row['artist_id']);
			}
			return $array;	
		}
		
		public function getAffiliateArtists() {			
			$sql = 'SELECT `artist_id` FROM `artist` WHERE `affiliate` = "y" ORDER BY `sort_by` ASC;';
				
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Artist($this->mysqli, $row['artist_id']);
			}
			return $array;	
		}
		
		public function getNews($limit='') {
			if(!empty($limit)) {
				$sql = 'SELECT `news_id` FROM `news` WHERE `publication_date` <= NOW() AND `show` = "y" ORDER BY `date` DESC LIMIT '.$limit.';';
			}else{
				$sql = 'SELECT `news_id` FROM `news` WHERE `publication_date` <= NOW() AND `show` = "y" ORDER BY `date` DESC;';
			}		
				
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new News($this->mysqli, $row['news_id']);
			}
			return $array;
		}
		
		public function getPinnedNews($add_latest_news=false) { 
			$sql = 'SELECT `news_id` FROM `news` WHERE `pinned` = "y" AND `show` = "y" ORDER BY `position` ASC';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			$count_pinned_news = $result->num_rows;  
			
			while($row = $result->fetch_assoc()) {
				$array[] = new News($this->mysqli, $row['news_id']);
			}			
			
			if($count_pinned_news <= 3 && $add_latest_news) {
				$add_to_sql = '';
				for($i=0; $i<count($array); $i++) {
					$add_to_sql.= " AND `news_id` != ".$array[$i]->news_id;
				}
				$rest = 3 - $count_pinned_news;
				$sql = 'SELECT `news_id` FROM `news` WHERE `publication_date` <= NOW() '.$add_to_sql.' AND `show` = "y" ORDER BY `date` DESC LIMIT '.$rest.';';
				$result = $this->mysqli->query($sql);
				
				while($row = $result->fetch_assoc()) {
					$array[] = new News($this->mysqli, $row['news_id']);
				}			
			}
			return $array;	
		}
		
		public function getScheduledNews() {
			$sql = 'SELECT `news_id` FROM `news` WHERE `publication_date` != "0000-00-00" AND `publication_date` > NOW() AND `show` = "y" ORDER BY `publication_date` DESC;';	
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new News($this->mysqli, $row['news_id']);
			}
			return $array;
		}
		
		public function getNewsByYear($year) {
			$sql = 'SELECT `news_id` FROM `news` WHERE YEAR(`date`) = "'.$year.'" AND `publication_date` <= NOW() AND `show` = "y" ORDER BY `date` DESC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new News($this->mysqli, $row['news_id']);
			}
			return $array;
		}
		
		public function getNewsByYearWithoutExternalNews($year) {
			$sql = 'SELECT `news_id` FROM `news` WHERE YEAR(`date`) = "'.$year.'" AND `publication_date` <= NOW() AND `show` = "y" AND `external_url` = "" ORDER BY `date` DESC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new News($this->mysqli, $row['news_id']);
			}
			return $array;
		}
		
		public function getNewsById($news_id) {
			return new News($this->mysqli, $news_id);
		}
		
		public function getNewsPhotosById($news_id) {
			$sql = 'SELECT `news_image_id` FROM `news_image` WHERE `news_id` = "'.$news_id.'";';
			$result = $this->mysqli->query($sql);
			$array = array();
			while($row = $result->fetch_assoc()) {
				$array[] = new NewsImage($this->mysqli, $row['news_image_id']);
			}
			return $array;
		}
		
		public function getArchiveYears() {
			$date = date("Y-m-d");
			$sql = 'SELECT DISTINCT YEAR(`start_date`) as year FROM `exhibition` WHERE `show` = "y" AND `end_date` < "'.$date.'" AND `start_date` != "0000-00-00" ORDER BY `year` DESC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = $row['year'];
			}
			return $array;
		}
		
		public function getArtFairsArchiveYears() {
			$date = date("Y-m-d");
			$sql = 'SELECT DISTINCT YEAR(`start_date`) as year FROM `art_fair` WHERE `show` = "y" AND `end_date` < "'.$date.'" AND `start_date` != "0000-00-00" ORDER BY `year` DESC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = $row['year'];
			}
			return $array;
		}
		
		public function getNewsYears() {	
			$date = date("Y-m-d");
			$sql = 'SELECT DISTINCT YEAR(`date`) as year FROM `news` WHERE `publication_date` < "'.$date.'" AND `show` = "y" AND `date` != "0000-00-00" ORDER BY year DESC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = $row['year'];
			}
			return $array;
		}
		
		public function getNewsYearsWithoutExternalNews() {	
			$date = date("Y-m-d");
			$sql = 'SELECT DISTINCT YEAR(`date`) as year FROM `news` WHERE `publication_date` < "'.$date.'" AND `show` = "y" AND `date` != "0000-00-00" AND `external_url` = "" ORDER BY year DESC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = $row['year'];
			}
			return $array;
		}
		
		public function getSettings() {		
			return new Settings($this->mysqli, 1);			
		}
		
		public function saveNotes($notes_to_save='') {			
			$sql = "UPDATE `admin_user` SET `notes` = '".$notes_to_save."' WHERE `admin_user_id` = '".$_SESSION['admin_user']->admin_user_id."';";
			$result = $this->mysqli->query($sql);
			return true;
		}
		
		public function getNotesForAdminUser($admin_user_id='') {
			$sql = 'SELECT `notes` FROM `admin_user` WHERE `admin_user_id` = '.$admin_user_id.';';
			$result = $this->mysqli->query($sql);
			
			$row = $result->fetch_object();
			return $row->notes;
		}
		
		public function getStaff() {
			$sql = 'SELECT `staff_id` FROM `staff` ORDER BY `position` ASC;';
			$result = $this->mysqli->query($sql);
			$array = array();
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Staff($this->mysqli, $row['staff_id']);
			}
			return $array;
		}
		
		public function getArtistByUrl($url) {
			$sql = 'SELECT `artist_id` FROM `artist` WHERE `url` = "'.$url.'";';
			$result = $this->mysqli->query($sql);
			
			if($result->num_rows == 1) {
				$result = $result->fetch_assoc();
				$id = $result['artist_id'];
				return new Artist($this->mysqli, $id);
			}else{
				print '<script type="text/javascript">window.location = "/artists"</script>';
			}
		}
		
		public function checkIfEmailaddressExcists($emailaddress) {
			$sql = 'SELECT `mailing_list_user_id` FROM `mailing_list_user` WHERE `emailaddress` = "'.$emailaddress.'";';
			$result = $this->mysqli->query($sql);
			
			if($result->num_rows > 0) {
				return true;
			}else{
				return false;
			}
		}
		
		public function getArtistSliderImagesById($artist_id='') {
			if(!empty($artist_id)) {
				$sql = 'SELECT `artist_slider_image_id` FROM `artist_slider_image` WHERE `artist_id` = '.$artist_id.' ORDER BY `position` ASC;';
				$result = $this->mysqli->query($sql);
				$array = array();
				
				if($result->num_rows > 0) {
					while($row = $result->fetch_assoc()) {
						$array[] = new ArtistSliderImage($this->mysqli, $row['artist_slider_image_id']);
					}			
					return $array;
				}
			}			
		}
		
		//////////////////////////////ADMINPANEEL//////////////////////////////
		
		public function createPassword($password='') {			
			$cost = 10;
			$salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.');
			$salt = sprintf("$2a$%02d$", $cost) . $salt;
			$hash = crypt($password, $salt);
			return($hash);
		}
		
		public function checkAdminPassword($password='') {		
			if(!empty($password)) {
				$sql = "SELECT `hash` FROM `admin_user` WHERE `admin_user_id` = '".$_SESSION['admin_user']->admin_user_id."';";
				$result = $this->mysqli->query($sql);
				
				if($result->num_rows == 1) {
					$result = $result->fetch_object();
					$hash = $result->hash;	
					
					if(crypt($password, $hash) == $hash) {
						return true;
					}else{
						return false;
					}
				}else{
					return false;
				}
			}else{
				return false;
			}			
		}
		
		public function changeAdminPassword($new_password='') {
			if(!empty($new_password) && !empty($_SESSION['admin_user']->admin_user_id)) {
				$admin_user = new AdminUser($this->mysqli, $_SESSION['admin_user']->admin_user_id);
				$new_hash = $this->createPassword($new_password);
				$admin_user->update(array('hash' => $new_hash));
				return true;
			}else{
				return false;
			}			
		}
		
		public function changeAdminEmailaddress($emailaddress='') {
			if(!empty($emailaddress)) {
				$admin_user = new AdminUser($this->mysqli, $_SESSION['admin_user']->admin_user_id);
				$admin_user->update(array('emailaddress' => $emailaddress));
				return true;
			}else{
				return false;
			}
		}
		
		public function adminLogin($username='', $password='') {
			if(!empty($username) && !empty($password)) {
				$sql = "SELECT `hash` FROM `admin_user` WHERE `username` = '".$username."' LIMIT 1";
				$result = $this->mysqli->query($sql);
				
				if($result->num_rows == 1) {
					$result = $result->fetch_object();
					$hash = $result->hash;			
					
					if(crypt($password, $hash) == $hash) {
						$sql = "SELECT `admin_user_id` FROM `admin_user` WHERE `username` = '".$username."' AND `hash` = '".$hash."';";
						$result = $this->mysqli->query($sql);
						$result = $result->fetch_object();
						
						$admin_user = new AdminUser($this->mysqli, $result->admin_user_id);
						$new_hash = $this->createPassword($password);
						$admin_user->update(array('hash' => $new_hash, 'hash_session' => md5(date('YmdHis'))));
						$admin_user = new AdminUser($this->mysqli, $admin_user->admin_user_id);
						
						$object = new stdClass();
						$object->admin_user_id 	= $admin_user->admin_user_id;
						$object->username 		= $admin_user->username;
						$object->emailaddress	= $admin_user->emailaddress;
						$object->hash_session 	= $admin_user->hash_session;					
						$_SESSION['admin_user'] = $object;						
						return true;					
					}else{
						return false;
					}
				}else{
					return false;
				}
			}else{
				return false;
			}			
		}
		
		public function checkAdminSession() {
			$sql = "SELECT `hash_session` FROM `admin_user` WHERE `admin_user_id` = '".$_SESSION['admin_user']->admin_user_id."';";
			$result = $this->mysqli->query($sql);
			$result = $result->fetch_object();
			
			if($result->hash_session == $_SESSION['admin_user']->hash_session) {
				return true;
			}else{
				return false;
			}
		}
		
		public function retrieveLogin($emailaddress='') {
			$sql = 'SELECT `admin_user_id` FROM `admin_user` WHERE `emailaddress` = "'.$emailaddress.'";';
			$result = $this->mysqli->query($sql);
			
			if($result->num_rows == 1) { //if there is one result
				$result 		= $result->fetch_object();
				$admin_user 	= new AdminUser($this->mysqli, $result->admin_user_id);
				$hash_reset 	= md5(date("YmdHis").$admin_user->admin_user_id.$this->random_string(50));
				$admin_user->update(array('hash_reset' => $hash_reset));
				
				$message 	= "<html><body>";
				$message	.= "Hi <b>$admin_user->username</b>,<br /><br />";
				$message 	.= "You have requested a new password for your administrator account.<br />";
				$message	.= "Click <a href='http://www.upstreamgallery.nl/configure/?action=send_password&i=$hash_reset&emailaddress=$admin_user->emailaddress'>here</a> to reset your password.<br /><br />";
				$message	.= "Please ignore this email if you do not want to reset your password. Your password will not be changed. If you keep getting this email, while you have not requested to change your password, then it is recommended to change the email address of the administrator account in the admin panel.<br /><br /><br />";
				$message	.= "Have a nice day!<br /><br />Upstream Bot";
				$message	.= "</body></html>";
				
				$headers = "From: Upstream Bot <noreply@upstreamgallery.nl>\r\n";
				$headers .= "MIME-Version: 1.0\r\n";
				$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
				mail($admin_user->emailaddress, 'Request for a new password', $message, $headers);
				return true;
			}else{
				return false;
			}
		}
		
		public function sendNewPassword($emailaddress, $hash_reset){
			$sql = 'SELECT admin_user_id FROM `admin_user` WHERE `emailaddress` = "'.$emailaddress.'" AND `hash_reset` = "'.$hash_reset.'";';
			$result = $this->mysqli->query($sql);
			
			if($result->num_rows == 1) { //if there is one result
				$result 		= $result->fetch_object();
				$admin_user 	= new AdminUser($this->mysqli, $result->admin_user_id);
				
				$new_password 	= $this->random_string();
				$new_hash 		= $this->createPassword($new_password);
				
				$admin_user->update(array('hash' => $new_hash, 'hash_reset' => ''));
				
				$message 	= "<html><body>";
				$message	.= "Hi <b>$admin_user->username</b>,<br /><br />";
				$message 	.= "You have requested a new password for your administrator account.<br />";
				$message	.= "Your new password: <b>$new_password</b><br /><br />";
				$message	.= "It is recommended to change this password once you have logged in.<br /><br /><br />";
				$message	.= "Have a nice day!<br /><br />Upstream Bot";
				$message	.= "</body></html>";
				
				$headers = "From: Upstream Bot <noreply@upstreamgallery.nl>\r\n";
				$headers .= "MIME-Version: 1.0\r\n";
				$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
				mail($admin_user->emailaddress, 'Your new password', $message, $headers);
				return true;
			}else{
				return false;
			}
		}
		
		public function countExhibitions() {
			$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE `show` = "y";';
			$result = $this->mysqli->query($sql);
			return $result->num_rows;
		}
		
				public function countArtfairs() {
			$sql = 'SELECT `art_fair_id` FROM `art_fair` WHERE `show` = "y";';
			$result = $this->mysqli->query($sql);
			return $result->num_rows;
		}
		
						public function countNews() {
			$sql = 'SELECT `news_id` FROM `news` WHERE `publication_date` <= NOW() AND `show` = "y";';
			$result = $this->mysqli->query($sql);
			return $result->num_rows;
		}
		
		public function countArtists() {
			$sql = 'SELECT `artist_id` FROM `artist`;';
			$result = $this->mysqli->query($sql);
			return $result->num_rows;
		}
		
		public function countMailingSubscritions() {
			$sql = 'SELECT `mailing_list_user_id` FROM `mailing_list_user` WHERE `show` = "y";';
			$result = $this->mysqli->query($sql);
			return $result->num_rows;
		}
		
		public function searchExhibition($title, $only_visable_items=false) {
			if($only_visable_items) {
				$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE `find_by` LIKE "%'.$title.'%" AND `show` = "y";';
			}else{
				$sql = 'SELECT `exhibition_id` FROM `exhibition` WHERE `find_by` LIKE "%'.$title.'%";';
			}			
			$result = $this->mysqli->query($sql);
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Exhibition($this->mysqli, $row['exhibition_id']);
			}
			return $array;
		}
		
		public function searchArtFair($title, $only_visable_items=false) {
			if($only_visable_items) {
				$sql = 'SELECT `art_fair_id` FROM `art_fair` WHERE `find_by` LIKE "%'.$title.'%" AND `show` = "y";';
			}else{
				$sql = 'SELECT `art_fair_id` FROM `art_fair` WHERE `find_by` LIKE "%'.$title.'%";';
			}			
			$result = $this->mysqli->query($sql);
			
			while($row = $result->fetch_assoc()) {
				$array[] = new ArtFair($this->mysqli, $row['art_fair_id']);
			}
			return $array;
		}
		
		public function searchArtist($last_name) {
			$sql = 'SELECT `artist_id` FROM `artist` WHERE `sort_by` LIKE "%'.$last_name.'%";';
			$result = $this->mysqli->query($sql);
			
			while($row = $result->fetch_assoc()) {
				$array[] = new Artist($this->mysqli, $row['artist_id']);
			}
			return $array;
		}
		
		public function searchNews($title='', $only_visable_items=false, $highlighted=false) {
			if($highlighted) {
				$sql = 'SELECT `news_id` FROM `news` WHERE `publication_date` <= NOW() AND `find_by` LIKE "'.$title.'%" AND `show` = "y";';
			}elseif($only_visable_items) {
				$sql = 'SELECT `news_id` FROM `news` WHERE `find_by` LIKE "%'.$title.'%" AND `show` = "y";';
			}else{
				$sql = 'SELECT `news_id` FROM `news` WHERE `find_by` LIKE "%'.$title.'%";';
			}			
			$result = $this->mysqli->query($sql);
			
			while($row = $result->fetch_assoc()) {
				$array[] = new News($this->mysqli, $row['news_id']);
			}
			return $array;
		}
		
		public function getHighestPositionArtistExhibition($artist_id) {
			$sql = 'SELECT MAX(position) FROM `artist_exhibition` WHERE `artist_id` = '.$artist_id.';';
			$result = $this->mysqli->query($sql);
			$row = $result->fetch_assoc();
			return $row['MAX(position)'];
		}
		
		public function getHighestPositionArtistDownload($artist_id) {
			$sql = 'SELECT MAX(position) FROM `artist_download` WHERE `artist_id` = '.$artist_id.';';
			$result = $this->mysqli->query($sql);
			$row = $result->fetch_assoc();
			return $row['MAX(position)'];
		}
		
		public function getHighestPositionArtistSliderImage($artist_id) {
			$sql = 'SELECT MAX(position) FROM `artist_slider_image` WHERE `artist_id` = '.$artist_id.';';
			$result = $this->mysqli->query($sql);
			$row = $result->fetch_assoc();
			return $row['MAX(position)'];
		}
		
		public function getHighestPositionStaff() {
			$sql = 'SELECT MAX(position) FROM `staff`;';
			$result = $this->mysqli->query($sql);
			$row = $result->fetch_assoc();
			return $row['MAX(position)'];
		}
		
		public function getHighestPositionPinnedNews() {
			$sql = 'SELECT MAX(position) FROM `news` WHERE `pinned` = "y";';
			$result = $this->mysqli->query($sql);
			$row = $result->fetch_assoc();
			return $row['MAX(position)'];
		}
		
		public function getHighestPositionHeader() {
			$sql = 'SELECT MAX(header_position) FROM `exhibition` WHERE `is_header` = "y";';
			$result = $this->mysqli->query($sql);
			$row = $result->fetch_assoc();
			return $row['MAX(header_position)'];
		}
		
		public function changePositionArtistExhibition($direction='', $position='', $artist_id='', $artist_exhibition_id='') {
			switch($direction) {
				case "up":
				if($position > 1) {
					$sql = 'UPDATE `artist_exhibition` SET position = '.($position - 1).' WHERE `artist_exhibition_id` = '.$artist_exhibition_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `artist_exhibition` SET position = '.$position.' WHERE `position` = '.($position - 1).' AND `artist_id` = '.$artist_id.' AND `artist_exhibition_id` != '.$artist_exhibition_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
				case "down":
				$highest_position = $this->getHighestPositionArtistExhibition($artist_id);
				if($position <= $highest_position) {
					$sql = 'UPDATE `artist_exhibition` SET position = '.($position + 1).' WHERE `artist_exhibition_id` = '.$artist_exhibition_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `artist_exhibition` SET position = '.$position.' WHERE `position` = '.($position + 1).' AND `artist_id` = '.$artist_id.' AND `artist_exhibition_id` != '.$artist_exhibition_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
			}
		}
		
		public function changePositionArtistDownload($direction='', $position='', $artist_id='', $artist_download_id='') {
			switch($direction) {
				case "up":
				if($position > 1) {
					$sql = 'UPDATE `artist_download` SET position = '.($position - 1).' WHERE `artist_download_id` = '.$artist_download_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `artist_download` SET position = '.$position.' WHERE `position` = '.($position - 1).' AND `artist_id` = '.$artist_id.' AND `artist_download_id` != '.$artist_download_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
				case "down":
				$highest_position = $this->getHighestPositionArtistDownload($artist_id);
				if($position <= $highest_position) {
					$sql = 'UPDATE `artist_download` SET position = '.($position + 1).' WHERE `artist_download_id` = '.$artist_download_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `artist_download` SET position = '.$position.' WHERE `position` = '.($position + 1).' AND `artist_id` = '.$artist_id.' AND `artist_download_id` != '.$artist_download_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
			}
		}
		
		public function changePositionArtistSliderImage($direction='', $position='', $artist_id='', $artist_slider_image_id='') {
			switch($direction) {
				case "up":
				if($position > 1) {
					$sql = 'UPDATE `artist_slider_image` SET position = '.($position - 1).' WHERE `artist_slider_image_id` = '.$artist_slider_image_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `artist_slider_image` SET position = '.$position.' WHERE `position` = '.($position - 1).' AND `artist_id` = '.$artist_id.' AND `artist_slider_image_id` != '.$artist_slider_image_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
				case "down":
				$highest_position = $this->getHighestPositionArtistSliderImage($artist_id);
				if($position <= $highest_position) {
					$sql = 'UPDATE `artist_slider_image` SET position = '.($position + 1).' WHERE `artist_slider_image_id` = '.$artist_slider_image_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `artist_slider_image` SET position = '.$position.' WHERE `position` = '.($position + 1).' AND `artist_id` = '.$artist_id.' AND `artist_slider_image_id` != '.$artist_slider_image_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
			}
		}
		
		public function changePositionStaff($direction='', $position='', $staff_id='') {
			switch($direction) {
				case "up":
				if($position > 1) {
					$sql = 'UPDATE `staff` SET position = '.($position - 1).' WHERE `staff_id` = '.$staff_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `staff` SET position = '.$position.' WHERE `position` = '.($position - 1).' AND `staff_id` != '.$staff_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
				case "down":
				$highest_position = $this->getHighestPositionStaff();
				if($position <= $highest_position) {
					$sql = 'UPDATE `staff` SET position = '.($position + 1).' WHERE `staff_id` = '.$staff_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `staff` SET position = '.$position.' WHERE `position` = '.($position + 1).' AND `staff_id` != '.$staff_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
			}
		}
		
		public function changePositionPinnedNews($direction='', $position='', $news_id='') {
			switch($direction) {
				case "up":
				if($position > 1) {
					$sql = 'UPDATE `news` SET position = '.($position - 1).' WHERE `news_id` = '.$news_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `news` SET position = '.$position.' WHERE `position` = '.($position - 1).' AND `news_id` != '.$news_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
				case "down":
				$highest_position = $this->getHighestPositionPinnedNews();
				if($position <= $highest_position) {
					$sql = 'UPDATE `news` SET position = '.($position + 1).' WHERE `news_id` = '.$news_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `news` SET position = '.$position.' WHERE `position` = '.($position + 1).' AND `news_id` != '.$news_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
			}
		}
		
		public function changePositionHeader($direction='', $position='', $exhibition_id='') {
			switch($direction) {
				case "up":
				if($position > 1) {
					$sql = 'UPDATE `exhibition` SET header_position = '.($position - 1).' WHERE `exhibition_id` = '.$exhibition_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `exhibition` SET header_position = '.$position.' WHERE `header_position` = '.($position - 1).' AND `exhibition_id` != '.$exhibition_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
				case "down":
				$highest_position = $this->getHighestPositionHeader();
				if($position <= $highest_position) {
					$sql = 'UPDATE `exhibition` SET header_position = '.($position + 1).' WHERE `exhibition_id` = '.$exhibition_id.';';
					$result = $this->mysqli->query($sql);
					
					$sql = 'UPDATE `exhibition` SET header_position = '.$position.' WHERE `header_position` = '.($position + 1).' AND `exhibition_id` != '.$exhibition_id.';';
					$result = $this->mysqli->query($sql);
				}				
				break;
			}
		}
		
		public function deleteArtistExhibition($artist_id='', $artist_exhibition_id='') {
			if(!empty($artist_id) && $artist_exhibition_id) {
				$sql = 'SELECT `position` FROM `artist_exhibition` WHERE `artist_exhibition_id` = '.$artist_exhibition_id.';';
				$result = $this->mysqli->query($sql);
				$row = $result->fetch_assoc();
				$position = $row['position']; //position from the deleted item
				
				$sql = 'DELETE FROM `artist_exhibition` WHERE `artist_exhibition_id` = '.$artist_exhibition_id.'';
				$this->mysqli->query($sql);
				
				$sql = 'SELECT `artist_exhibition_id` FROM `artist_exhibition` WHERE `artist_id` = '.$artist_id.' AND `position` > '.$position.';';
				$result = $this->mysqli->query($sql);
				
				while($row = $result->fetch_assoc()) {
					$artist_exhibition = new ArtistExhibition($this->mysqli, $row['artist_exhibition_id']);
					$artist_exhibition->update(array('position' => ($artist_exhibition->position - 1)));
				}
			}	
		}
		
		public function updateArtistImagePositions($artist_slider_image_id="" ,$artist_id="") { //update the position of all the image below the first 
			if(!empty($artist_slider_image_id) && !empty($artist_id)) {
				$sql = "SELECT `artist_slider_image_id` FROM `artist_slider_image` WHERE `artist_slider_image_id` != ".$artist_slider_image_id." AND `artist_id` = ".$artist_id.";";
				$result = $this->mysqli->query($sql);
				
				while($row = $result->fetch_assoc()) {
					$artist_slider_image = new ArtistSliderImage($this->mysqli, $row['artist_slider_image_id']);
					$artist_slider_image->update(array("position" => ($artist_slider_image->position + 1)));
				}
			}
		}
		
		public function updateArtistExhibitionPositions($artist_exhibition_id="" ,$artist_id="") { //update the position of all the image below the first 
			if(!empty($artist_exhibition_id) && !empty($artist_id)) {
				$sql = "SELECT `artist_exhibition_id` FROM `artist_exhibition` WHERE `artist_exhibition_id` != ".$artist_exhibition_id." AND `artist_id` = ".$artist_id.";";
				$result = $this->mysqli->query($sql);
				
				while($row = $result->fetch_assoc()) {
					$artist_exhibition = new ArtistExhibition($this->mysqli, $row['artist_exhibition_id']);
					$artist_exhibition->update(array("position" => ($artist_exhibition->position + 1)));
				}
			}
		}
		
		public function addHeaderUpdateOtherPositions($exhibition_id="") {
			if (!empty($exhibition_id)) {
				$sql = "SELECT `exhibition_id` FROM `exhibition` WHERE `exhibition_id` != ".$exhibition_id." AND `is_header` = 'y';";
				$result = $this->mysqli->query($sql);
				
				while($row = $result->fetch_assoc()) {
					$exhibition = new Exhibition($this->mysqli, $row['exhibition_id']);
					$exhibition->update(array("header_position" => ($exhibition->header_position + 1)));
				}
			}
		}
		
		public function removeHeaderUpdateOtherPositions($exhibition_id="") {
			if (!empty($exhibition_id)) {
				$exhibition = new Exhibition($this->mysqli, $exhibition_id);
				
				$sql = "SELECT `exhibition_id` FROM `exhibition` WHERE `header_position` > ".$exhibition->header_position." AND `is_header` = 'y';";
				$result = $this->mysqli->query($sql);
				
				while($row = $result->fetch_assoc()) {
					$exhibition = new Exhibition($this->mysqli, $row['exhibition_id']);
					$exhibition->update(array("header_position" => ($exhibition->header_position - 1)));
				}
			}
		}
	}
?>