<?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_string = "invoice_type = 2 AND is_delete IS NULL";
                if(strlen($search_text) > 0){
                    $search_string = "(invoice_type = 2 AND invoice_no LIKE '%$search_text%' AND is_delete IS NULL) OR (invoice_type = 2 AND bid_wo_no LIKE '%$search_text%' AND is_delete IS NULL) OR (invoice_type = 2 AND invoice_no LIKE '%$search_text%' AND bid_wo_no LIKE '%$search_text%' AND is_delete IS NULL)";
                }
                $draw = $_REQUEST['draw'];
                $totalRecords = 0;                
                $total_array = array();
                $query_bid_item_cnt = "SELECT * FROM invoice WHERE $search_string ORDER BY create_time DESC";
                $run_bid_item_cnt = mysqli_query($conn, $query_bid_item_cnt);
                $totalRecords = mysqli_num_rows($run_bid_item_cnt);  
                if($length == -1){
                    $length = $totalRecords;
                }
                $query_bid_item = "SELECT * FROM invoice WHERE $search_string ORDER BY create_time DESC LIMIT $start ,$length";
                $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 = '';
                        $crew_pay = '';
                        $projected_profit = '';
                        $actual_profit = '';
                        $act_color = '';
                        $pro_color = '';                    
                        $invoice_id =  $row_bid_item['invoice_id'];
                        $invoice_no = $row_bid_item['invoice_no'];
                        $invoice_edit = "<a class='btn btn-default select_dash' href='invoice_finalize.php?in_no=$invoice_id' id = ''>Edit</a>";
                        $invoice_no_row = $invoice_no . '<br>' . $invoice_edit;
                        $bid_wo_no_row = '';
                        $bid_wo_no = $row_bid_item['bid_wo_no'];
                        $bid_edit = '';
                        if(isset($bid_wo_no)){
                            if(strlen($bid_wo_no) > 0){
                                $fetch_bid_id_query = "SELECT * FROM bid WHERE is_delete IS NULL AND bid_wo_no = '$bid_wo_no'"; 
                                $fetch_bid_id_run = mysqli_query($conn, $fetch_bid_id_query);
                                if(mysqli_num_rows($fetch_bid_id_run) == 1){
                                    $fetch_bid_id_row = mysqli_fetch_array($fetch_bid_id_run);
                                    $bid_id = $fetch_bid_id_row['bid_id'];
                                }
                                if(isset($bid_id)){
                                    if(strlen($bid_id) > 0){
                                        $bid_edit = "<a class='btn btn-default select_dash' href='estimate_finalize.php?wo_no=$bid_id' id = ''>Edit</a>";
                                        $bid_wo_no_row = $bid_wo_no . '<br>' . $bid_edit;
                                    }
                                }

                            }
                        }                       
                        $prop_id = $row_bid_item['prop_id'];
                        $emp_id = $row_bid_item['employee_id'];     
                        $adm_id = $row_bid_item['admin_id'];        
                        $vendor_is_settle = $row_bid_item['vendor_is_settle'];
                        $is_settle = $row_bid_item['is_settle'];
                        $staff_name = '';
                        if(isset($adm_id)){ 
                            if(strlen($adm_id) > 0){                 
                                $query_adm = "SELECT * FROM admin WHERE admin_id = $adm_id";
                                $run_adm = mysqli_query($conn, $query_adm);
                                $row_adm = mysqli_fetch_array($run_adm);
                                $staff_name = $row_adm['admin_fname']. ' ' . $row_adm['admin_lname'];
                            }
                        }
                        if(isset($emp_id)){ 
                            if(strlen($emp_id) > 0){                 
                                $query_emp = "SELECT * FROM employee WHERE employee_id = $emp_id";
                                $run_emp = mysqli_query($conn, $query_emp);
                                $row_emp = mysqli_fetch_array($run_emp);
                                $staff_name = $row_emp['employee_fname']. ' ' . $row_emp['employee_lname'];
                            }
                        }    
                        if($row_bid_item['invoice_amount'] != NULL){
                            $invoice_amount = $row_bid_item['invoice_amount'];
                        }else{
                            $invoice_amount = 0;
                        }


                        if($row_bid_item['client_pay'] != NULL){
                            $client_pay = $row_bid_item['client_pay'];
                        }else{
                            $client_pay = 0;
                        }

                        if($row_bid_item['vendor_pay'] != NULL){
                            $crew_pay = $row_bid_item['vendor_pay'];
                        }else{
                            $crew_pay = 0;
                        }

                    
                        if(isset($vendor_is_settle)){
                        if($vendor_is_settle == 1){    
                        if(isset($crew_pay) && isset($invoice_amount)){
                        
                                $projected_profit = $invoice_amount - $crew_pay; 
                                $projected_profit = number_format($projected_profit, 2, '.', '');

                                if($projected_profit > 0){
                                    $profit_per_cent = $projected_profit/$invoice_amount;
                                    $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"';/* light */
                                    }                    
                                    
                                }else if($projected_profit == 0){
                                    $pro_color = 'style = "background-color:yellow"';
                                }else if ($projected_profit < 0){
                                    $pro_color = 'style = "background-color:red"';
                                }
                            }
                        }          

                        }
                        if(isset($is_settle) && isset($vendor_is_settle)){
                            if($is_settle == 1 && $vendor_is_settle == 1){
                        if(isset($crew_pay) && isset($client_pay) && isset($invoice_amount)){
                        
                                $actual_profit = $client_pay - $crew_pay; 
                                $actual_profit = number_format($actual_profit, 2, '.', '');
                                if($crew_pay > 0){
                                if($actual_profit > 0){
                                    $a_profit_per_cent = $actual_profit/$crew_pay;
                                    $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"';/* light */
                                    }                    
                                    
                                }else if($actual_profit == 0){
                                    $act_color = 'style = "background-color:yellow"';
                                }else if ($actual_profit < 0){
                                    $act_color = 'style = "background-color:red"';
                                }
                            }else{
                                if($actual_profit > 0){
                                $act_color = 'style = "background-color:#22803b"';
                                }
                            }
                        }
                    }          

                }

                $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'];
                $prop_full_add = $row_prop['prop_full_add'];
                $prop_county = $row_prop['prop_county'];
                $prop_state = $row_prop['prop_state'];
                $prop_city = $row_prop['prop_city'];
                $prop_zip = $row_prop['prop_zip'];
                
                $property_details ="# " .$prop_case_num. " - ".$prop_full_add ." ". $prop_county. " ". $prop_city. " ". $prop_state." ". $prop_zip;
                $client_pay_info = '';
                if(isset($is_settle)){
                    if($is_settle == 1){
                        $client_pay_info = $client_pay_info . $client_pay;
                    }        
                }                                

                $client_pay_date_info = '';
                $fetch_client_pay_details = "SELECT * FROM client_pay_settle WHERE invoice_id = $invoice_id";
                $fetch_client_pay_details_run = mysqli_query($conn, $fetch_client_pay_details);     
                if(mysqli_num_rows($fetch_client_pay_details_run) > 0){
                    $date_cnt = mysqli_num_rows($fetch_client_pay_details_run);
                    while($fetch_client_pay_details_row = mysqli_fetch_array($fetch_client_pay_details_run)){
                        $date_cnt--;
                        $client_pay_date = $fetch_client_pay_details_row['pay_date'];
                        $client_pay_date = strtotime($client_pay_date);
                        $client_pay_date = date('m-d-Y', $client_pay_date);  
                        $client_pay_date_info = $client_pay_date_info . $client_pay_date; 
                        if($date_cnt > 0){
                            $client_pay_date_info = $client_pay_date_info. '<br>';
                        }
                    }
                }      
                
                    $client_pay_settle = '';                    
                    $fetch_client_pay_detailsx = "SELECT * FROM client_pay_settle WHERE invoice_id = $invoice_id";
                    $fetch_client_pay_details_runx = mysqli_query($conn, $fetch_client_pay_detailsx);     
                    
                    if(mysqli_num_rows($fetch_client_pay_details_runx) > 0){
                        $client_pay_ref_cnt = mysqli_num_rows($fetch_client_pay_details_runx);
                        while($fetch_client_pay_details_rowx = mysqli_fetch_array($fetch_client_pay_details_runx)){
                            $client_pay_ref_cnt--;
                            $client_ref_no = $fetch_client_pay_details_rowx['ref_no'];
                            $client_pay_settle = $client_pay_settle . $client_ref_no;
                            if($client_pay_ref_cnt > 0){
                                $client_pay_settle = $client_pay_settle . '<br>';
                            }                
                        }
                    }      
                      
                    $crew_pay_info = '';
                    if(isset($vendor_is_settle)){
                        if($vendor_is_settle == 1){
                            $crew_pay_info = $crew_pay_info . $crew_pay;
                        }
                    }                  
                    $staff_info = '';
                    if(isset($staff_name)){
                        $staff_info = $staff_info . $staff_name;
                    }
                    if($user_type == 'admin'){  
                        $staff_info = $staff_info . "<br><a href='item_change_log.php?item_no=$invoice_id&type=inv&state=report'>Change History</a>";
                    }
                  
                    $data = array($invoice_no_row , $bid_wo_no_row, $client_name, $prop_case_num ,$invoice_amount ,$date, $client_pay_info, $client_pay_date_info, $client_pay_settle, $crew_pay_info, $projected_profit, $actual_profit, $staff_info);
                    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);
            }
        }
    }
