Confluence Script Runner Macro - Connection between Confluence, JIRA and Bitbucket - SQL & REST API

[Ramakrishnan Srinivasan]

Hi,

 I am sharing this information for others to benefit. All our Atlassian products are in Server deployment. All applications are linked with Applink configuration.

Requirement:

• Develop a Confluence Script Runner macro for user to select JIRA Project
  • JIRA Projects listed using SQL query and HTML Select
• Map selected JIRA project to Bitbucket Project and repo slug
  • Get JIRA custom field value for this mapping
• Enable user to click a button, get and display 10 Tags data

Other use cases where this code might help:

• Using Script Runner, develop form in confluence
• Read the HTML values into Javascript for further processing
• SQL dB connection
• From Script Runner REST API call (with applinks)

Create a New Script Runner Macro in Confluence:

I have not used any parameter variable; only set one value for example but Unused

Then this code block

//MACRO Code

import org.apache.log4j.Level
import org.apache.log4j.Logger
def log = Logger.getLogger("com.onresolve.jira.groovy")
log.setLevel(Level.DEBUG)

//for SQL
import groovy.sql.Sql
import java.sql.Driver

//for BB APP Link
import com.atlassian.applinks.api.ApplicationLink
import com.atlassian.applinks.api.ApplicationLinkService
import com.atlassian.applinks.api.application.bitbucket.BitbucketApplicationType
import com.atlassian.applinks.api.application.jira.JiraApplicationType
import com.atlassian.sal.api.component.ComponentLocator

//Unused
def limitTags = parameters.limitTags as Integer ?: 10

def driver = Class.forName('org.postgresql.Driver').newInstance() as Driver

def props = new Properties()
props.setProperty("user", "<database username - make sure READ ONLY user>")
props.setProperty("password", "<password>")

def conn = driver.connect("jdbc:postgresql://dB_URL:5432/jira", props)
def sql = new Sql(conn)
def selectOptions = ""

//get filtered jira projects
try {
          sql.eachRow("SELECT p.pname, p.pkey\
                                 FROM nodeassociation na, projectcategory pc, project p\
                                 WHERE na.sink_node_id=pc.id\
                                 AND na.source_node_id=p.id\
                                 AND na.sink_node_entity='ProjectCategory'\
                                 AND na.association_type='ProjectCategory'\
                                 AND pc.cname IN ('ProjectCategory_x', 'ProjectCategory_y')\
                                 ORDER BY p.pname;") {
                                           selectOptions = selectOptions + "<option value='" + it['pkey'] + "'>" + it['pname'] + " (" + it['pkey'] + ")"+ "</option>"
}
} finally {
           sql.close()
}

def ApplicationLink getPrimaryBitBucketLink() {
            def applicationLinkService = ComponentLocator.getComponent(ApplicationLinkService.class)
            final ApplicationLink BBLink = applicationLinkService.getPrimaryApplicationLink(BitbucketApplicationType.class)
BBLink
}

def bb_app_link_id = getPrimaryBitBucketLink().getId()
def bb_url = getPrimaryBitBucketLink().getDisplayUrl()

def ApplicationLink getPrimaryJiraLink() {
       def applicationLinkService = ComponentLocator.getComponent(ApplicationLinkService.class)
       final ApplicationLink JIRALink = applicationLinkService.getPrimaryApplicationLink(JiraApplicationType.class)
JIRALink
}

def jira_app_link_id = getPrimaryJiraLink().getId()
def jira_url = getPrimaryJiraLink().getDisplayUrl()

//https://stiltsoft.com/blog/2017/11/table_filter_and_charts_with_team_calendars/
"Select Project: <select id='select_jira_project'><option value='None'>None</option>" + selectOptions + "</select>\
Number of Tags (max 10):<input id='input_limit_tags' value='10' size='4'>\
<button id='btn_get_bb_tags' class='aui-button aui-button-primary' disabled>Get BB Tags Data</button>\
<input id='input_bb_app_link_id' hidden value='" + bb_app_link_id + "'></input><input id='input_bb_url' hidden value='" + bb_url + "'></input>\
<input id='input_jira_app_link_id' hidden value='" + jira_app_link_id + "'></input><input id='input_jira_url' hidden value='" + jira_url + "'></input>\
POWN Issue Link:<a id='pown_link' href=''></a>\
<p id='p_spinner'>Click the button and please wait for loading the table...</p>\
<table width='80%' id='tbl_bb_tags' >\
<thead><tr><th>Project, repo</th><th>Tag/Version</th><th>Last Commit Date</th><th>Last Commit Message</th><th>Branch</th></tr></thead>\
<tbody></tbody>\
</table>\
"

//Macro Javascript code section
AJS.toInit(function() {
let buttonSelector = '#btn_get_bb_tags';
let button = document.querySelector(buttonSelector);

let selectSelector = '#select_jira_project';
let select = document.querySelector(selectSelector);

AJS.$("#p_spinner").css("visibility", "hidden");

function changeHandler(changeEvent) {
    var selectedProject = AJS.$('#select_jira_project').children("option:selected").val();
    AJS.$('#tbl_bb_tags tbody').empty();
    if(selectedProject == 'None') {
        AJS.$('#btn_get_bb_tags').prop('disabled', true);
    } else {
        AJS.$('#btn_get_bb_tags').prop('disabled', false);
        AJS.$("#p_spinner").css("visibility", "visible");
   }
}

function clickHandler(clickEvent) {

    /*AJAX call to get 'BB Project and Repo' JIRA custom field value of selectedProject    PROD_OWN Issue;
    PROD_OWN is a JIRA project containing one issue for each other JIRA Project with respective project's meta data which can be used in many other places
for example ProductX has one JIRA issue with custom field 'BB Project and Repo' - customfield_14962 with values BitbucketProject,repo_slug as comma separated*/

    var selectedProject = AJS.$('#select_jira_project').children("option:selected").val();

    var jira_app_link_id = AJS.$('#input_jira_app_link_id').val();
    var jira_url = AJS.$('#input_jira_url').val();

    var jira_url_y = AJS.Confluence.getBaseUrl() + '/plugins/servlet/applinks/proxy?appId=' + jira_app_link_id +'&path=' + jira_url + '/rest/api/2/search?jql=%22Product%20to%20Own%22%20%3D' + selectedProject + '&fields=customfield_14962';
    var jira_url_x = jira_url + '/rest/api/2/search?jql=%22Product%20to%20Own%22%20%3D' + selectedProject + '&fields=customfield_14962';

    var bb_proj = "";
    var bb_repo = "";

    AJS.$.ajax({
        url : jira_url_x,
        type: 'get',
        dataType: 'json',
        async: false,
        success: function(data) {
             var pown_key = data['issues'][0]['key'];
             AJS.$("#pown_link").attr("href", jira_url + '/browse/' + pown_key).text(pown_key);
             var bb_proj_repo_Array = data['issues'][0]['fields']['customfield_14962'].split(',');
             bb_proj = bb_proj_repo_Array[0];
             bb_repo = bb_proj_repo_Array[1];
        }
   });

    /*AJAX call to get tags from Bitbucket*/
    var limitTags = AJS.$('#input_limit_tags').val();
    if(parseInt(limitTags) > 10){
        limitTags = 10;
     }

     var bb_app_link_id = AJS.$('#input_bb_app_link_id').val();
     var bb_url = AJS.$('#input_bb_url').val();

     var bb_url_x = AJS.Confluence.getBaseUrl() + '/plugins/servlet/applinks/proxy?appId=' + bb_app_link_id +'&path=' + bb_url + '/rest/api/1.0/projects/';

     var bb_tags_url_x = AJS.Confluence.getBaseUrl() + '/plugins/servlet/applinks/proxy?appId=' + bb_app_link_id +'&path=' + bb_url + '/rest/api/1.0/projects/' + bb_proj + '/repos/' + bb_repo + '/tags?limit=' + limitTags;

     AJS.$.ajax({
         url : bb_tags_url_x,
         type: 'get',
         dataType: 'json',
         async: false,
        success: function(data) {
            var tableBody = AJS.$('#tbl_bb_tags tbody');
            var tag_name = "";
            var last_commit_date = "";
            var last_commit_message = "";
            var branch_name = "";

    AJS.$.each(data.values, function(index, thisTag) {
        tag_name = '<a href="' + bb_url + '/projects/' + bb_proj + '/repos/' + bb_repo + '/commits/'+ thisTag['latestCommit'] + '">' + thisTag['displayId'] + '</a>';

        /*AJAX to get branch for a given tag and its latestCommit*/
        var bb_branch_url_x = AJS.Confluence.getBaseUrl() + '/plugins/servlet/applinks/proxy?appId=' + bb_app_link_id +'&path=' + bb_url + '/rest/branch-utils/1.0/projects/' + bb_proj + '/repos/' + bb_repo + '/branches/info/' + thisTag['latestCommit'] + '?limit=1';
        AJS.$.ajax({
            url : bb_branch_url_x,
            type: 'get',
            dataType: 'json',
            async: false,
            success: function(data) {
                /*branch_name = data['values'][0]['displayId'];*/
                AJS.$.each(data.values, function(index, thisBranch) {
                    if(thisBranch['displayId'].indexOf('refs/heads/bugfix') === -1
                       && thisBranch['displayId'].indexOf('refs/heads/feature') === -1
                       && thisBranch['displayId'].indexOf('refs/heads/hotfix') === -1
                       && thisBranch['displayId'].indexOf('refs/heads/enh') === -1) {
                               branch_name = '<a href="' + bb_url + '/projects/' + bb_proj + '/repos/' + bb_repo + '/compare/commits?sourceBranch=refs%2Fheads%2F'+ thisBranch['displayId'] + '&targetBranch=refs%2Fheads%2Fmaster">' + thisBranch['displayId'] + '</a>';
                     }
            })
       }
    });

    var bb_commits_url_x = AJS.Confluence.getBaseUrl() + '/plugins/servlet/applinks/proxy?appId=' + bb_app_link_id +'&path=' + bb_url + '/rest/api/1.0/projects/' + bb_proj + '/repos/' + bb_repo + '/commits/' + thisTag['latestCommit'];
    AJS.$.ajax({
        url : bb_commits_url_x,
        type: 'get',
        dataType: 'json',
        async: false,
        success: function(data) {
            last_commit_message = data['message'];
            var committerDate = new Date(data['committerTimestamp']);
            var month = committerDate.getMonth()+1;
            if(committerDate.getDate() < 10) {
                var date = '0' + committerDate.getDate();
            } else {
                var date = committerDate.getDate();
            }

        last_commit_date = committerDate.getFullYear() + '-' + month + '-' + date;
        console.log(last_commit_date);
     }
 });
    var tr = AJS.$('<tr></tr>');
    tr.append(AJS.$('<td></td>').text(bb_proj + ', ' + bb_repo));
    tr.append(AJS.$('<td></td>').html(tag_name));
    tr.append(AJS.$('<td></td>').text(last_commit_date));
    tr.append(AJS.$('<td></td>').text(last_commit_message));
   tr.append(AJS.$('<td></td>').html(branch_name));
   tableBody.append(tr);
}); /*AJS.$.each(data.values, function(index, thisTag)*/
} /* success of tags AJAX call */
});
AJS.$("#p_spinner").css("visibility", "hidden");
}

button.addEventListener('click', clickHandler);
select.addEventListener('change', changeHandler);
})

