<?php
// Utility sample for Aspicore GSM Tracker
// Store data into MySQL database and track a friend
//
// This sample demonstrates, how Aspicore GSM Tracker sends location data to the server via HTTP.
//
// Usage: Set "Internet page URL" in Aspicore GSM Tracker settings to point to this page.
// You see the output in your phone in the Info tab every time the phone sends
// some location data to the server via a HTTP connection.
//
// If you have entered an IMEI code of your friends phone
// into the TRACK_PHONE field in the PHONE table,
// the script calculates your friend's distance to your current location.
//
// Note: For simplicity, this script stores GPS data only.
// You can amend it to store also the cell tower info.
//
// (c) Aspicore Ltd 2005, www.aspicore.com
//
// Tested with PHP 5.0.3, Windows XP, IIS 5.1, MySQL 4.1.10a-nt and with Windows Server 2003, IIS 6.0
// The mysqli extension of PHP requires version 4.1.3 or above of MySQL
//
// NB. If you are using PHP with IIS, set output_buffering = On in php.ini
// In Windows, remember to enable extension=php_mysqli.dll in php.ini
// make
also sure, that php_mysqli.dll is at location php.ini/extension_dir
// and that libmySQL.dll is along the system path
// In Windows Server 2003, allow the required dll files in the Web Service Extensions tab
// ------------------------------
// Change History:
// 2005-04-06 jje - SQL script created
header("Expires: 0");
header("Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
header("Content-Type: text/plain; charset=ISO-8859-1");
header('Content-Disposition: inline; filename="response.txt"');
// ------------------------------
// SUBROUTINES
/* Adapted from the code by Sklar David, "Calculate the great circle distance between two latitude/longitudes"
http://www.weberdev.com/get_example.php3?count=357&mode=text
*/
function great_circle_distance($lat1, $lon1, $lat2, $lon2)
{
/* assume your points, in decimal, are in $lon1,$lat1 and $lon2,$lat2
*/
$pi = 3.1415926;
$rad = floatval($pi/180.0);
$lon1 = floatval($lon1)*$rad; $lat1 = floatval($lat1)*$rad;
$lon2 = floatval($lon2)*$rad; $lat2 = floatval($lat2)*$rad;
$theta = $lon2 - $lon1;
$dist = acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($theta));
if ($dist < 0) {
$dist += $pi;
}
$dist = $dist * 6371.2; // kilometers
/*
$miles = floatval($dist * 0.621);
$inches = floatval($miles*63360);
*/
return $dist;
}
/* Adapted from great_circle_distance with help of "Matti Suorsan Kotisivusto"
"Pasaatituulentie" / "6. Navigointi" / "ISOYMPYRÄPURJEHDUS"
http://koti.welho.com/msuorsa3/pasaati/navi06.htm
*/
function great_circle_bearing($lat1, $lon1, $lat2, $lon2)
{
/* assume your points, in decimal, are in $lon1,$lat1 and $lon2,$lat2
*/
$pi = 3.1415926;
$rad = floatval($pi/180.0);
$lon1 = floatval($lon1)*$rad; $lat1 = floatval($lat1)*$rad;
$lon2 = floatval($lon2)*$rad; $lat2 = floatval($lat2)*$rad;
if (($lat1 == 0) && ($lat2 == 0)) { // avoid dividing by zero
$c = $pi/2.0 * ( $lon2 > $lon1 );
}
else {
$theta = $lon2 - $lon1;
$c = atan(sin($theta) / ( cos($lat1) * tan($lat2) - sin($lat1) * cos($theta)));
}
if ($c < 0) {
$c += $pi;
}
if ($lon2 < $lon1) {
$c += $pi;
}
$course_deg = $c / $rad;
if ((abs($course_deg) < 0.000001) && ($lat2 < $lat1)) {
$course_deg = 180;
}
$course_deg = intval($course_deg);
return $course_deg;
}
function print_distance_and_direction($lat1, $lon1, $lat2, $lon2)
{
/* Diagnostics data:
printf("lat1 %s\r\n", $lat1);
printf("lon1 %s\r\n", $lon1);
printf("lat2 %s\r\n", $lat2);
printf("lon2 %s\r\n", $lon2);
*/
$distance_km = sprintf( "%.2f",great_circle_distance($lat1, $lon1, $lat2, $lon2));
printf("distance %s km\r\n", $distance_km);
$bearing_deg = great_circle_bearing($lat1, $lon1, $lat2, $lon2);
printf("bearing %d deg\r\n", $bearing_deg);
if (($bearing_deg > 337) || ($bearing_deg < 23))
$direction = "N";
else if ($bearing_deg < 68)
$direction = "NE";
else if ($bearing_deg < 113)
$direction = "E";
else if ($bearing_deg < 158)
$direction = "SE";
else if ($bearing_deg < 203)
$direction = "S";
else if ($bearing_deg < 248)
$direction = "SW";
else if ($bearing_deg < 293)
$direction = "W";
else if ($bearing_deg < 338)
$direction = "NW";
else
$direction = "unknown";
printf("direction %s\r\n", $direction);
}
// ------------------------------
// MAIN PROGRAM
// Show time and arguments in the response to the HTTP client
$timestamp = gmdate("D, d M Y H:i:s");
echo $timestamp;
echo "\r\n" ;
// print_r($_GET);
// echo "\r\n" ;
//------------------------
// Store new data into MySQL database
// (Create database 'gsmtrack' first with script GSM_Tracker_MySQL_tab.sql)
//------------------------
// Connecting, selecting database
// ***********************************************************************
// mysqli_connect parameters: host, username, passwd, dbname
$link = mysqli_connect("localhost", "root", "newpwd", "gsmtrack");
// ***********************************************************************
/* check connection */
if (!$link) {
printf("Connect failed: %s\r\n", mysqli_connect_error());
exit();
}
//echo 'Connected successfully';
/* Prepare an insert statement */
$query = "INSERT INTO GPS (PHONE,STATUS,LATITUDE,LONGITUDE,SPEED_KNOTS,COURSE_DEG,UTCTIME,UTCDATE,LABEL,REMOTE_IP)"
. " VALUES (?,?,?,?,?,?,?,?,?,?)";
$stmt = mysqli_prepare($link, $query);
/* check prepare result */
if (!$stmt) {
/* Show ERROR INFORMATION to the HTTP client */
printf("FATAL SERVER ERROR - SQL Prepare failed: %s\r\n", mysqli_error($link));
printf("Errorcode: %d\r\n", mysqli_errno($link));
printf("%s\r\n", mysqli_info($link));
printf("Check that your database structure matches GSM_Tracker_MySQL_tab.sql\r\n");
printf("Check access privileges (SQL GRANT statement).\r\n");
printf("\r\nTried to prepare the following SQL statement for execution:\r\n");
printf("$query\r\n");
printf("\r\nYour MySQL Server version is %s\r\n", mysqli_get_server_info($link));
printf("Your PHP version is %s\r\n", PHP_VERSION);
mysqli_close($link);
exit();
}
if (array_key_exists('REMOTE_ADDR', $_SERVER))
$remoteip = $_SERVER["REMOTE_ADDR"];
else
$remoteip = NULL;
mysqli_stmt_bind_param($stmt, 'ssddddssss',
$_GET['imei'], $_GET['status'], $_GET['lat'], $_GET['lon'],
$_GET['speed'],$_GET['course'], $_GET['time'], $_GET['date'],
$_GET['label'],$remoteip);
/* Execute the statement */
mysqli_stmt_execute($stmt);
/* close statement */
mysqli_stmt_close($stmt);
echo "Row added into DB\r\n";
echo "\r\n";
//------------------------
/* Check that the given IMEI code is valid before using it in SQL */
$imei = $_GET['imei'];
if(!preg_match("/^\d{15}$/",$imei,$matches)) {
/* Show error information to the HTTP client */
printf("You gave an illegal IMEI code as an URL argument\r\n");
printf("The IMEI code must be exactly 15 digits.\r\n");
printf("The current imei argument is: %s.\r\n", $imei);
mysqli_close($link);
exit();
}
//------------------------
/* Check, if a phone to be tracked has been defined */
$query = 'SELECT TRACK_PHONE FROM PHONE '
. 'WHERE PHONE = ' . $imei
. ' LIMIT 1';
$result = mysqli_query($link, $query);
if (!$result) {
printf("Query failed: %s\r\n", mysqli_error($link));
printf("Tried to execute the following SQL query:\r\n");
printf("%s\r\n", $query );
mysqli_close($link);
exit();
}
/* determine number of rows result set */
$row_cnt = mysqli_num_rows($result);
$trackphone = NULL;
if ($row_cnt === 0) {
echo "No other phone to track\r\n";
echo "(Missing row in PHONE table)\r\n";
}
else {
/* fetch associative array */
while ($row = mysqli_fetch_row($result)) {
if ($row[0] === NULL) {
echo "No other phone to track\r\n";
echo "(TRACK_PHONE value not set)\r\n";
}
else
$trackphone = $row[0];
}
}
// Free resultset
mysqli_free_result($result);
if ($trackphone === NULL) {
/* close connection */
mysqli_close($link);
exit();
}
//------------------------
/* Check that the TRACK_PHONE value is valid before using it in SQL */
if(!preg_match("/^\d{15}$/",$trackphone,$matches)) {
/* Show error information to the HTTP client */
printf("Illegal TRACK_PHONE value\r\n");
printf("Must be exactly 15 digits.\r\n");
printf("Current value: %s.\r\n", $trackphone);
mysqli_close($link);
exit();
}
//------------------------
/* Get description for the tracked phone */
$query = 'SELECT DESCR1, DESCR2 FROM PHONE '
. 'WHERE PHONE = ' . $trackphone
. ' LIMIT 1';
$result = mysqli_query($link, $query);
if (!$result) {
printf("Query failed: %s\r\n", mysqli_error($link));
printf("Tried to execute the following SQL query:\r\n");
printf("%s\r\n", $query );
mysqli_close($link);
exit();
}
$descr1 = NULL;
$descr2 = NULL;
while ($line = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
$descr1 = $line['DESCR1'];
$descr2 = $line['DESCR2'];
}
// Free resultset
mysqli_free_result($result);
//------------------------
/* Get last position of the tracked phone */
$query = 'SELECT GPSMSGID, TIME_RECEIVED, STATUS, LATITUDE, LONGITUDE, SPEED_KNOTS, COURSE_DEG, '
. 'UTCTIME, UTCDATE, LABEL, REMOTE_IP FROM GPS '
. 'WHERE PHONE = ' . $trackphone
. ' ORDER BY GPSMSGID DESC LIMIT 1';
$result = mysqli_query($link, $query);
if (!$result) {
printf("Query failed: %s\r\n", mysqli_error($link));
printf("Tried to execute the following SQL query:\r\n");
printf("%s\r\n", $query );
mysqli_close($link);
exit();
}
if (($descr1 === NULL) && ($descr2 === NULL))
$tracked_target = $trackphone;
else {
$tracked_target = "";
if ($descr1 !== NULL)
$tracked_target .= $descr1;
if (($descr1 !== NULL) && ($descr2 !== NULL))
$tracked_target .= " ";
if ($descr2 !== NULL)
$tracked_target .= $descr2;
}
printf("Tracking %s:\r\n", $tracked_target );
/* determine number of rows result set */
$row_cnt = mysqli_num_rows($result);
if ($row_cnt === 0) {
printf("No data.\r\n");
}
else {
$mylat = $_GET['lat'];
$mylong = $_GET['lon'];
while ($line = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
$targetlat = $line['LATITUDE'];
$targetlong = $line['LONGITUDE'];
print_distance_and_direction($mylat, $mylong, $targetlat, $targetlong);
printf("\r\nUsed DB data:\r\n" );
foreach ($line as $col_value) {
echo "$col_value\r\n";
}
}
}
// Free resultset
mysqli_free_result($result);
//------------------------
/* close connection */
mysqli_close($link);
//------------------------
?>