#!/usr/bin/python
#
# Programatically edit grades on an Open Office spreadsheet.
#
# Copyright (C) 2009 W. Trevor King
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 3 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
# See the GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
# 02111-1307, USA.
#
# The author may be contacted at <wking@drexel.edu> on the Internet, or
# write to Trevor King, Drexel University, Physics Dept., 3141 Chestnut St.,
# Philadelphia PA 19104, USA.

__author__ = 'wking@drexel.edu (W. Trevor King)'

import uno

import os
import re
import sys
import string
import time
import types

NoConnectException = uno.getClass("com.sun.star.connection.NoConnectException")

class Cell (object):
  def __init__(self, uno_cell, col, row):
    self.uno_cell = uno_cell
    self.col = col
    self.row = row

  def __str__(self):
    if self.uno_cell == None:
      return "<>"
    #return "<%d:%d %s>" % (self.col, self.row, self.getContents())
    absname = self.uno_cell.AbsoluteName # e.g. u'$Sheet1.$M$11'
    return "<%s %s>" % (absname, self.getContents())

  def __repr__(self):
    return str(self)

  def type(self):
    if self.uno_cell == None:
      return types.NoneType
    return self.uno_cell.getType().value

  def isLocked(self):
    return self.uno_cell.CellProtection.IsLocked

  def getContents(self):
    if self.uno_cell == None:
      return ''
    if self.type() == 'TEXT':
      return self.uno_cell.getString()
    if self.type() == 'VALUE':
      return self.uno_cell.getString() #self.uno_cell.getValue()
    if self.type() == 'EMPTY':
      return ''
    if self.type() == 'FORMULA':
      return self.uno_cell.getString()
    else:
      raise Exception, self.type()    
    return self.uno_cell.getValue()

  def setContents(self, value):
    if self.isLocked() == True:
      print >> sys.stderr, '! cell %s locked.  Cannot update %s -> %s' % (self, self.getContents(), value)
      return
    if value == None:
      self.setString("")
    if type(value) == types.StringType:
      self.uno_cell.setString(value)
    elif type(value) == types.IntType or type(value) == types.FloatType:
      self.uno_cell.setValue(value)
    else:
      raise Exception, type(value)

class OOGrade:
  def __init__(self):
    self.document = None

  def __del__(self):
    self.close()

  def save(self):
    if self.document != None and self.document.isModified() == True:
      self.document.store()

  def close(self):
    if self.document != None:
      self.document.close(True) # wrong number of args?

  def _GetSpreadsheet(self, name):
    self.filename = name
    os.system('oocalc -invisible -nologo -headless -accept="socket,host=localhost,port=2002;urp;" %s' \
                % self.filename) # pop up an ooffice window
    time.sleep(5) # wait for the document to come up
    self.local = uno.getComponentContext()
    self.resolver = self.local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", self.local)
