Jira Service Desk automation with Scriptrunner

Question

We recently created a Jira Service Desk, where customers create tickets. Created text field (Product Id) where customers enter the product id, in the backend I created two text fields (Product Name, Start Date). I just whenever customers enter the productId, it should show the product name and date from the backend to the service desk agent. I created a database an external database connection in scriptrunner and I can query the DB. 
I copied a script from online and try to make it work, but it is not working, I'm not sure what I'm doing wrong. To be honest, I don't have any about the groovy, so I just copied everything online. I'm also getting a warning from line 8 (screenshot). Can somebody help me please, thank you!

Custom Fields name: Product Id, Product Name, Start Date

Database Fields name: productId, productName, startDate

DatabaseName: Product

Poolname from resources: Product_Pool

Script

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.atlassian.jira.event.issue.IssueEvent
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import java.sql.Timestamp

def event = event as IssueEvent
def issue = event.issue
def customFieldObjects = ComponentAccessor.customFieldManager.getCustomFieldObjects(issue)

def valueCF = customFieldObjects.findByName("Product Id")
def productName = customFieldObjects.findByName("Product Name")
def startDateCF = customFieldObjects.findByName("Start Date")

def value = issue.getCustomFieldValue(valueCF)
DatabaseUtil.withSql('Product_Pool') { sql ->
def row = sql.firstRow("SELECT * FROM Prdouct WHERE productId = ?", value)
if (row) {
def startDate = row.'CreateDate' as Timestamp
def startDateValue = """${startDate.time <= System.currentTimeMillis() ?
"{color:#d04437}${startDate.format('yyyy-MM-dd')}{color}" :
"{color:#00875A}${startDate.format("yyyy-MM-dd")}{color}"}""".toString()

productName.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(productName), row.'ProrductName'), new DefaultIssueChangeHolder())
startDateCF.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(startDateCF), startDateValue), new DefaultIssueChangeHolder())

}
}

Error log;

2020-06-16 12:01:40,115 ERROR [runner.AbstractScriptListener]: *************************************************************************************
2020-06-16 12:01:40,116 ERROR [runner.AbstractScriptListener]: Script function failed on event: com.atlassian.jira.event.issue.IssueEvent, file: null
org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object '2020-05-01' with class 'java.sql.Date' to class 'java.sql.Timestamp'
	at Script126$_run_closure1.doCall(Script126.groovy:20)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSql(AbstractDbConnectionManager.groovy:63)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager$withSql$1.callCurrent(Unknown Source)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager$withSql$1.callCurrent(Unknown Source)
	at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSqlTracked(AbstractDbConnectionManager.groovy:92)
	at com.onresolve.scriptrunner.db.DbConnectionManager$withSqlTracked$1.call(Unknown Source)
	at com.onresolve.scriptrunner.db.DbConnectionManager$withSqlTracked$1.call(Unknown Source)
	at com.onresolve.scriptrunner.db.DatabaseUtil.withSql(DatabaseUtil.groovy:22)
	at com.onresolve.scriptrunner.db.DatabaseUtil$withSql.call(Unknown Source)
	at Script126.run(Script126.groovy:17)

Shah Baloch · Answer

Operational

* Impact if not implemented
* Type of change
* Business Uni  Table
* Communication plan (All Staff, or IT or External)

Update.....

The following script is working fine, whenever I type the FullName of the customer it'll display the desired results.

The only issue I'm having that it is not working with a Database Picker Custom Field. Like I created a Database Picker custom field which is showing customers names in the dropdown, whenever I pick a name from the list it won't show any data when I use a normal custom text field and type customer name, it works perfectly.

2 questions I have, to make this more useful for us.

1- How can you make the Database Picker custom field works with this script?

2- How I can make an autocomplete box? Like whenever customers start typing it should show the matching results where users can pick up the name instead of typing the complete name.

The reason I need one of those above fields because behaviour condition won't let the users to create a ticket unless they won't provide the correct name and some records contain 3 names, like first, middle and last name, I'll be doing the SQL part where I can put a clause that it displays the middle name too if customers have a middle name, but ticket creator always won't know that there are three names or 2. So a dropdown or autocomplete will be very helpful. I search online about it didn't find any Groovy script regarding the autocomplete. I'm not sure that I used the right wording for search.

import com.atlassian.jira.component.ComponentAccessor

import com.onresolve.scriptrunner.db.DatabaseUtil

import com.atlassian.jira.event.issue.IssueEvent

import com.atlassian.jira.issue.ModifiedValue

import com.atlassian.jira.issue.util.DefaultIssueChangeHolder

import java.sql.Timestamp

def event = event as IssueEvent

def issue = event.issue

def customFieldObjects = ComponentAccessor.customFieldManager.getCustomFieldObjects(issue)

def valueCF = customFieldObjects.findByName("Product Owner")

def productName = customFieldObjects.findByName("Product Name")

def productId = customFieldObjects.findByName("Product Id")

def value = issue.getCustomFieldValue(valueCF)

DatabaseUtil.withSql('Product_Pool ') { sql ->

def row = sql.firstRow("""SELECT Product.*, concat(FirstName,' ', LastName) as FullName, CustomerId
from Customer
left join Product on PersonId = CustomerId
having FullName like ?""" value)

if (row) {

productName.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(productName), row.'ProductName'), new DefaultIssueChangeHolder())

productId.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(productId), row.'ProductId'), new DefaultIssueChangeHolder())

}

I really appreciate your help.

Shah Baloch · Answer

Thank you @Alejandro Suárez García for your response. I made it works. The team wants me to replace the Product Id custom field to the "Owner". I mean we want to have custom field "Owner" in frontend. Like when the requester enters the owner's name and in the backend, it should display product id and product name. I could simply switch the fields but the problem is there are two tables in the database, product, and customer, the product contains product information and the customer table have customer information, like firstName, LastName, id etc. I can query the database by using "JOIN" with a condition customerId=id and see both product and customer information.

How can I map the first and last name with the "Owner" field as a name?
How can I use the "JOIN" clause in the groovy script? Like right now it is ("SELECT * FROM Product WHERE productId = ?", value), how I can use name instead of product id in condition?

I really appreciate your help.

Alejandro Suárez García · Answer

@Hi@Shah Baloch here you go. There is one line commented in the code that you must change to retrieve the data.

Put this code in a Scripted postfuntion in the create transition of your workflow(s). Move it to the last place.

import com.atlassian.jira.bc.issue.IssueService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.IssueInputParameters
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.fields.Field
import com.atlassian.jira.timezone.TimeZoneManager
import com.atlassian.jira.user.ApplicationUser
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.onresolve.scriptrunner.parameters.annotation.FieldPicker
import com.onresolve.scriptrunner.parameters.annotation.ShortTextInput
import com.onresolve.scriptrunner.parameters.annotation.UserPicker
import groovy.sql.GroovyRowResult

import java.text.SimpleDateFormat

@ShortTextInput(label = "Database Resource Name", description = "Write the DB Resource name configured in ScriptRunner'Resources' (I asume that is 'Product_Pool' but im not sure)")
String resource
@FieldPicker(label = "Product Id CustomField", description = "Select the Product ID CustomField")
Field productIdField
@FieldPicker(label = "Product Name CustomField", description = "Select the Product Name CustomField")
Field productNameField
@FieldPicker(label = "Start Date CustomField", description = "Select the Product ID CustomField")
Field startDateField
@UserPicker(label = "Admin User", description = "User with edit permissions in the project, usually a 'dummy' admin user")
ApplicationUser admin

DatabaseUtil.withSql(resource) { sql ->

IssueService issueService = ComponentAccessor.getIssueService()
    CustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager()
    CustomField cfProductId = customFieldManager.getCustomFieldObject(productIdField.getId())
    CustomField cfProductName = customFieldManager.getCustomFieldObject(productNameField.getId())
    CustomField cfStartDate = customFieldManager.getCustomFieldObject(startDateField.getId())
    String productId = issue.getCustomFieldValue(cfProductId).toString()

String query = "select productName, startDate from YOURTABLE where productId = ${productId}" //Change 'YOURTABLE' with the table that contains the info.

GroovyRowResult rowResult = sql.firstRow(query)

if (rowResult) {

String productName = rowResult.getProperty("productName")

Date startDate = rowResult.getProperty("startDate") as Date
        TimeZone timeZone = ComponentAccessor.getComponent(TimeZoneManager).getTimeZoneforUser(admin)
        String jiraTimeFormat = ComponentAccessor.getApplicationProperties().getString("jira.date.time.picker.java.format")
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(jiraTimeFormat)
        simpleDateFormat.setTimeZone(timeZone)
        String formattedStartDate = simpleDateFormat.format(startDate)

IssueInputParameters issueInputParameters = issueService.newIssueInputParameters()
        issueInputParameters.with {
            addCustomFieldValue(cfProductName.getIdAsLong(), productName)
            addCustomFieldValue(cfStartDate.getIdAsLong(), formattedStartDate)
}
        IssueService.UpdateValidationResult updateValidationResult = issueService.validateUpdate(admin, issue.getId(), issueInputParameters)
if (updateValidationResult.isValid()) {
            issueService.update(admin, updateValidationResult)
        } else {
            log.warn("Failed to update issue: ${issue.getKey()}: ${updateValidationResult.getErrorCollection()}")
        }
    }
}

Regards