Sales MIS Dashboard with Python and Dash: Empowering Small Business Owners to Track Data Effectively

In today’s competitive business landscape, small business owners face numerous challenges in managing and analyzing their sales data. To overcome this hurdle, an effective Sales Management Information System (MIS) dashboard can provide crucial insights and enable data-driven decision-making. In this article, we’ll explore a Python code that creates a Sales MIS dashboard using the Dash framework. We’ll also discuss how this code can help small business owners keep track of their data, make informed decisions, and drive growth.

Understanding the Code: The provided Python code demonstrates the implementation of a Sales MIS dashboard. Let’s break down its key components and functionalities:

1. Connecting to the Database: The code establishes a connection to a MySQL database, allowing access to the sales and product data. Small business owners can store their transactional data in the database for easy retrieval and analysis.
2. Fetching and Analyzing Sales Data: The code retrieves sales data from the database using SQL queries. It utilizes the pandas library to store the data in a DataFrame, making it easier to manipulate and analyze. The code calculates key performance indicators (KPIs) such as daily sales count, 7-day moving average of daily revenue, revenue distribution by product line, and revenue breakdown by city. These visualizations provide valuable insights into sales trends, customer behavior, and revenue sources.
3. Adding and Viewing Sales Data: The code includes functionality to add new sales records to the database. Small business owners can input relevant details such as invoice ID, branch, city, customer type, and product information. This feature allows users to track their sales in real-time and maintain an accurate record of transactions.
4. Adding and Viewing Product Lines: The code enables the addition and viewing of product lines. Small business owners can input details such as product line, category, tax, and cost of goods sold. This information helps in understanding the performance of different product lines and optimizing pricing strategies.
5. Interactive Dashboards: The code utilizes the Dash framework to create an interactive web-based dashboard. The dashboard consists of multiple tabs, each displaying different aspects of sales data and product information. Small business owners can navigate through these tabs to gain a comprehensive view of their business performance.
6. Data Visualization: The code leverages the Plotly and Plotly Express libraries to create visually appealing and interactive charts. The line charts visualize the daily sales count and the 7-day moving average of daily revenue, allowing small business owners to identify sales patterns and revenue trends over time. The pie chart illustrates the revenue distribution by product line, helping in identifying the most profitable product lines. The bar chart displays the revenue breakdown by city, assisting in geographical analysis and market targeting.

Benefits for Small Business Owners: The Sales MIS dashboard created by the code offers several advantages for small business owners:

1. Data Centralization: By storing sales and product data in a centralized database, small business owners can access and analyze their data conveniently. This eliminates the need for manual data collection and enables quick decision-making.
2. Real-time Insights: The ability to add sales data in real-time allows small business owners to monitor their business performance on a daily basis. They can identify trends, track sales growth, and respond to market changes promptly.
3. Data Visualization: Visual representations of sales data through interactive charts make it easier for small business owners to understand complex information. The charts provide a visual snapshot of key metrics, facilitating quick analysis and identifying areas for improvement.
4. Product Line Analysis: The product line functionality allows small business owners to evaluate the performance of different product categories. They can identify top-selling product lines, analyze profitability, and make informed decisions regarding inventory management and marketing strategies.
5. Efficiency and Scalability: Automating data analysis and visualization through the Sales MIS dashboard saves time and effort for small business owners. It also allows for scalability, enabling businesses to handle larger datasets and expanding operations without compromising on data management.

The Python code below is for creating a Sales Management Information System (MIS) dashboard using the Dash framework in Python.

Let’s go through the code step by step:

1. Importing Required Libraries:
   • The code begins by importing necessary libraries such as mysql.connector for connecting to MySQL database, datetime for working with dates and times, dash for creating the web application, pandas for data manipulation, plotly.express as px for creating interactive plots, dash_bootstrap_components for styling the application, and various components from dash, dash_table, dbc, html, dcc, Input, Output, and State for creating the UI components.
2. MySQL Connection Details:
   • Next, the script defines the connection details for the MySQL database including the host, username, password, and database name.
3. Establish MySQL Connection:
   • The script establishes a connection to the MySQL database using the provided connection details.
4. Fetch Sales Data:
   • The script executes an SQL query to fetch sales data from the MySQL database table called “sales” using the cursor.execute() method. It then fetches all the data using cursor.fetchall(). The column names are extracted from the cursor.description attribute.
   • The fetched data is stored in a pandas DataFrame called sales.
