1 answer
For your requirement, there is no simple way to access the CSV file from the REST Endpoint unless it has already been added to an issue.
If you are trying to access the CSV, an attachment in an issue, you can try this working sample code below in the ScriptRunner console. It will query the details from the database, store it in a CSV file and add the CSV file as an attachment to an issue.
import com.adaptavist.hapi.jira.issues.Issues
import com.onresolve.scriptrunner.db.DatabaseUtil
def issue = Issues.getByKey('MOCK-1')
def sqlQuery = 'select * from sample_table'
final def filePath = '/tmp'
final def fileName = 'output.csv'
def dest = new File("${filePath}/${fileName}")
def fileWriter = new FileWriter(dest)
DatabaseUtil.withSql('local_db'){ sql ->
def row = sql.rows(sqlQuery) as List<Map>
fileWriter.write(generateCSV(row))
fileWriter.close()
sql.close()
}
issue.update {
addAttachment(dest)
}
dest.delete()
static String generateCSV(List<Map> list) {
def headers = list.stream().flatMap(map -> map.keySet().stream()).distinct().collect()
def stringBuilder = new StringBuilder()
headers.each {
stringBuilder.append(it).append(', ')
}
stringBuilder.append('\n')
list.each {map ->
headers.each {
stringBuilder.append(map[it]).append(', ')
}
stringBuilder.append('\n')
}
int last = stringBuilder.lastIndexOf('\n')
if(last > 0) {
stringBuilder.delete(last, stringBuilder.length())
}
stringBuilder.toString()
}
Please note that the sample working code above is not 100% exact to your environment. Hence, you will need to make the required modifications.
Below is a screenshot of the ScriptRunner Console:-
Once the code is executed, the CSV file will be added to the issue that has been specified, as shown in the screenshot below:-
Once the attachment is added to the issue, you can invoke via REST Endpoint using the sample working code below:-
import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import groovyx.net.http.HttpResponseDecorator
import groovyx.net.http.RESTClient
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
@BaseScript CustomEndpointDelegate delegate
getCSVFile { MultivaluedMap queryParams ->
def issueKey = queryParams.getFirst('issueKey')
def applicationProperties = ComponentAccessor.applicationProperties
final def baseUrl = applicationProperties.getString('jira.baseurl')
final def username = 'admin'
final def password = 'q'
final def headers = ['Authorization': "Basic ${"${username}:${password}".bytes.encodeBase64()}", 'Accept': 'application/json'] as Map
def http = new RESTClient(baseUrl)
http.setHeaders(headers)
def resp = http.get(path: "/rest/api/2/issue/${issueKey}") as HttpResponseDecorator
if (resp.status != 200) {
log.warn 'Commander did not respond with 200 for retrieving project list'
}
def issueJson = resp.data as Map
def dbPickerValue = issueJson['fields']['attachment']['content'] //Custom Field ID for DB Picker
Response.ok(new JsonBuilder(dbPickerValue).toPrettyString()).build()
}
Please note that the sample code above is not 100% exact to your environment. Hence, you will need to make the required modifications.
Below is a screenshot of the REST Endpoint configuration:-
When the REST Endpoint is invoked, it will display the CSV attachment link as shown in the screenshot below:-
Once you click on the link, the CSV file can be downloaded.
I hope this helps to solve your question. :-)
Thank you and Kind regards,
Ram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.