Export CSV

[gdimaria]

Hi, 

I am using your script to export csv and send trends via mail.   

  Export csv script.txt (Size: 5.09 KB / Downloads: 96)

If I set export_all = true it sends all trends, of course... but if I set export_all = false it ignore my namescripts list and export n.25 trends instead of the 9 I selected.

What is wrong? Thanks

Peppe

[admin]

I'm not sure that filtering function is correct, try replacing it with this:

Code:

1234567891011121314

-- Check if all trends or selection neeed to be exported if export_all == false then   tmp = {}   for _, t in ipairs(trends_table) do     for _, name in ipairs(trendnames) do       if name == t.name then         tmp[ #tmp + 1 ] = t       end     end   end   trends_table = tmp end -- Check if the is at least 1 trend to be exported

[gdimaria]

uhmm..... now it seems ok, but I give a look to    hh:mm  in the report, I see they become over 24 hours and the day remain the same.

What I really need it to send a monthly mail to the customer with hir trendslog attached.

   

is it possible to select a monthly report like that by script?

[gdimaria]

I'm not sure that filtering function is correct, try replacing it with this:

Code:

1234567891011121314

-- Check if all trends or selection neeed to be exported if export_all == false then   tmp = {}   for _, t in ipairs(trends_table) do     for _, name in ipairs(trendnames) do       if name == t.name then         tmp[ #tmp + 1 ] = t       end     end   end   trends_table = tmp end -- Check if the is at least 1 trend to be exported

there is something wrong here:

Code:

12345678910111213141516171819202122

  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}         n15 = tonumber (string.format("%02d", _))*60*15         modulo = n15*15         hours1 = string.format("%02.f", math.floor(n15/3600));     mins1 = string.format("%02.f", math.floor(n15/60 - (hours1*60)));     Ora = hours1..":"..mins1                 -- format csv row    csv = string.format('%q,%q,%q', string.format("%02d", dates['start'].day) .. "-" .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year, Ora, row)     -- add to buffer     table.insert(buffer, csv)   end

Because time are over the 24 hours:

[admin]

The script assumes that trend resolution is fixed to 15 minutes but it can be different. This is fixable but the whole script has to fully rewritten.

[gdimaria]

I made some tests, and I notice it works fine when export_all = true, when I put it false then happens what I wrote upside.
So I think is just the filtering function not properly working.

here is the script:

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160

--**************************************************************************-- --** 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 *************************************************************-- --Set export mode (selected trend(s) or all trends) export_all = false --Set trend names if not all trends need to be exported (only used when export_all = false) trendnames = {     "Potenza Attiva PT",       "Forno",       "Microonde",       "Piano Lavoro",     "Prese Stireria",     "Lavastoviglie",     "Lavatrice",     "Asciugatrice",     "Lavastoviglie 2",       "Carico 9", } --**************************************************************************-- --**************************** 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   tmp = {}   for _, t in ipairs(trends_table) do     for _, name in ipairs(trendnames) do       if name == t.name then         tmp[ #tmp + 1 ] = t       end     end   end   trends_table = tmp end -- Check if the is at least 1 trend to be exported if #trends_table < 1 then   log("Nessun Trend disponibile, impossibile esportare")   return end -- csv buffer buffer = {} -- Add to buffer table.insert(buffer, '"Questo file contiene i dati in formato CSV di ' .. #trends_table .. ' trend(s), creato automaticamente in data: ' .. os.date("%d-%m-%y alle %H:%M") .. '"') -- Add empty line table.insert(buffer, '""') -- Loop through trends_table for _, trend_names in ipairs(trends_table) do   -- Grab trend name from DB table   trend_name = trend_names.name   if string.sub(trend_name, -1) ~= '_' then       -- Add to buffer   table.insert(buffer, '"START TREND N.' .. _ .. '     <<< ' .. trend_name .. ' >>>"')   -- Add empty line   table.insert(buffer, '""')     -- Get current timestamp timestamp = os.time() enddate = os.date('*t', timestamp)   startdate =  os.date('*t', (timestamp - 60*60*24)) -- 1 day dates = {} dates['start'] = startdate dates['end'] = enddate       -- Set resolution to dayly data   resolution = 60*15 -- 15 min     -- 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 header   table.insert(buffer, '"Data","Ora","Valore Medio Giornaliero"')   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}         n15 = tonumber (string.format("%02d", _))*60*15         modulo = n15*15         hours1 = string.format("%02.f", math.floor(n15/3600));     mins1 = string.format("%02.f", math.floor(n15/60 - (hours1*60)));     Ora = hours1..":"..mins1                 -- format csv row    csv = string.format('%q,%q,%q', string.format("%02d", dates['start'].day) .. "-" .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year, Ora, row)     -- add to buffer     table.insert(buffer, csv)   end   -- Add empty linestring.format("%02d", _)   table.insert(buffer, '""')   -- Add header   table.insert(buffer, '"END TREND ' .. _ .. '     <<< ' .. trend_name .. ' >>>"')   -- Add empty line   table.insert(buffer, '""')   end 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') .. '.csv' dst = '/home/ftp/' .. src io.writefile(dst, buffer) -- read csv report file data1 = io.readfile(dst) -- send file as report.csv with text/csv mime type soggetto = 'XXXXXX: invio TRENDS giornalieri' testo = 'Si allega file CSV.' res, err = mailattach('giuseppe.dimaria@komponext.it', soggetto, testo, src, data1, 'text/csv') log(res, err) --Delete created backup file from ftp folder inside HL os.remove(dst)

[Domoticatorino]

Hi,
set a user library with this following code:

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116

-- This function prepare export table and .csv file from defined range function trend_export(name, dates, mode, timestampType)     require('trends')   -- detect timeShift   function detectTimeShift(t0, t1)   t0_isdst, t1_isdst = os.date('*t', t0).isdst, os.date('*t', t1).isdst     if (t0_isdst ~= t1_isdst) then       if (t1_isdst) then         -- change to summertime         return 1       else         -- change to wintertime         return 2       end     else       return false     end   end   -- Convert word "now" in "start" to data table   if type(dates['start']) == "string" then     if dates['start'] == "now" then dates['start'] = os.date("*t", os.time())     elseif dates['start'] == "yesterday" then dates['start'] = os.date("*t", os.time()-24*3600)     elseif dates['start'] == "week_ago" then dates['start'] = os.date("*t", os.time()-7*24*3600)     elseif dates['start'] == "month_ago" then dates['start'] = os.date("*t", os.time()-30*24*3600)     elseif dates['start'] == "year_ago" then dates['start'] = os.date("*t", os.time()-365*24*3600)     end   end   -- Convert word "now" in "end" to data table   if type(dates['end']) == "string" then     if dates['end'] == "now" then dates['end'] = os.date("*t", os.time())     elseif dates['end'] == "yesterday" then dates['end'] = os.date("*t", os.time()-24*3600)     elseif dates['end'] == "week_ago" then dates['end'] = os.date("*t", os.time()-7*24*3600)     elseif dates['end'] == "month_ago" then dates['end'] = os.date("*t", os.time()-30*24*3600)     elseif dates['end'] == "year_ago" then dates['end'] = os.date("*t", os.time()-365*24*3600)     end   end            -- Start and end in unix time   starttime = os.time(dates['start'])   endtime = os.time(dates['end'])   -- Invert "start" and "end" when "end" is smaller than "start"   if starttime > endtime then     _ = dates['start']     dates['start'] = dates['end']     dates['end'] = _   end   -- Read trend resolution from DB   resolution = db:getlist("SELECT resolution*60 FROM trends WHERE name='" .. name .. "'")[1]   -- Prepare a table with the data which include the whole data from specified range   if resolution then       -- Expand a range with a one more day if there is some data in the last day     if (os.date("%H", endtime)*3600 + os.date("%M", endtime)*60) >= resolution then       dates['end'].day = dates['end'].day + 1       raw_data = trends.fetch(name, dates, resolution)     else       raw_data = trends.fetch(name, dates, resolution)     end     start = 1     data = {}     start_timestamp = os.time(dates['start']) + resolution     -- Cut the data and prepare a table with the data only from specified range     offset = 0     skip = 0     k = 1     while k <= #raw_data do       v = raw_data[k]             if k == 1 then last_timestamp = start_timestamp else last_timestamp = current_timestamp end       current_timestamp = start_timestamp + (k-1) * resolution + offset                   timeShiftStatus = detectTimeShift(last_timestamp, current_timestamp)       if timeShiftStatus then         if timeShiftStatus == 1 then -- winter->summer           skip = 6 -- skip 1 hour empty data           offset = offset - 3600         elseif timeShiftStatus == 2 then -- summer->winter           offset = offset + 3600         end       end             -- Add to new table only records from specified range       if current_timestamp >= starttime and current_timestamp <= endtime then         if timestampType == 'unixepoch' then data[start] = {current_timestamp, v}         else data[start] = {os.date('%Y.%m.%d %H:%M:%S', current_timestamp), v} end         start = start+1       end       k = k + 1 + skip       skip = 0     end         if mode == 'csv' then       -- Prepare .csv file       for k,v in ipairs(data) do              data[k] = table.concat(data[k], ",")       end         csv = "timestamp, value\r\n" .. table.concat(data, "\r\n")       return name, csv     elseif mode == 'table' then       return name, data     end   else       alert('There is no trend with such a name')   end   end

then in a event script or scheduled script this following:

require('user.trend_pro')

-- Export data
name = 'trend name' -- trend name to export
dates = {}
dates['start'] = "month_ago"
dates['end'] = "now"

name, csv = trend_export(name, dates, 'csv')

-- Send an email with data
attachement = { {filename=name .. '.csv', filedata=csv, mimetype='text/csv'} }
mailattach('mail_address@mail.com', 'Report from trend ' .. name, 'CSV file attached', attachement)