Imports System.IO.File
Imports System.Data.SqlClient
Public Class AdminUsersForm
    Private Sub AdminUsersForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim query As String = "SELECT * FROM UserTable"
        Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(query, con)
                Using sda As New SqlDataAdapter()
                    sda.SelectCommand = cmd
                    Using dt As New DataTable()
                        con.Open()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt
                        DataGridView1.GridColor = Color.Red
                        DataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.None
                        DataGridView1.BackgroundColor = Color.LightGray

                        DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Red
                        DataGridView1.DefaultCellStyle.SelectionForeColor = Color.White

                        DataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]

                        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                        DataGridView1.AllowUserToResizeColumns = False
                        DataGridView1.AllowUserToResizeRows = False

                        DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
                        DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
                        DataGridView1.Columns(0).Visible = True
                        DataGridView1.Columns(1).Visible = True
                        DataGridView1.Columns(2).Visible = True
                        DataGridView1.Columns(3).Visible = True
                        DataGridView1.Columns(4).Visible = True
                        DataGridView1.Columns(5).Visible = False
                        DataGridView1.Columns(6).Visible = False
                        DataGridView1.Columns(7).Visible = False
                        DataGridView1.Columns(8).Visible = False

                    End Using
                End Using
            End Using
        End Using
       
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim USERNAME As String = TextBox1.Text
        Dim PASSWORD As String = TextBox2.Text
        Dim USERTYPE As String = ComboBox1.SelectedItem
        Dim USTATUS As String = "DeActive"

        If TextBox1.Text = "" Or TextBox2.Text = "" Or ComboBox1.Text = "" Then
            MessageBox.Show("Please enter the missing field!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Try
                Dim query As String = "SELECT * FROM UserTable WHERE [USERNAME]=@USERNAME OR [PASSWORD]=@PASSWORD"
                Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
                    Using cmd As SqlCommand = New SqlCommand(query, con)
                        cmd.Parameters.AddWithValue("@USERNAME", USERNAME)
                        cmd.Parameters.AddWithValue("@PASSWORD", PASSWORD)
                        cmd.Parameters.AddWithValue("@USERTYPE", USERTYPE)
                        Using da As New SqlDataAdapter(cmd)
                            Using dt As New DataTable()
                                da.Fill(dt)
                                If dt.Rows.Count > 0 Then
                                    MessageBox.Show("Username or password is already in Used. Please try again!", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Hand)

                                Else
                                    Dim addquery As String = "INSERT INTO UserTable([USERNAME],[PASSWORD],[USERTYPE],[USTATUS])VALUES(@USERNAME,@PASSWORD,@USERTYPE,@USTATUS)"
                                    Dim com = New SqlCommand(addquery, con)

                                    com.Parameters.AddWithValue("@USERNAME", USERNAME)
                                    com.Parameters.AddWithValue("@PASSWORD", PASSWORD)
                                    com.Parameters.AddWithValue("@USERTYPE", USERTYPE)
                                    com.Parameters.AddWithValue("@USTATUS", USTATUS)
                                    Dim x As Integer = 0
                                    Try
                                        con.Open()
                                        x = com.ExecuteNonQuery()
                                    Catch ex As Exception
                                        MessageBox.Show(ex.Message)
                                    Finally
                                        con.Close()
                                        cmd.Parameters.Clear()
                                    End Try
                                    Select Case x
                                        Case 1
                                            MessageBox.Show("Successfully register!", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Information)
                                            BindData()
                                        Case 0
                                            MessageBox.Show("Opps, wait!! wrong entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                                    End Select
                                End If
                            End Using
                        End Using
                    End Using
                End Using

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
    End Sub
    Public Sub BindData()
        Dim query As String = "SELECT * FROM UserTable"
        Using conn As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using resultcmd As SqlCommand = New SqlCommand(query, conn)
                Using sda As SqlDataAdapter = New SqlDataAdapter()
                    sda.SelectCommand = resultcmd
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        DataGridView1.DataSource = dt

                    End Using
                End Using
            End Using
        End Using
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim query As String = "SELECT * FROM UserTable WHERE USERNAME ='" & TextBox3.Text & "'"
        Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
            Using cmd As SqlCommand = New SqlCommand(query, con)
                Using da As New SqlDataAdapter()
                    da.SelectCommand = cmd
                    Using dt As New DataTable()
                        da.Fill(dt)
                        If dt.Rows.Count > 0 Then
                            TextBox1.Text = dt.Rows(0)(0).ToString()
                            TextBox2.Text = dt.Rows(0)(1).ToString()
                            ComboBox1.Text = dt.Rows(0)(2).ToString()
                        End If
                    End Using
                End Using
            End Using
        End Using
        
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If TextBox3.Text = "" Then
            MessageBox.Show("Please select Username to delete!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Try
                Dim USERNAME As String = TextBox3.Text
                Dim query As String = "DELETE FROM UserTable WHERE USERNAME=@USERNAME"
                Using con As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
                    Using cmd As SqlCommand = New SqlCommand(query, con)
                        cmd.Parameters.AddWithValue("@USERNAME", USERNAME)
                        con.Open()
                        cmd.ExecuteNonQuery()
                        MessageBox.Show("Are you sure you want to delete this User?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        TextBox1.Text = ""
                        TextBox2.Text = ""
                        TextBox3.Text = ""

                        con.Close()
                        BindData()
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim USERNAME As String = TextBox1.Text
        Dim PASSWORD As String = TextBox2.Text
        If TextBox1.Text = Nothing Or TextBox2.Text = Nothing Then
            MessageBox.Show("Please enter the required fields!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try
                Dim updatequery As String = "UPDATE UserTable SET PASSWORD=@PASSWORD WHERE USERNAME=@USERNAME"
                Using updatecon As SqlConnection = New SqlConnection("Data Source=STALKER01\SQLEXPRESSSS;Initial Catalog=attendanceDB;Integrated Security=True;Pooling=False")
                    Using cmdupdate As New SqlCommand(updatequery, updatecon)
                        cmdupdate.Parameters.AddWithValue("@USERNAME", USERNAME)
                        cmdupdate.Parameters.AddWithValue("@PASSWORD", PASSWORD)
                        updatecon.Open()
                        cmdupdate.ExecuteNonQuery()
                        MessageBox.Show("successfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        updatecon.Close()
                        BindData()
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show("OPPS, WE HAVE ERROR FOUND", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub
End Class