State machine to read spreadsheet contain classes, categories and subcategories and produce sql inserts to populate relational db.

CatgManufacturingMachine.rb

require 'CSV'

class CatgManufacturingMachine
  attr_accessor :main_class, :catg_class
  attr_reader :cur_catg

  # Base state - always reset on blank line
  class StateLook
    def blank_line(o)
      o.reset
    end

    def process_row(o)
      if (o.catg)
        found_catg(o)
      elsif (o.subcatg)
        found_subcatg(o)
      else
        blank_line(o)
      end
    end
  end

  # Looking for a class - treat entry in category position as a category class
  class StateClassLook < StateLook
    def found_subcatg(o)
      o.reset
    end

    def found_catg(o)
      o.main_class = o.catg
      o.insert_a_class(o.main_class)
      o.change_state(:catg)
    end
  end

  # Looking for more categories
  class StateCatgLook < StateLook
    def found_subcatg(o)
      throw "subcategory (#{o.subcatg}) without a category" unless o.cur_catg   #Can't have a subcat without a cat!
      o.insert_a_class(o.cur_catg.clone)
      o.insert_a_catg(o.subcatg)
      o.change_state(:subcatg)
    end

    def found_catg(o)
      o.insert_a_catg(o.catg)
    end
  end

  #Looking for more subcategories
  class StateSubCatgLook < StateLook
    def found_subcatg(o)
      o.insert_a_catg(o.subcatg)
    end

    def found_catg(o)
      o.catg_class = o.main_class
      o.insert_a_catg(o.catg)
      o.change_state(:catg)
    end
  end

  def change_state(newstate)
    @state = @states[newstate]
  end

  def reset
    @cur_catg = nil
    @main_class = nil
    change_state(:class)
  end

  def catg
    @csv[0] ? {:en => quotify(@csv[0]), :fr => quotify(@csv[5]), :code => @csv[1]} : nil
  end

  def subcatg
    @csv[2] ? {:en => quotify(@csv[2]), :fr => quotify(@csv[7]), :code => @csv[3]} : nil
  end

  def quotify(s)
    s.gsub!("'", "''") if s
  end

  def insert_a_class(catg_class)
    @catg_class = catg_class
    @catg_class_id += 1
    @catg_class[:id] = @catg_class_id
    puts "insert into display_text(display_text_id, name) values (DISPLAY_TEXT_SEQ.NEXTVAL, 'CLASS-#{catg_class[:code]}');"
    puts "insert into display_value(display_value_id, display_text_id, locale, value)\nvalues \
(DISPLAY_VALUE_SEQ.NEXTVAL, DISPLAY_TEXT_SEQ.currval, 'EN', '#{catg_class[:en]}');"
    puts "insert into display_value(display_value_id, display_text_id, locale, value)\nvalues \
(DISPLAY_VALUE_SEQ.NEXTVAL, DISPLAY_TEXT_SEQ.currval, 'FR', '#{catg_class[:fr]}');"
    puts "insert into category_class(category_class_id, code, name_text_id, desc_text_id, display_order_num, published_flag)\nvalues \
(#{@catg_class_id}, '#{catg_class[:code]}', DISPLAY_TEXT_SEQ.currval, DISPLAY_TEXT_SEQ.currval, #{@catg_class_id*100}, 'Y');"
  end

  def insert_a_catg(catg)
    @cur_catg = catg
    @catg_id += 1
    @cur_catg[:id] = @catg_id
    puts "insert into display_text(display_text_id, name) values (DISPLAY_TEXT_SEQ.NEXTVAL, 'CAT-#{catg[:code]}');"
    puts "insert into display_value(display_value_id, display_text_id, locale, value)\nvalues \
(DISPLAY_VALUE_SEQ.NEXTVAL, DISPLAY_TEXT_SEQ.currval, 'EN', '#{catg[:en]}');"
    puts "insert into display_value(display_value_id, display_text_id, locale, value)\nvalues \
(DISPLAY_VALUE_SEQ.NEXTVAL, DISPLAY_TEXT_SEQ.currval, 'FR', '#{catg[:fr]}');"
    puts "insert into category(category_id, category_class_id, code, name_text_id, desc_text_id, display_order_num, published_flag)\nvalues \
(#{@catg_id}, #{@catg_class[:id]}, '#{catg[:code]}', DISPLAY_TEXT_SEQ.currval, DISPLAY_TEXT_SEQ.currval, #{@catg_id*100}, 'Y');"
  end

  def run(fnm)
    @catg_id = 0
    @catg_class_id = 100

    @states = {:class => StateClassLook.new,
                :catg => StateCatgLook.new,
                :subcatg => StateSubCatgLook.new}

    reset
    
    CSV.open(fnm, 'r') do |@csv|
      @state.process_row(self)
    end
  end

end

CatgManufacturingMachine.new.run('../data/catg_listing.csv')