<?php
include "include/db.php";
include "include/adminauth.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_text = trim($search_text);                
                $draw = $_REQUEST['draw'];
                $totalRecords = 0;                
                $total_array = array();          
           
                $length_str = '';
                if(strlen($search_text) == 0){ 
                    $query_vendor_pay_cnt = "SELECT * FROM client_pay_settle WHERE type = 'email' ORDER BY id DESC";
                    $run_vendor_pay_cnt= mysqli_query($conn, $query_vendor_pay_cnt); 
                    $totalRecords = mysqli_num_rows($run_vendor_pay_cnt);  
                    if($length == -1){
                        $length = $totalRecords;
                    }

                    $length_str = " LIMIT $start ,$length";
                }

                $query_vendor_pay = "SELECT * FROM client_pay_settle WHERE type = 'email' ORDER BY id DESC". $length_str;
                $run_vendor_pay= mysqli_query($conn, $query_vendor_pay);

                if(mysqli_num_rows($run_vendor_pay) >0){
                    while($row_vendor_pay = mysqli_fetch_array($run_vendor_pay)){
                        $invoice_id = $row_vendor_pay['invoice_id'];
                        $vendor_pay_ref = $row_vendor_pay['ref_no'];
                        $vendor_amount = $row_vendor_pay['amount'];
                        $vendor_amount = number_format($vendor_amount, 2, '.', '');  
                        $vendor_amount = '$'.$vendor_amount;
                        $vendor_charge = $row_vendor_pay['charge'];
                        if(isset($vendor_charge)){
                            $vendor_charge = number_format($vendor_charge, 2, '.', '');  
                            $vendor_charge = '$'.$vendor_charge;
                        }else{
                            $vendor_charge = '';
                        }                        
                        $vendor_date = $row_vendor_pay['pay_date'];
                        $vendor_date = strtotime($vendor_date);
                        $vendor_date = date('m-d-Y', $vendor_date);
                        $client_email = $row_vendor_pay['client_email_id'];
                        $create_time = $row_vendor_pay['create_time'];
                        $create_time = strtotime($create_time);
                        $create_time = date('m-d-Y', $create_time);
                        
                        $search_string = "invoice_id = $invoice_id AND invoice_type = 2 AND is_delete IS NULL";
                        if(strlen($search_text) > 0){
                            $search_string = "(invoice_id = $invoice_id AND invoice_type = 2 AND invoice_no LIKE '%$search_text%' AND is_delete IS NULL) OR (invoice_id = $invoice_id AND invoice_type = 2 AND bid_wo_no LIKE '%$search_text%' AND is_delete IS NULL) OR (invoice_id = $invoice_id AND invoice_type = 2 AND invoice_no LIKE '%$search_text%' AND bid_wo_no LIKE '%$search_text%' AND is_delete IS NULL)";
                        }                 

                        $query_bid_item = "SELECT * FROM invoice WHERE $search_string";
                        $run_bid_item = mysqli_query($conn, $query_bid_item);
                        if(mysqli_num_rows($run_bid_item) >0){
                            while($row_bid_item = mysqli_fetch_array($run_bid_item)){
                                $client_pay = '';
                                $date1 = '';
                                $crew_pay = '';      
                                $invoice_no = $row_bid_item['invoice_no'];
                                $bid_wo_no = $row_bid_item['bid_wo_no'];
                                $prop_id = $row_bid_item['prop_id'];

                                $invoice_amount = $row_bid_item['invoice_amount'];
                                $invoice_amount = number_format($invoice_amount, 2, '.', '');  
                                $invoice_amount = '$'.$invoice_amount;
                            
                                $client_id = $row_bid_item['client_id'];
                                $date = $row_bid_item['create_time'];
                                $timestamp = strtotime($date);
                                $date= date('m-d-Y', $timestamp);
                                    
                                $query_maps = "SELECT * FROM client WHERE client_id = $client_id";
                                $run_maps = mysqli_query($conn, $query_maps);
                                $row_maps = mysqli_fetch_array($run_maps);
                                $client_name = $row_maps['client_name'];

                                $query_prop = "SELECT * FROM property WHERE prop_id = $prop_id";
                                $run_prop = mysqli_query($conn, $query_prop);
                                $row_prop = mysqli_fetch_array($run_prop);

                                $prop_case_num = $row_prop['prop_case_num'];
                                if(strlen($search_text) > 0){
                                    $totalRecords++;
                                }                            
                            }
                        }else{
                            continue;
                        }                            
                                $data = array($invoice_no , $bid_wo_no , $client_name , $client_email , $prop_case_num , $vendor_date, $vendor_pay_ref, $invoice_amount, $vendor_charge, $vendor_amount,$create_time);
                                array_push($total_array, $data); 
                            
                            }
                }              
                    $json_data = array(
                        "draw"            => intval($draw),   
                        "recordsTotal"    => intval($totalRecords),  
                        "recordsFiltered" => intval($totalRecords),
                        "data"            => $total_array  
                        );
                    echo json_encode($json_data);
                }
        }
    }
