<?php
/**
 * @package linea21.modules
 * @subpackage dashboard
 * @author linea21 <info@linea21.com>
 * @version $id SVN
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 */

// ////////// Check Inclusion de pages ////////////
if (!function_exists('AuthenthificationProcess')) {
    include_once("../lib/lib_common.php");
    ReloadIndex('admin');
} 
// //////////
function SQL_getquickbox_list($scale_id)
{
    $req_quickbox_list = "SELECT  V.sdiv_date_crea, MAX(sdiv_date_crea) AS date_c_max, 
to_char(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, to_char(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS 
date_m, I.sdii_name, to_char(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, V.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 V.sdiv_sdi_info, V.sdiv_scale, V.sdiv_date_crea, V.sdiv_last_modify, I.sdii_name, V.sdiv_date_published, V.sdiv_statut, I.sdii_unit, 
   V.sdiv_id, V.sdiv_value, V.sdiv_scale 
ORDER BY date_c_max DESC
  OFFSET 0 LIMIT 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, to_char(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, 
to_char(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, to_char(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 V.sdiv_sdi_info, V.sdiv_scale, I.sdii_name, V.sdiv_value, V.sdiv_id, V.sdiv_statut , V.sdiv_date_published, 
 V.sdiv_date_crea, V.sdiv_last_modify, I.sdii_unit 
 ORDER BY date_m_max DESC OFFSET 0 LIMIT 10;";
    return $req_quickbox_list;
} 

function SQL_getListtheme()
{
    $req_list_theme = "SELECT T.theme_id, T.theme_name, T.theme_statut, SDT.sdtheme_name, SDT.sdtheme_id FROM " . T_THEME . " AS T LEFT OUTER JOIN " . T_SD_THEME . " AS SDT ON SDT.sdtheme_id=T.theme_sd_theme WHERE T.theme_statut  <> 'E' ORDER BY sdtheme_range DESC, sdtheme_name ASC, theme_name ASC;";
    return $req_list_theme;
} 

$req_list_theme = "SELECT T.theme_id, T.theme_name, T.theme_statut, SDT.sdtheme_name, SDT.sdtheme_id FROM " . T_THEME . " AS T LEFT OUTER JOIN " . T_SD_THEME . " AS SDT ON SDT.sdtheme_id=T.theme_sd_theme WHERE T.theme_statut  <> 'E' ORDER BY sdtheme_range DESC, sdtheme_name ASC, theme_name ASC;";

$req_list_scale = "SELECT * FROM " . T_SCALE . " WHERE scale_statut='P' OR scale_statut='D' ORDER BY scale_dependencies ASC, scale_denomination ASC;";

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

function SQL_getOnesdiname($id)
{
    $req_Onesdiname = "SELECT sdii_name, sdii_description FROM " . T_SDI_INFO . " WHERE sdii_id=$id ;";
    return $req_Onesdiname;
} 

function SQL_getOnesditheme($theme_id)
{
    $req_Onetheme = "SELECT theme_name FROM " . T_THEME . " WHERE theme_id=$theme_id ;";
    return $req_Onetheme;
} 

function SQL_getAllsditheme($theme_id)
{
    $req_Allsditheme = "SELECT COUNT(sdii_id) AS countsdi FROM " . T_SDI_INFO . " WHERE sdii_theme=$theme_id AND sdii_statut <> 'E';";
    return $req_Allsditheme;
} 

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

    $requete = "SELECT II.sdii_id, II.sdii_name, II.sdii_description, II.sdii_max_value, II.sdii_min_value, II.sdii_threshold_value, II.sdii_threshold_relative, II.sdii_unit, T.theme_name, II.sdii_range, IP.sdip_name,II.sdii_statut, II.sdii_theme			
		FROM " . T_SDI_INFO . " AS II LEFT OUTER JOIN theme as T on II.sdii_theme=T.theme_id 
		LEFT OUTER JOIN sdi_provider as IP on II.sdii_provider=IP.sdip_id";

        switch ($filter) {
            case 'T':
                $requete .= " WHERE T.theme_name LIKE'" . $valueparam . "'";
                $requete .= "AND II.sdii_statut<>'E';";
                break;
            case 'S':
                $valueparam = strtoupper($valueparam);
                $requete .= " WHERE II.sdii_statut='" . $valueparam . "'";
                break;
            case 'R':
                $requete .= " WHERE II.sdii_statut<>'E'
				ORDER BY II.sdii_theme DESC
				";
                break;
            case 'P':
                $requete .= " WHERE II.sdii_statut='P'
				ORDER BY II.sdii_theme DESC
				";
                break;
			default:
				$requete .= " WHERE II.sdii_statut<>'E';";
    } 
    return $requete;
} 

function SQL_getInfoSdi($id)
{
    $requete = "SELECT II.sdii_id, II.sdii_name, II.sdii_description, II.sdii_comment, T.theme_name, II.sdii_theme,
		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,
		to_char(II.sdii_date_crea, '".toStringSqlDate('long')."' ) AS date_c, II.sdii_date_crea, II.sdii_last_modify, II.sdii_statut			
		FROM " . T_SDI_INFO . " AS II LEFT OUTER JOIN theme as T on II.sdii_theme=T.theme_id 
		LEFT OUTER JOIN sdi_provider as IP on II.sdii_provider=IP.sdip_id";
    $requete .= " WHERE II.sdii_id=" . $id . ";";

    return $requete;
} 

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

    return $result;
} 

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

    return $requete;
} 

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

    return $requete;
} 

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

    return $requete;
} 

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

    return $requete;
} 