//Macro CSS style section
#select_jira_project {
height : 30px;
background-color: #ffffcc;
}

table {
border-collapse: collapse;
}

table, th, td {
border: 1px solid black;
color: black;
}

table thead th {
color: black;
background-color: #ffd9b3;
}

.hidden{
visibility: hidden;
}

[Ramakrishnan Srinivasan]

Another way of doing this which I felt more easy if we want canned report as a macro output. The reason I took this path is because I could not figure out how to pass javascript/ajax call output back to groovy domain.

Use case,

• From confluence goto JIRA and get some custom field values of a Project (Bitbucket Project and Repo of that JIRA project)
• Between From and To date, get all Bitbucket Tags
• Using Bitbucket REST API for those tags, get latestcommit and hash
• using the latestcommit id, get the branch details and commit message

import org.apache.log4j.Level
import org.apache.log4j.Logger
def log = Logger.getLogger("com.onresolve.jira.groovy")
log.setLevel(Level.DEBUG)

//for SQL
import groovy.sql.Sql
import java.sql.Driver

//for BB, JIRA APP Link
import com.atlassian.applinks.api.ApplicationLink
import com.atlassian.applinks.api.ApplicationLinkService
import com.atlassian.applinks.api.application.bitbucket.BitbucketApplicationType
import com.atlassian.applinks.api.application.jira.JiraApplicationType
import com.atlassian.sal.api.component.ComponentLocator

import com.atlassian.confluence.event.events.space.SpaceCreateEvent
import com.atlassian.sal.api.net.Response
import com.atlassian.sal.api.net.ResponseException
import com.atlassian.sal.api.net.ResponseHandler
import groovy.json.JsonBuilder

import static com.atlassian.sal.api.net.Request.MethodType.GET

import static java.util.Calendar.*
import java.text.SimpleDateFormat

def ApplicationLink getPrimaryBitBucketLink() {
def applicationLinkService = ComponentLocator.getComponent(ApplicationLinkService.class)
final ApplicationLink BBLink = applicationLinkService.getPrimaryApplicationLink(BitbucketApplicationType.class)
BBLink
}

def appLinkService = ComponentLocator.getComponent(ApplicationLinkService)
def BitBucketLink = appLinkService.getPrimaryApplicationLink(BitbucketApplicationType)
def bb_applicationLinkRequestFactory = BitBucketLink.createAuthenticatedRequestFactory()

def ApplicationLink getPrimaryJiraLink() {
def applicationLinkService = ComponentLocator.getComponent(ApplicationLinkService.class)
final ApplicationLink JIRALink = applicationLinkService.getPrimaryApplicationLink(JiraApplicationType.class)
JIRALink
}

def driver = Class.forName('org.postgresql.Driver').newInstance() as Driver

def jira_props = new Properties()
jira_props.setProperty("user", "jirauser")
jira_props.setProperty("password", "jirauserpassword")

def jira_conn = driver.connect("jdbc:postgresql://jira_server_IP_address:5432/jira", jira_props)
def jira_sql = new Sql(jira_conn)

def bb_props = new Properties()
bb_props.setProperty("user", "bbuser")
bb_props.setProperty("password", "bbuserpassword")

def bb_conn = driver.connect("jdbc:postgresql://bb_server_IP_address:5432/bitbucket", bb_props)
def bb_sql = new Sql(bb_conn)
def pattern = "yyyy-MM-dd"

def jira_project_keys_str = parameters.jiraProjectKeys as String
def commit_from_date_x = parameters.activityFromDate
def commit_to_date_x = parameters.activityToDate
def filterNullRESTtags = parameters.filterTagsWithNullRestResponse
def filterNullRESTtags_qry

/*def filterNullRESTtags = "false"
def jira_project_keys_str = "PKEY"
def commit_from_date_x = "2019-12-01"
def commit_to_date_x = "2019-12-15"

commit_from_date_x = ""
commit_to_date_x = ""*/

if(filterNullRESTtags == 'true') {
filterNullRESTtags_qry = " AND rest_tag != 'null'"
} else {
filterNullRESTtags_qry = ""
}

def calLM = Calendar.getInstance()
calLM.add(Calendar.MONTH, -1)
//def lastMonth = calLM.getTime().format('MMM-yy')

def calCM = Calendar.getInstance()
int dayOfCurrentMonth = calCM[Calendar.DAY_OF_MONTH]
//def currentMonth = calCM.getTime().format('MMM-yy')

def commit_from_date
def commit_to_date
if(!commit_from_date_x || commit_from_date_x == "") {
commit_from_date = new SimpleDateFormat(pattern).parse(calLM.getTime().format('yyyy-MM-01'))
} else {
commit_from_date = new SimpleDateFormat(pattern).parse(commit_from_date_x)
}

if(!commit_to_date_x || commit_to_date_x == "") {
commit_to_date = new SimpleDateFormat(pattern).parse(calCM.getTime().format('yyyy-MM-dd'))
} else {
commit_to_date = new SimpleDateFormat(pattern).parse(commit_to_date_x)
}

def fromSQLTimestamp = commit_from_date.toTimestamp()
def toSQLTimestamp = commit_to_date.toTimestamp()

