<?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'];
	
	$UID = "";
	$LID = "";
	$MID = "";
	$CID = "";
	$QID = "";
	
	$UIDor = "";
	$LIDor = "";
	$MIDor = "";
	$CIDor = "";
	$QIDor = "";
	
	$Uexport = "";
	$Lexport = "";
	$Mexport = "";
	$Cexport = "";
	$Qexport = "";
	
	$Uexportor = "";
	$Lexportor = "";
	$Mexportor = "";
	$Cexportor = "";
	$Qexportor = "";
		
	while (list($key, $val) = each ($personnel)){
		$UID .= "(UID = $val) and ";
		$UIDor .= "(UID = $val) or ";
		$Uexport .= "(arlstats.Current_Stats.UID = $val) and ";
		$Uexportor .= "(arlstats.Current_Stats.UID = $val) or ";}
		$UID = substr($UID, 0, -4);
		$Uexport = substr($Uexport,0, -4);
		$Uexportor = substr($Uexportor,0, -3);
		$UIDor = substr($UIDor, 0, -3);
		
	while (list($key2, $val2) = each ($location)){
		$LID .= "(LID = $val2) and ";
		$LIDor .= "(LID = $val2) or ";
		$Lexport .= "(arlstats.Current_Stats.LID = $val2) and ";
		$Lexportor .= "(arlstats.Current_Stats.LID = $val2) or ";}
		$LID = substr($LID, 0, -4);
		$Lexport= substr($Lexport, 0, -4);
		$Lexportor= substr($Lexportor, 0, -3);
		$LIDor = substr($LIDor, 0, -3);
	
	while (list($key3, $val3) = each ($material)){
		$MID .= "(MID = $val3) and ";
		$MIDor .= "(MID = $val3) or ";
		$Mexport .= "(arlstats.Current_Stats.MID = $val3) and ";
		$Mexportor .= "(arlstats.Current_Stats.MID = $val3) or ";}
		$Mexport = substr($Mexport, 0, -4);
		$Mexportor = substr($Mexportor, 0, -4);
		$MID = substr($MID, 0, -4);
		$MIDor = substr($MIDor, 0, -3);
		
	while (list($key4, $val4) = each ($catalog)){
		$CID .= "(CID = $val4) and ";
		$CIDor .= "(CID = $val4) or ";
		$Cexport .= "(arlstats.Current_Stats.CID = $val4) and ";
		$Cexportor .= "(arlstats.Current_Stats.CID = $val4) or ";}
		$Cexport = substr($Cexport, 0, -4);
		$Cexportor = substr($Cexportor, 0, -3);
		$CID = substr($CID, 0, -4);
		$CIDor = substr($CIDor, 0, -3);
		
	while (list($key5, $val5) = each ($month)){
		$QID .= "(QID = $val5) and ";
		$QIDor .= "(QID = $val5) or ";
		$Qexport .= "(arlstats.Current_Stats.QID = $val5) and ";
		$Qexportor .= "(arlstats.Current_Stats.QID = $val5) or";}
		$Qexport = substr($Qexport, 0, -4);
		$Qexportor = substr($Qexportor, 0, -3);
		$QID = substr($QID, 0, -4);
		$QIDor = substr($QIDor, 0, -4);
	
	
	$arr1 = array("$personnel", "$location","$material","$catalog","$month","$stype");
	$arr2 = array("$UID", "$LID", "$MID", "$CID", "$QID", "(SID = $stype)");	
	$arror = array("$UIDor", "$LIDor", "$MIDor", "$CIDor", "$QIDor", "(SID = $stype)");
	$arrexport = array("$Uexport", "$Lexport", "$Mexport", "$Cexport", "$Qexport", "(arlstats.Current_Stats.SID = $stype)");	
	$arrexportor = array("$Uexportor", "$Lexportor", "$Mexportor", "$Cexportor", "$Qexportor", "(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 ";
			$where2 .= " WHERE ("; 
		}
	}
	$count = 0;
	for($y=0;$y<6;$y++)
	{//add only selected options
		if(empty($arr1[$y]) != "FALSE")
		{
			$count++;
			if($count == 1)
			{
				$where .= $arr2[$y];
				$where2 .= $arror[$y];
			}
			if($count > 1)
			{
				
				
				$where .= " and ";
				$where2 .= ") and (";
				
				if ($count == 4)
				{
					$where5 = $where2;
					$where5 .= " (CID=7)) and (";
				}
							
				$where .= $arr2[$y];
				$where2 .= $arror[$y];
				
				if ($count > 4)
				{
					$where5 .= $arror[$y];
				}
			}	
		}
	}


// For Export Function

$atLeastOnce = "false";
for($r=0;$r<6;$r++)
	{
		if(($arr1[$r]) != 0 && $atLeastOnce == "false")
		{
			$atLeastOnce = "true";
			$where3 .= " WHERE (";
			 
		}
	}
	$count3 = 0;
	for($s=0;$s<6;$s++)
	{//add only selected options
		if(empty($arr1[$s]) != "FALSE")
		{
				$where3 .= $arrexportor[$s];
				
		
				$where3 .= ") and (";
							
				
		}
	}
$where3 = substr($where3, 0, -5);
//	for($r=0;$r<6;$r++)
	//{
		//if(($arr1[$r]) != 0 && $atLeastOnce2 == "false")
//		{
	//		$atLeastOnce2 = "true";
		//	$where3 .= " WHERE (";
//			$where4 .= " WHERE (";
	//	}
//	}
//	$count3 = 0;
//	for($s=0;$s<6;$s++)
//	{//add only selected options
//		if(empty($arr1[$s]) != "FALSE")
//		{
///			$count3++;
//			if($count3 == 1)
//				$where3 .= $arrexport[$s];
//				$where4 .= $arrexport[$s];
//			if($count3 > 1)
//			{
//				$where3 .= ") and (";
//				$where4 .= ") and (";
//				$where3 .= $arrexportor[$s];
//				$where4 .= $arrexportor[$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.");

	
	//display the name of the user
		$uid2 = $personnel;
		while (list($keyper, $valper) = each ($uid2)){
		$valper = str_replace("UID","personnel_id",$valper);
		$sql_personnel = "SELECT personnel_id, last_name, first_name, middle_name FROM personnel WHERE personnel_id = $valper";
		$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($z == 4)
			{
					$sql_select = "SELECT Month FROM $arr3[$z] WHERE $arror[$z]";
					$my_result = @mysql_query($sql_select, $arlConn) or die ("Unable to query: $sql_select");
					while($my_row = @mysql_fetch_array($my_result))
					{	
						$month = $my_row['Month'];
						echo "$arr4[$z] $month <br>";
					}
			}
				else
				{	
					$sql_select = "SELECT Name FROM $arr3[$z] WHERE $arror[$z]";
					$my_result = @mysql_query($sql_select, $arlConn) or die ("Unable to query: $sql_select");
					while($my_row = @mysql_fetch_array($my_result))
					{	
						$name = $my_row['Name'];
						echo "$arr4[$z] $name <br>";
					}
				}
				
			
			//echo "$arr4[$z]";
			//echo "$sql_select";
			//echo @mysql_result($my_result,0);
			//echo"<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";<br />
	//$cidpos = strpos($where2, "or (CID = 7)");
	$wherex .= str_replace("or (CID = 7)","",$where2);
	$count_add .= $wherex;
	$count_add .= ")";
	//echo"$count_add";
	$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);
	if(($CID == "(CID = 7) ") || ($add_total == NULL))
		{$add_total = 0;}
	echo $add_total;
	
	$count_all = "SELECT SUM(Value) FROM Current_Stats";
	if(strstr($CID,'CID = 7')) 
		{ 
		$count_all .= $where5;
		$count_all = substr_replace($count_all, ") and ", -9);
		$count_all .= $arr2[5];
		$all_result = @mysql_query($count_all, $arlConn) or die ("Unable to query: $count_all");
		$all_withdrawn =  @mysql_result($all_result,0);
		} 
	else
		{$all_withdrawn = 0;}

	echo "<br />Items Withdrawn: ";
	//$all_withdrawn = $all_total - $add_total;
	if($all_withdrawn == NULL)
		{$all_withdrawn = 0;}
	echo $all_withdrawn;
	
	echo "<br />Items Net: ";
	$net_total = $add_total - $all_withdrawn;
	echo $net_total;
	//echo "$count_all";
	
	//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.");

	
	//display the name of the user
		$uid2 = $personnel;
		while (list($keyper, $valper) = each ($uid2)){
		$valper = str_replace("UID","personnel_id",$valper);
		$sql_personnel = "SELECT personnel_id, last_name, first_name, middle_name FROM personnel WHERE personnel_id = $valper";
		$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($z == 4)
			{
					$sql_select = "SELECT Month FROM $arr3[$z] WHERE $arror[$z]";
					$my_result = @mysql_query($sql_select, $arlConn) or die ("Unable to query: $sql_select");
					while($my_row = @mysql_fetch_array($my_result))
					{	
						$month = $my_row['Month'];
						echo "$arr4[$z] $month <br>";
					}
			}
				else
				{	
					$sql_select = "SELECT Name FROM $arr3[$z] WHERE $arror[$z]";
					$my_result = @mysql_query($sql_select, $arlConn) or die ("Unable to query: $sql_select");
					while($my_row = @mysql_fetch_array($my_result))
					{	
						$name = $my_row['Name'];
						echo "$arr4[$z] $name <br>";
					}
				}
				
			
			//echo "$arr4[$z]";
			//echo "$sql_select";
			//echo @mysql_result($my_result,0);
			//echo"<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";<br />
	//$cidpos = strpos($where2, "or (CID = 7)");
	$wherex .= str_replace("or (CID = 7)","",$where2);
	$count_add .= $wherex;
	$count_add .= ")";
	//echo"$count_add";
	$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);
	if(($CID == "(CID = 7) ") || ($add_total == NULL))
		{$add_total = 0;}
	echo $add_total;
	
	$count_all = "SELECT SUM(Value) FROM Current_Stats";
	if(strstr($CID,'CID = 7')) 
		{ 
		$count_all .= $where5;
		$count_all = substr_replace($count_all, ") and ", -9);
		$count_all .= $arr2[5];
		$all_result = @mysql_query($count_all, $arlConn) or die ("Unable to query: $count_all");
		$all_withdrawn =  @mysql_result($all_result,0);
		} 
	else
		{$all_withdrawn = 0;}

	echo "<br />Items Withdrawn: ";
	//$all_withdrawn = $all_total - $add_total;
	if($all_withdrawn == NULL)
		{$all_withdrawn = 0;}
	echo $all_withdrawn;
	
	echo "<br />Items Net: ";
	$net_total = $add_total - $all_withdrawn;
	echo $net_total;
	//echo "$count_all";
	
	//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 * FROM Current_Stats";
	$curr_status .= $where2;
	$curr_status .= ")";
	//echo "$curr_status";
	$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 = "Select arlstats.Current_Stats.Stat_ID, arlstats.Current_Stats.`Date`, arlstats.Current_Stats.Description AS DESCRIPTION, arlstats.Current_Stats.Value, 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";
	
//echo "$export";
	
	//display the heads of the table
?>
            <table width='100%' cellspacing ='10' >
            <tr>
            <td>
               <FORM ACTION='http://ul.uc.edu/uclit/protected/depthead/collstats/menu_select_dh.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/depthead/collstats/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='left'><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>
				<td width='20%' align='center'><strong>Edit</strong></td>
				<td width='20%' align='center'><strong>Delete</strong></td>
				<?
					if ($stype == '4')
					echo"<td width='20%' align='center'><strong>Description</strong></td>"; ?>
				
			</tr> 	
<?
	while($curr_row = @mysql_fetch_array($curr_result))
	{	
		$statid = $curr_row['Stat_ID'];
        $uid = $curr_row['UID'];
		$lid = $curr_row['LID'];
		$mid = $curr_row['MID'];
		$qid = $curr_row['QID'];
		$cid = $curr_row['CID'];
		$val = $curr_row['Value'];
		$description = $curr_row['Description'];
		
		$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='center'>$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='center'>$val</td>
		<td align='center'><a href='http://ul.uc.edu/uclit/protected/catalogsuper/administer_form.php?type=edit&statid=$statid'><img src='edit.gif' border='0'></a></td>
<td align='center'><a href='http://ul.uc.edu/uclit/protected/catalogsuper/process_update.php?type=delete&statid=$statid&persid=$uid'><img src='delete.gif' border='0'></a></td>";
	if($stype == '4')
		echo "<td width='20%' align='center'>$description</td>";

		echo "</tr>";
		}
		
	echo "</table>";
}
	require ("/var/www/html/uclit/protected/common/css_login/css_foot2.php"); 
?>