<?php
include("db/cn.php");
include("comon/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 'Massive Seeds '; ?></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, tr{ font-weight:700; }
    </style>
</head>
<?php
// Fetch filter options
$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");

// Get filter inputs
$supplier = $_POST['supplier'] ?? '';
$company = $_POST['company'] ?? '';
$product = $_POST['product'] ?? '';
$customer_name = $_POST['customer_name'] ?? '';
$startDate = $_POST['startDate'] ?? '';
$endDate = $_POST['endDate'] ?? '';

// Build base filter string to reuse
$filter = "";
if ($supplier) $filter .= " AND supplier_name = '".mysqli_real_escape_string($connection,$supplier)."'";
if ($company) $filter .= " AND company = '".mysqli_real_escape_string($connection,$company)."'";
if ($product) $filter .= " AND product_name = '".mysqli_real_escape_string($connection,$product)."'";
if ($customer_name) $filter .= " AND customer_name = '".mysqli_real_escape_string($connection,$customer_name)."'";
if ($startDate && $endDate) $filter .= " AND date_time BETWEEN '$startDate' AND '$endDate'";

// Totals query for status = ''
$sum_sql = "SELECT SUM(product_quantity) AS total_quantity, SUM(net_amount) AS total_amount
FROM log_user_sales WHERE status = '' $filter";
$sum_result = mysqli_query($connection, $sum_sql);
$row = mysqli_fetch_assoc($sum_result);
$total_quantity = isset($row['total_quantity']) ? (float)$row['total_quantity'] : 0;
$total_amount = isset($row['total_amount']) ? (float)$row['total_amount'] : 0;

// Totals query for refunds (status = 'refund')
$refund_sql = "SELECT SUM(product_quantity) AS refund_quantity, SUM(net_amount) AS refund_amount
FROM log_user_sales WHERE status = 'refund' $filter";
$refund_result = mysqli_query($connection, $refund_sql);
$rrow = mysqli_fetch_assoc($refund_result);
$refund_quantity = isset($rrow['refund_quantity']) ? (float)$rrow['refund_quantity'] : 0;
$refund_amount = isset($rrow['refund_amount']) ? (float)$rrow['refund_amount'] : 0;

// Compute net values
$net_quantity = $total_quantity + $refund_quantity;
$net_amount = $total_amount - $refund_amount;

// Main query (list status = '', unchanged)
$sql = "SELECT * FROM log_user_sales WHERE status = '' $filter";
$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;">Sales Report</h3>
                <form method="post">
                    <div class="row">
                        <div class="col-md-3">
                            <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="<?= htmlspecialchars($row_customer['customer_name']); ?>" <?= ($customer_name==$row_customer['customer_name']?'selected':''); ?>><?= htmlspecialchars($row_customer['customer_name']); ?></option>
                                <?php } ?>
                            </select>
                        </div>
                        <div class="col-md-3">
                            <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="<?= htmlspecialchars($row_supplier_name['supplier']);?>" <?= ($supplier==$row_supplier_name['supplier']?'selected':''); ?>><?= htmlspecialchars($row_supplier_name['supplier']);?></option>
                                <?php } ?>
                            </select>
                        </div>
                        <div class="col-md-3">
                            <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="<?= htmlspecialchars($row_supplier_company['company']);?>" <?= ($company==$row_supplier_company['company']?'selected':''); ?>><?= htmlspecialchars($row_supplier_company['company']);?></option>
                                <?php } ?>
                            </select>
                        </div>
                        <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="<?= htmlspecialchars($row_products['product_name']);?>" <?= ($product==$row_products['product_name']?'selected':''); ?>><?= htmlspecialchars($row_products['product_name']);?></option>
                                <?php } ?>
                            </select>
                        </div>
                    </div>

                    <div class="row mt-3">
                        
                        <div class="col-md-3">
                            <label>Start Date:</label>
                            <input type="date" class="form-control" name="startDate" value="<?= htmlspecialchars($startDate); ?>">
                        </div>
                        <div class="col-md-3">
                            <label>End Date:</label>
                            <input type="date" class="form-control" name="endDate" value="<?= htmlspecialchars($endDate); ?>">
                        </div>
                        <div class=" col-md-4">
                            <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>
                            <button id="downloadPdf" type="button" class="btn btn-danger mt-4"><i class="fas fa-file fa-1x"></i> Download Pdf</button>
                            
                        </div>
                    </div>

                   <div id='report'>
                        <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>Date</th><th>Supplier</th><th>Company</th><th>Product Name</th><th>Total Quantity</th><th>Total Amount(Rs)</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php while($row = mysqli_fetch_array($rec_purchases)) { ?>
                                    <tr style="color:#000;">
                                        <td><?= htmlspecialchars($row['date_time']); ?></td>
                                        <td><?= htmlspecialchars($row['supplier_name']); ?></td>
                                        <td><?= htmlspecialchars($row['company']); ?></td>
                                        <td><?= htmlspecialchars($row['product_name']); ?></td>
                                        <td><?= htmlspecialchars($row['product_quantity']);?></td>
                                        <td><?= htmlspecialchars($row['net_amount']); ?></td>
                                    </tr>
                                <?php } ?>
                            </tbody>
                        </table>
                    </div>

                    <table class="table bg-white mt-3">
                        <tfoot>
                            <tr><th>Total Quantity :</th><td><?= number_format($total_quantity, 2); ?></td></tr>
                            <tr><th>Total Amount:</th><td>Rs <?= number_format($total_amount, 2); ?></td></tr>
                 
                            <tr><th>Total Quantity Refund :</th><td><?= number_format($refund_quantity, 2); ?></td></tr>
                            <tr><th>Total Amount Refund:</th><td>Rs <?= number_format($refund_amount, 2); ?></td></tr>
                       
                
                            <tr><th>Net Quantity :</th><td><?= number_format($net_quantity, 2); ?></td></tr>
                            <tr><th>Net Amount:</th><td>Rs <?= number_format($net_amount, 2); ?></td></tr>
                        </tfoot>
                    </table>

                   </div>
                </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 src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.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');
});

document.getElementById("downloadPdf").addEventListener("click", function () {
    const { jsPDF } = window.jspdf;
    const report = document.getElementById("report");

    const now = new Date();
    const formattedDateTime = now.toLocaleDateString() + " " + now.toLocaleTimeString();

    html2canvas(report, { scale: 2 }).then(canvas => {
        const imgData = canvas.toDataURL("image/png");
        const pdf = new jsPDF("p", "mm", "a4");
        const pdfWidth = pdf.internal.pageSize.getWidth();
        const pdfHeight = (canvas.height * pdfWidth) / canvas.width;

        pdf.addImage(imgData, "PNG", 0, 30, pdfWidth, pdfHeight);
        pdf.setFont("helvetica", "bold");
        pdf.setFontSize(14);
        pdf.text("Sales Report", pdfWidth / 2, 12, { align: "center" });
        pdf.setFontSize(10);
        pdf.setFont("helvetica", "normal");
        pdf.text(`Printed: ${formattedDateTime}`, pdfWidth - 10, 20, { align: "right" });

        pdf.save("Sales-Report.pdf");
    });
});
</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>
