<?
session_start();
include("../opendb.php");
include("../test.php");
?>
<link rel="stylesheet" href="../style.css" type="text/css" />

<style>
        td{font-size:15}
	th{background-color:steelblue;color:white;font-size:15}
</style>

<center>
<?
//include("../spares/stockrepo_br2.php");

$tbl="mini".$user;

mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),supqty double(10,2),brcode varchar(20),minstk double(10,2))");
mysqli_query($db,"delete from $tbl")or die('delerr'.mysqli_error());

$q="select partcode,partname,qty from minstock where brcode='$brcode' order by partcode";
$e=mysqli_query($db,$q)or die('ERr spareopstock'.mysqli_error());
while($s=mysqli_fetch_array($e))
{

$sq="select partcode,partname,sum(supqty),brcode from sparepurchase where partcode='$s[0]' and brcode='$brcode' group by brcode";
$re=mysqli_query($db,$sq)or die(mysqli_error());
  if(mysqli_num_rows($re)<=0)
{
 $sql="insert into $tbl values('$s[0]','aaaaa','0','$brcode',$s[2])"; 
//mysqli_query($db,$sql);
}

while($res=mysqli_fetch_array($re))
{
  $sql="insert into $tbl values('$res[0]','$res[1]','$res[2]','$res[3]','$s[2]')"; 
  mysqli_query($db,$sql)or die('Purchase ERR:'.mysqli_error());
}



$sqq="select itemcode,itemname,qty,brcode from spareopstock  where itemcode='$s[0]' and brcode='$brcode' group by brcode ";

$ree=mysqli_query($db,$sqq)or die('ERr spareopstock'.mysqli_error());

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

//*********** adj stock

 $sq8="select sum(qty),partcode,partname,brcode from adjstock where partcode='$s[0]' and brcode='$brcode' group by brcode ";

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

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

$sq6="select partcode,partname,sum(psnqty),brcode from sparetrinwarph where partcode='$s[0]' and brcode='$brcode' group by brcode";
$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]' and brcode='$res5[brcode]'";
 else
$sql="insert into $tbl values('$res5[0]','".addslashes(trim($res5[1]))."','$res5[2]','$res5[brcode]','$s[2]')";
mysqli_query($db,$sql)or die('err10'.mysqli_error());
}

$sqq="select partcode,partname,sum(qty),brcode from salesreturn  where partcode='$s[0]' and brcode='$brcode' group by brcode";

$ree=mysqli_query($db,$sqq)or die('err416'.mysqli_error());

while($ress=mysqli_fetch_array($ree))
{
  $rr34=mysqli_query($db,"select * from $tbl where partcode='$ress[partcode]' and brcode='$ress[brcode]'");
  if(mysqli_num_rows($rr34)>0)
  {
	$inrow1=mysqli_fetch_array($rr34);
    	$sql="update $tbl set supqty=(supqty+".$ress[2].") where partcode='$inrow1[partcode]' and brcode='$inrow1[brcode]'";
	mysqli_query($db,$sql)or die('++ Err:'.mysqli_error());
  }
  else
   	{
	$sql="insert into $tbl values('$ress[0]','$ress[1]','$ress[2]','$ress[3]','$s[2]')";
	mysqli_query($db,$sql)or die(mysqli_error());
   	}
}

$sq1="select partcode,partname,sum(qty),brcode from sparetransin  where partcode='$s[0]' and brcode='$brcode' group by brcode";
$re1=mysqli_query($db,$sq1)or die('err46'.mysqli_error());

while($res1=mysqli_fetch_array($re1))
{
  $rr99=mysqli_query($db,"select * from $tbl where partcode='$res1[partcode]' and brcode='$res1[brcode]'");
  if(mysqli_num_rows($rr99)>0)
  {
    $inrow2=mysqli_fetch_array($rr99);
	$sql="update $tbl set supqty=(supqty+".$res1[2].") where partcode='$inrow2[partcode]' and brcode='$inrow2[brcode]'";
	mysqli_query($db,$sql)or die(mysqli_error());
  }
  else
   	{
	$sql="insert into $tbl values('$res1[0]','$res1[1]','$res1[2]','$res1[brcode]','$s[2]')";
	mysqli_query($db,$sql)or die(mysqli_error());
   	}
}


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

$re2=mysqli_query($db,$sq2)or die('err54'.mysqli_error());


while($res2=mysqli_fetch_array($re2))
{
 $chktr=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]' and brcode='$res2[brcode]'");
 if(mysqli_num_rows($chktr)>0)
 {
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]' and brcode='$res2[brcode]'";
}
 else
{
 $sql="insert into $tbl values('$res2[0]','$res2[1]','-$res2[2]','$res2[brcode]','$s[2]')";
}
//print $sql.'<br>';
mysqli_query($db,$sql)or die('trans out'.mysqli_error());
}


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


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

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


while($res2=mysqli_fetch_array($re21))
{
  $chkin=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]' and brcode='$res2[brcode]'");
 if(mysqli_num_rows($chkin)>0)
 {
 $sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]' and 
  brcode='$res2[brcode]'";
 }
 else
{
 $sql="insert into $tbl values('$res2[0]','$res2[1]','-$res2[2]','$res2[brcode]','$s[2]')";
}


mysqli_query($db,$sql)or die('inv Err'.mysqli_error());
}
//************



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

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


