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 ?
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)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
have I specify issue ID ?!
Can not get user1 whole work logged in project without set issue ID in code ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Maybe there is some free time tracking addon
https://marketplace.atlassian.com/search?category=Time%20tracking
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.