Automatic export all logs and send by mail

[domotiqa]

I test it but I think the smtp output of my customer is blocked (result 1, nil) for the log. I will try on my test server

Code:

smtp = require('socket.smtp')
mime = require('mime')
ltn12 = require('ltn12')


to = 'xxx@gmail.com'
subject = 'CSV logs'
message='export log'
logtime = os.time() - 60 * 60--*24*7 -- last hour (3600 seconds)


-------------- cherche les cpt
query = [[
  SELECT ol2.name
  FROM objects ol2
  WHERE ol2.disablelog = 0
]]

csv={}
nombre=0
nom={}

for _, objectlog in ipairs(db:getall(query, logtime)) do
    nombre=nombre+1
 
    nom[nombre]=objectlog.name

  ----------------

  --buffer = {'"date","address","name","value"'}
  buffer = {'"date","name","value"'}

  query2 = [[
    SELECT o.id, o.datatype, o.name, ol.datahex, ol.logtime, ol.eventtype
    FROM objectlog ol
    JOIN objects o ON ol.address=o.id
    WHERE (ol.logtime >= ?)     ORDER BY ol.id DESC
  ]]

  for _, row in ipairs(db:getall(query2, logtime)) do
    if row.datatype and row.eventtype == 'write' and row.name == nom[nombre] then
      data = grp.decodevalue(row.datahex, row.datatype)
      logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)

      buffer[ #buffer + 1 ] = string.format('%q,%q,%q',
        --logdate, knxlib.decodega(row.id), row.name, tostring(data))
        logdate, string.split(row.name,'-')[5], tostring(data))
    end
  end

  csv[nombre] = table.concat(buffer, '\r\n')
    
end --pour chaque object ayant log


attachement = {}
nombre=0

for _, nomCpt in ipairs(nom) do
  nombre=nombre+1
  -- create file
  table.insert(attachement, {filename=nomCpt .. '.csv', filedata=csv[nombre], mimetype='text/csv'})
end

mailattach(to, subject, message, attachement)

and function in common

Code:

--********************************
-- send an e-mail with attachments
function mailattach(to, subject, message, files)
  local settings = {
    -- "from" field, only e-mail must be specified here
    from = 'notification@xxx.com',
    -- smtp username
    user = 'notification@xxx.com',
    -- smtp password
    password = 'xxx',
    -- smtp server
    server = 'xxx.net',
    --server = 'xxxx.net',
   
    -- smtp server port
    port = 465,
    --port = 587,
    -- enable ssl, required for gmail smtp
    secure = 'sslv23',
    --secure = 'tlsv1_2',
  }
-- ssl0.ovh.net sur port 465
-- ns0.ovh.net sur ports 25, 5025, 587, enfin tout ce que j'essaye...


  local smtp = require('socket.smtp')

  if type(to) ~= 'table' then
    to = { to }
  end

  for index, email in ipairs(to) do
    to[ index ] = '<' .. tostring(email) .. '>'
  end

    -- escape double quotes in file name
  for _, object in ipairs(table) do
    --files[_].filename = files[_].filename:gsub('"', '\\"')
    end

     -- fixup from field
    local from = '<' .. tostring(settings.from) .. '>'

    -- message headers and body
  email = {headers = {}, body = {}}
  email.headers = {
        to = table.concat(to, ', '),
        subject = subject,
        ['From'] = from,
        --['Content-type'] = 'text/html; charset=utf-8',
      }
  email.body[1] = { headers = { ['Content-Type'] = 'text/html; charset=utf-8', }, body = mime.eol(0, message) }

  -- adding attachements
  for _, object in ipairs(files) do
  email.body[_+1] = {
      headers = { ['Content-Type'] = files[_].mimetype or 'text/plain', ['Content-Disposition'] = 'attachment; filename="' .. files[_].filename ..'"', ['Content-Transfer-Encoding'] = 'BASE64', },
      body = ltn12.source.chain( ltn12.source.string(files[_].filedata), ltn12.filter.chain(mime.encode('base64'), mime.wrap() ) )
    }
  end

    settings.source = smtp.message(email)

  settings.from = from
  settings.rcpt = to

  return smtp.send(settings)
end