<?php

include("_dirinfo.php");
include (PATH_TO_ROOT."init.php");

$db->ChangeDatabase(DB_WARPIT_USERSPOOL);
$isFile =  $_FILES['csvfilepath']['tmp_name'] ? true:false;
if ($isFile || $_POST['pasteData']) {
	
	if ($isFile)
	{
		$fh = fopen($_FILES['csvfilepath']['tmp_name'], 'r');
		
		$struktura = trim(fgets($fh));
		$struktura = str_replace(array("'",'"','.',' '),'',$struktura);
		
		
		fclose($fh);
		//we read the line and column separator
		$lineSep = $_POST['lineSep'];
		$columnSep = stripslashes($_POST['columnSep']);
		
		//we detect the encoding of the file
		$fileEncoding = strtolower( mb_detect_encoding($struktura) );
		$sql = "SHOW CHARACTER SET WHERE Charset LIKE '$fileEncoding'";
		if(!$res = $db->SQLexecute($sql) ){echo '{"success":false,"error":"Error with charsets!"}'; exit; }
		if($row = $db->fetchAssoc($res))
		{//if the encoding matches one of the databases legal encoding we use it in the load querry
			$mysqlEncoding = $row['Charset'];
		}
		else
		{//otherwise we try to convert it
		
		//array to convert mb_detect_encoding values to mysql legal charset
			$encodTable['utf-8'] = 'UTF8';
			$encodTable['ascii'] = 'ascii';
			
			$mysqlEncoding = $encodTable[ $fileEncoding  ];
		}
		
		if(!$mysqlEncoding)
		{
			echo '{"success":false,"error":"Unknown character set: '.$encLine.'!"}'; exit;
		}
		
		
	}
	else //a paste event has occured...we try to read the first line 
	{
		$data = explode("\n",$_POST['pasteData']);
		$struktura = array_shift($data);
		if(!strstr($struktura,"\t"))//this means we have only one column! it has to be email
		{
			//we check if first column is an email...if it is we prepend it back to the structur
			if(strstr($struktura,"@"))
			{
				array_unshift($data,$struktura);
			}
			$struktura = 'email';
			
		}
		$columnSep = "\t";
	}
	$tmp = explode($columnSep,$struktura);
		//if encoding is UTF-8 we remove a very funny character that coused much much headaches
		if(mb_check_encoding($struktura,'UTF-8'))
		{
			$tempJSon = json_encode($tmp);
			$tempJSon = str_replace('\ufeff','',$tempJSon);
			$tmp = json_decode($tempJSon);
		}	
	//echo json_encode($tmp);
	$table = "data";
	
	$sql = "DROP TABLE IF EXISTS ".DB_WARPIT_USERSPOOL.".".$table;
	If (!$db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'"}'; exit;}
	
	
	
	// create the table
	if ($tmp) {
		
		for($i=0;$i<count($tmp) ;$i++) {
			$tmp[$i] = str_replace(' ','_',trim($tmp[$i]) );
			$tmpFld = trim($tmp[$i]);
		 	$sqlTmp[] = "`".$tmpFld."` VARCHAR( 255 ) DEFAULT NULL";
		 	
		}
		
		$sql = " CREATE TABLE ".DB_WARPIT_USERSPOOL.".".$table." (
			". implode(',',$sqlTmp).  "
			)ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
		
	//	echo $sql;
		If (!$db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'",statement:"'.$fileEncoding.'" }'; exit;}
	
	}
	if($isFile)
	{
	// importing raw data into table
	$sql = "LOAD DATA LOCAL INFILE '".$_FILES['csvfilepath']['tmp_name']."' 
				INTO TABLE  ".DB_WARPIT_USERSPOOL.".".$table." CHARACTER SET '$mysqlEncoding' FIELDS TERMINATED BY '$columnSep' ENCLOSED BY '\"' LINES TERMINATED BY '$lineSep' IGNORE 1 LINES ";
	if(ini_get('magic_quotes_gpc'))
		$sql = stripslashes($sql);
	If (!$db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'"}'; exit;}
	}
	else 
	{
		$l = count($data);
		for($i=0;$i<$l;$i++)
		{
			if($data[$i])
				$data[$i] = '("'.str_replace("\t",'","', addslashes($data[$i]) ).'")';
			else unset($data[$i]);
		}
		$sql = "INSERT INTO $table VALUES ".implode(',',$data);
			If (!$db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'"}'; exit;}
	
	}
	
	// adding id field with autoincrement numbers and 
	// adding field duplicated which indicates if some user is allready in webuserspool tables...
		
	$sql = "ALTER TABLE  ".DB_WARPIT_USERSPOOL.".".$table." 
				ADD `id` INT NOT NULL AUTO_INCREMENT FIRST , 
				ADD `duplicated` TINYINT( 1 ) NOT NULL, 
				ADD `dupIds` INT( 5 ) NOT NULL,
				ADD PRIMARY KEY ( id )";
	
	If (!$db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'"}'; exit;}
	
	$sql = "UPDATE ".DB_WARPIT_USERSPOOL.".".$table." SET email = REPLACE( LOWER( TRIM(email) ) ,';',''  )";
	If (!$db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'",statement:"'.$sql.'" }'; exit;}
	
	
	
	$sql = " UPDATE ".DB_WARPIT_USERSPOOL.".".$table." as a, ".DB_WARPIT_USERSPOOL."._Interviewer as b SET	a.duplicated = 1, a.dupIds = b.id WHERE a.email = b.email AND a.email != ''";
	If (!$db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'",statement:"'.$sql.'" }'; exit;}
	
	
	$sql = " SELECT count(*) as n, duplicated FROM ".DB_WARPIT_USERSPOOL.".".$table." GROUP BY duplicated";
	If (!$result = $db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'",statement:"'.$sql.'" }'; exit;}
	
		
	$retParams[0] = 0; // not duplicated number
	$retParams[1] = 0; // duplicated number
	
	while ($records = $db->fetchAssoc($result)) $retParams[$records['duplicated']] = $records['n'];
	
	$sql = "SELECT a.* FROM $table as a INNER JOIN (SELECT COUNT(*) as cnt,b.email FROM $table as b WHERE b.email != '' GROUP BY b.email HAVING cnt > 1 ) as c ON a.email = c.email";
	If (!$result = $db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'",statement:"'.$sql.'" }'; exit;}
	
	$retParams[2] = $db->NumRows($result);
	
	
	
	$sql = 'SELECT field_name FROM _InterviewerStructure';
	If (!$res = $db->SQLexecute($sql)){echo '{"success":false,"error":"'.$db->GetError().'",statement:"'.$sql.'" }'; exit;}
	$interColumns[] = 'email';
	while ($row = $db->fetchArray($res))
	{
		$interColumns[] = $row[0];
	}
	
	$colDiff = array_diff($tmp,$interColumns);
	
	echo "{ success: true,enc:'$mysqlEncoding', sql:'$sql', existingUsers: '$retParams[1]', duplicatedUsers: '$retParams[2]', newUsers: '$retParams[0]',columns: ".json_encode($tmp) .",diffs: ".json_encode($colDiff) ."  }";
}
else 
{
	echo "{ success: false,error:'File not found!'}";
}

?>

