<?php
include "include/db.php";
include "include/auth.php";
    if($conn){
        $estimate_query_unsettled = "";
        if($user_type == 'admin'){          
            $current_year = date('Y', time());
            $year_selected = $current_year;
            $option_selected = 'y';
            $value_selected =  'none';
           
            if(isset($_REQUEST['year']) && isset($_REQUEST['option']) && isset($_REQUEST['value'])){
                if(strlen($_REQUEST['year']) > 0 && strlen($_REQUEST['option']) > 0 && strlen($_REQUEST['value']) > 0){
                if($_REQUEST['year'] > 1990 && $_REQUEST['year'] < 2100){
                    $year_selected =  $_REQUEST['year'];
                }
                if($_REQUEST['option'] == 'y' || $_REQUEST['option'] == 'q' || $_REQUEST['option'] == 'm'){
                    $option_selected =  $_REQUEST['option'];
                    if($option_selected == 'm'){
                        if($_REQUEST['value'] >=0 && $_REQUEST['value'] <=11){
                            $value_selected =  $_REQUEST['value'];
                        }else{
                            $value_selected = 11;
                        }

                        $m_names = ['January', 'February', 'March', 
                            'April', 'May', 'June', 'July', 
                            'August', 'September', 'October', 'November', 'December'];
                                
                        $dash_month = $m_names[$value_selected];
                        
                        $value_selected = $value_selected + 1;
                    }
                    if($option_selected == 'q'){
                        if($_REQUEST['value'] >= 1 && $_REQUEST['value'] <= 4){
                            $value_selected = $_REQUEST['value'];
                        }else{
                            $value_selected = 4;
                        }
                        
                    }
                    
                }

                }
            }

            if($option_selected == 'y'){
                $estimate_query_unsettled = " AND YEAR(create_time) = $year_selected";
            }elseif($option_selected == 'm'){
                $estimate_query_unsettled = " AND YEAR(create_time) = $year_selected AND MONTH(create_time) = $value_selected";
            }elseif($option_selected == 'q'){
                $estimate_query_unsettled = " AND YEAR(create_time) = $year_selected AND QUARTER(create_time) = $value_selected";
            }else{
                $estimate_query_unsettled = " AND YEAR(create_time) = $year_selected";
            }
        }
            
        if(isset($_REQUEST['length']) && isset($_REQUEST['start'])){
            if(strlen($_REQUEST['length']) > 0 && strlen($_REQUEST['start']) > 0){
                $start = $_REQUEST['start'];
                $length = $_REQUEST['length'];
                $search_text = '';
                $search_text = $_REQUEST['search']['value'];
                $search_string = "";
                $month_timestamp = strtotime("-1 month", time());
                $month_time = date("Y-m-d h:i:s", $month_timestamp);
                $cl_set = '';
                $cl_id = '';
                if(isset($_REQUEST['client_id'])){
                    if(strlen($_REQUEST['client_id']) > 0){                       
                        $cl_id = $_REQUEST['client_id'];
                        $cl_id = trim($cl_id);
                        if($cl_id == 0){
                            $cl_id = '';
                        }
                    }
                }
                if(strlen($cl_id) > 0){                       
                    $cl_set = 'set';
                }
                if(strlen($search_text) > 0){
                    if($cl_set == ''){
                        $search_string = "(invoice_type = 2 AND is_delete IS NULL AND bid_wo_no LIKE '%$search_text%' $estimate_query_unsettled) OR (invoice_type = 2 AND is_delete IS NULL AND invoice_no LIKE '%$search_text%' $estimate_query_unsettled) OR (invoice_type = 2 AND is_delete IS NULL AND invoice_no LIKE '%$search_text%' AND bid_wo_no LIKE '%$search_text%' $estimate_query_unsettled)";
                    }else if($cl_set == 'set'){
                        $search_string = "(invoice_type = 2 AND is_approve = 1 $estimate_query_unsettled AND is_delete IS NULL AND client_id = $cl_id AND bid_wo_no LIKE '%$search_text%') OR (invoice_type = 2 AND is_approve = 1 $estimate_query_unsettled AND is_delete IS NULL AND client_id = $cl_id AND invoice_no LIKE '%$search_text%') OR (invoice_type = 2 AND is_approve = 1 $estimate_query_unsettled AND is_delete IS NULL AND client_id = $cl_id AND invoice_no LIKE '%$search_text%' AND bid_wo_no LIKE '%$search_text%')";
                    }                   
                }else{
                    if($cl_set == ''){
                        $search_string = "invoice_type = 2 AND is_delete IS NULL" . $estimate_query_unsettled;
                    }else if($cl_set == 'set'){
                        $search_string = "invoice_type = 2 AND is_approve = 1 AND is_delete IS NULL AND client_id = $cl_id". $estimate_query_unsettled;
                    }   
                }
                $draw = $_REQUEST['draw'];                
               
                $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);
                $total_array = array();
                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'];
                        $prop_id = $row_bid_item['prop_id'];
                        $invoice_amount = $row_bid_item['invoice_amount'];
                        $client_id = $row_bid_item['client_id'];
                        $emp_id = $row_bid_item['employee_id'];
                        $adm_id = $row_bid_item['admin_id'];
                        $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'];
                        }
                    }
                        $bid_wo_no = $row_bid_item['bid_wo_no'];
                        $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'];
                        
                        $prop_case_num_row = '';

                        if(isset($prop_case_num)){
                            $prop_case_num_row = $prop_case_num_row . $prop_case_num;
                        }
                        $bid_wo_no_row = '';

                        if(isset($bid_wo_no)){
                            $bid_wo_no_row = $bid_wo_no_row . $bid_wo_no;
                        }                           
                        
                        $staff_row = '';

                        if(isset($staff_name)){
                            $staff_row = $staff_row . $staff_name;
                        }
                        if($user_type == 'admin'){  
                            $staff_row = $staff_row . "<br><a href='item_change_log.php?item_no=$invoice_id&type=inv'>Change History</a>";
                        }                          

                        $delete =  "<a class='btn btn-danger tbl_e_narrow' href='view-invoices.php?delete=$invoice_id' id = ''>Delete</a>";

                        $edit = "<a class='btn btn-default tbl_e_narrow' href='invoice_finalize.php?in_no=$invoice_id' id = ''>Edit</a>";
                
                        $download = "<a class='btn btn-success tbl_e_narrow' href='invoice-maker.php?in_no=$invoice_id&state=download' id = ''>Download</a>";
                        
                        $data = array($date, $invoice_no, $client_name, $prop_case_num_row, $prop_full_add, $prop_city,$prop_state,$prop_zip, $prop_county, $bid_wo_no_row, $invoice_amount, $staff_row, $delete, $edit, $download);
                        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);
            }
        }
    }
