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/rutzotech.com/public_html/bill

Free Space:

10.26 TB

🔧 Quick Actions

✏️ Editing: exchange_reports.php

<?php
// exchange_reports.php
require_once 'auth_check.php';
require_once 'db_connection.php';

// Start session
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Get current shop ID
$shop_id = $_SESSION['shop_id'] ?? 1;

// Set default user_id
$current_user_id = $_SESSION['user_id'] ?? 1;

// Get shop details from database
$shopDetails = [];
$shop_result = $conn->query("SELECT * FROM shops WHERE id = $shop_id LIMIT 1");
if ($shop_result && $shop_result->num_rows > 0) {
    $shopDetails = $shop_result->fetch_assoc();
}

// Get date filters from GET parameters
$start_date = $_GET['start_date'] ?? date('Y-m-d');
$end_date = $_GET['end_date'] ?? date('Y-m-d');
$time_range = $_GET['time_range'] ?? 'today';
$exchange_status = $_GET['exchange_status'] ?? 'all'; // all, completed, pending

// Adjust dates based on time range
switch ($time_range) {
    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 'this_week':
        $start_date = date('Y-m-d', strtotime('monday this week'));
        $end_date = date('Y-m-d');
        break;
    case 'last_week':
        $start_date = date('Y-m-d', strtotime('monday last week'));
        $end_date = date('Y-m-d', strtotime('sunday last week'));
        break;
    case 'this_month':
        $start_date = date('Y-m-01');
        $end_date = date('Y-m-d');
        break;
    case 'last_month':
        $start_date = date('Y-m-01', strtotime('-1 month'));
        $end_date = date('Y-m-t', strtotime('-1 month'));
        break;
    case 'last_30_days':
        $start_date = date('Y-m-d', strtotime('-30 days'));
        $end_date = date('Y-m-d');
        break;
    case 'custom':
        // Use the dates provided
        break;
}

// Build WHERE clause for exchanges
$where_clause = "WHERE DATE(e.created_at) BETWEEN '$start_date' AND '$end_date' AND e.shop_id = $shop_id";

// Add status filter
if ($exchange_status === 'completed') {
    $where_clause .= " AND e.new_bill_id IS NOT NULL";
} elseif ($exchange_status === 'pending') {
    $where_clause .= " AND e.new_bill_id IS NULL";
}

// Get summary statistics
$summary_query = "
    SELECT 
        COUNT(DISTINCT e.id) as total_exchanges,
        COUNT(DISTINCT CASE WHEN e.new_bill_id IS NOT NULL THEN e.id END) as completed_exchanges,
        COUNT(DISTINCT CASE WHEN e.new_bill_id IS NULL THEN e.id END) as pending_exchanges,
        COUNT(DISTINCT e.bill_id) as unique_bills,
        COUNT(DISTINCT ei.id) as total_items,
        COALESCE(SUM(ei.old_quantity), 0) as total_old_quantity,
        COALESCE(SUM(ei.old_price * ei.old_quantity), 0) as total_old_value,
        COALESCE((
            SELECT SUM(bi.quantity) 
            FROM bill_items bi 
            WHERE bi.bill_id IN (
                SELECT DISTINCT e2.new_bill_id 
                FROM exchanges e2 
                WHERE e2.new_bill_id IS NOT NULL 
                AND DATE(e2.created_at) BETWEEN '$start_date' AND '$end_date'
                AND e2.shop_id = $shop_id
            )
        ), 0) as total_new_quantity,
        COALESCE((
            SELECT SUM(bi.price * bi.quantity) 
            FROM bill_items bi 
            WHERE bi.bill_id IN (
                SELECT DISTINCT e2.new_bill_id 
                FROM exchanges e2 
                WHERE e2.new_bill_id IS NOT NULL 
                AND DATE(e2.created_at) BETWEEN '$start_date' AND '$end_date'
                AND e2.shop_id = $shop_id
            )
        ), 0) as total_new_value,
        COALESCE(AVG(ei.old_price * ei.old_quantity), 0) as avg_exchange_value,
        COALESCE(MAX(ei.old_price * ei.old_quantity), 0) as max_exchange_value,
        COALESCE(MIN(ei.old_price * ei.old_quantity), 0) as min_exchange_value
    FROM exchanges e
    LEFT JOIN exchange_items ei ON e.id = ei.exchange_id
    $where_clause
";

$summary_result = $conn->query($summary_query);
$summary = $summary_result ? $summary_result->fetch_assoc() : [];

// Get daily exchange summary
$daily_query = "
    SELECT 
        DATE(e.created_at) as exchange_date,
        COUNT(DISTINCT e.id) as exchange_count,
        COUNT(DISTINCT CASE WHEN e.new_bill_id IS NOT NULL THEN e.id END) as completed_count,
        COUNT(DISTINCT CASE WHEN e.new_bill_id IS NULL THEN e.id END) as pending_count,
        COALESCE(SUM(ei.old_price * ei.old_quantity), 0) as daily_old_value,
        COUNT(DISTINCT ei.id) as daily_items
    FROM exchanges e
    LEFT JOIN exchange_items ei ON e.id = ei.exchange_id
    $where_clause
    GROUP BY DATE(e.created_at)
    ORDER BY exchange_date DESC
";

$daily_result = $conn->query($daily_query);
$daily_data = $daily_result ? $daily_result->fetch_all(MYSQLI_ASSOC) : [];

// Get new values for each day separately
if (!empty($daily_data)) {
    foreach ($daily_data as &$day) {
        $day_date = $day['exchange_date'];
        
        $new_value_query = "
            SELECT COALESCE(SUM(bi.price * bi.quantity), 0) as daily_new_value
            FROM bill_items bi
            WHERE bi.bill_id IN (
                SELECT DISTINCT e.new_bill_id 
                FROM exchanges e 
                WHERE DATE(e.created_at) = '$day_date'
                AND e.new_bill_id IS NOT NULL
                AND e.shop_id = $shop_id
            )
        ";
        
        $new_value_result = $conn->query($new_value_query);
        if ($new_value_result) {
            $new_value_data = $new_value_result->fetch_assoc();
            $day['daily_new_value'] = $new_value_data['daily_new_value'] ?? 0;
        } else {
            $day['daily_new_value'] = 0;
        }
    }
}

// FIXED: Get detailed exchange list with proper customer names
$exchange_list_query = "
    SELECT 
        e.id,
        e.exchange_no,
        e.reason,
        e.created_at,
        e.bill_id as old_bill_id,
        e.new_bill_id,
        ob.bill_no as old_bill_no,
        nb.bill_no as new_bill_no,
        COALESCE(ob.customer_name, 'Walk-in') as old_customer_name,
        ob.customer_mobile as old_customer_mobile,
        COALESCE(nb.customer_name, 'Walk-in') as new_customer_name,
        nb.customer_mobile as new_customer_mobile,
        u.name as user_name,
        COUNT(DISTINCT ei.id) as item_count,
        COALESCE(SUM(ei.old_quantity), 0) as total_old_qty,
        COALESCE(SUM(ei.old_price * ei.old_quantity), 0) as old_total
    FROM exchanges e
    LEFT JOIN bills ob ON e.bill_id = ob.id
    LEFT JOIN bills nb ON e.new_bill_id = nb.id
    LEFT JOIN users u ON e.user_id = u.id
    LEFT JOIN exchange_items ei ON e.id = ei.exchange_id
    $where_clause
    GROUP BY e.id, e.exchange_no, e.reason, e.created_at, e.bill_id, e.new_bill_id, 
             ob.bill_no, nb.bill_no, ob.customer_name, ob.customer_mobile, 
             nb.customer_name, nb.customer_mobile, u.name
    ORDER BY e.created_at DESC
";

$exchange_list_result = $conn->query($exchange_list_query);
$exchange_list = $exchange_list_result ? $exchange_list_result->fetch_all(MYSQLI_ASSOC) : [];

// Get new values for each exchange separately
if (!empty($exchange_list)) {
    foreach ($exchange_list as &$exchange) {
        $exchange['total_new_qty'] = 0;
        $exchange['new_total'] = 0;
        
        if ($exchange['new_bill_id']) {
            $new_items_query = "
                SELECT 
                    SUM(quantity) as total_qty,
                    SUM(price * quantity) as total_value
                FROM bill_items
                WHERE bill_id = " . $exchange['new_bill_id'] . "
            ";
            
            $new_items_result = $conn->query($new_items_query);
            if ($new_items_result) {
                $new_items_data = $new_items_result->fetch_assoc();
                $exchange['total_new_qty'] = $new_items_data['total_qty'] ?? 0;
                $exchange['new_total'] = $new_items_data['total_value'] ?? 0;
            }
        }
    }
}

// Handle CSV export
if (isset($_GET['export']) && $_GET['export'] == 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="exchange_report_' . date('Y-m-d') . '.csv"');
    
    // Add BOM for Excel
    echo "\xEF\xBB\xBF";

$output = fopen('php://output', 'w');

// Add Report Header
    fputcsv($output, ['EXCHANGE REPORT - ' . strtoupper(str_replace('_', ' ', $time_range))]);
    fputcsv($output, ['Period:', date('d M Y', strtotime($start_date)) . ' to ' . date('d M Y', strtotime($end_date))]);
    fputcsv($output, []);
    
    // Summary Section
    fputcsv($output, ['SUMMARY STATISTICS']);
    fputcsv($output, ['Total Exchanges:', $summary['total_exchanges'] ?? 0]);
    fputcsv($output, ['Completed Exchanges:', $summary['completed_exchanges'] ?? 0]);
    fputcsv($output, ['Pending Exchanges:', $summary['pending_exchanges'] ?? 0]);
    fputcsv($output, ['Unique Bills:', $summary['unique_bills'] ?? 0]);
    fputcsv($output, ['Total Items:', $summary['total_items'] ?? 0]);
    fputcsv($output, []);
    fputcsv($output, ['VALUE SUMMARY']);
    fputcsv($output, ['Total Old Value:', number_format($summary['total_old_value'] ?? 0, 2)]);
    fputcsv($output, ['Total New Value:', number_format($summary['total_new_value'] ?? 0, 2)]);
    fputcsv($output, ['Exchange Difference:', number_format(($summary['total_new_value'] ?? 0) - ($summary['total_old_value'] ?? 0), 2)]);
    fputcsv($output, ['Average Exchange Value:', number_format($summary['avg_exchange_value'] ?? 0, 2)]);
    fputcsv($output, []);
    
    // Detailed Exchanges
    fputcsv($output, ['DETAILED EXCHANGES']);
    fputcsv($output, [
        'Date',
        'Time',
        'Exchange No',
        'Old Bill No',
        'New Bill No',
        'Customer',
        'Items Count',
        'Old Qty',
        'Old Value',
        'New Qty',
        'New Value',
        'Difference',
        'Reason',
        'Cashier',
        'Status'
    ]);

// Add data
    foreach ($exchange_list as $exchange) {
        $difference = ($exchange['new_total'] ?? 0) - ($exchange['old_total'] ?? 0);
        $status = $exchange['new_bill_no'] ? 'Completed' : 'Pending';
        
        fputcsv($output, [
            date('Y-m-d', strtotime($exchange['created_at'])),
            date('H:i:s', strtotime($exchange['created_at'])),
            $exchange['exchange_no'],
            $exchange['old_bill_no'] ?? 'N/A',
            $exchange['new_bill_no'] ?? 'Pending',
            $exchange['old_customer_name'] ?? 'Walk-in',
            $exchange['item_count'] ?? 0,
            $exchange['total_old_qty'] ?? 0,
            number_format($exchange['old_total'] ?? 0, 2),
            $exchange['total_new_qty'] ?? 0,
            number_format($exchange['new_total'] ?? 0, 2),
            number_format($difference, 2),
            $exchange['reason'] ?? '',
            $exchange['user_name'] ?? '',
            $status
        ]);
    }

fclose($output);
    exit;
}
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Exchange Reports | DK BILLING</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.0/font/bootstrap-icons.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">
    <style>
        :root {
            --primary: #4361ee;
            --success: #06d6a0;
            --warning: #ffd166;
            --danger: #ef476f;
            --info: #118ab2;
            --dark: #1a1a2e;
            --light: #f8f9fa;
            --exchange: #fd7e14;
        }

body {
            background-color: #f5f7fa;
            font-family: 'Segoe UI', 'Roboto', 'Helvetica Neue', Arial, sans-serif;
        }

.content-section {
            padding: 30px;
        }

.premium-card {
            background: white;
            border-radius: 15px;
            box-shadow: 0 10px 30px rgba(0, 0, 0, 0.08);
            border: 1px solid rgba(253, 126, 20, 0.2);
            overflow: hidden;
            padding: 20px;
        }

.stat-card {
            border-radius: 12px;
            border: none;
            transition: transform 0.3s ease;
        }

.stat-card:hover {
            transform: translateY(-5px);
        }

.stat-icon {
            width: 48px;
            height: 48px;
            border-radius: 12px;
            display: flex;
            align-items: center;
            justify-content: center;
            font-size: 1.5rem;
        }

.badge-completed {
            background-color: #28a745;
            color: white;
            padding: 4px 8px;
            border-radius: 20px;
            font-size: 0.85rem;
        }

.badge-pending {
            background-color: #fd7e14;
            color: white;
            padding: 4px 8px;
            border-radius: 20px;
            font-size: 0.85rem;
        }

.exchange-diff-positive {
            color: #28a745;
            font-weight: bold;
        }

.exchange-diff-negative {
            color: #dc3545;
            font-weight: bold;
        }

.btn-exchange {
            background-color: #fd7e14;
            border-color: #fd7e14;
            color: white;
        }

.btn-exchange:hover {
            background-color: #e06b00;
            border-color: #e06b00;
            color: white;
        }

.old-value {
            color: #dc3545;
            font-weight: 500;
        }

.new-value {
            color: #28a745;
            font-weight: 500;
        }
    </style>
</head>

<body>
    <?php include('nav.php') ?>
    <div id="exchange-reports" class="content-section">
        <div class="row mb-4">
            <div class="col">
                <div class="d-flex justify-content-between align-items-center mb-4">
                    <div>
                        <h2 class="h3 mb-1">
                            <i class="bi bi-arrow-repeat text-warning me-2"></i> Exchange Reports
                        </h2>
                        <p class="text-muted mb-0">Track and manage product exchanges</p>
                    </div>
                    <div>
                        <a href="reports.php" class="btn btn-outline-primary me-2">
                            <i class="bi bi-graph-up me-2"></i> Back to Sales Reports
                        </a>
                        <a href="?export=csv&<?php echo http_build_query($_GET); ?>" class="btn btn-success">
                            <i class="bi bi-file-earmark-excel me-2"></i> Export CSV
                        </a>
                    </div>
                </div>

