<?php
die('Invailid Url');
set_time_limit(0);
ini_set('memory_limit','1024M');
ini_set('post_max_size','32M');
ini_set('upload_max_filesize ','32M');

$destdir  = "live-data-migration-csvs/";
$name     = "customer_enquiries_master.csv";
$destfile = $destdir.$name;  

$batch_size = 3000;
$table_name = "job";
$records_inserted = 0;
$records_updated = 0;  
                                   
$flag = true;
$fp = fopen($destfile, 'r');
while (!feof ($fp)){
  $buffer = fgetcsv($fp, 1000000, ',');  
  if($flag) { 
	  if($buffer){
			$variable_array = array("company_name","index","Source","Address1","Address2","Address3","City","County","Country","Zone","Postcode","Status","FirstContact","LastContact","FollowUpDate","FollowUp","EnquiryValue","Notes","Salutation","FirstName","Surname","Telephone1","Email1","Salutation2","FirstName2","Surname2","Telephone2","Email2","Salutation3","FirstName3","Surname3","Telephone3","Email3","Product1","Product2","Product3","Product4","Product5");
			foreach($variable_array as $key=>$val){		
				${$val} = addslashes(trim(strtolower($buffer[$key])));
			}
			if($company_name =="company_name"){
				continue;
			}
			$created_by				= $_SESSION['user_id'];
			if(isset($company_name) && $company_name !=""){
				$org_id = utility::getTableFieldById("organisation_management","id","company_name",$company_name);
				if(!(isset($org_id) && $org_id !="")){
					mysqli_query($conn,"INSERT INTO organisation_management(company_name,is_organisation,organisation_by) VALUE('$company_name','1','$created_by')");
					$org_id = mysqli_insert_id($conn);
				}
			}else{
				$org_id = "";
			} 
			$EnquiryValue=str_ireplace('?','',$EnquiryValue);
			if(isset($County) && $County !=""){
				$county_id = utility::getTableFieldById("county","id","county",$County);
				if(!(isset($county_id) && $county_id !="")){
					mysqli_query($conn,"INSERT INTO county(county) VALUE('$County')");
					$county_id = mysqli_insert_id($conn);
				}
			}else{
				$county_id = "";
			} 
			
			if(isset($Country) && $Country !=""){
				$country_id = utility::getTableFieldById("country","id","country",$Country);
				if(!(isset($country_id) && $country_id !="")){
					mysqli_query($conn,"INSERT INTO country(country,zone) VALUE('$Country','$Zone')");
					$country_id = mysqli_insert_id($conn);
				}
			}else{
				$country_id = "";
			} 
			
		 	if(isset($Source) && $Source !=""){
				$source_id = utility::getTableFieldByTwoCondition("keycode","id","name","source","code",$Source);
				$max_order = Keycode::getMaxKeycodeOrderByName("source") + 1;
				if(!(isset($source_id) && $source_id !="")){
					mysqli_query($conn,"INSERT INTO keycode(name,code,ord) VALUE('source','$Source','$max_order')");
					$source_id = mysqli_insert_id($conn);
				}
			}else{
				$source_id = "";
			}  
			$FirstContact 			= date('Y-m-d', strtotime($FirstContact));
			$LastContact 			= date('Y-m-d', strtotime($LastContact));
			$FollowUpDate			= date('Y-m-d', strtotime($FollowUpDate));
			$created_on				= date("Y-m-d H:i:s");	
			$created_by				= $_SESSION['user_id'];
			$updated_by				= $_SESSION['user_id'];
			$enquiry_date 			=date("Y-m-d");
			
			 $records_inserted++;
			 $sql =  "INSERT INTO $table_name(`org_id`,`company_name`,`index_id`,`source`,`address1`,`address2`,`address3`,`city`,`County`,`Country`,`country_zone`,`postcode`,`status`,`first_contact`,`last_contact`,`follow_update`,`follow_upby`,`enquiry_value`,`notes`,`is_imported`,`created_by`,`created_on`,`updated_by`) VALUE('$org_id','$company_name','$index','$source_id','$Address1','$Address2','$Address3','$City','$county_id','$country_id','$Zone','$Postcode','$Status','$FirstContact','$LastContact','$FollowUpDate','$FollowUp','$EnquiryValue','$Notes','1','$created_by','$created_on','$updated_by')"; 
			
			 mysqli_query($conn,$sql);
			$job_id = mysqli_insert_id($conn);
			 
			/* if(isset($note) && $note !=""){
				mysqli_query($conn,"INSERT INTO notes(job_id,note_text,note_date) VALUE('$job_id','$note','$note_date')");
			} */
			/* FIRST ROW */
			   if(isset($Salutation) && $Salutation !=""){
							$Salutation_id = utility::getTableFieldByTwoCondition("keycode","id","name","title","code",$Salutation);
							$max_order1 = Keycode::getMaxKeycodeOrderByName("title") + 1;
							if(!(isset($Salutation_id) && $Salutation_id !="")){
								mysqli_query($conn,"INSERT INTO keycode(name,code,ord) VALUE('title','$Salutation','$max_order1')");
								$Salutation_id = mysqli_insert_id($conn);
							}
						
					$Salutation_id = $Salutation_id; 
				}
				$name1='';
				if(isset($FirstName) && $FirstName !=""){
					$FirstName = $FirstName; 
					$name1 = $FirstName; 
				}
				if(isset($Surname) && $Surname !=""){
					$Surname = $Surname; 
					$name1 .=" ".$Surname; 
				}
				if(isset($Telephone1) && $Telephone1 !=""){
					$Telephone1=utility::app_crypt($Telephone1,'e');
					$name1 .=" ".$Telephone1; 
				}
				if(isset($Email1) && $Email1 !=""){
					$Email1=utility::app_crypt($Email1,'e');
					$name1 .=" ".$Email1; 
				}
				
			    if(isset($name1) && $name1 !=""){
					mysqli_query($conn,"INSERT INTO job_enquiry_mapping(job_id,title,first_name,last_name,phone_no,email) VALUE('$job_id','$Salutation_id','$FirstName','$Surname','$Telephone1','$Email1')");
				}
				/* SECOND ROW */
				
				$name2='';
				if(isset($Salutation2) && $Salutation2 !=""){
					$Salutation_id_2 = utility::getTableFieldByTwoCondition("keycode","id","name","title","code",$Salutation2);
							$max_order2 = Keycode::getMaxKeycodeOrderByName("title") + 1;
							if(!(isset($Salutation_id_2) && $Salutation_id_2 !="")){
								mysqli_query($conn,"INSERT INTO keycode(name,code,ord) VALUE('title','$Salutation2','$max_order2')");
								$Salutation_id_2 = mysqli_insert_id($conn);
							}
						
					$Salutation_id_2 = $Salutation_id_2;
				}
				if(isset($FirstName2) && $FirstName2 !=""){
					$FirstName2 = $FirstName2; 
					$name2 = $FirstName2; 
				}
				if(isset($Surname2) && $Surname2 !=""){
					$Surname2 = $Surname2; 
					$name2 .=" ".$Surname2; 
				}
				if(isset($Telephone2) && $Telephone2 !=""){
					$Telephone2=utility::app_crypt($Telephone2,'e');
					$name2 .=" ".$Telephone2; 
				}
				if(isset($Email2) && $Email2 !=""){
					$Email2=utility::app_crypt($Email2,'e');
					$name2 .=" ".$Email2;
					
				}
			    if(isset($name2) && $name2 !=""){
					mysqli_query($conn,"INSERT INTO job_enquiry_mapping(job_id,title,first_name,last_name,phone_no,email) VALUE('$job_id','$Salutation_id_2','$FirstName2','$Surname2','$Telephone2','$Email2')");
				}
				/* THIRD ROW*/
				
				$name3='';
				if(isset($Salutation3) && $Salutation3 !=""){
					$Salutation_id_2 = utility::getTableFieldByTwoCondition("keycode","id","name","title","code",$Salutation3);
							$max_order3 = Keycode::getMaxKeycodeOrderByName("title") + 1;
							if(!(isset($Salutation_id_2) && $Salutation_id_2 !="")){
								mysqli_query($conn,"INSERT INTO keycode(name,code,ord) VALUE('title','$Salutation3','$max_order3')");
								$Salutation_id_3 = mysqli_insert_id($conn);
							}
						
					$Salutation_id_3 = $Salutation_id_3;
				}
				if(isset($FirstName3) && $FirstName3 !=""){
					$FirstName3 = $FirstName3; 
					$name3 = $FirstName3; 
				}
				if(isset($Surname3) && $Surname3 !=""){
					$Surname3 = $Surname3; 
					$name3 .=" ".$Surname3; 
				}
				if(isset($Telephone3) && $Telephone3 !=""){
					$Telephone3=utility::app_crypt($Telephone3,'e');
					$name3 .=" ".$Telephone3;
				}
				if(isset($Email3) && $Email3 !=""){
					$Email3=utility::app_crypt($Email3,'e');
					$name3 .=" ".$Email3;
				}
			    if(isset($name3) && $name3 !=""){
					mysqli_query($conn,"INSERT INTO job_enquiry_mapping(job_id,title,first_name,last_name,phone_no,email) VALUE('$job_id','$Salutation_id_3','$FirstName3','$Surname3','$Telephone3','$Email3')");
				}
				/* PRODUCT ADD 1*/
				if(isset($Product1) && $Product1 !=""){
				$product_id = utility::getTableFieldById("product","id","product",$Product1);
				if(!(isset($product_id) && $product_id !="")){
					mysqli_query($conn,"INSERT INTO product(product) VALUE('$Product1')");
					$product_id = mysqli_insert_id($conn);
				}
				}else{
					$product_id = "";
				} 
				
			    if(isset($Product1) && $Product1 !=""){
					mysqli_query($conn,"INSERT INTO job_product_mapping(job_id,product,created_by,created_on,updated_by) VALUE('$job_id','$product_id','$created_by','$created_on','$updated_by')");
				}
				
				/* PRODUCT ADD 2*/ 
				if(isset($Product2) && $Product2 !=""){
				$product_id2 = utility::getTableFieldById("product","id","product",$Product2);
				if(!(isset($product_id2) && $product_id2 !="")){
					mysqli_query($conn,"INSERT INTO product(product) VALUE('$Product2')");
					$product_id2 = mysqli_insert_id($conn);
				}
				}else{
					$product_id2 = "";
				} 
				 if(isset($Product2) && $Product2 !=""){
					mysqli_query($conn,"INSERT INTO job_product_mapping(job_id,product,created_by,created_on,updated_by) VALUE('$job_id','$product_id2','$created_by','$created_on','$updated_by')");
				} 
				
				/* PRODUCT ADD 3*/
				if(isset($Product3) && $Product3 !=""){
				$product_id3 = utility::getTableFieldById("product","id","product",$Product3);
				if(!(isset($product_id3) && $product_id3 !="")){
					mysqli_query($conn,"INSERT INTO product(product) VALUE('$Product3')");
					$product_id3 = mysqli_insert_id($conn);
				}
				}else{
					$product_id3 = "";
				} 
				 if(isset($Product3) && $Product3 !=""){
					mysqli_query($conn,"INSERT INTO job_product_mapping(job_id,product,created_by,created_on,updated_by) VALUE('$job_id','$product_id3','$created_by','$created_on','$updated_by')");
				} 
				
				/* PRODUCT ADD 4*/
				if(isset($Product4) && $Product4 !=""){
				$product_id4 = utility::getTableFieldById("product","id","product",$Product4);
				if(!(isset($product_id4) && $product_id4 !="")){
					mysqli_query($conn,"INSERT INTO product(product) VALUE('$Product4')");
					$product_id4 = mysqli_insert_id($conn);
				}
				}else{
					$product_id4 = "";
				} 
				 if(isset($Product4) && $Product4 !=""){
					mysqli_query($conn,"INSERT INTO job_product_mapping(job_id,product,created_by,created_on,updated_by) VALUE('$job_id','$product_id4','$created_by','$created_on','$updated_by')");
				} 
				
				/* PRODUCT ADD 5*/
				if(isset($Product5) && $Product5 !=""){
				$product_id5 = utility::getTableFieldById("product","id","product",$Product5);
				if(!(isset($product_id5) && $product_id5 !="")){
					mysqli_query($conn,"INSERT INTO product(product) VALUE('$Product5')");
					$product_id5 = mysqli_insert_id($conn);
				}
				}else{
					$product_id5 = "";
				} 
				 if(isset($Product5) && $Product5 !=""){
					mysqli_query($conn,"INSERT INTO job_product_mapping(job_id,product,created_by,created_on,updated_by) VALUE('$job_id','$product_id5','$created_by','$created_on','$updated_by')");
				} 
	  }
  }
}
?>
<?php 
if($records_inserted){    
  echo '<br><span style="font-family:calibri;color:red;">'.($records_inserted).' records has been inserted...</span>';
}?>
 <?php 
if($records_updated){    
  echo '<br><span style="font-family:calibri;color:red;">'.($records_updated).' records has been updated...</span>';
}?>