Spaces:
Paused
Paused
File size: 6,979 Bytes
e4fe207 6fbf891 e4fe207 597ff7e e4fe207 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
# subj_inst.py
import streamlit as st
import pandas as pd
from app5_selectbox.database_con import cursor, db_connection
from app5_selectbox.app5_selectbox_func import display_table, generate_unique_4
import time
# import ast
# db_connection.disconnect()
# Define a function to fetch and display the desired data
def display_combined_table(cursor, table_name):
try:
# Execute the SQL query to fetch the data with the desired columns
cursor.execute("""
SELECT
si.subj_inst_id,
si.sub_id_code,
s.sub_name, -- Add subj_name to the query
si.inst_id,
i.inst_name,
si.class_id,
p.prog_code,
c.class_year,
c.class_section
FROM subj_inst si
INNER JOIN subject s ON si.sub_id_code = s.sub_id_code
INNER JOIN instructor i ON si.inst_id = i.inst_id
INNER JOIN class c ON si.class_id = c.class_id
INNER JOIN program p ON s.prog_id = p.prog_id
""")
data = cursor.fetchall()
if not data:
st.warning("No data found.")
else:
# Create a DataFrame from the fetched data and set column names
df = pd.DataFrame(data, columns=[
'subj_inst_id',
'sub_id_code',
'subj_name', # Add subj_name to the columns
'inst_id',
'inst_name',
'class_id',
'prog_code',
'class_year',
'class_section'
])
# Display the table with centered text
st.header(f"{table_name} Table")
# Create select boxes for filtering
prog_code_filter = st.selectbox("Filter by Program Code", df['prog_code'].unique())
class_year_filter = st.selectbox("Filter by Class Year", df['class_year'].unique())
# Filter sections based on the selected program code and class year
available_sections = df[(df['prog_code'] == prog_code_filter) & (df['class_year'] == class_year_filter)]['class_section'].unique()
if len(available_sections) == 0:
st.warning("No sections available for the selected program code and class year.")
else:
section_filter = st.selectbox("Filter by Section", sorted(available_sections))
# Apply filters
filtered_df = df[(df['prog_code'] == prog_code_filter) & (df['class_year'] == class_year_filter) & (df['class_section'] == section_filter)]
if filtered_df.empty:
st.warning("No matching records found.")
else:
print(filtered_df.style.set_properties(**{'text-align': 'center'}))
st.dataframe(filtered_df.style.set_properties(**{'text-align': 'center'}))
except Exception as e:
st.error(f"An error occurred while fetching data: {str(e)}")
def subj_inst(table_name):
subj_inst_id = generate_unique_4(cursor, "subj_inst_id", table_name)
# Fetch program options from the program table
cursor.execute("SELECT prog_id, prog_code, prog_name FROM program")
program_options = cursor.fetchall()
prog_code = st.selectbox("Program Code", [str(prog[1]) for prog in program_options])
# Fetch class options from the class table filtered by prog_code
cursor.execute("""
SELECT c.class_id,
c.class_year || ' - ' || c.class_section || ' (' || p.prog_code || ')'
FROM class c
INNER JOIN program p ON c.prog_id = p.prog_id
WHERE c.prog_id = ?
""", (program_options[[prog[1] for prog in program_options].index(prog_code)][0],))
class_options = cursor.fetchall()
class_id = st.selectbox("Class Year and Section (Program)", sorted([str(cl[1]) for cl in class_options]))
# Fetch instructor options from the instructor table
cursor.execute("SELECT inst_id, inst_name FROM instructor")
instructor_options = cursor.fetchall()
# Filter subject_options to exclude those that already have assigned instructors in the selected class
cursor.execute("""
SELECT sub_id_code
FROM subj_inst
WHERE class_id = ?
""", (class_options[[cl[1] for cl in class_options].index(class_id)][0],))
assigned_subjects = set(row[0] for row in cursor.fetchall())
cursor.execute("""
SELECT s.sub_id_code, s.sub_name
FROM subject s
WHERE s.prog_id = ?
""", (program_options[[prog[1] for prog in program_options].index(prog_code)][0],))
subject_options = cursor.fetchall()
subject_options = [(sub[0], sub[1]) for sub in subject_options if sub[0] not in assigned_subjects]
if not subject_options:
st.warning("All subjects in this class already have instructors.")
else:
sub_id_code = st.selectbox("Subject Name", [str(sub[1]) for sub in subject_options])
# Fetch selected subject's instructor options
cursor.execute("SELECT inst_id, inst_name FROM instructor")
instructor_options = cursor.fetchall()
inst_id = st.selectbox("Instructor Name", [str(inst[1]) for inst in instructor_options])
if st.button("Insert Subject Instructor Record"):
try:
# Extract the selected IDs from the displayed names
sub_id_code = subject_options[[sub[1] for sub in subject_options].index(sub_id_code)][0]
inst_id = instructor_options[[inst[1] for inst in instructor_options].index(inst_id)][0]
class_id = class_options[[cl[1] for cl in class_options].index(class_id)][0]
# Check for duplicates
cursor.execute("""
SELECT subj_inst_id
FROM subj_inst
WHERE sub_id_code = ?
AND inst_id = ?
AND class_id = ?
""", (sub_id_code, inst_id, class_id))
duplicate_check = cursor.fetchone()
if duplicate_check:
st.error("This combination of Subject, Instructor, and Class already exists.")
else:
# Insert a record into the subj_inst table
cursor.execute("""
INSERT INTO subj_inst (subj_inst_id, sub_id_code, inst_id, class_id)
VALUES (?, ?, ?, ?)
""", (subj_inst_id, sub_id_code, inst_id, class_id))
db_connection.commit()
st.success("Record inserted successfully.")
time.sleep(0.3)
st.rerun()
except Exception as e:
st.error(f"An error occurred: {str(e)}")
display_combined_table(cursor, table_name)
# # db_connection.reconnect()
|