<?php
/**
 * Dashboard module
 * 
 * Common functions used in both front and back-office to 
 * displays dashboard informations and charts
 * 
 * @package linea21\modules\dashboard
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */


/**
 * unit_javascript_validation
 * @return void
 */
if(!function_exists('unit_javascript_validation')) {
    function unit_javascript_validation() {

        $js = '$("#min_value, #max_value, #threshold_value").change(function() {
				var minVal = $("#min_value").val().replace(",", ".");
				var maxVal = $("#max_value").val().replace(",", ".");
				var thresholdVal = $("#threshold_value").val().replace(",", ".");
				var dashboardViz = $("#sdi_dashboard_viz").find(":selected").val();
				console.log("min : " + minVal + " - max : " + maxVal + " - threshold : " + thresholdVal);
				if(isNumeric(minVal) && isNumeric(maxVal) &&  maxVal <= minVal )  alertify.error("'._t("sdi","object_minmax_error").'");
				
				// if thresold and min are provided, be sure thresold is superior to min
				if(isNumeric(minVal) && isNumeric(thresholdVal) && thresholdVal < minVal )  alertify.error("'._t("sdi","object_minthreshold_error").'");
				// if thresold and max are provided, be sure thresold is inferior to max
				if(isNumeric(maxVal) && isNumeric(thresholdVal) && thresholdVal > maxVal )  alertify.error("'._t("sdi","object_maxthreshold_error").'");
				
				// we add check if gauge
				if(dashboardViz == "gauge") {
						if(!isNumeric(maxVal) ||  !isNumeric(minVal)  ||  !isNumeric(thresholdVal))  alertify.error("'.sprintf(_t('sdi','object_gauge_error1'), $GLOBALS['lang']['sdi']['select_dashboard_viz']['gauge']).'");
						if(isNumeric(maxVal) && isNumeric(thresholdVal) && thresholdVal >= maxVal )  alertify.error("'.sprintf(_t('sdi','object_gauge_error2'), $GLOBALS['lang']['sdi']['select_dashboard_viz']['gauge']).'");
						if(isNumeric(minVal) && isNumeric(thresholdVal) && thresholdVal <= minVal )  alertify.error("'.sprintf(_t('sdi','object_gauge_error3'), $GLOBALS['lang']['sdi']['select_dashboard_viz']['gauge']).'");
				}
			});';
        footerAddInlineJS($js);
    }
}


/**
 * get_unique_indicators
 * @param array $sdi
 * @param boolean $noorphan
 * @return array
 */
if(!function_exists('get_unique_indicators')) {
    function get_unique_indicators($sdi, $noorphan = false) {
        
        $uniques = [];
        $cnt = 0;
        if($noorphan) {
            $levelExclude = 0;
        } else {
            $levelExclude = -999; // wich has no effect
        }
        
        if(!is_array($sdi)) return array('indicators' => [], 'count' => 0);
        
        foreach ($sdi as $s) {
            if(!in_array($s['sdii_id'], $uniques) && $s['sdii_level'] != $levelExclude) {
                array_push($uniques, $s['sdii_id']);
                $cnt++;
            }
        }
        return array('indicators' => $uniques, 'count' => $cnt);
    }
}

/**
 * _get_results_attributes
 * @param array $json_arr
 * @return array 
 * @todo @simo remove ? - not used
 */
if(!function_exists('_get_results_attributes')) {
    function _get_results_attributes(array $json_arr) {
        
        $a = [];
        // we set array key we have to iterate from
        // opendatasoft : 'results'
        // koumoul (default query type) : 'results'
        // arcgis : 'features' + attributes field in loop
        
        if(isset($json_arr['features'])) {
            $a['main_field'] = 'features';
            $a['sec_field'] = 'attributes';
        }
        if(isset($json_arr['results']))  $a['main_field'] = 'results';
        
        return $a;
    }
}

/**
 * _format_results
 * Format results all the same way, whatever it comes from distinct API with different formats
 * Resulting array is like so : array('results' => array(0 => array( 'value' => 100, 'date' => '2022', 'something' => 'xxx'), 1 => array( 'value' => 100, 'date' => '2022', 'something' => 'xxx'));
 * @param array $json_arr
 * @return array
 */
if(!function_exists('_format_results')) {
    function _format_results(array $json_arr) {
        
        $a = [];

        // arcgis : 'features' + 'attributes' field in loop
        // geoserver : 'features' + 'properties' field in loop
        if(isset($json_arr['features'])) {
            $tmp = [];
            foreach ($json_arr['features'] as $rec) {
                if(isset($rec['attributes'])) array_push($tmp, $rec['attributes']); // arcgis 
                if(isset($rec['properties'])) array_push($tmp, $rec['properties']); // geoserver
            }
            $a['results'] = $tmp;
        }
        
        // opendatasoft and  koumoul (default query type) : 'results'
        if(isset($json_arr['results']))  $a = $json_arr;
        
        // @todo for koumoul aggregation see : https://data.ademe.fr/data-fair/api/v1/datasets/investissements-d'avenir-projets/values_agg?field=Code_EPCI&format=json&metric=sum&metric_field=Co%C3%BBt_total_du_projet&agg_size=99&qs=Code_EPCI%3A%22200018166%22&size=99&select=Total_autoris%C3%A9%2CCo%C3%BBt_total_du_projet%2CCode_EPCI
        // print_r($a);
        return $a;
    }
}


/**
 * api_update_indicators
 * @param array $sdi
 * @param boolean $initialImport
 * @return boolean
 */
if(!function_exists('api_update_indicators')) {
    function api_update_indicators($sdi, $initialImport = false) {
        
        global $sql_object;
        $debug = false;
        $scale_id = 1;

        # https://docs.guzzlephp.org/en/5.3/quickstart.html
        // use GuzzleHttp\Client;
        // $client = new Client();
        // @see https://stackoverflow.com/questions/20847633/limit-connecting-time-with-guzzle-http-php-client
        // we add a timeout to client not to block script
        $client = new GuzzleHttp\Client(['timeout' => 3, 'connect_timeout' => 3]);
        
        foreach ($sdi as $indicator) {
            
            // if API is enabled only
            if($indicator['sdii_api_enabled'] == 'Y') {
                
                // initial import, we set given year if passed
                if($initialImport) {
                    if(!empty($indicator['sdii_api_getvalues-since'])) $since = $indicator['sdii_api_getvalues-since'];
                    else $since='1900'; // we fake the date to import everything
                }
                // $sql_object -> DBQuery("DELETE FROM l21_sdi_value WHERE sdiv_sdi_info ='" . $indicator['sdii_id'] . "' AND sdiv_scale ='" .$scale_id."'");
                
                // we select last entered values and compare with frequency to see if we have to execute update routine or not
                // for all cases (even initial import) - if set, we get the latest indicator value and date for the given scale
                $lastV = $sql_object -> DBSelect(SQL_getlastInsertByPublicationDate($indicator['sdii_id'], $scale_id));
                
                // if exists, it can override $since variable to prevent multiple imports on initial import - security
                if(isset($lastV[0]['sdiv_value']) ) $since =  $lastV[0]['sdiv_date_published'];
                // we get threshold from old values or default value, if null, replacing by empty string
                if(isset($lastV[0]['sdiv_value']) && !is_null($lastV[0]['sdiv_threshold'])) {
                    $threshold = $lastV[0]['sdiv_threshold'];
                } else {
                    if(is_numeric($indicator['sdii_threshold_value'])) $threshold = $indicator['sdii_threshold_value'];
                    else $threshold = '';
                }
                
                // we check if url is responding, if not we log the error
                try {
                    $response = $client->get($indicator['sdii_api_url']);
                    $code = $response->getStatusCode();
                    
                    // no test on $indicator['sdii_api_getvalues'] == 'Y', because it is supposed to be done before calling the function
                    if($code == '200') {
                        
                        // we convert json response to array
                        $records = json_decode($response->getBody(), true);
                        // print_r($records);
                        
                        // we set fields structures, based on resulting json file
                        // $attrs = _get_results_attributes($records);  // @todo @simo remove ? - not used
                        
                        // we format retrieved json file if needed
                        $records = _format_results($records);
                        
                        // foreach ($records[$attrs['main_field']] as $rec) { // @todo @simo remove ? - not used
                        foreach ($records['results'] as $rec) {
                            
                            //if(isset($rec[$attrs['sec_field']])) $rec = $rec[$attrs['sec_field']];  // @todo @simo remove ? - not used

                            if($debug) print_r($rec);
                            
                            $format = "Y-m-d"; // date object format
                            
                            // we create date object for comparison
                            // if only year is passed, we add month and day and create the object
                            if(strlen($rec[$indicator['sdii_api_datefield']]) == 4) {
                                $rec[$indicator['sdii_api_datefield']] .= '-12-31';
                                $recordDate = DateTime::createFromFormat($format, $rec[$indicator['sdii_api_datefield']]);
                           // else default format is similar to : "2012-01-01T00:00:00+00:00"
                            } else {
                                $recordDate = new DateTime($rec[$indicator['sdii_api_datefield']]);
                            }
                            // we create a formatted date for display
                            $formattedDate = $recordDate->format("Y-m-d"); // Output format : 2023-06-04
                            
                            // only year is passed to since, we add month and day
                            if(strlen($since) == 4) $since .= '-01-01';                            
                            
                            // we get last date
                            $lastvalue = DateTime::createFromFormat($format, $since);
                            $formattedLastvalue = $lastvalue->format("Y-m-d");
                            // we add interval to latest retrieved date
                            $triggerDate = $lastvalue;
                            $triggerDate->modify('+' . $indicator['sdii_frequency'] . 'days');
                            
                            // we finally compare $triggerDate with $recordDate to check if we have to add records in database
                            if($debug) echo "<p>Indicator id : ".$indicator['sdii_id'] ." [comparison] current record :  " . $formattedDate . ' / last value: ' . $formattedLastvalue . " / trigger date : " . $triggerDate->format("Y-m-d") . '</p>';
                            
                            if($recordDate >= $triggerDate) {
                                if($debug) echo ">> AJOUT<br>";
                                
                                if(isset($rec[$indicator['sdii_api_valuefield']]) && isset($rec[$indicator['sdii_api_datefield']])) {
                                    $a =  [];
                                    $a[0] = sys_number_format(round($rec[$indicator['sdii_api_valuefield']], $indicator['sdii_api_decimal'])); // value
                                    $a[1] = $indicator['sdii_id']; // indicator id
                                    $a[2] = $threshold;
                                    $a[3] = $scale_id; // scale id
                                    $a[4] = $formattedDate; // date (format yyyy-mm-dd)
                                    $a[5] = 'p'; // status
                                    $a[6] = _t('sdi', 'api-retrieved'); // comment
                                    $a[7] = 'N'; // comment status
                                    $a['user_id'] = 1;
                                    
                                    if($debug) echo '<hr>';
                                    if($debug) print_r($a);
                                    if($debug) echo '<hr>';
                                    
                                    $sdio = new sdi;
                                    $integrity = $sdio->CheckDataIntegrity_value($a, $sql_object);
                                    
                                    if(!is_string($integrity)) {
                                        
                                        $r = $sdio->AddValue($a, $sql_object);
                                        if(is_numeric($r)) logfile(LOG_MAINFILE, array('[API call]', 'SUCCESS - adding values to db', 'indicator : ' . $indicator['sdii_id'], 'added value ID : '. $r));
                                    } else {
                                        // CheckDataIntegrity_value failed
                                        logfile(LOG_MAINFILE, array('[API call]', 'ERROR - CheckDataIntegrity_value() failed', 'indicator : ' . $indicator['sdii_id'], $indicator['sdii_api_url'], 'error message : '. strip_tags((string) $integrity)));
                                        return false;
                                    }
                                } else {
                                    // given keys are inconsistent
                                    logfile(LOG_MAINFILE, array('[API call]', 'ERROR - given array keys are inconsistents : ' . $indicator['sdii_api_valuefield'] . ' / "'. $indicator['sdii_api_datefield']. ' - check url result and change fields values', 'indicator : ' . $indicator['sdii_id'], $indicator['sdii_api_url']));
                                    return false;
                                }
                                
                            }
                            
                        }
                    }
                    // if 404 exception is caught - others errors as well
                } catch (Exception $e) {
                    logfile(LOG_MAINFILE, array('[API call]', 'URL not responding', $indicator['sdii_api_url'], 'indicator : ' . $indicator['sdii_id'], $e->getMessage()));
                    return false;
                }
                
            } // closing $indicator['sdii_api_enabled'] == 'Y' test
            
        }
        
        return true;
        
    }
}

if(!function_exists('get_associated_projects')) {
    
    function get_associated_projects($id) {
        
        $str = '';
        $projects = $GLOBALS['sql_object'] ->DBSelect(SQL_get_associated_projects($id));
        
        if(isset($projects[0]['project_name'])) {
            
            $str = '<ul>%s</ul>';
            $content = '';
            
            foreach($projects as $project) {
                $content .= '<li><a href="index.php?rub=project&amp;todo=det&amp;id='.$project['project_id'].'">'.formatText($project['project_name'], '2HTML').'</a></li>';
            }
            
            $str = sprintf($str, $content);
            
        }
        return empty_none($str);
    }
    
}


if(!function_exists('getMenuFilter')) {
    
    /**
     * getMenuFilter()
     *
     * @param array $tags
     * @return string
     */
    function getMenuFilter ($tags = array()) {
        
        footerAddJS('../lib/js/jquery-tag-filtering/jquery.tagfiltering.simo.js');
        addDynamicCSS('../lib/js/jquery-tag-filtering/jquery.tagfiltering.simo.css');
        
        // one call to apply many filters from several lists (ul) with same css class and using distincts data-* attributes
        // to comment if individual filtering are applied
        footerAddInlineJS('$("ul.filter-menu").tagfiltering({target_attr:"data-tags,data-types", separator:";", callback:compute_values});');
        
        // export Javascript part
        $js =
        '$("#save-export").click( function() {
				var to_export = [];
				var $items = $(".filter-items").children();
            
				// we populate the array to send with ajax
				$items.each(function() {
					if($(this).is(":visible"))  {
						var curval = $(this).attr("data-id");
						// we check if not in array yet
						if($.inArray( curval,to_export) == -1) to_export.push($(this).attr("data-id"));
					}
				});
            
				alertify.success("'. _t('items', 'selected').'".replace("%s", to_export.length));
				    
				$.ajax({
					   type: "POST",
					   data: {to_export:to_export},
					   url: "../dashboard/_ajax_save.php?token='.SECRET_KEY.'",
					   success: function(msg){
					   		window.location = "../admin/index.php?rub=dashboard&todo=report";
					   }
					});
			});';
        footerAddInlineJS($js);
        
        $filter_menu  = '<div class="toggle_title"><a id="indicator-filter" href="#">'._t('filter', 'content').'</a></div>';
        $filter_menu .= '<div class="contboxwhite filters" style="display:none">';
        $filter_menu .= getTypesMenuFilter ($GLOBALS['lang']['sdi']['select_type']);
        $filter_menu .= getTagsMenuFilter ($tags);
        if ($GLOBALS['l21auth']->hasRight('dashboard')) $filter_menu .= '<div class="end"><a href="#" id="save-export" class="button regular"><i class="fa fa-download" aria-hidden="true"></i></i> '._t('btn', 'save_export').'</a></div><br class="brendstep" />';
        $filter_menu .= '</div>';
        
        return $filter_menu;
    }
}


if(!function_exists('getTypesMenuFilter')) {
    
    /**
     * getTagsMenuFilter()
     *
     * get tags menu filter
     * @param array $tags
     * @return string
     */
    function getTypesMenuFilter ($types) {
        
        
        $filter = '';
        
        // we display filter if at least 1 values exists
        if(count($types) >= 1) {
            $options = '';
            $a = array();
            foreach($types as $key => $val) {
                if(!in_array($key, $a)) {
                    $options .= '<li data-tag="'.formatText(mb_ucfirst($val), '2HTML').'" id="type-'.$key.'"><a class="facet" href="#">'.formatText(mb_ucfirst($val), '2HTML').'</a></li>';
                    array_push($a, $key);
                }
            }
            
            $filter .= sprintf('<div class="type-filter item-filter"><h3>%s</h3><ul class="filter-menu types-filter-menu">%s</ul></div>', _t('filter', 'by_type'), $options);
            
        }
        return $filter;
    }
    
}

if(!function_exists('getTagsMenuFilter')) {
    
    /**
     * getTagsMenuFilter()
     *
     * get tags menu filter
     * @param array $tags
     * @return string
     */
    function getTagsMenuFilter ($tags) {
             
        
        $filter = '';
        
        // we display filter if at least 1 values exists
        if(count($tags) >= 1) {
            $options = '';
            $a = array();
            foreach($tags as $item) {
                if(!in_array($item['tag_id'], $a)) {
                    $options .= '<li data-tag="'.$item['tag_name'].'" id="tag-'.$item['tag_id'].'"><a class="facet" href="#">'.$item['tag_name'].'</a></li>';
                    array_push($a, $item['tag_id']);
                }
            }
            
            $filter .= sprintf('<div class="tag-filter item-filter"><h3>%s</h3><ul class="filter-menu tags-filter-menu">%s</ul></div>', _t('filter', 'by_tag'), $options);
            
        }
        return $filter;
    }
    
}

if(!function_exists('getPositionfromValue')) {

	function getPositionfromValue ($value, $indicator, $threshold_value) {

		if ($indicator['sdii_max_value']==0) {
			$max_value=0.000001;
		} else {
			$max_value=$indicator['sdii_max_value'];
		}
		if ($indicator['sdii_min_value']==0) {
			$min_value=0.000001;
		} else {
			$min_value=$indicator['sdii_min_value'];
		}
		if ($indicator['sdii_threshold_relative']=='Y') {
			$unitup	= 50 / ($max_value - $threshold_value);
			$unitdown	= 50 / ( $threshold_value - $min_value);
			if ($value > $threshold_value) $result = 50 + (($value - $threshold_value) *$unitup);
			else $result = 50 -( ($threshold_value - $value ) *$unitdown );
		}
		else{
			if(($max_value - $threshold_value) <> 0) $unitdown = 50 / ($max_value - $threshold_value);
			if(($threshold_value - $min_value) <> 0) $unitup = 50 / ($threshold_value - $min_value);
			if ($value > $threshold_value) $result=50 -( ($value - $threshold_value) * $unitdown);
			else $result = 50 + (( $threshold_value - $value ) * $unitup);
		}
		return (int)$result;
	}

}

if(!function_exists('getIndicatorInfoBox')) {

	function getIndicatorInfoBox($current_value, $indicator, $threshold_value, $class= '') {

		if($indicator['sdii_nature'] == 'quantitative') {
			$display_min = empty_nc($indicator['sdii_min_value']);
			$display_max = empty_nc($indicator['sdii_max_value']);
			$display_threshold = empty_nc(fnumber_format($threshold_value, 'auto', false));
		} else {
			
			$a = getBooleanValues($indicator);
			if($a) {
				$index = array();
				foreach ($a as $key => $value) {
					array_push($index, $key);
				}
				
				$display_min = $a[min($index)];
				$display_max = $a[max($index)];
			} else {
			    
			    $display_min = '[WARNING] no min/max defined for boolean / qualitative indicator';
			    $display_max = '[WARNING] no min/max defined for boolean / qualitative indicator';
			    if(count($index) < 2) {
			        logfile(LOG_MAINFILE, array('[WARNING] no min/max defined for boolean / qualitative indicator. Check if {} boolean values are defined in comment field.', 'indicator_id=' . $indicator['sdii_id']));
			    }
			}			
			
			if(!empty($threshold_value)) $display_threshold = $a[$threshold_value];
			else $display_threshold = empty_nc('');
			
		}
		$content = 	"<div class=\"indicator-info ".$class."\"><p>".ucfirst(_t('dashboard','value'))." : <strong>".fnumber_format($current_value, 'auto', false)." (".formatText($indicator['sdii_unit']).")</strong></p>".
				"<p>".ucfirst(_t('sdi','min_value'))." : <strong>".$display_min."</strong></p>".
				"<p>".ucfirst(_t('sdi','max_value'))." : <strong>".$display_max."</strong></p>".
				"<p>".ucfirst(_t('sdi','threshold_value'))." : <strong>".$display_threshold."</strong></p>".
				"</div>";

		return $content;
	}

}

if(!function_exists('getGaugeViz')) {

    function getGaugeViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues, $cursorcssposition) {

        if($indicator['sdii_multiple_type'] == 'sum')  $cfield = '_total';
        if($indicator['sdii_multiple_type'] == 'mean')  $cfield = '_mean';

        // values are stored
		if ($indicatorValues <> false) {

			// we handle multivalues
			if(!empty($indicatorValues[0]['sdiv_multivalue'])) {
				$data = unserialize($indicatorValues[0]['sdiv_multivalue']);
				$current_value = $data[0][$cfield];
				// we handle simple values
			} else {
				$current_value = $indicatorValues[0]['sdiv_value'];
			}

            $value_cursor1 = getPositionfromValue ($current_value, $indicator, $threshold_value);
            // Pour la jauge avec image
            $value_cursor = $value_cursor1 * 150 / 100 - 2; // on rapporte le placement à la largeur du bloc et on soustrait la moitié de la largeur du curseur, soit 4/2, ou 2 pixels

		}
		else {
			$value_cursor = 0;
		}

		// there is no value
		if ($indicatorValues == false) {

			$gauge= 'gauge_grey.gif';
			$gaugeclass = 'g-no-value';
			$current_value = _t('dashboard','novalue');
			$cursor_visibility = 'hidden';
			$class="indicator-no-data";

		}
		// min, max and threshold are not set together No display allowed
		elseif(is_null($indicator['sdii_max_value']) || is_null($indicator['sdii_min_value']) || is_null($threshold_value)) {

			$gauge= 'gauge_red.gif';
			$gaugeclass = 'g-no-fit';
            if(!empty($indicatorValues[0]['sdiv_multivalue'])) {
                $data = unserialize($indicatorValues[0]['sdiv_multivalue']);
                $current_value = $data[0][$cfield];
            }
            else $current_value = $indicatorValues[0]['sdiv_value'];
			$cursor_visibility = 'hidden';
			$class="indicator-no-cursor";

		}
		// there is at least one value and min, max and threshold are set together
		else {

			$gauge= 'gauge.gif';
			$gaugeclass = 'gradient';

			$cursor_visibility = 'visible';
			$class="";

			// we handle multivalues
			if(!empty($indicatorValues[0]['sdiv_multivalue'])) {
				$data = unserialize($indicatorValues[0]['sdiv_multivalue']);
				$current_value = $data[0][$cfield];
				// we handle simple values
			} else {
				$current_value = $indicatorValues[0]['sdiv_value'];
			}

		}

		if(CURRENT_APP == 'admin') {
			$link = "index.php?rub=dashboard&amp;todo=det&amp;id=".$indicator['sdii_id']."&amp;scale_id=".$scale_id;
		} else {
			$link = HrefMaker(array('rub'=> $GLOBALS['links'][U_L]['dashboard']['linkvalue'],'id'=>$indicator['sdii_id'], 'parentid'=> $scale_id));
		}
		$link="javascript:void(0);";

		$listing =	"<div class=\"dashboard-indicator-gauge indicator-info-container\">
		<div class=\"gauge ".$gaugeclass." infobox\" title=\"".ucfirst(_t('dashboard','value'))." : ". fnumber_format($current_value, 'auto', false) ." (".formatText($indicator['sdii_unit']).") ".
		"\n ".ucfirst(_t('sdi','min_value'))." : ".empty_nc($indicator['sdii_min_value']).
		"\n ".ucfirst(_t('sdi','max_value'))." : ".empty_nc($indicator['sdii_max_value']).
		"\n ".ucfirst(_t('sdi','threshold_value'))." : ".empty_nc($threshold_value)."\"  />";
		
		$listing .=	"<div class=\"threshold\"></div>";
		$listing .=	"<div class=\"circle\" style=\"visibility:".$cursor_visibility.";left:".$value_cursor."px;\"></div></div>";
		// $listing .= "<img src=\"" .ADMIN_THEME_URL. "images/cursor.gif\" alt=\"\" title=\""._t('dashboard','value')." : ". fnumber_format($current_value, 'auto', false) ." ". formatText($indicator['sdii_unit']) . " - "._t('dashboard','barre')."\" class=\"cursor\" style=\"visibility:".$cursor_visibility.";left:".$value_cursor."px;\"/>";
		$listing .= getIndicatorInfoBox($current_value, $indicator, $threshold_value, $class);
		
		$listing .= "</div>";
		
		if(isset($indicatorValues[0])) {
		    $arr = array_reverse($indicatorValues);
		    $lastval = end($arr);
		    $lastyear = get_year($lastval['date_p']);
		    $listing .= '<div class="indicator-last-date" title="'.sprintf(_t('dashboard','last_year_reference') . ' / '. _t('dashboard','name') . ' : ' . strtolower(fnumber_format($current_value, 'auto', false)), $lastyear, '2ATT') .'">' . $lastyear . '</div>';
		}
		$listing .= getTendency($indicatorValues, $indicator);
		
		// $listing .= "\t<span class=\"value\"> (".strtolower(fnumber_format($current_value, 'auto', false)).")</span>\n";

		return 	$listing;
	}

}

if(!function_exists('getSimpleViz')) {
    function getSimpleViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues)
    {

        $str = '<div class="dashboard-indicator-raw multiple-none">';

        // there is no value
        if ($indicatorValues == false) {
            $str .= '<span class="infobox no-value">-</span>';
            $current_value = _t('dashboard', 'novalue');
            $class = "indicator-no-data";
            // some values are there
        } else {
            // we handle multivalues
            if (!empty($indicatorValues[0]['sdiv_multivalue'])) {
                $data = unserialize($indicatorValues[0]['sdiv_multivalue']);
                $str .= '<span class="infobox value" data-value="' . _t('sdi', 'multiple_type_none'). '">' . _t('sdi', 'multiple_type_none'). '</span>';
                $current_value = _t('sdi', 'multiple_type_none');
            }
            $class = "";

        }
        $str .= getIndicatorInfoBox($current_value, $indicator, $threshold_value, $class);
        $str .= '<span class="unit">' . formatText($indicator['sdii_unit']) . '</span>';
        $str .= '</div>';

        if (isset($indicatorValues[0])) {
            $arr = array_reverse($indicatorValues);
            $lastval = end($arr);
            $lastyear = get_year($lastval['date_p']);
            $str .= '<div class="indicator-last-date" title="' . sprintf(_t('dashboard', 'last_year_reference') . ' / ' . _t('dashboard', 'name') . ' : ' . strtolower(fnumber_format($current_value, 'auto', false)), $lastyear, '2ATT') . '">' . $lastyear . '</div>';
        }
        $str .= getTendency($indicatorValues, $indicator);

        return $str;

    }
}


    if(!function_exists('getRawViz')) {
    function getRawViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues) {


        if($indicator['sdii_multiple_type'] == 'sum') $cfield = '_total';
        if($indicator['sdii_multiple_type'] == 'mean') $cfield = '_mean';
        if($indicator['sdii_multiple_type'] == 'none') $cfield = '_none';

        $str = '<div class="dashboard-indicator-raw">';

        // there is no value
        if ($indicatorValues==false) {
            $str .= '<span class="infobox no-value">-</span>';
            $current_value = _t('dashboard','novalue');
            $class="indicator-no-data";
            // some values are there
        } else {

            // we handle multivalues
            if(!empty($indicatorValues[0]['sdiv_multivalue'])) {
                $data = unserialize($indicatorValues[0]['sdiv_multivalue']);
                $str .= '<span class="infobox value" data-value="'.sys_number_format($data[0][$cfield]).'">'.fnumber_format($data[0][$cfield], 2, false).'</span>';
                $current_value = $data[0][$cfield];
                // we handle simple values
            } else {

                $a = getBooleanValues($indicator);

                $class= '';

                if($a && ($indicator['sdii_nature'] == 'boolean' || $indicator['sdii_nature'] == 'qualitative')) {

                    if(count($a) == 2) {
                        if($indicatorValues[0]['sdiv_value'] == 0) $class= " boolean-false";
                        else  $class= " boolean-true";
                    } else $class = $class= " boolean-value-" . $indicatorValues[0]['sdiv_value'];

                    $current_value = $a[$indicatorValues[0]['sdiv_value']];
                    $classbmask = ' boolean-mask';

                } else {

                    $classbmask = '';
                    $current_value = fnumber_format($indicatorValues[0]['sdiv_value'], 'auto', false);

                }

                $str .= '<span class="infobox value '.$class.$classbmask.'" data-value="'.sys_number_format($current_value).'">'.$current_value.'</span>';
            }

            $class="";

        }
        $str .= getIndicatorInfoBox($current_value, $indicator, $threshold_value, $class);

        $str .= '<span class="unit">'.formatText($indicator['sdii_unit']).'</span>';

        $str .= '</div>';

        if(isset($indicatorValues[0])) {
            $arr = array_reverse($indicatorValues);
            $lastval = end($arr);
            $lastyear = get_year($lastval['date_p']);
            $str .= '<div class="indicator-last-date" title="'.sprintf(_t('dashboard','last_year_reference') . ' / '. _t('dashboard','name') . ' : ' . strtolower(fnumber_format($current_value, 'auto', false)), $lastyear, '2ATT') .'">' . $lastyear . '</div>';
        }

        $str .= getTendency($indicatorValues, $indicator);

        return $str;

    }


}

if(!function_exists('getSparklineViz')) {

    function getSparklineViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues) {


        if($indicator['sdii_multiple_type'] == 'sum')  $cfield = '_total';
        if($indicator['sdii_multiple_type'] == 'mean')  $cfield = '_mean';

        // we handle multivalues
        if(!empty($indicatorValues[0]['sdiv_multivalue'])) {
            $data = unserialize($indicatorValues[0]['sdiv_multivalue']);
            $current_value = fnumber_format($data[0][$cfield], 2, false);

            // we handle simple values
        } else {
            if($indicatorValues == false) $current_value = _t('dashboard','novalue');
            else $current_value = $indicatorValues[0]['sdiv_value'];
        }

		$str = '<div class="dashboard-indicator-sparkline">';

		// there is no value
		if ($indicatorValues==false) {
			$str .= '<span class="infobox no-value">-</span>';
            $class="indicator-no-data";

			// some values are there
		} else {
			$data = array();
            $class = '';
			// we reverse the array to display eldest first
			$values = array_reverse($indicatorValues);

			foreach($values as $value) {

				// we handle multivalues
				if(!empty($value['sdiv_multivalue'])) {
                    $dataindic = unserialize($value['sdiv_multivalue']);
                    array_push($data, $dataindic[0][$cfield]);
					// we handle simple values
				} else {
					array_push($data, $value['sdiv_value']);
				}

			}
            $str .= '<p class="infobox">';
			$str .= '<span class="inlinebar">'.join(',',$data).'</span>';
            $str .= getIndicatorInfoBox($current_value, $indicator, $threshold_value, $class);
            $str .= '</p>';
		}

		$str .= '</div>';


		if(isset($indicatorValues[0])) {
		    $arr = array_reverse($indicatorValues);
		    $lastval = end($arr);
		    $lastyear = get_year($lastval['date_p']);
		    $str .= '<div class="indicator-last-date" title="'.sprintf(_t('dashboard','last_year_reference') . ' / '. _t('dashboard','name') . ' : ' . strtolower(fnumber_format(end($data), 'auto', false)), $lastyear, '2ATT') .'">' . $lastyear . '</div>';
		}


		$str .= getTendency($indicatorValues, $indicator);

		return $str;

	}

}

if(!function_exists('getViz')) {

	function getViz($sql_object, $scale_id, $indicator, $threshold_value, $overwrite = null, $cursorcssposition = 0) {

	    $req_sdiav=SQL_getAllValue("SCA", $scale_id, $indicator['sdii_id']);
	    $indicatorValues = $sql_object -> DBSelect($req_sdiav);
	    
		if(!is_null($overwrite)) {
			$vizualisation_type = $overwrite;
		} else {
			$vizualisation_type = $indicator['sdii_dashboard_viz'];
		}

        // we overwrite vizualisation type if $indicator['sdii_multiple_type'] == 'none'
        if($indicator['sdii_value_type'] == 'multiple' && $indicator['sdii_multiple_type'] == 'none')  $vizualisation_type = 'simple';

		
		// we override sparkline viz if one value only because it is not readable by user
		if($vizualisation_type == 'sparkline' && is_array($indicatorValues) && count($indicatorValues) == 1) $vizualisation_type = 'raw';

		if($vizualisation_type == 'raw') {
		    $viz = getRawViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues);
		}
		if($vizualisation_type == 'gauge') {
		    $viz = getGaugeViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues, $cursorcssposition);
		}
		if($vizualisation_type == 'sparkline') {
		    $viz = getSparklineViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues);
		}
        if($vizualisation_type == 'simple') {
            $viz = getSimpleViz($sql_object, $scale_id, $indicator, $threshold_value, $indicatorValues);
        }

		return $viz;
	}

}

/**
 * getBooleanValues()
 * Return boolean values enter in comment fields
 * or false if no mask is set or if not boolean
 * as {0=oui;1=non} format
 * @param array $indicator
 * @return mixed (false or array)
 */
if(!function_exists('getBooleanValues')) {

	function getBooleanValues($indicator) {

		if($indicator['sdii_nature'] == 'boolean' || $indicator['sdii_nature'] == 'qualitative') {
			
			
			// get content with the following pattern
			// For example {0=non signée;1=signée}
			$pattern = '/{(.*?)}/'; 
			
			$r =preg_match($pattern, $indicator['sdii_comment'], $matches);
			
			if($r == 0) return false;
			
			$b = array();
			
			$str = rtrim($matches[1], ";");
			
			$a = explode(";", $str);
			
			foreach($a as $entry) {
				list($key, $value) = explode("=", $entry);
				$key = trim($key);
				$b[$key] = trim($value);
			}

			return $b;
			
		}
			
		return false;
		
	}
}

/**
* getColorRamp()
* @param array $values
* @param array $colors
* @return array $hexa_colors
*/
if(!function_exists('getColorRamp')) {
	
	function getColorRamp($values, $colors = array('1' => '#D73027', '2' => '#FFFFBF', '3' => '#1A9850')) {
		$hexa_colors = array ();
		
		// default colors taken from chroma 'RdYlGn'
		// https://github.com/gka/chroma.js/wiki/Predefined-Colors
		require_once ('../lib/vendor/PHP-Color/autoload.php');
		// https://github.com/ProjectCleverWeb/PHP-Color/issues/4
		// https://github.com/ProjectCleverWeb/PHP-Color/issues/3#issuecomment-255130522
		
		/**
		 * * GRADIENTS/SCALES **
		 */
		$color1 = new projectcleverweb\color\main ( $colors ['1'] );
		$color2 = new projectcleverweb\color\main ( $colors ['2'] );
		$color3 = new projectcleverweb\color\main ( $colors ['3'] );
		
		// ge number of classes
		$nb_values = count ( $values );
		$nb_classes = ( int ) (count ( $values ) / 2);
		if ($nb_classes === 0)
			return false;
			
			// necessary to get number of wanted classes
		if ($nb_values % 2 == 1)
			$plus = 1;
		else
			$plus = 0;
		
		// print_r ( $values );
		
		// Gradient array with a custom number of colors (offset 0 is $color1 and offset 9 is $color2)
		$gradient_array_1 = $color1->gradient ( $color2, ($nb_classes + $plus) );
		
		// Gradient array with exactly enough non-duplicate colors
		$gradient_array_2 = $color1->gradient ( $color2 );
		
		// You can't currently create a gradient with more than 2 colors, but you can merge 2+ gradient arrays
		$gradient_array_3 = $color2->gradient ( $color3, ($nb_classes + 1) );
		array_shift ( $gradient_array_3 );
		
		$gradient_array_4 = array_merge ( $gradient_array_1, $gradient_array_3 );
		
		// print_r ( $gradient_array_4 );
		
		foreach ( $values as $key => $value ) {
			// foreach ($gradient_array_4 as $key) {
			
			// we determine text color by contrat to background
			$o = round ( (($gradient_array_4 [$key] ['r'] * 299) + ($gradient_array_4 [$key] ['g'] * 587) + ($gradient_array_4 [$key] ['b'] * 114)) / 1000 );
			if ($o > 125)
				$hexa_colors [$key] ['color'] = '000000';
			else
				$hexa_colors [$key] ['color'] = 'ffffff';
			
			$color = new projectcleverweb\color\main ( $gradient_array_4 [$key], 'RGB' );
			$hexa = $color->hex ();
			
			// echo '<div style="width:100%;heigth:15px;background-color:#'.$hexa.'">'. $key.' #'.$hexa . '</div><br>';
			$hexa_colors [$key] ['background-color'] = $hexa;
		}
		
		return $hexa_colors;
	}
}


/**
 * getQualitativeViz()
 * Return global rate
 * @param array $values
 * @param array $indicator
 */

if(!function_exists('getQualitativeViz')) {
	function getQualitativeViz($values, $indicator, $dateFormat = 'short', $legend = true, $topdf = false) {

		// @todo implement PHP version
		require_once('../lib/vendor/PHP-Color/autoload.php');
		// https://github.com/ProjectCleverWeb/PHP-Color/issues/4
		// https://github.com/ProjectCleverWeb/PHP-Color/issues/3#issuecomment-255130522

// 		$o = new projectcleverweb\color\main('FF0000');
// 		$hex_scheme  = $o->scheme('compound');
// 		print_r($hex_scheme);

				
		// test if there is record
		if(!isset($values[0]['sdiv_id'])) return false;
		
		// we retrieve values
		$a = getBooleanValues($indicator);
		
		// get get color ramp
		$colors = getColorRamp($a);
		
		if($a) {
			$index = array();
			foreach ($a as $key => $value) {
				array_push($index, $key);
			}
		
			$minvalue = min($index);
			$maxvalue = max($index);
		
			// @todo to remove
			//footerAddJS('../lib/js/chroma.js/chroma.min.js');
			// https://github.com/gka/chroma.js/wiki/Predefined-Colors
			$js = '	var viz_color = chroma.scale("RdYlGn").colors('.count($a).');
					// http://jsfiddle.net/PXJ2C/
					$("td.qualitative-value").each(function() {
					    var val = $(this).attr("data-value");
					 	$(this).css("background-color", viz_color[val]);
						var rgb = chroma(viz_color[val]).rgb();
						var o = Math.round(((parseInt(rgb[0]) * 299) + (parseInt(rgb[1]) * 587) + (parseInt(rgb[2]) * 114)) /1000);
						if(o > 125) $(this).css("color", "black");
					    else $(this).css("color", "white");

					});';
			// footerAddInlineJS($js);
		}
		 
		// print_r($values);
		
		$content = '';
		$header = '';
		$body = '';
		$width = round(100 / count($values));
		
		$values = $reversed = array_reverse($values);
		
		for ($i=0; $i <count($values); $i++) {
			
			if($dateFormat == 'short') $date = substr(formatDate($values[$i]['date_p'], true), 0, 4);
			else $date = $values[$i]['date_p'];
			$header .= '<td class="qualitative-header">'. $date .'</td>';
			$body   .= '<td class="qualitative-value" data-value="'. $values[$i]['sdiv_value'] .'" style="color:#'.$colors[$values[$i]['sdiv_value']]['color'].';background-color:#'.$colors[$values[$i]['sdiv_value']]['background-color'].';width:'.$width.'%">'. $a[$values[$i]['sdiv_value']] .'</td>';
		}
		if($topdf == true) $content .= '<style>
										table {
								  			font-size:0.9em;
								  		}
									  td.qualitative-value {
									    	text-align:center;
											height:50px;
											line-height:50px;
									  }
									  td.qualitative-header {
									    	text-align:center;
									  		color:#777777;
									    }
										div.qualitative-legend {margin-bottom:2em;color:#777777;font-size:0.8em;}


									</style>';
		$content .= '<div class="qualitative-viz"><table class="qualitative-table"><tbody><tr>%s</tr><tr>%s</tr></tbody></table></div>';
		
		if($legend) {
			$legend = '';
			if($topdf == false) $legendblock = '&nbsp;'; else $legendblock = 'xxxx'; 
			foreach ($a as $key => $value) {
				$legend .= '<span class="legend-block" style="background-color:#'.$colors[$key]['background-color'].';color:#'.$colors[$key]['background-color'].';">'.$legendblock.'</span> <span class="legend-label"> '.$value.'</span>';
				if($topdf == true) $legend .= '<span  style="background-color:#ffffff;color:#ffffff;">'.$legendblock.'</span>';
			}
			$content .= '<div class="qualitative-legend">'.$legend.'</div>';
		}
		return sprintf($content, $header, $body);
	}
}

/**
 * getRates()
 * Return global rate and 
 * @param array $indicator
 * @param numeric $firstValue
 * @param numeric $firstValue
 * @param integer $year_start
 * @param integer $year_end
 */

if(!function_exists('getRates')) {
	function getRates($firstValue, $lastValue, $year_start, $year_end, $format = 'html') {
		
		if((int) $year_start > (int) $year_end) $years = $year_start - $year_end;
		else $years = $year_end - $year_start;
		
		if($firstValue == 0) {
			$global_evol = empty_nc('');
			$tcam = empty_nc('');
		} else {
			// gobal rate
			$global_evol = (($lastValue - $firstValue) / $firstValue * 100);
			
			// tcam
			// $tcam = (pow ( (2349816 / 2339881) , 1/5 ) - 1) * 100; // doit retourner 0.085 %
			if($years > 1) $tcam = (pow ( ($lastValue / $firstValue) , 1 / $years ) - 1) * 100;
			else $tcam = empty_nc('');
		}
		
		if($format == 'html') {
		return '<div class="indicator-trend-values"><i class="fa fa-line-chart" aria-hidden="true"></i> 
				<span class="rate-label">' . _t('dashboard', 'global_rate') . ' : </span><span class="rate-value">' . fnumber_format($global_evol, 2, false) .  ' %</span>
				| <span class="rate-label">' . _t('dashboard', 'cagr'). ' : </span><span class="rate-value">' . fnumber_format($tcam, 2, false) .  ' %</span>
				</div>';
		} else {
			return  _t('dashboard', 'global_rate') . ' : ' . fnumber_format($global_evol, 2, false) . ' % | '. strip_tags(_t('dashboard', 'cagr')). ' : ' . fnumber_format($tcam, 2, false) . ' %'; 
		}
		

	}
}

/**
 * getTendency()
 * Return indicator tendency based on value
 * (not based on performance)
 * @param array $values
 * @param array $indicator
 */

if(!function_exists('getTendency')) {

	function getTendency($values, $indicator) {

        if($indicator['sdii_multiple_type'] == 'sum') $cfield = '_total';
        if($indicator['sdii_multiple_type'] == 'mean') $cfield = '_mean';
        if($indicator['sdii_multiple_type'] == 'none') $cfield = '_none';

		$tendency = '<div class="dashboard-indicator-tendency">';

		$percentage = '';
		$evolution = '';
		
		// we handle multivalues
		// if there is a previous value
		if(!empty($values[1]['sdiv_multivalue'])) {
			$data = unserialize($values[0]['sdiv_multivalue']);
			$dataPlusOne = unserialize($values[1]['sdiv_multivalue']);

			if($indicator['sdii_nature'] == 'quantitative' && $indicator['sdii_multiple_type'] != 'none') {
			    if($dataPlusOne[0][$cfield] != 0) {
    				$percentage = ($data[0][$cfield] - $dataPlusOne[0][$cfield]) / $dataPlusOne[0][$cfield] * 100;
    				$evolution = ' ('.round($percentage, 0). '%)';
			    } else $evolution = ' ('.empty_nc(''). ' %)';
			}

            if($indicator['sdii_multiple_type'] == 'none') $tendency.= '';
            else {
                if ($data[0][$cfield] > $dataPlusOne[0][$cfield]) {
                    $tendency .= "<img src=\"" . ADMIN_THEME_URL . "images/ico_asc.png\" alt=\"" . _t('dashboard', 'value_tendance') . "\" title=\"" . sprintf(_t('dashboard', 'previous_value'), fnumber_format($dataPlusOne[0][$cfield], 'auto', false)) . $evolution . "\" />";
                } elseif ($data[0][$cfield] < $dataPlusOne[0][$cfield]) {
                    $tendency .= "<img src=\"" . ADMIN_THEME_URL . "images/ico_desc.png\" alt=\"" . _t('dashboard', 'value_tendance') . "\" title=\"" . sprintf(_t('dashboard', 'previous_value'), fnumber_format($dataPlusOne[0][$cfield], 'auto', false)) . $evolution . "\" />";
                } else {
                    $tendency .= "<img src=\"" . ADMIN_THEME_URL . "images/ico_stable.png\" alt=\"" . _t('dashboard', 'value_tendance') . "\" title=\"" . sprintf(_t('dashboard', 'previous_value'), fnumber_format($dataPlusOne[0][$cfield], 'auto', false)) . $evolution . "\" />";
                }
            }

			// we handle simple values
			// if there is a previous value
		} elseif(isset($values[1]['sdiv_value'])) {
			
			$previous_value = fnumber_format($values[1]['sdiv_value'], 'auto', false);
			
			if($indicator['sdii_nature'] == 'quantitative') {
			    if($values[1]['sdiv_value'] != 0) {
    				$percentage = ($values[0]['sdiv_value'] - $values[1]['sdiv_value']) / $values[1]['sdiv_value'] * 100;
    				$evolution = ' ('.round($percentage, 0). '%)';
			    } else $evolution = ' ('.empty_nc(''). ' %)';
				
			} else {
				
				$a = getBooleanValues($indicator);
				if($a) {
					$index = array();
					foreach ($a as $key => $value) {
						array_push($index, $key);
					}
					$previous_value = $a[$values[1]['sdiv_value']];
				}
			}
			
			if ($values[0]['sdiv_value'] > $values[1]['sdiv_value']) {
				$tendency.="<img src=\"" .ADMIN_THEME_URL. "images/ico_asc.png\" alt=\""._t('dashboard','value_tendance')."\" title=\"".sprintf(_t('dashboard','previous_value'), fnumber_format($previous_value, 'auto', false)).$evolution."\" />";
			} elseif($values[0]['sdiv_value'] < $values[1]['sdiv_value']) {
				$tendency.="<img src=\"" .ADMIN_THEME_URL. "images/ico_desc.png\" alt=\""._t('dashboard','value_tendance')."\" title=\"".sprintf(_t('dashboard','previous_value'), fnumber_format($previous_value, 'auto', false))."\" />";
			} else {
				$tendency.="<img src=\"" .ADMIN_THEME_URL. "images/ico_stable.png\" alt=\""._t('dashboard','value_tendance')."\" title=\"".sprintf(_t('dashboard','previous_value'), fnumber_format($previous_value, 'auto', false))."\" />";	
			}
		} else {
		    $tendency .= '&nbsp;';
		}
		$tendency .= '</div>';

		return $tendency;
	}

}