<?
session_start();
include("../opendb.php");
include('mysqli_table.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');

      $type=$_REQUEST['type'];

 //$dfrom=$y1.'-'.$m1.'-'.$d1;
 //$dto=$y2.'-'.$m2.'-'.$d2;
  //$dto1=implode("-",array_reverse(explode("-",$verdt)));
$dto1=date('d-m-Y');
$brqry="select brname from branch where brcode='$brcode'";
$brrs=mysqli_query($db,$brqry);
$brrw=mysqli_fetch_array($brrs);
$brnchnm=$brrw[0];
$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) 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));
	
}
else
{

    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;
$pageno = 1;
       
			
				
			//$pdf = new FPDF();
                     $pdf = new FPDF('P','mm','A4');
			$pdf->AddPage();						
			$pdf->SetFont('Arial','B',10);
			$pdf->SetFont('','B');
			$dedres1=mysqli_query($db,"select brname from branch where brcode='$brcode'") or die("er5:".mysqli_error($db));
			$br_1=mysqli_fetch_array($dedres1);
			$pdf->SetFont('','BU');
			$pdf->Cell(210,5,'KERALA KHADI & VILLAGE INDUSTRIES BOARD',0,1,'C');
			$pdf->SetFont('Arial','',11);
			$brnchnm=strtoupper($brnchnm);						
                     $pdf->Cell(210,5,'CENTER NAME :'.$brnchnm,0,1,'C');
                     //$pdf->Ln(4);
		 	//$pdf->Ln(4);
                     $pdf->Cell(210,5,'STOCK VERIFICATION STATEMENT AS ON '.$dto1,0,1,'C');
                     $pdf->SetFont('Arial','',10);
                     $pdf->Cell(13,10,'No.','LTR');$pdf->Cell(30,10,'Item Code','LTR');$pdf->Cell(50,10,'Item Name','LTR');$pdf->Cell(21,10,'Rate','LTR');
                     $pdf->Cell(40,10,'Stock as per Ledger','LTR');$pdf->Cell(40,10,'Stock actually verified','LTR');                
                     //$pdf->Ln(4);
                     $pdf->Ln(4);
                     $pdf->Ln(4);
                     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;
     						
						if($mctr>25)
     						{
         						$mctr = 1;
         						//$new_page = 1;
							
							$pdf->AddPage();
							$pdf->SetFont('','BU');
			$pdf->Cell(210,5,'KERALA KHADI & VILLAGE INDUSTRIES BOARD',0,1,'C');
			$pdf->SetFont('Arial','',11);
			$brnchnm=strtoupper($brnchnm);						
                     $pdf->Cell(210,5,'CENTER NAME :'.$brnchnm,0,1,'C');
                     //$pdf->Ln(4);
		 	//$pdf->Ln(4);
                     $pdf->Cell(210,5,'STOCK VERIFICATION STATEMENT AS ON '.$dto1,0,1,'C');
                     $pdf->SetFont('Arial','',9);
                     $pdf->Cell(13,10,'No.','LTR');$pdf->Cell(30,10,'Item Code','LTR');$pdf->Cell(50,10,'Item Name','LTR');$pdf->Cell(21,10,'Rate','LTR');
                     $pdf->Cell(40,10,'Stock as per Ledger','LTR');$pdf->Cell(40,10,'Stock actually verified','LTR'); 
			$pdf->Ln(9); 
     						}
                                         $pdf->Cell(13,9,$i++,'LTRB',0, 'R');$pdf->Cell(30,9,$row[0],'LTRB',0, 'L');$pdf->Cell(50,9,$row[1],'LTRB',0, 'L');$pdf->Cell(21,9,number_format($prrow[0],2,'.',','),'LTRB',0, 'R');
                                         $pdf->Cell(20,9,number_format($close,2,'.',''),'LTRB',0, 'R');$pdf->Cell(20,9,number_format($pr,2,'.',','),'LTRB',0, 'R');$pdf->Cell(20,9,'','LTRB',0, 'R');
                                         $pdf->Cell(20,9,'','LTRB',0, 'R');
						
                                         $pdf->Ln(9);
						++$mctr;
						
                                         //$pdf->Ln(4);    
                                                        
                                  }

					$totrate+=$prrow[0];
					$totledqty+=$close;
					$totledvl+=$pr;
				}
                           	//$pdf->Ln(4);
                           //$pdf->Ln(4);                        
                        	$pdf->Cell(13,10,'Total','LTRB',0, 'R');$pdf->Cell(30,10,'','LTRB',0, 'R');$pdf->Cell(50,10,'','LTRB',0, 'R');$pdf->Cell(21,10,round($totrate,2),'LTRB',0, 'R');
                           $pdf->Cell(20,10,round($totledqty,2),'LTRB',0, 'R');$pdf->Cell(20,10,round($totledvl,2),'LTRB',0, 'R');$pdf->Cell(20,10,'','LTRB',0, 'R');$pdf->Cell(20,10,'','LTRB',0, 'R');
                           
                          
                        	//$pdf->Ln(4);
                           $pdf->Ln(4);
                           $pdf->Ln(4);
                      
				$pdf->Output();	
?>
