<?php


session_start();
if($_SESSION['dbName']==""){
header("location:../index.php");
}

include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.php');

//Set Time Execution For the Report
//ini_set('max_execution_time', $config['ReportTimeExecution']);
set_time_limit(0);

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");

$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Grn,0) As Module_Grn, ifNull(Module_Delivery,0) As Module_Delivery
				, ifNull(Module_Project,0) As Module_Project, ifNull(Module_Costcenter,0) As Module_Costcenter ".
				" ,ifNull(Module_Heatno,0) As Module_Heatno, ifNull(Module_expiredate,0) As Module_expiredate,
				ifNull(Module_NewProduction,0) As Module_NewProduction, ifnull(Module_BatchNo,0) as Module_BatchNo						
				From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

$queryPurchaseSetup="Select PurchaseTTC From PurchaseSetup";
$resultPurchaseSetup=mysql_query($queryPurchaseSetup,$connection) or die(mysql_error());
while($rowPurchaseSetup=mysql_fetch_array($resultPurchaseSetup)){
	extract($rowPurchaseSetup);
}

$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2,report_color1, report_color2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}


$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse, ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice  ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist  = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);		

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$_SESSION['dbLabel'].".loginstk ".
		 "                                          where user_account='".$_SESSION['useraccount']."' ".
		 "                                          and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '') ) ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist  = mysql_num_rows($resultwh);


$querygroup=" select distinct group_code,group_name ".
			" from grouping where group_code in (select group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."') ".
			" order by group_code";
$resultgroup = mysql_query($querygroup,$connection) or die(mysql_error());
$groupexist  = mysql_num_rows($resultgroup);

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);

//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_ListofProduction.xls');

//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List Of Production');
$worksheet->setLandscape();

//set all columns same width
$columnWidth = 10; 

//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
//Elie Format

$regularleftFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
$grouplevel1FormatNum1  =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2FormatNum1  =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3FormatNum1  =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormatNum1  =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormatNum1  =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));  
$summaryFormatNum1  =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ,
  'bold'=>1
  ));   
  
  
  

$regularleftFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel2FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel3FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1totalFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel2totalFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel3totalFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$summaryFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
//Elie format   
  
  
 

// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

 $boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  

$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));  
$summaryFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ,
  'bold'=>1
  ));   

$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap(); 


if (empty($curr))  $curr = $Base1;
if ($showbatch == '') $showbatch = 0;
  
$dispfromdate = $fromdate;
$disptodate   = $todate;

$dispfromedate = $fromedate;
$disptoedate   = $toedate;

$dispcurr     = $curr;


if($Module_Heatno==1 and $vShowHeatno==1 && $fromheatno != '')
	$dispheat = "Heat No. From/To: $fromheatno/$toheatno";

if (empty($fromitem))  $fromitem = 'null';
else  $fromitem = "'".$fromitem."'";   
if (empty($toitem))  $toitem = 'null';
else  $toitem = "'".$toitem."'";      

if (empty($fromheatno))  $fromheatno = 'null';
else  $fromheatno = "'".$fromheatno."'";   
if (empty($toheatno))  $toheatno = 'null';
else  $toheatno = "'".$toheatno."'";      

if (empty($fromitemRM))  $fromitemRM = 'null';
else  $fromitemRM = "'".$fromitemRM."'";   
if (empty($toitemRM))  $toitemRM = 'null';
else  $toitemRM = "'".$toitemRM."'"; 

if (empty($fromproject))  $fromproject = 'null';
else  $fromproject = "'".$fromproject."'";    
if (empty($toproject))  $toproject = 'null';
else  $toproject = "'".$toproject."'";       

if (empty($fromcostcent))  $fromcostcent = 'null';
else  $fromcostcent = "'".$fromcostcent."'";    
if (empty($tocostcent))  $tocostcent = 'null';
else  $tocostcent = "'".$tocostcent."'";       

if (empty($fromgroup))  $fromgroup = 'null';
else  $fromgroup = "'".$fromgroup."'";    
if (empty($togroup))  $togroup = 'null';
else  $togroup = "'".$togroup."'";  

if (empty($fromsubgroup))  $fromsubgroup = 'null';
else  $fromsubgroup = "'".$fromsubgroup."'";    
if (empty($tosubgroup))  $tosubgroup = 'null';
else  $tosubgroup = "'".$tosubgroup."'";  

if (empty($fromcategory))  $fromcategory = 'null';
else  $fromcategory = "'".$fromcategory."'";    
if (empty($tocategory))  $tocategory = 'null';
else  $tocategory = "'".$tocategory."'";  

if (empty($showlastprice)) $showlastprice = 0;

$fromwarehouse1=$fromwarehouse;
   




if (empty($fromwarehouse))  $fromwarehouse = 'null';
else  $fromwarehouse = "'".$fromwarehouse."'";      

$query=" select  warehouse_name ".
         " from warehouse where warehouse_code = $fromwarehouse" ;
		 
$result = mysql_query($query,$connection) or die(mysql_error());
$rows=mysql_fetch_row($result);
$warehousename = $rows[0];

if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);  

if (!empty($fromedate))
   $fromdate = explodeword($fromedate);
if (!empty($toedate))
   $todate = explodeword($toedate);  

if (empty($fromdate))  $fromdate = 'null';
else  $fromdate = "'".$fromdate."'"; 
if (empty($todate))  $todate = 'null';
else  $todate = "'".$todate."'";

if (empty($fromedate))  $fromedate = 'null';
else  $fromedate = "'".$fromedate."'"; 
if (empty($toedate))  $toedate = 'null';
else  $toedate = "'".$toedate."'";

if (empty($showdetail))  $showdetail = 0;
if (empty($showcost))  $showcost = 0; 

 
$SelectedOptions = ' ';
 if ($fromdate != '')      $SelectedOptions .= " Date From/To: ". $fromdate . " - ". $todate ." \n ";	
if($Module_expiredate==1 && $edatechk == 1)
	if ($fromedate != '')      $SelectedOptions .= " Expire Date From/To: ". $fromedate . " - ". $toedate ." \n ";
if ($dispcurr != '')    $SelectedOptions .= " Currency: ". $dispcurr ." \n  ";	
if ($fromwarehouse != 'null') $SelectedOptions .= " Warehouse From/To:  $fromwarehouse1 - $warehousename \n ";
 $SelectedOptions .= " $dispheat \n ";
 if ($frombatchno != '')
 $SelectedOptions .= " Batch No From/To:  $frombatchno - $tobatchno \n ";

 
if (empty($frombatchno))  $frombatchno = 'null';
else  $frombatchno = "'".$frombatchno."'";    
if (empty($tobatchno))  $tobatchno = 'null';
else  $tobatchno = "'".$tobatchno."'";  

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,4,"List Of Production", $TitleFormat);
               
$worksheet->write(1,9,"Printed Date:", $boldleftFormat);
$worksheet->write(1,10,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,9,"Time:", $boldleftFormat);		
$worksheet->write(2,10,$dt[1]." ".$dt[2], $regularleftFormat);

//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
if($Module_expiredate==1 && $edatechk == 1)
	$worksheet->setMerge(3, 0, 6, 11);
else $worksheet->setMerge(3, 0, 5, 11);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat); 

$RowNum    = 10;
$ColumnNum = 0;	

	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Reference", $boldcenterFormat);
	$ColumnNum += 1;
	
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum+3);
	$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
	$ColumnNum += 4;
	if($Module_Heatno==1 and $vShowHeatno==1){	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Heat No.", $boldcenterFormat);
	$ColumnNum += 1;
	}
	
	if($Module_BatchNo==1 and $showbatch==1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
		$worksheet->write($RowNum,$ColumnNum,"Batch No.", $boldcenterFormat);
		$ColumnNum += 1;
	}	 

	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
	$ColumnNum += 1;

if($Module_NewProduction == 1){  
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Actual Qty", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Initial Qty", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Damage Qty", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Remix Qty", $boldcenterFormat);
	$ColumnNum += 1;
}

if ($showcost == 1) {
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Currency", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Cost Per Unit", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldcenterFormat);
	$ColumnNum += 1;
}	
 else {
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum+2);
	$worksheet->write($RowNum,$ColumnNum," ", $boldcenterFormat);
	$ColumnNum += 1;
	
 }
if ($showdetail == 1) {
	$ColumnNum = 1;
	$RowNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum," ", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum+2);
	$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
	$ColumnNum += 3;
	$worksheet->write($RowNum,$ColumnNum,"Warehouse", $boldleftFormat);
	$ColumnNum += 1;
	if($Module_Heatno==1 and $vShowHeatno==1){
		$worksheet->write($RowNum,$ColumnNum,"HeatNo", $boldleftFormat);
		$ColumnNum += 1;
	}
	if($Module_BatchNo==1 and $showbatch==1){
		$worksheet->write($RowNum,$ColumnNum,"Batch No", $boldleftFormat);
		$ColumnNum += 1;
	}	
			
	$worksheet->write($RowNum,$ColumnNum,"Qty", $boldleftFormat);
	$ColumnNum += 1;	
	if ($showcost == 1) {
		$worksheet->write($RowNum,$ColumnNum,"Cost Price", $boldleftFormat);
		$ColumnNum += 1;	
		$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldleftFormat);
		$ColumnNum += 1;	
	}			
	 else if ($showlastprice == 1) {
		$worksheet->write($RowNum,$ColumnNum,"Last Price", $boldleftFormat);
		$ColumnNum += 1;	
		$worksheet->write($RowNum,$ColumnNum,"Total Last Price", $boldleftFormat);
		$ColumnNum += 1;
		
	}
} 
	

$query = '';

$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) Q5  ";
	

$query = " Select Production.Production_ID as ProductionID, Production_Reference as ProductionReference, Date_Format(Production_Date,'%d/%m/%Y') As ProductionDate, ".
		 " Production.Item_code As ItemCode, Item_Description as ItemDescription, Production.Warehouse_Code as WarehouseCode, Warehouse_Name as WarehouseName, ".
		 " ifNull(Production_Quantity,1) as ProductionQuantity,  Production_IdealQty as ProductionIdealQty, Production.Currency_Code as CurrencyCode, ".
		 " Round(ifNull(Production_TotalBase1,0), ".$DecimalBase.") As ProductionTotalBase1, ".
		 " Round(ifNull(Production_TotalBase2,0), ".$DecimalBase.") As ProductionTotalBase2, ".
		 " Round(ifNull(Production_TotalBase1,0)/ifNull(Production_Quantity,1), ".$DecimalBase.") As CostPerUnitBase1, ".
		 " Round(ifNull(Production_TotalBase2,0)/ifNull(Production_Quantity,1), ".$DecimalBase.") As CostPerUnitBase2, ".
		 " 'PD' as ProdType, Null as ProductionReqFGId, Production_Date as OrderProductionDate, Null as productionfg_heatno, Production.project_code, date_format(Production_ExpireDate,'%d-%m-%Y') as ExpireDate ".
		 " , round(ifnull(initialqty,0),2) as initialqty, 0 as damageqty, 0 as remixqty, Production_BatchNo as batchNo ".
		 " From Production ".
		 " Inner Join Items on Production.Item_code = Items.Item_code ".
		 " Left Join Warehouse on Production.Warehouse_Code = Warehouse.Warehouse_Code ".
		 " Left Join grouping on grouping.Group_Code = Items.Group_code ".
		 " Left Join subgroup on subgroup.Group_Code = Items.SubGroup_Code ".
		 " Left Join category on category.Category_Code = Items.Category_code ".
		 " left join (select production_id,sum(productiondt_initialQty) as initialqty
			from productiondt
			group by production_id ) as pdt on pdt.production_id =Production.production_id ";
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$query= $query.$QueryRestrictPrj." on Production.project_code like concat(Q4.project_code,'%') ";
							 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$query= $query.$QueryRestrictCst." on Production.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
		 

