<?php 
	include('/var/www/html/uclit/protected/common/login/check.php'); 
 	include('/var/www/html/uclit/protected/common/css_login/css_head2.php'); 
	include('/var/www/html/uclit/protected/connections/db_help.php');	

	// Establish Database Connection
	$dbConn = mysql_select_db("uclit_db", $uclit_dbConn) or die("Couldn't select database");	

	// Query Department 
	$sql_dept = "SELECT dept_id, dept_name FROM departments ORDER by dept_name";
	$result_dept = mysql_query($sql_dept, $uclit_dbConn) or die ("Unable to query: $sql_dept");

	$dbConn2 = mysql_select_db("helpcalls", $helpConn) or die("Couldn't select database");

	// Query Type
	$sql_type = "SELECT * FROM log order by Help_dept";
	$result_type = mysql_query($sql_type, $helpConn) or die ("Unable to query: $sql_type");

	// Query Status
	$sql_status = "SELECT help_type_id, help_type FROM help_catagories";
	$result_status = mysql_query($sql_status, $helpConn) or die ("Unable to query: $sql_type");

	// Query Count of Unresolved Requests
	$sql_count = "select count(completed) as count from log where completed = '0';";
	$result_count = mysql_query($sql_count, $helpConn) or die ("Unable to query: $sql_count");
?>
	<H1>Help Call Control Center</H1>
	<form action="list.php" method="get">
	<table cellspacing="2" cellpadding="2" border="0">
	  <tr>
		<td>Dept</td>
		<td>Type</td>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
	  </tr>
	  <tr>
		<td><select name="dept">
		  <option selected value=0> Any </option>
<?php 

	while($row = mysql_fetch_array($result_dept))
	{
	  $dept_id = $row['dept_id'];
	  $dept_name = $row['dept_name'];

	  echo"<option value=$dept_id>$dept_name</option>";
	}
?>
		</select>
		</td>	
		<td>
		  <select name="type">
		  <option selected value=0> Any </option>
<?php 
	while($row = @mysql_fetch_array($result_status))
	{
	  $type_id = $row['help_type_id'];
	  $type_name = $row['help_type'];

	  echo"<option value=$type_id>$type_name</option>";
	}
?>
	 	  </select>
		</td> 
		<td>
		  <input type="submit" value="View">
		</td>	 
	  </tr>
	</table>

	<p>
	<strong>
<?php 
	$row_count = @mysql_fetch_array($result_count);
	$count = $row_count['count'];
	echo" $count </strong>$unans Help Calls NOT Completed";
?>
	</p>

	<p>
	<!--  Department Counts -->
	<table cellspacing="2" cellpadding="2" border="0">
	  <tr>
		<td><strong>Department</strong></td>
		<td><strong>Count</strong></td>
	  </tr>

<?php

	// Query Department Counts
	$sql_deptcount = "
	SELECT Help_Dept, count(Help_Dept) as count 
	FROM log 
	WHERE completed = 0 
	GROUP BY Help_Dept";
	$result_deptcount = mysql_query($sql_deptcount, $helpConn) or die ("Unable to query: $sql_deptcount");
	
	while($row_counts = mysql_fetch_array($result_deptcount))
	{
	  $dept_name = $row_counts['Help_Dept'];
	  $dept_count = $row_counts['count'];
	 
	  //echo "$dept_name - $dept_count<br>";
echo"	  
<tr>
	<td><a href='list2.php?dept=$dept_name'>$dept_name</a></td>
	<td>$dept_count</td>
</tr>";
	}
?>
	</table>
	</form>
<?php include('/var/www/html/uclit/protected/common/css_login/css_foot2.php'); ?>