<?php
session_start();
if (!isset($_SESSION['reusername'])) { echo 'Sorry You Dont Have permissions to View this Page!'; exit();}
require_once 'includes/database.php';
require_once 'includes/display.php';
require_once 'includes/funcs.php';
echo $htmlheaders;  echo '<head>';
echo $js;
echo $recss;
echo '<style>body{overflow-y:scroll;}</style>';
echo '</head><body>';
$user = $_SESSION['reusername'];
$db=new MySQLDatabase();
$db->open_connection();

$sd = substr($_REQUEST['sdate'],6,4)."-".substr($_REQUEST['sdate'],3,2)."-".substr($_REQUEST['sdate'],0,2);
$ed = substr($_REQUEST['edate'],6,4)."-".substr($_REQUEST['edate'],3,2)."-".substr($_REQUEST['edate'],0,2);

    echo '<p><a $href="#" id="printnow" class="printnow" onclick="myprint();" style="float:left;">Print</a>';
    echo '<a href="#" id="closenow" class="closenow" onclick="window.close();">Close</a></p>';
	echo '<div id="printblk">';
    echo '<table id="treport" style="width:90%;margin-left:30px;font-family:Trebuchet MS, Arial;font-size:12px;">
    <tr><td colspan="6"><center><span style="font-size:18px;font-weight:normal;float:center;"> <b>CASH FLOW REPORT </b></span><center>
    <br/><span style="font-size:16px;font-weight:bold;"><center>'. str_repeat('&nbsp;',25).'</center><br/><span style="font-size:16px;font-weight:bold;"><center>'. str_repeat('&nbsp;',25).'From: &nbsp;'.$_REQUEST['sdate'].' &nbsp; To: &nbsp;'.$_REQUEST['edate'].'</center></span></td></tr>';
	echo'<tr><u> <th></th>'; 	$bi = 0;	$banks = array();	$recp = array(); $deds = array();	$bfs = array();
	$query = "SELECT bacc, ob FROM banks ORDER BY bacc;";
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC )){
		$bi++;	$banks[$bi] = $row['bacc']; 	$deds[$row['bacc']] = 0;  $recp[$row['bacc']] = 0; 	$jvcr[$row['bacc']] = 0;  
		$jvdb[$row['bacc']] = 0; 	$bfs[$row['bacc']] = 0; 	$pays[$row['bacc']] = 0; 
		echo '<th>'.$row['bacc'].'</th>'; 
	}	
	echo '<th> Totals </th></u></tr>';
    echo '<tr><td colspan="5"><hr/></td></tr>'; 
    $rt = 0;  $ddt = 0; $gam = 0;   $crtot=0;  $drtot=0;    $bf=0;  $pt=0;                                               // get bf bal
	
	$query = "SELECT SUM(amount) as am, bacc FROM recptrans WHERE rdate < '$sd' AND revsd = 0 GROUP BY bacc
			  UNION SELECT SUM(-1*amount) as am, bacc FROM dexptrans WHERE ddate < '$sd' GROUP BY bacc
			  UNION SELECT SUM(-1*amount) as am, bacc FROM paytrans WHERE pdate < '$sd' GROUP BY bacc
			  UNION SELECT ob as am, bacc FROM banks
			  UNION SELECT SUM(-1*amount) as am, cacc as bacc FROM bktrans WHERE jdate < '$sd' GROUP BY bacc
			  UNION SELECT SUM(amount) as am, dacc as bacc FROM bktrans WHERE jdate < '$sd'  GROUP BY bacc ORDER BY bacc";
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	echo '<tr><td>B/F</td>';		$i = 0; 	$bkchg = '';
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC )){
			if(($row['bacc'] !== $bkchg) && ($bkchg !== '' )) { $bfs[$bkchg] =  $bf;  
																$bf = floatval($row['am']);;			}
			else {	$bf += floatval($row['am']); }
			$bkchg = $row['bacc'];
			$gam = $gam + floatval($row['am']);
    }  
	$bfs[$bkchg] =  $bf;
	for($i=1; $i <= $bi; $i++){
				echo '<td align="right">'.number_format($bfs[$banks[$i]],2).'</td>';
	}
	echo '<td align="right">'.number_format($gam,2).'</td></tr>';
    echo '<tr><td colspan="'.($bi+2).'"><hr/></td></tr>';
																								// get reciepts
    $query = "SELECT SUM(amount) as am, bacc FROM recptrans WHERE rdate >= '$sd' AND rdate <= '$ed' AND revsd = 0 GROUP BY bacc ORDER BY bacc";
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	echo '<tr><td>SALES</td>';		$i = 0;
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC )){
			$i++;
			$recp[$row['bacc']] =  floatval($row['am']);	$rt += floatval($row['am']);
			$gam = $gam + floatval($row['am']);
    }  
	for($i=1; $i <= $bi; $i++){
				echo '<td align="right">'.number_format($recp[$banks[$i]],2).'</td>';
	}
	echo '<td align="right">'.number_format($rt,2).'</td></tr>';
    echo '<tr><td colspan='.($bi+2).'"><hr/></td></tr>';  
																								// get deductions
    $query = "SELECT SUM(amount) as am, bacc FROM dexptrans WHERE ddate >= '$sd' AND ddate <= '$ed' AND revsd = 0 GROUP BY bacc ORDER BY bacc";
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	echo '<tr><td>EXPENSES</td>';			
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC )){
			$deds[$row['bacc']] =  floatval($row['am']);	$ddt += floatval($row['am']);
			$gam = $gam - floatval($row['am']);
    }  
	for($i=1; $i <= $bi; $i++){
				echo '<td align="right">'.number_format($deds[$banks[$i]],2).'</td>';
	}
	echo '<td align="right">'.number_format($ddt,2).'</td></tr>';
    echo '<tr><td colspan="'.($bi+2).'"><hr/></td></tr>';  
	
																									// get payments
    $query = "SELECT SUM(amount) as am, bacc FROM paytrans WHERE pdate >= '$sd' AND pdate <= '$ed' AND revsd = 0 GROUP BY bacc ORDER BY bacc";
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	echo '<tr><td>PAYMENTS</td>';			
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC )){
			$pays[$row['bacc']] =  floatval($row['am']);	$pt += floatval($row['am']);
			$gam = $gam - floatval($row['am']);
    }  
	for($i=1; $i <= $bi; $i++){
				echo '<td align="right">'.number_format($pays[$banks[$i]],2).'</td>';
	}
	echo '<td align="right">'.number_format($pt,2).'</td></tr>';
    echo '<tr><td colspan="'.($bi+2).'"><hr/></td></tr>'; 
																								// get credit JVs
    $query = "SELECT SUM(amount) as am, cacc FROM bktrans WHERE jdate >= '$sd' AND jdate <= '$ed' GROUP BY cacc ORDER BY cacc";
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	echo '<tr><td>WITHDRAWALS</td>';			
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC )){
			$jvcr[$row['cacc']] =  floatval($row['am']);	
			$gam = $gam - floatval($row['am']);		$crtot += floatval($row['am']);
    }  
	for($i=1; $i <= $bi; $i++){
				echo '<td align="right">'.number_format($jvcr[$banks[$i]],2).'</td>';
	}
	echo '<td align="right">'.number_format($crtot,2).'</td></tr>';
    echo '<tr><td colspan="'.($bi+2).'"><hr/></td></tr>';  
																							// get debit JVs
    $query = "SELECT SUM(amount) as am, dacc FROM bktrans WHERE jdate >= '$sd' AND jdate <= '$ed' GROUP BY dacc ORDER BY dacc";
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	echo '<tr><td>DEPOSITS</td>';			
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC )){
			$jvdb[$row['dacc']] =  floatval($row['am']);	
			$gam = $gam + floatval($row['am']);		$drtot += floatval($row['am']);
    }  
	for($i=1; $i <= $bi; $i++){
				echo '<td align="right">'.number_format($jvdb[$banks[$i]],2).'</td>';
	}
	echo '<td align="right">'.number_format($drtot,2).'</td></tr>';
    echo '<tr><td colspan="'.($bi+2).'"><hr/></td></tr>'; 
	
	echo '<tr><td>GRAND TOTAL</td>';
	for ($i=1; $i <= $bi; $i++){ 
		echo '<td align="right">'.number_format($recp[$banks[$i]] + $bfs[$banks[$i]] - $deds[$banks[$i]] + $jvdb[$banks[$i]] - $jvcr[$banks[$i]] ,2).'</td>';
	}	
	echo '<td align="right">'.number_format($gam,2).'</td></tr>';
    echo '<tr><td colspan="'.($bi+2).'"><hr/></td></tr>';  
    echo '</table>'; 

    echo '<span align="left" style="font-size:x-small">Printed by: '.$user.' &nbsp;&nbsp; '.date("d-m-Y H:i:s"). '</span> </div> <br />';
    echo '<div style="page-break-after:always"> </div>';


echo '</body></html>';
?>
<script type="text/javascript">
function myprint(){
	var w = window.open('', '', 'width=800,height=600,resizeable,scrollbars');
	 w.document.write($("#printblk").html());
	
	 w.document.close(); 										// needed for chrome and safari
	 javascript:w.print();
	 w.close();
};	
</script>