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: 95)

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:

-- 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:

-- 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:

  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:

--**************************************************************************--
--** 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:

-- 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)