<?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="work_activity_list.xlsx"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

// Initialize XLSXWriter and set column headers
$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', [
    'Course Name' => 'string',
    'Course Department' => 'string',
    'College Name' => 'string',
    'Associate' => 'string',
    'Sub Associate' => 'string',
    'Employee' => 'string',
    'Created Date' => 'datetime',
    'Status' => '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'] ?? [];
$AndWhere = count($conditions) > 0 ? '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
    $fran_name = fetchUserName($con, $rows['pFranchiee']);
    $subFran_name = fetchUserName($con, $rows['pSubFranchiee']);
    $employee_name = !empty($rows['pEmployee']) ? fetchUserName($con, $rows['pEmployee']) : '';

    // Fetch service details
    $clg_name = ''; // Default value
    $course_name = ''; // Default value
    $pCat = ''; // Default value

    $clg_sql = mysqli_query($con, "SELECT uname FROM users WHERE uid = '{$rows["pCollege"]}'");
    if ($clg_row = mysqli_fetch_assoc($clg_sql)) {
        $clg_name = $clg_row["uname"];
    }

    $course_sql = mysqli_query($con, "SELECT course_name FROM clg_course WHERE sl_no = '{$rows["product"]}' LIMIT 1");
    if ($course_row = mysqli_fetch_assoc($course_sql)) {
        $course_name = $course_row["course_name"];
    }

    $cat_sql = mysqli_query($con, "SELECT cat_name FROM clg_course_cat WHERE sl_no = '{$rows["pCat"]}' LIMIT 1");
    if ($cat_row = mysqli_fetch_assoc($cat_sql)) {
        $pCat = $cat_row["cat_name"];
    }

    // Determine Status Name
    $statusList = ['Pending', 'Ongoing', 'Completed', 'Postponed', 'Rejected'];
    $pStatus = $rows["pStatus"];
    $statusName = $statusList[$pStatus] ?? 'Pending';

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

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

// Write summary rows at the end of the data rows
$writer->writeSheetRow('Sheet1', ['']);

// Ensure output buffer is clean before sending the file
ob_clean();
$writer->writeToStdOut();
exit();
?>