sum logged work by specific user in custom field

Question

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

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 ?

Fran Garcia_690cr7 · Answer

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)

sheikhmohammad · Answer

have I specify issue ID ?!

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

Fran Garcia_690cr7 · Answer

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 
" +
"where wl.issueid in ( 
" +
"	 select id from jiraissue where project = ( 
" +
"		 select id from project where pkey = 'DEM' 
" +
"	 )
" +
")
" +
"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