<?php
session_start();
include "../db.php";
include "include/php-functions.php";

// Load the XLSXWriter library (adjust the path as needed)
require_once "PHP_XLSXWriter/xlsxwriter.class.php"; // ensure this path is correct

header('Content-disposition: attachment; filename="product_list.xlsx"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

// Initialize XLSXWriter and set column headers
$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', [
    'Product Name' => 'string',
    'Category' => 'string',
    'Franchisee' => 'string',
    'Sub-Franchisee' => 'string',
    'Employee' => 'string',
    'Created Date' => 'datetime',
    'Status' => 'string',
    'Charges' => 'string',
    'Discount' => 'string',
    'Paid' => 'string',
    'Remaining' => 'string'
]);

// Fetch data with applied filters
$start = 0;
$conditions = [];
$AndWhere = '';
include("include/productListFilter.php");

// Fetch data with applied filters from session
$conditions = $_SESSION['export_conditions'] ?? []; // retrieve conditions from session
$AndWhere = '';
if (count($conditions) > 0) {
    $AndWhere = 'WHERE ' . implode(' AND ', $conditions);
}

$_SESSION['export_conditions'] = $conditions; // Store conditions for export use
    
$total_pricing_sum = 0; 
$total_payment_sum = 0;  
$total_discount = 0;

include("include/calculationProductListAmount.php");

// Fetch and write data rows
$sql = "SELECT * FROM product $AndWhere ORDER BY pid DESC";
$res = mysqli_query($con, $sql);

while ($rows = mysqli_fetch_assoc($res)) {
    // Fetch names and details with error-checking
    $client_name = fetchUserName($con, $rows['pClient']);
    $fran_name = fetchUserName($con, $rows['pFranchiee']);
    $subFran_name = fetchUserName($con, $rows['pSubFranchiee']);
    $employee_name = !empty($rows['pEmployee']) ? fetchUserName($con, $rows['pEmployee']) : '';
    
    // Fetch service details
    $serviceDetails = fetchServiceDetails($con, $rows["product"]);
    $product = $serviceDetails['product'] ?? '';
    $pricing = floatval($serviceDetails['pricing'] ?? 0);
    $total_payment = floatval(fetchTotalPayment($con, $rows["pid"]));
    $pCat = fetchCategoryName($con, $rows["pCat"]);
    $pDiscount = floatval($rows["pDiscount"] ?? 0);
    $remaining = $pricing - $pDiscount - $total_payment;

    $pStatus = $rows["pStatus"];
    $statusName = '';

    if ($pStatus == 0) {
        $statusName = 'Pending';
    } elseif ($pStatus == 1) {
        $statusName = 'Ongoing';
    } elseif ($pStatus == 2) {
        $statusName = 'Completed';
    } elseif ($pStatus == 3) {
        $statusName = 'Postponed';
    } elseif ($pStatus == 4) {
        $statusName = 'Rejected';
    } else {
        $statusName = 'Pending';
    }

    // Format the creation date to ensure compatibility with Excel
    $createdDate = date('Y-m-d H:i:s', strtotime($rows["pCreateDate"]));

    // Write data row to the Excel sheet
    $writer->writeSheetRow('Sheet1', [
        $product,
        $pCat,
        $fran_name,
        $subFran_name,
        $employee_name,
        $createdDate,
        $statusName,
        $pricing,
        $pDiscount,
        $total_payment,
        $remaining
    ]);
}

// Write summary rows at the end of the data rows
$writer->writeSheetRow('Sheet1', ['']);
$writer->writeSheetRow('Sheet1', ['Summary Report']);
$writer->writeSheetRow('Sheet1', ['Total Service Charges', $_SESSION["amount"]]);
$writer->writeSheetRow('Sheet1', ['Total Discount', $total_discount]);
$writer->writeSheetRow('Sheet1', ['Total Received', $_SESSION["recived"]]);
$writer->writeSheetRow('Sheet1', ['Pending', $_SESSION["amount"] - $_SESSION["recived"] - $total_discount]);

// Output the Excel file
$writer->writeToStdOut();
exit();
?>