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
I have the following group addresses:

1/1 / ... - the actual value of meter 
1/2 / ... - the value of meter  at the beginning of the month
1/3 / ... - the value of consumption for the current month
1/4 / ... - the value of consumption for the previous month

I need a script that once a month, generated a csv file on a local storage. Another script on the scheduled or by event sent this file by email.

thank you in advance
Reply
#2
(07.04.2017, 09:11)akn Wrote: I have the following group addresses:

1/1 / ... - the actual value of meter 
1/2 / ... - the value of meter  at the beginning of the month
1/3 / ... - the value of consumption for the current month
1/4 / ... - the value of consumption for the previous month

I need a script that once a month, generated a csv file on a local storage. Another script on the scheduled or by event sent this file by email.

thank you in advance

This can be for start:

Once a month:
Code:
actual_value = grp.getvalue('1/1/1')
start_month = grp.getvalue('1/1/1')
current_month = grp.getvalue('1/1/1')
previous_month = grp.getvalue('1/1/1')
labels = "actual_value, start_month, current_month, previous_month/r/n"
meter = labels .. actual_value .. "," .. start_month .. "," .. current_month .. "," .. previous_month .. "/r/n"

storage.set('meter', meter)


Scheduled:
Code:
-- read csv report file
data = storage.get('meter')

-- send file as report.csv with text/csv mime type
res, err = mailattach('someone@example.com', 'Report for current month', 'CSV file attached', 'report.csv', data, 'text/csv')
Reply
#3
Improved example Wink

Code:
rows = {}

-- from 1/1/1 to 1/1/30
addrstart = 1
addrend = 30

rows[ #rows + 1 ] = 'name,actual_value,start_month,current_month,previous_month'

for i = addrstart, addrend do
  actual_value_obj = grp.find('1/1/' .. i)
  name = actual_value_obj.name
  actual_value = actual_value_obj.value
  start_month = grp.getvalue('1/2/' .. i)
  current_month = grp.getvalue('1/3/' .. i)
  previous_month = grp.getvalue('1/4/' .. i)
  
  rows[ #rows + 1 ] = name .. ',' .. actual_value .. ',' .. start_month .. ',' .. current_month .. ',' .. previous_month
end

csv = table.concat(rows, '\r\n')
Reply
#4
Thumbs Up 
Perfect way, the best solution, table.concat() in right place can do miracle. I didn't know. From one hour ago I had a big problem with export to .csv tables(on Load Balancer) with 100k rows but with 2 table.concat() functions it is not a thingWink

So in this subject also it is better Smile

Thanks admin
Reply
#5
data = storage.get('meter')
res, err = mailattach('someone@example.com', 'Report for current month', 'CSV file attached', 'report.csv', data, 'text/csv')

I have error,

send mail 10.04.2017 18:47:11
* arg: 1
* nil
* arg: 2
* string: unknown type in record hdr
Reply
#6
How can I optimize this script and extend it to all objects?

actual_value = grp.getvalue('3/0/..')
start_month = grp.getvalue('3/1/..')
current_month = grp.getvalue('3/2/..')

current_month = actual_value - start_month
grp.write('3/2/..', current_month)
Reply
#7
Hi,

Try this:

for i = 0, 255, 1 do
   actual_value = grp.getvalue('3/0/' .. i)
   start_month = grp.getvalue('3/1/' .. i)
   current_month = actual_value - start_month
   grp.update('3/2/' .. i, current_month)
   os.sleep(0.2)
end

BR,

Erwin
Reply
#8
Thanks guys, but how about this problem?

data = storage.get('meter')
res, err = mailattach('someone@example.com', 'Report for current month', 'CSV file attached', 'report.csv', data, 'text/csv')

I have error,

send mail 10.04.2017 18:47:11
* arg: 1
* nil
* arg: 2
* string: unknown type in record hdr
Reply
#9
Have you tried installing the updated package from here?
http://forum.logicmachine.net/showthread.php?tid=402
Reply
#10
1. Admin does not work. I also tried to change the security settings ("sslv23", "tlsv1", "tlsv11" or "tlsv12")
Using 25 port without security settings, everything works!

2. Why does this script send two emails?

-- send an e-mail with attachment
function mailattach(to, subject, message, filename, filedata, mimetype)
-- make sure these settings are correct
local settings = {
-- "from" field, only e-mail must be specified here
from = 'user@domen.com',
-- smtp server
server = 'mail.apollo.lv',
-- smtp server port
port = 25,


}

  local smtp = require('socket.smtp')

  if type(to) ~= 'table' then
    to = { to }
  end

  for index, email in ipairs(to) do
    to[ index ] = '<' .. tostring(email) .. '>'
  end

  -- escape double quotes in file name
  filename = filename:gsub('"', '\\"')

  -- message headers and body
  settings.source = smtp.message({
    headers = {
      to = table.concat(to, ', '),
      subject = subject,
    },
    body = {
      {
        headers = {
          ['Content-Type'] = 'text/html; charset=utf-8',
        },
        body = mime.eol(0, message)
      },
      {
        headers = {
          ['Content-Type'] = mimetype or 'text/plain',
          ['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
          ['Content-Transfer-Encoding'] = 'BASE64',
        },
        body = ltn12.source.chain(
          ltn12.source.string(filedata),
          ltn12.filter.chain(mime.encode('base64'), mime.wrap())
        )
      }
    }
  })

  -- fixup from field
  settings.from = '<' .. tostring(settings.from) .. '>'
  settings.rcpt = to

  return smtp.send(settings)
end
Reply
#11
Are you sending to a different e-mail or to the same one? Just tested it with different e-mails and only one message is received.
Reply
#12
I adjusted the script so that users can change the settings themselves. I want to add possibility to send a copy to another email address.

--------- Event-based----------------

-- read csv report file
data = storage.get('csv')
userdatato = grp.getvalue('1/6/0')

-- send file as report.csv with text/csv mime type
res, err = mailattach(userdatato, '!!!!', 'CSV file attached', 'report.csv', data, 'text/csv')

--------- Common functions ----------------

-- send an e-mail with attachment
function mailattach(to, subject, message, filename, filedata, mimetype)
-- make sure these settings are correct

local settings = {

-- "from" field, 255 byte data type
from = grp.getvalue('1/6/1'),
-- smtp server, 255 byte data type
server = grp.getvalue('1/6/2'),
-- smtp server port, 2 byte unsigned data type
port = grp.getvalue('1/6/3'),


}

local smtp = require('socket.smtp')

if type(to) ~= 'table' then
to = { to }
end

for index, email in ipairs(to) do
to[ index ] = '<' .. tostring(email) .. '>'
end

-- escape double quotes in file name
filename = filename:gsub('"', '\\"')

-- message headers and body
settings.source = smtp.message({
headers = {
to = table.concat(to, ', '),
subject = subject,
},
body = {
{
headers = {
['Content-Type'] = 'text/html; charset=utf-8',
},
body = mime.eol(0, message)
},
{
headers = {
['Content-Type'] = mimetype or 'text/plain',
['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
['Content-Transfer-Encoding'] = 'BASE64',
},
body = ltn12.source.chain(
ltn12.source.string(filedata),
ltn12.filter.chain(mime.encode('base64'), mime.wrap())
)
}
}
})

-- fixup from field
settings.from = '<' .. tostring(settings.from) .. '>'
settings.rcpt = to

return smtp.send(settings)
end
Reply
#13
Hi,

To send to multiple addresses you can simply use a table.

to = {'mail1@domain.com', 'mail2@domain.com', 'mail3@domain.com'}

BR,

Erwin
Reply
#14
Thank you, but I want то sо that users can change the settings themselves.

For example:

-- "to" field, 255 byte data type
to1 = grp.getvalue('1/6/3')
-- "to" field, 255 byte data type
to2 = grp.getvalue('1/6/4')
-- "to" field, 255 byte data type
to3 = grp.getvalue('1/6/5')

to = {to1, to2, to3}

But it does not work!

Thank you, but I want sо that users can change the settings themselves.

For example:

-- "to" field, 255 byte data type
to1 = grp.getvalue('1/6/3')
-- "to" field, 255 byte data type
to2 = grp.getvalue('1/6/4')
-- "to" field, 255 byte data type
to3 = grp.getvalue('1/6/5')

to = {to1, to2, to3}

But it does not work!
Reply
#15
Hi,

Just tested your sample and it works for me, did you try to log the table 'to' ?

BR,

Erwin
Reply
#16
Now it works!

--------- Event-based----------------

-- read csv report file
data = storage.get('csv')

-- "to" field, 255 byte data type
to1 = grp.getvalue('1/6/3')
-- "to" field, 255 byte data type
to2 = grp.getvalue('1/6/4')
-- "to" field, 255 byte data type
to3 = grp.getvalue('1/6/5')

res, err = mailattach('subject', 'CSV file attached', 'report.csv', data, 'text/csv')

--log(res, err)



--------- Common functions ----------------


function mailattach (subject, message, filename, filedata, mimetype)

local settings = {

-- "from" field, 255 byte data type
from = grp.getvalue('1/6/1'),
-- smtp server, 255 byte data type
server = grp.getvalue('1/6/2'),
-- smtp server port, 2 byte unsigned data type
port = grp.getvalue('1/6/3'),

}


local smtp = require('socket.smtp')

to = {to2, to1, to3}

for index, email in ipairs(to) do
to[ index ] = '<' .. tostring(email) .. '>'
end

-- escape double quotes in file name
filename = filename:gsub('"', '\\"')

-- message headers and body
settings.source = smtp.message({
headers = {
to = table.concat(to, ', '),
subject = subject,
},
body = {
{
headers = {
['Content-Type'] = 'text/html; charset=utf-8',
},
body = mime.eol(0, message)
},
{
headers = {
['Content-Type'] = mimetype or 'text/plain',
['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
['Content-Transfer-Encoding'] = 'BASE64',
},
body = ltn12.source.chain(
ltn12.source.string(filedata),
ltn12.filter.chain(mime.encode('base64'), mime.wrap())
)
}
}
})

-- fixup from field
settings.from = '<' .. tostring(settings.from) .. '>'
settings.rcpt = to

return smtp.send(settings)
end
Reply
#17
I have a problem sending to gmail.com, I receive back error.
I d'not have problem with another email address!

----
This is the mail system at host mail.name.com.

I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.

For further assistance, please send mail to postmaster.

If you do so, please include this problem report. You can
delete your own text from the attached returned message.

The mail system

<name@gmail.com>: host gmail-smtp-in.l.google.com[74.125.131.26] said:
550-5.7.1 [82.193.64.9 11] Our system has detected that this message
is not 550-5.7.1 RFC 5322 compliant: 550-5.7.1 'From' header is missing.
550-5.7.1 To reduce the amount of spam sent to Gmail, this message has been
550-5.7.1 blocked. Please visit 550-5.7.1
https://support.google.com/mail/?p=RfcMe...nCompliant 550 5.7.1 and
review RFC 5322 specifications for more information. 16si5852631lfb.292 -
gsmtp (in reply to end of DATA command)

Final-Recipient: rfc822; name@gmail.com
Original-Recipient: rfc822;name@gmail.com
Action: failed
Status: 5.7.1
Remote-MTA: dns; gmail-smtp-in.l.google.com
Diagnostic-Code: smtp; 550-5.7.1 [82.193.64.9 11] Our system has detected
that this message is not 550-5.7.1 RFC 5322 compliant: 550-5.7.1 'From'
header is missing. 550-5.7.1 To reduce the amount of spam sent to Gmail,
this message has been 550-5.7.1 blocked. Please visit 550-5.7.1
https://support.google.com/mail/?p=RfcMe...nCompliant 550 5.7.1 and
review RFC 5322 specifications for more information. 16si5852631lfb.292 -
gsmtp
---
Reply
#18
Add From header after first Content-Type like this:
Code:
['Content-Type'] = 'text/html; charset=utf-8',
['From'] = '<' .. tostring(settings.from) .. '>',
Reply
#19
(24.08.2017, 10:05)admin Wrote: Add From header after first Content-Type like this:
Code:
['Content-Type'] = 'text/html; charset=utf-8',
['From'] = '<' .. tostring(settings.from) .. '>',



Does not help! 
I correctly added line in the script?



-- user function library


function mailattach (subject, message, filename, filedata, mimetype)

  local settings = {
 
    -- "from" field, 255 byte data type
    from = grp.getvalue('7/7/3'),
    -- smtp server, 255 byte data type
    server = grp.getvalue('7/7/4'),
    -- smtp server port, 2 byte unsigned data type
    port = grp.getvalue('7/7/5'),
      
  }
  
 
    local smtp = require('socket.smtp')

--if type(to) then
  to = {to1, to2}
--end

  for index, email in ipairs(to) do
   to[ index ] = '<' .. tostring(email) .. '>'
 end

  -- escape double quotes in file name
  filename = filename:gsub('"', '\\"')

  -- message headers and body
  settings.source = smtp.message({
    headers = {
     to = table.concat(to, ', '),   
      subject = subject,
    },
    body = {
      {
        headers = {
          ['Content-Type'] = 'text/html; charset=utf-8',
          ['From'] = '<' .. tostring(settings.from) .. '>',  
        },
        body = mime.eol(0, message)
      },
      {
        headers = {
          ['Content-Type'] = mimetype or 'text/plain',
          ['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
          ['Content-Transfer-Encoding'] = 'BASE64',
        },
        body = ltn12.source.chain(
          ltn12.source.string(filedata),
          ltn12.filter.chain(mime.encode('base64'), mime.wrap())
        )
      }
    }
  })

  -- fixup from field
  settings.from = '<' .. tostring(settings.from) .. '>'
  settings.rcpt = to

  return smtp.send(settings)
end
Reply


Forum Jump: