app5_selectbox/class_tbl.py

# class.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


# In the display_table function, fetch and display prog_code
def display_class_table(cursor, table_name):
    if table_name == "class":
        cursor.execute("SELECT class.class_id, class.prog_id, program.prog_code, class.class_year, class.class_section FROM class INNER JOIN program ON class.prog_id = program.prog_id")
        data = cursor.fetchall()
        column_names = [i[0] for i in cursor.description]
        df = pd.DataFrame(data, columns=column_names)
        st.dataframe(df.style.set_properties(**{'text-align': 'center'}))

def class_tbl(table_name):
    class_id = generate_unique_4(cursor, "class_id", table_name)

    # Fetch available programs from the 'program' table
    cursor.execute("SELECT prog_id, prog_name, prog_code FROM program")
    available_programs = cursor.fetchall()
    prog_id = st.selectbox("Program ID", available_programs, format_func=lambda row: f"{row[1]} ({row[2]})", key="prog_id")[0]
    class_year = st.selectbox("Class Year", ("1", "2", "3", "4"), key="class_year")
    class_section = st.text_input("Class Section", key="class_section", max_chars=1).upper()

    if st.button("Insert Class Record"):
        # Check if the class_year and class_section are provided
        if not class_year or not class_section:
            st.error("Class Year and Class Section are required. Please provide values for both fields.")
        else:
            try:
                # Check for duplicates
                cursor.execute("SELECT class_id FROM class WHERE prog_id = %s AND class_year = %s AND class_section = %s",
                                (prog_id, class_year, class_section))
                result = cursor.fetchone()
                if result is not None:
                    st.error("A record with the same Program ID, Class Year, and Class Section already exists.")
                else:
                    # Insert a record into the class table
                    cursor.execute("INSERT INTO class (class_id, prog_id, class_year, class_section) VALUES (%s, %s, %s, %s)",
                                    (class_id, prog_id, class_year, class_section))
                    db_connection.commit()
                    st.success("Record inserted successfully.")
            except Exception as e:
                st.error(f"An error occurred: {str(e)}")
    display_class_table(cursor, table_name)