Announcement

Collapse
No announcement yet.

Reading from SQL Database to display contents of wine cellar.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reading from SQL Database to display contents of wine cellar.

    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.

    Click image for larger version

Name:	20200517_174601.jpg
Views:	58
Size:	103.6 KB
ID:	1386287

    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.

    Code:
    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

    Code:
    '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

    Click image for larger version

Name:	Screenshot from 2020-05-17 17-26-19.png
Views:	56
Size:	383.6 KB
ID:	1386285

    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.

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

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Click image for larger version  Name:	Screenshot from 2020-05-21 09-23-44.png Views:	0 Size:	400.7 KB ID:	1387422


        Here is the code to change the column and row respectively

        Code:
        '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

        Code:
        '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.

        Code:
        '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:

        Code:
        '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
        Last edited by AllHailJ; May 21st, 2020, 11:43 AM. Reason: Corrected code for Date

        Comment


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

          The code to do this is given below:

          Code:
          '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:

          Code:
          '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.

          Click image for larger version

Name:	Screenshot from 2020-05-24 08-55-42.png
Views:	13
Size:	22.3 KB
ID:	1388136

          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.

          Click image for larger version

Name:	Screenshot from 2020-05-24 08-56-14.png
Views:	13
Size:	13.7 KB
ID:	1388135

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

          Click image for larger version

Name:	Screenshot from 2020-05-24 08-56-43.png
Views:	17
Size:	10.7 KB
ID:	1388134

          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.

          Comment


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

            Comment

            Working...
            X