Announcement

Collapse
No announcement yet.

Jon00 Database Charting Utility for Homeseer 3 & Homeseer 4

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

    Originally posted by jon00 View Post
    What input method are you recording data into the database? Is the database updated via device value change [Timer=0], regular timer or manual?

    I find this odd as I have made no changes to the internationalization code for over a year.
    Database entry INI file
    Code:
    [DBTable12]
    Name=Temp-Temperature1
    Data1=$dtnr:111||1
    Data1Label="Living"
    Data1Multiplier=
    Data2=$dtnr:112||1
    Data2Label="Meterkast"
    Data2Multiplier=
    Data3=$dtnr:113||1
    Data3Label="Niels"
    Data3Multiplier=
    Data4=$dtnr:114||1
    Data4Label="Bedroom"
    Data4Multiplier=
    Data5=$dtnr:221||1
    Data5Label="Bathroom"
    Data5Multiplier=
    Data6=$dtnr:222||1
    Data6Label="Office"
    Data6Multiplier=
    Data7=
    Data7Label=
    Data7Multiplier=
    ...etc...
    Timer=16
    PurgeDays=
    data as shown in the Web interface (without chart ;-)
    Click image for larger version

Name:	Screen Shot 2015-11-25 at 19.50.46 .png
Views:	1
Size:	91.3 KB
ID:	1182077

    Chart Create INI entry
    Code:
    [Create1]
    DBTable=12
    ChartStyle=1
    ChartPalette=12
    TitleText=Temperature 1
    XAxisText=Date
    YAxisText=Temperature
    YAxisTextSec=
    AxisXFormat=HH:mm
    AxisYFormat=
    AxisYFormatSec=
    UseData1=1
    UseData2=1
    UseData3=1
    UseData4=1
    UseData5=1
    UseData6=1
    UseData7=1
    UseData8=0
    UseData9=0
    UseData10=0
    ChartTimeStart=1
    ChartTimeFinish=0
    ChartTimeFormat=M
    BaselineValue=0
    BaselineValueSec=Auto
    AxisYMaximum=Auto
    AxisYMaximumSec=Auto
    CreateVD=1
    AutoUpdate=1
    I have also replaced all "-0" with "0" in one table but that did not make a difference.

    Any other things I can test?

    Comment


      The issue is that the database is being populated with , rather than .

      I don't understand how that can be happening because the last command is to replace , characters with . before being stored in the database.

      It must be due to you parsing numbers from the device string and something is not working correctly.

      Can you provide me with the full device string (including HTML code etc) for one of the devices in question?

      I have to ask if you need to use this method?
      Jon

      Comment


        Problem solved....

        I found out that while upgrading the DB Charting scrips to the latest 1.0.12 version, files in the bin/jon00, /script & /html folders were NOT updated.

        After updating I immediately noticed the web-based charts working and the saved chart files working.

        TIP: if you have similar problems, download the latest version and compare the file-size / dates with the ones you have in production.

        Comment


          Jon, does the plugin have the ability to plot maximum / average values for a time period? So for instance, could plot the maximum and average temp for a period, based on the temp data being captured from a device every 5 mins? I know I can add an "avg" & "max" label, but can I plot them over time?
          Author of Highpeak Plugins | SMS-Gateway Plugin | Blue Iris Plugin | Paradox (Beta) Plugin | Modbus Plugin | Yamaha Plugin

          Comment


            No but I can take a look. It would need to read the database for the specific metric and time period, find the average or max value and use that as a means to add data back to the database.

            Thinking about it, it may be one step behind.
            Jon

            Comment


              So a mother database config with a data source of Max:database1:source1 or avg:database1:source2 and then use a timespan entry for the time (day, month, hour,etc)?


              Sent from my iPad using Tapatalk HD
              Author of Highpeak Plugins | SMS-Gateway Plugin | Blue Iris Plugin | Paradox (Beta) Plugin | Modbus Plugin | Yamaha Plugin

              Comment


                Just like you configure a data source for the database such as a device value, I was thinking about adding a new method which you would enter the following parameters:

                DBtable number (1 upwards)
                Field number (1 to 10)
                Start time in minutes
                End time in minutes
                Max, Min or Avr (as a number 1, 2 or 3)

                So it could be:

                &dbq:1||4||5||0||1

                So this would query database [DBTable1], field 4, return the maximum value within the time period of the last 5 minutes.
                Jon

                Comment


                  Yup that's what I was thinking and just articulated poorly. Add 'Total' too for counters and I think it would be an awesome enhancement.
                  Author of Highpeak Plugins | SMS-Gateway Plugin | Blue Iris Plugin | Paradox (Beta) Plugin | Modbus Plugin | Yamaha Plugin

                  Comment


                    Yes I'll add total as well.
                    Jon

                    Comment


                      Seems to be working. As I suspected, it is one step behind as it cannot write a metric and then read that same metric at the same time.

                      .
                      Attached Files
                      Jon

                      Comment


                        V1.0.13 released

                        Changes to this version:

                        Relating to Jon00DBCharting.ini and Jon00DBChartingDemo.ini:

                        Added Database entry method $qdb: (read previous data stored in the database and retrieve Maximum/Minimum/Average/Total value over a defined time span in minutes).

                        Relating to Jon00DBChartingCreate.ini and Jon00DBChartingCreateDemo.ini:

                        Added ShowAllValues setting. This allows charts to plot all values in the database for the time span (as it was prior to V1.0.13) or just changes in value which may result in fewer plots (datapoints).

                        Other:


                        You can now download table201.csv database file of the dynamic chart table data which can be imported into Excel etc.
                        Timeout on the dynamic web page increased.
                        Jon

                        Comment


                          Dates messed up?

                          Team,

                          I had to do some manual changes to the DB when I noticed this:

                          The ORIGINAL DBTable41:
                          Click image for larger version

