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

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

include("config.inc.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 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);
}

$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_ItemAuction.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('ItemAuction');
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;

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

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

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

$regularleftFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel2FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel3FormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1totalFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel2totalFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel3totalFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$summaryFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
//Elie format   
  
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

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

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

if ($showmin == '') $showmin = 0; 

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($showmin == 1)		  $SelectedOptions .= "-> Show Minimum Values \n";	

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


//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,"Item Auction", $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    = 11;
$ColumnNum = 0;	


if ($showmin == 1)
$query = " select auction_reference, auction_startDate,auction_endDate, item_code, item_description,ledger_name, min(bid_price), auction_id, bid_dateTime from (
			select auction_reference, date_format(auction_startDate,'%d-%m-%Y %h:%i %p') as auction_startDate, date_format(auction_endDate,'%d-%m-%Y %h:%i %p') as auction_endDate,
			items.item_code as item_code, item_description,ledger_name, min(bid_price) as bid_price, item_auction.auction_id, bid_dateTime
			from supplierBid 
			left join suppliers on suppliers.ledger_number = supplierBid.supplier_code 
			inner join item_auction on item_auction.auction_id = supplierBid.auction_id 
			inner join items on items.item_code = item_auction.item_code
			where date_format(auction_startDate,'%Y-%m-%d %h:%i:%s') >= '$fromdate 00:00:00'
			and date_format(auction_endDate,'%Y-%m-%d %h:%i:%s') <= '$todate 23:59:59'
			group by supplierBid.supplier_code, item_auction.auction_id
			order by auction_startDate, auction_endDate, auction_reference asc, bid_price asc
			 ) as Q group by auction_id order by auction_reference asc, bid_price, bid_dateTime asc ";
			 
else	
$query = " select auction_reference, auction_startDate,auction_endDate, item_code, item_description,ledger_name, bid_price, auction_id, bid_dateTime from (
			select auction_reference, date_format(auction_startDate,'%d-%m-%Y %h:%i %p') as auction_startDate, date_format(auction_endDate,'%d-%m-%Y %h:%i %p') as auction_endDate,
			items.item_code as item_code, item_description, ledger_name, min(bid_price) as bid_price, item_auction.auction_id, bid_dateTime
			from supplierBid 
			left join suppliers on suppliers.ledger_number = supplierBid.supplier_code 
			inner join item_auction on item_auction.auction_id = supplierBid.auction_id 
			inner join items on items.item_code = item_auction.item_code
			where date_format(auction_startDate,'%Y-%m-%d %h:%i:%s') >= '$fromdate 00:00:00'
			and date_format(auction_endDate,'%Y-%m-%d %h:%i:%s') <= '$todate 23:59:59'
			group by supplierBid.supplier_code, item_auction.auction_id
			order by auction_startDate, auction_endDate, auction_reference
			 ) as Q order by auction_reference asc, bid_price, bid_dateTime asc ";
			
$queryC = " select max(countSup) from (
			select count(auction_reference) as countSup from ( ".$query." ) as Q group by auction_reference
			) as QQ; ";		//echo $queryC;	
			
			
$resC = mysql_query($queryC, $connection);
$rwC = mysql_fetch_row($resC);
$countSup = $rwC[0];
 
if($countSup == '') $countSup = 0;


$worksheet->write($RowNum,$ColumnNum,"S/N", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Auction Ref", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"From Date", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"To Date", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Item", $boldcenterFormat);
$ColumnNum += 1;

for($i=1; $i<=$countSup; $i++){
	$worksheet->write($RowNum,$ColumnNum,"Sup $i", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Price $i", $boldcenterFormat);
	$ColumnNum += 1;
}



$RowNum ++;
$ColumnNum = 0;
$res = mysql_query($query, $connection);
$col = 1;
$temRef = '';

while($rw=mysql_fetch_row($res)){
	
	if($temRef != $rw[0]){	
	
		if($col != 1) {
			$RowNum++;
			$ColumnNum = 0;
		}
		
		$worksheet->write($RowNum,$ColumnNum,$col, $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,$rw[0], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,$rw[1], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,$rw[2], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum,$ColumnNum,$rw[3]."-".$rw[4], $regularleftFormat);
		$ColumnNum += 1;
		
		$col++;
	}
	
	$worksheet->write($RowNum,$ColumnNum,$rw[5], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rw[6] , $regularleftFormat);
	$ColumnNum += 1;
	
	$temRef = $rw[0];
}
 
	


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