creating custom JQL functions

[Michel KATZ]

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