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

$fromdate = $_GET['fromdate'];
$todate	  = $_GET['todate'];
$defcurr  = $_GET['defcurr'];

$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('uploadsexcel/test.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();


$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($defcurr != '')		  $SelectedOptions .= "-> Default Currency $defcurr \n";	

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


//Display Report Header


$RowNum    = 0;
$ColumnNum = 0;	

$query = "Select VarLine,VarColumn,VarCur,StrLine,StrColumn, sum(if(balance = '>',if(VarTotal>0,VarTotal,0),if(balance = '<',if(VarTotal<0,VarTotal,0),VarTotal))) As VarTotalLine
		from (
		select Q.Ledger_Number, ledgerexport.balance as balance, linenb As VarLine, colnb As VarColumn, ledgerexport.currency As VarCur, linenb As StrLine, colnb As StrColumn,
		SUM(if(Q.JournalDT_DBCD = 'D',if(ledgerexport.currency = '$Base1',Amount1,Amount2),(-1)*if(ledgerexport.currency = '$Base1',Amount1,Amount2))) As VarTotal
		from ledgerexport
		left join
		( Select Cst_Increment,Sum(if(Cst_Increment is null,Journaldt_amount1,CstJour.CstJour_Amount1)) As amount1,
		Sum(if(Cst_Increment is null,Journaldt_amount2,CstJour.CstJour_Amount2)) As amount2,
		sum(CstJour_amount1) As CstJourAmount1,sum(CstJour_amount2) As CstJourAmount2,
		if(Cst_Increment is null,null,cstJour.Project_Code) As Project_code,
		if(Cst_Increment is null,null,cstjour.costcent_code) AS Costcent_Code,
		Ledger_Number,Journaldt_Dbcd,Currency_Code
		From Journaldt
		Left Join CstJour on CstJour.Journaldt_ID = JournalDt.JournalDT_ID
		Where (Journal_Date between '$fromdate 00:00:00' and '$todate 23:59:59')
		Group By Ledger_Number,Journaldt_Dbcd,Currency_Code,cstJour.Project_Code,cstjour.costcent_code
		) As Q On Q.Ledger_Number like concat(ledgerexport.Ledger_Number,'%')
		AND if(ledgerexport.dbcd is null or ledgerexport.dbcd = '', 1=1, Q.Journaldt_dbcd = ledgerexport.dbcd)
		AND if(ledgerexport.project_code is null or ledgerexport.project_code = '', 1=1, ledgerexport.project_code = Q.Project_Code)
		AND if(ledgerexport.costcent_code is null or ledgerexport.costcent_code = '',true,ledgerexport.costcent_code = Q.Costcent_Code)
		AND (colnb <> 0 or colnb is not null) AND (linenb <> 0 or linenb is not null)
		Group By Q.Ledger_Number,VarLine,VarColumn,VarCur,StrLine,StrColumn
		Order by VarLine,VarColumn,VarCur,StrLine,StrColumn,Ledger_Number
		) As Q Group By VarLine,VarColumn,VarCur,StrLine,StrColumn ";
$result = mysql_query($query, $connection);
while ($row = mysql_fetch_array($result)){
	$RowNum      = $row[0];
	$ColumnNum   = getColumn ($row[1]);
	$totalAmount = $row[5];
	if ($totalAmount == '') $totalAmount = 0;
	
	$worksheet->writeNumber($RowNum,$ColumnNum, $totalAmount, $regularrightFormat);
}
$worksheet=&$workbook->addWorksheet('2');

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


function getColumn ($search){
	$alphabet =   array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
	while ($alpha = current($alphabet)) {
		if ($alpha == $search) {
			$colnbr = key($alphabet);
			break;
		}
    next($alphabet);
	}
	return $colnbr;
}
$workbook->close(); 
?>