import gradio as gr
import mysql.connector
from mysql.connector import Error
import matplotlib.pyplot as plt

def clear_form():
    return "", "Submitted"  # Default values

def fetch_data(pr_number, status):
    details_html = ''
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='records',
            user='root',
            password='Nikki@1234'  # Placeholder: Use a secure method to store and use passwords
        )

        query = "SELECT * FROM PR_Details WHERE 1=1"
        params = []

        if pr_number:
            query += " AND PR_Number = %s"
            params.append(pr_number)
        if status:
            query += " AND status = %s"
            params.append(status)

        cursor = connection.cursor(dictionary=True)
        cursor.execute(query, params)
        records = cursor.fetchall()

        if records:
            # Enclose each record in a stylish div
            details_html = "<div style='display: flex; flex-direction: column; gap: 10px;'>"
            for record in records:
                details_html += f"<div style='border: 1px solid #ccc; padding: 10px; margin-top: 5px; border-radius: 5px;'><table>"
                for column, value in record.items():
                    details_html += f"<tr><td><strong>{column}:</strong></td><td>{value}</td></tr>"
                details_html += "</table></div>"
            details_html += "</div>"
        else:
            details_html = "<p>No matching records found.</p>"

    except Error as e:
        print(f"Database Error: {e}")
        details_html = "Failed to fetch data due to a database error."
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

    return details_html



def plot_status_pie_chart(status):
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='records',
            user='root',
            password='Nikki@1234'
        )
        cursor = connection.cursor()

        # If specific status is selected, fetch only that status count, otherwise fetch all
        if status:
            status_query = """
            SELECT status, COUNT(PR_Number) as Total
            FROM PR_Details
            WHERE status = %s
            GROUP BY status
            ORDER BY status
            """
            cursor.execute(status_query, (status,))
        else:
            status_query = """
            SELECT status, COUNT(PR_Number) as Total
            FROM PR_Details
            GROUP BY status
            ORDER BY status
            """
            cursor.execute(status_query)

        result = cursor.fetchall()

        if result:
            labels = [row[0] for row in result]
            sizes = [row[1] for row in result]
            colors = ['gold', 'lightcoral', 'lightskyblue'][:len(result)]  # Adjust color list to match size

            plt.figure(figsize=(8, 6))
            plt.pie(sizes, labels=labels, autopct='%1.1f%%', startangle=140, colors=colors)
            plt.axis('equal')
            pie_chart = plt.gcf()
            plt.close()
            return pie_chart
        else:
            plt.figure(figsize=(8, 6))
            plt.text(0.5, 0.5, 'No data for selected status', fontsize=12, ha='center')
            pie_chart = plt.gcf()
            plt.close()
            return pie_chart

    except Error as e:
        print(f"Database Error: {e}")
        plt.figure(figsize=(8, 6))
        plt.text(0.5, 0.5, 'Database error occurred', fontsize=12, ha='center')
        pie_chart = plt.gcf()
        plt.close()
        return pie_chart
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def update_outputs(pr_number, status):
    # Fetch PR details
    details = fetch_data(pr_number, status)
    # Update pie chart based on selected status
    pie_chart = plot_status_pie_chart(None if pr_number else status)
    return details, pie_chart

with gr.Blocks() as app:
    with gr.Tab("PR Details and Pie Chart"):
        pr_number_input = gr.Textbox(label="PR Number", placeholder="Enter PR Number…", value="")
        status_input = gr.Radio(choices=['Submitted', 'Ordered', 'Composing'], label="Status", value='Submitted')
        details_output = gr.HTML(label="Details")
        pie_chart_output = gr.Plot(label="Status Pie Chart")

        fetch_btn = gr.Button("Fetch PR Details")
        clear_btn = gr.Button("Clear")

        fetch_btn.click(
            update_outputs,
            inputs=[pr_number_input, status_input],
            outputs=[details_output, pie_chart_output]
        )

        clear_btn.click(
            clear_form,
            inputs=[],
            outputs=[pr_number_input, status_input]
        )

        # Update pie chart based on selected status when radio button value changes
        status_input.change(
            lambda status: plot_status_pie_chart(status),
            inputs=status_input,
            outputs=pie_chart_output
        )

app.launch(share=True)



This is my code. Please create a another radio button called pie chart and it should show the both submitted and ordered pie chart in one pie chart.