while($res2=mysqli_fetch_array($re21))
{
  $chkin=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]' and brcode='$res2[brcode]'");
 if(mysqli_num_rows($chkin)>0)
 {
 $sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]' and 
  brcode='$res2[brcode]'";
 }
 else
{
 $sql="insert into $tbl values('$res2[0]','$res2[1]','-$res2[2]','$res2[brcode]','$s[2]')";
}


mysqli_query($db,$sql)or die('warinv Err'.mysqli_error());
}
//************

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

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


while($res2=mysqli_fetch_array($re212))
{
  $chkin=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]' and brcode='$res2[brcode]'");
 if(mysqli_num_rows($chkin)>0)

{ 
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]' and brcode='$res2[brcode]'";
}
else
{
 $sql="insert into $tbl values('$res2[0]','$res2[1]','-$res2[2]','$res2[brcode]','$s[2]')";
}

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


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

$sq2="select partcode,partname,sum(retqty),brcode from sparepurreturn where partcode='$s[0]' and brcode='$brcode' group by brcode";
$re22=mysqli_query($db,$sq2)or die('err6'.mysqli_error());
while($res2=mysqli_fetch_array($re22))
{
  $chkin=mysqli_query($db,"select * from $tbl where partcode='$res2[partcode]' and brcode='$res2[brcode]'");
 if(mysqli_num_rows($chkin)>0)
{
$sql="update $tbl set supqty=(supqty-".$res2[2].") where partcode='$res2[partcode]' and brcode='$res2[brcode]'";
}
else
{
 $sql="insert into $tbl values('$res2[0]','$res2[1]','-$res2[2]','$res2[brcode]','$s[2]')";

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

//************ transout -war
$brres1=mysqli_query($db,"select brcode from branch");
while($brrow1=mysqli_fetch_array($brres1))
{
$tbl1="spareinvwar".$brrow1[brcode];
$sq5="select partcode,partname,sum(psnqty) from $tbl1  where trans='n' and partcode='$s[0]' and brcode='$brcode' group by brcode";

$re5a=mysqli_query($db,$sq5); //or die('War Err'.mysqli_error());
while($res5=mysqli_fetch_array($re5a))
{
$sql="update $tbl set supqty=(supqty-".$res5[2].") where partcode='$res5[partcode]' and brcode='$brrow1[brcode]'";

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

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


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

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

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


 $sq9="select sum(outqty),partcode,brcode,partname from adjstock where partcode='$s[0]' and brcode='$brcode' group by brcode";

 $re513=mysqli_query($db,$sq9)or die(mysqli_error());
while($res5=mysqli_fetch_array($re513))
{
  $chkin=mysqli_query($db,"select * from $tbl where partcode='$res5[partcode]' and brcode='$res5[brcode]'");
 if(mysqli_num_rows($chkin)>0)
{
$sql="update $tbl set supqty=(supqty-".$res5[0].") where partcode='$res5[partcode]' and brcode='$res5[brcode]'";
}
else
{
$sql="insert into $tbl values('$res5[1]','$res5[3]','-$res5[0]','$res5[brcode]','$s[2]')";
}
mysqli_query($db,$sql)or die('err12'.mysqli_error());
}

}

prn($user);
return;

//******************************************************************
function prn($usr)
{
$tbl="mini".$usr;

print "<center>";
?>
<TR><TD COLSPAN=2 ALIGN=CENTER><b> PSN CONSTRUCTION EQUIPMENTS P LTD </b></tr></td>
<?
$brres=mysqli_query($db,"select brcode,brname from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
print "<tr><td>".$brrow[brname]."</td>";
print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>"; 

print "<th>SNo<th>Part Code <th>Part Name<th>Stock <th>Min.Stock<th>Excess Stk<th>ToOrder<th>LST Price <th>MRP <br>(as on records)";

$re=mysqli_query($db,"select * from $tbl  ")or die('Err'.mysqli_error());
$i=1;
$tprice=0;

while($row=mysqli_fetch_array($re))
{

$exstk=$row[supqty]-$row[minstk];

if ($exstk>0)
{
$estk=$exstk;
}
else
{
$estk=0;
}

if ($exstk<0)
{
$mstk=abs($exstk);
}
else
{
$mstk=0;
}




 print "<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
 print "<td>".$i++."</td>";
 print "\n<td>$row[partcode]</td><td>$row[partname]</td>\n<td align=right>$row[supqty]</td><td align=right>$row[minstk]</td><td align=right>$estk</td><td align=right>$mstk</td>";
 $sq="select location from sparelocmast where itemcode='$row[partcode]' and brcode='$brrow[brcode]'";

  $locres=mysqli_query($db,$sq)or die(mysqli_error());
  $locrow=mysqli_fetch_row($locres);
$prres=mysqli_query($db,"select lstprice,mrp from spareprmast where partcode='$row[partcode]' order by effectfrom desc limit 1");
$prrow=mysqli_fetch_row($prres);
 print "<td align=right>$prrow[0]</td><td align=right>$prrow[1]</td></tr>";
 $totq+=$row[supqty];
 $totlst+=$prrow[0];
 $totmrp+=$prrow[1];
 }
print "<tr><td colspan=3 align=center>Total</td><td colspan=1 align=right>$totq</td>";
print "<td></td><td colspan=3 align=right>".putComa($totlst)."</td> <td align=right>".putComa($totmrp)."</td>";

}
?>

