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


// Get the filter values from AJAX request
$load_filter_flag = isset($_POST['load_filter_flag']) ? $_POST['load_filter_flag'] : 0;
$FirstFilterValue = isset($_POST['FirstFilterValue']) ? mysqli_real_escape_string($con, $_POST['FirstFilterValue']) : '';
$SecondFilterValue = isset($_POST['SecondFilterValue']) ? mysqli_real_escape_string($con, $_POST['SecondFilterValue']) : '';
$ThirdFilterValue = isset($_POST['ThirdFilterValue']) ? mysqli_real_escape_string($con, $_POST['ThirdFilterValue']) : '';
$StatusValue = isset($_POST['StatusValue']) ? mysqli_real_escape_string($con, $_POST['StatusValue']) : ''; // Ensure StatusValue is defined
$CategoryValue = isset($_POST['CategoryValue']) ? mysqli_real_escape_string($con, $_POST['CategoryValue']) : '';
$StartDate = isset($_POST['StartDate']) ? mysqli_real_escape_string($con, $_POST['StartDate']) : '';
$EndDate = isset($_POST['EndDate']) ? mysqli_real_escape_string($con, $_POST['EndDate']) : '';

$emp_id = $_SESSION["uid"];

// Initialize conditions array
$conditions = [];

// if (!empty($StatusValue)) {
    $conditions[] = "pStatus = '$StatusValue'";
// }

if (!empty($CategoryValue)) {
    $conditions[] = "pCat = '$CategoryValue'";
}

if (!empty($StartDate) && !empty($EndDate)) {
    $conditions[] = "pCreateDate BETWEEN '$StartDate' AND '$EndDate'";
}

// Add employee filter and any other filters
$conditions[] = "pEmployee = '$emp_id'";

// Append custom filters if needed
if (!empty($FirstFilterValue)) {
    $conditions[] = "custom_field_1 = '$FirstFilterValue'";
}
if (!empty($SecondFilterValue)) {
    $conditions[] = "custom_field_2 = '$SecondFilterValue'";
}
if (!empty($ThirdFilterValue)) {
    $conditions[] = "custom_field_3 = '$ThirdFilterValue'";
}

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

// Fetch and calculate totals
$total_pricing_sum = 0;
$total_payment_sum = 0;
$total_discount = 0;

$discount_sql = mysqli_query($con, "SELECT SUM(pDiscount) AS total_discount FROM product $AndWhere");
if ($discount_row = mysqli_fetch_assoc($discount_sql)) {
    $total_discount = !is_null($discount_row['total_discount']) ? $discount_row['total_discount'] : 0;
}

$pricing_sql = mysqli_query($con, "SELECT SUM(data_seo_tag) AS total_pricing FROM data WHERE data_catagory = 'service' AND data_sl_no IN (SELECT product FROM product $AndWhere)");
if ($pricing_row = mysqli_fetch_assoc($pricing_sql)) {
    $total_pricing_sum = !is_null($pricing_row['total_pricing']) ? $pricing_row['total_pricing'] : 0;
}

$payment_sql = mysqli_query($con, "SELECT SUM(payment) AS total_payment FROM payment WHERE p_forProduct IN (SELECT pid FROM product $AndWhere)");
if ($payment_row = mysqli_fetch_assoc($payment_sql)) {
    $total_payment_sum = !is_null($payment_row['total_payment']) ? $payment_row['total_payment'] : 0;
}

// Close connection
mysqli_close($con);
?>

<div class="row justify-content-center" style="font-weight: bold; margin-bottom: 10px;">
    <div class="col-md-10 d-flex justify-content-between">
        <ul class="service-details">
            <li>Total Charges: <?php echo $total_pricing_sum; ?></li>
            <li>Total Discount: <span style="color:blue;"><?php echo $total_discount; ?></span></li>
            <li>Total Received: <span style="color:green;"><?php echo $total_payment_sum; ?></span></li>
            <li>Pending: <span
                    style="color:red;"><?php echo $total_pricing_sum - $total_payment_sum - $total_discount; ?></span>
            </li>
        </ul>
    </div>
    <div class="col-md-2">
        <div class="text-end mb-3">
            <a href="export-productList.php" class="btn btn-success btn-sm"><i class="fa fa-download"></i> Excel</a>
        </div>
    </div>
</div>