<?php

// Global Variables
$host="localhost";
$port="";
$db_name="dvd_store";
$options="";
$tty="";
$db_user="dvd_user";
$db_password="";

$err_mesg;
$db_conn;


function dvd_open_db() {
	global $db_conn, $err_mesg;
	global $host, $db_name, $db_user;

	// Open a persistent database connection
	if (($db_conn = pg_pConnect("host=$host dbname=$db_name user=$db_user password=$db_password ")) == false) {
		$err_mesg = "Could not connect to the database";
	}
	return $db_conn;
}

function dvd_err_text() {
	global $err_mesg;

	return $err_mesg;
}

function dvd_close_db() {
	return;
}

function dvd_member_get($member_id, &$member_object) {
	global $db_conn, $err_mesg;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}

	// Execute the select query
	if (( $result_id = pg_exec($db_conn, "select * from member
				where member_id=$member_id")) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		$member_object = pg_Fetch_Object($result_id, 0);
		return true;
	}
}

function dvd_member_get_id_from_number($member_no, &$member_id) {
	global $db_conn, $err_mesg;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}

	// Execute the select query to get the member_id
	if (( $result_id = pg_exec($db_conn, "select member_id from member
				where member_id=$member_no")) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		$store_member_object = pg_Fetch_Object($result_id, 0);
		$member_id= $store_member_object->member_id;
		return true;
	}
}

function dvd_member_search($lname, &$ids) {
	global $db_conn, $err_mesg;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return false;	
		}
	}
		
		if (( $result_id = pg_exec($db_conn, "select * from member
				where lname= '$lname'")) == false) {
			$err_mesg=pg_errormessage($db_conn);
			return false;
		} else {
			$row=0;
			for($row=0; $row<pg_numrows($result_id); $row++) {
				$store_member_object = pg_Fetch_Object($result_id, 0); 
				$ids[$row] = $store_member_object->member_id;
			}
			
			return true;
		}
}

function dvd_title_get($title_id, &$title_object) {
	global $db_conn, $err_mesg;

	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return false;	
		}
	}

	if (( $result_id = pg_exec($db_conn, "select title_id, title_text, asin, director,  classification, actor1, actor2, release_date, rental_cost, genre_name
				 from title, genre
				where title_id=$title_id and title.genre_id = genre.genre_id")) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		$title_object = pg_Fetch_Object($result_id, PGSQL_ASSOC);
		return true;
	}
}

function dvd_get_genre_list(&$genre_list) {
	global $db_conn, $err_mesg;

	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return false;	
		}
	}

	if (( $result_id = pg_exec($db_conn, "select genre_name from genre")) 
				== false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		for($row=0; $row < pg_numrows($result_id); $row++) {
			$genre_object = pg_Fetch_Object($result_id, $row);
			$genre_list[] = $genre_object->genre_name;
		}
		return true;
	}

}

function dvd_get_classification_list(&$classification_list) {
	global $db_conn, $err_mesg;
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return false;	
		}
	}

	if (( $result_id = pg_exec($db_conn, "select * from filmclass")) 
				== false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		for($row=0; $row<pg_numrows($result_id); $row++) {
			$classification_object = pg_Fetch_Object($result_id, $row);
			$classification_list[$row] = 
				$classification_object->class_name;
		}
		return true;
	}

}


function dvd_title_search($title, $name, &$matches) {
	global $db_conn, $err_mesg;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return false;	
		}
	}

	$title = strtolower($title);
	$name = strtolower($name);


	// Generate the sql query
	if ((strlen(trim($title)) == 0) && (strlen(trim($name)) == 0))  {
		return false;
	} else if ((strlen(trim($title)) == 0) || (strlen(trim($name)) == 0)) {
		if (strlen(trim($title)) == 0) {
			$sql_query = "select * from title where lower(director) LIKE '%$name%' or lower(actor1) like '%$name%' or lower(actor2) LIKE '%$name%'";	
		} else {
			$sql_query = "select * from title where lower(title_text) LIKE '%$title%'";
		}
	} else {
			$sql_query = "select * from title where lower(title_text) LIKE '%$title%' or lower(director) LIKE '%$name%' or lower(actor1) like '%$name%' or lower(actor2) LIKE '%$name%'";	

	}


	if (( $result_id = pg_exec($db_conn,$sql_query)) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		for($row=0; $row<pg_numrows($result_id); $row++) {
			$title_object = pg_Fetch_Object($result_id, $row); 
			$matches[$row] = $title_object->title_id;
		}
		return true;
	}
}

// Check if the title is available
function dvd_title_available($title_id, $date, &$disk) {
	global $db_conn, $err_mesg;

	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}


	// Get the list of all the disks from the table disk 
	if (( $result_id = pg_exec($db_conn, "select * from disk
				where title_id = $title_id ")) == false){
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		$disk=0;
		for($row=0; $row<pg_numrows($result_id); $row++) {
			$disk_object = pg_Fetch_Object($result_id, $row);
			if ($disk_object->member_id == 0) {
				$disk = $disk_object->disk_id;
			}
		}
		return true;
	}
}

function dvd_reserve_title($date, $title_id, $disk_id, $member_id) {
	global $db_conn, $err_mesg;

	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}

	if (( $result_id = pg_exec($db_conn, "insert into member_booking(member_id, title_id, date_required) values
				($member_id, $title_id, '$date')")) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else if (( $result_id = pg_exec($db_conn, "update disk set member_id=$member_id where disk_id=$disk_id "))== false) {	
		 $err_mesg=pg_errormessage($db_conn);
		return false;
	} else{
		return true;	
	}
}

function dvd_reserve_title_cancel($member_id) {
	global $db_conn, $err_mesg;

	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}
	// This function assumes that there will be only one
	// reservation for the member in the member_booking table
	if (( $result_id = pg_exec($db_conn, "delete  from member_booking
				where member_id= $member_id ")) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else if (( $result_id = pg_exec($db_conn, "update disk set member_id=0
				where member_id= $member_id ")) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;

	}else {
		return true;
	}

}

function dvd_reserve_title_query_by_member($member_id, &$tmpTitle) {
	global $db_conn, $err_mesg;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return false;	
		}
	}

	// This function assumes that there will be only one
	// reservation for the member in member_booking table
	if (( $result_id = pg_exec($db_conn, "select * from member_booking
				where member_id= $member_id ")) == false) {
		$err_mesg=pg_errormessage($db_conn);
		return false;
	} else {
		if (pg_NumRows($result_id) > 0) {
			$member_booking_object = pg_Fetch_Object($result_id, 0);
			$tmpTitle = $member_booking_object->title_id;
		}
		else {
			$tmpTitle= 0;
		}
		return true;
	}
}

function dvd_begin_transaction() {
	global $db_conn;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}

	return pg_exec($db_conn, "begin");


}

function dvd_commit_transaction() {
	global $db_conn;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}
	return pg_exec($db_conn, "commit");

}



function dvd_rollback_transaction() {
	global $db_conn;

	// Get the database connection
	if (!$db_conn) {
		if (!($db_conn = dvd_open_db())) {
			return null;	
		}
	}
	return pg_exec($db_conn, "abort");

}


?>
