The sample database script is for MS Sql Server 2005 but can be adjusted to MYSQL or other.
Thanks to Digit for this code: check his blog here
http://blog.hekkers.net/2013/08/13/btraced-gps-app-and-geo-fencing/
BTracedUpload.aspx
- Code: Select all
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="BTracedUpload.aspx.vb" Inherits="BTracedUpload" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
BTracedUpload.aspx.vb
- Code: Select all
Imports System.IO
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Globalization
Partial Class BTracedUpload
Inherits System.Web.UI.Page
Dim Debug As Boolean = True
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim xml, uplpoints, totpoints, Speed, Angle, travelname, travelid, traveltime, travellength, siphoneid, username, password, batt As String
Dim connstr As String = "Data Source=domoticaserver;Initial Catalog=GPS;Persist Security Info=True;User ID=xxx;Password=yyy"
Dim TripData As XmlDocument
Dim TripPoint As XmlNode
Dim err As Boolean = False
Dim points As String = ""
Dim reader As System.IO.StreamReader
Try
reader = New StreamReader(Request.InputStream)
xml = reader.ReadToEnd()
reader.Close()
TripData = New XmlDocument()
TripData.LoadXml(xml)
Catch ex As Exception
Response.Write("{""id"":901,""error"":true,""message"":""" & ex.ToString & """,""valid"",:true}")
Response.End()
Exit Sub
End Try
WriteDebug(xml)
username = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/username").InnerText
password = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/password").InnerText
siphoneid = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/devId").InnerText
travelname = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/travel/description").InnerText
uplpoints = TripData.SelectSingleNode("//bwiredtravel/travel/uplpoints").InnerText
WriteDebug("UplPoints = " & uplpoints)
totpoints = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/travel/tpoints").InnerText
WriteDebug("TPoints = " & totpoints)
travelid = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/travel/id").InnerText
WriteDebug("TravelID = " & travelid)
traveltime = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/travel/time").InnerText
WriteDebug("TravelTime = " & traveltime)
travellength = TripData.DocumentElement.SelectSingleNode("//bwiredtravel/travel/length").InnerText
WriteDebug("TravelLength = " & travellength)
Dim SQL As String = ""
Dim connection As New SqlConnection(connstr)
Dim command As New SqlCommand("", connection)
command.Connection.Open()
Try
For Each TripPoint In TripData.DocumentElement.SelectNodes("travel/point")
SQL = ""
Speed = ToLocalizedDecimalSeparator(TripPoint.SelectSingleNode("speed").InnerText)
If IsNumeric(Speed) Then
If Speed > 0 Then
Speed = Speed * 3.6
Else
Speed = 0
End If
Else
Speed = 0
End If
Speed = ToDotDecimalSeparator(FormatNumber(Speed, 2))
Angle = ToLocalizedDecimalSeparator(TripPoint.SelectSingleNode("course").InnerText)
If Left(Angle, 1) = "-" Then
Angle = "0"
End If
Angle = ToDotDecimalSeparator(FormatNumber(Angle, 1))
batt = 0
If IsNumeric(ToLocalizedDecimalSeparator(TripPoint.SelectSingleNode("bat").InnerText)) Then
batt = Math.Round((CSng(ToLocalizedDecimalSeparator(TripPoint.SelectSingleNode("bat").InnerText)) * 100))
End If
batt = ToDotDecimalSeparator(batt.ToString())
SQL = "INSERT INTO GPS VALUES(" & _
"'" & GenericSQLDateTimeFormat(EpochToDate(TripPoint.SelectSingleNode("date").InnerText)) & "'," & _
"'" & travelid & "'," & _
"'" & travelname & "'," & _
"'" & travellength & "'," & _
"'" & Fix(traveltime / 60) & "'," & _
"'" & Fix(TripPoint.SelectSingleNode("ttime").InnerText / 60) & "'," & _
"'" & TripPoint.SelectSingleNode("id").InnerText & "'," & _
"'" & TripPoint.SelectSingleNode("rdist").InnerText & "'," & _
"'" & TripPoint.SelectSingleNode("tdist").InnerText & "'," & _
"'" & totpoints & "'," & _
"'" & uplpoints & "'," & _
"'" & TripPoint.SelectSingleNode("continous").InnerText & "'," & _
"'" & TripPoint.SelectSingleNode("lat").InnerText & "'," & _
"'" & TripPoint.SelectSingleNode("lon").InnerText & "'," & _
"'" & Speed & "'," & _
"'" & Angle & "'," & _
"'" & TripPoint.SelectSingleNode("altitude").InnerText & "'," & _
"'" & TripPoint.SelectSingleNode("haccu").InnerText & "'," & _
"'" & TripPoint.SelectSingleNode("vaccu").InnerText & "'," & _
"'" & batt & "'," & _
"'" & siphoneid & "'," & _
"'" & username & "'," & _
"'" & password & "'," & _
"'" & GenericSQLDateTimeFormat(Now()) & "')"
WriteDebug("SQL= " & SQL)
command.CommandType = Data.CommandType.Text
command.CommandText = SQL
If command.ExecuteNonQuery() = 1 Then
points = points + TripPoint.SelectSingleNode("id").InnerText + ","
End If
Next
Catch ex As Exception
' remove last comma if applicable
If points <> "" Then
points = points.Substring(0, points.Length - 1)
End If
Response.Write("{""id"":902,""tripid"":" & travelid & ",""points"":[" & points & "],""error"":true,""message"":"" & ex.Message & "",""valid"":true}")
WriteDebug("Exc= " & ex.Message)
WriteDebug("Iex= " & ex.ToString)
End Try
' remove last comma
points = points.Substring(0, points.Length - 1)
WriteDebug("Points = " & points)
WriteDebug("===========================")
' create response
Response.Write("{""id"":0,""tripid"":" & travelid & ",""points"":[" & points & "],""valid"":true}")
Response.End()
End Sub
'
' Replaces "." with the localized decimal separator
'
Function ToLocalizedDecimalSeparator(ByVal value As String) As String
Dim DecimalSeparator As Char = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator
If DecimalSeparator <> "." Then
value = value.Replace(".", DecimalSeparator)
End If
Return value
End Function
'
' Replaces the local decimal separator (back) to "."
'
Function ToDotDecimalSeparator(ByVal value As String) As String
Dim DecimalSeparator As Char = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator
If DecimalSeparator <> "." Then
value = value.Replace(DecimalSeparator, ".")
End If
Return value
End Function
'
' Returns a date from Linux Epoch
'
Function EpochToDate(ByVal Unix_Epoch As String) As Date
WriteDebug("Epoch=" & Unix_Epoch)
Dim aDate As Date = New Date(1970, 1, 1, 0, 0, 0, 0)
aDate = aDate.AddSeconds(CDbl(ToLocalizedDecimalSeparator(Unix_Epoch)))
Return aDate
End Function
'
' Returns the generic SQL Server Date & Time format of a given DateTime.
'
Public Function GenericSQLDateTimeFormat(ByVal D As DateTime) As String
Return Right("0000" + D.Year.ToString, 4) + Right("00" + D.Month.ToString, 2) + Right("00" + D.Day.ToString, 2) + " " + Right("00" + D.Hour.ToString, 2) + ":" + Right("00" + D.Minute.ToString, 2) + ":" + Right("00" + D.Day.ToString, 2) + "." + Right("000" + D.Millisecond.ToString, 3)
End Function
'
' Writes text to a file for debugging purpose
'
Public Sub WriteDebug(ByVal TextToLog As String)
If Not Debug Then
Exit Sub
End If
Dim Filename As String = Server.MapPath("App_Data\BTracedDebug.log")
Dim Fnum As Integer = FreeFile()
Try
FileOpen(Fnum, Filename, OpenMode.Append, , OpenShare.Shared)
Catch ex As Exception
Exit Sub
End Try
Print(Fnum, Now.ToString & " " & TextToLog & Chr(13) & Chr(10))
FileClose(Fnum)
End Sub
End Class
The MS SQL Server script for the database table
- Code: Select all
USE [GPS]
GO
/****** Object: Table [dbo].[gps] Script Date: 08/12/2013 20:59:47
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[gps](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Datum] [datetime] NULL DEFAULT (NULL),
[Travelid] [varchar](10) NULL DEFAULT (NULL),
[Travelname] [varchar](50) NULL DEFAULT (NULL),
[Travellenght] [varchar](20) NULL DEFAULT (NULL),
[Traveltime] [varchar](20) NULL DEFAULT (NULL),
[Traveltimepoint] [varchar](20) NULL DEFAULT (NULL),
[Pointid] [varchar](20) NULL DEFAULT (NULL),
[Pointdistance] [varchar](20) NULL DEFAULT (NULL),
[Pointdistancetotal] [varchar](20) NULL DEFAULT (NULL),
[Totpoints] [varchar](15) NULL DEFAULT (NULL),
[Uplpoints] [varchar](20) NULL DEFAULT (NULL),
[Continous] [char](2) NULL DEFAULT (NULL),
[Lat] [varchar](40) NULL DEFAULT (NULL),
[sLong] [varchar](40) NULL DEFAULT (NULL),
[Speed] [varchar](12) NULL DEFAULT (NULL),
[Angle] [varchar](10) NULL DEFAULT (NULL),
[Altitude] [varchar](15) NULL DEFAULT (NULL),
[Haccu] [varchar](20) NULL DEFAULT (NULL),
[Vaccu] [varchar](20) NULL DEFAULT (NULL),
[Phone_BattStat] [varchar](10) NULL DEFAULT (NULL),
[Iphoneid] [varchar](50) NULL DEFAULT (NULL),
[Username] [varchar](50) NULL DEFAULT (NULL),
[Password] [varchar](50) NULL DEFAULT (NULL),
[Datumupload] [datetime] NULL DEFAULT (NULL),
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
