<?
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;
$jtype=$_REQUEST['jtype'];
$ltbl="ledger".$brcode.$user;
$jtbl="journal2".$brcode.$user;
mysqli_query($db,"drop TABLE if exists $ltbl");
mysqli_query($db,"drop TABLE if exists $jtbl");
$re=mysqli_query($db,"select brcode from branch")or die(mysqli_error($db));
while($srow=mysqli_fetch_array($re))
{
 
    mysqli_query($db,"CREATE TABLE if not exists $ltbl select * from gen4psnauto_psn_ampleicher2324.ledger where brcode = '$srow[0]' ");
    //mysqli_query($db,"CREATE TABLE if not exists $ltbl select * from psn_psnautogen4_psn_telcondb2324.ledger where brcode = '$srow[0]' and led not in(select led from $ltbl where brcode='$srow[0]') ");
    mysqli_query($db,"insert into $ltbl (select * from gen4psnauto_psn_ampleicher2425.ledger where brcode = '$srow[0]' and led not in(select led from $ltbl where brcode='$srow[0]'))") or die(mysqli_error($db));
    mysqli_query($db,"insert into $ltbl (select * from ledger where brcode = '$srow[0]' and led not in(select led from $ltbl where brcode='$srow[0]'))") or die(mysqli_error($db));
    mysqli_query($db,"CREATE TABLE if not exists $jtbl select * from gen4psnauto_psn_ampleicher2324.journal2 where brcode = '$srow[0]'");
    mysqli_query($db,"insert into $jtbl select * from gen4psnauto_psn_ampleicher2425.journal2 where brcode='$srow[0]' and date <= '$dt'");
    //mysqli_query($db,"CREATE TABLE if not exists $jtbl select * from psn_psnautogen4_psn_telcondb2324.journal2 where brcode = '$srow[0]'");
    mysqli_query($db,"insert into $jtbl select * from journal2 where brcode='$srow[0]' 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";
print "<th>No. <th>Debtor<th>Branch<th>Total <th><=30 <th> >30 & <=90 <th> >90 & <=180 <th> >180 & <=365<th> >365 & <=730<th> >730 & <=1095<th> >1095";
$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,brcode from $ltbl where grp='$jtype' order by led,brcode")or die(mysqli_error($db));
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='$srow[4]'";
 	
 	$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;
		$cure=mysqli_query($db,"select brname from branch where brcode='$srow[4]' ");
	    $row_br = mysqli_fetch_array($cure);
  		print "<tr><td>".$i++."</td><td>$row_br[0]</td><td>$srow[led]</td>";
       /* $cusre=mysqli_query($db,"select web from customer where acname='$srow[led]' and brcode='$srow[4]'");
	    $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='$srow[4]'"; 
		$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='$srow[4]'"; 
 		$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='$srow[4]'"; 
 		$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 to_days('$dt')-to_days(date)<=365 and brcode='$srow[4]'"; 
 		$res3=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res3);
   		$r3=$row[0];
  		if($balance>$r3)
  		{ 
			print "<td align=right> $r3</td>"; 
			$t_1801=$t_1801 + $r3;
		}
  		else
   		{	
			print "<td align=right>".putComa(round($balance,2))."</td>";
			$t_1801=$t_1801 + $balance;
			continue;
		}
 
  		$balance-=$r3;
//************
		$sql="select sum(dramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>365 and to_days('$dt')-to_days(date)<=730 and brcode='$srow[4]'"; 
 		$res3=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res3);
   		$r3=$row[0];
  		if($balance>$r3)
  		{ 
			print "<td align=right> $r3</td>"; 
			$t_365=$t_365 + $r3;
		}
  		else
   		{	
			print "<td align=right>".putComa(round($balance,2))."</td>";
			$t_365=$t_365 + $balance;
			continue;
		}
 
  		$balance-=$r3;
//************
		$sql="select sum(dramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>730 and to_days('$dt')-to_days(date)<=1095 and brcode='$srow[4]'"; 
 		$res3=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res3);
   		$r3=$row[0];
  		if($balance>$r3)
  		{ 
			print "<td align=right> $r3</td>"; 
			$t_730=$t_730 + $r3;
		}
  		else
   		{	
			print "<td align=right>".putComa(round($balance,2))."</td>";
			$t_730=$t_730 + $balance;
			continue;
		}
 
  		$balance-=$r3;  	
//************
		$sql="select sum(dramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>1095  and brcode='$srow[4]'"; 
 		$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=1 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_1801)."</td><td align=right>".putComa($t_365)."</td><td align=right>".putComa($t_730)."</td><td align=right>".putComa($t_120more)."</td>";

?>
</table>

