def convert(input):
# if we can not find a match, we return "None" (default)
result = "None"
# using a set to find the sections (unique elements only)
findSection = set([])
# Connect to the database.
conn = cConnect.connection()
c = conn.cursor()
c.execute("START TRANSACTION")
c.execute("SELECT * FROM `class` " )
for section in c.fetchall():
# if section number, this means we need to format it to work with banner's formatting
if (input.find('.') != -1):
input = input.replace(" ", "")
input = input.replace(".0", ".")
# then we can match this up with a subject code and course number
if (input.upper() == section[2]+section[3]+'.'+section[4]):
# if match, we want to return the CRN
result = section[0]
# subject code + course number (no section number)
elif (input[0].isalpha() and input[len(input)-1].isdigit()):
input = input.replace(" ", "")
input = input.upper()
if(input.upper() == section[2]+section[3]):
findSection.add(section[2]+section[3]+'.'+section[4])
# if match, we want to return the CRN
result = section[0]
# subject code only
elif(input[0].isalpha() and len(input) < 5):
input = input.upper()
if (input == section[1]):
findSection.add(section[2]+section[3]+'.'+section[4])
# if match, we want to return the CRN
result = section[0]
# course title
else:
if (input.lower() == section[5].lower()):
findSection.add(section[2]+section[3]+'.'+section[4])
# if match, we want to return the CRN
result = section[0]
# if the course has at least 2 sections, ask the user to tell us which one he/she wants
if (len(findSection) > 1):
temp = input+' has '+str(len(findSection))+' sections, please choose one: '+findSection.pop()
while (len(findSection)) != 0:
temp += ', '+findSection.pop()
result = temp
c.close()
conn.close()
return str(result)
def getClassInfo(course, term):
# connect to database
conn = cConnect.connection()
c = conn.cursor()
c.execute("START TRANSACTION")
section = getBasics(course, term, c)
detail = getClassDetails(course, term, c)
breakdown = getClassificationBreakdown(course, term, c)
# prompt user to change...
# time: days will remain the same
# both: day and time can be changed
# done: exit the program
print('\n--------------------------------------------------------')
change = raw_input('\nChange time (time), day and time (both), or done: ')
change = change.lower()
while( change != 'done' ):
trynew = '' # variable for different time
# variable for days (default is current schedule)
changeDay = detail[3]+detail[4]+detail[5]+detail[6]+detail[7]
duration = 0 # change from 3 days to 2, or 2 to 1, or 3 to 1, or vica versa, duration will change
if change == 'time':
trynew = raw_input('\nNew time: ')
while (not trynew.isdigit()):
trynew = raw_input('\nPlease enter a number: ')
# default duration from current schedule if only change time, not days
duration = int(detail[9]) - int(detail[8])
elif change == 'both':
trynew = raw_input('\nNew time: ')
while (not trynew.isdigit()):
trynew = raw_input('\nPlease enter a number: ')
changeDay = raw_input('\nNew days (MWF, TR, MW, M, W, F): ')
while (not changeDay.isalpha()):
changeDay = raw_input('\nPlease enter your days: ')
changeDay = changeDay.upper()
# duration check
if len(changeDay) == 3:
print('Class duration: 50 minutes x 3 days')
duration = 50
elif len(changeDay) == 2:
print('Class duration: 80 minutes x 2 days')
duration = 80
elif len(changeDay) == 1:
duration = 50 * int(section[7])
print('Class duration: '+str(duration)+' minutes x '+str(len(changeDay))+' day')
dList = []
dList = dList + ['']*5
# list of days (parallelized to fit DB output = less checking required)
for i in changeDay:
if i == 'M':
dList[0] = i
elif i == 'T':
dList[1] = i
elif i == 'W':
dList[2] = i
elif i == 'R':
dList[3] = i
elif i == 'F':
dList[4] = i
# need new end time to go with new start time so we can find conflicts
end = 0
duration = int(duration)
trynew = int(trynew)
if duration == 80:
end = trynew + 120
elif duration == 150:
end = trynew + 250
else:
end = trynew + duration
# output what we are trying to do
print('\nAttempting to move '+section[3]+section[4]+': '+section[6])
print('From '+detail[3]+' '+detail[4]+' '+detail[5]+' '+detail[6]+' '+detail[7]+' '+detail[8]+' - '+detail[9])
print('To '+dList[0]+' '+dList[1]+' '+dList[2]+' '+dList[3]+' '+dList[4]+' '+str(trynew)+' - '+str(end))
iStatus = getInstructorDetails(course, term, c, dList, detail, trynew, end)
conflicts = getStudentDetails(course, term, c, dList, trynew, end)
total = int(detail[13]) + 1
# output conflict breakdown
print('\n\n=========================================================')
print('Total Conflicts: '+str(conflicts[0]+iStatus[1])+' / '+str(total))+' \t\t(instructor included)'
print('=========================================================')
print('\nInstructor Status: '+iStatus[0])
print('\nStudent Status: ')
print('\nSeniors \t'+str(conflicts[4])+' / '+str(breakdown[3])+' have a conflict')
print('Juniors \t'+str(conflicts[3])+' / '+str(breakdown[2]))
print('Sophomores \t'+str(conflicts[2])+' / '+str(breakdown[1]))
print('Freshmen \t'+str(conflicts[1])+' / '+str(breakdown[0]))
print('\n--------------------------------------------------------')
change = raw_input('\nChange time (time), day and time (both), or done: ')
print('\n')
c.close()
conn.close()
def setupDB():
cgitb.enable()
# connect to database
conn = cConnect.connection()
c = conn.cursor()
c.execute("START TRANSACTION")
count = 0
if (count == 0):
c.execute("DROP TABLE IF EXISTS `student`")
c.execute(
"CREATE TABLE `student` (id varchar(10) NOT NULL, firstName varchar(20) NOT NULL, lastName varchar(20) NOT NULL, classification varchar(10), email varchar(100), dept varchar(50), hours int(10), grade varchar(2), major varchar(50), PRIMARY KEY(id))"
)
c.execute("DROP TABLE IF EXISTS `enrollment`")
c.execute(
"CREATE TABLE `enrollment` (id varchar(10) NOT NULL, crn varchar(10), termCode varchar(10), hours int(3), PRIMARY KEY(id, crn, termCode))"
)
c.execute("DROP TABLE IF EXISTS `section`")
c.execute(
"CREATE TABLE `section` (crn varchar(10), termCode varchar(10) NOT NULL, profID varchar(10), d1 varchar(3), d2 varchar(3), d3 varchar(3), d4 varchar(3), d5 varchar(3), beginTime varchar(5), endTime varchar(5), building varchar(6), room varchar(5), capacity varchar(3), enrolled varchar(3), PRIMARY KEY(crn, termCode))"
)
c.execute("DROP TABLE IF EXISTS `class`")
c.execute(
"CREATE TABLE `class` (crn varchar(10), termCode varchar(10) NOT NULL, subjectCode varchar(5), courseNum varchar(5), section varchar(5), title varchar(80), credits int(5), PRIMARY KEY(crn, termCode, subjectCode, courseNum))"
)
c.execute("DROP TABLE IF EXISTS `instructor`")
c.execute(
"CREATE TABLE `instructor` (id varchar(10), name varchar(30), PRIMARY KEY(id))"
)
c.execute("DROP TABLE IF EXISTS `instSection`")
c.execute(
"CREATE TABLE `instSection` (id varchar(10), crn varchar(10), termCode varchar(10), eval varchar(3), PRIMARY KEY(id, crn, termCode))"
)
count += 1
# since we now know CRNs are reused, let's read in the file and make the primary key the max('Term Code') and CRN for section
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
term = 0
for t in reader:
if (t['Term Code'] > term): # get most recent term from csv
term = t[
'Term Code'] # we only want to deal with the most recent since CRNs are reused
print('Term: ' + term)
f.close()
n = 6
x = 0
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if (row['Term Code'] == term):
# section: crn, termCode, profID, d1, d2, d3, d4, d5, beginTime, endTime, building, room, capacity, enrolled
c.execute(
"""INSERT IGNORE INTO `section`
(`crn`, `termCode`, `profID`, `d1`, `d2`, `d3`, `d4`, `d5`, `beginTime`, `endTime`, `building`, `room`, `capacity`, `enrolled`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
(str(row['CRN']), str(row['Term Code']),
str(row['Instructor ID']), str(row['Monday Ind1']),
str(row['Tuesday Ind1']), str(row['Wednesday Ind1']),
str(row['Thursday Ind1']), str(row['Friday Ind1']),
str(row['Begin Time 1']), str(row['End Time1']),
str(row['Bldg Code1']), str(
row['Room Code1']), str(row['Section Max Enrollment']),
str(row['Section Enrollment'])))
x += 1
print('section added...\t(' + str(x) + ' / ' + str(n) + ' complete)')
conn.commit()
f.close()
# c.execute("SELECT * FROM `section`")
# for test in c.fetchall():
# print(test[0])
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if (row['Term Code'] == term):
# student: id, firstName, lastName, classification, email, dept, hours, grade
c.execute(
"""INSERT IGNORE INTO `student`
(`id`, `firstName`, `lastName`, `classification`, `email`, `dept`, `hours`, `grade`, `major`)
VALUES (%s, %s, %s, %s, %s, %s, 0, %s, %s)""",
(str(row['Banner ID']), str(row['First Name']),
str(row['Last Name']), str(row['Class Code']),
str(row['ACU Email Address']), str(row['Department Code']),
str(row['Grade Code']), str(row['Major Desc1'])))
x += 1
print('student added...\t(' + str(x) + ' / ' + str(n) + ' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if (row['Term Code'] == term):
# class: crn, termCode, subjectCode, courseNum, section, title
c.execute(
"""INSERT IGNORE INTO `class`
(`crn`, `termCode`, `subjectCode`, `courseNum`, `section`, `title`, `credits`)
VALUES (%s, %s, %s, %s, %s, %s, %s)""",
(str(row['CRN']), str(row['Term Code']),
str(row['Subject Code']), str(row['Course Number']),
str(row['Section Number']), str(
row['Course Title']), str(row['Credit Hours'])))
x += 1
print('class added...\t\t(' + str(x) + ' / ' + str(n) + ' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if (row['Term Code'] == term):
# instructor: id, prof
c.execute(
"""INSERT IGNORE INTO `instructor`
(`id`, `name`)
VALUES (%s, %s)""",
(str(row['Instructor ID']), str(row['Instructor Name'])))
x += 1
print('instructor added...\t(' + str(x) + ' / ' + str(n) + ' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if (row['Term Code'] == term):
# instSection: id, crn, termCode, eval
c.execute(
"""INSERT IGNORE INTO `instSection`
(`id`, `crn`, `termCode`, `eval`)
VALUES (%s, %s, %s, '')""", (str(
row['Banner ID']), str(row['CRN']), str(row['Term Code'])))
x += 1
print('teaches added...\t(' + str(x) + ' / ' + str(n) + ' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
# enrollment: id, crn, termCode, hours
c.execute(
"""INSERT IGNORE INTO `enrollment`
(`id`, `crn`, `termCode`, `hours`)
VALUES (%s, %s, %s, %s)""",
(str(row['Banner ID']), str(row['CRN']), str(
row['Term Code']), str(row['Credit Hours'])))
x += 1
print('enrollment added...\t(' + str(x) + ' / ' + str(n) + ' complete)\n')
conn.commit()
f.close()
c.close()
conn.close()
return term
def getClassInfo(course, term):
# connect to database
conn = cConnect.connection()
c = conn.cursor()
c.execute("START TRANSACTION")
section = getBasics(course, term, c)
detail = getClassDetails(course, term, c)
breakdown = getClassificationBreakdown(course, term, c)
# prompt user to change...
# time: days will remain the same
# both: day and time can be changed
# done: exit the program
print('\n--------------------------------------------------------')
change = raw_input('\nChange time (time), day and time (both), or done: ')
change = change.lower()
while (change != 'done'):
trynew = '' # variable for different time
# variable for days (default is current schedule)
changeDay = detail[3] + detail[4] + detail[5] + detail[6] + detail[7]
duration = 0 # change from 3 days to 2, or 2 to 1, or 3 to 1, or vica versa, duration will change
if change == 'time':
trynew = raw_input('\nNew time: ')
while (not trynew.isdigit()):
trynew = raw_input('\nPlease enter a number: ')
# default duration from current schedule if only change time, not days
duration = int(detail[9]) - int(detail[8])
elif change == 'both':
trynew = raw_input('\nNew time: ')
while (not trynew.isdigit()):
trynew = raw_input('\nPlease enter a number: ')
changeDay = raw_input('\nNew days (MWF, TR, MW, M, W, F): ')
while (not changeDay.isalpha()):
changeDay = raw_input('\nPlease enter your days: ')
changeDay = changeDay.upper()
# duration check
if len(changeDay) == 3:
print('Class duration: 50 minutes x 3 days')
duration = 50
elif len(changeDay) == 2:
print('Class duration: 80 minutes x 2 days')
duration = 80
elif len(changeDay) == 1:
duration = 50 * int(section[7])
print('Class duration: ' + str(duration) + ' minutes x ' +
str(len(changeDay)) + ' day')
dList = []
dList = dList + [''] * 5
# list of days (parallelized to fit DB output = less checking required)
for i in changeDay:
if i == 'M':
dList[0] = i
elif i == 'T':
dList[1] = i
elif i == 'W':
dList[2] = i
elif i == 'R':
dList[3] = i
elif i == 'F':
dList[4] = i
# need new end time to go with new start time so we can find conflicts
end = 0
duration = int(duration)
trynew = int(trynew)
if duration == 80:
end = trynew + 120
elif duration == 150:
end = trynew + 250
else:
end = trynew + duration
# output what we are trying to do
print('\nAttempting to move ' + section[3] + section[4] + ': ' +
section[6])
print('From ' + detail[3] + ' ' + detail[4] + ' ' + detail[5] + ' ' +
detail[6] + ' ' + detail[7] + ' ' + detail[8] + ' - ' +
detail[9])
print('To ' + dList[0] + ' ' + dList[1] + ' ' + dList[2] + ' ' +
dList[3] + ' ' + dList[4] + ' ' + str(trynew) + ' - ' + str(end))
iStatus = getInstructorDetails(course, term, c, dList, detail, trynew,
end)
conflicts = getStudentDetails(course, term, c, dList, trynew, end)
total = int(detail[13]) + 1
# output conflict breakdown
print('\n\n=========================================================')
print('Total Conflicts: ' + str(conflicts[0] + iStatus[1]) + ' / ' +
str(total)) + ' \t\t(instructor included)'
print('=========================================================')
print('\nInstructor Status: ' + iStatus[0])
print('\nStudent Status: ')
print('\nSeniors \t' + str(conflicts[4]) + ' / ' + str(breakdown[3]) +
' have a conflict')
print('Juniors \t' + str(conflicts[3]) + ' / ' + str(breakdown[2]))
print('Sophomores \t' + str(conflicts[2]) + ' / ' + str(breakdown[1]))
print('Freshmen \t' + str(conflicts[1]) + ' / ' + str(breakdown[0]))
print('\n--------------------------------------------------------')
change = raw_input(
'\nChange time (time), day and time (both), or done: ')
print('\n')
c.close()
conn.close()
def setupDB():
cgitb.enable()
# connect to database
conn = cConnect.connection()
c = conn.cursor()
c.execute("START TRANSACTION")
count = 0
if (count == 0):
c.execute("DROP TABLE IF EXISTS `student`")
c.execute("CREATE TABLE `student` (id varchar(10) NOT NULL, firstName varchar(20) NOT NULL, lastName varchar(20) NOT NULL, classification varchar(10), email varchar(100), dept varchar(50), hours int(10), grade varchar(2), major varchar(50), PRIMARY KEY(id))")
c.execute("DROP TABLE IF EXISTS `enrollment`")
c.execute("CREATE TABLE `enrollment` (id varchar(10) NOT NULL, crn varchar(10), termCode varchar(10), hours int(3), PRIMARY KEY(id, crn, termCode))")
c.execute("DROP TABLE IF EXISTS `section`")
c.execute("CREATE TABLE `section` (crn varchar(10), termCode varchar(10) NOT NULL, profID varchar(10), d1 varchar(3), d2 varchar(3), d3 varchar(3), d4 varchar(3), d5 varchar(3), beginTime varchar(5), endTime varchar(5), building varchar(6), room varchar(5), capacity varchar(3), enrolled varchar(3), PRIMARY KEY(crn, termCode))")
c.execute("DROP TABLE IF EXISTS `class`")
c.execute("CREATE TABLE `class` (crn varchar(10), termCode varchar(10) NOT NULL, subjectCode varchar(5), courseNum varchar(5), section varchar(5), title varchar(80), credits int(5), PRIMARY KEY(crn, termCode, subjectCode, courseNum))")
c.execute("DROP TABLE IF EXISTS `instructor`")
c.execute("CREATE TABLE `instructor` (id varchar(10), name varchar(30), PRIMARY KEY(id))")
c.execute("DROP TABLE IF EXISTS `instSection`")
c.execute("CREATE TABLE `instSection` (id varchar(10), crn varchar(10), termCode varchar(10), eval varchar(3), PRIMARY KEY(id, crn, termCode))")
count += 1
# since we now know CRNs are reused, let's read in the file and make the primary key the max('Term Code') and CRN for section
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
term = 0
for t in reader:
if(t['Term Code'] > term): # get most recent term from csv
term = t['Term Code'] # we only want to deal with the most recent since CRNs are reused
print('Term: '+term)
f.close()
n = 6
x = 0
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if(row['Term Code'] == term):
# section: crn, termCode, profID, d1, d2, d3, d4, d5, beginTime, endTime, building, room, capacity, enrolled
c.execute("""INSERT IGNORE INTO `section`
(`crn`, `termCode`, `profID`, `d1`, `d2`, `d3`, `d4`, `d5`, `beginTime`, `endTime`, `building`, `room`, `capacity`, `enrolled`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
(str(row['CRN']),
str(row['Term Code']),
str(row['Instructor ID']),
str(row['Monday Ind1']),
str(row['Tuesday Ind1']),
str(row['Wednesday Ind1']),
str(row['Thursday Ind1']),
str(row['Friday Ind1']),
str(row['Begin Time 1']),
str(row['End Time1']),
str(row['Bldg Code1']),
str(row['Room Code1']),
str(row['Section Max Enrollment']),
str(row['Section Enrollment'])
)
)
x += 1
print('section added...\t('+str(x)+' / '+str(n)+' complete)')
conn.commit()
f.close()
# c.execute("SELECT * FROM `section`")
# for test in c.fetchall():
# print(test[0])
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if(row['Term Code'] == term):
# student: id, firstName, lastName, classification, email, dept, hours, grade
c.execute("""INSERT IGNORE INTO `student`
(`id`, `firstName`, `lastName`, `classification`, `email`, `dept`, `hours`, `grade`, `major`)
VALUES (%s, %s, %s, %s, %s, %s, 0, %s, %s)""",
(str(row['Banner ID']),
str(row['First Name']),
str(row['Last Name']),
str(row['Class Code']),
str(row['ACU Email Address']),
str(row['Department Code']),
str(row['Grade Code']),
str(row['Major Desc1'])
)
)
x += 1
print('student added...\t('+str(x)+' / '+str(n)+' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if(row['Term Code'] == term):
# class: crn, termCode, subjectCode, courseNum, section, title
c.execute("""INSERT IGNORE INTO `class`
(`crn`, `termCode`, `subjectCode`, `courseNum`, `section`, `title`, `credits`)
VALUES (%s, %s, %s, %s, %s, %s, %s)""",
(str(row['CRN']),
str(row['Term Code']),
str(row['Subject Code']),
str(row['Course Number']),
str(row['Section Number']),
str(row['Course Title']),
str(row['Credit Hours'])
)
)
x += 1
print('class added...\t\t('+str(x)+' / '+str(n)+' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if(row['Term Code'] == term):
# instructor: id, prof
c.execute("""INSERT IGNORE INTO `instructor`
(`id`, `name`)
VALUES (%s, %s)""",
(str(row['Instructor ID']),
str(row['Instructor Name'])
)
)
x += 1
print('instructor added...\t('+str(x)+' / '+str(n)+' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
if(row['Term Code'] == term):
# instSection: id, crn, termCode, eval
c.execute("""INSERT IGNORE INTO `instSection`
(`id`, `crn`, `termCode`, `eval`)
VALUES (%s, %s, %s, '')""",
(str(row['Banner ID']),
str(row['CRN']),
str(row['Term Code'])
)
)
x += 1
print('teaches added...\t('+str(x)+' / '+str(n)+' complete)')
conn.commit()
f.close()
f = open('info/cs374_anon.csv')
reader = csv.DictReader(f)
for row in reader:
# enrollment: id, crn, termCode, hours
c.execute("""INSERT IGNORE INTO `enrollment`
(`id`, `crn`, `termCode`, `hours`)
VALUES (%s, %s, %s, %s)""",
(str(row['Banner ID']),
str(row['CRN']),
str(row['Term Code']),
str(row['Credit Hours'])
)
)
x += 1
print('enrollment added...\t('+str(x)+' / '+str(n)+' complete)\n')
conn.commit()
f.close()
c.close()
conn.close()
return term