5. Fetch Products Data:
   • Similar to fetching sales data, this section fetches product data from the MySQL database table called “products” and stores it in a pandas DataFrame called products.
6. Calculate Daily Sales Count:
   • Using pandas operations, the script calculates the daily sales count by grouping the “invoice_id” column based on the “date” column in the sales DataFrame. The resulting series is named daily_sales_number.
7. Create Line Chart for Daily Sales Count:
   • The script uses plotly.express (px) library to create a line chart (figure_daily_sales_number) based on the daily_sales_number series. The chart title is set to “Daily number of sales”.
8. Calculate 7-day Moving Average of Daily Revenue:
   • Similarly, the script calculates the 7-day moving average of daily revenue by summing the “total_amount” column based on the “date” column in the sales DataFrame. The rolling average is computed using the .rolling() function with a window size of 7 and minimum periods of 7. The resulting series is named m7d_mean_revenue.
9. Create Line Chart for 7-day Moving Average of Daily Revenue:
   • A line chart (figure_m7d_mean_revenue) is created using px.line based on the m7d_mean_revenue series. The chart title is set to “7-day moving average of daily revenue”.
10. Create Pie Chart for Revenue Distribution by Product Line:
    • The script creates a pie chart (figure_product_line) using px.pie by aggregating the total revenue by product line in the sales DataFrame. The chart title is set to “Product lines ratio”.
11. Create Bar Chart for Revenue Breakdown by City:
    • A bar chart (figure_revenue_bycity) is created using px.bar by aggregating the total revenue by city in the sales DataFrame. The chart title is set to “Revenue by city”.
12. Create Revenue Breakdown Table:
    • The script creates a revenue breakdown table using the DataTable component from dash_table. The table is populated with data from the sums DataFrame, which contains the summed values of “total_amount”, “tax”, “cost_of_goods_sold”, and “gross_income” columns. The table is styled using HTML and CSS.
13. Create Dash Application:
    • An instance of the Dash application is created using dash.Dash with the name app. The Bootstrap stylesheet is included for styling the application.
14. Define Tab Contents:
    • Different tab contents are defined as dbc.Card components containing various UI elements for viewing sales data, adding sales, viewing product lines, and adding product lines.
15. Create Tabs:
    • The tab structure is defined using the dbc.Tabs component, which contains multiple dbc.Tab components representing different sections of the dashboard.
16. Create Layout:
    • The layout of the application is defined as a html.Div containing a heading and the previously created tabs component.
17. Callback Functions:
    • The script defines callback functions using the @app.callback decorator. These functions are responsible for handling user interactions and updating the UI accordingly. There are callbacks for submitting sales and product forms, deleting rows from the sales and product line tables.
18. Run the Dash Application:
    • Finally, the script runs the Dash application using app.run_server() with debug mode enabled.

It sets up a Dash application, connects to a MySQL database, fetches data, creates visualizations, and provides interactive UI components for viewing and adding sales and product line data.

Python Code to generate random Sales, Product Line, and Product Category Data (GitHit Repository)

import random
import datetime
import mysql.connector

def generate_product_name():
    # Generate a random product name
    adjective = random.choice(['Red', 'Blue', 'Green', 'Yellow', 'Black', 'White'])
    noun = random.choice(['Shirt', 'Shoes', 'Hat', 'Watch', 'Bag'])
    return f"{adjective} {noun}"

def generate_price():
    # Generate a random price between 10 and 1000
    return round(random.uniform(10, 1000), 2)

def create_invoice_id():
    return "INV" + str(random.randint(1000, 9999))

def create_branch():
    return chr(random.randint(65, 71))  # ASCII values for A to G

def get_random_indian_city():
    cities = ["Mumbai", "Delhi", "Bangalore", "Chennai", "Kolkata", "Hyderabad", "Ahmedabad", "Pune", "Surat", "Jaipur"]
    return random.choice(cities)

def get_random_customer_type():
    return random.choice(["Member", "Non-Member"])

def get_random_gender():
    return random.choice(["Male", "Female"])

def get_random_category():
    product_category = ["Textile Cat A", "Textile Cat B", "Textile Cat C", "Textile Cat D", "Textile Cat E", "Textile Cat F", "Textile Cat G", "Textile Cat H", "Textile Cat I", "Textile Cat J"]
    return random.choice(product_category)


def get_random_unit_price():
    return random.randint(100, 500000)

def get_random_quantity():
    return random.randint(1, 10)

def calculate_tax(unit_price):
    return unit_price * 0.18

def calculate_total(unit_price, quantity, tax):
    return unit_price * quantity + tax

def get_random_date():
    start_date = datetime.datetime(2022, 1, 1)
    end_date = datetime.datetime(2022, 12, 31)
    random_date = start_date + datetime.timedelta(days=random.randint(0, (end_date - start_date).days))
    return random_date.strftime("%Y-%m-%d")

def get_random_time():
    return datetime.time(random.randint(9, 18), random.randint(0, 59)).strftime("%H:%M")

def get_random_payment():
    return random.choice(["Cash", "eWallet"])

def calculate_cost_of_goods_sold(unit_price):
    return random.uniform(0.8, 0.85) * unit_price

def calculate_gross_margin_percentage(cost_of_goods_sold, total_amount):
    return (total_amount - cost_of_goods_sold) / total_amount * 100

def calculate_gross_income(cost_of_goods_sold, quantity):
    return cost_of_goods_sold * quantity

def get_random_stratification_rating():
    return random.randint(1, 5)


# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="root",
  database="my_sales"
)

# Create the sales and products tables if they don't exist
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES LIKE 'sales'")
sales_table_exists = mycursor.fetchone()
mycursor.execute("SHOW TABLES LIKE 'products'")
products_table_exists = mycursor.fetchone()

if not sales_table_exists:
    mycursor.execute('''
        CREATE TABLE sales (
            id INT AUTO_INCREMENT PRIMARY KEY,
            invoice_id VARCHAR(255),
            branch VARCHAR(1),
            city VARCHAR(255),
            customer_type VARCHAR(255),
            gender VARCHAR(255),
            product_line VARCHAR(255),
            unit_price DECIMAL(10, 2),
            quantity INT,
            tax DECIMAL(10, 2),
            total_amount DECIMAL(10, 2),
            date DATE,
            time TIME,
            payment VARCHAR(255),
            cost_of_goods_sold DECIMAL(10, 2),
            gross_margin_percentage DECIMAL(10, 2),
            gross_income DECIMAL(10, 2),
            stratification_rating INT
        )
    ''')

if not products_table_exists:
    mycursor.execute('''
        CREATE TABLE products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            product_line VARCHAR(255),
            product_category VARCHAR(100),
            product_tax DECIMAL(2, 2),
            product_cogs DECIMAL(2, 2)
        )
    ''')

# Check if there is data in the sales and products tables
mycursor.execute("SELECT COUNT(*) FROM sales")
sales_data_count = mycursor.fetchone()[0]
mycursor.execute("SELECT COUNT(*) FROM products")
products_data_count = mycursor.fetchone()[0]

# Insert mock data into the products table if there is no data
if products_data_count == 0:
    # Generate 20 textile products with random names and categories
    products_data = []
    for _ in range(20):
        product_name = generate_product_name()
        product_category = get_random_category()
        product_tax = round(random.uniform(0.01, 0.20), 2)
        product_cogs = round(random.uniform(0.50, 0.80), 2)
        product_data = (product_name, product_category, product_tax, product_cogs)
        products_data.append(product_data)

    # Insert the products data into the products table
    insert_query = "INSERT INTO products (product_line, product_category, product_tax, product_cogs) VALUES (%s, %s, %s, %s)"
    mycursor.executemany(insert_query, products_data)
    mydb.commit()

