import pandas
import re

sheet = pandas.read_excel('F23 SoC TA Office Hours (In-Person and Virtual).xlsx', sheet_name=0)

table = dict()

for row in range(sheet.shape[0]):
    if str(sheet.loc[row, 'Location']).__contains__('McAdams'):
        if sheet.loc[row, 'TA Course'] not in table:
            table.setdefault(sheet.loc[row, 'TA Course'], dict())
        
for row in range(sheet.shape[0]):
    if str(sheet.loc[row, 'Location']).__contains__('McAdams'):
        if sheet.loc[row, 'Day'] not in table[sheet.loc[row, 'TA Course']]:
            table[sheet.loc[row, 'TA Course']].setdefault(sheet.loc[row, 'Day'], list())
        
for row in range(sheet.shape[0]):
    if str(sheet.loc[row, 'Location']).__contains__('McAdams'):
    
        startTime = re.sub('AM', '', sheet.loc[row, 'Start Time'])
        startTime = re.sub('PM', '', startTime)
        endTime = re.sub('AM', '', sheet.loc[row, 'End Time'])
        endTime = re.sub('PM', '', endTime)
        location = re.sub('McAdams Hall ', '', sheet.loc[row, 'Location'])
        
        record = startTime + " - " + endTime + " (" + location + ")"
        
        if record not in table[sheet.loc[row, 'TA Course']][sheet.loc[row, 'Day']]:
            table[sheet.loc[row, 'TA Course']][sheet.loc[row, 'Day']].append(record)
    
days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']

with open('tablegen.html', 'w') as outFile:
    for course in table:
        outFile.write('<tr>\n')
        outFile.write('\t<td>' + course + '</td>\n')
        for day in days:
            if day in table[course]:
                record = '<br />'.join(table[course][day])
                outFile.write('\t<td>' + record + '</td>\n')
            else:
                outFile.write('\t<td></td>\n')
        outFile.write('</tr>\n')