<?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_LogisticTransport.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of of Transport');
$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."'";

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

$RowNum    = 5;
$ColumnNum = 0;	   

	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,'Reference Number', $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"Status", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Time/Date Out of Port", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Transporter", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Pick up Point", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Destin", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Arrival at Client", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Date off Load", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Date Dropped Empty", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 6);
	$worksheet->write($RowNum,$ColumnNum,"S/N", $boldleftFormat);
	$ColumnNum += 1;

	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Date/Time TDO Given(TDP)", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Container #", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Container Size", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Truck #", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Days to Reach Client", $boldleftFormat);
	$ColumnNum += 1;

	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Days to Offload at Client", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Days to Drop Empty", $boldleftFormat);
	$ColumnNum += 1;

$ColumnNum = 0;
$RowNum += 1;
		
$temp = "**";
$tempstat = "**";

$query ="select LT_ProjectCode, LTdt_Status, date_format(LTdt_OutofPortDate,'%d-%m-%Y %h:%i %p') as OutofPortDate,  Transporter_Code, PickUp_Code, Destination_Code,
		date_format(LTdt_ArrivalatClientDate,'%d-%m-%Y %h:%i %p') as ArrivalatClientDate, date_format(LTdt_OffLoadDate,'%d-%m-%Y %h:%i %p') as OffLoadDate,
		date_format(LTdt_DroppedEmptyDate,'%d-%m-%Y %h:%i %p') as DroppedEmptyDate, date_format(LTdt_TdoDate,'%d-%m-%Y %h:%i %p') as TdoDate,
		LTdt_ContainerNo, LTdt_ContainerSize, LTdt_TruckNo, LTdt_DaysToReachClient, LTdt_DaysToOffload, LTdt_DaysToDropEmpty
		from logistic_transportdt
		inner join logistic_transport on logistic_transport.lt_id = logistic_transportdt.lt_id
		left join logistic_transporter on Transporter_Id = LTdt_TransporterId
		left join logistic_pickup on PickUp_Id = LTdt_PickUpPointId
		left join logistic_destination on Destination_Id = LTdt_ContainerId
		where date_format(LT_Date, '%Y-%m-%d') between '$fromdate' and '$todate'
		and LT_ProjectCode between ifnull($fromproject,LT_ProjectCode) and ifnull($toproject,LT_ProjectCode)
		order by LT_ProjectCode, LTdt_Status  ";	
$result = mysql_query($query, $connection);
while ($row = mysql_fetch_array($result)){
	extract($row);
	
	if ($temp != $LT_ProjectCode){
		$worksheet->write($RowNum, $ColumnNum, $LT_ProjectCode, $boldleftFormat);
		$ColumnNum = 0;
		$RowNum += 1;	
		$tempstat = "**";
	}	
	
	if ($temp != $LT_ProjectCode){
		$worksheet->write($RowNum, $ColumnNum, $LT_ProjectCode, $regularleftFormat1);
		$ColumnNum += 1;
	} else {
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat1);
		$ColumnNum += 1;
	}
		
	
	if ($tempstat != $LTdt_Status){
		$worksheet->write($RowNum, $ColumnNum, $LTdt_Status, $regularleftFormat1);
		$ColumnNum += 1;
	} else {
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat1);
		$ColumnNum += 1;
	}
	
	
	$worksheet->write($RowNum, $ColumnNum, $OutofPortDate, $regularrightFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Transporter_Code, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $PickUp_Code, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Destination_Code, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ArrivalatClientDate, $regularrightFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $OffLoadDate, $regularrightFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $DroppedEmptyDate, $regularrightFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $TdoDate, $regularrightFormat1);
	$ColumnNum += 1;	
	$worksheet->write($RowNum, $ColumnNum, $LTdt_ContainerNo, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LTdt_ContainerSize, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LTdt_TruckNo, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LTdt_DaysToReachClient, $regularrightFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LTdt_DaysToOffload, $regularrightFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $LTdt_DaysToDropEmpty, $regularrightFormat1);
	$ColumnNum += 1;
		
	$temp 	  = $LT_ProjectCode;
	$tempstat = $LTdt_Status;
	
	$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();


?>