Hello,
I've created a custom JQL function to run a SQL (to collect all issue with attach file name like XXX).
In log file it seems ok, but I didn't receive the list.
Could you help me ? What's missing ?
I've read the documenttaion, but it is not enough.
Thx
The log
2017-11-28 16:17:05,533 https-jsse-nio-443-exec-18 DEBUG FAB055 977x851x1 1hcpqzz 172.24.20.119 /rest/issueNav/1/issueTable [c.o.j.groovy.jql.AttachFileNameContains] *** AttachFileNameContains :: script running ++++++
2017-11-28 16:17:05,533 https-jsse-nio-443-exec-18 DEBUG FAB055 977x851x1 1hcpqzz 172.24.20.119 /rest/issueNav/1/issueTable [c.o.j.groovy.jql.AttachFileNameContains] *** AttachFileNameContains :: sqlgetattachfn = SELECT pkey+'-'+CAST(issuenum AS varchar(12)) as pkey from (
SELECT pr.pkey, ji.issuenum, count ([FILENAME]) as Nbr
FROM [fileattachment] as FA
inner join jiraissue as JI on FA.issueid = JI.ID
inner join project as PR on Ji.PROJECT = Pr.ID
where PR.[pname] like '%transl%' and [FILENAME] like '%raad%'
group by pr.pkey, ji.issuenum) as T
order by issuenum desc
2017-11-28 16:17:06,032 https-jsse-nio-443-exec-18 DEBUG FAB055 977x851x1 1hcpqzz 172.24.20.119 /rest/issueNav/1/issueTable [c.o.j.groovy.jql.AttachFileNameContains] *** AttachFileNameContains :: AttachCounter = 10
2017-11-28 16:17:06,079 https-jsse-nio-443-exec-18 DEBUG FAB055 977x851x1 1hcpqzz 172.24.20.119 /rest/issueNav/1/issueTable [c.o.j.groovy.jql.AttachFileNameContains] *** AttachFileNameContains :: booleanQuery = issue_id:TRSL-1738 issue_id:TRSL-1733 issue_id:TRSL-1239 issue_id:TRSL-1235 issue_id:TRSL-564 issue_id:TRSL-563 issue_id:TRSL-380 issue_id:TRSL-372 issue_id:TRSL-321 issue_id:TRSL-320
My script
// 28/11/2017 - M Katz
// AttachFileNameContains
package com.onresolve.jira.groovy.jql
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.jql.query.LuceneQueryBuilder
import com.atlassian.jira.jql.query.QueryCreationContext
import com.atlassian.jira.jql.validator.NumberOfArgumentsValidator
import com.atlassian.crowd.embedded.api.User
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.util.MessageSetImpl
import com.atlassian.jira.util.MessageSet
import com.atlassian.query.clause.TerminalClause
import com.atlassian.query.operand.FunctionOperand
import org.apache.lucene.index.Term
import org.apache.lucene.search.Query
import org.apache.lucene.search.TermQuery
import com.onresolve.jira.groovy.jql.AbstractScriptedJqlFunction
import org.apache.lucene.search.BooleanClause
import org.apache.lucene.search.BooleanQuery
import org.ofbiz.core.entity.DelegatorInterface
import groovy.sql.Sql
import java.sql.Connection
import org.ofbiz.core.entity.ConnectionFactory
import groovy.util.logging.Log4j
@Log4j
class AttachFileNameContains extends AbstractScriptedJqlFunction implements JqlQueryFunction{
def luceneQueryBuilder = ComponentAccessor.getComponent(LuceneQueryBuilder)
@Override
Integer getMinimumNumberOfExpectedArguments() {
2
}
@Override
String getDescription() {
"Function to validate if the last comment contains certain text"
}
@Override
List<Map> getArguments() {
[
[ "project name": "ProjName", "optional": false],
[ "substring": "Substring", "optional": false]
]
}
@Override
String getFunctionName() {
"AttachFileNameContains"
}
@Override
boolean isList() {
true
}
def String subquery
//@Override
MessageSet validate(User user, FunctionOperand operand, TerminalClause terminalClause) {
def messageSet = new MessageSetImpl()
return messageSet
}
@Override
Query getQuery(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {
def booleanQuery = new BooleanQuery()
def SelectIssues = { String TargetProj, String Text ->
log.setLevel(org.apache.log4j.Level.DEBUG)
log.debug("*** AttachFileNameContains :: script running ++++++ ")
def AttachIdList = null
Integer AttachCounter = -1
Connection conn
def sqlgetattachfn = """SELECT pkey+'-'+CAST(issuenum AS varchar(12)) as pkey from (
SELECT pr.pkey, ji.issuenum, count ([FILENAME]) as Nbr
FROM [fileattachment] as FA
inner join jiraissue as JI on FA.issueid = JI.ID
inner join project as PR on Ji.PROJECT = Pr.ID
where PR.[pname] like '%%Proj%%' and [FILENAME] like '%%Text%%'
group by pr.pkey, ji.issuenum) as T
order by issuenum desc
"""
// Select
//++++++++++
def ActionSelected = { Connection conn2, String SQL_select ->
def result
try {
Sql sqlP = new Sql(conn2)
result = sqlP.rows(SQL_select)
}
catch (Exception ex) {
result = ''
}
return result
}
try {
TargetProj = TargetProj == null ? '%' : '%'+TargetProj+'%'
Text = Text == null ? '%' : '%'+Text+'%'
sqlgetattachfn = sqlgetattachfn.replace('%%Proj%%',TargetProj).replace('%%Text%%',Text)
log.debug("*** AttachFileNameContains :: sqlgetattachfn = $sqlgetattachfn")
def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")
conn = ConnectionFactory.getConnection(helperName)
// List attachment ID
AttachIdList = ActionSelected (conn, sqlgetattachfn)
AttachCounter = AttachIdList.size()
log.debug("*** AttachFileNameContains :: AttachCounter = $AttachCounter")
}
catch (Exception ex) {
// Failure should be logged
}
finally {
conn.close()
return AttachIdList
}
}
def IssueList = SelectIssues (operand.args[0], operand.args[1])
IssueList.each {
booleanQuery.add(new TermQuery(new Term("issue_id", it.pkey as String)), BooleanClause.Occur.SHOULD)
}
log.debug("*** AttachFileNameContains :: booleanQuery = $booleanQuery")
return booleanQuery
}
}
Online forums and learning are now in one easy-to-use experience.
By continuing, you accept the updated Community Terms of Use and acknowledge the Privacy Policy. Your public name, photo, and achievements may be publicly visible and available in search engines.