<div class="premium-card mb-4">
                    <div class="card-body">
                        <h5 class="card-title mb-3">
                            <i class="bi bi-funnel me-2"></i> Filter Exchange Reports
                        </h5>
                        <form method="GET" class="row g-3" id="filterForm">
                            <div class="col-md-3">
                                <label class="form-label">Quick Range</label>
                                <select class="form-select" name="time_range" id="timeRange" onchange="updateDateRange()">
                                    <option value="today" <?php echo $time_range == 'today' ? 'selected' : ''; ?>>Today</option>
                                    <option value="yesterday" <?php echo $time_range == 'yesterday' ? 'selected' : ''; ?>>Yesterday</option>
                                    <option value="this_week" <?php echo $time_range == 'this_week' ? 'selected' : ''; ?>>This Week</option>
                                    <option value="last_week" <?php echo $time_range == 'last_week' ? 'selected' : ''; ?>>Last Week</option>
                                    <option value="this_month" <?php echo $time_range == 'this_month' ? 'selected' : ''; ?>>This Month</option>
                                    <option value="last_month" <?php echo $time_range == 'last_month' ? 'selected' : ''; ?>>Last Month</option>
                                    <option value="last_30_days" <?php echo $time_range == 'last_30_days' ? 'selected' : ''; ?>>Last 30 Days</option>
                                    <option value="custom" <?php echo $time_range == 'custom' ? 'selected' : ''; ?>>Custom Range</option>
                                </select>
                            </div>

<div class="col-md-3">
                                <label class="form-label">Start Date</label>
                                <input type="date" class="form-control" name="start_date" value="<?php echo $start_date; ?>" id="startDate">
                            </div>

<div class="col-md-3">
                                <label class="form-label">End Date</label>
                                <input type="date" class="form-control" name="end_date" value="<?php echo $end_date; ?>" id="endDate">
                            </div>

<div class="col-md-3">
                                <label class="form-label">Exchange Status</label>
                                <select class="form-select" name="exchange_status">
                                    <option value="all" <?php echo $exchange_status == 'all' ? 'selected' : ''; ?>>All Exchanges</option>
                                    <option value="completed" <?php echo $exchange_status == 'completed' ? 'selected' : ''; ?>>Completed</option>
                                    <option value="pending" <?php echo $exchange_status == 'pending' ? 'selected' : ''; ?>>Pending</option>
                                </select>
                            </div>

<div class="col-12 text-end">
                                <button type="submit" class="btn btn-exchange">
                                    <i class="bi bi-search me-2"></i> Apply Filters
                                </button>
                                <a href="exchange_reports.php" class="btn btn-outline-secondary">
                                    <i class="bi bi-arrow-clockwise me-2"></i> Reset
                                </a>
                            </div>
                        </form>
                    </div>
                </div>

<div class="row mb-4">
                    <div class="col-md-3 mb-3">
                        <div class="card stat-card border-warning bg-light">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center">
                                    <div>
                                        <h6 class="text-muted mb-2">Total Exchanges</h6>
                                        <h3 class="mb-0"><?php echo $summary['total_exchanges'] ?? 0; ?></h3>
                                    </div>
                                    <div class="stat-icon bg-warning text-white">
                                        <i class="bi bi-arrow-repeat"></i>
                                    </div>
                                </div>
                                <p class="text-muted mb-0 small mt-2">
                                    Completed: <?php echo $summary['completed_exchanges'] ?? 0; ?> | 
                                    Pending: <?php echo $summary['pending_exchanges'] ?? 0; ?>
                                </p>
                            </div>
                        </div>
                    </div>

<div class="col-md-3 mb-3">
                        <div class="card stat-card border-danger bg-light">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center">
                                    <div>
                                        <h6 class="text-muted mb-2">Old Items Value</h6>
                                        <h3 class="mb-0 text-danger">- ₹<?php echo number_format($summary['total_old_value'] ?? 0, 2); ?></h3>
                                    </div>
                                    <div class="stat-icon bg-danger text-white">
                                        <i class="bi bi-box-arrow-left"></i>
                                    </div>
                                </div>
                                <p class="text-muted mb-0 small mt-2">
                                    Qty: <?php echo $summary['total_old_quantity'] ?? 0; ?> items
                                </p>
                            </div>
                        </div>
                    </div>

<div class="col-md-3 mb-3">
                        <div class="card stat-card border-success bg-light">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center">
                                    <div>
                                        <h6 class="text-muted mb-2">New Items Value</h6>
                                        <h3 class="mb-0 text-success">+ ₹<?php echo number_format($summary['total_new_value'] ?? 0, 2); ?></h3>
                                    </div>
                                    <div class="stat-icon bg-success text-white">
                                        <i class="bi bi-box-arrow-right"></i>
                                    </div>
                                </div>
                                <p class="text-muted mb-0 small mt-2">
                                    Qty: <?php echo $summary['total_new_quantity'] ?? 0; ?> items
                                </p>
                            </div>
                        </div>
                    </div>

