Script to validate my invoices with shuhos

verify_shuho.rb

#!/usr/bin/env ruby
#encoding: UTF-8
#
#by James Rubingh james@wrive.com

require 'roo'

TRAN_TYPE = "翻訳"
CHECK_TYPE = "英文チェック"

if ARGV.length != 2
  puts "ERROR: Usage:"
  puts "  ./verify_.rb shuho.xls invoice.xls"
  exit
end

class Integer
  # To digit grouping
  def to_dg
    x = self.to_s
    v = x[0]
    for i in 1...x.length
      v += "," if (x.length - i) % 3 == 0
      v += x[i]
    end

    v
  end
end


#open files
shuho = Roo::Spreadsheet.open(ARGV[0])
invoice = Roo::Spreadsheet.open(ARGV[1])

def validate_invoice_entry_not_nil(value)
    return 1 if value.nil?
    return 1 if value[:type].nil?
    return 1 if value[:wordcount].nil?
    return 1 if value[:rate].nil?

    #everything is fine
    return false
end

#sum checks or translations, default to translations
def sum_row_type(cases, rowtype = TRAN_TYPE)
  total = 0
  cases.each { |value|

    next if validate_invoice_entry_not_nil(value)

    if value[:type] == rowtype
      total += value[:wordcount] * value[:rate]
    end

    if value[:total] != value[:wordcount] * value[:rate]
      puts "TOTAL IS INCORRECT at row #{value[:rownumber]} for #{value[:casenumber]}"
    end
  }

  total
end

#read excel file, create array of hashes for all entries
#invoice format as of 2023/06/24
def parse_invoice_sheet(invoice)
  cases = []
  invoice.sheet(0).each { |row|
    #check for 請求日
    if !row[6].nil? && (row[6].is_a? String) && row[6].match?(/請求日.*/)
      puts skb = "REQUEST DATE: #{row[6].match(/請求日[　, ]+(\d+年\d+月\d+日)/).captures}"
      puts ""
    end

    if (row[0].is_a? Integer) && (row[1] != 'ALP-')
      cases.push( { rownumber: row[0],
                    casenumber: row[1],
                    type: row[2],
                    date: row[3],
                    wordcount: row[4],
                    rate: row[5],
                    total: row[6],
      } )
    end
  }

  cases
end

#read excel file, create array of hashes for all entries
#shuho format as of 2023/06/24
def parse_shuho_sheet(shuho)
  cases = []
  shuho.sheets.each { |sheet|
    #shuho.sheet(sheet) is referring to the sheet by string: e.g. 'shuho.sheet('6月').each'
    shuho.sheet(sheet).each { |row|
      if (row[0].is_a? Date) && (row[1] != 'ALP-')
        cases.push( {
          date: row[0],
          casenumber: row[1],
          type: row[2],
          cwordcount: row[3],
          twordcount: row[4],
          author: row[6],
        } )
      end
    }
  }

  cases
end

def show_error(row: 0, text: "Mistake of some kind")
  if row > 0
    puts("row #{row}: #{text}")
  else
    puts("#{text}")
  end
end

def ensure_invoice_entries_are_in_shuho(invoice_cases, shuho_cases)
  number_of_errors = 0

  puts ""
  puts "Checking that Invoice Entries are in Shuho"
  invoice_cases.each{ |icase|
    result = shuho_cases.select {|s|
      (s[:casenumber] == icase[:casenumber]) && (s[:date] == icase[:date]) && (s[:type] == icase[:type])
    }
    if result.empty?
      #if it returns something
      number_of_errors += 1

      if !shuho_cases.select {|s|
        (s[:casenumber] == icase[:casenumber]) && (s[:date] == icase[:date]) && (s[:type] != icase[:type])
      }.empty?
      show_error(text: "#{icase[:casenumber]} is not in Shuho (Type is wrong)",
                 row: icase[:rownumber])

      elsif !shuho_cases.select {|s|
        (s[:casenumber] == icase[:casenumber]) && (s[:date] != icase[:date]) && (s[:type] == icase[:type])
      }.empty?
      show_error(text: "#{icase[:casenumber]} is not in Shuho (Date is wrong)",
                 row: icase[:rownumber])

      #if it doesn't return anything
      else
        show_error(text: "#{icase[:casenumber]} is not in Shuho (case number doesn't exist)",
                   row: icase[:rownumber])
      end
    end
  }

  if number_of_errors == 0
    puts "OKAY"
  end
end

def ensure_shuho_entries_are_in_invoice(invoice_cases, shuho_cases)
  number_of_errors = 0

  puts ""
  puts "Checking that Shuho Entries are in Invoice"

  scases = shuho_cases.select { |s|
    (s[:date] >= invoice_cases.first[:date]) && (s[:date] <= invoice_cases.last[:date])
  }

  scases.each { |s|
    result = invoice_cases.select {|i|
      (i[:casenumber] == s[:casenumber]) && (i[:date] == s[:date]) && (i[:type] == s[:type])
    }

    if result.empty?
      number_of_errors += 1
      show_error(text: "Date: #{s[:date]}, Case #{s[:casenumber]} is not in the Invoice")
    end
  }

  if number_of_errors == 0
    puts "OKAY"
  end
end

###
###  Program Entry Point
###
invoice_cases = parse_invoice_sheet(invoice)
shuho_cases = parse_shuho_sheet(shuho)

puts "Invoice Count: #{invoice_cases.count}"
puts "Shuho Count: #{shuho_cases.count}"

#Run some different checks on the data
ensure_invoice_entries_are_in_shuho(invoice_cases, shuho_cases)
ensure_shuho_entries_are_in_invoice(invoice_cases, shuho_cases)

puts ""
puts "The total translations are #{sum_row_type(invoice_cases, TRAN_TYPE).round().to_dg}"
puts "The total checks are #{sum_row_type(invoice_cases, CHECK_TYPE).round().to_dg}"
puts "The Total is ***  #{(sum_row_type(invoice_cases, TRAN_TYPE) + sum_row_type(invoice_cases, CHECK_TYPE) + 10000).round().to_dg}  ***"
puts "             *****************"

puts ""
puts "DONT FORGET TO EMAIL GIJYUTSUHA"