trend export via csv

[Erwin van der Zwart]

Hi,

Here is a example on mailing the export from new trends as CSV, the attachment is placed on FTP first, so if you remove the mail part you have a working script.

You can find new trend log functions here: http://openrb.com/docs/trends-new.htm

Code:

--**************************************************************************-- --** Email trendlog data as CSV attachment created by Erwin van der Zwart **-- --************ For HL from FW 1.5 and SL from FW 1.2 with NGINX ************-- --**************************************************************************-- --*************************** Start of parameters **************************-- -- Version 1.1 *************************************************************-- --Gmail (smtp) username !IMPORTANT! user = 'YOUR EMAIL ADRESS' --Gmail (smtp) password !IMPORTANT! password = 'YOUR PASSWORD' --Sender for e-mail from = '<' .. user .. '>' alias_from = 'YOUR ALIAS' --Recipient for e-mail to = '<receiver@domain.com>' alias_to = 'receiver' --Subject for e-mail subjectpart1 = 'Trend export file' subjectpart2 = 'automaticly send by homeLYnk' --Message on bottom of email (will only be showed when client don't understand attachment) epilogue = 'End of message' --Set export mode (selected trend(s) or all trends) export_all = true --Set trend names if not all trends need to be exported (only used when export_all = false) trendnames = {  "Total Electric Usage",  "Total Water Usage",  "Total Gas Usage", } --**************************************************************************-- --**************************** End of parameters ***************************-- --**************************************************************************-- --****************** DON'T CHANGE ANYTHING UNDER THIS LINE *****************-- --**************************************************************************-- require('trends') -- Get all trend names from DB trends_table = db:getall('SELECT name FROM trends ORDER BY name DESC') -- Check if all trends or selection neeed to be exported if export_all == false then  -- Loop through trends_table  i = 1     for _, trend_names in ipairs(trends_table) do    delete_from_table = true    -- Loop through trendnames    for _, trendname in ipairs(trendnames) do      if trendname == trend_names.name then            delete_from_table = false      end    end    if delete_from_table == true then      table.remove(trends_table, i)      end      i = i + 1  end end -- Check if the is at least 1 trend to be exported if #trends_table < 1 then  log("No trends available, Could not export trends")  return end -- csv buffer buffer = {} -- Add to buffer table.insert(buffer, '"This file contains the export data of ' .. #trends_table .. ' trend(s) and is automaticly created on ' .. os.date("%A",os.time()) .. ' ' .. os.date("%d-%m-%y at %H:%M") .. '"') -- Add empty line table.insert(buffer, '""') -- months local months = { "Januari", "Februari", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" } -- Loop through trends_table for _, trend_names in ipairs(trends_table) do  -- Grab trend name from DB table  trend_name = trend_names.name    -- Add to buffer  table.insert(buffer, '"##### START OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')  -- Add empty line  table.insert(buffer, '""') -- Get current timestamp  timestamp = os.time()     startpoint = os.date('*t', timestamp)     endpoint = os.date('*t')     -- Reset to first of month     startpoint.sec = 0     startpoint.min = 0     startpoint.hour = 0     startpoint.yday = startpoint.yday - (startpoint.day -1)     startpoint.day = 1     startpoint.wday = 1     endpoint.sec = 0     endpoint.min = 0     endpoint.hour = 0     endpoint.yday = endpoint.yday - (endpoint.day -1)     endpoint.day = 1     endpoint.wday = 1     -- Get data for the past month     dates = {}  dates['start'] = startpoint    if dates['start'].month == 1 then      dates['start'].month = 12    dates['start'].year = dates['start'].year - 1  else    dates['start'].month = dates['start'].month - 1  end     dates['end'] = endpoint  dates['end'].month = dates['start'].month + 1    -- Set resolution to dayly data  resolution = 86400    -- Get last month for data by each days in this month  trenddatamonth = trends.fetch(trend_name, dates, resolution)    -- Get last month total avarage data  trenddatamonthavg = trends.fetchone(trend_name, dates, resolution)    -- Add to buffer  table.insert(buffer, '"Export of the average usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')  -- Add empty line  table.insert(buffer, '""')  -- Add header  table.insert(buffer, '"Start date","End Date","Average month value"')  -- Add to buffer  table.insert(buffer, '"01-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. #trenddatamonth .. '-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. trenddatamonthavg .. '"')  -- Add empty line  table.insert(buffer, '""')  -- Add to buffer  table.insert(buffer, '"Detailed export of the daily usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')  -- Add empty line  table.insert(buffer, '""')  -- Add header  table.insert(buffer, '"Weekday","Date","Average day value"')  for _, row in ipairs(trenddatamonth) do    stamp = dates['start'].year .. '-' .. dates['start'].month .. '-' .. string.format("%02d", _)         local y, m, d = stamp:match("(%d+)%-(%d+)%-(%d+)")         local t = { year = y, month = m, day = d}      -- format csv row    csv = string.format('%q,%q,%q', os.date("%A",os.time(t)), "" .. string.format("%02d", _) .. "-" .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year, row)    -- add to buffer    table.insert(buffer, csv)  end  -- Add empty line  table.insert(buffer, '""')  -- Add header  table.insert(buffer, '"##### END OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')  -- Add empty line  table.insert(buffer, '""') end --Create table to include mail settings local settings = {    from = from,    rcpt = to,    user = user,    password = password,    server = 'smtp.gmail.com',    port = 465,    secure = 'sslv23', } --Create attachment inside FTP server src = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.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 backup file from ftp folder inside HL os.remove(dst)

Sample above has a complex date range selector as we wanted to grab a full month at any moment, here is the easy way to select a range (sample for a week selection):

Code:

-- Get current timestamp timestamp = os.time() enddate = os.date('*t', timestamp) startdate =  os.date('*t', (timestamp - 604800)) -- 1 week dates = {} dates['start'] = startdate dates['end'] = enddate

BR,

Erwin van der Zwart