bnmbnmbnhjkhjkhjkhjktyutyutyuytutyutyutyhgjgjghjgjghjguytutyry

halohalo bnmbnmbnhjkhjkhjkhjktyutyutyuytutyutyutyhgjgjghjgjghjguytutyry

<?php
session_start();
if (!isset($_SESSION['user_id'])) {
  header("Location: login.php");
  exit();
}
include('inc/header.php');
include 'config.php';

// -------------------- Fetch Metrics --------------------

// Total Sales
$sales_total = 0;
$stmt = $conn->prepare("SELECT SUM(total_amount) as total_sales FROM orders");
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
  $sales_total = $row['total_sales'] ?? 0;
}

// Total Customers
$total_customers = 0;
$stmt = $conn->prepare("SELECT COUNT(*) as total_customers FROM users");
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
  $total_customers = $row['total_customers'] ?? 0;
}

// Total Products
$total_products = 0;
$stmt = $conn->prepare("SELECT COUNT(*) as total_products FROM products");
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
  $total_products = $row['total_products'] ?? 0;
}

// Total Product Items Sold (sum of quantity from order_items)
$total_quantity = 0;
$stmt = $conn->prepare("SELECT SUM(qty) as total_quantity FROM order_items");
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
  $total_quantity = $row['total_quantity'] ?? 0;
}

// Total Revenue (completed orders)
$total_revenue = 0;
$stmt = $conn->prepare("SELECT SUM(total_amount) as revenue FROM orders WHERE order_status='completed'");
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
  $total_revenue = $row['revenue'] ?? 0;
}

// Profit Last Month (sum of total_amount for last month)
$profit_last_month = 0;
$stmt = $conn->prepare("
    SELECT SUM(total_amount) as profit 
    FROM orders 
    WHERE MONTH(created_at) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
      AND YEAR(created_at) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
");
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
  $profit_last_month = $row['profit'] ?? 0;
}

// Expenses Last Month (not tracked in your tables)
$expenses_last_month = 0;

// Generated Leads (from contact table for current month)
$generated_leads = 0;
$stmt = $conn->prepare("SELECT COUNT(*) as total_leads FROM contact WHERE MONTH(created_at) = MONTH(CURRENT_DATE) AND YEAR(created_at) = YEAR(CURRENT_DATE)");
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
  $generated_leads = $row['total_leads'] ?? 0;
}

// -------------------- Fetch Orders --------------------
$orders = [];
try {
  $stmt = $conn->prepare("
        SELECT o.id, COALESCE(u.name, o.name) AS username, o.order_number, o.order_status, o.total_amount, o.payment_status, o.created_at 
        FROM orders o 
        LEFT JOIN users u ON o.user_id = u.id 
        ORDER BY o.created_at DESC
    ");
  $stmt->execute();
  $result = $stmt->get_result();
  $orders = $result->fetch_all(MYSQLI_ASSOC);
} catch (Exception $e) {
  $error_message = "Error fetching orders: " . $e->getMessage();
}

// Function to fetch order items
function getOrderItems($conn, $order_id)
{
  $stmt = $conn->prepare("
        SELECT oi.qty, oi.subtotal, p.title, p.price
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        WHERE oi.order_id = ?
    ");
  $stmt->bind_param("i", $order_id);
  $stmt->execute();
  $result = $stmt->get_result();
  return $result->fetch_all(MYSQLI_ASSOC);
}
?>

<div class="content-wrapper">
  <div class="container-xxl flex-grow-1 container-p-y">

<div class="row g-6">
      <div class="col-xl-3 col-sm-6">
        <div class="card text-center">
          <div class="card-body">
            <h5 class="card-title mb-1">Total Product Items Sold</h5>
            <h3 class="text-primary"><?php echo number_format($total_quantity); ?></h3>
          </div>
        </div>
      </div>
      <div class="col-xl-3 col-sm-6">
        <div class="card text-center">
          <div class="card-body">
            <h5 class="card-title mb-1">Customers</h5>
            <h3 class="text-info"><?php echo number_format($total_customers); ?></h3>
          </div>
        </div>
      </div>
      <div class="col-xl-3 col-sm-6">
        <div class="card text-center">
          <div class="card-body">
            <h5 class="card-title mb-1">Total Products</h5>
            <h3 class="text-danger"><?php echo number_format($total_products); ?></h3>
          </div>
        </div>
      </div>
      <div class="col-xl-3 col-sm-6">
        <div class="card text-center">
          <div class="card-body">
            <h5 class="card-title mb-1">Revenue</h5>
            <h3 class="text-success">₹<?php echo number_format($sales_total, 2); ?></h3>
          </div>
        </div>
      </div>
    </div>

<div class="row g-6 mt-4">
      <div class="col-xl-4 col-sm-6">
        <div class="card text-center">
          <div class="card-body">
            <h5 class="card-title mb-1">Profit Last Month</h5>
            <h4>₹ <?php echo number_format($profit_last_month, 2); ?></h4>
          </div>
        </div>
      </div>
      <div class="col-xl-4 col-sm-6">
        <div class="card text-center">
          <div class="card-body">
            <h5 class="card-title mb-1">Expenses Last Month</h5>
            <h4>$<?php echo number_format($expenses_last_month, 2); ?></h4>
          </div>
        </div>
      </div>
      <div class="col-xl-4 col-sm-6">
        <div class="card text-center">
          <div class="card-body">
            <h5 class="card-title mb-1">Generated Leads (This Month)</h5>
            <h4><?php echo number_format($generated_leads); ?></h4>
          </div>
        </div>
      </div>
    </div>

<div class="col-xxl-12  mt-4">
      <div class="card">
        <div class="card-datatable table-responsive">
          <table class="table table-sm border-top" id="ordersTable">
            <thead>
              <tr>
                <th>#</th>
                <th>User</th>
                <th>Order Number</th>
                <th>Status</th>
                <th>Total</th>
                <th>Issued Date</th>
                <th>Payment Status</th>
                <th>Action</th>
              </tr>
            </thead>
            <tbody>
              <?php foreach ($orders as $index => $order): ?>
                <tr>
                  <td><?php echo $index + 1; ?></td>
                  <td><?php echo htmlspecialchars($order['username'] ?? 'Guest'); ?></td>
                  <td><?php echo htmlspecialchars($order['order_number']); ?></td>
                  <td><?php echo htmlspecialchars($order['order_status']); ?></td>
                  <td>$<?php echo htmlspecialchars($order['total_amount']); ?></td>
                  <td><?php echo htmlspecialchars($order['created_at']); ?></td>
                  <td><?php echo htmlspecialchars($order['payment_status']); ?></td>
                  <td>
                    <a href="invoice.php?order_id=<?php echo $order['id']; ?>" class="btn btn-sm btn-info">View</a>
                  </td>
                </tr>
              <?php endforeach; ?>
            </tbody>
          </table>
          <?php if (empty($orders) && isset($error_message)): ?>
            <p class="text-danger"><?php echo $error_message; ?></p>
          <?php endif; ?>
        </div>
      </div>
    </div>

</div>
</div>

<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>

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