Automatically set a custom field select list

Gerhard Degel October 19, 2015

HI everyone,

I have written a script which should first query a database and extract a certain value. Then a custom field exists in JIRA which a single select list with exactly the same values. By comparing these two values I want to set in the end the custom field value in JIRA to the same I extracted from the external database.

What I can say is that the "customer" object from my SQL query is being retrieved correctly because another functionality based on these queries is working well.

JIRA Version 6.4.5

Script Runner 3.0.16

 

Unfortunately the following script is not working:

 

import com.atlassian.crowd.embedded.api.User
import groovy.sql.Sql
import java.sql.Driver
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.user.util.UserManager
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.customfields.manager.OptionsManager


//Create and setup a database connection object which allows to query customer DB
def driver = Class.forName('net.sourceforge.jtds.jdbc.Driver').newInstance() as Driver

def props = new Properties()
props.setProperty("user", "*******")
props.setProperty("password", "***********")

def conn = driver.connect("jdbc:jtds:sqlserver://localhost:1433/Footprints", props)
def sql = new Sql(conn)
//end database connection

//an issue object is needed to proceed
MutableIssue currentIssue = issue

//the user who has raised the issue needs to be identified; Therefore the email address needs to be retrieved which
//will be used afterwards within the SQL query "customer" to retrieve the customer value from customer DB 
JiraAuthenticationContext=ComponentAccessor.getJiraAuthenticationContext()
loggedUser=JiraAuthenticationContext.getUser()
def reporterEmail = loggedUser.getEmailAddress()

//the first query retrieves the customer from customer DB
def customer = sql.firstRow("SELECT FIRMA FROM FIRMA, USER WHERE FIRMA.ID = USER.Firmenid AND USER.Mailadresse = ?",[reporterEmail])

//Since the return object "customer" is a Groovy Map which contains only one k/v pair it needs to be addressed 
//to retrieve only the value string customer which will be used in the 2nd query to retrieve the corresponding supportagent
def supportagent = sql.firstRow("SELECT SLA FROM FIRMA WHERE FIRMA.Firma = ?",[customer.getAt(0).toString()])

//sql connection close
sql.close()

//an autoassignment of the ticket shall be processed to the support agent based on the email address of the
//support agent which is being retrieved from the SQL query "supportagent" from customer DB 
switch (supportagent.getAt(0).toString()) {
    case 'a@a.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('a'))
        break
    case 'b@b.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('b'))
        break
    case 'c@c.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('c'))
        break
    case 'd@d.de':
        currentIssue.setAssignee(ComponentAccessor.getUserUtil().getUser('d'))
        break
    default:
        break
}
//the "Customer" selection list should be set to the corresponding value retrieved by the SQL query "customer" based on the user
//who opens the ticket.
ComponentManager componentManager = ComponentManager.getInstance()
CustomFieldManager customFieldManager = componentManager.getCustomFieldManager()
def cf = customFieldManager.getCustomFieldObjects(currentIssue).find {it.name == 'Customer'}
def fieldConfig = cf.getRelevantConfig(issue)
def value = ComponentAccessor.optionsManager.getOptions(fieldConfig)?.find { it.toString() == customer.getAt(0).toString() }
currentIssue.setCustomFieldValue(cf, value)

 

Every help is highly appreciated.

thx

Gerhard

1 answer

0 votes
JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 20, 2015

Looks broadly correct. Can you explain how it's not working. Check the logs for errors. Add some log messages so you can see if value is correct... it should be an Option object.

Also see https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events