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

$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="treport" style="width:90%;margin-left:30px;">
    <tr><td colspan="7"><center><span style="font-size:18px;font-weight:normal;float:right;"> <b>INSURANCE CUSTOMER ACCOUNT</b></span><span style="font-size:22px;float:left;font-weight:bold"> </span></center></td></tr>
    <tr><td colspan="7"><span style="font-size:16px;font-weight:bold;"><center>'. $cust.'</center></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><u><th>S/no</th> <th>Date</th><th>Doc. No.</th><th>Naration</th> <th>Debit</th> <th>Credit</th> <th>Balance</th></u></tr>'; 
    echo '<tr><td colspan="7"><hr/></td></tr>'; 
	$tot = 0;
	$db=new MySQLDatabase();
	$db->open_connection();
	if( $insno !== '0') {  $query = "SELECT 'B/F Balance' as nar, SUM(amount - paidamount) as bfam FROM insinvoices WHERE insno = '$insno' AND idate < '$sd';";
						}
	else {			
												// get BF balance
		if($cus === ""){ $query = "SELECT 'B/F Balance' as nar,  SUM(tt.ram) as bfam FROM ( SELECT SUM(-1*amount) as ram FROM recptrans WHERE rdate < '$sd' AND rtype='INSURANCE' UNION SELECT IF(invoicecredit=1, SUM(amount), SUM(-1*amount)) as ram FROM insinvoices WHERE idate < '$sd') as tt"; }
		else { $query = "SELECT 'B/F Balance' as nar,  SUM(tt.ram) as bfam FROM ( SELECT SUM(-1*amount) as ram FROM recptrans WHERE cname = '$cus' AND rtype='INSURANCE' AND rdate < '$sd' UNION SELECT IF(invoicecredit=1, SUM(amount), SUM(-1*amount)) as ram FROM insinvoices WHERE cname = '$cus' AND idate < '$sd') 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="3"></td> <td colspan="3">'.$row['nar'].'</td><td align="right">'.number_format($tot,0).'</td></tr>'; 
	 }	
																	// get invoices & receipts
	if( $insno !== '0') {  $query = "SELECT insinvoices.invno as recpno, insinvoices.idate as rdate, CONCAT('INS No.',insinvoices.insno,IF(insinvoices.invoicecredit=1,' INVOICE - ', ' CREDIT - '),insrecs.regno,' - ',insrecs.vehmod,' - ',insrecs.insurer,' - Insurance Value: ',insrecs.inam,' - Rate%: ',insrecs.insrate,' - Premium: ',insrecs.insprem) as nar, IF(insinvoices.invoicecredit=1,insinvoices.amount,0) as dam, IF(insinvoices.invoicecredit=0,insinvoices.amount,0) as cam FROM insinvoices, insrecs WHERE insinvoices.insno = '$insno' AND insinvoices.insno = insrecs.insno AND insinvoices.idate >= '$sd' AND insinvoices.idate <= '$ed'
									 UNION SELECT recptrans.recpno, recptrans.rdate, CONCAT(recptrans.pmode, '-', recptrans.chqno, ' - ', recptrans.chqdet, ' RECEIPT -', recptrans.rmks,'-', insinvoices.invno) as nar, IF(recptrans.amount<0,IF(insinvoices.invoicecredit=1,insinvoices.amount,0),IF(insinvoices.invoicecredit=0,insinvoices.paidamount,0)) as dam, IF(recptrans.amount>0,IF(insinvoices.invoicecredit=1,insinvoices.paidamount,0),0) as cam FROM recptrans, insinvoices WHERE recptrans.recpno = insinvoices.recpno AND insinvoices.insno = '$insno' AND recptrans.rdate >= '$sd' AND recptrans.rdate <= '$ed' AND recptrans.rtype='INSURANCE' ;";
						}
	else {																	
		if($cus === ""){ $query = "SELECT recpno, rdate, CONCAT(pmode, '-', chqno, ' - ', chqdet, ' RECEIPT -', rmks) as nar, IF(amount<0,-1*amount,0) as dam, IF(amount>0,amount,0) as cam FROM recptrans WHERE rdate >= '$sd' AND rdate <= '$ed' AND rtype='INSURANCE' 
				UNION SELECT insinvoices.invno as recpno, insinvoices.idate as rdate, CONCAT('INS No.',insinvoices.insno,' INVOICE - ',insrecs.regno,' - ',insrecs.vehmod,' - ',insrecs.insurer,' - Insurance Value: ',insrecs.inam,' - Rate%: ',insrecs.insrate,' - Premium: ',insrecs.insprem), IF(insinvoices.invoicecredit=1,insinvoices.amount,0), IF(insinvoices.invoicecredit=0,insinvoices.amount,0) FROM insinvoices, insrecs WHERE insinvoices.insno = insrecs.insno AND insinvoices.idate >= '$sd' AND insinvoices.idate <= '$ed' ORDER BY rdate, recpno"; }
		else { $query = "SELECT recpno, rdate, CONCAT(pmode, '-', chqno, ' - ', chqdet, ' RECEIPT -', rmks) as nar, IF(amount<0,-1*amount,0) as dam, IF(amount>0,amount,0) as cam FROM recptrans WHERE cname = '$cus' AND rdate >= '$sd' AND rdate <= '$ed' AND rtype='INSURANCE' 
				UNION SELECT insinvoices.invno as recpno, insinvoices.idate as rdate, CONCAT('INS No.',insinvoices.insno,' - CREDIT NOTE- ',insinvoices.rmks), 0, IF(insinvoices.invoicecredit=0,insinvoices.amount,0) FROM insinvoices WHERE invoicecredit = 0 AND insinvoices.cname = '$cus' AND insinvoices.idate >= '$sd' AND insinvoices.idate <= '$ed'
				UNION SELECT insinvoices.invno as recpno, insinvoices.idate as rdate, CONCAT('INS No.',insinvoices.insno,' INVOICE - ',insrecs.regno,' - ',insrecs.vehmod,' - ',insrecs.insurer,' - Insurance Value: ',insrecs.inam,' - Rate%: ',insrecs.insrate,' - Premium: ',insrecs.insprem), IF(insinvoices.invoicecredit=1,insinvoices.amount,0), IF(insinvoices.invoicecredit=0,insinvoices.amount,0) FROM insinvoices, insrecs WHERE insinvoices.insno = insrecs.insno AND invoicecredit=1 AND insinvoices.cname = '$cus' AND insinvoices.idate >= '$sd' AND insinvoices.idate <= '$ed' ORDER BY rdate, recpno";
				}
	}		
	$dtot = 0;  $ctot = 0;  $i = 1;
    $result = mysqli_query($db->connection, $query) or die(mysqli_error($db->connection) );
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
        $dtot = $dtot + floatval($row['dam']);
        $ctot = $ctot + floatval($row['cam']);
        $tot =  $tot + floatval($row['dam']) - floatval($row['cam']);
        $rdate = substr($row['rdate'],8,2)."-".substr($row['rdate'],5,2)."-".substr($row['rdate'] ,0,4);
        echo '<tr><td>'.$i.'</td><td>'.$rdate.'</td><td>'.$row['recpno'].'</td><td>'.$row['nar'].'</td><td align="right">'.number_format($row['dam'],0).'</td><td align="right">'.number_format($row['cam'],0).'</td><td align="right">'.number_format($tot,0).'</td></tr>';      
        $i++;
    }
    echo '<tr><td colspan="7"><hr/></td></tr>';   
    echo '<tr><td colspan="4"></td><td align="right">'.number_format($dtot,0).'</td><td align="right">'.number_format($ctot,0).'</td><td align="right">'.number_format($tot,0).'</td></tr>';  
    echo '<tr><td colspan="7"><hr/></td></tr>';  
    echo '</table>'; 
    //echo '<div id="report">Please check your balance and bring any discrepancies within 15 days</div> <br /><br />';
    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>';

$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>