diff --git a/servers/php/mysql/gpstracker-03-14-14.sql b/servers/php/mysql/gpstracker-03-14-14.sql deleted file mode 100644 index d7fb926..0000000 --- a/servers/php/mysql/gpstracker-03-14-14.sql +++ /dev/null @@ -1,238 +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=1186 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 (45,'2014-03-03 13:22:10',47.475931,-122.021119,'iosUser','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2014-03-03 13:22:08','n/a',65,'altitude: 120m','ios'),(114,'2014-03-06 20:21:22',37.785834,-122.406417,'iosUser','9E260B81-57D5-4C36-AED0-34C5F7676A45',0,0,0.0,'2014-03-07 17:21:14','n/a',5,'altitude: 0m','ios'),(121,'2014-03-07 09:16:19',-41.322089,174.771919,'iosUser','09287DC1-D8B2-43F4-885A-9EB8DB0A7CE9',0,0,0.0,'2014-03-08 06:16:19','n/a',65,'altitude: 31m','ios'),(122,'2014-03-07 09:17:59',-41.322139,174.771739,'iosUser','25DE2DB6-FFCE-462F-A22D-25994B0D901A',0,0,0.0,'2014-03-08 06:18:01','n/a',50,'altitude: 29m','ios'),(135,'2014-03-07 21:54:22',37.818813,22.661872,'androidUser','94e63b1f-aa6f-439a-83d8-b57fabb28355',0,0,0.0,'2014-03-08 07:54:19','fused',2276,'0.0','android'),(136,'2014-03-07 21:54:34',37.818813,22.661872,'androidUser','712db5d9-d495-44d4-b2e7-3bd177ebf169',0,0,0.0,'2014-03-08 07:54:35','fused',2276,'0.0','android'),(179,'2014-03-08 11:24:15',37.818813,22.661872,'User-4427','5e1d94a0-c3fb-47b8-9846-4eedb7f12375',0,0,0.0,'2014-03-08 21:24:15','fused',2276,'0.0','android'),(192,'2014-03-08 12:24:12',51.922687,4.500411,'X-androidUser','2b9a3194-491b-448f-bcef-89cc8299d68b',0,0,0.0,'2014-03-08 21:24:08','fused',25,'0.0','android'),(200,'2014-03-08 12:47:38',51.922655,4.500412,'ELAWONEN','722ab5cc-9cd1-439b-abfa-5f82a7e119a1',0,0,0.0,'2014-03-08 21:47:33','fused',26,'0.0','android'),(212,'2014-03-09 15:44:55',12.968171,77.533864,'androidUser','98464f77-a247-4048-a9b2-3d1e4191cb9c',0,0,0.0,'2014-03-10 04:14:50','fused',25,'0.0','android'),(213,'2014-03-09 15:45:05',12.968171,77.533864,'androidUser','98464f77-a247-4048-a9b2-3d1e4191cb9c',0,0,0.0,'2014-03-10 04:15:02','fused',25,'0.0','android'),(214,'2014-03-09 15:45:17',12.968171,77.533864,'androidUser','98464f77-a247-4048-a9b2-3d1e4191cb9c',0,0,0.0,'2014-03-10 04:15:14','fused',25,'0.0','android'),(215,'2014-03-09 15:45:23',12.968171,77.533864,'androidUser','98464f77-a247-4048-a9b2-3d1e4191cb9c',0,0,0.0,'2014-03-10 04:15:20','fused',25,'0.0','android'),(216,'2014-03-09 15:45:26',12.968104,77.533922,'androidUser','5fe474b4-8c3f-44c9-bf34-ebd517c340ef',0,0,0.0,'2014-03-10 04:15:23','fused',14,'0.0','android'),(217,'2014-03-09 15:45:32',12.968100,77.533911,'androidUser','5fe474b4-8c3f-44c9-bf34-ebd517c340ef',0,0,0.0,'2014-03-10 04:15:29','fused',12,'0.0','android'),(218,'2014-03-09 15:45:44',12.968134,77.533898,'androidUser','5fe474b4-8c3f-44c9-bf34-ebd517c340ef',0,0,0.0,'2014-03-10 04:15:41','fused',10,'0.0','android'),(1131,'2014-03-10 20:36:48',12.967535,77.532635,'andy','677332d9-e137-4767-b3fa-bf5c121509f7',0,0,0.0,'2014-03-11 09:06:43','fused',834,'0.0','android'),(1144,'2014-03-11 18:38:29',12.890422,77.578681,'androidUser','68c85b1c-18e2-49de-9286-0afc4396af2c',0,0,0.0,'2014-03-12 07:08:27','fused',20,'0.0','android'),(1145,'2014-03-11 18:42:04',12.890421,77.578679,'androidUser','31d5e433-fec7-40e6-85ea-9445551079dd',0,0,0.0,'2014-03-12 07:12:02','fused',29,'0.0','android'),(1156,'2014-03-12 16:52:43',12.890422,77.578682,'androidUser','46693e80-7411-4f06-aec4-1edc76585c08',0,0,0.0,'2014-03-13 05:22:41','fused',27,'0.0','android'),(1159,'2014-03-12 17:58:07',12.890422,77.578679,'GalaxyUser989','01606372-4610-41c2-b5c0-d4c7ce638738',0,0,0.0,'2014-03-13 06:28:05','fused',28,'0.0','android'),(1167,'2014-03-13 07:14:42',17.398090,78.571595,'androidUser','09c8fe0a-be70-4dad-9e6a-feb4148aade3',0,0,0.0,'2014-03-13 19:44:38','fused',1073,'0.0','android'),(1168,'2014-03-13 07:27:57',17.398090,78.571595,'androidUser','016c6409-0d7f-46f3-b1aa-4bc16092c43b',0,0,0.0,'2014-03-13 19:57:54','fused',1073,'0.0','android'),(1173,'2014-03-13 08:01:54',17.398090,78.571595,'androidUser','3bbeecb7-c869-48b0-a161-8475c5bc18cf',0,0,0.0,'2014-03-13 20:31:50','fused',1073,'0.0','android'),(1174,'2014-03-13 08:02:54',17.398090,78.571595,'androidUser','3bbeecb7-c869-48b0-a161-8475c5bc18cf',0,0,0.0,'2014-03-13 20:32:50','fused',1073,'0.0','android'),(1175,'2014-03-13 08:04:30',17.398090,78.571595,'androidUser','8ac5faa3-ba48-4e93-a6d0-e8a8b63514d6',0,0,0.0,'2014-03-13 20:34:26','fused',1073,'0.0','android'); -/*!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-03-13 11:42:37 diff --git a/servers/php/mysql/gpstracker-09-12-14.sql b/servers/php/mysql/gpstracker-09-12-14.sql new file mode 100644 index 0000000..b7cd427 --- /dev/null +++ b/servers/php/mysql/gpstracker-09-12-14.sql @@ -0,0 +1,218 @@ +-- 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); +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