get all category created in jira

[Karim Belhadj]

hello team

I would like to get all category in instance jira and make it in a select list , maybe i need to work with behaviour .

Have some one idea how to do it , how to get category without using project ?

regards

[Andrew]

Hi @Karim Belhadj ,

Maybe direct from DB?

SELECT * FROM projectcategory;

B.R.

[Karim Belhadj]

how can i use it in script runner ?

regards

[Andrew]

In scriptConsole:

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default");

def sqlStmt = """
SELECT cname FROM projectcategory;
"""
def result
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)

try {
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) {
sb << "${it.cname}; \n"
}
result = sb.toString()
//log.error sb.toString()
}
finally {
sql.close()
}

return result
 

 

[Karim Belhadj]

@Andrew   thank you but : it works and i get the categorie of jira instance , but now i would like to make theese categorie in a select list custom field : i write this code but the problem is how i will call result to be option in the select list .

Please find below my code.

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default");

def sqlStmt = """
SELECT cname FROM projectcategory;
"""
def result
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)

try {
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) {
sb << "${it.cname}; \n"
}
result = sb.toString()

//log.error sb.toString()
}

finally {
sql.close()
}

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.component.ComponentAccessor
def optionsManager = ComponentAccessor.getOptionsManager()
def ParticipationTypeField = getFieldByName("Categorie") // your field name here

//Participation Type Field Options
def ParticipationTypeCF = ComponentAccessor.getCustomFieldManager().getCustomFieldObject("customfield_10201"); // your field ID here
def ParticipationTypeConfig = ParticipationTypeCF.getRelevantConfig(getIssueContext());
def ParticipationTypeOptions = optionsManager.getOptions(ParticipationTypeConfig);

// Set the values inside the select list field
def ParticipationTypeMap = [null: "Noneeeeeee"]

ParticipationTypeMap += ParticipationTypeOptions.findAll {

it.value in [result, "c"]   ////////////////////// how can i make the value of the result in the option////////////////////////////////////

}.collectEntries {
[(it.optionId): it.value]
}

ParticipationTypeField.setFieldOptions(ParticipationTypeMap);

log.warn("***** ParticipationTypeOptions **** " + ParticipationTypeOptions);

regards

[Andrew]

Create textfield, in my example 'srselect'. Then create RestEnpoint:

import groovy.json.JsonOutput
import groovy.transform.BaseScript
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import javax.servlet.http.HttpServletRequest
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

@BaseScript CustomEndpointDelegate delegate

class position {
def value
def label
}

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default");

def sqlStmt = """
SELECT cname FROM projectcategory;
"""
def resultSql = []
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)

try {
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) {
resultSql.add(it.cname)
}

}
finally {
sql.close()
}

cats () { MultivaluedMap queryParams, String body,HttpServletRequest request ->

//def lista = ['1','2','3','4','5']
def result = []
def map = [:]
def i = 0
resultSql.each {
result << new position(value:it, label:it)
i++
}

map.put('items', result)

Response.ok(JsonOutput.toJson(map)).build()
}

And create 'behavior' with Initialiser:

getFieldByName("srselect").convertToSingleSelect([ 
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/cats",
query: false,
formatResponse: "general",
],
css: "max-width: 500px; width: 500px", 
])

Then mapping behavior to project.

For my it looks next:

B.R.

[Karim Belhadj]

hello @Andrew  thanks for response , but it still have a problem :

The text field works fine , but every time i add a new project category , and i go to create issue when i open the select list (srsselect) i find only the old options .. so i need every time to go to the endpoint rest and update the code to find after the right options .

I need your helps

Regards

[Andrew]

Change rest:

import groovy.json.JsonOutput
import groovy.transform.BaseScript
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import javax.servlet.http.HttpServletRequest
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

@BaseScript CustomEndpointDelegate delegate

class position {
def value
def label
}




cats () { MultivaluedMap queryParams, String body,HttpServletRequest request ->

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default");

def sqlStmt = """
SELECT cname FROM projectcategory;
"""
def resultSql = []
Connection conn = ConnectionFactory.getConnection(helperName);
Sql sql = new Sql(conn)

try {
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) {
resultSql.add(it.cname)
}

}
finally {
sql.close()
}


def result = []
def map = [:]
def i = 0
resultSql.each {
result << new position(value:it, label:it)
i++
}

map.put('items', result)

Response.ok(JsonOutput.toJson(map)).build()
}

B.R.

[Karim Belhadj]

Alexey Matveev i need your help please.