<?php

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

include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.php');
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");


$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, 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);
}


$query_login="select login_belongstogrp, ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlogin=mysql_query($query_login,$connection) or die(mysql_error());

$row_login=mysql_fetch_row($resultlogin);
$belongstogroup=$row_login[0];
if (empty($belongstogroup)) $belongstogroup = 0;

$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('Rep_ItemsExcel.xls');
 
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List Of Items');
$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
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));  
$summaryFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ,
  'bold'=>1
  ));   

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

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,"List of Items", $TitleFormat);
               
$worksheet->write(1,5,"Printed Date:", $boldleftFormat);
$worksheet->write(1,6,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,5,"Time:", $boldleftFormat);		
$worksheet->write(2,6,$dt[1]." ".$dt[2], $regularleftFormat);
  

  
$RowNum    = 4;
$ColumnNum = 0;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 25);
$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Barcode 1", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Barcode 2", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Barcode 3", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Group Code", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Name", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"SubGroup Code", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Name", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Category Code", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Name", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Item Unit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Item Vat", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Sales Unit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Purchase Unit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Supplier Code", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Name", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Project", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"CostCenter", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Status", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Item Production", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Item Pack", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Item NonStock", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Currency", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Selling Price 1", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Selling Price 2", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Selling Price 3", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Selling Price 4", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Selling Price 5", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Selling Price 6", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Cost Method", $boldcenterFormat);
$ColumnNum += 1;

if($FI_ShowCostPrice == 0)  {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Cost Price", $boldcenterFormat);
	$ColumnNum += 1;
}
	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Min Qty", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Max Qty", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Qty Exists", $boldcenterFormat);

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

$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Gross weight", $boldcenterFormat);	


$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Net weight", $boldcenterFormat);	

	
$query_clients=" Select Items.Item_Code as ItemCode, Items.Item_Description as ItemDescription, Item_Barcode, Item_Barcode2, Item_Barcode3, 
				 Items.Group_Code, Grouping.Group_Name, Items.SubGroup_Code, SubGroup.SubGroup_Name, Items.Ledger_Number as SupplierCode, Suppliers.Ledger_Name as SupplierName,  
				 Item_Unit, Item_UnitSalesDesc, Item_UnitPurchaseDesc, Item_MinQty, Item_MaxQty, Item_Weight, if(ifnull(Item_Active,0)=1, 'Active', 'InActive') as ItemStatus, 
				 Items.Currency_Code as CurrencyCode, Item_SellingPrice1, Item_SellingPrice2, Item_SellingPrice3, Item_SellingPrice4, Item_SellingPrice5, 
				 Item_SellingPrice6, Items.Project_Code as ProjectCode, Items.Costcent_code as CostcentCode, Items.Category_Code, Category.Category_Name, 
				 if(ifnull(Item_CostMethod,0)=0, 'Average', if(ifnull(Item_CostMethod,0)=1, 'Fifo', 'Lifo')) as Item_CostMethod, Item_LastCostPrice,item_volume,item_weight,Item_NetWeight ,
				 item_production , item_pack , item_nonstock , item_vat
				 From Items 
				 Left Join Grouping on Grouping.Group_Code = Items.Group_Code
				 Left Join SubGroup on SubGroup.SubGroup_Code = Items.SubGroup_Code
				 Left Join Category on Category.Category_Code = Items.Category_Code
				 Left Join Suppliers on Suppliers.Ledger_Number = Items.Ledger_Number
				 Order By Items.Item_Code ";
	
//echo$query_clients;
	
$result_clients=mysql_query($query_clients,$connection) or die(mysql_error());
$totalrecords=mysql_num_rows($result_clients);

$RowNum    = 5;
$ColumnNum = 0;

while($row=mysql_fetch_array($result_clients)){
	extract($row);
	
	if ($item_production==0)			
		$itemproduction='No';
	else $itemproduction='Yes';
	
	if ($item_pack==0)
		$itempack='No';
	else $itempack='Yes';
	
	if ($item_nonstock==0)
		$itemnonstock='No';
	else $itemnonstock='Yes';
	
	
	$queryQty="select qtyexists('$ItemCode','',now(),null,'','','')";
	$resultQty=mysql_query($queryQty,$connection) or die(mysql_error());	
	$rowQty=mysql_fetch_row($resultQty);
	$QtyExists = $rowQty[0];
	
	$worksheet->write($RowNum, $ColumnNum, $ItemCode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ItemDescription, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_Barcode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_Barcode2, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_Barcode3, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $Group_Code, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Group_Name, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $SubGroup_Code, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $SubGroup_Name, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $Category_Code, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $Category_Name, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_Unit, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $item_vat, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_UnitSalesDesc, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_UnitPurchaseDesc, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $SupplierCode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $SupplierName, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ProjectCode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $CostcentCode, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ItemStatus, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $itemproduction, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $itempack, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $itemnonstock, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $CurrencyCode, $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($Item_SellingPrice1, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($Item_SellingPrice2, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($Item_SellingPrice3, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($Item_SellingPrice4, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($Item_SellingPrice5, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($Item_SellingPrice6, $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_CostMethod, $regularleftFormat);
	
	if($FI_ShowCostPrice == 0)  {
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($Item_LastCostPrice, $DecimalBase), $regularrightFormat);
	}	
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_MinQty, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_MaxQty, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $QtyExists, $regularrightFormat);

	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($item_volume, 4), $regularrightFormat);
	$ColumnNum += 1;

	$worksheet->write($RowNum, $ColumnNum, $item_weight, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_NetWeight, $regularleftFormat);
	$ColumnNum += 1;
	
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;	
}

$RowNum += 1;
$ColumnNum = 0;	
	
$worksheet->write($RowNum, $ColumnNum, "Total Records: ".$totalrecords, $regularleftFormat);
$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;
}

$workbook->close();

?>