def jira_project_keys_list = jira_project_keys_str.tokenize(',')
//single quote each project key for IN clause in postgres query
def pkeys_list_of_strs = jira_project_keys_list.collect{"'" + it + "'"}
def pkeys_str = pkeys_list_of_strs.join(",")
//log.info(pkeys_str)

def bb_pkey_repo_map = [:]

try {
jira_sql.eachRow("SELECT p_pto.pkey, cfv_bb.stringvalue AS bb_proj_repo\
FROM jiraissue AS ji\
LEFT JOIN issuetype AS isstype ON isstype.id=ji.issuetype\
LEFT JOIN customfieldvalue AS cfv_pto ON cfv_pto.CUSTOMFIELD=(SELECT id FROM customfield WHERE cfname='Product to Own') AND cfv_pto.ISSUE=ji.id\
LEFT JOIN project AS p_pto ON p_pto.ID = cfv_pto.numbervalue::INTEGER\
LEFT JOIN nodeassociation AS na ON (p_pto.ID = na.SOURCE_NODE_ID AND na.ASSOCIATION_TYPE = 'ProjectCategory')\
LEFT JOIN projectcategory AS pc ON (na.SINK_NODE_ID = pc.ID)\
LEFT JOIN customfieldvalue AS cfv_bb ON cfv_bb.CUSTOMFIELD=(SELECT id FROM customfield WHERE cfname='BB Project & Repo Name') AND cfv_bb.ISSUE=ji.id\
WHERE ji.project=(SELECT id FROM project WHERE pkey='POWN')\
AND pc.cname IN ('Core/NGN', 'Core/NGN/Cloud Native Platform') AND isstype.pname='Product Ownership'\
AND p_pto.pkey IN (" + pkeys_str + ")\
ORDER BY p_pto.pname;") {
//log.info(it[0])
bb_pkey_repo_map.put(it[0], it[1])
}
} catch(Exception e) {
log.debug("Confluence Script Runner Bitbucket Tags vs Branch Macro POWN key problem for JIRA Projects " + pkeys_str)
} finally {
//jira_sql.close()
log.info("Finally of 'BB Project & Repo' JIRA custom field value section")
}

//log.info(bb_pkey_repo_map)
def bb_proj_list = []
bb_pkey_repo_map.each{ k, v -> "${bb_proj_list.add(v.toString().tokenize(',')[0])}" }
def bb_proj_list_of_strs = bb_proj_list.unique().collect{"'" + it + "'"}
def bb_proj_str = bb_proj_list_of_strs.join(",")
//log.info(bb_proj_str)

def bb_repo_list = []
bb_pkey_repo_map.each{ k, v -> "${bb_repo_list.add(v.toString().tokenize(',')[1])}" }
def bb_repo_list_of_strs = bb_repo_list.unique().collect{"'" + it + "'"}
def bb_repo_str = bb_repo_list_of_strs.join(",")
//log.info(bb_repo_str)

