<?php
	
require_once 'write_log.php';
require_once 'sparql.php';
require_once 'sqlUtils.php';
require_once 'sqlGeneratorClass.php';
require_once 'demosettings.php';
include_once 'searchWord.php';
require_once 'slotToCaption.php';
require_once 'systemCheckup.php';
require_once 'allConceptsData.php';
require_once 'allConceptArr.php';
require_once 'cacheEng.php';
require_once 'asyncRequst.php';
use setasign\Fpdi\Fpdi;

const NOT_REVIEWED = "Not-Reviewed";

$naGlobalSlots = array("family-name", "given-name", "middle-name", "maiden-name", "hispanic-maternal-name", "son", "nisba", //"kunya" - kunya is merely a dynamic instance which refers to slot "son"
		"father-patrimonyal-name", "grandfather-patrimonyal-name", "tribal-name", "unknown-name-role", "matronymic-name");


$action = filter_input(INPUT_GET,"Action");
if($action=="getStatistic"){
    require_once("userCake/models/config.php");
    require_once 'authorizations.php';
    $username = filter_input(INPUT_GET, "username");
    call_user_func($action,$username);
}

//run only in CLI (not accessible via web)
if (IS_CLI) {
    
    if (isset($argv[1]) && $argv[1] == "cleanupTempViews"){
        $mysqli = sqlCreateConnection("parseUtils - cleanupTempViews");
        cleanupAllTempViews($mysqli);

    }
    if (isset($argv[2]) && $argv[2] == "getStatistic") {
        $mysqli = sqlCreateConnection("parseUtils - getStatistic");      
        require_once("userCake/models/config.php");
        require_once 'authorizations.php';
        getStatistic($mysqli, $argv[3], null,  false);
    }
//     if (isset($argv[2]) && $argv[2] == "cleanDB") {
//         $response = cleanDB($mysqli, $userdetails);
//         write_to_log_depercated("INFO", "clean DB from CLI");
//     }
    
}

function getStatistic($mysqli, $username, $isAdmin, $from_background = false, $savedSearch = null){	
	global $CURRENT_SOURCE_FOLDER,$PHP_FOLDER;					
	global $skipDuplicate;
	
	$response = array();	
	$isAdmin = $isAdmin ?? getUserPermission(CAN_SHOW_ALL_USER,$username);	
	$response = getCaseStatisticTabel($mysqli,$isAdmin,$skipDuplicate,$username, $from_background, $savedSearch);
	return $response;
	//mark the in_process as false
}	

function getCaseStatisticTableSql($date_sql, $name) {
//if date_sql is not empty, add to the sql queries the date_sql condition
    if (empty($date_sql) || $name == "Earliest Date Mentioned" || $name == "Last Date Mentioned") {
        return;
    }

    $sql = " ";
    if ($name == "Documents") { //the Documents use the files hence has no docId
        $sql .= "AND files.id IN $date_sql";
    } else {
        $sql .= "AND table_name.docId IN $date_sql";
    }

    return $sql;
}

function getDashboardSavedSearch($mysqli, $username){
    global $hide_saved_search;
    $response = array();
    
    $lsid = getLastSavedSearchId($mysqli, $username);
    //add saved search
    $sql = "SELECT id,name FROM savedsearch WHERE username='$username' AND id != $lsid";
    $res = sqlQuery($mysqli, $sql);
    if ($res && $res->num_rows){
        while($obj = $res->fetch_object()){
            $savedsearch[$obj->name] = $obj->id;
        }
        foreach ($savedsearch as $k=>$v){
            $hide = false;
            foreach($hide_saved_search as $hid_inst){
                if (strpos($k, $hid_inst) !== false) {
                    $hide = true;
                    break;
                }
            }
            if (strlen($v) > 0 && !$hide){
                $s = "SELECT docid FROM savedsearchid WHERE id=$v";
                $sql = "SELECT COUNT(id) AS c FROM files WHERE id IN ($s)";
                $res = sqlQuery($mysqli, $sql);
                $count = $res->fetch_object()->c;
                $sql = "SELECT COUNT(DISTINCT docId) AS c FROM filetags WHERE name='Reviewed' AND docId IN ($s)";
                $res = sqlQuery($mysqli, $sql);
                $count_reviewed = $res->fetch_object()->c;
                $response["savedsearch"][$k] = $k . " ($count_reviewed reviewed out of $count)";
            }
        }
    }
    return $response;
}


function getCaseStatisticTabel($mysqli, $isAdmin, $skipDuplicate, $username, $from_background = false, $savedSearch = null){
    $response = array();
    $params = condForCaseStatisticTabel($mysqli, $isAdmin,$username);

    $user_cond = !$isAdmin ? "files.username='$username'" : "TRUE";
    if(!empty($savedSearch)){
        $info = getSavedSearch($mysqli, false, $savedSearch);
        $lastSearch = " docId IN (SELECT docid FROM savedsearchid WHERE id=". $info[0]['id'].") ";
        $lastSearchId = " files.id IN (SELECT docid FROM savedsearchid WHERE id=". $info[0]['id'].") ";
    }
    else{
        $lastSearch = " TRUE ";
        $lastSearchId = " TRUE ";
    }
    $response['sentiment'] = getOverviewSentiment($mysqli, $username, $lastSearch);
    
    if ($skipDuplicate) {
        if (strlen($params['usersWhereClauseId']) > 1){
            $document = "SELECT count(scan_time) AS Result FROM unique_files LEFT JOIN files ON files.id=unique_files.id WHERE $params[usersWhereClauseId] AND $params[file_date_sql] AND $user_cond AND $lastSearchId";
        }
        else{
            $document = "SELECT count(scan_time) AS Result FROM unique_files WHERE $params[usersWhereClauseId] AND $params[file_date_sql] AND $user_cond AND $lastSearchId";
            
            }
    }else {
        $document = "SELECT count(scan_time) AS Result FROM files WHERE $params[usersWhereClauseId] AND $params[file_date_sql] AND $user_cond AND $lastSearchId";
    }

    
    global $numberOfUsersTablesName;
    $left_join_files="";
    if(!empty($params['usersWhereClause']) && $params['usersWhereClause']!==" WHERE TRUE "){
      $left_join_files="LEFT JOIN files ON files.id=table_name.docid";
    }
   
    //Get the configured key_stats concepts
    require_once 'settings.php';
    $settings_class =  new Settings($mysqli, $username);
    $key_stats =  $settings_class->getSettings('getKeyStats', 'getKeyStats');
    
    $i=0;
    foreach ($key_stats as $element) {
        //if it's unchecked
        if(!$element->checked){
            continue;
        }
        $name = $element->name;
        $table = $element->table;
        
        //get concept index
        $id = getidFromConcept($mysqli, $table);
        
        $value_col = in_array($name, array('Languages','Theatres','Topics','Priority','Political Orientation')) ? "VALUE" : "NAME";
        
        $sql = "SELECT COUNT(DISTINCT $value_col) AS Result FROM $table  AS table_name $left_join_files WHERE $params[usersWhereClause] AND $params[table_date_sql] AND $lastSearch";
        $sql .= getCaseStatisticTableSql($params['date_sql'], $name);

        $res = sqlQuery($mysqli, $sql);
        write_to_log("PERMORMENCE", "case statistic sql - $sql");
        if ($res && $res->num_rows) { 
            $curr_res = [];
            $curr_res["name"] = $name;    
            $curr_res["scale"] = $res->fetch_object()->Result;  
            $curr_res["table"] = $table;
            $curr_res["id"] = $id;
            $response[$name] = $curr_res;
            
            
        }
    }
    
    $name2sql = array(
        "Documents" => $document,
        "Languages" => "SELECT COUNT(DISTINCT value) AS Result FROM languages AS table_name $left_join_files WHERE $params[filesUsersAnd] AND $params[table_date_sql] AND value != 'Unknown language' AND value IS NOT NULL AND value != '' AND $lastSearch",
        "Earliest Date Mentioned" => "SELECT date AS Result FROM files WHERE $params[usersAnd] AND date > '$params[from_date]'  AND $params[file_date_sql] AND $lastSearchId ORDER BY date LIMIT 1",
        "Last Date Mentioned" => "SELECT date AS Result FROM files WHERE $params[usersWhereClause] AND $params[file_date_sql] AND $lastSearchId ORDER BY date DESC LIMIT 1",
        "Authors" => "SELECT COUNT(DISTINCT value) AS Result FROM $numberOfUsersTablesName AS table_name $left_join_files WHERE  $params[usersWhereClause] AND $params[table_date_sql] AND $lastSearchId",
    );
    foreach($name2sql as $name=>$sql){
        $res = sqlQuery($mysqli, $sql);
        write_to_log("PERMORMENCE", "case statistic sql - $sql");
        if ($res && $res->num_rows) {
            $curr_res = [];
            $curr_res["name"] = $name;
            $curr_res["scale"] = $res->fetch_object()->Result;
            $curr_res["table"] = $name;
            $response[$name] = $curr_res;
        }
        elseif($name == 'Last Date Mentioned' || $name == 'Earliest Date Mentioned'){
            $curr_res["name"] = $name;
            $curr_res["scale"] = null;
            $curr_res["table"] = $name;
            $response[$name] = $curr_res;
            
        }
    }
 
    
    if(empty($from_background) && empty($savedSearch)){
        CacheEng::updateResultsAndCache($mysqli, $username, 'statistics', 'key_stats', $response);
    }
    return $response;
}

function dateDetails($mysqli, $username = null){
    
    //sets the pastPeriod addon
    $pastPeriod = $_REQUEST['pastPeriod'] ??  getPastPeriod($mysqli, $username);
    $dates_arr = getPastPeriod_ExtraData($pastPeriod);
    if (!is_null($dates_arr)) {
        $dates_range = $dates_arr["dates_range"];
        $from_date = $dates_range["from"];
        $date_sql = $dates_arr["date_sql"];
    } else {
        $from_date = new DateTime('1970-01-01');
        $from_date = $from_date->format('Y-m-d') . " 00:00:00";
        $date_sql = "";
    }
    return array("from_date" =>$from_date ,"date_sql" => $date_sql);
    

}
function condForCaseStatisticTabel($mysqli, $isAdmin,$username,$savedsearchId=0){
    if ($savedsearchId > 0) {
        $lastSearch = " docId IN (SELECT docid FROM savedsearchid WHERE id=$savedsearchId)";
        $lastSearchId = " id IN (SELECT docid FROM savedsearchid WHERE id=$savedsearchId)";
    } else{
        $lastSearch = " TRUE ";
        $lastSearchId = " TRUE ";
    }
   if ($isAdmin) {//all variables must be defined under all conditions
        if (strlen($lastSearch) > 0) {
            $usersWhereClause = "  $lastSearch";
            $usersWhereClauseId = "  $lastSearchId";
            $filesUsersAnd = " $lastSearch  ";
            $usersAnd = " $lastSearchId  ";
        } else {
            $usersWhereClause = "  TRUE ";
            $usersWhereClauseId = "  TRUE ";
            $filesUsersAnd = " TRUE ";
            $usersAnd = " TRUE ";
        }
   } else {
       if (strlen($lastSearch) > 0) {
           $usersAnd = "files.username = '$username' AND $lastSearchId  ";
           $usersWhereClause = "  files.username = '$username' AND $lastSearch ";
           $filesUsersAnd = "files.username = '$username' AND $lastSearch  ";
           $usersWhereClauseId = "  files.username = '$username' AND $lastSearchId ";
       } else {
           $usersAnd = "files.username = '$username'  ";
           $usersWhereClause = "  files.username = '$username'";
           $filesUsersAnd = "files.username = '$username'  ";
           $usersWhereClauseId = "  TRUE ";
       }
    }
    
    $dates = dateDetails($mysqli, $username);
    $from_date = $dates["from_date"];
    $date_sql = $dates["date_sql"];
    $file_date_sql = !empty($dates["date_sql"]) ? " files.id IN $dates[date_sql]" : " TRUE ";
    $table_date_sql = !empty($dates["date_sql"]) ? "table_name.docId IN $dates[date_sql]" : " TRUE ";

    return array("usersAnd"=>$usersAnd,
                   "usersWhereClause" => $usersWhereClause,
                   "filesUsersAnd" => $filesUsersAnd,
                   "usersWhereClauseId" => $usersWhereClauseId,
                    "date_sql" => $date_sql,
                    "file_date_sql" => $file_date_sql,
                    "table_date_sql" => $table_date_sql,
                    "from_date" => $from_date
                   );   
   
}

function numOfTableObjects($mysqli, $table,$isAdmin,$username){
    $params = condForCaseStatisticTabel($mysqli, $isAdmin,$username);
    $left_join_files="";
    if(!empty($params['usersWhereClause']) && $params['usersWhereClause']!==" WHERE TRUE "){
        $left_join_files="LEFT JOIN files ON files.id=table_name.docid";
    }
    
    switch ($table) {
        case "person_object":
        case "organizational_identity":
        case "event":
        case "place_object":
            $sql = "SELECT COUNT(DISTINCT NAME) AS Result FROM $table AS table_name $left_join_files $params[usersWhereClause]";
            break;
        case "languages":
            $sql = "SELECT COUNT(DISTINCT value) AS Result FROM languages AS table_name $left_join_files WHERE $params[filesUsersAnd] value != 'Unknown language' AND value IS NOT NULL AND value != ''";
            break;
        case "document_topic":
        case "topics":
            $sql = "SELECT COUNT(DISTINCT VALUE) AS Result FROM document_topic AS table_name $left_join_files WHERE $params[filesUsersAnd] value IS NOT NULL AND value != ''";
            break;
        case "popular":  
           $sql = "SELECT COUNT(DISTINCT NAME) AS Result FROM person_object  AS table_name $left_join_files $params[usersWhereClause] AND pro <> ''  AND pro IS NOT NULL";
           break;
        case "unpopular":
            $sql = "SELECT COUNT(DISTINCT NAME) AS Result FROM person_object  AS table_name $left_join_files $params[usersWhereClause] AND anti  <> '' AND anti IS NOT NULL";
           break;
       
        default:
            return 0;
    } 
    
    $dates = dateDetails($mysqli);
 
    if (!empty($dates["date_sql"])) {
        $sql .= " AND table_name.docId IN $dates[date_sql]";
    }
    $res = sqlQuery($mysqli, $sql);
    write_to_log("PERMORMENCE", "case statistic sql - $sql");
    
    if ($res) {
        $num = $res->fetch_object()->Result;
    }
        
    return $num;
}

function first_key($array)
{
	reset($array);
	$result = key($array);
	reset($array);
	return $result;
}

function key2val($array, $key)
{
	return $array && array_key_exists($key, $array)? $array[$key] : NULL;
}

function getNAslots()
{
	global $naGlobalSlots;
	return $naGlobalSlots;
}

function slot2field($slot)
{
	return str_replace("-", "_", $slot);
}

function getNAfields()
{
	return array_map("slot2field", getNAslots());
}

function dumpDbg($name, $content, $saveasjson = true) {
    //for debug dumps - set $shouldDumDbg=true in config.php
    global $shouldDumpDbg;
    if ($shouldDumpDbg) {
        global $TEMP_FOLDER;
        file_put_contents("$TEMP_FOLDER/$name.txt", $saveasjson ? json_encode($content, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) : $content);
    }
}


function dumpDocsDbg($name, $dcid, $content, $saveasjson = true) {
    //for debug dumps - set $shouldDumDbg=true in config.php
    global $shouldDumpDbg;
    if ($shouldDumpDbg) {
        global $TEMP_FOLDER;
        if (!is_dir("$TEMP_FOLDER/$dcid/"))
            mkdir_full("$TEMP_FOLDER/$dcid/", 0777, true);
            if(file_exists("$TEMP_FOLDER/$dcid/$name.txt") ){
                return;
            }
        file_put_contents("$TEMP_FOLDER/$dcid/$name.txt", $saveasjson ? json_encode($content, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) : $content);
    }
}

function rdf2structs($name, $content, &$qname2node, &$pred2inst,
		&$class2subClasses, &$subClass2classes)
{
	//dumpDbg($name."contents.xml", $content);
	if (empty($content))
		return;

	$contentXml = new DOMDocument();
	$contentXml->loadXml($content);
	$contentXml->encoding = "utf-8";
	if (!array_key_exists($name, $qname2node)){
		$qname2node[$name] = array();
	}
	
	write_to_log("TRACE", "rdf2structs start iteration");
	
	foreach ($contentXml->firstChild->childNodes as $rdfNode)
	{
		if ($rdfNode->nodeName == "rdf:Description")
		{
			$qname = $rdfNode->attributes->getNamedItem("about")->value;
			if (array_key_exists($qname, $qname2node[$name])){
				//write_to_log("WARNING", "Found same node twice: " . $qname);
			}
			else{
				$qname2node[$name][$qname] = array();
			}
			$instanceStruct = &$qname2node[$name][$qname];
			foreach ($rdfNode->childNodes as $childNode)
			{
				if ($childNode->nodeType == XML_TEXT_NODE)
					continue;//echo $qname . " - unexpected text node: " . $childNode->nodeValue;

					
				$pred = $childNode->nodeName;
				$lang = NULL;
					
				if ($childNode->hasAttributes() && $childNode->attributes->item(0)->nodeName == "rdf:resource")
					$value = $childNode->attributes->item(0)->value;
				else if ($childNode->childNodes->length == 1 && $childNode->firstChild->nodeType == XML_TEXT_NODE)
				{
					if ($childNode->hasAttributes()) $lang = $childNode->attributes->item(0)->value; //expect xml:lang
					$value = $childNode->firstChild->nodeValue;
				}
				else
				{
					//for rdf:Seq
					$value = array();
					foreach ($childNode->childNodes as $seqNode)
					{
						if ($seqNode->childNodes)
							foreach ($seqNode->childNodes as $seqMember)
							{
                                
							    //if :
							    //XML_TEXT_NODE or empty textContent OR empty attributes for the NODE
							    //than  SKIP
							    if ($seqMember->nodeType == XML_TEXT_NODE || 
							        (empty($seqMember->textContent) && $seqMember->attributes->length === 0)){
							        continue;
							    }
							    $insert_value = !empty($seqMember->textContent) ? $seqMember->textContent : $seqMember->attributes->item(0)->nodeValue;
							    array_push($value, $insert_value);
							}
					}
				}
				
				//virtual concepts patch
				$value = virtualConceptsPatch($pred, $value, $rdfNode);
				
				if ($lang){
					$instanceStruct[$pred][$lang] = $value;
				}
				elseif($pred == "iv:vocalized-form"){
				    //specical case for hebrew lang (until we support full the language)
				    $lang = "he";
				    $instanceStruct[$pred][$lang] = $value; 
				}
				else
				{
					if (array_key_exists($pred, $instanceStruct) && $instanceStruct[$pred] != $value)
					{
						//expect value as array only for extern values (caption etc.)
						if (is_array($value))
						{
							$arrayDiff = array_diff_assoc($instanceStruct[$pred], $value);
							$values = implode("", $value);
							if (!empty($arrayDiff) && !empty($values))
								write_to_log("WARNING", $qname . " - trying to set a DIFFERENT array value to predicate : " . $pred . print_r($value, true) . print_r($instanceStruct[$pred], true));
								
							continue;
						}
						if (!is_array($instanceStruct[$pred])) $instanceStruct[$pred] = array($instanceStruct[$pred]);
						//write_to_log("WARNING", $qname . " - multiple values for the same predicate : " . $pred . " - " . $value . " and " . print_r($instanceStruct[$pred], true));
						$instanceStruct[$pred][] = $value;
					}
					else
						$instanceStruct[$pred] = $value;
				}
					
				if (!array_key_exists($name, $pred2inst))
					$pred2inst[$name] = array();

				if (!array_key_exists($pred, $pred2inst[$name]))
					$pred2inst[$name][$pred] = array();

				$valArray = is_array($value)? $value : array($value);
				foreach ($valArray as $val)
				{
					if (!array_key_exists($val, $pred2inst[$name][$pred]))
						$pred2inst[$name][$pred][$val] = array();

					$pred2inst[$name][$pred][$val][] = &$instanceStruct;
				}
			}
			$instanceStruct["rdf:about"] = $qname;

		}
		else if ($rdfNode->nodeName == "rdfs:Class" && $name == "dig")
		{
			$class = unqualifyName($rdfNode->attributes->getNamedItem("about")->value);
			foreach ($rdfNode->childNodes as $childNode)
			{
				if ($childNode->nodeName != "rdfs:subClassOf") continue;
					
				$parent = unqualifyName($childNode->attributes->item(0)->nodeValue);
				addParentClass($parent, $class, $class2subClasses, $subClass2classes);
			}
		}
	}
	if ($name == "dig")
	{
	    dumpDbg("class2subClasses", $class2subClasses);
	    dumpDbg("subClass2classes", $subClass2classes);
	}
}

//patch for virtual concepts
//these are not real ontology concepts, but Document-identifiers with _Indicators set to a virtual concept name
//this was used once as a patch to demonstrate how intuScan identifies legal regexes
function virtualConceptsPatch($pred, $value, $rdfNode)
{
	global $virtualConcepts;
	if (!empty($virtualConcepts) && $pred == "rdf:type" && strpos($value, "Document-identifiers")> 0)
	{
		write_to_log("TRACE", "found doc ident: " . print_r($rdfNode, true));
		$indicators = $rdfNode->getElementsByTagName("indicators");
		if ($indicators && $indicators->length)
		{
			$indicatorsValue = trim((string)$indicators->item(0)->textContent);
			if (in_array($indicatorsValue, $virtualConcepts))
				$value = qualifyName($indicatorsValue, "ont");
				else
					write_to_log("TRACE", "indicators value doesn't match: $indicatorsValue");
		}
		else write_to_log("TRACE", "indicators not found");
	}
	return $value;
}

function addParentClass($parent, $child, &$class2subClasses, &$subClass2classes)
{
	//dumpDbg("addParentClass", $parent . $child);
	if (array_key_exists($child, $subClass2classes) && in_array($parent, $subClass2classes[$child])){
		return;
	}

	$class2subClasses[$parent][] = $child;

	$subClass2classes[$child][] = $parent;

	if (array_key_exists($child, $class2subClasses)){
		foreach ($class2subClasses[$child] as $grandchild)
		{
			addParentClass($parent, $grandchild, $class2subClasses, $subClass2classes);
		}
	}
	if (array_key_exists($parent, $subClass2classes)){
		foreach ($subClass2classes[$parent] as $grandParent)
		{
			addParentClass($grandParent, $child, $class2subClasses, $subClass2classes);
		}
	}
}

function slotValueToCaption($value, &$qname2node)
{
    
    //check if $value is dynamic-inst-rt- - if so fetch the text from the rdf element
    if(is_string($value) && strpos($value, 'dynamic-inst-') !== false && !empty($qname2node['dig'][$value]['iv:text'])){
        $value = $qname2node['dig'][$value]['iv:caption'] ?? $qname2node['dig'][$value]['iv:text'];
        return is_array($value) &&  array_key_exists("en", $value) ? $value["en"] : $value;
    }
	if (!is_array($value))
		$value = array($value);

	$captions = array();
	if (array_key_exists("en",$value)){
		$captions[] = $value["en"];
	}
	else{
		foreach ($value as $val)
		{
    		if (is_array($val) && array_key_exists("en", $val)){
    			$captions[] = $val["en"];
    		}
    		elseif(array_key_exists($val, $qname2node["dig"]) && !empty($qname2node["dig"][$val]) && 
    		    array_key_exists("iv:caption", $qname2node["dig"][$val])){
		        $captions[] = $qname2node["dig"][$val]["iv:caption"]["en"] ?? $qname2node["dig"][$val]["iv:caption"];
    		}
    		else
    		{
    			$valParts = explode("#", $val);
    			$captions[] = end($valParts);
    		}
		}
	}
	return join(", ", $captions);
}

function populateNameAlternatives(&$personKey2idName, &$pred2inst, &$qname2node, $isEmail=false)
{
	$personKey2node = array();
	$name2additionalInfo = array();
	$isEmail_string = $isEmail ? " - Emails" : "";
	
	if ($isEmail)
	{
		//check also email NAs
	    if(isset($pred2inst["dig"]["rdf:type"][qualifyName("Email", "ont")])){
		  $emailNodes = $pred2inst["dig"]["rdf:type"][qualifyName("Email", "ont")];
	    }
	    else{
	        $emailNodes = false;
	    }
		if ($emailNodes){
		    foreach ($emailNodes as $emailNode){
			     $personKey2node[unqualifyName($emailNode["rdf:about"])] = $emailNode;
		    }
		}
	}
	else
	{
	    $personNodes = false;
	    if(isset($pred2inst["dig"]["iv:entity-type"]["http://www.intuview.com/ontology#Person-object"])){
		      $personNodes = $pred2inst["dig"]["iv:entity-type"]["http://www.intuview.com/ontology#Person-object"];
	    }
	    if ($personNodes){
			foreach ($personNodes as $personNode)
			{
				$personKey2node[unqualifyName($personNode["rdf:about"])] = $personNode;
			}
	    }
	}
	dumpDbg("personKey2node$isEmail_string", $personKey2node);

	foreach ($personKey2idName as $key => $idName)
	{

		$nameAlts = $personKey2node[$key]["iv:name-alternatives"] ?? null;
		if ($nameAlts == NULL)
		{
		    write_to_log("WARNING", "Person doesn't have name alternatives: " . json_encode($idName,true));
			continue;
		}
		if (!is_array($nameAlts))
			$nameAlts = array($nameAlts);

		foreach ($nameAlts as $naQname)
		{
			$na = $qname2node["dig"][$naQname];
			$naStrings = array();
			$naSlots = getNAslots();
			foreach ($naSlots as $naSlot)
			{
				$slot = "iv:" . $naSlot;
				$field = slot2field($naSlot);
				if (!array_key_exists($slot, $na)) continue;

				$slotVal = $na[$slot];
				//TODO: create a separate NA for each txt value in array
				if (is_array($slotVal))
					$slotVal = $slotVal[0];

				$fieldStr = "";
				if (array_key_exists($slotVal, $qname2node["dig"]))
				{
					$slotValInst = $qname2node["dig"][$slotVal];
					if (array_key_exists("iv:name-components", $slotValInst))
						$components = $slotValInst["iv:name-components"];
					else $components = array($slotValInst);
					
					   if (is_array($components) || is_object($components)){	    
    				    foreach ($components as $component)
    					{
    						if (!is_array($component) && array_key_exists($component, $qname2node["dig"])){
    							$component = $qname2node["dig"][$component];
    						}
    
    						if (is_array($component))
    						{
    							if (array_key_exists("iv:instance", $component))
    							{
    								$instName = unqualifyName($component["iv:instance"]);
    								if (array_key_exists($component["iv:instance"], $qname2node["dig"]))
    									$component = $qname2node["dig"][$component["iv:instance"]];
    							}
    							else if (array_key_exists("iv:text", $component))
    							{
    								$instName = $component["iv:text"];
    								if (is_array($instName))
    								{
    									if (array_key_exists("en", $instName))
    										$instName = $instName["en"];
    									else if (array_key_exists("ar", $instName))
    										$instName = $instName["ar"];// . "_AR";
    								}
    
    								//temp hack to remove slashed before quotes (should be actually removed in intuscan
    								$instName = str_replace("\\", "", $instName);
    							}
    							else $instName = unqualifyName($component["rdf:about"]);
    
    							foreach (array("initials", "base-name", "short-form-of", "undetermined-form") as $baseSlot)
    							{
    								if (!array_key_exists("iv:".$baseSlot, $component)) continue;
    
    								$values = $component["iv:".$baseSlot];
    								if (!is_array($values)) $values = array($values);
    
    								$nc = $instName;
    
    								foreach ($values as $value)
    								{
    									$value = unqualifyName($value);
    									$name2additionalInfo[$nc][$baseSlot][] = $value;
    										
    									//patch for replacing FN with its undetermined-form
    									if ($naSlot == "family-name" && $baseSlot == "undetermined-form" && $nc == $instName)
    										$instName = $value;
    								}
    							}
    
    							$fieldStr .= $instName;
    						}
    						else {
    						    $fieldStr .= unqualifyName($component);
    						}
    					}
    				}
				}
				else{
				    $fieldStr = unqualifyName($slotVal);
				}

				$naStrings[$field] = $fieldStr;
			}
			$personKey2idName[$key]["naStrings"][] = $naStrings;
		}
	}
	//

    dumpDbg("name2additionalInfo$isEmail_string", $name2additionalInfo);
	
	return $name2additionalInfo;
}

function insertNaAdditionalInfoToDB(&$name2additionalInfo, $mysqli)
{
	//insert name additional info to DB
	foreach ($name2additionalInfo as $instName => $baseSlots)
		foreach ($baseSlots as $baseSlot => $values)
		foreach ($values as $value)
		{
			$instNameEscaped = $mysqli->real_escape_string($instName);
			$baseSlotEscaped = $mysqli->real_escape_string($baseSlot);
			$valueEscaped = $mysqli->real_escape_string($value);
			$sql = "INSERT INTO nc_additional_info(name_component, info_type, value) " .
			 			"SELECT * FROM (SELECT '$instNameEscaped', '$baseSlotEscaped', '$valueEscaped') AS tmp " .
			 			"WHERE NOT EXISTS " .
			 			"(SELECT * FROM nc_additional_info WHERE name_component='$instNameEscaped' AND info_type='$baseSlotEscaped' AND value='$valueEscaped') " .
			 			"LIMIT 1;";
			if($instNameEscaped == $valueEscaped || $instNameEscaped == $baseSlotEscaped || $valueEscaped == $baseSlotEscaped){
			    //TODO: check why the columns (usually $instNameEscaped & $valueEscaped) comes as same value
			    write_to_log("ERROR", "Failed to add to nc_additional_info - one or more columns are identical");
			    continue;    
			}
			$res = sqlQuery($mysqli,$sql);
			if (!$res)
				write_to_log("WARNING", "Failed to add to nc_additional_info ($instName,$baseSlot,$value):" . $mysqli->error);
		}
}

function getEntitiesManage($searchEntity,$entityType,$entityTable, $mysqli){
	$sql = "SELECT * FROM entitiesmanage WHERE entity=\"$entityType\" AND NAME=\"$searchEntity\"";
	$res = sqlQuery($mysqli, $sql);
	if (!empty($res)){
		$row = $res->fetch_object();
		if (!empty($row)){
			$arr = explode(";", $row->aggregatedName);
			$man_arr = explode(";",$row->aggregatedManual);
			$merge_arr = array_merge($arr,$man_arr);
			$sql = "SELECT docId FROM $entityTable WHERE";
			$first = true;
			foreach ($merge_arr as $v){
				if (strlen($v) > 0){
					if ($first){
						$first = false;
						$sql .= " name = \"$v\"";
					}else
						$sql .= " OR name = \"$v\"";
				}
			}
			$res = sqlQuery($mysqli, $sql);
			if (!empty($res)){
				while($row = $res->fetch_object()){
					$arr_res[$row->docId] = $row->docId;
				}
			}
		}
	}
	return $arr_res;
}

function getAggregatedNames($searchEntity, $mysqli, $entityType){
	$result = NULL;
	if (stripos($entityType, "person") !== FALSE)
		$entityType = "person";
	if ($entityType == "organization")
		$entityType = "org";
	
	$sql = "SELECT * FROM entitiesmanage WHERE entity=\"$entityType\" AND NAME=\"$searchEntity\"";
	write_to_log("TRACE", "getAggregatedNames sql: $sql");
	$res = sqlQuery($mysqli, $sql);
	if (!$res || !$res->num_rows)
		return [];
	
	$row = $res->fetch_object();
	$intuscan_arr = explode(";", $row->aggregatedName);
	$manual_arr = explode(";", $row->aggregatedManual);
	$merge_arr = array_merge($intuscan_arr,$manual_arr);
	return array_filter($merge_arr);
}

function getAggregatedEntity($searchEntity, $mysqli, $entityMatcher, $wl_name, $entityType, $entityTable){
	return getAggregatedEntityConditioned($searchEntity, $mysqli, $entityMatcher, $wl_name, $entityType, $entityTable, "");
}
function getAggregatedEntityConditioned($searchEntity, $mysqli, $entityMatcher, $wl_name, $entityType, $entityTable, $condition){
    $wl_res = searchEntities($mysqli, array(array("text" => $searchEntity,"condition" => $condition, "type" => "person,email")));
	return $wl_res;
}

function  getPersonVariationsDB($mysqli, $personName){
    $personName = $mysqli->real_escape_string($personName);
    $sql = "SELECT * FROM (
            SELECT NAME, COUNT(NAME) AS c FROM person_object WHERE NAME LIKE ('%$personName') OR
                   NAME LIKE ('$personName%') OR NAME = '$personName'  
            GROUP BY NAME ) AS t ORDER BY c DESC";
    $res = sqlQuery($mysqli, $sql);
    $results = $res->fetch_all(MYSQLI_ASSOC);
    $per_var = array();
    foreach($results as $element){
        $per_var[$element['name']] = $element['c'];
    }
    return $per_var;
}

function getPersonVariations($mysqli, $personName){
    if (!getUserPermission(CAN_SHOW_ALL_USER))
        $wl_name = "DOCEX Entities DB-" . $userdetails[Username];
    else
        $wl_name = "DOCEX Entities DB";


        $wl_res = searchEntities($mysqli, array(array("text" => $personName, "type" => "person", "skipAggregation" => true)));
    $personId = array();
    if (sizeof($wl_res) > 0)
        foreach ($wl_res[0]['Rules'] as $rule) {
            foreach ($rule as $val) {
                if (!array_key_exists("instance_name", $val))
                    continue;

                if (!array_key_exists($val["instance_name"], $personId))
                    $personId[$val["instance_name"]] = 1;
                else
                    $personId[$val["instance_name"]] ++;
            }
        }
    write_to_log("PERMORMENCE", "getPersonVariations - $personName - " . print_r($personId, true));
    return $personId;
}
function insertNAsToDB($mysqli, $dcid, &$personKey2idName, $personTable, $watchListName, $userName)
{
	foreach ($personKey2idName as $key => $idName)
	{
	    if(isset($idName["id"])){
		  $personId = $idName["id"];
	    }
		if (empty($personId)){
			continue;
		}
		
		if(array_key_exists("name", $idName)){
    		$personName = $mysqli->real_escape_string($idName["name"]);
    		if (isset($idName["naStrings"]) && $idName["naStrings"]){
    			foreach ($idName["naStrings"] as $naStrings)
    			{
    				//insert NA to DB
    				$sql = "INSERT INTO name_alternatives(watchListName, userName, personId, docId, personTable, personName";
    				$sql .= empty($naStrings) ? "" : ",".join(",", array_keys($naStrings));
    				$sql .= ") VALUES(\"$watchListName\", \"$userName\", $personId, $dcid, \"$personTable\", \"$personName\" ";
    				$naValues = array();
    				foreach (array_values($naStrings) as $val){
    					$naValues[] = "\"" . $mysqli->real_escape_string($val) . "\"";
    				}
    					
    				$sql .= empty($naValues) ? "" : ",".join(", ", $naValues);
    
    				$sql .= ")";
    				write_to_log("TRACE", $sql);
    				$res = sqlQuery($mysqli, $sql);
    				if (!$res)
    					write_to_log("WARNING", "Failed to add NA to DB:" . $mysqli->error);
    			}
    		}
		}
	}

}

function analyzeAndPopulateNAs($mysqli, $searchtext, &$personKey2idName, &$name2additionalInfo)
{
	global $useIntuscanServer;
	if ($useIntuscanServer)
	{
		$analyzeNameXml = new DOMDocument();
		$analyzeNameXml->loadXml('<?xml version="1.0" encoding="UTF-8"?>
				<IVEnvelope>
				<Request id="0">
				<AnalyzeName>
				<Name />
				</AnalyzeName>
				</Request>
				</IVEnvelope>');
		$analyzeNameXml->getElementsByTagName("Name")->item(0)->appendChild($analyzeNameXml->createTextNode($searchtext));
		$out = $analyzeNameXml->saveXML();

		$socket = socket_create(AF_INET, SOCK_STREAM, SOL_TCP);

		if ($socket === false)
		{
			$errorcode = socket_last_error();
			$errormsg = socket_strerror($errorcode);
			write_to_log("ERROR", "error creating socket: " . $errormsg);
			return false;
		}

		global $entityMatcherPort;
		
		$ivEntityMatcherService = getSystemSettingsProp($mysqli, "ivEntityMatcherService");
		
		$result = socket_connect($socket, $ivEntityMatcherService, $entityMatcherPort);

		if ($result === false)
		{
			$errorcode = socket_last_error();
			$errormsg = socket_strerror($errorcode);
			write_to_log("ERROR", "error connecting to socket ($ivEntityMatcherService:$entityMatcherPort): " . $errormsg);
			return false;
		}
		$result = socket_write($socket, $out, strlen($out));
		if ($result === false)
		{
			$errorcode = socket_last_error();
			$errormsg = socket_strerror($errorcode);
			write_to_log("ERROR", "error writing to socket: " . $errormsg);
			return false;
		}

		$rval = socket_set_option($socket,SOL_SOCKET, SO_RCVTIMEO, array("sec"=>5, "usec"=>0));
		$output="";
		$numRetries = 0;
		while (true){
			//$rval = socket_set_option($socket, SOL_SOCKET, SO_SNDTIMEO, array('sec' => 5, 'usec' => 0));
			$read = socket_read($socket, 1024);
			if ($read === FALSE)
			{
				if ($numRetries++ < 3)
				{
					sleep(1);
					continue;
				}
				else
				{
					$errorcode = socket_last_error();
					$errormsg = socket_strerror($errorcode);
					write_to_log("ERROR", "analyzeAndPopulateNAs of '$searchtext': error reading from socket: $errormsg");
					return false;
				}
			}
			$output = $output . $read;
			$ending = strpos($output, "</IVEnvelope>");
								if ($ending !== false) //|| strlen($output) == 0)
									break;
		}
		dumpDbg("searchEntity_output", $output);

		//check if load successed
		$IVEnvelope = simplexml_load_string($output);
		$rdfStr = (string) $IVEnvelope->Reply->AnalyzeName->Analysis;
	}
	//file_put_contents("C:/naresult.xml", $rdfStr);

	$qname2node = array();
	$pred2inst = array();
	$class2subClasses = array();
	$subClass2classes = array();
	rdf2structs("dig", $rdfStr, $qname2node, $pred2inst,
	$class2subClasses, $subClass2classes);
	dumpDbg("qname2node", $qname2node);

	$personKey2idName = array();
	$inputId = 0;
	if(array_key_exists("iv:entity-type", $pred2inst["dig"])){
	$personNodes = $pred2inst["dig"]["iv:entity-type"]["http://www.intuview.com/ontology#Person-object"];
    	if ($personNodes)
    		foreach ($personNodes as $personNode)
    		{
    			$personKey2idName[unqualifyName($personNode["rdf:about"])] =
    			array("id" => $inputId++, "name" => $personNode["iv:caption"]["en"]);
    		}
    		$name2additionalInfo = populateNameAlternatives($personKey2idName, $pred2inst, $qname2node);
    		dumpDbg("name2additionalInfo", print_r($name2additionalInfo, true));
    		dumpDbg("personKey2idName", print_r($personKey2idName, true));
	}

}

function searchRelation($mysqli, $entityText, $entityTable, $rel_type)
{
	$text = join("' , '", array_filter(explode(",", $mysqli->real_escape_string($entityText)), 'strlen'));
	$result = array();
	if (empty($text))
		return $result;

	$condition = $rel_type? "WHERE rel_type = '" . qualifyName($rel_type, "ont") . "'" : "";
	$sql = "SELECT relationship, object_table AS entityTable, object AS entity, relation_type, docId FROM inter_entity_relationship WHERE subject IN ('$text') $condition";
	$res = sqlQuery($mysqli, $sql);
	write_to_log("TRACE", "searchRelation objects sql: $sql");
	$objectRelationships = array();
	while ($res && $row = $res->fetch_object())
	{
		$result["objects"][$row->entityTable][$row->entity][unqualifyName($row->relation_type)][] = $row->docId;
		$objectRelationships[$row->relationship] = true;
	}

	$sql = "SELECT relationship, subject_table AS entityTable, subject AS entity, relation_type, docId FROM inter_entity_relationship WHERE object IN ('$text') $condition";
	$res = sqlQuery($mysqli, $sql);
	write_to_log("TRACE", "searchRelation subjects sql: $sql");
	while ($res && $row = $res->fetch_object())
	{
		//insert subject relationship only if the same relationship doesn't exist as object
		if (!array_key_exists($row->relationship, $objectRelationships))
			$result["subjects"][$row->entityTable][$row->entity][unqualifyName($row->relation_type)][] = $row->docId;
	}
	return $result;
}

function searchEntities($mysqli, $searches)
{
    $initialTableIndex = getInitialTableIndex($mysqli);
	$megaSqls = buildSearchEntitiesSql($mysqli, $searches, $initialTableIndex);
	$results = retrieveSearchResults($mysqli, $megaSqls, $initialTableIndex);
	return $results;
}
function buildSearchEntitiesSql($mysqli, $searches, $initialTableIndex = 0)
{
	$combinedSqls = array();
	$tableIndex = $initialTableIndex+1;
	foreach ($searches as $search)
	{
		//handle external sqls
		if (array_key_exists("Query", $search))
		{
			//$sqlStatement = "SELECT NULL AS instanceId, NULL AS instanceName, 'dummy' AS instanceTable, NULL AS docKey, " . $search["docId"] . " AS docId FROM (" . $search["Query"] . ") AS t$tableIndex";
			$sqlStatement = $search["Query"];
			if ($search["docId"] != "docId")
			{
				$sqlStatement = "SELECT " . $search["docId"] . " AS docId FROM (" . $search["Query"] . ") AS t$tableIndex";
				$tableIndex++;
			}
			$combinedSqls["statements"][$tableIndex] = $sqlStatement;
			$combinedSqls["unionExclude"][$tableIndex] = true;
			$tableIndex++;
			continue;
		}
		$statements = generateSqlStatements($mysqli, $search["text"], $search["type"], $search["condition"] ?? null, $tableIndex, $search["skipAggregation"] ?? null);
		addStatementsToCombined($statements, $combinedSqls, $tableIndex);
	}
	$megaSqls = intersectSqlStatements($combinedSqls, $tableIndex);
	return $megaSqls;
}

function getInitialTableIndex($mysqli)
{
	global $searchPolicy;
	$tableIndex = 0;
	if ($searchPolicy & TEMP_VIEWS)
	{
		for ($tableIndex = 0; $tableIndex < 1000; $tableIndex+=200)
		{
			sqlQuery($mysqli, "CREATE VIEW t$tableIndex AS SELECT 1");
			if (!$mysqli->errno)
			{
				return $tableIndex;
			}
		}
		write_to_log("ERROR", "no free view for search");
	}
	return 0;
}


function addStatementsToCombined($statements, &$combinedSqls, &$tableIndex)
{
	if (array_key_exists("dependencies", $statements))
		addStatementsToCombined($statements["dependencies"], $combinedSqls["dependencies"], $tableIndex);
	foreach ($statements["statements"] as $ti => $sta)
		$combinedSqls["statements"][$ti] = $sta;
}

function splitSearchParts($searchtext)
{
	$searchParts = ["returnAll" => true];
	$searchParts["parts"] = explode("^", $searchtext);
	if (sizeof($searchParts["parts"]) == 1){
		$searchParts["returnAll"] = false;
		$searchParts["parts"] = explode("&", $searchtext);
	}
	return $searchParts;
}

function getSearchCaptions($mysqli, $searchtext, $type, $condition, $skipAggregation)
{
	$table2CaptionsArray = [];
	$searchParts = splitSearchParts($searchtext);
	foreach ($searchParts["parts"] as $searchPart)
	{
		$searchPart = trim($searchPart);
		//if shouldn't return all - create different array element for each term - in order to intersect elements
		//otherwise (return all) - apped results of all terms to the same element - this will create a union
		if ($searchParts["returnAll"] == false || empty($table2CaptionsArray))
			$table2CaptionsArray[] = [];
		
		unset($table2escapedNames);
		$table2escapedNames = &$table2CaptionsArray[count($table2CaptionsArray)-1];
		populateMatchingNames($table2escapedNames, $mysqli, $searchPart,$type,$wl_name,$condition, $skipAggregation);
		$isAggregated = array_key_exists("Aggregated", $table2escapedNames); //check if names are a result of aggregation
		if (stripos($type, "person") !== FALSE && !$isAggregated)
		{
			foreach ($table2escapedNames as $agg => $t2names)
				foreach ($t2names as $table => $escapedNames)
					if ($table == "person_object")
			{
				if (empty($escapedNames))
					continue;
				
				$personEmailsSql = buildGetPersonEmailsSql($mysqli, "distinct email.name AS instanceName, 'email' AS instanceTable", $escapedNames, "", $condition);
				$res = sqlQuery($mysqli, $personEmailsSql . " LIMIT 1000");
				while ($res && ($row = $res->fetch_object()))
				{
					$table2escapedNames["Non-Aggregated"][$row->instanceTable][] = $mysqli->real_escape_string($row->instanceName);
				}
			}
		}
	}
	return $table2CaptionsArray;
}

function generateSqlStatements($mysqli, $searchtext, $type, $externalCondition, &$tableIndex, $skipAggregation)
{
	$searchParts = splitSearchParts($searchtext);
	$combinedSqls = array();
	$wl_name = null; //this is not defined..
	foreach ($searchParts["parts"] as $searchPart)
	{
		$searchPart = trim($searchPart);
		$searchStatements = createSearchStatements($mysqli, $searchPart,$type,$wl_name,$externalCondition, $skipAggregation);
		$isAggregated = array_key_exists("Aggregated", reset($searchStatements)); //check if statements are a result of aggregation
		$statements = flattenSearchStatements($searchStatements, $tableIndex);
		$statements = unionSqlStatements($statements, $tableIndex);
		if (stripos($type, "person") !== FALSE && !$isAggregated)
		$statements = searchPersonEmails($mysqli, $statements, $tableIndex, $externalCondition);
		addStatementsToCombined($statements, $combinedSqls, $tableIndex);
	}
	if ($searchParts["returnAll"])
		$megaSqls = unionSqlStatements($combinedSqls, $tableIndex);
	else
		$megaSqls = $combinedSqls;//intersection will occur outside: intersectSqlStatements($combinedSqls, $tableIndex);

	return $megaSqls;
}

function buildGetPersonEmailsSql($mysqli, $instanceColumns, $escapedPersons, $tName, $externalCondition)
{
	if (!empty($escapedPersons))
	{
		$relationEquality .= "ier.subject IN ('" . join("', '", $escapedPersons) . "') ";
	}
	else
	{
		$relationEquality = "ier.subject = $tName.instanceName";
		$tName = "$tName, ";
	}
	
	$sqls = [];
	foreach (parseAndReplaceExternalConditions($mysqli, $externalCondition, "email", "email") as $condObj)
	{
	$sqls[] = "SELECT $instanceColumns FROM email {$condObj->table}, " .
	"(SELECT DISTINCT ier.object FROM $tName inter_entity_relationship AS ier " .
	"WHERE $relationEquality AND ier.relation_type IN ('electronic-mail', '" . qualifyName("electronic-mail", "ont") . "')) AS ier1 " .
	" WHERE {$condObj->wherePhrase} ier1.object = email.name";
	}
	return join(" UNION ", $sqls);
}
	//
function searchPersonEmails($mysqli, $megaSqls, &$tableIndex, $externalCondition)
{
	$instanceColumns = "distinct email.id AS instanceId, email.name AS instanceName, 'email' AS instanceTable, email.docKey, email.docId , email.occurrences";
	
	$megaSqls = unionSqlStatements($megaSqls, $tableIndex);
	//expect 1 mega table index
	foreach ($megaSqls["statements"] as $ti => $statements){
		$megaTableIndex = $ti;
	}
	
	if (empty($megaTableIndex)){
		write_to_log("ERROR", "searchPersonEmails megaTableIndex is empty");
	}
	
	$isMemSQL = sqlGetConnectionDetails()["isMemSQL"];
	$ttSql = "SELECT DISTINCT instanceName FROM t$megaTableIndex";
	if ($isMemSQL){
		$ttSql = "SELECT DISTINCT instanceName, docId FROM t$megaTableIndex";
	}
	
	$ttIndex = $tableIndex++;
	$tNewMegaTableIndex = $tableIndex++;
	$megaSqls = array("dependencies" =>$megaSqls,
			"statements" => array($ttIndex => $ttSql, $tNewMegaTableIndex => "SELECT * FROM t$megaTableIndex"));
	
	$combinedSqls = ["dependencies" => $megaSqls, "statements" => [$tableIndex++ => "SELECT * FROM t$tNewMegaTableIndex"]];
	

	if (!$isMemSQL){
	    $cond = null; //cond is not defined here anyway
		$combinedSqls["statements"][$tableIndex++] = buildGetPersonEmailsSql($mysqli, $instanceColumns, NULL, "t$ttIndex", $cond);
	}
	else
	{
	foreach (parseExternalConditions($mysqli, $externalCondition, "email") as $condObj)
	{
		//TODO: think if docId equality will be always correct (in MemSql it should be faster)
		$sql = "SELECT $instanceColumns FROM email {$condObj->table}, t$ttIndex, inter_entity_relationship AS ier " .
			"WHERE {$condOb->wherePhrase} ier.subject = t$ttIndex.instanceName AND ier.object = email.name " .
			" AND ier.docId = t$ttIndex.docId AND ier.docId = email.docId AND ier.relation_type IN ('electronic-mail', '" .
			 qualifyName("electronic-mail", "ont") . "')";
		$combinedSqls["statements"][$tableIndex++] = $sql;
	}
	}
	return unionSqlStatements($combinedSqls, $tableIndex);
}

function buildFinalSql($megaSqls, $mysqli, $limit = -1)
{
	//expect one mega statement
	foreach ($megaSqls["statements"] as $ti => $s)
		$tableIndex = $ti;

	$megaSql = "";
	buildSqlStatement($megaSqls, $megaSql);
	global $limitResult;
	if ($limit == -1) $limit = $limitResult;
	if ($limit && !empty($megaSql))
		$megaSql .= " LIMIT $limit";
	
	dumpDbg("megaSqls", $megaSqls);
	global $searchPolicy;
	if ($searchPolicy & SUB_SELECTS)
	{
		$finalSql = $megaSql;
	}
	else
	{
		sqlMultiQuery($mysqli, $megaSql);
		write_to_log("TRACE", "retrieveSearchResults megaSql: $megaSql");

		$finalSql = "SELECT * FROM t$tableIndex";
	}
	write_to_log("TRACE", "searchEntity megaSql: $megaSql\r\nfinalSql: $finalSql");
	return trim($finalSql);
}

function retrieveSearchResults($mysqli, $megaSqls, $initialTableIndex)
{
	$finalSql = buildFinalSql($megaSqls, $mysqli);
	$result = retrieveInstances($mysqli, $finalSql);
	$tIndexes = array($initialTableIndex);
	cleanupSqlStatements($mysqli, $megaSqls, $tIndexes, true);
	return $result;
}

function parseAndReplaceExternalConditions($mysqli, $externalCondition, $instanceTableName, $replaceWith)
{
	$condObjects = parseExternalConditions($mysqli, $externalCondition, $instanceTableName);
	foreach ($condObjects as $condObj)
	{
		$condObj->table = str_replace("%PO%", $replaceWith, $condObj->table);
		$condObj->wherePhrase = str_replace("%PO%", $replaceWith, $condObj->wherePhrase);
	}
	return $condObjects;
}

function parseExternalConditions($mysqli, $externalCondition, $instanceTableName)
{
	if (empty($externalCondition))
		return [(object) ["table" => "", "wherePhrase" => ""]];
	
	//condition as string - deprecated (but still used)
	if (is_string($externalCondition))
	{
		return [(object) ["table" => "", "wherePhrase" => $externalCondition]];
	}
	//determine sentiment table
	$sentTables = $externalCondition["sentimentType"] == "Negative"? ["anti_instance"] :
		($externalCondition["sentimentType"] == "Positive"? ["pro_instance"] : 
				($externalCondition["sentimentType"] == "All"? ["pro_instance", "anti_instance"] : []));
	$conditions = [];
	foreach ($sentTables as $sentTable)
	{
		$escaped = NULL;
		if (array_key_exists("instances", $externalCondition) && !empty($externalCondition["instances"]))
		{
			$escaped = [];
			foreach ($externalCondition["instances"] as $v)
				$escaped[] = $mysqli->real_escape_string($v);
		}
		if (array_key_exists("concepts", $externalCondition) && !empty($externalCondition["concepts"]))
		{
			$sentTable = str_replace("instance", "concept", $sentTable); //rename anti_instance => anti_concept or pro_instance => pro_concept
			$escaped = [];
			foreach ($externalCondition["concepts"] as $v)
				$escaped[] = $mysqli->real_escape_string($v);
		}
		//
		$conditions[] = (object) [
			"table" => " JOIN $sentTable ON %PO%.docId = $sentTable.docId AND %PO%.id = $sentTable.instanceId AND $sentTable.instanceType =  '$instanceTableName' ",
			"wherePhrase" => $escaped? "$sentTable.value IN('" . join("', '", $escaped) . "') AND " : "$sentTable.value IS NOT NULL AND "
		];
                
	}
	return $conditions;
}

function createSearchStatements($mysqli, $searchtext, $type, $wlSelected, $externalCondition, $skipAggregation)
{
	$sqlStatements = array();
	$instanceColumns = "distinct %PO%.id AS instanceId, %PO%.name AS instanceName, '%INSTANCE_TABLE%' AS instanceTable, %PO%.docKey, %PO%.docId, %PO%.occurrences ";
	
	$table2escapedNames = [];
	populateMatchingNames($table2escapedNames, $mysqli, $searchtext, $type, $wlSelected, $externalCondition, $skipAggregation);
	foreach ($table2escapedNames as $agg => $tab2names)
	foreach ($tab2names as $insTab => $escapedNames)
	foreach (parseExternalConditions($mysqli, $externalCondition, $insTab) as $condObj)
	{
		$selectStatement = "SELECT $instanceColumns FROM $insTab AS %PO% {$condObj->table} WHERE {$condObj->wherePhrase} %PO%.name IN ('" . implode("', '", $escapedNames) . "')";
		$sqlStatements[$insTab][$agg][] = $selectStatement;
	}
	return $sqlStatements;
}

function populateMatchingNames(&$table2escapedNames, $mysqli, $searchtext, $type, $wlSelected, $externalCondition, $skipAggregation)
{	
	
	if (strpos($type, "person") === 0)
		$instanceType = "Person-object";
	else if ($type == "organization")
		$instanceType = "Organizational-identity";
	else if ($type == "place")
		$instanceType = "Place-object";
	else if ($type == "event")
		$instanceType = "Event";
	else
	{
		write_to_log("ERROR", "createSearchStatements: unexpected type: $type");
		return null;
	}
	$instanceTable= ontologyName2TableName($instanceType);
	$instanceTables = array($instanceTable);
	if ($instanceType == "Person-object")
		$instanceTables[] = "email";
	
// 	if (!$skipAggregation)
// 	{
// 		$aggregatedNames = getAggregatedNames($searchtext, $mysqli, $type);
// 		write_to_log("TRACE", "# aggregated names:" . count($aggregatedNames));
// 		if (!empty($aggregatedNames))
// 		{
// 			foreach ($aggregatedNames as $aggName)
// 				$escapedAggregatedNames[] = $mysqli->real_escape_string($aggName);
			
// 			foreach ($instanceTables as $insTab)
// 			{
// 				$table2escapedNames["Aggregated"][$insTab] = $escapedAggregatedNames; //copy aggregated names
// 			}
// 			//initialize with empty array - in case no match was found
// 			if (!array_key_exists("Aggregated", $table2escapedNames))
// 				$table2escapedNames["Aggregated"][$instanceTable] = [];
			
// 			//if found aggregated - don't proceed
// 			return $table2escapedNames;
// 		}
// 	}
	$statements = getMatchingInstancesStatements($mysqli, $searchtext, $instanceType, $instanceTables, "DISTINCT %PO%.name AS instanceName", $externalCondition);
	//order names by doc count. If this is too slow - skip by by setting any value to the var below:
	global $skipOrderSearchNameByDocCount;
	if (!isset($skipOrderSearchNameByDocCount))
	{
		$table2sqls = [];
		foreach ($statements as $insTab => $rule2statements)
			foreach ($rule2statements as $rule => $stats)
			foreach ($stats as $stat)
		{
			$table2sqls[$insTab][] = $stat;
		}
		$statements = [];
		foreach ($table2sqls as $insTab => $sqls)
		{
			$unionSql = "SELECT $insTab.name AS instanceName, '$insTab' AS instanceTable, COUNT($insTab.docId) AS instCount FROM $insTab, (";
			$unionSql .= join(" UNION ", $sqls);
			$unionSql .= ") AS instNamesTbl WHERE $insTab.name = instNamesTbl.instanceName GROUP BY $insTab.name ORDER BY instCount DESC, instanceName LIMIT 10000";
			$statements[$insTab]["union"][] = $unionSql;
		}
	}
	$tableIndex = 0;
	$statements = unionSqlStatements(flattenSearchStatements($statements, $tableIndex), $tableIndex);
	$finalSql = buildFinalSql($statements, $mysqli, NULL);
	if (empty($finalSql))
		$res = NULL;
	else
	$res = sqlQuery($mysqli, $finalSql);
	while ($res && ($row = $res->fetch_object()))
	{
		$table2escapedNames["Non-Aggregated"][$row->instanceTable][] = $mysqli->real_escape_string($row->instanceName);
	}
	//initialize with empty array - in case no match was found
	if (!array_key_exists("Non-Aggregated", $table2escapedNames))
		$table2escapedNames["Non-Aggregated"][$instanceTable] = [];
	
	return $table2escapedNames;
}

function getMatchingInstancesStatements($mysqli, $searchtext, $instanceType, $instanceTables, $instanceColumns, $externalCondition)
{
	global $useIntuscanServer;
	if ($useIntuscanServer)
	{
		//first, look in KB
		$kbInstance = getKbInstanceSlots($searchtext,$instanceType, false);
		if ($kbInstance)
		{
			if ($kbInstance["instance"]){
				foreach ($instanceTables as $insTab)
				foreach (parseExternalConditions($mysqli, $externalCondition, $insTab) as $condObj)
				{
					$selectStatement = "SELECT $instanceColumns FROM $insTab AS %PO% {$condObj->table} WHERE {$condObj->wherePhrase} %PO%.ontUrl = '" . $mysqli->real_escape_string($kbInstance["instance"]) . "'";
					$sqlStatements[$insTab]["Known"][] = $selectStatement;
				}
			}
			$kbInstanceCaption = $kbInstance["caption"];
		}
	}
	$escapedCaption = empty($kbInstanceCaption)? null : $mysqli->real_escape_string(strtolower($kbInstanceCaption));
	$escapedText = $mysqli->real_escape_string(strtolower($searchtext));
	if ($instanceType == "Place-object")
	{
		if ($escapedCaption && $escapedCaption != $escapedText)
			$condPart = " IN ('$escapedCaption', '$escapedText')";
		else
			$condPart = " = '$escapedText'";

		foreach (array("place_object", "city", "province") as $placeTable)
		foreach (parseExternalConditions($mysqli, $externalCondition, $placeTable) as $condObj)
		{
			$selectStatement = "SELECT $instanceColumns FROM $placeTable AS %PO% {$condObj->table} WHERE  {$condObj->wherePhrase} ";
			$selectStatement .= "(%PO%.name $condPart OR %PO%.orgText $condPart OR %PO%.country $condPart 
								  OR %PO%.province $condPart OR %PO%.city $condPart) ";
			foreach ($instanceTables as $insTab)
				$sqlStatements[$insTab]["Known"][] = $selectStatement;
		}
	}
	if ($instanceType == "Person-object")
	{
		analyzeAndPopulateNAs($mysqli, $searchtext, $personKey2idName, $name2additionalInfo);

		$conditions = array(
				//compare GN - including additional-info (initials, short-forms)
				"GN" => "%NAS%.given_name IN (%ADDITIONAL_INPUT% %INITIALS%)",
				"Mandatory" => "%NAS%.%SLOT% = '%INPUT%'",
				"Optional"  => "(%NAS%.%SLOT% IS NULL OR %NAS%.%SLOT% =  '%INPUT%')",
				"NULL"  => "%NAS%.%SLOT% IS NULL"

		);

		$additionalConditions = array(
				"GN" => "((%NAI%.value IN (%ADDITIONAL_INPUT%) AND %NAI%.name_component = %NAS%.given_name) OR (%NAI%.name_component IN (%ADDITIONAL_INPUT%) AND %NAI%.value = %NAS%.given_name))"
		);
		//for same GN - check for base-name etc, but not by initials
		$conditions["GN_Only"] = $conditions["GN"];
		$additionalConditions["GN_Only"] = $additionalConditions["GN"] . " AND %NAI%.info_type != 'initials'";


		$rules = array(
				"Possible Same Person" => array("given_name" => "GN", "family_name" => "Mandatory",
						"middle_name" => "Optional", "father_patrimonyal_name" => "Optional", "maiden_name" => "Optional", "son" => "Optional"),
				"Same Person (Kunya)" => array("son" => "Mandatory", "given_name" => "Optional", "family_name" => "Optional",
						"middle_name" => "Optional", "father_patrimonyal_name" => "Optional", "maiden_name" => "Optional"),
				"Same Given name" => array("given_name" => "GN_Only", "family_name" => "NULL"),
				"Same Person (Unknown name role)" => array("unknown_name_role" => "Mandatory"),
				"Same Family/Clan relationship" => array("family_name" => "Mandatory", "given_name" => "NULL", "son" => "NULL")
		);

		$naInstanceColumns = str_replace("'person_object' AS instanceTable", "%NAS%.personTable AS instanceTable", $instanceColumns);
		$selectPrefix = "SELECT $naInstanceColumns from (SELECT %NAS%.* FROM name_alternatives AS %NAS% %TABLES%) AS %NAS%, %PERSON_TABLE% AS %PO% %COND_TABLE% WHERE %COND_PHRASE% ";
		$moreConditions = array("%NAS%.docId = %PO%.docId", "%NAS%.personId = %PO%.id", "%NAS%.personTable = '%PERSON_TABLE%'",
				"%NAS%.personName = %PO%.name"); //the latter condition is almost redundant, but sometimes name don't match (probably a memsql glitch)
		$additionalTables = array("Default" => "", "Additional" => " INNER JOIN nc_additional_info AS %NAI%");

		global $userdetails;
		$selectSuffix = "";// AND watchListName = \"DOCEX Entities DB\"";
		if (!getUserPermission(CAN_SHOW_ALL_USER))
			$selectSuffix .= " AND %PO%.userName = \"" . $mysqli->real_escape_string($userdetails[Username]) . "\"";

		if ($personKey2idName)
		foreach ($personKey2idName as $key => $personValue)
		{
			$nasStrings = $personValue["naStrings"];

			foreach ($rules as $ruleId => $rule)
			{
				foreach ($nasStrings as $naStrings)
				{
					foreach (array("Default", "Additional") as $queryMode)
					{
						$skipQuery = $queryMode == "Additional";	//skip executing sql in Additional mode, unless one of the conditions is different than the default
						$conditionValues = array();
						$additionalConditionValues = array();
						foreach (getNAslots() as $naSlot)
						{
							$naSlot = str_replace("-", "_", $naSlot);
							if (!array_key_exists($naSlot, $rule))
								continue;

							$conditionKey = $rule[$naSlot];

							if (!array_key_exists($naSlot, $naStrings))
							{
								//if mandatory slot is missing in input person - skip the entire rule
								if ($conditionKey != "Optional" && $conditionKey != "NULL")
								{
									write_to_log("WARNING", $naSlot . " is empty - skipping query of rule " . $ruleId);
									$skipQuery = true;
									break;
								}
								else
									continue;	//if optional - skip slot condition
							}
							else if ($conditionKey == "NULL")
							{
								//prevent search for "Bob Dillen" return "Bob" as a result
								write_to_log("WARNING", $naSlot . " is empty - skipping query of rule " . $ruleId);
								$skipQuery = true;
								break;
							}

							$slotInputValue = $mysqli->real_escape_string($naStrings[$naSlot]);
							$additionalValue = "'$slotInputValue'";
							$initials = "";
							//concat additional info
							if (array_key_exists($naStrings[$naSlot], $name2additionalInfo))
								foreach ($name2additionalInfo[$naStrings[$naSlot]] as $slotName => $slotVals)
								foreach ($slotVals as $slotVal)
								if ($slotName == "initials")
								{
									//don't allow initials matching for GN only (prevent "Robert" from matching "R. Jones")
									if ($conditionKey != "GN_Only")
										$initials .= ", '" . $mysqli->real_escape_string($slotVal) . "'";
								}
								else
									$additionalValue .= ", '" . $mysqli->real_escape_string($slotVal) . "'";
								//
								//write_to_log("TRACE", $queryMode . " - qm, " . $ruleId . " -ri, ck - " . $conditionKey);
								if ($queryMode == "Additional" && array_key_exists($conditionKey, $additionalConditions))
								{
									$condition = $additionalConditions[$conditionKey];
									$skipQuery = false; //if additional condition is different than default condition - there is point in executing sql
								}
								else
									$condition = $conditions[$conditionKey];

								$condition = str_replace("%INPUT%", $slotInputValue, $condition);
								$condition = str_replace("%SLOT%", $naSlot, $condition);
								$condition = str_replace("%ADDITIONAL_INPUT%", $additionalValue, $condition);
								$condition = str_replace("%INITIALS%", $initials, $condition);

								$additionalConditionValues[] = $condition;
						}
						if ($skipQuery)
							continue;
						
						foreach ($moreConditions as $cond)
							$conditionValues[] = $cond;

						$selectStatement = $selectPrefix;
						$tablesReplacement = $additionalTables[$queryMode];
						if (!empty($additionalConditionValues))
							$tablesReplacement .= " WHERE " . implode(" AND ", $additionalConditionValues);
						
						$selectStatement = str_replace("%TABLES%", $tablesReplacement, $selectStatement);
						$selectStatement .= implode(" AND ", $conditionValues);
						$selectStatement .= $selectSuffix;

						foreach ($instanceTables as $insTab)
						foreach (parseExternalConditions($mysqli, $externalCondition, $insTab) as $condObj)
							$sqlStatements[$insTab][$ruleId][] =
								str_replace(
									["%PERSON_TABLE%", "%COND_TABLE%", "%COND_PHRASE%"],
									[$insTab, $condObj->table, $condObj->wherePhrase],
									$selectStatement);
					}
				}
			}
		}
	}
	//search by caption
	$escapedTexts = "";
	$tmpMysqli = sqlCreateConnection("getMatchingInstancesStatements");
	foreach (generateSimilarWords($searchtext, 20) as $similarSearchText)
	foreach ($instanceTables as $insTab)
	{
		$escapedText = $mysqli->real_escape_string($similarSearchText);
		//$selectStatement = "SELECT $instanceColumns FROM $insTab AS %PO% WHERE $externalCondition (%PO%.name RLIKE '[[:<:]]{$escapedText}[[:>:]]' OR %PO%.orgText = '$escapedText')  $selectSuffix";
		if (!empty($escapedTexts))
			$escapedTexts .= ", ";
		
		$escapedTexts .= "'$escapedText'"??"";
	}
	if (!empty($escapedTexts))
	foreach ($instanceTables as $insTab)
	foreach (parseExternalConditions($mysqli, $externalCondition, $insTab) as $condObj)
	{		
		if ($insTab == "organizational_identity")
			$org = " OR %PO%.clean_name IN ($escapedTexts)";
		else 
			$org = "";		
		$selectStatement = "SELECT $instanceColumns FROM $insTab AS %PO% {$condObj->table} WHERE {$condObj->wherePhrase} (%PO%.name IN ($escapedTexts) $org OR %PO%.orgText IN ($escapedTexts))  $selectSuffix";
		$res = sqlQuery($tmpMysqli, str_replace("%PO%", "po", $selectStatement . " LIMIT 1"));
		write_to_log("TRACE", "found results for sql $selectStatement");
		if ($res && $res->num_rows)
			$sqlStatements[$insTab]["Same $instanceType (Caption)"][] = $selectStatement;
	}
	$tmpMysqli->close();
	return $sqlStatements;
}

function buildSearchConceptSql($mysqli, $tableName, $searchText, $columnNames, $condition = NULL)
{
	if (empty($searchText) || strtolower($searchText) == "all"){
		$sql = "SELECT DISTINCT $columnNames FROM $tableName";
		$condPrefix = " WHERE ";
	}
	else{
	    $searchStr = $searchText;
	    if(strpos($searchText, '^') !== false){
    	    $searchArr = explode('^', $searchText);
    	    $searchStr = strtolower(implode("','", $searchArr));
	   }
       $nameExists = TablesInformation::isColumnExists($mysqli, $tableName, "name");
       
       $where_cond = $nameExists ? "name IN ('$searchStr') or orgText IN ('$searchStr')" :
                        "value IN ('$searchStr')";
	   
	   $sql = "SELECT DISTINCT $columnNames FROM $tableName
	   WHERE $where_cond";
	   $condPrefix = " AND ";
	}
	if (!empty($condition))
		$sql .= $condPrefix . $condition;
	
	return $sql;	
}

function generateSimilarWords($word, $maxResults)
{
	$similarWords = array($word => array(0));
	$transformations = array(
			"ak" => "ek",
			"aw" => "ea",
			"b" =>  "p",
			"ch" => array("ci", "sh"),
			"de" => "di",
			"di" => "de",
			"dt" => "t",
			"e" => array("a","ei","i","ie"),
			"eu" => "oi",
			"ev" => "ov",
			"f" => array("ph", "v"),
			"g" => array("h", "k", "sh"), // (end of word only)
			"h" => array("g", "j"),
			//h drop after vowel
			"ah" => "a",
			"eh" => "e",
			"ih" => "i",
			"oh" => "o",
			"uh" => "u",
			"hl" => "ll",//?
			"i" => array("e", "ei", "ie", "y"),
			"ic" => "ec",
			"ich" => array("ik", "itch", "icz"),
			"ie" => array("i", "ei"),
			"j" => array("y", "gi", "ge"),
			"k" => array("c", "q"),
			"m" => "n",
			"mac" => "mc",
			"mc" => "mac",
			"n" => "m",
			"o" => "u",
			//O?  O
			"ov" => "of",
			"q" => array("k", "ku"),
			"qu" => array("kw", "qw"),
			"s" => array("c", "z"),
			"sch" => "sh",
			"se" => "ce",
			"sh" => array("ch", "sch"),
			"sht" => "st",
			"si" => "ci",
			"sky" => array("ski", "skiy"),
			"t" => "dt",
			"u" => array("o", "w"),
			//ue U (umlaut)
			"v" => array("f", "w"),
			"w" => array("u", "v"),
			"y" => array("i", "ue"),
			"z" => array("ts", "tz")
	);
	$newWords = $similarWords;
	while (!empty($newWords))
	{
		$newWords = array();
		foreach ($similarWords as $sWord => $indArray)
		{
			$similarWords[$sWord] = array();
			foreach ($indArray as $ind)
			{
				foreach ($transformations as $transKey => $transVal)
				{
				    if($ind >= strlen($sWord)){
				        continue;
				    }
					$index = strpos($sWord, $transKey, $ind);
					if ($index === FALSE)
						continue;

					$transVals = is_array($transVal)? $transVal : array($transVal);
					foreach ($transVals as $val)
					{
						$incrementedIndex = $index+strlen($transKey);
						$newWord = substr($sWord, 0, $index) . $val . substr($sWord, $incrementedIndex);
						write_to_log("TRACE", "new word: $newWord");
						$newWords[$newWord][] = $incrementedIndex;
					}
				}
			}
			if (count($newWords) > $maxResults)
			{
				write_to_log("TRACE", "new words exceeded max results");
				break;
			}
		}
		foreach ($newWords as $nw => $indArray)
		{
			write_to_log("TRACE", "new word: $nw");
			foreach ($indArray as $ind)
			{
				if (array_key_exists($nw, $similarWords) && in_array($ind, $similarWords[$nw]))
					continue;

				$similarWords[$nw][] = $ind;
			}
		}
		if (count($similarWords) >= $maxResults)
			break;
	}
	return array_keys($similarWords);
}

function flattenSearchStatements($sqlStatements, &$tableIndex)
{
	$statements = array("statements" => array());
	foreach ($sqlStatements as $instanceTable => $rule2sql)
		foreach ($rule2sql as $ruleId => $selectStatements)
		foreach ($selectStatements as $selectStatement)
		{
			$ti = $tableIndex++;
			$statements["statements"][$ti] =
			str_replace(array("%PO%", "%NAI%", "%NAS%", "%INSTANCE_TABLE%"),
					array("po$ti", "nai$ti", "nas$ti", $instanceTable), $selectStatement);
		}
		return $statements;
}


function retrieveInstances($mysqli, $selectStatement)
{

	$results = array();
	$foundIds = array("person_object" => array(), "email" => array());
	$foundDocIds = array();
	$arr = array('Rules' => array());

	write_to_log("TRACE", "retrieveInstances sql: $selectStatement");
	$sqlResult = sqlQuery($mysqli, $selectStatement);
	while ($sqlResult && $row = $sqlResult->fetch_object()) {
		$instanceId = $row->instanceId;
		$instanceTable = $row->instanceTable;
		$docKey = $row->docKey;
		$docId = $row->docId;
		$foundDocId = array_key_exists($docId, $foundDocIds);
		$foundDocIds[$docId] = true;
		if (empty($docKey))
		{
			if ($foundDocId) continue;
		}
		else
		if (array_key_exists($docKey, $foundIds[$instanceTable])) continue;
		else $foundIds[$instanceTable][$docKey] = true;

		$arr["Rules"]["dummy rule id"][] = array("instance_id" => $instanceId, "instance_name" => $row->instanceName,
				"instance_table" => $instanceTable, "doc_key" => $docKey, "doc_id" => $docId);
	}

	/*
	 $sqlResult = sqlQuery($mysqli, $selectStatement);
	write_to_log("TRACE", $selectStatement);
	while ($sqlResult && $row = $sqlResult->fetch_object()) {
	}
	*/
	if (!empty($arr['Rules']))
		$results[] = $arr;

	dumpDbg("RuleResults", $results);
	return $results;
}

function buildSqlStatement($sqlStatements, &$sqlString)
{
	global $searchPolicy;
	if ($searchPolicy & SUB_SELECTS)
	{
		buildSqlStatementSubSelects($sqlStatements, $sqlString, -1);
		return;
	}
	if (array_key_exists("dependencies", $sqlStatements))
		buildSqlStatement($sqlStatements["dependencies"], $sqlString);

	foreach ($sqlStatements["statements"] as $tableIndex => $statement)
	{
		if ($searchPolicy & TEMP_VIEWS)
		{
			$sqlString .= "CREATE VIEW t$tableIndex AS $statement;\r\n";
			continue;
		}
		$sqlString .= "DROP TABLE IF EXISTS t$tableIndex;\r\n";
		$sqlString .= "CREATE TEMPORARY TABLE t$tableIndex AS $statement;\r\n";
	}
}

function buildSqlStatementSubSelects($sqlStatements, &$sqlString, $ti)
{
	foreach ($sqlStatements["statements"] as $tableIndex => $statement)
	{
		if ($ti >=0 && $ti != $tableIndex)
			continue;
		/*
		 $fromIndex = strrpos($statement, " FROM ");
		$select = substr($statement, 0, $fromIndex);
		$fromWhere = explode(" WHERE ", substr($statement, $fromIndex), 2);
		$from = $fromWhere[0];
		if (count($fromWhere) == 2)
			$where = " WHERE " . $fromWhere[1];
		else $where = "";
		*/
			
		$stmt = $statement;
			
		$dStartIndex = 0;
		$foundTable = false;
		if (array_key_exists("dependencies", $sqlStatements))
		foreach ($sqlStatements["dependencies"]["statements"] as $dTi => $dStatement)
		{
			$tempTableName = " t$dTi";
			$dtmpIndex = 0;
			while (($dIndex = strpos($stmt, $tempTableName, $dtmpIndex)) !== FALSE)
			{
				$dEndIndex = $dIndex + strlen($tempTableName);
				if ($dEndIndex < strlen($stmt) && (is_numeric($stmt[$dEndIndex]) || $stmt[$dEndIndex] == "."))
				{
					write_to_log("WARNING", "Found part of a larger table name: '$tempTableName' in $stmt");
					$dtmpIndex = $dEndIndex;
					continue;
				}
				if ($dIndex > 4 && substr($stmt, $dIndex-4,5) == ") AS ")
				{
					$dtmpIndex = $dEndIndex;
					continue;
				}
				
				unset($subSelect);
				$subSelect = "";
				buildSqlStatementSubSelects($sqlStatements["dependencies"], $subSelect, $dTi);
				$newStmt = substr($stmt, 0, $dIndex) . " (" . $subSelect . ") AS$tempTableName";
				$dtmpIndex = strlen($newStmt);
				write_to_log("TRACE", "old statement: $stmt");
				$stmt = $newStmt . substr($stmt, $dEndIndex);
				write_to_log("TRACE", "new statement: $stmt");
				$foundTable = true;
			}
			if (!$foundTable)
			{
				write_to_log("WARNING", "could not find '$tempTableName' in $stmt");
				continue;
			}
		}
		$sqlString = $stmt;//$select . $from . $where;
		return;
	}
}

function cleanupAllTempViews($mysqli)
{
	for ($i = 0; $i < 1000; $i++)
		$sqlQuery .= "DROP VIEW IF EXISTS t$i; ";

	sqlMultiQuery($mysqli, $sqlQuery);
}

function cleanupSqlStatements($mysqli, $sqlStatements, &$tIndexes, $isRoot)
{
	global $searchPolicy;
	if ($searchPolicy & TEMP_VIEWS)
	{
		foreach ($sqlStatements["statements"] as $tableIndex => $statement)
			$tIndexes[] = $tableIndex;

		if (array_key_exists("dependencies", $sqlStatements))
			cleanupSqlStatements($mysqli, $sqlStatements["dependencies"], $tIndexes, false);

		if ($isRoot)
		{
			foreach ($tIndexes as $tableIndex)
				$sqlQuery .= "DROP VIEW t$tableIndex; ";
				
			write_to_log("TRACE", "cleanup sql: $sqlQuery");
			sqlMultiQuery($mysqli, $sqlQuery);
		}
	}
}

function unionSqlStatements($sqlStatements, &$tableIndex)
{
	global $searchPolicy;
	if (count($sqlStatements["statements"]) < 2)
		return $sqlStatements;
	
	$union = array("dependencies" => $sqlStatements);
	if (!($searchPolicy & TEMP_VIEWS))
		$unionString = "SELECT * FROM (";
	$first = true;
	foreach ($sqlStatements["statements"] as $ti => $statement)
	{
		//write_to_log("TRACE", "unionExclude: $ti first: $first, count statements: " . count($sqlStatements["statements"]) . ", count excludes: " . count($sqlStatements["unionExclude"]) . ", key exists: " . array_key_exists("unionExclude", $sqlStatements) . ", ti exists: " . array_key_exists($ti, $sqlStatements["unionExclude"]) . ", print_r:" . print_r($sqlStatements["unionExclude"], true));
		//if current statement should be excluded from union
		if (array_key_exists("unionExclude", $sqlStatements) && array_key_exists($ti, $sqlStatements["unionExclude"]))
		{
			//if all statements are marked as "unionExclude" - don't exlude the first statement (need at least 1 staement)
			if (!$first || count($sqlStatements["statements"]) > count($sqlStatements["unionExclude"]))
				continue;
		}
		//append statement to union
		if (!$first) $unionString .= "UNION\r\n";
		$first = false;
		$unionString .= "SELECT * FROM t$ti\r\n";
	}
	if (!($searchPolicy & TEMP_VIEWS))
		$unionString .= ") AS temp";
	$union["statements"][$tableIndex] = $unionString;
	$tableIndex++;
	return $union;
}

function intersectSqlStatements($sqlStatements, &$tableIndex)
{
	global $searchPolicy;
	if (count($sqlStatements["statements"]) < 2)
		return $sqlStatements;
	
	$unionStatements = unionSqlStatements($sqlStatements, $tableIndex);
	$unionTableIndex = first_key($unionStatements["statements"]);
	$unionTableSql = $unionStatements["statements"][$unionTableIndex];
	write_to_log("TRACE", "unionTableIndex: $unionTableIndex" . print_r($unionStatements["statements"], true));
	$intersectedStatement = "SELECT t$unionTableIndex.* FROM ($unionTableSql) AS t$unionTableIndex INNER JOIN t";
	$tablesIndexes = array_keys($sqlStatements["statements"]);
	$tmpIndex = 0;
	foreach ($tablesIndexes as $ti)
	{
		$intersectedStatement .= "$ti ON t$unionTableIndex.docId = t$ti.docId ";
		if (++$tmpIndex < count($tablesIndexes))
			$intersectedStatement .= " INNER JOIN t";
	}
	$unionStatements["statements"][$unionTableIndex] = $intersectedStatement;
	return $unionStatements;
	/*
	$useTempViews = $searchPolicy & TEMP_VIEWS;
	$intersectedStatements = array("dependencies" => $sqlStatements);
	//$createTempTables = "";
	foreach ($sqlStatements["statements"] as $ti1 => $statement1)
	{
		//$createTempTables .= "DROP TABLE IF EXISTS t$ti1;\r\n";
		//$createTempTables .= "CREATE TEMPORARY TABLE t$ti1 AS ($statement1 AS temp);\r\n";
		if ($useTempViews)
			$intersectedStatement = "";
		else
			$intersectedStatement = "SELECT * FROM(";
		$intersectedStatement .= "SELECT t$ti1.* FROM t$ti1 ";
		$tableIndex++;
		$onClause = array();
		foreach ($sqlStatements["statements"] as $ti2 => $statement2)
		{
			if ($ti2 == $ti1) continue;
				
			$intersectedStatement .= " INNER JOIN t$ti2 ON t$ti1.docId = t$ti2.docId";
			$tableIndex++;
		}
		if (!$useTempViews)
			$intersectedStatement .= ") AS temp";
		$intersectedStatements["statements"][$tableIndex] = $intersectedStatement;
		$tableIndex++;
	}
	return unionSqlStatements($intersectedStatements, $tableIndex);
	*/
}

function genericDependentStatement($sqlStatements, &$tableIndex, $newStatement)
{
	$dependentTi = $tableIndex-1; //assume sqlStatements contain 1 statement with prev tableIndex
	return array("dependencies" => $sqlStatements,
			"statements" => array($tableIndex++ => str_replace("%TABLE%", "t$dependentTi", $newStatement)));
	
}

function sqlDocsAuthors($sqlStatements, &$tableIndex)
{
	return genericDependentStatement($sqlStatements, $tableIndex, "SELECT DISTINCT COALESCE(from_field.value,files.author) AS instanceName FROM files,from_field, %TABLE% WHERE %TABLE%.docId = files.id AND from_field.docId=files.id");
}

function sqlPersonAsAuthor($sqlStatements, &$tableIndex)
{
	return genericDependentStatement($sqlStatements, $tableIndex, "SELECT DISTINCT files.id AS docId FROM files,from_field, %TABLE% WHERE (from_field.value = %TABLE%.instanceName OR files.author = %TABLE%.instanceName) AND from_field.docId=files.id");
}

function sqlDocsEntities($sqlStatements, $tableName, $condition, &$tableIndex)
{
	return genericDependentStatement($sqlStatements, $tableIndex, str_replace("%PO%", "po".$tableIndex, "SELECT %PO%.id AS instanceId, %PO%.name AS instanceName, %PO%.docId, %PO%.docKey FROM $tableName AS %PO%, %TABLE% WHERE $condition %PO%.docId = %TABLE%.docId"));
}

function sqlPersonAsRecipient($sqlStatements, &$tableIndex)
{
	return genericDependentStatement($sqlStatements, $tableIndex, "SELECT DISTINCT files.id AS docId FROM files, %TABLE% WHERE files.to_field = %TABLE%.instanceName OR files.cc_field = %TABLE%.instanceName OR files.bcc_field = %TABLE%.instanceName");
}

function sqlDocsRecipients($sqlStatements, &$tableIndex)
{
	return genericDependentStatement($sqlStatements, $tableIndex, "SELECT DISTINCT files.to_field AS instanceName FROM files, %TABLE% WHERE files.id = %TABLE%.docId");
}

function uploadedFileRelativePath($filePath)
{
	global $uploadedFilesPath;
	$fp = realpath($filePath);
	$ufp = realpath($uploadedFilesPath);
	if (stripos($fp, $ufp) === 0)
		return trim(substr($fp, strlen($ufp)), "/\\");

	write_to_log("ERROR", "filePath '$filePath' ($fp), doesn't start with uploaded files path '$uploadedFilesPath' ($ufp)");
	return $filePath;
}

function uploadedFileAbsolutPath($filePath)
{
	//a bit of a hack for analyzed single files/texts/urls - which are not in uploade files path, but under "uploads"
	//those types (as opposed to analyze folder) pass to analayzeFile(...) only file name, so the origFolder turns out to be "."
	if ($filePath == ".")
	{
		global $UPLOADS_PATH;
		return $UPLOADS_PATH;
	}
	
	global $uploadedFilesPath, $UPLOADS_PATH;
	if (stripos(realpath($filePath), realpath($uploadedFilesPath)) === 0){
		return $filePath;
	}
	
	$path = $uploadedFilesPath . DIRECTORY_SEPARATOR . $filePath;
	if(!file_exists($path)){
	    $path = $UPLOADS_PATH . DIRECTORY_SEPARATOR . $filePath;
    }
	return $path;
}

$emailRegex = "email\\d+";
function isEmailfolder($folder)
{
	global $emailRegex;
	return preg_match("/^$emailRegex/", $folder);
}

function isFileNameEnronEmail($filename)
{
	global $gEmailNameRegex;
	if (!isset($gEmailNameRegex))
		return false;
	
	//if set in config, $gEmailNameRegex matches filenames which are emails - patch for enron files
	return preg_match($gEmailNameRegex, $filename);
}

function isFileNameEmailMessage($filename)
{
	//patch for enron files
	if (isFileNameEnronEmail($filename))
		return true;

	global $emailRegex;
	return preg_match("/^$emailRegex\\.(txt|htm)/", $filename);
}

function isFileNameEmailBody($filename)
{
	global $emailRegex;
	return preg_match("/^{$emailRegex}_body\\.htm$/", $filename);
}

function fixHeaders($emailMessage, $emailArr){
    $email_fields = array_values($emailArr);
    foreach($email_fields as $field){
        $emailMessage = str_ireplace($field, $field.':', $emailMessage);
    }
    return  $emailMessage;
}



function parseEmail($emailMessage, $ext, $emailArr  )
{
    //special care for ocr pdf emails - adding colon after headers
    if($ext == 'pdf'){
        $emailMessage = fixHeaders($emailMessage, $emailArr);
    }
    
	$result = array();
	//email body starts after the first empty line
	$headersAndBody = preg_split("/^\s*\r?\n/m", $emailMessage, 2);
	if ($headersAndBody && count($headersAndBody) > 0)
	{
		if (count($headersAndBody) ==2)
			$result["body"] = $headersAndBody[1];

		$matches = array();
		//match header like: From: ...
		preg_match_all("/^\w+\:[^\n]+(\n\s+[^\n]+){0,3}/m", $headersAndBody[0], $matches);
		if ($matches && count($matches))
			foreach ($matches[0] as $match)
			{
				$keyVal = explode(":", $match, 2);
				if ($keyVal && count($keyVal) == 2)
					$result["headers"][strtolower($keyVal[0])] = trim($keyVal[1], " \r\n\t");
			}
	}
	return $result;
}

function parseDate($dateString)
{
    //patch for celbrite whatsapp
	if(strpos(strtolower($dateString), '(utc')!==false){
	    $dateString = explode('(utc', strtolower($dateString))[0];
	}
    $dateString = trim($dateString);
	
	if (empty($dateString)) return null;
	$dateString = str_replace('/', '-', $dateString);
	$time = strtotime($dateString);
	write_to_log("TRACE", "parseDate: $dateString - $time");

	return date("Y-m-d H:i:s", $time);
}

function copyFileToTemp($fileName, $docId)
{	
	$directory = getDocMetadataFolder($docId);
	$tmpFile = "$directory/{$docId}$suffix." . pathinfo($fileName, PATHINFO_EXTENSION);
	
	if (file_exists($tmpFile))
		return $tmpFile;
	
	global $COPY_COMMAND;
	$fileName = str_replace("/", DIRECTORY_SEPARATOR , $fileName);
	$command = $COPY_COMMAND . " \"$fileName\" \"$tmpFile\"";
	write_to_log("TRACE", "copyFileToTemp command: $command");
	$return_var;
	$fileLines = array();
	exec($command, $fileLines, $return_var);
	if ($return_var)
		write_to_log("ERROR", "copyFileToTemp command failed: $command");
	
	return $tmpFile;
}



//to be used in sendDoc... to append converted words from hacking language (h4ck, b3l13ve) to normal language (hack, believe)
function appendHackingWords($content, $fileName="test.txt")
{
	//append hacking words only for txt docs, and only if configured
	global $shouldAppendHackingWords;
	if (!$shouldAppendHackingWords ||
		strtolower(pathinfo($fileName, PATHINFO_EXTENSION)) != "txt")
		return $content;

	$results = array();
	$matches = array();
	$offset = 0;
	while (preg_match('/(([[:alpha:]]+[0134]+)|([0134]+[[:alpha:]]+))[[:alpha:]0134]{0,20}/' , $content , $matches, PREG_OFFSET_CAPTURE, $offset))
	{
		$results[] = $matches[0][0];
		$nextOffset = $matches[0][1] + strlen($matches[0][0]);
		if ($nextOffset <= $offset)
		{
			write_to_log("ERROR", "next offset should be greater than offset :(");
			break;
		}
		$offset = $nextOffset;
	}
	if (count($results) <= 3)
		return $content;


	$content .= "\r\n\r\nConverted text:\r\n----------\r\n\r\n";
	foreach ($results as $result)
		$content .= " " . str_replace(array("0", "1", "3", "4"), array("o", "i", "e", "a"), $result);
	return $content;

}

function fileAppendLine($file, $line)
{
	file_put_contents($file, $line . "\r\n", FILE_APPEND);
}

function canContribute($mysqli, $docId)
{
	if (empty($docId))
		return false;
	
	//check if user is a contributor
	if (getUserPermission(CAN_CONTRIBUTE))
		return true;
	
	//check if the user is the owner of the document
	$userdetails = getUserDetails();
	$userCondition = " AND username='" . $mysqli->real_escape_string($userdetails['Username']) . "'";
	$res = sqlQuery($mysqli, "SELECT emailid FROM files where id=" . $dcid . $userCondition);
	$canContribute = !empty($res);
	return $canContribute;	
}
function redactedIdToFileName($redactedId)
{
	if (!$redactedId)
		return "";
	
	return sprintf("XYZ %05d", $redactedId);
}

function redactedIdToFolderName($redactedId)
{
	if (!$redactedId)
		return "";
	
	return sprintf("%03d", ($redactedId / 5000)+1);
}

function docToRedactedId($mysqli, $dcid, $pageNum, $pageCount)
{
    write_to_log("PERMORMENCE", "Start isRedacted - $dcid" . date('H:i:s'.substr((string)microtime(), 1, 8)));
	$redactedRange = docToRedactedRange($mysqli, $dcid, $pageNum, $pageCount);
	write_to_log("PERMORMENCE", "End isRedacted - $dcid" . date('H:i:s'.substr((string)microtime(), 1, 8)));
	if (!$redactedRange){
		return NULL;
	}

	return $redactedRange["redactedPageId"];
}

