Help displaying database content in a JSD issue

[Gabe de Soto]

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.

[Peter-Dave Sheehan · Accepted solution]

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"){ ... } 

[Gabe de Soto]

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.

[Rodolfo So]

Hi 

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

Thanks

[Gabe de Soto]

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: