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
19.10.2016, 19:56 (This post was last modified: 19.10.2016, 20:06 by 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.
--**************************************************************************----** 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-mailfrom = '<' .. user .. '>'alias_from = 'YOUR ALIAS'--Recipient for e-mailto = '<receiver@domain.com>'alias_to = 'receiver'--Subject for e-mailsubjectpart1 = '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 DBtrends_table = db:getall('SELECT name FROM trends ORDER BY name DESC')
-- Check if all trends or selection neeed to be exportedifexport_all == falsethen-- Loop through trends_tablei = 1for_, trend_namesinipairs(trends_table) dodelete_from_table = true-- Loop through trendnamesfor_, trendnameinipairs(trendnames) doiftrendname == trend_names.namethendelete_from_table = falseendendifdelete_from_table == truethentable.remove(trends_table, i)
endi = i + 1endend-- Check if the is at least 1 trend to be exportedif #trends_table < 1thenlog("No trends available, Could not export trends")
returnend-- csv bufferbuffer = {}
-- Add to buffertable.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 linetable.insert(buffer, '""')
-- monthslocalmonths = { "Januari", "Februari", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }
-- Loop through trends_tablefor_, trend_namesinipairs(trends_table) do-- Grab trend name from DB tabletrend_name = trend_names.name-- Add to buffertable.insert(buffer, '"##### START OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
-- Add empty linetable.insert(buffer, '""')
-- Get current timestamptimestamp = os.time()
startpoint = os.date('*t', timestamp)
endpoint = os.date('*t')
-- Reset to first of monthstartpoint.sec = 0startpoint.min = 0startpoint.hour = 0startpoint.yday = startpoint.yday - (startpoint.day -1)
startpoint.day = 1startpoint.wday = 1endpoint.sec = 0endpoint.min = 0endpoint.hour = 0endpoint.yday = endpoint.yday - (endpoint.day -1)
endpoint.day = 1endpoint.wday = 1-- Get data for the past monthdates = {}
dates['start'] = startpointifdates['start'].month == 1thendates['start'].month = 12dates['start'].year = dates['start'].year - 1elsedates['start'].month = dates['start'].month - 1enddates['end'] = endpointdates['end'].month = dates['start'].month + 1-- Set resolution to dayly dataresolution = 86400-- Get last month for data by each days in this monthtrenddatamonth = trends.fetch(trend_name, dates, resolution)
-- Get last month total avarage datatrenddatamonthavg = trends.fetchone(trend_name, dates, resolution)
-- Add to buffertable.insert(buffer, '"Export of the average usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
-- Add empty linetable.insert(buffer, '""')
-- Add headertable.insert(buffer, '"Start date","End Date","Average month value"')
-- Add to buffertable.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 linetable.insert(buffer, '""')
-- Add to buffertable.insert(buffer, '"Detailed export of the daily usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')
-- Add empty linetable.insert(buffer, '""')
-- Add headertable.insert(buffer, '"Weekday","Date","Average day value"')
for_, rowinipairs(trenddatamonth) dostamp = dates['start'].year .. '-' .. dates['start'].month .. '-' .. string.format("%02d", _)
localy, m, d = stamp:match("(%d+)%-(%d+)%-(%d+)")
localt = { year = y, month = m, day = d}
-- format csv rowcsv = 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 buffertable.insert(buffer, csv)
end-- Add empty linetable.insert(buffer, '""')
-- Add headertable.insert(buffer, '"##### END OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')
-- Add empty linetable.insert(buffer, '""')
end--Create table to include mail settingslocalsettings = {
from = from,
rcpt = to,
user = user,
password = password,
server = 'smtp.gmail.com',
port = 465,
secure = 'sslv23',
}
--Create attachment inside FTP serversrc = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.csv'dst = '/home/ftp/' .. srcio.writefile(dst, buffer)
--Create subjectsubject = subjectpart1 .. ": " .. src .. " " .. subjectpart2--Load required modules to send email with attachmentlocalsmtp = require("socket.smtp")
localmime = require("mime")
localltn12 = require("ltn12")
--Create e-mail headersettings.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 emailr, e = smtp.send(settings)
--Create alert when sending gives an error with error messageif (e) thenlog (e)
log (r)
alert("Could not send email: ", e, "\n")
end--Delete created backup file from ftp folder inside HLos.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):
19.10.2016, 20:14 (This post was last modified: 19.10.2016, 20:19 by 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
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
src = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.csv'dst = '/home/ftp/' .. srcio.writefile(dst, buffer)
localftp = require("socket.ftp")
localltn12 = require("ltn12")
-- Indiquer le dossier du serveur FTP dans lequel vous souhaitez mettre le fichier CSV des courbestarget = '/EXPORT/Courbes/' .. src--Indiquer HOST / USER / PASSWORDf, 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) thenlog (e)
log (f)
alert("Could not ftp: ", e, "\n")
endlog("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).
There are 10 kinds of people in the world; those who can read binary and those who don't
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",
(16.01.2023, 14:12)admin Wrote: 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.
There are 10 kinds of people in the world; those who can read binary and those who don't
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.
(16.01.2023, 14:20)admin Wrote: 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.
There are 10 kinds of people in the world; those who can read binary and those who don't
