<?php require "../_header.php";?>
<div id="main-content1" align="center">
<center><h1>Book Return Register Report</h1></center>
<?php
if($_REQUEST['registerid']=="" && $_REQUEST['bookdate_from']=="" && $_REQUEST['bookdate_to']=="")
{
?>
	<script language="javascript" type="text/javascript">
		location.href='report-form-book-return-register.php';
	</script>
<?php
}
elseif($_SESSION['aCollegeId']!="" && isset($_SESSION['aCollegeId']))
{ 
		$header_row_string = "Book Return Register Report";
		
		
		$register  = Escape($_REQUEST['registerid']);
		
		$date_from = Escape($_POST['bookdate_from']); print "<br/>";
	    $date_to   = Escape($_POST['bookdate_to']);
					
		if($register != ""){
		  	$condition .= " and lid.book_registerid = '$register'";
	    }
		                  
		if($date_from != "From"){
			$date_from=DateConversion(Escape($_POST['bookdate_from']),"Y-m-d","d/m/Y");
		    $condition .= " and issue_returndate >= '$date_from' ";
	     }
 	    if($date_to != "To"){
			$date_to=DateConversion(Escape($_POST['bookdate_to']),"Y-m-d","d/m/Y");
			$condition .= " and issue_returndate <= '$date_to' ";
	     }
		
		 $order_field="";	
	   	 $order_type="";
	   
	  	 $objIssueMgmt = new IssueMgmt();
	   	 $result = $objIssueMgmt -> GetDistincBookReturnDate("","",$condition,$order_field,$order_type);
		 
	   	if(dbRows($result)>0){	
		 ob_start(); //starting for 
					   
?>
<table width="70%" cellspacing="0" cellpadding="0">
	<thead>
	  <tr>
		<th style="text-align:center" width="15%">Book Reg.No</th>
		<th style="text-align:left" width="25%">Book Name</th>
		<th style="text-align:center" width="15%">Return Date</th>
		<th style="text-align:center" width="15%">Member Id</th>
		<th style="text-align:left" width="30%">Member Name</th>
	  </tr>
	</thead>
<?php	   	

		 while($row = dbArray($result)) { 
		        
				 $t = $row['issue_returndate'];
				
				$condition ="";
				
				$condition = " and issue_returndate = '$t' and issue_isreturned='1'"; 			    				
				
				if($register != ""){
		 		 	$condition .= " and lid.book_registerid = '$register'";
			    }
				
				 $qry = "SELECT lbm.book_registerno,
								lbm.book_name,
								DATE_FORMAT(issue_returndate,'$SQLDateFormat') As returndate,
								lim.member_id,						
								CASE WHEN(lim.member_type='1') 
									 THEN (
										SELECT CONCAT(student_firstname,' ',student_lastname,' ',student_surname) 
										FROM sms_studentmaster 
										WHERE school_id ='".$_SESSION["aCollegeId"]."' AND student_id=lim.member_id)
										
									WHEN(lim.member_type='2') 
									THEN (
										SELECT employee_name 
										FROM sms_employeemaster 
										WHERE school_id ='".$_SESSION["aCollegeId"]."' AND employee_id=lim.member_id)
										
									WHEN(lim.member_type='3') 
									THEN (
										SELECT member_name 
										FROM lms_membermaster 
										WHERE school_id ='".$_SESSION["aCollegeId"]."' AND member_id=lim.member_id)
								END AS member_name
		          		from 
								lms_bookmaster lbm ,
								lms_issuedetail lid,
								lms_issuemaster lim
						
					  where lbm.school_id ='".$_SESSION["aCollegeId"]."' AND 
							lid.school_id ='".$_SESSION["aCollegeId"]."' AND 
							lim.school_id ='".$_SESSION["aCollegeId"]."' AND
							
							lid.issue_id = lim.issue_id AND 						
							lbm.book_registerid = lid.book_registerid AND  
							lbm.book_id = lid.book_id"; 
													
                $qry .= $condition;
			    $qry .= "ORDER BY lim.member_id ASC" ; 
				$result1 = dbQuery($qry);
				//print "hi".dbRows($result);
				if(dbRows($result)>0) {
					while($row1=dbArray($result1))
					  {
							 
						  extract($row1) ;
					    ?>
						 <tr>
							<td style="text-align:center" width="10%"><?php echo $book_registerno;?> </td>
							<td style="text-align:left" width="30%"><?php echo $book_name;?></td>
							<td style="text-align:center" width="15%"><?php echo $returndate;?></td>
							<td style="text-align:center" width="15%"><?php echo $member_id;?></td>
							<td style="text-align:left" width="30%"><?php echo $member_name;?></td>
						  </tr> 
				<?php 	  }
					}
				
				
		}
	
?>
</table>
		<?php	 
				  
	                $HtmlCode= ob_get_contents();
			        ob_end_flush();   
			     	$HtmlUrl 	=	"book-return-register-report-". $_SESSION["aUserId"].".html";
					$HtmlPath 	=	"../report/".$HtmlUrl;
					
					createHTMLReportPage($header_row_string,$HtmlUrl,$HtmlPath,$HtmlCode);  ?>
		
<br/>
<form name="frm" id="frm" action="" method="post">		
	<input type="hidden" name="txtFile" id="txtFile" value="<?php echo $HtmlUrl ;?>" />
	<input type="button" name="btnPdf" id="btnPdf" value="Create PDF" onclick="SetPageAction('PDF');"/>
	<input type="button" name="btnHtml" id="btnHtml" value="Create HTML" onclick="SetPageAction('HTML');"/>
	<input type="button" name="btnWord" id="btnWord" value="Create WORD" onclick="SetPageAction('WORD');"/>
	<input type="button" name="btnExcel" id="btnExcel" value="Create EXCEL" onclick="SetPageAction('Excel');"/>
</form>
<script language="javascript" type="text/javascript">
	function SetPageAction(varAction) {
		if(varAction=="PDF"){			
			document.frm.action="../convertpdf/create-pdf.php";
			document.frm.target = "_blank";
			document.frm.submit();
		}
		else if(varAction=="HTML"){
			document.frm.action="<?php echo $HtmlPath ;?>";
			document.frm.target = "_blank";
			document.frm.submit();
		}
		else if(varAction=="WORD"){
			document.frm.action="../convertword/create-word.php";
			document.frm.target="_blank";
			document.frm.submit();
		}
		else if(varAction=="Excel"){
			document.frm.action="../convertword/create-excel.php";
			document.frm.target="_blank";
			document.frm.submit();
		}
	}
</script>
		<?php
		} else
			{
				
				echo "<br/><b>No Data Found</b>";
			}
		
}else {
       echo "Session Expired"; 
      }
?>
</div>
<?php require "../_footer.php";?>