<?
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');
 $dfrom=$_REQUEST['dfrom'];
 $dto=$_REQUEST['dto'];
 $type=$_REQUEST['gname'];
 $brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
 $brrow=mysqli_fetch_array($brres);
$brnchnm= $brrow[1];
  $file =  'stock_ver.prn';
  $handle = fopen($file, 'w');
  $dss=chr(27).chr(71).chr(N);
  $dse=chr(27).chr(72);
  $initialized = chr(27).chr(64);
  $load = chr(27).chr(25).chr(66);
  $bold = chr(27).chr(14);
  $unbold = chr(20);
  $pagelenth=chr(27).chr(67).chr(36);
  $Data = $initialized;
  $Data = "\n\n\n";
  $Data .= "                  KERALA KHADI & VILLAGE INDUSTRIES BOARD\n";
  $Data .= "                     CENTER NAME : ".$brnchnm." \n";
  $Data .= "              Stock Verification Report as on ".$dto."\n\n";
$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($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));
//****************************************************************************** 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'  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));
}
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));
//****************************************************************************** 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));
}
//**************************************
$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");


$Data .= "Item Group :".$type."\n\n";
$Data .= chr(15)."\n\n";
$Data.= "|-----|--------------|--------------------|---------|-------------------|-------------------|---------------|---------------|---------|\n";
$Data.= "|     |              |                    |         |STOCK AS PER LEDGER|STOCK  ACT VERIFIED|     EXCESS    |      SHORT    | REMARKS |\n";
$Data.= "| No. |  Item Code:  |      Item Name     |   Rate  |   QTY  |   VALUE  |   QTY  |   VALUE  |  QTY |  VALUE |  QTY |  VALUE |         |\n";
$Data.= "|-----|--------------|--------------------|---------|--------|----------|--------|----------|------|--------|------|--------|---------|\n";
$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)
  {
     $close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
    $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;
    $Data .= "|".substr(str_repeat(" ",5).$i++,-5);
    $Data .= "|".substr($row[0].str_repeat(" ",14),0,14);
    $Data .= "|".substr($row[1].str_repeat(" ",20),0,20);
    $Data .= "|".substr(str_repeat(" ",9).$prrow[0],-9);
    $Data .= "|".substr(str_repeat(" ",8).$close,-8);
    $Data .= "|".substr(str_repeat(" ",14).number_format($pr,2),-10)."|        |          |      |        |      |        |         |\n";
//    $Data .= "|".substr(str_repeat(" ",11).$op_qty,-11);
//    $Data .= "|".substr(str_repeat(" ",11).$row[5],-11);
//    $Data .= "|".substr(str_repeat(" ",12).$row[6],-12);
//    $Data .= "|".substr(str_repeat(" ",9).$row[8],-9);
//    $Data .= "|".substr(str_repeat(" ",11).$row[9],-11);
//    $Data .= "|".substr(str_repeat(" ",9).$prrow[0],-9);
    $Data.= "|-----|--------------|--------------------|---------|--------|----------|--------|----------|------|--------|------|--------|---------|\n";
  }
  }
//  $Data.= "|                                                      Total                                                        ";
//  $Data .= substr(str_repeat(" ",20).number_format($tprice,2),-20)."|\n";

  $Data .= chr(18);
    $Data .= chr(12);
//  $Data.= "|-----|--------------|--------------------|---------|--------|----------|--------|----------|------|--------|------|--------|---------|\n";
  fwrite($handle, $Data);
  fclose($handle);
  //print "lp -o media=Custom.10x6in $file";
  $output = shell_exec("lp -o media=Custom.10x6in '$file'");
    header('location:stockrepodw.php');
?>



