Notes

ORDER-CREATE:

<?php

ob_start(); // Start output buffering
include('includes/header.php');

// Check if the form is submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
require_once('../config/function.php');

// Retrieve and validate input values
$customerId = validate($_POST['customer_id']);
$orderStatus = validate($_POST['order_status']);
$deliveryDate = validate($_POST['delivery_date']); // Delivery Date
$description = validate($_POST['description']); // Overall Description
$trackingNo = 'INV-' . rand(100000, 999999);
$discount = isset($_POST['discount']) ? validate($_POST['discount']) : 0; // Default to 0
$advance = isset($_POST['advance'][0]) ? validate($_POST['advance'][0]) : 0; // Default to 0
$due = isset($_POST['due'][0]) ? validate($_POST['due'][0]) : 0; // Default to 0

$totalAmount = 0;

// Retrieve product and order details
$products = $_POST['products']; // Array of product IDs
$quantities = $_POST['quantities']; // Corresponding quantities
$units = $_POST['units']; // Unit
$productDescriptions = $_POST['descriptions']; // Individual product descriptions
$discounts = $_POST['discounts']; // Discounts for individual products
$advances = $_POST['advance']; // Advances
$dues = $_POST['due']; // Dues

foreach ($products as $index => $productId) {
$quantity = $quantities[$index];
$productQuery = "SELECT price FROM products WHERE id='$productId' LIMIT 1";
$productResult = mysqli_query($conn, $productQuery);
$product = mysqli_fetch_assoc($productResult);

$productDiscount = $discounts[$index] ?? 0; // Individual product discount
$totalAmount += ($product['price'] * $quantity) - $productDiscount; // Calculate total considering discount
}

// Insert the order
$orderQuery = "INSERT INTO orders (customer_id, tracking_no, total_amount, order_status, discount, advance, due, delivery_date, description, order_date)
VALUES ('$customerId', '$trackingNo', '$totalAmount', '$orderStatus', '$discount', '$advance', '$due', '$deliveryDate', '$description', NOW())";
$orderResult = mysqli_query($conn, $orderQuery);
if ($orderResult) {
$orderId = mysqli_insert_id($conn);

// Insert order items and update product quantities
foreach ($products as $index => $productId) {
$quantity = $quantities[$index];
$productQuery = "SELECT price, quantity FROM products WHERE id='$productId' LIMIT 1"; // Use 'quantity' directly
$productResult = mysqli_query($conn, $productQuery);
$product = mysqli_fetch_assoc($productResult);

if ($product['quantity'] < $quantity) { // Compare directly with 'quantity'
echo "<div class='alert alert-danger'>Not enough stock!</div>";
exit; // Stop the process if insufficient stock
}

$price = $product['price'];
$unit = $units[$index];
$productDiscount = $discounts[$index] ?? 0;
$productAdvance = $advances[$index] ?? 0;
$productDue = $dues[$index] ?? 0;
$productDescription = $productDescriptions[$index]; // Retrieve product description

$orderItemQuery = "INSERT INTO order_items (order_id, product_id, price, quantity, discount, advance, due, unit, descriptions)
VALUES ('$orderId', '$productId', '$price', '$quantity', '$productDiscount', '$productAdvance', '$productDue', '$unit', '$productDescription')";
mysqli_query($conn, $orderItemQuery);

// Update the product quantity in stock
$newQuantity = $product['quantity'] - $quantity; // Adjust the stock
$updateProductQuery = "UPDATE products SET quantity='$newQuantity' WHERE id='$productId'";
mysqli_query($conn, $updateProductQuery);
}

// Redirect to invoice page
header("Location: invoice.php?order_id=$orderId");
exit;
} else {
echo '<div class="alert alert-danger">Failed to create order. Please try again.</div>';
}
}
?>

<!-- HTML Form -->
<div class="container-fluid px-4">
<div class="card mt-4 shadow-sm">
<div class="card-header">
<h4 class="mb-0">Create Order</h4>
</div>
<div class="card-body">
<form method="POST" action="">
<div class="mb-3">
<label for="customer_id" class="form-label">Customer</label>
<select id="customer_id" name="customer_id" class="form-select mySelect2" required>
<option value="" disabled selected>Select Customer</option>
<?php
$customers = mysqli_query($conn, "SELECT * FROM customers");
while ($customer = mysqli_fetch_assoc($customers)) {
echo "<option value='{$customer['id']}'>{$customer['name']}</option>";
}
?>
</select>
</div>

<div class="mb-3">
<div class="col-md-2">
<label for="delivery_date" class="form-label">Delivery Date</label>
<input type="date" id="delivery_date" name="delivery_date" class="form-control" required>
</div>
</div>

<div class="mb-3">
<label for="description" class="form-label">Order Description</label>
<textarea id="description" name="description" class="form-control" rows="3" required></textarea>
</div>

<div id="productList">
<div class="row mb-3">
<div class="col-md-6">
<label for="products[]" class="form-label">Product</label>
<select name="products[]" class="form-select mySelect2" required>
<option value="" disabled selected>Select Product</option>
<?php
$products = mysqli_query($conn, "SELECT * FROM products");
while ($product = mysqli_fetch_assoc($products)) {
echo "<option value='{$product['id']}' data-price='{$product['price']}'>{$product['name']}</option>";
}
?>
</select>
</div>
<div class="col-md-3">
<label for="quantities[]" class="form-label">Quantity</label>
<input type="number" name="quantities[]" class="form-control quantity-input" min="1" required>
</div>
<div class="col-md-3">
<label for="units[]" class="form-label">Unit</label>
<select name="units[]" class="form-select" required>
<option value="Piece">Piece</option>
<option value="Set">Set</option>
<option value="Yard">Yard</option>
<option value="kg">kg</option>
<option value="Other">Other</option>
</select>
</div>
<div class="col-md-6">
<label for="descriptions[]" class="form-label">Description</label>
<textarea name="descriptions[]" class="form-control" rows="2" required></textarea>
</div>
<div class="col-md-3">
<label for="totals[]" class="form-label">Total</label>
<input type="text" name="totals[]" class="form-control total-input" readonly>
</div>
<div class="col-md-3">
<label for="discounts[]" class="form-label">Discount</label>
<input type="number" name="discounts[]" class="form-control discount-input" step="0.01" min="0" required>
</div>
<div class="col-md-3">
<label for="advance[]" class="form-label">Advance</label>
<input type="number" name="advance[]" class="form-control advance-input" step="0.01" min="0" required>
</div>
<div class="col-md-3">
<label for="due[]" class="form-label">Due</label>
<input type="number" name="due[]" class="form-control due-input" step="0.01" readonly>
</div>
</div>
</div>

<button type="button" id="addProduct" class="btn btn-success mb-3">Add Another Product</button>
<div class="mb-3">
<label for="order_status" class="form-label">Order Status</label>
<select id="order_status" name="order_status" class="form-select" required>
<option value="" disabled selected>Select Status</option>
<option value="sale">Sale</option>
<option value="order">Order</option>
</select>
</div>
<button type="submit" class="btn btn-primary">Create Order</button>
</form>
</div>
</div>
</div>

<script>
function calculateTotal(row) {
const productSelect = row.querySelector('select[name="products[]"]');
const quantityInput = row.querySelector('input[name="quantities[]"]');
const totalInput = row.querySelector('input[name="totals[]"]');
const discountInput = row.querySelector('input[name="discounts[]"]');
const advanceInput = row.querySelector('input[name="advance[]"]');
const dueInput = row.querySelector('input[name="due[]"]');

const price = parseFloat(productSelect.options[productSelect.selectedIndex]?.getAttribute('data-price')) || 0;
const quantity = parseFloat(quantityInput.value) || 0;
const discount = parseFloat(discountInput.value) || 0;
const advance = parseFloat(advanceInput.value) || 0;

const total = (price * quantity) - discount;
totalInput.value = total.toFixed(2);

const due = total - advance;
dueInput.value = due.toFixed(2);
}

document.addEventListener('input', function(event) {
if (event.target.matches('select[name="products[]"], input[name="quantities[]"], input[name="discounts[]"], input[name="advance[]"]')) {
const row = event.target.closest('.row');
calculateTotal(row);
}
});

document.getElementById('addProduct').addEventListener('click', function() {
const productList = document.getElementById('productList');
const newProductRow = `
<div class="row mb-3">
<div class="col-md-6">
<label for="products[]" class="form-label">Product</label>
<select name="products[]" class="form-select" required>
<option value="" disabled selected>Select Product</option>
<?php
$products = mysqli_query($conn, "SELECT * FROM products");
while ($product = mysqli_fetch_assoc($products)) {
echo "<option value='{$product['id']}' data-price='{$product['price']}'>{$product['name']}</option>";
}
?>
</select>
</div>
<div class="col-md-3">
<label for="quantities[]" class="form-label">Quantity</label>
<input type="number" name="quantities[]" class="form-control quantity-input" min="1" required>
</div>
<div class="col-md-3">
<label for="units[]" class="form-label">Unit</label>
<select name="units[]" class="form-select" required>
<option value="Piece">Piece</option>
<option value="Set">Set</option>
<option value="Yard">Yard</option>
<option value="kg">kg</option>
<option value="Other">Other</option>
</select>
</div>
<div class="col-md-6">
<label for="descriptions[]" class="form-label">Description</label>
<textarea name="descriptions[]" class="form-control" rows="2" required></textarea>
</div>
<div class="col-md-3">
<label for="totals[]" class="form-label">Total</label>
<input type="text" name="totals[]" class="form-control total-input" readonly>
</div>
<div class="col-md-3">
<label for="discounts[]" class="form-label">Discount</label>
<input type="number" name="discounts[]" class="form-control discount-input" step="0.01" min="0" required>
</div>
<div class="col-md-3">
<label for="advance[]" class="form-label">Advance</label>
<input type="number" name="advance[]" class="form-control advance-input" step="0.01" min="0" required>
</div>
<div class="col-md-3">
<label for="due[]" class="form-label">Due</label>
<input type="number" name="due[]" class="form-control due-input" step="0.01" readonly>
</div>
</div>`;
productList.insertAdjacentHTML('beforeend', newProductRow);
});
</script>

<?php include('includes/footer.php'); ?>
<?php ob_end_flush(); ?>




INVOICE:

<?php
include('includes/header.php');
require_once('../config/function.php');

// Check if order_id is provided
if (!isset($_GET['order_id'])) {
echo '<div class="alert alert-danger">No order ID provided.</div>';
exit;
}

$orderId = validate($_GET['order_id']);

// Fetch order details
$orderQuery = "SELECT o.*, c.name AS customer_name, c.phone AS customer_phone, c.email AS customer_email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.id = '$orderId'";
$orderResult = mysqli_query($conn, $orderQuery);

if (!$orderResult || mysqli_num_rows($orderResult) == 0) {
echo '<div class="alert alert-danger">Order not found.</div>';
exit;
}

$order = mysqli_fetch_assoc($orderResult);

// Fetch order items
$orderItemsQuery = "SELECT oi.*, p.name AS product_name
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE oi.order_id = '$orderId'";
$orderItemsResult = mysqli_query($conn, $orderItemsQuery);

if (!$orderItemsResult) {
echo '<div class="alert alert-danger">Failed to fetch order items.</div>';
exit;
}

$orderItems = [];
while ($item = mysqli_fetch_assoc($orderItemsResult)) {
$orderItems[] = $item;
}

// Calculate totals
$totalAdvance = array_sum(array_column($orderItems, 'advance'));
$totalDue = array_sum(array_column($orderItems, 'due'));
?>

<div class="container-fluid px-12">
<div class="card mt-12 shadow-sm">
<div class="card-header">
<h4 class="mb-0">Invoice</h4>
</div>
<div class="row mb-12">
<div class="col-md-4">
<h5>Customer Info</h5>
<p>Name: <?= htmlspecialchars($order['customer_name']); ?></p>
<p>Email: <?= htmlspecialchars($order['customer_email']); ?></p>
<p>Phone: <?= htmlspecialchars($order['customer_phone']); ?></p>
</div>
<div class="col-md-4 text-center">
<h3 style="font-weight: bold; margin: 0;">RJ Group Ltd</h3>
<p style="margin: 0;">Rahim Foam Market</p>
<p style="margin: 0;">Sylhet, Bangladesh</p>
</div>
<div class="col-md-4 text-end">
<h5>Additional Info</h5>
<p>Order Date: <?= htmlspecialchars($order['order_date']); ?></p>
<p>Tracking No: <?= htmlspecialchars($order['tracking_no']); ?></p>
<p>Delivery Date: <?= htmlspecialchars($order['delivery_date']); ?></p>
<p>Prepared By: <?= $_SESSION['loggedInUser']['name']; ?></p>
</div>
</div>

<div class="mb-4">
<h5>Order-Wide Description</h5>
<p><?= htmlspecialchars($order['description']); ?></p>
</div>

<div class="mb-4">
<h5>Order Items</h5>
<table class="table table-bordered">
<thead>
<tr>
<th>Product</th>
<th>Item Description</th>
<th>Price</th>
<th>Quantity</th>
<th>Discount</th>
<th>Total</th>
</tr>
</thead>
<tbody>
<?php foreach ($orderItems as $item) : ?>
<tr>
<td><?= htmlspecialchars($item['product_name']); ?></td>
<td><?= htmlspecialchars($item['descriptions']); ?></td>
<td><?= number_format($item['price'], 2); ?></td>
<td><?= $item['quantity']; ?></td>
<td><?= number_format($item['discount'], 2); ?></td>
<td><?= number_format(($item['price'] * $item['quantity']) - $item['discount'], 2); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
<tfoot>
<tr>
<td colspan="5" class="text-end">Grand Total:</td>
<td><?= number_format($order['total_amount'], 2); ?></td>
</tr>
<tr>
<td colspan="5" class="text-end">Total Advance Paid:</td>
<td><?= number_format($totalAdvance, 2); ?></td>
</tr>
<tr>
<td colspan="5" class="text-end">Total Due Amount:</td>
<td><?= number_format($totalDue, 2); ?></td>
</tr>
</tfoot>
</table>
</div>

<div class="text-end">
<button class="btn btn-primary" onclick="window.print()">Print Invoice</button>
</div>
</div>
</div>

<?php include('includes/footer.php'); ?>





ORDERS:


<?php include('includes/header.php'); ?>

