from flask import Blueprint, request, jsonify, session, redirect from werkzeug.security import generate_password_hash, check_password_hash from config import get_connection from flask_cors import CORS, cross_origin # Add cross_origin import import os from werkzeug.utils import secure_filename from flask import current_app, url_for from functools import wraps from flask import session, jsonify user_routes = Blueprint('user_routes', __name__) # Remove duplicate CORS setup and use a single configuration CORS(user_routes, supports_credentials=True, resources={ r"/*": { "origins": "https://lealavaschoolmanagement.com", "methods": ["GET", "POST", "PUT", "DELETE", "OPTIONS"], "allow_headers": ["Content-Type"], "supports_credentials": True } }) # CORS(user_routes, supports_credentials=True, resources={ # r"/*": { # "origins": "http://localhost:3000", # "methods": ["GET", "POST", "PUT", "DELETE", "OPTIONS"], # "allow_headers": ["Content-Type"], # "supports_credentials": True # } # }) # UPLOAD_FOLDER = 'uploads' # Define the folder to store uploaded files # ALLOWED_EXTENSIONS = {'pdf', 'png', 'jpg', 'jpeg', 'docx'} # Allowed file extensions # # Ensure the upload folder exists # if not os.path.exists(UPLOAD_FOLDER): # os.makedirs(UPLOAD_FOLDER) # def allowed_file(filename): # return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS # @user_routes.route('/upload', methods=['POST']) # def upload_file(): # if 'file' not in request.files: # return jsonify({"error": "No file part in the request"}), 400 # file = request.files['file'] # if file.filename == '': # return jsonify({"error": "No file selected for uploading"}), 400 # if file and allowed_file(file.filename): # filename = secure_filename(file.filename) # file_path = os.path.join(UPLOAD_FOLDER, filename) # file.save(file_path) # return jsonify({"message": "File uploaded successfully", "file_path": file_path}), 200 # else: # return jsonify({"error": "File type not allowed"}), 400 UPLOAD_FOLDER = '/var/www/html/School_Mangement_New/src/backend/static/uploads' # Define the folder to store uploaded files # UPLOAD_FOLDER = '/backend/static/uploads' ALLOWED_EXTENSIONS = {'pdf', 'png', 'jpg', 'jpeg', 'docx'} # Allowed file extensions # Ensure the upload folder exists if not os.path.exists(UPLOAD_FOLDER): os.makedirs(UPLOAD_FOLDER) def allowed_file(filename): return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS @user_routes.route('/upload', methods=['POST']) def upload_file(): if 'file' not in request.files: return jsonify({"error": "No file part in the request"}), 400 file = request.files['file'] if file.filename == '': return jsonify({"error": "No file selected for uploading"}), 400 if file and allowed_file(file.filename): filename = secure_filename(file.filename) file_path = os.path.join(UPLOAD_FOLDER, filename) file.save(file_path) return jsonify({"message": "File uploaded successfully", "file_path": file_path}), 200 else: return jsonify({"error": "File type not allowed"}), 400 @user_routes.route('/admin/Teachers', methods=['GET', 'POST']) def manage_teachers(): if request.method == 'GET': connection = get_connection() cursor = connection.cursor(dictionary=True) try: cursor.execute(""" SELECT teacher_id, name, email, mobile, joining_date, subject, `class`, gender FROM Teachers """) teachers = cursor.fetchall() return jsonify(teachers), 200 except Exception as e: return jsonify({"error": f"Failed to fetch teachers: {str(e)}"}), 500 finally: cursor.close() connection.close() elif request.method == 'POST': data = request.json required_fields = ['name', 'email', 'mobile', 'joining_date', 'subject', 'class', 'gender'] if not data or not all(data.get(field) for field in required_fields): return jsonify({"error": "All fields are required!"}), 400 connection = get_connection() cursor = connection.cursor() try: insert_query = """ INSERT INTO Teachers (name, email, mobile, joining_date, subject, `class`, gender) VALUES (%s, %s, %s, %s, %s, %s, %s) """ cursor.execute(insert_query, ( data['name'], data['email'], data['mobile'], data['joining_date'], data['subject'], data['class'], data['gender'] )) connection.commit() auto_id = cursor.lastrowid # Get auto-incremented ID teacher_id = f"TEACH{auto_id}" # Generate teacher_id like 'TEACH5' # Update the newly inserted record to set teacher_id update_query = "UPDATE Teachers SET teacher_id = %s WHERE auto_id = %s" cursor.execute(update_query, (teacher_id, auto_id)) connection.commit() return jsonify({"message": "Teacher added successfully!", "teacher_id": teacher_id}), 201 except Exception as e: connection.rollback() return jsonify({"error": f"Failed to add teacher: {str(e)}"}), 500 finally: cursor.close() connection.close() @user_routes.route('/Teachers/', methods=['PUT']) def update_teacher(teacher_id): data = request.json required_fields = ['name', 'email', 'mobile', 'joining_date', 'subject', 'class', 'gender'] if not data or not all(data.get(field) for field in required_fields): return jsonify({"error": "All fields are required!"}), 400 connection = get_connection() cursor = connection.cursor() try: update_query = """ UPDATE Teachers SET name = %s, email = %s, mobile = %s, joining_date = %s, subject = %s, `class` = %s, gender = %s WHERE teacher_id = %s """ cursor.execute(update_query, ( data['name'], data['email'], data['mobile'], data['joining_date'], data['subject'], data['class'], data['gender'], teacher_id )) connection.commit() if cursor.rowcount == 0: return jsonify({"error": "Teacher not found!"}), 404 return jsonify({"message": "Teacher updated successfully!"}), 200 except Exception as e: connection.rollback() return jsonify({"error": f"Failed to update teacher: {str(e)}"}), 500 finally: cursor.close() connection.close() @user_routes.route('/Teachers/', methods=['DELETE']) def delete_teacher(teacher_id): connection = get_connection() cursor = connection.cursor(dictionary=True) # optional: dictionary cursor for better handling try: # First check if the teacher exists select_query = "SELECT * FROM Teachers WHERE teacher_id = %s" cursor.execute(select_query, (teacher_id,)) teacher = cursor.fetchone() if not teacher: return jsonify({"error": "Teacher not found!"}), 404 # Delete the teacher delete_query = "DELETE FROM Teachers WHERE teacher_id = %s" cursor.execute(delete_query, (teacher_id,)) connection.commit() return jsonify({"message": "Teacher deleted successfully!"}), 200 except Exception as e: connection.rollback() return jsonify({"error": f"Failed to delete teacher: {str(e)}"}), 500 finally: cursor.close() connection.close() @user_routes.route('/admin/Students', methods=['GET', 'POST', 'PUT', 'DELETE']) def manage_students(): connection = get_connection() cursor = connection.cursor(dictionary=True) try: if request.method == 'GET': # Add filtering parameters class_filter = request.args.get('class') section_filter = request.args.get('section') base_query = "SELECT * FROM Students" params = [] conditions = [] if class_filter: conditions.append("class = %s") params.append(class_filter) if section_filter: conditions.append("section = %s") params.append(section_filter) if conditions: base_query += " WHERE " + " AND ".join(conditions) cursor.execute(base_query, params) students = cursor.fetchall() return jsonify(students), 200 elif request.method == 'POST': data = request.json required_fields = [ 'name', 'dob', 'class', 'section', 'gender', 'father_name', 'mother_name', 'email', 'mobile', 'address' ] if not all(field in data for field in required_fields): return jsonify({"error": "All fields are required!"}), 400 # Check for existing student by email or mobile number (unique constraints) cursor.execute("SELECT auto_id FROM Students WHERE email = %s OR mobile = %s", (data['email'], data['mobile'])) if cursor.fetchone(): return jsonify({"error": "Student with this email or mobile number already exists!"}), 409 # Insert student data cursor.execute(""" INSERT INTO Students ( name, dob, class, section, gender, father_name, mother_name, email, mobile, address ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, ( data['name'], data['dob'], data['class'], data['section'], data['gender'], data['father_name'], data['mother_name'], data['email'], data['mobile'], data['address'] )) connection.commit() # Fetch the last inserted auto_id to generate student_id cursor.execute("SELECT LAST_INSERT_ID() AS last_id") last_id = cursor.fetchone()['last_id'] student_id = f"STU{last_id}" # Update student_id cursor.execute(""" UPDATE Students SET student_id = %s WHERE auto_id = %s """, (student_id, last_id)) connection.commit() return jsonify({"message": "Student created successfully!", "student_id": student_id}), 201 elif request.method == 'PUT': data = request.json if 'id' not in data: return jsonify({"error": "Student ID is required for update"}), 400 # Check for email or mobile number conflict (unique constraints) cursor.execute(""" SELECT auto_id FROM Students WHERE (email = %s OR mobile = %s) AND auto_id != %s """, (data['email'], data['mobile'], data['id'])) if cursor.fetchone(): return jsonify({"error": "Another student with this email or mobile number already exists!"}), 409 cursor.execute(""" UPDATE Students SET name = %s, dob = %s, class = %s, section = %s, gender = %s, father_name = %s, mother_name = %s, email = %s, mobile = %s, address = %s WHERE auto_id = %s """, ( data['name'], data['dob'], data['class'], data['section'], data['gender'], data['father_name'], data['mother_name'], data['email'], data['mobile'], data['address'], data['id'] )) connection.commit() return jsonify({"message": "Student updated successfully!"}), 200 elif request.method == 'DELETE': student_id = request.args.get('id') if not student_id: return jsonify({"error": "Student ID is required"}), 400 # Check for existing student by student_id cursor.execute("SELECT auto_id FROM Students WHERE student_id = %s", (student_id,)) if not cursor.fetchone(): return jsonify({"error": "Student not found!"}), 404 # Delete student by student_id cursor.execute("DELETE FROM Students WHERE student_id = %s", (student_id,)) connection.commit() return jsonify({"message": "Student deleted successfully!"}), 200 except Exception as e: connection.rollback() return jsonify({"error": str(e)}), 500 finally: cursor.close() connection.close() @user_routes.route('/admin/Fee', methods=['GET', 'POST', 'PUT', 'DELETE']) def manage_fee(): connection = get_connection() cursor = connection.cursor(dictionary=True) try: if request.method == 'GET': class_filter = request.args.get('class') payment_date = request.args.get('payment_date') base_query = """ SELECT f.fee_id, f.student_id, f.name, f.class, f.amount, f.payment_mode, f.payment_date, f.receipt_no, f.created_at FROM Fee f """ params = [] conditions = [] if class_filter: conditions.append("f.class = %s") params.append(class_filter) if payment_date: conditions.append("DATE(f.payment_date) = %s") params.append(payment_date) if conditions: base_query += " WHERE " + " AND ".join(conditions) cursor.execute(base_query, params) fee_records = cursor.fetchall() return jsonify(fee_records), 200 elif request.method == 'POST': data = request.json required_fields = ['student_id', 'name', 'class', 'amount', 'payment_mode', 'payment_date', 'receipt_no'] if not all(field in data for field in required_fields): return jsonify({"error": "All fields are required!"}), 400 cursor.execute(""" INSERT INTO Fee (student_id, name, class, amount, payment_mode, payment_date, receipt_no) VALUES (%s, %s, %s, %s, %s, %s, %s) """, ( data['student_id'], data['name'], data['class'], data['amount'], data['payment_mode'], data['payment_date'], data['receipt_no'] )) connection.commit() return jsonify({"message": "Fee record created successfully!"}), 201 elif request.method == 'PUT': data = request.json if 'fee_id' not in data: return jsonify({"error": "Fee ID is required for update"}), 400 query = """ UPDATE Fee SET amount = %s, payment_mode = %s, payment_date = %s, receipt_no = %s WHERE fee_id = %s """ cursor.execute(query, ( data['amount'], data['payment_mode'], data['payment_date'], data['receipt_no'], data['fee_id'] )) connection.commit() return jsonify({"message": "Fee record updated successfully!"}), 200 elif request.method == 'DELETE': fee_id = request.args.get('fee_id') if not fee_id: return jsonify({"error": "Fee ID is required"}), 400 query = "DELETE FROM Fee WHERE fee_id = %s" cursor.execute(query, (fee_id,)) connection.commit() return jsonify({"message": "Fee record deleted successfully!"}), 200 except Exception as e: connection.rollback() return jsonify({"error": str(e)}), 500 finally: cursor.close() connection.close() # @user_routes.route('/admin/notices', methods=['GET', 'POST']) # def manage_notices(): # if request.method == 'GET': # connection = get_connection() # cursor = connection.cursor(dictionary=True) # try: # # Fetching all notices from the "Notices" table # cursor.execute("SELECT id, title, file_path, uploaded_at FROM Notices") # notices = cursor.fetchall() # return jsonify(notices), 200 # except Exception as e: # return jsonify({"error": f"Failed to fetch notices: {str(e)}"}), 500 # finally: # cursor.close() # connection.close() # elif request.method == 'POST': # data = request.json # required_fields = ['title', 'file_path'] # if not all(data.get(field) for field in required_fields): # return jsonify({"error": "Both title and file path are required!"}), 400 # connection = get_connection() # cursor = connection.cursor() # try: # # Inserting a new notice into the "Notices" table # insert_query = """ # INSERT INTO Notices (title, file_path) # VALUES (%s, %s) # """ # cursor.execute(insert_query, ( # data['title'], data['file_path'] # )) # connection.commit() # # Retrieve the auto-generated notice id # notice_id_query = "SELECT id FROM Notices WHERE id = LAST_INSERT_ID()" # cursor.execute(notice_id_query) # notice_id = cursor.fetchone()[0] # return jsonify({"message": "Notice added successfully!", "notice_id": notice_id}), 201 # except Exception as e: # return jsonify({"error": f"Failed to add notice: {str(e)}"}), 500 # finally: # cursor.close() # connection.close() @user_routes.route('/admin/notices', methods=['GET', 'POST']) def manage_notices(): if request.method == 'GET': connection = get_connection() cursor = connection.cursor(dictionary=True) try: cursor.execute("SELECT id, title, file_path, uploaded_at FROM Notices") notices = cursor.fetchall() for notice in notices: if notice['file_path']: # Construct a full URL using the static route notice['file_url'] = url_for('static', filename=f"{notice['file_path']}", _external=True) return jsonify(notices), 200 except Exception as e: return jsonify({"error": f"Failed to fetch notices: {str(e)}"}), 500 finally: cursor.close() connection.close() elif request.method == 'POST': # Accept both title and file as form-data (not JSON) if 'title' not in request.form or 'file' not in request.files: return jsonify({"error": "Title and file are required"}), 400 title = request.form['title'] file = request.files['file'] if file.filename == '': return jsonify({"error": "No file selected"}), 400 if file and allowed_file(file.filename): filename = secure_filename(file.filename) file_path = os.path.join(UPLOAD_FOLDER, filename) file.save(file_path) relative_file_path = f"uploads/{filename}" # relative path for database connection = get_connection() cursor = connection.cursor() try: insert_query = """ INSERT INTO Notices (title, file_path) VALUES (%s, %s) """ cursor.execute(insert_query, (title, relative_file_path)) connection.commit() # Get the last inserted ID cursor.execute("SELECT id FROM Notices WHERE id = LAST_INSERT_ID()") notice_id = cursor.fetchone()[0] return jsonify({ "message": "Notice added successfully!", "notice_id": notice_id, "file_url": url_for('static', filename=relative_file_path, _external=True) }), 201 except Exception as e: return jsonify({"error": f"Failed to add notice: {str(e)}"}), 500 finally: cursor.close() connection.close() else: return jsonify({"error": "File type not allowed"}), 400 @user_routes.route('/admin/notices/', methods=['DELETE']) def delete_notice(id): connection = get_connection() cursor = connection.cursor(dictionary=True) try: # Fetch file_path to delete the file from disk cursor.execute("SELECT file_path FROM Notices WHERE id = %s", (id,)) row = cursor.fetchone() if not row: return jsonify({"error": "Notice not found"}), 404 file_path = row['file_path'] absolute_path = os.path.join('static', file_path) # 'static/uploads/filename.ext' # Delete record from DB cursor.execute("DELETE FROM Notices WHERE id = %s", (id,)) connection.commit() # Delete file from disk if it exists if file_path and os.path.exists(absolute_path): os.remove(absolute_path) return jsonify({"message": "Notice deleted successfully"}), 200 except Exception as e: return jsonify({"error": f"Failed to delete notice: {str(e)}"}), 500 finally: cursor.close() connection.close() @user_routes.route('/admin/timetable', methods=['GET', 'POST']) def manage_timetable(): if request.method == 'GET': connection = get_connection() cursor = connection.cursor(dictionary=True) try: cursor.execute("SELECT id, title, file_path, uploaded_at FROM TimeTable") timetables = cursor.fetchall() for timetable in timetables: if timetable['file_path']: timetable['file_url'] = url_for('static', filename=f"{timetable['file_path']}", _external=True) return jsonify(timetables), 200 except Exception as e: return jsonify({"error": f"Failed to fetch timetables: {str(e)}"}), 500 finally: cursor.close() connection.close() elif request.method == 'POST': if 'title' not in request.form or 'file' not in request.files: return jsonify({"error": "Title and file are required"}), 400 title = request.form['title'] uploaded_file = request.files['file'] if uploaded_file.filename == '': return jsonify({"error": "No file selected"}), 400 if uploaded_file and allowed_file(uploaded_file.filename): filename = secure_filename(uploaded_file.filename) timetable_folder = os.path.join(UPLOAD_FOLDER, 'timetables') if not os.path.exists(timetable_folder): os.makedirs(timetable_folder) file_path = os.path.join(timetable_folder, filename) uploaded_file.save(file_path) relative_file_path = f"uploads/timetables/{filename}" # Relative path for DB and static serving connection = get_connection() cursor = connection.cursor() try: insert_query = """ INSERT INTO TimeTable (title, file_path) VALUES (%s, %s) """ cursor.execute(insert_query, (title, relative_file_path)) connection.commit() cursor.execute("SELECT id FROM TimeTable WHERE id = LAST_INSERT_ID()") timetable_id = cursor.fetchone()[0] return jsonify({ "message": "Timetable uploaded successfully!", "timetable_id": timetable_id, "file_url": url_for('static', filename=relative_file_path, _external=True) }), 201 except Exception as e: return jsonify({"error": f"Failed to upload timetable: {str(e)}"}), 500 finally: cursor.close() connection.close() else: return jsonify({"error": "File type not allowed"}), 400 @user_routes.route('/admin/timetable/', methods=['DELETE']) def delete_timetable(id): connection = get_connection() cursor = connection.cursor(dictionary=True) try: # Fetch file_path to delete the file cursor.execute("SELECT file_path FROM TimeTable WHERE id = %s", (id,)) row = cursor.fetchone() if not row: return jsonify({"error": "Timetable not found"}), 404 file_path = row['file_path'] absolute_path = os.path.join('static', file_path) # Delete from DB cursor.execute("DELETE FROM TimeTable WHERE id = %s", (id,)) connection.commit() # Delete file from disk if file_path and os.path.exists(absolute_path): os.remove(absolute_path) return jsonify({"message": "Timetable deleted successfully"}), 200 except Exception as e: return jsonify({"error": f"Failed to delete timetable: {str(e)}"}), 500 finally: cursor.close() connection.close() # @user_routes.route('/admin/timetable', methods=['GET', 'POST']) # def manage_timetable(): # if request.method == 'GET': # connection = get_connection() # cursor = connection.cursor(dictionary=True) # Ensure results are returned as dictionaries # try: # # Fetching all timetables from the "TimeTable" table # cursor.execute("SELECT id, title, file_path, uploaded_at FROM TimeTable") # timetables = cursor.fetchall() # return jsonify(timetables), 200 # except Exception as e: # return jsonify({"error": f"Failed to fetch timetables: {str(e)}"}), 500 # finally: # cursor.close() # connection.close() # elif request.method == 'POST': # if 'file' not in request.files: # return jsonify({"error": "File is required!"}), 400 # title = request.form.get('title') # if not title: # return jsonify({"error": "Title is required!"}), 400 # uploaded_file = request.files['file'] # # Ensure the directory exists # timetable_folder = os.path.join(UPLOAD_FOLDER, 'timetables') # if not os.path.exists(timetable_folder): # os.makedirs(timetable_folder) # # Save the file to the server # file_path = os.path.join(timetable_folder, uploaded_file.filename) # try: # uploaded_file.save(file_path) # Save file to the desired location # except Exception as e: # return jsonify({"error": f"Failed to save file: {str(e)}"}), 500 # connection = get_connection() # cursor = connection.cursor(dictionary=True) # Ensure results are returned as dictionaries # try: # # Inserting a new timetable into the "TimeTable" table # insert_query = """ # INSERT INTO TimeTable (title, file_path) # VALUES (%s, %s) # """ # cursor.execute(insert_query, (title, file_path)) # connection.commit() # # Retrieve the auto-generated timetable id # cursor.execute("SELECT LAST_INSERT_ID() AS id") # timetable_id = cursor.fetchone()['id'] # Access the dictionary key instead of tuple index # return jsonify({"message": "Timetable uploaded successfully!", "timetable_id": timetable_id}), 201 # except Exception as e: # return jsonify({"error": f"Failed to upload timetable: {str(e)}"}), 500 # finally: # cursor.close() # connection.close() # @user_routes.route('/admin/assignments', methods=['GET', 'POST', 'PUT', 'DELETE']) # def manage_assignments(): # connection = get_connection() # cursor = connection.cursor(dictionary=True) # try: # # GET: Fetch assignments # if request.method == 'GET': # cursor.execute("SELECT * FROM assignments ORDER BY date DESC") # assignments = cursor.fetchall() # return jsonify(assignments), 200 # # POST: Add new assignment # elif request.method == 'POST': # if 'file' not in request.files: # return jsonify({"error": "No file uploaded"}), 400 # file = request.files['file'] # title = request.form.get('title') # date = request.form.get('date') # description = request.form.get('description') # assignment_class = request.form.get('class') # if not (title and date and description and assignment_class): # return jsonify({"error": "All fields are required"}), 400 # if file and allowed_file(file.filename): # filename = secure_filename(file.filename) # file_path = os.path.join(UPLOAD_FOLDER, filename) # file.save(file_path) # cursor.execute( # "INSERT INTO assignments (title, date, description, class, file_path) VALUES (%s, %s, %s, %s, %s)", # (title, date, description, assignment_class, file_path) # ) # connection.commit() # return jsonify({"message": "Assignment uploaded successfully", "file_path": file_path}), 201 # else: # return jsonify({"error": "Invalid file type"}), 400 # # PUT: Update an assignment # elif request.method == 'PUT': # assignment_id = request.form.get('assignment_id') # title = request.form.get('title') # date = request.form.get('date') # description = request.form.get('description') # assignment_class = request.form.get('class') # if not assignment_id: # return jsonify({"error": "Assignment ID is required"}), 400 # cursor.execute("SELECT * FROM assignments WHERE id = %s", (assignment_id,)) # assignment = cursor.fetchone() # if not assignment: # return jsonify({"error": "Assignment not found"}), 404 # file = request.files.get('file') # if file and allowed_file(file.filename): # filename = secure_filename(file.filename) # file_path = os.path.join(UPLOAD_FOLDER, filename) # file.save(file_path) # # Delete the old file # old_file_path = assignment['file_path'] # if os.path.exists(old_file_path): # os.remove(old_file_path) # cursor.execute( # "UPDATE assignments SET title=%s, date=%s, description=%s, class=%s, file_path=%s WHERE id=%s", # (title, date, description, assignment_class, file_path, assignment_id) # ) # connection.commit() # return jsonify({"message": "Assignment updated successfully", "file_path": file_path}), 200 # else: # cursor.execute( # "UPDATE assignments SET title=%s, date=%s, description=%s, class=%s WHERE id=%s", # (title, date, description, assignment_class, assignment_id) # ) # connection.commit() # return jsonify({"message": "Assignment updated successfully"}), 200 # # DELETE: Delete an assignment # elif request.method == 'DELETE': # assignment_id = request.args.get('assignment_id') # if not assignment_id: # return jsonify({"error": "Assignment ID is required"}), 400 # cursor.execute("SELECT file_path FROM assignments WHERE id = %s", (assignment_id,)) # assignment = cursor.fetchone() # if not assignment: # return jsonify({"error": "Assignment not found"}), 404 # # Delete the file # file_path = assignment['file_path'] # if os.path.exists(file_path): # os.remove(file_path) # cursor.execute("DELETE FROM assignments WHERE id = %s", (assignment_id,)) # connection.commit() # return jsonify({"message": "Assignment deleted successfully"}), 200 # except Exception as e: # connection.rollback() # return jsonify({"error": str(e)}), 500 # finally: # cursor.close() # connection.close() @user_routes.route('/admin/assignments', methods=['GET', 'POST', 'PUT', 'DELETE']) def manage_assignments(): connection = get_connection() cursor = connection.cursor(dictionary=True) try: # GET: Fetch assignments if request.method == 'GET': cursor.execute("SELECT * FROM assignments ORDER BY date DESC") assignments = cursor.fetchall() for assignment in assignments: if assignment['file_path']: assignment['file_url'] = url_for('static', filename=assignment['file_path'], _external=True) return jsonify(assignments), 200 # POST: Add new assignment elif request.method == 'POST': if 'file' not in request.files: return jsonify({"error": "No file uploaded"}), 400 file = request.files['file'] title = request.form.get('title') date = request.form.get('date') description = request.form.get('description') assignment_class = request.form.get('class') if not (title and date and description and assignment_class): return jsonify({"error": "All fields are required"}), 400 if file and allowed_file(file.filename): filename = secure_filename(file.filename) assignment_folder = os.path.join(UPLOAD_FOLDER, 'assignments') if not os.path.exists(assignment_folder): os.makedirs(assignment_folder) file_path = os.path.join(assignment_folder, filename) file.save(file_path) relative_path = f"uploads/assignments/{filename}" cursor.execute( "INSERT INTO assignments (title, date, description, class, file_path) VALUES (%s, %s, %s, %s, %s)", (title, date, description, assignment_class, relative_path) ) connection.commit() return jsonify({ "message": "Assignment uploaded successfully", "file_url": url_for('static', filename=relative_path, _external=True) }), 201 else: return jsonify({"error": "Invalid file type"}), 400 # PUT: Update an assignment elif request.method == 'PUT': assignment_id = request.form.get('assignment_id') title = request.form.get('title') date = request.form.get('date') description = request.form.get('description') assignment_class = request.form.get('class') if not assignment_id: return jsonify({"error": "Assignment ID is required"}), 400 cursor.execute("SELECT * FROM assignments WHERE id = %s", (assignment_id,)) assignment = cursor.fetchone() if not assignment: return jsonify({"error": "Assignment not found"}), 404 file = request.files.get('file') if file and allowed_file(file.filename): filename = secure_filename(file.filename) assignment_folder = os.path.join(UPLOAD_FOLDER, 'assignments') if not os.path.exists(assignment_folder): os.makedirs(assignment_folder) file_path = os.path.join(assignment_folder, filename) file.save(file_path) # Delete the old file old_file_path = os.path.join('static', assignment['file_path']) if os.path.exists(old_file_path): os.remove(old_file_path) relative_path = f"uploads/assignments/{filename}" cursor.execute( "UPDATE assignments SET title=%s, date=%s, description=%s, class=%s, file_path=%s WHERE id=%s", (title, date, description, assignment_class, relative_path, assignment_id) ) connection.commit() return jsonify({ "message": "Assignment updated successfully", "file_url": url_for('static', filename=relative_path, _external=True) }), 200 else: cursor.execute( "UPDATE assignments SET title=%s, date=%s, description=%s, class=%s WHERE id=%s", (title, date, description, assignment_class, assignment_id) ) connection.commit() return jsonify({"message": "Assignment updated successfully"}), 200 # DELETE: Delete an assignment elif request.method == 'DELETE': assignment_id = request.args.get('assignment_id') if not assignment_id: return jsonify({"error": "Assignment ID is required"}), 400 cursor.execute("SELECT file_path FROM assignments WHERE id = %s", (assignment_id,)) assignment = cursor.fetchone() if not assignment: return jsonify({"error": "Assignment not found"}), 404 # Delete the file file_path = os.path.join('static', assignment['file_path']) if os.path.exists(file_path): os.remove(file_path) cursor.execute("DELETE FROM assignments WHERE id = %s", (assignment_id,)) connection.commit() return jsonify({"message": "Assignment deleted successfully"}), 200 except Exception as e: connection.rollback() return jsonify({"error": str(e)}), 500 finally: cursor.close() connection.close() @user_routes.after_request def add_security_headers(response): response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate, max-age=0' response.headers['Pragma'] = 'no-cache' response.headers['Expires'] = '0' return response @user_routes.route('/assignments', methods=['GET']) def get_student_assignments(): conn = get_connection() cursor = conn.cursor(dictionary=True) try: # Step 1: Check if user is logged in if 'user_id' not in session: return jsonify({ "isLoggedIn": False }), 401 try: # Step 2: Get student class using student_id student_id = session['user_id'] cursor.execute(""" SELECT class FROM Students WHERE student_id = %s """, (student_id,)) student = cursor.fetchone() if not student: return jsonify({"error": "Student not found"}), 404 student_class = student['class'] # Step 3: Fetch assignments for the student's class cursor.execute(""" SELECT id, title, date, description, file_path FROM assignments WHERE class = %s ORDER BY date DESC """, (student_class,)) assignments = cursor.fetchall() for assignment in assignments: if assignment['file_path']: assignment['file_url'] = url_for('static', filename=assignment['file_path'], _external=True) return jsonify({ "assignments": assignments }), 200 finally: cursor.close() conn.close() except Exception as e: print(f"Error fetching student assignments: {str(e)}") return jsonify({ "error": "Failed to fetch assignments" }), 500 # @user_routes.route('/admin/signup', methods=['POST']) # def signup_user(): # try: # data = request.json # email = data.get('email') # mobile = data.get('mobile_no') # roll_no = data.get('roll_no') # This is the user ID # password = data.get('password') # if not all([email, mobile, roll_no, password]): # return jsonify({"error": "All fields are required!"}), 400 # connection = get_connection() # cursor = connection.cursor() # # Check if the roll_no exists in Students or Teachers table # cursor.execute("SELECT * FROM Students WHERE student_id = %s, AND email = %s AND mobile = %s", (roll_no, email, mobile)) # student_result = cursor.fetchone() # cursor.execute("SELECT * FROM Teachers WHERE teacher_id = %s, AND email = %s AND mobile = %s ", (roll_no, email, mobile)) # teacher_result = cursor.fetchone() # # Determine user type # if roll_no == "ADMIN01": # user_type = "1" # Admin # elif student_result: # user_type = "3" # Student # elif teacher_result: # user_type = "2" # Teacher # else: # return jsonify({"error": "The provided ID does not exist in the Students or Teachers table."}), 404 # # Check if email is already registered # cursor.execute("SELECT * FROM Login WHERE email = %s", (email,)) # if cursor.fetchone(): # return jsonify({"error": "Email is already registered!"}), 409 # # Ensure the user_id exists in the appropriate table before inserting into Login # if user_type == "3" and not student_result: # return jsonify({"error": "Student ID does not exist in the Students table."}), 404 # if user_type == "2" and not teacher_result: # return jsonify({"error": "Teacher ID does not exist in the Teachers table."}), 404 # # Hash the password before storing # hashed_password = generate_password_hash(password) # # Insert user into Login table # insert_query = """ # INSERT INTO Login (user_id, email, mobile, password_hash, user_type) # VALUES (%s, %s, %s, %s, %s) # """ # cursor.execute(insert_query, (roll_no, email, mobile, hashed_password, user_type)) # connection.commit() # return jsonify({"message": "User registered successfully!", "user_type": user_type, "redirect": "/Signin"}), 201 # except Exception as e: # return jsonify({"error": f"An unexpected error occurred: {str(e)}"}), 500 # finally: # if 'cursor' in locals(): # cursor.close() # if 'connection' in locals(): # connection.close() @user_routes.route('/admin/signup', methods=['POST']) def signup_user(): try: data = request.json email = data.get('email') mobile = data.get('mobile_no') roll_no = data.get('roll_no') # This is the user ID password = data.get('password') if not all([email, mobile, roll_no, password]): return jsonify({"error": "All fields are required!"}), 400 connection = get_connection() cursor = connection.cursor() # Special handling for admin if roll_no == "ADMIN01": user_type = "1" # Admin else: # Check if the roll_no, email, and mobile match in Students table cursor.execute(""" SELECT * FROM Students WHERE student_id = %s AND email = %s AND mobile = %s """, (roll_no, email, mobile)) student_result = cursor.fetchone() # Check if the roll_no, email, and mobile match in Teachers table cursor.execute(""" SELECT * FROM Teachers WHERE teacher_id = %s AND email = %s AND mobile = %s """, (roll_no, email, mobile)) teacher_result = cursor.fetchone() # Determine user type if student_result: user_type = "3" # Student elif teacher_result: user_type = "2" # Teacher else: return jsonify({ "error": "No matching record found for the provided ID, email, and mobile number." }), 404 # Check if email is already registered cursor.execute("SELECT * FROM Login WHERE email = %s", (email,)) if cursor.fetchone(): return jsonify({"error": "Email is already registered!"}), 409 # Hash the password before storing hashed_password = generate_password_hash(password) # Insert user into Login table insert_query = """ INSERT INTO Login (user_id, email, mobile, password_hash, user_type) VALUES (%s, %s, %s, %s, %s) """ cursor.execute(insert_query, (roll_no, email, mobile, hashed_password, user_type)) connection.commit() return jsonify({ "message": "Registered Successfully!", "user_type": user_type, "redirect": "/Signin" }), 201 except Exception as e: return jsonify({"error": f"An unexpected error occurred: {str(e)}"}), 500 finally: if 'cursor' in locals(): cursor.close() if 'connection' in locals(): connection.close() @user_routes.route('/login', methods=['POST']) def login_user(): try: data = request.json roll_no = data.get('roll_no') password = data.get('password') if not all([roll_no, password]): return jsonify({"error": "Both User ID and Password are required!"}), 400 connection = get_connection() cursor = connection.cursor(dictionary=True) try: cursor.execute(""" SELECT user_id, password_hash, user_type FROM Login WHERE user_id = %s """, (roll_no,)) user = cursor.fetchone() if not user or not check_password_hash(user['password_hash'], password): return jsonify({ "error": "Invalid User ID or Password!", "isLoggedIn": False }), 401 # Set session data with permanent=True session.permanent = True session['user_id'] = user['user_id'] session['user_type'] = user['user_type'] response = jsonify({ "message": "Login successful!", "isLoggedIn": True, "user_id": user['user_id'], "user_type": user['user_type'], "redirect": "/StudentDashboard" if user['user_type'] == '3' else "/TeacherDashboard" if user['user_type'] == '2' else "/Dashboard" }) # Set cookie attributes response.set_cookie( 'session', session['user_id'], httponly=True, samesite='Lax', secure=False, # Set to True in production with HTTPS max_age=86400 # 24 hours ) return response, 200 finally: cursor.close() connection.close() except Exception as e: print(f"Login error: {str(e)}") return jsonify({ "error": "Login failed", "isLoggedIn": False }), 500 @user_routes.route('/admin_signup', methods=['POST']) def signup_admin(): try: data = request.json name = data.get('name') email = data.get('email') mobile = data.get('mobile_no') password = data.get('password') if not all([name, email, mobile, password]): return jsonify({"error": "All fields are required!"}), 400 connection = get_connection() cursor = connection.cursor() # 1. Get last ADMIN user_id from Login table cursor.execute(""" SELECT user_id FROM Login WHERE user_id LIKE 'ADMIN%%' ORDER BY LENGTH(user_id) DESC, user_id DESC LIMIT 1 """) last_row = cursor.fetchone() if last_row: last_id = last_row[0] # e.g., 'ADMIN07' last_number = int(last_id.replace('ADMIN', '')) new_number = last_number + 1 else: new_number = 1 # 2. Format user_id (e.g., ADMIN01, ADMIN02...) user_id = f"ADMIN{str(new_number).zfill(2)}" # 3. Hash the password password_hash = generate_password_hash(password) # Debug print for validation print("Generated user_id:", user_id) print("user_type:", 1) # 4. Insert new admin into Login table cursor.execute(""" INSERT INTO Login (user_id, name, email, mobile, password_hash, user_type) VALUES (%s, %s, %s, %s, %s, %s) """, (user_id, name, email, mobile, password_hash, 1)) # user_type as INTEGER connection.commit() return jsonify({"message": "Admin registered successfully", "user_id": user_id}), 201 except Exception as e: print("Exception occurred during admin registration:", str(e)) return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if connection: connection.close() @user_routes.route('/admin_signup', methods=['GET']) def get_admins(): try: user_id = request.args.get('user_id') # Optional query param connection = get_connection() cursor = connection.cursor(dictionary=True) if user_id: cursor.execute("SELECT * FROM Login WHERE user_id = %s AND user_type = '1'", (user_id,)) else: cursor.execute("SELECT * FROM Login WHERE user_type = '1'") admins = cursor.fetchall() return jsonify(admins), 200 if admins else (jsonify({"message": "No admin(s) found"}), 404) except Exception as e: return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if connection: connection.close() @user_routes.route('/admin_signup', methods=['PUT']) def update_admin(): try: data = request.json user_id = data.get('user_id') if not user_id: return jsonify({"error": "user_id is required"}), 400 name = data.get('name') email = data.get('email') mobile = data.get('mobile') password = data.get('password') connection = get_connection() cursor = connection.cursor() update_fields = [] update_values = [] if name: update_fields.append("name = %s") update_values.append(name) if email: update_fields.append("email = %s") update_values.append(email) if mobile: update_fields.append("mobile = %s") update_values.append(mobile) if password: password_hash = generate_password_hash(password) update_fields.append("password_hash = %s") update_values.append(password_hash) if not update_fields: return jsonify({"message": "No fields to update"}), 400 update_values.append(user_id) cursor.execute(f""" UPDATE Login SET {', '.join(update_fields)} WHERE user_id = %s AND user_type = '1' """, tuple(update_values)) connection.commit() if cursor.rowcount == 0: return jsonify({"message": "Admin not found or nothing updated"}), 404 return jsonify({"message": "Admin updated successfully"}), 200 except Exception as e: return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if connection: connection.close() @user_routes.route('/admin_signup', methods=['DELETE']) def delete_admin(): try: data = request.json print("DELETE request data:", data) # Debug log user_id = data.get('user_id') if not user_id: return jsonify({"error": "user_id is required"}), 400 connection = get_connection() cursor = connection.cursor() cursor.execute(""" DELETE FROM Login WHERE user_id = %s AND user_type = '1' """, (user_id,)) connection.commit() if cursor.rowcount == 0: return jsonify({"message": "Admin not found"}), 404 return jsonify({"message": "Admin deleted successfully"}), 200 except Exception as e: print("Exception in delete_admin:", str(e)) # Debug log return jsonify({"error": str(e)}), 500 finally: if cursor: cursor.close() if connection: connection.close() @user_routes.route('/admin/logout', methods=['POST']) def logout_admin(): try: # Clear the session session.clear() response = jsonify({ "message": "Logout successful!", "isLoggedIn": False }) # Clear the session cookie response.delete_cookie('session') return response, 200 except Exception as e: print(f"Error during logout: {e}") return jsonify({ "error": "An error occurred during logout.", "isLoggedIn": False, "redirect": "/signin" }), 500 def login_required(f): """ Decorator to protect routes that require login. Returns JSON response if the user is not logged in. """ @wraps(f) def decorated_function(*args, **kwargs): if 'user_id' not in session: return jsonify({ "isLoggedIn": False, "error": "Unauthorized access", "redirect": "/signin" }), 401 return f(*args, **kwargs) return decorated_function @user_routes.route('/login/status', methods=['GET']) def check_login_status(): try: if 'user_id' in session: connection = get_connection() cursor = connection.cursor(dictionary=True) try: cursor.execute(""" SELECT user_id, user_type FROM Login WHERE user_id = %s """, (session['user_id'],)) user = cursor.fetchone() if user: response = jsonify({ "isLoggedIn": True, "user_id": user['user_id'], "user_type": user['user_type'], "redirect": "/StudentDashboard" if user['user_type'] == '3' else "/TeacherDashboard" if user['user_type'] == '2' else "/Dashboard" }) # Refresh session cookie (optional) response.set_cookie( 'session', session['user_id'], httponly=True, samesite='Lax', secure=False, #Set True in production max_age=86400 ) return response, 200 finally: cursor.close() connection.close() return jsonify({ "isLoggedIn": False, "redirect": "/signin" }), 401 except Exception as e: print(f"Login status check error: {str(e)}") return jsonify({ "isLoggedIn": False, "error": "Failed to check login status" }), 500 @user_routes.route('/upload-timetable', methods=['POST']) def upload_timetable(): if 'file' not in request.files: return jsonify({"error": "No file part in the request"}), 400 file = request.files['file'] if file.filename == '': return jsonify({"error": "No file selected for uploading"}), 400 if file and allowed_file(file.filename): filename = secure_filename(file.filename) file_path = os.path.join(UPLOAD_FOLDER, filename) file.save(file_path) return jsonify({"message": "Timetable uploaded successfully", "file_path": f"/{file_path}"}), 200 else: return jsonify({"error": "File type not allowed"}), 400 @user_routes.route('/get-timetable', methods=['GET']) def get_timetable(): try: timetable_files = [f for f in os.listdir(UPLOAD_FOLDER) if allowed_file(f)] if not timetable_files: return jsonify({"error": "No timetable found"}), 404 # Assuming the latest uploaded timetable is required latest_timetable = max(timetable_files, key=lambda f: os.path.getctime(os.path.join(UPLOAD_FOLDER, f))) file_path = os.path.join(UPLOAD_FOLDER, latest_timetable) return jsonify({"file_path": f"/{file_path}"}), 200 except Exception as e: return jsonify({"error": f"Failed to retrieve timetable: {str(e)}"}), 500 @user_routes.route('/details', methods=['GET']) def get_user_details(): try: student_id = request.args.get('student_id') # If no student_id provided, check session if not student_id: if 'user_id' not in session: return jsonify({"error": "Not authenticated"}), 401 student_id = session.get('user_id') # If user is logged in, check if they're requesting their own profile if 'user_id' in session: if session.get('user_type') != '1' and session.get('user_id') != student_id: return jsonify({"error": "You can only view your own profile"}), 403 connection = get_connection() cursor = connection.cursor(dictionary=True) try: query = """ SELECT s.*, l.user_type, l.email as login_email FROM Students s JOIN Login l ON s.student_id = l.user_id WHERE s.student_id = %s """ cursor.execute(query, (student_id,)) user_details = cursor.fetchone() if not user_details: return jsonify({"error": "Student not found"}), 404 return jsonify({"user_details": user_details}), 200 finally: cursor.close() connection.close() except Exception as e: print(f"Error fetching details: {str(e)}") return jsonify({"error": "Failed to fetch user details"}), 500 @user_routes.route('/teacherdetails', methods=['GET']) def get__details(): try: user_id = request.args.get('student_id') # General user_id # Use session if no user_id is passed if not user_id: if 'user_id' not in session: return jsonify({"error": "Not authenticated"}), 401 user_id = session.get('user_id') session_user_id = session.get('user_id') session_user_type = session.get('user_type') # Allow users to fetch only their own profile if session_user_id != user_id: return jsonify({"error": "You can only view your own profile"}), 403 connection = get_connection() cursor = connection.cursor(dictionary=True) try: # 👨‍🎓 If Student if session_user_type == '1': query = """ SELECT s.*, l.user_type, l.email AS login_email FROM Students s JOIN Login l ON s.student_id = l.user_id WHERE s.student_id = %s """ cursor.execute(query, (user_id,)) user_details = cursor.fetchone() if not user_details: return jsonify({"error": "Student not found"}), 404 return jsonify({"user_details": user_details}), 200 # 👨‍🏫 If Teacher elif session_user_type == '2': query = """ SELECT t.teacher_id, t.name, t.email, t.mobile, t.joining_date, t.subject, t.class, t.gender, t.created_at, l.user_type, l.email AS login_email FROM Teachers t JOIN Login l ON t.teacher_id = l.user_id WHERE t.teacher_id = %s """ cursor.execute(query, (user_id,)) teacher_details = cursor.fetchone() if not teacher_details: return jsonify({"error": "Teacher not found"}), 404 return jsonify({"user_details": teacher_details}), 200 else: return jsonify({"error": "Invalid user type"}), 400 finally: cursor.close() connection.close() except Exception as e: print("Error in /teacherdetails:", str(e)) return jsonify({"error": "Server error"}), 500 @user_routes.route('/admin/marks', methods=['GET', 'POST', 'PUT', 'DELETE']) def manage_marks(): connection = get_connection() cursor = connection.cursor(dictionary=True) try: # ----------------- GET ----------------- if request.method == 'GET': cursor.execute(""" SELECT m.id AS marks_id, s.student_id, s.name AS student_name, s.dob, s.class, s.section, s.gender, s.mobile AS contact_no, m.exam_name, m.math, m.english, m.physics, m.chemistry, m.odia, m.hindi, m.total_marks, m.marks_obtained, CAST(m.percentage AS FLOAT) AS percentage, m.grade FROM marks_entry m JOIN Students s ON m.student_id = s.student_id; """) marks = cursor.fetchall() return jsonify(marks), 200 # ----------------- POST ----------------- elif request.method == 'POST': data = request.json student_id = data.get('student_id') exam_name = data.get('exam_name') class_name = data.get('class') math = int(data.get('math')) english = int(data.get('english')) physics = int(data.get('physics')) chemistry = int(data.get('chemistry')) odia = int(data.get('odia')) hindi = int(data.get('hindi')) total_marks= int(data.get('total_marks')) marks_obtained = math + english + physics + chemistry + odia + hindi percentage = (marks_obtained / total_marks) * 100 # Grade calculation if percentage >= 90: grade = 'A+' elif percentage >= 80: grade = 'A' elif percentage >= 70: grade = 'B' elif percentage >= 60: grade = 'C' elif percentage >= 50: grade = 'D' else: grade = 'F' cursor.execute(""" INSERT INTO marks_entry ( student_id, exam_name, class, math, english, physics, chemistry, odia, hindi, total_marks, marks_obtained, percentage, grade ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, ( student_id, exam_name, class_name, math, english, physics, chemistry, odia, hindi, total_marks, marks_obtained, percentage, grade )) connection.commit() return jsonify({"message": "Marks submitted successfully"}), 201 # ----------------- PUT ----------------- elif request.method == 'PUT': data = request.json entry_id = data.get('id') if not entry_id: return jsonify({"error": "Entry ID is required"}), 400 math = int(data.get('math')) english = int(data.get('english')) physics = int(data.get('physics')) chemistry = int(data.get('chemistry')) odia = int(data.get('odia')) hindi = int(data.get('hindi')) exam_name = data.get('exam_name') class_name = data.get('class') total_marks= int(data.get('total_marks')) # total_marks = 600 marks_obtained = math + english + physics + chemistry + odia + hindi percentage = (marks_obtained / total_marks) * 100 # Grade calculation if percentage >= 90: grade = 'A+' elif percentage >= 80: grade = 'A' elif percentage >= 70: grade = 'B' elif percentage >= 60: grade = 'C' elif percentage >= 50: grade = 'D' else: grade = 'F' cursor.execute("SELECT * FROM marks_entry WHERE id = %s", (entry_id,)) existing = cursor.fetchone() if not existing: return jsonify({"error": "Marks entry not found"}), 404 cursor.execute(""" UPDATE marks_entry SET exam_name=%s, class=%s, math=%s, english=%s, physics=%s, chemistry=%s, odia=%s, hindi=%s, marks_obtained=%s, percentage=%s, grade=%s WHERE id=%s """, ( exam_name, class_name, math, english, physics, chemistry, odia, hindi, marks_obtained, percentage, grade, entry_id )) connection.commit() return jsonify({"message": "Marks updated successfully"}), 200 # ----------------- DELETE ----------------- elif request.method == 'DELETE': entry_id = request.args.get('id') if not entry_id: return jsonify({"error": "Entry ID is required"}), 400 cursor.execute("SELECT * FROM marks_entry WHERE id = %s", (entry_id,)) existing = cursor.fetchone() if not existing: return jsonify({"error": "Marks entry not found"}), 404 cursor.execute("DELETE FROM marks_entry WHERE id = %s", (entry_id,)) connection.commit() return jsonify({"message": "Marks deleted successfully"}), 200 except Exception as e: connection.rollback() return jsonify({"error": str(e)}), 500 finally: cursor.close() connection.close() @user_routes.route('/attendance_s', methods=['GET']) def get_attendance(): conn = get_connection() cursor = conn.cursor(dictionary=True) try: # Check if user is logged in if 'user_id' not in session: return jsonify({"error": "Unauthorized access"}), 401 student_id = session['user_id'] # Fetch attendance only for the logged-in student cursor.execute(""" SELECT * FROM attendance WHERE student_id = %s ORDER BY attendance_date DESC """, (student_id,)) data = cursor.fetchall() return jsonify(data), 200 except Exception as e: print(f"Error fetching attendance: {str(e)}") return jsonify({"error": "Something went wrong"}), 500 finally: cursor.close() conn.close() @user_routes.route('/attendance', methods=['GET', 'POST', 'DELETE', 'PUT']) def manage_attendance(): conn = get_connection() cursor = conn.cursor(dictionary=True) try: # ----------------- GET ----------------- class_name = request.args.get('class') if request.method == 'GET': if class_name: cursor.execute("SELECT student_id, name FROM Students WHERE class = %s", (class_name,)) students = cursor.fetchall() return jsonify({"students": students}), 200 else: cursor.execute("SELECT * FROM attendance ORDER BY attendance_date DESC") data = cursor.fetchall() return jsonify(data), 200 # ----------------- POST ----------------- elif request.method == 'POST': data = request.json student_id = data.get('student_id') student_name = data.get('student_name') class_name = data.get('class') attendance_date = data.get('attendance_date') status = data.get('status') # should be 'Present' or 'Absent' if not all([student_id, student_name, class_name, attendance_date, status]): return jsonify({"error": "All fields are required"}), 400 cursor.execute(""" INSERT INTO attendance (student_id, student_name, class, attendance_date, status) VALUES (%s, %s, %s, %s, %s) """, (student_id, student_name, class_name, attendance_date, status)) conn.commit() return jsonify({"message": "Attendance marked successfully"}), 201 # ----------------- PUT ----------------- elif request.method == 'PUT': data = request.json attendance_id = data.get('id') if not attendance_id: return jsonify({"error": "Attendance ID is required"}), 400 student_id = data.get('student_id') student_name = data.get('student_name') class_name = data.get('class') attendance_date = data.get('attendance_date') status = data.get('status') cursor.execute("SELECT * FROM attendance WHERE id = %s", (attendance_id,)) if not cursor.fetchone(): return jsonify({"error": "Attendance record not found"}), 404 cursor.execute(""" UPDATE attendance SET student_id=%s, student_name=%s, class=%s, attendance_date=%s, status=%s WHERE id=%s """, (student_id, student_name, class_name, attendance_date, status, attendance_id)) conn.commit() return jsonify({"message": "Attendance updated successfully"}), 200 # ----------------- DELETE ----------------- elif request.method == 'DELETE': attendance_id = request.args.get('id') if not attendance_id: return jsonify({"error": "ID is required"}), 400 cursor.execute("SELECT * FROM attendance WHERE id = %s", (attendance_id,)) if not cursor.fetchone(): return jsonify({"error": "Attendance record not found"}), 404 cursor.execute("DELETE FROM attendance WHERE id = %s", (attendance_id,)) conn.commit() return jsonify({"message": "Attendance deleted successfully"}), 200 except Exception as e: conn.rollback() return jsonify({"error": str(e)}), 500 finally: cursor.close() conn.close() @user_routes.route('/attend', methods=['GET']) def attendance(): conn = get_connection() cursor = conn.cursor(dictionary=True) try: # ----------------- GET ----------------- if request.method == 'GET': # Check if user is logged in if 'user_id' not in session: return jsonify({"error": "Unauthorized access"}), 401 student_id = session['user_id'] # Fetch attendance only for the logged-in student cursor.execute(""" SELECT * FROM attendance WHERE student_id = %s ORDER BY attendance_date DESC """, (student_id,)) data = cursor.fetchall() return jsonify(data), 200 # (POST, DELETE, PUT code here if needed...) except Exception as e: print(f"Error in manage_attendance: {str(e)}") return jsonify({"error": "Something went wrong"}), 500 finally: cursor.close() conn.close() @user_routes.route('/admin/marks', methods=['GET']) def get_marks(): try: student_id = request.args.get('student_id') if not student_id: return jsonify({"error": "Student ID is required"}), 400 connection = get_connection() cursor = connection.cursor(dictionary=True) query = """ SELECT m.*, s.student_id, s.name AS student_name, s.class, CAST(m.percentage AS FLOAT) AS percentage FROM marks_entry m JOIN Students s ON m.student_id = s.student_id WHERE s.student_id = %s """ cursor.execute(query, (student_id,)) marks = cursor.fetchall() if not marks: return jsonify([]), 200 return jsonify(marks), 200 except Exception as e: print(f"Error fetching marks: {str(e)}") return jsonify({"error": str(e)}), 500 finally: if 'cursor' in locals(): cursor.close() if 'connection' in locals(): connection.close() @user_routes.route('/student/marks/', methods=['GET']) def get_student_marks(student_id): try: # # Check if user is authorized to view these marks # if 'user_id' in session: # if session.get('user_type') != '1' and session.get('user_id') != student_id: # return jsonify({"error": "Unauthorized to view these marks"}), 403 # else: # return jsonify({"error": "Not authenticated"}), 401 connection = get_connection() cursor = connection.cursor(dictionary=True) try: query = """ SELECT m.id AS marks_id, s.student_id, s.name AS student_name, m.exam_name, s.dob, s.class, s.section, s.gender, s.mobile AS contact_no, m.math, m.english, m.physics, m.chemistry, m.odia, m.hindi, m.total_marks, m.marks_obtained, CAST(m.percentage AS FLOAT) AS percentage FROM marks_entry m JOIN Students s ON m.student_id = s.student_id WHERE s.student_id = %s ORDER BY m.id DESC LIMIT 1 """ cursor.execute(query, (student_id,)) marks = cursor.fetchone() if not marks: return jsonify({"error": "No marks found for this student"}), 404 return jsonify({ "student_marks": [marks], # Wrap in array for consistency "message": "Marks retrieved successfully" }), 200 finally: cursor.close() connection.close() except Exception as e: print(f"Error fetching student marks: {str(e)}") return jsonify({"error": "Failed to fetch student marks"}), 500 @user_routes.route('/dashboard', methods=['GET']) def get_dashboard(): """Fetch dashboard data.""" try: conn = get_connection() if conn is None: return jsonify({"error": "Failed to connect to the database."}), 500 cursor = conn.cursor(dictionary=True) # Fetch total number of teachers cursor.execute("SELECT COUNT(*) AS total_teachers FROM Teachers") total_teachers = cursor.fetchone() # Fetch total number of students cursor.execute("SELECT COUNT(*) AS total_students FROM Students") total_students = cursor.fetchone() # Combine all dashboard data dashboard_data = { "total_teachers": total_teachers['total_teachers'], "total_students": total_students['total_students'] } return jsonify(dashboard_data), 200 except Exception as e: print(f"Error fetching dashboard data: {str(e)}") return jsonify({"error": "Failed to fetch dashboard data"}), 500 finally: cursor.close() conn.close()