require 'spreadsheet'
require 'win32ole'
require 'tiny_tds'

#SQL Query
sql = "SELECT NFDOCO, NFSAID, NFMPOS, NFBITB, NFSRVC, NFMRCR, NFMRPR, NFCNSM, NFCUM, NFBLCN, NFDBPD, NFBITM, NFBLRT, NFAG FROM TESTDTA.F19811
where (NFDOCO = 37969 and NFSAID = 28413) or (NFDOCO = 38006 and NFSAID = 28452) or (NFDOCO = 38007 and NFSAID = 28453)
or (NFDOCO = 30013 and NFSAID = 20255) or (NFDOCO = 30033 and NFSAID = 20275) or (NFDOCO = 30040 and NFSAID = 20282)
or (NFDOCO = 30088 and NFSAID = 20332) or (NFDOCO = 30104 and NFSAID = 20348) or (NFDOCO = 30088 and NFSAID = 20322)
or (NFDOCO = 30104 and NFSAID = 20348) or (NFDOCO = 30105 and NFSAID = 20349) or (NFDOCO = 30119 and NFSAID = 20363)
or (NFDOCO = 30122 and NFSAID = 20367) or (NFDOCO = 30122 and NFSAID = 20368) or (NFDOCO = 30122 and NFSAID = 0)
or (NFDOCO = 30128 and NFSAID = 20372) or (NFDOCO = 47855 and NFSAID = 39471) or (NFDOCO = 47897 and NFSAID = 39513)
or (NFDOCO = 74227 and NFSAID = 66666) or (NFDOCO = 99243 and NFSAID = 29549) or (NFDOCO = 174983 and NFSAID = 39501)
or (NFDOCO = 99243 and NFSAID = 29549) or (NFDOCO = 174983 and NFSAID = 39501) or (NFDOCO = 175926 and NFSAID = 110474)
or (NFDOCO = 176039 and NFSAID = 85223) or (NFDOCO = 190815 and NFSAID = 117686) or (NFDOCO = 218231 and NFSAID = 122307)
or (NFDOCO = 246073 and NFSAID = 0) or (NFDOCO = 246113 and NFSAID = 0)"

#~~~~~~~~~~DATABASE CREDENTIALS~~~~~~~~~~~~~~~~~~#
uid1 = "TESTDTA"           #User ID
pwd1 = "TESTDTA"           #Password
ds   = "IDAUTILDB\\E900"   #Data server
db   = "JDE_DEVELOPMENT"   #Database

#--------------LOGIN TO THE DATABASE-----------#
client = TinyTds::Client.new(:username => uid1, :password => pwd1, :dataserver => ds, :database => db)

if (ARGV[0].to_s.downcase == "master")
  result = client.execute(sql)
  results = result.each(:symbolize_keys => true, :as => :array, :cache_rows => true, :empty_sets => true) do |rowset| end

  #CREATE A NEW WORKBOOK
  book = Spreadsheet::Workbook.new
  sheet = book.create_worksheet

  #TRANSCRIBE THE SQL QUERY
  for i in (0..results.count)
    for j in (0..results[i].to_a.count)
      temp = results[i].to_a.at(j).to_s
      sheet.row(i).push(temp)
    end
  end

  #CHECK THE VALIDITY OF THE FILE NAME TO BE WRITTEN
  begin
    if (ARGV[1].to_s.end_with?(".xls"))
    else
      raise
    end
  rescue
    puts "The file name is invalid, please be sure it ends with '.xls'"
    exit!
  end

  #SAVE THE BOOK
  begin
    book.write(ARGV[1])
  rescue
    puts "The file path to create a new spreadsheet is invalid, please try again."
    exit!
  end

elsif (ARGV[0].to_s.downcase == "check")
  #----------GET THE CORRECT SQL QUERY-----------#
  client = TinyTds::Client.new(:username => uid1, :password => pwd1, :dataserver => ds, :database => db)

  result = client.execute(sql)
  results = result.each(:symbolize_keys => true, :as => :array, :cache_rows => true, :empty_sets => true) do |rowset| end

  #ENSURE THAT THE FILE EXISTS
  begin
    book = Spreadsheet.open(ARGV[1],'rb')
  rescue
    puts "The file path is invalid, please try again."
    exit!
  end
  sheet = book.worksheet(0)

  #COMPARE THE FILES
  for j in (0...sheet.count-1)
    for i in (0...sheet.row(j).length)
      #If it is blank in the SQL query
      if results[j].at(i) == 0
        print "blank "
        if sheet.row(j).at(i).to_s != ""
          print "Error: "
          print sheet.row(j).to_a.at(i)
          print " "
          puts results[j].to_a.at(i)
        end
      else
        if sheet.row(j).at(i).to_s == results[j].at(i).to_s
        else
          print "Error: "
          print sheet.row(j).to_a.at(i)
          print " "
          puts results[j].to_a.at(i)
        end
      end

    end
  end

  #IF THE COMMANDLINE IS INCORRECT
else
  puts "Invalid arguments"
  puts "It must be in the form:  ./BillingDetailReport.rb \"LocationOfMasterSheet\" \"MasterOrCheck\""
  puts "Input must be 'Master' or 'Check'"
  puts "'Master' for creating the original database call for future checks"
  puts "'Check' for checking the database now against a previously made call"
  exit!
end