It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

sum logged work by specific user in custom field Edited

Hi

Can I sum logged work by specific user and store in custom field ?

I can sum total time spent of a stories subtasks but in this case I want to sum total time spent base on specific user for whole project. for example I have Story A that has 3 subtasks (A.1 - A.2 - A.3) and Story B that has 2 subtasks (B.1 - B.2) and there are 3 user that logged on this issues (user1 - user2 - user3).

this is example of work logged on issues :

A.1 :

user1: 2 hour

user2: 3 hour

user1: 1 hour

user3: 4 hour

 

A.2 :

user1: 2 hour

user3: 6 hour

 

A.3 :

user2: 7 hour

user1: 3 hour

user1: 5 hour

 

B.1 :

user2: 1 hour

user1: 2 hour

 

B.2 :

user1: 4 hour

user1: 4 hour

user3: 3 hour

 

Now I want user1 total time spent custom field show 23 hour (2+1+2+3+5+2+4+4) on a specific story that I create for project health management.

 

Can I do this with script (script runner or misc custom field) or SIL (simple issue language) or other solution ?

3 answers

This groovy gives you the data

import org.ofbiz.core.entity.ConnectionFactory;
import org.ofbiz.core.entity.DelegatorInterface;
import com.atlassian.jira.ofbiz.OfBizDelegator;

import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.user.ApplicationUser;
import com.atlassian.jira.user.ApplicationUsers;

import groovy.sql.Sql;
import groovy.sql.GroovyRowResult;
import java.sql.SQLException;
import java.sql.Connection;

// Pillamos datos de la BD
OfBizDelegator delegator = ComponentAccessor.getOfBizDelegator();
DelegatorInterface delegatorInterface = delegator.getDelegatorInterface();
String helperName = delegatorInterface.getGroupHelperName("default");
Connection connection = ConnectionFactory.getConnection(helperName);

Sql sql = new Sql(connection);
List<GroovyRowResult> resultRows = sql.rows("SELECT wl.author, sum(wl.timeworked) FROM worklog wl \n" +
"where wl.issueid in ( \n" +
"\t select id from jiraissue where project = ( \n" +
"\t\t select id from project where pkey = 'DEM' \n" +
"\t )\n" +
")\n" +
"group by wl.author;");
sql.close();
if (resultRows == null || resultRows.size() == 0) {
log.debug("No result found");
return;
}

def totalwl = 0
resultRows.each{

log.warn it[0]
log.warn it[1]
}

You can change the SQL to refine the search

have I specify issue ID ?!

Can not get user1 whole work logged in project without set issue ID in code ?

do you mean in the SQL?

You only use the issue id to relate the worklog table with the project table.

The script i wrote there gives you an array of pairs of values with every author of a worklog on the first place of the pair and the sum of his logs (in seconds) in the project with the key DEM in the second value of the pair.

If you only want the sum of logs of one user, just change

group by wl.author;

with

and wl.author = (select id from cwd_users where user_name='yourusername')

wrote this last sentence without looking the database, so maybe cwd_users is cwd_user and maybe the field name is not exactly user_name (i think this one is correct)

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

314 views 1 3
Join discussion

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you