#!/usr/bin/env ruby
# == Synopsis
#
# xls2sql converts XLS files used as DB to SQL commands for import.
#
#  Assumptions:
#  1. First row [1] contains field names
#  2. Starting from row 2 foreward, one row per field.
#
#  Based on this source:
#
#   |    A    |    B    |    C    |
#  -+---------+---------+---------|
#  1| Field 1 | Field 2 | Field 3 |
#  -+---------+---------+---------|
#  2| foo     | bar     | ipsum   |
#  -+---------+---------+---------|
#  3| lorem   | etc     | etc     |
#  -+---------+---------+---------|

#  Build:
#  Create Table SomeTable (
#  `Field 1` varchar(255),
#  `Field 2` varchar(255),
#  `Field 3` varchar(255));
#
#  Insert Into Sometable 
#  (`Field 1`,`Field 2`,`Field 3`)
#  Values
#  (foo,bar, ipsum),
#  (lorem,etc,etc);
# 
#
#  By Carlos Troncoso Phillips
#  schmickcl@gmail.com
#  https://wiki.ubuntu.com/CarlosTroncoso
# == Usage
# 
# xls2sql -x <file> -s <file> [-t <table> -w <workbook>]
#
#  -h, --help:
#        Shows this help
#
#      --synopsis:
#        Short explanation of this program
#
#  -x, --xls:
#        xls input file
#
#  -s, --sql:
#        sql file to output
#
#  -t, --table:
#        table name to create (Defaults to source filename)
#        use '' with tables with spaces.
#
#      --size:
#        Field size to create.
#        xls2sql creates all fields as varchar(255),
#        but 255 is the default value. Use other value by setting --size.
#
#  -w, --workbook:
#        workbook name (A.K.A. Sheet) (Defaults to first workbook)
#
#  Note: if workbook has any linking, linked cells won't be parsed acurately.
#        Copy and paste as value to fix sheet.
#


require 'getoptlong'
require 'rdoc/usage'
require 'rubygems'
require 'spreadsheet' 

opts = GetoptLong.new(
[ '--help', '-h', GetoptLong::NO_ARGUMENT ],
[ '--synopsis', GetoptLong::NO_ARGUMENT ],
[ '--xls', '-x', GetoptLong::REQUIRED_ARGUMENT ],
[ '--sql', '-s', GetoptLong::REQUIRED_ARGUMENT ],
[ '--workbook', '-w', GetoptLong::OPTIONAL_ARGUMENT ],
[ '--size', GetoptLong::OPTIONAL_ARGUMENT ],
[ '--table','-t', GetoptLong::OPTIONAL_ARGUMENT ]

)

xls = nil
sql = nil
workbook = nil
size = nil
table = nil
opts.each do |opt, arg|
  case opt
    when '--help'
      RDoc::usage('Usage')
    when '--synopsis'
      RDoc::usage('Synopsis')
    when '--xls'
      if arg == ''
        puts '--xls needs a file as parameter (i.e.: --xls file.xls)'
        puts 'program halted.'
        exit 1        
      else
        xls = arg
      end
    when '--sql'
      if arg == ''
        puts '--sql needs a file as parameter (i.e.: --sql file.sql)'
        puts 'program halted.'
        exit 1  
      else
        sql = arg
      end
    when '--table'
      if arg == ''
        puts '--table needs a file as parameter (i.e.: --table \'my table\')'
        puts 'program halted.'
        exit 1  
      else
        table = arg
      end
    when '--workbook'
      if arg == ''
        puts 'no workbook option, defaulting to first sheet'
        workbook = 0
      else
        workbook = arg
      end
    when '--size'
      if arg == ''
        puts 'no size option, defaulting to 255'
        size = 255
      else
        size = arg
      end
  end
end
if !xls
  puts "Mandatory option --xls not found"
  puts "program halted."
  exit 1
end
if !sql
  puts "Mandatory option --sql not found"
  puts "program halted."
  exit 1
end
if !workbook
  puts 'no workbook option, defaulting to first sheet'
  workbook = 0 
end
if !size
puts 'no size option, defaulting to 255'
  size = 255
end
if !table
  puts "no table option, defaulting to #{xls[/^(.*)\./,1]}"
  table = xls[/^(.*)\./,1]
end



puts "Reading file #{xls}"
  if File.file?(xls)
    book = Spreadsheet.open(xls)
    puts 'File read.'
  else
    puts "File #{xls} doesn't exist."
    puts "program halted."
    exit 1
  end

puts "Finding first sheet..."
  sheet=book.worksheet(workbook)
  puts "Using #{workbook} as default sheet..."

puts "Finding last column with data..."
  firstrow=sheet.row(0)
  while firstrow[-1].nil?
    firstrow.pop
  end
  last_column=firstrow.length-1
  puts "Last column is #{last_column+1}"

puts "Creating array for data..."
  sql_field_def=""
  fields=[]

puts "Starting Loop..."
  0.upto(last_column) do |column|
#    print "Working on column #{column+1}. "
    data = sheet[0,column]
#    puts "Found #{data}"
    if data
      fields << data.strip
      sql_field_def << "`#{data.strip}` varchar(#{size}),\n"
    end
  end

#Cleanup
sql_field_def.chomp!(",\n")
command=<<EOS
Drop table if exists `#{table}`;
Create Table if not exists `#{table}` (
#{sql_field_def});

EOS
puts "Create loop finished..."

puts "Starting Data Creation Section..."
  command << "Insert Into `#{table}`\n(#{fields.collect{|x| "`" + x + "`," }.to_s.chop})\nValues\n"

  insert_values=''
  last_row = sheet.row_count-1
  1.upto(last_row) do |row|
  if (row%100==0)
    print "\e[0KProcessing record #{row} of #{last_row}...   #{((row.to_f/last_row.to_f)*100).truncate}%\r"
    $stdout.flush 
  end
    if sheet[row,0]
      insert_values<<"("
      0.upto(last_column) do |column|  
        data = sheet[row,column]
        insert_values << "'#{data}',"
      end
      insert_values.chop!
      insert_values << "),\n"
    end
  end
  print "\e[0KProcessing record #{last_row} of #{last_row}...   100%\r"
  $stdout.flush 
  puts
  insert_values.chomp!(",\n")
  command << insert_values

puts "Writing SQL script...\n"
File.open(sql,'w') do |f|
  f.print command
end

puts "--Output End--\n"