diff --git a/phoneClients/android/.idea/workspace.xml b/phoneClients/android/.idea/workspace.xml index 93aa7ac..dd24812 100644 --- a/phoneClients/android/.idea/workspace.xml +++ b/phoneClients/android/.idea/workspace.xml @@ -42,6 +42,8 @@ + + @@ -108,43 +110,29 @@ - - + + - - + + + + + + - + - - - - - - - - - - - - - - - - - - - - - - - + + + + + @@ -598,7 +586,7 @@ - @@ -1436,7 +1424,7 @@ - + @@ -1452,7 +1440,7 @@ - + @@ -1536,8 +1524,12 @@ - - + + + + + + @@ -1555,8 +1547,12 @@ - - + + + + + + @@ -1567,8 +1563,12 @@ - - + + + + + + @@ -1586,8 +1586,12 @@ - - + + + + + + @@ -1612,8 +1616,12 @@ - - + + + + + + @@ -1674,8 +1682,12 @@ - - + + + + + + @@ -1698,8 +1710,12 @@ - - + + + + + + @@ -1722,8 +1738,12 @@ - - + + + + + + @@ -1746,8 +1766,12 @@ - - + + + + + + @@ -1919,14 +1943,6 @@ - - - - - - - - @@ -1939,39 +1955,53 @@ - + - - + + - - + + - - + + + + + + + + + + + + + + + + + + + + + + - - - - - - - - - - - + + + + + diff --git a/phoneClients/android/app/build.gradle b/phoneClients/android/app/build.gradle index 0a81aa8..095b333 100644 --- a/phoneClients/android/app/build.gradle +++ b/phoneClients/android/app/build.gradle @@ -8,8 +8,8 @@ applicationId "com.websmithing.gpstracker" minSdkVersion 10 targetSdkVersion 20 - versionCode 6 - versionName "3.2.6" + versionCode 8 + versionName "3.2.8" } buildTypes { release { diff --git a/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/GpsTrackerActivity.java b/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/GpsTrackerActivity.java index 703f14c..45762d2 100644 --- a/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/GpsTrackerActivity.java +++ b/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/GpsTrackerActivity.java @@ -111,11 +111,18 @@ private void cancelAlarmManager() { Log.d(TAG, "cancelAlarmManager"); - +/* if (alarmManager != null) { alarmManager.cancel(pendingIntent); alarmManager = null; } +*/ + + Context context = getBaseContext(); + Intent gpsTrackerIntent = new Intent(context, GpsTrackerAlarmReceiver.class); + PendingIntent pendingIntent = PendingIntent.getBroadcast(context, 0, gpsTrackerIntent, 0); + AlarmManager alarmManager = (AlarmManager)context.getSystemService(Context.ALARM_SERVICE); + alarmManager.cancel(pendingIntent); } // called when trackingButton is tapped diff --git a/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/LocationService.java b/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/LocationService.java index 53d5ec2..a88078e 100644 --- a/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/LocationService.java +++ b/phoneClients/android/app/src/main/java/com/websmithing/gpstracker/LocationService.java @@ -121,8 +121,8 @@ requestParams.put("distance", 0); // in miles } - // phoneNumber is just an identifying string in the database, can be any identifier. - requestParams.put("phonenumber", sharedPreferences.getString("userName", "")); + requestParams.put("username", sharedPreferences.getString("userName", "")); + requestParams.put("phonenumber", "867-5309"); // not being used but is fully functional requestParams.put("sessionid", sharedPreferences.getString("sessionID", "")); // uuid Double accuracyInFeet = location.getAccuracy()* 3.28; @@ -141,7 +141,7 @@ LoopjHttpClient.get(uploadWebsite, requestParams, new AsyncHttpResponseHandler() { @Override public void onSuccess(int statusCode, org.apache.http.Header[] headers, byte[] responseBody) { - LoopjHttpClient.debugLoopJ(TAG, "sendLocationDataToWebsite - success", uploadWebsite, requestParams, responseBody, headers, statusCode, null); + // LoopjHttpClient.debugLoopJ(TAG, "sendLocationDataToWebsite - success", uploadWebsite, requestParams, responseBody, headers, statusCode, null); stopSelf(); } @Override diff --git a/servers/php/dbconnect.php b/servers/php/dbconnect.php index b88f346..5935373 100644 --- a/servers/php/dbconnect.php +++ b/servers/php/dbconnect.php @@ -3,8 +3,8 @@ $dbuser = 'gpstracker_user'; $dbpass = 'gpstracker'; $params = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, - PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM); + PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC); -$pdo = new PDO('mysql:host=localhost;dbname=gpstracker;charset=utf8', $dbuser, $dbpass, $params); +$pdo = new PDO('mysql:host=localhost;dbname=gpstracker2;charset=utf8', $dbuser, $dbpass, $params); ?> \ No newline at end of file diff --git a/servers/php/getallroutesformap.php b/servers/php/getallroutesformap.php index ef30fe6..c3e58bb 100644 --- a/servers/php/getallroutesformap.php +++ b/servers/php/getallroutesformap.php @@ -8,7 +8,7 @@ $json = '{ "locations": ['; foreach ($stmt as $row) { - $json .= $row[2]; + $json .= $row['json']; $json .= ','; } diff --git a/servers/php/getrouteformap.php b/servers/php/getrouteformap.php index dee74ae..816b047 100644 --- a/servers/php/getrouteformap.php +++ b/servers/php/getrouteformap.php @@ -9,7 +9,7 @@ $json = '{ "locations": ['; foreach ($stmt as $row) { - $json .= $row[0]; + $json .= $row['json']; $json .= ','; } diff --git a/servers/php/getroutes.php b/servers/php/getroutes.php index 2eb8460..4082b4a 100644 --- a/servers/php/getroutes.php +++ b/servers/php/getroutes.php @@ -7,7 +7,7 @@ $json = '{ "routes": ['; foreach ($stmt as $row) { - $json .= $row[0]; + $json .= $row['json']; $json .= ','; } diff --git a/servers/php/js/maps.js b/servers/php/js/maps.js index 6fe74b2..d8ac163 100644 --- a/servers/php/js/maps.js +++ b/servers/php/js/maps.js @@ -99,7 +99,7 @@ sessionIDArray.push($(this).attr('sessionID')); - option.innerHTML = $(this).attr('phoneNumber') + " " + $(this).attr('times'); + option.innerHTML = $(this).attr('userName') + " " + $(this).attr('times'); routeSelect.appendChild(option); }); @@ -204,7 +204,7 @@ $(this).attr('distance'), $(this).attr('locationMethod'), $(this).attr('gpsTime'), - $(this).attr('phoneNumber'), + $(this).attr('userName'), $(this).attr('sessionID'), $(this).attr('accuracy'), $(this).attr('extraInfo'), @@ -223,7 +223,7 @@ } function createMarker(latitude, longitude, sessionID, speed, direction, distance, locationMethod, gpsTime, - phoneNumber, sessionID, accuracy, extraInfo, map, finalLocation) { + userName, sessionID, accuracy, extraInfo, map, finalLocation) { var iconUrl; if (finalLocation) { @@ -258,12 +258,12 @@ "Speed: " + speed + " mph" + "Distance: " + distance + " mi " + "Time: " + gpsTime + "" + - "Name: " + phoneNumber + " " + + "Name: " + userName + " " + "Accuracy: " + accuracy + " ft "; var gpstrackerMarker; - var title = phoneNumber + " - " + gpsTime + var title = userName + " - " + gpsTime // make sure the final red marker always displays on top if (finalLocation) { @@ -279,6 +279,8 @@ gpstrackerMarker.on("click", function() { var url = 'getrouteformap.php?sessionid=' + sessionID; + console.log(url); + viewingAllRoutes = false; var indexOfRouteInRouteSelectDropdwon = sessionIDArray.indexOf(sessionID) + 1; @@ -390,8 +392,8 @@ if (hasMap()) { // comment out these two lines to get delete working - // var answer = confirm("Disabled here on test website, this works fine."); - // return false; + var answer = confirm("Disabled here on test website, this works fine."); + return false; var answer = confirm("This will permanently delete this route\n from the database. Do you want to delete?"); if (answer){ diff --git a/servers/php/mysql/gpstracker-09-12-14.sql b/servers/php/mysql/gpstracker-09-12-14.sql deleted file mode 100644 index b9ddec2..0000000 --- a/servers/php/mysql/gpstracker-09-12-14.sql +++ /dev/null @@ -1,220 +0,0 @@ --- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (i686) --- --- Host: localhost Database: gpstracker --- ------------------------------------------------------ --- Server version 5.5.38-0ubuntu0.12.04.1 - -/*!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 '', - `userName` 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`), - KEY `userNameIndex` (`userName`) -) ENGINE=InnoDB AUTO_INCREMENT=10 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 (1,'2007-01-03 19:37:00',47.627327,-122.325691,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:37:00','na',137,'na','gpsTracker'),(2,'2007-01-03 19:38:00',47.607258,-122.330077,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:38:00','na',137,'na','gpsTracker'),(3,'2007-01-03 19:39:00',47.601703,-122.324670,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:39:00','na',137,'na','gpsTracker'),(4,'0000-00-00 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'),(5,'2007-01-03 19:41:00',47.601397,-122.190353,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:41:00','na',137,'na','gpsTracker'),(6,'2007-01-03 19:42:00',47.610020,-122.190697,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:42:00','na',137,'na','gpsTracker'),(7,'2007-01-03 19:43:00',47.636631,-122.214558,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:43:00','na',137,'na','gpsTracker'),(8,'2007-01-03 19:44:00',47.637961,-122.201769,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:44:00','na',137,'na','gpsTracker'),(9,'2007-01-03 19:45:00',47.642935,-122.209579,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:45:00','na',137,'na','gpsTracker'); -/*!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)) -BEGIN - DELETE FROM gpslocations - WHERE sessionID = _sessionID; -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 `prcGetAllRoutesForMap` */; -/*!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 `prcGetAllRoutesForMap`() -BEGIN - SELECT DISTINCT(sessionId), MAX(gpsTime) gpsTime, - 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 != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00' - GROUP BY sessionID; -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)) -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 - 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=`gpstracker_user`@`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 `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); - - SELECT NOW(); -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-09-12 18:15:37 diff --git a/servers/php/mysql/gpstracker-09-14-14.sql b/servers/php/mysql/gpstracker-09-14-14.sql new file mode 100644 index 0000000..ad5cefe --- /dev/null +++ b/servers/php/mysql/gpstracker-09-14-14.sql @@ -0,0 +1,219 @@ +-- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (i686) +-- +-- Host: localhost Database: gpstracker +-- ------------------------------------------------------ +-- Server version 5.5.38-0ubuntu0.12.04.1 + +/*!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 '', + `userName` 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`), + KEY `userNameIndex` (`userName`) +) ENGINE=InnoDB AUTO_INCREMENT=10 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 (1,'2007-01-03 19:37:00',47.627327,-122.325691,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:37:00','na',137,'na','gpsTracker'),(2,'2007-01-03 19:38:00',47.607258,-122.330077,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:38:00','na',137,'na','gpsTracker'),(3,'2007-01-03 19:39:00',47.601703,-122.324670,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:39:00','na',137,'na','gpsTracker'),(4,'0000-00-00 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'),(5,'2007-01-03 19:41:00',47.601397,-122.190353,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:41:00','na',137,'na','gpsTracker'),(6,'2007-01-03 19:42:00',47.610020,-122.190697,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:42:00','na',137,'na','gpsTracker'),(7,'2007-01-03 19:43:00',47.636631,-122.214558,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:43:00','na',137,'na','gpsTracker'),(8,'2007-01-03 19:44:00',47.637961,-122.201769,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:44:00','na',137,'na','gpsTracker'),(9,'2007-01-03 19:45:00',47.642935,-122.209579,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:45:00','na',137,'na','gpsTracker'); +/*!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)) +BEGIN + DELETE FROM gpslocations + WHERE sessionID = _sessionID; +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 `prcGetAllRoutesForMap` */; +/*!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 `prcGetAllRoutesForMap`() +BEGIN + SELECT DISTINCT(sessionId), MAX(gpsTime) gpsTime, + 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'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json + FROM gpslocations + WHERE sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00' + GROUP BY sessionID; +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)) +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'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json + FROM gpslocations + WHERE sessionID = _sessionID + 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), + userName VARCHAR(50), + startTime DATETIME, + endTime DATETIME) + ENGINE = MEMORY; + + INSERT INTO tempRoutes (sessionID, userName) + SELECT DISTINCT sessionID, userName + FROM gpslocations; + + UPDATE tempRoutes tr + SET startTime = (SELECT MIN(gpsTime) FROM gpslocations gl + WHERE gl.sessionID = tr.sessionID + AND gl.userName = tr.userName); + + UPDATE tempRoutes tr + SET endTime = (SELECT MAX(gpsTime) FROM gpslocations gl + WHERE gl.sessionID = tr.sessionID + AND gl.userName = tr.userName); + + SELECT + + CONCAT('{ "sessionID": "', CAST(sessionID AS CHAR), '", "userName": "', userName, '", "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 `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=`root`@`localhost` PROCEDURE `prcSaveGPSLocation`( +_lat VARCHAR(15), +_lng VARCHAR(15), +_mph VARCHAR(15), +_direction VARCHAR(15), +_distance VARCHAR(15), +_date VARCHAR(50), +_locationMethod VARCHAR(100), +_userName VARCHAR(50), +_phoneNumber VARCHAR(50), +_sessionID VARCHAR(50), +_accuracy VARCHAR(15), +_extraInfo VARCHAR(255), +_eventType VARCHAR(50) +) +BEGIN + INSERT INTO gpslocations (latitude, longitude, speed, direction, distance, gpsTime, locationMethod, userName, phoneNumber, sessionID, accuracy, extraInfo, eventType) + VALUES (_lat, _lng, _mph, _direction, _distance, _date, _locationMethod, _userName, _phoneNumber, _sessionID, _accuracy, _extraInfo, _eventType); + SELECT NOW(); +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-09-14 18:38:51 diff --git a/servers/php/updatelocation.php b/servers/php/updatelocation.php index 94ae203..c513a70 100644 --- a/servers/php/updatelocation.php +++ b/servers/php/updatelocation.php @@ -1,20 +1,21 @@ $latitude, @@ -24,6 +25,7 @@ ':distance' => $distance, ':date' => $date, ':locationmethod' => $locationmethod, + ':username' => $username, ':phonenumber' => $phonenumber, ':sessionid' => $sessionid, ':accuracy' => $accuracy, @@ -38,7 +40,8 @@ :direction, :distance, :date, - :locationmethod, + :locationmethod, + :username, :phonenumber, :sessionid, :accuracy, @@ -47,5 +50,6 @@ ); $stmt->execute($params); - echo '0'; + $timestamp = $stmt->fetchColumn(); + echo $timestamp; ?>