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

// Load the XLSXWriter library
require_once "../PHP_XLSXWriter/xlsxwriter.class.php";

// Set headers for file download
header('Content-Disposition: attachment; filename="franchisee_payment_list.xlsx"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

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

// Initialize variables
$conditions = [];
$params = [];
$paramTypes = "";

// Validate and apply filters
if (!empty($_GET['FirstFilterValue'])) {
    $stmt = $con->prepare("SELECT uid FROM users WHERE uname = ? AND utype = 'Franchiee'");
    $stmt->bind_param("s", $_GET['FirstFilterValue']);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $conditions[] = "p_franchiee = ?";
        $params[] = $row['uid'];
        $paramTypes .= "i";
    }
    $stmt->close();
}

if (!empty($_GET['SecondFilterValue'])) {
    $stmt = $con->prepare("SELECT uid FROM users WHERE uname = ? AND utype = 'Client'");
    $stmt->bind_param("s", $_GET['SecondFilterValue']);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $conditions[] = "p_client = ?";
        $params[] = $row['uid'];
        $paramTypes .= "i";
    }
    $stmt->close();
}

if (!empty($_GET['thirdValue'])) {
    $stmt = $con->prepare("SELECT uid FROM users WHERE uname = ? AND utype = 'subFranchiee'");
    $stmt->bind_param("s", $_GET['thirdValue']);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $conditions[] = "p_subFranchiee = ?";
        $params[] = $row['uid'];
        $paramTypes .= "i";
    }
    $stmt->close();
}

if (!empty($_GET['StartDate']) && !empty($_GET['EndDate'])) {
    $startDate = DateTime::createFromFormat('Y-m-d', $_GET['StartDate']);
    $endDate = DateTime::createFromFormat('Y-m-d', $_GET['EndDate']);
    if ($startDate && $endDate) {
        $conditions[] = "DATE(p_createDate) BETWEEN ? AND ?";
        $params[] = $startDate->format('Y-m-d');
        $params[] = $endDate->format('Y-m-d');
        $paramTypes .= "ss";
    } else {
        echo "Invalid date format.";
        exit();
    }
}

// Build WHERE clause
$whereClause = count($conditions) > 0 ? 'WHERE ' . implode(' AND ', $conditions) : '';

// Fetch all franchisees using prepared statement
$query = "SELECT DISTINCT p_franchiee FROM payment $whereClause";
$stmt = $con->prepare($query);

// Bind parameters if conditions exist
if (count($params) > 0) {
    $stmt->bind_param($paramTypes, ...$params);
}

$stmt->execute();
$franchisees_result = $stmt->get_result();

while ($franchisee_row = $franchisees_result->fetch_assoc()) {
    $franchisee_id = $franchisee_row['p_franchiee'];

    // Fetch franchisee name
    $franchisee_name_sql = "SELECT uname FROM users WHERE uid = ?";
    $stmt = $con->prepare($franchisee_name_sql);
    $stmt->bind_param("s", $franchisee_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $franchisee_name = $result->fetch_assoc()['uname'] ?? 'Unknown Franchisee';
    $stmt->close();

    // Write franchisee heading
    $writer->writeSheetRow('Sheet1', ['Franchisee: ' . $franchisee_name]);
    $subFranchisee_id = '0';
    // Fetch franchisee payments
    $franchisee_payments_sql = "SELECT * FROM payment WHERE p_franchiee = ? AND p_subFranchiee = ?";
    $stmt = $con->prepare($franchisee_payments_sql);
    $stmt->bind_param("ss", $franchisee_id, $subFranchisee_id);
    $stmt->execute();
    $franchisee_payments_result = $stmt->get_result();

    $total_payment = 0;

    while ($payment_row = $franchisee_payments_result->fetch_assoc()) {
        // Fetch product and category details
        // Fetch product name and category
        $productId_sql = $con->prepare("SELECT product FROM product WHERE pid = ?");
        $productId_sql->bind_param("s", $rows['p_forProduct']);
        $productId_sql->execute();
        $productId_result = $productId_sql->get_result();
        if ($productId_row = $productId_result->fetch_assoc()) {
            $productId = $productId_row['product'];
        }
        $productId_sql->close();

        $product_sql = $con->prepare("SELECT data_title, data_subCategory FROM data WHERE data_sl_no = ?");
        $product_sql->bind_param("s", $productId);
        $product_sql->execute();
        $product_result = $product_sql->get_result();
        if ($product_row = $product_result->fetch_assoc()) {
            $product = $product_row['data_title'];
            $cat_id = $product_row['data_subCategory'];
        }
        $product_sql->close();

        $cat_sql = $con->prepare("SELECT cname FROM category WHERE cid = ?");
        $cat_sql->bind_param("s", $cat_id);
        $cat_sql->execute();
        $cat_result = $cat_sql->get_result();
        if ($cat_row = $cat_result->fetch_assoc()) {
            $category  = $cat_row['cname'];
        }
        $cat_sql->close();

        $writer->writeSheetRow('Sheet1', [
            $franchisee_name,
            $product ?? '',
            $category ?? '',
            $employee_name ?? '',
            $payment_row['p_createDate'],
            $payment_row['p_transectionStatus'],
            $payment_row['payment']
        ]);

        $total_payment += $payment_row['payment'];
    }
    $stmt->close();

    // Calculate GST and commission
    $gst_amount = ($total_payment * 18) / 100;
    $total_after_gst = $total_payment - $gst_amount;
    $commission_rate = $total_after_gst > 5000 ? 0.25 : 0.20;
    $commission_amount = $total_after_gst * $commission_rate;
    $final_amount = $total_after_gst - $commission_amount;

    // Write summary for franchisee
    $writer->writeSheetRow('Sheet1', ['']);
    $writer->writeSheetRow('Sheet1', ['Summary for Franchisee: ' . $franchisee_name]);
    $writer->writeSheetRow('Sheet1', ['Total Received', $total_payment]);
    $writer->writeSheetRow('Sheet1', ['GST Amount (18%)', $gst_amount]);
    $writer->writeSheetRow('Sheet1', ['Total After GST', $total_after_gst]);
    $writer->writeSheetRow('Sheet1', ['Commission (' . ($commission_rate * 100) . '%)', $commission_amount]);
    $writer->writeSheetRow('Sheet1', ['Total After Commission', $final_amount]);
    $writer->writeSheetRow('Sheet1', ['']);
    $writer->writeSheetRow('Sheet1', ['']);

    // Fetch sub-franchisees for this franchisee
    $sub_franchisees_sql = "SELECT DISTINCT p_subFranchiee FROM payment WHERE p_franchiee = ?";
    $stmt = $con->prepare($sub_franchisees_sql);
    $stmt->bind_param("s", $franchisee_id);
    $stmt->execute();
    $sub_franchisees_result = $stmt->get_result();

    while ($sub_franchisee_row = $sub_franchisees_result->fetch_assoc()) {
        $sub_franchisee_id = $sub_franchisee_row['p_subFranchiee'];
        if($sub_franchisee_id != 0){

            // Fetch sub-franchisee name
            $sub_franchisee_name_sql = "SELECT uname FROM users WHERE uid = ?";
            $stmt = $con->prepare($sub_franchisee_name_sql);
            $stmt->bind_param("s", $sub_franchisee_id);
            $stmt->execute();
            $result = $stmt->get_result();
            $sub_franchisee_name = $result->fetch_assoc()['uname'] ?? 'Unknown Sub-Franchisee';
            $stmt->close();

            // Write sub-franchisee heading
            $writer->writeSheetRow('Sheet1', ['Sub-Franchisee: ' . $sub_franchisee_name]);

            // Fetch sub-franchisee payments
            $sub_franchisee_payments_sql = "SELECT * FROM payment WHERE p_subFranchiee = ?";
            $stmt = $con->prepare($sub_franchisee_payments_sql);
            $stmt->bind_param("s", $sub_franchisee_id);
            $stmt->execute();
            $sub_franchisee_payments_result = $stmt->get_result();

            $sub_total_payment = 0;

            while ($payment_row = $sub_franchisee_payments_result->fetch_assoc()) {
                // Fetch product and category details
                // Fetch product name and category
                $productId_sql = $con->prepare("SELECT product FROM product WHERE pid = ?");
                $productId_sql->bind_param("s", $rows['p_forProduct']);
                $productId_sql->execute();
                $productId_result = $productId_sql->get_result();
                if ($productId_row = $productId_result->fetch_assoc()) {
                    $productId = $productId_row['product'];
                }
                $productId_sql->close();

                $product_sql = $con->prepare("SELECT data_title, data_subCategory FROM data WHERE data_sl_no = ?");
                $product_sql->bind_param("s", $productId);
                $product_sql->execute();
                $product_result = $product_sql->get_result();
                if ($product_row = $product_result->fetch_assoc()) {
                    $product = $product_row['data_title'];
                    $cat_id = $product_row['data_subCategory'];
                }
                $product_sql->close();

                $cat_sql = $con->prepare("SELECT cname FROM category WHERE cid = ?");
                $cat_sql->bind_param("s", $cat_id);
                $cat_sql->execute();
                $cat_result = $cat_sql->get_result();
                if ($cat_row = $cat_result->fetch_assoc()) {
                    $category  = $cat_row['cname'];
                }
                $cat_sql->close();

                $writer->writeSheetRow('Sheet1', [
                    $sub_franchisee_name,
                    $product ?? '',
                    $category ?? '',
                    $employee_name ?? '',
                    $payment_row['p_createDate'],
                    $payment_row['p_transectionStatus'],
                    $payment_row['payment']
                ]);

                $sub_total_payment += $payment_row['payment'];
            }
            $stmt->close();

            // Calculate GST, commission, and additional 5% deduction
            $sub_gst_amount = ($sub_total_payment * 18) / 100;
            $sub_total_after_gst = $sub_total_payment - $sub_gst_amount;
            $sub_commission_amount = $sub_total_after_gst * $commission_rate;
            $sub_additional_deduction = $sub_total_after_gst * 0.05;
            $sub_final_amount = "";

            // Write summary for sub-franchisee
            $writer->writeSheetRow('Sheet1', ['']);
            $writer->writeSheetRow('Sheet1', ['Summary for Sub-Franchisee: ' . $sub_franchisee_name]);
            $writer->writeSheetRow('Sheet1', ['Total Received', $sub_total_payment]);
            $writer->writeSheetRow('Sheet1', ['GST Amount (18%)', $sub_gst_amount]);
            $writer->writeSheetRow('Sheet1', ['Total After GST', $sub_total_after_gst]);
            $writer->writeSheetRow('Sheet1', ['Commission (' . ($commission_rate * 100) . '%)', $sub_commission_amount]);
            $writer->writeSheetRow('Sheet1', ['Additional Deduction (5%)', $sub_additional_deduction]);
            $writer->writeSheetRow('Sheet1', ['Total After Commission', $final_amount]);
            $writer->writeSheetRow('Sheet1', ['']);
            $writer->writeSheetRow('Sheet1', ['']);
        }
    }
}

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