Imports System.Collections
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class Prices
  Inherits DBTable
  Private priceList As ArrayList

  Public Sub New(ByVal datab As DBase)
    MyBase.New(datab, "Prices")
    priceList = New ArrayList()
  End Sub
  '------
  Public Sub addRow(ByVal storekey As Long, ByVal foodkey As Long, ByVal price As Single)
    priceList.Add(New StoreFoodPrice(storekey, foodkey, price))
  End Sub
  '------
  Public Overloads Sub makeTable()
    'Umieszcza zawartość list w tabelach bazy danych
    Dim adc As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dset As New DataSet(tablename)
    Dim row As DataRow
    Dim fprice As StoreFoodPrice
    Dim dtable As New DataTable(tablename)

    dset.Tables.Add(dtable)
    adc = db.getConnection
    If adc.State = ConnectionState.Closed Then adc.Open()
    Dim adcmd As New OleDbDataAdapter()

    'Wypełnij tabelę Price
    adcmd.SelectCommand = New OleDbCommand("Select * from " & tablename, adc)
    Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(adcmd)
    adcmd.TableMappings.Add("Table", tablename)
    adcmd.Fill(dset, tablename)
    Dim ienum As IEnumerator = priceList.GetEnumerator
    'Dodaj ceny
    While ienum.MoveNext
      fprice = CType(ienum.Current, StoreFoodPrice)
      row = dtable.NewRow
      row("foodkey") = fprice.getFood
      row("storekey") = fprice.getStore
      row("price") = fprice.getPrice
      dtable.Rows.Add(row)        'Dodaj do tabeli
    End While
    adcmd.Update(dset)            'Aktualizuj informacje w bazie
    adc.Close()
  End Sub
  '------
  Public Function getPrices(ByVal food As String) As DataTable
    Dim query As String
    query = "SELECT Stores.StoreName, " & _
    "Foods.Foodname, Prices.Price " & _
    "FROM (Prices INNER JOIN Foods ON " & _
    "Prices.Foodkey = Foods.Foodkey) " & _
    "INNER JOIN Stores ON Prices.StoreKey = Stores.StoreKey " & _
    "WHERE(((Foods.Foodname) = """ & food & """)) " & _
    "ORDER BY Prices.Price;"
    Return db.openQuery(query)
  End Function
End Class
