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