<?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_bid_item'])&& isset($_REQUEST['price_list'])){ 
    if(strlen($_REQUEST['price_list']) == 0){        
        $mess_arr = array("status"=>"price_list_not_set");
                
        echo json_encode($mess_arr);
        http_response_code(200);
        exit;
       
    } 
    $price_list = $_REQUEST['price_list'];    
    $message = '';  
    $uniqueId= time().'-'.mt_rand();	
    $arr = $_FILES["fileToUpload1"]["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["fileToUpload1"]["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["fileToUpload1"]["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;
                        for($i=0; $i<1000; $i++){
                            if(!isset($row[$i])){
                                $row[$i] = " ";
                            }
                            $cellVal = clean_input_data($row[$i]);                            
                            if($cellVal == 'category'){
                                $categoryRow = $i;
                                $set++;
                            }
                            if($cellVal == 'lineitem'){

                                $lineitemRow = $i;
                                $set++;
                            }
                            if($cellVal == 'description'){

                                $descriptionRow = $i;
                                $set++;
                            }                          
                            if($cellVal == 'unitofmeasurement'){

                                $unitofmeasurementRow = $i;
                                $set++;
                            }

                            if($cellVal == 'price'){

                                $priceRow = $i;
                                $set++;
                            }
                            
                            if($set == 5){
                                $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($categoryRow) || !isset($lineitemRow) || !isset($descriptionRow)|| !isset($unitofmeasurementRow) || !isset($priceRow) ){                        
                        $mess_arr = array("status"=>"Invalid_Excel_Format");
                
                        echo json_encode($mess_arr);
                        http_response_code(200);
                        exit;
                    }
                    if($count > 0){                        
                        $category = isset($row[$categoryRow]) ? clean_cell_input_data($row[$categoryRow]) : "";

                        $line_item = isset($row[$lineitemRow]) ? clean_cell_input_data($row[$lineitemRow]) : "";
                        
                        $description = isset($row[$descriptionRow]) ? clean_cell_input_data($row[$descriptionRow]) : "";     

                        $unit_of_measurement = isset($row[$unitofmeasurementRow]) ? clean_cell_input_data($row[$unitofmeasurementRow]) : "";

                        $price = isset($row[$priceRow]) ? clean_cell_input_data($row[$priceRow]) : 0; 
                        $price = str_replace('$', '', $price);
                        if(strlen($category) > 0 && strlen($line_item) > 0){
                         
                            $fetch_cat_details_query = "SELECT * FROM category WHERE cat_name = '$category' AND cat_is_delete IS NULL";
                            $fetch_cat_details_run = mysqli_query($conn, $fetch_cat_details_query);

                            if(mysqli_num_rows($fetch_cat_details_run) == 1){
                                $fetch_cat_details_row = mysqli_fetch_array($fetch_cat_details_run);
                                $cat_id = $fetch_cat_details_row['cat_id'];
                            }else if(mysqli_num_rows($fetch_cat_details_run) == 0){
                                $insert_cat_details_query = "INSERT INTO category SET cat_name = '$category', cat_time = '$time',cat_approve_time ='$time', cat_approved = 1, admin_id = $admin_id";
                                $insert_cat_details_run = mysqli_query($conn, $insert_cat_details_query);
                                $cat_id = mysqli_insert_id($conn);
                            }
                            if(isset($cat_id)){
                                if(strlen($cat_id) > 0){
                                    $fetch_maps_details_query = "SELECT * FROM maps WHERE maps_name= '$line_item' AND cat_id = $cat_id AND client_id = $price_list AND maps_is_delete IS NULL AND maps_desc = '$description' AND (maps_approved = 1 OR maps_approved = 0)";
                                    $fetch_maps_details_run = mysqli_query($conn, $fetch_maps_details_query);

                                    if(mysqli_num_rows($fetch_maps_details_run) == 0){
                                        $uom = 0;
                                        if(strlen($unit_of_measurement) > 0){
                                            $fetch_uom_details_query = "SELECT * FROM uom WHERE uom_name= '$unit_of_measurement' AND uom_is_delete IS NULL";
                                            $fetch_uom_details_run = mysqli_query($conn, $fetch_uom_details_query);
                                            if(mysqli_num_rows($fetch_uom_details_run) == 1){
                                                $fetch_uom_details_row = mysqli_fetch_array($fetch_uom_details_run);
                                                $uom = $fetch_uom_details_row['uom_id'];
                                            }
                                        }
                                        if($uom != 0){
                                            $query_part= ",maps_crew_unit_uom_id = $uom";
                                            $query_partp= "";
                                            }  else{
                                                $query_part= "";
                                                $query_partp= "maps_min_bid_price = $price,";
                                            }
                                        $insert_maps_details_query = "INSERT INTO maps SET client_id = $price_list, admin_id = $admin_id, maps_approved = 1, maps_created_time = '$time',maps_approve_time = '$time', cat_id = $cat_id, maps_name = '$line_item',$query_partp maps_desc = '$description' $query_part"; 
                                        $insert_maps_details_run = mysqli_query($conn, $insert_maps_details_query);
                                        if(mysqli_affected_rows($conn) == 1){
                                            $insert_row++;
                                            if($uom == 1 || $uom == 5|| $uom == 6|| $uom == 7|| $uom == 8){
                                                $inserted_maps_id = mysqli_insert_id($conn);
                                                $query_uom_val1 = "SELECT * FROM uom_field WHERE uom_id = $uom AND uom_field_name = 'maps_uom_val1'";  
                                                $run_uom_val1 = mysqli_query($conn, $query_uom_val1);
                                                $row_uom_value1 = mysqli_fetch_array($run_uom_val1);
                                                $uom_field_id1 = $row_uom_value1['uom_field_id'];
                                                $query_uom_amt1 = "INSERT INTO maps_uom_value SET maps_uom_value_amount = '$price' ,maps_id = $inserted_maps_id, uom_field_id = $uom_field_id1";
                                                $run_uom_amt1 = mysqli_query($conn, $query_uom_amt1);
                                            }                                            
                                        }
                                    }
                                }
                            }
                            
                        } 
                        
                    }
                    $count++;    
                }
                    if($insert_row > 0){
                        $successmessage = "$insert_row Items added.";
                    }else{
                        $message = $message . "$insert_row Items 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;
}
?>