<?
session_start();
?>
<link rel="stylesheet" href="../style.css" type="text/css" />
<style>
body,td{font-family:verdana narrow;font-size:14;background-color:white}
th{background-color:silver};
</style>
<center>
<?

$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");

print "<td><b>".$branch."</b></td>";
print "<br > <td align=center>Billwise supplier aging list as on ".$dt."</td><br>[printed by $user]";

$rew=mysqli_query($db,"select distinct(brcode) from branch order by brcode")or die(mysqli_error($db));
while($sroww=mysqli_fetch_array($rew))
{

$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;

$r=mysqli_query($db,"select pbillno,suplier,purdate,grno,sum(tot+cessamt) from sparepurchase where brcode='$sroww[0]' 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));

if(mysqli_num_rows($r)>0)
{

$ltbl="billwise".$sroww[0].$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)+avg(cessamt) from sparepurchase where brcode='$sroww[0]' 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,sum(tot)+avg(cessamt) from gen4psnauto_psn_ampleicher2223.sparepurchase where brcode='$sroww[0]' and entrydate <='$dt' and entrydate >='2023-02-01' 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,sum(tot)+avg(cessamt) from sparepurchase where brcode='$sroww[0]' and entrydate <='$dt' and suplier like '%Valvoline%' 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,sum(tot)+avg(cessamt) from gen4psnauto_psn_ampleicher2223.sparepurchase where brcode='$sroww[0]' and entrydate <='$dt' and entrydate >='2023-02-01' and suplier like '%Valvoline%' 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 gen4psnauto_psn_ampleicher2223.sparepurchase where brcode='$sroww[0]' and entrydate <='$dt' and entrydate <='2023-02-01' 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,recd_date,recd_amt,invdate) select invno,customer,date,sum(amt),date from downpayment_sup where brcode='$sroww[0]' and date >'2023-01-01' 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', '')");

?>

<?
/*$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='$sroww[0]' 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 "<table border=1 cellspacing=1 cellpadding=3 bgcolor='gray'>";
$dt=date("Y-m-d");



print "<th>Document No.<th>Document date<th>Billing type <th>Total amount <th> Days due <th>Days past Due  <th>Paid Amount<th>Paid Date<th>Paid After (days)<th>Branch Name";
$re=mysqli_query($db,"select invno,customer,invdate,sum(inv_amount),sales_person,due_amount,(to_days('$dt')-to_days(invdate)),sum(recd_amt),recd_date from $ltbl group by customer,invdate,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))
{
    $invamt=round($srow[3]);
    $invamt=number_format($invamt,2,".","");
    
    $mdate = implode('-', array_reverse(explode('-', $srow[2])));
$du='';
$dupast='';
$due=30-$srow[6];

if($due>0)
{
$du=$due;
}
else
{
    if($invamt>$srow[7])
    {
 $dupast=abs($due);   
}
    
}

    if($invamt==$srow[7])
    {
 $du='';   
}

    $q=mysqli_query($db,"select recd_date from $ltbl where  invno='$srow[0]' order by recd_date desc");
    $r2=mysqli_fetch_array($q);
    $rcd=$r2[0];
    $bdt=$srow[2];
     $qq=mysqli_query($db,"select (to_days('$rcd')-to_days('$bdt'))  from $ltbl where  invno='$srow[0]' order by recd_date desc");
    $r2q=mysqli_fetch_array($qq);

		print "<tr><td align='right'><b>$srow[0]</td><td align='right'><b>".datetohtml($srow[2])."</td><td align='right'><b>Lubricant</td><td align='right'><b>$invamt</td><td align='right'><b>$du</td><td align='right'><b>$dupast</td><td align='right'><b>$srow[7]</td><td align='right'><b>".datetohtml($r2[0])."</td><td align=center><b>$r2q[0]</td><td  align=center><b>".getbrname($sroww[0],$db)."</td>";
		$inv_amount_tot = 0;
		$days45_tot = 0;
		$days60_tot = 0;
		$days90_tot = 0;
		$days180_tot = 0;
		$daysmore_tot =0;
	}


	$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>
<?
}
}
