<?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();
 
	 if($_REQUEST['Reconcillation'] )
	 {
		/*echo "<pre>";
		print_r($_REQUEST['Bid']);
		echo "<br />---<br />";
		print_r($_REQUEST['main_id']);
		echo "<br />---<br />";
		print_r($_REQUEST['recon_id']);*/
		foreach($_REQUEST['recon_id'] as $key=>$val)
		{
			$dataArray['bank_reconcillation_status']=1;
			$dataArray['bank_reconcillation_date']=FormatDateForSQL($_REQUEST['bank_reconcillation_date'][$key]);  
			$cond="id='".$val."'";
	 		$s->RecordEdit('account_book_details', $dataArray, $cond);
			
			$update_date=DB_query("Update account_book_details set bank_reconcillation_date='".FormatDateForSQL($_REQUEST['bank_reconcillation_date'][$key])."', reconciled_by=".$_SESSION['UserID']." where account_bookid=".$_REQUEST['main_id'][$key]."",$db);
		}
		if($_REQUEST['ledgerId']!=''){
		$s->pageLocation('index.php?route=Account/Bank_Reconcillation&GO=1&ledgerId='.$_REQUEST['ledgerId']."&from_date=".$_REQUEST['from']."&to_date=".$_REQUEST['to'].$rurl);}
		else{
		$s->pageLocation('index.php?route=Account/Bank_Reconcillation&GO=1&from_date='.$_REQUEST['from'].'&to_date='.$_REQUEST['to'].$rurl);
		}
  	 }
	 
?>
<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div"> 
<h1>Bank Reconciliation</h1>

 <form action="" method="post">
 <table width="100%" border="0" cellpadding="3" cellspacing="0">
 <tr class="row1">
	 <td width="13%" align="right">From Date</td>
	 <td width="20%"><input type="text" name="from_date" value="<?php if($_REQUEST['from_date']!='') { echo $_REQUEST['from_date']; } else { echo ConvertSQLDate($_SESSION['financialYrStart']); } ?>" class="datepicker"  /></td>
	 <td width="13%" align="right">To Date</td>
	 <td width="20%"><input type="text" name="to_date" value="<?php if($_REQUEST['to_date']!='') { echo $_REQUEST['to_date']; } else { echo ConvertSQLDate($_SESSION['financialYrEnd']); } ?>" class="datepicker"  /></td>
	 <td width="13%" align="right">Ledger</td>
	 <td><select name="ledgerId" id="ledgerId"  onchange="ShowDiv(this.value,'hid-Bankdetails','ACBillbookBankDetails.php')">
	 <option value="">Please Select Ledger</option>
	 <?php 
	 $ROW=DB_fetch_array(DB_query("SELECT GroupId FROM accountgroupmaster WHERE GroupName REGEXP '^bank account'",$db));
	 $group_id=$ROW['GroupId'];
	 if($group_id!='')
	 {
	 $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="6" style="text-align:center;"><input name="GO" type="submit" value="Search" class="search" onclick="return validate();" /></td>
 </tr>
 </table>
</form>
	<?php if($_REQUEST['GO'] || $_REQUEST['GO']=='1')
	{

		$from_date=ConvertSQLDate($_REQUEST['from_date']);
		$todate=ConvertSQLDate($_REQUEST['to_date']);
		if($_REQUEST['from_date']!='' && $_REQUEST['to_date']!='')
		{
			$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 B.bank_reconcillation_status!=1 and A.VPrefix in(3,4) and  $search1 group by A.id order by A.Dates",$db);
		}
	
	 
	?>
	<br>
<form action="" method="post">	
<!-- <p align="center"><input name="Reconcillation" type="submit" value="Reconciliate" onclick="return checkDates();" class="search" /></p> -->
<table width="100%" border="0" cellspacing="0" cellpadding="3" class="thborder">
	<tr>
		<th width="6%">Date</th>
		<th>Particulars</th>
		<th width="10%">Vch Type</th>
		<th width="10%">Transaction Type</th>
		<th width="10%">Instrument No.</th>
		<th width="9%">Instrument Date</th>
		<th width="8%">Bank Date</th>
		<th width="9%" class="text-center">Debit</th> 
		<th width="9%" class="text-center">Credit</th>
		<th width="8%" class="text-center">Reconciliate</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);
	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){
	  
	  $name=$s->getField('accountledger','AccountsID',$row1['ledgerid'],'AccountName');
	  $pos1=strpos($name,'Bounced'); $pos2=strpos($name,'bounced');
	  if($pos1=='' && $pos2==''){
  ?>	
	<input type="hidden" name="from" value="<?php echo $_REQUEST['from_date'];?>" />
	<input type="hidden" name="to" value="<?php echo $_REQUEST['to_date'];?>" />
  <tr id="check_id_<?php echo $i;?>">
  	<td><?php echo ConvertSQLDate($row['Dates']); echo "&nbsp;";  //echo "<br />".$queryLedger."<br />";//echo $query1; 
  		//echo $row1['bank_reconcillation_status'];  ?></td>    
    <td><?php echo $name=$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><input name="bank_reconcillation_date[<?php echo $i;?>]" type="text" class="datepicker" value="<?php echo $bank_reconcillation_date;?>" size="9" id="bank_reconcillation_date_<?php echo $i;?>" onkeypress="validatenumber(event)"  /></td>
    <td style="text-align:right;"><?php echo ($row2['amount_type']=='DR'?$row2['amount']:0); ?></td>
    <td style="text-align:right;"><?php echo ($row2['amount_type']=='CR'?$row2['amount']:0); ?></td>
	<td style="text-align:center"><input type="checkbox" name="recon_id[<?php echo $i;?>]" id="recon_id_<?php echo $i;?>" value="<?php echo $row1['id']; ?>" /> 
	<input name="Bid1[<?php //echo $i;?>]" type="hidden" value="<?php echo $row1['id']; ?>" />
  	<input name="main_id[<?php echo $i;?>]" type="hidden" value="<?php echo $row['id']; ?>" />
  	<input name="ledgerId1[<?php echo $i;?>]" type="hidden" value="<?php echo $_REQUEST['ledgerId']; ?>" />
  	<input name="GO" type="hidden" value="1" /></td>
  </tr><?php $i++;}}}}?>
  <input type="hidden" name="count" id="count" value="<?php echo $i;?>" />
