<?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');
		
	// Create our database connection
	$db = mysql_select_db('arlstats', $arlConn) or die ("Unable to select arlstats database.");
	
/*-------------------------------------------------------------
Purpose: Create queries, and variables for all returned values.  Computation of final variable below queries.
-------------------------------------------------------------*/
	
	//Volumes held statistic
	

	$sql_volumes_held = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE ((MID = 1 OR MID = 2 OR MID = 3) AND (CID = 4 or CID=3 or CID=2 or CID=1) AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$volumes_held_result = @mysql_query($sql_volumes_held, $arlConn) or die ("Unable to query: $sql_volumes_held");
	$held_total = @mysql_result($volumes_held_result,0);

	$sql_volumes_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE ((MID = 1 OR MID = 2 OR MID = 3) AND (CID = 7) AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)AND SID = 1)";
	$volumes_widthdrawn_result = @mysql_query($sql_volumes_widthdrawn, $arlConn) or die ("Unable to query: $sql_volumes_widthdrawn");
	$widthdrawn_total = @mysql_result($volumes_widthdrawn_result,0);
	
	$vol_net = $held_total - $widthdrawn_total;
	//$vol_held_curr_add = 2481085 + $vol_net;
	
	//Microform Result
	$sql_microforms = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 4 AND CID = 4 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$microforms_result = @mysql_query($sql_microforms, $arlConn) or die ("Unable to query: $sql_microforms");
	$microforms_total = @mysql_result($microforms_result,0);
	
	$sql_microforms_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 4 AND CID = 7 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)AND SID = 1)";
	$microforms_widthdrawn_result = @mysql_query($sql_microforms_widthdrawn, $arlConn) or die ("Unable to query: $sql_microforms_widthdrawn");
	$volume_widthdrawn_total = @mysql_result($volumes_widthdrawn_result,0);
	
	$mi_total = $microforms_total - $volume_widthdrawn_total;
	
	//Computer File Result
	$sql_cfile = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 5 AND CID = 4 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$cfile_result = @mysql_query($sql_cfile, $arlConn) or die ("Unable to query: $sql_cfile");
	$cfile_total = @mysql_result($cfile_result,0);
	
	$sql_cfile_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 5 AND CID = 7 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)AND SID = 1)";
	$cfile_widthdrawn_result = @mysql_query($sql_cfile_widthdrawn, $arlConn) or die ("Unable to query: $sql_cfile_widthdrawn");
	$cfile_widthdrawn_total = @mysql_result($cfile_widthdrawn_result,0);
	
	$c_total = $cfile_total - $cfile_widthdrawn_total;
	
	//Map Result
	$sql_map = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 6 AND CID = 4 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)AND SID = 1)";
	$map_result = @mysql_query($sql_map, $arlConn) or die ("Unable to query: $sql_map");
	$map_total = @mysql_result($map_result,0);
	
	$sql_map_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 6 AND CID = 7 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)AND SID = 1)";
	$map_widthdrawn_result = @mysql_query($sql_map_widthdrawn, $arlConn) or die ("Unable to query: $sql_map_widthdrawn");
	$map_widthdrawn_total = @mysql_result($map_widthdrawn_result,0);
	
	$ma_total = $map_total - $map_widthdrawn_total;
	
	//2-D Object Result
	$sql_object = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 7 AND CID = 4 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$object_result = @mysql_query($sql_object, $arlConn) or die ("Unable to query: $sql_object");
	$object_total = @mysql_result($volumes_result,0);
	
	$sql_object_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 7 AND CID = 7 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)AND SID = 1)";
	$object_widthdrawn_result = @mysql_query($sql_object_widthdrawn, $arlConn) or die ("Unable to query: $sql_object_widthdrawn");
	$object_widthdrawn_total = @mysql_result($object_widthdrawn_result,0);
	
	$o_total = $object_total - $object_widthdrawn_total;
	
	//Sound Recordings Result
	$sql_sound = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 8 AND CID = 4 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$sound_result = @mysql_query($sql_sound, $arlConn) or die ("Unable to query: $sql_sound");
	$sound_total = @mysql_result($sound_result,0);
	
	$sql_sound_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 8 AND CID = 7 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$sound_widthdrawn_result = @mysql_query($sql_sound_widthdrawn, $arlConn) or die ("Unable to query: $sql_sound_widthdrawn");
	$sound_widthdrawn_total = @mysql_result($sound_widthdrawn_result,0);
	
	$s_total = $sound_total - $sound_widthdrawn_total;
	
	//Projected Medium Result
	$sql_pmedium = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 4 AND CID = 9 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$pmedium_result = @mysql_query($sql_microforms, $arlConn) or die ("Unable to query: $sql_microforms");
	$pmedium_total = @mysql_result($pmedium_result,0);
	
	$sql_pmedium_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 9 AND CID = 7 AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34) AND SID = 1)";
	$pmedium_widthdrawn_result = @mysql_query($sql_pmedium_widthdrawn, $arlConn) or die ("Unable to query: $sql_pmedium_widthdrawn");
	$pmedium_widthdrawn_total = @mysql_result($pmedium_widthdrawn_result,0);
	
	$p_total = $pmedium_total - $pmedium_widthdrawn_total;
	

	//Adjustments *** (MID1,2,3)(CID1,2,3,4)	
	$sql_govdoc = "SELECT SUM(Value) FROM Archive_Stats WHERE (SID = 3)AND (MID=1 or MID=2 or MID=3) AND (CID = 4 or CID=3 OR CID=2 OR CID=1) AND(QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)";
	$govdoc_result = @mysql_query($sql_govdoc, $arlConn) or die ("Unable to query: $sql_govdoc");
	$govdoc_total = @mysql_result($govdoc_result,0);
	
	$g_total = 38528 - $govdoc_total;
	$vol_held_curr = $vol_held_curr_add + $g_total;

	
	//get info for last year govdocs retro count
	$sql_lastyearadded = "SELECT SUM(Value) FROM Archive_Stats WHERE (SID = 3) AND (CID = 4 or CID=3 or CID=2 or CID=1) AND (MID = 1 OR MID = 2 OR MID = 3) AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)";
	$lastyearadded_result = @mysql_query($sql_lastyearadded, $arlConn) or die ("Unable to query: $sql_lastyearadded");
	$lastyearadded_total = @mysql_result($lastyearadded_result,0);

	//get info for last year govdocs retro withdrawn
	$sql_lastyearwithdrawn = "SELECT SUM(Value) FROM Archive_Stats WHERE (SID = 3) AND (CID = 7) AND (MID = 1 OR MID = 2 OR MID = 3) AND (QID = 17 OR QID = 18 OR QID = 19 OR QID = 20 OR QID = 21 OR QID = 22 OR QID = 29 OR QID = 30 OR QID = 31 OR QID = 32 OR QID = 33 OR QID = 34)";
	$lastyearwithdrawn_result = @mysql_query($sql_lastyearwithdrawn, $arlConn) or die ("Unable to query: $sql_lastyearwithdrawn");
	$lastyearwithdrawn_total = @mysql_result($lastyearwithdrawn_result,0);
	if($lastyearwithdrawn_total == "")
		$lastyearwithdrawn_total = 0;
	if($lastyearadded_total == "")
		$lastyearadded_total = 0;
	$net = $lastyearadded_total - $lastyearwithdrawn_total;
	$net_thisYear = 38528-$lastyearadded_total;
	$thisyear = date("F")." - ".date("Y");
	$yearAlone = date("Y");
	$adj_total = 2547033+$net;
	$vol_held_curr_add = $adj_total + $vol_net;

/*-----------------------------------
Purpose: Display on page
-----------------------------------*/	
	
$vol_net=number_format($vol_net);
$vol_held_curr_add=number_format($vol_held_curr_add);
$lastyearadded_total=number_format($lastyearadded_total);
$net_thisYear=number_format($net_thisYear);	
	
	
echo "

<table width='100%' cellspacing ='10' >
	<tr>
	<td>
	<FORM ACTION='http://ul.uc.edu/uclit/protected/assocdean/collstats/arl_report_ytd_2006.php' METHOD=GET>
	<INPUT TYPE=submit VALUE='ARL Collection Report 2006'>
	</FORM></td>
	<td>
	<FORM ACTION='http://ul.uc.edu/uclit/protected/assocdean/collstats/arl_report_adj_2006.php' METHOD=GET>
	<INPUT TYPE=submit VALUE='ARL Collection Adjustments 2006'>
	</FORM></td>
	<td>
	<FORM ACTION='http://ul.uc.edu/uclit/protected/assocdean/collstats/arl_report_summary_2006.php' METHOD=GET>
	<INPUT TYPE=submit VALUE='ARL Yearly Summaries 2006'>
	</FORM></td>
	</tr>
	</table>

	<font size='+2'>ARL Collection Report Yearly Summaries (2006)</font>
	<table border='1' cellspacing='10'>
						<tr><br /><td colspan='4'><B>Yearly Totals:</B></td></tr><tr>
						<td><strong>Year</strong></td>
						<td><strong>Previous Year Volume Held</strong></td>
						<td><strong>Volume Net</strong></td>
						<td><strong>Volumes Current Held</strong></td>
					</tr>
					
					<tr>
						<td>2006</td>
						<td>2,549,623</td>
						<td>$vol_net</td>
						<td>$vol_held_curr_add</td>
										
					</tr>
					
					<tr>
						<td>2005</td>
						<td>2,481,085</td>
						<td>65,013</td>
						<td>2,547,033</td>
										
					</tr>
					<tr>
						<td>2004</td>
						<td></td>
						<td></td>
						<td>2,481,085</td>
					
						
					</tr>
					<tr></tr>		
					<tr><td colspan='4'><br /><B>Uncataloged Gov Docs:</B><br /></td></tr>
					<tr>
						<td><strong>Year</strong></td>
						<td><strong>Previous Year Uncataloged Gov Docs</strong></td>
						<td><strong>Volumes Added</strong></td>
						<td><strong>Current Uncataloged Gov Docs</strong></td>
					</tr>
					<tr>
						<td>2006</td>
						<td>38,528</td>
						<td>$lastyearadded_total</td>
						<td>$net_thisYear</td>
					</tr>
					
					
					<tr>
						<td>2005</td>
						<td>40,550*</td>
						<td>2,022</td>
						<td>38,528</td>
					</tr>
										
				</table>
			</td>
		</tr>
	</table>	
	*Number provided by GovDocs Department
	  ";

require ("/var/www/html/uclit/protected/common/css_login/css_foot.php"); ?>