<?php
function get_branch($appId){
	global $conn; 
	$array = array();

	$stmt = mysqli_prepare($conn, "SELECT id, name FROM branch WHERE appId =".$appId);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $id, $name);

	while (mysqli_stmt_fetch($stmt)) {
		$array[] = array("id" => $id, "name" => $name);
	}
	mysqli_stmt_close($stmt);

	return $array;
}

function get_stylist($branchId){
	global $conn; 
	$array = array();

	$stmt = mysqli_prepare($conn, "SELECT id, name, image FROM stylist WHERE branchId =".$branchId);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $id, $name, $image);

	while (mysqli_stmt_fetch($stmt)) {
		$array[] = array("id" => $id, "name" => $name, "image" => $image);
	}
	mysqli_stmt_close($stmt);

	return $array;
}

function get_prior_date($branchId){
	global $conn; 

	$stmt = mysqli_prepare($conn, "SELECT duration, unitOfMeasurement FROM booking_priod_configuration WHERE branchId=".$branchId." LIMIT 1");
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $duration, $unit);
	$array = array();
	while (mysqli_stmt_fetch($stmt)) {
		$array[] = array("duration"=>$duration, "unit"=>$unit);
	}
	mysqli_stmt_close($stmt);
	return $array;
}

function get_max_date($branchId){
	global $conn; 

	$stmt = mysqli_prepare($conn, "SELECT duration, unitOfMeasurement FROM booking_max_configuration WHERE branchId=".$branchId." LIMIT 1");
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $duration, $unit);
	$array = array();
	while (mysqli_stmt_fetch($stmt)) {
		$array[] = array("duration"=>$duration, "unit"=>$unit);
	}
	mysqli_stmt_close($stmt);
	return $array;
}

function get_schedule($branchId, $stylistId, $bookingDate){
	global $conn; 
	
	$stmt = mysqli_prepare($conn, "SELECT business_start, business_end, interval_slots, rest_start, rest_end FROM booking_timeSettings WHERE branchid = ? LIMIT 1");
	mysqli_stmt_bind_param($stmt, "i", $branchId);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $business_start, $business_end, $interval_slots, $rest_start, $rest_end);

	while (mysqli_stmt_fetch($stmt)) {
		$start = $business_start;
		$end = $business_end;
		$interval = $interval_slots;
		$r_start = $rest_start;
		$r_end = $rest_end;
	}
	mysqli_stmt_close($stmt);

	$multiplyGet = 1;
	$stmt = mysqli_prepare($conn, "SELECT multiply FROM extraTimeRequired WHERE stylistId = ?");
	mysqli_stmt_bind_param($stmt, "i", $stylistId);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $multiply);

	while (mysqli_stmt_fetch($stmt)) {
		$multiplyGet = $multiply;
	}
	mysqli_stmt_close($stmt);

	$priodDurationNeeded = 0;
	
	$stmt = mysqli_prepare($conn, "SELECT duration, unitOfMeasurement FROM booking_priod_configuration WHERE branchId=? LIMIT 1");
	mysqli_stmt_bind_param($stmt, "i", $branchId);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $duration, $unitOfMeasurement);

	while (mysqli_stmt_fetch($stmt)) {
	    $priodDurationNeeded = $duration;
	    $unit = $unitOfMeasurement;
	}
	mysqli_stmt_close($stmt);

	$open_time = strtotime($start);
	$close_time = strtotime($end);
	$inter = 60*$interval*$multiplyGet;
	$rest_start = strtotime($r_start);
	$rest_end = strtotime($r_end);
	$now = time();
	$output = "";
	
	$today = date("Y-m-d");
	$currentTime = date("H:i");

	if(is_null($bookingDate) || $bookingDate == "" || $bookingDate == "(null)"){
		$bookingDate = $today;
	}

	for( $i=$open_time; $i<$close_time; $i+=$inter) {
		//if( $i < $now) continue;
		if($i >= $rest_start && $i < $rest_end){
			$output .= "";
		}else{
			$output .= date("H:i",$i)." ";
		}
	}
							
	$slotsArray = explode(" ",$output);
	// minus 1 because last array contain "space"
	$totalSlots = count($slotsArray) - 1;

	$stmt = mysqli_prepare($conn, "SELECT appointmentTime, confirmation FROM history WHERE branchid = ? AND stylistId = ? AND appointmentDate = ?");
	mysqli_stmt_bind_param($stmt, "iis", $branchId, $stylistId, $bookingDate);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $appointmentTime, $confirmation);

	$array = array();
	while (mysqli_stmt_fetch($stmt)) {
		$arrAppointmentTime = explode(":", $appointmentTime);
		$time = $arrAppointmentTime[0].":".$arrAppointmentTime[1];
		$array[] = array("time"=>$time, "confirmation"=>$confirmation);
	}
	$totalTaken = count($array);
	
	$schedule = array();

	// 20131202
	$todayDate =date('Y-m-d H:i:s');

	//$str = "+ ".$priodDurationNeeded." hours";
	$str = "+ ".$priodDurationNeeded." ".$unit;
	$dateOfPriod = date('Y-m-d H:i:s', strtotime($todayDate. $str));

	$priodArray = explode(" ", $dateOfPriod);
	$priodTimeArray = explode(":", $priodArray[1]);
	$priodTime = $priodTimeArray[0].":".$priodTimeArray[1];

	$actualBookingDate = $bookingDate." ".date('H:i:s');
	//echo "<br />";
	//echo "$dateOfPriod";
	
	if($actualBookingDate > $dateOfPriod){
	
		for($j=0; $j<$totalSlots; $j++){
			$taken = 0;
			for($k=0; $k<$totalTaken; $k++){
				if($array[$k]["time"] == $slotsArray[$j]){
					$taken = 1;
					$confirm = $array[$k]["confirmation"];
			
					if($confirm == 0){
						$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OCCUPIED");
					}else if($confirm == 1){
						$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OCCUPIED");
					}else{
						//$confirm == 3
						$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OFF");
					}
				}
			}
	
			if($taken == 0){
				//echo "123";
				$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"AVAILABLE");
			}
		}
	}else if($actualBookingDate == $dateOfPriod){
		for($j=0; $j<$totalSlots; $j++){
			if($slotsArray[$j] > $currentTime || $slotsArray[$j] > $priodTime){
			
				$taken = 0;
				for($k=0; $k<$totalTaken; $k++){
					if($array[$k]["time"] == $slotsArray[$j]){
						$taken = 1;
						$confirm = $array[$k]["confirmation"];
				
						if($confirm == 0){
							$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OCCUPIED");
						}else if($confirm == 1){
							$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OCCUPIED");
						}else{
							//$confirm == 3
							$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OFF");
						}
					}
				}
		
				if($taken == 0){
					$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"AVAILABLE");
				}
			}
		}
	}else{
		
		for($j=0; $j<$totalSlots; $j++){
			if(($priodArray[0] == $bookingDate) && ($slotsArray[$j] >= $priodTime)){
				$taken = 0;
				for($k=0; $k<$totalTaken; $k++){
					if($array[$k]["time"] == $slotsArray[$j]){
						$taken = 1;
						$confirm = $array[$k]["confirmation"];
				
						if($confirm == 0){
							$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OCCUPIED");
						}else if($confirm == 1){
							$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OCCUPIED");
						}else{
							//$confirm == 3
							$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"OFF");
						}
					}
				}
		
				if($taken == 0){
					//echo "123";
					$schedule[] = array("time"=>$slotsArray[$j] , "taken"=>$taken, "confirmation"=>"AVAILABLE");
				}	
			}
		}
	}

	//$string = json_encode($schedule);
	//echo $string;
	
	mysqli_stmt_close($stmt);

	return $schedule;
}

