<?php

	// Debugging
//	error_reporting(E_ALL);
//	ini_set("display_errors", 1);
	
	// This script can be in 'live' or 'debug' mode
	$mode = 'live';
//	$mode = 'debug';
	
	include('api-connect.inc.php');
	if ($mode == 'debug') {
		include('db-connect.inc.php');
		$smtp_host = 'localhost';
		$from_address = 'test@localhost';
	} else {
		include('db-connect.inc.php');
		$smtp_host = 'smtp.fastnet.co.uk';
		$from_address = 'crons@flude.com';
	}
	
	include('common.inc.php');
	
	include('class.phpmailer.php');
	$mail = new PHPMailer(); // defaults to using php "mail()"
	
	$mail->IsSMTP();
	$mail->Host= $smtp_host;
	$mail->Port='25';
	
	// Begin email building
	$mail_body = "Details of changes/additions/deletions: " . '<br /><br />';

	$mail->AddReplyTo($from_address,"Flude cron monitor");
	$mail->SetFrom($from_address,"Flude cron monitor");

	$address = "richard@fastnet.co.uk";
	$mail->AddAddress($address, "Flude cron monitor");

	$mail->Subject = "";
	
	// initialise email subject string
	$subject_str = "";
	

	// Get most recent token from database
	$token_qry_result 		= $db->query("SELECT token from api_tokens WHERE token <> '' ORDER BY api_tokenID DESC LIMIT 1");
	while($row = $token_qry_result->fetch(PDO::FETCH_ASSOC)) {
    	$token 					= $row['token'];
    }
    if (isset($token)) {
    	if ($mode == 'debug') echo 'token found: ' . $token . '<br />';	    
    } else {
    	// give a previously working token just so that the request can be made
    	$token = 'QJJUNDGVGCHKDPEVAYBQYCWMJVKJLAMXIBFBYVOGBMGOTXBQEV';
	    if ($mode == 'debug') echo 'no token found<br />';
    }
    
	
	// construct request url string
	
	// get most recently made request date and time values
	$request_qry_result 	= $db->query("SELECT * from requests_log ORDER BY requestID DESC LIMIT 1");
	while($row = $request_qry_result->fetch(PDO::FETCH_ASSOC)) {
		$year 					= $row['year'];
		$month 					= $row['month'];
		$day 					= $row['day'];
		$hour 					= $row['hour'];
		$min 					= $row['min'];
		$sec 					= $row['sec'];		
	}
	
	// date ovverride to debug script
//	$year 					= '2013';
//	$month 					= '07';
//	$day 					= '23';
//	$hour 					= '06';
//	$min 					= '00';
//	$sec 					= '00';
	
	$url_datetime_str = "$year/$month/$day/$hour/$min/$sec";
	
	$changed_get_url	= $url_stem . $datafeed_id . '/v' . $version . '/property/' . $url_datetime_str;
	// eg: http://webservices.vebra.com/export/fludeapi/v3/property/2013/07/29/10/00/19
	$mail_body .= '<br />constructed vebra web service URL: ' . $changed_get_url . '<br /><br />';
	if ($mode == 'debug') echo '<br />constructed vebra web service URL: ' . $changed_get_url . '<br /><br />';
	

	// Make the request using the token retrieved from the database
	$ch = curl_init();
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); 
	curl_setopt($ch, CURLOPT_HEADER, TRUE);
	curl_setopt($ch, CURLOPT_URL, $changed_get_url);
	curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: Basic '.base64_encode($token) ));

	$response = curl_exec($ch);

	// Check for errors
	if (curl_errno($ch)) {
		if ($mode == 'debug') echo 'Errors occurred';
		$subject_str .= "Curl error ~~ ";
		$mail->Subject = $subject_str;
		$mail->MsgHTML($mail_body);
		$mail->Send();
		exit();
	}
	
	// Debug marker output
	if ($mode == 'debug') echo '<br />Marker - 1<br />';

	// Begin to process the response
	
	$info = curl_getinfo($ch);
	if ($mode == 'debug') echo '<br />Returned HTTP code: ' . $info['http_code']  . '<br />';
	$mail_body .= 'Returned HTTP code: ' . $info['http_code'] . '<br /><br />';
	// If the token is no longer valid a 401 error code will be returned
	if (($info['http_code'] === 401) || ($info['http_code'] === 404)) {
	
		if ($mode == 'debug') echo '<strong>New token needed...</strong><br /><br />';
		$subject_str .= "New token needed ~~ ";

		
		// Try again using username and password combo to get and store the token
		curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: Basic '.base64_encode($userpass) ));
		$response = curl_exec($ch);
		
		// extract and store the token from the response
		list($headers, $body) = explode("\r\n\r\n", $response);
		$headers = nl2br($headers);
		$headers = explode('<br />', $headers);
		foreach($headers as $header) {
			$components = explode(': ', trim($header));
			$headers[$components[0]] = $components[1];
		}
		$token = $headers['Token'];
		if ($token == '') $token = 'QJJUNDGVGCHKDPEVAYBQYCWMJVKJLAMXIBFBYVOGBMGOTXBQEV'; 
		$subject_str .= $token . " ~~ ";
		if ($mode == 'debug') echo '<strong>New token is:</strong> [' . $token . ']<br /><br />';
		$mail_body .= '<strong>New token is:</strong> ' . $token . '<br /><br />';
		
		// Now store the token so that it can be used for the next attempted run of this script
		$nowdate_str = date("Y").'-'.date("m").'-'.date("d").' '.date("H").':'.date("i").':'.date("s");

		$sql = "INSERT INTO api_tokens (issue_datetime,token) VALUES (:issue_datetime,:token)";
		$q = $db->prepare($sql);
		$q->execute(array(':issue_datetime'=>$nowdate_str, ':token'=>$token));
	} else {
		$mail_body .= 'Token: ' . $token . ' is valid<br /><br />';
	}
	
	
	// Debug marker output
	if ($mode == 'debug') echo '<br />Marker - 2<br />';
	
	// Get the details from the response

	// extract body element (the XML) from the response
	list($headers, $body) = explode("\r\n\r\n", $response);
	
//	if ($mode == 'debug') echo 'Response: ' . $response . '<br /><br />';
	
	try {
		$propertieschanged = new SimpleXMLElement($body);
	} catch (Exception $e) {
		$subject_str .= 'Caught exception: '.  $e->getMessage(). " ~~ ";
		$subject_str .= 'Most likely cause: no updated properties since most recent request. ~~ ';
		if ($mode == 'debug') {
			echo '<br /><br />';
			echo 'Caught exception: '.  $e->getMessage(). " ~~ " . '<br />';
			echo 'Most likely cause: no updated properties since most recent request. ~~ ' . '<br />';
			echo '<br /><br />';
		}
		
		// Log the request
		$year 		= date("Y");
		$month 		= date("m");
		$day 		= date("d");
		$hour 		= date("H");
		$min 		= date("i");
		$sec 		= date("s");
		
		// Needs updating as per above sql statements
		
		$sql = "INSERT INTO requests_log (year,month,day,hour,min,sec) VALUES (:year,:month,:day,:hour,:min,:sec)";
		$q = $db->prepare($sql);
		$q->execute(array(':year'=>$year, ':month'=>$month, ':day'=>$day, ':hour'=>$hour, ':min'=>$min, ':sec'=>$sec));
	
		$mail->Subject = $subject_str;
		
		// debug and add email body
		if ($mode == 'debug') echo 'Email subject: ' . $subject_str . '<br /><br />';
		if ($mode == 'debug') echo 'Email body: ' . $mail_body . '<br /><br />';
		$mail->MsgHTML($mail_body);
	
		if(!$mail->Send()) {
			if ($mode == 'debug') echo "Mailer Error: " . $mail->ErrorInfo;
		} else {
			if ($mode == 'debug') echo "Message sent.";
		}
		exit();
	}
	
	
	// Debug marker output
	if ($mode == 'debug') echo '<br />Marker - 3<br />';
	
		
	foreach ($propertieschanged->property as $property) {
		
		$prop_id 			= $property->propid;
		$lastchanged 		= $property->lastchanged;
		$action 			= $property->action;
		$property_url 		= $property->url;
		
		if ($mode == 'debug') echo 'PropertyID: ' . $prop_id . '<br />';
		$mail_body .= 'PropertyID which needs changing or deleting: ' . $prop_id . '<br />';
		
		switch ($action) {
		
			// if the property has been deleted
			case 'deleted':
			
				if ($mode == 'debug') echo 'Property needs to be deleted<br><br>';
				$mail_body .= 'Property needs to be deleted<br><br>';

        		// update this property's active status
        		        		
        		$property_update_qry = "UPDATE properties SET ";
				$property_update_qry .= "active = 0, lastchanged = :lastchanged ";
				$property_update_qry .= "WHERE propertyID = :prop_id";
				
				$stmt = $db->prepare($property_update_qry);
				$stmt->execute(array(
        			'prop_id' => $prop_id,
        			'lastchanged' => $lastchanged
        		));

        		
				// also make inactive any files associated with this property
				$stmt = $db->prepare('UPDATE properties_files SET active = 0 WHERE propertyID = :prop_id');
				$stmt->execute(array(
        			'prop_id'   => $prop_id
        		));
				
				$subject_str .= "Deleted property: $prop_id ~~ ";
        		
        		break;
        			
			case 'updated':
			
				$mail_body .= 'Property needs to be updated<br><br>';
			
        		// first check if this property already exists in the database
        		$stmt = $db->prepare('SELECT address_display from properties WHERE propertyID = :prop_id LIMIT 1');
        		$stmt->execute(array(
        			'prop_id' => $prop_id
        		));
        		$result = $stmt->fetchAll();
        		$resultCount = 0;
        		if (count($result)) {
	        		foreach($result as $row) {
		        		if (isset($row['year'])) $year 		= $row['year'];
		        		if (isset($row['month'])) $month 	= $row['month'];
		        		if (isset($row['day'])) $day 		= $row['day'];
		        		if (isset($row['hour'])) $hour 		= $row['hour'];
		        		if (isset($row['min'])) $min 		= $row['min'];
		        		if (isset($row['year'])) $sec 		= $row['sec'];
		        		$resultCount++;
		        	}   
		        }
		        $property_check_num_rows 	= $resultCount;
				
				// Make a new request for full property details
				curl_setopt($ch, CURLOPT_URL, $property_url);
				curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: Basic '.base64_encode($token) ));
				$response = curl_exec($ch);
		
				// Check for errors
				if (curl_errno($ch)) {
					echo 'Errors occurred';
					exit();
				}
		
				// extract body element (the XML) from the response
				list($headers, $body) = explode("\r\n\r\n", $response);
				$property = new SimpleXMLElement($body);
					
				// Grab all property info from the XML
				$featured	 		= $property['featured'];
				$address_county 	= quote2entities($property->address->county);
				$address_display 	= quote2entities($property->address->display);
				$address_town 		= quote2entities($property->address->town);
				$available			= $property->available;
				$longitude			= $property->longitude;
				$latitude			= $property->latitude;
				$custom_status		= $property->custom_status;
				$type1			= $property->type[0];
				$type2			= $property->type[1];
				$comm_rent		= $property->comm_rent;
				$premium			= $property->premium;
				$service_charge		= $property->service_charge;
				$rateable_value		= $property->rateable_value;
				$furnished			= $property->furnished;
				$bedrooms		= $property->bedrooms;
				$receptions		= $property->receptions;
				$bathrooms		= $property->bathrooms;
			
				// Area variables
				$area_metric_unit 	= '';					
				$area_metric_min 	= 0;
				$area_metric_max 	= 0;
				$area_imperial_unit 	= '';
				$area_imperial_min 	= 0;
				$area_imperial_max 	= 0;
			
				$area1			= $property->area[0];
				if ($area1['measure'] == 'metric') {
					$area_metric_unit	= $area1[0]['unit'];
					$area_metric_min 	= $area1[0]->min;
					$area_metric_max 	= $area1[0]->max;
				} else if ($area1['measure'] == 'imperial') {
					$area_imperial_unit	= $area1[0]['unit'];
					$area_imperial_min 	= $area1[0]->min;
					$area_imperial_max 	= $area1[0]->max;
				}
				$area2			= $property->area[1];
				if ($area2['measure'] == 'metric') {
					$area_metric_unit	= $area2['unit'];
					$area_metric_min 	= $area2->min;
					$area_metric_max 	= $area2->max;
				} else if ($area2['measure'] == 'imperial') {
					$area_imperial_unit	= $area2['unit'];
					$area_imperial_min 	= $area2->min;
					$area_imperial_max 	= $area2->max;
				}

				$description = quote2entities($property->description);
				
				// Now get info of all files related to this property
				// [The Vebra API version 3 Get Changed Files call is buggy - it doesn't return all files which have been updated...]
				// [This method bypasses that call and will catch all files for the property and make sure that everything is up to date]
				
				$property_files = array();
				$loopCount = 0;
				foreach ($property->files->file as $file) {
					$file_url = $file->url;
					echo 'file url: ' . $file_url . '<br />';
					$file_url_elements = explode(".", $file_url);
					$file_extension = $file_url_elements[count($file_url_elements) - 1];
					echo 'file extension: ' . $file_extension . '<br /><br />';
					$property_files[$loopCount] = array($file_url, $file_extension);
					
					$loopCount++;
				}
				

				if ($property_check_num_rows === 0) {
				
					$subject_str .= "New property: $prop_id ~~ ";
				
					echo '<strong>This is a new property</strong><br><br>';
					$mail_body .= 'Property needs to be inserted<br><br>';

					// insert details of the new property
					$property_insert_qry = "INSERT INTO properties ";
					$property_insert_qry .= "(propertyID, lastchanged, featured, address_display, county, town, available, longitude, latitude, custom_status, type1, type2, ";
					$property_insert_qry .= "comm_rent, premium, service_charge, rateable_value, furnished, bedrooms, receptions, bathrooms, ";
					$property_insert_qry .= "area_metric_unit, area_metric_min, area_metric_max, area_imperial_unit, area_imperial_min, area_imperial_max, description) VALUES (";
			
					$property_insert_qry .= ":prop_id, :lastchanged, :featured, :address_display, :address_county, :address_town, :available, :longitude, :latitude, :custom_status, :type1, :type2, ";
					$property_insert_qry .= ":comm_rent, :premium, :service_charge, :rateable_value, :furnished, :bedrooms, :receptions, :bathrooms, ";
					$property_insert_qry .= ":area_metric_unit, :area_metric_min, :area_metric_max, :area_imperial_unit, :area_imperial_min, :area_imperial_max, :description)";
					
					
					$mail_body .= $property_insert_qry . '<br><br>';
					
					echo $property_insert_qry . '<br><br>';					
					
					$stmt = $db->prepare($property_insert_qry);
					$stmt->execute(array(
        				'prop_id' => $prop_id,
        				'lastchanged' => $lastchanged, 
        				'featured' => $featured, 
        				'address_display' => $address_display, 
        				'address_county' => $address_county, 
        				'address_town' => $address_town, 
        				'available' => $available, 
        				'longitude' => $longitude, 
        				'latitude' => $latitude, 
        				'custom_status' => $custom_status, 
        				'type1' => $type1, 
        				'type2' => $type2,
        				'comm_rent' => $comm_rent, 
        				'premium' => $premium, 
        				'service_charge' => $service_charge, 
        				'rateable_value' => $rateable_value, 
        				'furnished' => $furnished, 
        				'bedrooms' => $bedrooms, 
        				'receptions' => $receptions, 
        				'bathrooms' => $bathrooms,
        				'area_metric_unit' => $area_metric_unit, 
        				'area_metric_min' => $area_metric_min, 
        				'area_metric_max' => $area_metric_max, 
        				'area_imperial_unit' => $area_imperial_unit, 
        				'area_imperial_min' => $area_imperial_min, 
        				'area_imperial_max' => $area_imperial_max, 
        				'description' => $description
        			));

					
					// Now insert all files associated with this property
					if (count($property_files) > 0) {
					
						$loopCount = 0;
						foreach ($property_files as $property_file) {
						
							$property_file_url = $property_files[$loopCount][0];
							$property_file_extension = $property_files[$loopCount][1];
							
							$properties_files_insert_qry = "INSERT INTO properties_files (propertyID, file_url, file_extension) VALUES (:prop_id, :property_file_url, :property_file_extension)";
							$stmt = $db->prepare($properties_files_insert_qry);
							$stmt->execute(array(
								'prop_id' => $prop_id,
								'property_file_url' => $property_file_url,
								'property_file_extension' => $property_file_extension
							));

							$loopCount++;
						}
						
					}
			
				} else {
				
					$subject_str .= "Updated property: $prop_id ~~ ";
					
					// Property details need to be updated
					echo '<strong>Property needs to be updated</strong><br><br>';
					$mail_body .= '<strong>Property needs to be updated</strong><br><br>';
					
					$property_update_qry = "UPDATE properties SET ";
					$property_update_qry .= "lastchanged = :lastchanged, featured = :featured, address_display = :address_display, county = :address_county, town = :address_town, available = :available, longitude = :longitude, latitude = :latitude, custom_status = :custom_status, type1 = :type1, type2 = :type2, comm_rent = :comm_rent, premium = :premium, service_charge = :service_charge, rateable_value = :rateable_value, furnished = :furnished, bedrooms = :bedrooms, receptions = :receptions, bathrooms = :bathrooms, area_metric_unit = :area_metric_unit, area_metric_min = :area_metric_min, area_metric_max = :area_metric_max, area_imperial_unit = :area_imperial_unit, area_imperial_min = :area_imperial_min, area_imperial_max = :area_imperial_max, description = :description ";
					$property_update_qry .= "WHERE propertyID = :prop_id";
					
//					echo $property_update_qry . '<br><br>';

					$stmt = $db->prepare($property_update_qry);
					$stmt->execute(array(
        				'prop_id' => $prop_id,
        				'lastchanged' => $lastchanged, 
        				'featured' => $featured, 
        				'address_display' => $address_display, 
        				'address_county' => $address_county, 
        				'address_town' => $address_town, 
        				'available' => $available, 
        				'longitude' => $longitude, 
        				'latitude' => $latitude, 
        				'custom_status' => $custom_status, 
        				'type1' => $type1, 
        				'type2' => $type2,
        				'comm_rent' => $comm_rent, 
        				'premium' => $premium, 
        				'service_charge' => $service_charge, 
        				'rateable_value' => $rateable_value, 
        				'furnished' => $furnished, 
        				'bedrooms' => $bedrooms, 
        				'receptions' => $receptions, 
        				'bathrooms' => $bathrooms,
        				'area_metric_unit' => $area_metric_unit, 
        				'area_metric_min' => $area_metric_min, 
        				'area_metric_max' => $area_metric_max, 
        				'area_imperial_unit' => $area_imperial_unit, 
        				'area_imperial_min' => $area_imperial_min, 
        				'area_imperial_max' => $area_imperial_max, 
        				'description' => $description
        			));
					
					// Completely reset all files associated with this property - delete any existing then insert all found from this call
					$property_files_delete_qry = "DELETE from properties_files WHERE propertyID = :prop_id";
					$stmt = $db->prepare($property_files_delete_qry);
					$stmt->execute(array(
        				'prop_id' => $prop_id
        			));

					// Now insert all files associated with this property
					if (count($property_files) > 0) {
					
						$loopCount = 0;
						foreach ($property_files as $property_file) {
						
							$property_file_url = $property_files[$loopCount][0];
							$property_file_extension = $property_files[$loopCount][1];
						
							$properties_files_insert_qry = "INSERT INTO properties_files (propertyID, file_url, file_extension) VALUES (:prop_id, :property_file_url, :property_file_extension)";
							$stmt = $db->prepare($properties_files_insert_qry);
							$stmt->execute(array(
								'prop_id' => $prop_id,
								'property_file_url' => $property_file_url,
								'property_file_extension' => $property_file_extension
							));							
							
							$loopCount++;
						}
						
					}

				}

			// end switch case
			break;

		}
		
	}
	
	// Make sure all requests are closed
	curl_close($ch);
	
	// Log the request
	$year 		= date("Y");
	$month 		= date("m");
	$day 		= date("d");
	$hour 		= date("H");
	$min 		= date("i");
	$sec 		= date("s");
	
	$request_insert_qry = "INSERT INTO requests_log (year,month,day,hour,min,sec,updates) VALUES (:year,:month,:day,:hour,:min,:sec,1)";
	$stmt = $db->prepare($request_insert_qry);
	$stmt->execute(array(
        'year' => $year,
        'month' => $month,
        'day' => $day,
        'hour' => $hour,
        'min' => $min,
        'sec' => $sec
    ));

	
	// Send an email with full details in the subject line, if not already sent above
	$mail->Subject = $subject_str;
//	$mail->Send();

	echo 'Marker - final<br />';
	
	// debug and add email body
	echo 'Email subject: ' . $subject_str . '<br /><br />';
	echo 'Email body: ' . $mail_body . '<br /><br />';
	$mail->MsgHTML($mail_body);
	
	if(!$mail->Send()) {
	  echo "Mailer Error: " . $mail->ErrorInfo;
	} else {
	  echo "Message sent.";
	}

?>