# server/app.py from flask import Flask, request, jsonify, session, Response, send_file, after_this_request from flask_cors import CORS from flask_login import LoginManager, login_user, logout_user, login_required, current_user from datetime import timedelta, datetime from models import ( db, User, EnglishClass, EnglishLevel, PrivateSchoolClass, EnglishBook, BookPurchaseReceipt, EnglishRegistrationReceipt, RegistrationFee, Link_Reset, FormStock, EnglishRole, PrivateFeeItem, PrivateRole, EnglishStudent, EnglishSession, PrivateFeeAssignment, PrivateSession, PrivateStudent, PrivateEnrollmentReceipt, PrivateBook, PrivateBookReceipt, PrivateUniform, PrivateUniformReceipt, PrivateFormStock, PrivateFormReceipt, EnglishSessionTime, EnglishSessionPeriod, IslamiyyaFeeSetting, IslamiyyaPaymentReceipt ) from functions import ( requires_roles, require_password_changed, get_student_by_search, generate_receipt_number, is_valid_email, build_english_registration_receipt, private_school_classes_to_code, private_school_code_to_display, get_next_color_index, get_next_pass_number, SHORT_CODE_TO_CLASS_CODE, CLASS_CODE_TO_SHORT, get_class_name_from_code, get_class_display_name_from_code, format_session_label, get_class_codes_for_group, get_next_color_index_english, parse_class_code ) from gate_pass_file import ( process_college_pass, colors, color_map, process_college_pass_english, ) import os from io import BytesIO import uuid import time from constants_file import BASE_URL_FRONTEND from mail_file import send_reset_email, send_mail_vercel, send_new_user_message from itsdangerous import URLSafeTimedSerializer from flask_mail import Mail from init_data import initialize_database from openpyxl import Workbook from openpyxl.styles import Font, Alignment, numbers, PatternFill, Border, Side from datetime import datetime, timezone, timedelta import openpyxl from flask_migrate import Migrate import pandas as pd import re import tempfile import base64 from reportlab.lib.pagesizes import A4 from reportlab.pdfgen import canvas from reportlab.lib.utils import ImageReader from reportlab.lib.pagesizes import A4, landscape app = Flask(__name__) app.config['SECRET_KEY'] = 'your-secret-keysdsdsdss-change-in-production' app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///school_management.db' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False app.config["SESSION_COOKIE_NAME"] = "app2_session" app.config['REMEMBER_COOKIE_DURATION'] = timedelta(days=7) mail = Mail(app) CORS(app, supports_credentials=True) db.init_app(app) migrate = Migrate(app, db) login_manager = LoginManager(app) login_manager.login_view = 'login' s = URLSafeTimedSerializer(app.config['SECRET_KEY']) # Mail configuration (optional) app.config['MAIL_SERVER'] = 'smtp.gmail.com' app.config['MAIL_PORT'] = 587 app.config['MAIL_USE_TLS'] = True app.config['MAIL_USERNAME'] = os.environ.get('MAIL_USERNAME') app.config['MAIL_PASSWORD'] = os.environ.get('MAIL_PASSWORD') app.config['MAIL_DEFAULT_SENDER'] = os.environ.get('MAIL_DEFAULT_SENDER') @login_manager.user_loader def load_user(user_id): return User.query.get(user_id) @login_manager.unauthorized_handler def unauthorized(): return jsonify({'error': 'Authentication required.'}), 403 @app.route('/') def home(): return jsonify({"greetings": "Hello"}) # ==================== AUTH ROUTES ==================== @app.route('/api/login', methods=['POST']) def login(): data = request.get_json() email = data.get('email').lower() password = data.get('password') user = User.query.filter_by(email=email).first() if user and user.check_password(password): login_user(user) return jsonify({ 'success': True, 'user': { 'id': user.id, 'name': user.name, 'email': user.email, 'english_roles': [role.name for role in user.english_roles], 'private_roles': [role.name for role in user.private_roles], 'must_change_password': user.must_change_password } }) return jsonify({'error': 'Invalid credentials.'}), 401 @app.route('/api/change-password', methods=['POST']) @login_required def change_password(): data = request.get_json() current_password = data.get('current_password') new_password = data.get('new_password') if current_user.check_password(current_password): if new_password == '123456': return jsonify({'error': 'Cannot change back to default password.'}), 400 if len(new_password) < 6: return jsonify({'error': 'Password must be at least 6 characters.'}), 400 current_user.set_password(new_password) current_user.must_change_password = False db.session.commit() return jsonify({'success': True}) return jsonify({'error': 'Current password is incorrect.'}), 401 @app.route('/api/logout', methods=['POST']) @login_required def logout(): logout_user() return jsonify({'success': True}) @app.route('/api/current-user', methods=['GET']) @login_required def current_user_info(): return jsonify({ 'id': current_user.id, 'name': current_user.name, 'email': current_user.email, 'english_roles': [role.name for role in current_user.english_roles], 'private_roles': [role.name for role in current_user.private_roles], 'must_change_password': current_user.must_change_password }) # ==================== USER MANAGEMENT (Director) ==================== @app.route('/api/all-users', methods=['GET']) @login_required def get_all_users(): users = User.query.all() return jsonify([{ 'id': u.id, 'name': u.name, 'email': u.email, 'english_roles': [r.name for r in u.english_roles], 'private_roles': [r.name for r in u.private_roles], 'created_at': u.created_at.isoformat() } for u in users]) @app.route('/api/english/create-user', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Director') def create_english_user(): data = request.get_json() email = data.get('email').lower() name = data.get('name') if User.query.filter_by(email=email).first(): return jsonify({'error': 'Email already exists.'}), 400 user = User( name=name, email=email, must_change_password=True ) user.set_password('123456') # Assign English roles only role_names = data.get('roles', []) for role_name in role_names: role = EnglishRole.query.filter_by(name=role_name).first() if role: user.english_roles.append(role) msg = send_new_user_message(email, name) try: res = send_mail_vercel(msg) if not res.get("status"): raise Exception(res.get("error")) except Exception as e: if str(e) == '[Errno 11001] getaddrinfo failed': error = 'Failed to connect. Please check your internet connection.' else: error = f'Error: {str(e)}' return jsonify({'error': f'{error}'}), 400 db.session.add(user) db.session.commit() return jsonify({'success': True, 'user_id': user.id}) @app.route('/api/update-user/', methods=['PUT']) @login_required @require_password_changed def update_user(user_id): user = User.query.get(user_id) if not user: return jsonify({'error': 'User not found'}), 404 data = request.get_json() changing_english = 'english_roles' in data changing_private = 'private_roles' in data # Permission checks is_english_director = any(role.name == 'Director' for role in current_user.english_roles) is_private_director = any(role.name == 'Director' for role in current_user.private_roles) if changing_english and not is_english_director: return jsonify({'error': 'Requires English Director role to modify English roles'}), 403 if changing_private and not is_private_director: return jsonify({'error': 'Requires Private Director role to modify Private roles'}), 403 if not changing_english and not changing_private: # Only updating non‑role fields (e.g. name) – any Director is fine if not (is_english_director or is_private_director): return jsonify({'error': 'Requires Director role in either English or Private system to update user'}), 403 # Update name (applies to both systems) if 'name' in data: user.name = data['name'] # Update English roles if provided if 'english_roles' in data: # Check if trying to remove Director role from English system current_has_director = any(role.name == 'Director' for role in user.english_roles) new_has_director = 'Director' in data['english_roles'] if current_has_director and not new_has_director: # Count total English directors in system total_directors = User.query.filter(User.english_roles.any(name='Director')).count() if total_directors <= 1: return jsonify({'error': 'Cannot remove the only English Director from the system'}), 400 # Update English roles user.english_roles = [] for role_name in data['english_roles']: role = EnglishRole.query.filter_by(name=role_name).first() if role: user.english_roles.append(role) # Update Private roles if provided if 'private_roles' in data: # Check if trying to remove Director role from Private system current_has_director = any(role.name == 'Director' for role in user.private_roles) new_has_director = 'Director' in data['private_roles'] if current_has_director and not new_has_director: # Count total Private directors in system total_directors = User.query.filter(User.private_roles.any(name='Director')).count() if total_directors <= 1: return jsonify({'error': 'Cannot remove the only Private Director from the system'}), 400 # Update Private roles user.private_roles = [] for role_name in data['private_roles']: role = PrivateRole.query.filter_by(name=role_name).first() if role: user.private_roles.append(role) db.session.commit() return jsonify({'success': True}) # ==================== CLASS AND FEE MANAGEMENT ==================== @app.route('/api/english-classes', methods=['GET']) @login_required def get_english_classes(): classes = EnglishClass.query.all() return jsonify([{ 'id': c.id, 'name': c.name, 'code': c.code, 'levels': [{'id': l.id, 'name': l.name, 'level_order': l.level_order} for l in c.levels] } for c in classes]) @app.route('/api/private-school-classes', methods=['GET']) @login_required def get_private_school_classes(): classes = PrivateSchoolClass.query.all() return jsonify([{ 'id': c.id, 'name': c.name, 'code': c.code } for c in classes]) @app.route('/api/fees', methods=['GET']) @login_required def get_fees(): # Get form fees from FormStock table form_stock = FormStock.query.all() reg_fees = RegistrationFee.query.all() form_fees_data = [] for form in form_stock: # Get all levels for this class levels = EnglishLevel.query.filter_by(class_code=form.class_code).order_by(EnglishLevel.level_order).all() if levels and len(levels) > 0: # Class has levels - create form fee entry for EVERY level for level in levels: form_fees_data.append({ 'id': form.id, 'class_code': form.class_code, 'level_order': level.level_order, 'amount': float(form.price) }) else: # Class has no levels - don't include level_order form_fees_data.append({ 'id': form.id, 'class_code': form.class_code, 'amount': float(form.price) }) return jsonify({ 'form_fees': form_fees_data, 'registration_fees': [{'id': f.id, 'class_code': f.class_code, 'amount': float(f.amount)} for f in reg_fees] }) @app.route('/api/fees/registration', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def set_registration_fee(): data = request.get_json() class_code = data.get('class_code') amount = data.get('amount') fee = RegistrationFee.query.filter_by(class_code=class_code).first() if fee: fee.amount = amount else: fee = RegistrationFee(class_code=class_code, amount=amount) db.session.add(fee) db.session.commit() return jsonify({'success': True}) # ==================== BOOK MANAGEMENT (InventoryManager) ==================== @app.route('/api/english/books', methods=['GET']) @login_required def get_english_books(): # Get all English books (both english class books and general books) books = EnglishBook.query.order_by(EnglishBook.created_at.desc()).all() return jsonify([{ 'id': b.id, 'title': b.title, 'author': b.author, 'price': b.price, 'quantity': b.quantity, 'class_type': b.class_type, 'class_id': b.class_id, 'class_name': b.class_name, 'level': b.level, 'student_status': b.student_status, 'created_at': b.created_at.isoformat(), 'updated_at': b.updated_at.isoformat() } for b in books]) @app.route('/api/english/create-book', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def create_english_book(): data = request.get_json() title = (data.get('title') or '').strip() author = (data.get('author') or '').strip() price = data.get('price') quantity = data.get('quantity') class_type = data.get('class_type') # Must be explicitly 'english' or 'general' class_id = data.get('class_id') or '' class_name = data.get('class_name') or '' level = data.get('level') or '' student_status = data.get('student_status') or '' errors = {} # Title if not title: errors['title'] = 'Title is required' # Author if not author: errors['author'] = 'Author is required' # Price try: price = float(price) if price <= 0: errors['price'] = 'Price must be greater than 0' except (TypeError, ValueError): errors['price'] = 'Invalid price' # Quantity try: quantity = int(quantity) if quantity < 0: errors['quantity'] = 'Quantity cannot be negative' except (TypeError, ValueError): errors['quantity'] = 'Invalid quantity' # Class type validation - MUST be explicitly chosen if not class_type: errors['class_type'] = 'Book type (English Class or General) must be selected' elif class_type not in ['english', 'general']: errors['class_type'] = 'Invalid book type. Must be "english" or "general"' elif class_type == 'english': if not class_id: errors['class_id'] = 'Class must be selected' if not level: errors['level'] = 'Level must be selected' if not student_status or student_status not in ['fresh', 'returning']: errors['student_status'] = 'Student status must be selected' elif class_type == 'general': # Clear fields for general books class_id = '' class_name = '' level = '' student_status = '' if errors: return jsonify({'success': False, 'errors': errors}), 400 book = EnglishBook( title=title, author=author, price=price, quantity=quantity, class_type=class_type, class_id=class_id, class_name=class_name, level=level, student_status=student_status ) db.session.add(book) db.session.commit() return jsonify({'success': True, 'book_id': book.id}) @app.route('/api/english/update-book/', methods=['PUT']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def update_english_book(book_id): book = EnglishBook.query.get(book_id) if not book: return jsonify({'error': 'Book not found'}), 404 data = request.get_json() errors = {} title = (data.get('title') or '').strip() author = (data.get('author') or '').strip() price = data.get('price') quantity = data.get('quantity') class_type = data.get('class_type') class_id = data.get('class_id') or '' class_name = data.get('class_name') or '' level = data.get('level') or '' student_status = data.get('student_status') or '' # Title if not title: errors['title'] = 'Title is required' # Author if not author: errors['author'] = 'Author is required' # Price try: price = float(price) if price <= 0: errors['price'] = 'Price must be greater than 0' except (TypeError, ValueError): errors['price'] = 'Invalid price' # Quantity try: quantity = int(quantity) if quantity < 0: errors['quantity'] = 'Quantity cannot be negative' except (TypeError, ValueError): errors['quantity'] = 'Invalid quantity' # Class type validation if not class_type: errors['class_type'] = 'Book type (English Class or General) must be selected' elif class_type not in ['english', 'general']: errors['class_type'] = 'Invalid book type. Must be "english" or "general"' elif class_type == 'english': if not class_id: errors['class_id'] = 'Class must be selected' if not level: errors['level'] = 'Level must be selected' if not student_status or student_status not in ['fresh', 'returning']: errors['student_status'] = 'Student status must be selected' elif class_type == 'general': class_id = '' class_name = '' level = '' student_status = '' if errors: return jsonify({'success': False, 'errors': errors}), 400 # Check for actual changes has_changes = False if book.title != title: book.title = title has_changes = True if book.author != author: book.author = author has_changes = True if float(book.price) != price: book.price = price has_changes = True if int(book.quantity) != quantity: book.quantity = quantity has_changes = True if (book.class_type or '') != class_type: book.class_type = class_type has_changes = True if (book.class_id or '') != class_id: book.class_id = class_id has_changes = True if (book.class_name or '') != class_name: book.class_name = class_name has_changes = True if (book.level or '') != level: book.level = level has_changes = True if (book.student_status or '') != student_status: book.student_status = student_status has_changes = True if not has_changes: return jsonify({ 'success': False, 'message': 'No changes were made' }), 400 db.session.commit() return jsonify({'success': True}) @app.route('/api/english/delete-book/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def delete_english_book(book_id): book = EnglishBook.query.get(book_id) if not book: return jsonify({'error': 'Book not found'}), 404 db.session.delete(book) db.session.commit() return jsonify({'success': True, 'message': 'Book deleted successfully'}), 200 # ==================== STUDENT MANAGEMENT & ENROLLMENT ==================== @app.route('/api/english/students/search', methods=['GET']) @login_required def search_english_students(): query = request.args.get('q', '') include_deleted = request.args.get('include_deleted', 'false').lower() == 'true' if not query: return jsonify([]) students = get_student_by_search("english", query, include_deleted) return jsonify([s.to_dict() for s in students]) @app.route('/api/english/all-students', methods=['GET']) @login_required def get_all_english_students(): include_deleted = request.args.get('include_deleted', 'false').lower() == 'true' try: query = EnglishStudent.query if not include_deleted: query = query.filter_by(deleted=False) students = query.order_by(EnglishStudent.name.asc()).all() return jsonify([s.to_dict() for s in students]), 200 except Exception as e: return jsonify({'error': str(e)}), 500 # ==================== STUDENT MANAGEMENT (Edit/Update) ==================== @app.route('/api/english/update-student/', methods=['PUT']) @login_required @requires_roles('english', 'Cashier', 'Director') def update_student(student_id): """Update student information (name, phone, email)""" try: student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 data = request.get_json() or {} photo_base64 = data.get('photo_base64') new_name = (data.get('name') or '').strip() new_phone = (data.get('phone') or '').strip() new_email = (data.get('email') or '').strip() # Required fields (email is NOT required) if not new_name or not new_phone: return jsonify({ 'success': False, 'error': 'Name and phone are required' }), 400 # Email validation only if provided if new_email and not is_valid_email(new_email): return jsonify({ 'success': False, 'error': 'Enter a valid email address' }), 400 # Check if nothing changed # if ( # new_name == (student.name or '').strip() and # new_phone == (student.phone or '').strip() and # new_email == (student.email or '').strip() # ): # return jsonify({ # 'success': False, # 'error': 'No changes were made' # }), 400 student.name = new_name student.phone = new_phone student.email = new_email if new_email else None if photo_base64: try: # Decode and save as photos/{student_id}.jpg import re, base64 photos_dir = 'photos' os.makedirs(photos_dir, exist_ok=True) match = re.match(r'data:image/(?P\w+);base64,(?P.+)', photo_base64) if match: image_data = base64.b64decode(match.group('data')) else: image_data = base64.b64decode(photo_base64) filepath = os.path.join(photos_dir, f"{student_id}.jpg") with open(filepath, 'wb') as f: f.write(image_data) student.updated_at = datetime.now(timezone.utc) except Exception as e: db.session.rollback() return jsonify({'success': False, 'error': f'Failed to save photo: {str(e)}'}), 500 db.session.commit() # class name resolution (same as GET route) class_name = None if student.class_code: english_class = EnglishClass.query.filter_by(code=student.class_code).first() class_name = english_class.name if english_class else None return jsonify({ 'success': True, 'student': student.to_dict(), }), 200 except Exception as e: return jsonify({ 'success': False, 'error': str(e) }), 500 @app.route('/api/english//change-class', methods=['PUT']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def change_english_student_class(student_id): """Change student to a different class and level""" student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 data = request.get_json() new_class_code = data.get('class_code') new_level_order = data.get('level_order') payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') discount_type = data.get('discount_type', 'fixed') discount_value = data.get('discount_value', 0) session_id = data.get('session_id') additional_amount = data.get('additional_amount', 0) additional_reason = data.get('additional_reason', '') payment_splits = data.get('payment_splits') # new primary field # Get fee selection flags (default to True for backward compatibility) charge_form_fee = data.get('charge_form_fee') charge_registration_fee = data.get('charge_registration_fee') if charge_form_fee is None or charge_registration_fee is None: return jsonify({'error': 'charge_form_fee and charge_registration_fee fields are required'}), 400 if session_id is None: return jsonify({'error': 'session_id field is required'}), 400 session = EnglishSession.query.get(session_id) if not session: return jsonify({'error': 'Invalid session'}), 400 if discount_amount < 0 or additional_amount < 0: return jsonify({'error': 'Amounts cannot be negative'}), 400 # Mutual exclusivity if discount_amount > 0 and additional_amount > 0: return jsonify({'error': 'Cannot apply both discount and additional fee. Choose one.'}), 400 # Validate at least one fee is selected if not charge_form_fee and not charge_registration_fee: return jsonify({'error': 'At least one fee (Form Fee or Registration Fee) must be selected'}), 400 if not new_class_code: return jsonify({'error': 'Class is required'}), 400 if not student.is_enrolled: return jsonify({'error': 'Student is not enrolled. Use enroll instead.'}), 400 if student.class_code == new_class_code: if new_level_order is not None and student.level_order == new_level_order: return jsonify({'error': 'No changes were made.'}), 400 elif new_level_order is None and student.level_order is None: return jsonify({'error': 'No changes were made.'}), 400 english_class = EnglishClass.query.filter_by(code=new_class_code).first() if not english_class: return jsonify({'error': 'Invalid class'}), 400 levels = EnglishLevel.query.filter_by(class_code=new_class_code).order_by(EnglishLevel.level_order).all() has_levels = len(levels) > 0 level = None if has_levels: if new_level_order is None: return jsonify({'error': 'Level is required for this class'}), 400 level = EnglishLevel.query.filter_by(class_code=new_class_code, level_order=new_level_order).first() if not level: return jsonify({'error': 'Invalid level'}), 400 else: new_level_order = None # Get form fee and registration fee from tables form_stock = FormStock.query.filter_by(class_code=new_class_code).first() reg_fee = RegistrationFee.query.filter_by(class_code=new_class_code).first() # Calculate subtotal based on selected fees (regardless of level) form_fee_amount = 0 registration_fee_amount = 0 subtotal = 0 # Charge form fee if selected (regardless of level) if charge_form_fee: if not form_stock: return jsonify({ 'error': f'Form fee not configured for class {new_class_code}. Please contact administrator to set up form fees.' }), 400 form_fee_amount = float(form_stock.price) subtotal += form_fee_amount # DEDUCT FORM STOCK if form fee is charged if form_stock.stock_quantity <= 0: return jsonify({ 'error': f'No forms in stock for {new_class_code}. Please restock before processing class change.' }), 400 form_stock.stock_quantity -= 1 # Charge registration fee if selected (regardless of level) if charge_registration_fee: if not reg_fee: return jsonify({ 'error': f'Registration fee not configured for class {new_class_code}. Please contact administrator to set up registration fees.' }), 400 registration_fee_amount = float(reg_fee.amount) subtotal += registration_fee_amount # Calculate actual discount or additional fee actual_discount = 0 actual_additional = 0 if discount_amount > 0: actual_discount = discount_amount if discount_type == 'percentage' and discount_value > 0: actual_discount = (subtotal * min(discount_value, 100)) / 100 if actual_discount > subtotal: return jsonify({ 'error': f'Discount amount (₦{actual_discount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 discount_percentage = (actual_discount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({ 'error': f'Discount reason required.', 'discount_percentage': discount_percentage }), 400 elif additional_amount > 0: actual_additional = additional_amount if not additional_reason: return jsonify({'error': 'Additional fee reason required'}), 400 total_amount = subtotal - actual_discount + actual_additional # --- Payment splits validation --- if not payment_splits: return jsonify({'error': 'payment_splits is required'}), 400 if not isinstance(payment_splits, list) or len(payment_splits) == 0: return jsonify({'error': 'payment_splits must be a non-empty list'}), 400 total_splits = 0 allowed_methods = {'Cash', 'POS', 'Transfer'} for split in payment_splits: method = split.get('method') amount = split.get('amount') if method not in allowed_methods: return jsonify({'error': f'Invalid payment method {method}'}), 400 try: amount = float(amount) if amount <= 0: return jsonify({'error': 'Each split amount must be positive'}), 400 total_splits += amount except (TypeError, ValueError): return jsonify({'error': 'Invalid amount in payment split'}), 400 if abs(total_splits - total_amount) > 0.01: return jsonify({'error': f'Sum of split amounts (₦{total_splits:,.2f}) does not equal total (₦{total_amount:,.2f})'}), 400 method_names = [split['method'] for split in payment_splits] combined_method = " + ".join(method_names) # e.g., "Cash + Transfer" receipt = build_english_registration_receipt( class_code=new_class_code, student_id=student.id, level_name=level.name if level else None, level_order=new_level_order, session_id=session_id, form_fee_amount=form_fee_amount, registration_fee_amount=registration_fee_amount, actual_discount=actual_discount, discount_reason=discount_reason, total_amount=total_amount, payment_splits=payment_splits, payment_method=combined_method, cashier_id=current_user.id, receipt_type='class_change', charge_form_fee=charge_form_fee, charge_registration_fee=charge_registration_fee, additional_amount=actual_additional, additional_reason=additional_reason ) old_class = student.class_code old_level = student.level_name student.class_code = new_class_code student.level_name = level.name if level else None student.level_order = new_level_order student.english_class_id = english_class.id try: db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'message': 'Student class changed successfully', 'receipt': receipt.to_dict(), 'student': student.to_dict(), 'previous_class': old_class, 'previous_level': old_level, }), 200 except Exception as e: db.session.rollback() return jsonify({'error': f'Failed to change class: {str(e)}'}), 500 @app.route('/api/calculate-fees/', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def calculate_fees(class_code): """Calculate fees for enrollment, class change with fee selection options""" level_order = request.args.get('level_order', type=int) fee_type = request.args.get('type', 'enrollment') # Get query params as strings and convert to boolean properly charge_form_fee_param = request.args.get('charge_form_fee') charge_registration_fee_param = request.args.get('charge_registration_fee') # Handle the parameters - they come as strings 'true'/'false' if charge_form_fee_param is None: return jsonify({'error': 'charge_form_fee parameter is required'}), 400 if charge_registration_fee_param is None: return jsonify({'error': 'charge_registration_fee parameter is required'}), 400 charge_form_fee = charge_form_fee_param.lower() == 'true' charge_registration_fee = charge_registration_fee_param.lower() == 'true' form_fee_amount = 0 registration_fee_amount = 0 # Get form fee from FormStock table if requested if charge_form_fee: form_stock = FormStock.query.filter_by(class_code=class_code).first() if form_stock: form_fee_amount = float(form_stock.price) # Get registration fee if requested if charge_registration_fee: reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first() if reg_fee: registration_fee_amount = float(reg_fee.amount) subtotal = form_fee_amount + registration_fee_amount # Check stock availability form_stock = FormStock.query.filter_by(class_code=class_code).first() reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first() return jsonify({ 'form_fee': form_fee_amount, 'registration_fee': registration_fee_amount, 'subtotal': subtotal, 'has_stock': form_stock.stock_quantity > 0 if form_stock else False, 'stock_quantity': form_stock.stock_quantity if form_stock else 0, 'has_form_fee_config': form_stock is not None, 'has_reg_fee_config': reg_fee is not None }) @app.route('/api/enroll-english', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def enroll_english_student(): data = request.get_json() # Get new student data if provided (instead of student_id) student_id = data.get('student_id') new_student_data = data.get('new_student_data') class_code = data.get('class_code') level_order = data.get('level_order') session_id = data.get('session_id') payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') additional_amount = data.get('additional_amount', 0) additional_reason = data.get('additional_reason', '') photo_base64 = data.get('photo_base64') # added # payment_splits required payment_splits = data.get('payment_splits') # new field # REQUIRED: Explicit flag to determine if student should be enrolled should_enroll = data.get('should_enroll') if should_enroll is None: return jsonify({'error': 'should_enroll field is required (true/false)'}), 400 # Validate session if provided if session_id is None: return jsonify({'error': 'session_id field is required'}), 400 session = EnglishSession.query.get(session_id) if not session: return jsonify({'error': 'Invalid session'}), 400 # Get fee selection flags (default to True for backward compatibility) charge_form_fee = data.get('charge_form_fee') charge_registration_fee = data.get('charge_registration_fee') if charge_form_fee is None or charge_registration_fee is None: return jsonify({'error': 'charge_form_fee and charge_registration_fee fields are required'}), 400 # SANITY CHECK 1: Discount and additional fee cannot be negative if discount_amount < 0 or additional_amount < 0: return jsonify({'error': 'Amounts cannot be negative'}), 400 # Mutual exclusivity if discount_amount > 0 and additional_amount > 0: return jsonify({'error': 'Cannot apply both discount and additional fee. Choose one.'}), 400 # SANITY CHECK 2: At least one fee must be charged if not charge_form_fee and not charge_registration_fee: return jsonify({'error': 'At least one fee (Form Fee or Registration Fee) must be selected'}), 400 # SANITY CHECK 3: If should_enroll is True, class_code is required if should_enroll and not class_code: return jsonify({'error': 'class_code is required when should_enroll is true'}), 400 student = None # Case 1: New student provided (from frontend) if new_student_data and not student_id: name = (new_student_data.get('name') or '').strip() phone = (new_student_data.get('phone') or '').strip() email = (new_student_data.get('email') or '').strip() # Required fields if not name or not phone: return jsonify({'error': 'Name, phone, are required'}), 400 # Email validation (ONLY if provided) if email and not is_valid_email(email=email): return jsonify({'error': 'Invalid email address'}), 400 # Create new student new_english_student = EnglishStudent( name=name, phone=phone, email=email or None, is_enrolled=should_enroll ) db.session.add(new_english_student) db.session.flush() # Get ID without committing student_id = new_english_student.id student = new_english_student # Case 2: Existing student elif student_id: student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 else: return jsonify({'error': 'Student information is required'}), 400 english_class = None level = None if class_code: english_class = EnglishClass.query.filter_by(code=class_code).first() if not english_class: return jsonify({'error': 'Invalid class code'}), 400 if level_order: level = EnglishLevel.query.filter_by(class_code=class_code, level_order=level_order).first() if not level: return jsonify({'error': 'Invalid level'}), 400 # Calculate fees - NOW ALWAYS APPLIES REGARDLESS OF LEVEL subtotal = 0 form_fee_amount = 0 registration_fee_amount = 0 form_stock = None # Get form fee if charge_form_fee is True (regardless of level) if charge_form_fee: form_stock = FormStock.query.filter_by(class_code=class_code).first() if not form_stock: return jsonify({ 'error': f'Form fee not configured for class {class_code}. Please contact administrator to set up form fees.' }), 400 form_fee_amount = float(form_stock.price) subtotal += form_fee_amount # Get registration fee if charge_registration_fee is True (regardless of level) if charge_registration_fee: reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first() if not reg_fee: return jsonify({ 'error': f'Registration fee not configured for class {class_code}. Please contact administrator to set up registration fees.' }), 400 registration_fee_amount = float(reg_fee.amount) subtotal += registration_fee_amount # Apply discount or additional fee actual_discount = 0 actual_additional = 0 if discount_amount > 0: actual_discount = discount_amount if actual_discount > subtotal: return jsonify({ 'error': f'Discount amount (₦{actual_discount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 discount_percentage = (actual_discount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({ 'error': f'Discount reason required.', 'discount_percentage': discount_percentage }), 400 elif additional_amount > 0: actual_additional = additional_amount if not additional_reason: return jsonify({'error': 'Additional fee reason required'}), 400 total_amount = subtotal - actual_discount + actual_additional if not payment_splits: return jsonify({'error': 'payment_splits is required'}), 400 if not isinstance(payment_splits, list) or len(payment_splits) == 0: return jsonify({'error': 'payment_splits must be a non-empty list'}), 400 total_splits = 0 allowed_methods = {'Cash', 'POS', 'Transfer'} for split in payment_splits: method = split.get('method') amount = split.get('amount') if method not in allowed_methods: return jsonify({'error': f'Invalid payment method {method}'}), 400 try: amount = float(amount) if amount <= 0: return jsonify({'error': 'Each split amount must be positive'}), 400 total_splits += amount except (TypeError, ValueError): return jsonify({'error': 'Invalid amount in payment split'}), 400 if abs(total_splits - total_amount) > 0.01: return jsonify({'error': f'Sum of split amounts (₦{total_splits:,.2f}) does not equal total (₦{total_amount:,.2f})'}), 400 # DEDUCT FORM STOCK ONLY IF form fee is charged AND we have form_stock if charge_form_fee and form_stock: if form_stock.stock_quantity <= 0: return jsonify({ 'error': f'No forms in stock for {class_code}. Please restock before enrolling students.' }), 400 form_stock.stock_quantity -= 1 method_names = [split['method'] for split in payment_splits] combined_method = " + ".join(method_names) # e.g., "Cash + Transfer" # Create receipt receipt = build_english_registration_receipt( class_code=class_code if class_code else '', student_id=student_id, level_name=level.name if level else None, level_order=level_order, session_id=session_id, form_fee_amount=form_fee_amount, registration_fee_amount=registration_fee_amount, actual_discount=actual_discount, discount_reason=discount_reason, total_amount=total_amount, payment_splits=payment_splits, payment_method=combined_method, cashier_id=current_user.id, receipt_type="enrollment" if should_enroll else "payment_only", charge_form_fee=charge_form_fee, charge_registration_fee=charge_registration_fee, additional_amount=actual_additional, additional_reason=additional_reason ) # Mark which fees were actually paid (only for payment_only receipts) if not should_enroll: receipt.form_fee_paid = charge_form_fee receipt.registration_fee_paid = charge_registration_fee if should_enroll: student.class_code = class_code student.level_name = level.name if level else None student.level_order = level_order student.english_class_id = english_class.id if english_class else None student.is_enrolled = True db.session.add(receipt) # ---- SAVE PHOTO BEFORE COMMIT (so we can rollback if fails) ---- if photo_base64 and student: try: # Save as photos/{student.id}.jpg photos_dir = 'photos' os.makedirs(photos_dir, exist_ok=True) filepath = os.path.join(photos_dir, f"{student.id}.jpg") # Decode base64 import re, base64 match = re.match(r'data:image/(?P\w+);base64,(?P.+)', photo_base64) if match: image_data = base64.b64decode(match.group('data')) else: image_data = base64.b64decode(photo_base64) with open(filepath, 'wb') as f: f.write(image_data) # Optionally store path in student (if you have a photo_path column) # student.photo_path = filepath except Exception as e: db.session.rollback() return jsonify({'error': f'Failed to save student photo: {str(e)}'}), 500 db.session.commit() print("receipt:::", receipt.to_dict()) return jsonify({ 'success': True, 'receipt': receipt.to_dict() }) @app.route('/api/english//assign-class', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def assign_class_to_paid_student(student_id): """Assign a class to a student who has already paid but is not enrolled""" student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 data = request.get_json() class_code = data.get('class_code') level_order = data.get('level_order') # Validate required fields if class_code is None: return jsonify({'error': 'class_code is required'}), 400 if not class_code: return jsonify({'error': 'Class is required'}), 400 # Check if student is already enrolled if student.is_enrolled: return jsonify({'error': 'Student is already enrolled. Use change class instead.'}), 400 # Get class info english_class = EnglishClass.query.filter_by(code=class_code).first() if not english_class: return jsonify({'error': 'Invalid class'}), 400 # Check if class has levels levels = EnglishLevel.query.filter_by(class_code=class_code).order_by(EnglishLevel.level_order).all() has_levels = len(levels) > 0 level = None if has_levels: if level_order is None: return jsonify({'error': 'Level is required for this class'}), 400 level = EnglishLevel.query.filter_by(class_code=class_code, level_order=level_order).first() if not level: return jsonify({'error': 'Invalid level'}), 400 else: level_order = None # Update student info student.class_code = class_code student.level_name = level.name if level else None student.level_order = level_order student.english_class_id = english_class.id student.is_enrolled = True try: db.session.commit() return jsonify({ 'success': True, 'message': 'Class assigned successfully', 'student': student.to_dict() }), 200 except Exception as e: db.session.rollback() return jsonify({'error': f'Failed to assign class: {str(e)}'}), 500 @app.route('/api/english//assign-class-with-payment', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def assign_class_with_payment(student_id): """Assign class to paid student, collecting any unpaid fees""" student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 data = request.get_json() class_code = data.get('class_code') level_order = data.get('level_order') payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') session_id = data.get('session_id') additional_amount = data.get('additional_amount', 0) additional_reason = data.get('additional_reason', '') # Get ALL payment_only receipts for this student (not just the latest) payment_receipts = EnglishRegistrationReceipt.query.filter_by( student_id=student_id, receipt_type='payment_only' ).all() if not payment_receipts: return jsonify({'error': 'No payment receipt found for this student'}), 400 # Validate session if provided if session_id is None: return jsonify({'error': 'session_id field is required'}), 400 session = EnglishSession.query.get(session_id) if not session: return jsonify({'error': 'Invalid session'}), 400 if not class_code: return jsonify({'error': 'Class is required'}), 400 if student.is_enrolled: return jsonify({'error': 'Student is already enrolled'}), 400 if discount_amount < 0 or additional_amount < 0: return jsonify({'error': 'Amounts cannot be negative'}), 400 if discount_amount > 0 and additional_amount > 0: return jsonify({'error': 'Cannot apply both discount and additional fee. Choose one.'}), 400 english_class = EnglishClass.query.filter_by(code=class_code).first() if not english_class: return jsonify({'error': 'Invalid class'}), 400 levels = EnglishLevel.query.filter_by(class_code=class_code).order_by(EnglishLevel.level_order).all() has_levels = len(levels) > 0 level = None if has_levels: if level_order is None: return jsonify({'error': 'Level is required for this class'}), 400 level = EnglishLevel.query.filter_by(class_code=class_code, level_order=level_order).first() if not level: return jsonify({'error': 'Invalid level'}), 400 else: level_order = None # Calculate what fees still need to be paid # form_fee_already_paid = payment_receipt.form_fee_paid # registration_fee_already_paid = payment_receipt.registration_fee_paid # Aggregate: if ANY receipt paid the fee, it's paid form_fee_already_paid = any(r.form_fee_paid for r in payment_receipts) registration_fee_already_paid = any(r.registration_fee_paid for r in payment_receipts) form_stock = FormStock.query.filter_by(class_code=class_code).first() reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first() additional_form_fee = 0 additional_registration_fee = 0 # Charge form fee if not already paid if not form_fee_already_paid: if not form_stock: return jsonify({'error': f'Form fee not configured for class {class_code}'}), 400 additional_form_fee = float(form_stock.price) if form_stock.stock_quantity <= 0: return jsonify({'error': f'No forms in stock for {class_code}'}), 400 form_stock.stock_quantity -= 1 # Charge registration fee if not already paid if not registration_fee_already_paid: if not reg_fee: return jsonify({'error': f'Registration fee not configured for class {class_code}'}), 400 additional_registration_fee = float(reg_fee.amount) subtotal = additional_form_fee + additional_registration_fee # Apply discount or additional fee actual_discount = 0 actual_additional = 0 if discount_amount > 0: actual_discount = discount_amount if actual_discount > subtotal: return jsonify({'error': f'Discount cannot exceed subtotal'}), 400 discount_percentage = (actual_discount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({'error': 'Discount reason required'}), 400 elif additional_amount > 0: actual_additional = additional_amount if not additional_reason: return jsonify({'error': 'Additional fee reason required'}), 400 total_amount = subtotal - actual_discount + actual_additional # Create receipt for additional payment if any receipt = None # if subtotal > 0: receipt = build_english_registration_receipt( class_code=class_code, student_id=student.id, level_name=level.name if level else None, level_order=level_order, session_id=session_id, form_fee_amount=additional_form_fee, registration_fee_amount=additional_registration_fee, actual_discount=actual_discount, discount_reason=discount_reason, total_amount=total_amount, payment_method=payment_method, cashier_id=current_user.id, receipt_type='enrollment', charge_form_fee=not form_fee_already_paid, charge_registration_fee=not registration_fee_already_paid, additional_amount=actual_additional, additional_reason=additional_reason ) db.session.add(receipt) # Update student - now enrolled student.class_code = class_code student.level_name = level.name if level else None student.level_order = level_order student.english_class_id = english_class.id student.is_enrolled = True db.session.commit() return jsonify({ 'success': True, 'message': 'Class assigned successfully', 'receipt': receipt.to_dict() if subtotal > 0 else None, 'student': student.to_dict(), 'fees_paid_previously': { 'form_fee': form_fee_already_paid, 'registration_fee': registration_fee_already_paid }, 'additional_payment_required': subtotal > 0, 'additional_amount': total_amount }), 200 @app.route('/api/english//complete-payment', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def complete_payment(student_id): """Record remaining fees for a student who previously made a partial payment (payment only). Does not enroll the student.""" student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 if student.is_enrolled: return jsonify({'error': 'Student is already enrolled'}), 400 data = request.get_json() payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') session_id = data.get('session_id') additional_amount = data.get('additional_amount', 0) additional_reason = data.get('additional_reason', '') payment_splits = data.get('payment_splits') # new primary field if not session_id: return jsonify({'error': 'session_id is required'}), 400 session = EnglishSession.query.get(session_id) if not session: return jsonify({'error': 'Invalid session'}), 400 # SAME LOGIC AS assign_class_with_payment (latest receipt only) payment_receipt = EnglishRegistrationReceipt.query.filter_by( student_id=student_id, receipt_type='payment_only' ).order_by(EnglishRegistrationReceipt.created_at.desc()).first() if not payment_receipt: return jsonify({'error': 'No payment receipt found for this student'}), 400 class_code = payment_receipt.class_code english_class = EnglishClass.query.filter_by(code=class_code).first() if not english_class: return jsonify({'error': f'Class {class_code} not found'}), 400 # Determine what was already paid (SAME AS assign_class_with_payment) form_fee_already_paid = payment_receipt.form_fee_paid registration_fee_already_paid = payment_receipt.registration_fee_paid form_stock = FormStock.query.filter_by(class_code=class_code).first() reg_fee = RegistrationFee.query.filter_by(class_code=class_code).first() additional_form_fee = 0 additional_registration_fee = 0 if not form_fee_already_paid: if not form_stock: return jsonify({'error': f'Form fee not configured for class {class_code}'}), 400 additional_form_fee = float(form_stock.price) if form_stock.stock_quantity <= 0: return jsonify({'error': f'No forms in stock for {class_code}'}), 400 form_stock.stock_quantity -= 1 if not registration_fee_already_paid: if not reg_fee: return jsonify({'error': f'Registration fee not configured for class {class_code}'}), 400 additional_registration_fee = float(reg_fee.amount) subtotal = additional_form_fee + additional_registration_fee if subtotal <= 0: return jsonify({'error': 'No remaining fees to pay'}), 400 if discount_amount < 0 or additional_amount < 0: return jsonify({'error': 'Amounts cannot be negative'}), 400 if discount_amount > 0 and additional_amount > 0: return jsonify({'error': 'Cannot apply both discount and additional fee'}), 400 actual_discount = 0 actual_additional = 0 if discount_amount > 0: if discount_amount > subtotal: return jsonify({'error': 'Discount cannot exceed subtotal'}), 400 actual_discount = discount_amount if not discount_reason: return jsonify({'error': 'Discount reason required'}), 400 elif additional_amount > 0: actual_additional = additional_amount if not additional_reason: return jsonify({'error': 'Additional fee reason required'}), 400 total_amount = subtotal - actual_discount + actual_additional # --- Payment splits validation --- if not payment_splits: return jsonify({'error': 'payment_splits is required'}), 400 if not isinstance(payment_splits, list) or len(payment_splits) == 0: return jsonify({'error': 'payment_splits must be a non-empty list'}), 400 total_splits = 0 allowed_methods = {'Cash', 'POS', 'Transfer'} for split in payment_splits: method = split.get('method') amount = split.get('amount') if method not in allowed_methods: return jsonify({'error': f'Invalid payment method {method}'}), 400 try: amount = float(amount) if amount <= 0: return jsonify({'error': 'Each split amount must be positive'}), 400 total_splits += amount except (TypeError, ValueError): return jsonify({'error': 'Invalid amount in payment split'}), 400 if abs(total_splits - total_amount) > 0.01: return jsonify({'error': f'Sum of split amounts (₦{total_splits:,.2f}) does not equal total (₦{total_amount:,.2f})'}), 400 method_names = [split['method'] for split in payment_splits] combined_method = " + ".join(method_names) # e.g., "Cash + Transfer" receipt = build_english_registration_receipt( class_code=class_code, student_id=student.id, level_name=None, level_order=None, session_id=session_id, form_fee_amount=additional_form_fee, registration_fee_amount=additional_registration_fee, actual_discount=actual_discount, discount_reason=discount_reason, total_amount=total_amount, payment_splits=payment_splits, payment_method=combined_method, cashier_id=current_user.id, receipt_type='payment_only', charge_form_fee=not form_fee_already_paid, charge_registration_fee=not registration_fee_already_paid, additional_amount=actual_additional, additional_reason=additional_reason ) receipt.form_fee_paid = not form_fee_already_paid receipt.registration_fee_paid = not registration_fee_already_paid db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'message': 'Remaining payment recorded successfully', 'receipt': receipt.to_dict(), 'student': student.to_dict() }), 200 @app.route('/api/english/purchase-books', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'BookSeller') def purchase_english_books(): data = request.get_json() student_id = data.get('student_id') new_student_data = data.get('new_student_data') items = data.get('items') payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') discount_type = data.get('discount_type', 'fixed') discount_value = data.get('discount_value', 0) session_id = data.get('session_id') payment_splits = data.get('payment_splits') # Validate discount if discount_amount < 0: return jsonify({'error': 'Discount amount cannot be negative'}), 400 if not items or not isinstance(items, list): return jsonify({'error': 'No items provided'}), 400 if session_id is None: return jsonify({'error': 'session_id field is required'}), 400 session = EnglishSession.query.get(session_id) if not session: return jsonify({'error': 'Invalid session'}), 400 # Start transaction try: student = None # Case 1: New student provided (from frontend) if new_student_data and not student_id: name = (new_student_data.get('name') or '').strip() phone = (new_student_data.get('phone') or '').strip() email = (new_student_data.get('email') or '').strip() # Required fields if not name or not phone: return jsonify({'error': 'Name, phone, are required'}), 400 # Email validation (ONLY if provided) if email and not is_valid_email(email=email): return jsonify({'error': 'Invalid email address'}), 400 # Create new student new_english_student = EnglishStudent( name=name, phone=phone, email=email or None ) db.session.add(new_english_student) db.session.flush() student_id = new_english_student.id student = new_english_student # Case 2: Existing student elif student_id: student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 else: return jsonify({'error': 'Student ID is required'}), 400 # Process items and calculate totals subtotal = 0 purchase_items = [] for item in items: book = EnglishBook.query.get(item.get('book_id')) if not book: return jsonify({'error': 'Book not found'}), 404 qty = item.get('quantity') if not qty or qty <= 0: return jsonify({'error': 'Invalid quantity'}), 400 if book.quantity < qty: return jsonify({'error': f'Not enough stock for {book.title}'}), 400 item_total = book.price * qty subtotal += item_total purchase_items.append({ 'book_id': book.id, 'title': book.title, 'quantity': qty, 'price': float(book.price), 'class_name': book.class_name, 'class_type': book.class_type, 'total': float(item_total) }) book.quantity -= qty # Calculate actual discount from percentage if needed actual_discount = discount_amount if discount_type == 'percentage' and discount_value > 0: actual_discount = (subtotal * min(discount_value, 100)) / 100 # Validate discount doesn't exceed subtotal if actual_discount > subtotal: return jsonify({ 'error': f'Discount amount (₦{actual_discount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 total_amount = subtotal - actual_discount # Require reason for large discounts (>50%) discount_percentage = (actual_discount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({ 'error': f'Discount reason required.', 'discount_percentage': discount_percentage }), 400 # --- Payment splits validation --- if not payment_splits: return jsonify({'error': 'payment_splits is required'}), 400 if not isinstance(payment_splits, list) or len(payment_splits) == 0: return jsonify({'error': 'payment_splits must be a non-empty list'}), 400 total_splits = 0 allowed_methods = {'Cash', 'POS', 'Transfer'} for split in payment_splits: method = split.get('method') amount = split.get('amount') if method not in allowed_methods: return jsonify({'error': f'Invalid payment method {method}'}), 400 try: amount = float(amount) if amount <= 0: return jsonify({'error': 'Each split amount must be positive'}), 400 total_splits += amount except (TypeError, ValueError): return jsonify({'error': 'Invalid amount in payment split'}), 400 if abs(total_splits - total_amount) > 0.01: return jsonify({'error': f'Sum of split amounts does not equal total'}), 400 method_names = [split['method'] for split in payment_splits] combined_method = " + ".join(method_names) # Create receipt receipt = BookPurchaseReceipt( receipt_number=generate_receipt_number(), student_id=student.id, total_amount=total_amount, discount_amount=actual_discount, discount_reason=discount_reason, payment_method=combined_method, payment_splits=payment_splits, cashier_id=current_user.id, items=purchase_items, session_id=session_id, ) db.session.add(receipt) # Commit everything together db.session.commit() return jsonify({'success': True, 'receipt': receipt.to_dict()}) except Exception as e: db.session.rollback() return jsonify({'error': f'Purchase failed: {str(e)}'}), 500 # ==================== DASHBOARD STATS ==================== @app.route('/api/english/dashboard/stats', methods=['GET']) @login_required def get_english_dashboard_stats(): if 'Director' in [role.name for role in current_user.english_roles]: stats = { 'total_users': User.query.count(), 'total_students': EnglishStudent.query.count(), 'total_books': EnglishBook.query.count(), 'total_receipts': BookPurchaseReceipt.query.count() + EnglishRegistrationReceipt.query.count(), 'recent_receipts': [] } elif 'Cashier' in [role.name for role in current_user.english_roles]: stats = { 'total_students': EnglishStudent.query.count(), 'total_books_sold': sum(r.total_amount for r in BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id)), 'total_enrollments': EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).count() } else: stats = {} return jsonify(stats) @app.route('/api/private/dashboard/stats', methods=['GET']) @login_required def get_private_dashboard_stats(): user_roles = [role.name for role in current_user.private_roles] if 'Director' in user_roles or 'Cashier' in user_roles: stats = { 'total_students': PrivateStudent.query.count(), 'total_books': PrivateBook.query.count(), 'total_receipts': PrivateEnrollmentReceipt.query.count() + PrivateBookReceipt.query.count() + PrivateUniformReceipt.query.count() + PrivateFormReceipt.query.count(), } if 'Director' in user_roles: stats['total_users'] = User.query.count() else: stats = {} return jsonify(stats) # Delete user (works for both systems) @app.route('/api/delete-user/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('english', 'Director') def delete_user(user_id): user = User.query.get(user_id) if not user: return jsonify({'error': 'User not found'}), 404 # Prevent self-deletion if user.id == current_user.id: return jsonify({'error': 'You cannot delete your own account.'}), 400 # Prevent deleting the last English Director is_english_director = any(role.name == 'Director' for role in user.english_roles) if is_english_director: total_english_directors = User.query.filter(User.english_roles.any(name='Director')).count() if total_english_directors <= 1: return jsonify({'error': 'Cannot delete the only English Director from the system'}), 400 # Prevent deleting the last Private Director is_private_director = any(role.name == 'Director' for role in user.private_roles) if is_private_director: total_private_directors = User.query.filter(User.private_roles.any(name='Director')).count() if total_private_directors <= 1: return jsonify({'error': 'Cannot delete the only Private Director from the system'}), 400 try: db.session.delete(user) db.session.commit() return jsonify({'success': True, 'message': 'User deleted successfully'}) except Exception as e: db.session.rollback() return jsonify({'error': f'Failed to delete user: {str(e)}'}), 500 @app.route('/api/new-password/', methods=["POST", "GET"]) def new_password(token): if request.method == "GET": link_entry = Link_Reset.query.filter_by(link=token).first() if not link_entry: return jsonify({"error": "Invalid or expired link. Click the button below to request another."}), 403 # Check if the link is older than 30 minutes time_diff = datetime.utcnow() - link_entry.created_at if time_diff > timedelta(minutes=15): return jsonify({"error": "The confirmation link has expired. Click the button below to request another."}), 403 return jsonify({"message": "You can enter new password below."}), 200 else: password = request.form.get("new-password") password_conf = request.form.get("confirm-password") if password == '123456': return jsonify({'error': 'Cannot change back to default password.'}), 400 if len(password) < 6: return jsonify({'error': 'Password must be at least 6 characters.'}), 400 if password_conf == password: link_entry_post = Link_Reset.query.filter_by(link=token).first() user = User.query.filter_by(email=link_entry_post.email).first() user.set_password(password) link_entry_post.link = str(uuid.uuid4()) db.session.commit() session.clear() return jsonify({"messsage": "Password reset successful. Login now."}), 200 else: return jsonify({"error": "Passwords do not match."}), 403 @app.post('/api/reset-password') def reset_password(): data = request.get_json() email = data.get('email').lower() user = User.query.filter_by(email=email).first() if not user: time.sleep(3.5) return jsonify({ 'message': f'We have sent a reset-password email if you have an account with us.'}), 200 unique_uuid = str(uuid.uuid4()) # Generate a unique token using itsdangerous token = s.dumps(email + str(unique_uuid), salt='password-reset') token += str(unique_uuid) # Combine the token with the UUID4 in the link verification_link = f"{BASE_URL_FRONTEND}/new-password/{token}" # Save link and timestamp in the database link_entry = Link_Reset(email=user.email, link=token) db.session.add(link_entry) db.session.commit() # Send email with the verification link msg = send_reset_email(email, verification_link) try: # mail.send(msg) res = send_mail_vercel(msg) if not res.get("status"): raise Exception(res.get("error")) except Exception as e: if str(e) == '[Errno 11001] getaddrinfo failed': error = 'Failed to connect. Please check your internet connection.' else: error = f'Error: {str(e)}' return jsonify({ 'error': f'{error}'}), 400 return jsonify({ 'message': f'We have sent a reset-password email if you have an account with us.'}), 200 @app.route('/api/form-stock', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier', 'InventoryManager') def get_form_stock(): forms = FormStock.query.order_by(FormStock.created_at.desc()).all() return jsonify([{ 'id': f.id, 'class_code': f.class_code, 'class_name': f.class_name, 'price': float(f.price), 'stock_quantity': f.stock_quantity, 'created_at': f.created_at.isoformat(), 'updated_at': f.updated_at.isoformat() } for f in forms]) @app.route('/api/form-stock', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def create_form_stock(): data = request.get_json() class_code = data.get('class_code') price = data.get('price') stock_quantity = data.get('stock_quantity') errors = {} if not class_code: errors['class_code'] = 'Class is required' else: # Check if form stock already exists for this class existing = FormStock.query.filter_by(class_code=class_code).first() if existing: errors['class_code'] = 'Form stock already exists for this class' try: price = float(price) if price <= 0: errors['price'] = 'Price must be greater than 0' except (TypeError, ValueError): errors['price'] = 'Invalid price' try: stock_quantity = int(stock_quantity) if stock_quantity < 0: errors['stock_quantity'] = 'Stock quantity cannot be negative' except (TypeError, ValueError): errors['stock_quantity'] = 'Invalid stock quantity' if errors: return jsonify({'success': False, 'errors': errors}), 400 # Get class name from EnglishClass english_class = EnglishClass.query.filter_by(code=class_code).first() if not english_class: return jsonify({'error': 'Invalid class code'}), 400 class_name = f"{english_class.code} - {english_class.name}" form_stock = FormStock( class_code=class_code, class_name=class_name, price=price, stock_quantity=stock_quantity ) db.session.add(form_stock) db.session.commit() return jsonify({'success': True, 'form_stock_id': form_stock.id}) @app.route('/api/form-stock/', methods=['PUT']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def update_form_stock(form_id): form_stock = FormStock.query.get(form_id) if not form_stock: return jsonify({'error': 'Form stock not found'}), 404 data = request.get_json() errors = {} price = data.get('price') stock_quantity = data.get('stock_quantity') try: price = float(price) if price <= 0: errors['price'] = 'Price must be greater than 0' except (TypeError, ValueError): errors['price'] = 'Invalid price' try: stock_quantity = int(stock_quantity) if stock_quantity < 0: errors['stock_quantity'] = 'Stock quantity cannot be negative' except (TypeError, ValueError): errors['stock_quantity'] = 'Invalid stock quantity' if errors: return jsonify({'success': False, 'errors': errors}), 400 form_stock.price = price form_stock.stock_quantity = stock_quantity db.session.commit() return jsonify({'success': True}) @app.route('/api/english/students//level-books', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def get_english_student_level_books(student_id): student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 student_status = request.args.get('student_status', 'fresh') # Get the English class to find its ID english_class = EnglishClass.query.filter_by(code=student.class_code).first() if not english_class: return jsonify({'error': 'Student class not found'}), 404 # Get all books for this student's class_id and level from EnglishBook table books = EnglishBook.query.filter_by( class_id=english_class.id, class_type='english', level=str(student.level_order) if student.level_order else None, student_status=student_status ).all() return jsonify({ 'books': [{ 'id': book.id, 'title': book.title, 'author': book.author, 'price': float(book.price), 'quantity': book.quantity, 'class_name': book.class_name, 'class_type': book.class_type } for book in books] }), 200 @app.route('/api/english/sessions', methods=['GET']) @login_required def get_english_sessions(): include_deleted = request.args.get('include_deleted', 'false').lower() == 'true' try: query = EnglishSession.query if not include_deleted: query = query.filter_by(deleted=False) sessions = query.order_by(EnglishSession.created_at.desc()).all() return jsonify([s.to_dict() for s in sessions]), 200 except Exception as e: return jsonify({'error': str(e)}), 500 @app.route('/api/english/create-session', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def create_english_session(): data = request.get_json() name = (data.get('name') or '').strip() time_text = (data.get('time') or '').strip() starting_str = data.get('starting') ending_str = data.get('ending') if not name: return jsonify({'error': 'Session name is required'}), 400 # existing = EnglishSession.query.filter_by(name=name).first() # if existing: # return jsonify({'error': 'Session name already exists'}), 400 next_index = get_next_color_index_english() session = EnglishSession(name=name, pass_color=str(next_index)) db.session.add(session) db.session.flush() # get session.id if time_text: time_entry = EnglishSessionTime(session_id=session.id, time_text=time_text) db.session.add(time_entry) if starting_str and ending_str: try: starting = datetime.fromisoformat(starting_str) ending = datetime.fromisoformat(ending_str) if starting >= ending: return jsonify({'error': 'Starting must be before ending'}), 400 period = EnglishSessionPeriod(session_id=session.id, starting=starting, ending=ending) db.session.add(period) except ValueError: return jsonify({'error': 'Invalid date format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400 db.session.commit() return jsonify({'success': True, 'session': session.to_dict()}), 201 @app.route('/api/english/update-session/', methods=['PUT']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def update_english_session(session_id): session = EnglishSession.query.get(session_id) if not session: return jsonify({'error': 'Session not found'}), 404 data = request.get_json() name = (data.get('name') or '').strip() time_text = (data.get('time') or '').strip() starting_str = data.get('starting') ending_str = data.get('ending') if not name: return jsonify({'error': 'Session name is required'}), 400 existing = EnglishSession.query.filter(EnglishSession.name == name, EnglishSession.id != session_id).first() if existing: return jsonify({'error': 'Session name already exists'}), 400 if time_text is not None: time_text = time_text.strip() if time_text: if session.time_info: session.time_info.time_text = time_text else: session.time_info = EnglishSessionTime(session_id=session.id, time_text=time_text) # If time_text is empty string, we do NOT delete; we leave as is. # Update period if both starting and ending are provided (non-None) if starting_str is not None and ending_str is not None: # If both are empty strings, we do nothing (keep existing) if starting_str and ending_str: try: starting = datetime.fromisoformat(starting_str) ending = datetime.fromisoformat(ending_str) if starting >= ending: return jsonify({'error': 'Starting must be before ending'}), 400 if session.period: session.period.starting = starting session.period.ending = ending else: session.period = EnglishSessionPeriod(session_id=session.id, starting=starting, ending=ending) except ValueError: return jsonify({'error': 'Invalid date format. Use ISO format (YYYY-MM-DDTHH:MM:SS)'}), 400 # If both are empty strings, we skip (no deletion) session.name = name db.session.commit() return jsonify({'success': True, 'session': session.to_dict()}), 200 @app.route('/api/english/delete-session/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('english', 'InventoryManager') def delete_english_session(session_id): session = EnglishSession.query.get(session_id) if not session: return jsonify({'error': 'Session not found'}), 404 session.deleted = True db.session.commit() return jsonify({'success': True, 'message': 'Session deleted'}), 200 # ==================== ANALYST ROUTES (English only) ==================== @app.route('/api/analyst/transactions', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Analyst') def get_transactions(): start_date = request.args.get('start_date') end_date = request.args.get('end_date') payment_method = request.args.get('payment_method') transaction_type = request.args.get('transaction_type') min_amount = request.args.get('min_amount', type=float) max_amount = request.args.get('max_amount', type=float) cashier_id = request.args.get('cashier_id', type=str) session_id = request.args.get('session_id', type=str) def get_method_portions(payment_splits, total_amount, discount_amount, additional_amount, selected_method, original_payment_method=None): if not payment_splits: # Old receipt: only include if the original method matches selected if original_payment_method == selected_method: return (total_amount, discount_amount, additional_amount) return (0, 0, 0) method_total = sum(split['amount'] for split in payment_splits if split['method'] == selected_method) grand_total = sum(split['amount'] for split in payment_splits) if grand_total == 0: return (0, 0, 0) ratio = method_total / grand_total return (total_amount * ratio, discount_amount * ratio, additional_amount * ratio) transactions = [] # Registration receipts for r in EnglishRegistrationReceipt.query.all(): if session_id and str(r.session_id) != session_id: continue subtotal = (float(r.form_fee_amount) if r.charge_form_fee else 0) + (float(r.registration_fee_amount) if r.charge_registration_fee else 0) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 additional_reason = r.additional_reason or '' session_obj = r.session session_data = { 'session_id': str(session_obj.id) if session_obj else None, 'session_name': session_obj.name if session_obj else None, 'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None, 'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None, 'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None, } # Form fee if r.charge_form_fee and r.form_fee_amount > 0: amount = float(r.form_fee_amount) discount_ratio = amount / subtotal if subtotal > 0 else 0 item_discount = total_discount * discount_ratio if total_discount > 0 else 0 item_additional = total_additional * discount_ratio if total_additional > 0 else 0 item_total = amount - item_discount + item_additional transactions.append({ 'id': f"{r.id}_form", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'form', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': amount, 'discount_amount': item_discount, 'additional_amount': item_additional, 'discount_reason': r.discount_reason if r.discount_amount > 0 else None, 'additional_reason': additional_reason, 'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0, 'payment_method': r.payment_method, 'payment_splits': r.payment_splits, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.class_code, 'level_name': r.level_name, 'item_title': 'Form Fee', 'quantity': 1, **session_data, }) # Registration fee if r.charge_registration_fee and r.registration_fee_amount > 0: amount = float(r.registration_fee_amount) discount_ratio = amount / subtotal if subtotal > 0 else 0 item_discount = total_discount * discount_ratio if total_discount > 0 else 0 item_additional = total_additional * discount_ratio if total_additional > 0 else 0 item_total = amount - item_discount + item_additional transactions.append({ 'id': f"{r.id}_reg", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'registration', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': amount, 'discount_amount': item_discount, 'additional_amount': item_additional, 'discount_reason': r.discount_reason if r.discount_amount > 0 else None, 'additional_reason': additional_reason, 'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0, 'payment_method': r.payment_method, 'payment_splits': r.payment_splits, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.class_code, 'level_name': r.level_name, 'item_title': 'Registration Fee', 'quantity': 1, **session_data, }) # Book receipts for r in BookPurchaseReceipt.query.all(): if session_id and str(r.session_id) != session_id: continue if not (r.items and isinstance(r.items, list)): continue subtotal = (r.total_amount or 0) + (r.discount_amount or 0) for item in r.items: class_type = item.get('class_type', 'general') transaction_type_value = f"book_{class_type}" item_subtotal = item.get('total', 0) discount_ratio = item_subtotal / subtotal if subtotal > 0 else 0 item_discount = (r.discount_amount or 0) * discount_ratio item_total = item_subtotal - item_discount session_obj = r.session session_data = { 'session_id': str(session_obj.id) if session_obj else None, 'session_name': session_obj.name if session_obj else None, 'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None, 'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None, 'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None, } transactions.append({ 'id': f"{r.id}_{item.get('book_id')}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': transaction_type_value, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': item_subtotal, 'discount_amount': item_discount, 'additional_amount': 0, 'discount_reason': r.discount_reason, 'additional_reason': '', 'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0, 'payment_method': r.payment_method, 'payment_splits': r.payment_splits, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': None, 'level_name': None, 'item_title': item.get('title'), 'quantity': item.get('quantity'), **session_data, }) # Apply filters filtered = [] for t in transactions: if payment_method: portions = get_method_portions( t.get('payment_splits'), t['amount'], t['discount_amount'], t['additional_amount'], payment_method, t.get('payment_method') ) amount_adj, discount_adj, additional_adj = portions if amount_adj <= 0: continue t = t.copy() t['amount'] = amount_adj t['discount_amount'] = discount_adj t['additional_amount'] = additional_adj t['discount_percentage'] = (discount_adj / amount_adj * 100) if amount_adj > 0 else 0 if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if t['date'] < start: continue if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if t['date'] > end: continue if transaction_type and t['type'] != transaction_type: continue if min_amount is not None and t['amount'] < min_amount: continue if max_amount is not None and t['amount'] > max_amount: continue if cashier_id is not None and str(t['cashier_id']) != str(cashier_id): continue filtered.append(t) filtered.sort(key=lambda x: x['date'], reverse=True) total_amount = sum(t['amount'] for t in filtered) total_discount = sum(t['discount_amount'] for t in filtered) return jsonify({ 'transactions': [{**t, 'date': t['date'].isoformat()} for t in filtered], 'total_count': len(filtered), 'total_amount': total_amount, 'total_discount': total_discount }) def get_method_portions(payment_splits, total_amount, discount_amount, additional_amount, selected_method): """Return (amount, discount, additional) for a specific method based on split proportions.""" if not payment_splits: # Old receipt: use the full amounts if method matches return (total_amount, discount_amount, additional_amount) if total_amount > 0 else (0,0,0) # Find total paid for selected method method_total = sum(split['amount'] for split in payment_splits if split['method'] == selected_method) grand_total = sum(split['amount'] for split in payment_splits) if grand_total == 0: return (0,0,0) ratio = method_total / grand_total return (total_amount * ratio, discount_amount * ratio, additional_amount * ratio) @app.route('/api/analyst/students', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Analyst') def get_analyst_students(): """Get all English students with their enrollment and spending details.""" class_filter = request.args.get('class_code') level_order = request.args.get('level_order', type=int) students = EnglishStudent.query.all() result = [] for s in students: # Apply class filter if class_filter: if class_filter == 'not_enrolled' and s.class_code: continue elif class_filter != 'not_enrolled' and s.class_code != class_filter: continue # Apply level filter if level_order is not None and s.level_order != level_order: continue receipts = [] total_spent = 0.0 # Count actual receipts from the two receipt models registration_receipts_count = s.registration_receipts.count() book_receipts_count = s.book_receipts.count() receipts_count = registration_receipts_count + book_receipts_count # Build receipts array for display (splitting form/reg fees and book items for detailed view) # Registration receipts - split into form & reg for display for r in s.registration_receipts: # Form fee if r.charge_form_fee and r.form_fee_amount and float(r.form_fee_amount) > 0: amount = float(r.form_fee_amount) total_spent += amount receipts.append({ 'receipt_number': r.receipt_number, 'date': r.created_at.isoformat(), 'type': 'form', 'amount': amount, 'payment_method': r.payment_method }) # Registration fee if r.charge_registration_fee and r.registration_fee_amount and float(r.registration_fee_amount) > 0: amount = float(r.registration_fee_amount) total_spent += amount receipts.append({ 'receipt_number': r.receipt_number, 'date': r.created_at.isoformat(), 'type': 'registration', 'amount': amount, 'payment_method': r.payment_method }) # Book purchase receipts – each item as separate receipt entry for display for r in s.book_receipts: if r.items and isinstance(r.items, list): # Calculate total before discount for proportional distribution items_subtotal = sum(float(item.get('total', 0)) for item in r.items) total_discount = float(r.discount_amount) if r.discount_amount else 0 for item in r.items: item_subtotal = float(item.get('total', 0)) # Calculate proportional discount for this item item_discount = 0 if total_discount > 0 and items_subtotal > 0: discount_ratio = item_subtotal / items_subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount if item_total > 0: total_spent += item_total receipts.append({ 'receipt_number': r.receipt_number, 'date': r.created_at.isoformat(), 'type': f"book_{item.get('class_type', 'general')}", 'amount': item_total, 'payment_method': r.payment_method }) else: # Fallback for old receipts without items amount = float(r.total_amount) if r.total_amount else 0 if amount > 0: total_spent += amount receipts.append({ 'receipt_number': r.receipt_number, 'date': r.created_at.isoformat(), 'type': 'book_purchase', 'amount': amount, 'payment_method': r.payment_method }) receipts.sort(key=lambda x: x['date'], reverse=True) result.append({ 'id': s.id, 'name': s.name, 'phone': s.phone, 'email': s.email or '', 'student_type': 'english', 'class_code': s.class_code or 'Not Enrolled', 'level_name': s.level_name or '', 'level_order': s.level_order, 'total_spent': total_spent, 'receipts_count': receipts_count, # Now counts actual receipts, not split items 'receipts': receipts, # Still shows split items for detailed view 'created_at': s.created_at.isoformat() }) # Apply class filter again if class_filter: if class_filter == 'not_enrolled': result = [s for s in result if s['class_code'] == 'Not Enrolled'] elif class_filter != 'all': result = [s for s in result if s['class_code'] == class_filter] result.sort(key=lambda x: x['name']) return jsonify(result) @app.route('/api/analyst/summary', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Analyst') def get_analyst_summary(): start_date = request.args.get('start_date') end_date = request.args.get('end_date') all_items = [] # each item: {'date', 'amount', 'type', 'payment_splits'} or {'payment_method'} # Registration receipts – split form/reg with proportional discount & additional fee for r in EnglishRegistrationReceipt.query.all(): subtotal = 0 if r.charge_form_fee: subtotal += float(r.form_fee_amount) if r.charge_registration_fee: subtotal += float(r.registration_fee_amount) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 # Helper to add an item (form or registration fee) def add_item(amount, item_type, receipt): if amount <= 0: return if receipt.payment_splits: all_items.append({ 'date': receipt.created_at, 'amount': amount, 'type': item_type, 'payment_splits': receipt.payment_splits, }) else: all_items.append({ 'date': receipt.created_at, 'amount': amount, 'type': item_type, 'payment_method': receipt.payment_method, }) # Form fee if r.charge_form_fee and r.form_fee_amount > 0: amount = float(r.form_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional add_item(item_total, 'form', r) # Registration fee if r.charge_registration_fee and r.registration_fee_amount > 0: amount = float(r.registration_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional add_item(item_total, 'registration', r) # Book receipts for r in BookPurchaseReceipt.query.all(): if r.items and isinstance(r.items, list): items_subtotal = sum(float(item.get('total', 0)) for item in r.items) total_discount = float(r.discount_amount) if r.discount_amount else 0 for item in r.items: class_type = item.get('class_type', 'general') item_type = f"book_{class_type}" item_subtotal = float(item.get('total', 0)) item_discount = 0 if total_discount > 0 and items_subtotal > 0: discount_ratio = item_subtotal / items_subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount if item_total > 0: if r.payment_splits: all_items.append({ 'date': r.created_at, 'amount': item_total, 'type': item_type, 'payment_splits': r.payment_splits, }) else: all_items.append({ 'date': r.created_at, 'amount': item_total, 'type': item_type, 'payment_method': r.payment_method, }) else: amount = float(r.total_amount) if r.total_amount else 0 if amount > 0: if r.payment_splits: all_items.append({ 'date': r.created_at, 'amount': amount, 'type': 'book_purchase', 'payment_splits': r.payment_splits, }) else: all_items.append({ 'date': r.created_at, 'amount': amount, 'type': 'book_purchase', 'payment_method': r.payment_method, }) # Apply date filters if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') all_items = [i for i in all_items if i['date'] >= start] if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') all_items = [i for i in all_items if i['date'] <= end] total_revenue = sum(i['amount'] for i in all_items) total_transactions = len(all_items) by_type = {} by_payment = {} for item in all_items: # Aggregate by type by_type[item['type']] = by_type.get(item['type'], 0) + item['amount'] # Aggregate by payment method, handling splits if 'payment_splits' in item and item['payment_splits']: # For split payments, distribute the amount proportionally total_split_sum = sum(split['amount'] for split in item['payment_splits']) if total_split_sum > 0: for split in item['payment_splits']: method = split['method'] ratio = split['amount'] / total_split_sum method_amount = item['amount'] * ratio by_payment[method] = by_payment.get(method, 0) + method_amount else: # Single method (old receipts) method = item['payment_method'] by_payment[method] = by_payment.get(method, 0) + item['amount'] enrolled_students = EnglishStudent.query.filter(EnglishStudent.class_code.isnot(None)).count() total_students = EnglishStudent.query.count() return jsonify({ 'total_revenue': round(total_revenue, 2), 'total_transactions': total_transactions, 'form_revenue': round(by_type.get('form', 0), 2), 'registration_revenue': round(by_type.get('registration', 0), 2), 'books_revenue': round(by_type.get('book_english', 0) + by_type.get('book_general', 0) + by_type.get('book_purchase', 0), 2), 'english_books_revenue': round(by_type.get('book_english', 0), 2), 'general_books_revenue': round(by_type.get('book_general', 0), 2), 'by_type': {k: round(v, 2) for k, v in by_type.items()}, 'by_payment': {k: round(v, 2) for k, v in by_payment.items()}, 'enrolled_students': enrolled_students, 'total_students': total_students }) @app.route('/api/analyst/cashiers', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Analyst') def get_cashiers(): """Get list of users with Cashier role in English system.""" # Assuming EnglishRole table has a 'Cashier' role cashier_role = EnglishRole.query.filter_by(name='Cashier').first() if not cashier_role: return jsonify([]) cashiers = cashier_role.users.all() # backref 'users' from EnglishRole return jsonify([{'id': u.id, 'name': u.name} for u in cashiers]) # Export endpoints (simplified) @app.route('/api/analyst/export', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Analyst') def export_data(): """Export full data (unfiltered) to Excel.""" data = request.get_json() or {} export_type = data.get('type') # 'transactions' or 'students' return _build_export(export_type, filters={}) @app.route('/api/analyst/export-filtered', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Analyst') def export_filtered_data(): """Export filtered data to Excel.""" data = request.get_json() or {} export_type = data.get('type') filters = data.get('filters', {}) return _build_export(export_type, filters) def _build_export(export_type, filters): """Helper to build Excel export for transactions or students.""" wb = Workbook() wb.remove(wb.active) header_font = Font(bold=True, size=11) title_font = Font(bold=True, size=14) label_font = Font(bold=True, size=11) value_font = Font(size=11) def format_phone(phone): return str(phone) if phone else 'N/A' def safe_date(date_str, end=False): if not date_str: return None try: return datetime.fromisoformat(date_str + ('T23:59:59' if end else '')) except: return None def auto_width(ws, max_width=40): for col in ws.columns: max_length = 0 col_letter = None for cell in col: if isinstance(cell, openpyxl.cell.cell.MergedCell): continue if col_letter is None: col_letter = cell.column_letter try: if cell.value: max_length = max(max_length, len(str(cell.value))) except: pass if col_letter: ws.column_dimensions[col_letter].width = min(max_length + 2, max_width) def format_currency_cells(ws, currency_columns, start_row=2): for row in ws.iter_rows(min_row=start_row): for cell in row: if cell.column in currency_columns and isinstance(cell.value, (int, float)): cell.number_format = '#,##0' # Helper to compute split amounts for a receipt item def get_split_amounts(payment_splits, net_amount, original_payment_method=None): """ Returns a dict with cash_amount, pos_amount, transfer_amount. For old receipts (payment_splits is None), the full net_amount goes into the column matching original_payment_method. For new receipts, net_amount is split proportionally. """ if not payment_splits: # Old receipt: put all net amount into the method column method = original_payment_method.lower() if original_payment_method else '' if method == 'cash': return {'cash_amount': net_amount, 'pos_amount': 0, 'transfer_amount': 0} elif method == 'pos': return {'cash_amount': 0, 'pos_amount': net_amount, 'transfer_amount': 0} elif method == 'transfer': return {'cash_amount': 0, 'pos_amount': 0, 'transfer_amount': net_amount} else: return {'cash_amount': 0, 'pos_amount': 0, 'transfer_amount': 0} # New receipt with splits grand_total = sum(split['amount'] for split in payment_splits) if grand_total == 0: return {'cash_amount': 0, 'pos_amount': 0, 'transfer_amount': 0} result = {'cash_amount': 0, 'pos_amount': 0, 'transfer_amount': 0} for split in payment_splits: method = split['method'].lower() ratio = split['amount'] / grand_total net_portion = net_amount * ratio if method == 'cash': result['cash_amount'] = net_portion elif method == 'pos': result['pos_amount'] = net_portion elif method == 'transfer': result['transfer_amount'] = net_portion return result # Determine if filters are present (payment_method ignored for export) if export_type == 'transactions': has_filters = any([ filters.get('start_date'), filters.get('end_date'), filters.get('transaction_type'), filters.get('cashier_id'), filters.get('min_amount'), filters.get('max_amount'), filters.get('search_query'), filters.get('session_id') ]) else: # students has_filters = any([ filters.get('class_code'), filters.get('level_order'), filters.get('search_query') ]) # ============ SUMMARY SHEET ============ summary_ws = wb.create_sheet("Summary") title_cell = summary_ws['A1'] title_cell.value = f"{'FILTERED' if has_filters else 'ALL'} {export_type.upper()} EXPORT - SUMMARY" title_cell.font = Font(bold=True, size=14) summary_ws.merge_cells('A1:D1') summary_ws['A3'] = "Export Date:" summary_ws['B3'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S') summary_ws['A3'].font = Font(bold=True, size=11) summary_ws['B3'].font = Font(size=11) row = 5 summary_ws[f'A{row}'] = "SUMMARY STATISTICS" summary_ws[f'A{row}'].font = Font(bold=True, size=12) row += 2 if export_type == 'transactions': revenue_row = row summary_ws[f'A{revenue_row}'] = "Total Revenue:" summary_ws[f'B{revenue_row}'] = "Pending calculation" summary_ws[f'A{revenue_row}'].font = Font(bold=True, size=11) row += 1 transactions_row = row summary_ws[f'A{transactions_row}'] = "Total Transactions:" summary_ws[f'B{transactions_row}'] = "Pending calculation" summary_ws[f'A{transactions_row}'].font = Font(bold=True, size=11) row += 1 else: students_row = row summary_ws[f'A{students_row}'] = "Total Students:" summary_ws[f'B{students_row}'] = "Pending calculation" summary_ws[f'A{students_row}'].font = Font(bold=True, size=11) row += 1 spent_row = row summary_ws[f'A{spent_row}'] = "Total Spent:" summary_ws[f'B{spent_row}'] = "Pending calculation" summary_ws[f'A{spent_row}'].font = Font(bold=True, size=11) row += 1 row += 1 if has_filters: summary_ws[f'A{row}'] = "FILTERS APPLIED" summary_ws[f'A{row}'].font = Font(bold=True, size=12) row += 2 if export_type == 'transactions': if filters.get('start_date'): summary_ws[f'A{row}'] = "Start Date:"; summary_ws[f'B{row}'] = filters['start_date']; row += 1 if filters.get('end_date'): summary_ws[f'A{row}'] = "End Date:"; summary_ws[f'B{row}'] = filters['end_date']; row += 1 if filters.get('transaction_type'): summary_ws[f'A{row}'] = "Transaction Type:"; summary_ws[f'B{row}'] = filters['transaction_type']; row += 1 if filters.get('cashier_id'): cashier = User.query.get(filters['cashier_id']) summary_ws[f'A{row}'] = "Cashier:"; summary_ws[f'B{row}'] = cashier.name if cashier else filters['cashier_id']; row += 1 if filters.get('session_id'): session = EnglishSession.query.get(filters['session_id']) label = session.name if session else '' if session and session.time_info and session.time_info.time_text: label += f' • {session.time_info.time_text}' summary_ws[f'A{row}'] = "Session:"; summary_ws[f'B{row}'] = label; row += 1 if filters.get('min_amount'): summary_ws[f'A{row}'] = "Min Amount:"; summary_ws[f'B{row}'] = f"₦{float(filters['min_amount']):,.0f}"; row += 1 if filters.get('max_amount'): summary_ws[f'A{row}'] = "Max Amount:"; summary_ws[f'B{row}'] = f"₦{float(filters['max_amount']):,.0f}"; row += 1 if filters.get('search_query'): summary_ws[f'A{row}'] = "Search Query:"; summary_ws[f'B{row}'] = filters['search_query']; row += 1 else: if filters.get('class_code'): class_val = filters['class_code'] display_class = "Not Enrolled" if class_val == 'not_enrolled' else class_val summary_ws[f'A{row}'] = "Class:"; summary_ws[f'B{row}'] = display_class; row += 1 if filters.get('level_order'): summary_ws[f'A{row}'] = "Level:"; summary_ws[f'B{row}'] = f"Level {filters['level_order']}"; row += 1 if filters.get('search_query'): summary_ws[f'A{row}'] = "Search Query:"; summary_ws[f'B{row}'] = filters['search_query']; row += 1 summary_ws.column_dimensions['A'].width = 20 summary_ws.column_dimensions['B'].width = 30 # ============ DATA SHEET ============ if export_type == 'transactions': start_dt = safe_date(filters.get('start_date')) end_dt = safe_date(filters.get('end_date'), end=True) # payment_method filter is NOT applied in export transaction_type_filter = filters.get('transaction_type') min_amount = filters.get('min_amount') max_amount = filters.get('max_amount') cashier_id = filters.get('cashier_id') session_id = filters.get('session_id') search_query_raw = filters.get('search_query') search_query = search_query_raw.lower() if search_query_raw else '' def match_date(d): return (not start_dt or d >= start_dt) and (not end_dt or d <= end_dt) def match_search(text): if not search_query: return True return search_query in str(text).lower() rows = [] total_revenue = 0 # --- Registration receipts --- for r in EnglishRegistrationReceipt.query.all(): if session_id and str(r.session_id) != session_id: continue if not match_date(r.created_at): continue if cashier_id and r.cashier_id != cashier_id: continue session_obj = r.session session_label = format_session_label(session_obj) if session_obj else '' session_starting = session_obj.period.starting.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else '' session_ending = session_obj.period.ending.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else '' total_additional = float(r.additional_amount) if r.additional_amount else 0 additional_reason = r.additional_reason or '' # Form fee if r.charge_form_fee and r.form_fee_amount > 0: if transaction_type_filter and transaction_type_filter != 'form': pass else: amount = float(r.form_fee_amount) if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): pass elif match_search(r.receipt_number) or match_search(r.student.name): total_discount = float(r.discount_amount) if r.discount_amount else 0 subtotal = (float(r.form_fee_amount) if r.charge_form_fee else 0) + (float(r.registration_fee_amount) if r.charge_registration_fee else 0) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional splits = get_split_amounts(r.payment_splits, item_total, r.payment_method) rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'form', r.student.name, format_phone(r.student.phone), r.student.email or '-', 'Form Fee', 1, amount, item_discount, r.discount_reason or '-', item_additional, additional_reason, item_total, splits['cash_amount'], splits['pos_amount'], splits['transfer_amount'], r.payment_method, r.cashier.name, session_label, session_starting, session_ending ]) total_revenue += item_total # Registration fee if r.charge_registration_fee and r.registration_fee_amount > 0: if transaction_type_filter and transaction_type_filter != 'registration': pass else: amount = float(r.registration_fee_amount) if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): pass elif match_search(r.receipt_number) or match_search(r.student.name): total_discount = float(r.discount_amount) if r.discount_amount else 0 subtotal = (float(r.form_fee_amount) if r.charge_form_fee else 0) + (float(r.registration_fee_amount) if r.charge_registration_fee else 0) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional splits = get_split_amounts(r.payment_splits, item_total, r.payment_method) rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'registration', r.student.name, format_phone(r.student.phone), r.student.email or '-', 'Registration Fee', 1, amount, item_discount, r.discount_reason or '-', item_additional, additional_reason, item_total, splits['cash_amount'], splits['pos_amount'], splits['transfer_amount'], r.payment_method, r.cashier.name, session_label, session_starting, session_ending ]) total_revenue += item_total # --- Book receipts --- for r in BookPurchaseReceipt.query.all(): if session_id and str(r.session_id) != session_id: continue if not match_date(r.created_at): continue if cashier_id and r.cashier_id != cashier_id: continue session_obj = r.session session_label = format_session_label(session_obj) if session_obj else '' session_starting = session_obj.period.starting.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else '' session_ending = session_obj.period.ending.strftime('%Y-%m-%d %H:%M') if session_obj and session_obj.period else '' if r.items: subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) for item in r.items: class_type = item.get('class_type', 'general') transaction_type_value = f"book_{class_type}" if transaction_type_filter and transaction_type_filter != transaction_type_value: continue item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if float(r.discount_amount or 0) > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = float(r.discount_amount) * discount_ratio item_total = item_subtotal - item_discount item_additional = 0 additional_reason = '-' if match_search(r.receipt_number) or match_search(r.student.name) or match_search(item.get('title')): splits = get_split_amounts(r.payment_splits, item_total, r.payment_method) rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), transaction_type_value, r.student.name, format_phone(r.student.phone), r.student.email or '-', item.get('title'), item.get('quantity'), item_subtotal, item_discount, r.discount_reason or '-', item_additional, additional_reason, item_total, splits['cash_amount'], splits['pos_amount'], splits['transfer_amount'], r.payment_method, r.cashier.name, session_label, session_starting, session_ending ]) total_revenue += item_total rows.sort(key=lambda x: x[1], reverse=True) summary_ws[f'B{revenue_row}'] = f"₦{total_revenue:,.0f}" summary_ws[f'B{transactions_row}'] = len(rows) data_ws = wb.create_sheet("Transaction Data") headers = [ 'Receipt Number', 'Date', 'Transaction Type', 'Student Name', 'Phone', 'Email', 'Item Name', 'Quantity', 'Subtotal', 'Discount Amt', 'Discount Reason', 'Additional Amt', 'Additional Amt Reason', 'Total', 'Cash_Amount', 'POS_Amount', 'Transfer_Amount', 'Payment Method', 'Cashier', 'Session', 'Starting', 'Ending' ] data_ws.append(headers) for cell in data_ws[1]: cell.font = header_font cell.alignment = Alignment(horizontal="center", vertical="center") for row_data in rows: data_ws.append(row_data) currency_columns = [9, 10, 12, 14, 15, 16] # Subtotal, Discount, Additional, Total, Cash, POS, Transfer format_currency_cells(data_ws, currency_columns) auto_width(data_ws, 50) else: # export_type == 'students' class_filter = filters.get('class_code') level_order = filters.get('level_order') search_query_raw = filters.get('search_query') search_query = search_query_raw.lower() if search_query_raw else '' rows = [] students = EnglishStudent.query.all() total_students_count = 0 total_spent_all = 0 for s in students: if class_filter: if class_filter == 'not_enrolled' and s.class_code: continue elif class_filter != 'not_enrolled' and s.class_code != class_filter: continue if level_order is not None and s.level_order != level_order: continue if search_query: if not (search_query in s.name.lower() or search_query in s.phone.lower() or (s.email and search_query in s.email.lower())): continue total_spent = 0 receipt_count = s.registration_receipts.count() + s.book_receipts.count() for r in s.registration_receipts: # Calculate subtotal of this receipt (sum of selected fees) subtotal = 0 if r.charge_form_fee: subtotal += float(r.form_fee_amount) if r.form_fee_amount else 0 if r.charge_registration_fee: subtotal += float(r.registration_fee_amount) if r.registration_fee_amount else 0 total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 # Form fee if r.charge_form_fee and r.form_fee_amount: amount = float(r.form_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio total_spent += (amount - item_discount + item_additional) # Registration fee if r.charge_registration_fee and r.registration_fee_amount: amount = float(r.registration_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio total_spent += (amount - item_discount + item_additional) for r in s.book_receipts: if r.items: subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio total_spent += (item_subtotal - item_discount) else: total_spent += float(r.total_amount or 0) rows.append({ 'name': s.name, 'phone': format_phone(s.phone), 'email': s.email or '-', 'class_code': s.class_code or 'Not Enrolled', 'level_name': s.level_name or '-', 'created_at': s.created_at.strftime('%Y-%m-%d') if s.created_at else 'N/A', 'total_spent': total_spent, 'receipts_count': receipt_count }) total_students_count += 1 total_spent_all += total_spent summary_ws[f'B{students_row}'] = total_students_count summary_ws[f'B{spent_row}'] = f"₦{total_spent_all:,.0f}" data_ws = wb.create_sheet("Student Data") headers = ['Name', 'Phone', 'Email', 'Class', 'Level', 'Registration Date', 'Total Spent', 'Receipts Count'] data_ws.append(headers) for cell in data_ws[1]: cell.font = header_font cell.alignment = Alignment(horizontal="center", vertical="center") for row_data in rows: data_ws.append([row_data['name'], row_data['phone'], row_data['email'], row_data['class_code'], row_data['level_name'], row_data['created_at'], row_data['total_spent'], row_data['receipts_count']]) format_currency_cells(data_ws, currency_columns=[7]) auto_width(data_ws, 40) if 'Sheet' in wb.sheetnames: del wb['Sheet'] output = BytesIO() wb.save(output) output.seek(0) filename = f"{export_type}_{'filtered' if has_filters else 'full'}_export.xlsx" return Response( output.getvalue(), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', headers={'Content-Disposition': f'attachment; filename={filename}'} ) # ==================== CASHIER MY ANALYSIS (English) ==================== @app.route('/api/english/cashier/transactions', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def get_english_cashier_transactions(): """Get transactions for the logged-in cashier, including additional fee.""" start_date = request.args.get('start_date') end_date = request.args.get('end_date') payment_method = request.args.get('payment_method') transaction_type = request.args.get('transaction_type') min_amount = request.args.get('min_amount', type=float) max_amount = request.args.get('max_amount', type=float) session_id = request.args.get('session_id', type=str) all_transactions = [] # Registration receipts – split form & registration with discount AND additional fee for r in EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).all(): if session_id and str(r.session_id) != session_id: continue if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if r.created_at < start: continue if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if r.created_at > end: continue if payment_method and r.payment_method != payment_method: continue subtotal = 0 if r.charge_form_fee: subtotal += float(r.form_fee_amount) if r.charge_registration_fee: subtotal += float(r.registration_fee_amount) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 additional_reason = r.additional_reason or '' session_obj = r.session session_data = { 'session_id': str(session_obj.id) if session_obj else None, 'session_name': session_obj.name if session_obj else None, 'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None, 'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None, 'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None, } # Form fee if r.charge_form_fee and r.form_fee_amount > 0: if transaction_type and transaction_type != 'form': pass else: amount = float(r.form_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount): pass else: all_transactions.append({ 'id': f"{r.id}_form", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'form', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': amount, 'discount_amount': item_discount, 'additional_amount': item_additional, 'discount_reason': r.discount_reason if r.discount_amount else None, 'additional_reason': additional_reason, 'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.class_code, 'level_name': r.level_name, 'item_title': 'Form Fee', 'quantity': 1, **session_data }) # Registration fee if r.charge_registration_fee and r.registration_fee_amount > 0: if transaction_type and transaction_type != 'registration': pass else: amount = float(r.registration_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount): pass else: all_transactions.append({ 'id': f"{r.id}_reg", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'registration', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': amount, 'discount_amount': item_discount, 'additional_amount': item_additional, 'discount_reason': r.discount_reason if r.discount_amount else None, 'additional_reason': additional_reason, 'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.class_code, 'level_name': r.level_name, 'item_title': 'Registration Fee', 'quantity': 1, **session_data }) # Book receipts – no additional fee for r in BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id).all(): if session_id and str(r.session_id) != session_id: continue if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if r.created_at < start: continue if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if r.created_at > end: continue if payment_method and r.payment_method != payment_method: continue session_obj = r.session session_data = { 'session_id': str(session_obj.id) if session_obj else None, 'session_name': session_obj.name if session_obj else None, 'session_time_text': session_obj.time_info.time_text if session_obj and session_obj.time_info else None, 'session_starting': session_obj.period.starting.isoformat() if session_obj and session_obj.period else None, 'session_ending': session_obj.period.ending.isoformat() if session_obj and session_obj.period else None, } if r.items: subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) for item in r.items: class_type = item.get('class_type', 'general') txn_type = f"book_{class_type}" if transaction_type and transaction_type != txn_type: continue item_subtotal = float(item.get('total', 0)) item_discount = 0 if float(r.discount_amount or 0) > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = float(r.discount_amount) * discount_ratio item_total = item_subtotal - item_discount if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount): continue all_transactions.append({ 'id': f"{r.id}_{item.get('book_id')}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': txn_type, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': item_subtotal, 'discount_amount': item_discount, 'additional_amount': 0, 'discount_reason': r.discount_reason, 'additional_reason': '', 'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': None, 'level_name': None, 'item_title': item.get('title'), 'quantity': item.get('quantity'), **session_data }) else: amount = float(r.total_amount) if r.total_amount else 0 txn_type = 'book_purchase' if transaction_type and transaction_type != txn_type: continue if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue all_transactions.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': txn_type, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': amount, 'subtotal': amount, 'discount_amount': 0, 'additional_amount': 0, 'discount_reason': r.discount_reason, 'additional_reason': '', 'discount_percentage': 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': None, 'level_name': None, 'item_title': 'Book Purchase', 'quantity': 1, **session_data }) all_transactions.sort(key=lambda x: x['date'], reverse=True) total_amount = sum(t['amount'] for t in all_transactions) total_discount = sum(t['discount_amount'] for t in all_transactions) return jsonify({ 'transactions': [{**t, 'date': t['date'].isoformat()} for t in all_transactions], 'total_count': len(all_transactions), 'total_amount': total_amount, 'total_discount': total_discount }) @app.route('/api/english/cashier/summary', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def get_english_cashier_summary(): """Summary for the logged-in cashier with proportional discount and additional fee.""" start_date = request.args.get('start_date') end_date = request.args.get('end_date') payment_method = request.args.get('payment_method') transaction_type = request.args.get('transaction_type') min_amount = request.args.get('min_amount', type=float) max_amount = request.args.get('max_amount', type=float) session_id = request.args.get('session_id') # optional now_local = datetime.now() today_start_local = datetime(now_local.year, now_local.month, now_local.day, 0, 0, 0) tomorrow_start_local = today_start_local + timedelta(days=1) total_revenue = 0.0 total_transactions = 0 revenue_today = 0.0 # Registration receipts – split form & registration with proportional discount AND additional fee for r in EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).all(): if session_id and str(r.session_id) != session_id: continue if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if r.created_at < start: continue if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if r.created_at > end: continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type not in ['form', 'registration']: continue date_utc = r.created_at date_local = date_utc is_today_local = (today_start_local <= date_local < tomorrow_start_local) subtotal = 0 if r.charge_form_fee: subtotal += float(r.form_fee_amount) if r.charge_registration_fee: subtotal += float(r.registration_fee_amount) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 # Form fee if r.charge_form_fee and r.form_fee_amount > 0: amount = float(r.form_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional if (min_amount is None or item_total >= min_amount) and (max_amount is None or item_total <= max_amount): total_revenue += item_total total_transactions += 1 if is_today_local: revenue_today += item_total # Registration fee if r.charge_registration_fee and r.registration_fee_amount > 0: amount = float(r.registration_fee_amount) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = amount / subtotal item_discount = total_discount * discount_ratio item_additional = 0 if total_additional > 0 and subtotal > 0: additional_ratio = amount / subtotal item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional if (min_amount is None or item_total >= min_amount) and (max_amount is None or item_total <= max_amount): total_revenue += item_total total_transactions += 1 if is_today_local: revenue_today += item_total # Book receipts (no additional fee field) for r in BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id).all(): if session_id and str(r.session_id) != session_id: continue if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if r.created_at < start: continue if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if r.created_at > end: continue if payment_method and r.payment_method != payment_method: continue date_utc = r.created_at date_local = date_utc is_today_local = (today_start_local <= date_local < tomorrow_start_local) if r.items: items_subtotal = sum(float(item.get('total', 0)) for item in r.items) total_discount = float(r.discount_amount) if r.discount_amount else 0 for item in r.items: class_type = item.get('class_type', 'general') txn_type = f"book_{class_type}" if transaction_type and transaction_type != txn_type: continue item_subtotal = float(item.get('total', 0)) item_discount = 0 if total_discount > 0 and items_subtotal > 0: discount_ratio = item_subtotal / items_subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount if (min_amount is not None and item_total < min_amount) or (max_amount is not None and item_total > max_amount): continue total_revenue += item_total total_transactions += 1 if is_today_local: revenue_today += item_total else: amount = float(r.total_amount) if r.total_amount else 0 if transaction_type and transaction_type != 'book_purchase': continue if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue total_revenue += amount total_transactions += 1 if is_today_local: revenue_today += amount return jsonify({ 'total_revenue': total_revenue, 'total_transactions': total_transactions, 'revenue_today': revenue_today }) @app.route('/api/english/receipts', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Director') # Allow Cashier, Analyst, Director etc. def get_english_receipts(): """Return combined registration and book purchase receipts with filtering.""" # Get query parameters receipt_type = request.args.get('type') # 'registration', 'book', or None for both start_date = request.args.get('start_date') end_date = request.args.get('end_date') student_name = request.args.get('student_name') receipt_number = request.args.get('receipt_number') payment_method = request.args.get('payment_method') cashier_id = request.args.get('cashier_id') # Helper to filter datetime def filter_date(dt): if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if dt < start: return False if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if dt > end: return False return True receipts = [] # 1. Registration receipts if receipt_type is None or receipt_type == 'registration': query = EnglishRegistrationReceipt.query if cashier_id: query = query.filter_by(cashier_id=cashier_id) reg_receipts = query.all() for r in reg_receipts: if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue if (r.receipt_type == 'enrollment' and r.form_fee_amount == 0 and r.registration_fee_amount == 0 and r.total_amount == 0): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'registration', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'additional_amount': float(r.additional_amount), # NEW 'additional_reason': r.additional_reason, 'details': { 'class_code': r.class_code, 'level_name': r.level_name, 'form_fee': float(r.form_fee_amount) if r.form_fee_amount else 0, 'registration_fee': float(r.registration_fee_amount) if r.registration_fee_amount else 0, 'charge_form_fee': r.charge_form_fee, 'charge_registration_fee': r.charge_registration_fee, 'receipt_type': r.receipt_type } }) # 2. Book purchase receipts if receipt_type is None or receipt_type == 'book': book_receipts = BookPurchaseReceipt.query.all() for r in book_receipts: if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'book', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'details': { 'items': r.items, # list of {title, quantity, price, total, class_name} } }) # Sort by date descending receipts.sort(key=lambda x: x['date'], reverse=True) return jsonify(receipts) @app.route('/api/english/cashier/receipts', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def get_english_cashier_receipts(): """Return combined registration and book purchase receipts for the logged-in cashier.""" receipt_type = request.args.get('type') start_date = request.args.get('start_date') end_date = request.args.get('end_date') student_name = request.args.get('student_name') receipt_number = request.args.get('receipt_number') payment_method = request.args.get('payment_method') def filter_date(dt): if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if dt < start: return False if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if dt > end: return False return True receipts = [] # Registration receipts for this cashier if receipt_type is None or receipt_type == 'registration': reg_receipts = EnglishRegistrationReceipt.query.filter_by(cashier_id=current_user.id).all() for r in reg_receipts: if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue if (r.receipt_type == 'enrollment' and r.form_fee_amount == 0 and r.registration_fee_amount == 0 and r.total_amount == 0): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'registration', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'additional_amount': float(r.additional_amount), # NEW 'additional_reason': r.additional_reason, 'details': { 'class_code': r.class_code, 'level_name': r.level_name, 'form_fee': float(r.form_fee_amount) if r.form_fee_amount else 0, 'registration_fee': float(r.registration_fee_amount) if r.registration_fee_amount else 0, 'charge_form_fee': r.charge_form_fee, 'charge_registration_fee': r.charge_registration_fee, 'receipt_type': r.receipt_type } }) # Book purchase receipts for this cashier if receipt_type is None or receipt_type == 'book': book_receipts = BookPurchaseReceipt.query.filter_by(cashier_id=current_user.id).all() for r in book_receipts: if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'book', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'details': { 'items': r.items, } }) receipts.sort(key=lambda x: x['date'], reverse=True) return jsonify(receipts) # ==================== PRIVATE SCHOOL FEE MANAGEMENT ==================== @app.route('/api/private/fee-items', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Director') def get_private_fee_items(): include_linked = request.args.get('include_linked_to_uniforms_and_forms', 'true').lower() == 'true' query = PrivateFeeItem.query.order_by(PrivateFeeItem.created_at.desc()) if not include_linked: # Subquery to get fee_item_id from forms and uniforms linked_form_ids = db.session.query(PrivateFormStock.fee_item_id).filter(PrivateFormStock.fee_item_id.isnot(None)) linked_uniform_ids = db.session.query(PrivateUniform.fee_item_id).filter(PrivateUniform.fee_item_id.isnot(None)) # Union of all linked IDs linked_ids = linked_form_ids.union(linked_uniform_ids).subquery() # Exclude fee items that appear in linked_ids query = query.filter(PrivateFeeItem.id.notin_(linked_ids)) fee_items = query.all() result = [] for item in fee_items: # Convert database codes to display names assigned_display = [private_school_code_to_display.get(a.class_code, a.class_code) for a in item.assignments] result.append({ 'id': item.id, 'name': item.name, 'amount': float(item.amount), 'termly': item.termly, 'assigned_classes': assigned_display, 'created_at': item.created_at.isoformat(), 'updated_at': item.updated_at.isoformat() }) return jsonify(result) @app.route('/api/private/fee-items', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Director') def create_private_fee_item(): data = request.get_json() name = (data.get('name') or '').strip() amount = data.get('amount') assigned_classes = data.get('assigned_classes', []) termly = data.get('termly') # do NOT default – must be provided errors = {} if not name: errors['name'] = 'Fee name is required' try: amount = float(amount) if amount <= 0: errors['amount'] = 'Amount must be greater than 0' except (TypeError, ValueError): errors['amount'] = 'Invalid amount' if termly is None: errors['termly'] = 'Termly field is required' elif not isinstance(termly, bool): errors['termly'] = 'Termly must be true or false' if errors: return jsonify({'success': False, 'errors': errors}), 400 fee_item = PrivateFeeItem(name=name, amount=amount, termly=termly) db.session.add(fee_item) db.session.flush() # get ID for private_class in assigned_classes: class_code = private_school_classes_to_code[private_class] assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=class_code) db.session.add(assignment) db.session.commit() return jsonify({'success': True, 'fee_item_id': fee_item.id}) @app.route('/api/private/fee-items/', methods=['PUT']) @login_required @require_password_changed @requires_roles('private', 'Director') def update_private_fee_item(fee_item_id): fee_item = PrivateFeeItem.query.get(fee_item_id) if not fee_item: return jsonify({'error': 'Fee item not found'}), 404 data = request.get_json() name = (data.get('name') or '').strip() amount = data.get('amount') assigned_classes = data.get('assigned_classes', []) termly = data.get('termly') # required! errors = {} if not name: errors['name'] = 'Fee name is required' try: amount = float(amount) if amount <= 0: errors['amount'] = 'Amount must be greater than 0' except (TypeError, ValueError): errors['amount'] = 'Invalid amount' if termly is None: errors['termly'] = 'Termly field is required' elif not isinstance(termly, bool): errors['termly'] = 'Termly must be true or false' if errors: return jsonify({'success': False, 'errors': errors}), 400 fee_item.name = name fee_item.amount = amount fee_item.termly = termly # Update assignments: remove old, add new PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete() for private_class in assigned_classes: class_code = private_school_classes_to_code[private_class] assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=class_code) db.session.add(assignment) db.session.commit() return jsonify({'success': True}) @app.route('/api/private/fee-items/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('private', 'Director') def delete_private_fee_item(fee_item_id): fee_item = PrivateFeeItem.query.get(fee_item_id) if not fee_item: return jsonify({'error': 'Fee item not found'}), 404 linked_form = PrivateFormStock.query.filter_by(fee_item_id=fee_item.id).first() if linked_form: return jsonify({'error': 'Cannot delete fee item because it is linked to a form stock. Delete the form first.'}), 400 # Check if fee item is linked to a uniform linked_uniform = PrivateUniform.query.filter_by(fee_item_id=fee_item.id).first() if linked_uniform: return jsonify({'error': 'Cannot delete fee item because it is linked to a uniform. Delete the uniform first.'}), 400 # Delete assignments first (cascade) PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete() db.session.delete(fee_item) db.session.commit() return jsonify({'success': True}) @app.route('/api/private/create-user', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Director') def create_private_user(): data = request.get_json() email = data.get('email').lower() name = data.get('name') if User.query.filter_by(email=email).first(): return jsonify({'error': 'Email already exists.'}), 400 user = User( name=name, email=email, must_change_password=True ) user.set_password('123456') # Assign Private roles only role_names = data.get('roles', []) for role_name in role_names: role = PrivateRole.query.filter_by(name=role_name).first() if role: user.private_roles.append(role) msg = send_new_user_message(email, name) try: res = send_mail_vercel(msg) if not res.get("status"): raise Exception(res.get("error")) except Exception as e: if str(e) == '[Errno 11001] getaddrinfo failed': error = 'Failed to connect. Please check your internet connection.' else: error = f'Error: {str(e)}' return jsonify({'error': f'{error}'}), 400 db.session.add(user) db.session.commit() return jsonify({'success': True, 'user_id': user.id}) # ==================== PRIVATE SCHOOL SESSION MANAGEMENT ==================== @app.route('/api/private/sessions', methods=['GET']) @login_required def get_private_sessions(): include_deleted = request.args.get('include_deleted', 'false').lower() == 'true' query = PrivateSession.query if not include_deleted: query = query.filter_by(deleted=False) sessions = query.order_by(PrivateSession.created_at.desc()).all() return jsonify([s.to_dict() for s in sessions]), 200 @app.route('/api/private/sessions', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def create_private_session(): data = request.get_json() name = (data.get('name') or '').replace(' ', '') term = data.get('term') if not name: return jsonify({'error': 'Session name is required'}), 400 if term not in [1, 2, 3]: return jsonify({'error': 'Term must be 1, 2, or 3'}), 400 existing = PrivateSession.query.filter_by(name=name, term=term).first() if existing: return jsonify({'error': 'Session with this name and term already exists'}), 400 next_index = get_next_color_index() session = PrivateSession( name=name, term=term, pass_color=str(next_index) ) db.session.add(session) db.session.commit() return jsonify({'success': True, 'session': session.to_dict()}), 201 @app.route('/api/private/sessions/', methods=['PUT']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def update_private_session(session_id): session = PrivateSession.query.get(session_id) if not session: return jsonify({'error': 'Session not found'}), 404 data = request.get_json() name = (data.get('name') or '').replace(' ', '') term = data.get('term') if not name: return jsonify({'error': 'Session name is required'}), 400 if term not in [1, 2, 3]: return jsonify({'error': 'Term must be 1, 2, or 3'}), 400 existing = PrivateSession.query.filter(PrivateSession.name == name, PrivateSession.term == term, PrivateSession.id != session_id).first() if existing: return jsonify({'error': 'Session with this name and term already exists'}), 400 session.name = name session.term = term db.session.commit() return jsonify({'success': True, 'session': session.to_dict()}), 200 @app.route('/api/private/gate-passes/bulk-pdf', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def bulk_private_gate_passes_pdf(): data = request.get_json() session_id = data.get('session_id') student_ids = data.get('student_ids') cols = 5 rows = 2 passes_per_page = cols * rows if not session_id: return jsonify({'error': 'session_id required'}), 400 if not student_ids or not isinstance(student_ids, list): return jsonify({'error': 'student_ids list required'}), 400 session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Session not found'}), 404 # Get enrollment receipts for this session, ordered by pass_number receipts = PrivateEnrollmentReceipt.query.filter_by(session_id=session_id)\ .order_by(PrivateEnrollmentReceipt.pass_number).all() # Map student_id -> receipt (first occurrence) student_map = {} for r in receipts: if r.student_id not in student_map: student_map[r.student_id] = r # Filter by requested student_ids student_ids_set = set(student_ids) student_map = {sid: r for sid, r in student_map.items() if sid in student_ids_set} if not student_map: return jsonify({'error': 'No matching students found'}), 404 # Prepare list of passes with images passes = [] for r in student_map.values(): student = r.student # Format term text (e.g., "1st", "2nd", "3rd") term_suffix = {1: 'st', 2: 'nd', 3: 'rd'}.get(session.term) term_str = f"{session.term}{term_suffix}".upper() session_name = session.name color_index = session.pass_color pass_no = r.pass_number # Generate gate pass image using existing function img_base64 = process_college_pass( term_str, session_name, pass_no, color_index, student.name, get_class_display_name_from_code(student.class_code) ) img_bytes = base64.b64decode(img_base64) passes.append({ 'student_name': student.name, 'pass_no': pass_no, 'image_bytes': img_bytes }) # Create a temporary file (no auto-delete, we'll delete after sending) temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.pdf') temp_file.close() temp_path = temp_file.name c = canvas.Canvas(temp_path, pagesize=landscape(A4)) width, height = landscape(A4) # 841.89 x 595.276 points margin = 15 col_gap = 10 row_gap = 10 usable_width = width - 2 * margin usable_height = height - 2 * margin total_col_gaps = (cols - 1) * col_gap cell_width = (usable_width - total_col_gaps) / cols total_row_gaps = (rows - 1) * row_gap cell_height = (usable_height - total_row_gaps) / rows positions = [] for row in range(rows): y = height - margin - (row + 1) * cell_height - row * row_gap for col in range(cols): x = margin + col * (cell_width + col_gap) positions.append((x, y)) for idx, p in enumerate(passes): pos_idx = idx % passes_per_page if pos_idx == 0 and idx != 0: c.showPage() x, y = positions[pos_idx] img = ImageReader(BytesIO(p['image_bytes'])) c.drawImage(img, x, y, cell_width, cell_height, preserveAspectRatio=False) c.save() @after_this_request def cleanup(response): try: os.unlink(temp_path) except Exception: pass return response return send_file( temp_path, as_attachment=True, download_name=f'gate_passes_session_{session.id}.pdf', mimetype='application/pdf' ) @app.route('/api/private/sessions/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def delete_private_session(session_id): session = PrivateSession.query.get(session_id) if not session: return jsonify({'error': 'Session not found'}), 404 session.deleted = True db.session.commit() return jsonify({'success': True}), 200 # ==================== PRIVATE SCHOOL FEES BY CLASS ==================== @app.route('/api/private/fees-by-class/', methods=['GET']) @login_required def get_private_fees_by_class(class_code): # Parse query parameters, default True if not provided include_linked_to_form = request.args.get('include_linked_to_form', 'true').lower() == 'true' include_linked_to_uniform = request.args.get('include_linked_to_uniform', 'true').lower() == 'true' assignments = PrivateFeeAssignment.query.filter_by(class_code=class_code).all() result = [] for assignment in assignments: item = assignment.fee_item if not item: continue # Determine if this fee item is linked to a form or uniform linked_to_form = PrivateFormStock.query.filter_by(fee_item_id=item.id).first() is not None linked_to_uniform = PrivateUniform.query.filter_by(fee_item_id=item.id).first() is not None # Skip if linked to form and we don't want to include form-linked fees if linked_to_form and not include_linked_to_form: continue # Skip if linked to uniform and we don't want to include uniform-linked fees if linked_to_uniform and not include_linked_to_uniform: continue result.append({ 'id': item.id, 'name': item.name, 'amount': float(item.amount), 'termly': item.termly }) return jsonify(result), 200 # ==================== PRIVATE SCHOOL ENROLLMENT ==================== @app.route('/api/private/enroll', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def enroll_private_student(): data = request.get_json() # Either new student details or existing student ID student_id = data.get('student_id') name = (data.get('name') or '').strip() phone = (data.get('phone') or '').strip() email = (data.get('email') or '').strip() # Enrollment details session_id = data.get('session_id') class_code = data.get('class_code') selected_items = data.get('selected_items', []) # list of {id, name, amount} payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') additional_amount = data.get('additional_amount', 0) additional_reason = data.get('additional_reason', '') # Validation if not session_id: return jsonify({'error': 'Session is required'}), 400 if not class_code: return jsonify({'error': 'Class is required'}), 400 if not selected_items: return jsonify({'error': 'At least one fee item must be selected'}), 400 if discount_amount > 0 and additional_amount > 0: return jsonify({'error': 'Cannot apply both discount and additional fee. Choose one.'}), 400 if discount_amount < 0 or additional_amount < 0: return jsonify({'error': 'Amounts cannot be negative'}), 400 # Verify session exists and not deleted session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Invalid or deleted session'}), 400 # Verify class exists class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first() if not class_obj: return jsonify({'error': 'Invalid class'}), 400 # Calculate subtotal from selected items subtotal = sum(item['amount'] for item in selected_items) # Validate discount if discount_amount > subtotal: return jsonify({ 'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({ 'error': f'Discount reason required.' }), 400 # No validation for additional fee (can be any positive amount) if additional_amount > 0 and not additional_reason: return jsonify({'error': 'Additional fee reason required'}), 400 total_amount = subtotal - discount_amount + additional_amount if student_id: # Existing student: verify, must have no class student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 if student.class_code is not None: return jsonify({'error': 'Student already has a class. Use "Pay Fees" instead.'}), 400 # Update student's class student.class_code = class_code else: if not name or not phone: return jsonify({'error': 'Name and phone are required for new student'}), 400 if email and not is_valid_email(email): return jsonify({'error': 'Invalid email address'}), 400 # Create student student = PrivateStudent( name=name, phone=phone, email=email if email else None, class_code=class_code ) db.session.add(student) db.session.flush() # Generate receipt number # receipt_number = generate_private_receipt_number(session.id, session.term) receipt_number = generate_receipt_number() pass_number = get_next_pass_number(session.id) # Create receipt receipt = PrivateEnrollmentReceipt( receipt_number=receipt_number, student_id=student.id, session_id=session.id, term=session.term, class_code=class_code, items=selected_items, discount_amount=discount_amount, discount_reason=discount_reason, total_amount=total_amount, payment_method=payment_method, cashier_id=current_user.id, pass_number=pass_number, additional_amount=additional_amount, additional_reason=additional_reason, ) db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'receipt': receipt.to_dict(), 'student': student.to_dict() }), 200 # ==================== PRIVATE SCHOOL BOOK MANAGEMENT ==================== @app.route('/api/private/books', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager', 'BookSeller') def get_private_books(): """Get all private school books.""" books = PrivateBook.query.order_by(PrivateBook.created_at.desc()).all() return jsonify([b.to_dict() for b in books]) @app.route('/api/private/create-book', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def create_private_book(): data = request.get_json() title = (data.get('title') or '').strip() author = (data.get('author') or '').strip() price = data.get('price') quantity = data.get('quantity') class_code = data.get('class_code') class_name = data.get('class_name') or '' errors = {} if not title: errors['title'] = 'Title is required' if not author: errors['author'] = 'Author is required' try: price = float(price) if price <= 0: errors['price'] = 'Price must be greater than 0' except (TypeError, ValueError): errors['price'] = 'Invalid price' try: quantity = int(quantity) if quantity < 0: errors['quantity'] = 'Quantity cannot be negative' except (TypeError, ValueError): errors['quantity'] = 'Invalid quantity' if not class_code: errors['class_code'] = 'Class is required' else: # Verify class exists class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first() if not class_obj: errors['class_code'] = 'Invalid class selected' else: # Set class name if not provided if not class_name: class_name = class_obj.name if errors: return jsonify({'success': False, 'errors': errors}), 400 book = PrivateBook( title=title, author=author, price=price, quantity=quantity, class_code=class_code, class_name=class_name ) db.session.add(book) db.session.commit() return jsonify({'success': True, 'book_id': book.id}) @app.route('/api/private/update-book/', methods=['PUT']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def update_private_book(book_id): book = PrivateBook.query.get(book_id) if not book: return jsonify({'error': 'Book not found'}), 404 data = request.get_json() title = (data.get('title') or '').strip() author = (data.get('author') or '').strip() price = data.get('price') quantity = data.get('quantity') class_code = data.get('class_code') class_name = data.get('class_name') or '' errors = {} if not title: errors['title'] = 'Title is required' if not author: errors['author'] = 'Author is required' try: price = float(price) if price <= 0: errors['price'] = 'Price must be greater than 0' except (TypeError, ValueError): errors['price'] = 'Invalid price' try: quantity = int(quantity) if quantity < 0: errors['quantity'] = 'Quantity cannot be negative' except (TypeError, ValueError): errors['quantity'] = 'Invalid quantity' if not class_code: errors['class_code'] = 'Class is required' else: class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first() if not class_obj: errors['class_code'] = 'Invalid class selected' elif not class_name: class_name = class_obj.name if errors: return jsonify({'success': False, 'errors': errors}), 400 # Check for actual changes (optional optimisation) has_changes = False if book.title != title: book.title = title has_changes = True if book.author != author: book.author = author has_changes = True if book.price != price: book.price = price has_changes = True if book.quantity != quantity: book.quantity = quantity has_changes = True if book.class_code != class_code: book.class_code = class_code has_changes = True if book.class_name != class_name: book.class_name = class_name has_changes = True if not has_changes: return jsonify({'success': False, 'message': 'No changes were made'}), 400 db.session.commit() return jsonify({'success': True}) @app.route('/api/private/delete-book/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def delete_private_book(book_id): book = PrivateBook.query.get(book_id) if not book: return jsonify({'error': 'Book not found'}), 404 db.session.delete(book) db.session.commit() return jsonify({'success': True, 'message': 'Book deleted successfully'}), 200 @app.route('/api/private/students', methods=['GET']) @login_required @require_password_changed def get_private_students(): """Get all private school students (for selection)""" include_deleted = request.args.get('include_deleted', 'false').lower() == 'true' try: query = PrivateStudent.query if not include_deleted: query = query.filter_by(deleted=False) students = query.order_by(PrivateStudent.created_at.desc()).all() return jsonify([s.to_dict() for s in students]), 200 except Exception as e: return jsonify({'error': str(e)}), 500 @app.route('/api/private/search-students', methods=['GET']) @login_required def search_private_students(): """Search private students by name, phone, or email""" query = request.args.get('q', '') include_deleted = request.args.get('include_deleted', 'false').lower() == 'true' if not query: return jsonify([]) students = get_student_by_search("private", query, include_deleted) return jsonify([s.to_dict() for s in students]) @app.route('/api/private/purchase-books', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'BookSeller') def purchase_private_books(): data = request.get_json() student_id = data.get('student_id') if not student_id: return jsonify({'error': 'Student ID is required'}), 400 session_id = data.get('session_id') if not session_id: return jsonify({'error': 'Session ID is required'}), 400 items = data.get('items', []) if not items or not isinstance(items, list): return jsonify({'error': 'No items provided'}), 400 payment_method = data.get('payment_method') if not payment_method: return jsonify({'error': 'Payment method is required'}), 400 discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') # Validate session exists and not deleted session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Invalid or deleted session'}), 400 # Validate student exists student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 if discount_amount < 0: return jsonify({'error': 'Discount amount cannot be negative'}), 400 try: # Process items and calculate totals subtotal = 0 purchase_items = [] for item in items: book = PrivateBook.query.get(item.get('book_id')) if not book: return jsonify({'error': 'Book not found'}), 404 qty = item.get('quantity') if not qty or qty <= 0: return jsonify({'error': 'Invalid quantity'}), 400 if book.quantity < qty: return jsonify({'error': f'Not enough stock for {book.title}'}), 400 item_total = book.price * qty subtotal += item_total purchase_items.append({ 'book_id': book.id, 'title': book.title, 'quantity': qty, 'price': float(book.price), 'class_name': book.class_name, 'total': float(item_total) }) # Deduct stock book.quantity -= qty # Validate discount vs subtotal if discount_amount > subtotal: return jsonify({ 'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 total_amount = subtotal - discount_amount discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({ 'error': f'Discount reason required.' }), 400 # Generate receipt number receipt_number = generate_receipt_number() # Create receipt receipt = PrivateBookReceipt( receipt_number=receipt_number, student_id=student.id, session_id=session.id, term=session.term, total_amount=total_amount, discount_amount=discount_amount, discount_reason=discount_reason, payment_method=payment_method, cashier_id=current_user.id, items=purchase_items ) db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'receipt': receipt.to_dict() }) except Exception as e: db.session.rollback() return jsonify({'error': f'Purchase failed: {str(e)}'}), 500 @app.route('/api/private/students//class-books', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def get_private_student_class_books(student_id): """Get all books for the student's class (for "Add All Books" feature).""" student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 # Get all books for the student's class code books = PrivateBook.query.filter_by(class_code=student.class_code).all() return jsonify({ 'books': [{ 'id': book.id, 'title': book.title, 'author': book.author, 'price': float(book.price), 'quantity': book.quantity, 'class_name': book.class_name, 'class_code': book.class_code } for book in books] }), 200 # ==================== PRIVATE SCHOOL STUDENT MANAGEMENT ==================== @app.route('/api/private/students/', methods=['PUT']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def update_private_student(student_id): """Update a private student's name, phone, or email.""" student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 data = request.get_json() name = (data.get('name') or '').strip() phone = (data.get('phone') or '').strip() email = (data.get('email') or '').strip() if not name or not phone: return jsonify({'error': 'Name and phone are required'}), 400 if email and not is_valid_email(email): return jsonify({'error': 'Invalid email address'}), 400 has_changes = False if student.name != name: student.name = name has_changes = True if student.phone != phone: student.phone = phone has_changes = True if student.email != email: student.email = email if email else None has_changes = True if not has_changes: return jsonify({'error': 'No changes were made'}), 400 db.session.commit() return jsonify({'success': True, 'student': student.to_dict()}), 200 @app.route('/api/private/students//pay-fees', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def pay_private_school_fees(student_id): """ Change a private student's class. Creates a new PrivateEnrollmentReceipt for the fees charged. """ student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 data = request.get_json() session_id = data.get('session_id') class_code = data.get('class_code') selected_items = data.get('selected_items', []) # list of {id, name, amount} payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') additional_amount = data.get('additional_amount', 0) additional_reason = data.get('additional_reason', '') # Validation if not session_id: return jsonify({'error': 'Session is required'}), 400 if not class_code: return jsonify({'error': 'Class is required'}), 400 if not selected_items: return jsonify({'error': 'At least one fee item must be selected'}), 400 if discount_amount < 0 or additional_amount < 0: return jsonify({'error': 'Amounts cannot be negative'}), 400 # Mutual exclusivity if discount_amount > 0 and additional_amount > 0: return jsonify({'error': 'Cannot apply both discount and additional fee. Choose one.'}), 400 last_receipt = PrivateEnrollmentReceipt.query.filter_by(student_id=student.id)\ .order_by(PrivateEnrollmentReceipt.created_at.desc()).first() if last_receipt and last_receipt.session_id == session_id: return jsonify({ 'error': f'Student already paid fees for session "{last_receipt.session.name}" (Term {last_receipt.term}). Cannot pay again for the same session and term' }), 400 session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Invalid or deleted session'}), 400 class_obj = PrivateSchoolClass.query.filter_by(code=class_code).first() if not class_obj: return jsonify({'error': 'Invalid class'}), 400 # Calculate subtotal subtotal = sum(item['amount'] for item in selected_items) if discount_amount > subtotal: return jsonify({ 'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({ 'error': f'Discount reason required.' }), 400 if additional_amount > 0 and not additional_reason: return jsonify({'error': 'Additional fee reason required.'}), 400 total_amount = subtotal - discount_amount + additional_amount # Generate receipt number # receipt_number = generate_private_receipt_number(session.id, session.term) receipt_number = generate_receipt_number() pass_number = get_next_pass_number(session.id) # Create receipt (this acts as a "class change" receipt) receipt = PrivateEnrollmentReceipt( receipt_number=receipt_number, student_id=student.id, session_id=session.id, term=session.term, class_code=class_code, items=selected_items, discount_amount=discount_amount, discount_reason=discount_reason, total_amount=total_amount, payment_method=payment_method, cashier_id=current_user.id, pass_number=pass_number, additional_amount=additional_amount, additional_reason=additional_reason, ) # Update student's class student.class_code = class_code db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'receipt': receipt.to_dict(), 'student': student.to_dict() }), 200 @app.route('/api/private/analyst/cashiers', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Analyst') def get_private_cashiers(): cashier_role = PrivateRole.query.filter_by(name='Cashier').first() if not cashier_role: return jsonify([]) cashiers = cashier_role.users.all() return jsonify([{'id': u.id, 'name': u.name} for u in cashiers]) @app.route('/api/private/analyst/transactions', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Analyst') def get_private_transactions(): start_date = request.args.get('start_date') end_date = request.args.get('end_date') payment_method = request.args.get('payment_method') transaction_type = request.args.get('transaction_type') # 'form', 'school_fees', 'uniform', 'books' min_amount = request.args.get('min_amount', type=float) max_amount = request.args.get('max_amount', type=float) cashier_id = request.args.get('cashier_id') class_code = request.args.get('class_code') session_id = request.args.get('session_id') # fee_item = request.args.get('fee_item') all_items = [] # ---------- Enrolment receipts – each item with proportional discount ---------- for r in PrivateEnrollmentReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if transaction_type and transaction_type not in ['form', 'school_fees']: continue if class_code and r.class_code != class_code: continue if session_id and r.session_id != session_id: continue items_list = r.items subtotal_items = sum(float(item.get('amount', 0)) for item in items_list) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 for item in items_list: amount = float(item.get('amount', 0)) item_name = item.get('name', '') txn_type = 'form' if item_name == 'Form Fee' else 'school_fees' if transaction_type and transaction_type != txn_type: continue if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue # Proportional discount item_discount = 0 if total_discount > 0 and subtotal_items > 0: discount_ratio = amount / subtotal_items item_discount = total_discount * discount_ratio # Proportional additional fee item_additional = 0 if total_additional > 0 and subtotal_items > 0: additional_ratio = amount / subtotal_items item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional all_items.append({ 'id': f"{r.id}_{item['id']}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': txn_type, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': amount, 'discount_amount': item_discount, 'discount_reason': r.discount_reason or '', 'additional_amount': item_additional, # <-- distributed amount for this item 'additional_reason': r.additional_reason or '', 'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': item_name, 'quantity': 1, 'session_id': r.session_id }) for r in PrivateFormReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if transaction_type and transaction_type != 'form': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue amount = float(r.total_amount_float) if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue all_items.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'form', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': amount, 'subtotal': float(r.form_price), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'discount_percentage': (float(r.discount_amount) / float(r.form_price) * 100) if float(r.form_price) > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': f"Form ({r.form_class_group})", 'quantity': 1, 'session_id': r.session_id }) # ---------- Book receipts – each book item with proportional discount ---------- for r in PrivateUniformReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if transaction_type and transaction_type != 'uniform': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount all_items.append({ 'id': f"{r.id}_{item.get('uniform_id')}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'uniform', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': item_subtotal, 'discount_amount': item_discount, 'discount_reason': r.discount_reason, 'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': item.get('title'), 'quantity': item.get('quantity'), 'session_id': r.session_id }) for r in PrivateBookReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if transaction_type and transaction_type != 'books': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount all_items.append({ 'id': f"{r.id}_{item.get('book_id')}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'books', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': item_subtotal, 'discount_amount': item_discount, 'discount_reason': r.discount_reason, 'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': item.get('title'), 'quantity': item.get('quantity'), 'session_id': r.session_id }) # ---------- Islamiyya receipts ---------- for r in IslamiyyaPaymentReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if transaction_type and transaction_type != 'islamiyya': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue amount = r.total_amount_float if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue all_items.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'islamiyya', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': amount, 'subtotal': r.form_fee_charged_float + r.term_fee_charged_float, 'discount_amount': r.discount_amount_float, 'discount_reason': r.discount_reason, 'discount_percentage': (r.discount_amount_float / (r.form_fee_charged_float + r.term_fee_charged_float) * 100) if (r.form_fee_charged_float + r.term_fee_charged_float) > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': 'Islamiyya Fees', 'quantity': 1, 'session_id': r.session_id }) all_items.sort(key=lambda x: x['date'], reverse=True) total_amount = sum(i['amount'] for i in all_items) total_discount = sum(i['discount_amount'] for i in all_items) return jsonify({ 'transactions': [{**t, 'date': t['date'].isoformat()} for t in all_items], 'total_count': len(all_items), 'total_amount': total_amount, 'total_discount': total_discount }) @app.route('/api/private/analyst/students', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Analyst') def get_private_analyst_students(): class_filter = request.args.get('class_code') students = PrivateStudent.query.all() result = [] for s in students: if class_filter and s.class_code != class_filter: continue total_spent = 0.0 receipts_count = 0 # 1. Enrollment receipts – use the final total_amount (already includes discount & additional fee) for r in s.receipts: # PrivateEnrollmentReceipts receipts_count += 1 total_spent += r.total_amount_float # 2. Book receipts for r in PrivateBookReceipt.query.filter_by(student_id=s.id): receipts_count += 1 if r.items: subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio total_spent += (item_subtotal - item_discount) else: total_spent += float(r.total_amount or 0) # 3. Uniform receipts for r in PrivateUniformReceipt.query.filter_by(student_id=s.id): receipts_count += 1 subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio total_spent += (item_subtotal - item_discount) # 4. Form receipts (standalone) for r in PrivateFormReceipt.query.filter_by(student_id=s.id): receipts_count += 1 total_spent += r.total_amount_float # 5. Islamiyya receipts (if applicable) for r in IslamiyyaPaymentReceipt.query.filter_by(student_id=s.id): receipts_count += 1 total_spent += r.total_amount_float result.append({ 'id': s.id, 'name': s.name, 'phone': s.phone, 'email': s.email or '', 'class_code': s.class_code, 'class_name': s.class_obj.name if s.class_obj else '', 'total_spent': total_spent, 'receipts_count': receipts_count, 'created_at': s.created_at.isoformat() }) result.sort(key=lambda x: x['name']) return jsonify(result) @app.route('/api/private/analyst/summary', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Analyst') def get_private_analyst_summary(): start_date = request.args.get('start_date') end_date = request.args.get('end_date') all_items = [] # Enrolment – split Form Fee vs School Fees for r in PrivateEnrollmentReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue subtotal_items = sum(float(item.get('amount', 0)) for item in r.items) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 for item in r.items: amount = float(item.get('amount', 0)) item_name = item.get('name', '') txn_type = 'form' if item_name == 'Form Fee' else 'school_fees' item_discount = 0 if total_discount > 0 and subtotal_items > 0: discount_ratio = amount / subtotal_items item_discount = total_discount * discount_ratio # Proportional additional fee item_additional = 0 if total_additional > 0 and subtotal_items > 0: additional_ratio = amount / subtotal_items item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional if item_total > 0: all_items.append({ 'date': r.created_at, 'amount': item_total, 'type': txn_type, 'payment_method': r.payment_method }) # Standalone Form Receipts for r in PrivateFormReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue amount = float(r.total_amount_float) if amount > 0: all_items.append({ 'date': r.created_at, 'amount': amount, 'type': 'form', 'payment_method': r.payment_method }) # Uniform Receipts for r in PrivateUniformReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue total_discount = float(r.discount_amount or 0) subtotal = float(r.total_amount or 0) + total_discount for item in r.items: item_subtotal = float(item.get('total', 0)) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount if item_total > 0: all_items.append({ 'date': r.created_at, 'amount': item_total, 'type': 'uniform', 'payment_method': r.payment_method }) # Book Receipts (type 'books') for r in PrivateBookReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue total_discount = float(r.discount_amount or 0) subtotal = float(r.total_amount or 0) + total_discount for item in r.items: item_subtotal = float(item.get('total', 0)) item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount if item_total > 0: all_items.append({ 'date': r.created_at, 'amount': item_total, 'type': 'books', 'payment_method': r.payment_method }) # ---------- Islamiyya receipts ---------- for r in IslamiyyaPaymentReceipt.query.all(): if not _check_date(r.created_at, start_date, end_date): continue amount = r.total_amount_float if amount > 0: all_items.append({ 'date': r.created_at, 'amount': amount, 'type': 'islamiyya', 'payment_method': r.payment_method }) total_revenue = sum(i['amount'] for i in all_items) total_transactions = len(all_items) by_type = {'form': 0, 'school_fees': 0, 'uniform': 0, 'books': 0, 'islamiyya':0} by_payment = {} for i in all_items: by_type[i['type']] += i['amount'] by_payment[i['payment_method']] = by_payment.get(i['payment_method'], 0) + i['amount'] total_students = PrivateStudent.query.count() enrolled_students = PrivateStudent.query.filter(PrivateStudent.class_code.isnot(None)).count() return jsonify({ 'total_revenue': round(total_revenue, 2), 'total_transactions': total_transactions, 'form_revenue': round(by_type['form'], 2), 'school_fees_revenue': round(by_type['school_fees'], 2), 'uniform_revenue': round(by_type['uniform'], 2), 'books_revenue': round(by_type['books'], 2), 'islamiyya_revenue': round(by_type['islamiyya'], 2), 'by_type': {k: round(v, 2) for k, v in by_type.items()}, 'by_payment': {k: round(v, 2) for k, v in by_payment.items()}, 'total_students': total_students, 'enrolled_students': enrolled_students }) def _check_date(dt, start_str, end_str): if start_str: start = datetime.fromisoformat(start_str) if 'T' in start_str else datetime.fromisoformat(start_str + 'T00:00:00') if dt < start: return False if end_str: end = datetime.fromisoformat(end_str) if 'T' in end_str else datetime.fromisoformat(end_str + 'T23:59:59') if dt > end: return False return True @app.route('/api/private/analyst/export', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Analyst') def export_private_full(): """Export all data (no filters) for private school.""" data = request.get_json() or {} export_type = data.get('type') # 'transactions' or 'students' return _build_private_export(export_type, {}) @app.route('/api/private/analyst/export-filtered', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Analyst') def export_private_filtered(): """Export filtered data for private school.""" data = request.get_json() or {} export_type = data.get('type') filters = data.get('filters', {}) return _build_private_export(export_type, filters) def _build_private_export(export_type, filters): wb = Workbook() wb.remove(wb.active) header_font = Font(bold=True, size=11) title_font = Font(bold=True, size=14) label_font = Font(bold=True, size=11) value_font = Font(size=11) def format_phone(phone): return str(phone) if phone else 'N/A' def safe_date(date_str, end=False): if not date_str: return None try: return datetime.fromisoformat(date_str + ('T23:59:59' if end else '')) except: return None def auto_width(ws, max_width=40): for col in ws.columns: max_length = 0 col_letter = None for cell in col: if isinstance(cell, openpyxl.cell.cell.MergedCell): continue if col_letter is None: col_letter = cell.column_letter try: if cell.value: max_length = max(max_length, len(str(cell.value))) except: pass if col_letter: ws.column_dimensions[col_letter].width = min(max_length + 2, max_width) def format_currency_cells(ws, currency_columns, start_row=2): for row in ws.iter_rows(min_row=start_row): for cell in row: if cell.column in currency_columns and isinstance(cell.value, (int, float)): cell.number_format = '#,##0' # Summary sheet (unchanged) summary_ws = wb.create_sheet("Summary") has_filters = any(filters.values()) title_cell = summary_ws['A1'] title_cell.value = f"{'FILTERED' if has_filters else 'ALL'} {export_type.upper()} EXPORT - PRIVATE SCHOOL" title_cell.font = title_font summary_ws.merge_cells('A1:D1') summary_ws['A3'] = "Export Date:" summary_ws['B3'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S') summary_ws['A3'].font = label_font summary_ws['B3'].font = value_font row = 5 summary_ws[f'A{row}'] = "SUMMARY STATISTICS" summary_ws[f'A{row}'].font = Font(bold=True, size=12) row += 2 if export_type == 'transactions': revenue_row = row summary_ws[f'A{revenue_row}'] = "Total Revenue:" summary_ws[f'B{revenue_row}'] = "Pending calculation" summary_ws[f'A{revenue_row}'].font = label_font row += 1 transactions_row = row summary_ws[f'A{transactions_row}'] = "Total Transactions:" summary_ws[f'B{transactions_row}'] = "Pending calculation" summary_ws[f'A{transactions_row}'].font = label_font row += 1 else: students_row = row summary_ws[f'A{students_row}'] = "Total Students:" summary_ws[f'B{students_row}'] = "Pending calculation" summary_ws[f'A{students_row}'].font = label_font row += 1 spent_row = row summary_ws[f'A{spent_row}'] = "Total Spent:" summary_ws[f'B{spent_row}'] = "Pending calculation" summary_ws[f'A{spent_row}'].font = label_font row += 1 row += 1 if has_filters: summary_ws[f'A{row}'] = "FILTERS APPLIED" summary_ws[f'A{row}'].font = Font(bold=True, size=12) row += 2 if export_type == 'transactions': if filters.get('start_date'): summary_ws[f'A{row}'] = "Start Date:"; summary_ws[f'B{row}'] = filters['start_date']; row += 1 if filters.get('end_date'): summary_ws[f'A{row}'] = "End Date:"; summary_ws[f'B{row}'] = filters['end_date']; row += 1 if filters.get('payment_method'): summary_ws[f'A{row}'] = "Payment Method:"; summary_ws[f'B{row}'] = filters['payment_method']; row += 1 if filters.get('transaction_type'): summary_ws[f'A{row}'] = "Transaction Type:"; summary_ws[f'B{row}'] = filters['transaction_type']; row += 1 if filters.get('class_code'): class_obj = PrivateSchoolClass.query.filter_by(code=filters['class_code']).first() summary_ws[f'A{row}'] = "Class:"; summary_ws[f'B{row}'] = class_obj.name if class_obj else filters['class_code']; row += 1 if filters.get('cashier_id'): cashier = User.query.get(filters['cashier_id']) summary_ws[f'A{row}'] = "Cashier:"; summary_ws[f'B{row}'] = cashier.name if cashier else filters['cashier_id']; row += 1 if filters.get('session_id'): session = PrivateSession.query.get(filters['session_id']) summary_ws[f'A{row}'] = "Session:"; summary_ws[f'B{row}'] = f"{session.name} (Term {session.term})" if session else filters['session_id']; row += 1 if filters.get('min_amount'): summary_ws[f'A{row}'] = "Min Amount:"; summary_ws[f'B{row}'] = f"₦{float(filters['min_amount']):,.0f}"; row += 1 if filters.get('max_amount'): summary_ws[f'A{row}'] = "Max Amount:"; summary_ws[f'B{row}'] = f"₦{float(filters['max_amount']):,.0f}"; row += 1 else: if filters.get('class_code'): class_obj = PrivateSchoolClass.query.filter_by(code=filters['class_code']).first() summary_ws[f'A{row}'] = "Class:"; summary_ws[f'B{row}'] = class_obj.name if class_obj else filters['class_code']; row += 1 summary_ws.column_dimensions['A'].width = 20 summary_ws.column_dimensions['B'].width = 30 # Data sheet data_ws = wb.create_sheet(f"{export_type.capitalize()} Data") if export_type == 'transactions': start_dt = safe_date(filters.get('start_date')) end_dt = safe_date(filters.get('end_date'), end=True) payment_method = filters.get('payment_method') transaction_type_filter = filters.get('transaction_type') min_amount = filters.get('min_amount') max_amount = filters.get('max_amount') cashier_id = filters.get('cashier_id') class_code = filters.get('class_code') session_id = filters.get('session_id') search_query_raw = filters.get('search_query') search_query = search_query_raw.lower() if search_query_raw else '' def match_date(dt): return (not start_dt or dt >= start_dt) and (not end_dt or dt <= end_dt) def match_search(text): if not search_query: return True return search_query in str(text).lower() rows = [] total_revenue = 0.0 # Enrolment split with additional fee for r in PrivateEnrollmentReceipt.query.all(): if not match_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if class_code and r.class_code != class_code: continue if session_id and r.session_id != session_id: continue if not (match_search(r.receipt_number) or match_search(r.student.name)): continue subtotal_items = sum(float(item.get('amount', 0)) for item in r.items) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 for item in r.items: amount = float(item.get('amount', 0)) item_name = item.get('name', '') txn_type = 'form' if item_name == 'Form Fee' else 'school_fees' if transaction_type_filter and transaction_type_filter != txn_type: continue if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue # Proportional discount item_discount = 0 if total_discount > 0 and subtotal_items > 0: discount_ratio = amount / subtotal_items item_discount = total_discount * discount_ratio # Proportional additional fee item_additional = 0 if total_additional > 0 and subtotal_items > 0: additional_ratio = amount / subtotal_items item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional # ==== UPDATED ROW ORDER: Subtotal, Discount Amt, Discount Reason, Additional Amt, Additional Amt Reason, Total ==== rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), txn_type, r.student.name, format_phone(r.student.phone), r.student.email or '-', item_name, 1, amount, # Subtotal (col9) item_discount, # Discount Amt (col10) r.discount_reason or '-', # Discount Reason (col11) item_additional, # Additional Amt (col12) r.additional_reason or '-', # Additional Amt Reason (col13) item_total, # Total (col14) r.payment_method, r.cashier.name, r.session.name, r.term ]) total_revenue += item_total # Standalone Form for r in PrivateFormReceipt.query.all(): if not match_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue if not (match_search(r.receipt_number) or match_search(r.student.name)): continue amount = float(r.total_amount_float) if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'form', r.student.name, format_phone(r.student.phone), r.student.email or '-', f"Form ({r.form_class_group})", 1, float(r.form_price), # Subtotal float(r.discount_amount),# Discount Amt r.discount_reason or '-',# Discount Reason 0, # Additional Amt '-', # Additional Amt Reason amount, # Total r.payment_method, r.cashier.name, r.session.name, r.term ]) total_revenue += amount # Uniform for r in PrivateUniformReceipt.query.all(): if not match_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue if not (match_search(r.receipt_number) or match_search(r.student.name)): continue subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'uniform', r.student.name, format_phone(r.student.phone), r.student.email or '-', item.get('title'), item.get('quantity', 1), item_subtotal, # Subtotal item_discount, # Discount Amt r.discount_reason or '-',# Discount Reason 0, # Additional Amt '-', # Additional Amt Reason item_total, # Total r.payment_method, r.cashier.name, r.session.name, r.term ]) total_revenue += item_total # Books for r in PrivateBookReceipt.query.all(): if not match_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue if not (match_search(r.receipt_number) or match_search(r.student.name)): continue subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'books', r.student.name, format_phone(r.student.phone), r.student.email or '-', item.get('title'), item.get('quantity', 1), item_subtotal, # Subtotal item_discount, # Discount Amt r.discount_reason or '-',# Discount Reason 0, # Additional Amt '-', # Additional Amt Reason item_total, # Total r.payment_method, r.cashier.name, r.session.name, r.term ]) total_revenue += item_total # Islamiyya receipts for r in IslamiyyaPaymentReceipt.query.all(): if not match_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if cashier_id and r.cashier_id != cashier_id: continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue if not (match_search(r.receipt_number) or match_search(r.student.name)): continue amount = r.total_amount_float if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue subtotal = r.form_fee_charged_float + r.term_fee_charged_float rows.append([ r.receipt_number, r.created_at.strftime('%Y-%m-%d %H:%M'), 'islamiyya', r.student.name, format_phone(r.student.phone), r.student.email or '-', 'Islamiyya Fees', 1, subtotal, # Subtotal r.discount_amount_float, # Discount Amt r.discount_reason or '-', # Discount Reason 0, # Additional Amt '-', # Additional Amt Reason amount, # Total r.payment_method, r.cashier.name, r.session.name, r.term ]) total_revenue += amount rows.sort(key=lambda x: x[1], reverse=True) summary_ws[f'B{revenue_row}'] = f"₦{total_revenue:,.0f}" summary_ws[f'B{transactions_row}'] = len(rows) # ==== UPDATED HEADERS: Subtotal, Discount Amt, Discount Reason, Additional Amt, Additional Amt Reason, Total ==== headers = ['Receipt Number', 'Date', 'Transaction Type', 'Student Name', 'Phone', 'Email', 'Item Name', 'Quantity', 'Subtotal', 'Discount Amt', 'Discount Reason', 'Additional Amt', 'Additional Amt Reason', 'Total', 'Payment Method', 'Cashier', 'Session', 'Term'] data_ws.append(headers) for cell in data_ws[1]: cell.font = header_font cell.alignment = Alignment(horizontal="center", vertical="center") for row_data in rows: data_ws.append(row_data) # ==== UPDATED CURRENCY COLUMNS: Subtotal (9), Discount Amt (10), Additional Amt (12), Total (14) ==== format_currency_cells(data_ws, currency_columns=[9, 10, 12, 14]) auto_width(data_ws, 50) else: # students export (unchanged) class_filter = filters.get('class_code') search_query_raw = filters.get('search_query') search_query = search_query_raw.lower() if search_query_raw else '' rows = [] students = PrivateStudent.query.all() total_students_count = 0 total_spent_all = 0.0 for s in students: if class_filter and s.class_code != class_filter: continue if search_query: if not (search_query in s.name.lower() or search_query in s.phone.lower() or (s.email and search_query in s.email.lower())): continue total_spent = 0.0 receipt_count = 0 # Enrolment receipts for r in s.receipts: receipt_count += 1 total_spent += r.total_amount_float # Book receipts for r in PrivateBookReceipt.query.filter_by(student_id=s.id): receipt_count += 1 if r.items: subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio else: item_discount = 0 total_spent += (item_subtotal - item_discount) else: total_spent += float(r.total_amount or 0) # Uniform receipts for r in PrivateUniformReceipt.query.filter_by(student_id=s.id): receipt_count += 1 subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio else: item_discount = 0 total_spent += (item_subtotal - item_discount) # Form receipts for r in PrivateFormReceipt.query.filter_by(student_id=s.id): receipt_count += 1 total_spent += r.total_amount_float # Islamiyya receipts for r in IslamiyyaPaymentReceipt.query.filter_by(student_id=s.id): receipt_count += 1 total_spent += r.total_amount_float rows.append({ 'name': s.name, 'phone': format_phone(s.phone), 'email': s.email or '-', 'class_code': s.class_code, 'class_name': s.class_obj.name if s.class_obj else '', 'created_at': s.created_at.strftime('%Y-%m-%d') if s.created_at else 'N/A', 'total_spent': total_spent, 'receipts_count': receipt_count }) total_students_count += 1 total_spent_all += total_spent summary_ws[f'B{students_row}'] = total_students_count summary_ws[f'B{spent_row}'] = f"₦{total_spent_all:,.0f}" headers = ['Name', 'Phone', 'Email', 'Class Code', 'Class Name', 'Registration Date', 'Total Spent', 'Receipts Count'] data_ws.append(headers) for cell in data_ws[1]: cell.font = header_font cell.alignment = Alignment(horizontal="center", vertical="center") for row_data in rows: data_ws.append([ row_data['name'], row_data['phone'], row_data['email'], row_data['class_code'], row_data['class_name'], row_data['created_at'], row_data['total_spent'], row_data['receipts_count'] ]) format_currency_cells(data_ws, currency_columns=[7]) auto_width(data_ws, 40) if 'Sheet' in wb.sheetnames: del wb['Sheet'] output = BytesIO() wb.save(output) output.seek(0) filename = f"private_{export_type}_{'filtered' if has_filters else 'full'}_export.xlsx" return Response( output.getvalue(), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', headers={'Content-Disposition': f'attachment; filename={filename}'} ) # ==================== PRIVATE SCHOOL CASHIER MY ANALYSIS ==================== @app.route('/api/private/cashier/transactions', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def get_private_cashier_transactions(): """Get transactions for the logged-in cashier with filtering.""" start_date = request.args.get('start_date') end_date = request.args.get('end_date') payment_method = request.args.get('payment_method') transaction_type = request.args.get('transaction_type') # 'form', 'school_fees', 'uniform', 'books' class_code = request.args.get('class_code') # fee_item = request.args.get('fee_item') min_amount = request.args.get('min_amount', type=float) max_amount = request.args.get('max_amount', type=float) session_id = request.args.get('session_id') all_transactions = [] # ---------- Enrolment receipts – split Form Fee vs School Fees ---------- for r in PrivateEnrollmentReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type not in ['form', 'school_fees']: continue if class_code and r.class_code != class_code: continue if session_id and r.session_id != session_id: continue subtotal_items = sum(float(item.get('amount', 0)) for item in r.items) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 for item in r.items: amount = float(item.get('amount', 0)) item_name = item.get('name', '') txn_type = 'form' if item_name == 'Form Fee' else 'school_fees' if transaction_type and transaction_type != txn_type: continue if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal_items > 0: discount_ratio = amount / subtotal_items item_discount = total_discount * discount_ratio # Proportional additional fee item_additional = 0 if total_additional > 0 and subtotal_items > 0: additional_ratio = amount / subtotal_items item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional all_transactions.append({ 'id': f"{r.id}_{item['id']}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': txn_type, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': amount, 'discount_amount': item_discount, 'discount_reason': r.discount_reason if r.discount_amount else None, 'discount_percentage': (item_discount / amount * 100) if amount > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': item_name, 'quantity': 1, 'session_id': r.session_id, 'additional_amount': item_additional, 'additional_reason': r.additional_reason or '', }) # ---------- Standalone Form Receipts ---------- for r in PrivateFormReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'form': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue amount = float(r.total_amount_float) if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue all_transactions.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'form', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': amount, 'subtotal': float(r.form_price), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'discount_percentage': (float(r.discount_amount) / float(r.form_price) * 100) if float(r.form_price) > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': f"Form ({r.form_class_group})", 'quantity': 1, 'session_id': r.session_id }) # ---------- Uniform Receipts ---------- for r in PrivateUniformReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'uniform': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount all_transactions.append({ 'id': f"{r.id}_{item.get('uniform_id')}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'uniform', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': item_subtotal, 'discount_amount': item_discount, 'discount_reason': r.discount_reason, 'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': item.get('title'), 'quantity': item.get('quantity'), 'session_id': r.session_id }) # ---------- Book receipts – each book item with proportional discount (unchanged) ---------- for r in PrivateBookReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'books': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount all_transactions.append({ 'id': f"{r.id}_{item.get('book_id')}", 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'books', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': item_total, 'subtotal': item_subtotal, 'discount_amount': item_discount, 'discount_reason': r.discount_reason, 'discount_percentage': (item_discount / item_subtotal * 100) if item_subtotal > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': item.get('title'), 'quantity': item.get('quantity'), 'session_id': r.session_id }) # ---------- Islamiyya receipts ---------- for r in IslamiyyaPaymentReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'islamiyya': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue amount = r.total_amount_float if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue all_transactions.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'date': r.created_at, 'type': 'islamiyya', 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'amount': amount, 'subtotal': r.form_fee_charged_float + r.term_fee_charged_float, 'discount_amount': r.discount_amount_float, 'discount_reason': r.discount_reason, 'discount_percentage': (r.discount_amount_float / (r.form_fee_charged_float + r.term_fee_charged_float) * 100) if (r.form_fee_charged_float + r.term_fee_charged_float) > 0 else 0, 'payment_method': r.payment_method, 'cashier_id': r.cashier_id, 'cashier_name': r.cashier.name, 'class_code': r.student.class_code, 'session_name': r.session.name, 'term': r.term, 'item_title': 'Islamiyya Fees', 'quantity': 1, 'session_id': r.session_id }) all_transactions.sort(key=lambda x: x['date'], reverse=True) total_amount = sum(t['amount'] for t in all_transactions) total_discount = sum(t['discount_amount'] for t in all_transactions) return jsonify({ 'transactions': [{**t, 'date': t['date'].isoformat()} for t in all_transactions], 'total_count': len(all_transactions), 'total_amount': total_amount, 'total_discount': total_discount }) @app.route('/api/private/cashier/summary', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def get_private_cashier_summary(): """Summary for the logged-in cashier (Total Revenue, Total Transactions, Revenue Today).""" start_date = request.args.get('start_date') end_date = request.args.get('end_date') payment_method = request.args.get('payment_method') transaction_type = request.args.get('transaction_type') class_code = request.args.get('class_code') min_amount = request.args.get('min_amount', type=float) max_amount = request.args.get('max_amount', type=float) session_id = request.args.get('session_id') # Local timezone (UTC+1 for Nigeria) now_local = datetime.now() today_start_local = datetime(now_local.year, now_local.month, now_local.day, 0, 0, 0) tomorrow_start_local = today_start_local + timedelta(days=1) total_revenue = 0.0 total_transactions = 0 revenue_today = 0.0 # Enrolment split for r in PrivateEnrollmentReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type not in ['form', 'school_fees']: continue if class_code and r.class_code != class_code: continue if session_id and r.session_id != session_id: continue date_utc = r.created_at date_local = date_utc is_today_local = (today_start_local <= date_local < tomorrow_start_local) subtotal_items = sum(float(item.get('amount', 0)) for item in r.items) total_discount = float(r.discount_amount) if r.discount_amount else 0 total_additional = float(r.additional_amount) if r.additional_amount else 0 for item in r.items: amount = float(item.get('amount', 0)) item_name = item.get('name', '') txn_type = 'form' if item_name == 'Form Fee' else 'school_fees' if transaction_type and transaction_type != txn_type: continue if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal_items > 0: discount_ratio = amount / subtotal_items item_discount = total_discount * discount_ratio # Proportional additional fee item_additional = 0 if total_additional > 0 and subtotal_items > 0: additional_ratio = amount / subtotal_items item_additional = total_additional * additional_ratio item_total = amount - item_discount + item_additional total_revenue += item_total total_transactions += 1 if is_today_local: revenue_today += item_total # Standalone Form for r in PrivateFormReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'form': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue date_utc = r.created_at date_local = date_utc is_today_local = (today_start_local <= date_local < tomorrow_start_local) amount = float(r.total_amount_float) if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue total_revenue += amount total_transactions += 1 if is_today_local: revenue_today += amount # Uniform for r in PrivateUniformReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'uniform': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue date_utc = r.created_at date_local = date_utc is_today_local = (today_start_local <= date_local < tomorrow_start_local) subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount total_revenue += item_total total_transactions += 1 if is_today_local: revenue_today += item_total # Books for r in PrivateBookReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'books': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue date_utc = r.created_at date_local = date_utc is_today_local = (today_start_local <= date_local < tomorrow_start_local) subtotal = float(r.total_amount or 0) + float(r.discount_amount or 0) total_discount = float(r.discount_amount or 0) for item in r.items: item_subtotal = float(item.get('total', 0)) if (min_amount is not None and item_subtotal < min_amount) or (max_amount is not None and item_subtotal > max_amount): continue item_discount = 0 if total_discount > 0 and subtotal > 0: discount_ratio = item_subtotal / subtotal item_discount = total_discount * discount_ratio item_total = item_subtotal - item_discount total_revenue += item_total total_transactions += 1 if is_today_local: revenue_today += item_total # ---------- Islamiyya receipts ---------- for r in IslamiyyaPaymentReceipt.query.filter_by(cashier_id=current_user.id).all(): if not _check_date(r.created_at, start_date, end_date): continue if payment_method and r.payment_method != payment_method: continue if transaction_type and transaction_type != 'islamiyya': continue if class_code and r.student.class_code != class_code: continue if session_id and r.session_id != session_id: continue date_utc = r.created_at date_local = date_utc is_today_local = (today_start_local <= date_local < tomorrow_start_local) amount = r.total_amount_float if (min_amount is not None and amount < min_amount) or (max_amount is not None and amount > max_amount): continue total_revenue += amount total_transactions += 1 if is_today_local: revenue_today += amount return jsonify({ 'total_revenue': total_revenue, 'total_transactions': total_transactions, 'revenue_today': revenue_today }) @app.route('/api/private/receipts', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Director') # or 'Analyst'? Use appropriate role. def get_private_receipts(): """Return combined enrollment, book purchase, uniform purchase, and Islamiyya receipts for private school.""" receipt_type = request.args.get('type') # 'enrolment', 'book', 'uniform', 'islamiyya', or None for all start_date = request.args.get('start_date') end_date = request.args.get('end_date') student_name = request.args.get('student_name') receipt_number = request.args.get('receipt_number') payment_method = request.args.get('payment_method') cashier_id = request.args.get('cashier_id') def filter_date(dt): if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if dt < start: return False if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if dt > end: return False return True receipts = [] # 1. Enrollment receipts if receipt_type is None or receipt_type == 'enrolment': query = PrivateEnrollmentReceipt.query if cashier_id: query = query.filter_by(cashier_id=cashier_id) for r in query.all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue subtotal = sum(item['amount'] for item in r.items) discount_percentage = (r.discount_amount_float / subtotal * 100) if subtotal > 0 else 0 receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'enrolment', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'discount_percentage': round(discount_percentage, 2), 'additional_amount': float(r.additional_amount), 'additional_reason': r.additional_reason, 'subtotal': subtotal, 'details': { 'items': r.items, 'class_code': r.class_code, 'class_name': r.student.class_obj.name if r.student.class_obj else '', 'session_name': r.session.name, 'term': r.term, } }) # 2. Book purchase receipts if receipt_type is None or receipt_type == 'book': query = PrivateBookReceipt.query if cashier_id: query = query.filter_by(cashier_id=cashier_id) for r in query.all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'book', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'details': { 'items': r.items, 'session_name': r.session.name, 'term': r.term, } }) # 3. Uniform purchase receipts (NEW) if receipt_type is None or receipt_type == 'uniform': query = PrivateUniformReceipt.query if cashier_id: query = query.filter_by(cashier_id=cashier_id) for r in query.all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue # Calculate subtotal and discount percentage for consistency subtotal = sum(item.get('total', 0) for item in r.items) discount_percentage = (r.discount_amount / subtotal * 100) if subtotal > 0 else 0 receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'uniform', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'discount_percentage': round(discount_percentage, 2), 'subtotal': subtotal, 'details': { 'items': r.items, 'session_name': r.session.name, 'term': r.term, } }) # 5. Form receipts if receipt_type is None or receipt_type == 'form': query = PrivateFormReceipt.query if cashier_id: query = query.filter_by(cashier_id=cashier_id) for r in query.all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue # Calculate discount percentage (same as model's to_dict) price = float(r.form_price) if r.form_price else 0 discount = float(r.discount_amount) if r.discount_amount else 0 discount_percentage = (discount / price * 100) if price > 0 else 0 receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'form', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': discount, 'discount_reason': r.discount_reason, 'discount_percentage': round(discount_percentage, 2), # ← added 'form_price': float(r.form_price), 'form_class_group': r.form_class_group, 'session_name': r.session.name, 'term': r.term, 'details': { 'form_price': float(r.form_price), 'form_class_group': r.form_class_group, 'session_name': r.session.name, 'term': r.term, } }) # 4. Islamiyya receipts if receipt_type is None or receipt_type == 'islamiyya': query = IslamiyyaPaymentReceipt.query if cashier_id: query = query.filter_by(cashier_id=cashier_id) for r in query.all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'islamiyya', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': r.total_amount_float, 'discount_amount': r.discount_amount_float, 'discount_reason': r.discount_reason, 'details': { 'form_fee': r.form_fee_charged_float, 'term_fee': r.term_fee_charged_float, 'student_type': r.student_type, 'session_name': r.session.name, 'term': r.term, } }) receipts.sort(key=lambda x: x['date'], reverse=True) return jsonify(receipts) @app.route('/api/private/cashier/receipts', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def get_private_cashier_receipts(): """Return combined enrollment, book purchase, uniform purchase, and Islamiyya receipts for the logged-in cashier.""" receipt_type = request.args.get('type') # 'enrolment', 'book', 'uniform', 'islamiyya', or None for all start_date = request.args.get('start_date') end_date = request.args.get('end_date') student_name = request.args.get('student_name') receipt_number = request.args.get('receipt_number') payment_method = request.args.get('payment_method') def filter_date(dt): if start_date: start = datetime.fromisoformat(start_date) if 'T' in start_date else datetime.fromisoformat(start_date + 'T00:00:00') if dt < start: return False if end_date: end = datetime.fromisoformat(end_date) if 'T' in end_date else datetime.fromisoformat(end_date + 'T23:59:59') if dt > end: return False return True receipts = [] # 1. Enrollment receipts if receipt_type is None or receipt_type == 'enrolment': for r in PrivateEnrollmentReceipt.query.filter_by(cashier_id=current_user.id).all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue subtotal = sum(item['amount'] for item in r.items) discount_percentage = (r.discount_amount_float / subtotal * 100) if subtotal > 0 else 0 receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'enrolment', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'discount_percentage': round(discount_percentage, 2), 'additional_amount': float(r.additional_amount), 'additional_reason': r.additional_reason or '', 'subtotal': subtotal, 'details': { 'items': r.items, 'class_code': r.class_code, 'class_name': r.student.class_obj.name if r.student.class_obj else '', 'session_name': r.session.name, 'term': r.term, } }) # 2. Book purchase receipts if receipt_type is None or receipt_type == 'book': for r in PrivateBookReceipt.query.filter_by(cashier_id=current_user.id).all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'book', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'details': { 'items': r.items, 'session_name': r.session.name, 'term': r.term, } }) # 5. Form receipts (standalone form purchases) if receipt_type is None or receipt_type == 'form': for r in PrivateFormReceipt.query.filter_by(cashier_id=current_user.id).all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue # Calculate discount percentage (same as model's to_dict) price = float(r.form_price) if r.form_price else 0 discount = float(r.discount_amount) if r.discount_amount else 0 discount_percentage = (discount / price * 100) if price > 0 else 0 receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'form', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': discount, 'discount_reason': r.discount_reason, 'discount_percentage': round(discount_percentage, 2), # ← added 'form_price': float(r.form_price), 'form_class_group': r.form_class_group, 'session_name': r.session.name, 'term': r.term, 'details': { 'form_price': float(r.form_price), 'form_class_group': r.form_class_group, 'session_name': r.session.name, 'term': r.term, } }) # 3. Uniform purchase receipts (NEW) if receipt_type is None or receipt_type == 'uniform': for r in PrivateUniformReceipt.query.filter_by(cashier_id=current_user.id).all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue # Calculate subtotal and discount percentage for consistency subtotal = sum(item.get('total', 0) for item in r.items) discount_percentage = (r.discount_amount / subtotal * 100) if subtotal > 0 else 0 receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'uniform', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': float(r.total_amount), 'discount_amount': float(r.discount_amount), 'discount_reason': r.discount_reason, 'discount_percentage': round(discount_percentage, 2), 'subtotal': subtotal, 'details': { 'items': r.items, 'session_name': r.session.name, 'term': r.term, } }) # 4. Islamiyya receipts if receipt_type is None or receipt_type == 'islamiyya': for r in IslamiyyaPaymentReceipt.query.filter_by(cashier_id=current_user.id).all(): if not filter_date(r.created_at): continue if payment_method and r.payment_method != payment_method: continue if receipt_number and receipt_number.lower() not in r.receipt_number.lower(): continue if student_name and student_name.lower() not in r.student.name.lower(): continue receipts.append({ 'id': r.id, 'receipt_number': r.receipt_number, 'type': 'islamiyya', 'student_id': r.student_id, 'student_name': r.student.name, 'student_phone': r.student.phone, 'student_email': r.student.email or '', 'date': r.created_at.isoformat(), 'payment_method': r.payment_method, 'cashier_name': r.cashier.name, 'total_amount': r.total_amount_float, 'discount_amount': r.discount_amount_float, 'discount_reason': r.discount_reason, 'details': { 'form_fee': r.form_fee_charged_float, 'term_fee': r.term_fee_charged_float, 'student_type': r.student_type, 'session_name': r.session.name, 'term': r.term, 'discount_percentage': (r.discount_amount_float / (r.form_fee_charged_float + r.term_fee_charged_float) * 100) if (r.form_fee_charged_float + r.term_fee_charged_float) > 0 else 0 } }) receipts.sort(key=lambda x: x['date'], reverse=True) return jsonify(receipts) @app.route('/api/english/gate-passes', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def get_english_gate_passes(): session_id = request.args.get('session_id') search = request.args.get('search', '').strip() if not session_id: return jsonify({'error': 'session_id is required'}), 400 session = EnglishSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Session not found'}), 404 # Get all enrollment/class_change receipts (with pass_number) for this session receipts = EnglishRegistrationReceipt.query.filter( EnglishRegistrationReceipt.session_id == session_id, EnglishRegistrationReceipt.pass_number.isnot(None), EnglishRegistrationReceipt.receipt_type.in_(['enrollment', 'class_change']) ).order_by(EnglishRegistrationReceipt.pass_number).all() # Deduplicate by student – take earliest receipt per student student_map = {} for r in receipts: if r.student_id not in student_map: student_map[r.student_id] = r passes = [] for r in student_map.values(): student = r.student if search and search.lower() not in student.name.lower() and search.lower() not in (student.phone or '').lower(): continue class_name = '' if student.class_code: english_class = EnglishClass.query.filter_by(code=student.class_code).first() class_name = english_class.name if english_class else student.class_code passes.append({ 'pass_no': r.pass_number, 'student_id': student.id, 'student_name': student.name, 'student_phone': student.phone, 'student_email': student.email or '', 'receipt_number': r.receipt_number, 'class_code': student.class_code, 'class_name': class_name, 'level_name': student.level_name or '-', 'enrollment_date': r.created_at.isoformat() }) return jsonify({ 'session': session.to_dict(), 'passes': passes, 'total': len(passes) }) @app.route('/api/english/gate-pass/', methods=['GET']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def get_english_gate_pass(student_id): session_id = request.args.get('session_id') if not session_id: return jsonify({'error': 'session_id is required'}), 400 session = EnglishSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Session not found'}), 404 # Get the student's earliest enrollment/class_change receipt for this session receipt = EnglishRegistrationReceipt.query.filter( EnglishRegistrationReceipt.session_id == session_id, EnglishRegistrationReceipt.student_id == student_id, EnglishRegistrationReceipt.pass_number.isnot(None), EnglishRegistrationReceipt.receipt_type.in_(['enrollment', 'class_change']) ).order_by(EnglishRegistrationReceipt.created_at.asc()).first() if not receipt: return jsonify({'error': 'No enrollment found for this student in the selected session'}), 404 student = receipt.student pass_no = receipt.pass_number if pass_no is None: return jsonify({'error': 'Invalid pass number'}), 500 # Get class display name class_name = '' if student.class_code: english_class = EnglishClass.query.filter_by(code=student.class_code).first() class_name = english_class.name if english_class else student.class_code # Get time text and period from session time_text = session.time_info.time_text if session.time_info else 'Not Set' starting = session.period.starting.strftime('%B %Y') if session.period else 'Not Set' ending = session.period.ending.strftime('%B %Y') if session.period else 'Not Set' # Format receipt number for display: e.g., 0001NC9-11 suffix = student.class_code # Use time_text first 5 chars or fallback time_suffix = time_text if time_text else 'T' formatted_receipt_no = f"{pass_no:04d}{suffix}{time_suffix}" # Level name: hyphen if missing level_display = student.level_name if student.level_name else '-' color_index = session.pass_color # --- Check for student photo --- student_photo_base64 = None photo_path = os.path.join('photos', f"{student_id}.jpg") if os.path.exists(photo_path): try: with open(photo_path, 'rb') as f: student_photo_base64 = base64.b64encode(f.read()).decode('utf-8') except Exception as e: print(f"Could not load photo for student {student_id}: {e}") try: img_base64 = process_college_pass_english( receipt_no=formatted_receipt_no, name=student.name, class_name=class_name, level_name=level_display, time=time_text, phone=student.phone, starting=starting, ending=ending, color_index=color_index, student_photo_base64=student_photo_base64 ) return jsonify({ 'image': img_base64, 'pass_no': pass_no, 'student_name': student.name }) except Exception as e: return jsonify({'error': f'Failed to generate gate pass: {str(e)}'}), 500 @app.route('/api/private/gate-passes', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def get_private_gate_passes(): session_id = request.args.get('session_id') search = request.args.get('search', '').strip() if not session_id: return jsonify({'error': 'session_id is required'}), 400 session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Session not found'}), 404 # Query all enrollment receipts for this session, ordered by pass_number receipts = PrivateEnrollmentReceipt.query.filter_by(session_id=session_id)\ .order_by(PrivateEnrollmentReceipt.pass_number).all() # Optional filter by student (search) filtered = [] for r in receipts: student = r.student if search and search.lower() not in student.name.lower() \ and search.lower() not in (student.phone or '').lower() \ and search.lower() not in (student.email or '').lower(): continue filtered.append({ 'pass_no': r.pass_number, 'student_id': student.id, 'student_name': student.name, 'student_phone': student.phone, 'student_email': student.email or '', 'receipt_number': r.receipt_number, 'class_code': r.class_code, 'class_name': student.class_obj.name if student.class_obj else '', 'enrollment_date': r.created_at.isoformat() }) return jsonify({ 'session': session.to_dict(), 'passes': filtered, 'total': len(filtered) }) @app.route('/api/private/gate-pass/', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def get_private_gate_pass(student_id): """Generate and return a gate pass image (base64) for a student in a given session.""" session_id = request.args.get('session_id') if not session_id: return jsonify({'error': 'session_id is required'}), 400 session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Session not found'}), 404 # Get the student's enrollment receipt (earliest one – original pass number) receipt = PrivateEnrollmentReceipt.query.filter_by( student_id=student_id, session_id=session_id ).order_by(PrivateEnrollmentReceipt.created_at.asc()).first() if not receipt: return jsonify({'error': 'No enrollment found for this student in the selected session'}), 404 # Use the stored pass_number (already sequential per session) pass_no = receipt.pass_number if pass_no is None: # Fallback for old data – compute on the fly (should not happen after migration) all_receipts = PrivateEnrollmentReceipt.query.filter_by(session_id=session_id)\ .order_by(PrivateEnrollmentReceipt.created_at).all() unique_student_ids = [] for idx, r in enumerate(all_receipts, start=1): if r.student_id not in unique_student_ids: unique_student_ids.append(r.student_id) if r.student_id == student_id: pass_no = idx break if pass_no is None: return jsonify({'error': 'Student not found in session passes'}), 404 # Format term text (e.g., "1st", "2nd", "3rd") term_suffix = {1: 'st', 2: 'nd', 3: 'rd'}.get(session.term) term_str = f"{session.term}{term_suffix}".upper() session_name = session.name # color_letter = session.pass_color color_index = session.pass_color try: # img_base64 = process_college_pass(term_str, session_name, pass_no, color_letter) img_base64 = process_college_pass( term_str, session_name, pass_no, color_index, receipt.student.name, get_class_display_name_from_code(receipt.student.class_code) ) return jsonify({ 'image': img_base64, 'pass_no': pass_no, 'student_name': receipt.student.name }) except Exception as e: return jsonify({'error': 'Failed to generate gate pass'}), 500 @app.route('/api/private/uniforms', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def get_private_uniforms(): uniforms = PrivateUniform.query.all() return jsonify([u.to_dict() for u in uniforms]) @app.route('/api/private/uniforms/add-uniform', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def add_private_uniform(): data = request.get_json() title = data.get('title') price = data.get('price') quantity = data.get('quantity', 0) class_group = data.get('class_group') if not title or price is None or not class_group: return jsonify({'error': 'Title, price and class group are required'}), 400 uniform = PrivateUniform( title=title, price=price, quantity=quantity, class_group=class_group ) db.session.add(uniform) db.session.flush() # to get uniform.id # 2. Create associated fee item (one‑time, not termly) fee_item = PrivateFeeItem( name=f"{title} ({class_group})", amount=price, termly=False ) db.session.add(fee_item) db.session.flush() # 3. Assign fee item to all classes of this group class_codes = get_class_codes_for_group(class_group) for code in class_codes: assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code) db.session.add(assignment) # 4. Link uniform to fee item uniform.fee_item_id = fee_item.id db.session.commit() return jsonify({'success': True, 'uniform': uniform.to_dict()}) @app.route('/api/private/uniforms/edit-uniform/', methods=['PUT']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def edit_private_uniform(uniform_id): uniform = PrivateUniform.query.get_or_404(uniform_id) data = request.get_json() # Store old values for comparison old_group = uniform.class_group old_price = uniform.price old_title = uniform.title # Update uniform fields if 'title' in data: uniform.title = data['title'] if 'price' in data: if data['price'] <= 0: return jsonify({'error': 'Price must be greater than 0'}), 400 uniform.price = data['price'] if 'quantity' in data: if data['quantity'] < 0: return jsonify({'error': 'Quantity cannot be negative'}), 400 uniform.quantity = data['quantity'] if 'class_group' in data: uniform.class_group = data['class_group'] # Update linked fee item if it exists if uniform.fee_item_id: fee_item = PrivateFeeItem.query.get(uniform.fee_item_id) if fee_item: # Name might change if title or group changes new_name = f"{uniform.title} ({uniform.class_group})" if fee_item.name != new_name: fee_item.name = new_name # Amount might change if uniform.price != old_price: fee_item.amount = uniform.price # If class group changed, reassign classes if uniform.class_group != old_group: # Delete old assignments PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete() # Create new assignments for the new group new_codes = get_class_codes_for_group(uniform.class_group) for code in new_codes: assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code) db.session.add(assignment) db.session.add(fee_item) db.session.commit() return jsonify({'success': True, 'uniform': uniform.to_dict()}) @app.route('/api/private/uniforms/delete-uniform/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def delete_private_uniform(uniform_id): uniform = PrivateUniform.query.get(uniform_id) if not uniform: return jsonify({'error': 'Uniform not found'}), 404 # Delete linked fee item and its assignments if uniform.fee_item_id: fee_item = PrivateFeeItem.query.get(uniform.fee_item_id) if fee_item: # Delete assignments first PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete() db.session.delete(fee_item) db.session.delete(uniform) db.session.commit() return jsonify({'success': True, 'message': 'Uniform deleted successfully'}), 200 @app.route('/api/private/purchase-uniforms', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def purchase_private_uniforms(): data = request.get_json() student_id = data.get('student_id') if not student_id: return jsonify({'error': 'Student ID is required'}), 400 session_id = data.get('session_id') if not session_id: return jsonify({'error': 'Session ID is required'}), 400 items = data.get('items', []) if not items or not isinstance(items, list): return jsonify({'error': 'No items provided'}), 400 payment_method = data.get('payment_method') if not payment_method: return jsonify({'error': 'Payment method is required'}), 400 discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') # Validate session session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Invalid or deleted session'}), 400 # Validate student student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 if discount_amount < 0: return jsonify({'error': 'Discount amount cannot be negative'}), 400 try: subtotal = 0 purchase_items = [] for item in items: uniform = PrivateUniform.query.get(item.get('uniform_id')) if not uniform: return jsonify({'error': 'Uniform not found'}), 404 qty = item.get('quantity') if not qty or qty <= 0: return jsonify({'error': 'Invalid quantity'}), 400 if uniform.quantity < qty: return jsonify({'error': f'Not enough stock for {uniform.title}'}), 400 item_total = uniform.price * qty subtotal += item_total purchase_items.append({ 'uniform_id': uniform.id, 'title': uniform.title, 'quantity': qty, 'price': float(uniform.price), 'class_group': uniform.class_group, 'total': float(item_total) }) uniform.quantity -= qty if discount_amount > subtotal: return jsonify({ 'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 total_amount = subtotal - discount_amount discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({'error': 'Discount reason required'}), 400 receipt_number = generate_receipt_number() receipt = PrivateUniformReceipt( receipt_number=receipt_number, student_id=student.id, session_id=session.id, term=session.term, total_amount=total_amount, discount_amount=discount_amount, discount_reason=discount_reason, payment_method=payment_method, cashier_id=current_user.id, items=purchase_items ) db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'receipt': receipt.to_dict() }) except Exception as e: db.session.rollback() return jsonify({'error': f'Purchase failed: {str(e)}'}), 500 @app.route('/api/private/forms', methods=['GET']) @login_required def get_private_forms(): forms = PrivateFormStock.query.all() return jsonify([f.to_dict() for f in forms]) @app.route('/api/private/forms/add-form', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def add_private_form(): data = request.get_json() class_group = data.get('class_group') price = data.get('price') quantity = data.get('quantity', 0) if not class_group or price is None: return jsonify({'error': 'Class group and price are required'}), 400 if price <= 0: return jsonify({'error': 'Price must be greater than 0'}), 400 if quantity < 0: return jsonify({'error': 'Quantity cannot be negative'}), 400 form = PrivateFormStock( class_group=class_group, price=price, quantity=quantity ) db.session.add(form) db.session.flush() fee_item = PrivateFeeItem( name=f"Admission Form ({class_group})", amount=price, termly=False # session / one‑time ) db.session.add(fee_item) db.session.flush() # 3. Assign all classes of this group to the fee item class_codes = get_class_codes_for_group(class_group) for code in class_codes: assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code) db.session.add(assignment) # 4. Link form to fee item form.fee_item_id = fee_item.id db.session.commit() return jsonify({'success': True, 'form': form.to_dict()}) @app.route('/api/private/forms/edit-form/', methods=['PUT']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def edit_private_form(form_id): form = PrivateFormStock.query.get_or_404(form_id) data = request.get_json() # Store original values for comparison old_group = form.class_group old_price = form.price if 'class_group' in data: form.class_group = data['class_group'] if 'price' in data: if data['price'] <= 0: return jsonify({'error': 'Price must be greater than 0'}), 400 form.price = data['price'] if 'quantity' in data: if data['quantity'] < 0: return jsonify({'error': 'Quantity cannot be negative'}), 400 form.quantity = data['quantity'] # If the form has a linked fee item, update it if form.fee_item_id: fee_item = PrivateFeeItem.query.get(form.fee_item_id) if fee_item: # Update name if group changed if form.class_group != old_group: fee_item.name = f"Admission Form ({form.class_group})" # Update amount if price changed if form.price != old_price: fee_item.amount = form.price # If group changed, reassign classes if form.class_group != old_group: # Delete old assignments PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete() # Create new assignments for the new group new_codes = get_class_codes_for_group(form.class_group) for code in new_codes: assignment = PrivateFeeAssignment(fee_item_id=fee_item.id, class_code=code) db.session.add(assignment) db.session.add(fee_item) db.session.commit() return jsonify({'success': True, 'form': form.to_dict()}) @app.route('/api/private/forms/delete-form/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def delete_private_form(form_id): form = PrivateFormStock.query.get_or_404(form_id) if form.receipts: return jsonify({'error': 'Cannot delete form with existing sales receipts'}), 400 # If there is a linked fee item, delete it first if form.fee_item_id: fee_item = PrivateFeeItem.query.get(form.fee_item_id) if fee_item: # Manually delete assignments PrivateFeeAssignment.query.filter_by(fee_item_id=fee_item.id).delete() # Delete the fee item db.session.delete(fee_item) db.session.delete(form) db.session.commit() return jsonify({'success': True}) @app.route('/api/private/purchase-form', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def purchase_private_form(): data = request.get_json() # Student info (either existing or new) student_id = data.get('student_id') new_student_data = data.get('new_student_data') session_id = data.get('session_id') form_id = data.get('form_id') payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') if not session_id: return jsonify({'error': 'Session is required'}), 400 if not form_id: return jsonify({'error': 'Form is required'}), 400 if not payment_method: return jsonify({'error': 'Payment method is required'}), 400 if discount_amount < 0: return jsonify({'error': 'Discount cannot be negative'}), 400 # Validate session session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Invalid or deleted session'}), 400 # Validate form stock form = PrivateFormStock.query.get(form_id) if not form: return jsonify({'error': 'Form not found'}), 404 if form.quantity < 1: return jsonify({'error': 'Form out of stock'}), 400 # Determine student student = None if student_id: student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 elif new_student_data: name = (new_student_data.get('name') or '').strip() phone = (new_student_data.get('phone') or '').strip() email = (new_student_data.get('email') or '').strip() if not name or not phone: return jsonify({'error': 'Name and phone are required for new student'}), 400 if email and not is_valid_email(email): return jsonify({'error': 'Invalid email address'}), 400 student = PrivateStudent( name=name, phone=phone, email=email if email else None, class_code=None # No class for form-only purchase ) db.session.add(student) db.session.flush() else: return jsonify({'error': 'Either select an existing student or provide new student details'}), 400 # Calculate total subtotal = form.price if discount_amount > subtotal: return jsonify({ 'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed price (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({'error': 'Discount reason required'}), 400 total_amount = subtotal - discount_amount # Deduct stock form.quantity -= 1 # Create receipt receipt_number = generate_receipt_number() receipt = PrivateFormReceipt( receipt_number=receipt_number, student_id=student.id, session_id=session.id, term=session.term, form_id=form.id, form_price=form.price, form_class_group=form.class_group, discount_amount=discount_amount, discount_reason=discount_reason, total_amount=total_amount, payment_method=payment_method, cashier_id=current_user.id ) db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'receipt': receipt.to_dict(), 'student': student.to_dict() }) @app.route('/api/private/bulk-enroll-template', methods=['GET']) @login_required @require_password_changed def download_bulk_enroll_template(): """Download Excel template for bulk enrollment""" wb = Workbook() ws = wb.active ws.title = "Bulk Enrollment" # Headers headers = ['name', 'phone', 'email', 'class_code'] ws.append(headers) # Example rows examples = [ ['John Doe', '08012345678', 'john@example.com', 'N1'], ['Jane Smith', '08098765432', '', 'P3'], ] for row in examples: ws.append(row) # Add instruction notes ws['F1'] = 'Valid class codes:' ws['F2'] = 'PN (Pre-Nursery)' ws['F3'] = 'N1 (Nursery 1)' ws['F4'] = 'N2 (Nursery 2)' ws['F5'] = 'P1, P2, P3, P4, P5 (Primary 1-5)' ws['F6'] = 'JSS1, JSS2, JSS3 (JSS 1-3)' ws['F7'] = 'SSS1, SSS2, SSS3 (SSS 1-3)' ws['F9'] = 'Note: Email is optional. Phone numbers should be 11 digits.' # Auto-size columns for col in ws.columns: max_length = 0 col_letter = col[0].column_letter for cell in col: try: if cell.value and len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 30) ws.column_dimensions[col_letter].width = adjusted_width output = BytesIO() wb.save(output) output.seek(0) return Response( output.getvalue(), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', headers={ 'Content-Disposition': 'attachment; filename="private_bulk_enrollment_template.xlsx"' } ) @app.route('/api/private/bulk-enroll', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'EnrollmentOfficer') def bulk_enroll_private_students(): """Upload Excel file, validate, preview, and bulk enroll. Supports two formats: 1. Original: name, phone, class_code, email (phone required) 2. New: First Name, Other Names, Class, Phone No., Gmail (phone optional) """ if 'file' not in request.files: return jsonify({'error': 'No file uploaded'}), 400 file = request.files['file'] if file.filename == '': return jsonify({'error': 'Empty filename'}), 400 if not file.filename.lower().endswith(('.xlsx', '.xls')): return jsonify({'error': 'File must be Excel (.xlsx or .xls)'}), 400 try: df = pd.read_excel(file, engine='openpyxl') # Detect format by checking for new-style column is_new_format = 'First Name' in df.columns # ---------- ORIGINAL FORMAT (unchanged) ---------- if not is_new_format: required_columns = ['name', 'phone', 'class_code'] for col in required_columns: if col not in df.columns: return jsonify({'error': f'Missing column: {col}'}), 400 preview_data = [] errors = [] valid_rows = [] for idx, row in df.iterrows(): row_num = idx + 2 name = str(row.get('name', '')).strip() if pd.notna(row.get('name')) else '' phone = str(row.get('phone', '')).strip() if pd.notna(row.get('phone')) else '' email = str(row.get('email', '')).strip() if pd.notna(row.get('email')) else '' class_short = str(row.get('class_code', '')).strip().upper() if pd.notna(row.get('class_code')) else '' row_errors = [] if not name: row_errors.append('Name is required') if not phone: row_errors.append('Phone is required') if email and not is_valid_email(email): row_errors.append('Invalid email address') if not class_short: row_errors.append('Class code is required') elif class_short not in SHORT_CODE_TO_CLASS_CODE: row_errors.append(f'Invalid class code: {class_short}') actual_class_name = '' if class_short in SHORT_CODE_TO_CLASS_CODE: actual_class_name = get_class_display_name_from_code(SHORT_CODE_TO_CLASS_CODE[class_short]) if row_errors: errors.append({ 'row': row_num, 'name': name or '(empty)', 'phone': phone or '(empty)', 'email': email or '', 'class_code_entered': class_short or '(empty)', 'errors': row_errors }) else: valid_rows.append({ 'name': name, 'phone': phone, 'email': email if email else None, 'class_code': SHORT_CODE_TO_CLASS_CODE[class_short], 'class_short': class_short, 'actual_class_name': actual_class_name }) preview_data.append({ 'name': name, 'phone': phone, 'email': email, 'class_code_entered': class_short, 'actual_class_name': actual_class_name }) if errors: return jsonify({ 'success': False, 'errors': errors, 'message': f'{len(errors)} row(s) have errors. Please correct and re-upload.' }), 400 is_preview = request.args.get('preview', 'false').lower() == 'true' if is_preview: return jsonify({ 'success': True, 'preview': preview_data, 'total': len(valid_rows) }) students_to_add = [] for student_data in valid_rows: student = PrivateStudent( name=student_data['name'], phone=student_data['phone'], email=student_data['email'], class_code=student_data['class_code'] ) students_to_add.append(student) db.session.add_all(students_to_add) db.session.commit() return jsonify({ 'success': True, 'message': f'Successfully enrolled {len(students_to_add)} student(s)', 'enrolled': len(students_to_add) }) # ---------- NEW FORMAT (convert to old structure, then reuse same logic) ---------- else: # Required columns for new format required_columns = ['First Name', 'Other Names', 'Class'] for col in required_columns: if col not in df.columns: return jsonify({'error': f'Missing column: {col}'}), 400 preview_data = [] errors = [] valid_rows = [] for idx, row in df.iterrows(): row_num = idx + 2 # Convert new format → old format fields first = str(row.get('First Name', '')).strip() if pd.notna(row.get('First Name')) else '' other = str(row.get('Other Names', '')).strip() if pd.notna(row.get('Other Names')) else '' name = (first + ' ' + other).strip() if first else other phone = str(row.get('Phone No.', '')).strip() if pd.notna(row.get('Phone No.')) else '' email = str(row.get('Gmail', '')).strip() if pd.notna(row.get('Gmail')) else '' raw_class = str(row.get('Class', '')).strip() if pd.notna(row.get('Class')) else '' # Parse class to short code (e.g., "J1A" -> "JSS1") class_short = parse_class_code(raw_class) # Now run the exact same validation as original format, # except phone is NOT required (skip the "Phone is required" check) row_errors = [] if not name: row_errors.append('Name is required') # Phone is optional in new format – no check if email and not is_valid_email(email): row_errors.append('Invalid email address') if not raw_class: row_errors.append('Class code is required') elif not class_short: row_errors.append(f'Invalid class code: {raw_class}') elif class_short not in SHORT_CODE_TO_CLASS_CODE: row_errors.append(f'Invalid class code: {class_short}') actual_class_name = '' if class_short and class_short in SHORT_CODE_TO_CLASS_CODE: actual_class_name = get_class_display_name_from_code(SHORT_CODE_TO_CLASS_CODE[class_short]) if row_errors: errors.append({ 'row': row_num, 'name': name or '(empty)', 'phone': phone or '(empty)', 'email': email or '', 'class_code_entered': raw_class, 'errors': row_errors }) else: valid_rows.append({ 'name': name, 'phone': phone if phone else None, 'email': email if email else None, 'class_code': SHORT_CODE_TO_CLASS_CODE[class_short], 'class_short': class_short, 'actual_class_name': actual_class_name }) preview_data.append({ 'name': name, 'phone': phone, 'email': email, 'class_code_entered': raw_class, 'actual_class_name': actual_class_name }) # Same error & preview handling as original if errors: return jsonify({ 'success': False, 'errors': errors, 'message': f'{len(errors)} row(s) have errors. Please correct and re-upload.' }), 400 is_preview = request.args.get('preview', 'false').lower() == 'true' if is_preview: return jsonify({ 'success': True, 'preview': preview_data, 'total': len(valid_rows) }) students_to_add = [] for student_data in valid_rows: student = PrivateStudent( name=student_data['name'], phone=student_data['phone'], email=student_data['email'], class_code=student_data['class_code'] ) students_to_add.append(student) db.session.add_all(students_to_add) db.session.commit() return jsonify({ 'success': True, 'message': f'Successfully enrolled {len(students_to_add)} student(s)', 'enrolled': len(students_to_add) }) except Exception as e: db.session.rollback() return jsonify({'error': f'Failed to process file: {str(e)}'}), 500 @app.route('/api/islamiyya/fees', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier') # Cashier or Director can view def get_islamiyya_fees(): setting = IslamiyyaFeeSetting.query.first() if not setting: # Create default if not exists setting = IslamiyyaFeeSetting(form_fee=0, term_fee=0) db.session.add(setting) db.session.commit() return jsonify({ 'form_fee': setting.form_fee_float, 'term_fee': setting.term_fee_float }) @app.route('/api/islamiyya/edit-fees', methods=['PUT']) @login_required @require_password_changed @requires_roles('private', 'InventoryManager') def edit_islamiyya_fees(): data = request.get_json() form_fee = data.get('form_fee') term_fee = data.get('term_fee') if form_fee is None or term_fee is None: return jsonify({'error': 'Both form_fee and term_fee are required'}), 400 try: form_fee = float(form_fee) term_fee = float(term_fee) if form_fee < 0 or term_fee < 0: return jsonify({'error': 'Fees cannot be negative'}), 400 except (TypeError, ValueError): return jsonify({'error': 'Invalid fee amount'}), 400 setting = IslamiyyaFeeSetting.query.first() if not setting: setting = IslamiyyaFeeSetting() db.session.add(setting) setting.form_fee_float = form_fee setting.term_fee_float = term_fee db.session.commit() return jsonify({'success': True, 'form_fee': form_fee, 'term_fee': term_fee}) @app.route('/api/islamiyya/pay', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'Cashier') def pay_islamiyya_fees(): data = request.get_json() student_id = data.get('student_id') session_id = data.get('session_id') student_type = data.get('student_type') # 'fresh' or 'returning' payment_method = data.get('payment_method') discount_amount = data.get('discount_amount', 0) discount_reason = data.get('discount_reason', '') # Validation if not student_id: return jsonify({'error': 'Student ID is required'}), 400 if not session_id: return jsonify({'error': 'Session ID is required'}), 400 if student_type not in ['fresh', 'returning']: return jsonify({'error': 'Student type must be "fresh" or "returning"'}), 400 if not payment_method: return jsonify({'error': 'Payment method is required'}), 400 if discount_amount < 0: return jsonify({'error': 'Discount cannot be negative'}), 400 # Fetch student student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 # Fetch session session = PrivateSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Invalid or deleted session'}), 400 # Fetch current fee settings setting = IslamiyyaFeeSetting.query.first() if not setting: setting = IslamiyyaFeeSetting(form_fee=0, term_fee=0) db.session.add(setting) db.session.commit() # Calculate charges based on student type form_fee_charged = 0 term_fee_charged = 0 if student_type == 'fresh': form_fee_charged = setting.form_fee_float term_fee_charged = setting.term_fee_float else: # returning term_fee_charged = setting.term_fee_float subtotal = form_fee_charged + term_fee_charged # Validate discount if discount_amount > subtotal: return jsonify({ 'error': f'Discount amount (₦{discount_amount:,.2f}) cannot exceed subtotal (₦{subtotal:,.2f})', 'max_allowed_discount': subtotal }), 400 discount_percentage = (discount_amount / subtotal * 100) if subtotal > 0 else 0 if discount_percentage > 0 and not discount_reason: return jsonify({'error': 'Discount reason required'}), 400 total = subtotal - discount_amount # Generate receipt number receipt_number = generate_receipt_number() # e.g., RCP-XXXXXXX # Create receipt receipt = IslamiyyaPaymentReceipt( receipt_number=receipt_number, student_id=student.id, session_id=session.id, term=session.term, student_type=student_type, form_fee_charged=form_fee_charged, term_fee_charged=term_fee_charged, discount_amount=discount_amount, discount_reason=discount_reason, total_amount=total, payment_method=payment_method, cashier_id=current_user.id ) db.session.add(receipt) db.session.commit() return jsonify({ 'success': True, 'receipt': receipt.to_dict() }) @app.route('/api/islamiyya/receipts', methods=['GET']) @login_required @require_password_changed @requires_roles('private', 'Cashier', 'Analyst') def get_islamiyya_receipts(): # Optional filters: student_id, session_id, start_date, end_date student_id = request.args.get('student_id') session_id = request.args.get('session_id') start_date = request.args.get('start_date') end_date = request.args.get('end_date') query = IslamiyyaPaymentReceipt.query if student_id: query = query.filter_by(student_id=student_id) if session_id: query = query.filter_by(session_id=session_id) if start_date: start = datetime.fromisoformat(start_date) query = query.filter(IslamiyyaPaymentReceipt.created_at >= start) if end_date: end = datetime.fromisoformat(end_date) query = query.filter(IslamiyyaPaymentReceipt.created_at <= end) receipts = query.order_by(IslamiyyaPaymentReceipt.created_at.desc()).all() return jsonify([r.to_dict() for r in receipts]) @app.route('/api/private/classes', methods=['GET']) @login_required @require_password_changed def get_private_classes(): """Return all private school classes with id, name, and code.""" classes = PrivateSchoolClass.query.order_by(PrivateSchoolClass.name).all() return jsonify([{ 'id': c.id, 'name': c.name, 'code': c.code } for c in classes]) @app.route('/api/private/enroll-single', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'EnrollmentOfficer') def enroll_single_private_student(): """Enroll a single private student by providing full class name.""" data = request.get_json() if not data: return jsonify({'error': 'No data provided'}), 400 name = (data.get('name') or '').strip() phone = (data.get('phone') or '').strip() email = (data.get('email') or '').strip() or None class_name = (data.get('class_name') or '').strip() # Validation errors = [] if not name: errors.append('Name is required') if not phone: errors.append('Phone is required') if email and not is_valid_email(email): errors.append('Invalid email address') if not class_name: errors.append('Class is required') if errors: return jsonify({'success': False, 'errors': errors}), 400 # Find the class by full name class_obj = PrivateSchoolClass.query.filter_by(name=class_name).first() if not class_obj: return jsonify({'success': False, 'error': f'Class "{class_name}" not found'}), 400 # Optional: check duplicate phone existing = PrivateStudent.query.filter_by(phone=phone).first() if existing: return jsonify({'success': False, 'error': f'A student with phone {phone} already exists'}), 400 try: new_student = PrivateStudent( name=name, phone=phone, email=email, class_code=class_obj.code ) db.session.add(new_student) db.session.commit() return jsonify({ 'success': True, 'message': f'Successfully enrolled {name}', 'student': new_student.to_dict() }) except Exception as e: db.session.rollback() return jsonify({'error': f'Database error: {str(e)}'}), 500 @app.route('/api/private/students/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('private', 'EnrollmentOfficer') def delete_private_student(student_id): """Soft delete a student (set deleted=True).""" student = PrivateStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 student.deleted = True db.session.commit() return jsonify({'success': True, 'message': 'Student deleted successfully'}), 200 @app.route('/api/english/students/', methods=['DELETE']) @login_required @require_password_changed @requires_roles('english', 'Director') # adjust role as needed def delete_english_student(student_id): """Soft delete an English student (set deleted=True).""" student = EnglishStudent.query.get(student_id) if not student: return jsonify({'error': 'Student not found'}), 404 student.deleted = True db.session.commit() return jsonify({'success': True, 'message': 'Student deleted successfully'}), 200 @app.route('/api/private/students/bulk-transfer', methods=['POST']) @login_required @require_password_changed @requires_roles('private', 'EnrollmentOfficer') def bulk_transfer_private_students(): """Transfer selected students (by ID list) to a new class.""" data = request.get_json() student_ids = data.get('student_ids', []) target_class_code = data.get('target_class_code') if not student_ids: return jsonify({'error': 'No students selected'}), 400 if not target_class_code: return jsonify({'error': 'Target class is required'}), 400 target_class = PrivateSchoolClass.query.filter_by(code=target_class_code).first() if not target_class: return jsonify({'error': 'Invalid target class code'}), 400 # Fetch all selected, non-deleted students students_to_check = PrivateStudent.query.filter( PrivateStudent.id.in_(student_ids), PrivateStudent.deleted == False ).all() if not students_to_check: return jsonify({'error': 'No valid students found'}), 400 # Check: all selected students must have the SAME current class unique_current_classes = set(s.class_code for s in students_to_check) if len(unique_current_classes) > 1: class_names = [PrivateSchoolClass.query.filter_by(code=c).first().name for c in unique_current_classes if c] return jsonify({ 'error': f'Selected students belong to different classes: {", ".join(class_names)}. Please select students from only one class.' }), 400 # Check if any selected student already has the target class already_target = [s.name for s in students_to_check if s.class_code == target_class_code] if already_target: return jsonify({ 'error': f'The following students are already in {target_class.name}: {", ".join(already_target)}' }), 400 # Bulk update updated_count = PrivateStudent.query.filter( PrivateStudent.id.in_(student_ids), PrivateStudent.deleted == False ).update({PrivateStudent.class_code: target_class_code}, synchronize_session=False) db.session.commit() return jsonify({ 'success': True, 'message': f'Successfully transferred {updated_count} student(s) from {students_to_check[0].class_obj.name} to {target_class.name}', 'transferred_count': updated_count }), 200 @app.route('/api/english/gate-passes/bulk-pdf', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def bulk_english_gate_passes_pdf(): data = request.get_json() session_id = data.get('session_id') student_ids = data.get('student_ids') cols = 5 rows = 2 passes_per_page = cols * rows if not session_id: return jsonify({'error': 'session_id required'}), 400 if not student_ids or not isinstance(student_ids, list): return jsonify({'error': 'student_ids list required'}), 400 session = EnglishSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Session not found'}), 404 receipts = EnglishRegistrationReceipt.query.filter( EnglishRegistrationReceipt.session_id == session_id, EnglishRegistrationReceipt.pass_number.isnot(None), EnglishRegistrationReceipt.receipt_type.in_(['enrollment', 'class_change']) ).order_by(EnglishRegistrationReceipt.pass_number).all() student_map = {} for r in receipts: if r.student_id not in student_map: student_map[r.student_id] = r student_ids_set = set(student_ids) student_map = {sid: r for sid, r in student_map.items() if sid in student_ids_set} if not student_map: return jsonify({'error': 'No matching students found'}), 404 passes = [] for r in student_map.values(): student = r.student class_name = '' if student.class_code: english_class = EnglishClass.query.filter_by(code=student.class_code).first() class_name = english_class.name if english_class else student.class_code time_text = session.time_info.time_text if session.time_info else 'Not Set' starting = session.period.starting.strftime('%d %b %Y') if session.period else 'Not Set' ending = session.period.ending.strftime('%d %b %Y') if session.period else 'Not Set' suffix = student.class_code time_suffix = time_text if time_text else 'T' formatted_receipt_no = f"{r.pass_number:04d}{suffix}{time_suffix}" level_display = student.level_name if student.level_name else '-' color_index = session.pass_color student_photo_base64 = None photo_path = os.path.join('photos', f"{student.id}.jpg") if os.path.exists(photo_path): with open(photo_path, 'rb') as f: student_photo_base64 = base64.b64encode(f.read()).decode('utf-8') img_base64 = process_college_pass_english( receipt_no=formatted_receipt_no, name=student.name, class_name=class_name, level_name=level_display, time=time_text, phone=student.phone, starting=starting, ending=ending, color_index=color_index, student_photo_base64=student_photo_base64 ) img_bytes = base64.b64decode(img_base64) passes.append({ 'student_name': student.name, 'pass_no': r.pass_number, 'image_bytes': img_bytes }) # Create a temporary file (no auto-delete, we'll delete after sending) temp_file = tempfile.NamedTemporaryFile(delete=False, suffix='.pdf') temp_file.close() temp_path = temp_file.name c = canvas.Canvas(temp_path, pagesize=landscape(A4)) width, height = landscape(A4) margin = 15 col_gap = 10 row_gap = 10 usable_width = width - 2 * margin usable_height = height - 2 * margin total_col_gaps = (cols - 1) * col_gap cell_width = (usable_width - total_col_gaps) / cols total_row_gaps = (rows - 1) * row_gap cell_height = (usable_height - total_row_gaps) / rows positions = [] for row in range(rows): y = height - margin - (row + 1) * cell_height - row * row_gap for col in range(cols): x = margin + col * (cell_width + col_gap) positions.append((x, y)) for idx, p in enumerate(passes): pos_idx = idx % passes_per_page if pos_idx == 0 and idx != 0: c.showPage() x, y = positions[pos_idx] img = ImageReader(BytesIO(p['image_bytes'])) c.drawImage(img, x, y, cell_width, cell_height, preserveAspectRatio=False) c.save() @after_this_request def cleanup(response): try: os.unlink(temp_path) except Exception: pass return response return send_file( temp_path, as_attachment=True, download_name=f'gate_passes_session_{session.id}.pdf', mimetype='application/pdf' ) @app.route('/api/english/gate-passes/prep-batch', methods=['POST']) @login_required @require_password_changed @requires_roles('english', 'Cashier') def prep_batch_data(): data = request.get_json() session_id = data.get('session_id') student_ids = data.get('student_ids') # The frontend will only send max 10 at a time if not session_id or not student_ids: return jsonify({'error': 'Missing parameters'}), 400 session = EnglishSession.query.filter_by(id=session_id, deleted=False).first() if not session: return jsonify({'error': 'Session not found'}), 404 receipts = EnglishRegistrationReceipt.query.filter( EnglishRegistrationReceipt.session_id == session_id, EnglishRegistrationReceipt.student_id.in_(student_ids), EnglishRegistrationReceipt.pass_number.isnot(None), EnglishRegistrationReceipt.receipt_type.in_(['enrollment', 'class_change']) ).all() batch_payload = { "time_text": session.time_info.time_text if session.time_info else 'Not Set', "starting": session.period.starting.strftime('%d %b %Y') if session.period else 'Not Set', "ending": session.period.ending.strftime('%d %b %Y') if session.period else 'Not Set', "pass_color": session.pass_color, "students": [] } for r in receipts: student = r.student class_name = student.class_code if student.class_code: english_class = EnglishClass.query.filter_by(code=student.class_code).first() class_name = english_class.name if english_class else student.class_code # Read the file from cPanel storage and convert to base64 string photo_base64 = None photo_path = os.path.join('photos', f"{student.id}.jpg") if os.path.exists(photo_path): with open(photo_path, 'rb') as f: photo_base64 = base64.b64encode(f.read()).decode('utf-8') batch_payload["students"].append({ "name": student.name, "phone": student.phone, "class_code": student.class_code, "class_name": class_name, "level_name": student.level_name if student.level_name else '-', "pass_number": r.pass_number, "student_photo_base64": photo_base64 }) return jsonify(batch_payload) def save_student_photo(student_id, photo_base64): """ Save base64 photo to photos/{student_id} Returns the saved file path or raises an exception. """ if not photo_base64: return None # Extract base64 data (remove data URL prefix if present) match = re.match(r'data:image/(?P\w+);base64,(?P.+)', photo_base64) if match: image_data = base64.b64decode(match.group('data')) else: image_data = base64.b64decode(photo_base64) # Create photos directory if not exists photos_dir = 'photos' os.makedirs(photos_dir, exist_ok=True) # Save as photos/{student_id} (no extension) filepath = os.path.join(photos_dir, str(student_id)) with open(filepath, 'wb') as f: f.write(image_data) return filepath @app.route('/api/photos/') def serve_photo(filename): if '..' in filename or filename.startswith('/'): return "Invalid filename", 400 photo_dir = 'photos' safe_path = os.path.join(photo_dir, filename) if not os.path.exists(safe_path): return "Not found", 404 return send_file(safe_path, mimetype='image/jpeg') if __name__ == '__main__': with app.app_context(): initialize_database() app.run(debug=True, port=5006)