#!/usr/bin/python
#
# Programatically edit grades on a google spreadsheet.
#
# Copyright (C) 2007 Google Inc., 2009 W. Trevor King
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
# Based on spreadsheetExample.py by Laura Beth Lincoln

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


try:
  from xml.etree import ElementTree
except ImportError:
  from elementtree import ElementTree
import gdata.spreadsheet.service
import gdata.service
import atom.service
import gdata.spreadsheet
import atom
import getpass
import sys
import string


class Cell:
  def __init__(self, cell_entry, col, row):
    self.col = col
    self.row = row
    self.entry = cell_entry
  def __str__(self):
    if self.entry == None:
      return "<>"
    return "<%s %s>" % (self.entry.title.text, self.entry.content.text)
  def __repr__(self):
    return str(self)
  def value(self):
    if self.entry == None:
      return ''
    else:
      return self.entry.content.text

class GoogleGrade:
  def __init__(self, email=None, password=None):
    self.gd_client = gdata.spreadsheet.service.SpreadsheetsService()
    self.gd_client.source = 'GoogleGrades'
    if email == None:
      self.gd_client.email = raw_input('username: ')
    else:
      self.gd_client.email = email
    if password == None:
      self.gd_client.password = getpass.getpass('password: ', sys.stderr)
    else:
      self.gd_client.password = password
    self.gd_client.ProgrammaticLogin()
    self.curr_key = ''
    self.curr_wksht_id = ''

  def _GetSpreadsheet(self, name=None):
    # Get the list of spreadsheets
    feed = self.gd_client.GetSpreadsheetsFeed()
    if name != None:
      # pick automatically using the supplied name
      entry = self._PickFromFeed(feed, name)
    else: # let the user pick from the command line
      self._PrintFeed(feed)
      input = raw_input('\nSelection: ')
      entry = feed.entry[string.atoi(input)]
    id_parts = entry.id.text.split('/')
    self.curr_key = id_parts[len(id_parts) - 1]

  def _GetWorksheet(self, name=None):
    # Get the list of worksheets
    feed = self.gd_client.GetWorksheetsFeed(self.curr_key)
    if name != None:
      # pick automatically using the supplied name
      entry = self._PickFromFeed(feed, name)
    else: # let the user pick from the command line
      self._PrintFeed(feed)
      input = raw_input('\nSelection: ')
      entry = feed.entry[string.atoi(input)]
    id_parts = entry.id.text.split('/')
    self.curr_wksht_id = id_parts[len(id_parts) - 1]

  def _GetAllCells(self, dump=False, show_array=False):
    # Get the feed of cells
    feed = self.gd_client.GetCellsFeed(self.curr_key, self.curr_wksht_id)
    self.cell_feed = feed
    if dump == True:
        self._PrintFeed(feed)
    crs = [self._CellColumnRow(c) for c in self.cell_feed.entry]
    columns,rows = zip(*crs)
    self.max_col = max(columns)
    self.max_row = max(rows)
    self.data_array = []
    for r in range(self.max_row):
      row = []
      for c in range(self.max_col):
        row.append(Cell(None, c+1, r+1))
      self.data_array.append(row)
    for entry in self.cell_feed.entry:
      col,row = self._CellColumnRow(entry)
      self.data_array[row-1][col-1] = Cell(entry, col, row)
    # access with self.data_array[row-1][col-1]
    if show_array == True:
      for i in range(self.max_row):
        print self.data_array[i]

  def _CellsUpdateAction(self, row, col, inputValue):
    entry = self.gd_client.UpdateCell(row=row, col=col, inputValue=inputValue,
        key=self.curr_key, wksht_id=self.curr_wksht_id)
    if isinstance(entry, gdata.spreadsheet.SpreadsheetsCell):
      print 'Updated!'

  def _PrintFeed(self, feed):
    for i, entry in enumerate(feed.entry):
      if isinstance(feed, gdata.spreadsheet.SpreadsheetsCellsFeed):
        print '%s %s\n' % (entry.title.text, entry.content.text)
      else:
        print '%s %s\n' % (i, entry.title.text)

  def _PickFromFeed(self, feed, item_name):
    for entry in feed.entry:
      if entry.title.text == item_name:
        return entry

  def _GetColumn(self, column_title_row, column_title):
    for i,cell in enumerate(self.data_array[column_title_row-1]):
      if cell.value() == column_title:
        return i+1

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

  def _AlphaIntToColumnRow(self, alphaint):
    '(Column,Row) both count from 1'
    column_letters = ''.join([c for c in alphaint if c.isalpha()])
    assert alphaint.startswith(column_letters), \
        "%s -?-> %s" % (alphaint, column_letters)
    row_number = int(alphaint.replace(column_letters, '', 1))
    column_number = 0
    for i,char in enumerate(column_letters[::-1]):
      column_number += 26**i * (ord(char) - ord('A') + 1)
    return (column_number, row_number)

  def _ColumnRowToAlphaInt(self, column, row):
    "Alpha counts from 'A'; int counts from 1"
    alpha = ''
    while column > 0:
      lastdigit = (column-1) % 26
      column = (column-lastdigit)/26
      alpha = chr(lastdigit+ord('A')) + alpha
    return '%s%d' % (alpha, row)

  def _CellColumnRow(self, cell_entry):
    alphaint = cell_entry.title.text # e.g. "A1" or "AM421"
    return self._AlphaIntToColumnRow(alphaint)

  def _GetGradeFromRow(self, row_cells, assignment_column, firstname_column,
                       lastname_column):
    firstname_cell = row_cells[firstname_column-1]
    lastname_cell = row_cells[lastname_column-1]
    if firstname_cell.entry == None or lastname_cell.entry == None:
      return (None,None,None) # students need two names
    firstname = firstname_cell.value().strip()
    if firstname[0].isdigit():
      return (None,None,None) # students first names must start with letters
    lastname = lastname_cell.value().strip()
    grade = row_cells[assignment_column-1].value().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 i,entry in enumerate(self.cell_feed.entry):
      col,row = self._CellColumnRow(entry)
      if row == title_row:
        yield entry.content.text

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

  def GetGradesByAssignment(self,title_row,assignment,first_title,last_title):
    assignment_column, firstname_column, lastname_column \
        = self._GetColumns(title_row, [assignment, first_title, last_title])
    for row in range(title_row+1, self.max_row+1):
      firstname,lastname,grade \
          = self._GetGradeFromRow(self.data_array[row-1], assignment_column,
                                  firstname_column, lastname_column)
      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):
    grades = self._ReadGradefile(gradefile)
    assignment_column, firstname_column, lastname_column \
        = self._GetColumns(title_row, [assignment, first_title, last_title])
    for row in range(title_row+1, self.max_row+1):
      firstname,lastname,old_grade \
          = self._GetGradeFromRow(self.data_array[row-1], assignment_column,
                                  firstname_column, lastname_column)
      if firstname == None or (firstname[0], lastname) not in grades:
        continue # either not a student, or no grade for this student...
      new_grade = grades[(firstname[0], lastname)]
      if old_grade != new_grade:
        if old_grade != '':
          print >> sys.stderr, '! overwriting %s grade %s -> %s for %s %s' \
              % (assignment, old_grade, new_grade, firstname, lastname)
        self._CellsUpdateAction(row, assignment_column, 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',
worksheet 'Physics101Grades'.  Log in using google account 'jdoe'.

  ggrade.py -u jdoe -s 'PHYSICS 101 Grades' -w 'Physics101Grades' -L

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

  ggrade.py -u jdoe -s 'PHYSICS 101 Grades' -w 'Physics101Grades' Week1

Enter some Week1 grades

  echo -e "Jack Sprat\\t0\\nMiss Muffin\\t1\\nMother Hubbard\\t1\\n" > grades
  ggrade.py -u jdoe -s 'PHYSICS 101 Grades' -w 'Physics101Grades' Week1,grades
'''
  parser = optparse.OptionParser(usage=usage)
  parser.add_option('-u', '--user', dest='user',
                    help='Google username', metavar='USER')
  parser.add_option('-p', '--passwd', dest='password',
                    help='Google password (default: prompt)', metavar='PASS')
  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=2,
                    help='Row containing column titles (%default)',
                    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()
  
  gg = GoogleGrade(options.user, options.password)
  gg.GetSpreadsheet(options.spreadsheet, options.worksheet)

  if options.list_cols == True:
    for title in gg.GetColumnTitles(options.title_row):
      print title
  else:
    for arg in args:
      fields = arg.strip().split(',')
      if len(fields) == 1:
        grades = gg.GetGradesByAssignment(options.title_row, fields[0],
                                          options.first_title,
                                          options.last_title)
      elif len(fields) == 2:
        gg.SetGrades(options.title_row, fields[0],
                     options.first_title, options.last_title, fields[1])
      else:
        raise Exception, "invalid argument '%s'" % arg

if __name__ == '__main__':
  main()