<div class="col-md-3 mb-3">
                        <div class="card stat-card border-info bg-light">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center">
                                    <div>
                                        <h6 class="text-muted mb-2">Exchange Difference</h6>
                                        <?php $difference = ($summary['total_new_value'] ?? 0) - ($summary['total_old_value'] ?? 0); ?>
                                        <h3 class="mb-0 <?php echo $difference >= 0 ? 'text-success' : 'text-danger'; ?>">
                                            ₹<?php echo number_format($difference, 2); ?>
                                        </h3>
                                    </div>
                                    <div class="stat-icon bg-info text-white">
                                        <i class="bi bi-calculator"></i>
                                    </div>
                                </div>
                                <p class="text-muted mb-0 small mt-2">
                                    Avg: ₹<?php echo number_format($summary['avg_exchange_value'] ?? 0, 2); ?>
                                </p>
                            </div>
                        </div>
                    </div>
                </div>

<div class="premium-card mb-4">
                    <div class="card-body">
                        <h5 class="card-title mb-3">
                            <i class="bi bi-calendar-week me-2 text-warning"></i> Daily Exchange Summary
                        </h5>
                        <div class="table-responsive">
                            <table class="table table-sm table-hover">
                                <thead class="table-warning">
                                    <tr>
                                        <th>Date</th>
                                        <th>Exchanges</th>
                                        <th>Completed</th>
                                        <th>Pending</th>
                                        <th>Items</th>
                                        <th>Old Value</th>
                                        <th>New Value</th>
                                        <th>Difference</th>
                                        <th>Details</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if (empty($daily_data)): ?>
                                        <tr>
                                            <td colspan="9" class="text-center text-muted py-4">
                                                <i class="bi bi-arrow-repeat display-6 d-block mb-2"></i>
                                                No exchange data for selected period
                                            </td>
                                        </tr>
                                    <?php else: ?>
                                        <?php foreach ($daily_data as $day): 
                                            $day_diff = ($day['daily_new_value'] ?? 0) - ($day['daily_old_value'] ?? 0);
                                        ?>
                                            <tr>
                                                <td><?php echo date('d M Y', strtotime($day['exchange_date'])); ?></td>
                                                <td><?php echo $day['exchange_count']; ?></td>
                                                <td><span class="badge-completed"><?php echo $day['completed_count']; ?></span></td>
                                                <td><span class="badge-pending"><?php echo $day['pending_count']; ?></span></td>
                                                <td><?php echo $day['daily_items']; ?></td>
                                                <td class="old-value">- ₹<?php echo number_format($day['daily_old_value'] ?? 0, 2); ?></td>
                                                <td class="new-value">+ ₹<?php echo number_format($day['daily_new_value'] ?? 0, 2); ?></td>
                                                <td class="<?php echo $day_diff >= 0 ? 'exchange-diff-positive' : 'exchange-diff-negative'; ?>">
                                                    ₹<?php echo number_format($day_diff, 2); ?>
                                                </td>
                                                <td>
                                                    <button class="btn btn-sm btn-outline-warning" onclick="viewDayDetails('<?php echo $day['exchange_date']; ?>')">
                                                        <i class="bi bi-eye"></i>
                                                    </button>
                                                </td>
                                            </tr>
                                        <?php endforeach; ?>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>

<div class="premium-card">
    <div class="card-body">
        <div class="d-flex justify-content-between align-items-center mb-3">
            <h5 class="card-title mb-0">
                <i class="bi bi-list-check me-2 text-warning"></i> Detailed Exchange List
            </h5>
            <span class="text-muted">
                Showing <?php echo count($exchange_list); ?> exchanges
            </span>
        </div>

<div class="table-responsive">
            <table class="table table-hover" id="exchangesTable">
                <thead>
                    <tr>
                        <th>Date & Time</th>     
                        <th>Exchange No</th>      
                        <th>Old Bill</th>         
                        <th>New Bill</th>         
                        <th>Customer</th>         
                        <th>Items</th>            
                        <th>Old Value</th>        
                        <th>New Value</th>        
                        <th>Difference</th>       
                        <th>Cashier</th>          
                        <th>Actions</th>          
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($exchange_list)): ?>
                        <tr>
                            <td colspan="11" class="text-center text-muted py-4"> 
                                <i class="bi bi-arrow-repeat display-6 d-block mb-2"></i>
                                No exchanges found
                            </td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($exchange_list as $exchange): 
                            $difference = ($exchange['new_total'] ?? 0) - ($exchange['old_total'] ?? 0);
                            $status = $exchange['new_bill_no'] ? 'Completed' : 'Pending';
                            $status_class = $exchange['new_bill_no'] ? 'badge-completed' : 'badge-pending';
                        ?>
                            <tr>
                                
                                <td>
                                    <?php echo date('d M Y', strtotime($exchange['created_at'])); ?><br>
                                    <small class="text-muted"><?php echo date('h:i A', strtotime($exchange['created_at'])); ?></small>
                                </td>
                                
                                
                                <td>
                                    <strong class="text-warning"><?php echo $exchange['exchange_no']; ?></strong>
                                </td>
                                
                                
                                <td>
                                    <?php if ($exchange['old_bill_no']): ?>
                                        <a href="javascript:void(0)" onclick="viewBillDetails(<?php echo $exchange['old_bill_id']; ?>)">
                                            <?php echo $exchange['old_bill_no']; ?>
                                        </a>
                                    <?php else: ?>
                                        <span class="text-muted">N/A</span>
                                    <?php endif; ?>
                                </td>
                                
                                
                                <td>
                                    <?php if ($exchange['new_bill_no']): ?>
                                        <a href="javascript:void(0)" onclick="viewBillDetails(<?php echo $exchange['new_bill_id']; ?>)">
                                            <?php echo $exchange['new_bill_no']; ?>
                                        </a>
                                    <?php else: ?>
                                        <span class="badge-pending">Pending</span>
                                    <?php endif; ?>
                                </td>
                                
                                
                                <td>
                                    <?php echo htmlspecialchars($exchange['old_customer_name'] ?? 'Walk-in'); ?><br>
                                    <small class="text-muted"><?php echo $exchange['old_customer_mobile'] ?? ''; ?></small>
                                </td>
                                
                                
                                <td>
                                    <?php echo $exchange['item_count']; ?> items<br>
                                    <small class="text-muted">
                                        Old: <?php echo $exchange['total_old_qty']; ?> | 
                                        New: <?php echo $exchange['total_new_qty']; ?>
                                    </small>
                                </td>
                                
                                
                                <td class="old-value">
                                    - ₹<?php echo number_format($exchange['old_total'] ?? 0, 2); ?>
                                </td>
                                
                                
                                <td class="new-value">
                                    + ₹<?php echo number_format($exchange['new_total'] ?? 0, 2); ?>
                                </td>
                                
                                
                                <td class="<?php echo $difference >= 0 ? 'exchange-diff-positive' : 'exchange-diff-negative'; ?> fw-bold">
                                    ₹<?php echo number_format($difference, 2); ?>
                                </td>
                                
                                
                                <td><?php echo $exchange['user_name']; ?></td>
                                
                                
                                <td>
                                    <div class="btn-group btn-group-sm">
                                        <button class="btn btn-outline-warning" onclick="viewExchangeDetails(<?php echo $exchange['id']; ?>)" title="View Details">
                                            <i class="bi bi-eye"></i>
                                        </button>
                                       
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
            </div>
        </div>
    </div>

<div class="modal fade" id="exchangeDetailsModal" tabindex="-1" aria-hidden="true">
        <div class="modal-dialog modal-xl modal-dialog-centered">
            <div class="modal-content">
                <div class="modal-header bg-warning text-white">
                    <h5 class="modal-title">
                        <i class="bi bi-arrow-repeat me-2"></i> Exchange Details
                    </h5>
                    <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body" id="exchangeDetailsContent">
                    
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="button" class="btn btn-warning" onclick="printExchangeDetails()">
                        <i class="bi bi-printer me-2"></i> Print Exchange
                    </button>
                </div>
            </div>
        </div>
    </div>

<div class="modal fade" id="billDetailsModal" tabindex="-1" aria-hidden="true">
        <div class="modal-dialog modal-lg modal-dialog-centered">
            <div class="modal-content">
                <div class="modal-header bg-primary text-white">
                    <h5 class="modal-title"><i class="bi bi-receipt me-2"></i> Bill Details</h5>
                    <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body" id="billDetailsContent">
                    
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="button" class="btn btn-primary" onclick="printCurrentBill()">
                        <i class="bi bi-printer me-2"></i> Print Bill
                    </button>
                </div>
            </div>
        </div>
    </div>

<div class="modal fade" id="dayDetailsModal" tabindex="-1" aria-hidden="true">
        <div class="modal-dialog modal-xl modal-dialog-centered">
            <div class="modal-content">
                <div class="modal-header bg-info text-white">
                    <h5 class="modal-title">
                        <i class="bi bi-calendar-day me-2"></i> Day Details - <span id="dayDetailsDate"></span>
                    </h5>
                    <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body" id="dayDetailsContent">
                    
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                </div>
            </div>
        </div>
    </div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
    <script>
        // Initialize DataTable - FIXED: Properly initialize with correct configuration
// Initialize DataTable
$(document).ready(function () {
    try {
        if ($.fn.DataTable.isDataTable('#exchangesTable')) {
            $('#exchangesTable').DataTable().destroy();
        }
        
        $('#exchangesTable').DataTable({
            "pageLength": 25,
            "order": [[0, 'desc']],
            "language": {
                "search": "Search exchanges:",
                "lengthMenu": "Show _MENU_ exchanges",
                "info": "Showing _START_ to _END_ of _TOTAL_ exchanges",
                "infoEmpty": "No exchanges found",
                "infoFiltered": "(filtered from _MAX_ total exchanges)",
                "emptyTable": "No exchanges available",
                "zeroRecords": "No matching exchanges found"
            },
            "columnDefs": [
                { "orderable": false, "targets": [10] } // Changed from 11 to 10 (Actions column is the 11th column, index 10)
            ],
            "responsive": true,
            "autoWidth": false
        });
    } catch (error) {
        console.error('DataTable initialization error:', error);
    }
});

// Update date range based on quick range selection
        function updateDateRange() {
            const timeRange = document.getElementById('timeRange').value;
            const startDateInput = document.getElementById('startDate');
            const endDateInput = document.getElementById('endDate');

const today = new Date();
            let startDate = new Date();
            let endDate = new Date();

switch (timeRange) {
                case 'today':
                    startDate = today;
                    endDate = today;
                    break;
                case 'yesterday':
                    startDate = new Date(today);
                    startDate.setDate(today.getDate() - 1);
                    endDate = new Date(startDate);
                    break;
                case 'this_week':
                    startDate = new Date(today);
                    startDate.setDate(today.getDate() - today.getDay() + 1);
                    endDate = today;
                    break;
                case 'last_week':
                    startDate = new Date(today);
                    startDate.setDate(today.getDate() - today.getDay() - 6);
                    endDate = new Date(startDate);
                    endDate.setDate(startDate.getDate() + 6);
                    break;
                case 'this_month':
                    startDate = new Date(today.getFullYear(), today.getMonth(), 1);
                    endDate = today;
                    break;
                case 'last_month':
                    startDate = new Date(today.getFullYear(), today.getMonth() - 1, 1);
                    endDate = new Date(today.getFullYear(), today.getMonth(), 0);
                    break;
                case 'last_30_days':
                    startDate = new Date(today);
                    startDate.setDate(today.getDate() - 30);
                    endDate = today;
                    break;
                case 'custom':
                    return;
            }

startDateInput.value = startDate.toISOString().split('T')[0];
            endDateInput.value = endDate.toISOString().split('T')[0];

if (timeRange !== 'custom') {
                setTimeout(() => {
                    document.getElementById('filterForm').submit();
                }, 100);
            }
        }

// View exchange details
        function viewExchangeDetails(exchangeId) {
            document.getElementById('exchangeDetailsContent').innerHTML = `
                <div class="text-center py-5">
                    <div class="spinner-border text-warning" role="status">
                        <span class="visually-hidden">Loading...</span>
                    </div>
                    <p class="mt-2">Loading exchange details...</p>
                </div>
            `;

fetch(`get_exchange_details.php?id=${exchangeId}`)
                .then(response => response.text())
                .then(html => {
                    document.getElementById('exchangeDetailsContent').innerHTML = html;
                })
                .catch(error => {
                    document.getElementById('exchangeDetailsContent').innerHTML = `
                        <div class="alert alert-danger">
                            Error loading exchange details.
                        </div>
                    `;
                });

const modal = new bootstrap.Modal(document.getElementById('exchangeDetailsModal'));
            modal.show();
        }

// View bill details
        function viewBillDetails(billId) {
            document.getElementById('billDetailsContent').innerHTML = `
                <div class="text-center py-5">
                    <div class="spinner-border text-primary" role="status">
                        <span class="visually-hidden">Loading...</span>
                    </div>
                    <p class="mt-2">Loading bill details...</p>
                </div>
            `;

fetch(`get_bill_details.php?id=${billId}`)
                .then(response => response.text())
                .then(html => {
                    document.getElementById('billDetailsContent').innerHTML = html;
                    document.getElementById('billDetailsContent').dataset.billId = billId;
                })
                .catch(error => {
                    document.getElementById('billDetailsContent').innerHTML = `
                        <div class="alert alert-danger">
                            Error loading bill details.
                        </div>
                    `;
                });

const modal = new bootstrap.Modal(document.getElementById('billDetailsModal'));
            modal.show();
        }

// View day details
        function viewDayDetails(date) {
            document.getElementById('dayDetailsDate').textContent = date;
            document.getElementById('dayDetailsContent').innerHTML = `
                <div class="text-center py-5">
                    <div class="spinner-border text-info" role="status">
                        <span class="visually-hidden">Loading...</span>
                    </div>
                    <p class="mt-2">Loading day details...</p>
                </div>
            `;

fetch(`get_day_exchanges.php?date=${date}&shop_id=<?php echo $shop_id; ?>`)
                .then(response => response.text())
                .then(html => {
                    document.getElementById('dayDetailsContent').innerHTML = html;
                })
                .catch(error => {
                    document.getElementById('dayDetailsContent').innerHTML = `
                        <div class="alert alert-danger">
                            Error loading day details.
                        </div>
                    `;
                });

const modal = new bootstrap.Modal(document.getElementById('dayDetailsModal'));
            modal.show();
        }

// Print POS receipt
        function printPosReceipt(billId) {
            fetch(`get_bill_data.php?id=${billId}&format=json`)
                .then(response => response.json())
                .then(data => {
                    if (data.success) {
                        generateEnhancedPosReceipt(data.data);
                    } else {
                        alert('Could not load bill data');
                    }
                })
                .catch(error => {
                    console.error('Error:', error);
                    alert('Error loading bill details');
                });
        }

// Generate enhanced POS receipt
        function generateEnhancedPosReceipt(data) {
            const bill = data.bill;
            const items = data.items;
            
            const shopName = '<?php echo isset($shopDetails["shop_name"]) ? addslashes($shopDetails["shop_name"]) : "DK BILLING"; ?>';
            const shopAddress = '<?php echo isset($shopDetails["address"]) ? addslashes($shopDetails["address"]) : ""; ?>';
            const shopPhone = '<?php echo isset($shopDetails["phone"]) ? addslashes($shopDetails["phone"]) : ""; ?>';
            
            const billDate = new Date(bill.created_at);
            const dateStr = billDate.toLocaleDateString('en-IN');
            const timeStr = billDate.toLocaleTimeString('en-IN');
            
            let itemsHtml = '';
            items.forEach(item => {
                itemsHtml += `
                    <tr>
                        <td>${item.product_name}</td>
                        <td>${item.quantity}</td>
                        <td>₹${parseFloat(item.price).toFixed(2)}</td>
                        <td>₹${(item.quantity * item.price).toFixed(2)}</td>
                    </tr>
                `;
            });
            
            const receiptHTML = `
                <!DOCTYPE html>
                <html>
                <head>
                    <title>Bill - ${bill.bill_no}</title>
                    <style>
                        @media print {
                            @page { size: 80mm auto; margin: 0; }
                            body { width: 80mm; margin: 0; padding: 5px; font-family: 'Courier New', monospace; font-size: 11px; }
                        }
                        body { font-family: 'Courier New', monospace; width: 80mm; margin: 0 auto; padding: 5px; }
                        .header { text-align: center; margin-bottom: 10px; }
                        .shop-name { font-size: 16px; font-weight: bold; }
                        .bill-info { margin: 10px 0; }
                        table { width: 100%; border-collapse: collapse; }
                        th, td { padding: 3px 0; text-align: left; }
                        .total { font-weight: bold; border-top: 1px dashed #000; margin-top: 5px; padding-top: 5px; }
                        .footer { text-align: center; margin-top: 10px; font-size: 9px; }
                    </style>
                </head>
                <body onload="window.print(); setTimeout(() => { window.close(); }, 500);">
                    <div class="header">
                        <div class="shop-name">${shopName}</div>
                        <div>${shopAddress}</div>
                        <div>${shopPhone}</div>
                    </div>
                    
                    <div class="bill-info">
                        <div>Bill No: ${bill.bill_no}</div>
                        <div>Date: ${dateStr} ${timeStr}</div>
                        <div>Customer: ${bill.customer_name || 'Walk-in'}</div>
                    </div>
                    
                    <table>
                        <thead>
                            <tr>
                                <th>Item</th>
                                <th>Qty</th>
                                <th>Price</th>
                                <th>Total</th>
                            </tr>
                        </thead>
                        <tbody>
                            ${itemsHtml}
                        </tbody>
                    </table>
                    
                    <div class="total">
                        <div>Subtotal: ₹${parseFloat(bill.subtotal).toFixed(2)}</div>
                        <div>Discount: ₹${parseFloat(bill.discount_amount).toFixed(2)}</div>
                        <div>GST: ₹${parseFloat(bill.gst_amount).toFixed(2)}</div>
                        <div style="font-size: 14px;">TOTAL: ₹${parseFloat(bill.total_amount).toFixed(2)}</div>
                    </div>
                    
                    <div class="footer">
                        Thank you for your purchase!
                        <br>Powered by DK BILLING
                    </div>
                </body>
                </html>
            `;
            
            const printWindow = window.open('', '_blank', 'width=400,height=600');
            if (printWindow) {
                printWindow.document.write(receiptHTML);
                printWindow.document.close();
            }
        }

// Print exchange details
        function printExchangeDetails() {
            const content = document.getElementById('exchangeDetailsContent').innerHTML;
            
            const printHtml = `
                <!DOCTYPE html>
                <html>
                <head>
                    <title>Exchange Details</title>
                    <style>
                        body { font-family: Arial, sans-serif; margin: 20px; }
                        .header { text-align: center; margin-bottom: 30px; }
                        .shop-name { font-size: 24px; font-weight: bold; color: #fd7e14; }
                        table { width: 100%; border-collapse: collapse; margin: 20px 0; }
                        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                        th { background-color: #fd7e14; color: white; }
                        .old-value { color: #dc3545; }
                        .new-value { color: #28a745; }
                        .footer { margin-top: 30px; text-align: center; font-size: 12px; color: #666; }
                    </style>
                </head>
                <body onload="window.print(); setTimeout(() => { window.close(); }, 500);">
                    <div class="header">
                        <div class="shop-name"><?php echo isset($shopDetails['shop_name']) ? $shopDetails['shop_name'] : 'DK BILLING'; ?></div>
                        <div>Exchange Details</div>
                    </div>
                    ${content}
                    <div class="footer">
                        Powered by DK BILLING Software
                    </div>
                </body>
                </html>
            `;
            
            const printWindow = window.open('', '_blank', 'width=800,height=600');
            if (printWindow) {
                printWindow.document.write(printHtml);
                printWindow.document.close();
            }
        }

// Print current bill from modal
        function printCurrentBill() {
            const billId = document.getElementById('billDetailsContent').dataset.billId;
            if (billId) {
                printPosReceipt(billId);
            }
        }
    </script>
</body>

</html>

<?php
// Close database connection
if (isset($summary_result) && $summary_result) $summary_result->free();
if (isset($daily_result) && $daily_result) $daily_result->free();
if (isset($exchange_list_result) && $exchange_list_result) $exchange_list_result->free();
$conn->close();
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: exchange_reports.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation