Car Sales and Inventory Store Project In Flask

Creating an advanced-level database structure and Flask application for a Car Sales and Inventory Store involves designing a robust database schema, implementing CRUD operations, and creating a user-friendly interface using Bootstrap 5. Below is a detailed breakdown of the project.

1. Database Structure

The database will include the following tables:

• Users: For authentication and user roles.
• Cars: For storing car details.
• Inventory: For tracking car stock.
• Sales: For recording sales transactions.
• Customers: For storing customer details.
• Brands: For car brands.
• Models: For car models.

SQL Schema

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) `UNIQUE` NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'manager', 'sales') NOT NULL
);
CREATE TABLE Brands (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) `UNIQUE` NOT NULL
);
CREATE TABLE Models (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    brand_id INT,
    FOREIGN KEY (brand_id) REFERENCES Brands(id)
);
CREATE TABLE Cars (
    id INT PRIMARY KEY AUTO_INCREMENT,
    model_id INT,
    year `INT` NOT NULL,
    color VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    mileage INT,
    FOREIGN KEY (model_id) REFERENCES Models(id)
);
CREATE TABLE Inventory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    car_id INT UNIQUE,
    quantity INT DEFAULT 0,
    FOREIGN KEY (car_id) REFERENCES Cars(id)
);
CREATE TABLE Customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) `UNIQUE` NOT NULL,
    phone VARCHAR(15)
);
CREATE TABLE Sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    car_id INT,
    customer_id INT,
    sale_date `DATE` NOT NULL,
    sale_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (car_id) REFERENCES Cars(id),
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

2. Flask Application Structure

The Flask application will have the following structure:

car_sales_inventory/
│
├── app/
│   ├── __init__.py
│   ├── models.py
│   ├── routes/
│   │   ├── auth.py
│   │   ├── cars.py
│   │   ├── inventory.py
│   │   ├── sales.py
│   │   └── customers.py
│   ├── templates/
│   │   ├── base.html
│   │   ├── auth/
│   │   │   ├── login.html
│   │   │   └── register.html
│   │   ├── cars/
│   │   │   ├── list.html
│   │   │   ├── add.html
│   │   │   └── edit.html
│   │   ├── inventory/
│   │   │   ├── list.html
│   │   │   └── update.html
│   │   ├── sales/
│   │   │   ├── list.html
│   │   │   └── add.html
│   │   └── customers/
│   │       ├── list.html
│   │       ├── add.html
│   │       └── edit.html
│   └── static/
│       ├── css/
│       │   └── styles.css
│       └── js/
│           └── scripts.js
│
├── config.py
├── requirements.txt
└── run.py

3. Flask Code

1. app/__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager
from flask_bootstrap import Bootstrap5
db = SQLAlchemy()
login_manager = LoginManager()
def create_app():
    app = Flask(__name__)
    app.config.from_pyfile('config.py')
    db.init_app(app)
    login_manager.init_app(app)
    Bootstrap5(app)
    from .routes.auth import auth_bp
    from .routes.cars import cars_bp
    from .routes.inventory import inventory_bp
    from .routes.sales import sales_bp
    from .routes.customers import customers_bp
    app.register_blueprint(auth_bp)
    app.register_blueprint(cars_bp)
    app.register_blueprint(inventory_bp)
    app.register_blueprint(sales_bp)
    app.register_blueprint(customers_bp)
    return app

2. app/models.py

from . import db
from flask_login import UserMixin
class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    role = db.Column(db.Enum('admin', 'manager', 'sales'), nullable=False)
class Brand(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
class Model(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    brand_id = db.Column(db.Integer, db.ForeignKey('brand.id'))
class Car(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    model_id = db.Column(db.Integer, db.ForeignKey('model.id'))
    year = db.Column(db.Integer, nullable=False)
    color = db.Column(db.String(50))
    price = db.Column(db.Numeric(10, 2), nullable=False)
    mileage = db.Column(db.Integer)
class Inventory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    car_id = db.Column(db.Integer, db.ForeignKey('car.id'), unique=True)
    quantity = db.Column(db.Integer, default=0)
class Customer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    phone = db.Column(db.String(15))
class Sale(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    car_id = db.Column(db.Integer, db.ForeignKey('car.id'))
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'))
    sale_date = db.Column(db.Date, nullable=False)
    sale_price = db.Column(db.Numeric(10, 2), nullable=False)

3. app/routes/auth.py

from flask import Blueprint, render_template, redirect, url_for, flash
from flask_login import login_user, logout_user, login_required
from .models import User
from . import db
auth_bp = Blueprint('auth', __name__)
@auth_bp.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        user = User.query.filter_by(username=username).first()
        if user and user.verify_password(password):
            login_user(user)
            return redirect(url_for('cars.list'))
        flash('Invalid username or password')
    return render_template('auth/login.html')
@auth_bp.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('auth.login'))
@auth_bp.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        new_user = User(username=username, password_hash=generate_password_hash(password))
        db.session.add(new_user)
        db.session.commit()
        flash('Registration successful! Please log in.')
        return redirect(url_for('auth.login'))
    return render_template('auth/register.html')

4. app/routes/cars.py

from flask import Blueprint, render_template, request, redirect, url_for, flash
from .models import Car, Model, Inventory
from . import db
cars_bp = Blueprint('cars', __name__)
@cars_bp.route('/cars')
def list():
    cars = Car.query.all()
    return render_template('cars/list.html', cars=cars)
@cars_bp.route('/cars/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        model_id = request.form['model_id']
        year = request.form['year']
        color = request.form['color']
        price = request.form['price']
        mileage = request.form['mileage']
        new_car = Car(model_id=model_id, year=year, color=color, price=price, mileage=mileage)
        db.session.add(new_car)
        db.session.commit()
        flash('Car added successfully!')
        return redirect(url_for('cars.list'))
    models = Model.query.all()
    return render_template('cars/add.html', models=models)
@cars_bp.route('/cars/edit/<int:car_id>', methods=['GET', 'POST'])
def edit(car_id):
    car = Car.query.get_or_404(car_id)
    if request.method == 'POST':
        car.year = request.form['year']
        car.color = request.form['color']
        car.price = request.form['price']
        car.mileage = request.form['mileage']
        db.session.commit()
        flash('Car updated successfully!')
        return redirect(url_for('cars.list'))
    models = Model.query.all()
    return render_template('cars/edit.html', car=car, models=models)

5. app/routes/inventory.py

from flask import Blueprint, render_template, request, redirect, url_for, flash
from .models import Inventory, Car
from . import db
inventory_bp = Blueprint('inventory', __name__)
@inventory_bp.route('/inventory')
def list():
    inventory_items = Inventory.query.all()
    return render_template('inventory/list.html', inventory=inventory_items)
@inventory_bp.route('/inventory/update/<int:car_id>', methods=['GET', 'POST'])
def update(car_id):
    inventory_item = Inventory.query.filter_by(car_id=car_id).first()
    if request.method == 'POST':
        inventory_item.quantity = request.form['quantity']
        db.session.commit()
        flash('Inventory updated successfully!')
        return redirect(url_for('inventory.list'))
    return render_template('inventory/update.html', inventory_item=inventory_item)

6. app/routes/sales.py

from flask import Blueprint, render_template, request, redirect, url_for, flash
from .models import Sale, Car, Customer
from . import db
sales_bp = Blueprint('sales', __name__)
@sales_bp.route('/sales')
def list():
    sales = Sale.query.all()
    return render_template('sales/list.html', sales=sales)
@sales_bp.route('/sales/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        car_id = request.form['car_id']
        customer_id = request.form['customer_id']
        sale_date = request.form['sale_date']
        sale_price = request.form['sale_price']
        new_sale = Sale(car_id=car_id, customer_id=customer_id, sale_date=sale_date, sale_price=sale_price)
        db.session.add(new_sale)
        db.session.commit()
        flash('Sale recorded successfully!')
        return redirect(url_for('sales.list'))
    cars = Car.query.all()
    customers = Customer.query.all()
    return render_template('sales/add.html', cars=cars, customers=customers)

7. app/routes/customers.py

from flask import Blueprint, render_template, request, redirect, url_for, flash
from .models import Customer
from . import db
customers_bp = Blueprint('customers', __name__)
@customers_bp.route('/customers')
def list():
    customers = Customer.query.all()
    return render_template('customers/list.html', customers=customers)
@customers_bp.route('/customers/add', methods=['GET', 'POST'])
def add():
    if request.method == 'POST':
        name = request.form['name']
        email = request.form['email']
        phone = request.form['phone']
        new_customer = Customer(name=name, email=email, phone=phone)
        db.session.add(new_customer)
        db.session.commit()
        flash('Customer added successfully!')
        return redirect(url_for('customers.list'))
    return render_template('customers/add.html')
@customers_bp.route('/customers/edit/<int:customer_id>', methods=['GET', 'POST'])
def edit(customer_id):
    customer = Customer.query.get_or_404(customer_id)
    if request.method == 'POST':
        customer.name = request.form['name']
        customer.email = request.form['email']
        customer.phone = request.form['phone']
        db.session.commit()
        flash('Customer updated successfully!')
        return redirect(url_for('customers.list'))
    return render_template('customers/edit.html', customer=customer)

8. app/templates/base.html

<!DOCTYPE html>
<html lang=`en`>
<head>
    <meta charset=`UTF-8`>
    <meta name=`viewport` content=`width=device-width, initial-scale=1.0`>
    <link rel=`stylesheet` href=`https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css`>
    <title>{% block title %}Car Sales and Inventory{% endblock %}</title>
</head>
<body>
    <nav class=`navbar navbar-expand-lg navbar-light bg-light`>
        <div class=`container-fluid`>
            <a class=`navbar-brand` href=`{{ url_for('index') }}`>Car Sales</a>
            <div class=`collapse navbar-collapse`>
                <ul class=`navbar-nav me-auto`>
                    <li class=`nav-item`>
                        <a class=`nav-link` href=`{{ url_for('index') }}`>Home</a>
                    </li>
                    <li class=`nav-item`>
                        <a class=`nav-link` href=`{{ url_for('attendance') }}`>Attendance</a>
                    </li>
                    <li class=`nav-item`>
                        <a class=`nav-link` href=`{{ url_for('logout') }}`>Logout</a>
                    </li>
                </ul>
            </div>
        </div>
    </nav>
    <div class=`container mt-4`>
        {% with messages = get_flashed_messages() %}
            {% if messages %}
                <div class=`alert alert-info`>
                    {% for message in messages %}
                        <p>{{ message }}</p>
                    {% endfor %}
                </div>
            {% endif %}
        {% endwith %}
        {% block content %}{% endblock %}
    </div>
    <script src=`https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js`></script>
</body>
</html>

9. app/templates/index.html

{% extends 'base.html' %}
{% block content %}
<h1>Welcome to the Car Sales and Inventory System</h1>
<p>Manage your car sales and inventory efficiently!</p>
<a href=`{{ url_for('register') }}` class=`btn btn-primary`>Register</a>
<a href=`{{ url_for('login') }}` class=`btn btn-secondary`>Login</a>
{% endblock %}

10. app/templates/auth/login.html

{% extends 'base.html' %}
{% block content %}
<h2>Login</h2>
<form method=`POST`>
    <div class=`mb-3`>
        <label for=`username` class=`form-label`>Username</label>
        <input type=`text` class=`form-control` id=`username` name=`username` required>
    </div>
    <div class=`mb-3`>
        <label for=`password` class=`form-label`>Password</label>
        <input type=`password` class=`form-control` id=`password` name=`password` required>
    </div>
    <button type=`submit` class=`btn btn-primary`>Login</button>
</form>
{% endblock %}

11. app/templates/auth/register.html

{% extends 'base.html' %}
{% block content %}
<h2>Register</h2>
<form method=`POST`>
    <div class=`mb-3`>
        <label for=`username` class=`form-label`>Username</label>
        <input type=`text` class=`form-control` id=`username` name=`username` required>
    </div>
    <div class=`mb-3`>
        <label for=`password` class=`form-label`>Password</label>
        <input type=`password` class=`form-control` id=`password` name=`password` required>
    </div>
    <button type=`submit` class=`btn btn-primary`>Register</button>
</form>
{% endblock %}

12. app/templates/cars/list.html

{% extends 'base.html' %}
{% block title %}Cars{% endblock %}
{% block content %}
<h2>Cars</h2>
<a href=`{{ url_for('cars.add') }}` class=`btn btn-success`>Add Car</a>
<table class=`table mt-3`>
    <thead>
        <tr>
            <th>ID</th>
            <th>Model</th>
            <th>Year</th>
            <th>Price</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for car in cars %}
        <tr>
            <td>{{ car.id }}</td>
            <td>{{ car.model.name }}</td>
            <td>{{ car.year }}</td>
            <td>${{ car.price }}</td>
            <td>
                <a href=`{{ url_for('cars.edit', car_id=car.id) }}` class=`btn btn-warning`>Edit</a>
                <form action=`{{ url_for('cars.delete', car_id=car.id) }}` method=`POST` style=`display:inline;`>
                    <button type=`submit` class=`btn btn-danger`>Delete</button>
                </form>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

13. app/templates/cars/add.html

{% extends 'base.html' %}
{% block title %}Add Car{% endblock %}
{% block content %}
<h2>Add Car</h2>
<form method=`POST`>
    <div class=`mb-3`>
        <label for=`model_id` class=`form-label`>Model</label>
        <select class=`form-select` id=`model_id` name=`model_id` required>
            {% for model in models %}
            <option value=`{{ model.id }}`>{{ model.name }}</option>
            {% endfor %}
        </select>
    </div>
    <div class=`mb-3`>
        <label for=`year` class=`form-label`>Year</label>
        <input type=`number` class=`form-control` id=`year` name=`year` required>
    </div>
    <div class=`mb-3`>
        <label for=`color` class=`form-label`>Color</label>
        <input type=`text` class=`form-control` id=`color` name=`color`>
    </div>
    <div class=`mb-3`>
        <label for=`price` class=`form-label`>Price</label>
        <input type=`number` class=`form-control` id=`price` name=`price` required>
    </div>
    <div class=`mb-3`>
        <label for=`mileage` class=`form-label`>Mileage</label>
        <input type=`number` class=`form-control` id=`mileage` name=`mileage`>
    </div>
    <button type=`submit` class=`btn btn-primary`>Add Car</button>
</form>
{% endblock %}

14. app/templates/inventory/list.html

{% extends 'base.html' %}
{% block ```html
title %}Inventory{% endblock %}
{% block content %}
<h2>Inventory</h2>
<table class=`table mt-3`>
    <thead>
        <tr>
            <th>ID</th>
            <th>Car</th>
            <th>Quantity</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for item in inventory %}
        <tr>
            <td>{{ item.id }}</td>
            <td>{{ item.car.model.name }}</td>
            <td>{{ item.quantity }}</td>
            <td>
                <a href=`{{ url_for('inventory.update', car_id=item.car_id) }}` class=`btn btn-warning`>Update</a>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

15. app/templates/sales/list.html

{% extends 'base.html' %}
{% block title %}Sales{% endblock %}
{% block content %}
<h2>Sales</h2>
<a href=`{{ url_for('sales.add') }}` class=`btn btn-success`>Add Sale</a>
<table class=`table mt-3`>
    <thead>
        <tr>
            <th>ID</th>
            <th>Car</th>
            <th>Customer</th>
            <th>Sale Date</th>
            <th>Sale Price</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for sale in sales %}
        <tr>
            <td>{{ sale.id }}</td>
            <td>{{ sale.car.model.name }}</td>
            <td>{{ sale.customer.name }}</td>
            <td>{{ sale.sale_date }}</td>
            <td>${{ sale.sale_price }}</td>
            <td>
                <a href=`{{ url_for('sales.edit', sale_id=sale.id) }}` class=`btn btn-warning`>Edit</a>
                <form action=`{{ url_for('sales.delete', sale_id=sale.id) }}` method=`POST` style=`display:inline;`>
                    <button type=`submit` class=`btn btn-danger`>Delete</button>
                </form>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

16. app/templates/customers/list.html

{% extends 'base.html' %}
{% block title %}Customers{% endblock %}
{% block content %}
<h2>Customers</h2>
<a href=`{{ url_for('customers.add') }}` class=`btn btn-success`>Add Customer</a>
<table class=`table mt-3`>
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Phone</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for customer in customers %}
        <tr>
            <td>{{ customer.id }}</td>
            <td>{{ customer.name }}</td>
            <td>{{ customer.email }}</td>
            <td>{{ customer.phone }}</td>
            <td>
                <a href=`{{ url_for('customers.edit', customer_id=customer.id) }}` class=`btn btn-warning`>Edit</a>
                <form action=`{{ url_for('customers.delete', customer_id=customer.id) }}` method=`POST` style=`display:inline;`>
                    <button type=`submit` class=`btn btn-danger`>Delete</button>
                </form>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

17. Configuration

config.py

import os
class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'your_secret_key'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///site.db'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

18. Requirements

Flask
Flask-SQLAlchemy
Flask-Login
Flask-Bootstrap

19. Run the Application

run.py

from app import create_app
app = create_app()
if __name__ == '__main__':
    app.run(debug=True)

Conclusion

This structure provides a comprehensive foundation for a Car Sales and Inventory Store project using Flask and Bootstrap 5. You can expand upon this by adding features such as search functionality, user permissions, and more advanced reporting.