</table>
<p align="center"><input name="Reconcillation" type="submit" value="Reconciliate" onclick="return checkDates();" class="search" /></p>
</form>
 <?php } ?>
 <script language="javascript" type="text/javascript">
 function checkDates(i)
 {
	var count=document.getElementById("count").value;
	for(var i=0; i<count; i++)
	{
		var trobject = document.getElementById('check_id_'+i);
		if(document.getElementById("recon_id_"+i).checked==true)
		{
			if(document.getElementById("bank_reconcillation_date_"+i).value=='')
			{
				alert('Please enter the reconciliation date for all cheques to be reconciled');
				trobject.style.backgroundColor= "pink";
				document.getElementById("bank_reconcillation_date_"+i).focus();
				return false;
			}
		}
		
		if(document.getElementById("bank_reconcillation_date_"+i).value!='')
		{
			if(document.getElementById("recon_id_"+i).checked==false)
			{
				alert('Please select the check boxes for all cheques to be reconciled');
				trobject.style.backgroundColor= "brown";
				document.getElementById("recon_id_"+i).focus();
				return false;
			}
		}
	}
 }

    function validatenumber(event)
    {
		var charCode = event.keyCode;
         //Non-numeric character range
         if (charCode > 31 && (charCode < 48 || charCode > 57))
         return false;
    }
  
  function validate()
  {
  	var ledger=$("#ledgerId").val();
  	if(ledger=='')
  	{
  		alert("Please Select Ledger");
  		return false;
  	}
  }

 </script>
 </div>
 </div>