trend export via csv

[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.