Imports System.Data.SqlClient
Public Class Form_admin_purchase

    Dim constring As String = My.Settings.dbpharmaConnectionString
    Dim dtAdpter As New SqlDataAdapter
    Dim dtSet As New DataSet

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

        lstboxitem.Visible = False
        lstboxsupplyer.Visible = False
        lstboxsupplyer.Enabled = True

        Dt_exp.Text = Today.Date
        Dt_pur.Text = Today.Date

        Dim time_pur As DateTime
        time_pur = Convert.ToString(System.DateTime.Now.TimeOfDay)
        txttime.Text = Convert.ToDateTime(time_pur).ToShortTimeString

        Dt_pur.Select()

    End Sub
    Private Sub btn_add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add.Click

        DataGridView1.Refresh()

        lb_report_purchaseid.Text = "PurchseID:" + lbpur_id.Text
        lb_report_date.Text = "Date:" + Dt_pur.Text
        lb_report_supplier.Text = "Supplier:" + txtsupplyername.Text

        Dim para_pur(8) As SqlParameter
        para_pur(0) = New SqlParameter("@purchase_id", SqlDbType.VarChar)
        para_pur(0).Value = lbpur_id.Text
        para_pur(1) = New SqlParameter("@item_name", SqlDbType.VarChar)
        para_pur(1).Value = txtitemname.Text
        para_pur(2) = New SqlParameter("@supplyer_name", SqlDbType.VarChar)
        para_pur(2).Value = txtsupplyername.Text
        para_pur(3) = New SqlParameter("@qty", SqlDbType.Int)
        para_pur(3).Value = txtqty.Text
        para_pur(4) = New SqlParameter("@price", SqlDbType.Int)
        para_pur(4).Value = txtprice.Text
        para_pur(5) = New SqlParameter("@date", SqlDbType.DateTime)
        para_pur(5).Value = Dt_pur.Text
        para_pur(6) = New SqlParameter("@time", SqlDbType.DateTime)
        para_pur(6).Value = txttime.Text
        para_pur(7) = New SqlParameter("@exp_date", SqlDbType.DateTime)
        para_pur(7).Value = Dt_exp.Text

        Dim cmd_insert_pur As New SqlCommand
        cmd_insert_pur = New SqlCommand("dbo.pro_purchase_insert", New SqlConnection(constring))
        cmd_insert_pur.CommandType = CommandType.StoredProcedure

        For Each parameter As SqlParameter In para_pur
            If Not parameter Is Nothing Then
                cmd_insert_pur.Parameters.Add(parameter)
            End If
        Next

        Dim cmd_insert_inventory As New SqlCommand
        cmd_insert_inventory = New SqlCommand("dbo.pro_inventory_insert", New SqlConnection(constring))
        cmd_insert_inventory.CommandType = CommandType.StoredProcedure

        Dim para_inventory(5) As SqlParameter
        para_inventory(0) = New SqlParameter("@item_name", SqlDbType.VarChar)
        para_inventory(0).Value = txtitemname.Text
        para_inventory(1) = New SqlParameter("@qty", SqlDbType.Int)
        para_inventory(1).Value = txtqty.Text
        para_inventory(2) = New SqlParameter("@price", SqlDbType.Int)
        para_inventory(2).Value = txtprice.Text
        para_inventory(3) = New SqlParameter("@exp_date", SqlDbType.DateTime)
        para_inventory(3).Value = Dt_exp.Text
        para_inventory(4) = New SqlParameter("@re_order", SqlDbType.Int)
        para_inventory(4).Value = txtrol.Text

        For Each parameter As SqlParameter In para_inventory
            If Not parameter Is Nothing Then
                cmd_insert_inventory.Parameters.Add(parameter)
            End If
        Next

        cmd_insert_pur.Connection.Open()
        cmd_insert_pur.ExecuteNonQuery()
        cmd_insert_pur.Connection.Close()
        cmd_insert_pur.Dispose()

        cmd_insert_inventory.Connection.Open()
        cmd_insert_inventory.ExecuteNonQuery()
        cmd_insert_inventory.Connection.Close()
        cmd_insert_inventory.Dispose()


        Dim para_pur_id As SqlParameter
        para_pur_id = New SqlParameter("@purchase_id", SqlDbType.VarChar)
        para_pur_id.Value = lbpur_id.Text

        Dim rtnval As SqlParameter
        rtnval = New SqlParameter("@val", SqlDbType.Int)
        rtnval.Direction = ParameterDirection.ReturnValue

        Dim cmd_retrieve_pur As New SqlCommand
        cmd_retrieve_pur = New SqlCommand("dbo.pro_total_bill_purchase", New SqlConnection(constring))
        cmd_retrieve_pur.CommandType = CommandType.StoredProcedure
        cmd_retrieve_pur.Parameters.Add(para_pur_id)
        cmd_retrieve_pur.Parameters.Add(rtnval)
        cmd_retrieve_pur.Connection.Open()

        Dim dtTable As New DataTable

        dtAdpter.SelectCommand = cmd_retrieve_pur
        dtAdpter.Fill(dtSet, "dbTable")

        'Dim no_column As New DataColumn
        'no_column = New DataColumn("No", System.Type.GetType("System.Int32"))
        'dtTable.Columns.Add(no_column)

        'Dim i As Integer
        'Dim no As Integer = dtSet.Tables(0).Rows.Count + 1
        'For i = 0 To no
        '    dtTable.Rows.Add()
        '    dtTable.Rows(i)(0) = i
        'Next


        'For Each colum As DataColumn In dtSet.Tables(0).Columns
        '    dtTable.Columns.Add(colum)
        'Next

        Dim total As New Integer
        total = rtnval.Value

        Dim str As String() = {Nothing, "Total", Nothing, Nothing, Nothing, total, Nothing, Nothing, Nothing}

        dtSet.Tables(0).Rows.Add(str)

        If dtSet.Tables(0).Rows.Count > 0 Then
            DataGridView1.DataSource = dtSet.Tables(0)
        End If

        dtAdpter.Dispose()
        dtTable.Clear()
        dtSet.Tables.Clear()

        cmd_retrieve_pur.Connection.Close()
        cmd_retrieve_pur.Dispose()

        clear_purchase_form()
        Dt_pur.Select()

    End Sub

    Private Sub txtsupplyername_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtsupplyername.GotFocus, txtsupplyername.Click

        lstboxsupplyer.Visible = True
        lstboxitem.Visible = False
        lstboxsupplyer.Location = New System.Drawing.Point(105, 165)

        Dim supcmd As New SqlCommand
        supcmd = New SqlCommand("dbo.pro_fetch_supplier", New SqlConnection(constring))
        supcmd.CommandType = CommandType.StoredProcedure

        dtAdpter.SelectCommand = supcmd
        dtAdpter.Fill(dtSet, "tb_supplyer")

        Dim dtSup As DataTable = dtSet.Tables("tb_supplyer")
        dtSup.Rows.Add(Nothing, ("Add New"))

        If dtSet.Tables(0).Rows.Count > 0 Then

            lstboxsupplyer.DataSource = dtSup
            lstboxsupplyer.DisplayMember = ("sp_name")

        End If

        dtAdpter.Dispose()
        dtSet.Tables.Clear()

        lstboxsupplyer.Focus()
        lstboxsupplyer.SelectedIndex = -1

    End Sub

    Private Sub lstboxsupplyer_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstboxsupplyer.Click
        txtsupplyername.Text = lstboxsupplyer.GetItemText(lstboxsupplyer.SelectedItem)
        lstboxsupplyer.Visible = False
    End Sub

    Private Sub lstboxsupplyer_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles lstboxsupplyer.KeyPress

        If Microsoft.VisualBasic.Asc(e.KeyChar) = 13 Then
            txtsupplyername.Text = lstboxsupplyer.GetItemText(lstboxsupplyer.SelectedItem)
            lstboxsupplyer.Visible = False
            txtitemname.Focus()
        End If

    End Sub

    Private Sub lstboxsupplyer_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstboxsupplyer.LostFocus
        txtsupplyername.Text = lstboxsupplyer.GetItemText(lstboxsupplyer.SelectedItem)
        lstboxsupplyer.Visible = False

        If txtsupplyername.Text = "Add New" Then

            form_add_supplier.Show()
            Me.Enabled = False

        End If

        txtqty.Focus()
    End Sub

    Private Sub txtitemname_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtitemname.GotFocus, txtitemname.Click
        lstboxitem.Visible = True
        lstboxsupplyer.Visible = False

        lstboxitem.Location = New System.Drawing.Point(105, 130)

        Dim itemcmd As New SqlCommand
        itemcmd = New SqlCommand("dbo.pro_fetch_item", New SqlConnection(constring))
        itemcmd.CommandType = CommandType.StoredProcedure

        dtAdpter.SelectCommand = itemcmd
        dtAdpter.Fill(dtSet, "tb_item")

        Dim dtItem As DataTable = dtSet.Tables("tb_item")
        dtItem.Rows.Add(Nothing, "Add New")

        If dtSet.Tables(0).Rows.Count > 0 Then

            lstboxitem.DataSource = dtItem
            lstboxitem.DisplayMember = "item_name"

        End If

        dtAdpter.Dispose()
        dtSet.Tables.Clear()

        lstboxitem.Focus()
        lstboxitem.SelectedIndex = -1

    End Sub

    Private Sub lstboxitem_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstboxitem.Click
        txtitemname.Text = lstboxitem.GetItemText(lstboxitem.SelectedItem)
        lstboxitem.Visible = False
    End Sub

    Private Sub lstboxitem_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles lstboxitem.KeyPress
        If Microsoft.VisualBasic.Asc(e.KeyChar) = 13 Then
            txtitemname.Text = lstboxitem.GetItemText(lstboxitem.SelectedItem)
            lstboxitem.Visible = False
            txtqty.Focus()
        End If

    End Sub

    Private Sub lstboxitem_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstboxitem.LostFocus
        txtitemname.Text = lstboxitem.GetItemText(lstboxitem.SelectedItem)
        lstboxitem.Visible = False

        If txtitemname.Text = "Add New" Then

            form_add_item.Show()
            Me.Enabled = False

        End If

        txtsupplyername.Focus()
    End Sub

    Private Sub btn_finish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_finish.Click

        DataGridView1.Dispose()
        form_admin_user.Show()
        Me.Hide()

    End Sub

    Public Sub clear_purchase_form()
        txtitemname.Text = Nothing
        txtsupplyername.Text = Nothing
        txtprice.Text = Nothing
        txtqty.Text = Nothing
        txtprice.Text = Nothing
        txtrol.Text = Nothing
    End Sub

    Private Sub btn_cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_cancel.Click

        clear_purchase_form()

    End Sub

End Class