Imports System.Data.SqlClient
Public Class Frmlog
    Dim d As New Windows.Forms.DateTimePicker
    Dim sid As String
    Dim bid As String

    Private Sub Frmlog_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call fill()
        Call opencon()
        Call filllv()
        Call closecon()
    End Sub
    Private Sub fill()
        With lvRec
            .Columns.Add("ID", 30, HorizontalAlignment.Left)
            .Columns.Add("Guest Name", 110, HorizontalAlignment.Left)
            .Columns.Add("Address", 110, HorizontalAlignment.Left)
            .Columns.Add("Phone No", 110, HorizontalAlignment.Left)
            .Columns.Add("Amount Charge", 110, HorizontalAlignment.Left)
            .Columns.Add("Amount Paid", 110, HorizontalAlignment.Left)
            .Columns.Add("Balance", 110, HorizontalAlignment.Left)
            .Columns.Add("Check In", 110, HorizontalAlignment.Left)
            .Columns.Add("Check Out", 110, HorizontalAlignment.Left)
            .Columns.Add("Room ID", 110, HorizontalAlignment.Left)
            .Activation = ItemActivation.OneClick
            .FullRowSelect = True
            .GridLines = True
            .HideSelection = False
            .HoverSelection = False
            .MultiSelect = True
            .View = View.Details
            .HotTracking = False
            .Scrollable = True
            .Font = New Font(lvRec.Font.FontFamily, 11, FontStyle.Bold, GraphicsUnit.Pixel)
            .BackColor = Color.White
        End With

    End Sub
    Private Sub filllv()
        Me.lvRec.Items.Clear()
        Try
            Dim myd As New SqlDataAdapter
            With myd
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT b.* FROM borro as b inner join suj as s on s.id =b.rid WHERE s.status = 'Lodged' and b.stats = 1 "
                .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 Label8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label8.Click
        Me.Close()
    End Sub

    Private Sub btnOut_Click(sender As Object, e As EventArgs) Handles btnOut.Click


        If sid Is Nothing Then
            MessageBox.Show("Select a row First!")
        Else
            Try
                Call opencon()
                Dim myd As New SqlDataAdapter
                With myd
                    .UpdateCommand = New SqlCommand
                    .UpdateCommand.CommandText = "UPDATE suj SET [status] = @status WHERE id LIKE '%%" & sid & "%%' "
                    .UpdateCommand.Connection = con
                    With .UpdateCommand.Parameters
                        .AddWithValue("@status", "Not Lodged")

                    End With
                    .UpdateCommand.ExecuteNonQuery()
                End With
                With myd
                    .UpdateCommand = New SqlCommand
                    .UpdateCommand.CommandText = "UPDATE borro SET stats = @stats WHERE id LIKE '%%" & bid & "%%' "
                    .UpdateCommand.Connection = con
                    With .UpdateCommand.Parameters
                        .AddWithValue("@stats", "0")

                    End With
                    .UpdateCommand.ExecuteNonQuery()
                End With
                Call closecon()
                MessageBox.Show("Guest successfully checked out")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Return
            End Try
            Call opencon()
            Call filllv()
            Call closecon()
        End If
    End Sub

    Private Sub lvRec_ItemSelectionChanged(sender As Object, e As ListViewItemSelectionChangedEventArgs) Handles lvRec.ItemSelectionChanged
        sid = Me.lvRec.SelectedItems(0).SubItems(9).Text
        bid = Me.lvRec.SelectedItems(0).SubItems(0).Text
    End Sub
End Class