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 whether you accept or reject these cookies being set.

trend export via csv
#1
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
Reply
#2
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
Reply
#3
Thanks for the reply and all the answers you are helping with on this forumSmile

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?
Reply
#4
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
Reply
#5
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
Reply


Forum Jump: