<?
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');
$verdt=$_REQUEST['dfrom'];
$dto1=implode("-",array_reverse(explode("-",$verdt)));
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brnchnm=$brrow[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");
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;

$pdf = new FPDF('L','mm','A4');
$pdf->AddPage();
$pdf->SetFont('Arial','B',10);
$pdf->SetFont('','B');
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brnchnm= $brrow[1];
$pdf->SetFont('','BU');
$pdf->Cell(250,5,'KERALA KHADI & VILLAGE INDUSTRIES BOARD',0,1,'C');
$pdf->SetFont('Arial','',11);
$brnchnm=strtoupper($brnchnm);
$pdf->Cell(250,5,'CENTER NAME :'.$brnchnm,0,1,'C');
$pdf->Cell(250,5,'STOCK VERIFICATION STATEMENT AS ON '.$dto1,0,1,'C');
$pdf->SetFont('Arial','',9);

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'");
	  // print "select * from stockverification where stockverifydt='$verdt' and brcode='$brcode' and itemcode = '$mpartcode'";
	  
       if(mysqli_num_rows($re12) <= 0)
       {
           if($mctr==1)
            {
                 	$pdf->Cell(250,5,'Stock Verification Entry not updated for the following items',0,1,'C');              	
            }
       }
	 $mctr++;
       $mctr_1++;
       if($mctr_1 > 25)
       {
           $mctr_1 = 1;
           $pageno++;
       }
}
$re=mysqli_query($db,"select gname,sum(ledqty*sellprice),sum(curstk*sellprice),sum(stock_short*sellprice),sum(stock_excess*sellprice),count(itemcode) from stockverification where stockverifydt='$verdt' and brcode='$brcode' and not right(gname,3)='VIP' and (not ledqty=0 or not curstk=0) group by gname");
$pdf->Cell(60,10,'Item','LTR');$pdf->Cell(40,10,'Stock as per Ledger','LTR');$pdf->Cell(40,10,'Stock Actually Verified','LTR');
$pdf->Cell(40,10,'Excess','LTR');$pdf->Cell(40,10,'Short','LTR');$pdf->Cell(40,10,'Remarks','LTR');
$pdf->Ln(9);
$i=1;
$tot_led = 0;
$tot_act = 0;
$tot_exc = 0;
$tot_sho = 0;
$ctr_khadi = 1;
$ctr_vip = 1;
$mstr_vip[]="";
$mstr_khadi[]="";
$mkhadi_tot_1 = 0;
$mkhadi_tot_2 = 0;
$mkhadi_tot_3 = 0;
$mkhadi_tot_4 = 0;
$mvip_tot_1 = 0;
$mvip_tot_2 = 0;
$mvip_tot_3 = 0;
$mvip_tot_4 = 0;
$pg=1;
while($row=mysqli_fetch_array($re))
{
       $mtot_pages = ceil($row[5]/25);
           
          
          $pdf->Cell(60,9,"$row[0]($mtot_pages PAGES)",'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[1],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[2],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[4],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[3],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,'','LTRB',0, 'R');
		  $pdf->Ln(9);

                 	$ctr_khadi++;
          $mkhadi_tot_1 += $row[1];
          $mkhadi_tot_2 += $row[2];
          $mkhadi_tot_3 += $row[4];
          $mkhadi_tot_4 += $row[3];
            	
       
	   $tot_led += $row[1];
       $tot_act += $row[2];
       $tot_exc += $row[4];
       $tot_sho += $row[3];
}

		    $pdf->Cell(60,9,"KHADI TOTAL",'LTRB',0, 'L');
			$pdf->Cell(40,9,number_format($mkhadi_tot_1,2,'.',','),'LTRB',0, 'R');
			$pdf->Cell(40,9,number_format($mkhadi_tot_2,2,'.',''),'LTRB',0, 'R');
			$pdf->Cell(40,9,number_format($mkhadi_tot_3,2,'.',','),'LTRB',0, 'R');
			$pdf->Cell(40,9,number_format($mkhadi_tot_4,2,'.',','),'LTRB',0, 'R');
			$pdf->Cell(40,9,'','LTRB',0, 'R');
			$pdf->Ln(9); 
$re=mysqli_query($db,"select gname,sum(ledqty*sellprice),sum(curstk*sellprice),sum(stock_short*sellprice),sum(stock_excess*sellprice),count(itemcode) from stockverification where stockverifydt='$verdt' and brcode='$brcode' and right(gname,3)='VIP' group by gname");		
	while($row=mysqli_fetch_array($re))
{
       $mtot_pages = ceil($row[5]/25);
       
       
		  

		  $pdf->Cell(60,9,"$row[0]($mtot_pages PAGES)",'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[1],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[2],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[4],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,number_format($row[3],2),'LTRB',0, 'L');
		  $pdf->Cell(40,9,'','LTRB',0, 'R');
		  $pdf->Ln(9);

	      $ctr_vip++;
          $mvip_tot_1 += $row[1];
          $mvip_tot_2 += $row[2];
          $mvip_tot_3 += $row[4];
          $mvip_tot_4 += $row[3];
	 
            	
       
	   $tot_led += $row[1];
       $tot_act += $row[2];
       $tot_exc += $row[4];
       $tot_sho += $row[3];
}		
$pdf->Cell(60,9,'VIP TOTAL','LTRB',0, 'L');
$pdf->Cell(40,9,number_format($mvip_tot_1,2,'.',','),'LTRB',0, 'R');
$pdf->Cell(40,9,number_format($mvip_tot_2,2,'.',','),'LTRB',0, 'R');
$pdf->Cell(40,9,number_format($mvip_tot_3,2,'.',''),'LTRB',0, 'R');
$pdf->Cell(40,9,number_format($mvip_tot_4,2,'.',''),'LTRB',0, 'R');
$pdf->Cell(40,9,'','LTRB',0, 'R');
$pdf->Ln(9);

$pdf->Cell(60,10,'GRAND TOTAL','LTRB',0, 'L');
$pdf->Cell(40,10,number_format($mkhadi_tot_1+$mvip_tot_1,2),'LTRB',0, 'R');
$pdf->Cell(40,10,number_format($mkhadi_tot_2+$mvip_tot_2,2),'LTRB',0, 'R');
$pdf->Cell(40,10,number_format($mkhadi_tot_3+$mvip_tot_3,2),'LTRB',0, 'R');
$pdf->Cell(40,10,number_format($mkhadi_tot_4+$mvip_tot_4,2),'LTRB',0, 'R');
$pdf->Cell(40,10,'','LTRB',0, 'R');
$pdf->Ln(9);
$pdf->SetFont('Arial','B',11);
$pdf->Cell(80,10,'Certified that the stock as per the stock list of');$pdf->Cell(80,10,'','');
$pdf->Cell(80,10,'Certified that the stock as per the stock list of');
$pdf->Ln(9);
$pdf->Cell(80,10,$brnchnm);$pdf->Cell(80,10,'','');
$pdf->Cell(80,10,$brnchnm);
$pdf->Ln(9);
$pdf->Cell(80,10,'has been physically verified by me in the presence of the manager and the');$pdf->Cell(80,10,'','');
$pdf->Cell(80,10,'has been physically verified in my presence and the');
$pdf->Ln(9);
$pdf->Cell(80,10,'rate, quantity and value noted against each item is correct.');$pdf->Cell(80,10,'','');
$pdf->Cell(80,10,'rate, quantity and value noted against each item is correct.');
$pdf->Ln(9);
$pdf->Cell(80,10,'Stock Verification Officer');$pdf->Cell(80,10,'','');
$pdf->Cell(80,10,'Manager in charge','C');
$pdf->Ln(9);
$pdf->Cell(80,10,'Dist Khadi and Village Industries');$pdf->Cell(80,10,'','');
$pdf->Cell(80,10,$brnchnm);
$pdf->Ln(4);
$pdf->Output();
?>
