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

$dfrom1=implode("-",array_reverse(explode("-",$dfrom)));
$dto1=implode("-",array_reverse(explode("-",$dto)));
$brres=mysqli_query($db,"select * from branch where brcode='$brcode'");
$brrow=mysqli_fetch_array($brres);
$brnchnm=$brrow[0];
			
				
			//$pdf = new FPDF();
                     $pdf = new FPDF('L','mm','A4');
			$pdf->AddPage();						
			$pdf->SetFont('Arial','B',10);
			$pdf->SetFont('','B');
			$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->Ln(4);
		 	//$pdf->Ln(4);
                     $pdf->Cell(250,5,'STOCK REPORT FROM'.$dfrom1.'TO'.$dto1,0,1,'C');
                     $pdf->SetFont('Arial','',9);
$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,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(qty) from salesrtnwhole 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 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 deliverychallanin 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 deliverychallanout 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 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_outqty) select item_code,item,sum(qty) from prodinvoicewholesale 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(qty) from salesrtnwhole 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,trans_in) select item_code,item,sum(qty) from deliverychallanin 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 deliverychallanout 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 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,sale_out) select item_code,item,sum(qty) from prodinvoicewholesale 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)); 
		
			$pdf->Cell(25,5,'Item Group:'.$type,0,1,'C');
	

                     $pdf->Cell(11,10,'No.','LTR');$pdf->Cell(20,10,'Item Code','LTR');$pdf->Cell(40,10,'Item Name','LTR');$pdf->Cell(15,10,'OP Stock','LTR');
                     $pdf->Cell(20,10,'Purchase','LTR');$pdf->Cell(20,10,'Trans In','LTR');$pdf->Cell(20,10,'Sales Rtn','LTR');$pdf->Cell(20,10,'Sales','LTR');
			$pdf->Cell(20,10,'Trans Out','LTR');
			$pdf->Cell(20,10,'Purch Rtn','LTR');$pdf->Cell(15,10,'Closing','LTR');$pdf->Cell(20,10,'Purch Rate','LTR');$pdf->Cell(20,10,'Sale Rate','LTR');
			$pdf->Cell(20,10,'Amount','LTR');   
			$pdf->Ln(9); 
                    if($subgrp=='All')
			{
			$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");
			$sql="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;
                             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)
						{		
                                         $pdf->Cell(11,9,$i++,'LTRB',0, 'L');$pdf->Cell(20,9,$row[0],'LTRB',0, 'L');$pdf->Cell(40,9,$row[1],'LTRB',0, 'R');
                                         $pdf->Cell(15,9,$op_qty,'LTRB',0, 'R');$pdf->Cell(20,9,$row[4],'LTRB',0, 'R');$pdf->Cell(20,9,$row[5],'LTRB',0, 'R');
                                         $pdf->Cell(20,9,$row[6],'LTRB',0, 'R');$pdf->Cell(20,9,$row[8],'LTRB',0, 'R');$pdf->Cell(20,9,$row[9],'LTRB',0, 'R');
						$pdf->Cell(20,9,$row[10],'LTRB',0, 'R');
						$close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
						$pdf->Cell(15,9,$close,'LTRB',0, 'R');
						$prres=mysqli_query($db,"select ndpcost,lstprice from spareprmast where partcode='$row[0]' and brcode = '$brcode' order by effectfrom desc");
						$prrow=mysqli_fetch_row($prres);
						$pr=($prrow[0]*($close));
						$tprice+=$pr;
						$pdf->Cell(20,9,$prrow[0],'LTRB',0, 'R');$pdf->Cell(20,9,$prrow[1],'LTRB',0, 'R');$pdf->Cell(20,9,$pr,'LTRB',0, 'R');
						
						$locres=mysqli_query($db,"select location from sparestock where itemcode='$row[partcode]' and brcode='$brcode'");
						$locrow=mysqli_fetch_row($locres);
						
                                         $pdf->Ln(9);
						}
					}
			}
			else
			{
				$re=mysqli_query($db,"select partcode from partmaster where subgroup='$subgrp' and gname = '$type' and brcode = '$brcode' order by partcode ");
				$i=1;
				$tprice=0;
				while($row1=mysqli_fetch_array($re))
				{
				$sql1=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 where partcode = '$row1[0]' group by partcode");
				$row=mysqli_fetch_array($sql1);
				$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)
					{
						$pdf->Cell(11,9,$i++,'LTRB',0, 'L');$pdf->Cell(20,9,$row[0],'LTRB',0, 'L');$pdf->Cell(40,9,$row[1],'LTRB',0, 'R');
                                         $pdf->Cell(15,9,$op_qty,'LTRB',0, 'R');$pdf->Cell(20,9,$row[4],'LTRB',0, 'R');$pdf->Cell(20,9,$row[5],'LTRB',0, 'R');
                                         $pdf->Cell(20,9,$row[6],'LTRB',0, 'R');$pdf->Cell(20,9,$row[8],'LTRB',0, 'R');$pdf->Cell(20,9,$row[9],'LTRB',0, 'R');
						$pdf->Cell(20,9,$row[10],'LTRB',0, 'R');
						$close = $op_qty+($row[4]+$row[5]+$row[6]+$row[7])-($row[8]+$row[9]+$row[10]+$row[11]);
						$pdf->Cell(15,9,$close,'LTRB',0, 'R');
						$prres=mysqli_query($db,"select ndpcost,lstprice from spareprmast where partcode='$row[0]' and brcode = '$brcode' order by effectfrom desc");
						$prrow=mysqli_fetch_row($prres);
						$pr=($prrow[0]*($close));
						$tprice+=$pr;
						$pdf->Cell(20,9,$prrow[0],'LTRB',0, 'R');$pdf->Cell(20,9,$prrow[1],'LTRB',0, 'R');$pdf->Cell(20,9,$pr,'LTRB',0, 'R');
						$locres=mysqli_query($db,"select location from sparestock where itemcode='$row[partcode]' and brcode='$brcode'");
						$locrow=mysqli_fetch_row($locres);
						$pdf->Cell(15,9,$locrow[0],'LTRB',0, 'R');
                                         $pdf->Ln(9);
					}
				}
			}	
                     $pdf->Cell(231,10,'Total','LTRB',0, 'L'); 
			$pdf->Cell(40,10,round($tprice,2),'LTRB',0, 'R');
                     
                     $pdf->Ln(4);
                     $pdf->Ln(4);
           		$pdf->Output();	
?>
