This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm whether you accept or reject these cookies being set.

Read values in scheduled script store in ftp
#13
I have the function now. It is possible to skip the adress in the csv file.

I just want Date, Time, Name and Value

2021.01.13 18:38:17,"16/1/3","Mät 3","3400502"
2021.01.13 18:38:12,"16/1/2","Mät 2","200302"
2021.01.13 18:38:08,"16/1/1","Mät 1","10202"
Br


Code:
-- get past hour data (3600 seconds)
logtime = os.time() - 60 * 60

-- 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 = { '"date","name","adress","value"' }

-- 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('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

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

-- transform table to string for csv file
data1 = table.concat(buffer, '\r\n')

  -- make sure these settings are correctat
  local settings = {
    -- "from" field, only e-mail must be specified here
  from = 'gmail@gmail.com',
    -- smtp username
  user = 'gmail@gmail.com',
    -- smtp password
  password = 'Password!',
    -- smtp server
    server = 'smtp.gmail.com',
    -- smtp server port
    port = 465,
    -- enable ssl, required for gmail smtp
    secure = 'sslv23',
  }

  mime = require("mime")
  ltn12 = require("ltn12")
  local smtp = require('socket.smtp')
  local escape = function(v)
  return '<' .. tostring(v) .. '>'
  end

settings.from = escape(settings.from)
settings.rcpt = { escape(to) }

settings.source = smtp.message{
   headers = {
    to = to,
      subject = subject,
    },
   body = {
      preable = "This email contains 1 attachment.",
      [1] = {
      body = mime.eol(0, [["This message is automaticly send, Do not reply on this e-mail."]])
      },
      [2] = {
     headers = {
      ["content-type"] = 'text/plain; name="Objectlog.csv"',
      ["content-disposition"] = 'attachment; filename="Objectlog.csv"',
     },
      body = data1
      },
    epilogue = "End of mail"
   }
}

r, e = smtp.send(settings)

if (e) then
  io.stderr:write("Could not send email: ", e, "\n")
end
Reply


Messages In This Thread
RE: Read values in scheduled script store in ftp - by Rauschentofft - 13.01.2021, 18:00

Forum Jump: