def studentYearWiseQualitricsData(UID, academicYear):
# Change the Query
# print(UID)
# print(academicYear)
try:
sqlStudentQualitricsData = " Select Response_ID, End_Date, Concat(Start_Date, ' - ', End_Date), \
Case When role_Description = 'I am an instructor in Indiana University’s Advance College Project (ACP).' Then 'IU' \
ELSE Concat(Institution,' - ', High_School) \
END AS 'DC Partner', MoreThan_TwoYears_TeachingExperience, \
StateLicensure, Class_Taken_IndianaUniversity, FullLegalName \
From iuie_qualitrics_data qualitricsData \
Left Join iuie_academic_terms acadterm \
on qualitricsData.Acadmic_Term = acadterm.acad_term \
Where Student_UID = Trim(leading '0' from '" + UID + "') ANd acadterm.acad_term_cd = '"+ academicYear +"' \
Order by 1 "
#print(sqlStudentQualitricsData)
dataStudentQualitricsData = dataAdapter.execute(sqlStudentQualitricsData)
#StudentQualitricsYearDataList = dataAdapter.ConvertToList(dataStudentQualitricsData, 0)
return dataStudentQualitricsData
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(sys.exc_info())
logging.write_log(error)
def studentsList(userName, UID, firstName, lastName, campusOfEnrollment, dcPartner, currentlyEnrolled):
try:
sqlStudentQuery = " SELECT Distinct Trim(leading '0' from applications.PRSN_UNIV_ID), Concat(PRSN_PREF_1ST_NM,' ', PRSN_PREF_MID_NM, ' ', PRSN_PREF_LAST_NM) 'Student Name', \
qualitricsData.Institution, qualitricsData.Spring_CourseFirstRequest, qualitricsData.Spring_CourseSecondRequest, \
CREDITS_COMPLETED, PRSN_GDS_CMP_EMAIL_ADDR, INST_CD \
from iuie_applications applications \
Left Join iuie_qualitrics_data qualitricsData \
on Trim(leading '0' from applications.PRSN_UNIV_ID) = qualitricsData.Student_UID \
Left join iuie_enrollments enrollments \
on Trim(leading '0' from applications.PRSN_UNIV_ID) = enrollments.PRSN_UNIV_ID \
Where 1 = 1 "
if userName:
sqlStudentQuery = sqlStudentQuery + " AND Lower(applications.PRSN_NTWRK_ID) Like Lower('%" + userName + "%') "
if UID:
sqlStudentQuery = sqlStudentQuery + " AND Lower(applications.PRSN_UNIV_ID) Like ('%" + UID + "%')"
if firstName:
sqlStudentQuery = sqlStudentQuery + " AND ( Lower(applications.PRSN_PREF_1st_NM) Like Lower('%" + firstName + "%') OR Lower(applications.PRSN_PREF_MID_NM) Like Lower('%" + firstName + "%') ) "
if lastName:
sqlStudentQuery = sqlStudentQuery + " AND Lower(applications.PRSN_PREF_LAST_NM) Like Lower('%" + lastName + "%')"
if campusOfEnrollment:
sqlStudentQuery = sqlStudentQuery + " AND Lower(applications.INST_CD) = Lower('" + campusOfEnrollment + "')"
if dcPartner:
sqlStudentQuery = sqlStudentQuery + " AND Lower(qualitricsData.Institution) = Lower('" + dcPartner + "')"
if (currentlyEnrolled == "true"):
sqlStudentQuery = sqlStudentQuery + " AND enrollments.STU_ENRL_STAT_REAS_CD = 'ENRL' "
sqlStudentQuery = sqlStudentQuery + " Order by 1 "
## Important
# if studentType:
# sqlCoursesQuery = sqlCoursesQuery + " AND Lower(applications.INST_CD) = Lower('" + studentType + "')"
# if pendingEnrollmentRequests:
# sqlCoursesQuery = sqlCoursesQuery + " AND applications.ACAD_TERM_CD = '" + pendingEnrollmentRequests + "'"
# print(sqlStudentQuery)
data = dataAdapter.execute(sqlStudentQuery)
# print(data)
return data
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(sys.exc_info())
logging.write_log(error)
def campusList():
try:
sqlCampusList = "SELECT Distinct applications.INST_CD FROM iuie_applications applications Order By 1 "
data = dataAdapter.execute(sqlCampusList)
CampusList = dataAdapter.ConvertToList(data, 0)
return CampusList
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(sys.exc_info())
logging.write_log(error)
def dcPartnerList():
try:
sqldcPartnerList = "SELECT Distinct Institution FROM iuie_qualitrics_data Where Institution <> '' Order By 1 "
data = dataAdapter.execute(sqldcPartnerList)
dcPartnerList = dataAdapter.ConvertToList(data, 0)
return dcPartnerList
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(sys.exc_info())
logging.write_log(error)
def campusOfEnrollmentList():
try:
sqlCampusList = "SELECT Distinct classes.COE_INST_CD FROM iuie_classes classes Order By 1 "
data = dataAdapter.execute(sqlCampusList)
CampusList = dataAdapter.ConvertToList(data, 0)
return CampusList
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def termList():
try:
sqlTermList = "SELECT Distinct acadterm.ACAD_TERM_CD, acadterm.acad_term \
From iuie_academic_terms acadterm \
Order By 1"
data = dataAdapter.execute(sqlTermList)
termList = dataAdapter.ConvertToList(data, 1)
return termList
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def fundingList():
try:
sqlFundingList = "SELECT DISTINCT fundingType \
FROM iuie_funding_type Order By 1"
data = dataAdapter.execute(sqlFundingList)
fundingLst = dataAdapter.ConvertToList(data, 0)
return fundingLst
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def courseList():
try:
sqlCourseList = "SELECT DISTINCT enrollments.CRS_SUBJ_CD, CONCAT(enrollments.CRS_SUBJ_CD,':', enrollments.CRS_CATLG_NBR) \
FROM iuie_enrollments enrollments \
Order By 1"
data = dataAdapter.execute(sqlCourseList)
crsList = dataAdapter.ConvertToList(data, 1)
return crsList
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def GetAcadTermCode(term):
try:
sqlAcadTermCd = "SELECT acad_term_cd \
FROM iuie_academic_terms acadterm \
Where 1 = 1 "
if term:
sqlAcadTermCd = sqlAcadTermCd + " AND acadterm.acad_term Like ('%" + term + "%')"
data = dataAdapter.execute(sqlAcadTermCd)
academicTermCode = dataAdapter.ConvertToList(data, 0)
return academicTermCode[0]
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def enrollmentList(term, course, funding):
try:
if course:
crsSubjCd = (course.split(':')[0]).strip()
CrsCatlgNbr = (course.split(':')[1]).strip()
sqlEnrollmentQuery = "SELECT acadterm.acad_term, CONCAT(enrollments.CRS_SUBJ_CD, ':' ,enrollments.CRS_CATLG_NBR), \
date_format(enrollments.STu_ENRL_ADD_DT, '%M %D %Y'), applications.PRSN_NTWRK_ID, \
enrollments.ACAD_TERM_CD, funding_type.fundingtype \
from iuie_enrollments enrollments \
Left Join iuie_academic_terms acadterm \
on enrollments.ACAD_TERM_CD = acadterm.acad_term_cd \
Left Join iuie_applications applications \
on enrollments.PRSN_UNIV_ID = Trim(leading '0' from applications.PRSN_UNIV_ID) \
Left Join iuie_grant_enrollment grantEnrollment \
on enrollments.PRSN_UNIV_ID = grantEnrollment.PRSN_UNIV_ID \
Left Join iuie_funding_type funding_type \
on grantEnrollment.grant_id = funding_type.id \
Where 1 = 1 "
if term:
sqlEnrollmentQuery = sqlEnrollmentQuery + " AND enrollments.ACAD_TERM_CD = " + term
if course:
sqlEnrollmentQuery = sqlEnrollmentQuery + " AND enrollments.CRS_SUBJ_CD = '" + crsSubjCd + "' AND enrollments.CRS_CATLG_NBR = '" + CrsCatlgNbr + "'"
if funding:
sqlEnrollmentQuery = sqlEnrollmentQuery + " AND grantEnrollment.grant_id = " + str(
funding)
sqlEnrollmentQuery = sqlEnrollmentQuery + " Order by 5 ASC"
#print(sqlEnrollmentQuery)
data = dataAdapter.execute(sqlEnrollmentQuery)
return data
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def GetFundingId(fundingType):
try:
sqlFundingId = "SELECT id \
FROM iuie_funding_type \
Where 1 = 1 "
sqlFundingId = sqlFundingId + " AND fundingType = '" + fundingType + "'"
#print(sqlFundingId)
data = dataAdapter.execute(sqlFundingId)
fundingId = dataAdapter.ConvertToList(data, 0)
return fundingId[0]
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def coursesList(coursenumber, courseName, campusOfInstruction, acadTerm):
try:
sqlCoursesQuery = "SELECT classdata.CLS_KEY, classdata.CRS_ID, enrollments.CRS_DESC, acadterm.acad_term, classdata.COI_INST_CD, \
classdata.CLS_INSTR_NM, classdata.ENROLLMENT_CAP, classdata.ENROLLMENT_TOTAL, \
classdata.ENROLLMENT_CAP - classdata.ENROLLMENT_TOTAL Calculated_Remaining \
FROM iuie_classes classdata \
Left Join iuie_academic_terms acadterm \
on classdata.ACAD_TERM_CD = acadterm.acad_term_cd \
Left join iuie_enrollments enrollments \
on classdata.CRS_ID = enrollments.CRS_ID Where 1 = 1 "
if coursenumber:
sqlCoursesQuery = sqlCoursesQuery + " AND classdata.CRS_ID Like '%" + coursenumber + "%'"
if courseName:
sqlCoursesQuery = sqlCoursesQuery + " AND Lower(enrollments.CRS_DESC) Like Lower('%" + courseName + "%')"
if campusOfInstruction:
sqlCoursesQuery = sqlCoursesQuery + " AND Lower(classdata.COI_INST_CD) = Lower('" + campusOfInstruction + "')"
if acadTerm:
sqlCoursesQuery = sqlCoursesQuery + " AND classdata.ACAD_TERM_CD = '" + acadTerm + "'"
sqlCoursesQuery = sqlCoursesQuery + " Order by 2 "
#print(sqlCoursesQuery)
data = dataAdapter.execute(sqlCoursesQuery)
#print(data)
return data
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)
def studentDetailView(UID):
try:
# Student Details
sqlStudentDetails = " SELECT DISTINCT Concat(PRSN_PREF_1ST_NM,' ', PRSN_PREF_MID_NM, ' ', PRSN_PREF_LAST_NM) 'Student Name', \
PRSN_NTWRK_ID, Trim(leading '0' from applications.PRSN_UNIV_ID), \
DATE_FORMAT(PRSN_BIRTH_DT,'%b %d %Y') 'Birth Date', \
qualitricsData.legal_sex, qualitricsData.OPTIONAL_GenderIdentity, qualitricsData.Primary_PhoneNumber, qualitricsData.Contact_Method, qualitricsData.Student_PrimaryEmailAddress, \
Concat(CurrentAddress_Street, ' ', CurrentAddress_City, ' ', CurrentAddress_State, ' ', CurrentAddress_PostalCode , ' ', CurrentAddress_Country , ' '), \
Concat(PermanentMailingAddress_Street, ' ', PermanentMailingAddress_City, ' ', PermanentMailingAddress_State, ' ', PermanentMailingAddress_PostalCode , ' ', PermanentMailingAddress_Country , ' '), \
qualitricsData.US_Status, Documents_Arrived_UnderAnyOtherName \
FROM iuie_applications applications \
Left Join iuie_qualitrics_data qualitricsData \
on Trim(leading '0' from applications.PRSN_UNIV_ID) = qualitricsData.Student_UID \
Where Trim(leading '0' from applications.PRSN_UNIV_ID) = '" + UID + "' LIMIT 1 "
dataStudentDetails = dataAdapter.executeOne(sqlStudentDetails)
# print(sqlStudentDetails)
# Former Names
sqlStudentFormerNames = " SELECT FormerNames1 'FormerName' \
from iuie_qualitrics_data qualitricsData \
where qualitricsData.Student_UID = Trim(leading '0' from '" + UID + "') \
union \
SELECT FormerNames2 \
from iuie_qualitrics_data qualitricsData \
where qualitricsData.Student_UID = Trim(leading '0' from '" + UID + "') \
union \
SELECT FormerNames3 \
from iuie_qualitrics_data qualitricsData \
where qualitricsData.Student_UID = Trim(leading '0' from '" + UID + "') \
union \
SELECT FormerNames4 \
from iuie_qualitrics_data qualitricsData \
where qualitricsData.Student_UID = Trim(leading '0' from '" + UID + "') \
union \
SELECT FormerNames5 \
from iuie_qualitrics_data qualitricsData \
where qualitricsData.Student_UID = Trim(leading '0' from '" + UID + "') \
Order By 1 desc "
dataStudentFormerNames = dataAdapter.execute(sqlStudentFormerNames)
ListStudentFormerNames = []
for formerNames in dataStudentFormerNames:
for formerName in formerNames:
ListStudentFormerNames.append(formerName)
#print(sqlStudentFormerNames)
# Qualitrics Tabs
sqlStudentQualitricsTabsHeader = "Select Distinct acadterm.acad_term_cd, acadterm.acad_term \
from iuie_qualitrics_data qualitricsData \
Inner Join iuie_academic_terms acadterm \
on qualitricsData.Acadmic_Term = acadterm.acad_term \
Where qualitricsData.Student_UID = Trim(leading '0' from '" + UID + "') Order By 1 "
# print(sqlStudentQualitricsTabsHeader)
dataStudentQualitricsTabsHeader = dataAdapter.execute(sqlStudentQualitricsTabsHeader)
# print(sqlStudentQualitricsTabsHeader)
# print(dataStudentQualitricsTabsHeader)
# Qualitric Data
# sqlStudentQualitricsTabsData = " "
# dataStudentQualitricsTabsData = dataAdapter.execute(sqlStudentQualitricsTabsData)
# Enrollment History Data
sqlStudentEnrollmentHistory = "Select Distinct enrollments.CRS_DESC, enrollments.CLS_NBR, acadterm.acad_term, enrollments.STU_ENRL_STAT_REAS_CD, \
enrollments.CRS_OFCL_GRD_CD, applications.CREDITS_COMPLETED \
from iuie_enrollments enrollments \
Left Join iuie_classes classdata \
on classdata.CRS_ID = enrollments.CRS_ID AND classdata.ACAD_TERM_CD = enrollments.ACAD_TERM_CD \
Left Join iuie_applications applications \
on enrollments.PRSN_UNIV_ID = Trim(leading '0' from applications.PRSN_UNIV_ID) \
Left Join iuie_academic_terms acadterm \
on enrollments.ACAD_TERM_CD = acadterm.acad_term_cd \
Where enrollments.PRSN_UNIV_ID = Trim(leading '0' from '" + UID + "') \
Order By 2,3 "
dataStudentEnrollmentHistory = dataAdapter.execute(sqlStudentEnrollmentHistory)
# print(sqlStudentEnrollmentHistory)
# Course Request
sqlStudentCourseRequest = "Select enrollments.STU_ENRL_ADD_DT, enrollments.CRS_ID, enrollments.CLS_NBR, enrollments.STU_ENRL_DRP_DT \
from iuie_enrollments enrollments \
Where enrollments.PRSN_UNIV_ID = Trim(leading '0' from '" + UID + "')"
dataStudentCourseRequest = dataAdapter.execute(sqlStudentCourseRequest)
# Comments
# sqlStudentCommentsData = " "
# dataStudentCommentsData = dataAdapter.execute(sqlStudentCommentsData)
return dataStudentDetails, ListStudentFormerNames, dataStudentQualitricsTabsHeader, dataStudentEnrollmentHistory, dataStudentCourseRequest
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(sys.exc_info())
logging.write_log(error)
def courseDetailView(courseID, term, CampusOfInstruction):
try:
sqlCourseDetailViewHeader1 = "SELECT classdata.CRS_ID, enrollments.CRS_DESC, \
acadterm.acad_term, classdata.COE_INST_CD, classdata.COI_INST_CD \
from iuie_classes classdata \
Left Join iuie_academic_terms acadterm \
on classdata.ACAD_TERM_CD = acadterm.acad_term_cd \
Left join iuie_enrollments enrollments \
on classdata.CRS_ID = enrollments.CRS_ID AND classdata.ACAD_TERM_CD = enrollments.ACAD_TERM_CD \
WHERE 1= 1 "
sqlCourseDetailViewHeader1 = sqlCourseDetailViewHeader1 + " AND classdata.CRS_ID = " + courseID + " AND classdata.ACAD_TERM_CD = " + term + " AND classdata.COI_INST_CD = '" + CampusOfInstruction + "'"
sqlCourseDetailViewHeader1 = sqlCourseDetailViewHeader1 + " Order By 1 "
dataHeader1 = dataAdapter.executeOne(sqlCourseDetailViewHeader1)
# print(sqlCourseDetailViewHeader1)
# print(dataHeader1)
sqlCourseDetailViewHeader2 = "Select CRS_ID, ACAD_TERM_CD, COUNT(IF(enrollments.STU_ENRL_STAT_REAS_CD='ENRL',0, NULL)) 'ENRLLOED', \
COUNT(IF(enrollments.STU_ENRL_STAT_REAS_CD='DROP',0, NULL)) 'DROP', \
COUNT(IF(enrollments.STU_ENRL_STAT_REAS_CD='WDRW',0, NULL)) 'WITHDRAWAL' \
from iuie_enrollments enrollments \
WHERE enrollments.CRS_ID = " + courseID + " AND enrollments.ACAD_TERM_CD = '" + term + "'"
dataHeader2 = dataAdapter.executeOne(sqlCourseDetailViewHeader2)
# print(sqlCourseDetailViewHeader2)
# print(dataHeader2)
sqlCourseDetailViewCampusSeats = " SELECT classdata.COI_INST_CD, classdata.COE_INST_CD, classdata.ENROLLMENT_CAP, \
classdata.ENROLLMENT_TOTAL, classdata.ENROLLMENT_CAP - classdata.ENROLLMENT_TOTAL 'Calculated Remaining' \
FROM iuie_classes classdata "
sqlCourseDetailViewCampusSeats = sqlCourseDetailViewCampusSeats + " WHERE classdata.CRS_ID = " + courseID + " AND classdata.ACAD_TERM_CD = '" + term + "' AND classdata.COI_INST_CD = '" + CampusOfInstruction + "'"
sqlCourseDetailViewCampusSeats = sqlCourseDetailViewCampusSeats + " Order By 2 "
dataCampusSeats = dataAdapter.execute(sqlCourseDetailViewCampusSeats)
# print(sqlCourseDetailViewCampusSeats)
# print(dataSectionsSeats)
sqlCourseDetailViewStudentDetails = " SELECT CONCAT(applications.PRSN_PREF_1st_NM, ' ', applications.PRSN_PREF_MID_NM, ' ',applications.PRSN_PREF_LAST_NM) 'Student Name', \
enrollments.CLS_NBR, enrollments.STU_ENRL_STAT_REAS_CD \
from iuie_classes classdata \
left join iuie_enrollments enrollments \
on classdata.CRS_ID = enrollments.CRS_ID AND classdata.ACAD_TERM_CD = enrollments.ACAD_TERM_CD \
left join iuie_applications applications \
on enrollments.PRSN_UNIV_ID = Trim(leading '0' from applications.PRSN_UNIV_ID) "
sqlCourseDetailViewStudentDetails = sqlCourseDetailViewStudentDetails + " WHERE classdata.CRS_ID = " + courseID + " AND classdata.ACAD_TERM_CD = '" + term + "' AND classdata.COI_INST_CD = '" + CampusOfInstruction + "'"
sqlCourseDetailViewStudentDetails = sqlCourseDetailViewStudentDetails + " group by CONCAT(applications.PRSN_PREF_1st_NM, ' ', applications.PRSN_PREF_MID_NM, ' ',applications.PRSN_PREF_LAST_NM), enrollments.CLS_NBR, enrollments.STU_ENRL_STAT_REAS_CD \
order By 1 "
dataStudentDetails = dataAdapter.execute(
sqlCourseDetailViewStudentDetails)
# print(sqlCourseDetailViewStudentDetails)
# print("--------------------------------------------------------------------------------------------------------------------------")
# print(dataStudentDetails)
return dataHeader1, dataHeader2, dataCampusSeats, dataStudentDetails
except:
error = "Date: " + str(datetime.now()) + " Error: " + str(
sys.exc_info())
logging.write_log(error)