<?
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');
$verdt=$_REQUEST['verdt'];
      $y1=$_REQUEST['y1'];
      $m1=$_REQUEST['m1'];
      $d1=$_REQUEST['d1'];
      $y2=$_REQUEST['y2'];
      $m2=$_REQUEST['m2'];
      $d2=$_REQUEST['d2'];
      $type=$_REQUEST['type'];

 //$dfrom=$y1.'-'.$m1.'-'.$d1;
 //$dto=$y2.'-'.$m2.'-'.$d2;
$dto1=implode("-",array_reverse(explode("-",$verdt)));
$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>   </b></font><BR>
<font color='white'  > <br><u> </u>
</font>
</td></tr>
</TABLE>
<font> <? print "<B>STOCK VERIFICATION STATEMENT AS ON ".$dto1."</B>";?></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 type="text/css">
tr.top td { border-top: thin solid black; }
tr.bottom td { border-bottom: thin solid black; }
tr.row td:first-child { border-left: thin solid black; }
tr.row td:last-child { border-right: thin solid black; }
</style>
<center>

<?
$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) 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,stkadj_in,stkadj_out) select partcode,partname,sum(qty),sum(outqty) from adjstock where brcode='$brcode' 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 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 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 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 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 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 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");
$i=1;
$tprice=0;
$mctr=1;
$mctr_1=1;
$pageno = 1;
$mflg = 1;
while($row=mysqli_fetch_array($re))
{
       $mpartcode = $row[0];
       $mpartname = $row[1];
       $re12=mysqli_query($db,"select * from stockverification where stockverifydt='$verdt' and brcode='$brcode' and itemcode = '$mpartcode'");
       if(mysqli_num_rows($re12) <= 0)
       {
            if($mflg==1)
            {
                print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelgreen' style='border-collapse: collapse;'>";
                print "<tr><td colspan=4>Stock Verification Entry not updated for the following items</td></tr>";
                print "<tr><td>SL No</td><td>Sl in Page</td><td>Item Code</td><td>Item Name</td><td>Page No.</td></tr>";
                $mflg = 0;
            }
            print "<tr><td>".$mctr."</td><td>".$mctr_1."</td><td>".$mpartcode."</td><td>".$mpartname."</td><td>".$pageno."</td></tr>";
       }
       $mctr++;
       $mctr_1++;
       if($mctr_1 > 25)
       {
           $mctr_1 = 1;
           $pageno++;
       }

}

if($type=='All')
    {
        $re=mysqli_query($db,"select * from stockverification where stockverifydt='$verdt' and brcode='$brcode' group by itemcode ");
    }
else
{
    $re=mysqli_query($db,"select * from stockverification where stockverifydt='$verdt' and gname='$type' and brcode='$brcode' group by itemcode ");
}
$sql="select * from stockverification where stockverifydt='$verdt' and gname='$type' and brcode='$brcode' group by itemcode";
//**************************************
//$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),sum(sto_verify) from $tbl group by partcode");


print "<table border=0 cellspacing=1 cellpadding=3 bgcolor='lightsteelblue' style='border-collapse: collapse;'>";
print "<tr style='border:1px solid black;'><td>Item Group : </td><td>$type</td><td colspan='9'></td></tr>";
print "<tr><td>No.</td><td>Stock No. </td><td>Item Name</td><td>Rate</td><td colspan='2'>Stock as per Ledger</td><td colspan='2'>Stock actually verified</td><td colspan='2' align='center'>Excess</td><td colspan='2' align='center'>Short</td><td>Remarks</td></TR>";
print "<TR><td></td><td></td><td></td><td></td><td align='right'>Qty</td><td  align='right'>Value</td><td align='right'>Qty</td><td  align='right'>Value</td><td align='right'>Qty</td><td  align='right'>Value</td><td align='right'>Qty</td><td  align='right'>Value</td></tr>";
$i=1;
$tprice=0;
while($row=mysqli_fetch_array($re))
{
       $op_qty = $row[2]-$row[3];
       $ledgvl=$row[3]*$row[4];
       $actualvl=$row[3]*$row[5];
       $excsvl=$row[3]*$row[10];
       $shrtvl=$row[3]*$row[9];
       if(($row[4]==0)&&($row[5]==0))
       {    $a=1;
       }
       else
       {
          print "\n<tr bgcolor=white onMouseOver=bgColor='silver'; onMouseOut=bgColor='white' style='border:1px solid black;'><td>".$i++."</td>";
          print "<td >$row[1]</td><td>$row[2]</td>";
          print "<td align=right>".$row[3]."</td><td align=right >".$row[4]."</td><td align=right>".number_format($ledgvl,2)."</td><td>".$row[5]."</td><td>".number_format($actualvl)."</td><td align='right'>".$row[10]."</td><td align='right'>".$excsvl."</td><td align='right'>".$row[9]."</td><td align='right'> ".$shrtvl."</td><td>$row[13]</td></tr>";
       }
}
?>
</table>