12.02.2021, 12:07
Hello
I should only select alarms from the previous month and then export them to a csv file and send the whole thing via email.
But I thought about the query, I'm using the following code.
taking into account that if month = 1 I have to take the data of month 12 and previous year
my problem is the select query I don't know how to filter the column data whose name I don't know (alarm date)
grazie
I should only select alarms from the previous month and then export them to a csv file and send the whole thing via email.
But I thought about the query, I'm using the following code.
Code:
--My (smtps) username !IMPORTANT!
user = 'report@test.com'
--My (smtps) password !IMPORTANT!
password = 'xxxxxx'
--Sender for e-mail
from = '<' .. user .. '>'
alias_from = 'Do not Reply '
--Recipient for e-mail
to = '<xxxx@xxxx.com>'
alias_to = to
--Subject for e-mail
subjectpart1 = 'Lista Allarmi Mese'
subjectpart2 = 'E-Mail inviata in automatico dal WebServer'
--Message on bottom of email (will only be showed when client don't understand attachment)
epilogue = 'End of message'
-- vedere come filtrare mese se 1 prendere 12 e anno -1
----------------------------------------------------------------
if (os.date('%m')==1) then
mp=12
yr=os.date('%Y')-1
else
mp=os.date('%m')-1
yr=os.date('%Y')
end
------------------------------------------------------------------
--log('MESE ' ..mp .. ' ANNO ' ..yr)
-- Get all alerts from DB
alerts_table = db:getall('SELECT * FROM alerts where alerttime like %'..mp..'.'..yr..'% ORDER BY id DESC')
-- csv buffer
buffer = {}
-- format csv row
csv = string.format('%q,%q,%q', "ID", "Messaggio", "Data e Ora")
-- add to buffer
table.insert(buffer, csv)
-- add empty line to buffer
table.insert(buffer, "")
-- Loop through alerts_table
for _, alerts in ipairs(alerts_table) do
-- format csv row
csv = string.format('%q,%q,%q', alerts.id, alerts.alert, os.date("%x %X", alerts.alerttime))
-- add to buffer
table.insert(buffer, csv)
end
--Create table to include mail settings
local settings = {
from = from,
rcpt = to,
user = user,
password = password,
server = 'smtps.aruba.it',
port = 465,
secure = 'sslv23',
}
--Create attachment inside FTP server
src = os.date('%Y-%m') .. '.csv'
dst = '/home/ftp/' .. src
io.writefile(dst, buffer)
--Create subject
subject = subjectpart1 .. ": " .. src .. " " .. subjectpart2
--Load required modules to send email with attachment
local smtp = require("socket.smtp")
local mime = require("mime")
local ltn12 = require("ltn12")
--Create e-mail header
settings.source = smtp.message{
headers = {
from = '' .. alias_from .. ' ' .. from .. '',
to = '' .. alias_to .. ' ' .. to .. '',
subject = subject
},
--Load attachment inside body
body = {
preamble = "",
[1] = {
headers = {
["content-type"] = 'text/plain',
["content-disposition"] = 'attachment; filename="'..src..'"',
["content-description"] = '.. src ..',
["content-transfer-encoding"] = "BASE64",
},
body = ltn12.source.chain(
ltn12.source.file(io.open(dst, "rb")),
ltn12.filter.chain(
mime.encode("base64"),
mime.wrap()
)
)
},
epilogue = epilogue
}
}
--Send the email
r, e = smtp.send(settings)
--Create alert when sending gives an error with error message
if (e) then
log (e)
log (r)
alert("Could not send email: ", e, "\n")
end
--Delete created file from ftp folder inside HL
os.remove(dst)taking into account that if month = 1 I have to take the data of month 12 and previous year
my problem is the select query I don't know how to filter the column data whose name I don't know (alarm date)
grazie