function booking($name, $gender, $dob, $phone, $email, $branchId, $stylistId, $date, $time, $remark){
	
	global $conn; 
	$result = array();
	
	$time = $time.":00";
	$today = date("Y-m-d");
	$timeSelected = date("H:i:s");
	$dateAdded = date("Y-m-d H:i:s");

	$stmt = mysqli_prepare($conn, "SELECT confirmationVal FROM booking_confirmation WHERE branchId = ? LIMIT 1");
	mysqli_stmt_bind_param($stmt, "i", $branchId);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $confirm);

	while (mysqli_stmt_fetch($stmt)) {
		$need_confirmation = $confirm;
	}
	
	if($need_confirmation == 1){
		$confirmation = 0;
	}else{
		$confirmation = 1;
	}
	
	mysqli_stmt_close($stmt);

	$total = 0;
	$stmt = mysqli_prepare($conn, "SELECT count(*) as num FROM history WHERE stylistId=? AND branchId=? AND appointmentDate=? AND appointmentTime=? LIMIT 1");
	mysqli_stmt_bind_param($stmt, "iiss", $stylistId, $branchId, $date, $time);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $num);
	while (mysqli_stmt_fetch($stmt)) {
		$total = $num;
	}
	mysqli_stmt_close($stmt);

	if($total > 0){
		
		$keys = array('status', 'message');
		$values = array('failed', 'Booking time has been taken.');
		$result = array_combine($keys, $values);
		
	} else{
		if( strtotime($date) ==  strtotime($today)){
			if( strtotime($timeSelected) <= strtotime($time) ){
				$stmt = mysqli_prepare($conn, "INSERT INTO history (
							name,
							gender,
							dob,
							phone,
							email,
							confirmation,
							branchId,
							stylistId, 
							os,
							remark,
							appointmentDate,
							appointmentTime,
							dateAdded
							)
							VALUES (
							?,
							?,
							?,
							?,
							?,
							?,
							?,
							?,
							'web',
							?,
							?,
							?,
							?
							)");
				
				mysqli_stmt_bind_param($stmt, "ssssssiissss", $name, $gender, $dob, $phone, $email, $confirmation, $branchId, $stylistId, $remark, $date, $time, $dateAdded);
				mysqli_stmt_execute($stmt);
				$row = mysqli_affected_rows($conn);
				$inserted_id = mysqli_insert_id($conn);
				mysqli_stmt_close($stmt);
				
				if($row > 0){
					
					$keys = array('status', 'message');
					$values = array('success', $inserted_id);
					$result = array_combine($keys, $values);
					
				}else{
					$keys = array('status', 'message');
					$values = array('failed', 'Booking is failed. Please try again later.');
					$result = array_combine($keys, $values);
				}
			}else{
				$keys = array('status', 'message');
				$values = array('failed', 'Booking time selected is passed');
				$result = array_combine($keys, $values);
			}
		}else{
			
			$stmt = mysqli_prepare($conn, "INSERT INTO history (
							name,
							gender,
							dob,
							phone,
							email,
							confirmation,
							branchId,
							stylistId, 
							os,
							remark,
							appointmentDate,
							appointmentTime,
							dateAdded
							)
							VALUES (
							?,
							?,
							?,
							?,
							?,
							?,
							?,
							?,
							'web',
							?,
							?,
							?,
							?
							)");
				
			mysqli_stmt_bind_param($stmt, "ssssssiissss", $name, $gender, $dob, $phone, $email, $confirmation, $branchId, $stylistId, $remark, $date, $time, $dateAdded);
			mysqli_stmt_execute($stmt);
			$row = mysqli_affected_rows($conn);
			$inserted_id = mysqli_insert_id($conn);
			mysqli_stmt_close($stmt);
			
			if($row > 0){
				$keys = array('status', 'message');
				$values = array('success', $inserted_id);
				$result = array_combine($keys, $values);
			}else{
				$keys = array('status', 'message');
				$values = array('failed', 'Booking is failed. Please try again later.');
				$result = array_combine($keys, $values);
			}
		}
	}
	
	if($inserted_id){
		$bookingNumber = generate_booking_number($inserted_id);
		
		$stmt = mysqli_prepare($conn, "UPDATE history SET booking_id = ? WHERE id = ?");
		mysqli_stmt_bind_param($stmt, "si", $bookingNumber, $inserted_id);
		mysqli_stmt_execute($stmt);
		mysqli_stmt_close($stmt);
	}
	
	
	return $result;
}

function check($booking_id = NULL, $phone = NULL){
	
	global $conn; 
	$result = array();
	
	if($booking_id){
		$stmt = mysqli_prepare($conn, "SELECT h.name, h.gender, h.dob, h.phone, h.email, h.branchId, b.name as branchName, h.stylistId, s.name as stylistName, h.confirmation, h.remark, h.appointmentDate, h.appointmentTime, h.booking_id FROM history h LEFT JOIN branch b ON h.branchId = b.id LEFT JOIN stylist s ON h.stylistId = s.id WHERE h.booking_id = ? OR h.phone = ? ORDER BY h.appointmentDate DESC, h.appointmentTime DESC");
		mysqli_stmt_bind_param($stmt, "ss", $booking_id, $booking_id);
	}
	/*else if($phone){
		$stmt = mysqli_prepare($conn, "SELECT h.name, h.gender, h.dob, h.phone, h.email, h.branchId, b.name as branchName, h.stylistId, s.name as stylistName, h.confirmation, h.remark, h.appointmentDate, h.appointmentTime, h.booking_id FROM history h LEFT JOIN branch b ON h.branchId = b.id LEFT JOIN stylist s ON h.stylistId = s.id WHERE h.phone = ?");
		mysqli_stmt_bind_param($stmt, "s", $phone);
	}
	*/
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt, $name, $gender, $dob, $phone, $email, $branchId, $branchName, $stylistId, $stylistName, $confirmation, $remark, $appointmentDate, $appointmentTime, $booking_id);

	while (mysqli_stmt_fetch($stmt)) {
		$result[] = array("name"=>$name , "gender"=>$gender, "dob"=>$dob, "phone"=>$phone , "email"=>$email, "branchId"=>$branchId, "branchName" => $branchName, "stylistId"=>$stylistId, "stylistName" => $stylistName ,"confirmation"=>$confirmation, "remark" => $remark, "appointmentDate" => $appointmentDate, "appointmentTime" => $appointmentTime, "bookingID" => $booking_id);
	}
	
	if(count($result) < 1){
		$keys = array('status', 'message');
		$values = array('failed', 'No record found!');
		$result = array_combine($keys, $values);
	}
	
	return $result;
}

function isValidEmail($email){
	if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
		return true;
	}
	else {
		return false;
	}
}

function generate_booking_number($bookingId){
	$length = strlen($bookingId);
	$totalLength = 11;
	$balanceLength = $totalLength - $length;
	
	for($i=0; $i<$balanceLength; $i++){
		$bookingPrefix .= "0"; 
	}
	
	$bookingNumber = "B".$bookingPrefix.$bookingId;
	
	return $bookingNumber;
}

	
?>