<?
include_once('includes/top.php');
include_once('opendb.php');
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
if(strlen($brcode)<=0)
{
 print "<script> alert('Session timed out'); parent.document.location='inde.php'; </script>";
 return;
}
$dfrom=$y1.'-'.$m1.'-'.$d1;
$dto=$y2.'-'.$m2.'-'.$d2;
$tbl="sparetmpdw".$brcode.$user;
mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),op_inqty double(10,2) default 0,op_outqty double(10,2) default 0, stkadj_in double(10,2) default 0, stkadj_out double(12,2) default 0,purch_in double(12,2) default 0,salertn_in double(12,2) default 0,trans_in double(12,2) default 0,trans_out double(12,2) default 0,sale_out double(12,2) default 0,purchrtn_out double(12,2) default 0)");
mysqli_query($db,"delete from $tbl");
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,op_outqty) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' group by partcode") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select item_code,item,sum(qty) from itempurchase where brcode='$brcode'  group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select itemcode,itemname,sum(opstk) from sparestock where brcode='$brcode' group by itemcode") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select item_code,item,sum(rtnqty) from salesretn where brcode='$brcode' and itemtyp='N' group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and itemtyp='N'  group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select item_code,item,sum(qty) from brtransferin where brcode='$brcode'  group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and itemtyp='N'  group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from purchretn where brcode='$brcode'  group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,op_outqty) select itemcode,sum(nor_qty) from qtytransf where brcode='$brcode' group by itemcode") or die('err12'.mysqli_error($db));
$checktable = mysqli_query($db,"SHOW TABLES LIKE 'stockverification'");
if (mysqli_num_rows($checktable) > 0)
{
    mysqli_query($db,"insert into $tbl (partcode,op_outqty,op_inqty) select itemcode,sum(stock_short),sum(stock_excess) from stockverification where brcode='$brcode' group by itemcode") or die('err12'.mysqli_error($db));
}
$re=mysqli_query($db,"select partcode,partname,sum(op_inqty),sum(op_outqty),sum(purch_in),sum(trans_in),sum(salertn_in),sum(stkadj_in),sum(sale_out),sum(trans_out),sum(purchrtn_out),sum(stkadj_out) from $tbl group by partcode");
$i=1;
$tprice=0;
while($row=mysqli_fetch_array($re))
{
  $op_qty = $row[2]-$row[3];
       //$sql="update sparestock set curstk = $op_qty where itemcode='$row[partcode]' and brcode='$brcode'";
       //echo $sql;
       //echo "<br>";
  mysqli_query($db,"update sparestock set curstk = $op_qty where itemcode='$row[partcode]' and brcode='$brcode'");
}
print "Stock updated successfully!";

mysqli_query($db,"drop table $tbl");
mysqli_query($db,"CREATE TABLE $tbl (partcode varchar(200),partname varchar(200),op_inqty double(10,2) default 0,op_outqty double(10,2) default 0, stkadj_in double(10,2) default 0, stkadj_out double(12,2) default 0,purch_in double(12,2) default 0,salertn_in double(12,2) default 0,trans_in double(12,2) default 0,trans_out double(12,2) default 0,sale_out double(12,2) default 0,purchrtn_out double(12,2) default 0)");
mysqli_query($db,"delete from $tbl");
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select itemcode,itemname,sum(opstk_dam) from sparestock where brcode='$brcode' group by itemcode") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty) select item_code,item,sum(rtnqty) from salesretn where brcode='$brcode' and itemtyp='D' group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and itemtyp='D'  group by item_code") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,op_inqty) select itemcode,sum(dam_qty) from qtytransf where brcode='$brcode' group by itemcode") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and itemtyp='D'  group by item_code") or die('err12'.mysqli_error($db));
$re=mysqli_query($db,"select partcode,partname,sum(op_inqty),sum(op_outqty),sum(purch_in),sum(trans_in),sum(salertn_in),sum(stkadj_in),sum(sale_out),sum(trans_out),sum(purchrtn_out),sum(stkadj_out) from $tbl group by partcode");
$i=1;
$tprice=0;
while($row=mysqli_fetch_array($re))
{
       $op_qty = $row[2]-$row[3];
       mysqli_query($db,"update sparestock set curstk_dam = $op_qty where itemcode='$row[partcode]' and brcode='$brcode'");
}
print " Damage Stock updated successfully!";
?>