<?php
/**
 * Sdi class
 *
 * Povides methods related to indicator module (also called sdi)
 *
 * @package linea21\modules\sdi
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 * SDI (Sustainable Development Indicators) Management
 */

class sdi {
    /* @param
     * */
    // nom des tables utilisées dans l'objet.
    var $TDB_SDI_INFO = T_SDI_INFO ;
    var $TDB_SDI_EVAL = T_SDI_EVAL ;
    var $TDB_SDI_PROVIDER = T_SDI_PROVIDER ;
    var $TDB_SDI_RULES = T_SDI_RULES ;
    var $TDB_SDI_VALUE = T_SDI_VALUE ;
    var $TDB_LEVEL = T_LEVEL ;

    var $defaultMainColor = '#FE9440';
    var $defaultThresholdColor = '#4AC0F2';

    // variables utilisées pour caractéristiques principales d'un IDD
    var $I_ID;
    var $I_TYPE;
    var $I_NATURE;
    var $I_DASHBOARD_VIZ;
    var $I_DETAIL_VIZ;
    var $I_VALUE_TYPE;
    var $I_UNIQUE_MODE;
    var $I_MULTIPLE_TYPE;
    var $I_MULTIPLE_MODE;
    var $I_MCOLOR; // main color
    var $I_TCOLOR; // threshold color
    var $I_NAME;
    var $I_DESCRIPTION;
    var $I_INPUT_INSTRUCTIONS;
    var $I_COMMENT;
    var $I_LEVEL;
    var $I_RANGE;
    var $I_GOAL;
    var $I_CONSULTING;
    var $I_DASHBOARD;
    var $I_UNIT;
    var $I_FORCE_CHART_MIN;
    var $I_MAX_VALUE;
    var $I_MIN_VALUE;
    var $I_THRESHOLD;
    var $I_THRESHOLD_RELATIVE;
    var $I_FREQUENCY;
    var $I_API_ENABLED;
    var $I_API_URL;
    var $I_API_DATEFIELD;
    var $I_API_VALUEFIELD;
    var $I_API_MULTIMAPPING;
    var $I_API_DECIMAL;
    var $I_API_GETVALUES;
    var $I_API_GETVALUES_SINCE;
    var $I_API_OVERWRITEVALUES;
    var $I_PROVIDER;
    var $I_EVALUATION;
    var $I_REGLEMENTATION;
    var $I_DATE_CREA;
    var $I_LAST_MODIFY;
    var $I_STATUT;
    // variables utilisées pour critères d'évaluation d'un ID
    var $E_ID;
    var $E_SCALE;
    var $E_FIABILITY;
    var $E_ACCESSIBILITY;
    var $E_LISIBILITY;
    var $E_RELEVANCE;
    var $E_GLOBAL_PERFORMANCE;
    var $E_DATE_CREA;
    var $E_LAST_MODIFY;
    // variables utilisées pour organisme fournisseur d'un IDD
    var $P_ID;
    var $P_NAME;
    var $P_SERVICE;
    var $P_DESCRIPTION;
    var $P_INCHARGE;
    var $P_ADDRESS;
    var $P_PHONE;
    var $P_FAX;
    var $P_EMAIL;
    var $P_DATE_CREA;
    var $P_LAST_MODIFY;
    // variables utilisées pour réglementation d'un IDD
    var $R_ID;
    var $R_TITLE;
    var $R_BODY;
    var $R_REFERER_URI;
    var $R_MASK_URI;
    var $R_DATE_CREA;
    var $R_LAST_MODIFY;
    // variables utilisées pour valeur d'un IDD
    var $V_ID;
    var $V_VALUE;
    var $V_MULTIVALUE;
    var $V_THRESHOLD;
    var $V_SDI_ID;
    var $V_USER_ID;
    var $V_ANALYSIS;
    var $V_COMMENT;
    var $V_COMMENT_DISPLAY;
    var $V_SCALE;
    var $V_HISTORY;
    var $V_DATE_PUBLISHED;
    var $V_DATE_CREA;
    var $V_LAST_MODIFY;
    var $V_STATUT;
    protected $dispatcher = null;

    public function __construct()
    {
        $this->dispatcher = $GLOBALS['dispatcher'];
    }

    public function __call($method, $arguments)
    {
        $event = $this->dispatcher->notifyUntil(new sfEvent($this, 'sdi.extensible_function', array(
            'method'    => $method,
            'arguments' => $arguments
        )));
        if (!$event->isProcessed())
        {
            throw new Exception(sprintf('Call to undefined method %s::%s.', get_class($this), $method));
        }

        return $event->getReturnValue();
    }

    public function get_thresholdColor() {

        if(defined("CHART_TCOLOR")) return CHART_TCOLOR;
        else return $this->defaultMainColor;

    }

    public function get_ValuesColor() {

        if(defined("CHART_MCOLOR")) return CHART_MCOLOR;
        else return $this->defaultThresholdColor;

    }


    /**
     * sdi::CheckDataIntegrity_value()
     * Vérification d'une valeur d'un indicateur
     *
     * @access public
     * @param array $table contient les composants d'un indicateur
     * @return boolean si vrai renvoie true sinon message d'erreurs (string)
     */
    public function CheckDataIntegrity_value($table, $sql_object)
    {

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_datacheck_value', array('data' => $table)), $table);
        $table = $r->getReturnValue();

        $result = $this->_GetInfoSdi($table[1], $sql_object);

        $notvalue = strtolower(_t('dashboard','object_notvalue'));
        $notgoodvalue = strtolower(_t('dashboard','object_notgoodvalue'));

        if ($table[3] == 0) return _t('dashboard','object_notscale');

        // we test values only if value is not set as null
        if($table['nodata'] == 0) {
            // multivalues indicator
            if ($table['indicator_type'] == 'multiple') {

                $testvalue = 0;

                foreach ($table[0] as &$value) {
                    if (!is_numeric(sys_number_format($value['value']))) return $notvalue;
                    $testvalue += sys_number_format($value['value']); // return the total

                }
                if ($result[0]['sdii_multiple_type'] == 'mean') $testvalue = $testvalue / count($table[0]);

                // simple value indicator
            } else {
                if (!is_numeric(sys_number_format($table[0]))) return $notvalue;
                $testvalue = sys_number_format($table[0]);
            }
        }

        // test values only if max and min values are known and $result[0]['sdii_multiple_type'] != 'none'
        if(!is_null($result[0]['sdii_max_value']) && !is_null($result[0]['sdii_min_value']) && $result[0]['sdii_multiple_type'] != 'none') {

            // test only if threshold field is not blank
            if(!empty($table[2])) {
                if($result[0]['sdii_max_value'] < $result[0]['sdii_min_value']) {
                    if (sys_number_format($table[2]) > $result[0]['sdii_min_value'] ||  sys_number_format($table[2]) < $result[0]['sdii_max_value']) return ucfirst(_t('dashboard','threshold_value')) . ' : '. $notgoodvalue;
                } else {
                    if(sys_number_format($table[2]) < $result[0]['sdii_min_value'] || sys_number_format($table[2]) > $result[0]['sdii_max_value']) return ucfirst(_t('dashboard','threshold_value')) . ' : '.$notgoodvalue;
                }
            }

            // we test values only if value is not set as null
            if($table['nodata'] == 0) {
                if ($result[0]['sdii_max_value'] < $result[0]['sdii_min_value']) {
                    if ($testvalue > $result[0]['sdii_min_value'] || $testvalue < $result[0]['sdii_max_value']) return ucfirst(_t('dashboard', 'value')) . ' : ' . $notgoodvalue;
                } else {
                    if ($testvalue < $result[0]['sdii_min_value'] || $testvalue > $result[0]['sdii_max_value']) return ucfirst(_t('dashboard', 'value')) . ' : ' . $notgoodvalue;
                }
            }
        }

        if(is_string(checkdate_validity($table[4]))) return checkdate_validity($table[4]);

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_datacheck_value', array('data' => $table)));

        return true;
    }

    /**
     * sdi::CheckDataIntegrity_info()
     * Vérification des données générales d'un indicateur
     *
     * @access public
     * @param array $table contient les composants d'un indicateur
     * @return boolean si vrai renvoie true sinon message d'erreurs (string)
     */

