<?
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;
}
include_once('testtot.php');
      $fromdt=$_REQUEST['fromdt'];
      $todt=$_REQUEST['todt'];
      $type=$_REQUEST['type'];
      $it_type=$_REQUEST['it_type'];
$dfrom=implode('-', array_reverse(explode('-', $fromdt)));
$dto = implode('-', array_reverse(explode('-', $todt)));;
 //echo $dfrom,$dto,$type;
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
?>
<script type="text/javascript" src="number_to_word.js"></script>
<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>
<font   > <? print "<B>STOCK REPORT </B> <BR> From ".$fromdt." To ".$todt;?></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>
<style>
#txt{
font-family: Arial, Helvetica;
background-color: #F2F2F2;
}
</style>
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.min.css" />
<script type="text/javascript" src="jsDatePick.min.1.3.js"></script>
<script type="text/javascript">
window.onload = function(){
new JsDatePick({
  useMode:2,
  target:"inputField",
  dateFormat:"%Y-%m-%d"
});
new JsDatePick({
  useMode:2,
  target:"inputField1",
  dateFormat:"%Y-%m-%d"
});

};
</script>

<SCRIPT LANGUAGE="JavaScript" SRC="util/PopupWindow.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="util/AnchorPosition.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="util/CalendarPopup.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="util/date.js"></SCRIPT>
<script>

var cal1 = new CalendarPopup();
cal1.showYearNavigation();

var cal2 = new CalendarPopup();
cal2.showYearNavigation();

</script>
<style>
th{
    // background-color: grey;
    font-weight: bold;
    color: white;
    border:1px solid #D7D7D1;
    border-collapse:collapse;
background: rgb(181,189,200); /* Old browsers */
background: -moz-linear-gradient(top, rgba(181,189,200,1) 0%, rgba(130,140,149,1) 36%, rgba(40,52,59,1) 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,rgba(181,189,200,1)), color-stop(36%,rgba(130,140,149,1)), color-stop(100%,rgba(40,52,59,1))); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* IE10+ */
background: linear-gradient(to bottom, rgba(181,189,200,1) 0%,rgba(130,140,149,1) 36%,rgba(40,52,59,1) 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#b5bdc8', endColorstr='#28343b',GradientType=0 ); /* IE6-9 */
}
.ro_settled td
{
background: #6bba70; /* Old browsers */
background: -moz-linear-gradient(top, #6bba70 0%, #6bba70 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#6bba70), color-stop(100%,#6bba70)); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* IE10+ */
background: linear-gradient(to bottom, #6bba70 0%,#6bba70 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#6bba70', endColorstr='#6bba70',GradientType=0 ); /* IE6-9 */
color:white;
}
.ro_settl td
{
background: #6bba70; /* Old browsers */
background: -moz-linear-gradient(top, #6bba70 0%, #6bba70 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,#6bba70), color-stop(100%,#6bba70)); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, #6bba70 0%,#6bba70 100%); /* IE10+ */
background: linear-gradient(to bottom, #6bba70 0%,#6bba70 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#6bba70', endColorstr='#6bba70',GradientType=0 ); /* IE6-9 */
color:red;
}
</style>
<?
$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");
if($it_type=='Damaged')
{
    if($type=='All')
    {
        mysqli_query($db,"insert into $tbl (partcode,op_inqty) select itemcode,sum(dam_qty) from qtytransf where brcode='$brcode' and dataexchdt<'$dfrom' 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 prod_invoice where brcode='$brcode' and invdate<'$dfrom' and itemtyp='D' 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_dam) from sparestock 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 invdate<'$dfrom' and itemtyp = 'D' group by item_code") or die('err12'.mysqli_error($db));
        //****************************************************************************** in out*******
        mysqli_query($db,"insert into $tbl (partcode,trans_in) select itemcode,sum(dam_qty) from qtytransf where brcode='$brcode' and dataexchdt>='$dfrom' and dataexchdt<='$dto' group by itemcode") or die('err12'.mysqli_error($db));
        mysqli_query($db,"insert into $tbl (partcode,partname,sale_out) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto' and itemtyp='D' group by item_code") or die('err12'.mysqli_error($db));
        mysqli_query($db,"insert into $tbl (partcode,partname,trans_out) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto' 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");

        print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>";
        print "<tr><td>Item Group : </td><td>$type <b> (DAMAGED ITEMS)</td></tr>";
        print "<th>No.<th>Item Code <th>Item Name<th>OP Stock<th>Trans In<th>Sales Rtn<th>Sales<th>Trans Out<th>";
        if(($row_tot[0]>0) || ($row_tot[1]>0))
            print "Excess<th>Short<th>";
        print "Closing<th>Rate <th>Amount";
        $i=1;
        $tprice=0;
        while($row=mysqli_fetch_array($re))
        {
               $op_qty = $row[2]-$row[3];
               if(abs($op_qty)>0 || abs($row[4])>0 ||abs($row[5])>0 ||abs($row[6])>0 ||abs($row[7])>0 ||abs($row[8])>0 ||abs($row[9])>0 ||abs($row[10])>0 ||abs($row[11])>0)
               {
                  print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'><td>".$i++."</td>";
                  print "<td>$row[0]</td><td>$row[1]</td><td align=right>$op_qty</td>";
                  print "<td align=right >$row[5]</td>";
                  print "<td align=right >$row[6]</td>";
                  print "<td align=right >$row[8]</td>";
                  print "<td align=right >$row[9]</td>";
                  $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
                  if(($row_tot[0]>0) || ($row_tot[1]>0))
                  {
                       print "<td align=right >$row[7]</td><td align=right >$row[11]</td>";
                  }
                  print "<td align=right >".number_format($close,2,".",",")."</td>";
                  $prres=mysqli_query($db,"select ndpcost from spareprmast where partcode='$row[0]' order by effectfrom desc");
                  $prrow=mysqli_fetch_row($prres);
                  $pr=($prrow[0]*($close));
                  $tprice+=$pr;
                  print "<td align=right>".$prrow[0]."</td><td align=right>".number_format($pr,2)."</td>";
               }
        }
        print "<tr ><td colspan=10 align=center bgcolor='silver'>Total</td>";
        print "<td  bgcolor='silver' align=right>".number_format($tprice,2)."</td>";
        print "</tr>";
    }
    else
    {
        mysqli_query($db,"insert into $tbl (partcode,op_inqty) select itemcode,sum(dam_qty) from qtytransf,partmaster where qtytransf.brcode='$brcode' and qtytransf.dataexchdt<'$dfrom' and partmaster.partcode= qtytransf.itemcode and partmaster.gname = '$type' 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 prod_invoice where brcode='$brcode' and invdate<'$dfrom' and itemtyp='D' and gname = '$type'  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_dam) from sparestock where brcode='$brcode' and item_grp = '$type'  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 invdate<'$dfrom' and itemtyp = 'D' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
        //****************************************************************************** in out*******
        mysqli_query($db,"insert into $tbl (partcode,trans_in) select itemcode,sum(dam_qty) from qtytransf,partmaster where qtytransf.brcode='$brcode' and qtytransf.dataexchdt<='$dto' and qtytransf.dataexchdt>='$dfrom' and partmaster.partcode= qtytransf.itemcode and partmaster.gname = '$type' group by itemcode") or die('err12'.mysqli_error($db));
        mysqli_query($db,"insert into $tbl (partcode,partname,sale_out) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto' and itemtyp='D' and gname = '$type'  group by item_code") or die('err12'.mysqli_error($db));
        mysqli_query($db,"insert into $tbl (partcode,partname,trans_out) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate<='$dto' and itemtyp = 'D' and gname = '$type' 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");
        print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>";
        print "<tr><td>Item Group : </td><td>$type <b> (DAMAGED ITEMS)</td></tr>";
        print "<th>No.<th>Item Code <th>Item Name<th>OP Stock<th>Trans In<th>Sales Rtn<th>Sales<th>Trans Out<th>";
        if(($row_tot[0]>0) || ($row_tot[1]>0))
            print "Excess<th>Short<th>";
        print "Closing<th>Rate <th>Amount";
        $i=1;
        $tprice=0;
        while($row=mysqli_fetch_array($re))
        {
               $op_qty = $row[2]-$row[3];
               if(abs($op_qty)>0 || abs($row[4])>0 ||abs($row[5])>0 ||abs($row[6])>0 ||abs($row[7])>0 ||abs($row[8])>0 ||abs($row[9])>0 ||abs($row[10])>0 ||abs($row[11])>0)
               {
                  print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'><td>".$i++."</td>";
                  print "<td>$row[0]</td><td>$row[1]</td><td align=right>$op_qty</td>";
                  print "<td align=right >$row[5]</td>";
                  print "<td align=right >$row[6]</td>";
                  print "<td align=right >$row[8]</td>";
                  print "<td align=right >$row[9]</td>";
                  $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
                  if(($row_tot[0]>0) || ($row_tot[1]>0))
                  {
                       print "<td align=right >$row[7]</td><td align=right >$row[11]</td>";
                  }
                  print "<td align=right >".number_format($close,2,".",",")."</td>";
                  $prres=mysqli_query($db,"select ndpcost from spareprmast where partcode='$row[0]' order by effectfrom desc");
                  $prrow=mysqli_fetch_row($prres);
                  $pr=($prrow[0]*($close));
                  $tprice+=$pr;
                  print "<td align=right>".$prrow[0]."</td><td align=right>".number_format($pr,2)."</td>";
               }
        }
        print "<tr ><td colspan=10 align=center bgcolor='silver'>Total</td>";
        print "<td  bgcolor='silver' align=right>".number_format($tprice,2)."</td>";
        print "</tr>";
    }
    return;
}
else if($it_type=='Normal')
{
     if($type=='All')
     {
         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' 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' 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 invdate<'$dfrom' 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 invdate<'$dfrom' 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' and invdate<'$dfrom'   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' 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'  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' group by itemcode") or die('err12'.mysqli_error($db));
             }
             //****************************************************************************** in out*******
         mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' group by partcode") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,purch_in) select item_code,item,sum(qty) from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in) select item_code,item,sum(rtnqty) from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and itemtyp='N' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_out) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and itemtyp='N' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_in) select item_code,item,sum(qty) from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,sale_out) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and itemtyp='N'  group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out) select item_code,item,sum(qty) from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,trans_out) select itemcode,sum(nor_qty) from qtytransf where brcode='$brcode' and dataexchdt>='$dfrom' and dataexchdt<='$dto' 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,partname,stkadj_out,stkadj_in) select itemcode,itemname,stock_short,stock_excess from stockverification where brcode='$brcode' and stockverifydt>='$dfrom' and stockverifydt>='$dfrom' and stockverifydt<='$dto' group by itemcode") or die('err12'.mysqli_error($db));
         }
     }
     else
     {
         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' 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'  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' 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 gname = '$type' 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 invdate<'$dfrom' and gname = '$type' 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' and invdate<'$dfrom' and gname = '$type'  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 gname = '$type' 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' and invdate<'$dfrom' and gname = '$type'  group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,op_outqty) select a.itemcode,sum(a.nor_qty) from qtytransf a,partmaster b where a.brcode='$brcode' and a.itemcode=b.partcode and a.dataexchdt<'$dfrom' and b.gname='$type' 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 gname = '$type' group by itemcode") or die('err12'.mysqli_error($db));
         }
         //****************************************************************************** in out*******
         mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and gname = '$type'  group by partcode") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,purch_in) select item_code,item,sum(qty) from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in) select item_code,item,sum(rtnqty) from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' and itemtyp='N' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_out) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' and itemtyp='N' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_in) select item_code,item,sum(qty) from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,sale_out) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' and itemtyp='N' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out) select item_code,item,sum(qty) from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,trans_out) select a.itemcode,sum(a.nor_qty) from qtytransf a,partmaster b where a.brcode='$brcode' and a.itemcode=b.partcode and a.dataexchdt <='$dto' and a.dataexchdt >='$dfrom' and b.gname='$type' 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,partname,stkadj_out,stkadj_in) select itemcode,itemname,stock_short,stock_excess from stockverification where brcode='$brcode' and stockverifydt>='$dfrom' and stockverifydt>='$dfrom' and stockverifydt<='$dto' and gname = '$type' group by itemcode") or die('err12'.mysqli_error($db));
         }
     }
}
else
{
     if($type=='All')
     {
         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' 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' 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 invdate<'$dfrom'  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' 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'   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'  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'  group by item_code") 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' group by itemcode") or die('err12'.mysqli_error($db));
             }
             //****************************************************************************** in out*******
         mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' group by partcode") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,purch_in) select item_code,item,sum(qty) from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in) select item_code,item,sum(rtnqty) from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_out) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_in) select item_code,item,sum(qty) from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto'  group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,sale_out) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and itemtyp='N'   group by item_code") 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) select itemcode,itemname,stock_short,stock_excess from stockverification where brcode='$brcode' and stockverifydt>='$dfrom' and stockverifydt>='$dfrom' and stockverifydt<='$dto' group by itemcode") or die('err12'.mysqli_error($db));
         }
     }
     else
     {
         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' 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'  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' 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 gname = '$type' 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'  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'  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 gname = '$type' 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'  group by item_code") 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 gname = '$type' group by itemcode") or die('err12'.mysqli_error($db));
         }
         //****************************************************************************** in out*******
         mysqli_query($db,"insert into $tbl (partcode,partname,stkadj_in,stkadj_out) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' and entrydate>='$dfrom' and entrydate <='$dto' and gname = '$type'  group by partcode") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,purch_in) select item_code,item,sum(qty) from itempurchase where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,salertn_in) select item_code,item,sum(rtnqty) from salesretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_out) select item_code,item,sum(qty) from brtransferout where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,trans_in) select item_code,item,sum(qty) from brtransferin where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,sale_out) select item_code,item,sum(qty) from prod_invoice where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") or die('err12'.mysqli_error($db));
         mysqli_query($db,"insert into $tbl (partcode,partname,purchrtn_out) select item_code,item,sum(qty) from purchretn where brcode='$brcode' and invdate>='$dfrom' and invdate <='$dto' and gname = '$type' group by item_code") 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) select itemcode,itemname,stock_short,stock_excess from stockverification where brcode='$brcode' and stockverifydt>='$dfrom' and stockverifydt>='$dfrom' and stockverifydt<='$dto' and gname = '$type' 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");
