<?php

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

include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.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");

//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);

//Sending HTTP headers
$workbook->send('Exp_ListofContainers.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet($ReportName);
$worksheet->setInputEncoding('windows-1256'); 
$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'  ));  
  
$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
  ));    
  

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


if ($voyage_type != ''){
	$tempvtype = explode(',', $voyage_type);
	$voyage_type = '-1';
	foreach($tempvtype as $tempv)	
		$voyage_type .= ",'".$tempv."'";
}

if ($voyage_year!='' && $voyage_number != ''){
	$queryheader = "select vessel_id, BOL_type, manifest_auto, voyage_number from manifest where voyage_year = '$voyage_year' and voyage_number = '$voyage_number' "; 
		if ($manifest_auto!='') 		$queryheader .= " and manifest_auto = '$manifest_auto' ";
		if ($billlading!='') 			$queryheader .= " and BOL_identification = '$billlading' ";
		if ($voyage_destination!='') 	$queryheader .= " and voyage_destination = '$voyage_destination' ";
		if ($bol_number!='') 			$queryheader .= " and voyage_BOLnumber = '$bol_number' ";
		if ($voyage_type!='') 			$queryheader .= " and BOL_type in ($voyage_type) ";
		if ($voyage_pagenumber!='') 	$queryheader .= " and BOL_pagenumber = '$voyage_pagenumber' ";
		if ($portofloading!='') 		$queryheader .= " and Portofloading = '$portofloading' ";
		if ($portofdischarge!= '') 		$queryheader .= " and PortofDischarge='$portofdischarge' ";
		if ($localagent_code!='') 		$queryheader .= " and localagent_code = '$localagent_code' ";//echo $queryheader;
		if ($withempty == 1)			$queryheader .= " and manifest_emptyContainer = '$withempty' ";
	$queryheader .= " order by vessel_id, BOL_type ";	
		if ($vOrderby == '1') 			$queryheader .= " , localagent_code ";
} else 
	$queryheader = "select null, null, '$manifest_auto', null ";
//echo $queryheader;exit;
$tempvessel ="*";
$tempbol ="*";
$i=0;
$resheader = mysql_query($queryheader, $connection);

