X-Git-Url: https://git.rvb.name/weathermon.git/blobdiff_plain/7edb3771717d15f7c36d8459fa12b3d6f76d7d9a..e32107a7fe79ce34f3bdf860410a6d5455efdca7:/bin/weather-filter?ds=sidebyside

diff --git a/bin/weather-filter b/bin/weather-filter
new file mode 100755
index 0000000..1fc798f
--- /dev/null
+++ b/bin/weather-filter
@@ -0,0 +1,242 @@
+#!/usr/bin/lua
+
+local uci = require("uci")
+local cur = uci.cursor()
+local json = require "json"
+
+local logdb = arg[1]
+
+require "wm_util"
+
+if not logdb then
+
+  print("no sqlite log defined!")
+  return
+
+end
+
+function shallowcopy(orig)
+    local orig_type = type(orig)
+    local copy
+    if orig_type == 'table' then
+        copy = {}
+        for orig_key, orig_value in pairs(orig) do
+            copy[orig_key] = orig_value
+        end
+    else -- number, string, boolean, etc
+        copy = orig
+    end
+    return copy
+end
+
+function median(dataset)
+
+  table.sort(shallowcopy(dataset))
+  return dataset[math.floor(#dataset/2)]
+
+end
+
+function filter_data(dataset,width)
+
+  if not width then 
+    width = 7
+  end  
+
+  local result = {}
+  
+  local window_spread = math.floor(width/2)
+  local window = {}
+
+  for i = 1,window_spread do
+    window[#window+1] = dataset[i]["y"]
+  end
+  
+  for key,value in pairs(dataset) do
+    nextelem = dataset[key+window_spread]
+    if nextelem then
+      window[#window+1] = nextelem["y"]
+    end  
+    if not nextelem or #window>width then
+      table.remove(window,1)
+    end  
+    row = {}
+    row["t"]=value["t"]
+    row["y"]=median(window)
+    result[#result+1] = row
+  end
+  
+  return result
+
+end
+
+function average_results(dataset,con)
+  local name = os.tmpname()
+  touch(name)
+  local tmpcon = assert(env:connect(name))
+  assert(tmpcon:execute("create table series(time_stamp datetime,value float)"))
+  for key,value in pairs(dataset) do
+    assert(tmpcon:execute(string.format("INSERT INTO series(time_stamp,value) VALUES ('%s','%s')",value["t"],value["y"])))
+  end
+  local sql = "select rounded as t,avg(value) as y from (select substr(strftime('%Y-%m-%dT%H:%M',time_stamp),1,15)||'5:00' rounded,value from series) group by rounded order by rounded"
+  results = run_sql(sql,tmpcon)
+  tmpcon:close()
+  os.remove(name)
+  return results
+end
+
+function run_sql(sql,con)
+  local result = {}
+
+  cursor = assert(con:execute(sql))
+  row = cursor:fetch ({}, "a")
+  while row do
+    result[#result+1] = row
+    row = cursor:fetch ({}, "a")
+  end
+ 
+  return result
+end
+
+function get_list(day,con)
+  if day == "-" then
+    sql = string.format("SELECT DISTINCT sensor_id,sensor,param FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') ORDER BY sensor_id,sensor,param")
+  else
+    sql = string.format("SELECT DISTINCT sensor_id,sensor,param FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') ORDER BY sensor_id,sensor,param",day,day)
+  end  
+  return run_sql(sql,con)
+end
+
+function get_raw(day,con,sensor_id,sensor_type,param)
+  format = '%Y-%m-%dT%H:%M:%S'
+  if day == "-" then
+    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,sensor_id,sensor_type,param)
+  else
+    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,day,day,sensor_id,sensor_type,param)
+  end
+  return run_sql(sql,con)
+end
+
+function get_filtered(day,con,sensor_id,sensor_type,param,width)
+  format = '%Y-%m-%dT%H:%M:%S'
+  if day == "-" then
+    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,sensor_id,sensor_type,param)
+  else
+    sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,day,day,sensor_id,sensor_type,param)
+  end
+  return filter_data(run_sql(sql,con),width)
+end
+
+function dump_json(dataset,file)
+  local f
+  if file then
+    f = io.open(file,"w")
+    io.output(f)
+  end
+  io.write(json.encode(dataset))
+  if f then
+    io.close(f)
+  end   
+end
+
+function dump_txt(dataset,file)
+  local f
+  if file then
+    f = io.open(file,"w")
+    io.output(f)
+  end
+  for key,row in pairs(dataset) do
+    io.write(row["t"].." "..row["y"].."\n")
+  end
+  if f then
+    io.close(f)
+  end   
+end
+
+function dump_list(dataset,file)
+  local f
+  if file then
+    f = io.open(file,"w")
+    io.output(f)
+  end
+  for key,row in pairs(dataset) do
+    io.write(row["sensor_id"].." "..row["sensor"].." "..row["param"].."\n")
+  end
+  if f then
+    io.close(f)
+  end   
+end
+
+local command = arg[2]
+local day = arg[3]
+
+local dbdriver = require "luasql.sqlite3"
+env = assert(dbdriver.sqlite3())
+con = assert(env:connect(logdb))
+
+if command == "list" then
+
+  dump_list(get_list(day,con))
+
+elseif command == "get" then
+
+  sensor_id = arg[4]
+  sensor_type = arg[5]
+  param = arg[6]
+
+  dump_txt(get_raw(day,con,sensor_id,sensor_type,param))
+  
+elseif command == "get-filtered" then
+
+  sensor_id = arg[4]
+  sensor_type = arg[5]
+  param = arg[6]
+
+  width = arg[7]
+  if not width then
+    width = 5
+  end  
+
+  dataset =get_filtered(day,con,sensor_id,sensor_type,param)
+  dump_txt(dataset)
+
+elseif command == "get-compacted" then
+
+  sensor_id = arg[4]
+  sensor_type = arg[5]
+  param = arg[6]
+
+  width = arg[7]
+  if not width then
+    width = 5
+  end  
+
+  dataset =get_filtered(day,con,sensor_id,sensor_type,param)
+  dump_txt(average_results(dataset))
+
+elseif command == "dump" then
+
+  local directory = arg[4]
+  if not directory then 
+    directory = "."
+  end  
+  
+  list = get_list(day,con)
+  dump_json(list,directory.."/sensors.json")
+  for key,value in pairs(list) do
+    dump_json(get_filtered(day,con,value["sensor_id"],value["sensor"],value["param"]),directory.."/"..value["sensor_id"].."."..value["sensor"].."."..value["param"]..".json")
+  end
+
+elseif command == "dump-compacted" then
+
+  local directory = arg[4]
+  if not directory then 
+    directory = "."
+  end  
+  
+  list = get_list(day,con)
+  dump_json(list,directory.."/sensors.json")
+  for key,value in pairs(list) do
+    dump_json(average_results(get_filtered(day,con,value["sensor_id"],value["sensor"],value["param"])),directory.."/"..value["sensor_id"].."."..value["sensor"].."."..value["param"]..".json")
+  end
+  
+end