$query = $query.  " Where Items.Item_Code Between ifNull(".$fromitem.", Items.Item_Code) and ifNull(". $toitem.", Items.Item_Code) ".
				  " And ifNull(Production.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(Production.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(Production.Project_Code,'-1')) ".
				  " And ifNull(Production.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(Production.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(Production.Costcenter_Code,'-1')) ".
				  " And ifNull(Production.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(Production.Warehouse_Code,'-1')) ".
				  " And Date_format(Production.Production_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Production.Production_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Production.Production_Date,'%Y-%m-%d'))  ".
				  " And ifNull(Items.Group_code,'-1') between ifNull(".$fromgroup.",ifNull(Items.Group_code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_code,'-1')) ".
				  " And ifNull(Items.SubGroup_Code,'-1') between ifNull(".$fromsubgroup.",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1')) ".
				  " And ifNull(Items.Category_code,'-1') between ifNull(".$fromcategory.",ifNull(Items.Category_code,'-1')) and ifNull(". $fromcategory.",ifNull(Items.Category_code,'-1')) ";
if($Module_BatchNo==1)
  $query .= " And ifNull(Production_BatchNo,'-1') between ifNull(".$frombatchno.",ifNull(Production_BatchNo,'-1')) and ifNull(". $tobatchno.",ifNull(Production_BatchNo,'-1')) ";				  
if($Module_expiredate==1 && $edatechk == 1)
	$query=$query." and date_format(Production_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(Production_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(Production_ExpireDate,'%Y-%m-%d')) ";
				  
					  
if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
	$query = $query." And Production.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
if ($groupexist > 0)
	$query = $query." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
  
$query = $query." Union ";
$query = $query." Select ProductionHD.ProductionHD_ID as ProductionID, ProductionHD_Reference as ProductionReference, Date_Format(ProductionHD.ProductionHD_Date,'%d/%m/%Y') As ProductionDate, ".
				" ProductionFG.Item_Code As ItemCode, Item_Description as ItemDescription, ProductionFG.Warehouse_Code as WarehouseCode, Warehouse_Name as WarehouseName, ".
				" ifNull(ProductionFG_Quantity,1) as ProductionQuantity,  ProductionFG_IdealQty as ProductionIdealQty, ProductionFG.Currency_Code as CurrencyCode, ".
				" Round(ifNull(ProductionFG_CostBase1,0)*ifNull(ProductionFG_Quantity,1), ".$DecimalBase.") As ProductionTotalBase1, ".
				" Round(ifNull(ProductionFG_CostBase2,0)*ifNull(ProductionFG_Quantity,1), ".$DecimalBase.") As ProductionTotalBase2, ".
				" Round(ifNull(ProductionFG_CostBase1,0), ".$DecimalBase.") As CostPerUnitBase1, ".
				" Round(ifNull(ProductionFG_CostBase2,0), ".$DecimalBase.") As CostPerUnitBase2, ".
				" 'PDN' as ProdType, ProductionFG.ProductionReqFG_Id as ProductionReqFGId, ProductionHD.ProductionHD_Date as OrderProductionDate, productionfg_heatno, ProductionFG.project_code, date_format(ProductionFG_ExpireDate,'%d-%m-%Y') as ExpireDate, ".
				" ProductionFG_InitialQty as initialqty, ProductionFG_DamageQty as damageqty, ProductionFG_RemixQty as remixqty, ProductionFG_BatchNo as batchNo  ".
				" From ProductionFG ".
				" Inner Join ProductionHD on ProductionFG.ProductionHD_ID = ProductionHD.ProductionHD_ID ".
				" Inner Join Items on ProductionFG.Item_code = Items.Item_code ".
				" Left Join Warehouse on ProductionFG.Warehouse_Code = Warehouse.Warehouse_Code ".
				" Left Join grouping on grouping.Group_Code = Items.Group_code ".
				" Left Join subgroup on subgroup.Group_Code = Items.SubGroup_Code ".
				" Left Join category on category.Category_Code = Items.Category_code ";
		 


