gistfile1.groovy

package examples

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.MutableIssue
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

MutableIssue issue = issue
String sClient = issue.getSummary()
ComponentManager componentManager = ComponentManager.getInstance()
delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
String helperName = delegator.getGroupHelperName("default");

def sqlStmt = """
    SELECT     *
    FROM       jiraissue
    WHERE      issuetype = '11' AND issuestatus = '10009' AND summary = '${sClient}'
"""

Connection conn = null
Sql sql = null

try {
    conn = ConnectionFactory.getConnection(helperName);
    sql = new Sql(conn)
    StringBuffer sb = new StringBuffer()
    sCounter = 0
    sql.eachRow(sqlStmt) {
        sDueDate = it[14]
        def sqlStmt2 = """
                SELECT		*
                    FROM		customfieldvalue
                    WHERE 		customfield = '10400' AND stringvalue = '${sClient}'
            """
        sql.eachRow(sqlStmt2) {
            def sqlStmt3 = """
                        SELECT		*
                            FROM		customfieldvalue
                            WHERE		customfield = '10800' AND stringvalue = '10500' AND issue = '${it.issue}'
                    """
            sql.eachRow(sqlStmt3) {
                def sqlStmt4 = """
                                SELECT		count(*)
                                    FROM		jiraissue
                                    WHERE		id = '${it.issue}' AND duedate between DATE_SUB('${sDueDate}', INTERVAL 1 YEAR) and '${sDueDate}'
                                """
                sql.eachRow(sqlStmt4) {
                    if(it[0]>0) {
                        sCounter++
                    }
                }
            }
        }
    }
    sql.close()
    return sCounter.toDouble()
}
finally {
    sql.close()
    conn.close()
}