<div class="container-fluid px-4">
<div class="card mt-4 shadow-sm">
<div class="card-header">
<div class="row">
<div class="col-md-4">
<h4 class="mb-0">Orders</h4>
</div>
<div class="col-md-8">
<form action="" method="GET">
<div class="row g-1">
<div class="col-md-3">
<label for="start_date">From</label>
<input type="date"
name="from_date"
class="form-control"
value="<?= isset($_GET['from_date']) ? $_GET['from_date'] : ''; ?>"
/>
</div>
<div class="col-md-3">
<label for="end_date">To</label>
<input type="date"
name="to_date"
class="form-control"
value="<?= isset($_GET['to_date']) ? $_GET['to_date'] : ''; ?>"
/>
</div>
<div class="col-md-3">
<button type="submit" class="btn btn-primary">Filter</button>
<a href="orders.php" class="btn btn-danger">Reset</a>
</div>
</div>
</form>
</div>
</div>
</div>
<div class="card-body">
<div class="mb-3">
<input type="text" id="searchInput" class="form-control" placeholder="Search orders..." />
</div>
<?php
if(isset($_GET['from_date']) || isset($_GET['to_date']) || isset($_GET['payment_status'])){
$fromDate = validate($_GET['from_date']);
$toDate = validate($_GET['to_date']);
$paymentStatus = validate($_GET['payment_status']);

if($fromDate != '' && $toDate != '' && $paymentStatus == ''){
$query = "SELECT o.*, c.*, oi.quantity, p.minimum_sale_rate
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
WHERE o.order_date BETWEEN '$fromDate' AND '$toDate'
ORDER BY o.id DESC";

} elseif($fromDate != '' && $toDate != '' && $paymentStatus != '') {
$query = "SELECT o.*, c.*, oi.quantity, p.minimum_sale_rate
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
WHERE o.order_date BETWEEN '$fromDate' AND '$toDate'
AND o.payment_mode='$paymentStatus'
ORDER BY o.id DESC";

} elseif ($fromDate != '' && $toDate == '' && $paymentStatus == '') {
$query = "SELECT o.*, c.*, oi.quantity, p.minimum_sale_rate
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
WHERE o.order_date >= '$fromDate'
ORDER BY o.id DESC";

} elseif ($fromDate == '' && $toDate != '' && $paymentStatus == '') {
$query = "SELECT o.*, c.*, oi.quantity, p.minimum_sale_rate
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
WHERE o.order_date <= '$toDate'
ORDER BY o.id DESC";

} elseif ($paymentStatus != '') {
$query = "SELECT o.*, c.*, oi.quantity, p.minimum_sale_rate
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
WHERE o.payment_mode='$paymentStatus'
ORDER BY o.id DESC";

} else {
$query = "SELECT o.*, c.*, oi.quantity, p.minimum_sale_rate
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
ORDER BY o.id DESC";
}
} else {
$query = "SELECT o.*, c.*, oi.quantity, p.minimum_sale_rate
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
ORDER BY o.id DESC";
}
$orders = mysqli_query($conn, $query);
if($orders){
if(mysqli_num_rows($orders) > 0)
{
?>
<table class="table table-striped table-bordered align-items-center justify-content-center" id="ordersTable">
<thead>
<tr>
<th>Tracking No.</th>
<th>C Name</th>
<th>C Phone</th>
<th>Description</th>
<th>Order Date</th>
<th>Order Status</th>
<th>Delivery Date</th>
<th>Due</th>
<th>Total Sale</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php foreach($orders as $orderItem) : ?>
<tr
class="<?= ($orderItem['total_amount'] < ($orderItem['minimum_sale_rate'] * $orderItem['quantity'])) ? 'table-danger' : ''; ?>"
data-due="<?= $orderItem['due']; ?>"
data-total-sale="<?= $orderItem['total_amount']; ?>"
>
<td class="fw-bold"><?= $orderItem['tracking_no']; ?></td>
<td><?= $orderItem['name']; ?></td>
<td><?= $orderItem['phone']; ?></td>
<td><?= $orderItem['description']; ?></td>
<td><?= date('d M, Y', strtotime($orderItem['order_date'])); ?></td>
<td><?= $orderItem['order_status']; ?></td>
<td><?= $orderItem['delivery_date']; ?></td>
<td><?= $orderItem['due']; ?></td>
<td><?= $orderItem['total_amount']; ?></td>
<td>
<a href="orders-view.php?track=<?= $orderItem['tracking_no']; ?>" class="btn btn-info mb-0 px-2 btn-sm">View</a>
<a href="orders-view-print.php?track=<?= $orderItem['tracking_no']; ?>" class="btn btn-primary mb-0 px-2 btn-sm">Print</a>
<a href="edit-orders.php?track=<?= $orderItem['tracking_no']; ?>" class="btn btn-warning mb-0 px-2 btn-sm">Edit</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
<tfoot>
<tr>
<th colspan="7" class="text-end">Total:</th>
<th id="totalDue">0.00</th>
<th id="totalSale">0.00</th>
<th></th>
</tr>
</tfoot>
</table>
<?php
}
else
{
echo "<h5>No Record Available</h5>";
}
}
else
{
echo "<h5>Something Went Wrong</h5>";
}
?>
</div>
</div>
</div>

<script>
document.addEventListener('DOMContentLoaded', function () {
const searchInput = document.getElementById('searchInput');
const rows = document.querySelectorAll('#ordersTable tbody tr');
const totalDueElement = document.getElementById('totalDue');
const totalSaleElement = document.getElementById('totalSale');

function calculateTotals() {
let totalDue = 0;
let totalSale = 0;
const uniqueInvoices = new Set();

rows.forEach(row => {
if (row.style.display !== 'none') { // Only include visible rows
const trackingNo = row.querySelector('td:first-child').textContent.trim();

if (!uniqueInvoices.has(trackingNo)) {
uniqueInvoices.add(trackingNo);
totalDue += parseFloat(row.getAttribute('data-due')) || 0;
totalSale += parseFloat(row.getAttribute('data-total-sale')) || 0;
}
}
});

totalDueElement.textContent = totalDue.toFixed(2);
totalSaleElement.textContent = totalSale.toFixed(2);
}

searchInput.addEventListener('keyup', function () {
const searchValue = searchInput.value.toLowerCase();

rows.forEach(row => {
const rowText = row.textContent.toLowerCase();
row.style.display = rowText.includes(searchValue) ? '' : 'none';
});

calculateTotals(); // Recalculate totals after filtering
});

calculateTotals(); // Initial calculation
});

</script>

<?php include('includes/footer.php'); ?>