app.js
import mysql from 'mysql';
import express from 'express';
import bodyParser from 'body-parser';
import session from 'express-session';
const app = express();
// Middleware setup
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static('public'));
app.use(session({ secret: 'your-secret-key', resave: false, saveUninitialized: false }));
app.set('view engine', 'ejs');
// Create a MySQL connection pool
const con = mysql.createPool({
host: "localhost",
user: "root",
password: "",
database: "ecommerce_db",
});
// Utility function to execute database queries
function queryDatabase(query, params) {
return new Promise((resolve, reject) => {
con.query(query, params, (error, results) => {
if (error) {
reject(error);
} else {
resolve(results);
}
});
});
}
// Utility function to calculate total and prepare cart data
function calculateCartDetails(cart, productResults) {
const productMap = {};
productResults.forEach(product => {
productMap[product.id] = product;
});
let total = 0;
cart.forEach(item => {
const product = productMap[item.productId];
if (product) {
total += product.price * item.quantity; // Calculate total
}
});
return { productMap, total };
}
// Product Routes
app.get('/', async (req, res) => {
try {
const products = await queryDatabase('SELECT * FROM products', []);
res.render('pages/product', { products });
} catch (error) {
console.error('Database query error:', error);
return res.status(500).send('Internal Server Error');
}
});
// Cart Routes
app.get('/cart', async (req, res) => {
const cart = req.session.cart || []; // Get cart from session
if (cart.length === 0) {
return res.render('pages/cart', { cart, total: 0 });
}
const productIds = cart.map(item => item.productId);
const placeholders = productIds.map(() => '?').join(',');
try {
const productResults = await queryDatabase(`SELECT id, name, price FROM products WHERE id IN (${placeholders})`, productIds);
const { productMap, total } = calculateCartDetails(cart, productResults);
res.render('pages/cart', { cart, productMap, total });
} catch (error) {
console.error('Database query error:', error);
return res.status(500).send('Internal Server Error');
}
});
// Add to Cart (POST)
app.post('/cart/add', (req, res) => {
const productId = req.body.productId;
const quantity = parseInt(req.body.quantity, 10) || 1;
// Initialize cart if it doesn't exist
if (!req.session.cart) {
req.session.cart = [];
}
// Check if the product is already in the cart
const existingProduct = req.session.cart.find(item => item.productId === productId);
if (existingProduct) {
existingProduct.quantity += quantity; // Update quantity
} else {
req.session.cart.push({ productId, quantity }); // Add new product
}
res.redirect('/cart'); // Redirect to cart
});
// Remove from Cart (POST)
app.post('/cart/remove', (req, res) => {
const productId = req.body.productId;
// Filter out the product to remove
req.session.cart = req.session.cart.filter(item => item.productId !== productId);
res.redirect('/cart'); // Redirect to cart
});
// Payment Routes
app.get('/payment', async (req, res) => {
const cart = req.session.cart || [];
if (cart.length === 0) {
return res.redirect('/'); // Redirect to products if cart is empty
}
const productIds = cart.map(item => item.productId);
const placeholders = productIds.map(() => '?').join(',');
try {
const productResults = await queryDatabase(`SELECT id, name, price FROM products WHERE id IN (${placeholders})`, productIds);
const { productMap, total } = calculateCartDetails(cart, productResults);
res.render('pages/payment', { cart, productMap, total });
} catch (error) {
console.error('Database query error:', error);
return res.status(500).send('Internal Server Error');
}
});
// Payment Route
app.post('/payment', async (req, res) => {
const { orderId, total } = req.body;
try {
// Render the payment page with order details
res.render('pages/payment', { orderId, total });
} catch (error) {
console.error('Payment processing error:', error);
return res.status(500).send('Internal Server Error');
}
});
// Checkout Route (POST)
app.post('/checkout', async (req, res) => {
const { orderId, paymentMethod } = req.body;
try {
// Update order status to "paid" and save the payment method
await queryDatabase('UPDATE orders SET status = ?, payment_method = ? WHERE id = ?', ['paid', paymentMethod, orderId]);
// Clear the cart after payment
req.session.cart = [];
// Render a success page or send a confirmation message
res.render('pages/checkout', { orderId, paymentMethod });
} catch (error) {
console.error('Error updating payment status:', error);
return res.status(500).send('Internal Server Error');
}
});
// Start the server
app.listen(3001, () => {
console.log('Server running on http://localhost:3001');
});
// Order Route
app.get('/order', async (req, res) => {
const cart = req.session.cart || [];
if (cart.length === 0) {
return res.redirect('/'); // Redirect to products if cart is empty
}
const productIds = cart.map(item => item.productId);
const placeholders = productIds.map(() => '?').join(',');
try {
// Retrieve product details for the cart items
const productResults = await queryDatabase(
`SELECT id, name, price FROM products WHERE id IN (${placeholders})`,
productIds
);
const { productMap, total } = calculateCartDetails(cart, productResults);
// Insert order into the database with status "not paid"
const orderResult = await queryDatabase(
'INSERT INTO orders (status, total) VALUES (?, ?)',
['not paid', total]
);
const orderId = orderResult.insertId;
// Insert each cart item as an order item linked to the order ID
await Promise.all(
cart.map(item =>
queryDatabase(
'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)',
[orderId, item.productId, item.quantity, productMap[item.productId].price]
)
)
);
// Clear the cart after placing the order
req.session.cart = [];
// Render confirmation page with order details
res.render('pages/order_confirmation', { orderId, total });
} catch (error) {
console.error('Database query error:', error);
return res.status(500).send('Internal Server Error');
}
});
cart.ejs
<body>
<h1>Your Cart</h1>
<ul>
<% cart.forEach(item=> { %>
<li>
Product: <%= productMap[item.productId].name %> <br>
Price: $<%= productMap[item.productId].price.toFixed(2) %> <br>
Quantity: <%= item.quantity %> <br>
Total: $<%= (productMap[item.productId].price * item.quantity).toFixed(2) %><br>
<form action="/cart/remove" method="POST" style="display:inline;">
<input type="hidden" name="productId" value="<%= item.productId %>">
<button type="submit">Remove</button>
</form>
</li>
<% }) %>
</ul>
<h2>Total Price: $<%= total.toFixed(2) %>
</h2>
<a href="/order">Place Order</a>
<a href="/">Continue Shopping</a>
</body>
</html>
checkout.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Payment Success</title>
</head>
<body>
<h1>Pa
yment Successful!</h1>
<p>Thank you for your payment for Order #<%= orderId %>.</p>
<p>Your order status is now: Paid</p>
<a href="/">Return to Home</a>
</body>
</html>
order_confirmation.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Order Confirmation</title>
</head>
<body>
<h1>Order Confirmation</h1>
<p>Thank you for your order!</p>
<p>Order ID: <%= orderId %></p>
<p>Total Amount: $<%= total.toFixed(2) %></p>
<p>Status: Not Paid</p>
<!-- Payment Button -->
<form action="/payment" method="POST">
<input type="hidden" name="orderId" value="<%= orderId %>">
<input type="hidden" name="total" value="<%= total %>">
<button type="submit">Proceed to Payment</button>
</form>
</body>
</html>
payment.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Payment</title>
</head>
<body>
<h1>Payment for Order #<%= orderId %></h1>
<p>Total Amount: $<%= parseFloat(total).toFixed(2) %></p>
<!-- Payment Form with Payment Method Selection -->
<form action="/checkout" method="POST">
<input type="hidden" name="orderId" value="<%= orderId %>">
<h3>Select Payment Method:</h3>
<label>
<input type="radio" name="paymentMethod" value="Credit Card" required> Credit Card
</label><br>
<label>
<input type="radio" name="paymentMethod" value="PayPal" required> PayPal
</label><br>
<label>
<input type="radio" name="paymentMethod" value="Bank Transfer" required> Bank Transfer
</label><br>
<button type="submit">Complete Payment</button>
</form>
</body>
</html>
product.ejs
<body>
<header>
<%- include('../partials/header') %>
</header>
<main>
<h1>Products</h1>
<ul>
<% products.forEach(product => { %>
<li>
<h2><%= product.name %></h2>
<p>Price: $<%= product.price %></p>
<p>Description: <%= product.description %></p>
<img src="<%= product.image %>" alt="<%= product.name %>" />
<form action="/cart/add" method="POST">
<input type="hidden" name="productId" value="<%= product.id %>">
<input type="number" name="quantity" value="1" min="1">
<button type="submit">Add to Cart</button>
</form>
</li>
<% }) %>
</ul>
</main>
<footer>
<%- include('../partials/footer') %>
</footer>
</body>
</html>
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
status VARCHAR(20),
total DECIMAL(10, 2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10, 2),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
ALTER TABLE orders ADD COLUMN payment_method VARCHAR(50);
