This forum uses cookies

Erwin van der Zwart · (This post was last modified: 05.09.2016, 09:31 by Erwin van der Zwart.)

Here is a script to create trends as batch from start address until end address for new (and relaese candidate) FW.

Code:
-- Batch adding trends - Created by Erwin van der Zwart - Schneider Electric Netherlands -----------
-- For spaceLYnk FW 1.2.1 or higher and homeLYnk FW 1.5.1 or higher --------------------------------
-- Refresh browser after creation to show the trends -----------------------------------------------
----------------------------------------- Start Parameters -----------------------------------------

-- Start address of objects
start_objectaddress = '1/1/1'

-- Trends for all objects between these range are created

-- End address of objects
end_objectaddress = '1/1/3'

-- Set username and password for access to HL
username = 'admin'
password = 'admin'

-- Select trend type to create (or multiple types on same object)
create_trendtype_counter = true
create_trendtype_counter_with_negative_delta = true
create_trendtype_absolute_value = true

-- Set trend precicion (!Important! Can only be these values: 0 to 8)
trendprecision = 2

-- Set trend resolution (!Important! Can only be these values: 5 / 10 / 15 / 20 / 30 / 60 minutes))
trendresolution = 5

-- Set trend count resolution (!Important! Can only be these values: '30' = 30 days, '180' = 180 days, '365' = 1 year, '730' = 2 years, '1825' = 5 years
trendcountresolution = 365

-- Set trend count days (!Important! Can only be these values: '1' = 1 year / '2' = 2 years / '5' = 5 years / '10' = 10 years)
trendcountdaily = 10

-- Set show always 0 base line 
trendshowzero = 0 -- 0 = disabled 1 = enabled

------------------------------------------ End Parameters ------------------------------------------
------------------------------ DON'T CHANGE ANYTHING UNDER THIS LINE -------------------------------
-- Load modules
require('json')
require('socket.url')
require('socket.http')

-- Set HL ip address as localhost
ip = '127.0.0.1'

-- Calculate start address to DB format
start_objectaddress = knxlib.encodega(start_objectaddress)

-- Calculate end address to DB format
end_objectaddress = knxlib.encodega(end_objectaddress)

-- Create url for trend creation
url = 'http://' .. username .. ':' .. password .. '@' .. ip .. '/scada-main/trends/save'

-- Function to send request to create trend
function url_send(trend_object, trend_name, trend_type, trend_resolution, trend_precision, trend_count_resolution, trend_count_daily, trend_show_zero, trend_id)
  local trend = {
      object = trend_object,
      name = trend_name,
      type = trend_type,
      resolution = trend_resolution,
      precision = trend_precision,
    count_resolution = trend_count_resolution * 12 * 24, -- nr of days * nr of points in a hour at min resolution (60/5 = 12) * hours per day
    count_daily = trend_count_daily * 365, -- nr of days * 365 days a year
      show_zero = trend_show_zero,
      id = trend_id,
    }
  data = json.encode(trend)
    form_data = 'data=' .. socket.url.escape(data)
    socket.http.TIMEOUT = 15   
    local res, code, response_header = socket.http.request(url, form_data)
  return res, code, response_header
end

-- Set counters for creation log
number_of_trends = 0
number_failed = 0

-- Loop from startadres to end address to create trends
for i = start_objectaddress, end_objectaddress, 1 do
  
  -- Get current group address from loop 
  current_GA = knxlib.decodega(i)
  
  -- Get current object info
  objectinfo = grp.find(current_GA)
    
  -- Check if object excists
  if objectinfo ~= nil then
      
    -- Set parameters for request
    trendobject = objectinfo.id
    trendname = objectinfo.name
    trendid = ''
    
    -- Check if trend type 'Counter' should be created
    if create_trendtype_counter == true then
      
      -- Call function to send create request
      result, code, response_header = url_send(trendobject, trendname, 'C', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
      
      -- Calculate results for creation results log
        if result == '{"success":true}' then
          number_of_trends = number_of_trends + 1
        else
          number_failed = number_failed + 1
        end
      
    end
    
    -- Check if trend type 'Counter with negative delta' should be created
    if create_trendtype_counter_with_negative_delta == true then
      
            -- Call function to send create request
      result, code, response_header = url_send(trendobject, trendname, 'D', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
          
      -- Calculate results for creation results log
        if result == '{"success":true}' then
          number_of_trends = number_of_trends + 1
        else
          number_failed = number_failed + 1
        end
      
    end
    
    -- Check if trend type 'Absolute value' should be created
    if create_trendtype_absolute_value == true then
            
      -- Call function to send create request
      result, code, response_header = url_send(trendobject, trendname, 'G', trendresolution, trendprecision, trendcountresolution, trendcountdaily, trendshowzero, trendid)
          
      -- Calculate results for creation results log
        if result == '{"success":true}' then
          number_of_trends = number_of_trends + 1
        else
          number_failed = number_failed + 1
        end
      
    end
    
  end
  
end
    
if number_failed == 0 then
    log ("Created " .. number_of_trends .. " trends succesfully")
else
    log ("Created " .. number_of_trends .. " trends succesfully and creation of " .. number_failed .. " trends failed")
end

-- Disable script when done automaticly
script.disable(_SCRIPTNAME)

Here is a script to batch export last month trend data as csv and send by mail (with trend selection) for new (and relaese candidate) FW.

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 temp csv file from ftp folder inside HL
os.remove(dst)

BR,

Erwin van der Zwart

Login
Username:
Password:	Lost Password?
	Remember me