Overdue days custom field!

[Davor Fisher]

Hi all,

I am trying to create a custom field that will calculate  "Overdue days" based on this calculation:(Todays Date - SLA Date ). SLA Date is already a custom field that we have set up that calculates on created days + priority. Here is the script for SLA Date.

import org.joda.time.DateTime;
if(issue.priorityObject.name.contains("1-Urgent")) {
return new DateTime(issue.created).plusDays(1).toDate()
}
if(issue.priorityObject.name.contains("2-Very High")) {
return new DateTime(issue.created).plusDays(2).toDate()
}
if(issue.priorityObject.name.contains("3-High")) {
return new DateTime(issue.created).plusDays(3).toDate()
}
if(issue.priorityObject.name.contains("4-Medium")) {
return new DateTime(issue.created).plusDays(4).toDate()
}
if(issue.priorityObject.name.contains("5-Low")) {
return new DateTime(issue.created).plusDays(5).toDate()
}
issue.created

[Kristian Walker _Adaptavist_ · Accepted solution]

Hi DF,

I have done a similar thing in the past where I have created a scripted field called SLA Breach which calculates the number of days that have passed since the Due Date of an issue in order to show how much the SLA has been breached by.

This uses the TimeDuration in Groovy to work out how many days and hours overdue the SLA is. I have enclosed the Script which I have used below as this may help you to use the TimeDuration method to solve your problem.

Please bare in mind that this does not calculate working days only all days between the two dates and if you wish to only calculate weekdays then you should look at the article here.

I hope this helps.

Example Script:

// Required Import Statements
import com.atlassian.jira.issue.Issue
import groovy.time.TimeCategory
import groovy.time.TimeDuration
import java.text.SimpleDateFormat;

// Get a pointer to the current issue and extract the Due Date as a string
Issue issue = issue
Date dueDate = issue.getDueDate()

// Get the current date and time 
Date today = new Date()

// get formatted versions of the current and Due date to test if the SLA has been breached.
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy")
def currentDay = sdf.format(today)
def formatDueDate = sdf.format(dueDate)

// Work out the number of days between the current date and the due date
def daysBetween = today - dueDate

// Check if the due date is after the current date and if so return a message to say the SLA has not been breached.
if (formatDueDate >= currentDay) {
    return "SLA Not Breached"
}

// Check if it has been less than 1 day since the due date was breached and if so return the number of hours.
if (daysBetween <= 1) {
// Work out the amount of hours and minutes between the Current Time and the Due Date.

// Set the time of the current date to Midnight as we have not currently breached the SLA by more than 1 day.
    today.clearTime()

// Get the current date and time
    Date currentTime = new Date()

// Use the Time Duration class provided by groovy to work out the number of hours and minutes between the two dates and times.
    TimeDuration timeBetween = TimeCategory.minus(currentTime, today)

//timeBetween returns Hours, Minutes & Seconds so lets remove the seconds.
    String hours = timeBetween.toString().split(",")[0].trim();
    String minutes = timeBetween.toString().split(",")[1].trim();
    def trimmedTimeBetween = hours + ", " + minutes
    return trimmedTimeBetween
//If more than 1 day has passed since the due date was breached then return the number of days.
} else {
    return daysBetween + " days"
}

I hope this helps

Kristian

[Davor Fisher]

The custom field i have set up to value due date is called "SLA Date"! Should i look to replace all DueDate in the script above with SLADate?

[Kristian Walker _Adaptavist_]

Hi DF,

If you want to use my script then you can get the value of your SLA Date field which you could do by chaning the following line of: Date dueDate = issue.getDueDate() to be def dueDate = getCustomFieldValue("SLA Date") as Date providing the SLA Date Field is a date picker field in JIRA.

dueDate i just a variable for the script so you could rename this but if so would need to make sure all instances of it were renamed inside the script.

I hope this Helps

Thanks

Kristian

[Tuncay Senturk]

As Kristian mentioned, this script works against issue's due date field.

If you have a custom field that works just like your script, then my script above will work just as you expected. have you tried that?

[Pablo Beltran]

ScriptRunner is really a useful add-on, however, sometimes it brings a lot of complexity compared with other alternatives.

For example, SQL for JIRA is able to do those calculations in a breath:

select cf.customfield2-cf.customfield1 

from issues i inner join issuecustomfields cf on cf.issueid=i.id 

where i.key=?

You can adapt the SQL query above and use it with SQL for JIRA Custom Fields to display the difference among the two customfields values on the Issue Detail View.

[Tuncay Senturk]

I did not test but it can be similar as below, hope it helps

Bear in mind that it returns as days (24h = 1d), and does not count against working day/hour, nonworking day, ..

I strongly recommend using Time to SLA

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.Issue;
import org.joda.time.DateTime;

def customFieldManager = ComponentAccessor.getCustomFieldManager()
// replace your custom field id with customfield_11300
def slaDateField = customFieldManager.getCustomFieldObject("customfield_11300")
def slaDateValue = issue.getCustomFieldValue(slaDateField)
def now = new DateTime()
 
// return 0 if no overdue
if (slaDateValue.isAfter(now)) {
	return 0
}
// return days between sla date and now
return Days.daysBetween(slaDateValue, now).getDays()

[Davor Fisher]

Receiving an error in line 15 and 19

Screen Shot 2016-03-29 at 10.24.29 AM.png

Screen Shot 2016-03-29 at 10.25.13 AM.png

[Tuncay Senturk]

Means your Joda time version is not up-to-date.

Anyway try below instead

if (slaDateValue.toDate().after(now.toDate())) {
    return 0
}

also add this import 

import org.joda.time.Days;

[Tuncay Senturk]

Hi DF,

I could not see any question in your comment, however have you seen Time to SLA Plugin 

You can easily define SLAs and see countdown, see overdue, get report, and etc

Regards

Tuncay

[Davor Fisher]

Hi,

We are in the process of reviewing this plugin for another project that will be using these functions. What i need is something similar but yet still different. I already have a custom field created called SLA Date which value is Priority + days. I showed in the comment above. Now i need a custom field that will count overdue days based off the field (Todays date - SLA Date). Does that make any sense?