<?
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");
$y=$_REQUEST['y'];
$m=$_REQUEST['m'];
$d=$_REQUEST['d'];
$y1=$_REQUEST['y1'];
$m1=$_REQUEST['m1'];
$d1=$_REQUEST['d1'];
$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;
$ltbl="ledger".$brcode.$user;
$jtbl="journal2".$brcode.$user;
mysqli_query($db,"drop TABLE if exists $ltbl");
mysqli_query($db,"drop TABLE if exists $jtbl");
mysqli_query($db,"CREATE TABLE $ltbl select * from psnindia_gen4psnauto_psn_psndb2223.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());
mysqli_query($db,"CREATE TABLE $jtbl select * from psnindia_gen4psnauto_psn_psndb2223.journal2 where brcode='$brcode'");
mysqli_query($db,"insert into $jtbl select * from journal2 where brcode='$brcode' and 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 as on ".$dt."<br>[printed by $user]";
print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='gray'>";
print "<th>No. <th>Debtor<th>Total <th><=30 <th> >30 & <=90 <th> >90 & <=180 <th> >180";
$i=1;
$t_bal=0;
$t_30=0;
$t_60=0;
$t_90=0;
$t_120=0;
$t_120more=0;
$re=mysqli_query($db,"select distinct led,grp,op,drcr from $ltbl where grp='Sundry Debtors' and brcode='$brcode' order by led")or die(mysqli_error());

while($srow=mysqli_fetch_array($re))
{
 	$opbal=$srow['op'];
 	if($srow['drcr']=='Cr')
 	    $opbal=$opbal*-1;    
 	$sql="select sum(dramt)-sum(cramt) from $jtbl where partic='$srow[led]' and brcode='$brcode'";
 	
 	$balres=mysqli_query($db,$sql)or die('err1'.mysqli_error());
  	$balrow=mysqli_fetch_row($balres)or die('err2'.mysqli_error());
 	$balance=$opbal+$balrow[0];
	if($balance>0)
	{
		$t_bal+=$balance;
  		print "<tr><td>".$i++."</td><td>$srow[led]</td>";
       /* $cusre=mysqli_query($db,"select web from customer where acname='$srow[led]' and brcode='$brcode'");
	    $row_cus = mysqli_fetch_array($cusre);
	    print "<td>".$row_cus[0]."</td>";  		*/
  		print "<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))<=30 and brcode='$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)<=90 and brcode='$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)>90 and to_days('$dt')-to_days(date)<=180 and brcode='$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)>180  and brcode='$brcode'"; 
 		$res4=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res4);
   		$r3=$row[0];
		$r9=$balance;
		print "<td align=right>".putComa($r9)."</td>";
		$t_120more=$t_120more + $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=2 align=center>Total</td><td align=right>".putComa($t_bal)."</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_120more)."</td>";
?>
</table>

