<?
session_start();
$brcode=$_SESSION['brcode'];
$br = $_SESSION['br'];
$user = $_SESSION['user'];
$branch= $_SESSION['branch'];
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>";
$show = $_REQUEST['show'];
$d = $_REQUEST['d'];
$m = $_REQUEST['m'];
$y = $_REQUEST['y'];
$p1 = $_REQUEST['p1'];
$p2 = $_REQUEST['p2'];
$p3 = $_REQUEST['p3'];
if($show)
{
	$dt=$y.'-'.$m.'-'.$d;
	$s="select date_add('$dt', interval -'$p1' day)";
	$q1=mysqli_query($db,$s)or die(mysqli_error());
	$row30=mysqli_fetch_array($q1)or die(mysqli_error());
	$d_30=$row30[0];
	$q2=mysqli_query($db,"select date_add('$dt', interval -'$p2' day)");
	$row30=mysqli_fetch_array($q2);
	$d_60=$row30[0];
	$q3=mysqli_query($db,"select date_add('$dt', interval -'$p3' day)");
	$row30=mysqli_fetch_array($q3);
	$d_90=$row30[0];
	print "<br> Debtors aging list for ".implode('-', array_reverse(explode('-', $dt)))."<br>[printed by $user]";
	print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='gray'>";
	print "<th>No. <th>Debtor<th>Total <th><$p1 <th> >= $p1 & < $p2<th> >= $p2 & < $p3<th> >= $p3";
	
	$i=1;
	$t_bal=0;
	$tbl='dbaging'.$br.$user; 
	
	mysqli_query($db,"delete from $tbl");
	mysqli_query($db,"drop table $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,op_drcr varchar(2))");
	mysqli_query($db,"insert into $tbl (partic,op,op_drcr) select led,op,drcr from ledger where grp='Sundry Debtors' and brcode = '$brcode' and op!=0 group by led");
	mysqli_query($db,"insert into $tbl (partic,total) select partic,(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=journal2.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.brcode=journal2.brcode 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.brcode=journal2.brcode 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.brcode=journal2.brcode 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.brcode=journal2.brcode 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,op_drcr from $tbl group by partic")or die(mysqli_error());	
	while($srow=mysqli_fetch_array($re))
	{
	    if($srow['op_drcr']=='Cr')
	        $balance=($srow['op']*-1)+$srow['total'];
	    else
	     	$balance=$srow['op']+$srow['total'];
		$opbal=$srow['op'];
		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></tr>

<tr><td>Period </td><td>
Days <input type=text name='p1' size=2 value='30'>
Days <input type=text name='p2' size=2 value='60'>
Days <input type=text name='p3' size=4 value='90'>
</td>

<td><input type=submit value='Show' name='show'></td></tr>
<tr><th colspan=3></th></tr>
</table>
</form>

