diff --git a/README.md b/README.md
index 6e8d092..74c8fbb 100644
--- a/README.md
+++ b/README.md
@@ -1,11 +1,23 @@
Gps Tracker v4.0.3
-------------
-##### Google Map Gps Cell Phone Tracker
+##### Google Map Gps Cell Phone Tracker Server
-This project allows you to track cell phones periodically. For instance every minute or every five minutes. You can watch the cell phone being tracked in real time using google maps and you can store and reload routes easily. The map display page is built using bootstrap which makes the page responsive and also uses bootswatch which gives you the choice of 17 differeent themes. There are 4 clients, ios, android, windows phone and java me.
+This project allows you to track cell phones periodically. For instance every minute or every five minutes. You can watch the cell phone being tracked in real time using Google Maps and you can store and reload routes easily. The map display page is built using bootstrap which makes the page responsive and also uses bootswatch which gives you the choice of 17 different themes. There are 4 clients, iOS, Android, Windows Phone and Java ME.
-You have the choice of two server stacks. Either using asp.net and sql server or using php and mysql. Both are now in the same download but you only need to use one.
+You have the choice of two server stacks. You can choose one of:
+
+1. ASP.NET with SQL Server
+2. PHP with your choice of:
+ * MySQL
+ * PostgreSQL
+ * SQLite
+
+Both stacks are now in the same download but you only need to use one.
+
+By default the Tracker server is set up to use the included SQLite database. If you want to use one of the other supported database systems, edit the dbconnect.php file included with the Tracker Server.
+
+**Note:** This is only the server portion of the system. You will also need a client app running on your phone. Have a look at the Quick Start Guide in the link below for information on how to get the client apps.
If you need help, please go to:
diff --git a/servers/php/README.md b/servers/php/README.md
index 3dda108..ed7f001 100644
--- a/servers/php/README.md
+++ b/servers/php/README.md
@@ -1,4 +1,4 @@
-The database file is in the sqlserver or mysql directory and this needs to be restored, please read the quick start guide to see how to do that:
+If you are planning to use the default database, SQLite, the database is ready to go and no importing or configuring is necessary. If you want to use one of the other supported databases, the database import file is in the sqlserver, mysql, or PostgreSQL directory. This needs to be imported into your empty database. Please read the quick start guide to see how to do that:
https://www.websmithing.com/2014/01/20/quick-start-guide-for-gpstracker-3/#aspnetserver
diff --git a/servers/php/dbconnect.php b/servers/php/dbconnect.php
index 0261563..b64b175 100644
--- a/servers/php/dbconnect.php
+++ b/servers/php/dbconnect.php
@@ -1,10 +1,36 @@
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
-$pdo = new PDO('mysql:host=localhost;dbname=gpstracker;charset=utf8', $dbuser, $dbpass, $params);
-
+switch ($dbType) {
+ case DB_MYSQL:
+ $pdo = new PDO('mysql:host=localhost;dbname=gpstracker;charset=utf8', $dbuser, $dbpass, $params);
+ $sqlFunctionCallMethod = 'CALL ';
+ break;
+ case DB_POSTGRESQL:
+ $pdo = new PDO('pgsql:host=localhost;dbname=gpstracker', $dbuser, $dbpass, $params);
+ $sqlFunctionCallMethod = 'select ';
+ break;
+ case DB_SQLITE3:
+ $pdo = new PDO('sqlite:'.$pathToSQLite, $dbuser, $dbpass, $params);
+ $sqlFunctionCallMethod = 'select ';
+ break;
+}
?>
\ No newline at end of file
diff --git a/servers/php/deleteroute.php b/servers/php/deleteroute.php
index 2484af5..30a1718 100644
--- a/servers/php/deleteroute.php
+++ b/servers/php/deleteroute.php
@@ -2,8 +2,17 @@
include 'dbconnect.php';
$sessionid = isset($_GET['sessionid']) ? $_GET['sessionid'] : '0';
-
- $stmt = $pdo->prepare('CALL prcDeleteRoute(:sessionID)');
+
+ switch ($dbType) {
+ case DB_MYSQL:
+ $stmt = $pdo->prepare($sqlFunctionCallMethod.'prcDeleteRoute(:sessionID)');
+ break;
+ case DB_POSTGRESQL:
+ case DB_SQLITE3:
+ $stmt = $pdo->prepare('DELETE FROM gpslocations WHERE sessionID = :sessionID');
+ break;
+ }
+
$stmt->execute(array(':sessionID' => $sessionid));
?>
diff --git a/servers/php/getallroutesformap.php b/servers/php/getallroutesformap.php
index c3e58bb..101e780 100644
--- a/servers/php/getallroutesformap.php
+++ b/servers/php/getallroutesformap.php
@@ -1,8 +1,17 @@
prepare('CALL prcGetAllRoutesForMap();');
+
+ switch ($dbType) {
+ case DB_MYSQL:
+ $stmt = $pdo->prepare('CALL prcGetAllRoutesForMap();');
+ break;
+ case DB_POSTGRESQL:
+ case DB_SQLITE3:
+ $stmt = $pdo->prepare('select * from v_GetAllRoutesForMap;');
+ break;
+ }
+
$stmt->execute();
$json = '{ "locations": [';
diff --git a/servers/php/getrouteformap.php b/servers/php/getrouteformap.php
index 816b047..a7afcec 100644
--- a/servers/php/getrouteformap.php
+++ b/servers/php/getrouteformap.php
@@ -2,8 +2,17 @@
include 'dbconnect.php';
$sessionid = isset($_GET['sessionid']) ? $_GET['sessionid'] : '0';
-
- $stmt = $pdo->prepare('CALL prcGetRouteForMap(:sessionID)');
+
+ switch ($dbType) {
+ case DB_MYSQL:
+ $stmt = $pdo->prepare('CALL prcGetRouteForMap(:sessionID)');
+ break;
+ case DB_POSTGRESQL:
+ case DB_SQLITE3:
+ $stmt = $pdo->prepare("select * from v_GetRouteForMap where sessionID = :sessionID");
+ break;
+ }
+
$stmt->execute(array(':sessionID' => $sessionid));
$json = '{ "locations": [';
diff --git a/servers/php/getroutes.php b/servers/php/getroutes.php
index 4082b4a..59159cd 100644
--- a/servers/php/getroutes.php
+++ b/servers/php/getroutes.php
@@ -1,7 +1,16 @@
prepare('CALL prcGetRoutes();');
+ switch ($dbType) {
+ case DB_MYSQL:
+ $stmt = $pdo->prepare('CALL prcGetRoutes();');
+ break;
+ case DB_POSTGRESQL:
+ case DB_SQLITE3:
+ $stmt = $pdo->prepare('select * from v_GetRoutes;');
+ break;
+ }
+
$stmt->execute();
$json = '{ "routes": [';
diff --git a/servers/php/postgresql/README.md b/servers/php/postgresql/README.md
new file mode 100644
index 0000000..e06eb38
--- /dev/null
+++ b/servers/php/postgresql/README.md
@@ -0,0 +1,55 @@
+
+#### PHP and PostgreSQL
+
+Just like the MySQL version, using PostgreSQL with GpsTracker requires a web server, such as Apache, and PHP. Once you have your server software installed, you need to create a website on your Apache web server and create a directory called gpstracker. Put all of the files from the php download directory into there.
+
+Now install the PostgreSQL database server. You may want to install the GUI admin tool, pgAdmin III, but it is not required if you are comfortable using the command line.
+
+
+##### Creating the GpsTracker Database Using the PostgreSQL Command Line
+
+Open a command window, and change directory to Postgresql\bin:
+
+ cd "C:\Program Files\PostgreSQL\9.4\bin"
+
+
+Now create a database user for inserting and quering the data:
+
+ C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres -c "CREATE USER gpstracker_user WITH PASSWORD 'gpstracker';"
+ Password for user postgres:
+ CREATE ROLE
+
+ C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres -c "CREATE DATABASE gpstracker;"
+ Password for user postgres:
+ CREATE DATABASE
+
+ C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres -c "ALTER DATABASE gpstracker OWNER TO gpstracker_user;"
+ Password for user postgres:
+ ALTER DATABASE
+
+ C:\Program Files\PostgreSQL\9.4\bin>psql -U gpstracker_user -d gpstracker -f C:\Users\brent\Documents\gpstracker-09-14-14.sql
+ Password for user gpstracker_user:
+ psql:C:/Users/brent/Documents/gpstracker-09-14-14.sql:6: NOTICE: view "v_getallroutesformap" does not exist, skipping DROP VIEW
+ psql:C:/Users/brent/Documents/gpstracker-09-14-14.sql:7: NOTICE: view "v_getrouteformap" does not exist, skipping DROP VIEW
+ psql:C:/Users/brent/Documents/gpstracker-09-14-14.sql:8: NOTICE: view "v_getroutes" does not exist, skipping DROP VIEW
+ psql:C:/Users/brent/Documents/gpstracker-09-14-14.sql:9: NOTICE: index "sessionidindex" does not exist, skipping DROP INDEX
+ psql:C:/Users/brent/Documents/gpstracker-09-14-14.sql:10: NOTICE: index "phonenumberindex" does not exist, skipping DROP INDEX
+ psql:C:/Users/brent/Documents/gpstracker-09-14-14.sql:11: NOTICE: index "usernameindex" does not exist, skipping DROP INDEX
+ psql:C:/Users/brent/Documents/gpstracker-09-14-14.sql:13: NOTICE: table "gpslocations" does not exist, skipping DROP TABLE
+ CREATE TABLE
+ CREATE INDEX
+ CREATE INDEX
+ CREATE INDEX
+ INSERT 0 9
+ CREATE VIEW
+ CREATE VIEW
+ CREATE VIEW
+
+
+
+Now edit GpsTracker's PHP file "dbconnect.php: to set the database type to PostgreSQL:
+
+$dbType = DB_POSTGRESQL;
+
+
+That's it!
\ No newline at end of file
diff --git a/servers/php/postgresql/gpstracker-09-14-14.sql b/servers/php/postgresql/gpstracker-09-14-14.sql
new file mode 100644
index 0000000..b7442f7
--- /dev/null
+++ b/servers/php/postgresql/gpstracker-09-14-14.sql
@@ -0,0 +1,100 @@
+--------------------------------------------------------------
+-- SQL for creating GpsTracker object in a PostgreSQL database
+--------------------------------------------------------------
+-- CREATE USER gpstracker_user WITH PASSWORD 'gpstracker';
+
+DROP VIEW IF EXISTS v_GetAllRoutesForMap;
+DROP VIEW IF EXISTS v_GetRouteForMap;
+DROP VIEW IF EXISTS v_GetRoutes;
+DROP INDEX IF EXISTS sessionIDIndex;
+DROP INDEX IF EXISTS phoneNumberIndex;
+DROP INDEX IF EXISTS userNameIndex;
+
+DROP TABLE IF EXISTS gpslocations;
+
+CREATE TABLE gpslocations (
+ GPSLocationID serial,
+ lastUpdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ latitude double precision NOT NULL DEFAULT '0.0',
+ longitude double precision NOT NULL DEFAULT '0.0',
+ phoneNumber varchar(50) NOT NULL DEFAULT '',
+ userName varchar(50) NOT NULL DEFAULT '',
+ sessionID varchar(50) NOT NULL DEFAULT '',
+ speed integer NOT NULL DEFAULT '0',
+ direction integer NOT NULL DEFAULT '0',
+ distance double precision NOT NULL DEFAULT '0.0',
+ gpsTime timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+ locationMethod varchar(50) NOT NULL DEFAULT '',
+ accuracy integer NOT NULL DEFAULT '0',
+ extraInfo varchar(255) NOT NULL DEFAULT '',
+ eventType varchar(50) NOT NULL DEFAULT '',
+ PRIMARY KEY (GPSLocationID)
+);
+
+CREATE INDEX sessionIDIndex ON gpslocations (sessionID);
+CREATE INDEX phoneNumberIndex ON gpslocations (phoneNumber);
+CREATE INDEX userNameIndex ON gpslocations (userName);
+
+-----------------------------------------------
+-- Sample data for table gpslocations
+-----------------------------------------------
+
+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');
+
+
+-----------------------------------------------
+-- v_GetAllRoutesForMap --
+-----------------------------------------------
+CREATE OR REPLACE VIEW v_getallroutesformap AS
+ SELECT gpslocations.sessionid,
+ gpslocations.gpstime,
+ concat('{ "latitude":"', gpslocations.latitude::character varying, '", "longitude":"', gpslocations.longitude::character varying, '", "speed":"', gpslocations.speed::character varying, '", "direction":"', gpslocations.direction::character varying, '", "distance":"', gpslocations.distance::character varying, '", "locationMethod":"', gpslocations.locationmethod, '", "gpsTime":"', to_char(gpslocations.gpstime, 'Mon dd YYYY HH12:MIAM'::text), '", "userName":"', gpslocations.username, '", "phoneNumber":"', gpslocations.phonenumber, '", "sessionID":"', gpslocations.sessionid, '", "accuracy":"', gpslocations.accuracy::character varying, '", "extraInfo":"', gpslocations.extrainfo, '" }') AS json
+ FROM ( SELECT max(gpslocations_1.gpslocationid) AS id
+ FROM gpslocations gpslocations_1
+ WHERE gpslocations_1.sessionid::text <> '0'::text AND char_length(gpslocations_1.sessionid::text) <> 0 AND gpslocations_1.gpstime <> '0000-00-00 00:00:00'::timestamp without time zone
+ GROUP BY gpslocations_1.sessionid) maxid
+ JOIN gpslocations ON gpslocations.gpslocationid = maxid.id
+ ORDER BY gpslocations.gpstime;
+
+
+-----------------------------------------------
+-- v_GetRouteForMap --
+-----------------------------------------------
+CREATE Or REPLACE VIEW v_GetRouteForMap AS
+SELECT
+ sessionid,
+ lastupdate,
+ CONCAT('{ "latitude":"', CAST(latitude AS VARCHAR),'", "longitude":"', CAST(longitude AS VARCHAR), '", "speed":"', CAST(speed AS VARCHAR), '", "direction":"', CAST(direction AS VARCHAR), '", "distance":"', CAST(distance AS VARCHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', to_char(gpsTime, 'Mon dd YYYY HH12:MIAM'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS VARCHAR), '", "accuracy":"', CAST(accuracy AS VARCHAR), '", "extraInfo":"', extraInfo, '" }') json
+ FROM gpslocations
+ WHERE gpstime != '0000-00-00 00:00:00'
+ ORDER BY lastupdate;
+ ;
+
+-----------------------------------------------
+-- v_GetRoutes --
+-----------------------------------------------
+CREATE OR REPLACE VIEW v_GetRoutes AS
+select
+ CONCAT('{ "sessionID": "', CAST(sessionID AS VARCHAR), '", "userName": "', userName, '", "times": "(', to_char(startTime, 'Mon dd YYYY HH12:MIAM'), ' - ', to_char(endtime, 'Mon dd YYYY HH12:MIAM'), ')" }') json
+from (
+ select
+ distinct sessionid, userName,
+ MIN(gpsTime) startTime,
+ MAX(gpsTime) endtime
+ FROM gpslocations
+ group by sessionid,username
+ ORDER BY startTime DESC
+)
+AS routes
+;
+
+
diff --git a/servers/php/sqlite/gpstracker-09-14-14.sql b/servers/php/sqlite/gpstracker-09-14-14.sql
new file mode 100644
index 0000000..31a5866
--- /dev/null
+++ b/servers/php/sqlite/gpstracker-09-14-14.sql
@@ -0,0 +1,124 @@
+--------------------------------------------------------------
+-- SQL for creating GpsTracker objext in a PostgreSQL database
+--------------------------------------------------------------
+DROP VIEW IF EXISTS v_GetAllRoutesForMap;
+DROP VIEW IF EXISTS v_GetRouteForMap;
+DROP VIEW IF EXISTS v_GetRoutes;
+DROP INDEX IF EXISTS sessionIDIndex;
+DROP INDEX IF EXISTS phoneNumberIndex;
+DROP INDEX IF EXISTS userNameIndex;
+
+DROP TABLE IF EXISTS gpslocations;
+
+CREATE TABLE gpslocations (
+ GPSLocationID integer PRIMARY KEY autoincrement unique not null,
+ lastUpdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ latitude double precision NOT NULL DEFAULT '0.0',
+ longitude double precision NOT NULL DEFAULT '0.0',
+ phoneNumber varchar(50) NOT NULL DEFAULT '',
+ userName varchar(50) NOT NULL DEFAULT '',
+ sessionID varchar(50) NOT NULL DEFAULT '',
+ speed integer NOT NULL DEFAULT '0',
+ direction integer NOT NULL DEFAULT '0',
+ distance double precision NOT NULL DEFAULT '0.0',
+ gpsTime timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+ locationMethod varchar(50) NOT NULL DEFAULT '',
+ accuracy integer NOT NULL DEFAULT '0',
+ extraInfo varchar(255) NOT NULL DEFAULT '',
+ eventType varchar(50) NOT NULL DEFAULT ''
+);
+CREATE INDEX sessionIDIndex ON gpslocations (sessionID);
+CREATE INDEX phoneNumberIndex ON gpslocations (phoneNumber);
+CREATE INDEX userNameIndex ON gpslocations (userName);
+
+-----------------------------------------------
+-- Sample data for table gpslocations
+-----------------------------------------------
+
+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');
+
+
+-----------------------------------------------
+-- v_GetAllRoutesForMap --
+-----------------------------------------------
+CREATE VIEW v_GetAllRoutesForMap AS
+ SELECT
+ gpslocations.sessionid,
+ gpslocations.gpstime,
+ '{ "latitude":"' || CAST(latitude AS VARCHAR) ||
+ '", "longitude":"' || CAST(longitude AS VARCHAR) ||
+ '", "speed":"' || CAST(speed AS VARCHAR) ||
+ '", "direction":"' || CAST(direction AS VARCHAR) ||
+ '", "distance":"' || CAST(distance AS VARCHAR) ||
+ '", "locationMethod":"' || locationMethod ||
+ '", "gpsTime":"' || gpsTime ||
+ '", "userName":"' || userName ||
+ '", "phoneNumber":"' || phoneNumber ||
+ '", "sessionID":"' || CAST(sessionID AS VARCHAR) ||
+ '", "accuracy":"' || CAST(accuracy AS VARCHAR) ||
+ '", "extraInfo":"' || extraInfo || '" }' json
+ FROM ( SELECT max(gpslocations_1.gpslocationid) AS id
+ FROM gpslocations gpslocations_1
+ WHERE gpslocations_1.sessionid != '0' AND
+ length(gpslocations_1.sessionid) != 0 AND
+ gpslocations_1.gpstime != '0000-00-00 00:00:00'
+ GROUP BY gpslocations_1.sessionid) maxid
+ JOIN gpslocations ON gpslocations.gpslocationid = maxid.id
+ ORDER BY gpslocations.gpstime;
+
+
+
+-----------------------------------------------
+-- v_GetRouteForMap --
+-----------------------------------------------
+CREATE VIEW v_GetRouteForMap AS
+SELECT
+ sessionid,
+ lastupdate,
+ '{ "latitude":"' || CAST(latitude AS VARCHAR) ||
+ '", "longitude":"' || CAST(longitude AS VARCHAR) ||
+ '", "speed":"' || CAST(speed AS VARCHAR) ||
+ '", "direction":"' || CAST(direction AS VARCHAR) ||
+ '", "distance":"' || CAST(distance AS VARCHAR) ||
+ '", "locationMethod":"' || locationMethod ||
+ '", "gpsTime":"' || gpsTime ||
+ '", "userName":"' || userName ||
+ '", "phoneNumber":"' || phoneNumber ||
+ '", "sessionID":"' || CAST(sessionID AS VARCHAR) ||
+ '", "accuracy":"' || CAST(accuracy AS VARCHAR) ||
+ '", "extraInfo":"' || extraInfo || '" }' json
+ FROM gpslocations
+ WHERE gpstime != '0000-00-00 00:00:00'
+ ORDER BY lastupdate;
+ ;
+
+-----------------------------------------------
+-- v_GetRoutes --
+-----------------------------------------------
+CREATE VIEW v_GetRoutes AS
+select
+ '{ "sessionID": "'|| CAST(sessionID AS VARCHAR) ||
+ '", "userName": "' || userName ||
+ '", "times": "(' || startTime ||
+ ' - ' || endtime || ')" }' json
+from (
+ select
+ distinct sessionid, userName,
+ MIN(gpsTime) startTime,
+ MAX(gpsTime) endtime
+ FROM gpslocations
+ group by sessionid,username
+ ORDER BY startTime DESC
+)
+AS routes
+;
+
diff --git a/servers/php/sqlite/gpstracker.sqlite b/servers/php/sqlite/gpstracker.sqlite
new file mode 100644
index 0000000..2b15af5
--- /dev/null
+++ b/servers/php/sqlite/gpstracker.sqlite
Binary files differ
diff --git a/servers/php/updatelocation.php b/servers/php/updatelocation.php
index 8ce4b1c..b8d193c 100644
--- a/servers/php/updatelocation.php
+++ b/servers/php/updatelocation.php
@@ -40,7 +40,9 @@
':eventtype' => $eventtype
);
- $stmt = $pdo->prepare('CALL prcSaveGPSLocation(
+ switch ($dbType) {
+ case DB_MYSQL:
+ $stmt = $pdo->prepare( $sqlFunctionCallMethod.'prcSaveGPSLocation(
:latitude,
:longitude,
:speed,
@@ -55,7 +57,12 @@
:extrainfo,
:eventtype);'
);
-
+ break;
+ case DB_POSTGRESQL:
+ case DB_SQLITE3:
+ $stmt = $pdo->prepare('INSERT INTO gpslocations (latitude, longitude, speed, direction, distance, gpsTime, locationMethod, userName, phoneNumber, sessionID, accuracy, extraInfo, eventType) VALUES (:latitude, :longitude, :speed, :direction, :distance, :date, :locationmethod, :username, :phonenumber, :sessionid, :accuracy, :extrainfo, :eventtype)');
+ break;
+ }
$stmt->execute($params);
$timestamp = $stmt->fetchColumn();
echo $timestamp;