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

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


include("config.inc.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 fi_showcostprice,FI_ItemSelling1,FI_ItemSelling2,FI_ItemSelling3,FI_ItemSelling4,FI_ItemSelling5 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);
}
 

$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, ifNull(InventoryRMaskqty,0) As InventoryRMaskqty, Base1, Base2, 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 ".
          " 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);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Grn,0) As Module_Grn 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]);

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

//Sending HTTP headers
$workbook->send('Exp_InventoryPerWarehouse.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Inventory Per Warehouse');
$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'  ));  
  
$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
  ));    
  

$summaryFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ,
  'bold'=>1
  ));   

$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);_(* "-"??_);_(@_)');

if ($cur == 'currency1'){
   $cur='null';  
   $displaycur = '';
}   
else {
   $displaycur = $cur;
   $cur="'".$cur."'";
}  


$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."\n";
if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."\n";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."\n";
if ($fromcategory != '')  $SelectedOptions .= "-> Category From: ". $fromcategory . "  To: ". $tocategory ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "  To: ". $towarehouse . "\n";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier . "<br>";


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


$fromcur       = GetSQLValueString($fromcur, 'text');
$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromproject   = GetSQLValueString($fromproject, 'text');
$toproject     = GetSQLValueString($toproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$tocostcent    = GetSQLValueString($tocostcent, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromcategory  = GetSQLValueString($fromcategory, 'text');
$tocategory    = GetSQLValueString($tocategory, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$towarehouse   = GetSQLValueString($towarehouse, 'text');
$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date'); 
$fromsupplier  = GetSQLValueString($fromsupplier, 'date');   
$tosupplier    = GetSQLValueString($tosupplier, 'date');     
  
 

if (empty($prtcost))      			$prtcost = 0;
if (empty($showgroup))    			$showgroup = 0;
if (empty($showsubgroup)) 			$showsubgroup = 0;
if (empty($showcategory))	 		$showcategory = 0;
if (empty($showwarehousename)) 		$showwarehousename = 0;
if (empty($showsupplierInv)) 		$showsupplierInv = 0;
if (empty($showunmove))   			$showunmove = 0;
if (empty($showunsold))   			$showunsold = 0;
if (empty($abvmax))   	  			$abvmax = 0;
if (empty($blwmin))   	  			$blwmin = 0;
if (empty($nonstock)) 	  			$nonstock = 0;



//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,"Inventory Per Warehouse", $TitleFormat);
               
$worksheet->write(1,8,"Printed Date:", $boldleftFormat);
$worksheet->write(1,9,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,8,"Time:", $boldleftFormat);		
$worksheet->write(2,9,$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;	



 
$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 = '';
					

$query = $query." Select CategoryCode, CategoryName, GroupCode, GroupName, SubGroupCode, SubGroupName, ItemCode, ItemDescription, ".
				" WarehouseCode, WarehouseName, ItemMaxQty, ItemMinQty, QtyExist, CurrencyCode, MeanCost, (MeanCost*QtyExist) as TotalCost, ledger_number,ledger_name ";

$query = $query." From (   ".
			    "     Select CategoryCode, CategoryName, GroupCode, GroupName, SubGroupCode, SubGroupName, ItemCode, ItemDescription, ".
				" 	  WarehouseCode, WarehouseName, ItemMaxQty, ItemMinQty, Sum(ifNull(QtyExist,0)) as QtyExist, CurrencyCode, ";

if ($fi_showcostprice == 1 && $prtcost == 1) {			
	$query=$query." ifNull(MeanCostCur(ItemCode, concat(".$todate.", ' 23:59:59'), if(upper($cur)='".$Base1."', 1,if(upper($cur)='".$Base2."',2, 1)), CurrencyCode, if(upper($cur)='".$Base1."', '',if(upper($cur)='".$Base2."','',ifnull($fromcur, ItemCurrCode) ))),0) MeanCost,  ";
} else {
	$query=$query." 0 as MeanCost, ";
}
$query = $query."	ledger_number,ledger_name	";					
$query = $query." From  ".
                " ( ".
				"   select stktransaction.item_code As ItemCode, item_description As ItemDescription, item_maxqty As ItemMaxQty, item_minqty As ItemMinQty, ".
				"   items.currency_code As ItemCurrCode, Items.Group_Code As GroupCode, Grouping.Group_Name As GroupName, ".
				"   Items.SubGroup_Code As SubGroupCode, Subgroup.Subgroup_Name As SubGroupName, ".
				"   Items.Category_Code As CategoryCode, Category.Category_Name As CategoryName, ".
				"   stktransaction.Warehouse_Code As WarehouseCode, warehouse.warehouse_name As WarehouseName, ".
				"   ifnull($fromcur, items.currency_code) As CurrencyCode, ".
				"   if(date_format(transaction_date,'%Y-%m-%d') <= ifNull(". $todate.",date_format(transaction_date,'%Y-%m-%d')),(ifNull(quantityproducein,0) + ifNull(quantityentered,0) ) - (ifNull(quantityproduceout,0) + ifNull(quantitydelivered,0)), 0)  QtyExist, ".
				"   items.Ledger_Number as ledger_number, ledger_name ";

$query = $query." from stktransaction ";
					  
$query = $query." Inner Join Items on stktransaction.item_code = items.item_code ".
				" left join grouping on items.group_code = grouping.group_code ".
				" left join subgroup on items.subgroup_code = subgroup.subgroup_code ".
				" left join category on items.category_code = category.category_code ".
				" left join warehouse on stktransaction.warehouse_code = warehouse.warehouse_code ".
				" left join suppliers on suppliers.ledger_number = items.Ledger_Number ";
					  
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
	$query = $query.$QueryRestrictPrj." on stktransaction.project_code like concat(Q4.project_code,'%') ";
								 
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
	$query = $query.$QueryRestrictCst." on stktransaction.costcent_code like concat(Q5.costcent_code,'%') ";
		

$query = $query." where stktransaction.item_code between ifNull(".$fromitem.",stktransaction.item_code) and ifNull(". $toitem.",stktransaction.item_code) ".
				" and ifNull(stktransaction.project_code,'-1') between ifNull(".$fromproject.",ifNull(stktransaction.project_code,'-1')) and ifNull(". $toproject.",ifNull(stktransaction.project_code,'-1')) ".
				" and ifNull(stktransaction.costcent_code,'-1') between ifNull(".$fromcostcent.",ifNull(stktransaction.costcent_code,'-1')) and ifNull(". $tocostcent.",ifNull(stktransaction.costcent_code,'-1')) ".
				" and ifNull(stktransaction.warehouse_code,'-1') between ifNull(". $fromwarehouse .",ifNull(stktransaction.warehouse_code,'-1')) and ifNull(". $towarehouse.",ifNull(stktransaction.warehouse_code,'-1')) ".
				" and ifNull(items.Ledger_Number,'-1') between ifNull(".$fromsupplier.",ifNull(items.Ledger_Number,'-1')) and ifNull(". $tosupplier.",ifNull(items.Ledger_Number,'-1')) ".
				" 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(". $tocategory.",ifNull(Items.Category_Code,'-1'))  ".
				" And (($nonstock = 1 and 1 = 1) or ($nonstock = 0 and ifNull(Items.Item_NonStock,0) = 0)) ".
				//" and date_format(transaction_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(transaction_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(transaction_date,'%Y-%m-%d'))  ".
				//" And Items.Item_NonStock = 0 ".
                //" And Item_FixedAsset = 0 ".
				" and stktransaction.transaction_type IN ('GR','PI','PR','DI','DR','AI','AR','AD','PD','PDT','TR','TRT','SO','PO') ";
					  
if ($whexist > 0  && $FI_RepRestrictWarehouse == 1)
	$query = $query." and stktransaction.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_Grn == 1)
	$query = $query." And StkTransaction.Transaction_Type <> 'PI' ";
	
$query = $query."  ) As Q1  ".
				"  Group By ledger_number,CategoryCode, GroupCode, SubGroupCode, ItemCode, WarehouseCode ".
   			    "  Order by if($showsupplierInv=1,ledger_name,1),if($showcategory=1,CategoryCode,1), if($showgroup=1,GroupCode,1), if($showsubgroup=1,SubGroupCode,1), if($orderby=1, ItemCode, ItemDescription), WarehouseCode  ".
				" ) As Q2  ";


$query = $query." Where (($zeroprint=1 and QtyExist = 0) or ($zeroprint=2 and QtyExist != 0) or ($zeroprint=3 and 1 = 1) ) " .
				" And (($abvmax=1 and QtyExist > ItemMaxQty) or ($blwmin=1 and QtyExist < ItemMinQty) or ($abvmax=0 and $blwmin=0 and 1=1)) ";
				
if ($showunmove == 1) {
	$query = $query." and not exists ( select 1 from  stktransaction as stk ".
					"                  where stk.item_code = Q2.ItemCode ".
					"                  and date_format(stk.transaction_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(stk.transaction_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(stk.transaction_date,'%Y-%m-%d')) ".
					"                ) ";
	
}
else if ($showunsold == 1) {
	$query = $query." and not exists ( select 1 from  stktransaction as stk ".
					"                  where stk.item_code = Q2.ItemCode ".
					"				   and stk.transaction_type in ('SI','DI') ".
					"                  and date_format(stk.transaction_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(stk.transaction_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(stk.transaction_date,'%Y-%m-%d')) ".
					"                ) ";
	
}

$query = $query."  Order by if($showsupplierInv=1,ledger_name,1),if($showcategory=1,CategoryCode,1), if($showgroup=1,GroupCode,1), if($showsubgroup=1,SubGroupCode,1), if($orderby=1, ItemCode, ItemDescription), WarehouseCode  ";
	
	
				
//echo $query; 

$ArrayTotalByReport   = Array();
$ArrayWarehouse       = Array();
$ArrayItems	          = Array();
$ArrayRecordData      = Array();


				
$result=mysql_query($query, $connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){

	//Check If Item Exists In Array
	$ItemFound = 0;
	$count = count ($ArrayItems); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayItems[$j] == $rows[6])
			$ItemFound = 1;
	}
				
	if ($ItemFound == 0) 
		$ArrayItems[$count] = $rows[6];
		
    //Check If Warehouse Exists In Array
	$WarehouseFound = 0;
	$count = count ($ArrayWarehouse); 
	for ($j=0; $j<$count; $j++){
		if ($ArrayWarehouse[$j] == $rows[8])
			$WarehouseFound = 1;
	}
				
	if ($WarehouseFound == 0) 
		$ArrayWarehouse[$count] = $rows[8];
		
	$ArrayRecordData[$rows[6]]['CategoryCode']  = $rows[0];	
	$ArrayRecordData[$rows[6]]['CategoryName']  = $rows[1];
	$ArrayRecordData[$rows[6]]['GroupCode']     = $rows[2];
	$ArrayRecordData[$rows[6]]['GroupName']     = $rows[3];
	$ArrayRecordData[$rows[6]]['SubGroupCode']  = $rows[4];
	$ArrayRecordData[$rows[6]]['SubGroupName']  = $rows[5];
	$ArrayRecordData[$rows[6]]['ItemCode']  = $rows[6];
	$ArrayRecordData[$rows[6]]['ItemDesc']  = $rows[7];
	$ArrayRecordData[$rows[6]]['Currency']  = $rows[13];
	$ArrayRecordData[$rows[6]]['Cost']      = $rows[14];
	$ArrayRecordData[$rows[6]]['SupplierCode']   = $rows[16];
	$ArrayRecordData[$rows[6]]['SupplierName']   = $rows[17];
	
	$ArrayRecordData[$rows[6]][$rows[8]]['QtyExist']  = $rows[12];
	$ArrayRecordData[$rows[6]]['TotalQty']  = $ArrayRecordData[$rows[6]]['TotalQty'] + $rows[12];
	$ArrayRecordData[$rows[6]]['TotalCost'] = $ArrayRecordData[$rows[6]]['Cost'] * $ArrayRecordData[$rows[6]]['TotalQty'];
	
}

//sorting data in array
//array_multisort($ArrayItems);
array_multisort($ArrayWarehouse);

// Displaying Header
if ($showsupplierInv == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Supplier Code", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Supplier Name", $boldleftFormat);
	$ColumnNum += 1;
}
if ($showcategory == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Category Code", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Category Name", $boldleftFormat);
	$ColumnNum += 1;
}	


if ($showgroup == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Group Code", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Group Name", $boldleftFormat);
	$ColumnNum += 1;
}	
		
if ($showsubgroup == 1) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"SubGroup Code", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"SubGroup Name", $boldleftFormat);
	$ColumnNum += 1;
}

$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 1;
	
		 
$count = count ($ArrayWarehouse); 
for ($j=0; $j<$count; $j++){ 
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	if ($showwarehousename == 1){
		$name = "select Warehouse_name from warehouse where warehouse_code = '".$ArrayWarehouse[$j]."'";
		$res = mysql_query($name,$connection);
		$rw = mysql_fetch_row($res);
		$worksheet->write($RowNum,$ColumnNum,$rw[0], $boldcenterFormat);
	} else
	$worksheet->write($RowNum,$ColumnNum,$ArrayWarehouse[$j], $boldcenterFormat);
	$ColumnNum += 1;
}	

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

	
if ($prtcost == 1 && $fi_showcostprice == 1){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Mean Cost", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Total Cost", $boldcenterFormat);
	$ColumnNum += 1;
}


$RowNum    = 12;
$ColumnNum = 0;

// Displaying Data
$CountItems = count($ArrayItems); 
for ($i=0; $i<$CountItems; $i++){ 
	
	if ($showsupplierInv == 1) {
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['SupplierCode'], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['SupplierName'], $regularleftFormat);
		$ColumnNum += 1;
	}
	
	if ($showcategory == 1) {
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['CategoryCode'], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['CategoryName'], $regularleftFormat);
		$ColumnNum += 1;
	}	
		
	if ($showgroup == 1){
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['GroupCode'], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['GroupName'], $regularleftFormat);
		$ColumnNum += 1;
	}	

	if ($showsubgroup == 1) { 
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['SubGroupCode'], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['SubGroupName'], $regularleftFormat);
		$ColumnNum += 1;
	}	

	
	$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['ItemCode'], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['ItemDesc'], $regularleftFormat);
	$ColumnNum += 1;	

	$CountWarehouse = count($ArrayWarehouse); 
	for ($j=0; $j < $CountWarehouse; $j++){
		$ArrayTotalByReport[$ArrayWarehouse[$j]]['QtyExist'] = $ArrayTotalByReport[$ArrayWarehouse[$j]]['QtyExist'] + $ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['QtyExist'];
		
		$worksheet->write($RowNum, $ColumnNum, number_format($ArrayRecordData[$ArrayItems[$i]][$ArrayWarehouse[$j]]['QtyExist'], $InventoryRMaskqty), $regularrightFormat);
		$ColumnNum += 1;
	}
	
	$ArrayTotalByReport['TotalQty']  = $ArrayTotalByReport['TotalQty']  + $ArrayRecordData[$ArrayItems[$i]]['TotalQty'];
	$ArrayTotalByReport['TotalCost'] = $ArrayTotalByReport['TotalCost'] + $ArrayRecordData[$ArrayItems[$i]]['TotalCost'];
	
	$worksheet->write($RowNum, $ColumnNum, number_format($ArrayRecordData[$ArrayItems[$i]]['TotalQty'], $InventoryRMaskqty), $regularrightFormat);
	$ColumnNum += 1;
	
	if ($prtcost == 1 && $fi_showcostprice == 1)	{
		$worksheet->write($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['Currency'], $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['Cost'], $regularrightFormatNum);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayRecordData[$ArrayItems[$i]]['TotalCost'], $regularrightFormatNum);
		$ColumnNum += 1;
	}	
		

	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
}

// Display Totals By Report	
//Increment the Rows Counter By 1 and Reset the Columns Counter
$RowNum += 1;
$ColumnNum = 0;

if ($showsupplierInv == 1)
	$ColumnNum += 2;
if ($showcategory == 1)
	$ColumnNum += 2;
if ($showgroup == 1)
	$ColumnNum += 2;
if ($showsubgroup == 1)	
	$ColumnNum += 2;	
	


$worksheet->write($RowNum, $ColumnNum, "Totals", $boldleftFormat);
$ColumnNum += 2;

$CountWarehouse = count($ArrayWarehouse); 
for ($j=0; $j < $CountWarehouse; $j++){
	$worksheet->write($RowNum, $ColumnNum, number_format($ArrayTotalByReport[$ArrayWarehouse[$j]]['QtyExist'], $InventoryRMaskqty), $boldrightFormat);
	$ColumnNum += 1;
}

$worksheet->write($RowNum, $ColumnNum, number_format($ArrayTotalByReport['TotalQty'], $InventoryRMaskqty), $boldrightFormat);
$ColumnNum += 1;

	
if ($prtcost == 1 && $fi_showcostprice == 1)	{
	$ColumnNum += 2;
	$worksheet->writeNumber($RowNum, $ColumnNum, $ArrayTotalByReport['TotalCost'], $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;
}

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

?>