Enhanced PHP File Manager

⬆️ Go Up (..)

📊 Server Information

Server:

LiteSpeed

System:

Linux 5.14.0-503.34.1.el9_5.x86_64

PHP Version:

8.2.30

User:

u329947844

Directory:

/home/u329947844/domains/ecommer.in/public_html/billing/printing

Free Space:

10.27 TB

🔧 Quick Actions

✏️ Editing: stock-history.php

<?php
// stock-history.php
session_start();
require_once 'includes/db.php';

$message = '';

// Handle stock adjustment
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['adjust_stock'])) {
    $product_id = intval($_POST['product_id']);
    $adjustment_type = $_POST['adjustment_type'];
    $quantity = floatval($_POST['quantity']);
    $notes = trim($_POST['notes'] ?? '');
    
    // Get current product details
    $product_sql = "SELECT name, total_quantity, quantity_unit FROM products WHERE id = $product_id";
    $product_result = mysqli_query($conn, $product_sql);
    
    if (mysqli_num_rows($product_result) > 0) {
        $product = mysqli_fetch_assoc($product_result);
        $current_quantity = $product['total_quantity'];
        $product_name = $product['name'];
        $quantity_unit = $product['quantity_unit'];
        
        // Calculate new quantity
        if ($adjustment_type === 'add') {
            $new_quantity = $current_quantity + $quantity;
            $change_type = 'stock_added';
        } elseif ($adjustment_type === 'remove') {
            $new_quantity = $current_quantity - $quantity;
            if ($new_quantity < 0) {
                $message = '<div class="alert alert-danger">Cannot remove more stock than available!</div>';
            } else {
                $change_type = 'stock_removed';
            }
        } elseif ($adjustment_type === 'set') {
            $new_quantity = $quantity;
            $change_type = 'stock_set';
        }
        
        if (!isset($message)) {
            // Update product quantity
            $update_sql = "UPDATE products SET total_quantity = $new_quantity, updated_at = NOW() WHERE id = $product_id";
            
            if (mysqli_query($conn, $update_sql)) {
                // Record stock history
                $history_sql = "INSERT INTO stock_history (product_id, change_type, previous_quantity, new_quantity, quantity_change, notes, created_by, created_at) 
                               VALUES ($product_id, '$change_type', $current_quantity, $new_quantity, " . ($new_quantity - $current_quantity) . ", '" . mysqli_real_escape_string($conn, $notes) . "', 'admin', NOW())";
                
                if (mysqli_query($conn, $history_sql)) {
                    $message = '<div class="alert alert-success">Stock updated successfully! ' . $product_name . ' now has ' . $new_quantity . ' ' . $quantity_unit . ' in stock.</div>';
                } else {
                    $message = '<div class="alert alert-warning">Stock updated but history not recorded: ' . mysqli_error($conn) . '</div>';
                }
            } else {
                $message = '<div class="alert alert-danger">Error updating stock: ' . mysqli_error($conn) . '</div>';
            }
        }
    } else {
        $message = '<div class="alert alert-danger">Product not found!</div>';
    }
}

// Check if stock_history table exists, if not create it
$check_table_sql = "SHOW TABLES LIKE 'stock_history'";
$table_result = mysqli_query($conn, $check_table_sql);

if (mysqli_num_rows($table_result) == 0) {
    // Create stock_history table
    $create_table_sql = "CREATE TABLE stock_history (
        id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        product_id INT(11) NOT NULL,
        change_type ENUM('stock_added', 'stock_removed', 'stock_set', 'sale', 'purchase', 'adjustment') NOT NULL,
        previous_quantity DECIMAL(10,2) NOT NULL,
        new_quantity DECIMAL(10,2) NOT NULL,
        quantity_change DECIMAL(10,2) NOT NULL,
        notes TEXT NULL,
        created_by VARCHAR(100) DEFAULT 'system',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    if (mysqli_query($conn, $create_table_sql)) {
        // Migrate existing data from bill_products to stock_history
        $migrate_sql = "INSERT INTO stock_history (product_id, change_type, previous_quantity, new_quantity, quantity_change, notes, created_by, created_at)
                       SELECT 
                           bp.product_id,
                           'sale' as change_type,
                           p.total_quantity + bp.quantity as previous_quantity,
                           p.total_quantity as new_quantity,
                           -bp.quantity as quantity_change,
                           CONCAT('Sale - Bill #', bp.bill_id) as notes,
                           'system' as created_by,
                           b.created_at
                       FROM bill_products bp
                       JOIN bills b ON bp.bill_id = b.id
                       JOIN products p ON bp.product_id = p.id
                       ORDER BY b.created_at";
        
        mysqli_query($conn, $migrate_sql);
    }
}

