<?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 ;
	// variables utilisées pour caractéristiques principales d'un IDD
	var $I_ID;
	var $I_TYPE;
	var $I_DASHBOARD_VIZ;
	var $I_DETAIL_VIZ;
	var $I_VALUE_TYPE;
	var $I_NAME;
	var $I_DESCRIPTION;
	var $I_COMMENT;
	var $I_LEVEL;
	var $I_RANGE;
	var $I_GOAL;
	var $I_CONSULTING;
	var $I_DASHBOARD;
	var $I_UNIT;
	var $I_MAX_VALUE;
	var $I_MIN_VALUE;
	var $I_THRESHOLD;
	var $I_THRESHOLD_RELATIVE;
	var $I_FREQUENCY;
	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_COMMENT;
	var $V_COMMENT_DISPLAY;
	var $V_SCALE;
	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();
	}

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

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

		// multivalues indicator
		if(is_array($table[0])) {

			$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

			}

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

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

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

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

			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 beginning 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)
	 */

	function CheckDataIntegrity_info($table, $checktype = true, $field = 34)
	{

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

		$table[7]=trim($table[7]);
		if (empty($table[7])) return $notunit;
 		if (!empty($table[8]) && !is_numeric(sys_number_format($table[8]))) return $notmax_value;
 		if (!empty($table[9]) && !is_numeric(sys_number_format($table[9]))) return $notmin_value;
 		if (!empty($table[10]) && !is_numeric(sys_number_format($table[10]))) return $notthreshold_value;
		if (!is_numeric($table[11])) return $notfrequency;
		
		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 beginning 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)
	 */

	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 (!empty($table[13]) && !is_numeric($table[13])) return $not_scale_compare;
		if (!empty($table[14]) && !is_numeric($table[14])) return $not_fiability;
		if (!empty($table[15]) && !is_numeric($table[15])) return $not_accessibility;
		if (!empty($table[16]) && !is_numeric($table[16])) return $not_lisibility;
		if (!empty($table[17]) && !is_numeric($table[17])) return $not_relevance;
		if (!empty($table[18]) && !is_numeric($table[18])) return $not_global_performance;

		// Notify the beginning 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)
	 */
	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');

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

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

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

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

	/**
	 * resources::CheckOnlineFile()
	 * vérifie l'existence d'un fichier en ligne
	 *
	 * @access public
	 * @param  $path
	 * @return boolean
	 */
	function CheckOnlineFile($path)
	{
		// Notify the beginning of the current method
		$this->dispatcher->notify(new sfEvent($this, 'indicator.check_online_file', array('path' => $path)));

		$file_pt = @fopen($path, 'r');
		if (!$file_pt) return false;
		else {
			fclose($file_pt);
			return true;
		}
	}

	/**
	 * sdi::_AddProvider()
	 * Ajout d'un nouveau fournisseur d'un IDD
	 *
	 * @access private
	 * @param object $sql_object
	 * @return integer $last_id
	 */
	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
	 */
	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
	 */
	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
	 */
	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();
		 
		$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_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]);

		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_DASHBOARD_VIZ = $table_sdi_info[16];
		$this->I_DETAIL_VIZ = $table_sdi_info[17];
		$this->I_VALUE_TYPE = $table_sdi_info[18];



		$q = "INSERT INTO " . $this->TDB_SDI_INFO . " (sdii_type, sdii_dashboard_viz, sdii_detail_viz, sdii_value_type, sdii_name, sdii_description, 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_frequency, sdii_provider, sdii_evaluation,
		sdii_reglementation, sdii_statut, sdii_date_crea) VALUES ('".$this->I_TYPE."', '" .$this->I_DASHBOARD_VIZ."', '" .$this->I_DETAIL_VIZ."', '" .$this->I_VALUE_TYPE."', '" . $this->I_NAME . "', '" . $this->I_DESCRIPTION . "', '"
		. $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_FREQUENCY . " , " . $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::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
	 */
	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);

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

		$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_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]);
		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_DASHBOARD_VIZ = $table_sdi_info[16];
		$this->I_DETAIL_VIZ = $table_sdi_info[17];
		$this->I_VALUE_TYPE = $table_sdi_info[18];

		$q = "UPDATE " . $this->TDB_SDI_INFO . " SET sdii_type='"
		. $this->I_TYPE ."', sdii_dashboard_viz='"
		. $this->I_DASHBOARD_VIZ ."', sdii_detail_viz='"
		. $this->I_DETAIL_VIZ ."', sdii_value_type='"
		. $this->I_VALUE_TYPE ."', sdii_name='"
		. $this->I_NAME . "', sdii_description='"
		. $this->I_DESCRIPTION . "', sdii_comment='" . $this->I_COMMENT . "', sdii_level="
		. $this->I_LEVEL . ", sdii_range=" . $this->I_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_frequency="
		. $this->I_FREQUENCY . ", 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
	 */
	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
	 */
	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)), $table_sdi_info);
		$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
	 */
	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
	 */
	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;

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

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

		$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
	 */
	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;

	  // 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 = "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_threshold=".$this->V_THRESHOLD. ", sdiv_comment='".$this->V_COMMENT."', sdiv_comment_display='".$this->V_COMMENT_DISPLAY."', sdiv_scale=" . $this->V_SCALE . ",sdiv_date_published='" . $this->V_DATE_PUBLISHED . "',sdiv_statut='" . $this->V_STATUT . "', sdiv_last_modify = NOW()
		WHERE sdiv_id=" . $this->V_ID . ";";

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


	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_description, 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;
	}

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

		// we compute the total
		$sum = 0;
		foreach($array as $val) {
			if($val['value'] > 0) $sum += sys_number_format($val['value']); // do not add negative values
		}

		// we automatically add percentage field to the record
		foreach($array as &$val) {
			// $val['label'] = $sql_object->DBEscape($val['label']); @todo remove
			$val['value'] = sys_number_format($val['value']); // we convert data to system format
			$val['_percentage'] = sys_number_format($val['value']) / $sum * 100;
			if($val['_percentage'] < 0 ) $val['_percentage'] *= -1; // do exprim percentage as positive value
			$val['_total'] = $sum;
		}
	
		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
	 */
	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;
	}


}

?>