#!/usr/bin/ruby
# First attempt 28/3/2013
require 'roo'

data = Excel.new(ARGV[0])
data.default_sheet = data.sheets[0]

def header
  %q~Creator "JKenyon"  
Version 2.2  
graph [
  hierarchic  1
  directed  1
~
end

nodes = []
groups = []

# parse through the sheet to extract all nodes and groups
(data.first_row).upto(data.last_row) do |line|
  action = data.cell(line,'A')
  case action
  when /^node$/i
    nodes << data.cell(line,'B')
  when /^group$/i
    groups << data.cell(line,'B')
  when /^memberof$/i
    nodes << data.cell(line,'B')
    groups << data.cell(line,'C')
  when /^edge$/i
    nodes << data.cell(line,'B')
    nodes << data.cell(line,'C')
  end
end

# remove duplicate references to nodes and groups
nodes.uniq!
groups.uniq!
nodes = nodes - groups # if a node is a group make sure we don't display it as a node as well

all_nodes = [] #container for all nodes and group nodes
# Spec: id, gid, name, label, isgroup
# add in the group nodes
groups.each_with_index { |group,gid|
  all_nodes << [ gid, "", group, "", true ]
}

# add in the normal nodes
# find max gid and use as base number for node ids + 1
base_id = all_nodes.map {|row| row[0]}.to_a.max + 1
nodes.each_with_index { |node,id|
  all_nodes << [ (id + base_id), "", node, "", false ]
}

# insert the comments for nodes and groups (last one in the list takes priority)
# insert nodes as group members
(data.first_row).upto(data.last_row) do |line|
  action = data.cell(line,'A')
  case action
  when /^(node|group)$/i
    if (data.cell(line,'C').to_s != "") then
      # find the node or group in all_nodes and insert the comment
      all_nodes.each do |row|
        if (row[2] == data.cell(line,'B')) then
          row[3] = (data.cell(line,'C')).to_s
        end
      end

    end
  when /^memberof$/i
    # find the node or group in all_nodes and insert the gid
    all_nodes.each do |row|
      if (row[2] == data.cell(line,'B')) then
        row[1] = all_nodes.select { |node| node[2]==data.cell(line,'C') }[0][0]
      end
    end
  end
end

output = []
output << header
# Spec: id, gid, name, label, isgroup (is there some where of using an enum for the spec)
all_nodes.each do |row|
  output << "  node ["
  output << "    isGroup 1" if row[4]
  output << "    id " + row[0].to_s
  output << "    gid " + row[1].to_s if !row[1].to_s.empty?
  output << '    LabelGraphics [ text "' + row[2] + '" ]'
  output << "  ]"
end

# finally add the edges
(data.first_row).upto(data.last_row) do |line|
  action = data.cell(line,'A')
  case action
  when /^edge$/i
    output << '  edge ['
    output << '    source ' + all_nodes.select { |node| node[2]==data.cell(line,'B').to_s }[0][0].to_s
    output << '    target ' + all_nodes.select { |node| node[2]==data.cell(line,'C').to_s }[0][0].to_s
    if !(data.cell(line,'D').to_s.empty?) then
      output << '    label "' + data.cell(line,'D').to_s + '"'
    end
    output << '  ]'
  end
end

output << "]"
puts output