function docToRedactedRange($mysqli, $dcid, $pageNum, $pageCount)
{
	$dcid = intval($dcid);	//prevent SQL injections
	$res = sqlQuery($mysqli, "SELECT redactedId, redactedEndId FROM files WHERE id = $dcid AND redactedId IS NOT NULL");
	if ($res && $res->num_rows)
	{
		$row = $res->fetch_object();
		$redactedId = $row->redactedId;
		$redactedEndId = $row->redactedEndId;
	}
	else if (!$pageCount)
	{
		//if page count is "false" - don't automatically create redacted id
		return NULL;
	}
	else
	{
		$res = sqlQuery($mysqli, "SELECT redactedEndId FROM files ORDER BY redactedEndId DESC LIMIT 1");
		if (!$res)
		{
			sqlClose($mysqli, 'docToRedacatedRange');
			write_to_log("ERROR", "Failed to fetch last redacted id");
			return NULL;
		}
	
		$redactedId = $res && $res->num_rows? $res->fetch_object()->redactedEndId : 0;
		$redactedEndId = $redactedId + $pageCount;
		$redactedId++;
		sqlQuery($mysqli, "UPDATE files SET redactedId = $redactedId, redactedEndId = $redactedEndId WHERE id = $dcid");
	}
	$redactedPageId = $redactedId;
	if ($pageNum)
		$redactedPageId += $pageNum - 1;
	
	sqlClose($mysqli, 'docToRedacatedRange');
	return array("redactedPageId" => $redactedPageId, "redactedId" => $redactedId, "redactedEndId" => $redactedEndId);
}
function getDocRedactedImageName($mysqli, $dcid, $pageNum)
{
    $redactedId = docToRedactedId($mysqli, $dcid, $pageNum, false);
	if (!$redactedId)
		return NULL;
	
		return getRedactedImageName($mysqli, $redactedId);
}
function getRedactedImageName($redactedId)
{
	global $metadata_path;
	return "$metadata_path/{$redactedId}_redacted.png";
}

function getRedactedExportedTiffName($redactedId)
{
	global $redactedDocsPath;
	$subfolderName = redactedIdToFolderName($redactedId);
	$fileName = redactedIdToFileName($redactedId);
	$tiffDir = "$redactedDocsPath/IMAGES/$subfolderName";
	if (!is_dir($tiffDir))
		mkdir_full($tiffDir, 0777, true);
	
	return "$tiffDir/$fileName.tif";
}

function export($mysqli, $request)
{
	if ($request["filter"] == "reviewedNotRedacted")
	{
		$res = sqlQuery($mysqli, "SELECT files.id, files.origFolder FROM files, filetags WHERE filetags.name = 'Reviewed' AND files.id = filetags.docId AND files.redactedId IS NULL");
		global $uploadedFilesPath;
		global $CURRENT_SOURCE_FOLDER;
		while ($res && $row = $res->fetch_object())
		{
			$filePath = docIdToFilePath($row->id, $mysqli);
			if (!$filePath)
			{
				write_to_log("ERROR", "file path is null for doc id " . $row->id);
				continue;
			}
			$newPath = str_replace(realpath($uploadedFilesPath), $CURRENT_SOURCE_FOLDER . "/exported_docs", realpath($filePath));
			if (!$newPath || $newPath == $filePath) 
			{
				write_to_log("ERROR", "new path is not ok ($uploadedFilesPath): " . $newPath);
				continue;
			}
			mkdir_full(dirname($newPath), 0777, true);
			copy($filePath, $newPath);
		}
	}
}
////////////////////////////////////////////////
//
// TODO: The email parameters moved fron files 
//
///////////////////////////////////////////////
function createRedactionLoadFiles($mysqli)
{
	write_to_log("ERROR", "PROBLEM - Function createRedactionLoadFiles");
	global $redactedDocsPath;
	/*
	 * SELECT files.*,from_field.value AS from_field,to_field.value AS to_field,cc_field.value AS cc_field,bcc_field.value AS bcc_field,subject_field.value AS subject_field 
							  FROM files,from_field,to_field,cc_field,bcc_field,subject_field 
							  WHERE redactedId IS NOT NULL AND files.id=from_field.docId AND files.id=to_field.docId
	 */
	$res = sqlQuery($mysqli, "SELECT files.* FROM files WHERE redactedId IS NOT NULL");
	mkdir_full("$redactedDocsPath/LOADFILES", 0777, true);
	$diiFileName = "$redactedDocsPath/LOADFILES/VOL1.DII";
	$crossRefFileName = "$redactedDocsPath/LOADFILES/VOL1.TXT";
	file_put_contents($diiFileName, "@FULLTEXT DOC\r\n");
	file_put_contents($crossRefFileName, "");
	while ($res && ($row = $res->fetch_object()))
	{
		fileAppendLine($diiFileName, ";Record " . $row->redactedId);
		$fileName = redactedIdToFileName($row->redactedId);
		$endFileName = redactedIdToFileName($row->redactedEndId);
		fileAppendLine($diiFileName, "@T " . $fileName);
		$media = "eDoc";
		$parentFileName = $parentId = $parentRedactedId = $firstAttachmentRedactedId = $lastAttachmentEndRedactedId = NULL;
		$attachmentsFileNames = array();
		if ($row->emailid)
		{
			$res1 = sqlQuery($mysqli, "SELECT files.id, files.redactedId, files.redactedEndId FROM files WHERE emailid = {$row->emailid} ORDER BY id");
			while ($res1 && $row1 = $res1->fetch_object())
			{
				//first id expected to be the email message, which should be the parent id
				if ($parentId == NULL)
				{
					$parentId = $row1->id;
					$parentRedactedId = $row1->redactedId;
					$parentFileName = redactedIdToFileName($parentRedactedId);
				}
				else
				{
					if ($row1->redactedId)
					{
						if ($firstAttachmentRedactedId == NULL)
							$firstAttachmentRedactedId = $row1->redactedId;
						
						$attachmentsFileNames[] = redactedIdToFileName($row1->redactedId);
					}
					if ($row1->redactedEndId)
						$lastAttachmentEndRedactedId = $row1->redactedEndId;
				}
				if ($parentId == $dcid)
					$media = "eMail";
				else
				{
					$media = "Attachment";
					if ($parentRedactedId)
						fileAppendLine($diiFileName, "@PARENTID " . $parentFileName);
				}
			}
		}
		$dateSent = NULL;
		if ($row->date_field)
		{
			$dateSent = strtotime($row->date_field);
			fileAppendLine($diiFileName, "@DATESENT " . date("d-m-Y", $dateSent));
			fileAppendLine($diiFileName, "@TIMESENT " . date("h:i:s A", $dateSent));
		}
		//TODO: @DATERCVD/@TIMERCVD
		if ($row->from_field)
			fileAppendLine($diiFileName, "@FROM " . $row->from_field);

		if ($row->to_field)
			fileAppendLine($diiFileName, "@TO " . $row->to_field);
		
		if ($row->cc_field)
			fileAppendLine($diiFileName, "@CC " . $row->cc_field);
		
		if ($row->bcc_field)
			fileAppendLine($diiFileName, "@BCC " . $row->bcc_field);
		if ($row->subject_field)
			fileAppendLine($diiFileName, "@SUBJECT " . $row->s_field);
		
		if ($row->file_creation_date)
			fileAppendLine($diiFileName, "@DATECREATED " . date("d-m-Y h:i:s A", strtotime($row->file_creation_date)));

		$modifyDate = NULL;
		if ($row->file_last_modified_date)
		{
			$modifyDate = strtotime($row->file_last_modified_date);
			fileAppendLine($diiFileName, "@DATESAVED " . date("d-m-Y h:i:s A", $modifyDate));
		}

		fileAppendLine($diiFileName, "@C AUTHOR ");
		fileAppendLine($diiFileName, "@C TITLE ");
		fileAppendLine($diiFileName, "@C FOLDERNAME ");
		fileAppendLine($diiFileName, "@MEDIA $media");
		fileAppendLine($diiFileName, "@C ENDDOC# $endFileName");
		$pageCount = $row->redactedEndId - $row->redactedId +1;
		fileAppendLine($diiFileName, "@C PGCOUNT " . $pageCount);
		
		//append to cross reference file
		fileAppendLine($crossRefFileName, "BEGDOC $fileName");
		fileAppendLine($crossRefFileName, "ENDDOC $endFileName");
		fileAppendLine($crossRefFileName, "BEGATT " . redactedIdToFileName($firstAttachmentRedactedId));
		fileAppendLine($crossRefFileName, "ENDATT " . redactedIdToFileName($lastAttachmentEndRedactedId));
		if ($media == "Attachment")
			fileAppendLine($crossRefFileName, "PARENTID " . $parentFileName);
		
		if ($media == "eMail")
			fileAppendLine($crossRefFileName, "ATTACHMENT " . join(";", $attachmentsFileNames));
		
		fileAppendLine($crossRefFileName, "RECORDTYPE " . (($media == "eMail") ? "EMAIL" : (($media == "Attachment") ? "EMAIL ATT" : "EDoc")));
		if ($dateSent)
		{
			fileAppendLine($crossRefFileName, "DATESENT " . date("m/d/Y", $dateSent));
			fileAppendLine($crossRefFileName, "TIMESENT " . date("h:i:s A", $dateSent));
		}
		//TODO: DATERCVD...
		if ($modifyDate)
		{
			fileAppendLine($crossRefFileName, "MODIFYDATE " . date("m/d/Y", $modifyDate));
			fileAppendLine($crossRefFileName, "MODIFYTIME " . date("h:i:s A", $modifyDate));
		}
		fileAppendLine($crossRefFileName, "AUTHOR " . ($media == "eMail"? $row->from_field : $row->author));
		if ($media == "eMail")
		{
			fileAppendLine($crossRefFileName, "TO " . $row->to_field);
			fileAppendLine($crossRefFileName, "CC " . $row->cc_field);
			fileAppendLine($crossRefFileName, "BCC " . $row->bcc_field);
			fileAppendLine($crossRefFileName, "SUBJECT " . $row->subject_field);
		}
		$pathParts = pathinfo($row->filename);
		if ($media == "eDoc")
		{
			fileAppendLine($crossRefFileName, "FILENAME " . $pathParts['filename']);
			fileAppendLine($crossRefFileName, "FILEXTENSION " . $pathParts['extension']);
		}
		fileAppendLine($crossRefFileName, "VOLUME PROD_IMG001");
		fileAppendLine($crossRefFileName, "FILEPATH " . $row->origFolder);
		//TODO: PST name
		fileAppendLine($crossRefFileName, "PAGES $pageCount");
		$subfolderName = redactedIdToFolderName($row->redactedId);
		fileAppendLine($crossRefFileName, "ORIGINALSPATH ORIGINALS/$subfolderName/$fileName." . $pathParts['extension']);
		fileAppendLine($crossRefFileName, "TEXTPATH TEXT/$subfolderName/$fileName.TXT");
	}
}


function getRedactedDocOriginalId($mysqli, $redactedId)
{
	$res = sqlQuery($mysqli, "SELECT id FROM files WHERE redactedId <= $redactedId AND redactedEndId >= $redactedId");
        $result = $res && $res->num_rows? (int) $res->fetch_object()->id : NULL;
	return $result;
}

function lang_mapper($input) {
    global $lang_map;
    if(empty($input)){
        return null;
    }
    return array_key_exists(strtoupper($input),$lang_map) ? $lang_map[strtoupper($input)] : null;
}

function docIdToFilePath($id, $mysqli=NULL, $returnArr=NULL)
{
	global $folderSeparator, $UPLOADS_PATH;
	$origFolder = "";
	$createConnection = $mysqli == NULL;
	if ($createConnection){
		$mysqli = sqlCreateConnection("docIdToFilePath");
	}
	$res = sqlQuery($mysqli, "SELECT origFolder, filename, shortFilename FROM files WHERE id = $id");
	if ($res && $res->num_rows && $row = $res->fetch_object())
	{
		//temp hack for enron corpus
		$origFolder = uploadedFileAbsolutPath($row->origFolder);
		$origFolder = str_replace("\\", DIRECTORY_SEPARATOR, $origFolder);
		if ($returnArr){
			$arr['fileName'] = $row->filename;
			$arr['filePath'] = $origFolder;
			if (!empty($row->shortFilename))
				$arr["shortFilename"] = $row->shortFilename;
			
			if ($createConnection)
				sqlClose($mysqli, 'docIdToFilePath');
	
			return $arr;
		}
// 		$origFolder =$UPLOADS_PATH;
		$fullPath = $origFolder . DIRECTORY_SEPARATOR . $row->filename;
	}
	else
	{
		write_to_log("ERROR", "download origFile failed for $id");
		if ($createConnection)
		    sqlClose($mysqli, 'docIdToFilePath');
		    
		return NULL;
	}
	//handle file names with unicode characters
	if (!file_exists($fullPath) && $row->shortFilename)
	{
		$tmpFile = copyFileToTemp($origFolder . DIRECTORY_SEPARATOR . $row->shortFilename, $id);
		if (file_exists($tmpFile))
			$fullPath = $tmpFile;
		else
		{
			write_to_log("ERROR", "download file not found: $fullPath");
		if ($createConnection)
		    sqlClose($mysqli, 'docIdToFilePath');
		    
			return NULL;
		}
	}
	if ($createConnection)
	    sqlClose($mysqli, 'docIdToFilePath');
	    
	return $fullPath;
}

function getFileImagesPrefix($dcid)
{
	return getDocMetadataFolder($dcid) . "/{$dcid}_images/image";
}

function getDocMetadataFolder($dcid)
{
    global $metadata_path;
    $prefix_path = $metadata_path;
	$directory = "$prefix_path".DIRECTORY_SEPARATOR . ((int)($dcid / 10000)) . DIRECTORY_SEPARATOR . ($dcid % 10000);
	if (!is_dir($directory)){
		mkdir_full($directory, 0777, true);
	}
	return $directory;
}



function my_shell_exec($cmd, $cwd, &$stdout=null, &$stderr=null) {
    $bareCmd = str_replace("start /B ", "", $cmd);
    if ($bareCmd != $cmd) {
        if (!class_exists('COM')) {
            write_to_log("ERROR", "Class 'COM' does not exist. Probably 'extension=php_com_dotnet.dll' is off php.ini");
            $WshShell = NULL;
        } else {
            $WshShell = new COM("WScript.Shell");
        }
        if ($WshShell) {
            $WshShell->CurrentDirectory = $cwd;
            $oExec = $WshShell->Run($bareCmd, 0, false);
            return 0;
        }
    }
    $proc = proc_open($cmd, [
        1 => ['pipe', 'w'],
        2 => ['pipe', 'w'],
            ], $pipes, $cwd);
    if (!$proc) {
        write_to_log("ERROR", "my_shell_exec failed to $cmd");
        return false;
    }
    $stdout = stream_get_contents($pipes[1]);
    fclose($pipes[1]);
    $stderr = stream_get_contents($pipes[2]);
    fclose($pipes[2]);
    return proc_close($proc);
}


function executeTika($partialCommandLine)
{
	global $tikaJarPath;
	return executeJar($tikaJarPath, $partialCommandLine);
}

function executeJar($jarPath, $partialCommandLine)
{
	global $javaPath;
	$command = "java -jar \"$jarPath\" $partialCommandLine";
	return executeCommand($javaPath, $command);
}
function executeCommand($commandDir, $command, $ignoreStdErr=false)
{
	$return_var = 0;
	write_to_log("TRACE", "executeCommand dir ($commandDir): $command");
	$output = array();
	$stdout = "";
	$stderr = "";
	$return_var = my_shell_exec($command, $commandDir, $stdout, $stderr);
	if ($return_var || (!$ignoreStdErr && !empty($stderr)))
	{
		write_to_log("ERROR", "executeCommand failed with $return_var: $command, stderr: $stderr, stdout: $stdout");
	}
	return $return_var;
}

function getImageCount($dcid)
{
    require_once 'pdfTools.php';
    $pdfClass = new PDFTools();
	$fullPath = docIdToFilePath($dcid);
	if ($pdfClass->shouldConvertToPdf($fullPath))
	    $fullPath = $pdfClass->convertToPdf($fullPath, $dcid);
	    
	if (!$fullPath) return 0;

	$extension = pathinfo($fullPath, PATHINFO_EXTENSION);
	if ($extension != "pdf"){
		return 0;
	}

	return $pdfClass->pdfToImage($fullPath, $dcid);
}

function getImageFileName($dcid, $pageNum)
{
	$imageFileName = getFileImagesPrefix($dcid) . $pageNum . ".png";
	if (!file_exists($imageFileName))
		write_to_log("ERROR", "Missing image file: $imageFileName");
		
	return $imageFileName;
}

function updateSHA1($mysqli, $id, $emailid, $text = null, $regex = null)
{
    //if not empty the regex - omit it from the text
    if(!empty($text) && !empty($regex)){
        if (!preg_match($regex, $text)) {
            write_to_log("INFO", "No Match for regex");
            return;
        }
        else{
            write_to_log("INFO", "Ommiting regex test");
        }
        $omit_text = trim(preg_replace($regex, '', $text)); //removing the regex from the text - to temp variable
        $sha1 = sha1($omit_text); //creates sha1 string from the text  
    }
    else{
        $filePath = docIdToFilePath($id, $mysqli);
        if(!file_exists($filePath)){
            write_to_log("ERROR", "File doesnt exists");  
            return false;
        }
        $sha1 = sha1_file($filePath);
    }
	
	if (strlen($sha1) > 1){
       	$duplicatedDocId = 0; 
       	$sql = "SELECT id FROM duplicate_files WHERE hashCode= x'".$sha1."' AND duplicatedDocId IS NULL LIMIT 1";
	    $res = sqlQuery($mysqli, $sql);
	    if ($res && $res->num_rows && $row = $res->fetch_object())
		{
			$duplicatedDocId = $row->id;
		}
			//if file name	 		
		elseif ($emailid > 0 && $emailid != "NULL" && isFileNameEmailBody(basename($filePath)))
		{
			$res = sqlQuery($mysqli, "SELECT files.id FROM emailid, files WHERE emailid.value = $emailid AND emailid.docId = files.id AND files.filename LIKE 'email%.txt'");
			if ($res && $row = $res->fetch_object())
	 				$duplicatedDocId =$row->id; 				
	 		}
	 		if ($duplicatedDocId > 0)
 				$res = sqlQuery($mysqli, " INSERT INTO duplicate_files (id,hashCode,duplicatedDocId) VALUES ($id,x'".$sha1."',$duplicatedDocId)");
 			else 
 				$res = sqlQuery($mysqli, " INSERT INTO duplicate_files (id,hashCode) VALUES ($id,x'".$sha1."')");
        }else
    		write_to_log("ERROR", "problem creating sha1 filepath=$filePath");    
 	
 	if (isset($row))	
 		return $row->id;
 	else 
 		return false;
}

function updateUniqueDocToDB($mysqli, $id){
	$res = sqlQuery($mysqli, "UPDATE unique_files SET count_doc = count_doc + 1 WHERE id=$id");
}

function insertUniqueDocToDB($mysqli, $id, $date, $username){
	if (empty($date))
		$date = date('YmdHis'); 
	$res = sqlQuery($mysqli, "INSERT INTO unique_files VALUES ($id,'$date','$username',1)");
}

function getAuthorArr($connection, $docid = null){
    $arr = [];
    $ret = null;
    $docid_sql = !empty($docid) ? " docid=$docid " : " TRUE "; 
    $sql_author = "SELECT * FROM file_author WHERE $docid_sql";
    $res_author =  sqlQuery($connection,$sql_author);
    if($res_author){
        if(!empty($docid)){
            $row_author = $res_author->fetch_object();
            $ret = $row_author->value;
        }
        else{
            $row_author = $res_author->fetch_all();
            foreach($row_author as $element){
                $arr[$element[1]] = ["name" => $element[2], "info" => $element[3]];
            }
            $ret = $arr;
        }
    }
    
    return $ret;
}

function getRelatedFiles($mysqli, $dcid){
    $sql = "SELECT t_parent_id.docid as id, (SELECT filename FROM files WHERE id = t_parent_id.docid) AS name, 'Comment' AS type FROM t_parent_id, files WHERE files.id = $dcid  AND t_parent_id.value = files.id
	UNION
SELECT files.id, files.filename AS name, 'Post' AS type FROM  t_parent_id, files WHERE t_parent_id.docid = $dcid  AND t_parent_id.value = files.id";
    $res = sqlQuery($mysqli,$sql);
    $relatedDocs = $res? $res->fetch_all(MYSQLI_ASSOC) : NULL;
    return $relatedDocs;
}

function getEmailsFiles($mysqli, $dcid, $relatedDocs){
    $emailFiles = [];
    //$emailFiles = $relatedDocs;
    $currentEmailFile = ["id" => $dcid, "name" => $filePathName["fileName"], "type" => "Message"];
    foreach ($relatedDocs as $ef)
    {
        if ($ef["type"] == "Comment")
            $ef["type"] = "Attachment";
            else if ($ef["type"] == "Post")
            {
                $ef["type"] = "Message";
                $currentEmailFile["type"] = "Attachment"; //if returned post - probably this doc is a comment
            }
            
            $emailFiles[] = ["id" => $ef["id"], "name" => $ef["name"], "type" => $ef["type"] == "Post"? "Message" : "hello"];
    }
    return $emailFiles;
}

function getMetaDataSpecielArrFormDB($connection, $arr_id, $limitResult, $username, $isAdmin, $savesearchId=null,$ignore_limit=null, $numberToFetch=null, $offset=null){
	require_once 'globalArr.php';
	global $dropdownArr;	  		
 	$arr_meta = array();
 	
 	$author_arr = getAuthorArr($connection);
 	
	foreach ($dropdownArr as $meta_k => $meta_v){
		//skip phrases (used for word cloud)
		if ($meta_k == "phrases"){
			continue;
		}
        
		$file_col="$meta_k.value,$meta_k.docId";
		if($meta_k == "filetags"){
		    $file_col="GROUP_CONCAT(DISTINCT $meta_k.name) as value,$meta_k.docId";
		}
		if($meta_k == "emotion"){
		    $file_col="$meta_k.e_concept as value, $meta_k.anti, $meta_k.pro ,$meta_k.docId";
		}
		$sql=buildsqlLimitAddon("docId",$file_col,$arr_id,$meta_k,$savesearchId,$limitResult, $username, $isAdmin, $ignore_limit, null, null, $numberToFetch, $offset);
		    
		$res_meta =  sqlQuery($connection,$sql);
		$arr_meta[$meta_k] = array();
		if ($res_meta)
		while ($row = $res_meta->fetch_object()){
		    //special care for emotion response meta-data
		    if($meta_k == "emotion"){
		        $arr_meta["emotion"][$row->docId] = $row->value;
		        $arr_meta["pro_emotion"][$row->docId] = $row->pro;
		        $arr_meta["anti_emotion"][$row->docId] = $row->anti;	
		        $arr_meta["mood"][$row->docId] =  $row->pro - $row->anti;      
		    }
		    //otherwise
			elseif (array_key_exists($row->docId,$arr_meta[$meta_k])){
			    if(is_array($arr_meta[$meta_k][$row->docId])){
			        continue; //its for file author or any samiliar - no need again or to attach
			    }
				$arr_meta[$meta_k][$row->docId] .= "," . $row->value;
			}
			else{
				$arr_meta[$meta_k][$row->docId] = $row->value;
			}
			
			if(array_key_exists($row->docId, $author_arr)){
			    $value = $author_arr[$row->docId];
			    $arr_meta["file_author"][$row->docId] = ["name" => $value['name'], "info" => $value['info']];
			}

		}
		
	}
	
	
	dumpDbg("arr_meta", $arr_meta);
	return $arr_meta;						
}



function getDuplicateId($connection, $arr_id, $limitResult,$username, $isAdmin,$savesearchId=null, $ignore_limit=null, $numberToFetch=null, $offset=null){
    
    $file_col = "duplicate_files.duplicatedDocId,duplicate_files.id";
    $tables='duplicate_files';
    $sql = buildsqlLimitAddon("id",$file_col,$arr_id,$tables,$savesearchId,$limitResult,$username, $isAdmin, $ignore_limit, false, false, $numberToFetch, $offset);
        
	$res=  sqlQuery($connection,$sql);
	
	while ($row = $res->fetch_object()){		
		$arr[$row->id] = $row->duplicatedDocId;
	}
	return $arr;
}

function isJson($string) : bool {
    json_decode($string);
    return (json_last_error() == JSON_ERROR_NONE);
}

function postBulkInsertionCallback($mysqli, $bulkIds)
{
	global $postBulkInsertionFunctions;
	foreach ($postBulkInsertionFunctions as $f)
		$f($mysqli, $bulkIds);
}

function buildsqlLimitAddon($id_col,$file_col,$arr_id,$tables,$savesearchId,$limitResult,$username, $isAdmin, $ignore_limit, $keepSolrOrder=false, $lastSearchId=false, $fetchMoreSearchResults = null, $offset = null){
    $sql =  "SELECT DISTINCT $file_col";
    $sql .= " FROM $tables";
    
    if (!empty($savesearchId)) {
        $sql .= " INNER JOIN savedsearchid ON $tables.$id_col=savedsearchid.docId";
        
        $sql .= " WHERE savedsearchid.id=$savesearchId";
    }

    //by search id no need for that
//     if (!$isAdmin) {
//         $sql .= " AND username='$username'";
//     }

    if ($keepSolrOrder) { 
        $sql .= " ORDER BY FIELD(files.id, $lastSearchId)" ;
        $ignore_limit = false;
    }
    
    if($tables == "filetags"){
        $sql .= empty($savesearchId) ? " WHERE " : " AND ";
        $sql.= "  NAME != 'upload_folder' AND NAME !='Reviewed' group by filetags.docid ";
    }
    
    if(isset($fetchMoreSearchResults) && isset($offset)){
        $sql .= " LIMIT $fetchMoreSearchResults OFFSET $offset";
    }
    elseif (!$ignore_limit){
        $sql .= " LIMIT $limitResult";
    }
   


    return $sql;
}

function getReviewed($connection, $arr_id, $limitResult,$savesearchId=null,$ignore_limit=null, $numberToFetch=null, $offset=null){
    
	$sql = "SELECT DISTINCT filetags.docId from filetags ";				
	if (empty($arr_id)){
		$sql .= "NULL";
	}
	else{
	    if ($ignore_limit) {
	        $sql .= " INNER JOIN savedsearchid ON filetags.id=savedsearchid.docId WHERE savedsearchid.id=$savesearchId AND filetags.name = 'Reviewed' AND not filetags.docId = 0";
	    }
	    else{
	        $sql.="WHERE filetags.name = 'Reviewed' AND not filetags.docId = 0";
	        if(isset($numberToFetch) && isset($offset)){
	            $sql .= " LIMIT $numberToFetch OFFSET $offset";
	        }
	        else{
	            $sql .= " LIMIT $limitResult";
	        }
	    }
	}

	$res = sqlQuery($connection,$sql);
	$arr_review = array();	
	while ($res && $row = $res->fetch_object()) {
		$arr_review[$row->docId] = $row->docId;			
	}	
	return $arr_review;		
}

function getEmailQuery($field,$searchEmailNames, $mysqli){
	$names = explode("^",$searchEmailNames);
	$query = " AND ( ";
	$isFirst = true;
	foreach ($names as $name){
		$name = $mysqli->real_escape_string($name);
		if($isFirst){
			$query .= "files.$field LIKE('%$name%')";
			$isFirst = false;
			continue;
		}
		$query .= " OR files.$field LIKE('%$name%')";
	}
	$query .= ")";
	return $query;
	
}

function getFullToEmail($searchToEmailNames,$mysqli){
	$names = explode("^",$searchToEmailNames);
	$toFields = array("to_field","cc_field","bcc_field");
	$query = " AND (";
	$isFirstField = true;
	foreach ($toFields as $field){
		if($isFirstField){
			$query .= "(";
			$isFirstField = false;
		}else{
			$query .= " OR (";
		}
		$isFirst = true;
		foreach ($names as $name){
			$name = $mysqli->real_escape_string($name);
			if($isFirst){
				$query .= "files.$field LIKE('%$name%')";
				$isFirst = false;
				continue;
			}
			$query .= " OR files.$field LIKE('%$name%')";
		}
		$query .= ")";
	}
	$query .= ")";
	return $query;
}
/**Replaces dash with undescore and to lowercase. 
 * This function was built because of the need in case sensativity in UNIX*/
function ontologyName2TableName($table){
	return strtolower(str_replace("-","_",$table));
}

function getRunningStatus($connection, $username){
	$sql = "SELECT isRunning AS r FROM user_status WHERE username='$username'";
	$res_run = sqlQuery($connection,$sql);
	if(!empty($res_run)){
		$row = $res_run->fetch_object();
		$row_int = $row->r;
		if ($row_int === null)
			return 0;
		return (int)$row_int;
	}
	return null;
}

/**Get all the names of the user's pies/trends*/
function getUserDashboardDataList($username, $mysqli){
	$dashboardList = array();
	$query = "SELECT * FROM dashboard WHERE x_point is NOT NULL AND y_point is NOT NULL AND username=\"$username\"";
	$results = sqlQuery($mysqli,$query);
	while($row = $results->fetch_object()){
		$name = $row->name;
		$type = $row->type;
		$dashboardList[$name] = array($name,$type);
	}
	return $dashboardList;
}

function get_CondArr($mysqli, $pieName, $username) {
    $res = CacheEng::getFromCache($mysqli,   $username, $pieName);
    $results_exists = isset($res->results) && !empty($res->results);
    $in_process =  $res->in_process ?? 0;

    return array('results_exists' => $results_exists, 'in_process' => $in_process);
}

 /* in case (table === languages) relabel 'Unknown language' => 'None'
  */
function relabelUnknownLanguage($content){
     $decodedContent = json_decode($content);
            $amount = &$decodedContent->amount;
            if(!empty($amount)) {
				array_walk($decodedContent->amount, function(&$item, $key) { 
					if($item[0] == "Unknown language"){
						$item[0] = "None"; //We use "None" language instead on the pie
					}
				} );
			}
			return json_encode($decodedContent);
}


// function getPieInfo($mysqli, $v2, $username, $pieName, $savedSearch = false) {
//     global $CURRENT_SOURCE_FOLDER, $PHP_FOLDER;
//     global $pieResult;
//     $cond_array = get_CondArr($mysqli, $pieName, $username);
//     $results_exists = $cond_array['results_exists'];
//     $in_process = $cond_array['in_process'];
    
//     $pieData = getTableElementData($mysqli, 'dashboard', $pieName, 'username', $username);
    
//     // 1 - Pie results exists :
//     if ($results_exists && $savedSearch == false) {
//         if (!$in_process) {
//             asyncRequst::getInstance()->calcPie($pieName,  $username);
//         }
        
//         $res = CacheEng::getFromCache($mysqli,  $username, $pieName);
        
//         $content = $res->results;
//         if ($pieName == "languages") {
//             $content = relabelUnknownLanguage($content);
//         }
//     } elseif( isset($pieName) ) {
//         $pieDetailsArray = getPieDetails($pieData);
//         //fetch the details of the pie, and fetch the results, and put them into the DB
//         require_once 'StatisticPageEng.php'; //one call-why call to get pie 2 times , cash = true ,$req table ="" doese nothing
//         $pieDetailsArray["slices"] = $pieResult;
//         $pieDetailsArray["pastPeriod"] = $pieDetailsArray["pastPeriod"] ?? $pieData->pastperiod ?? null; //we only use the dashboard's pastPeriod if the pie did not define its own
//         $content = getPie($mysqli, $_REQUEST['v2'] ?? null,  $pieDetailsArray, $savedSearch ?? null);
        
//     }
    
//     //will reset in process after 5 req for pie
//     resetInProcessField($mysqli, $pieName, $username);
    
//     return $content;
// }


function getPieInfo($mysqli, $v2, $username, $pieName, $savedSearch = false) {
    global $CURRENT_SOURCE_FOLDER, $PHP_FOLDER;
    global $pieResult;
    $pieData = getTableElementData($mysqli, 'dashboard', $pieName, 'username', $username);
    $res = CacheEng::getFromCache($mysqli,  $username, $pieName);
    
    //if $savedSearch is defined - means it need to be calculate from scratch and not from cache
    //if Cache is completly empty - analyse it
    if(!empty($savedSearch) || empty($res)){
        $pieDetailsArray = getPieDetails($pieData);
        //fetch the details of the pie, and fetch the results, and put them into the DB
        require_once 'StatisticPageEng.php'; //one call-why call to get pie 2 times , cash = true ,$req table ="" doese nothing
        $pieDetailsArray["slices"] = $pieResult;
        $pieDetailsArray["pastPeriod"] = $pieDetailsArray["pastPeriod"] ?? $pieData->pastperiod ?? null; //we only use the dashboard's pastPeriod if the pie did not define its own
        $content = json_encode(getPie($mysqli, $_REQUEST['v2'] ?? null,  $pieDetailsArray, $savedSearch ?? null));
    
    }
    else{
        
        $content = $res->results;
        if ($pieName == "languages") {
            $content = relabelUnknownLanguage($content);
        }
    }

     
    return $content;
}


function getPieDetails($pieData) {
    $pieDetails = isset($pieData)? $pieData->details : null;
    //Takes a JSON encoded string and converts it into a PHP variable.
    //When TRUE, returned objects will be converted into associative arrays.
    return json_decode($pieDetails, true);
}

function getPieRes($pieData) {
    return $pieData->results ;
}

//will reset in process after 5 req for pie
function resetInProcessField($mysqli, $pieName,$username) {
    $cache_key = CacheEng::getCacheKey($mysqli, $username, $pieName);

    $res = CacheEng::getFromCache($mysqli,  $username, $pieName);
    $in_process= $res->in_process;
    if ($in_process > 5) {
        cacheEng::update_process($mysqli, $username, $pieName, 0);
        write_to_log("ERROR", $pieName . ":in_process field at cache table was reset from 5 to 0");
    }
}

function getTrendInfo($username, $mysqli, $trendName) {
    $cond_array = get_CondArr($mysqli, $trendName, $username);
    $results_exists = $cond_array['results_exists'];
    $in_process = $cond_array['in_process'];
    $results_encoded = null;

    //if the results data already exists
    if ($results_exists && !$in_process) {
        asyncRequst::getInstance()->calulatePieWrapper($mysqli, $trendName, $in_process, $username);

    }

    
    $stats = getTableElementData($mysqli, 'dashboard',$trendName,'username',$username);
    $data = $stats->details;
    $json_data = json_decode($data, true);
    if ($results_exists) {
        $results_encoded = json_decode($stats->results, true);
    } else {
        require_once 'proSentPageEng.php'; //should not run anything just load function declarations
        $results_encoded = getTrend($mysqli, false, $json_data, false, $trendName, true);
    }
    
    $results_encoded = CacheEng::getFromCache($mysqli, $username, $trendName);
    
    $json_return['data'] = json_decode($results_encoded->results, true);
    $json_return['search'] = $json_data;

    //will reset in process after 5 req for pie
    resetInProcessField($mysqli, $trendName, $username);
    return $json_return;
}


/**Removes a file properly if exists.*/
function removeFile($filePath){
	if(file_exists($filePath))
		unlink($filePath);
}

function updateUsersLastModifiedDate($connection){
	$date = date('YmdHis');
	$query = "UPDATE user_status SET last_time_modified=$date WHERE username IS NOT NULL";
	$res = $connection->query($query);
}

//return array of status and user
function isLockedDoc($mysqli, $docId, $username){
	$query = "SELECT user FROM lockedfiles WHERE docId=$docId";
	$res = sqlQuery($mysqli, $query);
    $row = $res->fetch_object();
    is_null($row) || $user = $row->user;
	if (!empty($user) && $user != $username && strlen($user) > 0)
		return array("status" => true,
					 "user" => $user);
	else 
		return array("status" => false,
					 "user" => "");
}

function setLockDoc($mysqli, $docId, $username){
	$date = date('YmdHis');
	$query = "UPDATE lockedfiles SET docId=$docId, date=$date WHERE user='$username'";
	$res = sqlQuery($mysqli,$query);
}

function releaseLockDoc($mysqli, $username){
	$query = "UPDATE lockedfiles SET docId=NULL, date=NULL WHERE user='$username'";
	$res = sqlQuery($mysqli,$query);
}

//returns a permutation of $arr ordered by $arr_id 
//$arr_id defined as (desired place => docId)
//call it like this: $resArray = reshuffleResults($res->fetch_all(), array_keys($arr_id));
function reshuffleResults($arr, $arr_id) {
	$backIndex = array_flip($arr_id);
	$reshuffled = range(0, count($arr)); //creating the entries here makes the final ksort($reshuffled) unnecessary
	foreach($arr as $row) {
		$reshuffled[$backIndex[$row[0]]] = $row;
	}
	return $reshuffled;
}

function toDbString($mysqli, $text, $len = 99) {
    if(empty($text)){
        return "";
    }
    //shouldn't exceed VARCHAR(100)
    if (is_array($text)) {
        $text = json_encode($text, true);
    } elseif (strlen($text) > $len) {
        $text = mb_strcut($text, 0, $len-3) . "...";
    }
    return $mysqli->real_escape_string($text);
}

//same as mkdir($folder, 0777, true) that for some reason does not work 
//does not support UNC paths
function mkdirRecursive($folder)
{
	if(preg_match('/^[a-zA-Z]:/', $folder)) {
		$pathbuilder = substr($folder, 0, 2);
		$pathbuilder .= "/";
		$folder = substr($folder, 2);
	} else {
		$pathbuilder = "";
	}
	$tok = strtok($folder, "\\/");
	
	while ($tok !== false) {
		$pathbuilder .= "$tok/";
		echo "$pathbuilder\n";
		if( !is_dir($pathbuilder) ) mkdir($pathbuilder);
		$tok = strtok("\\/");
	}
}

function getSavedSearchWordCloud($connection, $savedsearchId, $count=50)
{
	return getDocsWordCloud($connection, "SELECT docid FROM savedsearchid WHERE id=$savedsearchId", $count);
}

function getDocsWordCloud($connection, $docIds, $count = 50)
{
    //get most mentioned phrases in search
    $sql = "SELECT COUNT(VALUE) AS v,value FROM phrases " . (empty($docIds)?"" : "WHERE docId IN ($docIds) ");
    $sql .= "GROUP BY value ORDER BY v DESC LIMIT 2500";
    $res = sqlQuery($connection, $sql);
    $phrases = [];
    $sqlPhrases = [];
    while ($res && $row = $res->fetch_object()) {
        //a phrase unique to one document is not indicative
        if ($row->v == 1)
            continue;

        $phrases[$row->value] = $row->v;
        $sqlPhrases[] = $connection->real_escape_string($row->value);
    }
    //get overall count of search phrases
    $res = sqlQuery($connection, "SELECT COUNT(VALUE) AS v,value FROM phrases WHERE value IN('" . join("','", $sqlPhrases) . "') GROUP BY value");
    while ($res && ($row = $res->fetch_object())) {
        $phrasesTFIDF[$row->value] = $phrases[$row->value] / $row->v;
        //write_to_log("TRACE", "value:" . $row->value . ", phrases v: " . $phrases[$row->value] . ", total v: " . $row->v . ", tfidf: " . $phrasesTFIDF[$row->value]);
    }
    //sort by phrases by TFIDF
    arsort($phrasesTFIDF);
    //return the counts of phrases, ordered by TFIDF
    $phrases = array_replace($phrasesTFIDF, $phrases);
    //take top count by TFIDF
    $phrases = array_slice($phrases, 0, $count);
    //sort by counts (in search docs)
    arsort($phrases);
    return $phrases;
}

function cleanDB($mysqli, $userdetails){	
	global $info_log_file;
	global $warrning_log_file;
	global $error_log_file;
	global $trace_log_file;
	global $perfomence_log_file;
	global $xml_results_path;
	global $metadata_path;
	global $sharedFolder;
	if (!getUserPermission(CAN_DELETE)){
		return false;
	}
	
	require_once 'sqlTable.php';
	unlinkFile($info_log_file);
	unlinkFile($error_log_file);
	unlinkFile($warrning_log_file); 
	unlinkFile($trace_log_file);
	unlinkFile($perfomence_log_file);   
       
	if (getUserPermission(CAN_SHOW_ALL_USER)){
		//echo "allow deleting all data for administraor";
		createDB($mysqli);
	}
	else{
		cleanDBSpecificUser($mysqli, $userdetails[Username]);
	}
	$mysqli = sqlCreateConnection("addUsersSettings");
	addUsersSettings($mysqli);
	sqlClose($mysqli, "addUsersSettings");
	
	deleteFSDirectory($xml_results_path);
	deleteFSDirectory($sharedFolder.DIRECTORY_SEPARATOR . "done_results"); // remove saved rdf in shared folder
	deleteFSDirectory($metadata_path);
	mkdir_full($metadata_path, 0777, true);
	mkdir_full($xml_results_path, 0777, true);
	return true;
}	

function unlinkFile($file){
    if(file_exists($file)){
         unlink($file); 
    }
}

//delete file system
function deleteFSDirectory($dirPath) {
    if (is_dir($dirPath)) {
        $objects = scandir($dirPath);
        foreach ($objects as $object) {
            if ($object != "." && $object !="..") {
                if (filetype($dirPath . DIRECTORY_SEPARATOR . $object) == "dir") {
                    deleteFSDirectory($dirPath . DIRECTORY_SEPARATOR . $object);
                } else {
                    unlink($dirPath . DIRECTORY_SEPARATOR . $object);
                }
            }
        }
    //reset($objects);
    rmdir($dirPath);
    }
}

function sortByCoRefsDesc($a, $b)
{
	//if coRefs are equal - sort by name1-name2 ASC
	if ($a["coRefs"] == $b["coRefs"])
		return $a["name1"] . " - " . $a["name2"] > $b["name1"] . " - " . $b["name2"];
		
	return $a["coRefs"] < $b["coRefs"];
}


function getMultiTopCoRefs($mysqli, $table1, $table2, $limit, $savedsearchesid, $username, $isAdmin, $name1 = null, $name2 = null, $groupByDoc = false, $returnSQL = false, $smartSearch = false,$pastPeriod = null, $saved_sql = null)
{
    $object_arr = array("person_object","organizational_identity","event","place_object");
    $counter=0;
    $final_all_arr = array();
    
    //if only one side marks "all"
    if(!empty($table1)||!empty($table2) && ($table1 !== 'undefined' || $table2 !== 'undefined')){
        $const_table = empty($table2) ? $table1 : $table2;
        foreach($object_arr as $element){
            $res = getTopCoRefs($mysqli, $const_table, $element, $limit, $savedsearchesid, $username, $isAdmin, $name1, $name2, $groupByDoc, $returnSQL, $smartSearch,$pastPeriod);
            $final_res = array("from_to"=>array($const_table, $element),"data"=>$res); // adding the types as array in the begining
            $final_all_arr[] = $final_res;
            
        }
    }
    else{
        foreach($object_arr as $element){
            for($i=$counter; $i<sizeof($object_arr); $i++){
                $res = getTopCoRefs($mysqli, $element, $object_arr[$i], $limit, $savedsearchesid, $username, $isAdmin, $name1, $name2, $groupByDoc, $returnSQL, $smartSearch,$pastPeriod);
                $final_res = array("from_to"=>array($element, $object_arr[$i]),"data"=>$res); // adding the types as array in the begining
                $final_all_arr[] = $final_res;
            }
            $counter++;
        }
    }
    return $final_all_arr;
 }
 
 function fetchNamesFromName($mysqli, $table,$name){
     $names_str = "";
     $name_arr = empty($name) ? $name : getMangeEntities($mysqli, $table,$name, false, true) ;
     if(!empty($name_arr) && array_key_exists("rows", $name_arr)){
        foreach($name_arr["rows"] as $curr_name){
             $names_str .= "'".$mysqli->real_escape_string($curr_name)."',";
        }
     }
     if(!empty($names_str)){
         $res = rtrim($names_str, ',');
     }
     elseif(empty($name)){
         $res = null;
     }
     else{
         $res = "'".$mysqli->real_escape_string($name). "'";
     }
     return $res;
 }
 
 function  fetchCorefsParams($mysqli, $table1, $table2, $savedsearchesid, $groupByDoc, $name1, $name2, $pastPeriod, $isAdmin, $username){
     
     $names1_str = fetchNamesFromName($mysqli, $table1, $name1);  
     $names2_str = fetchNamesFromName($mysqli, $table2, $name2);
     
     //dates
     $dates_arr = getPastPeriod_ExtraData($pastPeriod);
     $date_sql = !is_null($dates_arr) ? $dates_arr["date_sql"] : ""; 
     $date_sql_string = $date_sql!=="" ? " t1.docID in $date_sql " : " TRUE ";

    //savedSearches
     $savedsearchesTable = $savedsearchesid ? ", savedsearchid ssi ": " ";
     $savedsearchesSql = $savedsearchesid ? " ssi.docId = t1.docId AND ssi.id = $savedsearchesid " : " TRUE ";
     $saved_sql=  !empty($saved_sql) ? "  t1.docId IN($saved_sql) " : " TRUE ";
     
     //permissions
     $user_sql = !$isAdmin ? " t1.docID=files.id AND files.username='$username' " : " TRUE ";
     $files_sql = !$isAdmin ? "files," : "";
 
     $docCol = $groupByDoc? "distinct t2.docId" : "COUNT(t2.docId) AS coRefs";
     $groupAndOrder = $groupByDoc? "" : "
		GROUP BY name1, name2
		ORDER BY coRefs DESC, name1 ASC, name2 ASC";
     $nameFilter =	($names1_str ? "(t1.name IN (" . $names1_str . 
//          "' OR t1.orgText = '" . $mysqli->real_escape_string($name1)
         ")) " : "  TRUE ");
     $nameFilter .= ($names2_str ? " AND t2.name IN (" . $names2_str .")" :  " AND TRUE");
     $nameComparisonOperator = $nameFilter? "!=" : "<"; //prevent duplications by comparing the names, leaving the name with higher alphabetical order first
     
     return array(
         'date_sql_string' => $date_sql_string,
         'savedsearchesSql' => $savedsearchesSql,
         'savedsearchesTable' => $savedsearchesTable,
         'saved_sql' => $saved_sql,
         'user_sql' => $user_sql,
         'files_sql' => $files_sql,
         'docCol' => $docCol,
         'groupAndOrder' => $groupAndOrder,
         'nameFilter' => $nameFilter,
         'nameComparisonOperator' => $nameComparisonOperator
     );
   
         
 }

 function getTopCoRefs($mysqli, $table1, $table2, $limit, $savedsearchesid, $username, $isAdmin, $name1 = null, $name2 = null, $groupByDoc = false, $returnSQL = false, $smartSearch = false,$pastPeriod=null, $saved_sql = null)
{  
//     if (!preg_match("/^[\\w_\\d]+$/", $table1) || !preg_match("/^[\\w_\\d]+$/", $table2))
//     {
//         write_to_log("ERROR", "getTopCoRefs: invalid table names: $table1, $table2");
//         return null;
//     }
    
    $corefs_params = fetchCorefsParams($mysqli, $table1, $table2, $savedsearchesid, $groupByDoc, $name1, $name2, $pastPeriod, $isAdmin, $username);
    
    $date_sql_string = $corefs_params['date_sql_string'];
    $savedsearchesSql = $corefs_params['savedsearchesSql'];
    $savedsearchesTable = $corefs_params['savedsearchesTable'];
    $saved_sql = $corefs_params['saved_sql'];
    $user_sql = $corefs_params['user_sql'];
    $files_sql = $corefs_params['files_sql'];
    $docCol = $corefs_params['docCol'];
    $groupAndOrder = $corefs_params['groupAndOrder'];
    $nameFilter = $corefs_params['nameFilter'];
    $nameComparisonOperator = $corefs_params['nameComparisonOperator'];
    
	if ($smartSearch){
		$topCoRefsSql = "
			SELECT $docCol
			FROM $files_sql $table1 t1, $table2 t2 $savedsearchesTable
			WHERE
			$nameFilter
			AND t1.docId = t2.docId AND $saved_sql AND STRCMP(t1.name, t2.name) $nameComparisonOperator 0
            AND $date_sql_string
            AND $user_sql
			AND $savedsearchesSql
			$groupAndOrder";
	}
	else{ 
		$topCoRefsSql = "
			SELECT t1.name AS name1, t2.name AS name2, $docCol,  GROUP_CONCAT(t1.docid) as docs
			FROM $files_sql $table1 t1, $table2 t2 $savedsearchesTable
			WHERE
			$nameFilter
			AND t1.docId = t2.docId AND $saved_sql AND  STRCMP(t1.name, t2.name) $nameComparisonOperator 0 
			AND $date_sql_string
			AND $user_sql
			AND $savedsearchesSql
			$groupAndOrder
			LIMIT $limit";
	}
	//handle manual aggregations
			global $entitiesArray;
		$entities = [];
	foreach ([$table1, $table2] as $t)
	    $entities[] = $entitiesArray[$t];
	$entities = array_filter(array_unique($entities)); //remove duplicates and nulls
	$res = sqlQuery($mysqli, "SELECT entity AS e, name AS n,aggregatedName AS aggn,aggregatedManual AS man FROM entitiesmanage WHERE entity IN ('" . join("', '", $entities) . "')");
	//maps aggregated names to list of names
	$name2agg = [];
	while ($res && ($row = $res->fetch_object()))
	{
		write_to_log("TRACE", "row:" . print_r($row, true));
		$names = array_filter(explode(";", $row->aggn . ";" . $row->man));
		foreach ($names as $name)
		{
			$agg2names[$row->e][$mysqli->real_escape_string($row->n)][] = $mysqli->real_escape_string($name);
		}
	}
	//replace tables with 'aggregated' tables
	$tables = [$table1];
	if ($table2 != $table1)
		$tables[] = $table2;
	
	foreach ($tables as $table)
	{
	    $entity = $entitiesArray[$table];
	    if (!$entity || (empty($agg2names) || !array_key_exists($entity, $agg2names)))
			continue;
		
		//replace table with an sql statement which replaces names with their aggregated name (if exists entitiesmanage table)
		$aggSqls = [];
		foreach ($agg2names[$entity] as $agg => $names)
			$aggSqls[] = " $table.name IN ('" . join("', '", $names) . "') AND entitiesmanage.name = '$agg' ";
		
		$aggregatedTable = "
			(SELECT $table.docId, COALESCE(entitiesmanage.name, $table.name) AS NAME FROM $table 
			LEFT JOIN entitiesmanage ON " . join(" OR ", $aggSqls) . ")";
		
		$topCoRefsSql = str_replace($table, $aggregatedTable, $topCoRefsSql);
	}
	if ($returnSQL)
		return $topCoRefsSql;
	
	$res = sqlQuery($mysqli, $topCoRefsSql);
	$results = $res ? $res->fetch_all(MYSQLI_ASSOC) : [];
	
	//add dates if configure in results docs aray
    if(!empty($results) && !empty($results[0]["docs"])){
        $results = convertCoRefsResults($mysqli, $results);
	}
	    
	return $results;

}

function fetchRelationsParams($mysqli, $username, $subjectName, $subjectType, $objectName, $objectType,$relationType, $ignoreSubject, $savedsearchesid, $pastPeriod){
    require_once 'ManageEntitiesEng.php';
    $savedsearchesSql = $savedsearchesTable = "";
    if ($savedsearchesid)
    {
        $savedsearchesTable = ", savedsearchid ssi";
        $savedsearchesSql = " AND ssi.docId = ier.docId AND ssi.id = $savedsearchesid ";
    }
    $aggSubject = false;
    $aggObject = false;

    
    //patch, v2 ui sends undefined or null for no reason
    $objectName = empty($objectName) || in_array($objectName,array('null','undefined')) ? "" : $objectName;
    $subjectName = empty($subjectName) || in_array($subjectName,array('null','undefined')) ? "" : $subjectName;
    $subjectType = empty($subjectType) || in_array($subjectType,array('null','undefined')) ? "" : $subjectType;
    $objectType = empty($objectType) || in_array($objectType,array('null','undefined')) ? "" : $objectType;

    $files_join = "";
    $orgSubjectName = $mysqli->real_escape_string($subjectName);
    $orgObjectName = $mysqli->real_escape_string($objectName);
    
    
    if(strlen($subjectName) > 0){
        $aggSub = getAggFromName($mysqli, $username, $subjectName, $subjectType);
        if(!empty($aggSub)){
            $subjectName = "'".implode("','", $aggSub)."'";
        }
        else{
            $subjectName = (strlen($subjectName) > 0) ? getAggSuffix($mysqli, $subjectName, $subjectType, $aggSubject) : $subjectName;  
        }      
    }
    if(strlen($objectName) > 0){
        $aggObj = getAggFromName($mysqli, $username, $objectName, $objectType);
        if(!empty($aggObj)){
            $objectName = "'".implode("','", $aggObj)."'";
        }
        else{
            $objectName = (strlen($objectName) > 0) ? getAggSuffix($mysqli, $objectName, $objectType, $aggObject) : $objectName;
        }
    }

    
    /**
     * Default for now - ignore subject or object - treat it as A and B
     */
    if ($ignoreSubject){   
        if ($subjectName){
            $subjectName = "(subject IN ($subjectName) || object IN ($subjectName))";
        }
        if ($objectName){
            $objectName = "(subject IN ($objectName) || object IN ($objectName))";
        }
        
        $subject_object_types = getRelationsTableTypes($subjectType, $objectType);
        $subjectType = $subject_object_types["subject"];
        $objectType = $subject_object_types["object"];

        
    }    
    else{
        if ($subjectName){
            $subjectName = "subject IN ($subjectName)";
        }
        if ($objectName){
            $objectName = "object IN ($objectName)";
        }
    }

//     $relationType = getRelationschildsTypes($mysqli, $relationType);
    $relationType = is_array($relationType) ? implode("','", $relationType) : 
    str_replace(",", "','", $relationType);
    
    $arr = array("subject_table" => $subjectType, "relation_type_inst" => "'$relationType'", "object_table" => $objectType,
        "subject" => $subjectName, "object" => $objectName);
    
    $where = arr_to_where($arr, $ignoreSubject);
    
    //sets the pastPeriod addon
    $dates_arr = getPastPeriod_ExtraData($pastPeriod);
    $date_sql = !empty($dates_arr) ? $dates_arr["date_sql"] : "";
    
    if (strlen($where) == 0 && $savedsearchesid){
        $where = "ssi.docId = ier.docId AND ssi.id = $savedsearchesid";
        if($date_sql!==""){
            $where.=" AND ssi.docId IN $date_sql";
        }
    }
    
    else if(strlen($where) > 0){
        $where .= $savedsearchesSql;
    }
    
    return array(
        'date_sql' => $date_sql,
        'aggSubject' => $aggSubject,
        'orgObjectName' => $orgObjectName,
        'aggSubject' => $aggSubject,
        'aggObject' => $aggObject,
        'savedsearchesSql' => $savedsearchesSql, 
        'savedsearchesTable' => $savedsearchesTable,
        'orgSubjectName' => $orgSubjectName,
        'orgObjectName' => $orgObjectName,
        'where' => $where
       
    );
}

/**
 * get relations childs by relation CAPTION
 * @param unknown $type
 * @return array
 */
function getRelationschildsTypes($mysqli, $type){
    if(empty($type)){
        return null;
    }
    
    $sql = "SELECT distinct relation_type_inst FROM inter_entity_relationship WHERE relation_type=\"$type\"";
    $res = sqlQuery($mysqli, $sql);
    $obj = $res->fetch_object();
    $rel_inst = $obj->relation_type_inst;
    
    $sql = "SELECT sub_relations FROM `sub_relations` WHERE relationship = '$rel_inst'";
    $res = sqlQuery($mysqli, $sql);
    $obj = $res->fetch_object();
    $sub_relations = $obj->sub_relations;
    $results = "'$rel_inst',".$sub_relations;
    return $results;
}




function getRelationsTableTypes($subjectType, $objectType){
    $subjectType_str = $objectType_str = "";
    if ($subjectType){
        if(empty($objectType)){
            $subjectType_str = "((subject_table IN (\"$subjectType\") || object_table IN (\"$subjectType\")))";
        }
        else{
            $subjectType_str = "((subject_table IN (\"$subjectType\") AND object_table IN (\"$objectType\")) ||
                             (subject_table IN (\"$objectType\") AND object_table IN (\"$subjectType\")))";              
        }
    }

    if ($objectType){
        if(empty($subjectType)){
            $objectType_str = "((subject_table IN (\"$objectType\") || object_table IN (\"$objectType\")))";
        }
        else{
            $objectType_str = "((subject_table IN (\"$subjectType\") AND object_table IN (\"$objectType\")) ||
                           (subject_table IN (\"$objectType\") AND object_table IN (\"$subjectType\")))";
        }       
    }
    
    return ["subject" => $subjectType_str, "object" => $objectType_str];
}

function getTopRelation($mysqli, $relationType, $subjectType, $objectType, $groupBy, $savedsearchesid, $subjectName, $objectName, $relationList, $ignoreSubject,$isAdmin,$username, $returnSqlId=null,$pastPeriod=null, $saved_sql = null) {
    $params = fetchRelationsParams($mysqli, $username, $subjectName, $subjectType, $objectName, $objectType,$relationType, $ignoreSubject, $savedsearchesid, $pastPeriod);
	
    $date_sql = $params['date_sql'] ?? null;
    $aggSubject = $params['aggSubject'] ?? null;
    $orgObjectName = $params['orgObjectName'] ?? null;
    $aggObject = $params['aggObject'] ?? null;
    $savedsearchesSql = $params['savedsearchesSql'] ?? null;
    $savedsearchesTable = $params['savedsearchesTable'] ?? null;
    $orgSubjectName = $params['orgSubjectName'] ?? null;
    $savedsearchesSql = $params['savedsearchesSql'] ?? null;
    $where = $params['where'] ?? null;
    
    
    $files_join = "";
   
    //A - get relations list SQL only
	if ($relationList){
	    if($date_sql !== ""){
	        $where.="AND inter_entity_relationship.docId IN $date_sql";
	    }
	   
		$topRelationSql = "SELECT DISTINCT relation_type FROM inter_entity_relationship AS ier WHERE $where";//($where OR $where_trans)";	
	}
	//B -return the sql - use on SEARCH ONLY - stops here and return the sql only	
	elseif ($returnSqlId){	
	    if(!empty($date_sql)){
	        $where.="AND ier.docId IN $date_sql";
	    }
	    if(!$isAdmin){
	        $where.=" AND ier.docId=files.id AND files.username='$username'";
	        
	    }
	    return "SELECT DISTINCT docId FROM inter_entity_relationship AS ier  WHERE $where";//($where OR $where_trans)";	
	}
	
	//C - the rest (Of Analytics of overview) FLOW - build the SQL for the analytics
	else{
	    
	    if($aggSubject && !$ignoreSubject){
			$subject = "'$orgSubjectName' AS subject";
	    }
	    else{
			$subject = "subject";
	    }
	    if ($aggObject && !$ignoreSubject){
	        $object = "'$orgObjectName' AS object";
	    }
	    else{
			$object = "object";
	    }
		if(!empty($date_sql)){
		    $where.=" AND ier.docId IN $date_sql";
		}
		if(!$isAdmin){
		    $where.=" AND ier.docId=files.id AND files.username='$username'";
		    $files_join=",files ";
		}
		
		if(!empty($saved_sql)){
		    $where.=" AND ier.docId IN($saved_sql)";		    
		}
		//For V2 (but it generic..) - add the limit of connection in the url
		$limit_suffix = isset($_REQUEST["connections"]) ? "LIMIT $_REQUEST[connections]" : "";
		
// 		$topRelationSql = "SELECT $subject, subject_agg, subject_table,$object, 
//                             object_agg, object_table,COUNT(DISTINCT ier.docId) AS count,
//                             relation_type, relation_type_inst, GROUP_CONCAT(ier.docId) as docs
//                              FROM inter_entity_relationship AS
//                              ier".$files_join." $savedsearchesTable WHERE ($where) 
//                             GROUP BY subject,object,relation_type ORDER BY count DESC,SUBJECT ASC,object ASC,relation_type ASC $limit_suffix";
		
		$topRelationSql = "
                            SELECT
                                $subject, subject_agg, subject_table,$object, 
                            object_agg, object_table,COUNT(DISTINCT ier.docId) AS count,
                            relation_type, relation_type_inst, GROUP_CONCAT(ier.docId) as docs
                            FROM
                                inter_entity_relationship AS ier".$files_join.
                                "$savedsearchesTable
                            WHERE
                                ($where)
                            GROUP BY
                                subject,
                                subject_agg,
                                subject_table,
                                object,
                                object_agg,
                                object_table,
                                relation_type,
                                relation_type_inst
                            ORDER BY
                                count DESC,
                                subject ASC,
                                object ASC,
                                relation_type ASC
                             $limit_suffix;
                            ";
	}
	//GENERIC FLOW FOR A AND C 
	$res = sqlQuery($mysqli, $topRelationSql);
	if ($res != false){
		$results_org = $res->fetch_all(MYSQLI_ASSOC);
		
		//I - if it's relations list - just fetch the name
		if (strlen($relationList ?? "") > 0){
		
		    $results_org = array();
			foreach ($res as $k => $v){
			    $results[]["relation_type"] = $v['relation_type'];
			}
		}else{
		    //II - fetch the whole DATA	    
		  
		    $results = convertRelationsResults($mysqli, $results_org);

			$res = array();
			foreach ($results as $o){
			    //hide relations with no table type
			    if(empty($o['subject_table']) || empty($o['object_table'])){
			        continue;
			    }
				$res[] = $o;			
			} 
			$results = $res;
		    
		}
	}
	
	
	//just for fetching the list
	if ($relationList){
        $results[] = ["relation_type" => "coRefs"]; // add co-ref explictly 
	    
         usort($results , function ($item1, $item2) {
             return $item1["relation_type"] <=> $item2["relation_type"];
         });
	}
	//returnning the actual results
	else{
	    usort($results , function ($item1, $item2) {
	        return $item2["count"] <=> $item1["count"];
	    });
	}
	
	
	return $results;
}

//relevant only for V2
function getTreeArr($connection, $arr){
    $arr_res = array();
    foreach($arr as $item_arr){
        $label = $item_arr['name'];
               
        $amount = $item_arr['amount'];
        $item_arr['id'] =  $label;
        
        $labelCaption = getCaptionConcept($connection, $label);
        if($labelCaption == "Not to present"){
            continue;
        }
        $item_arr['name'] =  $labelCaption;
        
        $sql = "SELECT parent_name FROM `parent_tables` WHERE concept_name ='$label' ORDER BY id LIMIT 1";
        $res_parent = sqlQuery($connection,$sql);
        $row_parent = $res_parent->fetch_object();
        $caption = $row_parent->parent_name ?? null;
        
        if(!empty($caption)){
            $labelCaptionParent = getCaptionConcept($connection, $caption);
            if($labelCaptionParent == "Not to present"){
                continue;
            }
            $index = array_search($caption, array_column($arr_res, 'id'));
            //if caption doesnt exists in anti_arr
            if(empty($index) && $index !== 0 ){
                $arr_res[] = array("id"=> $caption, "name"=> $labelCaptionParent, "children"=>array($item_arr));
            }
            else{
                $inner_index = array_search($label, array_column($arr_res[$index]["children"], 'name'));
                //if kid exists
                if(empty($inner_index) && $inner_index !== 0 ){
                    $arr_res[$index]["children"][] = $item_arr;      
                }
                else{
                    $arr_res[$index]["children"][$inner_index]['amount'] += $amount;       
                }
            }       
        }
        else{
            $arr_res[] = array("id"=> $label, "name"=> $labelCaption, "children"=>array($item_arr));
        }
    }
    
    return $arr_res;
}

/**
 * get caption from concept per db
 * @param unknown $mysqli
 * @param unknown $label
 * @return unknown
 */
function getCaptionConcept($mysqli, $label){
    $label_val = strtolower($label);
    $caption_sql = "SELECT caption FROM allconceptscaptions WHERE concept=\"$label_val\"";
    $res_caption = sqlQuery($mysqli,$caption_sql);
    $obj = $res_caption->fetch_object();
    if($obj->caption){
        $label = $obj->caption;
    }
    $label = TablesInformation::getConceptPresentName($label);
    return $label;
}

function getMinMaxDatesPerDocs($connection, $docs){
    $docs = trim($docs,',');
    $sql = "SELECT (DATE_FORMAT(MIN(DATE), \"%d.%m.%Y\")) AS min, (DATE_FORMAT(MAX(DATE), \"%d.%m.%Y\")) AS max FROM files WHERE id IN($docs)";
    $res = sqlQuery($connection,$sql);
    
    $row = $res->fetch_object();
    return ["min"=> $row->min, "max"=> $row->max];
}

function convertCoRefsResults($mysqli, $results){
    $res = $results;
    foreach ($res as &$o){
        $dates = getMinMaxDatesPerDocs($mysqli, $o["docs"]);
        unset($o["docs"]);
        $o["dates"] = "$dates[min] - $dates[max]";
    }
    return $res;
}

function convertRelationsResults($connection, $results){
    $res = $results;
    foreach ($res as &$o){
        $dates = getMinMaxDatesPerDocs($connection, $o["docs"]);
        unset($o["docs"]);
        $o["dates"] = "$dates[min] - $dates[max]";
        $names = getNamesEntity($connection, $o);
        $o['subject'] = !empty($names['subject']) ? $names['subject']: $o['subject'];
        $o['object'] = !empty($names['object']) ? $names['object']: $o['object'];            
    }
    return $res;
}

function getNamesEntity($connection, $rel){
    $row_sub = $row_obj = null;
    //SELECT NAME FROM person_object WHERE name_version='Sheikh al-Islam Ibn Taymiyyah'
    if(empty($rel['subject_table']) ||  !empty($rel['subject_table'])){
        return null;
    }
    if(!empty($rel['subject_table'])){
        $name_sub = $connection->real_escape_string($rel['subject']);   
        $sql_subject = "SELECT name FROM $rel[subject_table] WHERE name_version = '$name_sub'";
        $res_sub = sqlQuery($connection,$sql_subject);
        $row_sub = $res_sub->fetch_object();
    }
    if(!empty($rel['object_table'])){
        $name_obj = $connection->real_escape_string($rel['object']);
        $sql_object = "SELECT name FROM $rel[object_table] WHERE name_version = '$name_obj'";
        $res_obj = sqlQuery($connection,$sql_object);
        $row_obj = $res_obj->fetch_object();
    }
    
    return array(
        'subject' => $row_sub->name ?? null,
        'object' => $row_obj->name ?? null
    );
}
    
// function convertRelationsResults($results){
//     $res = array();
//     foreach ($results as $o){
//         $o['id'] = $o['name'] = $o['subject']." &#8594 ".$o['object'];
        
//         if(!searchSubRelationType($res, $o['relation_type'])){
//             $res[] = array("id"=> $o['relation_type'], "name"=> $o['relation_type'], "children"=> array());
//         }
        
//         $index = array_search($o['relation_type'], array_column($res, 'id'));
//         $res[$index]['children'][] = $o;
        
        
//     }
//     return $res;
// }

function searchSubRelationType($children, $type){
    foreach($children as $child){
        if($child['id'] == $type){
            return true;
        }
    }
    return false;
}

function flatten_assoc($item_arr) {
    $return = array();
    
    if(!is_array($item_arr)){
        return $item_arr;
    }
    
    $key_item = key($item_arr);
    
    $return[] = $key_item;
    
    $val = flatten_assoc($item_arr[$key_item]);
    if(!is_array($val)){
        $val = array($val);
    }
    foreach($val as $item){
        $return[] = $item;
        
    }
        
    return $return;
}


function unique_ids($array){
    foreach($array as &$parent_item){
        $i = 0;
        $parent_name = $parent_item['name'];
        foreach($parent_item['children'] as &$type_item){
            $type_name = $type_item['name'];
            if($parent_name==$type_name){
                $type_item['id'] .= '_'.strval(++$i);
                
            }
        }
    }
    return $array;
}

function getAggSuffix($mysqli, $entity_name, $entity_Type, &$agg){   
    global $entitiesArray;
    $gArr = array();
    $first = true;
    
    //relveant only for v1
    if(!array_key_exists($entity_Type, $entitiesArray) && empty($_REQUEST["v2"])){
        $entity_Type = $entitiesArray[$entity_Type] ?? null;
    
    }
    if (strlen($entity_Type) > 0){
        $gArr = getMangeEntities($mysqli, $entitiesArray[$entity_Type] ?? $entity_Type, $entity_name, true);
    }
    else
    {
        foreach(array_keys($entitiesArray) as $table){
            $tmp_arr = getMangeEntities($mysqli, $entitiesArray[$table], $entity_name, true);
            $gArr["rows"] = array_merge($gArr["rows"] ?? [],$tmp_arr["rows"] ?? []);
        }
    }
    $entity_name_org = $mysqli->real_escape_string($entity_name);
    
    if (sizeof($gArr["rows"] ?? []) > 0){
        $first = true;
        foreach ($gArr["rows"] as $r){
            $name = $mysqli->real_escape_string($r["name"]);
            if ($first){
                $entity_name = "'" . $name . "'";
                $first = false;
            }else{
                $entity_name .= ",'" . $name . "'";
            }
        }
        $entity_name .= ",'" . $entity_name_org . "'";
        $agg = true;
    }
    else{        
        $entity_name = "'$entity_name_org'";
    }           
    return $entity_name;
}

function arr_to_where($arr, $ignoreSubject = false){
    $first = true;
    $where = "";
    foreach ($arr as $k => $v){
        if (strlen($v) > 0 && trim($v, "'") !== 'all'){
            //set the key_val
            $key_val = getKeyValWhere($k, $v, $ignoreSubject);
  
            if ($first){
                $where = "$key_val ";
                $first = false;
            }
            else{
                $where .= " AND $key_val ";
            }
        }
    }
    if(empty($where))
        return "TRUE";
    return $where;
}

function getKeyValWhere($k, $v, $ignoreSubject = false){
    if($k=="relation_type_inst"){
//         $key_val = (strpos($v,"IN (")!==false) ? "$k$v" : "$k='$v'";
        $key_val = empty(trim($v)) || empty(trim($v, "'")) || empty(trim($v, '"')) ? " TRUE " : " $k in ($v)";
        
    }
    elseif(in_array($k, ["subject_table", "object_table"])){
        $key_val = $ignoreSubject ? "$v" : "$k='$v'";
    }
    else{
        $key_val = "$v";
    }
    
    return $key_val;
}

function getMoreVariations($name){
    $results = [];
    $output = preg_replace('!\s+!', ' ', $name);
    $arr = pc_permute(explode(' ', $output));
    foreach($arr as $item){
        $results[] = ["numRef" => "1", "name" => implode(', ', $item), "checked" => false];
    }
    return $results;
}

function pc_permute($items, $perms = array( )) {
    if (empty($items)) {
        $return = array($perms);
    }  else {
        $return = array();
        for ($i = count($items) - 1; $i >= 0; --$i) {
            $newitems = $items;
            $newperms = $perms;
            list($foo) = array_splice($newitems, $i, 1);
            array_unshift($newperms, $foo);
            $return = array_merge($return, pc_permute($newitems, $newperms));
        }
    }
    return $return;
}

function whereEmailSql($table,$org_name,$mysqli,$returnArr=false){
	$pArr = array();
	$pArr = getMangeEntities($mysqli, "person", $org_name,true);	
	$pArr["rows"] = array_merge($pArr["rows"], getMoreVariations($org_name));
	if (sizeof($pArr["rows"]) > 0){
		$checkedArr = array();
		$allArr = array();
		foreach ($pArr["rows"] as $r){
			$n = trim($r["name"],"\"");	
			$n = $mysqli->real_escape_string($n);
			$name = $mysqli->real_escape_string($r["name"]); 					
			if ($r["checked"]){
				$checkedArr[] = $name;
				if ($n != $name)					
					$checkedArr[] = $n;	
			}else{
				$allArr[] = $name;
				if ($n != $name)
					$allArr[] = $n;				
			}							
		}
		if (sizeof($checkedArr) > 0)
			//use the aggregeted only			
			$str = "'" . implode("','", $checkedArr) . "'";				
		else
			$str = "'" . implode("','", $allArr) . "'";			
	}else{
		$str = "'$name'";			
	}
	$str .= ",'$org_name'";
	$where = " ($table.value IN($str) OR $table.display_name IN($str) OR $table.email IN($str))";
	if ($returnArr){
	    $allArr[] = $org_name;
		return $allArr;
	}
	else
		return $where;
}

function getTopCorresponded($mysqli, $groupBy, $savedsearchesid, $subjectName, $objectName,$pastPeriod,$isAdmin,$username, $objectType=null, $limit=500, $saved_sql = null){
	$savedsearchesSql = $savedsearchesTable = "";
	
	//sets the pastPeriod addon
	$dates_arr = getPastPeriod_ExtraData($pastPeriod);
	    
	if ($savedsearchesid)
	{
		$savedsearchesTable = ", savedsearchid ssi";
		$savedsearchesSql = " AND ssi.docId = from_field.docId AND ssi.id = $savedsearchesid ";
	}	
	$whereFrom = $to = $cc = $bcc = "";	
	if (strlen($subjectName) > 0){
		$subjectName = $mysqli->real_escape_string($subjectName);
		$whereFrom = whereEmailSql("from_field",$subjectName,$mysqli);		
	}		
	if (strlen($objectName) > 0){
		$objectName = $mysqli->real_escape_string($objectName);
		if ($objectType == "to")		
			$to = whereEmailSql("to_field",$objectName,$mysqli);
		elseif 	($objectType == "cc")
			$cc = whereEmailSql("cc_field",$objectName,$mysqli);	
		elseif 	($objectType == "bcc")
			$bcc = whereEmailSql("bcc_field",$objectName,$mysqli);
		else{
			$to = whereEmailSql("to_field",$objectName,$mysqli);
			$cc = str_replace("to_field","cc_field",$to);
			$bcc = str_replace("to_field","bcc_field",$to); 			
		}
	}
	
	if ($objectType == "to"){
		$arrType[] = "to_field";
	}
	elseif ($objectType == "cc")
		$arrType[] = "cc_field";
	elseif ($objectType == "bcc")
		$arrType[] = "bcc_field";	
 	else
 		$arrType = array("to_field","cc_field","bcc_field");
 	$resArr = array(); 
 	if (sizeof($arrType) > 1)
 		$limit = intval($limit/3);	
 		
 	if (strlen($whereFrom) > 0)
 		$whereFrom = " AND " . $whereFrom;
 	foreach ($arrType as $v){	
 		if ($v == "to_field" && strlen($to) > 0){
 			 $where = $whereFrom . " AND $to";
 		}
 		if ($v == "cc_field" && strlen($cc) > 0){
 			 $where = $whereFrom . " AND $cc";	
 		}
 		if ($v == "bcc_field" && strlen($bcc) > 0){
 			 $where = $whereFrom . " AND $bcc";	
 		}
 		if (empty($where)){
 			$where = $whereFrom;
 		}
 		
		if(!is_null($dates_arr)){
		    $date_sql = "AND $v.docId IN ".$dates_arr["date_sql"];	    
		}
	    else{
	        $date_sql = "";
	    }
	    
	    if(!$isAdmin){
	    }
	    $user_sql = !$isAdmin ? "  files.username='$username'" : "  TRUE ";
	    
	    $saved_sql = " TRUE "; //empty($saved_sql) ? " TRUE " : " from_field.docId IN($saved_sql) ";
	    
	    $qery = "SELECT from_field.value AS f,$v.value AS t,COUNT(DISTINCT from_field.docId) AS c,GROUP_CONCAT(from_field.docid) AS docs FROM from_field,files,$v  $savedsearchesTable WHERE $saved_sql AND from_field.docId=$v.docId AND from_field.docId=files.id $date_sql AND $user_sql $where $savedsearchesSql GROUP BY from_field.value,$v.value ORDER BY c DESC LIMIT $limit";
	    $res = sqlQuery($mysqli, $qery);		
		if ($res){
			while ($row = $res->fetch_object()){	
			    $dates = getMinMaxDatesPerDocs($mysqli, $row->docs);
				$results[] = array("subject" => $row->f, "object" => $row->t, "count" => $row->c, "dates" => $dates["min"].'-'.$dates["max"], "subject_table" => "email", "object_table" => "email","relation_type" => $v);
			}
		}		
 	}
	

 	return $results;	
}

function listRelations($mysqli, $type, $subjectType, $objectType, $groupBy, $savedsearch, $subjectName=null, $objectName=null, $relationList=null, $relationType=null, $ignoreSubject=null, $lastSearch=null,$pastPeriod=null, $connections = null, $isAdmin=null,$username=null, $saved_sql = null)
{
	$response = [];
	//prevent SQL injection
// 	if ((preg_match("/[^\w_\-]/", $subjectType ?? "") || preg_match("/[^\w_\-]/", $objectType ?? "")
// 			 || preg_match("/[^\w_\-,]/", $groupBy)))
// 		die("invalid argument passed to listRelations");
			 
    //savedSearch attached
	$savedsearchesid = NULL;
	if ($savedsearch || strlen($lastSearch ?? "") > 0)
	{
	    $savedsearchesid = getLastSavedSearchId($mysqli, $username, $savedsearch ?? $lastSearch);
	}
	
	//A - COREFS
	if ($type == "coRefs" || $relationType  == "coRefs" || (!empty($relationType) && $relationType[0] == "coRefs"))
	{
	    $response["type"] = "coRefs";
	    //if we searched for all co-refs
	    if(empty($subjectType) || empty($objectType)){

	        $results = getMultiTopCoRefs($mysqli, $subjectType, $objectType, $connections ?? 500, $savedsearchesid, $username,$isAdmin, $subjectName,  $objectName, false,  false,  false,$pastPeriod, $saved_sql);
	        foreach ($results as $res)
	        {
	            foreach ($res["data"] as $row)
	            {
	                $response["data"][] = ["subject" => $row["name1"], "object" => $row["name2"], "count" => $row["coRefs"], "dates" => $row["dates"], "relation_type"=> "coRefs","relation_parent"=> "coRefs", "subject_table" => $res["from_to"][0], "object_table" => $res["from_to"][1]];
	            }
	        }
	        
	    }
	    else{
	        
	        $results = getTopCoRefs($mysqli, $subjectType, $objectType, $connections ?? 500, $savedsearchesid, $username,$isAdmin, $subjectName,  $objectName, false,  false,  false, $pastPeriod, $saved_sql);
    		foreach ($results as $row)
    		{
    		    $response["data"][] = ["subject" => $row["name1"], "object" => $row["name2"], "count" => $row["coRefs"], "relation_type"=> "coRefs","relation_parent"=> "coRefs", "subject_table" => $subjectType, "object_table" => $objectType];
    		}
	    }
	    usort($response["data"], function ($item1, $item2) {
            return $item2['count'] <=> $item1['count'];
        });
	    //if corefs_fs - take the data and download to user
	    $corefs_fs = getSystemSettingsProp($mysqli, "corefs_fs");
	    if($corefs_fs && !empty($response["data"])){
	        global $sharedFolder;
	        $date = date('m_d_Y_h_i_s', time());    
	        $path = $sharedFolder . DIRECTORY_SEPARATOR . 'exported_files' . DIRECTORY_SEPARATOR . $date."_corefs";
            mkdir($path);
	        $fp = fopen($path.".csv", 'w');

	        $first = true;
	        foreach ($response["data"] as $fields) {
	            if($first){
	                $first = false;
	                $keys = array_keys($fields);
	                fputcsv($fp, $keys);
	            }
	            fputcsv($fp, $fields);
	        }
	        fclose($fp);
	        
	    }
	    
	}
	//B - relation
	
	else if($type == "relation"){
		$response["type"] = "relation";
		
		$response["data"] = getTopRelation($mysqli, $relationType, $subjectType, $objectType, $groupBy, $savedsearchesid, $subjectName, $objectName, $relationList, $ignoreSubject,$isAdmin,$username,null,$pastPeriod, $saved_sql);
		
	}
	
	else if($type == "relation_v2"){
	    
	    $response = getTopRelation($mysqli, $relationType, $subjectType, $objectType, $groupBy, $savedsearchesid, $subjectName, $objectName, 500, $relationList, $ignoreSubject,$isAdmin,$username,null,$pastPeriod, $saved_sql);
	    
	}    
	//D - Corresponded
	else if($type == "corresponded"){
		$response["type"] = "corresponded";
		$response["data"] = getTopCorresponded($mysqli, $groupBy, $savedsearchesid, $subjectName, $objectName,$pastPeriod,$isAdmin,$username, $objectType, 500, $saved_sql);
	}
	
    
	return $response;
}




function getManageEntitiesArray($search,$returnStr=false){
	$connection = sqlCreateConnection();
	$query = "SELECT aggregatedname,aggregatedmanual FROM entitiesmanage WHERE name='$search'";
	$res = sqlQuery($connection,$query);
	$typeArr = array();
	while ($obj = $res->fetch_object()){		
		$e = explode(";",$obj->aggregatedname);
		foreach ($e as $v){
			if (strlen($v) > 0)
				$typeArr[$v] = $search;
		}
		$e = explode(",",$obj->aggregatedmanual);
		foreach ($e as $v){
			if (strlen($v) > 0)
				$typeArr[$v] = $search;
		}		
	}	
	if ($returnStr){
		$str = "";
		$first = true;
		foreach ($typeArr as $k => $v){
			$k = $connection->real_escape_string($k);
			if ($first){				
				$str = "'$k'";
				$first = false;
			}
			else
				$str .= ",'$k'";
		}
		sqlClose($connection, "getManageEntitiesArray");
		return $str;
	}
	sqlClose($connection, "getManageEntitiesArray");
	return $typeArr;
}

function getSlotKB($search, $table){
	global $slotToCaption;
	$kbSlots = array();
	$kbOrigSlots = getKbInstanceSlots($search, str_replace("_", "-", ucfirst($table)), true);
	if (!empty($kbOrigSlots))
	{
		foreach ($slotToCaption as $slot => $arr){
		    $label = $arr['label'];
		    $order = $arr['order'];
			if (array_key_exists($slot, $kbOrigSlots)){
				if (strlen(trim($kbOrigSlots[$slot])) > 0){
				    $kbSlots[] = ['label' =>$label, 'order' => $order, 'value'=> $kbOrigSlots[$slot]];
				}
			}
		}
	}	
	return $kbSlots;
}

function getSlotD($connection, $search, $table, $kbSlots, $aggArr = null){
    if ($aggArr != null){
        for($i = 0; $i<count($aggArr); $i++)
        {
            $aggArr[$i] = $connection->real_escape_string($aggArr[$i]);
        }
        
        $esc_val = "'" . implode("','", $aggArr) . "'";
    }else{
        $gArr = getMangeEntities($connection, $table, $search, false, true);
        if (sizeof($gArr["rows"]) < 1){
            $gArr["rows"] = $search;
        }
        $first = true;
        foreach ($gArr["rows"] as $v){
            if ($first){
                $esc_val = "'" . $connection->real_escape_string($v) . "'";
                $first = false;
            }else
                $esc_val .= ",'" . $connection->real_escape_string($v) . "'";
        }
    }
    
    global $slotToCaption;
    $sql = "SELECT DISTINCT ";
    $first = true;
    foreach ($slotToCaption as $k => $v){
        $e = str_replace("-", "_", $k);
        if ($first){
            $sql .= "$e";
            $first = false;
        }
        else
            $sql .= ",$e";
    }
    if (strlen($table) > 0)
        $sql .= " FROM slotdata WHERE conceptName=\"$table\" AND caption IN ($esc_val)";
        else
            $sql .= " FROM slotdata WHERE caption=\"$search\"";
            write_to_log("PERMORMENCE", "getSlotD - $sql");
            $res = sqlQuery($connection,$sql);
            if(!empty($res)){
                $db_slot = array();
                while($row = $res->fetch_object()){
                    foreach ($slotToCaption as $k => $arr){
                        $v = $arr['label'];
                        $k = str_replace("-", "_", $k);
                        if ($row->$k != null){
                            if ($k == "modifier"){
                                $v = "Title";
                                $row->$k = trim(trim($row->$k,"("),")");
                            }
                            if ($k == "title"){
                                $d = 1;
                            }
                            if (array_key_exists($v, $db_slot)){
                                if (array_key_exists($row->caption, $db_slot[$v])){
                                    if (strpos($db_slot[$v][$row->caption], $row->$k) === false)
                                        $db_slot[$v][$row->caption] .= ", " . $row->$k;
                                }else{
                                    $db_slot[$v][$row->caption] = $row->$k;
                                }
                            }
                            else
                                $db_slot[$v][$row->caption] = $row->$k;
                        }
                    }
                    
                }
            }
            $db_slot_new = array();
            foreach ($db_slot as $k => $v){
                foreach ($v as $c => $caption){
                    if(!isset($db_slot_new[$k])){
                        $db_slot_new[$k] = "";
                    }
                    $db_slot_new[$k] .= "$caption - ($c); ";
                }
            }
            $db_slot = $db_slot_new;
            //delete kb exsisting slots from db
            $db_array = array();
            foreach ($db_slot as $k => $v){
                if ($kbSlots && array_key_exists($k,$kbSlots)){
                    if (strpos($kbSlots[$k],$v) !== false)
                        //if ($v != $kbSlots[$k])
                            $db_array[$k] = $v;
                }else
                    $db_array [$k] = $v;
            }
            //making sure that the order will be Gender, Ethnicity, Title
            $order_arr = array("Title", "Ethnicity", "Gender");
            foreach($order_arr as $key){
                if(!array_key_exists($key, $db_array)){
                    continue;
                }
                $curr_val = $db_array[$key];
                unset($db_array[$key]);
                $db_array = array($key => $curr_val) + $db_array;
            }
            //End of patch
            
            return $db_array;
            
}


function getSlotD_v2($connection, $search, $table, $kbSlots, $aggArr = null){
	if ($aggArr != null){
		for($i = 0; $i<count($aggArr); $i++)
		{
			$aggArr[$i] = $connection->real_escape_string($aggArr[$i]);
		}
		
		$esc_val = "'" . implode("','", $aggArr) . "'";
	}else{
		$gArr["rows"] = array($search);
	 
		$first = true;
		foreach ($gArr["rows"] as $v){
			if ($first){
				$esc_val = "'" . $connection->real_escape_string($v) . "'";
				$first = false;
			}else 
				$esc_val .= ",'" . $connection->real_escape_string($v) . "'";			
		}	
	}
	
	global $slotToCaption;	
	$sql = "SELECT DISTINCT ";
	$first = true;
	foreach ($slotToCaption as $k => $v){
		$e = str_replace("-", "_", $k);
		if ($first){
			$sql .= "$e";
			$first = false;
		}
		else
			$sql .= ",$e";
	}	
	$concepts = [$table];
	$sub_sql = "SELECT DISTINCT sub_concept_name FROM `sub_tables` WHERE concept_name ='".ucfirst(str_replace('_', '-', $table))."'";
	$res = sqlQuery($connection, $sub_sql);
	if(!empty($res)){
	    while( ($row = $res->fetch_object()) != null){
	        $concepts[] = strtolower(str_replace('-', '_', $row->sub_concept_name));
	    }
	}
	$concepts_str = implode("','", $concepts);
	$key_cond = " conceptName IN ('$concepts_str')";
	
	if (strlen($table) > 0)
		$sql .= " FROM slotdata WHERE $key_cond AND caption IN ($esc_val)";
	else 
		$sql .= " FROM slotdata WHERE caption=\"$search\"";																				
    write_to_log("PERMORMENCE", "getSlotD - $sql");	   						
	$res = sqlQuery($connection,$sql);
	if(!empty($res)){
		$db_slot = array();
		while($row = $res->fetch_object()){
			foreach ($slotToCaption as $k => $arr){
			    $label = $arr['label'];
			    $order = $arr['order'];
		
			    //get only -text fields - new patch
			    $allowed_slots = ["full-name",
			        "given-name",
			        "family-name",
			        "ethnicity",
			        "gender",    
			        "surname",
			        "country-of-birth",
			        "city-of-birth",
			        "country-of-residence",
			        "city-of-residence",
			        "title",
			        "organizational-affiliation"];
			    if(strpos($k, '-text') == false && !(in_array($k, $allowed_slots))){
			        continue;
			    }
				$k = str_replace("-", "_", $k);

				if ($row->$k != null){
					if ($k == "modifier"){
					    $label = "Title";
						$row->$k = trim(trim($row->$k,"("),")");
					}	
					if ($k == "title"){
						$d = 1;
					}						
			
					if (!empty($db_slot[$label])){
					    if(strpos($db_slot[$k], $row->$k) === false){
					        $db_slot[$k] .= ", " . $row->$k;	
					    }
					}													
					else{
					    $db_slot[$k] = $row->$k;	
					}
				}
			}	
		}
	}	

	//delete kb exsisting slots from db	
	$db_array = array();
	foreach ($db_slot as $k => $v){
	    $label = $slotToCaption[str_replace("_", "-", $k)]['label'];
	    $order = $slotToCaption[str_replace("_", "-", $k)]['order'];
	    $v = str_replace('(ref)', '', $v);
		if ($kbSlots && array_key_exists($k,$kbSlots)){
			if (strpos($kbSlots[$k],$v) !== false){
			    $db_array[] = ['label'=>$label, 'order'=>$order, 'value'=> $v];
			}
		}else{ 
		    $db_array[] = ['label'=>$label, 'order'=>$order, 'value'=> $v];
		}
	}

	
	return $db_array;
		
}

function getGeneralInfo($connection, $username){
    global $skipDuplicate;
    $ssid = getLastSavedSearchId($connection, $username);
	$query = "SELECT id,label FROM savedsearch WHERE id=$ssid";
	$res = sqlQuery($connection,$query);
	if ($res){
		$obj = $res->fetch_object();
		$label = $obj->label;	
		$savedsearchId = $obj->id;
	}
	if ($savedsearchId){				
		if ($skipDuplicate)
			$query ="SELECT SUM(count_doc) as i FROM unique_files";	
		else 
			$query ="SELECT COUNT(id) as i FROM files";
		$res = sqlQuery($connection,$query);
		if(!empty($res)){          		
			$row = $res->fetch_object();
			$countDocument = $row->i;
		}
		else 
			$countDocument = "NA";
	
		$lastSearch = "SELECT docid FROM savedsearchid WHERE id=$savedsearchId";	
		if ($skipDuplicate)
			$query = "SELECT SUM(count_doc) AS sum FROM savedsearchid,unique_files WHERE savedsearchid.id=$savedsearchId AND unique_files.id=savedsearchid.docId";
		else 
			$query = "SELECT COUNT(docid) AS sum FROM savedsearchid WHERE id=$savedsearchId";
		$res = sqlQuery($connection,$query);
		
		$numberDocument = $res->fetch_object()->sum;
		if ($numberDocument == 0){	
			$response["search"] = $label;
			return $response;
		}
			
		$precentage = intval(($numberDocument/$countDocument)*100);
		if ($precentage == 0 && $numberDocument > 0){
			$precentage = "less than 1";
		}
		$response["search"] = $label;	
		$response["numberDocument"] =  $numberDocument;
		$response["precentage"] = $precentage;
		return $response;		
	}	
}

function addArraysToParentArray(&$existingArray, $extraData){
	foreach ($extraData as $key => $value) {
		$existingArray[$key] = array(
				'entity' => $value,
				'table' => $key
		);		
	};
};


function getMangeEntities($mysqli, $searchConceptInput,$name,$returnOrgText=false,$returnArrNames=false){
	global $extraEntityTypesArray;
	$present_existed = false;
	$where = "";
	$conceptDataArray = array(
		'events' => array(
				'entity' => 'event',
				'table' => 'event'
		),
	    
	    'event' => array(
	        'entity' => 'event',
	        'table' => 'event'
	    ),
		'places' => array(
				'entity' => 'place_object',
				'table' => 'place_object'
		),
	    'place' => array(
	        'entity' => 'place_object',
	        'table' => 'place_object'
	    ),
		'org' => array(
				'entity' => 'org',
				'table' => 'organizational_identity'
		),
	    'organisations' => array(
	        'entity' => 'org',
	        'table' => 'organizational_identity'
	    ),
	    'organisation' => array(
	        'entity' => 'org',
	        'table' => 'organizational_identity'
	    ),
		'persons' => array(
				'entity' => 'person_object',
				'table' => 'person_object'
		),	
	    'person' => array(
	        'entity' => 'person_object',
	        'table' => 'person_object'
	    ),	
		'place_object' => array(
				'entity' => 'place_object',
				'table' => 'place_object'
		),
		'organizational_identity' => array(
				'entity' => 'org',
				'table' => 'organizational_identity'
		),
		'person_object' => array(
				'entity' => 'person_object',
				'table' => 'person_object'
		),
                'idea' => array(
                    'entity' => 'Abstract-idea-entity',
                    'table' => 'abstract_idea_entity'
                )
	);
	// In case there are extra entities, we add them to $conceptDataArray
	addArraysToParentArray($conceptDataArray, $extraEntityTypesArray);
	
	// Now we input the search concept into the handy builder function,
	// to build the search concept name;
	$searchConceptEntity = $conceptDataArray[$searchConceptInput]['entity'] ?? $searchConceptInput ?? null;
	
	// Now we input the search concept into the handy builder function,
	// to build the search concept table;
	$searchConceptTable = $conceptDataArray[$searchConceptInput]['table'] ?? $searchConceptInput ?? null;
	
	$selected = []; //the checked instances
/**
* We check whether we are dealing with a person request.
* If we are, we must send the text to Intuscan for analysis.
* This will allow a more informed search, e.g. Nicknames, abbreviations etc
* such as searching for both Dick and Richard in the Database.
* See the definiton of $searchConceptEntity above;
*/
	//start process 
	
	$esc_name = $mysqli->real_escape_string($name);
	$sql = "SELECT aggregatedName FROM entitiesmanage WHERE NAME='$esc_name' AND entity IN ('$searchConceptEntity','$searchConceptInput')";
	$rs = sqlQuery($mysqli, $sql);
	
	//person_object case
	if ($searchConceptEntity === "person_object"){	
	    global $force_per_var_db;
	    $perVars = $force_per_var_db ? getPersonVariationsDB($mysqli, $name) : getPersonVariations($mysqli, $name);
	    if(empty($perVars) && !$force_per_var_db){
	        write_to_log("ERROR", "perVars is empty for $name, using getPersonVariationsDB");
	        $perVars = getPersonVariationsDB($mysqli, $name);
	    }
		if ($returnOrgText)
		{
			$perString = "";
			$first = true;						
		 	foreach ($perVars as $k => $v){
		 		$n = $mysqli->real_escape_string($k);		 		
		 		if ($first){
		 			$perString = "'$n'";
		 			$first = false;
		 		}else{
		 			$perString .= ",'$n'";	
		 		}
		 	}
		 	if (strlen($perString) > 0){
				//add orgtext
				$sql = "SELECT DISTINCT orgText FROM person_object WHERE NAME IN($perString)";
				$sql.= " AND (orgText IS NOT NULL AND orgText !='')";
				$res = sqlQuery($mysqli, $sql);
				if (!empty($res)){
				    while ($o = $res->fetch_object()){
		 			    if (!array_key_exists($o->orgText, $perVars)){
		 					$perVars[$o->orgText] = 1;		
		 			    }
		 			}
				}
			}
		}
	
	 	if (!empty($rs)){
	 		$r = $rs->fetch_object();
	 		if (!empty($r) && strlen($r->aggregatedName)){
	 			$e = explode(";",$r->aggregatedName);
	 			foreach ($e as $v){
	 			    if (!array_key_exists($v, $perVars)){
	 					continue;
	 			    }			
	 				$selected[$v] = true;
	 			}
	 		}
	 	}
		$uniqArr = array();
	 	foreach ($perVars as $k => $v){
	 		if (!array_key_exists(strtolower($k), $uniqArr)){
	 			//clean duplicate names casesensetive 	
	 			$uniqArr[strtolower($k)] = true;
	 			$names_array[] = $mysqli->real_escape_string($k);	
	 		}
	 	}
	 
	 	
	 	$response["rows"] = getNamesCount($mysqli, $names_array ?? null, 'person_object', $selected);
	 	
	}
	//NONE person_object case
	// In the case where we are not dealing with a person request, we rely on the SQL "LIKE"
	// to search for a closely matching entity.
	
	elseif ($searchConceptEntity || !$searchConceptTable){  
		if (!empty($rs)){
		    $name_column = ($searchConceptTable =='organizational_identity') ? 'clean_name' : 'name';
		               		    
		    $where = " NAME = '$esc_name' OR $name_column LIKE ('% $esc_name') OR $name_column LIKE ('$esc_name %') ";
// 		    . " OR orgText = '$esc_name' OR orgText LIKE ('% $esc_name') OR orgText LIKE ('$esc_name %')";	    
			$r = $rs->fetch_object();
			if (!empty($r) && strlen($r->aggregatedName)){
				$e = explode(";",$r->aggregatedName);
				foreach ($e as $aggName){
					$selected[$aggName] = true;
				}
				//in this case we do not need to look for instances in the searchTable, we just need the data from the aggName in EM
				$present_existed = true;	
			} 
			else{
				$e = explode("^", $name);
				if (sizeof($e) > 1){
					$sql = getSqlStetment($e,$mysqli);
				}
				else{
					$sql = "SELECT DISTINCT name FROM $searchConceptTable WHERE $where";
				}
			}
		}else{
			$e = explode("^", $name);
			if (sizeof($e) > 1){
				$sql = getSqlStetment($e,$mysqli);
			}
			else{
				$sql = "SELECT DISTINCT name FROM $searchConceptTable WHERE $where";
			}
		}
		
		if(!$present_existed){
		 
		  $res = sqlQuery($mysqli, $sql);
		}
		$rows_arr = array();
		
		if (!empty($res)){
			$uniqArr = array();
			while ($row = $res->fetch_object()) {
			    $names_array[] = $mysqli->real_escape_string($row->name);
			}
			$response["rows"] = getNamesCount($mysqli, $sql, $searchConceptTable, $selected);
			
		}
		elseif($present_existed){
		    $uniqArr = array();
		    foreach($selected as $k => $v){
		        $names_array[] = $mysqli->real_escape_string($k);
		    }
		    $rows_arr ['rows'] = getNamesCount($mysqli, $sql, $searchConceptTable, $selected);
	
		}
	}
	
	//If it's not a person, event, org or place
	else {
		$response["rows"][] = array();
	}
	
	//IF $returnArrNames is TRUE
	if ($returnArrNames){
		$checkedArr = array();
		$allArr = array();
		if(!empty($response))
		foreach ((array)$response["rows"] as $r){
			$name = $r["name"];
			if ($r["checked"]){
				$checkedArr[] = $name;
			}
			$allArr[] = $name;
		}
		if (sizeof($checkedArr) > 0){
			//use the aggregeted only			
			$response["rows"] = $checkedArr;
		}
		else{
			$response["rows"] = $allArr;		
		}
	}

	return $response ?? null;
}

//gets count of names  in specific tables
function getNamesCount($mysqli, $names_array_sql, $table, $selected){
    if(empty($names_array_sql)){
        return null;
    }
    if(is_array($names_array_sql)){
        $names_array_sql = "'".implode("','",$names_array_sql)."'";
    }
    $q = "SELECT name,  count(id) as NUM FROM $table WHERE name in($names_array_sql) GROUP BY NAME ORDER BY NUM DESC";
    $res = sqlQuery($mysqli, $q);
    $occ_name = $res->fetch_all();
    
    foreach($occ_name as $name_element){
        $res_rows[] = array("numRef" => $name_element[1], "name" => $name_element[0],"checked" => array_key_exists( $name_element[0], $selected));
    }

    return $res_rows ?? null;
}

// function getEntitiesRows($mysqli, $searchConceptTable, $selected, $name){
//     $uniqArr = array();
//     $temp_arr = array();
//     $curr_name = $mysqli->real_escape_string($name);
//     $q = "SELECT COUNT(id) as id FROM $searchConceptTable WHERE name='$curr_name' OR orgText='$curr_name'";
//     $rs = sqlQuery($mysqli, $q);
//     if (!empty($rs)){
//         $r = $rs->fetch_object();
//         if (!array_key_exists(strtolower($name), $uniqArr)){
//             $uniqArr[strtolower($name)] = true;
//             $temp_arr[] = array("numRef" => $r->id, "name" => $name, "checked" => array_key_exists($name, $selected));
//         }
//     }
//     return $temp_arr;
// }

function getSqlStetment($e,$mysqli){
	$sql = "SELECT DISTINCT name FROM organizational_identity WHERE ";
	$first = true;
	foreach ($e as $v){
		$v = $mysqli->real_escape_string($v);
		if ($first){
			$first = false;
			$sql .= " NAME LIKE LOWER('%$v%')";
		}else{
			$sql .= " OR NAME LIKE LOWER('%$v%')";
		}
	}
	return $sql;
}

function getTables($mysqli){
	global $dropdownArr;
	return getTablesExcept($mysqli, $dropdownArr);
}

function getFlavorClassArray($mysqli){
    require_once 'settings.php';  
    global $username;
    $concepts_arr = array();
    $settings_class =  new Settings($mysqli, $username);
    $allConceptArrDB = $settings_class->getAllConceptsArr();
    $allConceptFlavor =  $settings_class->getActiveFlavor();
    
    $concept_class =  new AllConceptsData($mysqli, $allConceptArrDB, $allConceptFlavor, $username);
    $flavorDataArr = $concept_class->getFlavorDataArr($mysqli);
    foreach($flavorDataArr as $element){
        $tableName = ontologyName2TableName($element["name"]);
        $concepts_arr[] = $tableName;
    }
    return $concepts_arr;
}

function getTablesExcept($mysqli, $restrictedTables)
{
    global $allConceptArr, $allConceptFlavor, $db_name, $username, $include_get_tables;
    $isMemSQL = sqlGetConnectionDetails()["isMemSQL"];
    $concepts_arr = array();
    $table = array();
    $concepts_arr = getFlavorClassArray($mysqli);
    
    $query = "SELECT * FROM `information_schema`.`TABLES` WHERE
        `TABLE_SCHEMA` = '$db_name'";
    
			$res = sqlQuery($mysqli, $query);
	while($obj = $res->fetch_object()){
	    $table_name = $obj->TABLE_NAME;
	    if (in_array($table_name, $concepts_arr) || in_array($table_name, $include_get_tables)){
	        if (!array_key_exists($table_name,$restrictedTables) || in_array($table_name, $include_get_tables))
	            $table[$table_name] = ($table_name);
	    }
	}
	return $table;
}

function getCocnceptsExcept($mysqli,  $restrictedTables)
{
    global $db_name, $username;
	$concepts_arr = array();
	$isAdmin = getUserPermission(CAN_SHOW_ALL_USER) ? 1 : 0;
	$user_sql = $isAdmin ? "TRUE" : "username='$username'";
	require_once 'settings.php';
	$settings_class =  new Settings($mysqli, $username);
	
	$concepts_arr = $_REQUEST["all"] ? $settings_class->getAllConceptsArr(true) : getFlavorClassArray($mysqli);
	
 	$res = sqlQuery($mysqli, "SHOW TABLES");
 	$tables_arr =  $res->fetch_all ();
 	
 	foreach($tables_arr as $key=>$table_arr){
 	    $table_name = $table_arr[0];
	    if (in_array($table_name, $concepts_arr) && (!array_key_exists($table_name,$restrictedTables))){
	        //gets the number of items in the specific tables
	        $internal_res = sqlQuery($mysqli, "SELECT id  from $table_name WHERE $user_sql LIMIT 1");
	        $row = $internal_res->fetch_object();
	        $tables[$table_name] = !empty($row->id) ? 1 : 0; 
		}
	}
	return $tables;
}

function tableNameToPretty($str){
    $str = str_replace(array('_','-'),' ',($str));
    return ($str);
}

function getDetailsForSmartSearch($mysqli, $param_json){
    global $dropdownArr, $all_search_pies;
	//for saving data to manageEntities	
    $tabels = getTables($mysqli);
	foreach ($dropdownArr as $k=>$v){
        $tabels[$v] = $k;
    }
    $param = json_decode($param_json, true);
    
    $transferArr = array("searchPerson" => "person_object", "searchOrganization" => "organizational_identity",
        "searchPlaces" => "place_object");
	
	$regular = array();
	$tab_i = 0;
        $entityParmeters = [];
	foreach ($param as $tab => $arr){	
	    if(isset($arr["regular"]))
		foreach ((array)$arr["regular"] as $t => $n){		
		    if (!is_array($n) && strlen($n) > 0 || array_key_exists($t, $all_search_pies)){
				if ($t == "searchPerson" || $t == "searchOrganization" || $t == "searchPlaces"){
					$entityParmeters[$transferArr[$t]][$n] = $n;
				}
				if ($t == "searchSentiment"){
					$e = explode("^", $n);
					$type = $e[1];
					$value = $e[2];
				    $entityParmeters[$type][$value] = $value;
				}
				if(array_key_exists($t, $regular)){
					$searchParameters[$tab_i]["regular"][$t] .= "&$n";
				}
				else {
					$searchParameters[$tab_i]["regular"][$t] = "$n";	
				}
			}elseif (sizeof($n) > 0){
			    if ( in_array($t,array("searchOther", "searchRelation"))){
					$searchParameters[$tab_i]["regular"][$t] = $n;
				}
			}			
		}
		$arrName = array();
		if(isset($arr["smart"]))
		foreach ((array)$arr["smart"] as $k_n => $v_n){								
			$first_union = true;			
			foreach ($v_n as $k => $v){				
				if (strlen($v) > 0){				
					if ($k == "place_object" || $k == "organizational_identity" || $k == "person_object"){					
					    $entityParmeters[$k][$k_n] = $v;
					}
					
					if ($first_union)										
						$first_union = false;
	
					
					$v = explode("^",$v);
					$val = "";
					$f = false;
					$saveVal = array();
					foreach ($v as $vv){
						if (strlen($vv) > 0){
							if ($f)
								$val .= ",";							
							else{
								$f = true;					
							}
							$val .= "'" . $mysqli->real_escape_string($vv) . "'";
							$saveVal[] = $vv;
						}
					}		
					$tableAndCaption[][$k] = $val;
					$arrName[$k_n][$k] = $saveVal;
					
					if (array_key_exists($k, $dropdownArr)){
					    $name_sql = "value IN ($val)";
					}
					else{
					    $name_sql = "name IN ($val) or orgText in ($val)";	
					}
				}			
				$name_pred = !empty($val) ? $name_sql : 'TRUE';;
				$searchParameters[$tab_i]["smart"][] = " SELECT DISTINCT docId FROM $k WHERE $name_pred";	
						
			}
			//insert search to manageentities table
			if (sizeof($arrName) > 0){
				foreach ($arrName as $name => $v){
					//delete old one
					$res = $mysqli->query("DELETE FROM entitiesmanage WHERE name=\"$name\" AND entity='smart'");
					$conceptArr = array();
					foreach ($v as $k => $vv){
						$conceptArr[$name][$k]["key"] = $k;		
					
						$conceptArr[$name][$k]["value"][$name] = $vv;
						
						$caption = array_search($k,$tabels);
						if (strlen($caption) < 1)
							$caption = str_replace("_"," ",$k);
						$conceptArr[$name][$k]["caption"] = $caption;
					}
					$json = json_encode($conceptArr);
					$val = $mysqli->real_escape_string($json);
					$sql = "INSERT INTO entitiesmanage (entity,name,aggregatedName)
							VALUES (\"smart\",\"$name\",\"$val\")";
					$res = $mysqli->query($sql);
				}
			}
		}
		$tab_i++;
	}	
	
	$entityParmetersArr = array();
	foreach ($entityParmeters as $t => $arr_n)
		foreach ($arr_n as $k => $v)	
			if (array_key_exists($t, $entityParmetersArr))
				$entityParmetersArr[$t] .= "&$v";
			else 
				$entityParmetersArr[$t] = "$v";
			
	$ret["searchParameters"] = $searchParameters ?? null;
	$ret["entityParmetersArr"] = $entityParmetersArr ?? null;
	$ret["tableAndCaption"] = $tableAndCaption ?? null;
	return $ret;
}

function getMonitor($sql_time_interval, $dirToMonitor="c:\\tmp\\monitor", $dirJsonReport="c:\\tmp\\monitor_json", $jsonFile="jsonReport.txt", $max_count_files=10){		
	$ret["files"] = monitor_folder($dirToMonitor);	
	$content = file_get_contents($dirJsonReport . "\\" . $jsonFile);
	$response = json_decode($content,true);
	
	$error = "";
	if ($ret["files"]["count_files"] > $max_count_files)
		$error = "count file (" . $response["count_files"] . ") > max file " . $max_count_files;		
	if ($response["files"]["first_date"] == $ret["files"]["first_date"])							
		$error .= " file first date (" . date("F d Y H:i:s",$response["first_date"]) . ") stuck";
	if ($response["files"]["last_date"] == $ret["files"]["last_date"])							
		$error .= " file last date (" . date("F d Y H:i:s",$response["last_date"]) . ") stuck";	
	
	if (strlen($error) > 0){	
		require_once("tools//monitor//gmail.php");
		$s = sendEmail($error);
	}
		
	$mysqli = sqlCreateConnection("getMonitor");
	$query = "SELECT MIN(id) as min_id FROM files WHERE scan_time > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL $sql_time_interval HOUR)";
	$res_sql = sqlQuery($mysqli, $query);
	if ($res_sql){
		$obj = $res_sql->fetch_object();
		if (!empty($obj))
			$ret["db"]["min_id"] = $obj->min_id;
	}else{
		$ret["db"]["min_id"] = "sql error";
	}	
	sqlClose($mysqli,"getMonitor");	
	$mysqli = sqlCreateConnection("getMonitor");
	$query = "SELECT
			COUNT(f.id) AS latestIds,
			COUNT(f.timeStamp) AS latestTimeStamps,
			COUNT(f.scan_time) AS latestScan_times,
			COUNT(fb_posts.docId) AS latestFacebooksPosts,
			COUNT(fb_comments.docId) AS latestFacebooksComments,
			COUNT(people.docId) AS latestPeople
			FROM files f
			LEFT JOIN t_type fb_posts
			ON fb_posts.docId = f.id AND fb_posts.value = 'facebook'
			AND SUBSTRING(f.filename, 1, 4) = 'post'
			LEFT JOIN t_type fb_comments
			ON fb_comments.docId = f.id AND fb_comments.value = 'facebook'
			AND SUBSTRING(f.filename, 1, 7) = 'comment'
			LEFT JOIN person_object people
			ON people.docId = f.id
			WHERE
			f.id > (SELECT MIN(id) FROM files WHERE scan_time > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL $sql_time_interval HOUR))";
	$res_sql = sqlQuery($mysqli, $query);
	if ($res_sql){
		$obj = $res_sql->fetch_object();
		if (!empty($obj)){		
			$ret["db"]["latestIds"] = $obj->latestIds;
			$ret["db"]["latestTimeStamps"] = $obj->latestTimeStamps;
			$ret["db"]["latestScan_times"] = $obj->latestScan_times;
			$ret["db"]["latestFacebooksPosts"] = $obj->latestFacebooksPosts;
			$ret["db"]["latestFacebooksComments"] = $obj->latestFacebooksComments;
			$ret["db"]["latestPeople"] = $obj->latestPeople;
		}
	}else{
		$ret["db"]["latestIds"] = "sql error";
	}
        sqlClose($mysqli, "getMonitor");
	return $ret;
}

function monitor_folder($dir){//, $dir_json, $jsonFile, $max_count_files = 10){			
		$files = scandir($dir);
		$count_files = 0;
		$last_date = 0;
		$first_date = 0;
		foreach ($files as $f){
			if (file_exists($dir . "\\" . $f) && $f != "." && $f != ".."){												
				$count_files++;
				if (filemtime($dir . "\\" . $f) > $last_date)
					$last_date = filemtime($dir . "\\" . $f);
				if (filemtime($dir . "\\" . $f) < $first_date || $first_date == 0)
					$first_date = filemtime($dir . "\\" . $f);
			}	
		}		
				
		$ret["count_files"] = $count_files;
		$ret["last_date"] = date("F d Y H:i:s", $last_date);
		$ret["first_date"] = date("F d Y H:i:s", $first_date);		
		return $ret;
	}
//checks if the column exists in the DB table
//$table - the table name | $field - the field name | $column - the column name
function columnExists($mysqli, $table,$field,$column){
    $columns = sqlQuery($mysqli, "show columns from $table");
    if ($columns){
        while ($obj = $columns->fetch_object()){
            $f =$obj->$field;
            if($obj->$field==$column){
                return true;
            }
        }
    }
    return false;
}

//get the pastPeriod value from the dashboard database
function getPastPeriod($mysqli, $username) {
    $response = null;
    //checks if pastPeriod defined in the DB
    if (columnExists($mysqli, 'dashboard', 'Field', 'pastperiod')) {
        $sql = "SELECT pastperiod FROM dashboard WHERE username=\"$username\" LIMIT 1";
        $res = sqlQuery($mysqli, $sql);
        if ($res && !empty($obj = $res->fetch_object())) {
            $response = $obj->pastperiod;
        }
    }

    return intval($response) > 0 ? intval($response) : null; //returns NULL or integer
}

function getPastPeriod_ExtraData($pastPeriod){
    if(empty($pastPeriod)){
        return null;
    }
    $toDate = date("Y-m-d");
    $fromDate = date("Y-m-d",strtotime("$toDate -" . $pastPeriod . " days"));
    $toDate.=" 23:59:59";
    $fromDate.=" 00:00:00";
    $dates_range = array("from"=> $fromDate, "to"=>$toDate);
    $date_sql = "(SELECT files.id FROM files WHERE (files.date BETWEEN '$fromDate' AND '$toDate'))";
    $return_arr = array("dates_range"=>$dates_range, "date_sql"=>$date_sql);
    return $return_arr;
}


//set the pastPeriod value to the dashboard database
function setPastPeriod($mysqli, $username,$period_val){
    //checks if pastPeriod defined in the DB
    if(!columnExists($mysqli, 'dashboard','Field','pastperiod'))
        return null;
    if($period_val == '-1')
        $period_val = "(NULL)";
    $sql = "UPDATE dashboard SET pastperiod = $period_val WHERE username=\"$username\"";
    $res = sqlQuery($mysqli, $sql);
    if (!$res){
        return null;
    }
    
    return true;
}


function exportFilesToFolderCopy($src, $dst) {
    $success = true;
    //make sure to create the origin dir in the dest copy
    if (!is_dir(dirname($dst))) {
        mkdir_full(dirname($dst), 0777, true);
    }
    if (!copy($src, $dst)) {
        $last_error = error_get_last();
        write_to_log("ERROR", $last_error["message"]);
        $success = false;
    }
    return $success;
    
}

//gets: $id_arr - array of id of files, $dest_path - the path to copy to
//Go through on the array and copy the files with this id to the dest_path
function exportFilesToFolderLoop($id_arr, $dest_path, $mysqli) {
    global $uploadedFilesPath;//, $extraFieldsPath; //I leave extraFieldsPath here for now for compatibility 
    $text_dest = "$dest_path/text";
    $extraData_dest = "$dest_path/extraData";
    $copied_count = 0;

    $res = sqlQuery($mysqli, "SELECT filename, origFolder FROM files WHERE id IN (" . join(",", $id_arr) . ")");
    if ($res && $res->num_rows != 0) {
        while ($obj = $res->fetch_object()) {
            $origFolder = $obj->origFolder;
            $filename = $obj->filename;
            $text_fullPath = "$uploadedFilesPath/$origFolder/$filename";
            //$extraData_fullPath = "$extraFieldsPath/$origFolder/$filename.json";
            $text_destPath = "$text_dest/$origFolder/$filename";
            $extraData_destPath = "$extraData_dest/$origFolder/$filename.json";
            $export_res = exportFilesToFolderCopy($text_fullPath, $text_destPath);
            if($export_res){
                $copied_count++;
            }
            //exportFilesToFolderCopy($extraData_fullPath, $extraData_destPath);
        }
    }
    return $copied_count;
}

function exportFilesToFolderLoopExport($id_arr, $dest_path, $mysqli) {
    global $uploadedFilesPath;//, $extraFieldsPath; //I leave extraFieldsPath here for now for compatibility
    $text_dest = "$dest_path/text";
    $extraData_dest = "$dest_path/extraData";
    $copied_count = 0;
    
    $res = sqlQuery($mysqli, "SELECT filename, origFolder FROM files WHERE id IN (" . join(",", $id_arr) . ")");
    if ($res && $res->num_rows != 0) {
        while ($obj = $res->fetch_object()) {
            $origFolder = $obj->origFolder;
            $filename = $obj->filename;
            $text_fullPath = "$uploadedFilesPath/$origFolder/$filename";
            //$extraData_fullPath = "$extraFieldsPath/$origFolder/$filename.json";
            $text_destPath = "$text_dest/$filename";
            //             $extraData_destPath = "$extraData_dest/$origFolder/$filename.json";
            $export_res = exportFilesToFolderCopy($text_fullPath, $text_destPath);
            if($export_res){
                $copied_count++;
            }
            //exportFilesToFolderCopy($extraData_fullPath, $extraData_destPath);
        }
    }
    
    
    
    return $copied_count;
}

function saveTozZip($path){
    // Get real path for our folder
    $rootPath = realpath($path);
    
    // Initialize archive object
    $zip = new ZipArchive();
    $filename = $path.DIRECTORY_SEPARATOR.'exportedFiles.zip';
    $zip->open($filename, ZipArchive::CREATE | ZipArchive::OVERWRITE);
    
    // Initialize empty "delete list"
    $filesToDelete = array();
    
    // Create recursive directory iterator
    /** @var SplFileInfo[] $files */
    $files = new RecursiveIteratorIterator(
        new RecursiveDirectoryIterator($rootPath),
        RecursiveIteratorIterator::LEAVES_ONLY
        );
    
    foreach ($files as $name => $file)
    {
        // Skip directories (they would be added automatically)
        if (!$file->isDir())
        {
            // Get real and relative path for current file
            $filePath = $file->getRealPath();
            $relativePath = substr($filePath, strlen($rootPath) + 1);
            
            // Add current file to archive
            $zip->addFile($filePath, $relativePath);
            
            // Add current file to "delete list"
            // delete it later cause ZipArchive create archive only after calling close function and ZipArchive lock files until archive created)
            if ($file->getFilename())
            {
                $filesToDelete[] = $filePath;
            }
        }
    }
    $zip->close();
    
    // Zip archive will be created only after closing object
    
    header('Content-disposition: attachment; filename=exportedFiles.zip');
    header('Content-type: application/zip');
    readfile($filename);

    

}

//input: id in the savedsearchid table
function exportFilesToFolder($mysqli, $id, $dest_path) {
    $res = sqlQuery($mysqli, "SELECT docid FROM savedsearchid WHERE id = $id");
    $results = array();
    while ($obj = $res->fetch_object()) {
        $results[] = $obj->docid;
    }
    echo count($results);
    $results && exportFilesToFolderLoop($results, $dest_path, $mysqli);
}

function exportLimitAllSearch($mysqli, $id){

    $colsBool = true;
    $sql = "SELECT files.* FROM files JOIN savedsearchid ON savedsearchid.id WHERE savedsearchid.id =$id AND files.id=docid";
    $res = sqlQuery($mysqli, $sql);
        
    header('Content-Type:: application/csv');
    header('Content-Disposition: attachment; filename="list_search.csv";');
    //open the "output" stream
    $stdout_file = fopen("php://output", 'w');
    echo "\xEF\xBB\xBF";

    while ($row = $res->fetch_object()){	 
        $arr = (array) $row;
        if ($colsBool) {
            $colsBool = false;
            $extra_cols = fetch_extradata_per_doc($mysqli, -1);
            $first_keys = array_keys($arr);
            $first_keys = array_merge($first_keys, $extra_cols);
            fputcsv($stdout_file, $first_keys);
        }      
        $extra_data = fetch_extradata_per_doc($mysqli, $arr['id']);
        $arr = array_merge($arr, $extra_data);
        fputcsv($stdout_file, $arr);      
    }
    fclose($stdout_file);
}

/**
 * uses only for the export all files csv - for none files table columns
 * @param unknown $mysqli
 * @param unknown $docid
 * @return string[]|NULL[]
 */
function fetch_extradata_per_doc($mysqli, $docid){    
    $results = [];
    $fields = array(
                array(
                    "table"=>'filetags', 
                    "title" => 'Tags',
                    "field"=>"GROUP_CONCAT(DISTINCT NAME) as value", 
                    "where" => "docid = $docid AND name not in ('upload_folder','Reviewed')"
                    )  
                );
   
    foreach($fields as $element){
        //if docid -1 - return only the titles of the extradata
        if($docid < 0){
            $results[] = $element['title'];
            continue;
        }
        $sql = "SELECT $element[field] from $element[table] WHERE $element[where]";
        $res = sqlQuery($mysqli, $sql);
        
        while ($row = $res->fetch_object()){
            $results[$element["title"]] = $row->value;
        }
    }
    
    $intuview_info = getIntuviewData($mysqli, $docid, $results);
   
    $results = array_merge($results, $intuview_info);
    
    return $results;
    
}

function getIntuviewData($mysqli, $id){
    $res = [];
    
    if($id < 0){
        return $res;
    }
    $arr = getMetadataFromDB($mysqli, $id,true);
    if(!empty($arr['concepts'])){
        $res['concepts'] = json_encode($arr['concepts'],true);
    }
    if(!empty($arr['full_text'])){
        $res['full_text'] = $arr['full_text'];
    }
    if(!empty($arr['org_json'])){
        $res['org_json'] = json_encode($arr['org_json'],true);
    }
    return $res;
}
//input: id in the savedsearchid table
function exportSearchFilesToFolder($mysqli, $id, $dest_path) {
    global $db_name, $export_limit, $sharedFolder;
    session_write_close();
    
    $site_name = str_replace('_db', '', $db_name);
    date_default_timezone_set('UTC');
    
    $date = date('m_d_Y_h_i_s', time());

    global $sharedFolder;

    $path = $sharedFolder . DIRECTORY_SEPARATOR . 'exported_files' . DIRECTORY_SEPARATOR . $date;
    $res = sqlQuery($mysqli, "SELECT docid FROM savedsearchid WHERE id = $id LIMIT $export_limit");
    $results = array();
    while ($obj = $res->fetch_object()) {
        $results[] = $obj->docid;
    }
    if(!empty($results)){
        $res_copyLoop = exportFilesToFolderLoopExport($results, $path , $mysqli);
        
        if(!empty($res_copyLoop)){
            $res_msg = ($res_copyLoop==count($results))  ? array("SUCCESS"=>"Completed: $res_copyLoop files were copied") : array("ERROR"=>"Not completed: $res_copyLoop out of ". count($results) ." files were copied");
            saveTozZip($path);
        }
        else{
            $res_msg = array("ERROR"=>"Issue with export process");   
        }
    }
    else{
        $res_msg = array("ERROR"=>"No files in saved search");
    }
    return $res_msg;
}


function deleteTags($connection, $tagGroup, $docId, $tagName = null) {
    $sql = "DELETE FROM filetags WHERE groupName=\"$tagGroup\" AND docId=$docId";
    if(isset($tagName)) {
       $sql.= " AND Name = '$tagName'"; 
    }
    sqlQuery($connection, $sql);
}

function insertTag($connection, $docId, $tagName, $tagGroup, $username) {
    $sql = "INSERT INTO filetags(DocId, name, groupName,username) VALUES ($docId, '$tagName', '$tagGroup','$username')";
    sqlQuery($connection, $sql);
}

function tagDocument($connection , $docId, $tagName, $enabale, $tagGroup, $username)
{
    if (empty($docId) || empty($tagName)) {
        return array("error" => "Missing document id or tag name");
    }
    $sql1 = "SELECT DISTINCT exclusive FROM filetags WHERE groupName='$tagGroup' AND docId=0";
    $res1 = sqlQuery($connection, $sql1);

    if ($res1 === FALSE) {
        return array();
    }
    $row_group = $res1->fetch_object();
    if ($row_group->exclusive == 1) {
        deleteTags($connection, $tagGroup, $docId);
        insertTag($connection, $docId, $tagName, $tagGroup, $username);
    } else {
        if ($enabale) {
            insertTag($connection, $docId, $tagName, $tagGroup, $username);
        } else {
            deleteTags($connection, $tagGroup, $docId, $tagName);
        }
    }
}

//get the pie Data from the dashboard table 
//Note! make sure arguments are not quoted
function getTableElementData($mysqli, $table, $pieName, $key, $key_val){
    $sql = "SELECT * FROM $table WHERE name ='$pieName' AND $key='$key_val'";
    $res = sqlQuery($mysqli, $sql);
    $stats = $res->fetch_object();
    return $stats;
}

//get the pie Data from the dashboard table
//Note! make sure arguments are not quoted
function getTableElementData_v2($mysqli, $pieName, $username){
    $sql = "SELECT * FROM dashboard_panels WHERE name ='$pieName' AND username='$username'";
    $res = sqlQuery($mysqli, $sql);
    $stats = $res->fetch_object();
    return $stats;
}

//sets pie data in dashboard table by field and value
function setTableElementData($mysqli, $table, $pieName,$field, $data,$key,$key_val){
    $value = $data;
    if(is_array($data)){
        $value = json_encode($data,JSON_UNESCAPED_UNICODE);
    }
    if($field == 'results'){
        $value = $mysqli->real_escape_string($value);
    }
    $sql = " UPDATE $table SET $field = '$value' WHERE name ='$pieName' AND $key='$key_val'";
    $stats = sqlQuery($mysqli, $sql);
    if( $stats === FALSE ){
        write_to_log("ERROR", "setTableElementData problem with the sql");
    }
}

function percentagePerTable($mysqli, $table,$username,$isAdmin){
    
    $sql_filesPerTable = "SELECT COUNT(DISTINCT docId) AS c FROM " . $table;
    $res1 = sqlQuery($mysqli, $sql_filesPerTable);
    $row1 = $res1->fetch_object();
    $filesPerTable = !empty($row1) ? $row1->c : null ;
    
    if ($isAdmin){
        $sql_numOfAllFiles = "SELECT COUNT(id) AS c FROM files";
    }
    if(!$isAdmin){
        $sql_numOfAllFiles = "SELECT COUNT(id) AS c FROM files WHERE files.username='$username'";   
    } 
    $res2 = sqlQuery($mysqli, $sql_numOfAllFiles);
    $row2 = $res2->fetch_object();
    $numOfAllFiles = !empty($row2) ? $row2->c : null ;
   
    $percentage = round(($filesPerTable/$numOfAllFiles)*100);
    if(is_infinite($percentage)){
        $percentage=0;
    }
 
    return $percentage;
}

function filesWithoutObjects($mysqli, $table,$fromDate,$toDate,$containsDate,$isAdmin,$username){
    $sqlFilesWithoutObjects="SELECT  COUNT(scan_time) AS F FROM files LEFT JOIN ".$table." ON ".
    $table.".docId= files.id"." WHERE ".$table.".docId IS NULL ";
    if($containsDate){
        $sqlFilesWithoutObjects .= " AND (files.date BETWEEN '".$fromDate."' AND '".$toDate."')";
    }
    if(!$isAdmin){
        $sqlFilesWithoutObjects .=" AND files.username='$username'";
    }

    $resultsFilesWithoutObjects = sqlQuery($mysqli, $sqlFilesWithoutObjects);
    if(($row_counter = $resultsFilesWithoutObjects->fetch_object()) != null){
            $FilesWithoutObjects_num = $row_counter->F;
    }
    return intval($FilesWithoutObjects_num);  
}




//gets the user Id - for sendFolder when updating the userId in the column
function getUserId($mysqli,$user,$userId,$i){
    $query = "SELECT userId FROM files WHERE username = '$user' ORDER BY id DESC LIMIT 1";
    $res = sqlQuery($mysqli, $query);
    
    $row = $res->fetch_object();
    if ($row->userId == null && $userId < 1){
        $userId = 1;
    }
    else{
        $userId = $row->userId + $i + 1;
    }
    return $userId;
}

function isTableExists($mysqli,$table_name){
    $query = "select 1 from $table_name LIMIT 1";
    $res = sqlQuery($mysqli, $query);
    return ($res !== FALSE) ? true : false;
}

function convertDateTemp($string){
    $components = explode('-', $string);
    
    if (count($components) == 3) {
        $month = $components[0];
        $day = $components[1];
        $year = $components[2];
        
        // Check if the year is in two-digit format
        if (strlen($year) == 2) {
            // Convert two-digit year to four-digit format
            $year = '20' . $year;
        }
        
        // Create the "y-m-d" format string
        $convertedString = "$year-$month-$day";
        return $convertedString;
    }
}
    function prase_datefield_date($date_field){
        if (!empty($date_field))
        {
            try{
//                 $date_field = convertDateTemp($date_field);
                $dt = new DateTime(str_replace(" at ", " ", $date_field));
            }

   
        catch (Exception $ex)
        {
            write_to_log("ERROR", $date_field . " - date parsing produced this error: " . $ex);
            $dt = NULL;
        }
        
        $date_field = $dt? $dt->format("Y-m-d H:i:s") : NULL;
    }
    return $date_field;
}

//get in getsmartSearch province/city related results only that fits the state/province information
function getCityProvinceData($mysqli,$k,$v) {
    $ind_name=null;
    $ind_val=null;
    $query = "SELECT * FROM $k WHERE name = '$v'";
    $res = sqlQuery($mysqli, $query);
    if (!empty($res)){
        while ($obj = $res->fetch_object()){
            $province = $obj->province;
            $country = $obj->country;
        }
    }
    if(isset($province)){
        $ind_name='province';
        $ind_val=$province;
    }
    else{
        $ind_name = 'country';
        $ind_val=$country;
    }
    $queryo = "SELECT DISTINCT name FROM $k WHERE $ind_name='$ind_val' ORDER BY name ASC limit 50";
    $reso =  sqlQuery($mysqli, $queryo);
    if (!empty($reso)){
        while ($obj = $reso->fetch_object()){
            if ($obj->name != $v){
                $names[] = $obj->name;
            }
        }
    }
    return $names;
}

//argument in: name of an entity concept
function isEntity(string $cand): bool {
    switch ($cand) {
        case "person_object":
        case "organizational_identity":
        case "place_object":
        case "event":
	case "abstract_idea_entity" :
            return true;
        default :
            return false;
    }
}


function getSubFields($mysqli, $table, $type) {
    if (strlen($type) > 0) {
        return getSubConcepts($mysqli, $table);
    } else {
        if(!empty($_REQUEST["subfield_new"])){
            return getConceptInstances_new($mysqli, $table);
        }
        return getConceptInstances($mysqli, $table);
    }
}


function getSubConcepts($mysqli, $table) {
    $allTab = addAllTab($table);
 
    $res = Concept::getSubConcepts($mysqli, $table);
    if(addAllTab($table)){
        $SubConcepts["All"] = "All";
    }
    if (!empty($res)) {
        $SubConcepts = array_merge($SubConcepts,$res);
    }
    return $SubConcepts;  
}


function getSubConceptsCount($mysqli, $table) {
    $allTab = addAllTab($table);
    $concepts_arr = getFlavorClassArray($mysqli);
    $res = Concept::getSubConcepts($mysqli, $table);
    $SubConcepts["All"] = "All";
    $SubConcepts[$table] = $table;
    if (addAllTab($table)) {
        $ret_arr = array_merge($SubConcepts,$res);
        foreach($ret_arr as $table_name){
            $table_name = TablesInformation::getTableName($table_name);
            if(!in_array($table_name, $concepts_arr)){
                continue;
            }
            //gets the number of items in the specific tables
            $internal_res = sqlQuery($mysqli, "SELECT MAX(id) as m from $table_name");
            $row = $internal_res->fetch_object();
            $final_arr[$table_name] = intval($row->m) ?? 0;
            
        }
        return $final_arr;
    }
}

function getConceptInstances($mysqli, $table) {
    $prefix = $_REQUEST["prefix"] ?? null;
    $prefix_sql = !empty($prefix) ? " name LIKE \"$prefix%\"" : " TRUE "; 
    if ($table == "aya"){
        $query = "SELECT DISTINCT name FROM $table WHERE $prefix_sql";
    }
    elseif ($table == "anti_concept" || $table == "pro_concept"){
        $prefix_sql = !empty($prefix) ? " value LIKE \"$prefix%\"" : " TRUE ";
        
        $query = "SELECT DISTINCT value as name FROM $table WHERE $prefix_sql";
    }
    else{
    
        $query = "SELECT DISTINCT name FROM $table WHERE $prefix_sql LIMIT 10000";
    }
    $res = sqlQuery($mysqli, $query);
    
    while ($res && $obj = $res->fetch_object()) {
        $arr[$obj->name] = $obj->name;
    }
    return $arr;
}

function getConceptInstances_new($mysqli,  $table) {
    $prefix = $_REQUEST["prefix"] ?? null;
    $prefix_sql = !empty($prefix) ? " name LIKE \"$prefix%\"" : " TRUE "; 
    
    if ($table == "aya"){
        $query = "SELECT DISTINCT name FROM $table";
    }
    elseif ($table == "anti_concept" || $table == "pro_concept"){
        $prefix_sql = !empty($prefix) ? " value LIKE \"$prefix%\"" : " TRUE ";
        
        $query = "SELECT DISTINCT value as name FROM $table WHERE $prefix_sql ORDER BY name ASC";
    }
    else{       
        $query = "SELECT DISTINCT name,ontName FROM $table WHERE $prefix_sql ORDER BY name ASC  LIMIT 10000 ";
    }
    $res = sqlQuery($mysqli, $query);
   
    while ($res && $obj = $res->fetch_object()) {
        $temp_arr = ["label"=>$obj->name,"isSelected"=>false];  
        if(property_exists($obj, 'ontName')){
            $temp_arr['ont'] = $obj->ontName;
        }
         $arr[] = $temp_arr;
    }
    return $arr;
}

function addAllTab($table){
    if ($table != "anti_concept" && $table != "pro_concept") {
        return true;
    } 
    return false;
}

function splitTypes($arr){
    $alltypes = array(); 
    array_walk($arr, function($value, $key) use (&$alltypes) { 
        $alltypes = array_merge($alltypes, explode(',', $key));	
    } );
    return array_combine($alltypes, $alltypes);   
}

/**Checks the requested data and <b>returns</b> the escaped mysql string */
function validateSQL($request, $mysqli){
    
    $getPar = $_REQUEST[$request] ?? "";
    return $mysqli->real_escape_string($getPar);
}
/**Checks the requested JSON data and <b>returns</b> the escaped mysql string */
function validateSQLJson($jsonArr,$request, $mysqli){
    
    $getPar = $jsonArr[$request] ?? "";
    return $mysqli->real_escape_string($getPar);
}

function fetchStatsVars($is_trend, $mysqli, $jsonArr, $username){
    $slices = 0;
    $response = array();
    $toDate = $jsonArr["toDate"] ?? ""; //getToDate($jsonArr, $mysqli);
    $fromDate = $jsonArr["fromDate"] ?? "";//getFromDate($jsonArr, $mysqli);
    
    if (is_array($jsonArr) && sizeof($jsonArr) > 1) {
        $table = validateSQLJson($jsonArr, "table", $mysqli);
        $name = validateSQLJson($jsonArr, "name", $mysqli);
        
        if (array_key_exists("pastPeriod", $jsonArr) && intval($jsonArr["pastPeriod"])>0) {
            $toDate = date("Y-m-d");
            $fromDate = date("Y-m-d", strtotime("$toDate -" . $jsonArr["pastPeriod"] . " days"));
        }
        if (array_key_exists("savedsearch", $jsonArr)) {
            $savedSearch = $jsonArr["savedsearch"];
        }
        if (!array_key_exists("slices", $jsonArr)) {
            $slices_query = "SELECT slices FROM dashboard WHERE name='$name' AND type='pie' AND username='$username'";
            write_to_log("PERMORMENCE", "piegraph sql - $slices_query -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
            $res = sqlQuery($mysqli, $slices_query);
            if ($res != false) {
                if ($row = $res->fetch_object())
                    $slices = $row->slices;
            }else {
                $slices = 10;
            }
        } else {
            $slices = $jsonArr["slices"];
        }
        if (array_key_exists("lastSearch", $jsonArr))
            $lastSearch = $jsonArr["lastSearch"];
            
            $tableData = validateSQLJson($jsonArr, "freeTextInput", $mysqli);
            $searchField = validateSQLJson($jsonArr, "search", $mysqli);
            $langs = validateSQLJson($jsonArr, "langs", $mysqli);
            $response["search"] = $searchField;
    } else {
        $past_days = validateSQL("pastPeriod", $mysqli);
        if (!empty($past_days)) {
            $toDate = date("Y-m-d");
            $fromDate = date("Y-m-d", strtotime("$toDate -" . $past_days . " days"));
        }
        $savedSearch = $_REQUEST["savedsearch"] ?? null;
        $lastSearch = $_REQUEST["lastSearch"] ?? null;
        $table = validateSQL("table", $mysqli);
        $tableData = validateSQL("freeTextInput", $mysqli);
       
        
        
        
        if (($slices = $_REQUEST["slices"] ?? null) == null) {
            $slices = 10;
        } else {
            $slices = intval(validateSQL("slices", $mysqli));
        }
    }
    if(isset($_REQUEST["type"])){
        if($_REQUEST["type"]=='corresponded'){
            $table = "searchEmails";
        }
        else{
            $table = "searchRelation";
        }
    }
    /*
     * check if we need to search withen savedsearch
     */
    
    if (!empty($savedSearch) || !empty($lastSearch)) {
        $_REQUEST["search_function"] = true;
        $_REQUEST["skip_include"] = true;
        require_once 'searchEng.php';
        $savedSearch = $mysqli->real_escape_string($savedSearch);
        $savedSearchesId = getLastSavedSearchId($mysqli, $username,$savedSearch);        
        $savedSearchSqlId = " AND files.id in (SELECT docid FROM savedsearchid WHERE id=$savedSearchesId) ";
        $savedSearchSqldocId = " AND docId in (SELECT docid FROM savedsearchid WHERE id=$savedSearchesId) ";
    }else {
        $savedSearchesId = NULL;
        $savedSearchSqldocId = "";
    }
    /*
     * Defines the date
     */
    $containsDate = false;
    $dates_range = array();
    if ((preg_match('/[0-9]/', $fromDate) && (preg_match('/[0-9]/', $toDate)))) {
        //add time to date
        $fromDate .= " 00:00:00";
        $toDate .= " 23:59:59";
        $containsDate = true;
        $dates_range = array("from" => $fromDate, "to" => $toDate);
    }
    $res = array();
    $res["table"] = (!empty($table)) ? $table  : null; //(($is_trend) ? "network" : null);
    $res["name"] = $name ?? null;
    $res["dates_range"] = $dates_range  ?? null;
    $res["containsDate"] = $containsDate  ?? null;
    $res["slices"] = $slices  ?? null;
    $res["tableData"] = $tableData  ?? null;
    $res["langs"] = $langs  ?? null;
    $res["savedSearch"] = $savedSearch  ?? null;
    $res["savedSearchesId"] = $savedSearchesId  ?? null;
    $res["savedSearchSqlId"] = $savedSearchSqlId  ?? null;
    $res["savedSearchSqldocId"] = $savedSearchSqldocId  ?? null;
    $res["response"] = $response ?? null;
    
    return $res;
}

function getSearchFieldValues($mysqli,$username, $isAdmin, $request, $v2){
    require_once 'sentimentUtils.php';
    global $dropdownArr;
    $table = $request["field"];
    
    if($table == "searchSentiment"){
        return getSentimentFields($mysqli);
    }
    
    if($table == "searchTags"){
        return getTags($mysqli, $v2);
    }
    
    if($table == "searchNotTagged"){
        return getTags($mysqli, false);
    }
    
    if($table == "folder_name"){
        return getFolders($mysqli, $username, $isAdmin);
    }
    
    if($table == "searchOther"){
        if(!empty($_GET["subField"])){        
            return getSubFields($mysqli, $_GET["subField"],$_GET["getType"] ?? null);   
        }
        return getConceptsCount($mysqli);
    }
    
    if(array_key_exists($table, $dropdownArr)){
        return getDropDownData($mysqli, $table, $v2);
    }
    ob_clean();
    return false;
}


function getTextNoBOM($text) {
    $textNoBOM = strlen($text) >= 3 && substr($text, 0, 3) == UTF8_BOM ? substr($text, 3) : $text;
    return $textNoBOM;
}

//get the last folder id that was updated into the system
function getLastFolderId($mysqli, $folder_name, $username){
    $sql = "SELECT max(id) as id FROM foldertree WHERE name='$folder_name' AND username='$username'";
    $res = sqlQuery($mysqli, $sql);
    $row = $res->fetch_object();
    return $row->id;
    
}


function getManageEntitiesNames($mysqli){
    
    $names = [];
    $sql = "SELECT name FROM entitiesmanage WHERE entity<>\"smart\"";
    $res = sqlQuery($mysqli,$sql);
    while($row = $res->fetch_object()){
        $names[] = $row->name;
    }
    return $names;
}


/**
 * getLastSavedSearchId - get the last search id (default of $lastSearchNameDefault)
 * We place it here since it called multiple time over the AAA
 * @param unknown $connection
 * @param unknown $username
 * @param unknown $special_name - if it's specific name of search
 * @return unknown
 */
function getLastSavedSearchId($connection, $username, $name_cond = null){
    global $lastSearchNameDefault;
    if(empty($name_cond)){
        $name_cond = $lastSearchNameDefault; //default
    }
    $name_cond = "NAME ='$name_cond'";
    
    $query = "SELECT id FROM savedsearch WHERE username='$username' AND $name_cond";
    $res = sqlQuery($connection,$query);
    if ($obj = $res->fetch_object()){
        $savedsearchId = $obj->id;
    }
    return $savedsearchId ?? -1;
}


/**
 * The mode is modified by your current umask, which is 022 in this case.
 * so this function set your umask temporarily to zero so it has no effect
 * @param unknown $path
 * @param unknown $permissions
 * @param unknown $flag
 */
function mkdir_full($path, $permissions, $flag = null){
    $oldmask = umask(0);
    mkdir($path, $permissions, $flag);
    umask($oldmask);
}

/**
 * this function is specific for the csv of coordinates to json (use it to update the json file with new csv)
 * @param unknown $file the path of the file
 * the output wil be under allInOneWeb - results
 */
function convertCoordCsvToJson($file, $filter_file){
    $csv= file_get_contents($file);
    $filter_file = file_get_contents($filter_file);
    $filter_array = explode("\n", $filter_file);
    
    $array = array_map("str_getcsv", explode("\n", $csv));
    $first = true;
    $result = array();
    foreach($array as $item){
        if($first || empty($item[0]) || empty($item[1]) || empty($item[2]) || !in_array($item[0],$filter_array)){
            $first = false;
            continue;
        }
        else{
            $result[$item[0]] = array("lat" => $item[1], "lan" => $item[2]);
        }
    }
    $json = json_encode($result, true);
    
    $fp = gzopen ('files'.DIRECTORY_SEPARATOR.'coordinates.html.gz', 'wb9');

    // Compress the file
    $fp && gzwrite ($fp, $json);
    
    // Close the gz file and we're done
    $fp && gzclose($fp);   
    
}

/**
 * gets folder if by it's name
 * @param unknown $mysqli
 * @param unknown $folder_value
 * @param unknown $username
 * @return unknown|boolean
 */
function getFolderIdFromName($mysqli, $folder_value, $username, $isAdmin, $saved_search = null){
    $folder_value = $mysqli->real_escape_string($folder_value);
    if($saved_search){
        $folder_value = "save_search::$folder_value";
    }
    $user_sql = $isAdmin ? " TRUE" : " username='$username' ";
    $sql = "SELECT id FROM foldertree WHERE NAME ='$folder_value' AND $user_sql";
    $res = sqlQuery($mysqli, $sql);
    if(empty($saved_search)){
        while($obj = $res->fetch_object()){
            $ids[] = $obj->id;
        }
        return $ids ?? null;
    }
    else{
        $row = $res->fetch_object();
        if($row){
            return $row->id;
        }
    }
    return false;
}

/**
 * 
*readAndParseWhatsApp_split - specific function for whatsapp - try it on test.php and update it here
*reads the output from celebrite and creates folders with all the messages + the original doc
*example: readAndParseWhatsApp_split("C:\\Users\\galar\\OneDrive\\Documents\\whastsapp","C:\\Users\\galar\\Downloads", '-----------------------------');
*/
function readAndParseWhatsApp_split($folder_path, $output_path, $delimiter){
    if ($handle = opendir($folder_path)) {
        
        while (false !== ($entry = readdir($handle))) {
            
            if ($entry != "." && $entry != "..") {
                $content = file_get_contents($folder_path."\\".$entry);
                
                //gets all the messages in the file
                $content_arr = explode($delimiter, $content);
                
                //getting generic info
                $save_path = $output_path."\\".$entry."_dir\\";
                mkdir($save_path, 0777);
                
                //copy the original file with different ext to not analyse
                copy($folder_path."\\".$entry, $save_path."$entry.original");
                
                //change current use to have utf8encode
                $current_user = utf8_encode(strtolower(rtrim($entry,'.txt')));
                $general_info = $content_arr[0];
                $general_info = explode("\r\n", $general_info);
                $participents_value = ltrim($general_info[2], "Participants: ");
                $participents_arr = explode(',', $participents_value);
                
                //getting the other user in the conversation
                foreach($participents_arr as $user){
                    if(strpos($participents_arr, $current_user) == false){
                        $other_user = explode('@s.whatsapp.net ', $user);
                        $other_user = $other_user[1];
                        break;
                    }
                }
                
                $count = 0;
                foreach($content_arr as &$msg){
                    //first 2 are metadata usually
                    if($count < 2){
                        $count++;
                        continue;
                    }
                    
                    
                    
                    //means the other person is appear - need to attach the current user
                    if(strpos($msg, "From: From:") !== false){
                        $msg = "To: $current_user $msg";
                    }
                    elseif(strpos($msg, "From:") !== false){
                        $msg = "To: $other_user $msg";
                    }
                    $count++;
                    
                    //reorginized to JSON
                    $msg_arr = explode("\r\n", $msg);
                    if($msg_arr[4]=="Body:"){
                        for($i = 5; $i <= count($msg_arr); $i++){
                            $msg_arr[4] .= " ".$msg_arr[$i];
                            unset($msg_arr[$i]);
                        }
                        
                    }
                    $msg_arr = array_values($msg_arr);
                    
                    //clear up empty values
                    for($i = 0; $i <= count($msg_arr); $i++){
                        
                        if(empty($msg_arr[$i])){
                            unset($msg_arr[$i]);
                        }
                    }
                    
                    $json_msg = array();
                    
                    foreach($msg_arr as $element){
                        $element_arr = explode(': ', $element);
                        if(isset($element_arr[2])){
                            $element_arr[1] = $element_arr[2];
                            unset($element_arr[2]);
                        }
                        $value = $element_arr[1] ?? null;
                        
                        if($element_arr[0]=='Body'){
                            $key = "";
                        }
                        elseif($element_arr[0]=='From'){
                            $value_arr = explode ("@s.whatsapp.net", $value);
                            $json_msg[] = "Tel: +" . rtrim($value_arr[0],",");
                            $value = $value_arr[1];
                            $key = "From: ";
                        }
                        elseif($element_arr[0]=='Timestamp'){
                            $key = "Date: ";
                            $value_arr = explode('(UTC', $value);
                            $value = $value_arr[0];
//                             $time = str_replace(array('(',')'), " ", $value_arr[1]);
//                             $json_msg[] = "Time: " . $time;
//                             $value = $value_arr[0];
                        }
                        elseif($element_arr[0]=='Source App'){
                            continue;
                        }
                        else{
                            $key =ltrim($element_arr[0],"\r\n").": ";
                        }

                        $json_msg[] = $key. rtrim($value,",");
                    }
                    
                    $done = implode("\n",$json_msg);
                    
                    
                    file_put_contents($save_path."\\msg_$count.split",$done );
                    
                }
                
            }
        }
        
        closedir($handle);
    }
    
}



function getSystemSettingsProp($mysqli, $prop){
    $result= null;
    $sql = "SELECT value FROM system_settings  WHERE prop=\"$prop\"";
    $res = sqlQuery($mysqli, $sql);
    if(!empty($res)){
        $row = $res->fetch_object();
        if (!empty($row)){
            $result = $row->value;
            if($result !== 'true'){         
                if($result == 'false' || ($result == 0 && $result !== ';')){
                    $result = false;
                }
                elseif($result == 1){
                    $result = true;
                }
            }
        }
    }
 
    
    return $result;
}

function getAlertSettingsProp($mysqli, $prop, $alerts = false){
    $result= "";
    $sql = "SELECT value FROM alerts_settings  WHERE prop=\"$prop\"";
    $res = sqlQuery($mysqli, $sql);
    if(!empty($res)){
        $row = $res->fetch_object();
        if (!empty($row)){
            $result = $row->value ?? 'false';
            if($result !== 'true'){
                if(strcmp($result, 'false') == 0){
                    $result = false;
                }
                elseif($result == 1){
                    $result = true;
                }
            }
        }
    }
    
    
    return $result;
}

function getConceptFromId($mysqli, $index){
    if(empty($index)){
        return null;
    }
    $results = "";
    $sql = "SELECT concept FROM `concepts_indexes` WHERE id = $index";
    
    $res = sqlQuery($mysqli,$sql);
    if(!empty($res)){
        $obj = $res->fetch_object();
        if(!empty($obj)){
            $results = $obj->concept;
        }
    }
    
    return $results;   
}

function getSavedSearchFromId($mysqli, $index){
    if(empty($index)){
        return null;
    }
    $results = "";
    $sql = "SELECT name FROM `savedsearch` WHERE id = $index";
    
    $res = sqlQuery($mysqli,$sql);
    if(!empty($res)){
        $obj = $res->fetch_object();
        if(!empty($obj)){
            $results = $obj->name;
        }
    }
    
    return $results; 
}

function getidFromConcept($mysqli, $concept){
    if(empty($concept)){
        return null;
    }
    $results = "";
    $sql = "SELECT id FROM `concepts_indexes` WHERE concept = \"$concept\"";
    $res = sqlQuery($mysqli,$sql);
    if(!empty($res)){
        $obj = $res->fetch_object();
        if(!empty($obj)){
            $results = $obj->id;
        }
    }
    
    return $results;
}

function getConceptsFromIds($mysqli, $indexes){
    if(empty($indexes)){
        return [];
    }
    $results = [];
    $sql = "SELECT concept FROM `concepts_indexes` WHERE id IN($indexes)";
    $res = sqlQuery($mysqli,$sql);
    while($obj = $res->fetch_object()){
        $results[] = $obj->concept;
    }
    return $results;
}

function init_rules_wl(){
    $possible_same_person_rules = json_decode(file_get_contents('../iventitymatcherhtm/jsons/possible_same_person_rules.json',true));
    $relations_rules = json_decode(file_get_contents('../iventitymatcherhtm/jsons/relations_rules.json',true));
    
    $rules_json = json_decode(json_encode($possible_same_person_rules), TRUE);
    $rules_relation_json = json_decode(json_encode($relations_rules), TRUE);
    $res = array_merge($rules_json, $rules_relation_json);
    return $res;
}

function checkMandFields($mysqli, $mand_fields){
    $missing_tables = [];
    $final_mand = [];
    $mand_tables = array_keys($mand_fields);
    foreach($mand_tables as $f){
        $table_f = TablesInformation::getTableName($f);
        //if table doesnt exists - move it to "Concept"
        if(!TablesInformation::isTableExists($mysqli,$table_f)){
            if(!array_key_exists("concept", $final_mand)){
                $final_mand["concept"] = [];
            }
            $final_mand["concept"] = array_merge($mand_fields[$f],$final_mand["concept"]);
            write_to_log("ERROR", "Table $table_f doesnt exists! repalced with Concept table");
            
        }
        else{
            $final_mand[$table_f] = $mand_fields[$f];
        }
        
    }
 
    return $final_mand;
}


function getCoordinates($mysqli, $ont = null){
    $coords = [];
    $ont_sql = !empty($ont) ? "location = \"$ont\"" : "true";
    $sql = "SELECT location,coordinates FROM coordinates WHERE $ont_sql";
    $res = sqlQuery($mysqli, $sql);
    while($obj =  $res->fetch_object()){
        $coords[$obj->location] = ($obj->coordinates);
    }
    
    return $coords;
}

/**
 * get key pos in array (handles pos 0 as well)
 * @param unknown $arr
 * @param unknown $key
 * @return NULL|number|mixed
 */
function getCurrPos($arr, $key){
    $curr_pos = array_search($key,$arr);
    if ($curr_pos === 0) {
        return 0;
    } elseif ($curr_pos === false) {
        return null;
    } else {
        return $curr_pos;
    }
}