<?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_cell_input_data($cellVal1){	
    include "include/db.php";
	$cellVal1 = trim($cellVal1);
	$cellVal1 = mysqli_real_escape_string($conn, $cellVal1);
	return $cellVal1;
}

if(isset($_REQUEST['upload_xl_prop'])){  
    $uniqueId= time().'-'.mt_rand();	
    $temp = explode(".", $_FILES["fileToUpload1"]["name"]);
    $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");
    
    if($_FILES["fileToUpload1"]["size"] > 2000000){
        $message = $message . 'The file you uploaded is too large. File size should be less than 2 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{
            if(move_uploaded_file($_FILES["fileToUpload1"]["tmp_name"], $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;
                        for($i=0; $i<1000; $i++){
                            if(!isset($row[$i])){
                                $row[$i] = " ";
                            }
                            $cellVal = clean_input_data($row[$i]);                            
                            if($cellVal == 'casenumber'){
                                $casenumberRow = $i;
                                $set++;
                            }
                            if($cellVal == 'client'){

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

                                $streetRow = $i;
                                $set++;
                            }                          
                            if($cellVal == 'zip'){

                                $zipRow = $i;
                                $set++;
                            }
                            if($cellVal == 'loannumber'){

                                $loannumberRow = $i;
                                $set++;
                            }
                           
                            if($set == 5){
                                $setVal = 1;
                            }

                            if($setVal == 1){
                                break;
                            }
                        }
                    }

                    if($setVal != 1){                        
                        header("Location:property.php?mess=Invalid Excel Format");
                        exit;
                    }
                   
                    if(!(isset($casenumberRow) || isset($loannumberRow)) || !isset($streetRow)|| !isset($clientRow) || !isset($zipRow) ){                        
                        header("Location:property.php?mess=Invalid excel Format");
                        exit;
                    }
                    if($count > 0){                        
                        $case_no = isset($row[$casenumberRow]) ? clean_cell_input_data($row[$casenumberRow]) : "";

                        $loan_no = isset($row[$loannumberRow]) ? clean_cell_input_data($row[$loannumberRow]) : "";
                        
                        $street = isset($row[$streetRow]) ? clean_cell_input_data($row[$streetRow]) : "";     

                        $zip = isset($row[$zipRow]) ? clean_cell_input_data($row[$zipRow]) : "";

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

                        if(strlen($loan_no) > 0 || strlen($case_no) > 0){
                            $prop_no = "";
                            if(strlen($loan_no) > 0){
                                $prop_no = $loan_no;
                            }elseif(strlen($case_no) > 0){
                                $prop_no = $case_no;
                            }
                            if(strlen($zip) > 0){
                                $county = '';
                                $city = '';
                                $state = ''; 
                                $country = '';
                                $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';                                   
                                }else{
                                    $message = $message . "$prop_no: Invalid zip filled.<br>";
                                }
                            }else{
                                $message = $message . "$prop_no: Zip not filled.<br>";
                            }                             

                            $time = date("Y-m-d H:i:s");

                            if(strlen($prop_no) > 0 && strlen($street) > 0 && strlen($county) > 0 && strlen($city) > 0 && strlen($state) > 0){

                                $query_check_prop = "SELECT * FROM property WHERE (prop_case_num = '$prop_no' AND prop_loan_num = '$prop_no') OR (prop_case_num = '$prop_no') OR (prop_loan_num = '$prop_no')";

                                $run_check_prop = mysqli_query($conn, $query_check_prop);

                                if(mysqli_num_rows($run_check_prop) == 0){ 
                                    $query_check_propnx = "SELECT * FROM property WHERE prop_full_add = '$street' AND prop_zip = $zip";
                                    $run_check_propnx = mysqli_query($conn, $query_check_propnx);
                                    if(mysqli_num_rows($run_check_propnx) == 0){
                                        $query_prop_insert = "INSERT INTO property SET prop_case_num = '$prop_no', prop_loan_num = '$prop_no', prop_full_add = '$street', 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){
                                            $insert_row++;                                        
                                            if(strlen($client) > 0){
                                                $new_prop_id = mysqli_insert_id($conn);
                                                $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'];

                                                    $query_prop_assign_insertx = "SELECT * FROM prop_assign WHERE prop_id = $new_prop_id AND client_id = $fetched_client_id";
        
                                                    $run_prop_assign_insertx = mysqli_query($conn, $query_prop_assign_insertx);
                                                    if(mysqli_affected_rows($conn) == 0){
                                                        $query_prop_assign_insert = "INSERT INTO prop_assign SET prop_id = $new_prop_id, client_id = $fetched_client_id,is_approve=1, create_time = '$time'";
        
                                                        $run_prop_assign_insert = mysqli_query($conn, $query_prop_assign_insert);
                                                        if(mysqli_affected_rows($conn) > 0){
                                                            $assign_row++;
                                                        }
                                                    }
                                                }
                                            }
                                        }else{                                        
                                            $message = $message . "$prop_no: Incorrect details filled.<br>";
                                        }
                                    }elseif(mysqli_num_rows($run_check_propnx) > 0){
                                        $row_check_propnx = mysqli_fetch_array($run_check_propnx); 
                                        $exist_loan_no = $row_check_propnx['prop_case_num'];
                                        $exist_prop_id = $row_check_propnx['prop_id'];
                                        if(strlen($client) > 0 && strlen($exist_prop_id) > 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'];
                                                $query_prop_assign_insertx = "SELECT * FROM prop_assign WHERE prop_id = $exist_prop_id AND client_id = $fetched_client_id";
    
                                                $run_prop_assign_insertx = mysqli_query($conn, $query_prop_assign_insertx);
                                                if(mysqli_affected_rows($conn) == 0){
                                                    $query_prop_insert = "INSERT INTO property SET prop_case_num = '$prop_no', prop_loan_num = '$prop_no', prop_full_add = '$street', 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){
                                                        $insert_row++;                   
                                                        $new_prop_id = mysqli_insert_id($conn);
                                                        $query_prop_assign_insert = "INSERT INTO prop_assign SET prop_id = $new_prop_id, client_id = $fetched_client_id,is_approve=1, create_time = '$time'";
            
                                                        $run_prop_assign_insert = mysqli_query($conn, $query_prop_assign_insert);
                                                        if(mysqli_affected_rows($conn) > 0){
                                                            $assign_row++;
                                                        }                  
                                                    }
                                                }else{
                                                    $message = $message . "$prop_no: $client already exists at $street with $exist_loan_no.<br>";
                                                }
                                            }
                                        }
                                        if(strlen($client) == 0){
                                            $message = $message . "$prop_no: this address already at Loan number: $exist_loan_no.<br>";
                                        }
                                    }
                                }elseif(mysqli_num_rows($run_check_prop) > 0){
                                    $row_check_prop = mysqli_fetch_array($run_check_prop);
                                    $exist_prop_id = $row_check_prop['prop_id'];
                                    if(isset($exist_prop_id)){
                                        if(strlen($client) > 0 && strlen($exist_prop_id) > 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'];

                                                $query_prop_assign_insertx = "SELECT * FROM prop_assign WHERE prop_id = $exist_prop_id AND client_id = $fetched_client_id";
    
                                                $run_prop_assign_insertx = mysqli_query($conn, $query_prop_assign_insertx);
                                                if(mysqli_affected_rows($conn) == 0){
                                                    $query_prop_assign_insert = "INSERT INTO prop_assign SET prop_id = $exist_prop_id, client_id = $fetched_client_id, is_approve=1, create_time = '$time'";
    
                                                    $run_prop_assign_insert = mysqli_query($conn, $query_prop_assign_insert);
                                                    if(mysqli_affected_rows($conn) > 0){
                                                        $assign_row++;
                                                    }
                                                }else{
                                                    $message = $message . "$prop_no: Property aleardy assigned to that client.<br>";
                                                }
                                            }
                                        }
                                    }                                    
                                }
                            }

                            
                        }   
                        
                    }
                    $count++;    
                }
                    if($insert_row > 0){
                        $successmessage = "$insert_row Property Created.";
                    }else{
                        $message = $message . "$insert_row Property Created.";
                    }

                    if($assign_row > 0){
                        $successmessage = $successmessage. " $assign_row Clients Assigned.";
                    }else{
                        $message = $message . " $assign_row Clients Assigned.";
                    }
                    
                }
                   			
                }else{
                    $message = $message . "The file could not be uploaded due to technical reasons. Try again.";
                }			
            
        }	
    } 
    
}
header("Location:property.php?smess=$successmessage&mess=$message");
?>