<?php
// error_reporting(E_ALL);
// ini_set('display_errors', '1');
class ItemManage
{
    private $db_con;
    private $db_pdo;

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

    public function manage_item_file($file_display_name, $input_data): array
    {

        $tmp_unique_id = uniqid();

        $tmp_upload_file = $this->base64_to_file($input_data);

        if (!$tmp_upload_file[0]) {
            return [false, "Base64 to File Error"];
        } else {
            $tmp_file_name = $tmp_upload_file[1];
        }

        $query_sql = /** @lang text */
            "INSERT INTO " . tbl_file . " (uid, display_name, file_name) VALUES ('$tmp_unique_id', ";
        $query_sql .= /** @lang text */
            "'$file_display_name', '$tmp_file_name')";


        return [mysqli_query($this->db_con, $query_sql) or die(mysqli_error($this->db_con)), $tmp_unique_id];

    }



    //Handle Inspector document
    public function manage_item_inspector_doc($file_display_name, $input_data, $data): array
    {


        // echo "Welcome to manage_item_inspector_doc function now. Do everything here";
        // echo "<pre>"; print_r($file_display_name); echo "</pre>";
        // echo "<pre>"; print_r($input_data); echo "</pre>";
        // echo "<pre>"; print_r($data); echo "</pre>";
        // die;


        
        $uuid = $data->usr_id;



        $user_details_sql = /** @lang text */
                "SELECT first_name, last_name FROM " . tbl_usr . " WHERE uid = '".$uuid."'";
        
        
        $user_details_query = mysqli_query($this->db_con, $user_details_sql);

        $user_details = mysqli_fetch_assoc($user_details_query);

        $user_folder_name = $user_details['first_name']. ' '. $user_details['last_name']; 
        $tmp_unique_id = uniqid();
        $user_uid = $uuid;
        $tmp_upload_file = $this->base64_to_file_inspector_doc($input_data, trim(ucwords($user_folder_name)), $data);
        // echo "tmp_upload_file from base 64 is  ";
        // echo "<pre>"; print_r($tmp_upload_file); echo "</pre>";
        // die;
        if (!$tmp_upload_file[0]) {
            return [false, "Base64 to File Error"];
        } else {
            $tmp_file_name = $tmp_upload_file[1];
        }

        $query_sql = /** @lang text */
            "INSERT INTO " . tbl_user_docs . " (uid, display_name, file_name, user_uid) VALUES ('$tmp_unique_id', ";
        $query_sql .= /** @lang text */
            "'$file_display_name', '$tmp_file_name', '$uuid')";
        
            //echo $query_sql; die;
        return [mysqli_query($this->db_con, $query_sql) or die(mysqli_error($this->db_con)), $tmp_unique_id];

    }
    //Ends here

    private function base64_to_file($file_data): array
    {
        $tmp_file_type = explode(';', explode(',', substr($file_data, 5), 2)[0], 2)[0];
        $mime_split = explode('/', $tmp_file_type, 2);
        $tmp_file_name = uniqid() . "." . $mime_split[1];

        return [file_put_contents(file_via_loc . $tmp_file_name, file_get_contents($file_data)), $tmp_file_name];
    }

    private function base64_to_file_inspector_doc($file_data, $user_folder_name, $data): array
    {
        // echo "<pre>"; print_r($data); echo "</pre>";
        // die;
        $tmp_file_type = explode(';', explode(',', substr($file_data, 5), 2)[0], 2)[0];
        $mime_split = explode('/', $tmp_file_type, 2);
        $tmp_file_name = uniqid() . "." . $mime_split[1];
        if (!file_exists(docs_via_loc.'/'.$user_folder_name.'/')) {
            mkdir(docs_via_loc.'/'.$user_folder_name.'/', 0777, true);
        }
        $location = docs_via_loc.'/'.$user_folder_name.'/';
        return [file_put_contents($location . $tmp_file_name, file_get_contents($file_data)), $tmp_file_name];
    }

