<?php

	
/* 
	customers.php 

	Mike Ontwerpt 2012
	www.mikeontwerpt.nl
	
*/

function initCustomers(){
	
	$output = '';
	if(isset($_POST['back'])){
		if(isset($_POST['location'])){
			$location = $_POST['location'];
			header('location: '.$location);
		}
		else{
			$output .= showDashboard();
		}
	}
	else if(isset($_POST['downloadExcel'])){
		//createExcelFile();
		createSimpleExcel();
	//	header('location: ./');
	}
	else if(isset($_POST['sbm-block-customer'])){
		changeStatus(2);		
	}
	else if(isset($_POST['sbm-unblock-customer'])){
		changeStatus(0);		
	}
	else if(isset($_POST['sbm-activate-customer'])){
		changeStatus(1);		
	}
	else if(isset($_POST['sbm_sortfilter'])){
		sortFilter();
	}
	else if(defined('URL_SUB')){
		$action=URL_SUB;
		switch($action){
			case 'details':
				$output .= showCustomerDetails();
			break;
			case 'block-customer':
				$output .= askStatusCustomer('to2');
			break;
			case 'unblock-customer':
				$output .= askStatusCustomer('from2to1');
			break;
			case 'activate-customer':
				$output .= askStatusCustomer('from0to1');
			break;	
			default:
				$output .= showDashboard();
		
		}
	}
	else{
		$output .= showDashboard();
	}
	return $output;
}