// Get filter parameters
$product_filter = $_GET['product_id'] ?? '';
$change_type_filter = $_GET['change_type'] ?? '';
$date_from = $_GET['date_from'] ?? '';
$date_to = $_GET['date_to'] ?? '';
$search = $_GET['search'] ?? '';

// Build query with filters
$where_conditions = ["sh.product_id IS NOT NULL"];

if (!empty($product_filter)) {
    $where_conditions[] = "sh.product_id = " . intval($product_filter);
}

if (!empty($change_type_filter)) {
    $where_conditions[] = "sh.change_type = '" . mysqli_real_escape_string($conn, $change_type_filter) . "'";
}

if (!empty($date_from)) {
    $where_conditions[] = "DATE(sh.created_at) >= '" . mysqli_real_escape_string($conn, $date_from) . "'";
}

if (!empty($date_to)) {
    $where_conditions[] = "DATE(sh.created_at) <= '" . mysqli_real_escape_string($conn, $date_to) . "'";
}

if (!empty($search)) {
    $search_term = mysqli_real_escape_string($conn, $search);
    $where_conditions[] = "(p.name LIKE '%$search_term%' OR sh.notes LIKE '%$search_term%')";
}

$where_clause = '';
if (!empty($where_conditions)) {
    $where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
}

// Get total count for pagination
$count_sql = "SELECT COUNT(*) as total 
              FROM stock_history sh
              LEFT JOIN products p ON sh.product_id = p.id 
              $where_clause";
$count_result = mysqli_query($conn, $count_sql);
$total_history = mysqli_fetch_assoc($count_result)['total'];

// Pagination
$per_page = 15;
$total_pages = ceil($total_history / $per_page);
$current_page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$offset = ($current_page - 1) * $per_page;

// Get stock history with pagination
$sql = "SELECT sh.*, p.name as product_name, p.quantity_unit, p.price,
               CASE 
                   WHEN sh.change_type = 'stock_added' THEN 'Stock Added'
                   WHEN sh.change_type = 'stock_removed' THEN 'Stock Removed'
                   WHEN sh.change_type = 'stock_set' THEN 'Stock Set'
                   WHEN sh.change_type = 'sale' THEN 'Sale'
                   WHEN sh.change_type = 'purchase' THEN 'Purchase'
                   ELSE 'Adjustment'
               END as change_type_name,
               CASE 
                   WHEN sh.change_type IN ('stock_added', 'purchase') THEN 'success'
                   WHEN sh.change_type IN ('stock_removed', 'sale') THEN 'danger'
                   ELSE 'warning'
               END as change_type_class
        FROM stock_history sh
        LEFT JOIN products p ON sh.product_id = p.id
        $where_clause
        ORDER BY sh.created_at DESC
        LIMIT $offset, $per_page";

$result = mysqli_query($conn, $sql);

// Get products for dropdown and overview (INCLUDING PRICE FIELD)
$products_sql = "SELECT id, name, total_quantity, quantity_unit, price FROM products WHERE is_active = 1 ORDER BY name";
$products_result = mysqli_query($conn, $products_sql);

// Get statistics
$stats_sql = "SELECT 
                COUNT(*) as total_transactions,
                SUM(CASE WHEN sh.change_type IN ('stock_added', 'purchase') THEN sh.quantity_change ELSE 0 END) as total_added,
                SUM(CASE WHEN sh.change_type IN ('stock_removed', 'sale') THEN ABS(sh.quantity_change) ELSE 0 END) as total_removed,
                COUNT(DISTINCT sh.product_id) as products_affected
              FROM stock_history sh
              $where_clause";
$stats_result = mysqli_query($conn, $stats_sql);
$stats = mysqli_fetch_assoc($stats_result);
?>
<!doctype html>
<html lang="en">
<?php include('includes/head.php')?>
<body data-sidebar="dark">
<?php include('includes/pre-loader.php')?>
<div id="layout-wrapper">
    <?php include('includes/topbar.php')?>
    <div class="vertical-menu">
        <div data-simplebar class="h-100">
            <?php include('includes/sidebar.php')?>
        </div>
    </div>

