export all trends logs - Printable Version

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 = '/SALVARI_BMS/' .. src



--Indiquer HOST / USER / PASSWORD

f, e = ftp.put{

  host = "192.168.1.181", 

  user = "ionel_bbtp",

  password = "my_pass",

  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)