<?
session_start();
$brcode = $_SESSION['brcode'];
$user = $_SESSION['user'];
if(strlen($brcode)<=0)
{
 	print "<script> alert('Invalid Login'); parent.document.location='ind.php' </script>";
 	return;
}
include("opendb.php");
include("test.php");
$d = $_REQUEST['d'];
$m = $_REQUEST['m'];
$y = $_REQUEST['y'];
$d1 = $_REQUEST['d1'];
$m1 = $_REQUEST['m1'];
$y1 = $_REQUEST['y1'];
$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;
$ltbl="ledger".$brcode.$user;
$jtbl="journal2".$brcode.$user;
mysqli_query($db,"drop TABLE $ltbl");
mysqli_query($db,"drop TABLE $jtbl");
/*
mysqli_query($db,"CREATE TABLE $ltbl select * from psn_psneicherdb2122.ledger  where brcode='$brcode'");
mysqli_query($db,"insert into $ltbl (select * from ledger  where brcode='$brcode' and led not in(select led from $ltbl where brcode='$brcode'))") or die(mysqli_error($db));
mysqli_query($db,"CREATE TABLE $jtbl select * from psn_psneicherdb2122.journal2 where brcode='$brcode'");
mysqli_query($db,"insert into $jtbl select * from journal2 where brcode='$brcode' and date <= '$dt'");*/

mysqli_query($db,"CREATE TABLE $ltbl select * from gen4psnauto_psn_psneicherdb2223.ledger");
mysqli_query($db,"insert into $ltbl (select * from ledger  where led not in(select led from $ltbl))") or die(mysqli_error($db));
mysqli_query($db,"CREATE TABLE $jtbl select * from gen4psnauto_psn_psneicherdb2223.journal2");
mysqli_query($db,"insert into $jtbl select * from journal2 where date <= '$dt'");

mysqli_query($db,"CREATE INDEX partic ON $jtbl (partic, date)");
?>
<link rel="stylesheet" href="../style.css" type="text/css" />
<style>
body,td{font-family:arial narrow;font-size:14;background-color:white}
th{background-color:silver};
</style>
<center>
<? 
print "<b>".$branch."</b>";
print "<br> Debtors aging list for staff advances as on ".$dt."<br>[printed by $user]";
print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='gray'>";
print "<th>No. <th>Branch<th>Debtor<th>Total<th><15<th> >=15 & <30 <th> >=30 & <60 <th> >=60 & <90 <th> >=90";
$i=1;
$t_bal=0;
$t_30=0;
$t_60=0;
$t_90=0;
$t_90more=0;
$re=mysqli_query($db,"select distinct led,grp,op,drcr,brcode from $ltbl where grp='STAFF ADVANCES' order by brcode,led")or die(mysqli_error($db));
while($srow=mysqli_fetch_array($re))
{
    if($srow['drcr']=='Cr')
 	    $opbal=-1*$srow['op'];
 	else
 	    $opbal=$srow['op'];
 	$sql="select sum(dramt)-sum(cramt) from $jtbl where partic='$srow[led]' and brcode='$srow[brcode]'";
 	$balres=mysqli_query($db,$sql)or die('err1'.mysqli_error($db));
  	$balrow=mysqli_fetch_row($balres)or die('err2'.mysqli_error($db));
 	$balance=$opbal+$balrow[0];
	if($balance>0)
	{
	    $brqry=mysqli_query($db,"select brname from branch where brcode='$srow[brcode]'");
	    $brrow=mysqli_fetch_array($brqry);
	    $branchname=$brrow[0];
		$t_bal+=$balance;
  		print "<tr><td>".$i++."</td><td>$branchname</td><td>$srow[led]</td><td align=right>".putComa(round($balance,2))."</td>";
  		
	    $sql="select sum(dramt) from $jtbl where partic='$srow[led]' and (to_days('$dt')-to_days(date))<=15 and brcode='$srow[brcode]'"; 
		$res1=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res1);
   		$r3=$row[0];
  		if($balance>$r3)
  		{ 
			print "<td align=right> $r3</td>"; 
			$t_15=$t_15 + $r3;
		}
  		else
   		{
			print "<td align=right>".putComa(round($balance,2))."</td>";
			$t_15=$t_15 + $balance;
			continue;
		}
  		$balance-=$r3;
//**************	
		$sql="select sum(dramt) from $jtbl where partic='$srow[led]' and (to_days('$dt')-to_days(date))>15 and to_days('$dt')-to_days(date)<=30 and brcode='$srow[brcode]'"; 
		$res1=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res1);
   		$r3=$row[0];
  		if($balance>$r3)
  		{ 
			print "<td align=right> $r3</td>"; 
			$t_30=$t_30 + $r3;
		}
  		else
   		{
			print "<td align=right>".putComa(round($balance,2))."</td>";
			$t_30=$t_30 + $balance;
			continue;
		}
  		$balance-=$r3;
 //************
		$sql="select sum(dramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>30 and to_days('$dt')-to_days(date)<=60 and brcode='$srow[brcode]'"; 
 		$res2=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res2);
   		$r3=$row[0];
  		if($balance>$r3)
  		{ 
			print "<td align=right> $r3</td>"; 
			$t_60=$t_60 + $r3;
		}
  		else
   		{
			print "<td align=right>".putComa(round($balance,2))."</td>";
			$t_60=$t_60 + $balance;
			continue;
		}
  		$balance-=$r3;
//************
		$sql="select sum(dramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>60 and to_days('$dt')-to_days(date)<=90 and brcode='$srow[brcode]'"; 
 		$res3=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res3);
   		$r3=$row[0];
  		if($balance>$r3)
  		{ 
			print "<td align=right> $r3</td>"; 
			$t_90=$t_90 + $r3;
		}
  		else
   		{	
			print "<td align=right>".putComa(round($balance,2))."</td>";
			$t_90=$t_90 + $balance;
			continue;
		}
  		$balance-=$r3;
//************
		$sql="select sum(dramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>90 and brcode='$srow[brcode]'"; 
 		$res4=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res4);
   		$r3=$row[0];
		$r9=$balance;
		print "<td align=right>".putComa($r9)."</td>";
		$t_90more=$t_90more + $r9;
//  		if($balance>$r3)
//  		{ print "<td align=right> $r3</td>"; }
//  		else
// 		{print "<td align=right>".putComa(round($balance,2))."</td>";continue;}

  		$balance-=$r3;
	}
}
print "<tr><td colspan=3 align=center>Total</td><td align=right>".putComa($t_bal)."</td><td align=right>".putComa($t_15)."</td><td align=right>".putComa($t_30)."</td><td align=right>".putComa($t_60)."</td><td align=right>".putComa($t_90)."</td><td align=right>".putComa($t_90more)."</td>";
?>
</table>
