<?php
include('../init.php');
include('../libs/functions.php');

if (checklogin($userID, "../index.php")) {


if (isset($_REQUEST['p']) && is_numeric($_REQUEST['p'])) {
	$p = scrub_white_list($_REQUEST['p'], 'NUMBERONLY');
} else {$p = 1;}

print_header();

$message = 'File History page accessed by ' . $username;
audit_log($userID, $message);

$sql = "SELECT account_type, cs_id FROM tb_user WHERE user_id=$userID";
$rs = $db->Execute($sql);
$account_type = $rs->fields('account_type');
$cs_id = $rs->fields('cs_id');
?>
<style type="text/css">
.auto-style1 {
	
	border: 1px solid #999999;
	border-style: inset;
	border-color: gray;
	background-color: white;
	-moz-border-radius: ;
}
</style>

<style type="text/css">
#manage {
	width:755px;
	border-width: 1px;
	border-spacing: ;
	border-style: outset;
	border-color: gray;
	border-collapse: separate;
	background-color: white;
}
#manage th {
	border-width: 1px;
	padding: 1px;
	border-style: inset;
	border-color: gray;
	background-color: white;
	-moz-border-radius: ;
}
#manage td {
	border-width: 1px;
	padding: 1px;
	border-style: inset;
	border-color: gray;
	background-color: white;
	-moz-border-radius: ;
}
</style>

<H1 align=center> View/Download Reports</H1>
<CENTER><A HREF="./index.php">Return to Main Menu</A></CENTER>
<HR>

<TABLE ALIGN="CENTER" NAME="manage" id="manage" cellspacing="0" class="auto-style1" style="width: 100%; float: left">
<TR>
	<TH class='auto-style1' align='center'>File ID</TH>
<?if ($account_type == 'CF') { ?>
	<TH class='auto-style1' align='center'>Correctional Facility </TH>
	<TH class='auto-style1' align='center'>Facility Type</TH>
<?} else if ($account_type == 'CS') { ?>
	<TH class='auto-style1' align='center'>Court System Name </TH>
	<TH class='auto-style1' align='center'>Court System Type</TH>
<?}?>
	
	<TH class='auto-style1' align='center'>UserName</TH>
	<TH class='auto-style1' align='center'>File Name</TH>
	<TH class='auto-style1' align='center'>Upload Date  (EST)</TH>
	<TH class='auto-style1' align='center'>Download File</TH>
<!--- <TH class='auto-style1'>Dataset Version</TH> --->	
</TR>

<?

$results = 0;
$start = 1+($p-1)*20;
$finish = $start+19;


$where_sql = 'hf.user_id = ' . $userID . ' and ';

