Public Class GeoCodes

    Const OUTPUTFILENAME As String = "SQLfile"

    Dim fileCount As Integer

    Private Sub Run_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Run.Click

        Dim inputFile As String = txtFileName.Text

        If My.Computer.FileSystem.FileExists(inputFile) = False Then

            MsgBox("File cannot be found")
            Exit Sub

        End If

        Using Reader As New _
            Microsoft.VisualBasic.FileIO.TextFieldParser(inputFile) '####Input path and file from user
            Reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.FixedWidth
            Reader.SetFieldWidths(7, -1)

            Dim firstField(0 To 4) As String, secondField(0 To 4) As String ' List of second fields in group
            Dim currentRow As String()

            ' Establish Place Level and Zoom
            Dim placeLev As Integer, zoomLev As Integer
            Dim i As Integer

            ' Delete output file
            If My.Computer.FileSystem.FileExists(OUTPUTFILENAME & fileCount.ToString & ".SQL") Then

                My.Computer.FileSystem.DeleteFile(OUTPUTFILENAME & fileCount.ToString & ".SQL")

            End If

            ' Read Records

            While Not Reader.EndOfData
                Try
                    currentRow = Reader.ReadFields() ' Creates exception if no 2nd field ie 2 MAP

                    ' put next record at top of stack
                    firstField(0) = currentRow(0)
                    secondField(0) = currentRow(1)

                    ' Check to see if valid group
                    If isValidRecord(firstField(0), firstField(1), firstField(2), firstField(3), firstField(4)) Then

                        ' convert E/W (Longitude)
                        secondField(0) = convertEW(secondField(0))

                        ' convert N/S (Latitude)
                        secondField(1) = convertNS(secondField(1))

                        ' Determine Place Level 
                        placeLev = determinePlaceLevel(secondField(3))

                        ' Determine Zoom Level
                        zoomLev = determineZoomLevel(secondField(3))

                        ' Write SQL File
                        writeToSQLFile(secondField(3), secondField(0), secondField(1), zoomLev, placeLev)

                    End If

                    ' Push up group stack
                    For i = 1 To 4
                        firstField(5 - i) = firstField(4 - i)
                        secondField(5 - i) = secondField(4 - i)
                    Next

                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    ' 2 MAP will be dropped as an exception (no 2nd field)

                End Try
            End While
        End Using
    End Sub

    Private Function isValidRecord(ByVal field0 As String, ByVal field1 As String, ByVal field2 As String, ByVal field3 As String, ByVal field4 As String)

        If field0 = "3 LONG" And field1 = "3 LATI" And field2 = "2 ABBR" And field3 = "1 PLAC" And field4 = "0 _PLAC" Then
            Return True
        End If

        Return False

    End Function


    Private Function convertEW(ByVal value As String)

        ' convert E/W
        If Microsoft.VisualBasic.Left(value, 1) = "W" Then
            Return "-" & Microsoft.VisualBasic.Right(value, Len(value) - 1)
        Else
            Return Microsoft.VisualBasic.Right(value, Len(value) - 1)
        End If

    End Function

    Private Function convertNS(ByVal value As String)

        If Microsoft.VisualBasic.Left(value, 1) = "S" Then
            Return "-" & Microsoft.VisualBasic.Right(value, Len(value) - 1)
        Else
            Return Microsoft.VisualBasic.Right(value, Len(value) - 1)
        End If

    End Function

    Private Function determinePlaceLevel(ByVal value As String)

        If Microsoft.VisualBasic.Left(value, 5) = ", , ," Then
            ' Found Country
            Return 6
        Else
            If Microsoft.VisualBasic.Left(value, 3) = ", ," Then
                ' Found state
                Return 5
            Else
                If Microsoft.VisualBasic.Left(value, 1) = "," Then
                    ' Found county
                    Return 4
                Else
                    ' Found city
                    Return 3
                End If
            End If
        End If

    End Function

    Private Function determineZoomLevel(ByVal value As String)

        ' Modify the following zoom levels for personal Preference
        ' ####Input from User?####
        Dim cityZm As Integer = 12
        Dim countyZm As Integer = 9
        Dim stateZm As Integer = 6
        Dim countryZm As Integer = 3

        If Microsoft.VisualBasic.Left(value, 5) = ", , ," Then
            ' Found Country
            Return countryZm
        Else
            If Microsoft.VisualBasic.Left(value, 3) = ", ," Then
                ' Found state
                Return stateZm
            Else
                If Microsoft.VisualBasic.Left(value, 1) = "," Then
                    ' Found county
                    Return countyZm
                Else
                    ' Found city
                    Return cityZm
                End If
            End If
        End If

    End Function


    Private Sub writeToSQLFile(ByVal placeName As String, ByVal longitude As String, ByVal latitude As String, ByVal zoomLevel As Integer, ByVal placeLevel As Integer)

        Dim fileName As String = OUTPUTFILENAME & fileCount.ToString & ".SQL"

        Dim sqlStatement As String

        sqlStatement = "UPDATE tng_places SET longitude = """ & longitude _
                        & """, latitude = """ + latitude _
                        & """, zoom = " & zoomLevel.ToString() _
                        & ", placelevel = " & placeLevel.ToString() _
                        & " WHERE place = """ & placeName & """ AND LONGITUDE IS NULL;" & vbCrLf

        My.Computer.FileSystem.WriteAllText(fileName, sqlStatement, True)

    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub


    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

    End Sub


    Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click

    End Sub
End Class