<?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_pers.php');
	include('/var/www/html/uclit/protected/connections/db_comments.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("comments", $commentsConn) or die("Couldn't select database");

	// Query Type
	$sql_type = "Select comments.comments.resolution, comments.comments.id, comments.comments.lib, comments.comments.visit_date, comments.comments.comment_date, comments.comments.patron_type, comments.comments.rating, comments.comments.`comment` AS Comment, comments.comments.comment_type, comments.comments.`status` AS Status, comments.comments.assigned_personnel, comments.comments.email_check, comments.comments.name, comments.comments.email, uclit_db.departments.dept_id, uclit_db.departments.dept_name AS DeptName
From comments.comments Inner Join uclit_db.departments ON uclit_db.departments.dept_id = comments.comments.lib
Order By uclit_db.departments.dept_name Asc";
	$result_type = mysql_query($sql_type, $commentsConn) or die ("Unable to query: $sql_type");

	// Query Status
	$sql_status = "SELECT * FROM comment_type";
	$result_status = mysql_query($sql_status, $commentsConn) or die ("Unable to query: $sql_status");
	
	//Query Type
	$sql_type = "SELECT * FROM followup_action";
	$result_type = mysql_query($sql_type, $commentsConn) or die ("Unable to query: $sql_type");
	
	
	// Query Count of Unresolved Requests
	$sql_count = "select count(id) as count from comments where (status != '1' and status !='5' and status !='6');";
	$result_count = mysql_query($sql_count, $commentsConn) or die ("Unable to query: $sql_count");
?>
	<H1>Comment Card 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 
	$dbConn = mysql_select_db("uclit_db", $uclit_dbConn) or die("Couldn't select database");	
	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 
	$dbConn2 = mysql_select_db("comments", $commentsConn) or die("Couldn't select database");
	while($row = @mysql_fetch_array($result_status))
	{
	  $type_id = $row['id'];
	  $type_name = $row['description'];

	  echo"<option value=$type_id>$type_name</option>";
	}
?>
	 	  </select>
		</td> </tr><tr><td>Status</td><td>Year</td></tr><tr>
				<td>
		  <select name="status">
		  <option selected value=0> Any </option>
<?php 
	$dbConn3 = mysql_select_db("comments", $commentsConn) or die("Couldn't select database");
	while($row = @mysql_fetch_array($result_type))
	{
	  $status_id = $row['id'];
	  $status_name = $row['desc'];

	  echo"<option value=$status_id>$status_name</option>";
	}
?>
	 	  </select>
		</td> 
			<td><select name="year">
		  <option selected value=0> Any </option>
		  <option value="2008">2008</option>	
		  <option value="2007">2007</option>
		  <option value="2006">2006</option>	
		  <option value="2005">2005</option>
		  <option value="2004">2004</option>			
		  <option value="2003">2003</option>	
		</td>
</tr><tr>
		<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 Comments NOT Resolved or a Response has NOT been sent.";
?>
	</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 uclit_db.departments.dept_id, uclit_db.departments.dept_name, comments.comments.resolution, comments.comments.id, Count(comments.comments.lib) AS `Count`, comments.comments.visit_date, comments.comments.comment_date, comments.comments.patron_type, comments.comments.rating, comments.comments.`comment`, comments.comments.comment_type, comments.comments.`status`, comments.comments.assigned_personnel, comments.comments.email_check, comments.comments.name, comments.comments.email
From comments.comments Inner Join uclit_db.departments ON uclit_db.departments.dept_id = comments.comments.lib
Where ((comments.comments.`status`!= '1') and (comments.comments.`status`!= '5') and (comments.comments.`status`!= '6'))
Group By comments.comments.lib";
	$result_deptcount = mysql_query($sql_deptcount, $commentsConn) or die ("Unable to query: $sql_deptcount");
	
	while($row_counts = mysql_fetch_array($result_deptcount))
	{
	  $dept_name = $row_counts['dept_name'];
	  $dept_id = $row_counts['dept_id'];
	  $dept_count = $row_counts['Count'];
	 
	  //echo "$dept_name - $dept_count<br>";
echo"	  
<tr>
	<td><a href='list2.php?dept=$dept_id'>$dept_name</a></td>
	<td>$dept_count</td>
</tr>";
	}
?>
	</table>
	</form>
<?php include('/var/www/html/uclit/protected/common/css_login/css_foot2.php'); ?>