#collage management and finance from flask import Flask, render_template, request, jsonify import spacy from collections import Counter import sqlite3 import pandas as pd import plotly.express as px import plotly.graph_objects as go from datetime import datetime import json app = Flask(__name__) # Load spaCy NLP model nlp = spacy.load("en_core_web_sm") # Enriched keywords for each criterion CRITERIA_KEYWORDS = { "6.1.1": [ "governance", "vision", "mission", "leadership", "stakeholders", "strategic leadership", "inclusive governance", "collaboration", "multi-stakeholder", "sustainable development", "diversity and inclusion", "ethics in leadership", "policy frameworks", "visionary leadership", "digital governance","governance", "vision", "mission", "leadership", "stakeholders", "participation", "NEP", "perspective plan", "decentralization", "policies", "strategies", "principal", "department", "interaction", "meetings", "stakeholder engagement", "decision-making", "short-term", "long-term" ], "6.2.1": [ "strategy", "plan", "development", "deployment", "implementation", "strategic alignment", "operational excellence", "data visualization", "analytics platforms", "goal setting", "milestone tracking", "resource allocation", "multi-skilling", "AI in planning", "predictive analytics", "stakeholder buy-in", "resource planning tools", "institutional benchmarking", "strategy", "plan", "development", "deployment", "implementation", "research", "laboratories", "interdisciplinary", "training", "placement", "projects", "add-on courses", "ISO", "NAAC", "NBA", "technical training", "industry collaboration", "vocational training", "internship", "holistic development" ], "6.3.1": [ "faculty", "welfare", "support", "career", "development", "microlearning", "certification programs", "collaborative learning", "AI in training", "EdTech adoption", "e-learning platforms", "job satisfaction", "mental health programs", "leadership training", "skill mapping", "career ladders", "mentoring programs", "knowledge-sharing","faculty", "welfare", "support", "career", "development", "performance appraisal", "insurance", "concession", "FDP", "refresher", "orientation", "seminars", "workshops", "conferences", "duty leave", "e-resources", "higher studies", "research", "training", "NPTEL", "non-teaching staff", "technical knowledge" ], "6.4.1": [ "audit", "finance", "resources", "utilization", "budget", "blockchain in finance", "fintech tools", "crowdfunding", "public-private partnerships", "grant writing", "financial transparency", "digital payments", "resource optimization", "budget forecasting", "investment strategies", "funding diversification", "financial KPIs","audit", "finance", "resources", "utilization", "budget", "internal audit", "external audit", "accounts", "financial approval", "chartered accountant", "budget proposals", "departmental needs", "infrastructure", "statutory compliance", "revenue", "financial status", "expenditure tracking", "annual audit" ], "6.5.1": [ "quality", "assurance", "IQAC", "improvement", "feedback", "six sigma", "total quality management (TQM)", "continuous improvement", "peer evaluation", "performance metrics", "quality benchmarking", "international rankings", "accreditation standards", "ISO compliance", "data-informed insights", "gap analysis", "NABH standards", "best practices", "feedback loops", "actionable feedback","audit", "finance", "resources", "utilization", "budget", "internal audit", "external audit", "accounts", "financial approval", "chartered accountant", "budget proposals", "departmental needs", "infrastructure", "statutory compliance", "revenue", "financial status", "expenditure tracking", "annual audit" ], "general": [ "digital transformation", "cloud computing", "AI integration", "machine learning", "data security", "cybersecurity", "remote collaboration", "hybrid work models", "virtual engagement", "sustainability initiatives", "UN SDGs", "global partnerships", "virtual reality", "augmented reality", "diversity and equity""quality", "assurance", "IQAC", "improvement", "feedback", "teaching-learning", "methodology", "recruitment", "laboratories", "certificate courses", "green audit", "environment audit", "newsletter", "centralized software", "awards", "project competition", "energy audit", "NIRF", "accreditation", "collaboration", "rankings" ] } # Questionnaire weights QUESTION_WEIGHTS = { "6.1.1": [ {"question": "Does your institution regularly involve stakeholders in planning discussions?", "weight": 10}, {"question": "Are there leadership training programs for staff?", "weight": 5}, {"question": "Is there a dedicated team to review the implementation of the institutional mission?", "weight": 5}, ], "6.2.1": [ {"question": "Do you track progress against strategic goals with measurable metrics?", "weight": 10}, {"question": "Are there annual reviews of institutional plans and their outcomes?", "weight": 10}, {"question": "Do you use digital tools for planning and resource allocation?", "weight": 5}, ], "6.3.1": [ {"question": "Does your institution provide financial support for external training programs?", "weight": 10}, {"question": "Are there structured mentoring programs for faculty?", "weight": 5}, {"question": "Does the institution have wellness programs for mental and physical health?", "weight": 5}, ], "6.4.1": [ {"question": "Is there an internal audit system for monitoring financial processes?", "weight": 10}, {"question": "Does your institution seek funding from non-governmental organizations?", "weight": 5}, {"question": "Are financial resources allocated transparently across departments?", "weight": 10}, ], "6.5.1": [ {"question": "Are there regular surveys to collect feedback from students and staff?", "weight": 10}, {"question": "Does the institution participate in national or international rankings?", "weight": 10}, {"question": "Are there collaborative initiatives with other institutions for quality improvement?", "weight": 5}, ] } # Analyze text-based inputs def analyze_text(data): results = {} total_score = 0 max_score = len(CRITERIA_KEYWORDS) * 100 for criterion, text in data.items(): doc = nlp(text) words = [token.text.lower() for token in doc if token.is_alpha] word_count = Counter(words) # Match keywords relevant_keywords = CRITERIA_KEYWORDS.get(criterion, []) matched_keywords = {kw: word_count[kw] for kw in relevant_keywords if kw in word_count} # Calculate score score = (len(matched_keywords) / len(relevant_keywords)) * 100 if relevant_keywords else 0 total_score += score results[criterion] = { "matched_keywords": matched_keywords, "score": score, "text": text, } return results, total_score, max_score # Analyze questionnaire responses def analyze_questions(responses): question_scores = {} for criterion, questions in QUESTION_WEIGHTS.items(): score = sum(q["weight"] for q, ans in zip(questions, responses[criterion]) if ans == "yes") question_scores[criterion] = score return question_scores # Database initialization def init_db(): conn = sqlite3.connect('college_management.db') c = conn.cursor() # Create tables for different sections c.execute('''CREATE TABLE IF NOT EXISTS utilities (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, electricity_units REAL, electricity_cost REAL, water_usage REAL, water_cost REAL)''') c.execute('''CREATE TABLE IF NOT EXISTS finances (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, total_revenue REAL, total_expenditure REAL, profit_loss REAL)''') c.execute('''CREATE TABLE IF NOT EXISTS staff (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, teaching_staff_count INTEGER, teaching_staff_salary REAL, non_teaching_staff_count INTEGER, non_teaching_staff_salary REAL)''') c.execute('''CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, total_students INTEGER, fees_collected REAL, fees_pending REAL, scholarship_amount REAL)''') conn.commit() conn.close() # Save form data to database def save_to_db(form_data, table_name): conn = sqlite3.connect('college_management.db') c = conn.cursor() current_date = datetime.now().strftime('%Y-%m-%d') if table_name == 'utilities': c.execute('''INSERT INTO utilities (date, electricity_units, electricity_cost, water_usage, water_cost) VALUES (?, ?, ?, ?, ?)''', (current_date, form_data.get('totalUnits'), form_data.get('totalAmount'), form_data.get('waterConsumption'), form_data.get('waterAmount'))) elif table_name == 'finances': c.execute('''INSERT INTO finances (date, total_revenue, total_expenditure, profit_loss) VALUES (?, ?, ?, ?)''', (current_date, form_data.get('totalRevenue'), form_data.get('totalExpenditure'), form_data.get('profitLoss'))) elif table_name == 'staff': c.execute('''INSERT INTO staff (date, teaching_staff_count, teaching_staff_salary, non_teaching_staff_count, non_teaching_staff_salary) VALUES (?, ?, ?, ?, ?)''', (current_date, form_data.get('teachingStaffCount'), form_data.get('teachingTotalSalary'), form_data.get('nonTeachingStaffCount'), form_data.get('nonTeachingTotalSalary'))) elif table_name == 'students': c.execute('''INSERT INTO students (date, total_students, fees_collected, fees_pending, scholarship_amount) VALUES (?, ?, ?, ?, ?)''', (current_date, form_data.get('totalStudents'), form_data.get('totalFeeCollected'), form_data.get('totalFeeDue'), form_data.get('totalScholarshipAmount'))) conn.commit() conn.close() # Generate insights and visualizations def generate_insights(): conn = sqlite3.connect('college_management.db') # Read data into pandas DataFrames utilities_df = pd.read_sql_query("SELECT * FROM utilities", conn) finances_df = pd.read_sql_query("SELECT * FROM finances", conn) staff_df = pd.read_sql_query("SELECT * FROM staff", conn) students_df = pd.read_sql_query("SELECT * FROM students", conn) insights = {} # Utility Insights if not utilities_df.empty: utilities_df['date'] = pd.to_datetime(utilities_df['date']) # Electricity usage trend fig_electricity = px.line(utilities_df, x='date', y='electricity_units', title='Electricity Usage Trend') insights['electricity_trend'] = fig_electricity.to_json() # Water usage trend fig_water = px.line(utilities_df, x='date', y='water_usage', title='Water Usage Trend') insights['water_trend'] = fig_water.to_json() # Financial Insights if not finances_df.empty: finances_df['date'] = pd.to_datetime(finances_df['date']) # Revenue vs Expenditure fig_finances = go.Figure() fig_finances.add_trace(go.Bar(x=finances_df['date'], y=finances_df['total_revenue'], name='Revenue')) fig_finances.add_trace(go.Bar(x=finances_df['date'], y=finances_df['total_expenditure'], name='Expenditure')) fig_finances.update_layout(title='Revenue vs Expenditure', barmode='group') insights['finance_comparison'] = fig_finances.to_json() # Staff Insights if not staff_df.empty: staff_df['date'] = pd.to_datetime(staff_df['date']) # Staff distribution pie chart latest_staff = staff_df.iloc[-1] fig_staff = px.pie(values=[latest_staff['teaching_staff_count'], latest_staff['non_teaching_staff_count']], names=['Teaching Staff', 'Non-Teaching Staff'], title='Staff Distribution') insights['staff_distribution'] = fig_staff.to_json() # Student Insights if not students_df.empty: students_df['date'] = pd.to_datetime(students_df['date']) # Fee collection status latest_students = students_df.iloc[-1] fig_fees = px.pie(values=[latest_students['fees_collected'], latest_students['fees_pending']], names=['Collected', 'Pending'], title='Fee Collection Status') insights['fee_status'] = fig_fees.to_json() conn.close() return insights # Add new function to generate comprehensive insights def generate_comprehensive_insights(): conn = sqlite3.connect('college_management.db') insights = { 'summary': {}, 'trends': {}, 'recommendations': [], 'charts': {}, 'key_metrics': {} } try: # Get all data utilities_df = pd.read_sql_query("SELECT * FROM utilities ORDER BY date", conn) finances_df = pd.read_sql_query("SELECT * FROM finances ORDER BY date", conn) staff_df = pd.read_sql_query("SELECT * FROM staff ORDER BY date", conn) students_df = pd.read_sql_query("SELECT * FROM students ORDER BY date", conn) # Financial Health Analysis if not finances_df.empty: latest_finance = finances_df.iloc[-1] revenue = latest_finance['total_revenue'] expenditure = latest_finance['total_expenditure'] profit_margin = ((revenue - expenditure) / revenue * 100) if revenue > 0 else 0 insights['key_metrics']['financial_health'] = { 'revenue': revenue, 'expenditure': expenditure, 'profit_margin': profit_margin, 'status': 'Good' if profit_margin > 15 else 'Fair' if profit_margin > 0 else 'Needs Attention' } # Revenue trend chart fig_revenue = px.line(finances_df, x='date', y=['total_revenue', 'total_expenditure'], title='Financial Trends Over Time') insights['charts']['financial_trend'] = fig_revenue.to_json() # Utility Efficiency Analysis if not utilities_df.empty: current_month = utilities_df.iloc[-1] prev_month = utilities_df.iloc[-2] if len(utilities_df) > 1 else current_month electricity_change = ((current_month['electricity_units'] - prev_month['electricity_units']) / prev_month['electricity_units'] * 100) if prev_month['electricity_units'] > 0 else 0 insights['key_metrics']['utility_efficiency'] = { 'current_electricity': current_month['electricity_units'], 'electricity_change': electricity_change, 'current_water': current_month['water_usage'], 'status': 'Efficient' if electricity_change < 0 else 'Normal' if electricity_change < 10 else 'High Usage' } # Staff Analysis if not staff_df.empty: latest_staff = staff_df.iloc[-1] total_staff = (latest_staff['teaching_staff_count'] + latest_staff['non_teaching_staff_count']) insights['key_metrics']['staff_metrics'] = { 'total_staff': total_staff, 'teaching_ratio': latest_staff['teaching_staff_count'] / total_staff * 100, 'avg_salary': (latest_staff['teaching_staff_salary'] + latest_staff['non_teaching_staff_salary']) / total_staff } # Student Analysis if not students_df.empty: latest_students = students_df.iloc[-1] fee_collection_rate = (latest_students['fees_collected'] / (latest_students['fees_collected'] + latest_students['fees_pending']) * 100) insights['key_metrics']['student_metrics'] = { 'total_students': latest_students['total_students'], 'fee_collection_rate': fee_collection_rate, 'scholarship_per_student': latest_students['scholarship_amount'] / latest_students['total_students'] } # Generate Recommendations recommendations = [] # Financial recommendations if 'financial_health' in insights['key_metrics']: if insights['key_metrics']['financial_health']['profit_margin'] < 10: recommendations.append("Consider cost optimization measures to improve profit margin") # Utility recommendations if 'utility_efficiency' in insights['key_metrics']: if insights['key_metrics']['utility_efficiency']['electricity_change'] > 10: recommendations.append("Implement energy conservation measures to reduce electricity consumption") # Staff recommendations if 'staff_metrics' in insights['key_metrics']: if insights['key_metrics']['staff_metrics']['teaching_ratio'] < 60: recommendations.append("Consider optimizing teaching to non-teaching staff ratio") # Student recommendations if 'student_metrics' in insights['key_metrics']: if insights['key_metrics']['student_metrics']['fee_collection_rate'] < 80: recommendations.append("Improve fee collection process and follow-up mechanisms") insights['recommendations'] = recommendations except Exception as e: insights['error'] = str(e) finally: conn.close() return insights # Add new route for comprehensive insights page @app.route("/comprehensive_insights") def comprehensive_insights(): insights = generate_comprehensive_insights() return render_template("insights.html", insights=insights) # Modify existing save_form route to redirect to insights @app.route("/save_form", methods=["POST"]) def save_form(): form_data = request.json form_type = form_data.get('form_type') try: save_to_db(form_data, form_type) return jsonify({ "status": "success", "message": "Data saved successfully", "redirect": "/comprehensive_insights" # Add redirect URL }) except Exception as e: return jsonify({"status": "error", "message": str(e)}) @app.route("/") def home(): return render_template("index.html") @app.route("/get_insights") def get_insights(): try: insights = generate_insights() return jsonify({"status": "success", "insights": insights}) except Exception as e: return jsonify({"status": "error", "message": str(e)}) if __name__ == "__main__": init_db() app.run(debug=True)