<div class="row align-items-center mb-3">
                    <div class="col-12 d-flex justify-content-between align-items-center flex-wrap gap-2">
                        <h4 class="mb-0">Stock History & Management</h4>
                        <div class="d-flex flex-wrap gap-2">
                            <a href="manage-products.php" class="btn btn-secondary btn-sm">Manage Products</a>
                            <a href="dashboard.php" class="btn btn-primary btn-sm">Dashboard</a>
                        </div>
                    </div>
                </div>

<?php echo $message; ?>

<div class="row mb-4">
                    <div class="col-xl-3 col-md-6">
                        <div class="card card-hover">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h5 class="font-size-14 mb-0">Total Transactions</h5>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-exchange-alt text-primary h4"></i>
                                    </div>
                                </div>
                                <div class="mt-3">
                                    <h3 class="mb-1"><?php echo number_format($stats['total_transactions']); ?></h3>
                                    <p class="text-muted mb-0">Stock movements</p>
                                </div>
                            </div>
                        </div>
                    </div>

<div class="col-xl-3 col-md-6">
                        <div class="card card-hover">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h5 class="font-size-14 mb-0">Stock Added</h5>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-arrow-up text-success h4"></i>
                                    </div>
                                </div>
                                <div class="mt-3">
                                    <h3 class="mb-1"><?php echo number_format($stats['total_added'] ?? 0, 2); ?></h3>
                                    <p class="text-muted mb-0">Total additions</p>
                                </div>
                            </div>
                        </div>
                    </div>

<div class="col-xl-3 col-md-6">
                        <div class="card card-hover">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h5 class="font-size-14 mb-0">Stock Removed</h5>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-arrow-down text-danger h4"></i>
                                    </div>
                                </div>
                                <div class="mt-3">
                                    <h3 class="mb-1"><?php echo number_format($stats['total_removed'] ?? 0, 2); ?></h3>
                                    <p class="text-muted mb-0">Total reductions</p>
                                </div>
                            </div>
                        </div>
                    </div>

<div class="col-xl-3 col-md-6">
                        <div class="card card-hover">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h5 class="font-size-14 mb-0">Products Tracked</h5>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-cubes text-info h4"></i>
                                    </div>
                                </div>
                                <div class="mt-3">
                                    <h3 class="mb-1"><?php echo number_format($stats['products_affected']); ?></h3>
                                    <p class="text-muted mb-0">Active products</p>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-lg-4">
                        
                        <div class="card">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0">Adjust Stock</h5>
                            </div>
                            <div class="card-body">
                                <form method="POST">
                                    <div class="mb-3">
                                        <label class="form-label">Product <span class="text-danger">*</span></label>
                                        <select name="product_id" class="form-select" required>
                                            <option value="">Select Product</option>
                                            <?php 
                                            // Store products in array for reuse
                                            $all_products = [];
                                            while ($product = mysqli_fetch_assoc($products_result)): 
                                                $all_products[] = $product;
                                            ?>
                                                <option value="<?php echo $product['id']; ?>">
                                                    <?php echo htmlspecialchars($product['name']); ?> 
                                                    (Current: <?php echo $product['total_quantity']; ?> <?php echo $product['quantity_unit']; ?>)
                                                </option>
                                            <?php endwhile; ?>
                                        </select>
                                    </div>

<div class="mb-3">
                                        <label class="form-label">Adjustment Type <span class="text-danger">*</span></label>
                                        <select name="adjustment_type" class="form-select" required onchange="toggleQuantityField(this)">
                                            <option value="">Select Type</option>
                                            <option value="add">Add Stock</option>
                                            <option value="remove">Remove Stock</option>
                                            <option value="set">Set Stock Level</option>
                                        </select>
                                    </div>

<div class="mb-3">
                                        <label class="form-label" id="quantityLabel">Quantity to Add <span class="text-danger">*</span></label>
                                        <input type="number" name="quantity" class="form-control" 
                                               step="0.01" min="0.01" required 
                                               placeholder="Enter quantity">
                                    </div>

<div class="mb-3">
                                        <label class="form-label">Notes</label>
                                        <textarea name="notes" class="form-control" rows="3" 
                                                  placeholder="Reason for stock adjustment..."></textarea>
                                    </div>

<div class="d-grid">
                                        <button type="submit" name="adjust_stock" class="btn btn-primary">
                                            <i class="fas fa-sync-alt me-1"></i> Update Stock
                                        </button>
                                    </div>
                                </form>
                            </div>
                        </div>

