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

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

Artur Martins Feb 02, 2017

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

1 vote
JamieA Feb 02, 2017

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 Feb 02, 2017

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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted in Statuspage

⭐️Would you recommend Statuspage? Leave a (honest!) review. ⭐️

Hi Community friends, We're working on sourcing more reviews on Capterra – a popular software review site –  to help teams like yours make more informed decisions when choosing an inc...

82 views 3 5
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