<?php
/**
 * Dashboard module
 * 
 * MySQL queries related to dashboard module
 * 
 * @package linea21\modules\dashboard
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

// ////////// Check Inclusion de pages ////////////
if (!class_exists('auth')) {
	include_once("../lib/lib_common.php");
	ReloadIndex('admin');
}
// //////////

function SQL_getquickbox_list_sdi()
{
    $q = "SELECT sdii_id, sdii_name, DATE_FORMAT(sdii_last_modify, '" . toStringSqlDate('long') . "') AS sdii_last_modify, sdii_date_crea AS sdii_date_crea2, DATE_FORMAT(sdii_date_crea, '" . toStringSqlDate() . "') AS sdii_date_crea
FROM " . T_SDI_INFO . "
WHERE sdii_statut <> 'E'
ORDER BY sdii_date_crea2 DESC
LIMIT 0 , 10;";
    return $q;
}

function SQL_getquickbox_mod_sdi()
{
    $q = "SELECT sdii_id, sdii_name, DATE_FORMAT(sdii_last_modify, '".toStringSqlDate('long')."') AS sdii_last_modify, sdii_last_modify AS sdii_last_modify2, 
    DATE_FORMAT(sdii_date_crea, '".toStringSqlDate()."') AS sdii_date_crea 
    FROM " . T_SDI_INFO . "
    WHERE sdii_statut <> 'E'
    ORDER BY sdii_last_modify2 DESC
    LIMIT 0 , 10;";
    
    return $q;
}


function SQL_getquickbox_list($scale_id)
{
	$req_quickbox_list = "SELECT  sdiv_date_crea, MAX(sdiv_date_crea) AS date_c_max,
	DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS
	date_m, I.sdii_name, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, sdiv_statut,  V.sdiv_sdi_info, I.sdii_unit, sdiv_id, sdiv_value, sdiv_scale
	FROM " . T_SDI_VALUE . " as V LEFT OUTER JOIN " . T_SDI_INFO . " as I ON I.sdii_id=V.sdiv_sdi_info
	WHERE sdiv_statut<>'E' AND sdii_statut<>'E' AND sdiv_scale=$scale_id
	GROUP BY sdiv_sdi_info, sdiv_scale
	ORDER BY date_c_max DESC
	LIMIT 0 , 10 ;";
	return $req_quickbox_list;
}

function SQL_getquickbox_mod_list($scale_id)
{
	$req_quickbox_list = "SELECT I.sdii_name, sdiv_value, sdiv_id, sdiv_scale, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p,
	DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS
	date_m, sdiv_statut, V.sdiv_sdi_info, I.sdii_unit, MAX(sdiv_last_modify) AS date_m_max, sdiv_last_modify
	FROM " . T_SDI_INFO . " as I LEFT OUTER JOIN " . T_SDI_VALUE . " as V ON I.sdii_id=V.sdiv_sdi_info
	WHERE sdiv_statut<>'E' AND sdii_statut<>'E' AND sdiv_scale=$scale_id
	GROUP BY sdiv_sdi_info, sdiv_scale
	ORDER BY date_m_max DESC LIMIT 0 , 10  ;";
	return $req_quickbox_list;
}

function SQL_getAllsdiByLevel($id) {
    $q= "SELECT COUNT(sdii_id) AS countsdi FROM " . T_SDI_INFO . " WHERE sdii_level=$id AND sdii_statut <> 'E';";
    return $q;
}

function SQL_getListProjects()
{
	// return all projects
	// $q = "SELECT DISTINCT P.project_id, P.project_name FROM " . T_PROJECT . " AS P LEFT OUTER JOIN ". J_PROJECT_SDI . " as JPS on P.project_id =JPS.jps_project_id  WHERE P.project_statut = 'P' ORDER BY project_range ASC, project_name DESC;";
	// only return projects with indicators
	$q = "SELECT DISTINCT P.project_id, P.project_name, P.project_range FROM " . T_PROJECT . " AS P, ". J_PROJECT_SDI . " as JPS WHERE P.project_statut = 'P' AND P.project_id =JPS.jps_project_id ORDER BY project_range ASC, project_name ASC;";
	return $q;
	
}

function SQL_getListProjectsOrderedByLevels()
{
    // return all projects
    // $q = "SELECT DISTINCT P.project_id, P.project_name FROM " . T_PROJECT . " AS P LEFT OUTER JOIN ". J_PROJECT_SDI . " as JPS on P.project_id =JPS.jps_project_id  WHERE P.project_statut = 'P' ORDER BY project_range ASC, project_name DESC;";
    // only return projects with indicators
    $q = "SELECT DISTINCT P.project_id, P.project_name, P.project_range FROM " . T_PROJECT . " AS P, ". J_PROJECT_SDI . " as JPS WHERE P.project_statut = 'P' AND P.project_id =JPS.jps_project_id ORDER BY project_range ASC, project_name ASC;";
    return $q;
    
}

function SQL_get_associated_projects( $id )
{
    
    $q = "SELECT project_id, project_name, project_range FROM " . T_PROJECT . " P LEFT JOIN ".J_PROJECT_SDI." PS ON P.project_id = PS.jps_project_id WHERE jps_sdi_id = '" . $id ."' ORDER BY project_range ASC, project_name ASC";
    
    return $q;
}


$req_list_scale = "SELECT scale_id, scale_denomination, scale_root FROM " . T_SCALE . " WHERE scale_statut<>'E' ORDER BY scale_root DESC, scale_range DESC, scale_denomination ASC;";

function SQL_getonescale($scale_id)
{
	$q = "SELECT scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' AND scale_id=" . $scale_id . ";";
	return $q;
}

function SQL_getOnesdiname($id, $force_status = false)
{
	if($force_status) $force_status = " AND ". sql_status_filter('sdii_statut', $force_status);
	else $force_status = "";

	$q = "SELECT sdii_name, sdii_description FROM " . T_SDI_INFO . " WHERE sdii_id='" . $id . "'". $force_status.";";

	return $q;
}

function SQL_getIndicatorsAlpha($force_status = false) {

	if($force_status) $force_status = " AND ". sql_status_filter('sdii_statut', $force_status);
	else $force_status = "";

	$q = "SELECT sdii_id, sdii_name
				FROM " . T_SDI_INFO . "
				WHERE 1 = 1 ". $force_status." ORDER BY sdii_name ASC;";

			return $q;
}

// Function from rom /sdi/mysql.inc.php, initialy called from /sdi/list.php
// SQL_getListSdi() gives duplicates - probably because of left join on tags !
// we keep both function for now .... even if almost similar
// @todo have a look on that
function SQL_getListSdi2($filter, $valueparam = -1)
{
    $filter=strtoupper($filter);
    
    $q = "SELECT II.sdii_id, II.sdii_name, II.sdii_type, II.sdii_description, II.sdii_to_dashboard, II.sdii_max_value, II.sdii_min_value, II.sdii_threshold_value, II.sdii_threshold_relative, II.sdii_unit, II.sdii_api_enabled, II.sdii_api_url, II.sdii_frequency, II.sdii_api_datefield, II.sdii_api_valuefield, II.sdii_api_decimal, II.sdii_api_getvalues, II.sdii_api_getvalues_since, L.level_name, II.sdii_range, IP.sdip_name,II.sdii_statut, II.sdii_level
		FROM ". T_SDI_INFO ." AS II LEFT OUTER JOIN " . T_LEVEL . " as L on II.sdii_level=L.level_id
		LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id";
    if($filter==-1) {
        $q.=" WHERE II.sdii_statut<>'E';";
    }	else	{
        
        switch ($filter) {
            case 'T':
                $q.=" WHERE L.level_name LIKE'".$valueparam."'";
                $q.=" AND II.sdii_statut<>'E' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
                break;
            case 'S':
                $valueparam=strtoupper($valueparam);
                $q.=" WHERE II.sdii_statut='".$valueparam."' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
                break;
            case 'R':
                $q.=" WHERE II.sdii_statut<>'E' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
                break;
            case 'L':
                $q.=" WHERE II.sdii_level =".$valueparam."";
                $q.=" AND II.sdii_statut<>'E' ORDER BY II.sdii_range DESC, II.sdii_name ASC, II.sdii_level DESC";
                break;
        }
    }
    return $q;
}

function SQL_getListSdi($filter, $valueparam = -1)
{
	$filter = strtoupper($filter);

	$q = "SELECT II.sdii_id, II.sdii_type, II.sdii_nature, II.sdii_dashboard_viz, II.sdii_detail_viz,  II.sdii_value_type,  II.sdii_multiple_type, II.sdii_unique_mode,  II.sdii_name, II.sdii_description, II.sdii_comment, II.sdii_to_dashboard, II.sdii_max_value, II.sdii_min_value, II.sdii_threshold_value, II.sdii_threshold_relative, II.sdii_unit, II.sdii_frequency, II.sdii_api_enabled, II.sdii_api_url, II.sdii_api_datefield, II.sdii_api_valuefield, II.sdii_api_decimal, II.sdii_api_getvalues, II.sdii_api_getvalues_since, L.level_name, L.level_label, L.level_id, II.sdii_range, IP.sdip_name,II.sdii_statut, II.sdii_level, JPS.jps_sdi_id, JPS.jps_project_id
	FROM " . T_SDI_INFO . " AS II LEFT OUTER JOIN " . T_LEVEL . " as L on II.sdii_level=L.level_id
	LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id
	LEFT OUTER JOIN " . J_PROJECT_SDI . " as JPS on II.sdii_id =JPS.jps_sdi_id
    LEFT OUTER JOIN ".J_TAG." on II.sdii_id = jta_module_id ";

	switch ($filter) {
		case 'T':
			$q .= " WHERE L.level_name LIKE'" . $valueparam . "'";
			$q .= "AND II.sdii_statut<>'E' ORDER BY II.sdii_level ASC, II.sdii_range DESC, II.sdii_name ASC;";
			break;
		case 'S':
			$valueparam = strtoupper($valueparam);
			$q .= " WHERE II.sdii_statut='" . $valueparam . "' ORDER BY II.sdii_level ASC, II.sdii_range DESC, II.sdii_name ASC;";
			break;
		case 'R':
			$q .= " WHERE II.sdii_statut<>'E' ORDER BY II.sdii_level ASC, II.sdii_range DESC, II.sdii_name ASC;";
			break;
		case 'P':
			$q .= " WHERE II.sdii_statut='P' ORDER BY II.sdii_level ASC, II.sdii_range DESC, II.sdii_name ASC;";
			break;
		case 'TAG':
		    $q.=" WHERE II.sdii_statut='P' AND jta_module = 'indicator' AND jta_tag_id = '".$valueparam."' ORDER BY II.sdii_level ASC, II.sdii_range DESC, II.sdii_name ASC;";
		    break;
		default:
			$q .= " WHERE II.sdii_statut<>'E' ORDER BY II.sdii_level ASC, II.sdii_range DESC, II.sdii_name ASC;";
	}
	return $q;
}

function SQL_getInfoSdi($id)
{

	$q = "SELECT II.sdii_id, II.sdii_type, II.sdii_nature, II.sdii_dashboard_viz, II.sdii_detail_viz, II.sdii_mcolor, II.sdii_tcolor, II.sdii_value_type, II.sdii_multiple_type, II.sdii_multiple_type, II.sdii_name, II.sdii_description, II.sdii_comment, L.level_name, L.level_name as sdii_level_name, L.level_label, L.level_label as sdii_level_label, II.sdii_level, II.sdii_comment, II.sdii_to_dashboard, II.sdii_multiple_mode, II.sdii_input_instructions, 
	II.sdii_force_chart_min, II.sdii_unique_mode, 
    II.sdii_api_enabled, II.sdii_api_url, II.sdii_api_datefield, II.sdii_api_valuefield, II. sdii_api_multimapping, II.sdii_api_decimal, II.sdii_api_getvalues, II.sdii_api_getvalues_since, II.sdii_api_overwritevalues, 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,
	DATE_FORMAT(II.sdii_date_crea, '".toStringSqlDate('long')."' ) AS date_c, II.sdii_date_crea, II.sdii_last_modify, II.sdii_statut, II.sdii_statut AS sdii_status 
	FROM " . T_SDI_INFO . " AS II LEFT OUTER JOIN " . T_LEVEL . " as L on II.sdii_level=L.level_id
	LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id";
	$q .= " WHERE II.sdii_id='" . $id ."';";

	return $q;
}

/**
 * optimized query to run dashboard/api-harvest script
 * we retrieve only indicators using API and which needs to be updated according to last values and frequency field
 * @return string
 */
function SQL_get_indicators_to_update_by_api() {
    
    $q = 	"SELECT I.sdii_name, V.sdiv_id, I.sdii_id, I.sdii_api_overwritevalues, I.sdii_frequency, MAX(sdiv_date_published) AS datep, COUNT( sdiv_date_published ), 
            I.sdii_threshold_value, I.sdii_value_type, I.sdii_api_enabled, I.sdii_api_url, I.sdii_api_datefield, I.sdii_api_valuefield, I.sdii_api_multimapping, I.sdii_api_decimal, I.sdii_api_getvalues, I.sdii_api_getvalues_since, DATE_ADD(MAX(sdiv_date_published), INTERVAL I.sdii_frequency DAY ) as expected_run, 
            S.scale_denomination, S.scale_id
			FROM " . T_SDI_VALUE . " AS V
			LEFT OUTER JOIN " . T_SDI_INFO . " AS I ON V.sdiv_sdi_info = I.sdii_id
			LEFT OUTER JOIN " . T_SCALE . " AS S ON V.sdiv_scale = S.scale_id
			WHERE sdiv_statut <> 'E' AND sdii_statut <> 'E' AND I.sdii_frequency<>0 AND S.scale_id = 1 AND sdii_api_enabled = 'Y'
			GROUP BY I.sdii_id, I.sdii_name, S.scale_denomination
			HAVING DATE_ADD(MAX(sdiv_date_published), INTERVAL I.sdii_frequency DAY ) < NOW() OR I.sdii_api_overwritevalues = 'Y' 
			ORDER BY S.scale_id ";

    return $q;
}

function SQL_getProjectSdiValuesNumber($id) {
    
    $query = "SELECT count(II.sdii_id) as number
	FROM " . T_SDI_INFO . " AS II
	LEFT OUTER JOIN " . J_PROJECT_SDI . " as JPS on II.sdii_id =JPS.jps_sdi_id
	WHERE II.sdii_statut<>'E' AND JPS.jps_project_id='".$id."';";
    
    return $query;
}

function SQL_getProjectSdiValues($id) {

	$query = "SELECT II.sdii_id, II.sdii_name, II.sdii_type, II.sdii_nature, II.sdii_dashboard_viz, II.sdii_value_type, II.sdii_multiple_type, II.sdii_value_type, II.sdii_unique_mode, II.sdii_detail_viz, II.sdii_description, II.sdii_comment, II.sdii_to_dashboard, II.sdii_max_value, II.sdii_min_value, II.sdii_threshold_value, II.sdii_threshold_relative, II.sdii_unit, L.level_name, II.sdii_range, IP.sdip_name,II.sdii_statut, II.sdii_level
	FROM " . T_SDI_INFO . " AS II
	LEFT OUTER JOIN " . J_PROJECT_SDI . " as JPS on II.sdii_id =JPS.jps_sdi_id
	LEFT OUTER JOIN " . T_LEVEL . " as L on II.sdii_level=L.level_id
	LEFT OUTER JOIN " . T_SDI_PROVIDER . " as IP on II.sdii_provider=IP.sdip_id
	WHERE II.sdii_statut<>'E' AND JPS.jps_project_id='".$id."';";

	return $query;
}

function SQL_GetKeysSdi($sdi_id, $sql_object)
{
	$q = "SELECT sdii_provider, sdii_evaluation, sdii_reglementation
	FROM " . T_SDI_INFO . "
	WHERE sdii_id=" . $sdi_id . ";";
	$result = $sql_object->DBSelect($q);

	return $result;
}

function SQL_GetKeysSdi2($sdi_id)
{
	$q = "SELECT sdii_provider, sdii_evaluation, sdii_reglementation
	FROM " . T_SDI_INFO . "
	WHERE sdii_id=" . $sdi_id . ";";

	return $q;
}

function SQL_getRules($id_idd, $sql_object, $ID = -1)
{
	if ($ID == -1) {
		$data = SQL_GetKeysSdi($id_idd, $sql_object);
		$ID = $data[0]['sdii_reglementation'];
	}
	$q = "SELECT sdir_id, sdir_title, sdir_body, sdir_referer_uri, sdir_mask_uri,
	DATE_FORMAT(sdir_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdir_last_modify, '".toStringSqlDate('long')."' ) AS date_m
	FROM " . T_SDI_RULES . " WHERE sdir_id=" . $ID . ";";

	return $q;
}

function SQL_getProvider($id_idd, $sql_object, $ID = -1)
{
	if ($ID == -1) {
		$data = SQL_GetKeysSdi($id_idd, $sql_object);
		$ID = $data[0]['sdii_provider'];
	}
	$q = "SELECT sdip_id, sdip_name, sdip_service, sdip_description, sdip_incharge,
	sdip_address, sdip_phone, sdip_fax, sdip_email, DATE_FORMAT(sdip_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdip_last_modify, '".toStringSqlDate('long')."' ) AS date_m
	FROM " . T_SDI_PROVIDER . " WHERE sdip_id=" . $ID . ";";

	return $q;
}

function SQL_getEvaluation($id_idd, $sql_object, $ID = -1)
{
	if ($ID == -1) {
		$data = SQL_GetKeysSdi($id_idd, $sql_object);
		$ID = $data[0]['sdii_evaluation'];
	}
	$q = "SELECT sdie_id, sdie_scale_compare, sdie_fiability, sdie_accessibility, sdie_lisibility,
	sdie_relevance, sdie_global_performance, DATE_FORMAT(sdie_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdie_last_modify, '".toStringSqlDate('long')."' ) AS date_m
	FROM " . T_SDI_EVAL . " WHERE sdie_id=" . $ID . ";";

	return $q;
}

function SQL_getAllValue($filter, $scale_id, $indicator_id, $sort = 'DESC')
{
	$filter = strtoupper($filter);
	$q = "SELECT IF(user_login IS NULL, '" . _t('api', 'api_source') . "', user_login) as user_login, sdiv_id, sdiv_value, sdiv_user_id, sdiv_multivalue, sdiv_threshold, sdiv_analysis, sdiv_comment, sdiv_comment_display, sdiv_scale, sdiv_date_published, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify,  '".toStringSqlDate('long')."' ) AS date_m, DATE_FORMAT(sdiv_last_modify,  '".toStringSqlDate('short')."' ) AS date_m_short, sdiv_history, sdiv_statut
	FROM " . T_SDI_VALUE . " LEFT JOIN " . T_USER . " ON user_id = sdiv_user_id  WHERE sdiv_sdi_info=" . $indicator_id ;

	switch ($filter) {
		case 'D':
			$q .= " AND sdiv_date_published LIKE '" . $scale_id . "';";
			break;
		case 'SCP':
			$q .= " AND sdiv_statut='P'";
			$q .= " AND sdiv_scale=" . $scale_id;
			$q .= " ORDER BY sdiv_date_published ".$sort.";";
			break;
		case 'SCA':
			$q .= " AND sdiv_statut<>'E'";
			$q .= " AND sdiv_scale=" . $scale_id;
			$q .= " ORDER BY sdiv_date_published ".$sort.";";
			break;
		case 'SCD':
			$q .= " AND sdiv_statut='P'";
			$q .= " AND sdiv_scale=" . $scale_id;
			$q .= " ORDER BY sdiv_date_published ".$sort.";";
			break;
		case 'ST':
			$q .= " AND sdiv_statut='" . $scale_id . "';";
			break;
		default:
			$q .= " AND sdiv_statut<>'E'";
	}
	return $q;
}
function SQL_getOneValue($id)
{
	$q = "SELECT user_login, sdiv_user_id, sdiv_value, sdiv_multivalue, sdiv_scale, sdiv_threshold, sdiv_analysis, sdiv_comment, sdiv_comment_display, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS date_m, DATE_FORMAT(sdiv_last_modify,  '".toStringSqlDate('short')."' ) AS date_m_short, sdiv_history, sdiv_statut 
	FROM " . T_SDI_VALUE . " LEFT JOIN " . T_USER . " ON user_id = sdiv_user_id WHERE sdiv_id=" . $id . "";
	return $q;
}

function SQL_getlastInsertByPublicationDate($id, $scale_id)
{
	$q = "SELECT sdiv_value, sdiv_multivalue, sdiv_scale, sdiv_threshold, sdiv_sdi_info, sdiv_analysis, sdiv_comment, sdiv_comment_display, sdiv_date_published, DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, DATE_FORMAT(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS date_m, DATE_FORMAT(sdiv_last_modify,  '".toStringSqlDate('short')."' ) AS date_m_short, sdiv_history, sdiv_statut 
	FROM " . T_SDI_VALUE . " WHERE sdiv_sdi_info=" . $id . " AND sdiv_scale=" . $scale_id . " AND sdiv_statut <> 'E' ORDER BY sdiv_date_published DESC LIMIT 0, 1;";
	return $q;
}
function SQL_getIndicValuesBydate($id, $scale_id, $date)
{
    $q = "SELECT * FROM " . T_SDI_VALUE . " WHERE sdiv_sdi_info = " . $id . " AND sdiv_scale = " . $scale_id . " AND sdiv_date_published = '".$date."' AND sdiv_statut <> 'E' LIMIT 0, 1;";
    return $q;
}


function SQL_getInfoSdiforvalue($id)
{
	$q = "SELECT II.sdii_id, II.sdii_name, II.sdii_detail_viz, II.sdii_type, II.sdii_value_type, II.sdii_multiple_type, II.sdii_mcolor, II.sdii_tcolor, II.sdii_nature, II.sdii_comment, II.sdii_unit, II.sdii_max_value, II.sdii_min_value, II.sdii_force_chart_min, II.sdii_unique_mode, II.sdii_multiple_mode, II.sdii_frequency, 
	II.sdii_threshold_value, II.sdii_threshold_relative
	FROM " . T_SDI_INFO . " AS II" ;
	$q .= " WHERE II.sdii_id=" . $id . ";";

	return $q;
}

function SQL_getlistscale($scale_id)
{
	$req_sup = "SELECT scale_id, scale_denomination FROM " . T_SCALE . " WHERE scale_statut<>'E' AND scale_statut<>'I' AND scale_id<>'$scale_id' AND scale_dependencies<" . (MAX_SCALE_LEVEL-1) . " ORDER BY scale_id;";
	return $req_sup;
}
// /////////////////////////////////
// AJOUT GENERATION DE RAPPORT
// /////////////////////////////////
function GetAllIndicatorsInfo ($status, $type, $sdi_array = -1)
{
	$mask = '';
	$mask .= 'WHERE sdii_statut <> \'E\'';
	if ($type != 'ALL') {
		$sep = ' AND ';
		for($i = 0; $i < count($sdi_array); $i++) {
			$mask .= $sep . 'sdii_id = ' . $sdi_array[$i] . '';
			$sep = ' OR ';
		}
	}
	$q = "SELECT I .  * , P .  * , E .  * , R .  * , L .  * 
	FROM " .T_SDI_INFO. " AS I
	LEFT OUTER JOIN ".T_SDI_PROVIDER." AS P ON P.sdip_id = I.sdii_provider
	LEFT OUTER JOIN ".T_SDI_EVAL." AS E ON E.sdie_id = I.sdii_evaluation
	LEFT OUTER JOIN ".T_SDI_RULES." AS R ON R.sdir_id = I.sdii_reglementation
	LEFT OUTER JOIN ".T_LEVEL." AS L ON L.level_id = I.sdii_level ";
	$q .= $mask;
	$q .= " ORDER  BY level_name ASC, sdii_range ASC, sdii_name ASC;";

	return $q;
}

function SQL_getSdiValue($id_idd, $status, $type, $scale_array = -1)
{
	$mask = '';
	if ($status == 'ALL') $mask .= 'AND sdiv_statut <> \'E\'';
	else $mask .= 'AND sdiv_statut = \'P\'';
	if ($type != 'ALL') {
		$sep = ' AND ';
		for($i = 0; $i < count($scale_array); $i++) {
			$mask .= $sep . 'sdiv_scale = ' . $scale_array[$i] . '';
			$sep = ' OR ';
		}
	}

	$q = "SELECT sdiv_id, sdiv_value, sdiv_scale,
	DATE_FORMAT(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p,
	DATE_FORMAT(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c,
	DATE_FORMAT(sdiv_last_modify,  '".toStringSqlDate('long')."' ) AS date_m, sdiv_statut,
	scale_denomination
	FROM  " . T_SDI_VALUE . "
	LEFT OUTER JOIN " . T_SCALE . " ON scale_id = sdiv_scale
	WHERE sdiv_sdi_info=' . $id_idd . '";
	$q .= $mask;
	$q .= ' ORDER  BY scale_id ASC, scale_denomination ASC, sdiv_date_published DESC;';

	return $q;
}

function SQL_getAllScale()
{
	$q = "SELECT scale_id, scale_denomination, scale_dependencies FROM " . T_SCALE . " WHERE scale_statut<>'E' AND scale_statut<>'I' ORDER BY scale_dependencies ASC, scale_id ASC;";
	return $q;
}

?>