<%@ LANGUAGE=VBScript LCID=1033%>
<%
' Utility sample for Aspicore GSM Tracker
' Show URL arguments as plain text and stores data into MS SQL Server database
'
' 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.
'
' Note: As opposed to storemdb.asp, this script stores also the cell tower info and test messages
' in addition to the GPS data.
'
' (c) Aspicore Ltd 2005, www.aspicore.com
'
' Tested with Windows XP, IIS ASP 5.1 and Windows 2000, IIS ASP 5.0
' ADODB v2.7, MSDE 1.0 (Binary compatible with MS SQL Server 7.0)
' ------------------------------
' Change History:
' 2005-04-19 jje - Script created
%>
<% Option Explicit
Response.Buffer = True
Response.ContentType = "text/plain; charset=ISO-8859-1"
Response.Expires = 0
' Response.LCID = 1033 ' Windows 2000 does not support this, so replaced by the following line
Session.LCID = 1033 ' Geographical locale telling how to format dates and times, 1033 = English - US
%>
<!--#INCLUDE FILE="adovbs.inc"-->
<%
' ------------------------
' Initialize argument variables to Null
Dim vImei
Dim vStatus
Dim vLat
Dim vLon
Dim vSpeed
Dim vCourse
Dim vTime
Dim vDate
Dim vLabel
Dim vRemoteIP
Dim vInnwname
Dim vIncell
Dim vInlac
Dim vInmcc
Dim vInmnc
Dim vOutnwname
Dim vOutcell
Dim vOutlac
Dim vOutmcc
Dim vOutmnc
Dim vTest
vImei = Null
vStatus = Null
vLat = Null
vLon = Null
vSpeed = Null
vCourse = Null
vTime = Null
vDate = Null
vLabel = Null
vRemoteIP = Request.ServerVariables("REMOTE_ADDR")
vInnwname = Null
vIncell = Null
vInlac = Null
vInmcc = Null
vInmnc = Null
vOutnwname = Null
vOutcell = Null
vOutlac = Null
vOutmcc = Null
vOutmnc = Null
vTest = Null
' ------------------------
' Show time and arguments in the response to the HTTP client
' Assign values to those argument variables that are in use
Response.Write Now() & vbCrLf
Dim item
Dim itemValue
For Each item In Request.QueryString
itemValue = Request.QueryString(item)
Response.Write item & " => " & itemValue
Select Case item
Case "imei"
vImei = itemValue
Case "status"
vStatus = itemValue
Case "lat"
ON ERROR RESUME NEXT
vLat = CDbl(itemValue)
ON ERROR GOTO 0
Case "lon"
ON ERROR RESUME NEXT
vLon = CDbl(itemValue)
ON ERROR GOTO 0
Case "speed"
ON ERROR RESUME NEXT
vSpeed = CDbl(itemValue)
ON ERROR GOTO 0
Case "course"
ON ERROR RESUME NEXT
vCourse = CDbl(itemValue)
ON ERROR GOTO 0
Case "time"
vTime = itemValue
Case "date"
vDate = itemValue
Case "label"
vLabel = itemValue
Case "innwname"
vInnwname = itemValue
Case "incell"
ON ERROR RESUME NEXT
vIncell = CLng(itemValue)
ON ERROR GOTO 0
Case "inlac"
ON ERROR RESUME NEXT
vInlac = CLng(itemValue)
ON ERROR GOTO 0
Case "inmcc"
ON ERROR RESUME NEXT
vInmcc = CLng(itemValue)
ON ERROR GOTO 0
Case "inmnc"
ON ERROR RESUME NEXT
vInmnc = CLng(itemValue)
ON ERROR GOTO 0
Case "outnwname"
vOutnwname = itemValue
Case "outcell"
ON ERROR RESUME NEXT
vOutcell = CLng(itemValue)
ON ERROR GOTO 0
Case "outlac"
ON ERROR RESUME NEXT
vOutlac = CLng(itemValue)
ON ERROR GOTO 0
Case "outmcc"
ON ERROR RESUME NEXT
vOutmcc = CLng(itemValue)
ON ERROR GOTO 0
Case "outmnc"
ON ERROR RESUME NEXT
vOutmnc = CLng(itemValue)
ON ERROR GOTO 0
Case "test"
vTest = itemValue
Case Else ' Other values.
Response.Write " (Ignored)"
End Select
Response.Write vbCrLf
Next
Response.Write vbCrLf
' ------------------------
' Store new data into MS SQL Server database
' (Make sure the database 'GSM_Tracker' exists in the location given in strConn.
' Create the database first with script GSM_Tracker_MSSQL7.sql)
' ------------------------
' Connecting, selecting database
Dim objDBConn ' As ADODB.Connection
Dim strConn
' ***********************************************************************
' MS SQL Server Connect String
strConn="Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=GSM_Tracker;User ID=UID;Password=PWD;Persist Security Info=True"
' ***********************************************************************
Set objDBConn = Server.CreateObject("ADODB.Connection")
objDBConn.Open strConn
' Common variables for the SQL statements
Dim cmd ' As ADODB.Command
Dim prm ' As ADODB.Parameter
Dim rsSelectResult ' As ADODB.Recordset
Dim sSQL
Dim vGPSMSGID ' Generated GPS.GPSMSGID value
vGPSMSGID = Null
' ------------------------
' Check, if there is a test message among the arguments
If Not IsNull(vTest) Then ' Handle test message
' Prepare an insert statement
sSQL = "INSERT INTO TEST (PHONE,MSG_RAW,REMOTE_IP)" _
& " VALUES (?,?,?)"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
' Create parameters
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 30, vImei)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 80, vTest)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 15, vRemoteIP)
cmd.Parameters.Append prm
Set cmd.ActiveConnection = objDBConn
' Execute the statement
cmd.Execute
' Release resources
Set prm = Nothing
Set cmd = Nothing
' Show message in the response to the HTTP client
Response.Write "Test message added into DB" & vbCrLf
End If ' Handle test message
' ------------------------
' Check, if there are GPS coordinates among the arguments
If (Not IsNull(vLat)) Or (Not IsNull(vLon)) Then ' Handle GPS coordinates
' Prepare an insert statement
sSQL = "INSERT INTO GPS (PHONE,STATUS,LATITUDE,LONGITUDE,SPEED_KNOTS,COURSE_DEG,UTCTIME,UTCDATE,LABEL,REMOTE_IP)" _
& " VALUES (?,?,?,?,?,?,?,?,?,?)"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
' Create parameters
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 30, vImei)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adChar, AdParamInput, 1, vStatus)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adDouble, AdParamInput, , vLat)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adDouble, AdParamInput, , vLon)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adDouble, AdParamInput, , vSpeed)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adDouble, AdParamInput, , vCourse)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 10, vTime)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 6, vDate)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 20, vLabel)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 15, vRemoteIP)
cmd.Parameters.Append prm
Set cmd.ActiveConnection = objDBConn
' Execute the statement
cmd.Execute
' Read the @@IDENTITY value from the returned result set
sSQL = "SELECT @@IDENTITY AS 'Identity'"
Set rsSelectResult = objDBConn.Execute( sSQL )
If rsSelectResult.EOF Then
Response.Write "Error while adding GPS coords into DB" & vbCrLf
Else
vGPSMSGID = rsSelectResult(0).Value
Response.Write "GPS coords added into DB" & vbCrLf
End If
' Free resultset
rsSelectResult.Close
Set rsSelectResult = Nothing
' Release resources
Set prm = Nothing
Set cmd = Nothing
End If ' Handle GPS coordinates
' ------------------------
' Check, if there is a 'incell' message among the arguments
If Not IsNull(vIncell) Then ' Handle 'incell' message
' Prepare an insert statement
sSQL = "INSERT INTO GSM_CELL (PHONE,GPSMSGID,EVENT_TYPE,CELL_ID,LAC,MCC,MNC,SHORT_NAME,REMOTE_IP,LABEL)" _
& " VALUES (?,?,?,?,?,?,?,?,?,?)"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
' Create parameters
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 30, vImei) ' PHONE
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vGPSMSGID) ' GPSMSGID
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 3, "In") ' EVENT_TYPE
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vIncell) ' CELL_ID
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vInlac) ' LAC
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vInmcc) ' MCC
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vInmnc) ' MNC
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 30, vInnwname) ' SHORT_NAME
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 15, vRemoteIP)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 20, vLabel)
cmd.Parameters.Append prm
Set cmd.ActiveConnection = objDBConn
' Execute the statement
cmd.Execute
' Release resources
Set prm = Nothing
Set cmd = Nothing
' Show message in the response to the HTTP client
Response.Write "Cell tower entry data added into DB" & vbCrLf
End If ' Handle 'incell' message
' ------------------------
' Check, if there is a 'outcell' message among the arguments
If Not IsNull(vOutcell) Then ' Handle 'outcell' message
' Prepare an insert statement
sSQL = "INSERT INTO GSM_CELL (PHONE,GPSMSGID,EVENT_TYPE,CELL_ID,LAC,MCC,MNC,SHORT_NAME,REMOTE_IP,LABEL)" _
& " VALUES (?,?,?,?,?,?,?,?,?,?)"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
' Create parameters
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 30, vImei) ' PHONE
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vGPSMSGID) ' GPSMSGID
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 3, "Out") ' EVENT_TYPE
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vOutcell) ' CELL_ID
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vOutlac) ' LAC
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vOutmcc) ' MCC
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adInteger, AdParamInput, , vOutmnc) ' MNC
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 30, vOutnwname) ' SHORT_NAME
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 15, vRemoteIP)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter(, adVarChar, AdParamInput, 20, vLabel)
cmd.Parameters.Append prm
Set cmd.ActiveConnection = objDBConn
' Execute the statement
cmd.Execute
' Release resources
Set prm = Nothing
Set cmd = Nothing
' Show message in the response to the HTTP client
Response.Write "Cell tower exit data added into DB" & vbCrLf
End If ' Handle 'outcell' message
' ------------------------
' close connection
objDBConn.Close
Set objDBConn = Nothing
%>