This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm that you accept these cookies being set.

Export CSV
#1
Hi, 

I am using your script to export csv and send trends via mail.   
.txt   Export csv script.txt (Size: 5.09 KB / Downloads: 93)


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

Attached Files
.txt   25 - Trend Export 2020-11-25.txt (Size: 109.09 KB / Downloads: 41)
.txt   43 - Trend Export 2020-11-25.txt (Size: 163.08 KB / Downloads: 11)
Reply
#2
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
Reply
#3
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?
Reply
#4
(25.11.2020, 13:15)admin Wrote: 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:

Reply
#5
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.
Reply
#6
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)
Reply
#7
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:


Quote: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)
Reply


Forum Jump: