<?php
global $db,$conn;
set_time_limit(0);
ini_set('memory_limit','1024M');
ini_set('post_max_size','32M');
ini_set('upload_max_filesize ','32M');
//echo phpinfo();
//if($_POST['importfile']) {
if(isset($_REQUEST['action1']) && $_REQUEST['action1'] == 'enquireupload'){
	$path = $_FILES['import_gsi']['name'];
	$ext = pathinfo($path, PATHINFO_EXTENSION);
	if($ext !='csv'){
		echo $msg = "<br><br><div style='border: 2px solid red;
		color: black;
		font-size: 14px;
		width: 250px;
		height: 50px;
		margin: 0 auto;
		text-align:center;
		padding: 10px;'>Please upload csv file only </div>";
		exit;
	}
/******************** Creating copy of product table ***********************/
$dateString = str_replace("-","_",date($config->dateFormat));
$timeString = str_replace(":","_",date('H:i:s'));
$backup_table_name = $dateString."_".$timeString;

mysqli_query($conn,"CREATE TABLE job_$backup_table_name LIKE gsi.job");
mysqli_query($conn,"INSERT INTO job_$backup_table_name SELECT * FROM job");
/********************** End Creating copy of product table *****************/
$destdir = getcwd()."/bulk_import_csv/";
$filename = explode('.', $_FILES['import_gsi']['name']);
$namep1   = $filename[0];
//$name     = $namep1.'.'.$filename[1];
$dateString = str_replace("-","_",date($config->dateFormat));
$timeString = str_replace(":","_",date('H:i:s'));
$name     = $namep1.'_'.$dateString.'_'.$timeString.'.'.$filename[1];
$destfile = $destdir.$name;  

$batch_size = 3000;
$user_inserted = 0;
$user_updated = 0;   
$user_skiped = 0;   
move_uploaded_file($_FILES['import_gsi']['tmp_name'], $destfile);
                                   
$flag = true;
$fp = fopen($destfile, 'r');
while (!feof ($fp)){
  $buffer = fgetcsv($fp, 1000000, ',');  
  if($flag) {
	$fixed_sample_header = "company_name,sector,trading_name_at_location,first_name,last_name,job_function,job_title,phone_no,industry_sector_directory_listing_description,notes,renewal_dates,turnover";
	$uploaded_sample_header = implode(",",$buffer);
	//echo $uploaded_sample_header."<br>";
	//echo $fixed_sample_header;
	$fixed_sample_header_array = explode(",",$fixed_sample_header);
	if($uploaded_sample_header != $fixed_sample_header){
		echo $msg = "<br><br><div style='border: 2px solid red;
		color: black;
		font-size: 14px;
		width: 450px;
		height: auto;
		margin: 0 auto;
		text-align:center;
		padding: 10px;'>CSV header is not correct, please follow the format given in nnconnect_import_sample.csv </div>";
		exit;
	}
	$flag = false;
	 continue; 
  }
  
	if($buffer){
		if(count($buffer) != count($fixed_sample_header_array)){
			$user_skiped++;
			continue;
		}
		$buffer=str_replace('"','',$buffer);//take care of double quotes in CSV
		
		$variable_array = array('company_name','sector','trading_name_at_location','first_name','last_name','job_function','job_title','phone_no','industry_sector_directory_listing_description','notes','renewal_dates','turnover');
		
		foreach($variable_array as $key=>$val){		
			${$val} = addslashes($buffer[$key]);
		}
		
		if(isset($renewal_dates) && $renewal_dates !=""){
			$renewal_date_array = explode("/",$renewal_dates);
			$renewal_dates = $renewal_date_array[2]."-".$renewal_date_array[1]."-".$renewal_date_array[0];
		}else{
			$renewal_dates = "0000-00-00 00:00:00";
		}
		$turnover = str_replace(',','',$turnover);
		$turnover = str_replace('£','',$turnover);
		/************** Data Upload ***********************************/
			$sql_to_job = "SELECT * FROM job WHERE phone_no = '$phone_no' AND is_enquiry = 1 AND is_celebrant = 0 AND is_funeral = 0 ";
			$rs_to_job = mysqli_query($conn,$sql_to_job);
			$num_rows = mysqli_num_rows($rs_to_job);
			
			
			$sector_ids = "";
			$sector_id1 = utility::getTableFieldByTwoCondition("keycode","id","name","sector","code",$sector);
			if(!(isset($sector_id1) && $sector_id1 !="")){
			mysqli_query($conn,"INSERT INTO keycode(name,code) VALUE('sector','$sector')");
			$sector_ids = mysqli_insert_id($conn);
			}else{
			$sector_ids = $sector_id1;
			}
			
			
			if($num_rows == 0){
				$user_inserted++;
				$enquiry_date = date("Y-m-d");
				$sql= "INSERT INTO job (`company_name`,`sector`,`trading_name_at_location`,`first_name`,`last_name`,`job_function`,`job_title`,`phone_no`,`industry_sector_directory_listing_description`,`notes`,`renewal_dates`,`turnover`,`is_enquiry`,`enquiry_date`) VALUES('$company_name','$sector_ids','$trading_name_at_location','$first_name','$last_name','$job_function','$job_title','$phone_no','$industry_sector_directory_listing_description','$notes','$renewal_dates','$turnover','1','$enquiry_date')";
				mysqli_query($conn,$sql);
			}else{
				$user_updated++;
				$sql1 = "SELECT * FROM job WHERE phone_no = '$phone_no' AND is_enquiry = 1 AND is_celebrant = 0 AND is_funeral = 0 ";
				$rs1 = mysqli_query($conn,$sql1);
				if(mysqli_num_rows($rs1) > 0){
					$data = mysqli_fetch_assoc($rs1);
					$id = $data["id"];
				}
				mysqli_query($conn,"UPDATE job SET company_name = '$company_name', sector = '$sector_ids', trading_name_at_location = '$trading_name_at_location', first_name = '$first_name', last_name = '$last_name', job_function = '$job_function', job_title = '$job_title', phone_no = '$phone_no', industry_sector_directory_listing_description = '$industry_sector_directory_listing_description', notes = '$notes', renewal_dates = '$renewal_dates', turnover = '$turnover' WHERE id = '$id' ");
			}
		/************** End Data Upload ***********************************/
	}
  }
}

if(isset($_REQUEST['action1']) && $_REQUEST['action1'] == 'quotationupload'){	
	$path = $_FILES['import_gsi_1']['name'];
	$ext = pathinfo($path, PATHINFO_EXTENSION);
	if($ext !='csv'){
		echo $msg = "<br><br><div style='border: 2px solid red;
		color: black;
		font-size: 14px;
		width: 250px;
		height: 50px;
		margin: 0 auto;
		text-align:center;
		padding: 10px;'>Please upload csv file only </div>";
		exit;
	}
/******************** Creating copy of product table ***********************/
$dateString = str_replace("-","_",date($config->dateFormat));
$timeString = str_replace(":","_",date('H:i:s'));
$backup_table_name = $dateString."_".$timeString;

mysqli_query($conn,"CREATE TABLE job_$backup_table_name LIKE gsi.job");
mysqli_query($conn,"INSERT INTO job_$backup_table_name SELECT * FROM job");
/********************** End Creating copy of product table *****************/
$destdir = getcwd()."/bulk_import_csv/";
$filename = explode('.', $_FILES['import_gsi_1']['name']);
$namep1   = $filename[0];
//$name     = $namep1.'.'.$filename[1];
$dateString = str_replace("-","_",date($config->dateFormat));
$timeString = str_replace(":","_",date('H:i:s'));
$name     = $namep1.'_'.$dateString.'_'.$timeString.'.'.$filename[1];
$destfile = $destdir.$name;  

$batch_size = 3000;
$user_inserted = 0;
$user_updated = 0;   
$user_skiped = 0;   
move_uploaded_file($_FILES['import_gsi_1']['tmp_name'], $destfile);
                                   
$flag = true;
$fp = fopen($destfile, 'r');
while (!feof ($fp)){
  $buffer = fgetcsv($fp, 1000000, ',');  
  if($flag) {
	$fixed_sample_header = "company_name,sector,trading_name_at_location,first_name,last_name,job_function,job_title,phone_no,industry_sector_directory_listing_description,notes,renewal_dates,turnover";
	$uploaded_sample_header = implode(",",$buffer);
	//echo $uploaded_sample_header."<br>";
	//echo $fixed_sample_header;
	$fixed_sample_header_array = explode(",",$fixed_sample_header);
	if($uploaded_sample_header != $fixed_sample_header){
		echo $msg = "<br><br><div style='border: 2px solid red;
		color: black;
		font-size: 14px;
		width: 450px;
		height: auto;
		margin: 0 auto;
		text-align:center;
		padding: 10px;'>CSV header is not correct, please follow the format given in nnconnect_import_sample.csv </div>";
		exit;
	}
	$flag = false;
	 continue; 
  }
  
	if($buffer){
		if(count($buffer) != count($fixed_sample_header_array)){
			$user_skiped++;
			continue;
		}
		$buffer=str_replace('"','',$buffer);//take care of double quotes in CSV
		
		$variable_array = array('company_name','sector','trading_name_at_location','first_name','last_name','job_function','job_title','phone_no','industry_sector_directory_listing_description','notes','renewal_dates','turnover');
		
		foreach($variable_array as $key=>$val){		
			${$val} = addslashes($buffer[$key]);
		}
		
		if(isset($renewal_dates) && $renewal_dates !=""){
			$renewal_date_array = explode("/",$renewal_dates);
			$renewal_dates = $renewal_date_array[2]."-".$renewal_date_array[1]."-".$renewal_date_array[0];
		}else{
			$renewal_dates = "0000-00-00 00:00:00";
		}
		$turnover = str_replace(',','',$turnover);
		$turnover = str_replace('£','',$turnover);
		/************** Data Upload ***********************************/
			$sql_to_job = "SELECT * FROM job WHERE phone_no = '$phone_no' AND is_celebrant = 1 AND is_funeral = 0 ";
			$rs_to_job = mysqli_query($conn,$sql_to_job);
			$num_rows = mysqli_num_rows($rs_to_job);
			
			$sector_ids = "";
			$sector_id1 = utility::getTableFieldByTwoCondition("keycode","id","name","sector","code",$sector);
			if(!(isset($sector_id1) && $sector_id1 !="")){
			mysqli_query($conn,"INSERT INTO keycode(name,code) VALUE('sector','$sector')");
			$sector_ids = mysqli_insert_id($conn);
			}else{
			$sector_ids = $sector_id1;
			}
			
			if($num_rows == 0){
				$user_inserted++;
				$celebrant_date = date("Y-m-d");
				$sql= "INSERT INTO job (`company_name`,`sector`,`trading_name_at_location`,`first_name`,`last_name`,`job_function`,`job_title`,`phone_no`,`industry_sector_directory_listing_description`,`notes`,`renewal_dates`,`turnover`,`is_celebrant`,`celebrant_date`) VALUES('$company_name','$sector_ids','$trading_name_at_location','$first_name','$last_name','$job_function','$job_title','$phone_no','$industry_sector_directory_listing_description','$notes','$renewal_dates','$turnover','1','$celebrant_date')";
				mysqli_query($conn,$sql);
			}else{
				$user_updated++;
				$sql1 = "SELECT * FROM job WHERE phone_no = '$phone_no' AND is_celebrant = 1 AND is_funeral = 0 ";
				$rs1 = mysqli_query($conn,$sql1);
				if(mysqli_num_rows($rs1) > 0){
					$data = mysqli_fetch_assoc($rs1);
					$id = $data["id"];
				}
				mysqli_query($conn,"UPDATE job SET company_name = '$company_name', sector = '$sector_ids', trading_name_at_location = '$trading_name_at_location', first_name = '$first_name', last_name = '$last_name', job_function = '$job_function', job_title = '$job_title', phone_no = '$phone_no', industry_sector_directory_listing_description = '$industry_sector_directory_listing_description', notes = '$notes', renewal_dates = '$renewal_dates', turnover = '$turnover' WHERE id = '$id' ");
			}
		/************** End Data Upload ***********************************/
	}
  }
}