# Insert mock data into the sales table if there is no data
if sales_data_count == 0:
    # Generate 1000 mock sales invoices
    data_set = []
    for _ in range(1000):
        invoice = {
            "invoice_id": "INV" + str(random.randint(1000, 9999)),
            "branch": chr(random.randint(65, 71)),  # ASCII values for A to G
            "city": random.choice(["Mumbai", "Delhi", "Bangalore", "Chennai", "Kolkata", "Hyderabad", "Ahmedabad", "Pune", "Surat", "Jaipur"]),
            "customer_type": random.choice(["Member", "Non-Member"]),
            "gender": random.choice(["Male", "Female"]),
            "product_line": get_random_category(),
            "unit_price": random.randint(100, 500000),
            "quantity": random.randint(1, 10),
            "tax": 0,
            "total_amount": 0,
            "date": get_random_date(),
            "time": get_random_time(),
            "payment": random.choice(["Cash", "eWallet"]),
            "cost_of_goods_sold": 0,
            "gross_margin_percentage": 0,
            "gross_income": 0,
            "stratification_rating": random.randint(1, 5)
        }
        invoice["tax"] = calculate_tax(invoice["unit_price"])
        invoice["total_amount"] = calculate_total(
            invoice["unit_price"], invoice["quantity"], invoice["tax"]
        )
        invoice["cost_of_goods_sold"] = calculate_cost_of_goods_sold(invoice["unit_price"])
        invoice["gross_margin_percentage"] = calculate_gross_margin_percentage(
            invoice["cost_of_goods_sold"], invoice["total_amount"]
        )
        invoice["gross_income"] = calculate_gross_income(
            invoice["cost_of_goods_sold"], invoice["quantity"]
        )
        data_set.append(invoice)

    # Insert the mock data into the sales table
    sql = "INSERT INTO sales (invoice_id, branch, city, customer_type, gender, product_line, unit_price, quantity, tax, total_amount, date, time, payment, cost_of_goods_sold, gross_margin_percentage, gross_income, stratification_rating) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    values = [
        (
            invoice["invoice_id"], invoice["branch"], invoice["city"], invoice["customer_type"],
            invoice["gender"], invoice["product_line"], invoice["unit_price"], invoice["quantity"],
            invoice["tax"], invoice["total_amount"], invoice["date"], invoice["time"], invoice["payment"],
            invoice["cost_of_goods_sold"], invoice["gross_margin_percentage"], invoice["gross_income"],
            invoice["stratification_rating"]
        ) for invoice in data_set
    ]
    mycursor.executemany(sql, values)
    mydb.commit()

Python Code for Sales MIS Dashboard app (GitHit Repository)

import mysql.connector
import datetime
import dash
import pandas as pd
import plotly.express as px
import dash_bootstrap_components as dbc
from dash.dash_table.Format import Group
from dash import Dash, dcc, html, Input, Output, State
from dash_table import DataTable


# MySQL connection details
host = "localhost"
user = "root"
password = "root"
database = "my_sales"

# Establish MySQL connection
conn = mysql.connector.connect(host=host, user=user, password=password, database=database)
cursor = conn.cursor()

# Fetch sales data from the MySQL database
sales_query = "SELECT * FROM sales"
cursor.execute(sales_query)
sales_data = cursor.fetchall()
sales_columns = [desc[0] for desc in cursor.description]
sales = pd.DataFrame(sales_data, columns=sales_columns)

# Fetch products data from the MySQL database
products_query = "SELECT * FROM products"
cursor.execute(products_query)
products_data = cursor.fetchall()
products_columns = [desc[0] for desc in cursor.description]
products = pd.DataFrame(products_data, columns=products_columns)

# Calculate the daily sales count
daily_sales_number = (
    sales["invoice_id"].groupby(sales["date"]).nunique().rename("Number of sales")
)

# Create a line chart for daily sales count
figure_daily_sales_number = px.line(
    daily_sales_number, title="Daily number of sales"
).update_layout(title_font_size=30)

# Calculate the 7-day moving average of daily revenue
m7d_mean_revenue = (
    sales["total_amount"].groupby(sales["date"]).sum().rolling(7, min_periods=7).mean()
)

# Create a line chart for 7-day moving average of daily revenue
figure_m7d_mean_revenue = px.line(
    m7d_mean_revenue, title="7-day moving average of daily revenue"
).update_layout(title_font_size=30)

# Create a pie chart for revenue distribution by product line
figure_product_line = px.pie(
    sales.groupby("product_line")["total_amount"].sum().reset_index(),
    names="product_line",
    values="total_amount",
    title="Product lines ratio"
).update_layout(title_font_size=30)

# Create a bar chart for revenue breakdown by city
figure_revenue_bycity = px.bar(
    sales.groupby("city")["total_amount"].sum().reset_index(),
    x="city",
    y="total_amount",
    title="Revenue by city"
).update_layout(title_font_size=30)

# Create a revenue breakdown table
sums = (
    sales[["total_amount", "tax", "cost_of_goods_sold", "gross_income"]]
    .sum()
    .rename("Value")
    .reset_index()
    .rename(columns={"index": "Item"})
)

sums_datatable = html.Div(
    [
        html.P(),
        html.Label(
            "Revenue breakdown",
            style={"font-size": "30px", "color": "grey"},
        ),
        html.P(),
        DataTable(
            data=sums.to_dict("records"),
            columns=[{"name": col, "id": col} for col in ["Item", "Value"]],
        ),
    ]
)



