Logfile parsing then instert/update database help =( (looping problem i think)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Logfile parsing then instert/update database help =( (looping problem i think)

  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Logfile parsing then instert/update database help =( (looping problem i think)

    Im working on a project for class and I need help trying to update a RFID output file into a database.

    Example RFID Output looks like this:

    Time, ID, Room

    12:45:30, 1000, 100 (First Initalization of item 1000)

    12:45:45, 1000, 100 (Leaves Room 100)

    12:45:50, 1000, 101 (Enters Elevator 101)

    12:47:37, 1000, 501 (Leave Elevator on floor 5 at 501)

    12:48:20, 1000, 500 (Enters storage room 500)



    Now the file is done by the second so there is alot of just junk text like

    12:41:00

    12:41:01

    12:41:02

    12:41:03

    12:41:04

    12:41:05

    12:41:06

    Where nothing is happening so I dont want to store this in the database.

    Also every time the clock hits the hour or half hour mark it needs to kick up a msgbox asking them if they want to continue parsing the file (No Problems with this)

    I also have no problems just storing the data into the database and not including the junk data.



    Problem occurs when I have to update the status of the items in the database.

    DB looks like

    ID, Time, Item, Room, Status, Travel Time

    ID=Auto Number

    1 12:45:30, 1000, 100 STORED

    2 12:45:45, 1000, 100 TRANSIT

    3 12:45:50, 1000, 101 TRANSIT

    4 12:47:37, 1000, 501 TRANSIT

    5 12:48:20, 1000, 500 STORED

    But when the item leaves room 100 then it needs to look like

    1 12:45:30, 1000, 100 LEFT/WAS_STORED

    2 12:45:45, 1000, 100 TRANSIT

    3 12:45:50, 1000, 101 TRANSIT

    4 12:47:37, 1000, 501 TRANSIT

    5 12:48:20, 1000, 500 STORED

    then

    1 12:45:30, 1000, 100 LEFT/WAS_STORED

    2 12:45:45, 1000, 100 WAS_TRANSIT

    3 12:45:50, 1000, 101 TRANSIT

    4 12:47:37, 1000, 501 TRANSIT

    5 12:48:20, 1000, 500 STORED

    then

    1 12:45:30, 1000, 100 LEFT/WAS_STORED

    2 12:45:45, 1000, 100 WAS_TRANSIT

    3 12:45:50, 1000, 101 WAS_TRANSIT

    4 12:47:37, 1000, 501 TRANSIT

    5 12:48:20, 1000, 500 STORED

    then

    1 12:45:30, 1000, 100 LEFT/WAS_STORED

    2 12:45:45, 1000, 100 WAS_TRANSIT

    3 12:45:50, 1000, 101 WAS_TRANSIT

    4 12:47:37, 1000, 501 WAS_TRANSIT

    5 12:48:20, 1000, 500 STORED

    and then if item 1000 leave the room again

    1 12:45:30, 1000, 100 LEFT/WAS_STORED

    2 12:45:45, 1000, 100 WAS_TRANSIT

    3 12:45:50, 1000, 101 WAS_TRANSIT

    4 12:47:37, 1000, 501 WAS_TRANSIT

    5 12:48:20, 1000, 500 LEFT/WAS_STORED



    So that if your were looking at the database at 12:45:59 then the item should be in transit but then if you look at it at 12:45:30 the item is stored. (I have the options setup and know how to have them stop the parsing and look at database/do reports)



    Im just lost on how to setup the inner loops for updating the status of the items from stored->left and so on



    Heres what I got so far and it sort of works =) but not really hehe



    Any help would be great

    Thanks

    Brad

    Code can be found at
    http://www.mmo-rpg.com/Files/File.txt

  2. #2
    Join Date
    Feb 2005
    Posts
    2
    Private Sub Parse_Click()

    Dim strFile As String, strTemp As String

    Dim tmpArray() As String

    strFile = "c:\Project\test2.txt"

    Dim Status As String

    Dim lcounter As Double

    Dim fin As Integer

    Dim yestimes As Double

    Dim time As Double

    fin = 0

    lcounter = 0



    Open strFile For Input As #1



    Do Until lcounter = gcounter

    Line Input #1, strTemp

    lcounter = lcounter + 1

    Loop



    Do While Not EOF(1) And fin = 0



    Line Input #1, strTemp

    lcounter = lcounter + 1

    gcounter = gcounter + 1

    strTemp = strTemp + ",0,0,"

    tmpArray() = Split(strTemp, ",")



    If tmpArray(4) = "0" Then

    ElseIf tmpArray(5) = "102" Or tmpArray(5) = "202" Or tmpArray(5) = "302" Or tmpArray(5) = "402" Then

    time = ((tmpArray(0) * 86400) + (tmpArray(1) * 3600) + (tmpArray(2) * 60) + tmpArray(3))

    display.Print time

    display.Print

    Status = "TRANSIT"

    strSQL = "INSERT INTO test ([Day],[Hour],[Min],[Sec],[Item],[Room],[Time],[Status]) VALUES ('"

    strSQL = strSQL & tmpArray(0) & "', '"

    strSQL = strSQL & tmpArray(1) & "', '"

    strSQL = strSQL & tmpArray(2) & "', '"

    strSQL = strSQL & tmpArray(3) & "', '"

    strSQL = strSQL & tmpArray(4) & "', '"

    strSQL = strSQL & tmpArray(5) & "', '"

    strSQL = strSQL & time & "', '"

    strSQL = strSQL & Status & "')"

    adoCmd.CommandText = strSQL

    adoCmd.CommandType = adCmdText

    adoCmd.ActiveConnection = adoConn

    adoCmd.Execute

    'adoRS.Close

    Else

    display.Print tmpArray(0) + " " + tmpArray(4);

    adoRS.Source = "SELECT * FROM test Where [Item]=" & CDbl(tmpArray(4))

    adoRS.CursorType = adOpenForwardOnly

    adoRS.ActiveConnection = adoConn

    adoRS.Open



    Status = "STORED"



    If adoRS.EOF Then

    time = ((tmpArray(0) * 86400) + (tmpArray(1) * 3600) + (tmpArray(2) * 60) + tmpArray(3))

    strSQL = "INSERT INTO test ([Day],[Hour],[Min],[Sec],[Item],[Room],[Time],[Status]) VALUES ('"

    strSQL = strSQL & tmpArray(0) & "', '"

    strSQL = strSQL & tmpArray(1) & "', '"

    strSQL = strSQL & tmpArray(2) & "', '"

    strSQL = strSQL & tmpArray(3) & "', '"

    strSQL = strSQL & tmpArray(4) & "', '"

    strSQL = strSQL & tmpArray(5) & "', '"

    strSQL = strSQL & time & "', '"

    strSQL = strSQL & Status & "')"

    adoCmd.CommandText = strSQL

    adoCmd.CommandType = adCmdText

    adoCmd.ActiveConnection = adoConn

    adoCmd.Execute

    adoRS.Close

    ElseIf adoRS.Fields("Room").Value = "102" Or adoRS.Fields("Room").Value = "202" Or adoRS.Fields("Room").Value = "302" Or adoRS.Fields("Room").Value = "402" Then

    Status = "TRANSIT"

    time = ((tmpArray(0) * 86400) + (tmpArray(1) * 3600) + (tmpArray(2) * 60) + tmpArray(3))

    strSQL = "INSERT INTO test ([Day],[Hour],[Min],[Sec],[Item],[Room],[Time],[Status]) VALUES ('"

    strSQL = strSQL & tmpArray(0) & "', '"

    strSQL = strSQL & tmpArray(1) & "', '"

    strSQL = strSQL & tmpArray(2) & "', '"

    strSQL = strSQL & tmpArray(3) & "', '"

    strSQL = strSQL & tmpArray(4) & "', '"

    strSQL = strSQL & tmpArray(5) & "', '"

    strSQL = strSQL & time & "', '"

    strSQL = strSQL & Status & "')"

    adoCmd.CommandText = strSQL

    adoCmd.CommandType = adCmdText

    adoCmd.ActiveConnection = adoConn

    adoCmd.Execute

    adoRS.Close

    Else

    Do While Not adoRS.EOF

    Dim itemnum As Long

    itemnum = adoRS.Fields("Item").Value = tmpArray(4)

    Dim roomnum As Long

    roomnum = adoRS.Fields("Item").Value = tmpArray(5)



    If ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "101") And ((adoRS.Fields("Status").Value = "STORED") Or (adoRS.Fields("Status").Value = "STARTED_IN"))) Or ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "201") And (adoRS.Fields("Status").Value = "STORED")) Or ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "301") And (adoRS.Fields("Status").Value = "STORED")) Or ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "401") And (adoRS.Fields("Status").Value = "STORED")) Then

    strSQL = "UPDATE test SET Status='STARTED_IN'WHERE ID=" & adoRS.Fields("ID")

    adoCmd.CommandText = strSQL

    adoCmd.CommandType = adCmdText

    adoCmd.ActiveConnection = adoConn

    adoCmd.Execute

    Set adoCmd = Nothing

    Status = "STORED"

    adoRS.MoveNext



    ElseIf ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "102") Or (adoRS.Fields("Room").Value = "101") And (adoRS.Fields("Status").Value = "TRANSIT")) Or ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "202") Or (adoRS.Fields("Room").Value = "201") And (adoRS.Fields("Status").Value = "TRANSIT")) Or ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "302") Or (adoRS.Fields("Room").Value = "301") And (adoRS.Fields("Status").Value = "TRANSIT")) Or ((adoRS.Fields("Item").Value = tmpArray(4)) And (adoRS.Fields("Room").Value = "402" Or (adoRS.Fields("Room").Value = "401")) And (adoRS.Fields("Status").Value = "TRANSIT")) Then

    strSQL = "UPDATE test SET Status='WAS_TRANSIT' WHERE ID=" & adoRS.Fields("ID")

    adoCmd.CommandText = strSQL

    adoCmd.CommandType = adCmdText

    adoCmd.ActiveConnection = adoConn

    adoCmd.Execute

    Set adoCmd = Nothing

    adoRS.MoveNext

    Else

    Status = "STORED"

    adoRS.MoveNext

    End If

    Loop

    time = ((tmpArray(0) * 86400) + (tmpArray(1) * 3600) + (tmpArray(2) * 60) + tmpArray(3))

    strSQL = "INSERT INTO test ([Day],[Hour],[Min],[Sec],[Item],[Room],[Time],[Status]) VALUES ('"

    strSQL = strSQL & tmpArray(0) & "', '"

    strSQL = strSQL & tmpArray(1) & "', '"

    strSQL = strSQL & tmpArray(2) & "', '"

    strSQL = strSQL & tmpArray(3) & "', '"

    strSQL = strSQL & tmpArray(4) & "', '"

    strSQL = strSQL & tmpArray(5) & "', '"

    strSQL = strSQL & time & "', '"

    strSQL = strSQL & Status & "')"

    adoCmd.CommandText = strSQL

    adoCmd.CommandType = adCmdText

    adoCmd.ActiveConnection = adoConn

    adoCmd.Execute

    adoRS.Close

    End If



    End If

    If ((tmpArray(2) = "0" And tmpArray(3) = "0") Or (tmpArray(2) = "30" And tmpArray(3) = "0")) Then

    response = MsgBox("Parse Again?", vbYesNo + vbQuestion, "Parse")



    If (response = vbYes) Then

    yestimes = yestimes + 1

    display.Cls

    display.Print "30 Min Has Elapsed"



    Else

    fin = 1

    ItemID.Visible = True

    Item_ID.Visible = True

    Search.Visible = True

    Room_Number.Visible = True

    RoomNumber.Visible = True

    RoomSearch.Visible = True

    End If



    End If



    Loop

    Close #1



    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center