<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}
include("config.inc.php");
include("function.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>Manifest Cash Flow</title></head>
<style>
.header1 {	
	text-decoration-line: underline;
    font-size: 16px;
    font-family: serif;
}

.header2 {	
	font-size: 15px;
    font-family: serif;
}
.header3 {	
	font-size: 14px;
    font-family: serif;
}
.detail1 {
	font-size: 13px;
    font-family: serif;
}
.detail2 {
	border-bottom: 1px solid black;
	font-size: 13px;
    font-family: serif;
}
@media print 
{
   @page
   {
    size: portrait;
  }
}
</style>
<body>

HERE;
$LineLimit = 40;

$query = "select upper(company_name), decimalBase from setup";
$res = mysql_query($query, $connection);
$rw = mysql_fetch_row($res);
$compname 		= $rw[0];
$decimalBase    = $rw[1];

$queryheader = "select distinct vessel_id, date_format(received_date,'%d/%m/%Y'), if(manifest.BOL_type='E',Portofloading, PortofDischarge) 
				from manifest where voyage_year = '$voyage_year' and voyage_number = '$voyage_number' and BOL_type = '$voyage_type' ";
$resheader   = mysql_query($queryheader, $connection);
$rowheader   = mysql_fetch_row($resheader);
$vessel  	 = $rowheader[0];
$manfdate  	 = $rowheader[1];
$portName  	 = $rowheader[2];

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

$pagenbr = 1;
$itemPrice = array();

$localCharges 	 = array();
$localChargesCur = array();

$tarrosCharges 	  = array();
$tarrosChargesCur = array();

rep_header($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr);

$lineNbr = 3;
$tempPort = '*';

		
$querydet = "select localagent_code, agent.Agent1, null as portname, null as BOL_identification, null as received_date, ifnull(chk_nodetail,0), null as manifest_auto, null as consignee1
			from manifest
			left join agent on agent.code = localagent_code
			where voyage_year = '$voyage_year' and voyage_number = '$voyage_number' and BOL_type = '$voyage_type' and ifnull(chk_nodetail,0) = '1'
			union
			select localagent_code, agent.Agent1, if(manifest.BOL_type='E',PortofDischarge,Portofloading) as portname, BOL_identification, date_format(received_date,'%d/%m/%Y') as received_date,
			ifnull(chk_nodetail,0),manifest_auto, consignee1
			from manifest
			left join agent on agent.code = localagent_code
			where voyage_year = '$voyage_year' and voyage_number = '$voyage_number' and BOL_type = '$voyage_type'  and ifnull(chk_nodetail,0) = '0'
			order by portname, date_format(received_date,'%Y-%m-%d'), BOL_identification  "; //echo $querydet;
$resdet = mysql_query($querydet, $connection);			
while ($rowdet = mysql_fetch_array($resdet)){
	$nodetail = $rowdet[5];
	if ($nodetail == 1){
		echo "<tr height='20px'><td><span class='detail1'>$rowdet[0]</span></td><td colspan='3'><span class='detail1'>$rowdet[1]</span></td></tr>";
		$lineNbr++;
		if (!($lineNbr%$LineLimit)){
			$pagenbr++;
			nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );	
			$lineNbr = 3;	
		}
		// GET Containers
		$queryCntr = "select BOL_freight, if(emptyContainer=0, 'EMPTY', 'FULL'), replace(group_concat(concat( cntr_nbr,'*',cntr_type)),',','+') as cntr
						from (
						select manifest.BOL_freight, ifnull(manifest_emptyContainer,0) as emptyContainer, if(ifnull(type_id,'') = '', container_type, type_id) as cntr_type, count(1) as cntr_nbr
						from manifestdt
						inner join manifest on manifest.Manifest_auto = manifestdt.manifest_manifestauto
						left join container on container.cntr_code = manifestdt.container
						where manifest.voyage_year = '$voyage_year' and manifest.voyage_number = '$voyage_number' and manifest.BOL_type = '$voyage_type' and (ifnull(ctrflag,'') != '*' or detailtype = '1')
						and localagent_code = '$rowdet[0]'
						group by manifest.BOL_freight, emptyContainer, cntr_type
						) as Q
						group by BOL_freight, emptyContainer ";
		$resCntr = mysql_query($queryCntr, $connection);
		while ($rwCntr = mysql_fetch_array($resCntr)){
			echo "<tr><td></td><td colspan='3'><span class='detail1'>$rwCntr[2] - $rwCntr[1] CNTRS $rwCntr[0]</span></td></tr>";
			$lineNbr++;
			if (!($lineNbr%$LineLimit)){
				$pagenbr++;
				nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );		
				$lineNbr = 3;
			}
		}
		// Tarros Prices
		if ($withprice == '1'){ 
			
			$result = calculate_TarrosPrice ($voyage_year, $voyage_number, $voyage_type, $rowdet[0], '', $connection);
			while ($items = mysql_fetch_array($result)){ 
				echo "<tr><td></td><td></td><td><span class='detail1'>".$items[0]."</span></td><td><span class='detail1'>".$items[2]." &nbsp;&nbsp ".number_format($items[1],$decimalBase)."</span></td></tr>";
				$tarrosCharges[$items[0]][$items[2]] += $items[1];
				$tarrosChargesCur[$items[2]] += $items[1];
				$lineNbr++;
				if (!($lineNbr%$LineLimit)){
					$pagenbr++;
					nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );		
					$lineNbr = 3;
				}
			}
		}
		// Local Prices
		if ($withlocalprice == '1'){
			$result = calculate_LocalPrice ($voyage_year, $voyage_number, $voyage_type, $rowdet[0], '', $connection);
			while ($items = mysql_fetch_array($result)){ 
				echo "<tr><td></td><td></td><td><span class='detail1'>".$items[0]."</span></td><td><span class='detail1'>".$items[2]." &nbsp;&nbsp ".number_format($items[1],$decimalBase)."</span></td></tr>";
				$localCharges[$items[0]][$items[2]] = $localCharges[$items[0]][$items[2]]+ $items[1];
				$localChargesCur[$items[2]] += $items[1];
				$lineNbr++;
				if (!($lineNbr%$LineLimit)){
					$pagenbr++;
					nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );	
					$lineNbr = 3;					
				}
			}
		}		
	}
	else {
		if ($tempPort != $rowdet[2]){
			echo "<tr height='20px'><td colspan='3'><span class='detail2'>$rowdet[2] ($rowdet[4])</span></td></tr>";
			$lineNbr++;
			if (!($lineNbr%$LineLimit)){
				$pagenbr++;
				nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );
				$lineNbr = 3;						
			}
		}
		echo "<tr height='20px'><td><span class='detail1'>$rowdet[3]</span></td><td colspan='3'><span class='detail1'>$rowdet[7]</span></td></tr>";
		$lineNbr++;
		if (!($lineNbr%$LineLimit)){
			$pagenbr++;
			nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );		
			$lineNbr = 3;		
		}
		// GET Containers
		$queryCntr = "select BOL_freight, if(emptyContainer=0, 'FULL', 'EMPTY'), replace(group_concat(concat( cntr_nbr,'*',cntr_type)),',','+') as cntr
					from (
					select manifest.BOL_freight, ifnull(manifest_emptyContainer,0) as emptyContainer, if(ifnull(type_id,'') = '', container_type, type_id) as cntr_type, count(1) as cntr_nbr
					from manifestdt
					inner join manifest on manifest.Manifest_auto = manifestdt.manifest_manifestauto
					left join container on container.cntr_code = manifestdt.container
					where manifest.manifest_auto = '$rowdet[6]' and (ifnull(CTRFlag,'') != '*' or detailtype = '1') 
					group by manifest.BOL_freight, emptyContainer, cntr_type
					) as Q
					group by BOL_freight, emptyContainer ";
		$resCntr = mysql_query($queryCntr, $connection);
		while ($rwCntr = mysql_fetch_array($resCntr)){
			echo "<tr><td></td><td colspan='3'><span class='detail1'>$rwCntr[2] - $rwCntr[1] CNTRS $rwCntr[0]</span></td></tr>";
			$lineNbr++;
			if (!($lineNbr%$LineLimit)){
				$pagenbr++;
				nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );	
				$lineNbr = 3;						
			}
		}
		// Tarros Prices
	 	if ( $withprice == '1'){ 
			$result = calculate_TarrosPrice ($voyage_year, $voyage_number, $voyage_type, '', $rowdet[6], $connection);
			while ($items = mysql_fetch_array($result)){ 
				echo "<tr><td></td><td></td><td><span class='detail1'>".$items[0]."</span></td><td><span class='detail1'>".$items[2]." &nbsp;&nbsp ".number_format($items[1],$decimalBase)."</span></td></tr>";
				$tarrosCharges[$items[0]][$items[2]] += $items[1];
				$tarrosChargesCur[$items[2]] += $items[1];
				$lineNbr++;
				if (!($lineNbr%$LineLimit)){
					$pagenbr++;
					nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );	
					$lineNbr = 3;				
				}
			}
		} 
		// Local Prices
		if ($withlocalprice == '1'){
			$result = calculate_LocalPrice ($voyage_year, $voyage_number, $voyage_type, '', $rowdet[6], $connection);
			while ($items = mysql_fetch_array($result)){ 
				echo "<tr><td></td><td></td><td><span class='detail1'>".$items[0]."</span></td><td><span class='detail1'>".$items[2]." &nbsp;&nbsp ".number_format($items[1],$decimalBase)."</span></td></tr>";
				$localCharges[$items[0]][$items[2]] = $localCharges[$items[0]][$items[2]]+ $items[1];
				$localChargesCur[$items[2]] += $items[1];
				$lineNbr++;
				if (!($lineNbr%$LineLimit)){
					$pagenbr++;
					nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );		
					$lineNbr = 3;			
				}
			}
		}	
		$tempPort = $rowdet[2];
	}
}

if ($withlocalprice == '1' || $withprice == '1')
echo "<tr><td colspan='4'><hr style='border-top: 1px solid black;'></td></tr>";

if ($withprice == '1'){ 
	echo "<tr><td><span class='detail2'>CHARGES</span></td><td colspan='2'></td><td></td></tr>";
	foreach($tarrosCharges as $item => $Curs){
		foreach($Curs as $Cur => $price){
			echo "<tr height='20px'><td></td><td colspan='2'><span class='detail1'>".$item."</span></td><td><span class='detail1'>".$Cur." &nbsp;&nbsp; ".number_format($price, $decimalBase)."</span></td></tr>";
			$lineNbr++;
			if (!($lineNbr%$LineLimit)){
				$pagenbr++;
				nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );		
				$lineNbr = 3;			
			}
		}
	}
	echo "<tr height='20px'><td></td><td colspan='2'></td><td><hr style='width: 25%;float: left;border-top: 1px solid black;'></td></tr>";
	foreach($tarrosChargesCur as $Curr => $price){
		echo "<tr height='20px'><td></td><td colspan='2'></td><td><span class='detail1'>".$Curr." &nbsp;&nbsp; ".number_format($price, $decimalBase)."</span></td></tr>";
	}
	
}


if ($withlocalprice == '1'){
	echo "<tr><td><span class='detail2'>LOCAL CHARGES</span></td><td colspan='2'></td><td></td></tr>";
	foreach($localCharges as $item => $Curs){
		foreach($Curs as $Cur => $price){
			echo "<tr height='20px'><td></td><td colspan='2'><span class='detail1'>".$item."</span></td><td><span class='detail1'>".$Cur." &nbsp;&nbsp; ".number_format($price, $decimalBase)."</span></td></tr>";
			$lineNbr++;
			if (!($lineNbr%$LineLimit)){
				$pagenbr++;
				nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr );		
				$lineNbr = 3;			
			}
		}
	}
	echo "<tr height='20px'><td></td><td colspan='2'></td><td><hr style='width: 25%;float: left;border-top: 1px solid black;'></td></tr>";
	foreach($localChargesCur as $Curr => $price){
		echo "<tr height='20px'><td></td><td colspan='2'></td><td><span class='detail1'>".$Curr." &nbsp;&nbsp; ".number_format($price, $decimalBase)."</span></td></tr>";
	}
	
	
}

echo "<tr><td colspan='4'><hr style='border-top: 1px solid black;'></td></tr>";
echo "<tr><td colspan='2' valign='top'><table width=100% cellpadding=0 cellspacing=0 border=0 >";
// calculate total Containers
$queryContainer = "select if(emptyContainer=0, 'FULL', 'EMPTY'), cntr_type, count(cntr_nbr)
					from (
					select manifest.BOL_freight, ifnull(manifest_emptyContainer,0) as emptyContainer,
					substr(if(ifnull(type_id,'') = '', container_type, type_id),1,2) as cntr_type, 1 as cntr_nbr
					from manifestdt
					inner join manifest on manifest.Manifest_auto = manifestdt.manifest_manifestauto
					left join container on container.cntr_code = manifestdt.container
					where manifest.voyage_year = '$voyage_year' and manifest.voyage_number = '$voyage_number' and manifest.BOL_type = '$voyage_type' and (ifnull(CTRFlag,'') != '*' or detailtype = '1')
					) as Q
					group by emptyContainer, cntr_type
					order by emptyContainer, cntr_type "; 
$resContainer = mysql_query($queryContainer, $connection);		
			
while ($rwContainer = mysql_fetch_array($resContainer)){
	echo "<tr><td><span class='detail1'>TOTAL CNTR $rwContainer[1] $rwContainer[0]  </span></td><td><span class='detail1'> = $rwContainer[2]</span></td></tr>";
}
echo "</table></td><td colspan='2' valign='top'><table width=100%  cellpadding=0 cellspacing=0 border=0>";