<div class="card mt-4">
                            <div class="card-header bg-light">
                                <h5 class="card-title mb-0">Current Stock Overview</h5>
                            </div>
                            <div class="card-body">
                                <?php
                                $low_stock_count = 0;
                                
                                if (!empty($all_products)):
                                    foreach ($all_products as $product):
                                        $stock_value = $product['total_quantity'] * $product['price'];
                                        $is_low_stock = $product['total_quantity'] < 10; // Threshold for low stock
                                        if ($is_low_stock) $low_stock_count++;
                                ?>
                                        <div class="d-flex align-items-center mb-3 pb-2 border-bottom">
                                            <div class="flex-grow-1">
                                                <h6 class="mb-1"><?php echo htmlspecialchars($product['name']); ?></h6>
                                                <p class="text-muted mb-0 small">
                                                    <?php echo number_format($product['total_quantity'], 2); ?> <?php echo $product['quantity_unit']; ?>
                                                    <?php if ($is_low_stock): ?>
                                                        <span class="badge bg-danger ms-1">Low Stock</span>
                                                    <?php endif; ?>
                                                </p>
                                            </div>
                                            <div class="flex-shrink-0 text-end">
                                                <div class="text-primary fw-bold">₹<?php echo number_format($stock_value, 2); ?></div>
                                                <small class="text-muted">Stock Value</small>
                                            </div>
                                        </div>
                                    <?php endforeach; ?>
                                <?php else: ?>
                                    <div class="text-center py-3">
                                        <p class="text-muted mb-0">No products found</p>
                                    </div>
                                <?php endif; ?>

<?php if ($low_stock_count > 0): ?>
                                    <div class="alert alert-warning mt-3">
                                        <i class="fas fa-exclamation-triangle me-2"></i>
                                        <strong><?php echo $low_stock_count; ?> product(s)</strong> are running low on stock.
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>

<div class="col-lg-8">
                        
                        <div class="card mb-4">
                            <div class="card-body">
                                <form method="GET" class="row g-3">
                                    <div class="col-md-3">
                                        <label class="form-label">Product</label>
                                        <select name="product_id" class="form-select">
                                            <option value="">All Products</option>
                                            <?php 
                                            if (!empty($all_products)):
                                                foreach ($all_products as $product): 
                                            ?>
                                                <option value="<?php echo $product['id']; ?>" 
                                                    <?php echo $product_filter == $product['id'] ? 'selected' : ''; ?>>
                                                    <?php echo htmlspecialchars($product['name']); ?>
                                                </option>
                                            <?php 
                                                endforeach;
                                            endif; 
                                            ?>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-3">
                                        <label class="form-label">Change Type</label>
                                        <select name="change_type" class="form-select">
                                            <option value="">All Types</option>
                                            <option value="stock_added" <?php echo $change_type_filter === 'stock_added' ? 'selected' : ''; ?>>Stock Added</option>
                                            <option value="stock_removed" <?php echo $change_type_filter === 'stock_removed' ? 'selected' : ''; ?>>Stock Removed</option>
                                            <option value="stock_set" <?php echo $change_type_filter === 'stock_set' ? 'selected' : ''; ?>>Stock Set</option>
                                            <option value="sale" <?php echo $change_type_filter === 'sale' ? 'selected' : ''; ?>>Sale</option>
                                            <option value="purchase" <?php echo $change_type_filter === 'purchase' ? 'selected' : ''; ?>>Purchase</option>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label class="form-label">Date From</label>
                                        <input type="date" name="date_from" class="form-control" value="<?php echo htmlspecialchars($date_from); ?>">
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label class="form-label">Date To</label>
                                        <input type="date" name="date_to" class="form-control" value="<?php echo htmlspecialchars($date_to); ?>">
                                    </div>
                                    
                                    <div class="col-md-2 d-flex align-items-end">
                                        <div class="d-flex gap-2 w-100">
                                            <button type="submit" class="btn btn-primary flex-grow-1">Filter</button>
                                            <a href="stock-history.php" class="btn btn-outline-secondary">Reset</a>
                                        </div>
                                    </div>
                                </form>
                            </div>
                        </div>

