<?php
include "include/db.php";
include "include/auth.php";
    if($conn){
        if(isset($_REQUEST['length']) && isset($_REQUEST['start'])){
            if(strlen($_REQUEST['length']) > 0 && strlen($_REQUEST['start']) > 0){
                $start = $_REQUEST['start'];
                $length = $_REQUEST['length'];
                $search_text = $_REQUEST['search']['value'];
                $search_string = "is_delete IS NULL";
                if(strlen($search_text) > 0){
                    $search_string = "(prop_case_num LIKE '%$search_text%' AND is_delete IS NULL) OR (prop_loan_num LIKE '%$search_text%' AND is_delete IS NULL) OR (prop_full_add LIKE '%$search_text%' AND is_delete IS NULL)";
                }
                $draw = $_REQUEST['draw'];
                $property_query = "SELECT * FROM property WHERE $search_string";
                $property_run = mysqli_query($conn, $property_query);
                $totalRecords = mysqli_num_rows($property_run);
                if($length == -1){
                    $length = $totalRecords;
                }
                $set = 'set';
                $count = 0;
                $length = $length;
                $query = "SELECT * FROM property WHERE $search_string ORDER BY prop_id LIMIT $start ,$totalRecords";
                $run = mysqli_query($conn, $query);
                $total_array = array();
                if($run){                    
                    while($row = mysqli_fetch_array($run)){
                        if($set == 'set'){
                            $prop_id = $row['prop_id'];
                            $prop_full_add = $row['prop_full_add']; 
                            $prop_city = $row['prop_city']; 
                            $prop_state = $row['prop_state']; 
                            $prop_zip = $row['prop_zip'];
                            $prop_case_num = $row['prop_case_num'];
                            $prop_county = $row['prop_county'];   
                             
                            $is_delete = $row['is_delete']; 
                            $fetch_client_details_query = "SELECT * FROM prop_assign WHERE prop_id = $prop_id AND is_delete IS NULL AND is_approve = 1";
                            $fetch_client_details_run = mysqli_query($conn, $fetch_client_details_query);
                            foreach($fetch_client_details_run as $fetch_client_details_row){
                                $bid_total = 0.00;
                                $invoice_total = 0.00;
                                $prop_client_id = $fetch_client_details_row['client_id'];
                                $fetch_prop_client_name_query = "SELECT * FROM client WHERE client_id = $prop_client_id";
                                $fetch_prop_client_name_run = mysqli_query($conn, $fetch_prop_client_name_query); 
                                $fetch_prop_client_name_row = mysqli_fetch_array($fetch_prop_client_name_run);
                                $prop_client_name = $fetch_prop_client_name_row['client_name'];
                               
                                $fetch_inv_totals_query = "SELECT * FROM invoice WHERE client_id = $prop_client_id AND prop_id = $prop_id AND is_delete IS NULL";
                                $fetch_inv_totals_run = mysqli_query($conn, $fetch_inv_totals_query);
                                while($fetch_inv_totals_row = mysqli_fetch_array($fetch_inv_totals_run)){
                                    $inv_amount = $fetch_inv_totals_row['invoice_amount'];
                                    $invoice_total = $invoice_total + $inv_amount;
                                }

                                $fetch_bid_totals_query = "SELECT * FROM bid WHERE client_id = $prop_client_id AND prop_id = $prop_id AND is_delete IS NULL";
                                $fetch_bid_totals_run = mysqli_query($conn, $fetch_bid_totals_query);
                                while($fetch_bid_totals_row = mysqli_fetch_array($fetch_bid_totals_run)){
                                    $bid_amount = $fetch_bid_totals_row['bid_amount'];
                                    $bid_total = $bid_total + $bid_amount;
                                }

                                $view = "<a href='property-order-list.php?view=$prop_id&cl=$prop_client_id'>View</a>";
                                $bid_total = number_format($bid_total, 2, '.', '');
                                $invoice_total = number_format($invoice_total, 2, '.', '');
                                $bid_total = '$' . $bid_total;
                                $invoice_total = '$' . $invoice_total;
                                $data = array($prop_id,$prop_case_num,$prop_client_name,$prop_full_add,$prop_city,$prop_state,$prop_zip, $prop_county,$bid_total,$invoice_total,$view);
                                array_push($total_array,$data);  
                                $count++;
                                if($count == $length){  
                                    $set = '';            
                                }                                
                            }
                        }else{
                            break;
                        }
                    }                    
                }
                $json_data = array(
                    "draw"            => intval($draw),   
                    "recordsTotal"    => intval($totalRecords),  
                    "recordsFiltered" => intval($totalRecords),
                    "data"            => $total_array  
                    );
                echo json_encode($json_data);
            }
        }
    }   
?>
           