<?php

if(!isset($_SESSION)) session_start();
include_once("demosettings.php");
require_once ("parseUtils.php");
require_once ("globalArr.php");
require_once ("cacheEng.php");
require_once 'sqlGeneratorClass.php';
require_once 'analyticsUtils.php';


//NO MORE SCRIPT BEYOND THIS LINE - only functions!

function setIfExists($key,$array) {
    if (array_key_exists($key, $array)) {
        $value = $array[$key];
        return $value;
    }
    return null;
}

function getDateFromRequest($toOrFrom, $json, $mysqli) : string {
    $date = empty($json) ?
            array(validateSQL("{$toOrFrom}Year", $mysqli), validateSQL("{$toOrFrom}Month", $mysqli), validateSQL("{$toOrFrom}Day", $mysqli)) :
            array($json["{$toOrFrom}Year"]?? null, $json["{$toOrFrom}Month"] ?? null, $json["{$toOrFrom}Day"] ?? null);
    return call_user_func_array('sprintf', array_merge(array("%s-%s-%s"), $date));
}

function getToDate($json, $mysqli): string {
    return getDateFromRequest("to", $json, $mysqli);
}

function getFromDate($json, $mysqli) :string {
    return getDateFromRequest("from", $json, $mysqli);
}

/**Get data to the global pies -> persons, organisations, events, places and concepts*/
function getPie($mysqli, $v2 = false, $jsonArr = null, $v2_saved_search = null, $docid = null) {
    session_write_close();
    
    global $userdetails;
    global $username;
//     global $pieName;
    global $pieResult;
    global $skipDuplicate;
    global $dropdownArr,$dropdownArrSolr;
    global $dropdownArrCaptionMapping, $dropdownArrMaxMapping;
    global $extraDataKey2Table;
    $useSolr = getSystemSettingsProp($mysqli, "useSolr");
    

    $isAdmin = $_REQUEST['isAdmin_override'] ?? $_REQUEST['isAdmin'] ?? getUserPermission(CAN_SHOW_ALL_USER);
    $username = $_REQUEST['username'] ?? $username;
    
    $from_dash = false;
    if ($jsonArr) {
        $from_dash = true;
        
        if(empty($pieName)){
           $pieName = $jsonArr['name'];
        }
            $stats = getTableElementData($mysqli, 'dashboard', $pieName, 'username', $username);  
        $jsonArr = json_decode($stats->details, true);
        $jsonArr["slices"] = $pieResult;
        $pastPeriod = getPastPeriod($mysqli, $username);
        if (!isset($jsonArr["pastPeriod"]) || intval($jsonArr["pastPeriod"]) < 0 || $jsonArr["pastPeriod"] == "null") {
            $jsonArr["pastPeriod"] = $pastPeriod;
        }
    }
    
    if(!$from_dash){
        //get relations now from get - so it might be []  - which is ok it'll be merged
        //to request if exists - if not - it wont do nothing
        $data = file_get_contents('php://input'); 
        if(!empty($data)){
            $_REQUEST = array_merge($_REQUEST,json_decode($data,1)); //merge to the request
        }
    }
    
    $trendTypeReq = array_key_exists("trendType", $_REQUEST) ? $_REQUEST["trendType"] : null;
    $trendTypeJArr = array_key_exists("trendType", $jsonArr ?? []) ? $jsonArr["trendType"] : null;
    
    $isTrendType_except_corresponded = ((!empty($trendTypeReq) && $trendTypeReq == "network") ||
    (!empty($trendTypeJArr) && $trendTypeJArr == "network")) && $_REQUEST['type'] !== 'corresponded';
 

    /**
     * Non Trend types: Fetching dates, saved search, free text search and more
     */
    $isSentiment = false;
    $isField = false;
    $fetched_vars = fetchStatsVars($isTrendType_except_corresponded, $mysqli, $jsonArr ?? $_REQUEST, $username);
    $table = strtolower(str_replace(' ', '_', trim($fetched_vars["table"]))) ?? "";
    $name = $fetched_vars["name"] ?? null;
    
    $dates_range  = $fetched_vars["dates_range"] ?? null;
    $dates_range_str = !empty($dates_range) ? $dates_range["from"]." AND ".$dates_range["to"] : "";
    
    $containsDate = $fetched_vars["containsDate"] ?? null;
    $slices = $fetched_vars["slices"] ?? null;
    $tableData = $fetched_vars["tableData"] ?? null;
    $langs = $fetched_vars["langs"] ?? null;
    $savedSearch = $fetched_vars["savedSearch"] ?? $_REQUEST['savedsearch'] ?? null;
    $savedSearchesId  = $fetched_vars["savedSearchesId"] ?? null;
    $savedSearchSqlId = $fetched_vars["savedSearchSqlId"] ?? null;
    $savedSearchSqldocId= $fetched_vars["savedSearchSqldocId"] ?? null;
    $response= $fetched_vars["response"] ?? null;

    $max_caption = array_key_exists($table, $dropdownArrMaxMapping) || array_key_exists($table, $dropdownArrCaptionMapping);
    

    
    
    /**
     * SEARCH REFACTORED AREA
     */
    if(array_key_exists($table, $dropdownArr) || (isEntity($table) && !empty($tableData)) || 
        ($table == "concept") || 
        ($table != "" && $table != "concept" && $table != "searchEmails" && !$isSentiment && !$isField)  || 
        $isTrendType_except_corresponded || $max_caption){
        
            if($max_caption){
                $caption_table = $dropdownArrMaxMapping[$table] ?? $dropdownArrCaptionMapping[$table];
                $orgSolrField_max = array_search($caption_table,$extraDataKey2Table);           
            }
           
            $analytics_obj = new AnalyticsClass($mysqli, $caption_table ?? $table, $tableData, $username, $slices, $docid);
            $_REQUEST['skip_include'] = true;
            require_once 'searchEng.php';
            $orgSolrField = array_search($table,$extraDataKey2Table);
            $response = array();
            $saved_search_arr = $analytics_obj->getSavedSearchName($savedSearch);
            $saved_search_name = $saved_search_arr["name"];
            $analytics_obj->setSavedSearchName($saved_search_name);
            //fetch the parameters for the search
            $params = $analytics_obj->fetch_params_search($saved_search_arr, $extraDataKey2Table, $useSolr, $dropdownArr, $dropdownArrSolr, $skipDuplicate, $orgSolrField_max ?? $orgSolrField, $savedSearch, $isSentiment, $isField, $isTrendType_except_corresponded, $trendTypeJArr,$jsonArr, $v2);
            /**
             * 0 - For MetaData tables from SM - maxMapping and regularMapping
             */
            
            if($max_caption){
                $isField = true;
                $max = array_key_exists($table, $dropdownArrMaxMapping);
                $id = $analytics_obj->search_and_fetch_id($params,$saved_search_name, $dates_range_str);
                
                $response = fetchMappedCaption($mysqli, $slices, $table, $caption_table, $max, $id, $tableData);
                
//                 $response["total"] = intval(numOfTableObjects($mysqli, $table, $isAdmin, $username));
                $response["FilesWithoutObjects_num"] = filesWithoutObjects($mysqli, $table, $dates_range['from'], $dates_range['to'], $containsDate, $isAdmin, $username);
            }
            
            /**
             * 1 - TrendType: Relations, Corefs, except Corresponded
             */
            elseif ($isTrendType_except_corresponded) {
                $saved_search_name .= !empty($name) ? "->$name" : "";
                $id = $analytics_obj->search_and_fetch_id($params,$saved_search_name, $dates_range_str); 
                $response = TrendTypePie($mysqli, $username, $isAdmin, $dates_range, $trendTypeJArr, $jsonArr, $id);   
            }
            /**
             * 2 - Concept type
             */
            elseif ($table == "concept") {
                if(!empty($params)){     
                    $id = $analytics_obj->search_and_fetch_id($params,$saved_search_name, $dates_range_str);      
                }       
                $response = loadConceptData($tableData, $mysqli, $isAdmin, $username, $slices, $dates_range, $savedSearchSqlId, $id ?? null, $v2);
            }
            /**
             * 3 - dropdownArr type
             */
            elseif (array_key_exists($table, $dropdownArr) || in_array($table, $dropdownArr)) {
                $isField = true;
                $id = empty($docid) ?  $analytics_obj->search_and_fetch_id($params,$saved_search_name, $dates_range_str)  : null;
                $res_cache = CacheEng::getFromCache($mysqli,  $username, $pieName);
                $res_cache = empty($res_cache) ? [] : json_decode($res_cache->results, true);
                
                $response = $analytics_obj->getValuesFromSearch($dates_range, $containsDate, $res_cache);
            }
            /**
             *  4 - Entity type - with free text search
             */
            elseif (in_array($table, array("person_object", "organizational_identity", "place_object", "event","abstract_idea_entity")) && !empty($tableData)) {
                $id = empty($docid) ?  $analytics_obj->search_and_fetch_id($params['all'],$saved_search_name, $dates_range_str) : null;                
                $response = $analytics_obj->getResponseFromSearch($table, [] );
            }
            elseif ($table == 'searchemails') {
                $id = empty($docid) ? $analytics_obj->search_and_fetch_id($params,$saved_search_name) : null;
                $response = TrendTypePie($mysqli, $username, $isAdmin, $dates_range, $trendTypeJArr, $jsonArr, $id);
            }

            /**
             * 5 - Entity type - Generic
             */
            elseif ($table != "" && $table != "concept" && !$isSentiment && !$isField) {
                $id = empty($docid) ? $analytics_obj->search_and_fetch_id($params,$saved_search_name, $dates_range_str) : null; 

                $res_cache = CacheEng::getFromCache($mysqli,  $username, $pieName);
                $res_cache = empty($res_cache) ? [] : json_decode($res_cache->results, true);
                
                $response = $analytics_obj->getResponseFromSearch($table, $res_cache);
            }      
    }
    
    // General Data
    $orients = validateSQL("orients", $mysqli);
    $religious = validateSQL("religious", $mysqli);
    $tables = json_decode($_POST["tabs"] ?? "");
    $risks = json_decode($_POST["risks"] ?? "");
  
    
    /**
     * special case for word cloud
     */
    if ($table == "phrases") {
        $response = $analytics_obj->loadWordCountData($savedSearch, $slices);
        $isField = true;
    }     
    /**
     * tables type
     */
    if ($tables != "") {
        $response = tables($tables, $savedSearchSqldocId, $username, $mysqli, $slices);
    }
    /**
     * langRes type
     */
    if (!empty($langs)) {
        $response = langRes($langs, $mysqli, $savedSearchSqldocId, $savedSearchSqlId, $username, $isAdmin, $slices);
    }       
    /**
     * risks type
     */
    if ($risks != "") {
        $response = risks($risks, $mysqli, $savedSearchSqlId, $username, $isAdmin, $slices);
    }
    /**
     * orients type
     */
    if ($orients != "") {
        $response = orients($mysqli, $savedSearchSqlId, $username, $isAdmin, $slices);
    }
    /**
     * religious type
     */
    if ($religious != "") {
        $response = religious($mysqli, $savedSearchSqlId, $username, $slices);
    }
    
    if(!empty($savedSearch)){
        if (strlen($savedSearch) > 0) {
            $response["savedSearch"] = $savedSearch;
        }
    }

    //update cache
    if(isset($jsonArr['type'])){
        $graphType = "$jsonArr[type]";
    }
    else{
        $graphType = "pie";
     
    }

    //in case of getPie with last-search OR get pie on docid - dont do cache
    if(empty($v2_saved_search) && empty($docid)){
        if(!empty($pieName)){
            CacheEng::updateResultsAndCache($mysqli, $username, $graphType, $pieName, $response);
        }
    }


//     if(IS_CLI){
//         sqlClose($mysqli, 'StatisticsPageEng');
//     }
    $response = fillResponse($mysqli, $response, $table);
    return $response;
}

function fillResponse($mysqli, $response, $table){    
    global $manageEntitiesArray;
    $entities = [];
    foreach($manageEntitiesArray as $key=>$val){
        $key = TablesInformation::getConceptPresentName($key);
        
        $entities[] = $key;
    }
    //check if its a entity
    $concept = TablesInformation::getConceptPresentName($table);
    $sql = "SELECT DISTINCT parent_name AS c FROM  parent_tables AS arr WHERE concept_name ='$concept'";
    $res = sqlQuery($mysqli, $sql);
    while (($row = $res->fetch_object()) != null) {
        $parent_tables[] = $row->c;
    }
    
    if(in_array($concept, $entities) || !empty(array_intersect($parent_tables ?? [], $entities))){
        $response["isEntity"] = true;
    }
    else{
        $response["isEntity"] = false;
    }
    
    $response["amount"] = $response["amount"] ?? json_decode('{}');
    $response["popular"] = $response["popular"] ?? json_decode('{}');
    $response["unpopular"] = $response["unpopular"] ?? json_decode('{}');
    return $response;
}



//maps the 
function maplangFull($response){
    $ret = array();
    foreach($response['amount'] as $key=>$val){
        $ret[] = array(lang_mapper($val[0]),$val[1]);
    }
    return $ret;
}

