<?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();		 
$fpdate = substr($_REQUEST['a'],6,4)."-".substr($_REQUEST['a'],3,2)."-".substr($_REQUEST['a'],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;">
    <tr><td colspan="6"><center><span style="font-size:18px;font-weight:normal;float:right;"> <b>INSURANCE PROVIDER AGED SUMMARY</b></span><span style="font-size:22px;font-weight:bold"> QOOLPOINT LTD</span></center>
	<br/><span style="font-size:16px;font-weight:bold;"><center>'. str_repeat('&nbsp;',15).'Details Upto: &nbsp;'.$_REQUEST['a'].'</center></span></td></tr>';
	echo "<tr>
    <th > Customer</th>
	<th> Total Amount </th>
	<th> Over 90 days </th>
	<th> Over 60 -90 days </th>
	<th> Over 30 - 60 days </th>
	<th> Over 0 - 30 days </th>
</tr>"; 
$td = strtotime("-1 month", strtotime($fpdate));
$thirtyday = date("Y-m-d", $td);
$td = strtotime("-2 month", strtotime($fpdate));
$sixtyday = date("Y-m-d", $td);
$td = strtotime("-3 month", strtotime($fpdate));
$ninetyday = date("Y-m-d", $td); 
$gtot =0; $gthirtytot = 0; $gsixtytot = 0; $gninetytot = 0; $govninetytot = 0;
$fetch = mysqli_query($db->connection, "SELECT sname, id FROM suppliers ORDER BY sname;") or die(mysqli_error($db->connection) );;
ini_set('max_execution_time', 500);
while ($row = mysqli_fetch_array($fetch, MYSQLI_ASSOC)) {
	// with each account get all records for each range
	$supp = $row['id']; $baltot = 0; $thirtytot = 0; $sixtytot = 0; $ninetytot = 0; $ovninetytot = 0;
	$sname = $row['sname'];
	$query = "SELECT insbills.supp, paytrans.payno, insbills.paidamount as bal FROM paytrans, insbills WHERE (paytrans.rtype = 'INSURANCE' AND paytrans.payno = insbills.payno AND insbills.supp = '$supp' AND paytrans.pdate <= '$thirtyday')";
	$query = $query." UNION SELECT supp, billno, IF(invoicecredit=1,-1*amount,amount) as bal FROM insbills WHERE ( supp = '$supp' AND idate <= '$thirtyday')";
	$result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	while ($arow = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
	 $thirtytot = $thirtytot + $arow['bal'];
	}
	
	$query = "SELECT insbills.supp, paytrans.payno, -1*insbills.paidamount AS bal FROM paytrans, insbills WHERE (paytrans.rtype = 'INSURANCE' AND paytrans.payno = insbills.payno AND insbills.supp = '$supp' AND pdate <= '$sixtyday')";
	$query = $query." UNION SELECT supp, billno, IF(invoicecredit=1,-1*amount,amount) as bal FROM insbills WHERE ( supp = '$supp' AND idate <= '$sixtyday')";
	$result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	while ($arow = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
	 $sixtytot = $sixtytot + $arow['bal'];
	}
	$query = "SELECT insbills.supp, paytrans.payno, -1*insbills.paidamount AS bal FROM paytrans, insbills WHERE (paytrans.rtype = 'INSURANCE' AND paytrans.payno = insbills.payno AND insbills.supp = '$supp' AND pdate <= '$ninetyday')";
	$query = $query." UNION SELECT supp, billno, IF(invoicecredit=1,-1*amount,amount) as bal FROM insbills WHERE ( supp = '$supp' AND idate <= '$ninetyday')";
	$result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	while ($arow = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
	 $ninetytot = $ninetytot + $arow['bal'];
	}
	$query = "SELECT insbills.supp, paytrans.payno, -1*insbills.paidamount AS bal FROM paytrans, insbills WHERE (paytrans.rtype = 'INSURANCE' AND paytrans.payno = insbills.payno AND insbills.supp = '$supp' AND pdate <= '$fpdate')";
	$query = $query." UNION SELECT supp, billno, IF(invoicecredit=1,-1*amount,amount) as bal FROM insbills WHERE ( supp = '$supp' AND idate <= '$fpdate')";
	$result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
	while ($arow = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
	 $baltot = $baltot + $arow['bal'];
	}
	
	$gtot = $gtot + $baltot;
	$l30 = $baltot - $thirtytot;	$gthirtytot = $gthirtytot + $l30;
	$l60 = $thirtytot - $sixtytot;	$gsixtytot = $gsixtytot + $l60;
	$l90 = $sixtytot - $ninetytot;	$gninetytot = $gninetytot + $l90;
	$govninetytot = $govninetytot + $ninetytot;
	echo "<tr><td>".$sname."</td> <td align='right'>".number_format( $baltot, 2 )."</td> <td align='right'>".number_format( $ninetytot, 2 )."</td><td align='right'>".number_format( $l90, 2 )."</td><td align='right'>".number_format( $l60, 2 )."</td><td align='right'>".number_format( $l30, 2 )."</td></tr>"; 	
}	
  echo "<tr><td><hr /></td> <td><hr /></td> <td><hr /></td> <td><hr /></td> <td><hr /></td> <td><hr /></td> </tr>";
  echo "<tr><td>TOTALS</td> <td align='right'>".number_format( $gtot, 2 )."</td> <td align='right'>".number_format( $govninetytot, 2 )."</td><td align='right'>".number_format( $gninetytot, 2 )."</td><td align='right'>".number_format( $gsixtytot, 2 )."</td><td align='right'>".number_format( $gthirtytot, 2 )."</td></tr>";
  echo "<tr><td><hr /></td> <td><hr /></td> <td><hr /></td> <td><hr /></td> <td><hr /></td> <td><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> <br />';
    echo '<div style="page-break-after:always"> </div> </div>';

$db->close_connection();
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>