<?php
session_start();

include("config.inc.php");
include("Functions_UserAccess.php");
include("function.inc.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']);
mysql_select_db($_SESSION['dbName'],$connection);

mysql_query("SET NAMES 'utf8'"); 
mysql_query('SET CHARACTER SET utf8'); 

$filename = "Archivinglist" . date('Ymd') . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
print <<< HERE
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<TITLE> Offer Report </TITLE>

<style type="text/css">

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                }				 
table.master td { font-size: 14px; 
                  font-family:Times-New-Roman;
                 }
table.detail th { font-size: 13px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				  border-top:1px solid black;
				  border-bottom:1px solid black;
				  border-left:1px solid black;
                }				 
				 
table.detail td { font-size: 12px; 
                  font-family:Times-New-Roman;
				  border-left:1px solid black;
                 }			
h1.breakhere { page-break-after: auto;}				 
</style>
</HEAD>
<BODY>
HERE;

include("config.inc.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass'],true,65536)	or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");

$connection1 = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass'],true,65536)	or die("Could not connect to DB");
mysql_select_db($config['dbName_log'],$connection1) 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);
}

//mysql_set_charset('utf8',$connection);

//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Setup_vatno, ".
             " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage, ".
			 " Company_Name, Company_NameF, Company_Memo, Company_MemoF, Company_Capital, Company_CapitalF ".
			 " from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}


$querysetup ="select base1, base2 from ".$config['dbName_log'].".setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultsetup)){
	extract($rowmodule);
}


$product_id	 		 = $_GET['product_id'];
$product_name 		 = $_GET['product_name'];

$archive_id 		 = $_GET["archive_id"];
$archive_company 	 = $_GET["archive_company"];
$archive_issuedate 	 = $_GET["archive_issuedate"];

$archive_vessel 	 = $_GET["archive_vessel"];
$archive_consigned 	 = $_GET["archive_consigned"];  
$archive_loading 	 = $_GET["archive_loading"]; 
$archive_discharge 	 = $_GET["archive_discharge"]; 
$archive_freight 	 = $_GET["archive_freight"]; 
$archive_producttype = $_GET["archive_producttype"];
$archive_docnumber 	 = $_GET["archive_docnumber"];

$archive_container 	 = $_GET["archive_container"];
$archive_ForwardL 	 = $_GET["archive_ForwardL"];
$archive_ForwardB 	 = $_GET["archive_ForwardB"];
$archive_clearing 	 = $_GET["archive_clearing"];
$archive_clearing 	 = $_GET["archive_clearing"];
$groupbylist 		 = $_GET["groupbylist"];
$chkdetail 		 	 = $_GET["chkdetail"];
$fromdate 		 	 = $_GET["fromdate"];
$todate 		 	 = $_GET["todate"];
$archive_issuedate	 = $_GET["archive_issuedate"];

$ndate=explode("/",$fromdate);
$fromdate=$ndate[2]."-".$ndate[1]."-".$ndate[0];
$ndate=explode("/",$todate);
$todate=$ndate[2]."-".$ndate[1]."-".$ndate[0];
$ndate=explode("/",$archive_issuedate);
$archive_issuedate=$ndate[2]."-".$ndate[1]."-".$ndate[0];

$querysearch = "select companyname, consigned, portofloading, freight,shipper, containertype, ForwarderL, ForwarderB, clearingtime, issuedate, documentnumber, BL,
				GM, DT, fullpageBL, fullpageGM, fullpageDT, auto from (
				select companyname, consigned, portofloading, freight, shipper, containertype, ForwarderL, ForwarderB, clearingtime, issuedate, documentnumber, filename as BL,
				'' as GM, '' as DT,  fullpage as fullpageBL,'' as fullpageGM, '' as fullpageDT, vessel, portofdischarge, producttype,
				auto, period_id
				from archiving
				union 
				select '' as companyname, '' as consigned, '' as portofloading, '' as freight,'' as shipper, '' as containertye, '' as ForwarderL, '' as ForwarderB, '' as clearingtime ,'' as issuedate, documentnumber, '' as BL,
				filename as GM, '' as DT,  '' as fullpageBL, fullpage as fullpageGM, '' as fullpageDT, '' as vessel, '' as portofdischarge, '' as producttype,
				auto, '' as period_id
				from shipping
				union ";
if($chkdetail == 1)				
$querysearch = $querysearch." select companyname, consigned,  portofloading, freight, shipper, containertye, ForwarderL, ForwarderB,  clearingtime , issuedate, archivingdt.documentnumber, '' as BL,
				'' as GM, archivingdt.filename as DT,  '' as fullpageBL, '' as fullpageGM, fullpage as fullpageDT, '' as vessel, portofdischarge, '' as producttype,
				auto, period_id
				from archivingdt 
				inner join archiving on archiving.documentnumber = archivingdt.documentnumber";
else 	
$querysearch = $querysearch." select '' as companyname, '' as consigned, '' as portofloading, '' as  freight, '' as shipper, '' as containertye, '' as ForwarderL, '' as ForwarderB, '' as clearingtime ,'' as issuedate, documentnumber, '' as BL,
				'' as GM, archivingdt.filename as DT,  '' as fullpageBL, '' as fullpageGM, fullpage as fullpageDT, '' as vessel, '' as portofdischarge, '' as producttype,
				auto, '' as period_id
				from archivingdt";
				
$querysearch = $querysearch." ) as Q where 1=1 ";				
				
//echo $querysearch;exit;
if($filterflag == 1){
	
	if($archive_company != '') $querysearch = $querysearch." and ifnull(companyname,'') like ('%".$archive_company."%') ";
	if($archive_consigned != '') $querysearch = $querysearch." and ifnull(consigned,'') like ('%".$archive_consigned."%') ";
	if($archive_loading != '') $querysearch = $querysearch." and ifnull(portofloading,'') like ('%".$archive_loading."%') ";
	if($archive_freight != '') $querysearch = $querysearch." and ifnull(freight,'') like ('%".$archive_freight."%') ";
	if($archive_shipper != '') $querysearch = $querysearch." and ifnull(shipper,'') like ('%".$archive_shipper."%') ";
	if($archive_docnumber != '') $querysearch = $querysearch." and ifnull(documentnumber,'') like ('%".$archive_docnumber."%') ";
	if($archive_vessel != '') $querysearch = $querysearch." and ifnull(vessel,'') like ('%".$archive_vessel."%') ";
	if($archive_discharge != '') $querysearch = $querysearch." and ifnull(portofdischarge,'') like ('%".$archive_discharge."%') ";
	if($archive_producttype != '') $querysearch = $querysearch." and ifnull(producttype,'') like ('%".$archive_producttype."%') ";
	if($period_id != '') $querysearch = $querysearch." and ifnull(period_id,'') like ('%".$period_id."%') ";
	
	if($archive_container != '') $querysearch = $querysearch." and ifnull(containertype,'') like ('%".$archive_container."%') ";
	if($archive_ForwarderL != '') $querysearch = $querysearch." and ifnull(ForwarderL,'') like ('%".$archive_ForwarderL."%') ";
	if($archive_ForwarderB != '') $querysearch = $querysearch." and ifnull(ForwarderB,'') like ('%".$archive_ForwarderB."%') ";
	if($archive_clearing != '') $querysearch = $querysearch." and ifnull(clearingtime,'') like ('%".$archive_clearing."%') ";
	
	if($chkdate == 1) 
		$querysearch = $querysearch." and ( Check_DateFormat(issuedate) between date_format('$fromdate','%Y-%m-%d') and date_format('$todate','%Y-%m-%d') ) ";
	if($chkdate2 == 1) 
		$querysearch = $querysearch." and ( Check_DateFormat(issuedate) = date_format('$archive_issuedate', '%Y-%m-%d')) ";
} else if($advfilterflag == 1){
	$querysearch = $querysearch." and ( ifnull(fullpageBL,'') like ('%".$archive_company."%') or 
							ifnull(fullpageGM,'') like ('%".$archive_company."%') or ifnull(fullpageDT,'') like ('%".$archive_company."%') ) ";
	} 
$querysearch = $querysearch." order by documentnumber ";
//echo $querysearch;
//exit;

$resultsearch = mysql_query($querysearch,$connection);
$numrow = mysql_num_rows($resultsearch);


//////////////////////////////////

$style="style='border:1px solid black; text-align:right;'";
echo "<table border='0' style='table-layout:fixed' align='left' width='1270px' valign='top' > ";

		//T-header

		