    public function manage_item_user($manageType, $input_data)
    {
        // echo "manageType is updated here in final stage before updating  ".$manageType;
        // echo "Input data ";
        // echo "<pre>"; print_r($input_data); echo "</pre>";
        // die;

        $item_details = new ItemDetails();
        $query_sql = '';

        if ($manageType == "add") {
            $tmp_unique_id = uniqid();
            $tmp_usr_auth_id = uniqid() . substr(str_shuffle("1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcefghijklmnopqrstuvwxyz"), 0, 10) . uniqid();
        } else {
            $tmp_unique_id = $input_data->usr_id;
            $tmp_usr_auth_id = $input_data->usr_auth;
        }

        $tmp_verification_code = rand(1000, 9999);
        $tmp_reset_code = rand(1000, 9999);
        $tmp_reset_time = date('Y-m-d H:i:s', strtotime("+15 minutes"));

        $tmp_usr_first_name = strtolower($input_data->usr_first_name);
        $tmp_usr_last_name = strtolower($input_data->usr_last_name);
        $tmp_usr_mail = strtolower($input_data->usr_mail);
        $tmp_usr_commission_mode = $input_data->is_commission_mode;
        $tmp_usr_commission_rate = $input_data->commission_rate;
        $tmp_usr_mail = strtolower($input_data->usr_mail);
        $tmp_usr_password = isset($input_data->usr_password) ? password_hash($input_data->usr_password, CRYPT_SHA512):"";
        $tmp_usr_alias = $input_data->usr_alias;
        $tmp_usr_salary = isset($input_data->usr_salary)?$input_data->usr_salary:0;
        $tmp_usr_type = $input_data->usr_type;
        $tmp_usr_verification_status = $input_data->usr_verification_status;
        $tmp_usr_access_level = $input_data->usr_access_level;
        $tmp_usr_status = $input_data->usr_status;

        $tmp_usr_img_id = isset($input_data->usr_img_id) ? $input_data->usr_img_id : "";
        // Updates for Manage Inspector Update section
        // Ends here
        $tmp_is_inspector_detail = isset($input_data->inspector_details_flag) ? $input_data->inspector_details_flag : "";
        $tmp_usr_dob = isset($input_data->usr_dob) ? $input_data->usr_dob : "";
        $tmp_usr_phone = isset($input_data->usr_phone) ? $input_data->usr_phone : "";
        $tmp_usr_inspector_account_type = isset($input_data->usr_inspector_account_type) ? $input_data->usr_inspector_account_type : "";
        $tmp_usr_business_name = isset($input_data->usr_business_name) ? $input_data->usr_business_name : "";
        $tmp_usr_document_type = isset($input_data->usr_document_type) ? $input_data->usr_document_type : "";
        $tmp_usr_social_identification_number = isset($input_data->usr_social_identification_number) ? $input_data->usr_social_identification_number : "";
        $tmp_usr_address = isset($input_data->usr_address) ? $input_data->usr_address : "";
        $tmp_usr_notes = isset($input_data->usr_notes) ? $input_data->usr_notes : "";
        $tmp_usr_bank_name = isset($input_data->usr_bank_name) ? $input_data->usr_bank_name : "";
        $tmp_usr_account_number = isset($input_data->usr_account_number) ? $input_data->usr_account_number : "";
        $tmp_usr_routing_number = isset($input_data->usr_routing_number) ? $input_data->usr_routing_number : "";
        


        if ($manageType == "add") {
            $query_sql = /** @lang text */
                "INSERT INTO " . tbl_usr . " (uid, authorize_token, first_name, last_name, mail, usr_type, ";
            $query_sql .= /** @lang text */
                "verification_code, verification_status, access_level, alias, salary, is_commission_mode, commission_rate) VALUES (";
            $query_sql .= /** @lang text */
                "'$tmp_unique_id', '$tmp_usr_auth_id', '$tmp_usr_first_name', '$tmp_usr_last_name', '$tmp_usr_mail', '$tmp_usr_type', ";
            $query_sql .= /** @lang text */
                "'$tmp_verification_code', '$tmp_usr_verification_status', '$tmp_usr_access_level', '$tmp_usr_alias', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_salary', '$tmp_usr_commission_mode', '$tmp_usr_commission_rate')";
        } else if ($manageType == "edit") {
            if($tmp_is_inspector_detail && $tmp_is_inspector_detail == true){
                $query_sql = /** @lang text */
                    "UPDATE " . tbl_usr . " SET dob= '$tmp_usr_dob', phone= '$tmp_usr_phone', inspector_account_type= '$tmp_usr_inspector_account_type', business_name= '$tmp_usr_business_name', document_type= '$tmp_usr_document_type', social_identification_number= '$tmp_usr_social_identification_number', address= '$tmp_usr_address', notes= '$tmp_usr_notes', bank_name= '$tmp_usr_bank_name', account_number= '$tmp_usr_account_number', routing_number= '$tmp_usr_routing_number'";
                $query_sql .= /** @lang text */
                    " WHERE uid= '$tmp_unique_id'";
            } else {
                $query_sql = /** @lang text */
                    "UPDATE " . tbl_usr . " SET first_name= '$tmp_usr_first_name', last_name= '$tmp_usr_last_name', ";
                $query_sql .= /** @lang text */
                    "alias= '$tmp_usr_alias', salary= '$tmp_usr_salary', mail= '$tmp_usr_mail', ";
                $query_sql .= /** @lang text */
                    "is_commission_mode= '$tmp_usr_commission_mode', commission_rate= '$tmp_usr_commission_rate'";
                $query_sql .= /** @lang text */
                    " WHERE uid= '$tmp_unique_id'";
            }

            
        } else if ($manageType == "update") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_usr . " SET first_name= '$tmp_usr_first_name', last_name= '$tmp_usr_last_name' ";
            $query_sql .= /** @lang text */
                "WHERE authorize_token= '$tmp_usr_auth_id'";
        } else if ($manageType == "change-image") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_usr . " SET img_id= '$tmp_usr_img_id' WHERE authorize_token= '$tmp_usr_auth_id'";
        } else if ($manageType == "upload-docs") {
            //Get User uuid
           
            $user_profile_details_data = $item_details->details_item_user("uid='$input_data->usr_id'");

            $user_details['first_name'] = $user_profile_details_data[0]['first_name'];
            $user_details['last_name'] = $user_profile_details_data[0]['last_name'];
            
            $user_folder_name = $user_details['first_name']. ' '. $user_details['last_name'];

            $inspectpr_doc_details = $item_details->details_item_inspector_doc("user_uid='$tmp_unique_id'", ucwords(strtolower($user_folder_name)));
            return $inspectpr_doc_details;
        }else if ($manageType == "change-status") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_usr . " SET status= '$tmp_usr_status' WHERE uid= '$tmp_unique_id'";
        }
        else if ($manageType == "delete") {
            if($tmp_usr_type=="manager"){
                $query_sql = /** @lang text */
                    "DELETE FROM " . tbl_usr . " WHERE uid= '$tmp_unique_id'";
            }else{
                $query_sql = /** @lang text */
                    "UPDATE " . tbl_usr . " SET status= '$tmp_usr_status' WHERE uid= '$tmp_unique_id'";
            }
        }



        else if ($manageType == "verify") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_usr . " SET verification_status='true', password= '$tmp_usr_password' WHERE ";
            $query_sql .= /** @lang text */
                "mail= '$tmp_usr_mail'";
        } else if ($manageType == "re-verify") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_usr . " SET verification_code= '$tmp_verification_code' WHERE uid= '$tmp_unique_id'";
        } else if ($manageType == "reset-pass") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_usr . " SET reset_code= '$tmp_reset_code', reset_time= '$tmp_reset_time', ";
            $query_sql .= /** @lang text */
                "reset_status='true' WHERE mail = '$tmp_usr_mail'";
        } else if ($manageType == "update-pass") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_usr . " SET password= '$tmp_usr_password', reset_code='', reset_time='', ";
            $query_sql .= /** @lang text */
                "reset_status='false' WHERE authorize_token = '$tmp_usr_auth_id'";
        }
        return mysqli_query($this->db_con, $query_sql) or die($manageType . 'ERROR');
    }

    public function manage_item_csv($file_data, $file_column, $file_for)
    {
        $tmp_upload_file_data = $this->base64_to_file($file_data);

        if (!$tmp_upload_file_data[0]) {
            return false;
        } else {
            return $this->upload_csv_to_db($tmp_upload_file_data[1], $file_column, $file_for);
        }
    }

    private function upload_csv_to_db($file_name, $file_column, $file_for)
    {  
        // ini_set('display_errors', 1);
        // ini_set('display_startup_errors', 1);
        // error_reporting(E_ALL);
        $item_details = new ItemDetails();

        $tmp_csv_file = fopen(file_via_loc . $file_name, 'r');
        
        $tot_line = file(file_via_loc . $file_name, FILE_SKIP_EMPTY_LINES);
        array_shift($tot_line);
        fgetcsv($tmp_csv_file);
        $tmp_file_data = new stdClass();
        
        while (($tmp_csv_file_data = fgetcsv($tmp_csv_file)) !== false) {
            
            if (sizeof($tmp_csv_file_data) != $file_column) {
                return false;
            } else {
                if ($file_for == "price_list") {
                    // $tmp_file_data->usr_price_amount = strtolower($tmp_csv_file_data[0]);
                    // $tmp_file_data->usr_price_work_code = strtolower($tmp_csv_file_data[1]);
                    $tmp_file_data->usr_price_work_code = strtolower($tmp_csv_file_data[2]);
                    // $tmp_file_data->usr_price_zip_code = strtolower($tmp_csv_file_data[2]);
                    $tmp_file_data->usr_price_zip_code = strtolower($tmp_csv_file_data[3]);
                    // 0 -> price, 1 -> state, 2 -> work code, 3 -> zip
                    if (strpos($tmp_csv_file_data[0], "$") !== false) {
                        $tmp_file_data->usr_price_amount = explode("$", $tmp_csv_file_data[0])[1];
                    } else {
                        $tmp_file_data->usr_price_amount = $tmp_csv_file_data[0];
                    }

                    $add_price_list_sql="INSERT INTO ".tbl_price_list." (uid, amount, work_code, zip_code) VALUES('".uniqid()."', '".$tmp_file_data->usr_price_amount."','".$tmp_file_data->usr_price_work_code."', '".$tmp_file_data->usr_price_zip_code."') ON DUPLICATE KEY UPDATE amount = '".$tmp_file_data->usr_price_amount."'";
                    mysqli_query($this->db_con, $add_price_list_sql);

                } 

                else if ($file_for == "profit") {

                    // Converting dates
                    $order_date = $this->convertDateFormat($tmp_csv_file_data[2]);
                    $reassigned_date = $this->convertDateFormat($tmp_csv_file_data[3]);
                    $completion_date = $this->convertDateFormat($tmp_csv_file_data[4]);
                    $due_date = $this->convertDateFormat($tmp_csv_file_data[5]);
                    $invoice_date = $this->convertDateFormat($tmp_csv_file_data[6]);
                    $check_date = $this->convertDateFormat($tmp_csv_file_data[18]);



                    $tmp_file_data->usr_profit_work_order_id = strtolower($tmp_csv_file_data[0]);
                    $tmp_file_data->usr_profit_check_no = strtolower($tmp_csv_file_data[17]);
                    $tmp_file_data->usr_profit_work_code = strtolower($tmp_csv_file_data[1]);
                    $tmp_file_data->usr_profit_main_vendor = strtolower($tmp_csv_file_data[9]);
                    $tmp_file_data->usr_profit_sub_vendor = strtolower($tmp_csv_file_data[10]);
                    $tmp_file_data->usr_profit_address = str_replace("'", "\'", strtolower($tmp_csv_file_data[11]));
                    $tmp_file_data->usr_profit_state = str_replace("'", "\'", strtolower($tmp_csv_file_data[13]));
                    $tmp_file_data->usr_profit_city = str_replace("'", "\'", strtolower($tmp_csv_file_data[12]));
                    $tmp_file_data->usr_profit_zone = strtolower($tmp_csv_file_data[15]);
                    $tmp_file_data->usr_profit_zip_code = strtolower($tmp_csv_file_data[14]);
                    $tmp_file_data->usr_profit_order_date = $order_date;
                    $tmp_file_data->usr_profit_reassigned_date = $reassigned_date;
                    $tmp_file_data->usr_profit_complete_date = $completion_date;
                    $tmp_file_data->usr_profit_due_date = date('n/j/y', strtotime($due_date));
                    $tmp_file_data->usr_profit_billed_date = date('n/j/y', strtotime($invoice_date));
                    $tmp_file_data->usr_profit_check_date = $check_date;

                    
                    if (strpos($tmp_csv_file_data[7], "$") !== false) {
                        $tmp_file_data->usr_profit_expected_amount = explode("$", $tmp_csv_file_data[7])[1];
                    } else {
                        $tmp_file_data->usr_profit_expected_amount = $tmp_csv_file_data[7];
                    }

                    if (strpos($tmp_csv_file_data[8], "$") !== false) {
                        $tmp_file_data->usr_profit_actual_amount = explode("$", $tmp_csv_file_data[8])[1];
                    } else {
                        $tmp_file_data->usr_profit_actual_amount = $tmp_csv_file_data[8];
                    }

                    //Ends here

                    //echo "<pre>"; print_r($tmp_file_data); echo "</pre>";
                    $add_profit_sql="INSERT INTO ".tbl_profit." (uid, work_order_id, check_no, work_code, expected_amount, actual_amount, main_vendor, sub_vendor, address, state, city, zone, zip_code, order_date, reassigned_date, complete_date, due_date, billed_date, check_date) VALUES('" . uniqid() . "', '".$tmp_file_data->usr_profit_work_order_id. "', '".$tmp_file_data->usr_profit_check_no. "', '".$tmp_file_data->usr_profit_work_code. "', '".$tmp_file_data->usr_profit_expected_amount. "', '".$tmp_file_data->usr_profit_actual_amount. "', '".$tmp_file_data->usr_profit_main_vendor. "', '".$tmp_file_data->usr_profit_sub_vendor. "', '".$tmp_file_data->usr_profit_address. "', '".$tmp_file_data->usr_profit_state. "', '".$tmp_file_data->usr_profit_city. "', '".$tmp_file_data->usr_profit_zone. "', '".$tmp_file_data->usr_profit_zip_code. "', '".$tmp_file_data->usr_profit_order_date. "', '".$tmp_file_data->usr_profit_reassigned_date. "', '".$tmp_file_data->usr_profit_complete_date. "', '".$tmp_file_data->usr_profit_due_date. "', '".$tmp_file_data->usr_profit_billed_date. "', '".$tmp_file_data->usr_profit_check_date."') ON DUPLICATE KEY UPDATE check_no= '".$tmp_file_data->usr_profit_check_no."', work_code= '".$tmp_file_data->usr_profit_work_code."', expected_amount= '".$tmp_file_data->usr_profit_expected_amount."', actual_amount= '".$tmp_file_data->usr_profit_actual_amount."', main_vendor= '".$tmp_file_data->usr_profit_main_vendor."', sub_vendor= '".$tmp_file_data->usr_profit_sub_vendor."', address= '".$tmp_file_data->usr_profit_address."', state= '".$tmp_file_data->usr_profit_state."', city= '".$tmp_file_data->usr_profit_city."', zone= '".$tmp_file_data->usr_profit_zone."', zip_code= '".$tmp_file_data->usr_profit_zip_code."', order_date= '".$tmp_file_data->usr_profit_order_date."', reassigned_date= '".$tmp_file_data->usr_profit_reassigned_date."', complete_date= '".$tmp_file_data->usr_profit_complete_date."', due_date= '".$tmp_file_data->usr_profit_due_date."', billed_date= '".$tmp_file_data->usr_profit_billed_date."', check_date= '".$tmp_file_data->usr_profit_check_date."'";
                    mysqli_query($this->db_con, $add_profit_sql);
                    //echo "=================================";
                    //echo $add_profit_sql;
                    //echo "=================================";

                } else if ($file_for == "work_order") {
                    // echo "<pre>"; print_r($tmp_csv_file_data); echo "</pre>";exit;
                    if(strtolower($tmp_csv_file_data[0]) == "order number"){
                        continue;
                    }
                 
                    // <pre>Array
                    // (
                    //     [0] => Order Number
                    //     [1] => Work Code
                    //     [2] => Order Date
                    //     [3] => Reassigned Date
                    //     [4] => Completion Date
                    //     [5] => Due Date
                    //     [6] => Invoice Date
                    //     [7] => Vendor Total
                    //     [8] => Payment Amount
                    //     [9] => Main Vendor Code
                    //     [10] => Performed
                    //     [11] => Address 1
                    //     [12] => City
                    //     [13] => State
                    //     [14] => Zip
                    //     [15] => Zone
                    //     [16] => Invoice Id
                    //     [17] => Check Number
                    //     [18] => Check Date
                    //     [19] => Exterior ISO - was pre-notification call made internally
                    //     [20] => Interior ISO � was appointment scheduled internally
                    // )
                    // </pre>

                    $word_order_unique_id = uniqid();

                    $work_order_id = mb_convert_encoding(strtolower($tmp_csv_file_data[0]), 'UTF-8', 'UTF-8'); // Order Number
                    $work_order_work_code = mb_convert_encoding(strtolower($tmp_csv_file_data[1]), 'UTF-8', 'UTF-8'); // Work Code
                    $work_order_complete_date = date('Y-m-d', strtotime(mb_convert_encoding($tmp_csv_file_data[4], 'UTF-8', 'UTF-8'))); // Completion Date
                    $work_order_amount = mb_convert_encoding($tmp_csv_file_data[7], 'UTF-8', 'UTF-8'); // Vendor Total
                    $work_order_alias = mb_convert_encoding(strtolower($tmp_csv_file_data[10]), 'UTF-8', 'UTF-8'); // Performed
                    $work_order_address = mb_convert_encoding(str_replace("'", "\'", strtolower($tmp_csv_file_data[11])), 'UTF-8', 'UTF-8'); // Address
                    $work_order_city = mb_convert_encoding(str_replace("'", "\'", strtolower($tmp_csv_file_data[12])), 'UTF-8', 'UTF-8'); // City
                    $work_order_state = mb_convert_encoding(strtoupper($tmp_csv_file_data[13]), 'UTF-8', 'UTF-8'); // State
                    $work_order_zip = mb_convert_encoding(strtolower($tmp_csv_file_data[14]), 'UTF-8', 'UTF-8'); // Zip Code
                    // $work_order_invoice_id = mb_convert_encoding(strtolower($tmp_csv_file_data[16]), 'UTF-8', 'UTF-8'); // Invoice Id
                    $work_order_check_date = mb_convert_encoding($tmp_csv_file_data[18], 'UTF-8', 'UTF-8'); // Check Date

                    if (strpos($work_order_amount, "$") !== false) {
                        $work_order_amount = explode("$", $work_order_amount)[1];
                    }

                    $work_order_is_paid = 0;
                    if(isset($work_order_check_date) && $work_order_check_date != ""){
                        $work_order_check_date = date('Y-m-d', strtotime($work_order_check_date));
                        $work_order_is_paid = 1;
                    }

                    $add_work_order_sql="INSERT INTO ".tbl_work_order." 
                        (
                            uid,
                            work_order_id,
                            work_code,
                            amount,
                            alias,
                            address,
                            city,
                            zip_code,
                            state,
                            completion_date,
                            check_date,
                            is_paid
                        )
                        VALUES (
                            :word_order_unique_id,
                            :work_order_id,
                            :work_order_work_code,
                            :work_order_amount,
                            :work_order_alias,
                            :work_order_address,
                            :work_order_city,
                            :work_order_zip,
                            :work_order_state,
                            :work_order_complete_date,
                            :work_order_check_date,
                            :work_order_is_paid
                        )
                        
                        ON DUPLICATE KEY 
                        UPDATE 
                        work_code= :work_order_work_code,
                        amount= :work_order_amount,
                        alias= :work_order_alias,
                        address= :work_order_address,
                        city= :work_order_city,
                        zip_code= :work_order_zip,
                        state= :work_order_state,
                        completion_date= :work_order_complete_date,
                        check_date= :work_order_check_date,
                        is_paid= :work_order_is_paid";

                    $stmt = $this->db_pdo->prepare($add_work_order_sql);
                    $stmt->bindParam(':word_order_unique_id', $word_order_unique_id);
                    $stmt->bindParam(':work_order_id', $work_order_id);
                    $stmt->bindParam(':work_order_work_code', $work_order_work_code);
                    $stmt->bindParam(':work_order_amount', $work_order_amount);
                    $stmt->bindParam(':work_order_alias', $work_order_alias);
                    $stmt->bindParam(':work_order_address', $work_order_address);
                    $stmt->bindParam(':work_order_city', $work_order_city);
                    $stmt->bindParam(':work_order_zip', $work_order_zip);
                    $stmt->bindParam(':work_order_state', $work_order_state);
                    $stmt->bindParam(':work_order_complete_date', $work_order_complete_date);
                    $stmt->bindParam(':work_order_check_date', $work_order_check_date);
                    $stmt->bindParam(':work_order_is_paid', $work_order_is_paid);

                    $stmt->execute();
                }
            }
        }

/*
        if ($temp_count == sizeof($tot_line)) {
            $tmp_query = substr($tmp_query, 0, -1);
            $tmp_query_sql = "";
            if ($file_for == "price_list--") {
                $tmp_query_sql =
                    "INSERT INTO " . tbl_price_list . " (uid, amount, work_code, zip_code) VALUES " . $tmp_query;
            } else if ($file_for == "profit") {
                $tmp_query_sql =
                    "INSERT INTO " . tbl_profit . " (uid, work_order_id, check_no, work_code, expected_amount, actual_amount, main_vendor, sub_vendor, address, state, city, zone, zip_code, order_date, reassigned_date, complete_date, due_date, billed_date, check_date) VALUES " . $tmp_query;
            } else if ($file_for == "work_order") {
                $tmp_query_sql =
                    "INSERT INTO " . tbl_work_order . " (uid, work_order_id, invoice_id, work_code, amount, alias, address, city, zip_code, invoice_date, completion_date) VALUES " . $tmp_query;
            }

            $add_status = mysqli_query($this->db_con, $tmp_query_sql);
        }
*/

        if (fclose($tmp_csv_file)) {
            if (unlink(file_via_loc . $file_name)) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }

    }

    public function manage_item_price($manageType, $input_data)
    {
        $query_sql = '';

        if ($manageType == "add") {
            $tmp_unique_id = uniqid();
        } else {
            $tmp_unique_id = $input_data->price_id;
        }

        $tmp_usr_price_work_code = strtolower($input_data->usr_price_work_code);
        $tmp_usr_price_amount = strtolower($input_data->usr_price_amount);
        $tmp_usr_price_zip_code = strtolower($input_data->usr_price_zip_code);


        if ($manageType == "add") {
            $query_sql = /** @lang text */
                "INSERT INTO " . tbl_price_list . " (uid, work_code, amount, zip_code) VALUES ('$tmp_unique_id', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_price_work_code', '$tmp_usr_price_amount', '$tmp_usr_price_zip_code')";
        } else if ($manageType == "edit") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_price_list . " SET work_code= '$tmp_usr_price_work_code', ";
            $query_sql .= /** @lang text */
                "amount= '$tmp_usr_price_amount', zip_code= '$tmp_usr_price_zip_code' WHERE uid= '$tmp_unique_id'";
        } else if ($manageType == "delete") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_price_list . " SET status='false' WHERE uid= '$tmp_unique_id'";
        }

        return mysqli_query($this->db_con, $query_sql) or die(mysqli_error($this->db_con));
    }

    public function manage_item_profit($manageType, $input_data)
    {

        $query_sql = '';

        if ($manageType == "add") {
            $tmp_unique_id = uniqid();
        } else {
            $tmp_unique_id = $input_data->profit_id;
        }

        $tmp_usr_profit_work_order_id = strtolower($input_data->usr_profit_work_order_id);
        $tmp_usr_profit_check_no = strtolower($input_data->usr_profit_check_no);
        $tmp_usr_profit_work_code = strtolower($input_data->usr_profit_work_code);
        $tmp_usr_profit_expected_amount = $input_data->usr_profit_expected_amount;
        $tmp_usr_profit_actual_amount = $input_data->usr_profit_actual_amount;
        $tmp_usr_profit_main_vendor = strtolower($input_data->usr_profit_main_vendor);
        $tmp_usr_profit_sub_vendor = strtolower($input_data->usr_profit_sub_vendor);
        $tmp_usr_profit_address = strtolower($input_data->usr_profit_address);
        $tmp_usr_profit_state = strtolower($input_data->usr_profit_state);
        $tmp_usr_profit_city = strtolower($input_data->usr_profit_city);
        $tmp_usr_profit_zone = strtolower($input_data->usr_profit_zone);
        $tmp_usr_profit_zip_code = strtolower($input_data->usr_profit_zip_code);


        $tmp_usr_profit_order_date = $input_data->usr_profit_order_date;
        $tmp_usr_profit_reassigned_date = $input_data->usr_profit_reassigned_date;
        $tmp_usr_profit_complete_date = $input_data->usr_profit_complete_date;
        $tmp_usr_profit_due_date = $input_data->usr_profit_due_date;
        $tmp_usr_profit_billed_date = $input_data->usr_profit_billed_date;
        $tmp_usr_profit_check_date = $input_data->usr_profit_check_date;


        if ($manageType == "add") {
            $query_sql = /** @lang text */
                "INSERT INTO " . tbl_profit . " (uid, work_order_id, check_no, work_code, expected_amount, ";
            $query_sql .= /** @lang text */
                "actual_amount, main_vendor, sub_vendor, address, state, city, zone, zip_code, order_date, ";
            $query_sql .= /** @lang text */
                "reassigned_date, complete_date, due_date, billed_date, check_date) ";
            $query_sql .= /** @lang text */
                "VALUES ('$tmp_unique_id', '$tmp_usr_profit_work_order_id', '$tmp_usr_profit_check_no', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_profit_work_code', '$tmp_usr_profit_expected_amount', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_profit_actual_amount', '$tmp_usr_profit_main_vendor', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_profit_sub_vendor', '$tmp_usr_profit_address', '$tmp_usr_profit_state', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_profit_city,' '$tmp_usr_profit_zone', '$tmp_usr_profit_zip_code', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_profit_order_date', '$tmp_usr_profit_reassigned_date', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_profit_complete_date', '$tmp_usr_profit_due_date', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_profit_billed_date', '$tmp_usr_profit_check_date')";
        } else if ($manageType == "edit") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_profit . " SET work_order_id= '$tmp_usr_profit_work_order_id', ";
            $query_sql .= /** @lang text */
                "check_no= '$tmp_usr_profit_check_no', work_code= '$tmp_usr_profit_work_code', ";
            $query_sql .= /** @lang text */
                "expected_amount= '$tmp_usr_profit_expected_amount', ";
            $query_sql .= /** @lang text */
                "actual_amount= '$tmp_usr_profit_actual_amount', main_vendor= '$tmp_usr_profit_main_vendor', ";
            $query_sql .= /** @lang text */
                "sub_vendor= '$tmp_usr_profit_sub_vendor', address= '$tmp_usr_profit_address', ";
            $query_sql .= /** @lang text */
                "state= '$tmp_usr_profit_state', city= '$tmp_usr_profit_city', ";
            $query_sql .= /** @lang text */
                "zone= '$tmp_usr_profit_zone', zip_code= '$tmp_usr_profit_zip_code', ";
            $query_sql .= /** @lang text */
                "order_date= '$tmp_usr_profit_order_date', ";
            $query_sql .= /** @lang text */
                "reassigned_date= '$tmp_usr_profit_reassigned_date', ";
            $query_sql .= /** @lang text */
                "complete_date= '$tmp_usr_profit_complete_date', due_date= '$tmp_usr_profit_due_date', ";
            $query_sql .= /** @lang text */
                "billed_date= '$tmp_usr_profit_billed_date', check_date= '$tmp_usr_profit_check_date' ";
            $query_sql .= /** @lang text */
                "WHERE uid= '$tmp_unique_id'";
        } else if ($manageType == "delete") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_profit . " SET status='false' WHERE uid= '$tmp_unique_id'";
        }


        return mysqli_query($this->db_con, $query_sql) or die($manageType . 'ERROR');
    }

    public function manage_item_work_order($manageType, $input_data)
    {
//   ini_set('display_errors', 1);
//         ini_set('display_startup_errors', 1);
//         error_reporting(E_ALL);
        $query_sql = '';

        if ($manageType == "add") {
            $tmp_unique_id = uniqid();
        } else {
            $tmp_unique_id = $input_data->work_id;
        }

        // echo '<pre>', print_r($input_data);exit;

        $tmp_usr_work_order_id = strtolower($input_data->usr_work_order_id);
        $tmp_usr_work_order_work_code = strtolower($input_data->usr_work_order_work_code);
        $tmp_usr_work_order_amount = strtolower($input_data->usr_work_order_amount);
        $tmp_usr_work_order_complete_date = date('Y-m-d', strtotime($input_data->usr_work_order_complete_date));
        $tmp_usr_work_order_alias = strtolower($input_data->usr_work_order_alias);
        $tmp_usr_work_order_address = strtolower($input_data->usr_work_order_address);
        $tmp_usr_work_order_city = strtolower($input_data->usr_work_order_city);
        $tmp_usr_work_order_zip = strtolower($input_data->usr_work_order_zip);
        $tmp_usr_work_order_state = strtoupper($input_data->usr_work_order_state);
        $tmp_usr_work_order_invoice_id = strtolower($input_data->usr_work_order_invoice_id);
        $tmp_usr_work_order_invoice_date = date('Y-m-d', strtotime($input_data->usr_work_order_invoice_date));

        // echo $tmp_usr_work_order_state;exit;


        if ($manageType == "add") {
            $query_sql = /** @lang text */
                "INSERT INTO " . tbl_work_order . " (uid, work_order_id, invoice_id, work_code, amount, alias, ";
            $query_sql .= /** @lang text */
                "address, city, zip_code, state , invoice_date, completion_date) VALUES ('$tmp_unique_id', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_work_order_id', '$tmp_usr_work_order_invoice_id', '$tmp_usr_work_order_work_code', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_work_order_amount', '$tmp_usr_work_order_alias', '$tmp_usr_work_order_address', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_work_order_city', '$tmp_usr_work_order_zip', '$tmp_usr_work_order_state', '$tmp_usr_work_order_invoice_date', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_work_order_complete_date')";


                // echo $query_sql;exit;
        } else if ($manageType == "edit") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_work_order . " SET work_order_id= '$tmp_usr_work_order_id', ";
            $query_sql .= /** @lang text */
                "invoice_id= '$tmp_usr_work_order_invoice_id', work_code= '$tmp_usr_work_order_work_code', ";
            $query_sql .= /** @lang text */
                "amount= '$tmp_usr_work_order_amount', alias= '$tmp_usr_work_order_alias', ";
            $query_sql .= /** @lang text */
                "address= '$tmp_usr_work_order_address', city= '$tmp_usr_work_order_city', ";
            $query_sql .= /** @lang text */
                "zip_code= '$tmp_usr_work_order_zip', state= '$tmp_usr_work_order_state', invoice_date= '$tmp_usr_work_order_invoice_date', ";
            $query_sql .= /** @lang text */
                "completion_date= '$tmp_usr_work_order_complete_date' WHERE uid= '$tmp_unique_id'";
        } else if ($manageType == "delete") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_work_order . " SET status='false' WHERE uid= '$tmp_unique_id'";
        }

        return mysqli_query($this->db_con, $query_sql) or die($manageType . 'ERROR');
    }

    public function manage_item_debit_credit($manageType, $input_data)
    {
        $query_sql = '';

        if ($manageType == "add") {
            $tmp_unique_id = uniqid();
        } else {
            $tmp_unique_id = $input_data->debit_credit_id;
        }

        $tmp_usr_debit_credit_inspector_id = strtolower($input_data->usr_debit_credit_inspector_id);
        $tmp_usr_debit_credit_date = date('Y-m-d', strtotime(strtolower($input_data->usr_debit_credit_date)));
        $tmp_usr_debit_credit_type = strtolower($input_data->usr_debit_credit_type);
        $tmp_usr_debit_credit_amount = $input_data->usr_debit_credit_amount;
        $tmp_usr_debit_credit_comment = $input_data->usr_debit_credit_comment;

        if ($manageType == "add") {
            $query_sql = /** @lang text */
                "INSERT INTO " . tbl_debit_credit . " (uid, usr_id, dc_date, dc_type, dc_amount, dc_comment) ";
            $query_sql .= /** @lang text */
                "VALUES ('$tmp_unique_id', '$tmp_usr_debit_credit_inspector_id', '$tmp_usr_debit_credit_date', ";
            $query_sql .= /** @lang text */
                "'$tmp_usr_debit_credit_type', '$tmp_usr_debit_credit_amount', '$tmp_usr_debit_credit_comment')";
        } else if ($manageType == "edit") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_debit_credit . " SET usr_id= '$tmp_usr_debit_credit_inspector_id', ";
            $query_sql .= /** @lang text */
                "dc_date= '$tmp_usr_debit_credit_date', dc_type= '$tmp_usr_debit_credit_type', ";
            $query_sql .= /** @lang text */
                "dc_amount= '$tmp_usr_debit_credit_amount', dc_comment= '$tmp_usr_debit_credit_comment' ";
            $query_sql .= /** @lang text */
                "WHERE uid= '$tmp_unique_id'";
        } else if ($manageType == "delete") {
            $query_sql = /** @lang text */
                "UPDATE " . tbl_debit_credit . " SET status='false' WHERE uid= '$tmp_unique_id'";
        }

        return mysqli_query($this->db_con, $query_sql) or die($manageType . 'ERROR');
    }

    public function delete_item_inspector_doc($input_data){
        $item_details = new ItemDetails();
        $user_profile_details_data = $item_details->details_item_user("uid='$input_data->inspector_id'");
        $user_details['first_name'] = $user_profile_details_data[0]['first_name'];
        $user_details['last_name'] = $user_profile_details_data[0]['last_name'];
        $user_folder_name = $user_details['first_name']. ' '. $user_details['last_name'];
        $inspectpr_doc_details = $item_details->details_item_inspector_doc("uid='$input_data->document_id'", ucwords(strtolower($user_folder_name)));
        if(count($inspectpr_doc_details) && count($inspectpr_doc_details) > 0){
            $document_path = docs_via_loc.ucwords(strtolower($user_folder_name)).'/'.$inspectpr_doc_details[0]['document_name'];
            if (file_exists($document_path)) {
                if(unlink($document_path)){
                    $query_sql = "DELETE FROM " . tbl_user_docs . " WHERE uid= '$input_data->document_id'";
                    return mysqli_query($this->db_con, $query_sql);
                }
             }
        }
    }

    public function erase_pricelist_pricing($input_data){
        if($input_data->user_resp && $input_data->user_resp === "delete me"){
            $query_sql = "TRUNCATE TABLE ". tbl_price_list;
            return mysqli_query($this->db_con, $query_sql);
            //return 1;
        }else{
            return 0;
        }
    }
    
    //Added on Aug 12, 2024 to format the date into previous/desirable format as the new csv upload for profit is in different format

    public function convertDateFormat($dateString) {
        // Create a DateTime object from the input date string
        $date = DateTime::createFromFormat('M j, Y', $dateString);
    
        // Check if the date was created successfully
        if (!$date) {
            return false; // or handle the error as needed
        }
    
        // Format the date into 'dd-mm-yyyy'
        //date('Y-m-d', strtotime($tmp_csv_file_data[1]));
        return $date->format('Y-m-d');
    }


}