Enhanced PHP File Manager

⬆️ Go Up (..)

📊 Server Information

Server:

LiteSpeed

System:

Linux 5.14.0-503.34.1.el9_5.x86_64

PHP Version:

8.2.30

User:

u329947844

Directory:

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

Free Space:

10.28 TB

🔧 Quick Actions

✏️ Editing: pipes-reports.php

<?php
// === server bootstrap & includes ===
include('includes/db.php');

// Check authentication

// Normalize connection charset/collation
mysqli_query($conn, "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
mysqli_query($conn, "SET collation_connection = 'utf8mb4_unicode_ci'");

// --- helpers ---
function h($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }

// --- UTC to IST conversion functions ---
function utcToIst($utcDateTime, $format = 'Y-m-d h:i A') {
    if (empty($utcDateTime)) return '';
    
    try {
        $utc = new DateTime($utcDateTime, new DateTimeZone('UTC'));
        $utc->setTimezone(new DateTimeZone('Asia/Kolkata'));
        return $utc->format($format);
    } catch (Exception $e) {
        return $utcDateTime;
    }
}

// --- Get Pipes Category ID ---
$pipes_category_id = null;
$q = mysqli_query($conn, "SELECT id FROM categories WHERE category_name = 'Pipes' COLLATE utf8mb4_unicode_ci LIMIT 1");
if ($q && ($r = mysqli_fetch_assoc($q))) {
    $pipes_category_id = (int)$r['id'];
}

// --- Get Pipes Products ---
$pipes_products = [];
if ($pipes_category_id) {
    $q = mysqli_query($conn, "SELECT id, product_name, quantity FROM products WHERE category_id = {$pipes_category_id} ORDER BY product_name");
    if ($q) {
        while ($row = mysqli_fetch_assoc($q)) {
            $pipes_products[] = [
                'id' => (int)$row['id'],
                'name' => $row['product_name'],
                'quantity' => (int)$row['quantity']
            ];
        }
    }
}

// --- Date range filters ---
$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');
$product_id = isset($_GET['product_id']) ? (int)$_GET['product_id'] : 0;
$period = isset($_GET['period']) ? $_GET['period'] : 'custom';

// Apply predefined periods
$today = date('Y-m-d');
switch ($period) {
    case 'today':
        $start_date = $today;
        $end_date = $today;
        break;
    case 'this_week':
        $start_date = date('Y-m-d', strtotime('monday this week'));
        $end_date = $today;
        break;
    case 'this_month':
        $start_date = date('Y-m-01');
        $end_date = $today;
        break;
    case 'last_6months':
        $start_date = date('Y-m-d', strtotime('-6 months'));
        $end_date = $today;
        break;
    case 'this_year':
        $start_date = date('Y-01-01');
        $end_date = $today;
        break;
}

$date_range_display = date('M d, Y', strtotime($start_date)) . ' to ' . date('M d, Y', strtotime($end_date));

// --- Build WHERE conditions ---
$where_conditions = [];
$where_conditions[] = "c.id = {$pipes_category_id}";
$where_conditions[] = "i.created_at BETWEEN '{$start_date} 00:00:00' AND '{$end_date} 23:59:59'";

if ($product_id > 0) {
    $product_name = '';
    foreach ($pipes_products as $product) {
        if ($product['id'] == $product_id) {
            $product_name = mysqli_real_escape_string($conn, $product['name']);
            break;
        }
    }
    if ($product_name) {
        $where_conditions[] = "p.product_name = '{$product_name}' COLLATE utf8mb4_unicode_ci";
    }
}

$where_clause = implode(' AND ', $where_conditions);
$where_clause_for_stats = str_replace("c.id = {$pipes_category_id}", "p.category_id = {$pipes_category_id}", $where_clause);

// --- Pipes Summary Statistics ---
$summary_stats = [
    'total_quantity' => 0,
    'total_products' => 0,
    'today_sales' => 0.00,
    'total_investment' => 0.00,
    'total_profit' => 0.00,
    'total_revenue' => 0.00,
    'total_orders' => 0,
    'avg_margin' => 0.00,
    'low_stock_count' => 0
];

if ($pipes_category_id) {
    // Current inventory stats (all products in Pipes category)
    $sql = "
        SELECT 
            COUNT(*) AS total_products,
            COALESCE(SUM(quantity), 0) AS total_quantity,
            COALESCE(SUM(stock_price * quantity), 0) AS total_investment,
            COALESCE(SUM(customer_price * quantity), 0) AS total_sellable,
            COALESCE(SUM(profit * quantity), 0) AS total_profit,
            SUM(CASE WHEN quantity <= 10 THEN 1 ELSE 0 END) AS low_stock_count
        FROM products p
        WHERE p.category_id = {$pipes_category_id}
    ";
    
    $q = mysqli_query($conn, $sql);
    if ($q && ($r = mysqli_fetch_assoc($q))) {
        $summary_stats['total_products'] = (int)$r['total_products'];
        $summary_stats['total_quantity'] = (int)$r['total_quantity'];
        $summary_stats['total_investment'] = (float)$r['total_investment'];
        $summary_stats['total_profit'] = (float)$r['total_profit'];
        $summary_stats['low_stock_count'] = (int)$r['low_stock_count'];
    }
    
    // Sales stats for date range
    $sql = "
        SELECT 
            COUNT(DISTINCT i.id) AS total_orders,
            COALESCE(SUM(ii.total_price), 0) AS total_revenue,
            COALESCE(SUM(p.stock_price * ii.quantity), 0) AS total_cost,
            COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) AS total_profit,
            CASE 
                WHEN COALESCE(SUM(ii.total_price), 0) > 0 
                THEN ROUND(
                    (COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) / 
                    COALESCE(SUM(ii.total_price), 0)) * 100, 2
                )
                ELSE 0 
            END AS avg_margin
        FROM invoices i
        LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
        LEFT JOIN products p ON p.product_name = ii.product_name COLLATE utf8mb4_unicode_ci
        LEFT JOIN categories c ON c.id = p.category_id 
            AND c.category_name = ii.product_category COLLATE utf8mb4_unicode_ci
        WHERE {$where_clause}
    ";
    
    $q = mysqli_query($conn, $sql);
    if ($q && ($r = mysqli_fetch_assoc($q))) {
        $summary_stats['total_orders'] = (int)$r['total_orders'];
        $summary_stats['total_revenue'] = (float)$r['total_revenue'];
        $summary_stats['total_profit'] = (float)$r['total_profit'];
        $summary_stats['avg_margin'] = (float)$r['avg_margin'];
    }
    
    // Today's sales separately
    $today_sql = "
        SELECT COALESCE(SUM(ii.total_price), 0) AS today_sales
        FROM invoices i
        LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
        LEFT JOIN products p ON p.product_name = ii.product_name COLLATE utf8mb4_unicode_ci
        LEFT JOIN categories c ON c.id = p.category_id 
            AND c.category_name = ii.product_category COLLATE utf8mb4_unicode_ci
        WHERE c.id = {$pipes_category_id}
            AND DATE(i.created_at) = CURDATE()
    ";
    
    if ($product_id > 0 && !empty($product_name)) {
        $today_sql .= " AND p.product_name = '{$product_name}' COLLATE utf8mb4_unicode_ci";
    }
    
    $q = mysqli_query($conn, $today_sql);
    if ($q && ($r = mysqli_fetch_assoc($q))) {
        $summary_stats['today_sales'] = (float)$r['today_sales'];
    }
}

// --- Get current stock quantities for all Pipes products ---
$current_stock = [];
$low_stock_products = [];
if ($pipes_category_id) {
    $stock_sql = "SELECT product_name, quantity FROM products WHERE category_id = {$pipes_category_id}";
    $q = mysqli_query($conn, $stock_sql);
    if ($q) {
        while ($row = mysqli_fetch_assoc($q)) {
            $current_qty = (int)$row['quantity'];
            $current_stock[$row['product_name']] = $current_qty;
            
            // Check if product is low stock (<= 10 units)
            if ($current_qty <= 10) {
                $low_stock_products[] = $row['product_name'];
            }
        }
    }
}

// --- Pipes Product-wise Performance Report ---
$product_report = [];
if ($pipes_category_id) {
    $sql = "
        SELECT 
            p.product_name,
            p.model_name,
            c.category_name,
            COALESCE(SUM(ii.quantity), 0) AS qty_sold,
            COALESCE(SUM(ii.total_price), 0) AS revenue,
            COALESCE(SUM(p.stock_price * ii.quantity), 0) AS investment,
            COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) AS profit,
            CASE 
                WHEN COALESCE(SUM(ii.total_price), 0) > 0 
                THEN ROUND((COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) / COALESCE(SUM(ii.total_price), 0)) * 100, 2)
                ELSE 0 
            END AS margin_percentage,
            CASE 
                WHEN COALESCE(SUM(p.stock_price * ii.quantity), 0) > 0 
                THEN ROUND((COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) / COALESCE(SUM(p.stock_price * ii.quantity), 0)) * 100, 2)
                ELSE 0 
            END AS avg_margin_percentage
        FROM products p
        LEFT JOIN categories c ON c.id = p.category_id
        LEFT JOIN invoice_items ii ON ii.product_name = p.product_name COLLATE utf8mb4_unicode_ci
            AND ii.product_category = c.category_name COLLATE utf8mb4_unicode_ci
        LEFT JOIN invoices i ON i.id = ii.invoice_id
            AND i.created_at BETWEEN '{$start_date} 00:00:00' AND '{$end_date} 23:59:59'
        WHERE c.id = {$pipes_category_id}
    ";
    
    if ($product_id > 0 && !empty($product_name)) {
        $sql .= " AND p.product_name = '{$product_name}' COLLATE utf8mb4_unicode_ci";
    }
    
    $sql .= " GROUP BY p.id, p.product_name, p.model_name, c.category_name
              HAVING qty_sold > 0
              ORDER BY revenue DESC";
    
    $q = mysqli_query($conn, $sql);
    if ($q) {
        while ($row = mysqli_fetch_assoc($q)) {
            $current_qty = $current_stock[$row['product_name']] ?? 0;
            $is_low_stock = $current_qty <= 10;
            
            $product_report[] = [
                'product_name' => $row['product_name'],
                'model_name' => $row['model_name'],
                'category' => $row['category_name'],
                'qty_sold' => (int)$row['qty_sold'],
                'current_qty' => $current_qty,
                'is_low_stock' => $is_low_stock,
                'revenue' => (float)$row['revenue'],
                'investment' => (float)$row['investment'],
                'profit' => (float)$row['profit'],
                'margin_percentage' => (float)$row['margin_percentage'],
                'avg_margin_percentage' => (float)$row['avg_margin_percentage']
            ];
        }
    }
}

// --- Pipes Monthly Performance Report ---
$monthly_report = [];
if ($pipes_category_id) {
    $sql = "
        SELECT 
            DATE_FORMAT(i.created_at, '%Y-%m') AS month,
            COUNT(DISTINCT i.id) AS orders,
            COALESCE(SUM(ii.quantity), 0) AS items_sold,
            COALESCE(SUM(ii.total_price), 0) AS revenue,
            COALESCE(SUM(p.stock_price * ii.quantity), 0) AS investment,
            COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) AS profit,
            CASE 
                WHEN COALESCE(SUM(ii.total_price), 0) > 0 
                THEN ROUND((COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) / COALESCE(SUM(ii.total_price), 0)) * 100, 2)
                ELSE 0 
            END AS margin_percentage,
            CASE 
                WHEN COUNT(DISTINCT i.id) > 0 
                THEN ROUND(COALESCE(SUM(ii.total_price), 0) / COUNT(DISTINCT i.id), 2)
                ELSE 0 
            END AS aov
        FROM invoices i
        LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
        LEFT JOIN products p ON p.product_name = ii.product_name COLLATE utf8mb4_unicode_ci
        LEFT JOIN categories c ON c.id = p.category_id 
            AND c.category_name = ii.product_category COLLATE utf8mb4_unicode_ci
        WHERE {$where_clause}
        GROUP BY DATE_FORMAT(i.created_at, '%Y-%m')
        ORDER BY month DESC
    ";
    
    $q = mysqli_query($conn, $sql);
    if ($q) {
        while ($row = mysqli_fetch_assoc($q)) {
            $monthly_report[] = [
                'month' => $row['month'],
                'orders' => (int)$row['orders'],
                'items_sold' => (int)$row['items_sold'],
                'revenue' => (float)$row['revenue'],
                'investment' => (float)$row['investment'],
                'profit' => (float)$row['profit'],
                'margin_percentage' => (float)$row['margin_percentage'],
                'aov' => (float)$row['aov']
            ];
        }
    }
}

// --- Pipes Customer-wise Performance Report ---
$customer_report = [];
if ($pipes_category_id) {
    $sql = "
        SELECT 
            c.name AS customer_name,
            c.phone,
            COUNT(DISTINCT i.id) AS orders,
            COALESCE(SUM(ii.quantity), 0) AS items,
            COALESCE(SUM(ii.total_price), 0) AS revenue,
            COALESCE(SUM(p.stock_price * ii.quantity), 0) AS investment,
            COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) AS profit,
            CASE 
                WHEN COALESCE(SUM(ii.total_price), 0) > 0 
                THEN ROUND((COALESCE(SUM(ii.total_price - (p.stock_price * ii.quantity)), 0) / COALESCE(SUM(ii.total_price), 0)) * 100, 2)
                ELSE 0 
            END AS margin_percentage,
            MAX(i.created_at) AS last_order
        FROM customers c
        LEFT JOIN invoices i ON i.customer_id = c.id
        LEFT JOIN invoice_items ii ON ii.invoice_id = i.id
        LEFT JOIN products p ON p.product_name = ii.product_name COLLATE utf8mb4_unicode_ci
        LEFT JOIN categories cat ON cat.id = p.category_id 
            AND cat.category_name = ii.product_category COLLATE utf8mb4_unicode_ci
        WHERE {$where_clause}
        GROUP BY c.id, c.name, c.phone
        HAVING revenue > 0
        ORDER BY revenue DESC
    ";
    
    $q = mysqli_query($conn, $sql);
    if ($q) {
        while ($row = mysqli_fetch_assoc($q)) {
            $customer_report[] = [
                'customer_name' => $row['customer_name'],
                'phone' => $row['phone'],
                'orders' => (int)$row['orders'],
                'items' => (int)$row['items'],
                'revenue' => (float)$row['revenue'],
                'investment' => (float)$row['investment'],
                'profit' => (float)$row['profit'],
                'margin_percentage' => (float)$row['margin_percentage'],
                'last_order' => $row['last_order']
            ];
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Pipes Reports - COMMER</title>
    <?php include('includes/head.php'); ?>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        .card-value { font-size: 1.5rem; font-weight: 600; margin-bottom: 0; }
        .card-label { font-size: 0.875rem; color: #fff; margin-bottom: 0.5rem; opacity: 0.9; }
        .stats-icon { font-size: 2rem; opacity: 0.8; }
        .report-table th { background-color: #f8f9fa; font-weight: 600; }
        .date-range-display { 
            background-color: #e9ecef; 
            padding: 8px 15px; 
            border-radius: 5px;
            font-weight: 500;
        }
        .summary-card { transition: transform 0.2s; border-radius: 10px; border: none; color: white; }
        .summary-card:hover { transform: translateY(-3px); box-shadow: 0 4px 15px rgba(0,0,0,0.2); }
        .table-action-buttons { display: flex; gap: 5px; }
        .table-action-buttons .btn { padding: 2px 8px; font-size: 12px; }
        .margin-positive { color: #28a745; }
        .margin-negative { color: #dc3545; }
        .no-data {
            text-align: center;
            padding: 40px;
            color: #6c757d;
        }
        .no-data i {
            font-size: 3rem;
            margin-bottom: 15px;
            opacity: 0.5;
        }
        .period-buttons .btn-period {
            margin: 2px;
            padding: 5px 12px;
            font-size: 0.85rem;
        }
        .period-buttons .btn-period.active {
            background-color: #007bff;
            color: white;
            border-color: #007bff;
        }
        .filter-section {
            background-color: #f8f9fa;
            padding: 15px;
            border-radius: 8px;
            margin-bottom: 20px;
        }
        .low-stock-badge {
            background-color: #dc3545;
            color: white;
            padding: 2px 8px;
            border-radius: 12px;
            font-size: 0.75rem;
            font-weight: 600;
            margin-left: 8px;
            display: inline-flex;
            align-items: center;
        }
        .low-stock-badge i {
            margin-right: 4px;
            font-size: 0.7rem;
        }
        .low-stock-row {
            background-color: #fff3cd !important;
            border-left: 4px solid #ffc107;
        }
        .stock-warning {
            background-color: #f8d7da;
            border: 1px solid #f5c6cb;
            border-radius: 5px;
            padding: 15px;
            margin-bottom: 20px;
            display: flex;
            align-items: flex-start;
            justify-content: space-between;
        }
        .stock-warning i {
            color: #dc3545;
            margin-right: 10px;
            font-size: 1.2rem;
            margin-top: 3px;
        }
        .stock-warning-content {
            flex-grow: 1;
        }
        .stock-warning-title {
            font-weight: 600;
            color: #721c24;
            margin-bottom: 5px;
            display: flex;
            align-items: center;
        }
        .stock-warning-text {
            color: #721c24;
            font-size: 0.9rem;
            margin-bottom: 0;
        }
        .stock-warning-actions {
            display: flex;
            gap: 10px;
            margin-left: 15px;
            flex-shrink: 0;
        }
        .stock-warning-actions .btn {
            white-space: nowrap;
        }
        .current-stock {
            font-size: 0.85rem;
            color: #6c757d;
            margin-top: 4px;
        }
        .stock-low {
            color: #dc3545;
            font-weight: 600;
        }
        .stock-ok {
            color: #28a745;
            font-weight: 600;
        }
        .product-name-cell {
            display: flex;
            flex-direction: column;
        }
        .product-main-name {
            font-weight: 600;
            display: flex;
            align-items: center;
        }
        .floating-stock-action {
            position: fixed;
            bottom: 30px;
            right: 30px;
            z-index: 1000;
        }
        .floating-stock-action .btn {
            padding: 12px 24px;
            font-size: 1rem;
            animation: pulse 2s infinite;
        }
        @keyframes pulse {
            0% {
                box-shadow: 0 0 0 0 rgba(220, 53, 69, 0.7);
            }
            70% {
                box-shadow: 0 0 0 10px rgba(220, 53, 69, 0);
            }
            100% {
                box-shadow: 0 0 0 0 rgba(220, 53, 69, 0);
            }
        }
    </style>
</head>
<body data-sidebar="dark">

<?php include('includes/pre-loader.php'); ?>

<div id="layout-wrapper">

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

<div class="vertical-menu">
        <div data-simplebar class="h-100">
            <?php include('includes/sidebar.php'); ?>
        </div>
    </div>

<div class="main-content">
        <div class="page-content">
            <div class="container-fluid">

<div class="row mb-4">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <h5 class="card-title">Filter Reports(PIPES)</h5>
                                
                                
                                <div class="row mb-3">
                                    <div class="col-12">
                                        <div class="period-buttons">
                                            <button type="button" class="btn btn-sm btn-outline-primary btn-period <?php echo $period == 'today' ? 'active' : ''; ?>" data-period="today">Today</button>
                                            <button type="button" class="btn btn-sm btn-outline-primary btn-period <?php echo $period == 'this_week' ? 'active' : ''; ?>" data-period="this_week">This Week</button>
                                            <button type="button" class="btn btn-sm btn-outline-primary btn-period <?php echo $period == 'this_month' ? 'active' : ''; ?>" data-period="this_month">This Month</button>
                                            <button type="button" class="btn btn-sm btn-outline-primary btn-period <?php echo $period == 'last_6months' ? 'active' : ''; ?>" data-period="last_6months">Last 6 Months</button>
                                            <button type="button" class="btn btn-sm btn-outline-primary btn-period <?php echo $period == 'this_year' ? 'active' : ''; ?>" data-period="this_year">This Year</button>
                                            <button type="button" class="btn btn-sm btn-outline-secondary btn-period <?php echo $period == 'custom' ? 'active' : ''; ?>" data-period="custom">Custom Range</button>
                                        </div>
                                    </div>
                                </div>
                                
                                
                                <form method="GET" class="row g-3">
                                    <div class="col-md-3">
                                        <label for="product_id" class="form-label">Product</label>
                                        <select class="form-select" id="product_id" name="product_id">
                                            <option value="0">All Products</option>
                                            <?php foreach ($pipes_products as $product): ?>
                                            <option value="<?php echo $product['id']; ?>" <?php echo $product_id == $product['id'] ? 'selected' : ''; ?>>
                                                <?php echo h($product['name']); ?>
                                                <?php if ($product['quantity'] <= 10): ?>
                                                 (Low Stock: <?php echo $product['quantity']; ?>)
                                                <?php endif; ?>
                                            </option>
                                            <?php endforeach; ?>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-3">
                                        <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 h($start_date); ?>" max="<?php echo date('Y-m-d'); ?>">
                                    </div>
                                    <div class="col-md-3">
                                        <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 h($end_date); ?>" max="<?php echo date('Y-m-d'); ?>">
                                    </div>
                                    
                                    <div class="col-md-3 d-flex align-items-end">
                                        <button type="submit" class="btn btn-primary w-100">
                                            <i class="fas fa-filter me-1"></i> Apply Filter
                                        </button>
                                    </div>
                                    
                                    
                                    <input type="hidden" name="period" id="period" value="<?php echo h($period); ?>">
                                </form>
                                
                                
                                <div class="row mt-3">
                                    <div class="col-12">
                                        <div class="date-range-display">
                                            <i class="fas fa-calendar-alt me-2"></i>
                                            <?php 
                                            $selected_product = "All Products";
                                            if ($product_id > 0) {
                                                foreach ($pipes_products as $product) {
                                                    if ($product['id'] == $product_id) {
                                                        $selected_product = h($product['name']);
                                                        break;
                                                    }
                                                }
                                            }
                                            echo "Product: <strong>" . $selected_product . "</strong> | Period: " . h($date_range_display);
                                            ?>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mb-4">
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-primary summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($summary_stats['total_quantity']); ?></h4>
                                        <p class="mb-0 card-label">Total Quantity</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-boxes stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-success summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($summary_stats['total_products']); ?></h4>
                                        <p class="mb-0 card-label">Products</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-cube stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-danger summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($summary_stats['low_stock_count']); ?></h4>
                                        <p class="mb-0 card-label">Low Stock Items</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-exclamation-triangle stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-info summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($summary_stats['today_sales'], 2); ?></h4>
                                        <p class="mb-0 card-label">Today's Sales</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-rupee-sign stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row mb-4">
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-warning summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($summary_stats['total_investment'], 2); ?></h4>
                                        <p class="mb-0 card-label">Investment</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-money-bill-wave stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-danger summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($summary_stats['total_profit'], 2); ?></h4>
                                        <p class="mb-0 card-label">Profit</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-chart-line stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-secondary summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($summary_stats['total_revenue'], 2); ?></h4>
                                        <p class="mb-0 card-label">Total Revenue</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-chart-bar stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-6 col-xl-3">
                        <div class="card bg-dark summary-card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo number_format($summary_stats['total_orders']); ?></h4>
                                        <p class="mb-0 card-label">Total Orders</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="fas fa-shopping-cart stats-icon"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<?php if (!empty($low_stock_products)): ?>
                <div class="row mb-4">
                    <div class="col-12">
                        <div class="stock-warning">
                            <i class="fas fa-exclamation-triangle"></i>
                            <div class="stock-warning-content">
                                <div class="stock-warning-title">
                                    <i class="fas fa-exclamation-circle me-1"></i>
                                    Low Stock Alert!
                                </div>
                                <p class="stock-warning-text">
                                    <?php 
                                    $low_stock_count = count($low_stock_products);
                                    if ($low_stock_count == 1) {
                                        echo "Product <strong>" . h($low_stock_products[0]) . "</strong> is running low on stock.";
                                    } else {
                                        echo $low_stock_count . " products are running low on stock: <strong>" . implode(", ", array_map('h', $low_stock_products)) . "</strong>";
                                    }
                                    ?>
                                    Please consider restocking soon.
                                </p>
                            </div>
                            <div class="stock-warning-actions">
                                <a href="stock-management.php" class="btn btn-sm btn-warning">
                                    <i class="fas fa-boxes me-1"></i> Manage Stock
                                </a>
                                <button type="button" class="btn btn-sm btn-outline-secondary" onclick="window.location.href='purchase-orders.php?action=create&category=pipes'">
                                    <i class="fas fa-shopping-cart me-1"></i> Create Purchase Order
                                </button>
                            </div>
                        </div>
                    </div>
                </div>
                <?php endif; ?>

<?php if (!empty($low_stock_products)): ?>
                <div class="floating-stock-action d-none">
                    <button type="button" class="btn btn-danger btn-lg rounded-pill shadow-lg" onclick="window.location.href='stock-management.php'">
                        <i class="fas fa-boxes me-2"></i>
                        Manage Stock
                        <span class="badge bg-white text-danger ms-2"><?php echo count($low_stock_products); ?></span>
                    </button>
                </div>
                <?php endif; ?>

<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-3">
                                    <h4 class="card-title mb-0">Product-wise Performance Report</h4>
                                    <div class="table-action-buttons">
                                        <button class="btn btn-sm btn-outline-secondary" onclick="printTable('product-table')">
                                            <i class="fas fa-print"></i> Print
                                        </button>
                                        <button class="btn btn-sm btn-outline-success" onclick="exportToCSV('product-table', 'pipes-products.csv')">
                                            <i class="fas fa-file-csv"></i> CSV
                                        </button>
                                        <button class="btn btn-sm btn-outline-primary" onclick="copyTable('product-table')">
                                            <i class="fas fa-copy"></i> Copy
                                        </button>
                                    </div>
                                </div>
                                
                                <?php if (!empty($product_report)): ?>
                                <div class="table-responsive">
                                    <table class="table table-hover report-table" id="product-table">
                                        <thead class="table-light">
                                            <tr>
                                                <th>#</th>
                                                <th>Product Name</th>
                                                <th>Current Stock</th>
                                                <th>Model</th>
                                                <th>Category</th>
                                                <th class="text-end">Qty Sold</th>
                                                <th class="text-end">Revenue (₹)</th>
                                                <th class="text-end">Investment (₹)</th>
                                                <th class="text-end">Profit (₹)</th>
                                                <th class="text-end">Margin %</th>
                                                <th class="text-end">Avg Margin %</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php 
                                            $counter = 1;
                                            $total_qty = 0;
                                            $total_revenue = 0;
                                            $total_investment = 0;
                                            $total_profit = 0;
                                            
                                            foreach ($product_report as $product): 
                                                $total_qty += $product['qty_sold'];
                                                $total_revenue += $product['revenue'];
                                                $total_investment += $product['investment'];
                                                $total_profit += $product['profit'];
                                                
                                                $row_class = $product['is_low_stock'] ? 'low-stock-row' : '';
                                                $stock_class = $product['is_low_stock'] ? 'stock-low' : 'stock-ok';
                                            ?>
                                            <tr class="<?php echo $row_class; ?>">
                                                <td><?php echo $counter++; ?></td>
                                                <td>
                                                    <div class="product-name-cell">
                                                        <div class="product-main-name">
                                                            <?php echo h($product['product_name']); ?>
                                                            <?php if ($product['is_low_stock']): ?>
                                                            <span class="low-stock-badge" title="Low Stock - Needs Restocking">
                                                                <i class="fas fa-exclamation-triangle"></i> Low Stock
                                                            </span>
                                                            <?php endif; ?>
                                                        </div>
                                                    </div>
                                                </td>
                                                <td class="<?php echo $stock_class; ?>">
                                                    <?php echo number_format($product['current_qty']); ?>
                                                    <?php if ($product['is_low_stock']): ?>
                                                    <div class="current-stock">
                                                        <i class="fas fa-exclamation-circle me-1"></i>
                                                        Needs restocking
                                                    </div>
                                                    <?php endif; ?>
                                                </td>
                                                <td><?php echo h($product['model_name'] ?: '-'); ?></td>
                                                <td><?php echo h($product['category']); ?></td>
                                                <td class="text-end"><?php echo number_format($product['qty_sold']); ?></td>
                                                <td class="text-end">₹<?php echo number_format($product['revenue'], 2); ?></td>
                                                <td class="text-end">₹<?php echo number_format($product['investment'], 2); ?></td>
                                                <td class="text-end <?php echo $product['profit'] >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    ₹<?php echo number_format($product['profit'], 2); ?>
                                                </td>
                                                <td class="text-end <?php echo $product['margin_percentage'] >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    <?php echo number_format($product['margin_percentage'], 2); ?>%
                                                </td>
                                                <td class="text-end <?php echo $product['avg_margin_percentage'] >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    <?php echo number_format($product['avg_margin_percentage'], 2); ?>%
                                                </td>
                                            </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                        <tfoot>
                                            <tr class="table-active">
                                                <th colspan="5" class="text-end">Total:</th>
                                                <th class="text-end"><?php echo number_format($total_qty); ?></th>
                                                <th class="text-end">₹<?php echo number_format($total_revenue, 2); ?></th>
                                                <th class="text-end">₹<?php echo number_format($total_investment, 2); ?></th>
                                                <th class="text-end <?php echo $total_profit >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    ₹<?php echo number_format($total_profit, 2); ?>
                                                </th>
                                                <th class="text-end">
                                                    <?php echo $total_revenue > 0 ? number_format(($total_profit / $total_revenue) * 100, 2) : '0.00'; ?>%
                                                </th>
                                                <th class="text-end">
                                                    <?php echo $total_investment > 0 ? number_format(($total_profit / $total_investment) * 100, 2) : '0.00'; ?>%
                                                </th>
                                            </tr>
                                        </tfoot>
                                    </table>
                                </div>
                                <div class="mt-2 text-muted">
                                    Showing <?php echo count($product_report); ?> entries
                                    <?php if (!empty($low_stock_products)): ?>
                                    <span class="text-danger ms-3">
                                        <i class="fas fa-exclamation-triangle"></i>
                                        <?php echo count($low_stock_products); ?> product(s) with low stock
                                    </span>
                                    <?php endif; ?>
                                </div>
                                <?php else: ?>
                                <div class="no-data">
                                    <i class="fas fa-box-open"></i>
                                    <h5>No Pipes product sales in selected period</h5>
                                    <p>Try selecting a different date range or product</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-3">
                                    <h4 class="card-title mb-0">Month-wise Performance Report</h4>
                                    <div class="table-action-buttons">
                                        <button class="btn btn-sm btn-outline-secondary" onclick="printTable('monthly-table')">
                                            <i class="fas fa-print"></i> Print
                                        </button>
                                        <button class="btn btn-sm btn-outline-success" onclick="exportToCSV('monthly-table', 'pipes-monthly.csv')">
                                            <i class="fas fa-file-csv"></i> CSV
                                        </button>
                                        <button class="btn btn-sm btn-outline-primary" onclick="copyTable('monthly-table')">
                                            <i class="fas fa-copy"></i> Copy
                                        </button>
                                    </div>
                                </div>
                                
                                <?php if (!empty($monthly_report)): ?>
                                <div class="table-responsive">
                                    <table class="table table-hover report-table" id="monthly-table">
                                        <thead class="table-light">
                                            <tr>
                                                <th>Month</th>
                                                <th class="text-end">Orders</th>
                                                <th class="text-end">Items Sold</th>
                                                <th class="text-end">Revenue (₹)</th>
                                                <th class="text-end">Investment (₹)</th>
                                                <th class="text-end">Profit (₹)</th>
                                                <th class="text-end">Margin %</th>
                                                <th class="text-end">AOV (₹)</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php 
                                            $total_orders = 0;
                                            $total_items = 0;
                                            $total_revenue_monthly = 0;
                                            $total_investment_monthly = 0;
                                            $total_profit_monthly = 0;
                                            
                                            foreach ($monthly_report as $monthly): 
                                                $total_orders += $monthly['orders'];
                                                $total_items += $monthly['items_sold'];
                                                $total_revenue_monthly += $monthly['revenue'];
                                                $total_investment_monthly += $monthly['investment'];
                                                $total_profit_monthly += $monthly['profit'];
                                            ?>
                                            <tr>
                                                <td><strong><?php echo date('M Y', strtotime($monthly['month'] . '-01')); ?></strong></td>
                                                <td class="text-end"><?php echo number_format($monthly['orders']); ?></td>
                                                <td class="text-end"><?php echo number_format($monthly['items_sold']); ?></td>
                                                <td class="text-end">₹<?php echo number_format($monthly['revenue'], 2); ?></td>
                                                <td class="text-end">₹<?php echo number_format($monthly['investment'], 2); ?></td>
                                                <td class="text-end <?php echo $monthly['profit'] >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    ₹<?php echo number_format($monthly['profit'], 2); ?>
                                                </td>
                                                <td class="text-end <?php echo $monthly['margin_percentage'] >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    <?php echo number_format($monthly['margin_percentage'], 2); ?>%
                                                </td>
                                                <td class="text-end">₹<?php echo number_format($monthly['aov'], 2); ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                        <tfoot>
                                            <tr class="table-active">
                                                <th class="text-end">Total:</th>
                                                <th class="text-end"><?php echo number_format($total_orders); ?></th>
                                                <th class="text-end"><?php echo number_format($total_items); ?></th>
                                                <th class="text-end">₹<?php echo number_format($total_revenue_monthly, 2); ?></th>
                                                <th class="text-end">₹<?php echo number_format($total_investment_monthly, 2); ?></th>
                                                <th class="text-end <?php echo $total_profit_monthly >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    ₹<?php echo number_format($total_profit_monthly, 2); ?>
                                                </th>
                                                <th class="text-end">
                                                    <?php echo $total_revenue_monthly > 0 ? number_format(($total_profit_monthly / $total_revenue_monthly) * 100, 2) : '0.00'; ?>%
                                                </th>
                                                <th class="text-end">
                                                    <?php echo $total_orders > 0 ? '₹' . number_format($total_revenue_monthly / $total_orders, 2) : '₹0.00'; ?>
                                                </th>
                                            </tr>
                                        </tfoot>
                                    </table>
                                </div>
                                <div class="mt-2 text-muted">
                                    Showing <?php echo count($monthly_report); ?> entries
                                </div>
                                <?php else: ?>
                                <div class="no-data">
                                    <i class="fas fa-calendar-alt"></i>
                                    <h5>No monthly Pipes sales data</h5>
                                    <p>Try selecting a different date range or product</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-3">
                                    <h4 class="card-title mb-0">Customer-wise Performance Report</h4>
                                    <div class="table-action-buttons">
                                        <button class="btn btn-sm btn-outline-secondary" onclick="printTable('customer-table')">
                                            <i class="fas fa-print"></i> Print
                                        </button>
                                        <button class="btn btn-sm btn-outline-success" onclick="exportToCSV('customer-table', 'pipes-customers.csv')">
                                            <i class="fas fa-file-csv"></i> CSV
                                        </button>
                                        <button class="btn btn-sm btn-outline-primary" onclick="copyTable('customer-table')">
                                            <i class="fas fa-copy"></i> Copy
                                        </button>
                                    </div>
                                </div>
                                
                                <?php if (!empty($customer_report)): ?>
                                <div class="table-responsive">
                                    <table class="table table-hover report-table" id="customer-table">
                                        <thead class="table-light">
                                            <tr>
                                                <th>#</th>
                                                <th>Customer Name</th>
                                                <th>Phone</th>
                                                <th class="text-end">Orders</th>
                                                <th class="text-end">Items</th>
                                                <th class="text-end">Revenue (₹)</th>
                                                <th class="text-end">Investment (₹)</th>
                                                <th class="text-end">Profit (₹)</th>
                                                <th class="text-end">Margin %</th>
                                                <th>Last Order</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php 
                                            $counter = 1;
                                            $total_cust_orders = 0;
                                            $total_cust_items = 0;
                                            $total_cust_revenue = 0;
                                            $total_cust_investment = 0;
                                            $total_cust_profit = 0;
                                            
                                            foreach ($customer_report as $customer): 
                                                $total_cust_orders += $customer['orders'];
                                                $total_cust_items += $customer['items'];
                                                $total_cust_revenue += $customer['revenue'];
                                                $total_cust_investment += $customer['investment'];
                                                $total_cust_profit += $customer['profit'];
                                            ?>
                                            <tr>
                                                <td><?php echo $counter++; ?></td>
                                                <td><strong><?php echo h($customer['customer_name']); ?></strong></td>
                                                <td><?php echo h($customer['phone'] ?: '—'); ?></td>
                                                <td class="text-end"><?php echo number_format($customer['orders']); ?></td>
                                                <td class="text-end"><?php echo number_format($customer['items']); ?></td>
                                                <td class="text-end">₹<?php echo number_format($customer['revenue'], 2); ?></td>
                                                <td class="text-end">₹<?php echo number_format($customer['investment'], 2); ?></td>
                                                <td class="text-end <?php echo $customer['profit'] >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    ₹<?php echo number_format($customer['profit'], 2); ?>
                                                </td>
                                                <td class="text-end <?php echo $customer['margin_percentage'] >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    <?php echo number_format($customer['margin_percentage'], 2); ?>%
                                                </td>
                                                <td><?php echo $customer['last_order'] ? utcToIst($customer['last_order'], 'M d, Y') : '-'; ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                        <tfoot>
                                            <tr class="table-active">
                                                <th colspan="3" class="text-end">Total:</th>
                                                <th class="text-end"><?php echo number_format($total_cust_orders); ?></th>
                                                <th class="text-end"><?php echo number_format($total_cust_items); ?></th>
                                                <th class="text-end">₹<?php echo number_format($total_cust_revenue, 2); ?></th>
                                                <th class="text-end">₹<?php echo number_format($total_cust_investment, 2); ?></th>
                                                <th class="text-end <?php echo $total_cust_profit >= 0 ? 'margin-positive' : 'margin-negative'; ?>">
                                                    ₹<?php echo number_format($total_cust_profit, 2); ?>
                                                </th>
                                                <th class="text-end">
                                                    <?php echo $total_cust_revenue > 0 ? number_format(($total_cust_profit / $total_cust_revenue) * 100, 2) : '0.00'; ?>%
                                                </th>
                                                <th></th>
                                            </tr>
                                        </tfoot>
                                    </table>
                                </div>
                                <div class="mt-2 text-muted">
                                    Showing <?php echo count($customer_report); ?> entries
                                </div>
                                <?php else: ?>
                                <div class="no-data">
                                    <i class="fas fa-users"></i>
                                    <h5>No customer Pipes purchase data</h5>
                                    <p>Try selecting a different date range or product</p>
                                </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

</div>
        </div>

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

</div>

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

// Period buttons functionality
document.addEventListener('DOMContentLoaded', function() {
    const today = new Date().toISOString().split('T')[0];
    
    // Set max date for both inputs
    document.getElementById('start_date').max = today;
    document.getElementById('end_date').max = today;
    
    // Auto-update end date if start date is after end date
    document.getElementById('start_date').addEventListener('change', function() {
        const endDate = document.getElementById('end_date');
        if (this.value > endDate.value) {
            endDate.value = this.value;
        }
    });
    
    // Auto-update start date if end date is before start date
    document.getElementById('end_date').addEventListener('change', function() {
        const startDate = document.getElementById('start_date');
        if (this.value < startDate.value) {
            startDate.value = this.value;
        }
    });
    
    // Period buttons
    const periodButtons = document.querySelectorAll('.btn-period');
    const periodInput = document.getElementById('period');
    
    periodButtons.forEach(button => {
        button.addEventListener('click', function() {
            const period = this.getAttribute('data-period');
            
            // Update active button
            periodButtons.forEach(btn => {
                btn.classList.remove('active');
                if (btn.getAttribute('data-period') === 'custom') {
                    btn.classList.remove('btn-primary');
                    btn.classList.add('btn-outline-secondary');
                } else {
                    btn.classList.remove('btn-primary');
                    btn.classList.add('btn-outline-primary');
                }
            });
            
            this.classList.add('active');
            if (period === 'custom') {
                this.classList.remove('btn-outline-secondary');
                this.classList.add('btn-primary');
            } else {
                this.classList.remove('btn-outline-primary');
                this.classList.add('btn-primary');
            }
            
            // Update hidden input
            periodInput.value = period;
            
            // If not custom, submit form immediately
            if (period !== 'custom') {
                document.querySelector('form').submit();
            }
        });
    });
    
    // Enable/disable date inputs based on period selection
    function updateDateInputs() {
        const isCustom = periodInput.value === 'custom';
        document.getElementById('start_date').disabled = !isCustom;
        document.getElementById('end_date').disabled = !isCustom;
    }
    
    updateDateInputs();
    
    // Watch for period changes
    periodInput.addEventListener('change', updateDateInputs);
    
    // Floating action button for low stock
    const floatingBtn = document.querySelector('.floating-stock-action');
    if (floatingBtn) {
        // Show button after page load
        setTimeout(() => {
            floatingBtn.classList.remove('d-none');
        }, 1000);
        
        // Show/hide based on scroll
        window.addEventListener('scroll', function() {
            if (window.scrollY > 300) {
                floatingBtn.classList.remove('d-none');
            } else {
                floatingBtn.classList.add('d-none');
            }
        });
    }
});
</script>

</body>
</html>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: pipes-reports.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation