<?
include_once('includes/top.php');
include_once('opendb.php');
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
//$br=$_SESSION['branch'];
if(strlen($user)<=0)
{
  print "<script> alert('Invalid User'); document.location='inde.php' </script>";
  return;
}


      $partcode=$_REQUEST['partcode'];
      $partcode1=$_REQUEST['partcode1'];
      $partname=$_REQUEST['partname'];
      $type=$_REQUEST['type'];

 $dfrom=$_REQUEST['fromdt'];
 $dto=$_REQUEST['todt'];
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<body bgcolor="#0033FF">


<?php //include('includes/top.php'); ?>
<center>

<table>
<TR><TD COLSPAN=2 ALIGN=CENTER><font color='white'><b> <?print $brrow[2]; ?>  </b></font><BR>
<font color='white'  > <?print $brrow[braddr].",".$brrow[brcity].",<br><u> PH - ".$brrow[brphone]?></u>
TIN: <?print $brrow[tin]?>, CST: <?print $brrow[cstno]?>&nbsp;</font>
</td></tr>
</TABLE>

<?$date1 = date_create($dfrom);
 $date2 = date_create($dto);?>

<font  > <? print "<B>STOCK REPORT</B> <BR> From ".date_format($date1, 'd-m-Y')." To ".date_format($date2, 'd-m-Y');?></font>


<link rel="stylesheet" href="style.css" type="text/css" />
<link type="text/css" href="includes/style1.css" rel="stylesheet" />
<link type="text/css" href="css/form.css" rel="stylesheet" />

<style>
  //tr1{background-color:white}
  th{background-color:steelblue;color:white}
</style>
<center>

<?
 $dfrom=implode('-', array_reverse(explode('-', $dfrom)));
 $dto=implode('-', array_reverse(explode('-', $dto)));
if($type=='single')
{
$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,ref_no varchar(15),ref_date date)");
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' and entrydate<'$dfrom' and partcode = '$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' and invdate<'$dfrom' and item_code = '$partcode'") 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' and itemcode = '$partcode'") 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 invdate<'$dfrom' and item_code = '$partcode' and itemtyp='N'") 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 invdate<'$dfrom' and itemtyp='N' and item_code = '$partcode'") 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' and invdate<'$dfrom' and item_code = '$partcode'") 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 invdate<'$dfrom' and item_code = '$partcode' and itemtyp='N'") 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' and invdate<'$dfrom' and item_code = '$partcode'") 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' and dataexchdt<'$dfrom' and itemcode = '$partcode'") 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' and stockverifydt<'$dfrom' and itemcode = '$partcode'") or die('err12'.mysqli_error($db));
}
//****************************************************************************** in out*******
mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out,ref_no,ref_date) select partcode,partname,qty,outqty,refno,entrydate from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and partcode = '$partcode'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,purch_in,ref_no,ref_date) select item_code,item,qty,supinvno,invdate from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in,ref_no,ref_date) select item_code,item,rtnqty,invno,invdate from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode' and itemtyp='N'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,trans_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode' and itemtyp='N'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,trans_in,ref_no,ref_date) select item_code,item,qty,invno,invdate from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,sale_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode' and itemtyp='N'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,stkadj_out,ref_date) select itemcode,nor_qty,dataexchdt from qtytransf where brcode='$brcode' and dataexchdt>='$dfrom' and dataexchdt<='$dto' and itemcode = '$partcode'") 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,partname,stkadj_out,stkadj_in,ref_no,ref_date) select itemcode,itemname,stock_short,stock_excess,concat('V-',ref_no),stockverifydt from stockverification where brcode='$brcode' and stockverifydt>='$dfrom' and stockverifydt<='$dto' and itemcode = '$partcode'") or die('err12'.mysqli_error($db));
}
//**************************************
$re=mysqli_query($db,"select sum(op_inqty),sum(op_outqty) from $tbl");
$row=mysqli_fetch_array($re);
$op_stock = $row[0]-$row[1];

$re=mysqli_query($db,"select partcode,partname,op_inqty,op_outqty,purch_in,trans_in,salertn_in,stkadj_in,sale_out,trans_out,purchrtn_out,stkadj_out,ref_no,ref_date from $tbl where length(ref_date)>0 order by ref_date");

print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>";
print "<tr><td>Item : </td><td>$partcode</td><td>Item Name :</td><td>$partname</td></tr>";
print "<th>Date <th>Ref No.<th>Transaction Details<th>In Qty<th>Out Qty<th>Closing Bal<th>";

print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
$date1 = date_create($dfrom);
print "<td>".date_format($date1, 'd-m-Y')."</td><td></td><td>Opening Stock</td></td><td align=right>".number_format($op_stock,2,'.',',')."</td><td></td><td align=right>".number_format($op_stock,2,'.',',')."</td><td></td>";

$i=1;
$tprice=0;
$cl_stock = $op_stock;
while($row=mysqli_fetch_array($re))
{
  if(abs($row[4])>0)
  {
    $cl_stock = $cl_stock + $row[4];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Purchase</td></td><td align=right>".number_format($row[4],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td></td>";
  }
  if(abs($row[5])>0)
  {
    $cl_stock = $cl_stock + $row[5];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Transfer In</td></td><td align=right>".number_format($row[5],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td></td>";
  }
  if(abs($row[6])>0)
  {
    $cl_stock = $cl_stock + $row[6];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Sales Return</td></td><td align=right>".number_format($row[6],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td></td>";
  }
  if(abs($row[7])>0)
  {
    $cl_stock = $cl_stock + $row[7];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Stock Verification (Excess)</td></td><td align=right>".number_format($row[7],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td>Actual Verified</td>";
  }
  if(abs($row[8])>0)
  {
    $cl_stock = $cl_stock - $row[8];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Sales Invoice </td></td><td align=right></td><td align=right>".number_format($row[8],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td></td>";
  }
  if(abs($row[9])>0)
  {
    $cl_stock = $cl_stock - $row[9];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Transfer Out</td></td><td align=right></td><td align=right>".number_format($row[9],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td></td>";
  }

  if(abs($row[10])>0)
  {
    $cl_stock = $cl_stock - $row[10];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Purchase Return</td></td><td align=right></td><td align=right>".number_format($row[10],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td></td>";
  }
  if(abs($row[11])>0)
  {
    $cl_stock = $cl_stock - $row[11];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
     if(substr($row[12],0,2) == 'V-')
         print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Stock Verification (Short)</td><td align=right></td><td align=right>".number_format($row[11],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td>Actual Verified</td>";
     else
         print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Conversion to Damage Stock</td><td align=right></td><td align=right>".number_format($row[11],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td><td></td>";
  }
}
$re2=mysqli_query($db,"select lstprice from spareprmast where partcode='$partcode'");
  $row13=mysqli_fetch_array($re2);
  $tprice = $cl_stock*$row13[0];
  print "<tr ><td colspan=4 align=center >Total</td>";
  print "<td >Cl.Stock $cl_stock</td><td align=right>Rs.".round($tprice,2)."</td>";
  print "<td ></td></tr>";
//************************************

$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,ref_no varchar(15),ref_date date)");
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' and itemcode = '$partcode'") 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 invdate<'$dfrom' and item_code = '$partcode' and itemtyp='D'") 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 invdate<'$dfrom' and item_code = '$partcode' and itemtyp='D'") 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' and dataexchdt<'$dfrom' and itemcode = '$partcode'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,op_outqty) select item_code,item,qty from brtransferout where brcode='$brcode' and itemtyp='D' and invdate<'$dfrom' and item_code = '$partcode'") or die('err12'.mysqli_error($db));
//****************************************************************************** in out*******
mysqli_query($db,"insert into $tbl (partcode,stkadj_in,ref_date) select itemcode,dam_qty,dataexchdt from qtytransf where brcode='$brcode' and dataexchdt>='$dfrom' and dataexchdt<='$dto' and itemcode = '$partcode'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in,ref_no,ref_date) select item_code,item,rtnqty,invno,invdate from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode' and itemtyp='D'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,sale_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode' and itemtyp='D'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,trans_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from brtransferout where brcode='$brcode' and itemtyp='D' and invdate>='$dfrom' and invdate <='$dto' and item_code = '$partcode'") or die('err12'.mysqli_error($db));
//**************************************
$re=mysqli_query($db,"select sum(op_inqty),sum(op_outqty) from $tbl");
$row=mysqli_fetch_array($re);
$op_stock = $row[0]-$row[1];

$re=mysqli_query($db,"select partcode,partname,op_inqty,op_outqty,purch_in,trans_in,salertn_in,stkadj_in,sale_out,trans_out,purchrtn_out,stkadj_out,ref_no,ref_date from $tbl where length(ref_date)>0 or (op_inqty+op_outqty)>0 order by ref_date");
  if(mysqli_num_rows($re)<=0)
     exit;

print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='FF6666'>";
print "<tr><td colspan=5><b>Damage Stock Details</td></tr>";
print "<th>Date <th>Ref No.<th>Transaction Details<th>In Qty<th>Out Qty<th>Closing Bal";

print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
$date1 = date_create($dfrom);
print "<td>".date_format($date1, 'd-m-Y')."</td><td></td><td>Opening Stock</td></td><td align=right>".number_format($op_stock,2,'.',',')."</td><td></td><td align=right>".number_format($op_stock,2,'.',',')."</td>";

$i=1;
$tprice=0;
$cl_stock = $op_stock;
while($row=mysqli_fetch_array($re))
{
  if(abs($row[4])>0)
  {
    $cl_stock = $cl_stock + $row[4];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Purchase</td></td><td align=right>".number_format($row[4],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }
  if(abs($row[5])>0)
  {
    $cl_stock = $cl_stock + $row[5];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Transfer In</td></td><td align=right>".number_format($row[5],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }
  if(abs($row[6])>0)
  {
    $cl_stock = $cl_stock + $row[6];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Sales Return</td></td><td align=right>".number_format($row[6],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }
  if(abs($row[7])>0)
  {
    $cl_stock = $cl_stock + $row[7];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Conversion to Damage Stock</td></td><td align=right>".number_format($row[7],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }
  if(abs($row[8])>0)
  {
    $cl_stock = $cl_stock - $row[8];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Sales Invoice </td></td><td align=right></td><td align=right>".number_format($row[8],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }
  if(abs($row[9])>0)
  {
    $cl_stock = $cl_stock - $row[9];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Transfer Out</td></td><td align=right></td><td align=right>".number_format($row[9],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }

  if(abs($row[10])>0)
  {
    $cl_stock = $cl_stock - $row[10];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Purchase Return</td></td><td align=right></td><td align=right>".number_format($row[10],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }
  if(abs($row[11])>0)
  {
    $cl_stock = $cl_stock - $row[11];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
         print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td>Item Conversion to Damage Stock</td><td align=right></td><td align=right>".number_format($row[11],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
  }
}
$re2=mysqli_query($db,"select lstprice from spareprmast where partcode='$partcode'");
  $row13=mysqli_fetch_array($re2);
  $tprice = $cl_stock*$row13[0];
  print "<tr ><td colspan=4 align=center >Total</td>";
  print "<td >Cl.Stock $cl_stock</td><td align=right>Rs.".round($tprice,2)."</td>";
  print "<td ></td></tr>";





//************************************
?>
</table>
<?
}
else
{
  $itemcode=$_REQUEST['itemcode'];
$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,ref_no varchar(15),ref_date date,index (partcode))");
mysqli_query($db,"delete from $tbl");
$sql=mysqli_query($db,"select partcode from partmaster where partcode>='$itemcode' and partcode<='$partcode1' and gname='$type' ");
//while($row=mysqli_fetch_array($sql))
//  {
mysqli_query($db,"insert into $tbl (partcode,partname,op_inqty,op_outqty) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' and entrydate<'$dfrom' and gname='$type' and partcode>='$itemcode' and partcode<='$partcode1' 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' and invdate<'$dfrom' and gname='$type' and item_code>='$itemcode' and item_code<='$partcode1' 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' and item_grp='$type' and itemcode>='$itemcode' and itemcode<='$partcode1' 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 invdate<'$dfrom' and itemtyp='N' and gname='$type' and item_code>='$itemcode' and item_code<='$partcode1' 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 invdate<'$dfrom' and gname='$type' and item_code>='$itemcode' and item_code<='$partcode1'  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' and invdate<'$dfrom' and gname='$type' and item_code>='$itemcode' and item_code<='$partcode1' 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 invdate<'$dfrom' and itemtyp='N' and gname='$type' and item_code>='$itemcode' and item_code<='$partcode1' 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' and invdate<'$dfrom' and gname='$type' and item_code>='$itemcode' and item_code<='$partcode1'  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' and dataexchdt<'$dfrom' 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' and stockverifydt<'$dfrom' and itemcode >= '$itemcode' and itemcode <= '$partcode1'") or die('err12'.mysqli_error($db));
}
//****************************************************************************** in out*******
mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out,ref_no,ref_date) select partcode,partname,qty,outqty,refno,entrydate from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and partcode>='$itemcode' and partcode<='$partcode1' and  gname='$type'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,purch_in,ref_no,ref_date) select item_code,item,qty,supinvno,invdate from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code>='$itemcode' and item_code<='$partcode1'  and gname='$type'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in,ref_no,ref_date) select item_code,item,rtnqty,invno,invdate from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname='$type' and item_code>='$itemcode' and item_code<='$partcode1'  and itemtyp='N'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,trans_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code>='$itemcode' and item_code<='$partcode1' and gname='$type'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,trans_in,ref_no,ref_date) select item_code,item,qty,invno,invdate from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code>='$itemcode' and item_code<='$partcode1' and gname='$type'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,sale_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code>='$itemcode' and item_code<='$partcode1' and gname='$type' and itemtyp='N'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out,ref_no,ref_date) select item_code,item,qty,invno,invdate from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and item_code>='$itemcode' and item_code<='$partcode1' and gname='$type'") or die('err12'.mysqli_error($db));
mysqli_query($db,"insert into $tbl (partcode,stkadj_out,ref_date) select itemcode,nor_qty,dataexchdt from qtytransf where brcode='$brcode' and dataexchdt>='$dfrom' and dataexchdt<='$dto' and itemcode>='$itemcode' and itemcode<='$partcode1' ") 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,partname,stkadj_out,stkadj_in,ref_no,ref_date) select itemcode,itemname,stock_short,stock_excess,concat('V-',ref_no),stockverifydt from stockverification where brcode='$brcode' and stockverifydt>='$dfrom' and stockverifydt<='$dto' and itemcode >= '$itemcode' and itemcode <= '$partcode1'") or die('err12'.mysqli_error($db));
}
//**************************************
//  }

$sql=mysqli_query($db,"select distinct(partcode) from $tbl where partcode > ''");
//$var="select distinct(partcode) from $tbl";
//echo $var;


print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>";
print "<tr><th>Date<th>Ref No.<th>Item Code: <th>Item Name:<th>Transaction Details<th>In Qty<th>Out Qty<th>Closing Bal</tr>";

$tprice=0;
$i=0;



while($row9=mysqli_fetch_array($sql))
{
  $re1=mysqli_query($db,"select sum(op_inqty),sum(op_outqty),max(partname) from $tbl where partcode='$row9[0]'");
  $row12=mysqli_fetch_array($re1);
  $op_stock = $row12[0]-$row12[1];

  $re=mysqli_query($db,"select partcode,partname,op_inqty,op_outqty,purch_in,trans_in,salertn_in,stkadj_in,sale_out,trans_out,purchrtn_out,stkadj_out,ref_no,ref_date from $tbl where length(ref_date)>0 and partcode='$row9[0]' order by ref_date");
  $var="select partcode,partname,op_inqty,op_outqty,purch_in,trans_in,salertn_in,stkadj_in,sale_out,trans_out,purchrtn_out,stkadj_out,ref_no,ref_date from $tbl where length(ref_date)>0 and partcode='$row9[0]' order by ref_date";
  $cl_stock = $op_stock;
  $date1 = date_create($dfrom);
  print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
  print "<tr><td>".date_format($date1, 'd-m-Y')."</td><td></td><td>Item Code: ".$row9[0]."</td><td>Item Name: ".$row12[2]."</td><td>Opening Stock</td>
    <td align=right>".number_format($op_stock,2,'.',',')."</td><td></td><td align=right>".number_format($op_stock,2,'.',',')."</td></tr>";
  while($row=mysqli_fetch_array($re))
  {




    if(abs($row[4])>0)
    {
      $cl_stock = $cl_stock + $row[4];
      $date1 = date_create($row[13]);
       print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
      print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Item Purchase</td></td><td align=right>".number_format($row[4],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }
    if(abs($row[5])>0)
    {
      $cl_stock = $cl_stock + $row[5];
      $date1 = date_create($row[13]);
       print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
      print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Item Transfer In</td></td><td align=right>".number_format($row[5],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }
    if(abs($row[6])>0)
    {
      $cl_stock = $cl_stock + $row[6];
      $date1 = date_create($row[13]);
       print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
      print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Sales Return</td></td><td align=right>".number_format($row[6],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }
    if(abs($row[7])>0)
    {
    $cl_stock = $cl_stock + $row[7];
    $date1 = date_create($row[13]);
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
    print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Stock Verification (Excess)</td></td><td align=right>".number_format($row[7],2,'.',',')."</td><td></td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }
    if(abs($row[8])>0)
    {
      $cl_stock = $cl_stock - $row[8];
      $date1 = date_create($row[13]);
       print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
      print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Sales Invoice </td></td><td align=right></td><td align=right>".number_format($row[8],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }
    if(abs($row[9])>0)
    {
      $cl_stock = $cl_stock - $row[9];
      $date1 = date_create($row[13]);
       print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
      print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Item Transfer Out</td></td><td align=right></td><td align=right>".number_format($row[9],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }

    if(abs($row[10])>0)
    {
      $cl_stock = $cl_stock - $row[10];
      $date1 = date_create($row[13]);
       print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
      print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Purchase Return</td></td><td align=right></td><td align=right>".number_format($row[10],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }
    if(abs($row[11])>0)
    {
      $cl_stock = $cl_stock - $row[11];
      $date1 = date_create($row[13]);
       print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'>";
      if(substr($row[12],0,2) == 'V-')
          print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Stock Verification (Short)</td><td align=right></td><td align=right>".number_format($row[11],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
      else
          print "<td>".date_format($date1, 'd-m-Y')."</td><td>$row[12]</td><td></td><td></td><td>Stock Adjustment Out</td><td align=right></td><td align=right>".number_format($row[11],2,'.',',')."</td><td align=right>".number_format($cl_stock,2,'.',',')."</td>";
    }

  }

  $re2=mysqli_query($db,"select lstprice from spareprmast where partcode='$row9[0]'");
  $row13=mysqli_fetch_array($re2);
  $tprice = $cl_stock*$row13[0];
  print "<tr  style='border:1pt solid brown;'><td colspan=4 align=center >Total</td><td></td><td></td>";
  print "<td >Cl.Stock $cl_stock</td><td align=right>Rs.".round($tprice,2)."</td>";
  print "</tr>";
}

















}
?>
</table>