Spaces:
Sleeping
Sleeping
Download app/database.py from teamasahkeren/BroDi_Model: direct link, hf CLI and curl.
- Browser
- Download file 11.3 kB
-
https://huggingface.co/spaces/teamasahkeren/BroDi_Model/resolve/main/app/database.py
- Command line
-
hf download hf://spaces/teamasahkeren/BroDi_Model/app/database.py
-
curl -L -o database.py https://huggingface.co/spaces/teamasahkeren/BroDi_Model/resolve/main/app/database.py
11.3 kB
| import mysql.connector | |
| import pandas as pd | |
| from app.config import DB_CONFIG | |
| def get_db_connection(): | |
| try: | |
| return mysql.connector.connect( | |
| **DB_CONFIG, | |
| ) | |
| except Exception as e: | |
| print(f"[DB Error] Gagal konek ke MySQL: {e}") | |
| return None | |
| # FUNGSI UNTUK CHATBOT & PROGRESS | |
| def fetch_student_progress(email): | |
| conn = get_db_connection() | |
| if not conn: return None | |
| try: | |
| # Ambil data progress user | |
| query = "SELECT * FROM student_progress WHERE email = %s" | |
| df = pd.read_sql(query, conn, params=(email,)) | |
| return df.to_dict(orient='records')[0] if not df.empty else None | |
| except Exception as e: | |
| print(f"[DB Error] Fetch Progress: {e}") | |
| return None | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| def fetch_current_tutorial_title(course_name, tutorial_order): | |
| """Mencari judul materi ke-X dari suatu course""" | |
| conn = get_db_connection() | |
| if not conn: return "Materi tidak ditemukan" | |
| try: | |
| query = """ | |
| SELECT t.tutorial_title | |
| FROM tutorials t | |
| JOIN courses c ON t.course_id = c.course_id | |
| WHERE c.course_name = %s | |
| ORDER BY t.tutorial_id ASC | |
| LIMIT 1 OFFSET %s | |
| """ | |
| # Offset = tutorial_order - 1 (karena index mulai 0) | |
| offset = max(0, int(tutorial_order) - 1) | |
| cursor = conn.cursor() | |
| cursor.execute(query, (course_name, offset)) | |
| result = cursor.fetchone() | |
| return result[0] if result else "Akhir Pembelajaran" | |
| except Exception as e: | |
| print(f"[DB Error] Fetch Tutorial: {e}") | |
| return "Info materi tidak tersedia" | |
| finally: | |
| if conn and conn.is_connected(): | |
| cursor.close() | |
| conn.close() | |
| def fetch_all_lp_course_names(): | |
| """Mengambil daftar semua nama kursus yang ada detailnya di tabel lp_answers""" | |
| conn = get_db_connection() | |
| if not conn: return [] | |
| try: | |
| query = "SELECT name FROM lp_answers" | |
| df = pd.read_sql(query, conn) | |
| return df['name'].tolist() | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| def fetch_lp_course_details_by_name(exact_name): | |
| """Mengambil detail lengkap kursus berdasarkan nama persis""" | |
| conn = get_db_connection() | |
| if not conn: return None | |
| try: | |
| query = "SELECT * FROM lp_answers WHERE name LIKE %s LIMIT 1" | |
| df = pd.read_sql(query, conn, params=(exact_name,)) | |
| return df.to_dict(orient='records')[0] if not df.empty else None | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| def fetch_all_learning_paths(): | |
| conn = get_db_connection() | |
| if not conn: return [] | |
| try: | |
| query = "SELECT learning_path_name FROM learning_paths" | |
| df = pd.read_sql(query, conn) | |
| return df['learning_path_name'].tolist() if not df.empty else [] | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| def fetch_courses_by_path_dynamic(path_name): | |
| conn = get_db_connection() | |
| if not conn: return [] | |
| try: | |
| query = """ | |
| SELECT c.course_name, c.course_level_str | |
| FROM courses c | |
| JOIN learning_paths lp ON c.learning_path_id = lp.learning_path_id | |
| WHERE lp.learning_path_name LIKE %s | |
| """ | |
| df = pd.read_sql(query, conn, params=(f"%{path_name}%",)) | |
| return df.to_dict(orient='records') | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| def fetch_all_courses(limit=40): | |
| conn = get_db_connection() | |
| if not conn: return [] | |
| try: | |
| query = """ | |
| SELECT c.course_name, lp.learning_path_name | |
| FROM courses c | |
| JOIN learning_paths lp ON c.learning_path_id = lp.learning_path_id | |
| LIMIT %s | |
| """ | |
| df = pd.read_sql(query, conn, params=(limit,)) | |
| return df.to_dict(orient='records') | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| # FUNGSI UNTUK ASSESSMENT & ROADMAP | |
| def fetch_questions_by_learning_path(lp_name): | |
| conn = get_db_connection() | |
| if not conn: return [] | |
| try: | |
| query = """ | |
| SELECT id, subskill, subskill_level, difficulty, question_desc, | |
| option_1, option_2, option_3, option_4, correct_answer | |
| FROM tech_questions | |
| WHERE learning_path LIKE %s | |
| """ | |
| df = pd.read_sql(query, conn, params=(f"%{lp_name}%",)) | |
| return df.to_dict(orient='records') | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| def fetch_specific_courses(path_name): | |
| conn = get_db_connection() | |
| if not conn: return [] | |
| try: | |
| query = """ | |
| SELECT c.course_name, c.course_level_str | |
| FROM courses c | |
| JOIN learning_paths lp ON c.learning_path_id = lp.learning_path_id | |
| WHERE lp.learning_path_name = %s | |
| """ | |
| df = pd.read_sql(query, conn, params=(path_name,)) | |
| return df.to_dict(orient='records') | |
| finally: | |
| conn.close() | |
| def fetch_questions_by_subskills(subskill_list): | |
| """ | |
| Mengambil soal berdasarkan DAFTAR SUBSKILL, bukan nama Learning Path. | |
| Ini solusi agar soal 'shared' (seperti Python) tetap muncul di GCP. | |
| """ | |
| conn = get_db_connection() | |
| if not conn: return [] | |
| if not subskill_list: | |
| return [] | |
| try: | |
| format_strings = ','.join(['%s'] * len(subskill_list)) | |
| query = f""" | |
| SELECT id, subskill, subskill_level, difficulty, question_desc, | |
| option_1, option_2, option_3, option_4, correct_answer | |
| FROM tech_questions | |
| WHERE subskill IN ({format_strings}) | |
| """ | |
| cursor = conn.cursor(dictionary=True) | |
| cursor.execute(query, tuple(subskill_list)) | |
| results = cursor.fetchall() | |
| return results | |
| except Exception as e: | |
| print(f"[DB Error] Fetch Questions by Subskill: {e}") | |
| return [] | |
| finally: | |
| if conn and conn.is_connected(): | |
| cursor.close() | |
| conn.close() | |
| def save_generated_roadmap(email, lp_name, roadmap_items, overall_level): | |
| conn = get_db_connection() | |
| if not conn: return None | |
| cursor = conn.cursor() | |
| try: | |
| # Hapus roadmap lama jika ada (untuk learning path yang sama) | |
| cursor.execute("DELETE FROM user_roadmaps WHERE user_email = %s AND learning_path_name = %s", (email, lp_name)) | |
| # Simpan Header | |
| cursor.execute( | |
| "INSERT INTO user_roadmaps (user_email, learning_path_name, status, overall_level, created_at, updated_at) VALUES (%s, %s, 'active', %s, NOW(), NOW())", | |
| (email, lp_name, overall_level) | |
| ) | |
| roadmap_id = cursor.lastrowid | |
| # Simpan Items | |
| for item in roadmap_items: | |
| cursor.execute( | |
| """INSERT INTO user_roadmap_items | |
| (user_roadmap_id, subskill_name, `order`, status, skill_level, created_at, updated_at) | |
| VALUES (%s, %s, %s, %s, %s, NOW(), NOW())""", | |
| (roadmap_id, item['subskill'], item['order'], item['status'], item['level_result']) | |
| ) | |
| conn.commit() | |
| return roadmap_id | |
| except Exception as e: | |
| conn.rollback() | |
| print(f"Error saving roadmap: {e}") | |
| return None | |
| finally: | |
| if conn and conn.is_connected(): | |
| cursor.close() | |
| conn.close() | |
| def fetch_weekly_progress(email): | |
| """Mencari skill yang diselesaikan dalam 7 hari terakhir""" | |
| conn = get_db_connection() | |
| if not conn: return None | |
| try: | |
| # Query: Cari item milik user yang completed & diupdate 7 hari terakhir | |
| query = """ | |
| SELECT r.learning_path_name, i.subskill_name, i.updated_at | |
| FROM user_roadmap_items i | |
| JOIN user_roadmaps r ON i.user_roadmap_id = r.id | |
| WHERE r.user_email = %s | |
| AND i.status = 'completed' | |
| AND i.updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) | |
| ORDER BY i.updated_at DESC | |
| """ | |
| df = pd.read_sql(query, conn, params=(email,)) | |
| if df.empty: | |
| return None | |
| return df.to_dict(orient='records') | |
| except Exception as e: | |
| print(f"[DB Error] Fetch Weekly: {e}") | |
| return None | |
| finally: | |
| if conn and conn.is_connected(): conn.close() | |
| def fetch_next_recommendation(email): | |
| """Mencari materi Active selanjutnya untuk user""" | |
| conn = get_db_connection() | |
| if not conn: return None | |
| try: | |
| # Cari item 'active' dengan urutan (order) paling kecil | |
| query = """ | |
| SELECT r.learning_path_name, i.subskill_name, i.order, c.course_name | |
| FROM user_roadmap_items i | |
| JOIN user_roadmaps r ON i.user_roadmap_id = r.id | |
| LEFT JOIN learning_path_curriculums c ON (i.subskill_name = c.subskill_name AND r.learning_path_name = c.learning_path) | |
| WHERE r.user_email = %s | |
| AND i.status = 'active' | |
| ORDER BY i.order ASC | |
| LIMIT 1 | |
| """ | |
| cursor = conn.cursor(dictionary=True) | |
| cursor.execute(query, (email,)) | |
| result = cursor.fetchone() | |
| return result | |
| except Exception as e: | |
| print(f"[DB Error] Fetch Recommendation: {e}") | |
| return None | |
| finally: | |
| if conn and conn.is_connected(): | |
| cursor.close() | |
| conn.close() | |
| def recalculate_overall_level(roadmap_id): | |
| """Menghitung ulang level Learning Path berdasarkan item yang sudah completed""" | |
| conn = get_db_connection() | |
| if not conn: return None | |
| try: | |
| # 1. Ambil semua item di roadmap ini | |
| cursor = conn.cursor(dictionary=True) | |
| query = "SELECT skill_level, status FROM user_roadmap_items WHERE user_roadmap_id = %s" | |
| cursor.execute(query, (roadmap_id,)) | |
| items = cursor.fetchall() | |
| if not items: return "Beginner" | |
| # 2. Hitung Poin (Logika Rata-rata Berbobot) | |
| total_points = 0 | |
| total_items = len(items) | |
| for item in items: | |
| # Poin berdasarkan level skill yang tercatat | |
| level = item['skill_level'] | |
| status = item['status'] | |
| points = 1 # Default Beginner | |
| if level == 'Intermediate': points = 2 | |
| elif level == 'Advanced': points = 3 | |
| # Jika status 'completed', kita anggap user sudah mencapai level tersebut | |
| # Jika belum completed, kita bisa beri penalti atau hitung apa adanya (tergantung kebijakan) | |
| # Di sini kita hitung apa adanya sesuai hasil tes/update terakhir | |
| total_points += points | |
| # 3. Tentukan Level Baru | |
| avg_score = total_points / total_items | |
| new_level = "Beginner" | |
| if avg_score >= 2.6: new_level = "Advanced" | |
| elif avg_score >= 1.6: new_level = "Intermediate" | |
| # 4. Update ke Database | |
| update_query = "UPDATE user_roadmaps SET overall_level = %s WHERE id = %s" | |
| cursor.execute(update_query, (new_level, roadmap_id)) | |
| conn.commit() | |
| return new_level | |
| except Exception as e: | |
| print(f"[DB Error] Recalculate Level: {e}") | |
| return None | |
| finally: | |
| if conn and conn.is_connected(): | |
| cursor.close() | |
| conn.close() |