HomeLynk: sending object log via email

[Koen_Rowies]

Hello,

We have a customer who ask if it's possible to get the object log from HL via email once a day.

I have been testing with a script that sends every hour a .csv of the last hour events to an ftp and that works fine. But now i would like to let it send the .csv by mail using a gmail account. Is there someone who did this already or could help me with modifying this script?

Regards,

Koen

script:

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

require('socket.ftp')

-- ftp file

ftpfile = string.format('ftp://ftp:ftp123@192.168.0.10/%s.csv', os.date('%Y-%m-%d_%H-%M'))

-- get past hour data (3600 seconds)

logtime = os.time() - 60 * 60

-- list of objects by id

objects = {}

-- 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","address","name","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

-- upload to ftp only when there's data in buffer

if #buffer > 1 then

  result, err = socket.ftp.put(ftpfile, table.concat(buffer, '\r\n'))

end

-- error while uploading

if err then

  alert('FTP upload error: %s', tostring(err))

end

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

[lfiat]

Hello Koen,

I propose to use Application for homeLYnk about how to send a email with homeLynk

AN_Email, SMS and FTP in homeLYnk

I suggest you not to send a csv file but the text of your current csv file as the mail content: I don't believe at moment with homeLYnk mail LUA library it is possible to send a file attached in a mail.

Regards,

Lionel

[vaca]

Hi Lionel and Koen,

it is possible to send a file directly without a need of storing it somewhere and actually it is not so complicated.

Just contact this guy , he was solving this issue with me a week or two ago. For sure I will inform him about your question here.

[sesa299442 · Accepted solution]

Hello,

you can use this example function enclosed. I composed it together of some other examples 🙂

You just need to set the parameters in User editable area of the function.

Then you can call the function with two parameters:

- to: destination address

- log_hrs: number of hours you want to display in the csv (backwards from now)

The problem is that there is some problem with LuaSocket library in homeLYnk version 1.2.1.

Please run this script once in order to install library update:

os.execute('opkg --force-depends install http://dl.openrb.com/pkg/luasocket_2.0.2-7_mxs.ipk'))

If you cannot access internet from your homeLYnk, set proxy IP in the command (bold):

os.execute('export http_proxy=http://1.1.1.1:8080/ opkg --force-depends install http://dl.openrb.com/pkg/luasocket_2.0.2-7_mxs.ipk'))

Regards

Tomas

[Koen_Rowies]

Hello Tomas,

Works perfect from the first test!

Thanks!