/** * Get all groups after the specified one. * @param $groupId * @return array */ public function getAllGroups($groupId) { $sql = "SELECT groupid AS id\n FROM dbtrack_actions\n WHERE groupid >= :groupid\n GROUP BY groupid\n ORDER BY groupid DESC"; return $this->dbms->getResults($sql, array('groupid' => $groupId)); }
<?php require_once 'db_helper.php'; require_once 'common.php'; if (!isset($_GET['stop'])) { die("Expected get data of 'stop'."); } $database = new Database(); $stop = mysql_real_escape_string($_GET['stop']); $date = new DateTime("now", new DateTimeZone("America/New_York")); $time = $date->format('H:i:s'); if (isset($_GET['time'])) { $time = $_GET['time']; } $time = mysql_real_escape_string($time); $routes = $database->getResults("SELECT DISTINCT rv.id, r.marta_id, r.name, rv.direction, rs.id AS route_stop_id\n\t\tFROM routing_route AS r\n\t\tJOIN routing_route_variation AS rv ON r.id = rv.route_id\n\t\tJOIN routing_route_stop AS rs ON rv.id = rs.route_var_id\n\t\tJOIN routing_stop AS s ON rs.stop_id = s.id\n\t\tWHERE s.id = {$stop};"); foreach ($routes as $key => $route) { $new_time = $database->getResult("SELECT stop_time\n\t\t\tFROM routing_route_time\n\t\t\tWHERE route_stop_id = " . $route['route_stop_id'] . " \n\t\t\tAND stop_time >= '{$time}'\n\t\t\tORDER BY stop_time ASC\n\t\t\tLIMIT 1;"); if ($new_time == null) { $new_time = $database->getResult("SELECT stop_time\n\t\t\t\tFROM routing_route_time\n\t\t\t\tWHERE route_stop_id = " . $route['route_stop_id'] . " \n\t\t\t\tAND stop_time >= '00:00:00'\n\t\t\t\tORDER BY stop_time ASC\n\t\t\t\tLIMIT 1;"); if ($new_time == null) { unset($routes[$key]); continue; } } $routes[$key]['next_time'] = $new_time['stop_time']; unset($routes[$key]['route_stop_id']); } echo json_encode(array_values($routes));
<?php require_once 'db_helper.php'; require_once 'common.php'; if (!isset($_GET['latitude']) or !isset($_GET['longitude'])) { die("Expected get data of 'latitude' and 'longitude'."); } $database = new Database(); $lat = mysql_real_escape_string($_GET['latitude']); $long = mysql_real_escape_string($_GET['longitude']); $maxD = 1000; if (isset($_GET['radius'])) { $maxD = $_GET['radius']; } $maxD = mysql_real_escape_string($maxD); $stops = $database->getResults("SELECT id, name, latitude, longitude, (3959 * 1000 * 1.609344 * ACOS(COS(RADIANS({$lat})) * COS( RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS({$long})) + SIN(RADIANS({$lat})) * SIN(RADIANS(latitude)))) AS distance \n\t\tFROM routing_stop\n\t\tHAVING distance < {$maxD} \n\t\tORDER BY distance ASC;"); echo json_encode($stops);
// Format Array of {"id": #, "short_name": bus_route or line, "name": name} $bus_routes = json_decode(file_get_contents("http://spice.ridecell.com/get_routes/buses/"), true); $train_routes = json_decode(file_get_contents("http://spice.ridecell.com/get_routes/trains/"), true); foreach ($bus_routes as $value) { if (!$database->getResult("SELECT id FROM Route WHERE id='" . mysql_real_escape_string($value['id']) . "';")) { $route_vals = "(" . mysql_real_escape_string($value['id']) . ",'" . mysql_real_escape_string($value['short_name']) . "','" . mysql_real_escape_string($value['name']) . "','Bus')"; $database->getResultInserted("INSERT INTO Route\n\t\t\t\t(id, marta_id, route_name, type)\n\t\t\t\tVALUES {$route_vals};"); } } foreach ($train_routes as $value) { if (!$database->getResult("SELECT id FROM Route WHERE id='" . mysql_real_escape_string($value['id']) . "';")) { $route_vals = "(" . mysql_real_escape_string($value['id']) . ",'" . mysql_real_escape_string($value['short_name']) . "','" . mysql_real_escape_string($value['name']) . "','Train')"; $database->getResultInserted("INSERT INTO Route\n\t\t\t\t(id, marta_id, route_name, type)\n\t\t\t\tVALUES {$route_vals};"); } } $routes = $database->getResults("SELECT id FROM Route;"); $head_val = ''; foreach ($routes as $route) { $headsigns = json_decode(do_post_request("http://spice.ridecell.com/desktop/route_headsigns/", array("route_id" => $route['id'])), true); $headsigns = $headsigns[1]; foreach ($headsigns as $head) { if (!$database->getResult("SELECT * \n\t\t\t\tFROM Route_Variation \n\t\t\t\tWHERE route_id='" . mysql_real_escape_string($head['route_id']) . "' \n\t\t\t\tAND direction='" . mysql_real_escape_string($head['direction']) . "' \n\t\t\t\tAND variation_name='" . mysql_real_escape_string($head['headsign']) . "' \n\t\t\t\tAND route_shape_id='" . mysql_real_escape_string($head['shape_id']) . "';")) { $head_val .= "(" . mysql_real_escape_string($head['direction']) . ",'" . mysql_real_escape_string($head['headsign']) . "'," . mysql_real_escape_string($head['route_id']) . "," . mysql_real_escape_string($head['shape_id']) . "),"; } } } $head_val = rtrim($head_val, ','); $database->getResultInserted("INSERT INTO Route_Variation\n\t\t(direction, variation_name, route_id, route_shape_id)\n\t\tVALUES {$head_val};"); set_time_limit(240); $headsigns = $database->getResults("SELECT * FROM Route_Variation"); foreach ($headsigns as $head) {