<?
session_start();
include_once('includes/top.php');
include_once('opendb.php');
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];

$y1=$_REQUEST['y1'];
$m1=$_REQUEST['m1'];
$d1=$_REQUEST['d1'];
$y2=$_REQUEST['y2'];
$m2=$_REQUEST['m2'];
$d2=$_REQUEST['d2'];

$p1=$_REQUEST['p1'];
$p2=$_REQUEST['p2'];
$p3=$_REQUEST['p3'];
$p4=$_REQUEST['p4'];

  $jtype = $_REQUEST['jtype'];




if(strlen($brcode)<=0)
{
 	print "<script> alert('Invalid Login'); parent.document.location='ind.php' </script>";
 	return;
}
include("opendb.php");
include("test.php");
$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;

$brres=mysqli_query($db,"select * from branch order by brname");
while ($brrow=mysqli_fetch_array($brres))
{
$ltbl="ledgercra1".$brrow[0].$user;
$jtbl="journal2acra1".$brrow[0].$user;
mysqli_query($db,"drop TABLE if  exists $ltbl");
mysqli_query($db,"drop TABLE if  exists $jtbl");

//print $ltbl;
mysqli_query($db,"CREATE TABLE if not exists $ltbl select * from gen4psnauto_psn_ampleicher2425.ledger  where brcode='$brrow[0]'");
mysqli_query($db,"CREATE INDEX led on $ltbl(led,grp)");
$sql="insert into $ltbl (select * from ledger  where brcode='$brrow[0]' and led not in(select led from $ltbl where brcode='$brrow[0]'))";
//echo $sql;
mysqli_query($db,"insert into $ltbl (select * from ledger  where brcode='$brrow[0]' and led not in(select led from $ltbl where brcode='$brrow[0]'))") or die(mysqli_error($db));

mysqli_query($db,"CREATE TABLE if not exists $jtbl select * from gen4psnauto_psn_ampleicher2425.journal2 where brcode='$brrow[0]'");
mysqli_query($db,"CREATE INDEX partic on  $jtbl(partic)");
mysqli_query($db,"insert into $jtbl select * from journal2 where brcode='$brrow[0]' and date <= '$dt'");

/*
mysqli_query($db,"CREATE TABLE $ltbl select * from 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 journal2 where brcode='$brcode' and date <= '$dt'");
*/
?>
<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>
<?


$re=mysqli_query($db,"select distinct led,grp,op,drcr from $ltbl where grp='$jtype' and brcode='$brrow[0]' order by led")or die(mysqli_error($db));

if(mysqli_num_rows($re)>0)
{
    
  print "<b>".$brrow[1]."</b>";
print "<br> $jtype aging list as on ".$dt."<br>[printed by $user]";
print "<table border=1 cellspacing=1 cellpadding=3 bgcolor='gray'>";
print "<th>No. <th>Ledger<th>Total <th>< $p1 <th> >=$p1 & <$p2 <th> >=$p2 & <$p3 <th> >=$p3 & <$p4 <th> >=$p4";
$i=1;
$t_bal=0;
$t_15=0;
$t_30=0;
$t_60=0;
$t_90=0;
$t_90more=0;  
while($srow=mysqli_fetch_array($re))
{
 	$opbal=$srow['op'];
 	$drcr=$srow['drcr'];
 	
 	if($drcr=='Dr')
 	{
 	  $opbal=-($opbal);  
 	}
 	
 	
  $sql="select sum(cramt)-sum(dramt) from $jtbl where partic='$srow[led]' and brcode='$brrow[0]'";
 	
 	$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)
	{
		$t_bal+=$balance;
  		print "<tr><td>".$i++."</td><td>$srow[led]</td><td align=right>".putComa(round($balance,2))."</td>";
  		
	    $sql="select sum(cramt) from $jtbl where partic='$srow[led]' and (to_days('$dt')-to_days(date))<=$p1 and brcode='$brrow[0]'"; 
		$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(cramt) from $jtbl where partic='$srow[led]' and (to_days('$dt')-to_days(date))>$p1 and to_days('$dt')-to_days(date)<=$p2 and brcode='$brrow[0]'"; 
		$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(cramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>$p2 and to_days('$dt')-to_days(date)<=$p3 and brcode='$brrow[0]'"; 
 		$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(cramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>$p3 and to_days('$dt')-to_days(date)<=$p4 and brcode='$brrow[0]'"; 
 		$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(cramt) from $jtbl where partic='$srow[led]' and to_days('$dt')-to_days(date)>$p4  and brcode='$brrow[0]'"; 
 		$res4=mysqli_query($db,$sql);
  		$row=mysqli_fetch_array($res4);
   		$r3=$row[0];
		$r9=$balance;
		print "<td align=right>".putComa($r9)."</td></tr>";
		$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;
  	
	}
}
	mysqli_query($db,"drop TABLE $ltbl");
mysqli_query($db,"drop TABLE $jtbl");
if($t_bal>0)
{
print "<tr><td colspan=2 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></tr></table>";
}
else
{
    print"</table>";
}
}
}
?>

