def upload_marks(faculty): try: subject_list = faculty_subjects(faculty,True) if len(subject_list)==0: print("There are no subjects allocated to you!") return con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) while 1: print("Note: Enter %d to exit"%(len(subject_list)+1)) sub_ctr = input("Please enter the serial# of the subject to upload marks: ") if sub_ctr.isdigit()==False or int(sub_ctr) <= 0 or int(sub_ctr) > len(subject_list)+1: print("Wrong Choice!") elif int(sub_ctr)==len(subject_list)+1: return else: break upload_marks_subject(subject_list[int(sub_ctr)-1]) return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def check_user_as_faculty(id_i): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) emp_id = check_user_as_employee(id_i) if len(emp_id)!=1: print("Not Employee!") return [] sql = "SELECT faculty_id, designation, salary from faculty \ where employee_id=%d"%(emp_id[0]) cur.execute(sql) for row in cur: return emp_id+row return [] except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def upload_marks_subject(faculty_subject): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) ret=display_enrolled_students(faculty_subject) if ret < 1: return year = 2018 print("Note: To upload marks *.csv file should be written in following format") print("Student_Roll_No,Internal_Marks,External_Marks,Credit\n") print("Please enter the absolute/relative path name of the *.csv file: ") path = input("Path: ") try: f1 = open(path,'r') student_list = [] marks = f1.readline() while marks: marks = marks.strip().split(',') for i in range(len(marks)): marks[i] = marks[i].strip() student_list.append(marks[0]) sql = "INSERT INTO stu_subject_marks (subject_code, semester, year,\ student_roll_no, max_internal_marks, internal_marks, max_external_marks,\ external_marks, max_credits, credits) VALUES ('%s',%d,%d,%s,%d,%s,%d,%s,%f,%s)"%(faculty_subject.subject_code, faculty_subject.semester,year,marks[0],faculty_subject.internal_marks,marks[1],faculty_subject.external_marks, marks[2],faculty_subject.credits,marks[3]) cur.execute(sql) marks = f1.readline() con.commit() for std_roll_no in student_list: update_semester_status(std_roll_no,faculty_subject.semester) except Exception as e: print(e) return con.commit() return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def display_enrolled_students(faculty_subject): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "SELECT A.std_roll_no, CONCAT(D.first_name,' ',D.last_name) as Name FROM students A\ JOIN user_details D ON D.parent_user_id = A.parent_user_id\ JOIN branch_subjects B ON B.branch_code = A.std_branch and B.semester=A.std_semester\ JOIN faculty_subjects C ON C.branch_subject_id = B.id\ WHERE C.id=%d"%(faculty_subject.faculty_subject_id) cur.execute(sql) if cur.rowcount==0: print("No students enrolled for this subject") return -5 print("\nPlease find below the list of students enrolled for this subject:") print("%-11s|%s"%('Roll.No.','Name')) for row in cur: print("%-11s|%s"%(row[0],row[1])) print('\n') return 1 except pymysql.OperationalError as e: print(e) return -1 except Exception as e: print(e) return -1 finally: cur.close() con.close()
def check_user_as_student(id_i): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "SELECT A.std_roll_no, A.std_admission_year, A.std_semester, \ A.std_branch, B.branch_name FROM students A\ JOIN branch B ON B.branch_code=A.std_branch\ WHERE parent_user_id=%d"%(id_i) cur.execute(sql) for row in cur: return row return [] except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def check_attendance(student): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) print("Name: %-25s | Roll No: %-10d | Branch: %-4s | Semester: %-2d"% (student.user_details.first_name, student.std_roll_no, student.std_branch, student.semester)) sem = student.semester while 1: sem = int(input('Enter the semester for which you want attendance: ')) if sem > student.semester or sem <= 0: print("Enter the correct Value") else: break sql1 = "SELECT C.id, D.subject_code, D.subject FROM students A \ JOIN branch B on A.std_branch = B.branch_code \ JOIN branch_subjects C on A.std_branch = C.branch_code \ JOIN subjects D on C.subject_code = D.subject_code \ where std_roll_no=%s and C.semester=%d"%(student.std_roll_no,int(sem)) cur.execute(sql1) cur2 = dBconnectivity.create_cursor(con) print("%-13s| %-45s|%11s|%7s"%('Subject_Code','Subject_Name','Net_Classes','Present')) for row in cur: sql = "select count(*) from class_attendance_header where class_id=%d"%(row[0]) cur2.execute(sql) tot_classes = 0 for row2 in cur2: tot_classes = row2[0] sql = "select count(*) from class_attendance_header A \ JOIN class_attendance_details B on A.id = B.class_id \ where A.class_id=%d and B.student_roll_no=%d"%(row[0],student.std_roll_no) cur2.execute(sql) present = 0 for row2 in cur2: present = row2[0] print("%-13s| %-45s|%11d|%7d"%(row[1],row[2],tot_classes,present)) cur2.close() except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def check_results(student): ch = input("Enter the semester to view the result: ") if ch.isdigit()==False or int(ch)<0 or int(ch)>student.semester: print("Wrong Semster!") return try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) print("\n\n\nName: %s %s"%(student.user_details.first_name, student.user_details.last_name)) print("Roll. No.: %d"%(student.std_roll_no)) print("Course: B.Tech. %s\n"%(student.branch_name)) print("Semester %s Details"%(ch)) print("%-10s|%-30s|%-18s|%-18s|%-14s|%-14s|%-6s"%('Paper Code','Paper Name','Max Internal Marks', 'Max External Marks','Internal Marks','External Marks','Credits')) sql = "SELECT B.subject_code, B.subject, A.max_internal_marks,\ A.max_external_marks, A.internal_marks, A.external_marks, A.credits\ FROM stu_subject_marks A\ JOIN subjects B ON B.subject_code = A.subject_code\ where A.student_roll_no=%s and A.semester=%d"%(student.std_roll_no,int(ch)) cur.execute(sql) ctr = 0 for row in cur: print("%-10s|%-30s|%18s|%18s|%14s|%14s|%6s"%(row[0],row[1],row[2],row[3],row[4],row[5],row[6])) ctr += 1 sql = "SELECT sem_%d_status FROM stu_semwise_status\ WHERE std_roll_no=%s"%(int(ch),student.std_roll_no) cur.execute(sql) for row in cur: status = row[0] if status not in ('PASS','FAIL') and ctr!=0: status = 'IN PROGRESS' print("\nStatus: %s\n\n"%(status)) return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def upload_attendance_subject(faculty_subject): try: ret=display_enrolled_students(faculty_subject) if ret < 1: return con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) datetime = input("Please enter datetime of the lecture (YYYY-MM-DD HH:MM:SS): ") sql = "INSERT INTO class_attendance_header (faculty_subject_id, \ class_id, class_startdatetime, class_total_strength) VALUES \ (%d,%d,'%s',%d)"%(faculty_subject.faculty_subject_id, faculty_subject.branch_subject_id, datetime,120) #print(sql) cur.execute(sql) #print("Executed") sql = "SELECT id FROM class_attendance_header \ WHERE faculty_subject_id=%d and class_id=%d and \ class_startdatetime='%s'"%(faculty_subject.faculty_subject_id, faculty_subject.branch_subject_id, datetime) #print(sql) cur.execute(sql) for row in cur: class_id = row[0] print("Please enter below the roll# of the students who attended the class separated by ,:") roll_list = input().strip().split(',') roll_list = map(int, roll_list) #Include check to ensure, that attendance should be given to the student belonging to the same class only for roll_no in roll_list: sql = "INSERT INTO class_attendance_details VALUES (%d,%d)"%(class_id, roll_no) cur.execute(sql) con.commit() return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def student_signup_form(user): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "SELECT branch_code, branch_name FROM branch" cur.execute(sql) branch_dict = {} for row in cur: branch_dict[row[0]]=row[1] print('Available Branches in our college: ') for key in branch_dict: print("%-6s:%s"%(key,branch_dict[key])) while 1: branch = input("Enter branch code from above list (xxx to exit): ") if branch=='xxx': return elif branch_dict.get(branch)==None: print("Wrong Choice!") else: break sql = "SELECT max(std_roll_no) FROM students" cur.execute(sql) for row in cur: pass max_id = int(row[0])+1 sql = "INSERT INTO students (parent_user_id, std_roll_no,\ std_admission_year, std_semester, std_branch) VALUES \ (%d,%d,%d,%d,'%s')"%(user.id,max_id,datetime.datetime.now().year,1,branch) cur.execute(sql) con.commit() except pymysql.OperationalError as e: print(e) return -1 except Exception as e: print(e) return -1 finally: cur.close() con.close()
def faculty_subjects(faculty,display_marks=False): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "SELECT A.id,C.id,B.Subject_code,B.Subject,C.branch_code,C.semester,\ B.internal_marks,B.external_marks,B.Credits \ FROM faculty_subjects A \ JOIN subjects B on B.Subject_code = A.subject_code \ JOIN branch_subjects C on C.id = A.branch_subject_id \ WHERE A.taught_by_faculty_id = '%s'"%(faculty.faculty_id) cur.execute(sql) if cur.rowcount == 0: return [] subject_list = [] if display_marks: print("%4s|%-11s|%-45s|%-6s|%-8s|%13s|%13s|%s"%('Sr.#','SubjectCode','Subject Name',\ 'Branch','Semester','Max.Int.Marks','Max.Ext.Marks','Credits')) else: print("%4s|%-11s|%-45s|%-6s|%-8s"%('Sr.#','SubjectCode','Subject Name',\ 'Branch','Semester')) ctr=1 for row in cur: temp_sub = oM.FacultySubject(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]) subject_list.append(temp_sub) if display_marks: print("%4s|%-11s|%-45s|%-6s|%-8s|%13s|%13s|%s"%(ctr,temp_sub.subject_code,temp_sub.subject_name,\ temp_sub.branch_code,temp_sub.semester,temp_sub.internal_marks,temp_sub.external_marks,temp_sub.credits)) else: print("%4d|%-11s|%-45s|%-6s|%-8d"%(ctr,temp_sub.subject_code,temp_sub.subject_name,\ temp_sub.branch_code,temp_sub.semester)) ctr += 1 return subject_list except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def get_user_pwd(user_name, password): try: con = db.create_connection() cur = db.create_cursor(con) sql = "select id,user_id from user_validation where lower(user_id)='%s' and password='******'" % ( user_name.lower(), password) cur.execute(sql) for row in cur: return row return [] except Exception as e: print("Error Detected", e) return -1 finally: cur.close() con.close()
def get_userid(user_id): try: con = db.create_connection() cur = db.create_cursor(con) cur.execute( "select id from user_validation where lower(user_id)='%s'" % (user_id.lower())) id_list = [] for row in cur: id_list.append(row[0]) return id_list except Exception as e: print(e) return [] finally: cur.close() con.close()
def display_faculty_subject_distribution(): try: con = db.create_connection() cur = db.create_cursor(con) sql = "SELECT B.faculty_id,E.employee_id,CONCAT(F.first_name,' ',F.last_name),\ D.branch_code,D.semester,C.subject_code,C.subject FROM faculty_subjects A\ JOIN faculty B ON B.faculty_id = A.taught_by_faculty_id\ JOIN subjects C ON C.subject_code = A.subject_code\ JOIN branch_subjects D ON D.id = A.branch_subject_id\ JOIN employee E ON E.employee_id = B.employee_id\ JOIN user_details F ON F.parent_user_id = E.parent_user_id\ ORDER BY B.faculty_id, D.branch_code, D.semester" cur.execute(sql) if cur.rowcount == 0: print("No subject allocation has been done yet") return print("%-5s|%-10s|%-7s|%-30s|%-11s|%-8s|%-8s|%s" % ('Sr.#', 'Faculty ID', 'Emp.ID', 'Name', 'Branch Code', 'Semester', 'Sub Code', 'Subject Name')) ctr = 1 for row in cur: print( "%-5s|%-10s|%-7s|%-30s|%-11s|%-8s|%-8s|%s" % (ctr, row[0], row[1], row[2], row[3], row[4], row[5], row[6])) ctr += 1 return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def check_subjects(student): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) print("Name: %-25s Roll No: %-10d Branch: %-4s Current Semester: %-2d"% (student.user_details.first_name, student.std_roll_no, student.std_branch, student.semester)) sem = student.semester while 1: sem = int(input('Enter the semester to view subjects: ')) if sem > 8 or sem <= 0: print("Enter the correct Value") else: break sql1 = "SELECT D.subject_code, D.subject, D.internal_marks, D.external_marks, D.credits FROM students A \ JOIN branch B on A.std_branch = B.branch_code \ JOIN branch_subjects C on A.std_branch = C.branch_code \ JOIN subjects D on C.subject_code = D.subject_code \ where std_roll_no=%s and C.semester=%d"%(student.std_roll_no,int(sem)) cur.execute(sql1) if cur.rowcount==0: return [] ctr = 1 print("%-6s|%-13s|%-45s|%13s|%13s|%11s"%('Sr.No.','Subject_Code','Subject_Name',\ 'Max.Int.Marks','Max.Ext.Marks','Credits')) for row in cur: print("%6d|%-13s|%-45s|%13s|%13s|%11.2f"%(ctr,row[0],row[1],row[2],row[3],row[4])) ctr += 1 except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def insert_user(user_id, password, f_name, l_name, e_id, gender): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "INSERT INTO `user_validation` (`user_id`,`password`) VALUES ('%s', '%s')" % ( user_id, password) cur.execute(sql) if cur.rowcount == 0: return 0 sql = "SELECT id FROM user_validation WHERE user_id='%s'" % (user_id) cur.execute(sql) if cur.rowcount == 0: return 0 parent_id = None for row in cur: parent_id = row[0] sql = "INSERT INTO user_details (parent_user_id, first_name, last_name, email, gender) VALUES \ (%d,'%s','%s','%s','%s')" % (parent_id, f_name, l_name, e_id, gender) cur.execute(sql) con.commit() return 1 except pymysql.OperationalError as e: print(e) print("Error Detected") return -1 except Exception as e: print(e) return -1 finally: cur.close() con.close()
def run_query(sql): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) cur.execute(sql) con.commit() return 1 except pymysql.OperationalError as e: print(e) return -1 except Exception as e: print(e) return -1 finally: print(cur) cur.close() con.close()
def check_user_as_employee(id_i): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "SELECT employee_id from employee \ where parent_user_id=%d"%(id_i) cur.execute(sql) for row in cur: return row return [] except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def display_faculty(): try: con = db.create_connection() cur = db.create_cursor(con) sql = "SELECT A.faculty_id, A.employee_id, CONCAT(C.first_name,' ',C.last_name),\ A.designation FROM faculty A\ JOIN employee B ON A.employee_id = B.employee_id\ JOIN user_details C ON C.parent_user_id = B.parent_user_id" cur.execute(sql) if cur.rowcount == 0: print("No faculties available") return print("%-5s|%-10s|%-7s|%-30s|%s" % ('Sr.#', 'Faculty ID', 'Emp.ID.', 'Name', 'Designation')) ctr = 1 for row in cur: print("%-5s|%-10s|%-7s|%-30s|%s" % (ctr, row[0], row[1], row[2], row[3])) ctr += 1 return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def display_subjects(): try: con = db.create_connection() cur = db.create_cursor(con) sql = "SELECT A.branch_code,A.semester,A.subject_code,B.subject\ FROM branch_subjects A\ JOIN subjects B ON B.subject_code = A.subject_code\ order by A.branch_code , A.semester ASC" cur.execute(sql) if cur.rowcount == 0: print("Thats weired! No Subject available") return print("%-5s|%-10s|%-7s|%-8s|%-30s" % ('Sr.#', 'Branch Code', 'Semester', 'Sub Code', 'Subject Name')) ctr = 1 for row in cur: print("%-5s|%-10s|%-7s|%-8s|%-30s" % (ctr, row[0], row[1], row[2], row[3])) ctr += 1 return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def fetch_user_details(id_i): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "SELECT first_name, last_name, email, gender from user_details \ where parent_user_id=%d"%(id_i) cur.execute(sql) for row in cur: return row return 1 except pymysql.OperationalError as e: print(e) return -1 except Exception as e: print(e) return -1 finally: cur.close() con.close()
def validate_admin(user_name, password): try: con = db.create_connection() cur = db.create_cursor(con) cur.execute( "select id from admin_table where lower(user_id)='%s' and password='******'" % (user_name, password)) count = 0 for row in cur: count += 1 if count == 1: return row[0] raise aM.invalidUserPassword except aM.invalidUserPassword as e: print(e) return -1 except Exception as e: print("Error Detected", e) return -2 finally: cur.close() con.close()
def update_semester_status(student_roll_no, semester): try: con = dBconnectivity.create_connection() cur = dBconnectivity.create_cursor(con) sql = "SELECT D.subject_code,D.internal_marks, \ D.external_marks, D.credits FROM students A \ JOIN branch B on A.std_branch = B.branch_code \ JOIN branch_subjects C on A.std_branch = C.branch_code \ JOIN subjects D on C.subject_code = D.subject_code \ where std_roll_no=%s and C.semester=%d"%(student_roll_no,semester) cur.execute(sql) tot_internal_marks, tot_external_marks, tot_credits = 0, 0, 0.0 subject_list = [] for row in cur: tot_internal_marks += int(0 if row[1]==None else row[1]) tot_external_marks += int(0 if row[2]==None else row[2]) tot_credits += float(0.0 if row[3]==None else row[3]) subject_list.append(row[0]) tot_max_marks = tot_internal_marks+tot_external_marks if tot_max_marks == 0: return sql = "SELECT subject_code, internal_marks, external_marks, \ credits FROM stu_subject_marks \ WHERE student_roll_no=%s and semester=%d"%(student_roll_no, semester) cur.execute(sql) internal_marks, external_marks, credits = 0, 0, 0.0 for row in cur: if row[0] in subject_list: subject_list.remove(row[0]) internal_marks += int(row[1]) external_marks += int(row[2]) credits += float(row[3]) if len(subject_list)!=0: return tot_marks_obtained = internal_marks+external_marks percentage = (tot_marks_obtained/tot_max_marks)*100 if percentage <= 34.0: status = "FAIL" else: status = "PASS" #ll = [semester,tot_max_marks,semester,tot_marks_obtained,semester,status,student_roll_no] sql = "UPDATE stu_semwise_status SET\ sem_%d_max_marks=%d, sem_%d_marks=%d, sem_%d_status='%s'\ WHERE std_roll_no=%s"%(semester,tot_max_marks,semester,tot_marks_obtained,semester,status,student_roll_no) cur.execute(sql) if status == "PASS" and semester!=8: sql = "UPDATE students SET std_semester=std_semester+1\ WHERE std_roll_no=%s"%(student_roll_no) cur.execute(sql) con.commit() except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()
def addFaculty(): try: con = db.create_connection() cur = db.create_cursor(con) print("-----------Add Faculty------------") print( "Note: To add faculty, the person should be registered as user already!" ) print( "Also, since you are admin you must be knowing the id(Primary key) of the users to be added." ) u_id = input('Please enter the user_id of user: '******'Assistant Professor', 2: 'Professor', 4: 'Lab Assistant', 1: 'HOD' } for i in range(1, 5): print("%d. %s" % (i, designation_dict[i])) while 1: key = input("Enter the sr# of designation: ") if key.isdigit() and int(key) in range(1, 5): break print("Wrong choice!") while 1: salary = input('Enter the salary: ') if salary.isdigit(): break print("Please enter the numeric value") sql = "INSERT INTO employee(parent_user_id, emp_category) VALUES (%s,'Faculty')" % ( user.id) cur.execute(sql) sql = "SELECT employee_id FROM employee WHERE parent_user_id=%s" % ( user.id) cur.execute(sql) if cur.rowcount == 0: raise Exception for row in cur: employee_id = row[0] sql = "INSERT INTO faculty(employee_id,designation,salary) VALUES (%s,'%s',%s)" % ( employee_id, designation_dict[int(key)], salary) cur.execute(sql) con.commit() sql = "SELECT faculty_id FROM faculty WHERE employee_id=%s" % ( employee_id) cur.execute(sql) if cur.rowcount == 0: raise Exception for row in cur: faculty_id = row[0] print("Generated Employee ID: %s Faculty ID: %s" % (employee_id, faculty_id)) return 1 except Exception as e: print("Error Detected", e) return -2 finally: cur.close() con.close()
def allocateSubjects(): try: con = db.create_connection() cur = db.create_cursor(con) ch = input("Press v to view the faculty list: ") if ch.lower() == 'v': display_faculty() ch = input("Press v to view the subject list: ") if ch.lower() == 'v': display_subjects() print( "Note: To allocate subjects *.csv file should be written in following format" ) print("Faculty ID,Branch Code,Semester,Subject Code\n") print( "Please enter the absolute/relative path name of the *.csv file: ") path = input("Path: ") try: f1 = open(path, 'r') subject = f1.readline() while subject: subject = subject.strip().split(',') print("Subject: ", subject) for i in range(len(subject)): subject[i] = subject[i].strip() sql = "SELECT id FROM branch_subjects WHERE branch_code='%s' \ and semester=%s and subject_code='%s'" % ( subject[1], subject[2], subject[3]) cur.execute(sql) if cur.rowcount == 0: print("Id not found!") print("Abort") return for row in cur: branch_subject_id = row[0] sql = "INSERT INTO faculty_subjects (taught_by_faculty_id, subject_code, branch_subject_id) VALUES\ ('%s','%s',%s)" % (subject[0], subject[3], branch_subject_id) cur.execute(sql) subject = f1.readline() con.commit() except Exception as e: print(e) return con.commit() return except pymysql.OperationalError as e: print(e) return [] except Exception as e: print(e) return [] finally: cur.close() con.close()