<?php 
include('includes/Class_Branch.php');
include('includes/AccountGroupMaster_Class.php');
$modid=$_REQUEST['modid'];
$frmid=$_REQUEST['frmid'];
$action=$_REQUEST['action'];
$daction=$_REQUEST['daction'];
$rurl="&modid=".$modid."&frmid=".$frmid;
$msgErr=array();
?>
<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div">
 <form action="" method="post">
 <table width="100%" border="0" cellpadding="3" cellspacing="0">
 <tr class="row1">
 <td width="8%" align="right">Type</td>
 <td width="18%"><select name="typebank" id="typebank" >
 		<option value="Bank"<?php echo($_REQUEST['typebank']=='Bank'?'selected':''); ?>>Bank</option>
		<option value="Voucher"<?php echo($_REQUEST['typebank']=='Voucher'?'selected':''); ?>>Voucher</option>
 </select></td>
 <td width="9%" align="right">From</td>
 <td width="15%"><input type="text" name="from_date" value="<?php echo $_REQUEST['from_date'] ?>" class="datepicker" /></td>
 <td width="8%" align="right">To</td>
 <td width="15%"><input type="text" name="to_date" value="<?php echo $_REQUEST['to_date'] ?>" class="datepicker" /></td>
 <td width="8%" align="right">Ledger</td>
 <td><select name="ledgerId" id="ledgerId" onchange="ShowDiv(this.value,'hid-Bankdetails','ACBillbookBankDetails.php')">
 <option value="">ALL</option>
 <?php 
 $ROW=DB_fetch_array(DB_query("SELECT GroupId FROM accountgroupmaster WHERE GroupName REGEXP '^bank account'",$db));
 $group_id=$ROW['GroupId'];
 $getLedgers=DB_query("Select * from accountledger where UnderGroup=$group_id",$db);
 while($ledgerRow=DB_fetch_array($getLedgers))
 {?>
 	<option value="<?php echo $ledgerRow['AccountsID'];?>" <?php if($_REQUEST['ledgerId']==$ledgerRow['AccountsID']) {echo "selected";}?>><?php echo $ledgerRow['AccountName'];?></option>
 <?php 
 }
 ?>
 </select></td>
 </tr>
 <tr class="row1">
 <td colspan="8" align="center"><input name="GO" type="submit" value="Search" class="search" /></td>
 </tr>
 </table>

</form>
	<?php if($_REQUEST['GO']){
		$from_date=ConvertSQLDate($_REQUEST['from_date']);
		$todate=ConvertSQLDate($_REQUEST['to_date']);
		if($_REQUEST['from_date']!='' && $_REQUEST['to_date']!='' && $_REQUEST['typebank']=='Bank')
		{
			$search[]="B.bank_reconcillation_date between '".$from_date."' and '".$todate."'";
		}
		if($_REQUEST['from_date']!='' && $_REQUEST['to_date']!='' && $_REQUEST['typebank']=='Voucher')
		{
			$search[]="A.Dates between '".$from_date."' and '".$todate."'";
		}
		if($_REQUEST['ledgerId']!='')
		{
			$search[]="B.ledgerid='".$_REQUEST['ledgerId']."'";
		}
	 	if($search!='')
		{
			$search1=implode(" and ",$search);			
  $query=DB_query("select A.*, B.id as Bid from account_book A, account_book_details B 
  where A.id=B.account_bookid and $search1 group by A.id order by A.Dates",$db);
		}
		else
		{
		 $query=DB_query("select A.*, B.id as Bid from account_book A, account_book_details B 
  where A.id=B.account_bookid  group by A.id order by A.Dates",$db);
		}
	?>
<br />
<table width="100%" border="0" cellpadding="3" cellspacing="0" class="thborder">
<tr>
	<th>Date</th>
	<th>Particulars</th>
	<th>Vch Type </th>
	<th>Transaction Type </th>
	<th>Instrument No. </th>
	<th>Instrument Date </th>
	<th>Bank Date </th>
	<th>Debit</th> 
	<th>Credit</th>
	<th>Status</th>
	<th>Action</th>
</tr>
  <?php $i=0;
  //echo "select *,B.id as Bid from account_book_details B,account_book A where A.id=B.account_bookid and B.ledgerid !='".$_REQUEST['ledgerId']."' and B.bank_reconcillation_status!=1";
  //$query=DB_query("select *,B.id as Bid from account_book_details B,account_book A where A.id=B.account_bookid and B.ledgerid ='".$_REQUEST['ledgerId']."' and B.bank_reconcillation_status!=1",$db);
	$arr_DR='';
	$arr_CR='';
	$pending_DR='';
	$pending_CR='';
	$cleared_DR='';
	$cleared_CR='';
	$i=0;
	while($row=DB_fetch_array($query)){
	  $BillBookType=  $s->getField('voucher_master','VoucherType',$row['VPrefix'],'VPrefix');
	  if($BillBookType=='BP' || $BillBookType=='BR'){
	  if($i%2!=0)
	  $bg="#CCCCCC";
	  
	  
	   if($BillBookType=='BR')
	  {
	  	$queryLedger="Select * from account_book_details where amount_type='CR' and account_bookid=".$row['id']." limit 0,1";
		$queryAmount="Select * from account_book_details where amount_type='DR' and account_bookid=".$row['id']." limit 0,1";
	  }
	  elseif($BillBookType=='BP')
	  {
	  	$queryLedger="Select * from account_book_details where amount_type='DR' and account_bookid=".$row['id']." limit 0,1";
		$queryAmount="Select * from account_book_details where amount_type='CR' and account_bookid=".$row['id']." limit 0,1";
	  }
	  
	  $result1=DB_query($queryLedger,$db);
	  $row1=DB_fetch_array($result1);
	  
	  $result2=DB_query($queryAmount,$db);
	  $row2=DB_fetch_array($result2);
	  
	  
	  
	  
	  if($row1['bank_reconcillation_status']=='1' && $row1['check_status']!='Y' ){$bgcolor='#A293AE';} 
	  else if($row1['check_status']=='Y' &&  $row1['bank_reconcillation_status']=='1') { $bgcolor='#dedede'; }
	  else if($row1['check_status']=='Y' &&  $row1['bank_reconcillation_status']=='0') { $bgcolor='#dedede'; }
	  else{$bgcolor='#FFFFFF';}
	  
	 
	  
	  $name=$s->getField('accountledger','AccountsID',$row1['ledgerid'],'AccountName');
	  $pos1=strpos($name,'Bounced'); $pos2=strpos($name,'bounced');
	  if($pos1=='' && $pos2==''){
  ?>
  <tr  bgcolor="<?php //echo $bgcolor?>" id="group_<?php echo $i; ?>">
  	<td><?php echo ConvertSQLDate($row['Dates']);  //echo "&nbsp;"; echo $query1;?></td>    
    <td><?php  echo $s->getField('accountledger','AccountsID',$row1['ledgerid'],'AccountName');;?></td>
 	<td><?php echo $BillBookType=  $s->getField('voucher_master','VoucherType',$row['VPrefix'],'GroupName'); ?></td>
    <td><?php echo $row['transation_type'];?></td>
    <td><?php echo ($row['banks_checkno']); ?></td>
   	<td><?php echo   ConvertSQLDate($row['banks_checkdate']); ?></td>
	<td><?php if($row1['bank_reconcillation_date']!='')echo ConvertSQLDate($row1['bank_reconcillation_date']); ?></td>
    <td><?php echo ($row2['amount_type']=='DR'?$row2['amount']:0); 
	if($row2['amount_type']=='DR'){$arr_DR[]=$row2['amount'];}
	if($row2['amount_type']=='DR' && $row1['bank_reconcillation_status']!='1'){$pending_DR[]=$row2['amount'];}
	if($row2['amount_type']=='DR' && $row1['bank_reconcillation_status']=='1'){$cleared_DR[]=$row2['amount'];} ?></td>
    <td><?php 
	echo ($row2['amount_type']=='CR'?$row2['amount']:0); 
	if($row2['amount_type']=='CR'){$arr_CR[]=$row2['amount'];}
	if($row2['amount_type']=='CR' && $row1['bank_reconcillation_status']!='1'){$pending_CR[]=$row2['amount'];}
	if($row2['amount_type']=='CR' && $row1['bank_reconcillation_status']=='1'){	$cleared_CR[]=$row2['amount'];}
	?></td>
	<td><?php 
	if($row1['bank_reconcillation_status']=='1'){echo "<b>Cleared</b>";} else {echo "Pending";}?></td>
	<td><input type="checkbox" name="check[]" value='<?php echo $row1['id']; ?>' onclick="showdata(this.value,<?php echo $i; ?>,<?php echo $row1['bank_reconcillation_status']; ?>)"  <?php if($row1['check_status']=='Y') { ?> checked="checked" <?php } ?> />
	
  </tr><?php $i++; }}}?>
  <tr>
  	<td></td>
    <td>&nbsp;</td>
 	<td>&nbsp;</td>
    <td>&nbsp;</td>
   	<td colspan="3"><strong>Balance as per company Books</strong></td>
	<?php if($arr_DR!=''){$sum_DR=array_sum($arr_DR);}else{$sum_DR=0;} 
	if($arr_CR!=''){$sum_CR=array_sum($arr_CR);}else{$sum_CR=0;} 
	if($sum_DR>$sum_CR)
	{$balance_DR=$sum_DR-$sum_CR;}
	elseif($sum_CR>$sum_DR){$balance_CR=$sum_CR-$sum_DR;}
	elseif($sum_DR==$sum_CR){$balance_DR=0; $balance_CR=0;}
	?>
   	<td><strong><?php //echo "<pre>";print_r($arr_DR);echo "<br /><br />".$sum_DR; echo "<br />"; echo $sum_CR; 
	echo $balance_DR;?></strong></td>
    <td><strong><?php echo $balance_CR;?></strong></td>
	<td>&nbsp;</td>
	<td>&nbsp;</td>
  </tr>
   <tr>
  <td></td>
    <td>&nbsp;</td>
 	<td>&nbsp;</td>
    <td>&nbsp;</td>
   	<td colspan="3"><strong>Amounts not reflected in bank</strong></td>
   	<td><strong><?php if($pending_DR!='')echo array_sum($pending_DR);?></strong></td>
    <td><strong><?php if($pending_CR!='')echo array_sum($pending_CR);?></strong></td>
	<td>&nbsp;</td>
	<td>&nbsp;</td>
  </tr>
  <tr>
  <td></td>
    <td>&nbsp;</td>
 	<td>&nbsp;</td>
    <td>&nbsp;</td>
   	<td colspan="3"><strong>Balance as per bank</strong></td>
	<?php 
	
	if($cleared_DR!=''){
	$sumClear_DR=array_sum($cleared_DR);}else{$sumClear_DR='';} 
	if($cleared_CR!=''){
	$sumClear_CR=array_sum($cleared_CR);}else{$sumClear_CR='';}
	if($sumClear_DR>$sumClear_CR){$balanceClear_DR=$sumClear_DR-$sumClear_CR; $balanceClear_CR='';}elseif($sumClear_CR>$sumClear_DR){$balanceClear_CR=$sumClear_CR-$sumClear_DR; $balanceClear_DR='';}
	?>
   	<td><strong><?php if($balanceClear_DR!='')echo $balanceClear_DR;?></strong></td>
    <td><strong><?php if($balanceClear_CR!='')echo $balanceClear_CR;?></strong></td>
	<td>&nbsp;</td>
	<td>&nbsp;</td>
  </tr>
</table>
 <?php } ?>
 
 <script >
 function showdata(str,i,j)
 {
 if (window.XMLHttpRequest)
	{// code for IE7+, Firefox, Chrome, Opera, Safari
		xmlhttp=new XMLHttpRequest();
	}
	else
	{// code for IE6, IE5
		xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
	}
	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
		var data=xmlhttp.responseText.split("__");
		if(data[1]=='N' && j==1)
		document.getElementById("group_"+i).style.background ='#A293AE';		
		else if(data[1]=='N' && j==0)
		document.getElementById("group_"+i).style.background ='#FFFFFF';
		else if(data[1]=='Y' && j==0)
		document.getElementById("group_"+i).style.background ='#dedede';
		else if(data[1]=='Y' && j==1)
		document.getElementById("group_"+i).style.background ='#dedede';
		}
	}
 	xmlhttp.open("GET","bank_recon.php?CA="+str,true);
	xmlhttp.send();
 
 
 
 }
 </script>
 </div>
 </div>