/src/test/resources/examples/Adam.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()

}