Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
Level
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Help displaying database content in a JSD issue

I'm new to JSD and currently evaluating ScriptRunner and have already found good use with the scripted fields.

Use case: We're a school district and if the reporter of an issue is a student, I'd like to be able to display the student's current schedule to the agents.

Current progress: I was able to create a scripted field that determines if the issue is a "Student Request". I was also able to successfully connect our student information system database (MS SQL Server) as a resource.

Need help: Figuring out what parts I need from ScriptRunner to do the following:
1. Query the database based on information in the issue for the specific student's schedule.
2. Generate a button in the opsbar titled "Student Schedule".
3. Clicking the button opens a dialog window with the rows of the query displayed in an html table.

It looks like fragments would add the button, but I'm not sure what else I need to get the button to actually do what I want.

Thanks in advance for any help or resources you can provide.

1 answer

1 accepted

0 votes
Answer accepted

That's a fairly complex set of requirements you have summarized in those 3 bullets.

For #3, yes, you'll want a web-item framgment to add the button and define the action.

See here: https://scriptrunner.adaptavist.com/5.7.1.1-p5/jira/fragments/WebItem.html#_dialogs_advanced

But the action will need to call a custom rest enpoint that returns the HTML table wrapped in a dialog element (see the example).

In your rest endpoint, you can query the database with the resource you've created

import com.onresolve.scriptrunner.db.DatabaseUtil 
def htmlTable = DatabaseUtil.withSql('sudentDb') { sql ->
sql.rows('select * from studentSchedule where studentUsername = :student', [student:studentUserName)
}

 You can convert the sql.rows result into html table with markup builder here is an example assumes many rows and columns:

import groovy.xml.MarkupBuilder
import com.onresolve.scriptrunner.db.DatabaseUtil

DatabaseUtil.withSql('dw_ocds') { sql ->
def rows = sql.rows('select lastName, firstname, email, username from employee where username = :searchUserName', [student:'p6s'])
def writer = new StringWriter()
def html = new MarkupBuilder(writer)
html.table() {
thead() {tr{
rows[0].keySet().each{ colHeader -> th(){ mkp.yield colHeader}}
}}
tbody() { rows.each { row->
tr() { row.each{col-> td() { mkp.yield col.value} }}
}}
}
writer.toString()
}

 If you are returning a single row and want to display it vertically:

import groovy.xml.MarkupBuilder
import com.onresolve.scriptrunner.db.DatabaseUtil

DatabaseUtil.withSql('dw_ocds') { sql ->
def rows = sql.rows('select lastName, firstname, email, username from employee where username = :student', [student:'p6s'])
def writer = new StringWriter()
def html = new MarkupBuilder(writer)
html.table() {
thead() {tr{
th() {mkp.yield 'Field'}
th() {mkp.yield 'Value'}
}}
tbody() { rows[0].each { attr ->
tr(){
td(){mkp.yield attr.key }
td(){mkp.yield attr.value }
}
}}
}
writer.toString()
}

You can but style or other html attributes inside each of the () next to html elements like:

td( style: "width: 20px; padding-left:3"){ ... } 

Thanks for the awesome response. Definitely makes sense, and I should be able to use this as a very decent starting off point. I appreciate it.

Hi 

 

Can you provide screenshots hows looks like when you run the scripts?

 

Thanks

Hi Rodolfo,

I ended up not trying to go with a pop-up, and instead just created a panel below the "Dates" panel that only appears if the reporter is a student.

Here's the result:

2020-11-09 10_26_57-2020_11_09_10_24_02_StudentSchedule.png

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Marketplace Apps & Integrations

New Cloud Apps Roundup - April 2021

Atlassian's marketplace partners have had a very productive start to 2021! Since our last roundup, our developer community has added over 160 new cloud apps to the Atlassian Marketplace to help you...

307 views 3 20
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you