<?
session_start();
$brcode = $_SESSION['brcode'];
$user = $_SESSION['user'];
$d = $_REQUEST['d'];
$m = $_REQUEST['m'];
$y = $_REQUEST['y'];
$d1 = $_REQUEST['d1'];
$m1 = $_REQUEST['m1'];
$y1 = $_REQUEST['y1'];
if(strlen($brcode)<=0)
{
 	print "<script> alert('Invalid Login'); parent.document.location='ind.php' </script>";
 	return;
}
include_once('includes/top.php');
include("opendb.php");
include("test.php");
$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;
$ltbl="billwise".$brcode.$user;
//$jtbl="journal2".$brcode.$user;
mysqli_query($db,"drop TABLE $ltbl");
//mysqli_query($db,"drop TABLE $jtbl");
mysqli_query($db,"CREATE TABLE $ltbl (invno varchar(30),customer varchar(300),invdate date,duedate date,sales_person varchar(200),inv_amount double(10,2),due_amount double(10,2),30_days double (10,2),60_days double (10,2),90_days double (10,2),more_days double (10,2),recd_date date,recd_amt double(10,2))");
mysqli_query($db,"insert into $ltbl (invno,customer,invdate,sales_person,inv_amount) select pbillno,suplier,purdate,grno,sum(tot) from sparepurchase where brcode='$brcode' and entrydate <='$dt' and suplier in ('Valvoline Cummins Private Limi (0030000281)','Valvoline Cummins Private Limi (0030000240)') and jvno>0 group by pbillno") or die(mysqli_error($db));

mysqli_query($db,"insert into $ltbl (invno,customer,invdate,sales_person,inv_amount) select pbillno,suplier,purdate,grno,cessamt from sparepurchase where brcode='$brcode' and entrydate <='$dt' and suplier in ('Valvoline Cummins Private Limi (0030000281)','Valvoline Cummins Private Limi (0030000240)') and jvno>0 group by pbillno") or die(mysqli_error($db));

//mysqli_query($db,"insert into $ltbl (invno,customer,invdate,sales_person,inv_amount) select invno,customer,entrydate,engineer,sum(tot) from psn_ampleicher1819.spareinvoice where brcode='$brcode' and entrydate <='$dt'group by invno") or die(mysqli_error($db));

mysqli_query($db,"insert into $ltbl (invno,customer,recd_date,recd_amt,invdate) select invno,customer,date,sum(amt),date from downpayment_sup where brcode='$brcode' group by invno,remark") or die(mysqli_error($db));

//mysqli_query($db,"UPDATE $ltbl set customer = replace(customer, '  ', ' ')");
//mysqli_query($db,"UPDATE $ltbl set customer = replace(customer, '0', '')");

?>
<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>
<?
/*$sql12="update $ltbl set due_amount = inv_amount";
$balres1=mysqli_query($db,$sql12)or die('err1'.mysqli_error($db));	
$re=mysqli_query($db,"select invno,customer,invdate from $ltbl")or die(mysqli_error($db));
while($srow=mysqli_fetch_array($re))
{
	$sql="select sum(amt) from downpayment where brcode='$brcode' and invno = '$srow[0]' and remark = '$srow[2]' and customer = '$srow[1]' ";
 	$balres=mysqli_query($db,$sql)or die('err1'.mysqli_error($db));
  	$rrow1=mysqli_fetch_row($balres)or die('err2'.mysqli_error($db));
	if($rrow1[0]>0)
	{
		$sql12="update $ltbl set  due_amount = inv_amount - $rrow1[0] where invno = '$srow[0]' and invdate = '$srow[2]' and customer = '$srow[1]'";
 		$balres1=mysqli_query($db,$sql12)or die('err1'.mysqli_error($db));	
	}
}*/
print "<b>".$branch."</b>";
print "<br> Billwise supplier aging list as on ".$dt."<br>[printed by $user]";
print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='gray'>";
$dt=date("Y-m-d");
print "<th>Invoice No. <th>Customer <th> Invoice Date <th>Due Date <th>Sales Person<th>Invoice Amount<th><=45 <th> >45 & <=60 <th> >60 & <=90 <th> >90 & <=180<th> >180";
$re=mysqli_query($db,"select invno,customer,invdate,sum(inv_amount),sales_person,due_amount,(to_days('$dt')-to_days(invdate)),sum(recd_amt) from $ltbl group by customer,invno")or die(mysqli_error($db));
$inv_amount_tot = 0;
$days45_tot = 0;
$days60_tot = 0;
$days90_tot = 0;
$days180_tot = 0;
$daysmore_tot =0;
$mprev_cus = "";
$ctr=0;
while($srow=mysqli_fetch_array($re))
{
    $mdate = implode('-', array_reverse(explode('-', $srow[2])));
	if($mprev_cus!=$srow[1] && $ctr > 0 && $inv_amount_tot>0)
	{
		print "<tr><td colspan=5  align='center'><b>TOTAL</td><td align='right'><b>$inv_amount_tot</td><td align='right'><b>$days45_tot</td><td align='right'><b>$days60_tot</td><td align='right'><b>$days90_tot</td><td align='right'><b>$days180_tot</td><td align='right'><b>$daysmore_tot</td>";
		$inv_amount_tot = 0;
		$days45_tot = 0;
		$days60_tot = 0;
		$days90_tot = 0;
		$days180_tot = 0;
		$daysmore_tot =0;
	}
	$mdue_amt = $srow[3]-$srow[7];
	if($mdue_amt>1)
	{
		$num1 = $srow[0];

		print "<tr><td>$num1</td><td>$srow[1]</td><td>$mdate</td><td></td><td>$srow[4]</td><td align='right'>$srow[3]</td>";
		if($srow[6]<=45)
		{
			print "<td align='right'>$mdue_amt</td><td></td><td></td><td></td><td></td>";
			$days45_tot += $mdue_amt;
		}
		else if($srow[6]<=60)
		{
			print "<td></td><td align='right'>$mdue_amt</td><td></td><td></td><td></td>";
			$days60_tot += $mdue_amt;
		}
		else if($srow[6]<=90)
		{
			print "<td></td><td></td><td align='right'>$mdue_amt</td><td></td><td></td>";
			$days90_tot += $mdue_amt;
		}
		else if($srow[6]<=180)
		{
			print "<td></td><td></td><td></td><td align='right'>$mdue_amt</td><td></td>";
			$days180_tot += $mdue_amt;
		}
		else
		{
			print "<td></td><td></td><td></td><td></td><td align='right'>$mdue_amt</td>";
			$daysmore_tot += $mdue_amt;
		}
			$inv_amount_tot += $mdue_amt;
	}	
	$mprev_cus = $srow[1];

	$ctr++;
}
print "<tr><td colspan=5  align='center'><b>TOTAL</td><td align='right'><b>$inv_amount_tot</td><td align='right'><b>$days45_tot</td><td align='right'><b>$days60_tot</td><td align='right'><b>$days90_tot</td><td align='right'><b>$days180_tot</td><td align='right'><b>$daysmore_tot</td>";
?>
</table>
