Home Automation Domotica Forum Europe, Bwired Forum

Code: Select all

      // MS SQL query
      
      sqlw = "DEVID = '"+deviceid.toUpperCase()+"' AND TimeStamp = CAST('"+newMStimestamp+"' AS DATETIME)";     
      q = "";
      q += "IF NOT EXISTS (SELECT * FROM DATA WHERE "+sqlw+") ";
      q += "INSERT INTO DATA (TimeStamp, DevID, StartValue, EndValue) ";
      q += "VALUES ('"+newMStimestamp+"'";
      q += ", '"+deviceid.toUpperCase()+"'";
      q += ","+prvValue.toString();
      q += ","+curValue.toString();
      q += ")";
      
      msqueryQueue.push(q);

      q = "";
      q += "UPDATE DATA SET EndValue = "+curValue.toString();
      q += "' WHERE "+sqlw;
      msqueryQueue.push(q);

Code: Select all

var q = '';
    var s = (new Date()).toMySQLDateFormat() //+ '/'+ s;   
    var d = new Date();
    var n = d.getHours();
      q = "";
      q += "INSERT INTO smartmeter1 (datum, tijd, daguur, aantal, hour_consumed, hour_produced, consumed_rate1, consumed_rate2, produced_rate1, produced_rate2, actual_consumed, actual_produced, gas) ";
      q += "VALUES ('"+s+"'";
      q += ", '"+s+"'";
      q += ", '"+n.toString()+"'";
      q += ", '1'";
      q += ", '0'";
      q += ", '0'";
      q += ", '"+res.ConsumedPowerRate1+"'";
      q += ", '"+res.ConsumedPowerRate2+"'";
      q += ", '"+res.ProducedPowerRate1+"'";
      q += ", '"+res.ProducedPowerRate2+"'";
      q += ", '"+res.ActualConsumedPower+"'";
      q += ", '"+res.ActualProducedPower+"'";
      q += ", "+res.ConsumedGas;      
          q += ")";
          q += " ON DUPLICATE KEY UPDATE"
          q += " tijd='"+s+"',";
      q += " hour_consumed=hour_consumed+('"+res.ConsumedPowerRate1+"'-consumed_rate1)+('"+res.ConsumedPowerRate2+"'-consumed_rate2),";
      q += " hour_produced=hour_produced+('"+res.ProducedPowerRate1+"'-produced_rate1)+('"+res.ProducedPowerRate2+"'-produced_rate2),";
      q += " consumed_rate1='"+res.ConsumedPowerRate1+"',";
      q += " consumed_rate2='"+res.ConsumedPowerRate2+"',";
      q += " produced_rate1='"+res.ProducedPowerRate1+"',";
      q += " produced_rate2='"+res.ProducedPowerRate2+"',";
      q += " actual_consumed='"+res.ActualConsumedPower+"',";
      q += " actual_produced='"+res.ActualProducedPower+"',";
      q += " gas='"+res.ConsumedGas+"',";
          q += " aantal=aantal+1;";
     
    queryQueue.push(q);
    processQueryQueue();

Code: Select all

'Log to Database for HS3
'WR. Muller
'08/03/2014
'Version: 1.0

'VB.Net script to write Homeseer 3 values to SQL Server Express Historical Database

'Add this line to Homeseer\Config\Settings.ini
'    ScriptingReferences=System.Data;System.Data.Dll

' Add this line to Homeseer\Scipts\Startup.ini
'    hs.RegisterStatusChangeCB("L2DH.vb","Main")

Imports System.Data.SqlClient

Sub Main(ByVal Parms As Object)

    Dim dev_address As String
    Dim device_valuenew, device_valueprv As Double
    Dim cn As New SqlConnection("Server=192.168.2.25,1433;Database=HS3Test;User ID=sa;Password=rm7800;Trusted_Connection=False;")

    hs.WriteLog("L2DH", "Script running")
    dev_address = Parms(1) 'address of device
    device_valuenew = Parms(2) 'new value of device
    device_valueprv = Parms(3) 'previous value of device

    Try
        cn.Open()
        hs.WriteLog("L2DB", "Connection is open")
        Dim SQLComm As New SqlCommand("UPDATE dbo.Data SET EndValue='" & device_valuenew & "' WHERE DevID='" & dev_address & "' AND DateDiff(hh, GetDate(),TimeStamp) = 0" & _
                                      "IF @@ROWCOUNT=0" & _
                                      "INSERT INTO dbo.Data(DevID, StartValue, EndValue) VALUES('" & dev_address & "','" & device_valueprv & "','" & device_valuenew & "')", cn)
        SQLComm.ExecuteNonQuery()
        hs.WriteLog("L2DH", "Logged to Historical Database: Address: " & dev_address & ", New Value: " & device_valuenew)
        cn.Close()
        hs.WriteLog("L2DH", "Connection is closed")
        'End If

    Catch ex As Exception
        hs.WriteLogEx("L2DH", "Error: " & ex.Message)
    End Try

End Sub

Code: Select all

USE [HS3Test]
GO

/****** Object:  Table [dbo].[Data]    Script Date: 16-3-2014 8:19:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Data](
	[TimeStamp] [smalldatetime] NOT NULL,
	[LastUpdate] [datetime] NULL,
	[DevID] [nchar](15) NOT NULL,
	[StartValue] [float] NULL,
	[EndValue] [float] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Data] ADD  CONSTRAINT [DF_Data_timestamp]  DEFAULT (dateadd(hour,datediff(hour,(0),getdate()),(0))) FOR [TimeStamp]
GO