<?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_arlstats.php');

	@ob_start();

	echo"<h1>Display Report</h1>";
	//process 1
	$personnel = $_POST['Personnel'];
	$location = $_POST['Location'];
	$material = $_POST['Material'];
	$catalog = $_POST['Catalog'];
	$month = $_POST['Month'];
	$stype = $_POST['stype'];
	$summary = $_POST['summary'];
	
	$arr1 = array("$personnel", "$location","$material","$catalog","$month","$stype");
	$arr2 = array("(UID = $personnel)", "LID = $location", "MID = $material", "CID = $catalog", "QID = $month", "SID = $stype");
	$arrexport = array("(arlstats.Current_Stats.UID = $personnel)", "arlstats.Current_Stats.LID = $location", "arlstats.Current_Stats.MID = $material", "arlstats.Current_Stats.CID = $catalog", "arlstats.Current_Stats.QID = $month", "arlstats.Current_Stats.SID = $stype");	
	$arr3 = array("blank", "Location", "Material_Types", "Catalog_Types", "Calendar", "Stat_Types");	
	$arr4 = array("Name/Dept: ", "Location: ", "Format: ", "Action: ", "Calendar: ", "Statistics Type:");	
	$arr5 = array("Name/Dept: Select All", "Location: Select All", "Format: Select All", "Action: Select All", "Month: Select All");	
	$atLeastOnce = "false";
	$atLeastOnce2 = "false";
	$total = 0;
	$where = "";
	$where3 = "";

/*------------------------------------------------------
Purpose: build a "WHERE" clause for the querry according the selected caterogies where
			one of the drop down box differs from "Select All" at least once

--------------------------------------------------------*/

	for($x=0;$x<6;$x++)
	{
		if($arr1[$x] != 0 && $atLeastOnce == "false")
		{
			$atLeastOnce = "true";
			$where .= " WHERE ";
		}
	}
	$count = 0;
	for($y=0;$y<6;$y++)
	{//add only selected options
		if($arr1[$y] != 0)
		{
			$count++;
			if($count == 1)
				$where .= $arr2[$y];
			if($count > 1)
			{
				$where .= " and ";
				$where .= $arr2[$y];
			}	
		}
	}
	
	// For Export Function
	for($r=0;$r<6;$r++)
	{
		if($arr1[$r] != 0 && $atLeastOnce2 == "false")
		{
			$atLeastOnce2 = "true";
			$where3 .= " WHERE ";
		}
	}
	$count3 = 0;
	for($s=0;$s<6;$s++)
	{//add only selected options
		if($arr1[$s] != 0)
		{
			$count3++;
			if($count3 == 1)
				$where3 .= $arrexport[$s];
			if($count3 > 1)
			{
				$where3 .= " and ";
				$where3 .= $arrexport[$s];
			}	
		}
	}

//Check to show full or partial report list.
if ($summary == "1")
{
/*---------------------------------------------
Purpose: because the personnel is from different table, a separate querry is made to get the 
		first, middle, and last name of that personnel
-----------------------------------------------*/
	//table holding what was selected and reported values
	echo"<table border='0' width='100%'>
		  <tr>
		   <td width='60%'>
		   <p>";
	
	$db = mysql_select_db('uclit_db', $uclit_dbConn) or die ("Unable to select uclit_db database.");

	if($personnel == 0)
	{//display a message of the user is "select all"
		echo "$arr5[0]<br>";
	}
	else
	{//display the name of the user
		$uid2 = $personnel;
		$uid2 = str_replace("UID","personnel_id",$uid2);
		$sql_personnel = "SELECT personnel_id, last_name, first_name, middle_name FROM personnel WHERE personnel_id = $uid2";
		$per_result = @mysql_query($sql_personnel, $uclit_dbConn) or die ("Unable to query: $sql_personnel");
		while($per_row = @mysql_fetch_array($per_result))
		{	
			$lname = $per_row['last_name'];
			$fname = $per_row['first_name'];
			$mname = $per_row['middle_name'];
			echo "$arr4[0] $lname, $fname $mname<br>";
			
		}
	
	}
/*-----------------------------------------------
Purpose: display back what had been selected from previous screen
------------------------------------------------*/

	$db = mysql_select_db('arlstats', $arlConn) or die ("Unable to select arlstats database.");
	
	for($z=0;$z<6;$z++)
	{
		if($z != 0)
		{
			if($arr1[$z] != 0)
			{	
				if($z == 4)
					$sql_select = "SELECT Month FROM $arr3[$z] WHERE $arr2[$z]";
				else	
					$sql_select = "SELECT Name FROM $arr3[$z] WHERE $arr2[$z]";
				$my_result = @mysql_query($sql_select, $arlConn) or die ("Unable to query: $sql_select");
				echo "$arr4[$z]";
				echo @mysql_result($my_result,0);
				echo"<br>";
			}
			else
			{//select all go here
				echo " $arr5[$z]<br>";
			}	
		}
	}	
//	echo "</p>";

	//Close first cell and open next
	echo"</p></td>
		 <td width='40%'><h3>";

/*-----------------------------------------------------
Purpose: tally up total records and sum of values of the selected item from previous screen
-------------------------------------------------------*/
	$count_add = "SELECT SUM(Value) FROM Current_Stats";
	$where2 = $where;
	$where2 .= " and CID != 7";
	$count_add .= $where2;
	$add_result = @mysql_query($count_add, $arlConn) or die ("Unable to query: $count_add");
	echo "<br />Items Added: ";
	$add_total = @mysql_result($add_result,0);
	echo $add_total;

	$count_all = "SELECT SUM(Value) FROM Current_Stats";
	$count_all .= $where;
	$all_result = @mysql_query($count_all, $arlConn) or die ("Unable to query: $count_all");
	$all_total =  @mysql_result($all_result,0);
	echo "<br />Items Withdrawn: ";
	$all_withdrawn = $all_total - $add_total;
	echo $all_withdrawn;
	
	echo "<br />Items Net: ";
	$net_total = $add_total - $all_withdrawn;
	echo $net_total;

	
	//close cell and table
	echo"</h3></td>
	    </tr>
	   </table>
	   </p>";
}
else
{
/*---------------------------------------------
Purpose: because the personnel is from different table, a separate querry is made to get the 
		first, middle, and last name of that personnel
-----------------------------------------------*/
	//table holding what was selected and reported values
	echo"<table border='0' width='100%'>
		  <tr>
		   <td width='60%'>
		   <p>";
	
	$db = mysql_select_db('uclit_db', $uclit_dbConn) or die ("Unable to select uclit_db database.");
//	echo "<p>";	
	if($personnel == 0)
	{//display a message of the user is "select all"
		echo "$arr5[0]<br>";
	}
	else
	{//display the name of the user
		$uid2 = $personnel;
		$uid2 = str_replace("UID","personnel_id",$uid2);
		$sql_personnel = "SELECT personnel_id, last_name, first_name, middle_name FROM personnel WHERE personnel_id = $uid2";
		$per_result = @mysql_query($sql_personnel, $uclit_dbConn) or die ("Unable to query: $sql_personnel");
		while($per_row = @mysql_fetch_array($per_result))
		{	
			$lname = $per_row['last_name'];
			$fname = $per_row['first_name'];
			$mname = $per_row['middle_name'];
			echo "$arr4[0] $lname, $fname $mname<br>";
			
		}
	
	}
/*-----------------------------------------------
Purpose: display back what had been selected from previous screen
------------------------------------------------*/

	$db = mysql_select_db('arlstats', $arlConn) or die ("Unable to select arlstats database.");
	
	for($z=0;$z<6;$z++)
	{
		if($z != 0)
		{
			if($arr1[$z] != 0)
			{	
				if($z == 4)
					$sql_select = "SELECT Month FROM $arr3[$z] WHERE $arr2[$z]";
				else	
					$sql_select = "SELECT Name FROM $arr3[$z] WHERE $arr2[$z]";
				$my_result = @mysql_query($sql_select, $arlConn) or die ("Unable to query: $sql_select");
				echo "$arr4[$z]";
				echo @mysql_result($my_result,0);
				echo"<br>";
			}
			else
			{//select all go here
				echo " $arr5[$z]<br>";
			}	
		}
	}	
//	echo "</p>";

	//Close first cell and open next
	echo"</p></td>
		 <td width='40%'><h3>";

/*-----------------------------------------------------
Purpose: tally up total records and sum of values of the selected item from previous screen
-------------------------------------------------------*/
	$count_add = "SELECT SUM(Value) FROM Current_Stats";
	$where2 = $where;
	$where2 .= " and CID != 7";
	$count_add .= $where2;
	$add_result = @mysql_query($count_add, $arlConn) or die ("Unable to query: $count_add");
	echo "<br />Items Added: ";
	$add_total = @mysql_result($add_result,0);
	echo $add_total;

	$count_all = "SELECT SUM(Value) FROM Current_Stats";
	$count_all .= $where;
	$all_result = @mysql_query($count_all, $arlConn) or die ("Unable to query: $count_all");
	$all_total =  @mysql_result($all_result,0);
	echo "<br />Items Withdrawn: ";
	$all_withdrawn = $all_total - $add_total;
	echo $all_withdrawn;
	
	echo "<br />Items Net: ";
	$net_total = $add_total - $all_withdrawn;
	echo $net_total;
	
	//close cell and table
	echo"</h3></td>
	    </tr>
	   </table>
	   </p>";

/*----------------------------------------------------
Purpose: querry and display in a table format for all recorded items that fit the cateria
-----------------------------------------------------*/
	$curr_status = "SELECT Current_Stats.Stat_ID, Current_Stats.Date, Current_Stats.LID, Current_Stats.MID, Current_Stats.CID, Current_Stats.UID, Current_Stats.QID, Sum(Current_Stats.Value) AS Total, Current_Stats.SID FROM Current_Stats";
	$curr_status .= $where;
	$curr_status .= " Group By Current_Stats.LID, Current_Stats.MID, Current_Stats.CID, Current_Stats.UID, Current_Stats.QID, Current_Stats.SID";
	
	
	$curr_result = @mysql_query($curr_status, $arlConn) or die ("Unable to query: $curr_status");
	
	$export = "Select arlstats.Current_Stats.Stat_ID, arlstats.Current_Stats.`Date`, Sum(arlstats.Current_Stats.Value) AS Total, arlstats.Catalog_Types.Name AS CNAME, arlstats.Calendar.`Month`, arlstats.Location.Name AS LNAME, arlstats.Material_Types.Name AS MNAME, arlstats.Stat_Types.Name as SNAME, uclit_db.personnel.last_name, uclit_db.personnel.first_name
From arlstats.Current_Stats Inner Join arlstats.Catalog_Types ON arlstats.Catalog_Types.CID = arlstats.Current_Stats.CID Inner Join arlstats.Calendar ON arlstats.Calendar.QID = arlstats.Current_Stats.QID Inner Join arlstats.Location ON arlstats.Location.LID = arlstats.Current_Stats.LID Inner Join arlstats.Material_Types ON arlstats.Material_Types.`MID` = arlstats.Current_Stats.`MID` Inner Join arlstats.Stat_Types ON arlstats.Stat_Types.SID = arlstats.Current_Stats.SID Inner Join uclit_db.personnel ON uclit_db.personnel.personnel_id = arlstats.Current_Stats.UID";
$export .= $where3;
$export .= " Group By arlstats.Current_Stats.LID, arlstats.Current_Stats.`MID`, arlstats.Current_Stats.CID, arlstats.Current_Stats.UID, arlstats.Current_Stats.QID, arlstats.Current_Stats.SID";



	//display the heads of the table
?>
	 <table width='100%' cellspacing ='10' >
            <tr>
            <td>
            <FORM ACTION='http://ul.uc.edu/uclit/protected/arlstats/user_select.php' METHOD=GET>
            <INPUT TYPE=submit VALUE='Search Again...'>
            </FORM></td>
            <td>
            <FORM ACTION='http://ul.uc.edu/uclit/protected/arlstats/menu_entry.php' METHOD=GET>
            <INPUT TYPE=submit VALUE='Enter Statistics'>
            </FORM></td>
            <td>
            <FORM id='form2' name='form2' method='Post' ACTION='http://ul.uc.edu/uclit/protected/arlstats/export.php'>
			<INPUT name='Submit' type='submit' id='Submit' value='Export to Excel' />
            <INPUT name='saveWord' type='hidden' id='saveWord' value='2' />
			<INPUT name='current_status' type='hidden' id='current_status' value='<?php echo"$export"; ?>' />
            </FORM></td>
            </tr>
            </table>
	<form id='result' name='result' action='0' method='POST'>
		<table border='1' width='100%' class="sortable" id="maintable">
			<tr>
				<td width='20%' align='center'><strong>User ID</strong></td>
				<td width='20%' align='center'><strong>Format</strong></td>
				<td width='20%' align='center'><strong>Action</strong></td>
				<td width='20%' align='center'><strong>Month</strong></td>
				<td width='20%' align='center'><strong>Location</strong></td>
				<td width='20%' align='center'><strong>Total</strong></td>
			</tr> 	
<?
	while($curr_row = @mysql_fetch_array($curr_result))
	{	
		$uid = $curr_row['UID'];
		$lid = $curr_row['LID'];
		$mid = $curr_row['MID'];
		$qid = $curr_row['QID'];
		$cid = $curr_row['CID'];
		$val = $curr_row['Total'];
		
		$arr8 = array(" Material_Types.Name, Current_Stats.MID", " Catalog_Types.Name, Current_Stats.CID", " Calendar.Month, Current_Stats.QID", " Location.Description, Current_Stats.LID");
		$arr9 = array(" Material_Types.MID = $mid ", " Catalog_Types.CID = $cid ", " Calendar.QID = $qid ", " Location.LID = $lid ");
		$arr10 = array("Material_Types", "Catalog_Types", "Calendar", "Location");	


		echo "<tr>";
		$db = mysql_select_db('uclit_db', $uclit_dbConn) or die ("Unable to select uclit_db database.");
		$uid = str_replace("UID","personnel_id",$uid); 
		//echo"$uid";
		$sql_personnel = "SELECT last_name, first_name, middle_name FROM personnel WHERE personnel_id = $uid";
		$per_result = @mysql_query($sql_personnel, $uclit_dbConn) or die ("Unable to query: $sql_personnel");
		while($per_row = @mysql_fetch_array($per_result))
		{	
			$name = $per_row['last_name'];
			$name .= ", " . $per_row['first_name'];
			$name .= " " . $per_row['middle_name'];
			echo "<td nowrap align='left'>$name</td>";
		}
		
		//convert id values from current_stats into its name	
		for($d=0;$d<4;$d++)
		{
			$db = mysql_select_db('arlstats', $arlConn) or die ("Unable to select arlstats database.");
			$convert_item = "SELECT $arr8[$d] FROM $arr10[$d] JOIN Current_Stats WHERE $arr9[$d]";
			$item_status = @mysql_query($convert_item, $arlConn) or die ("Unable to query: $convert_item");
			echo"<td width='20%' align='center'>";
			echo @mysql_result($item_status,0);
			echo "</td>";
			
		}
		echo "<td width='20%' align='right'>$val</td>";
		echo "</tr>";
	}
		
	echo "</table>";
}
	require ("/var/www/html/uclit/protected/common/css_login/css_foot2.php"); 
?>