<?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');
	
	//get all catagory
	$queryCatagory = "SELECT distinct Help_Catagory from log ORDER BY Help_Catagory";
	$db = mysql_select_db('helpcalls', $helpConn)	or die("Couldn't select database helpcalls.");
	$resultCat = mysql_query($queryCatagory, $helpConn)	or die("Couldn't execute query here3.<br />$queryCatagory");

	$cal = array();
	$c = 0;
	while ($catagory = mysql_fetch_array($resultCat))
	{
		$cal[$c++] = $catagory['Help_Catagory']; //story catagory into the array
	}
	//query all record	
	$db = mysql_select_db('helpcalls', $helpConn)	or die("Couldn't select database helpcalls.");
	$query= "SELECT (TO_DAYS(Date_Completed) - TO_DAYS(Date_Arrived))As Temp FROM log WHERE (Completed = 1 and Date_Arrived != '' and Date_Completed != '') and (Date_Arrived <= Date_Completed) and (Help_Catagory != '') and (Date_Arrived Between '2007-01-01' AND '2007-09-14')";
	
	$result = mysql_query($query, $helpConn) or die("Couldn't execute query here2.<br />$query");

	$count = 0;
	$sum = 0;
	$avg = 0;
	while ($row = mysql_fetch_array($result))
	{
		$da = $row['Temp'];
		if($da != '' && $da <= 50)
		{
			$sum += $da;
			$count++;		
		}
	}
	$avg = round($sum/$count);
	//create table here with 4 cols
	echo "
	<a name='top'></a>
	&nbsp; &nbsp; &nbsp; &nbsp; 
	<h2>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
	  Welcome to the Help Call Report</h2>
	<table border='1'>
	<tr bgcolor='#C0C0C0'>
		<td><strong>Catagory</strong></td>
		<td><strong>Total Record</strong></td>
		<td><strong>Total Day</strong></td>
		<td><strong>Average Day/Help Call</strong></td>
	</tr>
	<tr>
		<td bgcolor='#C0C0C0'><strong>All Records</strong></td>
		<td>$count </td>
		<td>$sum </td>
		<td>$avg</td>
	</tr>
	";

	for($x =0;$x < $c;$x++)
	{
		//query specific catagory
		$db = mysql_select_db('helpcalls', $helpConn) or die("Couldn't select database helpcalls.");
		if($cal[$x] != "")
		{
			$query2= "SELECT (TO_DAYS(Date_Completed) - TO_DAYS(Date_Arrived))As Temp FROM log WHERE (Completed = 1 and Date_Arrived != '' and Date_Completed != '') and (Date_Arrived <= Date_Completed) and (Help_Catagory = '$cal[$x]') and (Date_Arrived Between '2007-01-01' AND '2007-09-14')";
			$result2 = mysql_query($query2, $helpConn) or die("Couldn't execute query here2.<br />$query2");
		
			$count = 0;
			$sum = 0;
			$avg = 0;
			while ($row = mysql_fetch_array($result2))
			{
				
				$da = $row['Temp'];
				if($da != '' && $da <= 50)
				{
					$sum += $da;
					$count++;
				}
			}
			if($count != 0)
				$avg = round($sum/$count);
			else
				$avg = 0;
			echo "
			<tr>
				<td bgcolor='#C0C0C0'><strong>$cal[$x]</strong></td>
				<td>$count </td>
				<td>$sum </td>
				<td>$avg</td>
			</tr>";		
		}
	}	
echo "
</table>
<a href='#top'>Top</a>";
	include('/var/www/html/uclit/protected/common/css_login/css_foot2.php'); 
?>