Imports System
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.IO
Imports System.Diagnostics

Module Module1

Dim myConnection As MySqlConnection
Dim MySQLConnectionString As String
Dim CommandLineArgs As System.Collections.ObjectModel.ReadOnlyCollection(Of String) = My.Application.CommandLineArgs
Dim FILE_NAME As String = Nothing
Dim dir As String = "C:\Mytest\"
Dim strDate As String = Nothing
Dim serv_type As String = Nothing
Dim strSubservice As String = Nothing
Dim member_no As String = Nothing
Dim strIndicator As String = Nothing
Dim strDirection As String = Nothing
Dim file_num As String = Nothing
Dim origin As String = Nothing
Dim transac_Type As String
Dim beginDayAck As String = "51"
Dim beginDayNack As String = "61"
Dim fileAck As String = "52"
Dim fileNack As String = "62"
Dim endDayAck As String = "59"
Dim endDayNack As String = "69"
Dim error_code As String
Dim recID As String = "00"
Dim num_records As String
Public watchfolder As FileSystemWatcher



Sub Main(ByVal args As String())


    MySQLConnectionString = "Server=localhost;" & _
                        "Database=lebo_test;" & _
                        "Userid=root;" & _
                        "Password=12345;"
    myConnection = New MySqlConnection(MySQLConnectionString)


    For i As Integer = 0 To CommandLineArgs.Count - 1
        If i = 0 Then
            FILE_NAME = (CommandLineArgs(i))
        End If

    Next



        Dim last_char As String = FILE_NAME.Substring(7, 1)
        Dim last_three As String = FILE_NAME.Substring(5, 3)
        Console.WriteLine("lastchar is: " + last_char)

        If (last_char.Equals("D")) Then
            Console.WriteLine("here")
            Data_Validation()
        Else
            If (last_three.Equals("Z1C")) Then
                Z1C()

            ElseIf (last_three.Equals("Z9C")) Then
                Z9C()
            Else
                '    Call Z7C()
            End If
        End If



End Sub

Public Sub Nack(ByRef error_code As String)

    Dim NACKFILE As String = FILE_NAME.Substring(0, 7) + "N"

    Using writer As StreamWriter = New StreamWriter(dir + "\output\" + NACKFILE, False)
        writer.Write(recID + transac_Type + strDate + serv_type + " " + strSubservice + " " + " " + member_no +
        origin + strDirection + " " + strIndicator + (FILE_NAME.Substring(0, 7) + "N") + file_num + error_code)
    End Using

    'FileOpen(1, dir + NACKFILE, OpenMode.Output)
    'PrintLine(1, "lebo testing")
    'PrintLine(1, "LEBO AGAIN and AGAIN LEBO")
    'FileClose(1)

End Sub

Public Sub Ack(ByRef error_code As String)

    Dim ACKFILE As String = FILE_NAME.Substring(0, 7) + "A"

    Using writer As StreamWriter = New StreamWriter(dir + "\output\" + ACKFILE, False)
        writer.Write(recID + transac_Type + strDate + serv_type + " " + strSubservice + " " + member_no +
        origin + strDirection + " " + strIndicator + (FILE_NAME.Substring(0, 7) + "A") + file_num + error_code)
    End Using
End Sub
Public Sub Data_Validation()

    'Dim FILE_NAME As String = Nothing
    '"C:\Users\LebohangL\Desktop\Lebo\DEND001D"
    'Dim dr As String = "C:\Users\LebohangL\Desktop\Lebo"
    Dim TextLine As String = Nothing
    Dim num As Integer = 0
    'Dim strDate As String = Nothing
    Dim processing_date As String = Nothing
    'Dim CommandLineArgs As System.Collections.ObjectModel.ReadOnlyCollection(Of String) = My.Application.CommandLineArgs
    Dim service_type As String = Nothing
    'Dim serv_type As String = Nothing
    'Dim strSubservice As String = Nothing
    Dim subservice As String = Nothing
    Dim indicator As String = Nothing
    Dim direction As String = Nothing
    'Dim strIndicator As String = Nothing
    'Dim strDirection As String = Nothing
    Dim user_no As String = Nothing
    'Dim member_no As String = Nothing
    Dim recordID As String = Nothing
    Dim executeDA As MySqlDataAdapter
    Dim datacombo As New DataTable
    'Dim myConnection As MySqlConnection
    'Dim MySQLConnectionString As String
    'MySQLConnectionString = "Server=localhost;" & _
    '                    "Database=lebo_test;" & _
    '                    "Userid=root;" & _
    '                    "Password=12345;"
    'myConnection = New MySqlConnection(MySQLConnectionString)


    'For i As Integer = 0 To CommandLineArgs.Count - 1
    '      If i = 0 Then
    '        FILE_NAME = (CommandLineArgs(i))
    '    End If

    'Next
    datacombo = New DataTable



    If System.IO.File.Exists(dir + FILE_NAME) = True Then
        Dim objReader As New System.IO.StreamReader(dir + FILE_NAME)

        'reading the txt file
        Do While objReader.Peek() <> -1
            num += 1
            TextLine = objReader.ReadLine()

            If num = 1 Then
                serv_type = TextLine.Substring(10, 4).Trim
                strSubservice = TextLine.Substring(14, 10).Trim
                strDate = TextLine.Substring(2, 8)
                member_no = TextLine.Substring(24, 4)
                strIndicator = TextLine.Substring(59, 4)
                strDirection = TextLine.Substring(48, 3).Trim
                file_num = TextLine.Substring(40, 4)
                origin = TextLine.Substring(28, 4)
                recordID = TextLine.Substring(0, 2)
            End If

            'get number of records
            If num > 1 Then
                recordID = TextLine.Substring(0, 2)
            End If
            If recordID = "99" Then
                num_records = TextLine.Substring(28, 6)
                num_records = Val(num_records)

                Exit Do
            End If

        Loop


        Dim recNum As String = num
        Console.WriteLine(recNum)
        Console.WriteLine("Number of records are " + num_records)

        If recNum.ToString <> num_records.ToString Then
            transac_Type = fileNack
            Nack("22")
            Console.WriteLine("ERROR 22!!Invalid number of records")
            Exit Sub
        End If

        'Verify Service and continue validation
        Dim sqlcmd = "SELECT service_type from service where service_type = '" + serv_type + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("****************")
            Console.WriteLine("*Dataset Empty*")
            Console.WriteLine("****************")
        Else

            Dim dtRow As DataRow

            For Each dtRow In datacombo.Rows
                'subservice = dtRow("subservice")
                service_type = dtRow("service_type")

            Next
        End If



        Console.WriteLine("FileService type is: " + serv_type)

        Console.WriteLine("Service type is : " + service_type)

        If (serv_type.Equals(service_type)) Then
            transac_Type = fileAck
            'Ack("00")
            Console.WriteLine("Service Valid. Check Processing date to proceed")

        Else

            'Console.WriteLine("NACK NACK")
            transac_Type = fileNack
            Nack("03")
            Console.WriteLine("ERROR 03!! Invalid service")
            Exit Sub
        End If

        'Verify processing date
        sqlcmd = "SELECT processing_date from cleanup where service_type = '" + serv_type + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("****************")
            Console.WriteLine("Dataset Empty")
            Console.WriteLine("****************")
        Else
            For Each dtRow In datacombo.Rows
                processing_date = dtRow("processing_date")
            Next
        End If

        Console.WriteLine("File Processing Date is: " + strDate)

        Console.WriteLine("Processing Date  is : " + processing_date)

        If strDate.ToString.Equals(processing_date) Then
            transac_Type = fileAck
            'Ack("00")
            Console.WriteLine("Processing_date Valid.")

        Else

            transac_Type = fileNack
            Nack("02")
            Console.WriteLine("ERROR 02!! Invalid processing_date")
            Exit Sub
        End If

        'Is member setup for service
        sqlcmd = "SELECT user_no, service_type, subservice, direction, indicator FROM client where user_no = '" + member_no + "' AND service_type ='" + serv_type + "' AND subservice ='" + strSubservice + "' AND direction ='" + strDirection + "' AND indicator ='" + strIndicator + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("***********************************************")
            Console.WriteLine("Dataset Empty.Member not setup on client table")
            Console.WriteLine("***********************************************")
        Else

            For Each dtRow In datacombo.Rows
                user_no = dtRow("user_no")
                service_type = dtRow("service_type")
                subservice = dtRow("subservice")
                direction = dtRow("direction")
                indicator = dtRow("indicator")

            Next
        End If

        Console.WriteLine("File user number is: " + member_no)

        Console.WriteLine("User number is : " + user_no)

        Console.WriteLine("FileService type is: " + serv_type)

        Console.WriteLine("Service type is : " + service_type)
        Console.WriteLine("File subservice is:" + strSubservice)

        Console.WriteLine("Subservice is:" + subservice)

        Console.WriteLine("File direction is:" + strDirection)

        Console.WriteLine("Direction is:" + direction)

        Console.WriteLine("File indicator is:" + strIndicator)

        Console.WriteLine("Indicator is:" + indicator)



        If (member_no.ToString.Equals(user_no)) And (serv_type.Equals(service_type)) And (strDate.Equals(processing_date)) And (strSubservice.ToString.Equals(subservice)) And (strDirection.ToString.Equals(direction)) And (strIndicator.ToString.Equals(indicator)) Then

            transac_Type = fileAck
            Ack("00")
            Console.WriteLine("Member configured.")
        Else

            transac_Type = fileNack
            Nack("04")

            Console.WriteLine("ERROR 04!! Member not configured")

        End If
        Exit Sub
    Else
        Nack("92")

    End If
End Sub

Public Sub Z1C()

    Dim TextLine As String
    Dim num As Integer = 0
    Dim processing_date As String = Nothing
    Dim service_type As String = Nothing
    Dim subservice As String = Nothing
    Dim indicator As String = Nothing
    Dim direction As String = Nothing
    Dim user_no As String = Nothing
    Dim executeDA As MySqlDataAdapter
    Dim datacombo As New DataTable
    datacombo = New DataTable


    If System.IO.File.Exists(dir + FILE_NAME) = True Then
        Dim objReader As New System.IO.StreamReader(dir + FILE_NAME)

        Do While objReader.Peek() <> -1
            num += 1
            TextLine = objReader.ReadLine()

            If num = 1 Then
                serv_type = TextLine.Substring(12, 4).Trim
                strSubservice = TextLine.Substring(16, 10).Trim
                strDate = TextLine.Substring(4, 8)
                member_no = TextLine.Substring(26, 4)
                strIndicator = TextLine.Substring(37, 4)
                strDirection = TextLine.Substring(34, 3).Trim
                origin = TextLine.Substring(30, 4)
                transac_Type = TextLine.Substring(2, 4)
            End If
        Loop


        'Verify Service and continue validation
        Dim sqlcmd = "SELECT service_type from service where service_type = '" + serv_type + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("****************")
            Console.WriteLine("Dataset Empty")
            Console.WriteLine("****************")
        Else

            Dim dtRow As DataRow

            For Each dtRow In datacombo.Rows
                'subservice = dtRow("subservice")
                service_type = dtRow("service_type")

            Next
        End If

        Console.WriteLine("FileService type is: " + serv_type)

        Console.WriteLine("Service type is : " + service_type)

        If (serv_type.Equals(service_type)) Then
            transac_Type = beginDayAck
            'Ack("00")

            Console.WriteLine("Service Valid. Check Processing date to proceed")
        Else

            'Console.WriteLine("NACK NACK")
            transac_Type = beginDayNack
            Nack("03")

            Console.WriteLine("ERROR 03!! Invalid service")
            Exit Sub
        End If

        'Verify processing date
        sqlcmd = "SELECT processing_date from cleanup where service_type = '" + serv_type + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("****************")
            Console.WriteLine("Dataset Empty")
            Console.WriteLine("****************")
        Else
            For Each dtRow In datacombo.Rows
                processing_date = dtRow("processing_date")
            Next
        End If

        Console.WriteLine("File Processing Date is: " + strDate)

        Console.WriteLine("Processing Date  is : " + processing_date)

        If strDate.ToString.Equals(processing_date) Then

            transac_Type = beginDayAck
            'Ack("00")
            Console.WriteLine("Processing_date Valid.")
        Else

            transac_Type = beginDayNack
            Nack("02")
            Console.WriteLine("ERROR 02!! Invalid processing_date")
            Exit Sub
        End If

        'Is member setup for service
        sqlcmd = "SELECT user_no, service_type, subservice, direction, indicator FROM client where user_no = '" + member_no + "' AND service_type ='" + serv_type + "' AND subservice ='" + strSubservice + "' AND direction ='" + strDirection + "' AND indicator ='" + strIndicator + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("***********************************************")
            Console.WriteLine("Dataset Empty.Member not setup on client table")
            Console.WriteLine("***********************************************")
        Else

            For Each dtRow In datacombo.Rows
                user_no = dtRow("user_no")
                service_type = dtRow("service_type")
                subservice = dtRow("subservice")
                direction = dtRow("direction")
                indicator = dtRow("indicator")

            Next
        End If

        Console.WriteLine("File user number is: " + member_no)

        Console.WriteLine("User number is : " + user_no)

        Console.WriteLine("FileService type is: " + serv_type)

        Console.WriteLine("Service type is : " + service_type)
        Console.WriteLine("File subservice is:" + strSubservice)

        Console.WriteLine("Subservice is:" + subservice)

        Console.WriteLine("File direction is:" + strDirection)

        Console.WriteLine("Direction is:" + direction)

        Console.WriteLine("File indicator is:" + strIndicator)

        Console.WriteLine("Indicator is:" + indicator)



        If (member_no.ToString.Equals(user_no)) And (serv_type.Equals(service_type)) And (strDate.Equals(processing_date)) And (strSubservice.ToString.Equals(subservice)) And (strDirection.ToString.Equals(direction)) And (strIndicator.ToString.Equals(indicator)) Then
            transac_Type = beginDayAck
            Ack("00")

            Console.WriteLine("Member configured.")
        Else

            transac_Type = beginDayNack
            Nack("04")
            Console.WriteLine("ERROR 04!! Member not configured")
            Exit Sub

        End If
    Else
        Nack("92")
    End If



End Sub

Public Sub Z9C()


    Dim TextLine As String
    Dim num As Integer = 0
    Dim processing_date As String = Nothing
    Dim service_type As String = Nothing
    Dim subservice As String = Nothing
    Dim indicator As String = Nothing
    Dim direction As String = Nothing
    Dim user_no As String = Nothing
    Dim executeDA As MySqlDataAdapter
    Dim datacombo As New DataTable
    datacombo = New DataTable


    If System.IO.File.Exists(dir + FILE_NAME) = True Then
        Dim objReader As New System.IO.StreamReader(dir + FILE_NAME)

        Do While objReader.Peek() <> -1
            num += 1
            TextLine = objReader.ReadLine()

            If num = 1 Then
                serv_type = TextLine.Substring(12, 4).Trim
                strSubservice = TextLine.Substring(16, 10).Trim
                strDate = TextLine.Substring(4, 8)
                member_no = TextLine.Substring(26, 4)
                strIndicator = TextLine.Substring(37, 4)
                strDirection = TextLine.Substring(34, 3).Trim
                origin = TextLine.Substring(30, 4)

            End If
        Loop

        'Verify Service and continue validation
        Dim sqlcmd = "SELECT service_type from service where service_type = '" + serv_type + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("****************")
            Console.WriteLine("Dataset Empty")
            Console.WriteLine("****************")
        Else

            Dim dtRow As DataRow

            For Each dtRow In datacombo.Rows
                'subservice = dtRow("subservice")
                service_type = dtRow("service_type")

            Next
        End If

        Console.WriteLine("FileService type is: " + serv_type)

        Console.WriteLine("Service type is : " + service_type)

        If (serv_type.Equals(service_type)) Then
            transac_Type = endDayAck
            'Ack("00")
            Console.WriteLine("Service Valid. Check Processing date to proceed")

        Else
            transac_Type = endDayNack
            Nack("03")
            Console.WriteLine("ERROR 03!! Invalid service")

            Exit Sub
        End If

        'Verify processing date
        sqlcmd = "SELECT processing_date from cleanup where service_type = '" + serv_type + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("****************")
            Console.WriteLine("Dataset Empty")
            Console.WriteLine("****************")
        Else
            For Each dtRow In datacombo.Rows
                processing_date = dtRow("processing_date")
            Next
        End If

        Console.WriteLine("File Processing Date is: " + strDate)

        Console.WriteLine("Processing Date  is : " + processing_date)

        If strDate.ToString.Equals(processing_date) Then
            transac_Type = endDayAck
            Ack("00")

            Console.WriteLine("Processing_date Valid.")
        Else

            transac_Type = endDayNack
            Nack("02")

            Console.WriteLine("ERROR 02!! Invalid processing_date")
            Exit Sub
        End If

        'Is member setup for service
        sqlcmd = "SELECT user_no, service_type, subservice, direction, indicator FROM client where user_no = '" + member_no + "' AND service_type ='" + serv_type + "' AND subservice ='" + strSubservice + "' AND direction ='" + strDirection + "' AND indicator ='" + strIndicator + "'"
        executeDA = New MySqlDataAdapter(sqlcmd, myConnection)
        datacombo.Clear()
        executeDA.Fill(datacombo)

        If datacombo.Rows.Count = 0 Then
            Console.WriteLine("***********************************************")
            Console.WriteLine("Dataset Empty.Member not setup on client table")
            Console.WriteLine("***********************************************")
        Else

            For Each dtRow In datacombo.Rows
                user_no = dtRow("user_no")
                service_type = dtRow("service_type")
                subservice = dtRow("subservice")
                direction = dtRow("direction")
                indicator = dtRow("indicator")

            Next
        End If

        Console.WriteLine("File user number is: " + member_no)

        Console.WriteLine("User number is : " + user_no)

        Console.WriteLine("FileService type is: " + serv_type)

        Console.WriteLine("Service type is : " + service_type)

        Console.WriteLine("File subservice is:" + strSubservice)

        Console.WriteLine("Subservice is:" + subservice)

        Console.WriteLine("File direction is:" + strDirection)

        Console.WriteLine("Direction is:" + direction)

        Console.WriteLine("File indicator is:" + strIndicator)

        Console.WriteLine("Indicator is:" + indicator)


        If (member_no.ToString.Equals(user_no)) And (serv_type.Equals(service_type)) And (strSubservice.ToString.Equals(subservice)) And (strDirection.ToString.Equals(direction)) And (strIndicator.ToString.Equals(indicator)) Then

            transac_Type = endDayAck
            Ack("00")

            Console.WriteLine("Member configured.")
        Else
            transac_Type = endDayNack
            Nack("04")

            Console.WriteLine("ERROR 04!! Member not configured")
        End If

        Exit Sub
    Else
        Nack("92")
    End If
End Sub

Are you posting a code contribution, or requesting help?

If you are requesting help, it might be wise to ask a question when you post. =)

I need to monitor my folder at all times for new files coming in read them and if there aren't any my monitor must sleep for few seconds and monitor again and again.

You could start by stating the situation up front, then asking a question with what you are having trouble with. Also, you posted almost 700 lines of code, most of which is irrelevant to the question.

ok. i'm in a department where we receive an unknown # of files that needs to be read, validated and moved to a Done folder. Now I need to monitor the folder where those files come in all the time, read the file, validate and move it and get another file and do the same to it and so forth. Please help.

That's easily doable. Just another couple of questions.

How are the files being put into the "receive" folder?

This is important for determining the file availablilty. You don't want to start processing the file while it is still being transferred into the receive folder. The ideal method it for the sending process to

  1. create the destination file with a ".tmp" extension
  2. once the transfer is complete, the sender renames it to the final name

If timing isn't critical, one method is to just wait until the files is a few minutes old, however, in my experience, no matter how long a delay you use, at some poinit it will still be too short. Sometimes network transfers get delayed and sometimes processes get hung. If you use the ".tmp" approach, your scanner could send an alert if a temp file older than some threshhold is detected.

How soon after the file is received should it be processed?

If it is critical that the files be processed immediately then something like a FileSystemWatcher is probably your best bet. If timing isn't critical then you might get away with using a timer to scan the receive folder every 5, 10, 30, etc minutes. That's easier to set up than a FileSystemWatcher. Because you are moving the processed files to a "done" folder, scanning the receive folder periodically won't be an issue.

The following is an unpaid plug

If you have a few hundred dollars to spare you might want to look into adTempus from ArcanaDev. I used their scheduling software for years in our Control Centre when I was responsible for dozens of realtime processes that had to coordinate the processing of a hundred or so files every hour. The package is excellent and can be used for much more than just scheduling (server/workstation monitoring, for example) and can respond to multiple types of triggers. Their support is also second to none.

Edited 4 Years Ago by Reverend Jim

This article has been dead for over six months. Start a new discussion instead.