<?php

$start = time();

include_once 'db.php';

require 'vendor/autoload.php';

use PHPCoord\BritishNationalGridPoint;
use PHPCoord\GeographicPoint;
use PHPCoord\ProjectedPoint;
use PHPCoord\CoordinateReferenceSystem\Geographic2D;
use PHPCoord\CoordinateReferenceSystem\Projected;
use PHPCoord\UnitOfMeasure\Length\Metre;
use PHPCoord\UnitOfMeasure\Angle\Degree;


date_default_timezone_set('Europe/London');

$myloc_gr = 'TQ123456';

//$bof_url = "http://www.britishorienteering.org.uk/event_diary_pda.php";
$bof_url = "caches/map/event_diary_pda.html";

//$regex = "|<tr style.*?<td></td>\s<td>(.+?)</td>|s";
$regex = "|<tr style.*?<td>[A-Za-z]{3} ([0-9]{2})\/([0-9]{2})\/([0-9]{2})<\/td>\s*?<td>.*?=([0-9]{5})&.*?>(.+?)<\/a><\/td>\s*?<td>(.+?)<\/td>\s*?<td>(.*?)<\/td>\s*?<td>(.*?)<\/td>\s*?<td>(.*?)<\/td>\s*?<td>(.+?)<\/td>\s*?<td>(.*?)<\/td><\/tr>|s";
$regex = "|<tr style.*?<td>[A-Za-z]{3} ([0-9]{2})\/([0-9]{2})\/([0-9]{2})<\/td>\s*?<td>.*?=([0-9]{5})&.*?>(.+?)<\/a><\/td>\s*?<td>(.+?)<\/td>\s*?<td>(.*?)<\/td>\s*?<td>(.*?)<\/td>\s*?<td>(.*?)<\/td>\s*?<td>(.+?)<\/td>\s*?<td>(.*?)<\/td><\/tr>|s";


//$regex_pc = "|name=(.+?)&amp;z=|s";
$regex_pc = '|.*%2C\+(.+?)"target="_blank">|s';
$regex_gr = '|>(.*?)<|s';


$ftext = file_get_contents($bof_url);

$regex_result = preg_match_all($regex, $ftext, $result);

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
mysqli_select_db($conn, $dbdb); 

//print_r($result);

?>
<html>
<head>
<title>Map of British Orienteering Fixtures</title>
<link rel='stylesheet' type='text/css' href='style.css' />
<script type='text/javascript' src='https://lib.oomap.co.uk/openlayers/OpenLayers-2.13.1/OpenLayers.js'></script>
<script type='text/javascript' src='main.js'></script>
</head>
<body onLoad="init()">
<div id='map'></div>
<div id='title'><span id='titletext'>Map of Forthcoming Orienteering Races</span><br />
<span style="background-color: #ff0000">&nbsp;&nbsp;&nbsp;&nbsp;</span> this week, <span style="background-color: #ff8800">&nbsp;&nbsp;&nbsp;&nbsp;</span> next week, <span style="background-color: #ffff00">&nbsp;&nbsp;&nbsp;&nbsp;</span> week after, etc<br />
by <a href="https://oobrien.com/">Ollie O'Brien</a>.<span style='font-size: 9px;'> * Location calculated from postcode, low accuracy in rural areas. </span>
</div>
<div id='attribution'>Map data by <a href="https://osm.org/">OpenStreetMap</a> contributors</div>
<div id='status'>
<?php 
	$pc = "";
	$cfilter = "";
	$dfilter = "";
	$prfilter = "";
	
	if (isset($_GET['p'])) 
	{
		$pc = $_GET['p'];
	}
	if (isset($_GET['c'])) 
	{
		$cfilter = $_GET['c'];
	}
	if (isset($_GET['d'])) 
	{
		$dfilter = $_GET['d'];
	}
	if (isset($_GET['pr'])) 
	{
		$prfilter = $_GET['pr'];
	}
		
	$lmtime = filemtime($bof_url);
	$nowtime = time();
	if ($nowtime - $lmtime > 90000)
	{
		echo "<div style='color: yellow; background-color: red; font-weight: bold; text-decoration: blink;'>Last updated: " . date ("d M Y H:i", $lmtime) . " - update missed, please let me know at o.obrien (at) outlook.com</div>"; 
	}
	else
	{
		echo "Last updated: " . date ("d M Y H:i", $lmtime); 
	}
	
	if ($prfilter != "")
	{
		echo "<script type='text/javascript'>prfilter = true;</script>";
	}
	
?>
</div>
<form id="pcForm" method="GET">
<div id='search'>Home postcode <span id="pc_disp"></span><br />
Change 
<input type='hidden' id='p' name='p' value='' />
<input type='text' maxlength='8' size='8' id='p_user' />
<input type='submit' value='Go' onClick="return submitReturn();" /><br /><hr />
<input type='hidden' id='d' name='d' value='' />
<?php 
	echo "<input type='checkbox' id='d_user' onChange='submitGo();' "; 
	if ($dfilter == 'on') { echo 'checked="checked"'; } 
	echo " />"
?> 
Hide far events?<br />
<input type='hidden' id='c' name='c' value='' />
<?php
	$html_str = "<div id='tableDiv'>";
	$html_str .= "<table id='table'>";
	$html_str .= "<tr>";
	$html_str .= "<th>Date</th><th>Distance</th><th>Level</th><th>Club</th>";
	$html_str .= "<th>Event</th><th>Area</th><th>Location&nbsp;Maps</th></tr>";

	$mapjs_str = "";

	if ($cfilter == "EA") { $cfilter = "EAOA"; }

	if ($pc == "")
	{
		$pc = 'HA6';
	}

	$pc = strtoupper(str_replace(' ', '', $pc));
	$pc = str_replace('%20', '', $pc);
	if (!ctype_alnum($pc))
	{
		$pc = "???";
	}
	
	if (strlen($pc) > 7)
	{
		$pc = "???";
	}
	
	//Two-part postcode? - pad appropriately.
	if (strlen($pc) > 4)
	{
		if (strlen($pc) > 6)
		{
			$pc = $pc;		
		}
		else if (strlen($pc) > 5)
		{
			$pc = substr($pc, 0, -3) . " " . substr($pc, -3);		
		}
		else
		{
				$pc = substr($pc, 0, -3) . "  " . substr($pc, -3);
		}
	}
	
	$home_lat = 51.604;
	$home_lon = -0.418;
	if ($pc != "???" && $pc != "HA6")	
	{		
		if ($conn) 
		{
			$pc = mysqli_real_escape_string($conn, $pc);
			$pcns = str_replace(" ", "", $pc);
			$query = "select latitude, longitude from postcodes where postcode_ns like ('" . $pcns . "%') limit 1;"; 
			$sql_result = mysqli_query($conn, $query);
			if ($sql_result) 
			{		
				$row = mysqli_fetch_assoc($sql_result); 
				$home_lat = $row['latitude'];
				$home_lon = $row['longitude'];
			}
		}		
	}
	
	if ($home_lat < "49.77")
	{
		$home_lat = 51.502;
		$home_lon = -0.133;
		$pc = "???";
	}

	#$home_loc = new LatLng($home_lat, $home_lon);
	$crs = Geographic2D::fromSRID(Geographic2D::EPSG_WGS_84);
        $home_loc = GeographicPoint::create($crs, new Degree($home_lat), new Degree($home_lon), null);
        
	$mapjs_str .= "<script>var home_lon = $home_lon; var home_lat = $home_lat; document.getElementById('pc_disp').innerHTML = '$pc';</script>";

	$no_of_events = count($result[1]);
	$mapjs_str .= "<script type='text/javascript'>\nvar points = new Array();\n";

	$tr_alt = false;
	$curr_date_nice = "";

	$clubs = array();
	$regions = array();

	for ($i = 0; $i < $no_of_events; $i++)
	{
		$gr = "";
		$gr_orig = "";
		$pc = "";
		$gr_str = "";
		$frompc = false;
		$error = false;
		$lat = "";
		$lon = "";
		$debugtxt = "";
		
		//Process date.
		$date = date_create($result[3][$i] . '-' . $result[2][$i] . '-' . $result[1][$i]);   
		$date_nice_str = str_replace(' ','&nbsp;', date_format($date, "D j M Y"));
		$date_nice = '<td>' . $date_nice_str;
		$diff_sec = date_format($date, 'U') - time();
		$diff_sec_from_next_mon = date_format($date, 'U') - strtotime("next Monday");
		$diff_days = ceil(ceil($diff_sec_from_next_mon/86400)/7);

		if ($date_nice != $curr_date_nice)
		{
			$curr_date_nice = $date_nice;
			$tr_alt = !$tr_alt;
		}

		if ($diff_sec < 0)
		{
			$date_nice = '<td title="' . $date_nice_str . '" class="current">TODAY';
		}
		elseif ($diff_sec < 86400)
		{
			$date_nice = '<td title="' . $date_nice_str . '" class="current">Tomorrow';
		}

		//Don't show old days.
		if ($diff_sec < -86400)
		{
			continue;
		}
				
		//Don't show more than 8 months ahead.
		if ($diff_sec > 86400*31*8)
		{
			continue;
		}

		//Calc club.
		$club_full = $result[7][$i];
		$club = $club_full;
		$club_pos = strpos($club_full, '_blank">');
		if ($club_pos > 0)
		{
			$club = substr($club_full, $club_pos+8, -4);
		}
	
		if (!in_array($club, $clubs) && $club != "")
		{
			$clubs[] = $club;
		}

		$region = $result[8][$i];
		if (!in_array($region, $regions) && $region != "")
		{
			$regions[] = $region;
		}
	
		//Apply filter, if the club/region filter is on.
		if ($cfilter != "" && $cfilter != "All" && $cfilter != $club && $cfilter != $region && (($cfilter != "WMOA|EMOA" && $cfilter != "WMOA and EMOA") || ($region != 'West Midlands' && $region != 'East Midlands')))
		{
			continue;
		}

		if ($result[6][$i] == "Activity")
		{
			continue;
		}

		$distance = 999;
		$distance_str = "";		
	
		//Process ID.
		$bofid = $result[4][$i];
		//$id_pos = strpos($id_full, '&amp;z=');
		$mapfull = $result[9][$i];
		$mapname_pos = strpos($result[9][$i], '">');
		$id = "";
		
		$map_link = "";
		if (1==1)
		{
			$id = $i;

			//Process grid ref.
			$gr_str = $result[11][$i];
		
			$gr_str = str_replace('-', '', $gr_str);
			$gr_str = str_replace(' ', '', $gr_str);
			$gr_str = str_replace('%20', '', $gr_str);

			$gr_regex_result = preg_match($regex_gr, $gr_str, $gr_result);
			if ($gr_regex_result > 0)
			{
				$gr = $gr_result[1];
			}
			$gr_orig = $gr;			
			if ($gr == "" && strlen($gr_str) == 8)
			{
				$gr = $gr_str;
				$gr_orig = $gr;			
			}
			if (strlen($gr) == 8)
			{
				//NOOP
			}
			else
			{
				$pc_regex_result = preg_match($regex_pc, $gr_str, $pc_result);
				if ($pc_regex_result > 0)
				{
					$pc = $pc_result[1];
					$pc = str_replace('+', '', $pc);
	
					if (substr($pc, 0, 2) == "BT" || (strlen($pc) > 1 && strlen($pc) < 8 && ctype_alnum($pc) && ctype_alpha(substr($pc, 0, 1))))
					{
						// update `postcodes` set postcode_ns = replace(postcode_7, " ", "");
	
						// If the third digit is a zero and it's a seven digit postcode, remove the third digit. E.g. CR01AA is good, CR032AA is bad).
						if (strlen($pc) == 7 && substr($pc, 2, 1) == "0")
						{
							$pc = substr($pc, 0, 2) . substr($pc, 3);
							$error = "BADFMT";
						}
	
						$eventpc = mysqli_real_escape_string($conn, $pc);
						$query = "select easting, northing, latitude, longitude from postcodes where postcode_ns like ('" . $eventpc . "%') limit 1;"; 
						$sql_result = mysqli_query($conn, $query);
						if ($sql_result) 
						{		
							$row = mysqli_fetch_assoc($sql_result); 
							$lat = $row['latitude'];
							$lon = $row['longitude'];
							$easting = $row['easting'];
							$northing = $row['northing'];
							if (substr($eventpc, 0, 2) == "BT")
							{
								if ($row['latitude'])
								{
									$frompc = true;
									$gr = "";
								}
								else 
								{
									$error = "UNKNPC";
								}
							}
							else
							{
								if ($row['easting'])
								{
									$point = new BritishNationalGridPoint(new Metre($easting), new Metre($northing));
									$gr = $point->asGridReference(6);
									#$os1w = new OSRef($easting, $northing);
									#$gr = $os1w->toSixFigureString();
									$frompc = true;
								}
								else
								{
									$error = "UNKNPC";
								}
							}
						}
					}
				}
			}
				
			if (strlen($gr) == 0 || $error != false)
			{
				$gr = $gr_orig;
			}

			if (strlen($gr) == 8 && ctype_alnum($gr))
			{
				$error = false;
				$gr = strtoupper($gr);
				$map_link .="<a href='https://oobrien.com/go/google/$gr' target='_new'>Google</a>&nbsp;/&nbsp;"
						. "<a href='https://oobrien.com/go/os/$gr' target='_new'>Ordnance Survey</a>";
				if ($frompc)
				{
					$map_link .= " *";
				}
				#$os1w = getOSRefFromSixFigureReference($gr);
				$point = BritishNationalGridPoint::fromGridReference($gr);
				#$l1w = $os1w->toLatLng();
				#$l1w->OSGB36ToWGS84();
				$crs = Geographic2D::fromSRID(Geographic2D::EPSG_WGS_84);
				try 
				{
					$event_loc = $point->convert($crs);
		       			$event_lat = floatval((string) $event_loc->getLatitude());
        				$event_lon = floatval((string) $event_loc->getLongitude());
				}	
				catch (Exception $e) 
				{ 
					//echo "QQQ:" . $gr . $result[9][$i];
					$error = "BADGR";
					$map_link = "";
					$event_lat = 0;
					$event_lon = 0;
				}

				#$event_lat = $l1w->lat;
				#$event_lon = $l1w->lng;
							
				$distance = intval(0.001*($event_loc->calculateDistance($home_loc)->asMetres()->getValue()));
				$distance_str = $distance . " km";
			
				if ($dfilter == "on")
				{
					$dfilter = 100;
				}
				if (is_numeric($dfilter))
				{
					if ($result[6][$i] == "Local" && $distance > $dfilter)
					{
						continue;
					}
					if ($result[6][$i] == "Regional" && $distance > 2*$dfilter)
					{
						continue;
					}			
				}
						
				//Apply Park Race filter if necessary
				if ($prfilter != "" && !stristr($result[4][$i], "Park Race") && !stristr($result[4][$i], "Parkrace"))
				{
					continue;
				}
				
				if ($prfilter != "" && stristr($result[9][$i], "Nonsuch"))
				{
					continue;
				}
													
				$mapjs_str .= "points.push([$id, $event_lat, $event_lon, $diff_days]);\n";	
			}				
			else if (strlen($gr) == 0 && $lat != "")
			{
				$map_link .="<a href='https://www.google.ie/maps/@$lat,$lon,13z' target='_new'>Google</a> *";
				$event_lat = $lat;
				$event_lon = $lon;
		
				#$l1w = new LatLng($lat, $lon);
        			$crs = Geographic2D::fromSRID(Geographic2D::EPSG_WGS_84);
        			$event_loc = GeographicPoint::create($crs, new Degree($lat), new Degree($lon), null);


				$distance = intval(0.001*($event_loc->calculateDistance($home_loc)->asMetres()->getValue()));
				$distance_str = $distance . " km";
			
				if ($dfilter == "on")
				{
					$dfilter = 100;
				}
				if (is_numeric($dfilter))
				{
					if ($result[6][$i] == "Local" && $distance > $dfilter)
					{
						continue;
					}
					if ($result[6][$i] == "Regional" && $distance > 2*$dfilter)
					{
						continue;
					}			
				}
						
				//Apply Park Race filter if necessary
				if ($prfilter != "" && !stristr($result[4][$i], "Park Race") && !stristr($result[4][$i], "Parkrace"))
				{
					continue;
				}
				
				if ($prfilter != "" && stristr($result[9][$i], "Nonsuch"))
				{
					continue;
				}
													
				$mapjs_str .= "points.push([$id, $event_lat, $event_lon, $diff_days]);\n";	
			
			}
			
			if ($error)
			{
				$map_link .= " <span style='background-color: red; color: yellow; text-decoration: blink;'>$error</span>";
			}				
		}
				
		//Process closing date.
		$oc_date_str = "";
		$oc_date_nice = "<td>&nbsp;";
		//$closing_date_str = $result[9][$i];
		//$oc_date_pos = strpos($closing_date_str, 'O: ');
		//$pc_date_pos = strpos($closing_date_str, 'P: ');
		//if ($oc_date_pos > 0 && substr($closing_date_str, $oc_date_pos+9, 2) != '70')
		//{
		//	$oc_date_str = substr($closing_date_str, $oc_date_pos+3, 8);
		//}
		//elseif ($pc_date_pos > 0 && substr($closing_date_str, $pc_date_pos+9, 2) != '70')
		//{
		//	$oc_date_str = substr($closing_date_str, $pc_date_pos+3, 8);	
		//}		
		//if ($oc_date_str != "")
		//{
		//	$oc_date = date_create(substr($oc_date_str, 6, 2) . '-' . substr($oc_date_str, 3, 2) . '-' . substr($oc_date_str, 0, 2));
		//	$oc_date_nice_str = str_replace(' ','&nbsp;', date_format($oc_date, "D j M Y"));
		//	$oc_diff_sec = date_format($oc_date, 'U') - time();
		//	if ($oc_diff_sec < -86400) 
		//	{
		//		$oc_date_nice = '<td><s>' . $oc_date_nice_str . '</s>';
		//	}
		//	elseif ($oc_diff_sec < 0)
		//	{
		//		$oc_date_nice = '<td title="' . $oc_date_nice_str . '" class="imminent">TODAY';
		//	}
		//	elseif ($oc_diff_sec < 86400)
		//	{
		//		$oc_date_nice = '<td title="' . $oc_date_nice_str . '" class="imminent">Tomorrow';
		//	}
		//	elseif ($oc_diff_sec < 86400*13)
		//	{
		//		$days = floor($oc_diff_sec/86400);
		//		$oc_date_nice = '<td title="' . $oc_date_nice_str . '" class="soon">' . $days . ' days';
		//	}
		//
		//}
		
		//Process event name.				
		//Apply Park Race filter if necessary
		if ($prfilter != "" && !stristr($result[4][$i], "Park Race") && !stristr($result[4][$i], "Parkrace"))
		{
			continue;
		}
		if ($prfilter != "" && stristr($result[9][$i], "Nonsuch"))
		{
			continue;
		}


		
		//Process map name.
		$map_name = "";
		if ($mapname_pos > 0)
		{
			$map_name = substr($mapfull, $mapname_pos + 2, -4);
			if ($map_link != "")
			{
				$map_name = "<a href='#' onclick='selectPoint($id); return false;'>" . $map_name . "</a>";
			}
		}
		else if ($map_link != "")
		{
			$map_name = "<a href='#' onclick='selectPoint($id); return false;'>" . $mapfull . "</a>";
		
		}
		else
		{
			$map_name = $mapfull;
		}
		
		$alt = "";
		if ($tr_alt)
		{
			$alt = "alt ";
		}		

		$major = "";
		if ($result[6][$i] == "Level A")
		{
			$major = "major ";
		}

		$id_str = " id='event$id'";
		
		$like_link = "";
		$who_link = "";
		
		$html_str .= "<tr class='$alt$major' $id_str>";
		$html_str .= "" . $date_nice . "</td>" .
			  "<td style='text-align: right'>" . $distance_str . "</td>" .
			  "<td>" . $result[6][$i] . "</td>" .
			  "<td>" . $club_full . "</td>" .
			  "<td>" . $result[5][$i] . "</td>" .
			  "<td title='$gr'>" . $map_name . "</td>" .
			  "<td>" . $map_link . "</td>" .
/*			  "<td>" . $gr_orig . "</td>" .
			  "<td>" . $pc . "</td>" .
			  "<td>" . $gr . "</td>" .
			  "<td>" . $debugtxt . "</td>" . */
			  "</tr>";
		$html_str .= "\n";
	}

	$mapjs_str .= "addPoints();\n</script>\n";
	
	sort($clubs);
	sort($regions);
	$clubs = array_merge($clubs, $regions);
	
	echo "Club: <select id='c_user' onChange='submitGo();' style='width: 120px'>";
	echo "<option id='All'>All</option>";
	foreach ($clubs as $club)
	{
		if ($club == $cfilter)
		{
			echo "<option id='$club' selected='selected'>$club</option>";
		}
		else
		{
			echo "<option id='$club'>$club</option>";		
		}
	}
	echo "<option id='WMOA and EMOA'>WMOA and EMOA</option>";
	echo "</select></form></div>";
	
	echo $mapjs_str;
	echo $html_str;

	@mysqli_close($conn);

	$end = time();
	$diff = $end-$start;
?>
</table>
</div>


</body>
</html>
