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.28 TB

🔧 Quick Actions

✏️ Editing: profit-loss.php

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

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

// Get filter parameters
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
$category_filter = isset($_GET['category_filter']) ? (int)$_GET['category_filter'] : 0;
$show_details = isset($_GET['show_details']) ? (bool)$_GET['show_details'] : false;

// Calculate date ranges for comparison
$prev_start_date = date('Y-m-d', strtotime($start_date . ' -1 month'));
$prev_end_date = date('Y-m-d', strtotime($end_date . ' -1 month'));
$prev_year_start_date = date('Y-m-d', strtotime($start_date . ' -1 year'));
$prev_year_end_date = date('Y-m-d', strtotime($end_date . ' -1 year'));

// Build WHERE clause for filtering
$where_conditions = [];
$query_params = [];
$param_types = '';

if (!empty($start_date)) {
    $where_conditions[] = "DATE(s.sale_date) >= ?";
    $query_params[] = $start_date;
    $param_types .= 's';
}

if (!empty($end_date)) {
    $where_conditions[] = "DATE(s.sale_date) <= ?";
    $query_params[] = $end_date;
    $param_types .= 's';
}

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

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

// Calculate Current Period Profit/Loss
$current_query = "
    SELECT 
        COUNT(DISTINCT s.id) as total_sales,
        COUNT(DISTINCT s.customer_phone) as unique_customers,
        SUM(s.total_amount) as total_revenue,
        SUM(si.quantity * m.cost_price) as total_cost,
        SUM(si.quantity * si.unit_price) as total_sales_amount,
        SUM(si.quantity * (si.unit_price - m.cost_price)) as total_profit,
        SUM(s.discount) as total_discount,
        SUM(s.tax_amount) as total_tax,
        AVG(si.quantity * (si.unit_price - m.cost_price)) as avg_profit_per_sale
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN medicines m ON si.medicine_id = m.id
    $where_clause
    AND s.status = 'completed'
";

$current_stmt = $conn->prepare($current_query);
if (!empty($query_params)) {
    $current_stmt->bind_param($param_types, ...$query_params);
}
$current_stmt->execute();
$current_result = $current_stmt->get_result();
$current = $current_result->fetch_assoc();

if (!$current) {
    $current = [
        'total_sales' => 0,
        'unique_customers' => 0,
        'total_revenue' => 0,
        'total_cost' => 0,
        'total_sales_amount' => 0,
        'total_profit' => 0,
        'total_discount' => 0,
        'total_tax' => 0,
        'avg_profit_per_sale' => 0
    ];
}

// Calculate Previous Period (Month) Profit/Loss
$prev_query = "
    SELECT 
        SUM(s.total_amount) as total_revenue,
        SUM(si.quantity * m.cost_price) as total_cost,
        SUM(si.quantity * (si.unit_price - m.cost_price)) as total_profit
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN medicines m ON si.medicine_id = m.id
    WHERE DATE(s.sale_date) >= ? 
    AND DATE(s.sale_date) <= ?
    AND s.status = 'completed'
";

$prev_stmt = $conn->prepare($prev_query);
$prev_stmt->bind_param("ss", $prev_start_date, $prev_end_date);
$prev_stmt->execute();
$prev_result = $prev_stmt->get_result();
$prev = $prev_result->fetch_assoc();

if (!$prev) {
    $prev = ['total_revenue' => 0, 'total_cost' => 0, 'total_profit' => 0];
}

// Calculate Previous Year Period Profit/Loss
$prev_year_query = "
    SELECT 
        SUM(s.total_amount) as total_revenue,
        SUM(si.quantity * m.cost_price) as total_cost,
        SUM(si.quantity * (si.unit_price - m.cost_price)) as total_profit
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN medicines m ON si.medicine_id = m.id
    WHERE DATE(s.sale_date) >= ? 
    AND DATE(s.sale_date) <= ?
    AND s.status = 'completed'
";

$prev_year_stmt = $conn->prepare($prev_year_query);
$prev_year_stmt->bind_param("ss", $prev_year_start_date, $prev_year_end_date);
$prev_year_stmt->execute();
$prev_year_result = $prev_year_stmt->get_result();
$prev_year = $prev_year_result->fetch_assoc();

if (!$prev_year) {
    $prev_year = ['total_revenue' => 0, 'total_cost' => 0, 'total_profit' => 0];
}

// Calculate Profit Margin
$profit_margin = $current['total_revenue'] > 0 ? 
    ($current['total_profit'] / $current['total_revenue']) * 100 : 0;

// Calculate month-over-month changes
$revenue_change = $prev['total_revenue'] > 0 ? 
    (($current['total_revenue'] - $prev['total_revenue']) / $prev['total_revenue']) * 100 : 0;
$profit_change = $prev['total_profit'] > 0 ? 
    (($current['total_profit'] - $prev['total_profit']) / $prev['total_profit']) * 100 : 0;

// Calculate year-over-year changes
$revenue_change_yoy = $prev_year['total_revenue'] > 0 ? 
    (($current['total_revenue'] - $prev_year['total_revenue']) / $prev_year['total_revenue']) * 100 : 0;
$profit_change_yoy = $prev_year['total_profit'] > 0 ? 
    (($current['total_profit'] - $prev_year['total_profit']) / $prev_year['total_profit']) * 100 : 0;

// Get top profitable medicines
$top_medicines_query = "
    SELECT 
        m.medicine_name,
        mc.category_name,
        SUM(si.quantity) as total_quantity,
        SUM(si.quantity * m.cost_price) as total_cost,
        SUM(si.quantity * si.unit_price) as total_revenue,
        SUM(si.quantity * (si.unit_price - m.cost_price)) as total_profit,
        ROUND(((SUM(si.quantity * si.unit_price) - SUM(si.quantity * m.cost_price)) / SUM(si.quantity * si.unit_price) * 100), 2) as profit_margin
    FROM sale_items si
    JOIN sales s ON si.sale_id = s.id
    JOIN medicines m ON si.medicine_id = m.id
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE DATE(s.sale_date) >= ? AND DATE(s.sale_date) <= ?
    AND s.status = 'completed'
    GROUP BY m.medicine_name, m.category_id, mc.category_name
    HAVING total_profit > 0
    ORDER BY total_profit DESC
    LIMIT 10
";

$top_med_stmt = $conn->prepare($top_medicines_query);
$top_med_stmt->bind_param("ss", $start_date, $end_date);
$top_med_stmt->execute();
$top_med_result = $top_med_stmt->get_result();

// Get least profitable medicines
$least_medicines_query = "
    SELECT 
        m.medicine_name,
        mc.category_name,
        SUM(si.quantity) as total_quantity,
        SUM(si.quantity * m.cost_price) as total_cost,
        SUM(si.quantity * si.unit_price) as total_revenue,
        SUM(si.quantity * (si.unit_price - m.cost_price)) as total_profit,
        ROUND(((SUM(si.quantity * si.unit_price) - SUM(si.quantity * m.cost_price)) / SUM(si.quantity * si.unit_price) * 100), 2) as profit_margin
    FROM sale_items si
    JOIN sales s ON si.sale_id = s.id
    JOIN medicines m ON si.medicine_id = m.id
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE DATE(s.sale_date) >= ? AND DATE(s.sale_date) <= ?
    AND s.status = 'completed'
    GROUP BY m.medicine_name, m.category_id, mc.category_name
    HAVING total_profit <= 0 OR total_profit IS NULL
    ORDER BY total_profit ASC
    LIMIT 10
";

$least_med_stmt = $conn->prepare($least_medicines_query);
$least_med_stmt->bind_param("ss", $start_date, $end_date);
$least_med_stmt->execute();
$least_med_result = $least_med_stmt->get_result();

// Get profit by category
$profit_by_category_query = "
    SELECT 
        mc.category_name,
        COUNT(DISTINCT s.id) as sales_count,
        SUM(si.quantity) as total_quantity,
        SUM(si.quantity * m.cost_price) as total_cost,
        SUM(si.quantity * si.unit_price) as total_revenue,
        SUM(si.quantity * (si.unit_price - m.cost_price)) as total_profit,
        ROUND(((SUM(si.quantity * si.unit_price) - SUM(si.quantity * m.cost_price)) / SUM(si.quantity * si.unit_price) * 100), 2) as profit_margin
    FROM sale_items si
    JOIN sales s ON si.sale_id = s.id
    JOIN medicines m ON si.medicine_id = m.id
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE DATE(s.sale_date) >= ? AND DATE(s.sale_date) <= ?
    AND s.status = 'completed'
    GROUP BY mc.category_name
    ORDER BY total_profit DESC
";

$category_stmt = $conn->prepare($profit_by_category_query);
$category_stmt->bind_param("ss", $start_date, $end_date);
$category_stmt->execute();
$category_result = $category_stmt->get_result();

// Get daily profit trend
$daily_profit_query = "
    SELECT 
        DATE(s.sale_date) as sale_day,
        COUNT(DISTINCT s.id) as sales_count,
        SUM(s.total_amount) as daily_revenue,
        SUM(si.quantity * m.cost_price) as daily_cost,
        SUM(si.quantity * (si.unit_price - m.cost_price)) as daily_profit
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN medicines m ON si.medicine_id = m.id
    WHERE DATE(s.sale_date) >= ? AND DATE(s.sale_date) <= ?
    AND s.status = 'completed'
    GROUP BY DATE(s.sale_date)
    ORDER BY sale_day ASC
";

$daily_stmt = $conn->prepare($daily_profit_query);
$daily_stmt->bind_param("ss", $start_date, $end_date);
$daily_stmt->execute();
$daily_result = $daily_stmt->get_result();

$daily_labels = [];
$daily_profits = [];
$daily_revenues = [];
$daily_costs = [];

while ($day = $daily_result->fetch_assoc()) {
    $daily_labels[] = date('d M', strtotime($day['sale_day']));
    $daily_profits[] = $day['daily_profit'];
    $daily_revenues[] = $day['daily_revenue'];
    $daily_costs[] = $day['daily_cost'];
}

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

// Get detailed profit data if requested
$detailed_data = [];
if ($show_details) {
    $detailed_query = "
        SELECT 
            s.id as sale_id,
            s.sale_date,
            s.customer_phone,
            c.customer_name,
            m.medicine_name,
            mc.category_name,
            si.quantity,
            m.cost_price,
            si.unit_price as selling_price,
            (si.quantity * m.cost_price) as total_cost,
            (si.quantity * si.unit_price) as total_revenue,
            (si.quantity * (si.unit_price - m.cost_price)) as profit,
            ROUND(((si.unit_price - m.cost_price) / si.unit_price * 100), 2) as profit_margin_percent
        FROM sale_items si
        JOIN sales s ON si.sale_id = s.id
        JOIN medicines m ON si.medicine_id = m.id
        LEFT JOIN customers c ON s.customer_phone = c.customer_phone
        LEFT JOIN medicine_categories mc ON m.category_id = mc.id
        WHERE DATE(s.sale_date) >= ? AND DATE(s.sale_date) <= ?
        AND s.status = 'completed'
        ORDER BY s.sale_date DESC, profit DESC
    ";
    
    $detailed_stmt = $conn->prepare($detailed_query);
    $detailed_stmt->bind_param("ss", $start_date, $end_date);
    $detailed_stmt->execute();
    $detailed_result = $detailed_stmt->get_result();
    
    while ($row = $detailed_result->fetch_assoc()) {
        $detailed_data[] = $row;
    }
    
    if (isset($detailed_stmt)) {
        $detailed_stmt->close();
    }
}
?>

<?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-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">₹<?php echo number_format($current['total_revenue'], 2); ?></h4>
                                        <p class="text-muted mb-0">Total Revenue</p>
                                        <div class="mt-2">
                                            <span class="badge bg-<?php echo $revenue_change >= 0 ? 'success' : 'danger'; ?> me-1">
                                                <i class="mdi mdi-arrow-<?php echo $revenue_change >= 0 ? 'up' : 'down'; ?>"></i>
                                                <?php echo number_format(abs($revenue_change), 2); ?>%
                                            </span>
                                            <small class="text-muted">vs last month</small>
                                        </div>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-cash-multiple font-size-24 text-success"></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">₹<?php echo number_format($current['total_cost'], 2); ?></h4>
                                        <p class="text-muted mb-0">Total Cost</p>
                                        <div class="mt-2">
                                            <span class="text-muted">Cost of Goods Sold</span>
                                        </div>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-cash-minus 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">₹<?php echo number_format($current['total_profit'], 2); ?></h4>
                                        <p class="text-muted mb-0">Gross Profit</p>
                                        <div class="mt-2">
                                            <span class="badge bg-<?php echo $profit_change >= 0 ? 'success' : 'danger'; ?> me-1">
                                                <i class="mdi mdi-arrow-<?php echo $profit_change >= 0 ? 'up' : 'down'; ?>"></i>
                                                <?php echo number_format(abs($profit_change), 2); ?>%
                                            </span>
                                            <small class="text-muted">vs last month</small>
                                        </div>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-chart-line 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"><?php echo number_format($profit_margin, 2); ?>%</h4>
                                        <p class="text-muted mb-0">Profit Margin</p>
                                        <div class="mt-2">
                                            <span class="badge bg-<?php echo $profit_margin >= 20 ? 'success' : ($profit_margin >= 10 ? 'warning' : 'danger'); ?>">
                                                <?php 
                                                if ($profit_margin >= 20) echo 'Excellent';
                                                elseif ($profit_margin >= 10) echo 'Good';
                                                elseif ($profit_margin > 0) echo 'Low';
                                                else echo 'Loss';
                                                ?>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-percent font-size-24 text-info"></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 Report</h4>
                                
                                <form method="GET" action="" class="row g-3">
                                    <div class="col-md-4">
                                        <label for="start_date" class="form-label">From Date</label>
                                        <input type="date" class="form-control" id="start_date" name="start_date" value="<?php echo htmlspecialchars($start_date); ?>">
                                    </div>
                                    
                                    <div class="col-md-4">
                                        <label for="end_date" class="form-label">To Date</label>
                                        <input type="date" class="form-control" id="end_date" name="end_date" value="<?php echo htmlspecialchars($end_date); ?>">
                                    </div>
                                    
                                    <div class="col-md-4">
                                        <label for="category_filter" class="form-label">Medicine 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-12">
                                        <div class="form-check form-switch">
                                            <input class="form-check-input" type="checkbox" id="show_details" name="show_details" value="1" <?php echo $show_details ? 'checked' : ''; ?>>
                                            <label class="form-check-label" for="show_details">
                                                Show Detailed Transaction View
                                            </label>
                                        </div>
                                    </div>
                                    
                                    <div class="col-md-12 mt-3">
                                        <button type="submit" class="btn btn-primary">
                                            <i class="mdi mdi-filter me-1"></i> Apply Filters
                                        </button>
                                        <a href="profit-loss.php" class="btn btn-outline-secondary ms-2">
                                            <i class="mdi mdi-refresh me-1"></i> Reset Filters
                                        </a>
                                        <a href="export-profit-loss.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>
                                    </div>
                                    
                                    <?php if (!empty($start_date) || !empty($end_date) || $category_filter > 0): ?>
                                        <div class="col-12 mt-2">
                                            <small class="text-muted">
                                                <i class="mdi mdi-filter me-1"></i> 
                                                <?php
                                                $filters = [];
                                                if (!empty($start_date)) $filters[] = "From: $start_date";
                                                if (!empty($end_date)) $filters[] = "To: $end_date";
                                                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($filters)) {
                                                    echo "Active filters: " . implode(' | ', $filters);
                                                }
                                                ?>
                                            </small>
                                        </div>
                                    <?php endif; ?>
                                </form>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-lg-8">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Profit & Loss Overview</h4>
                                
                                <div class="table-responsive">
                                    <table class="table table-bordered mb-0">
                                        <thead class="table-light">
                                            <tr>
                                                <th>Description</th>
                                                <th class="text-end">Current Period</th>
                                                <th class="text-end">Last Month</th>
                                                <th class="text-end">Last Year</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            
                                            <tr>
                                                <td><strong>Total Revenue</strong></td>
                                                <td class="text-end text-success"><strong>₹<?php echo number_format($current['total_revenue'], 2); ?></strong></td>
                                                <td class="text-end">₹<?php echo number_format($prev['total_revenue'], 2); ?></td>
                                                <td class="text-end">₹<?php echo number_format($prev_year['total_revenue'], 2); ?></td>
                                            </tr>
                                            
                                            
                                            <tr>
                                                <td><em>Cost of Goods Sold</em></td>
                                                <td class="text-end text-danger"><em>₹<?php echo number_format($current['total_cost'], 2); ?></em></td>
                                                <td class="text-end">₹<?php echo number_format($prev['total_cost'], 2); ?></td>
                                                <td class="text-end">₹<?php echo number_format($prev_year['total_cost'], 2); ?></td>
                                            </tr>
                                            
                                            
                                            <tr class="table-active">
                                                <td><strong>Gross Profit</strong></td>
                                                <td class="text-end"><strong class="<?php echo $current['total_profit'] >= 0 ? 'text-success' : 'text-danger'; ?>">
                                                    ₹<?php echo number_format($current['total_profit'], 2); ?>
                                                </strong></td>
                                                <td class="text-end <?php echo $prev['total_profit'] >= 0 ? 'text-success' : 'text-danger'; ?>">
                                                    ₹<?php echo number_format($prev['total_profit'], 2); ?>
                                                </td>
                                                <td class="text-end <?php echo $prev_year['total_profit'] >= 0 ? 'text-success' : 'text-danger'; ?>">
                                                    ₹<?php echo number_format($prev_year['total_profit'], 2); ?>
                                                </td>
                                            </tr>
                                            
                                            
                                            <tr>
                                                <td><em>Profit Margin</em></td>
                                                <td class="text-end">
                                                    <span class="badge bg-<?php echo $profit_margin >= 20 ? 'success' : ($profit_margin >= 10 ? 'warning' : 'danger'); ?>">
                                                        <?php echo number_format($profit_margin, 2); ?>%
                                                    </span>
                                                </td>
                                                <td class="text-end">
                                                    <?php 
                                                    $prev_margin = $prev['total_revenue'] > 0 ? ($prev['total_profit'] / $prev['total_revenue']) * 100 : 0;
                                                    echo number_format($prev_margin, 2); ?>%
                                                </td>
                                                <td class="text-end">
                                                    <?php 
                                                    $prev_year_margin = $prev_year['total_revenue'] > 0 ? ($prev_year['total_profit'] / $prev_year['total_revenue']) * 100 : 0;
                                                    echo number_format($prev_year_margin, 2); ?>%
                                                </td>
                                            </tr>
                                            
                                            
                                            <tr>
                                                <td><em>Total Sales</em></td>
                                                <td class="text-end"><?php echo $current['total_sales']; ?></td>
                                                <td class="text-end">-</td>
                                                <td class="text-end">-</td>
                                            </tr>
                                            
                                            <tr>
                                                <td><em>Unique Customers</em></td>
                                                <td class="text-end"><?php echo $current['unique_customers']; ?></td>
                                                <td class="text-end">-</td>
                                                <td class="text-end">-</td>
                                            </tr>
                                            
                                            <tr>
                                                <td><em>Avg. Profit per Sale</em></td>
                                                <td class="text-end">₹<?php echo number_format($current['avg_profit_per_sale'], 2); ?></td>
                                                <td class="text-end">-</td>
                                                <td class="text-end">-</td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-lg-4">
                        <div class="card">
                            <div class="card-body">
                                <h4 class="card-title mb-4">Quick Insights</h4>
                                
                                <div class="d-grid gap-2">
                                    <div class="alert <?php echo $current['total_profit'] >= 0 ? 'alert-success' : 'alert-danger'; ?>">
                                        <h5 class="alert-heading">
                                            <?php echo $current['total_profit'] >= 0 ? 'Profit' : 'Loss'; ?> Status
                                        </h5>
                                        <p class="mb-0">
                                            <?php if ($current['total_profit'] >= 0): ?>
                                                Your store is making a profit of <strong>₹<?php echo number_format($current['total_profit'], 2); ?></strong> 
                                                with a margin of <strong><?php echo number_format($profit_margin, 2); ?>%</strong>.
                                            <?php else: ?>
                                                Your store is operating at a loss of <strong>₹<?php echo number_format(abs($current['total_profit']), 2); ?></strong>.
                                            <?php endif; ?>
                                        </p>
                                    </div>
                                    
                                    <div class="alert <?php echo $revenue_change >= 0 ? 'alert-success' : 'alert-danger'; ?>">
                                        <h5 class="alert-heading">Revenue Trend</h5>
                                        <p class="mb-0">
                                            Revenue is <?php echo $revenue_change >= 0 ? 'up' : 'down'; ?> by 
                                            <strong><?php echo number_format(abs($revenue_change), 2); ?>%</strong> compared to last month.
                                        </p>
                                    </div>
                                    
                                    <div class="alert <?php echo $profit_change >= 0 ? 'alert-success' : 'alert-danger'; ?>">
                                        <h5 class="alert-heading">Profit Trend</h5>
                                        <p class="mb-0">
                                            Profit is <?php echo $profit_change >= 0 ? 'up' : 'down'; ?> by 
                                            <strong><?php echo number_format(abs($profit_change), 2); ?>%</strong> compared to last month.
                                        </p>
                                    </div>
                                    
                                    <div class="alert <?php echo $profit_margin >= 20 ? 'alert-success' : ($profit_margin >= 10 ? 'alert-warning' : 'alert-danger'); ?>">
                                        <h5 class="alert-heading">Margin Analysis</h5>
                                        <p class="mb-0">
                                            <?php
                                            if ($profit_margin >= 20) {
                                                echo "Excellent margin! Keep up the good pricing strategy.";
                                            } elseif ($profit_margin >= 10) {
                                                echo "Good margin. Consider optimizing costs for better profits.";
                                            } elseif ($profit_margin > 0) {
                                                echo "Low margin. Review pricing and cost structure.";
                                            } else {
                                                echo "Operating at a loss. Immediate action required.";
                                            }
                                            ?>
                                        </p>
                                    </div>
                                </div>
                            </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">Top Profitable Medicines</h4>
                                
                                <?php if ($top_med_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>Quantity</th>
                                                    <th>Revenue</th>
                                                    <th>Profit</th>
                                                    <th>Margin</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($medicine = $top_med_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($medicine['medicine_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($medicine['category_name']); ?></td>
                                                        <td><?php echo $medicine['total_quantity']; ?></td>
                                                        <td>₹<?php echo number_format($medicine['total_revenue'], 2); ?></td>
                                                        <td class="text-success">
                                                            <strong>₹<?php echo number_format($medicine['total_profit'], 2); ?></strong>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $medicine['profit_margin'] >= 20 ? 'success' : ($medicine['profit_margin'] >= 10 ? 'warning' : 'danger'); ?>">
                                                                <?php echo $medicine['profit_margin']; ?>%
                                                            </span>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-chart-line display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No profitable medicine 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">Least Profitable Medicines</h4>
                                
                                <?php if ($least_med_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>Quantity</th>
                                                    <th>Revenue</th>
                                                    <th>Profit/Loss</th>
                                                    <th>Margin</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($medicine = $least_med_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($medicine['medicine_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($medicine['category_name']); ?></td>
                                                        <td><?php echo $medicine['total_quantity']; ?></td>
                                                        <td>₹<?php echo number_format($medicine['total_revenue'], 2); ?></td>
                                                        <td class="<?php echo $medicine['total_profit'] >= 0 ? 'text-success' : 'text-danger'; ?>">
                                                            <strong>₹<?php echo number_format($medicine['total_profit'], 2); ?></strong>
                                                        </td>
                                                        <td>
                                                            <?php if ($medicine['profit_margin'] > 0): ?>
                                                                <span class="badge bg-<?php echo $medicine['profit_margin'] >= 10 ? 'warning' : 'danger'; ?>">
                                                                    <?php echo $medicine['profit_margin']; ?>%
                                                                </span>
                                                            <?php else: ?>
                                                                <span class="badge bg-danger">Loss</span>
                                                            <?php endif; ?>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-alert-circle display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No low-profit medicine 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">Profit by Category</h4>
                                
                                <?php if ($category_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>Sales Count</th>
                                                    <th>Quantity Sold</th>
                                                    <th>Total Revenue</th>
                                                    <th>Total Cost</th>
                                                    <th>Gross Profit</th>
                                                    <th>Profit Margin</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($category = $category_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($category['category_name']); ?></td>
                                                        <td><?php echo $category['sales_count']; ?></td>
                                                        <td><?php echo $category['total_quantity']; ?></td>
                                                        <td>₹<?php echo number_format($category['total_revenue'], 2); ?></td>
                                                        <td>₹<?php echo number_format($category['total_cost'], 2); ?></td>
                                                        <td class="<?php echo $category['total_profit'] >= 0 ? 'text-success' : 'text-danger'; ?>">
                                                            <strong>₹<?php echo number_format($category['total_profit'], 2); ?></strong>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $category['profit_margin'] >= 20 ? 'success' : ($category['profit_margin'] >= 10 ? 'warning' : 'danger'); ?>">
                                                                <?php echo $category['profit_margin']; ?>%
                                                            </span>
                                                        </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>

<?php if ($show_details && !empty($detailed_data)): ?>
                <div class="row mt-4">
                    <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">Detailed Transaction View</h4>
                                    <span class="badge bg-info">Showing <?php echo count($detailed_data); ?> transactions</span>
                                </div>
                                
                                <div class="table-responsive">
                                    <table class="table table-centered table-nowrap mb-0">
                                        <thead class="table-light">
                                            <tr>
                                                <th>Date</th>
                                                <th>Sale ID</th>
                                                <th>Customer</th>
                                                <th>Medicine</th>
                                                <th>Category</th>
                                                <th>Qty</th>
                                                <th>Cost</th>
                                                <th>Selling</th>
                                                <th>Total Cost</th>
                                                <th>Total Revenue</th>
                                                <th>Profit</th>
                                                <th>Margin</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($detailed_data as $row): ?>
                                                <tr>
                                                    <td><?php echo date('d M', strtotime($row['sale_date'])); ?></td>
                                                    <td>#<?php echo $row['sale_id']; ?></td>
                                                    <td><?php echo htmlspecialchars($row['customer_name'] ?: $row['customer_phone']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['medicine_name']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['category_name']); ?></td>
                                                    <td><?php echo $row['quantity']; ?></td>
                                                    <td>₹<?php echo number_format($row['cost_price'], 2); ?></td>
                                                    <td>₹<?php echo number_format($row['selling_price'], 2); ?></td>
                                                    <td>₹<?php echo number_format($row['total_cost'], 2); ?></td>
                                                    <td>₹<?php echo number_format($row['total_revenue'], 2); ?></td>
                                                    <td class="<?php echo $row['profit'] >= 0 ? 'text-success' : 'text-danger'; ?>">
                                                        <strong>₹<?php echo number_format($row['profit'], 2); ?></strong>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-<?php echo $row['profit_margin_percent'] >= 20 ? 'success' : ($row['profit_margin_percent'] >= 10 ? 'warning' : 'danger'); ?>">
                                                            <?php echo $row['profit_margin_percent']; ?>%
                                                        </span>
                                                    </td>
                                                </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                <?php endif; ?>

</div>
            
        </div>

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

</div>

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

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

// Print report
    function printReport() {
        const printContent = document.querySelector('.container-fluid').innerHTML;
        const originalContent = document.body.innerHTML;
        
        document.body.innerHTML = `
            <html>
                <head>
                    <title>Profit & Loss 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; }
                        h4 { color: #555; margin-top: 20px; }
                        .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; margin-bottom: 20px; }
                        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                        th { background-color: #f2f2f2; font-weight: bold; }
                        .text-end { text-align: right; }
                        .text-success { color: #28a745; }
                        .text-danger { color: #dc3545; }
                        .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-info { background-color: #17a2b8; }
                        @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>Profit & Loss Report</h2>
                        <p><strong>Period:</strong> <?php echo date('d M Y', strtotime($start_date)); ?> to <?php echo date('d M Y', strtotime($end_date)); ?></p>
                        <p><strong>Generated:</strong> <?php echo date('d M Y h:i A'); ?></p>
                        
                        <div class="summary">
                            <div class="summary-row">
                                <span>Total Revenue:</span>
                                <span class="text-success">₹<?php echo number_format($current['total_revenue'], 2); ?></span>
                            </div>
                            <div class="summary-row">
                                <span>Total Cost:</span>
                                <span class="text-danger">₹<?php echo number_format($current['total_cost'], 2); ?></span>
                            </div>
                            <div class="summary-row">
                                <span>Gross Profit:</span>
                                <span class="<?php echo $current['total_profit'] >= 0 ? 'text-success' : 'text-danger'; ?>">
                                    <strong>₹<?php echo number_format($current['total_profit'], 2); ?></strong>
                                </span>
                            </div>
                            <div class="summary-row">
                                <span>Profit Margin:</span>
                                <span>
                                    <span class="badge bg-<?php echo $profit_margin >= 20 ? 'success' : ($profit_margin >= 10 ? 'warning' : 'danger'); ?>">
                                        <?php echo number_format($profit_margin, 2); ?>%
                                    </span>
                                </span>
                            </div>
                        </div>
                    </div>
                    ${printContent}
                    <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();
    }

// Quick date filters
    function setDateFilter(days) {
        const endDate = new Date();
        const startDate = new Date();
        startDate.setDate(startDate.getDate() - days);
        
        document.getElementById('start_date').value = startDate.toISOString().split('T')[0];
        document.getElementById('end_date').value = endDate.toISOString().split('T')[0];
        document.querySelector('form').submit();
    }

function setToday() {
        const today = new Date();
        document.getElementById('start_date').value = today.toISOString().split('T')[0];
        document.getElementById('end_date').value = today.toISOString().split('T')[0];
        document.querySelector('form').submit();
    }

function setThisMonth() {
        const today = new Date();
        const firstDay = new Date(today.getFullYear(), today.getMonth(), 1);
        const lastDay = new Date(today.getFullYear(), today.getMonth() + 1, 0);
        
        document.getElementById('start_date').value = firstDay.toISOString().split('T')[0];
        document.getElementById('end_date').value = lastDay.toISOString().split('T')[0];
        document.querySelector('form').submit();
    }

// Date validation
    document.getElementById('start_date').addEventListener('change', function() {
        const endDate = document.getElementById('end_date');
        if (this.value && endDate.value && this.value > endDate.value) {
            alert('Start date cannot be after end date!');
            this.value = '';
        }
    });

document.getElementById('end_date').addEventListener('change', function() {
        const startDate = document.getElementById('start_date');
        if (this.value && startDate.value && this.value < startDate.value) {
            alert('End date cannot be before start date!');
            this.value = '';
        }
    });
</script>

</body>

</html>
<?php
// Close database connections
if (isset($current_stmt)) {
    $current_stmt->close();
}
if (isset($prev_stmt)) {
    $prev_stmt->close();
}
if (isset($prev_year_stmt)) {
    $prev_year_stmt->close();
}
if (isset($top_med_stmt)) {
    $top_med_stmt->close();
}
if (isset($least_med_stmt)) {
    $least_med_stmt->close();
}
if (isset($category_stmt)) {
    $category_stmt->close();
}
if (isset($daily_stmt)) {
    $daily_stmt->close();
}
if (isset($conn)) {
    $conn->close();
}

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: profit-loss.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation