<?
session_start();
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='../index.html'; </script>";
 return;
}
//include("../opendb.php");
//include("../test.php");

function exqty1($pcode,$brcode,$user)
{
$tbl="sparetmp_br".$user;
mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),supqty double(10,2),brcode varchar(20))");
mysqli_query($db,"delete from $tbl")or die('delerr'.mysqli_error($db));

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

$re=mysqli_query($db,$sq)or die(mysqli_error($db));


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

$sqq="select itemcode,itemname,qty,brcode from spareopstock  where itemcode='$pcode' order by brcode";

$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]' 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($db));
  }
  else
   	{
	$sql="insert into $tbl values('$ress[0]','".addslashes(trim($ress[1]))."','$ress[2]','$ress[brcode]')";
	mysqli_query($db,$sql)or die(mysqli_error($db));
   	}
}

$sqq="select partcode,partname,sum(qty),brcode from salesreturnitem  where partcode='$pcode' group by brcode,partcode";

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

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($db));
  }
  else
   	{
	$sql="insert into $tbl values('$ress[0]','$ress[1]','$ress[2]','$ress[3]')";
	mysqli_query($db,$sql)or die(mysqli_error($db));
   	}
}

$sq6="select partcode,partname,sum(recqty),brcode from sparetrinwarph where partcode='$pcode' group by  brcode,partcode";
$phre52=mysqli_query($db,$sq6);
while($res5=mysqli_fetch_array($phre52))
{
 $seareph=mysqli_query($db,"select * from $tbl where partcode='$res5[partcode]' and brcode='$res5[brcode] ");
 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]')";
mysqli_query($db,$sql)or die('err10'.mysqli_error($db));
}


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

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($db));
  }
  else
   	{
	$sql="insert into $tbl values('$res1[0]','$res1[1]','$res1[2]','$res1[brcode]')";
	mysqli_query($db,$sql)or die(mysqli_error($db));
   	}
}

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

 $sq8="select sum(qty),partcode,partname,brcode from adjstock where partcode='$pcode' group by brcode,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]' 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($db));
  }
  else
   	{
	$sql="insert into $tbl values('$res5[1]','".addslashes(trim($res5[2]))."','$res5[0]','$res5[brcode]')";
	mysqli_query($db,$sql)or die('upd adj err'.mysqli_error($db));
   	}
}


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

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


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]')";
}
//print $sql.'<br>';
mysqli_query($db,$sql)or die('trans out'.mysqli_error($db));
}


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


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

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


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]')";
}


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

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

$re211=mysqli_query($db,$sq211)or die('Err3:'.mysqli_error($db));


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


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


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

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


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]')";
}

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


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

$sq2="select partcode,partname,sum(retqty),brcode from sparepurreturn where partcode='$pcode' group by  brcode,partcode";
$re22=mysqli_query($db,$sq2)or die('err6'.mysqli_error($db));
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]'";
mysqli_query($db,$sql)or die('err6'.mysqli_error($db));
}
else
{
$sql="insert into $tbl values('$res2[0]','$res2[1]','-$res2[2]','$res2[brcode]')";
}

}

//************ 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='$pcode' group by partcode";

$re5a=mysqli_query($db,$sq5); //or die('War Err'.mysqli_error($db));
while($res5=mysqli_fetch_array($re5a))

{
   $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[2].") where partcode='$res5[partcode]' and brcode='$res5[brcode]'";
}
else
{
$sql="insert into $tbl values('$res5[0]','$res5[1]','-$res5[2]','$res5[brcode]')";
}

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

//************** war trans out
$sq16="select partcode,partname,sum(psnqty),brcode from warsparetransout where partcode='$pcode' group by brcode, partcode";
$re521=mysqli_query($db,$sq16);
while($res501=mysqli_fetch_array($re521))
{
   $chkin=mysqli_query($db,"select * from $tbl where partcode='$res501[partcode]' and brcode='$res501[brcode]'");
 if(mysqli_num_rows($chkin)>0)
{

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

}
//print $sql.'<br>';
mysqli_query($db,$sql)or die('err101'.mysqli_error($db));
}


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

$sq5="select partcode,partname,sum(qty),brcode from spareinvoicefoc where partcode='$pcode' group by  brcode,partcode";
$re53=mysqli_query($db,$sq5)or die('err6'.mysqli_error($db));
while($res5=mysqli_fetch_array($re53))
{
   $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[2].") where partcode='$res5[partcode]' and brcode='$res5[brcode]'";
mysqli_query($db,$sql)or die('err12'.mysqli_error($db));
}
else
{
$sql="insert into $tbl values('$res5[0]','$res5[1]','-$res5[2]','$res5[brcode]')";

}

}

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


 $sq9="select sum(outqty),partcode,brcode from adjstock where partcode='$pcode' group by brcode,partcode";

 $re513=mysqli_query($db,$sq9)or die(mysqli_error($db));
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[0]','$res5[1]','-$res5[2]','$res5[brcode]')";
}
mysqli_query($db,$sql)or die('err12'.mysqli_error($db));
}



$sq="select * from $tbl where brcode='ERA153'";

$rem_1=mysqli_query($db,$sq)or die('Err'.mysqli_error($db));
$row=mysqli_fetch_array($rem_1);

return $row[supqty];

}// end function