# Create the Dash application
app = dash.Dash(__name__, external_stylesheets=[dbc.themes.BOOTSTRAP])

view_sales_content = dbc.Card(
    dbc.CardBody(
        [
            html.H4("View Sales Data"),
            DataTable(
                id="view-sales-data",
                data=sales.to_dict("records"),
                sort_action='native',
                columns=[
                    {"name": col, "id": col}
                    for col in sales_columns
                ],
                style_data={
                    "whiteSpace": "normal",
                    "height": "auto",
                    "maxHeight": "none",
                },
                style_cell={"textAlign": "left"},
                style_table={"overflowY": "scroll"},
                page_size=20,
                editable=False,  # Disable editing for the table
                row_deletable=True,  # Enable row deletion
            ),
        ]
    )
)



view_product_line_content = dbc.Card(
    dbc.CardBody(
        [
            html.H4("View Product Line"),
            DataTable(
                id="view-product-line",
                data=products.to_dict("records"),
                sort_action='native',
                columns=[
                    {"name": col, "id": col}
                    for col in products_columns
                ],
                style_data={
                    "whiteSpace": "normal",
                    "height": "auto",
                    "maxHeight": "none",
                },
                style_cell={"textAlign": "left"},
                style_table={"overflowY": "scroll"},
                page_size=20,
                editable=False,  # Disable editing for the table
                row_deletable=True,  # Enable row deletion
                tooltip_duration=None,
            ),
        ]
    )
)


# Add Sales tab content
add_sales_content = dbc.Card(
    dbc.CardBody(
        [
            html.H4("Add Sales"),
            html.Label("Invoice ID"),
            dbc.Input(id="invoice-id-input", type="text", placeholder="Enter Invoice ID"),
            html.Label("Branch"),
            dbc.Select(
                id="branch-select",
                options=[
                    {"label": "A", "value": "A"},
                    {"label": "B", "value": "B"},
                    {"label": "C", "value": "C"},
                    {"label": "D", "value": "D"},
                    {"label": "E", "value": "E"},
                    {"label": "F", "value": "F"},
                    {"label": "G", "value": "G"},
                ],
                placeholder="Select Branch",
            ),
            html.Label("City"),
            dbc.Select(
                id="city-select",
                options=[
                    {"label": "Kolkata", "value": "Kolkata"},
                    {"label": "Delhi", "value": "Delhi"},
                    {"label": "Pune", "value": "Pune"},
                    {"label": "Mumbai", "value": "Mumbai"},
                    {"label": "Chennai", "value": "Chennai"},
                    {"label": "Bangalore", "value": "Bangalore"},
                ],
                placeholder="Select City",
            ),
            html.Label("Customer Type"),
            dbc.Select(
                id="customer-type-select",
                options=[
                    {"label": "Member", "value": "Member"},
                    {"label": "Non-Member", "value": "Non-Member"},
                ],
                placeholder="Select Customer Type",
            ),
            html.Label("Gender"),
            dbc.Select(
                id="gender-select",
                options=[
                    {"label": "Male", "value": "Male"},
                    {"label": "Female", "value": "Female"},
                    {"label": "Other", "value": "Other"},
                ],
                placeholder="Select Gender",
            ),
            html.Label("Product Line"),
            dcc.Dropdown(
                id="product-line-dropdown",
                options=[
                    {"label": row, "value": row} for row in products["product_line"].unique() if row is not None
                ],
                placeholder="Select Product Line",
            ),
            html.Label("Unit Price"),
            dbc.Input(id="unit-price-input", type="number", placeholder="Enter Unit Price"),
            html.Label("Quantity"),
            dbc.Input(id="quantity-input", type="number", placeholder="Enter Quantity"),
            html.Label("Payment"),
            dbc.Select(
                id="payment-select",
                options=[
                    {"label": "eWallet", "value": "eWallet"},
                    {"label": "Cash", "value": "Cash"},
                    {"label": "Credit Card", "value": "Credit Card"},
                ],
                placeholder="Select Payment",
            ),
            html.Label("Customer Satisfaction"),
            dbc.Select(
                id="customer-satisfaction-select",
                options=[
                    {"label": "1", "value": "1"},
                    {"label": "2", "value": "2"},
                    {"label": "3", "value": "3"},
                    {"label": "4", "value": "4"},
                    {"label": "5", "value": "5"},
                ],
                placeholder="Select Customer Satisfaction",
            ),html.P(""),
            dbc.Button("Submit", id="submit-sales-btn", color="primary", className="mr-2"),
        ]
    )
)


# Add Sales tab content
add_porduct_line_content = dbc.Card(
    dbc.CardBody(
        [
            html.H4("Add Product Line"),
            html.Label("Product Line"),
            dbc.Input(id="product-line-input", type="text", placeholder="Enter Product Line"),
            html.Label("Product Category"),
            dbc.Input(id="product-category-input", type="text", placeholder="Enter Product Category"),
            html.Label("Product Tax"),
            dbc.Input(id="product-tax-input", type="number", placeholder="Enter tax amount  eg. for 10% use 0.1"),
            html.Label("Product COGS (% of total amount)"),
            dbc.Input(id="product-cogs-input", type="number", placeholder="Enter COGS eg. for 62% use 0.62"),
            html.P(""),
            dbc.Button("Submit", id="submit-product-btn", color="primary", className="mr-2"),
        ]
    )
)



# Create the tabs
tabs = dbc.Tabs(
    [
        dbc.Tab(label="Sales KPIs", children=[
            dbc.Row(
                [
                    dbc.Col(
                        dcc.Graph(figure=figure_product_line),
                        width=4
                    ),
                    dbc.Col(
                        dcc.Graph(figure=figure_revenue_bycity),
                        width=4
                    ),
                    dbc.Col(
                        sums_datatable,
                        width=4
                    ),
                ],
                justify="center",
                align="center",
                style={"padding": "20px"},
            ),
            dcc.Graph(figure=figure_daily_sales_number),
            dcc.Graph(figure=figure_m7d_mean_revenue),
        ]),
        dbc.Tab(label="Add Sales", children=[
            add_sales_content,
        ]),
        dbc.Tab(label="View Sales Data", children=[
            view_sales_content,
        ]),
        dbc.Tab(label="Add Product Line", children=[
            add_porduct_line_content,
        ]),
        dbc.Tab(label="View Product Line", children=[
            view_product_line_content,
        ]),
    ],
    id="tabs",
    active_tab="Sales KPIs",
)


# Create the layout
app.layout = html.Div(
    [
        html.H1(
        children='Welcome to Sales MIS Dashboard',
        style={
            'textAlign': 'center'
        }
    ),
        tabs,
    ],
    style={"margin": "auto", "max-width": "100%"},
)

# Submit sales form
@app.callback(
    Output("invoice-id-input", "value"),
    Output("branch-select", "value"),
    Output("city-select", "value"),
    Output("customer-type-select", "value"),
    Output("gender-select", "value"),
    Output("product-line-dropdown", "value"),
    Output("unit-price-input", "value"),
    Output("quantity-input", "value"),
    Output("payment-select", "value"),
    Output("customer-satisfaction-select", "value"),
    [
        Input("submit-sales-btn", "n_clicks"),
    ],
    [
        State("invoice-id-input", "value"),
        State("branch-select", "value"),
        State("city-select", "value"),
        State("customer-type-select", "value"),
        State("gender-select", "value"),
        State("product-line-dropdown", "value"),
        State("unit-price-input", "value"),
        State("quantity-input", "value"),
        State("payment-select", "value"),
        State("customer-satisfaction-select", "value"),
    ],
)


def submit_sales_form(
    n_clicks, invoice_id, branch, city, customer_type, gender, product_line, unit_price, quantity, payment, customer_satisfaction
):
    if n_clicks:
        # Calculate tax and total amount
        tax_query = f"SELECT product_tax FROM products WHERE product_line = '{product_line}'"
        cursor.execute(tax_query)
        tax_result = cursor.fetchone()
        if tax_result is not None:
            tax = tax_result[0]
            total_amount = (unit_price * quantity) + (unit_price * quantity * tax)

            # Calculate cost of goods sold and gross income
            cogs_query = f"SELECT product_cogs FROM products WHERE product_line = '{product_line}'"
            cursor.execute(cogs_query)
            cogs_result = cursor.fetchone()
            if cogs_result is not None:
                cogs = cogs_result[0]
                cost_of_goods_sold = total_amount * cogs
                gross_income = cost_of_goods_sold * quantity

                # Insert sales record into the MySQL database
                insert_query = f"""
                INSERT INTO sales (invoice_id, branch, city, customer_type, gender, product_line, unit_price, quantity, tax,
                total_amount, date, time, payment, cost_of_goods_sold, gross_margin_percentage, gross_income,
                stratification_rating)
                VALUES ('{invoice_id}', '{branch}', '{city}', '{customer_type}', '{gender}', '{product_line}',
                {unit_price}, {quantity}, {tax}, {total_amount}, CURDATE(), CURTIME(), '{payment}', {cost_of_goods_sold},
                100 - ({cost_of_goods_sold} / {total_amount} * 100), {gross_income}, '{customer_satisfaction}')
                """
                cursor.execute(insert_query)
                conn.commit()

                # Show success message
                print("Sales record added successfully!")

                return "", None, None, None, None, None, None, None, None, None
            else:
                print(f"No cost of goods sold found for product line: {product_line}")
        else:
            print(f"No tax found for product line: {product_line}")

    return invoice_id, branch, city, customer_type, gender, product_line, unit_price, quantity, payment, customer_satisfaction


