import mysql.
connector
def create_db_connection():
return mysql.connector.connect(
host="localhost",
user="your_username", # Replace with your MySQL username
password="your_password", # Replace with your MySQL password
database="billing_system"
def add_product(name, description, price, stock_quantity):
db = create_db_connection()
cursor = db.cursor()
query = """INSERT INTO products (name, description, price, stock_quantity)
VALUES (%s, %s, %s, %s)"""
values = (name, description, price, stock_quantity)
cursor.execute(query, values)
db.commit()
cursor.close()
db.close()
print("Product added successfully.")
def view_products():
db = create_db_connection()
cursor = db.cursor()
query = "SELECT * FROM products"
cursor.execute(query)
products = cursor.fetchall()
for product in products:
print(f"ID: {product[0]}, Name: {product[1]}, Price: {product[3]}, Stock: {product[4]}")
cursor.close()
db.close()
def add_customer(name, email, phone, address):
db = create_db_connection()
cursor = db.cursor()
query = """INSERT INTO customers (name, email, phone, address)
VALUES (%s, %s, %s, %s)"""
values = (name, email, phone, address)
cursor.execute(query, values)
db.commit()
cursor.close()
db.close()
print("Customer added successfully.")
def create_bill(customer_id, purchased_items):
db = create_db_connection()
cursor = db.cursor()
# Calculate the total bill amount
total_amount = 0
for product_id, quantity in purchased_items:
query = "SELECT price, stock_quantity FROM products WHERE product_id = %s"
cursor.execute(query, (product_id,))
result = cursor.fetchone()
if result:
price, stock_quantity = result
if stock_quantity < quantity:
print(f"Not enough stock for product ID {product_id}.")
return
total_amount += price * quantity
else:
print(f"Product ID {product_id} not found.")
return
# Insert bill record
query = "INSERT INTO bills (customer_id, total_amount) VALUES (%s, %s)"
cursor.execute(query, (customer_id, total_amount))
bill_id = cursor.lastrowid
# Insert bill items (products)
for product_id, quantity in purchased_items:
query = "INSERT INTO bill_items (bill_id, product_id, quantity, price) VALUES (%s, %s, %s,
%s)"
cursor.execute(query, (bill_id, product_id, quantity, result[0]))
# Update stock quantity
query = "UPDATE products SET stock_quantity = stock_quantity - %s WHERE product_id
= %s"
cursor.execute(query, (quantity, product_id))
db.commit()
cursor.close()
db.close()
print("Bill created successfully.")
def view_bills():
db = create_db_connection()
cursor = db.cursor()
query = """SELECT b.bill_id, c.name, b.bill_date, b.total_amount, b.payment_status
FROM bills b
JOIN customers c ON b.customer_id = c.customer_id"""
cursor.execute(query)
bills = cursor.fetchall()
for bill in bills:
print(f"Bill ID: {bill[0]}, Customer: {bill[1]}, Date: {bill[2]}, Total: {bill[3]}, Status: {bill[4]}")
cursor.close()
db.close()
def mark_as_paid(bill_id):
db = create_db_connection()
cursor = db.cursor()
query = "UPDATE bills SET payment_status = 'Paid' WHERE bill_id = %s"
cursor.execute(query, (bill_id,))
db.commit()
cursor.close()
db.close()
print(f"Bill {bill_id} marked as Paid.")
def main():
while True:
print("\nBilling System")
print("1. Add Product")
print("2. View Products")
print("3. Add Customer")
print("4. Create Bill")
print("5. View Bills")
print("6. Mark Bill as Paid")
print("7. Exit")
choice = input("Enter your choice: ")
if choice == '1':
name = input("Product Name: ")
description = input("Description: ")
price = float(input("Price: "))
stock_quantity = int(input("Stock Quantity: "))
add_product(name, description, price, stock_quantity)
elif choice == '2':
view_products()
elif choice == '3':
name = input("Customer Name: ")
email = input("Customer Email: ")
phone = input("Customer Phone: ")
address = input("Customer Address: ")
add_customer(name, email, phone, address)
elif choice == '4':
customer_id = int(input("Customer ID: "))
purchased_items = []
while True:
product_id = int(input("Product ID (or 0 to finish): "))
if product_id == 0:
break
quantity = int(input("Quantity: "))
purchased_items.append((product_id, quantity))
create_bill(customer_id, purchased_items)
elif choice == '5':
view_bills()
elif choice == '6':
bill_id = int(input("Enter Bill ID to mark as paid: "))
mark_as_paid(bill_id)
elif choice == '7':
break
else:
print("Invalid choice, please try again.")
main()
