<?php
include "include/db.php";
include "include/adminauth.php";
require 'vendor/autoload.php';
$successmessage = "";
$message = "";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;

function clean_input_data($cellVal){	
    include "include/db.php";
	$cellVal = trim($cellVal);
	$cellVal = mysqli_real_escape_string($conn, $cellVal);
	$cellVal = strtolower($cellVal);
    $cellVal = str_replace(' ', '', $cellVal);
    $cellVal = str_replace('*', '', $cellVal);
	return $cellVal;
}
function clean_input_data_sp($cellVal){	
    include "include/db.php";
	$cellVal = trim($cellVal);
	$cellVal = mysqli_real_escape_string($conn, $cellVal);
	$cellVal = strtolower($cellVal);
    $cellVal = str_replace('*', '', $cellVal);
	return $cellVal;
}

function clean_cell_input_data($cellVal1){	
    include "include/db.php";
	$cellVal1 = trim($cellVal1);
	$cellVal1 = mysqli_real_escape_string($conn, $cellVal1);
	return $cellVal1;
}

if(isset($_REQUEST['mess'])){
    $message = $_REQUEST['mess'];
}
if(isset($_REQUEST['upload_xl_bid'])){    
    $message = '';  
    $uniqueId= time().'-'.mt_rand();	
    $arr = $_FILES["fileToUpload_bid"]["name"];
    $arr1 = $arr[0];
    $temp = explode(".", $arr1);
    $pic_name =  $uniqueId . '.' . end($temp);
    $target_dir = getcwd().DIRECTORY_SEPARATOR. "reports/";
    $target_file = $target_dir . $pic_name;
    $imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
    $time = date("Y-m-d H:i:s");
    
    $arr2 = $_FILES["fileToUpload_bid"]["size"];
    $arr3 = $arr2[0];
    if($arr3 > 20000000){
        $message = $message . 'The file you uploaded is too large. File size should be less than 20 MB';
    }else{
        // Allow certain file formats
        if($imageFileType != "xlsx" && $imageFileType != "xls") {
            $pic_name = '';
            $message = $message . "The file you uploaded is not of xlsx/xls format. Please upload a XLSX/XLS file.";
        }else{
            $arr22 = $_FILES["fileToUpload_bid"]["tmp_name"];
            $arr32 = $arr22[0];
            if(move_uploaded_file($arr32, $target_file)) { 
                if($imageFileType == "xlsx"){
                    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
                }elseif($imageFileType == "xls"){
                    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
                }
                if($reader) {
                $reader->setReadDataOnly(true);
                $spreadsheet = $reader->load($target_file);  
                $sheetData = $spreadsheet->getActiveSheet()->toArray();
                $count = 0;
                $insert_row = 0;
                $assign_row = 0;
                foreach($sheetData as $row) {
                    if($count == 0){	
                        $setVal = 0;
                        $set = 0;
                        $stateRow = '';
                        $cityRow = '';
                        $loanRow = '';
                        $propertyaddressRow = '';
                        $zipRow = '';                        
                        $client_assign_set = '';

                        for($i=0; $i<1000; $i++){
                            if(!isset($row[$i])){
                                $row[$i] = " ";
                            }
                            $cellVal = clean_input_data($row[$i]); 

                            if($cellVal == 'workorder#'){
                                $workorderRow = $i;
                                $set++;
                            }

                            if($cellVal == 'client'){
                                $clientRow = $i;
                                $set++;
                            }

                            if($cellVal == 'loan#'){
                                $loanRow = $i;
                            }

                            if($cellVal == 'propertyaddress'){
                                $propertyaddressRow = $i;
                            }

                            if($cellVal == 'city'){
                                $cityRow = $i;                               
                            }

                            if($cellVal == 'state'){
                                $stateRow = $i;                                
                            }

                            if($cellVal == 'zip'){
                                $zipRow = $i;
                            }

                            if($cellVal == 'date'){
                                $paydateRow = $i;
                                $set++;
                            }

                            if($cellVal == 'amount'){
                                $amountRow = $i;
                                $set++;
                            }                            
                            if($set == 4){
                                $setVal = 1;
                            }
                            if($setVal == 1){
                                break;
                            }
                        }
                    }

                    if($setVal != 1){       
                        $mess_arr = array("status"=>"Invalid_Excel_Format");
                        echo json_encode($mess_arr);
                        http_response_code(200);
                        exit;
                    }
                   
                    if(!isset($workorderRow) || !isset($clientRow) || !isset($amountRow) || !isset($paydateRow)){
                        $mess_arr = array("status"=>"Invalid_Excel_Format");
                        echo json_encode($mess_arr);
                        http_response_code(200);
                        exit;
                    }
                    if($count > 0){   
                        $pay_date = isset($row[$paydateRow]) ? clean_cell_input_data($row[$paydateRow]) : "";   
                        
                        if(strlen($pay_date) > 0){
                            if (strpos($pay_date, '/') !== false) {
                                $pay_date = DateTime::createFromFormat('m/d/Y', "$pay_date");
                                $pay_date = $pay_date->format('Y-m-d h:i:s');
                               
                            }elseif (strpos($pay_date, '-') !== false) {
                                $pay_date = DateTime::createFromFormat('m-d-Y', "$pay_date");
                                $pay_date = $pay_date->format('Y-m-d h:i:s');
                               
                            }else{
                                $pay_date = date("Y-m-d h:i:s", ($pay_date - 25569)*86400);
                            }
                        }else{
                            $pay_date = date("Y-m-d h:i:s");
                        } 

                        $work_order = isset($row[$workorderRow]) ? clean_cell_input_data($row[$workorderRow]) : "";

                        $client = isset($row[$clientRow]) ? clean_cell_input_data($row[$clientRow]) : 0;

                        $amount = isset($row[$amountRow]) ? clean_cell_input_data($row[$amountRow]) : "";

                        $loan = '';
                        if(strlen($loanRow) > 0){
                            $loan = isset($row[$loanRow]) ? clean_cell_input_data($row[$loanRow]) : "";
                        }
                        $city = '';
                        if(strlen($cityRow) > 0){
                            $city = isset($row[$cityRow]) ? clean_cell_input_data($row[$cityRow]) : "";
                        }

                        $state = '';
                        $country = '';
                        $county = '';

                        if(strlen($stateRow) > 0){
                            $state = isset($row[$stateRow]) ? clean_cell_input_data($row[$stateRow]) : "";
                        }
                        $zip = '';
                        if(strlen($zipRow) > 0){
                            $zip = isset($row[$zipRow]) ? clean_cell_input_data($row[$zipRow]) : "";
                        }
                        $property_address = '';
                        if(strlen($propertyaddressRow) > 0){
                            $property_address = isset($row[$propertyaddressRow]) ? clean_cell_input_data($row[$propertyaddressRow]) : "";
                        }

                        if(strlen($work_order) > 0 && strlen($client) > 0 && $amount > 0){
                            $query_check_wo = "SELECT * FROM bid WHERE bid_wo_no = '$work_order' AND is_delete IS NULL";        
                            $run_check_wo = mysqli_query($conn, $query_check_wo);        
                            if(mysqli_num_rows($run_check_wo) == 0){ 
                                $query_check_client = "SELECT * FROM client WHERE (client_name = '$client') OR (client_alias = '$client')";
                                $run_check_client = mysqli_query($conn, $query_check_client);
                                if(mysqli_num_rows($run_check_client) == 1){ 
                                    $row_check_client = mysqli_fetch_array($run_check_client);
                                    $fetched_client_id = $row_check_client['client_id'];
                                    if(strlen($property_address) == 0 && strlen($loan) == 0){
                                        $message = $message. "<br>$work_order : has no address data.";
                                    }else{
                                        $prop_id = '';
                                        if(strlen($loan) > 0){
                                            $query_check_prop = "SELECT * FROM property WHERE (prop_case_num = '$loan' AND prop_loan_num = '$loan') OR (prop_case_num = '$loan') OR (prop_loan_num = '$loan')";

                                            $run_check_prop = mysqli_query($conn, $query_check_prop);
                                            if(mysqli_num_rows($run_check_prop) == 1){ 
                                                $row_check_prop = mysqli_fetch_array($run_check_prop);
                                                $prop_id = $row_check_prop['prop_id'];
                                            }
                                        }

                                        if(strlen($prop_id) == 0){
                                            if(strlen($property_address) > 0){
                                                $query_check_prop_add = "SELECT * FROM property WHERE (prop_full_add = '$property_address' AND prop_zip = '$zip') OR (prop_full_add = '$property_address' AND prop_state = '$state' AND prop_city = '$city')";

                                                $run_check_prop_add = mysqli_query($conn, $query_check_prop_add);
                                                if(mysqli_num_rows($run_check_prop_add) == 1){ 
                                                    $row_check_prop_add = mysqli_fetch_array($run_check_prop_add);
                                                    $prop_id = $row_check_prop_add['prop_id'];
                                                }

                                            }                                            
                                        }

                                        if(strlen($prop_id) == 0){
                                            if(strlen($property_address) > 0 && strlen($zip) > 0){
                                                $fetch_details_query = "SELECT * FROM us_zip WHERE zip = $zip";
                                                $fetch_details_run = mysqli_query($conn, $fetch_details_query);
                
                                                if(mysqli_num_rows($fetch_details_run) == 1){
                                                    $fetch_details_row = mysqli_fetch_array($fetch_details_run);$state = $fetch_details_row['state_id'];
                                                    $city = $fetch_details_row['city'];
                                                    $county = $fetch_details_row['county_name'];
                                                    $country = 'US';                                   
                                                }   
                                                if(strlen($loan) == 0){
                                                    $loan = time().mt_rand (10,99);
                                                }
                                                if(strlen($loan)){
                                                    $query_prop_insert = "INSERT INTO property SET prop_case_num = '$loan', prop_loan_num = '$loan', prop_full_add = '$property_address', prop_city = '$city', prop_state = '$state', prop_zip = '$zip', prop_county = '$county', create_time = '$time', is_approve = 1, admin_id = $admin_id, approve_time = '$time'";  
                                        
                                                    $run_prop_insert = mysqli_query($conn, $query_prop_insert);
                                                    if(mysqli_affected_rows($conn) > 0){
                                                        $prop_id = mysqli_insert_id($conn);
                                                        if(strlen($prop_id) > 0){
                                                            $query_insert_prop_assign = "INSERT INTO prop_assign SET client_id = $fetched_client_id, prop_id = $prop_id, admin_id = $admin_id, is_approve = 1, approve_time = '$time', create_time = '$time'";
                                                            $run_insert_prop_assign = mysqli_query($conn,$query_insert_prop_assign);
                                                            if(mysqli_affected_rows($conn) == 1){
                                                                $client_assign_set = 'set';
                                                            }
                                                        }
                                                    }
                                                }

                                            }                                            
                                            
                                        }
                                        if(strlen($prop_id) > 0){
                                            if(strlen($client_assign_set) == 0){
                                                $query_check_prop_assign = "SELECT * FROM prop_assign WHERE client_id = $fetched_client_id AND prop_id = $prop_id AND is_approve = 1";
                                                $run_check_prop_assign = mysqli_query($conn,$query_check_prop_assign);
                                                if(mysqli_num_rows($run_check_prop_assign) == 1){
                                                    $client_assign_set = 'set';
                                                }else{
                                                    $query_insert_prop_assign = "INSERT INTO prop_assign SET client_id = $fetched_client_id, prop_id = $prop_id, admin_id = $admin_id, is_approve = 1, approve_time = '$time', create_time = '$time'";
                                                    $run_insert_prop_assign = mysqli_query($conn,$query_insert_prop_assign);
                                                    if(mysqli_affected_rows($conn) == 1){
                                                        $client_assign_set = 'set';
                                                    }   
                                                }    
                                            }
                                            if($client_assign_set == 'set'){
                                                $query_bid_insert = "INSERT INTO bid SET bid_type = '2', bid_wo_no = '$work_order', client_id = $fetched_client_id, prop_id = $prop_id, create_time = '$pay_date', is_approve = 1, bid_amount = $amount, admin_id = $admin_id, approve_time = '$pay_date'";

                                                $run_bid_insert = mysqli_query($conn, $query_bid_insert);
                                                if(mysqli_affected_rows($conn) > 0){
                                                    $insert_row++;
                                                }
                                            }                                            
                                        }
                                        
                                    }
                                }else{
                                    $message = $message. "<br>$work_order : client does not exist.";
                                }
                            }else{
                                $message = $message. "<br>$work_order : bid already exists in the system.";
                            }
                        } 
                        
                    }
                    $count++;    
                }
                    if($insert_row > 0){
                        $successmessage = "<br> $insert_row bids added.";
                    }else{
                        $message = $message . "<br> $insert_row bids added.";
                    }
                }
                   			
                }else{
                    $message = $message . "The file could not be uploaded due to technical reasons. Try again.";
                }                $mess = '';
                if(strlen($message) > 0 && strlen($successmessage) > 0){
                    $mess = 'both';
                }elseif(strlen($message) > 0){
                    $mess = 'err';
                }elseif(strlen($successmessage) > 0){
                    $mess = 'succ';
                }

                $mess_arr = array("status"=>"yes","mess"=>"$mess","err"=>"$message", "succ"=>"$successmessage");
                
                echo json_encode($mess_arr);
                http_response_code(200);
                exit;

            
        }	
    } 
    
}else{
    http_response_code(400);
    exit;
}
?>