<?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_vendor'])){  
    $message = '';  
    $uniqueId= time().'-'.mt_rand();	
    $arr = $_FILES["fileToUpload_vendor"]["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_vendor"]["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_vendor"]["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;
                        $paydateRow = '';
                        $vendorpayRow = '';
                        $zipcodeRow = '';
                        $stateRow = '';
                        $phoneRow = '';
                        $cityRow = '';
                        $countyRow = '';
                        $streetaddressRow = '';
                        $emailRow = '';
                        for($i=0; $i<20; $i++){
                            if(!isset($row[$i])){
                                $row[$i] = " ";
                            }
                            $cellVal = clean_input_data($row[$i]);

                            if($cellVal == 'paydate'){
                                $paydateRow = $i;
                            }

                            if($cellVal == 'vendorpay'){
                                $vendorpayRow = $i;
                            }

                            if($cellVal == 'zipcode'){
                                $zipcodeRow = $i;
                            }

                            if($cellVal == 'county'){
                                $countyRow = $i;
                            }

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

                            if($cellVal == 'streetaddress'){
                                $streetaddressRow = $i;
                            }

                            if($cellVal == 'phone'){
                                $phoneRow = $i;
                            }

                            if($cellVal == 'email'){
                                $emailRow = $i;                               
                            }

                            if($cellVal == 'company'){
                                $companyRow = $i;
                                $set++;
                            }

                            if($cellVal == 'vendorname'){
                                $vendornameRow = $i;
                                $set++;
                            }
                            
                            if($cellVal == 'vendorid'){
                                $vendoridRow = $i;
                            }

                            if($set == 2){
                                $setVal = 1;
                            }                            
                        }
                    }

                    if($setVal != 1){
                        $mess_arr = array("status"=>"Invalid_Excel_Format");                
                        echo json_encode($mess_arr);
                        http_response_code(200);
                        exit;
                    }
                   
                    if(!isset($vendornameRow) || !isset($companyRow)){                        
                        $mess_arr = array("status"=>"Invalid_Excel_Format");                
                        echo json_encode($mess_arr);
                        http_response_code(200);
                        exit;
                    }
                    if($count > 0){   
                        if(isset($vendoridRow)){
                            if(strlen($vendoridRow) > 0){
                                $vendor_id = isset($row[$vendoridRow]) ? clean_cell_input_data($row[$vendoridRow]) : "";  
                            }else{
                                $vendor_id = "";  
                            }                            
                        }else{
                            $vendor_id = "";  
                        }                                         
                        $vendor_name = isset($row[$vendornameRow]) ? clean_cell_input_data($row[$vendornameRow]) : "";

                        $company = isset($row[$companyRow]) ? clean_cell_input_data($row[$companyRow]) : "";

                        $paydate = '';

                        if(strlen($paydateRow) > 0){
                            $paydate = isset($row[$paydateRow]) ? clean_cell_input_data($row[$paydateRow]) : "";
                        } 
                        if(strlen($paydate) > 0){
                            if (strpos($paydate, '/') !== false) {
                                $paydate = DateTime::createFromFormat('m/d/Y', "$paydate");
                                $paydate = $paydate->format('Y-m-d h:i:s');
                               
                            }elseif (strpos($paydate, '-') !== false) {
                                $paydate = DateTime::createFromFormat('m-d-Y', "$paydate");
                                $paydate = $paydate->format('Y-m-d h:i:s');
                               
                            }else{
                                $paydate = date("Y-m-d h:i:s", ($paydate - 25569)*86400);
                            }
                        }else{
                            $paydate = date("Y-m-d h:i:s");
                        }  
                        
                        $vendor_pay = 0;

                        if(strlen($vendorpayRow) > 0){
                            $vendor_pay = isset($row[$vendorpayRow]) ? clean_cell_input_data($row[$vendorpayRow]) : "";
                        } 
                        $vendor_pay = str_replace('$', '', $vendor_pay);

                        $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]) : "";
                        }
                        
                        $phone = '';

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

                        $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;
                                    }
                                }                                 
                            }
                        }

                        $street_address = '';

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

                        $email = '';

                        if(strlen($emailRow) > 0){
                            $email = isset($row[$emailRow]) ? clean_cell_input_data($row[$emailRow]) : "";
                        }
                        
                        $add = 0;
                        if(strlen($vendor_id) > 0){                           
                            $query_check_vendor = "SELECT * FROM vendor WHERE vendor_id = $vendor_id";
                            $run_check_vendor = mysqli_query($conn, $query_check_vendor);
                            if(mysqli_num_rows($run_check_vendor) == 1){
                                $is_set = 0;
                                $vendor_name = "vendor_name = '$vendor_name'";
                                if(strlen($vendor_name) > 0) { 
                                    $is_set++;
                                }                                
                                if(strlen($company) > 0) {
                                    $company = ", vendor_company = '$company'";
                                    $is_set++;
                                }                                
                                if(strlen($street_address) > 0) {
                                    $street_address = ", vendor_address = '$street_address'";
                                    $is_set++;
                                }                                
                                if(strlen($zip_code) > 0) {
                                    $zip_code = ", vendor_zip = '$zip_code'";
                                    $is_set++;
                                }
                                if(strlen($state) > 0) {
                                    $state = ", vendor_state = '$state'";
                                    $is_set++;
                                }
                                if(strlen($city) > 0) {
                                    $city = ", vendor_city = '$city'";
                                    $is_set++;
                                }
                                if(strlen($county) > 0) {
                                    $county = ", vendor_county = '$county'";
                                    $is_set++;
                                }

                                if(strlen($email) > 0) {
                                    $email = ", vendor_email = '$email'";
                                    $is_set++;
                                }

                                if(strlen($phone) > 0) {
                                    $phone = ", vendor_phone = '$phone'";
                                    $is_set++;
                                }

                                if($is_set > 0){
                                    $query_vendor_insert = "UPDATE vendor SET $vendor_name $email $phone $street_address $state $city $county $zip_code WHERE vendor_id = $vendor_id";
                                    $run_vendor_insert = mysqli_query($conn, $query_vendor_insert);
                                    if(mysqli_affected_rows($conn) > 0){
                                        $add++;
                                    }
                                }
                            }elseif(mysqli_num_rows($run_check_vendor) == 0){
                                if(strlen($vendor_name) > 0 && strlen($company) > 0){
                                    $query_vendor_insert = "INSERT INTO vendor SET vendor_id = $vendor_id, vendor_name = '$vendor_name', vendor_company = '$company', vendor_email = '$email', vendor_phone = '$phone', vendor_address = '$street_address', vendor_zip = '$zip_code', vendor_city = '$city', vendor_state = '$state', vendor_county = '$county', vendor_time = '$time', is_approve = 1, admin_id = $admin_id, approve_time = '$time'";              
                                    $run_vendor_insert = mysqli_query($conn, $query_vendor_insert);
                                    if(mysqli_affected_rows($conn) > 0){
                                        $add++;
                                    }                                        
                                                               
                                } 
                            }
                        }else{
                            if(strlen($vendor_name) > 0 && strlen($company) > 0){
                                $query_vendor_insert = "INSERT INTO vendor SET vendor_name = '$vendor_name', vendor_company = '$company', vendor_email = '$email', vendor_phone = '$phone', vendor_address = '$street_address', vendor_zip = '$zip_code', vendor_city = '$city', vendor_state = '$state', vendor_county = '$county', vendor_time = '$time', is_approve = 1, admin_id = $admin_id, approve_time = '$time'";              
                                $run_vendor_insert = mysqli_query($conn, $query_vendor_insert);
                                if(mysqli_affected_rows($conn) > 0){
                                    $vendor_id = mysqli_insert_id($conn);
                                    $add++;
                                }                          
                            }
                        }
                        if(strlen($vendor_id) > 0 && $vendor_pay > 0){
                            $insert_vendor_pay = "INSERT INTO vendor_pay SET vendor_id = $vendor_id , pay_date = '$paydate', pay_amount = $vendor_pay,admin_id = $admin_id, time = '$time'";
                            $insert_vendor_pay_run = mysqli_query($conn, $insert_vendor_pay);
                            if(mysqli_affected_rows($conn) > 0){
                                $add++;
                            }
                        }
                        if($add > 0){
                            $insert_row++;
                        } 
                    }
                    $count++;    
                }
                    if($insert_row > 0){
                        $successmessage = "$insert_row Vendor details added.";
                    }else{
                        $message = $message . "$insert_row Vendor details 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;
}
?>