function showDashboard(){
	/* access the global sql object */
	global $mysqli;	
	
	/* language */
	$siteLabels = getLanguage();

	$output = '';
	/* title */
	$output .= '<h1>'.$siteLabels['customers-title'].'</h1>';
	
	$output .= '<div class="submenu">';
		$output .= '<form action="" method="post">';
			$output .= '<input type="submit" name="downloadExcel" value="Download Excel Bestand" class="btn176 btn176b proceed"/>';
		$output .= '</form>';
	$output .= '</div>';
	
	/* SORT & FILTER */
		
		# sort & filter
		$i = 0;
		$sortarr[$i]['val'] = 'timest'; 
		$sortarr[$i]['label'] = 'Customer since';
		$i++;
		$sortarr[$i]['val'] = 'lastName'; 
		$sortarr[$i]['label'] = 'Achternaam';
		$i++;
		$sortarr[$i]['val'] = 'firstName'; 
		$sortarr[$i]['label'] = 'Voornaam';
		$i++;
		$sortarr[$i]['val'] = 'city'; 
		$sortarr[$i]['label'] = 'City';
		$i++;
		$sortarr[$i]['val'] = 'customerCode'; 
		$sortarr[$i]['label'] = 'Debiteur nr';
		$i++;
		
		$sortby = '';
		$sortdir = '';
		$filterquery = '';
		if(defined('URL_SUB') && substr(URL_SUB,0,6) == 'sortby'){
			# check sort
			$sortby = substr(URL_SUB,9);
			$sortdir = substr(URL_SUB, 7,1);
			$sortdir == 'a' ? $sortdir = 'ASC' : $sortdir = 'DESC';
			
			#check filter
			$filterby = substr(URL_SUB2,9);
			
			if(!empty($filterby)){
				$filterstr = str_replace('-',' ',$filterby);
				$filterquery = ' AND (';
				
				$filterquery .= 'cp.lastName LIKE "%'.$filterstr.'%" OR ';
				$filterquery .= 'cp.firstName LIKE "%'.$filterstr.'%" OR ';
				$filterquery .= 'cp.customerCode LIKE "%'.$filterstr.'%" OR ';
				$filterquery .= 'cp.city LIKE "%'.$filterstr.'%" OR ';
				$filterquery .= 'cl.email LIKE "%'.$filterstr.'%"';
				$filterquery .= ')';
			}
		}
		
		## output form
		$output .= '<form action="" method="post" >';
		$output .= '<div class="single-column sortfilter">';
				# Filter input	
				$output .= '<strong>Filter</strong><br />';
				if(!isset($filterstr)) $filterstr = '';
				$output .= '<input type="text" name="filter-by" value="'.$filterstr.'" class="txt" />';
		$output .= '</div>';
		$output .= '<div class="single-column sortfilter">';
				# Sort input
				$output .= '<strong>Sort by</strong><br />';
				
				$output .= '<select name="sort-by">';
					foreach($sortarr as $key => $value){
						$output .= '<option value="'.$value['val'].'" ';
						if(isset($sortby) && $value['val'] == $sortby) $output .= 'selected'; 
						$output .= '>'.$value['label'].'</option>';
					}
				$output .= '</select>';
		$output .= '</div>';
		$output .= '<div class="single-column sortfilter">';
			$output .= '<br /><input type="radio" name="orderdir" value="A" ';
				if (empty($sortdir) || $sortdir == 'ASC') $output .= 'checked';
			$output .= '> Ascending&nbsp;&nbsp;';
			$output .= '<input type="radio" name="orderdir" value="D" ';
				if(isset($sortdir) && $sortdir == 'DESC') $output .= 'checked';
			$output .= '> Descending';
	
		$output .= '</div>';
		$output .= '<div class="single-column sortfilter">';
			# submit button
			$output .= '<br /><input type="submit" name="sbm_sortfilter" value="go" class="btn176 proceed no-margin"/>';
		$output .= '</div>';
		$output .= '<div class="clear"></div><br /><br />';
	$output .= '</form>';
	
	
	//// get info \\\\
	$query = 'SELECT DISTINCT cl.id AS customerId, cl.email, cl.status, cp.customerCode, cp.firstName, cp.middleName, cp.lastName, cp.timest, cp.newsletter, cp.city
				FROM shop_customer_login AS cl, shop_customer_personal AS cp
				WHERE cl.personalId = cp.id';
				
				#sort & filter
				if(!empty($filterquery)) $query .= $filterquery;
				if(!empty($sortby)){ $query .= ' ORDER BY cp.'.$sortby.' '.$sortdir; }
				else{ $query .= ' ORDER BY cp.timest DESC'; }
	if($result = $mysqli->query($query)){
		$numOrders = $result->num_rows;							 
		
		/* top row */
		$output .= '<div class="itemlist-row itemlist-heading">';
			$output .= '<div class="itemlist-single-column"><input type="checkbox" name="checkCtrl" value="checkAll" onClick="check_all(this);" class="itemlist-cbox-first">debiteurnr</div>';
			$output .= '<div class="itemlist-double-column">naam</div>';
			$output .= '<div class="itemlist-double-column">email</div>';
			$output .= '<div class="itemlist-single-column">woonplaats</div>';
			$output .= '<div class="itemlist-single-column">klant sinds</div>';
			$output .= '<div class="itemlist-single-column">nieuwsbrief</div>';
			$output .= '<div class="itemlist-single-column">status</div>';
			$output .= '<div class="itemlist-single-column status-column">edit</div>';
		$output .= '</div>';
											 
		while($record = $result->fetch_assoc()){ 
			//// store info \\\\ 
			$customerId = $record['customerId'];				
			$customerCode = $record['customerCode'];				
			$memberSince = $record['timest'];
			$email = fromDatabase($record['email']);							
			$newsletter = $record['newsletter'];				
			$city = $record['city'];				
			$status = $record['status'];
			
			$name = $record['firstName'];
			if(!empty($record['middleName'])) $name .= ' '.$record['middleName'];
			$name .= ' '.$record['lastName'];
			
			//// create display info \\\\		
			$memberSince = date('d / m / Y',$memberSince); # memberSince
			$newsletter == 1 ? $displayNewsletter = 'ja' : $displayNewsletter = 'nee';
			switch($status){
				case 0: $displayStatus = 'non-actief';
				break;
				case 1: $displayStatus = 'actief';
				break;
				case 2: $displayStatus = 'geblokkeerd';
				break;
			}
			
			//display info
			$output .= '<div class="itemlist-row">';
				$output .= '<div class="itemlist-single-column"><input type="checkbox"  name="option[]" id="option"  value="'.$customerId.'" class="itemlist-cbox-first">
								<a href="/admin/customers/details/'.$customerCode.'-'.$customerId.'/">'.$customerCode.'</a>
							</div>';
				$output .= '<div class="itemlist-double-column">'.$name.'</div>';
				$output .= '<div class="itemlist-double-column">'.$email.'</div>';
				$output .= '<div class="itemlist-single-column">'.$city.'</div>';
				$output .= '<div class="itemlist-single-column">'.$memberSince.'</div>';
				$output .= '<div class="itemlist-single-column">'.$displayNewsletter.'</div>';
				$output .= '<div class="itemlist-single-column">'.$displayStatus.'</div>';
				$output .= '<div class="itemlist-single-column status-column">';
					if($status != 2) $output .= '<a href="/admin/customers/block-customer/'.$customerId.'/" class="itemlist-btn" id="itemlist-btn-block"></a>';
					if($status == 2) $output .= '<a href="/admin/customers/unblock-customer/'.$customerId.'/" class="itemlist-btn" id="itemlist-btn-unblock"></a>';
					if($status == 0) $output .= '<a href="/admin/customers/activate-customer/'.$customerId.'/" class="itemlist-btn" id="itemlist-btn-activate"></a>';
				$output .= '</div>';
			$output .= '</div>';
						 
		}
	}

	
	
	return $output;
}
	
