Visual Email Capacity Management and Monitoring (Part 1)

Imports System.Net.Security
Imports System.Security.Cryptography.X509Certificates
Imports Microsoft.Exchange.WebServices
Imports Microsoft.Exchange.WebServices.Data
Imports System.Net
Imports System.Web
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Collections.Generic
Imports System.Xml.Linq
Imports System.IO

Public Class Form1
    'Dim ewsurl As String
    'Dim service As New ExchangeServiceBinding
    Dim sqlins As String
    'Dim dbconn As New System.Data.OleDb.OleDbConnection
    'Dim dbcomm As System.Data.OleDb.OleDbCommand
    Dim dbconn As New SqlConnection
    Dim dbcomm As SqlCommand
   Dim dbpath1 As String = "Server=SERVERNAME\SQLEXPRESS;Database=DB_NAME;Trusted_Connection=True;"


    Dim run_time As Integer
    Dim mailboxes(,) As String

    'The delegate
    Delegate Sub SetLabelText_Delegate(ByVal [Label] As Label, ByVal [text] As String)

    ' The delegates subroutine.
    Private Sub SetLabelText_ThreadSafe(ByVal [Label] As Label, ByVal [text] As String)
        ' InvokeRequired required compares the thread ID of the calling thread to the thread ID of the creating thread.
        ' If these threads are different, it returns true.
        If [Label].InvokeRequired Then
            Dim MyDelegate As New SetLabelText_Delegate(AddressOf SetLabelText_ThreadSafe)
            Me.Invoke(MyDelegate, New Object() {[Label], [text]})
        Else
            [Label].Text = [text]
        End If
    End Sub
    Delegate Sub SettextboxText_Delegate(ByVal [Textbox] As TextBox, ByVal [text] As String)

    ' The delegates subroutine.
    Private Sub SettextboxText_ThreadSafe(ByVal [Textbox] As TextBox, ByVal [text] As String)
        ' InvokeRequired required compares the thread ID of the calling thread to the thread ID of the creating thread.
        ' If these threads are different, it returns true.
        If [Textbox].InvokeRequired Then
            Dim MyDelegate1 As New SettextboxText_Delegate(AddressOf SettextboxText_ThreadSafe)
            Me.Invoke(MyDelegate1, New Object() {Textbox, [text]})
        Else
            [Textbox].Text = [text]
        End If
    End Sub
    Private Sub btn_clear_Click(sender As System.Object, e As System.EventArgs) Handles btn_clear.Click
        txt_details.Clear()
        txt_err.Clear()
        lbl_status.Text = ""
        lbl_runtime.Text = ""

    End Sub

    Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        'has the backgroundworker been asked to stop?
        If BackgroundWorker1.CancellationPending Then
            'set cancel to true
            e.Cancel = True
            Exit Sub
        End If
        SetLabelText_ThreadSafe(Me.lbl_status, "...Started..")
        Try '//A
            dbconn = New SqlConnection(dbpath1) ' + dbpath2)
            dbconn.Open()
        Catch ex As Exception
            SettextboxText_ThreadSafe(Me.txt_err, ex.Message.ToString & " A")
        End Try
        Dim myexchange As New ExchangeService(ExchangeVersion.Exchange2010)
        Dim myversion As New ExchangeVersion
        myexchange.Credentials = New NetworkCredential(txt_uid.Text, txt_pwd.Text)
        myexchange.Url = New Uri("https://SERVER.DOMAIN.COM/EWS/Exchange.asmx")
        Dim inboxfolder As New Folder(myexchange)

        Dim mb As Mailbox
        Dim fid1 As FolderId
        SettextboxText_ThreadSafe(txt_details, "")
        Dim bound0 As Integer = mailboxes.GetUpperBound(0)
        Dim bound1 As Integer = mailboxes.GetUpperBound(1)
        Dim emailid As String
        Dim cntryname As String


        For i As Integer = 0 To bound0
            emailid = mailboxes(i, 0)

            cntryname = mailboxes(i, 1)
            mb = New Mailbox(emailid)


            Dim iv_old As ItemView = New ItemView(999)
            Dim oldmail As Integer
            Dim newmail As Integer

            Dim filters_old As SearchFilter = New SearchFilter.IsLessThan(ItemSchema.DateTimeReceived, DateTime.Today.AddHours(-24))
            Dim olditems As FindItemsResults(Of Item) = Nothing
            Try
                fid1 = New FolderId(WellKnownFolderName.Inbox, mb.Address)
                inboxfolder = folder.Bind(myexchange, fid1)

                iv_old.Traversal = ItemTraversal.Shallow

                olditems = myexchange.FindItems(inboxfolder.Id, filters_old, iv_old)
                oldmail = olditems.Items.Count
                iv_old = Nothing

                sqlins = "UPDATE tbl_TABLENAME SET ageing = " & oldmail & " WHERE co_code = '" & cntryname & "'"
                dbcomm = New SqlCommand(sqlins, dbconn)
                dbcomm.ExecuteNonQuery()
            Catch ex As Exception
                SettextboxText_ThreadSafe(Me.txt_err, Me.txt_err.Text & ex.Message.ToString & cntryname & " : C " & "| TRYING AGAIN" & vbNewLine)

            Finally

            End Try

            Dim iv_new As ItemView = New ItemView(999)
            iv_new.Traversal = ItemTraversal.Shallow
            Dim filters_new As SearchFilter = New SearchFilter.IsGreaterThanOrEqualTo(ItemSchema.DateTimeReceived, DateTime.Today.AddHours(-24))
            Dim newitems As FindItemsResults(Of Item) = Nothing
            newitems = myexchange.FindItems(inboxfolder.Id, filters_new, iv_new)
            newmail = newitems.Items.Count
            iv_new = Nothing

            Try
                sqlins = "UPDATE tbl_TABLENAME SET pending = " & newmail & " WHERE co_code = '" & cntryname & "'"
                dbcomm = New SqlCommand(sqlins, dbconn)
                dbcomm.ExecuteNonQuery()

            Catch ex As Exception
                SettextboxText_ThreadSafe(Me.txt_err, Me.txt_err.Text & ex.Message.ToString & cntryname & " : C " & "| TRYING AGAIN" & vbNewLine)
            Finally

            End Try
            SettextboxText_ThreadSafe(Me.txt_details, txt_details.Text + emailid + " : " + cntryname + " : " + newmail.ToString + " : " + oldmail.ToString + vbNewLine)

        Next
        mb = vbNull
        inboxfolder = Nothing
        fid1 = Nothing
        If dbconn.State = ConnectionState.Open Then
            dbconn.Close()
        End If
        SetLabelText_ThreadSafe(Me.lbl_runtime, lbl_runtime.Text + 1)
    End Sub
    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        Try
            If BackgroundWorker1.IsBusy Then
                lbl_status.Text = "Process busy, will try later."
                Exit Sub
            Else
                BackgroundWorker1.RunWorkerAsync()
            End If
        Catch ex As Exception '//D
            txt_err.Text = txt_err.Text & vbNewLine & ex.Message.ToString & " D"
        End Try
    End Sub

    Private Sub btn_stop_Click(sender As System.Object, e As System.EventArgs) Handles btn_stop.Click
        'is the background worker doing some work?
        Timer1.Stop()
        If BackgroundWorker1.IsBusy Then
            'if it supports cancellation, cancel it
            If BackgroundWorker1.WorkerSupportsCancellation Then
                'tell the background worker to stop working
                BackgroundWorker1.CancelAsync()
                Timer1.Stop()
            End If
        End If

        'enable the start button
        Me.btn_start.Enabled = True
        'dissable the stop button
        Me.btn_stop.Enabled = False
    End Sub

    Private Sub btn_start_Click(sender As Object, e As System.EventArgs) Handles btn_start.Click
        'dissable the start button
        Me.btn_start.Enabled = False
        Me.lbl_runtime.Text = 0
        'enable the stop button
        Me.btn_stop.Enabled = True
        txt_err.Text = ""
        Timer1.Enabled = True

        Timer1.Start()

        'start the background worker working
        BackgroundWorker1.RunWorkerAsync()
    End Sub
    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        If e.Cancelled Then
            Me.lbl_status.Text = "Cancelled"
        Else
            Me.lbl_status.Text = "Completed"
            If Not dbconn.State = ConnectionState.Closed Then
                dbconn.Close()
            End If

            ' Me.ProgressBar1.Value = 0
        End If
    End Sub
  
    Private Sub LoadConfiguration()
        If File.Exists(Application.StartupPath + "\Configuration.xml") Then
            'load from settings
            Dim xdoc As XDocument = XDocument.Load(Application.StartupPath + "\Configuration.xml")

            Dim config = From con In xdoc.Descendants("Configuration")
                         Select New With { _
         .RefreshInterval = con.Element("RefreshInterval").Value}

            For Each c As Object In config
                Timer1.Interval = (c.RefreshInterval * 60 * 1000)
            Next
        End If
    End Sub

    Private Sub LoadEmailList()
        If File.Exists(Application.StartupPath + "\EmailSettings.xml") Then
            'load from settings
            Dim xdoc As XDocument = XDocument.Load(Application.StartupPath + "\EmailSettings.xml")

            Dim mapping = From map In xdoc.Descendants("EmailMap")
                          Select New With { _
              .EmailAddress = map.Element("Email").Value, _
              .DatabaseTable = map.Element("DatabaseTable").Value _
            }

            ReDim mailboxes(mapping.Count - 1, 2)
            For m As Integer = 0 To mapping.Count() - 1
                mailboxes(m, 0) = mapping(m).EmailAddress.Trim
                mailboxes(m, 1) = mapping(m).DatabaseTable.Trim

            Next
        End If
    End Sub

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        LoadEmailList()
        LoadConfiguration()
    End Sub
End Class