Imports System.Data.SqlClient
Public Class FrmCash


    Public Sub fillcombo()
        txtname.Items.Clear()
        Try
            Call opencon()
            Dim da As New SqlDataAdapter
            With da
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT DISTINCT category FROM stocky"
                .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
                txtname.Items.Add(dt.Rows(i)("category"))
            Next
            Call closecon()
        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try
    End Sub
    Public Sub fillcombo2()
        ComboBox1.Items.Clear()

        Try
            Call opencon()
            Dim da As New SqlDataAdapter
            With da
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT DISTINCT supname FROM suj"
                .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
                ComboBox1.Items.Add(dt.Rows(i)("supname"))
            Next
            Call closecon()
        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try
    End Sub

    Private Sub FrmCash_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call fill()

        fillcombo()
        fillcombo2()
        'Call opencon()
        'filllv()
        'Call closecon()
    End Sub
   
   
   
    
    Public Sub clear()
        Me.txtname.ResetText()
        Me.txtadd.Clear()
        Me.txtpno.Clear()
        Me.TextBox3.Clear()
        Me.dtentry.ResetText()
        Me.dtdue.ResetText()
        ComboBox1.ResetText()
        'txtpay.Clear()
        ''txtbal.Clear()
        'TextBox41.Clear()
    End Sub

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

    Private Sub btn_Refresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Call clear()
    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 txtadd_Leave(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.txtadd.Text = StrConv(Me.txtadd.Text, vbProperCase)
    End Sub

    Private Sub txtadd_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 Label5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label5.Click
        Me.Close()
    End Sub

   
    Private Sub filldtg()
        Try
            Call opencon()
            Dim myd As New SqlDataAdapter
            With myd
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM stocky WHERE category LIKE '%%" & Me.txtname.SelectedItem.ToString & "%%'"
                .SelectCommand.Connection = con
            End With
            Dim dr As SqlDataReader
            dr = myd.SelectCommand.ExecuteReader
            With dr
                If Not .HasRows Then
                    'Nothing
                Else
                    .Read()
                    Me.txtpno.Text = IIf(.IsDBNull(1), " ", .Item("descrip")).ToString
                    Me.txtadd.Text = IIf(.IsDBNull(2), " ", .Item("dater")).ToString
                    lnam = Me.txtpno.Text
                End If
            End With
            Call closecon()

        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try
    End Sub
    Private Sub filltv()
        Try
            Call opencon()
            Dim myd As New SqlDataAdapter
            With myd
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM suj WHERE supname LIKE '%%" & Me.ComboBox1.SelectedItem.ToString & "%%'"
                .SelectCommand.Connection = con
            End With
            Dim dr As SqlDataReader
            dr = myd.SelectCommand.ExecuteReader
            With dr
                If Not .HasRows Then
                    'Nothing
                Else
                    .Read()
                    lnum = IIf(.IsDBNull(1), " ", .Item("id")).ToString
                    lnam = IIf(.IsDBNull(1), " ", .Item("category")).ToString
                    Me.txtstatus.Text = IIf(.IsDBNull(1), " ", .Item("category")).ToString
                    Me.TextBox4.Text = IIf(.IsDBNull(2), " ", .Item("status")).ToString

                End If
            End With
            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
    Private Sub txtname_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtname.SelectedIndexChanged
        Call filldtg()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Call filltv()
    End Sub

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

       
        ElseIf Me.ComboBox1.Text.Length = 0 Then
            MsgBox("Please Room to lodge in field is empty....", MsgBoxStyle.Information, "Hotel System..")
            Exit Sub
        ElseIf Me.dtentry.Text.Length = 0 Then
            MsgBox("Please in date field is empty....", MsgBoxStyle.Information, "Hotel System..")
            Exit Sub
        ElseIf Me.dtdue.Text.Length = 0 Then
            MsgBox("Please out date field is empty....", MsgBoxStyle.Information, "Hotel System..")
            Exit Sub
        ElseIf Me.TextBox2.Text.Length = 0 Then
            MsgBox("Please Amount paid field is empty....", MsgBoxStyle.Information, "Hotel System..")
            Exit Sub
        Else
            If TextBox4.Text = "Lodged" Then
                MsgBox("This room has been lodged......")
                Exit Sub
            Else
                Try
                    Call opencon()
                    Dim myd As New SqlDataAdapter
                    With myd
                        .InsertCommand = New SqlCommand
                        .InsertCommand.CommandText = "INSERT INTO borro(bname,bnum,blevel,bboro,isd,ddate,edate,odate,rid)VALUES(@bname,@bnum,@blevel,@bboro,@isd,@ddate,@edate,@odate,@rid) "
                        .InsertCommand.Connection = con
                        With .InsertCommand.Parameters
                            .AddWithValue("@bname", Me.txtname.Text)
                            .AddWithValue("@bnum", Me.txtadd.Text)
                            .AddWithValue("@blevel", Me.txtpno.Text)
                            .AddWithValue("@bboro", Me.TextBox1.Text)
                            .AddWithValue("@isd", Me.TextBox2.Text)
                            .AddWithValue("@ddate", Me.TextBox3.Text)
                            .AddWithValue("@edate", Me.dtentry.Text)
                            .AddWithValue("@odate", Me.dtdue.Text)
                            .AddWithValue("@rid", lnum)

                        End With
                        .InsertCommand.ExecuteNonQuery()
                    End With
                    'action = "Registered a new supplier"
                    'Call Userlog()
                    MsgBox("Record successfully saved...", MsgBoxStyle.Information, "Saved....")
                    Call updateboro()
                    Call Ref()
                    Call clear()
                    Call closecon()
                Catch ex As Exception
                    MsgBox(ex.Message)
                    Call closecon()
                End Try
            End If

           
        End If
       
    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)
            .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 updateboro()
        Try

            Dim myd2 As New SqlDataAdapter
            With myd2
                .UpdateCommand = New SqlCommand
                .UpdateCommand.CommandText = "UPDATE suj SET [status] = @status WHERE id LIKE '%%" & lnum & "%%'"
                .UpdateCommand.Connection = con
                With .UpdateCommand.Parameters
                    .AddWithValue("@status", "Lodged")
                   
                End With
                .UpdateCommand.ExecuteNonQuery()
            End With



            

        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 borro"
                .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 ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.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 borro  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_Refresh_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Refresh.Click
        Ref()
    End Sub
    Private Sub Ref()
        Me.TextBox1.Text = "0.00"

        fillcombo()
        fillcombo2()
        Call opencon()
        filllv()
        Call closecon()
        Call clear()
    End Sub
    Private Sub groupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles groupBox1.Enter

    End Sub

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

    End Sub
    Dim num As String
    Dim b As Integer
    Dim nk As Integer
    Private Sub dtdue_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtdue.ValueChanged
        num = DateDiff(DateInterval.Day, CDate(dtentry.Text), CDate(dtdue.Text))
        'MessageBox.Show(num & " Day/s")
        b = Val(num)
        If b = 0 Then
            b = 1
        End If

        nk = b * Val(lnam)
            Me.TextBox1.Text = FormatCurrency(nk, 2)
    End Sub

    Private Sub txtstatus_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtstatus.TextChanged
        Me.txtstatus.Text = FormatCurrency(txtstatus.Text, 2)
    End Sub

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
        Me.TextBox3.Text = nk - Val(Me.TextBox2.Text)
    End Sub
End Class