How to generate user selection choice values using SQL - 6.x


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 described 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