<?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
 */


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

        $js = '$("#reload-discover-api").click(function() {
                    $("#api-url").change();
                    return false;
                });;
                
                $("#sdi_value_type").change(function() {
                    let sdiType = $("#sdi_value_type").val();
            
                    if(sdiType == "unique") {
                        $("#api-value-field-cont").css("display", "block");
                        $("#api-multi-mapping-cont").css("display", "none");
                    } else {
                        $("#api-value-field-cont").css("display", "none");
                        $("#api-multi-mapping-cont").css("display", "block");
                    }
                });
                $("#sdi_value_type").change();
                
                $("#api-url").change(function() {
                
                        let sdiType = $("#sdi_value_type").val();
                        let api_url = $("#api-url").val();
                        
                        console.log(api_url);
                        console.log(sdiType);
                        let content = CKEDITOR.instances["comment"].getData(); // we get content from comment textarea
                        var rval = /{(.*?)}/g.exec(content); // select what is between curly brackets : rval[0] returns content with brackets, rval[1] without
                        
                        if(sdiType == "multiple" && !rval) { // no predefined values are set, we notify the user and exit
                            alertify.error("' . addslashes(sprintf(_t('sdi', 'api-no-predefined-values'), '#attached_to_dashboard')) . '");
                            return false;
                        }
                        if(api_url.length < 7) { // api_url is empty
                            // we do not do anything now
                            return false;
                        }  else {
                            // we send the request to discover fields
                            $.ajax({
                              method: "GET",
                              url: "../dashboard/api-harvest.php?token='.SECRET_KEY.'",
                              data: { apiURL: $("#api-url").val() },
                              dataType: "json",
                            }).done(function(data) {
                                if(data.status == 0) { // failure : we display a message 
                                    alertify.error(data.msg);
                                    return false;
                                }
                                if(data.status == 1) { // success : we prepare data
                                    
                                    selectBoxOptions("api-date-field", data.keys, true); // we replace date field
                                    if(sdiType == "unique") {
                                        selectBoxOptions("api-value-field", data.keys, true); // we replace date field
                                    }
                                    if(sdiType == "multiple") {
                                        if($(".api-mapping-field").length == 0) { // we prepare fields only if no existing data is there
                                            entries = rval[1].split(";");
                                            // @todo : idéalement récuperer les valeurs déjà peuplées
                                            $("#api-multi-mapping-cont").empty();                                           
                                            for(var i = 0; i < entries.length; i++) {
                                                if(entries[i] != "") $("#api-multi-mapping-cont").append("<p><label for=\"api-mapping-" + i + "\">" + entries[i].toUpperCase() + "</label><input name=\"api-mapping-i[]\" type=\"hidden\" id=\"api-mapping-i-" + i + "\" value=\"" + entries[i] + "\" /><input name=\"api-mapping[]\" type=\"text\" class=\"textfield api-child api-mapping-field\" id=\"api-mapping-" + i + "\" maxlength=\"150\" value=\""+ curval + "\" /></p>");
                                            }
                                        }
                                        // we apply selectBox anyway
                                        $( ".api-mapping-field").each(function( index ) {
                                              // console.log(this.id);
                                              selectBoxOptions(this.id, data.keys, true); 
                                        });
                                    }
                                }
                            });
                            
                            
                        }
                        // console.log(content);
                        // console.log(rval);
                });';
        footerAddInlineJS($js);
    }
}

/**
 * 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);
    }
}


/**
 * _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 = [];

        // Linea21 formatting
        if(isset($json_arr['values'])) {
            $json_arr['results'] =  $json_arr['values'];
        }


        // 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
            }
            $json_arr['results'] = $tmp;
        }
        
        // opendatasoft and  koumoul (default query type) : 'results'
        if(isset($json_arr['results']))  $a = $json_arr;

        // put returned values in results entry if 'results' key does not exists
        // for example, no 'results' key when using https://api.atmosud.org/cigale/ges/territoire?variable=prg100.3ges&code_territoire=200030195&bool_sources_additionnelles=false&perimetre=pcaet&all_years=true&format=json
        if(count($json_arr) > 0 && !isset($json_arr['results']))  $a = array('results' => $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_discover
 * @param array $sdi
 * @param boolean $initialImport
 * @return boolean
 */
if(!function_exists('api_discover')) {
    function api_discover($url, $oneRow = true) {

        # 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
        if(defined('PROXY_PARAMS')) {
            $client = new GuzzleHttp\Client(['timeout' => 3, 'connect_timeout' => 6, 'proxy' => PROXY_PARAMS]);
        } else {
            $client = new GuzzleHttp\Client(['timeout' => 3, 'connect_timeout' => 3]);
        }

        // we check if url is responding, if not we log the error
        try {
            $response = $client->get($url);
            $code = $response->getStatusCode();


            if($code == '200') {
                // we convert json response to array
                $records = json_decode($response->getBody(), true);
                // print_r($records);

                // we format retrieved json file if needed
                $records = _format_results($records);
                // print_r($records);
                if($oneRow) return $records['results'][0];
                else return $records['results'];

            }
            // if 404 exception is caught - others errors as well
        } catch (Exception $e) {
            logfile(LOG_MAINFILE, array('[API discover]', 'URL not responding', $url, $e->getMessage()));
            return _t('sdi', 'api-no-response');
        }

    }
}


/**
 * 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;
        $rflag = 1; // 1 means 'success', 2 : 'partial import due to strange values', 3 : 'action failed'

        # 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
        // @see https://docs.guzzlephp.org/en/5.3/clients.html?highlight=proxi
        // we add a timeout to client not to block script
        if(defined('PROXY_PARAMS')) {
            $client = new GuzzleHttp\Client(['timeout' => 3, 'connect_timeout' => 6, 'proxy' => PROXY_PARAMS]);
        } else {
            $client = new GuzzleHttp\Client(['timeout' => 3, 'connect_timeout' => 3]);
        }


        foreach ($sdi as $indicator) {

            // if API is enabled only
            if($indicator['sdii_api_enabled'] == 'Y') {

                // $initialImport flag is not used anymore
                // we set $since value anyway, not only 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 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 format retrieved json file if needed
                        $records = _format_results($records);

                        foreach ($records['results'] as $rec) {
                            if($debug) echo '<div style="border:1px solid slateblue;padding:1em;margin:1em">';
                            if($debug) echo '<p>Value from API : </p>';
                            if($debug) print_r($rec);
                            if($debug) echo '<hr>';

                            // 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']) ) {
                                $lastDate =  $lastV[0]['sdiv_date_published']; // return 2020-01-01
                                $dateSuffix = substr($lastDate,4);
                            }
                            else {
                                $lastDate = $since;
                                $dateSuffix = '-12-31';
                            }

                            // 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 = '';
                            }

                            $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']] .= $dateSuffix;
                                $recordDateFormatted = $rec[$indicator['sdii_api_datefield']];
                                $recordDate = DateTime::createFromFormat($format, $rec[$indicator['sdii_api_datefield']]);
                           // else default format is similar to : "2012-01-01T00:00:00+00:00"
                            } else {
                                $recordDateFormatted = $rec[$indicator['sdii_api_datefield']];
                                $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 .= $dateSuffix;
                            if(strlen($lastDate) == 4) $lastDate .= $dateSuffix;

                            $SinceDate = DateTime::createFromFormat($format, $since);

                            // we get last date
                            $lastvalue = DateTime::createFromFormat($format, $lastDate);
                            $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><strong>Indicator id : ".$indicator['sdii_id'] ."</strong> - Since date : " . $SinceDate->format("Y-m-d") . " / [comparison] current record \$recordate (from API) :  " . $formattedDate . ' / last value\'s date (from DB) : ' . $formattedLastvalue . " / trigger date \$triggerdate (from DB) - pour ajout seulement : " . $triggerDate->format("Y-m-d") . ' / frequency : '.$indicator['sdii_frequency'] . " / overwrite values : " . $indicator['sdii_api_overwritevalues'] . '</p>';

                            // if($indicator['sdii_api_overwritevalues'] == 'N' && $recordDate >= $triggerDate) {
                            // be sure we import values for desired years
                            if($recordDate >= $SinceDate) {

                                if(isset($rec[$indicator['sdii_api_datefield']])) {
                                    $a = [];

                                    $a['indicator_type'] = $indicator['sdii_value_type'];

                                    // if is sdii_value_type == 'unique'
                                    //  !!!! WARNING : if( isset($rec[$indicator['sdii_api_valuefield']])) does not pass test
                                    // when $rec[$indicator['sdii_api_valuefield']] == null
                                    if ($indicator['sdii_value_type'] == 'unique') {
                                        if (is_numeric($rec[$indicator['sdii_api_valuefield']])) {
                                            $a[0] = sys_number_format(round($rec[$indicator['sdii_api_valuefield']], $indicator['sdii_api_decimal']));
                                            $a['nodata'] = 0;
                                        } else {
                                            $a[0] = 0;
                                            $a['nodata'] = 1;
                                        }
                                    }

                                    // if is sdii_value_type == 'multiple'
                                    if ($indicator['sdii_value_type'] == 'multiple') {
                                        $mvalues = [];
                                        $mapping = unserialize($indicator['sdii_api_multimapping']);

                                        // first iteration to be sure data exists and is numeric
                                        $flags = [];
                                        foreach ($mapping as $k => $v) {
                                            if (isset($rec[$v]) && is_numeric($rec[$v])) array_push($flags, true);
                                            else array_push($flags, false); // we handle
                                        }
                                        // Multiple values format - no errors, we populate records
                                        // Array ( [0] => Array ( [value] => 45 [label] => chaleur ) [1] => Array ( [value] => 89 [label] => électricité ) [2] => Array ( [value] => 52 [label] => carburant ) )
                                        if (!in_array(false, $flags)) {
                                            foreach ($mapping as $k => $v) {
                                                array_push($mvalues, array('value' => sys_number_format(round($rec[$v], $indicator['sdii_api_decimal'])), 'label' => $k));
                                            }
                                            $a['nodata'] = 0;
                                        } else {
                                            foreach ($mapping as $k => $v) {
                                                array_push($mvalues, array('value' => 0, 'label' => $k));
                                            }
                                            $a['nodata'] = 1;
                                        }
                                        $a[0] = $mvalues;
                                    }
                                    $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'] = 0; // 0 stands for API

                                    $sdio = new sdi;
                                    $integrity = $sdio->CheckDataIntegrity_value($a, $sql_object);

                                    // Indicator is not set to update values, we just add new values if exists
                                    if ($indicator['sdii_api_overwritevalues'] == 'N' && $recordDate >= $triggerDate) {

                                        if (!is_string($integrity)) {
                                            if ($debug) echo '<p style="color:#6db517;font-size:0.9em;font-weight:bold;border-left:15px solid #6db517;padding-left:1em">ADD VALUE / integrity : ' . $integrity. '</p>';
                                            $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 - adding values / CheckDataIntegrity_value() failed', 'indicator : ' . $indicator['sdii_id'], $indicator['sdii_api_url'], 'error message : ' . strip_tags((string)$integrity)));
                                            // return false; // we do no return false to still execute the routine on dataset
                                            $rflag = 2; // partial import due to strange values
                                        }
                                    }
                                    // Indicator is set to update values, we do it only if value has changed for a given date
                                    if ($indicator['sdii_api_overwritevalues'] == 'Y') {

                                        $res = $sql_object->DBSelect(SQL_getIndicValuesBydate($indicator['sdii_id'], $scale_id, $recordDateFormatted));

                                        // check if value has changed for a given date and modify value if needed
                                        if(value_has_changed($a, $res, $indicator)) {


                                            if (!is_string($integrity)) {
                                                // value already exists we modify it if needed !
                                                if(isset($res[0]['sdiv_id'])) {
                                                    if ($debug) echo '<p style="color:#d66ee3;font-size:0.9em;font-weight:bold;border-left:15px solid #d66ee3;padding-left:1em">UPDATE VALUE / integrity : ' . $integrity. '</p>';
                                                    $r = $sdio->ModifyValue($res[0]['sdiv_id'], $a, $sql_object);
                                                    if (is_numeric($r)) logfile(LOG_MAINFILE, array('[API call]', 'SUCCESS - modifying values to db', 'indicator : ' . $indicator['sdii_id'], 'added value ID : ' . $r));
                                                } elseif(!isset($res[0]['sdiv_id']) && $recordDate >= $triggerDate) {
                                                    if ($debug) echo '<p style="color:#6db517;font-size:0.9em;font-weight:bold;border-left:15px solid #6db517;padding-left:1em">ADD VALUE / integrity : ' . $integrity. '</p>';
                                                    $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 {
                                                if(isset($res[0]['sdiv_id'])) {
                                                    // CheckDataIntegrity_value failed
                                                    logfile(LOG_MAINFILE, array('[API call]', 'ERROR - modifying values / CheckDataIntegrity_value() failed', 'indicator : ' . $indicator['sdii_id'], $res[0]['sdiv_id'], $indicator['sdii_api_url'], 'error message : ' . strip_tags((string)$integrity)));
                                                } else {
                                                    logfile(LOG_MAINFILE, array('[API call]', 'ERROR - adding values / CheckDataIntegrity_value() failed', 'indicator : ' . $indicator['sdii_id'], $indicator['sdii_api_url'], 'error message : ' . strip_tags((string)$integrity)));
                                                }
                                                // return false; // we do no return false to still execute the routine on dataset
                                                $rflag = 2; // partial import due to strange values
                                            }
                                        }
                                    }
                                } else {
                                    // given keys are inconsistent
                                    logfile(LOG_MAINFILE, array('[API call]', 'ERROR - given array keys are inconsistent : ' . $indicator['sdii_api_valuefield'] . ' / "'. $indicator['sdii_api_datefield']. ' - check url result and change fields values', 'indicator : ' . $indicator['sdii_id'], $indicator['sdii_api_url']));
                                    $rflag = 2; // partial import due to strange values
                                    // return false; // we do no return false to still execute the routine on dataset
                                }

                            }
                            if($debug) echo '</div>'; // closing debug container
                        }
                    }
                    // 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()));
                    $rflag = 3;
                }

            } // closing $indicator['sdii_api_enabled'] == 'Y' test

        }

        return $rflag; // 1, 2 or 3 here

    }
}

if(!function_exists('value_has_changed')) {
    function value_has_changed($a, $res, $indicator) {

        $debug = false;

        // no record found for the given date, so no modification
        if(!is_array($res)) {
            if ($debug) echo '<p style="color:purple;font-size:0.9em;font-weight:bold;border-left:8px solid purple;padding-left:1em">No values is stored for the current year. So, no changes !</p>';
            return true;
        }

        // for unique type indicator
        if($indicator['sdii_value_type'] == 'unique') {
            if($debug) {
                echo '<p style="color:orange;font-size:0.9em;"><strong>'.$res[0]['sdiv_date_published'].' - Value in db : '.sys_number_format(round($res[0]['sdiv_value'], $indicator['sdii_api_decimal'])).' / Value from API '.sys_number_format(round($a[0], $indicator['sdii_api_decimal'])).'</strong></p>';

                if (sys_number_format(round($res[0]['sdiv_value'], $indicator['sdii_api_decimal'])) == sys_number_format(round($a[0], $indicator['sdii_api_decimal']))) echo '<p style="color:purple;font-size:0.9em;font-weight:bold;border-left:8px solid purple;padding-left:1em">No changes</p>';
                else echo '<p style="color:red;font-size:0.9em;font-weight:bold;border-left:8px solid red;padding-left:1em">Changes ! Update is needed !</p>';
            }
            if($res[0]['sdiv_value'] != $a[0]) return true;
        }
        // for multiple type indicator
        if($indicator['sdii_value_type'] == 'multiple') {

            $mapping = unserialize($indicator['sdii_api_multimapping']);

            // we format stored values to be able to compared retrieved values with existing ones
            $tmp = [];
            $currentval = unserialize ($res[0]['sdiv_multivalue']);
            foreach ($currentval as $k => $v) {
                array_push($tmp, ['value' => $v['value'], 'label' => $v['label']]);
            }

            if($debug) {
                echo '<p style="color:orange;font-size:0.9em;"><strong>Formatted values from database :</strong><br>';
                print_r($tmp);
                echo '</p>';
                echo '<p style="color:blue;font-size:0.9em;"><strong>Formatted values from API :</strong><br>';
                print_r($a[0]);
                echo '</p>';
                if ($tmp == $a[0]) echo '<p style="color:purple;font-size:0.9em;font-weight:bold;border-left:8px solid purple;padding-left:1em">No changes</p>';
                else echo '<p style="color:red;font-size:0.9em;font-weight:bold;border-left:8px solid red;padding-left:1em">Changes ! Update is needed !</p>';
            }

            if ($tmp != $a[0]) return true;
        }

        return false;
    }
}

if(!function_exists('add_cumulative_value')) {
    function add_cumulative_value($values, $indic, $reverse = true)
    {
        if($indic['sdii_value_type'] != 'unique' || $indic['sdii_unique_mode'] != 'cumulative') return $values;
        if (!is_array($values) || count($values) <= 0) return $values;
        if($reverse) $values = array_reverse($values);
        $cumulative_val = 0;
        foreach ($values as $key => &$value) {

            $cumulative_val += $value['sdiv_value'];
            $value['sdiv_cumulative_value'] = $cumulative_val;
            // echo '<hr>';
            // print_r($value);

        }
        if($reverse) return array_reverse($values);
        else return $values;
    }
}

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_actions.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('getIndicatorValueContributors')) {

    function getIndicatorValueContributors($indicValue, $sqlo, $format = 'string') {

        if (empty($indicValue['sdiv_history'])) return '';

        $a = json_decode($indicValue['sdiv_history'], true);

        $cont = [];
        $cont_s = '';
        $sep ='';
        foreach($a as $item) {

            // print_r($item);
            // we add 'count(user_id), ' to the query to always get a result
            $q = "SELECT count(user_id), IF(user_login IS NULL, '" . _t('api', 'api_source') . "', user_login) as user_login, DATE_FORMAT('" . $item['date'] . "', '" . toStringSqlDate('short') . "' ) AS date FROM " . T_USER . " WHERE user_id = " . $item['user'] . ";";
            $r = $sqlo->DBSelect($q);

            if(is_array($r)) {
               array_push($cont, array('date' => $r[0]['date'], 'login' => $r[0]['user_login']));
               $cont_s .=   $sep . _t('published', 'on') . ' ' .$r[0]['date'] . ' ' . _t('divers', 'by') . ' ' . $r[0]['user_login'];
               $sep = ' / ';
            }
        }
        // finally we display last change
        // print_r($indicValue);
        $cont_s .=   $sep . _t('published', 'on') . ' ' .$indicValue['date_m_short'] . ' ' . _t('divers', 'by') . ' ' . $indicValue['user_login'];
        if($format == 'string') return $cont_s;
        else $cont;
    }

}

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, $cumulative_value) {

        // if mean/sum is provide, we do round values with 2 decimals
        if($indicator['sdii_value_type']=='multiple' && $indicator['sdii_multiple_type'] != 'none') {
            $current_value = fnumber_format($current_value, 2, false);
        }
		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('');
			
		}
        if(!empty($cumulative_value)) $display_cumulative_value = "<p>".ucfirst(_t('dashboard','cumulative_value'))." : <strong>".fnumber_format($cumulative_value, 'auto', false)." (".formatText($indicator['sdii_unit']).")</strong></p>";
        else $display_cumulative_value = '';
		$content = 	"<div class=\"indicator-info ".$class."\"><p>".ucfirst(_t('dashboard','value'))." : <strong>".fnumber_format($current_value, 'auto', false)." (".formatText($indicator['sdii_unit']).")</strong></p>".
                $display_cumulative_value.
				"<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) {

        $cumulative_value = '';

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

        // values are stored
		if ($indicatorValues <> false) {
            if(isset($indicatorValues[0]['sdiv_cumulative_value'])) $cumulative_value = $indicatorValues[0]['sdiv_cumulative_value'];

			// we handle multivalues
			if($indicator['sdii_value_type'] == 'multiple') {
				$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 || is_null($current_value) ) {

			$gauge= 'gauge_grey.gif';
			$gaugeclass = 'g-no-value';

            if($indicatorValues == false) $current_value = _t('dashboard','novalue');
            else $current_value = _t('dashboard','no_data');

			$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($indicator['sdii_value_type'] == 'multiple') {
                $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($indicator['sdii_value_type'] == 'multiple') {
				$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\">";
		
		$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, $cumulative_value);
		
		$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)
    {

        $cumulative_value = '';

        $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 {
            if(isset($indicatorValues[0]['sdiv_cumulative_value'])) $cumulative_value = $indicatorValues[0]['sdiv_cumulative_value'];
            // we handle multivalues
            if($indicator['sdii_value_type'] == 'multiple') {
                $data = unserialize($indicatorValues[0]['sdiv_multivalue']);
                if(null_values_only($data)) {
                    $str .= '<span class="infobox no-data">-</span>';
                    $current_value = _t('dashboard', 'no_data');
                } else {
                    $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, $cumulative_value);
        $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;

    }
}
/**
 * check if multivalues indicator contains only null value
 */
if(!function_exists('null_values_only')) {
    function null_values_only($data)
    {

        $a = [];
        foreach ($data as $d) {
            if (is_null($d['value'])) array_push($a, true);
            else array_push($a, 0);
            //echo ($d['value']).'<br><br>';
        }
        // print_r($a);

        if (in_array(0, $a)) return false;
        else return true;
    }
}


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

        $cumulative_value = '';

        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($indicator['sdii_value_type'] == 'multiple') {
                $data = unserialize($indicatorValues[0]['sdiv_multivalue']);
                if(null_values_only($data)) {
                    $str .= '<span class="infobox no-value no-data">-</span>';
                    $current_value = _t('dashboard', 'no_data');
                } else {
                    $current_value = fnumber_format($data[0][$cfield], 2, false);
                    $str .= '<span class="infobox value" data-value="' . sys_number_format($data[0][$cfield]) . '">' . $current_value . '</span>';

                }
                // we handle simple values
            } else {

                $a = getBooleanValues($indicator);

                $class= '';
                if(isset($indicatorValues[0]['sdiv_cumulative_value'])) $cumulative_value = $indicatorValues[0]['sdiv_cumulative_value'];

                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'];

                    if(is_null($indicatorValues[0]['sdiv_value'])) {
                        $current_value =  mb_ucfirst(_t('dashboard', 'no_data'));
                        $class= " no-value no-data";
                    } else {
                        $current_value = $a[$indicatorValues[0]['sdiv_value']];
                    }
                    $classbmask = ' boolean-mask';

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

                } else {
                    if(is_null($indicatorValues[0]['sdiv_value'])) {
                        $str .= '<span class="infobox no-value no-data">-</span>';
                        $current_value = _t('dashboard', 'no_data');
                      } 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, $cumulative_value);

        $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) {

        $cumulative_value = '';

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

        // we handle multivalues
        if($indicator['sdii_value_type'] == 'multiple') {
            $data = unserialize($indicatorValues[0]['sdiv_multivalue']);

            if(is_null($data[0][$cfield])) $current_value = _t('dashboard','no_data');
            else $current_value = fnumber_format($data[0][$cfield], 2, false);

            // we handle simple values
        } else {
            if($indicatorValues == false) $current_value = _t('dashboard','novalue');
            elseif(is_null($indicatorValues[0]['sdiv_value'])) $current_value = _t('dashboard','no_data');
            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>';

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

            if(isset($indicatorValues[0]['sdiv_cumulative_value'])) $cumulative_value = $indicatorValues[0]['sdiv_cumulative_value'];

			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, $cumulative_value);
            $str .= '</p>';
		}

		$str .= '</div>';


		if(isset($indicatorValues[0])) {
		    $arr = array_reverse($indicatorValues);
		    $lastval = end($arr);
		    $lastyear = get_year($lastval['date_p']);
            if($indicator['sdii_value_type'] == 'unique') {
                if (is_null($lastval['sdiv_value'])) $lastval = _t('dashboard', 'no_data');
                else $lastval = strtolower(fnumber_format($lastval['sdiv_value'], 'auto', false));
            } else { // multiple
                $data = unserialize($lastval['sdiv_multivalue']);
                if (null_values_only($data)) {
                    $lastval = _t('dashboard', 'no_data');
                }
                else {
                    $lastval = strtolower(fnumber_format($data[0][$cfield], 'auto', false));
                    if($cfield != 'none') $lastval = strtolower(fnumber_format($data[0][$cfield], 2, false));
                }
            }

		    $str .= '<div class="indicator-last-date" title="'.sprintf(_t('dashboard','last_year_reference') . ' / '. _t('dashboard','name') . ' : ' . $lastval, $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);
        $indicatorValues = add_cumulative_value($indicatorValues, $indicator); // we add 'sdiv_cumulative_value' attribute if needed

		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;
            $hexa_colors [$key] ['extracss'] = '';

		}
        // adding no data colors
        $hexa_colors [_t('dashboard', 'no_data')] ['color'] = '000000';
        $hexa_colors [_t('dashboard', 'no_data')] ['background-color'] = 'd5d5d5';
        $hexa_colors [_t('dashboard', 'no_data')] ['extracss'] = 'background: linear-gradient(45deg, #fff 1%, #d5d5d5 1%, #d5d5d5 49%, #fff 49%, #fff 51%, #d5d5d5 51%, #d5d5d5 99%, #fff 99%);background-size: 6px 6px;background-position: 50px 50px;';
		
		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) {

		// 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);

        $contains_null = false;
		
		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'];

            if(is_null($values[$i]['sdiv_value'])) {
                $cur_val = _t('dashboard', 'no_data');
                $cur_key = _t('dashboard', 'no_data');
                $contains_null = true;
            } else {
                $cur_val = $a[$values[$i]['sdiv_value']];
                $cur_key = $values[$i]['sdiv_value'];
            }

			$header .= '<td class="qualitative-header">'. $date .'</td>';
			$body   .= '<td class="qualitative-value" data-value="'. $cur_val .'" style="color:#'.$colors[$cur_key]['color'].';background-color:#'.$colors[$cur_key]['background-color'].';width:'.$width.'%;'.$colors[$cur_key]['extracss'].'">'. $cur_val .'</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>{p1}</tr><tr>{p2}</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>';
			}
            if($contains_null == true) {
                $legend .= '<span class="legend-block" style="background-color:#'.$colors[_t('dashboard', 'no_data')]['background-color'].';color:#'.$colors[_t('dashboard', 'no_data')]['background-color'].';'.$colors[_t('dashboard', 'no_data')]['extracss'].'">'.$legendblock.'</span> <span class="legend-label"> '._t('dashboard', 'no_data').'</span>';
            }
			$content .= '<div class="qualitative-legend">'.$legend.'</div>';
		}
        // because string contains % sprintf() is not working
        $content = str_replace('{p1}', $header, $content);
        $content = str_replace('{p2}', $body, $content);
		return $content;
	}
}

/**
 * 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 || is_null($firstValue) || is_null($lastValue)) {
			$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' && !is_null($data[0][$cfield]) && !is_null($dataPlusOne[0][$cfield])) {
			    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' || is_null($data[0][$cfield]) || is_null($dataPlusOne[0][$cfield])) $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 && !is_null($values[0]['sdiv_value']) && !is_null($values[1]['sdiv_value'])) {
    				$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(!is_null($values[0]['sdiv_value']) && !is_null($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 .= '';
            }
		} else {
		    $tendency .= '&nbsp;';
		}
		$tendency .= '</div>';

		return $tendency;
	}

}