$RowNum    = 0;
$ColumnNum = 0;
while ($rwheader = mysql_fetch_row($resheader)){
$vessel_id = $rwheader[0];
if ($rwheader[1] == 'I')
	$boltype = "Import";
else if ($rwheader[1] == 'E')
	$boltype = "Export"; 
else if ($rwheader[1] == 'S')
	$boltype = "Transhipment"; 
Else if ($rwheader[1] == 'T')
	$boltype = "FreeZone"; 
$manifest_auto = $rwheader[2];

$voynbr = $rwheader[3];



if ($tempvessel != $vessel_id && $tempbol != $boltype){
		
		if ($i>0){	
						
			$RowNum++;
			$ColumnNum = 0;
						
			$querytype = "select distinct substr(type_id, 1,2)
						from container
						order by type_id";
			$i = 0;			

			$restype = mysql_query($querytype, $connection);
			 while ($rwtype = mysql_fetch_array($restype)){
				if ($sumContainer['F'][$rwtype[0]] != 0){
					
					$sumWeight[$cntr_type1][$rwtype[0]] = number_format($sumWeight[$cntr_type1][$rwtype[0]],3);	
					if ($i == 0) {
						$RowNum++;
						$ColumnNum = 1;
						$worksheet->write($RowNum, $ColumnNum, "FULL:", $regularleftFormat);
						$ColumnNum += 1;
					}
					if ($i > 0){
						$RowNum++;
						$ColumnNum = 2;
					}
					$worksheet->write($RowNum, $ColumnNum,$sumContainer['F'][$rwtype[0]]." X ".$rwtype[0], $regularleftFormat);
					$ColumnNum += 2;
					$worksheet->write($RowNum, $ColumnNum, $sumWeight['F'][$rwtype[0]]." Kgs", $regularleftFormat);
					  
					$i++;
				}

			}

			$i = 0;
			$restype = mysql_query($querytype, $connection);
			 while ($rwtype = mysql_fetch_array($restype)){
				if ($sumContainer['E'][$rwtype[0]] != 0){
					
					$sumWeight[$cntr_type1][$rwtype[0]] = number_format($sumWeight[$cntr_type1][$rwtype[0]],3);	
					if ($i == 0){
						$RowNum++;
						$ColumnNum = 1;
						$worksheet->write($RowNum, $ColumnNum, "EMPTY:", $regularleftFormat);
						$ColumnNum += 1;
					}
					if ($i > 0){
						$RowNum++;
						$ColumnNum = 2;
					}
					$worksheet->write($RowNum, $ColumnNum,$sumContainer['E'][$rwtype[0]]." X ".$rwtype[0], $regularleftFormat);
					$ColumnNum += 2;
					$worksheet->write($RowNum, $ColumnNum, $sumWeight['E'][$rwtype[0]]." Kgs", $regularleftFormat);
					  
					$i++;
				}

			}	
			$RowNum++;

			$Sumtotal['weight'] = number_format($Sumtotal['weight'],3);		
			$RowNum++;		
			$ColumnNum = 2;
			$worksheet->write($RowNum, $ColumnNum,$Sumtotal['container']." Containers", $regularleftFormat);
			$ColumnNum += 2;
			$worksheet->write($RowNum, $ColumnNum,$Sumtotal['weight']." Kgs", $regularleftFormat);
			$ColumnNum += 2;
			
			unset($sumContainer);
			unset($sumWeight);
			unset($Sumtotal);
			$i=0;
		} 
		if ($rwheader[1] =='E')  { $reportname="LOAD LIST" ;} else {$reportname="DISCHARGE LIST";} 
		
		$worksheet->write($RowNum,$ColumnNum,($voynbr != ''?"VOY NO. $voynbr  $vessel_id":''), $TitleFormat);
		$ColumnNum += 3;
		$worksheet->write($RowNum,$ColumnNum,$reportname, $TitleFormat);
		$ColumnNum += 3;
		$worksheet->write($RowNum,$ColumnNum,$boltype, $TitleFormat);
		
		$RowNum++;
		$ColumnNum = 0;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Container", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Size/Type", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"F/E", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"POL", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Dest.", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"GWT(kg) \n (TARE)", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Line", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"EX M/V", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Voy. No.", $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
		$worksheet->write($RowNum,$ColumnNum,"Remark", $boldleftFormat);
		$ColumnNum += 1;
	
	} 
	
	
$query = "select container, type_id, manifestdt.voyage_destination,ifnull(replace(Container_Weight,',',''),0), line_operator, manifestdt.vessel_id, 
		date_format(date,'%d/%m/%Y'),bctc_remark,Portofloading, manifestdt.BOL_type, localagent_code, agent.agent1, if(ifnull(manifest_emptyContainer,0) = '1', 'E','F'), substr(type_id, 1,2) as cntr_type,
		manifestdt.voyage_number, Container_Weight, if(substr(type_id, 1,2)=20, 2200, if(substr(type_id, 1,2)>=40, 4000,'')) as tare, agent2.agent1
		from manifestdt
		inner join manifest on manifest.manifest_auto = manifestdt.manifest_manifestauto
		inner join manifestdesc on manifestdt.manifest_manifestauto=manifestdesc.manifest_manifestauto and  manifestdesc.detailsequence=manifestdt.detailsequence
		left join container on cntr_code = manifestdt.container
		left join agent on agent.code = manifest.localagent_code
		left join agent as agent2  on agent2.code = manifest.containeragent_code
		where ifnull(CTRFlag,'') != '*' ";	

if ($manifest_auto!='') 		$query .= " and manifest.manifest_auto = '$manifest_auto' ";
if ($billlading!='') 			$query .= " and manifestdt.BOL_identification = '$billlading' ";
if ($voyage_year!='') 			$query .= " and manifestdt.voyage_year = '$voyage_year' ";
if ($voyage_number!='') 		$query .= " and manifestdt.voyage_number = '$voyage_number' ";
if ($voyage_destination!='') 	$query .= " and manifestdt.voyage_destination = '$voyage_destination' ";
if ($bol_number!='') 			$query .= " and manifestdt.voyage_BOLnumber = '$bol_number' ";
if ($voyage_type!='') 			$query .= " and manifestdt.BOL_type in ($voyage_type) ";
if ($voyage_pagenumber!='') 	$query .= " and manifestdt.BOL_pagenumber = '$voyage_pagenumber' ";
if ($portofloading!='') 		$query .= " and Portofloading = '$portofloading' ";
if ($portofdischarge!= '') 		$query .= " and PortofDischarge='$portofdischarge' ";
if ($localagent_code!='') 		$query .= " and manifest.localagent_code = '$localagent_code' ";
 
if ($cntrtype == '1')			$query .= " and ifnull(manifest_emptyContainer,0) = '0' ";
else if ($cntrtype == '2')		$query .= " and ifnull(manifest_emptyContainer,0) = '1' ";

if ($vOrderby == '1') 			$query .= " order by manifest.localagent_code ";
else {
	if ($grpby == 1)			$query .= " order by Portofloading ";
	else if ($grpby == 2) 		$query .= " order by manifestdt.BOL_type ";
	else if ($grpby == 3) 		$query .= " order by localagent_code ";
	else				 		$query .= " order by container ";
}
//echo $query;exit;
$result = mysql_query($query, $connection);
$total = array();
$Sumtotal = array();

$grpbytotal['container'] = 0;
$grpbytotal['weight'] = 0;

$Sumtotal['container'] = 0;
$Sumtotal['weight'] = 0;

$temp_grpby = '**'; 
$groupby = '';
$ct = 0;


while ($rw = mysql_fetch_array($result)){
	
	if ($grpby == 1)		$groupby = $rw[8];
	else if ($grpby == 2) 	$groupby = $rw[9];
	else if ($grpby == 3) 	$groupby = $rw[10];
	
	if ($grpby != '' && ($temp_grpby != $groupby)){
	
		$grpbytotal['container'] = 0;
		$grpbytotal['weight'] = 0;
		$RowNum++;
		$ColumnNum = 0;
		$worksheet->write($RowNum, $ColumnNum, "Group By : ".$groupby, $regularleftFormat);
		$ColumnNum = 0;
	}
	if ($rw[12] == 'E')
		$tareWeight = $rw[16];
	else $tareWeight = $rw[15];
	
	$RowNum++;
	$ColumnNum = 0;
	$worksheet->write($RowNum, $ColumnNum, $rw[0], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[1], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[12], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[8], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[2], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $tareWeight, $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[17], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[5]." ".$rw[6], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[14], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rw[7], $regularleftFormat);
	$ColumnNum += 1;
	
	
	$temp_grpby = $groupby;
	$grpbytotal['container'] = $grpbytotal['container'] + 1;
	$grpbytotal['weight'] = $grpbytotal['weight'] + $rw[3];
	
	$cntr_type1 = $rw[12];
	$cntr_type2 = $rw[13];
	
	$sumContainer[$cntr_type1][$cntr_type2] = $sumContainer[$cntr_type1][$cntr_type2] + 1;
	$sumWeight[$cntr_type1][$cntr_type2] = $sumWeight[$cntr_type1][$cntr_type2] + ($rw[12] == 'E'?$rw[16]:$rw[3]);
	
	$Sumtotal['container'] = $Sumtotal['container'] + 1;
	$Sumtotal['weight'] = $Sumtotal['weight'] + ($rw[12] == 'E'?$rw[16]:$rw[3]);
	
	$ct++;
}


	$tempvessel = $vessel_id; 
	$tempbol = $boltype;
	$i++;
}

 
$workbook->close();

?>