<?
session_start();
if(strlen($brcode)<=0)
{
	print "<script> alert('Invalid Login'); parent.document.location='ind.php' </script>";
	return;
}
include("opendb.php");
include("test.php");
?>
<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>";
if($show)
{
	$dt=$y.'-'.$m.'-'.$d;
	$s="select date_add('$dt', interval -30 day)";
	$q1=mysqli_query($db,$s)or die(mysqli_error($db));
	$row30=mysqli_fetch_array($q1)or die(mysqli_error($db));
	$d_30=$row30[0];
	$q2=mysqli_query($db,"select date_add('$dt', interval -60 day)");
	$row30=mysqli_fetch_array($q2);
	$d_60=$row30[0];
	$q3=mysqli_query($db,"select date_add('$dt', interval -90 day)");
	$row30=mysqli_fetch_array($q3);
	$d_90=$row30[0];
	print "<br> Debtors aging list for ".datetohtml($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 & <60 <th> >=60 & <90 <th> >=90";
	$i=1;
	$t_bal=0;
	$tbl='dbaging'.$br.$user; 
	mysqli_query($db,"delete from $tbl");
	mysqli_query($db,"create table $tbl (partic varchar(100),op double(14,2) default 0,total double(14,2) default 0,less_30 double(14,2)default 0,30_60 double(14,2)default 0,60_90 double(14,2)default 0,more_90 double(14,2)default 0)");
	mysqli_query($db,"insert into $tbl (partic,op,total) select partic,op,(sum(dramt)-sum(cramt)) from journal2,ledger where ledger.led=journal2.partic and journal2.date<='$dt' and ledger.grp='Sundry Debtors' and journal2.brcode='$brcode' and ledger.brcode = '$brcode' group by journal2.partic");
	mysqli_query($db,"insert into $tbl (partic,less_30) select partic,sum(dramt) from journal2,ledger where date>='$d_30' and date<='$dt' and journal2.brcode='$brcode' and ledger.brcode='$brcode' and ledger.led=journal2.partic and ledger.grp = 'Sundry Debtors' group by partic");
	mysqli_query($db,"insert into $tbl (partic,30_60) select partic,sum(dramt) from journal2,ledger where date<'$d_30' and date>='$d_60' and journal2.brcode='$brcode' and ledger.brcode='$brcode' and ledger.led=journal2.partic and ledger.grp = 'Sundry Debtors' group by partic");
	mysqli_query($db,"insert into $tbl (partic,60_90) select partic,sum(dramt) from journal2,ledger where date<'$d_60' and date>='$d_90' and journal2.brcode='$brcode' and ledger.brcode='$brcode' and ledger.led=journal2.partic and ledger.grp = 'Sundry Debtors' group by partic");
	mysqli_query($db,"insert into $tbl (partic,more_90) select partic,sum(dramt) from journal2,ledger where date<'$d_90' and journal2.brcode='$brcode' and ledger.brcode='$brcode' and ledger.led=journal2.partic and ledger.grp = 'Sundry Debtors' group by partic");
	$re=mysqli_query($db,"select partic as led,sum(total) as total,sum(op) as op, sum(less_30) as less_30,sum(30_60) as ag30_60,sum(60_90) as ag60_90,sum(more_90) as more_90 from $tbl group by partic")or die(mysqli_error($db));	
	while($srow=mysqli_fetch_array($re))
	{
		$opbal=$srow[op];
		$balrow[0]=$srow[total];
		$balance=$opbal+$balrow[0];

		if($balance>0)
		{
			$t_bal+=$balance;
			print "<tr><td>".$i++."</td><td>$srow[led]</td><td align=right>".putComa($balance)."</td>";
			if($srow[total]<=0)
			{
				print "<td>$balance</td>";
			}
			$r3=$srow[less_30];
			//   $r3+=$opbal;
			if($balance>$r3)
			{ 
				print "<td align=right>$r3</td>"; 
				$tot2+=$r3;
   			}
  			else
   			{
				print "<td align=right>".putComa($balance)."</td>";
	      			$tot2+=$balance;
  				continue;
			}
  			$balance-=$r3;
			//************
   			$r3=$srow[ag30_60];
  			if($balance>$r3)
  			{ 
				print "<td align=right> $r3</td>";
    				$tot3+=$r3; 
			}
  			else
   			{
				print "<td align=right>".putComa($balance)."</td>";
     				$tot3+=$balance;
     				continue;
   			}
  			$balance-=$r3;
			//************
   			$r3=$srow[ag60_90];
  			if($balance>$r3)
  			{ 
				print "<td align=right> $r3</td>"; 
				$tot4+=$r3;
			}
  			else
   			{
				print "<td align=right>".putComa($balance)."</td>";
				$tot4+=$balance;
			    	continue;
			}
			$balance-=$r3;
			//************
   			$r3=$srow[more_90];
   			$r3+=$opbal;
  			if($balance>$r3)
  			{ 	
				print "<td align=right> $r3</td>"; $tot5+=$r3;		
			}
			else
   			{
				print "<td align=right>".putComa($balance)."</td>";
				$tot5+=$balance;
				continue;
			}
			$balance-=$r3;
		}
	}
	print "<tr><td colspan=2 align=center>Total</td><td align=right>".putComa($t_bal)."</td>";
	print "<td align=right>".putComa($tot2)."</td>";
	print "<td align=right>".putComa($tot3)."</td>";
	print "<td align=right>".putComa($tot4)."</td>";
	print "<td align=right>".putComa($tot5)."</td>";
	?>
	</table>
	<?
	return;
} // end if show
?>
<center>
<form action='<?Print $PHP_SELF?>' method='post'>
<table cellpadding=3>
<th colspan=3>Debtors aging list</th>
<tr><td>Date </td><td>
dd <input type=text name='d' size=2 value='<?print date('d')?>'>
mm <input type=text name='m' size=2 value='<?print date('m')?>'>
yy <input type=text name='y' size=4 value='<?print date('Y')?>'>
</td><td><input type=submit value='Show' name='show'></td></tr>
<tr><th colspan=3></th></tr>
</table>
</form>

