Extract and execute file

Imports System.IO
Imports System.IO.File
Imports System.Text.RegularExpressions
Imports System.Text
Public Class frm_FilePrep
Dim str_loadFilePath As String = ""
Dim str_showResult As String = ""
Dim objOperations As New clsFileOperations
Dim str_extractResult As String = ""

Private Property filepath()
Get
Return str_loadFilePath
End Get
Set(ByVal value)
str_loadFilePath = value
End Set
End Property
Public Enum loadFileExceptions
commonerror
allemptyrows
firstrowempty
loaderror
End Enum
Private Property ExtractResult()
Get
Return str_extractResult
End Get
Set(ByVal value)
str_extractResult = value
End Set
End Property
Public excepObj As loadFileExceptions
Private Sub frm_FilePrep_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
Me.MdiParent.Menu.MenuItems(0).MenuItems(0).Enabled = True
End Sub

Private Sub frm_FilePrep_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
fillCustodian()
lblResult.Visible = False
If (cmbCustodian.Items.Count = 0) Then
MessageBox.Show("No Custodian Found.Please Add Custodian", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

End Sub

Private Sub fillCustodian()
writelog.WriteLog("fillCustodian() called")
Dim dsCustodian As DataSet
Dim sqlGetData As String
sqlGetData = String.Format("SELECT CustodianID,CustodianName FROM custodian")
dsCustodian = MySqlHelper.ExecuteDataSet(sqlGetData)
If DMUtilities.FunCheckRowsAvailableInDataset(dsCustodian) Then

cmbCustodian.DataSource = dsCustodian.Tables(0)
cmbCustodian.ValueMember = "CustodianID"
cmbCustodian.DisplayMember = "CustodianName"
cmbCustodian.SelectedIndex = 0

Else
cmbCustodian.Items.Clear()
cmbCustodian.DataSource = Nothing
End If
End Sub
Public Sub New()

' This call is required by the Windows Form Designer.
InitializeComponent()

' Add any initialization after the InitializeComponent() call.
MySqlHelper.strConnectionString = DMConstants.strDataScrubberConnectionString
End Sub

Private Sub rdb_transaction_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rdb_transaction.CheckedChanged
If (rdb_transaction.Checked) Then
pnl_Transaction.Visible = True
pnl_Holdings.Visible = False
End If
End Sub

Private Sub rdb_holdings_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rdb_holdings.CheckedChanged
If (rdb_holdings.Checked) Then
pnl_Transaction.Visible = False
pnl_Holdings.Visible = True
End If
End Sub

Private Sub txt_asOfDate_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_asOfDate.KeyPress
Try
If DMUtilities.IsValidDateKey(e.KeyChar) Then
e.Handled = False
Else
e.Handled = True
End If
Catch ex As Exception
DMUtilities.ShowMessage(ex.Message, DMConstants.MessageType.ErrorMessage)
End Try
End Sub

Private Sub txt_startDate_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_startDate.KeyPress
Try
If DMUtilities.IsValidDateKey(e.KeyChar) Then
e.Handled = False
Else
e.Handled = True
End If
Catch ex As Exception
DMUtilities.ShowMessage(ex.Message, DMConstants.MessageType.ErrorMessage)
End Try
End Sub

Private Sub txt_endDate_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_endDate.KeyPress
Try
If DMUtilities.IsValidDateKey(e.KeyChar) Then
e.Handled = False
Else
e.Handled = True
End If
Catch ex As Exception
DMUtilities.ShowMessage(ex.Message, DMConstants.MessageType.ErrorMessage)
End Try
End Sub
Private Sub btn_browse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_browse.Click
Try
'Write the code to code to show file open dialog box and take file name from that control
writelog.WriteLog("btn_browse_Click() called")
lblResult.Visible = False
If cmbCustodian.SelectedValue = 26 Then
ofdBrowseCsv.Filter = "CSV File(*.csv)|*.csv|SLK File(*.slk)|*.slk|Text File(*.txt)|*.txt"
Else
ofdBrowseCsv.Filter = "CSV File(*.csv)|*.csv|Text File(*.txt)|*.txt"
End If

Dim TempFileName As String = ""
If ofdBrowseCsv.ShowDialog() = Windows.Forms.DialogResult.OK Then

TempFileName = ofdBrowseCsv.FileName
If File.Exists(TempFileName) Then

Dim ret As String = chekFileRowCount(TempFileName)
If ret <> "Success" Then
If excepObj = loadFileExceptions.allemptyrows Then
MessageBox.Show("File contains no rows." & ret, "Load File", MessageBoxButtons.OK, MessageBoxIcon.Information)
pnl_rawFields.Visible = False
Exit Sub
ElseIf excepObj = loadFileExceptions.firstrowempty Then
MessageBox.Show("First row is empty in the file." & ret, "Load File", MessageBoxButtons.OK, MessageBoxIcon.Information)
pnl_rawFields.Visible = False
Exit Sub
ElseIf excepObj = loadFileExceptions.loaderror Then
MessageBox.Show("Unable to load the file." & ret, "Load File", MessageBoxButtons.OK, MessageBoxIcon.Information)
pnl_rawFields.Visible = False
Exit Sub
ElseIf excepObj = loadFileExceptions.commonerror Then
MessageBox.Show("Error in file handling." & ret, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
pnl_rawFields.Visible = False
Exit Sub
End If
End If
txt_fileName.Text = TempFileName
txt_fileName.ReadOnly = True
filepath = TempFileName

'Hide the ClientID panel due to selection of new file
listClient.DataSource = Nothing
pnlClientID.Visible = False


If (Not filepath = "") Then
If Path.GetExtension(TempFileName).ToLower = ".csv" Then
If getCsvHeadfields(filepath) Then
cmb_rawFields.SelectedIndex = 0
pnl_rawFields.Visible = True
Else
'MessageBox.Show("Error:while reading file.Can not load header fields.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
ElseIf Path.GetExtension(TempFileName).ToLower = ".slk" Then
If getExcelHeadfields(filepath) Then
cmb_rawFields.SelectedIndex = 0
pnl_rawFields.Visible = True
Else
'MessageBox.Show("Error:while reading file.Can not load header fields.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If

'****** This is for A new type of file in which fields are separated by pipes (“|”) ********
ElseIf Path.GetExtension(TempFileName).ToLower = ".txt" Then
If getPipeHeadfields(filepath) Then
cmb_rawFields.SelectedIndex = 0
pnl_rawFields.Visible = True
Else
'MessageBox.Show("Error:while reading file.Can not load header fields.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If

Else
MessageBox.Show("Invalid file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End If
Else
MessageBox.Show("File does not exist", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End If

If cmbCustodian.SelectedValue = 26 Then 'NT HOLDINGS
If TempFileName <> "" Then
If Path.GetExtension(TempFileName).ToLower = ".slk" Then
pnl_rawFields.Visible = True
Else
pnl_rawFields.Visible = False
End If
End If
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Function chekFileRowCount(ByVal str_fileName As String) As String
Dim reader As New System.IO.StreamReader(str_fileName)
Dim rowcount As Integer = 0
Dim _status As String = "Success"
Dim str_string As String = ""
Dim isHeader As Boolean = True
Dim strFieldSeparator = ","
'This section is for check whether files contains datas and headers
Try

writelog.WriteLog("Function chekFileRowCount called")

'We check the file type and set the strFieldSeparator
If Path.GetExtension(str_fileName).ToLower = ".csv" Then
strFieldSeparator = ","
ElseIf Path.GetExtension(str_fileName).ToLower = ".txt" Then
strFieldSeparator = "|"
End If

Do While reader.Peek <> -1
rowcount += 1
str_string = reader.ReadLine()
If (str_string.Trim.Replace(strFieldSeparator, "") = "") Then
rowcount = rowcount - 1
End If
If (isHeader = True And str_string.Trim.Replace(strFieldSeparator, "") = "") Then
excepObj = loadFileExceptions.firstrowempty
Return ""
End If
isHeader = False
Loop
reader.Close()
If rowcount = 0 Or rowcount < 0 Then
excepObj = loadFileExceptions.allemptyrows
Return ""
End If
Catch ex As FileLoadException
_status = ex.Message
excepObj = loadFileExceptions.loaderror
reader.Close()
Catch ex As Exception
_status = ex.Message
excepObj = loadFileExceptions.commonerror
reader.Close()
End Try
writelog.WriteLog("Exiting Function chekFileRowCount")
Return _status
End Function
Private Function getCsvHeadfields(ByVal str_filename As String) As Boolean
Dim str_colHeaders() As String
Dim str_tableHeader As String = ""
Dim str_newColHeaders() As String
Dim i As Integer = 0
Dim reader As New System.IO.StreamReader(str_filename)
'Getting headers from csv file and loading into combobox
Try
writelog.WriteLog("getCsvHeadfields() called.")
str_tableHeader = reader.ReadLine()
str_colHeaders = Regex.Split(str_tableHeader, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
ReDim str_newColHeaders(str_colHeaders.Length - 1)
cmb_rawFields.Items.Clear()
For i = 0 To str_colHeaders.Length - 1
If (Not Trim(str_colHeaders(i).Replace("""", "")) = "") Then
str_newColHeaders(i) = formatCSVFields(str_colHeaders(i).ToString)
cmb_rawFields.Items.Add(Trim(str_newColHeaders(i)))
End If
Next
'cmb_rawFields.DataSource = str_newColHeaders
reader.Close()
Return True
Catch ex As Exception
MessageBox.Show("Error while reading file.Can not load header fields." & vbCrLf & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
reader.Close()
Return False
End Try
End Function
Private Function getExcelHeadfields(ByVal str_filename As String) As Boolean
Dim str_colHeaders() As String
Dim str_tableHeader As String = ""
Dim str_newColHeaders() As String
Dim i As Integer = 0
Dim reader As New System.IO.StreamReader(str_filename)
'Getting headers from csv file and loading into combobox
Try
writelog.WriteLog("getCsvHeadfields() called.")
str_tableHeader = reader.ReadLine()
str_colHeaders = Regex.Split(str_tableHeader, vbTab)
ReDim str_newColHeaders(str_colHeaders.Length - 1)
cmb_rawFields.Items.Clear()
For i = 0 To str_colHeaders.Length - 1
If (Not Trim(str_colHeaders(i).Replace("""", "")) = "") Then
str_newColHeaders(i) = formatCSVFields(str_colHeaders(i).ToString)
cmb_rawFields.Items.Add(Trim(str_newColHeaders(i)))
End If
Next
'cmb_rawFields.DataSource = str_newColHeaders
reader.Close()
Return True
Catch ex As Exception
MessageBox.Show("Error while reading file.Can not load header fields." & vbCrLf & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
reader.Close()
Return False
End Try
End Function
Private Function getPipeHeadfields(ByVal str_filename As String) As Boolean
Dim str_colHeaders() As String
Dim str_tableHeader As String = ""
Dim str_newColHeaders() As String
Dim i As Integer = 0
Dim reader As New System.IO.StreamReader(str_filename)
'Getting headers from csv file and loading into combobox
Try
writelog.WriteLog("getCsvHeadfields() called.")
str_tableHeader = reader.ReadLine()
'str_colHeaders = Regex.Split(str_tableHeader, "|")
str_colHeaders = str_tableHeader.Split("|")

' str_colHeaders = Regex.Split(str_tableHeader, "|(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
ReDim str_newColHeaders(str_colHeaders.Length - 1)
cmb_rawFields.Items.Clear()
For i = 0 To str_colHeaders.Length - 1
If (Not Trim(str_colHeaders(i).Replace("""", "")) = "") Then
str_newColHeaders(i) = formatPipeFields(str_colHeaders(i).ToString)
cmb_rawFields.Items.Add(Trim(str_newColHeaders(i)))
End If
Next
'cmb_rawFields.DataSource = str_newColHeaders
reader.Close()
Return True
Catch ex As Exception
MessageBox.Show("Error while reading file.Can not load header fields." & vbCrLf & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
reader.Close()
Return False
End Try
End Function

Private Function validateFormFields() As Boolean

If (rdb_transaction.Checked And pnl_Transaction.Visible) Then
If (txt_startDate.Text.Trim() = "") Then
MessageBox.Show("Please Enter StartDate", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
txt_startDate.Focus()
Return False
ElseIf (Not Regex.IsMatch(Me.txt_startDate.Text.Trim, "\d{1,2}(/|-)\d{1,2}(/|-)(\d{4})")) Then
MessageBox.Show("Invalid date format use mm-dd-yyyy format", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Error)
Me.txt_startDate.Focus()
Return False
ElseIf (Not IsDate(txt_startDate.Text.Trim())) Then
MessageBox.Show("Invalid date format use mm-dd-yyyy format", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Error)
txt_startDate.Focus()
Return False
ElseIf (Not Format(CDate(txt_startDate.Text), "yyyy") >= 1900) Then
MessageBox.Show("Year must be greater than or equal to 1900", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Error)
txt_startDate.Focus()
Return False
ElseIf (txt_endDate.Text.Trim() = "") Then
MessageBox.Show("Please Enter End Date", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
txt_endDate.Focus()
Return False
ElseIf (Not Regex.IsMatch(Me.txt_endDate.Text.Trim, "\d{1,2}(/|-)\d{1,2}(/|-)(\d{4})")) Then
MessageBox.Show("Invalid date format use mm-dd-yyyy format", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Error)
Me.txt_endDate.Focus()
Return False
ElseIf (Not IsDate(txt_endDate.Text.Trim())) Then
MessageBox.Show("Invalid date format use mm-dd-yyyy format", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Error)
txt_endDate.Focus()
Return False
ElseIf (Not Format(CDate(txt_endDate.Text), "yyyy") >= 1900) Then
MessageBox.Show("Year must be greater than or equal to 1900", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Error)
txt_endDate.Focus()
Return False
ElseIf (Not CDate(txt_endDate.Text.Trim()) > CDate(txt_startDate.Text.Trim())) Then
MessageBox.Show("End date must be greater than start date", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Error)
txt_startDate.Focus()
Return False
End If
Return True
End If
If (rdb_holdings.Checked And pnl_Holdings.Visible) Then
If (txt_asOfDate.Text.Trim() = "") Then
MessageBox.Show("Please enter As of date", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
txt_asOfDate.Focus()
Return False
ElseIf (Not IsDate(txt_asOfDate.Text.Trim())) Then
MessageBox.Show("Invalid date format use mm-dd-yyyy format", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
txt_asOfDate.Focus()
Return False
ElseIf (Not Format(CDate(txt_asOfDate.Text), "yyyy") >= 1900) Then
MessageBox.Show("Year must be greater than or equal to 1900", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
txt_asOfDate.Focus()
Return False
End If

End If
If Trim(txt_fileName.Text) = "" Then
MessageBox.Show("Please select a file", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return False
End If
If (cmb_rawFields.Items.Count = 0) Then
MessageBox.Show("No rawfields to found", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return False
End If
Return True
End Function

Private Sub btn_Extract_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Extract.Click
Dim int_status As Integer
If (Not validateFormFields()) Then
Exit Sub
End If
If cmbCustodian.SelectedValue = 26 Then
If Path.GetExtension(txt_fileName.Text).ToLower <> ".slk" And Path.GetExtension(txt_fileName.Text).ToLower <> ".csv" And Path.GetExtension(txt_fileName.Text).ToLower <> ".txt" Then
MessageBox.Show("Invalid file. Please load a CSV,SLK or TXT file", "Error", MessageBoxButtons.OK)
Exit Sub
End If
Else
If Path.GetExtension(txt_fileName.Text).ToLower <> ".csv" And Path.GetExtension(txt_fileName.Text).ToLower <> ".txt" Then
MessageBox.Show("Invalid file. Please load a CSV or TXT file", "Error", MessageBoxButtons.OK)
Exit Sub
End If
End If
If cmbCustodian.SelectedValue = 2 Then
int_status = ProcessNTHoldings()
showNTResult(int_status)
Exit Sub
ElseIf cmbCustodian.SelectedValue = 26 And Path.GetExtension(txt_fileName.Text).ToLower = ".slk" Then
int_status = isNTHeaderExtractProcess()
showNTResult(int_status)
Exit Sub
End If
lblResult.Visible = False
If (chk_header.Checked) Then
writelog.WriteLog("Function isHeaderExtractProcess() is called.")
int_status = isHeaderExtractProcess()
Else
writelog.WriteLog("Function noHeaderExtractProcess() is called.")
int_status = noHeaderExtractProcess()
End If

Select Case int_status
Case 1
lblResult.Text = str_showResult
lblResult.Visible = True
Case -1
MessageBox.Show("There is no data found in the selected CSV file", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
Case -2
MessageBox.Show("There is no matching client ids for the selected custodian and account number found in the database", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
lblResult.Text = ""
lblResult.Text = ExtractResult
lblResult.Visible = True
Case -3
MessageBox.Show("Error while accessing database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Select
writelog.WriteLog("----------------------------------------------------------------------------")
End Sub

Private Function isHeaderExtractProcess() As Integer

Try

Dim str_sql As String
Dim i As Integer
Dim str_accountNumber As String = ""
Dim dsClient As DataSet
Dim arr_fields() As String
Dim isHeader As Boolean = True
Dim int_index As Integer
Dim hTable As New Hashtable()
Dim hTable_account As New Hashtable()
Dim client_hTableAccCid As New Hashtable()
Dim ht_temp As New Hashtable()
Dim ht_temp2 As New Hashtable()
Dim j As Integer = 0
Dim extractWriter() As StreamWriter
Dim dirPath As String
Dim fileName() As String
Dim str_csvData As String
Dim Reader As System.IO.StreamReader
Dim str_invalidAccount As String = ""

Dim strSeperator As String = ","
Dim strFileExtension As String = ".csv"
Dim tempdsClient As DataSet
Dim strGlobalAccNum As String
Dim AccNumber, ClientID, CusCustodianID As String
Dim accountNumberList As String()

'Selecting accountnumber from csv fields and stored in hashtable
Try
If Path.GetExtension(txt_fileName.Text).ToLower = ".csv" Then
strSeperator = ","
strFileExtension = ".csv"
ElseIf Path.GetExtension(txt_fileName.Text).ToLower = ".txt" Then
strSeperator = "|"
strFileExtension = ".txt"
End If


writelog.WriteLog("Selecting accountnumber from csv fields and stored in hashtable [isHeaderExtractProcess()]")
Reader = New StreamReader(str_loadFilePath)

Do While Reader.Peek <> -1
str_csvData = Reader.ReadLine()

If strFileExtension = ".txt" Then
arr_fields = str_csvData.Split("|")
Else
arr_fields = Regex.Split(str_csvData, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
End If

If isHeader Then
For i = 0 To arr_fields.Length - 1
If (formatFields(arr_fields(i).ToString, strFileExtension) = cmb_rawFields.Text) Then
int_index = i
Exit For
End If
Next
isHeader = False
Else
If (Not arr_fields.Length - 1 < int_index) Then
If (Not hTable.ContainsValue(formatFields(arr_fields(int_index).ToString, strFileExtension)) And Not formatFields(arr_fields(int_index).ToString, strFileExtension) = "") Then
hTable.Add(j, formatFields(arr_fields(int_index).ToString, strFileExtension))
j += 1
End If
End If
End If
Loop
Reader.Close()
Catch ex As FileLoadException
MessageBox.Show(ex.Message, "FileLoad", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try

'If selected fields in csv contains no data then return -1
If (hTable.Count = 0) Then
Return -1
End If

'Creating sql query
For i = 0 To hTable.Count - 1
str_accountNumber = str_accountNumber & "," & "'" & Trim(hTable.Item(i).ToString()) & "'"
Next

str_accountNumber = str_accountNumber.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
strGlobalAccNum = str_accountNumber

'*****************The following section handle the "Accounts Mapped to Multiple Clients for Custodian********************"

str_sql = "DELETE FROM tbl_TempClientAccountMapping "
MySqlHelper.ExecuteNonQuery(str_sql)

'Featch Multiple Mapped Account Nums
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) Group by AccountNum Having ClientCount>1", cmbCustodian.SelectedValue, str_accountNumber)

tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
str_accountNumber = ""
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1
If str_accountNumber = "" Then
str_accountNumber = "'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
Else
str_accountNumber = str_accountNumber + ",'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
End If
Next
End If

'set all clientID and Accnum in tempDB
If str_accountNumber <> "" Then
accountNumberList = str_accountNumber.Replace("'", "").Split(",")
End If

If listClient.Items.Count = 0 Then
tempdsClient.Tables.Clear()
If str_accountNumber <> "" Then
'str_sql = String.Format("SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,cast(concat(`AccountNum`,'-', `ClientID`) as char) as `clientAcc` FROM clientaccountmapping WHERE `AccountNum` in ({0}) AND CustodianID ={1} GROUP BY ClientID ORDER BY ClientID ", str_accountNumber, cmbCustodian.SelectedValue)

str_sql = String.Format("SELECT T1.ClientID,T1.AccountNum,cast(concat(T1.AccNums,'-', T1.ClientID) as char) as `clientAcc` FROM " & _
"(SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,CONVERT(GROUP_CONCAT(AccountNum) USING utf8) AccNums FROM clientaccountmapping" & _
" WHERE `AccountNum` in ({0}) AND CustodianID = {1} GROUP BY ClientID) AS T1 ORDER BY T1.ClientID ", str_accountNumber, cmbCustodian.SelectedValue)


tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)

If tempdsClient.Tables(0).Rows.Count >= 1 Then
MessageBox.Show("Account number has multiple client IDs mapped in clientaccountmapping table", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
listClient.DataSource = tempdsClient.Tables(0)
'listClient.DataSource = dt
listClient.DisplayMember = "ClientID"
listClient.ValueMember = "clientAcc"
pnlClientID.Visible = True
'setListBoxSelection(listClient, Path.GetFileNameWithoutExtension(txt_fileName.Text))
'setListBoxSeperation(listClient)
Exit Function
End If
End If
Else
Dim arrSelected As String = ""
Dim intClientID As String
Dim CurrAccNumbers As String = ""
Dim strInsertVal As String = ""
Dim strCusID As String = "0"
Dim strClientAccountNo As String
Dim accountIndex As Integer

strCusID = Str(cmbCustodian.SelectedValue)
If listClient.SelectedItems.Count > 0 Then
'If listClient.SelectedItems.Count >= accountNumberList.Length Then
Dim strSelAcc As [String]
For Each selecteditem As [Object] In listClient.SelectedItems
strSelAcc = selecteditem(1)
intClientID = selecteditem(0)

strClientAccountNo = selecteditem(2)
Dim strAccountNos = strClientAccountNo.Split("-")
Dim arrAccountNumbers = strAccountNos(0).Split(",")
Dim arrAccountNos = FilterDuplicates(arrAccountNumbers)

For accountIndex = 0 To arrAccountNos.Length - 2
strSelAcc = arrAccountNos(accountIndex)
If strInsertVal = "" Then
strInsertVal = "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
Else
strInsertVal = strInsertVal + "," + "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
End If

If arrSelected = "" Then
arrSelected = strSelAcc
CurrAccNumbers = "//" + strSelAcc + "//"
Else
Dim searchAcc As String = "//" + strSelAcc + "//"
If CurrAccNumbers.IndexOf(searchAcc) = -1 Then
arrSelected = arrSelected + "," + strSelAcc
CurrAccNumbers = CurrAccNumbers + strSelAcc + "//"
Else
MessageBox.Show("Multiple clientID's are selected for single AccountNumber", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
'Exit Try
Exit Function
End If
End If
Next


Next
'Else
' MessageBox.Show("Some Account Numbers are not mapped in the list...Please map all Account Numbers", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
' 'Exit Try
' Exit Function
'End If

ElseIf listClient.SelectedItems.Count <= 0 Then
MessageBox.Show("ClientIDs are not selected in the list...Please select ClientIDs", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Function
End If

str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping(AccountNum, ClientID,CustodianID) " & _
" VALUES {0}", strInsertVal)
MySqlHelper.ExecuteNonQuery(str_sql)
End If

'Featch Single Mapped Account Nums
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID, CAST(Trim(`CustodianID`) AS CHAR) CustodianID, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) Group by AccountNum Having ClientCount=1", cmbCustodian.SelectedValue, strGlobalAccNum)
tempdsClient.Clear()
tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1

AccNumber = tempdsClient.Tables(0).Rows(i)("AccountNum").ToString
ClientID = tempdsClient.Tables(0).Rows(i)("ClientID").ToString
CusCustodianID = tempdsClient.Tables(0).Rows(i)("CustodianID").ToString

str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping (AccountNum, ClientID,CustodianID) " & _
" VALUES('{0}',{1},{2})", AccNumber, ClientID, CusCustodianID)
MySqlHelper.ExecuteNonQuery(str_sql)
Next
End If

'******************End the section for handle the "Accounts Mapped to Multiple Clients for Custodian***************************

'Executing query and from result creating filenames and initializing Streamwriter instances
Try
writelog.WriteLog("Executing query and from result creating filenames and initializing Streamwriter instances")
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID FROM tbl_TempClientAccountMapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1})", cmbCustodian.SelectedValue, strGlobalAccNum)
dsClient = MySqlHelper.ExecuteDataSet(str_sql)

If (dsClient.Tables(0).Rows.Count = 0) Then
str_accountNumber = strGlobalAccNum
Dim resAcc As String = str_accountNumber.Replace("'", "")
str_showResult = ""
str_showResult = "Account numbers in file that does not have a client id lookup are listed below:" & vbNewLine & resAcc & vbNewLine & str_showResult

ExtractResult = str_showResult
Return -2
Else
Try
dirPath = GetOutputDirectory()
j = 0
For i = 0 To dsClient.Tables(0).Rows.Count - 1
client_hTableAccCid.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)
If (Not hTable_account.ContainsValue(dsClient.Tables(0).Rows(i)("ClientID").ToString)) Then
hTable_account.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)
ht_temp.Add(j, dsClient.Tables(0).Rows(i)("ClientID").ToString)
ht_temp2.Add(dsClient.Tables(0).Rows(i)("ClientID").ToString, j)
j = j + 1
End If
Next

ReDim fileName(hTable_account.Count - 1)
ReDim extractWriter(hTable_account.Count - 1)

For i = 0 To hTable_account.Count - 1
If (rdb_transaction.Checked) Then
fileName(i) = Path.GetFullPath(dirPath) & Path.DirectorySeparatorChar & ht_temp.Item(i).ToString & "_Trans_" & txt_startDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_" & txt_endDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
extractWriter(i) = File.CreateText(fileName(i))
Else
fileName(i) = Path.GetFullPath(dirPath) & Path.DirectorySeparatorChar & ht_temp.Item(i).ToString & "_Pos_" & txt_asOfDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
extractWriter(i) = File.CreateText(fileName(i))
End If
Next
Catch ex As IndexOutOfRangeException
MessageBox.Show("Error in array index.please contact your software vendor." & vbCrLf & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try


'Extracting csv files
Try
writelog.WriteLog("Extracting csv/txt files")
isHeader = True
Reader = Nothing
Reader = New StreamReader(str_loadFilePath)
Do While Reader.Peek <> -1
str_csvData = Reader.ReadLine()

If isHeader Then
For i = 0 To extractWriter.Length - 1
If strFileExtension = ".txt" Then
str_csvData = str_csvData.Replace("|", ",")
End If

extractWriter(i).Write(str_csvData)
Next
isHeader = False
Else
If strFileExtension = ".txt" Then
arr_fields = str_csvData.Split("|")
Else
arr_fields = Regex.Split(str_csvData, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
End If

If (Not arr_fields.Length - 1 < int_index) Then
If (client_hTableAccCid.Contains(Trim(formatFields(arr_fields(int_index).ToString, strFileExtension)))) Then
extractWriter(ht_temp2.Item(client_hTableAccCid.Item(Trim(formatFields(arr_fields(int_index).ToString, strFileExtension))))).Write(vbNewLine)
If strFileExtension = ".txt" Then
str_csvData = str_csvData.Replace("|", ",")
End If
extractWriter(ht_temp2.Item(client_hTableAccCid.Item(Trim(formatFields(arr_fields(int_index).ToString, strFileExtension))))).Write(str_csvData)
End If
End If
End If
Loop
Catch ex As IndexOutOfRangeException
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As FileLoadException
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
Finally
Reader.Close()
End Try


'Disposing streamwriter instances and creating messages
Try
str_showResult = "Total files created = " & ht_temp.Count.ToString & vbNewLine
For i = 0 To ht_temp.Count - 1
extractWriter(i).Dispose()
str_showResult = str_showResult & fileName(i) & " Saved Successfully" & vbNewLine
Next
For i = 0 To hTable.Count - 1
If (Not client_hTableAccCid.ContainsKey(Trim(hTable.Item(i)))) Then
str_invalidAccount = str_invalidAccount & hTable.Item(i) & ","
End If
Next
str_invalidAccount = str_invalidAccount.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
If (Not str_invalidAccount = "") Then
str_showResult = "Account numbers in csv/txt file that does not have a client id lookup are listed below:" & vbNewLine & str_invalidAccount & vbNewLine & str_showResult
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try

Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try
writelog.WriteLog("Exiting isHeaderExtractProcess()")
Return 1
End Function
Private Function noHeaderExtractProcess() As Integer
Dim str_sql As String
Dim i As Integer
Dim str_accountNumber As String = ""
Dim dsClient As DataSet
Dim arr_fields() As String
Dim isHeader As Boolean = True
Dim int_index As Integer
Dim hTable As New Hashtable()
Dim hTable_account As New Hashtable()
Dim client_hTableAccCid As New Hashtable()
Dim ht_temp As New Hashtable()
Dim ht_temp2 As New Hashtable()
Dim j As Integer = 0
Dim extractWriter() As StreamWriter
Dim dirPath As String
Dim fileName() As String
Dim str_csvData As String
Dim Reader As System.IO.StreamReader
Dim str_invalidAccount As String = ""

Dim strSeperator As String = ","
Dim strFileExtension As String = ".csv"
Dim tempdsClient As DataSet
Dim strGlobalAccNum As String
Dim AccNumber, ClientID, CusCustodianID As String
Dim accountNumberList As String()

Try
'Selecting accountnumber from csv fields and stored in hashtable
Try
writelog.WriteLog("Selecting accountnumber from csv fields and stored in hashtable [noHeaderExtractProcess()]")

Reader = New StreamReader(str_loadFilePath)
str_csvData = Reader.ReadLine()

If Path.GetExtension(txt_fileName.Text).ToLower = ".csv" Then
strSeperator = ","
strFileExtension = ".csv"
ElseIf Path.GetExtension(txt_fileName.Text).ToLower = ".txt" Then
strSeperator = "|"
strFileExtension = ".txt"
End If

If strFileExtension = ".txt" Then
arr_fields = str_csvData.Split("|")
Else
arr_fields = Regex.Split(str_csvData, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
End If

For i = 0 To arr_fields.Length - 1

If (formatFields(arr_fields(i).ToString, strFileExtension) = cmb_rawFields.Text) Then
int_index = i
Exit For
End If
Next
Reader.Close()

Reader = Nothing
Reader = New StreamReader(str_loadFilePath)
Do While Reader.Peek <> -1
str_csvData = Reader.ReadLine()

If strFileExtension = ".txt" Then
arr_fields = str_csvData.Split("|")
Else
arr_fields = Regex.Split(str_csvData, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
End If

If (Not arr_fields.Length - 1 < int_index) Then
If (Not hTable.ContainsValue(formatFields(arr_fields(int_index).ToString, strFileExtension)) And Not formatFields(arr_fields(int_index).ToString, strFileExtension) = "") Then
hTable.Add(j, formatFields(arr_fields(int_index).ToString, strFileExtension))
j += 1
End If
End If
Loop
Reader.Close()
Catch ex As FileLoadException
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try


'If selected fields in csv contains no data then return -1
If (hTable.Count = 0) Then
Return -1
End If

'Creating sql query
For i = 0 To hTable.Count - 1
str_accountNumber = str_accountNumber & "," & "'" & Trim(hTable.Item(i).ToString()) & "'"
Next

str_accountNumber = str_accountNumber.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
strGlobalAccNum = str_accountNumber


'*********************The following section handle the "Accounts Mapped to Multiple Clients for Custodian********************

str_sql = "DELETE FROM tbl_TempClientAccountMapping "
MySqlHelper.ExecuteNonQuery(str_sql)

'Featch Multiple Mapped Account Nums
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) Group by AccountNum Having ClientCount>1", cmbCustodian.SelectedValue, str_accountNumber)

tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
str_accountNumber = ""
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1
If str_accountNumber = "" Then
str_accountNumber = "'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
Else
str_accountNumber = str_accountNumber + ",'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
End If
Next
End If

'set all clientID and Accnum in tempDB
If str_accountNumber <> "" Then
accountNumberList = str_accountNumber.Replace("'", "").Split(",")
End If

If listClient.Items.Count = 0 Then
tempdsClient.Tables.Clear()
If str_accountNumber <> "" Then
MySqlHelper.strConnectionString = DMConstants.strDataScrubberConnectionString
'str_sql = String.Format("SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,cast(concat(`AccountNum`,'-', `ClientID`) as char) as `clientAcc` FROM clientaccountmapping WHERE `AccountNum` in ({0}) AND CustodianID ={1} GROUP BY ClientID ORDER BY ClientID", str_accountNumber, cmbCustodian.SelectedValue)
str_sql = String.Format("SELECT T1.ClientID,T1.AccountNum,cast(concat(T1.AccNums,'-', T1.ClientID) as char) as `clientAcc` FROM " & _
"(SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,CONVERT(GROUP_CONCAT(AccountNum) USING utf8) AccNums FROM clientaccountmapping" & _
" WHERE `AccountNum` in ({0}) AND CustodianID = {1} GROUP BY ClientID) AS T1 ORDER BY T1.ClientID ", str_accountNumber, cmbCustodian.SelectedValue)
tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
If tempdsClient.Tables(0).Rows.Count >= 1 Then
MessageBox.Show("Account number has multiple client IDs mapped in clientaccountmapping table", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)

listClient.DataSource = tempdsClient.Tables(0)
listClient.DisplayMember = "ClientID"
listClient.ValueMember = "clientAcc"
pnlClientID.Visible = True


Exit Function
End If
End If
Else
Dim arrSelected As String = ""
Dim intClientID As String
Dim CurrAccNumbers As String = ""
Dim strInsertVal As String = ""
Dim strCusID As String = "0"
Dim strClientAccountNo As String
Dim accountIndex As Integer

strCusID = Str(cmbCustodian.SelectedValue)

If listClient.SelectedItems.Count > 0 Then
'If listClient.SelectedItems.Count >= accountNumberList.Length Then
Dim strSelAcc As [String]
For Each selecteditem As [Object] In listClient.SelectedItems
strSelAcc = selecteditem(1)
intClientID = selecteditem(0)

strClientAccountNo = selecteditem(2)
Dim strAccountNos = strClientAccountNo.Split("-")
Dim arrAccountNumbers = strAccountNos(0).Split(",")
Dim arrAccountNos = FilterDuplicates(arrAccountNumbers)

For accountIndex = 0 To arrAccountNos.Length - 2
If strInsertVal = "" Then
strInsertVal = "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
Else
strInsertVal = strInsertVal + "," + "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
End If

If arrSelected = "" Then
arrSelected = strSelAcc
CurrAccNumbers = "//" + strSelAcc + "//"
Else
Dim searchAcc As String = "//" + strSelAcc + "//"
If CurrAccNumbers.IndexOf(searchAcc) = -1 Then
arrSelected = arrSelected + "," + strSelAcc
CurrAccNumbers = CurrAccNumbers + strSelAcc + "//"
Else
MessageBox.Show("Multiple clientID's are selected for single AccountNumber", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Function
End If
End If
Next
Next

'Else
' MessageBox.Show("Some Account Numbers are not mapped in the list...Please map all Account Numbers", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
' 'Exit Try
' Exit Function
'End If

ElseIf listClient.SelectedItems.Count <= 0 Then
MessageBox.Show("ClientIDs are not selected in the list...Please select ClientIDs", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Function
End If

str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping(AccountNum, ClientID,CustodianID) " & _
" VALUES {0}", strInsertVal)
MySqlHelper.ExecuteNonQuery(str_sql)
End If

'Featch Single Mapped Account Nums
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID, CAST(Trim(`CustodianID`) AS CHAR) CustodianID,Trim(`ClientNameCode`) AS ClientNameCode, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) Group by AccountNum Having ClientCount=1", cmbCustodian.SelectedValue, strGlobalAccNum)
tempdsClient.Clear()
tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1

AccNumber = tempdsClient.Tables(0).Rows(i)("AccountNum").ToString
ClientID = tempdsClient.Tables(0).Rows(i)("ClientID").ToString
CusCustodianID = tempdsClient.Tables(0).Rows(i)("CustodianID").ToString
str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping (AccountNum, ClientID,CustodianID) " & _
" VALUES('{0}',{1},{2})", AccNumber, ClientID, CusCustodianID)
MySqlHelper.ExecuteNonQuery(str_sql)
Next
End If

'******************The End of the section which handle the "Accounts Mapped to Multiple Clients for Custodian*****************

'Executing query and from result creating filenames and initializing Streamwriter instances
Try
writelog.WriteLog("Executing query and from result creating filenames and initializing Streamwriter instances")

str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID FROM tbl_TempClientAccountMapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1})", cmbCustodian.SelectedValue, strGlobalAccNum)
dsClient = MySqlHelper.ExecuteDataSet(str_sql)
If (dsClient.Tables(0).Rows.Count = 0) Then
str_accountNumber = strGlobalAccNum
Dim resAcc As String = str_accountNumber.Replace("'", "")
str_showResult = ""
str_showResult = "Account numbers in file that does not have a client id lookup are listed below:" & vbNewLine & resAcc & vbNewLine & str_showResult
ExtractResult = str_showResult
Return -2
Else
Try
dirPath = GetOutputDirectory()
j = 0
For i = 0 To dsClient.Tables(0).Rows.Count - 1
client_hTableAccCid.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)
If (Not hTable_account.ContainsValue(dsClient.Tables(0).Rows(i)("ClientID").ToString)) Then
hTable_account.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)
ht_temp.Add(j, dsClient.Tables(0).Rows(i)("ClientID").ToString)
ht_temp2.Add(dsClient.Tables(0).Rows(i)("ClientID").ToString, j)
j = j + 1
End If
Next

ReDim fileName(hTable_account.Count - 1)
ReDim extractWriter(hTable_account.Count - 1)

For i = 0 To hTable_account.Count - 1
If (rdb_transaction.Checked) Then
fileName(i) = Path.GetFullPath(dirPath) & Path.DirectorySeparatorChar & ht_temp.Item(i).ToString & "_Trans_" & txt_startDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_" & txt_endDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
extractWriter(i) = File.CreateText(fileName(i))
objOperations.writeHeaderIfRequired(cmbCustodian.SelectedValue, extractWriter(i), True)
Else
fileName(i) = Path.GetFullPath(dirPath) & Path.DirectorySeparatorChar & ht_temp.Item(i).ToString & "_Pos_" & txt_asOfDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
extractWriter(i) = File.CreateText(fileName(i))
objOperations.writeHeaderIfRequired(cmbCustodian.SelectedValue, extractWriter(i), False)

End If
Next
Catch ex As IndexOutOfRangeException
MessageBox.Show("Error in array index.please contact your software vendor." & vbCrLf & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try

'Extracting csv files
Try
writelog.WriteLog("Extracting csv/txt files")
isHeader = True
Reader = Nothing
Reader = New StreamReader(str_loadFilePath)
Do While Reader.Peek <> -1
str_csvData = Reader.ReadLine()

'If the Custodian is FCTI then remove the quotes and additional spaces in the data field
If cmbCustodian.SelectedValue = 7 Then
str_csvData = PreProcessRowValues(str_csvData, strFileExtension)
End If

If strFileExtension = ".txt" Then
arr_fields = str_csvData.Split("|")
Else
arr_fields = Regex.Split(str_csvData, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
End If

If (Not arr_fields.Length - 1 < int_index) Then
If (client_hTableAccCid.Contains(Trim(formatFields(arr_fields(int_index).ToString, strFileExtension)))) Then
If strFileExtension = ".txt" Then
str_csvData = str_csvData.Replace("|", ",")
End If


extractWriter(ht_temp2.Item(client_hTableAccCid.Item(Trim(formatFields(arr_fields(int_index).ToString, strFileExtension))))).Write(str_csvData)
extractWriter(ht_temp2.Item(client_hTableAccCid.Item(Trim(formatFields(arr_fields(int_index).ToString, strFileExtension))))).Write(vbNewLine)
End If
End If
Loop
Catch ex As IndexOutOfRangeException
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As FileLoadException
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
Finally
Reader.Close()
End Try

'Disposing streamwriter instances and creating messages
Try
str_showResult = "Total files created = " & ht_temp.Count.ToString & vbNewLine
For i = 0 To ht_temp.Count - 1
extractWriter(i).Dispose()
str_showResult = str_showResult & fileName(i) & " Saved Successfully" & vbNewLine
Next
For i = 0 To hTable.Count - 1
If (Not client_hTableAccCid.ContainsKey(Trim(hTable.Item(i)))) Then
str_invalidAccount = str_invalidAccount & hTable.Item(i) & ","
End If
Next
str_invalidAccount = str_invalidAccount.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
If (Not str_invalidAccount = "") Then
str_showResult = "Account numbers in csv/txt file that does not have a client id lookup are listed below:" & vbNewLine & str_invalidAccount & vbNewLine & str_showResult
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try

writelog.WriteLog("Exiting noHeaderExtractProcess()")
Return 1
End Function
Public Function GetDefaultOutputLocation() As String
writelog.WriteLog("Function 'GetDefaultOutputLocation' called")
'If there is no output directory set by user it will select the default location (in the installed directory)
Dim folderName As String = "ExtractedFiles"
Dim defaultoutputlocation As String = Application.StartupPath & Path.DirectorySeparatorChar & folderName
If Not Directory.Exists(defaultoutputlocation) Then
Directory.CreateDirectory(defaultoutputlocation)
End If
writelog.WriteLog("Existing Function 'GetOutputDirectory' called")
Return defaultoutputlocation
End Function
Private Function GetOutputDirectory() As String
writelog.WriteLog("Function 'GetOutputDirectory' called")
'Finding the output location from xml file
Dim doc As New System.Xml.XmlDocument
doc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile)
Dim outputdirectory As String = doc.ChildNodes(1).ChildNodes(0).ChildNodes(4).Attributes(1).Value
If Directory.Exists(outputdirectory) Then
writelog.WriteLog("ExistingFunction 'GetOutputDirectory'")
Return outputdirectory
Else 'If save path not specified save it in a default location....
writelog.WriteLog("ExistingFunction 'GetOutputDirectory'")
Return GetDefaultOutputLocation()
End If
End Function

Private Sub cmbCustodian_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCustodian.SelectionChangeCommitted
lblResult.Visible = False
If cmbCustodian.SelectedValue = 7 Then
chk_header.Checked = False
chk_header.Visible = False
lblHeader.Visible = False
cmb_rawFields.Enabled = True
ElseIf cmbCustodian.SelectedValue = 2 Then
cmb_rawFields.Enabled = False
Else
chk_header.Checked = True
chk_header.Visible = True
lblHeader.Visible = True
cmb_rawFields.Enabled = True
End If
'Hide the ClientID panel due to selection of new file
listClient.DataSource = Nothing
pnlClientID.Visible = False
End Sub

Private Sub cmb_rawFields_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_rawFields.SelectionChangeCommitted
lblResult.Visible = False
End Sub
Private Function formatCSVFields(ByVal field As String) As String
Dim str_field As String = ""
str_field = Trim(field)
str_field = str_field.Trim().Replace("""", "").Replace(",", "")
str_field = Trim(str_field.Replace("'", ""))
Return str_field
End Function

Private Function formatPipeFields(ByVal field As String) As String
Dim str_field As String = ""
str_field = Trim(field)
str_field = str_field.Trim().Replace("""", "").Replace("|", "")
str_field = Trim(str_field.Replace("'", ""))
Return str_field
End Function
Private Function formatFields(ByVal field As String, ByVal strFileType As String) As String
Dim str_field As String = ""
Dim strSeperator As String = ","

If strFileType = ".csv" Then
strSeperator = ","
ElseIf strFileType = ".txt" Then
strSeperator = "|"
End If
str_field = Trim(field)
str_field = str_field.Trim().Replace("""", "").Replace(strSeperator, "")
str_field = Trim(str_field.Replace("'", ""))
Return str_field
End Function


Private Function formatExcelFields(ByVal field As String) As String
Dim str_field As String = ""
str_field = Trim(field)
str_field = str_field.Trim().Replace("""", "").Replace(",", "")
str_field = Regex.Match(str_field, "([a-zA-Z0-9\s*-./%&_\$()#]+)").Groups(0).ToString
str_field = Trim(str_field.Replace("'", ""))
Return str_field
End Function
Private Function ProcessNTHoldings()
Dim ntReader As StreamReader
Dim ntExtractWriter() As StreamWriter
Dim str_csvData As String = ""
Dim str_filePath As String
Dim fileName() As String
Dim i As Integer = 0
Dim j As Integer = 0
Dim htabClientMap As New Hashtable
Dim str_clientMapData As String
Dim str_accountNumber As String = ""
Dim str_sql As String
Dim dsClient As New DataSet
Dim _dirPath As String
Dim hTable_account As New Hashtable
Dim hTable_temp As New Hashtable
Dim _blFoundPattern As Boolean = False
Dim str_clientNameCode As String = ""
Dim client_hTableAccCid As New Hashtable
Dim _clientID As Integer = 0
Dim hTable_temp1 As New Hashtable
Dim int_incrVal As Integer
Dim _flag As Boolean = False
Dim str_showResult As String = ""
Dim str_invalidAccount As String = ""
Dim blMapped As Boolean = True
Dim arr_fields() As String
Dim int_index As Integer
Dim hTable As New Hashtable()

Dim strSeperator = ","
Dim strFileExtension = ".csv"
Dim tempdsClient As DataSet
Dim strGlobalAccNum As String
Dim AccNumber, ClientID, CusCustodianID As String
Dim accountNumberList As String()

Try
writelog.WriteLog("Function ProcessNTHoldings..")
str_filePath = filepath
If Path.GetExtension(str_filePath).ToLower = ".csv" Or Path.GetExtension(str_filePath).ToLower = ".txt" Then
If Path.GetExtension(str_filePath).ToLower = ".csv" Then
strSeperator = ","
strFileExtension = ".csv"
ElseIf Path.GetExtension(str_filePath).ToLower = ".txt" Then
strSeperator = "|"
strFileExtension = ".txt"
End If

ntReader = New StreamReader(str_filePath)
Do While ntReader.Peek <> -1
str_csvData = ntReader.ReadLine()
_blFoundPattern = objOperations.isMarking(str_csvData)
If _blFoundPattern = False Then 'checking for pattern

str_clientMapData = objOperations.getNTClientMapData(str_csvData, strSeperator)
If (Not htabClientMap.ContainsValue(str_clientMapData) And Not str_clientMapData = "") Then
htabClientMap.Add(j, str_clientMapData)
j += 1
End If
End If
Loop
ntReader.Close()

'If selected fields in csv contains no data then return -1
If (htabClientMap.Count = 0) Then
Return -1
End If

'Creating sql query
For i = 0 To htabClientMap.Count - 1
str_clientNameCode = str_clientNameCode & "," & "'" & Trim(htabClientMap.Item(i).ToString()) & "'"
Next

str_clientNameCode = str_clientNameCode.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
strGlobalAccNum = str_clientNameCode

'*****************The following section handle the "Accounts Mapped to Multiple Clients for Custodian"**************
str_sql = "DELETE FROM tbl_TempClientAccountMapping "
MySqlHelper.ExecuteNonQuery(str_sql)

'Featch Multiple Mapped Account Nums
If rdb_transaction.Checked = True Then
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) AND ClientNameCode='{2}' Group by AccountNum Having ClientCount>1", cmbCustodian.SelectedValue, str_clientNameCode, Path.GetFileNameWithoutExtension(str_filePath))
Else
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) AND PosClientNameCode='{2}' Group by AccountNum Having ClientCount>1", cmbCustodian.SelectedValue, str_clientNameCode, Path.GetFileNameWithoutExtension(str_filePath))
End If

tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
str_accountNumber = ""
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1
If str_accountNumber = "" Then
str_accountNumber = "'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
Else
str_accountNumber = str_accountNumber + ",'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
End If
Next
End If

'set all clientID and Accnum in tempDB
If str_accountNumber <> "" Then
accountNumberList = str_accountNumber.Replace("'", "").Split(",")
End If

If listClient.Items.Count = 0 Then
tempdsClient.Tables.Clear()
If str_accountNumber <> "" Then

If rdb_transaction.Checked = True Then
' str_sql = String.Format("SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,cast(concat(`AccountNum`,'-', `ClientID`) as char) as `clientAcc` FROM clientaccountmapping WHERE `AccountNum` in ({0}) AND CustodianID ={1} AND ClientNameCode='{2}' GROUP BY ClientID ORDER BY ClientID", str_accountNumber, cmbCustodian.SelectedValue, Path.GetFileNameWithoutExtension(str_filePath))
str_sql = String.Format("SELECT T1.ClientID,T1.AccountNum,cast(concat(T1.AccNums,'-', T1.ClientID) as char) as `clientAcc` FROM " & _
"(SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,CONVERT(GROUP_CONCAT(AccountNum) USING utf8) AccNums FROM clientaccountmapping" & _
" WHERE `AccountNum` in ({0}) AND CustodianID = {1} AND ClientNameCode='{2}' GROUP BY ClientID) AS T1 ORDER BY T1.ClientID ", str_accountNumber, cmbCustodian.SelectedValue, Path.GetFileNameWithoutExtension(str_filePath))
Else
'str_sql = String.Format("SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,cast(concat(`AccountNum`,'-', `ClientID`) as char) as `clientAcc` FROM clientaccountmapping WHERE `AccountNum` in ({0}) AND CustodianID ={1} AND PosClientNameCode='{2}' GROUP BY ClientID ORDER BY ClientID", str_accountNumber, cmbCustodian.SelectedValue, Path.GetFileNameWithoutExtension(str_filePath))
str_sql = String.Format("SELECT T1.ClientID,T1.AccountNum,cast(concat(T1.AccNums,'-', T1.ClientID) as char) as `clientAcc` FROM " & _
"(SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,CONVERT(GROUP_CONCAT(AccountNum) USING utf8) AccNums FROM clientaccountmapping" & _
" WHERE `AccountNum` in ({0}) AND CustodianID = {1} AND PosClientNameCode='{2}' GROUP BY ClientID) AS T1 ORDER BY T1.ClientID ", str_accountNumber, cmbCustodian.SelectedValue, Path.GetFileNameWithoutExtension(str_filePath))
End If
tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
If tempdsClient.Tables(0).Rows.Count >= 1 Then
MessageBox.Show("Account number has multiple client IDs mapped in clientaccountmapping table", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
'txtFilename.Text = fileName
listClient.DataSource = tempdsClient.Tables(0)
listClient.DisplayMember = "ClientID"
listClient.ValueMember = "clientAcc"
pnlClientID.Visible = True
'setListBoxSelection(listClient, Path.GetFileNameWithoutExtension(txt_fileName.Text))
Exit Function
End If
End If
Else
Dim arrSelected As String = ""
Dim intClientID As String
Dim CurrAccNumbers As String = ""
Dim strInsertVal As String = ""
Dim strCusID As String = "0"
Dim strClientAccountNo As String
Dim accountIndex As Integer
strCusID = Str(cmbCustodian.SelectedValue)

If listClient.SelectedItems.Count > 0 Then
'If listClient.SelectedItems.Count >= accountNumberList.Length Then
Dim strSelAcc As [String]
For Each selecteditem As [Object] In listClient.SelectedItems
strSelAcc = selecteditem(1)
intClientID = selecteditem(0)

strClientAccountNo = selecteditem(2)
Dim strAccountNos = strClientAccountNo.Split("-")
Dim arrAccountNumbers = strAccountNos(0).Split(",")
Dim arrAccountNos = FilterDuplicates(arrAccountNumbers)

For accountIndex = 0 To arrAccountNos.Length - 2
strSelAcc = arrAccountNos(accountIndex)
If strInsertVal = "" Then
strInsertVal = "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
Else
strInsertVal = strInsertVal + "," + "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
End If

If arrSelected = "" Then
arrSelected = strSelAcc
CurrAccNumbers = "//" + strSelAcc + "//"
Else
Dim searchAcc As String = "//" + strSelAcc + "//"
If CurrAccNumbers.IndexOf(searchAcc) = -1 Then
arrSelected = arrSelected + "," + strSelAcc
CurrAccNumbers = CurrAccNumbers + strSelAcc + "//"
Else
MessageBox.Show("Multiple clientID's are selected for single AccountNumber", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
'Exit Try
Exit Function
End If
End If
Next


Next
'Else
' MessageBox.Show("Some Account Numbers are not mapped in the list...Please map all Account Numbers", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
' 'Exit Try
' Exit Function
'End If

ElseIf listClient.SelectedItems.Count <= 0 Then
MessageBox.Show("ClientIDs are not selected in the list...Please select ClientIDs", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Function
End If

str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping(AccountNum, ClientID,CustodianID) VALUES {0}", strInsertVal)
MySqlHelper.ExecuteNonQuery(str_sql)
End If

'Featch Single Mapped Account Nums

If rdb_transaction.Checked = True Then
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID, CAST(Trim(`CustodianID`) AS CHAR) CustodianID, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) AND ClientNameCode='{2}' Group by AccountNum Having ClientCount=1", cmbCustodian.SelectedValue, strGlobalAccNum, Path.GetFileNameWithoutExtension(str_filePath))
Else
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID, CAST(Trim(`CustodianID`) AS CHAR) CustodianID,count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) AND PosClientNameCode='{2}' Group by AccountNum Having ClientCount=1", cmbCustodian.SelectedValue, strGlobalAccNum, Path.GetFileNameWithoutExtension(str_filePath))
End If
tempdsClient.Clear()
tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1

AccNumber = tempdsClient.Tables(0).Rows(i)("AccountNum").ToString
ClientID = tempdsClient.Tables(0).Rows(i)("ClientID").ToString
CusCustodianID = tempdsClient.Tables(0).Rows(i)("CustodianID").ToString
str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping (AccountNum, ClientID,CustodianID) " & _
" VALUES('{0}',{1},{2})", AccNumber, ClientID, CusCustodianID)
MySqlHelper.ExecuteNonQuery(str_sql)
Next
End If

'*************The End of the section which handle the "Accounts Mapped to Multiple Clients for Custodian***************

'Executing query and from result creating filenames and initializing Streamwriter instances

writelog.WriteLog("Executing query and from result creating filenames and initializing Streamwriter instances")

str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID FROM tbl_TempClientAccountMapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1})", cmbCustodian.SelectedValue, strGlobalAccNum)
dsClient = MySqlHelper.ExecuteDataSet(str_sql)

If (dsClient.Tables(0).Rows.Count = 0) Then
str_clientNameCode = strGlobalAccNum
Dim resAcc As String = str_clientNameCode.Replace("'", "")
str_showResult = ""
str_showResult = "Account numbers in file that does not have a client id lookup are listed below:" & vbNewLine & resAcc & vbNewLine & str_showResult
ExtractResult = str_showResult
Return -2
Else
_dirPath = GetOutputDirectory()
j = 0
For i = 0 To dsClient.Tables(0).Rows.Count - 1

client_hTableAccCid.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)

If (Not hTable_account.ContainsValue(dsClient.Tables(0).Rows(i)("ClientID").ToString)) Then
hTable_account.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)
hTable_temp.Add(j, dsClient.Tables(0).Rows(i)("ClientID").ToString)
hTable_temp1.Add(dsClient.Tables(0).Rows(i)("ClientID").ToString, j)
j = j + 1
End If
Next

ReDim fileName(hTable_account.Count - 1)
ReDim ntExtractWriter(hTable_account.Count - 1)

End If

For i = 0 To hTable_account.Count - 1
If (rdb_transaction.Checked) Then
fileName(i) = Path.GetFullPath(_dirPath) & Path.DirectorySeparatorChar & hTable_temp.Item(i).ToString & "_Trans_" & txt_startDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_" & txt_endDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
ntExtractWriter(i) = File.CreateText(fileName(i))
Else
fileName(i) = Path.GetFullPath(_dirPath) & Path.DirectorySeparatorChar & hTable_temp.Item(i).ToString & "_Pos_" & txt_asOfDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
ntExtractWriter(i) = File.CreateText(fileName(i))
End If
Next

'Extracting csv files
writelog.WriteLog("Extracting csv/txt files")

Dim bl_write As Boolean = False
ntReader = Nothing
_blFoundPattern = False
str_clientMapData = ""
ntReader = New StreamReader(str_filePath)
Do While ntReader.Peek <> -1
bl_write = True
str_csvData = ntReader.ReadLine()
_blFoundPattern = objOperations.isMarking(str_csvData)
If _blFoundPattern = False Then
bl_write = True
str_clientMapData = objOperations.getNTClientMapData(str_csvData, strSeperator)
If _flag = False Then
_flag = True 'Setting the flag at the first occourance of the pattern
End If
If Trim(str_clientMapData) <> "" Then
If client_hTableAccCid.Contains(Trim(str_clientMapData)) Then
_clientID = client_hTableAccCid(str_clientMapData)
int_incrVal = hTable_temp1(_clientID.ToString)
blMapped = True
Else
blMapped = False
End If
End If
Else
bl_write = False
End If
If bl_write = True AndAlso _flag = True AndAlso blMapped = True Then

If strFileExtension = ".txt" Then
str_csvData = str_csvData.Replace("|", ",")
End If
'Write
ntExtractWriter(int_incrVal).Write(str_csvData)
ntExtractWriter(int_incrVal).Write(vbNewLine)
End If
Loop

Try
str_showResult = "Total files created = " & hTable_temp1.Count.ToString & vbNewLine
For i = 0 To hTable_temp1.Count - 1
ntExtractWriter(i).Dispose()
str_showResult = str_showResult & fileName(i) & " Saved Successfully" & vbNewLine
Next
For i = 0 To htabClientMap.Count - 1
If (Not client_hTableAccCid.ContainsKey(Trim(htabClientMap.Item(i)))) Then
str_invalidAccount = str_invalidAccount & htabClientMap.Item(i) & ","
End If
Next
str_invalidAccount = str_invalidAccount.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
If (Not str_invalidAccount = "") Then
str_showResult = "ClientNameCode in csv/txt file that does not have a client id lookup are listed below:" & vbNewLine & str_invalidAccount & vbNewLine & str_showResult
End If
ExtractResult = str_showResult

Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try

Else
MessageBox.Show("Invalid file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return 0
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try
writelog.WriteLog("Exiting Function ProcessNTHoldings..")
Return 1
End Function

Private Sub showNTResult(ByVal int_result As Integer)
Select Case int_result
Case 1
lblResult.Text = ExtractResult
lblResult.Visible = True
Case -1
MessageBox.Show("There is no data found in the selected CSV file", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
Case -2
MessageBox.Show("There is no matching client ids for the selected custodian ,account number and Job Number found in the database", "File Prep Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
lblResult.Text = ""
lblResult.Text = ExtractResult
lblResult.Visible = True
Case -3
MessageBox.Show("Error while accessing database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Select
writelog.WriteLog("----------------------------------------------------------------------------")
End Sub
Private Function isNTHeaderExtractProcess() As Integer
Dim str_sql As String
Dim i As Integer
Dim str_accountNumber As String = ""
Dim dsClient As DataSet
Dim arr_fields() As String
Dim isHeader As Boolean = True
Dim int_index As Integer
Dim hTable As New Hashtable()
Dim hTable_account As New Hashtable()
Dim client_hTableAccCid As New Hashtable()
Dim ht_temp As New Hashtable()
Dim ht_temp2 As New Hashtable()
Dim j As Integer = 0
Dim extractWriter() As StreamWriter
Dim dirPath As String
Dim fileName() As String
Dim str_csvData As String
Dim Reader As System.IO.StreamReader
Dim str_invalidAccount As String = ""

Dim tempdsClient As DataSet
Dim strGlobalAccNum As String
Dim AccNumber, ClientID, CusCustodianID As String
Dim accountNumberList As String()


Try
'Selecting accountnumber from csv fields and stored in hashtable
Try
writelog.WriteLog("Selecting accountnumber from csv fields and stored in hashtable [isHeaderExtractProcess()]")
Reader = New StreamReader(str_loadFilePath)

Do While Reader.Peek <> -1
str_csvData = Reader.ReadLine()
arr_fields = Regex.Split(str_csvData, vbTab)
If isHeader Then
For i = 0 To arr_fields.Length - 1
If (formatCSVFields(arr_fields(i).ToString) = cmb_rawFields.Text) Then
int_index = i
Exit For
End If
Next
isHeader = False
Else
If (Not arr_fields.Length - 1 < int_index) Then
If (Not hTable.ContainsValue(formatExcelFields(arr_fields(int_index).ToString)) And Not formatExcelFields(arr_fields(int_index).ToString) = "") Then
hTable.Add(j, formatExcelFields(arr_fields(int_index).ToString))
j += 1
End If
End If
End If
Loop
Reader.Close()
Catch ex As FileLoadException
MessageBox.Show(ex.Message, "FileLoad", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try

'If selected fields in csv contains no data then return -1
If (hTable.Count = 0) Then
Return -1
End If

'Creating sql query
For i = 0 To hTable.Count - 1
str_accountNumber = str_accountNumber & "," & "'" & Trim(hTable.Item(i).ToString()) & "'"
Next

str_accountNumber = str_accountNumber.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
strGlobalAccNum = str_accountNumber

'**************The following section handle the "Accounts Mapped to Multiple Clients for Custodian*******************"

str_sql = "DELETE FROM tbl_TempClientAccountMapping "
MySqlHelper.ExecuteNonQuery(str_sql)

'Featch Multiple Mapped Account Nums
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum, count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) Group by AccountNum Having ClientCount>1", cmbCustodian.SelectedValue, str_accountNumber)
tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
str_accountNumber = ""
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1
If str_accountNumber = "" Then
str_accountNumber = "'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
Else
str_accountNumber = str_accountNumber + ",'" + tempdsClient.Tables(0).Rows(i)("AccountNum").ToString + "'"
End If
Next
End If

'set all clientID and Accnum in tempDB
If str_accountNumber <> "" Then
accountNumberList = str_accountNumber.Replace("'", "").Split(",")
End If

If listClient.Items.Count = 0 Then
tempdsClient.Tables.Clear()
If str_accountNumber <> "" Then
MySqlHelper.strConnectionString = DMConstants.strDataScrubberConnectionString
'str_sql = String.Format("SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,cast(concat(`AccountNum`,'-', `ClientID`) as char) as `clientAcc` FROM clientaccountmapping WHERE `AccountNum` in ({0}) AND CustodianID ={1} Group By ClientID ORDER BY ClientID", str_accountNumber, cmbCustodian.SelectedValue)
str_sql = String.Format("SELECT T1.ClientID,T1.AccountNum,cast(concat(T1.AccNums,'-', T1.ClientID) as char) as `clientAcc` FROM " & _
"(SELECT CAST(Trim(`ClientID`) AS CHAR) ClientID,`AccountNum`,CONVERT(GROUP_CONCAT(AccountNum) USING utf8) AccNums FROM clientaccountmapping" & _
" WHERE `AccountNum` in ({0}) AND CustodianID = {1} GROUP BY ClientID) AS T1 ORDER BY T1.ClientID ", str_accountNumber, cmbCustodian.SelectedValue)

tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)

If tempdsClient.Tables(0).Rows.Count >= 1 Then
MessageBox.Show("Account number has multiple client IDs mapped in clientaccountmapping table", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)

listClient.DataSource = tempdsClient.Tables(0)
listClient.DisplayMember = "ClientID"
listClient.ValueMember = "clientAcc"
pnlClientID.Visible = True
'setListBoxSelection(listClient, Path.GetFileNameWithoutExtension(txt_fileName.Text))
Exit Function
End If
End If
Else
Dim arrSelected As String = ""
Dim intClientID As String
Dim CurrAccNumbers As String = ""
Dim strInsertVal As String = ""
Dim strCusID As String = "0"
Dim strClientAccountNo As String
Dim accountIndex As Integer
strCusID = Str(cmbCustodian.SelectedValue)

If listClient.SelectedItems.Count > 0 Then
'If listClient.SelectedItems.Count >= accountNumberList.Length Then
Dim strSelAcc As [String]
For Each selecteditem As [Object] In listClient.SelectedItems
strSelAcc = selecteditem(1)
intClientID = selecteditem(0)

strClientAccountNo = selecteditem(2)
Dim strAccountNos = strClientAccountNo.Split("-")
Dim arrAccountNumbers = strAccountNos(0).Split(",")
Dim arrAccountNos = FilterDuplicates(arrAccountNumbers)

For accountIndex = 0 To arrAccountNos.Length - 2
strSelAcc = arrAccountNos(accountIndex)
If strInsertVal = "" Then
strInsertVal = "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
Else
strInsertVal = strInsertVal + "," + "('" + strSelAcc + "'," + intClientID + "," + strCusID + ")"
End If

If arrSelected = "" Then
arrSelected = strSelAcc
CurrAccNumbers = "//" + strSelAcc + "//"
Else
Dim searchAcc As String = "//" + strSelAcc + "//"
If CurrAccNumbers.IndexOf(searchAcc) = -1 Then
arrSelected = arrSelected + "," + strSelAcc
CurrAccNumbers = CurrAccNumbers + strSelAcc + "//"
Else
MessageBox.Show("Multiple clientID's are selected for single AccountNumber", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
'Exit Try
Exit Function
End If
End If

Next


Next
'Else
' MessageBox.Show("Some Account Numbers are not mapped in the list...Please map all Account Numbers", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
' 'Exit Try
' Exit Function
'End If
ElseIf listClient.SelectedItems.Count <= 0 Then
MessageBox.Show("Account Numbers are not mapped in the list...Please map all Account Numbers", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Function
End If

str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping(AccountNum, ClientID,CustodianID) VALUES {0}", strInsertVal)
MySqlHelper.ExecuteNonQuery(str_sql)
End If

'Insert the single mapped account number
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID, CAST(Trim(`CustodianID`) AS CHAR) CustodianID,count(AccountNum) AS ClientCount FROM clientaccountmapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1}) Group by AccountNum Having ClientCount=1", cmbCustodian.SelectedValue, strGlobalAccNum)
tempdsClient.Clear()
tempdsClient = MySqlHelper.ExecuteDataSet(str_sql)
If (tempdsClient.Tables(0).Rows.Count > 0) Then
For i = 0 To tempdsClient.Tables(0).Rows.Count - 1

AccNumber = tempdsClient.Tables(0).Rows(i)("AccountNum").ToString
ClientID = tempdsClient.Tables(0).Rows(i)("ClientID").ToString
CusCustodianID = tempdsClient.Tables(0).Rows(i)("CustodianID").ToString

str_sql = String.Format("INSERT INTO tbl_TempClientAccountMapping (AccountNum, ClientID,CustodianID) " & _
" VALUES('{0}',{1},{2})", AccNumber, ClientID, CusCustodianID)
MySqlHelper.ExecuteNonQuery(str_sql)
Next
End If

'******************The End of the section which handle the "Accounts Mapped to Multiple Clients for Custodian**************"

'Executing query and from result creating filenames and initializing Streamwriter instances
Try
writelog.WriteLog("Executing query and from result creating filenames and initializing Streamwriter instances")
str_sql = String.Format("SELECT Trim(`AccountNum`) AS AccountNum,CAST(Trim(`ClientID`) AS CHAR) ClientID FROM tbl_TempClientAccountMapping WHERE CustodianID ={0} AND" & _
" `ClientID` IS NOT NULL AND `AccountNum` IN({1})", cmbCustodian.SelectedValue, strGlobalAccNum)
dsClient = MySqlHelper.ExecuteDataSet(str_sql)
If (dsClient.Tables(0).Rows.Count = 0) Then
str_accountNumber = strGlobalAccNum
Dim resAcc As String = str_accountNumber.Replace("'", "")
str_showResult = ""
str_showResult = "Account numbers in file that does not have a client id lookup are listed below:" & vbNewLine & resAcc & vbNewLine & str_showResult

ExtractResult = str_showResult
Return -2
Else
Try
dirPath = GetOutputDirectory()
j = 0
For i = 0 To dsClient.Tables(0).Rows.Count - 1
client_hTableAccCid.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)
If (Not hTable_account.ContainsValue(dsClient.Tables(0).Rows(i)("ClientID").ToString)) Then
hTable_account.Add(dsClient.Tables(0).Rows(i)("AccountNum").ToString, dsClient.Tables(0).Rows(i)("ClientID").ToString)
ht_temp.Add(j, dsClient.Tables(0).Rows(i)("ClientID").ToString)
ht_temp2.Add(dsClient.Tables(0).Rows(i)("ClientID").ToString, j)
j = j + 1
End If
Next

ReDim fileName(hTable_account.Count - 1)
ReDim extractWriter(hTable_account.Count - 1)

For i = 0 To hTable_account.Count - 1
If (rdb_transaction.Checked) Then
fileName(i) = Path.GetFullPath(dirPath) & Path.DirectorySeparatorChar & ht_temp.Item(i).ToString & "_Trans_" & txt_startDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_" & txt_endDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
extractWriter(i) = File.CreateText(fileName(i))
Else
fileName(i) = Path.GetFullPath(dirPath) & Path.DirectorySeparatorChar & ht_temp.Item(i).ToString & "_Pos_" & txt_asOfDate.Text.Trim().Replace("/", "-").Replace("\", "-") & "_splitORIG" & ".csv"
extractWriter(i) = File.CreateText(fileName(i))
End If
Next
Catch ex As IndexOutOfRangeException
MessageBox.Show("Error in array index.please contact your software vendor." & vbCrLf & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try


'Extracting csv files
Try
writelog.WriteLog("Extracting csv files")
isHeader = True
Reader = Nothing
Reader = New StreamReader(str_loadFilePath)
Do While Reader.Peek <> -1
str_csvData = Reader.ReadLine()
Dim strRes As String = ""
Dim strResArry As String()
Dim strArry() As String = str_csvData.Split(vbTab)
For i = 0 To strArry.Length - 1
Dim processedVal As String = Regex.Match(strArry(i), "([a-zA-Z0-9\s*-./%&_\$()#]+)").Groups(0).ToString
processedVal = processedVal.Replace(",", " ")
If i = 0 Then
strRes = processedVal
Else
strRes = strRes + vbTab + processedVal
End If
Next
strResArry = strRes.Split(vbTab)
str_csvData = Join(strResArry, ",")
' str_csvData = strRes
If isHeader Then
For i = 0 To extractWriter.Length - 1
extractWriter(i).Write(str_csvData)
Next
isHeader = False
Else
arr_fields = Regex.Split(str_csvData, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
'arr_fields = Regex.Split(str_csvData, vbTab)
If (Not arr_fields.Length - 1 < int_index) Then
If (client_hTableAccCid.Contains(Trim(formatExcelFields(arr_fields(int_index).ToString)))) Then
extractWriter(ht_temp2.Item(client_hTableAccCid.Item(Trim(formatExcelFields(arr_fields(int_index).ToString))))).Write(vbNewLine)
extractWriter(ht_temp2.Item(client_hTableAccCid.Item(Trim(formatExcelFields(arr_fields(int_index).ToString))))).Write(str_csvData)
End If
End If
End If
Loop
Catch ex As IndexOutOfRangeException
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As FileLoadException
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
Finally
Reader.Close()
End Try


'Disposing streamwriter instances and creating messages
Try
str_showResult = "Total files created = " & ht_temp.Count.ToString & vbNewLine
For i = 0 To ht_temp.Count - 1
extractWriter(i).Dispose()
str_showResult = str_showResult & fileName(i) & "Saved Successfully" & vbNewLine
Next
For i = 0 To hTable.Count - 1
If (Not client_hTableAccCid.ContainsKey(Trim(hTable.Item(i)))) Then
str_invalidAccount = str_invalidAccount & hTable.Item(i) & ","
End If
Next
str_invalidAccount = str_invalidAccount.TrimStart(",", "").TrimEnd(",", "").Replace("""", "")
If (Not str_invalidAccount = "") Then
str_showResult = "Account numbers in file that does not have a client id lookup are listed below:" & vbNewLine & str_invalidAccount & vbNewLine & str_showResult
End If
ExtractResult = str_showResult
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return -5
End Try

Catch ex As Exception
MessageBox.Show(ex.Message)
Return -5
End Try
writelog.WriteLog("Exiting isHeaderExtractProcess()")
Return 1
End Function

'*******************************************************************************************************************************
'------------------------------Description for the method setListBoxSelection---------------------------------------------------

'File Prep Utility should be able to make an “educated guess” as to which clientid the file should split out to.
'The guess highlights the clientids on the scroll box automatically.
'For example, a file name may be 111_122_223_trans_11-1-10_11-30-10_ORIG,
'and the clientid 122 has accounts that are also found in another clientid.
'Automatically highlight the choices for 122 and allow the user to either choose to split the file
'or else select the other clientid choices associated with the accounts

'********************************************************************************************************************************
'Public Sub setListBoxSelection(ByVal lstClient As ListBox, ByVal fileName As String)
' Dim arrClientIDs()
' Dim intClientIDs()
' Dim strItem, strClientID As String
' Dim i, j As Integer
' Dim blnFlag As Boolean = False
' Try
' If lstClient.Items.Count > 0 Then


' lstClient.SetSelected(0, False)
' arrClientIDs = fileName.Split("_")
' strClientID = ""

' For i = 0 To arrClientIDs.Length - 1
' strItem = arrClientIDs(i).ToString()
' If IsNumeric(strItem) Then
' If strClientID = "" Then
' strClientID = strItem & ","
' Else
' strClientID = strClientID & strItem & ","
' End If
' End If
' Next
' intClientIDs = strClientID.Split(",")
' For i = 0 To intClientIDs.Length - 2
' strItem = intClientIDs(i)
' For j = 0 To lstClient.Items.Count - 1
' arrClientIDs = lstClient.GetItemText(lstClient.Items(j)).ToString.Split("-")
' If Int(strItem) = Int(arrClientIDs(1)) Then
' lstClient.SetSelected(j, True)
' blnFlag = True
' Exit For
' End If
' Next
' Next
' If blnFlag = False Then
' lstClient.SetSelected(0, True)
' End If
' End If

' Catch ex As Exception
' writelog.WriteLog("Error in the method setListBoxSelection()")
' End Try

'End Sub



'-------------------------------------------Description------------------------------------------------------
'Rremove all sets of quotations,additional spaces between the data in a given field and its separation commas
'------------------------------------------------------------------------------------------------------------

Private Function PreProcessRowValues(ByVal row As String, ByVal strFileExtension As String) As String

Dim strArr() As String
Dim strRetval As String = ""
Dim seperator As String = ","
Try
If Not row Is Nothing Then

If strFileExtension = ".txt" Then
strArr = row.Split("|")
seperator = "|"
ElseIf strFileExtension = ".csv" Then
strArr = Regex.Split(row, ",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
seperator = ","
End If

Dim iter As Integer = 0
Dim matchexpr As String = "^(\"")(.+)(\"")$"
Dim matchexpr1 As String = "^(\"")(\s*)(\"")$"
Dim matchexpr2 As String = "^(')('.+?')(')$".Replace("'", """")

For iter = 0 To strArr.Length - 1
Dim tempstr As String = strArr(iter)

If (Regex.IsMatch(tempstr, matchexpr) And Not Regex.IsMatch(tempstr, seperator)) Then
strArr(iter) = Regex.Match(tempstr, matchexpr).Groups(2).Value
strArr(iter) = strArr(iter).Replace("'", "").Replace("\", "").Replace("""", "").Trim
End If

If Regex.IsMatch(tempstr, matchexpr1) Then
strArr(iter) = Regex.Match(tempstr, matchexpr).Groups(2).Value
strArr(iter) = strArr(iter).Replace("'", "").Replace("\", "").Replace("""", "").Trim

End If
strArr(iter) = strArr(iter).Replace("'", "").Replace("\", "").Trim
strArr(iter) = strArr(iter).Replace(",", "")
strArr(iter) = strArr(iter).Replace("""", "")
strArr(iter) = strArr(iter).Replace(" ,", " ")
strArr(iter) = strArr(iter).Replace(", ", " ")

Next
strRetval = Join(strArr, seperator)
If strFileExtension = ".txt" Then
strRetval = Regex.Replace(strRetval, "\s+", "")
End If

End If

Catch ex As Exception
writelog.WriteLog("Exception on function PreProcessRowvalues")
writelog.WriteLog(ex.Message)
End Try

Return strRetval


End Function


'Private Function setListBoxSeperation1(ByVal dtTable As DataTable) As DataTable
' Dim i, j As Integer
' Dim strOldAccountNumber As String = ""
' Dim strCurrentAccountNumber As String = ""
' Dim dtClientTable As New DataTable
' Dim drClientData As DataRow

' Try
' dtClientTable = dtTable
' If dtTable.Rows.Count > 1 Then
' j = 0

' For i = 0 To dtTable.Rows.Count - 1

' strCurrentAccountNumber = dtTable.Rows(i).ItemArray(1)
' If strOldAccountNumber <> strCurrentAccountNumber And i <> 0 And strCurrentAccountNumber <> "" Then
' drClientData = dtClientTable.NewRow()
' drClientData.Item(0) = ""
' drClientData.Item(1) = ""
' drClientData.Item(2) = ""
' dtClientTable.Rows.InsertAt(drClientData, j)
' End If
' strOldAccountNumber = strCurrentAccountNumber
' j = j + 1

' Next

' End If
' Return dtClientTable
' Catch ex As Exception
' MsgBox(ex.ToString())
' End Try

'End Function

'Private Sub setListBoxSeperation2(ByVal lstClient As ListBox)
' Dim strAccountNum As String = ""
' Dim j As Integer = 0

' Try
' For j = 0 To lstClient.Items.Count - 1
' strAccountNum = lstClient.GetItemText(lstClient.Items(j)).ToString
' If strAccountNum = "" Then
' lstClient.SetSelected(j, False)

' End If
' Next
' Catch ex As Exception

' End Try
'End Sub


'tempdsClient.Tables(0)

Private Sub listClient_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles listClient.MouseClick

Dim strClientID As String = ""
Dim intCordinate As Int16 = listClient.IndexFromPoint(e.X, e.Y)
If intCordinate <> -1 Then
Dim currentItem As Object = listClient.Items(intCordinate)
Dim LatestItemSelected As Object
If listClient.SelectedItems.Contains(currentItem) Then
LatestItemSelected = currentItem
Dim dataRow As DataRowView = LatestItemSelected
strClientID = dataRow.Item(0).ToString()
End If

CheckMultipleClientID(listClient, strClientID)
End If
End Sub

Private Sub CheckMultipleClientID(ByVal lstClient As ListBox, ByVal strSelectedClientID As String)
Dim arrSelected As String = ""
Dim intClientID As String
Dim CurrAccNumbers As String = ""
Dim strClientID As String = ""
Dim strClientAccountNo As String

Dim i, j As Integer
Try
If lstClient.SelectedItems.Count > 0 Then

Dim strSelAcc As [String]
For Each selecteditem As [Object] In listClient.SelectedItems
strClientAccountNo = selecteditem(2)
Dim strAccountNos = strClientAccountNo.Split("-")
Dim arrAccountNumbers = strAccountNos(0).Split(",")
Dim arrAccountNos = FilterDuplicates(arrAccountNumbers)
strSelAcc = selecteditem(1)
intClientID = selecteditem(0)

For i = 0 To arrAccountNos.Length - 2

strSelAcc = arrAccountNos(i)
If arrSelected = "" Then
arrSelected = strSelAcc
CurrAccNumbers = "//" + strSelAcc + "//"
Else
Dim searchAcc As String = "//" + strSelAcc + "//"

If CurrAccNumbers.IndexOf(searchAcc) = -1 Then
arrSelected = arrSelected + "," + strSelAcc
CurrAccNumbers = CurrAccNumbers + strSelAcc + "//"
Else
MessageBox.Show("The account number available for this ClientID is already selected with another ClientID.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

For j = 0 To lstClient.Items.Count - 1
strClientID = lstClient.GetItemText(lstClient.Items(j)).ToString()
If strSelectedClientID = Int(strClientID) Then
lstClient.SetSelected(j, False)
Exit Sub
End If
Next
Exit Sub
End If
End If

Next
Next
End If
Catch ex As Exception
'MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
writelog.WriteLog("Exception on function CheckMultipleClientID")
writelog.WriteLog(ex.Message)
End Try
End Sub

Function FilterDuplicates(ByVal arrAccountNos As Array) As Array
Dim index As Integer
Dim dups As Integer
Dim strResult As String = ""

Try
' we use a hashtable to track duplicates
Dim htAccountNos As New Collections.Hashtable(arrAccountNos.Length * 2)

For index = 0 To arrAccountNos.Length - 1
Dim value As Object = arrAccountNos.GetValue(index)

If htAccountNos.Contains(value) Then
' we've found a duplicate
arrAccountNos.SetValue(Nothing, index)
dups += 1
Else
' add to the hashtable (use a dummy Nothing value)
htAccountNos.Add(value, Nothing)
' if we've found one or more duplicates so far
' we need to move elements towards lower indices
If dups > 0 Then
arrAccountNos.SetValue(value, index - dups)
arrAccountNos.SetValue(Nothing, index)
End If
End If
Next

For index = 0 To arrAccountNos.Length - 1
If arrAccountNos(index) <> Nothing Then
If strResult = "" Then
strResult = arrAccountNos(index) + ","
Else
strResult = strResult + arrAccountNos(index) + ","
End If
End If
Next

Dim resArray = strResult.Split(",")

' return the number of duplicates
Return resArray

Catch ex As Exception
'MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
writelog.WriteLog("Exception on function FilterDuplicates")
writelog.WriteLog(ex.Message)
End Try

End Function




End Class