combine-quickbooks-p-n-l.rb

# bundle exec ruby scripts/combine-quickbooks-p-n-l.rb report-1.csv report-2.csv
#
# Run and export the two reports in Quickbooks, then feed into this script.

require 'csv'

class CombineQuickbooksPNL
  def initialize(unspecified_csv_file, corporate_csv_file)
    @unspecified_csv_file = unspecified_csv_file
    @corporate_csv_file = corporate_csv_file
  end

  def run
    unspecified_data = csv_rows_keyed_on_column(@unspecified_csv_file)
    corporate_data = csv_rows_keyed_on_column(@corporate_csv_file)

    combined_data = merge_reports(unspecified_data, corporate_data)

    unspecified_keys = csv_values_for_column(@unspecified_csv_file, 0)

    raise StandardError, "Unexpected combined data size" unless unspecified_keys.size == combined_data.size

    CSV.open("combined-profit-and-loss.csv", "wb") do |output_csv|
      unspecified_keys.each do |key|
        row = combined_data[key]
        output_csv << [space_preserved(key), *row]
      end
    end
  end

  private

  def merge_reports(unspecified_data, corporate_data)
    unspecified_data.merge(corporate_data) do |key, unspecified_row, corporate_row|
      raise StandardError, "rows are not the same size for key=#{key}" unless unspecified_row.size == corporate_row.size

      combined_row = []
      unspecified_row.each_with_index do |unspecified_column, i|
        corporate_column = corporate_row[i]
        combined_row[i] = numeric(unspecified_column) + numeric(corporate_column)
      end

      combined_row
    end
  end

  def csv_rows_keyed_on_column(file, key_column = 0)
    data = {}

    CSV.foreach(file, headers: true, encoding: 'ISO-8859-1', col_sep: "\t") do |row|
      data[row[key_column]] = row[1..]
    end

    return data
  end

  def csv_values_for_column(file, key_column = 0)
    values = []

    CSV.foreach(file, headers: true, encoding: 'ISO-8859-1', col_sep: "\t") do |row|
      values << row[key_column]
    end

    return values
  end

  def numeric(value)
    return 0 if value.nil?

    return value.to_s.strip.delete('$').delete(',').to_f
  end

  def space_preserved(string)
    prefix_to_prevent_google_sheets_from_removing_leading_spaces = '|'
    "#{prefix_to_prevent_google_sheets_from_removing_leading_spaces}#{string}"
  end
end

script = CombineQuickbooksPNL.new(*ARGV)
script.run