<?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>