#!/usr/bin/env python
'''Read a spreadsheet and generate grade reports to the command line or a maildir.'''

import getopt,os,sys
from datetime import datetime
import xlrd

conv={'1.0':'OK', '1':'OK', '0.8':'So-so',
	'0.0':'Missing', 'M':'Missing', 'L':'Late', 'E':'Excused'}

isMbox = query = False

try:
	(options,files) = getopt.getopt (sys.argv[1:],"mn:")
except getopt.error:
	usage('getopt error: ' + str(msg))

# Options
for o, a in options:
	if o == '-m':
		isMbox = True
	if o == '-n':
		query = a

for file in files:
	fileName, extension = os.path.splitext(file)
	wb = xlrd.open_workbook(file)
	
	grad_sh = wb.sheet_by_name(u'Grades')
	grad_columns = {} # Categories of grades
	for colnum in range(grad_sh.ncols):
		grad_columns[colnum] = grad_sh.col_values(colnum)[0]
	grad_headings = dict([(term, token) for token, term in grad_columns.items()])

	attn_sh = wb.sheet_by_name(u'Attendance')
	attn_columns = {} # Headings (mostly dates) on attendance sheet
	for colnum in range(4,attn_sh.ncols):
		attn_columns[colnum] = "%2d/%2d" %(xlrd.xldate_as_tuple(attn_sh.col_values(colnum)[0],0)[1:3])

	rspn_sh = wb.sheet_by_name(u'Responses')
	rspn_columns = {} # Headings (mostly dates) on responses sheet
	for colnum in range(3,rspn_sh.ncols):
		rspn_columns[colnum] = "%2d/%2d" %(xlrd.xldate_as_tuple(rspn_sh.col_values(colnum)[0],0)[1:3])

	if isMbox:
		import mailbox
		import email
		sender = '"Joseph Reagle" <joseph.2008@reagle.org>'
		subject = 'Grade Report'
		maildir = mailbox.Maildir('/home/reagle/data/Mail/drafts/')

	for rownum in range(1,grad_sh.nrows-1): # for each student
		if grad_sh.cell(rownum,0).ctype == xlrd.XL_CELL_EMPTY: # if row empty
			continue
		else:
			if not query or query in grad_sh.cell(rownum,grad_headings['Name']).value:
				body = []
				for colnum in range(grad_sh.ncols):
					cell = grad_sh.cell(rownum,colnum)
					if cell.value != 0 and cell.ctype != xlrd.XL_CELL_EMPTY:
						if grad_columns[colnum] == 'Name':
							name = '"%s" ' %cell.value.strip()
							body.append(name + '\n')
						elif grad_columns[colnum] == 'Email':
							address = '<%s@nyu.edu>' %cell.value
							to = ''.join((name,address))
						else:
							body.append('\n%s	%s' %(grad_columns[colnum], cell.value))

				# attendance
				body.append("\nAttendance\n")
				for colnum in attn_columns.keys():
					cell = attn_sh.cell(rownum,colnum)
					if cell.ctype != xlrd.XL_CELL_EMPTY:
						body.append('%s	%s\n' %(attn_columns[colnum], conv[str(cell.value)]))

				# responses
				body.append("\nResponses\n")
				for colnum in rspn_columns.keys():
					cell = rspn_sh.cell(rownum,colnum)
					if cell.ctype != xlrd.XL_CELL_EMPTY:
						body.append('%s	%s\n' %(rspn_columns[colnum], conv[str(cell.value)]))
				if isMbox:
					maildir.lock()
					message = mailbox.Message()
					message["To"]      = to
					message["From"]    = sender
					message["Subject"] = subject
					message.set_payload(''.join(body))
					maildir.add(message)
					maildir.close()
				else:
					print ''.join(body)