<?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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) 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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)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 or CID=3 or CID=2 or CID=1) AND (QID = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) 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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)AND SID = 1)";
	$microforms_widthdrawn_result = @mysql_query($sql_microforms_widthdrawn, $arlConn) or die ("Unable to query: $sql_microforms_widthdrawn");
	$microforms_widthdrawn_total = @mysql_result($microforms_widthdrawn_result,0);
		$mi_total = $microforms_total - $microforms_widthdrawn_total;
	
	//Computer File Result
	$sql_cfile = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 5 AND (CID = 4 or CID=3 or CID=2 or CID=1) AND (QID = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) 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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)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 or CID=3 or CID=2 or CID=1) AND (QID = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)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 or CID=3 or CID=2 or CID=1) AND (QID = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) AND SID = 1)";
	$object_result = @mysql_query($sql_object, $arlConn) or die ("Unable to query: $sql_object");
	$object_total = @mysql_result($object_result,0);
	
	$sql_object_widthdrawn = "SELECT SUM(Value) AS Total FROM Archive_Stats WHERE (MID = 7 AND CID = 7 AND (QID = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)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 OR CID = 3 OR CID = 2 OR CID =1) AND (QID = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) 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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) 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 = 9 AND (CID = 4 OR CID = 3 OR CID = 2 OR CID =1) AND (QID = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) AND SID = 1)";
	$pmedium_result = @mysql_query($sql_pmedium, $arlConn) or die ("Unable to query: $sql_pmedium");
	$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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28) 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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)";
	$govdoc_result = @mysql_query($sql_govdoc, $arlConn) or die ("Unable to query: $sql_govdoc");
	$govdoc_total = @mysql_result($govdoc_result,0);
	
	$g_total = 40550 - $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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)";
	$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 = 11 OR QID = 12 OR QID = 13 OR QID = 14 OR QID = 15 OR QID = 16 OR QID = 23 OR QID = 24 OR QID = 25 OR QID = 26 OR QID = 27 OR QID = 28)";
	$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 = 40550-$lastyearadded_total;
	$thisyear = date("F")." - ".date("Y");
	$yearAlone = date("Y");
	$adj_total = 2481085+$net+442;
	$vol_held_curr_add = $adj_total + $vol_net;
	$mi_unit= $mi_total+2584094;
	$cf_total= $c_total+11076;
	$ma_t2= $ma_total+168292;
	$o_t2= $o_total+257383;
	$p_t2= $p_total+17421;
	$s_t2= $s_total+50864;
	
	

/*-----------------------------------
Purpose: Display on page
-----------------------------------*/	

$adj_total =number_format($adj_total);
$held_total =number_format($held_total);
$widthdrawn_total =number_format($widthdrawn_total);
$vol_net =number_format($vol_net);
$vol_held_curr_add =number_format($vol_held_curr_add);
$mi_total=number_format($mi_total);
$mi_unit=number_format($mi_unit);
$c_total=number_format($c_total);
$cf_total=number_format($cf_total);
$ma_total=number_format($ma_total);
$ma_t2=number_format($ma_t2);
$o_total=number_format($o_total);
$o_t2=number_format($o_t2);
$s_total=number_format($s_total);
$s_t2=number_format($s_t2);
$p_total=number_format($p_total);
$p_t2=number_format($p_t2);
	
echo "
	<table width='100%' cellspacing ='10' >
	<tr>
	<td>
	<FORM ACTION='http://ul.uc.edu/uclit/protected/assocdean/collstats/arl_report_ytd.php' METHOD=GET>
	<INPUT TYPE=submit VALUE='ARL Collection Report 2005'>
	</FORM></td>
	<td>
	<FORM ACTION='http://ul.uc.edu/uclit/protected/assocdean/collstats/arl_report_adj.php' METHOD=GET>
	<INPUT TYPE=submit VALUE='ARL Collection Adjustments 2005'>
	</FORM></td>
	<td>
	<FORM ACTION='http://ul.uc.edu/uclit/protected/assocdean/collstats/arl_report_summary.php' METHOD=GET>
	<INPUT TYPE=submit VALUE='ARL Yearly Summaries 2005'>
	</FORM></td>
	</tr>
	</table>







	<font size='+2'>ARL Collection Report Year-To-Date (2005)</font>
	<table border='1' width='100%' cellspacing='10' >
				<tr><td></td>
					<td colspan='4' align='center'><strong><br />Volumes</strong></td>
				</tr>
				<tr><td></td>
					<td></td><td></td>
					<td border='0' align='right'>Volumes Held Previous Year (2004)</td>
					<td >$adj_total</td>
				</tr>
				<tr><td></td>
					<td></td><td></td>
					<td border='0'align='right'>Volumes Added(Gross):</td>
					<td>$held_total</td>
				</tr>
				<tr><td></td>
					<td></td><td></td>
					<td align='right'>Volumes Withdrawn:</td>
					<td>$widthdrawn_total</td>
				</tr>
				<tr><td></td>
					<td></td><td></td>
					<td align='right'>Volumes Added(Net):</td>
					<td>$vol_net</td>
				</tr>	
				<tr><td></td>
					<td></td><td></td>
					<td align='right'><br />Volume Held Current</td>
					<td><strong><br />$vol_held_curr_add</strong></td>
				</tr>
					<br /><br />
				<tr>
					<td><b>2003-2004 Totals</b></td>	    
					<td colspan='4'align='center'><strong><br />Other Library Materials</strong><br/></td> 
				</tr>
				<tr>
					<td align='right'>2,584,094</td>
					<td align='right'>Microforms Net:</td>
					<td>$mi_total</td>
					<td align='right'>Microforms Units Total:</td>
					<td><strong>$mi_unit</strong></td>
				</tr>
				<tr>
					<td align='right'>11,076</td>
					<td align='right'>Computer Files Net:</td>
					<td>$c_total</td>
					<td align='right'>Computer Files Total:</td>
					<td><strong>$cf_total</strong></td>
				</tr>
				<tr><td></td>	 
					<td colspan='4'align='center'><strong><br />Audiovisual Materials</strong><br/></td> 
				</tr>
				<tr>
					<td align='right'>168,292</td>
					<td align='right'>Cartographic(Maps) Net:</td>
					<td>$ma_total</td>
					<td align='right'>Cartographic(Maps)Total:</td>
					<td><strong>$ma_t2</strong></td>
					
					</tr>	
				<tr>
					<td align='right'>257,383</td>
					<td align='right'>Graphic (2D)Net:</td>
					<td>$o_total</td>
					<td align='right'>Graphic (2D) Total:</td>
					<td><strong>$o_t2</strong></td>
				</tr>		
				<tr>
					<td align='right'>50,864</td>
					<td align='right'>Audio (sound recordings)Net:</td>
					<td>$s_total</td>
					<td align='right'>Audio (sound recordings)Total:</td>
					<td><strong>$s_t2</strong></td>
				</tr>		
				<tr>
					<td align='right'>17,421</td>
					<td align='right'>Film & Video (Projected Medium)Net:</td>
					<td>$p_total</td>
					<td align='right'>Film & Video (Projected Medium)Total:</td>
					<td><strong>$p_t2</strong></td>
				</tr>
			
			</table>
	  ";

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