Imports System.Data.SqlClient
Public Class frmSeat

    Private Sub frmSeat_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        getSeat()
        capacity()
        btnActive.Enabled = False
        Button1.Enabled = False
        Button2.Enabled = False
    End Sub

    Sub capacity()
        Try
            cm = New SqlCommand("select * from tblCapacity", cn)
            dr = cm.ExecuteReader
            While dr.Read
                lblCapacity.Text = dr.Item(0).ToString
            End While
            dr.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Sub getSeat()
        Try
            ListView1.Items.Clear()
            cm = New SqlCommand("select * from tblSeat order by seatNo", cn)
            dr = cm.ExecuteReader
            While dr.Read
                lst = ListView1.Items.Add(dr.Item(0).ToString)
                lst.SubItems.Add(dr.Item(1).ToString)
                lst.SubItems.Add(dr.Item(2).ToString)
            End While
            dr.Close()
            lblCount.Text = recordCount("select count(seatNo) from tblSeat")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If is_empty(txtSeat) = True Then Exit Sub
        cm = New SqlCommand("select * from tblSeat where seatNo like '" & txtSeat.Text & "'", cn)
        dr = cm.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            MessageBox.Show("This seat number is already in the list", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            dr.Close()
            Return
        End If

        dr.Close()
        cm = New SqlCommand("select * from tblCapacity where capacity like '" & lblCount.Text & "'", cn)
        dr = cm.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            MessageBox.Show("Passengers capacity is up to " & lblCapacity.Text & " persons only.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            dr.Close()
            Return
        End If

        dr.Close()
        Try
            cm = New SqlCommand("insert into tblSeat(seatNo,status) values('" & txtSeat.Text & "','Active')", cn)
            cm.ExecuteNonQuery()
            MessageBox.Show("Successfully saved", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtSeat.Clear()
            getSeat()
            capacity()

            frmCenter.auditTrail(uID, "Add seat number " & txtSeat.Text & "")
            frmCenter.dawat(uID, loginDate)

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

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged
        txtSeat.Text = ListView1.FocusedItem.SubItems(1).Text
        btnSave.Enabled = False
        btnActive.Enabled = True
        Button1.Enabled = True
        Button2.Enabled = True
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If is_empty(txtSeat) = True Then Exit Sub
        Try
            cm = New SqlCommand("update tblSeat set seatNo = '" & txtSeat.Text & "' where seatID like '" & ListView1.FocusedItem.Text & "'", cn)
            cm.ExecuteNonQuery()
            MessageBox.Show("Successfully updated", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
            frmCenter.auditTrail(uID, "Update seat number to " & txtSeat.Text & "")
            frmCenter.dawat(uID, loginDate)
            txtSeat.Clear()
            getSeat()
            capacity()
            btnSave.Enabled = True
            btnActive.Enabled = False
            Button1.Enabled = False
            Button2.Enabled = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If is_empty(txtSeat) = True Then Exit Sub
        Try
            cm = New SqlCommand("delete from tblSeat where seatID like '" & ListView1.FocusedItem.Text & "'", cn)
            cm.ExecuteNonQuery()
            MessageBox.Show("Successfully updated", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
            frmCenter.auditTrail(uID, "Delete seat number " & txtSeat.Text & "")
            frmCenter.dawat(uID, loginDate)
            txtSeat.Clear()
            getSeat()
            capacity()
            btnSave.Enabled = True
            btnActive.Enabled = False
            Button1.Enabled = False
            Button2.Enabled = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnActive_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnActive.Click
        If is_empty(txtSeat) = True Then Exit Sub
        Try
            cm = New SqlCommand("update tblSeat set status = 'Active' where seatID like '" & ListView1.FocusedItem.Text & "'", cn)
            cm.ExecuteNonQuery()
            MessageBox.Show("Successfully Active", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
            frmCenter.auditTrail(uID, "Active seat number " & txtSeat.Text & "")
            frmCenter.dawat(uID, loginDate)
            txtSeat.Clear()
            getSeat()
            capacity()
            btnSave.Enabled = True
            btnActive.Enabled = False
            Button1.Enabled = False
            Button2.Enabled = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class