import java.sql.*;
import java.util.*;
import java.util.Map;
import java.util.Map.Entry;
import groovy.sql.Sql
import java.math.BigDecimal;
import com.atlassian.jira.util.json.JSONArray;
import com.atlassian.jira.util.json.JSONObject;
import com.atlassian.jira.component.ComponentAccessor;
import com.decadis.jira.xchart.utils.PastellColorUtil;
// JDBC Driver - here MySQL
String driverName = "com.mysql.jdbc.Driver";
Driver driver = Class.forName(driverName).newInstance() as Driver
// JDBC URL
String url = "jdbc:mysql://localhost/jira?useUnicode=yes&characterEncoding=UTF-8";
// SQL Authentication
Properties props = new Properties();
props.setProperty("user", "user");
props.setProperty("password", "password");
Connection c = driver.connect(url,props);
Sql sql = new Sql(c);
// SQL Statement
def sqlString = """ select * from label """;
Map map = new TreeMap<String,BigDecimal>();
// Iterate over each row, select rows by it.{rowName}
sql.eachRow(sqlString)
{
// handle the data input
if(map.get(it.label))
{
map.put(it.label,map.get(it.label).add(BigDecimal.ONE))
}
else
{
map.put(it.label,BigDecimal.ONE)
}
}
c.close();
// create the c3 data
JSONObject chartData = new JSONObject();
JSONObject data = new JSONObject();
JSONArray colors = new JSONArray();
JSONArray columns = new JSONArray();
JSONArray column = new JSONArray();
JSONArray categories = new JSONArray();
// first colum entry must be the name of the data row - here labels
column.put(ComponentAccessor.getJiraAuthenticationContext().getI18nHelper().getText("admin.customfield.type.labels.name"));
int i = 0;
// add the collected values to the json array
for ( Entry<String,BigDecimal> entry : map.entrySet() )
{
// add the value
column.put(entry.getValue().intValue());
//add the key
categories.put(entry.getKey());
// add a custom color
colors.put(PastellColorUtil.toHex(PastellColorUtil.getNiceColor(i++ * 0.23)));
}
// combine the json objects
columns.put(column);
data.put("columns", columns);
data.put("type", "bar");
JSONObject axis = new JSONObject();
JSONObject x = new JSONObject();
x.put("type", "category");
x.put("categories", categories);
axis.put("x", x);
axis.put("rotated", "true");
chartData.put("data", data);
chartData.put("axis", axis);
chartData.put("colors",colors);
// return the jsonobject as string
return chartData.toString() |