echo "	<tr><td style='vertical-align: top;' colspan='3'><span style='font-size: 20px; font-weight: bold'>$Company_Name</span><br>
			<span style='font-size: 11px;'><pre>".str_replace( '\n', '<br>', $Company_Memo)."</pre></span></td>
			<td align='center'  colspan='8' valign='top' style='font-size:20px; font-weight:bold;'>List of Archives<td>";
		echo "<td colspan='3' align='left' style='font-size: 11px;' ><span><b>Date: </b>";
		echo date("d/m/Y")."<br>";
		echo "<span><b>Time:</b></span>&nbsp;".date("h:i:sa");
		echo "</td>";			
			
		//table header
		
echo "	</tr><tr></tr></table>";

echo "<table border='1'  width='2000px'>";
echo "<col width='150px'><col width='30px'><col width='30px'><col width='30px'><col width='100px'><col width='180px'><col width='180px'>
	<col width='100px'><col width='120px'><col width='100px'><col width='100px'><col width='100px'><col width='100px'><col width='50px'><col width='50px'><col width='50px'>";  

$query = "select archive_path from setup";
$res = mysql_query($query,$connection);
$row = mysql_fetch_row($res);
$archive_path = $row[0];

echo "<tr height=50 bgcolor='999999' valign='center'>
		<td><span class='style1'><b>Document #</b></span></td>
		<td><span class='style1'><b>BL</b></span></td>
		<td><span class='style1'><b>GM</b></span></td>
		<td><span class='style1'><b>DT</b></span></td>
		<td><span class='style1'><b>Company Name</b></span></td>
		<td><span class='style1'><b>Shipper</b></span></td>
		<td><span class='style1'><b>Consigned</b></span></td>
		<td><span class='style1'><b>Port of Loading</b></span></td>
		<td><span class='style1'><b>Freight</b></span></td>
		<td><span class='style1'><b>Container</b></span></td>
		<td><span class='style1'><b>Forwarder L</b></span></td>
		<td><span class='style1'><b>Forwarder D</b></span></td>
		<td><span class='style1'><b>Clearing Time</b></span></td>
		<td><span class='style1'><b>Full Page BL</b></span></td>
		<td><span class='style1'><b>Full Page GM</b></span></td>
		<td><span class='style1'><b>Full Page DT</b></span></td>
	  </tr>";
echo "<tr><td colspan='16'><hr></td></tr>";

	  $i = 0;
	while($rows = mysql_fetch_array($resultsearch)){
		extract($rows);
		
	if($i%2) $bgcolor = "bgcolor = '#E6E6E6'";
		else $bgcolor = "bgcolor = '#F7F7F7'";
		
	echo "<tr $bgcolor  height=60>
			<td><span class='style111'>$documentnumber</span></td>";
	echo "<td><span class='style111'>";
	if($BL != '')
	echo "<a href='./$archive_path$BL' download>$archive_path$BL</a>";
	echo"</span></td>";
	echo "<td><span class='style111'>";
	if($GM != '')
	echo "<a href='./$archive_path$GM' download >$GM</a>";
	echo"</span></td>";
	echo "<td><span class='style111'>";
	if($DT != '')
	echo "<a href='./$archive_path$DT' download >$DT</a>";
	echo"</span></td>";
	echo "	<td><span class='style111'>$companyname</span></td>
	        <td><span class='style111'>$shipper</span></td>
			<td><span class='style111'>$consigned</span></td>
			<td><span class='style111'>$portofloading</span></td>
			<td><span class='style111'>$freight</span></td>
			<td><span class='style111'>$containertype</span></td>
			<td><span class='style111'>$ForwarderL</span></td>
			<td><span class='style111'>$ForwarderB</span></td>
			<td><span class='style111'>$clearingtime</span></td>"; 
	echo "<td><span class='style111'>";
	if($fullpageBL != '')		

	echo"</span></td>";
	echo "<td><span class='style111'>";
	if($fullpageGM != '')
	
	echo"</span></td>";
	echo "<td><span class='style111'>";
	if($fullpageDT != '')
	
	echo"</span></td></tr>";	
	$i++;
	}
	
echo "	</table>";


function formatdate($dateval){
	if($dateval != ''){
	$ndate = explode("-", $dateval);
	$dateval = $ndate[2]."-".$ndate[1]."-".$ndate[0];
}
	return $dateval;
}
print <<< HERE

</BODY>
</HTML>
HERE;
	