def years_list():
#DB connection
connString = dbConnection.database_connection()
conn = pyodbc.connect(connString)
cursor = conn.cursor()
#Absolute path of report directory
abs_path = os.path.abspath(os.path.dirname(__file__))
rel_path = "QUERY_YearsList.txt"
path = os.path.join(abs_path, rel_path)
f = open(path, "r")
#Query Index is assigned based on line number of query from QUERY_YearsList.txt file:
yearsList_query_index = 8
dbQuery = f.readlines()
yearsList_query = dbQuery[yearsList_query_index]
f.close()
curYEARS = cursor.execute(yearsList_query)
yearsList = []
for row in curYEARS:
yearsList.append(row.YEARS)
return (yearsList)
def course_distribution_pie_query(yearVal):
#DB connection
connString = dbConnection.database_connection()
conn = pyodbc.connect(connString)
cursor = conn.cursor()
#Absolute path of report directory
abs_path = os.path.abspath(os.path.dirname(__file__))
rel_path = "QUERY_CourseDistributionPieChart.txt"
path = os.path.join(abs_path, rel_path)
f = open(path, "r")
#Query Index is assigned based on line number of query from QUERY_CourseDistributionPieChart.txt file:
courseWare_query_index = 8
blackboard_query_index = 12
courseWare_allYear_query_index = 16
blackboard_allYear_query_index = 20
dbQuery = f.readlines()
courseWare_query = dbQuery[courseWare_query_index]
blackboard_query = dbQuery[blackboard_query_index]
courseWare_allYear_query = dbQuery[courseWare_allYear_query_index]
blackboard_allYear_query = dbQuery[blackboard_allYear_query_index]
f.close()
courseWareCNT = blackBoardCNT = 0
if yearVal not in ['all', 'All', 'ALL']:
curCourseWare = cursor.execute(courseWare_query, (yearVal))
for row in curCourseWare:
courseWareCNT = row.cnt
curBlackBoard = cursor.execute(blackboard_query, (yearVal))
for row in curBlackBoard:
blackBoardCNT = row.cnt
else:
curCourseWare = cursor.execute(courseWare_allYear_query)
for row in curCourseWare:
courseWareCNT = row.cnt
curBlackBoard = cursor.execute(blackboard_allYear_query)
for row in curBlackBoard:
blackBoardCNT = row.cnt
return [courseWareCNT, blackBoardCNT]
def student_distribution_bar_plot_query(yearVal):
#DB connection
connString = dbConnection.database_connection()
conn = pyodbc.connect(connString)
cursor = conn.cursor()
#Absolute path of report directory
abs_path = os.path.abspath(os.path.dirname(__file__))
rel_path = "QUERY_StudentDistributionBarPlot.txt"
path = os.path.join(abs_path, rel_path)
f = open(path, "r")
#Query Index is assigned based on line number of query from QUERY_StudentDistributionPieChart.txt file:
aah_query_index = 8
cbb_query_index = 12
gar_query_index = 16
dbQuery = f.readlines()
aah_query = dbQuery[aah_query_index]
cbb_query = dbQuery[cbb_query_index]
gar_query = dbQuery[gar_query_index]
f.close()
aahCNT = cbbCNT = garCNT = 0
#Query for AAH:
curAAH = cursor.execute(aah_query, (yearVal))
for row in curAAH:
aahCNT = row.cnt
#Query for CBB:
curCBB = cursor.execute(cbb_query, (yearVal))
for row in curCBB:
cbbCNT = row.cnt
#Query for GAR:
curGAR = cursor.execute(gar_query, (yearVal))
for row in curGAR:
garCNT = row.cnt
return [aahCNT, cbbCNT, garCNT]
def exams_by_month_query(yearVal):
#DB connection
connString = dbConnection.database_connection()
conn = pyodbc.connect(connString)
cursor = conn.cursor()
#Absolute path of report directory
abs_path = os.path.abspath(os.path.dirname(__file__))
rel_path = "QUERY_ExamsByMonth.txt"
path = os.path.join(abs_path, rel_path)
f = open(path, "r")
#Query Index is assigned based on line number of query from QUERY_StudentDistributionPieChart.txt file:
aah_query_index = 8
cbb_query_index = 12
gar_query_index = 16
aah_allYear_query_index = 20
cbb_allYear_query_index = 24
gar_allYear_query_index = 28
dbQuery = f.readlines()
aah_query = dbQuery[aah_query_index]
cbb_query = dbQuery[cbb_query_index]
gar_query = dbQuery[gar_query_index]
aah_allYear_query = dbQuery[aah_allYear_query_index]
cbb_allYear_query = dbQuery[cbb_allYear_query_index]
gar_allYear_query = dbQuery[gar_allYear_query_index]
f.close()
month_List = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
month_Index_Key = 2
month_Index_Count = 3
aah_Month_CNT_Dict = dict.fromkeys(month_List, 0)
cbb_Month_CNT_Dict = dict.fromkeys(month_List, 0)
gar_Month_CNT_Dict = dict.fromkeys(month_List, 0)
if yearVal not in ['all', 'All', 'ALL']:
#Query for AAH:
curAAH = cursor.execute(aah_query, (yearVal))
for row in curAAH:
if row[month_Index_Key] in aah_Month_CNT_Dict:
aah_Month_CNT_Dict[
row[month_Index_Key]] = row[month_Index_Count]
#Query for CBB:
curCBB = cursor.execute(cbb_query, (yearVal))
for row in curCBB:
if row[month_Index_Key] in cbb_Month_CNT_Dict:
cbb_Month_CNT_Dict[
row[month_Index_Key]] = row[month_Index_Count]
#Query for GAR:
curGAR = cursor.execute(gar_query, (yearVal))
for row in curGAR:
if row[month_Index_Key] in gar_Month_CNT_Dict:
gar_Month_CNT_Dict[
row[month_Index_Key]] = row[month_Index_Count]
else:
#Query for AAH:
curAAH = cursor.execute(aah_allYear_query)
for row in curAAH:
if row[month_Index_Key] in aah_Month_CNT_Dict:
aah_Month_CNT_Dict[
row[month_Index_Key]] = row[month_Index_Count]
#Query for CBB:
curCBB = cursor.execute(cbb_allYear_query)
for row in curCBB:
if row[month_Index_Key] in cbb_Month_CNT_Dict:
cbb_Month_CNT_Dict[
row[month_Index_Key]] = row[month_Index_Count]
#Query for GAR:
curGAR = cursor.execute(gar_allYear_query)
for row in curGAR:
if row[month_Index_Key] in gar_Month_CNT_Dict:
gar_Month_CNT_Dict[
row[month_Index_Key]] = row[month_Index_Count]
return aah_Month_CNT_Dict, cbb_Month_CNT_Dict, gar_Month_CNT_Dict