groovy assignee/sql/autotransition

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.util.JiraUtils
import com.atlassian.jira.workflow.WorkflowTransitionUtil
import com.atlassian.jira.workflow.WorkflowTransitionUtilImpl
import groovy.sql.Sql
import org.apache.log4j.FileAppender
import org.apache.log4j.Level
import org.apache.log4j.TTCCLayout

import java.sql.Driver

log.setLevel(Level.DEBUG)
/**
 * Created by user on 22.08.2017.
 */

def tc = new TTCCLayout("yyyy-MM-dd HH:mm:ss.SSS")
def fa = new FileAppender(tc, "/opt/atlassian/jira/logs/scriptrunner.log")

log.addAppender(fa)

MutableIssue issue = (MutableIssue) issue                                                // Заявка
def xml = new XmlSlurper().parse("/var/atlassian/application-data/jira/scripts/config/conf.xml")                            // Указываем файл на логин пароль для postgres
def driver = Class.forName('org.postgresql.Driver').newInstance() as Driver   // драйвер подключения к postgres
def issueManager = ComponentAccessor.getIssueManager()                                 //менеджер заявок
def userManager = ComponentAccessor.getUserManager()                                   //менеджер пользователей
def CFM = ComponentAccessor.getCustomFieldManager()

ApplicationUser UpdateUser = userManager.getUserByName("Admin")
def reporter = issue.getReporter().name

String podrazdelenie = CFM.getCustomFieldObject(12104).getValue(issue)          //Значение пользовательского поля "Подразделение"
String cost = CFM.getCustomFieldObject(12100).getValue(issue)                   //Значение пользовательского поля "Статья затрат"
String vid_perevozok = CFM.getCustomFieldObject(10700).getValue(issue)          //Значение пользовательского поля "Вид перевозок"
def status = issue.getStatus().getName()                                              //Получение имени статуса текущей заявки

String login = xml.depthFirst().find {
    it.name() == 'login'
}                                                                                     //Парсинг логина
String password = xml.depthFirst().find {
    it.name() == 'password'
}                                                                                     //Парсинг пароля

def props = new Properties()                                                          //Переменная проперти
props.setProperty("user", login)                                                      //Указываем логин в проперти
props.setProperty("password", password)                                               //Указываем пароль в проперти

String UserSQL = new String()                                                        // Переменная пользователя который будет назначен в исполнители
def conn = driver.connect("jdbc:postgresql://localhost:5432/jira", props)         // Коннект к базе
def sql = new Sql(conn)                                                              //Переменная  с параметрами при запросе в базу

def paramsCost = [cost]                                                              //Параметры статьи затрат получаемая с поля cost

if(vid_perevozok == "Грузовые перевозки") {                                           //Значение для переменной paramsJurname,значение берутся с поля podrazdelenie
    paramsJurname = [podrazdelenie + " грузотакси"]
} else{
    paramsJurname = [podrazdelenie]
}

try {

    switch (status) {
        case "Согл. рег. дир.":
            sql.eachRow("SELECT comdirneed FROM main.jira_cost where cost = ?", paramsCost) { row -> comdirneed = row.comdirneed
            }
            sql.eachRow("SELECT regdir FROM main.jira_juridical where jurname = ?", paramsJurname) { row -> regdir = row.regdir
            }
            if (reporter == regdir) {
                if (comdirneed == true) {
                    def actionId = 341
                    WorkflowTransitionUtil workflowTransitionUtil = ( WorkflowTransitionUtil ) JiraUtils.loadComponent( WorkflowTransitionUtilImpl.class )

                    workflowTransitionUtil.setIssue(issue)
                    workflowTransitionUtil.setUserkey("autotransition")
                    workflowTransitionUtil.setAction (actionId)

                    // validate and transition issue
                    workflowTransitionUtil.validate()
                    workflowTransitionUtil.progress()

                    sql.eachRow("SELECT comdir FROM main.jira_juridical where jurname = ?", paramsJurname) { row ->
                        UserSQL = row.comdir
                    }

                } else {
                    def actionId = 431
                    WorkflowTransitionUtil workflowTransitionUtil = ( WorkflowTransitionUtil ) JiraUtils.loadComponent( WorkflowTransitionUtilImpl.class )

                    workflowTransitionUtil.setIssue(issue)
                    workflowTransitionUtil.setUserkey("admin")
                    workflowTransitionUtil.setAction (actionId)

                    // validate and transition issue
                    workflowTransitionUtil.validate()
                    workflowTransitionUtil.progress()
                }
            } else {
                sql.eachRow("SELECT regdir FROM main.jira_juridical where jurname = ?", paramsJurname) { row -> UserSQL = row.regdir
                }
            }; break


        case "Согл. держателем бюджета": if (cost == "Реклама") {
            if (vid_perevozok == "Грузовые перевозки") {
                sql.eachRow("SELECT koordreklama FROM main.jira_juridical where jurname = ?", paramsJurname) { row ->
                    UserSQL = row.koordreklama
                }
            } else {
                sql.eachRow("SELECT koordreklama FROM main.jira_juridical where jurname = ?", paramsJurname) { row ->
                    UserSQL = row.koordreklama
                }
            }
                } else {
            sql.eachRow("SELECT budgetHolder FROM  main.jira_cost where cost = ?", paramsCost) { row ->
                UserSQL = row.budgetHolder

            }; break
        }; break

        case "Согл. казначеем": sql.eachRow("SELECT kaznachey FROM main.jira_juridical where jurname = ?", paramsJurname) { row ->
            UserSQL = row.kaznachey
        }; break

        case "Оплата": sql.eachRow("SELECT buhoplata FROM main.jira_juridical where jurname = ?", paramsJurname) { row ->
            UserSQL = row.buhoplata
        }; break

        case "Согл. фин. дир.": sql.eachRow("SELECT findir FROM main.jira_juridical where jurname = ?", paramsJurname) { row ->
            UserSQL = row.findir
        }; break
        case "Согл. комм.дир.":
            sql.eachRow("SELECT comdir FROM main.jira_juridical where jurname = ?", paramsJurname) { row ->
                UserSQL = row.comdir
            }; break


    }
} finally {
    sql.close()
    conn.close()
}
/*
log.debug("Номер заявки " + issue +
        "Cтатус: " + issue.getStatus().getName()
        + "Автор: " + issue.getReporter().getName())

*/
log.removeAppender(fa)
ApplicationUser assignee = userManager.getUserByName(UserSQL)

issue.setAssignee(assignee)

//issueManager.updateIssue(UpdateUser, issue, EventDispatchOption.ISSUE_ASSIGNED, false)