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 that you accept these cookies being set.

filter alarm table
#25
(12.02.2021, 16:59)admin Wrote: Post the latest version of your script that should export current month data instead of previous

This i scomplete scritp for currento mounth

Code:
--My (smtps) username !IMPORTANT!
user = '...'
--My (smtps) password !IMPORTANT!
password = '...'

--Sender for e-mail
from = '<' .. user .. '>'
alias_from = 'Do not Reply '

--Recipient for e-mail
to = '<claudio@ciemme.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'

date = os.date('*t') -- recupera data e ora dalla tabella del db

date.day, date.hour, date.min, date.sec = 1, 0, 0, 0
ts_end = os.time(date)
date.month = date.month + 1
ts_start = os.time(date)
alerts_table = db:getall('SELECT * FROM alerts where alerttime BETWEEN ? AND ? ORDER BY id DESC', ts_start, ts_end)
--alerts_table = db:getall('SELECT * FROM alerts ORDER BY id DESC')

---------------------------------------------------------------------------------
log('Dal - ' .. os.date("%F",ts_start) .. ' Al - ' .. os.date("%F",ts_end))
---------------------------------------------------------------------------------

-- 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("%d.%m.%Y %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)

BR
Reply


Messages In This Thread
filter alarm table - by Frank68 - 12.02.2021, 12:07
RE: filter alarm table - by admin - 12.02.2021, 12:14
RE: filter alarm table - by Frank68 - 12.02.2021, 12:41
RE: filter alarm table - by admin - 12.02.2021, 13:04
RE: filter alarm table - by Frank68 - 12.02.2021, 13:12
RE: filter alarm table - by admin - 12.02.2021, 13:20
RE: filter alarm table - by Frank68 - 12.02.2021, 13:35
RE: filter alarm table - by admin - 12.02.2021, 13:45
RE: filter alarm table - by Frank68 - 12.02.2021, 13:49
RE: filter alarm table - by admin - 12.02.2021, 14:00
RE: filter alarm table - by Frank68 - 12.02.2021, 14:07
RE: filter alarm table - by admin - 12.02.2021, 14:18
RE: filter alarm table - by Frank68 - 12.02.2021, 14:22
RE: filter alarm table - by admin - 12.02.2021, 14:25
RE: filter alarm table - by Frank68 - 12.02.2021, 14:30
RE: filter alarm table - by admin - 12.02.2021, 14:40
RE: filter alarm table - by Frank68 - 12.02.2021, 14:56
RE: filter alarm table - by admin - 12.02.2021, 15:14
RE: filter alarm table - by Frank68 - 12.02.2021, 15:21
RE: filter alarm table - by admin - 12.02.2021, 15:27
RE: filter alarm table - by Frank68 - 12.02.2021, 15:35
RE: filter alarm table - by admin - 12.02.2021, 15:41
RE: filter alarm table - by Frank68 - 12.02.2021, 15:56
RE: filter alarm table - by admin - 12.02.2021, 16:59
RE: filter alarm table - by Frank68 - 15.02.2021, 07:03
RE: filter alarm table - by admin - 15.02.2021, 07:49
RE: filter alarm table - by Frank68 - 15.02.2021, 08:03

Forum Jump: