Tkinter MySQL Daily Sales Report with DateEntry

This report module uses Python Tkinter with a DateEntry calendar to select a sales date. The selected date is sent to MySQL through SQLAlchemy, and the returned restaurant sale details are displayed in a Treeview.

The report shows each product sold on the selected day, including its product name, unit price, quantity, line total and bill number. A second query summarizes the stored bills and displays the number of bills, subtotal, tax and final sales total for that date.

This completes the reporting stage of our Restaurant Management project. If you need fresh data for today and previous days, first run the sample-data utility from Restaurant Management database installation.

DateEntry
    |
    v
Selected date
    |
    +--> plus2_sell + plus2_products
    |           |
    |           v
    |     Detailed Treeview report
    |
    +--> plus2_bill
                |
                v
        Bills / Subtotal / Tax
                |
                v
            Final Total

Daily Restaurant Sales Report Flow Top

The sales records are created in Restaurant Management Version 3. Each completed order creates one row in plus2_bill and one or more rows in plus2_sell.

The report uses both tables:

For a simpler example that loads database rows directly into a Treeview, see display MySQL records using Tkinter Treeview.

Select a Date with Tkinter DateEntry Top

The DateEntry widget from tkcalendar provides a calendar interface for selecting the report date.

from tkcalendar import DateEntry

cal=DateEntry(
    controls,
    selectmode='day',
    date_pattern='yyyy-mm-dd'
)

cal.grid(
    row=0,
    column=1,
    padx=8
)

The selected value can be retrieved directly as a Python date object:

selected_date=cal.get_date()

Because get_date() already returns a date object, we do not need to test the length of a date string before running the report.

Run the Report When the Date Changes Top

The older version connected DateEntry to a StringVar and used:

sel.trace('w',my_upd)

That approach works, but DateEntry also provides an event specifically for calendar selections:

cal.bind(
    '<<DateEntrySelected>>',
    load_report
)

This expresses the purpose more clearly: run the report after the user selects a date.

We also keep a Refresh Report button:

ttk.Button(
    controls,
    text='Refresh Report',
    command=load_report
)

This lets the user reload the currently displayed date whenever required.

Load Daily Product Sales from MySQL Top

The old query used:

SELECT * FROM plus2_sell WHERE bill_date=%s

The revised query lists the required columns explicitly and uses a named SQLAlchemy parameter:

detail_sql=text('''SELECT
    s.s_id,
    s.p_id,
    p.p_name,
    s.price,
    s.quantity,
    s.bill_no,
    s.bill_date
FROM plus2_sell AS s
INNER JOIN plus2_products AS p
    ON p.p_id=s.p_id
WHERE s.bill_date=:bill_date
ORDER BY s.bill_no,s.s_id''')

The parameter is passed separately:

params={
    'bill_date':selected_date
}

rows=conn.execute(
    detail_sql,
    params
).mappings().all()

We do not concatenate the selected date into the SQL statement.

Join the Product Name to Each Sale Top

plus2_sell stores the p_id of the sold product. The product name is available in plus2_products.

The report joins the two tables:

INNER JOIN plus2_products AS p
    ON p.p_id=s.p_id

This changes a report row from something like:

3 | 30.40 | 2 | 105

to something more useful to the user:

Item-3-BF | 30.40 | 2 | 60.80 | 105

Display the Daily Sales in Treeview Top

The Treeview uses named columns:

tree=ttk.Treeview(
    report_frame,
    columns=(
        'product',
        'price',
        'quantity',
        'line_total',
        'bill_no'
    ),
    show='headings'
)

Before loading another date, remove the old rows:

for item in tree.get_children():
    tree.delete(item)

Then calculate the line total from the stored sale price and quantity:

price=Decimal(str(row['price']))

line_total=(
    price*row['quantity']
).quantize(
    MONEY,
    rounding=ROUND_HALF_UP
)

The value is inserted along with the product information:

tree.insert(
    '',
    tk.END,
    iid=str(row['s_id']),
    values=(
        row['p_name'],
        f"{price:.2f}",
        row['quantity'],
        f"{line_total:.2f}",
        row['bill_no']
    )
)

The sale ID is suitable as the Treeview iid because s_id is the unique primary key of plus2_sell.

Calculate Bills, Subtotal, Tax and Final Total Top

The original report calculated only:

price * quantity

for all rows in plus2_sell. Version 3 already stores each completed bill's subtotal and tax in plus2_bill, so the report can also summarize those stored values.

summary_sql=text('''SELECT
    COUNT(*) AS bill_count,
    COALESCE(SUM(total),0) AS subtotal,
    COALESCE(SUM(tax),0) AS tax
FROM plus2_bill
WHERE bill_date=:bill_date''')

The returned values are converted to Decimal:

subtotal=Decimal(
    str(summary['subtotal'])
).quantize(MONEY)

tax=Decimal(
    str(summary['tax'])
).quantize(MONEY)

final_total=(subtotal+tax).quantize(MONEY)

The report can therefore show:

Bills       : 8
Subtotal    : 2450.00
Tax         : 245.00
Final Total : 2695.00

Handle a Date with No Sales Top

Some dates may not have any stored transactions.

The summary query still returns one row because it uses aggregate functions, while the detail query may return an empty list.

if rows:
    status_var.set(
        f'Sale rows: {len(rows)}'
    )
else:
    status_var.set(
        'No sales found for this date.'
    )

If you need sample transactions for today and recent dates, use the generator on the Restaurant Management installation page.

Complete Tkinter MySQL Daily Sales Report Top

import tkinter as tk
from tkinter import ttk
from decimal import Decimal, ROUND_HALF_UP
from tkcalendar import DateEntry
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from my_connect import engine

MONEY=Decimal('0.01')

detail_sql=text('''SELECT
    s.s_id,
    s.p_id,
    p.p_name,
    s.price,
    s.quantity,
    s.bill_no,
    s.bill_date
FROM plus2_sell AS s
INNER JOIN plus2_products AS p
    ON p.p_id=s.p_id
WHERE s.bill_date=:bill_date
ORDER BY s.bill_no,s.s_id''')

summary_sql=text('''SELECT
    COUNT(*) AS bill_count,
    COALESCE(SUM(total),0) AS subtotal,
    COALESCE(SUM(tax),0) AS tax
FROM plus2_bill
WHERE bill_date=:bill_date''')

root=tk.Tk()
root.geometry('950x650')
root.minsize(800,520)
root.title(
    'Restaurant Daily Sales Report - plus2net'
)

root.columnconfigure(
    0,
    weight=1
)
root.rowconfigure(
    2,
    weight=1
)

title_label=ttk.Label(
    root,
    text='Restaurant Daily Sales Report',
    font=('Times',22,'bold')
)
title_label.grid(
    row=0,
    column=0,
    sticky='w',
    padx=15,
    pady=(15,5)
)

controls=ttk.Frame(
    root,
    padding=(15,10)
)
controls.grid(
    row=1,
    column=0,
    sticky='ew'
)

ttk.Label(
    controls,
    text='Report Date:'
).grid(
    row=0,
    column=0,
    padx=(0,8)
)

cal=DateEntry(
    controls,
    selectmode='day',
    date_pattern='yyyy-mm-dd',
    width=12
)
cal.grid(
    row=0,
    column=1,
    padx=8
)

date_label_var=tk.StringVar()
status_var=tk.StringVar()

ttk.Label(
    controls,
    textvariable=date_label_var,
    font=('Times',14,'bold')
).grid(
    row=0,
    column=3,
    padx=20
)

report_frame=ttk.Frame(
    root,
    padding=(15,5)
)
report_frame.grid(
    row=2,
    column=0,
    sticky='nsew'
)

report_frame.columnconfigure(
    0,
    weight=1
)
report_frame.rowconfigure(
    0,
    weight=1
)

tree=ttk.Treeview(
    report_frame,
    columns=(
        'product',
        'price',
        'quantity',
        'line_total',
        'bill_no'
    ),
    show='headings',
    selectmode='browse'
)

tree.grid(
    row=0,
    column=0,
    sticky='nsew'
)

scrollbar=ttk.Scrollbar(
    report_frame,
    orient='vertical',
    command=tree.yview
)
scrollbar.grid(
    row=0,
    column=1,
    sticky='ns'
)

tree.configure(
    yscrollcommand=scrollbar.set
)

tree.heading(
    'product',
    text='Product'
)
tree.heading(
    'price',
    text='Price'
)
tree.heading(
    'quantity',
    text='Quantity'
)
tree.heading(
    'line_total',
    text='Line Total'
)
tree.heading(
    'bill_no',
    text='Bill No'
)

tree.column(
    'product',
    width=260,
    anchor='w'
)
tree.column(
    'price',
    width=100,
    anchor='e'
)
tree.column(
    'quantity',
    width=90,
    anchor='center'
)
tree.column(
    'line_total',
    width=110,
    anchor='e'
)
tree.column(
    'bill_no',
    width=90,
    anchor='center'
)

summary_frame=ttk.Frame(
    root,
    padding=(15,10)
)
summary_frame.grid(
    row=3,
    column=0,
    sticky='ew'
)

bill_count_var=tk.StringVar(value='0')
subtotal_var=tk.StringVar(value='0.00')
tax_var=tk.StringVar(value='0.00')
final_var=tk.StringVar(value='0.00')

ttk.Label(
    summary_frame,
    text='Bills:'
).grid(
    row=0,
    column=0,
    padx=5
)

ttk.Label(
    summary_frame,
    textvariable=bill_count_var
).grid(
    row=0,
    column=1,
    padx=5
)

ttk.Label(
    summary_frame,
    text='Subtotal:'
).grid(
    row=0,
    column=2,
    padx=(20,5)
)

ttk.Label(
    summary_frame,
    textvariable=subtotal_var
).grid(
    row=0,
    column=3,
    padx=5
)

ttk.Label(
    summary_frame,
    text='Tax:'
).grid(
    row=0,
    column=4,
    padx=(20,5)
)

ttk.Label(
    summary_frame,
    textvariable=tax_var
).grid(
    row=0,
    column=5,
    padx=5
)

ttk.Label(
    summary_frame,
    text='Final Total:',
    font=('Times',14,'bold')
).grid(
    row=0,
    column=6,
    padx=(20,5)
)

ttk.Label(
    summary_frame,
    textvariable=final_var,
    font=('Times',14,'bold')
).grid(
    row=0,
    column=7,
    padx=5
)

ttk.Label(
    root,
    textvariable=status_var
).grid(
    row=4,
    column=0,
    sticky='w',
    padx=15,
    pady=(0,12)
)

def clear_report():
    for item in tree.get_children():
        tree.delete(item)

    bill_count_var.set('0')
    subtotal_var.set('0.00')
    tax_var.set('0.00')
    final_var.set('0.00')

def load_report(event=None):
    selected_date=cal.get_date()

    date_label_var.set(
        selected_date.strftime(
            '%d-%B-%Y'
        )
    )

    clear_report()

    params={
        'bill_date':selected_date
    }

    try:
        with engine.connect() as conn:
            rows=conn.execute(
                detail_sql,
                params
            ).mappings().all()

            summary=conn.execute(
                summary_sql,
                params
            ).mappings().one()

    except SQLAlchemyError as e:
        print(e)
        status_var.set(
            'Unable to load the daily sales report.'
        )
        return

    for row in rows:
        price=Decimal(
            str(row['price'])
        ).quantize(
            MONEY,
            rounding=ROUND_HALF_UP
        )

        line_total=(
            price*row['quantity']
        ).quantize(
            MONEY,
            rounding=ROUND_HALF_UP
        )

        tree.insert(
            '',
            tk.END,
            iid=str(row['s_id']),
            values=(
                row['p_name'],
                f"{price:.2f}",
                row['quantity'],
                f"{line_total:.2f}",
                row['bill_no']
            )
        )

    subtotal=Decimal(
        str(
            summary['subtotal'] or 0
        )
    ).quantize(
        MONEY,
        rounding=ROUND_HALF_UP
    )

    tax=Decimal(
        str(
            summary['tax'] or 0
        )
    ).quantize(
        MONEY,
        rounding=ROUND_HALF_UP
    )

    final_total=(subtotal+tax).quantize(
        MONEY,
        rounding=ROUND_HALF_UP
    )

    bill_count_var.set(
        str(
            summary['bill_count']
        )
    )

    subtotal_var.set(
        f'{subtotal:.2f}'
    )

    tax_var.set(
        f'{tax:.2f}'
    )

    final_var.set(
        f'{final_total:.2f}'
    )

    if rows:
        status_var.set(
            f'Sale rows: {len(rows)}'
        )
    else:
        status_var.set(
            'No sales found for this date.'
        )

refresh_btn=ttk.Button(
    controls,
    text='Refresh Report',
    command=load_report
)
refresh_btn.grid(
    row=0,
    column=2,
    padx=8
)

cal.bind(
    '<<DateEntrySelected>>',
    load_report
)

load_report()

root.mainloop()

my_connect.py Top

As in the previous Restaurant Management pages, keep the SQLAlchemy Engine in a separate file.

from sqlalchemy import create_engine

engine=create_engine(
    'mysql+mysqldb://root:pw@localhost/db_name'
)

The report program imports text separately because that is where the SQL queries are defined.

Practical Extensions to This Report Top

This page is also a good starting point for several small practical Tkinter projects.

Report for a Date Range

Add two DateEntry widgets:

From Date
To Date

and retrieve rows using:

WHERE bill_date BETWEEN :from_date AND :to_date

Product-wise Sales Report

Group sale rows by product to calculate:

Product
Quantity Sold
Sales Value

This can show which menu products sell the most.

Bill-wise Report

Group records by bill_no to display individual invoices for a selected day.

Daily Sales Trend

Retrieve several days of data and display:

Date        Sales
01-Sep      2450.00
02-Sep      3120.00
03-Sep      2780.00

This can later become a chart or dashboard project.

Export the Report

A useful future extension is exporting selected-day sales to CSV or Excel so the restaurant owner can keep daily records outside the application.

Frequently Asked Questions Top

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: