Generate Confluence Report with Jira Data

import groovy.transform.Field
import java.time.LocalDateTime
import groovy.json.JsonSlurper
import groovy.json.JsonOutput

/********** User Input Start **********/

// ID of the Confluence template to use as the report base.
// Find it in the template URL: /wiki/rest/api/template/<TEMPLATE_ID>
String TEMPLATE_ID = '<YOUR_TEMPLATE_ID>'

// Key of the Confluence space where the report page will be created (e.g. 'HR', 'DS')
String SPACE_KEY = '<YOUR_SPACE_KEY>'

// ID of the parent page or folder under which the report page will be created
String PARENT_ID = '<YOUR_PARENT_ID>'

// Set to 'page' or 'folder' depending on the type of the parent identified by PARENT_ID.
// See: https://developer.atlassian.com/cloud/confluence/rest/v2/api-group-children
String PARENT_TYPE = 'page'

// Base title for the generated report page; the month/year prefix is prepended automatically
String PAGE_BASE_TITLE = '<YOUR_REPORT_TITLE>'

// Credentials for the Jira instance. Use an API token generated from:
// https://id.atlassian.com/manage-profile/security/api-tokens
@Field String AUTH_EMAIL     = '<YOUR_EMAIL>'
@Field String AUTH_API_TOKEN = '<YOUR_API_TOKEN>'

// Base URL of the Jira instance that provides the issue data (e.g. 'https://yoursite.atlassian.net')
@Field String JIRA_BASE_URL = '<YOUR_JIRA_BASE_URL>'

// JQL project key used to scope the Jira queries (e.g. 'SEO', 'HR')
@Field String JIRA_PROJECT_KEY = '<YOUR_JIRA_PROJECT_KEY>'

/********** User Input End **********/

// HAPI: https://docs.adaptavist.com/sr4cc/latest/hapi/work-with-spaces#retrieve-a-space-with-its-space-key
def spaceId = Spaces.getByKey(SPACE_KEY)['id']

// Conf API v1: https://developer.atlassian.com/cloud/confluence/rest/v1/api-group-template/#api-wiki-rest-api-template-contenttemplateid-get
def getTemplateAdfResponse = get("/wiki/rest/api/template/${TEMPLATE_ID}")
    .queryString('expand', 'body.atlas_doc_format')
    .asObject(Map)
assert getTemplateAdfResponse.status >= 200 && getTemplateAdfResponse.status < 300 : "${getTemplateAdfResponse.status}: ${getTemplateAdfResponse.body}"
def templateAdfInString = getTemplateAdfResponse.body['body']['atlas_doc_format']['value'] as String
def templateAdf = new JsonSlurper().parseText(templateAdfInString) as Map

// Uses instance system time; adjust month offsets below if your report cadence differs
def now = LocalDateTime.now()

// Replace date placeholder variables in the template (last 6 months)
// How to format Groovy dates: https://docs.groovy-lang.org/latest/html/groovy-jdk/java/util/Date.html#format(java.lang.String)
replaceVariable(templateAdf, 'm0' , createTextNode(now.minusMonths(1).format('MM/yy')))
replaceVariable(templateAdf, 'm-1', createTextNode(now.minusMonths(2).format('MM/yy')))
replaceVariable(templateAdf, 'm-2', createTextNode(now.minusMonths(3).format('MM/yy')))
replaceVariable(templateAdf, 'm-3', createTextNode(now.minusMonths(4).format('MM/yy')))
replaceVariable(templateAdf, 'm-4', createTextNode(now.minusMonths(5).format('MM/yy')))
replaceVariable(templateAdf, 'm-5', createTextNode(now.minusMonths(6).format('MM/yy')))

// Replace resolved issue count variables linked to Jira JQL searches (last 6 months)
replaceVariable(templateAdf, 'e0' , createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(1).format('yyyy-MM')}-01 and resolved < ${now.format('yyyy-MM')}-01"))
replaceVariable(templateAdf, 'e-1', createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(2).format('yyyy-MM')}-01 and resolved < ${now.minusMonths(1).format('yyyy-MM')}-01"))
replaceVariable(templateAdf, 'e-2', createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(3).format('yyyy-MM')}-01 and resolved < ${now.minusMonths(2).format('yyyy-MM')}-01"))
replaceVariable(templateAdf, 'e-3', createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(4).format('yyyy-MM')}-01 and resolved < ${now.minusMonths(3).format('yyyy-MM')}-01"))
replaceVariable(templateAdf, 'e-4', createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(5).format('yyyy-MM')}-01 and resolved < ${now.minusMonths(4).format('yyyy-MM')}-01"))
replaceVariable(templateAdf, 'e-5', createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(6).format('yyyy-MM')}-01 and resolved < ${now.minusMonths(5).format('yyyy-MM')}-01"))

// Replace created and signed-offer issue count variables for the most recent month
replaceVariable(templateAdf, 'created', createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and created >= ${now.minusMonths(1).format('yyyy-MM')}-01 and created < ${now.format('yyyy-MM')}-01"))
replaceVariable(templateAdf, 'signed' , createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and created >= ${now.minusMonths(1).format('yyyy-MM')}-01 and created < ${now.format('yyyy-MM')}-01 and status was \"Signed offer\""))

// Replace per-role breakdown variables — adjust role values to match your Jira custom field options
replaceVariable(templateAdf, 'new_hr'        , createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(1).format('yyyy-MM')}-01 and resolved < ${now.format('yyyy-MM')}-01 and \"Role[Dropdown]\" = HR"))
replaceVariable(templateAdf, 'new_developers', createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(1).format('yyyy-MM')}-01 and resolved < ${now.format('yyyy-MM')}-01 and \"Role[Dropdown]\" = Developer"))
replaceVariable(templateAdf, 'new_sales'     , createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(1).format('yyyy-MM')}-01 and resolved < ${now.format('yyyy-MM')}-01 and \"Role[Dropdown]\" = Sales"))
replaceVariable(templateAdf, 'new_interns'   , createJqlLinkedTextNode("project in (${JIRA_PROJECT_KEY}) and resolved >= ${now.minusMonths(1).format('yyyy-MM')}-01 and resolved < ${now.format('yyyy-MM')}-01 and \"Role[Dropdown]\" = Intern"))

// At the point of writing, HAPI has yet to support setting page body. Watch for updates:
// https://docs.adaptavist.com/sr4cc/latest/hapi/work-with-pages#create-a-page
// Conf API v2: https://developer.atlassian.com/cloud/confluence/rest/v2/api-group-page/#api-pages-post
def createReportResponse = post("/wiki/api/v2/pages")
    .header('Content-Type', 'application/json')
    .body([
        spaceId: spaceId,
        title: "${now.minusMonths(1).format('MMM yy')} ${PAGE_BASE_TITLE}",
        parentId: PARENT_ID,
        body: [
            representation: 'atlas_doc_format',
            value: JsonOutput.toJson(templateAdf)
        ]
    ])
    .asObject(Map)
assert createReportResponse.status >= 200 && createReportResponse.status < 300 : "${createReportResponse.status}: ${createReportResponse.body}"

// Move the newly created report page to the top of the parent's children list for easy access.
// Remove the block below if you do not need this ordering behaviour.
def reportId = createReportResponse.body['id']

// HAPI: https://docs.adaptavist.com/sr4cc/latest/hapi/work-with-pages#query-pages
def pageChildrenResults = Pages.search("parent = ${PARENT_ID}", 1).toList()

// If parent has no children yet, or new page is already first, skip reordering
if (pageChildrenResults.isEmpty() || pageChildrenResults.first().id == reportId) {
    logger.info("Skipping page reordering: ${pageChildrenResults.isEmpty() ? 'no children exist' : 'page is already first'}")
    return
}

def firstChildPageId = pageChildrenResults.first().id

// Conf API v1: https://developer.atlassian.com/cloud/confluence/rest/v1/api-group-content---children-and-descendants/#api-wiki-rest-api-content-pageid-move-position-targetid-put
def putPageAsFirstChildResponse = put("/wiki/rest/api/content/$reportId/move/before/$firstChildPageId")
    .asObject(Map)
assert putPageAsFirstChildResponse.status >= 200 && putPageAsFirstChildResponse.status < 300 : "${putPageAsFirstChildResponse.status}: ${putPageAsFirstChildResponse.body}"

void replaceVariable(Object node, String varName, Map newNode) {
    def isTargetVar = { o ->
        o instanceof Map &&
        o.type == 'inlineExtension' &&
        o.attrs instanceof Map &&
        o.attrs.extensionType == 'com.atlassian.confluence.template' &&
        o.attrs.extensionKey  == 'variable' &&
        o.attrs.parameters['name'] == varName
    }

    if (node instanceof Map) {
        node.each { k, v ->
            if (isTargetVar(v)) {
                node[k] = newNode
            } else {
                replaceVariable(v, varName, newNode)
            }
        }
    } else if (node instanceof List) {
        for (def i = 0; i < node.size(); i++) {
            def v = node[i]
            if (isTargetVar(v)) {
                node[i] = newNode
            } else {
                replaceVariable(v, varName, newNode)
            }
        }
    }
}

Map createTextNode(String text) {
    [type: 'text', text: text]
}

Map createJqlLinkedTextNode(String jql) {
    // Queries the Jira instance for an approximate issue count matching the given JQL,
    // then returns a linked text node whose label is the count and whose href opens the issue list.
    // Jira API v3: https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-approximate-count-post
    def getIssuesCountResponse = post("${JIRA_BASE_URL}/rest/api/3/search/approximate-count")
        .basicAuth(AUTH_EMAIL, AUTH_API_TOKEN)
        .header('Content-Type', 'application/json')
        .body([jql: jql])
        .asObject(Map)
    assert getIssuesCountResponse.status >= 200 && getIssuesCountResponse.status < 300 : "${getIssuesCountResponse.status}: ${getIssuesCountResponse.body}"
    def count = getIssuesCountResponse.body['count']

    def encodedJql = URLEncoder.encode(jql, 'UTF-8').replace('+', '%20')
    def url = "${JIRA_BASE_URL}/issues?jql=${encodedJql}"
    [
        type : 'text',
        text : count.toString(),
        marks: [
            [
                type : 'link',
                attrs: [href: url]
            ]
        ]
    ]
}