Searching A Database of License Plates

[Div]

So I've built an investigative tools for my office that searches a database of license plates for matches. The database can only be searched by a "brute force" method as the data is on an offsite server.

I am having trouble wrapping my head around how to make this work. 

If the detective wants to search for all license plates that start with W*R9**0, for example, how do I get for next statements to pump out:

WAR9000

WBR9000

...

WAR9010

WBR9010

etc. 

My attempts have failed thus far with nested For Next statements. The plate to search for is stored in the array plates[8]. And is imputed by the detective with * as the wildcard variable. 

[careca]

I can see it be done with a bunch of stringinstr and stringleft/right.

The best one is regex, that i don't know much about.

But to implement regex in a situation like that i don't have a clue, simply because the wildcard can switch places with next searches, right?

[SlackerAl]

Take a look at 

Chr()

You can use this to increment through ascii values (65-90) giving you A-Z, then you just have a bunch of nested loops. It would be much nicer if you could make a remote SQL query of the database...

Post a replicator, even if it does not work as well as you want.

[Div]

I already use the Chr function to make the plates populate. I'll post my code when I get to work so you can see what I have. It's embarrassing and doesn't work properly. 

Its simply the logic of the matter that is the issue:

i cant figure out how to iterate the first wildcard and the second wildcard (and so on) in a fashion that works. 

[SlackerAl]

OK, I'm going to to be busy for a bit, but I can always take a look in a few hours if no one else comes to the rescue.

As an aside, you might want to be careful to have some sort of cap on the maximum number of requests you generate: If a user puts all "*"s into that ("I just wondered what would happen!"), you are going have your own little DoS tool...

[Div]

It's already limited to four wild cards. I'm the late detective tonight so I don't start work until 7:00pm. I won't be able to post my code until then. 

[Div]

So here the issue as I best can explain it typing on my iPhone:

Plate[8] stores the 7 characters of the license plate. (Plate[0] is unused)

1. Find the first wildcard. 

2. Save non-wild characters and step 1 into wildcard. 

3. Find second wildcard. 

4. Save non-wild characters and step 1 into new wildcard. 

5. Find last wildcard. 

6. Save non-wild characters and step 1 into last wildcard. 

Now make the rest iterate so I get every combo for wild cards. 

I can seem to find an elegant solution that doesn't repeat plates or misses large chunks. 

[SlackerAl]

I don't have access to AutoIt here, but I think you want something that looks like this:

NOT REAL CODE

Global $plates[7] = ["W", "*", "R", "9", "*", "*", "0"]
Global $output_plates[7][1]

PlateBuild(0, 0)

Func PlateBuild($iPos, $iPlateID)
  Local $iPos, $iPlateID
  Determine loop start finish depending on $iPos value
  if *
    loop all letters/numbers
    $output_plates[$iPos][$iPlateID] = current letter
    if $iPos < 7 then callPlateBuild($iPos + 1, $iPlateID)
    else $iPlateID++
    Next    
  else
    $output_plates[$iPos][$iPlateID] = $plates[$iPos]
    if $iPos < 7 then callPlateBuild($iPos + 1, $iPlateID)
    else $iPlateID++    
EndFunc

Basically, use a recursive function call to build each plate and add it to a 2D array. You will see $iPlateID increments each time the last digit of the plate is determined.

If you can make some sense of that, great (syntax is a bit random, but hopefully you get the idea)! If not I will try to write some running code when I get home tonight.

Edit: you'll need to make $iPlateID global I think...

Edited October 3, 2017 by SlackerAl

[Div]

Plate is already global in my program. Can you explain why you made the array 2D? What's the function of the second dimension?

[SlackerAl]

I was just building an array of all possible plates, you could drop it and call a function to process the single plate each time one is created if you prefer.

[SlackerAl]

This should be ok, just put your action for the plate in the function ProcessPlate() this gets called each time a complete plate is generated. 

expandcollapse popup

#include <Array.au3>

Opt("MustDeclareVars", 1)

Global $plates[7] = ["W", "*", "R", "9", "*", "*", "0"]
Global $output_plate[7]

PlateBuild(0)

