Index: app.py =================================================================== --- app.py (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ app.py (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -1,6 +1,7 @@ -from flask import Flask, render_template, jsonify, request, session, redirect +from flask import Flask, render_template, jsonify, request, session, redirect, url_for, flash +from psycopg2.errors import ForeignKeyViolation +from functools import wraps from utils.database_manager import DatabaseManager from utils.auth_manager import AuthManager -import hashlib app = Flask(__name__) @@ -8,4 +9,38 @@ app.config['JSON_AS_ASCII'] = False + +# ------------------------------ +# Helpers / Decorators +# ------------------------------ +def require_login(role=None): + def decorator(f): + @wraps(f) + def wrapper(*args, **kwargs): + if 'user_id' not in session: + return redirect(url_for('login')) + if role and session.get('role') != role: + return redirect(url_for('login')) + return f(*args, **kwargs) + return wrapper + return decorator + + +def _render_generic(title, rows): + headers = list(rows[0].keys()) if rows else [] + return render_template('reports/generic_report.html', title=title, headers=headers, rows=rows) + + +def _enrich_with_equipment(exp_rows): + enriched = [] + for row in (exp_rows or []): + exp = dict(row) + exp['equipment'] = DatabaseManager.get_experiment_equipment(exp['experiment_id']) or [] + enriched.append(exp) + return enriched + + +# ------------------------------ +# Home / Auth +# ------------------------------ @app.route('/') def index(): @@ -22,20 +57,14 @@ ''' + @app.route('/test-db') def test_db(): if DatabaseManager.test_connection(): - return jsonify({ - 'status': 'success', - 'message': 'Успешно поврзување со факултетската база!' - }) - else: - return jsonify({ - 'status': 'error', - 'message': 'Грешка при поврзување' - }) + return jsonify({'status': 'success', 'message': 'Успешно поврзување со факултетската база!'}) + return jsonify({'status': 'error', 'message': 'Грешка при поврзување'}) + @app.route('/users') def users(): - """Прикажи сите корисници""" users = DatabaseManager.get_all_users() if users: @@ -48,58 +77,10 @@ return jsonify({'status': 'error', 'message': 'Нема корисници'}) -@app.route('/elements') -def elements(): - if 'user_id' not in session: - return redirect('/login') - - elements_data = DatabaseManager.get_all_elements() - - if elements_data: - return render_template('elements_list.html', elements=elements_data, user_role=session['role']) - else: - return render_template('elements_list.html', elements=[], error='Нема елементи во базата') - -@app.route('/equipment') -def equipment(): - """Прикажи лабораториска опрема""" - if 'user_id' not in session: - return redirect('/login') - - equipment_data = DatabaseManager.get_all_equipment() - if equipment_data: - return render_template('equipment_list.html', equipment=equipment_data, user_role=session['role']) - else: - return render_template('equipment_list.html', equipment=[], error='Нема опрема во базата') - -@app.route('/reports/equipment-usage') -def equipment_usage_report(): - """SQL Извештаи за опрема""" - usage_report = DatabaseManager.get_equipment_usage_report() - if usage_report: - return jsonify({ - 'status': 'success', - 'report_name': 'Извештај за користење на лабораториска опрема', - 'sql_query': ''' - SELECT le.equipment_name, COUNT(ele.experiment_id) AS usage_count - FROM ExperimentLabEquipment ele - RIGHT JOIN LabEquipment le ON ele.equipment_id = le.equipment_id - GROUP BY le.equipment_name - ORDER BY usage_count DESC - ''', - 'count': len(usage_report), - 'data': usage_report - }) - else: - return jsonify({ - 'status': 'error', - 'message': 'Грешка при генерирање на извештај' - }) @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'POST': - email = request.form['email'] + email = request.form['email'].strip() password = request.form['password'] - user = DatabaseManager.authenticate_user(email, password) if user and AuthManager.verify_password(password, user['password']): @@ -107,14 +88,12 @@ session['user_name'] = user['user_name'] session['role'] = user['role'] - return redirect('/dashboard') - else: - return render_template('login.html', error='Погрешен email или лозинка') - + return redirect(url_for('dashboard')) + return render_template('login.html', error='Погрешен email или лозинка') return render_template('login.html') + @app.route('/register', methods=['GET', 'POST']) def register(): teachers = DatabaseManager.get_all_teachers() - if request.method == 'POST': name = request.form['name'] @@ -124,52 +103,101 @@ role = request.form['role'] teacher_id = request.form.get('teacher_id') if role == 'student' else None - password_hash = AuthManager.hash_password(password) - user_id = DatabaseManager.register_user(name, surname, email, password_hash, role, teacher_id) if user_id: - return redirect('/login') - else: - return render_template('register.html', error='Грешка при регистрација', teachers=teachers) - + return redirect(url_for('login')) + return render_template('register.html', error='Грешка при регистрација', teachers=teachers) return render_template('register.html', teachers=teachers) + @app.route('/logout') def logout(): session.clear() - return redirect('/') - + return redirect(url_for('index')) + + +# ------------------------------ +# Dashboard +# ------------------------------ @app.route('/dashboard') +@require_login() def dashboard(): - """Dashboard со статистики според улога""" - if 'user_id' not in session: - return redirect('/login') - if session['role'] == 'teacher': - stats = None try: stats = DatabaseManager.get_teacher_dashboard_statistics(session['user_id']) - except: + except Exception: stats = {'student_count': 0, 'reaction_count': 0, 'experiment_count': 0, 'activity_count': 0} - - return render_template('dashboard_teacher.html', - user_name=session['user_name'], - stats=stats) + return render_template('dashboard_teacher.html', user_name=session['user_name'], stats=stats) else: - stats = None try: stats = DatabaseManager.get_student_statistics(session['user_id']) - except: + except Exception: stats = {'experiment_count': 0, 'element_count': 0, 'equipment_count': 0, 'reaction_count': 0} - - return render_template('dashboard_student.html', - user_name=session['user_name'], - stats=stats) + return render_template('dashboard_student.html', user_name=session['user_name'], stats=stats) + + +@app.route('/api/dashboard-stats') +@require_login() +def dashboard_stats(): + try: + if session['role'] == 'student': + stats = DatabaseManager.get_student_statistics(session['user_id']) or {} + stats = { + 'student_count': 0, + 'reaction_count': int(stats.get('reaction_count', 0)), + 'experiment_count': int(stats.get('experiment_count', 0)), + 'activity_count': 0 + } + else: + stats = DatabaseManager.get_teacher_dashboard_statistics(session['user_id']) or {} + stats = { + 'student_count': int(stats.get('student_count', 0)), + 'reaction_count': int(stats.get('reaction_count', 0)), + 'experiment_count': int(stats.get('experiment_count', 0)), + 'activity_count': int(stats.get('activity_count', 0)) + } + return jsonify(stats), 200 + except Exception as e: + app.logger.exception("dashboard_stats failed") + return jsonify({'error': f'Серверска грешка: {str(e)}'}), 500 + + +@app.route('/api/debug-dashboard') +@require_login() +def api_debug_dashboard(): + uid = session['user_id'] + role = session.get('role') + if role == 'teacher': + stats = DatabaseManager.get_teacher_dashboard_statistics(uid) + else: + stats = DatabaseManager.get_student_statistics(uid) + return jsonify({'session_user_id': uid, 'session_role': role, 'stats': stats}), 200 + + +# ------------------------------ +# Elements +# ------------------------------ +@app.route('/elements') +@require_login() +def elements(): + elements_data = DatabaseManager.get_all_elements() + if elements_data: + return render_template('elements_list.html', elements=elements_data, user_role=session['role']) + return render_template('elements_list.html', elements=[], error='Нема елементи во базата') + + +@app.route('/elements/') +@require_login() +def element_detail(element_id): + DatabaseManager.track_element_view(session['user_id'], element_id) + element = DatabaseManager.get_element_by_id(element_id) + if element: + return render_template('element_detail.html', element=element, user_role=session['role']) + return redirect(url_for('elements')) + @app.route('/elements/add', methods=['GET', 'POST']) +@require_login('teacher') def add_element(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - if request.method == 'POST': symbol = request.form['symbol'] @@ -182,171 +210,20 @@ description = request.form['description'] teacher_id = session['user_id'] - - element_id = DatabaseManager.add_element(symbol, name, atomic_number, atomic_weight, - melting_point, boiling_point, hazard_type, - description, teacher_id) - + element_id = DatabaseManager.add_element( + symbol, name, atomic_number, atomic_weight, + melting_point, boiling_point, hazard_type, description, teacher_id + ) if element_id: - return redirect('/dashboard') - else: - return render_template('add_element.html', error='Грешка при додавање на елементот') - + return redirect(url_for('dashboard')) + return render_template('add_element.html', error='Грешка при додавање на елементот') return render_template('add_element.html') -@app.route('/reports') -def reports_menu(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - return render_template('reports/menu.html') - -@app.route('/reports/low_activity_students') -def low_activity_students(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - teacher_id = session['user_id'] - students = DatabaseManager.get_students_with_few_experiments(teacher_id, 3) - return render_template('reports/low_activity_students.html', students=students) - -@app.route('/reports/teacher_statistics') -def teacher_statistics(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - stats = DatabaseManager.get_teacher_statistics() - return render_template('reports/teacher_statistics.html', statistics=stats) - -@app.route('/reports/inactive_students') -def inactive_students(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - teacher_id = session['user_id'] - students = DatabaseManager.get_students_without_experiments(teacher_id) - return render_template('reports/inactive_students.html', students=students) - -@app.route('/reports/detailed_experiments') -def detailed_experiments(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - teacher_id = session['user_id'] - experiments = DatabaseManager.get_students_experiments_detailed(teacher_id) - return render_template('reports/detailed_experiments.html', experiments=experiments) - -@app.route('/elements/') -def element_detail(element_id): - if 'user_id' not in session: - return redirect('/login') - - DatabaseManager.track_element_view(session['user_id'], element_id) - - element = DatabaseManager.get_element_by_id(element_id) - if element: - return render_template('element_detail.html', element=element, user_role=session['role']) - else: - return redirect('/elements') - -@app.route('/my_students') -def my_students(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - teacher_id = session['user_id'] - students_activity = DatabaseManager.get_my_students_activity(teacher_id) - - return render_template('my_students.html', - students=students_activity, - teacher_name=session['user_name']) - -@app.route('/experiments') -def experiments(): - if 'user_id' not in session: - return redirect('/login') - - experiments_data = DatabaseManager.get_all_experiments() - return render_template('experiments_list.html', experiments=experiments_data, user_role=session['role']) - -@app.route('/experiments/') -def experiment_detail(experiment_id): - if 'user_id' not in session: - return redirect('/login') - - experiments = DatabaseManager.get_all_experiments() - experiment = None - for exp in experiments: - if exp['experiment_id'] == experiment_id: - experiment = exp - break - - if not experiment: - return redirect('/experiments') - - equipment = DatabaseManager.get_experiment_equipment(experiment_id) - - return render_template('experiment_detail.html', - experiment=experiment, - equipment=equipment, - user_role=session['role']) - -@app.route('/reports/element_views') -def element_views_report(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - views_data = DatabaseManager.get_element_views_report() - return render_template('reports/element_views.html', views=views_data) - -@app.route('/equipment/add', methods=['GET', 'POST']) -def add_equipment(): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - if request.method == 'POST': - name = request.form['name'] - equipment_type = request.form['type'] - description = request.form['description'] - safety_info = request.form['safety_info'] - teacher_id = session['user_id'] - - equipment_id = DatabaseManager.add_lab_equipment(name, equipment_type, description, - safety_info, teacher_id) - - if equipment_id: - return redirect('/dashboard') - else: - return render_template('add_equipment.html', error='Грешка при додавање на опремата') - - return render_template('add_equipment.html') - -@app.route('/equipment/') -def equipment_detail(equipment_id): - if 'user_id' not in session: - return redirect('/login') - - DatabaseManager.track_equipment_view(session['user_id'], equipment_id) - - equipment_data = DatabaseManager.get_all_equipment() - equipment = None - for item in equipment_data: - if item['equipment_id'] == equipment_id: - equipment = item - break - - if equipment: - return render_template('equipment_detail.html', equipment=equipment, user_role=session['role']) - else: - return redirect('/equipment') @app.route('/elements//edit', methods=['GET', 'POST']) +@require_login('teacher') def edit_element(element_id): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - element = DatabaseManager.get_element_by_id(element_id) if not element: - return redirect('/elements') - + return redirect(url_for('elements')) if request.method == 'POST': symbol = request.form['symbol'] @@ -358,22 +235,28 @@ hazard_type = request.form['hazard_type'] description = request.form['description'] - - if DatabaseManager.update_element(element_id, symbol, name, atomic_number, atomic_weight, - melting_point, boiling_point, hazard_type, description): - return redirect('/elements') - else: - return render_template('edit_element.html', element=element, error='Грешка при ажурирање') - + if DatabaseManager.update_element( + element_id, symbol, name, atomic_number, atomic_weight, + melting_point, boiling_point, hazard_type, description + ): + return redirect(url_for('elements')) + return render_template('edit_element.html', element=element, error='Грешка при ажурирање') return render_template('edit_element.html', element=element) -@app.route('/equipment//edit', methods=['GET', 'POST']) -def edit_equipment(equipment_id): - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - equipment = DatabaseManager.get_equipment_by_id(equipment_id) - if not equipment: - return redirect('/equipment') - + +# ------------------------------ +# Equipment +# ------------------------------ +@app.route('/equipment') +@require_login() +def equipment(): + equipment_data = DatabaseManager.get_all_equipment() + if equipment_data: + return render_template('equipment_list.html', equipment=equipment_data, user_role=session['role']) + return render_template('equipment_list.html', equipment=[], error='Нема опрема во базата') + + +@app.route('/equipment/add', methods=['GET', 'POST']) +@require_login('teacher') +def add_equipment(): if request.method == 'POST': name = request.form['name'] @@ -381,29 +264,118 @@ description = request.form['description'] safety_info = request.form['safety_info'] - + teacher_id = session['user_id'] + equipment_id = DatabaseManager.add_lab_equipment(name, equipment_type, description, safety_info, teacher_id) + if equipment_id: + return redirect(url_for('dashboard')) + return render_template('add_equipment.html', error='Грешка при додавање на опремата') + return render_template('add_equipment.html') + + +@app.route('/equipment/') +@require_login() +def equipment_detail(equipment_id): + DatabaseManager.track_equipment_view(session['user_id'], equipment_id) + equipment_data = DatabaseManager.get_all_equipment() + equipment = None + for item in (equipment_data or []): + if item['equipment_id'] == equipment_id: + equipment = item + break + if equipment: + return render_template('equipment_detail.html', equipment=equipment, user_role=session['role']) + return redirect(url_for('equipment')) + + +@app.route('/equipment//edit', methods=['GET', 'POST']) +@require_login('teacher') +def edit_equipment(equipment_id): + equipment = DatabaseManager.get_equipment_by_id(equipment_id) + if not equipment: + return redirect(url_for('equipment')) + if request.method == 'POST': + name = request.form['name'] + equipment_type = request.form['type'] + description = request.form['description'] + safety_info = request.form['safety_info'] if DatabaseManager.update_equipment(equipment_id, name, equipment_type, description, safety_info): - return redirect('/equipment') - else: - return render_template('edit_equipment.html', equipment=equipment, error='Грешка при ажурирање') - + return redirect(url_for('equipment')) + return render_template('edit_equipment.html', equipment=equipment, error='Грешка при ажурирање') return render_template('edit_equipment.html', equipment=equipment) + +# ------------------------------ +# Reactions +# ------------------------------ @app.route('/reactions') +@require_login() def reactions(): - """Прикажи сите реакции""" - if 'user_id' not in session: - return redirect('/login') - reactions_data = DatabaseManager.get_all_reactions() return render_template('reactions_list.html', reactions=reactions_data, user_role=session['role']) + @app.route('/reactions/add', methods=['GET', 'POST']) +@require_login('teacher') def add_reaction(): - """Додавање реакција со автоматско креирање експеримент""" - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - + if request.method == 'POST': + try: + element1_id = int(request.form['element1_id']) + element2_id = int(request.form['element2_id']) + product = (request.form.get('product') or '').strip() or None + conditions = (request.form.get('conditions') or '').strip() or None + temperature = (request.form.get('temperature') or '').strip() + pressure = (request.form.get('pressure') or '').strip() + catalyst = (request.form.get('catalyst') or '').strip() + + extra = [] + if temperature: extra.append(f"T={temperature}") + if pressure: extra.append(f"p={pressure}") + if catalyst: extra.append(f"катализатор={catalyst}") + if extra: + conditions = (conditions + "; " if conditions else "") + "; ".join(extra) + + experiment_result = (request.form.get('experiment_result') or '').strip() or None + safety_warning = (request.form.get('safety_warning') or '').strip() or None + equipment_ids = [int(x) for x in request.form.getlist('equipment_ids')] or None + + if element1_id == element2_id: + flash('Одбери два различни елементи.', 'warning') + return redirect(url_for('add_reaction')) + + res = DatabaseManager.create_reaction_and_experiment( + teacher_id=session['user_id'], + element1_id=element1_id, + element2_id=element2_id, + product=product, + conditions=conditions, + experiment_result=experiment_result, + safety_warning=safety_warning, + equipment_ids=equipment_ids + ) + if not res: + flash('Неуспешно креирање (провери ID вредности).', 'danger') + return redirect(url_for('add_reaction')) + + flash('Реакцијата и експериментот се успешно креирани.', 'success') + return redirect(url_for('reactions')) + + except ForeignKeyViolation: + flash('Погрешни ID вредности за елементи/наставник/опрема.', 'danger') + return redirect(url_for('add_reaction')) + except Exception as ex: + flash(f'Настана грешка: {ex}', 'danger') + return redirect(url_for('add_reaction')) + + elements = DatabaseManager.get_all_elements() + equipment = DatabaseManager.get_all_equipment() + return render_template('add_reaction.html', elements=elements, equipment=equipment) + + +@app.route('/reactions//edit', methods=['GET', 'POST']) +@require_login('teacher') +def edit_reaction(reaction_id): + reaction = DatabaseManager.get_reaction_by_id(reaction_id) + if not reaction: + return redirect(url_for('reactions')) elements = DatabaseManager.get_all_elements() - if request.method == 'POST': element1_id = int(request.form['element1_id']) @@ -411,79 +383,70 @@ product = request.form['product'] conditions = request.form['conditions'] - safety_warning = request.form.get('safety_warning', 'Стандардни безбедносни мерки') - teacher_id = session['user_id'] - - result = DatabaseManager.create_reaction_and_experiment( - teacher_id, element1_id, element2_id, product, conditions, safety_warning - ) - - if result: - return redirect('/reactions') - else: - return render_template('add_reaction.html', - elements=elements, - error='Грешка при додавање реакција и експеримент') - - return render_template('add_reaction.html', elements=elements) - + if DatabaseManager.update_reaction(reaction_id, element1_id, element2_id, product, conditions): + return redirect(url_for('reactions')) + return render_template('edit_reaction.html', reaction=reaction, elements=elements, error='Грешка при ажурирање') + return render_template('edit_reaction.html', reaction=reaction, elements=elements) + + +@app.route('/reactions//delete', methods=['POST']) +@require_login('teacher') +def delete_reaction(reaction_id): + if DatabaseManager.delete_reaction(reaction_id): + return redirect(url_for('reactions')) + return redirect(url_for('reactions')) + + +# ------------------------------ +# Laboratory + APIs +# ------------------------------ @app.route('/laboratory') +@require_login() def laboratory(): - """Виртуелна лабораторија - различни темплејти според улога""" - if 'user_id' not in session: - return redirect('/login') - elements = DatabaseManager.get_all_elements() - if session['role'] == 'teacher': - return render_template('laboratory.html', - elements=elements, - user_role=session['role']) - else: - return render_template('virtual_laboratory.html', - elements=elements, - user_role=session['role']) + return render_template('laboratory.html', elements=elements, user_role=session['role']) + return render_template('virtual_laboratory.html', elements=elements, user_role=session['role']) + @app.route('/api/simulate-reaction', methods=['POST']) +@require_login() def simulate_reaction(): - """API за симулација на реакција""" - if 'user_id' not in session: - return jsonify({'error': 'Неавторизиран пристап'}) - - data = request.get_json() + try: + data = request.get_json(silent=True) or {} + element1_symbol = (data.get('element1') or '').strip() + element2_symbol = (data.get('element2') or '').strip() + if not element1_symbol or not element2_symbol: + return jsonify({'success': False, 'message': 'Недостигаат параметри (element1/element2)'}), 400 + + reactions = DatabaseManager.get_all_reactions() or [] + for reaction in reactions: + if ((reaction['element1_symbol'] == element1_symbol and reaction['element2_symbol'] == element2_symbol) or + (reaction['element1_symbol'] == element2_symbol and reaction['element2_symbol'] == element1_symbol)): + experiment = DatabaseManager.get_experiment_by_reaction(reaction['reaction_id']) + return jsonify({ + 'success': True, + 'product': reaction['product'], + 'conditions': reaction.get('conditions'), + 'reaction_id': reaction['reaction_id'], + 'experiment_id': experiment['experiment_id'] if experiment else None, + 'elements': f"{reaction['element1_name']} + {reaction['element2_name']}" + }), 200 + + return jsonify({ + 'success': False, + 'message': f'Реакцијата меѓу {element1_symbol} и {element2_symbol} не е дефинирана во системот.' + }), 200 + except Exception as e: + app.logger.exception("simulate_reaction failed") + return jsonify({'success': False, 'message': f'Серверска грешка: {str(e)}'}), 500 + + +@app.route('/api/check-reaction', methods=['POST']) +@require_login() +def check_reaction(): + data = request.get_json() or {} element1_symbol = data.get('element1') element2_symbol = data.get('element2') - - reactions = DatabaseManager.get_all_reactions() - for reaction in reactions: - if ((reaction['element1_symbol'] == element1_symbol and reaction['element2_symbol'] == element2_symbol) or - (reaction['element1_symbol'] == element2_symbol and reaction['element2_symbol'] == element1_symbol)): - - experiment = DatabaseManager.get_experiment_by_reaction(reaction['reaction_id']) - - return jsonify({ - 'success': True, - 'product': reaction['product'], - 'conditions': reaction['conditions'], - 'reaction_id': reaction['reaction_id'], - 'experiment_id': experiment['experiment_id'] if experiment else None, - 'elements': f"{reaction['element1_name']} + {reaction['element2_name']}" - }) - - return jsonify({ - 'success': False, - 'message': f'Реакцијата меѓу {element1_symbol} и {element2_symbol} не е дефинирана во системот.' - }) - -@app.route('/api/check-reaction', methods=['POST']) -def check_reaction(): - """API за проверка на реакција""" - if 'user_id' not in session: - return jsonify({'error': 'Неавторизиран пристап'}) - - data = request.get_json() - element1_symbol = data.get('element1') - element2_symbol = data.get('element2') - - reactions = DatabaseManager.get_all_reactions() + reactions = DatabaseManager.get_all_reactions() or [] for reaction in reactions: if ((reaction['element1_symbol'] == element1_symbol and reaction['element2_symbol'] == element2_symbol) or @@ -495,127 +458,372 @@ 'reaction_id': reaction['reaction_id'] }) - - return jsonify({ - 'success': False, - 'message': 'Реакцијата не е дефинирана во системот' - }) - -@app.route('/reactions//edit', methods=['GET', 'POST']) -def edit_reaction(reaction_id): - """Едитирај реакција - само за професори""" - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - reaction = DatabaseManager.get_reaction_by_id(reaction_id) - if not reaction: - return redirect('/reactions') - - elements = DatabaseManager.get_all_elements() - - if request.method == 'POST': - element1_id = int(request.form['element1_id']) - element2_id = int(request.form['element2_id']) - product = request.form['product'] - conditions = request.form['conditions'] - - if DatabaseManager.update_reaction(reaction_id, element1_id, element2_id, product, conditions): - return redirect('/reactions') - else: - return render_template('edit_reaction.html', reaction=reaction, elements=elements, - error='Грешка при ажурирање') - - return render_template('edit_reaction.html', reaction=reaction, elements=elements) - -@app.route('/reactions//delete', methods=['POST']) -def delete_reaction(reaction_id): - """Избриши реакција - само за професори""" - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - - if DatabaseManager.delete_reaction(reaction_id): - return redirect('/reactions') - else: - return redirect('/reactions') + return jsonify({'success': False, 'message': 'Реакцијата не е дефинирана во системот'}) + @app.route('/save-experiment', methods=['POST']) +@require_login() def save_experiment(): - """Зачувување на учество во експеримент""" - if 'user_id' not in session: - return jsonify({'error': 'Неавторизиран пристап'}) - - data = request.get_json() + data = request.get_json() or {} reaction_id = data.get('reaction_id') - experiment = DatabaseManager.get_experiment_by_reaction(reaction_id) - if not experiment: if session['role'] != 'teacher': - return jsonify({ - 'success': False, - 'message': 'Не постои експеримент за оваа реакција. Контактирајте го вашиот професор.' - }) - + return jsonify({'success': False, 'message': 'Не постои експеримент за оваа реакција. Контактирајте го вашиот професор.'}) result_description = data.get('result', 'Експериментална симулација') safety_warning = data.get('safety_warning', 'Стандардни безбедносни мерки') - - experiment_id = DatabaseManager.insert_experiment( - session['user_id'], - reaction_id, - result_description, - safety_warning - ) + experiment_id = DatabaseManager.insert_experiment(session['user_id'], reaction_id, result_description, safety_warning) else: experiment_id = experiment['experiment_id'] - if experiment_id: DatabaseManager.track_experiment_participation(session['user_id'], experiment_id) return jsonify({'success': True, 'experiment_id': experiment_id}) + return jsonify({'success': False, 'message': 'Грешка при зачувување'}) + + +# ------------------------------ +# Experiments +# ------------------------------ +@app.route('/experiments') +@require_login() +def experiments(): + base = DatabaseManager.get_all_experiments() + experiments = _enrich_with_equipment(base) + return render_template('experiments_list.html', experiments=experiments, user_role=session['role']) + + +@app.route('/experiments/') +@require_login() +def experiment_detail(experiment_id): + experiments_list = DatabaseManager.get_all_experiments() + experiment = None + for exp in (experiments_list or []): + if exp['experiment_id'] == experiment_id: + experiment = exp + break + if not experiment: + return redirect(url_for('experiments')) + equipment = DatabaseManager.get_experiment_equipment(experiment_id) + return render_template('experiment_detail.html', experiment=experiment, equipment=equipment, user_role=session['role']) + + +# ------------------------------ +# Students (teacher view) +# ------------------------------ +@app.route('/my_students') +@require_login('teacher') +def my_students(): + teacher_id = session['user_id'] + students_activity = DatabaseManager.get_my_students_activity(teacher_id) + return render_template('my_students.html', students=students_activity, teacher_name=session['user_name']) + + +# ------------------------------ +# My experiments (by role) +# ------------------------------ +@app.route('/my-experiments') +@require_login() +def my_experiments(): + if session['role'] == 'student': + base = DatabaseManager.get_student_participation_experiments(session['user_id']) else: - return jsonify({'success': False, 'message': 'Грешка при зачувување'}) + base = DatabaseManager.get_user_experiments(session['user_id']) + experiments = _enrich_with_equipment(base) + return render_template('my_experiments.html', experiments=experiments, user_name=session['user_name'], user_role=session['role']) + + +# ------------------------------ +# Reports (menu + basic) +# ------------------------------ +@app.route('/reports') +@require_login('teacher') +def reports_menu(): + return render_template('reports/menu.html') + + +@app.route('/reports/equipment-usage') +@require_login('teacher') +def reports_equipment_usage(): + usage_report = DatabaseManager.get_equipment_usage_report() + if usage_report: + return jsonify({ + 'status': 'success', + 'report_name': 'Извештај за користење на лабораториска опрема', + 'sql_query': ''' + SELECT le.equipment_name, COUNT(ele.experiment_id) AS usage_count + FROM experimentlabequipment ele + RIGHT JOIN labequipment le ON ele.equipment_id = le.equipment_id + GROUP BY le.equipment_name + ORDER BY usage_count DESC + ''', + 'count': len(usage_report), + 'data': usage_report + }) + return jsonify({'status': 'error', 'message': 'Грешка при генерирање на извештај'}) + + +@app.route('/reports/teacher_statistics') +@require_login('teacher') +def reports_teacher_statistics(): + stats = DatabaseManager.get_teacher_statistics() + return render_template('reports/teacher_statistics.html', statistics=stats) + + +@app.route('/reports/inactive_students') +@require_login('teacher') +def reports_inactive_students(): + teacher_id = session['user_id'] + students = DatabaseManager.get_students_without_experiments(teacher_id) + return render_template('reports/inactive_students.html', students=students) + + +@app.route('/reports/element_views') +@require_login('teacher') +def reports_element_views(): + views_data = DatabaseManager.get_element_views_report() + return render_template('reports/element_views.html', views=views_data) + + +@app.route('/reports/detailed_experiments') +@require_login('teacher') +def reports_detailed_experiments(): + teacher_id = session['user_id'] + experiments = DatabaseManager.get_students_experiments_detailed(teacher_id) + return render_template('reports/detailed_experiments.html', experiments=experiments) + + +@app.route('/reports/low_activity_students') +@require_login('teacher') +def reports_low_activity_students(): + teacher_id = session['user_id'] + students = DatabaseManager.get_students_with_few_experiments(teacher_id, 3) + return render_template('reports/low_activity_students.html', students=students) + @app.route('/reports/student_experiments') -def student_experiments_report(): - """Извештај за експерименти на студенти""" - if 'user_id' not in session or session['role'] != 'teacher': - return redirect('/login') - +@require_login('teacher') +def reports_student_experiments(): teacher_id = session['user_id'] experiments = DatabaseManager.get_students_experiments_for_teacher(teacher_id) - - return render_template('reports/student_experiments.html', - student_experiments=experiments) - -@app.route('/my-experiments') -def my_experiments(): - """Приказ на експерименти според улога""" - if 'user_id' not in session: + return render_template('reports/student_experiments.html', student_experiments=experiments) + + +@app.route('/reports/user_activity') +@require_login('teacher') +def reports_user_activity(): + summary = DatabaseManager.get_user_activity_summary() + return render_template('reports/user_activity.html', summary=summary) + + +# ------------------------------ +# Advanced Reports (SQL) +# ------------------------------ +@app.route('/reports/adv/student_experiment_counts') +@require_login('teacher') +def reports_adv_student_experiment_counts(): + sql = """ + SELECT + s.student_id, + u.user_name || ' ' || u.user_surname AS full_name, + COUNT(up.experiment_id) AS total_experiments + FROM student s + JOIN "User" u ON s.student_id = u.user_id + LEFT JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + LEFT JOIN experiment e ON up.experiment_id = e.experiment_id + WHERE s.teacher_id = %s + GROUP BY s.student_id, full_name + ORDER BY total_experiments DESC, full_name + """ + rows = DatabaseManager.execute_query(sql, (session['user_id'],)) or [] + return _render_generic("Студенти и број на извршени експерименти", rows) + + +@app.route('/reports/adv/equipment_usage') +@require_login('teacher') +def reports_adv_equipment_usage(): + sql = """ + SELECT + le.equipment_name, + COUNT(ele.experiment_id) AS usage_count + FROM experimentlabequipment ele + JOIN labequipment le ON ele.equipment_id = le.equipment_id + GROUP BY le.equipment_name + ORDER BY usage_count DESC, le.equipment_name + """ + rows = DatabaseManager.execute_query(sql) or [] + return _render_generic("Користеност на лабораториска опрема", rows) + + +@app.route('/reports/adv/students_experiments_detailed') +def reports_adv_students_experiments_detailed(): + if 'user_id' not in session or session.get('role') != 'teacher': return redirect('/login') - - if session['role'] == 'student': - # 🔑 земи експерименти каде студентот учествувал - experiments = DatabaseManager.get_student_participation_experiments(session['user_id']) - else: - # 🔑 земи експерименти креирани од професорот - experiments = DatabaseManager.get_user_experiments(session['user_id']) - - return render_template('my_experiments.html', - experiments=experiments, - user_name=session['user_name'], - user_role=session['role']) - - -@app.route('/api/dashboard-stats') -def dashboard_stats(): - """API endpoint за статистики на dashboard""" - if 'user_id' not in session: - return jsonify({'error': 'Неавторизиран пристап'}) - - if session['role'] == 'student': - stats = DatabaseManager.get_student_statistics(session['user_id']) - else: - stats = DatabaseManager.get_teacher_dashboard_statistics(session['user_id']) - - return jsonify(stats) - + + sql = """ + SELECT + s.student_id, + u.user_name || ' ' || u.user_surname AS full_name, + e.experiment_id, + e.result, + up.participation_timestamp AS participation_time + FROM student s + JOIN "User" u ON s.student_id = u.user_id + JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + JOIN experiment e ON up.experiment_id = e.experiment_id + WHERE s.teacher_id = %s + ORDER BY full_name, participation_time DESC + """ + rows = DatabaseManager.execute_query(sql, (session['user_id'],)) or [] + + # Ако сакаш брзо да провериш дали има глобални податоци без филтер по професор: + if not rows and request.args.get('all') == '1': + sql_all = sql.replace("WHERE s.teacher_id = %s", "") + rows = DatabaseManager.execute_query(sql_all) or [] + + return _render_generic("Детален извештај: студенти и експерименти", rows) + +@app.route('/reports/adv/experiment_participants') +@require_login('teacher') +def reports_adv_experiment_participants(): + experiment_id = request.args.get('experiment_id', type=int) + if not experiment_id: + exps = DatabaseManager.execute_query( + "SELECT experiment_id, result FROM experiment ORDER BY experiment_id DESC" + ) or [] + return render_template('reports/experiment_participants.html', experiments=exps) + + sql = """ + SELECT + s.student_id, + u.user_name || ' ' || u.user_surname AS full_name, + e.experiment_id, + e.result + FROM student s + JOIN "User" u ON s.student_id = u.user_id + JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + JOIN experiment e ON up.experiment_id = e.experiment_id + WHERE s.teacher_id = %s AND e.experiment_id = %s + ORDER BY u.user_name + """ + rows = DatabaseManager.execute_query(sql, (session['user_id'], experiment_id)) or [] + return _render_generic(f"Студенти кои го извршиле експеримент #{experiment_id}", rows) + + +@app.route('/reports/adv/avg_equipment_per_experiment') +@require_login('teacher') +def reports_adv_avg_equipment_per_experiment(): + sql = """ + SELECT + COALESCE(AVG(instrument_count), 0) AS average_lab_equipment_per_experiment + FROM ( + SELECT e.experiment_id, COUNT(ele.equipment_id) AS instrument_count + FROM experiment e + LEFT JOIN experimentlabequipment ele ON e.experiment_id = ele.experiment_id + GROUP BY e.experiment_id + ) subquery + """ + rows = DatabaseManager.execute_query(sql) or [] + return _render_generic("Просечен број инструменти по експеримент", rows) + + +@app.route('/reports/adv/most_used_elements') +@require_login('teacher') +def reports_adv_most_used_elements(): + sql = """ + SELECT + el.element_name, + COUNT(r.reaction_id) AS total_uses + FROM elements el + JOIN reaction r + ON el.element_id = r.element1_id + OR el.element_id = r.element2_id + GROUP BY el.element_name + ORDER BY total_uses DESC, el.element_name + """ + rows = DatabaseManager.execute_query(sql) or [] + return _render_generic("Најчесто користени елементи во експерименти", rows) + + +@app.route('/reports/adv/most_performed_experiments') +@require_login('teacher') +def reports_adv_most_performed_experiments(): + sql = """ + SELECT + e.experiment_id, + e.result, + COUNT(up.user_id) AS student_participation + FROM experiment e + LEFT JOIN userparticipatesinexperiment up + ON e.experiment_id = up.experiment_id + GROUP BY e.experiment_id, e.result + ORDER BY student_participination DESC, e.experiment_id + """ + sql = sql.replace("student_participination", "student_participation") + rows = DatabaseManager.execute_query(sql) or [] + return _render_generic("Најчесто реализирани експерименти", rows) + + +@app.route('/reports/adv/never_participated_students') +@require_login('teacher') +def reports_adv_never_participated_students(): + sql = """ + SELECT + s.student_id, + u.user_name || ' ' || u.user_surname AS full_name + FROM student s + JOIN "User" u ON s.student_id = u.user_id + LEFT JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + WHERE s.teacher_id = %s + AND up.user_id IS NULL + ORDER BY full_name + """ + rows = DatabaseManager.execute_query(sql, (session['user_id'],)) or [] + return _render_generic("Студенти кои никогаш не учествувале во експерименти", rows) + + +@app.route('/reports/adv/students_below_threshold') +@require_login('teacher') +def reports_adv_students_below_threshold(): + max_exp = request.args.get('max', default=3, type=int) + sql = """ + SELECT + s.student_id, + u.user_name || ' ' || u.user_surname AS full_name, + COUNT(up.experiment_id) AS total_experiments + FROM student s + JOIN "User" u ON s.student_id = u.user_id + LEFT JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + WHERE s.teacher_id = %s + GROUP BY s.student_id, full_name + HAVING COUNT(up.experiment_id) < %s + ORDER BY total_experiments ASC, full_name + """ + rows = DatabaseManager.execute_query(sql, (session['user_id'], max_exp)) or [] + return _render_generic(f"Студенти со помалку од {max_exp} експерименти", rows) + + +@app.route('/reports/adv/student_views') +@require_login('teacher') +def reports_adv_student_views(): + sql = """ + SELECT + s.student_id, + u.user_name || ' ' || u.user_surname AS full_name, + COUNT(DISTINCT ue.element_id) AS total_elements_viewed, + COUNT(DISTINCT ul.equipment_id) AS total_lab_equipment_viewed + FROM student s + JOIN "User" u ON s.student_id = u.user_id + LEFT JOIN userviewselement ue ON s.student_id = ue.user_id + LEFT JOIN userviewslabequipment ul ON s.student_id = ul.user_id + WHERE s.teacher_id = %s + GROUP BY s.student_id, full_name + ORDER BY total_elements_viewed DESC, total_lab_equipment_viewed DESC + """ + rows = DatabaseManager.execute_query(sql, (session['user_id'],)) or [] + return _render_generic("Прегледи на елементи и опрема по студент", rows) + + +# ------------------------------ +# Run +# ------------------------------ if __name__ == '__main__': app.run(debug=True) Index: templates/add_reaction.html =================================================================== --- templates/add_reaction.html (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ templates/add_reaction.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -4,5 +4,5 @@ {% block content %}
-
+
@@ -18,25 +18,19 @@
Реактанти
- -
+
- -
+
@@ -45,16 +39,43 @@
-
Резултати
- -
- - +
Реакција
+
+ +
- -
- - +
+ + +
+
+
+ +
+
+ +
+
+ +
+
+
+
+ +
+ +
+
+
Експеримент
+
+ + +
+
+ +
+
Безбедност
+
+ +
@@ -65,22 +86,25 @@
-
Безбедносни информации за експериментот
- +
Лабораториска опрема
- - + {% for item in equipment %} +
+ + +
+ {% endfor %}
-
- Напомена: При креирање на реакцијата, автоматски ќе се креира и експеримент - кој студентите ќе можат да го извршуваат во виртуелната лабораторија. +
+ Напомена: Со креирање на реакција, автоматски се додава и експеримент кој студентите можат да го извршуваат во виртуелната лабораторија.
Откажи @@ -93,17 +117,7 @@ {% endblock %} Index: templates/dashboard_student.html =================================================================== --- templates/dashboard_student.html (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ templates/dashboard_student.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -63,15 +63,5 @@
-
-
-
-
Реакции
-
-
-

Прегледајте ги дефинираните хемиски реакции

- Види реакции -
-
-
+
@@ -82,59 +72,8 @@

Следете го вашиот напредок и активности

- + Види напредок
- - -
-
-
-
-
Брза статистика
-
-
-

0

-

Мои експерименти

-
-
-

0

-

Прегледани елементи

-
-
-

0

-

Користена опрема

-
-
-

0

-

Симулирани реакции

-
-
-
-
-
-
- - {% endblock %} Index: templates/dashboard_teacher.html =================================================================== --- templates/dashboard_teacher.html (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ templates/dashboard_teacher.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -4,200 +4,132 @@ {% block content %}
-
-

Добредојде, {{ user_name }}!

-

Професорска контролна табла

-
+
+

Добредојде, {{ user_name }}!

+

Професорска контролна табла

+
-
-
-
-
Хемиски елементи
-
-
-

Управување со хемиски елементи

- Прегледај - Додај нов -
-
+
+
+
+
Хемиски елементи
+
+
+

Управување со хемиски елементи

+ Прегледај + Додај нов +
- -
-
-
-
Лабораториска опрема
-
-
-

Управување со опрема

- Прегледај - Додај нова -
-
+
+ +
+
+
+
Лабораториска опрема
+
+
+

Управување со опрема

+ Прегледај + Додај нова +
- -
-
-
-
Реакции
-
-
-

Креирање на реакции

- Прегледај - Додај нова -
-
+
+ +
+
+
+
Реакции
+
+
+

Креирање на реакции

+ Прегледај + Додај нова +
- -
-
-
-
Лабораторија
-
-
-

Тестирај реакции

- Отвори -
-
+
+ +
+
+
+
Лабораторија
+
+
+

Тестирај реакции

+ Отвори +
+
-
-
-
-
Експерименти
-
-
-

Преглед на експерименти

- Сите - Мои -
-
+
+
+
+
Експерименти
+
+
+

Преглед на експерименти

+ Сите + Мои +
- -
-
-
-
Студенти
-
-
-

Следете го напредокот на студентите

- Види студенти -
-
+
+ +
+
+
+
Студенти
+
+
+

Следете го напредокот на студентите

+ Види студенти +
- -
-
-
-
Извештаи
-
-
-

Детални статистики

- Види извештаи -
-
+
+ +
+
+
+
Извештаи
+
+
+

Детални статистики

+ Види извештаи +
+
-
-
-
-
Брзи акции
-
-
-
- - Нова реакција - - - Нов елемент - - - Нова опрема - - - Експерименти на студенти - - - Неактивни студенти - -
-
+
+
+
+
Брзи акции
+
+
+
+ + Нова реакција + + + Нов елемент + + + Нова опрема + + + Експерименти на студенти + + + Неактивни студенти +
+
+
- - -
-
-
-
-
Статистики на системот
-
-
-
-

0

-

Мои студенти

-
-
-
-
-

0

-

Креирани реакции

-
-
-
-
-

0

-

Вкупно експерименти

-
-
-
-
-

0

-

Активности денес

-
-
-
-
-
-
-
- - - - {% endblock %} Index: templates/experiments_list.html =================================================================== --- templates/experiments_list.html (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ templates/experiments_list.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -57,12 +57,26 @@
{% endif %} - + {% if experiment.equipment and experiment.equipment|length > 0 %} +

+ Опрема: +

    + {% for eq in experiment.equipment %} +
  • + {{ eq.equipment_name }} + {% if eq.type %}({{ eq.type }}){% endif %} +
    • + {% endfor %} +
+

+ {% else %} +

+ Опрема: + Нема евидентирана опрема +

+ {% endif %}
Креирано од: {{ experiment.created_by }} - - Детали → -
Index: templates/laboratory.html =================================================================== --- templates/laboratory.html (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ templates/laboratory.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -4,110 +4,99 @@ {% block content %}
-
-
-

🧬 Професорска лабораторија

-

Тестирајте реакции и креирајте експерименти

-
+
+
+

🧬 Професорска лабораторија

+

Тестирајте реакции и креирајте експерименти

- -
-
-
-
- -
- - -
-
- Избери
Елемент 1 -
- -
- - -
-
+
- -
→
-
- - -
-
- Избери
Елемент 2 -
- -
-
- - - +
+ +
+
+
+
+
+
+
+ Избери
Елемент 1 +
+ +
+ +
+
+
+ +
→
+
+ +
+
+ Избери
Елемент 2 +
+ +
+
+ + +
+
+ +
+
+
- Назад до Dashboard - 📋 Управувај реакции - 📊 Сите експерименти - 📝 Мои експерименти + Назад до Dashboard + 📋 Управувај реакции + 📊 Сите експерименти + 📝 Мои експерименти
@@ -117,170 +106,183 @@ document.addEventListener('DOMContentLoaded', function() { - document.getElementById('element1-select').addEventListener('change', function() { - updateElementDisplay(this, 'element1-display', 'element1'); - checkReactionReady(); - }); - - document.getElementById('element2-select').addEventListener('change', function() { - updateElementDisplay(this, 'element2-display', 'element2'); - checkReactionReady(); - }); + document.getElementById('element1-select').addEventListener('change', function() { + updateElementDisplay(this, 'element1-display', 'element1'); + checkReactionReady(); + }); + document.getElementById('element2-select').addEventListener('change', function() { + updateElementDisplay(this, 'element2-display', 'element2'); + checkReactionReady(); + }); }); -function updateElementDisplay(select, displayId, elementKey) { - const display = document.getElementById(displayId); - const value = select.value; - const name = select.options[select.selectedIndex]?.dataset?.name || ''; - - if (value) { - display.innerHTML = ` -
${value}
-
${name}
- `; - display.style.borderColor = '#6f42c1'; - display.style.background = 'linear-gradient(45deg, #6f42c1, #007bff)'; - - selectedElements[elementKey] = { symbol: value, name: name }; +function updateElementDisplay(select, displayId, key) { + const display = document.getElementById(displayId); + const id = select.value; + const symbol = select.options[select.selectedIndex]?.dataset?.symbol || ''; + const name = select.options[select.selectedIndex]?.dataset?.name || ''; + + if (id) { + display.innerHTML = ` +
${symbol}
+
${name}
`; + display.style.borderColor = '#6f42c1'; + display.style.background = 'linear-gradient(45deg,#6f42c1,#007bff)'; + selectedElements[key] = { id: parseInt(id), symbol, name }; + } else { + display.innerHTML = displayId === 'element1-display' + ? 'Избери
Елемент 1' + : 'Избери
Елемент 2'; + display.style.borderColor = '#6f42c1'; + display.style.background = '#f8f9fa'; + delete selectedElements[key]; + } +} + +function checkReactionReady() { + document.getElementById('react-btn').disabled = !(selectedElements.element1 && selectedElements.element2); +} + +function testReaction() { + if (!selectedElements.element1 || !selectedElements.element2) return; + + const btn = document.getElementById('react-btn'); + btn.innerHTML = '⏳ Проверувам...'; + btn.disabled = true; + + fetch('/api/simulate-reaction', { + method: 'POST', + headers: { 'Content-Type': 'application/json' }, + body: JSON.stringify({ + element1: selectedElements.element1.symbol, + element2: selectedElements.element2.symbol + }) + }) + .then(async (resp) => { + const text = await resp.text(); + let data; + try { + data = JSON.parse(text); + } catch (e) { + throw new Error(`Неочекуван одговор од серверот: ${text.substring(0, 200)}`); + } + if (!resp.ok) { + throw new Error(data.message || `HTTP ${resp.status}`); + } + return data; + }) + .then((data) => { + showTeacherResult(data); + btn.innerHTML = '🔬 Тестирај'; + btn.disabled = false; + }) + .catch((err) => { + console.error(err); + alert(err.message); + btn.innerHTML = '🔬 Тестирај'; + btn.disabled = false; + }); +} + + +function showTeacherResult(data) { + const resultArea = document.getElementById('result-area'); + const resultCard = document.getElementById('result-card'); + const resultContent = document.getElementById('result-content'); + const teacherActions = document.getElementById('teacher-actions'); + + resultArea.style.display = 'block'; + document.getElementById('new-reaction-form').style.display = 'none'; + + if (data.success) { + currentReactionData = data; + resultCard.className = 'card border-success'; + resultContent.innerHTML = ` +

✅ Постои реакција!

+
+ ${selectedElements.element1.symbol} + ${selectedElements.element2.symbol} → ${data.product} +
+
+
Продукт: ${data.product}
+
Услови: ${data.conditions || 'Стандардни услови'}
+
+
+ Reaction ID: ${data.reaction_id}
+ Реакцијата постои и студентите можат да ја користат. +
`; + teacherActions.style.display = 'block'; + } else { + currentReactionData = null; + resultCard.className = 'card border-warning'; + resultContent.innerHTML = ` +

⚠️ Реакцијата не постои

+
+ ${selectedElements.element1.symbol} + ${selectedElements.element2.symbol} → ? +
+
+ Оваа реакција не е дефинирана. Креирај ја сега и автоматски ќе се додаде експеримент. +
`; + teacherActions.style.display = 'block'; + } +} + +function createNewReaction() { + document.getElementById('new-reaction-form').style.display = 'block'; + document.getElementById('teacher-actions').style.display = 'none'; +} + +function cancelNewReaction() { + document.getElementById('new-reaction-form').style.display = 'none'; + document.getElementById('teacher-actions').style.display = 'block'; +} + +function saveNewReaction() { + const product = document.getElementById('product-input').value.trim(); + const conditions = document.getElementById('conditions-input').value.trim(); + const safety = document.getElementById('safety-input').value.trim() || 'Стандардни безбедносни мерки'; + const expRes = document.getElementById('expres-input').value.trim(); + + if (!selectedElements.element1 || !selectedElements.element2) { + alert('Изберете два елементи.'); + return; + } + if (!product) { + alert('Внесете продукт на реакцијата!'); + return; + } + + const params = new URLSearchParams({ + element1_id: selectedElements.element1.id, + element2_id: selectedElements.element2.id, + product: product, + conditions: conditions, + experiment_result: expRes, + safety_warning: safety + }); + + fetch('/reactions/add', { + method: 'POST', + headers: { 'Content-Type': 'application/x-www-form-urlencoded' }, + body: params.toString() + }) + .then(resp => { + if (resp.redirected) { + window.location.href = resp.url; + return; + } + if (resp.ok) { + alert('Реакцијата и експериментот се успешно креирани!'); + window.location.href = '/reactions'; } else { - display.innerHTML = displayId === 'element1-display' ? - 'Избери
Елемент 1' : - 'Избери
Елемент 2'; - display.style.borderColor = '#6f42c1'; - display.style.background = '#f8f9fa'; - - delete selectedElements[elementKey]; - } -} - -function checkReactionReady() { - const reactBtn = document.getElementById('react-btn'); - reactBtn.disabled = !(selectedElements.element1 && selectedElements.element2); -} - -function testReaction() { - if (!selectedElements.element1 || !selectedElements.element2) return; - - const btn = document.getElementById('react-btn'); - btn.innerHTML = '⏳ Проверувам...'; - btn.disabled = true; - - fetch('/api/simulate-reaction', { - method: 'POST', - headers: { - 'Content-Type': 'application/json', - }, - body: JSON.stringify({ - element1: selectedElements.element1.symbol, - element2: selectedElements.element2.symbol - }) - }) - .then(response => response.json()) - .then(data => { - showTeacherResult(data); - btn.innerHTML = '🔬 Тестирај'; - btn.disabled = false; - }) - .catch(error => { - console.error('Грешка:', error); - btn.innerHTML = '🔬 Тестирај'; - btn.disabled = false; - }); -} - -function showTeacherResult(data) { - const resultArea = document.getElementById('result-area'); - const resultCard = document.getElementById('result-card'); - const resultContent = document.getElementById('result-content'); - const teacherActions = document.getElementById('teacher-actions'); - - resultArea.style.display = 'block'; - document.getElementById('new-reaction-form').style.display = 'none'; - - if (data.success) { - currentReactionData = data; - resultCard.className = 'card border-success'; - resultContent.innerHTML = ` -

✅ Постои реакција!

-
- ${selectedElements.element1.symbol} + ${selectedElements.element2.symbol} → ${data.product} -
-
-
- Продукт: ${data.product} -
-
- Услови: ${data.conditions || 'Стандардни услови'} -
-
-
- Reaction ID: ${data.reaction_id}
- Оваа реакција веќе постои во системот и студентите можат да ја користат. -
- `; - teacherActions.style.display = 'block'; - - } else { - currentReactionData = null; - resultCard.className = 'card border-warning'; - resultContent.innerHTML = ` -

⚠️ Реакцијата не постои

-
- ${selectedElements.element1.symbol} + ${selectedElements.element2.symbol} → ? -
-
- Оваа реакција не е дефинирана во системот.
- Можете да ја додадете како нова реакција и автоматски да креирате експеримент. -
- `; - teacherActions.style.display = 'block'; - } -} - -function createNewReaction() { - document.getElementById('new-reaction-form').style.display = 'block'; - document.getElementById('teacher-actions').style.display = 'none'; -} - -function cancelNewReaction() { - document.getElementById('new-reaction-form').style.display = 'none'; - document.getElementById('teacher-actions').style.display = 'block'; -} - -function saveNewReaction() { - const product = document.getElementById('product-input').value; - const conditions = document.getElementById('conditions-input').value; - const safety = document.getElementById('safety-input').value; - - if (!product) { - alert('Внесете продукт на реакцијата!'); - return; - } - - // Прво креирај реакција - fetch('/reactions/add', { - method: 'POST', - headers: { - 'Content-Type': 'application/x-www-form-urlencoded', - }, - body: new URLSearchParams({ - element1_id: selectedElements.element1.id, - element2_id: selectedElements.element2.id, - product: product, - conditions: conditions - }) - }) - .then(response => { - if (response.ok) { - alert('Реакцијата и експериментот се успешно креирани!'); - window.location.href = '/reactions'; - } else { - alert('Грешка при креирање на реакцијата'); - } - }); + alert('Грешка при креирање на реакцијата.'); + } + }) + .catch(err => { + console.error(err); + alert('Грешка при креирање.'); + }); } function testAsStudent() { - // Редиректирај на студентски поглед - window.location.href = '/virtual-laboratory'; + window.location.href = '/laboratory'; } Index: templates/my_experiments.html =================================================================== --- templates/my_experiments.html (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ templates/my_experiments.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -7,5 +7,5 @@

📊 Мои експерименти

- {{ user_name }} | + {{ user_name }} | {{ 'Професор' if user_role == 'teacher' else 'Студент' }} @@ -39,7 +39,7 @@

{% if experiments[0].participation_timestamp %} - {{ experiments[0].participation_timestamp.strftime('%d.%m') }} + {{ experiments[0].participation_timestamp.strftime('%d.%m') }} {% elif experiments[0].time_stamp %} - {{ experiments[0].time_stamp.strftime('%d.%m') }} + {{ experiments[0].time_stamp.strftime('%d.%m') }} {% endif %}

@@ -60,7 +60,7 @@ {% if experiment.participation_timestamp %} - {{ experiment.participation_timestamp.strftime('%d.%m.%Y') }} + {{ experiment.participation_timestamp.strftime('%d.%m.%Y') }} {% elif experiment.time_stamp %} - {{ experiment.time_stamp.strftime('%d.%m.%Y') }} + {{ experiment.time_stamp.strftime('%d.%m.%Y') }} {% endif %} @@ -74,5 +74,5 @@
- +
@@ -81,15 +81,16 @@ {{ experiment.element1_name }} + {{ experiment.element2_name }}

- +

Услови:
{{ experiment.conditions or 'Стандардни услови' }}

- +

Резултат:
- {{ experiment.result[:80] }}{% if experiment.result|length > 80 %}...{% endif %} + {{ experiment.result[:80] }}{% if experiment.result|length > 80 %}...{% + endif %}

- + {% if experiment.safety_warning %}
@@ -97,15 +98,33 @@
{% endif %} - + {% if experiment.participation_timestamp or experiment.time_stamp %}

- + {% if experiment.participation_timestamp %} - {{ experiment.participation_timestamp.strftime('%H:%M:%S') }} + {{ experiment.participation_timestamp.strftime('%H:%M:%S') }} {% else %} - {{ experiment.time_stamp.strftime('%H:%M:%S') }} + {{ experiment.time_stamp.strftime('%H:%M:%S') }} {% endif %} +

+ {% endif %} + {% if experiment.equipment and experiment.equipment|length > 0 %} +

+ Опрема: +

    + {% for eq in experiment.equipment %} +
  • + {{ eq.equipment_name }} + {% if eq.type %}({{ eq.type }}){% endif %} +
    • + {% endfor %} +
+

+ {% else %} +

+ Опрема: + Нема евидентирана опрема

{% endif %} Index: templates/reports/experiment_participants.html =================================================================== --- templates/reports/experiment_participants.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) +++ templates/reports/experiment_participants.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -0,0 +1,25 @@ +{% extends "base.html" %} +{% block title %}Студенти по експеримент{% endblock %} + +{% block content %} +

Студенти кои го извршиле одбран експеримент

+

Одберете експеримент:

+ +
+
+ +
+
+ +
+
+ +
+ ◀ Назад кон извештаи +
+{% endblock %} Index: templates/reports/generic_report.html =================================================================== --- templates/reports/generic_report.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) +++ templates/reports/generic_report.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -0,0 +1,39 @@ +{% extends "base.html" %} +{% block title %}{{ title }}{% endblock %} + +{% block content %} +
+
+

{{ title }}

+
+
+ +{% if rows and headers %} +
+ + + + {% for h in headers %} + + {% endfor %} + + + + {% for r in rows %} + + {% for h in headers %} + + {% endfor %} + + {% endfor %} + +
{{ h }}
{{ r[h] }}
+
+{% else %} +
Нема податоци за прикажување.
+{% endif %} + +
+ ◀ Назад кон извештаи +
+{% endblock %} Index: templates/reports/menu.html =================================================================== --- templates/reports/menu.html (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ templates/reports/menu.html (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -4,50 +4,125 @@ {% block content %}
-
-

Извештаи и статистики

-

Изберете извештај за преглед

-
+
+

Извештаи и статистики

+

Изберете извештај за преглед

+
-
-
-
Активност на студенти
- - Мои студенти
- Преглед на активностите на вашите студенти - - - Неактивни студенти
- Студенти кои не учествувале во експерименти - - - Слабо активни студенти
- Студенти со помалку од 3 експерименти - -
+ +
+
+
Активност на студенти
+ + + Мои студенти
+ Преглед на активностите на вашите студенти + + + + Неактивни студенти
+ Студенти кои не учествувале во експерименти + + + + Слабо активни студенти
+ Студенти со помалку од 3 експерименти +
- -
-
-
Детални извештаи
- - Детални експерименти
- Сите експерименти по студент - - - Прегледи на елементи
- Кој студент кои елементи ги прегледал - - - Статистики по професор
- Споредба помеѓу професори - -
+
+ + +
+
+
Детални извештаи
+ + + Детални експерименти
+ Сите експерименти по студент + + + + Прегледи на елементи
+ Кој корисник кои елементи ги прегледал + + + + Статистики по професор
+ Споредба помеѓу професори +
+
+
+ +
+ + +
+
+

Напредни извештаи (SQL)

+

Извештаи базирани на директни SQL-записи

+
+ +
+
+
Студенти & учество
+ + + Студенти и број на експерименти
+ Колку експерименти има извршено секој студент + + + + Учесници по експеримент
+ Изберете експеримент и видете кои студенти учествувале + + + + Никогаш не учествувале
+ Студенти без ниту едно учество во експерименти + + + + Под праг на учество
+ Студенти со помалку од 3 експерименти (параметар: max) + +
+
+ +
+
+
Опрема, елементи & популарност
+ + + Користеност на опрема
+ Колку пати е користен секој инструмент + + + + Просечен број инструменти
+ Просек на опрема по експеримент + + + + Најчесто користени елементи
+ Елементи кои најмногу се појавуваат во реакции + + + + Најреализирани експерименти
+ Експерименти со најмногу учесници + + + + Прегледи: елементи & опрема
+ Број на прегледани елементи/опрема по студент + +
+
- Назад до Dashboard + Назад до Dashboard
{% endblock %} Index: utils/database_manager.py =================================================================== --- utils/database_manager.py (revision dfb03fea3a363812cf0c96d117a912bec2c6be49) +++ utils/database_manager.py (revision 0df6224b9fe7a85d473d1bc8058214530802b309) @@ -1,9 +1,10 @@ +# database_manager.py import psycopg2 from psycopg2.extras import RealDictCursor class DatabaseManager: + # ---------- CONNECTION ---------- @staticmethod def get_connection(): - # Ова е новиот метод што ќе реши грешката return psycopg2.connect( host='localhost', @@ -15,12 +16,13 @@ ) + # ЕДИНСТВЕНА execute_query (го избришавме дупликатот) @staticmethod def execute_query(query, params=None): try: conn = DatabaseManager.get_connection() - cursor = conn.cursor() - cursor.execute(query, params or ()) - result = cursor.fetchall() - cursor.close() + cur = conn.cursor() + cur.execute(query, params or ()) + result = cur.fetchall() + cur.close() conn.close() return result @@ -28,332 +30,183 @@ print(f"Грешка во execute_query: {e}") return None - + @staticmethod def test_connection(): try: - conn = psycopg2.connect( - host='localhost', - port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute("SELECT 1") + cur.close() + conn.close() + return True + except Exception: + return False + + # ---------- USERS / AUTH ---------- + @staticmethod + def authenticate_user(email, password): + """Автентикација (враќа row од \"User\" или None). Password проверката прави ја во сервис/route (bcrypt).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute( + 'SELECT user_id, user_name, user_surname, email, role, password FROM "User" WHERE email = %s', + (email,) ) - cursor = conn.cursor() - cursor.execute("SELECT 1") - cursor.close() - conn.close() - return True - except: - return False - + user = cur.fetchone() + cur.close() + conn.close() + return dict(user) if user else None + except Exception as e: + print(f"Грешка при автентикација: {e}") + return None + + @staticmethod + def register_user(name, surname, email, password_hash, role, teacher_id=None): + """Регистрирај нов корисник. Ако role='student' → внес во student; ако role='teacher' → внес во teacher.""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + + # User + cur.execute( + 'INSERT INTO "User" (user_name, user_surname, email, password, role) ' + 'VALUES (%s, %s, %s, %s, %s) RETURNING user_id', + (name, surname, email, password_hash, role) + ) + user_id = cur.fetchone()['user_id'] + + # Subtype + if role == 'student' and teacher_id: + cur.execute('INSERT INTO student (student_id, teacher_id) VALUES (%s, %s)', + (user_id, teacher_id)) + elif role == 'teacher': + cur.execute('INSERT INTO teacher (teacher_id) VALUES (%s)', (user_id,)) + + conn.commit() + cur.close() + conn.close() + return user_id + except Exception as e: + print(f"Грешка при регистрација: {e}") + return None + + @staticmethod + def get_user_by_id(user_id): + """Земи корисник + teacher_id ако е студент (LEFT JOIN со student во мали букви).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + SELECT u.*, s.teacher_id + FROM "User" u + LEFT JOIN student s ON u.user_id = s.student_id + WHERE u.user_id = %s + ''', (user_id,)) + row = cur.fetchone() + cur.close() + conn.close() + return dict(row) if row else None + except Exception as e: + print(f"Грешка: {e}") + return None + + @staticmethod + def get_all_teachers(): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + SELECT t.teacher_id, u.user_name, u.user_surname + FROM teacher t + JOIN "User" u ON t.teacher_id = u.user_id + ORDER BY u.user_name + ''') + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return [] + @staticmethod def get_all_users(): try: - conn = psycopg2.connect( - host='localhost', - port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute('SELECT user_id, user_name, user_surname, email, role FROM "User" ORDER BY user_name') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return None - + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute('SELECT user_id, user_name, user_surname, email, role FROM "User" ORDER BY user_name') + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return None + + # ---------- ELEMENTS ---------- @staticmethod def get_all_elements(): try: - conn = psycopg2.connect( - host='localhost', - port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' SELECT element_id, symbol, element_name, atomic_number, - atomic_weight, melting_point, boiling_point, hazard_type, description_element + atomic_weight, melting_point, boiling_point, hazard_type, description_element FROM elements ORDER BY atomic_number ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return None - - @staticmethod - def get_all_equipment(): - try: - conn = psycopg2.connect( - host='localhost', - port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT equipment_id, equipment_name, type, description, safety_info - FROM labequipment - ORDER BY equipment_name - ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return None - - @staticmethod - def get_equipment_usage_report(): - try: - conn = psycopg2.connect( - host='localhost', - port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - le.equipment_name, - COUNT(ele.experiment_id) AS usage_count - FROM ExperimentLabEquipment ele - RIGHT JOIN LabEquipment le ON ele.equipment_id = le.equipment_id - GROUP BY le.equipment_name, le.equipment_id - ORDER BY usage_count DESC, le.equipment_name - ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - @staticmethod - def authenticate_user(email, password): - """Автентикација на корисник""" - try: - conn = psycopg2.connect( - host='localhost', - port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute('SELECT user_id, user_name, user_surname, email, role, password FROM "User" WHERE email = %s', (email,)) - user = cursor.fetchone() - cursor.close() - conn.close() - return dict(user) if user else None - except Exception as e: - print(f"Грешка при автентикација: {e}") - return None - - @staticmethod - def register_user(name, surname, email, password_hash, role, teacher_id=None): - """Регистрирај нов корисник""" - try: - conn = psycopg2.connect( - host='localhost', - port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - - # Вметни во User табела - cursor.execute( - 'INSERT INTO "User" (user_name, user_surname, email, password, role) VALUES (%s, %s, %s, %s, %s) RETURNING user_id', - (name, surname, email, password_hash, role) - ) - user_id = cursor.fetchone()[0] - - # Ако е студент, вметни во Student табела - if role == 'student' and teacher_id: - cursor.execute('INSERT INTO Student (student_id, teacher_id) VALUES (%s, %s)', (user_id, teacher_id)) - # Ако е наставник, вметни во Teacher табела - elif role == 'teacher': - cursor.execute('INSERT INTO Teacher (teacher_id) VALUES (%s)', (user_id,)) - - conn.commit() - cursor.close() - conn.close() - return user_id - except Exception as e: - print(f"Грешка при регистрација: {e}") - return None - @staticmethod - def get_all_teachers(): - """Земи ги сите наставници""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT t.teacher_id, u.user_name, u.user_surname - FROM Teacher t - JOIN "User" u ON t.teacher_id = u.user_id - ORDER BY u.user_name - ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return None + @staticmethod def add_element(symbol, name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description, teacher_id): try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute(''' + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' INSERT INTO elements (symbol, element_name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description_element, teacher_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING element_id ''', (symbol, name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description, teacher_id)) - - element_id = cursor.fetchone()[0] - conn.commit() - cursor.close() - conn.close() - return element_id + el_id = cur.fetchone()['element_id'] + conn.commit() + cur.close() + conn.close() + return el_id except Exception as e: print(f"Грешка при додавање елемент: {e}") return None - - @staticmethod - def track_element_view(user_id, element_id): - """Track дека корисникот го погледнал елементот""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - # Провери дали веќе постои запис - cursor.execute('SELECT 1 FROM userviewselement WHERE user_id = %s AND element_id = %s', (user_id, element_id)) - if not cursor.fetchone(): - cursor.execute('INSERT INTO userviewselement (user_id, element_id) VALUES (%s, %s)', (user_id, element_id)) - conn.commit() - cursor.close() - conn.close() - except Exception as e: - print(f"Грешка при tracking: {e}") - - @staticmethod - def get_element_views_report(): - """Извештај за кој корисник кои елементи ги прегледал""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - u.user_name || ' ' || u.user_surname AS full_name, - u.role, - e.symbol, - e.element_name, - COUNT(*) as view_count - FROM userviewselement uve - JOIN "User" u ON uve.user_id = u.user_id - JOIN elements e ON uve.element_id = e.element_id - GROUP BY u.user_id, u.user_name, u.user_surname, u.role, e.element_id, e.symbol, e.element_name - ORDER BY u.user_name, e.symbol - ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - - @staticmethod - def get_user_activity_summary(): - """Сумарен извештај за активности по корисник""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - u.user_id, - u.user_name || ' ' || u.user_surname AS full_name, - u.role, - COUNT(DISTINCT uve.element_id) AS elements_viewed, - COUNT(DISTINCT uvl.equipment_id) AS equipment_viewed, - COUNT(DISTINCT upe.experiment_id) AS experiments_participated - FROM "User" u - LEFT JOIN userviewselement uve ON u.user_id = uve.user_id - LEFT JOIN userviewslabequipment uvl ON u.user_id = uvl.user_id - LEFT JOIN userparticipatesinexperiment upe ON u.user_id = upe.user_id - GROUP BY u.user_id, u.user_name, u.user_surname, u.role - ORDER BY u.user_name - ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - + + @staticmethod + def update_element(element_id, symbol, name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + UPDATE elements + SET symbol = %s, element_name = %s, atomic_number = %s, atomic_weight = %s, + melting_point = %s, boiling_point = %s, hazard_type = %s, description_element = %s + WHERE element_id = %s + ''', (symbol, name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description, element_id)) + conn.commit() + cur.close() + conn.close() + return True + except Exception as e: + print(f"Грешка: {e}") + return False + @staticmethod def get_element_by_id(element_id): - """Земи конкретен елемент по ID""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' SELECT e.*, u.user_name || ' ' || u.user_surname AS created_by FROM elements e @@ -361,202 +214,201 @@ WHERE e.element_id = %s ''', (element_id,)) - result = cursor.fetchone() - cursor.close() - conn.close() - return dict(result) if result else None - except Exception as e: - print(f"Грешка: {e}") - return None - @staticmethod - def get_my_students_activity(teacher_id): - """Извештај за активности на студентите на конкретен професор""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - s.student_id, - u.user_name || ' ' || u.user_surname AS full_name, - COUNT(DISTINCT ue.element_id) AS total_elements_viewed, - COUNT(DISTINCT ul.equipment_id) AS total_lab_equipment_viewed - FROM Student s - JOIN "User" u ON s.student_id = u.user_id - LEFT JOIN UserViewsElement ue ON s.student_id = ue.user_id - LEFT JOIN UserViewsLabEquipment ul ON s.student_id = ul.user_id - WHERE s.teacher_id = %s - GROUP BY s.student_id, full_name - ORDER BY total_elements_viewed DESC, total_lab_equipment_viewed DESC - ''', (teacher_id,)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - @staticmethod - def get_students_with_few_experiments(teacher_id, max_experiments=3): - """Студенти со помалку од одреден број експерименти""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - s.student_id, - u.user_name || ' ' || u.user_surname AS full_name, - COUNT(up.experiment_id) AS total_experiments - FROM Student s - JOIN "User" u ON s.student_id = u.user_id - LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id - WHERE s.teacher_id = %s - GROUP BY s.student_id, full_name - HAVING COUNT(up.experiment_id) < %s - ORDER BY total_experiments ASC - ''', (teacher_id, max_experiments)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - - @staticmethod - def get_teacher_statistics(): - """Статистики по професор""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - t.teacher_id, - u.user_name || ' ' || u.user_surname AS full_name, - COUNT(DISTINCT s.student_id) AS total_students, - COUNT(up.experiment_id) AS total_experiments, - ROUND( - COUNT(up.experiment_id) * 1.0 / NULLIF(COUNT(DISTINCT s.student_id), 0), 2 - ) AS avg_experiments_per_student - FROM Teacher t - JOIN "User" u ON t.teacher_id = u.user_id - LEFT JOIN Student s ON t.teacher_id = s.teacher_id - LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id - GROUP BY t.teacher_id, full_name - ORDER BY avg_experiments_per_student DESC + row = cur.fetchone() + cur.close() + conn.close() + return dict(row) if row else None + except Exception as e: + print(f"Грешка: {e}") + return None + + # ---------- LAB EQUIPMENT ---------- + @staticmethod + def get_all_equipment(): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + SELECT equipment_id, equipment_name, type, description, safety_info + FROM labequipment + ORDER BY equipment_name ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - - @staticmethod - def get_students_without_experiments(teacher_id): - """Студенти без експерименти""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - s.student_id, - u.user_name || ' ' || u.user_surname AS full_name - FROM Student s - JOIN "User" u ON s.student_id = u.user_id - LEFT JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id - WHERE s.teacher_id = %s - AND up.user_id IS NULL - ''', (teacher_id,)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - - @staticmethod - def get_students_experiments_detailed(teacher_id): - """Детален извештај за студенти и експерименти""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - s.student_id, - u.user_name || ' ' || u.user_surname AS full_name, - e.experiment_id, - e.result, - e.time_stamp AS participation_time - FROM Student s - JOIN "User" u ON s.student_id = u.user_id - JOIN UserParticipatesInExperiment up ON s.student_id = up.user_id - JOIN Experiment e ON up.experiment_id = e.experiment_id - WHERE s.teacher_id = %s - ORDER BY u.user_name, e.time_stamp DESC - ''', (teacher_id,)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return None + + @staticmethod + def add_lab_equipment(name, equipment_type, description, safety_info, teacher_id): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + INSERT INTO labequipment (equipment_name, type, description, safety_info, teacher_id) + VALUES (%s, %s, %s, %s, %s) + RETURNING equipment_id + ''', (name, equipment_type, description, safety_info, teacher_id)) + eq_id = cur.fetchone()['equipment_id'] + conn.commit() + cur.close() + conn.close() + return eq_id + except Exception as e: + print(f"Грешка при додавање опрема: {e}") + return None + + @staticmethod + def update_equipment(equipment_id, name, equipment_type, description, safety_info): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + UPDATE labequipment + SET equipment_name = %s, type = %s, description = %s, safety_info = %s + WHERE equipment_id = %s + ''', (name, equipment_type, description, safety_info, equipment_id)) + conn.commit() + cur.close() + conn.close() + return True + except Exception as e: + print(f"Грешка: {e}") + return False + + @staticmethod + def get_equipment_by_id(equipment_id): + rows = DatabaseManager.get_all_equipment() + if not rows: + return None + for item in rows: + if item['equipment_id'] == equipment_id: + return item + return None + + # ---------- REACTIONS ---------- + @staticmethod + def add_reaction(teacher_id, element1_id, element2_id, product, conditions): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + INSERT INTO reaction (teacher_id, element1_id, element2_id, product, conditions) + VALUES (%s, %s, %s, %s, %s) + RETURNING reaction_id + ''', (teacher_id, element1_id, element2_id, product, conditions)) + rid = cur.fetchone()['reaction_id'] + conn.commit() + cur.close() + conn.close() + return rid + except Exception as e: + print(f"Грешка: {e}") + return None + + @staticmethod + def update_reaction(reaction_id, element1_id, element2_id, product, conditions): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + UPDATE reaction + SET element1_id = %s, element2_id = %s, product = %s, conditions = %s + WHERE reaction_id = %s + ''', (element1_id, element2_id, product, conditions, reaction_id)) + conn.commit() + cur.close() + conn.close() + return True + except Exception as e: + print(f"Грешка: {e}") + return False + + @staticmethod + def delete_reaction(reaction_id): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute('DELETE FROM reaction WHERE reaction_id = %s', (reaction_id,)) + conn.commit() + cur.close() + conn.close() + return True + except Exception as e: + print(f"Грешка: {e}") + return False + + @staticmethod + def get_all_reactions(): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + SELECT r.*, + e1.symbol AS element1_symbol, e1.element_name AS element1_name, + e2.symbol AS element2_symbol, e2.element_name AS element2_name, + u.user_name || ' ' || u.user_surname AS created_by + FROM reaction r + JOIN elements e1 ON r.element1_id = e1.element_id + JOIN elements e2 ON r.element2_id = e2.element_id + JOIN "User" u ON r.teacher_id = u.user_id + ORDER BY r.reaction_id DESC + ''') + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return [] + + @staticmethod + def get_reaction_by_id(reaction_id): + rows = DatabaseManager.get_all_reactions() + if not rows: + return None + for r in rows: + if r['reaction_id'] == reaction_id: + return r + return None + + # ---------- EXPERIMENTS ---------- + @staticmethod + def insert_experiment(teacher_id, reaction_id, result, safety_warning): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + INSERT INTO experiment (teacher_id, reaction_id, result, safety_warning, time_stamp) + VALUES (%s, %s, %s, %s, CURRENT_TIMESTAMP) + RETURNING experiment_id + ''', (teacher_id, reaction_id, result, safety_warning)) + eid = cur.fetchone()['experiment_id'] + conn.commit() + cur.close() + conn.close() + return eid + except Exception as e: + print(f"Грешка: {e}") + return None + @staticmethod def get_all_experiments(): - """Земи сите експерименти""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - e.experiment_id, - e.result, - e.time_stamp, - e.safety_warning, - r.product, - r.conditions, - el1.symbol AS element1_symbol, - el1.element_name AS element1_name, - el2.symbol AS element2_symbol, - el2.element_name AS element2_name, - u.user_name || ' ' || u.user_surname AS created_by + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + SELECT e.experiment_id, + e.result, + e.time_stamp, + e.safety_warning, + r.product, + r.conditions, + el1.symbol AS element1_symbol, + el1.element_name AS element1_name, + el2.symbol AS element2_symbol, + el2.element_name AS element2_name, + u.user_name || ' ' || u.user_surname AS created_by FROM experiment e JOIN reaction r ON e.reaction_id = r.reaction_id @@ -566,8 +418,8 @@ ORDER BY e.time_stamp DESC ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result + rows = cur.fetchall() + cur.close() + conn.close() + return rows except Exception as e: print(f"Грешка: {e}") @@ -576,15 +428,8 @@ @staticmethod def get_experiment_equipment(experiment_id): - """Земи ја опремата за конкретен експеримент""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' SELECT le.equipment_name, le.type, le.safety_info FROM experimentlabequipment ele @@ -592,322 +437,40 @@ WHERE ele.experiment_id = %s ''', (experiment_id,)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - @staticmethod - def get_element_views_report(): - """Извештај за кој корисник кои елементи ги прегледал""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - u.user_name || ' ' || u.user_surname AS full_name, - u.role, - e.symbol, - e.element_name, - COUNT(*) as view_count - FROM userviewselement uve - JOIN "User" u ON uve.user_id = u.user_id - JOIN elements e ON uve.element_id = e.element_id - GROUP BY u.user_id, u.user_name, u.user_surname, u.role, e.element_id, e.symbol, e.element_name - ORDER BY u.user_name, e.symbol - ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - - @staticmethod - def add_lab_equipment(name, equipment_type, description, safety_info, teacher_id): - """Додај нова лабораториска опрема""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute(''' - INSERT INTO labequipment (equipment_name, type, description, safety_info, teacher_id) - VALUES (%s, %s, %s, %s, %s) - RETURNING equipment_id - ''', (name, equipment_type, description, safety_info, teacher_id)) - - equipment_id = cursor.fetchone()[0] - conn.commit() - cursor.close() - conn.close() - return equipment_id - except Exception as e: - print(f"Грешка при додавање опрема: {e}") - return None - - @staticmethod - def track_equipment_view(user_id, equipment_id): - """Track дека корисникот ја погледнал опремата""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute('SELECT 1 FROM userviewslabequipment WHERE user_id = %s AND equipment_id = %s', (user_id, equipment_id)) - if not cursor.fetchone(): - cursor.execute('INSERT INTO userviewslabequipment (user_id, equipment_id) VALUES (%s, %s)', (user_id, equipment_id)) - conn.commit() - cursor.close() - conn.close() - except Exception as e: - print(f"Грешка при tracking: {e}") - - @staticmethod - def update_element(element_id, symbol, name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description): - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute(''' - UPDATE elements - SET symbol = %s, element_name = %s, atomic_number = %s, atomic_weight = %s, - melting_point = %s, boiling_point = %s, hazard_type = %s, description_element = %s - WHERE element_id = %s - ''', (symbol, name, atomic_number, atomic_weight, melting_point, boiling_point, hazard_type, description, element_id)) - conn.commit() - cursor.close() - conn.close() - return True - except Exception as e: - print(f"Грешка: {e}") - return False - - @staticmethod - def get_equipment_by_id(equipment_id): - equipment_data = DatabaseManager.get_all_equipment() - for item in equipment_data: - if item['equipment_id'] == equipment_id: - return item - return None - - @staticmethod - def update_equipment(equipment_id, name, equipment_type, description, safety_info): - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute(''' - UPDATE labequipment - SET equipment_name = %s, type = %s, description = %s, safety_info = %s - WHERE equipment_id = %s - ''', (name, equipment_type, description, safety_info, equipment_id)) - conn.commit() - cursor.close() - conn.close() - return True - except Exception as e: - print(f"Грешка: {e}") - return False - - @staticmethod - def add_reaction(teacher_id, element1_id, element2_id, product, conditions): - """Додај нова реакција""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute(''' - INSERT INTO reaction (teacher_id, element1_id, element2_id, product, conditions) - VALUES (%s, %s, %s, %s, %s) - RETURNING reaction_id - ''', (teacher_id, element1_id, element2_id, product, conditions)) - - reaction_id = cursor.fetchone()[0] - conn.commit() - cursor.close() - conn.close() - return reaction_id - except Exception as e: - print(f"Грешка: {e}") - return None - - @staticmethod - def get_all_reactions(): - """Земи ги сите реакции""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT r.*, - e1.symbol as element1_symbol, e1.element_name as element1_name, - e2.symbol as element2_symbol, e2.element_name as element2_name, - u.user_name || ' ' || u.user_surname as created_by - FROM reaction r - JOIN elements e1 ON r.element1_id = e1.element_id - JOIN elements e2 ON r.element2_id = e2.element_id - JOIN "User" u ON r.teacher_id = u.user_id - ORDER BY r.reaction_id DESC - ''') - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - - @staticmethod - def get_reaction_by_id(reaction_id): - """Земи реакција по ID""" - reactions = DatabaseManager.get_all_reactions() - for reaction in reactions: - if reaction['reaction_id'] == reaction_id: - return reaction - return None - - @staticmethod - def update_reaction(reaction_id, element1_id, element2_id, product, conditions): - """Ажурирај реакција""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute(''' - UPDATE reaction - SET element1_id = %s, element2_id = %s, product = %s, conditions = %s - WHERE reaction_id = %s - ''', (element1_id, element2_id, product, conditions, reaction_id)) - conn.commit() - cursor.close() - conn.close() - return True - except Exception as e: - print(f"Грешка: {e}") - return False - - @staticmethod - def delete_reaction(reaction_id): - """Избриши реакција""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute('DELETE FROM reaction WHERE reaction_id = %s', (reaction_id,)) - conn.commit() - cursor.close() - conn.close() - return True - except Exception as e: - print(f"Грешка: {e}") - return False - - @staticmethod - def insert_experiment(teacher_id, reaction_id, result, safety_warning): - """Додај нов експеримент""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - cursor.execute(''' - INSERT INTO experiment (teacher_id, reaction_id, result, safety_warning, time_stamp) - VALUES (%s, %s, %s, %s, CURRENT_TIMESTAMP) - RETURNING experiment_id - ''', (teacher_id, reaction_id, result, safety_warning)) - - experiment_id = cursor.fetchone()[0] - conn.commit() - cursor.close() - conn.close() - return experiment_id - except Exception as e: - print(f"Грешка: {e}") - return None - + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return [] + + # ---------- PARTICIPATION / VIEWS ---------- @staticmethod def track_experiment_participation(user_id, experiment_id): try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - print(f"DEBUG: Додавам user_id={user_id}, experiment_id={experiment_id}") - cursor.execute(''' + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' INSERT INTO userparticipatesinexperiment (user_id, experiment_id) VALUES (%s, %s) ''', (user_id, experiment_id)) conn.commit() - cursor.close() - conn.close() - print("DEBUG: Успешно додадено учество") - except Exception as e: - print(f"DEBUG Грешка при додавање учество: {e}") + cur.close() + conn.close() + except Exception as e: + print(f"Грешка при додавање учество: {e}") @staticmethod def get_user_experiments(user_id): - """Земи експерименти на корисник""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT e.*, - r.product, - r.conditions, - el1.symbol as element1_symbol, el1.element_name as element1_name, - el2.symbol as element2_symbol, el2.element_name as element2_name, - up.participation_timestamp + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + SELECT e.*, + r.product, + r.conditions, + el1.symbol AS element1_symbol, el1.element_name AS element1_name, + el2.symbol AS element2_symbol, el2.element_name AS element2_name, + up.participation_timestamp FROM experiment e JOIN userparticipatesinexperiment up ON e.experiment_id = up.experiment_id @@ -918,121 +481,579 @@ ORDER BY up.participation_timestamp DESC ''', (user_id,)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - @staticmethod - def get_user_by_id(user_id): - """Земи корисник по ID""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT u.*, s.teacher_id - FROM "User" u - LEFT JOIN Student s ON u.user_id = s.student_id - WHERE u.user_id = %s - ''', (user_id,)) - result = cursor.fetchone() - cursor.close() - conn.close() - return dict(result) if result else None - except Exception as e: - print(f"Грешка: {e}") - return None - - # Додајте овие нови методи во DatabaseManager класата (database_manager.py) - - @staticmethod - def create_reaction_and_experiment(teacher_id, element1_id, element2_id, product, conditions, safety_warning=None): - """Креирај реакција и автоматски креирај експеримент (за професори)""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - - # Прво креирај реакција - cursor.execute(''' + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return [] + + @staticmethod + def track_element_view(user_id, element_id): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute('SELECT 1 FROM userviewselement WHERE user_id = %s AND element_id = %s', + (user_id, element_id)) + if not cur.fetchone(): + cur.execute('INSERT INTO userviewselement (user_id, element_id) VALUES (%s, %s)', + (user_id, element_id)) + conn.commit() + cur.close() + conn.close() + except Exception as e: + print(f"Грешка при tracking (element): {e}") + + @staticmethod + def track_equipment_view(user_id, equipment_id): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute('SELECT 1 FROM userviewslabequipment WHERE user_id = %s AND equipment_id = %s', + (user_id, equipment_id)) + if not cur.fetchone(): + cur.execute('INSERT INTO userviewslabequipment (user_id, equipment_id) VALUES (%s, %s)', + (user_id, equipment_id)) + conn.commit() + cur.close() + conn.close() + except Exception as e: + print(f"Грешка при tracking (equipment): {e}") + + # ---------- REPORT EXAMPLES ---------- + @staticmethod + def get_equipment_usage_report(): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(''' + SELECT le.equipment_name, + COUNT(ele.experiment_id) AS usage_count + FROM experimentlabequipment ele + RIGHT JOIN labequipment le ON ele.equipment_id = le.equipment_id + GROUP BY le.equipment_name, le.equipment_id + ORDER BY usage_count DESC, le.equipment_name + ''') + rows = cur.fetchall() + cur.close() + conn.close() + return rows + except Exception as e: + print(f"Грешка: {e}") + return [] + + # ---------- NEW: N:M helper ---------- + @staticmethod + def add_experiment_equipment(experiment_id, equipment_ids, conn=None, cur=None): + """Bulk insert во experimentlabequipment; ако нема листа, ништо не прави.""" + if not equipment_ids: + return + own = False + if conn is None or cur is None: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + own = True + + values_sql = ",".join(["(%s,%s)"] * len(equipment_ids)) + params = [] + for eq_id in equipment_ids: + params.extend([experiment_id, eq_id]) + + cur.execute(f''' + INSERT INTO experimentlabequipment (experiment_id, equipment_id) + VALUES {values_sql} + ON CONFLICT DO NOTHING + ''', params) + + if own: + conn.commit() + cur.close() + conn.close() + + # ---------- NEW: main transactional creator ---------- + @staticmethod + def create_reaction_and_experiment( + teacher_id, + element1_id, + element2_id, + product, + conditions, + experiment_result, # опис во проза (НЕ product) + safety_warning=None, + equipment_ids=None # листа од int + ): + """Креира Reaction → Experiment (+ опрема) во ЕДНА транскација.""" + conn = None + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + + # 1) Reaction + cur.execute(''' INSERT INTO reaction (teacher_id, element1_id, element2_id, product, conditions) VALUES (%s, %s, %s, %s, %s) RETURNING reaction_id ''', (teacher_id, element1_id, element2_id, product, conditions)) - - reaction_id = cursor.fetchone()[0] - - # Автоматски креирај експеримент за оваа реакција - cursor.execute(''' + reaction_id = cur.fetchone()['reaction_id'] + + # 2) Ако нема експериментски опис → генерирај од симболи + if not experiment_result: + # симболи + cur.execute('SELECT symbol FROM elements WHERE element_id = %s', (element1_id,)) + s1 = cur.fetchone()['symbol'] + cur.execute('SELECT symbol FROM elements WHERE element_id = %s', (element2_id,)) + s2 = cur.fetchone()['symbol'] + experiment_result = ( + f"Експеримент со {s1} и {s2} под услови: {conditions or 'стандардни'}. " + f"Очекуван производ: {product or 'непознат'}." + ) + + # 3) Experiment + cur.execute(''' INSERT INTO experiment (teacher_id, reaction_id, result, safety_warning, time_stamp) VALUES (%s, %s, %s, %s, CURRENT_TIMESTAMP) RETURNING experiment_id - ''', (teacher_id, reaction_id, - f'Експеримент за реакција: {product}', - safety_warning or 'Стандардни безбедносни мерки')) - - experiment_id = cursor.fetchone()[0] - - conn.commit() - cursor.close() - conn.close() - - return {'reaction_id': reaction_id, 'experiment_id': experiment_id} - except Exception as e: - print(f"Грешка при креирање реакција и експеримент: {e}") - return None + ''', (teacher_id, reaction_id, experiment_result, safety_warning)) + experiment_id = cur.fetchone()['experiment_id'] + + # 4) Equipment links + if equipment_ids: + DatabaseManager.add_experiment_equipment( + experiment_id=experiment_id, + equipment_ids=equipment_ids, + conn=conn, cur=cur + ) + + conn.commit() + cur.close() + conn.close() + return {"reaction_id": reaction_id, "experiment_id": experiment_id} + + except Exception as e: + if conn: + conn.rollback() + conn.close() + print(f"Грешка при креирање реакција+експеримент: {e}") + return None +# --- ДОДАДИ ВО КЛАСАТА DatabaseManager --- @staticmethod def get_experiment_by_reaction(reaction_id): - """Најди експеримент за дадена реакција""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT * FROM experiment + """Врати го најновиот експеримент за дадена реакција (или None).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT * + FROM experiment WHERE reaction_id = %s ORDER BY time_stamp DESC LIMIT 1 - ''', (reaction_id,)) - result = cursor.fetchone() - cursor.close() - conn.close() - return dict(result) if result else None - except Exception as e: - print(f"Грешка: {e}") - return None + """, (reaction_id,)) + row = cur.fetchone() + cur.close(); conn.close() + return dict(row) if row else None + except Exception as e: + print(f"Грешка get_experiment_by_reaction: {e}") + return None + + + @staticmethod + def add_experiment_equipment(experiment_id, equipment_ids, conn=None, cur=None): + """Додај повеќе записи во N:M табелата experimentlabequipment.""" + own_conn = own_cur = False + try: + if conn is None or cur is None: + conn = DatabaseManager.get_connection(); own_conn = True + cur = conn.cursor(); own_cur = True + + if equipment_ids: + cur.executemany( + "INSERT INTO experimentlabequipment (experiment_id, equipment_id) VALUES (%s, %s)", + [(experiment_id, eq_id) for eq_id in equipment_ids] + ) + + if own_conn: + conn.commit() + return True + except Exception as e: + if own_conn and conn: + conn.rollback() + print(f"Грешка add_experiment_equipment: {e}") + return False + finally: + if own_cur: + cur.close() + if own_conn and conn: + conn.close() + + + @staticmethod + def create_reaction_and_experiment( + teacher_id, + element1_id, + element2_id, + product, + conditions, + experiment_result=None, + safety_warning=None, + equipment_ids=None + ): + """ + Креирај реакција + експеримент во една транскација. + experiment_result е опис во проза (не е исто со product). + equipment_ids е листа од INT (опционално). + """ + conn = None + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + + # 1) Reaction + cur.execute(""" + INSERT INTO reaction (teacher_id, element1_id, element2_id, product, conditions) + VALUES (%s, %s, %s, %s, %s) + RETURNING reaction_id + """, (teacher_id, element1_id, element2_id, product, conditions)) + reaction_id = cur.fetchone()[0] + + # 2) Experiment + if not experiment_result: + experiment_result = f"Експеримент за: {product or 'непознат производ'}" + + cur.execute(""" + INSERT INTO experiment (teacher_id, reaction_id, result, safety_warning, time_stamp) + VALUES (%s, %s, %s, %s, CURRENT_TIMESTAMP) + RETURNING experiment_id + """, (teacher_id, reaction_id, experiment_result, safety_warning or 'Стандардни безбедносни мерки')) + experiment_id = cur.fetchone()[0] + + # 3) Опрема (ако има) + if equipment_ids: + cur.executemany( + "INSERT INTO experimentlabequipment (experiment_id, equipment_id) VALUES (%s, %s)", + [(experiment_id, eq_id) for eq_id in equipment_ids] + ) + + conn.commit() + cur.close(); conn.close() + return {'reaction_id': reaction_id, 'experiment_id': experiment_id} + + except Exception as e: + if conn: conn.rollback() + print(f"Грешка create_reaction_and_experiment: {e}") + return None + finally: + if conn: + try: conn.close() + except: pass + + @staticmethod + def get_student_participation_experiments(student_id): + """Експерименти во кои студентот учествувал (со детали за реакцијата).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + e.experiment_id, + e.result, + e.safety_warning, + e.time_stamp, + r.product, + r.conditions, + el1.symbol AS element1_symbol, + el1.element_name AS element1_name, + el2.symbol AS element2_symbol, + el2.element_name AS element2_name + FROM userparticipatesinexperiment up + JOIN experiment e ON up.experiment_id = e.experiment_id + JOIN reaction r ON e.reaction_id = r.reaction_id + JOIN elements el1 ON r.element1_id = el1.element_id + JOIN elements el2 ON r.element2_id = el2.element_id + WHERE up.user_id = %s + ORDER BY e.time_stamp DESC + """, (student_id,)) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_student_participation_experiments: {e}") + return [] + @staticmethod + def get_student_statistics(student_id): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + (SELECT COUNT(*) FROM userparticipatesinexperiment WHERE user_id = %s) AS experiment_count, + (SELECT COUNT(*) FROM userviewselement WHERE user_id = %s) AS element_count, + (SELECT COUNT(*) FROM userviewslabequipment WHERE user_id = %s) AS equipment_count, + ( + SELECT COUNT(DISTINCT e.reaction_id) + FROM userparticipatesinexperiment up + JOIN experiment e ON up.experiment_id = e.experiment_id + WHERE up.user_id = %s + ) AS reaction_count + """, (student_id, student_id, student_id, student_id)) + row = cur.fetchone() + cur.close(); conn.close() + return dict(row) if row else { + 'experiment_count': 0, 'element_count': 0, 'equipment_count': 0, 'reaction_count': 0 + } + except Exception as e: + print(f"Грешка get_student_statistics: {e}") + return {'experiment_count': 0, 'element_count': 0, 'equipment_count': 0, 'reaction_count': 0} + + @staticmethod + def get_teacher_dashboard_statistics(teacher_id): + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + + def get_c(): + row = cur.fetchone() + if row is None: + return 0 + try: + return int(row['c']) # RealDictRow + except Exception: + return int(row[0]) # tuple fallback + + # 1) број на студенти + cur.execute(""" + SELECT COUNT(*) AS c + FROM student + WHERE teacher_id = %s + """, (teacher_id,)) + student_count = get_c() + + # 2) број на реакции на овој професор + cur.execute(""" + SELECT COUNT(*) AS c + FROM reaction + WHERE teacher_id = %s + """, (teacher_id,)) + reaction_count = get_c() + + # 3) број на експерименти креирани од овој професор + cur.execute(""" + SELECT COUNT(*) AS c + FROM experiment + WHERE teacher_id = %s + """, (teacher_id,)) + experiment_count = get_c() + + # 4) активности денес — прво пробај participation_timestamp; ако ја нема колоната, падни на e.time_stamp + try: + cur.execute(""" + SELECT COUNT(*) AS c + FROM userparticipatesinexperiment up + JOIN student s ON up.user_id = s.student_id + WHERE s.teacher_id = %s + AND up.participation_timestamp::date = CURRENT_DATE + """, (teacher_id,)) + activity_count = get_c() + except Exception: + cur.execute(""" + SELECT COUNT(*) AS c + FROM userparticipatesinexperiment up + JOIN student s ON up.user_id = s.student_id + JOIN experiment e ON up.experiment_id = e.experiment_id + WHERE s.teacher_id = %s + AND e.time_stamp::date = CURRENT_DATE + """, (teacher_id,)) + activity_count = get_c() + + cur.close(); conn.close() + return { + 'student_count': student_count, + 'reaction_count': reaction_count, + 'experiment_count': experiment_count, + 'activity_count': activity_count + } + except Exception as e: + print("Грешка get_teacher_dashboard_statistics:", e) + try: + cur.close(); conn.close() + except Exception: + pass + return {'student_count': 0, 'reaction_count': 0, 'experiment_count': 0, 'activity_count': 0} + + @staticmethod + def get_my_students_activity(teacher_id): + """Активност на студентите на конкретен професор: прегледани елементи/опрема.""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + s.student_id, + (u.user_name || ' ' || u.user_surname) AS full_name, + COALESCE(COUNT(DISTINCT uve.element_id), 0) AS total_elements_viewed, + COALESCE(COUNT(DISTINCT uvl.equipment_id), 0) AS total_lab_equipment_viewed + FROM student s + JOIN "User" u ON s.student_id = u.user_id + LEFT JOIN userviewselement uve ON s.student_id = uve.user_id + LEFT JOIN userviewslabequipment uvl ON s.student_id = uvl.user_id + WHERE s.teacher_id = %s + GROUP BY s.student_id, full_name + ORDER BY total_elements_viewed DESC, total_lab_equipment_viewed DESC + """, (teacher_id,)) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_my_students_activity: {e}") + return [] + + @staticmethod + def get_students_without_experiments(teacher_id): + """Студенти на конкретен професор кои немаат ниту еден експеримент.""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + s.student_id, + (u.user_name || ' ' || u.user_surname) AS full_name + FROM student s + JOIN "User" u ON s.student_id = u.user_id + LEFT JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + WHERE s.teacher_id = %s + AND up.user_id IS NULL + ORDER BY full_name + """, (teacher_id,)) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_students_without_experiments: {e}") + return [] + + + @staticmethod + def get_students_with_few_experiments(teacher_id, max_experiments=3): + """Студенти со помалку од max_experiments експерименти.""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + s.student_id, + (u.user_name || ' ' || u.user_surname) AS full_name, + COUNT(up.experiment_id) AS total_experiments + FROM student s + JOIN "User" u ON s.student_id = u.user_id + LEFT JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + WHERE s.teacher_id = %s + GROUP BY s.student_id, full_name + HAVING COUNT(up.experiment_id) < %s + ORDER BY total_experiments ASC, full_name + """, (teacher_id, max_experiments)) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_students_with_few_experiments: {e}") + return [] + + + @staticmethod + def get_students_experiments_detailed(teacher_id): + """Детален извештај за студентите на професорот и нивните експерименти.""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + s.student_id, + (u.user_name || ' ' || u.user_surname) AS full_name, + e.experiment_id, + e.result, + e.time_stamp AS participation_time + FROM student s + JOIN "User" u ON s.student_id = u.user_id + JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + JOIN experiment e ON up.experiment_id = e.experiment_id + WHERE s.teacher_id = %s + ORDER BY u.user_name, e.time_stamp DESC + """, (teacher_id,)) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_students_experiments_detailed: {e}") + return [] + + + @staticmethod + def get_element_views_report(): + """Кој корисник кои елементи ги прегледал (агрегиран извештај).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + (u.user_name || ' ' || u.user_surname) AS full_name, + u.role, + e.symbol, + e.element_name, + COUNT(*) AS view_count + FROM userviewselement uve + JOIN "User" u ON uve.user_id = u.user_id + JOIN elements e ON uve.element_id = e.element_id + GROUP BY u.user_id, u.user_name, u.user_surname, u.role, + e.element_id, e.symbol, e.element_name + ORDER BY u.user_name, e.symbol + """) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_element_views_report: {e}") + return [] + + + @staticmethod + def get_teacher_statistics(): + """Статистики за сите професори (вкупно студенти/експерименти и просек по студент).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" + SELECT + t.teacher_id, + (u.user_name || ' ' || u.user_surname) AS full_name, + COUNT(DISTINCT s.student_id) AS total_students, + COUNT(up.experiment_id) AS total_experiments, + ROUND( + COUNT(up.experiment_id) * 1.0 / NULLIF(COUNT(DISTINCT s.student_id), 0), 2 + ) AS avg_experiments_per_student + FROM teacher t + JOIN "User" u ON t.teacher_id = u.user_id + LEFT JOIN student s ON t.teacher_id = s.teacher_id + LEFT JOIN userparticipatesinexperiment up ON s.student_id = up.user_id + GROUP BY t.teacher_id, full_name + ORDER BY avg_experiments_per_student DESC, full_name + """) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_teacher_statistics: {e}") + return [] + @staticmethod def get_students_experiments_for_teacher(teacher_id): - """Земи експерименти на студенти за конкретен професор""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' + """Експерименти изведени од студентите на даден професор (за извештаи).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" SELECT - u.user_name || ' ' || u.user_surname AS student_name, + (u.user_name || ' ' || u.user_surname) AS student_name, s.student_id, e.experiment_id, @@ -1040,338 +1061,51 @@ e.time_stamp, r.product, - el1.symbol as element1_symbol, - el2.symbol as element2_symbol, - up.participation_timestamp as participation_date + el1.symbol AS element1_symbol, + el2.symbol AS element2_symbol, + up.participation_timestamp AS participation_date FROM student s JOIN "User" u ON s.student_id = u.user_id JOIN userparticipatesinexperiment up ON s.student_id = up.user_id JOIN experiment e ON up.experiment_id = e.experiment_id - JOIN reaction r ON e.reaction_id = r.reaction_id + JOIN reaction r ON e.reaction_id = r.reaction_id JOIN elements el1 ON r.element1_id = el1.element_id JOIN elements el2 ON r.element2_id = el2.element_id WHERE s.teacher_id = %s - ORDER BY up.participation_timestamp DESC - ''', (teacher_id,)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - - @staticmethod - def get_student_statistics(student_id): - """Земи статистики за студент""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - - # Статистики за студентот - cursor.execute(''' + ORDER BY up.participation_timestamp DESC, student_name + """, (teacher_id,)) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_students_experiments_for_teacher: {e}") + return [] + + + @staticmethod + def get_user_activity_summary(): + """Сумарен извештај за активности по корисник (елементи/опрема/експерименти).""" + try: + conn = DatabaseManager.get_connection() + cur = conn.cursor() + cur.execute(""" SELECT - (SELECT COUNT(*) FROM userparticipatesinexperiment WHERE user_id = %s) as experiment_count, - (SELECT COUNT(*) FROM userviewselement WHERE user_id = %s) as element_count, - (SELECT COUNT(*) FROM userviewslabequipment WHERE user_id = %s) as equipment_count, - (SELECT COUNT(DISTINCT r.reaction_id) - FROM userparticipatesinexperiment up - JOIN experiment e ON up.experiment_id = e.experiment_id - JOIN reaction r ON e.reaction_id = r.reaction_id - WHERE up.user_id = %s) as reaction_count - ''', (student_id, student_id, student_id, student_id)) - - result = cursor.fetchone() - cursor.close() - conn.close() - return dict(result) if result else {'experiment_count': 0, 'element_count': 0, 'equipment_count': 0, 'reaction_count': 0} - except Exception as e: - print(f"Грешка: {e}") - return {'experiment_count': 0, 'element_count': 0, 'equipment_count': 0, 'reaction_count': 0} - - @staticmethod - def get_teacher_dashboard_statistics(teacher_id): - """Земи статистики за професорски dashboard""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - - # Број на студенти - cursor.execute('SELECT COUNT(*) as count FROM student WHERE teacher_id = %s', (teacher_id,)) - student_count = cursor.fetchone()['count'] - - # Број на креирани реакции - cursor.execute('SELECT COUNT(*) as count FROM reaction WHERE teacher_id = %s', (teacher_id,)) - reaction_count = cursor.fetchone()['count'] - - # Број на експерименти - cursor.execute('SELECT COUNT(*) as count FROM experiment WHERE teacher_id = %s', (teacher_id,)) - experiment_count = cursor.fetchone()['count'] - - # Активности денес (експерименти од студенти денес) - cursor.execute(''' - SELECT COUNT(*) as count - FROM userparticipatesinexperiment up - JOIN student s ON up.user_id = s.student_id - WHERE s.teacher_id = %s - AND DATE(up.participation_timestamp) = CURRENT_DATE - ''', (teacher_id,)) - activity_today = cursor.fetchone()['count'] - - cursor.close() - conn.close() - - return { - 'student_count': student_count, - 'reaction_count': reaction_count, - 'experiment_count': experiment_count, - 'activity_count': activity_today - } - except Exception as e: - print(f"Грешка: {e}") - return { - 'student_count': 0, - 'reaction_count': 0, - 'experiment_count': 0, - 'activity_count': 0 - } - - # Додајте ги овие методи во вашиот DatabaseManager класа (database_manager.py) - - @staticmethod - def create_reaction_and_experiment(teacher_id, element1_id, element2_id, product, conditions, safety_warning=None): - """Креирај реакција и автоматски креирај експеримент""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332' - ) - cursor = conn.cursor() - - # Прво креирај реакција - cursor.execute(''' - INSERT INTO reaction (teacher_id, element1_id, element2_id, product, conditions) - VALUES (%s, %s, %s, %s, %s) - RETURNING reaction_id - ''', (teacher_id, element1_id, element2_id, product, conditions)) - - reaction_id = cursor.fetchone()[0] - - # Автоматски креирај експеримент за оваа реакција - cursor.execute(''' - INSERT INTO experiment (teacher_id, reaction_id, result, safety_warning, time_stamp) - VALUES (%s, %s, %s, %s, CURRENT_TIMESTAMP) - RETURNING experiment_id - ''', (teacher_id, reaction_id, - f'Експеримент за: {product}', - safety_warning or 'Стандардни безбедносни мерки')) - - experiment_id = cursor.fetchone()[0] - - conn.commit() - cursor.close() - conn.close() - - print(f"Успешно креирани: Реакција #{reaction_id} и Експеримент #{experiment_id}") - return {'reaction_id': reaction_id, 'experiment_id': experiment_id} - except Exception as e: - print(f"Грешка при креирање реакција и експеримент: {e}") - if conn: - conn.rollback() - conn.close() - return None - - @staticmethod - def get_experiment_by_reaction(reaction_id): - """Најди експеримент за дадена реакција""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT * FROM experiment - WHERE reaction_id = %s - ORDER BY time_stamp DESC - LIMIT 1 - ''', (reaction_id,)) - result = cursor.fetchone() - cursor.close() - conn.close() - return dict(result) if result else None - except Exception as e: - print(f"Грешка при барање експеримент: {e}") - return None - - @staticmethod - def get_student_statistics(student_id): - """Земи статистики за студент""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - - cursor.execute(''' - SELECT - (SELECT COUNT(*) FROM userparticipatesinexperiment WHERE user_id = %s) as experiment_count, - (SELECT COUNT(*) FROM userviewselement WHERE user_id = %s) as element_count, - (SELECT COUNT(*) FROM userviewslabequipment WHERE user_id = %s) as equipment_count, - (SELECT COUNT(DISTINCT r.reaction_id) - FROM userparticipatesinexperiment up - JOIN experiment e ON up.experiment_id = e.experiment_id - JOIN reaction r ON e.reaction_id = r.reaction_id - WHERE up.user_id = %s) as reaction_count - ''', (student_id, student_id, student_id, student_id)) - - result = cursor.fetchone() - cursor.close() - conn.close() - return dict(result) if result else {'experiment_count': 0, 'element_count': 0, 'equipment_count': 0, 'reaction_count': 0} - except Exception as e: - print(f"Грешка при статистики за студент: {e}") - return {'experiment_count': 0, 'element_count': 0, 'equipment_count': 0, 'reaction_count': 0} - - @staticmethod - def get_teacher_dashboard_statistics(teacher_id): - """Земи статистики за професорски dashboard""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - - cursor.execute('SELECT COUNT(*) as count FROM student WHERE teacher_id = %s', (teacher_id,)) - student_count = cursor.fetchone()['count'] - - cursor.execute('SELECT COUNT(*) as count FROM reaction WHERE teacher_id = %s', (teacher_id,)) - reaction_count = cursor.fetchone()['count'] - - cursor.execute('SELECT COUNT(*) as count FROM experiment WHERE teacher_id = %s', (teacher_id,)) - experiment_count = cursor.fetchone()['count'] - - cursor.execute(''' - SELECT COUNT(*) as count - FROM userparticipatesinexperiment up - JOIN student s ON up.user_id = s.student_id - WHERE s.teacher_id = %s - AND DATE(up.participation_timestamp) = CURRENT_DATE - ''', (teacher_id,)) - activity_today = cursor.fetchone()['count'] - - cursor.close() - conn.close() - - return { - 'student_count': student_count, - 'reaction_count': reaction_count, - 'experiment_count': experiment_count, - 'activity_count': activity_today - } - except Exception as e: - print(f"Грешка при dashboard статистики: {e}") - return { - 'student_count': 0, - 'reaction_count': 0, - 'experiment_count': 0, - 'activity_count': 0 - } - - @staticmethod - def get_students_experiments_for_teacher(teacher_id): - """Земи експерименти на студенти за конкретен професор""" - try: - conn = psycopg2.connect( - host='localhost', port=9999, - database='db_202425z_va_prj_simlab25', - user='db_202425z_va_prj_simlab25_owner', - password='c9e5ebb7d332', - cursor_factory=RealDictCursor - ) - cursor = conn.cursor() - cursor.execute(''' - SELECT - u.user_name || ' ' || u.user_surname AS student_name, - s.student_id, - e.experiment_id, - e.result, - e.time_stamp, - r.product, - el1.symbol as element1_symbol, - el2.symbol as element2_symbol, - up.participation_timestamp as participation_date - FROM student s - JOIN "User" u ON s.student_id = u.user_id - JOIN userparticipatesinexperiment up ON s.student_id = up.user_id - JOIN experiment e ON up.experiment_id = e.experiment_id - JOIN reaction r ON e.reaction_id = r.reaction_id - JOIN elements el1 ON r.element1_id = el1.element_id - JOIN elements el2 ON r.element2_id = el2.element_id - WHERE s.teacher_id = %s - ORDER BY up.participation_timestamp DESC - ''', (teacher_id,)) - result = cursor.fetchall() - cursor.close() - conn.close() - return result - except Exception as e: - print(f"Грешка: {e}") - return [] - @staticmethod - def get_student_participation_experiments(student_id): - query = ''' - SELECT ep.user_id, - r.product, - r.conditions, - el1.symbol AS element1_symbol, - el2.symbol AS element2_symbol, - el1.element_name AS element1_name, - el2.element_name AS element2_name, - e.result, - e.safety_warning, - e.time_stamp, - e.experiment_id - FROM userparticipatesinexperiment ep - JOIN experiment e ON ep.experiment_id = e.experiment_id - JOIN reaction r ON e.reaction_id = r.reaction_id - JOIN elements el1 ON r.element1_id = el1.element_id - JOIN elements el2 ON r.element2_id = el2.element_id - WHERE ep.user_id = %s - ORDER BY e.time_stamp DESC - ''' - return DatabaseManager.execute_query(query, (student_id,)) - - @staticmethod - def execute_query(query, params=None): - conn = DatabaseManager.get_connection() - cur = conn.cursor() - cur.execute(query, params or ()) - result = cur.fetchall() - cur.close() - conn.close() - return result + u.user_id, + (u.user_name || ' ' || u.user_surname) AS full_name, + u.role, + COUNT(DISTINCT uve.element_id) AS elements_viewed, + COUNT(DISTINCT uvl.equipment_id) AS equipment_viewed, + COUNT(DISTINCT upe.experiment_id) AS experiments_participated + FROM "User" u + LEFT JOIN userviewselement uve ON u.user_id = uve.user_id + LEFT JOIN userviewslabequipment uvl ON u.user_id = uvl.user_id + LEFT JOIN userparticipatesinexperiment upe ON u.user_id = upe.user_id + GROUP BY u.user_id, full_name, u.role + ORDER BY full_name + """) + rows = cur.fetchall() + cur.close(); conn.close() + return rows + except Exception as e: + print(f"Грешка get_user_activity_summary: {e}") + return [] +