<?php
include ('includes/session.inc');
include('includes/SQL_CommonFunctions.inc');


if (isset($_GET['ref'])){
	$ref = $_GET['ref'];
}
if (isset($_GET['date'])){
	$date = $_GET['date'];
}
	
//collect reasons for cancelation


					$userid = $_SESSION['UserID'];

include('includes/newheader.php');


	
$sqlbalances="SELECT *
					FROM banktrans 
					WHERE ref = '" . $ref. "'
					Or banktranstype = 'Cash' 
					Or banktranstype = 'Cheque'
					
					Order by banktransid desc";
		$resultfromdebtoraccount =DB_query($sqlbalances);
$myrowbeg =  DB_fetch_array($resultfromdebtoraccount);

	$SQL= "SELECT bank_name,
	           bankaccountname,
				bankaccountnumber
			FROM bankaccounts 
			WHERE accountcode='" . $myrowbeg['bankact']. "'
			";

	$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'];

$sqldate = $date;
$reasons = $ref;
	
	
if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
//include_once('Classes/PHPExcel.php');


include_once('Classes/PHPExcel/IOFactory.php');

$inputFileName = 'printing/reprintwormaldbanking.xlsx';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);


/*check point*/

// Read the existing excel file
//$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(false);
$objPHPExcel = $objReader->load($inputFileName);
	
	$empty = '';
	
	  $objPHPExcel->getActiveSheet()->setCellValue('D3', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('D4',  $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('D5', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('D6', $empty );
	$objPHPExcel->getActiveSheet()->setCellValue('G4', $empty );
//$objPHPExcel->getActiveSheet()->setCellValue('G6', $empty );
	$cellcount = 9;	
	
	//$lastrecord= $cellcount + $_POST["total_item"];
	
while ($cellcount < 500 ) {
	
		
	
		
		
	
		
	    $objPHPExcel->getActiveSheet()->setCellValue('A'.$cellcount, $empty);
		
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$cellcount, $empty);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$cellcount, $empty);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$cellcount, $empty);
	//$objPHPExcel->getActiveSheet()->setCellValue('J'.$cellcount, $empty);
	
		$cellcount++;
		
			
	
	
}
	
	

	
	
        $objPHPExcel->getActiveSheet()->setCellValue('D3', $sqldate);
		$objPHPExcel->getActiveSheet()->setCellValue('D4', $bankname);
		$objPHPExcel->getActiveSheet()->setCellValue('D5', $bankaccountnumber);
		$objPHPExcel->getActiveSheet()->setCellValue('D6', 'Evergreen Int(Fiji) Ltd');
	$objPHPExcel->getActiveSheet()->setCellValue('G4', $ref );
	//$objPHPExcel->getActiveSheet()->setCellValue('G6', $totalamount);
	$count=0;
	$cellcount = 9;

$sqlbalances1= "SELECT 
					banktrans.transno,
					banktrans.client_account_number,
					banktrans.banktranstype,
					banktrans.amount,
					banktrans.drawer_bank,
					banktrans.drawer_accountnumber,
					banktrans.drawer_chqnumber,
					debtoraccount.billing_name
				FROM banktrans INNER JOIN debtoraccount
				ON banktrans.client_account_number=debtoraccount.account_number
				WHERE banktrans.ref ='" . $ref. "'
				AND banktrans.type = 12
				
				
				";
		$resultfromdebtoraccount1 =DB_query($sqlbalances1);
$totalamount = 0;
	while ($myrowdetail =  DB_fetch_array($resultfromdebtoraccount1)) {
	//before canceling this invoice unallocate invoices if there are any allocations

	  
			  $objPHPExcel->getActiveSheet()->setCellValue('A'.$cellcount,$myrowdetail["transno"]);
			   $objPHPExcel->getActiveSheet()->setCellValue('B'.$cellcount,$sqldate);
			 
	          $objPHPExcel->getActiveSheet()->setCellValue('C'.$cellcount,$myrowdetail["billing_name"]);
			   $objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount,$myrowdetail["client_account_number"]);
	 $objPHPExcel->getActiveSheet()->setCellValue('E'.$cellcount,$myrowdetail["banktranstype"]);
			   $objPHPExcel->getActiveSheet()->setCellValue('F'.$cellcount,$myrowdetail["amount"]);
	 $objPHPExcel->getActiveSheet()->setCellValue('G'.$cellcount,$myrowdetail["drawer_bank"]);
			   $objPHPExcel->getActiveSheet()->setCellValue('H'.$cellcount,$myrowdetail["drawer_accountnumber"]);
	 $objPHPExcel->getActiveSheet()->setCellValue('I'.$cellcount,$myrowdetail["drawer_chqnumber"]);
			
	  //$objPHPExcel->getActiveSheet()->setCellValue('J'.$cellcount,$_POST["reference"][$count]);
		
	
	 
	 
	 
	 
	 $totalamount += $myrowdetail["amount"];
	
	 	$cellcount++;
	
}



	$objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount,'Total Banked:');
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$cellcount,$totalamount);
	
		

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save($inputFileName);
$objWriter->save($inputFileName);


    
    //<?php
	
echo '<br /><p></p><p></p> <p></p> <p></p> <p></p>  <p class="page_title_text"><a href="' . $inputFileName . '">' . _('File generated o.k. Click to view this  excel File ') . '</p>';




//echo $AccountCode;



$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
			
		


		
			    
		
	


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

?>

  <script>


  $( function() {

    $( "#datepicker" ).datepicker({

    dateFormat: 'dd/mm/yy',//check change

    changeMonth: true,

    changeYear: true

});

  } );

	  

 
 

	  jQuery(document).ready(function($){
		  
		  
		  
		  
		  $(document).on('click', '.remove_row', function(){

          var row_id = $(this).attr("id");

        
          $('#row_id_'+row_id).remove();
			  
			  var counttotal =  $('#total_item').val();

         counttotal --;

          $('#total_item').val(counttotal);
			  
			  
			  
        });

		   $('#process').click(function(){

          


			

			$('#process').hide();
			   
			  



        });
		  
		  var table = $('#invoice-item-table1').DataTable( {
        dom: 'Bfrtip',
	    order: [[ 3, 'desc' ]],
        buttons: [
           
		{
                extend: 'pdfHtml5',
                orientation: 'landscape',
			pageSize: 'A4',
			
			
                customize: function(doc) {
      doc.defaultStyle.fontSize = 12; //<-- set fontsize to 16 instead of 10 
					  
   }  
            },
			{
                extend: 'excelHtml5'},
			
			{
                extend: 'copyHtml5'}
			
			
		],
	   
	   
	   
	   
    } );
		
		
		  
		  
		  
		  	 });
	  
	  

  </script>	
