<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}
include("config.inc.php");

$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");

if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}


print <<<HERE
<html>
<head><title>Recaptulation</title></head>
<style>
.header1 {	
	font-weight: bold;
    font-size: 20px;
}
</style>
<body>

HERE;

$query = "select company_name from setup";
$res = mysql_query($query, $connection);
$rw = mysql_fetch_row($res);
$compname = $rw[0];
if ($voyage_type=='I') $voyagetype="Import";
if ($voyage_type=='E') $voyagetype="Export";
if ($voyage_type=='T') $voyagetype="FreeZone";
if ($voyage_type=='S') $voyagetype="Transhipment";


$bottom = "border-bottom: 1px solid black";
$top = "border-top: 1px solid black";
$top2 = "border-top: 1px dashed black";

echo "<table cellpadding=0 cellspacing=0 width=900px><col width=300px><col width=400px><col width=250px>";
echo "<tr><td><span class='header1'>$compname</span></td><td align='center'><span class='header1'>Recaptulation</span></td><td align='right'><span class='header1'>$voyagetype</span></td></tr>";
echo "</table>";
$querydt = '';
if ($manifest_auto!='') 		$querydt .= " and manifest.manifest_auto = '$manifest_auto' ";
if ($billlading!='') 			$querydt .= " and manifestdt.BOL_identification = '$billlading' ";
if ($voyage_year!='') 			$querydt .= " and manifestdt.voyage_year = '$voyage_year' ";
if ($voyage_number!='') 		$querydt .= " and manifestdt.voyage_number = '$voyage_number' ";
if ($voyage_destination!='') 	$querydt .= " and manifestdt.voyage_destination = '$voyage_destination' ";
if ($bol_number!='') 			$querydt .= " and manifestdt.voyage_BOLnumber = '$bol_number' ";
if ($voyage_type!='') 			$querydt .= " and manifestdt.BOL_type = '$voyage_type' ";
if ($voyage_pagenumber!='') 	$querydt .= " and manifestdt.BOL_pagenumber = '$voyage_pagenumber' ";
if ($portofloading!='') 		$querydt .= " and Portofloading = '$portofloading' ";
if ($portofdischarge!= '') 		$querydt .= " and PortofDischarge='$portofdischarge' ";
if ($localagent_code!='') 		$querydt .= " and manifest.localagent_code = '$localagent_code' ";
 
if ($cntrtype == '1')			$querydt .= " and ifnull(manifest_emptyContainer,0) = '0' ";
else if ($cntrtype == '2')		$querydt .= " and ifnull(manifest_emptyContainer,0) = '1' ";		


$query = "select BOL_type, portname, localagent_code, Agent1, sum(pkgs), sum(weight), sum(container20), sum(container40) from (
		select BOL_type, portname, localagent_code, Agent1, pkgs, weight, if (conttype = '20',contcount,'') as container20,
		if (conttype = '40' or conttype = '45',contcount,'') as container40
		from (
		select BOL_type, portname, localagent_code, Container, sum(pkgs) as pkgs, sum(weight) as weight, count(conttype) as contcount, conttype, Agent1 
		from (
		select manifest.BOL_type as BOL_type, if(manifest.BOL_type='E',PortofDischarge,Portofloading) as portname, localagent_code, Container, Packages as pkgs, if(ifnull(manifest_emptyContainer,0) = 1,
		if (if(ifnull(type_id,'') = '', substr(container_type, 1,1),substr(type_id, 1,1)) = '2', '2200', '4000'),replace(Container_Weight,',','')) as weight,
		if(ifnull(type_id,'') = '', substr(container_type, 1,2),substr(type_id, 1,2)) as conttype, agent.Agent1 as Agent1
		from manifestdt
		inner join manifest on manifest.Manifest_auto = manifestdt.manifest_manifestauto
		left join agent on agent.code = manifest.localagent_code
		left join container on container.cntr_code = container
		where (ifnull(ctrflag,'') = '' or detailtype=1) ";
		
$query .= $querydt;
		
$query .="	) as Q1
		 group by BOL_type, portname, localagent_code, conttype
		 order by BOL_type, portname, localagent_code, conttype
		) as Q ";
//order by BOL_type, portname, localagent_code, conttype		
$query .= " union
			select manifest.BOL_type as BOL_type, if(manifest.BOL_type='E',PortofDischarge,Portofloading) as portname, localagent_code, agent.Agent1 as Agent1,
			ifnull(Packages,0) as pkgs,replace(Container_Weight,',','') as weight,
			0 as container20, 0 as container40
			from manifestdt inner join manifest on manifest.Manifest_auto = manifestdt.manifest_manifestauto left join agent on agent.code = manifest.localagent_code
			 where (ifnull(ctrflag,'') = '*' and detailtype <> 1) "; 
$query .= $querydt;			 
$query .= " ) as QQ
		group by BOL_type, portname, localagent_code
		order by BOL_type, portname, localagent_code;";
$result = mysql_query($query, $connection);
$temptype = "*";
$tempport = "*";
$tempagent = "*";

$pkgPerPort    = 0;
$WeightPerPort = 0;
$Cntr20PerPort = 0;
$Cntr40PerPort = 0;

$TotpkgPerPort    = 0;
$TotWeightPerPort = 0;
$TotCntr20PerPort = 0;
$TotCntr40PerPort = 0;

$i=0;
$t=0;
echo "<table cellpadding=0 cellspacing=0 width=900px>
			<col width='320px'><col width='200px'><col width='200px'><col width='120px'><col width='120px'>";
while ($row = mysql_fetch_array($result)){
	if ($t==0 && $voyage_year != '' && $voyage_number != ''){
		$queryh = "select distinct vessel_id,date_format(received_date,'%d/%m/%Y') from manifest where voyage_year = $voyage_year and voyage_number = $voyage_number and bol_type='$row[0]'";
		$resh = mysql_query($queryh, $connection);
		$rowh = mysql_fetch_row($resh);
		$vesselname = $rowh[0];
		$receiveddate =$rowh[1];
		echo "<tr><td colspan = '5'><b>VOY NO. $voyage_number &nbsp;&nbsp; $vesselname $receiveddate </b></td></tr>";
		
	}
	if ($temptype != $row[0] && $i>0){
		
		echo "<tr height='25px'><td ></td><td align='right' style='$top;'>$pkgPerPort</td>
				<td align='right' style='$top;'>".number_format($WeightPerPort,2)." KILOS</td>
				<td align='right' style='$top;'>$Cntr20PerPort * 20'</td><td align='right' style='$top;'>$Cntr40PerPort * 40'/45'</td>
			</tr>";
			
		echo "<tr height='40px'><td ><b>TOTAL GENERAL</b></td><td align='right' style='$top2;'><b>$TotpkgPerPort</b></td>
				<td align='right' style='$top2;'><b>".number_format($TotWeightPerPort,2)." KILOS</b></td>
				<td align='right' style='$top2;'><b>$TotCntr20PerPort * 20'</b></td><td align='right' style='$top2;'><b>$TotCntr40PerPort * 40'/45'</b></td>
			</tr>";
		echo "</table>";	
		echo "<div style='page-break-after: always;'></div>";
		
		echo "<table cellpadding=0 cellspacing=0 width=900px><col width=300px><col width=400px><col width=250px>";
		echo "<tr><td><span class='header1'>$compname</span></td><td align='center'><span class='header1'>Recaptulation</span></td><td align='right'><span class='header1'>$voyagetype </span></td></tr>";
		echo "</table>";
		echo "<table cellpadding=0 cellspacing=0 width=900px>
			<col width='320px'><col width='200px'><col width='200px'><col width='120px'><col width='120px'>";
			if ($voyage_year != '' && $voyage_number != ''){
				$queryh = "select distinct vessel_id from manifest where voyage_year = $voyage_year and voyage_number = $voyage_number and bol_type='$row[0]'";
				$resh = mysql_query($queryh, $connection);
				$rowh = mysql_fetch_row($resh);
				$vesselname = $rowh[0];
				echo "<tr><td colspan = '5'><b>VOY NO. $voyage_number &nbsp;&nbsp; $vesselname</b></td></tr>";
				
			}
		$temptype = "*";
		$tempagent = "*";
		
		$TotpkgPerPort    = 0;
		$TotWeightPerPort = 0;
		$TotCntr20PerPort = 0;
		$TotCntr40PerPort = 0;
		
		$WeightPerPort = 0; 
		$Cntr20PerPort = 0;
		$Cntr40PerPort = 0;
		$pkgPerPort    = 0;
		$i = 0;
	}
	
	if($tempport != $row[1]){
		if ($i>0)
		echo "<tr height='25px'><td ></td><td align='right' style='$top;'>$pkgPerPort</td>
				<td align='right' style='$top;'>".number_format($WeightPerPort,2)." KILOS</td>
				<td align='right' style='$top;'>$Cntr20PerPort * 20'</td><td align='right' style='$top;'>$Cntr40PerPort * 40'/45'</td>
			</tr>";
		echo "<tr height='25px'><td></td><td colspan='4'></td></tr>";
		echo "<tr height='25px'><td><u>$row[1]</u></td><td colspan='4'></td></tr>";
		$tempagent = "*";
		$WeightPerPort = 0; 
		$Cntr20PerPort = 0;
		$Cntr40PerPort = 0;
		$pkgPerPort    = 0;
	}
	if($tempagent != $row[2]){
		echo "<tr height='25px'><td>$row[3]</td><td align='right'>$row[4]</td><td align='right'>".number_format($row[5],2)." KILOS</td>
							<td align='right'>$row[6] * 20'</td><td align='right'>$row[7] * 40'/45'</td>
			  </tr>";
		
	}	
	
	$i++;
	$tempport = $row[1];
	$temptype = $row[0];
	$tempagent = $row[2];
	
	$TotpkgPerPort    = $TotpkgPerPort + $row[4];
	$TotWeightPerPort = $TotWeightPerPort + round($row[5],2);
	$TotCntr20PerPort = $TotCntr20PerPort + $row[6];
	$TotCntr40PerPort = $TotCntr40PerPort + $row[7];

	$pkgPerPort    = $pkgPerPort + $row[4];
	$WeightPerPort = $WeightPerPort + round($row[5],2);
	$Cntr20PerPort = $Cntr20PerPort + $row[6];
	$Cntr40PerPort = $Cntr40PerPort + $row[7];
	$t++;
}

echo "<tr height='25px'><td ></td><td align='right' style='$top;'>$pkgPerPort</td>
		<td align='right' style='$top;'>".number_format($WeightPerPort,2)." KILOS</td>
		<td align='right' style='$top;'>$Cntr20PerPort * 20'</td><td align='right' style='$top;'>$Cntr40PerPort * 40'/45'</td>
	</tr>";
	
echo "<tr height='40px'><td ><b>TOTAL GENERAL</b></td><td align='right' style='$top2;'><b>$TotpkgPerPort</b></td>
		<td align='right' style='$top2;'><b>".number_format($TotWeightPerPort,2)." KILOS</b></td>
		<td align='right' style='$top2;'><b>$TotCntr20PerPort * 20'</b></td><td align='right' style='$top2;'><b>$TotCntr40PerPort * 40'/45'</b></td>
	</tr>";
echo "</table>";
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
HERE;

echo "</body>
	</html>";

?>