function askStatusCustomer($newstatus){
	$output = '';
		
	/* access the global sql object */
	global $mysqli;	
	
	/* language */
	$siteLabels = getLanguage();
	
	$customerId = URL_SUB2;
	$output = '';
	//// get info \\\\
	if($result = $mysqli->query('SELECT DISTINCT cl.id AS customerId, cl.email, cl.status, cp.title, cp.customerCode, cp.firstName, cp.middleName, cp.lastName, cp.timest, cp.newsletter, cp.city
								FROM shop_customer_login AS cl, shop_customer_personal AS cp
								WHERE cl.personalId = cp.id
								AND cl.id = '.$customerId.' LIMIT 1')){
		$numOrders = $result->num_rows;							 											 
		$record = $result->fetch_assoc(); 
		//// store info \\\\ 
		$customerId = $record['customerId'];				
		$customerCode = $record['customerCode'];				
		$memberSince = $record['timest'];
		$email = fromDatabase($record['email']);							
		$newsletter = $record['newsletter'];				
		$city = $record['city'];				
		$status = $record['status'];
		$title = $record['title'];
		
		$name = $record['firstName'];
		if(!empty($record['middleName'])) $name .= ' '.$record['middleName'];
		$name .= ' '.$record['lastName'];
		
		//// create display info \\\\		
		$memberSince = date('d / m / Y',$memberSince); # memberSince
		$newsletter == 1 ? $displayNewsletter = 'ja' : $displayNewsletter = 'nee';
		switch($status){
			case 0: $displayStatus = 'non-actief';
			break;
			case 1: $displayStatus = 'actief';
			break;
			case 2: $displayStatus = 'geblokkeerd';
			break;
		}
		
		
		/* title */
		switch($newstatus){
			case 'to2': 
				$output .= '<h1>'.$siteLabels['customers-title'].' &rsaquo; blokkeer "'.strtolower($name).'"</h1>';
				$output .= 'Weet je zeker dat je deze klant wilt blokkeren? <br />';
					$title == 'dhr' ? $output .= 'Hij' : $output .= 'Zij';
				$output .= ' zal zich niet meer aan kunnen melden.<br /><br />';
			break;
			case 'from2to1': 
				$output .= '<h1>'.$siteLabels['customers-title'].' &rsaquo; deblokkeer "'.strtolower($name).'"</h1>';
				$output .= 'Weet je zeker dat je deze klant wilt deblokkeren? <br />';
					$title == 'dhr' ? $output .= 'Hij' : $output .= 'Zij';
				$output .= ' kan zich weer hierna aanmelden.<br /><br />';	
			break;
			case 'from0to1': $output .= '<h1>'.$siteLabels['customers-title'].' &rsaquo; activeer "'.strtolower($name).'"</h1>';
				$output .= 'Weet je zeker dat je deze handmatig wilt activeren? <br />';
				$output .= 'We kunnen op deze manier niet garanderen dat het email adres juist is.<br /><br />';	
			break;
		}
		
		//display info
		/* top row */
		$output .= '<div class="itemlist-row itemlist-heading">';
			$output .= '<div class="itemlist-single-column"><input type="checkbox" name="checkCtrl" value="checkAll" onClick="check_all(this);" class="itemlist-cbox-first">debiteurnr</div>';
			$output .= '<div class="itemlist-double-column">naam</div>';
			$output .= '<div class="itemlist-double-column">email</div>';
			$output .= '<div class="itemlist-single-column">woonplaats</div>';
			$output .= '<div class="itemlist-single-column">klant sinds</div>';
			$output .= '<div class="itemlist-single-column">nieuwsbrief</div>';
			$output .= '<div class="itemlist-single-column">status</div>';
			$output .= '<div class="itemlist-single-column status-column"></div>';
		$output .= '</div>';
		$output .= '<div class="itemlist-row">';
			$output .= '<div class="itemlist-single-column"><input type="checkbox"  name="option[]" id="option"  value="'.$customerId.'" class="itemlist-cbox-first">'.$customerCode.'</div>';
			$output .= '<div class="itemlist-double-column">'.$name.'</div>';
			$output .= '<div class="itemlist-double-column">'.$email.'</div>';
			$output .= '<div class="itemlist-single-column">'.$city.'</div>';
			$output .= '<div class="itemlist-single-column">'.$memberSince.'</div>';
			$output .= '<div class="itemlist-single-column">'.$displayNewsletter.'</div>';
			$output .= '<div class="itemlist-single-column">'.$displayStatus.'</div>';
			$output .= '<div class="itemlist-single-column status-column">';
			$output .= '</div>';
		$output .= '</div>';
		$output .= '<div class="clear"></div>';	
		$output .= '<form action="" method="post">';
			switch($newstatus){
				case 'to2': 
					$output .= '<input type="submit" name="sbm-block-customer" value="block" class="btn176 proceed"/>';
				break;
				case 'from2to1': 
					$output .= '<input type="submit" name="sbm-unblock-customer" value="unblock" class="btn176 proceed"/>';
				break;
				case 'from0to1': 
					$output .= '<input type="submit" name="sbm-activate-customer" value="activate" class="btn176 proceed"/>';
				break;
			}
			$output .= '<input type="submit" name="back" value="cancel" class="btn176"/>';
			$output .= '<input type="hidden" name="location" value="../../"/>';
			$output .= '<input type="hidden" name="customerId" value="'.$customerId.'"/>';
		$output .= '</form>';
	}	
	return $output;
}


function askActivateCustomer(){
	$output = '';
		
	return $output;
}

function changeStatus($status){
	global $mysqli;	
	$customerId = $_POST['customerId'];
	if(!empty($customerId)){
		$mysqli->query('UPDATE shop_customer_login SET status = '.$status.' WHERE id = '.$customerId.' LIMIT 1');
	}
	header('location: '.$_POST['location']);
}




function showCustomerDetails(){
	global $mysqli;	
	
	/* language */
	$siteLabels = getLanguage();
	
	$output = '';
	// klant details
	
	if(defined('URL_SUB2')){
		$temp = explode('-', URL_SUB2);
		$customerId = $temp[1];
		//// get info \\\\
		if($result = $mysqli->query('SELECT DISTINCT cl.id AS customerId, cl.email, cl.status, 
													 cp.title, cp.customerCode, cp.firstName, cp.middleName, cp.lastName, cp.timest, cp.newsletter, 
													 cp.postal, cp.street, cp.number, cp.addition, cp.city, cp.country, cp.phone, cp.birthday
									FROM shop_customer_login AS cl, shop_customer_personal AS cp
									WHERE cl.personalId = cp.id
									AND cl.id = '.$customerId.' LIMIT 1')){
			$numOrders = $result->num_rows;							 											 
			$record = $result->fetch_assoc(); 
			//// store info \\\\ 
			$customerId = $record['customerId'];				
			$customerCode = $record['customerCode'];				
			$memberSince = $record['timest'];
			$email = fromDatabase($record['email']);							
			$newsletter = $record['newsletter'];				
			$status = $record['status'];
			$title = $record['title'];
			$phone = $record['phone'];
			$street = $record['street'];
			$number = $record['number'];
			$addition = $record['addition'];
			$postal = $record['postal'];				
			$city = $record['city'];	
			$country = $record['country'];				

			$birthday = $record['birthday'];	
			
			$firstName = $record['firstName'];
			$middleName = $record['middleName'];
			$lastName = $record['lastName'];
			
			$adress = $street.' '.$number;
			if(!empty($addition)) $adress .= $addition;
			
			$country = $record['country'];
			
			$name = $record['firstName'];
			if(!empty($record['middleName'])) $name .= ' '.$record['middleName'];
			$name .= ' '.$record['lastName'];
			
			//// create display info \\\\		
			$memberSince = date('d / m / Y',$memberSince); # memberSince
			$newsletter == 1 ? $displayNewsletter = 'ja' : $displayNewsletter = 'nee';
			switch($status){
				case 0: $displayStatus = 'non-actief';
				break;
				case 1: $displayStatus = 'actief';
				break;
				case 2: $displayStatus = 'geblokkeerd';
				break;
			}
			
			$output .= '<h1>'.$siteLabels['customers-title'].' &rsaquo; '.strtolower($name).'</h1>';
			$output .= '<div class="submenu">';
				//buttons
				$output .= '<a href="/admin/customers/"><span class="btn176 btn176a proceed">Klantenoverzicht</span></a>';
			$output .= '</div>';
			
			$output .= '<div class="order-customer-column">';
				$output .= '<h2>klant details</h2>';
				
				$output .= '<table class="order-customer">';
					// debiteur nummer
					$output .= '<tr>';
						$output .= '<td>Debiteurnummerr</td>';
						$output .= '<td class="order-customer-detail">'.$customerCode.'</td>';
					$output .= '</tr>';
					// debiteur nummer
					$output .= '<tr>';
						$output .= '<td>Klant sinds</td>';
						$output .= '<td class="order-customer-detail">'.$memberSince.'</td>';
					$output .= '</tr>';
						// space
					$output .= '<tr>';
						$output .= '<td colspan="2">&nbsp;</td>';
					$output .= '</tr>';
					// voornaam
					$output .= '<tr>';
						$output .= '<td>Voornaam</td>';
						$output .= '<td class="order-customer-detail">'.ucfirst($firstName).'</td>';
					$output .= '</tr>';
					// naam
					$output .= '<tr>';
						$output .= '<td>Naam</td>';
						$output .= '<td class="order-customer-detail">'.ucfirst($lastName);
						if(!empty($middleName)) $output .= ', '.$middleName;
						$output .= '</td>';
					$output .= '</tr>';
					// email
					$output .= '<tr>';
						$output .= '<td>Email</td>';
						$output .= '<td class="order-customer-detail">'.$email.'</td>';
					$output .= '</tr>';
					// tel
					$output .= '<tr>';
						$output .= '<td>Tel.</td>';
						$output .= '<td class="order-customer-detail">'.$phone.'</td>';
					$output .= '</tr>';
					
					// space
					$output .= '<tr>';
						$output .= '<td colspan="2">&nbsp;</td>';
					$output .= '</tr>';
					
					// afleveradres / straat
					$output .= '<tr>';
						$output .= '<td>Adres.</td>';
						$output .= '<td class="order-customer-detail">'.$adress.'</td>';
					$output .= '</tr>';
					// postcode
					$output .= '<tr>';
						$output .= '<td>&nbsp;</td>';
						$output .= '<td class="order-customer-detail">'.$postal.' '.$city.'</td>';
					$output .= '</tr>';
					// land
					$output .= '<tr>';
						$output .= '<td>&nbsp;</td>';
						$output .= '<td class="order-customer-detail">'.$country.'</td>';
					$output .= '</tr>';
					
					// space
					$output .= '<tr>';
						$output .= '<td colspan="2">&nbsp;</td>';
					$output .= '</tr>';
					
					$output .= '<tr>';
						$output .= '<td>Geboren</td>';
						$output .= '<td class="order-customer-detail">'.date('d / m / Y',$birthday).'</td>';
					$output .= '</tr>';
					$output .= '<tr>';
						$output .= '<td>Nieuwsbrief</td>';
						$output .= '<td class="order-customer-detail">'.$displayNewsletter.'</td>';
					$output .= '</tr>';
					
				$output .= '</table>';
			$output .= '</div>';	
		}
		else{
			$output .= 'Geen klant gevonden';
		}		
	}
	else{
		$output .= 'Geen klant gevonden';
	}
	
	return $output;
}
	
function sortFilter(){
	//get sort & filter
	
	$sort = $_POST['sort-by'];
	$filter = linkname($_POST['filter-by']);
	$direction = linkname($_POST['orderdir']);
	
	header('location: /admin/customers/sortby-'.$direction.'-'.$sort.'/filterby-'.$filter.'/');
}	
	
?>