require 'rubygems' #TODO:
require 'gdata/spreadsheet'
#require 'log4r'

# overwrite some methods from the gdata-gem:
module GData
  class Spreadsheet < GData::Base
    #-- modified
    def evaluate_cell(cell, sheet_no=1)
      raise ArgumentError, "invalid cell: #{cell}" unless cell
      raise ArgumentError, "invalid sheet_no: #{sheet_no}" unless sheet_no >0 and sheet_no.class == Fixnum 
      path = "/feeds/cells/#{@spreadsheet_id}/#{sheet_no}/#{@headers ? "private" : "public"}/basic/#{cell}"
  
      doc = Hpricot(request(path))
      result = (doc/"content").inner_html
    end

    #-- new
    def sheetlist
      path = "/feeds/worksheets/#{@spreadsheet_id}/private/basic"
      doc = Hpricot(request(path))
      result = []
      (doc/"content").each { |elem|
        result << elem.inner_html
      }
      result
    end

    # #-- new (testing only)  
    # def raw_url(url)
    #   path = "/feeds/worksheets/#{@spreadsheet_id}/private/basic"
    #   path = url
    #   doc = Hpricot(request(path))
    # end

    #-- new
    def save_entry_roo(entry)
      path = "/feeds/cells/#{@spreadsheet_id}/1/#{@headers ? 'private' : 'public'}/full"
      post(path, entry)
    end
  
    #-- new
    def entry_roo(formula, row=1, col=1)
      <<XML
    <entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
      <gs:cell row='#{row}' col='#{col}' inputValue='#{formula}' />
    </entry>
XML
    end
  
    #-- new
    def add_to_cell_roo(row,col,value)
      save_entry_roo(entry_roo(value,row,col))
    end

    #-- new
    def get_one_sheet
      ath = "/feeds/worksheets/#{@spreadsheet_id}/private/basic"
      path = "/feeds/cells/#{@spreadsheet_id}/1/private/full"
      doc = Hpricot(request(path))
      #pp doc
      #p doc # TODO:prey:
      # result = (doc/"content[@type='text']").inner_html
      # p (doc/"content").inner_html
      #result = (doc/"content").inner_html.each { |item| item + '#'}
      #-- result = []
      #-- (doc/"content").each { |elem|
      #--   #p elem
      #--   result << elem.inner_html
      #-- }
      #-- result
    end

    #new
    def oben_unten_links_rechts(sheet_no)
      #sheet_no = sheets.index(sheet) + 1
      path = "/feeds/cells/#{@spreadsheet_id}/#{sheet_no}/private/full"
      doc = Hpricot(request(path))
      #(doc/"entry").each {|item|
      rows = []
      cols = []
      (doc/"gs:cell").each {|item|
        #    p item
        #p item['row']
        rows.push item['row'].to_i
        #p item['col']
        cols.push item['col'].to_i
      }
      # $log.debug("row min: #{rows.min}")
      # $log.debug("row max: #{rows.max}")
      # $log.debug("col min: #{cols.min}")
      # $log.debug("col max: #{cols.max}")
      return rows.min, rows.max, cols.min, cols.max
    end

    def fulldoc(sheet_no)
      path = "/feeds/cells/#{@spreadsheet_id}/#{sheet_no}/private/full"
      doc = Hpricot(request(path))
      return doc
      #(doc/"entry").each {|item|
      rows = []
      cols = []
      (doc/"gs:cell").each {|item|
        #    p item
        #p item['row']
        rows.push item['row'].to_i
        #p item['col']
        cols.push item['col'].to_i
      }
      # $log.debug("row min: #{rows.min}")
      # $log.debug("row max: #{rows.max}")
      # $log.debug("col min: #{cols.min}")
      # $log.debug("col max: #{cols.max}")
      return rows.min, rows.max, cols.min, cols.max
    end
  end # class
end # module

