<?php
include_once ("_dirinfo.php");

include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."_ajaxManipulators/_class/class.gridData.php");
include_once (PATH_TO_ROOT."webuserspool/UsersPoolinit.php");

class showGroupImportGDM extends GridDataManipulator
{
	
	function selectAction()
	{
		$dataTable = $this->dataTable;
		if($this->table != 'data')
		{
				$importCols = array();
				$interColumns = array();
				$sql = "SHOW COLUMNS FROM data WHERE Field != 'id' AND Field != 'duplicated' AND Field != 'dupIds' AND Field != 'email'";
				If (!$this->rs = $this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
					
					while($row = $this->db->fetchArray($this->rs)){
						$importCols[] = $row[0];
					
						
					}			
				$sql = 'SELECT field_name FROM _InterviewerStructure WHERE is_leaf = 1';
				If (!$res = $this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}'; exit;}
				while ($row = $this->db->fetchArray($res))
				{
					$interColumns[] = $row[0];
				}
			if($this->table == 'columnsData')
			{
				$diff = array_diff($importCols,$interColumns);	
			}
			else 
			{
				$diff = array_diff($interColumns,$importCols);	
				array_unshift($diff,'[Ignore]');
			}
			$rows = array();
			foreach ($diff as $col) {
				$data['importCol'] = $col;
			//	$data['origCol'] = '';
				$rows[] = $data;
			}
			
			
			return '{success:true,rows:'.json_encode($rows).'}';
		}
		return parent::selectAction();
	}
	function updateAction()
	{
		if($_POST['field'] == 'email')
		{ //if email is changed we check if the new email exists and update duplicate columns!
			$val = $_POST['value'] = trim( strtolower($_POST['value'])  );
			$sql = $this->db->SQLexecute( "SELECT id FROM _Interviewer WHERE email = '$val'");
			if($row = $this->db->fetchRow($sql))
			{
				$sql = "UPDATE data SET duplicated = 1,dupIds=".$row[0]." WHERE id = ".$_POST['id'];
			}
			else 
				$sql =  "UPDATE data SET duplicated = 0,dupIds=0 WHERE id = ".$_POST['id'];
			
			$this->db->SQLexecute($sql);
			//calculate the new numbers
			parent::updateAction();
			
			$sql = " SELECT count(*) as n, duplicated FROM ".DB_WARPIT_USERSPOOL.".".$this->table." GROUP BY duplicated";
			If (!$result = $this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'",statement:"'.$sql.'" }'; exit;}
			
				
			$retParams[0] = 0; // not duplicated number
			$retParams[1] = 0; // duplicated number
			
			while ($records = $this->db->fetchAssoc($result)) $retParams[$records['duplicated']] = $records['n'];
			
			$sql = "SELECT a.* FROM $this->table as a INNER JOIN (SELECT COUNT(*) as cnt,b.email FROM $this->table as b WHERE b.email != '' GROUP BY b.email HAVING cnt > 1 ) as c ON a.email = c.email";
			If (!$result = $this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'",statement:"'.$sql.'" }'; exit;}
			
			$retParams[2] = $this->db->NumRows($result);
			
			parent::updateAction();
			return "{ success: true, existingUsers: '$retParams[1]', duplicatedUsers: '$retParams[2]', newUsers: '$retParams[0]'  }";
			
			
			
		}
		return parent::updateAction();
	}
	
	function importGroupAction()
	{
		$groupId = $_POST['groupId'];
		$impCols = json_decode( stripslashes($_POST['impCols'])  );
		$origCols = json_decode( stripslashes($_POST['origCols'])  );
		
		$InterTable = WUP_INTERVIEWER;
		$dataTable = $this->dataTable;
		
		//we set empty emails to NULL so that we don't get duplicate key errors!
		$sql = "UPDATE ".DB_WARPIT_USERSPOOL.".".$this->dataTable." SET email = NULL WHERE CHAR_LENGTH(email) < 3";
		//echo $sql;
		If (!$this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'"}'; exit;}
	
		
	/*	$sql = "SELECT `".implode('`,`',$impCols)."` FROM data WHERE duplicated = 0";
		If (!$this->rs = $this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
		while($row = $this->db->fetchAssoc($this->rs)){
			$sql = "INSERT INTO _Interviewer (".implode(',',$origCols).") VALUES ( '".  implode("','", str_replace( array("'",'"'), array("\'",'\"') , $row )  )  ."')";
			if (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
			$ind = $this->db->GetLastInsertId();
			$sql = "INSERT INTO _group_link VALUES ( $groupId, $ind ) ";
			if (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
			
		}*/
		//we get the AI value
		
	//	$sql = "LOCK TABLES $InterTable WRITE,$dataTable READ,_group_link WRITE";
	//	If (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
	//	$unlockSql = "UNLOCK TABLES";
		
			
		
		$lastId = $this->db->getAutoIncVal($InterTable);
		
		$sql = "INSERT INTO $InterTable (".implode(',',$origCols).") SELECT `".implode('`,`',$impCols)."` FROM $dataTable WHERE duplicated = 0";
		If (!$this->db->SQLexecute($sql)) {
			$err = '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
			
			$sql = "DELETE FROM $InterTable WHERE id >= $lastId";
			$this->db->SQLexecute($sql);
			$this->db->SQLexecute($unlockSql);
			
			return $err;
		
		}
		//now let's insert all 'new' IDs into group_link
		$sql = "INSERT INTO ".WUP_GROUP_LINK." SELECT $groupId,id FROM $InterTable WHERE id >= $lastId";
		If (!$this->db->SQLexecute($sql)) {
			$err = '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';
			
			$sql = "DELETE FROM $InterTable WHERE id >= $lastId";
			$this->db->SQLexecute($sql);
			$this->db->SQLexecute($unlockSql);
			
			return $err;
		
		}
		
		
		
		
		
		
		if( $_POST['withDups'] == 1 )
		{
			
			$cnt = count($origCols);
			$set = array();
			for ($i = 0;$i<$cnt;$i++)
			{
					$set[] = "a.".$origCols[$i]." = b.".$impCols[$i];
			}
			
			$sql = "UPDATE _Interviewer as a, ".$dataTable." as b SET ".implode(",",$set)." WHERE a.id = b.dupIds AND b.duplicated = 1 ";
			//echo $sql;
			//exit;
			if (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
			
		}
		
		$dupsToGroups = json_decode( stripslashes($_POST['dupsToGroups'])  );
		//$dupsToGroups[] = $groupId;
		if(count($dupsToGroups) > 0)
		{
			$sql = "SELECT dupIds FROM $dataTable WHERE duplicated = 1";
				If (!$this->rs = $this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
				while($row = $this->db->fetchArray($this->rs)){
					$usr = $row[0];
					foreach ($dupsToGroups as $grp) {
						$val[] = "($grp,$usr)";
					}
					
				}
			if(is_array($val))
			{
				$sql = "INSERT IGNORE INTO _group_link VALUES ".implode(',',$val);
				//echo $sql;
				If (!$this->rs = $this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
				
			}
		}
		//$this->db->SQLexecute($unlockSql);
			
		
		return '{success:true}';
	}
	function numDuplicatedAction()
	{
		$dataTable = 'data';
		
		//check if there are duplicate users!
		$dupExist = 0;
		$sql = "SELECT COUNT(*) as cnt FROM $dataTable as b WHERE b.email != '' GROUP BY b.email HAVING cnt > 1";
		If (!$rs = $this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
		if($row = $this->db->fetchArray($rs))
			$dupExist = 1;
		
		$sql = "SELECT COUNT(*) FROM $dataTable WHERE duplicated = 1";
		If (!$rs = $this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
		$row = $this->db->fetchArray($rs);
		$num = $row[0];
		return "{success:true,numDup:$num,dupExist:$dupExist}";
	}
	
}

$db->ChangeDatabase($_REQUEST['database']);


$manipulator = new showGroupImportGDM($db,$_REQUEST['RMtable']);
$manipulator->dataTable = 'data';


$comm = $_REQUEST['action'].'Action';
if(method_exists($manipulator,$comm))
	echo $manipulator->$comm();
else 
{
	
	Echo '{success:false,error:"Unknown action!"}';	
	
}


?>
