<?php
require_once('Spreadsheet/Excel/Writer.php');

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


include("config.inc.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($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");


$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, ifNull(ListOfProductionMaskQty,0) As ListOfProductionMaskQty, 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_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse, ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice  ".
          " from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
		 "                                          where user_account='".$HTTP_SESSION_VARS['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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['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 (empty($curr))  $curr = $Base1;



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

//Sending HTTP headers
$workbook->send('Exp_ProductionReportCosting.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Production Cost');
$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'  ));  
  
$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
  ));    
  
$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();
$boldcenterFormat->setTextWrap();

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."\n";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse: ". $fromwarehouse ."\n";	
if ($curr != '')          $SelectedOptions .= "-> Currency: ". $curr ."\n";	

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

   
$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');     
$fromwarehouse = GetSQLValueString($fromwarehouse, 'date');     


	
//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,5,"Direct Cost of Production", $TitleFormat);
               
$worksheet->write(1,10,"Printed Date:", $boldleftFormat);
$worksheet->write(1,11,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,10,"Time:", $boldleftFormat);		
$worksheet->write(2,11,$dt[1]." ".$dt[2], $regularleftFormat);
  
//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 6);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  


$RowNum    = 10;
$ColumnNum = 0;


	  
$ArrayItemFG     = Array();
$ArrayItemDescFG = Array();
$ArrayItemRM     = Array();
$ArrayItemDescRM = Array();

$ArrayDataFG    = Array();
$ArrayDataRM    = Array(); 


$ArrayNonStockItemRM  = Array();
$ArrayDataNonStockRM  = Array(); 

$sub_query = " Select ProductionFG.ProductionFG_ID, ProductionReqFG_ID, ProductionFG.Item_Code As ItemCodeFG, Item_Description as ItemDescriptionFG, ".
			 " ProductionFG.Warehouse_Code As WarehouseCodeFG, ifNull(ProductionFG_Quantity,0) As ProductionFG_Quantity, Items.Item_UnitSalesDesc As ItemUnitFG ".
			 " From ProductionFG Left Join Items on ProductionFG.Item_Code = Items.Item_code ";
			 
$sub_query = $sub_query." Where ProductionFG.Item_Code Between ifNull(".$fromitem.", ProductionFG.Item_Code) and ifNull(". $toitem.", ProductionFG.Item_Code) ".
					    " 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)
	$sub_query = $sub_query." And ProductionFG.warehouse_code In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
if ($groupexist > 0)
	$sub_query = $sub_query." And Items.group_code In (select distinct group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  ";  
	

$sub_query = $sub_query." Order By ProductionFG.Item_Code ";
	
//echo $sub_query;
$sub_result=mysql_query($sub_query,$connection) or die(mysql_error());
while($sub_rows=mysql_fetch_array($sub_result)){
	extract($sub_rows);
		
	//Check If ItemFG Exists In Array
	$ItemFGFound = 0;
	$count = count ($ArrayItemFG); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayItemFG[$j] == $ItemCodeFG)
			$ItemFGFound = 1;
	}
					
	if ($ItemFGFound == 0){
		$ArrayItemFG[$count] = $ItemCodeFG;
		$ArrayItemDescFG[$count] = $ItemDescriptionFG;
	}	
			
	$ArrayDataFG[$ItemCodeFG]['Qty'] = $ProductionFG_Quantity;
		
	$sub_query1 = " Select ProductionRM.ProductionRM_ID, ProductionRM.Item_Code As ItemCodeRM, Item_Description As ItemDescriptionRM, ".
				  " ProductionRM.Warehouse_Code As WarehouseCodeRM, ifNull(ProductionRM_Quantity,0) As ProductionRM_Quantity, ".
				  " Items.Item_UnitSalesDesc As ItemUnitRM, ifNull(ProductionRM_CostBase1,0) As ProductionRM_CostBase1, ".
				  " ifNull(ProductionRM_CostBase2,0) As ProductionRM_CostBase2, ProductionRM_WasteRate, ProductionRM_WastePerBillet, ".
				  " ifNull(Items.Item_NonStock,0) As Item_NonStock ".
				  " From ProductionRM Left Join Items on ProductionRM.Item_Code = Items.Item_code ".
				  " Left Join ProductionReqRM on ProductionReqRM.ProductionReqRM_Id = ProductionRM.ProductionReqRM_Id ".
				  " Where ProductionReqRM.ProductionReqFG_Id = '".$ProductionReqFG_ID."' ".
				  " Order By ProductionRM.Item_Code ";
			
	$sub_result1=mysql_query($sub_query1,$connection) or die(mysql_error());
	while($sub_rows1=mysql_fetch_array($sub_result1)){
		extract($sub_rows1);
				
		//Check If ItemRM Exists In Array
		If ($Item_NonStock == 1) {
			if($curr == '' or $curr == $Base1) {
				$ItemCostRM = $ProductionRM_CostBase1;
			} else {
				$ItemCostRM = $ProductionRM_CostBase2;
			}
			
			$ItemRMFound = 0;
			$count = count ($ArrayNonStockItemRM['Code']); 
			for ($j=0; $j<$count; $j++){
				if ($ArrayNonStockItemRM['Code'][$j] == $ItemCodeRM)
					$ItemRMFound = 1;
			}
							
			if ($ItemRMFound == 0) { 
				$ArrayNonStockItemRM['Code'][$count] = $ItemCodeRM;	
				$ArrayNonStockItemRM['Unit'][$count] = $ItemUnitRM;	
				$ArrayNonStockItemRM['UnitCost'][$count] = $ItemCostRM;	
			}	
			
			$ArrayDataNonStockRM[$ItemCodeRM][$ItemCodeFG]['Qty'] = $ProductionRM_Quantity;
			$ArrayDataNonStockRM[$ItemCodeRM][$ItemCodeFG]['Cost'] = $ItemCostRM;
			$ArrayDataNonStockRM[$ItemCodeRM][$ItemCodeFG]['TotalCost'] = $ItemCostRM*$ProductionRM_Quantity;
		}
		else {
			if($curr == '' or $curr == $Base1) {
				$ItemCostRM = $ProductionRM_CostBase1;
			} else {
				$ItemCostRM = $ProductionRM_CostBase2;
			}
			
			$ItemRMFound = 0;
			$count = count ($ArrayItemRM['Code']); 
			for ($j=0; $j<$count; $j++){
				if ($ArrayItemRM['Code'][$j] == $ItemCodeRM)
					$ItemRMFound = 1;
			}
							
			if ($ItemRMFound == 0) { 
				$ArrayItemRM['Code'][$count] = $ItemCodeRM;	
				$ArrayItemRM['Unit'][$count] = $ItemUnitRM;	
			}	
					
				
			$ArrayDataRM[$ItemCodeRM][$ItemCodeFG]['Qty'] = $ProductionRM_Quantity;
			$ArrayDataRM[$ItemCodeRM][$ItemCodeFG]['Cost'] = $ItemCostRM;
			$ArrayDataRM[$ItemCodeRM][$ItemCodeFG]['TotalCost'] = $ItemCostRM*$ProductionRM_Quantity;
			$ArrayDataRM[$ItemCodeRM][$ItemCodeFG]['WasteRate'] = $ProductionRM_WasteRate;
			$ArrayDataRM[$ItemCodeRM][$ItemCodeFG]['WastePerBillet'] = $ProductionRM_WastePerBillet;

		}	
	}
			
}

$RowNum    = 11;
$ColumnNum = 1;


if($curr == '' or $curr == $Base1) {
	$DecimalBase = $DecimalBase1;
} else {
	$DecimalBase = $DecimalBase2;
}

//sorting data in array
array_multisort($ArrayItemFG);
array_multisort($ArrayItemRM);
array_multisort($ArrayNonStockItemRM);

$CountItemRM = count($ArrayNonStockItemRM['Code']);
$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + $CountItemRM - 1);
$worksheet->write($RowNum,$ColumnNum,"Power for Production (Qty)", $boldcenterFormat);
$ColumnNum += $CountItemRM;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + $CountItemRM - 1);
$worksheet->write($RowNum,$ColumnNum,"Cost of Production ($curr)", $boldcenterFormat);
$ColumnNum += $CountItemRM;

