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
and function in common
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
-----------
FRANCE SMARTHOME & SMARTBUILDING INTEGRATION
SE ECO EXPERT
FRANCE SMARTHOME & SMARTBUILDING INTEGRATION
SE ECO EXPERT