<?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['mess'])){
    $message = $_REQUEST['mess'];
}

if(isset($_REQUEST['upload_xl_client'])){  
    $message = '';  
    $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;              
                foreach($sheetData as $row) {
                    if($count == 0){	
                        $setVal = 0;
                        $set = 0;
                        $zipcodeRow = '';
                        $stateRow = '';
                        $cityRow = '';
                        $countyRow = '';
                        for($i=0; $i<1000; $i++){
                            if(!isset($row[$i])){
                                $row[$i] = " ";
                            }
                            $cellVal = clean_input_data($row[$i]);                            
                            if($cellVal == 'clientname'){
                                $clientnameRow = $i;
                                $set++;
                            }
                            if($cellVal == 'clientalias'){

                                $clientaliasRow = $i;
                                $set++;
                            }
                            if($cellVal == 'address'){
                                $addressRow = $i;
                                $set++;
                            } 
                            if($cellVal == 'zipcode'){
                                $zipcodeRow = $i;
                            }                          
                            if($cellVal == 'state'){
                                $stateRow = $i;
                            } 
                            if($cellVal == 'city'){
                                $cityRow = $i;
                            } 
                            if($cellVal == 'county'){
                                $countyRow = $i;
                            } 
                            if($cellVal == 'phonehome'){

                                $phonehomeRow = $i;
                                $set++;
                            }

                            if($cellVal == 'primaryemail'){

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

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

                    if($setVal != 1){                        
                        header("Location:clients.php?mess=Invalid Excel Format");
                        exit;
                    }
                   
                    if(!isset($clientnameRow) || !isset($clientaliasRow) || !isset($addressRow)|| !isset($phonehomeRow) || !isset($primaryemailRow) ){                        
                        header("Location:clients.php?mess=Invalid excel Format");
                        exit;
                    }
                    if($count > 0){                        
                        $client_name = isset($row[$clientnameRow]) ? clean_cell_input_data($row[$clientnameRow]) : "";

                        $client_alias = isset($row[$clientaliasRow]) ? clean_cell_input_data($row[$clientaliasRow]) : "";
                        
                        $address = isset($row[$addressRow]) ? clean_cell_input_data($row[$addressRow]) : "";     

                        $phone_home = isset($row[$phonehomeRow]) ? clean_cell_input_data($row[$phonehomeRow]) : "";

                        $primary_email = isset($row[$primaryemailRow]) ? clean_cell_input_data($row[$primaryemailRow]) : "";

                        $zip_code = '';

                        if(strlen($zipcodeRow) > 0){
                            $zip_code = isset($row[$zipcodeRow]) ? clean_cell_input_data($row[$zipcodeRow]) : "";
                        }                        

                        $county = '';

                        if(strlen($countyRow) > 0){
                            $county = isset($row[$countyRow]) ? clean_cell_input_data($row[$countyRow]) : "";
                        }

                        $state = '';

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

                        $city = '';

                        if(strlen($cityRow) > 0){
                            $city = isset($row[$cityRow]) ? clean_cell_input_data($row[$cityRow]) : "";
                        } 
                        if(strlen($zip_code) > 0){
                            $fetch_details_query = "SELECT * FROM us_zip WHERE zip = $zip_code";
                            $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_temp = $fetch_details_row['state_id'];
                                if(isset($state_temp)){
                                    if(strlen($state_temp) > 0){
                                        $state = $state_temp;
                                    }
                                }
                                $city_temp = $fetch_details_row['city'];
                                if(isset($city_temp)){
                                    if(strlen($city_temp) > 0){
                                        $city = $city_temp;
                                    }
                                }
                                $county_temp = $fetch_details_row['county_name'];  
                                if(isset($county_temp)){
                                    if(strlen($county_temp) > 0){
                                        $county = $county_temp;
                                    }
                                }                                 
                            }
                        }                   
                       

                        if(strlen($client_name) > 0 && strlen($client_alias) > 0){
                            $query_check_client = "SELECT * FROM client WHERE (client_name = '$client_name') OR (client_alias = '$client_alias')";        
                            $run_check_client = mysqli_query($conn, $query_check_client);        
                            if(mysqli_num_rows($run_check_client) == 0){ 

                                $query_prop_assign_insertx = "INSERT INTO client SET client_name = '$client_name', client_alias = '$client_alias',client_address = '$address',client_county = '$county',client_city = '$city', client_state = '$state', client_zip = '$zip_code', client_phone = '$phone_home', client_email = '$primary_email', admin_id = $admin_id,is_approve = 1, approve_time = '$time', client_time = '$time'";

                                $run_prop_assign_insertx = mysqli_query($conn, $query_prop_assign_insertx);
                                if(mysqli_affected_rows($conn) == 1){
                                    $insert_row++; 
                                }
                            }else{
                                $message = $message . "$client_name or $client_alias already exists in system and cannot be created.<br>";
                            }                            
                        }
                    }
                    $count++;    
                }
                    if($insert_row > 0){
                        $successmessage = "$insert_row Clients Created.";
                    }else{
                        $message = $message . "$insert_row Clients Created.";
                    }                                       
                }
                   			
                }else{
                    $message = $message . "The file could not be uploaded due to technical reasons. Try again.";
                }
        }	
    } 
}
header("Location:clients.php?smess=$successmessage&mess=$message");
?>