<?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']);

$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, 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);
}


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

//Sending HTTP headers
$workbook->send('Exp_LogisticShipping.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of of Shipping');
$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'  ));    
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));    
$regularrightFormat1Num =& $workbook->addFormat(array('size'=>8,
  '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
  ));    
  
$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);_(* "-"??_);_(@_)');
$regularrightFormat1Num->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1totalFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

	
//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);


if ($fromproject == '')  $fromproject = "NULL";
else $fromproject = "'".$fromproject."'";

if ($toproject == '')  $toproject = "NULL";
else $toproject = "'".$toproject."'";

if ($fromclient == '')  $fromclient = "NULL";
else $fromclient = "'".$fromclient."'";

if ($toclient == '')  $toclient = "NULL";
else $toclient = "'".$toclient."'";

$fromdate 	= explodeword($fromdate);
$todate 	= explodeword($todate);

$RowNum    = 5;
$ColumnNum = 0;	   

	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,'FTL Reference', $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"20", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"40", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"BL #", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Ship Co", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Vessel", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"ETA", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Terminal", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 6);
	$worksheet->write($RowNum,$ColumnNum,"ROT #", $boldleftFormat);
	$ColumnNum += 1;

	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"ROT Date", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Vessel Berthed", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Ship Co DN Request", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Ship Co Rating", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Ship Co DN Paid", $boldleftFormat);
	$ColumnNum += 1;

	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Ship Co Released", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
	$worksheet->write($RowNum,$ColumnNum,"Remark", $boldleftFormat);
	$ColumnNum += 1;
	
	
$ColumnNum = 0;
$RowNum += 1;

$query ="select Project_Code, LO_twenty, LO_Forty, Dealer_Code, Team_Code, LO_BLNumber, Ship_Code, LO_VesselName,date_format(LO_ETADate,'%d-%m-%Y') as ETADate, Terminal_Code,
		LO_ROTNumber, date_format(LO_ROTDate,'%d-%m-%Y') as ROTDate,
		date_format(LO_ShippingAdditionalDate,'%d-%m-%Y') as  ShippingAdditionalDate,
		date_format(LO_ShippingRatePerDate,'%d-%m-%Y') as  ShippingRatePerDate,
		date_format(LO_ShippingPaidDate,'%d-%m-%Y') as ShippingPaidDate,
		date_format(LO_ShipCoReleaseDate,'%d-%m-%Y') as ShipCoReleaseDate,LO_Remark
		from Logistic_operations
		left join logistic_dealer on Dealer_Id = LO_DealerId
		left join logistic_team on Team_Id = LO_TeamId
		left join logistic_terminal on Terminal_Id = LO_TerminalID
		left join logistic_shipco on Ship_Id = LO_ShipCoId
		where date_format(LO_CreationDate, '%Y-%m-%d') between '$fromdate' and '$todate'
		and Project_Code between ifnull($fromproject,Project_Code) and ifnull($toproject,Project_Code)
		and Client_code between ifnull($fromclient,Client_code) and ifnull($toclient,Client_code)
		and if('$opStatus'='',1=1, LO_Status = '$opStatus')
		order by LO_CreationDate, Project_Code, Client_code ";	
	 	
$result = mysql_query($query, $connection);

while ($row = mysql_fetch_array($result)){
	extract($row);

	$worksheet->write($RowNum, $ColumnNum, $Project_Code, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LO_twenty, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LO_Forty, $regularleftFormat1);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $LO_BLNumber, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Ship_Code, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LO_VesselName, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ETADate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Terminal_Code, $regularleftFormat1);
	$ColumnNum += 1;	
	$worksheet->write($RowNum, $ColumnNum, $LO_ROTNumber, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ROTDate, $regularleftFormat1);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ShippingAdditionalDate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ShippingRatePerDate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ShippingPaidDate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ShipCoReleaseDate, $regularleftFormat1);
	$ColumnNum += 1;
	 
	$worksheet->write($RowNum, $ColumnNum, $LO_Remark, $regularleftFormat1);
	$ColumnNum += 1;
	
	$ColumnNum = 0;
	$RowNum += 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;
}




$workbook->close();


?>