form1.frm

用于车辆监控数据的分析

VERSION 5.00
Object = "{058ADF50-1120-11D0-80D0-0000F73D691E}#1.0#0"; "mexdemo25.ocx"
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   6030
   ClientLeft      =   165
   ClientTop       =   735
   ClientWidth     =   8535
   LinkTopic       =   "Form1"
   ScaleHeight     =   6030
   ScaleWidth      =   8535
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton Command2 
      Caption         =   "Command2"
      Height          =   495
      Left            =   4080
      TabIndex        =   5
      Top             =   4080
      Width           =   1695
   End
   Begin VB.CommandButton Command1 
      Caption         =   "送货情况分析new"
      Height          =   495
      Left            =   4080
      TabIndex        =   4
      Top             =   5040
      Width           =   1695
   End
   Begin VB.CommandButton SuspiciousCheck 
      Caption         =   "可疑送货点分析"
      Height          =   495
      Left            =   1920
      TabIndex        =   3
      Top             =   5040
      Width           =   1695
   End
   Begin VB.CommandButton CustomerCheck 
      Caption         =   "送货点分析"
      Height          =   495
      Left            =   1920
      TabIndex        =   2
      Top             =   4080
      Width           =   1695
   End
   Begin MapEngineLib.SpaDB SpaDB1 
      Height          =   375
      Left            =   7920
      TabIndex        =   1
      Top             =   0
      Width           =   615
      _Version        =   65536
      _ExtentX        =   1085
      _ExtentY        =   661
      _StockProps     =   0
   End
   Begin MapEngineLib.CompoundMap CompoundMap1 
      Height          =   3015
      Left            =   720
      TabIndex        =   0
      Top             =   600
      Width           =   6135
      _Version        =   65536
      _ExtentX        =   10821
      _ExtentY        =   5318
      _StockProps     =   0
   End
   Begin VB.Menu report 
      Caption         =   "统计报表"
   End
   Begin VB.Menu distance 
      Caption         =   "用户位置信息"
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim j        As Integer
Dim i        As Integer
Dim str1     As String
Dim dataproperty  As Boolean

Private Sub Command1_Click()

'按照给客户留的接口,得到车号,车辆数目,送货点的位置,送货点数目

'* Step 1*

'根据不同的车号将表重读,得到按车号不同排的几张表,按照车号如GV2data来命名表
'MsgBox carID(1)
Dim cnn     As New ADODB.Connection
Dim rs_Tabs As ADODB.Recordset
Dim flag    As Boolean
Set cnn = adoConn
'
''******判断用于生成报表的表是否在数据库中存在**********
'   Set rs_Tabs = cnn.OpenSchema(adSchemaTables) '创建数据库记录集为了得到数据库中所有表名
'   Do While Not rs_Tabs.EOF
'      For i = 1 To carCount
'      If carID(i) = rs_Tabs!TABLE_NAME Then
'         flag = True
'         MsgBox flag
'         Exit Do
'      End If
'      rs_Tabs.MoveNext
'      Next
'   Loop
'   rs_Tabs.Close

'如果表没有创建
If flag = False Then
Dim rs_temp As New ADODB.Recordset
   
   rs_temp.CursorLocation = adUseClient
'   create table [a1] ([vehicleID] String,[datetime] Date,[lat] double,[lon] double,[speed] byte, [state] string)
   For i = 1 To carCount
   str1 = "create table " & carID(i) & " ( [vehicleID] String,[datetime] Date,[lat] double,[lon] double,[speed] byte,[state] string)"
   MsgBox str1
   rs_temp.Open str1, adoConn, adOpenDynamic, adLockOptimistic
   Next i
End If
   
'如果表已经创建
If flag = True Then



End If

'*Step2* 按照车号判断记录属于那个表的记录,将该记录放到对应的表中
'*模拟数据输入过程,读一条数据 = 得到一条车辆返回信息

'留接口输入每一条记录 vehicleID , datetime ,lat ,lon ,speed
' rs1.CursorLocation = adUseClient
'    str1 = "select * from data "
'    rs1.Open str1, adoConn, adOpenDynamic, adLockOptimistic


Do While Not rs.EOF
    '读数据
    lat = rs.Fields("lat").Value
    log = rs.Fields("lon").Value
    vehicleID = rs.Fields("vehicleID").Value
    speed = rs.Fields("speed").Value
    curTime = rs.Fields("datetime").Value
'    Debug.Print vehicleID

    '送货分析判断过程中数据的预处理
    For j = 1 To userCount
    
    If GetDistance(lat, log, userLat(j), userLon(j)) < 100 Then
    dataproperty = True
    End If
    
    Next
    
   '写数据 , 之前先要判断属于那张表, 按车号来判断
    For i = 1 To carCount

    If carID(i) = vehicleID And dataproperty = True Then
   
   '添加数据,模拟动态添加过程
        rs1.CursorLocation = adUseClient
        str1 = "select * from " & carID(i)
        rs1.Open str1, adoConn, adOpenDynamic, adLockOptimistic
        rs1.AddNew
        rs1!DateTime = curTime
        rs1!vehicleID = vehicleID
        rs1!speed = speed
        rs1!lat = lat
        rs1!lon = log
        rs1.Update
        rs1.MoveLast
        rs1.Close
        
        
    End If

    Next
    
    
     Call Distinguish(vehicleID)
     dataproperty = False
     rs.MoveNext
Loop

'    If rs1.AbsolutePosition = rs1.RecordCount Then
'    rs1.Close
'    MsgBox ("送货点分析结束")
'    End If
End Sub

Private Sub Command2_Click()
'*************
rs.Close
rs.Open "select * from 'db' where  XType = 'U' AND  name='GV2'", adoConn
If rs.Fields(0) = "1" Then
'   conn.Execute "drop table 表名"
MsgBox "D"
End If


'If rs.Fields(0) = "1" Then
'MsgBox "d"   '表存在，想怎么处理都可以
'Else
'   '表不存在，就不要费心了
'End If
rs.Close

'*************

Dim rr As New ADODB.Recordset
str1 = "select * from sysobjects where  XType = 'U' AND  name= test "
rr.Open str1, adoConn, adOpenDynamic, adLockOptimistic
If rr.RecordCount > 0 Then
MsgBox "hi"
End If
'select * from sysobjects where  XType = 'U' AND  name=表名
'if @@rowcounr>0 就存在
'   drop table 表名
End Sub

'用户点送达状态判断过程
Private Sub CustomerCheck_Click()
  
'将数据集默认的服务器游标改为客户端游标
  
    rs1.CursorLocation = adUseClient
    str1 = "select * from customercheck  "
    rs1.Open str1, adoConn, adOpenDynamic, adLockOptimistic


Do While Not rs.EOF
    lat = rs.Fields("lat").Value
    log = rs.Fields("lon").Value
    vehicleID = rs.Fields("vehicleID").Value
    speed = rs.Fields("speed").Value
    curTime = rs.Fields("datetime").Value
    Debug.Print vehicleID
    
    '写新表
    rs1.AddNew
    rs1!DateTime = curTime
    rs1!vehicleID = vehicleID
    rs1!speed = speed
    rs1!lat = lat
    rs1!lon = log
    rs1.Update
    rs1.MoveLast
    rs1.Close
    'MsgBox speed

    '进行送货状态判别
 
    Call Distinguish
    
    'MsgBox userLon(0) & "" & userLat(0)
    rs.MoveNext
  
Loop
    If rs1.AbsolutePosition = rs1.RecordCount Then
    rs1.Close
    MsgBox ("送货点分析结束")
    End If
End Sub

'进行接口变量值的初始化

Private Sub Form_Load()
  
    carCount = 3
    ReDim carID(carCount)
'    ReDim work(carCount)
    carID(1) = "GV2"
    carID(2) = "GV3"
    carID(3) = "GV7"

    userCount = 3
    ReDim userLon(userCount)
    ReDim userLat(userCount)

    userLon(1) = 116.226425170898
    userLat(1) = 39.8890991210938
    userLon(2) = 116.246887207031
    userLat(2) = 39.8713188171387
    userLon(3) = 116.232467651367
    userLat(3) = 39.9284896850586



    SpaDB1.Visible = False
    CompoundMap1.Visible = False
'    For i = 1 To carCount
'    work(i) = False '默认工作状态
'    Next i
    
    '打开源数据库表
    Call OpenDb
    rs.CursorLocation = adUseClient
    str1 = "select * from dbo_GPSStatus "
    rs.Open str1, adoConn, adOpenDynamic, adLockOptimistic

End Sub

Private Sub distance_Click()
    Dialog.Show
End Sub


Private Sub report_Click()
    
    Dialog1.Show
    'strsql = "select * from dbo_GPSStatus"
    'DataReport1.Show

End Sub

' 可疑送货点状态的判断过程
Private Sub SuspiciousCheck_Click()
 
   Dim rs_temp As New ADODB.Recordset
   
   rs_temp.CursorLocation = adUseClient
'   create table [a1] ([vehicleID] String,[datetime] Date,[lat] double,[lon] double,[speed] byte, [state] string)
   For i = 1 To carCount
   str1 = "create table " & carID(i) & "Sus" & " ( [vehicleID] String,[datetime] Date,[lat] double,[lon] double,[speed] byte,[state] string)"
   MsgBox str1
   rs_temp.Open str1, adoConn, adOpenDynamic, adLockOptimistic
   Next i

'   rs_temp.Close
Do While Not rs.EOF
    '读数据
    lat = rs.Fields("lat").Value
    log = rs.Fields("lon").Value
    vehicleID = rs.Fields("vehicleID").Value
    speed = rs.Fields("speed").Value
    curTime = rs.Fields("datetime").Value
'    Debug.Print vehicleID

    '送货分析判断过程中数据的预处理
 
    
   '写数据 , 之前先要判断属于那张表, 按车号来判断
    For i = 1 To carCount

    If carID(i) = vehicleID Then
   
   '添加数据,模拟动态添加过程
        
        rs_temp.CursorLocation = adUseClient
        str1 = "select * from " & carID(i) & "Sus"
        rs_temp.Open str1, adoConn, adOpenDynamic, adLockOptimistic
        rs_temp.AddNew
        rs_temp!DateTime = curTime
        rs_temp!vehicleID = vehicleID
        rs_temp!speed = speed
        rs_temp!lat = lat
        rs_temp!lon = log
        rs_temp.Update
        rs_temp.MoveLast
        rs_temp.Close
        
        
    End If

    Next
    
    
     Call Suspicious(vehicleID)
    
     rs.MoveNext
Loop
'    If rs_temp.AbsolutePosition = rs_temp.RecordCount Then
'    rs_temp.Close
'    MsgBox ("可疑送货点分析结束")
'    End If
End Sub