$queryPrepaid = "select replace(group_concat(cntr),',',' + ') from (
					select BOL_freight, concat(cntr_type,'/', count(cntr_nbr)) as cntr
					from (
					select manifest.BOL_freight, ifnull(manifest_emptyContainer,0) as emptyContainer,
					substr(if(ifnull(type_id,'') = '', container_type, type_id),1,2) as cntr_type, 1 as cntr_nbr
					from manifestdt
					inner join manifest on manifest.Manifest_auto = manifestdt.manifest_manifestauto
					left join container on container.cntr_code = manifestdt.container
					where manifest.voyage_year = '$voyage_year' and manifest.voyage_number = '$voyage_number' and manifest.BOL_type = '$voyage_type' and (ifnull(CTRFlag,'') != '*' or detailtype = '1')
					and manifest.BOL_freight = 'PREPAID'
					) as Q
					group by BOL_freight, cntr_type
					) as QQ;";
$resPrepaid = mysql_query($queryPrepaid, $connection);		
$rwPrepaid = mysql_fetch_array($resPrepaid);
	
echo "<tr><td align='right'><span class='detail1'>TOTAL AT DEST </span></td><td><span class='detail1'> : </span></td></tr>";
echo "<tr><td align='right'><span class='detail1'>TOTAL PREPAID </span></td><td><span class='detail1'> : $rwPrepaid[0]</span></td></tr>";
echo "</table></td></tr>";
echo "</table>";

print <<< HERE

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

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

function nextPage($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr ){
	echo "</table>";
	echo "<div style='page-break-after: always;'></div>";
	rep_header($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr);
}	
function rep_header($vessel, $manfdate, $voyage_number, $portName, $compname, $pagenbr){
	$bottom = "border-bottom: 1px solid black";
	
	echo "<table border=0 cellpadding=0 cellspacing=0 width=100%>
		<col width=50%><col width=25%><col width=25%>";
	echo "<tr height='30px'><td><span class='header1'>$compname</span></td><td></td><td><span class='header2'>PAGE $pagenbr</span></td></tr>";
	echo "<tr height='30px'><td colspan='3'><span class='header2'>$vessel V/$voyage_number ARRIVAL: $manfdate</span></td></tr>";
	echo "</table>";
	
	echo "<table border=0 cellpadding=0 cellspacing=5 width=100%>
		<col width=20%><col width=30%><col width=30%><col width=20%>";
	echo "<tr height='20px'>
			<td align='center' style='$bottom;'><span class='header3'>B/L No.</span></td>
			<td></td>
			<td align='center' style='$bottom;'><span class='header3'>COLLECT AT $portName</span></td>
			<td align='center' style='$bottom;'><span class='header3'>PREPAID</span></td>
		</tr>";	
	echo "</table>";
	echo "<table border=0 cellpadding=0 cellspacing=0 width=100%>
		<col width=20%><col width=15%><col width=30%><col width=35%>";	
}	

function calculate_TarrosPrice ($voyage_year, $voyage_number, $voyage_type, $localAgent, $manifest_auto, $connection){
	if ($manifest_auto != '') $query2 = " manifest_auto = $manifest_auto ";
	else $query2 = " voyage_year = '$voyage_year' and voyage_number = '$voyage_number' and BOL_type = '$voyage_type' and localagent_code = '$localAgent' ";
	
	$query = "select itemdesc, sum(amount), currency from ( ";
	
	$query .= "select tax1_desc as itemdesc, tax1_amount as amount, currency
			from manifest
			where ";
	$query .= $query2;
	$query .= " union
			select tax2_desc as itemdesc, tax2_amount as amount, currency
			from manifest
			where ";
	$query .= $query2;
	$query .= " union
			select tax3_desc as itemdesc, tax3_amount as amount, currency
			from manifest
			where ";
	$query .= $query2;
	$query .= " union
			select tax4_desc as itemdesc, tax4_amount as amount, currency
			from manifest
			where ";
	$query .= $query2;
	$query .= " union
			select tax5_desc as itemdesc, tax5_amount as amount, currency
			from manifest
			where ";
	$query .= $query2;
	$query .= " union
			select tax6_desc as itemdesc, tax6_amount as amount, currency
			from manifest
			where ";
	$query .= $query2;
	$query .= " ) as Q where ifnull(itemdesc,'') <> ''
				group by itemdesc, currency ";
	
	//echo $query;echo "<br>";
	$res = mysql_query($query, $connection);
	return $res;
}

function calculate_LocalPrice ($voyage_year, $voyage_number, $voyage_type, $localAgent, $manifest_auto, $connection){
	$query = "select upper(item_name), (tax_amount*quantity) - discount, currencycode  from manifestdtl
			inner join manifest on manifest.Manifest_auto = manifestdtl.manifest_manifestauto
			where ";
	if ($manifest_auto != '') $query .= " manifest_auto = $manifest_auto ";
	else $query .= " manifest.voyage_year = '$voyage_year' and manifest.voyage_number = '$voyage_number' and manifest.BOL_type = '$voyage_type' and localagent_code = '$localAgent' ";
	$res = mysql_query($query, $connection);
	return $res;
}



?>














