How is possible to update a customfield value from database and after update this value from a table?

Hello,

I am new to groovy and looking for help for the following:

I created a table to calculated the average of my issue type´s LOGWORK (based on SQL - it is already done).

So, I have to develop two groovy scripts that should:

1) Access this database and return the last average value (LOGWORK) and show on CUSTOMFIELD

2) When the user closed the subtask, I need to store this value (LOGWORK) in my table to calculate a new average.

To execute my query, I have this:

import ...
def delegator = (DelegatorInterface) 
ComponentAccessor.getComponent(DelegatorInterface)
String helperName = 
delegator.getGroupHelperName("default");

def sqlStmt = """
SELECT 
A.ISSUETYPE, A.REQ_TYPE, A.SUB_TASK_TYPE, ROUND(SUM(TOTAL_SEC)/COUNT(1), 0) 
AVERAGE_SEC, ROUND(SUM(TOTAL_MIN)/COUNT(1), 2) AVERAGE_MIN, 
ROUND(SUM(TOTAL_HOUR)/COUNT(1), 2) AVERAGE_HOUR, COUNT(1) TOTAL_TASKS
FROM 
(
SELECT I.ID, I.ISSUETYPE, IT.PNAME REQ_TYPE,
ST.ID 
SUB_TASK_ID,
SUBSTR(ST.SUMMARY, INSTR(ST.SUMMARY, '(')+1, INSTR(ST.SUMMARY, 
')')-INSTR(ST.SUMMARY, '(')-1) SUB_TASK_TYPE,
SUM(WL.TIMEWORKED) 
TOTAL_SEC,
SUM(WL.TIMEWORKED)/60 TOTAL_MIN,
SUM(WL.TIMEWORKED)/3600 
TOTAL_HOUR
FROM JIRAISSUE I, JIRAISSUE ST, ISSUELINK IL, ISSUETYPE IT, 
WORKLOG WL
WHERE IL.SOURCE = I.ID
AND IL.DESTINATION = ST.ID
AND 
I.ISSUETYPE = IT.ID
AND IL.LINKTYPE = 10100
AND ST.ID = WL.ISSUEID
AND 
I.CREATED >= to_date('2016/09/01', 'YYYY/MM/DD')
GROUP BY I.ID, 
I.ISSUETYPE, IT.PNAME, ST.ID, SUBSTR(ST.SUMMARY, INSTR(ST.SUMMARY, '(')+1, 
INSTR(ST.SUMMARY, ')')-INSTR(ST.SUMMARY, '(')-1)
) A
WHERE REQ_TYPE = 'ISSUE TYPE' –  ISSUE TYPE
AND SUB_TASK_TYPE = 
'SUB TASK' –  SUB TASK TYPE
GROUP BY A.ISSUETYPE, 
A.REQ_TYPE, A.SUB_TASK_TYPE;

Connection conn = 
ConnectionFactory.getConnection(helperName);
Sql sql = new 
Sql(conn)

try {
StringBuffer sb = new 
StringBuffer()
sql.eachRow(sqlStmt) {
sb << 
"${it.pname}\t${it.kount}\n"
}
log.debug sb.toString()
}
finally 
{
sql.close()
}

Thank you everyone!

1 answer

What's the question? 

I don't think this is the right way to do it - it's quite fragile. I would run a jql query to get the issues you care about, get their worklogs, 

def worklogs = ComponentAccessor.getWorklogManager().getByIssue(issue)
worklogs.sum {
    it.timeSpent
} / worklogs.size()

Jamie,

I will need a script to get a value in a table(jql) and show in my custom field and another to get the valeu on my custom field and update this table

 

thank you

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted 6 hours ago in United States

Atlassian acquires AgileCraft

         Good Day, Bad bad traffic, not sure why!!!! 1/2 hour commute took me 2 hours today 🤯 What helped me is that I kept browsing LinkedIn until...

30 views 1 0
View post

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