<?php
ob_start();
include('includes/session.inc');
$Title = _('Banking Batch');
include('includes/SQL_CommonFunctions.inc');

include('includes/newheader.php');
?> 


      <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jszip-2.5.0/dt-1.10.16/b-1.5.1/b-flash-1.5.1/b-html5-1.5.1/b-print-1.5.1/datatables.min.css"/>
 <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css"/>
 <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"/>
 
   <script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
	<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script>
	<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>


<?php

	
//collect reasons for cancelation


					$userid = $_SESSION['UserID'];

echo '<div class="col-sm-12">';


?>
Banking for North

<button id="alltrans" onclick="printData()">Print</button>
<div class="table-responsive" id = 'Report'>
 <table id="example" class="table ">
 <thead> 
                    <tr>
					
                      <th>Receipt No:</th>
						

                      <th>Tran Date</th>
					  

                      <th>Customer</th>
						<th>A/c No</th>

                      <th>Payment Method</th>

                     <th>Amount</th>
						 <th >Drawers Bank/Branch</th>
						 <th >Drawers Account Number</th>
						<th >Drawers Cheque Number</th>
						<th >Reference</th>
                     </tr>
                    
                 </thead>   
	 	<tfoot align="right">
		<tr><td class="grandTotal"></td><td class="grandTotal"></td><td class="grandTotal"></td><td class="grandTotal"></td><td class="grandTotal"></td><td class="grandTotal"></td><td class="grandTotal"></td><td class="grandTotal"></td><td class="grandTotal"></td></tr>
	</tfoot>

                  
<?php

										$sqlbalances="SELECT debtortrans.transno, debtortrans.trandate,debtortrans.account_number, debtortrans.reference,debtortrans.ovamount,debtortrans.payment_method,debtortrans.drawer_bank,debtortrans.drawer_accountnumber,debtortrans.drawer_chqnumber,debtorsmaster.name
					FROM debtortrans
					INNER JOIN debtorsmaster on debtorsmaster.debtorno = debtortrans.debtorno
					
					WHERE debtortrans.type = 12
					AND debtortrans.receipt_banked = 0
					AND debtortrans.department = 31
					ORDER By debtortrans.transno DESC 
					";
		$resultfromdebtoraccount =DB_query($sqlbalances);
	 
	
while ($myrowfromdebtoraccount = DB_fetch_array($resultfromdebtoraccount)){



	
          		
          		echo 		
					'
					 <tr"><td>' . $myrowfromdebtoraccount['transno'].'</td>
					
							
					<td>' . $myrowfromdebtoraccount['trandate']. '</td>
					<td>' . $myrowfromdebtoraccount['name']. '</td>
					<td>' . $myrowfromdebtoraccount['account_number']. '</td>
					<td>' . $myrowfromdebtoraccount['payment_method']. '</td>
					<td>' . -$myrowfromdebtoraccount['ovamount']. '</td>
					<td>'.$myrowfromdebtoraccount['drawer_bank'].'</td>
					<td>'.$myrowfromdebtoraccount['drawer_accountnumber'].'</td>
					<td>'.$myrowfromdebtoraccount['drawer_chqnumber'].'</td>
					<td>'.$myrowfromdebtoraccount['reference'].'</td>
			    
				
					</tr>';
		
		
		
		$count++;
	
	
	
		
	
	
			//$ac[]=	$accountnumber;
				//$cust=$AgedAnalysis['name'];
				
				//$bal[]=$finalbalance;
				//$cur[] = $finalcurrent;
			//	$thirty[]= $final30days;
			//	$sixty[]= $final60days;
			//	$ninety[]= $final90daysplus;
				
				
				// $data_arr[] = array("customer" =>$cust,"account" =>$accountnumber,"bal" =>$finalbalance,"cur" =>$finalcurrent,"thirty" =>$final30days,"sixty" =>$final60days,"ninety" =>$final90daysplus);
		}
				?>						
				

	</table></div>




<?php


echo '<p> </p> <p></p><p class="page_title_text"><img alt="" src="'.$RootPath.'/css/'.$Theme.
		'/images/printer.png" title="' .// Icon image.
		_('Reasons for Cancellation .  ') . '" /> ' .// Icon title.
		_('Please this process needs to be done by the Finance Team in charge of banking') . '</p>';// Page title.
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
	echo '<div class="col-sm-12">';// div class=?
	echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<table id= "firsttable" class="table">';

	echo '<tr>

		<td>' . _('Date when it will be banked') . ':</td>';

echo '<td>   <input  type="text" name="order_date" id="datepicker"  placeholder="" required = "required" /></td>

      </tr>';
$SQL = "SELECT bankaccountname,
				bankaccounts.accountcode,
				bankaccounts.bankaccountnumber,
				bankaccounts.bank_name
		FROM bankaccounts
				
		WHERE accountcode = '1100000'";

$ErrMsg = _('The bank accounts could not be retrieved because');
$DbgMsg = _('The SQL used to retrieve the bank accounts was');
$AccountsResults = DB_query($SQL,$ErrMsg,$DbgMsg);


echo '
	<tr>
		<td>' . _('Bank Account') . ':</td>
		 <td><select tabindex="1" autofocus="autofocus" name="BankAccount" onchange="ReloadForm(form1.BatchInput)" required = "required">';


	
	while ($myrow=DB_fetch_array($AccountsResults)){
		/*list the bank account names */
	
			echo '<option value="' . $myrow['accountcode'] . '">' . $myrow['bankaccountnumber'].'-'. $myrow['bankaccountname']. ' - ' . $myrow['bank_name'] . '</option>';
		
	}
	echo '</select></td>
		</tr>';

echo '<tr>
			<td>' ._('Reference'). ':</td>
			<td><input tabindex="'.$j.'" type="text" maxlength="200" size="50" required="required" name="reason"  /></td>
		</tr></table></form>';
echo '<input type="submit" name="process" id="process" value="' ._('After printing please click here to Process Banking batch') . '" />';

$reasons = trim($_POST["reason"]);

$sqldate= FormatDateForSQL(DB_escape_string($_POST['order_date'] ));

$bankedinto =$_POST["BankAccount"];

if (isset($_POST['process']) ){
	
	
		
	$SQL= "SELECT bank_name,
	           bankaccountname,
				bankaccountnumber
			FROM bankaccounts 
			WHERE accountcode='" . $bankedinto. "'
			";

	$ErrMsg = _('An error occurred getting the header information about the receipt batch number') ;
	$DbgMsg = _('The SQL used to get the receipt header information that failed was');
	$Result=DB_query($SQL,$ErrMsg,$DbgMsg);


	/* OK get the row of receipt batch header info from the BankTrans table */
	$myrow = DB_fetch_array($Result);
	
	$bankname = $myrow['bank_name'];
	$bankaccountname = $myrow['bankaccountname'];
	$bankaccountnumber= $myrow['bankaccountnumber'];
	
	
	

		 
		$sqlbalances1="SELECT debtortrans.transno, debtortrans.trandate,debtortrans.account_number, debtortrans.reference,debtortrans.ovamount,debtortrans.payment_method,debtortrans.drawer_bank,debtortrans.drawer_accountnumber,debtortrans.drawer_chqnumber,debtorsmaster.name
					FROM debtortrans
					INNER JOIN debtorsmaster on debtorsmaster.debtorno = debtortrans.debtorno
					WHERE debtortrans.type = 12
					AND debtortrans.receipt_banked = 0
					AND debtortrans.department = 31
					ORDER By debtortrans.transno DESC 
					
					";
		$resultfromdebtoraccount1 =DB_query($sqlbalances1);

		 
		 
	
			 
			 while ($myrowfromdebtoraccount1 = DB_fetch_array($resultfromdebtoraccount1)){
			
			 
			 
		 //update database

		
		
			 
			 
	
	
			 
		$SQL="INSERT INTO banktrans (type,
								transno,
								client_account_number,
								bankact,
								ref,
								exrate,
								
								transdate,
								banktranstype,
								amount,
								currcode,
								drawer_bank,
								drawer_accountnumber,
								drawer_chqnumber,
								drawer_chqdate
								)
		VALUES (
			12,
			'" . $myrowfromdebtoraccount1['transno']. "',
			'" . $myrowfromdebtoraccount1['account_number']. "',
			'" .  $bankedinto. "',
			'" .$reasons . "',
			'1',
			
			'" .$sqldate . "',
			'" .$myrowfromdebtoraccount1['payment_method']. "',
			'" . -$myrowfromdebtoraccount1['ovamount'] . "',
			'FJD',
			'" . trim($myrowfromdebtoraccount1["drawer_bank"]). "',
			'" . trim($myrowfromdebtoraccount1["drawer_accountnumber"]). "',
			'" . trim($myrowfromdebtoraccount1["drawer_chqnumber"]). "',
			'" . trim($myrowfromdebtoraccount1["drawer_chqdate"]). "'
		)";
	$DbgMsg = _('The SQL that failed to insert the bank account transaction was');
	$ErrMsg = _('Cannot insert a bank transaction');
	$result = DB_query($SQL,$ErrMsg,$DbgMsg,true);
			 
		
	$sql4012 =  "UPDATE debtortrans set receipt_banked = '1'
				WHERE transno = '" . $myrowfromdebtoraccount1["transno"]. "'
				AND type = 12
				";

	$ErrMsg = _('This customer could not be added because');
			$result4012 = DB_query($sql4012,$ErrMsg);
	
	
	
			 }
	
	
	unset($_POST['order_date']);
	unset($_POST['BankAccount']);
	unset($_POST['reason']);
	$message = "This banking batch banked. Status has now changed to banked.";
	echo "<script type='text/javascript'>alert('$message');</script>";
		echo "<script type='text/javascript'>javascript:history.go(-2)</script>"; 
	exit();

}

		
			    
		
	


//include('includes/footer.inc'); 

?>

  <script>


 
$(document).ready(function() {
      var table = $('#example').DataTable( {
		  
		  
        dom: 'Bfrtip',
	    order: [[ 7, 'desc' ]],
		  
		  
        buttons: [
           
	
			{
                extend: 'excelHtml5'},
		
			
			
		],
	    paging: 'false',
	   aLengthMenu: [
        [25, 50, 100, 200, -1],
        [25, 50, 100, 200, "All"]
    ],
    iDisplayLength: -1,
		  
        "drawCallback": function ( settings ) {
            var api = this.api();
            var rows = api.rows( {page:'all'} ).nodes();
            var last=null;
            
            // Remove the formatting to get integer data for summation
	          var intVal = function ( i ) {
	          	return typeof i === 'string' ?
			          i.replace(/[\$,]/g, '')*1 :
			          typeof i === 'number' ?
				               i : 0;
	          };
          
            total=[];
            api.column(7, {page:'all'} ).data().each( function ( group, i ) {
              group_assoc=group.replace(' ',"_");
              console.log(group_assoc);
              if(typeof total[group_assoc] != 'undefined'){
            total[group_assoc]=total[group_assoc]+intVal(api.column(5).data()[i]);
        }else{
            total[group_assoc]=intVal(api.column(5).data()[i]);
        }
              if ( last !== group ) {
                    $(rows).eq( i ).before(
                        '<tr class="group"><td colspan="5">Drawer Account Number-'+group+'</td><td class=" '+group_assoc+'"></td>  </tr>'
                    );
 
                    last = group;
                }
            } );
          for(var key in total) {
        $("."+key).html("$"+total[key].toFixed(2));
    }
        },
		   
	
	   
	   
    } );
 
   
} );
	  

 
	function printData()
{
   var divToPrint=document.getElementById('Report');
   newWin= window.open('');
   newWin.document.write(divToPrint.innerHTML);
   newWin.print();
   newWin.close();
}
 

	  
	  

  </script>	
