diff --git a/servers/php/dbconnect.php b/servers/php/dbconnect.php index 967cd7d..fd9c8e0 100644 --- a/servers/php/dbconnect.php +++ b/servers/php/dbconnect.php @@ -3,7 +3,7 @@ $dbhost = 'localhost'; $dbuser = 'gpstracker_user'; $dbpass = 'gpstracker'; -$dbname = 'gpstracker'; +$dbname = 'gpstracker2'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); diff --git a/servers/php/displaymap.php b/servers/php/displaymap.php index 70307d9..5503568 100644 --- a/servers/php/displaymap.php +++ b/servers/php/displaymap.php @@ -4,8 +4,8 @@ Google Map GPS Cell Phone Tracker - - + + @@ -44,10 +44,16 @@ $.ajax({ url: 'getroutes.php', type: 'GET', - dataType: 'xml', + dataType: 'json', success: function(data) { + //console.log("success loadRoutes"); loadRoutes(data); - } + }, + error: function (xhr, status, errorThrown) { + console.log("responseText: " + xhr.responseText); + console.log("status: " + xhr.status); + console.log("errorThrown: " + errorThrown); + } }); } //]]> @@ -94,10 +100,7 @@

Please note that routes in the dropdown box are a concatenation of phoneNumber (ie. androidUser) and the first five characters of the sessionID. Start times and end times for the routes are in parentheses. Routes will be deleted after 3 days, there were getting to be to many.
 
- The routes in the dropdown box are sorted in descending order by startTime so your route should be near the top. -
 
- Feb 12, 2014 - + The routes in the dropdown box are sorted in descending order by startTime so your route should be near the top.

diff --git a/servers/php/getgpslocations.php b/servers/php/getgpslocations.php index e096c17..388086b 100644 --- a/servers/php/getgpslocations.php +++ b/servers/php/getgpslocations.php @@ -7,28 +7,30 @@ $query = 'CALL prcGetRouteForMap(\'' . $sessionID . '\',\'' . $phoneNumber . '\')'; - $xml = ''; + $json = '{ "locations": ['; // execute query if ($mysqli->multi_query($query)) { - do { + do { // build our json array if ($result = $mysqli->store_result()) { while ($row = $result->fetch_row()) { - $xml .= $row[0]; + $json .= $row[0]; + $json .= ','; } $result->close(); } } while ($mysqli->next_result()); } else { - die('$mysqli->multi_query: ' . $mysqli->error); + die('error: ' . $mysqli->error); } - $xml .= ''; + $json = rtrim($json, ","); + $json .= '] }'; - header('Content-Type: text/xml'); - echo $xml; + header('Content-Type: application/json'); + echo $json; $mysqli->close(); ?> \ No newline at end of file diff --git a/servers/php/getroutes.php b/servers/php/getroutes.php index 20955aa..1907ce0 100644 --- a/servers/php/getroutes.php +++ b/servers/php/getroutes.php @@ -1,30 +1,32 @@ '; + $json = '{ "routes": ['; // execute query if ($mysqli->multi_query($query)) { - do { + do { // build our json array if ($result = $mysqli->store_result()) { while ($row = $result->fetch_row()) { - $xml .= $row[0]; + $json .= $row[0]; + $json .= ','; } $result->close(); } } while ($mysqli->next_result()); } else { - die('$mysqli->multi_query: ' . $mysqli->error); + die('error: ' . $mysqli->error); } + + $json = rtrim($json, ","); + $json .= '] }'; - $xml .= ''; - - header('Content-Type: text/xml'); - echo $xml; + header('Content-Type: application/json'); + echo $json; $mysqli->close(); ?> diff --git a/servers/php/javascript/maps.js b/servers/php/javascript/maps.js index 1a5a70b..42917a4 100644 --- a/servers/php/javascript/maps.js +++ b/servers/php/javascript/maps.js @@ -1,6 +1,6 @@  -function loadRoutes(xml) { - if (xml.length == 0) { +function loadRoutes(json) { + if (json.length == 0) { showMessage('There are no routes available to view.'); map.innerHTML = ''; } @@ -12,7 +12,7 @@ routeSelect.appendChild(option); // iterate through the routes and load them into the dropdwon box. - $(xml).find('route').each(function(){ + $(json.routes).each(function(key, value){ var option = document.createElement('option'); option.setAttribute('value', '?sessionID=' + $(this).attr('sessionID') + '&phoneNumber=' + $(this).attr('phoneNumber')); @@ -36,15 +36,21 @@ showWait('Getting map...'); var url = 'getgpslocations.php' + routeSelect.options[routeSelect.selectedIndex].value; - //alert("testing route: " + routeSelect.options[routeSelect.selectedIndex].value); + //console.log("testing route: " + routeSelect.options[routeSelect.selectedIndex].value); $.ajax({ url: url, type: 'GET', - dataType: 'xml', + dataType: 'json', success: function(data) { + //console.log("success getRouteForMap"); loadGPSLocations(data); - } + }, + error: function (xhr, status, errorThrown) { + console.log("responseText: " + xhr.responseText); + console.log("status: " + xhr.status); + console.log("errorThrown: " + errorThrown); + } }); } else { @@ -62,8 +68,8 @@ } } -function loadGPSLocations(xml) { - if (xml.length == 0) { +function loadGPSLocations(json) { + if (json.length == 0) { showMessage('There is no tracking data to view.'); map.innerHTML = ''; } @@ -96,17 +102,19 @@ }; var adUnit = new google.maps.adsense.AdUnit(adUnitDiv, adUnitOptions); - var finalLocation = false; - + var finalLocation = false; + var counter = 0; + // iterate through the locations and create map markers for each location - $(xml).find('locations').each(function(){ - + $(json.locations).each(function(key, value){ + counter++; + // want to set the map center on the last location - if ($(this).is(':last-child')) { + if (counter == $(json.locations).length) { map.setCenter(new google.maps.LatLng($(this).attr('latitude'),$(this).attr('longitude'))); finalLocation = true; } - + var marker = createMarker( $(this).attr('latitude'), $(this).attr('longitude'), diff --git a/servers/php/mysql/gpstracker-02-12-14.sql b/servers/php/mysql/gpstracker-02-12-14.sql deleted file mode 100644 index 422ec6e..0000000 --- a/servers/php/mysql/gpstracker-02-12-14.sql +++ /dev/null @@ -1,250 +0,0 @@ --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) --- --- Host: localhost Database: gpstracker --- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.12.04.2 - -/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; -/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; -/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; -/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; -/*!40103 SET TIME_ZONE='+00:00' */; -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; - --- --- Table structure for table `gpslocations` --- - -DROP TABLE IF EXISTS `gpslocations`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `gpslocations` ( - `GPSLocationID` int(10) unsigned NOT NULL AUTO_INCREMENT, - `LastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `Latitude` decimal(10,6) NOT NULL DEFAULT '0.000000', - `Longitude` decimal(10,6) NOT NULL DEFAULT '0.000000', - `phoneNumber` varchar(50) NOT NULL DEFAULT '', - `sessionID` varchar(50) NOT NULL DEFAULT '', - `speed` int(10) unsigned NOT NULL DEFAULT '0', - `direction` int(10) unsigned NOT NULL DEFAULT '0', - `distance` decimal(10,1) NOT NULL DEFAULT '0.0', - `gpsTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `LocationMethod` varchar(50) NOT NULL DEFAULT '', - `accuracy` int(10) unsigned NOT NULL DEFAULT '0', - `extraInfo` varchar(255) NOT NULL DEFAULT '', - `eventType` varchar(50) NOT NULL DEFAULT '', - PRIMARY KEY (`GPSLocationID`), - KEY `sessionIDIndex` (`sessionID`), - KEY `phoneNumberIndex` (`phoneNumber`) -) ENGINE=InnoDB AUTO_INCREMENT=2312 DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Dumping data for table `gpslocations` --- - -LOCK TABLES `gpslocations` WRITE; -/*!40000 ALTER TABLE `gpslocations` DISABLE KEYS */; -INSERT INTO `gpslocations` VALUES (3,'2014-01-08 11:52:25',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(4,'2014-01-08 11:52:54',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(5,'2014-01-08 11:53:01',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(6,'2014-01-08 11:53:09',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(7,'2014-01-08 12:03:29',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'); -/*!40000 ALTER TABLE `gpslocations` ENABLE KEYS */; -UNLOCK TABLES; - --- --- Dumping routines for database 'gpstracker' --- -/*!50003 DROP PROCEDURE IF EXISTS `prcDeleteRoute` */; -/*!50003 SET @saved_cs_client = @@character_set_client */ ; -/*!50003 SET @saved_cs_results = @@character_set_results */ ; -/*!50003 SET @saved_col_connection = @@collation_connection */ ; -/*!50003 SET character_set_client = utf8 */ ; -/*!50003 SET character_set_results = utf8 */ ; -/*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; -DELIMITER ;; -CREATE DEFINER=`root`@`localhost` PROCEDURE `prcDeleteRoute`( -_sessionID VARCHAR(50), -_phoneNumber VARCHAR(50)) -BEGIN - DELETE FROM gpslocations - WHERE sessionID = _sessionID - AND phoneNumber = _phoneNumber - ORDER BY lastupdate; -END ;; -DELIMITER ; -/*!50003 SET sql_mode = @saved_sql_mode */ ; -/*!50003 SET character_set_client = @saved_cs_client */ ; -/*!50003 SET character_set_results = @saved_cs_results */ ; -/*!50003 SET collation_connection = @saved_col_connection */ ; -/*!50003 DROP PROCEDURE IF EXISTS `prcGetAllGpsLocations` */; -ALTER DATABASE `gpstracker` CHARACTER SET utf8 COLLATE utf8_general_ci ; -/*!50003 SET @saved_cs_client = @@character_set_client */ ; -/*!50003 SET @saved_cs_results = @@character_set_results */ ; -/*!50003 SET @saved_col_connection = @@collation_connection */ ; -/*!50003 SET character_set_client = utf8 */ ; -/*!50003 SET character_set_results = utf8 */ ; -/*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; -DELIMITER ;; -CREATE DEFINER=`gpstracker_user`@`localhost` PROCEDURE `prcGetAllGpsLocations`() -BEGIN - -SELECT * FROM gpslocations; - -END ;; -DELIMITER ; -/*!50003 SET sql_mode = @saved_sql_mode */ ; -/*!50003 SET character_set_client = @saved_cs_client */ ; -/*!50003 SET character_set_results = @saved_cs_results */ ; -/*!50003 SET collation_connection = @saved_col_connection */ ; -ALTER DATABASE `gpstracker` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; -/*!50003 DROP PROCEDURE IF EXISTS `prcGetRouteForMap` */; -ALTER DATABASE `gpstracker` CHARACTER SET utf8 COLLATE utf8_general_ci ; -/*!50003 SET @saved_cs_client = @@character_set_client */ ; -/*!50003 SET @saved_cs_results = @@character_set_results */ ; -/*!50003 SET @saved_col_connection = @@collation_connection */ ; -/*!50003 SET character_set_client = utf8 */ ; -/*!50003 SET character_set_results = utf8 */ ; -/*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; -DELIMITER ;; -CREATE DEFINER=`gpstracker_user`@`localhost` PROCEDURE `prcGetRouteForMap`( -_sessionID VARCHAR(50), -_phoneNumber VARCHAR(50)) -BEGIN - SELECT - CONCAT('') xml - FROM gpslocations - WHERE sessionID = _sessionID - AND phoneNumber = _phoneNumber - ORDER BY lastupdate; -END ;; -DELIMITER ; -/*!50003 SET sql_mode = @saved_sql_mode */ ; -/*!50003 SET character_set_client = @saved_cs_client */ ; -/*!50003 SET character_set_results = @saved_cs_results */ ; -/*!50003 SET collation_connection = @saved_col_connection */ ; -ALTER DATABASE `gpstracker` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; -/*!50003 DROP PROCEDURE IF EXISTS `prcGetRoutes` */; -/*!50003 SET @saved_cs_client = @@character_set_client */ ; -/*!50003 SET @saved_cs_results = @@character_set_results */ ; -/*!50003 SET @saved_col_connection = @@collation_connection */ ; -/*!50003 SET character_set_client = utf8 */ ; -/*!50003 SET character_set_results = utf8 */ ; -/*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; -DELIMITER ;; -CREATE DEFINER=`root`@`localhost` PROCEDURE `prcGetRoutes`() -BEGIN - CREATE TEMPORARY TABLE tempRoutes ( - sessionID VARCHAR(50), - phoneNumber VARCHAR(50), - startTime DATETIME, - endTime DATETIME) - ENGINE = MEMORY; - - INSERT INTO tempRoutes (sessionID, phoneNumber) - SELECT DISTINCT sessionID, phoneNumber - FROM gpslocations; - - UPDATE tempRoutes tr - SET startTime = (SELECT MIN(gpsTime) FROM gpslocations gl - WHERE gl.sessionID = tr.sessionID - AND gl.phoneNumber = tr.phoneNumber); - - UPDATE tempRoutes tr - SET endTime = (SELECT MAX(gpsTime) FROM gpslocations gl - WHERE gl.sessionID = tr.sessionID - AND gl.phoneNumber = tr.phoneNumber); - - SELECT - CONCAT('') - FROM tempRoutes - ORDER BY startTime DESC; - - DROP TABLE tempRoutes; -END ;; -DELIMITER ; -/*!50003 SET sql_mode = @saved_sql_mode */ ; -/*!50003 SET character_set_client = @saved_cs_client */ ; -/*!50003 SET character_set_results = @saved_cs_results */ ; -/*!50003 SET collation_connection = @saved_col_connection */ ; -/*!50003 DROP PROCEDURE IF EXISTS `prcGetUUID` */; -ALTER DATABASE `gpstracker` CHARACTER SET utf8 COLLATE utf8_general_ci ; -/*!50003 SET @saved_cs_client = @@character_set_client */ ; -/*!50003 SET @saved_cs_results = @@character_set_results */ ; -/*!50003 SET @saved_col_connection = @@collation_connection */ ; -/*!50003 SET character_set_client = utf8 */ ; -/*!50003 SET character_set_results = utf8 */ ; -/*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; -DELIMITER ;; -CREATE DEFINER=`gpstracker_user`@`localhost` PROCEDURE `prcGetUUID`() -BEGIN - SELECT UUID(); -END ;; -DELIMITER ; -/*!50003 SET sql_mode = @saved_sql_mode */ ; -/*!50003 SET character_set_client = @saved_cs_client */ ; -/*!50003 SET character_set_results = @saved_cs_results */ ; -/*!50003 SET collation_connection = @saved_col_connection */ ; -ALTER DATABASE `gpstracker` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; -/*!50003 DROP PROCEDURE IF EXISTS `prcSaveGPSLocation` */; -ALTER DATABASE `gpstracker` CHARACTER SET utf8 COLLATE utf8_general_ci ; -/*!50003 SET @saved_cs_client = @@character_set_client */ ; -/*!50003 SET @saved_cs_results = @@character_set_results */ ; -/*!50003 SET @saved_col_connection = @@collation_connection */ ; -/*!50003 SET character_set_client = utf8 */ ; -/*!50003 SET character_set_results = utf8 */ ; -/*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; -DELIMITER ;; -CREATE DEFINER=`gpstracker_user`@`localhost` PROCEDURE `prcSaveGPSLocation`( -_lat VARCHAR(45), -_lng VARCHAR(45), -_mph VARCHAR(45), -_direction VARCHAR(45), -_distance VARCHAR(45), -_date VARCHAR(100), -_locationMethod VARCHAR(100), -_phoneNumber VARCHAR(20), -_sessionID VARCHAR(50), -_accuracy VARCHAR(20), -_extraInfo VARCHAR(255), -_eventType VARCHAR(50) -) -BEGIN - INSERT INTO gpslocations (latitude, longitude, speed, direction, distance, gpsTime, locationMethod, phoneNumber, sessionID, accuracy, extraInfo, eventType) - VALUES (_lat, _lng, _mph, _direction, _distance, _date, _locationMethod, _phoneNumber, _sessionID, _accuracy, _extraInfo, _eventType); -END ;; -DELIMITER ; -/*!50003 SET sql_mode = @saved_sql_mode */ ; -/*!50003 SET character_set_client = @saved_cs_client */ ; -/*!50003 SET character_set_results = @saved_cs_results */ ; -/*!50003 SET collation_connection = @saved_col_connection */ ; -ALTER DATABASE `gpstracker` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; -/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; -/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; -/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; -/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; - --- Dump completed on 2014-02-13 3:53:13 diff --git a/servers/php/mysql/gpstracker-02-20-14.sql b/servers/php/mysql/gpstracker-02-20-14.sql new file mode 100644 index 0000000..4363f06 --- /dev/null +++ b/servers/php/mysql/gpstracker-02-20-14.sql @@ -0,0 +1,238 @@ +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: localhost Database: gpstracker +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.12.04.2 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `gpslocations` +-- + +DROP TABLE IF EXISTS `gpslocations`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `gpslocations` ( + `GPSLocationID` int(10) unsigned NOT NULL AUTO_INCREMENT, + `LastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `Latitude` decimal(10,6) NOT NULL DEFAULT '0.000000', + `Longitude` decimal(10,6) NOT NULL DEFAULT '0.000000', + `phoneNumber` varchar(50) NOT NULL DEFAULT '', + `sessionID` varchar(50) NOT NULL DEFAULT '', + `speed` int(10) unsigned NOT NULL DEFAULT '0', + `direction` int(10) unsigned NOT NULL DEFAULT '0', + `distance` decimal(10,1) NOT NULL DEFAULT '0.0', + `gpsTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `LocationMethod` varchar(50) NOT NULL DEFAULT '', + `accuracy` int(10) unsigned NOT NULL DEFAULT '0', + `extraInfo` varchar(255) NOT NULL DEFAULT '', + `eventType` varchar(50) NOT NULL DEFAULT '', + PRIMARY KEY (`GPSLocationID`), + KEY `sessionIDIndex` (`sessionID`), + KEY `phoneNumberIndex` (`phoneNumber`) +) ENGINE=InnoDB AUTO_INCREMENT=2312 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `gpslocations` +-- + +LOCK TABLES `gpslocations` WRITE; +/*!40000 ALTER TABLE `gpslocations` DISABLE KEYS */; +INSERT INTO `gpslocations` VALUES (3,'2014-01-08 11:52:25',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(4,'2014-01-08 11:52:54',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(5,'2014-01-08 11:53:01',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(6,'2014-01-08 11:53:09',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'),(7,'2014-01-08 12:03:29',48.856700,2.350800,'webUser','11137',137,0,25.0,'2007-10-17 18:37:00','0',95,'yes','webUser'); +/*!40000 ALTER TABLE `gpslocations` ENABLE KEYS */; +UNLOCK TABLES; + +-- +-- Dumping routines for database 'gpstracker' +-- +/*!50003 DROP PROCEDURE IF EXISTS `prcDeleteRoute` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`root`@`localhost` PROCEDURE `prcDeleteRoute`( +_sessionID VARCHAR(50), +_phoneNumber VARCHAR(50)) +BEGIN + DELETE FROM gpslocations + WHERE sessionID = _sessionID + AND phoneNumber = _phoneNumber + ORDER BY lastupdate; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PROCEDURE IF EXISTS `prcGetAllGpsLocations` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`gpstracker_user`@`localhost` PROCEDURE `prcGetAllGpsLocations`() +BEGIN + +SELECT * FROM gpslocations; + +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PROCEDURE IF EXISTS `prcGetRouteForMap` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`root`@`localhost` PROCEDURE `prcGetRouteForMap`( +_sessionID VARCHAR(50), +_phoneNumber VARCHAR(50)) +BEGIN + SELECT + CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json + FROM gpslocations + WHERE sessionID = _sessionID + AND phoneNumber = _phoneNumber + ORDER BY lastupdate; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PROCEDURE IF EXISTS `prcGetRoutes` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`root`@`localhost` PROCEDURE `prcGetRoutes`() +BEGIN + CREATE TEMPORARY TABLE tempRoutes ( + sessionID VARCHAR(50), + phoneNumber VARCHAR(50), + startTime DATETIME, + endTime DATETIME) + ENGINE = MEMORY; + + INSERT INTO tempRoutes (sessionID, phoneNumber) + SELECT DISTINCT sessionID, phoneNumber + FROM gpslocations; + + UPDATE tempRoutes tr + SET startTime = (SELECT MIN(gpsTime) FROM gpslocations gl + WHERE gl.sessionID = tr.sessionID + AND gl.phoneNumber = tr.phoneNumber); + + UPDATE tempRoutes tr + SET endTime = (SELECT MAX(gpsTime) FROM gpslocations gl + WHERE gl.sessionID = tr.sessionID + AND gl.phoneNumber = tr.phoneNumber); + + SELECT + + CONCAT('{ "sessionID": "', CAST(sessionID AS CHAR), '", "phoneNumber": "', phoneNumber, '", "times": "(', DATE_FORMAT(startTime, '%b %e %Y %h:%i%p'), ' - ', DATE_FORMAT(endTime, '%b %e %Y %h:%i%p'), ')" }') json + FROM tempRoutes + ORDER BY startTime DESC; + + DROP TABLE tempRoutes; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PROCEDURE IF EXISTS `prcGetUUID` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`gpstracker_user`@`localhost` PROCEDURE `prcGetUUID`() +BEGIN + SELECT UUID(); +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PROCEDURE IF EXISTS `prcSaveGPSLocation` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`gpstracker_user`@`localhost` PROCEDURE `prcSaveGPSLocation`( +_lat VARCHAR(45), +_lng VARCHAR(45), +_mph VARCHAR(45), +_direction VARCHAR(45), +_distance VARCHAR(45), +_date VARCHAR(100), +_locationMethod VARCHAR(100), +_phoneNumber VARCHAR(20), +_sessionID VARCHAR(50), +_accuracy VARCHAR(20), +_extraInfo VARCHAR(255), +_eventType VARCHAR(50) +) +BEGIN + INSERT INTO gpslocations (latitude, longitude, speed, direction, distance, gpsTime, locationMethod, phoneNumber, sessionID, accuracy, extraInfo, eventType) + VALUES (_lat, _lng, _mph, _direction, _distance, _date, _locationMethod, _phoneNumber, _sessionID, _accuracy, _extraInfo, _eventType); +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-02-20 17:19:29