Two depending select customfields with OracleDB values

[evzensx]

Hello,
I'm trying to create two custom fields with Organization list and Contract list. Those values are situated in Oracle database. I want ScriptRunner to catch Organization name from database and put it on SR Organization customfield (it works) and then automatically show in SR Contracts customfield only Contracts list that depends to chosen Organization.
I created two behaviours and Organization fields works good, but I have some difficulties with Contract field: I get list of right values but can't put in on select field.

SR Organization behaviour code:

import groovy.sql.Sql
import java.sql.Driver
import com.onresolve.jira.groovy.user.FieldBehaviours
import com.onresolve.jira.groovy.user.FormField
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.fields.CustomField

def optionsManager = ComponentAccessor.getOptionsManager()
def fieldManager = ComponentAccessor.getCustomFieldManager()
def envFld = fieldManager.getCustomFieldObjectByName("SR Organization")
def fieldConfig = envFld.getRelevantConfig(getIssueContext())
def options = optionsManager.getOptions(fieldConfig)
def newSeqId = 100

def driver = Class.forName('oracle.jdbc.OracleDriver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", "DBUser") 
props.setProperty("password", "DBPassword")
def conn = driver.connect("jdbc:oracle:thin:@192.168.166.78:1521:scacc", props) 
def sql = new Sql(conn)

def results = sql.rows("select DISTINCT CLIENTNAME from V81C_SCACC.repdata where CONTRACTSIDE = 'BUYER' ORDER BY CLIENTNAME ASC")

def issueService = ComponentAccessor.getIssueService()
def issueInputParameters = issueService.newIssueInputParameters()
for (String env : results.CLIENTNAME) {

 if (options.findAll {it.value in env}) {
 println "${env} already in an option list"
 } else {
 def option = optionsManager.createOption(fieldConfig, null, newSeqId++, env)
 issueInputParameters.addCustomFieldValue(envFld.idAsLong, option.optionId.toString())
 println "${env} not in an option list. Added!"
 }
}

SR Contract behaviour code:

import groovy.sql.Sql
import java.sql.Driver
import com.onresolve.jira.groovy.user.FieldBehaviours
import com.onresolve.jira.groovy.user.FormField
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.fields.CustomField
import static com.atlassian.jira.issue.IssueFieldConstants.*
 
def optionsManager1 = ComponentAccessor.getOptionsManager()
def fieldManager1 = ComponentAccessor.getCustomFieldManager()
def envFld1 = fieldManager1.getCustomFieldObjectByName("SR Contract")
def envFld2 = getFieldByName("SR Contract")
def parentFld = fieldManager1.getCustomFieldObjectByName("SR Organization")
def fieldConfig1 = envFld1.getRelevantConfig(getIssueContext())
def options1 = optionsManager1.getOptions(fieldConfig1)
def newSeqId = 100

def driver = Class.forName('oracle.jdbc.OracleDriver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", "DBUser") 
props.setProperty("password", "DBPassword")
def conn = driver.connect("jdbc:oracle:thin:@192.168.166.78:1521:scacc", props) 
def sql = new Sql(conn)

Issue issue = getUnderlyingIssue()
MutableIssue mutableIssue = ComponentAccessor.getIssueManager().getIssueObject(issue.id);
def query = issue.getCustomFieldValue(parentFld)

def issueService1 = ComponentAccessor.getIssueService()
def issueInputParameters1 = issueService1.newIssueInputParameters()
def results1 = sql.rows("select CONTRACTNAME, CLIENTNAME from V81C_SCACC.repdata where CONTRACTSIDE = 'BUYER' ORDER BY CONTRACTNAME ASC").each {row->
 def arrElem = row.CONTRACTNAME.toString() + "("+row.CLIENTNAME.toString()+")" 
 if (options1.findAll {it.value in arrElem}) {
 println "${row.CONTRACTNAME} already in an option list"
 } else {
 def option1 = optionsManager1.createOption(fieldConfig1, null, newSeqId++, arrElem)
 issueInputParameters1.addCustomFieldValue(envFld1.idAsLong, option1.optionId.toString())
 println "${row.CONTRACTNAME} not in an option list. Added!"
 }
}
def optmap = []

def postresults = sql.rows("select CONTRACTNAME, CLIENTNAME from V81C_SCACC.repdata where CONTRACTSIDE = 'BUYER' AND CLIENTNAME LIKE '${query}' ORDER BY CONTRACTNAME ASC").each {row->
 def arrElem = row.CONTRACTNAME.toString() + "("+row.CLIENTNAME.toString()+")" 
 def optionsMap = options1.findAll {
 it.value in arrElem 
 }.collectEntries {
 [(it.optionId.toString()) : it.value.toString()]
 
 }

 optmap.add(optionsMap) /*mistake. result = array of arrays, need simple array of strings*/
}
println "optmap="+optmap
 envFld2.setFieldOptions(optmap)

 It looks like I get wrong value of $optmap. For now it is array of arrays of Contracts but setFieldOptions need just array of Contracts.

I stucked. Need help.

[evzensx · Accepted solution]

Solved. Returned to the original version with cascading lists. As a result, the addition of Organizations and Contracts in a single script.

Behaviour code:

import groovy.sql.Sql
import java.sql.Driver
import com.atlassian.jira.component.ComponentAccessor

def optionsManager = ComponentAccessor.getOptionsManager()
def fieldManager = ComponentAccessor.getCustomFieldManager()
def envFld = fieldManager.getCustomFieldObjectByName("SR OrgsContracts")
def fieldConfig = envFld.getRelevantConfig(getIssueContext())
def options = optionsManager.getOptions(fieldConfig)
def newSeqId = 1000
def driver = Class.forName('oracle.jdbc.OracleDriver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", "DBUser")
props.setProperty("password", "DBPassword")
def conn = driver.connect("jdbc:oracle:thin:@192.168.166.78:1521:scacc", props)
def sql = new Sql(conn)
def issueService = ComponentAccessor.getIssueService()
def issueInputParameters = issueService.newIssueInputParameters()
def org_results = sql.rows("select DISTINCT CLIENTNAME from V81C_SCACC.repdata ORDER BY CLIENTNAME ASC")

for (String env : org_results.CLIENTNAME) {
if (options.findAll {it.value in env}) {
println "${env} already in an option list of Organizations."
} else {
def option = optionsManager.createOption(fieldConfig, null, newSeqId++, env)
issueInputParameters.addCustomFieldValue(envFld.idAsLong, option.optionId.toString())
println "${env} not in an option list of Organizations. Added!"
}
}

def switcher = true //childOption exist and assigned to right parent
def contr_results = sql.rows("select CONTRACTNAME, CLIENTNAME from V81C_SCACC.repdata ORDER BY CLIENTNAME ASC").each {row ->
for (option in options.getRootOptions()){
if (option.toString() == row.CLIENTNAME.toString()) {
def childOptions = option.getChildOptions()
switcher = false
for (childOption in childOptions){
if (childOption.findAll {it.toString() in row.CONTRACTNAME.toString()}) {
println "${childOption} already in an option list of ${option}"
switcher = true
}
}
if (!switcher) {
def cOption = optionsManager.createOption(fieldConfig, option.optionId, newSeqId++, row.CONTRACTNAME.toString())
issueInputParameters.addCustomFieldValue(envFld.idAsLong, cOption.optionId.toString())
switcher = true
println "${row.CONTRACTNAME} (${row.CLIENTNAME}) not in an option list. Added to ${option}!"
}
}
}
}

[evzensx]

Changed script ending to

def optmap = [:]
def postresults = sql.rows("select CONTRACTNAME, CLIENTNAME from V81C_SCACC.repdata where CONTRACTSIDE = 'BUYER' AND CLIENTNAME LIKE '${query}' ORDER BY CONTRACTNAME ASC").each {row->
 def arrElem = row.CONTRACTNAME.toString() + "("+row.CLIENTNAME.toString()+")" 
 def optionsMap = options1.findAll {
 it.value in arrElem 
 }.collectEntries {
 [(it.optionId.toString()) : it.value.toString()]
 }
 optmap << optionsMap
}

envFld2.setFieldOptions(optmap)

And now it works. 

Also I delete SR Contract behaviour and added code from it to server-side script on SR Organization behaviour.

For now if I change SR Organization select field no changes on SR Contract applied until I update and click Edit issue again. After that right values shown.

I tried to use listener and catch moment of changing SR Organization. I've done it. But I need to call behaviour from listener or maybe make another query to select right values from DB and show them in SR Contract select?

Listener code:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.*
import com.atlassian.jira.model.ChangeGroup
import com.atlassian.jira.model.ChangeItem
import com.atlassian.jira.issue.history.ChangeItemBean

def optionsManager = ComponentAccessor.getOptionsManager()

def commentManager = ComponentAccessor.getCommentManager()
def customFieldManager = ComponentAccessor.getCustomFieldManager()
def issue = event.issue
def issueManager = ComponentAccessor.getIssueManager().getIssueObject(issue.id)
def user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()
def cf = customFieldManager.getCustomFieldObjectByName("SR Organization")
def pcf = customFieldManager.getCustomFieldObjectByName("SR Contract")
def cfv = cf.getValue(issue) 
String comment = "_SR Organization was changed to " + cfv

def changeHistoryManager = ComponentAccessor.getChangeHistoryManager()
def changeItems = changeHistoryManager.getChangeItemsForField(issue,'SR Organization')
def change = event?.getChangeLog()?.getRelated("ChildChangeItem")?.find {it.field == "SR Organization"}

def issueService = ComponentAccessor.getIssueService()
def issueInputParameters = issueService.newIssueInputParameters()
def loggedInUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def validationResult = issueService.validateUpdate(loggedInUser, issue.id, issueInputParameters)

if (change) {
 log.warn "Value changed from ${change.oldstring} to ${change.newstring}"
 commentManager.create(issue,user,comment,true)
}
else {
 log.warn event?.getChangeLog()?.getRelated("ChildChangeItem")?.find {it.field}
}

@Thanos Batagiannis [Adaptavist], @Nic Brough -Adaptavist-, @Jamie Echlin _ScriptRunner - The Adaptavist Group_ need your advise, guys! Please.