if ($account_type == 'CF') {
	
	if ( 0 == $cs_id ) {

		$sql = 'Select id, user_id, file_id, username, cs_type, correctional_system, file_name, download_date
		
				From (
					SELECT hf.file_id, hf.user_id, hf.id, tu.username, cs.cs_type, cs.correctional_name as correctional_system, hf.file_name, hf.download_date, ROW_NUMBER() OVER(ORDER BY hf.download_date desc) [rowNumber]
					From tb_response_file hf, tb_cf_system cs,  tb_user tu
				      WHERE     ' . $where_sql . ' hf.user_id = cs.user_id and hf.user_id = tu.user_id 
				)q
				
			WHERE rowNumber BETWEEN ' . $start . ' and ' . $finish . '
			';
			
			$rs = $db->Execute($sql);
			$record_count = $rs->RecordCount();
			//echo $sql . '<br>';
			
			if ( 0 == $record_count) {
				$sql = 'Select id, user_id, file_id, username, cs_type, correctional_system, file_name, download_date
		
				From (
					SELECT hf.file_id, hf.user_id, hf.id, tu.username, cs.cs_type, cs.other_cs as correctional_system, hf.file_name, hf.download_date, ROW_NUMBER() OVER(ORDER BY hf.download_date desc) [rowNumber]
					From tb_response_file hf, tb_other_cs cs,  tb_user tu
				      WHERE     ' . $where_sql . ' hf.user_id = cs.user_id and hf.user_id = tu.user_id 
				)q
				
					WHERE rowNumber BETWEEN ' . $start . ' and ' . $finish . '
				';
				
				$rs = $db->Execute($sql);
			}
		
	} else {
		
		$sql = 'Select id, file_id, username, cs_type, correctional_system, file_name, download_date
		
				From (
					SELECT hf.file_id, hf.user_id, hf.id, tu.username, sg.cs_type, sp.correctional_system, hf.file_name, hf.download_date, ROW_NUMBER() OVER(ORDER BY hf.download_date desc) [rowNumber]
					From tb_response_file hf, tb_correctional_system sp, tb_correctional_system_type sg, tb_user tu
				      WHERE     ' . $where_sql . ' hf.cs_id = sp.cs_id and sp.cs_type = sg.cs_type_id and hf.user_id = tu.user_id 
				)q
				
			WHERE rowNumber BETWEEN ' . $start . ' and ' . $finish . '
			';
			
			$rs = $db->Execute($sql);
	}
	
//echo $sql;

while (!$rs->EOF) { 	
	$data_id = $rs->fields('id');
	$file_id = $rs->fields('file_id');
	$user_name = $rs->fields('username');
	$cs_type = $rs->fields('cs_type');
	$correctional_system = $rs->fields('correctional_system');
	$file_name = $rs->fields('file_name');
	//$dataset_type = $rs->fields('dataset_type');
	$download_date = $rs->fields('download_date');
	$newDate = date("m/d/Y  g:s  a", strtotime($download_date));
	
	
	
	print ("
	
		<tr>
			<td class='auto-style1' align='center'>$data_id</td>
			<td class='auto-style1' align='center'>$correctional_system</a></td>
			<td class='auto-style1' align='center'>$cs_type</td>
			<td class='auto-style1' align='center'>$username</td>
			<td class='auto-style1' align='center'><a href='download_file.php?data_id=$data_id' target='_new'>$file_name</a></td>
			<td class='auto-style1' align='center'>$newDate</td>
			<td class='auto-style1' align='center'><form action='download_file.php?data_id=$data_id' target='_new' method='post'><input type='submit' value='Download'></form></td>
			
		</tr>
	
	");
	
	
	$results = $results + 1;    
	$rs->MoveNext();
  }

 } else if ($account_type == 'CS') {
 
 $sql = 'Select id, file_id, username, court_type, court_name, file_name, download_date
		From (
			SELECT hf.file_id, hf.user_id, hf.id, tu.username, sg.court_type, sp.court_name, hf.file_name, hf.download_date, ROW_NUMBER() OVER(ORDER BY hf.download_date desc) [rowNumber]
			From tb_response_file hf, tb_court_system sp, tb_court_system_type sg, tb_user tu
                      WHERE     ' . $where_sql . ' sg.court_type_id = sp.court_type_id and hf.user_id = tu.user_id and tu.user_id = sp.user_id
		)q
		
	WHERE rowNumber BETWEEN ' . $start . ' and ' . $finish . '
	';

$rs = $db->Execute($sql);

while (!$rs->EOF) { 	
	$data_id = $rs->fields('id');
	$file_id = $rs->fields('file_id');
	$user_name = $rs->fields('username');
	//$cs_type = $rs->fields('cs_type');
	//$correctional_system = $rs->fields('correctional_system');
	$court_name = $rs->fields('court_name');
	$court_system = $rs->fields('court_type');
	$file_name = $rs->fields('file_name');
	//$dataset_type = $rs->fields('dataset_type');
	$download_date = $rs->fields('download_date');
	$newDate = date("m/d/Y  g:s  a", strtotime($download_date));
	
	
	
	print ("
	
		<tr>
			<td class='auto-style1' align='center'>$data_id</td>
			<td class='auto-style1'>$court_name</td>
			<td class='auto-style1'>$court_system</td>
			<td class='auto-style1' align='center'>$username</td>
			<td class='auto-style1' align='center'><a href='download_file.php?data_id=$data_id' target='_new'>$file_name</a></td>
			<td class='auto-style1' align='center'>$newDate</td>
			<td class='auto-style1' align='center'><form action='download_file.php?data_id=$data_id' target='_new' method='post'><input type='submit' value='Download'></form></td>
			
		</tr>
	
	");
	
	
	$results = $results + 1;    
	$rs->MoveNext();
  }
 
 }
?>




</TABLE>
<p><b>*** Note: The File ID is randomly generated in the system to help in identifying the file in the database. ***</b></p>
<br><Br>
<?
	print '<center>';
	if ($p > 1) {
		$lastp=$p-1;
		print "<a href=file_history.php?p=$lastp><< Previous Page</a> ";
	}

	if (20 == $results) {
		$nextp=$p+1;
		print "<a href=file_history.php?p=$nextp> Next Page >></a>";
	}
	print '</center> <p><br><br><br><CENTER><A HREF="./index.php">Return to Main Menu</A></CENTER></p>';

	print_footer();

} // end check login

?>