#__main__.NoConnectException: Connector : couldn't connect to socket (Success)
    connected = False
    while connected == False:
      try:
        self.context = self.resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
        connected = True
      except NoConnectException:
        print >> sys.stderr, '! OOffice server not connected, trying again'
        time.sleep(1)
    time.sleep(1)
    self.desktop = self.context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", self.context)
    self.document = self.desktop.getCurrentComponent()
    assert self.document != None

  def _GetWorksheet(self, name=None):
    if name != None:
      self.worksheet = name
      self.sheets = self.document.getSheets()
      self.sheet = getattr(self.sheets, name)
    else: # prompt user
      self.sheets = self.document.getSheets()
      for i in range(self.sheets.Count):
        sheet = self.sheets.getByIndex(i)
        print '%s %s' % (i,sheet.getName())
      input = raw_input('\nSelection: ')
      self.sheet = self.sheets.getByIndex(string.atoi(input))
      self.worksheet = self.sheet.getName()
    self.num_rows = len(self.sheet.RowDescriptions) # HACK, but works?
    self.num_cols = len(self.sheet.ColumnDescriptions) # HACK, doesn't work

  def _GetCell(self, col, row):
    return Cell(self.sheet.getCellByPosition(col,row), col, row)

  def _GetRowCells(self, row, maxblanks=50):
    cells = []
    blanks = []
    col = 0
    while len(blanks) < maxblanks:
      cell = self._GetCell(col, row)
      if cell.type() == "EMPTY" :
        blanks.append(cell)
      else:
        cells.extend(blanks)
        blanks = []
        cells.append(cell)
      col += 1
    return cells

  def _GetRowCellsByColumn(self, row, columns):
    cells = []
    for col in columns:
      cells.append(self._GetCell(col, row))
    return cells

  def _GetColCells(self, col, maxblanks=50):
    cells = []
    blanks = []
    col = 0
    while len(blanks) < maxblanks:
      cell = self._GetCell(col, row)
      if cell.type == "EMPTY" :
        blanks.append(cell)
      else:
        cells.extend(blanks)
        blanks = []
        cells.append(cell)
      row += 1
    return cells

  def _GetColumn(self, column_title_row, column_title):
    row = self._GetRowCells(column_title_row)
    for i in range(len(row)-1, 0, -1): # search from the right-hand side b/c Venkat names Homework Submission and Hwk grades the same way...
      if row[i].getContents() == column_title:
        return i
    raise Exception, "Could not find column matching '%s'" % column_title

  def _GetColumns(self, column_title_row, column_titles):
    for column_title in column_titles:
      yield self._GetColumn(column_title_row, column_title)

  def _GetGradeFromRow(self, row_cells, assignment_column, firstname_column,
                       lastname_column):
    firstname_cell = row_cells[firstname_column]
    lastname_cell = row_cells[lastname_column]
    if firstname_cell.uno_cell == None or lastname_cell.uno_cell == None:
      return (None,None,None) # students need two names
    firstname = firstname_cell.getContents().strip()
    if len(firstname) == 0 or firstname[0].isdigit():
      return (None,None,None) # students first names must start with letters
    lastname = lastname_cell.getContents().strip()
    if len(lastname) == 0:
      return (None,None,None) # students must have last names
    grade = row_cells[assignment_column].getContents().strip()
    return (firstname, lastname, grade)

  def _ReadGradefile(self, gradefile):
    grades = {}
    for line in file(gradefile, 'r'):
      if len(line.strip()) == 0: continue
      fields = line.strip().split('\t') # [name, grade]
      names = fields[0].split() # ["John", "J", "J", "Smith"]
      grades[(names[0][0], names[-1])] = fields[1] # grades[('J','Smith')] = 5
    return grades

  def GetColumnTitles(self, title_row):
    for cell in self._GetRowCells(title_row):
        yield cell.getContents()

  def GetSpreadsheet(self, spreadsheet_name=None, worksheet_name=None):
    self._GetSpreadsheet(spreadsheet_name)
    self._GetWorksheet(worksheet_name)

  def GetGradesByAssignment(self,title_row,assignment,first_title,last_title,
                            maxblanks=10):
    names = [assignment, first_title, last_title]
    columns = list(self._GetColumns(title_row,
                                    [assignment,first_title,last_title]))
    blanks = 0
    i = title_row+1
    while blanks < maxblanks:
      cells = self._GetRowCellsByColumn(i, columns)
      i += 1
      if len(cells) == len([c for c in cells if c.type() == 'EMPTY']):
        blanks += 1; continue
      blanks = 0
      firstname,lastname,grade \
          = self._GetGradeFromRow(cells, 0, 1, 2)
      if firstname == None:
        continue # ignore non-student lines
      if grade == '': grade = '-'
      print "%s %s\t%s" % (firstname, lastname, grade)

  def SetGrades(self,title_row,assignment,first_title,last_title,gradefile,
                maxblanks=10):
    grades = self._ReadGradefile(gradefile)
    assignment_column, firstname_column, lastname_column \
        = self._GetColumns(title_row, [assignment, first_title, last_title])
    blanks = 0
    i = title_row+1
    while blanks < maxblanks and len(grades) > 0:
      cells = self._GetRowCellsByColumn(i, [assignment_column,
                                            firstname_column,
                                            lastname_column])
      i += 1
      if len(cells) == len([c for c in cells if c.type() == 'EMPTY']):
        blanks += 1; continue
      blanks = 0
      firstname,lastname,old_grade \
          = self._GetGradeFromRow(cells, 0, 1, 2)
      if firstname == None or (firstname[0], lastname) not in grades:
        continue # either not a student, or no grade for this student...
      new_grade = grades.pop((firstname[0], lastname))
      if old_grade != new_grade:
        if old_grade != '':
          if old_grade == "A" and new_grade == "0":
            continue # Don't rename absence grade.
          try:
            ogf = float(old_grade)
            ngf = float(new_grade)
          except:
            ogf = ngf = None
          if ogf != None and ngf != None and ogf > ngf:
            print >> sys.stderr, '! will not downgrade %s, %s -> %s for %s %s'\
                % (assignment, old_grade, new_grade, firstname, lastname)
            continue
          print >> sys.stderr, '! overwriting %s grade %s -> %s for %s %s' \
              % (assignment, old_grade, new_grade, firstname, lastname)
        cells[0].setContents(float(new_grade))
      print "%s\t%s %s\t%s" % (assignment, firstname, lastname, new_grade)

