Sales Reporting

Relevant source files

The following files were used as context for generating this wiki page:

• pos_app.py
• notification_service.py
• config.example.json
• CLAUDE.md
• build_exe.bat

The Sales Reporting system provides insights into sales performance, product trends, and inventory status. It is composed of two primary mechanisms: a manual, on-demand PDF report generator integrated into the main POS application, and an automated daily summary service that sends notifications via Telegram. Both systems rely on data stored in the sales and products tables within the pos_inventory.db SQLite database.

The manual reporting feature allows users to generate detailed PDF reports for any given date, including sales summaries, top-selling products, and a list of out-of-stock items. The automated service runs independently, sending a concise daily sales recap to a configured Telegram chat, focusing on key performance metrics and top products from the previous day.

Data Foundation

All sales reporting is derived from the sales and products tables in the pos_inventory.db database. When a sale is completed, a record is created for each item in the transaction.

Sources: pos_app.py:44-55, CLAUDE.md

Database Schema

The relationship between products and sales is central to all reporting. Each entry in the sales table is linked to a product in the products table via a foreign key.

Key Data Tables

sales Table

This table logs every individual product sold in a transaction.

Column	Type	Description
id	INTEGER	Primary Key for the sale record.
folio	TEXT	A unique identifier for a single transaction (e.g., "F-0001"). Groups multiple items from one sale.
product_id	INTEGER	Foreign key referencing products.id.
quantity	INTEGER	The number of units of the product sold in this transaction.
total	REAL	The total price for this line item (quantity * price).
timestamp	TEXT	The date and time the sale was recorded (YYYY-MM-DD HH:MM:SS).

Sources: pos_app.py:44-55

Manual PDF Reporting (GUI)

The main POS application includes a "Reportes" tab for generating detailed, A4-sized PDF sales reports for a specific date. These reports are saved in the reports/ directory.

Sources: pos_app.py:819-830, build_exe.bat:29-32

UI and Workflow

The user interface for manual reporting is created by the create_reports_tab method. It provides a date entry field and buttons to generate a report for the specified date, today, or yesterday.

The process for generating a manual report is as follows:

1. The user enters a date in YYYY-MM-DD format or clicks a shortcut button ("Reporte de Hoy", "Reporte de Ayer").
2. The manual_generate_report function is triggered.
3. It calls generate_sales_report_pdf with the selected date.
4. This function queries the database for sales summaries, top products, and zero-stock items.
5. The data is compiled into a PDF using the FPDF library.
6. The final PDF is saved to the reports/ folder, and a success message is shown to the user.

Sources: pos_app.py:801-863, pos_app.py:1468-1563

PDF Report Structure

The generated PDF report contains three main sections:

Section	Content	Source Queries
Resumen de Ventas	Total transactions, total items sold, and total revenue for the day.	get_sales_summary
Top 10 Productos Mas Vendidos	A table listing the top 10 products by units sold, including revenue and transaction count for each.	get_top_selling_products
Productos Sin Stock	A table of all products with a current quantity of 0.	get_zero_stock_products

Sources: pos_app.py:1499-1552

Automated Daily Reporting (Telegram Service)

An independent Python script, notification_service.py, provides automated daily sales reports via Telegram. This service runs in the background, polling the POS database and sending a summary at a configured time.

Sources: notification_service.py

Architecture

The service is composed of several key classes:

• NotificationService: The main orchestrator that starts and manages monitoring threads.
• POSDatabaseReader: Provides read-only access to the pos_inventory.db to fetch sales data without interfering with the main application.
• TelegramNotifier: Handles all communication with the Telegram Bot API.
• StateTracker: Manages a separate notification_state.db to prevent sending duplicate reports.

Sources: notification_service.py:20-494

Workflow

The automated reporting is handled by the daily_report_scheduler thread.

Configuration

The behavior of the automated report is controlled by the daily_report and notifications sections in config.example.json.

Parameter	File Section	Description	Default Value
send_time	daily_report	The time of day to send the report (HH:MM).	"08:00"
timezone	daily_report	The timezone for the scheduler.	"America/Mexico_City"
top_products_count	daily_report	The number of top products to include in the report.	10
metric	daily_report	The metric for ranking top products (quantity or revenue).	"quantity"
enabled	notifications.daily_report	Toggles the daily report feature on or off.	true
message_template	notifications.daily_report	The Markdown template for the Telegram message.	(See below)

Sources: config.example.json:9-14, 30-33

The message template defines the format of the Telegram notification:

"message_template": "📊 REPORTE DIARIO - {date}\\n\\n🏆 Top {count} Productos Más Vendidos:\\n\\n{products}\\n\\n💰 Ventas totales: ${total:.2f}\\n📦 Productos únicos vendidos: {unique_count}"

Sources: config.example.json:32-32

Core Reporting Queries

The reporting features are powered by a set of SQL queries encapsulated in the Database class in pos_app.py and the POSDatabaseReader class in notification_service.py.

Method	Description
get_sales_summary(start, end)	Calculates total transactions, items sold, and revenue for a date range.
get_top_selling_products(start, end, limit)	Ranks products by total quantity sold or revenue within a date range.
get_zero_stock_products()	Retrieves all products with a stock quantity of zero.
get_sales_by_date_range(start, end)	Fetches a detailed list of all sales transactions within a date range.

Sources: pos_app.py:586-649, notification_service.py:302-383

get_top_selling_products Query

This query aggregates sales data to find the best-performing products.

SELECT p.name, p.category, p.brand,
       SUM(s.quantity) as total_sold,
       SUM(s.total) as total_revenue,
       COUNT(DISTINCT s.folio) as transaction_count
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.timestamp BETWEEN ? AND ?
GROUP BY s.product_id
ORDER BY total_sold DESC
LIMIT ?

Sources: pos_app.py:609-623

get_sales_summary Query

This query provides high-level statistics for a given period.

SELECT COUNT(DISTINCT folio) as total_transactions,
       SUM(quantity) as total_items_sold,
       SUM(total) as total_revenue
FROM sales
WHERE timestamp BETWEEN ? AND ?

Sources: pos_app.py:631-637

Summary

The sales reporting system in this project offers a dual approach to business intelligence. The GUI-based manual reporting provides deep, flexible analysis for any historical date, producing professional PDF documents for record-keeping. Complementing this is the fully automated Telegram service, which delivers timely, high-level daily summaries directly to stakeholders, ensuring key performance indicators are always visible without manual intervention. Both systems are built upon a solid data foundation in the SQLite database, leveraging SQL queries to aggregate and present sales data effectively.