api-calls-json-to-csv-example.md

#!/usr/bin/env ruby

# frozen_string_literal: true

require "csv"
require "jwt"
require "octokit"
require "optparse"
require "spreadsheet"
require "yaml"

# parse CLI options
options = {config: ".config.yml"}
OptionParser.new do |opts|
  opts.banner = "Usage: #{__FILE__} [options]"

  opts.on("-c", "--config PATH", String, "$PATH to config file") do |v|
    if File.file?(v)
      options[:config] = v
    else
      puts "Error: invalid config file path\n\n"
      puts opts
      exit 1
    end
  end
  opts.on_tail("-h", "--help", "show this message") do
    puts opts
    exit
  end
  opts.parse!
end

# load yaml config
config = YAML.safe_load(File.read(options[:config]))

# create API requests
class AuditGitHubAPI
  # init
  def initialize(ghc, org, repo_type)
    @gh = ghc
    @org = org
    @repo_type = repo_type
  end

  # repos for each org
  def repos
    repos = []
    @gh.org_repos(@org, per_page: 100, type: @repo_type).each do |repo|
      repos.push(repo)
    end
    repos
  end

  # branches for each repo
  def branches
    branches = {}
    repos.each do |repo|
      @gh.branches(repo.full_name).each do |ref|
        branches[repo.full_name] ||= []
        branches[repo.full_name] << ref
      end
    end
    branches
  end

  # commits for each repo/branch
  def commits
    commits = {}
    branches.each do |repo, branch|
      commits[repo] ||= {}
      branch.each do |ref|
        @gh.commits(repo, ref[:name]).each do |commit|
          commits[repo][ref[:name]] ||= []
          commits[repo][ref[:name]] << commit
        end
      end
    end
    commits
  end

  # issue for each repo
  def issues
    issues = {}
    repos.each do |repo|
      @gh.issues(repo.full_name).each do |issue|
        issues[repo.full_name] ||= []
        issues[repo.full_name] << issue
      end
    end
    issues
  end

  # issue comments for each issue
  def issues_comments
    issues_comments = {}
    repos.each do |repo|
      @gh.issues(repo.full_name).each do |issue_comment|
        issues_comments[repo.full_name] ||= []
        issues_comments[repo.full_name] << issue_comment
      end
    end
    issues_comments
  end

  # pull requests for each repo
  def pull_requests
    pull_requests = {}
    repos.each do |repo|
      @gh.pull_requests(repo.full_name, state: "all").each do |pull_request|
        pull_requests[repo.full_name] ||= []
        pull_requests[repo.full_name] << pull_request
      end
    end
    pull_requests
  end
end

# create CSV reports
# TODO: boilerplate/duplication ahead
class AuditGitHubCSV
  # init
  def initialize(ghc, org, repo_type)
    @gh = AuditGitHubAPI.new(ghc, org, repo_type)
    @org = org
  end

  # write audit-commits.csv file
  def commits
    CSV.open("#{@org}-audit-commits.csv", "wb") do |csv|
      csv << %w[org/repo branch sha date name email]
      @gh.commits.each do |repo, ref|
        ref.each do |branch, commits|
          commits.each do |commit|
            csv << [
              repo,
              branch,
              commit[:sha],
              commit[:commit][:author][:date],
              commit[:commit][:author][:name],
              commit[:commit][:author][:email]
            ]
          end
        end
      end
    end
  end

  # write audit-issues.csv file
  def issues
    CSV.open("#{@org}-audit-issues.csv", "wb") do |csv|
      csv << %w[org/repo id state title date name]
      @gh.issues.each do |repo, issues|
        issues.each do |issue|
          csv << [
            repo,
            issue[:number],
            issue[:state],
            issue[:title],
            issue[:created_at],
            issue[:user][:login]
          ]
        end
      end
    end
  end

  # write audit-issues-comments.csv file
  def issues_comments
    CSV.open("#{@org}-audit-issues_comments.csv", "wb") do |csv|
      csv << %w[org/repo id state title date name]
      @gh.issues_comments.each do |repo, issues|
        issues.each do |issue|
          csv << [
            repo,
            issue[:number],
            issue[:state],
            issue[:title],
            issue[:created_at],
            issue[:user][:login]
          ]
        end
      end
    end
  end

  # write audit-pull-requests.csv file
  def pull_requests
    CSV.open("#{@org}-audit-pull_requests.csv", "wb") do |csv|
      csv << %w[org/repo id state title date name]
      @gh.pull_requests.each do |repo, issues|
        issues.each do |issue|
          csv << [
            repo,
            issue[:number],
            issue[:state],
            issue[:title],
            issue[:created_at],
            issue[:user][:login]
          ]
        end
      end
    end
  end
end

# create CSV reports
class AuditGitHubReport
  # create xls workbook
  def workbook
    book = Spreadsheet::Workbook.new
    head = Spreadsheet::Format.new \
      weight: "bold",
      horizontal_align: "center",
      locked: true
    [book, head]
  end

  # create xls worksheet
  def worksheet(org, book, head, name)
    sheet = book.create_worksheet(name: name)
    sheet.row(0).default_format = head

    CSV.open("#{org}-audit-#{name}.csv", "r") do |csv|
      csv.each_with_index do |row, i|
        sheet.row(i).replace(row)
      end
    end
  end

  # create xls workbook with worksheets
  def generate(org)
    book, head = workbook
    Dir.glob("#{org}*").each do |file|
      sheet = File.basename(file, File.extname(file)).split(/#{org}-audit-/).last
      worksheet(org, book, head, sheet)
    end
    book.write "#{org}-audit.xls"
  end
end

# create JWT token for Github App
def jwt_token(pem, app_id, installation_id, config)
  private_pem = File.read(pem)
  private_key = OpenSSL::PKey::RSA.new(private_pem)
  payload = {
    iat: Integer(Time.now),
    exp: Integer(Time.now + 600),
    iss: app_id
  }
  if config.key?("url")
    Octokit.configure do |c|
      c.api_endpoint = config["url"]
    end
  end
  jwt_client = Octokit::Client.new \
    bearer_token: JWT.encode(payload, private_key, "RS256")
  access_token = jwt_client.create_installation_access_token(installation_id)
  access_token[:token]
end

# connect to API using pat or github app
def github_client(auth, config)
  access_token =
    if auth.key?("access_token")
      auth["access_token"]
    elsif auth.key?("pem") && auth.key?("app_id") && auth.key?("installation_id")
      jwt_token(auth["pem"], auth["app_id"], auth["installation_id"], config)
    else
      puts "Error: invalid auth config"
      exit 1
    end

  if config.key?("url")
    Octokit.configure do |c|
      c.api_endpoint = config["url"]
    end
  end

  gh = Octokit::Client.new \
    access_token: access_token,
    auto_paginate: true
  gh
end

# main loop, iterate config file
config["org"].each do |org, value|
  audit = AuditGitHubCSV.new \
    github_client(value["auth"], value),
    org,
    value["repo"]

  # run reports
  audit.commits
  audit.issues
  audit.issues_comments
  audit.pull_requests

  # create excel file
  report = AuditGitHubReport.new
  report.generate(org)
end