from flask import Flask, render_template, request, redirect, url_for, session, flash, send_file, jsonify from database import get_db_connection, hash_password, init_db from datetime import datetime import os import io import csv import uuid import re import hashlib from werkzeug.utils import secure_filename # PDF Generation imports from reportlab.lib.pagesizes import A4, landscape from reportlab.lib.units import mm from reportlab.lib import colors from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, Image from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.lib.enums import TA_CENTER, TA_LEFT from reportlab.pdfbase import pdfmetrics from reportlab.pdfbase.ttfonts import TTFont app = Flask(__name__) app.secret_key = 'drr_management_secret_key_2024' # Separate folders for different upload types app.config['REPORT_UPLOAD_FOLDER'] = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'static/uploads/report_images/') app.config['PROFILE_UPLOAD_FOLDER'] = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'static/uploads/profile_pics/') app.config['MAX_CONTENT_LENGTH'] = 10 * 1024 * 1024 # 10MB max file size # Print upload folder info for debugging print(f"Report images folder: {app.config['REPORT_UPLOAD_FOLDER']}") print(f"Profile pictures folder: {app.config['PROFILE_UPLOAD_FOLDER']}") # Force create the folders if not os.path.exists(app.config['REPORT_UPLOAD_FOLDER']): os.makedirs(app.config['REPORT_UPLOAD_FOLDER'], exist_ok=True) print("Created report_images folder") if not os.path.exists(app.config['PROFILE_UPLOAD_FOLDER']): os.makedirs(app.config['PROFILE_UPLOAD_FOLDER'], exist_ok=True) print("Created profile_pics folder") # Allowed extensions for profile pictures and report images ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif'} def allowed_file(filename): return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS def save_profile_picture(file): """Save profile picture and return filename""" if file and allowed_file(file.filename): ext = file.filename.rsplit('.', 1)[1].lower() filename = str(uuid.uuid4()) + '.' + ext filepath = os.path.join(app.config['PROFILE_UPLOAD_FOLDER'], filename) file.save(filepath) print(f"Profile picture saved: {filename}") return filename return 'default.png' def save_report_images(files, user_id): """Save multiple report images and return filenames as comma-separated string Naming format: {username}_{nid}_{date}_{imagenumber}.{ext} """ saved_files = [] if files: # Get user details for naming conn, cursor = get_db() cursor.execute("SELECT username, nid FROM users WHERE id=%s", (user_id,)) user = cursor.fetchone() cursor.close() conn.close() if user: username = user['username'] nid = user['nid'] current_date = datetime.now().strftime('%Y%m%d') # Format: 20241212 for idx, file in enumerate(files): if file and file.filename and allowed_file(file.filename): ext = file.filename.rsplit('.', 1)[1].lower() # Format: username_nid_date_1.jpg filename = f"{username}_{nid}_{current_date}_{idx+1}.{ext}" filepath = os.path.join(app.config['REPORT_UPLOAD_FOLDER'], filename) # Handle duplicate filenames counter = 1 while os.path.exists(filepath): filename = f"{username}_{nid}_{current_date}_{idx+1}_v{counter}.{ext}" filepath = os.path.join(app.config['REPORT_UPLOAD_FOLDER'], filename) counter += 1 file.save(filepath) saved_files.append(filename) print(f"Report image saved as: {filename}") return ','.join(saved_files) def get_db(): """Get database connection with dictionary cursor""" conn = get_db_connection() if conn: cursor = conn.cursor(dictionary=True) return conn, cursor return None, None # ============ CERTIFICATE PDF GENERATOR ============ def generate_certificate(username, full_name, training_title, completion_date, training_id): """Generate PDF certificate for completed training - single page landscape""" buffer = io.BytesIO() # Create PDF document - Landscape orientation doc = SimpleDocTemplate(buffer, pagesize=landscape(A4), rightMargin=25*mm, leftMargin=25*mm, topMargin=15*mm, bottomMargin=15*mm) # ============ CANVAS CALLBACK FOR BORDER ============ def add_page_border(canvas, doc): canvas.saveState() page_width, page_height = landscape(A4) # Outer decorative border canvas.setStrokeColor(colors.HexColor('#8B0000')) canvas.setLineWidth(4) canvas.rect(12, 12, page_width - 24, page_height - 24) # Inner decorative border canvas.setStrokeColor(colors.HexColor('#8B0000')) canvas.setLineWidth(1.5) canvas.rect(18, 18, page_width - 36, page_height - 36) # Top decorative line canvas.setLineWidth(2) canvas.setStrokeColor(colors.HexColor('#8B0000')) canvas.line(40, page_height - 35, page_width - 40, page_height - 35) # Bottom decorative line canvas.line(40, 35, page_width - 40, 35) canvas.restoreState() # Helper to find signature images def find_signature(filename_base): for ext in ['.png', '.jpg', '.jpeg']: path = os.path.join('static/uploads', filename_base + ext) if os.path.exists(path): return path return None story = [] styles = getSampleStyleSheet() # ============ CUSTOM STYLES (compact) ============ title_style = ParagraphStyle( 'CustomTitle', parent=styles['Heading1'], fontSize=26, leading=30, textColor=colors.HexColor('#8B0000'), alignment=TA_CENTER, spaceAfter=5, spaceBefore=0, ) cert_style = ParagraphStyle( 'Certificate', parent=styles['Heading1'], fontSize=15, leading=20, textColor=colors.black, alignment=TA_CENTER, spaceAfter=5, spaceBefore=0, ) name_style = ParagraphStyle( 'Name', parent=styles['Heading1'], fontSize=22, leading=28, textColor=colors.HexColor('#006400'), alignment=TA_CENTER, spaceAfter=5, spaceBefore=0, ) body_style = ParagraphStyle( 'Body', parent=styles['Normal'], fontSize=11, leading=14, alignment=TA_CENTER, spaceAfter=2, spaceBefore=0, ) small_style = ParagraphStyle( 'Small', parent=styles['Normal'], fontSize=9, leading=12, alignment=TA_CENTER, spaceAfter=0, spaceBefore=0, textColor=colors.HexColor('#555555'), ) # ============ BUILD STORY (compact layout) ============ story.append(Spacer(1, 5)) # Organization Logo - CENTERED using a table, WIDER size for banner logos try: logo_path = os.path.join('static/uploads/Prottyashi_Logo.png') if os.path.exists(logo_path): # Changed from 90x90 to 200x100 to fit wide organization logos nicely logo = Image(logo_path, width=300, height=100) logo_table = Table([[logo]], colWidths=[doc.width]) logo_table.setStyle(TableStyle([ ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'), ('TOPPADDING', (0, 0), (-1, -1), 0), ('BOTTOMPADDING', (0, 0), (-1, -1), 0), ])) story.append(logo_table) story.append(Spacer(1, 5)) except: pass # Title story.append(Paragraph("CERTIFICATE OF COMPLETION", title_style)) story.append(Spacer(1, 8)) # Decorative line line_table = Table([['']], colWidths=[300], rowHeights=[2]) line_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, -1), colors.HexColor('#8B0000')), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('TOPPADDING', (0, 0), (-1, -1), 0), ('BOTTOMPADDING', (0, 0), (-1, -1), 0), ])) line_wrapper = Table([[line_table]], colWidths=[doc.width]) line_wrapper.setStyle(TableStyle([ ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('TOPPADDING', (0, 0), (-1, -1), 0), ('BOTTOMPADDING', (0, 0), (-1, -1), 0), ])) story.append(line_wrapper) story.append(Spacer(1, 10)) # Certificate text story.append(Paragraph("This certificate is proudly presented to", body_style)) story.append(Spacer(1, 5)) # Recipient name story.append(Paragraph(f"{full_name.upper()}", name_style)) story.append(Spacer(1, 5)) story.append(Paragraph("for successfully completing the training course on", body_style)) story.append(Spacer(1, 5)) # Course title story.append(Paragraph(f"ยซ {training_title} ยป", cert_style)) story.append(Spacer(1, 10)) # Date story.append(Paragraph(f"Date of Completion: {completion_date.strftime('%B %d, %Y')}", body_style)) story.append(Spacer(1, 20)) # ============ SIGNATURES WITH IMAGES ============ # Load signature images if they exist sig_coordinator_path = find_signature('signature_coordinator') sig_head_path = find_signature('signature_head') sig_coordinator = "" sig_head = "" try: if sig_coordinator_path: sig_coordinator = Image(sig_coordinator_path, width=120, height=50) except: sig_coordinator = "" try: if sig_head_path: sig_head = Image(sig_head_path, width=120, height=50) except: sig_head = "" # Construct signature table (Image on top, line in middle, title at bottom) signature_table_data = [ [sig_coordinator, sig_head], # Row 1: Images ["_________________________", "_________________________"], # Row 2: Lines ["DRR Training Coordinator", "Organization Head"], # Row 3: Titles ] sig_table = Table(signature_table_data, colWidths=[220, 220]) sig_table.setStyle(TableStyle([ ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('VALIGN', (0, 0), (-1, -1), 'BOTTOM'), ('FONTNAME', (0, 0), (-1, -1), 'Helvetica'), ('FONTSIZE', (0, 0), (-1, -1), 10), ('TOPPADDING', (0, 0), (-1, -1), 2), ('BOTTOMPADDING', (0, 0), (-1, -1), 2), ])) # Center signature table on page sig_wrapper = Table([[sig_table]], colWidths=[doc.width]) sig_wrapper.setStyle(TableStyle([ ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('TOPPADDING', (0, 0), (-1, -1), 0), ('BOTTOMPADDING', (0, 0), (-1, -1), 0), ])) story.append(sig_wrapper) story.append(Spacer(1, 10)) # Certificate number cert_number = hashlib.md5(f"{username}{training_title}{completion_date}".encode()).hexdigest()[:12].upper() story.append(Paragraph(f"Certificate No: {cert_number}", small_style)) # Build PDF with border callback doc.build(story, onFirstPage=add_page_border, onLaterPages=add_page_border) buffer.seek(0) return buffer # ============ CONTEXT PROCESSOR FOR UNREAD COUNT ============ @app.context_processor def inject_unread_count(): if 'user_id' in session: conn, cursor = get_db() cursor.execute("SELECT COUNT(*) as count FROM messages WHERE recipient_id=%s AND is_read=0", (session['user_id'],)) result = cursor.fetchone() cursor.close() conn.close() unread_count = result['count'] if result else 0 return {'unread_count': unread_count} return {'unread_count': 0} # ============ TRAINING CERTIFICATE ROUTE ============ @app.route('/generate_training_certificate/') def generate_training_certificate(training_id): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() # Check if user completed this training cursor.execute(""" SELECT ut.*, t.title as training_title, u.full_name, u.username FROM user_trainings ut JOIN trainings t ON ut.training_id = t.id JOIN users u ON ut.user_id = u.id WHERE ut.user_id=%s AND ut.training_id=%s AND ut.status='completed' """, (session['user_id'], training_id)) record = cursor.fetchone() cursor.close() conn.close() if not record: flash('You have not completed this training yet!', 'danger') return redirect(url_for('training')) # Generate certificate completion_date = record['completed_date'] if record['completed_date'] else datetime.now() pdf_buffer = generate_certificate( record['username'], record['full_name'], record['training_title'], completion_date, training_id ) filename = f"certificate_{record['username']}_{training_id}.pdf" return send_file( pdf_buffer, mimetype='application/pdf', as_attachment=True, download_name=filename ) # ============ DETAILED TRAINING ROUTES ============ @app.route('/training_module/') def training_module_detail(training_id): if 'user_id' not in session: return redirect(url_for('login')) conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # Get training details cursor.execute("SELECT * FROM trainings WHERE id=%s", (training_id,)) training = cursor.fetchone() if not training: flash('Training not found!', 'danger') return redirect(url_for('training')) # Get modules for this training cursor.execute("SELECT * FROM training_modules WHERE training_id=%s ORDER BY module_number", (training_id,)) modules = cursor.fetchall() # Get all lessons for this training cursor.execute(""" SELECT tl.*, tm.id as module_table_id, tm.module_number FROM training_lessons tl JOIN training_modules tm ON tl.module_id = tm.id WHERE tm.training_id=%s ORDER BY tm.module_number, tl.id """, (training_id,)) all_lessons = cursor.fetchall() # Get completed lesson IDs for this user cursor.execute(""" SELECT lesson_id FROM user_lesson_progress WHERE user_id=%s AND completed=1 """, (session['user_id'],)) completed = cursor.fetchall() completed_lesson_ids = set(c['lesson_id'] for c in completed) # Group lessons by module_id lessons_by_module = {} for lesson in all_lessons: mid = lesson['module_id'] if mid not in lessons_by_module: lessons_by_module[mid] = [] lessons_by_module[mid].append(lesson) # Calculate module completion status module_status = {} all_modules_completed = True for module in modules: mid = module['id'] module_lessons = lessons_by_module.get(mid, []) total = len(module_lessons) completed_count = len([l for l in module_lessons if l['id'] in completed_lesson_ids]) is_complete = total > 0 and completed_count == total module_status[mid] = { 'total_lessons': total, 'completed_lessons': completed_count, 'is_complete': is_complete, 'lessons': module_lessons } if not is_complete: all_modules_completed = False # Determine which modules are accessible (sequential unlock) # Module 1 is always accessible # Module N is accessible only if Module N-1 is complete accessible_modules = set() for i, module in enumerate(modules): if i == 0: # First module always accessible accessible_modules.add(module['id']) else: # Check if previous module is complete prev_module = modules[i - 1] if module_status[prev_module['id']]['is_complete']: accessible_modules.add(module['id']) # Get enrollment status cursor.execute("SELECT * FROM user_trainings WHERE user_id=%s AND training_id=%s", (session['user_id'], training_id)) enrollment = cursor.fetchone() # Overall progress total_lessons = len(all_lessons) completed_lessons_count = len([l for l in all_lessons if l['id'] in completed_lesson_ids]) except Exception as e: print(f"Error: {e}") import traceback traceback.print_exc() flash('Error loading training details', 'danger') return redirect(url_for('training')) finally: cursor.close() conn.close() return render_template('training_module_detail.html', training=training, modules=modules, module_status=module_status, lessons_by_module=lessons_by_module, accessible_modules=accessible_modules, all_modules_completed=all_modules_completed, enrollment=enrollment, completed_lessons=completed_lessons_count, total_lessons=total_lessons, completed_lesson_ids=completed_lesson_ids) @app.route('/training_lesson//') def training_lesson(module_id, lesson_id): if 'user_id' not in session: return redirect(url_for('login')) conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: # Get lesson details cursor.execute("SELECT * FROM training_lessons WHERE id=%s", (lesson_id,)) lesson = cursor.fetchone() if not lesson: flash('Lesson not found!', 'danger') return redirect(url_for('training')) # Get module info cursor.execute("SELECT * FROM training_modules WHERE id=%s", (module_id,)) module = cursor.fetchone() if not module: flash('Module not found!', 'danger') return redirect(url_for('training')) training_id = module['training_id'] # ===== SEQUENTIAL VALIDATION ===== # Check if this module is accessible (previous modules must be completed) cursor.execute("SELECT * FROM training_modules WHERE training_id=%s ORDER BY module_number", (training_id,)) all_modules = cursor.fetchall() module_accessible = False for i, m in enumerate(all_modules): if m['id'] == module_id: if i == 0: # First module always accessible module_accessible = True else: # Check if previous module is fully completed prev_module_id = all_modules[i - 1]['id'] cursor.execute("SELECT COUNT(*) as total FROM training_lessons WHERE module_id=%s", (prev_module_id,)) prev_total = cursor.fetchone()['total'] cursor.execute(""" SELECT COUNT(*) as completed FROM user_lesson_progress ulp JOIN training_lessons tl ON ulp.lesson_id = tl.id WHERE tl.module_id=%s AND ulp.user_id=%s AND ulp.completed=1 """, (prev_module_id, session['user_id'])) prev_completed = cursor.fetchone()['completed'] module_accessible = (prev_total > 0 and prev_completed >= prev_total) break if not module_accessible: flash('๐Ÿ”’ Please complete the previous module first before accessing this one!', 'warning') return redirect(url_for('training_module_detail', training_id=training_id)) # Check if already completed cursor.execute("SELECT * FROM user_lesson_progress WHERE user_id=%s AND lesson_id=%s", (session['user_id'], lesson_id)) is_completed = cursor.fetchone() is not None # Get all lessons in this module to find next/prev cursor.execute("SELECT id FROM training_lessons WHERE module_id=%s ORDER BY id", (module_id,)) module_lesson_ids = [row['id'] for row in cursor.fetchall()] # Find next lesson in same module next_lesson = None if lesson_id in module_lesson_ids: current_index = module_lesson_ids.index(lesson_id) if current_index + 1 < len(module_lesson_ids): next_lesson = module_lesson_ids[current_index + 1] # If no next lesson in this module, find first lesson of next module next_module_first_lesson = None if next_lesson is None: current_module_index = None for i, m in enumerate(all_modules): if m['id'] == module_id: current_module_index = i break if current_module_index is not None and current_module_index + 1 < len(all_modules): next_module_id = all_modules[current_module_index + 1]['id'] cursor.execute("SELECT id FROM training_lessons WHERE module_id=%s ORDER BY id LIMIT 1", (next_module_id,)) result = cursor.fetchone() if result: next_module_first_lesson = result['id'] # Get previous lesson prev_lesson = None if lesson_id in module_lesson_ids: current_index = module_lesson_ids.index(lesson_id) if current_index > 0: prev_lesson = module_lesson_ids[current_index - 1] finally: cursor.close() conn.close() return render_template('training_lesson.html', lesson=lesson, module=module, training_id=training_id, lesson_id=lesson_id, module_id=module_id, is_completed=is_completed, next_lesson=next_lesson, next_module_first_lesson=next_module_first_lesson, prev_lesson=prev_lesson) @app.route('/mark_lesson_complete/') def mark_lesson_complete(lesson_id): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() # Check if already marked cursor.execute("SELECT * FROM user_lesson_progress WHERE user_id=%s AND lesson_id=%s", (session['user_id'], lesson_id)) existing = cursor.fetchone() if not existing: cursor.execute(""" INSERT INTO user_lesson_progress (user_id, lesson_id, completed, completed_date) VALUES (%s, %s, 1, NOW()) """, (session['user_id'], lesson_id)) conn.commit() # Get module_id and training_id cursor.execute(""" SELECT tm.id as module_id, tm.training_id, tm.module_number FROM training_lessons tl JOIN training_modules tm ON tl.module_id = tm.id WHERE tl.id=%s """, (lesson_id,)) result = cursor.fetchone() if not result: cursor.close() conn.close() return redirect(url_for('training')) module_id = result['module_id'] training_id = result['training_id'] # Find next lesson in the same module cursor.execute("SELECT id FROM training_lessons WHERE module_id=%s AND id > %s ORDER BY id LIMIT 1", (module_id, lesson_id)) next_lesson = cursor.fetchone() cursor.close() conn.close() if next_lesson: # Go to next lesson in same module flash('โœ… Lesson completed! Moving to next lesson.', 'success') return redirect(url_for('training_lesson', module_id=module_id, lesson_id=next_lesson['id'])) else: # No more lessons in this module - go back to module detail flash('๐ŸŽ‰ Module completed! Well done!', 'success') return redirect(url_for('training_module_detail', training_id=training_id)) @app.route('/training_quiz/', methods=['GET', 'POST']) def training_quiz(training_id): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() # Get training title cursor.execute("SELECT title FROM trainings WHERE id=%s", (training_id,)) training = cursor.fetchone() # ===== VALIDATION: Check all modules are completed ===== cursor.execute("SELECT * FROM training_modules WHERE training_id=%s ORDER BY module_number", (training_id,)) modules = cursor.fetchall() all_complete = True for module in modules: cursor.execute("SELECT COUNT(*) as total FROM training_lessons WHERE module_id=%s", (module['id'],)) total = cursor.fetchone()['total'] cursor.execute(""" SELECT COUNT(*) as completed FROM user_lesson_progress ulp JOIN training_lessons tl ON ulp.lesson_id = tl.id WHERE tl.module_id=%s AND ulp.user_id=%s AND ulp.completed=1 """, (module['id'], session['user_id'])) completed_count = cursor.fetchone()['completed'] if total > 0 and completed_count < total: all_complete = False break if not all_complete: flash('๐Ÿ”’ You must complete all modules before taking the final quiz!', 'warning') cursor.close() conn.close() return redirect(url_for('training_module_detail', training_id=training_id)) # Get all quiz questions cursor.execute("SELECT * FROM training_quizzes WHERE training_id=%s", (training_id,)) questions = cursor.fetchall() if request.method == 'POST': score = 0 total = len(questions) # Process answers for q in questions: answer = request.form.get(f'q_{q["id"]}') if answer and answer == q['correct_answer']: score += q['points'] cursor.execute(""" INSERT INTO user_quiz_answers (user_id, quiz_id, selected_answer, is_correct) VALUES (%s, %s, %s, 1) """, (session['user_id'], q['id'], answer)) else: cursor.execute(""" INSERT INTO user_quiz_answers (user_id, quiz_id, selected_answer, is_correct) VALUES (%s, %s, %s, 0) """, (session['user_id'], q['id'], answer if answer else 'None')) passing_score = 70 # 70% to pass percentage = (score / total) * 100 if total > 0 else 0 if percentage >= passing_score: cursor.execute(""" UPDATE user_trainings SET status='completed', completed_date=NOW() WHERE user_id=%s AND training_id=%s """, (session['user_id'], training_id)) conn.commit() flash(f'๐ŸŽ‰ Congratulations! You passed the quiz with {percentage:.0f}%! Certificate has been generated.', 'success') else: flash(f'โŒ You scored {percentage:.0f}%. You need {passing_score}% to pass. Please review the modules and try again.', 'danger') cursor.close() conn.close() return redirect(url_for('training_module_detail', training_id=training_id)) cursor.close() conn.close() return render_template('training_quiz.html', questions=questions, training_id=training_id, training=training) # ============ MARK MESSAGES AS READ ROUTES ============ @app.route('/mark_single_message_read', methods=['POST']) def mark_single_message_read(): if 'user_id' not in session: return jsonify({'success': False, 'error': 'Not logged in'}), 401 try: data = request.json message_id = data.get('message_id') if not message_id: return jsonify({'success': False, 'error': 'Message ID required'}), 400 conn, cursor = get_db() # Verify the message belongs to the logged-in user cursor.execute("SELECT id FROM messages WHERE id=%s AND recipient_id=%s", (message_id, session['user_id'])) message = cursor.fetchone() if not message: cursor.close() conn.close() return jsonify({'success': False, 'error': 'Message not found'}), 404 # Mark as read cursor.execute("UPDATE messages SET is_read=1 WHERE id=%s", (message_id,)) conn.commit() # Get remaining unread count cursor.execute("SELECT COUNT(*) as count FROM messages WHERE recipient_id=%s AND is_read=0", (session['user_id'],)) result = cursor.fetchone() remaining = result['count'] if result else 0 cursor.close() conn.close() return jsonify({'success': True, 'remaining': remaining}) except Exception as e: print(f"Error marking message as read: {e}") return jsonify({'success': False, 'error': str(e)}), 500 @app.route('/mark_messages_read', methods=['POST']) def mark_messages_read(): if 'user_id' not in session: return jsonify({'success': False, 'error': 'Not logged in'}), 401 try: conn, cursor = get_db() # Get count before update cursor.execute("SELECT COUNT(*) as count FROM messages WHERE recipient_id=%s AND is_read=0", (session['user_id'],)) result = cursor.fetchone() affected_count = result['count'] if result else 0 # Mark all as read cursor.execute("UPDATE messages SET is_read=1 WHERE recipient_id=%s AND is_read=0", (session['user_id'],)) conn.commit() cursor.close() conn.close() return jsonify({'success': True, 'count': affected_count}) except Exception as e: print(f"Error marking all messages as read: {e}") return jsonify({'success': False, 'error': str(e)}), 500 # ============ REGULAR ROUTES ============ @app.route('/') def index(): return render_template('index.html') @app.route('/register', methods=['GET', 'POST']) def register(): if request.method == 'POST': username = request.form['username'] password = hash_password(request.form['password']) full_name = request.form['full_name'] email = request.form['email'] phone = request.form['phone'] nid = request.form['nid'] district = request.form.get('district', '') upazila = request.form.get('upazila', '') latitude = request.form.get('latitude') longitude = request.form.get('longitude') # Server-side validation if '@' not in email: flash('Email must contain @ symbol', 'danger') return redirect(url_for('register')) if not re.match(r'^0[0-9]{10}$', phone): flash('Mobile number must be 11 digits starting with 0 (e.g., 01873525000)', 'danger') return redirect(url_for('register')) if not re.match(r'^[0-9]{10}$|^[0-9]{13}$|^[0-9]{17}$', nid): flash('NID must be 10, 13, or 17 digits only', 'danger') return redirect(url_for('register')) # Handle profile picture upload profile_pic = 'default.png' if 'profile_pic' in request.files: file = request.files['profile_pic'] if file and file.filename: profile_pic = save_profile_picture(file) conn, cursor = get_db() try: cursor.execute(""" INSERT INTO users (username, password, full_name, email, phone, nid, district, upazila, latitude, longitude, profile_pic) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, (username, password, full_name, email, phone, nid, district, upazila, latitude, longitude, profile_pic)) conn.commit() flash('Registration successful! Please login.', 'success') return redirect(url_for('login')) except Exception as e: if 'Duplicate' in str(e): flash('Username or NID already exists!', 'danger') else: flash(f'Registration error: {str(e)}', 'danger') finally: cursor.close() conn.close() return render_template('register.html') @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'POST': username = request.form['username'] password = hash_password(request.form['password']) conn, cursor = get_db() cursor.execute("SELECT * FROM users WHERE username=%s AND password=%s", (username, password)) user = cursor.fetchone() cursor.close() conn.close() if user: session.clear() session['user_id'] = user['id'] session['username'] = user['username'] session['role'] = user['role'] session['profile_pic'] = user.get('profile_pic', 'default.png') session.permanent = True if user['role'] == 'admin': flash(f'Welcome Admin {user["full_name"]}!', 'success') return redirect(url_for('admin_dashboard')) else: flash(f'Welcome {user["full_name"]}!', 'success') return redirect(url_for('volunteer_dashboard')) else: flash('Invalid username or password!', 'danger') return render_template('login.html') @app.route('/logout') def logout(): session.clear() flash('Logged out successfully!', 'success') return redirect(url_for('index')) @app.route('/volunteer/dashboard') def volunteer_dashboard(): if 'user_id' not in session or session['role'] != 'volunteer': return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT * FROM users WHERE id=%s", (session['user_id'],)) user = cursor.fetchone() cursor.execute("SELECT * FROM reports WHERE user_id=%s ORDER BY report_date DESC LIMIT 5", (session['user_id'],)) recent_reports = cursor.fetchall() cursor.execute("SELECT COUNT(*) as count FROM user_trainings WHERE user_id=%s", (session['user_id'],)) enrolled_count = cursor.fetchone()['count'] cursor.close() conn.close() return render_template('volunteer_dashboard.html', user=user, reports=recent_reports, enrolled_trainings_count=enrolled_count) @app.route('/volunteer/submit_report', methods=['GET', 'POST']) def submit_report(): if 'user_id' not in session: return redirect(url_for('login')) if request.method == 'POST': conn, cursor = get_db() # Handle image uploads report_images = '' if 'report_images' in request.files: files = request.files.getlist('report_images') valid_files = [f for f in files if f and f.filename] if valid_files: report_images = save_report_images(valid_files, session['user_id']) # Ensure reports table has images column try: cursor.execute("ALTER TABLE reports ADD COLUMN images TEXT") conn.commit() except: pass cursor.execute(""" INSERT INTO reports (user_id, disaster_type, title, description, location, latitude, longitude, affected_people, action_taken, images) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, (session['user_id'], request.form['disaster_type'], request.form['title'], request.form['description'], request.form['location'], request.form.get('latitude'), request.form.get('longitude'), request.form['affected_people'], request.form['action_taken'], report_images)) conn.commit() cursor.close() conn.close() if report_images: image_count = len(report_images.split(',')) if report_images else 0 flash(f'Report submitted successfully with {image_count} image(s)!', 'success') else: flash('Report submitted successfully!', 'success') return redirect(url_for('volunteer_dashboard')) return render_template('submit_report.html') @app.route('/volunteer/my_reports') def my_reports(): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT * FROM reports WHERE user_id=%s ORDER BY report_date DESC", (session['user_id'],)) reports = cursor.fetchall() cursor.close() conn.close() return render_template('my_reports.html', reports=reports) @app.route('/volunteer/download_report') def download_report(): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT * FROM reports WHERE user_id=%s ORDER BY report_date DESC", (session['user_id'],)) reports = cursor.fetchall() cursor.close() conn.close() output = io.StringIO() writer = csv.writer(output) writer.writerow(['ID', 'Disaster Type', 'Title', 'Description', 'Location', 'Latitude', 'Longitude', 'Affected People', 'Action Taken', 'Images', 'Report Date']) for r in reports: writer.writerow([r['id'], r['disaster_type'], r['title'], r['description'], r['location'], r['latitude'], r['longitude'], r['affected_people'], r['action_taken'], r.get('images', ''), r['report_date']]) output.seek(0) return send_file(io.BytesIO(output.getvalue().encode('utf-8')), mimetype='text/csv', as_attachment=True, download_name=f'reports_{session["username"]}.csv') # ============ TRAINING ROUTES ============ @app.route('/training') def training(): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT * FROM trainings") trainings = cursor.fetchall() cursor.execute("SELECT training_id FROM user_trainings WHERE user_id=%s AND status='enrolled'", (session['user_id'],)) enrolled = cursor.fetchall() cursor.execute("SELECT training_id FROM user_trainings WHERE user_id=%s AND status='completed'", (session['user_id'],)) completed = cursor.fetchall() cursor.close() conn.close() enrolled_ids = [e['training_id'] for e in enrolled] completed_ids = [c['training_id'] for c in completed] return render_template('training.html', trainings=trainings, enrolled_ids=enrolled_ids, completed_ids=completed_ids) @app.route('/training/enroll/') def enroll_training(training_id): if 'user_id' not in session: return redirect(url_for('login')) conn = get_db_connection() cursor = conn.cursor(dictionary=True) try: cursor.execute("SELECT * FROM user_trainings WHERE user_id=%s AND training_id=%s", (session['user_id'], training_id)) existing = cursor.fetchone() if not existing: cursor.execute(""" INSERT INTO user_trainings (user_id, training_id, status) VALUES (%s, %s, 'enrolled') """, (session['user_id'], training_id)) conn.commit() flash('Enrolled successfully! Start learning now.', 'success') else: flash('Already enrolled!', 'info') except Exception as e: print(f"Error in enroll_training: {e}") flash('Error enrolling in training', 'danger') cursor.close() conn.close() return redirect(url_for('training')) cursor.close() conn.close() return redirect(url_for('training_module_detail', training_id=training_id)) def execute_query(cursor, query, params=None, fetch_one=False, fetch_all=False): """Helper function to execute queries safely""" try: if params: cursor.execute(query, params) else: cursor.execute(query) if fetch_one: return cursor.fetchone() elif fetch_all: return cursor.fetchall() else: return True except Exception as e: print(f"Query error: {e}") return None # ============ EDIT AND DELETE REPORT ROUTES ============ @app.route('/edit_report/', methods=['GET', 'POST']) def edit_report(report_id): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() # Get the report cursor.execute("SELECT * FROM reports WHERE id=%s", (report_id,)) report = cursor.fetchone() if not report: flash('Report not found!', 'danger') if session['role'] == 'admin': return redirect(url_for('admin_reports')) else: return redirect(url_for('my_reports')) # Check permission if session['role'] != 'admin' and report['user_id'] != session['user_id']: flash('You do not have permission to edit this report!', 'danger') if session['role'] == 'admin': return redirect(url_for('admin_reports')) else: return redirect(url_for('my_reports')) if request.method == 'POST': # Get current images current_images = report.get('images', '') current_images_list = current_images.split(',') if current_images else [] # Handle image deletion delete_images = request.form.get('delete_images', '') if delete_images: delete_list = delete_images.split(',') for img in delete_list: if img in current_images_list: current_images_list.remove(img) img_path = os.path.join(app.config['REPORT_UPLOAD_FOLDER'], img) if os.path.exists(img_path): os.remove(img_path) # Handle new image uploads with proper naming new_images = [] if 'report_images' in request.files: files = request.files.getlist('report_images') # Get user details for naming conn2, cursor2 = get_db() cursor2.execute("SELECT username, nid FROM users WHERE id=%s", (session['user_id'],)) user = cursor2.fetchone() cursor2.close() conn2.close() if user: username = user['username'] nid = user['nid'] current_date = datetime.now().strftime('%Y%m%d') # Start numbering from after existing images base_number = len(current_images_list) + 1 for idx, file in enumerate(files): if file and file.filename and allowed_file(file.filename): ext = file.filename.rsplit('.', 1)[1].lower() image_number = base_number + idx filename = f"{username}_{nid}_{current_date}_{image_number}.{ext}" filepath = os.path.join(app.config['REPORT_UPLOAD_FOLDER'], filename) # Handle duplicate filenames counter = 1 while os.path.exists(filepath): filename = f"{username}_{nid}_{current_date}_{image_number}_v{counter}.{ext}" filepath = os.path.join(app.config['REPORT_UPLOAD_FOLDER'], filename) counter += 1 file.save(filepath) new_images.append(filename) print(f"New image saved as: {filename}") # Combine images all_images = current_images_list + new_images images_string = ','.join(all_images) # Update database cursor.execute(""" UPDATE reports SET disaster_type=%s, title=%s, description=%s, location=%s, latitude=%s, longitude=%s, affected_people=%s, action_taken=%s, images=%s WHERE id=%s """, (request.form['disaster_type'], request.form['title'], request.form['description'], request.form['location'], request.form.get('latitude'), request.form.get('longitude'), request.form['affected_people'], request.form['action_taken'], images_string, report_id)) conn.commit() flash('Report updated successfully!', 'success') cursor.close() conn.close() if session['role'] == 'admin': return redirect(url_for('admin_reports')) else: return redirect(url_for('my_reports')) cursor.close() conn.close() return render_template('edit_report.html', report=report) @app.route('/delete_report/') def delete_report(report_id): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() # Get the report cursor.execute("SELECT * FROM reports WHERE id=%s", (report_id,)) report = cursor.fetchone() if not report: flash('Report not found!', 'danger') if session['role'] == 'admin': return redirect(url_for('admin_reports')) else: return redirect(url_for('my_reports')) # Check permission if session['role'] != 'admin' and report['user_id'] != session['user_id']: flash('You do not have permission to delete this report!', 'danger') if session['role'] == 'admin': return redirect(url_for('admin_reports')) else: return redirect(url_for('my_reports')) # Delete associated images if report.get('images'): images = report['images'].split(',') for img in images: img_path = os.path.join(app.config['REPORT_UPLOAD_FOLDER'], img) if os.path.exists(img_path): os.remove(img_path) # Delete the report cursor.execute("DELETE FROM reports WHERE id=%s", (report_id,)) conn.commit() cursor.close() conn.close() flash('Report deleted successfully!', 'success') if session['role'] == 'admin': return redirect(url_for('admin_reports')) else: return redirect(url_for('my_reports')) # ============ INBOX ============ @app.route('/inbox') def inbox(): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT * FROM messages WHERE recipient_id=%s ORDER BY sent_date DESC", (session['user_id'],)) messages = cursor.fetchall() cursor.close() conn.close() return render_template('inbox.html', messages=messages) # ============ PROFILE ROUTES ============ @app.route('/profile', methods=['GET', 'POST']) def profile(): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() if request.method == 'POST': email = request.form.get('email') phone = request.form.get('phone') if '@' not in email: flash('Email must contain @ symbol', 'danger') return redirect(url_for('profile')) if not re.match(r'^0[0-9]{10}$', phone): flash('Mobile number must be 11 digits starting with 0 (e.g., 01873525000)', 'danger') return redirect(url_for('profile')) if 'profile_pic' in request.files: file = request.files['profile_pic'] if file and file.filename: cursor.execute("SELECT profile_pic FROM users WHERE id=%s", (session['user_id'],)) old_pic = cursor.fetchone()['profile_pic'] if old_pic and old_pic != 'default.png': old_path = os.path.join(app.config['PROFILE_UPLOAD_FOLDER'], old_pic) if os.path.exists(old_path): os.remove(old_path) new_pic = save_profile_picture(file) cursor.execute("UPDATE users SET email=%s, phone=%s, profile_pic=%s WHERE id=%s", (email, phone, new_pic, session['user_id'])) session['profile_pic'] = new_pic else: cursor.execute("UPDATE users SET email=%s, phone=%s WHERE id=%s", (email, phone, session['user_id'])) else: cursor.execute("UPDATE users SET email=%s, phone=%s WHERE id=%s", (email, phone, session['user_id'])) conn.commit() flash('Profile updated successfully!', 'success') return redirect(url_for('profile')) cursor.execute("SELECT * FROM users WHERE id=%s", (session['user_id'],)) user = cursor.fetchone() cursor.execute("SELECT COUNT(*) as count FROM reports WHERE user_id=%s", (session['user_id'],)) total_reports = cursor.fetchone()['count'] cursor.execute("SELECT COUNT(*) as count FROM user_trainings WHERE user_id=%s AND status='completed'", (session['user_id'],)) completed_trainings = cursor.fetchone()['count'] cursor.execute("SELECT COUNT(*) as count FROM user_trainings WHERE user_id=%s AND certificate IS NOT NULL", (session['user_id'],)) certificates_earned = cursor.fetchone()['count'] cursor.close() conn.close() return render_template('profile.html', user=user, total_reports=total_reports, completed_trainings=completed_trainings, certificates_earned=certificates_earned) @app.route('/change_password', methods=['GET', 'POST']) def change_password(): if 'user_id' not in session: return redirect(url_for('login')) if request.method == 'POST': current = hash_password(request.form['current_password']) new = request.form['new_password'] confirm = request.form['confirm_password'] if new != confirm: flash('New passwords do not match!', 'danger') return redirect(url_for('change_password')) if len(new) < 6: flash('Password must be at least 6 characters!', 'danger') return redirect(url_for('change_password')) conn, cursor = get_db() cursor.execute("SELECT password FROM users WHERE id=%s", (session['user_id'],)) user = cursor.fetchone() if user['password'] != current: flash('Current password is incorrect!', 'danger') else: cursor.execute("UPDATE users SET password=%s WHERE id=%s", (hash_password(new), session['user_id'])) conn.commit() flash('Password changed successfully!', 'success') return redirect(url_for('profile')) cursor.close() conn.close() return render_template('change_password.html') @app.route('/certificates') def certificates(): if 'user_id' not in session: return redirect(url_for('login')) conn, cursor = get_db() cursor.execute(""" SELECT ut.*, t.title as training_title FROM user_trainings ut JOIN trainings t ON ut.training_id = t.id WHERE ut.user_id=%s AND ut.status='completed' """, (session['user_id'],)) certificates = cursor.fetchall() cursor.close() conn.close() return render_template('certificate_view.html', certificates=certificates) @app.route('/download_certificate/') def download_certificate(cert_id): if 'user_id' not in session: return redirect(url_for('login')) flash('Certificate download feature coming soon!', 'info') return redirect(url_for('certificates')) # ============ ADMIN ROUTES ============ @app.route('/admin') def admin_dashboard(): if 'user_id' not in session or session['role'] != 'admin': return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT COUNT(*) as count FROM users WHERE role='volunteer'") total_users = cursor.fetchone()['count'] cursor.execute("SELECT COUNT(*) as count FROM reports") total_reports = cursor.fetchone()['count'] cursor.execute("SELECT COUNT(*) as count FROM user_trainings WHERE status IN ('enrolled', 'in_progress')") total_trainings = cursor.fetchone()['count'] cursor.execute("SELECT COUNT(*) as count FROM user_trainings WHERE status='completed'") completed_trainings = cursor.fetchone()['count'] cursor.close() conn.close() return render_template('admin_dashboard.html', total_users=total_users, total_reports=total_reports, total_trainings=total_trainings, completed_trainings=completed_trainings) @app.route('/admin/reports') def admin_reports(): if 'user_id' not in session or session['role'] != 'admin': return redirect(url_for('login')) conn, cursor = get_db() cursor.execute(""" SELECT reports.*, users.full_name, users.district, users.upazila FROM reports JOIN users ON reports.user_id = users.id ORDER BY reports.report_date DESC """) reports = cursor.fetchall() cursor.close() conn.close() return render_template('admin_reports.html', reports=reports) @app.route('/admin/users_map') def admin_users_map(): if 'user_id' not in session or session['role'] != 'admin': return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT full_name, district, upazila, latitude, longitude FROM users WHERE role='volunteer' AND latitude IS NOT NULL") users = cursor.fetchall() cursor.close() conn.close() return render_template('admin_users_map.html', users=users) @app.route('/admin/send_alert', methods=['POST']) def send_alert(): if 'user_id' not in session or session['role'] != 'admin': return jsonify({'error': 'Unauthorized'}), 403 data = request.json zone = data.get('zone') subject = data.get('subject') message = data.get('message') conn, cursor = get_db() if zone == 'all': cursor.execute("SELECT id FROM users WHERE role='volunteer'") users = cursor.fetchall() else: cursor.execute("SELECT id FROM users WHERE district=%s AND role='volunteer'", (zone,)) users = cursor.fetchall() for user in users: cursor.execute(""" INSERT INTO messages (sender_id, recipient_id, zone, subject, message) VALUES (%s, %s, %s, %s, %s) """, (session['user_id'], user['id'], zone, subject, message)) conn.commit() cursor.close() conn.close() return jsonify({'success': True, 'count': len(users)}) @app.route('/admin/training_stats') def admin_training_stats(): if 'user_id' not in session or session['role'] != 'admin': return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT * FROM trainings") trainings = cursor.fetchall() stats = [] for t in trainings: cursor.execute("SELECT COUNT(*) as count FROM user_trainings WHERE training_id=%s AND status IN ('enrolled', 'in_progress')", (t['id'],)) enrolled = cursor.fetchone()['count'] cursor.execute("SELECT COUNT(*) as count FROM user_trainings WHERE training_id=%s AND status='completed'", (t['id'],)) completed = cursor.fetchone()['count'] stats.append({'training': t, 'enrolled': enrolled, 'completed': completed}) cursor.close() conn.close() return render_template('admin_training_stats.html', stats=stats) @app.route('/admin/volunteers') def admin_volunteers(): if 'user_id' not in session or session['role'] != 'admin': return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT * FROM users WHERE role='volunteer' ORDER BY registered_date DESC") volunteers = cursor.fetchall() cursor.close() conn.close() return render_template('admin_volunteers.html', volunteers=volunteers) @app.route('/admin/export_volunteers') def admin_export_volunteers(): if 'user_id' not in session or session['role'] != 'admin': return redirect(url_for('login')) conn, cursor = get_db() cursor.execute("SELECT id, full_name, username, email, phone, district, upazila, registered_date FROM users WHERE role='volunteer'") volunteers = cursor.fetchall() cursor.close() conn.close() output = io.StringIO() writer = csv.writer(output) writer.writerow(['ID', 'Full Name', 'Username', 'Email', 'Phone', 'District', 'Upazila', 'Registration Date']) for v in volunteers: writer.writerow([v['id'], v['full_name'], v['username'], v['email'], v['phone'], v['district'], v['upazila'], v['registered_date']]) output.seek(0) return send_file(io.BytesIO(output.getvalue().encode('utf-8')), mimetype='text/csv', as_attachment=True, download_name='volunteers_list.csv') if __name__ == '__main__': init_db() # Add images column to reports table if not exists conn, cursor = get_db() if conn and cursor: try: cursor.execute("ALTER TABLE reports ADD COLUMN images TEXT") conn.commit() print("Added images column to reports table") except Exception as e: print(f"Images column already exists or error: {e}") cursor.close() conn.close() app.run(debug=True, host='0.0.0.0', port=5000)