<?php

class ItemDetails
{
    private $db_con;
    private $db_pdo;

    public function __construct()
    {
        global $con, $pdo;
        $this->db_con = $con;
        $this->db_pdo = $pdo;
    }

    public function details_item_user($search_term): array
    {
        $user_details_data = [];

        if ($search_term != '') {
            $search_term = ' WHERE ' . $search_term;
        } else {
            $search_term = " ORDER BY id DESC";
        }

        $user_details_sql = /** @lang text */
            "SELECT * FROM " . tbl_usr . $search_term;

        $user_details_query = mysqli_query($this->db_con, $user_details_sql);

        

        while ($user_details_arr = mysqli_fetch_assoc($user_details_query)) {

            if ($user_details_arr["img_id"] == "") {
                $tmp_usr_img = usr_placeholder_img;
            } else {
                $tmp_usr_img = $this->details_item_file($user_details_arr["img_id"]);
                $tmp_usr_img = $tmp_usr_img["file_data"];
            }

            if (filter_var($user_details_arr["access_level"], FILTER_VALIDATE_BOOLEAN)) {
                $tmp_access = "With Profit Module";
            } else {
                $tmp_access = "Without Profit Module";
            }


            $tmp_data = [
                "id" => $user_details_arr["uid"],
                "authorize_token" => $user_details_arr["authorize_token"],
                "first_name" => strtoupper($user_details_arr["first_name"]),
                "last_name" => strtoupper($user_details_arr["last_name"]),
                "is_commission_mode" => $user_details_arr["is_commission_mode"],
                "commission_rate" => $user_details_arr["commission_rate"],
                "user_img" => $tmp_usr_img,
                "mail" => $user_details_arr["mail"],
                "alias" => explode(",", strtoupper($user_details_arr["alias"])),
                "salary" => isset($user_details_arr["salary"])?$user_details_arr["salary"]:0,
                "password" => $user_details_arr["password"],
                "reset_code" => $user_details_arr["reset_code"],
                "reset_time" => $user_details_arr["reset_time"],
                "type" => $user_details_arr["usr_type"],
                "access_level" => ucwords($tmp_access),
                "verification_code" => $user_details_arr["verification_code"],
                "verification_status" => filter_var($user_details_arr["verification_status"], FILTER_VALIDATE_BOOLEAN),
                "status" => filter_var($user_details_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "reset_status" => filter_var($user_details_arr["reset_status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($user_details_arr["date_time"])),
                //Loading Inspector other Details
                "address" => $user_details_arr['address'],
                "notes" => $user_details_arr['notes'],
                "dob" => $user_details_arr["dob"],
                "document_type" => $user_details_arr['document_type'],
                "phone" => $user_details_arr["phone"],
                "inspector_account_type" => $user_details_arr["inspector_account_type"],
                "business_name" => $user_details_arr["business_name"],
                "social_identification_number" => $user_details_arr["social_identification_number"],
                "bank_name" => $user_details_arr["bank_name"],
                "account_number" => $user_details_arr["account_number"],
                "routing_number" => $user_details_arr["routing_number"]

            ];
            $user_details_data[] = $tmp_data;
        }
        return $user_details_data;
    }

    private function details_item_file($file_id): array
    {
        $file_data = [];

        $file_sql = /** @lang text */
            "SELECT * FROM " . tbl_file . " WHERE uid='" . $file_id . "' ORDER BY id DESC";

        $file_query = mysqli_query($this->db_con, $file_sql);

        while ($file_arr = mysqli_fetch_assoc($file_query)) {
            $tmp_data = [
                "id" => $file_arr["uid"],
                "file_name" => ucwords($file_arr["display_name"]),
                "file_data" => file_via_url . $file_arr["file_name"],
                "status" => filter_var($file_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($file_arr["date_time"]))
            ];
            $file_data = $tmp_data;
        }

        return $file_data;
    }

    public function details_item_price($search_term): array
    {
        $price_data = [];

        if ($search_term != '') {
            $search_term = ' WHERE ' . $search_term;
        } else {
            $search_term = " ORDER BY id DESC";
        }

        $price_sql = /** @lang text */
            "SELECT * FROM " . tbl_price_list . $search_term;

        $price_query = mysqli_query($this->db_con, $price_sql);
        while ($price_arr = mysqli_fetch_assoc($price_query)) {
            $state = $this->get_state_from_zip($price_arr['zip_code']);
            $tmp_data = [
                "id" => $price_arr["uid"],
                "work_code" => strtoupper($price_arr["work_code"]),
                "amount" => number_format((float)filter_var($price_arr["amount"], FILTER_VALIDATE_FLOAT), 2, '.', ','),
                "zip_code" => $price_arr["zip_code"],
                'state' => ucwords($state),
                "status" => filter_var($price_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($price_arr["date_time"]))
            ];
            $price_data[] = $tmp_data;
        }

        return $price_data;
    }

    public function details_item_profit($search_term): array
    {
        $profit_data = [];

        if ($search_term != '') {
            $search_term = ' WHERE ' . $search_term;
        } else {
            $search_term = " ORDER BY id DESC";
        }

        $profit_sql = /** @lang text */
            "SELECT * FROM " . tbl_profit . $search_term;

        $profit_query = mysqli_query($this->db_con, $profit_sql);

        while ($profit_arr = mysqli_fetch_assoc($profit_query)) {
            $tmp_data = [
                "id" => $profit_arr["uid"],
                "work_order_id" => strtoupper($profit_arr["work_order_id"]),
                "check_no" => strtoupper($profit_arr["check_no"]),
                "work_code" => strtoupper($profit_arr["work_code"]),
                "expected_amount" => number_format((float)filter_var($profit_arr["expected_amount"], FILTER_VALIDATE_FLOAT), 2, '.', ','),
                "actual_amount" => number_format((float)filter_var($profit_arr["actual_amount"], FILTER_VALIDATE_FLOAT), 2, '.', ','),
                "main_vendor" => strtoupper($profit_arr["main_vendor"]),
                "sub_vendor" => strtoupper($profit_arr["sub_vendor"]),
                "address" => strtoupper($profit_arr["address"]),
                "state" => strtoupper($profit_arr["state"]),
                "city" => strtoupper($profit_arr["city"]),
                "zone" => strtoupper($profit_arr["zone"]),
                "zip_code" => $profit_arr["zip_code"],
                "order_date" => date('m/d/Y', strtotime($profit_arr["order_date"])),
                "reassigned_date" => date('m/d/Y', strtotime($profit_arr["reassigned_date"])),
                "complete_date" => date('m/d/Y', strtotime($profit_arr["complete_date"])),
                "due_date" => date('m/d/Y', strtotime($profit_arr["due_date"])),
                "billed_date" => date('m/d/Y', strtotime($profit_arr["billed_date"])),
                "check_date" => date('m/d/Y', strtotime($profit_arr["check_date"])),
                "status" => filter_var($profit_arr["status, FILTER_VALIDATE_BOOLEAN"]),
                "date_time" => date('m/d/Y', strtotime($profit_arr["date_time"]))
            ];

            $profit_data[] = $tmp_data;
        }

        return $profit_data;
    }

    function formatDollarAmount($amount) {
        $absAmount = number_format(abs($amount), 2);
        return ($amount < 0 ? '-' : '') . '$' . $absAmount;
    }

    // Orders List
    public function work_order_list_detail($search_term, $limit_sql = "")
    {
        $work_order_data = [];

        $result = [];
        if ($search_term != '') {
            $search_term = ' WHERE ' . $search_term;
        } else {
            $search_term = " ORDER BY id DESC";
        }

        $work_order_sql ="SELECT a.*, b.amount as inspector_pay, c.is_commission_mode, c.commission_rate, CONCAT(c.first_name, ' ', c.last_name) as Inspector,b.id as price_id
                        FROM " . tbl_work_order . " a
                        LEFT JOIN ".tbl_price_list." b ON  a.work_code = b.work_code AND a.zip_code = b.zip_code AND b.status='true'
                        LEFT JOIN ".tbl_usr." c ON c.alias like CONCAT('%', a.alias, '%') AND c.status='true'
                        ";

        $work_order_sql .=  $search_term;

        if($limit_sql != "") {
            $work_order_sql_limit = "SELECT *
                FROM " . tbl_work_order . " a
                $search_term
            ";

            $total_rows = mysqli_query($this->db_con, $work_order_sql_limit);
            $total_rows = mysqli_num_rows($total_rows);
    
            if ($total_rows > 0) {
                $work_order_sql .= $limit_sql;
            }
            
            $result['pagination']['total_records'] = $total_rows;
        }


        $work_order_query = mysqli_query($this->db_con, $work_order_sql);

        while ($work_order_arr = mysqli_fetch_assoc($work_order_query)) {

            $amount = (float)filter_var($work_order_arr["amount"], FILTER_VALIDATE_FLOAT);
            $inspector_pay = (float)filter_var($work_order_arr["inspector_pay"], FILTER_VALIDATE_FLOAT);
            $commission_rate = (float)filter_var($work_order_arr["commission_rate"], FILTER_VALIDATE_FLOAT);
            $is_commission_mode = (int)$work_order_arr["is_commission_mode"];

            if ($is_commission_mode === 1) {
                $company_share = ($amount * $commission_rate) / 100;
                $inspector_pay = $amount - $company_share; 
                $profit_amount = $company_share; 
            } else {
                $profit_amount = $amount - $inspector_pay;
            }

            
            $tmp_data = [
                "id" => $work_order_arr["uid"],
                // "invoice_id" => $work_order_arr["invoice_id"],
                "work_order_id" => $work_order_arr["work_order_id"],
                "work_code" => strtoupper($work_order_arr["work_code"]),
                "alias" => strtoupper($work_order_arr["alias"]),
                "address" => ucwords($work_order_arr["address"]),
                "city" => $work_order_arr["city"],
                "zip_code" => $work_order_arr["zip_code"],
                "state" => $work_order_arr["state"],
                // "invoice_date" => date('m/d/Y', strtotime($work_order_arr["invoice_date"])),
                "completion_date" => date('m/d/Y', strtotime($work_order_arr["completion_date"])),
                "status" => filter_var($work_order_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($work_order_arr["date_time"])),
                "is_paid" => filter_var($work_order_arr["is_paid"], FILTER_VALIDATE_BOOLEAN),
                "check_date" => date('m/d/Y', strtotime($work_order_arr["check_date"])),
                "amount" => $this->formatDollarAmount($amount),
                "inspector_pay" => $this->formatDollarAmount($inspector_pay),
                "profit_amount" => $this->formatDollarAmount($profit_amount),
                "price_id" => strtoupper($work_order_arr["price_id"]),
                "inspector_name" => ucwords($work_order_arr["Inspector"]),
                "full_address" => ucwords($work_order_arr['address']) . ', '. ucwords($work_order_arr['city']). ', '. ucwords($work_order_arr['state']).', '.ucwords($work_order_arr['zip_code'])
            ];

            $work_order_data[] = $tmp_data;
        }

        $result['list'] = $work_order_data;
        
        return $result;
    }

    //Single Order
    public function work_order_detail($search_term)
    {
        $work_order_data = [];

        if ($search_term != '') {
            $search_term = ' WHERE ' . $search_term;
        }

        $work_order_sql = "SELECT *
                FROM " . tbl_work_order . "

                ";

        $work_order_sql .=  $search_term;

       

        $work_order_query = mysqli_query($this->db_con, $work_order_sql);

        while ($work_order_arr = mysqli_fetch_assoc($work_order_query)) {

            if($work_order_arr["amount"]==0){
                $tmp_amount="NOT PAID";
            }else{
                $tmp_amount = number_format((float)filter_var($work_order_arr["amount"], FILTER_VALIDATE_FLOAT), 2, '.', ',');
            }
            // $state = $this->get_state_from_zip($work_order_arr['zip_code']);
            $tmp_data = [
                "id" => $work_order_arr["uid"],
                // "invoice_id" => $work_order_arr["invoice_id"],
                "work_order_id" => $work_order_arr["work_order_id"],
                "work_code" => strtoupper($work_order_arr["work_code"]),
                "amount" => $tmp_amount,
                "alias" => strtoupper($work_order_arr["alias"]),
                "address" => ucwords($work_order_arr["address"]),
                "city" => $work_order_arr["city"],
                "zip_code" => $work_order_arr["zip_code"],
                "state" => $work_order_arr["state"],
                // "invoice_date" => date('m/d/Y', strtotime($work_order_arr["invoice_date"])),
                "completion_date" => date('m/d/Y', strtotime($work_order_arr["completion_date"])),
                "status" => filter_var($work_order_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($work_order_arr["date_time"])),
                "is_paid" => filter_var($work_order_arr["is_paid"], FILTER_VALIDATE_BOOLEAN),
                "check_date" => date('m/d/Y', strtotime($work_order_arr["check_date"])),
                "full_address" => ucwords($work_order_arr['address']) . ', '. ucwords($work_order_arr['city'])
            ];

            $work_order_data[] = $tmp_data;
        }

        return $work_order_data;
    }

    // Simple Order List for download
    public function work_order_list($search_term)
    {
        $work_order_data = [];

        if ($search_term != '') {
            $search_term = ' WHERE ' .$search_term;
        } else {
            $search_term = " ORDER BY id DESC";
        }

        $work_order_sql ="  SELECT  a.*,
                                    b.amount as inspector_pay, c.is_commission_mode, c.commission_rate,
                                    CONCAT(c.first_name, ' ', c.last_name) as Inspector,b.id as price_id
                            FROM " . tbl_work_order . " a
                            LEFT JOIN ".tbl_price_list." b ON  a.work_code = b.work_code AND a.zip_code = b.zip_code AND b.status='true'
                            LEFT JOIN ".tbl_usr." c ON c.alias like CONCAT('%', a.alias, '%') AND c.status='true'
                            ";

        $work_order_sql .=  $search_term;

        $work_order_query = mysqli_query($this->db_con, $work_order_sql);

        while ($work_order_arr = mysqli_fetch_assoc($work_order_query)) {
            $amount = (float)filter_var($work_order_arr["amount"], FILTER_VALIDATE_FLOAT);
            $inspector_pay = (float)filter_var($work_order_arr["inspector_pay"], FILTER_VALIDATE_FLOAT);
            $commission_rate = (float)filter_var($work_order_arr["commission_rate"], FILTER_VALIDATE_FLOAT);
            $is_commission_mode = (int)$work_order_arr["is_commission_mode"];

            if ($is_commission_mode === 1) {
                $company_share = ($amount * $commission_rate) / 100;
                $inspector_pay = $amount - $company_share; 
                $profit_amount = $company_share; 
            } else {
                $profit_amount = $amount - $inspector_pay;
            }
            
            $profit_amount = $amount - $inspector_pay;

            
            $tmp_data = [
                "id" => $work_order_arr["uid"],
                "invoice_id" => $work_order_arr["invoice_id"],
                "work_order_id" => $work_order_arr["work_order_id"],
                "work_code" => strtoupper($work_order_arr["work_code"]),
                "alias" => strtoupper($work_order_arr["alias"]),
                "address" => ucwords($work_order_arr["address"]),
                "city" => $work_order_arr["city"],
                "zip_code" => $work_order_arr["zip_code"],
                "state" => $work_order_arr["state"],
                "invoice_date" => date('m/d/Y', strtotime($work_order_arr["invoice_date"])),
                "completion_date" => date('m/d/Y', strtotime($work_order_arr["completion_date"])),
                "status" => filter_var($work_order_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($work_order_arr["date_time"])),
                "is_paid" => filter_var($work_order_arr["is_paid"], FILTER_VALIDATE_BOOLEAN),
                "check_date" => isset($work_order_arr["check_date"]) && $work_order_arr["check_date"] != ''? date('m/d/Y', strtotime($work_order_arr["check_date"])):'',
                "amount" => $this->formatDollarAmount($amount),
                "inspector_pay" => $this->formatDollarAmount($inspector_pay),
                "profit_amount" => $this->formatDollarAmount($profit_amount),
                "price_id" => strtoupper($work_order_arr["price_id"]),
                "inspector_name" => ucwords($work_order_arr["Inspector"]),
                "full_address" => ucwords($work_order_arr['address']) . ', '. ucwords($work_order_arr['city']). ', '. ucwords($work_order_arr['state']).', '.ucwords($work_order_arr['zip_code'])
            ];

            $work_order_data[] = $tmp_data;

        }


        return $work_order_data;
    }
    



    //Ends here
    public function details_item_debit_credit($search_term): array
    {
        $debit_credit_data = [];

        if ($search_term != '') {
            $search_term = ' WHERE ' . $search_term;
        } else {
            $search_term = " ORDER BY id DESC";
        }

        $debit_credit_sql = /** @lang text */
            "SELECT * FROM " . tbl_debit_credit . $search_term;


        $debit_credit_query = mysqli_query($this->db_con, $debit_credit_sql);

        while ($debit_credit_arr = mysqli_fetch_assoc($debit_credit_query)) {
            $tmp_data = [
                "id" => $debit_credit_arr["uid"],
                "inspector_id" => $debit_credit_arr["usr_id"],
                "date" => date('m/d/Y', strtotime($debit_credit_arr["dc_date"])),
                "type" => $debit_credit_arr["dc_type"],
                "amount" => number_format((float)filter_var($debit_credit_arr["dc_amount"], FILTER_VALIDATE_FLOAT), 2, '.', ','),
                "comment"=> $debit_credit_arr["dc_comment"],
                "status" => filter_var($debit_credit_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($debit_credit_arr["date_time"]))
            ];
            $debit_credit_data[] = $tmp_data;
        }


        return $debit_credit_data;
    }

    public function details_item_inspector_doc($search_term, $folder_name): array
    {
        $inspector_file_details_data = [];

        if ($search_term != '') {
            $search_term = ' WHERE ' . $search_term;
        } else {
            $search_term = " ORDER BY id DESC";
        }
        //$folder_name
        $location = docs_via_url.ucwords(strtolower($folder_name)).'/';
        $inspector_file_details_sql = "SELECT * FROM " . tbl_user_docs . $search_term;
        $inspector_file_details_query = mysqli_query($this->db_con, $inspector_file_details_sql);

        while ($file_arr = mysqli_fetch_assoc($inspector_file_details_query)) {
            $tmp_data = [
                "id" => $file_arr["uid"],
                "file_name" => ucwords($file_arr["display_name"]),
                "file_data" => $location . $file_arr["file_name"],
                "document_name" => $file_arr["file_name"],
                "status" => filter_var($file_arr["status"], FILTER_VALIDATE_BOOLEAN),
                "date_time" => date('m/d/Y', strtotime($file_arr["date_time"])),
                "user_uid" => $file_arr["user_uid"]
            ];
            $inspector_file_details_data[] = $tmp_data;
        }
        return $inspector_file_details_data;
    }

    public function get_state_from_zip($zip)
    {
        require_once($_SERVER['DOCUMENT_ROOT'].'api/clt_payroll/api/v1/lib/core/db/zip-states.php');
        if(ZIP_STATE[$zip]){
            return ZIP_STATE[$zip];
        }else{
            $zip = substr($zip, 1);
            return ZIP_STATE[$zip];
        }
        
        //echo "Zip passed is    ".$zip.'<hr/> and state returned is    '.ZIP_STATE[$zip].'<hr/>';
    }

}