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

[Artur Martins]

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!

[JamieA]

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

[Artur Martins]

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

[JamieA]

Start here - https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/running-a-jql-query.html