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

if (isset($_POST['start'])) {
    $start = mysqli_real_escape_string($con, $_POST['start']);
    $AndWhere = '';

    // Initialize an array to hold filter conditions
    $conditions = [];

    if (!empty($_POST['FirstFilterValue'])) {
        $fran_sql = mysqli_query($con, "SELECT * FROM users WHERE uname = '{$_POST['FirstFilterValue']}' AND utype = 'Franchiee' ");
        if ($fran_row = mysqli_fetch_assoc($fran_sql)) {
            $fran_id = $fran_row["uid"];
        }
        $conditions[] = "p_franchiee = '" . $fran_id . "'";
    }

    if (!empty($_POST['thirdValue'])) {
        $sfran_sql = mysqli_query($con, "SELECT * FROM users WHERE uname = '{$_POST['thirdValue']}' AND utype = 'subFranchiee' ");
        if ($sfran_row = mysqli_fetch_assoc($sfran_sql)) {
            $sfran_id = $sfran_row["uid"];
        }
        $conditions[] = "p_subFranchiee = '" . $sfran_id . "'";
    }

    if (!empty($_POST['SecondFilterValue'])) {
        $client_sql = mysqli_query($con, "SELECT uid FROM users WHERE uname = '{$_POST['SecondFilterValue']}' AND utype = 'Client' ");
        if ($client_row = mysqli_fetch_assoc($client_sql)) {
            $client_id = $client_row["uid"];
        }
        $conditions[] = "p_client = '" . $client_id . "'";
    }

   // Add date range filters
    if (!empty($_POST['StartDate']) && !empty($_POST['EndDate'])) {
        // Parse the Start Date and End Date from the POST request
        $startDate = DateTime::createFromFormat('Y-m-d', $_POST['StartDate']);
        $endDate = DateTime::createFromFormat('Y-m-d', $_POST['EndDate']);
    
        if ($startDate && $endDate) {
            // Format only the date part (Y-m-d) to be used in the SQL query
            $startDateFormatted = $startDate->format('Y-m-d');
            $endDateFormatted = $endDate->format('Y-m-d');
        
            // Compare using only the date part of p_createDate
            $conditions[] = "DATE(p_createDate) BETWEEN '$startDateFormatted' AND '$endDateFormatted'";
        } else {
            if (!$startDate) {
                echo "Invalid Start Date format.";
            }
            if (!$endDate) {
                echo "Invalid End Date format.";
            }
        }
    } elseif (!empty($_POST['StartDate'])) {
        // Handle StartDate only
        $startDate = DateTime::createFromFormat('Y-m-d', $_POST['StartDate']);
    
        if ($startDate) {
            $startDateFormatted = $startDate->format('Y-m-d');
            $conditions[] = "DATE(p_createDate) >= '$startDateFormatted'";
        } else {
            echo "Invalid Start Date format.";
        }
    } elseif (!empty($_POST['EndDate'])) {
        // Handle EndDate only
        $endDate = DateTime::createFromFormat('Y-m-d', $_POST['EndDate']);
    
        if ($endDate) {
            $endDateFormatted = $endDate->format('Y-m-d');
            $conditions[] = "DATE(p_createDate) <= '$endDateFormatted'";
        } else {
            echo "Invalid End Date format.";
        }
    }


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

    // Get total payments for the current query
    $total_sql = "SELECT SUM(payment) AS total_payment FROM payment $AndWhere";
    $total_res = mysqli_query($con, $total_sql);
    $total_payment = 0;
    if ($total_row = mysqli_fetch_assoc($total_res)) {
        $total_payment = $total_row['total_payment'];
    }
    // Get total discount for the current query
    $discount_sql = "SELECT SUM(discount) AS total_discount FROM payment $AndWhere";
    $discount_res = mysqli_query($con, $discount_sql);
    $total_discount = 0;
    if ($discount_row = mysqli_fetch_assoc($discount_res)) {
        $total_discount = $discount_row['total_discount'];
    }

    // Get total payments for the current query
    $total_pricing = 0;

    // Combine your queries to reduce overhead
    $pricing_sql = "
        SELECT p.p_forProduct, pr.product, d.data_seo_tag 
        FROM payment p
        JOIN product pr ON p.p_forProduct = pr.product
        JOIN data d ON d.data_catagory = 'service' AND d.data_sl_no = pr.product
        $AndWhere
    ";

    $pricing_res = mysqli_query($con, $pricing_sql);
    if ($pricing_res) {
        while ($pricing_row = mysqli_fetch_assoc($pricing_res)) {
            // Ensure that data_seo_tag exists and is numeric
            $pricing = isset($pricing_row["data_seo_tag"]) && is_numeric($pricing_row["data_seo_tag"]) 
                   ? (float)$pricing_row["data_seo_tag"] 
                   : 0;

            // Add to total pricing
            $total_pricing += $pricing;
        }
    } else {
        // Handle the error here, e.g., log it or display a message
        echo "Error executing query: " . mysqli_error($con);
    }



    // Fetch individual payment records
    $sql = "SELECT * FROM payment $AndWhere ORDER BY p_id DESC LIMIT $start, 10";
    $res = mysqli_query($con, $sql);

    if ($total_rows = mysqli_num_rows($res) > 0) {
        $c = $start + 1;

        // Display total payments
        if ($total_pricing > 0) {
            // echo "<h5 class='text-center'>Total Payable: <i class='fa fa-inr'></i> $total_pricing</h5>";
        }
        if ($total_discount > 0) {
            // echo "<h5 class='text-center'>Total Payable: <i class='fa fa-inr'></i> $total_discount</h5>";
        }

        $firstFilter = "";
        if(!empty($_POST['FirstFilterValue'])){
            $firstFilter = $_POST['FirstFilterValue'];
        }
        $secondFilter = "";
        if(!empty($_POST['SecondFilterValue'])){
            $secondFilter = $_POST['SecondFilterValue'];
        }
        $thirdFilter = "";
        if(!empty($_POST['thirdValue'])){
            $thirdFilter = $_POST['thirdValue'];
        }
        $StartDate = "";
        if(!empty($_POST['StartDate'])){
            $StartDate = $_POST['StartDate'];
        }
        $EndDate = "";
        if(!empty($_POST['EndDate'])){
            $EndDate = $_POST['EndDate'];
        }
?>
<div class="row justify-content-between mb-2">
    <div class="col-md-8">
        <?php
        if ($total_payment > 0) {
            echo "<h6>Total Payment Received: <i class='fa fa-inr'></i> $total_payment</h6>";
        }
        ?>
    </div>
    <div class="col-md-4 text-end">
        <a href="export-priceList.php?FirstFilterValue=<?php echo $firstFilter;?>&SecondFilterValue=<?php echo $secondFilter?>&thirdValue=<?php echo $thirdFilter?>&StartDate=<?php echo $StartDate;?>&EndDate=<?php echo $EndDate;?>"
            class="btn btn-success btn-sm">Download Excel</a>

    </div>
</div>
<?php
        // Loop through each payment and display
        while ($rows = mysqli_fetch_assoc($res)) {
            $client_sql = "SELECT * FROM users WHERE uid = '" . mysqli_real_escape_string($con, $rows['p_client']) . "'";
            $client_run = mysqli_query($con, $client_sql);
            if($client_rows = mysqli_fetch_assoc($client_run)){
                $client_name = $client_rows["uname"];
            }

            $subFran_name = '';
            $fran_sql = "SELECT * FROM users WHERE uref_code = '" . mysqli_real_escape_string($con, $client_rows['admin_ref']) . "'";
            $fran_run = mysqli_query($con, $fran_sql);
            if($fran_rows = mysqli_fetch_assoc($fran_run)){
                if($fran_rows["utype"] == 'Franchiee'){
                    $fran_name = $fran_rows["uname"];
                }
                if($fran_rows["utype"] == 'subFranchiee'){
                    $subFran_name = $fran_rows["uname"];
                }
            }

            if(!empty($subFran_name)){
                $subFran_sql = "SELECT * FROM users WHERE uref_code = '" . mysqli_real_escape_string($con, $fran_rows['admin_ref']) . "'";
                $subFran_run = mysqli_query($con, $subFran_sql);
                if($subFran_rows = mysqli_fetch_assoc($subFran_run)){
                    $fran_name = $subFran_rows["uname"];
                }
            }

            $employee_name = '';
            if(!empty($rows['pEmployee']) && $rows['pEmployee'] != 0){
                $employee_sql = "SELECT uname, uref FROM users WHERE uid = '" . mysqli_real_escape_string($con, $rows['pEmployee']) . "'";
                $employee_run = mysqli_query($con, $employee_sql);
                if($employee_rows = mysqli_fetch_assoc($employee_run)){
                    $employee_name = $employee_rows["uname"];
                }
            }

            $product_sql = mysqli_query($con, "SELECT * FROM product WHERE pid = '{$rows["p_forProduct"]}' ");
            if($product_row = mysqli_fetch_assoc($product_sql)){
                $product_id = $product_row["product"];
            }
            
            // Fetch services for the selected category
            $service_sql = mysqli_query($con, "SELECT * FROM data WHERE data_catagory = 'service' AND data_sl_no = '{$product_id}' ");
            if ($service_row = mysqli_fetch_assoc($service_sql)) {
                $product = $service_row["data_title"];
                
                $cat_sql = mysqli_query($con, "SELECT * FROM category WHERE cid = '{$service_row["data_subCategory"]}' ");
                if($cat_row = mysqli_fetch_assoc($cat_sql)){
                    $pCat = $cat_row["cname"];
                }
            }

            $statusName = $rows["p_transectionStatus"];
            $amount = $rows["payment"];
            $transectionId = $rows["p_transectionId"];
            
?>
<li class='clearfix' style='float: none;'>
    <div class="single-dis-admin mb-2">
        <div class='row align-items-center'>
            <div class='col-md-10 col-12 mb-2'>
                <a style='color:orange;font-size:17px;cursor:pointer;text-decoration:none;' class='viewDetails'
                    data-bs-toggle='modal' data-bs-target='#studentDetailsModal' data-pname='<?php echo $product;?>'
                    data-pcat='<?php echo $pCat;?>' data-pamount='<?php echo $amount;?>'
                    data-pstatus='<?php echo $statusName;?>' data-transid='<?php echo $transectionId;?>'
                    data-raname='<?php echo $fran_name;?>' data-rasname='<?php echo $subFran_name;?>'
                    data-empmane='<?php echo $employee_name;?>' data-clientname='<?php echo $client_name;?>'
                    data-date='<?php echo $rows["p_createDate"];?>'>
                    <i class='fab fa-product-hunt' aria-hidden='true'></i> <b><?php echo $product;?></b>&nbsp;
                    ( <i class="fa fa-inr"></i> Paid Amount : <?php echo $amount;?> )
                </a>
                <br>
                <b><i class='fa fa-list-alt' style="color:orange;"></i> </b><?php echo $pCat;?> &nbsp; &nbsp; &nbsp;
                <b><i class='fa fa-clock' style="color:orange;"></i> </b><?php echo $rows["p_createDate"];?>
            </div>

            <div class='col-md-2 col-12'>
                <a class='btn btn-secondary btn-sm viewDetails' title='Click to View' data-bs-toggle="modal"
                    data-bs-target='#studentDetailsModal' data-pname='<?php echo $product;?>'
                    data-pcat='<?php echo $pCat;?>' data-pamount='<?php echo $amount;?>'
                    data-pstatus='<?php echo $statusName;?>' data-transid='<?php echo $transectionId;?>'
                    data-raname='<?php echo $fran_name;?>' data-rasname='<?php echo $subFran_name;?>'
                    data-empmane='<?php echo $employee_name;?>' data-clientname='<?php echo $client_name;?>'
                    data-date='<?php echo $rows["p_createDate"];?>'>
                    <i class='fa fa-eye' aria-hidden='true'></i> Payment Details
                </a>
            </div>
        </div>
    </div>
</li>

<?php
            $c++;
        }
        mysqli_close($con);
    }
}
?>


<script>
$(document).ready(function() {
    $('.viewDetails').on('click', function(e) {
        e.preventDefault(); // Prevent default anchor tag action

        var pname = $(this).data('pname');
        $('#pname').text(pname);

        var pcat = $(this).data('pcat');
        $('#pcat').text(pcat);

        var pamount = $(this).data('pamount');
        $('#pamount').text(pamount);

        var pstatus = $(this).data('pstatus');
        $('#pstatus').text(pstatus);

        var transid = $(this).data('transid');
        $('#transid').text(transid);

        var raname = $(this).data('raname');
        $('#raname').text(raname);

        var rasname = $(this).data('rasname');
        $('#rasname').text(rasname);

        var empmane = $(this).data('empmane');
        $('#empmane').text(empmane);

        var clientname = $(this).data('clientname');
        $('#clientname').text(clientname);

        var date = $(this).data('date');
        $('#date').text(date);

        $('#studentDetailsModal').modal('show'); // Open the modal
    });

});
</script>