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/hifi11.in/public_html/medical/admin

Free Space:

10.27 TB

🔧 Quick Actions

✏️ Editing: stock-report.php

<!doctype html>
<html lang="en">

<?php 
include('includes/head.php');
include('includes/db_connection.php');

// Get filter parameters
$category_filter = isset($_GET['category_filter']) ? (int)$_GET['category_filter'] : 0;
$stock_status = isset($_GET['stock_status']) ? $_GET['stock_status'] : '';
$min_stock = isset($_GET['min_stock']) ? (int)$_GET['min_stock'] : 0;
$max_stock = isset($_GET['max_stock']) ? (int)$_GET['max_stock'] : 0;
$search_term = isset($_GET['search']) ? trim($_GET['search']) : '';
$sort_by = isset($_GET['sort_by']) ? $_GET['sort_by'] : 'stock_quantity';
$sort_order = isset($_GET['sort_order']) ? $_GET['sort_order'] : 'DESC';

// Validate sort parameters
$allowed_sort_columns = ['medicine_name', 'category_name', 'stock_quantity', 'selling_price', 'cost_price', 'total_value', 'expiry_date'];
$sort_by = in_array($sort_by, $allowed_sort_columns) ? $sort_by : 'stock_quantity';
$sort_order = in_array(strtoupper($sort_order), ['ASC', 'DESC']) ? strtoupper($sort_order) : 'DESC';

// Build WHERE clause for filtering
$where_conditions = ["m.status = 'active'"];
$query_params = [];
$param_types = '';

if ($category_filter > 0) {
    $where_conditions[] = "m.category_id = ?";
    $query_params[] = $category_filter;
    $param_types .= 'i';
}

if (!empty($stock_status)) {
    switch($stock_status) {
        case 'low':
            $where_conditions[] = "m.stock_quantity <= m.min_stock_level";
            break;
        case 'out':
            $where_conditions[] = "m.stock_quantity = 0";
            break;
        case 'expiring':
            $where_conditions[] = "m.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)";
            $where_conditions[] = "m.expiry_date > CURDATE()";
            break;
        case 'expired':
            $where_conditions[] = "m.expiry_date <= CURDATE()";
            break;
        case 'good':
            $where_conditions[] = "m.stock_quantity > m.min_stock_level";
            $where_conditions[] = "m.expiry_date > DATE_ADD(CURDATE(), INTERVAL 30 DAY)";
            break;
    }
}

if ($min_stock > 0) {
    $where_conditions[] = "m.stock_quantity >= ?";
    $query_params[] = $min_stock;
    $param_types .= 'i';
}

if ($max_stock > 0) {
    $where_conditions[] = "m.stock_quantity <= ?";
    $query_params[] = $max_stock;
    $param_types .= 'i';
}

if (!empty($search_term)) {
    $where_conditions[] = "(m.medicine_name LIKE ? OR m.generic_name LIKE ? OR m.batch_number LIKE ?)";
    $search_param = "%$search_term%";
    $query_params[] = $search_param;
    $query_params[] = $search_param;
    $query_params[] = $search_param;
    $param_types .= 'sss';
}

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

// Fetch stock data with filters
$stock_query = "
    SELECT 
        m.*,
        mc.category_name,
        (m.stock_quantity * m.cost_price) as total_cost_value,
        (m.stock_quantity * m.selling_price) as total_selling_value,
        (m.stock_quantity * (m.selling_price - m.cost_price)) as potential_profit,
        CASE 
            WHEN m.stock_quantity = 0 THEN 'Out of Stock'
            WHEN m.stock_quantity <= m.min_stock_level THEN 'Low Stock'
            WHEN m.expiry_date <= CURDATE() THEN 'Expired'
            WHEN m.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 'Expiring Soon'
            ELSE 'In Stock'
        END as stock_status,
        DATEDIFF(m.expiry_date, CURDATE()) as days_to_expiry,
        ROUND((m.stock_quantity / NULLIF(m.min_stock_level * 2, 0)) * 100, 2) as stock_percentage
    FROM medicines m
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    $where_clause
    ORDER BY $sort_by $sort_order, m.medicine_name ASC
";

$stmt = $conn->prepare($stock_query);
if (!empty($query_params)) {
    $stmt->bind_param($param_types, ...$query_params);
}
$stmt->execute();
$stock_result = $stmt->get_result();
$total_items = $stock_result->num_rows;

// Calculate summary statistics
$summary_query = "
    SELECT 
        COUNT(*) as total_items,
        SUM(m.stock_quantity) as total_quantity,
        SUM(m.stock_quantity * m.cost_price) as total_investment,
        SUM(m.stock_quantity * m.selling_price) as total_market_value,
        SUM(m.stock_quantity * (m.selling_price - m.cost_price)) as total_potential_profit,
        AVG(m.stock_quantity) as avg_stock,
        COUNT(CASE WHEN m.stock_quantity = 0 THEN 1 END) as out_of_stock,
        COUNT(CASE WHEN m.stock_quantity <= m.min_stock_level AND m.stock_quantity > 0 THEN 1 END) as low_stock,
        COUNT(CASE WHEN m.expiry_date <= CURDATE() THEN 1 END) as expired_items,
        COUNT(CASE WHEN m.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) AND m.expiry_date > CURDATE() THEN 1 END) as expiring_soon
    FROM medicines m
    $where_clause
";

$summary_stmt = $conn->prepare($summary_query);
if (!empty($query_params)) {
    $summary_stmt->bind_param($param_types, ...$query_params);
}
$summary_stmt->execute();
$summary_result = $summary_stmt->get_result();
$summary = $summary_result->fetch_assoc();

if (!$summary) {
    $summary = [
        'total_items' => 0,
        'total_quantity' => 0,
        'total_investment' => 0,
        'total_market_value' => 0,
        'total_potential_profit' => 0,
        'avg_stock' => 0,
        'out_of_stock' => 0,
        'low_stock' => 0,
        'expired_items' => 0,
        'expiring_soon' => 0
    ];
}

// Calculate profit margin percentage
$profit_margin = $summary['total_investment'] > 0 ? 
    ($summary['total_potential_profit'] / $summary['total_investment']) * 100 : 0;

// Get low stock items (below min level)
$low_stock_query = "
    SELECT 
        m.medicine_name,
        mc.category_name,
        m.stock_quantity,
        m.min_stock_level,
        m.expiry_date
    FROM medicines m
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE m.stock_quantity <= m.min_stock_level 
    AND m.stock_quantity > 0
    AND m.status = 'active'
    ORDER BY (m.stock_quantity / m.min_stock_level) ASC
    LIMIT 10
";

$low_stock_result = $conn->query($low_stock_query);

// Get expiring soon items (within 30 days)
$expiring_query = "
    SELECT 
        m.medicine_name,
        mc.category_name,
        m.stock_quantity,
        m.expiry_date,
        DATEDIFF(m.expiry_date, CURDATE()) as days_left
    FROM medicines m
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE m.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
    AND m.expiry_date > CURDATE()
    AND m.status = 'active'
    ORDER BY m.expiry_date ASC
    LIMIT 10
";

$expiring_result = $conn->query($expiring_query);

// Get top valued items
$top_value_query = "
    SELECT 
        m.medicine_name,
        mc.category_name,
        m.stock_quantity,
        m.cost_price,
        m.selling_price,
        (m.stock_quantity * m.cost_price) as total_value
    FROM medicines m
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE m.status = 'active'
    AND m.stock_quantity > 0
    ORDER BY total_value DESC
    LIMIT 10
";

$top_value_result = $conn->query($top_value_query);

// Get categories for filter
$categories_result = $conn->query("SELECT id, category_name FROM medicine_categories WHERE status = 'active' ORDER BY category_name");

// Get stock by category distribution
$category_distribution_query = "
    SELECT 
        mc.category_name,
        COUNT(m.id) as item_count,
        SUM(m.stock_quantity) as total_quantity,
        SUM(m.stock_quantity * m.cost_price) as total_investment
    FROM medicines m
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE m.status = 'active'
    GROUP BY mc.category_name
    ORDER BY total_investment DESC
";

$category_distribution_result = $conn->query($category_distribution_query);
?>

<?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="main-content">
        <div class="page-content">
           
            <div class="container-fluid">

<div class="row">
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($summary['total_items']); ?></h4>
                                        <p class="text-muted mb-0">Total Items</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-package-variant font-size-24 text-primary"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($summary['total_quantity']); ?></h4>
                                        <p class="text-muted mb-0">Total Quantity</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-counter font-size-24 text-success"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($summary['total_investment'], 2); ?></h4>
                                        <p class="text-muted mb-0">Total Investment</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-cash font-size-24 text-warning"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($summary['total_market_value'], 2); ?></h4>
                                        <p class="text-muted mb-0">Market Value</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-cash-multiple font-size-24 text-info"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($summary['total_potential_profit'], 2); ?></h4>
                                        <p class="text-muted mb-0">Potential Profit</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-chart-line font-size-24 text-success"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-xl-2 col-md-4">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($profit_margin, 2); ?>%</h4>
                                        <p class="text-muted mb-0">Profit Margin</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-percent font-size-24 text-danger"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-xl-3 col-md-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0 text-danger"><?php echo $summary['out_of_stock']; ?></h4>
                                        <p class="text-muted mb-0">Out of Stock</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-alert-circle font-size-24 text-danger"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0 text-warning"><?php echo $summary['low_stock']; ?></h4>
                                        <p class="text-muted mb-0">Low Stock</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-alert font-size-24 text-warning"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0 text-primary"><?php echo $summary['expiring_soon']; ?></h4>
                                        <p class="text-muted mb-0">Expiring Soon</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-clock-alert font-size-24 text-primary"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0 text-secondary"><?php echo $summary['expired_items']; ?></h4>
                                        <p class="text-muted mb-0">Expired Items</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-clock-remove font-size-24 text-secondary"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-3">Filter Stock Report</h4>
                                
                                <form method="GET" action="" class="row g-3">
                                    <div class="col-md-3">
                                        <label for="search" class="form-label">Search Medicine</label>
                                        <input type="text" class="form-control" id="search" name="search" 
                                               value="<?php echo htmlspecialchars($search_term); ?>" 
                                               placeholder="Name, Generic, Batch">
                                    </div>
                                    
                                    <div class="col-md-3">
                                        <label for="category_filter" class="form-label">Category</label>
                                        <select class="form-select" id="category_filter" name="category_filter">
                                            <option value="">All Categories</option>
                                            <?php if ($categories_result && $categories_result->num_rows > 0): ?>
                                                <?php while ($category = $categories_result->fetch_assoc()): ?>
                                                    <option value="<?php echo $category['id']; ?>" <?php echo $category_filter == $category['id'] ? 'selected' : ''; ?>>
                                                        <?php echo htmlspecialchars($category['category_name']); ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            <?php endif; ?>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label for="stock_status" class="form-label">Stock Status</label>
                                        <select class="form-select" id="stock_status" name="stock_status">
                                            <option value="">All Status</option>
                                            <option value="good" <?php echo $stock_status === 'good' ? 'selected' : ''; ?>>Good Stock</option>
                                            <option value="low" <?php echo $stock_status === 'low' ? 'selected' : ''; ?>>Low Stock</option>
                                            <option value="out" <?php echo $stock_status === 'out' ? 'selected' : ''; ?>>Out of Stock</option>
                                            <option value="expiring" <?php echo $stock_status === 'expiring' ? 'selected' : ''; ?>>Expiring Soon</option>
                                            <option value="expired" <?php echo $stock_status === 'expired' ? 'selected' : ''; ?>>Expired</option>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label for="min_stock" class="form-label">Min Quantity</label>
                                        <input type="number" class="form-control" id="min_stock" name="min_stock" 
                                               value="<?php echo $min_stock; ?>" min="0">
                                    </div>
                                    
                                    <div class="col-md-2">
                                        <label for="max_stock" class="form-label">Max Quantity</label>
                                        <input type="number" class="form-control" id="max_stock" name="max_stock" 
                                               value="<?php echo $max_stock; ?>" min="0">
                                    </div>
                                    
                                    <div class="col-md-4">
                                        <label for="sort_by" class="form-label">Sort By</label>
                                        <select class="form-select" id="sort_by" name="sort_by">
                                            <option value="stock_quantity" <?php echo $sort_by === 'stock_quantity' ? 'selected' : ''; ?>>Stock Quantity</option>
                                            <option value="medicine_name" <?php echo $sort_by === 'medicine_name' ? 'selected' : ''; ?>>Medicine Name</option>
                                            <option value="category_name" <?php echo $sort_by === 'category_name' ? 'selected' : ''; ?>>Category</option>
                                            <option value="selling_price" <?php echo $sort_by === 'selling_price' ? 'selected' : ''; ?>>Selling Price</option>
                                            <option value="cost_price" <?php echo $sort_by === 'cost_price' ? 'selected' : ''; ?>>Cost Price</option>
                                            <option value="total_value" <?php echo $sort_by === 'total_value' ? 'selected' : ''; ?>>Total Value</option>
                                            <option value="expiry_date" <?php echo $sort_by === 'expiry_date' ? 'selected' : ''; ?>>Expiry Date</option>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-4">
                                        <label for="sort_order" class="form-label">Sort Order</label>
                                        <select class="form-select" id="sort_order" name="sort_order">
                                            <option value="DESC" <?php echo $sort_order === 'DESC' ? 'selected' : ''; ?>>Descending</option>
                                            <option value="ASC" <?php echo $sort_order === 'ASC' ? 'selected' : ''; ?>>Ascending</option>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-12 mt-4">
                                        <button type="submit" class="btn btn-primary">
                                            <i class="mdi mdi-filter me-1"></i> Apply Filters
                                        </button>
                                        <a href="stock-report.php" class="btn btn-outline-secondary ms-2">
                                            <i class="mdi mdi-refresh me-1"></i> Reset Filters
                                        </a>
                                        <a href="export-stock.php?<?php echo http_build_query($_GET); ?>" class="btn btn-outline-success ms-2">
                                            <i class="mdi mdi-file-excel me-1"></i> Export Excel
                                        </a>
                                        <button type="button" class="btn btn-outline-info ms-2" onclick="printReport()">
                                            <i class="mdi mdi-printer me-1"></i> Print Report
                                        </button>
                                        <a href="add-medicine.php" class="btn btn-outline-primary ms-2">
                                            <i class="mdi mdi-plus me-1"></i> Add New Medicine
                                        </a>
                                        <a href="purchase-order.php" class="btn btn-outline-warning ms-2">
                                            <i class="mdi mdi-cart-plus me-1"></i> Create Purchase Order
                                        </a>
                                    </div>
                                    
                                    <?php if (!empty($search_term) || $category_filter > 0 || !empty($stock_status) || $min_stock > 0 || $max_stock > 0): ?>
                                        <div class="col-12 mt-2">
                                            <small class="text-muted">
                                                <i class="mdi mdi-filter me-1"></i> 
                                                <?php
                                                $filters = [];
                                                if (!empty($search_term)) $filters[] = "Search: $search_term";
                                                if ($category_filter > 0) {
                                                    $cat_name = '';
                                                    $cat_result = $conn->query("SELECT category_name FROM medicine_categories WHERE id = $category_filter");
                                                    if ($cat_result->num_rows > 0) {
                                                        $cat = $cat_result->fetch_assoc();
                                                        $cat_name = $cat['category_name'];
                                                    }
                                                    $filters[] = "Category: $cat_name";
                                                }
                                                if (!empty($stock_status)) $filters[] = "Status: " . ucfirst($stock_status);
                                                if ($min_stock > 0) $filters[] = "Min Qty: $min_stock";
                                                if ($max_stock > 0) $filters[] = "Max Qty: $max_stock";
                                                
                                                if (!empty($filters)) {
                                                    echo "Active filters: " . implode(' | ', $filters);
                                                }
                                                ?>
                                            </small>
                                        </div>
                                    <?php endif; ?>
                                </form>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center mb-4">
                                    <h4 class="card-title">Stock Summary</h4>
                                    <div class="text-muted">
                                        Showing <?php echo number_format($total_items); ?> item(s) | 
                                        Total Value: <strong>₹<?php echo number_format($summary['total_investment'], 2); ?></strong>
                                    </div>
                                </div>
                                
                                <?php if ($total_items > 0): ?>
                                    <div class="table-responsive" id="stockTable">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>#</th>
                                                    <th>Medicine Name</th>
                                                    <th>Category</th>
                                                    <th>Batch No</th>
                                                    <th>Expiry Date</th>
                                                    <th>Cost Price</th>
                                                    <th>Selling Price</th>
                                                    <th>Stock Qty</th>
                                                    <th>Min Level</th>
                                                    <th>Total Value</th>
                                                    <th>Status</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                $counter = 1;
                                                while ($medicine = $stock_result->fetch_assoc()): 
                                                    $stock_qty = $medicine['stock_quantity'];
                                                    $min_level = $medicine['min_stock_level'];
                                                    $expiry_date = $medicine['expiry_date'];
                                                    $days_to_expiry = $medicine['days_to_expiry'];
                                                    
                                                    // Determine status color
                                                    $status_class = '';
                                                    $status_text = '';
                                                    
                                                    if ($stock_qty == 0) {
                                                        $status_class = 'danger';
                                                        $status_text = 'Out of Stock';
                                                    } elseif ($stock_qty <= $min_level) {
                                                        $status_class = 'warning';
                                                        $status_text = 'Low Stock';
                                                    } elseif ($days_to_expiry <= 0) {
                                                        $status_class = 'secondary';
                                                        $status_text = 'Expired';
                                                    } elseif ($days_to_expiry <= 30) {
                                                        $status_class = 'primary';
                                                        $status_text = 'Expiring Soon';
                                                    } else {
                                                        $status_class = 'success';
                                                        $status_text = 'In Stock';
                                                    }
                                                ?>
                                                    <tr>
                                                        <td><?php echo $counter++; ?></td>
                                                        <td>
                                                            <strong><?php echo htmlspecialchars($medicine['medicine_name']); ?></strong>
                                                            <?php if (!empty($medicine['generic_name'])): ?>
                                                                <br>
                                                                <small class="text-muted"><?php echo htmlspecialchars($medicine['generic_name']); ?></small>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td><?php echo htmlspecialchars($medicine['category_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($medicine['batch_number']); ?></td>
                                                        <td>
                                                            <?php echo date('d M Y', strtotime($expiry_date)); ?>
                                                            <?php if ($days_to_expiry <= 30): ?>
                                                                <br>
                                                                <small class="text-<?php echo $days_to_expiry <= 0 ? 'secondary' : 'primary'; ?>">
                                                                    <?php 
                                                                    if ($days_to_expiry <= 0) {
                                                                        echo 'Expired';
                                                                    } elseif ($days_to_expiry == 1) {
                                                                        echo '1 day left';
                                                                    } else {
                                                                        echo $days_to_expiry . ' days left';
                                                                    }
                                                                    ?>
                                                                </small>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>₹<?php echo number_format($medicine['cost_price'], 2); ?></td>
                                                        <td>₹<?php echo number_format($medicine['selling_price'], 2); ?></td>
                                                        <td>
                                                            <strong><?php echo $stock_qty; ?></strong>
                                                            <?php if ($stock_qty <= $min_level && $stock_qty > 0): ?>
                                                                <br>
                                                                <small class="text-warning">
                                                                    <?php echo $min_level - $stock_qty; ?> below minimum
                                                                </small>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td><?php echo $min_level; ?></td>
                                                        <td>
                                                            <strong>₹<?php echo number_format($medicine['total_cost_value'], 2); ?></strong>
                                                            <br>
                                                            <small class="text-muted">
                                                                Potential: ₹<?php echo number_format($medicine['potential_profit'], 2); ?>
                                                            </small>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $status_class; ?>">
                                                                <?php echo $status_text; ?>
                                                            </span>
                                                        </td>
                                                        <td>
                                                            <div class="d-flex gap-2">
                                                                <a href="edit-medicine.php?id=<?php echo $medicine['id']; ?>" class="btn btn-sm btn-outline-primary" title="Edit">
                                                                    <i class="mdi mdi-pencil"></i>
                                                                </a>
                                                                <button type="button" class="btn btn-sm btn-outline-success" onclick="showStockUpdateModal(<?php echo $medicine['id']; ?>, '<?php echo htmlspecialchars($medicine['medicine_name']); ?>')" title="Update Stock">
                                                                    <i class="mdi mdi-plus-box"></i>
                                                                </button>
                                                                <a href="medicine-details.php?id=<?php echo $medicine['id']; ?>" class="btn btn-sm btn-outline-info" title="View Details">
                                                                    <i class="mdi mdi-eye"></i>
                                                                </a>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-5">
                                        <i class="mdi mdi-package-variant-off display-4 text-muted"></i>
                                        <h4 class="mt-3">No Stock Items Found</h4>
                                        <p class="text-muted">No items match the selected filters.</p>
                                        <a href="add-medicine.php" class="btn btn-primary mt-2">
                                            <i class="mdi mdi-plus me-1"></i> Add New Medicine
                                        </a>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mt-4">
                    
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center mb-4">
                                    <h4 class="card-title">Low Stock Alert</h4>
                                    <span class="badge bg-warning"><?php echo $low_stock_result->num_rows; ?> items</span>
                                </div>
                                
                                <?php if ($low_stock_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Medicine</th>
                                                    <th>Category</th>
                                                    <th>Current Qty</th>
                                                    <th>Min Level</th>
                                                    <th>Shortage</th>
                                                    <th>Expiry</th>
                                                    <th>Action</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($item = $low_stock_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($item['medicine_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($item['category_name']); ?></td>
                                                        <td class="text-warning"><strong><?php echo $item['stock_quantity']; ?></strong></td>
                                                        <td><?php echo $item['min_stock_level']; ?></td>
                                                        <td class="text-danger">
                                                            <?php echo $item['min_stock_level'] - $item['stock_quantity']; ?>
                                                        </td>
                                                        <td><?php echo date('d M Y', strtotime($item['expiry_date'])); ?></td>
                                                        <td>
                                                            <button type="button" class="btn btn-sm btn-outline-primary" onclick="showStockUpdateModalByName('<?php echo htmlspecialchars($item['medicine_name']); ?>')">
                                                                <i class="mdi mdi-plus"></i> Add Stock
                                                            </button>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-check-circle display-4 text-success"></i>
                                        <p class="text-muted mt-2">No low stock items</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center mb-4">
                                    <h4 class="card-title">Expiring Soon (30 days)</h4>
                                    <span class="badge bg-primary"><?php echo $expiring_result->num_rows; ?> items</span>
                                </div>
                                
                                <?php if ($expiring_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Medicine</th>
                                                    <th>Category</th>
                                                    <th>Stock Qty</th>
                                                    <th>Expiry Date</th>
                                                    <th>Days Left</th>
                                                    <th>Action</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($item = $expiring_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($item['medicine_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($item['category_name']); ?></td>
                                                        <td><?php echo $item['stock_quantity']; ?></td>
                                                        <td><?php echo date('d M Y', strtotime($item['expiry_date'])); ?></td>
                                                        <td class="<?php echo $item['days_left'] <= 7 ? 'text-danger' : 'text-warning'; ?>">
                                                            <strong><?php echo $item['days_left']; ?> days</strong>
                                                        </td>
                                                        <td>
                                                            <button type="button" class="btn btn-sm btn-outline-warning" onclick="showSaleModal('<?php echo htmlspecialchars($item['medicine_name']); ?>')">
                                                                <i class="mdi mdi-sale"></i> Promote
                                                            </button>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-clock-check display-4 text-success"></i>
                                        <p class="text-muted mt-2">No items expiring soon</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mt-4">
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Stock by Category</h4>
                                
                                <?php if ($category_distribution_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Category</th>
                                                    <th>Items</th>
                                                    <th>Total Qty</th>
                                                    <th>Investment</th>
                                                    <th>Avg. Qty</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($category = $category_distribution_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($category['category_name']); ?></td>
                                                        <td><?php echo $category['item_count']; ?></td>
                                                        <td><?php echo number_format($category['total_quantity']); ?></td>
                                                        <td>₹<?php echo number_format($category['total_investment'], 2); ?></td>
                                                        <td><?php echo number_format($category['total_quantity'] / max($category['item_count'], 1), 1); ?></td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-package-variant display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No category data available</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Top Valued Items by Investment</h4>
                                
                                <?php if ($top_value_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Medicine</th>
                                                    <th>Category</th>
                                                    <th>Stock Qty</th>
                                                    <th>Cost Price</th>
                                                    <th>Total Value</th>
                                                    <th>Action</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($item = $top_value_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($item['medicine_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($item['category_name']); ?></td>
                                                        <td><?php echo $item['stock_quantity']; ?></td>
                                                        <td>₹<?php echo number_format($item['cost_price'], 2); ?></td>
                                                        <td class="text-success">
                                                            <strong>₹<?php echo number_format($item['total_value'], 2); ?></strong>
                                                        </td>
                                                        <td>
                                                            <a href="medicine-details.php?name=<?php echo urlencode($item['medicine_name']); ?>" class="btn btn-sm btn-outline-info">
                                                                <i class="mdi mdi-chart-bar"></i> Analyze
                                                            </a>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-chart-bar display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No value data available</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mt-4">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Quick Actions</h4>
                                <div class="row">
                                    <div class="col-md-3 mb-3">
                                        <a href="add-medicine.php" class="btn btn-outline-primary w-100">
                                            <i class="mdi mdi-plus-box me-1"></i> Add New Medicine
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="purchase-order.php" class="btn btn-outline-success w-100">
                                            <i class="mdi mdi-cart-plus me-1"></i> Create Purchase Order
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="low-stock-report.php" class="btn btn-outline-warning w-100">
                                            <i class="mdi mdi-alert me-1"></i> View Low Stock Report
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="expiry-report.php" class="btn btn-outline-danger w-100">
                                            <i class="mdi mdi-clock-alert me-1"></i> View Expiry Report
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="inventory-audit.php" class="btn btn-outline-info w-100">
                                            <i class="mdi mdi-clipboard-check me-1"></i> Inventory Audit
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="stock-adjustment.php" class="btn btn-outline-secondary w-100">
                                            <i class="mdi mdi-adjust me-1"></i> Stock Adjustment
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="reorder-list.php" class="btn btn-outline-primary w-100">
                                            <i class="mdi mdi-reorder-horizontal me-1"></i> Reorder List
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <button type="button" class="btn btn-outline-success w-100" onclick="exportStockReport()">
                                            <i class="mdi mdi-file-pdf me-1"></i> Export PDF Report
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

</div>
            
        </div>

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

</div>

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

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

<div class="modal fade" id="stockUpdateModal" tabindex="-1" aria-labelledby="stockUpdateModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="stockUpdateModalLabel">Update Stock</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <form id="stockUpdateForm" method="POST" action="update-stock.php">
                    <input type="hidden" id="medicine_id" name="medicine_id">
                    <div class="mb-3">
                        <label for="medicine_name" class="form-label">Medicine Name</label>
                        <input type="text" class="form-control" id="medicine_name_display" readonly>
                    </div>
                    <div class="row">
                        <div class="col-md-6 mb-3">
                            <label for="current_stock" class="form-label">Current Stock</label>
                            <input type="number" class="form-control" id="current_stock" readonly>
                        </div>
                        <div class="col-md-6 mb-3">
                            <label for="min_stock_level" class="form-label">Minimum Level</label>
                            <input type="number" class="form-control" id="min_stock_level" readonly>
                        </div>
                    </div>
                    <div class="mb-3">
                        <label for="adjustment_type" class="form-label">Adjustment Type</label>
                        <select class="form-select" id="adjustment_type" name="adjustment_type" required>
                            <option value="add">Add Stock</option>
                            <option value="subtract">Subtract Stock</option>
                            <option value="set">Set Stock to</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <label for="quantity" class="form-label">Quantity</label>
                        <input type="number" class="form-control" id="quantity" name="quantity" min="1" required>
                    </div>
                    <div class="mb-3">
                        <label for="reason" class="form-label">Reason (Optional)</label>
                        <textarea class="form-control" id="reason" name="reason" rows="2" placeholder="e.g., Purchase, Sale, Damaged, Expired"></textarea>
                    </div>
                </form>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                <button type="submit" form="stockUpdateForm" class="btn btn-primary">Update Stock</button>
            </div>
        </div>
    </div>
</div>

// Show stock update modal
    function showStockUpdateModal(medicineId, medicineName) {
        // Fetch medicine details via AJAX
        fetch('get-medicine-details.php?id=' + medicineId)
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    document.getElementById('medicine_id').value = medicineId;
                    document.getElementById('medicine_name_display').value = medicineName;
                    document.getElementById('current_stock').value = data.stock_quantity;
                    document.getElementById('min_stock_level').value = data.min_stock_level;
                    
                    const modal = new bootstrap.Modal(document.getElementById('stockUpdateModal'));
                    modal.show();
                } else {
                    alert('Error loading medicine details');
                }
            })
            .catch(error => {
                console.error('Error:', error);
                alert('Error loading medicine details');
            });
    }

// Show stock update modal by medicine name
    function showStockUpdateModalByName(medicineName) {
        // You can implement AJAX to search for medicine by name
        alert('Search functionality for: ' + medicineName);
        // For now, redirect to medicine search
        window.location.href = 'add-medicine.php?search=' + encodeURIComponent(medicineName);
    }

// Show sale modal for expiring items
    function showSaleModal(medicineName) {
        window.location.href = 'new-sale.php?search=' + encodeURIComponent(medicineName);
    }

// Print report
    function printReport() {
        const printContent = document.getElementById('stockTable');
        if (!printContent) {
            alert('No data to print!');
            return;
        }
        
        const printHTML = printContent.outerHTML;
        const originalContent = document.body.innerHTML;
        
        document.body.innerHTML = `
            <html>
                <head>
                    <title>Stock Report - <?php echo date('d M Y'); ?></title>
                    <style>
                        body { font-family: Arial, sans-serif; margin: 20px; }
                        h2 { color: #333; border-bottom: 2px solid #007bff; padding-bottom: 10px; }
                        .summary { background: #f8f9fa; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
                        .summary-row { display: flex; justify-content: space-between; margin-bottom: 5px; }
                        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
                        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                        th { background-color: #f2f2f2; font-weight: bold; }
                        .badge { padding: 3px 8px; border-radius: 4px; color: white; font-size: 12px; }
                        .bg-success { background-color: #28a745; }
                        .bg-warning { background-color: #ffc107; }
                        .bg-danger { background-color: #dc3545; }
                        .bg-primary { background-color: #007bff; }
                        .bg-secondary { background-color: #6c757d; }
                        @media print {
                            .no-print { display: none; }
                            @page { margin: 0.5cm; }
                        }
                        .print-header { margin-bottom: 20px; }
                        .print-footer { margin-top: 30px; font-size: 12px; color: #666; }
                    </style>
                </head>
                <body>
                    <div class="print-header">
                        <h2>Stock Report</h2>
                        <p><strong>Generated:</strong> <?php echo date('d M Y h:i A'); ?></p>
                        
                        <div class="summary">
                            <div class="summary-row">
                                <span>Total Items:</span>
                                <span><?php echo number_format($summary['total_items']); ?></span>
                            </div>
                            <div class="summary-row">
                                <span>Total Quantity:</span>
                                <span><?php echo number_format($summary['total_quantity']); ?></span>
                            </div>
                            <div class="summary-row">
                                <span>Total Investment:</span>
                                <span>₹<?php echo number_format($summary['total_investment'], 2); ?></span>
                            </div>
                            <div class="summary-row">
                                <span>Market Value:</span>
                                <span>₹<?php echo number_format($summary['total_market_value'], 2); ?></span>
                            </div>
                            <div class="summary-row">
                                <span>Potential Profit:</span>
                                <span>₹<?php echo number_format($summary['total_potential_profit'], 2); ?></span>
                            </div>
                            <div class="summary-row">
                                <span>Profit Margin:</span>
                                <span><?php echo number_format($profit_margin, 2); ?>%</span>
                            </div>
                        </div>
                    </div>
                    ${printHTML}
                    <div class="print-footer">
                        <p>Generated by Medical Store Management System</p>
                    </div>
                    <div class="no-print" style="margin-top: 20px;">
                        <button onclick="window.print()" class="btn btn-primary">Print</button>
                        <button onclick="window.close()" class="btn btn-secondary">Close</button>
                    </div>
                </body>
            </html>
        `;
        
        window.print();
        document.body.innerHTML = originalContent;
        location.reload();
    }

// Export stock report to PDF
    function exportStockReport() {
        // This would typically use a server-side PDF generation library
        alert('PDF export functionality would be implemented here');
        // For now, redirect to print version
        window.open('stock-report.php?<?php echo http_build_query($_GET); ?>&print=1', '_blank');
    }

// Quick filter buttons
    function filterLowStock() {
        document.getElementById('stock_status').value = 'low';
        document.querySelector('form').submit();
    }

function filterExpiring() {
        document.getElementById('stock_status').value = 'expiring';
        document.querySelector('form').submit();
    }

function filterOutOfStock() {
        document.getElementById('stock_status').value = 'out';
        document.querySelector('form').submit();
    }

function filterExpired() {
        document.getElementById('stock_status').value = 'expired';
        document.querySelector('form').submit();
    }

// Search validation
    document.getElementById('search').addEventListener('keypress', function(e) {
        if (e.key === 'Enter') {
            e.preventDefault();
            document.querySelector('form').submit();
        }
    });

// Stock quantity validation
    document.getElementById('min_stock').addEventListener('change', function() {
        const maxStock = document.getElementById('max_stock');
        if (this.value && maxStock.value && parseInt(this.value) > parseInt(maxStock.value)) {
            alert('Minimum quantity cannot be greater than maximum quantity!');
            this.value = '';
        }
    });

document.getElementById('max_stock').addEventListener('change', function() {
        const minStock = document.getElementById('min_stock');
        if (this.value && minStock.value && parseInt(this.value) < parseInt(minStock.value)) {
            alert('Maximum quantity cannot be less than minimum quantity!');
            this.value = '';
        }
    });
</script>

</body>

</html>
<?php
// Close database connections
if (isset($stmt)) {
    $stmt->close();
}
if (isset($summary_stmt)) {
    $summary_stmt->close();
}
if (isset($low_stock_result)) {
    $low_stock_result->free();
}
if (isset($expiring_result)) {
    $expiring_result->free();
}
if (isset($top_value_result)) {
    $top_value_result->free();
}
if (isset($category_distribution_result)) {
    $category_distribution_result->free();
}
if (isset($conn)) {
    $conn->close();
}

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: stock-report.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation