require 'rubygems'
require 'rexml/document'
require 'fileutils'
require 'zip/zipfilesystem'
require 'date'
#require 'llip'
require 'base64'
require 'logger'

#require 'lib/roo/spreadsheetparser'

class Openoffice

  @@nr = 0

  # initialization and opening of a spreadsheet file
  # values for packed: :zip
  def initialize(filename, packed=nil) #, create = false)
    @tmpdir = "oo_"+$$.to_s
    unless File.exists?(@tmpdir)
      FileUtils::mkdir(@tmpdir)
    end
    filename = open_from_uri(filename) if filename[0,7] == "http://"
    filename = unzip(filename) if packed and packed == :zip
    if filename[-4..-1] != ".ods"
      warn "are you sure, this is an openoffice file?"
    end
    #if create and ! File.exists?(filename)
    #  self.create_openoffice(filename)
    #end
    @cells_read = false
    @filename = filename
    @@nr += 1
    @file_nr = @@nr
    extract_content
    file = File.new(File.join(@tmpdir, @file_nr.to_s+"_roo_content.xml")) 
    @doc = REXML::Document.new file
    file.close
    @cell = Hash.new
    @cell_type = Hash.new
    @formula = Hash.new
    #if ENV["roo_local"] != "thomas-p"
      FileUtils::rm_r(@tmpdir)
    #end
    @default_sheet = nil 
    # no need to set default_sheet if there is only one sheet in the document
    if self.sheets.size == 1
      @default_sheet = self.sheets.first
    end
    @first_column = @last_column = nil
    @first_row = @last_row = nil
#    trap('INT') {
#      FileUtils::rm_r(@tmpdir)
#    }
  end

  # creates a new empty openoffice-spreadsheet file
  def create_openoffice(filename)
    #TODO: a better way for creating the file contents
    # now you have to call mkbase64...rb to create an include file with all
    # the empty files in an openoffice zip-file
    load 'base64include.rb'
    # puts @@empty_spreadsheet
    f = File.open(filename,'wb')
    f.print(Base64.decode64(@@empty_spreadsheet))
    f.close
  end

  # reopens and read a spreadsheet document
  def reload
    default_sheet = @default_sheet
    initialize(@filename) 
    self.default_sheet = default_sheet
    @first_row = @last_row = @first_column = @last_column = nil
  end

  # returns the content of a spreadsheet-cell
  # (1,1) is the upper left corner
  # (1,1), (1,'A'), ('A',1), ('a',1) all refers to the
  # cell at first line, first row
  def cell(row,col)
    read_cells unless @cells_read
    row,col = normalize(row,col)
    if celltype(row,col) == :date
      yyyy,mm,dd = @cell["#{row},#{col}"].split('-')
      return Date.new(yyyy.to_i,mm.to_i,dd.to_i)
    end
    @cell["#{row},#{col}"]
  end

  # returns the formula at (row,col)
  # nil if there is no formula
  def formula(row,col)
    read_cells unless @cells_read
    row,col = normalize(row,col)
    if @formula["#{row},#{col}"] == nil
      return nil
    else
      return @formula["#{row},#{col}"]["oooc:".length..-1]
    end
  end

  # true, if there is a formula
  def formula?(row,col)
    read_cells unless @cells_read
    row,col = normalize(row,col)
    formula(row,col) != nil
  end

  # set a cell to a certain value
  # (this will not be saved back to the spreadsheet file!)
  def set(row,col,value)
    read_cells unless @cells_read
    row,col = normalize(row,col)
    set_value(row,col,value)
    if value.class == Fixnum
      set_type(row,col,:float)
    elsif value.class == String
      set_type(row,col,:string)
    elsif value.class == Float
      set_type(row,col,:string)
    else
      raise ArgumentError, "Typ fuer "+value.to_s+" nicht gesetzt"
    end 
  end

  # returns the open-office type of a cell
  def celltype(row,col)
    read_cells unless @cells_read
    row,col = normalize(row,col)
    if @formula["#{row},#{col}"]
      return :formula
    else
      @cell_type["#{row},#{col}"]
    end
  end

  # recursively removes the current temporary directory
  # this is only needed if you work with zipped files or files via the web
  def remove_tmp
    if File.exists?(@tmpdir)
      FileUtils::rm_r(@tmpdir)
    end
  end

  # returns an array of sheet names in the spreadsheet
  def sheets
    return_sheets = []
    oo_document_count = 0
    @doc.each_element do |oo_document|
      oo_document_count += 1
      oo_element_count = 0
      oo_document.each_element do |oo_element|
        oo_element_count += 1
        if oo_element.name == "body"
          oo_element.each_element do |be|
            if be.name == "spreadsheet"
              be.each_element do |se|
                if se.name == "table"
                  return_sheets << se.attributes['name']
                end
              end
            end
          end
        end
      end
    end
    return_sheets
  end

  # set the working sheet in the document
  def default_sheet=(sheet)
    if ! sheet.kind_of?(String)
      raise TypeError, "what are you trying to set as default sheet?"
    end
    @default_sheet = sheet
    @first_row = @last_row = @first_column = @last_column = nil
    @cells_read = false
    @cell = Hash.new
    @cell_type = Hash.new
    @formula = Hash.new
    check_default_sheet
  end

  alias set_default_sheet default_sheet=

  # version of the openoffice document 
  # at 2007 this is always "1.0"
  def officeversion
    # read_cells(false) unless @cells_read
    oo_version
    @officeversion
  end

  # shows the internal representation of all cells
  # mainly for debugging purposes
  def to_s
    read_cells unless @cells_read
    @cell.inspect
  end

  # returns all values in this row as an array
  # row numbers are 1,2,3,... like in the spreadsheet
  def row(rownumber)
    read_cells unless @cells_read
    result = []
    tmp_arr = []
    @cell.each_pair {|key,value|
      y,x = key.split(',')
      x = x.to_i
      y = y.to_i
      if y == rownumber
        tmp_arr[x] = value
      end
    }
    result = tmp_arr[1..-1]
    while result[-1] == nil
      result = result[0..-2]
    end
    result
  end
  
  # returns the number of the last non-empty row
  def last_row
    read_cells unless @cells_read
    if @last_row
      return @last_row
    end
    impossible_value = 0
    result = impossible_value
    @cell.each_pair {|key,value|
      y,x = key.split(',')
      y = y.to_i
      result = [result, y].max if value
    }
    result = nil if result == impossible_value 
    @last_row = result
    result
  end

  # returns the number of the last non-empty column
  def last_column
    read_cells unless @cells_read
    if @last_column
      return @last_column
    end
    impossible_value = 0
    result = impossible_value
    @cell.each_pair {|key,value|
      y,x = key.split(',')
      x = x.to_i
      result = [result, x].max if value
    }
    result = nil if result == impossible_value
    @last_column = result
    result
  end

  # returns the number of the first non-empty row
  def first_row
    read_cells unless @cells_read
    if @first_row
      return @first_row
    end
    impossible_value = 999_999 # more than a spreadsheet can hold
    result = impossible_value 
    @cell.each_pair {|key,value|
      y,x = key.split(',')
      y = y.to_i
      result = [result, y].min if value
    }
    result = nil if result == impossible_value
    @first_row = result
    result
  end

  # returns the number of the first non-empty column
  def first_column
    read_cells unless @cells_read
    if @first_column
      return @first_column
    end
    impossible_value = 999_999 # more than a spreadsheet can hold
    result = impossible_value 
    @cell.each_pair {|key,value|
      y,x = key.split(',')
      x = x.to_i
      result = [result, x].min if value
    }
    result = nil if result == impossible_value
    @first_column = result
    result
  end

  # first non-empty column as a letter
  def first_column_as_letter
    Openoffice.number_to_letter(first_column)
  end

  # last non-empty column as a letter
  def last_column_as_letter
    Openoffice.number_to_letter(last_column)
  end

  # true if cell is empty
  def empty?(row, col)
    read_cells unless @cells_read
    return true unless cell(row, col)
    return true if celltype(row, col) == :string && cell(row, col).empty?
    false
  end

  # save spreadsheet
  def save
    42
  end

  # evaluate the formula at this cell 
  # experimental: DO NOT USE THIS!
  def solve(row,col)
    parser = SpreadsheetParser.new
    visitor = Visitor.new
    #puts cell(row,col)
    #puts formula(row,col)
    formula = formula(row,col)[1..-1] # .downcase
    # puts formula
    #eval formula
    #parser.parse(formula)
    parser.parse(formula).accept(visitor)
  end

  # returns each formula in the selected sheet as an array of elements
  # [row, col, formula]
  def formulas
    theformulas = Array.new
    read_cells unless @cells_read
    first_row.upto(last_row) {|row|
      first_column.upto(last_column) {|col|
        if formula?(row,col)
          f = [row, col, formula(row,col)]
          theformulas << f
        end
      }
    } 
    theformulas
  end
  
  # returns a rectangular area (default: all cells) as yaml-output 
  # you can add additional attributes with the prefix parameter like:
  # oo.to_yaml({"file"=>"flightdata_2007-06-26", "sheet" => "1"})
  def to_yaml(prefix={}, from_row=nil, from_column=nil, to_row=nil, to_column=nil)
    result = "--- \n"
    (from_row||first_row).upto(to_row||last_row) do |row|
      (from_column||first_column).upto(to_column||last_column) do |col|
        unless self.empty?(row,col)
          result << "cell_#{row}_#{col}: \n" 
          prefix.each {|k,v|
            result << "  #{k}: #{v} \n"
          }
          result << "  row: #{row} \n" 
          result << "  col: #{col} \n" 
          result << "  celltype: #{self.celltype(row,col)} \n" 
          result << "  value: #{self.cell(row,col)} \n" 
        end
      end
    end
    result
  end

  # write the current spreadsheet to stdout or into a file
  def to_csv(filename=nil)
    if filename
      file = File.open(filename,"w") # do |file|
      write_csv_content(file)
      file.close
    else
      write_csv_content
    end
    true
  end

protected

  def process_zipfile_packed(zip, path='')
    ret=nil
    if zip.file.file? path
      # extract and return filename
      @tmpdir = "oo_"+$$.to_s
      unless File.exists?(@tmpdir)
        FileUtils::mkdir(@tmpdir)
      end
      file = File.open(File.join(@tmpdir, path),"wb")
      file.write(zip.read(path))
      file.close
      return File.join(@tmpdir, path)
    else
      unless path.empty?
        path += '/'
      end
      zip.dir.foreach(path) do |filename|
        ret = process_zipfile_packed(zip, path + filename)
      end
    end
    ret
  end

private
  def unzip(filename) 
    ret = nil
    Zip::ZipFile.open(filename) do |zip|
      ret = process_zipfile_packed zip
    end
    ret
  end

  # read the version of the OO-Version
  def oo_version
    sheet_found = false
    @doc.each_element do |oo_document|
      @officeversion = oo_document.attributes['version']
    end
  end

  def belegen(x,y,i,v,vt,formula,tr,str_v)
    @cell_type["#{y},#{x+i}"] = Openoffice.oo_type_2_roo_type(vt)
    @formula["#{y},#{x+i}"] = formula if formula
    if @cell_type["#{y},#{x+i}"] == :float
      @cell["#{y},#{x+i}"] = v.to_f
    elsif @cell_type["#{y},#{x+i}"] == :string
      @cell["#{y},#{x+i}"] = str_v
    elsif @cell_type["#{y},#{x}"] == :date
      @cell["#{y},#{x}"] = tr.attributes['date-value']
    elsif @cell_type["#{y},#{x+i}"] == :percentage
      @cell["#{y},#{x+i}"] = v
    else
      @cell["#{y},#{x}"] = v
    end                       
  end

  # read all cells in the selected sheet
  #--
  # the following construct means '4 blanks'
  # some content <text:s text:c="3"/>
  #++
  def read_cells
    sheet_found = false
    raise ArgumentError, "Error: default_sheet not set" if @default_sheet == nil
    oo_document_count = 0
    @doc.each_element do |oo_document|
      # @officeversion = oo_document.attributes['version']
      oo_document_count += 1
      oo_element_count = 0
      oo_document.each_element do |oo_element|
        oo_element_count += 1
        if oo_element.name == "body"
          oo_element.each_element do |be|
            if be.name == "spreadsheet"
              be.each_element do |se|
                if se.name == "table"
                  if se.attributes['name']==@default_sheet
                    sheet_found = true
                    x=1
                    y=1
                    se.each_element do |te|
                      if te.name == "table-column"
                        rep = te.attributes["number-columns-repeated"]
                      elsif te.name == "table-row"
                        if te.attributes['number-rows-repeated']
                          skip_y = te.attributes['number-rows-repeated'].to_i 
                          y = y + skip_y - 1 # minus 1 because this line will be counted as a line element
                        end
                        te.each_element do |tr|
                          if tr.name == 'table-cell'
                            skip = tr.attributes['number-columns-repeated']
                            formula = tr.attributes['formula']
                            vt = tr.attributes['value-type']
                            v  = tr.attributes['value']
                            if vt == 'string'
                              str_v  = ''
                              # insert \n if there is more than one paragraph
                              para_count = 0
                              tr.each_element do |str|
                                if str.name == 'p'
                                  v = str.text
                                  str_v += "\n" if para_count > 0
                                  para_count += 1
                                  if str.children.size > 1
                                    str_v = children_to_string(str.children)
                                  else
                                    str.children.each {|child|
                                      str_v = str_v + child.to_s #.text
                                    }
                                  end
                                  str_v.gsub!(/&quot;/,'"')
                                  str_v.gsub!(/&amp;/,'&')
                                  str_v.gsub!(/&apos;/,"'")
                                end # == 'p'
                              end
                            end
                            if skip
                              if v != nil
                                0.upto(skip.to_i-1) do |i|
                                  belegen(x,y,i,v,vt,formula,tr,str_v)
                                end
                              end
                              x += (skip.to_i - 1)
                            end # if skip
                            belegen(x,y,0,v,vt,formula,tr,str_v)
                            x += 1
                          end
                        end
                        y += 1
                        x = 1
                      end
                    end
                  end # sheet 
                end
              end
            end
          end
        end
      end
    end
    if sheet_found == false
      raise RangeError 
    end
    @cells_read = true
  end

  # Checks if the default_sheet exists. If not an RangeError exception is 
  # raised
  def check_default_sheet
    sheet_found = false
    raise ArgumentError, "Error: default_sheet not set" if @default_sheet == nil
    @doc.each_element do |oo_document|
      oo_element_count = 0
      oo_document.each_element do |oo_element|
        oo_element_count += 1
        if oo_element.name == "body"
          oo_element.each_element do |be|
            if be.name == "spreadsheet"
              be.each_element do |se|
                if se.name == "table"
                  if se.attributes['name'] == @default_sheet
                    sheet_found = true
                  end # sheet 
                end
              end
            end
          end
        end
      end
    end
    if ! sheet_found
      raise RangeError, "sheet '#{@default_sheet}' not found"
    end
  end

  def process_zipfile(zip, path='')
    if zip.file.file? path
      if path == "content.xml"
        open(@tmpdir+'/'+@file_nr.to_s+'_roo_content.xml','wb') {|f|
          f << zip.read(path) 
        }
      end 
    else
      unless path.empty?
        path += '/' 
      end 
      zip.dir.foreach(path) do |filename|
        process_zipfile(zip, path+filename)
      end 
    end 
  end


  def extract_content
    Zip::ZipFile.open(@filename) do |zip|
      process_zipfile(zip)
    end
  end

  # converts cell coordinate to numeric values of row,col
  def normalize(row,col)
    if row.class == String
      if col.class == Fixnum
        # ('A',1):
        # ('B', 5) -> (5, 2)
        row, col = col, row
      else
        raise ArgumentError
      end
    end
    if col.class == String
      col = Openoffice.letter_to_number(col)
    end 
    return row,col
  end 

  # convert a number to something like this: 'AB'
  def Openoffice.number_to_letter(n)
    letters=""
    while n > 0
      num = n%26
      letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"[num-1,1] + letters
      n = n.div(26)
     end
     letters
  end

  # convert letters like 'AB' to a number
  def Openoffice.letter_to_number(letters)
    result = 0
    while letters && letters.length > 0
      character = letters[0,1].upcase
      num = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".index(character)+1
      result = result * 26 + num
      letters = letters[1..-1]
    end
    result
  end

  
  def set_value(row,col,value)
    @cell["#{row},#{col}"] = value
  end

  def set_type(row,col,type)
    @cell_type["#{row},#{col}"] = type
  end

  A_ROO_TYPE = {
    "float"      => :float,
    "string"     => :string,
    "date"       => :date,
    "percentage" => :percentage,
  }

  def Openoffice.oo_type_2_roo_type(ootype)
    return A_ROO_TYPE[ootype]
  end

  
  def open_from_uri(uri)
    require 'open-uri' ;
    tempfilename = File.join(@tmpdir, File.basename(uri))
    f = File.open(tempfilename,"wb")
    begin
    open(uri) do |net|
      f.write(net.read)
    end
    rescue
      raise "could not open #{uri}"
    end
    f.close
    File.join(@tmpdir, File.basename(uri))
  end

  def write_csv_content(file=nil)
    file = STDOUT unless file
      first_row.upto(last_row) do |row|
        cells = []
        1.upto(last_column) do |col|
          file.print(",") if col > 1
          onecell = cell(row,col)
          onecelltype = celltype(row,col)
          if empty?(row,col)
            file.print('')
          else
            case onecelltype 
            when :string
              if onecell == ""
                file.print('')
              else
                onecell.gsub!(/"/,'""')
                file.print('"'+onecell+'"')
              end
            when :float
              if onecell == onecell.to_i
                file.print onecell.to_i
              else
                file.print onecell
              end
            else
              raise "unhandled celltype "+celltype.to_s
            end
          end
          cells << onecell
        end
        file.print("\n")
      end
  end

  # helper method to convert compressed spaces and other elements within 
  # an text into a string
  def children_to_string(children)
    result = ''
    children.each {|child|
      if child.class == REXML::Text
        result = result + child.to_s
      else
        if child.name == 's'
          compressed_spaces = child.attributes['c'].to_i
          # no explicit number means a count of 1:
          if compressed_spaces == 0
            compressed_spaces = 1
          end
          result = result + " "*compressed_spaces
        else
          result = result + child.to_s
        end
      end
    }
    result
  end

end # class