Groovy script to filter work log date from Issue Navigator

[CST JIRA Confluence Admin]

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.

[JamieA]

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) && it.created.before(endDate)
}

[CST JIRA Confluence Admin]

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)

[Jonny Carter]

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 http://stackoverflow.com/a/10828459/1524502 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. 

[CST JIRA Confluence Admin]

Hi Jamie,

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

[Nic Brough -Adaptavist-]

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

[CST JIRA Confluence Admin]

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<Worklog> 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 && value2 !=null) {
        def parse = options[options.findIndexValues { it =~ (/${value1}*.*${value2}/) }]
        if (parse.size() > 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

[CST JIRA Confluence Admin]

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")

[Nic Brough -Adaptavist-]

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.