class Google < GenericSpreadsheet
  #include Log4r
 
  attr_accessor :header_line 

  # TIMEOUT_IN_SECONDS = 42
  # GOOGLE_MAX_ROWS = 99
  # GOOGLE_MAX_COLS = 10 # TODO:
  
  # Creates a new Google spreadsheet object.
  def initialize(spreadsheetkey,user=nil,password=nil)
    @filename = spreadsheetkey
    # $log = Logger.new("my_log")
    # $log.outputters = FileOutputter.new("f1", :filename => "roo.log")
 
     # $log.debug("created a new Google-object")
    @spreadsheetkey = spreadsheetkey
    @user = user
    @password = password
    unless user 
      user = ENV['GOOGLE_MAIL']
    end
    unless password
      password = ENV['GOOGLE_PASSWORD']
    end
    #p spreadsheetkey
    #p user
    #p password
    #spreadsheetkey = ENV['GOOGLE_KEY']
    @default_sheet = nil
    @cell = Hash.new
    @cell_type = Hash.new
    @formula = Hash.new
    @first_row = Hash.new
    @last_row = Hash.new
    @first_column = Hash.new
    @last_column = Hash.new
    @cells_read = Hash.new
    @header_line = 1
    #??? @sheets = Array.new
    
    @gs = GData::Spreadsheet.new(spreadsheetkey)
    #@column = FromGData::Spreadsheet.new(spreadsheetkey)
    @gs.authenticate(user, password)
   
    #--Test only
    #@gs.get_one_sheet 
    #@gs.oben_unten_links_rechts
    #--
    #-- ----------------------------------------------------------------------
    #-- Behandlung von Berechtigungen hier noch einbauen
    #-- ----------------------------------------------------------------------
    
    #result = gs.add_to_cell formula
    #puts gs.evaluate_cell('A1')
    #p gs.evaluate_cell('A1')
    #puts gs.evaluate_cell('A2')
    #puts gs.evaluate_cell('A3')

    #Timeout.timeout(TIMEOUT_IN_SECONDS) {
    #  @gs = GoogleSpreadSheet.new(spreadsheetkey)
    #  @gs.authenticate(user, password)
    #  @default_sheet = nil
    #}
    # alle eigenen Spreadsheets bei Google
    # @spreadsheets= GData::Spreadsheet.spreadsheets("emmanuel.pirsch@gmail.com", "secret") # funktioniert anscheinend noch nicht!!!!!!!!!
    # ein einzelnes Spreadsheet bei Google
    # @spreadsheet= spreadsheet[spreadsheetname]

    #gb = GData::Base.new
    #gb.authenticate("thopre@gmail.com","secret")

    #g = GData::Spreadsheet.new("ttt")
    #@default_sheet = nil
    if self.sheets.size  == 1
      @default_sheet = self.sheets.first
    end 
  end

  #def gssheetlist
   # @gs.sheetlist
  #end

  # returns an array of sheet names in the spreadsheet
  def sheets
      #if @sheets == []
      #  @sheets = @gs.sheetlist
      #end
      #return @sheets 
      #p gssheetlist
      return @gs.sheetlist
  end

  #def reload
  #  sav_default_sheet = default_sheet
  #  super
  #  @sheets = sheets #[]
  #  puts "DEBUG: reload: erneutes Einlesen ergibt @sheets=#{@sheets.to_s}"
  #  @default_sheet = sav_default_sheet
  #end

  # set the working sheet in the document
  #--
  # TODO: eigenlich identisch mit Openoffice => refactoring
  def default_sheet=(sheet)
    #puts "DEBUG: default_sheet=#{sheet}"
    if ! sheet.kind_of?(String)
      raise TypeError, "what are you trying to set as default sheet?"
    end
    @default_sheet = sheet
    check_default_sheet
    @first_row[sheet] = @last_row[sheet] = @first_column[sheet] = @last_column[sheet] = nil
    #--TODO: @cells_read[sheet] = false
  end

  # is String a date with format DD/MM/YYYY
  def Google.date?(string)
    return false if string.class == Float
    return true if string.class == Date
    return string.strip =~ /^([0-9]+)\/([0-9]+)\/([0-9]+)$/
  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 the first line and first row.
  def cell(row, col, sheet=nil)
    sheet = @default_sheet unless sheet
    check_default_sheet #TODO: 2007-12-16
    read_cells(sheet) unless @cells_read[sheet]
    row,col = normalize(row,col)

    if celltype(row,col,sheet) == :date
 # $log.debug("splitte Datumsfeld auf (#{row},#{col}): #{ @cell[sheet]["#{row},#{col}"]}")
      yyyy,mm,dd = @cell[sheet]["#{row},#{col}"].split('-')
      # $log.debug(yyyy)
      # $log.debug(mm)
      # $log.debug(dd)
#TODO:
if dd.to_i < 1 or dd.to_i >31 or mm.to_i < 1 or mm.to_i > 12 or yyyy.to_i < 1900 or yyyy.to_i > 3000
  raise "Invalid date parameter: #{yyyy}, #{mm}, #{dd}"
end
      return Date.new(yyyy.to_i,mm.to_i,dd.to_i)
    end
    return @cell[sheet]["#{row},#{col}"]
  end

  # returns the type of a cell:
  # * :float 
  # * :string, 
  # * :date
  # * :percentage
  def celltype(row, col, sheet=nil)
    sheet = @default_sheet unless sheet
    read_cells(sheet) unless @cells_read[sheet]
    row,col = normalize(row,col)
    if @formula[sheet]["#{row},#{col}"]
      return :formula
    else
      @cell_type[sheet]["#{row},#{col}"]
    end
  end
 
  # Returns the formula at (row,col).
  # Returns nil if there is no formula.
  # The method #formula? checks if there is a formula.
  def formula(row,col,sheet=nil)
    sheet = @default_sheet unless sheet
    read_cells(sheet) unless @cells_read[sheet]
    row,col = normalize(row,col)
    if @formula[sheet]["#{row},#{col}"] == nil
      return nil
    else
      return @formula[sheet]["#{row},#{col}"] #TODO: ["oooc:".length..-1]
    end
  end

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

  # returns each formula in the selected sheet as an array of elements
  # [row, col, formula]
  def formulas(sheet=nil)
    theformulas = Array.new
    sheet = @default_sheet unless sheet
    read_cells(sheet) unless @cells_read[sheet]
    first_row(sheet).upto(last_row(sheet)) {|row|
      first_column(sheet).upto(last_column(sheet)) {|col|
        if formula?(row,col,sheet)
          f = [row, col, formula(row,col,sheet)]
          theformulas << f
        end
      }
    } 
    theformulas
  end

#  # returns all values in this row as an array
#  # row numbers are 1,2,3,... like in the spreadsheet
#  def row_old(rownumber,sheet=nil)
#    sheet = @default_sheet unless sheet
#    result = []
#    1.upto(GOOGLE_MAX_ROWS) { |col| # TODO: maximum size of x-coordinate of google spreadsheet
#      result[col] = cell(rownumber,col,sheet)
#    }
#    result = result[1..-1]
#    while result[-1] == '' #TODO: besser?
#      result = result[0..-2]
#    end
#    result
#  end

  # returns all values in this row as an array
  # row numbers are 1,2,3,... like in the spreadsheet
  def row(rownumber,sheet=nil)
    sheet = @default_sheet unless sheet
    read_cells(sheet) unless @cells_read[sheet]
    result = []
    tmp_arr = []
    @cell[sheet].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 
  
  # true, if the cell is empty
  def empty?(row, col, sheet=nil)
    value = cell(row, col, sheet)
    return true unless value
    return false if value.class == Date # a date is never empty
    return false if value.class == Float
    value.empty?
  end
  
  # returns all values in this column as an array
  # column numbers are 1,2,3,... like in the spreadsheet
  #--
  #TODO: refactoring nach GenericSpreadsheet?
  def column(columnnumber, sheet=nil)
    if columnnumber.class == String
      columnnumber = GenericSpreadsheet.letter_to_number(columnnumber)
    end
    sheet = @default_sheet unless sheet
    read_cells(sheet) unless @cells_read[sheet]
    result = []
    first_row(sheet).upto(last_row(sheet)) do |row|
      result << cell(row,columnnumber,sheet)
    end
    result
  end 

  # sets the cell to the content of 'value'
  # a formula can be set in the form of '=SUM(...)'
  def set_value(row,col,value)
    @gs.add_to_cell_roo(row,col,value)
  end

  # returns the first non-empty row in a sheet
  def first_row(sheet=nil)
    sheet = @default_sheet unless sheet
    unless @first_row[sheet]
      sheet_no = sheets.index(sheet) + 1
      @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no)
    end
    return @first_row[sheet]
  end

  # returns the last non-empty row in a sheet
  def last_row(sheet=nil)
    sheet = @default_sheet unless sheet
    unless @last_row[sheet]
      sheet_no = sheets.index(sheet) + 1
      @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no)
    end
    return @last_row[sheet]
  end

    # returns the first non-empty column in a sheet
  def first_column(sheet=nil)
    sheet = @default_sheet unless sheet
    unless @first_column[sheet]
      sheet_no = sheets.index(sheet) + 1
      @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no)
    end
    return @first_column[sheet]
  end

    # returns the last non-empty column in a sheet
  def last_column(sheet=nil)
    sheet = @default_sheet unless sheet
    unless @last_column[sheet]
      sheet_no = sheets.index(sheet) + 1
      @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no)
    end
    return @last_column[sheet]
  end

  # write the current spreadsheet to stdout or into a file
  #--
  #TODO: refactoring --> GenericSpreadsheet
  def to_csv(filename=nil,sheet=nil)
    sheet = @default_sheet unless sheet
    if filename
      file = File.open(filename,"w") # do |file|
      write_csv_content(file,sheet)
      file.close
    else
      write_csv_content(STDOUT,sheet)
    end
    true
  end
  
   
  # find a row either by row number or a condition
  # Caution: this works only within the default sheet -> set default_sheet before you call this method
  # (experimental. see examples in the test_roo.rb file)
  #--
  # -----------------------------------------------------
  # !!!TODO: should be factored out to GenericSpreadsheet
  # also in Openofiffe
  # -----------------------------------------------------
  #++
  def find(*args)
    result_array = false
    args.each {|arg,val|
      if arg.class == Hash
        arg.each { |hkey,hval|
          if hkey == :array and hval == true
            result_array = true
          end
        }
      end
    }
    column_with = {}
    1.upto(last_column) do |col|
      column_with[cell(@header_line,col)] = col
    end
    result = Array.new
    #-- id
    if args[0].class == Fixnum
      rownum = args[0]
      tmp = {}
      1.upto(self.row(rownum).size) {|j|
        x = ''
        column_with.each { |key,val|
          if val == j
            x = key
          end
        }
        tmp[x] = cell(rownum,j) 
      } 
      result = [ tmp ] # row(rownum)
      #-- :all
    elsif args[0] == :all 
      if args[1].class == Hash
        args[1].each {|key,val|
          if key == :conditions
            column_with = {}
            1.upto(last_column) do |col|
              column_with[cell(@header_line,col)] = col
            end
            conditions = val
            first_row.upto(last_row) do |i|
              # are all conditions met?
              found = 1
              conditions.each { |key,val|
                if cell(i,column_with[key]) == val
                  found *= 1
                else
                  found *= 0
                end
              }
              # p self.row(i) if found > 0
              if found > 0
                tmp = {}
                1.upto(self.row(i).size) {|j|
                  x = ''
                  column_with.each { |key,val|
                    if val == j
                      x = key
                    end
                  }
                  tmp[x] = cell(i,j) 
                } 
                if result_array
                  result << self.row(i)
                else
                  result << tmp
                end
              end
            end
          end # :conditions
        }
      end
    end
    result
  end
  private

#  def read_cells_old(sheet=nil) #TODO:
#    sheet = @default_sheet unless sheet
#    sheet_found = true # TODO:
#    #TODO: raise ArgumentError falls sheet nicht existiert - siehe openoffice
#    #TODO: boundaries !!!
#    1.upto(25) { |y|
#      1.upto(20) { |x|
#        key = "#{y},#{x}"
#        @cell[sheet] = {} unless @cell[sheet]
#        value = cell(y,x,sheet)
#        @cell[sheet][key] = value unless value == "" or value == nil
#      }
#    }
#    if !sheet_found
#      raise RangeErrror, "invalid sheet name"
#    end
#    @cells_read[sheet] = true
#  end

