<?php
include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.php');

session_start();
if($_SESSION['dbName']==""){
header("location:../index.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($_SESSION['dbName'],$connection) or die("Could not find DB");

				
$querysetup= " select COMPANY_NAME, DecimalBase, DecimalBase1, DecimalBase2, BASE1, BASE2, report_color1, report_color2, grid_color1, grid_color2, ".
             " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}
	//User Access
$querylog=" select ifNull(login_belongstogrp,0) As FI_BelongsToGrp from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$CostProj = $_GET['CostProj'];

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);

$ReportTitle = 'Journal Listing';



//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Exp_JvListing.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Journal');
$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'  )); 
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ));  
  
$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
  ));      
  
$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
  ));   

$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);_(* "-"??_);_(@_)');
   	
$worksheet->setColumn (0, 0, 20); 
$worksheet->setColumn (1, 1, 40); 
$worksheet->setColumn (2, 200, 12); 
//$worksheet->setRow (3, 100);

//Display Report Header
$worksheet->write(1,0,$COMPANY_NAME, $TitleFormat);
$worksheet->write(1,2,$ReportTitle, $TitleFormat);
               
$worksheet->write(1,6,"Printed Date:", $boldleftFormat);
$worksheet->write(1,7,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,6,"Time:", $boldleftFormat);		
$worksheet->write(2,7,$dt[1]." ".$dt[2], $regularleftFormat);
				
$dispfromdate = $fromdate;
	$disptodate = $todate;
	
	if ($fromref=='')
		$fromref = 'null';
	if ($toref=='')
		$toref = 'null';
	if ($fromfolio=='')
		$fromfolio = 'null';
	if ($tofolio=='')
		$tofolio = 'null';
		
	 if (!empty($fromdate))
	   $fromdate = explodeword($fromdate);
	if (!empty($todate))
	   $todate = explodeword($todate);  

	if (empty($fromdate))   
	   $fromdate = 'null';
	else
	   $fromdate = "'".$fromdate."'"; 
	   
	if (empty($todate))   
	   $todate = 'null';
	else
	   $todate = "'".$todate."'";	
	   
	   $jvtypelen = 0;
	if (!empty($jvtype))
	   $jvtypelen = strlen($jvtype);
	   
	if (empty($jvtype))
	   $jvtype = 'null'; 
	
$TotalDebit_Amount = 0;
$TotalCredit_Amount = 0;
$TotalDebit_Amount1 = 0;
$TotalCredit_Amount1 = 0;
$TotalDebit_Amount2 = 0;
$TotalCredit_Amount2 = 0;	
				
$SelectedOptions = "Filter by: \n";
if ($dispfromdate != '')  $SelectedOptions .= "-> Date From: ". $dispfromdate . "  To: ". $disptodate ."\n";	

$query = "  select Date_Format(journal.Journal_date,'%d/%m/%Y'), Journal_refgovernment, journaldt.Ledger_number, Journaldt_desc, journaldt.Currency_code, journaldt_dbcd,
		     Journaldt_desc1,
			if (journaldt_dbcd = 'D' ,round(Journaldt_amount,".$DecimalBase."),0) As Debit_Amount,
			if (journaldt_dbcd = 'C' ,round(Journaldt_amount,".$DecimalBase."),0) As Credit_Amount,
			if (journaldt_dbcd = 'D' ,round(Journaldt_amount1,".$DecimalBase1."),0) As Debit_Amount1,
			if (journaldt_dbcd = 'C' ,round(Journaldt_amount1,".$DecimalBase1."),0) As Credit_Amount1,
			if (journaldt_dbcd = 'D' ,round(Journaldt_amount2,".$DecimalBase2."),0) As Debit_Amount2,
			if (journaldt_dbcd = 'C' ,round(Journaldt_amount2,".$DecimalBase2."),0) As Credit_Amount2,
			Journal_folio, Journal_type, Ledger_name,  concat(journal.Project_code,'-',Project_Name), concat(journal.Costcent_Code,'-',Costcent_name)
			from journal
			inner join journaldt on journaldt.journal_id=journal.journal_id
			inner join ledger on ledger.ledger_number=journaldt.ledger_number
			left join project on project.project_code = journal.project_code 
			left join costcent on costcent.Costcent_code = journal.Costcent_code ";
		
$query .= " where ( ($option = 2 and  journal_refgovernment between ifnull($fromref,journal_refgovernment) and ifnull($toref,journal_refgovernment)) or
			 ($option = 3 and Journal_folio between ifnull($fromfolio,Journal_folio) and ifnull($tofolio,Journal_folio)) or
			 ($option = 1 and date_format(journal.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(journal.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(journal.Journal_date,'%Y-%m-%d')) ))
			 and (($jvtypelen > 0 and Journal.Journal_type in ($jvtype)) or ($jvtypelen = 0 and 1 = 1))";
			 
			
//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 3);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  

$RowNum    = 10;
$ColumnNum = 0;


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

if ($varType==1 ){
	$worksheet->write($RowNum,$ColumnNum,"Type", $boldcenterFormat);
	$ColumnNum += 1;
}

	$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldcenterFormat);
	$ColumnNum += 1;

if ($varFolio==1 ){
	$worksheet->write($RowNum,$ColumnNum,"Folio", $boldcenterFormat);
	$ColumnNum += 1;
}

	$worksheet->write($RowNum,$ColumnNum,"Ledger No./Description", $boldcenterFormat);
	$ColumnNum += 2;
	$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
	$ColumnNum += 1;

if ($varCurrency ==1 ){
	$worksheet->write($RowNum,$ColumnNum,"Debit", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Credit", $boldcenterFormat);
	$ColumnNum += 1;
}

if ($varNGN ==1 ){
	$worksheet->write($RowNum,$ColumnNum,"Debit $BASE1", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Credit $BASE1", $boldcenterFormat);
	$ColumnNum += 1;
}
if ($varUSD ==1 ){
	$worksheet->write($RowNum,$ColumnNum,"Debit $BASE2", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Credit $BASE2", $boldcenterFormat);
	$ColumnNum += 1;
}
if ($CostProj ==1 ){
	$worksheet->write($RowNum,$ColumnNum,"Project", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Coscenter", $boldcenterFormat);
	$ColumnNum += 1;
}
$RowNum    = 13;
$ColumnNum = 0;

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

				 
		$worksheet->write($RowNum, $ColumnNum, $rows[0], $regularleftFormat);
		$ColumnNum += 1;
	if ($varType==1 ){	
		$worksheet->write($RowNum, $ColumnNum, $rows[14], $regularleftFormat);
		$ColumnNum += 1;
	}
		$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularcenterFormat);
		$ColumnNum += 1;
	if ($varFolio==1 ){
		$worksheet->write($RowNum, $ColumnNum, $rows[13], $regularleftFormat);
		$ColumnNum += 1;
	}
		$worksheet->write($RowNum, $ColumnNum, $rows[2], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[15], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[4], $regularleftFormat);
		$ColumnNum += 1;
	
	if ($varCurrency ==1 ){	
		$worksheet->write($RowNum, $ColumnNum, number_format($rows[7], $ListOfProductionMaskQty), $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($rows[8], $ListOfProductionMaskQty), $regularrightFormat);
		$ColumnNum += 1;
	}
		
	if ($varNGN ==1 ){	
		$worksheet->write($RowNum, $ColumnNum, number_format($rows[9], $ListOfProductionMaskQty), $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($rows[10], $ListOfProductionMaskQty), $regularrightFormat);
		$ColumnNum += 1;
	}
	
	if ($varUSD ==1 ){	
		$worksheet->write($RowNum, $ColumnNum, number_format($rows[11], $ListOfProductionMaskQty), $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, number_format($rows[12], $ListOfProductionMaskQty), $regularrightFormat);
		$ColumnNum += 1;
	}
	
	if ($CostProj==1 ){
		$worksheet->write($RowNum, $ColumnNum, $rows[16], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[17], $regularleftFormat);
		$ColumnNum += 1;
	}

	$RowNum += 1;
	$ColumnNum = 0;
	
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
	if ($varType==1 ){	
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
	}
		$worksheet->write($RowNum, $ColumnNum, '', $regularcenterFormat);
		$ColumnNum += 1;
	if ($varFolio==1 ){
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
	}
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[3]." ".$rows[6] , $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
	
	$TotalDebit_Amount += $rows[7];
	$TotalCredit_Amount += $rows[8];
	$TotalDebit_Amount1 += $rows[9];
	$TotalCredit_Amount1 += $rows[10];
	$TotalDebit_Amount2 += $rows[11];
	$TotalCredit_Amount2 += $rows[12];
	
	$RowNum += 1;
	$ColumnNum = 0;
	
}

$RowNum += 2;
$ColumnNum = 1;
 
if ($varType==1 )
	$ColumnNum += 1;
	$ColumnNum += 1;
if ($varFolio==1 )
	$ColumnNum += 1;
	$ColumnNum += 1;
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Total, $boldcenterFormat);
	$ColumnNum += 1;
	
if ($varCurrency ==1 )	
	$ColumnNum += 2;
if ($varNGN ==1 ){
	$worksheet->write($RowNum, $ColumnNum, "Debit $BASE1", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, "Credit $BASE1", $boldcenterFormat);
	$ColumnNum += 1;
}
	
if ($varUSD==1 ){
	$worksheet->write($RowNum, $ColumnNum, "Debit $BASE2", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, "Credit $BASE2", $boldcenterFormat);
	$ColumnNum += 1;
}
$RowNum += 1;
$ColumnNum = 1;	

if ($varType==1 )
	$ColumnNum += 1;
	$ColumnNum += 1;
if ($varFolio==1 )
	$ColumnNum += 1;
	$ColumnNum += 1;
	$ColumnNum += 1;
	$ColumnNum += 1;
	
if ($varCurrency ==1 )	
	$ColumnNum += 2;
if ($varNGN ==1 ){
	$worksheet->writeNumber($RowNum, $ColumnNum, $TotalDebit_Amount1, $boldrightFormatNum);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $TotalCredit_Amount1, $boldrightFormatNum);
	$ColumnNum += 1;
}
	
if ($varUSD==1 ){
	$worksheet->writeNumber($RowNum, $ColumnNum, $TotalDebit_Amount2, $boldrightFormatNum);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $TotalCredit_Amount2, $boldrightFormatNum);
	$ColumnNum += 1;
}
$RowNum += 1;
$ColumnNum = 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;
}

$workbook->close();

?>