creating custom JQL functions

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
 }
}

0 answers

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

5 ways you can make the most of Jira Software and Bitbucket Cloud

As part of the Bitbucket product team I'm always interested in better understanding what kind of impact the use of our tools have on the way you work. In a recent study we conducted of software devel...

79 views 0 5
Read article

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