import mysql.
connector as mysqltor
from datetime import date
# --- Database Connection ---
# Establish connection and cursor globally
try:
mycon = [Link](host='localhost', user='root', passwd='root',
database='billing')
mycur = [Link]()
except [Link] as err:
print("Error connecting to MySQL:", err)
exit()
# --- Product Functions ---
def Add_Product():
print("\n--- ADD NEW PRODUCT ---")
pid = input("Product ID (e.g., P001): ")
n = input("Product Name: ")
p = float(input("Unit Price: "))
s = int(input("Current Stock: "))
data = (pid, n, p, s)
sql = "INSERT INTO PRODUCTS (PRODUCT_ID, NAME, PRICE, STOCK) VALUES
(%s, %s, %s, %s)"
try:
[Link](sql, data)
[Link]()
print("\nProduct added successfully.")
except [Link] as e:
print("Error adding product:", e)
def Display_Products():
print("\n--- AVAILABLE PRODUCTS ---")
sql = "SELECT PRODUCT_ID, NAME, PRICE, STOCK FROM PRODUCTS ORDER BY
PRODUCT_ID"
[Link](sql)
data = [Link]()
if not data:
print("No products available in stock.")
return
print("ID | Name | Price | Stock")
print("-" * 45)
for rec in data:
print(rec[0], "|", rec[1], "|", rec[2], "|", rec[3])
# --- Invoice/Billing Functions ---
def Create_Invoice():
print("\n--- CREATE NEW INVOICE ---")
cust_name = input("Customer Name: ")
bill_date = [Link]()
items = []
total_amount = 0.0
while True:
pid = input("Enter Product ID to add (or 'done' to finish): ").upper()
if pid == 'DONE':
break
# 1. Check if product exists and get price
sql_check = "SELECT NAME, PRICE FROM PRODUCTS WHERE PRODUCT_ID =
%s"
[Link](sql_check, (pid,))
product_info = [Link]()
if not product_info:
print("Product ID not found.")
continue
p_name, p_price = product_info
try:
# Removed formatting specifier
qty = int(input("Enter quantity for " + p_name + " @ " + str(p_price) + ":
"))
if qty <= 0:
raise ValueError
except ValueError:
print("Invalid quantity. Please enter a positive number.")
continue
item_total = p_price * qty
[Link]((p_name, qty, p_price, item_total))
total_amount += item_total
print("Item added. Current total:", total_amount)
if not items:
print("Invoice cancelled. No items were added.")
return
# 2. Insert Invoice Header
sql_invoice = "INSERT INTO INVOICES (CUSTOMER_NAME, BILL_DATE,
TOTAL_AMOUNT) VALUES (%s, %s, %s)"
[Link](sql_invoice, (cust_name, bill_date, total_amount))
[Link]()
invoice_id = [Link]
print("\n" + "=" * 30)
print("INVOICE CREATED (ID:", invoice_id, ")")
print("Customer:", cust_name)
print("Date:", bill_date)
print("Items:")
for name, qty, price, total in items:
print(" -", name, "(", qty, "x", price, ") =", total)
print("TOTAL AMOUNT:", total_amount)
print("=" * 30)
def Display_Invoices():
print("\n--- ALL INVOICES ---")
sql = "SELECT INVOICE_ID, CUSTOMER_NAME, BILL_DATE, TOTAL_AMOUNT
FROM INVOICES ORDER BY INVOICE_ID DESC"
[Link](sql)
data = [Link]()
if not data:
print("No invoices found.")
return
print("ID | Date | Customer Name | Total")
print("-" * 50)
for rec in data:
print(rec[0], "|", str(rec[2]), "|", rec[1], "|", rec[3])
# --- Main Program Loop ---
def Main():
ch = 'y'
while ch in ['y', 'Y']:
print("\n" + "=" * 30)
print("==== BILLING MANAGEMENT SYSTEM ====")
print("=" * 30)
print("1. Products (Inventory)")
print("2. Invoices (Billing)")
print("-" * 30)
try:
table = int(input("Enter module no. (1 or 2):"))
except ValueError:
print("\nInvalid input. Please enter a number.")
continue
# --- Product Menu ---
if table == 1:
op = 'y'
while op in ['y', 'Y']:
print("\n**PRODUCT/INVENTORY MENU**")
print("1. Add New Product")
print("2. Display All Products")
try:
task = int(input("Enter task no. :"))
if task == 1:
Add_Product()
elif task == 2:
Display_Products()
else:
print("Enter valid choice (1-2)")
except ValueError:
print("Invalid input. Please enter a number.")
op = input("Continue in this module (y/n):").lower()
# --- Invoice Menu ---
elif table == 2:
op = 'y'
while op in ['y', 'Y']:
print("\n**INVOICE/BILLING MENU**")
print("1. Create New Invoice")
print("2. Display All Invoices")
try:
task = int(input("Enter task no. :"))
if task == 1:
Create_Invoice()
elif task == 2:
Display_Invoices()
else:
print("Enter valid choice (1-2)")
except ValueError:
print("Invalid input. Please enter a number.")
op = input("Continue in this module (y/n):").lower()
else:
print("Enter a valid module choice (1 or 2)")
ch = input("\nDo you want to continue with the Main Menu (y/n):").lower()
# Execute the main function
if mycon.is_connected():
Main()
[Link]()
[Link]()
print("\nProgram finished. Database connection closed.")