<?php

$addon_path="../../";

include $addon_path."includes/config.php";
include $addon_path."includes/database.php";
include $addon_path."includes/keyword.php";
include $addon_path."includes/function.php";

$addon_path="";


require('../fpdf16/fpdf.php');


//============Date Gathering start===================================================================


//formating-----------------------------------------------------------------------------------
$doc_paper_size=$_REQUEST["s"]; //A4 or Letter

//parameter-----------------------------------------------------------------------------------	
$selected_date_start_simple = $_REQUEST["start"];
$selected_date_end_simple = $_REQUEST["end"];


$selected_date_start=$selected_date_start_simple." 00:00:00";
$selected_date_end=$selected_date_end_simple." 23:59:59";



//title---------------------------------------------------------------------------------------
$doc_title="Stock Balance with Price Report";


if($_REQUEST["warehouse"]=="")
{
	$criteria1="All Warehouse";
}
else {
	
	$query_info="select * from t_".$key_table_prefix."_warehouse where whs_code = '".$_REQUEST["warehouse"]."' ; ";
	$result_info =mysqli_query($dbconn,$query_info);
	
	if($line_info = mysqli_fetch_array($result_info,MYSQLI_ASSOC))
	{
		$criteria1="".$line_info["whs_code"]." ".$line_info["whs_name"];	
	}
		
	
}


if($_REQUEST["category"]=="")
{
	$criteria2="All Category";
}
else {
	
	$query_info="select * from t_".$key_table_prefix."_category where cat_code = '".$_REQUEST["category"]."' ; ";
	$result_info =mysqli_query($dbconn,$query_info);
	
	if($line_info = mysqli_fetch_array($result_info,MYSQLI_ASSOC))
	{
		$criteria2="".$line_info["cat_code"]." ".$line_info["cat_name"];	
	}
	

}


if($_REQUEST["vendor"]=="")
{
	$criteria3="All Vendor";
}
else {
	
	
	$query_info="select * from t_".$key_table_prefix."_vendor where vdr_code = '".$_REQUEST["vendor"]."' ; ";
	$result_info =mysqli_query($dbconn,$query_info);
	
	if($line_info = mysqli_fetch_array($result_info,MYSQLI_ASSOC))
	{
		$criteria3="".$line_info["vdr_code"]." ".$line_info["vdr_name"];	
	}
	


}


$i=0;


//=================================================================================================



	$query_category="select *
				from t_".$key_table_prefix."_category
				 ; ";
	$result_category =mysqli_query($dbconn,$query_category);
	while($line_category = mysqli_fetch_array($result_category,MYSQLI_ASSOC))
	{
		$category_name[$line_category["cat_code"]]=$line_category["cat_name"];
	}








	$query_balance="select sum(bal_quantity) as bal_quantity ,
				bal_product_code
				from t_".$key_table_prefix."_balance
				";
	if($_REQUEST["warehouse"]!="")		 
	{
		$query_balance = $query_balance." where bal_warehouse_code = '".$_REQUEST["warehouse"]."' ";
	}
	$query_balance = $query_balance." group by bal_product_code  ";
	
	
	$result_balance=mysqli_query($dbconn,$query_balance);
	while($line_balance=mysqli_fetch_array($result_balance,MYSQLI_ASSOC))
	{
		
		$bal_data[$line_balance["bal_product_code"]] = $bal_data[$line_balance["bal_product_code"]] +  $line_balance["bal_quantity"];	
		
	}



	
	$data[$i][0] = "";
	$i++;
	
	$data_width_this_part[0]=40;
	$data_width_this_part[1]=75;
	$data_width_this_part[2]=25;
	$data_width_this_part[3]=25;
	$data_width_this_part[4]=25;
	//$data_width_this_part[5]=25;
	//$data_width_this_part[6]=25;
	//$data_width_this_part[7]=20;
	//$data_width_this_part[8]=20;
	//$data_width_this_part[9]=20;
	
	$data_align_this_part[0]="L";
	$data_align_this_part[1]="L";
	$data_align_this_part[2]="R";
	$data_align_this_part[3]="R";
	$data_align_this_part[4]="R";
	//$data_align_this_part[5]="R";
	//$data_align_this_part[6]="R";
	//$data_width_this_part[7]=20;
	//$data_width_this_part[8]=20;
	//$data_width_this_part[9]=20;
	
	
	//---------------------------------------------------------------------------
	//title:
	/*
	$data_width[$i] = $data_width_this_part;
	$data_align[$i] = $data_align_this_part;
	
	$data_font_size[$i]="BIG";
	$data_bold[$i]="YES";
	$data[$i][0] = "Stock Balance";
	$i++;
	*/
	
	$data[$i][0] = "<line>";
	$i++;	
	
	//header----
	$data_bold[$i]="YES";
	$data_width[$i] = $data_width_this_part;
	$data_align[$i] = $data_align_this_part;
	
	$data[$i][0] = "Product Code";
	$data[$i][1] = "Product Name";
	$data[$i][2] = "Quantity Balance";
	
	$data[$i][3] = "Total Value";
	$data[$i][4] = "Average Cost";
	
	$i++;
		
		
	$data[$i][0] = "<line>";
	$i++;	
	
	
	
	$query_pdt="select * from t_".$key_table_prefix."_product 
			where pdt_id> 0 
			and pdt_inventory_tracking = 'YES'
			";
			//where pdt_code = '".$_SESSION["BALANCE_PDT_CODE"]."'
	if($_REQUEST["category"]!="")
	{
		$query_pdt=$query_pdt." and pdt_category_code = '".$_REQUEST["category"]."' ";
	}	 
	if($_REQUEST["vendor"]!="")
	{
		$query_pdt=$query_pdt." and pdt_vendor_code = '".$_REQUEST["vendor"]."' ";
	}
	
	$query_pdt=$query_pdt." order by pdt_category_code, pdt_code  ";
	
	$result_pdt =mysqli_query($dbconn,$query_pdt);
	
	$sub_total_value=0;
	$temp_cat_name="";
	
	while($line_pdt = mysqli_fetch_array($result_pdt,MYSQLI_ASSOC))
	{
	
		
		
		
		if(($bal_data[$line_pdt["pdt_code"]]!=0)or($_REQUEST["zero"]=="YES"))
		{
			
				
			if(($temp_cat_name != $line_pdt["pdt_category_code"])and($temp_cat_name!=""))
			{
				
				
				
				
				$data_width[$i] = $data_width_this_part;
				$data_align[$i] = $data_align_this_part;
				
				$data_bold[$i]="YES";
				$data[$i][0] = "";
				$data[$i][1] = "";
				$data[$i][2] = "Sub-Total";
				
				$data[$i][3] = number_format($sub_total_value,2,".",",");
				$data[$i][4] = "";
				
				$counter++;
				$i++;
				
				
				$sub_total_value=0;
				
				
				$data[$i][0] = "<line>";
				$i++;	
				
			}
			
			
			if($temp_cat_name != $line_pdt["pdt_category_code"])
			{
				
				
				$sub_total_value=0;
				
				$data_width[$i] = $data_width_this_part;
				$data_align[$i] = $data_align_this_part;
				
				$data_bold[$i]="YES";
				$data[$i][0] = $line_pdt["pdt_category_code"]." :  ".$category_name[$line_pdt["pdt_category_code"]];
				
				$counter++;
				$i++;
				
			}
		
		
		
	
		
			$data_width[$i] = $data_width_this_part;
			$data_align[$i] = $data_align_this_part;
			
			
			$data[$i][0] = $line_pdt["pdt_code"];
			$data[$i][1] = $line_pdt["pdt_name"];
			$data[$i][2] = $bal_data[$line_pdt["pdt_code"]]+0;
			
			$data[$i][3] = number_format($line_pdt["pdt_cost"]*$bal_data[$line_pdt["pdt_code"]],2,".",",");
			$data[$i][4] = number_format($line_pdt["pdt_cost"]*$bal_data[$line_pdt["pdt_code"]]/$bal_data[$line_pdt["pdt_code"]],2,".",",");
			
			$counter++;
			$i++;
			
			$total_value=$total_value + ($line_pdt["pdt_cost"]*$bal_data[$line_pdt["pdt_code"]]);
			$sub_total_value=$sub_total_value + ($line_pdt["pdt_cost"]*$bal_data[$line_pdt["pdt_code"]]);
		
		
			$temp_cat_name = $line_pdt["pdt_category_code"];
			
		}

		
		
		
		
		
		
		
		
	}	
	
	
	
	if(($temp_cat_name != $line_pdt["pdt_category_code"])and($temp_cat_name!=""))
	{
		
		
		
		
		$data_width[$i] = $data_width_this_part;
		$data_align[$i] = $data_align_this_part;
		
		$data_bold[$i]="YES";
		$data[$i][0] = "";
		$data[$i][1] = "";
		$data[$i][2] = "Sub-Total";
		
		$data[$i][3] = number_format($sub_total_value,2,".",",");
		$data[$i][4] = "";
		
		$counter++;
		$i++;
		
		
		$sub_total_value=0;
		
	}
	
	
	
	
	//footer----
	$data[$i][0] = "<line>";
	$i++;	
	
	
	$data_bold[$i]="YES";
	$data_width[$i] = $data_width_this_part;
	$data_align[$i] = $data_align_this_part;
	
	$data[$i][0] = "";
	$data[$i][1] = "";
	$data[$i][2] = "Total";
	
	$data[$i][3] = number_format($total_value,2,".",",");
	$data[$i][4] = "";
	
	$i++;
		
		
	$data[$i][0] = "<line>";
	$i++;	
	
	
	
	






//bottom----------------------------------------------------------------------------------------
$doc_time_stamp=date("Y-m-d H:m:s");
$doc_user=$CurrentUserFullName;






//technical--------------------------------------------------------------------------------------
$doc_debug_line=0;




//============printing start===================================================================
class PDF extends FPDF
{
		
		//Page header
		function Header()
		{
			global $sys_system_master_setting_line;
			global $doc_debug_line;
			global $doc_title;
			global $selected_date_start_simple, $selected_date_end_simple;
		 	
			global $dbconn;
		 	global $table_header;
		 	global $key_table_prefix;
		 	global $data,$data_bold;
		 	 
			 global $criteria1, $criteria2, $criteria3;
			 
		 	 
			//include '../letter_head.php';    
			//$this->Ln(5);
		
		    //line 1======================
			
		   	$this->SetFont('Arial','',13);
		   	$this->SetFont('','B');
			$this->Cell(190,5,$doc_title,$doc_debug_line,10,'L');
			$this->SetFont('','');
			$this->Ln(0);
		    
		  
			 
			//Draw Line Item Table Header====================================== 
		 	$this->SetFillColor(255,255,255);
			$this->SetTextColor(0);
			    //$this->SetDrawColor(128,0,0);
			$this->SetDrawColor(0,0,0);		    
			
			$this->SetLineWidth(.3);
			$this->SetFont('','B');
			//Header

			
			
			$this->Ln(0);
			$this->SetFont('Arial','B',8);
			$this->Cell(30,5,"Warehouse","0",0,'L',true);
			$this->SetFont('Arial','',8);
			$this->Cell(160,5,": ".$criteria1,"0",0,'L',true);
			
			$this->Ln(4);
			$this->SetFont('Arial','B',8);
			$this->Cell(30,5,"Category","0",0,'L',true);
			$this->SetFont('Arial','',8);
			$this->Cell(160,5,": ".$criteria2,"0",0,'L',true);
			$this->Ln(4);
			
			$this->SetFont('Arial','B',8);
			$this->Cell(30,5,"Vendor","0",0,'L',true);
			$this->SetFont('Arial','',8);
			$this->Cell(160,5,": ".$criteria3,"0",0,'L',true);
			$this->Ln(4);
			
			
			$this->SetFont('Arial','B',8);
			$this->Cell(30,5,"Balance as at","0",0,'L',true);
			$this->SetFont('Arial','',8);
			$this->Cell(160,5,": ".date("Y-m-d H:i:s"),"0",0,'L',true);
			$this->Ln(4);
		    
		    
		}

		//Page footer
		function Footer()
		{	
		 
			global $doc_time_stamp,$doc_user,$doc_print_count;
		 	
			//Position at 1.5 cm from bottom
			$this->SetY(-15);
		    
			//Arial italic 8
			$this->SetFont('Arial','I',8);
			
			//Footer (Page Number / timestamp)
			$this->Cell(100,10,"Printed on ".$doc_time_stamp." ".$doc_user.'',0,0,'L');
			$this->Cell(90,10,"Page: ".$this->PageNo()." of {nb}",0,0,'R');
		    
		}
		

		
		function render_grid()
		{
			global $data;			
			global $data_bold;
			global $data_font_size;
			global $data_width;
			global $data_align;
			
			
			
			
			
			$this->SetLineWidth(.3);
			$this->SetFillColor(245,245,245);
			$this->SetTextColor(0);
			$this->SetFont('Arial','',9);
			
			
			
			$fill=false;
			foreach($data as $key=>$row)
			{
				
				$w = $data_width[$key];
				$align = $data_align[$key];
				
				
				
				
				if($row[0]=="<line>")
				{
					if($row[0]!="")
					{
						$length = $row[1];	
					}
					else {
						$length = 190;
					}
					
					$fill=false;
					$this->Cell($length,0,"","B",0,'L',$fill);
					$this->Ln();	
					$item_text_line++;
				}
				else
				{
					
					if($data_bold[$key]=="YES")
					{
						$this->SetFont('Arial','B',7);
						if($data_font_size[$key] =="SMALL")
						{
							$this->SetFont('Arial','B',6);	
						}
						if($data_font_size[$key] =="BIG")
						{
							$this->SetFont('Arial','B',10);	
						}
					}
					else
					{
						$this->SetFont('Arial','',7);
						if($data_font_size[$key] =="SMALL")
						{
							$this->SetFont('Arial','',6);	
						}
						if($data_font_size[$key] =="BIG")
						{
							$this->SetFont('Arial','',10);	
						}
					}
					
					
					
					
					foreach($row as $col => $col_value)
					{
						if($w[$col]>0)
						{
								$this->Cell($w[$col],5,$row[$col],"0",0,$align[$col],$fill);
						}
						
						
					}
					
					$this->Ln();	
					
							
					$item_text_line++;
					//$fill=!$fill;
				}
							
				
					
			
			
			  
			}
			//$this->Cell(array_sum($w),0,'','T');
		}

}

//Instanciation of inherited class


$pdf=new PDF();

$pdf->AliasNbPages();
$pdf->AddPage('P',$doc_paper_size);
$pdf->SetFont('Arial','',8);





$pdf->SetFont('Arial','',8);


$pdf->render_grid();



$pdf->Ln();
//Draw Line Item Table Header====================================== 
$pdf->SetFillColor(255,255,255);
$pdf->SetTextColor(0);
    //$this->SetDrawColor(128,0,0);
$pdf->SetDrawColor(0,0,0);		    

$pdf->SetLineWidth(.3);
$pdf->SetFont('','B');
//Header
/*
$w=array(170,20);
$pdf->Cell($w[0],7,$table_footer[0],"T",0,'R',true);
$pdf->Cell($w[1],7,$table_footer[1],"T",0,'R',true);
$pdf->Ln(7);
*/		    
		    
$pdf->Ln(10);
    
		    
$pdf->Output();




?>