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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.