def getSubjectNames_indi(data):
db, cursor = connection()
sql = "SELECT sub_master.subjectName from faculty_master INNER JOIN sub_master on faculty_master.sub_id = sub_master.sub_id WHERE faculty_master.faculty_code = %s"
cursor.execute(sql, data)
resultSet = cursor.fetchall()
faculty_name = []
for result in resultSet:
faculty_name.append(result[0])
cursor.close()
#print(faculty_name)
return jsonify(faculty_name)
def getFacultyNames(subject):
sql = "SELECT DISTINCT Faculty_Name from response_master where Semester = %s and Dept_Name = %s and Subject_Name = %s"
subject = json.loads(subject)
values = (subject[0], subject[1], subject[2])
db, cursor = connection()
cursor.execute(sql, values)
resultSet = cursor.fetchall()
faculty = []
for result in resultSet:
faculty.append(result[0])
cursor.close()
return jsonify(faculty)
def addEntry(req, filename):
db, cursor = connection()
sql = "INSERT INTO response_master(Pdf_Id, Institute_Name, Dept_Name, Program, Academy_Year, Semester, Subject_Code, Subject_Name, Faculty_Name, q1, q2, q3, q4, q5, q6, q7,q8, q9, q10, q11, q12, q13, q14, q15, q16, q17, q18, q19, q20, q21, q22, q23, q24, q25, q26, q27, q28, q29, q30, q31, q32, q33, q34, q35, q36, q37, q38, q39, q40, q41, q42, q43, q44, q45, q46, q47, q48, q49, q50, q51,q52, q53, q54, q55, q56, q57, q58, q59, q60, Total_stu, Total_feed) VALUES(NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
values = (req['inputInstitute'], req['inputDepartment'],
req['inputProgram'], req['inputAcademicYear'],
req['inputSemester'], req['inputSubjectId'],
req['inputSubjectName'], req['inputFacultyName'])
df = extractTable('uploads/', filename)
tp = list(df.itertuples(index=False, name=None))[0]
values = values + tp
values = values + (req['inputTotalStudents'], req['inputTotalFeedback'])
cursor.execute(sql, values)
db.commit()
cursor.close()
def getSubjectNames(sem):
sql = "SELECT DISTINCT Subject_Name from response_master where Semester = %s and Dept_Name = %s"
sem = sem.split(',')
if sem[0] == '' or sem[1] == '':
return jsonify([])
values = (sem[0], sem[1])
db, cursor = connection()
cursor.execute(sql, values)
resultSet = cursor.fetchall()
semester = []
for result in resultSet:
semester.append(result[0])
cursor.close()
return jsonify(semester)
def showPieChart(req):
#print('req : ', req)
percent = "SELECT Faculty_Name, Total_feed/Total_stu*100 as PER from response_master where Semester = %s and Dept_Name = %s and Subject_Name = %s ORDER by PER DESC"
req = json.loads(req)
values = (req[0], req[1], req[2])
#print(values)
db, cursor = connection()
cursor.execute(percent, values)
resultSet = cursor.fetchall()
f_name = []
per = []
for result in resultSet:
f_name.append(result[0])
per.append(int(result[1]))
data = {'FacultyName': f_name, 'Overall Feedback (%)': per}
#print('query : ', data)
cursor.close()
return data
def showBarChart(req):
#print(req)
sql = "SELECT Faculty_Name, (q1+q6+q11+q16+q21+q26+q31+q36+q41+q46+q51+q56)/12 as A, (q2+q7+q12+q17+q22+q27+q32+q37+q42+q47+q52+q57)/12 as B, (q3+q8+q13+q18+q23+q28+q33+q38+q43+q48+q53+q58)/12 as C, (q4+q9+q14+q19+q24+q29+q34+q39+q44+q49+q54+q59)/12 as D, (q5+q10+q15+q20+q25+q30+q35+q40+q45+q50+q55+q60)/12 as E from response_master WHERE Semester = %s and Dept_Name = %s and Subject_Name = %s ORDER BY A DESC"
req = json.loads(req)
values = (req[0], req[1], req[2])
db, cursor = connection()
cursor.execute(sql, values)
rv = cursor.fetchall()
fname = []
a = []
b = []
c = []
d = []
e = []
for result in rv:
fname.append(result[0])
a.append(int(result[1]))
b.append(int(result[2]))
c.append(int(result[3]))
d.append(int(result[4]))
e.append(int(result[5]))
payload = []
content = {}
for result in rv:
content = {'id': result[0]}
payload.append(content)
content = {}
data = {
"FacultyName": fname,
"Excellent": a,
"Good": b,
"Average": c,
"Bad": d,
"VeryBad": e
}
#print('data ', data)
cursor.close()
return data
def showBarChart_indi(req):
db, cursor = connection()
sql = "SELECT `q1`, `q2`, `q3`, `q4`, `q5`, `q6`, `q7`, `q8`, `q9`, `q10`, `q11`, `q12`, `q13`, `q14`, `q15`, `q16`, `q17`, `q18`, `q19`, `q20`, `q21`, `q22`, `q23`, `q24`, `q25`, `q26`, `q27`, `q28`, `q29`, `q30`, `q31`, `q32`, `q33`, `q34`, `q35`, `q36`, `q37`, `q38`, `q39`, `q40`, `q41`, `q42`, `q43`, `q44`, `q45`, `q46`, `q47`, `q48`, `q49`, `q50`, `q51`, `q52`, `q53`, `q54`, `q55`, `q56`, `q57`, `q58`, `q59`, `q60` from response_master WHERE response_master.Faculty_Name = (SELECT faculty_master.faculty_name from faculty_master WHERE faculty_master.faculty_code = %s ) AND response_master.Subject_Name = %s"
req = json.loads(req)
values = (req[0], req[1])
cursor.execute(sql, values)
resultSet = cursor.fetchall()
print('resultset:', resultSet)
questions = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
a = []
b = []
c = []
d = []
e = []
for result in resultSet:
for i in range(0, 60, 5):
a.append(int(result[i]))
for i in range(1, 60, 5):
b.append(int(result[i]))
for i in range(2, 60, 5):
c.append(int(result[i]))
for i in range(3, 60, 5):
d.append(int(result[i]))
for i in range(4, 60, 5):
e.append(int(result[i]))
data = {
"Question No": questions,
"Excellent": a,
"Good": b,
"Average": c,
"Bad": d,
"VeryBad": e
}
print(data)
cursor.close()
return data