Get email attachments and upload to specific server

Script: uploadFile.rb

#!/opt/zammad/bin/ruby

Steps: Get max ticket number -> search new tickets -> Get correct email attachment -> upload to server

require ‘zammad_api’

require ‘mysql2’

require ‘net/sftp’

File to save the maximum ticket number queried last time, We will search from this ticket number

ticket_file_name = “ticket.txt”

Minimum ticket number (Last asked maximum ticket number)

min_ticket = File.read(ticket_file_name)

mysql connection to Zammad database to get maximum ticket number

mysql_client = Mysql2::Client.new(:host => “localhost”, :database => “zammad”, :username => “zammad”, :password => “mysql_zammad_password”)

Get maximum ticket number from database

mysql_result = mysql_client.query(“select max(id) max_ticket from tickets”)

Maximum ticket number

max_ticket=mysql_result.first[‘max_ticket’]

Store maximum ticket number in ticket file

File.open(ticket_file_name, “w”) {|file| file.puts max_ticket}

Set up Zammad client for api call

zammad_client = ZammadAPI::Client.new(

url: ‘http://localhost:3000/’,

user: ‘Zammad_user_name’,

password: ‘Zammad_password’

)

Form ticket search query

ticket_search_query=“customer.email:accounts@martialvolvocars.in and id:>” + min_ticket.to_s.strip + " and id:<=" + max_ticket.to_s

Get tickets by search query

tickets = zammad_client.ticket.search(query: ticket_search_query)

p “Tickets count=#{tickets.count} — Search Query=#{ticket_search_query}”

Loop through the tickets

tickets.each { |ticket|

    # Get ticket article as it contains email attachments
    articles = ticket.articles

    # Loop through articles
    articles.each { |article|

            # Loop through all article attachments
            article.attachments.each { |attachment|

                    # Check if file match the condition
                    if attachment.filename.include? ".pdf"

                            p "Attachment=#{attachment.filename} --- Ticketid=#{ticket.id} --- TicketOwner=#{ticket.owner.inspect}"

                            # Open local file and write content into the file
                            File.open(attachment.filename, 'wb') do |file|
                                    file.write(attachment.download)
                            end

                            # Upload file to 172 server using sftp  
                            Net::SFTP.start('SFTP_IP_ADDRESS', 'SFTP_USER_NAME', :password => 'SFTP_PASSWORD') do |sftp|
                                    sftp.upload!("./"+attachment.filename, "./FOLDER_NAME/"+attachment.filename)
                            end
                    end
            }
    }

}

Sorry but could you update your first article to a shape that allows other people to read it?
Is it a question, whats the goal?

Hi, I have searched forum for following problem, but did not find it, So I have uploaded the code what I wrote by my own.

Business Case: Copy the new invoice from specific customer (which we are getting as email) to ETL server for processing

Goal: Get the attachment from new tickets being created for specific customer and upload to different server.

1 Like
#!/opt/zammad/bin/ruby

# Author: Arun Kumar Swain
# Date: 20-October-2020
# Desc: Upload latest parts grn to ETL server using sftp
# Steps: Get max ticket number -> search new tickets -> Get correct email attachment -> upload to server


require 'zammad_api'
require 'mysql2'
require 'net/sftp'

# File to save the maximum ticket number queried last time, We will search from this ticket number
ticket_file_name = "ticket.txt"

# Minimum ticket number (Last asked maximum ticket number)
min_ticket = File.read(ticket_file_name)

#mysql connection to Zammad database to get maximum ticket number
mysql_client = Mysql2::Client.new(:host => "localhost", :database => "zammad", :username => "zammad", :password => "Zammad")

#Get maximum ticket number from database
mysql_result = mysql_client.query("select max(id) max_ticket from tickets")

# Maximum ticket number
max_ticket=mysql_result.first['max_ticket']

# Store maximum ticket number in ticket file
File.open(ticket_file_name, "w") {|file| file.puts max_ticket}

# Set up Zammad client for api call
zammad_client = ZammadAPI::Client.new(
  url:      'http://localhost:3000/',
  user:     'xxxxx@xxxxxxx.in',
  password: 'PASSWORD'
)

# Form ticket search query
ticket_search_query="customer.email:customer_name@domain.com and id:>" + min_ticket.to_s.strip + " and id:<=" + max_ticket.to_s

# Get tickets by search query
tickets = zammad_client.ticket.search(query: ticket_search_query)

p "Tickets count=#{tickets.count} --- Search Query=#{ticket_search_query}"

# Loop through the tickets
tickets.each { |ticket|

        #Get ticket article as it contains email attachments
        articles = ticket.articles

        #Loop through articles
        articles.each { |article|

                #Loop through all article attachments
                article.attachments.each { |attachment|

                        # Check if file match the condition
                        if attachment.filename.include? ".csv"

                                p "Attachment=#{attachment.filename} --- Ticketid=#{ticket.id} --- TicketOwner=#{ticket.owner.inspect}"

                                # Open local file and write content into the file
                                File.open(attachment.filename, 'wb') do |file|
                                        file.write(attachment.download)
                                end

                                # Upload file to ETL server using sftp  
                                Net::SFTP.start('IP_ADDRESS', 'USER_NAME', :password => 'PASSWORD') do |sftp|
                                        sftp.upload!("./"+attachment.filename, "./partsGRN/"+attachment.filename)
                                end
                        end
                }
        }
}

Please use operators in capital letter:
ticket_search_query="customer.email:customer_name@domain.com AND id:>" + min_ticket.to_s.strip + " AND id:<=" + max_ticket.to_s

Hi @ArunKumarSwain

could you please use GitHub for your code? Also, a README.md explaining how this is used would be beneficial. Not everybody here reads Ruby or code at all…

Thanks!

Martin

1 Like

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.