Groovy script to filter work log date from Issue Navigator

We have a scripted field to loop into the issue to calculate the cost with formula as below, for every issue.

Vendor cost = No. of hours clocked X Rate per hour

We use the below JQL to filter the issue which has clocked hours for a current month

worklogDate >= startOfMonth() and worklogDate <= endOfMonth()

The total cost is wrong because it takes all the worklog under the issue. For instance, in the issue we have one worklog on August and another worklog on December.

Are there any ways to get the exact worklogs for a specific month or time range to do a calculation by using Issue Navigator??

Idea is if the script can capture and get the time range, result from Issue Navigator and do the calculation.

2 answers

0 votes

JQL is for finding issues matching criteria, so I suspect your script is simply missing a chunk of logic.  Please correct me if this guess is wrong:

I think your script does this:

  • For each issue matching ( worklogDate >= startOfMonth() and worklogDate <= endOfMonth() )
    • Read work logs
    • Add them up
  • End

What it should do is:

  • For each issue matching ( worklogDate >= startOfMonth() and worklogDate <= endOfMonth() )
    • For each work log
      • Look at the date of the log
      • If it is in the month, add it up
    • End
  • End

Hi Nic,

For current month, it is ok to check and add it up. But the problem is when I choose the time range. For instance, worklog between Oct to Dec. How can I get the JQL command and then add up only the worklog from Oct to Dec ( worklogDate >= startOfMonth(-2) and worklogDate <= endOfMonth() )

Here is my script for reference, sorry i'm not groovy dev so the code is hard to understand.

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.worklog.Worklog
import com.atlassian.jira.issue.worklog.WorklogManager
import com.atlassian.jira.issue.customfields.option.Options
import com.atlassian.jira.issue.fields.CustomField
CustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager()
IssueManager issueManager = ComponentAccessor.getIssueManager()
WorklogManager worklogManager = ComponentAccessor.getWorklogManager()
def userPropertyManager = ComponentAccessor.getUserPropertyManager()
Issue issue = issue
List&lt;Worklog&gt; worklogs = worklogManager.getByIssue(issue)
Double total_cost = 0
Double cost = 0

for (worklog in worklogs) {
    def author = worklog.getAuthorKey()
    def value1 = userPropertyManager.getPropertySetForUserKey(author).getString("jira.meta.Employer")
    def value2 = userPropertyManager.getPropertySetForUserKey(author).getString("jira.meta.Onshore / Offshore")
    Double vendor_rate = 0
    CustomField cf = ComponentAccessor.getCustomFieldManager().getCustomFieldObject("customfield_12003") //Vendor-Rate cf
    Options options = ComponentAccessor.getOptionsManager().getOptions(cf.getConfigurationSchemes().listIterator().next().getOneAndOnlyConfig())
    if (value1 != null &amp;&amp; value2 !=null) {
        def parse = options[options.findIndexValues { it =~ (/${value1}*.*${value2}/) }]
        if (parse.size() &gt; 1) {
            parse = parse[parse.findIndexValues {it =~ (/$value1.$value2/)}]
        def asString = parse.join(", ")
        def arr = asString.tokenize("|")
        vendor_rate = Double.valueOf(arr[2])
    def timespent = worklog.getTimeSpent()
    cost = vendor_rate * timespent / 3600.0d / 8.0d
    total_cost += cost
return total_cost

Moreover we are using Tempo Timesheet. Can Tempo servlet be used in this case to calculate the cost from <date> to <date> or can I use JQL function from ScriptRunner as below then get the user input date and use it in the script to compare the worklog date.

issueFunction in workLogged("after 2016/10/01 before 2016/12/01")

It's the same answer I gave earlier - your code is reading all the logs for an issue.  You need to get it to look at the date on the worklog and ignore ones out of the date range you want to calculate for.

What Nic is saying is correct. Get the issues with candidate worklogs then filter the worklogs further, eg:

import com.atlassian.jira.component.ComponentAccessor

def worklogManager = ComponentAccessor.getWorklogManager()
def startDate = Date.parse("dd/MMM/yyyy", "01/Dec/2016")
def endDate = ...

worklogManager.getByIssue(issue).findAll {
    it.created.after(startDate) &amp;&amp; it.created.before(endDate)

Thanks Jamie, but in this case we still do hard code for the date. Are there any ways to do it automatically from the JQL issue navigator? (user input)

A scripted field couldn't get its values as input from a JQL function, since scripted fields show up in places outside of the issue navigator.

You could configure a few different scripted fields that got information like the worklogs from the last month, the last three months, etc., and query by them as needed. See for an example of using Groovy's date extensions to get Date objects for specific months in relation to the current date.

You could also make the scripted field dependent on a plain date custom field on the issue, then change its value using bulk edit as needed.

As a side note, there's an issue in our backlog (SRJIRA-2133) to allow querying the worklogs directly in a JQL function. 

Hi Jamie,

Appreciated if you can help me how to get all the worklogs within the previous month. 

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

1,099 views 4 9
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you