function getamount($amt,$table,$mysqli){
    $isOrgText = getSystemSettingsProp($mysqli, "isOrgText");  
    foreach ($amt as $key => $value) {
        //Add orgText
        $orgText = "";
        if ($isOrgText) {
           $orgText = orgText($mysqli,$table,$key);
        }
        if (strlen($orgText) > 0) {
            $tmp = array($orgText, $value, $key);
        } else {
            $tmp = array($key, $value);
        }
        $final_arr[] = $tmp;
    }
    return $final_arr;
}

function orgText($mysqli,$table,$key){
        $name = $mysqli->real_escape_string($key);
        $sql = "SELECT COUNT('$name') AS s,orgText FROM $table WHERE NAME='$name' GROUP BY orgText ORDER BY s DESC LIMIT 1";
        $orgText = "";
        $res = sqlQuery($mysqli, $sql);
        if (($row = $res->fetch_object()) != null) {
            $orgText = $row->orgText;
        }
        return trim($orgText,'"');
}

function TrendTypePie($mysqli, $username, $isAdmin, $dates_range, $trendTypeJArr, $jsonArr, $id=null){
     $saved_sql = "SELECT docid FROM savedsearchid WHERE id=$id";
      $relations_array_args = getRelationsArgs($mysqli, $trendTypeJArr,$jsonArr,$isAdmin,$username);
      
      $relations_array_args['saved_sql'] = $saved_sql;
      
      $vals = array_values($relations_array_args);
      
      return call_user_func_array('listRelations', $vals);     
}

function getRelationsArgs($mysqli, $trendTypeJArr,$jsonArr,$isAdmin,$username){
    $response = array();
    if (!empty($jsonArr) && sizeof($jsonArr)>1 && is_array($jsonArr) && $trendTypeJArr == "network"){
        $response['mysqli'] = $jsonArr['mysqli'] ?? $mysqli;
        $response['type'] = $jsonArr["type"];
        $response['subjectType'] = $jsonArr["subjectType"];
        $response['objectType'] = $jsonArr["objectType"];
        $response['groupBy'] = $jsonArr["groupBy"];
        $response['savedsearch'] = $jsonArr["savedsearch"];
        $response['subjectName'] = $jsonArr["subjectName"];
        $response['objectName'] = $jsonArr["objectName"];
        $response['relationList'] = $jsonArr["relationList"];
        $response['relationType'] = !empty($jsonArr["relationType"]) ? $jsonArr["relationType"] : "" ;
        $response['ignoreSubject'] = $jsonArr["ignoreSubject"];
        $response['lastSearch'] = $jsonArr["lastSearch"];
        $response['pastPeriod'] = $jsonArr["pastPeriod"];
        $response['connections'] = $jsonArr["connections"];
        
    }else{
        $response['mysqli'] = $_REQUEST["mysqli"] ?? $mysqli ?? null;
        $response['type'] = $_REQUEST["type"] ?? null;   
        $response['subjectType'] = !empty($_REQUEST["subjectType"]) && !in_array(strtolower($_REQUEST["subjectType"]), array('all','undefined', 'null'))  ? $_REQUEST["subjectType"] : null;
        $response['objectType'] = !empty($_REQUEST["objectType"]) && !in_array(strtolower($_REQUEST["objectType"]),array('all','undefined', 'null'))  ? $_REQUEST["objectType"] : null;    
        $response['groupBy'] = $_REQUEST["groupBy"] ?? null;
        $response['savedsearch'] = $_REQUEST["savedsearch"] ?? null;
        $response['subjectName'] = !empty($_REQUEST["subjectName"]) && $_REQUEST["subjectName"] !== 'undefined'  ? $_REQUEST["subjectName"] : null;
        $response['objectName'] = !empty($_REQUEST["objectName"]) && $_REQUEST["objectName"] !== 'undefined'  ? $_REQUEST["objectName"] : null;
        $response['relationList'] = $_REQUEST["relationList"] ?? null;
        $response['relationType'] = !empty($_REQUEST["relationType"]) ? $_REQUEST["relationType"] : "" ;
        $response['ignoreSubject'] = $_REQUEST["ignoreSubject"]  ?? null;
        $response['lastSearch'] = $_REQUEST["lastSearch"]  ?? null;
        $response['pastPeriod'] = $_REQUEST["pastPeriod"] ?? null;
        $response['connections'] = $_REQUEST["connections"] ?? null;
    }
    $response['isAdmin'] = $isAdmin;
    $response['username'] = $username;
    return $response; 
        
    }

function tables($tables,$savedSearchSqldocId,$username,$mysqli,$slices) {
    foreach ($tables as $table) {
        if ($isAdmin) {
            $sql = "SELECT SUM(occurrences) as s FROM $table WHERE occurrences IS NOT NULL " . $savedSearchSqldocId;
        } else {
            $sql = "SELECT SUM(occurrences) as s FROM $table WHERE username='$username'" . $savedSearchSqldocId;
        }
        write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        $res = sqlQuery($mysqli, $sql);
        if (!empty($res)) {
            $row = $res->fetch_object();
        }

        if ($table == "person_object") {
            $res_arr["Person"] = $row->s;
        }
        if ($table == "organizational_identity") {
            $res_arr["Organisation"] = $row->s;
        }
        if ($table == "place_object") {
            $res_arr["Places"] = $row->s;
        }
        if ($table == "date") {
            $res_arr["Date"] = $row->s;
        }
    }
    $response = loadResponseJSON($res_arr, "General", false, $slices);
    return $response;
}

function langRes($langs, $mysqli, $savedSearchSqldocId, $savedSearchSqlId, $username, $isAdmin, $slices) {
    if (!is_array($langs)) {
        $langs_sql = "SELECT DISTINCT(value) AS langs,docId FROM languages WHERE value IS NOT NULL AND value !='' AND value != 'Unknown language'" . $savedSearchSqldocId;
        write_to_log("PERMORMENCE", "piegraph sql - $langs_sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        $langs_res = sqlQuery($mysqli, $langs_sql);
        if (!empty($langs_res)) {
            while (($row = $langs_res->fetch_object()) != null) {
                $language = $row->langs;
                if (strpos($language, ';')) {
                    continue;
                }
                $all_langs[] = $row->langs;
            }
        }
    } else {
        $all_langs = $langs;
    }

    $sql = "SELECT l1.value, COUNT(DISTINCT dcid) AS DocCount FROM (";
    $sql .= "SELECT languages.value, languages.docId AS dcid FROM languages ";
    if (!$isAdmin) {
        $sql .= "INNER JOIN files ON docId = files.id AND files.username = '$username' " . $savedSearchSqlId;
    }
    $sql .= "GROUP BY dcid";
    $sql .= ") AS l1 GROUP BY l1.value";
    write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
    $res = sqlQuery($mysqli, $sql);
    while ($res && $row = $res->fetch_object()) {
        $l = $row->value;
        if (!in_array($l, $all_langs)) {
            continue;
        }

        $res_arr[$l] = $row->DocCount;
    }
    $response = loadResponseJSON($res_arr, "Languages", false, $slices);
    return $response;
}

function fileFormat($mysqli, $savedSearchSqlId, $username, $isAdmin, $slices) {
    $sql = "SELECT DISTINCT file_format FROM files WHERE file_format IS NOT NULL" . $savedSearchSqlId;
    write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
    $res = sqlQuery($mysqli, $sql);
    $arr = array();
    if (!empty($res)) {
        while ($row = $res->fetch_object()) {
            $arr[$row->file_format] = $row->file_format;
        }
    }

    foreach ($arr as $p) {
        if ($isAdmin) {
            $sql = "SELECT COUNT(file_format) AS p FROM files WHERE file_format='$p'" . $savedSearchSqlId;
        } else {
            $sql = "SELECT COUNT(file_format) AS p FROM files WHERE file_format='$p' AND username='$username'" . $savedSearchSqlId;
        }
        write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        $res = sqlQuery($mysqli, $sql);
        if (!empty($res)) {
            $row = $res->fetch_object();
        }
        $res_arr[$p] = $row->p;
    }

    $response = loadResponseJSON($res_arr, "File Format", false, $slices);
    return $response;
}


function fetchMappedCaption($mysqli,  $limit, $field, $mappedCaption, $max, $id, $free_text_str){
    $docs_arr_sql = "SELECT docId FROM savedsearchid WHERE id=$id";
    if($max){
        $sql = getMaxCaptionSql($field, $mappedCaption, $free_text_str, $limit, $docs_arr_sql);
    }
    else{
        $sql = getMappedCaptionSql($field, $mappedCaption, $free_text_str, $limit, $docs_arr_sql);
    }

    $results = sqlQuery($mysqli, $sql);
    $map_results = array();
    while (!is_null($row = $results->fetch_object())) {
        $map_results[] = array("label" => $row->m, "scale"=> intval($row->c));
    }
    $response["amount"] = $map_results;
    return $response;
}


function risks($risks, $mysqli, $savedSearchSqlId, $username, $isAdmin, $slices) {
    foreach ($risks as $p) {
        if ($p === "None") {
            $sql = "SELECT COUNT(id) AS p FROM files WHERE threat_rating IS NULL" . $savedSearchSqlId;
        } else {
            if ($isAdmin) {
                $sql = "SELECT COUNT(threat_rating) AS p FROM files WHERE threat_rating=LOWER('$p')" . $savedSearchSqlId;
            } else {
                $sql = "SELECT COUNT(threat_rating) AS p FROM files WHERE threat_rating=LOWER('$p') AND username='$username'" . $savedSearchSqlId;
            }
        }
        write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        $res = sqlQuery($mysqli, $sql);
        if (!empty($res)) {
            $row = $res->fetch_object();
        }
        $res_arr[$p] = $row->p;
    }

    $response = loadResponseJSON($res_arr, "Priority", false, $slices);
    return $response;
}

function orients($mysqli, $savedSearchSqlId, $username, $isAdmin, $slices) {
    $sql = "SELECT DISTINCT ideological_stream AS i FROM files WHERE ideological_stream IS NOT NULL" . $savedSearchSqlId;
    write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
    $res = sqlQuery($mysqli, $sql);
    $i = 0;
    $PoliticalOrientation = array();
    if (!empty($res)) {
        while ($row = $res->fetch_object()) {
            $PoliticalOrientation[$i++] = $row->i;
        }
    }

    $PoliticalOrientation[$i] = "None";

    foreach ($PoliticalOrientation as $p) {
        if ($p === "None") {
            $sql = "SELECT COUNT(id) AS p FROM files WHERE ideological_stream IS NULL" . $savedSearchSqlId;
        } else {
            if ($isAdmin) {
                $sql = "SELECT COUNT(ideological_stream) AS p FROM files WHERE ideological_stream=LOWER('$p')" . $savedSearchSqlId;
            } else {
                $sql = "SELECT COUNT(ideological_stream) AS p FROM files WHERE ideological_stream=LOWER('$p') AND username='$username'" . $savedSearchSqlId;
            }
        }
        write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        $res = sqlQuery($mysqli, $sql);
        if (!empty($res)) {
            $row = $res->fetch_object();
        }
        $res_arr[$p] = $row->p;
    }
    $response = loadResponseJSON($res_arr, "Political Orientation", false, $slices);
    return $response;
}

function religious($mysqli, $savedSearchSqlId, $username, $isAdmin, $slices) {
    $sql = "SELECT DISTINCT islamic_sect AS i FROM files WHERE islamic_sect IS NOT NULL" . $savedSearchSqlId;
    write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
    $res = sqlQuery($mysqli, $sql);
    $i = 0;
    $religiousAffiliation = array();
    if (!empty($res)) {
        while ($row = $res->fetch_object()) {
            $religiousAffiliation[$i++] = $row->i;
        }
    }
    $religiousAffiliation[$i] = "None";

    foreach ($religiousAffiliation as $p) {
        if ($p === "None") {
            $sql = "SELECT COUNT(id) AS p FROM files WHERE islamic_sect IS NULL" . $savedSearchSqlId;
        }
        if ($p !== "None") {
            if ($isAdmin) {
                $sql = "SELECT COUNT(islamic_sect) AS p FROM files WHERE islamic_sect=LOWER('$p')" . $savedSearchSqlId;
            } else {
                $sql = "SELECT COUNT(islamic_sect) AS p FROM files WHERE islamic_sect=LOWER('$p') AND username='$username'" . $savedSearchSqlId;
            }
        }
        write_to_log("PERMORMENCE", "piegraph sql - $sql -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
        $res = sqlQuery($mysqli, $sql);
        if (!empty($res)) {
            $row = $res->fetch_object();
        }
        $res_arr[$p] = $row->p;
    }
    $response = loadResponseJSON($res_arr, "Religious Affiliation", false, $slices);
    return $response;
}

function stringToSqlString( $delimiter, $phrase, $mysql){
		
	$arr = explode($delimiter,$phrase);
	$isFirst = true;
	
	foreach ($arr as $value) {
		
		if($isFirst){
			$newPhrase .= "'" . $mysql->real_escape_string($value);
			$isFirst = false;
		}else{
			$newPhrase .= "','" . $mysql->real_escape_string($value);
		}
	}
	
	return $newPhrase . "'";
}

function loadResponseJSON($results_array, $title, $isEntity, $slices){
	$resp = array();
	if($isEntity){
		$holder = 1;
		$other = 0;
		while($row = $results_array->fetch_object()){
			
			if($holder <= $slices){
				
				$resp[$row->n] = (int)$row->c;
				$holder++;
			}else{
				$other += $row->c;
			}
		}
	}else{
		
		foreach ($results_array as $k => $v){
			
			$tmp = array($k,(int)$v);
			$resp[] = $tmp;			
		}
		$response["title"] = $title;
	}
	
	$response["amount"] = $resp;
	
	return $response;
	
}

function loadConceptData($tableData,$connection, $isAdmin, $username, $slices, $dates_range, $savedSearchSql, $saved_sql_id, $v2 = false){
	
	$data = explode( ";", $tableData );
	if ($data[0] === "conceptType"){
		$conceptTable = strtolower($data[1]);
		$conceptName = $data[2];
		$type = true;
	}else{
		$conceptTable = strtolower($data[0]);
		$conceptName = $data[1];
		$type = false;
	}
	$hasText = true;
	if($conceptName !== "All"){
		$response["hasTwoParts"] = "true";
		$hasText = false;
	}
	$title = ucfirst (str_replace( "_", " ", $conceptTable));
	
	$results = getTableDataForPie($conceptTable,$connection, $isAdmin, $username, $slices, $hasText, $dates_range, $savedSearchSql, $type, $saved_sql_id);
	
	$holder = 1;
	$resp = array();
	$tmp = "";
	while($row = $results->fetch_object()){
	    if($v2){
	        if(!$hasText){
	            $resp[] = array("label"=>$row->n, "scale"=>intval($row->c));
	            $tmp = $row->n;
	        }
	        
	        if($holder <= $slices){
	            $resp[] = array("label"=>ucfirst($row->n), "scale"=>intval($row->c));
	            $holder++;
	        }     
	    }
	    else{
    		if(!$hasText){
    			$resp[$row->n] = (int)$row->c;
    			$tmp = $row->n;
    		}
    		
    		if($holder <= $slices){
    			$resp[ucfirst($row->n)] = (int)$row->c;
    			$holder++;
    		}
	    }
	}

	$counter=0;
	foreach ($resp as $key=>$value) {
		$tmp = array($key, $value);
		$final_arr[] = $tmp;
		$counter += $value;
	}
	
	$response["amount"] = $final_arr;
	$response["title"] = $title;
	return $response;
}

/**Returns the names of the requested table as an object*/
function getTableDataForPie($table,$connection, $isAdmin, $username, $limit, $hasLimit, $dates_range, $savedSearchSql, $type, $saved_sql_id = null){
	global $skipDuplicate;
	
	
	$fromDate = $dates_range["from"];
	$toDate = $dates_range["to"];
	
	$hiddenInstancesSql = "";
	if ($type)
		$name = $table . ".type";
	else
	{
		$name = $table . ".name";
		//a patch for hiding problematic instances in pie
		global $hideConceptInstances;
		if (!empty($hideConceptInstances) && array_key_exists($table, $hideConceptInstances))
			$hiddenInstancesSql = " AND $name NOT IN ('" . join("','", $hideConceptInstances[$table]) . "') ";
	}
	
	if(!empty($fromDate) && !empty($toDate)){
	    $date_sql ="AND (files.date BETWEEN '$fromDate' AND '$toDate')";
	}
	else{
	    $date_sql="";
	}
	$saved_sql = empty($saved_sql_id) ? "TRUE" : "files.id IN (SELECT docid FROM savedsearchid WHERE id=$saved_sql_id)";
	
		
	if ($skipDuplicate)				
		$query = "SELECT $name AS n, SUM(unique_files.count_doc) AS c FROM $table,files,unique_files WHERE  $saved_sql AND $table.docId=files.id AND unique_files.id=files.id $date_sql $hiddenInstancesSql $savedSearchSql";
	else
		$query = "SELECT $name AS n, COUNT(DISTINCT($table.docId)) AS c FROM $table,files WHERE $saved_sql AND $table.docId=files.id $date_sql $hiddenInstancesSql $savedSearchSql";
	
	
	if(!$isAdmin)
		$query .= " AND $table.username='$username' ";
	
	$query .= " GROUP BY n ORDER BY c DESC";
	
	if($hasLimit)
		$query .= " LIMIT $limit";
	write_to_log("PERMORMENCE", "piegraph sql - $query -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
	$results = sqlQuery($connection, $query);
	
	return $results;
	
}

function loadSentimentData($mysqli, $sentimentData, $table, $isAdmin, $username, $dates_range, $savedSearchSql){
	
	$sentimentOptions = array("pro", "anti","none");
	$aggArr = array("person"=>"person_object","org"=>"organizational_identity");
	$entityResults = array();
	
	if(in_array($table,$aggArr))
		$aggData = getAggregatedData($mysqli,$sentimentData, $aggArr);
	
	foreach ($sentimentOptions as $sentiment){
		
		if((in_array($table, $aggArr)) && (sizeof($aggData)>1)){
			$entityResults[$sentiment][0] = $sentiment;
			$name = implode("','", $aggData);
			//$name = substr($name,1,-1);			
			/*foreach ($aggData as $name){
				$name = $mysqli->real_escape_string($name);
				$entitySentimentData = getSumEntitySentiment($mysqli,$table,$name,$username,$isAdmin,$sentiment,$dates_range,$savedSearchSql);
				$entityResults[$sentiment][1] += $entitySentimentData[1];
			}*/
			$entitySentimentData = getSumEntitySentiment($mysqli,$table,$name,$username,$isAdmin,$sentiment,$dates_range,$savedSearchSql,false);
			$entityResults[$sentiment][1] += $entitySentimentData[1];
			continue;
		}
		
		$entitySentimentData = getSumEntitySentiment($mysqli,$table,$sentimentData,$username,$isAdmin,$sentiment,$dates_range,$savedSearchSql);
		$entityResults[$sentiment] = $entitySentimentData;
	}

	$s = 0;
	foreach ($entityResults as $entityResult){
		$s += $entityResult[1];
	}
	
	if($s<1){
		$response["noData"] = "true";
	}
	
	$response["amount"] = $entityResults;
	$response["isSentiment"] = "true";
	return $response;
	
}

function getSumEntitySentiment($connection,$table,$sentimentData,$username,$isAdmin,$sentiment, $dates_range, $savedSearchSql,$runAnalyze = true){
	global $entityMatcher,$skipDuplicate;
	require_once 'searchWord.php';
	
	$fromDate = $dates_range["from"];
	$toDate = $dates_range["to"];
	
	if($sentiment == "none"){
		$sentiment = "";
	}
	$tempfreeTextInput = null;
	
 	if($table === 'organizational_identity' && $runAnalyze){
 		$tempfreeTextInput = analyzeWord($sentimentData, 'Organizational-identity');
 	}
 	if($table === 'place_object' && $runAnalyze){
 		$tempfreeTextInput = analyzeWord($sentimentData, 'Place-object');
 	}
 	if(strlen($tempfreeTextInput)>1){
 	    $sentimentData = array($sentimentData);
 		$sentimentData[] = $tempfreeTextInput;
 		$sentimentData = implode("','", $sentimentData);
 	}
 	
 	if(!empty($fromDate) && !empty($toDate)){
 	    $date_sql ="AND (files.date BETWEEN '$fromDate' AND '$toDate')";
 	}
 	else{
 	    $date_sql="";
 	}
 	
 	if($table === 'person_object'){
		$perMegaSqls = buildSearchEntitiesSql($connection, array(array("text" => $sentimentData, "type" => "person,email")));
		$searchPerSql = buildFinalSql($perMegaSqls, $connection, NULL);
		if ($skipDuplicate)								
 			$query = "SELECT SUM(count_doc) AS sum FROM unique_files WHERE id IN(SELECT DISTINCT($table.docId) AS id FROM $table,files, ($searchPerSql) AS searchedPerson WHERE files.id=$table.docId $date_sql AND person_object.docId = searchedPerson.docId AND person_object.id = searchedPerson.instanceId " . $savedSearchSql;
 		else
 			$query = "(SELECT COUNT(DISTINCT($table.docId)) AS sum FROM $table,files, ($searchPerSql) AS searchedPerson WHERE files.id=$table.docId $date_sql AND person_object.docId = searchedPerson.docId AND person_object.id = searchedPerson.instanceId " . $savedSearchSql;
 	}
 	else{
 		if ($skipDuplicate)	
 			$query = "SELECT SUM(count_doc) AS sum FROM unique_files WHERE id IN(SELECT DISTINCT($table.docId) AS id FROM $table,files WHERE files.id=$table.docId $date_sql AND ($table.name IN('$sentimentData') or $table.orgText IN('$sentimentData'))" . $savedSearchSql;
 		else 
 			$query = "(SELECT COUNT(DISTINCT($table.docId)) AS sum FROM $table,files WHERE files.id=$table.docId $date_sql AND ($table.name IN('$sentimentData') or $table.orgText IN('$sentimentData'))" . $savedSearchSql;
 	}
 		
 	if(!$isAdmin)
 		$query .= " AND $table.username='$username'"; 		
 	if(strlen($sentiment)>1){
 		$query .= " AND $table.$sentiment IS NOT NULL)";
 		$sum[] = $sentiment;
 	}
        else{
 		$query .= " AND ($table.negative IS NULL AND $table.positive IS NULL))";
 		$sum[] = "none";
 	}
 	 
 	
 	write_to_log("PERMORMENCE", "piegraph sql - $query -" . date('H:i:s'.substr((string)microtime(), 1, 8)));
 	$results = sqlQuery($connection, $query);
 	while($row = $results->fetch_object()){
 			
 		$sum[] = $row->sum;
 	}
 	return $sum;
 	
}

function loadFieldData($connection, $field, $slices,$isAdmin, $username, $tableData, $dates_range, $savedSearchSql, $fromDate, $toDate){
    global $skipDuplicate, $extraColumns;
    $hasText = false;
    if (!empty($tableData)) {
        $response["hasTwoParts"] = "true";
        $hasText = true;
    }

    $fromDate = $dates_range['from'] ?? null;
    $toDate = $dates_range['to'] ?? null;
    if (!empty($fromDate) && !empty($toDate)) {
        $date_sql = "(files.date BETWEEN '$fromDate' AND '$toDate')";
    } else {
        $date_sql = " TRUE ";
    }
    
    if (!$isAdmin) {
        $user_sql = "files.username = '$username'";
    } else {
        $user_sql = "TRUE";
    }
    
    if (!empty($savedSearchSql) || !empty($date_sql) || !$isAdmin) {
        $hasConditions = true;
    }

    if ($skipDuplicate && !array_key_exists($field, $extraColumns)) {
        $query = "SELECT $field.value AS v, SUM(unique_files.count_doc) AS c FROM $field ";
        if ($hasConditions) {
            $query .= ",files,unique_files WHERE $field.docId = files.id AND $user_sql AND unique_files.id=files.id AND $date_sql AND $field.value IS NOT NULL" . $savedSearchSql;
        } else {
            $query .= "WHERE TRUE";
        }
    } else {
        $query = "SELECT $field.value AS v, COUNT(DISTINCT($field.docId)) AS c FROM $field ";
        if ($hasConditions) {
            $query .= ",files WHERE $field.docId = files.id AND $user_sql AND $date_sql AND $field.value IS NOT NULL" . $savedSearchSql;
        } else {
            $query .= "WHERE TRUE";
        }
    }
    
    //add exception folr languages - ignore unknown languages 
    if(strcmp($field,'languages')==0){
        $query .= " AND  languages.value != 'Unknown language'";
    }

    $query .= " GROUP BY v ORDER BY c DESC";
    write_to_log("PERMORMENCE", "piegraph sql - $query -" . date('H:i:s' . substr((string) microtime(), 1, 8)));
    $results = sqlQuery($connection, $query);
    $result = array();
    $tmp = "";
    while (!is_null($row = $results->fetch_object())) {

        if ($hasText) {
            if ($row->v == $tableData) {
                $tmp = $row->v;
                $result[$row->v] = $row->c;
                continue;
            }
            $result["Other"] += $row->c;
            continue;
        }
        $result[$row->v] = $row->c;
    }

    if (empty($tmp) && $hasText)
        $response["noData"] = "empty";

    if ($hasText) {
        //The front-end will get the same order everytime if there are the relative slice
        if ($result[0] != "Other") {
            $b = array($tmp, "Other");
            $c = array();
            foreach ($b as $index) {
                $c[$index] = $result[$index];
            }
            $result = $c;
        }
    }


    if (!$hasText) {
        $result = array_slice($result, 0, $slices);
    }

    $counter = 0;
    foreach ($result as $key => $value) {
        $tmp = array($key, $value);
        $final_arr[] = $tmp;
        $counter += $value;
    }

    $response["amount"] = $final_arr;    

    return $response;
}

