from flask import Flask, redirect, url_for, render_template, request, flash, session, render_template_string, abort, current_app, Response import mysql.connector from mysql.connector import pooling import re import hashlib import random import string import os import openpyxl from datetime import datetime from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter from io import BytesIO from werkzeug.security import generate_password_hash, check_password_hash from flask_mail import Mail, Message # Database connection pool db_pool = pooling.MySQLConnectionPool( pool_name="suppliers_pool", pool_size=5, pool_reset_session=True, host=os.environ.get('DB_HOST', 'localhost'), database=os.environ.get('DB_NAME', 'citcypqm_suppliers_listing'), user=os.environ.get('DB_USER', 'citcypqm_steven'), password=os.environ.get('DB_PASSWORD', 'domino+3-3') ) def get_db_connection(): """Get a database connection from the pool.""" return db_pool.get_connection() def close_connection(connection, cursor): """Safely close cursor and connection.""" if cursor: try: cursor.close() except: pass if connection: try: connection.close() except: pass app = Flask(__name__) app.secret_key = "sparta" app.config['MAIL_SERVER'] = "smtp.gmail.com" app.config['MAIL_PORT'] = 587 app.config['MAIL_USERNAME'] = 'stevenchipunza1998@gmail.com' app.config['MAIL_PASSWORD'] = 'rfcd szjd pqsq anop' app.config['MAIL_USE_TLS'] = True app.config['MAIL_USE_SSL'] = False app.config['MAIL_DEBUG'] = True mail = Mail(app) @app.route('/', methods=['GET']) def index(): connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute(""" SELECT t.task_id, t.name AS task_name, t.description, t.expiry_date, j.name AS job_name FROM tasks t JOIN jobs j ON t.job_id = j.job_id WHERE t.expiry_date >= CURDATE() OR t.expiry_date IS NULL ORDER BY t.created_at DESC LIMIT 10 """) tasks = cursor.fetchall() return render_template('index.html', tasks=tasks) except mysql.connector.Error as err: print(f"Database error in index: {err}") return render_template('index.html', tasks=[]) finally: close_connection(connection, cursor) @app.route('/login', methods=['GET']) def login(): return render_template("login.html") @app.route('/register_form', methods=['GET']) def register_form(): return render_template('register_user.html') def register_admin_form(): return render_template("register_admin.html") @app.route('/create_job_form', methods=["GET"]) def create_job_form(): return render_template('add_job_listing.html') @app.route('/edit_job') def edit_job(): return render_template('supplier_edit_job.html') @app.route('/admin_home') def admin_home(): return render_template("admin_main.html") @app.route('/supplier_home') def supplier_home(): return render_template("supplier_main.html") @app.route('/login_form', methods=["GET", "POST"]) def login_form(): msg = None connection = None cursor = None if request.method == 'POST': try: username = request.form['username'] password = request.form['password'] connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT * FROM company WHERE email = %s", (username,)) record = cursor.fetchone() temp_password_used = False if record: if record['temp_password'] and check_password_hash(record['temp_password'], password): temp_password_used = True elif check_password_hash(record['password'], password): pass else: msg = "Invalid username or password" if not msg: session['loggedin'] = True session['email'] = record['email'] session['comp_name'] = record['name'] session['role'] = record['role'] close_connection(connection, cursor) if temp_password_used: return redirect(url_for('force_password_change', email=record['email'])) elif record['role'] == 'supplier': return render_template('supplier_main.html') else: return render_template('index.html') else: cursor.execute("SELECT * FROM admins WHERE email = %s", (username,)) admin_record = cursor.fetchone() if admin_record: temp_password_used = False if admin_record['temp_password'] and check_password_hash(admin_record['temp_password'], password): temp_password_used = True elif check_password_hash(admin_record['password'], password): pass else: msg = "Invalid username or password" if not msg: session['loggedin'] = True session['email'] = admin_record['email'] session['role'] = 'admin' close_connection(connection, cursor) if temp_password_used: return redirect(url_for('force_password_change', email=admin_record['email'])) return redirect(url_for('admin_dashboard')) else: msg = "Invalid username or password" except mysql.connector.Error as err: msg = f"Error: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template("login.html", msg=msg) @app.route('/logout') def logout(): session.clear() return render_template('index.html') @app.route('/force-password-change/', methods=['GET', 'POST']) def force_password_change(email): if 'loggedin' not in session: return redirect(url_for('login')) msg = None success = False connection = None cursor = None if request.method == 'POST': new_password = request.form['new_password'] confirm_password = request.form['confirm_password'] if new_password != confirm_password: msg = "Passwords do not match." elif len(new_password) < 6: msg = "Password must be at least 6 characters long." else: try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT * FROM company WHERE email = %s AND temp_password IS NOT NULL", (email,)) company = cursor.fetchone() if company: hashed_password = generate_password_hash(new_password) cursor.execute(""" UPDATE company SET password = %s, temp_password = NULL WHERE email = %s """, (hashed_password, email)) connection.commit() success = True msg = "Password changed successfully! You can now use your new password." else: cursor.execute("SELECT * FROM admins WHERE email = %s AND temp_password IS NOT NULL", (email,)) admin = cursor.fetchone() if admin: hashed_password = generate_password_hash(new_password) cursor.execute(""" UPDATE admins SET password = %s, temp_password = NULL WHERE email = %s """, (hashed_password, email)) connection.commit() success = True msg = "Password changed successfully! You can now use your new password." else: msg = "No temporary password found. Please login again." except mysql.connector.Error as err: msg = f"Database error: {str(err)}" if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template('force_password_change.html', msg=msg, success=success, email=email) @app.route('/forgot-password', methods=['GET', 'POST']) def forgot_password(): msg = None connection = None cursor = None if request.method == 'POST': email = request.form['email'].strip() if not re.match(r"[^@]+@[^@]+\.[^@]+", email): msg = "Please enter a valid email address." else: try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT comp_id, name, email FROM company WHERE email = %s", (email,)) company = cursor.fetchone() if not company: cursor.execute("SELECT email FROM admins WHERE email = %s", (email,)) admin = cursor.fetchone() if company or admin: temp_code = ''.join(random.choices(string.digits, k=6)) hashed_temp_code = generate_password_hash(temp_code) if company: cursor.execute("UPDATE company SET temp_password = %s WHERE email = %s", (hashed_temp_code, email)) else: cursor.execute("UPDATE admins SET temp_password = %s WHERE email = %s", (hashed_temp_code, email)) connection.commit() close_connection(connection, cursor) try: subject = "Password Reset Code - Suppliers List" body = f""" Dear User, Your temporary login code: {temp_code} Use this code to login. You'll be prompted to set a new password. Best regards, Suppliers List Team """ message = Message(sender=app.config['MAIL_USERNAME'], recipients=[email], subject=subject, body=body) mail.send(message) flash(f"Reset code sent to {email}! Check your inbox.", 'success') return redirect(url_for('login')) except Exception as mail_err: flash("Code generated but email failed. Contact support.", 'warning') return redirect(url_for('login')) else: msg = "No account found with that email address." except mysql.connector.Error as err: msg = f"Database error: {str(err)}" if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template('forgot_password.html', msg=msg) @app.route('/reset-password', methods=['GET', 'POST']) def reset_password(): if request.method == 'POST': email = request.form['email'] new_password = request.form['new_password'] confirm_password = request.form['confirm_password'] connection = None cursor = None if new_password != confirm_password: return render_template('reset_password.html', msg="Passwords do not match.", email=email) if len(new_password) < 6: return render_template('reset_password.html', msg="Password must be at least 6 characters.", email=email) try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT * FROM company WHERE email = %s AND temp_password IS NOT NULL", (email,)) company = cursor.fetchone() if company and company['temp_password'] is not None: hashed_password = generate_password_hash(new_password) cursor.execute("UPDATE company SET password = %s, temp_password = NULL WHERE email = %s", (hashed_password, email)) connection.commit() close_connection(connection, cursor) return render_template('reset_password.html', msg="Password reset successfully! You can now login with your new password.", success=True) else: close_connection(connection, cursor) return render_template('reset_password.html', msg="Invalid or expired reset code. Please request a new one.", email=email) except mysql.connector.Error as err: if connection: connection.rollback() close_connection(connection, cursor) return render_template('reset_password.html', msg=f"Error: {str(err)}", email=email) return render_template('reset_password.html') @app.route('/register', methods=['GET', 'POST']) def register(): msg = None connection = None cursor = None if request.method == 'POST': print("POST request received") try: name = request.form.get('name') address = request.form.get('address') email = request.form.get('email') phone = request.form.get('phone') password = request.form.get('password') confirm_password = request.form.get('confirmPassword') company_document = request.files.get('company_document') if password != confirm_password: msg = "Passwords do not match." return render_template('register_user.html', msg=msg) if company_document: if not company_document.filename.endswith('.pdf'): msg = "Please upload a valid PDF file." return render_template('register_user.html', msg=msg) company_document.seek(0, 2) file_size = company_document.tell() company_document.seek(0) max_size = 25 * 1024 * 1024 if file_size > max_size: msg = "File size must not exceed 25 MB." return render_template('register_user.html', msg=msg) document_data = company_document.read() else: msg = "Please upload a company document." return render_template('register_user.html', msg=msg) connection = get_db_connection() cursor = connection.cursor() cursor.execute("SELECT email FROM company WHERE email = %s", (email,)) if cursor.fetchone(): msg = "Email already registered." close_connection(connection, cursor) return render_template('register_user.html', msg=msg) hashed_password = generate_password_hash(password) cursor.execute("INSERT INTO company (name, address, phone_number, email, company_document, password, temp_password, role) VALUES (%s, %s, %s, %s, %s, %s, NULL, %s)", (name, address, phone, email, document_data, hashed_password, 'supplier')) connection.commit() close_connection(connection, cursor) msg = "Registration successful!" return render_template('login.html', msg=msg) except Exception as err: msg = f"Error: {err}" print(f"Exception: {err}") if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template('register_user.html', msg=msg) @app.route('/register_admin', methods=['GET', 'POST']) def register_admin(): msg = None connection = None cursor = None if request.method == 'POST': try: email = request.form['email'] password = request.form['password'] confirm_password = request.form['confirmPassword'] if password != confirm_password: msg = "Passwords do not match." return render_template('register_admin.html', msg=msg) connection = get_db_connection() cursor = connection.cursor() cursor.execute("SELECT email FROM admins WHERE email = %s", (email,)) if cursor.fetchone(): msg = "Email already registered as admin." close_connection(connection, cursor) return render_template('register_admin.html', msg=msg) hashed_password = generate_password_hash(password) cursor.execute("INSERT INTO admins (email, password, temp_password) VALUES (%s, %s, NULL)", (email, hashed_password)) connection.commit() close_connection(connection, cursor) msg = "Admin registration successful!" return render_template('login.html', msg=msg) except mysql.connector.Error as err: msg = f"Error: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template('register_admin.html', msg=msg) @app.route('/add_job', methods=['POST']) def add_job(): msg = None connection = None cursor = None if request.method == 'POST': try: name = request.form['name'] job_description = request.form['job_description'] status = request.form['status'] closing_date = request.form['closing_date'] connection = get_db_connection() cursor = connection.cursor() cursor.execute("INSERT INTO jobs (name, job_description, status, job_closing_date) VALUES (%s, %s, %s, %s)", (name, job_description, status, closing_date)) connection.commit() cursor.execute("SELECT email FROM company") companies = cursor.fetchall() close_connection(connection, cursor) if companies: for company in companies: try: msg_body = f""" Dear Supplier, A new job listing has been added to our system! Job Name: {name} Description: {job_description} Closing Date: {closing_date} If you are interested, please log in to your account and apply for this job listing. Best regards, Construction Industry Pension Fund """ msg = Message( subject=f"New Job Listing: {name}", sender=app.config['MAIL_USERNAME'], recipients=[company['email']], body=msg_body ) mail.send(msg) except Exception as e: print(f"Failed to send email to {company['email']}: {e}") msg = "Job listing added successfully! Notifications sent to all companies." return render_template('admin_main.html', msg=msg) except mysql.connector.Error as err: msg = f"Error: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template('admin_main.html', msg=msg) @app.route('/supplier/apply', methods=['GET', 'POST']) def supplier_apply(): if 'loggedin' not in session or session.get('role') != 'supplier': return redirect(url_for('login')) msg = None connection = None cursor = None if request.method == 'POST': jobs_id = request.form['jobs_id'] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT comp_id FROM company WHERE email = %s", (session['email'],)) company = cursor.fetchone() if not company: msg = "Company not found." else: company_id = company['comp_id'] cursor.execute("SELECT company_job_id FROM company_jobs WHERE company_id = %s AND jobs_id = %s", (company_id, jobs_id)) existing = cursor.fetchone() if existing: msg = "You have already applied for this job." else: cursor.execute("INSERT INTO company_jobs (company_id, jobs_id, date_application) VALUES (%s, %s, CURDATE())", (company_id, jobs_id)) connection.commit() msg = "Application submitted successfully!" except mysql.connector.Error as err: msg = f"Error: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT job_id, name, job_description, job_closing_date FROM jobs WHERE status = 'Open' AND job_closing_date >= CURDATE()") open_jobs = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching jobs: {err}") open_jobs = [] finally: close_connection(connection, cursor) if not open_jobs: msg = "No open job listings available at this time." return render_template('supplier_apply.html', open_jobs=open_jobs, msg=msg) @app.route('/admin/job_listings') def admin_job_listings(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) page = int(request.args.get('page', 1)) per_page = 10 offset = (page - 1) * per_page connection = None cursor = None job_listings = [] total_pages = 1 try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT COUNT(*) as total FROM jobs") total_jobs = cursor.fetchone()['total'] total_pages = (total_jobs + per_page - 1) // per_page cursor.execute(""" SELECT j.job_id, j.name, j.job_description, j.status, j.job_closing_date, COUNT(cj.company_job_id) as applicant_count FROM jobs j LEFT JOIN company_jobs cj ON j.job_id = cj.jobs_id GROUP BY j.job_id ORDER BY j.job_id DESC LIMIT %s OFFSET %s """, (per_page, offset)) job_listings = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching job listings: {err}") finally: close_connection(connection, cursor) return render_template('admin_job_listings.html', job_listings=job_listings, page=page, total_pages=total_pages) @app.route('/admin/update_status/', methods=['POST']) def update_job_status(job_id): if 'loggedin' not in session or session.get('role') != 'admin': return {'error': 'Unauthorized'}, 403 data = request.get_json() new_status = data.get('status') if new_status not in ['Open', 'Closed']: return {'error': 'Invalid status'}, 400 connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute("UPDATE jobs SET status = %s WHERE job_id = %s", (new_status, job_id)) connection.commit() return {'message': 'Status updated successfully'}, 200 except mysql.connector.Error as err: if connection: connection.rollback() return {'error': str(err)}, 500 finally: close_connection(connection, cursor) @app.route('/admin/update_job/', methods=['POST']) def update_job(job_id): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) msg = None connection = None cursor = None try: name = request.form['name'] job_description = request.form['job_description'] status = request.form['status'] closing_date = request.form['closing_date'] connection = get_db_connection() cursor = connection.cursor() cursor.execute(""" UPDATE jobs SET name = %s, job_description = %s, status = %s, job_closing_date = %s WHERE job_id = %s """, (name, job_description, status, closing_date, job_id)) connection.commit() msg = "Job updated successfully!" except mysql.connector.Error as err: msg = f"Error: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) return redirect(url_for('admin_job_listings', msg=msg)) @app.route('/admin/preview_company_document/') def preview_company_document(company_id): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute("SELECT company_document FROM company WHERE comp_id = %s", (company_id,)) result = cursor.fetchone() except mysql.connector.Error as err: print(f"Error fetching document: {err}") abort(404) finally: close_connection(connection, cursor) if not result or not result[0]: abort(404) return Response(result[0], mimetype='application/pdf', headers={'Content-Disposition': 'inline; filename=company_document.pdf'}) @app.route('/admin/download_company_document/') def download_company_document(company_id): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT company_document, name FROM company WHERE comp_id = %s", (company_id,)) result = cursor.fetchone() except mysql.connector.Error as err: print(f"Error fetching document: {err}") abort(404) finally: close_connection(connection, cursor) if not result or not result['company_document']: abort(404) company_name = result['name'].replace(' ', '_').replace('/', '_') filename = f"{company_name}_document.pdf" return Response(result['company_document'], mimetype='application/pdf', headers={'Content-Disposition': f'attachment; filename={filename}'}) @app.route('/supplier/edit_profile', methods=['GET', 'POST']) def edit_profile(): if 'loggedin' not in session or session.get('role') != 'supplier': return redirect(url_for('login')) msg = None connection = None cursor = None user = None if request.method == 'POST': try: name = request.form['name'] address = request.form['address'] email = request.form['email'] phone = request.form['phone'] company_document = request.files.get('company_document') connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT comp_id FROM company WHERE email = %s AND comp_id != (SELECT comp_id FROM company WHERE email = %s)", (email, session['email'])) if cursor.fetchone(): msg = "Email already in use by another account." else: update_fields = "name = %s, address = %s, phone_number = %s, email = %s" update_values = [name, address, phone, email] if company_document and company_document.filename.endswith('.pdf'): company_document.seek(0, 2) file_size = company_document.tell() company_document.seek(0) if file_size > 25 * 1024 * 1024: msg = "File size must not exceed 25 MB." else: document_data = company_document.read() update_fields += ", company_document = %s" update_values.append(document_data) if not msg: update_values.append(session['email']) cursor.execute(f"UPDATE company SET {update_fields} WHERE email = %s", update_values) connection.commit() msg = "Profile updated successfully!" session['email'] = email session['comp_name'] = name except mysql.connector.Error as err: msg = f"Error: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT name, address, email, phone_number FROM company WHERE email = %s", (session['email'],)) user = cursor.fetchone() except mysql.connector.Error as err: print(f"Error fetching user: {err}") finally: close_connection(connection, cursor) return render_template('edit_profile.html', user=user, msg=msg) @app.route('/admin/suppliers', methods=['GET', 'POST']) def admin_suppliers(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) selected_company_id = request.args.get('company_id', type=int) selected_job_id = request.args.get('job_id', type=int) page = int(request.args.get('page', 1)) per_page = 10 offset = (page - 1) * per_page suppliers = [] applications = [] job_applications = [] all_jobs = [] total_pages = 1 connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT job_id, name FROM jobs ORDER BY name ASC") all_jobs = cursor.fetchall() if selected_job_id: cursor.execute(""" SELECT COUNT(DISTINCT c.comp_id) as total FROM company c JOIN company_jobs cj ON c.comp_id = cj.company_id WHERE cj.jobs_id = %s """, (selected_job_id,)) total_companies = cursor.fetchone()['total'] else: cursor.execute("SELECT COUNT(*) as total FROM company") total_companies = cursor.fetchone()['total'] total_pages = (total_companies + per_page - 1) // per_page if selected_job_id: cursor.execute(""" SELECT DISTINCT c.comp_id, c.name, c.email, c.phone_number, c.address, COUNT(cj.company_job_id) AS application_count FROM company c JOIN company_jobs cj ON c.comp_id = cj.company_id WHERE cj.jobs_id = %s GROUP BY c.comp_id ORDER BY c.name ASC LIMIT %s OFFSET %s """, (selected_job_id, per_page, offset)) else: cursor.execute(""" SELECT c.comp_id, c.name, c.email, c.phone_number, c.address, COUNT(cj.company_job_id) AS application_count FROM company c LEFT JOIN company_jobs cj ON c.comp_id = cj.company_id GROUP BY c.comp_id ORDER BY c.name ASC LIMIT %s OFFSET %s """, (per_page, offset)) suppliers = cursor.fetchall() if selected_company_id: cursor.execute(""" SELECT cj.company_job_id, cj.date_application, COALESCE(cj.job_status, 'Pending') AS job_status, j.job_id, j.name AS job_name, j.job_description, j.status AS job_status_original FROM company_jobs cj JOIN jobs j ON cj.jobs_id = j.job_id WHERE cj.company_id = %s ORDER BY cj.date_application DESC """, (selected_company_id,)) applications = cursor.fetchall() elif selected_job_id: cursor.execute(""" SELECT cj.company_job_id, cj.date_application, COALESCE(cj.job_status, 'Pending') AS job_status, c.comp_id AS company_id, c.name AS company_name, j.job_id, j.name AS job_name, j.job_description, j.status AS job_status_original FROM company_jobs cj JOIN company c ON cj.company_id = c.comp_id JOIN jobs j ON cj.jobs_id = j.job_id WHERE cj.jobs_id = %s ORDER BY cj.date_application DESC """, (selected_job_id,)) job_applications = cursor.fetchall() except Exception as e: print(f"Database error in admin_suppliers: {e}") finally: close_connection(connection, cursor) return render_template('admin_suppliers.html', suppliers=suppliers, applications=applications, job_applications=job_applications, selected_company_id=selected_company_id, selected_job_id=selected_job_id, all_jobs=all_jobs, page=page, total_pages=total_pages) @app.route('/admin/create_task', methods=['GET', 'POST']) def admin_create_task(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None jobs = [] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT job_id, name FROM jobs WHERE job_closing_date >= CURDATE() ORDER BY name ASC") jobs = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching jobs: {err}") finally: close_connection(connection, cursor) msg = None if request.method == 'POST': job_id = request.form['job_id'] name = request.form['name'] description = request.form['description'] expiry_date = request.form['expiry_date'] if not job_id or not name or not description or not expiry_date: msg = "All fields are required." return render_template('admin_create_task.html', jobs=jobs, msg=msg) connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute(""" INSERT INTO tasks (job_id, name, description, expiry_date) VALUES (%s, %s, %s, %s) """, (job_id, name, description, expiry_date)) connection.commit() cursor.execute(""" SELECT c.email FROM company_jobs cj JOIN company c ON cj.company_id = c.comp_id WHERE cj.jobs_id = %s AND cj.job_status = 'Accepted' """, (job_id,)) accepted_companies = cursor.fetchall() close_connection(connection, cursor) if accepted_companies: for company in accepted_companies: try: msg_body = f""" Dear Supplier, A new task has been added under the job you applied for! Task Name: {name} Description: {description} Expiry Date: {expiry_date} If you are interested, please log in to your account and apply and attach quotation for this task. Best regards, Construction Industry Pension Fund Team """ msg = Message( subject=f"New Task Available: {name}", sender=app.config['MAIL_USERNAME'], recipients=[company['email']], body=msg_body ) mail.send(msg) except Exception as e: print(f"Failed to send email to {company['email']}: {e}") msg = "Task created successfully! Notifications sent to accepted companies." except mysql.connector.Error as err: msg = f"Error creating task: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template('admin_create_task.html', jobs=jobs, msg=msg) return render_template('admin_create_task.html', jobs=jobs) @app.route('/admin/job//tasks', methods=['GET', 'POST']) def admin_job_tasks(job_id): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None job = None tasks = [] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT * FROM jobs WHERE job_id = %s", (job_id,)) job = cursor.fetchone() if not job: abort(404) cursor.execute("SELECT * FROM tasks WHERE job_id = %s ORDER BY created_at DESC", (job_id,)) tasks = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching job tasks: {err}") finally: close_connection(connection, cursor) msg = None if request.method == 'POST': name = request.form.get('name') description = request.form.get('description') expiry_date = request.form.get('expiry_date') if name and description and expiry_date: connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute("INSERT INTO tasks (job_id, name, description, expiry_date) VALUES (%s, %s, %s, %s)", (job_id, name, description, expiry_date)) connection.commit() msg = "Task created successfully!" except mysql.connector.Error as err: msg = f"Error creating task: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) else: msg = "All fields are required for creation." return redirect(url_for('admin_job_tasks', job_id=job_id, msg=msg)) return render_template('admin_job_tasks.html', job=job, tasks=tasks, msg=msg) @app.route('/admin/update_task/', methods=['POST']) def update_task(task_id): if 'loggedin' not in session or session.get('role') != 'admin': return {'error': 'Unauthorized'}, 403 data = request.get_json() name = data.get('name') description = data.get('description') expiry_date = data.get('expiry_date') if not name or not description or not expiry_date: return {'error': 'All fields are required'}, 400 connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute(""" UPDATE tasks SET name = %s, description = %s, expiry_date = %s WHERE task_id = %s """, (name, description, expiry_date, task_id)) connection.commit() return {'message': 'Task updated successfully'}, 200 except mysql.connector.Error as err: if connection: connection.rollback() return {'error': str(err)}, 500 finally: close_connection(connection, cursor) @app.route('/admin/update_application_status/', methods=['POST']) def update_application_status(application_id): if 'loggedin' not in session or session.get('role') != 'admin': return {'error': 'Unauthorized'}, 403 data = request.get_json() new_status = data.get('status') if new_status not in ['Pending', 'Accepted', 'Declined']: return {'error': 'Invalid status'}, 400 connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT job_status FROM company_jobs WHERE company_job_id = %s", (application_id,)) current_status_result = cursor.fetchone() if not current_status_result: return {'error': 'Application not found'}, 404 current_status = current_status_result['job_status'] status_changed = current_status != new_status send_email = status_changed and new_status in ['Accepted', 'Declined'] cursor.execute("UPDATE company_jobs SET job_status = %s WHERE company_job_id = %s", (new_status, application_id)) connection.commit() if send_email: cursor.execute(""" SELECT cj.jobs_id, cj.company_id, j.name AS job_name, j.job_description, c.name AS company_name, c.email AS company_email FROM company_jobs cj JOIN jobs j ON cj.jobs_id = j.job_id JOIN company c ON cj.company_id = c.comp_id WHERE cj.company_job_id = %s """, (application_id,)) app_details = cursor.fetchone() if app_details: company_email = app_details['company_email'] job_name = app_details['job_name'] if new_status == 'Accepted': try: msg_body = f""" Dear {app_details['company_name']}, Congratulations! Your application for the job "{job_name}" has been ACCEPTED. You have been selected for this opportunity. Please contact lkashanu@cit.co.zw for further instructions. Best regards, Construction Industry Pension Fund Team """ msg = Message( subject=f"Congratulations! Job Application ACCEPTED - {job_name}", sender=app.config['MAIL_USERNAME'], recipients=[company_email], body=msg_body ) mail.send(msg) except Exception as e: print(f"Failed to send acceptance email: {e}") elif new_status == 'Declined': try: msg_body = f""" Dear {app_details['company_name']}, Thank you for applying for the job "{job_name}". Unfortunately, your application has been DECLINED at this time. We appreciate your interest and encourage you to apply for other opportunities in the future. Best regards, Construction Industry Pension Fund Team """ msg = Message( subject=f"Job Application DECLINED - {job_name}", sender=app.config['MAIL_USERNAME'], recipients=[company_email], body=msg_body ) mail.send(msg) except Exception as e: print(f"Failed to send decline email: {e}") return {'message': f'Status updated to {new_status} successfully'}, 200 except mysql.connector.Error as err: if connection: connection.rollback() return {'error': str(err)}, 500 finally: close_connection(connection, cursor) @app.route('/supplier/tasks') def supplier_tasks(): if 'loggedin' not in session or session.get('role') != 'supplier': return redirect(url_for('login')) connection = None cursor = None tasks = [] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute(""" SELECT t.task_id, t.name, t.description, t.created_at, j.name AS job_name FROM tasks t JOIN jobs j ON t.job_id = j.job_id JOIN company_jobs cj ON j.job_id = cj.jobs_id WHERE cj.company_id = (SELECT comp_id FROM company WHERE email = %s) AND cj.job_status = 'Accepted' ORDER BY j.name, t.created_at DESC """, (session['email'],)) tasks = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching tasks: {err}") finally: close_connection(connection, cursor) return render_template('supplier_tasks.html', tasks=tasks) @app.route('/admin/manage_tasks') def admin_manage_tasks(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None tasks = [] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute(""" SELECT t.task_id, t.name, t.description, t.expiry_date, t.created_at, j.name AS job_name, j.job_id FROM tasks t JOIN jobs j ON t.job_id = j.job_id ORDER BY j.name, t.created_at DESC """) tasks = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching tasks: {err}") finally: close_connection(connection, cursor) return render_template('admin_manage_tasks.html', tasks=tasks) @app.route('/admin/update_task_application/', methods=['POST']) def update_task_application(application_id): if 'loggedin' not in session or session.get('role') != 'admin': return {'error': 'Unauthorized'}, 403 data = request.get_json() new_status = data.get('status') if new_status not in ['Pending', 'Accepted', 'Declined']: return {'error': 'Invalid status'}, 400 connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT task_id FROM task_applications WHERE application_id = %s", (application_id,)) app = cursor.fetchone() if not app: return {'error': 'Application not found'}, 404 task_id = app['task_id'] if new_status == 'Accepted': cursor.execute("SELECT application_id FROM task_applications WHERE task_id = %s AND status = 'Accepted' AND application_id != %s", (task_id, application_id)) if cursor.fetchone(): return {'error': 'Another supplier is already accepted for this task'}, 400 cursor.execute("UPDATE task_applications SET status = %s WHERE application_id = %s", (new_status, application_id)) if new_status == 'Accepted': cursor.execute("UPDATE task_applications SET status = 'Declined' WHERE task_id = %s AND application_id != %s", (task_id, application_id)) connection.commit() cursor.execute(""" SELECT t.name AS task_name, j.name AS job_name, c.name AS company_name, c.email AS company_email FROM task_applications ta JOIN tasks t ON ta.task_id = t.task_id JOIN jobs j ON t.job_id = j.job_id JOIN company c ON ta.supplier_id = c.comp_id WHERE ta.application_id = %s """, (application_id,)) app_details = cursor.fetchone() if new_status == 'Accepted' and app_details: try: msg_body = f""" Dear {app_details['company_name']}, Congratulations! Your application for the task "{app_details['task_name']}" under the job "{app_details['job_name']}" has been accepted. You have been assigned this task. Please contact lkashanu@cit.co.zw for more information. Best regards, Construction Industry Pension Fund """ msg = Message( subject=f"Congratulations! Task Assignment: {app_details['task_name']}", sender=app.config['MAIL_USERNAME'], recipients=[app_details['company_email']], body=msg_body ) mail.send(msg) except Exception as e: print(f"Failed to send email: {e}") return {'message': 'Status updated successfully'}, 200 except mysql.connector.Error as err: if connection: connection.rollback() return {'error': str(err)}, 500 finally: close_connection(connection, cursor) @app.route('/admin/preview_quotation/') def preview_quotation(application_id): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute("SELECT quotation FROM task_applications WHERE application_id = %s", (application_id,)) result = cursor.fetchone() except mysql.connector.Error as err: print(f"Error fetching quotation: {err}") abort(404) finally: close_connection(connection, cursor) if not result or not result[0]: abort(404) return Response(result[0], mimetype='application/pdf', headers={'Content-Disposition': 'inline; filename=quotation.pdf'}) @app.route('/admin/download_quotation/') def download_quotation(application_id): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute("SELECT quotation FROM task_applications WHERE application_id = %s", (application_id,)) result = cursor.fetchone() except mysql.connector.Error as err: print(f"Error fetching quotation: {err}") abort(404) finally: close_connection(connection, cursor) if not result or not result[0]: abort(404) return Response(result[0], mimetype='application/pdf', headers={'Content-Disposition': 'attachment; filename=quotation.pdf'}) @app.route('/admin/task//applications') def admin_task_applications(task_id): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None task = None applications = [] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT * FROM tasks WHERE task_id = %s", (task_id,)) task = cursor.fetchone() if not task: abort(404) cursor.execute(""" SELECT ta.application_id, ta.supplier_id, ta.application_date, ta.status, c.name AS supplier_name, c.email AS supplier_email FROM task_applications ta JOIN company c ON ta.supplier_id = c.comp_id WHERE ta.task_id = %s ORDER BY ta.application_date DESC """, (task_id,)) applications = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching applications: {err}") finally: close_connection(connection, cursor) return render_template('admin_task_applications.html', task=task, applications=applications) @app.route('/supplier/apply_task', methods=['GET', 'POST']) def supplier_apply_task(): if 'loggedin' not in session or session.get('role') != 'supplier': return redirect(url_for('login')) connection = None cursor = None supplier_id = None available_tasks = [] msg = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT comp_id FROM company WHERE email = %s", (session['email'],)) supplier = cursor.fetchone() if not supplier: close_connection(connection, cursor) return render_template('supplier_apply_task.html', msg="Supplier not found.") supplier_id = supplier['comp_id'] cursor.execute(""" SELECT t.task_id, t.name AS task_name, t.description, t.expiry_date, j.name AS job_name FROM tasks t JOIN jobs j ON t.job_id = j.job_id JOIN company_jobs cj ON j.job_id = cj.jobs_id WHERE cj.company_id = %s AND cj.job_status = 'Accepted' AND (t.expiry_date IS NULL OR t.expiry_date >= CURDATE()) ORDER BY j.name, t.created_at DESC """, (supplier_id,)) available_tasks = cursor.fetchall() close_connection(connection, cursor) except mysql.connector.Error as err: print(f"Error fetching tasks: {err}") finally: close_connection(connection, cursor) if request.method == 'POST': task_id = request.form.get('task_id') quotation_file = request.files.get('quotation') if not task_id or not quotation_file: return render_template('supplier_apply_task.html', available_tasks=available_tasks, msg="Task and quotation are required.") if not quotation_file.filename.endswith('.pdf'): return render_template('supplier_apply_task.html', available_tasks=available_tasks, msg="Please upload a valid PDF file.") quotation_file.seek(0, 2) file_size = quotation_file.tell() quotation_file.seek(0) if file_size > 25 * 1024 * 1024: return render_template('supplier_apply_task.html', available_tasks=available_tasks, msg="File size must not exceed 25 MB.") quotation_data = quotation_file.read() connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor() cursor.execute("SELECT application_id FROM task_applications WHERE task_id = %s AND supplier_id = %s", (task_id, supplier_id)) if cursor.fetchone(): close_connection(connection, cursor) return render_template('supplier_apply_task.html', available_tasks=available_tasks, msg="You have already applied for this task.") cursor.execute(""" INSERT INTO task_applications (task_id, supplier_id, quotation) VALUES (%s, %s, %s) """, (task_id, supplier_id, quotation_data)) connection.commit() cursor.execute(""" SELECT t.name AS task_name, j.name AS job_name, c.name AS supplier_name FROM tasks t JOIN jobs j ON t.job_id = j.job_id JOIN company c ON c.comp_id = %s WHERE t.task_id = %s """, (supplier_id, task_id)) details = cursor.fetchone() cursor.execute("SELECT email FROM admins") admins = cursor.fetchall() close_connection(connection, cursor) if admins and details: for admin in admins: try: msg_body = f""" Dear Admin, A new task application has been submitted. Supplier: {details['supplier_name']} Task: {details['task_name']} Job: {details['job_name']} Please review the attached quotation. Best regards, Construction Industry Pension Fund """ msg = Message( subject=f"New Task Application: {details['task_name']} by {details['supplier_name']}", sender=app.config['MAIL_USERNAME'], recipients=[admin['email']], body=msg_body ) msg.attach("quotation.pdf", "application/pdf", quotation_data) mail.send(msg) except Exception as e: print(f"Failed to send email to {admin['email']}: {e}") msg = "Application submitted successfully! Notifications sent to admins." except mysql.connector.Error as err: msg = f"Error: {err}" if connection: connection.rollback() finally: close_connection(connection, cursor) return render_template('supplier_apply_task.html', available_tasks=available_tasks, msg=msg) return render_template('supplier_apply_task.html', available_tasks=available_tasks) @app.route('/supplier/status') def supplier_job_status(): if 'loggedin' not in session or session.get('role') != 'supplier': return redirect(url_for('login')) page = int(request.args.get('page', 1)) per_page = 7 offset = (page - 1) * per_page connection = None cursor = None applications = [] total_pages = 1 try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT comp_id FROM company WHERE email = %s", (session['email'],)) supplier = cursor.fetchone() if not supplier: close_connection(connection, cursor) return render_template('supplier_job_status.html', applications=[], page=page, total_pages=0) supplier_id = supplier['comp_id'] cursor.execute(""" SELECT COUNT(*) as total FROM company_jobs cj JOIN jobs j ON cj.jobs_id = j.job_id WHERE cj.company_id = %s """, (supplier_id,)) total = cursor.fetchone()['total'] total_pages = (total + per_page - 1) // per_page cursor.execute(""" SELECT cj.company_job_id, c.name AS company_name, j.name AS job_name, j.job_description, j.job_closing_date, cj.date_application, cj.job_status AS status FROM company_jobs cj JOIN jobs j ON cj.jobs_id = j.job_id JOIN company c ON cj.company_id = c.comp_id WHERE cj.company_id = %s ORDER BY cj.date_application DESC LIMIT %s OFFSET %s """, (supplier_id, per_page, offset)) applications = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching status: {err}") finally: close_connection(connection, cursor) return render_template('supplier_job_status.html', applications=applications, page=page, total_pages=total_pages) @app.route('/supplier/task_status') def supplier_task_status(): if 'loggedin' not in session or session.get('role') != 'supplier': return redirect(url_for('login')) page = int(request.args.get('page', 1)) per_page = 7 offset = (page - 1) * per_page connection = None cursor = None applications = [] total_pages = 1 try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT comp_id FROM company WHERE email = %s", (session['email'],)) supplier = cursor.fetchone() if not supplier: close_connection(connection, cursor) return render_template('supplier_task_status.html', applications=[], page=page, total_pages=0) supplier_id = supplier['comp_id'] cursor.execute(""" SELECT COUNT(*) as total FROM task_applications ta WHERE ta.supplier_id = %s """, (supplier_id,)) total = cursor.fetchone()['total'] total_pages = (total + per_page - 1) // per_page cursor.execute(""" SELECT ta.application_id, t.name AS task_name, j.name AS job_name, t.description AS task_description, t.expiry_date, ta.application_date, ta.status FROM task_applications ta JOIN tasks t ON ta.task_id = t.task_id JOIN jobs j ON t.job_id = j.job_id WHERE ta.supplier_id = %s ORDER BY ta.application_date DESC LIMIT %s OFFSET %s """, (supplier_id, per_page, offset)) applications = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching status: {err}") finally: close_connection(connection, cursor) return render_template('supplier_task_status.html', applications=applications, page=page, total_pages=total_pages) @app.route('/admin/dashboard') def admin_dashboard(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) connection = None cursor = None total_jobs = 0 total_tasks = 0 total_suppliers = 0 accepted_tasks = 0 declined_tasks = 0 pending_tasks = 0 total_applications = 0 open_jobs = 0 closed_jobs = 0 almost_due_tasks = 0 tasks_by_job = [] recent_activities = [] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT COUNT(*) as total_jobs FROM jobs") total_jobs = cursor.fetchone()['total_jobs'] cursor.execute("SELECT COUNT(*) as total_tasks FROM tasks") total_tasks = cursor.fetchone()['total_tasks'] cursor.execute("SELECT COUNT(*) as total_suppliers FROM company") total_suppliers = cursor.fetchone()['total_suppliers'] cursor.execute("SELECT COUNT(*) as accepted_tasks FROM task_applications WHERE status = 'Accepted'") accepted_tasks = cursor.fetchone()['accepted_tasks'] cursor.execute("SELECT COUNT(*) as declined_tasks FROM task_applications WHERE status = 'Declined'") declined_tasks = cursor.fetchone()['declined_tasks'] cursor.execute("SELECT COUNT(*) as pending_tasks FROM task_applications WHERE status = 'Pending'") pending_tasks = cursor.fetchone()['pending_tasks'] cursor.execute("SELECT COUNT(*) as total_applications FROM task_applications") total_applications = cursor.fetchone()['total_applications'] cursor.execute("SELECT COUNT(*) as open_jobs FROM jobs WHERE status = 'Open'") open_jobs = cursor.fetchone()['open_jobs'] cursor.execute("SELECT COUNT(*) as closed_jobs FROM jobs WHERE status = 'Closed'") closed_jobs = cursor.fetchone()['closed_jobs'] cursor.execute(""" SELECT COUNT(*) as almost_due_tasks FROM tasks WHERE expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) """) almost_due_tasks = cursor.fetchone()['almost_due_tasks'] cursor.execute(""" SELECT j.name AS job_name, COUNT(t.task_id) AS task_count FROM jobs j LEFT JOIN tasks t ON j.job_id = t.job_id GROUP BY j.job_id, j.name ORDER BY task_count DESC """) tasks_by_job = cursor.fetchall() cursor.execute(""" SELECT t.name AS task_name, c.name AS supplier_name, ta.status FROM tasks t LEFT JOIN task_applications ta ON t.task_id = ta.task_id AND ta.status = 'Accepted' LEFT JOIN company c ON ta.supplier_id = c.comp_id ORDER BY t.created_at DESC LIMIT 5 """) recent_activities = cursor.fetchall() except mysql.connector.Error as err: print(f"Error fetching dashboard: {err}") finally: close_connection(connection, cursor) return render_template('admin_dashboard.html', total_jobs=total_jobs, total_tasks=total_tasks, total_suppliers=total_suppliers, accepted_tasks=accepted_tasks, declined_tasks=declined_tasks, pending_tasks=pending_tasks, total_applications=total_applications, open_jobs=open_jobs, closed_jobs=closed_jobs, almost_due_tasks=almost_due_tasks, tasks_by_job=tasks_by_job, recent_activities=recent_activities) @app.route('/admin/emailing', methods=['GET', 'POST']) def admin_emailing(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) msg = None connection = None cursor = None if request.method == 'POST': email_content = request.form.get('content', '').strip() if not email_content: msg = "Email content cannot be empty." else: try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute(""" SELECT comp_id, name, phone_number, email FROM company WHERE email IS NOT NULL AND email != '' ORDER BY name """) companies = cursor.fetchall() close_connection(connection, cursor) if not companies: msg = "No companies found in database." else: successful_emails = [] failed_emails = [] for company in companies: try: email_body = f""" Dear {company['name']}, {email_content} Best regards, Construction Industry Pension Fund Admin Team """ message = Message( subject="Important Update from Construction Industry Pension Fund", sender=app.config['MAIL_USERNAME'], recipients=[company['email']], body=email_body ) mail.send(message) successful_emails.append({ 'comp_id': company['comp_id'], 'name': company['name'], 'phone_number': company['phone_number'] or 'N/A', 'email': company['email'], 'status': 'SUCCESS' }) except Exception as e: failed_emails.append({ 'comp_id': company['comp_id'], 'name': company['name'], 'phone_number': company['phone_number'] or 'N/A', 'email': company['email'], 'status': f'FAILED - {str(e)[:100]}' }) total_sent = len(successful_emails) total_failed = len(failed_emails) msg = f"Broadcast completed! Sent: {total_sent} | Failed: {total_failed}" excel_file = create_email_report_excel(successful_emails, failed_emails, email_content) send_excel_report_to_admins(excel_file, total_sent, total_failed, email_content) msg += " | Excel report sent to all admins!" except Exception as err: msg = f"Error: {str(err)}" total_companies = 0 try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT COUNT(*) as total_companies FROM company WHERE email IS NOT NULL AND email != ''") result = cursor.fetchone() total_companies = result['total_companies'] if result else 0 except: pass finally: close_connection(connection, cursor) return render_template('admin_emailing.html', msg=msg, total_companies=total_companies) def create_email_report_excel(successful_emails, failed_emails, email_content): wb = openpyxl.Workbook() wb.remove(wb.active) ws_success = wb.create_sheet("Successful", 0) ws_success.append(['Company ID', 'Company Name', 'Phone Number', 'Email', 'Status', 'Sent At']) for email_data in successful_emails: ws_success.append([ email_data['comp_id'], email_data['name'], email_data['phone_number'], email_data['email'], email_data['status'], datetime.now().strftime("%Y-%m-%d %H:%M:%S") ]) ws_failed = wb.create_sheet("Failed", 1) ws_failed.append(['Company ID', 'Company Name', 'Phone Number', 'Email', 'Status', 'Error Time']) for email_data in failed_emails: ws_failed.append([ email_data['comp_id'], email_data['name'], email_data['phone_number'], email_data['email'], email_data['status'], datetime.now().strftime("%Y-%m-%d %H:%M:%S") ]) header_font = Font(bold=True, color="FFFFFF", size=12) header_fill_success = PatternFill(start_color="27AE60", end_color="27AE60", fill_type="solid") header_fill_failed = PatternFill(start_color="E74C3C", end_color="E74C3C", fill_type="solid") for cell in ws_success[1]: cell.font = header_font cell.fill = header_fill_success for cell in ws_failed[1]: cell.font = header_font cell.fill = header_fill_failed ws_success.freeze_panes = 'A2' ws_failed.freeze_panes = 'A2' excel_buffer = BytesIO() wb.save(excel_buffer) excel_buffer.seek(0) return excel_buffer def send_excel_report_to_admins(excel_file, total_sent, total_failed, email_content): connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute("SELECT email FROM admins WHERE email IS NOT NULL AND email != ''") admins = cursor.fetchall() except: pass finally: close_connection(connection, cursor) try: subject = f"Email Broadcast Report - {total_sent} Sent / {total_failed} Failed" body = f""" Email Broadcast Report Summary: - Successfully sent: {total_sent} - Failed: {total_failed} - Generated: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")} Message sent: {email_content[:300]}{'...' if len(email_content) > 300 else ''} Excel report attached. --- Email Broadcast System | Construction Industry Pension Fund """ message = Message(subject=subject, sender=app.config['MAIL_USERNAME'], body=body) message.attach( filename=f"Email_Broadcast_Report_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx", content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", data=excel_file.getvalue() ) for admin in admins: try: message.recipients = [admin['email']] mail.send(message) print(f"Report sent to: {admin['email']}") except Exception as e: print(f"Report failed for {admin['email']}: {e}") except Exception as e: print(f"Report error: {e}") @app.route('/admin/suppliers_listing', methods=['GET', 'POST']) def admin_suppliers_listing(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) selected_job_id = request.args.get('job_id', type=int) connection = None cursor = None all_jobs = [] job_details = [] try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) cursor.execute(""" SELECT j.job_id, j.name, j.job_description, j.job_closing_date, COUNT(cj.company_job_id) as total_applicants, SUM(CASE WHEN COALESCE(cj.job_status, 'Pending') = 'Accepted' THEN 1 ELSE 0 END) as accepted_count, SUM(CASE WHEN COALESCE(cj.job_status, 'Pending') = 'Declined' THEN 1 ELSE 0 END) as declined_count FROM jobs j LEFT JOIN company_jobs cj ON j.job_id = cj.jobs_id GROUP BY j.job_id ORDER BY j.name ASC """) all_jobs = cursor.fetchall() if selected_job_id: cursor.execute(""" SELECT c.comp_id, c.name AS company_name, c.email, c.phone_number, c.address, cj.company_job_id, cj.date_application, COALESCE(cj.job_status, 'Pending') AS job_status, j.name AS job_name FROM company_jobs cj JOIN company c ON cj.company_id = c.comp_id JOIN jobs j ON cj.jobs_id = j.job_id WHERE j.job_id = %s ORDER BY CASE WHEN cj.job_status = 'Accepted' THEN 1 WHEN cj.job_status = 'Pending' THEN 2 ELSE 3 END, cj.date_application DESC """, (selected_job_id,)) job_details = cursor.fetchall() else: cursor.execute(""" SELECT j.name AS job_name, c.name AS company_name, c.email, c.phone_number, COALESCE(cj.job_status, 'Pending') AS job_status, cj.date_application FROM company_jobs cj JOIN company c ON cj.company_id = c.comp_id JOIN jobs j ON cj.jobs_id = j.job_id WHERE cj.job_status = 'Accepted' ORDER BY j.name, c.name LIMIT 50 """) job_details = cursor.fetchall() except Exception as e: print(f"Error in suppliers_listing: {e}") finally: close_connection(connection, cursor) return render_template('admin_suppliers_listing.html', all_jobs=all_jobs, job_details=job_details, selected_job_id=selected_job_id) @app.route('/admin/export_suppliers_listing') def export_suppliers_listing(): if 'loggedin' not in session or session.get('role') != 'admin': return redirect(url_for('login')) job_id = request.args.get('job_id', type=int) connection = None cursor = None try: connection = get_db_connection() cursor = connection.cursor(dictionary=True) wb = openpyxl.Workbook() wb.remove(wb.active) if job_id: cursor.execute(""" SELECT j.name AS job_name, c.name AS company_name, c.email, c.phone_number, c.address, cj.company_job_id, cj.date_application, COALESCE(cj.job_status, 'Pending') AS job_status FROM company_jobs cj JOIN company c ON cj.company_id = c.comp_id JOIN jobs j ON cj.jobs_id = j.job_id WHERE j.job_id = %s ORDER BY CASE WHEN cj.job_status = 'Accepted' THEN 1 WHEN cj.job_status = 'Pending' THEN 2 ELSE 3 END, cj.date_application DESC """, (job_id,)) data = cursor.fetchall() if data: ws_accepted = wb.create_sheet("ACCEPTED", 0) ws_accepted.append(['Job Name', 'Company ID', 'Company Name', 'Email', 'Phone', 'Address', 'Application Date', 'Status']) accepted_data = [row for row in data if row['job_status'] == 'Accepted'] for row in accepted_data: ws_accepted.append([ row['job_name'], row['company_job_id'], row['company_name'], row['email'], row['phone_number'], row['address'], row['date_application'], row['job_status'] ]) ws_pending = wb.create_sheet("PENDING", 1) ws_pending.append(['Job Name', 'Company ID', 'Company Name', 'Email', 'Phone', 'Address', 'Application Date', 'Status']) pending_data = [row for row in data if row['job_status'] == 'Pending'] for row in pending_data: ws_pending.append([ row['job_name'], row['company_job_id'], row['company_name'], row['email'], row['phone_number'], row['address'], row['date_application'], row['job_status'] ]) ws_declined = wb.create_sheet("DECLINED", 2) ws_declined.append(['Job Name', 'Company ID', 'Company Name', 'Email', 'Phone', 'Address', 'Application Date', 'Status']) declined_data = [row for row in data if row['job_status'] == 'Declined'] for row in declined_data: ws_declined.append([ row['job_name'], row['company_job_id'], row['company_name'], row['email'], row['phone_number'], row['address'], row['date_application'], row['job_status'] ]) filename = f"Suppliers_Listing_{data[0]['job_name'].replace(' ', '_')}_{datetime.now().strftime('%Y%m%d')}.xlsx" else: ws = wb.create_sheet("No Data", 0) ws.append(["No applications found for this job"]) filename = f"Suppliers_Listing_Empty_{datetime.now().strftime('%Y%m%d')}.xlsx" else: cursor.execute(""" SELECT DISTINCT j.job_id, j.name AS job_name FROM jobs j JOIN company_jobs cj ON j.job_id = cj.jobs_id ORDER BY j.name """) jobs = cursor.fetchall() for job in jobs: cursor.execute(""" SELECT c.name AS company_name, c.email, c.phone_number, c.address, cj.company_job_id, cj.date_application, COALESCE(cj.job_status, 'Pending') AS job_status FROM company_jobs cj JOIN company c ON cj.company_id = c.comp_id WHERE cj.jobs_id = %s ORDER BY CASE WHEN cj.job_status = 'Accepted' THEN 1 WHEN cj.job_status = 'Pending' THEN 2 ELSE 3 END """, (job['job_id'],)) job_data = cursor.fetchall() if job_data: sheet_name = job['job_name'][:31] ws = wb.create_sheet(sheet_name, len(wb.worksheets)) ws.append(['Company ID', 'Company Name', 'Email', 'Phone', 'Address', 'Application Date', 'Status']) for row in job_data: ws.append([ row['company_job_id'], row['company_name'], row['email'], row['phone_number'], row['address'], row['date_application'], row['job_status'] ]) filename = f"All_Suppliers_Listing_{datetime.now().strftime('%Y%m%d')}.xlsx" header_font = Font(bold=True, color="FFFFFF", size=12) header_fill = PatternFill(start_color="3498DB", end_color="3498DB", fill_type="solid") for ws in wb.worksheets: for cell in ws[1]: cell.font = header_font cell.fill = header_fill excel_buffer = BytesIO() wb.save(excel_buffer) excel_buffer.seek(0) return Response( excel_buffer, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', headers={'Content-Disposition': f'attachment; filename={filename}'} ) except Exception as e: print(f"Excel export error: {e}") return "Export failed", 500 finally: close_connection(connection, cursor) if __name__ == '__main__': port = int(os.environ.get("PORT", 5004)) app.run(host="0.0.0.0", port=port, debug=True)