Func PlateBuild($iPos)

  if $plates[$iPos] = "*" Then
    Local $iStart = 65
    Local $iFinish = 90
    if $iPos > 2 Then
      $iStart = 48
      $iFinish = 57
    EndIf
    For $i = $iStart To $iFinish
      $output_plate[$iPos] = Chr($i)
      If $iPos < 6 Then
        PlateBuild($iPos + 1)
      Else
        ProcessPlate()
      EndIf
    Next
  Else
    $output_plate[$iPos] = $plates[$iPos] 
    If $iPos < 6 Then
      PlateBuild($iPos + 1)
    Else
      ProcessPlate()  
    EndIf
  EndIf
  
EndFunc


Func ProcessPlate()
  _ArrayDisplay($output_plate, "1D display")
EndFunc

Edit: Trivial format

Edited October 3, 2017 by SlackerAl

[kylomas]

Div,

How do you get the data from the server?

Kylomas

edit:

A rudimentary example using a flat file and regexp.  "*" is a single char wild card.

expandcollapse popup

#include <StaticConstants.au3>
#include <GUIConstantsEx.au3>
#include <array.au3>
#include <GuiListView.au3>

#AutoIt3Wrapper_Add_Constants=n

;_gen_test_file()

Local $gui010 = GUICreate('License Plate Search Utility')
GUICtrlCreateLabel('Enter Plate Number' & @CRLF & '(Use an "*" for wildcards)', 10, 10, 160, 30, $ss_center)
GUICtrlSetFont(-1, 9, 1200)
Local $inp010 = GUICtrlCreateInput('', 40, 50, 100, 20)
Local $btn010 = GUICtrlCreateButton('Search', 10, 380, 380, 20)
Local $lsv010 = GUICtrlCreateListView('Matches', 200, 10, 170, 300)
_GUICtrlListView_SetColumnWidth($lsv010, 0, $LVSCW_AUTOSIZE_USEHEADER)

GUISetState()

Local $platefile = FileRead(@ScriptDir & '\lic.txt')

While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $gui_event_close
            Exit
        Case $btn010
            _srch(GUICtrlRead($inp010))
    EndSwitch
WEnd


Func _srch($str)

    if stringlen($str) = 0 then return GUICtrlCreateListViewItem('Cannot be blank', $lsv010)
    $str = StringReplace($str, '*', '.')
    Local $aRET = StringRegExp($platefile, '(?i)' & $str, 3)
    If @error = 1 Then Return GUICtrlCreateListViewItem('No Matches', $lsv010)
    _GUICtrlListView_DeleteAllItems($lsv010)
    For $1 = 0 To UBound($aRET) - 1
        GUICtrlCreateListViewItem($aRET[$1], $lsv010)
    Next

EndFunc   ;==>_srch


Func _gen_test_file()

    ; generate a flat file of 50000 lic plates in 3 alpha by 4 numeric format

    Local $hOutFile = FileOpen(@ScriptDir & '\lic.txt', 2)
    If $hOutFile = -1 Then Exit MsgBox(17, 'ERROR', 'Error opening file = ' & @ScriptDir & '\lic.txt')

    Local $str = ''
    For $1 = 1 To 50000
        For $2 = 1 To 3
            $str &= Chr(Random(65, 90, 1))
        Next
        For $2 = 1 To 4
            $str &= Random(0, 9, 1)
        Next
        $str &= @CRLF
    Next

    FileWrite($hOutFile, $str)
    FileClose($hOutFile)

EndFunc   ;==>_gen_test_file

 

Edited October 3, 2017 by kylomas
example code

[Div]

The data is gathered through a wc3270 emulator communicating with a DMV mainframe. 

You'd think that partial plate search is a function we'd have as a detective. You'd be wrong. 

[Div]

SlackAI,

 

I used your code and it works flawlessly! I never considered the recursive function as an answer and man it works well. I'll post the final code when I'm done with the parsing of the data.

 

Thanks a million brother!

[Div]

As promised, here is the full code:

 

expandcollapse popup

#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>


 Global $iPID
 Global $SaveFile = "C:\Users\" & @UserName & "\Documents\platesearch.csv"
 Global $Plate[7]
 Global $output_plate[7]
 Global $SearchPlates
 Global $iSocket
 Global $HitPlates = "PLATE,MAKE,MODEL,YEAR,CITY" & @CR

 GetPartial()

 $iPID = Run("C:\wc3270\wc3270.exe +S -scriptport 65432 ""C:\ProgramData\wc3270\State Mainframe.wc3270""","C:\ProgramData\wc3270\",@SW_SHOW, $STDIN_CHILD + $STDOUT_CHILD)

If $iPID = 0 Then
    MsgBox($MB_ICONINFORMATION, "Error", "Couldn't open the file.")
    Exit
 EndIf



Sleep(1000) ;Wait for TCp Connection to Establish

StartTCP()

 Func StartTCP()

     TCPStartup() ; Start the TCP service.

    ; Register OnAutoItExit to be called when the script is closed.
    OnAutoItExitRegister("OnAutoItExit")

    ; Assign Local variables the loopback IP Address and the Port.
    Local $sIPAddress = "127.0.0.1" ; This IP Address only works for testing on your own computer.
    Local $iPort = 65432 ; Port used for the connection.

    ; Assign a Local variable the socket and connect to a Listening socket with the IP Address and Port specified.
    $iSocket = TCPConnect($sIPAddress, $iPort)

    ; If an error occurred display the error code and return False.dmv
    If @error Then
        ; The server is probably offline/port is not opened on the server.
        Local $iError = @error
        MsgBox(BitOR($MB_SYSTEMMODAL, $MB_ICONHAND), "", "Could not connect, Error code: " & $iError)
        Return False
    Else
        ;MsgBox($MB_SYSTEMMODAL, "Connected", "Connection to server succesful")
    EndIf

      ;#### Get to DMV Module #####
      Sleep(2000)
      TCPSend($iSocket, "String(dmv)" & @LF)
      Sleep(500)
      TCPSend($iSocket, "enter" & @LF)

      ;#### Get Username and PW #####
      $username = InputBox("DMV Username", "Username:")
      $password = InputBox("DMV Password", "Password:","","*")

      ;#### Send Login Information #####
      Sleep(500)
      TCPSend($iSocket, "String("& $username & ")" & @LF)
      Sleep(500)
      TCPSend($iSocket, "enter" & @LF)

      Sleep(500)
      TCPSend($iSocket, "String("& $password & ")" & @LF)
      Sleep(500)
      TCPSend($iSocket, "enter" & @LF)

      ;#### Get to Main Screen -> Search Screen #####
      Sleep(500)
      TCPSend($iSocket, "enter" & @LF)
      Sleep(500)
      TCPSend($iSocket, "String(o)" & @LF)
      Sleep(500)
      TCPSend($iSocket, "enter" & @LF)

      ;#### Start TCP Stream Loop ####
      While 1
         Sleep(500)
         $iCommand = "Ascii"

         TCPSend($iSocket, $iCommand & @LF)
            If @error Then
               $iError = @error
               MsgBox(BitOR($MB_SYSTEMMODAL, $MB_ICONHAND), "", "Client:" & @CRLF & "Could not send the data, Error code: " & $iError)
               Return False
            EndIf


         $Screen = TCPRecv( $iSocket, "1000")
            If @error Then
               ; The server is probably offline/port is not opened on the server.
               Local $iError = @error
               MsgBox(BitOR($MB_SYSTEMMODAL, $MB_ICONHAND), "", "Could not connect, Error code: " & $iError)
               Return False
            EndIf

         If StringInStr($Screen, "M.  VEHICLE INQUIRY") = True Then
            TCPSend($iSocket, "String(m)" & @LF)
            Sleep(500)

            PlateBuild(0)
            StartSearch()
            SaveData()
            Exit
         EndIf
      ;ConsoleWrite($Screen)

      WEnd
   EndFunc

Func GetPartial()
   Local $wilds = 0
   $iString = InputBox("Partial Plate", "Input the Partial Plate you wish to search for. Use * for wildcards. The maximum wildcards you can search is 4." & @CR & @CR & "For example: WV***67")
   If StringLen($iString) <> 7 Then
        MsgBox($MB_SYSTEMMODAL, "Error!", "A standard Virginia license plate has 7 characters. You have input more or less than 7 characters, including wildcards.")
        Exit
   EndIf

   $RawPlate = StringSplit($iString,"")

   For $x = 1 to 7
      $plate[$x-1] = $RawPlate[$x]
      If $RawPlate[$x] = "*" Then
         $wilds = $wilds + 1
      EndIf
   Next

   If $wilds > 4 Then
      MsgBox($MB_SYSTEMMODAL, "Error!", "Your search has too many wildcards! Try again.")
      Exit
   EndIf
EndFunc

Func PlateBuild($iPos)
      If $Plate[$iPos] = "*" Then
         Local $iStart = 65
         Local $iFinish = 90
         If $iPos > 2 Then
            $iStart = 48
            $iFinish = 57
         EndIf
         For $i = $iStart To $iFinish
            $output_plate[$iPos] = Chr($i)
            If $iPos < 6 Then
               PlateBuild($iPos + 1)
            Else
               ProcessPlate()
            EndIf
         Next
      Else
         $output_plate[$iPos] = $plate[$iPos]
         If $iPos < 6 Then
            PlateBuild($iPos + 1)
         Else
            ProcessPlate()
         EndIf
      EndIf
   EndFunc

Func ProcessPlate()
   $String = ""
   For $u = 0 to 6
      $String = $String & $output_plate[$u]
   Next
   $SearchPlates = $SearchPlates & $String & ","
EndFunc

Func StartSearch()
   $SearchArray = StringSplit($SearchPlates, ",")
   Local $PlateHit[$SearchArray[0]]
   $Time = (($SearchArray[0] - 1)*2.5)/60
   $Time = Round($Time,0)
   MsgBox($MB_SYSTEMMODAL, "About to Begin Search", "There are " & $SearchArray[0] - 1 & " plates to be searched. This will take approximately " & $time & " minutes to complete.")


   For $x = 1 to $SearchArray[0] - 1
      Local $command = "String(" & $SearchArray[$x] & ")"
      Local $wait = 250
      Local $Screen

      TCPSend($iSocket, "Ascii" & @LF)
      $Screen = TCPRecv($iSocket, 10000)
      Sleep($wait)

      For $t = 0 to 4
         TCPSend($iSocket, "tab" & @LF)
         Sleep($wait)
      Next

      TCPSend($iSocket, $command & @LF)
      Sleep($wait)
      TCPSend($iSocket, "enter" & @LF)
      Sleep($wait)
      TCPSend($iSocket, "Ascii" & @LF)
      Sleep($wait)

      $Screen = TCPRecv($iSocket, 10000)

      ;MsgBox($MB_SYSTEMMODAL, "Debug!", "Plate string sent!" & @CR & @CR & $Screen)

      If StringInStr($Screen, "VEHINQ - VEHICLE INQUIRY (PAGE 1)") = True Then
         $Trim = StringInStr($Screen, "VEHINQ - VEHICLE INQUIRY (PAGE 1)")
         $Screen = StringTrimLeft($Screen, $Trim - 1)
         ;ConsoleWrite($Screen)
         GetData($Screen, $SearchArray[$x])
         ;Exit
         For $h = 0 to 2
            Sleep($wait)
            TCPSend($iSocket, "enter" & @LF)
         Next
            TCPSend($iSocket, "String(m)" & @LF)
            Sleep($wait)
      Else
         TCPSend($iSocket, "String(m)" & @LF)
         Sleep($wait)
      EndIf

   Next
EndFunc

Func GetData($Parse,$Plate)
   Local $Make, $Model, $Year, $City
   ;##### String Math for Makes #####
   $i = StringInStr($Parse, "MKE: ")
   $Make = StringTrimLeft($Parse, $i-1)
   $Make = StringLeft($Make, 15)
   $Make = StringStripWS($Make,2)
   $Make = StringTrimLeft($Make, 5)

   ;##### String Math for Models #####
   $i = StringInStr($Parse, "MODEL: ")
   $Model = StringTrimLeft($Parse, $i-1)
   $Model = StringLeft($Model, 24)
   $Model = StringStripWS($Model,2)
   $Model = StringTrimLeft($Model, 7)


   ;##### String Math for Year #####
   $i = StringInStr($Parse, "YR: ")
   $Year = StringTrimLeft($Parse, $i-1)
   $Year = StringLeft($Year, 8)
   $Year = StringStripWS($Year,2)
   $Year = StringTrimLeft($Year, 4)


   ;##### String Math for City #####
   $i = StringInStr($Parse, "CITY: ")
   $City = StringTrimLeft($Parse, $i-1)
   $City = StringLeft($City, 36)
   $City = StringStripWS($City,2)
   $City = StringTrimLeft($City, 6)

   ;#### Put Match Into Final String ####
   $HitPlates = $HitPlates & $Plate & "," & $Make & "," & $Model & "," & $Year & "," & $City & @CR
EndFunc

Func SaveData()
   Local $FileName = FileOpen($SaveFile,2)

FileWrite($FileName, $HitPlates)
FileClose($FileName)
ProcessClose($iPID)
Run("C:\Program Files\Microsoft Office\Office14\excel.exe " & $SaveFile)
EndFunc

 

[kylomas]

Hi Div...you in Milwakee?

[Div]

Nope, Virginia.