<div class="card">
                            <div class="card-header bg-light d-flex justify-content-between align-items-center">
                                <h5 class="card-title mb-0">Stock History</h5>
                                <div class="text-muted small">
                                    Showing <?php echo min($per_page, mysqli_num_rows($result)); ?> of <?php echo $total_history; ?> records
                                </div>
                            </div>
                            <div class="card-body">
                                <?php if (mysqli_num_rows($result) > 0): ?>
                                <div class="table-responsive">
                                    <table class="table table-hover table-centered mb-0">
                                        <thead class="table-light">
                                            <tr>
                                                <th>Date & Time</th>
                                                <th>Product</th>
                                                <th>Type</th>
                                                <th class="text-end">Previous</th>
                                                <th class="text-end">Change</th>
                                                <th class="text-end">New</th>
                                                <th>Notes</th>
                                                <th>By</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php while ($history = mysqli_fetch_assoc($result)): ?>
                                            <tr>
                                                <td>
                                                    <div class="small text-muted">
                                                        <?php echo date('M j, Y', strtotime($history['created_at'])); ?>
                                                    </div>
                                                    <div class="small">
                                                        <?php echo date('h:i A', strtotime($history['created_at'])); ?>
                                                    </div>
                                                </td>
                                                <td>
                                                    <strong><?php echo htmlspecialchars($history['product_name']); ?></strong>
                                                    <div class="small text-muted">
                                                        <?php echo $history['quantity_unit']; ?>
                                                    </div>
                                                </td>
                                                <td>
                                                    <span class="badge bg-<?php echo $history['change_type_class']; ?>">
                                                        <?php echo $history['change_type_name']; ?>
                                                    </span>
                                                </td>
                                                <td class="text-end">
                                                    <span class="text-muted">
                                                        <?php echo number_format($history['previous_quantity'], 2); ?>
                                                    </span>
                                                </td>
                                                <td class="text-end">
                                                    <?php if ($history['quantity_change'] > 0): ?>
                                                        <span class="text-success">
                                                            <i class="fas fa-arrow-up me-1"></i>
                                                            +<?php echo number_format($history['quantity_change'], 2); ?>
                                                        </span>
                                                    <?php elseif ($history['quantity_change'] < 0): ?>
                                                        <span class="text-danger">
                                                            <i class="fas fa-arrow-down me-1"></i>
                                                            <?php echo number_format($history['quantity_change'], 2); ?>
                                                        </span>
                                                    <?php else: ?>
                                                        <span class="text-muted">0</span>
                                                    <?php endif; ?>
                                                </td>
                                                <td class="text-end">
                                                    <strong><?php echo number_format($history['new_quantity'], 2); ?></strong>
                                                </td>
                                                <td>
                                                    <small class="text-muted">
                                                        <?php echo htmlspecialchars($history['notes'] ?? 'No notes'); ?>
                                                    </small>
                                                </td>
                                                <td>
                                                    <small class="text-muted">
                                                        <?php echo htmlspecialchars($history['created_by']); ?>
                                                    </small>
                                                </td>
                                            </tr>
                                            <?php endwhile; ?>
                                        </tbody>
                                    </table>
                                </div>

<?php if ($total_pages > 1): ?>
                                <div class="row mt-3">
                                    <div class="col-12">
                                        <nav>
                                            <ul class="pagination justify-content-center">
                                                <li class="page-item <?php echo $current_page <= 1 ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $current_page - 1])); ?>">Previous</a>
                                                </li>
                                                
                                                <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                                                    <li class="page-item <?php echo $i == $current_page ? 'active' : ''; ?>">
                                                        <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $i])); ?>"><?php echo $i; ?></a>
                                                    </li>
                                                <?php endfor; ?>
                                                
                                                <li class="page-item <?php echo $current_page >= $total_pages ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $current_page + 1])); ?>">Next</a>
                                                </li>
                                            </ul>
                                        </nav>
                                    </div>
                                </div>
                                <?php endif; ?>

<?php else: ?>
                                <div class="text-center py-5">
                                    <i class="fas fa-history fa-3x text-muted mb-3"></i>
                                    <h5>No stock history found</h5>
                                    <p class="text-muted">No stock movements match your current filters.</p>
                                    <?php if (!empty($product_filter) || !empty($change_type_filter) || !empty($date_from)): ?>
                                        <a href="stock-history.php" class="btn btn-primary">Clear Filters</a>
                                    <?php else: ?>
                                        <p class="text-muted">Stock history will appear here when you make adjustments or sales.</p>
                                    <?php endif; ?>
                                </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

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

<?php include('includes/rightbar.php')?>
<?php include('includes/scripts.php')?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: stock-history.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation