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
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Apr 17, 2018 in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

764 views 2 19
Join discussion

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