def loadCourses(request):
data = request.GET
cursor = MySQLConnection()
coursesData = cursor.execute(
'SELECT course.idCourse, course.name, course.courseCode FROM rel_cycle_opticalSheet JOIN aggr_opticalSheetField on aggr_opticalSheetField.idOpticalSheet = rel_cycle_opticalSheet.idOpticalSheet JOIN aggr_offer ON aggr_offer.idOffer = aggr_opticalSheetField.idOffer JOIN course ON course.idCourse = aggr_offer.idCourse WHERE idTimePeriod = '
+ str(data['idTimePeriod']) + ' AND idCycle = ' +
str(data['idCycle']) + ' AND term = ' + str(data['term']) +
' GROUP BY idCourse')
courses = []
for courseData in coursesData:
course = {}
course['courseCode'] = courseData[2]
course['name'] = courseData[1]
course['idCourse'] = courseData[0]
courses.append(course)
assessmentsData = cursor.execute(
'SELECT aggr_survey.assessmentNumber FROM rel_cycle_opticalSheet JOIN aggr_opticalSheetField on aggr_opticalSheetField.idOpticalSheet = rel_cycle_opticalSheet.idOpticalSheet JOIN aggr_offer ON aggr_offer.idOffer = aggr_opticalSheetField.idOffer JOIN aggr_survey ON aggr_survey.idOpticalSheet = rel_cycle_opticalSheet.idOpticalSheet WHERE idTimePeriod = '
+ str(data['idTimePeriod']) + ' AND idCycle = ' +
str(data['idCycle']) + ' AND term = ' + str(data['term']) +
' GROUP BY aggr_survey.assessmentNumber')
assessments = [int(assessment[0]) for assessment in assessmentsData]
response = {}
response['courses'] = courses
response['assessments'] = assessments
return HttpResponse(json.dumps(response))
def tearDown(self):
cursor = MySQLConnection()
[offer.delete() for offer in Offer.find()]
[schedule.delete() for schedule in Schedule.find()]
cursor.execute('DELETE FROM minitableDayOfTheWeek')
[timePeriod.delete() for timePeriod in TimePeriod.find()]
[course.delete() for course in Course.find()]
[professor.delete() for professor in Professor.find()]
self.browser.quit()
def tearDown(self):
cursor = MySQLConnection()
[offer.delete() for offer in Offer.find()]
[schedule.delete() for schedule in Schedule.find()]
cursor.execute('DELETE FROM minitableDayOfTheWeek')
[timePeriod.delete() for timePeriod in TimePeriod.find()]
[course.delete() for course in Course.find()]
[professor.delete() for professor in Professor.find()]
self.browser.quit()
def __init__(self):
self.connection = MySQLConnection()
self.timeperiod = None
self.cycle = None
self.coursereader = None
self.offerreader = None
self.crawler = Crawler()
self.offerreader = OfferReader(self.connection, self.cycle,
self.timeperiod)
def create_timePeriod_and_course(self):
cursor = MySQLConnection()
length = cursor.execute('SELECT idLength FROM minitableLength where length="Semestral"')
if not length:
cursor.execute('INSERT INTO minitableLength (length) values ("Semestral")')
self.course = Course('tst9999', 'teste9999', '0000-00-00')
self.course.store()
length = cursor.execute('SELECT idLength FROM minitableLength where length="Semestral"')
self.timePeriod = TimePeriod(1, 2014, 1)
self.timePeriod.store()
def findCourses(searchedAbbreviation,
searchedCourseCode,
searchedName,
idTimePeriod,
idCycle=None,
term=None):
"""
returns a list of dicts containig the keys: courseName, courseAbbreviation,
courseCode and idCourse.
@param string searchedAbbreviation : Part of the wanted course's abbreviation.
@param string searchedCourseCode : Part of the wanted course's courseCode
@param string searchedName : Part of the wanted course's name.
@param int idTimePeriod : The idTimePeriod of the wanted course's timePeriod.
@param int idCycle : Is None by default, if it is set the courses related to the chosen cycle will be the firsts of the returned list.
@param int term : The term related to the idCycle, is None by default.
@return [] :
@author
"""
cursor = MySQLConnection()
activeIdCourses = cursor.execute(
'SELECT idCourse FROM aggr_offer WHERE idTimePeriod = ' +
str(idTimePeriod) + ' GROUP BY idCourse')
activeIdCourses = [course[0] for course in activeIdCourses]
firstIdCourses = []
if idCycle != None or term != None:
query = 'SELECT idCourse FROM rel_course_cycle WHERE endDate = "0000-00-00"'
if idCycle != None:
query = query + ' AND idCycle = ' + str(idCycle)
if term != None:
query = query + ' AND term = ' + str(term)
firstIdCourses = cursor.execute(query)
firstIdCourses = [course[0] for course in firstIdCourses]
courses = Course.find(abbreviation_like=searchedAbbreviation,
courseCode_like=searchedCourseCode,
name_like=searchedName,
endDate_equal='0000-00-00',
idCourse=activeIdCourses)
finalList = []
for course in courses:
dictCourse = {}
dictCourse['courseName'] = course.name
dictCourse['courseAbbreviation'] = course.abbreviation
dictCourse['courseCode'] = course.courseCode
dictCourse['idCourse'] = course.idCourse
if course.idCourse in firstIdCourses: #if it is an empty list no one will be at the top
finalList.insert(0, dictCourse)
dictCourse['oneOfTheFirst'] = True
else:
finalList.append(dictCourse)
dictCourse['oneOfTheFirst'] = False
return finalList
def removeCycleFromOpticalSheet(idCycle, term, idOpticalSheet):
"""
Delete the relation between a term of a cycle and an opticalSheet.
@param int idCycle :
@param float term :
@param int idOpticalSheet :
@return :
@author
"""
cursor = MySQLConnection()
cursor.execute('DELETE FROM rel_cycle_opticalSheet WHERE idCycle = ' + str(idCycle) + ' AND term = ' + str(term) + ' AND idOpticalSheet = ' + str(idOpticalSheet))
return
def create_professor_and_schedule(self):
cursor = MySQLConnection()
cursor.execute('INSERT INTO `minitableDayOfTheWeek` (dayOfTheWeek) VALUES ("Domingo"), ("Segunda"), ("Terça"), ("Quarta"), ("Quinta"), ("Sexta"), ("Sabado")')
self.schedule = Schedule('Domingo', '14:00:00', 'weekly', '12:00:00')
self.schedule.store()
self.schedule = Schedule('Segunda', '19:00:00', 'weekly', '16:00:00')
self.schedule.store()
self.schedule = Schedule('Quarta', '16:00:00', 'weekly', '14:00:00')
self.schedule.store()
self.professor = Professor('Professor Teste')
self.professor.store()
self.second_professor = Professor('Professor Teste2')
self.second_professor.store()
def removeCycleFromOpticalSheet(idCycle, term, idOpticalSheet):
"""
Delete the relation between a term of a cycle and an opticalSheet.
@param int idCycle :
@param float term :
@param int idOpticalSheet :
@return :
@author
"""
cursor = MySQLConnection()
cursor.execute('DELETE FROM rel_cycle_opticalSheet WHERE idCycle = ' +
str(idCycle) + ' AND term = ' + str(term) +
' AND idOpticalSheet = ' + str(idOpticalSheet))
return
def create_professor_and_schedule(self):
cursor = MySQLConnection()
cursor.execute(
'INSERT INTO `minitableDayOfTheWeek` (dayOfTheWeek) VALUES ("Domingo"), ("Segunda"), ("Terça"), ("Quarta"), ("Quinta"), ("Sexta"), ("Sabado")'
)
self.schedule = Schedule('Domingo', '14:00:00', 'weekly', '12:00:00')
self.schedule.store()
self.schedule = Schedule('Segunda', '19:00:00', 'weekly', '16:00:00')
self.schedule.store()
self.schedule = Schedule('Quarta', '16:00:00', 'weekly', '14:00:00')
self.schedule.store()
self.professor = Professor('Professor Teste')
self.professor.store()
self.second_professor = Professor('Professor Teste2')
self.second_professor.store()
def loadCourses(request):
data = request.GET
cursor = MySQLConnection()
coursesData = cursor.execute('SELECT course.idCourse, course.name, course.courseCode FROM rel_cycle_opticalSheet JOIN aggr_opticalSheetField on aggr_opticalSheetField.idOpticalSheet = rel_cycle_opticalSheet.idOpticalSheet JOIN aggr_offer ON aggr_offer.idOffer = aggr_opticalSheetField.idOffer JOIN course ON course.idCourse = aggr_offer.idCourse WHERE idTimePeriod = ' + str(data['idTimePeriod']) + ' AND idCycle = ' + str(data['idCycle']) + ' AND term = ' + str(data['term']) + ' GROUP BY idCourse')
courses = []
for courseData in coursesData:
course = {}
course['courseCode'] = courseData[2]
course['name'] = courseData[1]
course['idCourse'] = courseData[0]
courses.append(course)
assessmentsData = cursor.execute('SELECT aggr_survey.assessmentNumber FROM rel_cycle_opticalSheet JOIN aggr_opticalSheetField on aggr_opticalSheetField.idOpticalSheet = rel_cycle_opticalSheet.idOpticalSheet JOIN aggr_offer ON aggr_offer.idOffer = aggr_opticalSheetField.idOffer JOIN aggr_survey ON aggr_survey.idOpticalSheet = rel_cycle_opticalSheet.idOpticalSheet WHERE idTimePeriod = ' + str(data['idTimePeriod']) + ' AND idCycle = ' + str(data['idCycle']) + ' AND term = ' + str(data['term']) + ' GROUP BY aggr_survey.assessmentNumber')
assessments = [int(assessment[0]) for assessment in assessmentsData]
response = {}
response['courses'] = courses
response['assessments'] = assessments
return HttpResponse(json.dumps(response))
def __init__(self):
self.connection = MySQLConnection()
self.timeperiod = None
self.cycle = None
self.coursereader = None
self.offerreader = None
self.crawler = Crawler()
self.offerreader = OfferReader(self.connection, self.cycle,
self.timeperiod)
def getEncodings(idTimePeriod):
"""
Return a list of dicts with the keys: idOpticalSheet and encodingName. Where
each one represents a line of the encoding table, that is related with an
opticalSheet belonging to the given idTimePeriod.
@return [] :
@author
"""
cursor = MySQLConnection()
encodings = cursor.execute('SELECT encoding.idOpticalSheet, encoding.name FROM encoding JOIN aggr_opticalSheetField ON encoding.idOpticalSheet = aggr_opticalSheetField.idOpticalSheet JOIN aggr_offer ON aggr_offer.idOffer = aggr_opticalSheetField.idOffer WHERE aggr_offer.idTimePeriod = ' + str(idTimePeriod) + ' GROUP BY encoding.idOpticalSheet')
response = []
for encoding in encodings:
encodingDict = {}
encodingDict['idOpticalSheet'] = encoding[0]
encodingDict['encodingName'] = encoding[1]
response.append(encodingDict)
return response
def getOldOpticalSheets(idCycle):
"""
Returns the a dict with the idOpticalSheet, the term and the TimePeriod string of the existing
opticalSheets relatade to this cycle.
@param Cycle cycle : The cycle related to the wanted opticalSheets
@return :
@author
"""
cursor = MySQLConnection()
opticalSheets = cursor.execute('SELECT aggr_opticalSheetField.idOpticalSheet, rel_cycle_opticalSheet.term, aggr_offer.idTimePeriod FROM rel_cycle_opticalSheet JOIN aggr_opticalSheetField ON rel_cycle_opticalSheet.idOpticalSheet = aggr_opticalSheetField.idOpticalSheet JOIN aggr_offer ON aggr_offer.idOffer = aggr_opticalSheetField.idOffer WHERE rel_cycle_opticalSheet.idCycle = ' + str(idCycle) + ' GROUP BY aggr_opticalSheetField.idOpticalSheet ORDER BY aggr_offer.idTimePeriod DESC;')
response = []
for opticalSheet in opticalSheets:
opticalSheetDict = {}
opticalSheetDict['term'] = opticalSheet[1]
opticalSheetDict['timePeriod'] = str(TimePeriod.pickById(opticalSheet[2]))
opticalSheetDict['idOpticalSheet'] = opticalSheet[0]
response.append(opticalSheetDict)
return response
def findCourses(searchedAbbreviation, searchedCourseCode, searchedName, idTimePeriod, idCycle = None, term = None):
"""
returns a list of dicts containig the keys: courseName, courseAbbreviation,
courseCode and idCourse.
@param string searchedAbbreviation : Part of the wanted course's abbreviation.
@param string searchedCourseCode : Part of the wanted course's courseCode
@param string searchedName : Part of the wanted course's name.
@param int idTimePeriod : The idTimePeriod of the wanted course's timePeriod.
@param int idCycle : Is None by default, if it is set the courses related to the chosen cycle will be the firsts of the returned list.
@param int term : The term related to the idCycle, is None by default.
@return [] :
@author
"""
cursor = MySQLConnection()
activeIdCourses = cursor.execute('SELECT idCourse FROM aggr_offer WHERE idTimePeriod = ' + str(idTimePeriod) + ' GROUP BY idCourse')
activeIdCourses = [course[0] for course in activeIdCourses]
firstIdCourses = []
if idCycle != None or term != None:
query = 'SELECT idCourse FROM rel_course_cycle WHERE endDate = "0000-00-00"'
if idCycle != None:
query = query + ' AND idCycle = ' + str(idCycle)
if term != None:
query = query + ' AND term = ' + str(term)
firstIdCourses = cursor.execute(query)
firstIdCourses = [course[0] for course in firstIdCourses]
courses = Course.find(abbreviation_like = searchedAbbreviation, courseCode_like = searchedCourseCode, name_like = searchedName, endDate_equal = '0000-00-00', idCourse = activeIdCourses)
finalList = []
for course in courses:
dictCourse = {}
dictCourse['courseName'] = course.name
dictCourse['courseAbbreviation'] = course.abbreviation
dictCourse['courseCode'] = course.courseCode
dictCourse['idCourse'] = course.idCourse
if course.idCourse in firstIdCourses: #if it is an empty list no one will be at the top
finalList.insert(0,dictCourse)
dictCourse['oneOfTheFirst'] = True
else:
finalList.append(dictCourse)
dictCourse['oneOfTheFirst'] = False
return finalList
def getEncodings(idTimePeriod):
"""
Return a list of dicts with the keys: idOpticalSheet and encodingName. Where
each one represents a line of the encoding table, that is related with an
opticalSheet belonging to the given idTimePeriod.
@return [] :
@author
"""
cursor = MySQLConnection()
encodings = cursor.execute(
'SELECT encoding.idOpticalSheet, encoding.name FROM encoding JOIN aggr_opticalSheetField ON encoding.idOpticalSheet = aggr_opticalSheetField.idOpticalSheet JOIN aggr_offer ON aggr_offer.idOffer = aggr_opticalSheetField.idOffer WHERE aggr_offer.idTimePeriod = '
+ str(idTimePeriod) + ' GROUP BY encoding.idOpticalSheet')
response = []
for encoding in encodings:
encodingDict = {}
encodingDict['idOpticalSheet'] = encoding[0]
encodingDict['encodingName'] = encoding[1]
response.append(encodingDict)
return response
def create_timePeriod_and_course(self):
cursor = MySQLConnection()
length = cursor.execute(
'SELECT idLength FROM minitableLength where length="Semestral"')
if not length:
cursor.execute(
'INSERT INTO minitableLength (length) values ("Semestral")')
self.course = Course('tst9999', 'teste9999', '0000-00-00')
self.course.store()
length = cursor.execute(
'SELECT idLength FROM minitableLength where length="Semestral"')
self.timePeriod = TimePeriod(1, 2014, 1)
self.timePeriod.store()
class CycleReader(object):
"""A reader object which will use a crawler to scan through the page
of a cycle"""
def __init__(self):
self.connection = MySQLConnection()
self.timeperiod = None
self.cycle = None
self.coursereader = None
self.offerreader = None
self.crawler = Crawler()
self.offerreader = OfferReader(self.connection, self.cycle,
self.timeperiod)
def settimeperiod(self, idtimeperiod):
"Sets the timeperiod of this cycle by providing its id"
self.timeperiod = idtimeperiod
self.offerreader.timeperiod = TimePeriod.pickById(idtimeperiod)
def setcycle(self, idcycle):
"Sets the cycle of this reader by searching for the cycle in the bank"
self.cycle = Cycle.pickById(idcycle)
self.offerreader.cycle = self.cycle
def startreadingcycles(self):
"""Starts scanning through the Cycle's page and iterates through each
of it's courses. This function will not read 'Ciclo Básico' or 'Grande
Área'"""
urlstart = 'https://uspdigital.usp.br/jupiterweb/listarGradeCurricular'
codcg = self.findidfaculty()
codcurlist = self.findcodcur()
codhab = str(self.cycle.cycleCode)
for codcur in codcurlist:
parameters = {
'codcg': str(codcg),
'codcur': str(codcur),
'codhab': codhab,
'tipo': 'N'
}
completeurl = appendparameters(urlstart, parameters)
self.crawler.loadpage(completeurl)
coursecodedata = self.crawler.htmlpage.findAll('table', {})
coursecodedata = coursecodedata[1] # The table with the courses
codes = getcoursecodes(coursecodedata)
if codes:
break
return self.startreadingoffers(codes)
def findidfaculty(self):
"returns the id of the faculty that has this cycle"
relations = 'rel_courseCoordination_cycle r1, '\
'rel_courseCoordination_faculty r2 '
conditions = 'WHERE r1.idCourseCoordination = r2.idCourseCoordination'\
' AND r1.idCycle = ' + str(self.cycle.idCycle)
query = 'SELECT idFaculty FROM ' + relations + conditions
results = self.connection.execute(query)
try:
idfaculty = results[0][0]
except IndexError:
raise IndexError('Não conseguiu achar idFaculty,\
checar rel_courseCoordination_cycle e\
rel_courseCoordination_faculty com idCycle = ' +
str(self.cycle.idCycle))
sys.exit()
return idfaculty
def findcodcur(self):
"""returns the idAcademicProgram representing the code for this
object's cycle"""
query = 'SELECT idAcademicProgram FROM rel_academicProgram_cycle '\
'WHERE idCycle = ' + str(self.cycle.idCycle)
codcurall = self.connection.execute(query)
listcodcur = []
for codcur in codcurall:
listcodcur.append(codcur[0])
return listcodcur
def startreadingoffers(self, coursecodes):
"Using the coursecodes list, reads all the offers from each code"
requisitiontypetranslationdict = {0: 1, 1: 2, 2: 3}
# In the bank: 1 - Obrigatória, 2 - Eletiva, 3 - Livre
index = 0
offers = []
while index < len(coursecodes):
for period in coursecodes[index]:
for code in coursecodes[index][period]:
if code in COURSES_TO_IGNORE:
continue
reader = CourseReader(
code, self.connection, self.cycle, period,
requisitiontypetranslationdict[index])
course = reader.scancoursepage()
self.offerreader.setcourse(course)
offers.extend(self.offerreader.scancourseofferpage())
index += 1
return offers
class CycleReader(object):
"""A reader object which will use a crawler to scan through the page
of a cycle"""
def __init__(self):
self.connection = MySQLConnection()
self.timeperiod = None
self.cycle = None
self.coursereader = None
self.offerreader = None
self.crawler = Crawler()
self.offerreader = OfferReader(self.connection, self.cycle,
self.timeperiod)
def settimeperiod(self, idtimeperiod):
"Sets the timeperiod of this cycle by providing its id"
self.timeperiod = idtimeperiod
self.offerreader.timeperiod = TimePeriod.pickById(idtimeperiod)
def setcycle(self, idcycle):
"Sets the cycle of this reader by searching for the cycle in the bank"
self.cycle = Cycle.pickById(idcycle)
self.offerreader.cycle = self.cycle
def startreadingcycles(self):
"""Starts scanning through the Cycle's page and iterates through each
of it's courses. This function will not read 'Ciclo Básico' or 'Grande
Área'"""
urlstart = 'https://uspdigital.usp.br/jupiterweb/listarGradeCurricular'
codcg = self.findidfaculty()
codcurlist = self.findcodcur()
codhab = str(self.cycle.cycleCode)
for codcur in codcurlist:
parameters = {'codcg': str(codcg), 'codcur': str(codcur), 'codhab':
codhab, 'tipo': 'N'}
completeurl = appendparameters(urlstart, parameters)
self.crawler.loadpage(completeurl)
coursecodedata = self.crawler.htmlpage.findAll('table', {})
coursecodedata = coursecodedata[1] # The table with the courses
codes = getcoursecodes(coursecodedata)
if codes:
break
return self.startreadingoffers(codes)
def findidfaculty(self):
"returns the id of the faculty that has this cycle"
relations = 'rel_courseCoordination_cycle r1, '\
'rel_courseCoordination_faculty r2 '
conditions = 'WHERE r1.idCourseCoordination = r2.idCourseCoordination'\
' AND r1.idCycle = ' + str(self.cycle.idCycle)
query = 'SELECT idFaculty FROM ' + relations + conditions
results = self.connection.execute(query)
try:
idfaculty = results[0][0]
except IndexError:
raise IndexError('Não conseguiu achar idFaculty,\
checar rel_courseCoordination_cycle e\
rel_courseCoordination_faculty com idCycle = ' +
str(self.cycle.idCycle))
sys.exit()
return idfaculty
def findcodcur(self):
"""returns the idAcademicProgram representing the code for this
object's cycle"""
query = 'SELECT idAcademicProgram FROM rel_academicProgram_cycle '\
'WHERE idCycle = ' + str(self.cycle.idCycle)
codcurall = self.connection.execute(query)
listcodcur = []
for codcur in codcurall:
listcodcur.append(codcur[0])
return listcodcur
def startreadingoffers(self, coursecodes):
"Using the coursecodes list, reads all the offers from each code"
requisitiontypetranslationdict = {0: 1, 1: 2, 2: 3}
# In the bank: 1 - Obrigatória, 2 - Eletiva, 3 - Livre
index = 0
offers = []
while index < len(coursecodes):
for period in coursecodes[index]:
for code in coursecodes[index][period]:
if code in COURSES_TO_IGNORE:
continue
reader = CourseReader(code, self.connection, self.cycle,
period,
requisitiontypetranslationdict
[index])
course = reader.scancoursepage()
self.offerreader.setcourse(course)
offers.extend(self.offerreader.scancourseofferpage())
index += 1
return offers