?Imports System.Data
Imports System.Data.SqlClient       '新增命名空間 for SQL Server


Public Class ProductQuery

    '\\\\\\\\\\\\\\\\\\\\\ 進行點餐 宣告 \\\\\\\\\\\\\\\\\\\\\\\\\
    Dim errorMsg As String
    Dim ConnString As String

    Dim conn As SqlConnection
    Dim da As SqlDataAdapter

    Dim MQ As ModityQuantity
    Dim FL As FoodList
    '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

#Region "資料庫初始化"

    ''' <summary>
    ''' 資料庫初始化
    ''' </summary>
    Public Sub InitDB()

        ConnString = SQLConnectionString()
        conn = New SqlConnection(ConnString)
        conn.Open()

    End Sub

#End Region

    '---------------------------進行點餐 start-------------------------------

    '濃湯類
    Private Sub btn_Soup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Soup.Click
        GetProductListView("Soup")
    End Sub

    '開胃菜
    Private Sub btn_Antipasto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Antipasto.Click
        GetProductListView("Antipasto")
    End Sub

    '主餐類
    Private Sub btn_MainFood_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_MainFood.Click
        GetProductListView("MainFood")
    End Sub

    '飲品類
    Private Sub btn_Refreshmen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Refreshmen.Click
        GetProductListView("Refreshmen")
    End Sub

    '甜點類
    Private Sub btn_Dolce_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Dolce.Click
        GetProductListView("Dolce")
    End Sub

#Region "讀取菜單資料"

    Private Sub GetProductListView(ByVal ProductClassId As String)

        Dim DT As DataTable = GetProductInfoDataTable("ProductId", "ASC", ProductClassId)

        Dim LVI As ListViewItem

        lvwProduct.Items.Clear()

        For Each row As DataRow In DT.Rows

            LVI = New ListViewItem(row("ProductName").ToString())
            LVI.SubItems.Add(row("ProductCost").ToString())
            LVI.SubItems.Add(row("ProductId").ToString())
            lvwProduct.Items.Add(LVI)

        Next

        DT.Dispose()

    End Sub

#End Region

#Region "將ProductInfo資料表的內容以DataTable方式回傳"

    ''' <summary>
    ''' 將ProductInfo資料表的內容以DataTable方式回傳
    ''' </summary>       
    Public Function GetProductInfoDataTable(ByVal OrderByColumn As String, ByVal ASCorDESC As String, ByVal ProductClassId As String) As DataTable

        Dim TableName As String = "ProductInfo"

        Dim selectCmd As String

        selectCmd = "SELECT ProductName, ProductCost, ProductId FROM " + TableName + " WHERE (IsValid = 1) AND (ProductClassId = '" + ProductClassId + "') Order By " + OrderByColumn + " " + ASCorDESC

        Try
            InitDB()

            da = New SqlDataAdapter(selectCmd, conn)
            Dim DT As DataTable = New DataTable()
            da.Fill(DT)

            da.Dispose()
            conn.Close()
            Return DT

        Catch ex As Exception

            MsgBox("資料磁連線錯誤！")
            Return Nothing

        End Try


    End Function

#End Region

    '詳細資料
    Private Sub btn_List_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_List.Click

        Try
            If lvwProduct.Items.Count <> 0 Then

                If lvwProduct.FocusedItem.Selected = True Then

                    Dim ProductName As String = lvwProduct.Items(lvwProduct.FocusedItem.Index).SubItems(0).Text
                    Dim ProductId As String = lvwProduct.Items(lvwProduct.FocusedItem.Index).SubItems(2).Text

                    FL = New FoodList(ProductName, ProductId)
                    FL.ShowDialog()

                Else

                    MsgBox("未選取項目！")

                End If

            Else

                MsgBox("未選取項目！")

            End If

        Catch ex As Exception

            MsgBox("未選取項目！")

        End Try

    End Sub

    '---------------------------進行點餐 End-------------------------------
End Class