$CountItemRM = count($ArrayItemRM['Code']);
$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + ($CountItemRM*6) - 1);
$worksheet->write($RowNum,$ColumnNum,"Raw Material", $boldcenterFormat);
$ColumnNum += ($CountItemRM*6);

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
$worksheet->write($RowNum,$ColumnNum,"Result", $boldcenterFormat);
$ColumnNum += 2;

$RowNum += 1;
$ColumnNum = 0;	

$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$ColumnNum += 1;

$CountItemRM = count($ArrayNonStockItemRM['Code']);
for ($i=0; $i<$CountItemRM; $i++){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,$ArrayNonStockItemRM['Code'][$i]."\n".$ArrayNonStockItemRM['Unit'][$i]."/TON", $boldcenterFormat);
	$ColumnNum += 1;
}

$CountItemRM = count($ArrayNonStockItemRM['Code']);
for ($i=0; $i<$CountItemRM; $i++){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,$ArrayNonStockItemRM['Code'][$i]."/TON"."\n".$ArrayNonStockItemRM['UnitCost'][$i]."/".$ArrayNonStockItemRM['Unit'][$i], $boldcenterFormat);
	$ColumnNum += 1;
}

$CountItemRM = count($ArrayItemRM['Code']);
for ($i=0; $i<$CountItemRM; $i++){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Raw Material\n"."Cost/TON", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Waste Rate", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Waste/Billet", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Damage", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Waste Cost/Ton", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Other \nDirect Cost\n(Lubricants and \nconsumabels)", $boldcenterFormat);
	$ColumnNum += 1;
}

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Total Cost\n $curr/Ton", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Indirect Cost\nof Production", $boldcenterFormat);
$ColumnNum += 1;


$RowNum += 1;
$ColumnNum = 0;	



$CountItemFG = count($ArrayItemFG);
for ($i=0; $i<$CountItemFG; $i++){
	$worksheet->write($RowNum,$ColumnNum,$ArrayItemFG[$i], $boldleftFormat);
	$ColumnNum += 1;
	
	$TotalCosts = 0;
	
	$CountItemRM = count($ArrayNonStockItemRM['Code']);
	for ($j=0; $j<$CountItemRM; $j++){
		$ItemQtyRM = $ArrayDataNonStockRM[$ArrayNonStockItemRM['Code'][$j]][$ArrayItemFG[$i]]['Qty'];
		$worksheet->write($RowNum,$ColumnNum,number_format($ItemQtyRM, $ListOfProductionMaskQty), $regularrightFormat);
		$ColumnNum += 1;
	}
	
	$CountItemRM = count($ArrayNonStockItemRM['Code']);
	for ($j=0; $j<$CountItemRM; $j++){
		$ItemTotalCostRM = $ArrayDataNonStockRM[$ArrayNonStockItemRM['Code'][$j]][$ArrayItemFG[$i]]['TotalCost'];
		$worksheet->write($RowNum,$ColumnNum,number_format($ItemTotalCostRM, $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
		
		$TotalCosts += $ItemTotalCostRM;
	}
	
	$CountItemRM = count($ArrayItemRM['Code']);
	for ($j=0; $j<$CountItemRM; $j++){
		$WasteRateRM	  = $ArrayDataRM[$ArrayItemRM['Code'][$j]][$ArrayItemFG[$i]]['WasteRate'];
		$WastePerBilletRM = $ArrayDataRM[$ArrayItemRM['Code'][$j]][$ArrayItemFG[$i]]['WastePerBillet'];
		$ItemTotalCostRM  = $ArrayDataRM[$ArrayItemRM['Code'][$j]][$ArrayItemFG[$i]]['Cost'];

		$TotalCosts += $ItemTotalCostRM;
		
		$worksheet->write($RowNum,$ColumnNum,number_format($ItemTotalCostRM, $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,number_format($WasteRateRM, $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,number_format($WastePerBilletRM, $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"", $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"", $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,"", $regularrightFormat);
		$ColumnNum += 1;
	}
	
	$worksheet->write($RowNum,$ColumnNum,number_format($TotalCosts, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	
	
	$RowNum += 1;
	$ColumnNum = 0;	
}




function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 

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();

?>