trend export via csv

[PassivPluss]

hi
Earlier you had a example of how to export trends via ftp server as csv but now this doesnt work due to latest firmware.

Is it possible to get an working update for this?

require('socket.ftp')
require('genohm-scada.trends')

time = os.time()

date = {
  stime = time - 86400,
  etime = time,
}

values = trends.fetch('Effektforbruk lys', 'hour', date)

buffer = {}

count = 0
average = 0
resolution = 60 -- in minutes

row = string.format('%q,%q', "Tid", "Watt")
  table.insert(buffer, row)

for _, data in ipairs(values) do
  count = count + 1
  average = average + data[ 2 ]
  if count == resolution then
    date = os.date('%d.%b %H:%M', time -86400)
    value = average / resolution
    row = string.format('%q,%q', date, value)
    
    table.insert(buffer, row)

    time = time + 60 * resolution

    count = 0
    average = 0
  end
 
end
     
if #buffer > 0 then
  data = table.concat(buffer, '\n\r')
  res, err = socket.ftp.put({
    host = 'example.no',
    user = 'example@example.no',
    password = 'knfvkdnkd',
      argument = 'trend_dag_lys.csv',
      source = ltn12.source.string(data)
  })
end

[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

[PassivPluss]

Thanks for the reply and all the answers you are helping with on this forum

I have tried that script earlier but I get the following error
Line 144: attempt to get length of global 'trenddatamonth' ( a nil value)

Any idea what makes this?

[Erwin van der Zwart]

Hi,

Trenddatamonth is the result of the trend.fetch on line 133 and this is (or should be) a table you get back from the trend. 

Are you sure you have used the correct trend name?

See line 30 to 37:

--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",
}

If export_all = false then the table trendnames will be used, so those should match your trend names

BR,

Erwin

[benanderson_475]

I have this working but i want to use this to get daily data and also the max value for the day for a few trends, what would i need to change in this script

Many Thanks

[Gadjoken]

Hello,

Can you confirm that this script by erwin Van der Zwart that i adapted allows you to send all the trends to a remote FTP server?

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 *************************************************************--


--Mettre la valeur true pour exporter l'ensemble des courbes :
export_all = true

-- mettre le noms de chaque courbes que vous souhaitez exporter (Seulement utilisé quand : export_all = false)
trendnames = {
  "Total Electric Usage",
  "Total Water Usage",
  "Total Gas Usage",
}


require('trends')


trends_table = db:getall('SELECT name FROM trends ORDER BY name DESC')


if export_all == false then
 
  i = 1
    for _, trend_names in ipairs(trends_table) do
    delete_from_table = true
   
    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


if #trends_table < 1 then
  log("No trends available, Could not export trends")
  return
end


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") .. '"')

table.insert(buffer, '""')


local months = { "Januari", "Februari", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }


for _, trend_names in ipairs(trends_table) do
 
  trend_name = trend_names.name
 
 
  table.insert(buffer, '"##### START OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
 
  table.insert(buffer, '""')


  timestamp = os.time()
    startpoint = os.date('*t', timestamp)
    endpoint = os.date('*t')

   
    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

   
    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
 
 
  resolution = 86400
 
 
  trenddatamonth = trends.fetch(trend_name, dates, resolution)
 
 
  trenddatamonthavg = trends.fetchone(trend_name, dates, resolution)
 
 
  table.insert(buffer, '"Export of the average usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
 
  table.insert(buffer, '""')
 
  table.insert(buffer, '"Start date","End Date","Average month value"')
 
  table.insert(buffer, '"01-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. #trenddatamonth .. '-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. trenddatamonthavg .. '"')
 
  table.insert(buffer, '""')
 
  table.insert(buffer, '"Detailed export of the daily usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
 
  table.insert(buffer, '""')
 
  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} 
   
    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)
   
    table.insert(buffer, csv)
  end
 
  table.insert(buffer, '""')
 
  table.insert(buffer, '"##### END OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
 
  table.insert(buffer, '""')
end



src = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.csv'
dst = '/home/ftp/' .. src
io.writefile(dst, buffer)




local ftp = require("socket.ftp")
local ltn12 = require("ltn12")


-- Indiquer le dossier du serveur FTP dans lequel vous souhaitez mettre le fichier CSV des courbes
target = '/EXPORT/Courbes/' .. src

--Indiquer HOST / USER / PASSWORD
f, e = ftp.put{
  host = "192.168.54.246",
  user = "FTP-User",
  password = "my_password",
  type = "i",
  argument = target,
  source = ltn12.source.file(io.open(dst, "rb"))
 
}

if (e) then
  log (e)
  log (f)
  alert("Could not ftp: ", e, "\n")
end
log("ftp_trends")


os.remove(dst)

Thank you for your understanding.
Best regards.

[Trond Hoyem]

Hello,

Can you confirm that this script by erwin Van der Zwart that i adapted allows you to send all the trends to a remote FTP server?

Code:

src = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.csv'
dst = '/home/ftp/' .. src
io.writefile(dst, buffer)




local ftp = require("socket.ftp")
local ltn12 = require("ltn12")


-- Indiquer le dossier du serveur FTP dans lequel vous souhaitez mettre le fichier CSV des courbes
target = '/EXPORT/Courbes/' .. src

--Indiquer HOST / USER / PASSWORD
f, e = ftp.put{
  host = "192.168.54.246",
  user = "FTP-User",
  password = "my_password",
  type = "i",
  argument = target,
  source = ltn12.source.file(io.open(dst, "rb"))
 
}

if (e) then
  log (e)
  log (f)
  alert("Could not ftp: ", e, "\n")
end
log("ftp_trends")


os.remove(dst)

Thank you for your understanding.
Best regards.

Hi

I am doing the same here on an ftp I have set up. I can modify the directories and add files from remote computer with the user I have set up, but when I try to write from SL I get "550 Permission denied". Any idea why?

I have disabled the FW on the remote computer. I get no error when connecting (tried with wrong user/pw, and then I get the expected login-failure).

[admin]

Have you checked that you can upload files to the same directory that the script puts files to? You can also try changing type = "i", to command = "appe",

[Trond Hoyem]

Have you checked that you can upload files to the same directory that the script puts files to? You can also try changing type = "i", to command = "appe",

Still get Permission denied. 

I see that the supported types are I, I N, A, A N and L 8. What does the different mean? I tried A, thinking it means "append" and A N, thinking it means "Append new", but that might not be correct.

[admin]

Try uploading to the root directory and check that the user has all required permissions. You can start by giving all permissions to check if it works at all.

[Trond Hoyem]

Try uploading to the root directory and check that the user has all required permissions. You can start by giving all permissions to check if it works at all.

Not entirely sure what I had wrong, but it works now. It might have been some misspelling in the script, but I did a lot of copy/paste now when testing, so not sure what I had to start with. 

Anyway, it works now.