Support Region Scriptrunner Listener

supportregion.groovy

/**
 * Created by scottha on 30/03/2016.
 * Note - All fields need to be changed to regular text
 */
import org.apache.log4j.Logger
import org.apache.log4j.Level
def log = Logger.getLogger("com.s-a-m.SupportStatus")
log.setLevel(Level.DEBUG)
log.debug "Start debug - Region and Country Listener..."
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.IssueInputParametersImpl
import com.atlassian.crowd.embedded.api.Group
import groovy.sql.Sql
import java.sql.Driver

// Get Issue Index Manager instance
def issueService = ComponentAccessor.issueService;
def issueParams = new IssueInputParametersImpl()

def driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver
def props = new Properties()
props.setProperty("user", "jira")
props.setProperty("password", "jira")

def customFieldManager = ComponentAccessor.getCustomFieldManager()
userUtil = ComponentAccessor.getUserUtil()
log.debug "**************"
log.debug "ISSUE Key: " + event.issue.key
log.debug "**************"
def cf = customFieldManager.getCustomFieldObject("customfield_10030")

def country_cf = customFieldManager.getCustomFieldObject("customfield_12366")
def region  = customFieldManager.getCustomFieldObject("customfield_12365")
def ccgroup_cf = customFieldManager.getCustomFieldObject("customfield_10361")
def customer = issue.getCustomFieldValue(cf).toString()
def cust_text_searcher = customFieldManager.getCustomFieldObject("customfield_12367")
def cust_text_string = issue.getCustomFieldValue(cust_text_searcher).toString();
//not in this context
//def sfdc_url = customFieldManager.getCustomFieldObject("customfield_14566")
def cust_addr = customFieldManager.getCustomFieldObject("customfield_14564")
def contract_date = customFieldManager.getCustomFieldObject("customfield_14565")
def result;
if (customer == null) {
    log.debug "No customer - returning null"
    return null
}
def conn = driver.connect( 'jdbc:mysql://10.10.10.10:3306/jira_customers?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF8', props )
def sql = new Sql(conn)
try {


    //def instance
    log.debug "Issue CF: "  + event.issue.getCustomFieldValue(cf).toString()
    sql.eachRow('select distinct account_id, customer_address, ccgroup, renewal_date, country_code from customer where account_name="' + customer+ '" or address_code="' + customer+ '" limit 1' ) {
        log.debug "CC Group: " + it.ccgroup
        //account = "https://eu1.salesforce.com/" + it.account_id
        //not in this context
        //if (it.account_id != null)
        //issue.setCustomFieldValue(sfdc_url, account);
        if (it.customer_address != null && it.customer_address != issueParams.getCustomFieldValue(cust_addr.idAsLong).toString())
            try {
                issueParams.addCustomFieldValue(cust_addr.idAsLong, '' + it.customer_address)
            }
            catch (MissingMethodException) { log.debug "Address Field Missing in this context"}
        if (it.renewal_date != null)
            try {
                issueParams.addCustomFieldValue(contract_date.idAsLong,  it.renewal_date)
            }
            catch (MissingMethodException) { log.debug "Renewal Field Missing in this context"}
        log.debug "Country: " + it.country_code
        def country_query = "select distinct country, supportregion from customer_codes where code = '" + it.country_code + "'";
        sql.eachRow (country_query) {
            log.debug "Country Lookup: " + it.country + " Support Region: " + it.supportregion
            if (it.country != null || it.country != issueParams.getCustomFieldValue(country_cf.idAsLong).toString() )
                try {
                    log.debug "trying to set country field - was: " + issueParams.getCustomFieldValue(country_cf.idAsLong).toString()
                    issueParams.addCustomFieldValue(country_cf.idAsLong, it.country)

                }
                catch (MissingMethodException) { log.debug "Country Field Missing in this context"}
            log.debug "did we set country field?"
            if (it.supportregion != null || it.supportregion != issueParams.getCustomFieldValue(region.idAsLong).toString() )
                try {
                    log.debug "trying to set region field - was: " + issueParams.getCustomFieldValue(region.idAsLong).toString()
                    issueParams.addCustomFieldValue(region.idAsLong, it.supportregion)

                }
                catch (MissingMethodException) {  log.debug "Region Field Missing in this context"}
            log.debug "Did we try to set region field?"
        }

        try { if (event.issue.project.key.contains ("INC") && it.ccgroup != issueParams.getCustomFieldValue(ccgroup_cf.idAsLong)) {
            log.debug "Group: " + it.ccgroup
            try {
                List<Group> groupValue = new ArrayList<Group>()
                if (userUtil.getGroupObject(it.ccgroup) != null) {
                    Group ccgroup_list = userUtil.getGroupObject(it.ccgroup)
                    log.debug "CC Group list" +  ccgroup_list.toString();
                    groupValue.add(ccgroup_list)
                    issueParams.addCustomFieldValue(ccgroup_cf.idAsLong,groupValue)
                    log.debug "Group Value: " + groupValue.toString() + " - Group Name in DB: " + it.ccgroup.toString()}
            } catch (Exception e) {log.error "Invalid Group specified: " + it.ccgroup + e}
        }} catch (Exception e) { log.debug "Failed to read to check group value" }
    }

} finally {
    sql.close()
    conn.close()
}
//if (cust_text_string == customer){

if (cust_text_string != customer){
    try {
        issueParams.addCustomFieldValue(cust_text_searcher.idAsLong, customer)
    }
    catch (MissingMethodException) { log.debug "Customer Searcher Field Missing in this context"}
}
//if (region != null) result=region

//log.debug "trying to set country field - is: " + issue.getCustomFieldValue(country_cf).toString()
//log.debug "trying to set region field - is: " + issue.getCustomFieldValue(region).toString()
//log.debug result
log.debug "Region Field Debuggering End..."

log.debug event.issue
log.debug event.issue.id
log.debug event.user
log.debug issueParams.toString()
def updateResult = issueService.validateUpdate(event.user, event.issue.id, issueParams)
log.debug updateResult.warningCollection.warnings
log.debug updateResult.errorCollection.errorMessages
//if(!updateResult.errorCollection.) {
    issueService.update(event.user, updateResult)
//}