app5_selectbox/evaluation_fac.py

import streamlit as st
import pandas as pd
import plotly.graph_objs as go
import time
import plotly.express as px
# import ast
import numpy as np

from app5_selectbox.database_con import cursor, db_connection
from app5_selectbox.app5_selectbox_func import generate_unique_4
from app5_selectbox.evaluation_analysis import eval_analysis
# from app5_selectbox.evaluation_analysis_g4f import eval_analysis

# from app5_selectbox.langchain_llama_gpu import llm_chain
from app5_selectbox.g4f_prompt import g4f_prompt


# st.title("Student-Faculty Evaluation")





# st.write(st.session_state.student_id)
# Function to fetch evaluation data
def fetch_evaluation_data():
    cursor.execute("SELECT * FROM evaluation")
    evaluation_data = cursor.fetchall()
    if not evaluation_data:
        st.warning("No evaluation data found.")
        return None
    column_names = [i[0] for i in cursor.description]
    return pd.DataFrame(evaluation_data, columns=column_names)

# Function to analyze instructors
def analyze_instructors(evaluation_df):
    if evaluation_df is None:
        return

    column_names = evaluation_df.columns[4:14]
    criteria_labels = [column.replace("_", " ") for column in column_names]
    
    cursor.execute("SELECT * FROM instructor")
    instructor_data = cursor.fetchall()
    
    instructor_df = pd.DataFrame(instructor_data, columns=["inst_id", "instructor name","program code", "user name", "password"])
    instructor_avg_scores = evaluation_df.groupby("inst_id")[column_names].mean().reset_index()
    instructor_avg_scores = instructor_avg_scores.merge(instructor_df, on="inst_id", how="left")
    
    
    # st.write(instructor_avg_scores)
    # programs_list = sorted(instructor_avg_scores["program code"].unique())
    
    # # Fetch program options from the program table
    # cursor.execute("SELECT prog_id, prog_code, prog_name FROM program")
    # selected_program = pd.DataFrame(cursor.fetchall(), columns=["prog_id", "prog_code", "prog_name"])
    # st.write(selected_program)
    # # st.write(list({str(prog): prog[0] for prog in program_options}))
    # selected_program_select = st.selectbox("Select Program", selected_program["prog_code"])
    # # selected_program = ast.literal_eval(str(selected_program))
    
    # # selected_program = st.selectbox("Select Program", programs_list)
    # filtered_instructor_list = pd.DataFrame(instructor_avg_scores)
    # # st.write(filtered_instructor_list)
    # mask = filtered_instructor_list["program code"] == selected_program.loc[selected_program['prog_code'] == selected_program_select, 'prog_id'].values[0]
    # # st.write(mask)
    # filtered_instructor_list = filtered_instructor_list.loc[mask]

    # # st.write(filtered_instructor_list)
    # instructors_list = sorted(filtered_instructor_list["instructor name"].unique())
    # # print(type(instructor_avg_scores)) 
    
    # instructors_list = instructor_avg_scores.query("program code == {selected_program}")
    # st.write(len(instructors_list))   # df to graph
    
    # selected_instructor = st.selectbox("Select Instructor", instructors_list)
    selected_instructor = st.session_state.inst_name
    
    try:
        filtered_data = evaluation_df[evaluation_df["inst_id"] == instructor_avg_scores[instructor_avg_scores["instructor name"] == selected_instructor]["inst_id"].values[0]]
        selected_instructor_comments = list(filtered_data["comments"])
        st.write(f"## Welcome! {selected_instructor}")
        st.subheader(f"You are Evaluated by: {len(selected_instructor_comments)} students")
    except:
        st.info("### No Existing Evaluation Found!",icon="❗")
    
    
    models = ['BERT-BASE MODEL', 'BERT-LARGE MODEL', 'DISTILIBERT MODEL', 'NAIVE BAYES MODEL']
    with st.sidebar.expander("Settings"):
        # enable_analyze_graph =  st.checkbox("Analyze graph by LLM", value=False)
        global enable_llm_analyze_sintement, sentiment_model, sentiment_model_index
        enable_llm_analyze_sintement = st.checkbox("Enable LLM (LLAMA)", value=False)
        if enable_llm_analyze_sintement:
            sentiment_model = st.selectbox("Select Model for Sentiment Analysis:", models)
            sentiment_model_index = models.index(sentiment_model)
        if st.button("Log Out", type="primary", use_container_width=True):
            st.session_state.pop("logged_in", None)
            st.session_state.pop("inst_id", None)
            st.session_state.pop("inst_name", None)
            st.session_state.pop("prog_id", None)
            st.session_state.pop("user_type", None)
            st.rerun()
        st.button("Refresh", use_container_width=True)
    
    
    cursor.execute("""
        SELECT subj_inst.subj_inst_id, subject.sub_name 
        FROM subj_inst 
        INNER JOIN subject 
        ON subj_inst.sub_id_code = subject.sub_id_code
    """)

    # Assuming you have a DataFrame named 'filtered_data'
    # and column_names is a list of column names you want to consider for calculating average scores

    # Convert all columns to numeric data
    filtered_data[column_names] = filtered_data[column_names].apply(pd.to_numeric, errors='coerce')
    
    # Convert all columns to numeric data
    # filtered_data.loc[:, column_names] = filtered_data.loc[:, column_names].apply(pd.to_numeric, errors='coerce')


    # Fetch subject data from the cursor
    subject_data = cursor.fetchall()

    # Create a DataFrame for subject data
    subject_df = pd.DataFrame(subject_data, columns=["subj_inst_id", "sub name"])

    # Merge subject data with filtered data based on 'subj_inst_id'
    filtered_data = filtered_data.merge(subject_df, on="subj_inst_id", how="left")

    # Group by subject name and calculate average scores
    subject_avg_scores = filtered_data.groupby("sub name")[column_names].mean().reset_index()

    # Calculate total average and add it as a new column
    subject_avg_scores["total average"] = subject_avg_scores[column_names].mean(axis=1)
    
    subject_avg_scores = filtered_data.groupby("sub name")[column_names].mean().reset_index()
    subject_avg_scores["total average"] = subject_avg_scores[column_names].mean(axis=1)
    



    cursor.execute(f"""
        SELECT
            s.class_id,
            pr.prog_code || '-' || c.class_year || '-' || c.class_section AS class_info,
            COUNT(DISTINCT s.stud_id) AS num_respondents,
            ROUND((AVG(Teaching_Effectiveness) + AVG(Course_Organization) + AVG(Accessibility_and_Communication) +
            AVG(Assessment_and_Grading) + AVG(Respect_and_Inclusivity) + AVG(Engagement_and_Interactivity) +
            AVG(Feedback_and_Improvement) + AVG(Accessibility_of_Learning_Resources) +
            AVG(Passion_and_Enthusiasm) + AVG(Professionalism_and_Ethical_Conduct)) / 10, 2) AS avg_overall,
            ROUND((COUNT(DISTINCT s.stud_id) * (AVG(Teaching_Effectiveness) + AVG(Course_Organization) + AVG(Accessibility_and_Communication) +
            AVG(Assessment_and_Grading) + AVG(Respect_and_Inclusivity) + AVG(Engagement_and_Interactivity) +
            AVG(Feedback_and_Improvement) + AVG(Accessibility_of_Learning_Resources) +
            AVG(Passion_and_Enthusiasm) + AVG(Professionalism_and_Ethical_Conduct)) / 10), 2) AS weighted_avg_overall
        FROM
            evaluation e
        JOIN
            student s ON e.stud_id = s.stud_id
        JOIN
            class c ON s.class_id = c.class_id
        JOIN
            program pr ON c.prog_id = pr.prog_id
        WHERE
            s.stud_id IN {tuple(list(filtered_data["stud_id"]))}
        GROUP BY
            s.class_id, pr.prog_code, c.class_year, c.class_section, class_info;
    """)

    avg_scores_per_class = pd.DataFrame(cursor.fetchall(), columns=[
        "class_id",
        "class_info",
        "num_respondents",
        "avg_overall",
        "weighted_avg_overall"
    ])
    
    # Calculate the last row's weighted_avg_overall / num_respondents
    last_row_index = avg_scores_per_class["weighted_avg_overall"].last_valid_index()
    if last_row_index is not None:
        avg_scores_per_class.at[last_row_index, "weighted_avg_overall"] /= avg_scores_per_class.at[last_row_index, "num_respondents"]

    # Convert the column to decimal.Decimal before rounding
    avg_scores_per_class["weighted_avg_overall"] = avg_scores_per_class["num_respondents"] * avg_scores_per_class["avg_overall"]  # avg_scores_per_class["weighted_avg_overall"].apply(lambda x: round(float(x), 2))

    # Drop rows with None values
    avg_scores_per_class = avg_scores_per_class.dropna()


    # Calculate the overall averages for avg_overall and weighted_avg_overall
    num_respondents = round(avg_scores_per_class["num_respondents"].sum(), 2)
    overall_avg_overall = round(avg_scores_per_class["avg_overall"].mean(), 2)
    overall_weighted_avg_overall = round(avg_scores_per_class["weighted_avg_overall"].sum(),2)
    weighted_avg_overall = round(overall_weighted_avg_overall / num_respondents,2)

    # # Append an additional row for avg_overall and weighted_avg_overall
    # avg_scores_per_class = avg_scores_per_class.append({
    #     "class_id": int(avg_scores_per_class["class_id"].max()) + 1,
    #     "class_info": "Total",
    #     "num_respondents": avg_scores_per_class["num_respondents"].sum(),
    #     "avg_overall": round(overall_avg_overall, 2),
    #     "weighted_avg_overall": round(overall_weighted_avg_overall / avg_scores_per_class["num_respondents"].sum(), 2)
    # }, ignore_index=True)

    # st.write(avg_scores_per_class.style.set_properties(**{'text-align': 'center'}))
    
    
    
    # Add summary rows to the DataFrame
    avg_scores_per_class = avg_scores_per_class.append({
        "class_id": "",
        "class_info": "Summary",
        "num_respondents": num_respondents,
        "avg_overall": " ",
        "weighted_avg_overall": overall_weighted_avg_overall
    }, ignore_index=True)

    
    def get_color(weighted_avg_overall):
        satisfaction_level = calculate_satisfaction(weighted_avg_overall)
        if satisfaction_level == "Outstanding":
            return "rgb(171, 235, 198 )"
        elif satisfaction_level == "Above Average":
            return "rgb(218, 247, 166)"
        elif satisfaction_level == "Average":
            return "rgb(255, 195, 0)"
        elif satisfaction_level == "Below Average":
            return "rgb(255, 87, 51)"
        else:
            return "rgb(255, 87, 51)"
    
    def calculate_satisfaction(weighted_avg_overall):
        if weighted_avg_overall > 4:
            return "Outstanding"
        elif weighted_avg_overall > 3:
            return "Above Average"
        elif weighted_avg_overall > 2:
            return "Average"
        elif weighted_avg_overall > 1:
            return "Below Average"
        else:
            return "Unsatisfactory"
        
    def highlight_cell(col, col_label, row_label):
    # check if col is a column we want to highlight
        if col.name == col_label:
            # a boolean mask where True represents a row we want to highlight
            mask = (col.index == row_label)
            # return an array of string styles (e.g. ["", "background-color: yellow"])
            # return ["background-color: lightgreen" if val_bool else "" for val_bool in mask]
            return [f"background-color: {get_color(weighted_avg_overall)}" if val_bool else "" for val_bool in mask]
        else:
            # return an array of empty strings that has the same size as col (e.g. ["",""])
            return np.full_like(col, "", dtype="str")
    
    
    

    
    avg_scores_per_class = avg_scores_per_class.append({
        "class_id": "",
        "class_info": "Weighted Avg.",
        "num_respondents": " ",  # You can set this to "N/A" or any appropriate value
        "avg_overall": calculate_satisfaction(weighted_avg_overall),  # You can set this to "N/A" or any appropriate value
        "weighted_avg_overall": weighted_avg_overall
    }, ignore_index=True)

    
    # # st.dataframe(avg_scores_per_class.style.background_gradient(subset=["C"], cmap="RdYlGn", vmin=0, vmax=2.5))
    
    last_row = avg_scores_per_class.index[-1]
    # avg_scores_per_class =avg_scores_per_class.style.apply(highlight_cell, col_label="avg_overall", row_label=last_row)
    # Assuming avg_scores_per_class is your DataFrame

        
    # Rename columns
    avg_scores_per_class.rename(columns={'class_id': 'CLASS ID',
                                        'class_info': 'SECTION',
                                        'num_respondents': 'NO. of RESPONDENTS',
                                        'avg_overall': 'AVERAGE',
                                        'weighted_avg_overall': 'WEIGHTED AVERAGE'}, inplace=True)

    # Format numeric values to two decimal places
    avg_scores_per_class = avg_scores_per_class.applymap(lambda x: '{:.2f}'.format(x) if isinstance(x, float) else x)

    # Get the last row index
    last_row = avg_scores_per_class.index[-1]

    # Apply any specific styling
    avg_scores_per_class = avg_scores_per_class.style.apply(highlight_cell, col_label="AVERAGE", row_label=last_row)

    # Drop index column
    avg_scores_per_class.hide_index()

    # Render DataFrame without index column
    # st.dataframe(avg_scores_per_class_no_index)
    
    # avg_scores_per_class.style.apply(lambda x: ["background: red" if v > x.iloc[3] else "" for v in x], axis = 1)
    
    # avg_scores_per_class = pd.DataFrame(avg_scores_per_class)
    # avg_scores_per_class.set_index('CLASS ID', inplace=True)
    # avg_scores_per_class.reset_index(drop=True, inplace=True)
    # st.write(type(avg_scores_per_class))
    # avg_scores_per_class.reset_index(drop=True, inplace=True)
    # st.markdown(avg_scores_per_class.style.hide(axis="index").to_html(), unsafe_allow_html=True)
    # avg_scores_per_class1 = avg_scores_per_class.style.hide()
    
    
    
    # # Convert DataFrame to HTML without index column
    # avg_scores_per_class_html = avg_scores_per_class.to_html(index=False)

    # Use CSS to hide the index column    
    avg_scores_per_class_html = avg_scores_per_class.render()
    avg_scores_per_class_html = avg_scores_per_class_html.replace('<table ', '<table style="table-layout: fixed; " ')

    st.markdown(avg_scores_per_class_html, unsafe_allow_html=True)
    
    st.write(f"### Number of respondents: {num_respondents}")
    st.write(f"### Overall weighted avg.: {overall_weighted_avg_overall}")
    st.write(f"### Weighted avg overall: {weighted_avg_overall}")
    
    
    
    with st.expander("VISUALIZATIONS"):
        fig = go.Figure(layout=dict(
        autosize=True,  # Set autosize to True for automatic adjustment
        ))
        for criterion, label in zip(column_names, criteria_labels):
            fig.add_trace(go.Bar(
                x=subject_avg_scores["sub name"],
                y=subject_avg_scores[criterion],
                name=label,
            ))

        # Add the total average score above the bars
        total_average = subject_avg_scores["total average"].mean()
        fig.add_trace(go.Scatter(   
            x=subject_avg_scores["sub name"],
            y=subject_avg_scores["total average"],
            mode="markers+text",
            text=round(subject_avg_scores["total average"], 2),
            textposition="top center",
            textfont=dict(size=14),
            marker=dict(size=10, color="black"),
            name="Total Average",
        ))

        fig.update_layout(
            # width=1000, height=600, 
            # autosize=True,  # Set autosize to True for automatic adjustment
            barmode="group", 
            title=f"Average Scores per Criteria by Subject for Instructor: {selected_instructor}",
            xaxis_title=f"Overall Average Score (All Subjects): {total_average:.2f}",
            yaxis_title="Average Score",
        )

        st.plotly_chart(fig)
        
        results_to_prompt = "Average score per Criteria\n"
        criteria_averages = [(criteria.replace("_", " "), round(filtered_data[criteria].mean(), 2)) for criteria in column_names]
        for criteria, average_score in criteria_averages:
            results_to_prompt += f"{criteria}: {average_score}/5, \n"

        fig = go.Figure(layout=dict(
        autosize=True,  # Set autosize to True for automatic adjustment
        ))
        fig.add_trace(go.Bar(
            x=[criteria for criteria, _ in criteria_averages],
            y=[average_score for _, average_score in criteria_averages],
            text=[f"{average_score}/5" for _, average_score in criteria_averages],
        ))

        fig.update_layout(
            width=None, 
            height=None,
            title="Average Score per Criteria",
            xaxis_title="Criteria",
            yaxis_title="Average Score",
        )
        
        st.plotly_chart(fig)
        results_to_prompt = f"""
        Based from these over-all average score please Analyze it and provide short insights: {str(results_to_prompt)}. 
        Make it in sentence type and in English language only.
        
        """
        while True:
            try:
                with st.spinner("Analyzing... "):
                    # st.write(llm_chain.run(prompt))
                    if enable_llm_analyze_sintement and sentiment_model:
                        # st.write(g4f_prompt(results_to_prompt))     #############################
                        st.success("Analyzing Complete!")
                    break
                
            except Exception as e:
                    # Handle the error (e.g., log it or take appropriate action)
                    # Sleep for a moment before retrying
                    st.write(f"Error occurred.. Retrying {e}")
                    # pass
                    # time.sleep(0.4)
        # Add pie graph of evaluation distribution per student's section
        # Fetch program options from the program table
        cursor.execute(f"""
                        SELECT
                            pr.prog_code || '-' || c.class_year || '-' || c.class_section AS merged_result,
                            COUNT(*) AS occurrence_count
                        FROM
                            student s
                        JOIN
                            class c ON s.class_id = c.class_id
                        JOIN
                            program pr ON c.prog_id = pr.prog_id
                        WHERE
                            s.stud_id IN {tuple(list(filtered_data["stud_id"]))}
                        GROUP BY
                            s.class_id, pr.prog_code, c.class_year, c.class_section;

                    """)
        
        merged_result = pd.DataFrame(cursor.fetchall(), columns=["merged_result", "occurrence_count"])
        # st.write(filtered_data)
        # st.write(merged_result)
        # section_counts = filtered_data["stud_id"].value_counts()
        # st.write(section_counts)
        
        fig = px.pie(
            merged_result,
            values="occurrence_count",
            names="merged_result",
            title="Evaluation Distribution per Student's Section",
        )

        # Add percentage and occurrence_count to the hover information
        fig.update_traces(
            hovertemplate="%{label}: %{percent} <br>Occurrence Count: %{value}",
            textinfo="percent+value",
        )

        fig.update_layout(
            width=600,
            height=600,
            font=dict(size=20), 
        )
        st.plotly_chart(fig)

    # if st.button("Analyze the results", key="analyze_results"):

    st.write(f"### ANALYSIS PER SUBJECT AREA")
    for subject in subject_avg_scores["sub name"]:
        with st.expander(subject):
            subject_filtered_data = filtered_data[filtered_data["sub name"] == subject]
            promt_txt = ""
            fig = go.Figure(layout=dict(
                autosize=True,  # Set autosize to True for automatic adjustment
                showlegend=False
            ))
                    
            # st.write(subject_filtered_data)  # displays DF for every graphs
            for criterion, label in zip(column_names, criteria_labels):
                text = round(subject_filtered_data[criterion].mean(),2)
                fig.add_trace(go.Bar(
                    x=[label],
                    y=[text],
                    text=text,
                    name=label,
                    # textposition="none",  # Remove text labels below the bars
                ))
                promt_txt += criterion.replace("_", " ") + ": " + str(text)+ "\n"
            # st.text(promt_txt)  # prompt per graph

            total_average = subject_filtered_data[column_names].mean(axis=1).mean()

            total_average_txt = f"{subject} Average Score:  {round(total_average,2)}/5"
            fig.update_layout(
                barmode="group",
                # width=1000, 
                title=total_average_txt,
                yaxis_title="Average Score",
            )
            st.plotly_chart(fig)
            
            prompt = f"generate a very short insights about this faculty evaluation result for the subject {subject}?\n{promt_txt}\nplease strictly shorten your response in sentence format"
            # st.text(prompt)
            while True:
                with st.spinner("Generating Recommendation"):
                    try:
                        # if enable_llm_analyze_sintement and sentiment_model: st.write(g4f_prompt(prompt))    #############################
                        # pass
                        # break
                        break
                    except Exception as e:
                        # Handle the error (e.g., log it or take appropriate action)
                        # Sleep for a moment before retrying
                        # st.write("Error occurred.. Retrying")
                        pass
                        # time.sleep(0.4)
    
    return selected_instructor, selected_instructor_comments, results_to_prompt



def evaluation():
    try:
        evaluation_df = fetch_evaluation_data()
        if evaluation_df is not None and st.session_state.logged_in:
            analyze_instructors_results = analyze_instructors(evaluation_df)
        # if st.button("Analyze comments"):
        #     eval_analysis(analyze_instructors_results[0], analyze_instructors_results[1], analyze_instructors_results[2])
            if enable_llm_analyze_sintement and sentiment_model:
                eval_analysis(analyze_instructors_results[0], analyze_instructors_results[1], analyze_instructors_results[2], sentiment_model_index)      #############################
                # pass
                
        # st.markdown("""
        #     <style>
        #     div.stButton > button:first-child {
        #         background-color: #0099ff;
        #         color:#ffffff;
        #     }
        #     div.stButton > button:hover {
        #         background-color: #397399;
        #         color:#ffffff;
        #         }
        #     </style>""", unsafe_allow_html=True)

            
    except Exception as e:
        pass
        # st.error(f"An error occurred: {str(e)}")