How to generate user selection choice values using SQL

Description

This is example groovy script functions to help generate choice values based on standard Confluence, JIRA, or Crowd based user tables. Use together with your other choice generating script as part of a groovy macro to produce a page as describe in How to generate choice values using SQL. You will need to modify for your situation.

import com.atlassian.renderer.v2.RenderMode
 
def applicationId = 1  // Get this value out of your cwd_application database or modify the sql below to get it dynamically
def ANY = '*'  // Special value to represent any user id (like in JIRA)
def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)

/**
 * Get colon separated list of users - userid:displayName - order by name
 * A few user's are explicitly not shown on the list - modify for your situation
 */
def getUserChoice() {

    def sql = """
        select lower_user_name || ':' || quote_nullable(display_name) || ':\\\\'
            from cwd_user where
                (lower_user_name not like '%-admin%')
                and (lower_user_name not like 'automation%')
                and (directory_id in (select directory_id from cwd_app_dir_mapping where application_id = ${applicationId}))
            order by display_name
        """
    def userChoice = "${ANY}:Any:current_user_id:Current user:" + getChoice(sql, crowdDataSource) 
    return userChoice
}

/**
 * General function to run SQL for choice text
 */
def getChoice(final sql, final dataSource) {

    def choiceMacro = "{sql-query:datasource=${dataSource}|table=false} ${sql} {sql-query}"
    def choices = subRenderer.render(choiceMacro, context, renderMode)

    if (choices.contains('div class="error"')) {
        println "Error rendering macro: ${choiceMacro}"
        println "Error: ${choices}"
        return "ERROR"
    }
    return choices
}