# Submit Products form
@app.callback(
    Output("product-line-input", "value"),
    Output("product-category-input", "value"),
    Output("product-tax-input", "value"),
    Output("product-cogs-input", "value"),
    [
        Input("submit-product-btn", "n_clicks"),
    ],
    [
        State("product-line-input", "value"),
        State("product-category-input", "value"),
        State("product-tax-input", "value"),
        State("product-cogs-input", "value"),
    ],
)

def submit_products_form(
    n_clicks, product_line, product_category, product_tax, product_cogs
):
    if n_clicks:
        # Calculate tax and total amount
        product_query = f"INSERT INTO products (product_line, product_category, product_tax, product_cogs) VALUES ('{product_line}', '{product_category}', {product_tax}, {product_cogs})"
        cursor.execute(product_query)
        conn.commit()
        print("Sales record added successfully!")

        return "", None, None, None
    return product_line, product_category, product_tax, product_cogs

# Handle row deletion in the sales table
@app.callback(
    Output("view-product-line", "data"),
    [Input("view-product-line", "data_previous")],
    [State("view-product-line", "data")],
)
def delete_sales_row(previous_data, current_data):
    if previous_data and current_data and len(previous_data) > len(current_data):
        # Identify the deleted rows by comparing the lengths of previous and current data
        deleted_ids = set(d["id"] for d in previous_data) - set(d["id"] for d in current_data)
        if deleted_ids:
            # Filter out the deleted rows
            products_filtered = [row for row in current_data if row["id"] not in deleted_ids]
            
            # Delete the rows from the SQL Server database
            conn = mysql.connector.connect(host=host, user=user, password=password, database=database)
            cursor = conn.cursor()
            for deleted_id in deleted_ids:
                delete_query = "DELETE FROM products WHERE id = %s"
                cursor.execute(delete_query, (deleted_id,))
            conn.commit()
            cursor.close()
            conn.close()
            
            return products_filtered

    return current_data

# Handle row deletion in the sales table
@app.callback(
    Output("view-sales-data", "data"),
    [Input("view-sales-data", "data_previous")],
    [State("view-sales-data", "data")],
)
def delete_sales_row(previous_data, current_data):
    if previous_data and current_data and len(previous_data) > len(current_data):
        # Identify the deleted rows by comparing the lengths of previous and current data
        deleted_ids = set(d["id"] for d in previous_data) - set(d["id"] for d in current_data)
        if deleted_ids:
            # Filter out the deleted rows
            sales_filtered = [row for row in current_data if row["id"] not in deleted_ids]
            
            # Delete the rows from the SQL Server database
            conn = mysql.connector.connect(host=host, user=user, password=password, database=database)
            cursor = conn.cursor()
            for deleted_id in deleted_ids:
                delete_query = "DELETE FROM sales WHERE id = %s"
                cursor.execute(delete_query, (deleted_id,))
            conn.commit()
            cursor.close()
            conn.close()
            
            return sales_filtered

    return current_data

# Run the Dash application
if __name__ == "__main__":
    app.run_server(debug=True)

The Python code presented in this article demonstrates the power of a Sales MIS dashboard in empowering small business owners to track their sales data effectively. By centralizing and visualizing data, small business owners gain valuable insights into their business performance, identify growth opportunities, and make informed decisions. The code provides a foundation for small business owners to build a customized MIS dashboard tailored to their specific needs. With this tool at their disposal, small business owners can navigate the complexities of sales management, drive growth, and thrive in the competitive marketplace.