Imports System
Imports System.IO
Imports System.Data
Imports System.Data.OleDb


Public Class qid_search

    
    Public Function getQueryString() As String
        Dim oApp As Microsoft.Office.Interop.Outlook.Application = New Microsoft.Office.Interop.Outlook.Application()
        Dim oNS As Microsoft.Office.Interop.Outlook.NameSpace = oApp.GetNamespace("mapi")
        Dim objNS As Microsoft.Office.Interop.Outlook._NameSpace = oApp.Session
        Dim qidFolder As Microsoft.Office.Interop.Outlook.MAPIFolder = oNS.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox).Folders("QID")
        Dim oItems As Microsoft.Office.Interop.Outlook.Items = qidFolder.Items

        ' Dim mail As Microsoft.Office.Interop.Outlook.MailItem
        Dim mail As Object
        Dim email_address As String

        If (xlsFileLocation = "") Then
            MsgBox("Please select an excel file from the main screen first, and try again. Thank you", MsgBoxStyle.Critical, "QID E-mailer")
            Return Nothing
        End If

        Dim goHere As Boolean = True
        Dim getQid As String = "SELECT QID FROM [Sheet1$] WHERE [Name] = "
        Dim andVal As String = " and [Name] = "
        Dim singleQuote As String = "'"


        For i As Integer = 1 To oItems.Count

            mail = oItems.Item(i)
            'if its not a report item (returned mail) ... skip over it
            If (TypeOf mail Is Microsoft.Office.Interop.Outlook.ReportItem) Then

                email_address = extract_email_address(mail.Body, " ") ' stop char is a blank space

                Dim name As String = get_name_from_email(email_address)
                ' the word and is not in the query string and thus its the first one
                If (goHere) Then
                    getQid = getQid & singleQuote & name & singleQuote
                    goHere = False
                Else
                    getQid = getQid & andVal & singleQuote & name & singleQuote
                End If
         
            End If
        Next

        Return getQid
    End Function

    Public Sub doDataGrid()
        Dim grid As DataGrid
        Dim table As String = "[Sheet1$]"

        grid = New DataGrid
        grid.Location = New Point(7, 39)
        grid.Size = New Size(360, 276)
        grid.Enabled = False
        grid.DataSource = getDataSource()
        grid.DataMember = table
        Controls.Add(grid)


    End Sub

    Public Function getDataSource() As DataSet

        Dim dataSetx As DataSet
        Dim adapter As OleDbDataAdapter
        Dim table As String = "[Sheet1$]"


        If (conn Is Nothing) Then
            connect_to_xls(Form1.xlsLocation.Text)
            conn.Open()
        ElseIf (conn.State <> ConnectionState.Open) Then
            conn.Open()
        End If

        dataSetx = New DataSet()
        adapter = New OleDbDataAdapter(getQueryString(), conn)
        adapter.Fill(dataSetx, table)

        Return dataSetx

    End Function

    Public Function extract_email_address(ByVal email As String, ByVal stopChar As String) As String
        Dim idxOfAt As Integer
        Dim idxofSpaceatBegin As Integer
        Dim idxofSpaceatEnd As Integer
        Dim currentIdx As Integer
        Dim currentChar As Char = ""
        Dim c As Char
        Dim result As String
        Dim NewResult As String

        idxOfAt = email.IndexOf("@")

        'find the @ in the string and keep going left untill you hit a space

        idxofSpaceatBegin = email.LastIndexOf(" ", idxOfAt)
        idxofSpaceatEnd = email.IndexOf(" ", idxOfAt)
        result = email.Substring(idxofSpaceatBegin, idxofSpaceatEnd - idxofSpaceatBegin).ToString()
        NewResult = ""
        For Each c In result
            Select Case c
                Case "A" To "Z", "a" To "z", "0" To "9", "@", "."
                    NewResult = NewResult & c
                Case Else
            End Select
        Next


        Return NewResult

    End Function

    Public Function get_name_from_email(ByVal email_address As String) As String

        Dim nameSeperator As String = "."
        Dim splitArray(1) As String
        Dim fName As String
        Dim lName As String

        'split the email address at the @ sign
        splitArray = email_address.Split("@")
        'split array will now = the split of the portion of the email before the @ (firstname.lastname)
        splitArray = splitArray(0).Split(nameSeperator)
        fName = splitArray(0)
        lName = splitArray(1)

        Return lName & ", " & fName

    End Function

    Private Sub qid_search_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        getQueryString()
        doDataGrid()
    End Sub
End Class