(27.04.2017, 21:55)gtsamis Wrote: Here is a part of my daily report script. It runs in a scheduled script every day at 00:00 and creates a csv file in /tmp folder for object logs of previous day.

PS: Credit for parts of the script goes to Edgars 

Code:

-- Set logtime start-end and date to get past day data
date = os.date('*t')
date.hour=0
date.min=0
date.sec=0
log_end=os.time(date)-1
date.day = date.day - 1
log_start=os.time(date)


dates = {
 ['start'] = date,
 ['end'] = os.date('*t'),
}

-- Log filename
logfile=string.format('DailyLog_%s.csv', os.date('%d-%m-%Y',log_start))


-- list of objects by id
objects = {}

-- Select objects address and datatype
query = 'SELECT address, datatype, name FROM objects'
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 = { '"date","address","name","value"' }

-- get object logs
-- query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
query = 'SELECT * FROM objectlog WHERE logtime >='..log_start.. ' and logtime<=' .. log_end.. ' ORDER BY id'
for _, row in ipairs(db:getall(query)) 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('%d/%m/%Y %H:%M:%S', row.logtime)
csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

-- add to buffer
table.insert(buffer, csv)
end
end

-- write file only when there's data in buffer
if #buffer > 1 then
result, err = io.writefile ('/tmp/'..logfile, table.concat(buffer, '\r\n'))
log('Created log file '..logfile)
end

-- error while writing
if err then
alert('File write error: %s', tostring(err))
end

(02.05.2017, 14:41)deimostier Wrote:

(27.04.2017, 21:55)gtsamis Wrote: Here is a part of my daily report script. It runs in a scheduled script every day at 00:00 and creates a csv file in /tmp folder for object logs of previous day.

PS: Credit for parts of the script goes to Edgars 

Code:

-- Set logtime start-end and date to get past day data
date = os.date('*t')
date.hour=0
date.min=0
date.sec=0
log_end=os.time(date)-1
date.day = date.day - 1
log_start=os.time(date)


dates = {
 ['start'] = date,
 ['end'] = os.date('*t'),
}

-- Log filename
logfile=string.format('DailyLog_%s.csv', os.date('%d-%m-%Y',log_start))


-- list of objects by id
objects = {}

-- Select objects address and datatype
query = 'SELECT address, datatype, name FROM objects'
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 = { '"date","address","name","value"' }

-- get object logs
-- query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
query = 'SELECT * FROM objectlog WHERE logtime >='..log_start.. ' and logtime<=' .. log_end.. ' ORDER BY id'
for _, row in ipairs(db:getall(query)) 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('%d/%m/%Y %H:%M:%S', row.logtime)
csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

-- add to buffer
table.insert(buffer, csv)
end
end

-- write file only when there's data in buffer
if #buffer > 1 then
result, err = io.writefile ('/tmp/'..logfile, table.concat(buffer, '\r\n'))
log('Created log file '..logfile)
end

-- error while writing
if err then
alert('File write error: %s', tostring(err))
end

Sorry, but it is necessary to create only txt files

(02.04.2020, 11:20)admin Wrote: FTP path is /home/ftp, just make sure that you have some algorithm to delete old files. Otherwise you can run out of free space at some point.

(02.04.2020, 11:20)admin Wrote: FTP path is /home/ftp, just make sure that you have some algorithm to delete old files. Otherwise you can run out of free space at some point.

(02.04.2020, 11:20)admin Wrote: FTP path is /home/ftp, just make sure that you have some algorithm to delete old files. Otherwise you can run out of free space at some point.