Reading from SQL Database to display contents of wine cellar.

[AllHailJ]

I said I would post when I had the system cleaner and working. My cellar is set up with 31 bin columns and 23 rows. Here is a partial picture. To answer the question - Yes I built and installed everything in the room with the help of my wife. The walls are 2x6 and are high density spray foam. The cellar will hold 689 bottles maximum. It is in the basement and it is not maintained by AC. Humidity is manual as well but terracotta pots hold the humidity to 60-70% with little work.



There are step downs in a couple areas to fit under a beam. These areas are represent by "." Empty bins are shown as available. This is still a work in progress as I have to finish populating the database and then do remove and add bottles screen. I also have to copy reviews into the database. I will post to this thread as I progress.

The code to change the bin column and row follow. First to select the column.

05/14/2020 ControlCounter.vb
'Pass three parameters, The addition or subtraction, the refid to increase the value, and weather to print to HS Log

IMPORTS System.IO
IMPORTS System.Core
IMPORTS System.Net

Sub Main(ByVal parms As Object) 'There are 0 parameters

Dim sParm() As String
Dim parm, rName As String
Dim dvRef, Inc As Integer
Dim CntrValue As Integer
Dim Debug As Boolean

If parms.GetType().ToString = "System.String" Then 'Called from an Event
  parm = parms.ToString()
Else     ' Called from HSTouch
  parm = parms(0).ToString() & "|" & parms(1).ToString() & "|" & parms(2).ToString()
End If   'Now use parm as your input variable...

sParm = Split(parm, "|", -1, 1)

Inc = CInt(sParm(0))
If Inc = 2 Then
 Inc = -1
End If

dvRef = CInt(sParm(1))
Debug = sParm(1)
rName = "CtlCntr"

If Debug Then
  hs.WriteLog(rName, parm)
End If

CntrValue = hs.DeviceValueEx(dvRef)
CntrValue = CntrValue + Inc

If CntrValue > 31 Then
  CntrValue = 1
End If

If CntrValue <= 0 Then
  CntrValue = 31
End If

hs.SetDeviceValueByRef(dvRef, CntrValue, True)

End Sub

Then Select the Row

'PopulateWineCellar.vb - Used to pull wine information from database and populate the Wine inventory screen
'5/15/2020 - JLG

IMPORTS System.Data.SQLite
IMPORTS System.Core
IMPORTS System.IO
IMPORTS System.Net
IMPORTS System.Data.Objects

Sub Main(ByVal parms As Object)
'
' cs is the connection String that points to the desired database.
'
  Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;"
  Dim RackCol As String
  Dim parm As String
  Dim Debug As Boolean
  Dim rdr As SqliteDataReader
  Dim dvRef(23), i As Integer
  Dim Wine(23) As String
  Dim sWinery As String

'
'Set up the 23 rows Device Refernce ID
'
  dvRef(0) = 787 'Device which holds the column count.
'
'I created all of my devices that hold the row in the wine rack at the same time and I luckly got them continuous.
'
  For i = 1 to 23
    dvRef(i) = 763 + i
  Next
'
'Get the column number in rack.
'
  RackCol = CStr(hs.DeviceValue(dvRef(0)))

  Try 'Absolutely critical to use or debug messages will be cryptic

  Using con As New SqliteConnection(cs)
    if con.State = ConnectionState.Closed Then
     con.Open() 'open the database
    End if
    Using cmd As New SqliteCommand(con) 'create command
      cmd.CommandText = "SELECT * FROM CellarContents WHERE Column = " & RackCol & " ORDER BY Row ASC"
      rdr = cmd.ExecuteReader()
      i = 0
      Using rdr
        While (rdr.Read()) 'Concatenate the fields into a single string
          i = i + 1
          sWinery = rdr("Winery")
          If sWinery = "N/A" Then
            Wine(i) = "."
          ElseIf sWinery = "Empty" Then
            Wine(i) = "Available "
          Else
            Wine(i)= sWinery & " " & CStr(rdr("Year")) & " " & rdr("WineName") & " " & rdr("Varietal") & " " & rdr("Country") & " " & rdr("Region")
          End If
        End While
      End Using
    End Using
   con.Close()
  End Using

  Catch ex As Exception      'Error trapping
      hs.WriteLogEx("Wine", ex.Message, "#ff0000")
  End Try

  For i = 1 to 23
    hs.SetDeviceString(dvRef(i), Wine(i), True)
  Next

End Sub

Here is the Database I created:

[ATTACH]n1386286[/ATTACH]

Here is what the HSTouch Screen looks like



The up and down buttons do the heavy lifting. They the two scripts above. I will ultimately be able to push the bin number and a proper review will be placed where it now says review.

I hope this helps.

[AllHailJ]

My apologies, I forgot to thank Jon00 and Zwolfpack for all of their help.

[johnpeterson]

Very nice!! Just spent the last hour picking our where to build by cellar and reviewing online review/inventory sites. Another rabbit hole to go down...

John

[AllHailJ]

I have some more updates on the system. I added removal and addition of single bottles to allow better managment. I built a keyboard for hstouch and you can find it in the how section at this link.

https://forums.homeseer.com/forum/ho...rd-for-hstouch

Version 2 handles punctuation but not all. I still have to finish.

I ended up with more virtual devices than I like but so be it. I can view, remove and add bottles to the db from HSTouch. It also copies all removed bottles to a consumed table. I need to add a pictures blog so I can show pics of the wine as well. Software is never done.

Here is a screenshot for Adding and Removing Bottles of Wine.




Here is the code to change the column and row respectively

'05/14/2020 ColumnCounter.vb
'Pass three parameters, The addition or subtraction, the refid to increase the value, and whether to print to HS Log

IMPORTS System.IO
IMPORTS System.Core
IMPORTS System.Net

Sub Main(ByVal parms As Object) 'There are 0 parameters

Dim sParm() As String
Dim parm, rName As String
Dim dvRef, Inc As Integer
Dim CntrValue As Integer
Dim Debug As Boolean

If parms.GetType().ToString = "System.String" Then 'Called from an Event
  parm = parms.ToString()
Else     ' Called from HSTouch
  parm = parms(0).ToString() & "|" & parms(1).ToString() & "|" & parms(2).ToString()
End If   'Now use parm as your input variable...

sParm = Split(parm, "|", -1, 1)

Inc = CInt(sParm(0))
If Inc = 2 Then
 Inc = -1
End If

dvRef = CInt(sParm(1))
Debug = sParm(1)
rName = "CtlCntr"

If Debug Then
  hs.WriteLog(rName, parm)
End If

CntrValue = hs.DeviceValueEx(dvRef)
CntrValue = CntrValue + Inc

If CntrValue > 31 Then
  CntrValue = 1
End If

If CntrValue <= 0 Then
  CntrValue = 31
End If

hs.SetDeviceValueByRef(dvRef, CntrValue, True)

End Sub

Row Counter

'05/18/2020 RowCounter.vb
'Pass three parameters, The addition or subtraction, the refid to increase the value, and whether to print to HS Log

IMPORTS System.IO
IMPORTS System.Core
IMPORTS System.Net

Sub Main(ByVal parms As Object) 'There are 0 parameters

Dim sParm() As String
Dim parm, rName As String
Dim dvRef, Inc As Integer
Dim RowValue As Integer
Dim Debug As Boolean

If parms.GetType().ToString = "System.String" Then 'Called from an Event
  parm = parms.ToString()
Else     ' Called from HSTouch
  parm = parms(0).ToString() & "|" & parms(1).ToString() & "|" & parms(2).ToString()
End If   'Now use parm as your input variable...

sParm = Split(parm, "|", -1, 1)

Inc = CInt(sParm(0))
If Inc = 2 Then
 Inc = -1
End If

dvRef = CInt(sParm(1))
Debug = sParm(1)
rName = "RowCntr"

If Debug Then
  hs.WriteLog(rName, parm)
End If

RowValue = hs.DeviceValueEx(dvRef)
RowValue = RowValue + Inc

If RowValue > 23 Then
  RowValue = 1
End If

If RowValue <= 0 Then
  RowValue = 23
End If

hs.SetDeviceValueByRef(dvRef, RowValue, True)

End Sub

I could have used counters but they are a pain when you have to go from minimum to maximum.

The up and down arrows also call the following script to populate the display string.

'PopulateWineRowCol.vb - Used to pull wine information from database and populate the Removal screen
'5/15/2020 - JLG

IMPORTS System.Data.SQLite
IMPORTS System.Core
IMPORTS System.IO
IMPORTS System.Net
IMPORTS System.Data.Objects

Sub Main(ByVal parms As Object)
'
' cs is the connection String that points to the desired database.
'
  Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;"
  Dim RackCol, RackRow As String
  Dim rdr As SqliteDataReader
  Dim i As Integer
  Dim Wine As String
  Dim sWinery As String
'
'Get the Column and Row number in rack.
'
  RackCol = CStr(hs.DeviceValue(787))
  RackRow = CStr(hs.DeviceValue(789))
  'hs.WriteLog("RowCol", "RackCol = " & RackCol & " RackRow = " & RackRow)


  Try 'Absolutely critical to use or debug messages will be cryptic

  Using con As New SqliteConnection(cs)
    if con.State = ConnectionState.Closed Then
     con.Open() 'open the database
    End if
    Using cmd As New SqliteCommand(con) 'create command
      cmd.CommandText = "SELECT * FROM CellarContents WHERE Column = " & RackCol & " AND Row = " & RackRow & " ORDER BY Row ASC"
      'hs.WriteLog("RackCol", cmd.CommandText)
      rdr = cmd.ExecuteReader()
      Using rdr
        While (rdr.Read()) 'Must use the while even though it is a single record.
          sWinery = rdr("Winery")
          If sWinery = "N/A" Then
            Wine = "."
          ElseIf sWinery = "Empty" Then
            Wine = "Available"
          Else
            Wine = sWinery & " " & CStr(rdr("Year")) & " " & rdr("WineName") & " " & rdr("Varietal") & " " & rdr("Country") & " " & rdr("Region")
          End If
        End While
      End Using
    End Using
   con.Close()
  End Using

  Catch ex As Exception      'Error trapping
      hs.WriteLogEx("RowCol", ex.Message, "#ff0000")
  End Try

  hs.SetDeviceString(790, Wine, True)

End Sub

The code to remove the selected bottle is:

'PopulateWineConsumed.vb - Used to pull wine information from database and populate the Wine inventory screen
'5/15/2020 - JLG

IMPORTS System.Data.SQLite
IMPORTS System.Core
IMPORTS System.IO
IMPORTS System.Net
IMPORTS System.Data.Objects

Sub Main(ByVal parms As Object)
'
' cs is the connection String that points to the desired database.
'
  Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;"
  Dim RackCol, RackRow As String
  Dim rdr As SqliteDataReader
  Dim i, iYear As Integer
  Dim Wine As String
  Dim sWinery,sWineName,sVarietal,sCountry,sRegion,sReview As String
  Dim sp As String = " "
  Dim myNow As DateTime = DateTime.Now
  Dim TimeFmt As String = "MM/dd/yyyy"
'
'Get the Column and Row number in rack.
'
  RackCol = CStr(hs.DeviceValue(787))
  RackRow = CStr(hs.DeviceValue(789))
  'hs.WriteLog("Consum", "RackCol = " & RackCol & " RackRow = " & RackRow)


  Try 'Absolutely critical to use or debug messages will be cryptic

  Using con As New SqliteConnection(cs)
    if con.State = ConnectionState.Closed Then
     con.Open() 'open the database
    End if

'
'Retrieve the data for the row and column
'
    Using cmd As New SqliteCommand(con) 'create command
      cmd.CommandText = "SELECT * FROM CellarContents WHERE Column = " & RackCol & " AND Row = " & RackRow & " ORDER BY Row ASC"
      'hs.WriteLog("RackCol", cmd.CommandText)
      rdr = cmd.ExecuteReader()

      Using rdr
        While (rdr.Read()) 'Must use the while even though it is a single record.
          'hs.WriteLog("RackCol", "Inside While")
          sWinery = rdr("Winery")
          sWineName = rdr("WineName")
          sVarietal = rdr("Varietal")
          iYear = rdr("Year")
          sCountry = rdr("Country")
          sRegion = rdr("Region")
          sReview = rdr("Review")
          'hs.WriteLog("RackCol", sWinery & sp & sWineName & sp & sVarietal & sp & cstr(iYear) & sp &  sCountry & sp & sRegion & sp & sReview)
        End While
      End Using 'rdr

    End Using 'cmd as new

'
'Write the data for the row and column
'
    Using cmd1 As New SqliteCommand(con) 'create command
      cmd1.CommandText = "UPDATE CellarContents SET Winery = 'Empty', WineName = '.', Varietal = '.', Year = 0, Country = '.', Region = '.', Review = 'Review' WHERE Column = " & RackCol & " AND Row = " & RackRow
      'hs.WriteLog("RackCol", cmd1.CommandText)
      cmd1.ExecuteNonQuery()
    End Using 'cmd1 As New

'
'Write the data for the row and column
'
    Using cmd2 As New SqliteCommand(con) 'create command
      cmd2.CommandText = "INSERT INTO WineConsumed (cDate, cWinery, cWineName, cVarietal, cYear, cCountry, cRegion, cReview) VALUES ('" & myNow.ToString(TimeFmt) & "','" & sWinery & "','" & sWineName & "','" & sVarietal & "','" & Cstr(iYear) & "','" & sCountry & "','" & sRegion & "','" & sReview & "')"
      'hs.WriteLog("RackCol", cmd2.CommandText)
      cmd2.ExecuteNonQuery()
    End Using 'cmd2 As New

   con.Close()
  End Using 'con as new sqlite



  Catch ex As Exception      'Error trapping
      hs.WriteLogEx("RowCol", ex.Message, "#ff0000")
  End Try

  If sWinery = "N/A" Then
    Wine = "."
  ElseIf sWinery = "Empty" Then
    Wine = "Available"
  Else
    Wine = sWinery & sp & CStr(iYear) & sp & sWineName & sp & sVarietal & sp & sCountry & sp & sRegion
  End If


  hs.SetDeviceString(790, Wine, True)

End Sub

Note that I still need to create a string data and substitute for my hard coded date. - Code corrected to add date.


I will discuss the add in the next post.

Regards

[AllHailJ]

The last part is to add a bottle of wine to the database.

The code to do this is given below:

'AddBottleRowCol.vb - Used to push wine information to database and populate the Wine inventory screen
'There are no parameters. All dvRefs are hard coded.
'5/20/2020 - JLG

IMPORTS System.Data.SQLite
IMPORTS System.Core
IMPORTS System.IO
IMPORTS System.Net
IMPORTS System.Data.Objects

Sub Main(ByVal parms As Object)
'
' cs is the connection string that points to the desired database.
'
  Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;"
  Dim RackCol, RackRow As String
  Dim Wine As String
  Dim sWinery, sWineName, sVarietal, sYear, sCountry, sRegion, sReview As String

'
'Initialize all the data from the devices.
'
  sWinery   = hs.DeviceString(792)
  sWineName = hs.DeviceString(793)
  sVarietal = hs.DeviceString(794)
  sYear     = hs.DeviceString(795)
  sCountry  = hs.DeviceString(796)
  sRegion   = hs.DeviceString(797)
  sReview   = hs.DeviceString(798)
  RackCol   = hs.DeviceString(799)
  RackRow   = hs.DeviceString(800)

  Wine = "UPDATE CellarContents SET Winery = '" & sWinery & "', WineName = '" & sWineName
  Wine = Wine & "', Varietal = '" & sVarietal & "', Year = " & sYear & ", Country = '" & sCountry
  Wine = Wine & "', Region = '" & sRegion & "', Review = '" & sReview
  Wine = Wine & "' WHERE Column = " & RackCol & " AND Row = " & RackRow

  hs.WriteLog("AddBtl", "RackCol = " & RackCol & " RackRow = " & RackRow & " SQL = " & Wine)

Try 'Absolutely critical to use or debug messages will be cryptic

   Using con As New SqliteConnection(cs)
     If con.State = ConnectionState.Closed Then
      con.Open() 'open the database
     End if
     Using cmd As New SqliteCommand(con) 'create command
       cmd.CommandText = Wine
       cmd.ExecuteNonQuery()
     End Using
    con.Close()
  End Using

  Catch ex As Exception      'Error trapping
      hs.WriteLogEx("RowCol", ex.Message, "#ff0000")
  End Try

End Sub

Here is the script to populate the devices that store the keyboard data:

'SetdbFields.vb
'The purpose of this routine is to move data from Keyboard Stack device to Specific Field Device in HSTouch
'The Routine is expecting one parameters with two pieces of data.  The first is the field device, the second is the Source Device
'5/20/2020 JLG

IMPORTS System.Core
IMPORTS System.IO
IMPORTS System.Net
IMPORTS System.Object

Sub Main(ByVal parms As Object)

  Dim parm     As String
  Dim sParm()  As String
  Dim KbdStack As String
  Dim dvRefTgt As Integer
  Dim dvRefSrc As Integer                        

  If parms.GetType().ToString = "System.String" Then            'Called from an Event
    parm = parms.ToString()
  Else                                                          ' Called from HSTouch
    parm = parms(0).ToString()
  End If 'Now use parm as your input variable...

  sParm = Split(parm, "?", -1, 1) 'Split into array of parameters using "?" as the delimeter

  dvRefTgt = CInt(sParm(0)) 'Target Device
  dvRefSrc = CInt(sParm(1)) 'Source Device

  hs.WriteLog("SetdbF", parm)

  KbdStack = hs.DeviceString(dvRefSrc) 'Retrieve the Data off the stack device
  hs.SetDeviceString(dvRefTgt, KbdStack, True) 'Set the Device String

End Sub

As for a bit of explanation. My premise was to take the typed string in the keyboard device and populate each device that holds data when you press the label. You press add bottle to update the database. None of the values are cleared when you update. This is to allow faster input of the same wine type.

The following is a screenshot of how I programmed the elements. The first is to populate an invisible element with transparent text to hold the text to pass to a script. This was done to have only one invisible element.



The next step is to load the data to the device. This takes the Display text from above to have element text as a parameter. Note that HoldDevNo must have null text for this to work.



The last is to refresh the screen so all data appears.



I have been adding wine to the database and using the tablet to input data is relatively fast and effective - better than lugging a laptop around. Not to mention the heat buildup in the cellar.. To make finding a specific bin easier I am considering putting metal tags every 5 rows and columns. Always another project.

I hope others find value in this.

[AllHailJ]

One last thing - here is the final database for the system. WineCellar.db.txt