Visual Basic

Imports System.IO
Public Class Form1

    Dim TheFile As Microsoft.Office.Interop.Excel.Workbook
    Dim SaveFile As StreamWriter
    Dim Files() As String
    '  Dim filebrowser As New FolderBrowserDialog
    Dim intAccountNumber As Integer
    Dim decTotal As Decimal


    Private Sub btnexit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexit.Click
        'Closes the application
        Me.Close()
    End Sub

    Private Sub btnshowthefile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnshowthefile.Click
        'Shows the spreadsheet
        TheFile = GetObject("G:\data\dr.xls")
        TheFile.Application.Visible = True
        TheFile.Windows(1).Visible = True
        TheFile.Application.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized

    End Sub

    Private Sub btnbrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbrowse.Click


        FolderBrowserDialog1.Description = "Select location of file"
        FolderBrowserDialog1.ShowNewFolderButton = False
        FolderBrowserDialog1.RootFolder = Environment.SpecialFolder.MyComputer

        'FolderBrowserDialog1.Filter = "Excel files (*.xls)|*.xls"
        FolderBrowserDialog1.ShowDialog()
        Dim fpath As String = FolderBrowserDialog1.SelectedPath()
        Dim tfiles() As String
        Dim files() As String
        Dim count As Integer
        tfiles = Directory.GetFiles(fpath)
        files = tfiles
        For count = 0 To files.Length() - 1
            ListBox1.Items.Add(files(count))
        Next


        SaveFile = File.CreateText("G:\data\dr.xls")
    End Sub

    Function findAccountNumber()

        'To find the account number
        Try

            If TheFile.Worksheets("sheet1").cells("9", "f").value = ("State") Then
                intAccountNumber = TheFile.Worksheets("sheet1").cells("10", "f").value
            Else
                intAccountNumber = TheFile.Worksheets("sheet1").cells("10", "g").value
            End If

        Catch ex As Exception
            intAccountNumber = 0
        End Try
        Return intAccountNumber

    End Function

    Function findthetotal()

        Dim intTotalSearch As Integer = 0

        'find the total
        
        Try
            For intTotalSearch = 0 To 90
                If TheFile.Worksheets("sheet1").cells(intTotalSearch, "g").value = ("Total") Then
                    decTotal = TheFile.Worksheets("sheet1").cells(intTotalSearch, "h").value
                Else
                    MessageBox.Show("Wrong")
                End If
            Next intTotalSearch
        Catch ex As Exception
            decTotal = 0
        End Try
        Return decTotal
    End Function

    Private Sub btnaccountinformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaccountinformation.Click

        Try
            For Each item As String In Files

                findAccountNumber()
                findthetotal()

                'ListBox2.Items.Add("Account # " & intAccountNumber & " Total " & decTotal)

                ListBox2.Items.Add("Account # " & findAccountNumber() & " Total " & findthetotal())

                'To append
                TheFile.AppendFile(intAccountNumber & vbTab & decTotal & vbCrLf)
            Next
        Catch ex As Exception

        End Try


    End Sub

Private Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick



        Dim api As New RfidApiLib.RfidApi
        Dim status1 As Integer

        Dim StrCon As String
        Dim conn As SqlConnection
        Dim host As String
        'Dim status1 As Integer
        test.WriteByDate("e:\temp", "log", "service", "reader timer this connected") ' Writes to log file
        'mylog.WriteEntry("Connected", EventLogEntryType.Information)

        StrCon = "data source=XXX;initial catalog=XXX; uid=XXX; pwd=XXX;"
        conn = New SqlConnection(StrCon)

        conn.Open()

        Dim outadapter1 As New SqlDataAdapter("select * from dbo.ReaderInfo where dbo.ReaderInfo.Reader_location in (Select Reader_location from dbo.Reader_Status )", conn)
        Dim ds1 As New DataSet()
        outadapter1.Fill(ds1, "dbo.ReaderInfo")

        Dim outadapter2 As New SqlDataAdapter("Select * from dbo.Reader_Status where Remarks = 'C'", conn)
        Dim ds2 As New DataSet()
        outadapter1.Fill(ds2, "dbo.ReaderInfo")

        For i As Integer = 0 To ds2.Tables(0).Rows.Count - 1

            host = ds1.Tables(0).Rows(i)("ReaderIP").ToString
            Try
                'My.Computer.Network.Ping(host)
                Dim ping As New System.Net.NetworkInformation.Ping()
                Dim pingReply As System.Net.NetworkInformation.PingReply = ping.Send(host) 'ping ipaddress

                'test.WriteByDate("e:\temp", "log", "service", "reader" & ds1.Tables(0).Rows(i)("Reader_location").ToString & "disconnected" & e1.Message)

            Catch e1 As System.Net.NetworkInformation.PingException 'if not connected try reconnecting

                status1 = api.TcpConnectReader(ds1.Tables(0).Rows(i)("ReaderIP").ToString, ds1.Tables(0).Rows(i)("ReaderPort")) 'API call to connect to device

                If status1 = 0 Then ' successfully connected
                    Exit For

                Else
                    test.WriteByDate("e:\temp", "log", "service", "reader" & ds1.Tables(0).Rows(i)("Reader_location").ToString & "disconnected" & e1.Message) 'otherwise write to log
                    GoTo nxt
                End If



            End Try

nxt:    Next

End Sub
        conn.Close()