export csv

[akn]

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

[buuuudzik]

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

[admin]

Improved example

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

[buuuudzik]

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 thing

So in this subject also it is better

Thanks admin

[akn]

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

[akn]

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)

[Erwin van der Zwart]

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

[akn]

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

[admin]

Have you tried installing the updated package from here?
http://forum.logicmachine.net/showthread.php?tid=402

[akn]

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

[admin]

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.

[akn]

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

[Erwin van der Zwart]

Hi,

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

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

BR,

Erwin

[akn]

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!

[Erwin van der Zwart]

Hi,

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

BR,

Erwin

[akn]

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

[akn]

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

[admin]

Add From header after first Content-Type like this:

Code:

['Content-Type'] = 'text/html; charset=utf-8',
['From'] = '<' .. tostring(settings.from) .. '>',

[akn]

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