I want to send an e-mail it has a inside JQL

Erdem UÇAK

Hi community,

I have a JQL query and I want to send an e-mail this result of JQL query. I tried some method (scriptrunner, automation) but I could not resolve. I tried better excel automation and better excel export tool for this situation but I could not resolve again. Could you please help me Community

Kawan Diogo_ykfovu

Hi @Erdem UÇAK 

 

I created a script template that might be useful for your request.

It will fetch the JQL query results and include them in a .csv file, then send it to the desired email.

Note: This template only handles simple information. If you need to include more details, don't forget to reference the creation of the file and make edits in the same order.

 

import com.atlassian.core.util.FileUtils
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.mail.Email
import javax.mail.internet.MimeBodyPart
import javax.mail.internet.MimeMultipart
import javax.activation.DataHandler
import javax.activation.FileDataSource
import javax.mail.internet.MimeMessage
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.mail.server.SMTPMailServer

// Set TO
def recipientEmail = 'sample@email.com'

// Set Subject
def subject = 'Test mail'

// Set Body
def emailBody = 'Sample Email'

// Set the JQL
def jqlQuery = """project not in ("project1", "project2", "project3") AND status in (Resolved, Closed) AND createdDate >= -1w AND resolved >= -1w ORDER BY resolved DESC, created ASC"""

// Get user and services
def user = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def searchService = ComponentAccessor.getComponent(SearchService)
def mailServer = ComponentAccessor.getMailServerManager().defaultSMTPMailServer

if (!mailServer) {
log.warn("No server SMTP configured!")
return
}

// Validate JQL
def parseResult = searchService.parseQuery(user, jqlQuery)
if (!parseResult.isValid()) {
log.error("Invalid JQL: ${jqlQuery}")
return
}

// Execute JQL
def searchResult = searchService.search(user, parseResult.getQuery(), PagerFilter.getUnlimitedFilter())
def issues = searchResult.getResults()

if (issues.isEmpty()) {
log.warn("No issues found in JQL.")
return "No issues found."
}

// Generate content in CSV
def csvContent = new StringBuilder("Issue Key,Summary,Status,Created,Resolved\n")
issues.each { issue ->
csvContent.append("${issue.key},\"${issue.summary}\",${issue.status.name},${issue.created},${issue.resolutionDate}\n")
}

// Save file CSV
def fileName = "JQL_Result_${System.currentTimeMillis()}.csv"
def file = new File("/tmp/${fileName}")

try {
file.withWriter("UTF-8") { writer ->
writer.write(csvContent.toString())
}

// Send email with Attachment
createMessage(recipientEmail, subject, emailBody, "${file}")
log.info("Mensage created")
} catch (Exception e) {
log.error("Error while try create email: ${e.message}", e)
return "Error: ${e.message}"
} finally {
file.delete() 
log.info("File removed")
}

static createMessage(String to, String subject, String content, String filename) {
def mailServerManager = ComponentAccessor.mailServerManager
def mailServer = mailServerManager.defaultSMTPMailServer
def multipart = new MimeMultipart()
def body = new MimeBodyPart()
def mailAttachment = new MimeBodyPart()
def attachmentFile = new File(filename)

body.setContent(content, 'text/html; charset=utf-8')
mailAttachment.attachFile(attachmentFile)
multipart.addBodyPart(body)
multipart.addBodyPart(mailAttachment)

def email = new Email(to)
email.setSubject(subject)
email.setMultipart(multipart)
email.setMimeType('text/html')

def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}

return "Process done"

 

Let me know if this helped!

 

Best Regards