Spaces:
Paused
Paused
| # 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() | |