<?php

// header("Content-Type: text/html; charset=utf-8");

class Core {
	var $dbHost;
	var $dbUser;
	var $dbPass;
	var $dbName;
	var $connection;

	function startDBConnection() {
		$this->connection = mysql_connect($this->dbHost, $this->dbUser, $this->dbPass) or die (mysql_error());
		mysql_select_db($this->dbName);
		mysql_query("SET NAMES 'utf8'");
		mysql_query("SET CHARACTER SET utf8");
	}

	function endDBConnection() {
		mysql_close($this->connection);
	}

    //-User-

	function saveUser($user) {
		$now   = date('Y-m-d H:i:s');
		$query = "";
		$query = sprintf("INSERT INTO Users (cityId, email, password, fullName, registration, latitude, longitude) VALUES ('%s','%s','%s','%s','%s','%s','%s')",
		$user->cityId,
		mysql_real_escape_string($user->email),
		mysql_real_escape_string($user->password),
		mysql_real_escape_string($user->fullName),
		$now,
		$user->latitude,
		$user->longitude
		);
		mysql_query($query) or die (mysql_error());
	}

	function updateUser($user) {
		$query = "";
		$query = sprintf("UPDATE Users 
			SET 
			cityId = '%s', 
			email = '%s', 
			password = '%s', 
			fullName = '%s', 
			registration = '%s', 
			latitude = '%s', 
			longitude  = '%s'
			WHERE userId = '%s'",
		$user->cityId,
		mysql_real_escape_string($user->email),
		mysql_real_escape_string($user->password),
		mysql_real_escape_string($user->fullName),
		$user->registration,
		$user->latitude,
		$user->longitude,
		$user->userId
		);
		mysql_query($query) or die (mysql_error());
	}

	function getUserByUserId($userId) {
		$query = sprintf("SELECT
			userId,
			cityId,
			email,
			password,
			fullName,
			registration,
			latitude,
			longitude
			FROM Users WHERE userId = '%s'",
			mysql_real_escape_string($userId)
		);
		$result = mysql_query($query) or die (mysql_error());
		$row  = mysql_fetch_row($result);
		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			$user->userId       = $row[0];
			$user->cityId       = $row[1];
			$user->email        = $row[2];
			$user->password     = $row[3];
			$user->fullName     = $row[4];
			$user->registration = $row[5];
			$user->latitude 	= $row[6];
			$user->longitude 	= $row[7];

			mysql_freeresult($result);
			return $user;
		}
	}

	function getUserByEmail( $email ) {
		$query = sprintf("SELECT
			userId,
			cityId,
			email,
			password,
			fullName,
			registration,
			latitude,
			longitude
			FROM Users WHERE email = '%s'",
			mysql_real_escape_string($email)
		);
		$result = mysql_query($query) or die (mysql_error());
		$row  = mysql_fetch_row($result);
		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			$user->userId       = $row[0];
			$user->cityId       = $row[1];
			$user->email        = $row[2];
			$user->password     = $row[3];
			$user->fullName     = $row[4];
			$user->registration = $row[5];
			$user->latitude 	= $row[6];
			$user->longitude 	= $row[7];

			mysql_freeresult($result);
			return $user;
		}
	}

	function getUserIdByEmail($email) {
		$query = sprintf("SELECT userId FROM Users WHERE email = '%s'",
			mysql_real_escape_string($email));

		$result = mysql_query($query) or die (mysql_error());

		$row = mysql_fetch_row($result);

		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			mysql_freeresult($result);
			return $data;
		}
	}

	function userMatches($user) {
		$query = sprintf("SELECT userId FROM Users WHERE email = '%s' AND password = '%s'",
			mysql_real_escape_string($user->email),
			mysql_real_escape_string($user->password));

		$result = mysql_query($query) or die (mysql_error());

		$row = mysql_fetch_row($result);

		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			mysql_freeresult($result);
			return $data;
		}
	}

	function getUserIdByDni($dni) {
		$query = sprintf("SELECT userId FROM Users WHERE dni = '%s'",
			$dni);

		$result = mysql_query($query) or die (mysql_error());

		$row = mysql_fetch_row($result);

		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			mysql_freeresult($result);
			return $data;
		}
	}

	function sendMailToUser($user, $client) {

		$to         = $user->email . "," . "luishon02@gmail.com";
		$userName   = $user->fullName;
		$clientName = $client->fullName;
		$email      = "luishon02@gmail.com";
		$subject    = "Appelo - Solicitud de servicio - "."$clientName";
		$matter     = "Estimado(a)"." ". "$userName" . ",\n\n" . 
				      "Su estilista ha aprobado su solicitud. Los datos del mismo son los siguientes:\n\n".
				      "Nombre:"." "."$clientName"."\n\n".
		              "Email:"." "."$email"."\n\n";

		if($to && $subject && $matter && $email) {
			mail("$to", "$subject","$matter","From: $email");
			
			$response[] = array
			(
				'message' => 'success',
				'to'      => $to,
				'subject' => $subject,
				'matter'  => $matter,
				'email'  => $email,
			);

			return $response;

		} else {
			$response[] = array
			(
				'message' => 'empty'
			);

			return $response;
		}
	}


	// Client

	function saveClient($client) {
		$now   = date('Y-m-d H:i:s');
		$query = "";
		$query = sprintf("INSERT INTO Clients (
			cityId,
			email,
			password,
			fullName,
			dni,
			registration,
			isActive,
			averageRating,
			latitude,
			longitude
		) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
		$client->cityId,
		mysql_real_escape_string($client->email),
		mysql_real_escape_string($client->password),
		mysql_real_escape_string($client->fullName),
		mysql_real_escape_string($client->dni),
		$now,
		$client->isActive,
		$client->averageRating,
		$client->latitude,
		$client->longitude
		);
		mysql_query($query) or die (mysql_error());
		return mysql_insert_id();
	}

	function updateClient($client) {
		$query = "";
		$query = sprintf("UPDATE Clients 
			SET
			cityId = '%s',
			email = '%s',
			password = '%s',
			fullName = '%s',
			dni = '%s',
			registration = '%s',
			isActive = '%s',
			averageRating = '%s',
			latitude = '%s',
			longitude = '%s'
			WHERE clientId = '%s'",
		$client->cityId,
		mysql_real_escape_string($client->email),
		mysql_real_escape_string($client->password),
		mysql_real_escape_string($client->fullName),
		mysql_real_escape_string($client->dni),
		$client->registration,
		$client->isActive,
		$client->averageRating,
		$client->latitude,
		$client->longitude,
		$client->clientId
		);

		mysql_query($query) or die (mysql_error());
		return mysql_insert_id();
	}

	function getClientByClientId($clientId) {
		$query = sprintf("SELECT
			clientId,
			cityId,
			email,
			password,
			fullName,
			dni,
			registration,
			isActive,
			averageRating,
			latitude,
			longitude
			FROM Clients WHERE clientId = '%s'",
			mysql_real_escape_string($clientId)
		);
		$result = mysql_query($query) or die (mysql_error());
		$row  = mysql_fetch_row($result);
		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			$client->clientId     	= $row[0];
			$client->cityId       	= $row[1];
			$client->email        	= $row[2];
			$client->password     	= $row[3];
			$client->fullName     	= $row[4];
			$client->dni          	= $row[5];
			$client->registration 	= $row[6];
			$client->isActive 		= $row[7];
			$client->averageRating 	= $row[8];
			$client->latitude 		= $row[9];
			$client->longitude 		= $row[10];

			mysql_freeresult($result);
			return $client;
		}
	}

	function clientIsActive($client) {
		$query = sprintf("SELECT isActive FROM Clients WHERE email = '%s'", mysql_real_escape_string($client->email));

		$result = mysql_query($query) or die (mysql_error());

		$row = mysql_fetch_row($result);

		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			mysql_freeresult($result);
			return $data;
		}
	}

	function getClientIdByEmail($email) {
		$query = sprintf("SELECT clientId FROM Clients WHERE email = '%s'",
			mysql_real_escape_string($email));

		$result = mysql_query($query) or die (mysql_error());

		$row = mysql_fetch_row($result);

		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			mysql_freeresult($result);
			return $data;
		}
	}

	function getClientIdByDni($dni) {
		$query = sprintf("SELECT clientId FROM Clients WHERE dni = '%s'",
			$dni);

		$result = mysql_query($query) or die (mysql_error());

		$row = mysql_fetch_row($result);

		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			mysql_freeresult($result);
			return $data;
		}
	}

	function clientMatches($client) {
		$query = sprintf("SELECT clientId FROM Clients WHERE email = '%s' AND password = '%s'",
			mysql_real_escape_string($client->email),
			mysql_real_escape_string($client->password));

		$result = mysql_query($query) or die (mysql_error());

		$row = mysql_fetch_row($result);

		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			mysql_freeresult($result);
			return $data;
		}
	}

	function getClientByEmail( $email ) {
		$query = sprintf("SELECT
			clientId,
			cityId,
			email,
			password,
			fullName,
			dni,
			registration,
			isActive,
			averageRating,
			latitude,
			longitude
			FROM Clients WHERE email = '%s'",
			mysql_real_escape_string($email)
		);
		$result = mysql_query($query) or die (mysql_error());
		$row  = mysql_fetch_row($result);
		$data = 0;
		$data = $row[0];

		if ($data == null) {
			mysql_freeresult($result);
			return null;
		} else {
			$client->clientId     	= $row[0];
			$client->cityId       	= $row[1];
			$client->email        	= $row[2];
			$client->password     	= $row[3];
			$client->fullName     	= $row[4];
			$client->dni          	= $row[5];
			$client->registration 	= $row[6];
			$client->isActive 		= $row[7];
			$client->averageRating 	= $row[8];
			$client->isActive 		= $row[7];
			$client->averageRating 	= $row[8];
			$client->latitude 		= $row[9];
			$client->longitude 		= $row[10];

			mysql_freeresult($result);
			return $client;
		}
	}

	function getGeodistanceFormula( $lat2, $lon2 ) {
		
	/*
		var R = 6371; // Radius of the earth in km
		var dLat = deg2rad(lat2-lat1);  // deg2rad below
		var dLon = deg2rad(lon2-lon1); 
		var a = 
		  Math.sin(dLat/2) * Math.sin(dLat/2) +
		  Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * 
		  Math.sin(dLon/2) * Math.sin(dLon/2)
		  ; 
		var b = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
		var c = R * b; // Distance in km
		return c;
	*/

		$R = "6371";
		$dLat = sprintf("RADIANS( %s - latitude )", $lat2);
		$dLon = sprintf("RADIANS( %s - longitude )", $lon2);
		$a = sprintf("SIN( %s / 2 ) * SIN( %s / 2 ) + COS( RADIANS( latitude ) ) * COS( RADIANS( %s ) ) * SIN( %s / 2 ) * SIN( %s / 2 )", $dLat, $dLat, $lat2, $dLon, $dLon);
		$b = sprintf("2 * ATAN2( SQRT( %s ), SQRT( 1 - %s ) )", $a, $a );
		$c = sprintf("%s * %s", $R, $b);

		return $c;
	}

	function getNearActiveClientsByUserAndJobs( $user, $jobs ) {

		$geodistanceFormula = $this->getGeodistanceFormula( $user->latitude, $user->longitude );
		
		$jobsWhereCondition = "";
		for ($i=0; $i < count($jobs); $i++) { 
			$jobsWhereCondition .= sprintf("'%s'", $jobs[$i]);

			if ($i < count($jobs) - 1) {
				$jobsWhereCondition .= ", ";
			}
		}

		$jobsHavingCondition = sprintf(count($jobs));

		$query = sprintf("SELECT DISTINCT 
			C.clientId, 
			C.cityId, 
			C.email, 
			C.password, 
			C.fullName, 
			C.dni, 
			C.registration, 
			C.isActive, 
			C.averageRating, 
			C.latitude, 
			C.longitude, 
			%s AS distance
			FROM Clients AS C 
				INNER JOIN JobsByClient AS JC ON (C.clientId = JC.clientId) 
				INNER JOIN Jobs AS J ON (JC.jobId = J.jobId)
			WHERE isActive = 1 AND
			      J.jobId IN (%s)
			GROUP BY C.clientId
			HAVING COUNT(*) = %s
			ORDER BY distance ASC
			LIMIT 3",
			$geodistanceFormula,
			$jobsWhereCondition,
			$jobsHavingCondition

		);

		$result = mysql_query($query) or die (mysql_error());

		$data = array();
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$data[] = $row;
		}
		mysql_freeresult($result);

		return $data;
	}

	function sendMailToClient($user, $client) {

		$to         = $client->email .  "," . "luishon02@gmail.com";
		$userName   = $user->fullName;
		$clientName = $client->fullName;
		$jobs       = $this->getJobsStringFromDataArray($client->jobs);
		$email      = "luishon02@gmail.com";
		$subject    = "Appelo - Solicitud de servicio - "."$userName";
		$matter     = "Estimado(a)"." ". "$clientName" . ",\n\n" . 
				      "Un usuario ha solicitado sus servicios como estilista. Los datos del mismo son los siguientes:\n\n".
				      "Nombre:"." "."$userName"."\n\n".
		              "Email:"." "."$email"."\n\n".
		              "Tareas solicitadas:"." "."$jobs"."\n\n";

		if($to && $subject && $matter && $email) {
			mail("$to", "$subject","$matter","From: $email");

			$response[] = array
			(
				'message' => 'success',
				'to'      => $to,
				'subject' => $subject,
				'matter'  => $matter,
				'email'  => $email,
			);

			return $response;

		} else {
			$response[] = array
			(
				'message' => 'empty'
			);

			return $response;
		}
	}

	// Jobs

	function getJobs() {
		$query = sprintf("SELECT * FROM Jobs");

		$result = mysql_query($query) or die (mysql_error());

		$data = array();
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$data[] = $row;
		}
		mysql_freeresult($result);

		return $data;
	}

	function getJobsByIds($jobIds) {

		$jobsWhereCondition = "";
		for ($i=0; $i < count($jobIds); $i++) { 
			$jobsWhereCondition .= sprintf("'%s'", $jobIds[$i]);

			if ($i < count($jobIds) - 1) {
				$jobsWhereCondition .= ", ";
			}
		}

		$query = sprintf("SELECT * FROM Jobs WHERE jobId IN (%s)", $jobsWhereCondition);

		$result = mysql_query($query) or die (mysql_error());

		$data = array();
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$data[] = $row;
		}
		mysql_freeresult($result);

		return $data;
	}

	function getJobsByClientId($client) {
		$query = sprintf("SELECT * FROM JobsByClient WHERE clientId = '%s'", $client["clientId"]);

		$result = mysql_query($query) or die (mysql_error());

		$data = array();
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$data[] = $row;
		}
		mysql_freeresult($result);

		return $data;
	}

	function getJobsStringFromDataArray($jobsDataArray) {
		$jobsString = "";

		for ($i=0; $i < count($jobsDataArray); $i++) { 
			$jobsString .= sprintf("'%s'", $jobsDataArray[$i]["name"]);

			if ($i < count($jobsDataArray) - 1) {
				$jobsString .= ", ";
			}
		}

		return $jobsString;

	}

	// Cities
	function getCities() {
		$query = sprintf("SELECT * FROM Cities");

		$result = mysql_query($query) or die (mysql_error());

		$data = array();
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$data[] = $row;
		}
		mysql_freeresult($result);

		return $data;
	}

	// JobsByClient
	function saveJobsByClient($jobs,$clientId) {
		foreach ($jobs as $key => $value) {
			$query = "";
			$query = sprintf("INSERT INTO JobsByClient(clientId, jobId) VALUES ('%s','%s')",
			$clientId,
			$value
			);
			mysql_query($query) or die (mysql_error());
		}
	}

	// Appointment

	function saveAppointment($appointment) {
		$now   = date('Y-m-d H:i:s');
		$query = "";
		$query = sprintf("INSERT INTO Appointments (userId, clientId, datetime, registration, rating) VALUES ('%s','%s','%s','%s','%s')",
		$appointment->userId,
		$appointment->clientId,
		$now,
		$now,
		0
		);
		mysql_query($query) or die (mysql_error());
	}

	function rateAppointment($appointment) {

		$rateAppointmentQuery = sprintf("UPDATE Appointments 
			SET 
			rating = '%s'
			WHERE appointmentId = '%s'",
			$appointment->rating,
			$appointment->appointmentId
			);

		mysql_query($rateAppointmentQuery) or die (mysql_error());

		$ratingDataQuery = sprintf("SELECT 
			COUNT(appointmentId) AS appointmentCount, 
			SUM(rating) AS appointmentsRatingSum 
			FROM Appointments 
			WHERE clientId = '%s'", 
			$appointment->clientId);

		$result = mysql_query($ratingDataQuery) or die (mysql_error());

		$data = array();
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$data[] = $row;
		}
		mysql_freeresult($result);

		$client = $this->getClientByClientId($appointment->clientId);

		$appointmentCount = $data["appointmentCount"];
		$appointmentsRatingSum = $data["appointmentsRatingSum"];
		$averageRating = 0;

		if ($appointmentCount > 0 ) {
			$averageRating = $appointmentsRatingSum / $appointmentCount;
			$averageRating = round($averageRating);
		}

		$client->averageRating = $averageRating;

		$this->updateClient($client);
		
	}
}
?>