Jira ticket count jql generator (e.g.: for use in dashboard)

README.md

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.

3. Browse to https://linuxacademy.atlassian.net/secure/Dashboard.jspa
4. Click "... | Create Dashboard" from the top-right "..." drop down.
5. Name and save the dashboard.
6. Click "Add gadget".
7. Click "Load all gadgets" and enter "pie" in the "Search" box.
8. 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)
9. Arrange gadgets into columns as desired.
10. 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.)

example_usage.rb

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

ticket_count_jql_generator.rb

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

ticket_count_jql_generator_all_statuses.rb

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