def tag_rest_api_data_str = ""
//GET ALL TAGS for given BB Project and Repo
try {
bb_sql.eachRow("SELECT bb_proj, repo_slug, bb_tag FROM (SELECT p.project_key AS bb_proj, repo.slug AS repo_slug, LTRIM(repo_push_ref.ref_id, 'refs/tags/') AS bb_tag\
FROM sta_repo_push_ref AS repo_push_ref\
LEFT JOIN sta_repo_activity AS repo_act ON repo_act.activity_id = repo_push_ref.activity_id\
LEFT JOIN public.repository AS repo ON repo.id = repo_act.repository_id\
LEFT JOIN project AS p on p.id = repo.project_id\
LEFT JOIN public.sta_activity AS act ON act.id = repo_push_ref.activity_id\
WHERE 1=1 AND p.project_key IN (" + bb_proj_str + ")\
AND repo.slug IN (" + bb_repo_str + ")\
AND repo_push_ref.ref_id LIKE '%refs/tags/%'\
AND act.created_timestamp::timestamp BETWEEN '" + fromSQLTimestamp + "'::timestamp AND '" + toSQLTimestamp + "'::timestamp\
GROUP BY p.project_key, repo.slug, repo_push_ref.ref_id) AS s1 ORDER BY s1.repo_slug;") {
//log.info(it['bb_proj'])
//log.info(it['repo_slug'])
//log.info(it['bb_tag'])
def rest_api_str = "rest/api/1.0/projects/" + it['bb_proj'] + "/repos/" + it['repo_slug'] + "/tags/" + it['bb_tag']
//log.info(rest_api_str)
def request = bb_applicationLinkRequestFactory.createRequest(GET, rest_api_str).addHeader("Content-Type", "application/json")
try {
request.execute(new ResponseHandler<Response>() {
@Override
void handle(Response response) throws ResponseException {
//log.info(response.statusCode)
//log.info(response.responseBodyAsString)
if (response.statusCode != 200) {
log.error("REST API call to tag name failed: ${response.responseBodyAsString}")
}
//"displayId" which is a tag
def displayId_x = response.responseBodyAsString.tokenize(',')[0]
if(displayId_x) {
displayId = displayId_x.tokenize(':')[1].replaceAll('"', "'")
//log.info(displayId)
}
def latestCommit = response.responseBodyAsString.tokenize(',')[3].tokenize(':')[1]
//log.info(latestCommit)
if(latestCommit != 'null') {
latestCommit = latestCommit.replaceAll('"', "'")
}
def hash = response.responseBodyAsString.tokenize(',')[5].tokenize(':')[1].replaceAll("}","")
//log.info(hash)
if(hash != 'null') {
hash = hash.replaceAll('"', "'")
}
//form the VALUES string to INSERT into tag_data_from_rest_api temporary table
tag_rest_api_data_str = tag_rest_api_data_str + "('" + it['bb_proj'] + "','" + it['repo_slug'] + "'," + displayId + ',' + latestCommit + ',' + hash + '),'
}
}) //END of request.execute
} catch(Exception e) {
log.debug("Tag not found " + rest_api_str)
} finally {
log.info("Finally for request execute")
}
} //END of bb_sql.eachRow
} finally {
//bb_sql.close()
log.info("Got Bitbucket tags")
}
//remove the last comma in tag_rest_api_data_str
tag_rest_api_data_str = tag_rest_api_data_str.substring(0, tag_rest_api_data_str.length() - 1)

//create a temporary table to hold tags rest api data from above block - tag_rest_api_data_str
bb_sql.execute("DROP TABLE IF EXISTS tag_data_from_rest_api;\
CREATE TEMPORARY TABLE tag_data_from_rest_api (\
bb_proj VARCHAR(128), repo_slug VARCHAR(128), tag VARCHAR(1024), latestcommit VARCHAR(40), hash VARCHAR(40));\
INSERT INTO tag_data_from_rest_api VALUES " + tag_rest_api_data_str +";")

//GET the message of latestcommit
def commit_message = ""
try {
bb_sql.eachRow("SELECT bb_proj, repo_slug, latestcommit\
FROM tag_data_from_rest_api\
WHERE latestcommit IS NOT NULL\
GROUP BY bb_proj, repo_slug, latestcommit;") {
//log.info(it['bb_proj'])
//log.info(it['repo_slug'])
//log.info(it['latestcommit'])
def rest_api_str = "rest/api/1.0/projects/" + it['bb_proj'] + "/repos/" + it['repo_slug'] + "/commits/" + it['latestcommit']
def request = bb_applicationLinkRequestFactory.createRequest(GET, rest_api_str).addHeader("Content-Type", "application/json")
try {
request.execute(new ResponseHandler<Response>() {
@Override
void handle(Response response) throws ResponseException {
//log.info(response.responseBodyAsString.tokenize(',')[20])
def message
if(response.responseBodyAsString.tokenize(',')[20]) {
message = response.responseBodyAsString.tokenize(',')[20].tokenize(':')[1].replaceAll("'", '"')
//message = "x"
} else {
message = ""
}
commit_message = commit_message + "('" + it['bb_proj'] + "','" + it['repo_slug'] + "','" + it['latestcommit'] + "','" + message + "'),"
} // END of void handle
}) //END of request.execute
} catch(Exception e) {
log.debug("tag_data_from_rest_api query issue ")
} finally {
log.info("Finally tag_data_from_rest_api query")
} //END of try
} //END of bb_sql.eachRow
} catch(Exception e) {
log.debug("tag_data_from_rest_api query issue ")
} finally {
log.info("Finally tag_data_from_rest_api query")
}
commit_message = commit_message.substring(0, commit_message.length() - 1)

