<?
session_start();
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['dfrom'];


$type=$_REQUEST['gname'];

$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$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' 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' 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' 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' 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' 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' 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' 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' 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;
$filename ="stkrpt_con.xls";



$re=mysqli_query($db,"select gname,ledqty*sellprice,curstk*sellprice,stock_short*sellprice,stock_excess*sellprice from stockverification where stockverifydt='$verdt' and brcode='$brcode' and gname not like '%VIP%' order by gname,itemcode ");

$contents="       \t STOCK VERIFICATION STATEMENT AS ON    \t           \t Item Group : \t\t\t \n";
$contents="       \t CONSOLIDATION    \t           \t Item Group : \t\t\t\n";
$contents.="Item \t Page No. \t Stock as per Ledger \t Stock actually verified \t Excess \t Short \t Remarks \n";
$i=1;
$tot_led = 0;
$tot_act = 0;
$tot_exc = 0;
$tot_sho = 0;
$mgroup_tot_1 = 0;
$mgroup_tot_2 = 0;
$mgroup_tot_3 = 0;
$mgroup_tot_4 = 0;
$mgr_tot_1 = 0;
$mgr_tot_2 = 0;
$mgr_tot_3 = 0;
$mgr_tot_4 = 0;
$page_tot_1 =0;
$page_tot_2 =0;
$page_tot_3 =0;
$page_tot_4 =0;
$mgname = "";
$mpageno = 1;
$mline = 1;
while($row=mysqli_fetch_array($re))
{
       if ($mline > 25 || ($mgname != $row[0] && $mgname >""))
       {
    
            print "<td >$mgname</td><td>$mpageno</td><td align=right>".number_format($page_tot_1,2)."</td><td align=right >".number_format($page_tot_2,2)."</td><td align=right>".number_format($page_tot_3,2)."</td><td align='right'>".number_format($page_tot_4,2)."</td><td></td></tr>";
            $mgroup_tot_1 += $page_tot_1;
            $mgroup_tot_2 += $page_tot_2;
            $mgroup_tot_3 += $page_tot_3;
            $mgroup_tot_4 += $page_tot_4;

            $mline = 1;
            $mpageno++;
            $page_tot_1 =0;
            $page_tot_2 =0;
            $page_tot_3 =0;
            $page_tot_4 =0;
            if ($mgname != $row[0])
            {
                $contents="  $mgname TOTAL \t ".number_format($mgroup_tot_1,2)."  \t     ".number_format($mgroup_tot_2,2)."      \t ".number_format($mgroup_tot_3,2)." \t ".number_format($mgroup_tot_4,2)."\t  \t \n";

                
                 $mgr_tot_1 += $mgroup_tot_2;
                 $mgr_tot_2 += $mgroup_tot_2;
                 $mgr_tot_3 += $mgroup_tot_3;
                 $mgr_tot_4 += $mgroup_tot_4;
                 $mgroup_tot_1 = 0;
                 $mgroup_tot_2 = 0;
                 $mgroup_tot_3 = 0;
                 $mgroup_tot_4 = 0;
                 $mpageno = 1;
            }
       }
       $mgname = $row[0];
       $page_tot_1 += $row[1];
       $page_tot_2 += $row[2];
       $page_tot_3 += $row[4];
       $page_tot_4 += $row[3];
       $mline++;
}
$mgroup_tot_1 += $page_tot_1;
$mgroup_tot_2 += $page_tot_2;
$mgroup_tot_3 += $page_tot_3;
$mgroup_tot_4 += $page_tot_4;
$mgr_tot_1 += $mgroup_tot_2;
$mgr_tot_2 += $mgroup_tot_2;
$mgr_tot_3 += $mgroup_tot_3;
$mgr_tot_4 += $mgroup_tot_4;

$contents="  $mgname \t ".$mpageno."   \t     ".number_format($page_tot_1,2)."      \t ".number_format($page_tot_2,2)." \t ".number_format($page_tot_3,2)."\t ".number_format($page_tot_4,2)." \t \n";

$contents="  $mgname TOTAL \t ".number_format($mgroup_tot_1,2)."   \t     ".number_format($mgroup_tot_2,2)."      \t ".number_format($mgroup_tot_3,2)." \t ".number_format($mgroup_tot_4,2)."\t\t \n";

$contents="  GRAND TOTAL \t ".number_format($mgr_tot_1,2)."   \t     ".number_format($mgr_tot_2,2)."      \t ".number_format($mgr_tot_3,2)." \t ".number_format($mgr_tot_4,2)."\t\t \n";
$contents="       \t Stock Verification Officer    \t           \t Manager in charge \t\t\t \n";
$contents="       \t Dist Khadi and Village Industries    \t           \t $brrow[1] \t\t\t \n";



$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];



 
            $contents.=$i++."\t";
            $contents.=$row[1]."\t";
            $contents.=$row[2]."\t";
            $contents.=$row[3]."\t";
            $contents.=$row[4]."\t";
            $contents.=number_format($ledgvl,2)."\t";
            $contents.=$row[5]."\t";
            $contents.=number_format($actualvl,2)."\t";
            $contents.=$row[10]."\t";
            $contents.=$excsvl."\t";
            $contents.=$row[9]."\t";
            $contents.=$shrtvl."\n";

    
  
}
$contents.="\n";
$contents.="Grand Total\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";

$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.="\t";
$contents.=number_format($tprice,2)."\t";
$contents = strip_tags($contents);
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
print $contents;
?>