04.10.2018, 06:47
Hi.
I got this script to work. But i have two qiestions.
It is possible to skip past hour data and get the acctual time instead?
How read the unit/suffix and add it to the result.
Result:
--Date, Time, Adress, Name, Value
"2018.10.04 07:34:10","33/1/3","Meter 2","1495"
--------Script write to FTP----------
-- get past hour data (3600 seconds)
logtime = os.time() - 10 * 1
-- list of objects by id
objects = {'Mätare'}
-- objects with logging enabled
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
objects[ tonumber(object.address) ] = {
datatype = tonumber(object.datatype),
name = tostring(object.name or ''),
}
end
-- csv buffer
buffer = { }
-- get object logs
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
object = objects[ tonumber(row.address) ]
-- found matching object and event type is group write
if object and row.eventtype == 'write' then
datatype = object.datatype
-- check that object datatype is set
if datatype then
-- decode data
data = knxdatatype.decode(row.datahex, datatype)
-- remove null chars from char/string datatype
if datatype == dt.char or datatype == dt.string then
data = data:gsub('%z+', '')
-- date to DD.MM.YYYY
elseif datatype == dt.date then
data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
-- time to HH:MM:SS
elseif datatype == dt.time then
data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
end
else
data = ''
end
-- format csv row
logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
csv = string.format('\r%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))
-- add to buffer
table.insert(buffer, csv)
end
end
ftpfile = string.format('/home/ftp/%s.csv', os.date('%Y-%m-%d_%H-%M'))
if #buffer > 1 then
data = table.concat(buffer, '\r\n')
io.writefile(ftpfile, data)
end
log(data)
I got this script to work. But i have two qiestions.
It is possible to skip past hour data and get the acctual time instead?
How read the unit/suffix and add it to the result.
Result:
--Date, Time, Adress, Name, Value
"2018.10.04 07:34:10","33/1/3","Meter 2","1495"
--------Script write to FTP----------
-- get past hour data (3600 seconds)
logtime = os.time() - 10 * 1
-- list of objects by id
objects = {'Mätare'}
-- objects with logging enabled
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
objects[ tonumber(object.address) ] = {
datatype = tonumber(object.datatype),
name = tostring(object.name or ''),
}
end
-- csv buffer
buffer = { }
-- get object logs
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
object = objects[ tonumber(row.address) ]
-- found matching object and event type is group write
if object and row.eventtype == 'write' then
datatype = object.datatype
-- check that object datatype is set
if datatype then
-- decode data
data = knxdatatype.decode(row.datahex, datatype)
-- remove null chars from char/string datatype
if datatype == dt.char or datatype == dt.string then
data = data:gsub('%z+', '')
-- date to DD.MM.YYYY
elseif datatype == dt.date then
data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
-- time to HH:MM:SS
elseif datatype == dt.time then
data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
end
else
data = ''
end
-- format csv row
logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
csv = string.format('\r%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))
-- add to buffer
table.insert(buffer, csv)
end
end
ftpfile = string.format('/home/ftp/%s.csv', os.date('%Y-%m-%d_%H-%M'))
if #buffer > 1 then
data = table.concat(buffer, '\r\n')
io.writefile(ftpfile, data)
end
log(data)