//commit_message = "('x', 'y', 'z', 'xyz')"

bb_sql.execute("DROP TABLE IF EXISTS latestcommit_message;\
CREATE TEMPORARY TABLE latestcommit_message (\
bb_proj VARCHAR(128), repo_slug VARCHAR(128), latestcommit VARCHAR(40), message TEXT);\
INSERT INTO latestcommit_message VALUES " + commit_message +";")

//tags are used to get latestCommit and hash from REST API call
//matched to ref_id OR 'to_hash' column in sta_repo_push_ref
def master_query = "DROP TABLE IF EXISTS tmptbl_master_x;\
CREATE TEMPORARY TABLE tmptbl_master_x AS\
SELECT p.project_key, repo.slug, act.created_timestamp, act.user_id, usr.name\
,repo_push_ref.activity_id, repo_push_ref.ref_id, repo_push_ref.from_hash, repo_push_ref.to_hash\
,rest.tag AS rest_tag, rest.latestcommit AS rest_latestcommit, rest.hash AS rest_hash, msg.message AS rest_latestcommit_message\
FROM public.sta_repo_push_ref AS repo_push_ref\
LEFT JOIN sta_repo_activity AS repo_act ON repo_act.activity_id = repo_push_ref.activity_id\
LEFT JOIN public.repository AS repo ON repo.id = repo_act.repository_id\
LEFT JOIN project AS p on p.id = repo.project_id\
LEFT JOIN public.sta_activity AS act ON act.id = repo_push_ref.activity_id\
LEFT JOIN sta_normal_user AS usr ON usr.user_id = act.user_id\
LEFT JOIN tag_data_from_rest_api AS rest ON rest.tag = repo_push_ref.ref_id OR rest.latestcommit = repo_push_ref.to_hash\
LEFT JOIN latestcommit_message AS msg ON msg.latestcommit = rest.latestcommit\
WHERE 1=1 AND p.project_key IN (" + bb_proj_str + ")\
AND repo.slug IN (" + bb_repo_str + ")\
AND act.created_timestamp::timestamp BETWEEN '" + fromSQLTimestamp + "'::timestamp AND '" + toSQLTimestamp + "'::timestamp\
ORDER by p.project_key, repo.slug, repo_push_ref.activity_id DESC;\
DROP TABLE IF EXISTS tmptbl_branches;\
CREATE TEMPORARY TABLE tmptbl_branches AS\
SELECT * FROM tmptbl_master_x AS branch\
WHERE 1=1 AND branch.ref_id LIKE 'refs/heads/%';\
DROP TABLE IF EXISTS tmptbl_master;\
CREATE TEMPORARY TABLE tmptbl_master AS\
SELECT master.*, branch.ref_id AS branch\
FROM tmptbl_master_x AS master\
LEFT JOIN tmptbl_branches AS branch ON branch.to_hash=master.rest_latestcommit;\
"

bb_sql.execute(master_query)

def table_rows = ""

try {
bb_sql.eachRow("SELECT project_key, slug, created_timestamp, name\
,activity_id, ref_id, branch, from_hash, to_hash\
,rest_tag, rest_latestcommit, rest_hash, rest_latestcommit_message FROM tmptbl_master\
WHERE 1=1 " + filterNullRESTtags_qry + "\
GROUP BY project_key, slug, created_timestamp, name, activity_id, ref_id, branch,\
from_hash, to_hash, rest_tag, rest_latestcommit, rest_hash, rest_latestcommit_message\
ORDER BY project_key, slug, activity_id DESC;") {
//log.info(it['created_timestamp'])
table_rows = table_rows + "<tr><td>" + it['project_key'] + "</td>"\
+ "<td>" + it['slug'] + "</td>"\
+ "<td>" + it['created_timestamp'] + "</td>"\
+ "<td>" + it['name'] + "</td>"\
+ "<td>" + it['activity_id'] + "</td>"\
+ "<td class='class_tag_tds'>" + it['ref_id'] + "</td>"\
+ "<td>" + it['branch'] + "</td>"\
+ "<td>" + it['from_hash'] + "</td>"\
+ "<td class='class_tohash_tds'>" + it['to_hash'] + "</td>"\
+ "<td class='class_resptag_tds'>" + it['rest_tag'] + "</td>"\
+ "<td class='class_rest_latestcommit'>" + it['rest_latestcommit'] + "</td>"\
+ "<td class='class_rest_hash'>" + it['rest_hash'] + "</td>"\
+ "<td>" + it['rest_latestcommit_message'] + "</td></tr>"
}
} finally {
bb_sql.close()
}