$query = $query.  " Where Items.Item_Code Between ifNull(".$fromitem.", Items.Item_Code) and ifNull(". $toitem.", Items.Item_Code)
					And IfNull(ProductionFG.productionfg_heatno,'-1') Between ifNull(".$fromheatno.", IfNull(ProductionFG.productionfg_heatno,'-1')) and ifNull(". $toheatno.", IfNull(ProductionFG.productionfg_heatno,'-1'))".
				 // " And ifNull(ProductionHD.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(ProductionHD.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(ProductionHD.Project_Code,'-1')) ".
				 // " And ifNull(ProductionHD.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(ProductionHD.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(ProductionHD.Costcenter_Code,'-1')) ".
				  " And ifNull(ProductionFG.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(ProductionFG.Warehouse_Code,'-1')) ".
				  " And Date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(ProductionHD.ProductionHD_Date,'%Y-%m-%d'))  ".
				  " And ifNull(Items.Group_code,'-1') between ifNull(".$fromgroup.",ifNull(Items.Group_code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_code,'-1')) ".
				  " And ifNull(Items.SubGroup_Code,'-1') between ifNull(".$fromsubgroup.",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1')) ".
				  " And ifNull(Items.Category_code,'-1') between ifNull(".$fromcategory.",ifNull(Items.Category_code,'-1')) and ifNull(". $fromcategory.",ifNull(Items.Category_code,'-1')) ";
if($Module_BatchNo==1)
  $query .= " And ifNull(ProductionFG_BatchNo,'-1') between ifNull(".$frombatchno.",ifNull(ProductionFG_BatchNo,'-1')) and ifNull(". $tobatchno.",ifNull(ProductionFG_BatchNo,'-1')) ";		 				  
if($Module_expiredate==1 && $edatechk == 1)
	$query=$query." and date_format(ProductionFG_ExpireDate,'%Y-%m-%d') between ifNull(". $fromedate.",date_format(ProductionFG_ExpireDate,'%Y-%m-%d')) and ifNull(". $toedate.",date_format(ProductionFG_ExpireDate,'%Y-%m-%d')) ";
					  
	
if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
	$query = $query." And ProductionFG.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
if ($groupexist > 0)
	$query = $query." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
 
 if($Module_expiredate==1 && $edatechk == 1 && $grpedate == 1) 
	 $query = $query.  " Order By ExpireDate, OrderProductionDate, ProductionReference ";

 else $query = $query.  " Order By OrderProductionDate, ProductionReference ";
		 


 //echo $query;

$TotalAmount  = 0;
$ProductionUnitCost     = 0;
$ProductionTotalCost    = 0;
$ProductionDtUnitCost   = 0;
$ProductionDtTotalCost  = 0;

$prevExpdate = '';
$col=0;
$ColumnNum=0;
$RowNum += 1;
$result=mysql_query($query,$connection) or die(mysql_error()); 

