<?php
include "include/db.php";
include "include/auth.php";
    if($conn){
        if(isset($_REQUEST['length']) && isset($_REQUEST['start']) && isset($_REQUEST['view']) && isset($_REQUEST['cl'])){
            if(strlen($_REQUEST['length']) > 0 && strlen($_REQUEST['start']) > 0 && strlen($_REQUEST['view']) > 0 && strlen($_REQUEST['cl']) > 0){
                $url_prop_id = $_REQUEST['view'];
                $url_client_id = $_REQUEST['cl'];
                $start = $_REQUEST['start'];
                $length = $_REQUEST['length'];
                $draw = $_REQUEST['draw'];
                $totalRecords = 0;
                $query_bid_item_cnt = "SELECT * FROM invoice WHERE invoice_type = 2 AND client_id = $url_client_id AND prop_id = $url_prop_id AND is_delete IS NULL ORDER BY create_time DESC";
                $run_bid_item_cnt = mysqli_query($conn, $query_bid_item_cnt);
                $totalRecords = mysqli_num_rows($run_bid_item_cnt);  
                
                $query_bid_detail_cnt = "SELECT * FROM bid WHERE bid_type = 2 AND client_id = $url_client_id AND prop_id = $url_prop_id AND is_delete IS NULL ORDER BY create_time DESC";
                $run_bid_detail_cnt = mysqli_query($conn, $query_bid_detail_cnt);
                $totalRecords = $totalRecords + mysqli_num_rows($run_bid_detail_cnt); 
                
                $arr_prop_order_list = array();
                $query_bid_item = "SELECT * FROM invoice WHERE invoice_type = 2 AND client_id = $url_client_id AND prop_id = $url_prop_id AND is_delete IS NULL ORDER BY create_time DESC";
                $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)){
                        $invoice_id =  $row_bid_item['invoice_id'];
                        $invoice_no = $row_bid_item['invoice_no'];
                        $bid_wo_no = $row_bid_item['bid_wo_no'];        
                        $invoice_amount = $row_bid_item['invoice_amount'];
                        $client_pay_ref = $row_bid_item['client_pay_ref'];
                        if(strlen($row_bid_item['employee_id']) > 0){
                            $staff_id = $row_bid_item['employee_id'];
                            $emp_details_query = "SELECT * FROM employee WHERE employee_id = $staff_id AND status = 'active'";
                            $emp_details_run = mysqli_query($conn, $emp_details_query);
                            $emp_details_row = mysqli_fetch_array($emp_details_run);
                            $staff_name = $emp_details_row['employee_fname'];
                            
                        }elseif(strlen($row_bid_item['admin_id']) > 0){
                            $admin_id = $row_bid_item['admin_id'];
                            $admin_details_query = "SELECT * FROM admin WHERE admin_id = $admin_id AND status = 'active'";
                            $admin_details_run = mysqli_query($conn, $admin_details_query);
                            $admin_details_row = mysqli_fetch_array($admin_details_run);
                            $staff_name = $admin_details_row['admin_fname'] . ' ' . $admin_details_row['admin_lname'];
                        }else{
                            $staff_name = '';
                        }
                        if($row_bid_item['client_pay'] != NULL){
                            $client_pay = $row_bid_item['client_pay'];
                        }else{
                            $client_pay = '';
                        }
                        if($row_bid_item['client_pay_date'] != NULL){
                            $client_pay_date = $row_bid_item['client_pay_date'];
                            $client_pay_date = strtotime($client_pay_date);
                            $client_pay_date= date('m-d-Y', $client_pay_date);  

                        }else{
                            $client_pay_date = '';
                        }
                        if($row_bid_item['vendor_pay'] != NULL){
                            $crew_pay = $row_bid_item['vendor_pay'];
                        }else{
                            $crew_pay = '';
                        }
                        if($row_bid_item['vendor_pay_date'] != NULL){
                            $vendor_pay_date = $row_bid_item['vendor_pay_date'];
                            $timestamp2 = strtotime($vendor_pay_date);
                            $vendor_pay_date= date('m-d-Y', $timestamp2);  

                        }else{
                            $vendor_pay_date = '';
                        }
                        $date = $row_bid_item['create_time'];
                        $arr_inv_details = array("type"=>"invoice","inv_no"=>"$invoice_no","bid_no"=>"$bid_wo_no","amount"=>"$invoice_amount","client_pay"=>"$client_pay","client_pay_date"=>"$client_pay_date","vendor_pay"=>"$crew_pay","vendor_pay_date"=>"$vendor_pay_date","date"=>"$date","client_pay_ref"=>"$client_pay_ref","staff"=>"$staff_name","inv_id"=>"$invoice_id","bid_id"=>"");
                        array_push($arr_prop_order_list, $arr_inv_details);
                    }
                }

                $query_bid_detail = "SELECT * FROM bid WHERE bid_type = 2 AND client_id = $url_client_id AND prop_id = $url_prop_id AND is_delete IS NULL ORDER BY create_time DESC";
                $run_bid_detail = mysqli_query($conn, $query_bid_detail);
                if(mysqli_num_rows($run_bid_detail) >0){
                    while($row_bid_detail = mysqli_fetch_array($run_bid_detail)){
                        $bid_id =  $row_bid_detail['bid_id'];       
                        $bid_wo_no = $row_bid_detail['bid_wo_no'];        
                        $bid_amount = $row_bid_detail['bid_amount'];       
                        $date = $row_bid_detail['create_time'];
                        if(strlen($row_bid_detail['employee_id']) > 0){
                            $staff_id = $row_bid_detail['employee_id'];
                            $emp_details_query = "SELECT * FROM employee WHERE employee_id = $staff_id AND status = 'active'";
                            $emp_details_run = mysqli_query($conn, $emp_details_query);
                            $emp_details_row = mysqli_fetch_array($emp_details_run);
                            $staff_name = $emp_details_row['employee_fname'];
                        

                        }elseif(strlen($row_bid_detail['admin_id']) > 0){
                            $admin_id = $row_bid_detail['admin_id'];
                            $admin_details_query = "SELECT * FROM admin WHERE admin_id = $admin_id AND status = 'active'";
                            $admin_details_run = mysqli_query($conn, $admin_details_query);
                            $admin_details_row = mysqli_fetch_array($admin_details_run);
                            $staff_name = $admin_details_row['admin_fname'] . ' ' . $admin_details_row['admin_lname'];
                        }else{
                            $staff_name = '';
                        }
                        $arr_inv_details = array("type"=>"bid","inv_no"=>"","bid_no"=>"$bid_wo_no","amount"=>"$bid_amount","client_pay"=>"","client_pay_date"=>"","vendor_pay"=>"","vendor_pay_date"=>"","date"=>"$date","client_pay_ref"=>"","staff"=>"$staff_name","inv_id"=>"","bid_id"=>"$bid_id");
                        array_push($arr_prop_order_list, $arr_inv_details);

                    }
                }

                array_multisort(array_column($arr_prop_order_list, 'date'), SORT_DESC, $arr_prop_order_list);
                array_slice($arr_prop_order_list, $start, $length);

                $total_array = array();

                foreach($arr_prop_order_list as $arr_prop_order_list_item){
                    $bid_no_item = $arr_prop_order_list_item['bid_no'];
                    $invoice_no_item = $arr_prop_order_list_item['inv_no'];
                    $client_pay_date_item = $arr_prop_order_list_item['client_pay_date'];
                    $vendor_pay_date_item = $arr_prop_order_list_item['vendor_pay_date'];  
                    $crew_pay_item = $arr_prop_order_list_item['vendor_pay'];
                    $client_pay_item = $arr_prop_order_list_item['client_pay'];
                    $client_pay_ref_item = $arr_prop_order_list_item['client_pay_ref'];
                    $item_amount = $arr_prop_order_list_item['amount'];
                    $staff_name_item = $arr_prop_order_list_item['staff'];
                    $bid_id_item = $arr_prop_order_list_item['bid_id'];
                    $inv_id_item = $arr_prop_order_list_item['inv_id'];
                    if($arr_prop_order_list_item['type'] == 'invoice'){
                        $invoice_amount_item = $item_amount;
                        $view_button = "<a class='btn btn-primary' href='invoice_finalize.php?in_no=$inv_id_item' id = ''>View Invoice</a>";
                    }else{
                        $invoice_amount_item = '';
                    }
                    if($arr_prop_order_list_item['type'] == 'bid'){
                        $invoice_amount_item = $item_amount;
                        $view_button = "<a class='btn btn-primary' href='estimate_finalize.php?wo_no=$bid_id_item' id = ''>View Bid</a>";
                    }
                    $projected_profit = ''; 
                    $actual_profit = '';
                    $act_color = '';
                    $pro_color = '';
                    $date_item = $arr_prop_order_list_item['date'];
                    $date_item = strtotime($date_item);
                    $date_item= date('m-d-Y', $date_item);
                

                    if(strlen($crew_pay_item) > 0 && strlen($invoice_amount_item) > 0){
                            if($invoice_amount_item > 0 && $crew_pay_item > 0){
                                $projected_profit = $invoice_amount_item - $crew_pay_item; 
                                $projected_profit = number_format($projected_profit, 2, '.', '');

                                if($projected_profit > 0){
                                    $profit_per_cent = $projected_profit/$invoice_amount_item;
                                    $profit_per_cent = $profit_per_cent * 100;

                                    if($profit_per_cent > 30){
                                        $pro_color = 'style = "background-color:#22803b"';
                                    }else{
                                        $pro_color = 'style = "background-color:#5de381"';
                                    }                    
                                    
                                }else if($projected_profit == 0){
                                    $pro_color = 'style = "background-color:yellow"';
                                }else if ($projected_profit < 0){
                                    $pro_color = 'style = "background-color:red"';
                                }

                            }

                        }

                        if(strlen($crew_pay_item) > 0 && strlen($client_pay_item) > 0 && strlen($invoice_amount_item) > 0){
                            if($crew_pay_item > 0 && $client_pay_item > 0 && $invoice_amount_item > 0){
                                $actual_profit = $client_pay_item - $crew_pay_item; 
                                $actual_profit = number_format($actual_profit, 2, '.', '');

                                if($actual_profit > 0){
                                    $a_profit_per_cent = $actual_profit/$crew_pay_item;
                                    $a_profit_per_cent = $a_profit_per_cent * 100;

                                    if($a_profit_per_cent > 30){
                                        $act_color = 'style = "background-color:#22803b"';
                                    }else{
                                        $act_color = 'style = "background-color:#5de381"';
                                    }                    
                                    
                                }else if($actual_profit == 0){
                                    $act_color = 'style = "background-color:yellow"';
                                }else if ($actual_profit < 0){
                                    $act_color = 'style = "background-color:red"';
                                }
                            }
                        }
                    
                        $data = array($date_item, $invoice_no_item, $bid_no_item, $item_amount, $crew_pay_item, $vendor_pay_date_item, $projected_profit, $client_pay_ref_item, $client_pay_item, $client_pay_date_item, $actual_profit, $staff_name_item, $view_button);
                        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);
            }
        }
    }   
?>
           