Imports System.Data.SqlClient
Public Class frmSupp
    'Public Sub fillcombo()
    '    Try
    '        Call opencon()
    '        Dim da As New SqlDataAdapter
    '        With da
    '            .SelectCommand = New SqlCommand
    '            .SelectCommand.CommandText = "SELECT DISTINCT category FROM supplier"
    '            .SelectCommand.Connection = con
    '        End With
    '        Dim dr As SqlDataReader = da.SelectCommand.ExecuteReader
    '        Dim dt As New DataTable
    '        dt.Load(dr)
    '        For i As Integer = 0 To dt.Rows.Count - 1
    '            cbocatt.Items.Add(dt.Rows(i)("category"))
    '        Next
    '        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
    Private Sub fill()
        With lvRec
            .Columns.Add("ID", 30, HorizontalAlignment.Left)
            .Columns.Add("Room Number", 110, HorizontalAlignment.Left)
            .Columns.Add("Room Charge", 110, HorizontalAlignment.Left)
            .Columns.Add("Room Type", 110, HorizontalAlignment.Left)
            .Columns.Add("No of Extra", 110, HorizontalAlignment.Left)
            .Columns.Add("No of Adult", 110, HorizontalAlignment.Left)
            .Columns.Add("No of Kids", 80, HorizontalAlignment.Left)
            .Columns.Add("Entry Date", 80, 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 supplier WHERE category 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
            Call opencon()
            Dim myd As New SqlDataAdapter
            With myd
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM suj"
                .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
            Call closecon()
        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try
    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
    Public Sub clear()
        Me.txtname.Clear()
        txtAddress.Clear()
        txtcat.Clear()
        mtxtpno.ResetText()
        txtedt.Clear()
        txtyear.Clear()
        dtentry.ResetText()

    End Sub
    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 frmSupp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call opencon()
        Call fill()
        Call filllv()
        Call closecon()
        ' Call fillcombo()
    End Sub

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

    'Private Sub cbocatt_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
    '    Call opencon()
    '    Call ConSelect()
    '    Call closecon()
    'End Sub

    Private Sub btn_Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
       
    End Sub
    Private Function EditValidate() As Boolean
        If Me.lvRec.Items.Count = 0 Then
            MsgBox("No record to Edit...", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Hotels...")
            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.Critical, "Hotels...")
            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.Critical, "Hotels...")
            Return True
            Exit Function
        End If
        Return 0
    End Function

    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)

    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 txtcat_Leave(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.txtcat.Text = StrConv(Me.txtcat.Text, vbProperCase)
    End Sub

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

    End Sub

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

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

    End Sub

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

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

    End Sub

    Private Sub btn_close_Click_1(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_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 suj WHERE id LIKE '%%" & list.Text & "%%'"
                            .DeleteCommand.Connection = con
                            .DeleteCommand.ExecuteNonQuery()
                        End With
                    Next
                    Call filllv()
                    Call closecon()
                    'action = "Deleted a supplier info"
                    'Call Userlog()
                    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_Edit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Edit.Click
        If Me.txtname.Text.Length = 0 Then
            MsgBox("Please Room number field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub

        ElseIf Me.txtcat.Text.Length = 0 Then
            MsgBox("Please No of Adult field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtaddress.Text.Length = 0 Then
            MsgBox("Please Room charge field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.mtxtpno.Text.Length = 0 Then
            MsgBox("Please Room type field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtedt.Text.Length = 0 Then
            MsgBox("Please No of kids field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtyear.Text.Length = 0 Then
            MsgBox("Please No of extra field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.dtentry.Text.Length = 0 Then
            MsgBox("Please Entry Date field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub

        Else
            Try
                Call opencon()
                Dim myd2 As New SqlDataAdapter
                With myd2
                    .UpdateCommand = New SqlCommand
                    .UpdateCommand.CommandText = "UPDATE suj SET [supname] = @supname,[category] = @category,[address] = @address,[pnumber] = @pnumber,[isb] = @isb,[edt] = @edt,[datte] = @datte WHERE id LIKE '%%" & Me.lvRec.SelectedItems(0).Text & "%%'"
                    .UpdateCommand.Connection = con
                    With .UpdateCommand.Parameters
                        .AddWithValue("@supname", Me.txtname.Text)
                        .AddWithValue("@category", Me.txtaddress.Text)
                        .AddWithValue("@address", Me.mtxtpno.Text)
                        .AddWithValue("@pnumber", Me.txtyear.Text)
                        .AddWithValue("@isb", Me.txtcat.Text)
                        .AddWithValue("@edt", Me.txtedt.Text)
                        .AddWithValue("@datte", Me.dtentry.Text)
                    End With
                    .UpdateCommand.ExecuteNonQuery()
                End With
                Call filllv()
                Call closecon()
                'action = "Performs supplier info update"
                'Call Userlog()
                MsgBox("Record successfully updated...", MsgBoxStyle.Information, "Submitted...")

                Call clear()

            Catch ex As Exception
                MsgBox(ex.Message)
                Call closecon()
            End Try
        End If
    End Sub

    Private Sub btn_Add_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Add.Click
        If Me.txtname.Text.Length = 0 Then
            MsgBox("Please Room number field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub

        ElseIf Me.txtcat.Text.Length = 0 Then
            MsgBox("Please No of Adult field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtaddress.Text.Length = 0 Then
            MsgBox("Please Room charge field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.mtxtpno.Text.Length = 0 Then
            MsgBox("Please Room type field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtedt.Text.Length = 0 Then
            MsgBox("Please No of kids field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.txtyear.Text.Length = 0 Then
            MsgBox("Please No of extra field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub
        ElseIf Me.dtentry.Text.Length = 0 Then
            MsgBox("Please Entry Date field is empty....", MsgBoxStyle.Information, "Hotels..")
            Exit Sub

        Else

            Call opencon()
            Dim myda As New SqlDataAdapter
            With myda
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM suj WHERE supname LIKE '%%" & Me.txtname.Text & "%%'"
                .SelectCommand.Connection = con
            End With
            Dim dr As New DataSet
            myda.Fill(dr, "myTable")
            If dr.Tables("myTable").Rows.Count > 0 Then
                MsgBox("Room Number already exist...", MsgBoxStyle.Exclamation, "Alert...")
                Me.txtname.Focus()
                Me.txtname.SelectAll()
                Exit Sub
            Else


                Try
                    Call opencon()
                    Dim myd As New SqlDataAdapter
                    With myd
                        .InsertCommand = New SqlCommand
                        .InsertCommand.CommandText = "INSERT INTO suj(supname,category,address,pnumber,isb,edt,datte,status)VALUES(@supname,@category,@address,@pnumber,@isb,@edt,@datte,@status) "
                        .InsertCommand.Connection = con
                        With .InsertCommand.Parameters
                            .AddWithValue("@supname", Me.txtname.Text)
                            .AddWithValue("@category", Me.txtaddress.Text)
                            .AddWithValue("@address", Me.mtxtpno.Text)
                            .AddWithValue("@pnumber", Me.txtyear.Text)
                            .AddWithValue("@isb", Me.txtcat.Text)
                            .AddWithValue("@edt", Me.txtedt.Text)
                            .AddWithValue("@datte", Me.dtentry.Text)
                            .AddWithValue("@status", "Not Lodged")
                        End With
                        .InsertCommand.ExecuteNonQuery()
                    End With
                    'action = "Registered a new supplier"
                    'Call Userlog()
                    MsgBox("Record successfully saved...", MsgBoxStyle.Information, "Saved....")
                    Call filllv()
                    Call clear()
                    Call closecon()
                Catch ex As Exception
                    MsgBox(ex.Message)
                    Call closecon()
                End Try
            End If
        End If

        'user = Me.txtname.Text
        'action = "Registered " + user + "as a supplier"
        'logger = FrmLogin.txtname.Text
        'Call Userlog()
    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 suj 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.txtname.Text = IIf(.IsDBNull(1), " ", .Item("supname")).ToString
                    Me.txtaddress.Text = IIf(.IsDBNull(2), " ", .Item("category")).ToString
                    Me.mtxtpno.Text = IIf(.IsDBNull(3), " ", .Item("address")).ToString
                    Me.txtyear.Text = IIf(.IsDBNull(4), " ", .Item("pnumber")).ToString
                    Me.txtcat.Text = IIf(.IsDBNull(5), " ", .Item("isb")).ToString
                    Me.txtedt.Text = IIf(.IsDBNull(6), " ", .Item("edt")).ToString
                    Me.dtentry.Text = IIf(.IsDBNull(7), " ", .Item("datte")).ToString
                End If
            End With
            Call closecon()
        End If
    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 Panel5_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel5.Paint

    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 txtname_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtname.KeyPress
        Dim NotAllowed As String = "~!`@#$%^&*()_+={}][|\;:'/?.>,<"
        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 txtname_Leave1(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtname.Leave
        Me.txtname.Text = StrConv(Me.txtname.Text, vbProperCase)
    End Sub

    Private Sub txtname_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtname.TextChanged

    End Sub

    Private Sub txtcat_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtcat.KeyPress
        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 txtcat_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtcat.TextChanged

    End Sub

    Private Sub txtaddress_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtaddress.KeyPress
        Dim NotAllowed As String = "~!`@#$%^&*()_+={}][|\;:'/?.>,<"
        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 txtaddress_Leave1(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtaddress.Leave
        ' Me.txtaddress.Text = FormatCurrency(txtaddress.Text, 2)
    End Sub

    Private Sub txtaddress_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtaddress.TextChanged

    End Sub

    Private Sub txtedt_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtedt.KeyPress
        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 txtedt_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtedt.TextChanged

    End Sub

    Private Sub txtyear_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtyear.KeyPress
        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 txtyear_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtyear.TextChanged

    End Sub

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

    End Sub
End Class