From f65cf9406397ac688ec4060ba070e172af0cd90b Mon Sep 17 00:00:00 2001
From: Patrick Davison <snapwilliam@gmail.com>
Date: Thu, 14 Feb 2013 00:42:29 -0800
Subject: Lots'n Lots of work on the scoreboard...

---
 ajax/maps.ajax.php   |  14 ++++---
 ajax/scores.ajax.php | 108 ++++++++++++++++++++++++++++++++++++++++++++++-----
 2 files changed, 106 insertions(+), 16 deletions(-)

(limited to 'ajax')

diff --git a/ajax/maps.ajax.php b/ajax/maps.ajax.php
index d2ae750..a5d72f2 100644
--- a/ajax/maps.ajax.php
+++ b/ajax/maps.ajax.php
@@ -1,20 +1,22 @@
 <?
+ob_start("ob_gzhandler");
 
 //CACHE HEADERS
 include('../includes/mapclass.php');
 include('../includes/maps.php');
 
 //RETURN MAP w/ CACHE HEADERS
-$mapID = $_GET['mapid'] * 1;
+$tmp = explode(".", $_GET['mapid']);
+$mapID = $tmp[0] * 1;
 if (!is_int($mapID)) exit;
 
 $map = new map(getMapCode($mapID), $mapID);
 
-// 62 days
-// $expires = 62*24*60*60
-// header("Pragma: public");
-// header("Cache-Control: maxage=".$expires);
-// header('Expires: ' . gmdate('D, d M Y H:i:s', time()+$expires) . ' GMT');
+//62 days
+$expires = 62*24*60*60;
+header("Cache-Control: public, maxage=".$expires);
+header('Expires: ' . gmdate('D, d M Y H:i:s', time()+$expires) . ' GMT');
+header("Content-type: text/javascript");
 echo json_encode($map);
 
 ?>
\ No newline at end of file
diff --git a/ajax/scores.ajax.php b/ajax/scores.ajax.php
index b95d22e..7ba25d0 100644
--- a/ajax/scores.ajax.php
+++ b/ajax/scores.ajax.php
@@ -8,6 +8,7 @@ $userID = $_SESSION['userID'];
 require('../includes/maps.php');
 require('../includes/mapclass.php');
 require('../includes/datas.php');
+include_once('../includes/sqli.php');
 
 if ($_GET['getmap'] == 'true') {
 	$mapID = $_GET['mapID'];
@@ -28,17 +29,105 @@ if ($_GET['getmaplist'] == 'true') {
 	echo getMapNavigation($todaysScoreMaps);
 }
 
+if (isset($_GET['getmapidsbydate'])) {
+	//TODO: SANATIZE
+	$requestDate = $_GET['getmapidsbydate'];
+	echo json_encode(getMapIDsByDate($requestDate));
+}
+
+// Here is that SQL for you
+// SELECT correctMapIds.mapType, maps.ID, maps.code
+// FROM maps
+// INNER JOIN
+// (
+       // SELECT MIN(mapOfTheDay.mapId) AS mapId, mapOfTheDay.mapType
+       // FROM `mapOfTheDay`
+       // INNER JOIN
+       // (
+               // SELECT mapType, MIN(mapExpireTime) AS mapExpireTime
+               // FROM mapOfTheDay
+               // WHERE mapExpireTime > DATE_ADD(CURDATE(), INTERVAL -1 DAY)
+               // GROUP BY mapType
+       // ) AS expireTimes ON mapOfTheDay.mapType = expireTimes.mapType
+                                       // AND mapOfTheDay.mapExpireTime = expireTimes.mapExpireTime
+       // GROUP BY mapOfTheDay.mapType
+// ) AS correctMapIds ON maps.ID = correctMapIds.mapId
+
+//TODO: SANITIZE.
+function getMapIDsByDate($date) {
+	global $mysqli;
+$sql = "SELECT MIN(mapOfTheDay.mapId) AS mapId
+FROM `mapOfTheDay`
+INNER JOIN
+(
+	SELECT mapType, MIN(mapExpireTime) AS mapExpireTime
+	FROM mapOfTheDay
+	WHERE mapExpireTime > ?
+	GROUP BY mapType
+) AS expireTimes ON mapOfTheDay.mapType = expireTimes.mapType
+						AND mapOfTheDay.mapExpireTime = expireTimes.mapExpireTime
+GROUP BY mapOfTheDay.mapType";
+
+$stmt = $mysqli->prepare($sql);
+$stmt->bind_param("s", $date);
+$stmt->execute();
+
+    $stmt->bind_result($mapID);
+    /* fetch values */
+    while ($stmt->fetch()) {
+        $ids[] = $mapID;
+    }
+
+	 return $ids;
+
+var_dump($ids);
+
+
+while ($myrow[] = $result->fetch_assoc()) {}
+
+	$result = mysql_query($sql) or die(mysql_error());
+	if (mysql_num_rows($result) == 0) return -1;
+	global $mapNamesByType;
+	$r = array();
+	while($data[] = mysql_fetch_array($result)) {
+		$mapID = $data['mapID'];
+		$mapType = $data['mapType'];
+		$map = new map($code);
+		if ($map->name == '')
+			$map->name = $mapNamesByType[$mapType];
+		$r[$mapID] = $map;
+	}
+	return $myrow;
+}
 
 	
 function getMapsPlayed($daysAgo) {
-	$sql = "
-	SELECT `mapID`, `code`, `mapType`
-	FROM `mapOfTheDay`
-	INNER JOIN `maps` ON `mapID` = maps.ID
-	WHERE DATE_ADD(CURDATE(), INTERVAL -$daysAgo DAY) =    
-	mapDate AND
-	`mapType` IN (1, 2, 3, 4)
-	";
+	// $sql = "
+	// SELECT `mapID`, `code`, `mapType`
+	// FROM `mapOfTheDay`
+	// INNER JOIN `maps` ON `mapID` = maps.ID
+	// WHERE DATE_ADD(CURDATE(), INTERVAL -$daysAgo DAY) =    
+	// mapDate AND
+	// `mapType` IN (1, 2, 3, 4, 5)
+	// ";
+	
+	$sql = "SELECT correctMapIds.mapType, maps.ID as mapID, maps.code
+FROM maps
+INNER JOIN
+(
+       SELECT MIN(mapOfTheDay.mapId) AS mapId, mapOfTheDay.mapType
+       FROM `mapOfTheDay`
+       INNER JOIN
+       (
+               SELECT mapType, MIN(mapExpireTime) AS mapExpireTime
+               FROM mapOfTheDay
+               WHERE mapExpireTime > DATE_ADD(CURDATE(), INTERVAL -$daysAgo DAY)
+               GROUP BY mapType
+       ) AS expireTimes ON mapOfTheDay.mapType = expireTimes.mapType
+                                       AND mapOfTheDay.mapExpireTime = expireTimes.mapExpireTime
+       GROUP BY mapOfTheDay.mapType
+) AS correctMapIds ON maps.ID = correctMapIds.mapId";
+	
 	$result = mysql_query($sql) or die(mysql_error());
 	if (mysql_num_rows($result) == 0)
 		return -1;
@@ -49,8 +138,7 @@ function getMapsPlayed($daysAgo) {
 		$mapID = $data['mapID'];
 		$mapType = $data['mapType'];
 		$map = new map($code);
-		if ($map->name == '')
-			$map->name = $mapNamesByType[$mapType];
+		if ($map->name == '') $map->name = $mapNamesByType[$mapType];
 		$r[$mapID] = $map;
 
 	}
-- 
cgit v1.2.3