function SQL_getAllValue($filter = -1, $valueparam, $id_idd)
{
    $filter = strtoupper($filter);
    $requete = "SELECT sdiv_id, sdiv_value, sdiv_scale, to_char(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, to_char(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, to_char(sdiv_last_modify,  '".toStringSqlDate('long')."' ) AS date_m, sdiv_statut			
		FROM " . T_SDI_VALUE . " WHERE sdiv_sdi_info=" . $id_idd ;

    switch ($filter) {
        case 'D':
            $requete .= " AND sdiv_date_published LIKE'" . $valueparam . "';";
            break;
        case 'SCP':
            $requete .= " AND sdiv_statut='P'";
            $requete .= " AND sdiv_scale=" . $valueparam;
            $requete .= " ORDER BY sdiv_date_published ;";
            break;
        case 'SCA':
            $requete .= " AND sdiv_statut<>'E'";
            $requete .= " AND sdiv_scale=" . $valueparam;
            $requete .= " ORDER BY sdiv_date_published DESC ;";
            break;
        case 'SCD':
            $requete .= " AND sdiv_statut='P'";
            $requete .= " AND sdiv_scale=" . $valueparam;
            $requete .= " ORDER BY sdiv_date_published DESC;";
            break;
        case 'ST':
            $requete .= " AND sdiv_statut='" . $valueparam . "';";
            break;
        default:
            $requete .= " AND sdiv_statut<>'E'";
    } 
    return $requete;
} 
function SQL_getOneValue($id)
{
    $requete = "SELECT sdiv_value, sdiv_scale, to_char(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, to_char(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, to_char(sdiv_last_modify, '".toStringSqlDate('long')."' ) AS date_m, sdiv_statut			
		FROM " . T_SDI_VALUE . " WHERE sdiv_id=" . $id . "";
    return $requete;
} 

function SQL_getInfoSdiforvalue($id)
{
    $requete = "SELECT II.sdii_id, II.sdii_unit, II.sdii_max_value, II.sdii_min_value,
		II.sdii_threshold_value, II.sdii_threshold_relative			
		FROM " . T_SDI_INFO . " AS II" ;
    $requete .= " WHERE II.sdii_id=" . $id . ";";

    return $requete;
} 

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 = '';
    if ($status == 'ALL') $mask .= 'WHERE sdii_statut <> \'E\'';
    else $mask .= 'WHERE sdii_statut = \'P\'';
    if ($type != 'ALL') {
        $sep = ' AND ';
        for($i = 0; $i < count($sdi_array); $i++) {
            $mask .= $sep . 'sdii_id = ' . $sdi_array[$i] . '';
            $sep = ' OR ';
        } 
    } 
    $requete = "SELECT I .  * , P .  * , E .  * , R .  * , T .  * , SDT .  * 
				FROM sdi_info AS I 
				LEFT OUTER JOIN sdi_provider AS P ON P.sdip_id = I.sdii_provider 
				LEFT OUTER JOIN sdi_evaluation AS E ON E.sdie_id = I.sdii_evaluation 
				LEFT OUTER JOIN sdi_reglementation AS R ON R.sdir_id = I.sdii_reglementation 
				LEFT OUTER JOIN theme AS T ON T.theme_id = I.sdii_theme 
				LEFT OUTER JOIN sd_theme AS SDT ON SDT.sdtheme_id = T.theme_sd_theme ";
    $requete .= $mask;
    $requete .= " ORDER  BY sdtheme_name ASC, sdii_name ASC;";

    return $requete;
} 

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

    $requete = "SELECT sdiv_id, sdiv_value, sdiv_scale, 
			  	to_char(sdiv_date_published, '".toStringSqlDate()."' ) AS date_p, 
				to_char(sdiv_date_crea, '".toStringSqlDate('long')."' ) AS date_c, 
				to_char(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 . "'";
    $requete .= $mask;
    $requete .= ' ORDER  BY scale_id ASC, scale_denomination ASC, sdiv_date_published DESC;';

    return $requete;
} 

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

?>