$re_tot=mysqli_query($db,"select sum(stkadj_in),sum(stkadj_out) from $tbl");
$row_tot=mysqli_fetch_array($re_tot);
print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue'>";
print "<tr><td>Item Group : </td><td>$type</td></tr>";
print "<th>No.<th>Item Code <th>Item Name<th>OP Stock<th>Trans In<th>Sales Rtn<th>Sales<th>Trans Out<th>";
if(($row_tot[0]>0) || ($row_tot[1]>0))
    print "Excess<th>Short<th>";
print "Closing<th>Rate <th>Amount";
$i=1;
$tprice=0;
while($row=mysqli_fetch_array($re))
{
  $op_qty = $row[2]-$row[3];
  if(abs($op_qty)>0 || abs($row[4])>0 ||abs($row[5])>0 ||abs($row[6])>0 ||abs($row[7])>0 ||abs($row[8])>0 ||abs($row[9])>0 ||abs($row[10])>0 ||abs($row[11])>0)
  {
     print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white'><td>".$i++."</td>";
     print "<td>$row[0]</td><td>$row[1]</td><td align=right>$op_qty</td>";
    print "<td align=right >$row[5]</td>";
    print "<td align=right >$row[6]</td>";
    print "<td align=right >$row[8]</td>";
    print "<td align=right >$row[9]</td>";
    $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
    if(($row_tot[0]>0) || ($row_tot[1]>0))
    {
        print "<td align=right >$row[7]</td><td align=right >$row[11]</td>";
    }
    print "<td align=right >".number_format($close,2,".",",")."</td>";
    $prres=mysqli_query($db,"select ndpcost from spareprmast where partcode='$row[0]' order by effectfrom desc");
    $prrow=mysqli_fetch_row($prres);
    $pr=($prrow[0]*($close));
    $tprice+=$pr;
    print "<td align=right>".$prrow[0]."</td><td align=right>".number_format($pr,2)."</td>";
    //$locres=mysqli_query($db,"select location from sparestock where itemcode='$row[partcode]' and brcode='$brcode'");
    //$locrow=mysqli_fetch_row($locres);
    //print "<td>$locrow[0]</td></tr>";
  }
}
print "<tr ><td colspan=10 align=center bgcolor='silver'>Total</td>";
print "<td  bgcolor='silver' align=right>".number_format($tprice,2)."</td>";
print "</tr>";
?>
</table>