RecordOrganizer/app/DD-Record-Organizer/frmUserKonfig.vb

Public Class frmUserKonfig
    Private Shared _Instance As frmUserKonfig = Nothing
    Public Shared Function Instance() As frmUserKonfig
        If _Instance Is Nothing OrElse _Instance.IsDisposed = True Then
            _Instance = New frmUserKonfig
        End If
        _Instance.BringToFront()
        Return _Instance
    End Function
    Private Sub TBDD_USERBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles TBDD_USERBindingNavigatorSaveItem.Click
        Save_User()
    End Sub

    Private Sub frmUserKonfig_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Load_User()
        Load_Clients()
    End Sub
    Public Sub Load_User()
        Try
            Me.TBDD_USERTableAdapter.Connection.ConnectionString = MYDB_ECM.CurrentConnectionString
            Me.TBDD_USERTableAdapter.Fill(Me.DD_DMSDataSet.TBDD_USER)

            Me.TBDD_USER_GROUPSTableAdapter.Connection.ConnectionString = MYDB_ECM.CurrentConnectionString
            Me.TBDD_USER_GROUPSTableAdapter.Fill(Me.DD_DMSDataSet.TBDD_USER_GROUPS)
            Dim sql = "select GUID, CLIENT_NAME FROM TBDD_CLIENT order by Client_Name"
            Dim DT_CLIENT As DataTable = MYDB_ECM.GetDatatable(sql)
        Catch ex As Exception
            MsgBox("Error in frmUserKonfig_Load:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub
    Sub Load_Clients()
        Try
            Me.TBDD_CLIENTTableAdapter.Connection.ConnectionString = MYDB_ECM.CurrentConnectionString
            Me.TBDD_CLIENTTableAdapter.Fill(Me.DD_DMSDataSet.TBDD_CLIENT)
        Catch ex As Exception
            MsgBox("Error in Load Clients:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try

    End Sub
    Sub Save_Clients()
        Try
            Me.TBDD_CLIENTBindingSource.EndEdit()
            If DD_DMSDataSet.TBDD_CLIENT.GetChanges Is Nothing = False Then
                Me.CHANGED_WHOTextBox2.Text = USER_USERNAME
                Me.TBDD_CLIENTBindingSource.EndEdit()
                Me.TBDD_CLIENTTableAdapter.Update(Me.DD_DMSDataSet.TBDD_CLIENT)
                Label7.Visible = True
            Else
                Label7.Visible = False
            End If
        Catch ex As Exception
            MsgBox("Error in Save Clients:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Sub Save_User()
        Try
            Me.TBDD_USERBindingSource.EndEdit()
            If DD_DMSDataSet.TBDD_USER.GetChanges Is Nothing = False Then
                Me.CHANGED_WHOTextBox.Text = USER_USERNAME
                Me.TBDD_USERBindingSource.EndEdit()
                Me.TBDD_USERTableAdapter.Update(Me.DD_DMSDataSet.TBDD_USER)
                lblsave.Visible = True
            Else
                lblsave.Visible = False
            End If
        Catch ex As Exception
            MsgBox("Error in Save USer:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub
    Private Sub TBDD_USERBindingSource_AddingNew(sender As Object, e As System.ComponentModel.AddingNewEventArgs) Handles TBDD_USERBindingSource.AddingNew
        DD_DMSDataSet.TBDD_USER.ADDED_WHOColumn.DefaultValue = USER_USERNAME
    End Sub

    Private Sub tsAddUser_Click(sender As Object, e As EventArgs) Handles tsAddUser.Click
        Dim frm As New frmUserKonfig_AddUsers
        Cursor = Cursors.WaitCursor
        frm.ShowDialog()

        Load_User()
        Cursor = Cursors.Default
    End Sub

    Private Sub tbRefreshUsers_Click(sender As Object, e As EventArgs) Handles tbRefreshUsers.Click
        Load_User()
    End Sub

    Private Sub btnAddUserToGroup_Click(sender As Object, e As EventArgs) Handles btnAddUserToGroup.Click
        Try

            Dim group As DataRowView = GetSelectedGroupObject()

            Dim userID As Integer = USER_GUIDTextBox.Text

            Dim groupID As Integer = group.Item(0)
            Dim groupName As String = group.Item(1)

            Dim SQL = String.Format("SELECT GUID FROM TBDD_GROUPS_USER WHERE USER_ID = {0} AND GROUP_ID = {1}", userID, groupID)

            Dim userInGroup = MYDB_ECM.GetScalarValue(SQL)

            If Not IsNothing(userInGroup) Then
                MsgBox(String.Format("Der Benutzer {0} ist bereits Mitglied der Gruppe {1}", USERNAMETextBox.Text, groupName), MsgBoxStyle.Exclamation)
                Exit Sub
            Else
                SQL = String.Format("INSERT INTO TBDD_GROUPS_USER (USER_ID, GROUP_ID, ADDED_WHO) VALUES ({0}, {1}, '{2}')", userID, groupID, USER_USERNAME)
                If MYDB_ECM.ExecuteNonQuery(SQL) = True Then
                    LoadGroupsForSelectedUser()
                Else
                    MsgBox(String.Format("Fehler beim Hinzufügen des Benutzers {0} zur Gruppe {1}", USERNAMETextBox.Text, groupName))
                End If
            End If
        Catch ex As Exception
            MsgBox("Error in btnAddUserToGroup_Click:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Private Function GetSelectedGroupObject() As DataRowView
        Dim group As DataRowView = cmbGroup.SelectedItem
        Return group
    End Function

    Private Function GetSelectedGroupObjectFromList() As String
        Return lbGroups.SelectedItem
    End Function

    Private Sub LoadGroupsForSelectedUser()
        Dim userID As Integer
        Try
            userID = USER_GUIDTextBox.Text
        Catch ex As Exception
            Exit Sub
        End Try
        Dim SQL As String = String.Format("SELECT GROUP_NAME FROM VWPMO_USERS_GROUPS WHERE USER_ID = {0}", userID)

        Try
            Dim dt As DataTable = MYDB_ECM.GetDatatable(SQL)

            lbGroups.Items.Clear()
            For Each row As DataRow In dt.Rows
                lbGroups.Items.Add(row.Item(0).ToString)
            Next
        Catch ex As Exception
            MsgBox("Error in LoadGroupsForSelectedUser:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try

    End Sub
    Private Sub GetMandantForSelectedUser()
        Dim userID As Integer
        Try
            userID = USER_GUIDTextBox.Text
        Catch ex As Exception
            Exit Sub
        End Try

        Dim SQL As String = String.Format("SELECT T.GUID, T1.CLIENT_NAME FROM TBDD_CLIENT_USER T, TBDD_CLIENT T1 WHERE T.CLIENT_ID = T1.GUID AND T.USER_ID = {0} ORDEr BY T1.CLIENT_NAME", userID)

        Try
            Dim dt As DataTable = MYDB_ECM.GetDatatable(SQL)

            lstbxUserMandant.Items.Clear()
            For Each row As DataRow In dt.Rows
                lstbxUserMandant.Items.Add(row.Item(1).ToString)
            Next
        Catch ex As Exception
            MsgBox("Error in GetMandantForSelectedUser:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try


    End Sub

    Private Sub TBDD_USERBindingSource_PositionChanged(sender As Object, e As EventArgs) Handles TBDD_USERBindingSource.PositionChanged
        LoadGroupsForSelectedUser()
        GetMandantForSelectedUser()
    End Sub

    Private Sub btnRemoveUserFromGroup_Click(sender As Object, e As EventArgs) Handles btnRemoveUserFromGroup.Click
        Try
            Dim userID As Integer = USER_GUIDTextBox.Text
            Dim group As String = GetSelectedGroupObjectFromList()

            ' Gruppen ID über Name auslesen
            Dim SQL = String.Format("SELECT GUID FROM TBDD_USER_GROUPS WHERE NAME = '{0}'", group)
            Dim groupID As Integer = MYDB_ECM.GetScalarValue(SQL)

            ' Keine Gruppe ausgewählt
            If IsNothing(group) Then
                MsgBox("Bitte wählen Sie eine Gruppe aus.", MsgBoxStyle.Exclamation)
                Exit Sub
            End If

            SQL = String.Format("DELETE FROM TBDD_GROUPS_USER WHERE USER_ID = {0} AND GROUP_ID = {1}", userID, groupID)
            If MYDB_ECM.ExecuteNonQuery(SQL) = True Then
                LoadGroupsForSelectedUser()
            Else
                Throw New Exception(String.Format("Fehler beim Löschen der Gruppe {0} für Benutzer {1}", group, USERNAMETextBox.Text))
            End If
        Catch ex As Exception
            MsgBox("Error in btnRemoveUserFromGroup_Click:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Private Sub TBDD_USER_GROUPSBindingSource_AddingNew(sender As Object, e As System.ComponentModel.AddingNewEventArgs) Handles TBDD_USER_GROUPSBindingSource.AddingNew
        Me.DD_DMSDataSet.TBDD_USER_GROUPS.ADDED_WHOColumn.DefaultValue = USER_USERNAME
    End Sub

    Private Sub ToolStripButton7_Click(sender As Object, e As EventArgs) Handles ToolStripButton7.Click
        Save_Groups()
    End Sub

    Sub Save_Groups()
        Try
            Me.TBDD_USER_GROUPSBindingSource.EndEdit()
            If DD_DMSDataSet.TBDD_USER_GROUPS.GetChanges Is Nothing = False Then
                Me.CHANGED_WHOTextBox1.Text = USER_USERNAME
                Me.TBDD_USER_GROUPSBindingSource.EndEdit()
                Me.TBDD_USER_GROUPSTableAdapter.Update(Me.DD_DMSDataSet.TBDD_USER_GROUPS)
            End If
        Catch ex As Exception
            MsgBox("Error in Save Groups:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Private Sub ToolStripButton8_Click(sender As Object, e As EventArgs) Handles ToolStripButton8.Click
        Load_Groups()
    End Sub
    Sub Load_Groups()
        Try
            Me.TBDD_USER_GROUPSTableAdapter.Fill(Me.DD_DMSDataSet.TBDD_USER_GROUPS)
        Catch ex As Exception
            MsgBox("Error in Load_Groups:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Private Sub XtraTabControl1_SelectedPageChanged(sender As Object, e As DevExpress.XtraTab.TabPageChangedEventArgs) Handles XtraTabControl1.SelectedPageChanged
        Select Case XtraTabControl1.SelectedTabPageIndex
            Case 0
                Load_Groups()
                LoadGroupsForSelectedUser()
            Case 1
            Case 2
                Load_Client_relations()
            Case 3
                Try
                    DD_ECMAdmin.TBAD_Users.Clear()
                    Dim sql = String.Format("SELECT DISTINCT T.GUID, T.USERNAME, T.EMAIL FROM TBDD_USER T, TBDD_USER_MODULES T1 where T.GUID = T1.USER_ID AND T1.MODULE_ID = 1 ORDER BY T.USERNAME")
                    Dim DT As DataTable = MYDB_ECM.GetDatatable(sql)
                    For Each userrow As DataRow In DT.Rows
                        Dim newUserRow As DD_ECMAdmin.TBAD_UsersRow
                        newUserRow = DD_ECMAdmin.TBAD_Users.NewTBAD_UsersRow

                        newUserRow.Username = userrow.Item(1)
                        Try
                            newUserRow.Email = userrow.Item(2)
                        Catch ex As Exception
                            newUserRow.Email = ""
                        End Try

                        newUserRow.ID = userrow.Item(0)
                        DD_ECMAdmin.TBAD_Users.Rows.Add(newUserRow)
                        'clbUsersGroups.Items.Add(New MyListBoxItem() With {.Text = userrow.Item(1), .ExtraData = userrow.Item(0)})
                    Next
                Catch ex As Exception
                    MsgBox("Error in Load_Selectable_Users_for_Groups:" & vbNewLine & ex.Message & vbNewLine & "Check wether User is listed in User Client Relation!", MsgBoxStyle.Critical)
                End Try
        End Select

    End Sub

    Private Sub LANGUAGEComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LANGUAGEComboBox.SelectedIndexChanged

    End Sub

    Private Sub frmUserKonfig_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        GetMandantForSelectedUser()
    End Sub
    Public Class MyListBoxItem
        Private _text As String
        Private _extraData As String

        Public Property Text As String
            Get
                Return _text
            End Get
            Set(ByVal value As String)
                _text = value
            End Set
        End Property

        Public Property ExtraData As String
            Get
                Return _extraData
            End Get
            Set(ByVal value As String)
                _extraData = value
            End Set
        End Property

        Public Overrides Function ToString() As String
            Return Text
        End Function

    End Class
    Sub Load_Selectable_Users_for_Groups()
        Try
            DD_ECMAdmin.TBAD_Users.Clear()
            Dim sql = String.Format("SELECT DISTINCT T.GUID, T.USERNAME, T.EMAIL FROM TBDD_USER T where T.GUID in (SELECT DISTINCT USER_ID FROM TBDD_CLIENT_USER WHERE CLIENT_ID IN (SELECT CLIENT_ID FROM TBDD_CLIENT_USER WHERE USER_ID = {0})) ORDER BY T.USERNAME", USER_GUID)
            Dim DT As DataTable = MYDB_ECM.GetDatatable(sql)
            For Each userrow As DataRow In DT.Rows
                Dim newUserRow As DD_ECMAdmin.TBAD_UsersRow
                newUserRow = DD_ECMAdmin.TBAD_Users.NewTBAD_UsersRow

                newUserRow.Username = userrow.Item(1)
                Try
                    newUserRow.Email = userrow.Item(2)
                Catch ex As Exception
                    newUserRow.Email = ""
                End Try

                newUserRow.ID = userrow.Item(0)
                DD_ECMAdmin.TBAD_Users.Rows.Add(newUserRow)
                'clbUsersGroups.Items.Add(New MyListBoxItem() With {.Text = userrow.Item(1), .ExtraData = userrow.Item(0)})
            Next
        Catch ex As Exception
            MsgBox("Error in Load_Selectable_Users_for_Groups:" & vbNewLine & ex.Message & vbNewLine & "Check wether User is listed in User Client Relation!", MsgBoxStyle.Critical)
        End Try
    End Sub
    Private Sub TabControl1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged
        Select Case TabControl1.SelectedIndex
            Case 0
                Load_Groups()
                LoadGroupsForSelectedUser()
            Case 1
                Load_Groups()
                Load_Selectable_Users_for_Groups()

                Try
                    cmbGroups_Group2User.SelectedIndex = 1
                Catch ex As Exception

                End Try
                cmbGroups_Group2User.SelectedIndex = 0
        End Select
    End Sub

    Private Sub btnaddSelectedUser2Group_Click(sender As Object, e As EventArgs) Handles btnaddSelectedUser2Group.Click
        For Each row As DataRow In DD_ECMAdmin.TBAD_Users.Rows
            If row.Item(0) = CBool(True) Then
                Dim sql = "select count(*) from TBDD_GROUPS_USER Where user_id = " & row.Item(5) & " AND GROUP_ID = " & cmbGroups_Group2User.SelectedValue
                If MYDB_ECM.GetScalarValue(sql) = 0 Then
                    sql = String.Format("INSERT INTO TBDD_GROUPS_USER (USER_ID, GROUP_ID, ADDED_WHO) VALUES ({0}, {1}, '{2}')", row.Item(5), cmbGroups_Group2User.SelectedValue, USER_USERNAME)
                    If MYDB_ECM.ExecuteNonQuery(sql) = False Then
                        MsgBox(String.Format("Error in Adding User {0} to Group {1}", row.Item(1), cmbGroups_Group2User.Text), MsgBoxStyle.Critical)
                    End If
                End If
            End If
        Next
        For Each row As DataRow In DD_ECMAdmin.TBAD_Users.Rows
            row.Item(0) = CBool(False)
        Next
        'For Each obj As Object In clbUsersGroups.CheckedItems
        '    Dim item As MyListBoxItem = CType(obj, MyListBoxItem)


        'Next
        LoadUserForGroups()
    End Sub
    Private Sub LoadUserForGroups()
        Dim groupid
        Try
            groupid = cmbGroups_Group2User.SelectedValue
        Catch ex As Exception
            Exit Sub
        End Try
        If groupid Is Nothing Then Exit Sub
        If Not cmbGroups_Group2User.SelectedValue Is Nothing Then
            Dim SQL As String = String.Format("SELECT T1.GUID, T.USERNAME, T.EMAIL FROM TBDD_USER T, TBDD_GROUPS_USER T1 WHERE T1.USER_ID = T.GUID AND T1.GROUP_ID = {0} ORDER BY T.USERNAME", cmbGroups_Group2User.SelectedValue)
            lblUserforGroup.Text = "Users for Group " & cmbGroups_Group2User.Text
            Try
                Dim dt As DataTable = MYDB_ECM.GetDatatable(SQL)
                DD_ECMAdmin.TBWH_Users1.Clear()

                For Each row As DataRow In dt.Rows
                    Dim newUserRow As DD_ECMAdmin.TBWH_Users1Row
                    newUserRow = DD_ECMAdmin.TBWH_Users1.NewTBWH_Users1Row

                    newUserRow.Username = row.Item(1)
                    Try
                        newUserRow.Email = row.Item(2)
                    Catch ex As Exception
                        newUserRow.Email = ""
                    End Try

                    newUserRow.ID = row.Item(0)
                    DD_ECMAdmin.TBWH_Users1.Rows.Add(newUserRow)
                    ' chklbxUserForGroup.Items.Add(New MyListBoxItem() With {.Text = row.Item(1), .ExtraData = row.Item(0)})
                Next
            Catch ex As Exception
                MsgBox("Error in LoadUserForGroups:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
            End Try
        End If

    End Sub

    Private Sub cmbGroups_Group2User_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbGroups_Group2User.SelectedIndexChanged
        LoadUserForGroups()
    End Sub

    Private Sub btndeleteUserfromGroup_Click(sender As Object, e As EventArgs) Handles btndeleteUserfromGroup.Click
        For Each row As DataRow In DD_ECMAdmin.TBWH_Users1.Rows
            If row.Item(0) = CBool(True) Then
                Dim sql
                sql = String.Format("DELETE FROM TBDD_GROUPS_USER  WHERE GUID = {0}", row.Item(5))
                If MYDB_ECM.ExecuteNonQuery(sql) = False Then
                    MsgBox(String.Format("Error in deleting User {0} from Group", row.Item(1)), MsgBoxStyle.Critical)
                End If
            End If
        Next

        'For Each obj As Object In chklbxUserForGroup.CheckedItems
        '    Dim item As MyListBoxItem = CType(obj, MyListBoxItem)
        '    Dim sql
        '    sql = String.Format("DELETE FROM TBDD_GROUPS_USER  WHERE GUID = {0}", item.ExtraData)

        'Next
        LoadUserForGroups()
        Load_Selectable_Users_for_Groups()
    End Sub

    Private Sub ToolStripButton16_Click(sender As Object, e As EventArgs) Handles ToolStripButton24.Click
        Load_Clients()
    End Sub

    Private Sub ToolStripButton15_Click(sender As Object, e As EventArgs) Handles ToolStripButton23.Click
        Save_Clients()
    End Sub
    Private Sub TBDD_CLIENTBindingSource_AddingNew(sender As Object, e As System.ComponentModel.AddingNewEventArgs) Handles TBDD_CLIENTBindingSource.AddingNew
        DD_DMSDataSet.TBDD_CLIENT.ADDED_WHOColumn.DefaultValue = USER_USERNAME
    End Sub

    Private Sub TabControl3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControl3.SelectedIndexChanged
        Select Case TabControl3.SelectedIndex
            Case 0
                Load_Client_relations()
        End Select
    End Sub
    Sub Load_Client_relations()
        Load_Users_not_Related_2_Client()
        Try
            cmbClientsforUser.SelectedIndex = 1
        Catch ex As Exception
        End Try
        cmbClientsforUser.SelectedIndex = 0
    End Sub
    Sub Load_Users_not_Related_2_Client()
        Try
            DD_ECMAdmin.TBWH_Users2.Clear()
            Dim sql = String.Format("SELECT T.GUID, T.USERNAME,T.EMAIL FROM TBDD_USER T WHERE T.GUID NOT IN (SELECT USER_ID FROM TBDD_CLIENT_USER WHERE CLIENT_ID = {0}) ORDER BY T.USERNAME", cmbClientsforUser.SelectedValue)
            Dim DT As DataTable = MYDB_ECM.GetDatatable(sql)
            For Each userrow As DataRow In DT.Rows
                Dim newUserRow As DD_ECMAdmin.TBWH_Users2Row
                newUserRow = DD_ECMAdmin.TBWH_Users2.NewTBWH_Users2Row

                newUserRow.Username = userrow.Item(1)
                Try
                    newUserRow.Email = userrow.Item(2)
                Catch ex As Exception
                    newUserRow.Email = ""
                End Try

                newUserRow.ID = userrow.Item(0)
                DD_ECMAdmin.TBWH_Users2.Rows.Add(newUserRow)
                ' chklbxUserClient.Items.Add(New MyListBoxItem() With {.Text = userrow.Item(1), .ExtraData = userrow.Item(0)})
            Next
            Label16.Text = "Users not linked to client (" & DT.Rows.Count & ")"
        Catch ex As Exception
            MsgBox("Error in Load Users not related 2 Client:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Private Sub btnsaddUser2Client_Click(sender As Object, e As EventArgs) Handles btnsaddUser2Client.Click
        For Each row As DataRow In DD_ECMAdmin.TBWH_Users2.Rows
            If row.Item(0) = CBool(True) Then
                Dim sql = "select count(*) from TBDD_CLIENT_USER Where user_id = " & row.Item(5) & " AND CLIENT_ID = " & cmbClientsforUser.SelectedValue
                If MYDB_ECM.GetScalarValue(sql) = 0 Then
                    sql = String.Format("INSERT INTO TBDD_CLIENT_USER (USER_ID, CLIENT_ID, ADDED_WHO) VALUES ({0}, {1}, '{2}')", row.Item(5), cmbClientsforUser.SelectedValue, USER_USERNAME)
                    If MYDB_ECM.ExecuteNonQuery(sql) = False Then
                        MsgBox(String.Format("Error in Adding User {0} to Client {1}", row.Item(1), cmbGroups_Group2User.Text), MsgBoxStyle.Critical)
                    End If
                End If
            End If
        Next
        For Each row As DataRow In DD_ECMAdmin.TBWH_Users2.Rows
            row.Item(0) = CBool(False)
        Next
        LoadUserForClient()
    End Sub
    Private Sub LoadUserForClient()
        Dim clientid
        Try
            clientid = cmbClientsforUser.SelectedValue
        Catch ex As Exception
            Exit Sub
        End Try
        If clientid Is Nothing Then Exit Sub

        Load_Users_not_Related_2_Client()


        Dim SQL As String = String.Format("SELECT T1.GUID, T.USERNAME, T.EMAIL FROM TBDD_USER T, TBDD_CLIENT_USER T1 WHERE T1.USER_ID = T.GUID AND T1.CLIENT_ID = {0} ORDER BY T.USERNAME", cmbClientsforUser.SelectedValue)
        Try
            Dim dt As DataTable = MYDB_ECM.GetDatatable(SQL)
            DD_ECMAdmin.TBWH_Users1.Clear()
            For Each row As DataRow In dt.Rows
                Dim newUserRow As DD_ECMAdmin.TBWH_Users1Row
                newUserRow = DD_ECMAdmin.TBWH_Users1.NewTBWH_Users1Row
                newUserRow.Username = row.Item(1)
                Try
                    newUserRow.Email = row.Item(2)
                Catch ex As Exception
                    newUserRow.Email = ""
                End Try

                newUserRow.ID = row.Item(0)
                DD_ECMAdmin.TBWH_Users1.Rows.Add(newUserRow)
                ' chklbxUsersforClient.Items.Add(New MyListBoxItem() With {.Text = row.Item(1), .ExtraData = row.Item(0)})
            Next
            lblUser2Client.Text = "Users linked to Client '" & cmbClientsforUser.Text & "' (" & dt.Rows.Count & ")"
        Catch ex As Exception
            MsgBox("Error in LoadUserForClient:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try

    End Sub

    Private Sub cmbClientsforUser_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbClientsforUser.SelectedIndexChanged
        LoadUserForClient()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btndeleteUserfromClient.Click
        For Each row As DataRow In DD_ECMAdmin.TBWH_Users1.Rows
            If row.Item(0) = CBool(True) Then
                Dim sql
                sql = String.Format("DELETE FROM TBDD_CLIENT_USER  WHERE GUID = {0}", row.Item(5))
                If MYDB_ECM.ExecuteNonQuery(sql) = False Then
                    MsgBox(String.Format("Error in deleting User {0} from Client", row.Item(1)), MsgBoxStyle.Critical)
                End If
            End If
        Next
        'For Each obj As Object In chklbxUsersforClient.CheckedItems
        '    Dim item As MyListBoxItem = CType(obj, MyListBoxItem)

        'Next
        LoadUserForClient()
    End Sub
    'Private Sub txtUserSearch2_TextChanged(sender As Object, e As EventArgs)
    '    Dim i As Integer = chklbxUserForGroup.FindString(txtUserSearch2.Text)
    '    chklbxUserForGroup.SelectedIndex = i
    '    If txtUserSearch2.Text = "" Then
    '        chklbxUserForGroup.SelectedIndex = -1
    '    End If
    'End Sub

    'Private Sub txtUserSearch3_TextChanged(sender As Object, e As EventArgs)
    '    Dim i As Integer = clbUsersGroups.FindString(txtUserSearch3.Text)
    '    clbUsersGroups.SelectedIndex = i
    '    If txtUserSearch3.Text = "" Then
    '        clbUsersGroups.SelectedIndex = -1
    '    End If
    'End Sub

    'Private Sub txtUserSearch5_TextChanged(sender As Object, e As EventArgs)
    '    Dim i As Integer = chklbxUsersforClient.FindString(txtUserSearch5.Text)
    '    chklbxUsersforClient.SelectedIndex = i
    '    'If txtUserSearch2.Text = "" Then
    '    '    chklbxUsersforClient.SelectedIndex = -1
    '    'End If
    'End Sub

    Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
        Delete_user()


    End Sub
    Sub Delete_user()
        Dim msg = "Sind Sie sicher, dass Sie diesen User löschen wollen?"
        If USER_LANGUAGE <> "de-DE" Then
            msg = "Are You sure You want to delete the selected user?"
        End If
        Dim result As MsgBoxResult
        result = MessageBox.Show(msg, CAPTION_CONFIRMATION, MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If result = MsgBoxResult.Yes Then
            Dim del = "EXEC [dbo].[PRPMO_DELETE_USER] " & USER_GUIDTextBox.Text
            If MYDB_ECM.ExecuteNonQuery(del) = True Then
                Load_User()
            Else
                MsgBox("Unexpected Error while deleting User.", MsgBoxStyle.Exclamation)
            End If
        End If
    End Sub
    Private Sub btnopenfolder_Click(sender As Object, e As EventArgs) Handles btnopenfolder.Click
        Dim fbdia As New FolderBrowserDialog
        If fbdia.ShowDialog() = DialogResult.OK Then
            txtPath.Text = fbdia.SelectedPath
        End If
    End Sub

    Private Sub btnUpdatePath_Click(sender As Object, e As EventArgs) Handles btnUpdatePath.Click
        Dim msg As String = ""
        For Each row As DataRow In DD_ECMAdmin.TBAD_Users.Rows
            If row.Item(0) = CBool(True) Then

                Dim sql = "select count(*) from TBDD_VERSION_USER_UPDATE_PATH Where USER_ID = " & row.Item(5) & " AND UPPER(MODULE_NAME) = 'RECORD-ORGANIZER'"
                If MYDB_ECM.GetScalarValue(sql) = 0 Then
                    sql = String.Format("INSERT INTO TBDD_VERSION_USER_UPDATE_PATH (USER_ID,MODULE_NAME,UPDATE_PATH) VALUES ( ({0}, {1}, '{2}'))", row.Item(5), "RECORD-ORGANIZER", txtPath.Text)
                Else
                    sql = String.Format("UPDATE TBDD_VERSION_USER_UPDATE_PATH  SET UPDATE_PATH = '{0}' WHERE USER_ID = {1} AND UPPER(MODULE_NAME) = 'RECORD-ORGANIZER'", txtPath.Text, row.Item(5))
                End If
                If MYDB_ECM.ExecuteNonQuery(sql) = False Then
                    msg &= vbNewLine & String.Format("for User '{0}'", row.Item(1))
                End If
            End If
        Next
        If msg <> String.Empty Then
            msg = "Error in Adding UpdatePath" & msg
            MsgBox(msg, MsgBoxStyle.Critical)
        Else
            For Each row As DataRow In DD_ECMAdmin.TBWH_Users2.Rows
                row.Item(0) = CBool(False)
            Next
        End If

    End Sub

    Private Sub SelectAllToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SelectAllToolStripMenuItem.Click
        For Each row As DataRow In DD_ECMAdmin.TBAD_Users.Rows
            row.Item(0) = CBool(True)
        Next
    End Sub

    Private Sub AuswahlAufhebenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AuswahlAufhebenToolStripMenuItem.Click
        For Each row As DataRow In DD_ECMAdmin.TBAD_Users.Rows
            row.Item(0) = CBool(False)
        Next
    End Sub

    Private Sub tsbtnExoirtExcel_Click(sender As Object, e As EventArgs) Handles tsbtnExoirtExcel.Click
        Dim saveFileDialog1 As New SaveFileDialog
        saveFileDialog1.Filter = "Excel File|*.xlsx"
        saveFileDialog1.Title = "Export to Excel:"
        saveFileDialog1.ShowDialog()
        If saveFileDialog1.FileName <> "" Then
            Cursor = Cursors.WaitCursor
            GridControlUser.MainView.ExportToXlsx(saveFileDialog1.FileName)
            Dim result As MsgBoxResult
            Dim msg = String.Format("Datei wurde erstellt! Wollen Sie diese nun öffnen?")

            If USER_LANGUAGE <> "de-DE" Then
                msg = String.Format("File was created. Do You want to open excel?")
            End If
            result = MessageBox.Show(msg, "Exporting result:", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
            If result = MsgBoxResult.Yes Then
                Process.Start(saveFileDialog1.FileName)
            End If
        End If
        Cursor = Cursors.Default
    End Sub
End Class