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

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




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

$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");


$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, Company_Name As CompanyName, ifNull(ListOfProductionMaskQty,2) as ListOfProductionMaskQty 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);
}


$querymodule=" Select ifNull(Module_NewProduction,0) As Module_NewProduction From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

$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]);

if ($listby == 1) $ReportTitle = 'List of Finished Goods';
else $ReportTitle = 'List of Raw Materials'; 


//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Exp_ProductionReport.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('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,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  )); 
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ));  
  
$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
  )); 
$boldrightFormatNum =& $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
  ));   

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

$regularrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
   	
$worksheet->setColumn (0, 0, 20); 
$worksheet->setColumn (1, 1, 40); 
$worksheet->setColumn (2, 200, 12); 
//$worksheet->setRow (3, 100);

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,2,$ReportTitle, $TitleFormat);
               
$worksheet->write(1,6,"Printed Date:", $boldleftFormat);
$worksheet->write(1,7,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,6,"Time:", $boldleftFormat);		
$worksheet->write(2,7,$dt[1]." ".$dt[2], $regularleftFormat);
				
$SelectedOptions = "Filter by: \n";
if ($fromdate != '')  $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($curr != '')      $SelectedOptions .= "-> Currency: ". $curr ."\n";	
				
				
				
if (empty($curr))  $curr = $Base1;
  
if (empty($fromitem))  $fromitem = 'null';
else  $fromitem = "'".$fromitem."'";   
if (empty($toitem))  $toitem = 'null';
else  $toitem = "'".$toitem."'";      

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($fromwarehouse))  $fromwarehouse = 'null';
else  $fromwarehouse = "'".$fromwarehouse."'";      


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

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

if (empty($showdetail))   $showdetail = 0;
if (empty($showcost))     $showcost = 0;
if (empty($showunit2qty)) $showunit2qty = 0;
if (empty($showlastprice)) $showlastprice = 0;
if($showlastprice == 1) $showcost=1;   


if ($showdetail == 1) $masterclass = 'master1';
else $masterclass = 'master';


//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 3);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  

$RowNum    = 10;
$ColumnNum = 0;

if ($showcost == 1) {
	if ($listby == 2 && $showidealqty == 1) {
		$ColumnNum = 4;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
		$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
		
		$RowNum   += 1;
		$ColumnNum = 0;

		if ($showunit2qty == 1) {	
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 2;
			$worksheet->write($RowNum,$ColumnNum,"Unit2", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit2Qty", $boldcenterFormat);
			$ColumnNum += 1;
		} else {
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 4;
		}
		
		$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Ideal", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Actual", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Diff", $boldcenterFormat);
		$ColumnNum += 1;
		if($Module_NewProduction == 1) {
			$worksheet->write($RowNum,$ColumnNum,"Actual Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Initial Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Damage Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Remix Qty", $boldcenterFormat);
			$ColumnNum += 1;
		}
		$worksheet->write($RowNum,$ColumnNum,"Currency", $boldcenterFormat);
		$ColumnNum += 1;
		if($showlastprice == 1 && $listby == 2) 
			$worksheet->write($RowNum,$ColumnNum,"Last Price", $boldcenterFormat);
		else 
			$worksheet->write($RowNum,$ColumnNum,"Cost Per Unit", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldcenterFormat);
		$ColumnNum += 1;

	} else {
		if ($showunit2qty == 1) {
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 4;
			$worksheet->write($RowNum,$ColumnNum,"Unit2", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit2Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
			$ColumnNum += 1;
		} else {
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 6;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
			$ColumnNum += 1;
		}
		
		if($Module_NewProduction == 1) {
			$worksheet->write($RowNum,$ColumnNum,"Actual Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Initial Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Damage Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Remix Qty", $boldcenterFormat);
			$ColumnNum += 1;
		}
		
		$worksheet->write($RowNum,$ColumnNum,"Currency", $boldcenterFormat);
		$ColumnNum += 1;
		if($showlastprice == 1 && $listby == 2) 
			$worksheet->write($RowNum,$ColumnNum,"Last Price ", $boldcenterFormat);
		else 
			$worksheet->write($RowNum,$ColumnNum,"Cost Per Unit", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldcenterFormat);
		$ColumnNum += 1;
	}	
} else {
	if ($listby == 2 && $showidealqty == 1) {
		$ColumnNum = 4;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
		$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
		
		$RowNum   += 1;
		$ColumnNum = 0;
		
		if ($showunit2qty == 1) {	
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 2;
			$worksheet->write($RowNum,$ColumnNum,"Unit2", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit2Qty", $boldcenterFormat);
			$ColumnNum += 1;
		} else {	
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 4;
		}	
		
		$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Ideal Qty", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Diff", $boldcenterFormat);
		$ColumnNum += 1;
		if($Module_NewProduction == 1) {
			$worksheet->write($RowNum,$ColumnNum,"Actual Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Initial Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Damage Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Remix Qty", $boldcenterFormat);
			$ColumnNum += 1;
		}
	} else {
	
		if ($showunit2qty == 1) {
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 4;
			$worksheet->write($RowNum,$ColumnNum,"Unit2", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit2Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
			$ColumnNum += 1;
		} else {
			$worksheet->write($RowNum,$ColumnNum,"Item Code / Description", $boldcenterFormat);
			$ColumnNum += 6;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
			$ColumnNum += 1;
		}	
		if($Module_NewProduction == 1) {
			$worksheet->write($RowNum,$ColumnNum,"Actual Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Initial Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Damage Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Remix Qty", $boldcenterFormat);
			$ColumnNum += 1;
		}
	}	

}		  
		  
		
if ($showdetail == 1) {
	$RowNum   += 1;
	$ColumnNum = 0;

	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldcenterFormat);
	$ColumnNum += 1;
				
	if ($showidealqty == 1) {		
		if ($showunit2qty == 1) {
			$worksheet->write($RowNum,$ColumnNum,"Description", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit2", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit2Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Ideal", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Actual", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Diff", $boldcenterFormat);
			$ColumnNum += 1;
			
		} else {
			$worksheet->write($RowNum,$ColumnNum,"Description", $boldcenterFormat);
			$ColumnNum += 3;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Ideal", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Actual", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Diff", $boldcenterFormat);
			$ColumnNum += 1;
		}
	} else {
		if ($showunit2qty == 1) {	
			$worksheet->write($RowNum,$ColumnNum,"Description", $boldcenterFormat);
			$ColumnNum += 3;
			$worksheet->write($RowNum,$ColumnNum,"Unit2", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit2Qty", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
			$ColumnNum += 1;
		} else {
			$worksheet->write($RowNum,$ColumnNum,"Description", $boldcenterFormat);
			$ColumnNum += 5;
			$worksheet->write($RowNum,$ColumnNum,"Unit1", $boldcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum,$ColumnNum,"Qty", $boldcenterFormat);
			$ColumnNum += 1;
		}
	}
				
	if ($showcost == 1) {
		if($showlastprice == 1 && $listby == 1)
			$worksheet->write($RowNum,$ColumnNum,"Last Price", $boldcenterFormat);
		else 
			$worksheet->write($RowNum,$ColumnNum,"Cost Price", $boldcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldcenterFormat);
		$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 Q1.ItemCode, ItemDescription, ProductionQuantity, Currency_Code, ProductionTotalBase1, ProductionTotalBase2,  ".
		 " CostPerUnitBase1, CostPerUnitBase2, ProdType, Unit1FG, Unit2FG, Unit2QtyFG  ";
		 
if ($showdetail	== 1) {
	$query = $query.", ItemCodeDt, ItemDescriptionDt, ProductionDtQuantity, ProductionDtIdealQty, ProductionDtCostBase1, ProductionDtCostBase2, ".
					"  ProductionDtTotalCostBase1, ProductionDtTotalCostBase2, Unit1RM, Unit2RM, Unit2QtyRM ";
} else $query = $query.", null as ItemCodeDt, null as ItemDescriptionDt, null as ProductionDtQuantity, null as ProductionDtIdealQty, null as ProductionDtCostBase1, null as ProductionDtCostBase2, ".
					"  null as ProductionDtTotalCostBase1, null as ProductionDtTotalCostBase2, null as Unit1RM, null as Unit2RM, null as Unit2QtyRM ";
$query = $query." ,initialqty, damageqty, remixqty ";


$query = $query." From ( ";
$query = $query." Select Production.Item_code As ItemCode, Items.Item_Description As ItemDescription, Sum(ifNull(Production_Quantity,0)) As ProductionQuantity,  ".
				" Production.Currency_Code, Round(Sum(ifNull(Production_TotalBase1,0)), ".$DecimalBase.") As ProductionTotalBase1, ".
				" Round(Sum(ifNull(Production_TotalBase2,0)), ".$DecimalBase.") As ProductionTotalBase2, ".
				" Round(Sum(ifNull(Production_TotalBase1,0))/Sum(ifNull(Production_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase1, ".
				" Round(Sum(ifNull(Production_TotalBase2,0))/Sum(ifNull(Production_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase2, ".
				" 'PD' as ProdType, Item_UnitSalesDesc As Unit1FG, Item_UnitPurchaseDesc As Unit2FG, Sum(ifNull(Production.Item_Unit2Qty,0)) As Unit2QtyFG ".
				" , round(ifnull(initialqty,0),2) as initialqty, 0 as damageqty, 0 as remixqty ".
				" From Production ".
				" Inner Join Items on Production.Item_code = Items.Item_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'))  ";
					  
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." Group By ItemCode, ItemDescription  ";	
$query = $query." ) as Q1  ";
			 
if ($showdetail	== 1) {
	$query = $query." INNER JOIN (  ";
	$query = $query." Select Production.Item_code As ItemCode, ProductionDt.Item_code As ItemCodeDt, Items1.Item_Description As ItemDescriptionDt, ".
					" Round(Sum(ifNull(ProductionDt_Quantity,0)), ".$DecimalBase.") As ProductionDtQuantity, ".
					" Round(Sum(ifNull(ProductionDt_IdealQty,0)), ".$DecimalBase.") As ProductionDtIdealQty, ".
					" Round((Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase1,0))/Sum(ifNull(ProductionDt_Quantity,0))), ".$DecimalBase.")  As ProductionDtCostBase1, ".
					" Round((Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase2,0))/Sum(ifNull(ProductionDt_Quantity,0))), ".$DecimalBase.")  As ProductionDtCostBase2, ".
					" Round((Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase1,0))), ".$DecimalBase.") As ProductionDtTotalCostBase1, ".
					" Round((Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase2,0))), ".$DecimalBase.") As ProductionDtTotalCostBase2, ".
					" Item_UnitSalesDesc As Unit1RM, Item_UnitPurchaseDesc As Unit2RM, Round(Sum(ifNull(ProductionDt.Item_Unit2Qty,0)), ".$DecimalBase.") As Unit2QtyRM ".
					" From ProductionDt ".
					" Inner Join Production on ProductionDt.Production_ID = Production.Production_ID ".
					" Inner Join Items as Items1 on ProductionDt.Item_code = Items1.Item_code ".
					" Where ProductionDt.Item_Code Between ifNull(".$fromitemRM.", ProductionDt.Item_Code) and ifNull(". $toitemRM.", ProductionDt.Item_Code) ";
					" And Date_format(Productiondt.Production_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Productiondt.Production_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Productiondt.Production_Date,'%Y-%m-%d'))  ";
	$query = $query." Group By ItemCode, ItemCodeDt, ItemDescriptionDt  ";
	$query = $query." ) as Q2 on Q2.ItemCode = Q1.ItemCode  ";

}



$query = $query." Union "; 
$query = $query." Select Q1.ItemCode, ItemDescription, ProductionQuantity, Currency_Code, ProductionTotalBase1, ProductionTotalBase2,  ".
				" CostPerUnitBase1, CostPerUnitBase2, ProdType, Unit1FG, Unit2FG, Unit2QtyFG  ";
		 
if ($showdetail	== 1) {
	$query = $query.", ItemCodeDt, ItemDescriptionDt, ProductionDtQuantity, ProductionDtIdealQty, ProductionDtCostBase1, ProductionDtCostBase2, ".
					"  ProductionDtTotalCostBase1, ProductionDtTotalCostBase2, Unit1RM, Unit2RM, Unit2QtyRM ";
} else $query = $query.", null as ItemCodeDt, null as ItemDescriptionDt, null as ProductionDtQuantity, null as ProductionDtIdealQty, null as ProductionDtCostBase1, null as ProductionDtCostBase2, ".
					"  null as ProductionDtTotalCostBase1, null as ProductionDtTotalCostBase2, null as Unit1RM, null as Unit2RM, null as Unit2QtyRM ";
$query = $query." ,initialqty, damageqty, remixqty ";

$query = $query." From ( ";
$query = $query." Select ProductionFG.Item_code As ItemCode, Items.Item_Description As ItemDescription, Sum(ifNull(ProductionFG_Quantity,0)) As ProductionQuantity,  ".
			    " ProductionFG.Currency_Code, Round(Sum(ifNull(ProductionFG_CostBase1,0)*ifNull(ProductionFG_Quantity,1)), ".$DecimalBase.") As ProductionTotalBase1, ".
				" Round(Sum(ifNull(ProductionFG_CostBase2,0)*ifNull(ProductionFG_Quantity,1)), ".$DecimalBase.") As ProductionTotalBase2, ".
				" Round(Sum(ifNull(ProductionFG_CostBase1,0)*ifNull(ProductionFG_Quantity,0))/Sum(ifNull(ProductionFG_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase1, ".
				" Round(Sum(ifNull(ProductionFG_CostBase2,0)*ifNull(ProductionFG_Quantity,0))/Sum(ifNull(ProductionFG_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase2, ".
				" 'PDN' as ProdType, Item_UnitSalesDesc As Unit1FG, Item_UnitPurchaseDesc As Unit2FG, Sum(ifNull(ProductionFG.Item_Unit2Qty,0)) As Unit2QtyFG, ".
				" ProductionFG_InitialQty as initialqty, ProductionFG_DamageQty as damageqty, ProductionFG_RemixQty as remixqty ".
				" From ProductionFG ".
				" Inner Join Items on ProductionFG.Item_code = Items.Item_code ".
				" Where Items.Item_Code Between ifNull(".$fromitem.", Items.Item_Code) and ifNull(". $toitem.", Items.Item_Code) ".
				//" And ifNull(ProductionFG.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(ProductionFG.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(ProductionFG.Project_Code,'-1')) ".
				//" And ifNull(ProductionFG.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(ProductionFG.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(ProductionFG.Costcenter_Code,'-1')) ".
				" And ifNull(ProductionFG.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(ProductionFG.Warehouse_Code,'-1')) ".
				" And Date_format(ProductionFG.ProductionHD_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(ProductionFG.ProductionHD_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(ProductionFG.ProductionHD_Date,'%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']."')  ";  

$query = $query." Group By ItemCode, ItemDescription  ";	
$query = $query." ) as Q1  ";
			 
if ($showdetail	== 1) {
	$query = $query." INNER JOIN (  ";
	$query = $query." Select ProductionFG.Item_code As ItemCode, ProductionRM.Item_code As ItemCodeDt, Items1.Item_Description As ItemDescriptionDt, ".
					" Round(Sum(ifNull(ProductionRM_Quantity,0)), ".$DecimalBase.") As ProductionDtQuantity, ".
					" Round(Sum(ifNull(ProductionRM_IdealQty,0)), ".$DecimalBase.") As ProductionDtIdealQty, ".
					" Round((Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase1,0))/Sum(ifNull(ProductionRM_Quantity,0))), ".$DecimalBase.") As ProductionDtCostBase1, ".
					" Round((Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase2,0))/Sum(ifNull(ProductionRM_Quantity,0))), ".$DecimalBase.") As ProductionDtCostBase2, ".
					" Round((Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase1,0) )), ".$DecimalBase.") As ProductionDtTotalCostBase1, ".
					" Round((Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase2,0) )), ".$DecimalBase.") As ProductionDtTotalCostBase2, ".
					" Item_UnitSalesDesc As Unit1RM, Item_UnitPurchaseDesc As Unit2RM, Round(Sum(ifNull(ProductionRM.Item_Unit2Qty,0)), ".$DecimalBase.") As Unit2QtyRM ".
					" From ProductionRM ".
					" Inner Join ProductionFG on ProductionRM.ProductionHD_ID = ProductionFG.ProductionHD_ID ".
					" Inner Join ProductionReqRM on ProductionReqRM.ProductionReqRM_Id = ProductionRM.ProductionReqRM_Id And ProductionReqRM.ProductionReqFG_Id = ProductionFG.ProductionReqFG_Id ".
					" Inner Join Items as Items1 on ProductionRM.Item_code = Items1.Item_code ".
					" Where ProductionRM.Item_Code Between ifNull(".$fromitemRM.", ProductionRM.Item_Code) and ifNull(". $toitemRM.", ProductionRM.Item_Code) ";
					
	$query = $query." Group By ItemCode, ItemCodeDt, ItemDescriptionDt  ";
	$query = $query." ) as Q2 on Q2.ItemCode = Q1.ItemCode  ";

}

if ($showdetail	== 1) {  
	$query = $query." Order By ItemCode, ItemCodeDt ";
} else {
	$query = $query." Order By ItemCode ";
}		 

//echo $query;
		 
$query1 = " Select ProductionDt.Item_code As ItemCode, Items.Item_Description As ItemDescription, ".
		  " Round(Sum(ifNull(ProductionDt_Quantity,0)), ".$DecimalBase.") As ProductionDtQuantity, ProductionDt.Currency_Code,  ".
		  " Round((Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase1,0))), ".$DecimalBase.") As ProductionDtTotalCostBase1, ".
		  " Round((Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase2,0))), ".$DecimalBase.") As ProductionDtTotalCostBase2, ".
		  " Round(Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase1,0))/Sum(ifNull(ProductionDt_Quantity,0)), ".$DecimalBase.") As ProductionDtCostBase1, ".
      	  " Round(Sum(ifNull(ProductionDt_Quantity,0)*ifNull(ProductionDt_CostBase2,0))/Sum(ifNull(ProductionDt_Quantity,0)), ".$DecimalBase.") As ProductionDtCostBase2, ".
		  " 'PD' as ProdType, Item_UnitSalesDesc As Unit1RM, Item_UnitPurchaseDesc As Unit2RM, Round(Sum(ifNull(ProductionDt.Item_Unit2Qty,0)), ".$DecimalBase.") As Unit2QtyRM ";	
			 
if ($showdetail	== 1) {
	$query1 = $query1.",ItemCodeDt, ItemDescriptionDt, ProductionQuantity, Round(Sum(ifNull(ProductionDt_IdealQty,0)), ".$DecimalBase.") As ProductionDtIdealQty, ".
				      " CostPerUnitBase1, CostPerUnitBase2, ProductionTotalBase1, ProductionTotalBase2, Unit1FG, Unit2FG, Unit2QtyFG ";
} else {
	$query1 = $query1.",Null As ItemCodeDt, Null As ItemDescriptionDt, Null As ProductionQuantity, Round(Sum(ifNull(ProductionDt_IdealQty,0)), ".$DecimalBase.") As ProductionDtIdealQty, ".
				      " Null As CostPerUnitBase1, Null As CostPerUnitBase2, Null As ProductionTotalBase1, Null As ProductionTotalBase2, Null As Unit1FG, Null As Unit2FG, Null As Unit2QtyFG ";
}


$query1 = $query1." From ProductionDt ".
			      " Inner Join Items on ProductionDt.Item_code = Items.Item_code ";

if ($showdetail	== 1) {
	$query1 = $query1." Inner Join  ".
	                  " ( Select Production.Item_code As ItemCodeDt, Items.Item_Description As ItemDescriptionDt, ".
			    	  "   ProductionDt.Item_code As QItemCode, Sum(ifNull(Production_Quantity,0)) As ProductionQuantity,  ".
				      "   Round(Sum(ifNull(Production_TotalBase1,0))/Sum(ifNull(Production_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase1, ".
				      "   Round(Sum(ifNull(Production_TotalBase2,0))/Sum(ifNull(Production_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase2, ".
				      "   Round(Sum(ifNull(Production_TotalBase1,0)), ".$DecimalBase.") As ProductionTotalBase1, ".
				      "   Round(Sum(ifNull(Production_TotalBase2,0)), ".$DecimalBase.") As ProductionTotalBase2, ".
					  "   Item_UnitSalesDesc As Unit1FG, Item_UnitPurchaseDesc As Unit2FG, Sum(ifNull(Production.Item_Unit2Qty,0)) As Unit2QtyFG ".
					  "   From Production ".
			          "   Inner Join Items on Production.Item_code = Items.Item_code ".
					  "   Inner Join ProductionDt on ProductionDt.Production_ID = Production.Production_ID ";
					  
	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query1= $query1.$QueryRestrictPrj." on Production.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query1= $query1.$QueryRestrictCst." on Production.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
			 

	$query1 = $query1.  " 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'))  ";
					  
					  
						  
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query1 = $query1." And Production.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	if ($groupexist > 0)
		$query1 = $query1." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
					  
					  
    $query1 = $query1."   Group By ItemCodeDt, ItemDescriptionDt, QItemCode  ".
	                  "   Order By QItemCode, ItemCodeDt ".
					  " ) Q1 on Q1.QItemCode = ProductionDt.Item_code ";
				      
}
		 
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$query1= $query1.$QueryRestrictPrj." on ProductionDt.project_code like concat(Q4.project_code,'%') ";
							 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$query1= $query1.$QueryRestrictCst." on ProductionDt.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 
		 

$query1 = $query1.  " Where ProductionDt.Item_Code Between ifNull(".$fromitemRM.", ProductionDt.Item_Code) and ifNull(". $toitemRM.", ProductionDt.Item_Code) ".
					" And ifNull(ProductionDt.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(ProductionDt.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(ProductionDt.Project_Code,'-1')) ".
				    " And ifNull(ProductionDt.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(ProductionDt.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(ProductionDt.Costcenter_Code,'-1')) ".
					" And ifNull(ProductionDt.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(ProductionDt.Warehouse_Code,'-1')) ".
				    " And Date_format(ProductionDt.Production_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(ProductionDt.Production_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(ProductionDt.Production_Date,'%Y-%m-%d'))  ";
  
if ($showdetail	== 1) {  
	$query1 = $query1." Group By ItemCode, ItemDescription, ItemCodeDt ";
} else {
	$query1 = $query1." Group By ItemCode, ItemDescription ";
}  
  
$query1 = $query1." Union ";  
$query1 = $query1." Select ProductionRM.Item_code As ItemCode, Items.Item_Description As ItemDescription, ".
				  " Round(Sum(ifNull(ProductionRM_Quantity,0)), ".$DecimalBase.") As ProductionDtQuantity, ProductionRM.Currency_Code,  ".
				  " Round((Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase1,0))), ".$DecimalBase.") As ProductionDtTotalCostBase1, ".
				  " Round((Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase2,0))), ".$DecimalBase.") As ProductionDtTotalCostBase2, ".
				  " Round(Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase1,0))/Sum(ifNull(ProductionRM_Quantity,0)), ".$DecimalBase.") As ProductionDtCostBase1, ".
				  " Round(Sum(ifNull(ProductionRM_Quantity,0)*ifNull(ProductionRM_CostBase2,0))/Sum(ifNull(ProductionRM_Quantity,0)), ".$DecimalBase.") As ProductionDtCostBase2, ".
				  " 'PDN' as ProdType, Item_UnitSalesDesc As Unit1RM, Item_UnitPurchaseDesc As Unit2RM, Round(Sum(ifNull(ProductionRM.Item_Unit2Qty,0)), ".$DecimalBase.") As Unit2QtyRM ";
			 
if ($showdetail	== 1) {
	$query1 = $query1.",ItemCodeDt, ItemDescriptionDt, ProductionQuantity, Round(Sum(ifNull(ProductionRM_IdealQty,0)), ".$DecimalBase.") As ProductionDtIdealQty, ".
				      " CostPerUnitBase1, CostPerUnitBase2, ProductionTotalBase1, ProductionTotalBase2, Unit1FG, Unit2FG, Unit2QtyFG ";
} else {
	$query1 = $query1.",Null As ItemCodeDt, Null As ItemDescriptionDt, Null As ProductionQuantity, Round(Sum(ifNull(ProductionRM_IdealQty,0)), ".$DecimalBase.") As ProductionDtIdealQty, ".
				      " Null As CostPerUnitBase1, Null As CostPerUnitBase2, Null As ProductionTotalBase1, Null As ProductionTotalBase2, Null As Unit1FG, Null As Unit2FG, Null As Unit2QtyFG ";
}


$query1 = $query1." From ProductionRM ".
			      " Inner Join Items on ProductionRM.Item_code = Items.Item_code ".
				  " left join (
						SELECT if('$PurchaseTTC' = '1', PurchaseDt_PriceTTCBase1, PurchaseDt_PriceBase1) as lastpriceb1,
						if('$PurchaseTTC' = '1', PurchaseDt_PriceTTCBase2, PurchaseDt_PriceBase2) as lastpriceb2, Item_code
						FROM PurchasesDt WHERE ifNull(PurchaseDt_Price,0) > 0
						Order By Purchase_Date Desc Limit 1
					) as PurchasesDt on PurchasesDt.Item_code = ProductionRM.Item_code ";

if ($showdetail	== 1) {
	$query1 = $query1." Inner Join  ".
	                  " ( Select ProductionFG.Item_code As ItemCodeDt, Items.Item_Description As ItemDescriptionDt, ".
			    	  "   ProductionRM.Item_code As QItemCode, Sum(ifNull(ProductionFG_Quantity,0)) As ProductionQuantity,  ".
				      "   Round(Sum(ifNull(ProductionFG_CostBase1,0)*ifNull(ProductionFG_Quantity,1))/Sum(ifNull(ProductionFG_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase1, ".
				      "   Round(Sum(ifNull(ProductionFG_CostBase1,0)*ifNull(ProductionFG_Quantity,1))/Sum(ifNull(ProductionFG_Quantity,0)), ".$DecimalBase.") As CostPerUnitBase2, ".
				      "   Round(Sum(ifNull(ProductionFG_CostBase1,0)*ifNull(ProductionFG_Quantity,1)), ".$DecimalBase.") As ProductionTotalBase1, ".
				      "   Round(Sum(ifNull(ProductionFG_CostBase2,0)*ifNull(ProductionFG_Quantity,1)), ".$DecimalBase.") As ProductionTotalBase2, ".
					  "   Item_UnitSalesDesc As Unit1FG, Item_UnitPurchaseDesc As Unit2FG, Sum(ifNull(ProductionFG.Item_Unit2Qty,0)) As Unit2QtyFG ".
					  "   From ProductionFG ".
			          "   Inner Join Items on ProductionFG.Item_code = Items.Item_code ".
					  "	  Inner Join ProductionRM on ProductionRM.ProductionHD_ID = ProductionFG.ProductionHD_ID ".
					  "   Inner Join ProductionReqRM on ProductionReqRM.ProductionReqRM_Id = ProductionRM.ProductionReqRM_Id And ProductionReqRM.ProductionReqFG_Id = ProductionFG.ProductionReqFG_Id ";
					  
		 

	$query1 = $query1.  " Where Items.Item_Code Between ifNull(".$fromitem.", Items.Item_Code) and ifNull(". $toitem.", Items.Item_Code) ".
					    //" And ifNull(ProductionFG.Project_Code,'-1') between ifNull(".$fromproject.",ifNull(ProductionFG.Project_Code,'-1')) and ifNull(". $toproject.",ifNull(ProductionFG.Project_Code,'-1')) ".
					    //" And ifNull(ProductionFG.Costcenter_Code,'-1') between ifNull(".$fromcostcent.",ifNull(ProductionFG.Costcenter_Code,'-1')) and ifNull(". $tocostcent.",ifNull(ProductionFG.Costcenter_Code,'-1')) ".
					    //" And ifNull(ProductionFG.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(ProductionFG.Warehouse_Code,'-1')) ".
					    " And Date_format(ProductionFG.ProductionHD_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(ProductionFG.ProductionHD_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(ProductionFG.ProductionHD_Date,'%Y-%m-%d'))  ";
					  
					  
						  
	if ($whexist > 0 && $FI_RepRestrictWarehouse == 1)
		$query1 = $query1." And ProductionFG.warehouse_code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	if ($groupexist > 0)
		$query1 = $query1." And Items.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
					  
					  
    $query1 = $query1."   Group By ItemCodeDt, ItemDescriptionDt, QItemCode  ".
	                  "   Order By QItemCode, ItemCodeDt ".
					  " ) Q1 on Q1.QItemCode = ProductionRM.Item_code ";
				      
}
		 
		 

$query1 = $query1.  " Where ProductionRM.Item_Code Between ifNull(".$fromitemRM.", ProductionRM.Item_Code) and ifNull(". $toitemRM.", ProductionRM.Item_Code) ".
					" And Date_format(ProductionRM.ProductionHD_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(ProductionRM.ProductionHD_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(ProductionRM.ProductionHD_Date,'%Y-%m-%d'))  ".
					" And ifNull(ProductionRM.Warehouse_Code,'-1') = ifNull(". $fromwarehouse .", ifNull(ProductionRM.Warehouse_Code,'-1')) ";
  
if ($showdetail	== 1) {  
	$query1 = $query1." Group By ItemCode, ItemDescription, ItemCodeDt ";
} else {
	$query1 = $query1." Group By ItemCode, ItemDescription ";
}   
 
  
if ($showdetail	== 1) {  
	$query1 = $query1." Order By ItemCode, ItemCodeDt ";
} else {
	$query1 = $query1." Order By ItemCode ";
}
//echo $query1;
//exit;

$RowNum    = 13;
$ColumnNum = 0;

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

$MasterItemCode = '';

if ($listby == 1)
	$result=mysql_query($query,$connection) or die(mysql_error());
else 
	$result=mysql_query($query1,$connection) or die(mysql_error());
	
while($rows=mysql_fetch_row($result)){

	if($curr == '' or $curr == $Base1) {
		$ProductionUnitCost  = $rows[6];
		$ProductionTotalCost = $rows[4];
		$base = 1;
	} else {
		$ProductionUnitCost  = $rows[7];
		$ProductionTotalCost = $rows[5];
		$base = 2;
	}	
		
	if ($showdetail == 0) {	
			 
		$worksheet->write($RowNum, $ColumnNum, $rows[0], $regularleftFormat);
		$ColumnNum += 1;
		
		if ($listby == 2 && $showidealqty == 1) {
			if ($showunit2qty == 1) {
				$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, $rows[10], $regularcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[11], $ListOfProductionMaskQty), $regularrightFormat);
				$ColumnNum += 1;
			} else {
				$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
				$ColumnNum += 3;
			}	
			
			$worksheet->write($RowNum, $ColumnNum, $rows[9], $regularcenterFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[15], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[2], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format(($rows[15] - $rows[2]), $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
		}
		else {
			if ($showunit2qty == 1 && $listby == 1) {
				$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
				$ColumnNum += 3;
				$worksheet->write($RowNum, $ColumnNum, $rows[10], $regularcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[11], $ListOfProductionMaskQty), $regularrightFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, $rows[9], $regularcenterFormat);
				$ColumnNum += 1;
			}	
			else if ($showunit2qty == 1 && $listby == 2) {
				$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
				$ColumnNum += 3;
				$worksheet->write($RowNum, $ColumnNum, $rows[10], $regularcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[11], $ListOfProductionMaskQty), $regularrightFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, $rows[9], $regularcenterFormat);
				$ColumnNum += 1;
			} else {
				$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
				$ColumnNum += 5;
				$worksheet->write($RowNum, $ColumnNum, $rows[9], $regularcenterFormat);
				$ColumnNum += 1;
			}	
			
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[2], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
		}
		if($Module_NewProduction == 1) {
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[2], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[23], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[24], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[25], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
		}	
		if ($showcost == 1) {
			if($showlastprice == 1){
				$lastprice = getLastPrice($rows[0], $base, $PurchaseTTC, $connection);
				$worksheet->write($RowNum, $ColumnNum, $curr, $regularleftFormat);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $lastprice, $regularrightFormatNum);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $lastprice*$rows[2], $regularrightFormatNum);
	
			} else {
				$worksheet->write($RowNum, $ColumnNum, $curr, $regularleftFormat);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $ProductionUnitCost, $regularrightFormatNum);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $ProductionTotalCost, $regularrightFormatNum);
			$ColumnNum += 1;
			}
		} else {
			$ColumnNum += 3;
		}	
	
		$TotalAmount = $TotalAmount +  $ProductionTotalCost;
		
	} else {
		
		if ($MasterItemCode == '' || $MasterItemCode != $rows[0]) {
			
			$worksheet->write($RowNum, $ColumnNum, $rows[0], $boldleftFormat);
			$ColumnNum += 1;
			
			if ($listby == 2 && $showidealqty == 1) {
				if ($showunit2qty == 1) {
					$worksheet->write($RowNum, $ColumnNum, $rows[1], $boldleftFormat);
					$ColumnNum += 1;
					$worksheet->write($RowNum, $ColumnNum, $rows[10], $boldcenterFormat);
					$ColumnNum += 1;
					$worksheet->write($RowNum, $ColumnNum, number_format($rows[11], $ListOfProductionMaskQty), $boldrightFormat);
					$ColumnNum += 1;
				} else {
					$worksheet->write($RowNum, $ColumnNum, $rows[1], $boldleftFormat);
					$ColumnNum += 3;
				}	
				
				$worksheet->write($RowNum, $ColumnNum, $rows[9], $boldcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[15], $ListOfProductionMaskQty), $boldrightFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[2], $ListOfProductionMaskQty), $boldrightFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format(($rows[15] - $rows[2]), $ListOfProductionMaskQty), $boldrightFormat);
				$ColumnNum += 1;
			}
			else {
				if ($showunit2qty == 1) {
					$worksheet->write($RowNum, $ColumnNum, $rows[1], $boldleftFormat);
					$ColumnNum += 3;
					$worksheet->write($RowNum, $ColumnNum, $rows[10], $boldcenterFormat);
					$ColumnNum += 1;
					$worksheet->write($RowNum, $ColumnNum, number_format($rows[11], $ListOfProductionMaskQty), $boldrightFormat);
					$ColumnNum += 1;
				} else {
					$worksheet->write($RowNum, $ColumnNum, $rows[1], $boldleftFormat);
					$ColumnNum += 5;
				}

				$worksheet->write($RowNum, $ColumnNum, $rows[9], $boldcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[2], $ListOfProductionMaskQty), $boldrightFormat);
				$ColumnNum += 1;
			}
			
			if ($showcost == 1) {
				$worksheet->write($RowNum, $ColumnNum, $curr, $boldleftFormat);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $ProductionUnitCost, $boldrightFormatNum);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $ProductionTotalCost, $boldrightFormatNum);
				$ColumnNum += 1;
			} else {
				$ColumnNum += 3;
			}	
			
			$MasterItemCode = $rows[0];
			
			$TotalAmount = $TotalAmount +  $ProductionTotalCost;
		
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 0;
		}

		if($curr == '' or $curr == $Base1) {
			$ProductionDtUnitCost  = $rows[16];
			$ProductionDtTotalCost = $rows[18];
		} else {
			$ProductionDtUnitCost  = $rows[17];
			$ProductionDtTotalCost = $rows[19];
		}	
		
	
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[12], $regularleftFormat);
		$ColumnNum += 1;
		
		if ($showidealqty == 1) {
			$worksheet->write($RowNum, $ColumnNum, $rows[13], $regularleftFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[15], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format($rows[14], $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, number_format(($rows[15] - $rows[14]), $ListOfProductionMaskQty), $regularrightFormat);
			$ColumnNum += 1;
		} else {
			if ($showunit2qty == 1) {
				$worksheet->write($RowNum, $ColumnNum, $rows[13], $regularleftFormat);
				$ColumnNum += 3;
				$worksheet->write($RowNum, $ColumnNum, $rows[21], $regularcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[22], $ListOfProductionMaskQty), $regularrightFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, $rows[20], $regularcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[14], $ListOfProductionMaskQty), $regularrightFormat);
				$ColumnNum += 1;
			} else {
				$worksheet->write($RowNum, $ColumnNum, $rows[13], $regularleftFormat);
				$ColumnNum += 5;
				$worksheet->write($RowNum, $ColumnNum, $rows[20], $regularcenterFormat);
				$ColumnNum += 1;
				$worksheet->write($RowNum, $ColumnNum, number_format($rows[14], $ListOfProductionMaskQty), $regularrightFormat);
				$ColumnNum += 1;
			}	
		}
			
		if ($showcost == 1) {
			$worksheet->writeNumber($RowNum, $ColumnNum, $ProductionDtUnitCost, $regularrightFormatNum);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $ProductionDtTotalCost, $regularrightFormatNum);
			$ColumnNum += 1;
		}	
		
	}
		
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
	
	
}


// Report Totals

if ($showcost == 1) {
	$RowNum += 2;
	if($Module_NewProduction == 1)
		$ColumnNum = 13;
	else $ColumnNum = 9;
	
	
	$worksheet->write($RowNum, $ColumnNum, "Total", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $TotalAmount, $boldrightFormatNum);
	$ColumnNum += 1;
}	



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;
}

$workbook->close();

?>