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: customer-reports.php

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

// Set default time range
$time_range = isset($_GET['time_range']) ? $_GET['time_range'] : 'month';
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : '';
$sort_by = isset($_GET['sort_by']) ? $_GET['sort_by'] : 'total_spent';
$limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 20;
$search_term = isset($_GET['search']) ? trim($_GET['search']) : '';

// Validate inputs
$allowed_ranges = ['today', 'yesterday', 'week', 'month', 'quarter', 'year', 'all', 'custom'];
$time_range = in_array($time_range, $allowed_ranges) ? $time_range : 'month';
$limit = min(max($limit, 5), 100);
$allowed_sort = ['total_spent', 'purchase_count', 'last_purchase', 'customer_name', 'avg_order_value'];
$sort_by = in_array($sort_by, $allowed_sort) ? $sort_by : 'total_spent';

// Calculate date range
$date_condition = "";
$date_params = [];
$date_param_types = "";

if ($time_range === 'custom' && $start_date && $end_date) {
    $date_condition = "AND DATE(s.sale_date) BETWEEN ? AND ?";
    $date_params = [$start_date, $end_date];
    $date_param_types = "ss";
} elseif ($time_range !== 'all') {
    switch ($time_range) {
        case 'today':
            $date_condition = "AND DATE(s.sale_date) = CURDATE()";
            break;
        case 'yesterday':
            $date_condition = "AND DATE(s.sale_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
            break;
        case 'week':
            $date_condition = "AND s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
            break;
        case 'month':
            $date_condition = "AND s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
            break;
        case 'quarter':
            $date_condition = "AND s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)";
            break;
        case 'year':
            $date_condition = "AND s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY)";
            break;
    }
}

// Search condition - Initialize variables
$search_condition = "";
$search_params = [];
$search_param_types = "";

if (!empty($search_term)) {
    $search_condition = "AND (c.customer_name LIKE ? OR c.customer_phone LIKE ? OR c.customer_email LIKE ?)";
    $search_param = "%$search_term%";
    $search_params = [$search_param, $search_param, $search_param];
    $search_param_types = "sss";
}

// Build the main query
$sort_mapping = [
    'total_spent' => 'total_amount DESC',
    'purchase_count' => 'purchase_count DESC',
    'last_purchase' => 'last_purchase_date DESC',
    'customer_name' => 'c.customer_name ASC',
    'avg_order_value' => 'avg_order_value DESC'
];

$customer_query = "
    SELECT 
        c.id,
        c.customer_name,
        c.customer_phone,
        c.customer_email,
        c.customer_address,
        c.loyalty_points,
        COUNT(DISTINCT s.id) as purchase_count,
        COALESCE(SUM(s.total_amount), 0) as total_amount,
        COALESCE(AVG(s.total_amount), 0) as avg_order_value,
        MAX(s.sale_date) as last_purchase_date,
        MIN(s.sale_date) as first_purchase_date,
        COUNT(DISTINCT DATE(s.sale_date)) as days_with_purchases,
        TIMESTAMPDIFF(DAY, COALESCE(MAX(s.sale_date), CURDATE()), CURDATE()) as days_since_last_purchase,
        COALESCE(SUM(CASE WHEN s.status = 'cancelled' THEN s.total_amount ELSE 0 END), 0) as cancelled_amount,
        COALESCE(SUM(CASE WHEN s.status = 'refunded' THEN s.total_amount ELSE 0 END), 0) as refunded_amount
    FROM customers c
    LEFT JOIN sales s ON (c.customer_phone = s.customer_phone OR c.customer_name = s.customer_name) 
        AND s.status = 'completed'
        $date_condition
    WHERE c.status = 'active'
    $search_condition
    GROUP BY c.id
    HAVING (purchase_count > 0 OR ? = 1)
    ORDER BY {$sort_mapping[$sort_by]}
    LIMIT ?
";

// Prepare and execute query
$show_all = !empty($search_term) ? 1 : 0; // Show all customers if searching
$limit_param = $limit;

$stmt = $conn->prepare($customer_query);

// Determine which parameters to bind
if (!empty($date_params) && !empty($search_params)) {
    // Both date range and search
    $all_params = array_merge($date_params, $search_params, [$show_all, $limit_param]);
    $stmt->bind_param($date_param_types . $search_param_types . "ii", ...$all_params);
} elseif (!empty($date_params)) {
    // Only date range
    $all_params = array_merge($date_params, [$show_all, $limit_param]);
    $stmt->bind_param($date_param_types . "ii", ...$all_params);
} elseif (!empty($search_params)) {
    // Only search
    $all_params = array_merge($search_params, [$show_all, $limit_param]);
    $stmt->bind_param($search_param_types . "ii", ...$all_params);
} else {
    // No filters
    $all_params = [$show_all, $limit_param];
    $stmt->bind_param("ii", ...$all_params);
}

$stmt->execute();
$customers_result = $stmt->get_result();

// Get overall statistics
$stats_query = "
    SELECT 
        COUNT(DISTINCT c.id) as total_customers,
        COUNT(DISTINCT CASE WHEN s.id IS NOT NULL THEN c.id END) as active_customers,
        COALESCE(SUM(s.total_amount), 0) as total_revenue,
        COALESCE(AVG(s.total_amount), 0) as avg_order_value,
        COALESCE(MAX(s.sale_date), CURDATE()) as last_sale_date,
        COALESCE(COUNT(DISTINCT s.id), 0) as total_transactions,
        COALESCE(SUM(c.loyalty_points), 0) as total_loyalty_points,
        COALESCE(AVG(TIMESTAMPDIFF(DAY, s.sale_date, CURDATE())), 0) as avg_days_since_purchase
    FROM customers c
    LEFT JOIN sales s ON (c.customer_phone = s.customer_phone OR c.customer_name = s.customer_name) 
        AND s.status = 'completed'
        $date_condition
    WHERE c.status = 'active'
    $search_condition
";

$stats_stmt = $conn->prepare($stats_query);

// Determine which parameters to bind for stats query
if (!empty($date_params) && !empty($search_params)) {
    $stats_stmt->bind_param($date_param_types . $search_param_types, ...array_merge($date_params, $search_params));
} elseif (!empty($date_params)) {
    $stats_stmt->bind_param($date_param_types, ...$date_params);
} elseif (!empty($search_params)) {
    $stats_stmt->bind_param($search_param_types, ...$search_params);
} else {
    // No parameters to bind - execute without binding
    $stats_stmt->execute();
}

// Only execute if not already executed above
if (empty($date_params) && empty($search_params)) {
    $stats_stmt->execute();
}

$stats_result = $stats_stmt->get_result();
$overall_stats = $stats_result->fetch_assoc();

if (!$overall_stats) {
    $overall_stats = [
        'total_customers' => 0,
        'active_customers' => 0,
        'total_revenue' => 0,
        'avg_order_value' => 0,
        'last_sale_date' => null,
        'total_transactions' => 0,
        'total_loyalty_points' => 0,
        'avg_days_since_purchase' => 0
    ];
}

// Get new customers (first purchase in selected period)
$new_customers_sql = "
    SELECT 
        c.customer_name,
        c.customer_phone,
        MIN(s.sale_date) as first_purchase_date,
        COUNT(s.id) as purchase_count,
        SUM(s.total_amount) as total_spent
    FROM customers c
    INNER JOIN sales s ON (c.customer_phone = s.customer_phone OR c.customer_name = s.customer_name)
        AND s.status = 'completed'
        $date_condition
    WHERE c.status = 'active'
    GROUP BY c.id
    HAVING MIN(s.sale_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
    ORDER BY first_purchase_date DESC
    LIMIT 10
";

$new_customers_stmt = $conn->prepare($new_customers_sql);

if (!empty($date_params)) {
    $new_customers_stmt->bind_param($date_param_types, ...$date_params);
    $new_customers_stmt->execute();
    $new_customers_result = $new_customers_stmt->get_result();
} else {
    $new_customers_result = $conn->query($new_customers_sql);
}

// Get top customer categories by spending
$top_categories_query = "
    SELECT 
        CASE 
            WHEN total_amount >= 10000 THEN 'VIP (>₹10k)'
            WHEN total_amount >= 5000 THEN 'Premium (₹5k-10k)'
            WHEN total_amount >= 1000 THEN 'Regular (₹1k-5k)'
            ELSE 'Casual (<₹1k)'
        END as customer_category,
        COUNT(*) as customer_count,
        SUM(total_amount) as total_spent,
        AVG(total_amount) as avg_spent,
        AVG(purchase_count) as avg_purchases
    FROM (
        SELECT 
            c.id,
            COALESCE(SUM(s.total_amount), 0) as total_amount,
            COUNT(DISTINCT s.id) as purchase_count
        FROM customers c
        LEFT JOIN sales s ON (c.customer_phone = s.customer_phone OR c.customer_name = s.customer_name)
            AND s.status = 'completed'
            $date_condition
        WHERE c.status = 'active'
        $search_condition
        GROUP BY c.id
        HAVING purchase_count > 0
    ) as customer_stats
    GROUP BY customer_category
    ORDER BY total_spent DESC
";

$top_categories_stmt = $conn->prepare($top_categories_query);

if (!empty($date_params) && !empty($search_params)) {
    $top_categories_stmt->bind_param($date_param_types . $search_param_types, ...array_merge($date_params, $search_params));
    $top_categories_stmt->execute();
    $top_categories_result = $top_categories_stmt->get_result();
} elseif (!empty($date_params)) {
    $top_categories_stmt->bind_param($date_param_types, ...$date_params);
    $top_categories_stmt->execute();
    $top_categories_result = $top_categories_stmt->get_result();
} elseif (!empty($search_params)) {
    $top_categories_stmt->bind_param($search_param_types, ...$search_params);
    $top_categories_stmt->execute();
    $top_categories_result = $top_categories_stmt->get_result();
} else {
    $top_categories_result = $conn->query($top_categories_query);
}

// Get payment method preferences by customers
$payment_methods_query = "
    SELECT 
        s.payment_method,
        COUNT(DISTINCT s.id) as transaction_count,
        COUNT(DISTINCT CASE WHEN c.customer_phone IS NOT NULL THEN c.customer_phone ELSE s.customer_name END) as customer_count,
        SUM(s.total_amount) as total_amount,
        AVG(s.total_amount) as avg_amount
    FROM sales s
    LEFT JOIN customers c ON (c.customer_phone = s.customer_phone OR c.customer_name = s.customer_name)
    WHERE s.status = 'completed'
    AND (s.customer_phone IS NOT NULL OR s.customer_name IS NOT NULL)
    $date_condition
    GROUP BY s.payment_method
    ORDER BY total_amount DESC
";

$payment_methods_stmt = $conn->prepare($payment_methods_query);

if (!empty($date_params)) {
    $payment_methods_stmt->bind_param($date_param_types, ...$date_params);
    $payment_methods_stmt->execute();
    $payment_methods_result = $payment_methods_stmt->get_result();
} else {
    $payment_methods_result = $conn->query($payment_methods_query);
}

// Get customer purchase frequency
$purchase_frequency_query = "
    SELECT 
        purchase_frequency,
        COUNT(*) as customer_count,
        SUM(total_spent) as total_spent
    FROM (
        SELECT 
            c.id,
            c.customer_name,
            COUNT(DISTINCT s.id) as purchase_count,
            COUNT(DISTINCT DATE(s.sale_date)) as purchase_days,
            COUNT(DISTINCT DATE(s.sale_date)) / GREATEST(DATEDIFF(MAX(s.sale_date), MIN(s.sale_date)), 1) * 30 as monthly_frequency,
            CASE 
                WHEN COUNT(DISTINCT s.id) >= 10 AND COUNT(DISTINCT DATE(s.sale_date)) / GREATEST(DATEDIFF(MAX(s.sale_date), MIN(s.sale_date)), 1) * 30 >= 4 THEN 'Frequent (4+ times/month)'
                WHEN COUNT(DISTINCT s.id) >= 5 THEN 'Regular (2-3 times/month)'
                WHEN COUNT(DISTINCT s.id) >= 2 THEN 'Occasional (1 time/month)'
                ELSE 'One-time'
            END as purchase_frequency,
            SUM(s.total_amount) as total_spent
        FROM customers c
        LEFT JOIN sales s ON (c.customer_phone = s.customer_phone OR c.customer_name = s.customer_name)
            AND s.status = 'completed'
            $date_condition
        WHERE c.status = 'active'
        $search_condition
        GROUP BY c.id
        HAVING purchase_count > 0
    ) as frequency_data
    GROUP BY purchase_frequency
    ORDER BY FIELD(purchase_frequency, 'Frequent (4+ times/month)', 'Regular (2-3 times/month)', 'Occasional (1 time/month)', 'One-time')
";

$purchase_frequency_stmt = $conn->prepare($purchase_frequency_query);

if (!empty($date_params) && !empty($search_params)) {
    $purchase_frequency_stmt->bind_param($date_param_types . $search_param_types, ...array_merge($date_params, $search_params));
    $purchase_frequency_stmt->execute();
    $purchase_frequency_result = $purchase_frequency_stmt->get_result();
} elseif (!empty($date_params)) {
    $purchase_frequency_stmt->bind_param($date_param_types, ...$date_params);
    $purchase_frequency_stmt->execute();
    $purchase_frequency_result = $purchase_frequency_stmt->get_result();
} elseif (!empty($search_params)) {
    $purchase_frequency_stmt->bind_param($search_param_types, ...$search_params);
    $purchase_frequency_stmt->execute();
    $purchase_frequency_result = $purchase_frequency_stmt->get_result();
} else {
    $purchase_frequency_result = $conn->query($purchase_frequency_query);
}

// Get customer retention rate
$retention_query = "
    SELECT 
        CONCAT(MONTH(s.sale_date), '-', YEAR(s.sale_date)) as month_year,
        COUNT(DISTINCT CASE WHEN c.customer_phone IS NOT NULL THEN c.customer_phone ELSE s.customer_name END) as active_customers,
        COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(MONTH, s.sale_date, CURDATE()) = 1 
            THEN CASE WHEN c.customer_phone IS NOT NULL THEN c.customer_phone ELSE s.customer_name END END) as returning_customers
    FROM sales s
    LEFT JOIN customers c ON (c.customer_phone = s.customer_phone OR c.customer_name = s.customer_name)
    WHERE s.status = 'completed'
    AND s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    AND (s.customer_phone IS NOT NULL OR s.customer_name IS NOT NULL)
    GROUP BY YEAR(s.sale_date), MONTH(s.sale_date)
    ORDER BY YEAR(s.sale_date) DESC, MONTH(s.sale_date) DESC
    LIMIT 6
";

$retention_result = $conn->query($retention_query);

// Get inactive customers (no purchases in last 90 days)
$inactive_customers_query = "
    SELECT 
        c.customer_name,
        c.customer_phone,
        c.last_purchase_date,
        c.total_purchases,
        DATEDIFF(CURDATE(), c.last_purchase_date) as days_inactive
    FROM customers c
    WHERE c.status = 'active'
    AND c.last_purchase_date IS NOT NULL
    AND c.last_purchase_date < DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    ORDER BY days_inactive DESC
    LIMIT 10
";

$inactive_customers_result = $conn->query($inactive_customers_query);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8"/>
    <title>Customer Reports - Medical Store</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    
    
    <link rel="shortcut icon" href="assets/images/favicon.ico">
    
    
    <link href="assets/css/bootstrap.min.css" rel="stylesheet" type="text/css"/>
    
    <link href="assets/css/icons.min.css" rel="stylesheet" type="text/css"/>
    
    <link href="assets/css/app.min.css" rel="stylesheet" type="text/css"/>
    
    
    <link href="assets/libs/datatables.net-bs4/css/dataTables.bootstrap4.min.css" rel="stylesheet" type="text/css"/>
    
    <style>
        .customer-card {
            border-radius: 10px;
            transition: transform 0.2s;
        }
        .customer-card:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(0,0,0,0.1);
        }
        .customer-vip { border-color: #ffd700; }
        .customer-premium { border-color: #c0c0c0; }
        .customer-regular { border-color: #cd7f32; }
        .customer-casual { border-color: #6c757d; }
        
        .customer-badge {
            padding: 4px 10px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: 500;
            display: inline-block;
        }
        .badge-vip { background: linear-gradient(135deg, #FFD700, #FFA500); color: #000; }
        .badge-premium { background: linear-gradient(135deg, #C0C0C0, #A9A9A9); color: #000; }
        .badge-regular { background: linear-gradient(135deg, #CD7F32, #A0522D); color: white; }
        .badge-casual { background: linear-gradient(135deg, #6c757d, #495057); color: white; }
        
        .metric-card {

transition: transform 0.2s;
        }
        .metric-card:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(0,0,0,0.1);
        }
        .metric-customers { border-color: #0acf97; }
        .metric-revenue { border-color: #39afd1; }
        .metric-orders { border-color: #ffbc00; }
        .metric-loyalty { border-color: #fa5c7c; }
        
        .progress-bar-customer {
            background: linear-gradient(90deg, #6a11cb 0%, #2575fc 100%);
        }
        
        .activity-indicator {
            width: 10px;
            height: 10px;
            border-radius: 50%;
            display: inline-block;
            margin-right: 5px;
        }
        .active-customer { background-color: #0acf97; }
        .inactive-customer { background-color: #fa5c7c; }
        .new-customer { background-color: #39afd1; }
        
        .customer-avatar {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            background: linear-gradient(135deg, #6a11cb 0%, #2575fc 100%);
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: bold;
            font-size: 16px;
        }
    </style>
</head>

<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 mb-4">
                    <div class="col-xl-3 col-md-6">
                        <div class="card metric-card metric-customers">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($overall_stats['total_customers']); ?></h4>
                                        <p class="text-muted mb-0">Total Customers</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-account-group font-size-24 text-success"></i>
                                    </div>
                                </div>
                                <div class="mt-2">
                                    <small class="text-muted">
                                        <?php echo number_format($overall_stats['active_customers']); ?> active
                                    </small>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <div class="card metric-card metric-revenue">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($overall_stats['total_revenue'], 2); ?></h4>
                                        <p class="text-muted mb-0">Customer Revenue</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-cash-multiple font-size-24 text-info"></i>
                                    </div>
                                </div>
                                <div class="mt-2">
                                    <small class="text-muted">
                                        ₹<?php echo number_format($overall_stats['avg_order_value'], 2); ?> avg/order
                                    </small>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <div class="card metric-card metric-orders">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($overall_stats['total_transactions']); ?></h4>
                                        <p class="text-muted mb-0">Total Transactions</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-shopping font-size-24 text-warning"></i>
                                    </div>
                                </div>
                                <div class="mt-2">
                                    <small class="text-muted">
                                        <?php echo number_format($overall_stats['avg_days_since_purchase'], 0); ?> days since avg purchase
                                    </small>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-3 col-md-6">
                        <div class="card metric-card metric-loyalty">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($overall_stats['total_loyalty_points']); ?></h4>
                                        <p class="text-muted mb-0">Loyalty Points</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-star-circle font-size-24 text-danger"></i>
                                    </div>
                                </div>
                                <div class="mt-2">
                                    <small class="text-muted">
                                        Active loyalty program
                                    </small>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mb-4">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h5 class="card-title mb-3">Customer Analysis Filters</h5>
                                <form method="GET" action="" class="row g-3">
                                    <div class="col-md-3">
                                        <label class="form-label">Time Range</label>
                                        <select class="form-select" name="time_range" onchange="this.form.submit()">
                                            <option value="today" <?php echo $time_range == 'today' ? 'selected' : ''; ?>>Today</option>
                                            <option value="yesterday" <?php echo $time_range == 'yesterday' ? 'selected' : ''; ?>>Yesterday</option>
                                            <option value="week" <?php echo $time_range == 'week' ? 'selected' : ''; ?>>Last 7 Days</option>
                                            <option value="month" <?php echo $time_range == 'month' ? 'selected' : ''; ?>>Last 30 Days</option>
                                            <option value="quarter" <?php echo $time_range == 'quarter' ? 'selected' : ''; ?>>Last 90 Days</option>
                                            <option value="year" <?php echo $time_range == 'year' ? 'selected' : ''; ?>>Last 365 Days</option>
                                            <option value="all" <?php echo $time_range == 'all' ? 'selected' : ''; ?>>All Time</option>
                                            <option value="custom" <?php echo $time_range == 'custom' ? 'selected' : ''; ?>>Custom Range</option>
                                        </select>
                                    </div>
                                    
                                    <?php if ($time_range == 'custom'): ?>
                                    <div class="col-md-3">
                                        <label class="form-label">Start Date</label>
                                        <input type="date" class="form-control" name="start_date" 
                                               value="<?php echo $start_date; ?>" max="<?php echo date('Y-m-d'); ?>">
                                    </div>
                                    <div class="col-md-3">
                                        <label class="form-label">End Date</label>
                                        <input type="date" class="form-control" name="end_date" 
                                               value="<?php echo $end_date; ?>" max="<?php echo date('Y-m-d'); ?>">
                                    </div>
                                    <?php else: ?>
                                    <div class="col-md-3"></div>
                                    <div class="col-md-3"></div>
                                    <?php endif; ?>
                                    
                                    <div class="col-md-3">
                                        <label class="form-label">Sort By</label>
                                        <select class="form-select" name="sort_by" onchange="this.form.submit()">
                                            <option value="total_spent" <?php echo $sort_by == 'total_spent' ? 'selected' : ''; ?>>Total Spent</option>
                                            <option value="purchase_count" <?php echo $sort_by == 'purchase_count' ? 'selected' : ''; ?>>Purchase Count</option>
                                            <option value="last_purchase" <?php echo $sort_by == 'last_purchase' ? 'selected' : ''; ?>>Last Purchase</option>
                                            <option value="customer_name" <?php echo $sort_by == 'customer_name' ? 'selected' : ''; ?>>Customer Name</option>
                                            <option value="avg_order_value" <?php echo $sort_by == 'avg_order_value' ? 'selected' : ''; ?>>Avg Order Value</option>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-3">
                                        <label class="form-label">Show Top</label>
                                        <select class="form-select" name="limit" onchange="this.form.submit()">
                                            <option value="10" <?php echo $limit == 10 ? 'selected' : ''; ?>>Top 10</option>
                                            <option value="20" <?php echo $limit == 20 ? 'selected' : ''; ?>>Top 20</option>
                                            <option value="30" <?php echo $limit == 30 ? 'selected' : ''; ?>>Top 30</option>
                                            <option value="50" <?php echo $limit == 50 ? 'selected' : ''; ?>>Top 50</option>
                                            <option value="100" <?php echo $limit == 100 ? 'selected' : ''; ?>>All Customers</option>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-6">
                                        <label class="form-label">Search Customer</label>
                                        <div class="input-group">
                                            <input type="text" class="form-control" name="search" 
                                                   value="<?php echo htmlspecialchars($search_term); ?>" 
                                                   placeholder="Search by name, phone, email">
                                            <button class="btn btn-outline-secondary" type="submit">
                                                <i class="mdi mdi-magnify"></i>
                                            </button>
                                        </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="customer-reports.php" class="btn btn-outline-secondary ms-2">
                                            <i class="mdi mdi-refresh me-1"></i> Reset
                                        </a>
                                        <button type="button" class="btn btn-outline-success ms-2" onclick="printReport()">
                                            <i class="mdi mdi-printer me-1"></i> Print
                                        </button>
                                        <button type="button" class="btn btn-outline-info ms-2" onclick="exportToExcel()">
                                            <i class="mdi mdi-file-excel me-1"></i> Export Excel
                                        </button>
                                        <a href="add-customer.php" class="btn btn-outline-primary ms-2">
                                            <i class="mdi mdi-account-plus me-1"></i> Add Customer
                                        </a>
                                        <a href="loyalty-program.php" class="btn btn-outline-warning ms-2">
                                            <i class="mdi mdi-crown me-1"></i> Loyalty Program
                                        </a>
                                    </div>
                                    
                                    <?php if ($time_range == 'custom'): ?>
                                    <div class="col-md-12">
                                        <small class="text-muted">
                                            <i class="mdi mdi-calendar-range me-1"></i>
                                            Custom range selected: <?php echo date('d M Y', strtotime($start_date)); ?> to <?php echo date('d M Y', strtotime($end_date)); ?>
                                        </small>
                                    </div>
                                    <?php else: ?>
                                    <div class="col-md-12">
                                        <small class="text-muted">
                                            <i class="mdi mdi-clock-outline me-1"></i>
                                            Showing data for: <?php echo ucfirst($time_range); ?> | 
                                            Sorted by: <?php echo str_replace('_', ' ', ucfirst($sort_by)); ?> | 
                                            Showing: <?php echo $limit == 100 ? 'All' : 'Top ' . $limit; ?> customers
                                            <?php if (!empty($search_term)): ?>
                                                | Search: "<?php echo htmlspecialchars($search_term); ?>"
                                            <?php endif; ?>
                                        </small>
                                    </div>
                                    <?php endif; ?>
                                </form>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mb-4">
                    
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h5 class="card-title mb-4">Customer Categories by Spending</h5>
                                <?php if ($top_categories_result && $top_categories_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-hover mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Category</th>
                                                    <th>Customers</th>
                                                    <th>Total Spent</th>
                                                    <th>Avg. Spent</th>
                                                    <th>Avg. Purchases</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($category = $top_categories_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td>
                                                            <span class="customer-badge badge-<?php 
                                                                echo strtolower(explode(' ', $category['customer_category'])[0]); 
                                                            ?>">
                                                                <?php echo $category['customer_category']; ?>
                                                            </span>
                                                        </td>
                                                        <td><?php echo $category['customer_count']; ?></td>
                                                        <td>₹<?php echo number_format($category['total_spent'], 2); ?></td>
                                                        <td>₹<?php echo number_format($category['avg_spent'], 2); ?></td>
                                                        <td><?php echo number_format($category['avg_purchases'], 1); ?></td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-chart-pie display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No customer category data</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>

<div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h5 class="card-title mb-4">Purchase Frequency Analysis</h5>
                                <?php if ($purchase_frequency_result && $purchase_frequency_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-hover mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Frequency</th>
                                                    <th>Customers</th>
                                                    <th>Total Spent</th>
                                                    <th>Avg. Customer Value</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($frequency = $purchase_frequency_result->fetch_assoc()): ?>
                                                    <?php 
                                                    $avg_value = $frequency['customer_count'] > 0 ? 
                                                        $frequency['total_spent'] / $frequency['customer_count'] : 0;
                                                    ?>
                                                    <tr>
                                                        <td><?php echo $frequency['purchase_frequency']; ?></td>
                                                        <td><?php echo $frequency['customer_count']; ?></td>
                                                        <td>₹<?php echo number_format($frequency['total_spent'], 2); ?></td>
                                                        <td>₹<?php echo number_format($avg_value, 2); ?></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 purchase frequency data</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mb-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">Customer Performance Analysis</h4>
                                    <div class="text-muted">
                                        Showing <?php echo number_format($customers_result->num_rows); ?> customers | 
                                        Generated: <?php echo date('d M Y h:i A'); ?>
                                    </div>
                                </div>
                                
                                <?php if ($customers_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-hover mb-0" id="customersTable">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Customer</th>
                                                    <th>Contact</th>
                                                    <th>Purchase History</th>
                                                    <th>Spending Analysis</th>
                                                    <th>Loyalty</th>
                                                    <th>Activity</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($customer = $customers_result->fetch_assoc()): 
                                                    // Determine customer category
                                                    $total_spent = $customer['total_amount'];
                                                    if ($total_spent >= 10000) {
                                                        $category = 'VIP (>₹10k)';
                                                        $category_class = 'vip';
                                                        $badge_class = 'badge-vip';
                                                    } elseif ($total_spent >= 5000) {
                                                        $category = 'Premium (₹5k-10k)';
                                                        $category_class = 'premium';
                                                        $badge_class = 'badge-premium';
                                                    } elseif ($total_spent >= 1000) {
                                                        $category = 'Regular (₹1k-5k)';
                                                        $category_class = 'regular';
                                                        $badge_class = 'badge-regular';
                                                    } else {
                                                        $category = 'Casual (<₹1k)';
                                                        $category_class = 'casual';
                                                        $badge_class = 'badge-casual';
                                                    }
                                                    
                                                    // Determine activity status
                                                    $days_since = $customer['days_since_last_purchase'];
                                                    if ($days_since <= 7) {
                                                        $activity_status = 'Active';
                                                        $activity_class = 'active-customer';
                                                    } elseif ($days_since <= 30) {
                                                        $activity_status = 'Recent';
                                                        $activity_class = 'new-customer';
                                                    } else {
                                                        $activity_status = 'Inactive';
                                                        $activity_class = 'inactive-customer';
                                                    }
                                                    
                                                    // Get initials for avatar
                                                    $initials = '';
                                                    $name_parts = explode(' ', $customer['customer_name']);
                                                    foreach ($name_parts as $part) {
                                                        $initials .= strtoupper(substr($part, 0, 1));
                                                        if (strlen($initials) >= 2) break;
                                                    }
                                                    if (empty($initials)) {
                                                        $initials = 'CU';
                                                    }
                                                ?>
                                                    <tr class="customer-card customer-<?php echo $category_class; ?>">
                                                        <td>
                                                            <div class="d-flex align-items-center">
                                                                <div class="customer-avatar me-3">
                                                                    <?php echo $initials; ?>
                                                                </div>
                                                                <div>
                                                                    <h6 class="mb-1"><?php echo htmlspecialchars($customer['customer_name']); ?></h6>
                                                                    <span class="<?php echo $badge_class; ?>"><?php echo $category; ?></span>
                                                                    <?php if ($customer['purchase_count'] == 0): ?>
                                                                        <span class="badge bg-secondary ms-1">No purchases</span>
                                                                    <?php endif; ?>
                                                                </div>
                                                            </div>
                                                        </td>
                                                        <td>
                                                            <?php if ($customer['customer_phone']): ?>
                                                                <strong><?php echo htmlspecialchars($customer['customer_phone']); ?></strong>
                                                            <?php endif; ?>
                                                            <?php if ($customer['customer_email']): ?>
                                                                <br>
                                                                <small class="text-muted"><?php echo htmlspecialchars($customer['customer_email']); ?></small>
                                                            <?php endif; ?>
                                                            <?php if ($customer['customer_address']): ?>
                                                                <br>
                                                                <small class="text-muted"><?php echo htmlspecialchars(substr($customer['customer_address'], 0, 30)); ?>...</small>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <div class="mb-1">
                                                                <strong><?php echo $customer['purchase_count']; ?></strong> purchases
                                                            </div>
                                                            <div class="mb-1">
                                                                <small class="text-muted">
                                                                    First: <?php echo $customer['first_purchase_date'] ? date('d M Y', strtotime($customer['first_purchase_date'])) : 'Never'; ?>
                                                                </small>
                                                            </div>
                                                            <div>
                                                                <small class="text-muted">
                                                                    Last: <?php echo $customer['last_purchase_date'] ? date('d M Y', strtotime($customer['last_purchase_date'])) : 'Never'; ?>
                                                                </small>
                                                            </div>
                                                        </td>
                                                        <td>
                                                            <div class="mb-1">
                                                                <strong class="text-success">₹<?php echo number_format($customer['total_amount'], 2); ?></strong> total
                                                            </div>
                                                            <div class="mb-1">
                                                                <small class="text-muted">
                                                                    Avg: ₹<?php echo number_format($customer['avg_order_value'], 2); ?>
                                                                </small>
                                                            </div>
                                                            <div class="progress" style="height: 5px;">
                                                                <?php 
                                                                // Calculate percentage of max spending (for progress bar)
                                                                $max_spending = max($customer['total_amount'], 1);
                                                                $percentage = ($customer['total_amount'] / $max_spending) * 100;
                                                                ?>
                                                                <div class="progress-bar progress-bar-customer" role="progressbar" 
                                                                     style="width: <?php echo min($percentage, 100); ?>%">
                                                                </div>
                                                            </div>
                                                        </td>
                                                        <td>
                                                            <?php if ($customer['loyalty_points'] > 0): ?>
                                                                <div class="mb-1">
                                                                    <strong class="text-warning"><?php echo $customer['loyalty_points']; ?></strong> points
                                                                </div>
                                                                <div>
                                                                    <small class="text-muted">
                                                                        Value: ₹<?php echo number_format($customer['loyalty_points'] * 0.1, 2); ?>
                                                                    </small>
                                                                </div>
                                                            <?php else: ?>
                                                                <span class="text-muted">No points</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <div class="d-flex align-items-center">
                                                                <span class="activity-indicator <?php echo $activity_class; ?>"></span>
                                                                <div>
                                                                    <div class="mb-1"><?php echo $activity_status; ?></div>
                                                                    <div>
                                                                        <small class="text-muted">
                                                                            <?php if ($customer['last_purchase_date']): ?>
                                                                                <?php echo $customer['days_since_last_purchase']; ?> days ago
                                                                            <?php else: ?>
                                                                                Never purchased
                                                                            <?php endif; ?>
                                                                        </small>
                                                                    </div>
                                                                </div>
                                                            </div>
                                                        </td>
                                                        <td>
                                                            <div class="btn-group">
                                                                <a href="customer-details.php?id=<?php echo $customer['id']; ?>" 
                                                                   class="btn btn-sm btn-outline-primary" title="View Details">
                                                                    <i class="mdi mdi-eye"></i>
                                                                </a>
                                                                <a href="customer-sales.php?customer_id=<?php echo $customer['id']; ?>" 
                                                                   class="btn btn-sm btn-outline-info" title="Sales History">
                                                                    <i class="mdi mdi-history"></i>
                                                                </a>
                                                                <a href="edit-customer.php?id=<?php echo $customer['id']; ?>" 
                                                                   class="btn btn-sm btn-outline-success" title="Edit">
                                                                    <i class="mdi mdi-pencil"></i>
                                                                </a>
                                                                <button type="button" class="btn btn-sm btn-outline-warning" 
                                                                        onclick="addLoyaltyPoints(<?php echo $customer['id']; ?>, '<?php echo htmlspecialchars($customer['customer_name']); ?>')"
                                                                        title="Add Loyalty Points">
                                                                    <i class="mdi mdi-plus-circle"></i>
                                                                </button>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-5">
                                        <i class="mdi mdi-account-off display-4 text-muted"></i>
                                        <h4 class="mt-3">No Customer Data Found</h4>
                                        <p class="text-muted">No customers match the selected filters.</p>
                                        <a href="add-customer.php" class="btn btn-primary mt-2">
                                            <i class="mdi mdi-account-plus me-1"></i> Add First Customer
                                        </a>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mb-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">
                                    <h5 class="card-title">New Customers (Last 30 Days)</h5>
                                    <span class="badge bg-success"><?php echo $new_customers_result ? $new_customers_result->num_rows : 0; ?> new</span>
                                </div>
                                <?php if ($new_customers_result && $new_customers_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-hover mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Customer</th>
                                                    <th>Phone</th>
                                                    <th>First Purchase</th>
                                                    <th>Purchases</th>
                                                    <th>Total Spent</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($new_customer = $new_customers_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($new_customer['customer_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($new_customer['customer_phone']); ?></td>
                                                        <td><?php echo date('d M Y', strtotime($new_customer['first_purchase_date'])); ?></td>
                                                        <td><?php echo $new_customer['purchase_count']; ?></td>
                                                        <td class="text-success">₹<?php echo number_format($new_customer['total_spent'], 2); ?></td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-account-plus display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No new customers in last 30 days</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">
                                    <h5 class="card-title">Inactive Customers (90+ Days)</h5>
                                    <span class="badge bg-danger"><?php echo $inactive_customers_result ? $inactive_customers_result->num_rows : 0; ?> inactive</span>
                                </div>
                                <?php if ($inactive_customers_result && $inactive_customers_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-hover mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Customer</th>
                                                    <th>Phone</th>
                                                    <th>Last Purchase</th>
                                                    <th>Total Purchases</th>
                                                    <th>Days Inactive</th>
                                                    <th>Action</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($inactive = $inactive_customers_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($inactive['customer_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($inactive['customer_phone']); ?></td>
                                                        <td><?php echo date('d M Y', strtotime($inactive['last_purchase_date'])); ?></td>
                                                        <td>₹<?php echo number_format($inactive['total_purchases'], 2); ?></td>
                                                        <td class="text-danger"><?php echo $inactive['days_inactive']; ?> days</td>
                                                        <td>
                                                            <button type="button" class="btn btn-sm btn-outline-primary" 
                                                                    onclick="sendReminder('<?php echo htmlspecialchars($inactive['customer_phone']); ?>', '<?php echo htmlspecialchars($inactive['customer_name']); ?>')">
                                                                <i class="mdi mdi-email-send"></i> Remind
                                                            </button>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-account-check display-4 text-success"></i>
                                        <p class="text-muted mt-2">All customers are active!</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    
                    <div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h5 class="card-title mb-4">Payment Method Preferences</h5>
                                <?php if ($payment_methods_result && $payment_methods_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-hover mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Method</th>
                                                    <th>Customers</th>
                                                    <th>Transactions</th>
                                                    <th>Total Amount</th>
                                                    <th>Avg. Amount</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($method = $payment_methods_result->fetch_assoc()): ?>
                                                    <tr>
                                                        <td>
                                                            <span class="badge bg-<?php 
                                                                echo strtolower($method['payment_method']) == 'cash' ? 'success' : 
                                                                    (strtolower($method['payment_method']) == 'card' ? 'info' : 
                                                                    (strtolower($method['payment_method']) == 'upi' ? 'primary' : 'secondary'));
                                                            ?>">
                                                                <?php echo strtoupper($method['payment_method']); ?>
                                                            </span>
                                                        </td>
                                                        <td><?php echo $method['customer_count']; ?></td>
                                                        <td><?php echo $method['transaction_count']; ?></td>
                                                        <td>₹<?php echo number_format($method['total_amount'], 2); ?></td>
                                                        <td>₹<?php echo number_format($method['avg_amount'], 2); ?></td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="mdi mdi-credit-card-off display-4 text-muted"></i>
                                        <p class="text-muted mt-2">No payment method data</p>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>

<div class="col-lg-6">
                        <div class="card">
                            <div class="card-body">
                                <h5 class="card-title mb-4">Customer Retention (Last 6 Months)</h5>
                                <?php if ($retention_result && $retention_result->num_rows > 0): ?>
                                    <div class="table-responsive">
                                        <table class="table table-hover mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Month</th>
                                                    <th>Active Customers</th>
                                                    <th>Returning Customers</th>
                                                    <th>Retention Rate</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($retention = $retention_result->fetch_assoc()): 
                                                    $retention_rate = $retention['active_customers'] > 0 ? 
                                                        ($retention['returning_customers'] / $retention['active_customers']) * 100 : 0;
                                                ?>
                                                    <tr>
                                                        <td><?php echo $retention['month_year']; ?></td>
                                                        <td><?php echo $retention['active_customers']; ?></td>
                                                        <td><?php echo $retention['returning_customers']; ?></td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $retention_rate >= 70 ? 'success' : ($retention_rate >= 50 ? 'warning' : 'danger'); ?>">
                                                                <?php echo number_format($retention_rate, 1); ?>%
                                                            </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 retention 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-customer.php" class="btn btn-outline-primary w-100">
                                            <i class="mdi mdi-account-plus me-1"></i> Add Customer
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="new-sale.php" class="btn btn-outline-success w-100">
                                            <i class="mdi mdi-cart-plus me-1"></i> New Sale
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="loyalty-program.php" class="btn btn-outline-warning w-100">
                                            <i class="mdi mdi-crown me-1"></i> Loyalty Program
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="customer-import.php" class="btn btn-outline-info w-100">
                                            <i class="mdi mdi-import me-1"></i> Import Customers
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="customer-segments.php" class="btn btn-outline-secondary w-100">
                                            <i class="mdi mdi-chart-pie me-1"></i> Customer Segments
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <a href="customer-birthdays.php" class="btn btn-outline-danger w-100">
                                            <i class="mdi mdi-cake me-1"></i> Birthdays
                                        </a>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <button type="button" class="btn btn-outline-success w-100" onclick="sendBulkSMS()">
                                            <i class="mdi mdi-message-text me-1"></i> Bulk SMS
                                        </button>
                                    </div>
                                    <div class="col-md-3 mb-3">
                                        <button type="button" class="btn btn-outline-info w-100" onclick="sendNewsletter()">
                                            <i class="mdi mdi-email-send me-1"></i> Newsletter
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

</div>
            
        </div>

<footer class="footer">
            <div class="container-fluid">
                <div class="row">
                    <div class="col-12">
                        ©
                        <script>document.write(new Date().getFullYear())</script> MD Medicals<span class="d-none d-sm-inline-block"> -
                            designed and developed by Ecommer <a href="https://ecommer.in" target="_blank">ecommer.in</a></span>
                    </div>
                </div>
            </div>
        </footer>
    </div>

</div>

<div class="modal fade" id="loyaltyModal" tabindex="-1" aria-labelledby="loyaltyModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="loyaltyModalLabel">Add Loyalty Points</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <form id="loyaltyForm" method="POST" action="add-loyalty-points.php">
                    <input type="hidden" id="customer_id" name="customer_id">
                    <div class="mb-3">
                        <label class="form-label">Customer</label>
                        <input type="text" class="form-control" id="customer_name" readonly>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Points to Add</label>
                        <input type="number" class="form-control" name="points" min="1" max="10000" value="10" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Reason</label>
                        <select class="form-select" name="reason" required>
                            <option value="">Select reason...</option>
                            <option value="Purchase Bonus">Purchase Bonus</option>
                            <option value="Birthday Bonus">Birthday Bonus</option>
                            <option value="Referral Bonus">Referral Bonus</option>
                            <option value="Customer Service">Customer Service</option>
                            <option value="Special Promotion">Special Promotion</option>
                            <option value="Other">Other</option>
                        </select>
                    </div>
                </form>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                <button type="submit" form="loyaltyForm" class="btn btn-primary">Add Points</button>
            </div>
        </div>
    </div>
</div>

<script src="assets/libs/jquery/jquery.min.js"></script>
<script src="assets/libs/bootstrap/js/bootstrap.bundle.min.js"></script>
<script src="assets/libs/metismenu/metisMenu.min.js"></script>
<script src="assets/libs/simplebar/simplebar.min.js"></script>
<script src="assets/libs/node-waves/waves.min.js"></script>
<script src="assets/libs/datatables.net/js/jquery.dataTables.min.js"></script>
<script src="assets/libs/datatables.net-bs4/js/dataTables.bootstrap4.min.js"></script>

<script src="assets/js/app.js"></script>

// Print report
    function printReport() {
        const printWindow = window.open('', '_blank');
        
        const printHTML = `
            <html>
                <head>
                    <title>Customer Report - ${new Date().toLocaleDateString()}</title>
                    <style>
                        body { font-family: Arial, sans-serif; margin: 20px; }
                        h1 { color: #333; border-bottom: 2px solid #007bff; padding-bottom: 10px; }
                        .print-header { margin-bottom: 30px; }
                        .print-footer { margin-top: 30px; font-size: 12px; color: #666; border-top: 1px solid #ddd; padding-top: 10px; }
                        table { width: 100%; border-collapse: collapse; margin: 20px 0; }
                        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                        th { background-color: #f5f5f5; font-weight: bold; }
                        .summary { background: #f8f9fa; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
                        @media print {
                            @page { margin: 0.5cm; }
                            .no-print { display: none; }
                        }
                    </style>
                </head>
                <body>
                    <div class="print-header">
                        <h1>Customer Analysis Report</h1>
                        <p><strong>Generated:</strong> ${new Date().toLocaleString()}</p>
                        <p><strong>Time Range:</strong> <?php echo ucfirst($time_range); ?></p>
                        <p><strong>Total Customers:</strong> <?php echo number_format($overall_stats['total_customers']); ?></p>
                        <p><strong>Active Customers:</strong> <?php echo number_format($overall_stats['active_customers']); ?></p>
                    </div>
                    
                    <div class="summary">
                        <h3>Summary Statistics</h3>
                        <p><strong>Total Revenue:</strong> ₹<?php echo number_format($overall_stats['total_revenue'], 2); ?></p>
                        <p><strong>Total Transactions:</strong> <?php echo number_format($overall_stats['total_transactions']); ?></p>
                        <p><strong>Average Order Value:</strong> ₹<?php echo number_format($overall_stats['avg_order_value'], 2); ?></p>
                        <p><strong>Total Loyalty Points:</strong> <?php echo number_format($overall_stats['total_loyalty_points']); ?></p>
                    </div>
                    
                    <h3>Top Customers</h3>
                    <table>
                        <thead>
                            <tr>
                                <th>Customer Name</th>
                                <th>Phone</th>
                                <th>Purchases</th>
                                <th>Total Spent</th>
                                <th>Avg. Order</th>
                                <th>Last Purchase</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php 
                            // Reset pointer and fetch data again for print
                            $customers_result->data_seek(0);
                            $print_count = 0;
                            while ($customer = $customers_result->fetch_assoc() && $print_count < 20): 
                                $print_count++;
                            ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($customer['customer_name']); ?></td>
                                    <td><?php echo htmlspecialchars($customer['customer_phone']); ?></td>
                                    <td><?php echo $customer['purchase_count']; ?></td>
                                    <td>₹<?php echo number_format($customer['total_amount'], 2); ?></td>
                                    <td>₹<?php echo number_format($customer['avg_order_value'], 2); ?></td>
                                    <td><?php echo $customer['last_purchase_date'] ? date('d M Y', strtotime($customer['last_purchase_date'])) : 'Never'; ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                    
                    <div class="print-footer">
                        <p>Generated by Medical Store Management System | <?php echo date('d M Y H:i:s'); ?></p>
                        <p>Report ID: CR<?php echo time(); ?></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>
        `;
        
        printWindow.document.write(printHTML);
        printWindow.document.close();
        printWindow.focus();
    }

// Export to Excel
    function exportToExcel() {
        const table = document.getElementById('customersTable');
        if (!table) {
            alert('No data to export!');
            return;
        }
        
        // Create CSV content
        let csv = [];
        const rows = table.querySelectorAll('tr');
        
        for (let i = 0; i < rows.length; i++) {
            const row = [], cols = rows[i].querySelectorAll('td, th');
            
            for (let j = 0; j < cols.length; j++) {
                // Clean the data
                let data = cols[j].innerText.replace(/(\r\n|\n|\r)/gm, '').replace(/(\s\s)/gm, ' ');
                data = data.replace(/"/g, '""');
                row.push('"' + data + '"');
            }
            
            csv.push(row.join(','));
        }
        
        // Download CSV file
        const csvString = csv.join('\n');
        const blob = new Blob([csvString], { type: 'text/csv' });
        const url = window.URL.createObjectURL(blob);
        const a = document.createElement('a');
        a.href = url;
        a.download = 'customer-report-<?php echo date('Y-m-d'); ?>.csv';
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
        window.URL.revokeObjectURL(url);
    }

// Add loyalty points
    function addLoyaltyPoints(customerId, customerName) {
        document.getElementById('customer_id').value = customerId;
        document.getElementById('customer_name').value = customerName;
        const modal = new bootstrap.Modal(document.getElementById('loyaltyModal'));
        modal.show();
    }

// Send reminder to inactive customer
    function sendReminder(phone, name) {
        if (confirm(`Send reminder to ${name} (${phone})?`)) {
            const message = prompt('Enter reminder message:', `Hi ${name}, we miss you at MD Medicals! Visit us for special offers.`);
            if (message) {
                // In a real implementation, you would send SMS via API
                alert(`Reminder sent to ${name} (${phone})`);
            }
        }
    }

// Bulk SMS
    function sendBulkSMS() {
        const message = prompt('Enter message for bulk SMS:');
        if (message) {
            alert(`Bulk SMS would be sent to all active customers with message: "${message}"`);
            // In real implementation, send via SMS gateway API
        }
    }

// Newsletter
    function sendNewsletter() {
        const subject = prompt('Enter newsletter subject:');
        if (subject) {
            const message = prompt('Enter newsletter message:');
            if (message) {
                alert(`Newsletter would be sent to all customers with email`);
                // In real implementation, send via email API
            }
        }
    }

// Quick filters
    function filterVIP() {
        window.location.href = 'customer-reports.php?sort_by=total_spent&limit=20&search=';
    }

function filterInactive() {
        window.location.href = 'customer-reports.php?sort_by=last_purchase&limit=20';
    }

function filterNew() {
        window.location.href = 'customer-reports.php?time_range=month&sort_by=first_purchase';
    }

// Auto-refresh every 5 minutes
    setTimeout(function() {
        window.location.reload();
    }, 300000);

// Quick view customer details
    function viewCustomerDetails(customerId) {
        window.open('customer-details.php?id=' + customerId, '_blank');
    }
</script>

</body>
</html>
<?php
// Close database connections
if (isset($stmt)) $stmt->close();
if (isset($stats_stmt)) $stats_stmt->close();
if (isset($new_customers_stmt)) $new_customers_stmt->close();
if (isset($top_categories_stmt)) $top_categories_stmt->close();
if (isset($payment_methods_stmt)) $payment_methods_stmt->close();
if (isset($purchase_frequency_stmt)) $purchase_frequency_stmt->close();
if (isset($conn)) $conn->close();
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: customer-reports.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation