database_importer.rb

class DatabaseImporter

  def initialize(sql_file)
    @sql_file = sql_file
  end

  def all_values
    tables_names.inject({}) { |memo,name| memo[name] = values_for(name); memo }
  end

  def tables_names
    @content ||= File.read(@sql_file)
    @content.scan(/CREATE TABLE `(\w+)`/).flatten.uniq
  end

  def fields_for(table)
    @content ||= File.read(@sql_file)
    if @content =~ /CREATE TABLE `#{table}` \(([^;]+)\).+;/m
      fields_text = $1
      fields_text.lines.map do |line|
        line =~ /^\s+`(\w+)`.+$/ ? $1 : nil
      end.compact
    else
      []
    end
  end

  def values_for(table)
    @content ||= File.read(@sql_file)
    part_of_content = @content
    fields = fields_for(table)
    all_values = {}
    keep_going = true
    while match = part_of_content.match(/INSERT INTO `#{table}` VALUES(.*?);\n\/\*!/m)
      values_text = $1
      values = values_text.lines.reject { |line| line =~ /^INSERT INTO `#{table}` VALUES.*$/ }
      .map { |line| eval(line.strip.gsub('NULL', 'nil').gsub('(', '[').gsub(/\),?/, ']')) }
      .compact.inject({}) do |memo, array|
        hash = {}
        array.each_with_index do |item, index|
          hash[fields[index].to_sym] = item
        end
        memo[hash[:id]] = hash
        memo
      end
      all_values.merge! values
      part_of_content = match.post_match
    end
    all_values
  end

  def import_venues
    venues = values_for('venues')
    venues.values.each do |vdata|
      Venue.create(
        :name => vdata[:title],
        :address => vdata[:address],
        :state => vdata[:state],
        :lat => vdata[:latitude].to_f.round(5),
        :lon => vdata[:longitude].to_f.round(5),
        :description => vdata[:description],
        :slug => vdata[:slug],
        :url => vdata[:url],
        :phone => vdata[:phone],
        :city_id => 1,
        :zip_id => 1
      )
    end
  end

  def import_venue_images
    venues = values_for('venues')
    venue_images = values_for('venue_images')
    venue_images.each_pair do |iid, idata|
      vdata = venues[idata[:venue_id]]
      if vdata
        venue = Venue.find_by_slug(vdata[:slug])
        if venue
          venue.images << Image.new(
            :file_name => idata[:content_item_image_uri],
            :url => idata[:content_item_image_uri],
            :thumbnail_url => idata[:content_item_thumbnail_uri],
            :caption => idata[:caption],
            :credit => idata[:creadit],
            :taken_at => idata[:date_taken]
          )
          venue.save
        end
      end
    end
  end

  def import_venue_types
    venues = values_for('venues')
    venue_channels = values_for('venue_channels')
    venue_channels.each_pair do |id, cdata|
      vdata = venues[cdata[:venue_id]]
      if vdata
        venue = Venue.find_by_slug(vdata[:slug])
        if venue
          venue.type = Type.find_or_create_by_name_and_slug(:name => cdata[:name], :slug => cdata[:code])
          venue.save
        end
      end
    end
  end

  def import_venue_subtypes
    venues = values_for('venues')
    subtypes = values_for('venue_content_subtypes')
    subtypes.each_pair do |id, stdata|
      vdata = venues[stdata[:venue_id]]
      if vdata
        venue = Venue.find_by_slug(vdata[:slug])
        if venue
          venue.subtypes << Subtype.find_or_create_by_name_and_slug(:name => stdata[:name], :slug => stdata[:slug])
          venue.save
        end
      end
    end
  end

  def import_venue_attributes
    venues = values_for('venues')
    attrs = values_for('venue_attributes')
    attrs.each_pair do |id, att|
      vdata = venues[att[:venue_id]]
      if vdata
        venue = Venue.find_by_slug(vdata[:slug])
        if venue and venue.venue_attributes.where(:name => att[:name], :value => att[:value]).empty?
          VenueAttribute.create :name => att[:key_name], :value => att[:value], :venue => venue
        end
      end
    end
  end

  def import_venue_neighborhoods
    venues = values_for('venues')
    neighborhoods = values_for('neighborhoods')
    venues.each_pair do |vid, vdata|
      ndata = neighborhoods[vdata[:neighborhood_id]]
      if ndata
        venue = Venue.find_by_slug(vdata[:slug])
        if venue
          neighborhood = Neighborhood.find_by_name(ndata[:name])
          neighborhood ||= Neighborhood.create :name => ndata[:name], :taxonomy => ndata[:taxnomy_path]
          venue.neighborhood = neighborhood
          venue.save
        end
      end
    end
  end

  def import_events
    events = values_for('events')
    events.each_pair do |id, edata|
      Event.create :title => edata[:title],
        :slug => edata[:slug],
        :description => edata[:description],
        :url => edata[:url],
        :phone => edata[:phone],
        :price => edata[:price],
        :state => edata[:state],
        :address => edata[:address],
        :lat => edata[:latitude].to_f.round(5),
        :lon => edata[:longitude].to_f.round(5),
        :on_sale_at => edata[:on_sale_date].nil? ? nil : Date.parse(edata[:on_sale_date]),
        :alternate_name => edata[:alternate_name],
        :building_location => edata[:building_location]
    end
  end

  def import_event_images
    events = values_for('events')
    images = values_for('event_images')
    images.each_pair do |iid,idata|
      edata = events[idata[:event_id]]
      if edata
        event = Event.find_by_title(edata[:title])
        if event
          event.images << Image.create(
            :url => idata[:content_item_image_uri],
            :thumbnail_url => idata[:content_item_thumbnail_uri],
            :credit => idata[:credit],
            :caption => idata[:caption],
            :taken_at => idata[:date_taken],
            :file_name => idata[:content_item_image_uri]
          )
          event.save
        end
      end
    end
  end

  def import_event_types
    events = values_for('events')
    channels = values_for('event_channels')
    channels.each_pair do |cid, cdata|
      edata = events[cdata[:event_id]]
      if edata
        event = Event.find_by_title(edata[:title])
        if event
          event.type = Type.find_by_name(cdata[:name])
          event.save
        end
      end
    end
  end

  def import_event_subtypes
    events = values_for('events')
    subtypes = values_for('event_content_subtypes')
    subtypes.each_pair do |id, sdata|
      edata = events[sdata[:event_id]]
      if edata
        event = Event.find_by_title(edata[:title])
        if event
          event.subtypes << Subtype.find_or_create_by_name_and_slug(:name => sdata[:name], :slug => sdata[:slug])
          event.save
        end
      end
    end
  end

  def import_event_phone_numbers
    events = values_for('events')
    phones = values_for('event_other_phone_numbers')
    phones.each_pair do |id, pdata|
      edata = events[pdata[:event_id]]
      if edata
        event = Event.find_by_title(edata[:title])
        if event
          event.extra_phones << PhoneNumber.new(:type => pdata[:type], :number => pdata[:number])
          event.save
        end
      end
    end
  end

  def import_event_non_pattern_schedules
    Time.zone = "America/Chicago"
    events = values_for('events')
    scheds = values_for('event_non_pattern_schedules')
    problems = []
    scheds.each_pair do |sid, sdata|
      edata = events[sdata[:event_id]]
      duration = ""
      starts_at = nil
      if edata
        event = Event.find_by_title( edata[:title] )
        if event
          date = Date.parse(sdata[:occurs_on])
          start_time = Time.parse(sdata[:starts_at]) rescue nil
          if start_time
            starts_at = Time.new(date.year, date.month, date.day, start_time.hour, start_time.min)
            end_time = Time.parse(sdata[:ends_at]) if sdata[:ends_at]
            if end_time
              duration = ((end_time - start_time) / 3600).to_i
              duration = "#{duration}s"
            end
          else
            problems << sdata
          end

          schedule = Schedule.create :starts_at => starts_at, :duration => duration, :event => event
        end
      end
    end
    File.open("invalid_event_non_pattern_schedules.log", "w+") {|f| f.write(problems) }
  end

  def import_event_schedule_patterns
    Time.zone = "America/Chicago"
    problems = []
    events = values_for('events')
    scheds = values_for('event_schedule_patterns')
    scheds.values.each do |sdata|
      edata = events[sdata[:event_id]]
      if edata
        event = Event.find_by_title(edata[:title])
        if event
          begin
            occurs = Time.parse(sdata[:begin_on])
            starts = Time.parse(sdata[:starts_at])
            starts -= starts.beginning_of_day
            occurs += starts
            period = sdata[:pattern][0..-3].downcase.to_sym
            options = {:starts => occurs, :every => period, :on => sdata[:printable_day].downcase.to_sym, :comment => sdata[:comment]}
            event.schedule(options) unless period == :"by-week"
          rescue
            problems << sdata
          end
        end
      end
    end
    File.open("invalid_event_schedule_patterns.log", "w+") {|f| f.write(problems) }
  end

  def import_event_attributes
    events = values_for('events')
    attrs = values_for('event_attributes')
    attrs.each_pair do |id, att|
      edata = events[att[:event_id]]
      if edata
        event = Event.find_by_slug(edata[:slug])
        if event and event.event_attributes.where(:name => att[:name], :value => att[:value]).empty?
          EventAttribute.create :name => att[:key_name], :value => att[:value], :event => event
        end
      end
    end
  end

  def associate_events_and_venues
    events = values_for('events')
    venues = values_for('venues')
    events.each_pair do |id, edata|
      vdata = venues[edata[:venue_id]]
      event = Event.find_by_title(edata[:title])
      if vdata
        venue = Venue.find_by_slug(vdata[:slug])
        if venue
          event.venue = venue
          event.save
        end
      end
    end
  end

end