<?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();
$c = $_REQUEST['c'];			 
$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="6"><center><span style="font-size:18px;font-weight:normal;float:right;"> <b>TRACKER CUSTOMER STATEMENT</b></span><span style="font-size:22px;font-weight:bold"> </span></center>
    <br/><span style="font-size:16px;font-weight:bold;"><center>'. $c.'</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 class="borderclass"><u><th>S/no</th> <th>Date</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;
																	// get BF balance
	$query = "SELECT 'B/F Balance' as nar,  SUM(tt.ram) as bfam FROM ( SELECT SUM(-1*amount) as ram FROM recptrans WHERE cname = '$c' AND rtype = 'TRACKER' AND rdate < '$sd' UNION SELECT SUM(IF(invoicecredit=1, amount, -1*amount)) as ram FROM trackinvoices WHERE cname = '$c' 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 class="borderclass" colspan="2"></td> <td class="borderclass" colspan="3">'.$row['nar'].'</td><td class="borderclass" align="right">'.number_format($tot,0).'</td></tr>'; 
	 }	
																	// get invoices & receipts
	$query = "SELECT recpno, rdate, CONCAT(pmode, '-', chqno, ' - ', rtype, ' RECEIPT -', rmks) as nar, 'R' as rin, 		
			  IF(amount<0,-1*amount,0) as dam, IF(amount>0,amount,0) as cam FROM recptrans WHERE cname = '$c' AND rtype = 'TRACKER' AND rdate >= '$sd' AND rdate <= '$ed' 
              UNION SELECT invno as recpno, idate as rdate, CONCAT(IF(invoicecredit=1,'TRACKERS - ','CREDIT APPLIED - '),rmks), IF(invoicecredit=1,'I','CR'), IF(invoicecredit=1,amount,0), IF(invoicecredit=0,amount,0) FROM trackinvoices WHERE cname = '$c' AND idate >= '$sd' AND idate <= '$ed' ORDER BY rdate, recpno";
      $dtot = 0;  $ctot = 0;  $i = 0;
    $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 class="borderclass"> </td> <td class="borderclass">'.$rdate.'</td>  <td class="borderclass">'.$row['nar'].'</td> <td class="borderclass" align="right">'.number_format($row['dam'],0).'</td> <td class="borderclass" align="right">'.number_format($row['cam'],0).'</td> <td class="borderclass" align="right">'.number_format($tot,0).'</td></tr>'; 
        if($row['rin'] === "I"){ $invno = $row['recpno']; 
           $sql = "SELECT jcno, jdate, CONCAT(regno, ' - ',vehmod, ' - ',chasis, ' @ ',am) as nar from jcards where invno = '$invno'";
           $res = mysqli_query($db->connection, $sql) or die(mysqli_error($db->connection) );
           $j = 0;
           while ($rowj = mysqli_fetch_array($res, MYSQLI_ASSOC)){
            $i++;
            $jdate = substr($rowj['jdate'],8,2)."-".substr($rowj['jdate'],5,2)."-".substr($rowj['jdate'] ,0,4);
            echo '<tr><td class="borderclass">'.$i.'</td>  <td class="borderclass"></td> <td class="borderclass">'.$jdate.' - '.$rowj['jcno'].' - '.$rowj['nar'].'</td> <td class="borderclass"></td> <td class="borderclass"></td> <td class="borderclass"></td> </tr>';}     
         }
       
    }
    echo '<tr><td colspan="6"><hr/></td></tr>';   
    echo '<tr><td class="borderclass" colspan="3"></td> <td class="borderclass" align="right">'.number_format($dtot,0).'</td> <td class="borderclass" align="right">'.number_format($ctot,0).'</td> <td class="borderclass" align="right">'.number_format($tot,0).'</td></tr>';  
    echo '<tr><td colspan="6"><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.write("<style> .borderclass{ border:1px solid black;font-family:'Trebuchet MS';font-size:12px;padding:3px;}</style>\n");
	 w.document.close(); 										// needed for chrome and safari
	 javascript:w.print();
	 w.close();
};	
</script>