def main():
  import optparse

  usage='''%prog [options] assignment[,gradefile] [assignment,gradefile ...]
for example:

Show all the columns in the spreadsheet 'PHYSICS-101-Grades.ods',
worksheet 'Physics101Grades'.

  oograde.py -s 'PHYSICS-101-Grades.ods' -w 'Physics101Grades' -L

Show grades for all students on the assignment 'Week1'.  

  oograde.py -s 'PHYSICS-101-Grades.ods' -w 'Physics101Grades' Week1

Enter some Week1 grades

  echo -e "Jack Sprat\\t0\\nMiss Muffin\\t1\\nMother Hubbard\\t1\\n" > grades
  oograde.py -s 'PHYSICS-101-Grades.ods' -w 'Physics101Grades' Week1,grades
'''
  parser = optparse.OptionParser(usage=usage)
  parser.add_option('-s', '--spreadsheet', dest='spreadsheet',
                    help='Spreadsheet name (default: prompt)', metavar='NAME')
  parser.add_option('-w', '--worksheet', dest='worksheet',
                    help='Worksheet name (default: prompt)', metavar='NAME')
  parser.add_option('-t', '--title-row', dest='title_row', default=1,
                    help='Row containing column titles (%default, counting from 0)',
                    metavar='ROW', type='int')
  parser.add_option('-L', '--list-columns', dest='list_cols', default=False,
                    help='Print available columns and exit',
                    action='store_true')
  parser.add_option('-f', '--firstname-title', dest='first_title',
                    help='Row containing column titles (%default)',
                    metavar='NAME', default='First Name')
  parser.add_option('-l', '--lastname-title', dest='last_title',
                    help='Name of lastname column (%default)',
                    metavar='NAME', default='Last Name')
  options,args = parser.parse_args()
  
  oog = OOGrade()
  oog.GetSpreadsheet(options.spreadsheet, options.worksheet)
  try:
    if options.list_cols == True:
      for title in oog.GetColumnTitles(options.title_row):
        print title
    else:
      for arg in args:
        fields = arg.strip().split(',')
        if len(fields) == 1:
          grades = oog.GetGradesByAssignment(options.title_row, fields[0],
                                             options.first_title,
                                             options.last_title)
        elif len(fields) == 2:
          oog.SetGrades(options.title_row, fields[0],
                        options.first_title, options.last_title, fields[1])
        else:
          raise Exception, "invalid argument '%s'" % arg
    oog.save()
    oog.close()
  except Exception, e:
    oog.close()
    print >> sys.stderr, e
    sys.exit(1)

if __name__ == '__main__':
  main()