Merge branch 'master' of ssh://xapek.org:2222/var/repos/ebus

2 files changed, 68 insertions, 27 deletions

diff --git a/test.r b/test.r
index 8a5885b..58716e9 100755
--- a/test.r
+++ b/test.r
@@ -32,6 +32,28 @@ for (i in seq(1,nrow(brenner))) {
 }
 brenner <- brennerBla
 
+#  Solar Pumpe
+solarEngine <- dbGetQuery(con,
+    paste("SELECT timestamp as \"time\",
+                  value_int
+           FROM value
+           WHERE sensor_id IN (select id from sensor where name = 'heizkreisregler9.solarDaten.solarPumpe')
+                AND timestamp > now() - interval '1 day'
+                AND value_int IS NOT NULL
+           ORDER BY timestamp asc", sep=""))
+# reduzieren
+solarEngineNew <- table(NULL,NULL)
+emptyList <- c()
+old_value_int <- "blub"
+for (i in seq(1,nrow(solarEngine))) {
+    if (solarEngine$value_int[i] != old_value_int) {
+        old_value_int <- solarEngine$value_int[i]
+        solarEngineNew <- rbind(solarEngineNew, data.frame(time=solarEngine$time[i], value_int=solarEngine$value_int[i]))
+    }
+}
+solarEngine <- solarEngineNew
+
+# Numerische Sensoren Lesen
 readNumericSensorDay <- function (sensorName) {
     d <- dbGetQuery(con,
         paste("SELECT timestamp",
@@ -39,22 +61,24 @@ readNumericSensorDay <- function (sensorName) {
               "  FROM value",
               " WHERE sensor_id IN (select id from sensor where name = '",sensorName,"')",
               "   AND timestamp > now() - interval '1 day'",
+              # ausreißer ausblenden
+              "   AND COALESCE(value_int,value_float) < 200",
+              "   AND COALESCE(value_int,value_float) > -50",
               " ORDER BY timestamp desc", sep=""))
     d$timestamp <- as.POSIXct(round(d$timestamp, "mins"))
-    d <- supsmu(as.vector(d$timestamp), as.vector(d$value))
+    #d <- supsmu(as.vector(d$timestamp), as.vector(d$value))
     names(d) <- c("time", "value")
     
-#    myMean <- function(values) {
-#      print(mad(values))
-#      if (mad(values) > 1) {
-#        print(paste("Streiche ",length(values),
-#                    " Werte aus",sensorName,
-#                    "mad =", mad(values)))
-#        values <- values * NA
-#      }
-#      mean(values)
-#    }
-#
+    myMean <- function(values) {
+      if (mad(values) > 2) {
+        print(paste("Streiche ",length(values),
+                    " Werte aus",sensorName,
+                    "mad =", mad(values)))
+        values <- values * NA
+      }
+      mean(values)
+    }
+
 #    dm <- aggregate(d$value, list(time=d$time), myMean)
     dm <- d
     dm$time <- as.POSIXct(dm$time,origin=nulltime)
@@ -89,6 +113,23 @@ for (i in c(1:nrow(brenner))) {
   text(as.numeric(brenner$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("ab hier", brenner$value_string[i]) ,srt=90)
 }
 
+# quick and dirty add solarEngine to plot
+
+rect_start <- 0
+rect_color <- rgb(255, 255, 0, alpha=40, maxColorValue=255)
+
+for (i in c(1:nrow(solarEngine))) {
+    if (solarEngine$value_int[i] == 1) {
+        rect_start <- solarEngine$time[i];
+    } else if (rect_start != 0) {
+        end <- solarEngine$time[i];
+        # plot
+        rect(rect_start, 0, solarEngine$time[i], 100, col=rect_color,border=F)
+        beginn <- 0
+    }
+    #text(as.numeric(solarEngine$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("Solar Pumpe", solarEngine$value_int[i]), srt=90)
+}
+
 rangeX <- as.POSIXct(range(data$time))
 s <- round.POSIXt(seq(rangeX[1], rangeX[2], by=60*60),"hour")
 axis.POSIXct(1, at=s, format="%H:%M")
diff --git a/trigger.sql b/trigger.sql
index 0c06a99..fb60617 100644
--- a/trigger.sql
+++ b/trigger.sql
@@ -20,26 +20,26 @@ DROP FUNCTION value_insert_trigger_func() CASCADE;
 
 CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$
        BEGIN
-	DELETE FROM value_cache
-	      WHERE date_trunc('hour', timestamp) = 
-	      	    		       (SELECT date_trunc('hour', max(timestamp)) FROM value)
+    DELETE FROM value_cache
+          WHERE date_trunc('hour', timestamp) = 
+                               (SELECT date_trunc('hour', max(timestamp)) FROM value)
                 AND sensor_id = (SELECT sensor_id from value where id = (select max(id) from value));
 
-	INSERT INTO value_cache
+    INSERT INTO value_cache
                SELECT
-	          COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq
-            	 ,date_trunc('hour', timestamp) AS "timestamp"
-            	 ,sensor_id AS "sensor_id"
-            	 ,type AS "type"
-            	 ,AVG(value_float) AS "value_float"
-            	 ,AVG(value_int) AS "value_int"
-            	 ,first(value_string) AS "value_string"
+              COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq
+                 ,date_trunc('hour', timestamp) AS "timestamp"
+                 ,sensor_id AS "sensor_id"
+                 ,type AS "type"
+                 ,AVG(value_float) AS "value_float"
+                 ,AVG(value_int) AS "value_int"
+                 ,first(value_string) AS "value_string"
                  FROM value
-  	         WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value)
-		   AND sensor_id  = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value))
-	      GROUP BY type, sensor_id, date_trunc('hour', timestamp);               
+             WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value)
+           AND sensor_id  = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value))
+          GROUP BY type, sensor_id, date_trunc('hour', timestamp);               
 
-	RETURN NULL;
+    RETURN NULL;
         END;
 $$ LANGUAGE plpgsql;