<?php

	// Author:  Andy Asberry
	// Date:	2008
	// Pre-conditions: 	A request is made by the GET method to this PHP program with two name/value parameters. The first is the 
	// 					empType, which signifies to query by employee id (empType=ID), employee ssn (empTyp=SSN), or employee 
	//					last name (empType=NAME). The second is the value of the id (empID=?????),ssn (empSSN=?????????), or 
	//					last name (?+ characters from valid set of characters). Example for querying by employee id where the 
	//					id to query for is 11111: ..getData.php?empType=ID&empID=11111
	// Post-conditions: A comma separated response is echoed back to the XMLHttpRequest object for formatting. The first parameter signifies the number of results found. -1 Indicates an error. Greater than 0 indicates success and how many. 0 indicates either none were found or some sort of informative message follows.
	
		$empType = $_GET['empType']; // store which radio button was selected into variable empType

		$mysql_link = @mysql_connect('REMOVED', 'REMOVED', 'REMOVED')
					or die ("MySQL not running.  Can't continue.\n");	// connect to the database

		mysql_select_db('REMOVED');	// select the database

		if ($empType == 'ID') // if type to query by is Employee ID
		{	
			$empID = $_GET['empID']; // store into variable from GET method
			if (strlen($empID) == 5)	// employee id's are of length 5
			{
				$violation = false; // variable to hold whether or not there has been a violation yet. initially, there has not been a violation
				for ($i = 0; $i < 5; $i++)	// loop through to check each individual character
				{
					$char = substr($empID,$i,1); // get the next character
					if ($char != '0' && $char != '1' && $char != '2' && $char != '3' && $char != '4' && $char != '5' && $char != '6' && $char != '7' && $char != '8' && $char != '9') // if the character is not a digit >= 0 and <= 9
					{
						$violation = true;	// there has been a violation
					}
				}
				
				if ($violation == true)	// if a violation has occurred
				{
					echo "-1,Employee ID has incorrect format.";	// return invalid format to XMLHttpRequest object
				}
				else	// else there has not been a violation
				{
					//echo "0,Employee ID format okay";			
					// so perform query on SQL database for employee information to display		
					$query = 	"SELECT eid, ssn, lname, fname, city, state, dname, pay
								FROM `employees`, `stores`, `departments`
								WHERE eid = '$empID' AND store_id = sid AND dept_id = did";
					// select the employee id, employee ssn, etc
					
					$result = mysql_query($query);	// store the query into variable result
					
					$num = mysql_num_rows($result);	// get the number of results found from the query
					
					if ($num == 1)	// since employee id is primary key, the number found will be 1 or none/FALSE
					{
						$r = mysql_fetch_array($result);	// fetch an associative array from the result (split)
						echo ("1," . $r[eid] . "," . $r[ssn] . "," . $r[lname] . "," . $r[fname] . "," . $r[city] . "," . $r[state] . "," . $r[dname] . "," . $r[pay]);	// echo the results back to the XMLHttpRequest object
					}
					else	// the employee id was not found
					{
						echo "-1,Employee ID not found.";	// echo back an error message
					}
					
				}
			}
			else if (strlen($empID) == 0)	// if the employee id is an empty string, send back informative message
			{
				echo "0,For results please enter a Query and mouse out of the text box."; // inform user to enter data
			}
			else	// else the length is not 0 or 5, so it has invalid format
			{
				echo "-1,Employee ID has incorrect format.";	// report error back to XMLHttpRequest object
			}
		}
		else if ($empType == 'SSN')	// if the type to query by is employee ssn
		{	
			$empSSN = $_GET['empSSN'];	// get the value of the employee ssn from the GET method	
			if (strlen($empSSN) == 9)	// check to make sure the length is that of social security numbers (9)
			{
				$violation = false;	// there has not yet been a violation
				for ($i = 0; $i < 9; $i++)	// loop through to check each of the characters
				{
					$char = substr($empSSN,$i,1);	// get the next character
					if ($char != '0' && $char != '1' && $char != '2' && $char != '3' && $char != '4' && $char != '5' && $char != '6' && $char != '7' && $char != '8' && $char != '9')	// if the character is not >= 0 and <= 9
					{
						$violation = true; // there has been a violation
					}
				}
				
				if ($violation == true)	// if there has been a violation after the validation process
				{
					echo "-1,Employee SSN has incorrect format."; // report error back to XMLHttpRequest Object
				}
				else	// else there has been no error
				{
					//echo "0,Employee SSN format is OK.";
					
					// query string for employee information by social security number
					$query = 	"SELECT eid, ssn, lname, fname, city, state, dname, pay
								FROM `employees`, `stores`, `departments`
								WHERE ssn = '$empSSN' AND store_id = sid AND dept_id = did";
					
					// store the result of the query
					$result = mysql_query($query);
					
					$num = mysql_num_rows($result);	// get the number of records found in the query. Should be 1 since social security numbers are unique for each entity
					
					if ($num == 1)	// if there was exactly 1 found
					{
						$r = mysql_fetch_array($result);	// get the results into an associative array
						echo ("1," . $r[eid] . "," . $r[ssn] . "," . $r[lname] . "," . $r[fname] . "," . $r[city] . "," . $r[state] . "," . $r[dname] . "," . $r[pay]);	// echo the results back to the XMLHttpRequest object
					}
					else	// else the employee ssn was not found
					{
						echo "-1,Employee SSN not found.";	// so report error back to XMLHttpRequest object
					}
					
				}
			}
			else if (strlen($empSSN) == 0) // if the empSSN is an empty string
			{
				echo "0,For results please enter a Query and mouse out of the text box."; // report back informative message to user
			}
			else	// else the length of the ssn is incorrect
			{
				echo "-1,Employee SSN has incorrect format.";	// report error message back to XMLHttpRequest object
			}
		}
		else if ($empType == 'NAME')	// if the type to query by is employee last name
		{	
			$empName = $_GET['empName'];	// get the last name of the employee by the GET method
			if (strlen($empName) > 0)		// if the length of the last name is not empty
			{
				$violation = false;		// there has not yet been a violation while validating the last name for valid characters
				$nameLength = strlen($empName);	// get the length of the last name entered
				$charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-'"; // this is a valid character set
				for ($i = 0; $i < $nameLength; $i++) // for each character in the last name
				{
					$char = substr($empName,$i,1); // get the character
					if (strchr($charset,$char) == false)	// check to make sure it is a valid character
					{
						$violation = true;	// if it is not, there has been a violation
					}
				}
				
				if ($violation == true)	// if there was a violation
				{
					echo "-1,Employee Last Name has incorrect format."; // report error back to XMLHttpRequest object
				}
				else	// if there was not a violation
				{
					//echo "0,Employee Last Name format is OK.";
					$values = '';	// create variable to hold string to be echoed back to XMLHttpRequest object
					
					// form query string to query by employee last name
					$query = "SELECT eid, ssn, lname, fname, city, state, dname, pay
								FROM `employees`, `stores`, `departments`
								WHERE lname = '$empName' AND store_id = sid AND dept_id = did";
					
					$result = mysql_query($query); // store the results of the query
					
					$num = mysql_num_rows($result); // get the number of records found from the query. Since some employees may have the same last name, there may be more than one record found
					
					if ($num > 0)	// if records were found from the query
					{
						$values = $num;	// we want to report the number of records found, so store it first in the query string. This will indicate how many rows will be needed for display in the results table
						while ($r = mysql_fetch_array($result))	// get each record
						{
							$values = $values . "," . $r[eid] . "," . $r[ssn] . "," . $r[lname] . "," . $r[fname] . "," . $r[city] . "," . $r[state] . "," . $r[dname] . "," . $r[pay]; // append each record separated by commas
						}
						echo($values);	// echo the records back to the XMLHttpRequest object
					}
					else	// else there were no matching records found from the query
					{
						echo "-1,Employee Last Name not found.";	// report error back to XMLHttpRequest object
					}
				}
			}
			else	// else the length of the employee name was not greater than 0
			{
				echo "0,For results please enter a Query and mouse out of the text box."; // inform user to enter data
			}
		}
		else
		{
			echo "-1,GET ERROR!";	// else there was a GET error
		}
	mysql_close($mysql_link);	// close the database connection
?>