<?php
include("db/cn.php");
include("common/tx.php");
ob_start();
if(isset($_SESSION['user_name']) && isset($_SESSION['user_role']) && isset($_SESSION['outlet_name']) && isset($_SESSION['outlet_address'])) {
    $userName = $_SESSION['user_name'];
    $userRole = $_SESSION['user_role'];
    $outlet_address = $_SESSION['outlet_address'];
    $outlet_name = $_SESSION['outlet_name'];
}
ob_end_flush();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title><?php echo $tx; ?></title>
    <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Quicksand:wght@200;300;400;500;600;700&display=swap" rel="stylesheet">
    <link href="../css/sb-admin-2.min.css" rel="stylesheet">
    <link href="../css/sb-admin-2.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        body { font-family: 'Quicksand', sans-serif; }
        .chart-container { position: relative; background: rgba(255, 255, 255, 0.9); border-radius: 10px; padding: 15px; box-shadow: 0 0 10px rgba(0,0,0,0.2); }
        canvas { max-width: 100%; height: auto; }
        .bg-gradient-primary{ background:#0A4657; color:#fff; }
        .btn-primary{ background:#0A4657; color:#fff; border:1px #0A4657 solid; }
        .btn-primary:hover{ background:#fff; border:1px #0A4657 solid; color:#0A4657; }
        .fa-download:hover{ color:#953E39; }
        #table-container-wrapper{ max-height:460px; overflow-y:auto; }
        tr{ font-weight:500; }
        .form-control,.btn{ font-weight:700; }
    </style>
</head>
<?php

$rec_customer_names = mysqli_query($connection, "SELECT DISTINCT customer_name FROM log_user_sales");
$rec_suplier_name = mysqli_query($connection, "SELECT DISTINCT supplier FROM products");
$rec_suplier_company = mysqli_query($connection, "SELECT DISTINCT company FROM products");
$rec_products = mysqli_query($connection, "SELECT DISTINCT product_name FROM products");

$supplier = $_POST['supplier'] ?? '';
$company = $_POST['company'] ?? '';
$product = $_POST['product'] ?? '';
$customer_name = $_POST['customer_name'] ?? '';
$startDate = $_POST['startDate'] ?? '';
$endDate = $_POST['endDate'] ?? '';

$sql = "SELECT order_id, SUM(product_quantity) AS total_quantity, SUM(net_amount) AS total_amount1
FROM log_user_sales WHERE status = 'refund'";
if ($supplier) $sql .= " AND supplier_name = '".mysqli_real_escape_string($connection,$supplier)."'";
if ($company) $sql .= " AND company = '".mysqli_real_escape_string($connection,$company)."'";
if ($product) $sql .= " AND product_name = '".mysqli_real_escape_string($connection,$product)."'";
if ($customer_name) $sql .= " AND customer_name = '".mysqli_real_escape_string($connection,$customer_name)."'";
if ($startDate && $endDate) $sql .= " AND date_time BETWEEN '$startDate' AND '$endDate'";

$sum_result = mysqli_query($connection, $sql);
$row = mysqli_fetch_assoc($sum_result);
$total_quantity = $row['total_quantity'] ?? 0;
$total_amount = $row['total_amount1'] ?? 0;

$sql = "
SELECT 
    order_id,
    supplier_name,
    company,
    product_name,
    SUM(product_quantity) AS total_quantity,
    SUM(net_amount) AS total_amount,
    MAX(date_time) AS refund_date
FROM log_user_sales
WHERE status = 'refund'
";
if ($supplier) $sql .= " AND supplier_name = '".mysqli_real_escape_string($connection,$supplier)."'";
if ($company) $sql .= " AND company = '".mysqli_real_escape_string($connection,$company)."'";
if ($product) $sql .= " AND product_name = '".mysqli_real_escape_string($connection,$product)."'";
if ($customer_name) $sql .= " AND customer_name = '".mysqli_real_escape_string($connection,$customer_name)."'";
if ($startDate && $endDate) $sql .= " AND date_time BETWEEN '$startDate' AND '$endDate'";

$sql .= " GROUP BY order_id ORDER BY refund_date DESC";
$rec_purchases = mysqli_query($connection, $sql);
?>
<body id="page-top">
<div id="wrapper">
    <?php include("common/sd.php"); ?>
    <div id="content-wrapper" class="d-flex flex-column">
        <div id="content">
            <nav class="navbar navbar-expand navbar-light bg-white topbar mb-4 static-top shadow">
                <button id="sidebarToggleTop" class="btn btn-link d-md-none rounded-circle mr-3"><i class="fa fa-bars"></i></button>
                <?php include('common/log.php'); ?>
            </nav>

            <div class="container-fluid mt-5">
                <h3 style="color:#060606;font-weight:700;">Refund Items Report</h3>
                <form method="post">
                    <div class="row">
                        <div class="col-md-4">
                            <label>Customer Name:</label>
                            <select class="form-control" name="customer_name">
                                <option value="">Select Customer</option>
                                <?php while ($row_customer = mysqli_fetch_array($rec_customer_names)) { ?>
                                    <option value="<?= $row_customer['customer_name']; ?>" <?= ($customer_name==$row_customer['customer_name']?'selected':''); ?>><?= $row_customer['customer_name']; ?></option>
                                <?php } ?>
                            </select>
                        </div>
                        <div class="col-md-4">
                            <label>Supplier Name:</label>
                            <select class="form-control" name="supplier">
                                <option value="">Select Supplier</option>
                                <?php while($row_supplier_name = mysqli_fetch_array($rec_suplier_name)){ ?>
                                    <option value="<?= $row_supplier_name['supplier'];?>" <?= ($supplier==$row_supplier_name['supplier']?'selected':''); ?>><?= $row_supplier_name['supplier'];?></option>
                                <?php } ?>
                            </select>
                        </div>
                        <div class="col-md-4">
                            <label>Supplier Company:</label>
                            <select class="form-control" name="company">
                                <option value="">Select Company</option>
                                <?php while($row_supplier_company = mysqli_fetch_array($rec_suplier_company)){ ?>
                                    <option value="<?= $row_supplier_company['company'];?>" <?= ($company==$row_supplier_company['company']?'selected':''); ?>><?= $row_supplier_company['company'];?></option>
                                <?php } ?>
                            </select>
                        </div>
                    </div>

                    <div class="row mt-3">
                        <div class="col-md-3">
                            <label>Product Name:</label>
                            <select class="form-control" name="product">
                                <option value="">Select Product</option>
                                <?php while($row_products = mysqli_fetch_array($rec_products)){ ?>
                                    <option value="<?= $row_products['product_name'];?>" <?= ($product==$row_products['product_name']?'selected':''); ?>><?= $row_products['product_name'];?></option>
                                <?php } ?>
                            </select>
                        </div>
                        <div class="col-md-3">
                            <label>Start Date:</label>
                            <input type="date" class="form-control" name="startDate" value="<?= $startDate; ?>">
                        </div>
                        <div class="col-md-3">
                            <label>End Date:</label>
                            <input type="date" class="form-control" name="endDate" value="<?= $endDate; ?>">
                        </div>
                        <div class="col-md-3">
                            <button type="submit" class="btn btn-dark mt-4" name="filter">Filter</button>
                            <button id="downloadExcel" type="button" class="btn btn-dark mt-4">Download</button>
                            <a href="report_s.php" class="btn btn-dark mt-4"><i class="fas fa-sync-alt"></i></a>
                        </div>
                    </div>

                    <div class="col-sm-12 mt-3" id="table-container-wrapper">
                        <table id="patientTable" class="table table-striped table-hover text-center table-sm bg-white">
                            <thead>
                                <tr class="bg-dark text-white">
                                    <th>Order ID</th>
                                    <th>Date</th>
                                    <th>Supplier</th>
                                    <th>Company</th>
                                    <th>Total Quantity</th>
                                    <th>Total Amount (Rs)</th>
                                    <th>View Details</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php while($row = mysqli_fetch_array($rec_purchases)) { ?>
                                    <tr style="color:#000;">
                                        <td><strong><?= $row['order_id']; ?></strong></td>
                                        <td><?= $row['refund_date']; ?></td>
                                        <td><?= $row['supplier_name']; ?></td>
                                        <td><?= $row['company']; ?></td>
                                        <td><?= $row['total_quantity']; ?></td>
                                        <td><?= number_format($row['total_amount'], 2); ?></td>
                                        <td>
                                            <a href="refund_invoice_detail.php?order_id=<?= $row['order_id']; ?>" 
                                               class="btn btn-dark btn-sm">
                                               <i class="fas fa-eye"></i> View
                                            </a>
                                        </td>
                                    </tr>
                                <?php } ?>
                            </tbody>
                        </table>
                    </div>
                    <table class="table bg-white mt-3">
                        <tfoot>
                            <tr><th>Total Quantity :</th><td><?= $total_quantity; ?></td></tr>
                            <tr><th>Total Amount:</th><td>Rs <?= $total_amount; ?></td></tr>
                        </tfoot>
                    </table>
                </form>
            </div>
            </div>
            <?php include("common/main_ft.php"); ?>
        </div>
    </div>
</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.9/xlsx.full.min.js"></script>
<script>
document.getElementById('downloadExcel').addEventListener('click', function() {
    var table = document.getElementById('patientTable');
    var clone = table.cloneNode(true);
    var rows = clone.querySelectorAll('tr');
    rows.forEach(function(row) {
        if (row.style.display === 'none' || row.innerText.trim() === '') {
            row.remove();
        }
    });
    var workbook = XLSX.utils.table_to_book(clone, { sheet: "Sales" });
    XLSX.writeFile(workbook, 'Sales_data.xlsx');
});
</script>
<script src="../vendor/jquery/jquery.min.js"></script>
<script src="../vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
<script src="../vendor/jquery-easing/jquery.easing.min.js"></script>
<script src="../js/sb-admin-2.min.js"></script>
</body>
</html>