Imports System.IO

Imports System.Data.SqlClient

Imports System.Windows.Forms

 

Public Class Form1

    Inherits System.Windows.Forms.Form

 

    Public objGetWUDetailRecords As SqlDataReader

 

  WithEvents t1 As Timer

  WithEvents t2 As Timer

 

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Windows.Forms.Application.DoEvents()

        t1 = New Timer()

        t1.Interval = 100

        t1.Start()

        t2 = New Timer()

        t2.Interval = 100

        t2.Start()

    End Sub

 

 

    Private Sub t1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles t1.Tick

        Static t As Integer = 60000

        Static x As Integer

        t -= 100

        TextBox1.Text = "Waiting for timer..."

        x = t / 1000

        lblTimerDn.Text = CStr(x \ 60) & ":" & Format$(x Mod 60, "00")

        If t = 0 Then

            t = 60000

            t1.Stop()

 

            'Now check the hour to decide to run or not

            Select Case Hour(Now())

                'Returns an Integer value from 0 through 23 representing the hour of the day.

                'If the hour is between 06 and 19, go ahead and rum the app

            Case 6 To 19

                    TextBox1.Text = "PROCESSING..."

                    lblTimerDn.Text = "Wait!"

                    GetWUFiles() ' do something!

                    t1.Start()

                Case Else

                    t1.Start()

            End Select

        End If

    End Sub

 

    Private Sub t2_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles t2.Tick

        Static tt As Integer = 900000

        Static xx As Integer

        tt -= 100

        TextBox1.Text = "Waiting for timer..."

        xx = tt / 1000

        lblTimerUp.Text = CStr(xx \ 60) & ":" & Format$(xx Mod 60, "00")

        If tt = 0 Then

            tt = 900000

            t2.Stop()

 

            'Now check the hour to decide to run or not

            Select Case Hour(Now())

                'Returns an Integer value from 0 through 23 representing the hour of the day.

                'If the hour is between 06 and 19, go ahead and rum the app

            Case 6 To 19

                    TextBox1.Text = "PROCESSING..."

                    lblTimerUp.Text = "Wait!"

                    GetWUDetailRecords() ' do something!

                    t2.Start()

                    'MessageBox.Show(Hour(Now()))

                Case Else

                    t2.Start()

            End Select

        End If

    End Sub

 

    Private Sub cmdScan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdScan.Click

        Windows.Forms.Application.DoEvents()

        t1.Stop()

        t2.Stop()

        TextBox1.Text = "PROCESSING..."

        lblTimerDn.Text = "Wait!"

        lblTimerUp.Text = "Wait!"

        GetWUFiles()

        GetWUDetailRecords()

        t1.Start()

        t2.Start()

    End Sub

 

    Private Function GetWUFiles()

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

        'reset the public vars

        gGetFromFileName_w_Path = ""

        gGetToFileName_w_LocalDir = ""

 

        'Download any new files and process them from Primary site MO-228

        gURL = "xxx.xxx.xxx.xxx"

        gUserName = "communication"

        gPassword = "password"

        gCDto = "out"       'watch the slash

 

        'Establish Database Access For Query the database and check for files already downloaded

        Dim objQConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objQConnection.ConnectionString = strConnection

        objQConnection.Open()

 

        'Query Database and check if already downloaded file

        Dim objQCommand As New SqlCommand()

        objQCommand.CommandType = CommandType.StoredProcedure

        objQCommand.CommandText = "ap_CheckDownloadedWUFiles"

        objQCommand.CommandTimeout = 120

        objQCommand.Connection = objQConnection

        Dim WUFileName As SqlParameter = objQCommand.Parameters.Add("@wufilename", SqlDbType.VarChar, 25)

        Dim isFound As SqlParameter = objQCommand.Parameters.Add("@isfound", SqlDbType.Bit)

        isFound.Direction = ParameterDirection.Output

 

        'Get a directory listing from WU and loop thru checking against downloaded files.

        Dim hInternet As IntPtr = InternetOpen(Nothing, INTERNET_OPEN_TYPE_DIRECT, Nothing, Nothing, 0)

        Dim hSession As IntPtr = InternetConnect(hInternet, gURL, INTERNET_DEFAULT_FTP_PORT, gUserName, gPassword, INTERNET_SERVICE_FTP, 0, IntPtr.Zero)

        Dim hgCDto As Boolean = FtpSetCurrentDirectory(hSession, gCDto)

        Dim finddata As WIN32_FIND_DATA

 

        Dim hFind As IntPtr = FtpFindFirstFile(hSession, Nothing, finddata, 0, IntPtr.Zero)

        Dim more As Boolean = True

 

        Do While more

 

            TextBox1.Text = "PROCESSING..."

            If Not CBool(finddata.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) Then

                'Get the first filename from the WU directory

                'Send the filename to the query and see if it is already downloaded

                If finddata.cFileName & "" = "" Then

                    Exit Do

                Else

                    WUFileName.Value = finddata.cFileName

                    lWUFileName = finddata.cFileName

                    lWUFileDate = FileTimeToDate(finddata.ftLastWriteTime).ToShortDateString

                    objQCommand.ExecuteNonQuery()

                End If

                If isFound.Value = False Then

                    'not downloaded yet - get the file and log it and then process it

                     gGetFromFileName_w_Path = gCDto & "/" & finddata.cFileName            '"upload/MyFile.txt"

 gGetToFileName_w_LocalDir = "\\Halo\E\Work\OrderManage\WesternUnion\In\" & finddata.cFileName   '"C:\Temp\MyFile.txt"    

                    Dim passedname As String = ""

                    passedname = finddata.cFileName 'pass the filename to the download function

                    sharedhSession = hSession 'share the connection with the download function

                    Download(passedname)

                End If

            End If

            more = InternetFindNextFile(hFind, finddata)

        Loop 'check the next file

 

        'Kill Connections And Objects

        InternetCloseHandle(hFind)

        InternetCloseHandle(hSession)

        InternetCloseHandle(hInternet)

 

        objQConnection.Close()

        objQConnection = Nothing

        objQCommand = Nothing

 

        GetWUFiles2()

 

    End Function

 

    Private Function GetWUFiles2()      'Check the other FTP site

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

 

        'reset the vars

        gGetFromFileName_w_Path = ""

        gGetToFileName_w_LocalDir = ""

 

        'Download any new files and process them from Secondary site TX-227

           gURL = "xxx.xxx.xxx.xxx"

        gUserName = "communication"

        gPassword = "password"

        gCDto = "out"       'watch the slash

 

        'Establish Database Access For Query the database and check for files already downloaded

        Dim objQConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objQConnection.ConnectionString = strConnection

        objQConnection.Open()

 

        'Query Database for the list of already downloaded files

        Dim objQCommand As New SqlCommand()

        objQCommand.CommandType = CommandType.StoredProcedure

        objQCommand.CommandText = "ap_CheckDownloadedWUFiles"

        objQCommand.CommandTimeout = 120

        objQCommand.Connection = objQConnection

        Dim WUfileName As SqlParameter = objQCommand.Parameters.Add("@wufilename", SqlDbType.VarChar, 25)

        Dim isFound As SqlParameter = objQCommand.Parameters.Add("@isfound", SqlDbType.Bit)

        isFound.Direction = ParameterDirection.Output

 

        'Get a directory listing from WU and loop thru checking against downloaded files.

        Dim hInternet As IntPtr = InternetOpen(Nothing, INTERNET_OPEN_TYPE_DIRECT, Nothing, Nothing, 0)

        Dim hSession As IntPtr = InternetConnect(hInternet, gURL, INTERNET_DEFAULT_FTP_PORT, gUserName, gPassword, INTERNET_SERVICE_FTP, 0, IntPtr.Zero)

        Dim hgCDto As Boolean = FtpSetCurrentDirectory(hSession, gCDto)

        Dim finddata As WIN32_FIND_DATA

 

        Dim hFind As IntPtr = FtpFindFirstFile(hSession, Nothing, finddata, 0, IntPtr.Zero)

        Dim more As Boolean = True

 

        Do While more

            TextBox1.Text = "PROCESSING..."

            If Not CBool(finddata.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) Then

                'Get the first filename from the WU directory

                'Send the filename to the query and see if it is already downloaded

                If finddata.cFileName & "" = "" Then

                    Exit Do

                Else

                    WUfileName.Value = finddata.cFileName

                    lWUFileName = finddata.cFileName

                    lWUFileDate = FileTimeToDate(finddata.ftLastWriteTime).ToShortDateString

                    objQCommand.ExecuteNonQuery()

                End If

                If isFound.Value = False Then

                     gGetFromFileName_w_Path = gCDto & "/" & finddata.cFileName            '"upload/MyFile.txt"

 gGetToFileName_w_LocalDir = "\\Halo\E\Work\OrderManage\WesternUnion\In\" & finddata.cFileName   '"C:\Temp\MyFile.txt"    

                    Dim passedname As String = ""

                    passedname = finddata.cFileName 'pass the filename to the download function

                    sharedhSession = hSession 'share the connection with the download function

                    Download(passedname)

                End If

            End If

            more = InternetFindNextFile(hFind, finddata)

        Loop 'check the next file

 

        'Kill Connections And Objects

        InternetCloseHandle(hFind)

        InternetCloseHandle(hSession)

        InternetCloseHandle(hInternet)

 

        objQConnection.Close()

        objQConnection = Nothing

        objQCommand = Nothing

 

    End Function

 

 

    Private Function Download(ByVal passedname As String)

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

        gSuccess = False 'used as testing flag

        Dim RC As Boolean

        Dim DelFile As Boolean

 

        ReceivedFile = gGetToFileName_w_LocalDir

        RC = FtpGetFile(sharedhSession, passedname, gGetToFileName_w_LocalDir, False, 1, 0, 0)

 

        If RC Then gSuccess = True 'used as testing flag

 

        gSize = FileLen(ReceivedFile) 'Public to update file log

        If gSize > 50 Then

            'Delete the file on the remote server

            DelFile = FtpDeleteFile(sharedhSession, passedname)

        End If

        'Update the file log

        LogWUFileDownload()

 

    End Function

 

 

    Private Sub LogWUFileDownload()

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

 

        'Establish Database Access For Update the file log with the new file

        Dim objUConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objUConnection.ConnectionString = strConnection

        objUConnection.Open()

 

        'Set Up Command To Update the log

        Dim objUCommand As New SqlCommand()

        objUCommand.CommandType = CommandType.StoredProcedure

        objUCommand.CommandText = "ap_LogWUFileDownload"

        objUCommand.CommandTimeout = 120

        objUCommand.Connection = objUConnection

        Dim LogWUFileName As SqlParameter = objUCommand.Parameters.Add("@logWUfilename", SqlDbType.VarChar, 25)

        Dim LogWUFileDate As SqlParameter = objUCommand.Parameters.Add("@logWUfiledate", SqlDbType.VarChar, 20)

        Dim FileSize As SqlParameter = objUCommand.Parameters.Add("@filesize", SqlDbType.Int)

 

        'set the values for the stored procedure

        LogWUFileName.Value = lWUFileName

        LogWUFileDate.Value = lWUFileDate

        FileSize.Value = gSize

 

        'fire the stored procedure

        objUCommand.ExecuteNonQuery()

 

        'Kill Connections And Objects

        objUConnection.Close()

        objUConnection = Nothing

        objUCommand = Nothing

 

        'Now process the file

        ProcessWUFile()

 

    End Sub

 

 

    Private Sub ProcessWUFile()

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

 

        'Establish Database Access to Update the file log with the new file

        Dim objUConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objUConnection.ConnectionString = strConnection

        objUConnection.Open()

 

        'Set Up Command To Update the log

        Dim objUCommand As New SqlCommand()

        objUCommand.CommandType = CommandType.StoredProcedure

        objUCommand.CommandText = "ap_UpdateWUFileLog"

        objUCommand.CommandTimeout = 120

        objUCommand.Connection = objUConnection

        Dim ULogWUFileName As SqlParameter = objUCommand.Parameters.Add("@logwufilename", SqlDbType.VarChar, 25)

        Dim ULogWUFileDate As SqlParameter = objUCommand.Parameters.Add("@logwufiledate", SqlDbType.VarChar, 20)

        Dim UFileSize As SqlParameter = objUCommand.Parameters.Add("@filesize", SqlDbType.Int)

 

        'Establish Database Access to Update WU Data

        Dim objIConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objIConnection.ConnectionString = strConnection

        objIConnection.Open()

 

        'Set Up Command To Insert the WU File data

        Dim objICommand As New SqlCommand()

        objICommand.CommandType = CommandType.StoredProcedure

        objICommand.CommandText = "ap_InsertWUFileData"

        objICommand.CommandTimeout = 120

        objICommand.Connection = objIConnection

 

        Dim ILogWUFileName As SqlParameter = objICommand.Parameters.Add("@logwufilename", SqlDbType.VarChar, 25)

        Dim ILogWUFileDate As SqlParameter = objICommand.Parameters.Add("@logwufiledate", SqlDbType.VarChar, 20)

        Dim IFileSize As SqlParameter = objICommand.Parameters.Add("@filesize", SqlDbType.Int)

        Dim WURecNum As SqlParameter = objICommand.Parameters.Add("@wurecnum", SqlDbType.Int)

        Dim CustomerAccountNumber As SqlParameter = objICommand.Parameters.Add("@customeraccountnumber", SqlDbType.VarChar, 25)

        Dim CustomerFName As SqlParameter = objICommand.Parameters.Add("@customerfname", SqlDbType.VarChar, 40)

        Dim CustomerLName As SqlParameter = objICommand.Parameters.Add("@customerlname", SqlDbType.VarChar, 40)

        Dim Address As SqlParameter = objICommand.Parameters.Add("@address", SqlDbType.VarChar, 40)

        Dim City As SqlParameter = objICommand.Parameters.Add("@city", SqlDbType.VarChar, 24)

        Dim State As SqlParameter = objICommand.Parameters.Add("@state", SqlDbType.VarChar, 2)

        Dim Zip As SqlParameter = objICommand.Parameters.Add("@zip", SqlDbType.VarChar, 10)

        Dim TelephoneNumber As SqlParameter = objICommand.Parameters.Add("@telephonenumber", SqlDbType.VarChar, 10)

        Dim MTCN As SqlParameter = objICommand.Parameters.Add("@mtcn ", SqlDbType.VarChar, 16)

        Dim PaymentAmount As SqlParameter = objICommand.Parameters.Add("@paymentamount", SqlDbType.Money)

        Dim TransactionDate As SqlParameter = objICommand.Parameters.Add("@transactiondate", SqlDbType.DateTime)

        Dim CommentText As SqlParameter = objICommand.Parameters.Add("@commenttext", SqlDbType.VarChar, 70)

        Dim ClientIdentiferValue As SqlParameter = objICommand.Parameters.Add("@clientidentifervalue", SqlDbType.VarChar, 9)

 

        'Set some vars for parsing the file

        Dim wuCustomerAccountNumber As String

        Dim wuCustomerName As String  'there is a "\" separating the first and last so parse for Insert Query

        Dim wuAddress As String

        Dim wuCity As String

        Dim wuState As String

        Dim wuZip As String

        Dim wuTelephoneNumber As String

        Dim wuMTCN As String

        Dim wuPaymentAmount As String

        Dim wuTransactionDate As String

        'Dim wuAgentZip As String

        Dim wuCommentText As String

        Dim wuClientIdentiferValue As String

        Dim FRecNum As Integer = 0

 

        'Open the "Quoted Data" - C,S,V, file to read.

 

        FileOpen(1, gGetToFileName_w_LocalDir, OpenMode.Input)

 

        Do While Not EOF(1)

            FRecNum = FRecNum + 1

 

            Input(1, wuCustomerAccountNumber)

            Input(1, wuCustomerName)

            Input(1, wuAddress)

            Input(1, wuCity)

            Input(1, wuState)

            Input(1, wuZip)

            Input(1, wuTelephoneNumber)

            Input(1, wuMTCN)

            Input(1, wuPaymentAmount)

            Input(1, wuTransactionDate)

            Input(1, wuCommentText)

            Input(1, wuClientIdentiferValue)

 

            'Set the SQL vars from parsing the file

            ILogWUFileName.Value = lWUFileName

            ILogWUFileDate.Value = lWUFileDate

            IFileSize.Value = gSize

            WURecNum.Value = FRecNum

            CustomerAccountNumber.Value = wuCustomerAccountNumber

            CustomerFName.Value = Mid(wuCustomerName, 1, InStr(1, wuCustomerName, "\") - 1)

            CustomerLName.Value = Mid(wuCustomerName, InStr(1, wuCustomerName, "\") + 1, Len(wuCustomerName) - InStr(1, wuCustomerName, "\"))

            Address.Value = wuAddress

            City.Value = wuCity

            State.Value = wuState

            Zip.Value = wuZip

            TelephoneNumber.Value = wuTelephoneNumber

            MTCN.Value = wuMTCN

 

            PaymentAmount.Value = wuPaymentAmount

            TransactionDate.Value = Mid(wuTransactionDate, 5, 2) & "/" & Mid(wuTransactionDate, 7, 2) & "/20" & Mid(wuTransactionDate, 3, 2) 'trimmed off CC cycle

            'AgentZip.Value = wuAgentZip

            CommentText.Value = Mid(wuCommentText, 1, InStr(1, wuCommentText, "\") - 1)  'this ends with a "\"

            ClientIdentiferValue.Value = wuClientIdentiferValue

 

            'fire the stored procedure

            objICommand.ExecuteNonQuery()

 

            'Read the next line

        Loop

 

        'set the values for the update processed flag stored procedure

        ULogWUFileName.Value = lWUFileName

        ULogWUFileDate.Value = lWUFileDate

        UFileSize.Value = gSize

 

        'fire the stored procedure

        objUCommand.ExecuteNonQuery()

 

        'Kill Connections And Objects

        objICommand = Nothing

        objUCommand = Nothing

        objUConnection.Close()

        objUConnection = Nothing

        objIConnection.Close()

        objIConnection = Nothing

 

        FileClose(1)

 

    End Sub

 

    Private Sub GetWUDetailRecords()

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

        'WUUploadTimer.Enabled = False

 

        Dim recDetail As WUDetailRecord

        Dim space128 As String = Space(128)

        Dim lngX As Long = 0

        Dim firstspnum As String

        Dim lastspnum As String

        Dim lngFile As Long

        Dim lngFilesize As Long

        Dim tcount As String

        Dim nextsuffix As String

 

        'Establish Database Access For Query the database

        Dim objQConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objQConnection.ConnectionString = strConnection

        objQConnection.Open()

 

        'Query Database for next suffix for the upload file

        Dim objSCommand As New SqlCommand()

        objSCommand.CommandType = CommandType.StoredProcedure

        objSCommand.CommandText = "ap_GetWUUploadSuffix"

        objSCommand.CommandTimeout = 120

        objSCommand.Connection = objQConnection

        Dim NewSuffix As SqlParameter = objSCommand.Parameters.Add("@newsuffix", SqlDbType.Char, 2)

        NewSuffix.Direction = ParameterDirection.Output

 

        objSCommand.ExecuteNonQuery()

        nextsuffix = NewSuffix.Value 'hold it here to allow for next query

        objSCommand = Nothing

        objQConnection.Close()

        objQConnection.Open()

 

        'Establish Database Access For Update the database record as SENT

        Dim strUConnection As String

        Dim objUConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objUConnection.ConnectionString = strUConnection

        objUConnection.Open()

 

        'Use to Send the record ID to the Stored Procedure to flag the upload

        Dim objUCommand As New SqlCommand()

        objUCommand.CommandType = CommandType.StoredProcedure

        objUCommand.CommandText = "ap_UpdateWURecordSent"

        objUCommand.CommandTimeout = 120

        objUCommand.Connection = objUConnection

        Dim RecID As SqlParameter = objUCommand.Parameters.Add("@recid", SqlDbType.Int)

        Dim SentFile As SqlParameter = objUCommand.Parameters.Add("@sentfile", SqlDbType.VarChar, 20)

        Dim FileRecNum As SqlParameter = objUCommand.Parameters.Add("@filerecnum", SqlDbType.Int)

 

 

        'Query Database To Get WU Deatil Records for File to Send --

        Dim objQCommand As New SqlCommand()

        objQCommand.CommandType = CommandType.StoredProcedure

        objQCommand.CommandText = "ap_GetWUDetailRecords"

        objQCommand.CommandTimeout = 120

        objQCommand.Connection = objQConnection

        objGetWUDetailRecords = objQCommand.ExecuteReader

 

 

        '*********** BUILD WU FILE NAME HERE as

        ' "NO" is WU assigned Client Identifier

        ' "I" = SwiftPay Customer input file

        ' JJJ date

        ' nextletter - caps

        ' nextnumber - never zero

        'need to create a "Julian" (3 digit day of year) `cause it is not in dotnet as we need it here

        Dim lngDays As Long = DateDiff("d", DateSerial(Year(Now), 1, 1), Now) + 1

        Dim myJulian As String = Convert.ToString(lngDays).PadLeft(3, "0")

 

        Dim strFilename As String = "xxx" & myJulian & nextsuffix

 

        Dim strFilepath As String = "\\HALO\E\Work\OrderManage\WesternUnion\Out\"

        Dim upassedname As String = strFilename

 

 

        'Open a file for binary writing

        FileOpen(1, strFilepath & strFilename, OpenMode.Binary.Output)

 

        While objGetWUDetailRecords.Read

 

            'Set the value for the FIRST CustAcctNum for the Trailer Record

            If lngX < 1 Then

                firstspnum = objGetWUDetailRecords("SWPayCardNum")

            End If

 

            lngX = lngX + 1 'Increment the counter for the Trailer record

 

            'Build a new record and write it to the file

            recDetail.RecordType = objGetWUDetailRecords("RecordType")                                     '1, value D=Detail

            recDetail.SwiftPayCardNumber = objGetWUDetailRecords("SWPayCardNum").PadLeft(16, "0")      '16, fill w/ zeros

            'ClientID is hardcoded for trailer

            recDetail.ClientID = objGetWUDetailRecords("ClientID") '9, as AAT152555

            recDetail.CustomerAccountNumber = objGetWUDetailRecords("CustAcctNum").PadRight(23, " ")   '23, left justify space fill

            recDetail.FirstName = OnlyAlphaNumericChars(objGetWUDetailRecords("FirstName")).PadRight(16, " ")               '16, space fill

            recDetail.LastName = OnlyAlphaNumericChars(objGetWUDetailRecords("LastName")).PadRight(21, " ")                '21, space fill

            recDetail.StreetAddress = objGetWUDetailRecords("STAddress").PadRight(40, " ")           '40, space fill

            recDetail.City = objGetWUDetailRecords("City").PadRight(24, " ")                    '24, space fill

            recDetail.State = objGetWUDetailRecords("State")                                          '2 use UPS types

            recDetail.Zip = objGetWUDetailRecords("Zip").PadRight(9, "0")                      '9, fill w/ zeros if len=5 as 120950000

            recDetail.Country = objGetWUDetailRecords("Country")                                        '3, USA or CAN

            If Not IsDBNull(objGetWUDetailRecords("Phone")) Then

                recDetail.Phone = objGetWUDetailRecords("Phone").PadRight(10, " ")               '10, optional

            Else

                recDetail.Phone = Space(10)

            End If

            recDetail.ProcessType = objGetWUDetailRecords("ProcessType")                               '1, A=Add C=Change S=Stop

            recDetail.Issuecard = objGetWUDetailRecords("IssueCard")                                 '1, Y=Yes or N=No

 

            Print(1, recDetail.RecordType & recDetail.SwiftPayCardNumber & recDetail.ClientID)

            Print(1, recDetail.CustomerAccountNumber & recDetail.FirstName & recDetail.LastName)

            Print(1, recDetail.StreetAddress & recDetail.City & recDetail.State)

            Print(1, recDetail.Zip & recDetail.Country & recDetail.Phone)

            Print(1, recDetail.ProcessType & recDetail.Issuecard)

            Print(1, Chr(13) & Chr(10))

 

            'Set the value for the LAST CustAcctNum for the Trailer Record 

            lastspnum = objGetWUDetailRecords("SWPayCardNum")

 

            'Send the record ID to the Stored Procedure to flag the upload

            RecID.Value = objGetWUDetailRecords("ID")

            SentFile.Value = strFilename

            FileRecNum.Value = lngX

            objUCommand.ExecuteNonQuery()

 

        End While

        If lngX <> 0 Then

 

            '********* Trailer record as

            '1, record type = "T"

            '6, number of records right justified - fill w/ leading zeros

            '9, OUR ClientID

            '16, first of Swiftpay number for file- need to capture FIRST in file - do order by in Q

            '16, last of Swiftpay number for file- need to capture LAST in file - do order by in Q

            '128, 128 spaces

            '**************************

 

            'Set the value for the Trailer Record Count

            tcount = Convert.ToString(lngX)

            tcount = tcount.PadLeft(6, "0")

 

            'Write the Trailer record to the file with our client id.

            Print(1, "T" & tcount & "xxxxxxx" & firstspnum.PadLeft(16, "0") & lastspnum.PadLeft(16, "0") & space128 & Chr(13) & Chr(10))

        End If

 

        'always call Close Reader when done reading.

        objGetWUDetailRecords.Close()

 

        FileClose(1)    'Close the file

 

        'Kill Connections And Objects

        objQConnection.Close()

        objQConnection = Nothing

        objQCommand = Nothing

        objUConnection.Close()

        objUConnection = Nothing

        objUCommand = Nothing

 

 

 

        'NOW SEND THE FILE AND CONFIRM - IF NO GO - DO SECOND UPLOAD!!!!!!!!!

        If lngX <> 0 Then

            Upload(upassedname, lngX)

        End If

 

    End Sub

 

 

    Private Function Upload(ByVal upassedname As String, ByVal reccount As Integer)

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

 

        'reset the public vars for sending more than one file

        pSendFromFileName_w_Path = ""

        pSendToFileName = ""

        pSize = ""

 

        'Upload any new files to Primary site MO-228

        pURL = "xxx.xxx.xxx.xxx"

        pUserName = "communication"

        pPassword = "password"

        pCDto = "in"       'watch the slash

 

 

        pSendFromFileName_w_Path = "\\Halo\E\Work\OrderManage\WesternUnion\Out\" & upassedname     '"C:\Temp\MyFile.txt"

        pSendToFileName = upassedname   '"upload/MyFile.txt"

        pSize = FileLen(pSendFromFileName_w_Path)    'Public to update file log, use for confirmation and logging

        pSuccess = False

 

 

        'Open an internet connection

        Dim hInternet As IntPtr = InternetOpen(Nothing, INTERNET_OPEN_TYPE_DIRECT, Nothing, Nothing, 0)

        Dim hSession As IntPtr = InternetConnect(hInternet, pURL, INTERNET_DEFAULT_FTP_PORT, pUserName, pPassword, INTERNET_SERVICE_FTP, 0, IntPtr.Zero)

 

        'Change the directory

        Dim hpCDto As Boolean = FtpSetCurrentDirectory(hSession, pCDto)

 

        'Send the file

        Dim SND As Boolean

        Dim TempSendName As String = "XXXXX" & upassedname

        SND = FtpPutFile(hSession, pSendFromFileName_w_Path, TempSendName, 0, 0)

 

        If SND Then

            pSuccess = True 'used as testing flag

 

            'Rename the file

            Dim REN As Boolean

            REN = FtpRenameFile(hSession, TempSendName, upassedname)

 

            'Update the file log

            LogWUFileUpload(upassedname, reccount)

 

            'Kill Connections And Objects

 

            InternetCloseHandle(hSession)

            InternetCloseHandle(hInternet)

        Else

            'Kill Connections And Objects from the failed attempt

            InternetCloseHandle(hSession)

            InternetCloseHandle(hInternet)

 

            'Send to the other FTP site

            'Upload any new files to secondary site TX-227

            pURL = "xxx.xxx.xxx.xxx"

            pUserName = "communication"

            pPassword = "password"

            pCDto = "in"       'watch the slash

 

            pSendFromFileName_w_Path = "\\Halo\E\Work\OrderManage\WesternUnion\Out\" & upassedname     '"C:\Temp\MyFile.txt"

            pSendToFileName = upassedname  '"upload/MyFile.txt"

            pSize = FileLen(pSendFromFileName_w_Path)    'Public to update file log, use for confirmation and logging

            pSuccess = False 'used as testing flag

 

 

            'Open an internet connection

            Dim hInternet2 As IntPtr = InternetOpen(Nothing, INTERNET_OPEN_TYPE_DIRECT, Nothing, Nothing, 0)

            Dim hSession2 As IntPtr = InternetConnect(hInternet2, pURL, INTERNET_DEFAULT_FTP_PORT, pUserName, pPassword, INTERNET_SERVICE_FTP, 0, IntPtr.Zero)

            'Change the directory

            Dim hpCDto2 As Boolean = FtpSetCurrentDirectory(hSession2, pCDto)

 

            'Send the file

            Dim SND2 As Boolean

            Dim TempSendName2 As String = "XXXXX" & upassedname

            SND2 = FtpPutFile(hSession, pSendFromFileName_w_Path, TempSendName, 0, 0)

            If SND2 Then pSuccess = True 'used as testing flag

 

            'Rename the file

            Dim REN2 As Boolean

            REN2 = FtpRenameFile(hSession, TempSendName, upassedname)

 

            'Update the file log

            LogWUFileUpload(upassedname, reccount)

 

            'Kill Connections And Objects------

 

            InternetCloseHandle(hSession2)

            InternetCloseHandle(hInternet2)

 

        End If

 

    End Function

 

    Private Sub LogWUFileUpload(ByVal upassedname As String, ByVal reccount As Integer)

        Windows.Forms.Application.DoEvents()

        TextBox1.Text = "PROCESSING..."

 

        'Establish Database Access For Update the file log with the new file

        Dim objIConnection As New SqlConnection()

       strConnection = "Application Name=WUNexus;Connect Timeout =120;Server=OSS;InitialCatalog=Management;Password=xxxxxxxx;User ID=app;Workstation ID=WUNexus"

        objIConnection.ConnectionString = strConnection

        objIConnection.Open()

 

        'Set Up Command To Update the log

        Dim objICommand As New SqlCommand()

        objICommand.CommandType = CommandType.StoredProcedure

        objICommand.CommandText = "ap_LogWUSentFile"

        objICommand.CommandTimeout = 120

        objICommand.Connection = objIConnection

        Dim LogSWUFileName As SqlParameter = objICommand.Parameters.Add("@logSWUfilename", SqlDbType.VarChar, 25)

        Dim LogSWUFileDate As SqlParameter = objICommand.Parameters.Add("@logSWUfiledate", SqlDbType.VarChar, 25)

        Dim FileSize As SqlParameter = objICommand.Parameters.Add("@filesize", SqlDbType.Int)

        Dim FRecCount As SqlParameter = objICommand.Parameters.Add("@freccount", SqlDbType.Int)

 

        'set the values for the stored procedure

        LogSWUFileName.Value = upassedname

        LogSWUFileDate.Value = Now

        FileSize.Value = pSize

        FRecCount.Value = reccount

 

        'fire the stored procedure

        objICommand.ExecuteNonQuery()

 

        'Kill Connections And Objects

        objIConnection.Close()

        objIConnection = Nothing

        objICommand = Nothing

 

    End Sub

 

 

End Class

 

 

Option Strict On

Option Explicit On

 

Imports System.Runtime.InteropServices

Imports System.Text

 

Module modFTP

 

    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'May need later

 

    'Passable from the form stuff

    Public strConnection As String

    Public sharedhSession As IntPtr

    Public lWUFileName As String

    Public lWUFileDate As String

    Public ReceivedFile As String

    Public SentFile As String

 

    Public Overloads Function PadRight(ByVal totalWidth As Integer, ByVal paddingChar As Char) As String

    End Function

 

    Public Overloads Function PadLeft(ByVal totalWidth As Integer, ByVal paddingChar As Char) As String

    End Function

 

    Public Structure WUDetailRecord

        Public RecordType As String     '1, value D=Detail

        Public SwiftPayCardNumber As String '16, fill w/ zeros

        Public ClientID As String       '9, as AAA123456

        Public CustomerAccountNumber As String  '23, left justify space fill

        Public FirstName As String      '16, space fill

        Public LastName As String       '21, space fill

        Public StreetAddress As String  '40, space fill

        Public City As String           '24, space fill

        Public State As String          '2 use UPS types

        Public Zip As String            '9, fill w/ zeros if len=5 as 120950000

        Public Country As String        '3, USA or CAN

        Public Phone As String          '10, optional

        Public ProcessType As String    '1, A=Add C=Change S=Stop

        Public Issuecard As String      '1, Y=Yes or N=No

    End Structure

 

    Public Const FTP_TRANSFER_TYPE_ASCII As Integer = &H1

    Public Const MAX_PATH As Integer = 260

    Public Const INTERNET_OPEN_TYPE_DIRECT As Integer = 1

    Public Const INTERNET_DEFAULT_FTP_PORT As Short = 21

    Public Const INTERNET_SERVICE_FTP As Integer = 1

    Public Const FILE_ATTRIBUTE_DIRECTORY As Integer = &H10

 

    <StructLayout(LayoutKind.Sequential)> Public Structure SYSTEMTIME

        Public wYear As Short

        Public wMonth As Short

        Public wDayOfWeek As Short

        Public wDay As Short

        Public wHour As Short

        Public wMinute As Short

        Public wSecond As Short

        Public wMilliseconds As Short

    End Structure

 

    <StructLayout(LayoutKind.Sequential)> Public Structure FILETIME

        Public dwLowDateTime As Integer

        Public dwHighDateTime As Integer

    End Structure

 

    <StructLayout(LayoutKind.Sequential, CharSet:=CharSet.Auto)> Public Structure WIN32_FIND_DATA

        Public dwFileAttributes As Integer

        Public ftCreationTime As FILETIME

        Public ftLastAccessTime As FILETIME

        Public ftLastWriteTime As FILETIME

        Public nFileSizeHigh As Integer

        Public nFileSizeLow As Integer

        Public dwReserved0 As Integer

        Public dwReserved1 As Integer

        <MarshalAs(UnmanagedType.ByValTStr, SizeConst:=MAX_PATH)> Public cFileName As String

        <MarshalAs(UnmanagedType.ByValTStr, SizeConst:=14)> Public cAlternateFileName As String

    End Structure

 

    'Use these for (put) sending a file

    Public pURL As String

    Public pUserName As String

    Public pPassword As String

    Public pCDto As String   ' just add to remote file name

    Public pSendFromFileName_w_Path As String       '"C:\Temp\MyFile.txt"

    Public pSendToFileName As String    '"upload/MyFile.txt"

    Public pSize As String                          'use for confirmation and logging

    Public pSuccess As Boolean

 

    'Use these to (get) a file

    Public gURL As String

    Public gUserName As String

    Public gPassword As String

    Public gCDto As String   ' just add to remote file name

    Public gGetFromFileName_w_Path As String      '"Results/MyFile.txt" 

    Public gGetToFileName_w_LocalDir As String    '"C:\Temp\MyFile.txt"

    Public gSize As Integer                        'use for confirmation and logging

    Public gSuccess As Boolean                      'used as testing flag

 

    'wininet.dll access functions

    Public Declare Auto Function FtpGetFile Lib "wininet" _

     (ByVal hConnect As IntPtr, _

     ByVal lpszRemoteFile As String, _

     ByVal lpszNewFile As String, _

     ByVal fFailIfExists As Boolean, _

     ByVal dwFlagsAndAttributes As Integer, _

     ByVal dwFlags As Integer, _

     ByVal dwContext As Integer) As Boolean

 

    Public Declare Auto Function FtpPutFile Lib "wininet" _

       (ByVal hConnect As IntPtr, _

       ByVal lpszLocalFile As String, _

       ByVal lpszRemoteFile As String, _

       ByVal dwFlags As Integer, _

       ByVal dwContext As Integer) As Boolean

 

    Public Declare Auto Function FtpRenameFile Lib "wininet" _

       (ByVal hConnect As IntPtr, _

       ByVal lpszExisting As String, _

       ByVal lpszNew As String) As Boolean

 

    Public Declare Auto Function FtpSetCurrentDirectory Lib "wininet" _

        (ByVal hConnect As IntPtr, _

        ByVal lpszDirectory As String) As Boolean

 

    Public Declare Auto Function FtpDeleteFile Lib "wininet" _

        (ByVal hConnect As IntPtr, _

        ByVal lpszFileName As String) As Boolean

 

    Public Declare Auto Function InternetOpen Lib "wininet" _

        (ByVal lpszAgent As String, _

         ByVal dwAccess As Integer, _

         ByVal lpszProxyName As String, _

         ByVal lpszProxyBypass As String, _

         ByVal dwFlags As Integer) As IntPtr

 

    Public Declare Auto Function InternetConnect Lib "wininet" _

        (ByVal hInternet As IntPtr, _

         ByVal lpszServerName As String, _

         ByVal nServerPort As Short, _

         ByVal lpszUserName As String, _

         ByVal lpszPassword As String, _

         ByVal dwService As Integer, _

         ByVal dwFlags As Integer, _

         ByVal dwContext As IntPtr) As IntPtr

 

    Public Declare Auto Function FtpFindFirstFile Lib "wininet" _

        (ByVal hConnect As IntPtr, _

         ByVal lpszSearchFile As String, _

         ByRef lpFindFileData As WIN32_FIND_DATA, _

         ByVal dwFlags As Integer, _

         ByVal dwContext As IntPtr) As IntPtr

 

    Public Declare Auto Function InternetFindNextFile Lib "wininet" _

        (ByVal hFind As IntPtr, _

         ByRef lpvFindData As WIN32_FIND_DATA) As Boolean

 

    Public Declare Function InternetCloseHandle Lib "wininet" _

        (ByVal hInternet As IntPtr) As Boolean

 

    Public Declare Function FileTimeToSystemTime Lib "kernel32" _

        (ByRef lpFileTime As FILETIME, _

         ByRef lpSystemTime As SYSTEMTIME) As Boolean

 

 

    Public Function FileTimeToDate(ByVal ft As FILETIME) As DateTime

        If ft.dwLowDateTime <> 0 OrElse ft.dwHighDateTime <> 0 Then

            Dim systime As SYSTEMTIME

            FileTimeToSystemTime(ft, systime)

            Return New DateTime(systime.wYear, systime.wMonth, systime.wDay, systime.wHour, systime.wMinute, systime.wSecond, systime.wMilliseconds)

        Else

            Return DateTime.MinValue

        End If

    End Function

 

    Public Function OnlyAlphaNumericChars(ByVal OrigString As _

      String) As String

        '***********************************************************

        'INPUT:  Any String

        'OUTPUT: The Input String with all non-alphanumeric characters removed

        'EXAMPLE Debug.Print OnlyAlphaNumericChars("Hello World!")

        '   output = ("Hello World")

        'NOTES:  Not optimized for speed and will run slow on long

        '        strings.  If you plan on using long strings, consider

        '        using alternative method of appending to output string.

        '***********************************************************

        Dim lLen As Integer

        Dim sAns As String

        Dim lCtr As Integer

        Dim sChar As String

 

        OrigString = Trim(OrigString)

        lLen = Len(OrigString)

        For lCtr = 1 To lLen

            sChar = Mid(OrigString, lCtr, 1)

            If IsAlphaNumeric(Mid(OrigString, lCtr, 1)) Then

                sAns = sAns & sChar

            End If

            '**Windows.Forms.Application.DoEvents()

            'optional, but if processing long string,necessary to prevent program from appearing to hang

            'if used, write the app so no re-entrancy into this function can occur

        Next

 

        OnlyAlphaNumericChars = sAns

 

    End Function

 

    Private Function IsAlphaNumeric(ByVal sChr As String) As Boolean

        'Set the values of allowed chars, "-" indicates a range of FROM-TO

        IsAlphaNumeric = sChr Like "[0-9A-Za-z ]"

    End Function

 

End Module