Need help with Script listener

[Aisha M]

Hi,

I have a listener in place for a particular functionality. This listener is meant to work on a select list of projects.

I now want the listener to get the list of projects from the database, instead for manually adding each time into the listener.

Can I please get some help on what needs to the appended to the existing listener in order for it to get the select list of projects from the SQL database.

[Tuncay Senturk _Snapbytes_ · Accepted solution]

Hello @Aisha M 

Do you need to get the list of projects from the database?

Wouldn't the script below help?

import com.atlassian.jira.component.ComponentAccessor

def projects = ComponentAccessor.getProjectManager().getProjectObjects()

[Aisha M]

Up until now, the projects for which the listener must work is separately mentioned on the listener page under 'projects".

But now, we want the listener to get a specific set of projects from the database table . . 

So I want to know how to specify in the listener for getting a particular set of projects from the db . . 

[Tuncay Senturk _Snapbytes_]

So, as far as I understand you need to connect the Jira database and get the result of the SELECT statement within your script linstener. Is that the case?

[Tuncay Senturk _Snapbytes_]

In either case (whether it is a Jira database or an external database) I'd recommend checking Script Runner's Connecting to databases link. 

[Aisha M]

@Tuncay Senturk _Snapbytes_ Below is a initial snipped of my code . . .I wanted to include in the script to fetch the results from the jira db we have.

[Tuncay Senturk _Snapbytes_]

Thanks for providing the script, but the mysterious part is not there.

I need to know where are the projects stored? Is it the Jira projects or do you want to listen to the projects which are listed in a database table that is created by you just for this purpose?

[Aisha M]

@Tuncay Senturk _Snapbytes_ Basically the script copies the Assignee name from TFS to the Assignee in JIRA . . . (Both tools are connected via a TFS4JIRA synchronizer) 

These projects are residing in JIRA  . . . But instead of adding the projects individually to the listener "project field' every time I create a new project, I want the listener to check the database & get the list of projects automatically from a table

I hope I'm making sense :O

[Tuncay Senturk _Snapbytes_]

Ok, let me ask you another question then.

Why don't you use a custom listener which is applied to Global (All projects) rather than selecting those projects manually?

[Aisha M]

That is because, this custom listener is limited to only a few (say 100) projects that is just synchronizing itself with information from the TFS . . 
So, over time, the number of projects could increase, so kinda becomes a hassle adding each project manually to the "select project list" . . . so looking for ways to gets this specific set of projects from the jira SQL database instead. . 

My theory is, I want the listener to first look for the sql table with the list of projects (based on a criteria), and then run this listener script only for those projects . . 

I'm so sorry for confusing so much :D I'm so desperate to find the code that could make this work 

[Tuncay Senturk _Snapbytes_]

Yeah, that's doable but still, there is a manual job. I mean you can get all projects list from the database table, but you would not know whether they needed to be used in that listener.

To make it clear, assume you have 250 projects and right now you are synching (say) 100 of them. The question is where should the listener know which 100?

If you have a table specifying those tables, it is easy to handle. Otherwise, you need to specify each project manually within the job (either by code or configuration)

I hope I was clear.

[Aisha M]

@Tuncay Senturk _Snapbytes_ Yes, I could have a table specifying about those set of projects alone . .  I need to know what the code should be inorder for the listener to fetch info from the table . . . at the moment, the listener is functional & working fine. .

[Tuncay Senturk _Snapbytes_]

Hello again,

I am not sure if it is a good idea (in terms of performance) to hit the database every time to get that list but this might be a solution.

First, it gets the projectId values from the database table and collect them in projectIds variable, then check the issue's project Id.

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 projectid FROM customtablename; 
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

List projectIds = new ArrayList()

try {
  StringBuffer sb = new StringBuffer()
  sql.eachRow(sqlStmt) {
    projectIds << "${it.projectid}" 
  }
}
finally {
  sql.close()
}

if (projectIds.contains(issue.getProjectObject().getID())) {
   do what you want here 
}

I haven't run the code but it might be a good point for your script.

Please let me know if you have further questions

[Aisha M]

Thank you thank you thank you for me helping out ! And that is definitely not a small snippet of code , that looks bigger than the actual code itself . . lol

So, I ll need to append this at the start of my script ? Will try and test it out 

[Tuncay Senturk _Snapbytes_]

Haha, that's 'for sure' not a small snippet. This code gets a connection from the Jira database, executes select to the table (you need to change table name and column name part), and eventually, it checks whether the changed issue's project is included.

So, your code will look like;

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
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.event.type.EventDispatchOption
import com.atlassian.jira.event.type.EventTypeManager;
import com.atlassian.jira.issue.index.IssueIndexingService
import com.atlassian.jira.util.ImportUtils;
import com.atlassian.jira.security.groups.GroupManager
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.CustomFieldManager

def issueManager = ComponentAccessor.issueManager
def issueService = ComponentAccessor.getIssueService()
def userManager = ComponentAccessor.getUserManager()
def CustomFieldManager = ComponentAccessor.getCustomFieldManager()

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

def sqlStmt = """
  SELECT projectid FROM customtablename; 
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

List projectIds = new ArrayList()

try {
  StringBuffer sb = new StringBuffer()
  sql.eachRow(sqlStmt) {
    projectIds << "${it.projectid}" 
  }
}
finally {
  sql.close()
}

if (projectIds.contains(event.issue.getProjectObject().getID())) {
  //your code here
  def cField = CustomFieldManager.getCustomFieldObject("customfield_1234")
  //log.info("Cfname: 1 ${cField.name}")
}

[Aisha M]

@Tuncay Senturk _Snapbytes_  Will test it out & update you if it works ! *Praying it does*

[Tuncay Senturk _Snapbytes_]

Hello

Even the code is not complete and it is hard to guess but I think the script does not get the list from the Jira database.

It works for the projects which are under the "Sub-domain" category in your Jira.

There is a comment-out section in which I believe they first added infra project keys manually "ISPE,IRBR, ...". Then I think they switched to collect those projects under one category which is also a good idea.

[Aisha M]

Oh okay ! I was just so confused as there seemed no SQL statements present to get any info from the db . . . Makes sense now . . 

[Tuncay Senturk _Snapbytes_]

Yes, the code above has the section (I believe it would work after a few touches) to get the list from the database

Below the code, there is a section to put your actual code.

if (projectIds.contains(event.issue.getProjectObject().getID())) {
  //your code here
  def cField = CustomFieldManager.getCustomFieldObject("customfield_1234")
  //log.info("Cfname: 1 ${cField.name}")
  def myIssue = event.issue
  //log.info("myIssue: 1 ${myIssue.key}")
  String cFieldValue = myIssue.getCustomFieldValue(cField)
  def issueObject = issueManager.getIssueObject(myIssue.key)
}

 I hope it helps

[Aisha M]

Also, can I improvise the below section of your code with additional SQL statements to find the correct projects

def sqlStmt = """
  SELECT projectid FROM customtablename; 
"""

 Maybe with something like this,

def sqlStmt = """
  USE <Dtatabase context>
  GO

SELECT projectid
FROM <table name>; 
"""

Will this modification make sense to the listener ? 

[Tuncay Senturk _Snapbytes_]

Yes, of course, you can use any SQL statement. Bear in mind that you have to use the same column name in your code which is returned from SQL statement.

SELECT projectid FROM ...

${it.projectid}

[Aisha M]

It our case its "Project id" right ? Also, what happens if the sql statement returns multiple columns . .  just curious

[Tuncay Senturk _Snapbytes_]

It can return multiple columns

SELECT projectid, second, third FROM ...

and you can get anyone as

${it.projectid}
${it.second}
${it.third}

[Aisha M]

Hi @Tuncay Senturk _Snapbytes_ , thank you soo sooo much helping out :):) Was able to get the scriptrunner to work for the projects from the DB for a particular domain.

Just one doubt, I'm trying to capture projects through a service desk form to add to the database along with their scrum board ID.

[Tuncay Senturk _Snapbytes_]

Hi @Aisha M 

I'm glad that you managed to get it worked.

I'd recommend using a number field to get the board ID value through a JSM ticket form.

Thanks

[Tuncay Senturk _Snapbytes_]

And of course, please let me know if you have any issues with the script

[Aisha M]

@Tuncay Senturk _Snapbytes_  So, instead of the WORK field , can we use a input field to maunally input the BOARD ID  (this will be used by admins ONLY, to add select projects to the DB) , and then add that value in the to the board ID of the table ?

[Tuncay Senturk _Snapbytes_]

@Aisha M 

Indeed, it is doable. You can easily use a number field and the admins can paste the Board ID and update the table accordingly. But in that case, you will not have the project ID values to compare.

I mean, if you want to change your script listener and check whether the issue is in the boardID values, I don't know if there is a way to do it. Anyway, let me know what exactly do you plan to do, and I will try to find out a solution.

Thanks

[Aisha M]

@Tuncay Senturk _Snapbytes_  We already have one to map particular project to a field value. That script has been added to the workflow. So, the db table is updated with the project key & a corresponding field value. (Have added the script above)

So, the service desk request form has three questions.

Project ID-

Team Operation - Add/Remove
Work - (Drop down field)

So, once the service desk form is submitted. The above values are added to the DB. If the operation is remove, it removes it from the table.

I wanna do the same, but instead of the custom field. I wanna add the board ID. But I want JIRA to know it is that particular projects board ID . Or maybe I want the script to pick that projects board ID from the project key & add it to DB. dunno which is the right way

[Tuncay Senturk _Snapbytes_]

Hmm, a project may have multiple boards, you can't pick a project's board id and add it to the DB. In this case, it is better for admin to fill in the id value manually.

[Aisha M]

@Tuncay Senturk _Snapbytes_ Yes, that is no problem . . . We can have have multiple boardID for a same project in the table. (there is another listener which picks the baordID from the table & applies it for the project) 

I want to manually input the BoardID (thro a number field) & the project key from the service desk form. And then use those values to populate the db table (the workflow script shared above, takes this info and updates the table in the db)

[Tuncay Senturk _Snapbytes_]

That's totally understandable, everyone does not need to be a code geek.

Below is the code how to get a number custom field value.

CustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager()
def boardIdField = customFieldManager.getCustomFieldObjectByName("Board ID")
def boardIdValue = issue.getCustomFieldValue(boardIdField) as int

[Aisha M]

@Tuncay Senturk _Snapbytes_  Thank you for saying that :) wow, thanks ! Is the below script alright ? I changed the code with what you had provided :)

[Tuncay Senturk _Snapbytes_]

At first glance, the code seems alright  :) 

[Aisha M]

@Tuncay Senturk _Snapbytes_  I get an error for the below line,

def boardIdValue = issue.getCustomFieldValue(boardIdField) as int

ERROR - Cannot find matching method. com.atlassian.jira.issue.MutableIssue#getCustomFieldValue(java.util.collection<com.atlassian.jira.issue.fields.Customfield>) Please check if declated type is correct and if method exists

[Tuncay Senturk _Snapbytes_]

Hi,

I think you used the one which is cast to MutableIssue.

Can you use  event.getIssue().getCustomFieldValue... instead?

[Tuncay Senturk _Snapbytes_]

Please let me know if it helped :) 

[Aisha M]

Sure . .  It says variable [event] is undeclared

[Tuncay Senturk _Snapbytes_]

I supposed it was a listener.

So, how do you define the issue variable?

From above, I can see that you use the below code.

def myIssue = event.issue
String cFieldValue = myIssue.getCustomFieldValue(cField)

That's why I asked you to use event.getIssue()

[Aisha M]

No no, this is a workflow post function script . . . this script does not have any issue variable defined. Hope I make sense :)

[Tuncay Senturk _Snapbytes_]

Oh ok, it should be because of this, sorry

def boardIdField = customFieldManager.getCustomFieldObjectByName("Board ID")

instead, please use

def boardIdField = customFieldManager.getCustomFieldObjectByName("Board ID")[0]

[Aisha M]

No no, its okay :)

Also, my script defines def cfm = ComponentAccessor.customFieldManager

So, should it be ,

def boardIdField = cfm.getCustomFieldObjectsByName("Board ID")[0]
def boardIdValue = event.getIssue().getCustomFieldValue(boardIdField) as int

or

def boardIdField = cfm.getCustomFieldObjectsByName("Board ID")[0]
def boardIdValue = issue.getCustomFieldValue(boardIdField) as int

UPDATE - THE BELOW CODE DID NOT GIVE ANY ERRORS NOW

def boardIdField = cfm.getCustomFieldObjectsByName("Board ID")[0]
def boardIdValue = issue.getCustomFieldValue(boardIdField) as int

Will try to submit a Service Desk request form & update u if it works :D

[Tuncay Senturk _Snapbytes_]

Fingers crossed :) 

[Aisha M]

@Tuncay Senturk _Snapbytes_ Its working perfectly !!! *happy dance* . .  Thank you thank you !!! 

[Tuncay Senturk _Snapbytes_]

Wow, brilliant!! 

@Aisha M I'm glad that it worked!

[Aisha M]

Just a dumb question, why do we add [0] while defining the BoardID field ? Does it signify something ?

[Tuncay Senturk _Snapbytes_]

That's not a dumb question, that's a great question.

getCustomFieldObjectsByName method returns an array as there can be multiple custom fields having the same name. Assuming you have only one, I used [0].

If you use getCustomFieldObject(10xxx) where 10xxx is the ID of the custom field, then you would not need to add that ([0])

Hope it is clear now.

[Aisha M]

Thank you ! That makes sense now :):) And super grateful to you for helping me every step of the question, even if its a super silly one :):)

[Tuncay Senturk _Snapbytes_]

As I mentioned before, everyone does not need to be good at reading APIs and coding scripts ;)

[Aisha M]

<CLEARED>

[Tuncay Senturk _Snapbytes_]

Yes could you please create a separate post, it became very hard to track.

Please do not forget to mention my name :) 

[Tuncay Senturk _Snapbytes_]

@Aisha M 

in case you did not get a notification :) 

[Aisha M]

I have removed the new question from this thread to keep it clean :):) I have tagged you in the new post