if(isset($_REQUEST['action1']) && $_REQUEST['action1'] == 'clientsupload'){	
	$path = $_FILES['import_gsi_2']['name'];
	$ext = pathinfo($path, PATHINFO_EXTENSION);
	if($ext !='csv'){
		echo $msg = "<br><br><div style='border: 2px solid red;
		color: black;
		font-size: 14px;
		width: 250px;
		height: 50px;
		margin: 0 auto;
		text-align:center;
		padding: 10px;'>Please upload csv file only </div>";
		exit;
	}
/******************** Creating copy of product table ***********************/
$dateString = str_replace("-","_",date($config->dateFormat));
$timeString = str_replace(":","_",date('H:i:s'));
$backup_table_name = $dateString."_".$timeString;

mysqli_query($conn,"CREATE TABLE job_$backup_table_name LIKE gsi.job");
mysqli_query($conn,"INSERT INTO job_$backup_table_name SELECT * FROM job");
/********************** End Creating copy of product table *****************/
$destdir = getcwd()."/bulk_import_csv/";
$filename = explode('.', $_FILES['import_gsi_2']['name']);
$namep1   = $filename[0];
//$name     = $namep1.'.'.$filename[1];
$dateString = str_replace("-","_",date($config->dateFormat));
$timeString = str_replace(":","_",date('H:i:s'));
$name     = $namep1.'_'.$dateString.'_'.$timeString.'.'.$filename[1];
$destfile = $destdir.$name;  

$batch_size = 3000;
$user_inserted = 0;
$user_updated = 0;   
$user_skiped = 0;   
move_uploaded_file($_FILES['import_gsi_2']['tmp_name'], $destfile);
                                   
$flag = true;
$fp = fopen($destfile, 'r');
while (!feof ($fp)){
  $buffer = fgetcsv($fp, 1000000, ',');  
  if($flag) {
	$fixed_sample_header = "company_name,sector,trading_name_at_location,first_name,last_name,job_function,job_title,phone_no,industry_sector_directory_listing_description,notes,renewal_dates,turnover";
	$uploaded_sample_header = implode(",",$buffer);
	//echo $uploaded_sample_header."<br>";
	//echo $fixed_sample_header;
	$fixed_sample_header_array = explode(",",$fixed_sample_header);
	if($uploaded_sample_header != $fixed_sample_header){
		echo $msg = "<br><br><div style='border: 2px solid red;
		color: black;
		font-size: 14px;
		width: 450px;
		height: auto;
		margin: 0 auto;
		text-align:center;
		padding: 10px;'>CSV header is not correct, please follow the format given in nnconnect_import_sample.csv </div>";
		exit;
	}
	$flag = false;
	 continue; 
  }
  
	if($buffer){
		if(count($buffer) != count($fixed_sample_header_array)){
			$user_skiped++;
			continue;
		}
		$buffer=str_replace('"','',$buffer);//take care of double quotes in CSV
		
		$variable_array = array('company_name','sector','trading_name_at_location','first_name','last_name','job_function','job_title','phone_no','industry_sector_directory_listing_description','notes','renewal_dates','turnover');
		
		foreach($variable_array as $key=>$val){		
			${$val} = addslashes($buffer[$key]);
		}
		
		if(isset($renewal_dates) && $renewal_dates !=""){
			$renewal_date_array = explode("/",$renewal_dates);
			$renewal_dates = $renewal_date_array[2]."-".$renewal_date_array[1]."-".$renewal_date_array[0];
		}else{
			$renewal_dates = "0000-00-00 00:00:00";
		}
		$turnover = str_replace(',','',$turnover);
		$turnover = str_replace('£','',$turnover);
		/************** Data Upload ***********************************/
			$sql_to_job = "SELECT * FROM job WHERE phone_no = '$phone_no' AND is_funeral = 1 ";
			$rs_to_job = mysqli_query($conn,$sql_to_job);
			$num_rows = mysqli_num_rows($rs_to_job);
			
			$sector_ids = "";
			$sector_id1 = utility::getTableFieldByTwoCondition("keycode","id","name","sector","code",$sector);
			if(!(isset($sector_id1) && $sector_id1 !="")){
			mysqli_query($conn,"INSERT INTO keycode(name,code) VALUE('sector','$sector')");
			$sector_ids = mysqli_insert_id($conn);
			}else{
			$sector_ids = $sector_id1;
			}
			
			if($num_rows == 0){
				$user_inserted++;
				$funeral_date = date("Y-m-d");
				$sql= "INSERT INTO job (`company_name`,`sector`,`trading_name_at_location`,`first_name`,`last_name`,`job_function`,`job_title`,`phone_no`,`industry_sector_directory_listing_description`,`notes`,`renewal_dates`,`turnover`,`is_funeral`,`funeral_date`) VALUES('$company_name','$sector_ids','$trading_name_at_location','$first_name','$last_name','$job_function','$job_title','$phone_no','$industry_sector_directory_listing_description','$notes','$renewal_dates','$turnover','1','$funeral_date')";
				mysqli_query($conn,$sql);
			}else{
				$user_updated++;
				$sql1 = "SELECT * FROM job WHERE phone_no = '$phone_no' AND is_funeral = 1 ";
				$rs1 = mysqli_query($conn,$sql1);
				if(mysqli_num_rows($rs1) > 0){
					$data = mysqli_fetch_assoc($rs1);
					$id = $data["id"];
				}
				mysqli_query($conn,"UPDATE job SET company_name = '$company_name', sector = '$sector_ids', trading_name_at_location = '$trading_name_at_location', first_name = '$first_name', last_name = '$last_name', job_function = '$job_function', job_title = '$job_title', phone_no = '$phone_no', industry_sector_directory_listing_description = '$industry_sector_directory_listing_description', notes = '$notes', renewal_dates = '$renewal_dates', turnover = '$turnover' WHERE id = '$id' ");
			}
		/************** End Data Upload ***********************************/
	}
  }
}
?>

<section class="content">
	<div class="row">
		<div class="col-xs-12">
			<!-- general form elements -->
              <div class="box box-default">
			
			<section class="bulkuploadheader">
			  <h1>
				Bulk Upload
			  </h1>
			</section>
			
			<form id="bulkuploadform" method="post" name="update" action="" enctype="multipart/form-data">
			<div style="background-color: #f9f9f9;margin:0 auto;text-align:center;">
				<br /><br />
				<span style="color:#F44336;font-weight:bold;font-style:italic;">To bulk upload, please follow the below mentioned rules
					<br>1. The upload file should be in <span style='color:black;font-size:22px;text-transform:uppercase;'>"CSV"</span> file format.
					<br>2. The csv file should be like <a target="_blank" href="<?echo $config->siteURL;?>/images/import_sample.csv">import_sample.csv</a></span>
					<br /><br /><br />
					<div class="upload-btn-wrapper">
						<button class="btn btn-primary">Enquires</button>
						<input type="file" name="import_gsi" id="import_gsi">
					</div>
					<div class="upload-btn-wrapper">
						<button class="btn btn-primary">Quotations</button>
						<input type="file" name="import_gsi_1" id="import_gsi_1">
					</div>
					<div class="upload-btn-wrapper">
						<button class="btn btn-primary">Clients</button>
						<input type="file" name="import_gsi_2" id="import_gsi_2">
					</div>
					<br /><br /><br />
					<div style="display:none;"><input type="submit" name="importfile" id="importfile" value="import file"></div>
					<!--<div style="display:none;"><input type="submit" name="importfile_1" id="importfile_1" value="import file"></div>
					<div style="display:none;"><input type="submit" name="importfile_2" id="importfile_2" value="import file"></div>-->
			</div>
			</form>
			<?php   
				if($user_inserted || $user_updated){
					echo '<div style="color:#F44336;text-align:center;padding:10px;">'.($user_inserted).' users has been inserted, '.($user_updated).' users has been updated and '.($user_skiped).' users has been skiped...</div>';
				}
			?>
			</div>
		</div>
	</div>
</section>
<style>
.content-wrapper{
	min-height: 593px;
}

.bulkuploadheader{
	padding:16px;
	background: aliceblue;
    text-align: center;
}

.bulkuploadheader h1{
	color: #808080;
	font-weight: bold;
	padding: 0;
    margin: 0;
    font-size: 25px;
}

.upload-btn-wrapper {
  position: relative;
  overflow: hidden;
  display: inline-block;
}

.exportbtns {
  border: 2px solid gray;
  color: gray;
  background-color: white;
  padding: 8px 20px;
  border-radius: 8px;
  font-size: 20px;
  font-weight: bold;
}

.choosefilebtn{
	border: 1px solid #2196F3;
    color: #2196F3;
    background-color: transparent;
    padding: 6px 20px;
    border-radius: 0;
    font-size: 15px;
}

.upload-btn-wrapper input[type=file] {
  font-size: 100px;
  position: absolute;
  left: 0;
  top: 0;
  opacity: 0;
}
</style>
<script type="text/javascript">
$(document).ready(function() {
	$("#import_gsi").change(function(){
		$('#bulkuploadform').attr("action","index?action=bulk_import&action1=enquireupload");
		if(confirm("Do you want to upload The Enquires File.")){
			$('#importfile').trigger("click");
		}
	});
	
	$("#import_gsi_1").change(function(){
		$('#bulkuploadform').attr("action","index?action=bulk_import&action1=quotationupload");
		if(confirm("Do you want to upload The Quotations File.")){
			$('#importfile').trigger("click");
		}
	});
	
	$("#import_gsi_2").change(function(){
		$('#bulkuploadform').attr("action","index?action=bulk_import&action1=clientsupload");
		if(confirm("Do you want to upload The Clients File.")){
			$('#importfile').trigger("click");
		}
	});
});     
</script>   