<?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();