Export CSV - Printable Version

Export CSV - Printable Version

+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: Export CSV (/showthread.php?tid=3018)

Export CSV - gdimaria - 25.11.2020

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

RE: Export CSV - admin - 25.11.2020

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

RE: Export CSV - gdimaria - 26.11.2020

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.

Filename: Cattura (1).PNG Size: 174.17 KB

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

RE: Export CSV - gdimaria - 26.11.2020

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

RE: Export CSV - admin - 27.11.2020

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.

RE: Export CSV - gdimaria - 27.11.2020

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)

RE: Export CSV - Domoticatorino - 11.12.2020

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)