--- /dev/null
+#!/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
projects / weathermon.git / blobdiff