Name:	Screen Shot 2015-12-08 at 21.18.23 .png
Views:	1
Size:	34.3 KB
ID:	1182213

                          When using the Web Interface to display a Chart it will ONLY show data starting 8-12-2015 10:00:03, so data with the "new" date format. It will not show anything before that time, regardless of how I search or which dates I search.


                          Then I exported this table, modified the date formats (in the CSV file) and imported the result in DBTable55:

                          Click image for larger version

Name:	Screen Shot 2015-12-08 at 21.23.30 .png
Views:	1
Size:	38.0 KB
ID:	1182214

                          AGAIN, when using the Web Interface to display a Chart it will ONLY show data starting 8-12-2015 10:00:03 even though it appears as if the dates before 10:00:03 are fine..

                          The ERROR log shows: (right after displaying DBTable55 for the entire day 2015-12-08)
                          Code:
                          2015-12-08 21:13:48.764	Error in ReadDB Block 4: System.FormatException: String was not recognized as a valid DateTime.
                             at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
                             at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)
                             at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
                             at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
                             at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
                             at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
                             at System.Data.SQLite.SQLiteDataReader.GetValues(Object[] values)
                             at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
                             at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
                             at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
                             at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
                             at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
                             at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
                             at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
                             at System.Data.DataTable.Load(IDataReader reader)
                             at Jon00DBChartingHS3.Form1.쟌딱ﯾʢꆃ(String ਕጣ덨䁸J, String 쓘≩㘰쩬Ȏ챐, String ਲ਼윅屝箬椾峀廓滌)
                          2015-12-08 21:13:48.780	Error in CreatePage Block 1: System.DivideByZeroException: Attempted to divide by zero.
                             at System.Decimal.FCallDivide(Decimal& d1, Decimal& d2)
                             at System.Decimal.Divide(Decimal d1, Decimal d2)
                             at Jon00DBChartingHS3.Form1.푌ᯱ䂨䓠흇㆜楍捺(Object ﵭ牪영骍꒿梾왳)
                          Any idea?

                          Comment


                            I don't understand the question. Why are you changing the date formats in the first place? Manually updating the database is ripe for corruption issues.
                            Jon

                            Comment


                              Originally posted by jon00 View Post
                              I don't understand the question. Why are you changing the date formats in the first place? Manually updating the database is ripe for corruption issues.
                              Well, to he honest I have no clue what happened there and why
                              1. the newly written data has a different format?
                              2. how the imported data's date format somehow changed?

                              One of the two happened but don't see the logic :-(

                              Comment


                                So is this all databases or just one? I cannot see how this can happen unless you were playing with regional settings.
                                Jon

                                Comment

                                Working...
                                X