# read all cells in a sheet
  def read_cells(sheet=nil)
    sheet = @default_sheet unless sheet
    # sheet_no = sheets.index(sheet) + 1
    # @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no)
    #@first_row[sheet].upto(@last_row[sheet]) { |y|
    #  @first_column[sheet].upto(@last_column[sheet]) { |x|
    #    key = "#{y},#{x}"
    #    @cell[sheet] = {} unless @cell[sheet]
    #    value = cell(y,x,sheet)
    #    @cell[sheet][key] = value unless value == "" or value == nil
    #  }
    #}
    sheet_no = sheets.index(sheet)+1
    doc = @gs.fulldoc(sheet_no)
    (doc/"gs:cell").each {|item|
      row = item['row']
      col = item['col']
      value = item['inputvalue']
      numericvalue = item['numericvalue']
      # puts numericvalue
      # puts value 
      # puts value[0,1] 
      if value[0,1] == '='
        formula = value
# $log.debug("formula: <#{formula}>")
        # puts "Formel gefunden"
      else
        formula = nil
      end
      # puts formula
      #--
      @cell_type[sheet] = {} unless @cell_type[sheet]
      if formula 
        ty = :formula
if numeric?(numericvalue)
        value = numericvalue.to_f 
else
        value = numericvalue
end
      elsif Google.date?(value)
        ty = :date
      elsif numeric?(value) # or o.class ??? 
        ty = :float
        value = value.to_f
      else
        ty = :string
      end
      key = "#{row},#{col}"
      @cell[sheet] = {} unless @cell[sheet]
      if ty == :date
        dd,mm,yyyy = value.split('/')
        @cell[sheet][key] = sprintf("%04d-%02d-%02d",yyyy.to_i,mm.to_i,dd.to_i) 
      else
        @cell[sheet][key] = value unless value == "" or value == nil
      end
      @cell_type[sheet][key] = ty # Openoffice.oo_type_2_roo_type(vt)
      @formula[sheet] = {} unless @formula[sheet]
# $log.debug("formula vor belegen @formula: <#{formula}>") if formula
      @formula[sheet][key] = formula  if formula
      # $log.debug("@formula[#{sheet}][#{key}] = #{formula}")  if formula
      # $log.debug("#{@formula[sheet][key]}")  if formula


    }
    @cells_read[sheet] = true
  end

  # Checks if the default_sheet exists. Otherwise a RangeError exception is 
  # raised
  def check_default_sheet
    sheet_found = false
    raise ArgumentError, "Error: default_sheet not set" if @default_sheet == nil
    if sheets.index(@default_sheet) 
      sheet_found = true
    end
    if ! sheet_found
      raise RangeError, "sheet '#{@default_sheet}' not found"
    end
  end

  def numeric?(string)
    string =~ /^[0-9]+[\.]*[0-9]*$/
  end

  # convert string DD/MM/YYYY into a Date-object
  #TODO: was ist mit verschiedenen Typen der Datumseingabe bei Google?
  def Google.to_date(string)
    if string.strip =~ /^([0-9]+)\/([0-9]+)\/([0-9]+)$/
      return Date.new($3.to_i,$2.to_i,$1.to_i)
    else
      return nil
    end
  end

  #TODO: refactoring to GenericSpreadsheet?
  def write_csv_content(file=nil,sheet=nil)
    file = STDOUT unless file
    if first_row # sheet is not empty
      first_row(sheet).upto(last_row(sheet)) do |row|
        1.upto(last_column(sheet)) do |col|
          file.print(",") if col > 1
          onecell = cell(row,col,sheet)
          onecelltype = celltype(row,col,sheet)
          file.print one_cell_output(onecelltype,onecell,empty?(row,col,sheet))
        end
        file.print("\n")
      end # sheet not empty
    end
  end

  #TODO: refactor to Generic....
  def one_cell_output(onecelltype,onecell,empty)
    str = ""
    if empty 
      str += ''
    else
      case onecelltype 
      when :string
        if onecell == ""
          str << ''
        else
          onecell.gsub!(/"/,'""')
          str << ('"'+onecell+'"')
        end
      when :float,:percentage
        if onecell == onecell.to_i
          str << onecell.to_i.to_s
        else
          str << onecell.to_s
        end
      when :formula
        if onecell.class == String
          if onecell == ""
            str << ''
          else
            onecell.gsub!(/"/,'""')
            str << '"'+onecell+'"'
          end
        elsif onecell.class == Float
          if onecell == onecell.to_i
            str << onecell.to_i.to_s
          else
            str << onecell.to_s
          end
        else
          raise "unhandled onecell-class "+onecell.class.to_s
        end
      when :date
        str << '"'+onecell.to_s+'"'
      else
        raise "unhandled celltype "+onecelltype.to_s
      end
    end
    #cells << onecell
    str
  end

end # class