<?php
session_start();
if (!isset($_SESSION['reusername'])) { echo 'Sorry You Dont Have permissions to View this Page!'; exit();}
require_once 'includes/database.php';

$user = $_SESSION['reusername'];
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
        <script type="text/javascript" src="js/jquery-1.10.2.js"></script>
        <script type="text/javascript" src="js/jquery-ui-1.10.4.custom.js"></script>
        <link rel="stylesheet" href="css/allcss.css" type="text/css" />
        <link rel="stylesheet" href="css/jquery-ui-1.10.4.custom.css" type="text/css" />
        <style>
            html, body {margin:0; padding:0; height:100%; width:100%; min-width:100%; min-height:100%; overflow:scroll;}
            tr { border: 1px solid #666; }
            td { border: 1px solid #666; }
		</style>
</head><body>			
<?php
$pend = $_REQUEST['pend'];	
$cus = $_REQUEST['cus'];	
if($cus === ""){ $cust = "All Customers"; }	
else { $cust = $_REQUEST['cus']; }
if(isset($_REQUEST['insno'])){ $insno = $_REQUEST['insno']; }
else { $insno = '0'; }		 
$ed = substr($_REQUEST['e'],6,4)."-".substr($_REQUEST['e'],3,2)."-".substr($_REQUEST['e'],0,2);
$sd = substr($_REQUEST['s'],6,4)."-".substr($_REQUEST['s'],3,2)."-".substr($_REQUEST['s'],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="tt" style="width:90%;margin-left:30px;">';
    echo '<tr><td colspan="2" align="middle"><img style="vertical-align:top;" src="images/inslogo.png">&nbsp;</img> <span style="font-size:28px;font-weight:bold;font-family:Trebuchet MS;"> &nbsp;&nbsp;QOOLPOINT INSURANCE AGENCY </span> <br> <span style="font-size:14px;font-weight:bold; margin-top:-40px; font-family:Trebuchet MS"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; P.O.Box 82496 - 80100, Mombasa  Tel: 0412228887 / 0722718887 <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Email: insurance@qool-point.co.ke </span><br>REGULATED BY THE INSURANCE REGULATORY AUTHORITY<br></td><tr>';

    echo '<table id="treport" style="width:90%;margin-left:30px;border: 1px solid #666;">
    <tr><td colspan="9" align="left"><span style="font-size:18px;font-weight:bold;">'. $cust.'</span><br/>
	<br/><span style="font-size:16px;font-weight:bold;"><center>'. str_repeat('&nbsp;',15).'Details From: &nbsp;'.$_REQUEST['s'].' &nbsp; To: &nbsp;'.$_REQUEST['e'].'</center></span></td></tr>';
    echo '<tr><td colspan="9">&nbsp;</td></tr>';
	echo '<tr style="border:1px solid black;"><u> <th>DATE</th> <th>INSURED</th> <th>REG. NO.</th> <th> SUM INSURED</th> <th>PREMIUM</th> <th>INV. No.</th> <th>PAID</th> <th>PAY DATE </th> <th>BALANCE</th></u></tr>'; 	
	$tot = 0;
	$db=new MySQLDatabase();
	$db->open_connection();
	$query = "SELECT 'B/F Balance' as nar, SUM(tt.am) as bfam FROM (SELECT SUM(IF(invoicecredit=1,amount, -1*amount)) as am FROM insinvoices WHERE cname = '$cus' AND idate < '$sd' UNION SELECT -1*SUM(amount) as am FROM recptrans WHERE cname = '$cus' AND rdate < '$sd' AND rtype='INSURANCE') as tt;";
	$result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){		  
		$tot = $tot + floatval($row['bfam']);
        echo '<tr><td colspan="4"></td> <td colspan="4">'.$row['nar'].'</td> <td align="right">'.number_format($tot,0).'</td></tr>'; 
	 }	
	 														// get invoices & receipts
	if($pend == '1') { $query = "SELECT insinvoices.idate, insrecs.insno, insrecs.insurer, insrecs.regno, insinvoices.invoicecredit, insrecs.inam, insrecs.amount as fpam, insrecs.inst1, insrecs.inst2, insrecs.inst3, insrecs.invno1, insrecs.invno2, insrecs.invno3, insinvoices.paidamount, IF(insinvoices.invoicecredit=1,insinvoices.amount, -1*insinvoices.amount) as amount, insinvoices.invno, insinvoices.recpno FROM insinvoices, insrecs WHERE insinvoices.insno = insrecs.insno AND insinvoices.cname = '$cus' AND insinvoices.idate >= '$sd' AND insinvoices.idate <= '$ed' AND (insinvoices.amount-insinvoices.paidamount)>0  ORDER BY invno"; }															
	else { $query = "SELECT insinvoices.idate, insrecs.insno, insrecs.insurer, insrecs.regno, insinvoices.invoicecredit, insrecs.inam, insrecs.amount as fpam, insrecs.inst1, insrecs.inst2, insrecs.inst3, insrecs.invno1, insrecs.invno2, insrecs.invno3, insinvoices.paidamount, IF(insinvoices.invoicecredit=1,insinvoices.amount, -1*insinvoices.amount) as amount, insinvoices.invno, insinvoices.recpno FROM insinvoices, insrecs WHERE insinvoices.insno = insrecs.insno AND insinvoices.cname = '$cus' AND insinvoices.idate >= '$sd' AND insinvoices.idate <= '$ed' ORDER BY invno"; }
	$prtot = 0;  $pdtot = 0;  $i = 1;
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$recpno = $row['recpno'];	$rdate = '';	$pdet = '';
		$qqq = "SELECT CONCAT('Recp No.',recpno, ' - ', pmode, '-', chqno, ' - ', chqdet) as pdet, rdate FROM recptrans WHERE recpno = '$recpno';";
		$res = mysqli_query($db->connection, $qqq) or die(mysqli_error($db->connection) );
		if($rowb = mysqli_fetch_array($res)){ $rdate = substr($rowb['rdate'],8,2)."-".substr($rowb['rdate'],5,2)."-".substr($rowb['rdate'] ,0,4);	$pdet = $rowb['pdet']; }
        $prtot = $prtot + floatval($row['amount']);
        $pdtot = $pdtot + floatval($row['paidamount']);
		$diff = floatval($row['amount']) - floatval($row['paidamount']);
        $tot =  $tot + $diff;
        $idate = substr($row['idate'],8,2)."-".substr($row['idate'],5,2)."-".substr($row['idate'] ,0,4);
		
        echo '<tr><td style="border:1px solid black;">'.$idate.'</td> <td style="border:1px solid black;">Book No.'.$row['insno'].' - '.$row['insurer'].'</td> <td style="border:1px solid black;">'.$row['regno'].'</td> <td align="right" style="border:1px solid black;">'.number_format($row['inam'],0).'</td> <td align="right" style="border:1px solid black;">'.number_format($row['amount'],0).'</td> <td style="border:1px solid black;">'.$row['invno'].'</td> <td align="right" style="border:1px solid black;">'.number_format($row['paidamount'],0).'</td> <td style="border:1px solid black;">'.$rdate.' - '.$pdet.'</td> <td align="right" style="border:1px solid black;">'.number_format($diff,0).'</td></tr>';      
        $i++;
    }
  
    echo '<tr><td colspan="4"></td> <td align="right">'.number_format($prtot,0).'</td> <td>&nbsp;</td> <td align="right">'.number_format($pdtot,0).'</td> <td></td> <td align="right">'.number_format($tot,0).'</td></tr>';  
    echo '</table><br><br>'; 
    echo '<div id="report" style="margin-left:16px;font-size:11px;font-family:Trebuchet MS;">Above is the statement as at '.$_REQUEST['e'].'. Kindly let us have the payment for the balances. <br /> Thank You for the business support.</div><br /><br /><br />';
    
    echo '<span align="left" style="font-family:Trebuchet MS;font-size:11px;">Printed by: '.$user.' &nbsp;&nbsp; '.date("d-m-Y H:i:s"). '</span> </div> <br />';
    echo '<div style="page-break-after:always"> </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('<link rel="stylesheet" type="text/css" href="css/allcss.css">');
	 w.document.write($("#printblk").html());
	 w.document.close(); 										// needed for chrome and safari
	 javascript:w.print();
	 w.close();
};	
</script>