ext-competence.rb

#!/usr/bin/env ruby
require "pp"
require "roo"

if ARGV.size < 2
    puts "USAGE: #$0 competence.xlsx SIR201911xxx.xlsx"
    exit
end

subject_mappings = {
  #知識情報概論
  GA14101: %w[ GA14111 GA14121 ],
  #知識情報演習I II III
  GE11002: %w[ GE11012 GE11022 ],
  GE11102: %w[ GE11112 GE11122 ], 
  GE11202: %w[ GE11212 GE11222 ],
  #情報数学
  GA15141: %w[ GE10811 ],
  #専門英語A1
  GE11602: %w[ GE11612 GE11622 GE11632 ],
  #専門英語A2
  GE11702: %w[ GE11712 GE11722 GE11732 ],
  #専門英語B
  GE50102: %w[ GE50712 GE50722 GE50732 ],
  #専門英語C
  GE50202: %w[ GE50812 GE50822 GE50832 ],
}
competence = {}
xlsx = Roo::Excelx.new(ARGV[0])
xlsx.default_sheet = "TWINSコンピテンス設定"
xlsx.each(headers: true, pad_cells: true) do |row|
    p row
    %w[ g-competence1	g-competence2	g-competence3	g-competence4	g-competence5
        g-competence6	s-competence1	s-competence2	s-competence3	s-competence4	s-competence5
    ].each do |c|
        competence[row["科目マスタ"]] ||= {}
        competence[row["科目マスタ"]][c] = row[c].to_f
        if subject_mappings.has_key? row["科目マスタ"].intern
            subject_mappings[row["科目マスタ"].intern].each do |code|
                competence[code] ||= {}
                competence[code][c] = row[c].to_f    
            end
        end
        competence[row["course number"]] ||= {}
        competence[row["course number"]][c] = row[c].to_f
    end
end

results = {}
xlsx = Roo::Excelx.new(ARGV[1])
xlsx.each(headers: true, pad_cells: true) do |row|
    next if row["総合評価"] == "D"
    if competence[row["科目番号"]]
        results[row["開講年度"]] ||= {}
        %w[ 
            g-competence1	g-competence2	g-competence3	g-competence4	g-competence5
            g-competence6	s-competence1	s-competence2	s-competence3	s-competence4 s-competence5
        ].each do |c|
            results[row["開講年度"]][c] ||= 0
            results[row["開講年度"]][c] += competence[row["科目番号"]][c] * row["単位数"].to_i
        end
    else
        warn "#{row["科目番号"]} not found"
    end
end
sum = [ results.keys.sort.first ]
results.keys.sort[1..-1].each do |y|
    sum << y
    results[y].each do |c, v|
        total = 0
        sum.each do |y2|
            total += results[y2][c]
        end
        results[ sum.join("+") ] ||= {}
        results[ sum.join("+") ][c]  = total
    end
end
pp results