<?
session_start();
include("../opendb.php");
include("../test.php");
?>

<center>
<?
print $branch;
$tbl="agesparetmp".$brcode.$user;

mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),supqty double(10,2),pdate int)");
mysqli_query($db,"delete from $tbl");

$sq="select partcode,partname,sum(supqty) from sparepurchase where brcode='$brcode' group by  partcode";
$re=mysqli_query($db,$sq);


while($res=mysqli_fetch_array($re))
{
  $sql="insert into $tbl values('$res[0]','$res[1]','$res[2]','')"; 
  mysqli_query($db,$sql);
}

$sqq="select itemcode,itemname,sum(qty) from spareopstock where brcode='$brcode' group by  itemcode";
$ree=mysqli_query($db,$sqq)or die('ERr spareopstock'.mysqli_error($db));

while($ress=mysqli_fetch_array($ree))
{
  $rr=mysqli_query($db,"select * from $tbl where partcode='$ress[itemcode]'");
  if(mysqli_num_rows($rr)>0)
  {
	$inrow=mysqli_fetch_array($rr);
    	$sql="update $tbl set supqty=(supqty+".$ress[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql);
  }
  else
   	{
	$sql="insert into $tbl values('$ress[0]','".addslashes(trim($ress[1]))."','$ress[2]','')";
	mysqli_query($db,$sql);
   	}
}

//*********** adj stock
 $sq8="select sum(qty),partcode,partname from adjstock where brcode='$brcode' group by partcode";

 $re512=mysqli_query($db,$sq8)or die('ERr adjtock'.mysqli_error($db));

while($res5=mysqli_fetch_array($re512))
{
  $rr=mysqli_query($db,"select * from $tbl where partcode='$res5[partcode]'");
  if(mysqli_num_rows($rr)>0)
  {
	$inrow=mysqli_fetch_array($rr);
    	$sql="update $tbl set supqty=(supqty+".$res5[0].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql);
  }
  else
   	{
	$sql="insert into $tbl values('$res5[1]','".addslashes(trim($res5[2]))."','$res5[0]','')";
	mysqli_query($db,$sql)or die('adj in err:'.mysqli_error($db));
   	}
}

//********************sale return
$sqq="select partcode,partname,sum(qty) from salesreturn where brcode='$brcode' group by  partcode";
$ree=mysqli_query($db,$sqq);

while($ress=mysqli_fetch_array($ree))
{
  $rr2=mysqli_query($db,"select * from $tbl where partcode='$ress[partcode]'");
  if(mysqli_num_rows($rr2)>0)
  {
	$inrow=mysqli_fetch_array($rr2);
    	$sql="update $tbl set supqty=(supqty+".$ress[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql);
  }
  else
   	{
	$sql="insert into $tbl values('$ress[0]','$ress[1]','$ress[2]','')";
	mysqli_query($db,$sql);
   	}
}



$sq1="select partcode,partname,sum(qty) from sparetransin where brcode='$brcode' group by  partcode";
$re1=mysqli_query($db,$sq1);

while($res1=mysqli_fetch_array($re1))
{
  $rr3=mysqli_query($db,"select * from $tbl where partcode='$res1[partcode]'");
  if(mysqli_num_rows($rr3)>0)
  {
    $inrow=mysqli_fetch_array($rr3);
	$sql="update $tbl set supqty=(supqty+".$res1[2].") where partcode='$inrow[partcode]'";
	mysqli_query($db,$sql);
  }
  else
   	{
	$sql="insert into $tbl values('$res1[0]','$res1[1]','$res1[2]','')";
	mysqli_query($db,$sql);
   	}
}
$sq6="select partcode,partname,sum(psnqty) from sparetrinwarph where brcode='$brcode' group by  partcode";
$phre52=mysqli_query($db,$sq6);
while($res5=mysqli_fetch_array($phre52))
{
 $seareph=mysqli_query($db,"select * from $tbl where partcode='$res5[partcode]'");
 if(mysqli_num_rows($seareph)>0)
$sql="update $tbl set supqty=(supqty+".$res5[2].") where partcode='$res5[partcode]'";
 else
$sql="insert into $tbl values('$res5[0]','".addslashes(trim($res5[1]))."','$res5[2]','')";

mysqli_query($db,$sql)or die('err10'.mysqli_error($db));
}


$sq2="select partcode,partname,sum(qty) from sparetransout where brcode='$brcode' group by  partcode";

$re2=mysqli_query($db,$sq2);


while($res2=mysqli_fetch_array($re2))
{
 $cch=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]'");
  if(mysqli_num_rows($cch)>0)
   {
     $sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";
     mysqli_query($db,$sql)or die('err2'.mysqli_error($db));
   }
   else
    {
        $sq="insert into $tbl values('$res2[0]','$res2[1]','$res2[2]','')";
         mysqli_query($db,$sq) or die('tranout err'.mysqli_error($db));

    }
}


$sq2="select partcode,partname,sum(qty) from spareinvoice where brcode='$brcode' group by  partcode";

$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));


while($res2=mysqli_fetch_array($re21))
{
 
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";

mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
}

//************

$sq2="select partcode,partname,sum(qty) from warspareinvoice where brcode='$brcode' group by  partcode";

$re21=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));


while($res2=mysqli_fetch_array($re21))
{
 
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";

mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
}

//************


$sq2="select partcode,partname,sum(qty) from jcardparts where brcode='$brcode' group by  partcode";

$re212=mysqli_query($db,$sq2)or die('Err3:'.mysqli_error($db));


while($res2=mysqli_fetch_array($re212))
{
 
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";

mysqli_query($db,$sql)or die('err4'.mysqli_error($db));
}


//*****************

$sq2="select partcode,partname,sum(retqty) from sparepurreturn where brcode='$brcode' group by  partcode";
$re23=mysqli_query($db,$sq2);
while($res2=mysqli_fetch_array($re23))
{
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]'";
mysqli_query($db,$sql)or die('err6'.mysqli_error($db));
}

//************ transout -war
$tbl1="spareinvwar".$brcode;
$sq5="select partcode,partname,sum(psnqty) from $tbl1  where trans='n' group by  partcode";
$re51=mysqli_query($db,$sq5);
while($res5=mysqli_fetch_array($re51))
{
$sql="update $tbl set supqty=(supqty-".$res5[2].") where partcode='$res5[partcode]'";
mysqli_query($db,$sql)or die('err8'.mysqli_error($db));
}


//*********** adj stock
 $sq9="select sum(outqty),partcode from adjstock where brcode='$brcode' group by partcode";

 $re513=mysqli_query($db,$sq9)or die(mysqli_error($db));
while($res5=mysqli_fetch_array($re513))
{
$sql="update $tbl set supqty=(supqty-".$res5[0].") where partcode='$res5[partcode]'";

mysqli_query($db,$sql)or die('err12'.mysqli_error($db));
}


//************** war trans out
$sq16="select partcode,partname,sum(psnqty) from warsparetransout where brcode='$brcode' group by  partcode";
$re521=mysqli_query($db,$sq16);
while($res501=mysqli_fetch_array($re521))
{
$sql="update $tbl set supqty=(supqty-".$res501[2].") where partcode='$res501[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sql)or die('err101'.mysqli_error($db));
}

//************ foc invoice

$sq5="select partcode,partname,sum(qty) from spareinvoicefoc where brcode='$brcode' group by  partcode";
$re53=mysqli_query($db,$sq5);
while($res5=mysqli_fetch_array($re53))
{
$sql="update $tbl set supqty=(supqty-".$res5[2].") where partcode='$res5[partcode]'";
mysqli_query($db,$sql)or die('err12'.mysqli_error($db));
}

//***********************
print "<table border=1><th>Part Code <th>Count <th><=30 <th>>30&<=60<th>>60&<=90 <th>>90&<120 <th>>120";
mysqli_query($db,"delete from $tbl where supqty=0");
$agres=mysqli_query($db,"select * from $tbl group by partcode order by partcode")or die(mysqli_error($db));
while($agerow=mysqli_fetch_array($agres))
{
   print "<tr><td>$agerow[partcode]-$agerow[partname]</td><td>$agerow[supqty]</td>";



//*******************************88888888888*************8

 $sql="select sum(supqty) from sparepurchase where brcode='$brcode' and to_days(curdate())-to_days(entrydate)<=30 and partcode ='$agerow[partcode]' group by partcode";

  $dres=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
 if(mysqli_num_rows($dres)<=0)
 {
 $sql="select sum(qty) from sparetransin where brcode='$brcode' and to_days(curdate())-to_days(entrydate)<=30 and partcode ='$agerow[partcode]' group by partcode";
  $drest=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
  $drow=mysqli_fetch_array($drest);
}
else
  $drow=mysqli_fetch_array($dres);
  $qnow=$agerow[supqty];
  if($drow[0]<=$agerow[supqty])
   {
     print "<td>$drow[0]</td>";
     mysqli_query($db,"update $tbl set supqty=(supqty-'".$drow[0]."') where partcode='$agerow[partcode]'")or die('up1'.mysqli_error($db));
 $qnow-=$drow[0];
 }
 else
 {
   print "<td>$agerow[supqty]</td>";
   continue;
 }

 $sql="select sum(supqty) from sparepurchase where brcode='$brcode' and to_days(curdate())-to_days(entrydate)>=30 and  to_days(curdate())-to_days(entrydate)<=60 and partcode ='$agerow[partcode]' group by partcode";

  $dres1=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
 if(mysqli_num_rows($dres1)<=0)
 {
 $sql="select sum(qty) from sparetransin where brcode='$brcode' and to_days(curdate())-to_days(entrydate)<=30 and partcode ='$agerow[partcode]' group by partcode";
  $drest1=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
  $drow=mysqli_fetch_array($drest1);
}
else
  $drow=mysqli_fetch_array($dres1);
  if($drow[0]<=$qnow)
   {
     print "<td>$drow[0]</td>";
     mysqli_query($db,"update $tbl set supqty=(supqty-'".$drow[0]."') where partcode='$agerow[partcode]'")or die('up2'.mysqli_error($db));
 $qnow-=$drow[0];
}
 else
 {
   print "<td>$qnow</td>";
     mysqli_query($db,"update $tbl set supqty=(supqty-'".$drow[0]."') where partcode='$agerow[partcode]'")or die('up2'.mysqli_error($db));

   continue;
 }


 $sql="select sum(supqty) from sparepurchase where brcode='$brcode' and to_days(curdate())-to_days(entrydate)>=60 and  to_days(curdate())-to_days(entrydate)<=90 and partcode ='$agerow[partcode]' group by partcode";

  $dres2=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
 if(mysqli_num_rows($dres2)<=0)
 {
 $sql="select sum(qty) from sparetransin where brcode='$brcode' and to_days(curdate())-to_days(entrydate)<=60 and partcode ='$agerow[partcode]' group by partcode";
  $drest11=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
  $drow=mysqli_fetch_array($drest11);
}
else
  $drow=mysqli_fetch_array($dres2);
  if($drow[0]<=$qnow)
   {
     print "<td bgcolor='ivory'>$drow[0]</td>";
     mysqli_query($db,"update $tbl set supqty=(supqty-'".$drow[0]."') where partcode='$agerow[partcode]'")or die('up3'.mysqli_error($db));
 $qnow-=$drow[0];
}
 else
 {
   print "<td bgcolor='ivory'>$qnow</td>";
   continue;
 }


 $sql="select sum(supqty) from sparepurchase where brcode='$brcode' and to_days(curdate())-to_days(entrydate)>=90 and partcode ='$agerow[partcode]' group by partcode";
//print $sql.'<br>';
  $dres3=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
 if(mysqli_num_rows($dres3)<=0)
 {
 $sql="select sum(qty) from sparetransin where brcode='$brcode' and to_days(curdate())-to_days(entrydate)>=90 and to_days(curdate())-to_days(entrydate)<=120 and partcode ='$agerow[partcode]' group by partcode";
  $drest13=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
  $drow=mysqli_fetch_array($drest13);
}
else
  $drow=mysqli_fetch_array($dres3);

  if($drow[0]<=$qnow)
   {
     print "<td align=right>".putComa($drow[0])."<font color='red'> | </font><font color='brown'> ".putComa($drow[0]*getPrice($agerow[partcode]))."</font></td>";
   $sq="update $tbl set supqty=(supqty-'".$drow[0]."') where partcode='$agerow[partcode]'";
      mysqli_query($db,$sq)or die(mysqli_error($db));
 $qnow-=$drow[0];
}
 else
 {
   print "<td align=right>".putComa($qnow)."<font color='red'> | </font><font color='brown'> ".putComa($qnow*getPrice($agerow[partcode]))."</td>";
   continue;
 }


//**************8 op
 $sql="select sum(qty) from spareopstock where brcode='$brcode' and itemcode ='$agerow[partcode]' group by itemcode";
$dresop=mysqli_query($db,$sql)or die('Er'.mysqli_error($db));
  $drow=mysqli_fetch_array($dresop);
  if($drow[0]<=$qnow)
   {
     print "<td>".putComa($drow[0])."<font color='red'> | </font><font color='brown'> ".putComa($drow[0]*getPrice($agerow[partcode]))."</td>";
     mysqli_query($db,"update $tbl set supqty=(supqty-'".$drow[0]."') where partcode='$agerow[partcode]'")or die('up1'.mysqli_error($db));
 $qnow-=$drow[0];
 }
 else
 {
   print "<td align=right>".putComa($qnow)."<font color='red'> | </font><font color='brown'> ".putComa($qnow*getPrice($agerow[partcode]))."</td>";
   continue;
 }

}



return;
function getPrice($cde)
{
  $prres=mysqli_query($db,"select lstprice from spareprmast where partcode='$cde'");
  $prrow=mysqli_fetch_array($prres);
  return $prrow[0];
}
//************ trans in war

$sq6="select partcode,partname,sum(psnqty) from sparetrinwar where brcode='$brcode' group by  partcode";
$re52=mysqli_query($db,$sq6);
while($res5=mysqli_fetch_array($re52))
{
$sql="update $tbl set supqty=(supqty+".$res6[2].") where partcode='$res6[partcode]'";
mysqli_query($db,$sql)or die('err10'.mysqli_error($db));
}
/*
//************** war trans out
$sq16="select partcode,partname,sum(psnqty) from warsparetransout where brcode='$brcode' group by  partcode";
$re521=mysqli_query($db,$sq16);
while($res501=mysqli_fetch_array($re521))
{
$sql="update $tbl set supqty=(supqty-".$res501[2].") where partcode='$res501[partcode]'";
//print $sql.'<br>';
mysqli_query($db,$sql)or die('err101'.mysqli_error($db));
}

*/

?>
