app5_selectbox/evaluation.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

# 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()
    
    # st.write(instructor_data)
    
    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)

    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(filtered_data)
    st.subheader(f"Evaluated by: {len(selected_instructor_comments)} students")
    
    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')

    # 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)
    
    fig = go.Figure()

    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, 
        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()
    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=1000, 
        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))
                # 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("Error occurred.. Retrying")
                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)



    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 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]
        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))
    avg_scores_per_class =avg_scores_per_class.style.apply(highlight_cell, col_label="avg_overall", row_label=9)

    st.write(avg_scores_per_class)
    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}")
    
    
    


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

    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()
            
            # 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,
                ))
                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:
                        # 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:
            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])
            
            with st.expander("Sentiment Analysis"):
                
                eval_analysis(analyze_instructors_results[0], analyze_instructors_results[1], analyze_instructors_results[2])      #############################
                # pass
                
    except Exception as e:
        st.error(f"An error occurred: {str(e)}")