while($rows=mysql_fetch_row($result)){
	
	if($curr == '' or $curr == $Base1) {
		$ProductionUnitCost  = $rows[12];
		$ProductionTotalCost = $rows[10];
	} else {
		$ProductionUnitCost  = $rows[13];
		$ProductionTotalCost = $rows[11];
	}	
    	
	if($Module_expiredate==1 && $grpedate == 1 && $prevExpdate != $rows[19]){
		
		$worksheet->write($RowNum, 1, 'Expire Date:', $boldcenterFormat);
		$worksheet->write($RowNum, 2, $rows[19], $boldcenterFormat);
		$RowNum += 1;
	}
	
	if($showdetail !=1){
		$ColumnNum = 0;
		$worksheet->write($RowNum, $ColumnNum, $rows[2], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum+3);
		$worksheet->write($RowNum, $ColumnNum, $rows[3]." - ".$rows[4], $regularleftFormat);
		$ColumnNum += 4;
		if($Module_Heatno==1 and $vShowHeatno==1){
		$worksheet->write($RowNum, $ColumnNum, $rows[17], $regularleftFormat);
		$ColumnNum += 1;
		}
		if($Module_BatchNo==1 and $showbatch==1){
			$worksheet->write($RowNum, $ColumnNum, $rows[23], $regularleftFormat);
			$ColumnNum += 1;		
		}
		$worksheet->write($RowNum, $ColumnNum, $rows[7], $regularrightFormat);
		$ColumnNum += 1;
		
	}else{
		$ColumnNum = 0;
		$worksheet->write($RowNum, $ColumnNum, $rows[2], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum+3);
		$worksheet->write($RowNum, $ColumnNum, $rows[3]." - ".$rows[4], $regularleftFormat);
		$ColumnNum += 4;
		if($Module_Heatno==1 and $vShowHeatno==1){
			$worksheet->write($RowNum, $ColumnNum, $rows[17], $regularleftFormat);
			$ColumnNum += 1;
		}
		if($Module_BatchNo==1 and $showbatch==1){
			$worksheet->write($RowNum, $ColumnNum, $rows[23], $regularleftFormat);
			$ColumnNum += 1;		
		}
		$worksheet->write($RowNum, $ColumnNum, $rows[7], $regularrightFormat);
		$ColumnNum += 1;
	}
	
	if($Module_NewProduction == 1) {
		$worksheet->write($RowNum, $ColumnNum, $rows[7], $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[20], $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[21], $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[22], $regularrightFormat);
		$ColumnNum += 1;
	}
	
	if ($showcost == 1) {
		$worksheet->write($RowNum, $ColumnNum, $curr, $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, number_format($ProductionUnitCost, $DecimalBase), $regularrightFormatNum1);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, number_format($ProductionTotalCost, $DecimalBase), $regularrightFormatNum1);
		$ColumnNum += 1;
	} else {
		$worksheet->setMerge($RowNum, $ColumnNum,$RowNum , $ColumnNum+2);
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
	}
	$RowNum += 1;
	
	if ($showdetail == 1) {
	
		if ($rows[14] == 'PDN') {
			$querydt = " Select ProductionRM.Item_code, Items.Item_Description, ProductionRM.Warehouse_Code, ".
					   " Round(ifNull(ProductionRM_Quantity,0), ".$DecimalBase.") As ProductionRM_Quantity, ProductionRM_IdealQty, ".
					   " if('$showlastprice' = '1', Round(ifNull(GetLastValuesFunc('$Base1','$Base1',ProductionRM.Item_code),0), ".$DecimalBase."), Round(ifNull(ProductionRM_CostBase1,0), ".$DecimalBase.")) As ProductionDtCostBase1, ".
					   " if('$showlastprice' = '1', Round(ifNull(GetLastValuesFunc('$Base2','$Base2',ProductionRM.Item_code),0), ".$DecimalBase."), Round(ifNull(ProductionRM_CostBase2,0), ".$DecimalBase.")) As ProductionDtCostBase2, ".
					   " Round((ifNull(ProductionRM_Quantity,0)*if('$showlastprice' = '1',ifNull(GetLastValuesFunc('$Base1','$Base1',ProductionRM.Item_code),0),ifNull(ProductionRM_CostBase1,0))), ".$DecimalBase.") As TotalCostBase1, ".
					   " Round((ifNull(ProductionRM_Quantity,0)*if('$showlastprice' = '1',ifNull(GetLastValuesFunc('$Base2','$Base2',ProductionRM.Item_code),0),ifNull(ProductionRM_CostBase2,0))), ".$DecimalBase.") As TotalCostBase2, ".
					   " ProductionRM_BatchNo as batchno ".
					   " From ProductionRM  ".
					   " Inner Join ProductionReqRM on ProductionReqRM.ProductionReqRM_Id = ProductionRM.ProductionReqRM_Id ".
					   " Inner Join Items on ProductionRM.Item_code = Items.Item_code ".
					   " Where ProductionRM.ProductionHD_ID = '".$rows[0]."' ".
					   " And ProductionReqRM.ProductionReqFG_Id = '".$rows[15]."' ".
					   " And ProductionRM.Item_Code Between ifNull(".$fromitemRM.", ProductionRM.Item_Code) and ifNull(". $toitemRM.", ProductionRM.Item_Code) ".
					   " Order By ProductionRM.Item_code ";
					   
		} else {
			$querydt = " Select ProductionDt.Item_code, Items.Item_Description, ProductionDt.Warehouse_Code, ".
					   " Round(ifNull(ProductionDt_Quantity,0), ".$DecimalBase.") As ProductionDtQuantity, ProductionDt_IdealQty, ".
					   " if('$showlastprice' = '1', Round(ifNull(GetLastValuesFunc('$Base1','$Base1',Productiondt.Item_code),0), ".$DecimalBase."), Round(ifNull(ProductionDt_CostBase1,0), ".$DecimalBase.")) As ProductionDtCostBase1, ".
					   " if('$showlastprice' = '1', Round(ifNull(GetLastValuesFunc('$Base2','$Base2',Productiondt.Item_code),0), ".$DecimalBase."), Round(ifNull(ProductionDt_CostBase2,0), ".$DecimalBase.")) As ProductionDtCostBase2, ".
					   " Round((ifNull(ProductionDt_Quantity,0)*if('$showlastprice' = '1',ifNull(GetLastValuesFunc('$Base1','$Base1',Productiondt.Item_code),0),ifNull(ProductionDt_CostBase1,0))), ".$DecimalBase.") As TotalCostBase1, ".
					   " Round((ifNull(ProductionDt_Quantity,0)*if('$showlastprice' = '1',ifNull(GetLastValuesFunc('$Base2','$Base2',Productiondt.Item_code),0),ifNull(ProductionDt_CostBase2,0))), ".$DecimalBase.") As TotalCostBase2, ".
					   " ProductionDt_BatchNo as batchno ".
					   " From ProductionDt  ".
					   " Inner Join Items on ProductionDt.Item_code = Items.Item_code ".
					   " Where ProductionDt.Production_ID = '".$rows[0]."' ".
					   " And ProductionDt.Item_Code Between ifNull(".$fromitemRM.", ProductionDt.Item_Code) and ifNull(". $toitemRM.", ProductionDt.Item_Code) ".
					   " Order By ProductionDt.Item_code ";
		}
			
		$resultdt=mysql_query($querydt,$connection) or die(mysql_error());
		$col1=0; $ColumnNum1 = 1;	
		while($rowsdt=mysql_fetch_row($resultdt)){
		
			if($curr == '' or $curr == $Base1) {
				$ProductionDtUnitCost  = $rowsdt[5];
				$ProductionDtTotalCost = $rowsdt[7];
			} else {
				$ProductionDtUnitCost  = $rowsdt[6];
				$ProductionDtTotalCost = $rowsdt[8];
			}	
			
			$worksheet->write($RowNum, $ColumnNum1, '', $regularrightFormatNum1);
			$ColumnNum1 += 1;		
			$worksheet->write($RowNum, $ColumnNum1, $rowsdt[0], $regularrightFormatNum1);
			$ColumnNum1 += 1;
			$worksheet->setMerge($RowNum, $ColumnNum1, $RowNum, $ColumnNum1+2);
			$worksheet->write($RowNum, $ColumnNum1, $rowsdt[1], $regularrightFormatNum1);
			$ColumnNum1 += 3;
			$worksheet->write($RowNum, $ColumnNum1, $rowsdt[2], $regularrightFormatNum1);
			$ColumnNum1 += 1;
			if($Module_BatchNo==1 and $showbatch==1){
				$worksheet->write($RowNum, $ColumnNum1, $rowsdt[9], $regularrightFormatNum1);
				$ColumnNum1 += 1;
			}
			$worksheet->write($RowNum, $ColumnNum1, $rowsdt[3], $regularrightFormatNum1);
			$ColumnNum1 += 1;
			if ($showcost == 1 || $showlastprice == 1) {
				$worksheet->writeNumber($RowNum, $ColumnNum1, $ProductionDtUnitCost, $regularrightFormatNum1);
				$ColumnNum1 += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum1, $ProductionDtTotalCost, $regularrightFormatNum1);
				$ColumnNum1 += 1;
			}	
			else {
				// Merge cells from row 3, col 0 to row 3, col 0
				$worksheet->setMerge($RowNum, $ColumnNum1,$RowNum , $ColumnNum1+1);
				$worksheet->write($RowNum, $ColumnNum1, '', $regularrightFormatNum1);
				$ColumnNum1 += 3;
			}
			
			$RowNum += 1;
			$ColumnNum1 = 1;
		}
		
	} 
	$TotalAmount = $TotalAmount +  $ProductionTotalCost;
	$prevExpdate = $rows[19];
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
} 

if ($showcost == 1) {
	$RowNum += 1;
	if($Module_NewProduction == 1)
		$ColumnNum = 12;
	else $ColumnNum = 8;
	if($Module_Heatno==1 and $vShowHeatno==1)  $ColumnNum++;
	
	$worksheet->write($RowNum, $ColumnNum, 'Total', $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $TotalAmount, $regularrightFormatNum1);
	
} 


function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}

function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}



$workbook->close();

?>