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

Free Space:

10.28 TB

🔧 Quick Actions

✏️ Editing: sales_report.php

<?php
// sales_report.php
session_start();
require_once 'config.php'; // provides $conn

// Optional: include head with Bootstrap + DataTables + Chart.js
// If you don't have head.php, keep the CDN tags in <head> below.
include_once 'includes/head.php';

// -------- Auth (both admin roles can view) ----------
$admin_id = $_SESSION['admin_id'] ?? null;
if (!$admin_id) {
    header('Location: index.php'); exit;
}

// -------- Helpers ----------
function valid_ymd($s) { return is_string($s) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $s); }
function esc($s) { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

// -------- Date range (defaults: last 30 days) ----------
$default_from = date('Y-m-d', strtotime('-30 days'));
$default_to   = date('Y-m-d');
$from = isset($_GET['from']) && valid_ymd($_GET['from']) ? $_GET['from'] : $default_from;
$to   = isset($_GET['to'])   && valid_ymd($_GET['to'])   ? $_GET['to']   : $default_to;

// Normalize end to 23:59:59
$from_dt = $from . ' 00:00:00';
$to_dt   = $to . ' 23:59:59';

// -------- CSV Export ----------
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    $stmt = mysqli_prepare($conn, "
        SELECT o.id, o.created_at, o.status, o.payment_method, o.subtotal, o.savings, o.delivery_cost, o.total,
               u.first_name, u.last_name, u.email, o.phone_number, o.district, o.pincode
        FROM orders o
        JOIN users u ON u.id = o.user_id
        WHERE o.created_at BETWEEN ? AND ?
        ORDER BY o.created_at DESC
    ");
    mysqli_stmt_bind_param($stmt, 'ss', $from_dt, $to_dt);
    mysqli_stmt_execute($stmt);
    $rs = mysqli_stmt_get_result($stmt);

header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=sales_orders_' . $from . '_to_' . $to . '.csv');

$out = fopen('php://output', 'w');
    fputcsv($out, ['OrderID','Date','Status','Payment','Subtotal','Savings','Delivery','Total','FirstName','LastName','Email','Phone','District','Pincode']);
    while ($row = mysqli_fetch_assoc($rs)) {
        fputcsv($out, [
            $row['id'], $row['created_at'], $row['status'], $row['payment_method'],
            $row['subtotal'], $row['savings'], $row['delivery_cost'], $row['total'],
            $row['first_name'], $row['last_name'], $row['email'], $row['phone_number'],
            $row['district'], $row['pincode']
        ]);
    }
    fclose($out);
    exit;
}

// -------- KPIs ----------
$kpis = [
    'orders' => 0,
    'revenue' => 0.0,
    'aov' => 0.0,
    'cancelled' => 0
];

$stmt = mysqli_prepare($conn, "
    SELECT COUNT(*)        AS orders_count,
           COALESCE(SUM(CASE WHEN status <> 'cancelled' THEN total END),0) AS revenue_sum,
           COALESCE(SUM(CASE WHEN status = 'cancelled' THEN 1 END),0)     AS cancelled_count
    FROM orders
    WHERE created_at BETWEEN ? AND ?
");
mysqli_stmt_bind_param($stmt, 'ss', $from_dt, $to_dt);
mysqli_stmt_execute($stmt);
$rs = mysqli_stmt_get_result($stmt);
if ($row = mysqli_fetch_assoc($rs)) {
    $kpis['orders']    = (int)$row['orders_count'];
    $kpis['revenue']   = (float)$row['revenue_sum'];
    $kpis['cancelled'] = (int)$row['cancelled_count'];
}
$kpis['aov'] = $kpis['orders'] > 0 ? ($kpis['revenue'] / $kpis['orders']) : 0;

// -------- Status breakdown ----------
$status_counts = ['pending'=>0,'processing'=>0,'shipped'=>0,'delivered'=>0,'cancelled'=>0];
$stmt = mysqli_prepare($conn, "
    SELECT status, COUNT(*) c
    FROM orders
    WHERE created_at BETWEEN ? AND ?
    GROUP BY status
");
mysqli_stmt_bind_param($stmt, 'ss', $from_dt, $to_dt);
mysqli_stmt_execute($stmt);
$rs = mysqli_stmt_get_result($stmt);
while ($r = mysqli_fetch_assoc($rs)) {
    $status = $r['status'];
    if (isset($status_counts[$status])) $status_counts[$status] = (int)$r['c'];
}

// -------- Revenue by day (exclude cancelled) ----------
$rev_by_day = []; // [ 'YYYY-MM-DD' => revenue ]
$stmt = mysqli_prepare($conn, "
    SELECT DATE(created_at) d, COALESCE(SUM(total),0) rev
    FROM orders
    WHERE created_at BETWEEN ? AND ?
      AND status <> 'cancelled'
    GROUP BY DATE(created_at)
    ORDER BY DATE(created_at)
");
mysqli_stmt_bind_param($stmt, 'ss', $from_dt, $to_dt);
mysqli_stmt_execute($stmt);
$rs = mysqli_stmt_get_result($stmt);
while ($r = mysqli_fetch_assoc($rs)) {
    $rev_by_day[$r['d']] = (float)$r['rev'];
}

// Fill missing dates with 0 for smoother chart
$labels = [];
$values = [];
$cursor = new DateTime($from);
$end    = new DateTime($to);
for (; $cursor <= $end; $cursor->modify('+1 day')) {
    $d = $cursor->format('Y-m-d');
    $labels[] = $d;
    $values[] = isset($rev_by_day[$d]) ? $rev_by_day[$d] : 0;
}

// -------- Top products (qty & revenue) ----------
$top_products = []; // each row: name, qty, revenue
$stmt = mysqli_prepare($conn, "
    SELECT p.product_name,
           SUM(oi.quantity) AS qty_sold,
           SUM(oi.item_total) AS revenue
    FROM order_items oi
    JOIN orders o   ON o.id = oi.order_id
    JOIN products p ON p.id = oi.product_id
    WHERE o.created_at BETWEEN ? AND ?
      AND o.status <> 'cancelled'
    GROUP BY p.product_name
    ORDER BY qty_sold DESC, revenue DESC
    LIMIT 100
");
mysqli_stmt_bind_param($stmt, 'ss', $from_dt, $to_dt);
mysqli_stmt_execute($stmt);
$rs = mysqli_stmt_get_result($stmt);
while ($r = mysqli_fetch_assoc($rs)) {
    $top_products[] = $r;
}

// -------- Orders list (for table) ----------
$orders = [];
$stmt = mysqli_prepare($conn, "
    SELECT o.id, o.created_at, o.status, o.payment_method, o.total,
           u.first_name, u.last_name, u.email
    FROM orders o
    JOIN users u ON u.id = o.user_id
    WHERE o.created_at BETWEEN ? AND ?
    ORDER BY o.created_at DESC
");
mysqli_stmt_bind_param($stmt, 'ss', $from_dt, $to_dt);
mysqli_stmt_execute($stmt);
$rs = mysqli_stmt_get_result($stmt);
while ($r = mysqli_fetch_assoc($rs)) { $orders[] = $r; }

?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Sales Report</title>

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.jsdelivr.net/npm/datatables.net-bs5@1.13.10/css/dataTables.bootstrap5.min.css" rel="stylesheet">

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

<div class="d-flex align-items-center justify-content-between mb-3">
    <h4 class="mb-0">Sales Report</h4>
    <form class="d-flex gap-2" method="get" action="">
      <div>
        <label class="small-label">From</label>
        <input type="date" name="from" value="<?= esc($from) ?>" class="form-control form-control-sm" required>
      </div>
      <div>
        <label class="small-label">To</label>
        <input type="date" name="to" value="<?= esc($to) ?>" class="form-control form-control-sm" required>
      </div>
      <div class="d-flex align-items-end gap-2">
        <button class="btn btn-primary btn-sm" type="submit">Apply</button>
        <a class="btn btn-outline-secondary btn-sm" href="sales_report.php">Reset</a>
        <a class="btn btn-success btn-sm" href="sales_report.php?from=<?= esc($from) ?>&to=<?= esc($to) ?>&export=csv">Export CSV</a>
      </div>
    </form>
  </div>

<div class="row g-3 mb-4">
    <div class="col-md-3">
      <div class="p-3 bg-white kpi-card">
        <div class="small-label">Total Orders</div>
        <div class="fs-3 fw-semibold"><?= esc(number_format($kpis['orders'])) ?></div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="p-3 bg-white kpi-card">
        <div class="small-label">Gross Revenue (₹)</div>
        <div class="fs-3 fw-semibold">₹<?= esc(number_format($kpis['revenue'],2)) ?></div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="p-3 bg-white kpi-card">
        <div class="small-label">Avg. Order Value (₹)</div>
        <div class="fs-3 fw-semibold">₹<?= esc(number_format($kpis['aov'],2)) ?></div>
      </div>
    </div>
    <div class="col-md-3">
      <div class="p-3 bg-white kpi-card">
        <div class="small-label">Cancelled Orders</div>
        <div class="fs-3 fw-semibold"><?= esc(number_format($kpis['cancelled'])) ?></div>
      </div>
    </div>
  </div>

<div class="row g-3 mb-4">
    <div class="col-lg-8">
      <div class="card"><div class="card-body">
        <h5 class="card-title">Revenue by Day</h5>
        <canvas id="revLine"></canvas>
      </div></div>
    </div>
    <div class="col-lg-4">
      <div class="card"><div class="card-body">
        <h5 class="card-title">Orders by Status</h5>
        <canvas id="statusPie"></canvas>
      </div></div>
    </div>
  </div>

<div class="card mb-4">
    <div class="card-body">
      <div class="d-flex justify-content-between align-items-center mb-2">
        <h5 class="card-title mb-0">Top Products (Qty & Revenue)</h5>
      </div>
      <div class="table-responsive">
        <table id="top-products" class="table table-striped align-middle">
          <thead><tr><th>#</th><th>Product</th><th>Qty Sold</th><th>Revenue (₹)</th></tr></thead>
          <tbody>
            <?php if (count($top_products) === 0): ?>
              <tr><td colspan="4" class="text-center">No product sales in this period</td></tr>
            <?php else: $i=1; foreach ($top_products as $tp): ?>
              <tr>
                <td><?= $i++ ?></td>
                <td><?= esc($tp['product_name']) ?></td>
                <td><?= esc((int)$tp['qty_sold']) ?></td>
                <td>₹<?= esc(number_format((float)$tp['revenue'],2)) ?></td>
              </tr>
            <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

<div class="card">
    <div class="card-body">
      <h5 class="card-title">Orders (<?= esc($from) ?> → <?= esc($to) ?>)</h5>
      <div class="table-responsive">
        <table id="orders-table" class="table table-bordered table-striped align-middle">
          <thead>
            <tr>
              <th>#</th>
              <th>Date</th>
              <th>Customer</th>
              <th>Email</th>
              <th>Status</th>
              <th>Payment</th>
              <th>Total (₹)</th>
            </tr>
          </thead>
          <tbody>
            <?php if (count($orders) === 0): ?>
              <tr><td colspan="7" class="text-center">No orders in this period</td></tr>
            <?php else: foreach ($orders as $o): ?>
              <tr>
                <td><strong>#<?= esc($o['id']) ?></strong></td>
                <td><?= esc(date('M j, Y h:i A', strtotime($o['created_at']))) ?></td>
                <td><?= esc(trim(($o['first_name'] ?? '').' '.($o['last_name'] ?? ''))) ?></td>
                <td><?= esc($o['email']) ?></td>
                <td><?= esc($o['status']) ?></td>
                <td><?= esc($o['payment_method'] ?? '-') ?></td>
                <td>₹<?= esc(number_format((float)$o['total'],2)) ?></td>
              </tr>
            <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

</div>
</div>

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

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

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.7.1/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/datatables.net@1.13.10/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/datatables.net-bs5@1.13.10/js/dataTables.bootstrap5.min.js"></script>

// Revenue Line
new Chart(document.getElementById('revLine'), {
  type: 'line',
  data: {
    labels: labels,
    datasets: [{
      label: 'Revenue (₹)',
      data: values,
      tension: .25,
      fill: false
    }]
  },
  options: {
    responsive: true,
    plugins: { legend: { display: true } },
    scales: { y: { beginAtZero: true } }
  }
});

// Status Pie
new Chart(document.getElementById('statusPie'), {
  type: 'pie',
  data: {
    labels: statusLabels,
    datasets: [{ data: statusCounts }]
  },
  options: { responsive: true }
});

// Tables
$(function(){
  $('#orders-table').DataTable({ order:[[1,'desc']], pageLength:25, autoWidth:false });
  $('#top-products').DataTable({ order:[[2,'desc']], pageLength:10, searching:false, lengthChange:false });
});
</script>
</body>
</html>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: sales_report.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation