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/vsenthur

Free Space:

10.24 TB

🔧 Quick Actions

✏️ Editing: oc_report.php

<?php
// opening_closing_reports.php
session_start();
require_once 'config/db_config.php';

// ---- Handle CSV export ----
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    try {
        // Clear any output buffers
        while (ob_get_level()) {
            ob_end_clean();
        }
        
        // ---- Determine filters ----
        $selected_branch_id = $_SESSION['role'] === 'branch' ? $_SESSION['branch_id'] : ($_GET['branch_id'] ?? 'all');
        $date_filter = $_GET['date_filter'] ?? 'today';
        $start_date = $_GET['start_date'] ?? date('Y-m-d');
        $end_date = $_GET['end_date'] ?? date('Y-m-d');

// Set date range based on filter
        switch($date_filter) {
            case 'today':
                $start_date = date('Y-m-d');
                $end_date = date('Y-m-d');
                break;
            case 'yesterday':
                $start_date = date('Y-m-d', strtotime('-1 day'));
                $end_date = date('Y-m-d', strtotime('-1 day'));
                break;
            case 'custom':
                // Use provided dates
                break;
        }

$where_clause = [];
        $params = [];

if ($selected_branch_id !== 'all' && is_numeric($selected_branch_id)) {
            $where_clause[] = 'e.branch_id = :branch_id';
            $params['branch_id'] = $selected_branch_id;
        }

// For branch users, only show their branch
        if ($_SESSION['role'] === 'branch') {
            $where_clause[] = 'e.branch_id = :user_branch_id';
            $params['user_branch_id'] = $_SESSION['branch_id'];
        }

// Date range filter
        $where_clause[] = 'DATE(e.expense_date) BETWEEN :start_date AND :end_date';
        $params['start_date'] = $start_date;
        $params['end_date'] = $end_date;

$where_sql = !empty($where_clause) ? 'WHERE ' . implode(' AND ', $where_clause) : '';

// Get expenses for the date range
        $query = "
            SELECT 
                e.expense_id,
                e.expense_date,
                e.expense_title,
                e.amount,
                e.payment_method,
                e.reference_no,
                e.description,
                b.branch_name,
                creator.full_name as created_by_name,
                e.created_at
            FROM expenses e
            JOIN branches b ON e.branch_id = b.branch_id
            LEFT JOIN users creator ON e.added_by = creator.user_id
            {$where_sql}
            ORDER BY e.expense_date DESC, e.created_at ASC
        ";
        
        $stmt = $pdo->prepare($query);
        
        // Bind parameters
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        
        $stmt->execute();
        $expenses = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get sales data for the date range
        $sales_where = [];
        $sales_params = [];

if ($selected_branch_id !== 'all' && is_numeric($selected_branch_id)) {
            $sales_where[] = 'branch_id = :branch_id';
            $sales_params['branch_id'] = $selected_branch_id;
        } elseif ($_SESSION['role'] === 'branch') {
            $sales_where[] = 'branch_id = :branch_id';
            $sales_params['branch_id'] = $_SESSION['branch_id'];
        }

$sales_where[] = 'DATE(order_date) BETWEEN :start_date AND :end_date';
        $sales_params['start_date'] = $start_date;
        $sales_params['end_date'] = $end_date;
        $sales_where[] = "status = 'completed'";

$sales_where_sql = !empty($sales_where) ? 'WHERE ' . implode(' AND ', $sales_where) : '';

$sales_query = "
            SELECT 
                COUNT(DISTINCT order_id) as total_orders,
                COALESCE(SUM(total_amount), 0) as total_sales,
                COALESCE(SUM(cgst_amount + sgst_amount), 0) as total_tax,
                COALESCE(SUM(taxable_amount), 0) as taxable_amount,
                COUNT(CASE WHEN order_type = 'Dine In' THEN 1 END) as dine_in_count,
                COUNT(CASE WHEN order_type = 'Pickup' THEN 1 END) as pickup_count,
                COUNT(CASE WHEN order_type = 'Delivery' THEN 1 END) as delivery_count
            FROM orders 
            {$sales_where_sql}
        ";
        
        $sales_stmt = $pdo->prepare($sales_query);
        foreach ($sales_params as $key => $value) {
            $sales_stmt->bindValue($key, $value);
        }
        $sales_stmt->execute();
        $sales_data = $sales_stmt->fetch(PDO::FETCH_ASSOC);

// Calculate totals
        $total_expenses = array_sum(array_column($expenses, 'amount'));

// Calculate opening balance (previous period's closing)
        $prev_date = date('Y-m-d', strtotime($start_date . ' -1 day'));
        $opening_query = "
            SELECT 
                COALESCE((
                    SELECT COALESCE(SUM(total_amount), 0) FROM orders 
                    WHERE branch_id = :branch_id 
                    AND DATE(order_date) = :prev_date
                    AND status = 'completed'
                ), 0) as prev_sales,
                COALESCE((
                    SELECT COALESCE(SUM(amount), 0) FROM expenses 
                    WHERE branch_id = :branch_id 
                    AND DATE(expense_date) = :prev_date
                ), 0) as prev_expenses
        ";
        
        $branch_for_query = ($selected_branch_id !== 'all' && is_numeric($selected_branch_id)) ? $selected_branch_id : 
                           ($_SESSION['role'] === 'branch' ? $_SESSION['branch_id'] : 1);
        
        $opening_stmt = $pdo->prepare($opening_query);
        $opening_stmt->execute([
            ':branch_id' => $branch_for_query,
            ':prev_date' => $prev_date
        ]);
        $prev_data = $opening_stmt->fetch(PDO::FETCH_ASSOC);
        
        $opening_balance = $prev_data['prev_sales'] - $prev_data['prev_expenses'];
        $closing_balance = $opening_balance + $sales_data['total_sales'] - $total_expenses;

// Set headers for CSV download
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="opening_closing_report_' . $start_date . '_to_' . $end_date . '_' . date('Y-m-d_His') . '.csv"');
        header('Cache-Control: no-cache, no-store, must-revalidate');
        header('Pragma: no-cache');
        header('Expires: 0');

$output = fopen('php://output', 'w');
        
        // Write UTF-8 BOM for Excel compatibility
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        
        // Write report header
        fputcsv($output, ['OPENING/CLOSING REPORT']);
        fputcsv($output, ['Date Range:', date('d-m-Y', strtotime($start_date)) . ' to ' . date('d-m-Y', strtotime($end_date))]);
        
        if ($selected_branch_id !== 'all' && is_numeric($selected_branch_id)) {
            $branch_stmt = $pdo->prepare("SELECT branch_name FROM branches WHERE branch_id = ?");
            $branch_stmt->execute([$selected_branch_id]);
            $branch_name = $branch_stmt->fetchColumn();
            fputcsv($output, ['Branch:', $branch_name]);
        } elseif ($_SESSION['role'] === 'branch') {
            $branch_stmt = $pdo->prepare("SELECT branch_name FROM branches WHERE branch_id = ?");
            $branch_stmt->execute([$_SESSION['branch_id']]);
            $branch_name = $branch_stmt->fetchColumn();
            fputcsv($output, ['Branch:', $branch_name]);
        } else {
            fputcsv($output, ['Branch:', 'All Branches']);
        }
        fputcsv($output, ['Generated On:', date('d-m-Y H:i:s')]);
        fputcsv($output, []);
        
        // Summary section
        fputcsv($output, ['SUMMARY']);
        fputcsv($output, ['Description', 'Amount']);
        fputcsv($output, ['Opening Balance', '₹' . number_format($opening_balance, 2)]);
        fputcsv($output, ['Total Sales', '₹' . number_format($sales_data['total_sales'], 2)]);
        fputcsv($output, ['Total Expenses', '₹' . number_format($total_expenses, 2)]);
        fputcsv($output, ['Closing Balance', '₹' . number_format($closing_balance, 2)]);
        fputcsv($output, []);
        
        // Sales Breakdown
        fputcsv($output, ['SALES BREAKDOWN']);
        fputcsv($output, ['Metric', 'Value']);
        fputcsv($output, ['Total Orders', $sales_data['total_orders']]);
        fputcsv($output, ['Taxable Amount', '₹' . number_format($sales_data['taxable_amount'], 2)]);
        fputcsv($output, ['Total Tax (CGST+SGST)', '₹' . number_format($sales_data['total_tax'], 2)]);
        fputcsv($output, ['Dine In Orders', $sales_data['dine_in_count']]);
        fputcsv($output, ['Pickup Orders', $sales_data['pickup_count']]);
        fputcsv($output, ['Delivery Orders', $sales_data['delivery_count']]);
        fputcsv($output, []);
        
        // Expenses details
        fputcsv($output, ['EXPENSE DETAILS']);
        fputcsv($output, ['S.No', 'Date', 'Time', 'Branch', 'Title', 'Amount', 'Payment Method', 'Reference No', 'Description', 'Added By']);
        
        if (empty($expenses)) {
            fputcsv($output, ['No expenses found for the selected period']);
        } else {
            foreach ($expenses as $index => $expense) {
                fputcsv($output, [
                    $index + 1,
                    date('d-m-Y', strtotime($expense['expense_date'])),
                    date('H:i', strtotime($expense['created_at'])),
                    $expense['branch_name'],
                    $expense['expense_title'],
                    '₹' . number_format($expense['amount'], 2),
                    $expense['payment_method'] ?? 'Cash',
                    $expense['reference_no'] ?? '',
                    $expense['description'] ?? '',
                    $expense['created_by_name'] ?? 'System'
                ]);
            }
        }

fclose($output);
        exit();
    } catch (Exception $e) {
        error_log("Export error: " . $e->getMessage());
        $_SESSION['error'] = "Error exporting to Excel: " . htmlspecialchars($e->getMessage());
        header("Location: opening_closing_reports.php");
        exit();
    }
}

// ---- Get previous period's closing balance ----
function getOpeningBalance($pdo, $branch_id, $date) {
    $prev_date = date('Y-m-d', strtotime($date . ' -1 day'));
    
    // Get previous day's sales
    $sales_query = "
        SELECT COALESCE(SUM(total_amount), 0) as total_sales
        FROM orders 
        WHERE branch_id = :branch_id 
        AND DATE(order_date) = :prev_date
        AND status = 'completed'
    ";
    $sales_stmt = $pdo->prepare($sales_query);
    $sales_stmt->execute(['branch_id' => $branch_id, 'prev_date' => $prev_date]);
    $prev_sales = $sales_stmt->fetchColumn();
    
    // Get previous day's expenses
    $expenses_query = "
        SELECT COALESCE(SUM(amount), 0) as total_expenses
        FROM expenses 
        WHERE branch_id = :branch_id 
        AND DATE(expense_date) = :prev_date
    ";
    $expenses_stmt = $pdo->prepare($expenses_query);
    $expenses_stmt->execute(['branch_id' => $branch_id, 'prev_date' => $prev_date]);
    $prev_expenses = $expenses_stmt->fetchColumn();
    
    return $prev_sales - $prev_expenses;
}

include './partials/layouts/layoutTop.php';

// ---- Auth check ----
if (!isset($_SESSION['user_id']) || !isset($_SESSION['role'])) {
    header("Location: login.php");
    exit();
}

// ---- CSRF token ----
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}
$csrf = $_SESSION['csrf_token'];

// ---- Get filters ----
$selected_branch_id = $_SESSION['role'] === 'branch' ? $_SESSION['branch_id'] : ($_GET['branch_id'] ?? 'all');
$date_filter = $_GET['date_filter'] ?? 'today';
$start_date = $_GET['start_date'] ?? date('Y-m-d');
$end_date = $_GET['end_date'] ?? date('Y-m-d');

// Set date range based on filter
switch($date_filter) {
    case 'today':
        $start_date = date('Y-m-d');
        $end_date = date('Y-m-d');
        break;
    case 'yesterday':
        $start_date = date('Y-m-d', strtotime('-1 day'));
        $end_date = date('Y-m-d', strtotime('-1 day'));
        break;
    case 'custom':
        // Use provided dates
        break;
}

// ---- Fetch branches for admin ----
$branches = [];
try {
    if ($_SESSION['role'] === 'admin') {
        $stmt = $pdo->query("SELECT branch_id, branch_name FROM branches ORDER BY branch_name");
        $branches = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
} catch (PDOException $e) {
    $_SESSION['error'] = "Error fetching branches: " . h($e->getMessage());
}

// ---- Fetch expenses for the selected period ----
$expenses = [];
$total_expenses = 0;
$sales_data = [];
$opening_balance = 0;
$closing_balance = 0;

try {
    // Build WHERE clause
    $where_clause = [];
    $params = [];

if ($selected_branch_id !== 'all' && is_numeric($selected_branch_id)) {
        $branch_id_for_query = $selected_branch_id;
    } elseif ($_SESSION['role'] === 'branch') {
        $branch_id_for_query = $_SESSION['branch_id'];
    } else {
        $branch_id_for_query = null;
    }

// Get expenses for the date range
    if ($branch_id_for_query) {
        // For single branch
        $expense_query = "
            SELECT 
                e.*,
                b.branch_name,
                creator.full_name as created_by_name
            FROM expenses e
            JOIN branches b ON e.branch_id = b.branch_id
            LEFT JOIN users creator ON e.added_by = creator.user_id
            WHERE e.branch_id = :branch_id 
            AND DATE(e.expense_date) BETWEEN :start_date AND :end_date
            ORDER BY e.expense_date DESC, e.created_at ASC
        ";
        $expense_stmt = $pdo->prepare($expense_query);
        $expense_stmt->execute([
            ':branch_id' => $branch_id_for_query,
            ':start_date' => $start_date,
            ':end_date' => $end_date
        ]);
        $expenses = $expense_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Get sales data
        $sales_query = "
            SELECT 
                COUNT(DISTINCT order_id) as total_orders,
                COALESCE(SUM(total_amount), 0) as total_sales,
                COALESCE(SUM(cgst_amount + sgst_amount), 0) as total_tax,
                COALESCE(SUM(taxable_amount), 0) as taxable_amount,
                COUNT(CASE WHEN order_type = 'Dine In' THEN 1 END) as dine_in_count,
                COUNT(CASE WHEN order_type = 'Pickup' THEN 1 END) as pickup_count,
                COUNT(CASE WHEN order_type = 'Delivery' THEN 1 END) as delivery_count
            FROM orders 
            WHERE branch_id = :branch_id 
            AND DATE(order_date) BETWEEN :start_date AND :end_date
            AND status = 'completed'
        ";
        $sales_stmt = $pdo->prepare($sales_query);
        $sales_stmt->execute([
            ':branch_id' => $branch_id_for_query,
            ':start_date' => $start_date,
            ':end_date' => $end_date
        ]);
        $sales_data = $sales_stmt->fetch(PDO::FETCH_ASSOC);
        
        // Get opening balance (day before start date)
        $opening_balance = getOpeningBalance($pdo, $branch_id_for_query, $start_date);
        
    } else {
        // For all branches (admin only)
        $expense_query = "
            SELECT 
                e.*,
                b.branch_name,
                creator.full_name as created_by_name
            FROM expenses e
            JOIN branches b ON e.branch_id = b.branch_id
            LEFT JOIN users creator ON e.added_by = creator.user_id
            WHERE DATE(e.expense_date) BETWEEN :start_date AND :end_date
            ORDER BY e.branch_id, e.expense_date DESC, e.created_at ASC
        ";
        $expense_stmt = $pdo->prepare($expense_query);
        $expense_stmt->execute([
            ':start_date' => $start_date,
            ':end_date' => $end_date
        ]);
        $expenses = $expense_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Get sales data for all branches
        $sales_query = "
            SELECT 
                COUNT(DISTINCT order_id) as total_orders,
                COALESCE(SUM(total_amount), 0) as total_sales,
                COALESCE(SUM(cgst_amount + sgst_amount), 0) as total_tax,
                COALESCE(SUM(taxable_amount), 0) as taxable_amount,
                COUNT(CASE WHEN order_type = 'Dine In' THEN 1 END) as dine_in_count,
                COUNT(CASE WHEN order_type = 'Pickup' THEN 1 END) as pickup_count,
                COUNT(CASE WHEN order_type = 'Delivery' THEN 1 END) as delivery_count
            FROM orders 
            WHERE DATE(order_date) BETWEEN :start_date AND :end_date
            AND status = 'completed'
        ";
        $sales_stmt = $pdo->prepare($sales_query);
        $sales_stmt->execute([
            ':start_date' => $start_date,
            ':end_date' => $end_date
        ]);
        $sales_data = $sales_stmt->fetch(PDO::FETCH_ASSOC);
        
        // For all branches, sum opening balances
        $opening_balance = 0;
        $branch_stmt = $pdo->query("SELECT branch_id FROM branches");
        $all_branches = $branch_stmt->fetchAll(PDO::FETCH_COLUMN);
        foreach ($all_branches as $bid) {
            $opening_balance += getOpeningBalance($pdo, $bid, $start_date);
        }
    }
    
    // Calculate totals
    $total_expenses = array_sum(array_column($expenses, 'amount'));
    
    // Calculate closing balance
    $closing_balance = $opening_balance + ($sales_data['total_sales'] ?? 0) - $total_expenses;
    
    // Group expenses by branch for multi-branch view
    $expenses_by_branch = [];
    foreach ($expenses as $expense) {
        $branch = $expense['branch_name'];
        if (!isset($expenses_by_branch[$branch])) {
            $expenses_by_branch[$branch] = [];
        }
        $expenses_by_branch[$branch][] = $expense;
    }
    
} catch (PDOException $e) {
    $_SESSION['error'] = "Error fetching data: " . h($e->getMessage());
}

// Helper function
function h($v) { return htmlspecialchars($v ?? '', ENT_QUOTES, 'UTF-8'); }
?>

/* Expense card for mobile */
.expense-card {
    border: 1px solid #e9ecef;
    border-radius: 12px;
    transition: all 0.3s ease;
    margin-bottom: 16px;
    background: white;
}
.expense-card:hover {
    box-shadow: 0 4px 12px rgba(0,0,0,0.1);
    transform: translateY(-2px);
}
.expense-card .card-body { padding: 16px; }
.expense-amount { font-weight: 700; color: #dc3545; }

/* Title pills */
.title-pill {
    display: inline-block;
    padding: 4px 12px;
    border-radius: 20px;
    font-size: 0.75rem;
    font-weight: 500;
    background: #e9ecef;
    color: #495057;
}

/* Action buttons */
.action-btn {
    width: 32px;
    height: 32px;
    border-radius: 50%;
    display: inline-flex;
    align-items: center;
    justify-content: center;
    transition: all 0.2s;
}
.action-btn:hover {
    transform: scale(1.1);
}
.view-btn {
    background: rgba(13, 110, 253, 0.1);
    color: #0d6efd;
}
.view-btn:hover {
    background: #0d6efd;
    color: white;
}

/* Responsive */
@media (max-width: 768px) {
    .small-card { min-height: 100px; }
    .export-buttons {
        flex-direction: column;
        width: 100%;
    }
    .export-buttons .btn {
        width: 100%;
        margin: 2px 0;
    }
}

.row-cols-xxxl-4 > * {
    flex: 0 0 auto;
    width: 25%;
}
@media (max-width: 1399px) {
    .row-cols-lg-3 > * { width: 33.333%; }
}
@media (max-width: 991px) {
    .row-cols-sm-2 > * { width: 50%; }
}
@media (max-width: 575px) {
    .row-cols-1 > * { width: 100%; }
}

/* Report specific styles */
.balance-positive { color: #28a745; font-weight: bold; }
.balance-negative { color: #dc3545; font-weight: bold; }
.report-header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 20px; border-radius: 12px; margin-bottom: 24px; }
.stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 16px; margin-bottom: 24px; }
.stat-item { background: white; padding: 16px; border-radius: 12px; box-shadow: 0 2px 8px rgba(0,0,0,0.1); text-align: center; }
.stat-label { font-size: 0.85rem; color: #6c757d; margin-bottom: 8px; }
.stat-value { font-size: 1.5rem; font-weight: bold; color: #333; }

/* Custom date range visibility */
.custom-date-range {
    display: none;
}
.custom-date-range.show {
    display: flex;
    gap: 10px;
    flex-wrap: wrap;
}
</style>

<div class="dashboard-main-body">
    <div class="d-flex flex-wrap align-items-center justify-content-between gap-2 mb-16">
        <h6 class="fw-semibold mb-0 text-lg">Opening/Closing Report</h6>
        <ul class="d-flex align-items-center gap-1 small">
            <li class="fw-medium">
                <a href="index.php" class="d-flex align-items-center gap-1 hover-text-primary text-sm">
                    <iconify-icon icon="solar:home-smile-angle-outline" class="icon text-md"></iconify-icon>
                    Dashboard
                </a>
            </li>
            <li>-</li>
            <li class="fw-medium text-sm">Opening/Closing Report</li>
        </ul>
    </div>

<div class="card h-100 mb-24">
        <div class="card-header compact-header">
            <h6 class="section-title fw-semibold mb-0 text-md">Report Filters</h6>
        </div>
        <div class="card-body compact-body">
            <form method="GET" action="oc_report.php" class="d-flex flex-wrap gap-3 align-items-end" id="reportForm">
                <?php if ($_SESSION['role'] === 'admin'): ?>
                <div style="min-width: 200px;">
                    <label class="form-label mb-1">Branch</label>
                    <select class="form-select form-select-sm" name="branch_id">
                        <option value="all" <?= $selected_branch_id === 'all' ? 'selected' : ''; ?>>All Branches</option>
                        <?php foreach ($branches as $branch): ?>
                        <option value="<?= $branch['branch_id']; ?>" <?= $selected_branch_id == $branch['branch_id'] ? 'selected' : ''; ?>>
                            <?= h($branch['branch_name']); ?>
                        </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <?php endif; ?>
                
                <div style="min-width: 150px;">
                    <label class="form-label mb-1">Date Filter</label>
                    <select class="form-select form-select-sm" name="date_filter" id="dateFilter">
                        <option value="today" <?= $date_filter == 'today' ? 'selected' : ''; ?>>Today</option>
                        <option value="yesterday" <?= $date_filter == 'yesterday' ? 'selected' : ''; ?>>Yesterday</option>
                        <option value="custom" <?= $date_filter == 'custom' ? 'selected' : ''; ?>>Custom Range</option>
                    </select>
                </div>
                
                <div class="custom-date-range <?= $date_filter == 'custom' ? 'show' : ''; ?>" id="customDateRange">
                    <div>
                        <label class="form-label mb-1">Start Date</label>
                        <input type="date" class="form-control form-control-sm" name="start_date" value="<?= h($start_date); ?>" max="<?= date('Y-m-d'); ?>">
                    </div>
                    <div>
                        <label class="form-label mb-1">End Date</label>
                        <input type="date" class="form-control form-control-sm" name="end_date" value="<?= h($end_date); ?>" max="<?= date('Y-m-d'); ?>">
                    </div>
                </div>
                
                <div class="export-buttons d-flex gap-2 flex-wrap">
                    <button type="submit" class="btn btn-primary btn-sm d-flex align-items-center gap-1">
                        <iconify-icon icon="mdi:filter"></iconify-icon> Generate Report
                    </button>
                    <button type="submit" name="export" value="excel" class="btn btn-success btn-sm d-flex align-items-center gap-1">
                        <iconify-icon icon="mdi:file-excel"></iconify-icon> Export to Excel
                    </button>
                   
                </div>
            </form>
        </div>
    </div>

<?php if (!empty($_SESSION['success'])): ?>
        <div class="alert alert-success alert-dismissible fade show mb-24">
            <?= h($_SESSION['success']); unset($_SESSION['success']); ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>
    <?php if (!empty($_SESSION['error'])): ?>
        <div class="alert alert-danger alert-dismissible fade show mb-24">
            <?= h($_SESSION['error']); unset($_SESSION['error']); ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>

<div class="report-header">
        <div class="row align-items-center">
            <div class="col-md-6">
                <h4 class="text-white mb-2">
                    Report: 
                    <?php if ($start_date == $end_date): ?>
                        <?= date('d F Y', strtotime($start_date)); ?>
                    <?php else: ?>
                        <?= date('d M Y', strtotime($start_date)); ?> - <?= date('d M Y', strtotime($end_date)); ?>
                    <?php endif; ?>
                </h4>
                <p class="text-white-50 mb-0">
                    <?php if ($selected_branch_id !== 'all' && is_numeric($selected_branch_id)): ?>
                        <?php 
                        $branch_stmt = $pdo->prepare("SELECT branch_name FROM branches WHERE branch_id = ?");
                        $branch_stmt->execute([$selected_branch_id]);
                        echo "Branch: " . h($branch_stmt->fetchColumn());
                        ?>
                    <?php elseif ($_SESSION['role'] === 'branch'): ?>
                        <?php 
                        $branch_stmt = $pdo->prepare("SELECT branch_name FROM branches WHERE branch_id = ?");
                        $branch_stmt->execute([$_SESSION['branch_id']]);
                        echo "Branch: " . h($branch_stmt->fetchColumn());
                        ?>
                    <?php else: ?>
                        Branch: All Branches
                    <?php endif; ?>
                </p>
            </div>
            <div class="col-md-6 text-md-end">
                <p class="text-white-50 mb-0">Generated on: <?= date('d-m-Y H:i'); ?></p>
                <p class="text-white-50 mb-0">
                    Period: 
                    <?php
                    switch($date_filter) {
                        case 'today': echo 'Today'; break;
                        case 'yesterday': echo 'Yesterday'; break;
                        case 'custom': echo 'Custom Range'; break;
                    }
                    ?>
                </p>
            </div>
        </div>
    </div>

<div class="row row-cols-xxxl-4 row-cols-lg-3 row-cols-sm-2 row-cols-1 gy-3 mb-24">
        <div class="col">
            <div class="card shadow-none border bg-gradient-start-1 h-100 small-card stat-card">
                <div class="card-body compact-body">
                    <div class="d-flex align-items-center justify-content-between">
                        <div>
                            <p class="fw-medium text-primary-light mb-1 text-sm">Opening Balance</p>
                            <h6 class="mb-0 text-lg important-text-1">₹<?= number_format($opening_balance, 2); ?></h6>
                        </div>
                        <div class="w-40-px h-40-px bg-primary rounded-circle d-flex align-items-center justify-content-center">
                            <iconify-icon icon="mdi:cash-plus" class="text-white small-icon"></iconify-icon>
                        </div>
                    </div>
                    <p class="text-xs text-primary-light mt-8 mb-0">As of <?= date('d-m-Y', strtotime($start_date . ' -1 day')); ?></p>
                </div>
            </div>
        </div>
        
        <div class="col">
            <div class="card shadow-none border bg-gradient-start-2 h-100 small-card stat-card">
                <div class="card-body compact-body">
                    <div class="d-flex align-items-center justify-content-between">
                        <div>
                            <p class="fw-medium text-primary-light mb-1 text-sm">Total Sales</p>
                            <h6 class="mb-0 text-lg important-text-2">₹<?= number_format($sales_data['total_sales'] ?? 0, 2); ?></h6>
                        </div>
                        <div class="w-40-px h-40-px bg-success rounded-circle d-flex align-items-center justify-content-center">
                            <iconify-icon icon="mdi:trending-up" class="text-white small-icon"></iconify-icon>
                        </div>
                    </div>
                    <p class="text-xs text-primary-light mt-8 mb-0"><?= $sales_data['total_orders'] ?? 0; ?> orders</p>
                </div>
            </div>
        </div>
        
        <div class="col">
            <div class="card shadow-none border bg-gradient-start-3 h-100 small-card stat-card">
                <div class="card-body compact-body">
                    <div class="d-flex align-items-center justify-content-between">
                        <div>
                            <p class="fw-medium text-primary-light mb-1 text-sm">Total Expenses</p>
                            <h6 class="mb-0 text-lg important-text-3">₹<?= number_format($total_expenses, 2); ?></h6>
                        </div>
                        <div class="w-40-px h-40-px bg-warning rounded-circle d-flex align-items-center justify-content-center">
                            <iconify-icon icon="mdi:cash-minus" class="text-white small-icon"></iconify-icon>
                        </div>
                    </div>
                    <p class="text-xs text-primary-light mt-8 mb-0"><?= count($expenses); ?> transactions</p>
                </div>
            </div>
        </div>
        
        <div class="col">
            <div class="card shadow-none border bg-gradient-start-4 h-100 small-card stat-card">
                <div class="card-body compact-body">
                    <div class="d-flex align-items-center justify-content-between">
                        <div>
                            <p class="fw-medium text-primary-light mb-1 text-sm">Closing Balance</p>
                            <h6 class="mb-0 text-lg <?= $closing_balance >= 0 ? 'important-text-4' : 'text-danger'; ?>">
                                ₹<?= number_format($closing_balance, 2); ?>
                            </h6>
                        </div>
                        <div class="w-40-px h-40-px bg-info rounded-circle d-flex align-items-center justify-content-center">
                            <iconify-icon icon="mdi:cash-check" class="text-white small-icon"></iconify-icon>
                        </div>
                    </div>
                    <p class="text-xs text-primary-light mt-8 mb-0">Opening + Sales - Expenses</p>
                </div>
            </div>
        </div>
    </div>

<div class="row mb-24">
        <div class="col-md-6">
            <div class="card h-100">
                <div class="card-header compact-header">
                    <h6 class="fw-semibold mb-0">Sales Breakdown</h6>
                </div>
                <div class="card-body compact-body">
                    <div class="table-responsive">
                        <table class="table table-sm table-bordered">
                            <tr>
                                <th style="width: 50%">Total Orders</th>
                                <td class="fw-bold"><?= number_format($sales_data['total_orders'] ?? 0); ?></td>
                            </tr>
                            <tr>
                                <th>Taxable Amount</th>
                                <td>₹<?= number_format($sales_data['taxable_amount'] ?? 0, 2); ?></td>
                            </tr>
                            <tr>
                                <th>Total Tax (CGST+SGST)</th>
                                <td>₹<?= number_format($sales_data['total_tax'] ?? 0, 2); ?></td>
                            </tr>
                            <tr>
                                <th>Dine In Orders</th>
                                <td><?= number_format($sales_data['dine_in_count'] ?? 0); ?></td>
                            </tr>
                            <tr>
                                <th>Pickup Orders</th>
                                <td><?= number_format($sales_data['pickup_count'] ?? 0); ?></td>
                            </tr>
                            <tr>
                                <th>Delivery Orders</th>
                                <td><?= number_format($sales_data['delivery_count'] ?? 0); ?></td>
                            </tr>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        
        <div class="col-md-6">
            <div class="card h-100">
                <div class="card-header compact-header">
                    <h6 class="fw-semibold mb-0">Balance Summary</h6>
                </div>
                <div class="card-body compact-body">
                    <div class="table-responsive">
                        <table class="table table-sm table-bordered">
                            <tr>
                                <th style="width: 50%">Opening Balance</th>
                                <td class="fw-bold">₹<?= number_format($opening_balance, 2); ?></td>
                            </tr>
                            <tr>
                                <th>Add: Total Sales</th>
                                <td class="text-success">+ ₹<?= number_format($sales_data['total_sales'] ?? 0, 2); ?></td>
                            </tr>
                            <tr>
                                <th>Less: Total Expenses</th>
                                <td class="text-danger">- ₹<?= number_format($total_expenses, 2); ?></td>
                            </tr>
                            <tr class="border-top">
                                <th class="pt-2">Closing Balance</th>
                                <td class="pt-2 fw-bold <?= $closing_balance >= 0 ? 'text-success' : 'text-danger'; ?>">
                                    ₹<?= number_format($closing_balance, 2); ?>
                                </td>
                            </tr>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>

<?php if ($_SESSION['role'] === 'admin' && $selected_branch_id === 'all' && !empty($expenses_by_branch)): ?>
        <?php foreach ($expenses_by_branch as $branch_name => $branch_expenses): ?>
        <div class="card mb-24">
            <div class="card-header compact-header bg-light">
                <h6 class="fw-semibold mb-0"><?= h($branch_name); ?> - Expenses</h6>
            </div>
            <div class="card-body compact-body">
                <div class="table-responsive">
                    <table class="table bordered-table sm-table small-table mb-0">
                        <thead>
                            <tr>
                                <th>S.No</th>
                                <th>Date</th>
                                <th>Time</th>
                                <th>Title</th>
                                <th>Amount</th>
                                <th>Payment</th>
                                <th>Reference</th>
                                <th>Description</th>
                                <th>Added By</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($branch_expenses as $index => $exp): ?>
                            <tr>
                                <td><?= $index + 1; ?></td>
                                <td><?= date('d-m-Y', strtotime($exp['expense_date'])); ?></td>
                                <td><?= date('H:i', strtotime($exp['created_at'])); ?></td>
                                <td><span class="title-pill"><?= h($exp['expense_title']); ?></span></td>
                                <td class="fw-bold text-danger">₹<?= number_format($exp['amount'], 2); ?></td>
                                <td><?= h($exp['payment_method']); ?></td>
                                <td><?= h($exp['reference_no'] ?: '-'); ?></td>
                                <td><?= h($exp['description'] ?: '-'); ?></td>
                                <td><?= h($exp['created_by_name'] ?: 'System'); ?></td>
                                <td>
                                    <a href="javascript:void(0)" class="action-btn view-btn" 
                                       data-bs-toggle="modal" data-bs-target="#viewExpenseModal<?= $exp['expense_id']; ?>">
                                        <iconify-icon icon="iconamoon:eye-light"></iconify-icon>
                                    </a>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr class="bg-light">
                                <th colspan="4" class="text-end">Branch Total:</th>
                                <th class="text-danger">₹<?= number_format(array_sum(array_column($branch_expenses, 'amount')), 2); ?></th>
                                <th colspan="5"></th>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        </div>
        <?php endforeach; ?>
    <?php endif; ?>

<?php if (($selected_branch_id !== 'all' && is_numeric($selected_branch_id)) || $_SESSION['role'] !== 'admin'): ?>
    <div class="card d-none d-md-block">
        <div class="card-header compact-header">
            <h6 class="fw-semibold mb-0">
                Expense Details 
                <?php if ($start_date == $end_date): ?>
                    - <?= date('d-m-Y', strtotime($start_date)); ?>
                <?php else: ?>
                    (<?= date('d-m-Y', strtotime($start_date)); ?> to <?= date('d-m-Y', strtotime($end_date)); ?>)
                <?php endif; ?>
            </h6>
        </div>
        <div class="card-body compact-body">
            <div class="table-responsive">
                <table class="table bordered-table sm-table small-table mb-0">
                    <thead>
                        <tr>
                            <th>S.No</th>
                            <th>Date</th>
                            <th>Time</th>
                            <th>Title</th>
                            <th>Amount</th>
                            <th>Payment</th>
                            <th>Reference No</th>
                            <th>Description</th>
                            <th>Added By</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($expenses)): ?>
                       
                        <?php else: ?>
                        <?php foreach ($expenses as $index => $exp): ?>
                        <tr>
                            <td><?= $index + 1; ?></td>
                            <td><?= date('d-m-Y', strtotime($exp['expense_date'])); ?></td>
                            <td><?= date('H:i', strtotime($exp['created_at'])); ?></td>
                            <td><span class="title-pill"><?= h($exp['expense_title']); ?></span></td>
                            <td class="fw-bold text-danger">₹<?= number_format($exp['amount'], 2); ?></td>
                            <td><?= h($exp['payment_method']); ?></td>
                            <td><?= h($exp['reference_no'] ?: '-'); ?></td>
                            <td><?= h($exp['description'] ?: '-'); ?></td>
                            <td><?= h($exp['created_by_name'] ?: 'System'); ?></td>
                            <td>
                                <a href="javascript:void(0)" class="action-btn view-btn" 
                                   data-bs-toggle="modal" data-bs-target="#viewExpenseModal<?= $exp['expense_id']; ?>">
                                    <iconify-icon icon="iconamoon:eye-light"></iconify-icon>
                                </a>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                    <?php if (!empty($expenses)): ?>
                    <tfoot>
                        <tr class="bg-light">
                            <th colspan="4" class="text-end">Total Expenses:</th>
                            <th class="text-danger">₹<?= number_format($total_expenses, 2); ?></th>
                            <th colspan="5"></th>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
            </div>
        </div>
    </div>

<div class="d-md-none">
        <h6 class="mb-3 fw-semibold">
            Expense Details 
            <?php if ($start_date == $end_date): ?>
                - <?= date('d-m-Y', strtotime($start_date)); ?>
            <?php else: ?>
                (<?= date('d-m-Y', strtotime($start_date)); ?> to <?= date('d-m-Y', strtotime($end_date)); ?>)
            <?php endif; ?>
        </h6>
        <?php if (empty($expenses)): ?>
        <div class="empty-state p-5 text-center border rounded">
            <iconify-icon icon="mdi:cash-remove" style="font-size: 48px; color: #aaa;"></iconify-icon>
            <h6 class="mt-3">No expenses found for the selected period</h6>
        </div>
        <?php else: ?>
        <?php foreach ($expenses as $exp): ?>
        <div class="expense-card">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-start mb-2">
                    <div>
                        <span class="fw-bold"><?= date('d M Y', strtotime($exp['expense_date'])); ?></span>
                        <span class="title-pill ms-2"><?= h($exp['expense_title']); ?></span>
                    </div>
                    <span class="expense-amount">₹<?= number_format($exp['amount'], 2); ?></span>
                </div>
                
                <div class="mb-2">
                    <small class="text-muted">Time:</small>
                    <span class="ms-2"><?= date('H:i', strtotime($exp['created_at'])); ?></span>
                </div>
                
                <?php if (!empty($exp['description'])): ?>
                <div class="mb-2">
                    <small class="text-muted">Description:</small>
                    <p class="mb-0"><?= h($exp['description']); ?></p>
                </div>
                <?php endif; ?>
                
                <div class="row g-2 mb-2">
                    <?php if (!empty($exp['reference_no'])): ?>
                    <div class="col-6">
                        <small class="text-muted d-block">Reference</small>
                        <span><?= h($exp['reference_no']); ?></span>
                    </div>
                    <?php endif; ?>
                    
                    <?php if (!empty($exp['payment_method'])): ?>
                    <div class="col-6">
                        <small class="text-muted d-block">Payment</small>
                        <span><?= h($exp['payment_method']); ?></span>
                    </div>
                    <?php endif; ?>
                </div>
                
                <div class="d-flex justify-content-between align-items-center mt-3 pt-2 border-top">
                    <small class="text-muted">By: <?= h($exp['created_by_name'] ?: 'System'); ?></small>
                    <a href="javascript:void(0)" class="btn btn-sm btn-outline-primary" 
                       data-bs-toggle="modal" data-bs-target="#viewExpenseModal<?= $exp['expense_id']; ?>">
                        <iconify-icon icon="iconamoon:eye-light"></iconify-icon> View
                    </a>
                </div>
            </div>
        </div>
        <?php endforeach; ?>
        
        
        <div class="card mt-3 bg-light">
            <div class="card-body">
                <div class="d-flex justify-content-between">
                    <span class="fw-bold">Total Expenses:</span>
                    <span class="fw-bold text-danger">₹<?= number_format($total_expenses, 2); ?></span>
                </div>
            </div>
        </div>
        <?php endif; ?>
    </div>
    <?php endif; ?>

<?php foreach ($expenses as $exp): ?>
    <div class="modal fade" id="viewExpenseModal<?= $exp['expense_id']; ?>" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Expense Details</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <div class="modal-body">
                    <table class="table table-sm table-bordered">
                        <tr>
                            <th style="width: 40%">Date</th>
                            <td><?= date('d-m-Y', strtotime($exp['expense_date'])); ?></td>
                        </tr>
                        <tr>
                            <th>Time</th>
                            <td><?= date('H:i:s', strtotime($exp['created_at'])); ?></td>
                        </tr>
                        <tr>
                            <th>Branch</th>
                            <td><?= h($exp['branch_name']); ?></td>
                        </tr>
                        <tr>
                            <th>Title</th>
                            <td><span class="title-pill"><?= h($exp['expense_title']); ?></span></td>
                        </tr>
                        <tr>
                            <th>Amount</th>
                            <td class="fw-bold text-danger">₹<?= number_format($exp['amount'], 2); ?></td>
                        </tr>
                        <tr>
                            <th>Payment Method</th>
                            <td><?= h($exp['payment_method']); ?></td>
                        </tr>
                        <?php if (!empty($exp['reference_no'])): ?>
                        <tr>
                            <th>Reference #</th>
                            <td><?= h($exp['reference_no']); ?></td>
                        </tr>
                        <?php endif; ?>
                        <?php if (!empty($exp['description'])): ?>
                        <tr>
                            <th>Description</th>
                            <td><?= nl2br(h($exp['description'])); ?></td>
                        </tr>
                        <?php endif; ?>
                        <tr>
                            <th>Added By</th>
                            <td><?= h($exp['created_by_name'] ?: 'System'); ?></td>
                        </tr>
                        <tr>
                            <th>Created At</th>
                            <td><?= date('d-m-Y H:i:s', strtotime($exp['created_at'])); ?></td>
                        </tr>
                    </table>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                </div>
            </div>
        </div>
    </div>
    <?php endforeach; ?>
</div>

<?php include './partials/layouts/layoutBottom.php'; ?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: oc_report.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation