Imports System.Data.SqlClient
Public Class AddItem
    Private Function EditValidate() As Boolean
        If Me.lvRec.Items.Count = 0 Then
            MsgBox("No record to Edit...", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Attention...")
            Return True
            Exit Function
        End If
        If Me.lvRec.SelectedItems.Count > 1 Then
            MsgBox("Please select only one record to Edit or double click the record...", MsgBoxStyle.Information, "Attendance...")
            Return True
            Exit Function
        End If
        If Me.lvRec.SelectedItems.Count = 0 Then
            MsgBox("Please select a record to Edit or double click the record...", MsgBoxStyle.Information, "Attendance...")
            Return True
            Exit Function
        End If
        Return 0
    End Function
    Public Sub Userlog()
        Dim dd As String
        dd = Now.Date
        Try
            Call opencon()
            Dim myd As New SqlDataAdapter
            With myd
                .InsertCommand = New SqlCommand
                .InsertCommand.CommandText = "INSERT INTO loy(datte,uname,action)VALUES(@datte,@uname,@action) "
                .InsertCommand.Connection = con
                With .InsertCommand.Parameters
                    .AddWithValue("@datte", dd)
                    .AddWithValue("@uname", logger)
                    .AddWithValue("@action", action)
                End With
                .InsertCommand.ExecuteNonQuery()
            End With
            Call closecon()
        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try
    End Sub
    Private Sub btn_Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        

    End Sub
    Public Sub clear()
        Me.txtDesc.Clear()
        txtPrice.ResetText()
        txtqty.ResetText()
        txtSup.ResetText()
        Me.cboCat.Clear()
        Me.dtPur.ResetText()
        Me.dtRev.ResetText()
        Me.TextBox1.Clear()
    End Sub

    Private Sub txtQty_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        Dim NotAllowed As String = "0123456789"
        If e.KeyChar <> ControlChars.Back Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 Then
                e.Handled = True
                MsgBox("Letters not allowed...", MsgBoxStyle.Information, "Attention....")
            End If
        End If
    End Sub

    Private Sub txtPrice_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        Dim NotAllowed As String = "0123456789"
        If e.KeyChar <> ControlChars.Back Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 Then
                e.Handled = True
                MsgBox("Characters not allowed...", MsgBoxStyle.Information, "Attention....")
            End If
        End If
    End Sub
   
    Private Sub txtSup_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        Dim NotAllowed As String = "~!`@#$%^&*()_-+={}][|\;:'/?.>,<0123456789"
        If e.KeyChar <> ControlChars.Back Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
                MsgBox("Special characters & Numbers not allowed...", MsgBoxStyle.Information, "Attention....")
            End If
        End If
    End Sub

    Private Sub txtPrice_Leave(ByVal sender As Object, ByVal e As System.EventArgs)
        If Me.txtPrice.Text.Length = 0 Then
            MsgBox("Do not leave this field unfilled", MsgBoxStyle.Information, "Attention....")
            txtPrice.Focus()
        Else
            txtPrice.Text = FormatNumber(txtPrice.Text)
        End If
    End Sub
    Private Sub fill()
        With lvRec
            .Columns.Add("ID", 30, HorizontalAlignment.Left)
            .Columns.Add("Guest Name", 110, HorizontalAlignment.Left)
            .Columns.Add("Phone No", 110, HorizontalAlignment.Left)
            .Columns.Add("Sex", 110, HorizontalAlignment.Left)
            .Columns.Add("State of Origin", 110, HorizontalAlignment.Left)
            .Columns.Add("Nationality", 110, HorizontalAlignment.Left)
            .Columns.Add("Occupation", 110, HorizontalAlignment.Left)
            .Columns.Add("Entry Date", 110, HorizontalAlignment.Left)
            .Columns.Add("Address", 110, HorizontalAlignment.Left)
            .Activation = ItemActivation.OneClick
            .FullRowSelect = True
            .GridLines = True
            .HideSelection = False
            .HoverSelection = False
            .MultiSelect = True
            .View = View.Details
            .HotTracking = False
            .Font = New Font(lvRec.Font.FontFamily, 11, FontStyle.Bold, GraphicsUnit.Pixel)
            .BackColor = Color.White
        End With

    End Sub
    'Public Sub ConSelect()
    '    Me.lvRec.Items.Clear()
    '    Try
    '        Dim myd As New SqlDataAdapter
    '        With myd
    '            .SelectCommand = New SqlCommand
    '            .SelectCommand.CommandText = "SELECT * FROM stocky WHERE datep LIKE '%%" & Me.cbocatt.Text & "%%'"
    '            .SelectCommand.Connection = con
    '        End With
    '        Dim dr As SqlDataReader
    '        dr = myd.SelectCommand.ExecuteReader
    '        Do While dr.Read
    '            Dim item As New ListViewItem
    '            item.Text = IIf(dr.IsDBNull(0), " ", dr.Item(0)).ToString
    '            Dim c As Integer
    '            For c = 1 To dr.FieldCount - 1
    '                If Not dr.IsDBNull(c) Then
    '                    item.SubItems.Add(dr.Item(c)).ToString()
    '                Else
    '                    item.SubItems.Add(" ")
    '                End If
    '            Next
    '            Me.lvRec.Items.Add(item)
    '        Loop

    '    Catch ex As Exception
    '        MsgBox(ex.Message)

    '    End Try
    'End Sub
    Private Sub filllv()
        Me.lvRec.Items.Clear()
        Try
            Dim myd As New SqlDataAdapter
            With myd
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM stocky"
                .SelectCommand.Connection = con
            End With
            Dim dr As SqlDataReader
            dr = myd.SelectCommand.ExecuteReader
            Do While dr.Read
                Dim item As New ListViewItem
                item.Text = IIf(dr.IsDBNull(0), " ", dr.Item(0)).ToString
                Dim c As Integer
                For c = 1 To dr.FieldCount - 1
                    If Not dr.IsDBNull(c) Then
                        item.SubItems.Add(dr.Item(c)).ToString()
                    Else
                        item.SubItems.Add(" ")
                    End If
                Next
                Me.lvRec.Items.Add(item)
            Loop

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Sub
    Private Sub AddItem_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call opencon()
        Call fill()
        Call filllv()
        Call closecon()

    End Sub

    Private Sub btn_view_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
       
    End Sub

    Private Sub lvRec_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs)
        btn_view.PerformClick()
    End Sub
   
    Private Sub btn_Refresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
       
    End Sub

    Private Sub btn_close_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub btn_Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
       
    End Sub
    Private Function DelValidate() As Boolean
        If Me.lvRec.Items.Count = 0 Then
            MsgBox("No record to delete...", MsgBoxStyle.Exclamation, "No records...")
            Return True
            Exit Function
        End If
        If Me.lvRec.SelectedItems.Count = 0 Then
            MsgBox("Please select a record to delete, or use Ctrl + Click to delete more records", MsgBoxStyle.Exclamation, "Select record(s)")
            Return True
            Exit Function
        End If
        Return 0
    End Function

    Private Sub btn_Edit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        

    End Sub

    

    Private Sub txtPrice_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub cboCat_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        Dim NotAllowed As String = "~!`@#$%^&*()_-+={}][|\;:'/?.>,<0123456789"
        If e.KeyChar <> ControlChars.Back Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
                MsgBox("Special characters & Numbers not allowed...", MsgBoxStyle.Information, "Attention....")
            End If
        End If
    End Sub

    Private Sub cboCat_Leave(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.cboCat.Text = StrConv(Me.cboCat.Text, vbProperCase)
    End Sub

    Private Sub cboCat_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub txtDesc_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        Dim NotAllowed As String = "~!`@#$%^&*()_-+={}][|\;:'/?.>,<0123456789"
        If e.KeyChar <> ControlChars.Back Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
                MsgBox("Special characters & Numbers not allowed...", MsgBoxStyle.Information, "Attention....")
            End If
        End If
    End Sub

    Private Sub txtDesc_Leave(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.txtDesc.Text = StrConv(Me.txtDesc.Text, vbProperCase)
    End Sub

    Private Sub txtDesc_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub txtSup_Leave(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.txtSup.Text = StrConv(Me.txtSup.Text, vbProperCase)
    End Sub

    Private Sub txtSup_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub Label8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label8.Click
        Me.Close()
    End Sub

    Private Sub btn_Add_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Add.Click
        If Me.cboCat.Text.Length = 0 Then
            MsgBox("Please enter Guest name....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtDesc.Text.Length = 0 Then
            MsgBox("Please enter phone number....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtqty.Text.Length = 0 Then
            MsgBox("Please select gender....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtPrice.Text.Length = 0 Then
            MsgBox("Please select state of origin....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtSup.Text.Length = 0 Then
            MsgBox("Please enter nationality....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.dtPur.Text.Length = 0 Then
            MsgBox("Please select/enter occupation....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.dtRev.Text.Length = 0 Then
            MsgBox("Please enter entry date....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.TextBox1.Text.Length = 0 Then
            MsgBox("Please enter address....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        Else
            Call opencon()
            Dim myda As New SqlDataAdapter
            With myda
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM stocky WHERE category LIKE '%%" & Me.cboCat.Text & "%%'"
                .SelectCommand.Connection = con
            End With
            Dim dr As New DataSet
            myda.Fill(dr, "myTable")
            If dr.Tables("myTable").Rows.Count > 0 Then
                MsgBox("Guest Name already exist...Double Click it and update it if needed", MsgBoxStyle.Exclamation, "Alert...")
                Exit Sub
            Else
                Try
                    Call opencon()
                    Dim myd As New SqlDataAdapter
                    With myd
                        .InsertCommand = New SqlCommand
                        .InsertCommand.CommandText = "INSERT INTO stocky(category,descrip,qty,price,supname,datep,endate,dater)VALUES(@category,@descrip,@qty,@price,@supname,@datep,@endate,@dater) "
                        .InsertCommand.Connection = con
                        With .InsertCommand.Parameters
                            .AddWithValue("@category", Me.cboCat.Text)
                            .AddWithValue("@descrip", Me.txtDesc.Text)
                            .AddWithValue("@qty", Me.txtqty.Text)
                            .AddWithValue("@price", Me.txtPrice.Text)
                            .AddWithValue("@supname", Me.txtSup.Text)
                            .AddWithValue("@datep", Me.dtPur.Text)
                            .AddWithValue("@endate", Me.dtRev.Text)
                            .AddWithValue("@dater", Me.TextBox1.Text)
                        End With
                        .InsertCommand.ExecuteNonQuery()
                    End With
                    Call filllv()
                    Call closecon()
                    
                    MsgBox("Record successfully saved...", MsgBoxStyle.Information, "Saved....")
                    Call clear()
                Catch ex As Exception
                    MsgBox(ex.Message)
                    Call closecon()
                End Try
            End If
        End If

    End Sub

    Private Sub dtRev_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtRev.ValueChanged
       
    End Sub

    Private Sub Panel5_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel5.Paint

    End Sub

    Private Sub cboCat_KeyPress1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cboCat.KeyPress
        Dim NotAllowed As String = "~!`@#$%^&*()_-+={}][|\;:'/?.>,<0123456789"
        If e.KeyChar <> ControlChars.Back Then
            If NotAllowed.IndexOf(e.KeyChar) = -1 = False Then
                e.Handled = True
                MsgBox("Special characters & Numbers not allowed...", MsgBoxStyle.Information, "Attention....")
            End If
        End If
    End Sub

    Private Sub cboCat_Leave1(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCat.Leave
        Me.cboCat.Text = StrConv(Me.cboCat.Text, vbProperCase)
    End Sub

    Private Sub cboCat_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCat.TextChanged

    End Sub

    Private Sub txtDesc_KeyPress1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
       
    End Sub

    Private Sub txtDesc_Leave1(ByVal sender As Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub txtDesc_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub btn_Edit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Edit.Click
        If Me.cboCat.Text.Length = 0 Then
            MsgBox("Please enter Guest name....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtDesc.Text.Length = 0 Then
            MsgBox("Please enter phone number....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtqty.Text.Length = 0 Then
            MsgBox("Please select gender....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtPrice.Text.Length = 0 Then
            MsgBox("Please select state of origin....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtSup.Text.Length = 0 Then
            MsgBox("Please enter nationality....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.dtPur.Text.Length = 0 Then
            MsgBox("Please select/enter occupation....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.dtRev.Text.Length = 0 Then
            MsgBox("Please enter entry date....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.TextBox1.Text.Length = 0 Then
            MsgBox("Please enter address....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        Else
            Try
                Call opencon()
                Dim myd2 As New SqlDataAdapter
                With myd2
                    .UpdateCommand = New SqlCommand
                    .UpdateCommand.CommandText = "UPDATE stocky SET [category] = @category,[descrip] = @descrip,[qty] = @qty,[price] = @price,[supname] = @supname,[datep] = @datep,[endate] = @endate,[dater] = @dater WHERE id LIKE '%%" & Me.lvRec.SelectedItems(0).Text & "%%'"
                    .UpdateCommand.Connection = con
                    With .UpdateCommand.Parameters
                        .AddWithValue("@category", Me.cboCat.Text)
                        .AddWithValue("@descrip", Me.txtDesc.Text)
                        .AddWithValue("@qty", Me.txtqty.Text)
                        .AddWithValue("@price", Me.txtPrice.Text)
                        .AddWithValue("@supname", Me.txtSup.Text)
                        .AddWithValue("@datep", Me.dtPur.Text)
                        .AddWithValue("@endate", Me.dtRev.Text)
                        .AddWithValue("@dater", Me.TextBox1.Text)
                    End With
                    .UpdateCommand.ExecuteNonQuery()
                End With
                Call filllv()
                Call closecon()

                MsgBox("Record successfully updated...", MsgBoxStyle.Information, "Submitted...")

                Call clear()

            Catch ex As Exception
                MsgBox("Ensure that you double click below the record you want to update first" + "-" + ex.Message)
                Call closecon()
            End Try
        End If
    End Sub

    Private Sub btn_Delete_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Delete.Click
        If DelValidate() = True Then
            Return
            Exit Sub
        Else
            If MsgBox("Are you sure you want delete the selected record(s)?", MsgBoxStyle.Question + MsgBoxStyle.DefaultButton2 + MsgBoxStyle.YesNo, "Confirmation...") = MsgBoxResult.Yes Then


                Try

                    Dim list As New ListViewItem
                    For Each list In Me.lvRec.SelectedItems
                        list.Remove()
                        Call opencon()
                        Dim myd As New SqlDataAdapter
                        With myd
                            .DeleteCommand = New SqlCommand
                            .DeleteCommand.CommandText = "DELETE FROM stocky WHERE id LIKE '%%" & list.Text & "%%'"
                            .DeleteCommand.Connection = con
                            .DeleteCommand.ExecuteNonQuery()
                        End With
                    Next
                    Call filllv()
                    Call closecon()
                   
                    MsgBox("Record(s) successfully deleted....", MsgBoxStyle.Information, "Deleted records...")
                Catch ex As Exception
                    MsgBox(ex.Message)
                    Call closecon()
                End Try
            Else
                MsgBox("Deletion cancelled....", MsgBoxStyle.Information, "Cancelled...")
                Call opencon()
                Call filllv()
                Call closecon()

            End If
        End If
    End Sub

    Private Sub btn_view_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_view.Click
        If EditValidate() = True Then
            Return
            Exit Sub
        Else
            Call opencon()
            Dim myd As New SqlDataAdapter
            With myd
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM stocky WHERE id LIKE '%%" & Me.lvRec.SelectedItems(0).Text & "%%'"
                .SelectCommand.Connection = con
            End With
            Dim dr As SqlDataReader
            dr = myd.SelectCommand.ExecuteReader
            With dr
                If Not .HasRows Then
                    'Nothing
                Else
                    .Read()
                    Me.cboCat.Text = IIf(.IsDBNull(1), " ", .Item("category")).ToString
                    Me.txtDesc.Text = IIf(.IsDBNull(2), " ", .Item("descrip")).ToString
                    Me.txtqty.Text = IIf(.IsDBNull(3), " ", .Item("qty")).ToString
                    Me.txtPrice.Text = IIf(.IsDBNull(4), " ", .Item("price")).ToString
                    Me.txtSup.Text = IIf(.IsDBNull(5), " ", .Item("supname")).ToString
                    Me.dtPur.Text = IIf(.IsDBNull(5), " ", .Item("datep")).ToString
                    Me.dtRev.Text = IIf(.IsDBNull(5), " ", .Item("endate")).ToString
                    Me.TextBox1.Text = IIf(.IsDBNull(6), " ", .Item("dater")).ToString
                End If
            End With
            Call closecon()
        End If
    End Sub

    Private Sub lvRec_DoubleClick1(ByVal sender As Object, ByVal e As System.EventArgs) Handles lvRec.DoubleClick
        btn_view.PerformClick()
    End Sub

    Private Sub lvRec_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvRec.SelectedIndexChanged

    End Sub

    Private Sub btn_Refresh_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Refresh.Click
        Call clear()
        Call opencon()
        Call filllv()
        Call closecon()
    End Sub

   

    Private Sub Panel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel1.Paint

    End Sub

    Private Sub TextBox1_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.Leave
        Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged

    End Sub
End Class