    public function CheckDataIntegrity_info($table, $checktype = true, $field = 'nature')
    {

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_datacheck_info', array('data' => $table)), $table);
        $table = $r->getReturnValue();

        $notname = _t('sdi','object_notname');
        $notdescription = _t('sdi','object_notdescription');
        $notlevel = _t('sdi','object_notlevel');
        //$notrange = _t('sdi','object_notrange');
        $notunit = _t('sdi','object_notunit');
        $notmax_value = _t('sdi','object_notmax_value');
        $notmin_value = _t('sdi','object_notmin_value');
        $notthreshold_value = _t('sdi','object_notthreshold_value');
        $notfrequency = _t('sdi','object_notfrequency');

        if (strlen($table[0]) < 2) return $notname;
        if (strlen($table[1]) < 2) return $notdescription;
        if ($table[3] < 0 || !is_numeric($table[3])) return $notlevel;

        // we trim array elements
        $table = array_map('trim', $table);

        if ($table[7] == '') return $notunit;
        if ($table[8] != '' && !is_numeric(sys_number_format($table[8]))) return $notmax_value;
        if ($table[9] != '' && !is_numeric(sys_number_format($table[9]))) return $notmin_value;
        if ($table[10] != '' && !is_numeric(sys_number_format($table[10]))) return $notthreshold_value;
        if (!is_numeric($table[11])) return $notfrequency;

        // if max and min are provided, be sure max is superior to min
        if ($table[8] != '' && $table[9] != '' && sys_number_format($table[8]) <=  sys_number_format($table[9])) return _t('sdi','object_minmax_error');
        // if thresold and min are provided, be sure thresold is superior to min
        if ($table[10] != '' && $table[9] != '' && sys_number_format($table[10]) <   sys_number_format($table[9])) return _t('sdi','object_minthreshold_error');
        // if thresold and max are provided, be sure thresold is inferior to max
        if ($table[10] != '' && $table[8] != '' &&  sys_number_format($table[10]) >  sys_number_format($table[8])) return _t('sdi','object_maxthreshold_error');

        if ($table['dashboard_viz'] == 'gauge') {
            if(!is_numeric(sys_number_format($table[8])) || !is_numeric(sys_number_format($table[9])) || !is_numeric(sys_number_format($table[10])) ) return sprintf(_t('sdi','object_gauge_error1'), $GLOBALS['lang']['sdi']['select_dashboard_viz']['gauge']);
            // be sure min, max and threshold values are different !
            if ($table[10] != '' && $table[8] != '' &&  sys_number_format($table[10]) >= sys_number_format($table[8])) return sprintf(_t('sdi','object_gauge_error2'), $GLOBALS['lang']['sdi']['select_dashboard_viz']['gauge']);
            if ($table[10] != '' && $table[9] != '' &&  sys_number_format($table[10]) <= sys_number_format($table[9])) return sprintf(_t('sdi','object_gauge_error3'), $GLOBALS['lang']['sdi']['select_dashboard_viz']['gauge']);
        }

        if($table['api-enabled'] == 'Y') {
            if(strlen($table['api-date-field']) < 2) return _t('sdi','object_api_missingvaluedate');
            if($table['value-type'] == 'unique' && strlen($table['api-value-field']) < 2) return _t('sdi','object_api_missingvalues');
            if($table['value-type'] == 'multiple' && !is_array(unserialize($table['api-multimapping']))) return _t('sdi','object_api_missingvalues');
            if(strlen($table['api-url']) < 8) return _t('sdi','object_api_missingurl');
            if(!Stringy\Stringy::create($table['api-url'], CHARSET)->isUrl()) return _t('sdi','object_api_urlnotrecognised');
        }


        if($checktype) {
            if($table[$field] == 'qualitative' ||  $table[$field] == 'boolean') {
                // test comment field
                // get content with the following pattern
                // For example {0=non signée;1=signée}
                $pattern = '/{(.*?)}/';
                $r =preg_match($pattern, $table[2], $matches);
                if($r == 0 || strlen($matches[1]) < 6) return ucfirst(_t('sdi','object_noqualboolmask'));
            }
        }

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_datacheck_info', array('data' => $table)));

        return true;
    }

    /**
     * sdi::CheckDataIntegrity_eval()
     * Vérification des données d'évaluation d'un indicateur
     *
     * @access public
     * @param array $table contient les composants évaluation d'un indicateur
     * @return boolean si vrai renvoie true sinon message d'erreurs (string)
     */

    public function CheckDataIntegrity_eval($table)
    {
        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_datacheck_eval', array('data' => $table)), $table);
        $table = $r->getReturnValue();

        $not_scale_compare = _t('sdi','object_note_scale_compare');
        $not_fiability = _t('sdi','object_note_fiability');
        $not_accessibility = _t('sdi','object_note_accessibility');
        $not_lisibility = _t('sdi','object_note_lisibility');
        $not_relevance = _t('sdi','object_note_relevance');
        $not_global_performance = _t('sdi','object_note_global_performance');

        if (!is_numeric($table[13])) return $not_scale_compare;
        if (!is_numeric($table[14])) return $not_fiability;
        if (!is_numeric($table[15])) return $not_accessibility;
        if (!is_numeric($table[16])) return $not_lisibility;
        if (!is_numeric($table[17])) return $not_relevance;
        if (!is_numeric($table[18])) return $not_global_performance;

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_datacheck_eval', array('data' => $table)));

        return true;
    }

    /**
     * sdi::CheckDataIntegrity_provider()
     * Vérification des données d'un fournisseur d'un indicateur
     *
     * @access public
     * @param array $table contient les composants évaluation d'un indicateur
     * @return boolean si vrai renvoie true sinon message d'erreurs (string)
     */
    public function CheckDataIntegrity_provider($table)
    {

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_datacheck_provider', array('data' => $table)), $table);
        $table = $r->getReturnValue();

        $notp_name = _t('sdi','object_notp_name');
        $notp_address = _t('sdi','object_notp_address');
        $notp_phone = _t('sdi','object_notp_phone');
        $notp_fax = _t('sdi','object_notp_fax');
        $notp_mail = _t('sdi','object_notp_mail');

        // we trim array elements
        $table = array_map('trim', $table);

        if (!empty($table[19]) && strlen($table[19]) < 2) return $notp_name;
        if (!empty($table[23]) && strlen($table[23]) < 2) return $notp_address;

        if (trim($table[24]) != '' && !is_numeric($table[24])) return $notp_phone;
        if (trim($table[25]) != '' && !is_numeric($table[25])) return $notp_fax;

        if (!empty($table[26])) {
            $testmail = $this->_checkEmailValidity($table[26]);
            if ($testmail===false) return $notp_mail;
        }

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_datacheck_provider', array('data' => $table)));

        return true;
    }


    /**
     * sdi::CheckDataIntegrity_reglementation()
     * Vérification des données de réglementation d'un indicateur
     *
     * @access public
     * @param array $table contient les composants réglementation d'un indicateur
     * @return boolean si vrai renvoie true sinon message d'erreurs (string)
     */
    public function CheckDataIntegrity_reglementation($table)
    {

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_datacheck_reglementation', array('data' => $table)), $table);
        $table = $r->getReturnValue();

        $notr_title = _t('sdi','object_notr_title');
        $notr_body = _t('sdi','object_notr_body');

        // we trim array elements
        $table = array_map('trim', $table);

        if (!empty($table[27]) && strlen($table[27]) < 2) return $notr_title;
        if (!empty($table[28]) && strlen($table[28]) < 10) return $notr_body;

        if(CHECK_LINK==true && !empty($table[29])) {
            if (!url_exists($table[29])) return _t('sdi','server_noresponse');
        }

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_datacheck_reglementation', array('data' => $table)));

        return true;
    }

    /**
     * sdi::_checkEmailValidity()
     * Vérification du mail
     *
     * @access private
     * @param string $email contient les composants d'un mail
     * @return boolean si vrai renvoie true sinon message d'erreurs (string)
     */
    public function _checkEmailValidity($email)
    {
        // Notify the beginning of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.check_email_validity', array('email' => $email)));

        return validEmail($email);
    }


    /**
     * sdi::_AddProvider()
     * Ajout d'un nouveau fournisseur d'un IDD
     *
     * @access private
     * @param object $sql_object
     * @return integer $last_id
     */
    public function _AddProvider($sql_object)
    {
        // Notify the beginning of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.add_provider'));

        $q = "INSERT INTO " . $this->TDB_SDI_PROVIDER . " (sdip_name, sdip_service, sdip_description, sdip_incharge, sdip_address, sdip_phone, sdip_fax, sdip_email, sdip_date_crea) VALUES ('','','', '', '', '', '', '',NOW());";
        $last_id = $sql_object->DBInsert ($q, 1);
        return $last_id;
    }

    /**
     * sdi::_AddEvaluation()
     * Ajout de critères d'évaluation d'un IDD
     *
     * @access private
     * @param object $sql_object
     * @return integer $last_id
     */
    public function _AddEvaluation ($sql_object)
    {
        // Notify the beginning of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.add_evaluation'));

        $q = "INSERT INTO " . $this->TDB_SDI_EVAL . " (sdie_scale_compare, sdie_fiability, sdie_accessibility, sdie_lisibility, sdie_relevance, sdie_global_performance, sdie_date_crea)VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, NOW());";
        $last_id = $sql_object->DBInsert ($q, 1);
        return $last_id;
    }


    /**
     * sdi::_AddRules()
     * Ajout d'une réglementation d'un IDD
     *
     * @access private
     * @param object $sql_object
     * @return integer $last_id
     */
    public function _AddRules($sql_object)
    {
        // Notify the beginning of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.add_rules'));

        $q = "INSERT INTO " . $this->TDB_SDI_RULES . " (sdir_title, sdir_body, sdir_referer_uri, sdir_mask_uri, sdir_date_crea) VALUES ('', '', '', '', NOW());";
        $last_id = $sql_object->DBInsert ($q, 1);
        return $last_id;
    }


    /**
     * sdi::AddSdi()
     * Ajout d'un IDD
     *
     * @access public
     * @param array $table_sdi_info contient les infos detaillees d'un IDD
     * @param object $sql_object
     * @return integer $last_id
     */
    public function AddSdi($table_sdi_info, $sql_object)
    {
        global $l21auth;

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_add_indicator', array('data' => $table_sdi_info)), $table_sdi_info);
        $table_sdi_info = $r->getReturnValue();

        // image 64 encoded conversion if enabled before escaping content
        $table_sdi_info[1] = convertBase64Images($table_sdi_info[1], 'indic-');
        $table_sdi_info[2] = convertBase64Images($table_sdi_info[2], 'indic-');
        $table_sdi_info[5] = convertBase64Images($table_sdi_info[5], 'indic-');
        $table_sdi_info[6] = convertBase64Images($table_sdi_info[6], 'indic-');


        $table_sdi_info=$sql_object->DBescape($table_sdi_info);

        $this->I_NAME = strip_input($table_sdi_info[0]);
        $this->I_DESCRIPTION = strip_input($table_sdi_info[1], true);
        $this->I_INPUT_INSTRUCTIONS = strip_input($table_sdi_info['input_instructions'], true);
        $this->I_COMMENT = strip_input($table_sdi_info[2], true);
        $this->I_LEVEL = $table_sdi_info[3];
        $this->I_RANGE = $table_sdi_info[4];
        $this->I_GOAL = strip_input($table_sdi_info[5], true);
        $this->I_CONSULTING = strip_input($table_sdi_info[6], true);
        $this->I_UNIT = strip_input($table_sdi_info[7]);
        $this->I_FORCE_CHART_MIN = $table_sdi_info['chart_force_min'];


        if($table_sdi_info[8]=='') {
            $this->I_MAX_VALUE="NULL";
        } else {
            $this->I_MAX_VALUE=sys_number_format($table_sdi_info[8]);
        }
        if($table_sdi_info[9]=='') {
            $this->I_MIN_VALUE="NULL";
        } else {
            $this->I_MIN_VALUE=sys_number_format($table_sdi_info[9]);
        }
        if($table_sdi_info[10]=='') {
            $this->I_THRESHOLD="NULL";
        } else {
            $this->I_THRESHOLD=sys_number_format($table_sdi_info[10]);
        }
        $this->I_FREQUENCY = $table_sdi_info[11];

        $this->I_PROVIDER = $this->_AddProvider($sql_object);
        $this->I_EVALUATION = $this->_AddEvaluation($sql_object);
        $this->I_REGLEMENTATION = $this->_AddRules($sql_object);
        $this->I_STATUT = $table_sdi_info[12];
        $this->I_THRESHOLD_RELATIVE = $table_sdi_info[13];
        $this->I_DASHBOARD = $table_sdi_info[14];
        $this->I_TYPE = $table_sdi_info[15];
        $this->I_NATURE = $table_sdi_info['nature'];
        $this->I_DASHBOARD_VIZ = $table_sdi_info[16];
        $this->I_DETAIL_VIZ = $table_sdi_info[17];
        $this->I_VALUE_TYPE = $table_sdi_info[18];
        $this->I_UNIQUE_MODE = strip_input($table_sdi_info['unique_mode']);
        $this->I_MULTIPLE_TYPE = strip_input($table_sdi_info['multiple_type']);
        $this->I_MULTIPLE_MODE = strip_input($table_sdi_info['multiple_mode']);
        $this->I_MCOLOR = strip_input($table_sdi_info['mcolor']);
        $this->I_TCOLOR = strip_input($table_sdi_info['tcolor']);

        if($this->I_VALUE_TYPE == 'unique') $this->I_MULTIPLE_TYPE = 'sum'; // we force I_MULTIPLE_TYPE to 'sum' if I_VALUE_TYPE == 'unique' to prevent unexpected behavior when switching types

        // API info
        $this->I_API_ENABLED = strip_input($table_sdi_info['api-enabled']);
        $this->I_API_URL = strip_input($table_sdi_info['api-url']);
        $this->I_API_DATEFIELD = strip_input($table_sdi_info['api-date-field']);
        $this->I_API_VALUEFIELD = strip_input($table_sdi_info['api-value-field']);
        $this->I_API_MULTIMAPPING = strip_input($table_sdi_info['api-multimapping']);
        $this->I_API_DECIMAL = (is_numeric($table_sdi_info['api-decimal']) && $table_sdi_info['api-decimal'] < 6) ? $table_sdi_info['api-decimal'] : 0;
        $this->I_API_GETVALUES = strip_input($table_sdi_info['api-get-values']);
        $this->I_API_GETVALUES_SINCE = strip_input($table_sdi_info['api-get-values-since']);
        $this->I_API_OVERWRITEVALUES = strip_input($table_sdi_info['api-overwrite-values']);

        // we set field values based on I_NATURE
        if($this->I_NATURE == 'boolean' || $this->I_NATURE == 'qualitative') $this->I_VALUE_TYPE = 'unique';

        // we reset fields values based on I_VALUE_TYPE
        if($this->I_VALUE_TYPE == 'unique') $this->I_API_MULTIMAPPING = '';
        else $this->I_API_VALUEFIELD = '';

        $q = "INSERT INTO " . $this->TDB_SDI_INFO . " (sdii_type, sdii_nature, sdii_dashboard_viz, sdii_detail_viz, sdii_value_type, sdii_unique_mode, sdii_multiple_type, sdii_multiple_mode, sdii_mcolor, sdii_tcolor, sdii_name, sdii_description, sdii_input_instructions, sdii_comment, sdii_level, sdii_range, sdii_goal,
		sdii_consulting, sdii_to_dashboard, sdii_unit, sdii_max_value, sdii_min_value, sdii_threshold_value, sdii_threshold_relative, sdii_force_chart_min, sdii_frequency, sdii_api_enabled, sdii_api_url, sdii_api_datefield, sdii_api_valuefield, sdii_api_multimapping, sdii_api_decimal, sdii_api_getvalues, sdii_api_getvalues_since, sdii_api_overwritevalues, sdii_provider, sdii_evaluation,
		sdii_reglementation, sdii_statut, sdii_date_crea) VALUES ('".$this->I_TYPE."', '".$this->I_NATURE."', '" .$this->I_DASHBOARD_VIZ."', '" .$this->I_DETAIL_VIZ."', '" .$this->I_VALUE_TYPE."', '" .$this->I_UNIQUE_MODE."', '" .$this->I_MULTIPLE_TYPE."', '" .$this->I_MULTIPLE_MODE."', '". $this->I_MCOLOR . "', '" . $this->I_TCOLOR . "', '" .$this->I_NAME . "', '" . $this->I_DESCRIPTION . "', '". $this->I_INPUT_INSTRUCTIONS . "', '"
            . $this->I_COMMENT . "', " . $this->I_LEVEL . ", " . $this->I_RANGE . ", '" . $this->I_GOAL . "', '"
            . $this->I_CONSULTING ."' , '" . $this->I_DASHBOARD . "', '" . $this->I_UNIT . "' , " . $this->I_MAX_VALUE . " , " . $this->I_MIN_VALUE . " , "
            . $this->I_THRESHOLD . " , '" . $this->I_THRESHOLD_RELATIVE . "' , '" . $this->I_FORCE_CHART_MIN . "' , ". $this->I_FREQUENCY . " , '"
            . $this->I_API_ENABLED ."' , '" . $this->I_API_URL . "', '" . $this->I_API_DATEFIELD . "' , '" . $this->I_API_VALUEFIELD . "' , '" . $this->I_API_MULTIMAPPING . "' , " . $this->I_API_DECIMAL. " , '" . $this->I_API_GETVALUES . "' , '" . $this->I_API_GETVALUES_SINCE . "' , '". $this->I_API_OVERWRITEVALUES . "', "
            . $this->I_PROVIDER . " , " . $this->I_EVALUATION . " , "
            . $this->I_REGLEMENTATION . " ,'" . $this->I_STATUT . "' , NOW());";

        $result = $sql_object->DBInsert ($q, 1);

        // we log add action
        if(is_numeric($result))	logfile(LOG_MAINFILE, array('[action] add indicator', 'ID='.$result, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));


        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_add_indicator', array('data' => $table_sdi_info, 'id' => $result)));

        return $result;
    }

    /**
     * sdi::getIncrementalValuesFromAPI()
     * Ajout d'une valeur IDD
     *
     * @access public
     * @param array $table_sdi_value contient les composants d'une valeur d'un IDD
     * @param object $sql_object
     * @return integer $last_id
     */
    // @todo remove ?
    public function getIncrementalValuesFromAPI($sdi_id, $sql_object)
    {
        global $l21auth;

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_add_value', array('data' => $table_sdi_value)), $table_sdi_value);
        $table_sdi_value = $r->getReturnValue();


        $r = $sql_object->DBSelect ('SELECT * FROM ' . $this->TDB_SDI_INFO . ' WHERE sdii_id='.$sql_object->DBescape($sdi_id).';');

        if(is_array($r) && $r[0]['sdii_api_enabled'] == 'Y')

            $table_sdi_value=$sql_object->DBescape($table_sdi_value);

        // if multivalues
        if(is_array($table_sdi_value[0])) {
            $this->V_VALUE = 0;
            $this->V_MULTIVALUE = $this->_formatMultiValues($table_sdi_value[0], $sql_object);
        } else {
            $this->V_VALUE = sys_number_format($table_sdi_value[0]);
            $this->V_MULTIVALUE = '';
        }

        $this->V_SDI_ID = $table_sdi_value[1];
        if($table_sdi_value[2] !== '') {
            $this->V_THRESHOLD = sys_number_format($table_sdi_value[2]);
        } else {
            $this->V_THRESHOLD = "NULL";
        }
        $this->V_SCALE = $table_sdi_value[3];
        $this->V_DATE_PUBLISHED = formatDate($table_sdi_value[4], true);
        $this->V_STATUT = $table_sdi_value[5];
        $this->V_COMMENT = $table_sdi_value[6];
        $this->V_COMMENT_DISPLAY = $table_sdi_value[7];
        $this->V_USER_ID = $table_sdi_value['user_id'];

        $q = "INSERT INTO " . $this->TDB_SDI_VALUE . " (sdiv_value, sdiv_multivalue, sdiv_sdi_info, sdiv_user_id, sdiv_threshold, sdiv_comment, sdiv_comment_display, sdiv_scale, sdiv_date_published, sdiv_statut, sdiv_date_crea)
		VALUES (" . $this->V_VALUE . ", '" . $this->V_MULTIVALUE . "', ". $this->V_SDI_ID . ", " . $this->V_USER_ID . ", ". $this->V_THRESHOLD . ", '" . $this->V_COMMENT . "', '" . $this->V_COMMENT_DISPLAY . "', "
            . $this->V_SCALE . ", '" . $this->V_DATE_PUBLISHED . "', '" . $this->V_STATUT . "', NOW());";

        $last_id = $sql_object->DBInsert ($q, 1);

        // we log add action
        if(is_numeric($last_id))	logfile(LOG_MAINFILE, array('[action] add indicator value', 'ID='.$last_id, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));


        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_add_value', array('data' => $table_sdi_value, 'id' => $last_id)));

        return $last_id;
    }


    /**
     * sdi::AddMultipleValues()
     * Ajout de multiples valeurs IDD
     *
     * @access public
     * @param array $array contient les composants de 10 valeurs indicateurs
     * @param object $sql_object
     * @return integer $last_id
     */
    public function AddMultipleValues($array, $sql_object)
    {
        global $l21auth;
        $last_id = 0;

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_add_multiple_values', array('data' => $array)), $array);
        $array = $r->getReturnValue();

        for($i=0; $i < count($array); $i++) {
            // we check integrity and do not enter data if any problem, and log it
            if(is_string($this->CheckDataIntegrity_value($array[$i], $sql_object))) {
                logfile(LOG_MAINFILE, array('[ERROR] when entering indicator multiple values', 'data='.implode("/", $array[$i]), 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));
            } else {
                logfile(LOG_MAINFILE, array('[SUCCESS] multiple values insertion', 'data='.implode("/", $array[$i]), 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));
                $last_id = $this->AddValue($array[$i], $sql_object);
            }
        }

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_add_multiple_values', array('data' => $array, 'id' => $last_id)));

        return $last_id;
    }

    /**
     * sdi::AddValue()
     * Ajout d'une valeur IDD
     *
     * @access public
     * @param array $table_sdi_value contient les composants d'une valeur d'un IDD
     * @param object $sql_object
     * @return integer $last_id
     */
    public function AddValue($table_sdi_value, $sql_object)
    {
        global $l21auth;

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_add_value', array('data' => $table_sdi_value)), $table_sdi_value);
        $table_sdi_value = $r->getReturnValue();

        $table_sdi_value=$sql_object->DBescape($table_sdi_value);

        $this->V_HISTORY = '';

        // if multivalues
        if($table_sdi_value['indicator_type'] == 'multiple') {
            $this->V_VALUE = 0;
            $this->V_MULTIVALUE = '"'.$this->_formatMultiValues($table_sdi_value[0], $table_sdi_value['nodata']).'"';
        } else {
            if($table_sdi_value['nodata'] == 1) $this->V_VALUE = "NULL";
            else $this->V_VALUE = sys_number_format($table_sdi_value[0]);
            $this->V_MULTIVALUE = '""';
        }

        $this->V_SDI_ID = $table_sdi_value[1];
        if($table_sdi_value[2] !== '') {
            $this->V_THRESHOLD = sys_number_format($table_sdi_value[2]);
        } else {
            $this->V_THRESHOLD = "NULL";
        }
        $this->V_SCALE = $table_sdi_value[3];
        $this->V_DATE_PUBLISHED = formatDate($table_sdi_value[4], true);
        $this->V_STATUT = $table_sdi_value[5];
        $this->V_COMMENT = $table_sdi_value[6];
        $this->V_COMMENT_DISPLAY = $table_sdi_value[7];
        $this->V_USER_ID = $table_sdi_value['user_id'];
        $this->V_ANALYSIS = empty($table_sdi_value['analysis']) ? '' : $table_sdi_value['analysis'];

        $q = "INSERT INTO " . $this->TDB_SDI_VALUE . " (sdiv_value, sdiv_multivalue, sdiv_sdi_info, sdiv_user_id, sdiv_threshold, sdiv_analysis, sdiv_comment, sdiv_comment_display, sdiv_history, sdiv_scale, sdiv_date_published, sdiv_statut, sdiv_date_crea)
		VALUES (" . $this->V_VALUE . ", " . $this->V_MULTIVALUE . ", ". $this->V_SDI_ID . ", " . $this->V_USER_ID . ", ". $this->V_THRESHOLD . ", '" . $this->V_ANALYSIS ."', '" . $this->V_COMMENT . "', '" . $this->V_COMMENT_DISPLAY . "', '" . $this->V_HISTORY . "', "
            . $this->V_SCALE . ", '" . $this->V_DATE_PUBLISHED . "', '" . $this->V_STATUT . "', NOW());";

        $last_id = $sql_object->DBInsert ($q, 1);

        // we log add action
        if(is_numeric($last_id))	logfile(LOG_MAINFILE, array('[action] add indicator value', 'ID='.$last_id, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));


        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_add_value', array('data' => $table_sdi_value, 'id' => $last_id)));

        return $last_id;
    }

    /**
     * sdi::setInitialImportDone()
     * change sdii_api_getvalues to 'N' value
     *
     * @access public
     * @param int $ID identifiant de l'IDD
     * @param object $sql_object
     * @return bool $result
     */
    public function setInitialImportDone($ID, $sql_object) {

        // Notify the beginning of the current method
        $this->dispatcher->notify(new sfEvent($this, 'project.before_initial_import_done', array('id' => $ID)));

        $q = "UPDATE " . $this->TDB_SDI_INFO . " SET  sdii_api_getvalues='N', sdii_last_modify= NOW() WHERE sdii_id=" . $ID . ";";
        $result = $sql_object->DBQuery ($q);

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_initial_import_done', array( 'id' => $ID)));

        return $result;
    }


    /**
     * sdi::ModifySdi()
     * modification d'un IDD
     *
     * @access public
     * @param int $ID identifiant de l'IDD
     * @param object $sql_object
     * @param array $table_sdi_info contient les composants d'un IDD
     * @return bool $result
     */
    public function ModifySdi($ID, $table_sdi_info, $sql_object)
    {
        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_modify_indicator', array('data' => $table_sdi_info)), $table_sdi_info);
        $table_sdi_info = $r->getReturnValue();

        // image 64 encoded conversion if enabled before escaping content
        $table_sdi_info[1] = convertBase64Images($table_sdi_info[1], 'indic-');
        $table_sdi_info[2] = convertBase64Images($table_sdi_info[2], 'indic-');
        $table_sdi_info[5] = convertBase64Images($table_sdi_info[5], 'indic-');
        $table_sdi_info[6] = convertBase64Images($table_sdi_info[6], 'indic-');

        $table_sdi_info=$sql_object->DBescape($table_sdi_info);

        if (is_numeric($ID)) {
            $this->I_ID = $ID;
        } else return false;

        $this->I_NAME = strip_input($table_sdi_info[0]);
        $this->I_DESCRIPTION = strip_input($table_sdi_info[1], true);
        $this->I_INPUT_INSTRUCTIONS = strip_input($table_sdi_info['input_instructions'], true);
        $this->I_COMMENT = strip_input($table_sdi_info[2], true);
        $this->I_LEVEL = $table_sdi_info[3];
        $this->I_RANGE = $table_sdi_info[4]; // not updated
        $this->I_GOAL = strip_input($table_sdi_info[5], true);
        $this->I_CONSULTING = strip_input($table_sdi_info[6], true);
        $this->I_UNIT = strip_input($table_sdi_info[7]);
        $this->I_FORCE_CHART_MIN = $table_sdi_info['chart_force_min'];

        if($table_sdi_info[8]=='') {
            $this->I_MAX_VALUE="NULL";
        } else {
            $this->I_MAX_VALUE=sys_number_format($table_sdi_info[8]);
        }
        if($table_sdi_info[9]=='') {
            $this->I_MIN_VALUE="NULL";
        } else {
            $this->I_MIN_VALUE=sys_number_format($table_sdi_info[9]);
        }
        if($table_sdi_info[10]=='') {
            $this->I_THRESHOLD="NULL";
        } else {
            $this->I_THRESHOLD=sys_number_format($table_sdi_info[10]);
        }
        $this->I_FREQUENCY = $table_sdi_info[11];
        $this->I_STATUT = $table_sdi_info[12];
        $this->I_THRESHOLD_RELATIVE = $table_sdi_info[13];
        $this->I_DASHBOARD = $table_sdi_info[14];
        $this->I_TYPE = $table_sdi_info[15];
        $this->I_NATURE = $table_sdi_info['nature'];
        $this->I_DASHBOARD_VIZ = $table_sdi_info[16];
        $this->I_DETAIL_VIZ = $table_sdi_info[17];
        $this->I_VALUE_TYPE = $table_sdi_info[18];
        $this->I_UNIQUE_MODE = strip_input($table_sdi_info['unique_mode']);
        $this->I_MULTIPLE_TYPE = strip_input($table_sdi_info['multiple_type']);
        $this->I_MULTIPLE_MODE = strip_input($table_sdi_info['multiple_mode']);
        $this->I_MCOLOR = strip_input($table_sdi_info['mcolor']);
        $this->I_TCOLOR = strip_input($table_sdi_info['tcolor']);

        // we set field values based on I_NATURE
        if($this->I_NATURE == 'boolean' || $this->I_NATURE == 'qualitative') $this->I_VALUE_TYPE = 'unique';

        if($this->I_VALUE_TYPE == 'unique') $this->I_MULTIPLE_TYPE = 'sum'; // we force I_MULTIPLE_TYPE to 'sum' if I_VALUE_TYPE == 'unique' to prevent unexpected behavior when switching types

        // API info
        $this->I_API_ENABLED = strip_input($table_sdi_info['api-enabled']);
        $this->I_API_URL = strip_input($table_sdi_info['api-url']);
        $this->I_API_DATEFIELD = strip_input($table_sdi_info['api-date-field']);
        $this->I_API_VALUEFIELD = strip_input($table_sdi_info['api-value-field']);
        $this->I_API_MULTIMAPPING = strip_input($table_sdi_info['api-multimapping']);
        $this->I_API_DECIMAL = (is_numeric($table_sdi_info['api-decimal']) && $table_sdi_info['api-decimal'] < 6) ? $table_sdi_info['api-decimal'] : 0;
        $this->I_API_GETVALUES = strip_input($table_sdi_info['api-get-values']);
        $this->I_API_GETVALUES_SINCE = strip_input($table_sdi_info['api-get-values-since']);
        $this->I_API_OVERWRITEVALUES = strip_input($table_sdi_info['api-overwrite-values']);

        // we reset fields values based on I_VALUE_TYPE
        if($this->I_VALUE_TYPE == 'unique') $this->I_API_MULTIMAPPING = '';
        else $this->I_API_VALUEFIELD = '';

        $q = "UPDATE " . $this->TDB_SDI_INFO . " SET sdii_type='"
            . $this->I_TYPE ."', sdii_nature='"
            . $this->I_NATURE ."', sdii_dashboard_viz='"
            . $this->I_DASHBOARD_VIZ ."', sdii_detail_viz='"
            . $this->I_DETAIL_VIZ ."', sdii_value_type='"
            . $this->I_VALUE_TYPE ."', sdii_unique_mode='"
            . $this->I_UNIQUE_MODE ."', sdii_multiple_type='"
            . $this->I_MULTIPLE_TYPE ."', sdii_multiple_mode='"
            . $this->I_MULTIPLE_MODE ."', sdii_mcolor='"
            . $this->I_MCOLOR ."', sdii_tcolor='"
            . $this->I_TCOLOR ."', sdii_name='"
            . $this->I_NAME . "', sdii_description='"
            . $this->I_DESCRIPTION . "', sdii_input_instructions='"
            . $this->I_INPUT_INSTRUCTIONS   . "', sdii_comment='" . $this->I_COMMENT . "', sdii_level="
            . $this->I_LEVEL . ", sdii_range = sdii_range, sdii_goal='"
            . $this->I_GOAL . "', sdii_consulting='" . $this->I_CONSULTING . "', sdii_to_dashboard='"
            . $this->I_DASHBOARD . "', sdii_unit='"
            . $this->I_UNIT . "', sdii_max_value=" . $this->I_MAX_VALUE . ", sdii_min_value="
            . $this->I_MIN_VALUE . ", sdii_threshold_value=" . $this->I_THRESHOLD . ", sdii_threshold_relative='"
            . $this->I_THRESHOLD_RELATIVE . "', sdii_force_chart_min='"
            . $this->I_FORCE_CHART_MIN . "', sdii_frequency="
            . $this->I_FREQUENCY . ", sdii_api_enabled='"
            . $this->I_API_ENABLED . "', sdii_api_url='"
            . $this->I_API_URL . "', sdii_api_datefield='"
            . $this->I_API_DATEFIELD . "', sdii_api_valuefield='"
            . $this->I_API_VALUEFIELD . "', sdii_api_multimapping='"
            . $this->I_API_MULTIMAPPING . "', sdii_api_decimal='"
            . $this->I_API_DECIMAL . "', sdii_api_getvalues='"
            . $this->I_API_GETVALUES . "', sdii_api_getvalues_since='"
            . $this->I_API_GETVALUES_SINCE . "', sdii_api_overwritevalues='"
            . $this->I_API_OVERWRITEVALUES . "', sdii_statut='"
            . $this->I_STATUT . "', sdii_last_modify= NOW()
		WHERE sdii_id=" . $this->I_ID . ";";
        $result = $sql_object->DBQuery ($q);

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_modify_indicator', array('data' => $table_sdi_info, 'id' => $ID)));

        return $result;
    }

    /**
     * sdi::_GetKeysSdi()
     * récupération des clés d'un ID
     *
     * @access private
     * @param int $id_id identifiant de l'IDD
     * @param object $sql_object
     * @return array $table_key
     */
    public function _GetKeysSdi($sdi_id, $sql_object)
    {
        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.get_indicator_keys', array('id' => $sdi_id)));

        if (is_numeric($sdi_id)) {
            $this->I_ID = $sdi_id;
        }
        $q = "SELECT sdii_provider, sdii_evaluation, sdii_reglementation
		FROM " . $this->TDB_SDI_INFO . "
		WHERE sdii_id=" . $this->I_ID . ";";
        $result = $sql_object->DBSelect($q);

        return $result;

    }

    /**
     * sdi::ModifyParam()
     * modification des paramètres d'un IDD en fonction de son sdii_id
     *
     * @access public
     * @param int $ID identifiant de l'IDD
     * @param array $tableparam contient les composants d'un IDD
     * @param string $param paramètre passé :
     * E -> evaluation
     * P -> provider
     * R -> reglementation
     * @param object $sql_object
     * @return bool $result
     */
    public function ModifyParam($ID, $tableparam, $param, $sql_object)
    {
        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_modify_params', array('data' => $tableparam, 'id' => $ID, 'element' => $param)), $tableparam);
        $table_sdi_info = $r->getReturnValue();

        $param = strtoupper($param);
        switch ($param) {
            case 'E':
                $data = $this->_GetKeysSdi($ID, $sql_object);
                $id_e = $data[0]['sdii_evaluation'];
                $result = $this->_ModifyEvaluation($id_e, $tableparam, $sql_object);
                break;
            case 'P':
                $data = $this->_GetKeysSdi($ID, $sql_object);
                $id_p = $data[0]['sdii_provider'];
                $result = $this->_ModifyProvider($id_p, $tableparam, $sql_object);
                break;
            case 'R':
                $data = $this->_GetKeysSdi($ID, $sql_object);
                $id_r = $data[0]['sdii_reglementation'];
                $result = $this->_ModifyRules($id_r, $tableparam, $sql_object);
                break;
        }

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_modify_params', array('data' => $tableparam, 'id' => $ID, 'element' => $param)));

        return $result;
    }
    /**
     * sdi::DeleteSdi()
     * suppression d'un IDD
     *
     * @access public
     * @param int $id identifiant de l'IDD a supprimer
     * @param object $sql_object
     * @return bool $result
     */
    public function DeleteSdi($ID, $sql_object)
    {
        global $l21auth;

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.delete_indicator', array('id' => $ID)));

        $state = "E";
        $result = $this->StateSdi($ID, $state, $sql_object);

        // we log delete action
        if($result)	logfile(LOG_MAINFILE, array('[action] delete indicator', 'ID='.$ID, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));

        $q = "UPDATE " . $this->TDB_SDI_VALUE . " SET sdiv_statut='" . $state . "' WHERE sdiv_sdi_info=" . $ID . ";";
        $result2 = $sql_object->DBQuery ($q);
        return $result;
    }

    /**
     * sdi::_ModifyEvaluation()
     * modification d'une évaluation IDD
     *
     * @access private
     * @param int $ID identifiant de l'évaluation
     * @param object $sql_object
     * @param array $table_sdi_eval contient les composants d'un IDD
     * @return bool $result
     */
    public function _ModifyEvaluation($ID, $table_sdi_eval, $sql_object)
    {

        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_modify_evaluation', array('data' => $table_sdi_eval, 'id' => $ID)), $table_sdi_eval);
        $table_sdi_eval = $r->getReturnValue();

        $table_sdi_eval=$sql_object->DBescape($table_sdi_eval);

        if (is_numeric($ID)) {
            $this->E_ID = $ID;
        } else return false;

        // we fix maximum as 255
        $max =255;
        for($i=0; $i <= 5; $i++) {
            if($table_sdi_eval[$i] > $max) $table_sdi_eval[$i] = $max;
        }

        $this->E_SCALE = empty_numeric($table_sdi_eval[0]);
        $this->E_FIABILITY = empty_numeric($table_sdi_eval[1]);
        $this->E_ACCESSIBILITY = empty_numeric($table_sdi_eval[2]);
        $this->E_LISIBILITY = empty_numeric($table_sdi_eval[3]);
        $this->E_RELEVANCE = empty_numeric($table_sdi_eval[4]);
        $this->E_GLOBAL_PERFORMANCE = empty_numeric($table_sdi_eval[5]);


        $q = "UPDATE " . $this->TDB_SDI_EVAL . " SET sdie_scale_compare='" . $this->E_SCALE . "', sdie_fiability='"
            . $this->E_FIABILITY . "', sdie_accessibility='" . $this->E_ACCESSIBILITY . "', sdie_lisibility='"
            . $this->E_LISIBILITY . "', sdie_relevance='" . $this->E_RELEVANCE . "', sdie_global_performance='"
            . $this->E_GLOBAL_PERFORMANCE . "', sdie_last_modify = NOW()
		WHERE sdie_id=" . $this->E_ID . ";";

        $result = $sql_object->DBQuery ($q);

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_modify_evaluation', array('data' => $table_sdi_eval, 'id' => $ID)));

        return $result;
    }

    /**
     * sdi::_ModifyProvider()
     * modification d'un fournisseur IDD
     *
     * @access private
     * @param int $ID identifiant du fournisseur
     * @param object $sql_object
     * @param array $table_sdi_provider contient les composants d'un fournisseur
     * @return bool $result
     */
    public function _ModifyProvider ($ID, $table_sdi_provider, $sql_object)
    {
        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_modify_provider', array('data' => $table_sdi_provider, 'id' => $ID)), $table_sdi_provider);
        $table_sdi_provider = $r->getReturnValue();

        // image 64 encoded conversion if enabled before escaping content
        $table_sdi_provider[2] = convertBase64Images($table_sdi_provider[2], 'indic-');
        $table_sdi_provider[4] = convertBase64Images($table_sdi_provider[4], 'indic-');

        $table_sdi_provider=$sql_object->DBescape($table_sdi_provider);

        if (is_numeric($ID)) {
            $this->P_ID = $ID;
        } else return false;

        $this->P_NAME = strip_input($table_sdi_provider[0]);
        $this->P_SERVICE = strip_input($table_sdi_provider[1]);
        $this->P_DESCRIPTION = strip_input($table_sdi_provider[2], true);
        $this->P_INCHARGE = strip_input($table_sdi_provider[3]);
        $this->P_ADDRESS = strip_input($table_sdi_provider[4], true);
        $this->P_PHONE = strip_input($table_sdi_provider[5]);
        $this->P_FAX = strip_input($table_sdi_provider[6]);
        $this->P_EMAIL = strip_input($table_sdi_provider[7]);

        $q = "UPDATE " . $this->TDB_SDI_PROVIDER . " SET sdip_name= '" . $this->P_NAME . "', sdip_service='"
            . $this->P_SERVICE . "', sdip_description='" . $this->P_DESCRIPTION . "', sdip_incharge='"
            . $this->P_INCHARGE . "', sdip_address='" . $this->P_ADDRESS . "', sdip_phone='"
            . $this->P_PHONE . "', sdip_fax='" . $this->P_FAX . "', sdip_email='"
            . $this->P_EMAIL . "', sdip_last_modify = NOW()
		WHERE sdip_id='" . $this->P_ID . "';";

        $result = $sql_object->DBQuery ($q);

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_modify_provider', array('data' => $table_sdi_provider, 'id' => $ID)));

        return $result;
    }

    /**
     * sdi::_ModifyRules()
     * modification d'une réglementation
     *
     * @access private
     * @param int $ID identifiant d'une réglementation
     * @param object $sql_object
     * @param array $table_sdi_rules contient les composants d'une réglementation
     * @return bool $result
     */
    public function _ModifyRules ($ID, $table_sdi_rules, $sql_object)
    {
        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_modify_rules', array('data' => $table_sdi_rules, 'id' => $ID)), $table_sdi_rules);
        $table_sdi_rules = $r->getReturnValue();

        // image 64 encoded conversion if enabled before escaping content
        $table_sdi_rules[1] = convertBase64Images($table_sdi_rules[1], 'indic-');

        $table_sdi_rules=$sql_object->DBescape($table_sdi_rules);

        if (is_numeric($ID)) {
            $this->R_ID = $ID;
        } else return false;

        $this->R_TITLE = strip_input($table_sdi_rules[0]);
        $this->R_BODY = strip_input($table_sdi_rules[1], true);
        $this->R_REFERER_URI = strip_input($table_sdi_rules[2]);
        $this->R_MASK_URI = strip_input($table_sdi_rules[3]);

        $q = "UPDATE " . $this->TDB_SDI_RULES . " SET sdir_title= '" . $this->R_TITLE . "', sdir_body='"
            . $this->R_BODY . "', sdir_referer_uri='" . $this->R_REFERER_URI . "', sdir_mask_uri='"
            . $this->R_MASK_URI . "',  sdir_last_modify = NOW()
		WHERE sdir_id='" . $this->R_ID . "';";

        $result = $sql_object->DBQuery ($q);

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_modify_rules', array('data' => $table_sdi_rules, 'id' => $ID)));

        return $result;
    }

    /**
     * sdi::ModifyValue()
     * modification d'une valeur
     *
     * @access public
     * @param int $ID identifiant d'une valeur
     * @param object $sql_object
     * @param array $table_sdi_value contient les composants d'une valeur
     * @return bool $result
     */
    public function ModifyValue ($ID, $table_sdi_value, $sql_object)
    {
        // Filter data event + return value
        $r = $this->dispatcher->filter(new sfEvent($this, 'indicator.before_modify_value', array('data' => $table_sdi_value, 'id' => $ID)), $table_sdi_value);
        $table_sdi_value = $r->getReturnValue();

        $table_sdi_value=$sql_object->DBescape($table_sdi_value);

        if (is_numeric($ID)) {
            $this->V_ID = $ID;
        } else return false;

        // we populate history field
        $rec = $sql_object->DBSelect ('SELECT * FROM '. $this->TDB_SDI_VALUE . ' WHERE sdiv_id="' . $this->V_ID . '";');

        if(!empty($rec[0]['sdiv_history'])) $history = json_decode($rec[0]['sdiv_history'], true);
        else $history = [];
        array_push($history, array('date' => $rec[0]['sdiv_last_modify'], 'user' => $rec[0]['sdiv_user_id']));
        $this->V_HISTORY = json_encode($history);

        // if multivalues
        if($table_sdi_value['indicator_type'] == 'multiple') {
            $this->V_VALUE = 0;
            $this->V_MULTIVALUE = '"'.$this->_formatMultiValues($table_sdi_value[0], $table_sdi_value['nodata']).'"';
        } else {
            if($table_sdi_value['nodata'] == 1) $this->V_VALUE = "NULL";
            else $this->V_VALUE = sys_number_format($table_sdi_value[0]);
            $this->V_MULTIVALUE = '""';
        }

        $this->V_SDI_ID = $table_sdi_value[1];
        if($table_sdi_value[2] != '') {
            $this->V_THRESHOLD = sys_number_format($table_sdi_value[2]);
        } else {
            $this->V_THRESHOLD = "NULL";
        }
        $this->V_SCALE = $table_sdi_value[3];
        $this->V_DATE_PUBLISHED = formatDate($table_sdi_value[4], true);
        $this->V_STATUT = $table_sdi_value[5];
        $this->V_COMMENT = $table_sdi_value[6];
        $this->V_COMMENT_DISPLAY = $table_sdi_value[7];
        $this->V_USER_ID = $table_sdi_value['user_id'];
        $this->V_ANALYSIS = empty($table_sdi_value['analysis']) ? '' : $table_sdi_value['analysis'];

        $q = "UPDATE " . $this->TDB_SDI_VALUE . " SET sdiv_value=" . $this->V_VALUE . ", sdiv_multivalue=" . $this->V_MULTIVALUE . ", sdiv_sdi_info="
            . $this->V_SDI_ID . ", sdiv_user_id = ". $this->V_USER_ID  . ", sdiv_analysis='".$this->V_ANALYSIS. "', sdiv_threshold=".$this->V_THRESHOLD. ", sdiv_comment='".$this->V_COMMENT."', sdiv_comment_display='".$this->V_COMMENT_DISPLAY."', sdiv_scale=" . $this->V_SCALE .", sdiv_history='" . $this->V_HISTORY . "',sdiv_date_published='" . $this->V_DATE_PUBLISHED . "',sdiv_statut='" . $this->V_STATUT . "', sdiv_last_modify = NOW()
		WHERE sdiv_id=" . $this->V_ID . ";";

        // echo $q.'<br>';
        $result = $sql_object->DBQuery ($q);

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.after_modify_value', array('data' => $table_sdi_value, 'id' => $ID)));

        return $result;
    }

    /**
     * sdi::ModifyRange()
     * modification simple du rang d'un IDD
     *
     * @access public
     * @param int $ID identifiant de l'IDD
     * @param object $sql_object
     * @param int $range contient la valeur du rang à modifier
     * @return bool $result
     */
    public function ModifyRange ($ID, $range, $sql_object)
    {
        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.change_range', array('id' => $ID, 'range' => $range)));

        if (is_numeric($ID)) {
            $this->I_ID = $ID;
        } else return false;

        if (is_numeric($range)) {
            $this->I_RANGE = $range;
        } else return false;

        $q = "UPDATE " . $this->TDB_SDI_INFO . " SET sdii_range=" . $this->I_RANGE . ",	sdii_last_modify= NOW() WHERE sdii_id='" . $this->I_ID . "';";

        $result = $sql_object->DBQuery ($q);

        return $result;
    }

    /**
     * sdi::StateSdi()
     * modification simple du statut d'un IDD
     *
     * @access public
     * @param int $ID identifiant de l'IDD
     * @param object $sql_object
     * @param string $state contient la valeur du statut à modifier
     * @return bool $result
     */
    public function StateSdi ($ID, $state, $sql_object)
    {
        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.change_indicator_state', array('id' => $ID, 'state' => $state)));

        if (is_numeric($ID)) {
            $this->ID = $ID;
        } else return false;

        $q = "UPDATE " . $this->TDB_SDI_INFO . " SET sdii_statut='" . $state . "' WHERE sdii_id=" . $this->ID . ";";
        $result = $sql_object->DBQuery ($q);
        return $result;
    }


    /**
     * sdi::changeRanges()
     * changes Level range
     *
     * @access public
     * @param array : Id (key) and ranges (value)
     * @param object $sql_object
     * @return bool $result
     */

    public function changeRanges($array, $sql_object)
    {

        $rcnt = count($array); // reversed counter

        $child = array();

        if(count($array) > 0) {
            foreach ($array as $el) {

                $query = "UPDATE " . $this->TDB_SDI_INFO . " set sdii_range='".$rcnt."', sdii_last_modify=sdii_last_modify WHERE sdii_id='" . $el['id'] . "';";
                // echo $query. '<br />';
                $result = $sql_object->DBQuery($query);
                $rcnt--;

            }

        }

        // Notify the beginning of the current method
        $this->dispatcher->notify(new sfEvent($this, 'sdi.change_ranges', array('data' => $array)));

        return true;
    }

    /**
     * sdi::StateValue()
     * modification simple du statut d'une valeur d'un IDD
     *
     * @access public
     * @param int $id identifiant de l'IDD
     * @param object $sql_object
     * @param string $state contient la valeur du statut à modifier
     * @return bool $result
     */
    public function StateValue ($ID, $state, $sql_object)
    {
        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.change_value_state', array('id' => $ID, 'state' => $state)));

        if (is_numeric($ID)) {
            $this->ID = $ID;
        } else return false;
        $q = "UPDATE " . $this->TDB_SDI_VALUE . " SET sdiv_statut='" . $state . "' WHERE sdiv_id=" . $this->ID . ";";
        $result = $sql_object->DBQuery ($q);
        return $result;
    }

    /**
     * sdi::DeleteValue()
     * suppression  d'une valeur d'un IDD
     *
     * @access public
     * @param int $id identifiant de la valeur a supprimer
     * @param object $sql_object
     * @return bool $result
     */
    public function DeleteValue ($ID, $sql_object)
    {
        global $l21auth;

        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.delete_value', array('id' => $ID)));

        $state = "E";
        $result = $this->StateValue($ID, $state, $sql_object);

        // we log delete action
        if($result)	logfile(LOG_MAINFILE, array('[action] delete indicator value', 'ID='.$ID, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));

        return $result;
    }


    public function _GetInfoSdi($id, $sql_object)
    {
        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.get_info', array('id' => $id)));

        if(SQL=='mysql') $datefunction="DATE_FORMAT";
        if(SQL=='pgsql') $datefunction="to_char";

        $q = "SELECT II.sdii_id, II.sdii_name, II.sdii_value_type, II.sdii_description, II.sdii_multiple_type, II.sdii_comment, L.level_name, II.sdii_level,
		II.sdii_range, II.sdii_goal, II.sdii_consulting, II.sdii_unit, II.sdii_max_value, II.sdii_min_value,
		II.sdii_threshold_value, II.sdii_threshold_relative, II.sdii_frequency, II.sdii_provider, IP.sdip_name, II.sdii_evaluation, II.sdii_reglementation,
		" . $datefunction . "(II.sdii_date_crea, '" . toStringSqlDate() . "' ) AS date_c, II.sdii_statut
		FROM " . $this->TDB_SDI_INFO . " AS II LEFT OUTER JOIN " . $this->TDB_LEVEL . " as L on II.sdii_level=L.level_id
		LEFT OUTER JOIN " . $this->TDB_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id";
        $q .= " WHERE II.sdii_id=" . $id . ";";

        $result = $sql_object->DBSelect ($q);

        return $result;
    }

    public function _formatMultiValues($array, $nodata)
    {
        global $sql_object;
        // Notify the end of the current method
        $this->dispatcher->notify(new sfEvent($this, 'indicator.format_multi_values', array('data' => $array)));

        // there is no data, no need to compute anything
        if($nodata == 1) {
            foreach($array as &$val) {
                $val['value'] = NULL;
                $val['_percentage'] = NULL;
                $val['_total'] = NULL;
                $val['_mean'] = NULL;
            }
        } else {

            // we compute the total
            $sum = 0;
            $cnt = 0;
            foreach ($array as &$val) {
                $tmpVal = sys_number_format($val['value']);
                if (is_nan($tmpVal)) $val['value'] = NULL; // if NaN, we put NULL inside
                if ($tmpVal > 0 && !is_null($tmpVal)) $sum += $tmpVal; // do not add negative values
                $cnt++;
            }

            // we automatically add percentage field to the record
            foreach ($array as &$val) {
                $val['value'] = sys_number_format($val['value']); // we convert data to system format
                if ($sum > 0) $val['_percentage'] = sys_number_format($val['value']) / $sum * 100;
                else $val['_percentage'] = 0;
                if ($val['_percentage'] < 0) $val['_percentage'] *= -1; // do exprim percentage as positive value
                $val['_total'] = $sum;
                $val['_mean'] = $sum / $cnt;
            }
        }

        return $sql_object->DBescape(serialize($array));
    }

    /**
     * sdi::getScalesThresholds()
     * return an associative array with threshold value for
     * each scale (as key)
     *
     * @access public
     * @param int $id Indicator ID
     * @param $default_threshold
     * @param object $sql_object
     * @return array $a
     */
    public function getScalesThresholds($id, $default_threshold, $sql_object) {

        $a = array();
        $scales = $sql_object -> DBSelect(SQL_getAllScale(), 'OBJECT');

        foreach($scales as $scale) {

            $r = $sql_object -> DBSelect(SQL_getlastInsertByPublicationDate($id, $scale->scale_id));
            if(isset($r[0]['sdiv_value']) && !is_null($r[0]['sdiv_threshold'])) {
                $a[$scale->scale_id] = $r[0]['sdiv_threshold'];
                // _debug('key : '. $scale->scale_id . ' - value  : '.$r[0]['sdiv_threshold']);
            } else {
                $a[$scale->scale_id] = $default_threshold;
                // _debug('key : '. $scale->scale_id . ' - value  : '.$default_threshold. '(default)');
            }
        }
        return $a;
    }

    /**
     * @param $sql_object
     * @param $id
     * @param $status
     * @return array
     */
    public function api_list($sql_object, $id, $status) {

        $a = ['count' => 0, 'results' => []];
        $unique = [];
        $counter = 0;

        ($status == 'all') ? $status = 'R' : $status = 'P';
        $data = $sql_object->DBSelect(SQL_getListSdi($status));

        if(is_array($data)) {

            foreach ($data as $record) {
                // be sure we don't have duplicates !
                if (!in_array($record['sdii_id'], $unique)) {
                    array_push($unique, $record['sdii_id']);
                    $tmpArray = [];
                    $tmpArray['sdii_id'] = $record['sdii_id'];
                    $tmpArray['sdii_name'] = $record['sdii_name'];
                    $tmpArray['sdii_description'] = $record['sdii_description'];
                    $tmpArray['sdii_level'] = $record['sdii_level'];
                    $tmpArray['sdii_level_name'] = $record['level_name'];
                    $tmpArray['sdii_level_label'] = $record['level_label'];
                    $tmpArray['sdii_status'] = $record['sdii_statut'];
                    $counter++;
                    array_push($a['results'], $tmpArray);
                }
            }
            $a['count'] = $counter;
        }

        return $a;

    }

    /**
     * @param $sql_object
     * @param $id
     * @param $full
     * @return array
     */
    public function api_info($sql_object, $id, $status, $full = false) {

        // $data = $sql_object->DBSelect(GetAllIndicatorsInfo (null, 'ONE', array($id)));

        $excludes = ['level_name' => null, 'level_label' => null, 'level_name' => null, 'sdii_comment' => null, 'sdii_provider' => null, 'sdii_evaluation' => null, 'sdii_reglementation' => null, 'sdip_name' => null, 'date_c' => null, 'sdii_statut' => null, 'sdip_id' => null, 'sdie_id' => null, 'sdir_id' => null, 'date_m' => null]; // 'level_name' => null, 'level_label' => null, 'sdii_api_url' => null, 'sdii_api_datefield' => null, 'sdii_api_valuefield' => null, 'sdii_api_decimal' => null, 'sdii_api_getvalues' => null, 'sdii_api_getvalues_since' => null,

        $data = $sql_object->DBSelect(SQL_getInfoSdi($id));

        ($status == 'all') ? $status = 'all' : $status = 'P';

        // return no record message if empty or if status is not public when only public is accepted
        if(!is_array($data) || ($data[0]['sdii_status'] != 'P' && $status == 'P') ) return ['error' => 1, 'message' => _t('api', 'no_record')];

        // we add tags
        $otag = new tag();
        $tags = $otag->getTags('indicator', $id, false);
        $r = array_merge($data[0], ['sdii_tags' => $tags]);

        if($full) {
            $indicP = $sql_object->DBSelect(SQL_getProvider($id, $sql_object));
            $indicE = $sql_object->DBSelect(SQL_getEvaluation($id, $sql_object));
            $indicR = $sql_object->DBSelect(SQL_getRules($id, $sql_object));

            $r = array_merge($r, ['sdii_tags' => $tags], $indicP[0], $indicE[0], $indicR[0]);
        }

        // we remove unwanted fields before returning values
        $r = array_diff_key($r, $excludes);


        return $r;

    }

    /**
     * @param $sql_object
     * @param $id
     * @return array
     */
    public function api_infofull($sql_object, $id, $status) {

        // $data = $sql_object->DBSelect(GetAllIndicatorsInfo (null, 'ONE', array($id)));

        return $this->api_info($sql_object, $id, $status, true);

    }

    /**
     * @param $sql_object
     * @param $id
     * @return array
     */
    public function api_values($sql_object, $id, $status, $lastOnly = false) {

        $a = ['count_values' => 0, 'values' => []];

        // we retrieve indicator's info
        $info = $sql_object->DBSelect(SQL_getInfoSdi($id));
        // return no record message if empty or if status is not public when only public is accepted
        if(!is_array($info) || ($info[0]['sdii_status'] != 'P' && $status == 'P') ) return ['error' => 1, 'message' => _t('api', 'no_record')];

        ($status == 'all') ? $status = 'SCA' : $status = 'SCD';

        $excludes = ['user_login' => null, 'sdiv_id' => null, 'sdiv_user_id' => null, 'date_p' => null, 'date_c' => null, 'date_m' => null, 'sdiv_history' => null,  'sdiv_statut' => null];
        $data = $sql_object->DBSelect(SQL_getAllValue($status, 1, $id, 'ASC'));

        // return no record message
        if(!is_array($data)) return ['count_values' => 0, 'values' => []];

        $data = add_cumulative_value($data, $info[0], false);

        $r = [];
        // we remove unwanted fields before returning values
        foreach ($data as $rec) {
            if(!empty($rec['sdiv_multivalue'])) $rec['sdiv_multivalue'] = unserialize($rec['sdiv_multivalue']);
            if($rec['sdiv_comment_display'] == 'N') $rec['sdiv_comment'] = '[hidden]';
            $rec['sdiv_date_published'] = formatDate($rec['sdiv_date_published'], true);
            $tmp = array_diff_key($rec, $excludes);
            array_push($r, $tmp);
        }
        $a['count_values'] = count($r);
        $a['values'] = $r;

        if($lastOnly) {
            $a['count_values'] = 1;
            $a['values'] = $r[count($r)-1];
        }

        return $a;

    }

    /**
     * @param $sql_object
     * @param $id
     * @return array
     */
    public function api_infovalues($sql_object, $id, $status) {

        $info = $this->api_info($sql_object, $id, $status);
        $values = $this->api_values($sql_object, $id, $status);

        return array_merge($info, $values);

    }

    /**
     * @param $sql_object
     * @param $id
     * @return array
     */
    public function api_lastvalue($sql_object, $id, $status) {

        return $this->api_values($sql_object, $id, $status, true);

    }

    /**
     * @param $dataset
     * @param $fields
     * @return array
     */
    private function ghost_fields($dataset, $fields) {

        $cleanArray = [];

        if(count($dataset) == 0) return [];

        foreach($dataset as $rec) {

            foreach($fields as $v) {
                if(isset($rec[$v])) unset($rec[$v]);
            }
            // print_r($rec);
            array_push($cleanArray, $rec);
        }
        return $cleanArray;
    }

}

?>