BroDi_Model / app /database.py
SatriaDharma's picture
please last
c30766a
Raw History Blame Contribute Delete
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()