<?
//Support Ticket Tracker
//By Snake

//Setup the DB Connections
$server = 'localhost';
$user = 'root';
$pass = 'Snake6.*';
$db = 'tickets';

$link = mysql_connect($server, $user, $pass) or die ('Could Not Connect: ' . mysql_error());
mysql_select_db($db); //Selects the DB

/*
CREATE TABLE tickets  (
id INT,
PRIMARY KEY(id),
startDate BLOB,
lastName BLOB,
firstName BLOB,
phone BLOB,
userComments BLOB,
brand BLOB,
model BLOB,
sn BLOB,
user BLOB,
pass BLOB,
serivces BLOB,
techComments BLOB,
endDate BLOB
);
*/

//Functions

function createTicket() {
	$query1 = 'SELECT * FROM tickets';
	$numberget = mysql_query($query1); // Gets all fields in the table
	$number = mysql_num_rows($numberget) +1; //Count the rows and add 1 to get the new one
	$datestamp = date('Ymd H:m:s'); // Get the date and time
	$techComments = $datestamp . $_POST['tech'] . ':<br />' . $_POST['techComments'] . '<br /><br />';
	$query = 'INSERT INTO tickets (id, startDate, lastName, firstName, phone, userComments, brand, model, sn, user, pass, services, techComments, endDate) VALUES ("'.$number.'",
"'.$datestamp.'",
"'.$_POST['lastName'].'",
"'.$_POST['firstName'].'",
"'.$_POST['phone'].'",
"'.$_POST['userComments'].'",
"'.$_POST['brand'].'",
"'.$_POST['model'].'",
"'.$_POST['sn'].'",
"'.$_POST['user'].'",
"'.$_POST['pass'].'",
"'.$_POST['services'].'",
"'.$techComments.'",
"OPEN")'; //Tells the server what to post
	mysql_query($query) or die ('createTicket() failed! ' . mysql_error() . ''); //posts the data
}
function editTicket() {
	$query = 'SELECT * FROM tickets';
	$numberget = mysql_query($query); // Gets all fields in the table
	$datestamp = date('Ymd H:m:s'); // Get the date and time
	$query1 = 'UPDATE tickets SET services WHERE id = '.$ticketnum.' CONCAT(services) VALUES ("'.$_POST['services'].'")';
	$query2 = 'UPDATE tickets SET techComments WHERE id = '.$ticketnum.' CONCAT(techComments) VALUES ("'.$techComments.'")';
	if(isset($_POST['close'])){mysql_query('UPDATE tickets SET endDate = CONCAT(endDate, '.$datestamp.')');}
	mysql_query($query,$query1) or die ('editTicket() failed!'); //posts the data
}

function findTicket() {
	$query = 'SELECT * FROM tickets WHERE '.$_POST['column'].' LIKE %'.$_POST['parameters'].'% ORDER BY id DESC';
	$result = mysql_query($query) or die('search failure');
		if (mysql_num_rows($result) == 0) { echo ('No Results<br />'); }
		else {
			$number = mysql_num_rows($result);
			$i = 0;
			mysql_data_seek($result,$i);
			$tickettext = mysql_fetch_array($result, MYSQL_ASSOC);
			echo ('<table><tr><td>Ticket Number:</td><td>Start Date:</td><td>Last Name</td><td>First Name</td><td>Status:</td></tr>');
			echo ('<tr><td><a href="index.php?action=viewticket&ticketnum='.$tickettext['id'].'">'.$tickettext['id'].'</a><td>'.$tickettext['startDate'].'</td><td>'.$tickettext['lastName'].'</td><td>'.$tickettext['firstName'].'</td><td>'.$tickettext['endDate'].'</td></tr>');
			$i++; }
			while ($i < $number);
			echo '</table>';
	}
function viewTicket() {
	$query = 'SELECT * FROM tickets WHERE id = '.$ticketnum.'';
	$result = mysql_query($query) or die ('Invalid Query');
		if (mysql_num_rows($result) == 0) { echo ('No Tickets<br />'); }
		else {
			$tickettext = mysql_fetch_array($result, MYSQL_ASSOC);
			echo ('
			<table>
			<tr><td>Customer Information</td></tr>
			<tr><td>Ticket Number: '.$tickettext['id'].'</td><td>Last Name:</td><td>'.$tickettext['lastName'].'</td><td>First Name:</td><td>'.$tickettext['firstName'].'</td><td>Phone:</td><td>'.$tickettext['phone'].'</td></tr>
		<tr><td>User Comments:</td</tr>
		<tr><td>'.$tickettext['userComments'].'</td></tr>
		<tr><td>Computer Information</td</tr>
		<tr><td>Brand:</td><td>'.$tickettext['brand'].'</td><td>Model:</td><td>'.$tickettext['model'].'</td><td>sn:</td><td>'.$tickettext['sn'].'</td><td>User:</td>'.$tickettext['user'].'</td><td>Pass:</td><td>'.$tickettext['pass'].'</td></tr>
		<tr><td>Services:</td><td>Tech Comments:</td></tr>
		<tr><td>
		'.$tickettext['services'].'
		</td><td>'.$tickettext['techComments'].'</td></tr>
		</table>');
		}
}
//HTML Stuff
$action = $_GET['action'];
$ticketnum = $_GET['ticketnum'];
$header = '<a href="index.php?action=create">Create a Ticket</a><a href="index.php?action=search>Search for a Ticket</a>';
$footer = '::PHP Support Ticket Script::By Snake::';

//Create a new ticket
if ($action == "create") {
	if (isset ($_POST['services'])) {
		createTicket();
		echo 'Your Ticket has been Created as <a href="index.php?action=viewticket&ticketnum='.$number.'">'.$number.'</a>.';
	}
	else {
		echo ('
		<form method="post" action="index.php?action=create">
		<table>
		<tr><td>Customer Information</td></tr>
		<tr><td>Last Name:</td><td><input type="text" name="lastName" /></td><td>First Name:</td><td><input type="text" name="firstName" /></td><td>Phone:</td><td><input type="text" name="phone" /></td></tr>
		<tr><td>User Comments:</td</tr>
		<tr><td><input type="textarea" name="userComments" width="800" height="300" /></td></tr>
		<tr><td>Computer Information</td</tr>
		<tr><td>Brand:</td><td><input type="text" name="brand" /></td><td>Model:</td><td><input type="text" name="model" /></td><td>Serial:</td><td><input type="text" name="sn" /></td><td>User:</td><td><input type="text" name="user" /></td><td>Pass:</td><td><input type="text" name="pass" /></td></tr>
		<tr><td>Services:</td><td>Initial Comments:</td></tr>
		<tr><td>
		<select name="services" multiple="multiple">
		<option value="Virus Removal">Virus Removal
		<option value="OS Reinstall">OS Reinstall
		<option value="Diagnostic">Diagnostic
		</select>
		</td><td><input type="textarea" name="techComments" /></td></tr>
		<tr><td>
		<select name="tech">
		<option value="Ollie">Ollie
		<option value="Kyle">Kyle
		<option value="Trevor">Trevor
		</select>
		<input type="submit"/></td></tr>
		</form></table>');
	}
}
//Edit a ticket
if ($action == "edit") {
	if (isset($_POST['submit'])) {
		editTicket();
		}
	else {
		echo $header;
		echo ('
		<table>
		<form method="post" action="index.php?action=edit&ticketnum'.$ticketnum.'">
		<tr><td>
		<select name="services" multiple="multiple">
		<option value="Virus Removal">Virus Removal
		<option value="OS Reinstall">OS Reinstall
		<option value="Diagnostic">Diagnostic
		</select>
		</td><td><input type="textarea" name="techComments" /></td></tr>
		</table>
		<select name="tech">
		<option value="Ollie">Ollie
		<option value="Kyle">Kyle
		<option value="Trevor">Trevor
		</select>
		<input type="submit" name="edit" />
		</form>
		</table>');
		echo $footer;
	}
}
//Search for a ticket
if ($action == search) {
	if (isset($_POST['submit'])) {
		findTicket();
		}
	else {
		echo $header;
		echo ('
		<table><form><tr><td>Search By:<select name="column"><option name="id">Ticket Number</option><option name="lastName">Last Name</option></select></td><td>Parameters:<input type="text" name=parameters/></td><td><input type=submit name=Search /></td></tr></form></table>');
		echo $footer;
	}
}
//Display a ticket
if ($action == viewticket) {
	echo $header;
	viewTicket();
	echo ('<form action="index.php?action=edit&ticketnum='.$ticketnum.'><input type="submit" name="edit"/></form>');
	echo footer;
}
?>