//log.info(table_rows)
"<table width='80%' id='tbl_bb_tags' >\
<thead><tr><th>Project</th><th>repo</th><th>Created Timestamp</th><th>User Name</th>\
<th>activity_id</th><th>ref_id</th><th>branch</th><th>from_hash</th><th>to_hash</th>\
<th>rest_tag</th><th>rest_latestcommit</th><th>rest_hash</th><th>latestcommit_message</th></tr></thead>\
" + table_rows + "<tbody></tbody>\
</table>"

/*try {
jira_sql.eachRow("") {
log.info(it)

}
} finally {
jira_sql.close()
log.info("Got G2 G3s from JIRA")
} */

Macro JAVA SCRIPT Code - just to make some cells as links

AJS.toInit(function() {

AJS.$('.class_tag_tds').each(function() {
var prefix = AJS.$(this).text().substring(0, 10);
console.log(prefix);
var tagName = AJS.$(this).text().substring(10);
console.log(tagName);
if(prefix == 'refs/tags/') {
AJS.$(this).html("<a href='https://hostaddress/bb/rest/api/1.0/projects/" + AJS.$(this).prev().prev().prev().prev().prev().text() + "/repos/" + AJS.$(this).prev().prev().prev().prev().text() + "/tags/" + tagName + "'>" + AJS.$(this).text() + "</a>");
}
;
});

AJS.$('.class_resptag_tds').each(function() {
var prefix = AJS.$(this).text().substring(0, 10);
console.log(prefix);
var tagName = AJS.$(this).text().substring(10);
console.log(tagName);
if(prefix == 'refs/tags/') {
AJS.$(this).html("<a href='https://hostaddress/bb/rest/api/1.0/projects/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().prev().prev().text() + "/repos/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().prev().text() + "/tags/" + tagName + "'>" + AJS.$(this).text() + "</a>");
}
;
});

AJS.$('.class_tohash_tds').each(function() {
if( AJS.$(this).text() != '0000000000000000000000000000000000000000') {
AJS.$(this).html("<a href='https://hostaddress/bb/rest/api/1.0/projects/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().prev().text() + "/repos/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().text() + "/commits/" + this.innerHTML + "'>"+this.innerHTML+" </a>");
}
});

AJS.$('.class_rest_latestcommit').each(function() {
if( AJS.$(this).text() != 'null') {
AJS.$(this).html("<a href='https://hostaddress/bb/rest/api/1.0/projects/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().prev().prev().prev().text() + "/repos/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().prev().prev().text() + "/commits/" + this.innerHTML + "'>"+this.innerHTML+" </a>");
}
});

AJS.$('.class_rest_hash').each(function() {
if( AJS.$(this).text() != 'null') {
AJS.$(this).html("<a href='https://hostaddress/bb/rest/api/1.0/projects/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().prev().prev().prev().prev().text() + "/repos/" + AJS.$(this).prev().prev().prev().prev().prev().prev().prev().prev().prev().prev().text() + "/commits/" + this.innerHTML + "'>"+this.innerHTML+" </a>");
}
});
})

MACRO CSS style

#tbl_bb_tags {
font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 100%;
}

#tbl_bb_tags td, #tbl_bb_tags th {
border: 1px solid #ddd;
padding: 8px;
}

#tbl_bb_tags tr:nth-child(even){background-color: #f2f2f2;}

#tbl_bb_tags tr:hover {background-color: #ddd;}

#tbl_bb_tags th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #e6f3ff;
color: black;
}