Skip to content

Instantly share code, notes, and snippets.

@drhuffman12
Last active February 26, 2020 01:14
Show Gist options
  • Save drhuffman12/971f57fd3df3a32d008ebf03b41a968b to your computer and use it in GitHub Desktop.
Save drhuffman12/971f57fd3df3a32d008ebf03b41a968b to your computer and use it in GitHub Desktop.
Jira ticket count jql generator (e.g.: for use in dashboard)
  1. Browse to https://linuxacademy.atlassian.net/issues/ to start entering your jql scripts.
  2. For each of the jql's do the following:
  • Paste the jql into the query box and click "Search"
  • Click "Save As" at the top and name it as per the comment at the beginning of the jql file.
  1. Browse to https://linuxacademy.atlassian.net/secure/Dashboard.jspa
  2. Click "... | Create Dashboard" from the top-right "..." drop down.
  3. Name and save the dashboard.
  4. Click "Add gadget".
  5. Click "Load all gadgets" and enter "pie" in the "Search" box.
  6. Click "Add gadget" as many times as you want and glick "Close" (e.g.: I do one for each of the 13 weeks and a couple for the team quarterly and one for my quarterly)
  7. Arrange gadgets into columns as desired.
  8. In your dashboard, edit your gadgets to use the applicable jql name you saved (in the "Project or Saved Filter" field) and select "Assignee" or "Status" (or etc) for "Statistic Type:required" and click "Save" (Optionally, click "Update every 15 minutes"; but I don't so as to minimize CPU load.)
require_relative 'ticket_count_jql_generator_all_statuses'
class MyTicketCountJqlGenerator
def self.run
q_start_date_str = '2020-01-01'
q_end_date_str = '2020-03-31'
team_projects = 'UAB, BSV, CAM, FB, LAE, CTB, NJ, VS'
all_team_members = %w[
my.name
his.name
other.person
our.lead
]
my_self = 'my.name'
TicketCountJqlGeneratorAllStatuses.run(q_start_date_str, q_end_date_str, team_projects, all_team_members, my_self)
end
end
MyTicketCountJqlGenerator.run
class TicketCountJqlGenerator
# The generated jql queries are put into the 'jql' folder.
attr_accessor :q_start_date, :q_end_date, :q_num, :q_year, :team_projects, :team_issue_types, :all_team_members, :my_self
# WK_END_DAY_NUM = 5 # aka Friday # if you want to force weekly split to be on a particular weekday; this might give you a more than 13 weeks in the quarter
JQL_FOLDER = 'jql'
def self.run(q_start_date_str, q_end_date_str, team_projects, team_issue_types, all_team_members, my_self)
djg = TicketCountJqlGenerator.new(q_start_date_str, q_end_date_str, team_projects, team_issue_types, all_team_members, my_self)
FileUtils.mkdir_p(JQL_FOLDER)
jql_name = "y#{djg.q_year}_q#{djg.q_num}_team"
local_filename = "#{JQL_FOLDER}/#{jql_name}.jql"
jql = djg.jql_quarterly
save(local_filename, jql_name, jql)
jql_name = "y#{djg.q_year}_q#{djg.q_num}_mine"
local_filename = "#{JQL_FOLDER}/#{jql_name}.jql"
jql = djg.jql_quarterly(only_me = true, include_in_progress = true)
save(local_filename, jql_name, jql)
djg.jql_weekly(only_me = true, include_in_progress = true).each_with_index do |key_ft_val_jql,index|
key_ft_val_jql.each_pair do |fr_to, jql|
from_date = fr_to[:from_date]
to_date = fr_to[:to_date]
wk_num_str = (index + 1).to_s.rjust(2, "0")
jql_name = "y#{djg.q_year}_q#{djg.q_num}_w#{wk_num_str}"
local_filename = "#{JQL_FOLDER}/#{jql_name}.#{from_date}_to_#{to_date}.jql"
save(local_filename, jql_name, jql)
end
end
end
def self.save(local_filename, jql_name, jql)
File.open(local_filename, 'w') {|f| f.write("-- #{jql_name}\n" + jql.strip + "\n") }
end
def initialize(q_start_date_str, q_end_date_str, team_projects, team_issue_types, all_team_members, my_self )
@q_start_date = ::Date.parse(q_start_date_str)
@q_num = (@q_start_date.month - 1) / 3
@q_year = @q_start_date.year
@q_end_date = ::Date.parse(q_end_date_str)
@team_projects = team_projects
@team_issue_types = team_issue_types
@all_team_members = all_team_members
@my_self = my_self
end
def generate_jql(from_date, to_date, only_me = false, include_in_progress = false)
in_progress = include_in_progress ? ', "In Progress"' : ''
assignees = only_me ? [@my_self] : @all_team_members
<<-JQL
project in (#{@team_projects}) AND issuetype in (standardIssueTypes(), subTaskIssueTypes()) AND status in (#{@team_issue_types + in_progress}) AND statusCategoryChangedDate >= #{from_date} AND statusCategoryChangedDate <= #{to_date} AND assignee in (#{assignees.join(', ')}) ORDER BY created DESC
JQL
end
def date_ranges
weeks = []
day_before_start_wday = (@q_start_date - 1).wday
current_week = {from_date: q_start_date, to_date: nil}
(q_start_date..q_end_date).each do |current_date|
# next if current_date.wday != WK_END_DAY_NUM # if you want to force weekly split to be on a particular weekday; this might give you a more than 13 weeks in the quarter
next if current_date.wday != day_before_start_wday
current_week[:to_date] = current_date
weeks << current_week
current_week = {from_date: current_date + 1, to_date: nil}
end
weeks
end
def jql_quarterly(only_me = false, include_in_progress = false)
generate_jql(q_start_date, q_end_date, only_me, include_in_progress)
end
def jql_weekly(only_me = false, include_in_progress = false)
date_ranges.map {|date_range| {date_range => generate_jql(date_range[:from_date],date_range[:to_date],only_me, include_in_progress)} }
end
end
class TicketCountJqlGeneratorAllStatuses
# The generated jql queries are put into the 'jql' folder.
attr_accessor :q_start_date, :q_end_date, :q_num, :q_year, :team_projects, :all_team_members, :my_self
# WK_END_DAY_NUM = 5 # aka Friday # if you want to force weekly split to be on a particular weekday; this might give you a more than 13 weeks in the quarter
JQL_FOLDER = 'jql'
def self.run(q_start_date_str, q_end_date_str, team_projects, all_team_members, my_self)
djg = TicketCountJqlGenerator.new(q_start_date_str, q_end_date_str, team_projects, all_team_members, my_self)
FileUtils.mkdir_p(JQL_FOLDER)
jql_name = "y#{djg.q_year}_q#{djg.q_num + 1}_team"
local_filename = "#{JQL_FOLDER}/#{jql_name}.jql"
jql = djg.jql_quarterly
save(local_filename, jql_name, jql)
jql_name = "y#{djg.q_year}_q#{djg.q_num + 1}_mine"
local_filename = "#{JQL_FOLDER}/#{jql_name}.jql"
jql = djg.jql_quarterly(only_me = true)
save(local_filename, jql_name, jql)
djg.jql_weekly(only_me = true).each_with_index do |key_ft_val_jql,index|
key_ft_val_jql.each_pair do |fr_to, jql|
from_date = fr_to[:from_date]
to_date = fr_to[:to_date]
wk_num_str = (index + 1).to_s.rjust(2, "0")
jql_name = "y#{djg.q_year}_q#{djg.q_num + 1}_w#{wk_num_str}"
local_filename = "#{JQL_FOLDER}/#{jql_name}.#{from_date}_to_#{to_date}.jql"
save(local_filename, jql_name, jql)
end
end
end
def self.save(local_filename, jql_name, jql)
File.open(local_filename, 'w') {|f| f.write("-- #{jql_name}\n" + jql.strip + "\n") }
end
def initialize(q_start_date_str, q_end_date_str, team_projects, all_team_members, my_self )
@q_start_date = ::Date.parse(q_start_date_str)
@q_num = (@q_start_date.month - 1) / 3
@q_year = @q_start_date.year
@q_end_date = ::Date.parse(q_end_date_str)
@team_projects = team_projects
@all_team_members = all_team_members
@my_self = my_self
end
def generate_jql(from_date, to_date, only_me = false)
puts "[from_date, to_date, only_me] == #{[from_date, to_date, only_me]}"
assignees = only_me ? [@my_self] : @all_team_members
<<-JQL
project in (#{@team_projects}) AND
issuetype in (standardIssueTypes(), subTaskIssueTypes()) AND
statusCategoryChangedDate > #{from_date - 1} AND
statusCategoryChangedDate <= #{to_date} AND
assignee in (#{assignees.join(', ')}) ORDER BY created DESC
JQL
end
def date_ranges
weeks = []
day_before_start_wday = (@q_start_date - 1).wday
current_week = {from_date: q_start_date, to_date: nil}
(q_start_date..q_end_date).each do |current_date|
# next if current_date.wday != WK_END_DAY_NUM # if you want to force weekly split to be on a particular weekday; this might give you a more than 13 weeks in the quarter
next if current_date.wday != day_before_start_wday
current_week[:to_date] = current_date
weeks << current_week
current_week = {from_date: current_date + 1, to_date: nil}
end
weeks
end
def jql_quarterly(only_me = false)
generate_jql(q_start_date, q_end_date, only_me)
end
def jql_weekly(only_me = false)
date_ranges.map {|date_range| {date_range => generate_jql(date_range[:from_date],date_range[:to_date],only_me)} }
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment